org.mifos.application.importexport.xls.XlsClientsImporter.java Source code

Java tutorial

Introduction

Here is the source code for org.mifos.application.importexport.xls.XlsClientsImporter.java

Source

/*
 * Copyright (c) 2005-2011 Grameen Foundation USA
 * All rights reserved.
 *
 * Licensed 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.
 *
 * See also http://www.apache.org/licenses/LICENSE-2.0.html for an
 * explanation of the license and how it is applied.
 */

package org.mifos.application.importexport.xls;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.MessageSource;
import org.springframework.context.MessageSourceAware;

import org.joda.time.DateMidnight;
import org.joda.time.DateTime;
import org.joda.time.LocalDate;
import org.joda.time.Years;
import org.mifos.application.meeting.util.helpers.MeetingType;
import org.mifos.application.meeting.util.helpers.RankOfDay;
import org.mifos.application.meeting.util.helpers.RecurrenceType;
import org.mifos.application.meeting.util.helpers.WeekDay;
import org.mifos.config.ClientRules;
import org.mifos.config.exceptions.ConfigurationException;
import org.mifos.config.util.helpers.HiddenMandatoryFieldNamesConstants;
import org.mifos.customers.exceptions.CustomerException;
import org.mifos.customers.group.business.GroupBO;
import org.mifos.customers.office.persistence.OfficeDao;
import org.mifos.customers.persistence.CustomerDao;
import org.mifos.customers.personnel.business.PersonnelBO;
import org.mifos.customers.personnel.persistence.LegacyPersonnelDao;
import org.mifos.customers.util.helpers.CustomerStatus;
import org.mifos.dto.domain.AddressDto;
import org.mifos.dto.domain.ApplicableAccountFeeDto;
import org.mifos.dto.domain.ClientCreationDetail;
import org.mifos.dto.domain.ImportedClientDetail;
import org.mifos.dto.domain.MeetingDetailsDto;
import org.mifos.dto.domain.MeetingDto;
import org.mifos.dto.domain.MeetingRecurrenceDto;
import org.mifos.dto.domain.MeetingTypeDto;
import org.mifos.dto.domain.OfficeDto;
import org.mifos.dto.domain.ParsedClientsDto;
import org.mifos.dto.domain.ValueListElement;
import org.mifos.dto.screen.ClientFamilyDetailDto;
import org.mifos.dto.screen.ClientNameDetailDto;
import org.mifos.dto.screen.ClientPersonalDetailDto;
import org.mifos.framework.business.util.Address;
import org.mifos.framework.components.fieldConfiguration.util.helpers.FieldConfig;
import org.mifos.framework.util.helpers.DateUtils;

public class XlsClientsImporter implements MessageSourceAware {

    private static final Short CLIENT_NAME_TYPE = 3;
    private static final Short SPOUSE_NAME_TYPE = 1;
    private static final Short FATHER_NAME_TYPE = 2;

    private static final String CLIENT_ENTITY = "Client.";

    private static final String WEEK_FREQUENCY = "week(s)";
    private static final String MONTH_FREQUENCY = "month(s)";

    private MessageSource messageSource;
    private Locale locale;

    private final CustomerDao customerDao;
    private final LegacyPersonnelDao legacyPersonnelDao;
    private final OfficeDao officeDao;

    @Autowired
    public XlsClientsImporter(CustomerDao customerDao, LegacyPersonnelDao legacyPersonnelDao, OfficeDao officeDao) {
        this.customerDao = customerDao;
        this.legacyPersonnelDao = legacyPersonnelDao;
        this.officeDao = officeDao;
    }

    @Override
    public void setMessageSource(MessageSource messageSource) {
        this.messageSource = messageSource;
    }

    public Locale getLocale() {
        return locale;
    }

    public void setLocale(Locale locale) {
        this.locale = locale;
    }

