CREATE OR REPLACE VIEW public.location_view
 AS
SELECT r.id as regionid,
   r.regionname,
   d.id as districtid,
   d.districtname,
   d.hckey,
   ct.id as countyid,
   ct.countyname,
   sb.id as subcountyid,
   sb.subcountyname,
   ph.id as parishid,
   ph.parishname,
   v.id,
   v.villagename

FROM region r
   JOIN district d ON (r.id=d.region_id)
   JOIN county ct ON (d.id=ct.district_id)
   JOIN subcounty sb ON (ct.id=sb.county_id)
   JOIN parish ph ON (sb.id=ph.subcounty_id)
   JOIN village v ON (ph.id=v.parish_id);

CREATE OR REPLACE VIEW public.organisation_feature_view
 AS
 SELECT comp.id,
    sc.id AS component_id,
    sc.component,
    sc.component_desc AS "desc",
    sc.type,
    sc.key,
    sc.parent_component AS parent,
    sc.is_active AS is_feature_active,
    comp.date_added,
    comp.is_active,
    org.id AS org_id,
    org.name AS org_name,
    org.short_name AS org_short_name
   FROM system_component sc
     JOIN org_component comp ON comp.system_component_id = sc.id
     JOIN organisation org ON org.id = comp.component_org_id;

CREATE OR REPLACE VIEW public.user_permissions_view
 AS
 SELECT uar.id,
    ur.role_name,
    ur.role_desc,
    ur.is_active AS is_role_active,
    rc.is_active AS is_role_component_active,
    uar.date_added,
    uar.user_id,
    uar.assigned_role_added_by_id,
    uar.is_active AS is_user_role_active,
    comp.component_org_id AS org_id,
    comp.is_active AS is_org_comp_active,
    sc.id AS permission_id,
    sc.component AS permission,
    sc.component_desc AS "desc",
    sc.type,
    sc.key,
    sc.parent_component AS parent,
    sc.is_active AS is_feature_active
   FROM user_role ur
     JOIN role_component rc ON rc.user_role_id = ur.id
     JOIN user_assigned_role uar ON uar.assigned_role_id = ur.id
     JOIN org_component comp ON comp.id = rc.org_component_id
     JOIN system_component sc ON comp.system_component_id = sc.id;

DROP VIEW public.loanapplicationsview;

CREATE OR REPLACE VIEW public.loanapplicationsview
 AS
 SELECT lapp.id,
    lapp.loan_amount,
    lapp.date_added,
    lapp.reason,
    lapp.app_grace_period,
    lapp.loan_date,
    lapp.status,
    lapp.int_rate,
    lapp.int_method,
    cust.id AS customer_id,
    cust.name,
    cust.member_number,
    cust.old_member_number,
    cust.status AS customer_status,
    lp.id AS loan_product_id,
    lp.product_name,
    cust.customer_branch_id,
    cust_org_b.name AS customer_branch_name,
    loan_org_b.name AS loan_branch_name,
    lapp.organisation_branch_id AS loan_branch_id,
    lapp.is_deleted,
	 org.id as organisation_id
   FROM loan_applications lapp
     JOIN customer cust ON lapp.customer_id = cust.id
     JOIN organisation_branch cust_org_b ON cust_org_b.id = cust.customer_branch_id
     JOIN loan_products lp ON lapp.loan_application_product_id = lp.id
     JOIN organisation_branch loan_org_b ON loan_org_b.id = lapp.organisation_branch_id
	 JOIN organisation org ON org.id=loan_org_b.branch_organisation_id;

DROP VIEW public.loanview;
CREATE OR REPLACE VIEW public.loanview
 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,
    lps.id AS loan_product_id,
    lps.product_name,
    lps.int_rate AS product_int_rate,
    lps.int_method AS product_int_method,
	 sf.id AS loan_officer_id, sf.name AS loan_officer_full_name,
    cr.gender,
    cr.telephone
	
   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
     JOIN loan_products lps ON lps.id = lap.loan_application_product_id
     JOIN staff sf ON sf.id = lap.loan_officer_id;

ALTER TABLE user_session_management DROP CONSTRAINT user_session_management_user_id_key;

