WIP - MFT, Oneway, Grabpay - Jason

set @start := '2019-1-1';
Select P.Week, P.TotalMFTs,P.GrabMFT, P.mft_21,Q.MFT_Oneway, ROUND(P.NetRev,2) as Net_rev,ROUND(P.GrabPay,2) as Grabpay_Rev, ROUND(P.GrossRev,2) as Gross_rev from
(select
A.week as week,A.TotalMFTs as TotalMFTs,A.GrabMFT as GrabMFT,A.mft_21 as mft_21,IFNULL(B.NetRev,0) as NetRev,IFNULL(B.GrabPay,0) as GrabPay,B.GrossRev as GrossRev
from (select
weekofyear(c.created_at + interval '8' hour) as week,
count(distinct case when c.kind = 'subscriptionFee' and str_to_date(m.birthday, '%Y%m%d') >= NOW() - interval '21' year then c.member_id end) as mft_21,
count(distinct case when c.kind = 'subscriptionFee' and pm.card_type = 'eWallet' and (c.created_at + interval '8' hour) >= '2019-6-12' then c.member_id end) as GrabMFT,
count(distinct case when c.kind = 'subscriptionFee' then c.member_id end) as TotalMFTs
from charges c join members m on m.id = c.member_id
join payment_methods pm on pm.member_id = m.id
left outer join member_appendixes ma on ma.id = m.id
where c.created_at + interval '8' hour >= @start
and m.imaginary = 'normal'
group by 1) A
join (
select
weekofyear(r.created_at + interval '8' hour) as week,
sum(case when p.state = 'normal' and p.paid_type IN ('card', 'eWallet') then p.amount end) as NetRev,
sum(case when p.state = 'normal' and p.paid_type IN ('eWallet') and (r.start_at + interval '8' hour) >= '2019-6-12' then p.amount end) as GrabPay,
sum(case when p.state = 'normal' then p.amount end) as GrossRev
from reservations r
join members m on m.id = r.member_id and m.email not like '%socar.my%' and m.imaginary = 'normal'
join payments p on p.reservation_id = r.id
where r.created_at + interval '8' hour >= @start and p.state = 'normal'
group by 1 order by 1 asc) B
on B.week = A.week group by 1 order by 1 asc) P
join
(Select Weekofyear(A.date) as week, Count(A.rid) as MFT_Oneway from
(select FB.rid2 as rid, FB.mid as mid, FB.way as way, FB.date as date from
(select r.id as rid2 ,r.start_zone_id as stid, r.end_zone_id as edid,
date(r.start_at + interval '8' hour) as date,
fbo.mid as mid,fbo.rid,fbo.way as way from
reservations r
join
(select
fbi.mid as mid,fbi.rid as rid,fbi.way as way from
(select
distinct r.member_id as mid, min(r.id) as rid, r.way as way
from reservations r join members m on m.id = r.member_id
where r.state = 'completed' and m.imaginary = 'normal' and m.email not like '%socar.my%' group by r.member_id) fbi
where fbi.way in ('oneway','onewayReturn') ) fbo on fbo.rid = r.id
where r.start_zone_id in ('244','245') or r.end_zone_id in ('244','245') )FB
where date(FB.date + interval '8' hour) >= '2019-01-01') A group by week order by week desc ) Q
on P.week = Q.week