CREATE OR REPLACE FUNCTION "public"."fnc_laproa"("user_id" int4, "var_id_bpr" int4, "var_id_bpr_cabang" int4, "tahundate" text, "bulandate" text)
RETURNS "pg_catalog"."text" AS $BODY$
DECLARE
titles TEXT DEFAULT '';
finalreport record;
loopneraca "temporary".tmp_proses_roa_awal%ROWTYPE;
BEGIN
---
delete from "temporary".tmp_proses_roa_awal where userid = user_id;
delete from "temporary".tmp_userid;
--create table "temporary".tmp_proses_roa_awal as
insert into "temporary".tmp_proses_roa_awal
(userid, tglawalbulan, tglakhirbulan, totalaset, labasebelumpajak)
select
user_id userid,
tglawalbulan, (date_trunc('month', tglawalbulan::date) + interval '1 month' - interval '1 day')::date tglakhirbulan,
0 totalaset,
0 labasebelumpajak
from (
select unnest(array[satu, dua, tiga, empat, lima, enam, tujuh, delapan, sembilan, sepuluh, sebelas, duabelas]) tglawalbulan from (
select
((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '11 month'):: date) satu,
((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '10 month'):: date) dua,
((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '9 month'):: date) tiga,
((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '8 month'):: date) empat,
((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '7 month'):: date) lima,
((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '6 month'):: date) enam,
((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '5 month'):: date) tujuh,
((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '4 month'):: date) delapan,
((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '3 month'):: date) sembilan,
((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '2 month'):: date) sepuluh,
((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '1 month'):: date) sebelas,
( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) duabelas) xxx) tglawalakhir;
for loopneraca in
select * from "temporary".tmp_proses_roa_awal where userid = user_id
loop
--- CARA PEMANGGILAN FUNCTION ---
insert into "temporary".tmp_userid
select fnc_neraca_atmr(loopneraca.userid, var_id_bpr, var_id_bpr_cabang, to_char(loopneraca.tglakhirbulan,'yyyy-mm-dd'), to_char(loopneraca.tglakhirbulan,'yyyy-mm-dd'), to_char(loopneraca.tglakhirbulan,'yyyymm'));
--- CARA PEMANGGILAN FUNCTION ---
update "temporary".tmp_proses_roa_awal
set totalaset=(select COALESCE(sum(saldoawal+debet-kredit),0) saldo from "temporary".tmp_neraca_atmr where userid = user_id and left(nomor_akun,1)='1')
where userid = user_id and tglakhirbulan=loopneraca.tglakhirbulan;
end loop;
select userid into finalreport from "temporary".tmp_proses_roa_awal where userid = user_id limit 1;
RETURN finalreport;
END; $BODY$
LANGUAGE plpgsql VOLATILE
COST 100
