Untitled

CREATE OR REPLACE FUNCTION public.get_list_wo_monthly_pos_latest_v1(mon character varying, tahun character varying, kodeahass character varying, posid character varying)
 RETURNS TABLE(wonomesin 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 
    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.paymentdate, 
          dp.posserviceid 
        from 
          datapool_domain dp 
        where 
          LEFT(dp.woid, 2) = 'JR' 
        group by 
          dp.paymentdate, 
          dp.posserviceid, 
          dp.wonomesin
      ), 
      claim_lcr_treatment_ganti as(
        select 
          sum(dp.total) total, 
          10 priority, 
          concat(dp.wonomesin, ' SC') AS wonomesin, 
          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.posserviceid
      ), 
      claimc2 as(
        select 
          coalesce(
            sum(cok.hargajasa), 
            sum(dp.total), 
            0
          ) total, 
          10 priority, 
          dp.wonomesin, 
          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.posserviceid
      ), 
      hr as(
        select 
          sum(dp.total) total, 
          20 priority, 
          dp.wonomesin, 
          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.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.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.posserviceid
      ), 
      completeserv as(
        select 
          sum(dp.total) total, 
          40 priority, 
          dp.wonomesin, 
          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.posserviceid
      ), 
      lightserv as(
        select 
          sum(dp.total) total, 
          41 priority, 
          dp.wonomesin, 
          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.posserviceid
      ), 
      literepair as(
        select 
          sum(dp.total) total, 
          50 priority, 
          dp.wonomesin, 
          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.posserviceid
      ), 
      msev as (
        select 
          sum(dp.total) total, 
          55 priority, 
          dp.wonomesin, 
          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.posserviceid
      ), 
      oilserv as (
        select 
          sum(dp.total) total, 
          60 priority, 
          dp.wonomesin, 
          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.posserviceid
      ), 
      otherserv as(
        select 
          sum(dp.total) total, 
          70 priority, 
          dp.wonomesin, 
          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.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'
        ) 
        and a.pittype <> 'EXPRESS' 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, 
        a.wonomesin,
        a.servicename
    ) query 
  group by 
    query.wonomesin, 
    query.paymentdate 
  order by 
    query.paymentdate
) 
select 
  sc.wonomesin, 
  sc.paymentdate, 
  sc.total, 
  sc.servicecategory, 
  wt.wotype, 
  sc.posserviceid 
from 
  svc_category sc 
  left join wo_type wt on wt.wonomesin = sc.wonomesin 
and 
  wt.paymentdate = sc.paymentdate;
end;
$function$
;