Night time price + Hourly Reservations + DL Info = Rahil

set @startdate1 = date_sub(CURDATE(), interval 2 year);
set @startdate2 = date_add(CURDATE(), interval 10 day);
use socar_malaysia;
Select A.return_Date, Count(A.rid) as Total_resv,
Sum(A._1_hr) as 1_hr,Sum(A._2_hr) as 2_hr,Sum(A._3_hr) as 3_hr,
Sum(A._4_hr) as 4_hr,Sum(A._5_hr) as 5_hr,Sum(A._6_hr) as 6_hr,Sum(A._7_hr) as 7_hr, Sum(A._8_hr) as 8_hr,Sum(A._9_hr) as 9_hr,Sum(A._10_hr) as 10_hr,
Sum(A._11_hr) as 11_hr,Sum(A._12_hr) as 12_hr,Sum(A._13_hr) as 13_hr,Sum(A._14_hr) as 14_hr,Sum(A._15_hr) as 15_hr,Sum(A._16_hr) as 16_hr,Sum(A._17_hr) as 17_hr,
Sum(A._18_hr) as 18_hr,Sum(A._19_hr) as 19_hr,Sum(A._20_hr) as 20_hr,Sum(A._21_hr) as 21_hr,Sum(A._22_hr) as 22_hr,Sum(A._23_hr) as 23_hr, Sum(A._24_hr) as 24hr
from
(select distinct r.id as rid,
Date(r.return_at + interval '8' hour) as return_Date ,
hour(r.start_at + interval '8' hour) as Resv_start_hour,
count(distinct case when hour(r.start_at + interval '8' hour) = 0 then r.id end) as _24_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 1 then r.id end) as _1_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 2 then r.id end) as _2_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 3 then r.id end) as _3_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 4 then r.id end) as _4_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 5 then r.id end) as _5_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 6 then r.id end) as _6_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 7 then r.id end) as _7_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 8 then r.id end) as _8_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 9 then r.id end) as _9_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 10 then r.id end) as _10_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 11 then r.id end) as _11_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 12 then r.id end) as _12_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 13 then r.id end) as _13_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 14 then r.id end) as _14_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 15 then r.id end) as _15_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 16 then r.id end) as _16_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 17 then r.id end) as _17_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 18 then r.id end) as _18_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 19 then r.id end) as _19_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 20 then r.id end) as _20_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 21 then r.id end) as _21_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 22 then r.id end) as _22_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 23 then r.id end) as _23_hr
from reservations r left join members m on r.member_id = m.id
where r.state in ('completed','inUse')
and r.return_at + interval 8 hour >= @startdate1
and r.return_at + interval 8 hour <= @startdate2
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
group by r.id) A
group by A.return_date
Order by A.return_date desc;


set @startdate = date_sub(CURDATE(), interval 12 month);
select
WEEKofyear(convert_tz(r.start_at, '+00:00', '+8:00')) as start_week,
DATE(convert_tz(r.start_at, '+00:00', '+8:00')) as start_date,
czl.total_cars as total_cars,
count(distinct r.car_id) as used_cars,
count(r.id) as srid,
count(distinct r.member_id) as dau,
round(SUM(timestampdiff(minute, convert_tz(r.start_at, '+00:00', '+8:00'), convert_tz(r.end_at, '+00:00', '+8:00'))/60),2) as sdur,
round(sum(floor(timestampdiff(minute, convert_tz(r.start_at, '+00:00', '+8:00'), convert_tz(r.end_at, '+00:00', '+8:00'))/60/24)*12+if(mod(timestampdiff(minute, convert_tz(r.start_at, '+00:00', '+8:00'), convert_tz(r.end_at, '+00:00', '+8:00'))/60,24)>=12,12,mod(timestampdiff(minute, convert_tz(r.start_at, '+00:00', '+8:00'), convert_tz(r.end_at, '+00:00', '+8:00'))/60,24))),2) as adur,
round(SUM(c.rev),2) AS rev,
round(SUM(c.rev - IFNULL(p.coupon,0)),2) AS net_rev
from reservations r
left join members on r.member_id = members.id
left join coupons on r.id = coupons.reservation_id
left join (select IFNULL(payments.amount,0) as coupon, payments.reservation_id as rid from payments where payments.state = 'normal' and payments.paid_type = 'coupon') as p on p.rid = r.id
left join (select IFNULL(SUM(charges.amount),0) as rev, charges.reservation_id as rid from charges where charges.state = 'normal' and charges.kind IN ('rent' , 'oneway', 'd2d', 'mileage') group by charges.reservation_id) as c on r.id = c.rid
left join (select log_date, COUNT(car_zone_logs.car_id) as total_cars from car_zone_logs where car_state = 'normal' and zone_state = 'normal' group by log_date) as czl on czl.log_date = DATE(convert_tz(r.start_at,'+00:00', '+8:00'))
where   r.state = 'completed' and members.imaginary in ('sofam', 'normal')
        and convert_tz(r.start_at, '+00:00', '+8:00') >= @startDate
        # Only Monday - Thursday
        and DAYNAME(convert_tz(r.start_at, '+00:00', '+8:00')) in ('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday', 'Sunday')
        # Bookings made between 9 PM to 8 AM
        and (TIME(convert_tz(r.created_at, '+00:00', '+8:00')) between '00:00:00' and '08:00:00' or TIME(convert_tz(r.created_at, '+00:00', '+8:00')) between '21:00:00' and '23:59:59')
        # Less than 12 hours
        and timestampdiff(minute, r.start_at, r.end_at)/60 < 12
