get_list_wo_monthly_pos_v1 merged version
unknown
mysql
10 months ago
11 kB
4
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
),
allservice_exceptlcr as(
select dp.wonomesin,
dp.paymentdate
from datapool_domain dp
where (LEFT(dp.woid, 2) = 'WO' or LEFT(dp.woid, 3) = 'VST') and
LOWER(dp.servicename) not like all (array['%lcr check%', '%lcr treatment%', '%lcr ganti%'])
group by dp.paymentdate, dp.wonomesin
),
sumallservice_bynosindate as(
select count(dp.*) totalcounts,
dp.wonomesin,
dp.paymentdate
from datapool_domain dp
where (LEFT(dp.woid, 2) = 'WO' or LEFT(dp.woid, 3) = 'VST')
group by dp.paymentdate, dp.wonomesin
having count(dp.*) = 1
),
svc_category as (
select MIN(resultset02.woid)::character varying as woid,
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,
resultset02.posserviceid
from (
with jrvst_serv as (
select sum(dp.total) total,
9 priority,
dp.wonomesin,
dp.woid,
dp.paymentdate,
dp.posserviceid
from datapool_domain dp
where LEFT(dp.woid, 2) = 'JR'
group by dp.paymentdate, dp.wonomesin, dp.woid, dp.posserviceid
), claim_lcr_treatment_ganti as(
select sum(dp.total) total,
10 priority,
concat(dp.wonomesin, ' SC') AS wonomesin,
dp.woid,
dp.paymentdate,
dp.posserviceid
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) like any (array['%lcr treatment%', '%lcr ganti%'])
group by dp.woserviceid, dp.paymentdate, dp.wonomesin, dp.woid, dp.posserviceid
), claimc2 as(
select coalesce(
sum(cok.hargajasa),
sum(dp.total),
0
) total,
10 priority,
dp.wonomesin,
dp.woid,
dp.paymentdate,
dp.posserviceid
from datapool_domain dp
left join view_c2 c2 on c2.nowo = dp.woid
left join mst_unittypemarketingcode umc on umc.unittypeid = dp.unittypeid
left join mst_sync_claim_ongkos_kerja cok on cok.tipemotor = umc.unittypecode and c2.ongkoskerja = cok.jenispekerjaan
where (LEFT(dp.woid, 2) = 'WO' or LEFT(dp.woid, 3) = 'VST') and
LOWER(dp.servicecategory) like '%claim c2%' and
LOWER(dp.servicename) not like all (array['%lcr treatment%', '%lcr ganti%']) and
case
when lower(dp.servicename) like '%lcr check%' then
exists (
select 1
from allservice_exceptlcr ae
where dp.wonomesin = ae.wonomesin
and dp.paymentdate = ae.paymentdate
)
or exists (
select 1
from sumallservice_bynosindate ae
where dp.wonomesin = ae.wonomesin
and dp.paymentdate = ae.paymentdate
)
else 1 = 1
end
group by dp.woserviceid, dp.paymentdate, dp.wonomesin, dp.woid, dp.posserviceid
), hr as(
select sum(dp.total) total,
20 priority,
dp.wonomesin,
dp.woid,
dp.paymentdate,
dp.posserviceid
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, dp.woid, dp.posserviceid
), 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.woid,
dp.paymentdate,
dp.posserviceid
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, dp.woid, dp.posserviceid
), completeserv as(
select sum(dp.total) total,
40 priority,
dp.wonomesin,
dp.woid,
dp.paymentdate,
dp.posserviceid
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, dp.woid, dp.posserviceid
), lightserv as(
select sum(dp.total) total,
41 priority,
dp.wonomesin,
dp.woid,
dp.paymentdate,
dp.posserviceid
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, dp.woid, dp.posserviceid
), literepair as(
select sum(dp.total) total,
50 priority,
dp.wonomesin,
dp.woid,
dp.paymentdate,
dp.posserviceid
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, dp.woid, dp.posserviceid
), msev as (
select sum(dp.total) total,
55 priority,
dp.wonomesin,
dp.woid,
dp.paymentdate,
dp.posserviceid
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, dp.woid, dp.posserviceid
), oilserv as (
select sum(dp.total) total,
60 priority,
dp.wonomesin,
dp.woid,
dp.paymentdate,
dp.posserviceid
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, dp.woid, dp.posserviceid
), otherserv as(
select sum(dp.total) total,
70 priority,
dp.wonomesin,
dp.woid,
dp.paymentdate,
dp.posserviceid
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, dp.woid, dp.posserviceid
)
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, resultset02.posserviceid
),
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) like any (array['%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.woid, sc.paymentdate, sc.total, sc.servicecategory, wt.wotype, sc.posserviceid
from svc_category sc
left join wo_type wt on sc.wonomesin = wt.wonomesin
and sc.paymentdate = wt.paymentdate;
end;
$function$
;
Editor is loading...
Leave a Comment