Ryan Queries - Monthly All

# summary of Usage all (end_at) - Monthly collected


use socar_malaysia;
select
    r.id, r.member_id, m.first_name, m.last_name, m.email,
    (select dl.birthday
      from driver_licenses dl
      where m.id=dl.member_id
        order by dl.created_at desc limit 1) as birthday,
    (year(convert_tz(now(), '+00:00', '+8:00')) - substring(birthday, 1, 4)
    - if(substring(birthday, 5, 4) - date_format(convert_tz(now(), '+00:00', '+8:00'), "%m%d") > 0, 1, 0)) as age,
    (select dl.gender
      from driver_licenses dl
      where m.id=dl.member_id
        order by dl.created_at desc limit 1
    ) as gender, r.way, r.state, r.start_zone_id as zone_id, z.region as region, z.name as zone_name, r.car_id, c.registration_no, cs.car_name as cartype,
    convert_tz(r.created_at, '+00:00', '+8:00') as rc, convert_tz(r.start_at, '+00:00', '+8:00') as rs, convert_tz(r.end_at, '+00:00', '+8:00') as re, convert_tz(r.return_at, '+00:00', '+8:00') as rr,
    timestampdiff(hour, r.start_at, r.end_at) as dur,
    timestampdiff(hour, r.created_at, r.start_at) as buffer,
    timestampdiff(minute, r.end_at, r.return_at) as delay,
    ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind='rent'),0) as rent,
    ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind='d2d'),0) as d2d,
    ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind='oneway'),0) as oneway,
    ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind='mileage'),0) as mileage,
    ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind='penalty'),0) as penalty,
    ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind='accident'),0) as accident,
    ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind not in ('rent','d2d','oneway','mileage','penalty','accident','subscriptionFee')),0) as etc,
    ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','d2d','oneway','mileage')),0)
    - ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0) as nr,
    ifnull((select sum(amount) from payments where state='normal' and r.id=reservation_id),0) as paid,
    ifnull((select sum(amount) from payments where state='normal' and r.id=reservation_id and paid_type='coupon'),0) as paid_coupon

from reservations r, zones z, members m, car_classes cs, cars c
where
    r.start_zone_id = z.id
    and r.member_id = m.id
    and c.id = r.car_id
    and c.car_class_id = cs.id
    and m.imaginary in ('sofam', 'normal')
    #and m.imaginary not in ('test')
    and r.member_id not in ('125', '127')
    and z.id not in (2, 3, 101)
    and r.state in ('completed')
    #and cs.id = '4'
    #and convert_tz(r.return_at, '+00:00', '+8:00') >= '2018-01-01= 00:00:00'
    and convert_tz(r.end_at, '+00:00', '+8:00') between '2019-03-01= 00:00:00' and '2019-03-31= 23:59:59'



group by r.id
order by re
;

#created_at > '2017-11-20= 00:00:00'
#and created_at <= '2017-11-28= 23:59:59'
#group by m

###########################################################################

##########################################################################################################
# summary of Usage all (end_at)


use socar_malaysia;
select
    r.id, r.member_id, m.first_name, m.last_name, m.email,
    (select dl.birthday
      from driver_licenses dl
      where m.id=dl.member_id
        order by dl.created_at desc limit 1) as birthday,
    (year(convert_tz(now(), '+00:00', '+8:00')) - substring(birthday, 1, 4)
    - if(substring(birthday, 5, 4) - date_format(convert_tz(now(), '+00:00', '+8:00'), "%m%d") > 0, 1, 0)) as age,
    (select dl.gender
      from driver_licenses dl
      where m.id=dl.member_id
        order by dl.created_at desc limit 1
    ) as gender, r.way, r.state, r.start_zone_id as zone_id, z.region as region, z.name as zone_name, r.car_id, c.registration_no, cs.car_name as cartype,
    convert_tz(r.created_at, '+00:00', '+8:00') as rc, convert_tz(r.start_at, '+00:00', '+8:00') as rs, convert_tz(r.end_at, '+00:00', '+8:00') as re, convert_tz(r.return_at, '+00:00', '+8:00') as rr,
    timestampdiff(hour, r.start_at, r.end_at) as dur,
    timestampdiff(hour, r.created_at, r.start_at) as buffer,
    timestampdiff(minute, r.end_at, r.return_at) as delay,
    ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind='rent'),0) as rent,
    ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind='d2d'),0) as d2d,
    ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind='oneway'),0) as oneway,
    ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind='mileage'),0) as mileage,
    ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind not in ('rent','d2d','oneway','mileage','subscriptionFee')),0) as etc,
    ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','d2d','oneway','mileage')),0)
    - ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0) as nr,
    ifnull((select sum(amount) from payments where state='normal' and r.id=reservation_id),0) as paid,
    ifnull((select sum(amount) from payments where state='normal' and r.id=reservation_id and paid_type='coupon'),0) as paid_coupon

from reservations r, zones z, members m, car_classes cs, cars c
where
    r.start_zone_id = z.id
    and r.member_id = m.id
    and c.id = r.car_id
    and c.car_class_id = cs.id
    and m.imaginary in ('sofam', 'normal')
    #and m.imaginary not in ('test')
    and r.member_id not in ('125', '127')
    and z.id not in (2, 3, 101)
    and r.state in ('completed')
    #and cs.id = '4'
    #and convert_tz(r.return_at, '+00:00', '+8:00') >= '2018-01-01= 00:00:00'
    and convert_tz(r.end_at, '+00:00', '+8:00') between '2018-01-01= 00:00:00' and '2018-11-30= 23:59:59'



group by r.id
order by re
;
#created_at > '2017-11-20= 00:00:00'
#and created_at <= '2017-11-28= 23:59:59'
#group by m