-- FUNCTION: public.arrear_days(bigint, integer, character varying, timestamp without time zone, timestamp without time zone) -- DROP FUNCTION IF EXISTS public.arrear_days(bigint, integer, character varying, timestamp without time zone, timestamp without time zone); CREATE OR REPLACE FUNCTION public.arrear_days( loan_id bigint, arrear_grace_period integer, arrear_grace_period_type character varying, as_at timestamp without time zone, start_date timestamp without time zone DEFAULT NULL::timestamp without time zone) RETURNS bigint LANGUAGE 'plpgsql' COST 100 VOLATILE PARALLEL UNSAFE AS $BODY$ DECLARE temprow RECORD; arrear_days bigint :=0; total_princ_payments double precision :=0; total_int_payments double precision :=0; arrear_date timestamp without time zone :=now(); arrear_days_count bigint :=0; total_interest_waivered double precision :=0; BEGIN FOR temprow IN SELECT * FROM loan_repayments_schedule WHERE loan_application_id = loan_id AND status = 'active' ORDER BY payment_number ASC LOOP if start_date is null then SELECT sum(princ_paid) INTO total_princ_payments FROM loan_payments WHERE loan_repayment_schedule_id = temprow.id AND date(payment_date) <= as_at; SELECT sum(int_paid) INTO total_int_payments FROM loan_payments WHERE loan_repayment_schedule_id = temprow.id AND date(payment_date) <= as_at; SELECT sum(amount) INTO total_interest_waivered FROM loan_interest_waivered WHERE loan_application_id = loan_id AND loan_repayment_schedule_id = temprow.id AND date(date_added) <= as_at; else SELECT sum(princ_paid) INTO total_princ_payments FROM loan_payments WHERE loan_repayment_schedule_id = temprow.id AND date(payment_date) <= as_at AND date(payment_date) >= start_date; SELECT sum(int_paid) INTO total_int_payments FROM loan_payments WHERE loan_repayment_schedule_id = temprow.id AND date(payment_date) <= as_at AND date(payment_date) >= start_date; SELECT sum(amount) INTO total_interest_waivered FROM loan_interest_waivered WHERE loan_application_id = loan_id AND loan_repayment_schedule_id = temprow.id AND date(date_added) <= as_at AND date(date_added) >= start_date; END if; total_princ_payments:= COALESCE(total_princ_payments, 0); total_int_payments:= COALESCE(total_int_payments, 0); total_interest_waivered:=COALESCE(total_interest_waivered, 0); if temprow.principal_expected > total_princ_payments or temprow.interest_expected > (total_int_payments + total_interest_waivered) then if arrear_grace_period_type = 'd' then arrear_date:= temprow.expected_date + make_interval(days => COALESCE(arrear_grace_period, 0)); elsif arrear_grace_period_type = 'w' then arrear_date:= temprow.expected_date + make_interval(weeks => COALESCE(arrear_grace_period, 0)); elsif arrear_grace_period_type = 'bw' then arrear_date:= temprow.expected_date + make_interval(weeks => COALESCE(arrear_grace_period*2, 0)); elsif arrear_grace_period_type = 'm' then arrear_date:= temprow.expected_date + make_interval(months => COALESCE(arrear_grace_period, 0)); elsif arrear_grace_period_type = 'q' then arrear_date:= temprow.expected_date + make_interval(months => COALESCE(arrear_grace_period*3, 0)); elsif arrear_grace_period_type = 'y' then arrear_date:= temprow.expected_date + make_interval(years => COALESCE(arrear_grace_period, 0)); END if; arrear_days_count:= as_at::DATE - arrear_date::DATE; if arrear_days_count > 0 then RETURN arrear_days_count; END if; END if; END LOOP; RETURN arrear_days; END; $BODY$; -- FUNCTION: public.loan_repayment_func(timestamp without time zone, timestamp without time zone) -- DROP FUNCTION IF EXISTS public.loan_repayment_func(timestamp without time zone, timestamp without time zone); CREATE OR REPLACE FUNCTION public.loan_repayment_func( as_at timestamp without time zone, start_date timestamp without time zone DEFAULT NULL::timestamp without time zone) RETURNS TABLE(customer_type_id bigint, customer_type character varying, organisation_id bigint, has_members boolean, customer_type_date_added timestamp with time zone, customer_id bigint, name character varying, member_number character varying, old_member_number character varying, customer_status character varying, branch_id bigint, branch_name character varying, branch_short_name character varying, id bigint, loan_amount double precision, loan_date_added timestamp with time zone, reason character varying, status character varying, int_rate double precision, loan_date timestamp with time zone, app_grace_period integer, int_method character varying, is_deleted boolean, loan_sector_id bigint, loan_product_id bigint, product_name character varying, product_int_rate double precision, product_int_method character varying, approval_amount double precision, approval_date timestamp with time zone, disbursed_amount double precision, loan_start_date timestamp with time zone, loan_disbursement_date timestamp with time zone, total_interest_expected double precision, total_principal_expected double precision, total_expected double precision, arrear_grace_period integer, write_off_grace_period integer, arrears_period_type character varying, write_off_period_type character varying, loan_officer_id bigint, loan_officer_full_name character varying, gender character varying, telephone character varying, princ_paid double precision, int_paid double precision, penalty_paid double precision, total_penalty double precision, penalty_waivered double precision, interest_waivered double precision, arrear_days bigint,, write_off_days bigint, written_off_date timestamp without time zone, write_off_comment character varying, loan_writen_off_ammount double precision) LANGUAGE 'plpgsql' COST 100 VOLATILE PARALLEL UNSAFE ROWS 1000 AS $BODY$ BEGIN if start_date is null then EXECUTE format('CREATE OR REPLACE TEMP VIEW tmp AS SELECT ct.id AS customer_type_id, ct.customer_type, ct.organisation_id, ct.has_members, ct.date_added AS customer_type_date_added, cr.id AS customer_id, cr.name, cr.member_number, cr.old_member_number, cr.status AS customer_status, lap.organisation_branch_id AS branch_id, ob.name AS branch_name, ob.short_name AS branch_short_name, lap.id, lap.loan_amount, lap.date_added AS loan_date_added, lap.reason, lap.status, lap.int_rate, lap.loan_date, lap.app_grace_period, lap.int_method, lap.is_deleted, lap.loan_sector_id, lps.id AS loan_product_id, lps.product_name, lps.int_rate AS product_int_rate, lps.int_method AS product_int_method, loan_approval.loan_amount AS approval_ammount, loan_approval.approval_date, loan_disbursed.loan_amount AS disbursed_ammount, loan_disbursed.loan_start_date, loan_disbursed.loan_disbursement_date, loan_disbursed.total_interest_expected, loan_disbursed.total_principal_expected, loan_disbursed.total_expected, COALESCE(loan_disbursed.arrear_grace_period, lps.arrears_period, 0) AS arrear_grace_period, COALESCE(loan_disbursed.write_off_grace_period, lps.write_off_period, 0) AS write_off_grace_period, COALESCE(loan_disbursed.arrears_period_type, lps.arrears_period_type,''d''::character varying) AS arrears_period_type, COALESCE(loan_disbursed.write_off_period_type, lps.write_off_period_type, ''d''::character varying) AS write_off_period_type, sf.id AS loan_officer_id, sf.name AS loan_officer_full_name, cr.gender, cr.telephone, COALESCE(( SELECT sum(loan_payments.princ_paid) AS sum FROM loan_payments WHERE loan_payments.loan_application_id = lap.id AND date(loan_payments.payment_date) <= %L ), 0::double precision) AS princ_paid, COALESCE(( SELECT sum(loan_payments.int_paid) AS sum FROM loan_payments WHERE loan_payments.loan_application_id = lap.id AND date(loan_payments.payment_date) <= %L ), 0::double precision) AS int_paid, COALESCE(( SELECT sum(loan_payments.penalty_paid) AS sum FROM loan_payments WHERE loan_payments.loan_application_id = lap.id AND date(loan_payments.payment_date) <= %L ), 0::double precision) AS penalty_paid, COALESCE(( SELECT sum(loan_penalty.amount) AS sum FROM loan_penalty WHERE loan_penalty.loan_application_id = lap.id AND date(loan_penalty.date_added) <= %L ), 0::double precision) AS total_penalty, COALESCE(( SELECT sum(loan_penalty_waivered.amount) AS sum FROM loan_penalty_waivered WHERE loan_penalty_waivered.loan_application_id = lap.id AND date(loan_penalty_waivered.date_added) <= %L ), 0::double precision) AS penalty_waivered, COALESCE(( SELECT sum(loan_interest_waivered.amount) AS sum FROM loan_interest_waivered WHERE loan_interest_waivered.loan_application_id = lap.id AND date(loan_interest_waivered.date_added) <= %L ), 0::double precision) AS interest_waivered, arrear_days(lap.id, COALESCE(loan_disbursed.arrear_grace_period, lps.arrears_period, 0), COALESCE(loan_disbursed.arrears_period_type, lps.arrears_period_type,''d''::character varying), %L) AS arrear_days, write_off_days(loan_disbursed.loan_start_date,loan_approval.loan_period,loan_approval.period_type, loan_disbursed.write_off_grace_period,loan_disbursed.write_off_period_type,%L) AS write_off_days, written_off.write_off_date AS written_off_date, written_off.description AS write_off_comment, written_off.loan_writeoff_ammount AS loan_writen_off_ammount FROM customer_type ct JOIN customer cr ON cr.branch_customer_type_id = ct.id JOIN organisation_branch ob ON ob.id = cr.customer_branch_id JOIN loan_applications lap ON lap.customer_id = cr.id LEFT JOIN loan_application_approval loan_approval ON loan_approval.loan_application_id = lap.id LEFT JOIN loan_application_disbursement loan_disbursed ON loan_disbursed.loan_application_id = lap.id LEFT JOIN loan_written_off written_off ON written_off.loan_application_id = lap.id JOIN loan_products lps ON lps.id = lap.loan_application_product_id JOIN staff sf ON sf.id = lap.loan_officer_id', as_at, as_at, as_at, as_at, as_at, as_at, as_at, as_at); else EXECUTE format('CREATE OR REPLACE TEMP VIEW tmp AS SELECT ct.id AS customer_type_id, ct.customer_type, ct.organisation_id, ct.has_members, ct.date_added AS customer_type_date_added, cr.id AS customer_id, cr.name, cr.member_number, cr.old_member_number, cr.status AS customer_status, lap.organisation_branch_id AS branch_id, ob.name AS branch_name, ob.short_name AS branch_short_name, lap.id, lap.loan_amount, lap.date_added AS loan_date_added, lap.reason, lap.status, lap.int_rate, lap.loan_date, lap.app_grace_period, lap.int_method, lap.is_deleted, lap.loan_sector_id, lps.id AS loan_product_id, lps.product_name, lps.int_rate AS product_int_rate, lps.int_method AS product_int_method, loan_approval.loan_amount AS approval_ammount, loan_approval.approval_date, loan_disbursed.loan_amount AS disbursed_ammount, loan_disbursed.loan_start_date, loan_disbursed.loan_disbursement_date, loan_disbursed.total_interest_expected, loan_disbursed.total_principal_expected, loan_disbursed.total_expected, COALESCE(loan_disbursed.arrear_grace_period, lps.arrears_period, 0) AS arrear_grace_period, COALESCE(loan_disbursed.write_off_grace_period,lps.write_off_period, 0) AS write_off_grace_period, COALESCE(loan_disbursed.arrears_period_type,lps.arrears_period_type, ''d''::character varying) AS arrears_period_type, COALESCE(loan_disbursed.write_off_period_type,lps.write_off_period_type, ''d''::character varying) AS write_off_period_type, sf.id AS loan_officer_id, sf.name AS loan_officer_full_name, cr.gender, cr.telephone, COALESCE(( SELECT sum(loan_payments.princ_paid) AS sum FROM loan_payments WHERE loan_payments.loan_application_id = lap.id AND date(loan_payments.payment_date) <= %L AND date(loan_payments.payment_date) >= %L ), 0::double precision) AS princ_paid, COALESCE(( SELECT sum(loan_payments.int_paid) AS sum FROM loan_payments WHERE loan_payments.loan_application_id = lap.id AND date(loan_payments.payment_date) <= %L AND date(loan_payments.payment_date) >= %L ), 0::double precision) AS int_paid, COALESCE(( SELECT sum(loan_payments.penalty_paid) AS sum FROM loan_payments WHERE loan_payments.loan_application_id = lap.id AND date(loan_payments.payment_date) <= %L AND date(loan_payments.payment_date) >= %L ), 0::double precision) AS penalty_paid, COALESCE(( SELECT sum(loan_penalty.amount) AS sum FROM loan_penalty WHERE loan_penalty.loan_application_id = lap.id AND date(loan_penalty.date_added) <= %L AND date(loan_penalty.date_added) >= %L ), 0::double precision) AS total_penalty, COALESCE(( SELECT sum(loan_penalty_waivered.amount) AS sum FROM loan_penalty_waivered WHERE loan_penalty_waivered.loan_application_id = lap.id AND date(loan_penalty_waivered.date_added) <= %L AND date(loan_penalty_waivered.date_added) >= %L ), 0::double precision) AS penalty_waivered, COALESCE(( SELECT sum(loan_interest_waivered.amount) AS sum FROM loan_interest_waivered WHERE loan_interest_waivered.loan_application_id = lap.id AND date(loan_interest_waivered.date_added) <= %L AND date(loan_interest_waivered.date_added) >= %L ), 0::double precision) AS interest_waivered, arrear_days(lap.id, COALESCE(loan_disbursed.arrear_grace_period, lps.arrears_period, 0), COALESCE(loan_disbursed.arrears_period_type, lps.arrears_period_type,''d''::character varying), %L, %L) AS arrear_days, write_off_days(loan_disbursed.loan_start_date,loan_approval.loan_period,loan_approval.period_type, loan_disbursed.write_off_grace_period,loan_disbursed.write_off_period_type,%L) AS write_off_days, written_off.write_off_date AS written_off_date, written_off.description AS write_off_comment, written_off.loan_writeoff_ammount AS loan_writen_off_ammount FROM customer_type ct JOIN customer cr ON cr.branch_customer_type_id = ct.id JOIN organisation_branch ob ON ob.id = cr.customer_branch_id JOIN loan_applications lap ON lap.customer_id = cr.id LEFT JOIN loan_application_approval loan_approval ON loan_approval.loan_application_id = lap.id LEFT JOIN loan_application_disbursement loan_disbursed ON loan_disbursed.loan_application_id = lap.id LEFT JOIN loan_written_off written_off ON written_off.loan_application_id = lap.id JOIN loan_products lps ON lps.id = lap.loan_application_product_id JOIN staff sf ON sf.id = lap.loan_officer_id', as_at, start_date, as_at, start_date,as_at,start_date,as_at,start_date,as_at,start_date,as_at,start_date,as_at,start_date,as_at); END if; -- return the rows of view tmp RETURN QUERY SELECT * FROM tmp; END $BODY$; -- FUNCTION: public.write_off_days(timestamp with time zone, integer, character varying, integer, character varying, timestamp with time zone) -- DROP FUNCTION IF EXISTS public.write_off_days(timestamp with time zone, integer, character varying, integer, character varying, timestamp with time zone); CREATE OR REPLACE FUNCTION public.write_off_days( loan_start_date timestamp with time zone, loan_period integer, loan_period_type character varying, write_off_grace_period integer, write_off_period_type character varying, as_at timestamp with time zone) RETURNS bigint LANGUAGE 'plpgsql' COST 100 VOLATILE PARALLEL UNSAFE AS $BODY$ DECLARE loan_end_date timestamp with time zone :=(now() + make_interval(days => 1)); write_off_date timestamp with time zone :=(now() + make_interval(days => 1)); write_off_days bigint :=0; BEGIN if as_at is null then as_at = now(); END if; if loan_period_type is null then loan_period_type = 'd'; END if; if write_off_period_type is null then write_off_period_type = 'd'; END if; if loan_period_type = 'd' then loan_end_date = loan_start_date + make_interval(days => COALESCE(loan_period, 0)); elsif loan_period_type = 'w' then loan_end_date = loan_start_date + make_interval(weeks => COALESCE(loan_period, 0)); elsif loan_period_type = 'bw' then loan_end_date = loan_start_date + make_interval(weeks => COALESCE(loan_period*2, 0)); elsif loan_period_type = 'm' then loan_end_date = loan_start_date + make_interval(months => COALESCE(loan_period, 0)); elsif loan_period_type = 'q' then loan_end_date = loan_start_date + make_interval(months => COALESCE(loan_period*3, 0)); elsif loan_period_type = 'y' then loan_end_date = loan_start_date + make_interval(years => COALESCE(loan_period, 0)); END if; if write_off_period_type = 'd' then write_off_date = loan_end_date::DATE + make_interval(days => COALESCE(write_off_grace_period, 0)); elsif write_off_period_type = 'w' then write_off_date = loan_end_date::DATE + make_interval(weeks => COALESCE(write_off_grace_period, 0)); elsif write_off_period_type = 'bw' then write_off_date = loan_end_date::DATE + make_interval(weeks => COALESCE(write_off_grace_period*2, 0)); elsif write_off_period_type = 'm' then write_off_date = loan_end_date::DATE + make_interval(months => COALESCE(write_off_grace_period, 0)); elsif write_off_period_type = 'q' then write_off_date = loan_end_date::DATE + make_interval(months => COALESCE(write_off_grace_period*3, 0)); elsif write_off_period_type = 'y' then write_off_date = loan_end_date::DATE + make_interval(years => COALESCE(write_off_grace_period, 0)); END if; write_off_days:= as_at::DATE - write_off_date::DATE; RETURN write_off_days; END; $BODY$;