Untitled

mail@pastecode.io avatar
unknown
mysql
a month ago
8.6 kB
5
Indexable
Never
CREATE OR REPLACE FUNCTION public.get_list_wo_monthly_ue_aff(mon character varying, tahun character varying, kodeahass character varying)
 RETURNS TABLE(wonomesin character varying, paymentdate date, total double precision, servicecategory text, wotype text)
 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_ue_aff(kodeahass, mon, tahun) wo
		order by wo.woid
	),
	svc_category as (
		select 
			resultset02.wonomesin,
			resultset02.paymentdate, 
			sum(resultset02.total) total, 
			(case when MIN(priority) = 9 then 'JR'
				when MIN(priority) = 10 then 'CLAIM'
				when MIN(priority) = 20 then 'HR'
				when MIN(priority) = 31 then 'ASS 1'
				when MIN(priority) = 32 then 'ASS 2'
				when MIN(priority) = 33 then 'ASS 3'
				when MIN(priority) = 34 then 'ASS 4'
				when MIN(priority) = 40 then 'CS'
				when MIN(priority) = 41 then 'LS'
				when MIN(priority) = 50 then 'LR'
				when MIN(priority) = 55 then 'MS'
				when MIN(priority) = 60 then 'OR'
				when MIN(priority) = 70 then 'OTHER SERVICE' END)::text servicecategory 
		from (
			with jrvst_serv as (
				select sum(dp.total) total,
					9 priority,
					dp.wonomesin,
					dp.paymentdate
				from datapool_domain dp
				where LEFT(dp.woid, 2) = 'JR' 
				group by dp.paymentdate, dp.wonomesin
			), claim_lcr_treatment_ganti as(
				select sum(dp.total) total, 
					10 priority,
					concat(dp.wonomesin, ' SC') AS wonomesin,
					dp.paymentdate
				from datapool_domain dp
				where (LEFT(dp.woid, 2) = 'WO' or LEFT(dp.woid, 3) = 'VST') and
					LOWER(dp.servicecategory) like '%claim c2%' and 
					lower(dp.servicename) in ('lcr treatment', 'lcr ganti')
				group by dp.woserviceid, dp.paymentdate, dp.wonomesin
			), claimc2 as(
				select sum(dp.total) total, 
					10 priority,
					dp.wonomesin,
					dp.paymentdate
				from datapool_domain dp
				where (LEFT(dp.woid, 2) = 'WO' or LEFT(dp.woid, 3) = 'VST') and
					LOWER(dp.servicecategory) like '%claim c2%'
					and lower(dp.servicename) not in ('lcr treatment', 'lcr ganti')
					and case 
						when lower(dp.servicename) like '%lcr check%' then 
							not exists (
			                    select 1 
			                    from claim_lcr_treatment_ganti c 
			                    where dp.wonomesin = LEFT(c.wonomesin, LENGTH(c.wonomesin) - 3)
			                )
			            end
				group by dp.woserviceid, dp.paymentdate, dp.wonomesin
			), hr as(
				select sum(dp.total) total, 
					20 priority,
					dp.wonomesin,
					dp.paymentdate
				from datapool_domain dp
				where (LEFT(dp.woid, 2) = 'WO' or LEFT(dp.woid, 3) = 'VST') and
						(LOWER(dp.servicecategory) like '%heavy repair%' 
						or LOWER(dp.servicecategory) like '%heavy_repair%')
				group by dp.woserviceid, dp.paymentdate, dp.wonomesin
			), ass as(
				select sum(kpb.claimprice) as total, 
					(case when LOWER(dp.servicecategory) like '%ass 1%' then 31 
						when LOWER(dp.servicecategory) like '%ass 2%' then 32 
						when LOWER(dp.servicecategory) like '%ass 3%' then 33 
						when LOWER(dp.servicecategory) like '%ass 4%' then 34 
						else 3 end)priority,
					dp.wonomesin,
					dp.paymentdate
				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 (LEFT(dp.woid, 2) = 'WO' or LEFT(dp.woid, 3) = 'VST') and LOWER(dp.servicecategory) like '%ass%'
				group by dp.woserviceid, dp.servicecategory, dp.paymentdate, kpb.claimprice, dp.wonomesin
			), completeserv as(
				select sum(dp.total) total, 
					40 priority,
					dp.wonomesin,
					dp.paymentdate
				from datapool_domain dp
				where (LEFT(dp.woid, 2) = 'WO' or LEFT(dp.woid, 3) = 'VST') and
					(LOWER(dp.servicecategory) like '%complete service%'
					or LOWER(dp.servicecategory) like '%paket lengkap%')
				group by dp.woserviceid, dp.paymentdate, dp.wonomesin
			), lightserv as(
				select sum(dp.total) total, 
					41 priority,
					dp.wonomesin,
					dp.paymentdate
				from datapool_domain dp
				where (LEFT(dp.woid, 2) = 'WO' or LEFT(dp.woid, 3) = 'VST') and
					(LOWER(dp.servicecategory) like '%light service%'
					or LOWER(dp.servicecategory) like '%paket ringan%')
				group by dp.woserviceid, dp.paymentdate, dp.wonomesin
			), literepair as(
				select sum(dp.total) total, 
					50 priority,
					dp.wonomesin,
					dp.paymentdate
				from datapool_domain dp
				where (LEFT(dp.woid, 2) = 'WO' or LEFT(dp.woid, 3) = 'VST') and
					(LOWER(dp.servicecategory) like '%paket ringan%'
					or LOWER(dp.servicecategory) like '%light repair%'
					or LOWER(dp.servicecategory) like '%light service%')
				group by dp.woserviceid, dp.paymentdate, dp.wonomesin
			), msev as (
				select sum(dp.total) total,
					55 priority,
					dp.wonomesin,
					dp.paymentdate
				from datapool_domain dp
				where (LEFT(dp.woid, 2) = 'WO' or LEFT(dp.woid, 3) = 'VST') and
					(LOWER(dp.servicecategory) like '%ms%')
				group by dp.woserviceid, dp.paymentdate, dp.wonomesin
			), oilserv as (
				select sum(dp.total) total,
					60 priority,
					dp.wonomesin,
					dp.paymentdate
				from datapool_domain dp
				where (LEFT(dp.woid, 2) = 'WO' or LEFT(dp.woid, 3) = 'VST') and
					(LOWER(dp.servicecategory) like '%ganti oli plus%')
				group by dp.woserviceid, dp.paymentdate, dp.wonomesin
			), otherserv as(
				select sum(dp.total) total, 
					70 priority,
					dp.wonomesin,
					dp.paymentdate
				from datapool_domain dp
				where (LEFT(dp.woid, 2) = 'WO' or LEFT(dp.woid, 3) = 'VST') and
					(LOWER(dp.servicecategory) like '%paket lain%')
				group by dp.woserviceid, dp.paymentdate, dp.wonomesin
			)
			
			select * from jrvst_serv
			union all
			select * from claimc2
			union all
			select * from claim_lcr_treatment_ganti
			union all
			select * from hr
			union all
			select * from ass
			union all
			select * from completeserv
			union all
			select * from lightserv
			union all
			select * from literepair
			union all
			select * from msev
			union all
			select * from oilserv
			union all
			select * from otherserv) resultset02
		group by resultset02.paymentdate, resultset02.wonomesin
	),
	wo_type as (
		select 
				query.wonomesin as wonomesin,
				query.paymentdate as 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 'REGULER' end) wotype
		from
			(select 
				case when lower(a.servicename) in ('lcr treatment', 'lcr ganti') then concat(a.wonomesin, ' SC')
					else a.wonomesin end as wonomesin,
				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.wonomesin, a.woid, 
			a.alasandatang, a.pittype, a.typejasa, a.paymentdate, a.servicename) query
		group by 
		query.paymentdate, query.wonomesin
		order by query.paymentdate
	)
	select sc.wonomesin, sc.paymentdate, sc.total, sc.servicecategory, wt.wotype
	from svc_category sc
	left join wo_type wt on sc.wonomesin = wt.wonomesin;
end;
$function$
;
Leave a Comment