Izračunavanja korišćenjem formula u meta podacima

Vrlo često se formule za razliičita izračunavanja nad podacima, obično finansijskim, smeštavaju kao meta podaci u tabele na SQL Serveru. To podrazumeva da postoji logika koja dinamički može da zameni promenjive u tim forrmulama i uradi potrebna izračunavanja nad nekim skupom podataka iz jedne ili više tabela.

Skoro je i meni tako nešto zatrebalo pa, pošto nas je Google navikao da je moguće, adekvatnom pretragom, naći rešenje skoro za sve i da je bacanje vremena sesti i iskodirati samostalno ono što nam treba, ja sam pokrenuo browser i pitao Google za TSQL kod  koji bi mi dinamički radio neka izračunavanja na osnovu formula u metapodacima. Ispostavilo se je da je ono što sam našao od primera i rešenja na Google-u blago rečeno neupotrebljivo a neki su, čini mi se, i baš zabrazdili uključujući u svoje TSQL izraze, pored ostalog recimo i ASCII kodove.

Onda sam, pošto me je Google ovog puta izdao, seo i napisao ono što mi je trebalo u vremenu koje sam imao (rok je naravno bio za juče ako je neko sumnjao). Prvo sam napisao funkciju koja mi parsira formulu i od toga pravi tabelu sa promenjivima koje se javljaju u formuli (primer koda 1).

CREATE FUNCTION [dbo].[RazloziFormulu]
(
@Formula nvarchar(250)
)
RETURNS @TabelaSaRazlozenomFormulom TABLE
([Promenjiva] varchar(50)
,[Operacija] varchar(10))
AS
BEGIN
declare @Promenjiva nvarchar(50)
,@Operacija nvarchar(10)
,@JedanKarakter nvarchar(1)
,@Brojac int
,@Iznos numeric(18, 2)
---------------------------------------

set @JedanKarakter = ''
set @Brojac = 1
set @Promenjiva = ''
set @Operacija = ''

---------------------------------------

set @Brojac = 1
while @Brojac <= len(@Formula)
begin
set @JedanKarakter=substring(@Formula, @Brojac, 1)

if @JedanKarakter not in ('+','-','*','/','(',')')
begin
set @Promenjiva = @Promenjiva + @JedanKarakter
end
else
begin
set @Operacija = @JedanKarakter

if len(@Promenjiva) > 1
begin
insert into @TabelaSaRazlozenomFormulom([Promenjiva],[Operacija]) values(@Promenjiva, ISNULL(@Operacija, ''))
end

set @Promenjiva = ''
end

if @Brojac = len(@Formula)
begin
if len(@Promenjiva) > 1
begin
insert into @TabelaSaRazlozenomFormulom([Promenjiva],[Operacija]) values(@Promenjiva, '')
end
end

set @Brojac = @Brojac + 1
end

Return
END

Primer koda 1. Table-valued funkcija za razlaganje formule na promenjive

Ako izvršimo ovu Table-valued funkciju dobićemo set zapisa kao što je dato na slici 1.

Meta formule sl. 1

Slika 1. IzvršavanjemTable-valued funkcije dobija se set zapisa sa promenjivima u konkretnoj formuli

Sad je još ostalo da tim promenjivima dodelimo neke vrednosti a zatim ih zamenimo tim vrednostima u formuli. Primer jednog takvog TSQL- dat je u primeru koda 2.

declare @Formula nvarchar(250)
,@Promenjiva nvarchar(50)
,@Operacija varchar(10)
,@Iznos numeric(18, 2)
,@FormulaSaIznosima nvarchar(250)
---------------------------------------
set @Promenjiva = ''
set @Iznos = 0
set @Operacija = ''
---------------------------------------
set @Formula = 'U213+(-1*U215)+(-1*U217)-(U216+U218)'
---------------------------------------

set @FormulaSaIznosima = @Formula
DECLARE db_cursor_t1 CURSOR FOR
SELECT [Promenjiva]
,[Operacija]
FROM [dbo].[RazloziFormulu] (@Formula)
OPEN db_cursor_t1
FETCH NEXT FROM db_cursor_t1 INTO @Promenjiva, @Operacija

WHILE @@FETCH_STATUS = 0
BEGIN
set @Iznos = @Iznos + 1000

set @FormulaSaIznosima = replace(@FormulaSaIznosima, @Promenjiva + @Operacija, convert(varchar(50), @Iznos) + @Operacija)

FETCH NEXT FROM db_cursor_t1 INTO @Promenjiva, @Operacija
END

CLOSE db_cursor_t1
DEALLOCATE db_cursor_t1
---------------------------------------
select [Formula]=@Formula, [FormulaSaIznosima]=@FormulaSaIznosima

set @FormulaSaIznosima = 'select [IzracunatiIznos]=' + @FormulaSaIznosima

exec sp_executesql @FormulaSaIznosima
---------------------------------------

Primer koda 2. Izračunavanje formule na osnovu metapodataka

Izlaz iz ovog koda je dat na slici 2.

Meta formule sl. 2

Slika 2. Rezultat izračunavanja konkretne formule na osnovu meta podataka

Naravno, ono što sam ovde izneo je samo osnovna ideja koju sam trebao da doradim i prilagodim konkretnim potrebama. Ipak, završilo mi je posao pa se nadam da će i nekima od vas koristiti 🙂

Leave a Reply

%d bloggers like this: