API tools faq
paste
ahmedrahil786

New Monthly Index Dashboard - Rahil

ahmedrahil786
Dec 3rd, 2019 (edited)
308
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 8.20 KB | None | 0 0
raw download clone embed print report
  1. ##All charges ###############################################################################################################################
  2.  
  3. set @startdate1 = '2021-01-01';
  4. set @startdate2 = '2021-06-01';
  5. use socar_malaysia;
  6.  
  7. Select A.month,
  8. B.totalcars as ncars,
  9. A.nuse,
  10. A.nuse_rnd,
  11. A.res_d2d,
  12. A.res_oneway,
  13. A.ncar_used,
  14. A.nzone,
  15. A.nmem,
  16. A.rmil,
  17. A.dur,
  18. A.ad_sdur,
  19. A.revenues,
  20. A.coupons,
  21. A.nr
  22. from
  23.  
  24.  
  25. (select concat(month(convert_tz(r.return_at, '+00:00', '+8:00')),"-",year(convert_tz(r.return_at, '+00:00', '+8:00'))) as month,
  26. month(convert_tz(r.return_at, '+00:00', '+8:00')) as month2,
  27. year(convert_tz(r.return_at, '+00:00', '+8:00')) as year,
  28. (select count( czl.id) from car_zone_logs czl where month=month(czl.log_date+interval 8 hour) and czl.zone_state='normal') as ncars,
  29. count(r.id) as nuse,
  30. count(if(r.way='round', r.id, null)) as nuse_rnd,
  31. count(if(r.way='d2d', r.id, null)) as res_d2d,
  32. count(if(r.way in('oneway','onewayReturn' ), r.id, null)) as res_oneway,
  33. count(distinct r.car_id) as ncar_used,
  34. count(distinct z.id) as nzone,
  35. count(distinct m.id) as nmem,
  36. #count(if(r.way='oneway', r.id, null)) as res_ow,
  37. sum(ra.mileage) as rmil,
  38. round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
  39. round(sum(floor(timestampdiff(minute, r.start_at, r.end_at)/60/24)*12+if(mod(timestampdiff(hour, r.start_at, r.end_at),24)>=12,12,mod(timestampdiff(minute, r.start_at, r.end_at)/60,24))),2) as ad_sdur,
  40. round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','oneway','d2d','mileage','insurance','penalty','fine','reservationFee','cancelFee','parkingFee','etc','accident','chauffeur','fuel')),0)),2) as revenues,
  41. round(sum(ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0)),2) as coupons,
  42. round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','oneway','d2d','mileage','insurance','penalty','fine','reservationFee','cancelFee','parkingFee','etc','accident','chauffeur','fuel')),0)
  43. - ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0)),2) as nr
  44.  
  45.  
  46. from reservations r, zones z, members m, reservation_appendixes ra
  47.  
  48. Where
  49.     r.start_zone_id = z.id
  50.     and r.member_id = m.id
  51.     and r.id = ra.reservation_id
  52.     and m.imaginary in ('sofam', 'normal')
  53.     and r.member_id not in ('125', '127')
  54.     and z.id not in (2, 3, 101)
  55.     and r.state in ('completed')
  56.     and r.return_at + interval 8 hour >= @startdate1
  57.     and r.return_at + interval 8 hour <= @startdate2
  58.  
  59. group by month
  60. order by month desc) A
  61.  
  62. join
  63. (select
  64.  rmonth2 as month2, ncars as ncars, wtotal_ncars as totalcars
  65.  
  66. from (
  67.   select rmonth2,
  68.     (select count(cz.id) from car_zone_logs cz, zones z
  69.       where cz.zone_id=z.id and z.id not in (2,3,101,383,378,712,818,786) and date_format(date_sub(cz.log_date, interval 1 day)+interval 8 hour, "%Y-%m-%d")=mlast
  70.     ) as ncars,
  71.     (select count(cz.id) from car_zone_logs cz, zones z
  72.       where cz.zone_id=z.id and z.id not in (2,3,101,383,378,712,818,786) and date_sub(cz.log_date, interval 1 day)+interval 8 hour >= mfirst and date_sub(cz.log_date, interval 1 day)+interval 8 hour < mlast+interval 1 day
  73.     ) as wtotal_ncars
  74.  
  75.   from (
  76.  
  77.     select
  78.      rmonth2, min(date_format(rret,"%Y-%m-%d")) as mfirst, max(date_format(rret,"%Y-%m-%d")) as mlast
  79.  
  80.     from (
  81.       select
  82.         month(r.return_at+interval 8 hour) as rmonth2,
  83.         r.start_at+interval 8 hour as rst, r.end_at+interval 8 hour as rend, r.return_at+interval 8 hour as rret
  84.              from reservations r, members m, zones z
  85.       where
  86.         r.state='completed'
  87.         and r.member_id=m.id
  88.         and m.imaginary in ('normal', 'sofam')
  89.         and r.start_zone_id=z.id
  90.         and r.start_zone_id not in (2,3,101,383,378,697,712,818,786)
  91.         and r.return_at + interval 8 hour >= @startdate1
  92.         and r.return_at + interval 8 hour <= @startdate2
  93.     ) temp
  94.     group by rmonth2
  95.   ) temp
  96.   group by rmonth2
  97. ) temp
  98. group by rmonth2) B
  99.  
  100. on A.month2 = B.month2
  101.  
  102. order by  A.year desc, A.month2 desc;
  103.  
  104.  
  105. ##Only Rental ###############################################################################################################################
  106.  
  107. set @startdate1 = '2021-01-01';
  108. set @startdate2 = '2021-06-01';
  109. use socar_malaysia;
  110.  
  111. Select A.month,
  112. B.totalcars as ncars,
  113. A.nuse,
  114. A.nuse_rnd,
  115. A.res_d2d,
  116. A.res_oneway,
  117. A.ncar_used,
  118. A.nzone,
  119. A.nmem,
  120. A.rmil,
  121. A.dur,
  122. A.ad_sdur,
  123. A.revenues,
  124. A.coupons,
  125. A.nr
  126. from
  127.  
  128.  
  129. (select concat(month(convert_tz(r.return_at, '+00:00', '+8:00')),"-",year(convert_tz(r.return_at, '+00:00', '+8:00'))) as month,
  130. month(convert_tz(r.return_at, '+00:00', '+8:00')) as month2,
  131. year(convert_tz(r.return_at, '+00:00', '+8:00')) as year,
  132. (select count( czl.id) from car_zone_logs czl where month=month(czl.log_date+interval 8 hour) and czl.zone_state='normal') as ncars,
  133. count(r.id) as nuse,
  134. count(if(r.way='round', r.id, null)) as nuse_rnd,
  135. count(if(r.way='d2d', r.id, null)) as res_d2d,
  136. count(if(r.way in('oneway','onewayReturn' ), r.id, null)) as res_oneway,
  137. count(distinct r.car_id) as ncar_used,
  138. count(distinct z.id) as nzone,
  139. count(distinct m.id) as nmem,
  140. #count(if(r.way='oneway', r.id, null)) as res_ow,
  141. sum(ra.mileage) as rmil,
  142. round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
  143. round(sum(floor(timestampdiff(minute, r.start_at, r.end_at)/60/24)*12+if(mod(timestampdiff(hour, r.start_at, r.end_at),24)>=12,12,mod(timestampdiff(minute, r.start_at, r.end_at)/60,24))),2) as ad_sdur,
  144. round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','oneway','d2d','mileage','insurance','chauffeur','fuel')),0)),2) as revenues,
  145. round(sum(ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0)),2) as coupons,
  146. round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','oneway','d2d','mileage','insurance','chauffeur','fuel')),0)
  147. - ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0)),2) as nr
  148.  
  149.  
  150. from reservations r, zones z, members m, reservation_appendixes ra
  151.  
  152. Where
  153.     r.start_zone_id = z.id
  154.     and r.member_id = m.id
  155.     and r.id = ra.reservation_id
  156.     and m.imaginary in ('sofam', 'normal')
  157.     and r.member_id not in ('125', '127')
  158.     and z.id not in (2, 3, 101)
  159.     and r.state in ('completed')
  160.     and r.return_at + interval 8 hour >= @startdate1
  161.     and r.return_at + interval 8 hour <= @startdate2
  162.  
  163. group by month
  164. order by month desc) A
  165.  
  166. join
  167. (select
  168.  rmonth2 as month2, ncars as ncars, wtotal_ncars as totalcars
  169.  
  170. from (
  171.   select rmonth2,
  172.     (select count(cz.id) from car_zone_logs cz, zones z
  173.       where cz.zone_id=z.id and z.id not in (2,3,101,383,378,712,818,786) and date_format(date_sub(cz.log_date, interval 1 day)+interval 8 hour, "%Y-%m-%d")=mlast
  174.     ) as ncars,
  175.     (select count(cz.id) from car_zone_logs cz, zones z
  176.       where cz.zone_id=z.id and z.id not in (2,3,101,383,378,712,818,786) and date_sub(cz.log_date, interval 1 day)+interval 8 hour >= mfirst and date_sub(cz.log_date, interval 1 day)+interval 8 hour < mlast+interval 1 day
  177.     ) as wtotal_ncars
  178.  
  179.   from (
  180.  
  181.     select
  182.      rmonth2, min(date_format(rret,"%Y-%m-%d")) as mfirst, max(date_format(rret,"%Y-%m-%d")) as mlast
  183.  
  184.     from (
  185.       select
  186.         month(r.return_at+interval 8 hour) as rmonth2,
  187.         r.start_at+interval 8 hour as rst, r.end_at+interval 8 hour as rend, r.return_at+interval 8 hour as rret
  188.              from reservations r, members m, zones z
  189.       where
  190.         r.state='completed'
  191.         and r.member_id=m.id
  192.         and m.imaginary in ('normal', 'sofam')
  193.         and r.start_zone_id=z.id
  194.         and r.start_zone_id not in (2,3,101,383,378,697,712,818,786)
  195.         and r.return_at + interval 8 hour >= @startdate1
  196.         and r.return_at + interval 8 hour <= @startdate2
  197.     ) temp
  198.     group by rmonth2
  199.   ) temp
  200.   group by rmonth2
  201. ) temp
  202. group by rmonth2) B
  203.  
  204. on A.month2 = B.month2
  205.  
  206. order by  A.year desc, A.month2   desc;
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!