group by start_date
order by start_date desc;

# no of members by reject reason /* */

set @start = date_sub(CURDATE(), interval 12 month);
 Select A.date, A.week, B.DocUploaded,B.DocApproved, A.total as rejected, C.timetaken_to_approve, A.sf_cov_bydoc, A.sf_wo_face, A.sf_wo_doc, A.dl_no_doc,
 A.dl_doc_blur,A.dl_expired,A.pdl,A.jpj_blacklist,A.dl_unmatched, A.pic_crop, A.under_21,
 A.fake_id, A.etc,A.duplicate_IC,A.IC_in_selfie_blur, A.passport_blur,A.unrelated , A.nullcases as nullcases
 from

(select P.date as date , weekofyear(P.date)  as week,
 count(distinct P.mid) as total ,
 count(Distinct(if(rcid=1, P.mid, null))) as sf_cov_bydoc,
 count(Distinct(if(rcid=2, P.mid, null))) as sf_wo_face,
 count(Distinct(if(rcid=3, P.mid, null))) as sf_wo_doc,
 count(Distinct(if(rcid=4, P.mid, null))) as dl_no_doc,
 count(Distinct(if(rcid=5, P.mid, null))) as dl_doc_blur,
 count(Distinct(if(rcid=6, P.mid, null))) as dl_expired,
 count(Distinct(if(rcid=7, P.mid, null))) as pdl,
 count(Distinct(if(rcid=8, P.mid, null))) as jpj_blacklist,
 count(Distinct(if(rcid=9, P.mid, null))) as dl_unmatched,
 count(Distinct(if(rcid=10, P.mid, null))) as pic_crop,
 count(Distinct(if(rcid=11, P.mid, null))) as under_21,
 count(Distinct(if(rcid=12, P.mid, null))) as fake_id,
 count(Distinct(if(rcid=13, P.mid, null))) as etc,
 count(Distinct(if(rcid=14, P.mid, null))) as duplicate_IC,
 count(Distinct(if(rcid=15, P.mid, null))) as IC_in_selfie_blur,
 count(Distinct(if(rcid=16, P.mid, null))) as passport_blur,
 count(Distinct(if(rcid=17, P.mid, null))) as unrelated,
count(Distinct(if(rcid is null, P.mid, null))) as nullcases
from
(select  C.mid as mid , C.reject_category_id as rcid, C.date as date
 from
(select distinct dl.member_id as mid, max(dl.id) as dlid from driver_licenses dl
where date(dl.created_at + interval '8' hour) >= @start
group by 1) B
left join
(select dl.member_id as mid, dl.id as dlid,date(dl.created_at + interval 8 hour) as date,
 dl.state as state, dl.reject_category_id as reject_category_id , dlc.name as reject_category
 from driver_licenses dl
left join driver_license_reject_categories dlc on dlc.id = dl.reject_category_id
group by 1,2) C
on B.dlid = C.dlid
where C.state in ('reject')
group by 1
order by 2 desc )P
group by 1
order by 1 desc) A
left join
(select
Date(dl.created_at + interval '8' hour) as Date,
weekofyear(dl.created_at + interval 8 hour)  as week,
count(distinct case when dl.state not in ('noInput','null') then dl.member_id end) DocUploaded,
count(distinct case when dl.state = 'approved' then dl.member_id end) DocApproved
from driver_licenses dl
join members m
on m.id = dl.member_id
where m.imaginary = 'normal'
group by 1) B
on A.date = B.date
left join
(select A.date as date, round(avg(A.average_time_for_Approval),0) as timetaken_to_approve from
(select distinct dl.member_id,
Date(dl.created_at + interval '8' hour) as Date,
(timestampdiff(minute,dl.created_at + interval '8' hour,dl.approved_at + interval '8' hour)) as average_time_for_Approval
from driver_licenses dl
where dl.state = 'approved'
and (timestampdiff(minute,dl.created_at + interval '8' hour,dl.approved_at + interval '8' hour)) <= '240'
order by 1 desc) A
group by 1
order by 1 desc) C
on C.date = A.date
where A.date >= @start ;





