Incr_ DL Approval Rate Project _ Rahil - Nash - CSR Project

# no of members by reject reason /* */

 set @start := '2019-1-1';
 Select A.date, A.week, B.DocUploaded, A.total, 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 from

(select
 date(dl.created_at + interval 8 hour) as date,
 weekofyear(dl.created_at + interval 8 hour)  as week,
 count(dl.id) as total,
 count(if(rc.id=1, dl.id, null)) as sf_cov_bydoc,
 count(if(rc.id=2, dl.id, null)) as sf_wo_face,
 count(if(rc.id=3, dl.id, null)) as sf_wo_doc,
 count(if(rc.id=4, dl.id, null)) as dl_no_doc,
 count(if(rc.id=5, dl.id, null)) as dl_doc_blur,
 count(if(rc.id=6, dl.id, null)) as dl_expired,
 count(if(rc.id=7, dl.id, null)) as pdl,
 count(if(rc.id=8, dl.id, null)) as jpj_blacklist,
 count(if(rc.id=9, dl.id, null)) as dl_unmatched,
 count(if(rc.id=10, dl.id, null)) as pic_crop,
 count(if(rc.id=11, dl.id, null)) as under_21,
 count(if(rc.id=12, dl.id, null)) as fake_id,
 count(if(rc.id=13, dl.id, null)) as etc,
 count(if(rc.id=14, dl.id, null)) as duplicate_IC,
 count(if(rc.id=15, dl.id, null)) as IC_in_selfie_blur,
 count(if(rc.id=16, dl.id, null)) as passport_blur,
 count(if(rc.id=17, dl.id, null)) as unrelated
from members m, driver_licenses dl, driver_license_reject_categories rc
where
 m.id = dl.member_id
 and m.imaginary in ('sofam', 'normal')
 and dl.state = 'reject'
 and dl.reject_category_id = rc.id
group by date
order by date 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
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
where A.date >= @start
;

# all members and reject reason /* */

select distinct dl.member_id , dl.reject_category_id , dlc.name , Date(dl.created_at + interval 8 hour) as date
from driver_licenses dl left join driver_license_reject_categories dlc on dlc.id = dl.reject_category_id
where dl.state = 'reject'
and Date(dl.created_at + interval 8 hour) = date_sub(curdate() , interval 1 day)
order by dl.member_id desc ;

# Members with blur Reason /* */

select distinct dl.member_id , dl.reject_category_id , dlc.name , Date(dl.created_at + interval 8 hour) as date
from driver_licenses dl left join driver_license_reject_categories dlc on dlc.id = dl.reject_category_id
where dl.state = 'reject'
and dl.state not in ('approved')
and dl.reject_category_id in ('5','15','16')
and Date(dl.created_at + interval 8 hour) >= '1-8-2019'
order by dl.member_id desc ;