Changes between Version 8 and Version 9 of AdvancedReports

Timestamp:

01/18/23 00:05:44 (2 years ago)

Author:

201087

Comment:

--

AdvancedReports

@@ -16 +16 @@
 }}}
 
-* Објавите на газдите и која е разликата меѓу цената објавена и цената потпишана за станот
+* Извештај за сите студенти и сите места каде имаат седено порано (доколку имале седено), кој бил газдата, колку вкупно платиле, и периодот на договорот подредено по име на студент и датум.
 {{{
-select au.ime,au.prezime,p.cityname as city_posted_for,p.price as posted_price, aa.apartment_rent as signed_price, abs(p.price-aa.apartment_rent) as difference from apartment_agreement as aa
+select au.ime as student_ime,au.prezime as student_prezime, aa.apartment_rent as rent, aa.apartment_rent * extract(month from age(aa.date_expires,aa.date_signed)) as total_paid,
+appu.ime as owner_ime, appu.prezime as owner_prezime, aa.date_signed, aa.date_expires  from student s 
+join appuser au on au.userid=s.studentid 
+left join student_signs ss on ss.studentid = s.studentid 
+left join apartment_agreement aa on aa.apartment_agreementid=ss.agreementid 
+join apartment_owner ao on ao.ownerid=aa.ownerid 
+join appuser appu on appu.userid = ao.ownerid 
+order by au.ime,aa.date_signed
+}}}
+
+* Објавите на газдите и која е разликата меѓу цената објавена и цената потпишана за станот, и колку вкупно имаат заработено во тој договор.
+{{{
+select au.ime,au.prezime,p.cityname as city_posted_for,p.price as posted_price, aa.apartment_rent as signed_price, abs(p.price-aa.apartment_rent) as difference, aa.apartment_rent * extract(month from age(aa.date_expires,aa.date_signed)) as total_euros_earned from apartment_agreement as aa
 join apartment_owner ao on ao.ownerid=aa.ownerid 
 join appuser au on au.userid = ao.ownerid 
@@ -24 +36 @@
 join owner_post op on op.ownerid  = ao.ownerid  
 join post p on p.postid = op.owner_postid 
-order by au.ime,difference
+order by au.ime,difference, total_euros_earned 
 }}}
 
-* Извештај за станови во системот кои не се моментално издадени
+* Извештај за станови во системот кои не се моментално издадени и нивните газди соодветно, и доколку биле издадени порано да се покаже кој седел во нив
 {{{
-select apt.apartmentid,concat(b.cityname,', ',b.building_adress,' ',apt.apartment_adress) as adresa, a.ime, a.prezime, a.phone_number from apartment as apt
+select distinct apart.apartmentid,apart.adresa as adresa, apart.ime, apart.prezime, apart.phone_number, au.ime,au.prezime,aa2.date_signed,aa2.date_expires from student s 
+join student_signs ss on ss.studentid = s.studentid 
+join appuser au on au.userid = s.studentid 
+join apartment_agreement aa2 on aa2.apartment_agreementid = ss.agreementid 
+right join 
+(
+select distinct apt.apartmentid,concat(b.cityname,', ',b.building_adress,' ',apt.apartment_adress) as adresa, a.ime, a.prezime, a.phone_number from apartment as apt
 left join apartment_agreement aa on aa.apartmentid = apt.apartmentid 
 join apartment_owner ao on ao.ownerid = apt.ownerid
 join appuser as a on ao.ownerid = a.userid
-join building b on apt.buildingid = b.buildingid 
-where now()>aa.date_expires  or aa.apartment_agreementid isnull
+join building b on apt.buildingid = b.buildingid
+) apart on apart.apartmentid=aa2.apartmentid 
+where now()>aa2.date_expires or aa2.date_expires isnull 
 }}}