Untitled
unknown
mysql
a year ago
8.6 kB
7
Indexable
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$
;
Editor is loading...
Leave a Comment