######## weekly Revenue Related Info

Select A.date as date,
sum(A.Basic_SCDW) as Basic_SCDW,
sum(A.Standard_SCDW) as Standard_SCDW,
sum(A.Premium_SCDW) as Premium_SCDW,
Round(sum(A.Total_SCDW_amount),2) as Total_SCDW_amount,
Round(sum(A.Basic_SCDW_amount),2) as Basic_SCDW_amount,
Round(sum(A.Standard_SCDW_amount),2) as Standard_SCDW_amount,
Round(sum(A.Premium_SCDW_amount),2) as Premium_SCDW_amount,
Round(sum(A.Basic_SCDW_amount)/sum(A.Basic_SCDW),2) as BASIC_SCDW_per_resv,
ROUND(sum(A.Standard_SCDW_amount)/sum(A.Standard_SCDW),2) as Standard_SCDW_per_resv,
ROUND(sum(A.Premium_SCDW_amount)/sum(A.Premium_SCDW),2) Premium_SCDW_per_resv

from
(select c.reservation_id as rid ,
Date(r.start_at + interval 8 hour) as date,
c.member_id as mid,
cm.meta as meta,
count(distinct case when cm.meta LIKE '%Basic SCDW%' then cm.charge_id end) as Basic_SCDW,
count(distinct case when cm.meta LIKE '%Standard SCDW%' then cm.charge_id end) as Standard_SCDW,
count(distinct case when cm.meta LIKE '%Premium SCDW%' then cm.charge_id end) as Premium_SCDW,
Sum(distinct case when cm.meta LIKE '%SCDW%' then c.amount end) as Total_SCDW_amount,
Sum(distinct case when cm.meta LIKE '%Basic SCDW%' then c.amount end) as Basic_SCDW_amount,
Sum(distinct case when cm.meta LIKE '%Standard SCDW%' then c.amount end) as Standard_SCDW_amount,
Sum(distinct case when cm.meta LIKE '%Premium SCDW%' then c.amount end) as Premium_SCDW_amount
from charges c
left join charge_meta  cm on cm.charge_id = c.id
left join reservations r on r.id = c.reservation_id
left join members m on m.id = c.member_id
where c.kind = 'insurance'
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and r.state in ('completed','inUse')
and c.state = 'normal'
group by 1 ) A
Where A.date is not null
group by 1
order by 1 desc
;

###### Reservations and Net Revenues by duration length

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

