Changes between Version 20 and Version 21 of AdvancedReports

Timestamp:

01/10/23 16:08:10 (2 years ago)

Author:

201063

Comment:

--

AdvancedReports

@@ -48 +48 @@
 
 == Прашалник 2
-(text here)
-{{{#!sql
---sql here
+За секое посвојување, идентификациски број, почетен и краен датум и статус. За посвојувачот се прикажуваат името, електронската пошта и телефонскиот број. За посвојувањето се пресметуваат број на миленици, број на различни видови (мачки, кучиња, птици) и севкупен број на терапии и интервенции потребни на сите миленици.
+{{{#!sql
+select 
+        q2.id_adoption as id,
+        q2.start_date,
+        q2.end_date_foster,
+        q2.status,
+        user_table.name_user as adopter_name,
+        user_table.email_user as adopter_email,
+        user_table.telephone_user as adopter_phone,
+        q2.number_of_pets,
+        q2.number_of_species,
+        q2.total_therapies,
+        q2.total_interventions
+from 
+        (
+                select 
+                        adoption.id_adoption,
+                        adoption.start_date,
+                        adoption.end_date_foster,
+                        case 
+                        when adoption.approved = false then 'under review'
+                        when adoption.approved = true then 'approved'
+                        end 
+                        as status,
+                    count(distinct q1.id_pet) as number_of_pets,
+                    count(distinct q1.species) as number_of_species,
+                    sum(q1.therapies) as total_therapies,
+                    sum(q1.interventions) as total_interventions
+                from
+                        (
+                                select 
+                                        pet.id_pet,
+                                        pet.name_pet,
+                                        pet.id_adoption,
+                                        pet.species,
+                                    count(distinct pnt.id_therapy) as therapies,
+                                    count(distinct description) as interventions
+                                from 
+                                        pet
+                                left join
+                                        pet_needs_therapy pnt
+                                        on pnt.id_pet = pet.id_pet 
+                                left join 
+                                        pet_needs_intervention_in_vet_clinic pni
+                                        on pni.id_pet = pet.id_pet 
+                                group by 
+                                        pet.id_pet
+                        ) as q1
+                right join 
+                        adoption
+                        on q1.id_adoption = adoption.id_adoption 
+                group by 
+                        adoption.id_adoption
+        ) as q2
+left join       
+        adoption
+        on adoption.id_adoption = q2.id_adoption
+left join 
+        adopter 
+        on adopter.id_user = adoption.id_adopter 
+left join 
+        user_table
+        on user_table.id_user = adopter.id_user 
 }}}