in fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/loan/LoanWorkbookPopulator.java [90:303]
private void setRules(Sheet worksheet, String dateFormat) {
CellRangeAddressList officeNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.OFFICE_NAME_COL, LoanConstants.OFFICE_NAME_COL);
CellRangeAddressList loanTypeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.LOAN_TYPE_COL, LoanConstants.LOAN_TYPE_COL);
CellRangeAddressList clientNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.CLIENT_NAME_COL, LoanConstants.CLIENT_NAME_COL);
CellRangeAddressList productNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.PRODUCT_COL, LoanConstants.PRODUCT_COL);
CellRangeAddressList loanOfficerRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.LOAN_OFFICER_NAME_COL, LoanConstants.LOAN_OFFICER_NAME_COL);
CellRangeAddressList submittedDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.SUBMITTED_ON_DATE_COL, LoanConstants.SUBMITTED_ON_DATE_COL);
CellRangeAddressList fundNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.FUND_NAME_COL, LoanConstants.FUND_NAME_COL);
CellRangeAddressList principalRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.PRINCIPAL_COL, LoanConstants.PRINCIPAL_COL);
CellRangeAddressList noOfRepaymentsRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.NO_OF_REPAYMENTS_COL, LoanConstants.NO_OF_REPAYMENTS_COL);
CellRangeAddressList repaidFrequencyRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.REPAID_EVERY_FREQUENCY_COL, LoanConstants.REPAID_EVERY_FREQUENCY_COL);
CellRangeAddressList loanTermRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.LOAN_TERM_COL, LoanConstants.LOAN_TERM_COL);
CellRangeAddressList loanTermFrequencyRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.LOAN_TERM_FREQUENCY_COL, LoanConstants.LOAN_TERM_FREQUENCY_COL);
CellRangeAddressList interestFrequencyRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.NOMINAL_INTEREST_RATE_FREQUENCY_COL, LoanConstants.NOMINAL_INTEREST_RATE_FREQUENCY_COL);
CellRangeAddressList interestRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.NOMINAL_INTEREST_RATE_COL, LoanConstants.NOMINAL_INTEREST_RATE_COL);
CellRangeAddressList amortizationRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.AMORTIZATION_COL, LoanConstants.AMORTIZATION_COL);
CellRangeAddressList interestMethodRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.INTEREST_METHOD_COL, LoanConstants.INTEREST_METHOD_COL);
CellRangeAddressList intrestCalculationPeriodRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.INTEREST_CALCULATION_PERIOD_COL, LoanConstants.INTEREST_CALCULATION_PERIOD_COL);
CellRangeAddressList repaymentStrategyRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.REPAYMENT_STRATEGY_COL, LoanConstants.REPAYMENT_STRATEGY_COL);
CellRangeAddressList arrearsToleranceRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.ARREARS_TOLERANCE_COL, LoanConstants.ARREARS_TOLERANCE_COL);
CellRangeAddressList graceOnPrincipalPaymentRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.GRACE_ON_PRINCIPAL_PAYMENT_COL, LoanConstants.GRACE_ON_PRINCIPAL_PAYMENT_COL);
CellRangeAddressList graceOnInterestPaymentRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.GRACE_ON_INTEREST_PAYMENT_COL, LoanConstants.GRACE_ON_INTEREST_PAYMENT_COL);
CellRangeAddressList graceOnInterestChargedRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.GRACE_ON_INTEREST_CHARGED_COL, LoanConstants.GRACE_ON_INTEREST_CHARGED_COL);
CellRangeAddressList approvedDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.APPROVED_DATE_COL, LoanConstants.APPROVED_DATE_COL);
CellRangeAddressList disbursedDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.DISBURSED_DATE_COL, LoanConstants.DISBURSED_DATE_COL);
CellRangeAddressList paymentTypeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.DISBURSED_PAYMENT_TYPE_COL, LoanConstants.DISBURSED_PAYMENT_TYPE_COL);
CellRangeAddressList repaymentTypeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.REPAYMENT_TYPE_COL, LoanConstants.REPAYMENT_TYPE_COL);
CellRangeAddressList lastrepaymentDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.LAST_REPAYMENT_DATE_COL, LoanConstants.LAST_REPAYMENT_DATE_COL);
DataValidationHelper validationHelper = new HSSFDataValidationHelper((HSSFSheet) worksheet);
CellRangeAddressList chargeOneNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.CHARGE_NAME_1, LoanConstants.CHARGE_NAME_1);
CellRangeAddressList chargeOneAmountTypeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.CHARGE_AMOUNT_TYPE_1, LoanConstants.CHARGE_AMOUNT_TYPE_1);
CellRangeAddressList chargeTwoNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.CHARGE_NAME_2, LoanConstants.CHARGE_NAME_2);
CellRangeAddressList chargeTwoAmountTypeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),
LoanConstants.CHARGE_AMOUNT_TYPE_2, LoanConstants.CHARGE_AMOUNT_TYPE_2);
setNames(worksheet);
DataValidationConstraint officeNameConstraint = validationHelper.createFormulaListConstraint("Office");
DataValidationConstraint loanTypeConstraint = validationHelper.createExplicitListConstraint(
new String[] { LoanConstants.LOAN_TYPE_INDIVIDUAL, LoanConstants.LOAN_TYPE_GROUP, LoanConstants.LOAN_TYPE_JLG });
DataValidationConstraint clientNameConstraint = validationHelper.createFormulaListConstraint(
"IF($B1=\"Group\",INDIRECT(CONCATENATE(\"Group_\",$A1)),INDIRECT(CONCATENATE(\"Client_\",$A1)))");
DataValidationConstraint productNameConstraint = validationHelper.createFormulaListConstraint("Products");
DataValidationConstraint loanOfficerNameConstraint = validationHelper
.createFormulaListConstraint("INDIRECT(CONCATENATE(\"Staff_\",$A1))");
DataValidationConstraint submittedDateConstraint = validationHelper.createDateConstraint(
DataValidationConstraint.OperatorType.BETWEEN,
"=IF(" + "DATEVALUE(INDIRECT(\"START_DATE_\" & $E1)) > DATEVALUE(VLOOKUP($C1, $AR$2:$AT$"
+ (clientSheetPopulator.getClientsSize() + groupSheetPopulator.getGroupsSize() + 1) + ", 3, FALSE)), "
+ "DATEVALUE(INDIRECT(\"START_DATE_\" & $E1)), " + "DATEVALUE(VLOOKUP($C1, $AR$2:$AT$"
+ (clientSheetPopulator.getClientsSize() + groupSheetPopulator.getGroupsSize() + 1) + ", 3, FALSE)))",
"=TODAY()", dateFormat);
DataValidationConstraint approvalDateConstraint = validationHelper
.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, "=$G1", "=TODAY()", dateFormat);
DataValidationConstraint disbursedDateConstraint = validationHelper
.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, "=$H1", "=TODAY()", dateFormat);
DataValidationConstraint paymentTypeConstraint = validationHelper.createFormulaListConstraint("PaymentTypes");
DataValidationConstraint fundNameConstraint = validationHelper.createFormulaListConstraint("Funds");
DataValidationConstraint principalConstraint = validationHelper.createDecimalConstraint(
DataValidationConstraint.OperatorType.BETWEEN, "=INDIRECT(CONCATENATE(\"MIN_PRINCIPAL_\",$E1))",
"=INDIRECT(CONCATENATE(\"MAX_PRINCIPAL_\",$E1))");
DataValidationConstraint noOfRepaymentsConstraint = validationHelper.createIntegerConstraint(
DataValidationConstraint.OperatorType.BETWEEN, "=INDIRECT(CONCATENATE(\"MIN_REPAYMENT_\",$E1))",
"=INDIRECT(CONCATENATE(\"MAX_REPAYMENT_\",$E1))");
DataValidationConstraint frequencyConstraint = validationHelper
.createExplicitListConstraint(new String[] { "Days", "Weeks", "Months", "Semi Month" });
DataValidationConstraint loanTermFrequencyConstraint = validationHelper
.createExplicitListConstraint(new String[] { "Days", "Weeks", "Months" });
DataValidationConstraint loanTermConstraint = validationHelper
.createIntegerConstraint(DataValidationConstraint.OperatorType.GREATER_OR_EQUAL, "=$M1/$N1", "=$M1*$N1");
DataValidationConstraint interestFrequencyConstraint = validationHelper
.createFormulaListConstraint("INDIRECT(CONCATENATE(\"INTEREST_FREQUENCY_\",$E1))");
DataValidationConstraint interestConstraint = validationHelper.createDecimalConstraint(
DataValidationConstraint.OperatorType.BETWEEN, "=INDIRECT(CONCATENATE(\"MIN_INTEREST_\",$E1))",
"=INDIRECT(CONCATENATE(\"MAX_INTEREST_\",$E1))");
DataValidationConstraint amortizationConstraint = validationHelper
.createExplicitListConstraint(new String[] { "Equal principal payments", "Equal installments" });
DataValidationConstraint interestMethodConstraint = validationHelper
.createExplicitListConstraint(new String[] { "Flat", "Declining Balance" });
DataValidationConstraint interestCalculationPeriodConstraint = validationHelper
.createExplicitListConstraint(new String[] { "Daily", "Same as repayment period" });
DataValidationConstraint repaymentStrategyConstraint = validationHelper.createExplicitListConstraint(new String[] {
"Penalties, Fees, Interest, Principal order", "HeavensFamily Unique", "Creocore Unique", "Overdue/Due Fee/Int,Principal",
"Principal, Interest, Penalties, Fees Order", "Interest, Principal, Penalties, Fees Order", "Early Repayment Strategy" });
DataValidationConstraint arrearsToleranceConstraint = validationHelper
.createIntegerConstraint(DataValidationConstraint.OperatorType.GREATER_OR_EQUAL, "0", null);
DataValidationConstraint graceOnPrincipalPaymentConstraint = validationHelper
.createIntegerConstraint(DataValidationConstraint.OperatorType.GREATER_OR_EQUAL, "0", null);
DataValidationConstraint graceOnInterestPaymentConstraint = validationHelper
.createIntegerConstraint(DataValidationConstraint.OperatorType.GREATER_OR_EQUAL, "0", null);
DataValidationConstraint graceOnInterestChargedConstraint = validationHelper
.createIntegerConstraint(DataValidationConstraint.OperatorType.GREATER_OR_EQUAL, "0", null);
DataValidationConstraint lastRepaymentDateConstraint = validationHelper
.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, "=$I1", "=TODAY()", dateFormat);
DataValidationConstraint chargeOneNameConstraint = validationHelper.createFormulaListConstraint("Charges");
DataValidationConstraint chargeOneAmountTypeConstraint = validationHelper
.createExplicitListConstraint(new String[] { "Flat", "% Amount" });
DataValidationConstraint chargeTwoNameConstraint = validationHelper.createFormulaListConstraint("Charges");
DataValidationConstraint chargeTwoAmountTypeConstraint = validationHelper
.createExplicitListConstraint(new String[] { "Flat", "% Amount" });
DataValidation officeValidation = validationHelper.createValidation(officeNameConstraint, officeNameRange);
DataValidation loanTypeValidation = validationHelper.createValidation(loanTypeConstraint, loanTypeRange);
DataValidation clientValidation = validationHelper.createValidation(clientNameConstraint, clientNameRange);
DataValidation productNameValidation = validationHelper.createValidation(productNameConstraint, productNameRange);
DataValidation loanOfficerValidation = validationHelper.createValidation(loanOfficerNameConstraint, loanOfficerRange);
DataValidation fundNameValidation = validationHelper.createValidation(fundNameConstraint, fundNameRange);
DataValidation repaidFrequencyValidation = validationHelper.createValidation(frequencyConstraint, repaidFrequencyRange);
DataValidation loanTermFrequencyValidation = validationHelper.createValidation(loanTermFrequencyConstraint, loanTermFrequencyRange);
DataValidation amortizationValidation = validationHelper.createValidation(amortizationConstraint, amortizationRange);
DataValidation interestMethodValidation = validationHelper.createValidation(interestMethodConstraint, interestMethodRange);
DataValidation interestCalculationPeriodValidation = validationHelper.createValidation(interestCalculationPeriodConstraint,
intrestCalculationPeriodRange);
DataValidation repaymentStrategyValidation = validationHelper.createValidation(repaymentStrategyConstraint, repaymentStrategyRange);
DataValidation paymentTypeValidation = validationHelper.createValidation(paymentTypeConstraint, paymentTypeRange);
DataValidation repaymentTypeValidation = validationHelper.createValidation(paymentTypeConstraint, repaymentTypeRange);
DataValidation submittedDateValidation = validationHelper.createValidation(submittedDateConstraint, submittedDateRange);
DataValidation approvalDateValidation = validationHelper.createValidation(approvalDateConstraint, approvedDateRange);
DataValidation disbursedDateValidation = validationHelper.createValidation(disbursedDateConstraint, disbursedDateRange);
DataValidation lastRepaymentDateValidation = validationHelper.createValidation(lastRepaymentDateConstraint, lastrepaymentDateRange);
DataValidation principalValidation = validationHelper.createValidation(principalConstraint, principalRange);
DataValidation loanTermValidation = validationHelper.createValidation(loanTermConstraint, loanTermRange);
DataValidation noOfRepaymentsValidation = validationHelper.createValidation(noOfRepaymentsConstraint, noOfRepaymentsRange);
DataValidation interestValidation = validationHelper.createValidation(interestConstraint, interestRange);
DataValidation arrearsToleranceValidation = validationHelper.createValidation(arrearsToleranceConstraint, arrearsToleranceRange);
DataValidation graceOnPrincipalPaymentValidation = validationHelper.createValidation(graceOnPrincipalPaymentConstraint,
graceOnPrincipalPaymentRange);
DataValidation graceOnInterestPaymentValidation = validationHelper.createValidation(graceOnInterestPaymentConstraint,
graceOnInterestPaymentRange);
DataValidation graceOnInterestChargedValidation = validationHelper.createValidation(graceOnInterestChargedConstraint,
graceOnInterestChargedRange);
DataValidation interestFrequencyValidation = validationHelper.createValidation(interestFrequencyConstraint, interestFrequencyRange);
DataValidation chargeOneNameValidation = validationHelper.createValidation(chargeOneNameConstraint, chargeOneNameRange);
DataValidation chargeOneAmountTypeValidation = validationHelper.createValidation(chargeOneAmountTypeConstraint,
chargeOneAmountTypeRange);
DataValidation chargeTwoNameValidation = validationHelper.createValidation(chargeTwoNameConstraint, chargeTwoNameRange);
DataValidation chargeTwoAmountTypeValidation = validationHelper.createValidation(chargeTwoAmountTypeConstraint,
chargeTwoAmountTypeRange);
interestFrequencyValidation.setSuppressDropDownArrow(true);
worksheet.addValidationData(officeValidation);
worksheet.addValidationData(loanTypeValidation);
worksheet.addValidationData(clientValidation);
worksheet.addValidationData(productNameValidation);
worksheet.addValidationData(loanOfficerValidation);
worksheet.addValidationData(submittedDateValidation);
worksheet.addValidationData(approvalDateValidation);
worksheet.addValidationData(disbursedDateValidation);
worksheet.addValidationData(paymentTypeValidation);
worksheet.addValidationData(fundNameValidation);
worksheet.addValidationData(principalValidation);
worksheet.addValidationData(repaidFrequencyValidation);
worksheet.addValidationData(loanTermFrequencyValidation);
worksheet.addValidationData(noOfRepaymentsValidation);
worksheet.addValidationData(loanTermValidation);
worksheet.addValidationData(interestValidation);
worksheet.addValidationData(interestFrequencyValidation);
worksheet.addValidationData(amortizationValidation);
worksheet.addValidationData(interestMethodValidation);
worksheet.addValidationData(interestCalculationPeriodValidation);
worksheet.addValidationData(repaymentStrategyValidation);
worksheet.addValidationData(arrearsToleranceValidation);
worksheet.addValidationData(graceOnPrincipalPaymentValidation);
worksheet.addValidationData(graceOnInterestPaymentValidation);
worksheet.addValidationData(graceOnInterestChargedValidation);
worksheet.addValidationData(lastRepaymentDateValidation);
worksheet.addValidationData(repaymentTypeValidation);
worksheet.addValidationData(chargeOneNameValidation);
// worksheet.addValidationData(chargeOneAmountValidation);
worksheet.addValidationData(chargeOneAmountTypeValidation);
worksheet.addValidationData(chargeTwoNameValidation);
// worksheet.addValidationData(chargeTwoAmountValidation);
worksheet.addValidationData(chargeTwoAmountTypeValidation);
}