select
  L.return_Date,
  count(L.rid) as total,
  sum(L.net_rev) as net_rev,
  count(if(L.duration < 1, L.rid, null)) as 00_01_hr,
  count(if(L.duration >= 1 and L.duration < 2, L.rid, null)) as 01_02_hr,
  count(if(L.duration >= 2 and L.duration < 3, L.rid, null)) as 02_03_hr,
  count(if(L.duration >= 3 and L.duration < 4, L.rid, null)) as 03_04_hr,
  count(if(L.duration >= 4 and L.duration < 5, L.rid, null)) as 04_05_hr,
  count(if(L.duration >= 5 and L.duration < 6, L.rid, null)) as 05_06_hr,
  count(if(L.duration >= 6 and L.duration < 7, L.rid, null)) as 06_07_hr,
  count(if(L.duration >= 7 and L.duration < 8, L.rid, null)) as 07_08_hr,
  count(if(L.duration >= 8 and L.duration < 9, L.rid, null)) as 08_09_hr,
  count(if(L.duration >= 9 and L.duration < 10, L.rid, null)) as 09_10_hr,
  count(if(L.duration >= 10 and L.duration < 11, L.rid, null)) as 10_11_hr,
  count(if(L.duration >= 11 and L.duration < 12, L.rid, null)) as 11_12_hr,
  count(if(L.duration >= 12 and L.duration < 24, L.rid, null)) as 12_24_hr,
  count(if(L.duration >= 24 and L.duration < 48, L.rid, null)) as 24_48_hr,
  count(if(L.duration >= 48, L.rid, null)) as 48__hr ,
 ##### Net_rev
  Sum(if(L.duration < 1, L.net_rev, null)) as 00_01_hr_amount,
  Sum(if(L.duration >= 1 and L.duration < 2, L.net_rev, null)) as 01_02_hr_amount,
  Sum(if(L.duration >= 2 and L.duration < 3, L.net_rev, null)) as 02_03_hr_amount,
  Sum(if(L.duration >= 3 and L.duration < 4, L.net_rev, null)) as 03_04_hr_amount,
  Sum(if(L.duration >= 4 and L.duration < 5, L.net_rev, null)) as 04_05_hr_amount,
  Sum(if(L.duration >= 5 and L.duration < 6, L.net_rev, null)) as 05_06_hr_amount,
  Sum(if(L.duration >= 6 and L.duration < 7, L.net_rev, null)) as 06_07_hr_amount,
  Sum(if(L.duration >= 7 and L.duration < 8, L.net_rev, null)) as 07_08_hr_amount,
  Sum(if(L.duration >= 8 and L.duration < 9, L.net_rev, null)) as 08_09_hr_amount,
  Sum(if(L.duration >= 9 and L.duration < 10, L.net_rev, null)) as 09_10_hr_amount,
  Sum(if(L.duration >= 10 and L.duration < 11, L.net_rev, null)) as 10_11_hr_amount,
  Sum(if(L.duration >= 11 and L.duration < 12, L.net_rev, null)) as 11_12_hr_amount,
  Sum(if(L.duration >= 12 and L.duration < 24, L.net_rev, null)) as 12_24_hr_amount,
  Sum(if(L.duration >= 24 and L.duration < 48, L.net_rev, null)) as 24_48_hr_amount,
  Sum(if(L.duration >= 48, L.net_rev, null)) as 48__hr_amount

 from

(select A.return_date as return_date, A.rid2 as rid, A.dur as duration, A.ad_sdur as ad_sdur, IFNULL(E.charges,0) as gross_rev,
IFNULL(F.coupon_Spent,0) as Coupon_Spent,
round((IFNULL(E.charges,0) - IFNULL(F.coupon_Spent,0)),2) as net_rev from
(select
distinct r.id as rid2, r.member_id as mid,
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,
Date(r.return_at + interval '8' hour) as return_Date
from reservations r left join members m on r.member_id = m.id
left join reservation_appendixes ra on ra.reservation_id = r.id
where r.state in ('completed')
and r.return_at + interval 8 hour >= @startdate1
and r.return_at + interval 8 hour <= @startdate2
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
group by rid2) A
left join
(select c.reservation_id as rid, sum(c.amount) as charges from charges c
where c.state='normal' and c.kind in ('rent','oneway','d2d','mileage','insurance')
group by rid) E on A.rid2 = E.rid
left join
(select p.reservation_id as rid, month(p.created_at + interval '8' hour) as month,Year(p.created_at + interval '8' hour) as year, IFNULL(p.amount,0) as coupon_Spent from payments p
where p.state = 'normal' and p.paid_type = 'coupon'
group by p.reservation_id) F on A.rid2 = F.rid) L
group by 1
order by 1 desc;


############# Daily Query for Payment Provider



