Temp - KID (Revenues Both) - 6 th Month

use socar_malaysia;
set @start := '2020-10-01 00:00';


select
A.Week,
A.signups,
B.DocUploaded,
B.DocApproved,
D.TotalMFTs,
C.ActiveMembers,
C.Bookings as rentals,
C.ridelength

from (select
WEEKOFYEAR(m.created_at + interval '8' hour) as Week,
count(distinct m.id) as signups
from members m
left outer join reservations r on
r.member_id = m.id
where m.created_at + interval '8' hour >= @start
and m.imaginary = 'normal'
group by 1 ) A

join (select
WEEKOFYEAR(dl.created_at + interval '8' hour) as Week,
count(distinct case when dl.state not like 'noInput' then dl.member_id end) DocUploaded,
count(distinct case when dl.state = 'approved' then dl.member_id end) DocApproved,
count(distinct case when dl.gender = 'man' then dl.member_id end) MaleApproved,
count(distinct case when dl.gender = 'woman' then dl.member_id end) FemaleApproved
from driver_licenses dl
join members m
on m.id = dl.member_id
where dl.created_at + interval '8' hour >= @start
and m.imaginary = 'normal'
group by 1) B
on B.Week = A.Week

join (select
weekofyear(r.return_at + interval '8' hour) as week,
count(distinct case when r.state = 'completed' then r.member_id end) as ActiveMembers,
count(distinct case when r.state = 'completed' then r.id end) as Bookings,
round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as ridelength,
sum(G.amount) as Charges
from reservations r
left outer join (select
ch.reservation_id as rid,
sum(ch.amount) as amount
from payments ch
group by 1
) G on G.rid = r.id
left join members m on m.id = r.member_id
where r.start_at + interval '8' hour >= @start
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and r.start_zone_id not in (2, 3, 101)
and r.state = 'completed'
group by 1) C on
C.Week = B.Week

join (
select
WEEKOFYEAR(c.created_at + interval '8' hour) as Week,
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
where c.created_at + interval '8' hour >= @start
and m.imaginary = 'normal'
group by 1) D on
D.Week = C.Week
group by 1
order by 1 asc;

Select
weekofyear(t1.date) as week,
t1.date,
    t1.ncars,
    t1.ncars - COALESCE(t2.ncars, t1.ncars) AS diff
from

(select
Date(date_sub(czl.log_date , interval 1 day)) as date,
weekday(date_sub(czl.log_date , interval 1 day)) as weekday,
count(distinct czl.car_id ) as ncars
from car_zone_logs czl
where czl.log_date + interval '8' hour >= @start
and czl.car_state = 'normal'
and czl.zone_state = 'normal'
and czl.csa_state = 'normal'
and weekday(date_sub(czl.log_date , interval 1 day)) = 6
group by 1) t1

left join

(select
Date(date_add(czl.log_date , interval 7 day)) as date,
weekday(date_add(czl.log_date , interval 7 day)) as weekday,
count(distinct czl.car_id ) as ncars
from car_zone_logs czl
where czl.log_date + interval '8' hour >= @start
and czl.car_state = 'normal'
and czl.zone_state = 'normal'
and czl.csa_state = 'normal'
and weekday(date_sub(czl.log_date , interval 1 day)) = 6
group by 1) t2

on t1.date   = t2.date - 1

order by t1.date ;


set @start2 := '2020-10-01 00:00';

select
A.month,
A.signups,
B.DocUploaded,
B.DocApproved,
D.TotalMFTs,
C.ActiveMembers,
C.Bookings as rentals,
C.ridelength
from (select
month(m.created_at + interval '8' hour) as month,
count(distinct m.id) as signups
from members m
left outer join reservations r on r.member_id = m.id
where m.created_at + interval '8' hour >= @start2
and m.imaginary = 'normal'
group by 1 ) A

join (select
month(dl.created_at + interval '8' hour) as month,
count(distinct case when dl.state not like 'noInput' then dl.member_id end) DocUploaded,
count(distinct case when dl.state = 'approved' then dl.member_id end) DocApproved,
count(distinct case when dl.gender = 'man' then dl.member_id end) MaleApproved,
count(distinct case when dl.gender = 'woman' then dl.member_id end) FemaleApproved
from driver_licenses dl
join members m on m.id = dl.member_id
where dl.created_at + interval '8' hour >= @start2
and m.imaginary = 'normal'
group by 1) B
on B.month = A.month

join (select
month(r.return_at + interval '8' hour) as month,
count(distinct case when r.state = 'completed' then r.member_id end) as ActiveMembers,
count(distinct case when r.state = 'completed' then r.id end) as Bookings,
round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as ridelength,
sum(G.amount) as Charges
from reservations r
left outer join (select
ch.reservation_id as rid,
sum(ch.amount) as amount
from payments ch
group by 1
) G on G.rid = r.id
left join members m on m.id = r.member_id
where r.start_at + interval '8' hour >= @start2
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and r.start_zone_id not in (2, 3, 101)
and r.state = 'completed'
group by 1) C on
C.month = B.month

