Daily Index Query - Zone car Half

set @start := '2019-02-01 00:00:00';
set @end := '2019-04-31 23:59:59';


select
cz.zid,
cz.zname,
cz.class,
cz.ctype,
cz.cid,
#count(cz.ncars2) as cars2,
cz.ncars,
ifnull(sum(rm.nuse),0) as nuse,
ifnull(sum(rm.rnd),0) as round,
ifnull(sum(rm.d2d),0) as d2d,
ifnull(sum(rm.ncar_used),0) as ncar_used,
cz.nzone,
ifnull(sum(rm.nmember),0) as memeber,
ifnull(rm.rmil,0) as rmil,
ifnull(sum(rm.dur),0) as dur,
ifnull(sum(rm.revenues),0) as rrevenues,
ifnull(sum(rm.coupons),0) as couponrev,
ifnull(sum(rm.NR),0) as NR,
ifnull(count(rm.MFT),0) as MFT,
ifnull(sum(rm.ActiveMembers),0) as MAU


from
(select cz.zone_id as zid,
z.region as region,
z.city as team,
z.name as zname,
cz.car_id as cid,
c.registration_no as plate,
c.car_class_id as class,
(select car_classes.car_name from car_classes where car_classes.id=c.car_class_id) as ctype,
count(cz.id) as op_day,
#count(z.id) as nzone,
count(distinct case when z.state = 'normal' then z.id end) as nzone,
count(cz.id)  as ncars,
#count(distinct case when cz.zone_state = 'normal' then cz.id end) as ncars2,
date_sub(cz.log_date, interval 1 day) as czdate
from car_zone_logs cz, zones z, cars c
where cz.zone_id=z.id
and cz.car_id=c.id
and z.id not in (2,3,101,383,378,697,712,818)
and date_sub(cz.log_date+interval 8 hour, interval 1 day) between (@start) and (@end)
group by cid, zid
order by cid
)cz
left join
(select
 r.car_id as cid2,
 r.start_zone_id as zid2,
 count(r.id) as nuse,
 count(if(r.way='round', r.id, null)) as rnd,
 count(if(r.way='d2d', r.id, null)) as d2d,
 count(if(r.way='oneway', r.id, null)) as oneway,
 count(if(r.way='onewayreturn', r.id, null)) as onewayreturn,
 count(distinct case when r.state = 'completed' then r.member_id end) as ActiveMembers,
 count(distinct r.car_id) as ncar_used,
 count(distinct r.member_id) as nmember,
 ifnull(sum(ra.mileage),0) as rmil,
 sum(timestampdiff(hour, r.start_at, r.end_at)) as dur,
 ifnull((select count(distinct case when charges.kind = 'subscriptionFee' then charges.member_id end) from charges where member_id = m.id),0) as MFT,
 ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','d2d','mileage')),0) as rent,
 ifnull((select sum(amount) from payments where state='normal' and r.id=reservation_id and paid_type='coupon'),0) as paid_coupon,
 round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','oneway','d2d','mileage')),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,
 sum((ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','oneway','d2d','mileage')),0)- ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0))) as NR,
 date_format(convert_tz(r.return_at, '+00:00', '+8:00'),'%Y-%m-%d') as czdate2
from reservations r, members m , reservation_appendixes ra
where
r.member_id=m.id
and r.id = ra.reservation_id
and m.imaginary in ('normal','sofam')
and r.way in ('round','d2d','oneway')
and r.state = 'completed'
and r.return_at+interval 8 hour between @start and @end
group by cid2, zid2, czdate2
order by cid2
) rm
on ( cz.zid=rm.zid2 and cz.cid=rm.cid2)
group by cz.zid, cz.cid
order by cz.zid, op_day desc, cz.cid;