    public ParsedClientsDto parse(final InputStream input) {

        final List<ValueListElement> buisnessActivitiesList = customerDao.retrieveBusinessActivities();
        final List<ValueListElement> gendersList = customerDao.retrieveGenders();
        final List<ValueListElement> citizenshipsList = customerDao.retrieveCitizenship();
        final List<ValueListElement> ethinicitiesList = customerDao.retrieveEthnicity();
        final List<ValueListElement> educationLevelsList = customerDao.retrieveEducationLevels();
        final List<ValueListElement> handicappedList = customerDao.retrieveHandicapped();
        final List<ValueListElement> povertyStatusList = customerDao.retrievePoverty();
        final List<ValueListElement> maritalStatusList = customerDao.retrieveMaritalStatuses();
        final List<ValueListElement> salutationsList = customerDao.retrieveSalutations();

        final List<OfficeDto> allOfficess = officeDao.findAllOffices();

        final FieldConfig fieldConfig = FieldConfig.getInstance();

        final List<String> globalCustNums = new ArrayList<String>();

        final List<String> errorsList = new ArrayList<String>();
        final List<ImportedClientDetail> parsedClientDetails = new ArrayList<ImportedClientDetail>();

        try {
            final HSSFWorkbook workbook = new HSSFWorkbook(input);
            final HSSFSheet sheet = workbook.getSheetAt(0);

            /* test first data row */
            HSSFRow row = sheet.getRow(XlsImportConstants.FIRST_CLIENT_ROW.value());
            if (row == null) {
                errorsList.add(getMessage(XlsMessageConstants.NOT_ENOUGH_INPUT_ROW));
            }

            @SuppressWarnings("rawtypes")
            Iterator rowIterator = sheet.rowIterator();

            /* Skip first rows */
            if (errorsList.isEmpty()) {
                for (int i = 0; i < XlsImportConstants.SKIPPED_ROWS.value(); i++) {
                    if (rowIterator.hasNext()) {
                        rowIterator.next();
                    } else {
                        errorsList.add(getMessage(XlsMessageConstants.NOT_ENOUGH_INPUT_ROW));
                        break;
                    }
                }
            }

            XlsImportConstants currentCell = XlsImportConstants.CLIENT_NUM_CELL;
            int friendlyRowNumber = 0;

            /* Parse client data */
            if (errorsList.isEmpty()) {
                while (rowIterator.hasNext()) {
                    try {
                        row = (HSSFRow) rowIterator.next();
                        friendlyRowNumber = row.getRowNum() + 1;
                        /* Get data from sheet */
                        currentCell = XlsImportConstants.CLIENT_NUM_CELL;
                        String clientGlobalNum = getCellStringValue(row, currentCell);
                        if (StringUtils.isBlank(clientGlobalNum)) {
                            clientGlobalNum = null; // generate number
                        } else {
                            // check for duplicates
                            validateGlobalCustNum(clientGlobalNum, globalCustNums);
                            globalCustNums.add(clientGlobalNum);
                        }

                        currentCell = XlsImportConstants.BRANCH_SHORT_NAME_CELL;
                        final String branchShortName = getCellStringValue(row, currentCell);
                        final Short branchOfficeId = getBranchId(branchShortName, allOfficess);

                        currentCell = XlsImportConstants.GROUP_GLOBAL_NUM_CELL;
                        final String groupGlobalNum = getCellStringValue(row, currentCell);
                        validateGroup(groupGlobalNum);

                        if (StringUtils.isBlank(groupGlobalNum) && branchOfficeId == null) {
                            String error = getRowError(friendlyRowNumber)
                                    + getMessage(XlsMessageConstants.OFFICE_AND_BRANCH);
                            errorsList.add(error);
                            continue;
                        }

                        currentCell = XlsImportConstants.SALUTATION_CELL;
                        final String salutation = getCellStringValue(row, currentCell);
                        final Integer salutationId = getValueElementId(salutation, salutationsList);
                        validateMandatoryField(salutationId);

                        currentCell = XlsImportConstants.FIRST_NAME_CELL;
                        final String clientFirstName = getCellStringValue(row, currentCell);
                        validateMandatoryField(clientFirstName);

                        currentCell = XlsImportConstants.MIDDLE_NAME_CELL;
                        final String clientMiddleName = getCellStringValue(row, currentCell);
                        if (fieldConfig.isFieldManadatory(
                                CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.MIDDLE_NAME)) {
                            validateMandatoryField(clientMiddleName);
                        }

                        currentCell = XlsImportConstants.LAST_NAME_CELL;
                        final String clientLastName = getCellStringValue(row, currentCell);
                        validateMandatoryField(clientLastName);

                        currentCell = XlsImportConstants.SECOND_LAST_NAME_CELL;
                        final String clientSecondLastName = getCellStringValue(row, currentCell);
                        if (fieldConfig.isFieldManadatory(
                                CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.SECOND_LAST_NAME)) {
                            validateMandatoryField(clientSecondLastName);
                        }

                        currentCell = XlsImportConstants.GOVERNMENT_ID_CELL;
                        String governmentId = getCellStringValue(row, currentCell);
                        if (fieldConfig.isFieldManadatory(
                                CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.GOVERNMENT_ID)) {
                            validateMandatoryField(governmentId);
                        }

                        currentCell = XlsImportConstants.DATE_OF_BIRTH_CELL;
                        final Date dateOfBirth = getCellDateValue(row, currentCell);
                        validateMandatoryField(dateOfBirth);
                        validateAge(dateOfBirth);

                        currentCell = XlsImportConstants.GENDER_CELL;
                        final String gender = getCellStringValue(row, currentCell);
                        final Short genderId = intToShort(getValueElementId(gender, gendersList));
                        validateMandatoryField(genderId);

                        currentCell = XlsImportConstants.MARITAL_STATUS_CELL;
                        final String maritalStatus = getCellStringValue(row, currentCell);
                        final Integer maritalStatusId = getValueElementId(maritalStatus, maritalStatusList);
                        if (fieldConfig.isFieldManadatory(
                                CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.MARITAL_STATUS)) {
                            validateMandatoryField(maritalStatusId);
                        }

                        currentCell = XlsImportConstants.NUMBER_OF_CHILDREN_CELL;
                        final Short numberOfChildren = intToShort(getCellIntegerValue(row, currentCell));
                        if (fieldConfig.isFieldManadatory(
                                CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.NUMBER_OF_CHILDREN)) {
                            validateMandatoryField(numberOfChildren);
                        }

                        currentCell = XlsImportConstants.CITIZENSHIP_CELL;
                        final String citizenship = getCellStringValue(row, currentCell);
                        final Integer citizenshipId = getValueElementId(citizenship, citizenshipsList);
                        if (fieldConfig.isFieldManadatory(
                                CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.CITIZENSHIP)) {
                            validateMandatoryField(citizenshipId);
                        }

                        currentCell = XlsImportConstants.ETHINICITY_CELL;
                        final String ethinicity = getCellStringValue(row, currentCell);
                        final Integer ethinicityId = getValueElementId(ethinicity, ethinicitiesList);
                        if (fieldConfig
                                .isFieldHidden(CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.ETHNICITY)) {
                            validateMandatoryField(ethinicityId);
                        }

                        currentCell = XlsImportConstants.EDUCATION_LEVEL_CELL;
                        final String educationLevel = getCellStringValue(row, currentCell);
                        final Integer educationLevelId = getValueElementId(educationLevel, educationLevelsList);
                        if (fieldConfig.isFieldHidden(
                                CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.EDUCATION_LEVEL)) {
                            validateMandatoryField(educationLevelId);
                        }

                        currentCell = XlsImportConstants.ACTIVITIES_CELL;
                        final String activites = getCellStringValue(row, currentCell);
                        final Integer activityId = getValueElementId(activites, buisnessActivitiesList);
                        if (fieldConfig.isFieldManadatory(
                                CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.BUSINESS_ACTIVITIES)) {
                            validateMandatoryField(activityId);
                        }

                        currentCell = XlsImportConstants.POVERTY_STATUS_CELL;
                        final String povertyStatus = getCellStringValue(row, currentCell);
                        final Short povertyStatusId = intToShort(
                                getValueElementId(povertyStatus, povertyStatusList));
                        if (fieldConfig.isFieldManadatory(
                                CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.POVERTY_STATUS)) {
                            validateMandatoryField(povertyStatusId);
                        }

                        currentCell = XlsImportConstants.HANDICAPPED_CELL;
                        final String handicapped = getCellStringValue(row, currentCell);
                        final Integer handicappedId = getValueElementId(handicapped, handicappedList);
                        if (fieldConfig.isFieldManadatory(
                                CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.HANDICAPPED)) {
                            validateMandatoryField(handicappedId);
                        }

                        currentCell = XlsImportConstants.SPOUSE_FATHER_RELATIONSHIP_CELL;
                        final String spouseOrFather = getCellStringValue(row, currentCell);
                        final Short spouseFatherNameType = getSpouseNameType(spouseOrFather);

                        final boolean familyMandatory = fieldConfig.isFieldManadatory(
                                CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.FAMILY_DETAILS);
                        if (familyMandatory) {
                            validateMandatoryField(spouseFatherNameType);
                        }

                        currentCell = XlsImportConstants.SPOUSE_FIRST_NAME_CELL;
                        final String spouseFirstName = getCellStringValue(row, currentCell);
                        if (familyMandatory) {
                            validateMandatoryField(spouseFirstName);
                        }

                        currentCell = XlsImportConstants.SPOUSE_MIDDLE_NAME_CELL;
                        final String spouseMiddleName = getCellStringValue(row, currentCell);

                        currentCell = XlsImportConstants.SPOUSE_SECOND_LAST_NAME_CELL;
                        final String spouseSecondLastName = getCellStringValue(row, currentCell);
                        if (familyMandatory && fieldConfig.isFieldManadatory(CLIENT_ENTITY
                                + HiddenMandatoryFieldNamesConstants.SPOUSE_FATHER_SECOND_LAST_NAME)) {
                            validateMandatoryField(spouseSecondLastName);
                        }

                        currentCell = XlsImportConstants.SPOUSE_LAST_NAME_CELL;
                        final String spouseLastName = getCellStringValue(row, currentCell);
                        if (familyMandatory) {
                            validateMandatoryField(spouseLastName);
                        }

                        currentCell = XlsImportConstants.ADDRESS_CELL;
                        final String address = getCellStringValue(row, currentCell);
                        if (fieldConfig
                                .isFieldManadatory(CLIENT_ENTITY + HiddenMandatoryFieldNamesConstants.ADDRESS1)) {
                            validateMandatoryField(address);
                        }

                        currentCell = XlsImportConstants.CITY_DISTRICT_CELL;
                        final String cityDistrict = getCellStringValue(row, currentCell);

                        currentCell = XlsImportConstants.STATE_CELL;
                        final String state = getCellStringValue(row, currentCell);

                        currentCell = XlsImportConstants.COUNTRY_CELL;
                        final String country = getCellStringValue(row, currentCell);

                        currentCell = XlsImportConstants.POSTAL_CODE_CELL;
                        final String postalCode = getCellStringValue(row, currentCell);

                        currentCell = XlsImportConstants.TELEPHONE_CELL;
                        final String telephone = getCellStringValue(row, currentCell);

                        currentCell = XlsImportConstants.RECRUITED_BY_CELL;
                        final String recruitedBy = getCellStringValue(row, currentCell);
                        validateMandatoryField(recruitedBy);
                        final Short formedBy = getOfficerId(recruitedBy);

                        currentCell = XlsImportConstants.STATUS_CELL;
                        final String status = getCellStringValue(row, currentCell);
                        final Short statusId = statusToShort(status);

                        currentCell = XlsImportConstants.LOAN_OFFICER_CELL;
                        final String loanOfficer = getCellStringValue(row, currentCell);
                        if (!StringUtils.isBlank(groupGlobalNum) && !StringUtils.isBlank(loanOfficer)) {
                            throw new CellException(getMessage(XlsMessageConstants.LOAN_OFFICER_FOR_GROUP_CLIENT));
                        }

                        currentCell = XlsImportConstants.ACTIVATION_DATE_CELL;
                        final LocalDate activationDate = DateUtils
                                .getLocalDateFromDate(getCellDateValue(row, currentCell));
                        if (activationDate != null && !status.equals(getMessage(XlsMessageConstants.ACTIVE))) {
                            throw new CellException(
                                    getMessage(XlsMessageConstants.ACTIVE_STATUS_FOR_ACTIVATION_DATE));
                        }

                        /* Meeting data */

                        currentCell = XlsImportConstants.MEETING_FREQUENCY_CELL;
                        final Integer recurrenceType = getRecurrenceType(getCellStringValue(row, currentCell));

                        currentCell = XlsImportConstants.MEETING_RECUR_EVERY_WEEK_CELL;
                        final Integer recurEveryWeek = getCellIntegerValue(row, currentCell);

                        currentCell = XlsImportConstants.MEETING_ON_WEEK_CELL;
                        final Integer weeklyMeetingDay = getDayValue(getCellStringValue(row, currentCell));

                        currentCell = XlsImportConstants.MEETING_OPT1_DAY_CELL;
                        final Integer opt1Day = getCellIntegerValue(row, currentCell);
                        validateMonthDay(opt1Day);

                        currentCell = XlsImportConstants.MEETING_OPT1_EVERY_CELL;
                        final Integer opt1Every = getCellIntegerValue(row, currentCell);
                        validatePositive(opt1Every);

                        currentCell = XlsImportConstants.MEETING_OPT2_THE_CELL;
                        final Integer opt2The = getDayRankValue(getCellStringValue(row, currentCell));

                        currentCell = XlsImportConstants.MEETING_OPT2_DAY_CELL;
                        final Integer opt2Day = getDayValue(getCellStringValue(row, currentCell));

                        currentCell = XlsImportConstants.MEETING_OPT2_EVERY_CELL;
                        final Integer opt2Every = getCellIntegerValue(row, currentCell);
                        validatePositive(opt2Every);

                        currentCell = XlsImportConstants.MEETING_LOCATION_CELL;
                        final String meetingLocation = getCellStringValue(row, currentCell);

                        Integer recurrenceDayNumber = 0;
                        Integer recurrenceWeekOfMonth = 0;
                        Integer recurrenceDayOfWeek = 0;
                        Integer recurEvery = 0;
                        /*
                         * Validate meeting data
                         */
                        if (!StringUtils.isBlank(groupGlobalNum) && recurrenceType != null) {
                            // no meeting allowed for a group
                            throw new RowException(getMessage(XlsMessageConstants.MEETING_FOR_GROUP));
                        } else if (recurrenceType == null) {
                            // make sure everything is empty
                            if (recurEveryWeek != null || weeklyMeetingDay != null || opt1Day != null
                                    || opt1Every != null || opt2Day != null || opt2Every != null || opt2The != null
                                    || !StringUtils.isBlank(meetingLocation)) {
                                throw new RowException(getMessage(XlsMessageConstants.INCOMPLETE_MEETING_DATA));
                            }
                        } else if (recurrenceType == RecurrenceType.WEEKLY.getValue().intValue()) {
                            // make sure weekly data is set
                            if (recurEveryWeek == null || weeklyMeetingDay == null) {
                                throw new RowException(getMessage(XlsMessageConstants.INCOMPLETE_MEETING_DATA));
                            }
                            // make sure monthly details are empty
                            if (opt1Day != null || opt1Every != null || opt2Day != null || opt2Every != null
                                    || opt2The != null) {
                                throw new RowException(
                                        getMessage(XlsMessageConstants.MONTHLY_MEETING_DETAILS_NOT_EMPTY));
                            }
                            // set data
                            recurrenceDayOfWeek = weeklyMeetingDay;
                            recurEvery = recurEveryWeek;
                            // validate location
                            validateMandatoryField(meetingLocation);
                        } else { // monthly recurrence
                            // make sure weekly details are empty
                            if (recurEveryWeek != null || weeklyMeetingDay != null) {
                                throw new RowException(
                                        getMessage(XlsMessageConstants.WEEKLY_MEETING_DETAILS_NOT_EMPTY));
                            }
                            if (opt1Day == null) { // option 2
                                // make sure option 2 is set
                                if (opt2Day == null || opt2Every == null || opt2The == null) {
                                    throw new RowException(getMessage(XlsMessageConstants.INCOMPLETE_MEETING_DATA));
                                }
                                // make sure option 1 is empty
                                if (opt1Every != null) {
                                    throw new RowException(getMessage(XlsMessageConstants.OPTIONS_EXCLUSIVE));
                                }
                                // set data
                                recurrenceWeekOfMonth = opt2The;
                                recurrenceDayOfWeek = opt2Day;
                                recurEvery = opt2Every;
                            } else { // option 1
                                // make sure option 1 is set
                                if (opt1Every == null) {
                                    throw new RowException(getMessage(XlsMessageConstants.INCOMPLETE_MEETING_DATA));
                                }
                                // make sure option 2 is empty
                                if (opt2Day != null || opt2Every != null || opt2The != null) {
                                    throw new RowException(getMessage(XlsMessageConstants.OPTIONS_EXCLUSIVE));
                                }
                                // set data
                                recurrenceDayNumber = opt1Day;
                                recurEvery = opt1Every;
                            }
                            // validate location
                            validateMandatoryField(meetingLocation);
                        }
                        /*
                         * Create meeting data
                         */
                        MeetingDto meetingDto = null;
                        if (recurrenceType != null) {
                            final LocalDate meetingStartDate = new LocalDate();
                            final MeetingRecurrenceDto meetingRecurrenceDto = new MeetingRecurrenceDto(
                                    recurrenceDayNumber, recurrenceWeekOfMonth, recurrenceDayOfWeek);
                            final MeetingDetailsDto meetingDetailsDto = new MeetingDetailsDto(recurrenceType, null,
                                    recurEvery, meetingRecurrenceDto);
                            final MeetingTypeDto meetingTypeDto = new MeetingTypeDto(
                                    MeetingType.CUSTOMER_MEETING.getValue().intValue(), null, null);
                            meetingDto = new MeetingDto(meetingStartDate, meetingLocation, meetingTypeDto,
                                    meetingDetailsDto);
                        } else {

                        }

                        String clientName = buildName(clientFirstName, clientMiddleName, clientLastName,
                                clientSecondLastName);
                        customerDao.validateClientForDuplicateNameOrGovtId(clientName, dateOfBirth, governmentId);

                        /* Create dto's */
                        /* address */
                        final Address addressObject = new Address(address, null, null, cityDistrict, state, country,
                                postalCode, telephone);
                        final AddressDto addressDto = Address.toDto(addressObject);
                        /* Personal details */
                        final ClientNameDetailDto clientNameDetailDto = new ClientNameDetailDto(CLIENT_NAME_TYPE,
                                salutationId, clientFirstName, clientMiddleName, clientLastName,
                                clientSecondLastName);
                        final ClientPersonalDetailDto clientPersonalDetailDto = new ClientPersonalDetailDto(
                                ethinicityId, citizenshipId, handicappedId, activityId, maritalStatusId,
                                educationLevelId, numberOfChildren, genderId, povertyStatusId);
                        /* Spouse details */
                        ClientNameDetailDto spouseNameDetailDto = null;
                        if (spouseFatherNameType == null) {
                            spouseNameDetailDto = new ClientNameDetailDto();
                            spouseNameDetailDto.setFirstName("");
                            spouseNameDetailDto.setLastName("");
                        } else {
                            spouseNameDetailDto = new ClientNameDetailDto(spouseFatherNameType, null,
                                    spouseFirstName, spouseMiddleName, spouseLastName, spouseSecondLastName);
                        }
                        /* branch office */
                        Short officeId = 0;
                        Short loanOfficerId = null;
                        Short groupFlagValue = 1;
                        if (StringUtils.isBlank(groupGlobalNum)) {
                            if (statusId == CustomerStatus.CLIENT_ACTIVE.getValue() && meetingDto == null) {
                                String error = getRowError(friendlyRowNumber)
                                        + getMessage(XlsMessageConstants.NO_MEETING_ERROR);
                                errorsList.add(error);
                                continue;
                            }

                            groupFlagValue = 0;
                            officeId = branchOfficeId;

                            List<PersonnelBO> officers = legacyPersonnelDao
                                    .getActiveLoanOfficersUnderOffice(officeId);
                            if (officers.isEmpty()) {
                                String error = getCellError(friendlyRowNumber,
                                        XlsImportConstants.BRANCH_SHORT_NAME_CELL)
                                        + getMessage(XlsMessageConstants.NO_OFFICERS_ERROR, branchShortName);
                                errorsList.add(error);
                                continue;
                            }

                            loanOfficerId = null;
                            for (PersonnelBO officer : officers) {
                                if (officer.getDisplayName().equals(loanOfficer)) {
                                    loanOfficerId = officer.getPersonnelId();
                                    break;
                                }
                            }

                        } else {
                            validateGroupStatus(groupGlobalNum, statusId);
                        }

                        /* Not imported values */
                        final boolean trained = false;
                        final Date trainedDate = null;
                        final java.sql.Date mfiJoiningDate = null;
                        final String externalId = "";
                        final InputStream picture = null;
                        final List<ApplicableAccountFeeDto> feesToApply = null;
                        final List<ClientNameDetailDto> familyNames = null;
                        final List<ClientFamilyDetailDto> familyDetails = null;
                        final List<Short> selectedSavingsProducts = null;
                        /* Final dto */
                        final ClientCreationDetail clientCreationDetail = new ClientCreationDetail(
                                selectedSavingsProducts, clientName, statusId, mfiJoiningDate, externalId,
                                addressDto, formedBy, dateOfBirth, governmentId, trained, trainedDate,
                                groupFlagValue, clientNameDetailDto, clientPersonalDetailDto, spouseNameDetailDto,
                                picture, feesToApply, groupGlobalNum, familyNames, familyDetails, loanOfficerId,
                                officeId, activationDate);

                        validateDuplicateCustomers(clientCreationDetail, parsedClientDetails);

                        final ImportedClientDetail importedClientDetail = new ImportedClientDetail(
                                clientCreationDetail, clientGlobalNum, meetingDto);
                        parsedClientDetails.add(importedClientDetail);
                    } catch (RowException ex) {
                        final String error = getRowError(friendlyRowNumber) + ex.getMessage();
                        errorsList.add(error);
                    } catch (CustomerException ex) {
                        final String error = getRowError(friendlyRowNumber)
                                + getMessage(XlsMessageConstants.DUPLICATE_CLIENT_ERROR);
                        errorsList.add(error);
                    } catch (Exception ex) {
                        final String error = getCellError(friendlyRowNumber, currentCell) + ex.getMessage();
                        errorsList.add(error);
                    }
                }
            }
        } catch (Exception ex) {
            errorsList.add(getMessage(XlsMessageConstants.ERROR_READING_DOCUMENT, ex.getMessage()));
        }
        return new ParsedClientsDto(errorsList, parsedClientDetails);
    }

