@Override
public Result downloadAndParse() {
Result result = officeSheetPopulator.downloadAndParse();
if (result.isSuccess()) result = clientSheetPopulator.downloadAndParse();
if (result.isSuccess()) result = extrasSheetPopulator.downloadAndParse();
if (result.isSuccess()) {
try {
restClient.createAuthToken();
content = restClient.get("savingsaccounts?limit=-1");
Gson gson = new Gson();
JsonParser parser = new JsonParser();
JsonObject obj = parser.parse(content).getAsJsonObject();
JsonArray array = obj.getAsJsonArray("pageItems");
Iterator<JsonElement> iterator = array.iterator();
while (iterator.hasNext()) {
JsonElement json = iterator.next();
CompactSavingsAccount savingsAccount = gson.fromJson(json, CompactSavingsAccount.class);
if (savingsAccount.isActive()) savings.add(savingsAccount);
}
} catch (Exception e) {
result.addError(e.getMessage());
logger.error(e.getMessage());
}
}
return result;
}
private Result populateLoansTable(Sheet savingsTransactionSheet) {
Result result = new Result();
Workbook workbook = savingsTransactionSheet.getWorkbook();
CellStyle dateCellStyle = workbook.createCellStyle();
short df = workbook.createDataFormat().getFormat("dd/mm/yy");
dateCellStyle.setDataFormat(df);
int rowIndex = 1;
Row row;
Collections.sort(savings, CompactSavingsAccount.ClientNameComparator);
try {
for (CompactSavingsAccount savingsAccount : savings) {
row = savingsTransactionSheet.createRow(rowIndex++);
writeString(LOOKUP_CLIENT_NAME_COL, row, savingsAccount.getClientName());
writeLong(LOOKUP_ACCOUNT_NO_COL, row, Long.parseLong(savingsAccount.getAccountNo()));
writeString(LOOKUP_PRODUCT_COL, row, savingsAccount.getSavingsProductName());
writeDouble(LOOKUP_OPENING_BALANCE_COL, row, savingsAccount.getMinRequiredOpeningBalance());
writeDate(
LOOKUP_SAVINGS_ACTIVATION_DATE_COL,
row,
savingsAccount.getTimeline().getActivatedOnDate().get(2)
+ "/"
+ savingsAccount.getTimeline().getActivatedOnDate().get(1)
+ "/"
+ savingsAccount.getTimeline().getActivatedOnDate().get(0),
dateCellStyle);
}
} catch (Exception e) {
result.addError(e.getMessage());
logger.error(e.getMessage());
}
return result;
}
@Override
public Result populate(Workbook workbook) {
Sheet savingsTransactionSheet = workbook.createSheet("SavingsTransaction");
setLayout(savingsTransactionSheet);
Result result = officeSheetPopulator.populate(workbook);
if (result.isSuccess()) result = clientSheetPopulator.populate(workbook);
if (result.isSuccess()) result = extrasSheetPopulator.populate(workbook);
if (result.isSuccess()) result = populateLoansTable(savingsTransactionSheet);
if (result.isSuccess()) result = setRules(savingsTransactionSheet);
setDefaults(savingsTransactionSheet);
return result;
}
@Override
public Result parse() {
Result result = new Result();
Sheet savingsTransactionSheet = workbook.getSheet("RecurringDepositTransaction");
Integer noOfEntries = getNumberOfRows(savingsTransactionSheet, AMOUNT_COL);
for (int rowIndex = 1; rowIndex < noOfEntries; rowIndex++) {
Row row;
try {
row = savingsTransactionSheet.getRow(rowIndex);
if (isNotImported(row, STATUS_COL)) savingsTransactions.add(parseAsTransaction(row));
} catch (Exception e) {
logger.error("row = " + rowIndex, e);
result.addError("Row = " + rowIndex + " , " + e.getMessage());
}
}
return result;
}
@Override
public Result upload() {
Result result = new Result();
Sheet savingsTransactionSheet = workbook.getSheet("RecurringDepositTransaction");
for (Transaction transaction : savingsTransactions) {
try {
Gson gson = new Gson();
String payload = gson.toJson(transaction);
restClient.post(
"recurringdepositaccounts/"
+ transaction.getAccountId()
+ "/transactions?command="
+ transaction.getTransactionType(),
payload);
Cell statusCell =
savingsTransactionSheet.getRow(transaction.getRowIndex()).createCell(STATUS_COL);
statusCell.setCellValue("Imported");
statusCell.setCellStyle(getCellStyle(workbook, IndexedColors.LIGHT_GREEN));
} catch (Exception e) {
Cell savingsAccountIdCell =
savingsTransactionSheet
.getRow(transaction.getRowIndex())
.createCell(SAVINGS_ACCOUNT_NO_COL);
savingsAccountIdCell.setCellValue(transaction.getAccountId());
String message = parseStatus(e.getMessage());
Cell statusCell =
savingsTransactionSheet.getRow(transaction.getRowIndex()).createCell(STATUS_COL);
statusCell.setCellValue(message);
statusCell.setCellStyle(getCellStyle(workbook, IndexedColors.RED));
result.addError("Row = " + transaction.getRowIndex() + " ," + message);
}
}
savingsTransactionSheet.setColumnWidth(STATUS_COL, 15000);
writeString(STATUS_COL, savingsTransactionSheet.getRow(0), "Status");
return result;
}
private Result setRules(Sheet worksheet) {
Result result = new Result();
try {
CellRangeAddressList officeNameRange =
new CellRangeAddressList(
1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), OFFICE_NAME_COL, OFFICE_NAME_COL);
CellRangeAddressList clientNameRange =
new CellRangeAddressList(
1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), CLIENT_NAME_COL, CLIENT_NAME_COL);
CellRangeAddressList accountNumberRange =
new CellRangeAddressList(
1,
SpreadsheetVersion.EXCEL97.getLastRowIndex(),
SAVINGS_ACCOUNT_NO_COL,
SAVINGS_ACCOUNT_NO_COL);
CellRangeAddressList transactionTypeRange =
new CellRangeAddressList(
1,
SpreadsheetVersion.EXCEL97.getLastRowIndex(),
TRANSACTION_TYPE_COL,
TRANSACTION_TYPE_COL);
CellRangeAddressList paymentTypeRange =
new CellRangeAddressList(
1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), PAYMENT_TYPE_COL, PAYMENT_TYPE_COL);
CellRangeAddressList transactionDateRange =
new CellRangeAddressList(
1,
SpreadsheetVersion.EXCEL97.getLastRowIndex(),
TRANSACTION_DATE_COL,
TRANSACTION_DATE_COL);
DataValidationHelper validationHelper = new HSSFDataValidationHelper((HSSFSheet) worksheet);
setNames(worksheet);
DataValidationConstraint officeNameConstraint =
validationHelper.createFormulaListConstraint("Office");
DataValidationConstraint clientNameConstraint =
validationHelper.createFormulaListConstraint("INDIRECT(CONCATENATE(\"Client_\",$A1))");
DataValidationConstraint accountNumberConstraint =
validationHelper.createFormulaListConstraint(
"INDIRECT(CONCATENATE(\"Account_\",SUBSTITUTE($B1,\" \",\"_\")))");
DataValidationConstraint transactionTypeConstraint =
validationHelper.createExplicitListConstraint(new String[] {"Withdrawal", "Deposit"});
DataValidationConstraint paymentTypeConstraint =
validationHelper.createFormulaListConstraint("PaymentTypes");
DataValidationConstraint transactionDateConstraint =
validationHelper.createDateConstraint(
DataValidationConstraint.OperatorType.BETWEEN,
"=VLOOKUP($C1,$Q$2:$T$" + (savings.size() + 1) + ",4,FALSE)",
"=TODAY()",
"dd/mm/yy");
DataValidation officeValidation =
validationHelper.createValidation(officeNameConstraint, officeNameRange);
DataValidation clientValidation =
validationHelper.createValidation(clientNameConstraint, clientNameRange);
DataValidation accountNumberValidation =
validationHelper.createValidation(accountNumberConstraint, accountNumberRange);
DataValidation transactionTypeValidation =
validationHelper.createValidation(transactionTypeConstraint, transactionTypeRange);
DataValidation paymentTypeValidation =
validationHelper.createValidation(paymentTypeConstraint, paymentTypeRange);
DataValidation transactionDateValidation =
validationHelper.createValidation(transactionDateConstraint, transactionDateRange);
worksheet.addValidationData(officeValidation);
worksheet.addValidationData(clientValidation);
worksheet.addValidationData(accountNumberValidation);
worksheet.addValidationData(transactionTypeValidation);
worksheet.addValidationData(paymentTypeValidation);
worksheet.addValidationData(transactionDateValidation);
} catch (RuntimeException re) {
result.addError(re.getMessage());
}
return result;
}
