Key Index Dashboard - Revenues - V 2.0

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

set @startDate = "2019-01-01 00: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

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','d2d','oneway','mileage','insurance')),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 ('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')),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')
group by 1) A on A.week = rm.week
left join
(select
weekofyear(r.return_at + interval 8 hour) as week,
sum(c.amount) as oneway_amount
from reservations r
join charges c on c.reservation_id = r.id
where r.way in ('oneway', 'onewayReturn')
and r.state = 'completed'
and (r.return_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
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
group by rm.week ;

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

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

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','d2d','oneway','mileage','insurance')),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 ('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')),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')
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(r.return_at + interval 8 hour) as month,
sum(c.amount) as oneway_amount
from reservations r
join charges c on c.reservation_id = r.id
where r.way in ('oneway', 'onewayReturn')
and r.state = 'completed'
and (r.return_at + interval 8 hour) >= @startdate
group by 1) O on O.month = A.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 A.month = C.month
group by rm.month