

CREATE OR REPLACE FUNCTION loan_repayment_func( as_at timestamp with time zone)
  RETURNS TABLE(customer_type_id bigint,
				customer_type character varying(150),
				organisation_id bigint,
				has_members boolean,
				customer_type_date_added timestamp with time zone,
				customer_id bigint,
				name character varying(150) ,
				member_number character varying(150),
				old_member_number character varying(150),
				customer_status character varying(150),
				branch_id bigint,
				branch_name character varying(150),
				branch_short_name character varying(150),
				id bigint,
				loan_amount double precision,
				loan_date_added timestamp with time zone,
				reason character varying(1556),
				status character varying(150),
				int_rate double precision,
				loan_date timestamp with time zone,
				app_grace_period int,
				int_method character varying(150),
				is_deleted boolean,
				loan_sector_id bigint,
				loan_product_id bigint,
				product_name character varying(150),
				product_int_rate double precision,
				product_int_method character varying(150),
				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 int,
				write_off_grace_period int,
				arrears_period_type character varying(150),
				write_off_period_type character varying(150),
				loan_officer_id bigint, 
				loan_officer_full_name character varying(150),
				gender character varying(150),
				telephone character varying(150),
				princ_paid double precision,
				int_paid double precision,
                penalty_paid double precision,
				total_penalty double precision,
				penalty_waivered double precision,
				interest_waivered double precision
			   ) AS
$func$
BEGIN

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, 0) AS arrear_grace_period,
		COALESCE(loan_disbursed.write_off_grace_period, 0) AS write_off_grace_period,
		COALESCE(loan_disbursed.arrears_period_type, ''d''::character varying) AS arrears_period_type,
		COALESCE(loan_disbursed.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 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 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 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 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 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 loan_interest_waivered.date_added <= %L ), 0::double precision) AS interest_waivered
		
	 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
     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);

-- return the rows of view tmp
RETURN QUERY
SELECT * FROM tmp;

END
$func$  LANGUAGE plpgsql;

SELECT * FROM loan_repayment_func('2023-02-15 12:00');


CREATE OR REPLACE FUNCTION public.arrear_days(
	loan_id bigint, arrear_grace_period integer, arrear_grace_period_type character varying, as_at timestamp with time zone)
    RETURNS bigint
	LANGUAGE 'plpgsql'
AS $BODY$
  DECLARE
    temprow RECORD;
  	arrear_days bigint :=0;
	total_princ_payments double precision :=0;
	arrear_date timestamp with time zone :=now();
	arrear_days_count bigint :=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

			  SELECT sum(princ_paid) INTO total_princ_payments FROM loan_payments WHERE loan_repayment_schedule_id = temprow.id AND payment_date <= as_at;

			  if temprow.principal_expected > total_princ_payments 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;
				 else
				  RETURN 0;
				 END if;
			  END if;
			 END LOOP;
			 
		  RETURN arrear_days;
		END;
$BODY$;




CREATE OR REPLACE FUNCTION loan_repayment_func( as_at timestamp with time zone)
  RETURNS TABLE(customer_type_id bigint,
				customer_type character varying(150),
				organisation_id bigint,
				has_members boolean,
				customer_type_date_added timestamp with time zone,
				customer_id bigint,
				name character varying(150) ,
				member_number character varying(150),
				old_member_number character varying(150),
				customer_status character varying(150),
				branch_id bigint,
				branch_name character varying(150),
				branch_short_name character varying(150),
				id bigint,
				loan_amount double precision,
				loan_date_added timestamp with time zone,
				reason character varying(1556),
				status character varying(150),
				int_rate double precision,
				loan_date timestamp with time zone,
				app_grace_period int,
				int_method character varying(150),
				is_deleted boolean,
				loan_sector_id bigint,
				loan_product_id bigint,
				product_name character varying(150),
				product_int_rate double precision,
				product_int_method character varying(150),
				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 int,
				write_off_grace_period int,
				arrears_period_type character varying(150),
				write_off_period_type character varying(150),
				loan_officer_id bigint, 
				loan_officer_full_name character varying(150),
				gender character varying(150),
				telephone character varying(150),
				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
			   ) AS
$func$
BEGIN

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, 0) AS arrear_grace_period,
		COALESCE(loan_disbursed.write_off_grace_period, 0) AS write_off_grace_period,
		COALESCE(loan_disbursed.arrears_period_type, ''d''::character varying) AS arrears_period_type,
		COALESCE(loan_disbursed.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 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 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 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 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 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 loan_interest_waivered.date_added <= %L ), 0::double precision) AS interest_waivered,
		arrear_days(lap.id, loan_disbursed.arrear_grace_period, loan_disbursed.arrears_period_type, %L) AS arrear_days
			   
	 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
     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);

