report_penjualanjasa_by_noinvoice_v4

 avatar
unknown
mysql
5 months ago
8.5 kB
4
Indexable
CREATE OR REPLACE FUNCTION public.report_penjualanjasa_by_noinvoice_v4(startdate character varying, enddate character varying, kodeahass character varying)
 RETURNS TABLE(noinvoice character varying, woid character varying, paymentdate date, jenis_service character varying, harga double precision, discount double precision, total double precision, dpp double precision, ppn double precision)
 LANGUAGE plpgsql
AS $function$
declare

        startdate Date := to_date(startdate, 'DD/MM/YYYY');--startdate::Date;
        enddate timestamp := (to_date(enddate, 'DD/MM/YYYY')::timestamp + interval '1 day - 0 second');
               ppn_rate_value double precision;
        
--        startdate2 character varying := to_char(to_date(fromdate, 'DD/MM/YYYY'), 'YYYY-MM-DD');
----        enddate2 character varying := to_char(to_date(todate, 'DD/MM/YYYY'), 'YYYY-MM-DD');
--        enddate2 character varying := to_char(enddate, 'YYYY-MM-DD');

begin
        
                SELECT ppn_rate 
            INTO ppn_rate_value 
            FROM mst_ppn 
            WHERE status = 'Active' 
            ORDER BY id DESC 
            LIMIT 1;
                
--        drop table temp_dealer
        DECLARE
                  start_time2 TIMESTAMP := clock_timestamp();
                  execution_time2 interval;
        begin
        
        create temp table temp_dealer 
        on commit DROP 
        as
        select
        ahmdealercode,
        dealercode 
        from fdw_dealer fd 
        where fd.ahmdealercode = kodeahass
        ;
        raise notice 'log message 1 %', clock_timestamp() - start_time2;
        end;
        
        
--        drop table temp_wo_monitoring_0
        
        DECLARE
                  start_time3 TIMESTAMP := clock_timestamp();
                  execution_time3 interval;
        begin
        create temp table temp_wo_monitoring_0 
        on commit DROP 
        as
        select 
        td.ahmdealercode as ahassid,
        vwm.woid,
        vwm.createdat
        from fdw_wo_monitoring vwm, temp_dealer td
        where 
          td.ahmdealercode = kodeahass
          and vwm.wolastknownstatus in ('PAYMENT', 'COMPLETE')
--          and vwm.createdat >= startdate
--          and vwm.createdat < enddate
          and vwm.createdat >= '2022-12-01 00:00:00'
          and split_part(vwm.woid, '-', 2) = td.ahmdealercode
        ;
        CREATE INDEX ix_temp_wo_monitoring_0_ahassid_woid ON temp_wo_monitoring_0 USING btree (ahassid, woid);
        raise notice 'log message 2 %', clock_timestamp() - start_time3;
        end;

--        drop table temp_wo_monitoring;
        
DECLARE
                  start_time4 TIMESTAMP := clock_timestamp();
                  execution_time4 interval;
        begin
        create temp table temp_wo_monitoring 
        on commit DROP 
        as
        select 
        vwm.ahassid,
        vwm.woid,
        min(vwm.createdat) as createdat
        from temp_wo_monitoring_0 vwm
        where 
         vwm.ahassid = kodeahass
        group by vwm.ahassid, vwm.woid
        ;
        CREATE INDEX ix_temp_wo_monitoring_ahassid_woid ON temp_wo_monitoring USING btree (ahassid, woid);
                raise notice 'log message 3 %', clock_timestamp() - start_time4;
        end;
                
--        drop table temp_report_penjualan_by_invoice;
        --truncate temp_report_penjualan_by_invoice;
                
        DECLARE
                  start_time5 TIMESTAMP := clock_timestamp();
                  execution_time5 interval;
        begin
        CREATE TEMP TABLE temp_report_penjualan_by_invoice(
        ahassid varchar(255) NOT NULL,
        woid varchar(255),
        kpb boolean default false,
    sumber varchar(255),
        paymentdate timestamp,
        invoice varchar(255),
        createdat timestamp NOT NULL,
        qty bigint,
        price float8,
        totalprice float8,
        discount float8,
        ppnamount float8,
        ppnrate float8,
        c2 boolean
        )
        on commit DROP
        ;
        CREATE INDEX ix_temp_report_penjualan_by_invoice_ahassid ON temp_report_penjualan_by_invoice USING btree (ahassid);
        CREATE INDEX ix_temp_report_penjualan_by_invoice_woid ON temp_report_penjualan_by_invoice USING btree (woid);
        raise notice 'log message 4 %', clock_timestamp() - start_time5;