DROP VIEW public.loan_installment_repayment_view;
CREATE OR REPLACE VIEW public.loan_installment_repayment_view
 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,
    ob.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_amount,
    loan_disbursed.loan_start_date,
    loan_disbursed.loan_disbursement_date,
    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,
    schedule.id AS schedule_id,
    COALESCE(schedule.principal_expected, 0::double precision) AS schedule_principal_expected,
    COALESCE(schedule.interest_expected, 0::double precision) AS schedule_interest_expected,
    schedule.expected_date AS schedule_expected_date,
    schedule.status AS schedule_status,
    COALESCE(int_waivered.amount, 0::double precision) AS int_waivered_amount,
    int_waivered.comment AS int_waivered_comment,
    pen.id AS penalty_id,
    COALESCE(pen.amount, 0::double precision) AS penalty_amount,
    pen.comment AS penalty_comment,
    pen_waivered.id AS penalty_waivered_id,
    COALESCE(pen_waivered.amount, 0::double precision) AS penalty_waivered_amount,
    pen_waivered.comment AS pen_waivered_comment,
    COALESCE(trans.amount, 0::double precision) AS paid_amount,
    trans.voucher_no,
    trans.payment_method,
    lmt.transaction_type,
    loan_approval.period_type,
    loan_approval.loan_period,
    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,
        CASE
            WHEN loan_approval.period_type::text = 'w'::text THEN loan_disbursed.loan_start_date + make_interval(weeks => COALESCE(loan_approval.loan_period, 0))
            WHEN loan_approval.period_type::text = 'm'::text THEN loan_disbursed.loan_start_date + make_interval(months => COALESCE(loan_approval.loan_period, 0))
            WHEN loan_approval.period_type::text = 'q'::text THEN loan_disbursed.loan_start_date + make_interval(months => COALESCE(loan_approval.loan_period * 3, 0))
            WHEN loan_approval.period_type::text = 'y'::text THEN loan_disbursed.loan_start_date + make_interval(years => COALESCE(loan_approval.loan_period, 0))
            ELSE loan_disbursed.loan_start_date + make_interval(days => COALESCE(loan_approval.loan_period, 0))
        END AS loan_end_date
   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_repayments_schedule schedule ON schedule.loan_application_id = lap.id
     LEFT JOIN loan_penalty pen ON pen.loan_repayment_schedule_id = schedule.id
     LEFT JOIN loan_penalty_waivered pen_waivered ON pen_waivered.loan_repayment_schedule_id = schedule.id
     LEFT JOIN loan_interest_waivered int_waivered ON int_waivered.loan_repayment_schedule_id = schedule.id
     LEFT JOIN loan_payments payments ON payments.loan_repayment_schedule_id = schedule.id
     LEFT JOIN loan_main_transactions lmt ON lmt.id = payments.loan_main_transaction_id
     LEFT JOIN system_transactions trans ON trans.id = lmt.system_transaction_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;


