* to the users office hierarchy. Here, a few key tables are done. But
* if additional fields are needed on other tables the same pattern
* applies
*/
final AppUser currentUser = this.context.authenticatedUser();
String scopedSQL = null;
/*
* m_loan and m_savings_account are connected to an m_office thru either
* an m_client or an m_group If both it means it relates to an m_client
* that is in a group (still an m_client account)
*/
if (appTable.equalsIgnoreCase("m_loan")) {
scopedSQL = "select distinctrow x.* from ("
+ " (select o.id as officeId, l.group_id as groupId, l.client_id as clientId, null as savingsId, l.id as loanId, null as entityId from m_loan l "
+ " join m_client c on c.id = l.client_id "
+ " join m_office o on o.id = c.office_id and o.hierarchy like '"
+ currentUser.getOffice().getHierarchy()
+ "%'"
+ " where l.id = "
+ appTableId
+ ")"
+ " union all "
+ " (select o.id as officeId, l.group_id as groupId, l.client_id as clientId, null as savingsId, l.id as loanId, null as entityId from m_loan l "
+ " join m_group g on g.id = l.group_id " + " join m_office o on o.id = g.office_id and o.hierarchy like '"
+ currentUser.getOffice().getHierarchy() + "%'" + " where l.id = " + appTableId + ")" + " ) x";
}
if (appTable.equalsIgnoreCase("m_savings_account")) {
scopedSQL = "select distinctrow x.* from ("
+ " (select o.id as officeId, s.group_id as groupId, s.client_id as clientId, s.id as savingsId, null as loanId, null as entityId from m_savings_account s "
+ " join m_client c on c.id = s.client_id "
+ " join m_office o on o.id = c.office_id and o.hierarchy like '"
+ currentUser.getOffice().getHierarchy()
+ "%'"
+ " where s.id = "
+ appTableId
+ ")"
+ " union all "
+ " (select o.id as officeId, s.group_id as groupId, s.client_id as clientId, s.id as savingsId, null as loanId, null as entityId from m_savings_account s "
+ " join m_group g on g.id = s.group_id " + " join m_office o on o.id = g.office_id and o.hierarchy like '"
+ currentUser.getOffice().getHierarchy() + "%'" + " where s.id = " + appTableId + ")" + " ) x";
}
if (appTable.equalsIgnoreCase("m_client")) {
scopedSQL = "select o.id as officeId, null as groupId, c.id as clientId, null as savingsId, null as loanId, null as entityId from m_client c "
+ " join m_office o on o.id = c.office_id and o.hierarchy like '"
+ currentUser.getOffice().getHierarchy()
+ "%'"
+ " where c.id = " + appTableId;
}
if (appTable.equalsIgnoreCase("m_group") || appTable.equalsIgnoreCase("m_center")) {
scopedSQL = "select o.id as officeId, g.id as groupId, null as clientId, null as savingsId, null as loanId, null as entityId from m_group g "
+ " join m_office o on o.id = g.office_id and o.hierarchy like '"
+ currentUser.getOffice().getHierarchy()
+ "%'"
+ " where g.id = " + appTableId;
}
if (appTable.equalsIgnoreCase("m_office")) {
scopedSQL = "select o.id as officeId, null as groupId, null as clientId, null as savingsId, null as loanId, null as entityId from m_office o "
+ " where o.hierarchy like '" + currentUser.getOffice().getHierarchy() + "%'" + " and o.id = " + appTableId;
}
if (appTable.equalsIgnoreCase("m_product_loan") || appTable.equalsIgnoreCase("m_savings_product")) {
scopedSQL = "select null as officeId, null as groupId, null as clientId, null as savingsId, null as loanId, p.id as entityId from "
+ appTable + " as p WHERE p.id = " + appTableId;