join (
select
month(c.created_at + interval '8' hour) as month,
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
where c.created_at + interval '8' hour >= @start2
and m.imaginary = 'normal'
group by 1) D on
D.month = C.month

group by 1

order by 1 asc;

set @start := '2020-10-01 00:00';

Select
t1.date,
    t1.ncars,
    t1.ncars - COALESCE(t2.ncars, t1.ncars) AS diff_cars,
    t1.nzones,
    t1.nzones - COALESCE(t2.nzones, t1.nzones) AS diff_zones,
    P.cars as avg_cars

from

(select
Month(date_sub(czl.log_date , interval 1 day)) as date,
LAST_DAY(date_sub(czl.log_date , interval 1 day)) as weekday,
count(distinct czl.car_id ) as ncars,
count(distinct czl.zone_id ) as nzones
from car_zone_logs czl
where czl.log_date + interval '8' hour >= @start
and czl.car_state = 'normal'
and czl.zone_state = 'normal'
and czl.csa_state = 'normal'
and czl.log_date = LAST_DAY(date_sub(czl.log_date , interval 1 day))
group by 1) t1

left join

(select
Month(date_add(czl.log_date , interval 1 month)) as date,
LAST_DAY(date_add(czl.log_date , interval 1 month)) as weekday,
count(distinct czl.car_id ) as ncars,
count(distinct czl.zone_id ) as nzones
from car_zone_logs czl
where czl.log_date + interval '8' hour >= @start
and czl.car_state = 'normal'
and czl.zone_state = 'normal'
and czl.csa_state = 'normal'
and czl.log_date = LAST_DAY(date_sub(czl.log_date , interval 1 day))
group by 1) t2
on t1.date = t2.date

left join

(select month(L.date) as month, ROUND(Avg(L.cars),0) as cars from
(select date(date_sub(czl.log_date , interval 1 day)) as date,
count(distinct czl.car_id) as cars
from car_zone_logs czl
where czl.log_date + interval '8' hour >= @start
and czl.zone_state = 'normal'
and czl.csa_state = 'normal'
#where czl.zone_state='normal'
group by 1
order by 1 desc) L
group by 1) P
on P.month = t1.date


order by t1.date;


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

set @startDate = '2020-10-01 00:00';

select
rm.week as week,
sum(rm.nr) as netrevenues,
sum(rm.rent) as rental,
sum(rm.d2d) as d2d,
sum(O.oneway_amount) as oneway,
sum(C.subscriptionFee) as subscriptionFee,
sum(rm.mileage) as mileage,
sum(A.Amount) as penalty_Repair,
sum(rm.paid_coupon) as Coupon,
IFNULL(B.noa,0) as Accidents,
IFNULL(B.accidentamount,0) as accidentamount,
IFNULL(sum(I.insurance),0) as insurance,
sum(rm.chauffeur) as chauffeur,
sum(F.fuel) as fuel

from
(select
  weekofyear(r.return_at + interval '8' hour) as week,
  sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent')),0)) as rent,
  sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('d2d')),0)) as d2d,
  sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('oneway')),0)) as oneway,
  sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('chauffeur')),0)) as chauffeur,
  sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('mileage')),0)) as mileage,
  sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('subscriptionFee')),0)) as subscriptionFee,
  sum(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','insurance','chauffeur','fuel')),0)- ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0)),2) as nr
  from reservations r, members m

 where r.member_id=m.id
 and m.imaginary in ('normal','sofam')
 and r.state = 'completed'
 and convert_tz(r.return_at, '+0:00','+8:00') >= @startdate
 group by 1
) rm
left join