    private String getMessage(final XlsMessageConstants xlsMessageConstant) {
        return messageSource.getMessage(xlsMessageConstant.getText(), null, locale);
    }

    private String getMessage(final XlsMessageConstants xlsMessageConstant, final String param) {
        return messageSource.getMessage(xlsMessageConstant.getText(), new Object[] { param }, locale);
    }

    private String getMessage(final String key) {
        return messageSource.getMessage(key, null, locale);
    }

    private String getMessage(final XlsMessageConstants xlsMessageConstant, final Object[] params) {
        return messageSource.getMessage(xlsMessageConstant.getText(), params, locale);
    }

    private String getRowError(final int friendlyRowNumber) {
        final StringBuilder sb = new StringBuilder(
                getMessage(XlsMessageConstants.ROW_ERROR, String.valueOf(friendlyRowNumber)));
        sb.append(": ");
        return sb.toString();
    }

    private String getCellError(final int friendlyRowNumber, final XlsImportConstants cell) {
        final StringBuilder sb = new StringBuilder(
                getMessage(XlsMessageConstants.ROW_ERROR, String.valueOf(friendlyRowNumber)));
        sb.append(", ");
        sb.append(getMessage(XlsMessageConstants.CELL_ERROR, getMessage(cell.getCellNameKey())));
        sb.append(": ");
        return sb.toString();
    }