DROP VIEW public.loan_repayment_view;
CREATE OR REPLACE VIEW public.loan_repayment_view
 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,
    ob.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_amount,
    loan_disbursed.loan_start_date,
    loan_disbursed.loan_disbursement_date,
    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,
    schedule.id AS schedule_id,
    COALESCE(schedule.principal_expected, 0::double precision) AS schedule_principal_expected,
    COALESCE(schedule.interest_expected, 0::double precision) AS schedule_interest_expected,
    schedule.expected_date AS schedule_expected_date,
    schedule.status AS schedule_status,
    COALESCE(( SELECT sum(loan_penalty.amount) AS sum
           FROM loan_penalty
          WHERE loan_penalty.loan_repayment_schedule_id = schedule.id), 0::double precision) AS penalty_amount,
    COALESCE(( SELECT sum(loan_penalty_waivered.amount) AS sum
           FROM loan_penalty_waivered
          WHERE loan_penalty_waivered.loan_repayment_schedule_id = schedule.id), 0::double precision) AS penalty_waivered_amount,
    COALESCE(( SELECT sum(loan_interest_waivered.amount) AS sum
           FROM loan_interest_waivered
          WHERE loan_interest_waivered.loan_repayment_schedule_id = schedule.id), 0::double precision) AS interest_waivered_amount,
    COALESCE(( SELECT sum(loan_payments.princ_paid) AS sum
           FROM loan_payments
          WHERE loan_payments.loan_repayment_schedule_id = schedule.id), 0::double precision) AS princ_paid,
    COALESCE(( SELECT sum(loan_payments.int_paid) AS sum
           FROM loan_payments
          WHERE loan_payments.loan_repayment_schedule_id = schedule.id), 0::double precision) AS int_paid,
    COALESCE(( SELECT sum(loan_payments.penalty_paid) AS sum
           FROM loan_payments
          WHERE loan_payments.loan_repayment_schedule_id = schedule.id), 0::double precision) AS penalty_paid,
    COALESCE(schedule.principal_expected, 0::double precision) - COALESCE(( SELECT sum(loan_payments.princ_paid) AS sum
           FROM loan_payments
          WHERE loan_payments.loan_repayment_schedule_id = schedule.id), 0::double precision) AS principal_balance,
    COALESCE(schedule.interest_expected, 0::double precision) - (COALESCE(( SELECT sum(loan_payments.int_paid) AS sum
           FROM loan_payments
          WHERE loan_payments.loan_repayment_schedule_id = schedule.id), 0::double precision) + COALESCE(( SELECT sum(loan_interest_waivered.amount) AS sum
           FROM loan_interest_waivered
          WHERE loan_interest_waivered.loan_repayment_schedule_id = schedule.id), 0::double precision)) AS interest_balance,
    COALESCE(( SELECT sum(loan_penalty.amount) AS sum
           FROM loan_penalty
          WHERE loan_penalty.loan_repayment_schedule_id = schedule.id), 0::double precision) - (COALESCE(( SELECT sum(loan_payments.penalty_paid) AS sum
           FROM loan_payments
          WHERE loan_payments.loan_repayment_schedule_id = schedule.id), 0::double precision) + COALESCE(( SELECT sum(loan_penalty_waivered.amount) AS sum
           FROM loan_penalty_waivered
          WHERE loan_penalty_waivered.loan_repayment_schedule_id = schedule.id), 0::double precision)) AS penalty_balance,
    loan_approval.period_type,
    loan_approval.loan_period,
    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,
        CASE
            WHEN loan_approval.period_type::text = 'w'::text THEN loan_disbursed.loan_start_date + make_interval(weeks => COALESCE(loan_approval.loan_period, 0))
            WHEN loan_approval.period_type::text = 'm'::text THEN loan_disbursed.loan_start_date + make_interval(months => COALESCE(loan_approval.loan_period, 0))
            WHEN loan_approval.period_type::text = 'q'::text THEN loan_disbursed.loan_start_date + make_interval(months => COALESCE(loan_approval.loan_period * 3, 0))
            WHEN loan_approval.period_type::text = 'y'::text THEN loan_disbursed.loan_start_date + make_interval(years => COALESCE(loan_approval.loan_period, 0))
            ELSE loan_disbursed.loan_start_date + make_interval(days => COALESCE(loan_approval.loan_period, 0))
        END AS loan_end_date,
        CASE
            WHEN loan_approval.period_type::text = 'w'::text THEN loan_disbursed.loan_start_date + make_interval(weeks => COALESCE(loan_approval.loan_period, 0))
            WHEN loan_approval.period_type::text = 'm'::text THEN loan_disbursed.loan_start_date + make_interval(months => COALESCE(loan_approval.loan_period, 0))
            WHEN loan_approval.period_type::text = 'q'::text THEN loan_disbursed.loan_start_date + make_interval(months => COALESCE(loan_approval.loan_period * 3, 0))
            WHEN loan_approval.period_type::text = 'y'::text THEN loan_disbursed.loan_start_date + make_interval(years => COALESCE(loan_approval.loan_period, 0))
            ELSE loan_disbursed.loan_start_date + make_interval(days => COALESCE(loan_approval.loan_period, 0))
        END +
        CASE
            WHEN loan_disbursed.write_off_period_type::text = 'w'::text THEN make_interval(weeks => COALESCE(loan_disbursed.write_off_grace_period, 0))
            WHEN loan_disbursed.write_off_period_type::text = 'bw'::text THEN make_interval(weeks => COALESCE(loan_disbursed.write_off_grace_period * 2, 0))
            WHEN loan_disbursed.write_off_period_type::text = 'm'::text THEN make_interval(months => COALESCE(loan_disbursed.write_off_grace_period, 0))
            WHEN loan_disbursed.write_off_period_type::text = 'q'::text THEN make_interval(months => COALESCE(loan_disbursed.write_off_grace_period * 3, 0))
            WHEN loan_disbursed.write_off_period_type::text = 'y'::text THEN make_interval(years => COALESCE(loan_disbursed.write_off_grace_period, 0))
            ELSE make_interval(days => COALESCE(loan_disbursed.write_off_grace_period, 0))
        END AS loan_write_off_date,
        CASE
            WHEN loan_disbursed.arrears_period_type::text = 'w'::text THEN schedule.expected_date + make_interval(weeks => COALESCE(loan_disbursed.arrear_grace_period, 0))
            WHEN loan_disbursed.arrears_period_type::text = 'bw'::text THEN schedule.expected_date + make_interval(weeks => COALESCE(loan_disbursed.arrear_grace_period * 2, 0))
            WHEN loan_disbursed.arrears_period_type::text = 'm'::text THEN schedule.expected_date + make_interval(months => COALESCE(loan_disbursed.arrear_grace_period, 0))
            WHEN loan_disbursed.arrears_period_type::text = 'q'::text THEN schedule.expected_date + make_interval(months => COALESCE(loan_disbursed.arrear_grace_period * 3, 0))
            WHEN loan_disbursed.arrears_period_type::text = 'y'::text THEN schedule.expected_date + make_interval(years => COALESCE(loan_disbursed.arrear_grace_period, 0))
            ELSE schedule.expected_date + make_interval(days => COALESCE(loan_disbursed.arrear_grace_period, 0))
        END AS loan_arrear_date
   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_repayments_schedule schedule ON schedule.loan_application_id = lap.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;

ALTER TABLE public.loan_installment_repayment_view
    OWNER TO postgres;

SELECT setval('system_component_id_seq', COALESCE((SELECT MAX(id)+1 FROM system_component), 1), false);
SELECT setval('village_id_seq', COALESCE((SELECT MAX(id)+1 FROM village), 1), false);
SELECT setval('parish_id_seq', COALESCE((SELECT MAX(id)+1 FROM parish), 1), false);
SELECT setval('subcounty_id_seq', COALESCE((SELECT MAX(id)+1 FROM subcounty), 1), false);
SELECT setval('county_id_seq', COALESCE((SELECT MAX(id)+1 FROM county), 1), false);
SELECT setval('district_id_seq', COALESCE((SELECT MAX(id)+1 FROM district), 1), false);



#back us
-- 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 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, 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$;

ALTER FUNCTION public.loan_repayment_func(timestamp without time zone, timestamp without time zone)
    OWNER TO postgres;


