report_penjualanjasa_by_noinvoice_v4
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