Changes between Initial Version and Version 1 of AdvancedReports

Timestamp:

02/10/23 10:50:41 (3 years ago)

Author:

181129

Comment:

--

AdvancedReports

@@ +1 @@
+{{{#!html
+<h1 style="text-align: left; color: #5d5250; margin: 0; margin-top: 10px">
+ Напредни извештаи од базата (SQL и складирани процедури)
+</h1>
+<hr style="margin: 0; margin-bottom:10px">
+}}}
+
+{{{#!html
+<h3 style="text-align: left; color: #5d5250; margin: 0; margin-top: 10px">
+ Промет во последната година
+</h3>
+<hr style="margin: 0; margin-bottom:10px">
+}}}
+
+{{{#!sql
+SELECT
+  SUM(CASE WHEN TipDok.TipID = 10 THEN -Stavka.Cena_Vkupno
+  WHEN TipDok.TipID = 20 THEN Stavka.Cena_Vkupno END) as Promet
+FROM
+  Stavka
+JOIN TipDok ON Stavka.TipID = TipDok.TipID
+WHERE
+  Datum >= date_trunc('year', current_date) - INTERVAL '1 year'
+  AND Datum < date_trunc('year', current_date)
+}}}
+
+{{{#!html
+<h3 style="text-align: left; color: #5d5250; margin: 0; margin-top: 10px">
+ Најтргован продукт во последната година
+</h3>
+<hr style="margin: 0; margin-bottom:10px">
+}}}
+
+{{{#!sql
+SELECT Artikal_Ime, SUM(Kolicina)
+  AS Vkupno_Trgovan
+FROM
+  LagerList
+JOIN Artikal ON LagerList.Artikal_ID = Artikal.Artikal_ID
+JOIN Stavka ON LagerList.StavkaID = Stavka.StavkaID
+WHERE
+  Datum >= date_trunc('year', current_date) - INTERVAL '1 year'
+  AND Datum < date_trunc('year', current_date)
+GROUP BY Artikal_Ime
+ORDER BY Vkupno_Trgovan DESC
+LIMIT 1;
+}}}
+
+{{{#!html
+<h3 style="text-align: left; color: #5d5250; margin: 0; margin-top: 10px">
+ Клиенти подредени по најпрофитабилни во последниот квартал
+</h3>
+<hr style="margin: 0; margin-bottom:10px">
+}}}
+
+{{{#!sql
+SELECT 
+  Klient.KlientIme,
+  SUM(CASE WHEN Stavka.TipID = 10 THEN -Stavka.Cena_Vkupno
+  WHEN Stavka.TipID = 20 THEN Stavka.Cena_Vkupno END) AS Vkupen_Promet
+FROM
+  Klient
+JOIN Stavka ON Klient.KlientID = Stavka.KlientID
+JOIN TipDok ON Stavka.TipID = TipDok.TipID
+WHERE
+  Stavka.Datum >= date_trunc('quarter', current_date) - INTERVAL '3 months'
+  AND Stavka.Datum < date_trunc('quarter', current_date)
+GROUP BY Klient.KlientIme
+ORDER BY Vkupen_Promet DESC
+}}}
+