set @startdate1 = date_sub(CURDATE(), interval 6 month);
set @startdate2 = date_add(CURDATE(), interval 10 day);
use socar_malaysia;
Select
date(A.start_Date) as month,
year(A.start_Date) as year,
Count(Distinct A.rid) as Total_attempts,
count(distinct case when A.provider = 'mcp'  then A.rid end) as Total_MCP,
count(distinct case when A.provider = 'braintree'  then A.rid end) as Total_braintree,
count(distinct case when A.provider = 'grabpay'  then A.rid end) as Total_grabpay,
count(distinct case when A.provider = 'stripe'  then A.rid end) as Total_stripe,
count(distinct case when A.provider = 'mcp' and A.state not in ('fail')  then A.rid end) as MCP_Good,
count(distinct case when A.provider = 'braintree' and A.state not in ('fail')  then A.rid end) as braintree_Good,
count(distinct case when A.provider = 'grabpay' and A.state not in ('fail')  then A.rid end) as grabpay_Good,
count(distinct case when A.provider = 'stripe' and A.state not in ('fail')  then A.rid end) as stripe_Good,
count(distinct case when A.provider = 'mcp' and A.state in ('fail')  then A.rid end) as MCP_failed,
count(distinct case when A.provider = 'braintree' and A.state in ('fail')  then A.rid end) as braintree_failed,
count(distinct case when A.provider = 'grabpay' and A.state in ('fail')  then A.rid end) as grabpay_failed,
count(distinct case when A.provider = 'stripe' and A.state in ('fail')  then A.rid end) as stripe_failed
from
(select
distinct r.id as rid, r.member_id as mid, r.state as state, p.paid_type as type, pm.provider as provider,
Date(r.created_at + interval '8' hour) as start_Date, p.paygate as provider_2
from reservations r left join members m on r.member_id = m.id
LEFT JOIN payment_methods pm ON r.payment_method_id = pm.id
left join payments p on p.reservation_id = r.id
where r.created_at + interval 8 hour >= @startdate1
and r.created_at + interval 8 hour <= @startdate2
and r.member_id not in ('125', '127')
group by rid, type) A
group by 1
order by 1 desc;

##### Coupons info W.O.W

set @startdate1 = date_sub(CURDATE(), interval 6 week);
set @startdate2 = date_add(CURDATE(), interval 10 day);
use socar_malaysia;
use socar_malaysia;
Select I.week as week,I.coupon as coupon, I.cpid as cpid, count(distinct case when I.mid is not null then I.mid end) as Mau,count(distinct case when I.rid is not null then I.rid end) as total_resv,
Sum(I.dur) as dur, Sum(I.ad_sdur) as ad_sdur, Sum(nuse_rnd) as nuse_rnd, Sum(res_d2d) as res_d2d,sum(res_oneway) as res_oneway,
sum(gross_rev) as gross_rev,sum(Coupon_Spent) as Coupon_Spent,sum(net_rev) as net_rev,sum(mft) as mft from
(select A.week as week, A.mid as mid, A.rid2 as rid, A.coupon as coupon,A.cpid as cpid, A.dur as dur, A.ad_sdur as ad_sdur,
A.nuse_rnd as nuse_rnd,A.res_d2d as res_d2d,A.res_oneway as res_oneway ,IFNULL(E.charges,0) as gross_rev, IFNULL(F.coupon_Spent,0) as Coupon_Spent,
round((IFNULL(E.charges,0) - IFNULL(F.coupon_Spent,0)),2) as net_rev,if(rid2=firstRes,1,0) as mft from
(select
distinct r.id as rid2, r.member_id as mid,IFNULL(co.coupon_policy_id,"none") as cpid,IFNULL(co.comment,"none") as coupon,
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,
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,
Date(r.return_at + interval '8' hour) as return_Date,
weekofyear(r.return_at + interval '8' hour) as week,
Year(r.return_at + interval '8' hour) as Year,
ifnull((select min(id) from reservations where state='completed' and r.member_id=member_id group by member_id),0) as firstRes
from reservations r left join members m on r.member_id = m.id
left join coupons co on co.reservation_id = r.id
where r.state in ('completed','inUse','reserved')
and r.return_at + interval 8 hour >= @startdate1
and r.return_at + interval 8 hour <= @startdate2
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and (YEAR(r.start_at) - YEAR(m.birthday)) < 100
group by rid2) A
left join
(select c.reservation_id as rid, sum(c.amount) as charges from charges c
where c.state='normal' and c.kind in ('rent','oneway','d2d','mileage')
group by rid) E on A.rid2 = E.rid
left join
(select p.reservation_id as rid, month(p.created_at + interval '8' hour) as month,Year(p.created_at + interval '8' hour) as year, IFNULL(p.amount,0) as coupon_Spent from payments p
where p.state = 'normal' and p.paid_type = 'coupon'
group by p.reservation_id) F on A.rid2 = F.rid) I
where I.coupon not in ('none')
and I.week > Weekofyear(now()) - 7
Group by I.week, I.cpid
order by I.week, gross_rev desc;

