/**
* This Source Code Form is subject to the terms of the Mozilla Public
* License, v. 2.0. If a copy of the MPL was not distributed with this file,
* You can obtain one at http://mozilla.org/MPL/2.0/.
*/
package org.mifosplatform.portfolio.client.service;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.joda.time.LocalDate;
import org.mifosplatform.infrastructure.codes.data.CodeValueData;
import org.mifosplatform.infrastructure.codes.service.CodeValueReadPlatformService;
import org.mifosplatform.infrastructure.core.api.ApiParameterHelper;
import org.mifosplatform.infrastructure.core.data.EnumOptionData;
import org.mifosplatform.infrastructure.core.domain.JdbcSupport;
import org.mifosplatform.infrastructure.core.service.Page;
import org.mifosplatform.infrastructure.core.service.PaginationHelper;
import org.mifosplatform.infrastructure.core.service.RoutingDataSource;
import org.mifosplatform.infrastructure.core.service.SearchParameters;
import org.mifosplatform.infrastructure.security.service.PlatformSecurityContext;
import org.mifosplatform.organisation.office.data.OfficeData;
import org.mifosplatform.organisation.office.service.OfficeReadPlatformService;
import org.mifosplatform.organisation.staff.data.StaffData;
import org.mifosplatform.organisation.staff.service.StaffReadPlatformService;
import org.mifosplatform.portfolio.client.api.ClientApiConstants;
import org.mifosplatform.portfolio.client.data.ClientData;
import org.mifosplatform.portfolio.client.data.ClientNonPersonData;
import org.mifosplatform.portfolio.client.data.ClientTimelineData;
import org.mifosplatform.portfolio.client.domain.ClientEnumerations;
import org.mifosplatform.portfolio.client.domain.ClientStatus;
import org.mifosplatform.portfolio.client.domain.LegalForm;
import org.mifosplatform.portfolio.client.exception.ClientNotFoundException;
import org.mifosplatform.portfolio.group.data.GroupGeneralData;
import org.mifosplatform.portfolio.savings.data.SavingsProductData;
import org.mifosplatform.portfolio.savings.service.SavingsProductReadPlatformService;
import org.mifosplatform.useradministration.domain.AppUser;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
@Service
public class ClientReadPlatformServiceImpl implements ClientReadPlatformService {
private final JdbcTemplate jdbcTemplate;
private final PlatformSecurityContext context;
private final OfficeReadPlatformService officeReadPlatformService;
private final StaffReadPlatformService staffReadPlatformService;
private final CodeValueReadPlatformService codeValueReadPlatformService;
private final SavingsProductReadPlatformService savingsProductReadPlatformService;
// data mappers
private final PaginationHelper<ClientData> paginationHelper = new PaginationHelper<>();
private final ClientMapper clientMapper = new ClientMapper();
private final ClientLookupMapper lookupMapper = new ClientLookupMapper();
private final ClientMembersOfGroupMapper membersOfGroupMapper = new ClientMembersOfGroupMapper();
private final ParentGroupsMapper clientGroupsMapper = new ParentGroupsMapper();
@Autowired
public ClientReadPlatformServiceImpl(final PlatformSecurityContext context, final RoutingDataSource dataSource,
final OfficeReadPlatformService officeReadPlatformService, final StaffReadPlatformService staffReadPlatformService,
final CodeValueReadPlatformService codeValueReadPlatformService,
final SavingsProductReadPlatformService savingsProductReadPlatformService) {
this.context = context;
this.officeReadPlatformService = officeReadPlatformService;
this.jdbcTemplate = new JdbcTemplate(dataSource);
this.staffReadPlatformService = staffReadPlatformService;
this.codeValueReadPlatformService = codeValueReadPlatformService;
this.savingsProductReadPlatformService = savingsProductReadPlatformService;
}
@Override
public ClientData retrieveTemplate(final Long officeId, final boolean staffInSelectedOfficeOnly) {
this.context.authenticatedUser();
final Long defaultOfficeId = defaultToUsersOfficeIfNull(officeId);
final Collection<OfficeData> offices = this.officeReadPlatformService.retrieveAllOfficesForDropdown();
final Collection<SavingsProductData> savingsProductDatas = this.savingsProductReadPlatformService.retrieveAllForLookupByType(null);
Collection<StaffData> staffOptions = null;
final boolean loanOfficersOnly = false;
if (staffInSelectedOfficeOnly) {
staffOptions = this.staffReadPlatformService.retrieveAllStaffForDropdown(defaultOfficeId);
} else {
staffOptions = this.staffReadPlatformService.retrieveAllStaffInOfficeAndItsParentOfficeHierarchy(defaultOfficeId,
loanOfficersOnly);
}
if (CollectionUtils.isEmpty(staffOptions)) {
staffOptions = null;
}
final List<CodeValueData> genderOptions = new ArrayList<>(
this.codeValueReadPlatformService.retrieveCodeValuesByCode(ClientApiConstants.GENDER));
final List<CodeValueData> clientTypeOptions = new ArrayList<>(
this.codeValueReadPlatformService.retrieveCodeValuesByCode(ClientApiConstants.CLIENT_TYPE));
final List<CodeValueData> clientClassificationOptions = new ArrayList<>(
this.codeValueReadPlatformService.retrieveCodeValuesByCode(ClientApiConstants.CLIENT_CLASSIFICATION));
final List<CodeValueData> clientNonPersonConstitutionOptions = new ArrayList<>(
this.codeValueReadPlatformService.retrieveCodeValuesByCode(ClientApiConstants.CLIENT_NON_PERSON_CONSTITUTION));
final List<CodeValueData> clientNonPersonMainBusinessLineOptions = new ArrayList<>(
this.codeValueReadPlatformService.retrieveCodeValuesByCode(ClientApiConstants.CLIENT_NON_PERSON_MAIN_BUSINESS_LINE));
final List<EnumOptionData> clientLegalFormOptions = ClientEnumerations.legalForm(LegalForm.values());
return ClientData.template(defaultOfficeId, new LocalDate(), offices, staffOptions, null, genderOptions, savingsProductDatas,
clientTypeOptions, clientClassificationOptions, clientNonPersonConstitutionOptions, clientNonPersonMainBusinessLineOptions,
clientLegalFormOptions);
}
@Override
public Page<ClientData> retrieveAll(final SearchParameters searchParameters) {
final String userOfficeHierarchy = this.context.officeHierarchy();
final String underHierarchySearchString = userOfficeHierarchy + "%";
final String appUserID = String.valueOf(context.authenticatedUser().getId());
// if (searchParameters.isScopedByOfficeHierarchy()) {
// this.context.validateAccessRights(searchParameters.getHierarchy());
// underHierarchySearchString = searchParameters.getHierarchy() + "%";
// }
final StringBuilder sqlBuilder = new StringBuilder(200);
sqlBuilder.append("select SQL_CALC_FOUND_ROWS ");
sqlBuilder.append(this.clientMapper.schema());
sqlBuilder.append(" where (o.hierarchy like ? or transferToOffice.hierarchy like ?) ");
if(searchParameters.isSelfUser()){
sqlBuilder.append(" and c.id in (select umap.client_id from m_selfservice_user_client_mapping as umap where umap.appuser_id = ? ) ");
}
final String extraCriteria = buildSqlStringFromClientCriteria(searchParameters);
if (StringUtils.isNotBlank(extraCriteria)) {
sqlBuilder.append(" and (").append(extraCriteria).append(")");
}
if (searchParameters.isOrderByRequested()) {
sqlBuilder.append(" order by ").append(searchParameters.getOrderBy());
if (searchParameters.isSortOrderProvided()) {
sqlBuilder.append(' ').append(searchParameters.getSortOrder());
}
}
if (searchParameters.isLimited()) {
sqlBuilder.append(" limit ").append(searchParameters.getLimit());
if (searchParameters.isOffset()) {
sqlBuilder.append(" offset ").append(searchParameters.getOffset());
}
}
final String sqlCountRows = "SELECT FOUND_ROWS()";
Object[] params = new Object[] {underHierarchySearchString, underHierarchySearchString };
if(searchParameters.isSelfUser()){
params = new Object[] {underHierarchySearchString, underHierarchySearchString, appUserID };
}
return this.paginationHelper.fetchPage(this.jdbcTemplate, sqlCountRows, sqlBuilder.toString(), params, this.clientMapper);
}
private String buildSqlStringFromClientCriteria(final SearchParameters searchParameters) {
String sqlSearch = searchParameters.getSqlSearch();
final Long officeId = searchParameters.getOfficeId();
final String externalId = searchParameters.getExternalId();
final String displayName = searchParameters.getName();
final String firstname = searchParameters.getFirstname();
final String lastname = searchParameters.getLastname();
String extraCriteria = "";
if (sqlSearch != null) {
sqlSearch = sqlSearch.replaceAll(" display_name ", " c.display_name ");
sqlSearch = sqlSearch.replaceAll("display_name ", "c.display_name ");
extraCriteria = " and (" + sqlSearch + ")";
}
if (officeId != null) {
extraCriteria += " and c.office_id = " + officeId;
}
if (externalId != null) {
extraCriteria += " and c.external_id like " + ApiParameterHelper.sqlEncodeString(externalId);
}
if (displayName != null) {
//extraCriteria += " and concat(ifnull(c.firstname, ''), if(c.firstname > '',' ', '') , ifnull(c.lastname, '')) like "
extraCriteria += " and c.display_name like "
+ ApiParameterHelper.sqlEncodeString("%" + displayName + "%");
}
if (firstname != null) {
extraCriteria += " and c.firstname like " + ApiParameterHelper.sqlEncodeString(firstname);
}
if (lastname != null) {
extraCriteria += " and c.lastname like " + ApiParameterHelper.sqlEncodeString(lastname);
}
if (searchParameters.isScopedByOfficeHierarchy()) {
extraCriteria += " and o.hierarchy like " + ApiParameterHelper.sqlEncodeString(searchParameters.getHierarchy() + "%");
}
if(searchParameters.isOrphansOnly()){
extraCriteria += " and c.id NOT IN (select client_id from m_group_client) ";
}
if (StringUtils.isNotBlank(extraCriteria)) {
extraCriteria = extraCriteria.substring(4);
}
return extraCriteria;
}
@Override
public ClientData retrieveOne(final Long clientId) {
try {
final String hierarchy = this.context.officeHierarchy();
final String hierarchySearchString = hierarchy + "%";
final String sql = "select " + this.clientMapper.schema()
+ " where ( o.hierarchy like ? or transferToOffice.hierarchy like ?) and c.id = ?";
final ClientData clientData = this.jdbcTemplate.queryForObject(sql, this.clientMapper, new Object[] { hierarchySearchString,
hierarchySearchString, clientId });
final String clientGroupsSql = "select " + this.clientGroupsMapper.parentGroupsSchema();
final Collection<GroupGeneralData> parentGroups = this.jdbcTemplate.query(clientGroupsSql, this.clientGroupsMapper,
new Object[] { clientId });
return ClientData.setParentGroups(clientData, parentGroups);
} catch (final EmptyResultDataAccessException e) {
throw new ClientNotFoundException(clientId);
}
}
@Override
public Collection<ClientData> retrieveAllForLookup(final String extraCriteria) {
String sql = "select " + this.lookupMapper.schema();
if (StringUtils.isNotBlank(extraCriteria)) {
sql += " and (" + extraCriteria + ")";
}
return this.jdbcTemplate.query(sql, this.lookupMapper, new Object[] {});
}
@Override
public Collection<ClientData> retrieveAllForLookupByOfficeId(final Long officeId) {
final String sql = "select " + this.lookupMapper.schema() + " where c.office_id = ? and c.status_enum != ?";
return this.jdbcTemplate.query(sql, this.lookupMapper, new Object[] { officeId, ClientStatus.CLOSED.getValue() });
}
@Override
public Collection<ClientData> retrieveClientMembersOfGroup(final Long groupId) {
final AppUser currentUser = this.context.authenticatedUser();
final String hierarchy = currentUser.getOffice().getHierarchy();
final String hierarchySearchString = hierarchy + "%";
final String sql = "select " + this.membersOfGroupMapper.schema() + " where o.hierarchy like ? and pgc.group_id = ?";
return this.jdbcTemplate.query(sql, this.membersOfGroupMapper, new Object[] { hierarchySearchString, groupId });
}
@Override
public Collection<ClientData> retrieveActiveClientMembersOfGroup(final Long groupId) {
final AppUser currentUser = this.context.authenticatedUser();
final String hierarchy = currentUser.getOffice().getHierarchy();
final String hierarchySearchString = hierarchy + "%";
final String sql = "select " + this.membersOfGroupMapper.schema()
+ " where o.hierarchy like ? and pgc.group_id = ? and c.status_enum = ? ";
return this.jdbcTemplate.query(sql, this.membersOfGroupMapper,
new Object[] { hierarchySearchString, groupId, ClientStatus.ACTIVE.getValue() });
}
private static final class ClientMembersOfGroupMapper implements RowMapper<ClientData> {
private final String schema;
public ClientMembersOfGroupMapper() {
final StringBuilder sqlBuilder = new StringBuilder(200);
sqlBuilder
.append("c.id as id, c.account_no as accountNo, c.external_id as externalId, c.status_enum as statusEnum,c.sub_status as subStatus, ");
sqlBuilder
.append("cvSubStatus.code_value as subStatusValue,cvSubStatus.code_description as subStatusDesc,c.office_id as officeId, o.name as officeName, ");
sqlBuilder.append("c.transfer_to_office_id as transferToOfficeId, transferToOffice.name as transferToOfficeName, ");
sqlBuilder.append("c.firstname as firstname, c.middlename as middlename, c.lastname as lastname, ");
sqlBuilder.append("c.fullname as fullname, c.display_name as displayName, ");
sqlBuilder.append("c.mobile_no as mobileNo, ");
sqlBuilder.append("c.date_of_birth as dateOfBirth, ");
sqlBuilder.append("c.gender_cv_id as genderId, ");
sqlBuilder.append("cv.code_value as genderValue, ");
sqlBuilder.append("c.client_type_cv_id as clienttypeId, ");
sqlBuilder.append("cvclienttype.code_value as clienttypeValue, ");
sqlBuilder.append("c.client_classification_cv_id as classificationId, ");
sqlBuilder.append("cvclassification.code_value as classificationValue, ");
sqlBuilder.append("c.legal_form_enum as legalFormEnum, ");
sqlBuilder.append("c.activation_date as activationDate, c.image_id as imageId, ");
sqlBuilder.append("c.staff_id as staffId, s.display_name as staffName,");
sqlBuilder.append("c.default_savings_product as savingsProductId, sp.name as savingsProductName, ");
sqlBuilder.append("c.default_savings_account as savingsAccountId, ");
sqlBuilder.append("c.submittedon_date as submittedOnDate, ");
sqlBuilder.append("sbu.username as submittedByUsername, ");
sqlBuilder.append("sbu.firstname as submittedByFirstname, ");
sqlBuilder.append("sbu.lastname as submittedByLastname, ");
sqlBuilder.append("c.closedon_date as closedOnDate, ");
sqlBuilder.append("clu.username as closedByUsername, ");
sqlBuilder.append("clu.firstname as closedByFirstname, ");
sqlBuilder.append("clu.lastname as closedByLastname, ");
sqlBuilder.append("acu.username as activatedByUsername, ");
sqlBuilder.append("acu.firstname as activatedByFirstname, ");
sqlBuilder.append("acu.lastname as activatedByLastname, ");
sqlBuilder.append("cnp.constitution_cv_id as constitutionId, ");
sqlBuilder.append("cvConstitution.code_value as constitutionValue, ");
sqlBuilder.append("cnp.incorp_no as incorpNo, ");
sqlBuilder.append("cnp.incorp_validity_till as incorpValidityTill, ");
sqlBuilder.append("cnp.main_business_line_cv_id as mainBusinessLineId, ");
sqlBuilder.append("cvMainBusinessLine.code_value as mainBusinessLineValue, ");
sqlBuilder.append("cnp.remarks as remarks ");
sqlBuilder.append("from m_client c ");
sqlBuilder.append("join m_office o on o.id = c.office_id ");
sqlBuilder.append("left join m_client_non_person cnp on cnp.client_id = c.id ");
sqlBuilder.append("join m_group_client pgc on pgc.client_id = c.id ");
sqlBuilder.append("left join m_staff s on s.id = c.staff_id ");
sqlBuilder.append("left join m_savings_product sp on sp.id = c.default_savings_product ");
sqlBuilder.append("left join m_office transferToOffice on transferToOffice.id = c.transfer_to_office_id ");
sqlBuilder.append("left join m_appuser sbu on sbu.id = c.submittedon_userid ");
sqlBuilder.append("left join m_appuser acu on acu.id = c.activatedon_userid ");
sqlBuilder.append("left join m_appuser clu on clu.id = c.closedon_userid ");
sqlBuilder.append("left join m_code_value cv on cv.id = c.gender_cv_id ");
sqlBuilder.append("left join m_code_value cvclienttype on cvclienttype.id = c.client_type_cv_id ");
sqlBuilder.append("left join m_code_value cvclassification on cvclassification.id = c.client_classification_cv_id ");
sqlBuilder.append("left join m_code_value cvSubStatus on cvSubStatus.id = c.sub_status ");
sqlBuilder.append("left join m_code_value cvConstitution on cvConstitution.id = cnp.constitution_cv_id ");
sqlBuilder.append("left join m_code_value cvMainBusinessLine on cvMainBusinessLine.id = cnp.main_business_line_cv_id ");
this.schema = sqlBuilder.toString();
}
public String schema() {
return this.schema;
}
@Override
public ClientData mapRow(final ResultSet rs, @SuppressWarnings("unused") final int rowNum) throws SQLException {
final String accountNo = rs.getString("accountNo");
final Integer statusEnum = JdbcSupport.getInteger(rs, "statusEnum");
final EnumOptionData status = ClientEnumerations.status(statusEnum);
final Long subStatusId = JdbcSupport.getLong(rs, "subStatus");
final String subStatusValue = rs.getString("subStatusValue");
final String subStatusDesc = rs.getString("subStatusDesc");
final boolean isActive = false;
final CodeValueData subStatus = CodeValueData.instance(subStatusId, subStatusValue, subStatusDesc, isActive);
final Long officeId = JdbcSupport.getLong(rs, "officeId");
final String officeName = rs.getString("officeName");
final Long transferToOfficeId = JdbcSupport.getLong(rs, "transferToOfficeId");
final String transferToOfficeName = rs.getString("transferToOfficeName");
final Long id = JdbcSupport.getLong(rs, "id");
final String firstname = rs.getString("firstname");
final String middlename = rs.getString("middlename");
final String lastname = rs.getString("lastname");
final String fullname = rs.getString("fullname");
final String displayName = rs.getString("displayName");
final String externalId = rs.getString("externalId");
final String mobileNo = rs.getString("mobileNo");
final LocalDate dateOfBirth = JdbcSupport.getLocalDate(rs, "dateOfBirth");
final Long genderId = JdbcSupport.getLong(rs, "genderId");
final String genderValue = rs.getString("genderValue");
final CodeValueData gender = CodeValueData.instance(genderId, genderValue);
final Long clienttypeId = JdbcSupport.getLong(rs, "clienttypeId");
final String clienttypeValue = rs.getString("clienttypeValue");
final CodeValueData clienttype = CodeValueData.instance(clienttypeId, clienttypeValue);
final Long classificationId = JdbcSupport.getLong(rs, "classificationId");
final String classificationValue = rs.getString("classificationValue");
final CodeValueData classification = CodeValueData.instance(classificationId, classificationValue);
final LocalDate activationDate = JdbcSupport.getLocalDate(rs, "activationDate");
final Long imageId = JdbcSupport.getLong(rs, "imageId");
final Long staffId = JdbcSupport.getLong(rs, "staffId");
final String staffName = rs.getString("staffName");
final Long savingsProductId = JdbcSupport.getLong(rs, "savingsProductId");
final String savingsProductName = rs.getString("savingsProductName");
final Long savingsAccountId = JdbcSupport.getLong(rs, "savingsAccountId");
final LocalDate closedOnDate = JdbcSupport.getLocalDate(rs, "closedOnDate");
final String closedByUsername = rs.getString("closedByUsername");
final String closedByFirstname = rs.getString("closedByFirstname");
final String closedByLastname = rs.getString("closedByLastname");
final LocalDate submittedOnDate = JdbcSupport.getLocalDate(rs, "submittedOnDate");
final String submittedByUsername = rs.getString("submittedByUsername");
final String submittedByFirstname = rs.getString("submittedByFirstname");
final String submittedByLastname = rs.getString("submittedByLastname");
final String activatedByUsername = rs.getString("activatedByUsername");
final String activatedByFirstname = rs.getString("activatedByFirstname");
final String activatedByLastname = rs.getString("activatedByLastname");
final Integer legalFormEnum = JdbcSupport.getInteger(rs, "legalFormEnum");
EnumOptionData legalForm = null;
if(legalFormEnum != null)
legalForm = ClientEnumerations.legalForm(legalFormEnum);
final Long constitutionId = JdbcSupport.getLong(rs, "constitutionId");
final String constitutionValue = rs.getString("constitutionValue");
final CodeValueData constitution = CodeValueData.instance(constitutionId, constitutionValue);
final String incorpNo = rs.getString("incorpNo");
final LocalDate incorpValidityTill = JdbcSupport.getLocalDate(rs, "incorpValidityTill");
final Long mainBusinessLineId = JdbcSupport.getLong(rs, "mainBusinessLineId");
final String mainBusinessLineValue = rs.getString("mainBusinessLineValue");
final CodeValueData mainBusinessLine = CodeValueData.instance(mainBusinessLineId, mainBusinessLineValue);
final String remarks = rs.getString("remarks");
final ClientNonPersonData clientNonPerson = new ClientNonPersonData(constitution, incorpNo, incorpValidityTill, mainBusinessLine, remarks);
final ClientTimelineData timeline = new ClientTimelineData(submittedOnDate, submittedByUsername, submittedByFirstname,
submittedByLastname, activationDate, activatedByUsername, activatedByFirstname, activatedByLastname, closedOnDate,
closedByUsername, closedByFirstname, closedByLastname);
return ClientData.instance(accountNo, status, subStatus, officeId, officeName, transferToOfficeId, transferToOfficeName, id,
firstname, middlename, lastname, fullname, displayName, externalId, mobileNo, dateOfBirth, gender, activationDate,
imageId, staffId, staffName, timeline, savingsProductId, savingsProductName, savingsAccountId, clienttype,
classification, legalForm, clientNonPerson);
}
}
@Override
public Collection<ClientData> retrieveActiveClientMembersOfCenter(final Long centerId) {
final AppUser currentUser = this.context.authenticatedUser();
final String hierarchy = currentUser.getOffice().getHierarchy();
final String hierarchySearchString = hierarchy + "%";
final String sql = "select "
+ this.membersOfGroupMapper.schema()
+ " left join m_group g on pgc.group_id=g.id where o.hierarchy like ? and g.parent_id = ? and c.status_enum = ? group by c.id";
return this.jdbcTemplate.query(sql, this.membersOfGroupMapper,
new Object[] { hierarchySearchString, centerId, ClientStatus.ACTIVE.getValue() });
}
private static final class ClientMapper implements RowMapper<ClientData> {
private final String schema;
public ClientMapper() {
final StringBuilder builder = new StringBuilder(400);
builder.append("c.id as id, c.account_no as accountNo, c.external_id as externalId, c.status_enum as statusEnum,c.sub_status as subStatus, ");
builder.append("cvSubStatus.code_value as subStatusValue,cvSubStatus.code_description as subStatusDesc,c.office_id as officeId, o.name as officeName, ");
builder.append("c.transfer_to_office_id as transferToOfficeId, transferToOffice.name as transferToOfficeName, ");
builder.append("c.firstname as firstname, c.middlename as middlename, c.lastname as lastname, ");
builder.append("c.fullname as fullname, c.display_name as displayName, ");
builder.append("c.mobile_no as mobileNo, ");
builder.append("c.date_of_birth as dateOfBirth, ");
builder.append("c.gender_cv_id as genderId, ");
builder.append("cv.code_value as genderValue, ");
builder.append("c.client_type_cv_id as clienttypeId, ");
builder.append("cvclienttype.code_value as clienttypeValue, ");
builder.append("c.client_classification_cv_id as classificationId, ");
builder.append("cvclassification.code_value as classificationValue, ");
builder.append("c.legal_form_enum as legalFormEnum, ");
builder.append("c.submittedon_date as submittedOnDate, ");
builder.append("sbu.username as submittedByUsername, ");
builder.append("sbu.firstname as submittedByFirstname, ");
builder.append("sbu.lastname as submittedByLastname, ");
builder.append("c.closedon_date as closedOnDate, ");
builder.append("clu.username as closedByUsername, ");
builder.append("clu.firstname as closedByFirstname, ");
builder.append("clu.lastname as closedByLastname, ");
// builder.append("c.submittedon as submittedOnDate, ");
builder.append("acu.username as activatedByUsername, ");
builder.append("acu.firstname as activatedByFirstname, ");
builder.append("acu.lastname as activatedByLastname, ");
builder.append("cnp.constitution_cv_id as constitutionId, ");
builder.append("cvConstitution.code_value as constitutionValue, ");
builder.append("cnp.incorp_no as incorpNo, ");
builder.append("cnp.incorp_validity_till as incorpValidityTill, ");
builder.append("cnp.main_business_line_cv_id as mainBusinessLineId, ");
builder.append("cvMainBusinessLine.code_value as mainBusinessLineValue, ");
builder.append("cnp.remarks as remarks, ");
builder.append("c.activation_date as activationDate, c.image_id as imageId, ");
builder.append("c.staff_id as staffId, s.display_name as staffName, ");
builder.append("c.default_savings_product as savingsProductId, sp.name as savingsProductName, ");
builder.append("c.default_savings_account as savingsAccountId ");
builder.append("from m_client c ");
builder.append("join m_office o on o.id = c.office_id ");
builder.append("left join m_client_non_person cnp on cnp.client_id = c.id ");
builder.append("left join m_staff s on s.id = c.staff_id ");
builder.append("left join m_savings_product sp on sp.id = c.default_savings_product ");
builder.append("left join m_office transferToOffice on transferToOffice.id = c.transfer_to_office_id ");
builder.append("left join m_appuser sbu on sbu.id = c.submittedon_userid ");
builder.append("left join m_appuser acu on acu.id = c.activatedon_userid ");
builder.append("left join m_appuser clu on clu.id = c.closedon_userid ");
builder.append("left join m_code_value cv on cv.id = c.gender_cv_id ");
builder.append("left join m_code_value cvclienttype on cvclienttype.id = c.client_type_cv_id ");
builder.append("left join m_code_value cvclassification on cvclassification.id = c.client_classification_cv_id ");
builder.append("left join m_code_value cvSubStatus on cvSubStatus.id = c.sub_status ");
builder.append("left join m_code_value cvConstitution on cvConstitution.id = cnp.constitution_cv_id ");
builder.append("left join m_code_value cvMainBusinessLine on cvMainBusinessLine.id = cnp.main_business_line_cv_id ");
this.schema = builder.toString();
}
public String schema() {
return this.schema;
}
@Override
public ClientData mapRow(final ResultSet rs, @SuppressWarnings("unused") final int rowNum) throws SQLException {
final String accountNo = rs.getString("accountNo");
final Integer statusEnum = JdbcSupport.getInteger(rs, "statusEnum");
final EnumOptionData status = ClientEnumerations.status(statusEnum);
final Long subStatusId = JdbcSupport.getLong(rs, "subStatus");
final String subStatusValue = rs.getString("subStatusValue");
final String subStatusDesc = rs.getString("subStatusDesc");
final boolean isActive = false;
final CodeValueData subStatus = CodeValueData.instance(subStatusId, subStatusValue, subStatusDesc, isActive);
final Long officeId = JdbcSupport.getLong(rs, "officeId");
final String officeName = rs.getString("officeName");
final Long transferToOfficeId = JdbcSupport.getLong(rs, "transferToOfficeId");
final String transferToOfficeName = rs.getString("transferToOfficeName");
final Long id = JdbcSupport.getLong(rs, "id");
final String firstname = rs.getString("firstname");
final String middlename = rs.getString("middlename");
final String lastname = rs.getString("lastname");
final String fullname = rs.getString("fullname");
final String displayName = rs.getString("displayName");
final String externalId = rs.getString("externalId");
final String mobileNo = rs.getString("mobileNo");
final LocalDate dateOfBirth = JdbcSupport.getLocalDate(rs, "dateOfBirth");
final Long genderId = JdbcSupport.getLong(rs, "genderId");
final String genderValue = rs.getString("genderValue");
final CodeValueData gender = CodeValueData.instance(genderId, genderValue);
final Long clienttypeId = JdbcSupport.getLong(rs, "clienttypeId");
final String clienttypeValue = rs.getString("clienttypeValue");
final CodeValueData clienttype = CodeValueData.instance(clienttypeId, clienttypeValue);
final Long classificationId = JdbcSupport.getLong(rs, "classificationId");
final String classificationValue = rs.getString("classificationValue");
final CodeValueData classification = CodeValueData.instance(classificationId, classificationValue);
final LocalDate activationDate = JdbcSupport.getLocalDate(rs, "activationDate");
final Long imageId = JdbcSupport.getLong(rs, "imageId");
final Long staffId = JdbcSupport.getLong(rs, "staffId");
final String staffName = rs.getString("staffName");
final Long savingsProductId = JdbcSupport.getLong(rs, "savingsProductId");
final String savingsProductName = rs.getString("savingsProductName");
final Long savingsAccountId = JdbcSupport.getLong(rs, "savingsAccountId");
final LocalDate closedOnDate = JdbcSupport.getLocalDate(rs, "closedOnDate");
final String closedByUsername = rs.getString("closedByUsername");
final String closedByFirstname = rs.getString("closedByFirstname");
final String closedByLastname = rs.getString("closedByLastname");
final LocalDate submittedOnDate = JdbcSupport.getLocalDate(rs, "submittedOnDate");
final String submittedByUsername = rs.getString("submittedByUsername");
final String submittedByFirstname = rs.getString("submittedByFirstname");
final String submittedByLastname = rs.getString("submittedByLastname");
final String activatedByUsername = rs.getString("activatedByUsername");
final String activatedByFirstname = rs.getString("activatedByFirstname");
final String activatedByLastname = rs.getString("activatedByLastname");
final Integer legalFormEnum = JdbcSupport.getInteger(rs, "legalFormEnum");
EnumOptionData legalForm = null;
if(legalFormEnum != null)
legalForm = ClientEnumerations.legalForm(legalFormEnum);
final Long constitutionId = JdbcSupport.getLong(rs, "constitutionId");
final String constitutionValue = rs.getString("constitutionValue");
final CodeValueData constitution = CodeValueData.instance(constitutionId, constitutionValue);
final String incorpNo = rs.getString("incorpNo");
final LocalDate incorpValidityTill = JdbcSupport.getLocalDate(rs, "incorpValidityTill");
final Long mainBusinessLineId = JdbcSupport.getLong(rs, "mainBusinessLineId");
final String mainBusinessLineValue = rs.getString("mainBusinessLineValue");
final CodeValueData mainBusinessLine = CodeValueData.instance(mainBusinessLineId, mainBusinessLineValue);
final String remarks = rs.getString("remarks");
final ClientNonPersonData clientNonPerson = new ClientNonPersonData(constitution, incorpNo, incorpValidityTill, mainBusinessLine, remarks);
final ClientTimelineData timeline = new ClientTimelineData(submittedOnDate, submittedByUsername, submittedByFirstname,
submittedByLastname, activationDate, activatedByUsername, activatedByFirstname, activatedByLastname, closedOnDate,
closedByUsername, closedByFirstname, closedByLastname);
return ClientData.instance(accountNo, status, subStatus, officeId, officeName, transferToOfficeId, transferToOfficeName, id,
firstname, middlename, lastname, fullname, displayName, externalId, mobileNo, dateOfBirth, gender, activationDate,
imageId, staffId, staffName, timeline, savingsProductId, savingsProductName, savingsAccountId, clienttype,
classification, legalForm, clientNonPerson);
}
}
private static final class ParentGroupsMapper implements RowMapper<GroupGeneralData> {
public String parentGroupsSchema() {
return "gp.id As groupId , gp.account_no as accountNo, gp.display_name As groupName from m_client cl JOIN m_group_client gc ON cl.id = gc.client_id "
+ "JOIN m_group gp ON gp.id = gc.group_id WHERE cl.id = ?";
}
@Override
public GroupGeneralData mapRow(final ResultSet rs, @SuppressWarnings("unused") final int rowNum) throws SQLException {
final Long groupId = JdbcSupport.getLong(rs, "groupId");
final String groupName = rs.getString("groupName");
final String accountNo = rs.getString("accountNo");
return GroupGeneralData.lookup(groupId, accountNo, groupName);
}
}
private static final class ClientLookupMapper implements RowMapper<ClientData> {
private final String schema;
public ClientLookupMapper() {
final StringBuilder builder = new StringBuilder(200);
builder.append("c.id as id, c.display_name as displayName, ");
builder.append("c.office_id as officeId, o.name as officeName ");
builder.append("from m_client c ");
builder.append("join m_office o on o.id = c.office_id ");
this.schema = builder.toString();
}
public String schema() {
return this.schema;
}
@Override
public ClientData mapRow(final ResultSet rs, @SuppressWarnings("unused") final int rowNum) throws SQLException {
final Long id = rs.getLong("id");
final String displayName = rs.getString("displayName");
final Long officeId = rs.getLong("officeId");
final String officeName = rs.getString("officeName");
return ClientData.lookup(id, displayName, officeId, officeName);
}
}
@Override
public ClientData retrieveClientByIdentifier(final Long identifierTypeId, final String identifierKey) {
try {
final ClientIdentifierMapper mapper = new ClientIdentifierMapper();
final String sql = "select " + mapper.clientLookupByIdentifierSchema();
return this.jdbcTemplate.queryForObject(sql, mapper, new Object[] { identifierTypeId, identifierKey });
} catch (final EmptyResultDataAccessException e) {
return null;
}
}
private static final class ClientIdentifierMapper implements RowMapper<ClientData> {
public String clientLookupByIdentifierSchema() {
return "c.id as id, c.account_no as accountNo, c.firstname as firstname, c.middlename as middlename, c.lastname as lastname, "
+ "c.fullname as fullname, c.display_name as displayName," + "c.office_id as officeId, o.name as officeName "
+ " from m_client c, m_office o, m_client_identifier ci " + "where o.id = c.office_id and c.id=ci.client_id "
+ "and ci.document_type_id= ? and ci.document_key like ?";
}
@Override
public ClientData mapRow(final ResultSet rs, @SuppressWarnings("unused") final int rowNum) throws SQLException {
final Long id = rs.getLong("id");
final String accountNo = rs.getString("accountNo");
final String firstname = rs.getString("firstname");
final String middlename = rs.getString("middlename");
final String lastname = rs.getString("lastname");
final String fullname = rs.getString("fullname");
final String displayName = rs.getString("displayName");
final Long officeId = rs.getLong("officeId");
final String officeName = rs.getString("officeName");
return ClientData.clientIdentifier(id, accountNo, firstname, middlename, lastname, fullname, displayName, officeId, officeName);
}
}
private Long defaultToUsersOfficeIfNull(final Long officeId) {
Long defaultOfficeId = officeId;
if (defaultOfficeId == null) {
defaultOfficeId = this.context.authenticatedUser().getOffice().getId();
}
return defaultOfficeId;
}
@Override
public ClientData retrieveAllNarrations(final String clientNarrations) {
final List<CodeValueData> narrations = new ArrayList<>(this.codeValueReadPlatformService.retrieveCodeValuesByCode(clientNarrations));
final Collection<CodeValueData> clientTypeOptions = null;
final Collection<CodeValueData> clientClassificationOptions = null;
final Collection<CodeValueData> clientNonPersonConstitutionOptions = null;
final Collection<CodeValueData> clientNonPersonMainBusinessLineOptions = null;
final List<EnumOptionData> clientLegalFormOptions = null;
return ClientData.template(null, null, null, null, narrations, null, null, clientTypeOptions, clientClassificationOptions,
clientNonPersonConstitutionOptions, clientNonPersonMainBusinessLineOptions, clientLegalFormOptions);
}
}