end;

        DECLARE
                  start_time6 TIMESTAMP := clock_timestamp();
                  execution_time6 interval;
        begin
        insert into temp_report_penjualan_by_invoice (
        ahassid, woid, kpb, sumber, paymentdate, invoice, createdat, qty, price, totalprice, discount, ppnamount, ppnrate, c2)
        select
        twm.ahassid,
        twm.woid,
        fws.kpb,
        fws.source,
        twm.createdat,
        fwo.invoice,
        fwo.createdat,
        fws.qty,
        fws.harga,
        fws.total,
        fws.discount,
        fws.ppnamount,
        fws.ppnrate,
        fws.c2 
        from temp_wo_monitoring twm, fdw_wo fwo, fdw_woservice fws
        where 
          twm.ahassid = fwo.ahassid 
          and twm.woid = fwo.woid
          and twm.ahassid = kodeahass
          and twm.woid = fws.woid
          and fwo.wolastknownstatus != 'CANCEL'
          and twm.createdat >= startdate
          and twm.createdat < enddate;
        
         raise notice 'log message 5 %', clock_timestamp() - start_time6;
end;
--        explain
--        update temp_report_penjualan_by_invoice trp
--        set  
--        paymentdate = date(fpy.paymentdate)
--        from fdw_payment fpy
--        where 
--          trp.woid = fpy.woid;

         
         
--        update temp_report_penjualan_by_invoice trp
--        set 
--        kpb = fws.kpb, 
--        sumber = fws.source,
--        qty = fws.qty,
--        price = fws.harga,
--        totalprice = fws.total,
--        discount = fws.discount,
--        ppnamount = fws.ppnamount,
--        ppnrate = fws.ppnrate 
--        from fdw_woservice fws
--        where trp.woid = fws.woid
--           and fws.createdat >= startdate;
          
          
                          
         DECLARE
                  start_time7 TIMESTAMP := clock_timestamp();
                  execution_time7 interval;
        begin
        RETURN QUERY
                select 
                trp.invoice as noinvoice, 
                trp.woid as woid,
                trp.paymentdate, 
                (select trpi.sumber from temp_report_penjualan_by_invoice trpi where trp.invoice = trpi.invoice limit 1) as jenis_service, 
                sum(trp.price) as harga, 
                sum(trp.discount) as discount,
                sum(trp.totalprice) as total,
                sum(round(trp.totalprice - trp.ppnamount)) as dpp,
                sum(round(trp.ppnamount)) as ppn
                from
                (select
                        trp.ahassid,
                        trp.invoice, 
                        date(trp.paymentdate) as paymentdate, 
                        trp.woid,
                        trp.sumber,  
                        trp.qty,
                        trp.price, 
                        trp.discount,
                        case 
                                when (trp.totalprice = 0) then trp.qty * trp.price - trp.discount
                                when (trp.totalprice != 0) then trp.totalprice
                        end
                        as totalprice,
--                        case 
--                                when (trp.ppnamount is null) then round(trp.totalprice / (1+0.11)*(0.11))
--                                else trp.ppnamount
--                        end
--                        as ppnamount
                        trp.ppnamount as ppnamount
                from temp_report_penjualan_by_invoice trp
                where trp.ahassid = kodeahass
                        and not (trp.kpb is true and trp.sumber in ('KPB', 'KPB_SATUAN'))
                        and not (trp.c2 is true)
                        ) trp
                group by trp.invoice, trp.woid, trp.paymentdate
                order by trp.invoice;
        raise notice 'log message 6 %', clock_timestamp() - start_time7;
end;
end;
$function$
;
Editor is loading...
Leave a Comment