#################################################################################Fuel Product Dashboard

set @startdate1 = date_sub(CURDATE(), interval 3 month);
set @startdate2 = date_add(CURDATE(), interval 10 day);
use socar_malaysia;
select I.date as date, Q.cars as cars, count(I.rid) as resv,
sum(I.gross_rev) as gross, sum(I.Coupon_Spent) as Coupon_Spent, Sum(I.net_rev) as net_rev, round(sum(I.mileage),2) as mileage,
round(sum(I.mileage)+Sum(I.fuel),2) as Total_mileage_fuel , Round(Sum(I.fuel),2) as fuel, round(count(I.rid)/Q.cars,2) as RESPCPD,round(sum(I.dur)/Q.cars,2) as DURPCPD,round(sum(I.ad_sdur)/Q.cars,2) as AD_DURPCPD,
round(sum(I.fuel_optedin)*100/count(I.rid),2) as opt_in_PCT,Round(sum(I.mileage_paid_resv)*100/count(I.rid),2) as mileage_paid_PCT,
round((sum(I.mileage)+Sum(I.fuel))/count(I.rid),2) as Total_mileage_fuel_per_res , Round(Sum(I.fuel)/count(I.rid),2) as fuel_per_res, round(sum(I.gross_rev)/Q.cars,2) as Gross_REVPCPD,
round(Sum(I.net_rev)/Q.cars,2) as NRPCPD,  Round(Sum(I.fuel)/Q.cars,2) as fuel_PCPD, round(sum(I.mileage)/Q.cars,2) as mileage_PCPD, round((sum(I.mileage)+Sum(I.fuel))/Q.cars,2) as Total_mileage_fuel_DCPD,
sum(I.distance) as distance, ROUND(round((sum(I.mileage)+Sum(I.fuel)),2)/sum(I.distance),4) EMF_P_FP_P_KM
from
(select A.return_date as Date, A.rid2 as rid, A.dur as dur, A.ad_sdur as ad_sdur, A.distance as distance,
IFNULL(G.mileage,0) as mileage , Ifnull(H.fuel,0) as fuel ,IFNULL(G.excess_km,0) as excess_km ,
IFNULL(H.fuel_optedin,0) as fuel_optedin ,
IFNULL(G.mileage_paid,0) as mileage_paid_resv,
IFNULL(E.charges,0) as gross_rev,
IFNULL(F.coupon_Spent,0) as Coupon_Spent,
round((IFNULL(E.charges,0) - IFNULL(F.coupon_Spent,0)),2) as net_rev from
(select
distinct r.id as rid2, r.member_id as mid, IFNULL(sum(ra.mileage),0) as distance,
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,
Date(r.return_at + interval '8' hour) as return_Date
from reservations r left join members m on r.member_id = m.id
left join reservation_appendixes ra on ra.reservation_id = r.id
where r.state in ('completed')
and r.return_at + interval 8 hour >= @startdate1
and r.return_at + interval 8 hour <= @startdate2
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
group by rid2) A
left join
(select c.reservation_id as rid, sum(c.amount) as mileage, count(distinct c.reservation_id) as mileage_paid, SUBSTRING_INDEX(SUBSTRING_INDEX(c.etc, 'excess: ', -1), '= distance', 1) as excess_km from charges c
where c.state='normal' and c.kind in ('mileage')
group by rid) G on A.rid2 = G.rid
left join
(select c.reservation_id as rid, sum(c.amount) as fuel, count(distinct c.reservation_id) as fuel_optedin from charges c
where c.state='normal' and c.kind in ('fuel')
group by rid) H on A.rid2 = H.rid
left join
(select c.reservation_id as rid, sum(c.amount) as charges from charges c
where c.state='normal' and c.kind in ('rent','oneway','d2d','mileage','insurance','chauffeur','fuel')
group by rid) E on A.rid2 = E.rid
left join
(select p.reservation_id as rid, month(p.created_at + interval '8' hour) as month,Year(p.created_at + interval '8' hour) as year, IFNULL(p.amount,0) as coupon_Spent from payments p
where p.state = 'normal' and p.paid_type = 'coupon'
group by p.reservation_id) F on A.rid2 = F.rid) I
left join
(select date(czl.log_date + interval 8 hour ) as date, count(czl.id) as cars
from car_zone_logs czl
where czl.zone_state='normal'
and czl.log_date + interval 8 hour >= @startdate1
and czl.log_date + interval 8 hour <= @startdate2
group by 1
order by 1 desc) Q
on Q.Date = I.Date
group by 1
order by 1 desc ;

