Changes between Version 2 and Version 3 of DatabaseReports

Timestamp:

05/18/21 22:06:53 (3 years ago)

Author:

171510

Comment:

--

DatabaseReports

@@ -1 +1 @@
 == Напредни извештаи од базата
+
+1. Листа на вработени кои имаат направено барем едно отсуство, а не се пријавени во ниту еден оддел, а фирмата во која се вработени има барем еден оддел.
+
+{{{#!sql
+select t1.v_id, t1.v_ime, t1.v_prezime, t1.v_telefon from vraboteni as t1
+join (
+        select distinct v_id from otsustva as o
+        where exists(
+                select v_id, f_id from vraboteni as v
+                where not exists (select v_id from vraboten_vo_oddel as vvo where v_id=v.v_id)
+                and exists (select distinct f_id from oddeli as o2 where f_id=v.f_id)
+                and o.v_id=v.v_id)
+        ) as t2 on t1.v_id=t2.v_id
+where t1.v_aktiven=1 and t1.f_id=1
+}}}
+
+2. Листа на фирми со обем на внесени податоци(вкупно отсуства по сите вработени). Овој извештај служи за администраторот да има евиденција која фирма има и може да ги шире податоците.
+
+{{{#!sql
+Zoran sent 7 May at 13:42
+select t1.f_id, t1.f_ime, t1.f_grad, 
+(case when t2.vkupno_otsustva is null then 0 else t2.vkupno_otsustva end) *
+(case when koef_vraboteni is null then 1 else koef_vraboteni end) * koef_oddeli as count_data 
+from firmi as t1
+left join(
+        select f.f_id, sum(1) as vkupno_otsustva from firmi as f
+        join vraboteni as v on v.f_id=f.f_id
+        join otsustva as o on o.v_id=v.v_id
+        group by f.f_id) as t2 on t2.f_id=t1.f_id
+left join (
+        select f_id, (case when sum(1)<50 then 1 else (case when sum(1)<100 then 2 else 3 end) end) 
+        as koef_vraboteni from vraboteni as v
+        group by f_id) as t3 on t3.f_id=t1.f_id
+left join (
+        select f.f_id, (case when sum(1) is null then 1 else sum(1) end) as koef_oddeli from firmi as f
+        left join oddeli as o on o.f_id=f.f_id
+        group by f.f_id) as t4 on t4.f_id=t1.f_id
+order by count_data desc
+}}}