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