########################## NRPCPD , RESPCPD , DURPCPD  by City

set @startdate1 = date_sub(CURDATE(), interval 2 month);
set @startdate2 = date_add(CURDATE(), interval 10 day);
use socar_malaysia;
Select I.date as date, I.city as city, weekofyear(I.date) as week, year(I.date) as year,
(Q.cars - L.Common_block_Cars) as cars, Round(I.net_rev,2) as Net_rev, ROUND(I.Total_reservations/(Q.cars-L.Common_block_Cars),2) as RESPCPD,
ROUND(I.gross_rev/(Q.cars-L.Common_block_Cars),2) as Gross_RevPCPD, ROUND(I.Coupon_Spent/(Q.cars-L.Common_block_Cars),2) as Coupon_RevPCPD, ROUND(I.net_rev/(Q.cars-L.Common_block_Cars),2) as net_RevPCPD,
Round(I.Dur/(Q.cars-L.Common_block_Cars),2) as DURPCPD, Round(I.ad_sdur/(Q.cars-L.Common_block_Cars),2) as AD_DURPCPD,
Round(I.net_rev/I.Total_reservations,2) Net_BS, Round(I.net_rev/I.ad_sdur,2) NRPH
from
(select date(A.return_date) as date, A.city as city, count(A.rid2) as Total_reservations,
count(distinct A.mid) as Total_Unique_Members, sum(A.distance) as distance, sum(A.dur) as dur, sum(A.ad_sdur) as ad_sdur,
IFNULL(Sum(E.charges),0) as gross_rev,
IFNULL(sum(F.coupon_Spent),0) as Coupon_Spent,
round((IFNULL(sum(E.charges),0) - IFNULL(sum(F.coupon_Spent),0)),2) as net_rev from
(select
distinct r.id as rid2, r.member_id as mid, r.way as way, z.city as city,
IFNULL(sum(ra.mileage),0) as distance,
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,
Date(r.return_at + interval '8' hour) as return_Date,
month(r.return_at + interval '8' hour) as month
from reservations r left join members m on r.member_id = m.id
left join reservation_appendixes ra on ra.reservation_id = r.id
left join zones z on z.id = r.start_zone_id
where r.state in ('completed')
and r.return_at + interval 8 hour >= @startdate1
and r.return_at + interval 8 hour <= @startdate2
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and (YEAR(r.start_at) - YEAR(m.birthday)) < 100
group by rid2) A
left join
(select c.reservation_id as rid, sum(c.amount) as charges from charges c
where c.state='normal' and c.kind in ('rent','oneway','d2d','mileage')
group by rid) E on A.rid2 = E.rid
left join
(select p.reservation_id as rid, month(p.created_at + interval '8' hour) as month,Year(p.created_at + interval '8' hour) as year, IFNULL(p.amount,0) as coupon_Spent from payments p
where p.state = 'normal' and p.paid_type = 'coupon'
group by p.reservation_id) F on A.rid2 = F.rid
group by 1,2 ) I
left join
(select date(czl.log_date + interval 8 hour ) as date, z.city as city, count(czl.id) as cars from car_zone_logs czl left join cars cr on cr.id = czl.car_id left join zones z on z.id = czl.zone_id
left join car_classes cc on cc.id = cr.car_class_id where czl.zone_state='normal'
and czl.log_date + interval 8 hour >= @startdate1
and czl.log_date + interval 8 hour <= @startdate2
group by 1,2
order by 1,2 desc) Q
on Q.date = I.date and Q.city = I.city
left join
(select date, city, count(Distinct(if(rst<=date, if(rend>=date,cid,null),null))) as Common_block_Cars
from
(select date_format(r.return_at, "%Y-%m-%d") as date
from reservations r
where r.start_at + interval 8 hour >= @startdate1
and r.start_at + interval 8 hour <= @startdate2
group by date)d
join
(select r.id as rid, r.car_id as cid, r.state, z.city as city,
date_format(r.start_at+interval 8 hour, "%Y-%m-%d")  as rst, date_format(r.return_at+interval 8 hour,"%Y-%m-%d") as rrt, date_format(r.end_at+interval 8 hour,"%Y-%m-%d") as rend
, timestampdiff(day, r.start_at, r.end_at) as dur
from reservations r ,zones z
where member_id=125
and r.start_zone_id = z.id
and r.start_at + interval 8 hour >= @startdate1
and r.start_at + interval 8 hour <= @startdate2
and r.state in ('completed', 'inuse')
)r
where dur >=7
group by date ,city) L
on L.city = I.city and L.date = I.date
Where L.city in ('JB','PG','KL','SL')
group by 1,2
order by 1 desc ;

