@Override
  public Collection<MifosEntityAccessData> retrieveEntityAccessFor(
      Long firstEntityId,
      MifosEntityType firstEntityType,
      MifosEntityAccessType accessType,
      MifosEntityType secondEntityType,
      boolean includeAllSubOffices) {
    final AppUser currentUser = this.context.authenticatedUser();

    final String hierarchy = currentUser.getOffice().getHierarchy();
    String hierarchySearchString = null;
    if (includeAllSubOffices) {
      hierarchySearchString = "." + "%";
    } else {
      hierarchySearchString = hierarchy + "%";
    }
    String sql = getSQLForRetriveEntityAccessFor(firstEntityType, accessType, secondEntityType);

    Collection<MifosEntityAccessData> entityAccessData = null;
    MifosEntityAccessDataMapper mapper = new MifosEntityAccessDataMapper();

    if (includeAllSubOffices && (firstEntityType.getTable().equals("m_office"))) {
      sql += " where firstentity.hierarchy like ? order by firstEntity.hierarchy";
      entityAccessData =
          this.jdbcTemplate.query(sql, mapper, new Object[] {firstEntityId, hierarchySearchString});
    } else {
      entityAccessData = this.jdbcTemplate.query(sql, mapper, new Object[] {firstEntityId});
    }

    return entityAccessData;
  }
  private String dataScopedSQL(final String unscopedSQL, final String appTable) {
    String dataScopeCriteria = null;
    /*
     * unfortunately have to, one way or another, be able to restrict data
     * to the users office hierarchy. Here it's hardcoded for client and
     * loan. They are the main application tables. But if additional fields
     * are needed on other tables like group, loan_transaction or others the
     * same applies (hardcoding of some sort)
     */

    AppUser currentUser = context.authenticatedUser();
    if (appTable.equalsIgnoreCase("m_client")) {
      dataScopeCriteria =
          " join m_office o on o.id = t.office_id and o.hierarchy like '"
              + currentUser.getOffice().getHierarchy()
              + "%'";
    }
    if (appTable.equalsIgnoreCase("m_loan")) {
      dataScopeCriteria =
          " join m_client c on c.id = t.client_id "
              + " join m_office o on o.id = c.office_id and o.hierarchy like '"
              + currentUser.getOffice().getHierarchy()
              + "%'";
    }
    if (appTable.equalsIgnoreCase("m_group")) {
      dataScopeCriteria =
          " join m_office o on o.id = t.office_id and o.hierarchy like '"
              + currentUser.getOffice().getHierarchy()
              + "%'";
    }
    if (appTable.equalsIgnoreCase("m_office")) {
      dataScopeCriteria =
          " join m_office o on o.id = t.id and o.hierarchy like '"
              + currentUser.getOffice().getHierarchy()
              + "%'";
    }

    if (dataScopeCriteria == null) {
      throw new PlatformDataIntegrityException(
          "error.msg.invalid.dataScopeCriteria",
          "Application Table: " + appTable + " not catered for in data Scoping");
    }

    return genericDataService.replace(unscopedSQL, "${dataScopeCriteria}", dataScopeCriteria);
  }
  @Override
  public JLGCollectionSheetData generateCenterCollectionSheet(
      final Long centerId, final JsonQuery query) {

    this.collectionSheetGenerateCommandFromApiJsonDeserializer.validateForGenerateCollectionSheet(
        query.json());

    final AppUser currentUser = this.context.authenticatedUser();
    final String hierarchy = currentUser.getOffice().getHierarchy();
    final String officeHierarchy = hierarchy + "%";

    final CenterData center = this.centerReadPlatformService.retrieveOne(centerId);

    final LocalDate transactionDate =
        query.localDateValueOfParameterNamed(transactionDateParamName);
    final DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
    final String dueDateStr = df.format(transactionDate.toDate());

    final JLGCollectionSheetFaltDataMapper mapper = new JLGCollectionSheetFaltDataMapper();

    StringBuilder sql = new StringBuilder(mapper.collectionSheetSchema(true));

    final SqlParameterSource namedParameters =
        new MapSqlParameterSource()
            .addValue("dueDate", dueDateStr)
            .addValue("centerId", center.getId())
            .addValue("officeHierarchy", officeHierarchy)
            .addValue("entityTypeId", CalendarEntityType.CENTERS.getValue());

    final Collection<JLGCollectionSheetFlatData> collectionSheetFlatDatas =
        this.namedParameterjdbcTemplate.query(sql.toString(), namedParameters, mapper);

    // loan data for collection sheet
    JLGCollectionSheetData collectionSheetData =
        buildJLGCollectionSheet(transactionDate, collectionSheetFlatDatas);

    // mandatory savings data for collection sheet
    Collection<JLGGroupData> groupsWithSavingsData =
        this.namedParameterjdbcTemplate.query(
            mandatorySavingsExtractor.collectionSheetSchema(true),
            namedParameters,
            mandatorySavingsExtractor);

    // merge savings data into loan data
    mergeSavingsGroupDataIntoCollectionsheetData(groupsWithSavingsData, collectionSheetData);

    collectionSheetData =
        JLGCollectionSheetData.withSavingsProducts(
            collectionSheetData, retrieveSavingsProducts(groupsWithSavingsData));

    return collectionSheetData;
  }
  @Transactional
  @Override
  @CacheEvict(value = "usersByUsername", allEntries = true)
  public CommandProcessingResult updateUser(final Long userId, final JsonCommand command) {

    try {
      this.context.authenticatedUser();

      this.fromApiJsonDeserializer.validateForUpdate(command.json());

      final AppUser userToUpdate = this.appUserRepository.findOne(userId);
      if (userToUpdate == null) {
        throw new UserNotFoundException(userId);
      }

      final Map<String, Object> changes =
          userToUpdate.update(command, this.platformPasswordEncoder);

      if (changes.containsKey("officeId")) {
        final Long officeId = (Long) changes.get("officeId");
        final Office office = this.officeRepository.findOne(officeId);
        if (office == null) {
          throw new OfficeNotFoundException(officeId);
        }

        userToUpdate.changeOffice(office);
      }

      if (changes.containsKey("roles")) {
        final String[] roleIds = (String[]) changes.get("roles");
        final Set<Role> allRoles = assembleSetOfRoles(roleIds);

        userToUpdate.updateRoles(allRoles);
      }

      if (!changes.isEmpty()) {
        this.appUserRepository.saveAndFlush(userToUpdate);
      }

      return new CommandProcessingResultBuilder() //
          .withEntityId(userId) //
          .withOfficeId(userToUpdate.getOffice().getId()) //
          .with(changes) //
          .build();
    } catch (final DataIntegrityViolationException dve) {
      handleDataIntegrityIssues(command, dve);
      return CommandProcessingResult.empty();
    }
  }
  @Override
  public GroupGeneralData retrieveOne(final Long groupId) {

    try {
      final AppUser currentUser = this.context.authenticatedUser();
      final String hierarchy = currentUser.getOffice().getHierarchy();
      final String hierarchySearchString = hierarchy + "%";

      final String sql =
          "select "
              + this.allGroupTypesDataMapper.schema()
              + " where g.id = ? and o.hierarchy like ?";
      return this.jdbcTemplate.queryForObject(
          sql, this.allGroupTypesDataMapper, new Object[] {groupId, hierarchySearchString});
    } catch (final EmptyResultDataAccessException e) {
      throw new GroupNotFoundException(groupId);
    }
  }
  @Transactional
  @Override
  @CacheEvict(value = "usersByUsername", allEntries = true)
  public CommandProcessingResult deleteUser(final Long userId) {

    final AppUser user = this.appUserRepository.findOne(userId);
    if (user == null || user.isDeleted()) {
      throw new UserNotFoundException(userId);
    }

    user.delete();
    this.appUserRepository.save(user);

    return new CommandProcessingResultBuilder()
        .withEntityId(userId)
        .withOfficeId(user.getOffice().getId())
        .build();
  }
  @Override
  public Page<GroupGeneralData> retrieveAll(final SearchParameters searchParameters) {

    final AppUser currentUser = this.context.authenticatedUser();
    final String hierarchy = currentUser.getOffice().getHierarchy();
    final String hierarchySearchString = hierarchy + "%";

    final StringBuilder sqlBuilder = new StringBuilder(200);
    sqlBuilder.append("select SQL_CALC_FOUND_ROWS ");
    sqlBuilder.append(this.allGroupTypesDataMapper.schema());
    sqlBuilder.append(" where o.hierarchy like ?");

    final String extraCriteria = getGroupExtraCriteria(searchParameters);

    if (StringUtils.isNotBlank(extraCriteria)) {
      sqlBuilder.append(" and (").append(extraCriteria).append(")");
    }

    if (searchParameters.isOrderByRequested()) {
      sqlBuilder
          .append(" order by ")
          .append(searchParameters.getOrderBy())
          .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()";
    return this.paginationHelper.fetchPage(
        this.jdbcTemplate,
        sqlCountRows,
        sqlBuilder.toString(),
        new Object[] {hierarchySearchString},
        this.allGroupTypesDataMapper);
  }
  /*
   * used to restrict modifying operations to office that are either the users
   * office or lower (child) in the office hierarchy
   */
  private Office validateUserPriviledgeOnOfficeAndRetrieve(
      final AppUser currentUser, final Long officeId) {

    final Long userOfficeId = currentUser.getOffice().getId();
    final Office userOffice = this.officeRepository.findOne(userOfficeId);
    if (userOffice == null) {
      throw new OfficeNotFoundException(userOfficeId);
    }

    if (userOffice.doesNotHaveAnOfficeInHierarchyWithId(officeId)) {
      throw new NoAuthorizationException(
          "User does not have sufficient priviledges to act on the provided office.");
    }

    Office officeToReturn = userOffice;
    if (!userOffice.identifiedBy(officeId)) {
      officeToReturn = this.officeRepository.findOne(officeId);
      if (officeToReturn == null) {
        throw new OfficeNotFoundException(officeId);
      }
    }

    return officeToReturn;
  }
  @Override
  public CommandProcessingResult convertToClient(final Long entityId) {

    final AppUser currentUser = context.authenticatedUser();
    final ClientProspect clientProspect = retrieveCodeBy(entityId);

    Long clientId = null;

    final JSONObject newClientJsonObject = new JSONObject();

    try {
      SimpleDateFormat formatter = new SimpleDateFormat("dd MMMM yyyy");
      String activationDate = formatter.format(DateUtils.getDateOfTenant());

      final Long officeId = currentUser.getOffice().getId();
      newClientJsonObject.put("dateFormat", "dd MMMM yyyy");
      newClientJsonObject.put("locale", "en");
      newClientJsonObject.put("officeId", officeId);
      newClientJsonObject.put("firstname", clientProspect.getFirstName());
      newClientJsonObject.put("middlename", clientProspect.getMiddleName());
      newClientJsonObject.put("lastname", clientProspect.getLastName());
      newClientJsonObject.put("fullname", "");
      newClientJsonObject.put("externalId", "");
      newClientJsonObject.put("clientCategory", "20");
      // newClientJsonObject.put("active","300");
      newClientJsonObject.put("activationDate", activationDate);
      newClientJsonObject.put("active", "true");
      newClientJsonObject.put("email", clientProspect.getEmail());
      newClientJsonObject.put("phone", clientProspect.getMobileNumber());
      newClientJsonObject.put("flag", false);
      /*
       * newClientJsonObject.put("login","");
       * newClientJsonObject.put("password","");
       */

      newClientJsonObject.put("addressNo", clientProspect.getAddress());
      newClientJsonObject.put("street", clientProspect.getStreetArea());
      newClientJsonObject.put("city", clientProspect.getCityDistrict());
      newClientJsonObject.put("zipCode", clientProspect.getZipCode());
      newClientJsonObject.put("state", clientProspect.getState());
      newClientJsonObject.put("country", clientProspect.getCountry());
      newClientJsonObject.put("flag", "false");

      final CommandWrapper commandNewClient =
          new CommandWrapperBuilder()
              .createClient()
              .withJson(newClientJsonObject.toString().toString())
              .build(); //

      final CommandProcessingResult clientResult =
          this.commandsSourceWritePlatformService.logCommandSource(commandNewClient);
      /*
       * final CommandWrapper commandRequest = new
       * CommandWrapperBuilder().
       * createAddress(clientResult.getClientId()).
       * withJson(newClientAddressObject.toString().toString()).build();
       * final CommandProcessingResult addressResult =
       * this.commandsSourceWritePlatformService
       * .logCommandSource(commandRequest);
       */

      clientProspect.setStatusRemark(clientResult.getClientId().toString());
      clientId = clientResult.getClientId();

    } catch (JSONException e) {
      e.printStackTrace();
    }

    clientProspect.setStatus("Closed");
    // clientProspect.setIsDeleted('Y');

    // clientProspect.setStatusRemark(command.stringValueOfParameterNamed("statusRemark"));

    this.clientProspectJpaRepository.saveAndFlush(clientProspect);

    return new CommandProcessingResultBuilder().withEntityId(clientId).build();
  }
  @Override
  public JLGCollectionSheetData generateGroupCollectionSheet(
      final Long groupId, final JsonQuery query) {

    this.collectionSheetGenerateCommandFromApiJsonDeserializer.validateForGenerateCollectionSheet(
        query.json());

    final Long calendarId = query.longValueOfParameterNamed(calendarIdParamName);
    final LocalDate transactionDate =
        query.localDateValueOfParameterNamed(transactionDateParamName);
    final DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
    final String transactionDateStr = df.format(transactionDate.toDate());

    final Calendar calendar =
        this.calendarRepositoryWrapper.findOneWithNotFoundDetection(calendarId);
    // check if transaction against calendar effective from date

    if (!calendar.isValidRecurringDate(transactionDate)) {
      throw new NotValidRecurringDateException(
          "collectionsheet",
          "The date '" + transactionDate + "' is not a valid meeting date.",
          transactionDate);
    }

    final AppUser currentUser = this.context.authenticatedUser();
    final String hierarchy = currentUser.getOffice().getHierarchy();
    final String officeHierarchy = hierarchy + "%";

    final GroupGeneralData group = this.groupReadPlatformService.retrieveOne(groupId);

    final JLGCollectionSheetFaltDataMapper mapper = new JLGCollectionSheetFaltDataMapper();

    // entityType should be center if it's within a center
    final CalendarEntityType entityType =
        (group.isChildGroup()) ? CalendarEntityType.CENTERS : CalendarEntityType.GROUPS;

    final SqlParameterSource namedParameters =
        new MapSqlParameterSource()
            .addValue("dueDate", transactionDateStr)
            .addValue("groupId", group.getId())
            .addValue("officeHierarchy", officeHierarchy)
            .addValue("entityTypeId", entityType.getValue());

    final Collection<JLGCollectionSheetFlatData> collectionSheetFlatDatas =
        this.namedParameterjdbcTemplate.query(
            mapper.collectionSheetSchema(false), namedParameters, mapper);

    // loan data for collection sheet
    JLGCollectionSheetData collectionSheetData =
        buildJLGCollectionSheet(transactionDate, collectionSheetFlatDatas);

    // mandatory savings data for collection sheet
    Collection<JLGGroupData> groupsWithSavingsData =
        this.namedParameterjdbcTemplate.query(
            mandatorySavingsExtractor.collectionSheetSchema(false),
            namedParameters,
            mandatorySavingsExtractor);

    // merge savings data into loan data
    mergeSavingsGroupDataIntoCollectionsheetData(groupsWithSavingsData, collectionSheetData);

    collectionSheetData =
        JLGCollectionSheetData.withSavingsProducts(
            collectionSheetData, retrieveSavingsProducts(groupsWithSavingsData));

    return collectionSheetData;
  }