get_list_wo_monthly_pos_v1 06/06/2024

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$
;