Changes between Version 32 and Version 33 of AdvancedReports

Timestamp:

01/11/23 10:54:49 (2 years ago)

Author:

201235

Comment:

--

AdvancedReports

@@ -157 +157 @@
 
 == Извештај 4
-(text here)
-{{{#!sql
---sql here
+За секој посвојувач, вкупен број на миленици од прифатилиште и вкупен број на миленици огласени од граѓани. За посвојувачите се прикажуваат име презиме и контакт информации (електронска пошта и телефон).
+{{{#!sql
+select 
+        q3.id_user,
+        user_table.name_user,
+        user_table.email_user,
+        user_table.telephone_user,
+        q3.pets_from_surendee,
+        q3.pets_from_shelter
+from 
+        (
+                select 
+                        adopter.id_user,
+                        sum(q1.surendee_count) as pets_from_surendee,
+                        sUm(q2.shelter_count) as pets_from_shelter
+                from 
+                        adopter
+                left join
+                        (
+                                select
+                                        adoption.id_adoption,
+                                        adoption.id_adopter,
+                                        count(surendee.id_user) as surendee_count
+                                from    
+                                        adoption
+                                left join
+                                        pet 
+                                        on pet.id_adoption = adoption.id_adoption 
+                                left join
+                                        post
+                                        on post.id_pet = pet.id_pet 
+                                left join 
+                                        surendee
+                                        on surendee.id_user = post.id_surendee 
+                                group by 
+                                        adoption.id_adoption
+                        ) as q1
+                        on q1.id_adopter = adopter.id_user 
+                left join 
+                        (
+                                select
+                                        adoption.id_adoption,
+                                        adoption.id_adopter,
+                                        count(employee.id_user) as shelter_count
+                                from    
+                                        adoption
+                                left join
+                                        pet 
+                                        on pet.id_adoption = adoption.id_adoption 
+                                left join
+                                        post
+                                        on post.id_pet = pet.id_pet 
+                                left join 
+                                        employee
+                                        on employee.id_user = post.id_employee
+                                group by 
+                                        adoption.id_adoption
+                        ) as q2
+                        on q2.id_adopter = adopter.id_user
+                group by 
+                        adopter.id_user 
+                order by 
+                        adopter.id_user 
+        ) as q3
+left join 
+        user_table
+        on user_table.id_user = q3.id_user
 }}}