Zone MFT MAU Pricing - To test MFT Prices

set @startdate = '2019-04-01 00:00:00';
select
date_format(@startdate, '%Y-%m') as rmonth, cz.team as team, cz.region as region, cz.zid, cz.zname,
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, ifnull(sum(dur),0) as dur, ifnull(sum(adj_dur),0) as adj_dur,
sum(rm.rental) as rental, sum(rm.discount) as discount,
sum(rm.rental) - sum(rm.discount) as nr,
ifnull(sum(rm.mau),0) as mau,
A.mft
from
(select c.log_date as date1, z.city as team, z.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) < date_add(@startdate, interval 1 month)
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,
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
where r.member_id=m.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 < date_add(@startdate, interval 1 month)
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)

left join

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

from(
  select z.zone_id as zid

  from car_zone_logs z

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

  group by zid
  order by zid desc

) uu
left join
(select zid2, count(distinct if(before_use=0, mid, null)) as mft
  from
  (select
      z.id as zid2,  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 < @startdate
      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 < @startdate+interval 1 month
      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 zid2) cc
on (uu.zid = cc.zid2)

group by zid
order by zid) A

on A.zid = cz.zid
group by cz.zid;