Changes between Initial Version and Version 1 of Advancedreports

Timestamp:

01/27/23 15:51:51 (2 years ago)

Author:

201134

Comment:

--

Advancedreports

@@ +1 @@
+= Напредни извештаи од базата (SQL и складирани процедури) =
+
+=== Извештај за број на закажани настани во секој од последните 3 месеци поединечно ===
+
+{{{#!sql
+select sum(ovoj_mesec.broj_nastani) as zakazani_nastani_ovoj_mesec, sum(prethoden_mesec.broj_nastani) as zakazani_nastani_prethoden_mesec, sum(predprethoden_mesec.broj_nastani) as zakazani_nastani_predprethoden_mesec
+from nastani as n 
+left join(
+        select n.nastan_id as nastan_id, n.datum as nastan_datum, n.tip as tip_nastan, kb.ime as ime_bend, kk.ime as ime_ketering, kf.ime as ime_fotograf , count (*) as broj_nastani
+        from nastani as n 
+        left join bend_sviri_nastan bsn on bsn.nastan_id = n.nastan_id 
+        left join bendovi as b on b.korisnik_id = bsn.korisnik_id 
+        left join korisnici kb on kb.korisnik_id = b.korisnik_id 
+        
+        left join ketering_rezervira_nastan krn on krn.nastan_id = n.nastan_id 
+        left join keterinzi as k on k.korisnik_id = krn.korisnik_id 
+        left join korisnici kk on kk.korisnik_id = k.korisnik_id 
+        
+        left join fotograf_slika_nastan fsn on fsn.nastan_id = n.nastan_id
+        left join fotografi as f on f.korisnik_id = fsn.korisnik_id 
+        left join korisnici kf on kf.korisnik_id = f.korisnik_id 
+        
+        where extract(month from n.datum) = extract(month from now()) and extract(year from n.datum) = extract(year from now())
+        
+        group by 1,2,3,4,5,6
+
+) as ovoj_mesec on n.nastan_id = ovoj_mesec.nastan_id and n.tip = ovoj_mesec.tip_nastan
+
+left join( 
+        select n.nastan_id as nastan_id, n.datum as nastan_datum, n.tip as tip_nastan, kb.ime as ime_bend, kk.ime as ime_ketering, kf.ime as ime_fotograf, count (*)  as broj_nastani
+        from nastani as n 
+        left join bend_sviri_nastan bsn on bsn.nastan_id = n.nastan_id 
+        left join bendovi as b on b.korisnik_id = bsn.korisnik_id 
+        left join korisnici kb on kb.korisnik_id = b.korisnik_id 
+        
+        left join ketering_rezervira_nastan krn on krn.nastan_id = n.nastan_id 
+        left join keterinzi as k on k.korisnik_id = krn.korisnik_id 
+        left join korisnici kk on kk.korisnik_id = k.korisnik_id 
+        
+        left join fotograf_slika_nastan fsn on fsn.nastan_id = n.nastan_id
+        left join fotografi as f on f.korisnik_id = fsn.korisnik_id 
+        left join korisnici kf on kf.korisnik_id = f.korisnik_id 
+        
+        where extract(month from n.datum) = extract(month from now() - interval '1 month') and extract(year from n.datum) = extract(year from now()- interval '1 month')
+        group by 1,2,3,4,5,6
+
+) as prethoden_mesec on n.nastan_id = prethoden_mesec.nastan_id and n.tip = prethoden_mesec.tip_nastan
+
+left join( 
+        select n.nastan_id as nastan_id, n.datum as nastan_datum, n.tip as tip_nastan, kb.ime as ime_bend, kk.ime as ime_ketering, kf.ime as ime_fotograf, count (*)  as broj_nastani
+        from nastani as n 
+        left join bend_sviri_nastan bsn on bsn.nastan_id = n.nastan_id 
+        left join bendovi as b on b.korisnik_id = bsn.korisnik_id 
+        left join korisnici kb on kb.korisnik_id = b.korisnik_id 
+        
+        left join ketering_rezervira_nastan krn on krn.nastan_id = n.nastan_id 
+        left join keterinzi as k on k.korisnik_id = krn.korisnik_id 
+        left join korisnici kk on kk.korisnik_id = k.korisnik_id 
+        
+        left join fotograf_slika_nastan fsn on fsn.nastan_id = n.nastan_id
+        left join fotografi as f on f.korisnik_id = fsn.korisnik_id 
+        left join korisnici kf on kf.korisnik_id = f.korisnik_id 
+        
+        where extract(month from n.datum) = extract(month from now() - interval '2 month') and extract(year from n.datum) = extract(year from now()- interval '2 month')
+        group by 1,2,3,4,5,6
+) as predprethoden_mesec on n.nastan_id = predprethoden_mesec.nastan_id and n.tip = predprethoden_mesec.tip_nastan
+ }}}
+
+
+=== Извештај за број на типови на алкохолни пијалоци конзумирани за секој настан заедно со името на клиентот кој го закажал,кетеринг групата која служела и бројот на келнери сортирани според датумот во растечки редослед ===
+
+{{{#!sql
+select n.nastan_id, n.datum, lista_klienti.ime as ime_klient, lista_keterinzi.ime as ime_ketering, count(lista_pijaloci) as broj_alkoholni_pijaloci, lista_keterinzi.kolicina as broj_kelneri
+from nastani as n
+
+join (
+        select * from korisnici as k 
+        join klienti as kl on k.korisnik_id = kl.korisnik_id_korisnici 
+) as lista_klienti on n.korisnik_id_klienti = lista_klienti.korisnik_id
+
+join(
+        select * from korisnici as k
+        join keterinzi as ke on k.korisnik_id = ke.korisnik_id 
+        join ketering_rezervira_nastan as krn on ke.korisnik_id = krn.korisnik_id  
+) as lista_keterinzi on n.nastan_id = lista_keterinzi.nastan_id
+
+join (
+        select * from korisnici as k 
+        join keterinzi as ke on k.korisnik_id = ke.korisnik_id 
+        join ketering_nudi_produkt as knp on ke.korisnik_id = knp.korisnik_id
+        join produkti as p on knp.produkt_id = p.produkt_id 
+        join pijaloci as pij on p.produkt_id = pij.produkt_id 
+        join ketering_rezervira_nastan as krn on ke.korisnik_id = krn.korisnik_id  
+) as lista_pijaloci on n.nastan_id = lista_pijaloci.nastan_id
+
+where lista_pijaloci.dali_alkoholen = true and n.datum between now()- interval '12 months' and now ()
+group by 1,2,3,4,6
+order by 2 asc
+}}}