get_list_wo_monthly_pos_v1 06/06/2024
unknown
mysql
a year ago
5.9 kB
5
Indexable
CREATE OR REPLACE FUNCTION public.get_list_wo_monthly_pos_v1(mon character varying, tahun character varying, kodeahass character varying, posid character varying) RETURNS TABLE(woid character varying, paymentdate date, total double precision, servicecategory text, wotype text, posserviceid character varying) LANGUAGE plpgsql AS $function$ declare startdate Date := concat(tahun, '-', mon, '-01')::Date; enddate Date; begin enddate := (startdate + interval '1 month - 1 day'); RETURN QUERY with datapool_domain as ( select wo.* from get_service_category_wo_pos_latest_v1(kodeahass, mon, tahun, posid) wo order by wo.woid ), svc_category AS ( SELECT dp.woid, dp.paymentdate, CASE WHEN dp.servicename LIKE '%LCR%' THEN SUM(cskpb.claimprice) ELSE MAX(dp.total) END AS total, CASE WHEN LEFT(dp.woid, 2) = 'JR' THEN 'JR' WHEN LEFT(dp.woid, 2) = 'WO' AND LOWER(dp.servicecategory) LIKE '%claim c2%' THEN 'CLAIM' WHEN LEFT(dp.woid, 2) = 'WO' AND (LOWER(dp.servicecategory) LIKE '%heavy repair%' OR LOWER(dp.servicecategory) LIKE '%heavy_repair%') THEN 'HR' WHEN LEFT(dp.woid, 2) = 'WO' AND LOWER(dp.servicecategory) LIKE '%ass%' THEN CASE WHEN LOWER(dp.servicecategory) LIKE '%ass 1%' THEN 'ASS 1' WHEN LOWER(dp.servicecategory) LIKE '%ass 2%' THEN 'ASS 2' WHEN LOWER(dp.servicecategory) LIKE '%ass 3%' THEN 'ASS 3' WHEN LOWER(dp.servicecategory) LIKE '%ass 4%' THEN 'ASS 4' ELSE 'ASS' END WHEN LEFT(dp.woid, 2) = 'WO' AND (LOWER(dp.servicecategory) LIKE '%complete service%' OR LOWER(dp.servicecategory) LIKE '%paket lengkap%') THEN 'CS' WHEN LEFT(dp.woid, 2) = 'WO' AND (LOWER(dp.servicecategory) LIKE '%light service%' OR LOWER(dp.servicecategory) LIKE '%paket ringan%') THEN 'LS' WHEN LEFT(dp.woid, 2) = 'WO' AND (LOWER(dp.servicecategory) LIKE '%paket ringan%' OR LOWER(dp.servicecategory) LIKE '%light repair%' OR LOWER(dp.servicecategory) LIKE '%light service%') THEN 'LR' WHEN LEFT(dp.woid, 2) = 'WO' AND LOWER(dp.servicecategory) LIKE '%ms%' THEN 'MS' WHEN LEFT(dp.woid, 2) = 'WO' AND LOWER(dp.servicecategory) LIKE '%ganti oli plus%' THEN 'OR' WHEN LEFT(dp.woid, 2) = 'WO' AND LOWER(dp.servicecategory) LIKE '%paket lain%' THEN 'OTHER SERVICE' ELSE NULL END AS servicecategory, dp.posserviceid FROM datapool_domain dp LEFT join mst_configworkshopservice cws ON dp.woserviceid = cws.configworkshopserviceid LEFT join mst_configworkshopservicedetail cwsd ON dp.woserviceid = cwsd.configworkshopserviceid LEFT JOIN mst_configservicekpbdetail cskpbd ON cwsd.workshopserviceid = cskpbd.workshopserviceid and cskpbd.configworkshopservicecode = cws.configworkshopservicecode LEFT JOIN mst_configservicekpb cskpb ON cskpbd.configworkshopservicekpbid = cskpb.configworkshopservicekpbid GROUP BY dp.woid, dp.paymentdate, dp.servicecategory, dp.servicename, dp.posserviceid ), wo_type AS ( SELECT query.woid, query.paymentdate, SUM(query.total) AS total, CASE WHEN MIN(priority) = 10 THEN 'REMINDER' WHEN MIN(priority) = 20 THEN 'VISIT' WHEN MIN(priority) = 30 THEN 'EVENT' WHEN MIN(priority) = 40 THEN 'EXPRESS' WHEN MIN(priority) = 50 THEN 'REGULAR' END AS wotype FROM ( SELECT a.woid, a.paymentdate, CASE WHEN a.alasandatang = 'SMS/Phone Reminder' THEN SUM(a.total) WHEN LEFT(a.woid, 3) = 'VST' AND a.alasandatang NOT IN ('SMS/Phone Reminder') THEN SUM(a.total) WHEN (a.typejasa = 'PACKAGE' OR a.typejasa = 'KM, PACKAGE' OR a.typejasa = 'PACKAGE, KM') AND a.alasandatang NOT IN ('SMS/Phone Reminder') AND LEFT(a.woid, 3) NOT LIKE 'VST' THEN SUM(a.total) WHEN a.pittype = 'EXPRESS' AND a.alasandatang NOT IN ('SMS/Phone Reminder') AND LEFT(a.woid, 3) NOT LIKE 'VST' AND a.typejasa NOT IN ('PACKAGE', 'KM, PACKAGE', 'PACKAGE, KM') THEN SUM(a.total) ELSE SUM(a.total) END AS total, CASE WHEN a.alasandatang = 'SMS/Phone Reminder' THEN 10 WHEN LEFT(a.woid, 3) = 'VST' THEN 20 WHEN (a.typejasa = 'PACKAGE' OR a.typejasa = 'KM, PACKAGE' OR a.typejasa = 'PACKAGE, KM') THEN 30 WHEN a.pittype = 'EXPRESS' THEN 40 ELSE 50 END AS priority FROM datapool_domain a GROUP BY a.woid, a.alasandatang, a.pittype, a.typejasa, a.paymentdate ) query GROUP BY query.woid, query.paymentdate ), price_ass as ( select dp.woid, kpb.claimprice from datapool_domain dp inner join mst_unittypemarketingcode umc on umc.unittypeid = dp.unittypeid inner join mst_configservicekpb kpb on kpb.marketingcode = umc.marketingcode inner join mst_configservicekpbdetail kpbd on kpb.configworkshopservicekpbid = kpbd.configworkshopservicekpbid and dp.woserviceid = kpbd.workshopserviceid where LOWER(dp.servicecategory) LIKE '%ass%' ) SELECT sc.woid, sc.paymentdate, coalesce(ass.claimprice, sc.total) as total, sc.servicecategory, wt.wotype, sc.posserviceid FROM svc_category sc JOIN wo_type wt ON wt.woid = sc.woid LEFT JOIN price_ass ass on sc.woid = ass.woid; END; $function$ ;
Editor is loading...
Leave a Comment