private Collection<PortfolioAccountData> retrieveToAccounts(
      final PortfolioAccountData excludeThisAccountFromOptions,
      final Integer toAccountType,
      final Long toClientId) {

    final String currencyCode =
        excludeThisAccountFromOptions != null ? excludeThisAccountFromOptions.currencyCode() : null;

    Collection<PortfolioAccountData> accountOptions =
        this.portfolioAccountReadPlatformService.retrieveAllForLookup(
            toAccountType, toClientId, currencyCode, null);
    if (!CollectionUtils.isEmpty(accountOptions)) {
      accountOptions.remove(excludeThisAccountFromOptions);
    } else {
      accountOptions = null;
    }

    return accountOptions;
  }
  @Override
  public AccountTransferData retrieveTemplate(
      final Long fromOfficeId,
      final Long fromClientId,
      final Long fromAccountId,
      final Integer fromAccountType,
      final Long toOfficeId,
      final Long toClientId,
      final Long toAccountId,
      final Integer toAccountType) {

    final EnumOptionData loanAccountType =
        AccountTransferEnumerations.accountType(PortfolioAccountType.LOAN);
    final EnumOptionData savingsAccountType =
        AccountTransferEnumerations.accountType(PortfolioAccountType.SAVINGS);

    Integer mostRelevantFromAccountType = fromAccountType;
    final Collection<EnumOptionData> fromAccountTypeOptions =
        Arrays.asList(savingsAccountType, loanAccountType);
    final Collection<EnumOptionData> toAccountTypeOptions;
    if (mostRelevantFromAccountType == 1) {
      // overpaid loan amt transfer to savings account
      toAccountTypeOptions = Arrays.asList(savingsAccountType);
    } else {
      toAccountTypeOptions = Arrays.asList(loanAccountType, savingsAccountType);
    }
    final Integer mostRelevantToAccountType = toAccountType;

    final EnumOptionData fromAccountTypeData =
        AccountTransferEnumerations.accountType(mostRelevantFromAccountType);
    final EnumOptionData toAccountTypeData =
        AccountTransferEnumerations.accountType(mostRelevantToAccountType);

    // from settings
    OfficeData fromOffice = null;
    ClientData fromClient = null;
    PortfolioAccountData fromAccount = null;

    OfficeData toOffice = null;
    ClientData toClient = null;
    PortfolioAccountData toAccount = null;

    // template
    Collection<PortfolioAccountData> fromAccountOptions = null;
    Collection<PortfolioAccountData> toAccountOptions = null;

    Long mostRelevantFromOfficeId = fromOfficeId;
    Long mostRelevantFromClientId = fromClientId;

    Long mostRelevantToOfficeId = toOfficeId;
    Long mostRelevantToClientId = toClientId;

    if (fromAccountId != null) {
      Integer accountType;
      if (mostRelevantFromAccountType == 1) {
        accountType = PortfolioAccountType.LOAN.getValue();
      } else {
        accountType = PortfolioAccountType.SAVINGS.getValue();
      }
      fromAccount =
          this.portfolioAccountReadPlatformService.retrieveOne(fromAccountId, accountType);

      // override provided fromClient with client of account
      mostRelevantFromClientId = fromAccount.clientId();
    }

    if (mostRelevantFromClientId != null) {
      fromClient = this.clientReadPlatformService.retrieveOne(mostRelevantFromClientId);
      mostRelevantFromOfficeId = fromClient.officeId();
      long[] loanStatus = null;
      if (mostRelevantFromAccountType == 1) {
        loanStatus = new long[] {300, 700};
      }
      fromAccountOptions =
          this.portfolioAccountReadPlatformService.retrieveAllForLookup(
              mostRelevantFromAccountType, mostRelevantFromClientId, loanStatus);
    }

    Collection<OfficeData> fromOfficeOptions = null;
    Collection<ClientData> fromClientOptions = null;
    if (mostRelevantFromOfficeId != null) {
      fromOffice = this.officeReadPlatformService.retrieveOffice(mostRelevantFromOfficeId);
      fromOfficeOptions = this.officeReadPlatformService.retrieveAllOfficesForDropdown();
      fromClientOptions =
          this.clientReadPlatformService.retrieveAllForLookupByOfficeId(mostRelevantFromOfficeId);
    }

    // defaults
    final LocalDate transferDate = DateUtils.getLocalDateOfTenant();
    Collection<OfficeData> toOfficeOptions = fromOfficeOptions;
    Collection<ClientData> toClientOptions = null;

    if (toAccountId != null && fromAccount != null) {
      toAccount =
          this.portfolioAccountReadPlatformService.retrieveOne(
              toAccountId, mostRelevantToAccountType, fromAccount.currencyCode());
      mostRelevantToClientId = toAccount.clientId();
    }

    if (mostRelevantToClientId != null) {
      toClient = this.clientReadPlatformService.retrieveOne(mostRelevantToClientId);
      mostRelevantToOfficeId = toClient.officeId();

      toClientOptions =
          this.clientReadPlatformService.retrieveAllForLookupByOfficeId(mostRelevantToOfficeId);

      toAccountOptions =
          retrieveToAccounts(fromAccount, mostRelevantToAccountType, mostRelevantToClientId);
    }

    if (mostRelevantToOfficeId != null) {
      toOffice = this.officeReadPlatformService.retrieveOffice(mostRelevantToOfficeId);
      toOfficeOptions = this.officeReadPlatformService.retrieveAllOfficesForDropdown();

      toClientOptions =
          this.clientReadPlatformService.retrieveAllForLookupByOfficeId(mostRelevantToOfficeId);
      if (toClientOptions != null && toClientOptions.size() == 1) {
        toClient = new ArrayList<ClientData>(toClientOptions).get(0);

        toAccountOptions =
            retrieveToAccounts(fromAccount, mostRelevantToAccountType, mostRelevantToClientId);
      }
    }

    return AccountTransferData.template(
        fromOffice,
        fromClient,
        fromAccountTypeData,
        fromAccount,
        transferDate,
        toOffice,
        toClient,
        toAccountTypeData,
        toAccount,
        fromOfficeOptions,
        fromClientOptions,
        fromAccountTypeOptions,
        fromAccountOptions,
        toOfficeOptions,
        toClientOptions,
        toAccountTypeOptions,
        toAccountOptions);
  }
    @Override
    public AccountTransferData mapRow(
        final ResultSet rs, @SuppressWarnings("unused") final int rowNum) throws SQLException {

      final Long id = rs.getLong("id");
      final boolean reversed = rs.getBoolean("isReversed");

      final LocalDate transferDate = JdbcSupport.getLocalDate(rs, "transferDate");
      final BigDecimal transferAmount =
          JdbcSupport.getBigDecimalDefaultToZeroIfNull(rs, "transferAmount");
      final String transferDescription = rs.getString("transferDescription");

      final String currencyCode = rs.getString("currencyCode");
      final String currencyName = rs.getString("currencyName");
      final String currencyNameCode = rs.getString("currencyNameCode");
      final String currencyDisplaySymbol = rs.getString("currencyDisplaySymbol");
      final Integer currencyDigits = JdbcSupport.getInteger(rs, "currencyDigits");
      final Integer inMultiplesOf = JdbcSupport.getInteger(rs, "inMultiplesOf");
      final CurrencyData currency =
          new CurrencyData(
              currencyCode,
              currencyName,
              currencyDigits,
              inMultiplesOf,
              currencyDisplaySymbol,
              currencyNameCode);

      final Long fromOfficeId = JdbcSupport.getLong(rs, "fromOfficeId");
      final String fromOfficeName = rs.getString("fromOfficeName");
      final OfficeData fromOffice = OfficeData.dropdown(fromOfficeId, fromOfficeName, null);

      final Long toOfficeId = JdbcSupport.getLong(rs, "toOfficeId");
      final String toOfficeName = rs.getString("toOfficeName");
      final OfficeData toOffice = OfficeData.dropdown(toOfficeId, toOfficeName, null);

      final Long fromClientId = JdbcSupport.getLong(rs, "fromClientId");
      final String fromClientName = rs.getString("fromClientName");
      final ClientData fromClient =
          ClientData.lookup(fromClientId, fromClientName, fromOfficeId, fromOfficeName);

      final Long toClientId = JdbcSupport.getLong(rs, "toClientId");
      final String toClientName = rs.getString("toClientName");
      final ClientData toClient =
          ClientData.lookup(toClientId, toClientName, toOfficeId, toOfficeName);

      final Long fromSavingsAccountId = JdbcSupport.getLong(rs, "fromSavingsAccountId");
      final String fromSavingsAccountNo = rs.getString("fromSavingsAccountNo");
      final Long fromLoanAccountId = JdbcSupport.getLong(rs, "fromLoanAccountId");
      final String fromLoanAccountNo = rs.getString("fromLoanAccountNo");
      PortfolioAccountData fromAccount = null;
      EnumOptionData fromAccountType = null;
      if (fromSavingsAccountId != null) {
        fromAccount = PortfolioAccountData.lookup(fromSavingsAccountId, fromSavingsAccountNo);
        fromAccountType = AccountTransferEnumerations.accountType(PortfolioAccountType.SAVINGS);
      } else if (fromLoanAccountId != null) {
        fromAccount = PortfolioAccountData.lookup(fromLoanAccountId, fromLoanAccountNo);
        fromAccountType = AccountTransferEnumerations.accountType(PortfolioAccountType.LOAN);
      }

      PortfolioAccountData toAccount = null;
      EnumOptionData toAccountType = null;
      final Long toSavingsAccountId = JdbcSupport.getLong(rs, "toSavingsAccountId");
      final String toSavingsAccountNo = rs.getString("toSavingsAccountNo");
      final Long toLoanAccountId = JdbcSupport.getLong(rs, "toLoanAccountId");
      final String toLoanAccountNo = rs.getString("toLoanAccountNo");

      if (toSavingsAccountId != null) {
        toAccount = PortfolioAccountData.lookup(toSavingsAccountId, toSavingsAccountNo);
        toAccountType = AccountTransferEnumerations.accountType(PortfolioAccountType.SAVINGS);
      } else if (toLoanAccountId != null) {
        toAccount = PortfolioAccountData.lookup(toLoanAccountId, toLoanAccountNo);
        toAccountType = AccountTransferEnumerations.accountType(PortfolioAccountType.LOAN);
      }

      return AccountTransferData.instance(
          id,
          reversed,
          transferDate,
          currency,
          transferAmount,
          transferDescription,
          fromOffice,
          toOffice,
          fromClient,
          toClient,
          fromAccountType,
          fromAccount,
          toAccountType,
          toAccount);
    }