get_list_wo_monthly_pos_v1 merged version

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