return sql.toString();
<<<<<<< HEAD
public String collectionSheetSchema() {
return "SELECT gp.display_name As groupName, "
+ "gp.id As groupId, "
+ "cl.display_name As clientName, "
+ "sf.id As staffId, "
+ "sf.display_name As staffName, "
+ "gl.id As levelId, "
+ "gl.level_name As levelName, "
+ "cl.id As clientId, "
+ "ln.id As loanId, "
+ "ln.account_no As accountId, "
+ "ln.loan_status_id As accountStatusId, "
+ "pl.short_name As productShortName, "
+ "ln.product_id As productId, "
+ "ln.currency_code as currencyCode, ln.currency_digits as currencyDigits, ln.currency_multiplesof as inMultiplesOf, rc.`name` as currencyName, rc.display_symbol as currencyDisplaySymbol, rc.internationalized_name_code as currencyNameCode, "
+ "if(ln.loan_status_id = 200 , ln.principal_amount , null) As disbursementAmount, "
+ "sum(ifnull(if(ln.loan_status_id = 300, ls.principal_amount, 0.0), 0.0) - ifnull(if(ln.loan_status_id = 300, ls.principal_completed_derived, 0.0), 0.0)) As principalDue, "
+ "ln.principal_repaid_derived As principalPaid, "
+ "sum(ifnull(if(ln.loan_status_id = 300, ls.interest_amount, 0.0), 0.0) - ifnull(if(ln.loan_status_id = 300, ls.interest_completed_derived, 0.0), 0.0)) As interestDue, "
+ "ln.interest_repaid_derived As interestPaid, "
+ "sum(if(ln.loan_status_id = 300, lc.amount_outstanding_derived, 0.0)) as chargesDue, "
+ "ca.attendance_type_enum as attendanceTypeId "
+ "FROM m_group gp "
+ "LEFT JOIN m_office of ON of.id = gp.office_id AND of.hierarchy like :officeHierarchy "
// + "LEFT JOIN m_office of ON of.id = gp.office_id "
+ "JOIN m_group_level gl ON gl.id = gp.level_Id "
+ "LEFT JOIN m_staff sf ON sf.id = gp.staff_id "
+ "JOIN m_group_client gc ON gc.group_id = gp.id "
+ "JOIN m_client cl ON cl.id = gc.client_id "
+ "LEFT JOIN m_loan ln ON cl.id = ln.client_id AND ln.group_id is not null AND ( ln.loan_status_id = 300 OR ( ln.loan_status_id =200 AND ln.expected_disbursedon_date <= :dueDate )) "
+ "LEFT JOIN m_product_loan pl ON pl.id = ln.product_id "
+ "LEFT JOIN m_currency rc on rc.`code` = ln.currency_code "
+ "LEFT JOIN m_loan_repayment_schedule ls ON ls.loan_id = ln.id AND ls.completed_derived = 0 AND ls.duedate <= :dueDate "
+ "LEFT JOIN m_loan_charge lc ON lc.loan_id = ln.id AND lc.is_paid_derived = 0 AND ( lc.due_for_collection_as_of_date <= :dueDate OR lc.charge_time_enum = 1) "
+ "left join m_calendar_instance ci on gp.parent_id = ci.entity_id and ci.entity_type_enum =:entityTypeId "
+ "left join m_meeting mt on ci.id = mt.calendar_instance_id and mt.meeting_date =:dueDate "
+ "left join m_client_attendance ca on ca.meeting_id=mt.id and ca.client_id=cl.id ";
// + "WHERE gp.hierarchy like :hierarchy " +
// "GROUP BY gp.id ,cl.id , ln.id " +
// "ORDER BY gp.id , cl.id , ln.id ";
=======
>>>>>>> 027b818f27f46dc04b0a1d1f5e8c65c58569dba2
}
@Override |