Changes between Version 2 and Version 3 of AdvancedReports

Timestamp:

02/03/22 03:22:13 (4 years ago)

Author:

193014

Comment:

--

AdvancedReports

@@ -1 +1 @@
 = Напредни извештаи од базата (SQL и складирани процедури) 
+
+=== Извештај за секој салон со бројот на вработени, термини,резервации и вкупната заработувачка
+
+
+{{{
+select distinct bc.salon_id , br_vraboteni.broj_vraboteni, br_termini.broj_termini, br_rezervacii.broj_rez, zarabotuvacka.vkupno
+       
+  from beautycenter as bc 
+
+left join 
+(       select bc.salon_id, count(v_bc.user_id_vraboten) as broj_vraboteni
+                 from beautycenter as bc 
+                 left join vraboteni_beautycenter as v_bc on bc.salon_id = v_bc.salon_id
+                 group by bc.salon_id
+  ) as br_vraboteni on bc.salon_id = br_vraboteni.salon_id
+  
+left join 
+(       select bc.salon_id, count(t.termin_id) as broj_termini
+                 from beautycenter as bc 
+                 left join vraboteni_beautycenter as v_bc on bc.salon_id = v_bc.salon_id
+                  left join vraboteni as v on v_bc.user_id_vraboten = v.user_id
+                  left join termini as t on v.user_id = t.user_id_vraboten
+                 group by bc.salon_id
+  ) as br_termini on bc.salon_id = br_termini.salon_id
+ 
+  left join 
+(       select bc.salon_id, count(r.br_rez) as broj_rez
+                 from beautycenter as bc 
+                 left join vraboteni_beautycenter as v_bc on bc.salon_id = v_bc.salon_id
+                  left join vraboteni as v on v_bc.user_id_vraboten = v.user_id
+                  left join termini as t on v.user_id = t.user_id_vraboten
+                  left join rezervacija as r on t.termin_id = r.termin_id
+                 group by bc.salon_id
+  ) as br_rezervacii on bc.salon_id = br_rezervacii.salon_id
+     
+  left join 
+(       select bc.salon_id,u.usluga_id, count(ru.usluga_id)*cast(u.cena as numeric)  suma_ceni
+                 from beautycenter as bc 
+                 left join vraboteni_beautycenter as v_bc on bc.salon_id = v_bc.salon_id
+                  left join vraboteni as v on v_bc.user_id_vraboten = v.user_id
+                  left join termini as t on v.user_id = t.user_id_vraboten
+                  left join rezervacija as r on t.termin_id = r.termin_id
+                  left join rezervacija_uslugi as ru on r.br_rez = ru.br_rez
+                  left join uslugi as u on ru.usluga_id = u.usluga_id
+                 group by bc.salon_id,u.usluga_id,u.cena
+  ) as suma_uslugi on bc.salon_id = suma_uslugi.salon_id
+
+  left join 
+(       select bc.salon_id, sum(suma_uslugi.suma_ceni) vkupno
+                 from beautycenter as bc 
+                  left join (
+                     select bc.salon_id,u.usluga_id, count(ru.usluga_id)*cast(u.cena as numeric)  suma_ceni
+                     from beautycenter as bc 
+                     left join vraboteni_beautycenter as v_bc on bc.salon_id = v_bc.salon_id
+                     left join vraboteni as v on v_bc.user_id_vraboten = v.user_id
+                     left join termini as t on v.user_id = t.user_id_vraboten
+                     left join rezervacija as r on t.termin_id = r.termin_id
+                     left join rezervacija_uslugi as ru on r.br_rez = ru.br_rez
+                     left join uslugi as u on ru.usluga_id = u.usluga_id
+                    group by bc.salon_id,u.usluga_id,u.cena
+                  ) as suma_uslugi on bc.salon_id = suma_uslugi.salon_id
+                 group by bc.salon_id
+  ) as zarabotuvacka on bc.salon_id = zarabotuvacka.salon_id
+
+}}}
+
 
 === Број на вработени за дадена дејност. 
@@ -57 +123 @@
 
 ( 
-   select count(usluga_id)*cast(u.cena as numeric)  broj_rezervacii
+   select count(usluga_id)*cast(u.cena as numeric)  suma_ceni
    from rezervacija_uslugi  
    group by usluga_id