Java tutorial
/** * Licensed to the Apache Software Foundation (ASF) under one * or more contributor license agreements. See the NOTICE file * distributed with this work for additional information * regarding copyright ownership. The ASF licenses this file * to you under the Apache License, Version 2.0 (the * "License"); you may not use this file except in compliance * with the License. You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, * software distributed under the License is distributed on an * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY * KIND, either express or implied. See the License for the * specific language governing permissions and limitations * under the License. */ package com.gst.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 com.gst.infrastructure.codes.data.CodeValueData; import com.gst.infrastructure.codes.service.CodeValueReadPlatformService; import com.gst.infrastructure.configuration.data.GlobalConfigurationPropertyData; import com.gst.infrastructure.configuration.service.ConfigurationReadPlatformService; import com.gst.infrastructure.core.api.ApiParameterHelper; import com.gst.infrastructure.core.data.EnumOptionData; import com.gst.infrastructure.core.domain.JdbcSupport; import com.gst.infrastructure.core.service.Page; import com.gst.infrastructure.core.service.PaginationHelper; import com.gst.infrastructure.core.service.RoutingDataSource; import com.gst.infrastructure.core.service.SearchParameters; import com.gst.infrastructure.dataqueries.data.DatatableData; import com.gst.infrastructure.dataqueries.data.EntityTables; import com.gst.infrastructure.dataqueries.data.StatusEnum; import com.gst.infrastructure.dataqueries.service.EntityDatatableChecksReadService; import com.gst.infrastructure.security.service.PlatformSecurityContext; import com.gst.organisation.office.data.OfficeData; import com.gst.organisation.office.service.OfficeReadPlatformService; import com.gst.organisation.staff.data.StaffData; import com.gst.organisation.staff.service.StaffReadPlatformService; import com.gst.portfolio.address.data.AddressData; import com.gst.portfolio.address.service.AddressReadPlatformService; import com.gst.portfolio.client.api.ClientApiConstants; import com.gst.portfolio.client.data.ClientData; import com.gst.portfolio.client.data.ClientNonPersonData; import com.gst.portfolio.client.data.ClientTimelineData; import com.gst.portfolio.client.domain.ClientEnumerations; import com.gst.portfolio.client.domain.ClientStatus; import com.gst.portfolio.client.domain.LegalForm; import com.gst.portfolio.client.exception.ClientNotFoundException; import com.gst.portfolio.group.data.GroupGeneralData; import com.gst.portfolio.savings.data.SavingsProductData; import com.gst.portfolio.savings.service.SavingsProductReadPlatformService; import com.gst.useradministration.domain.AppUser; import org.joda.time.LocalDate; 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(); private final AddressReadPlatformService addressReadPlatformService; private final ConfigurationReadPlatformService configurationReadPlatformService; private final EntityDatatableChecksReadService entityDatatableChecksReadService; @Autowired public ClientReadPlatformServiceImpl(final PlatformSecurityContext context, final RoutingDataSource dataSource, final OfficeReadPlatformService officeReadPlatformService, final StaffReadPlatformService staffReadPlatformService, final CodeValueReadPlatformService codeValueReadPlatformService, final SavingsProductReadPlatformService savingsProductReadPlatformService, final AddressReadPlatformService addressReadPlatformService, final ConfigurationReadPlatformService configurationReadPlatformService, final EntityDatatableChecksReadService entityDatatableChecksReadService) { this.context = context; this.officeReadPlatformService = officeReadPlatformService; this.jdbcTemplate = new JdbcTemplate(dataSource); this.staffReadPlatformService = staffReadPlatformService; this.codeValueReadPlatformService = codeValueReadPlatformService; this.savingsProductReadPlatformService = savingsProductReadPlatformService; this.addressReadPlatformService = addressReadPlatformService; this.configurationReadPlatformService = configurationReadPlatformService; this.entityDatatableChecksReadService = entityDatatableChecksReadService; } @Override public ClientData retrieveTemplate(final Long officeId, final boolean staffInSelectedOfficeOnly) { this.context.authenticatedUser(); final Long defaultOfficeId = defaultToUsersOfficeIfNull(officeId); AddressData address = null; final Collection<OfficeData> offices = this.officeReadPlatformService.retrieveAllOfficesForDropdown(); final Collection<SavingsProductData> savingsProductDatas = this.savingsProductReadPlatformService .retrieveAllForLookupByType(null); final GlobalConfigurationPropertyData configuration = this.configurationReadPlatformService .retrieveGlobalConfiguration("Enable-Address"); final Boolean isAddressEnabled = configuration.isEnabled(); if (isAddressEnabled) { address = this.addressReadPlatformService.retrieveTemplate(); } 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()); final List<DatatableData> datatableTemplates = this.entityDatatableChecksReadService .retrieveTemplates(StatusEnum.CREATE.getCode().longValue(), EntityTables.CLIENT.getName(), null); return ClientData.template(defaultOfficeId, new LocalDate(), offices, staffOptions, null, genderOptions, savingsProductDatas, clientTypeOptions, clientClassificationOptions, clientNonPersonConstitutionOptions, clientNonPersonMainBusinessLineOptions, clientLegalFormOptions, address, isAddressEnabled, datatableTemplates); } @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, null, null, null); } }