    private String getCellStringValue(final HSSFRow row, final XlsImportConstants xlsImportConstant) {
        final HSSFCell cell = row.getCell(xlsImportConstant.value(), HSSFRow.RETURN_BLANK_AS_NULL);
        if (cell != null) {
            switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                HSSFRichTextString richText = cell.getRichStringCellValue();
                return (richText == null) ? "" : richText.getString();
            case HSSFCell.CELL_TYPE_NUMERIC:
                int intVal = (int) cell.getNumericCellValue();
                return String.valueOf(intVal);
            default:
                return "";
            }
        } else {
            return "";
        }
    }

    private Date getCellDateValue(final HSSFRow row, final XlsImportConstants xlsImportConstant) throws Exception {
        HSSFCell cell = null;
        try {
            cell = row.getCell(xlsImportConstant.value(), HSSFRow.RETURN_BLANK_AS_NULL);
            return (cell == null) ? null : cell.getDateCellValue();
        } catch (Exception ex) {
            String invalidDateString = (cell == null) ? "" : getCellStringValue(row, xlsImportConstant);
            throw new Exception(getMessage(XlsMessageConstants.INVALID_DATE, invalidDateString));
        }
    }

    private Integer getCellIntegerValue(final HSSFRow row, final XlsImportConstants xlsImportConstant) {
        final HSSFCell cell = row.getCell(xlsImportConstant.value(), HSSFRow.RETURN_BLANK_AS_NULL);
        if (cell == null) {
            return null;
        } else {
            Double val = cell.getNumericCellValue();
            return val.intValue();
        }
    }

    private Integer getValueElementId(final String value, final List<ValueListElement> elements)
            throws CellException {
        if (StringUtils.isBlank(value)) {
            return null;
        }

        for (ValueListElement element : elements) {
            if (element.getName().toLowerCase().equals(value.toLowerCase())) {
                return element.getId();
            }
        }

        throw new CellException(getMessage(XlsMessageConstants.NOT_FOUND_ERROR, value));

    }

    private String buildName(final String firstName, final String middleName, final String lastName,
            final String secondLastName) {
        final StringBuilder sb = new StringBuilder(firstName);

        if (!StringUtils.isBlank(middleName)) {
            sb.append(" ").append(middleName);
        }

        sb.append(" ").append(lastName);

        if (!StringUtils.isBlank(secondLastName)) {
            sb.append(" ").append(secondLastName);
        }
        return sb.toString();
    }

    private Short getSpouseNameType(final String spouseOrFather) throws CellException {
        if (StringUtils.isBlank(spouseOrFather)) {
            return null;
        }

        final String spouse = getMessage(XlsMessageConstants.SPOUSE);
        if (spouse.equals(spouseOrFather)) {
            return SPOUSE_NAME_TYPE;
        }
        final String father = getMessage(XlsMessageConstants.FATHER);
        if (father.equals(spouseOrFather)) {
            return FATHER_NAME_TYPE;
        }

        throw new CellException(getMessage(XlsMessageConstants.NOT_FOUND_ERROR, spouseOrFather));
    }

    private Short intToShort(final Integer arg) {
        return (arg == null) ? null : arg.shortValue();
    }

    private Short getOfficerId(final String recruitedBy) throws CellException {
        if (StringUtils.isBlank(recruitedBy)) {
            return null;
        } else {
            try {
                PersonnelBO personnelBO = legacyPersonnelDao.getPersonnelByDisplayName(recruitedBy);
                return personnelBO.getPersonnelId();
            } catch (Exception ex) {
                throw new CellException(getMessage(XlsMessageConstants.NOT_FOUND_ERROR, recruitedBy));
            }
        }
    }

    private void validateGlobalCustNum(final String globalCustNum, final List<String> newCustGlobalNums)
            throws CellException {
        if (!StringUtils.isBlank(globalCustNum) && !newCustGlobalNums.contains(globalCustNum)) {
            if (customerDao.findClientBySystemId(globalCustNum) == null
                    && customerDao.findGroupBySystemId(globalCustNum) == null
                    && customerDao.findCenterBySystemId(globalCustNum) == null) {
                return; // success
            }
        }
        throw new CellException(getMessage(XlsMessageConstants.DUPLICATE_GLOBAL_NUM_ERROR, globalCustNum));
    }

    private Short statusToShort(final String status) throws CellException {
        if (StringUtils.isBlank(status)) {
            return CustomerStatus.CLIENT_PENDING.getValue();
        } else if (status.equals(getMessage(XlsMessageConstants.PARTIALLY_APPROVED))) {
            return CustomerStatus.CLIENT_PARTIAL.getValue();
        } else if (status.equals(getMessage(XlsMessageConstants.PENDING_APPROVAL))) {
            return CustomerStatus.CLIENT_PENDING.getValue();
        } else if (status.equals(getMessage(XlsMessageConstants.ACTIVE))) {
            return CustomerStatus.CLIENT_ACTIVE.getValue();
        } else {
            throw new CellException(getMessage(XlsMessageConstants.NOT_FOUND_ERROR, status));
        }
    }

    private void validateMandatoryField(final String string) throws CellException {
        if (string == null || StringUtils.isBlank(string)) {
            throw new CellException(getMessage(XlsMessageConstants.MANDATORY_ERROR));
        }
    }

    private void validateMandatoryField(final Object arg) throws CellException {
        if (arg == null) {
            throw new CellException(getMessage(XlsMessageConstants.MANDATORY_ERROR));
        }
    }

    private void validateGroup(final String globalGroupNum) throws CellException {
        if (StringUtils.isBlank(globalGroupNum)) {
            return;
        }

        final GroupBO group = customerDao.findGroupBySystemId(globalGroupNum);
        if (group == null) {
            throw new CellException(getMessage(XlsMessageConstants.GROUP_NOT_FOUND_ERROR, globalGroupNum));
        }

        final CustomerStatus groupStatus = group.getStatus();
        if (groupStatus == CustomerStatus.GROUP_CANCELLED) {
            throw new CellException(getMessage(XlsMessageConstants.GROUP_CANCELED, globalGroupNum));
        } else if (groupStatus == CustomerStatus.GROUP_CLOSED) {
            throw new CellException(getMessage(XlsMessageConstants.GROUP_CLOSED, globalGroupNum));
        }

    }

    private void validateGroupStatus(final String globalGroupNum, final Short customerStatusId)
            throws RowException {
        final CustomerStatus groupStatus = customerDao.findGroupBySystemId(globalGroupNum).getStatus();
        final CustomerStatus clientStatus = CustomerStatus.fromInt(customerStatusId);

        if (isGroupStatusLower(clientStatus, groupStatus)) {
            throw new RowException(getMessage(XlsMessageConstants.GROUP_BAD_STATUS));
        }
    }

    private boolean isGroupStatusLower(final CustomerStatus clientStatus, final CustomerStatus groupStatus) {
        if (clientStatus.equals(CustomerStatus.CLIENT_PENDING)) {
            if (groupStatus.equals(CustomerStatus.GROUP_PARTIAL)) {
                return true;
            }
        } else if (clientStatus.equals(CustomerStatus.CLIENT_ACTIVE)
                || clientStatus.equals(CustomerStatus.CLIENT_HOLD)) {
            if (groupStatus.equals(CustomerStatus.GROUP_PARTIAL)
                    || groupStatus.equals(CustomerStatus.GROUP_PENDING)) {
                return true;
            }
        }
        return false;
    }

    private Short getBranchId(final String branchShortName, final List<OfficeDto> allOfficess)
            throws CellException {

        if (StringUtils.isBlank(branchShortName)) {
            return null;
        }

        for (OfficeDto office : allOfficess) {
            if (office.getOfficeShortName().equals(branchShortName)) {
                return office.getId();
            }
        }
        throw new CellException(getMessage(XlsMessageConstants.OFFICE_NOT_FOUND_ERROR, branchShortName));
    }

    private void validateDuplicateCustomers(final ClientCreationDetail client,
            final List<ImportedClientDetail> importedClients) throws RowException {
        final Date dob = client.getDateOfBirth();
        final String clientName = client.getClientName();

        for (ImportedClientDetail importedClient : importedClients) {
            final Date otherDob = importedClient.getClientCreationDetail().getDateOfBirth();
            final String otherName = importedClient.getClientCreationDetail().getClientName();

            if (dob.equals(otherDob) && clientName.equals(otherName)) {
                throw new RowException(getMessage(XlsMessageConstants.DUPLICATE_CLIENT_ERROR));
            }
        }
    }

    private void validateAge(final Date dateOfBirth) throws CellException, ConfigurationException {
        final DateMidnight dob = new DateMidnight(dateOfBirth);
        final DateTime now = new DateTime();
        final Years age = Years.yearsBetween(dob, now);

        final int minimumAge = ClientRules.getMinimumAge();
        final int maximumAge = ClientRules.getMaximumAge();

        if (age.getYears() < 0) {
            throw new CellException(getMessage(XlsMessageConstants.FUTURE_DATE));
        } else if (ClientRules.isAgeCheckEnabled()
                && (age.getYears() < minimumAge || age.getYears() > maximumAge)) {
            throw new CellException(
                    getMessage(XlsMessageConstants.INVALID_AGE, new Object[] { minimumAge, maximumAge }));
        }
    }

    private Integer getRecurrenceType(final String meetingFrequency) throws CellException {
        RecurrenceType recurrenceType;

        if (meetingFrequency.equals(WEEK_FREQUENCY)) {
            recurrenceType = RecurrenceType.WEEKLY;
        } else if (meetingFrequency.equals(MONTH_FREQUENCY)) {
            recurrenceType = RecurrenceType.MONTHLY;
        } else if (StringUtils.isBlank(meetingFrequency)) {
            recurrenceType = null;
        } else {
            throw new CellException(getMessage(XlsMessageConstants.NOT_FOUND_ERROR, meetingFrequency));
        }
        return (recurrenceType == null) ? null : recurrenceType.getValue().intValue();
    }

    private Integer getDayValue(final String dayString) throws CellException {
        WeekDay day;

        if (dayString.equals(getMessage(XlsMessageConstants.WEEKDAYS_MONDAY))) {
            day = WeekDay.MONDAY;
        } else if (dayString.equals(getMessage(XlsMessageConstants.WEEKDAYS_TUESDAY))) {
            day = WeekDay.TUESDAY;
        } else if (dayString.equals(getMessage(XlsMessageConstants.WEEKDAYS_WEDNESDAY))) {
            day = WeekDay.WEDNESDAY;
        } else if (dayString.equals(getMessage(XlsMessageConstants.WEEKDAYS_THURSDAY))) {
            day = WeekDay.THURSDAY;
        } else if (dayString.equals(getMessage(XlsMessageConstants.WEEKDAYS_FRIDAY))) {
            day = WeekDay.FRIDAY;
        } else if (dayString.equals(getMessage(XlsMessageConstants.WEEKDAYS_SATURDAY))) {
            day = WeekDay.SATURDAY;
        } else if (StringUtils.isBlank(dayString)) {
            day = null;
        } else if (dayString.equals(getMessage(XlsMessageConstants.WEEKDAYS_SUNDAY))) {
            throw new CellException(getMessage(XlsMessageConstants.SUNDAY_MEETING));
        } else {
            throw new CellException(getMessage(XlsMessageConstants.INVALID_DAY, dayString));
        }

        return (day == null) ? null : day.getValue().intValue();
    }

    private Integer getDayRankValue(final String rankString) throws CellException {
        RankOfDay rankOfDay;

        if (StringUtils.isBlank(rankString)) {
            rankOfDay = null;
        } else if (getMessage(XlsMessageConstants.DAYRANK_FIRST).equals(rankString)) {
            rankOfDay = RankOfDay.FIRST;
        } else if (getMessage(XlsMessageConstants.DAYRANK_SECOND).equals(rankString)) {
            rankOfDay = RankOfDay.SECOND;
        } else if (getMessage(XlsMessageConstants.DAYRANK_THIRD).equals(rankString)) {
            rankOfDay = RankOfDay.THIRD;
        } else if (getMessage(XlsMessageConstants.DAYRANK_FOURTH).equals(rankString)) {
            rankOfDay = RankOfDay.FOURTH;
        } else if (getMessage(XlsMessageConstants.DAYRANK_LAST).equals(rankString)) {
            rankOfDay = RankOfDay.LAST;
        } else {
            throw new CellException(getMessage(XlsMessageConstants.NOT_FOUND_ERROR, rankString));
        }

        return (rankOfDay == null) ? null : rankOfDay.getValue().intValue();
    }

    private void validatePositive(final Number number) throws CellException {
        if (number != null && number.intValue() <= 0) {
            throw new CellException(getMessage(XlsMessageConstants.NOT_POSITIVE_NUMBER));
        }
    }

    private void validateMonthDay(final Integer day) throws CellException {
        if (day != null && (day <= 0 || day > 31)) {
            throw new CellException(getMessage(XlsMessageConstants.INVALID_MONTH));
        }
    }

}