Dashboard querys

-- Daily Collection Target
SELECT sum(la.installment_amount),
       count(distinct (la.member_id))
FROM loan_account la
         INNER JOIN member_info mi
                    ON (mi.id = la.member_id
                        AND mi.branch_info_id = :branchId
                        AND mi.project_info_id = :projectId
                        AND mi.domain_status_id = :domainStatusActive
                        AND mi.member_status_id = :memberStatusActive)
WHERE la.domain_status_id = :domainStatusActive
  AND la.office_info_id = :branchId
  AND la.project_info_id = :projectId
  AND la.next_collection_date = :localDate
  AND (la.loan_status_id = :currentStatus
    OR la.loan_status_id = :late1
    OR la.loan_status_id = :late2)
  AND mi.assignedpo_id = :poId;

-- Daily collection achieved
SELECT sum(lat.collection_amount),
       count(distinct (mi.id))
FROM loan_account_transaction lat
         INNER JOIN member_info mi
                    ON (mi.id = lat.member_info_id
                        and mi.branch_info_id = :branchId
                        and mi.project_info_id = :projectId
                        AND mi.assignedpo_id = :poId
                        and mi.domain_status_id = :domainStatusActive
                        and mi.member_status_id = :memberStatusActive)
WHERE lat.domain_status_id = :domainStatusActive
  AND lat.office_info_id = :branchId
  AND lat.project_info_id = :projectId
  AND lat.domain_status_id = :domainStatusActive
  AND lat.collection_amount > 0
  AND (lat.loan_status_id = :currentStatus
    OR lat.loan_status_id = :late1
    OR lat.loan_status_id = :late2)
  AND lat.transaction_date = :localDate;

-- Monthly/Trimester achieved
select la.id,
       la.office_info_id branch_id,
       la.group_info_id  vo_id,
       la.loan_product_details_id,
       la.disbursement_date,
       la.no_of_installment,
       la.installment_amount,
       la.interest_outstanding,
       la.late_interest_os,
       la.interest_amount,
       la.realizable_interest_amount,
       la.principal_outstanding,
       la.overdue_amount,
       la.outstanding_balance,
       la.interest_factor,
       la.interest_provision_fraction,
       la.last_provision_date,
       la.next_collection_date,
       la.loan_duration_end_date,
       la.loan_status_id,
       la.country_id,
       la.frequency_id
from loan_account la
         inner join member_info mi
                    on (mi.branch_info_id = :branchId
                        and mi.project_info_id = :projectId
                        and mi.domain_status_id = :domainStatusActive
                        and mi.member_status_id = :memberStatusActive
                        and (cast(:poId as character varying) is null
                            or mi.assignedpo_id = :poId)
                        and la.member_id = mi.id)
where la.office_info_id = :branchId
  and la.project_info_id = :projectId
  and la.domain_status_id = :domainStatusActive
  and (cast(:loanAccountId as character varying) is null
    or la.id = :loanAccountId);

-- Monthly/Trimester collection collected.
SELECT sum(lat.collection_amount)
FROM loan_account_transaction lat
         INNER JOIN member_info mi
                    ON (mi.id = lat.member_info_id
                        and mi.branch_info_id = :branchId
                        and mi.project_info_id = :projectId
                        and mi.domain_status_id = :domainStatusActive
                        and mi.member_status_id = :memberStatusActive)
WHERE lat.domain_status_id = :domainStatusActive
  AND lat.office_info_id = :branchId
  AND lat.project_info_id = :projectId
  AND (lat.loan_status_id = :currentStatus
    OR lat.loan_status_id = :late1
    OR lat.loan_status_id = :late2)
  AND lat.transaction_date >= :startDate
  AND lat.transaction_date <= :endDate
  AND mi.assignedpo_id = :poId;

-- Daily overdue target.
SELECT sum(la.overdue_amount), count(distinct (mi.id))
FROM loan_account la
         INNER JOIN member_info mi
                    ON (mi.id = la.member_id
                        AND mi.branch_info_id = :branchId
                        AND mi.project_info_id = :projectId
                        AND mi.assignedpo_id = :poId
                        AND mi.domain_status_id = :domainStatusActive
                        AND mi.member_status_id = :memberStatusActive)