-- return the rows of view tmp
RETURN QUERY
SELECT * FROM tmp;

END
$func$  LANGUAGE plpgsql;

SELECT * FROM loan_repayment_func('2023-02-15');


CREATE OR REPLACE VIEW public.loans_repayment_view
 AS
SELECT * FROM loan_repayment_func(CAST(now() as timestamp without time zone));



CREATE OR REPLACE FUNCTION public.loan_repayment_func(
	as_at timestamp with time zone,
	start_date timestamp with time zone DEFAULT NULL::timestamp with 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 with 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, 0) AS arrear_grace_period,
			COALESCE(loan_disbursed.write_off_grace_period, 0) AS write_off_grace_period,
			COALESCE(loan_disbursed.arrears_period_type, ''d''::character varying) AS arrears_period_type,
			COALESCE(loan_disbursed.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 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 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 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 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 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 loan_interest_waivered.date_added <= %L ), 0::double precision) AS interest_waivered,
			arrear_days(lap.id, loan_disbursed.arrear_grace_period, loan_disbursed.arrears_period_type, %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, 0) AS arrear_grace_period,
			COALESCE(loan_disbursed.write_off_grace_period, 0) AS write_off_grace_period,
			COALESCE(loan_disbursed.arrears_period_type, ''d''::character varying) AS arrears_period_type,
			COALESCE(loan_disbursed.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 loan_payments.payment_date <= %L AND 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 loan_payments.payment_date <= %L AND 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 loan_payments.payment_date <= %L AND 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 loan_penalty.date_added <= %L AND 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 loan_penalty_waivered.date_added <= %L AND 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 loan_interest_waivered.date_added <= %L AND loan_interest_waivered.date_added >= %L ), 0::double precision) AS interest_waivered,
			arrear_days(lap.id, loan_disbursed.arrear_grace_period, loan_disbursed.arrears_period_type, %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$;
$func$  LANGUAGE plpgsql;



SELECT * FROM loan_repayment_func('2023-02-15 12:00');



CREATE OR REPLACE FUNCTION public.arrear_days(
	loan_id bigint, arrear_grace_period integer, arrear_grace_period_type character varying, as_at timestamp with time zone, start_date timestamp with time zone default null)
    RETURNS bigint
	LANGUAGE 'plpgsql'
AS $BODY$
  DECLARE
    temprow RECORD;
  	arrear_days bigint :=0;
	total_princ_payments double precision :=0;
	arrear_date timestamp with time zone :=now();
	arrear_days_count bigint :=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 payment_date <= as_at;
			  else
			  	SELECT sum(princ_paid) INTO total_princ_payments FROM loan_payments WHERE loan_repayment_schedule_id = temprow.id AND payment_date <= as_at AND payment_date >= start_date;
			  END if;
			  
			  if temprow.principal_expected > total_princ_payments 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;
				 else
				  RETURN 0;
				 END if;
			  END if;
			 END LOOP;
			 
		  RETURN arrear_days;
		END;
$BODY$;


CREATE OR REPLACE FUNCTION public.write_off_days(
	loan_start_date timestamp with time zone, arrear_grace_period integer, write_off_period_type character varying)
    RETURNS bigint
	LANGUAGE 'plpgsql'
AS $BODY$
  DECLARE
	write_off_date timestamp with time zone :=now();
	write_off_days_count bigint :=0;
	loan_approval.loan_period
	
        BEGIN 
			if write_off_period_type = 'd' then
				write_off_date = loan + make_interval(days => COALESCE(write_off_grace_period, 0));
			
			elsif write_off_period_type = 'w' then
				write_off_date = loan_start_date + make_interval(weeks => COALESCE(write_off_grace_period, 0));
			
			elsif write_off_period_type = 'bw' then
				write_off_date = loan_start_date + make_interval(weeks => COALESCE(write_off_grace_period*2, 0));

			elsif write_off_period_type = 'm' then
				write_off_date = loan_start_date + make_interval(months => COALESCE(write_off_grace_period, 0));

			elsif write_off_period_type = 'q' then
				write_off_date = loan_start_date + make_interval(months => COALESCE(write_off_grace_period*3, 0));

			elsif write_off_period_type = 'y' then
				write_off_date = loan_start_date + make_interval(years => COALESCE(write_off_grace_period, 0));
		    END if;
			write_off_days_count:= loan_start_date::DATE - write_off_date::DATE;
			RETURN write_off_days_count;
		END;
$BODY$;