Changes between Version 5 and Version 6 of AdvancedReports

Timestamp:

12/20/22 13:48:38 (3 years ago)

Author:

201045

Comment:

--

AdvancedReports

@@ -158 +158 @@
 $$
 }}}
+
+== Функција за пресметување на вкупен износ на нарачка ==
+
+{{{#!sql
+drop function calculate_cost_of_order_food(order_id int);
+
+
+create  function calculate_cost_of_order_food(order_id int)
+returns table(
+        vkupno_hrana int
+)
+language plpgsql
+as 
+$$
+begin 
+        return query select kolicina * iznos as vkupno
+        from naracka n join naracka_sodrzi_hrana nih on n.naracka_id = nih.naracka_id
+        join hrana h on h.id_stavka = nih.id_stavka 
+        join cena c on (c.id_stavka = h.id_stavka and vazi_do is null)
+        or (c.id_stavka = h.id_stavka and vazi_do > now())
+        where n.naracka_id = order_id;
+        
+        
+        
+end 
+$$
+
+drop function calculate_cost_of_order_packets(order_id int);
+
+create function calculate_cost_of_order_packets(order_id int)
+returns table (
+        vkupno_paketi bigint
+)
+language plpgsql
+as 
+$$
+begin
+        return query select sum(iznos) * nip.kolicina  as vkupno_paketi
+        from naracka n join naracka_ima_paket nip on n.naracka_id = nip.naracka_id 
+        join paket pa on pa.paket_id  = nip.paket_id 
+        join paket_sodrzi_hrana psh on psh.paket_id = pa.paket_id 
+        join hrana h on h.id_stavka = psh.id_stavka 
+        join cena c on (c.id_stavka = h.id_stavka and vazi_do is null)
+                or (c.id_stavka = h.id_stavka and vazi_do > now())
+        where n.naracka_id = order_id
+        group by n.naracka_id, pa.paket_id, nip.kolicina ; 
+end
+$$
+
+
+drop function calculate_order_cost(orider_id int);
+
+create function calculate_order_cost(order_id int)
+returns bigint
+language plpgsql
+as 
+$$
+declare 
+vkupno_cena_hrana bigint;
+vkupno_cena_paketi bigint;
+begin
+        select sum(vkupno_hrana) into vkupno_cena_hrana from calculate_cost_of_order_food(order_id);
+        select sum(vkupno_paketi) into vkupno_cena_paketi from calculate_cost_of_order_packets(order_id);
+        return vkupno_cena_hrana + vkupno_cena_paketi;
+        
+end
+$$
+
+}}}