####################### Simedarby cars - Partnership


set @startdate1 = date_sub(CURDATE(), interval 2 month);
set @startdate2 = date_add(CURDATE(), interval 10 day);
use socar_malaysia;
Select  I.week as week, I.car_name as car_name, I.maker as maker, Round(I.net_rev,2) as Net_rev,
ROUND(I.Total_reservations/Q.cars,2) as RESPCPD, ROUND(I.net_rev/Q.cars,2) as net_RevPCPD, Round(I.ad_sdur/Q.cars,2) as AD_DURPCPD from
(select weekofyear(A.return_date) as week, A.car_name as car_name, A.maker as maker, count(A.rid2) as Total_reservations,
count(distinct A.mid) as Total_Unique_Members, sum(A.distance) as distance, sum(A.dur) as dur, sum(A.ad_sdur) as ad_sdur,
IFNULL(Sum(E.charges),0) as gross_rev,
IFNULL(sum(F.coupon_Spent),0) as Coupon_Spent,
round((IFNULL(sum(E.charges),0) - IFNULL(sum(F.coupon_Spent),0)),2) as net_rev from
(select
distinct r.id as rid2, r.member_id as mid, r.way as way, cc.car_name as car_name, cc.maker as maker, cc.id as ccid, cr.id as crid,
IFNULL(sum(ra.mileage),0) as distance,
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,
Date(r.return_at + interval '8' hour) as return_Date,
month(r.return_at + interval '8' hour) as month
from reservations r left join members m on r.member_id = m.id
left join reservation_appendixes ra on ra.reservation_id = r.id
left join cars cr on cr.id = r.car_id
left join car_classes cc on cc.id = cr.car_class_id
where r.state in ('completed')
and r.return_at + interval 8 hour >= @startdate1
and r.return_at + interval 8 hour <= @startdate2
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and (YEAR(r.start_at) - YEAR(m.birthday)) < 100
and (cc.id in (29,30,31) or cr.id in ('2262'))
group by 1) A
left join
(select c.reservation_id as rid, sum(c.amount) as charges from charges c
where c.state='normal' and c.kind in ('rent','oneway','d2d','mileage','insurance','chauffeur','fuel')
group by rid) E on A.rid2 = E.rid
left join
(select p.reservation_id as rid, month(p.created_at + interval '8' hour) as month,Year(p.created_at + interval '8' hour) as year, IFNULL(p.amount,0) as coupon_Spent from payments p
where p.state = 'normal' and p.paid_type = 'coupon'
group by p.reservation_id) F on A.rid2 = F.rid
group by 1,2 ) I
left join
(select weekofyear(czl.log_date + interval 8 hour ) as week, cc.car_name as car_name, count(czl.id) as cars from car_zone_logs czl left join cars cr on cr.id = czl.car_id
left join car_classes cc on cc.id = cr.car_class_id where czl.zone_state='normal'
and czl.log_date + interval 8 hour >= @startdate1
and czl.log_date + interval 8 hour <= @startdate2
and (cc.id in (29,30,31) or cr.id in ('2262'))
group by 1,2
order by 1,2 desc) Q
on Q.week = I.week and Q.car_name = I.car_name
group by 1,2
order by 1 desc ;