F1- New Daily Index File share tracker

#With all factors ###############################################################################################################################

set @startdate1 = date_sub(CURDATE(), interval 10 day);
set @startdate2 = date_add(CURDATE(), interval 10 day);
use socar_malaysia;

select
A.date,
A.ncars,
A.nuse,
A.nuse_rnd,
A.res_d2d,
A.res_oneway,
B.ncars_used,
B.ncar_returned,
A.nzone,
A.nmem,
A.rmil,
A.dur,
A.ad_sdur,
A.revenues,
A.coupons,
A.nr
from
(select date_format(convert_tz(r.return_at, '+00:00', '+8:00'),'%Y-%m-%d') as date,
(select count(czl.id) from car_zone_logs czl where date=date_format(czl.log_date+interval 8 hour, "%Y-%m-%d") and czl.zone_state='normal') as ncars,
count(r.id) as nuse,
count(if(r.way='round', r.id, null)) as nuse_rnd,
count(if(r.way='d2d', r.id, null)) as res_d2d,
count(if(r.way in('oneway','onewayReturn' ), r.id, null)) as res_oneway,
count(distinct r.car_id) as ncar_used,
count(distinct z.id) as nzone,
count(distinct m.id) as nmem,
#count(if(r.way='oneway', r.id, null)) as res_ow,
sum(ra.mileage) as rmil,
round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
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,
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,
round(sum(ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0)),2) as coupons,
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)
- ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0)),2) as nr


from reservations r, zones z, members m, reservation_appendixes ra

Where
    r.start_zone_id = z.id
    and r.member_id = m.id
    and r.id = ra.reservation_id
    and m.imaginary in ('sofam', 'normal')
    and r.member_id not in ('125', '127')
    and z.id not in (2, 3, 101)
    and r.state in ('completed')
    and r.return_at + interval 8 hour >= @startdate1
    and r.return_at + interval 8 hour <= @startdate2

group by date
order by date desc) A

left join

((select temp.rday as date,
  (select count(czl.id) from car_zone_logs czl where rday=date_format(czl.log_date, "%Y-%m-%d") and czl.zone_state='normal') as ncars,
  temp.ncars_used,
  (select count(distinct car_id) from reservations r, members m
      where r.state = 'completed'
        and date_format(r.return_at+interval 8 hour, "%Y-%m-%d") = temp.rday
        and r.member_id=m.id
        and m.imaginary in ('sofam', 'normal')
        and r.start_zone_id not in (2,3,101)
  ) ncar_returned
from (
  select rday,count(distinct cid) as ncars_used
  from (select date_format(dt.rday, "%Y-%m-%d") as rday, resv.rid as rid, resv.cid as cid, resv.rsh as rsh, resv.reh as reh
    from (select concat(date_format(r.return_at+interval 8 hour, "%Y-%m-%d"), " 00:00:00") as rday from reservations r
      where r.return_at is not null and r.return_at+interval 8 hour > @startdate1
      group by rday
    ) dt,
    (select r.id as rid, r.car_id as cid, r.start_at+interval 8 hour as rsh, r.end_at+interval 8 hour as reh
      from reservations r, members m
      where r.member_id=m.id
        and m.imaginary in ('sofam', 'normal')
        and r.state in ('completed', 'inuse')
        and r.start_zone_id not in (2,3,101)
        and r.end_at+interval 8 hour >= @startdate1
    ) resv
where resv.rsh < (dt.rday+interval 1 day) and resv.reh > dt.rday) temp
group by rday
order by rday desc) temp)) B
on A.Date = B.Date
group by A.date
order by A.date desc
;

#####Only reservation Related################################################################################################################################

set @startdate1 = date_sub(CURDATE(), interval 10 day);
set @startdate2 = date_add(CURDATE(), interval 10 day);
use socar_malaysia;

select
A.date,
A.ncars,
A.nuse,
A.nuse_rnd,
A.res_d2d,
A.res_oneway,
B.ncars_used,
B.ncar_returned,
A.nzone,
A.nmem,
A.rmil,
A.dur,
A.ad_sdur,
A.revenues,
A.coupons,
A.nr
from
(select date_format(convert_tz(r.return_at, '+00:00', '+8:00'),'%Y-%m-%d') as date,
(select count(czl.id) from car_zone_logs czl where date=date_format(czl.log_date+interval 8 hour, "%Y-%m-%d") and czl.zone_state='normal') as ncars,
count(r.id) as nuse,
count(if(r.way='round', r.id, null)) as nuse_rnd,
count(if(r.way='d2d', r.id, null)) as res_d2d,
count(if(r.way in('oneway','onewayReturn' ), r.id, null)) as res_oneway,
count(distinct r.car_id) as ncar_used,
count(distinct z.id) as nzone,
count(distinct m.id) as nmem,
#count(if(r.way='oneway', r.id, null)) as res_ow,
sum(ra.mileage) as rmil,
round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
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,
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,
round(sum(ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0)),2) as coupons,
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)
- ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0)),2) as nr


from reservations r, zones z, members m, reservation_appendixes ra

Where
    r.start_zone_id = z.id
    and r.member_id = m.id
    and r.id = ra.reservation_id
    and m.imaginary in ('sofam', 'normal')
    and r.member_id not in ('125', '127')
    and z.id not in (2, 3, 101)
    and r.state in ('completed')
    and r.return_at + interval 8 hour >= @startdate1
    and r.return_at + interval 8 hour <= @startdate2

group by date
order by date desc) A

left join

((select temp.rday as date,
  (select count(czl.id) from car_zone_logs czl where rday=date_format(czl.log_date, "%Y-%m-%d") and czl.zone_state='normal') as ncars,
  temp.ncars_used,
  (select count(distinct car_id) from reservations r, members m
      where r.state = 'completed'
        and date_format(r.return_at+interval 8 hour, "%Y-%m-%d") = temp.rday
        and r.member_id=m.id
        and m.imaginary in ('sofam', 'normal')
        and r.start_zone_id not in (2,3,101)
  ) ncar_returned
from (
  select rday,count(distinct cid) as ncars_used
  from (select date_format(dt.rday, "%Y-%m-%d") as rday, resv.rid as rid, resv.cid as cid, resv.rsh as rsh, resv.reh as reh
    from (select concat(date_format(r.return_at+interval 8 hour, "%Y-%m-%d"), " 00:00:00") as rday from reservations r
      where r.return_at is not null and r.return_at+interval 8 hour > @startdate1
      group by rday
    ) dt,
    (select r.id as rid, r.car_id as cid, r.start_at+interval 8 hour as rsh, r.end_at+interval 8 hour as reh
      from reservations r, members m
      where r.member_id=m.id
        and m.imaginary in ('sofam', 'normal')
        and r.state in ('completed', 'inuse')
        and r.start_zone_id not in (2,3,101)
        and r.end_at+interval 8 hour >= @startdate1
    ) resv
where resv.rsh < (dt.rday+interval 1 day) and resv.reh > dt.rday) temp
group by rday
order by rday desc) temp)) B
on A.Date = B.Date
group by A.date
order by A.date desc
;