Dashboard for Leon - NRPCPD, MFT and MAU / Region

set @startdate = date_sub(CURDATE(), interval 1 day);
set @startdate2 = date_sub(@startdate, interval 1 month);
select
date_format(@startdate, '%Y-%m') as rmonth, cz.team as team, cz.region as region,
count(distinct cz.zid) as ending_zone, count(distinct cz.cid) as ending_car,
count(distinct cz.ncar) as cumulative_car,
ifnull(sum(rm.nuse),0) as rsv, round(ifnull(sum(dur),0),2) as dur, round(ifnull(sum(adj_dur),0),2) as adj_dur,
round(sum(rm.rental),2) as rental, round(sum(rm.discount),2) as discount,
round(sum(rm.rental) - sum(rm.discount),2) as nr,
B.mau,
A.mft
from
(select c.log_date as date1, z.city as team, z.region as region, z.id as zid, z.name as zname, c.car_id as cid, c.id as ncar
from car_zone_logs c, zones z
where c.zone_id=z.id
and date_sub(c.log_date, interval 1 day) <= @startdate
and date_sub(c.log_date, interval 1 day) >= @startdate2
and z.id not in (2,3,101,383,378,697,712,818)
group by date1, region, zid, cid)cz
left join
(select
date_format(r.return_at, '%Y-%m-%d') as date2,  r.start_zone_id as zid2, r.car_id as cid2, count(r.id) as nuse, z.region,
sum(timestampdiff(minute, r.start_at, r.return_at)/60) as dur,
sum((timestampdiff(minute, r.start_at, r.end_at)/60)/24)*12 + if(mod((timestampdiff(minute, r.start_at, r.end_at)/60), 24)>=12,12,mod((timestampdiff(minute, r.start_at, r.end_at)/60), 24)) as adj_dur,
sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','oneway','d2d','mileage')),0)) as rental,
sum(ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0)) as discount
#count(distinct m.id) as mau
from reservations r, members m , zones z
where r.member_id=m.id
and r.start_zone_id = z.id
and r.way in ('round','d2d','oneway')
and r.state = 'completed'
and m.imaginary in ('normal', 'sofam')
and r.return_at+interval 8 hour <= @startdate
and r.return_at+interval 8 hour >= @startdate2
and r.start_zone_id not in (2,3,101,383,378,697,712,818)
group by date2, zid2, cid2)rm

on (date_sub(cz.date1,interval 1 day)=rm.date2 and cz.cid=rm.cid2 and cz.zid = rm.zid2)

left join

(select uu.region, ifnull(cc.mft,0) as mft

from(
  select zo.region as region
  from car_zone_logs z
  join zones zo
  on z.zone_id = zo.id

  where
  date_sub(z.log_date, interval 1 day) <= @startdate
  and date_sub(z.log_date, interval 1 day) >= @startdate2
  and z.zone_id not in (2,3,101,383,378,697,712,818,786)

  group by zo.region
  order by zo.region desc

) uu
left join
(select zid2, region, count(distinct if(before_use=0, mid, null)) as mft
  from
  (select
      z.id as zid2, z.region as region, r.member_id as mid,
      (select count(id)
      from reservations
      where member_id=r.member_id
      and state='completed'
      and return_at+interval 8 hour < @startdate2
      and start_zone_id not in (2,3,101,383,378,697,712,818,786)) as before_use
    from reservations r, members m, zones z
    where
      r.state='completed'
      and r.member_id=m.id
      and r.start_zone_id = z.id
      and m.imaginary in ('normal', 'sofam')
      and r.return_at+interval 8 hour <= @startdate
      and r.return_at+interval 8 hour >= @startdate2
      and r.start_zone_id not in (2,3,101,383,378,697,712,818,786)

  group by r.member_id
  ) temp

  where
    before_use=0

group by region) cc
on (uu.region = cc.region)

group by uu.region
) A

on A.region = cz.region

left join

(select
z.region as region,
count(distinct m.id) as mau
from reservations r, members m , zones z
where r.member_id=m.id
and r.start_zone_id = z.id
and r.way in ('round','d2d','oneway')
and r.state = 'completed'
and m.imaginary in ('normal', 'sofam')
and r.return_at+interval 8 hour <= @startdate
and r.return_at+interval 8 hour >= @startdate2
and r.start_zone_id not in (2,3,101,383,378,697,712,818)
group by z.region) B

on B.region = A.region


group by cz.region;