WHERE la.office_info_id = :branchId
  AND la.project_info_id = :projectId
  AND la.domain_status_id = :domainStatusActive;

-- Overdue Collect.
SELECT sum(lc.last_overdue_amount - lc.overdue_amount),
       count(distinct mi.id)
FROM loan_collection lc
         INNER JOIN loan_account la
                    ON (lc.loan_account_id = la.id
                        AND la.office_info_id = :branchId
                        AND la.project_info_id = :projectId
                        AND la.domain_status_id = :domainStatusActive)
         INNER JOIN member_info mi
                    ON (mi.id = la.member_id
                        and mi.branch_info_id = :branchId
                        AND mi.assignedpo_id = :poId
                        AND mi.project_info_id = :projectId
                        and mi.domain_status_id = :domainStatusActive
                        and mi.member_status_id = :memberStatusActive)
WHERE lc.domain_status_id = :domainStatusActive
  AND lc.office_info_id = :branchId
  AND lc.overdue_amount < lc.last_overdue_amount
  AND lc.collection_date between :startDate and :endDate;

-- Disbursement Achieved
SELECT sum(la.disbursed_amount)
FROM loan_account la
         INNER JOIN member_info mi
                    ON (la.member_id = mi.id
                        AND mi.branch_info_id = :branchId
                        AND mi.project_info_id = :projectId
                        AND mi.domain_status_id = :domainStatusActive
                        AND mi.member_status_id = :memberStatusActive
                        AND mi.assignedpo_id = :poId)
WHERE la.office_info_id = :branchId
  AND la.project_info_id = :projectId
  AND la.domain_status_id = :domainStatusActive
  AND la.disbursement_date >= :startDate
  AND la.disbursement_date <= :endDate;

-- Disbursement Target
SELECT pt.generalSavings,
       pt.memberTarget,
       pt.loanProposalTarget,
       pt.loanDisbursementTarget,
       pt.loanDisbursementAmountTarget
FROM PoTarget pt
         INNER JOIN ProgrammeOrganizer po
                    on (pt.programmeOrganizerId = po.id AND po.branchId = :branchId)
WHERE pt.programmeOrganizerId = :poId
  and pt.yearOfTarget = :targetYear
  AND pt.targetQuarterId = :quarterId
  and pt.deleted = :isDeleted;


-- Other Loan Info --

-- Current,Late,NIBL1 debt/OS
SELECT la.loan_status_id, sum(la.outstanding_balance), count(*)
FROM loan_account la
WHERE la.office_info_id = :branchId
  AND la.domain_status_id = 1
  AND not la.loan_status_id = ANY (:loanStatus)
  AND la.project_info_id = :projectId
GROUP BY la.loan_status_id;

-- Number of new loan
SELECT count(la.id)
FROM loan_account la
WHERE la.office_info_id = :branchId
  AND la.domain_status_id = :domainStatusId
  AND la.project_info_id = :projectId
  AND la.disbursement_date >= :startDate
  AND la.disbursement_date <= :endDate;


-- Savings Information --

--Savings
SELECT COUNT(sat.id),
       SUM(sat.installment_amount)
FROM savings_account_transaction sat
WHERE sat.office_info_id = :branchId
  AND sat.domain_status_id = :domainStatusId
  AND sat.project_info_id = :projectId;

-- Special Savings
SELECT COUNT(sa.id),
       SUM(sa.savings_balance)
FROM savings_account sa
WHERE sa.office_info_id = :branchId
  AND sa.domain_status_id = :domainStatusId
  AND sa.account_type_id != :typeId
  AND sa.project_info_id = :projectId;

-- Branch Address
SELECT oa.address_line1,
       oa.address_line2,
       oa.zip_code,
       city.name,
       district.district_name,
       division.division_name
FROM physical_office_info poi
         LEFT JOIN office_address oa
                   ON (oa.branch_ref_code = poi.office_ref_code)
         LEFT JOIN city ON (city.id = oa.city_id)
         LEFT JOIN district ON (district.id = oa.district_info_id)
         LEFT JOIN division ON (division.id = oa.division_info_id)
WHERE poi.id = :branchRefId;