######### Weekly other amounts
(select
weekofyear(c.created_at + interval '8' hour) as week,
sum(c.amount) as amount
from charges c
where convert_tz(c.created_at, '+0:00','+8:00') >= @startdate
and c.state = 'normal'
and c.kind not in ('rent','d2d','oneway','mileage','subscriptionFee','insurance','chauffeur','fuel','accident')
group by 1) A on A.week = rm.week
left join
(select
weekofyear(c.created_at + interval 8 hour) as week,
sum(c.amount) as oneway_amount
from charges c
where c.kind in ('oneway')
and c.state = 'normal'
and (c.created_at + interval 8 hour) >= @startdate
group by 1) O on O.week = rm.week
left join
(select
A.week as week,
A.noa as noa,
round(Sum(A.amount),0) as accidentamount
from
(
select
weekofyear(c.created_at + interval '8' hour) as week,
c.id as cid,
count(distinct case when c.kind = 'accident' then c.id end) as noa,
sum(c.amount) as amount
from charges c
where convert_tz(c.created_at, '+0:00','+8:00') >= @startdate
and c.state = 'normal'
and c.kind in ('accident')
group by 1) A
group by 1,2) B on A.Week = B.week
left join
(select
weekofyear(c.created_at + interval 8 hour) as week,
sum(c.amount) as insurance
from charges c
where c.kind in ('insurance')
and c.state = 'normal'
and (c.created_at + interval 8 hour) >= @startdate
group by 1) I on I.week = rm.week
left join
(select
A.week as week,
A.Paid_subs as Paid_subs,
round(Sum(A.amount),0) as subscriptionFee
from
(
select
weekofyear(c.created_at + interval '8' hour) as week,
c.id as cid,
count(distinct case when c.kind = 'subscriptionFee' then c.id end) as Paid_subs,
sum(c.amount) as amount
from charges c
where convert_tz(c.created_at, '+0:00','+8:00') >= @startdate
and c.state = 'normal'
and c.kind in ('subscriptionFee')
group by 1) A
group by 1,2) C
on A.week = C.week
left join
(select
weekofyear(c.created_at + interval 8 hour) as week,
sum(c.amount) as fuel
from charges c
where c.kind in ('fuel')
and c.state = 'normal'
and (c.created_at + interval 8 hour) >= @startdate
group by 1) F on F.week = rm.week
group by rm.week ;

######## monthly Revenue Related Info


set @startDate = '2020-10-01 00:00';

select
rm.month as month,
sum(rm.nr) as netrevenues,
sum(rm.rent) as rental,
sum(rm.d2d) as d2d,
sum(O.oneway_amount) as oneway,
sum(C.subscriptionFee) as subscriptionFee,
sum(rm.mileage) as mileage,
sum(A.Amount) as penalty_Repair,
sum(rm.paid_coupon) as Coupon,
IFNULL(B.noa,0) as Accidents,
IFNULL(B.accidentamount,0) as accidentamount,
IFNULL(sum(I.insurance),0) as insurance,
sum(rm.chauffeur) as chauffeur,
sum(F.fuel) as fuel

from
(select
  month(r.return_at + interval '8' hour) as month,
  sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent')),0)) as rent,
  sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('d2d')),0)) as d2d,
  sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('oneway')),0)) as oneway,
  sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('chauffeur')),0)) as chauffeur,
  sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('mileage')),0)) as mileage,
  sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('subscriptionFee')),0)) as subscriptionFee,
  sum(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','insurance','chauffeur','fuel')),0)- ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0)),2) as nr
  from reservations r, members m

 where r.member_id=m.id
 and m.imaginary in ('normal','sofam')
 and r.state = 'completed'
 and convert_tz(r.return_at, '+0:00','+8:00') >= @startdate
 group by 1
) rm
left join
######### monthly other amounts
(select
month(c.created_at + interval '8' hour) as month,
sum(c.amount) as amount
from charges c
where convert_tz(c.created_at, '+0:00','+8:00') >= @startdate
and c.state = 'normal'
and c.kind not in ('rent','d2d','oneway','mileage','subscriptionFee','insurance','chauffeur','fuel','accident')
group by 1) A on A.month = rm.month
left join
(select
A.month as month,
A.noa as noa,
round(Sum(A.amount),0) as accidentamount
from
(
select
month(c.created_at + interval '8' hour) as month,
c.id as cid,
count(distinct case when c.kind = 'accident' then c.id end) as noa,
sum(c.amount) as amount
from charges c
where convert_tz(c.created_at, '+0:00','+8:00') >= @startdate
and c.state = 'normal'
and c.kind in ('accident')
group by 1) A
group by 1,2) B
on A.month = B.month
left join
(select
month(c.created_at + interval 8 hour) as month,
sum(c.amount) as oneway_amount
from charges c
where c.kind in ('oneway')
and c.state = 'normal'
and (c.created_at + interval 8 hour) >= @startdate
group by 1) O on O.month = rm.month
left join
(select
month(c.created_at + interval 8 hour) as month,
sum(c.amount) as insurance
from charges c
where c.kind in ('insurance')
and c.state = 'normal'
and (c.created_at + interval 8 hour) >= @startdate
group by 1) I on I.month = rm.month
left join
(select
A.month as month,
A.Paid_subs as Paid_subs,
round(Sum(A.amount),0) as subscriptionFee
from
(
select
month(c.created_at + interval '8' hour) as month,
c.id as cid,
count(distinct case when c.kind = 'subscriptionFee' then c.id end) as Paid_subs,
sum(c.amount) as amount
from charges c
where convert_tz(c.created_at, '+0:00','+8:00') >= @startdate
and c.state = 'normal'
and c.kind in ('subscriptionFee')
group by 1) A
group by 1,2) C
on rm.month = C.month
left join
(select
month(c.created_at + interval 8 hour) as month,
sum(c.amount) as fuel
from charges c
where c.kind in ('fuel')
and c.state = 'normal'
and (c.created_at + interval 8 hour) >= @startdate
group by 1) F on F.month = rm.month
group by rm.month