| Line 843... |
Line 843... |
| 843 |
// statement that does NOT end on a month-end the trailing month's CN isn't issued yet, so we fall back to these
|
843 |
// statement that does NOT end on a month-end the trailing month's CN isn't issued yet, so we fall back to these
|
| 844 |
// raw entries for that partial month (see populateData).
|
844 |
// raw entries for that partial month (see populateData).
|
| 845 |
List<UserWalletHistory> schemeEntries = history.stream()
|
845 |
List<UserWalletHistory> schemeEntries = history.stream()
|
| 846 |
.filter(x -> WalletService.CN_WALLET_REFERENCES.contains(x.getReferenceType()))
|
846 |
.filter(x -> WalletService.CN_WALLET_REFERENCES.contains(x.getReferenceType()))
|
| 847 |
.sorted(Comparator.comparing(UserWalletHistory::getId)).collect(Collectors.toList());
|
847 |
.sorted(Comparator.comparing(UserWalletHistory::getId)).collect(Collectors.toList());
|
| 848 |
// Body shows genuine money movements. Exclude: PURCHASE (rebuilt via billing); the loan cash legs
|
848 |
// Body shows wallet movements. Exclude only: PURCHASE (rebuilt via billing) and the scheme set
|
| 849 |
// CREDIT_LIMIT/CREDIT_UTILIZED (represented by the Interest Accrued line + opening/closing loan); and the
|
849 |
// CN_WALLET_REFERENCES (shown instead as the month-end MARGINS credit-note line / partial fallback).
|
| - |
|
850 |
// Loan/credit legs (CREDIT_LIMIT/CREDIT_UTILIZED/LOAN/LOAN_REPAYMENT) are kept so the body reconciles to
|
| 850 |
// scheme set CN_WALLET_REFERENCES (shown instead as the month-end MARGINS credit-note line / partial fallback).
|
851 |
// the loan-free closing (wallet + pending indent); loan is not represented separately in this statement.
|
| 851 |
history = history.stream().filter(x -> !x.getReferenceType().equals(WalletReferenceType.PURCHASE))
|
852 |
history = history.stream().filter(x -> !x.getReferenceType().equals(WalletReferenceType.PURCHASE))
|
| 852 |
.filter(x -> !x.getReferenceType().equals(WalletReferenceType.CREDIT_LIMIT))
|
- |
|
| 853 |
.filter(x -> !x.getReferenceType().equals(WalletReferenceType.CREDIT_UTILIZED))
|
- |
|
| 854 |
.filter(x -> !x.getReferenceType().equals(WalletReferenceType.LOAN))
|
- |
|
| 855 |
.filter(x -> !x.getReferenceType().equals(WalletReferenceType.LOAN_REPAYMENT))
|
- |
|
| 856 |
.filter(x -> !WalletService.CN_WALLET_REFERENCES.contains(x.getReferenceType()))
|
853 |
.filter(x -> !WalletService.CN_WALLET_REFERENCES.contains(x.getReferenceType()))
|
| 857 |
.sorted(Comparator.comparing(UserWalletHistory::getId)).collect(Collectors.toList());
|
854 |
.sorted(Comparator.comparing(UserWalletHistory::getId)).collect(Collectors.toList());
|
| 858 |
|
855 |
|
| 859 |
InputStream is = getClass().getClassLoader().getResourceAsStream("account-statement.xlsx");
|
856 |
InputStream is = getClass().getClassLoader().getResourceAsStream("account-statement.xlsx");
|
| 860 |
CustomRetailer customRetailer = retailerService.getAllFofoRetailers().get(fofoId);
|
857 |
CustomRetailer customRetailer = retailerService.getAllFofoRetailers().get(fofoId);
|
| Line 863... |
Line 860... |
| 863 |
Map<Integer, Float> openingAmountMap = transactionService.getPendingIndentValueMap(startDate, Optional.of(fofoId));
|
860 |
Map<Integer, Float> openingAmountMap = transactionService.getPendingIndentValueMap(startDate, Optional.of(fofoId));
|
| 864 |
Map<Integer, Float> closingAmountMap = transactionService.getPendingIndentValueMap(endDate, Optional.of(fofoId));
|
861 |
Map<Integer, Float> closingAmountMap = transactionService.getPendingIndentValueMap(endDate, Optional.of(fofoId));
|
| 865 |
|
862 |
|
| 866 |
float openingPendingAmount = openingAmountMap.get(fofoId) == null ? 0 : openingAmountMap.get(fofoId);
|
863 |
float openingPendingAmount = openingAmountMap.get(fofoId) == null ? 0 : openingAmountMap.get(fofoId);
|
| 867 |
float closingPendingAmount = closingAmountMap.get(fofoId) == null ? 0 : closingAmountMap.get(fofoId);
|
864 |
float closingPendingAmount = closingAmountMap.get(fofoId) == null ? 0 : closingAmountMap.get(fofoId);
|
| 868 |
float openingLoan = loanStatementRepository.getLoanPrincipalAtDate(fofoId, startDate) + loanStatementRepository.getLoanInterestAtDate(fofoId, startDate);
|
- |
|
| 869 |
float closingLoan = loanStatementRepository.getLoanPrincipalAtDate(fofoId, endDate) + loanStatementRepository.getLoanInterestAtDate(fofoId, endDate);
|
- |
|
| 870 |
float interestAccruedCost = loanStatementRepository.getInterestAccruedBetween(fofoId, startDate, endDate);
|
- |
|
| 871 |
LOGGER.info("OpeningPI - {}, ClosingPI - {}, OpeningLoan - {}, ClosingLoan - {}, InterestAccrued - {}", openingPendingAmount, closingPendingAmount, openingLoan, closingLoan, interestAccruedCost);
|
865 |
LOGGER.info("OpeningPI - {}, ClosingPI - {}", openingPendingAmount, closingPendingAmount);
|
| 872 |
ByteArrayOutputStream byteArrayOutputStream = this.populateData(is, openingBalance + openingPendingAmount - openingLoan, closingBalance + closingPendingAmount - closingLoan,
|
866 |
ByteArrayOutputStream byteArrayOutputStream = this.populateData(is, openingBalance + openingPendingAmount, closingBalance + closingPendingAmount,
|
| 873 |
customRetailer, history, startDate, endDate, billedStatementDetails, returnDetails, schemeEntries, interestAccruedCost);
|
867 |
customRetailer, history, startDate, endDate, billedStatementDetails, returnDetails, schemeEntries);
|
| 874 |
|
868 |
|
| 875 |
final HttpHeaders headers = new HttpHeaders();
|
869 |
final HttpHeaders headers = new HttpHeaders();
|
| 876 |
headers.set("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
|
870 |
headers.set("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
|
| 877 |
headers.set("Content-disposition", "inline; filename=account-statement." + StringUtils.toHyphenatedString(startDate.toLocalDate()) + "-" + StringUtils.toHyphenatedString(endDate.toLocalDate()) + ".xlsx");
|
871 |
headers.set("Content-disposition", "inline; filename=account-statement." + StringUtils.toHyphenatedString(startDate.toLocalDate()) + "-" + StringUtils.toHyphenatedString(endDate.toLocalDate()) + ".xlsx");
|
| 878 |
headers.setContentLength(byteArrayOutputStream.toByteArray().length);
|
872 |
headers.setContentLength(byteArrayOutputStream.toByteArray().length);
|
| Line 914... |
Line 908... |
| 914 |
}
|
908 |
}
|
| 915 |
}
|
909 |
}
|
| 916 |
|
910 |
|
| 917 |
private ByteArrayOutputStream populateData(InputStream is, float openingBalance, float closingBalance, CustomRetailer customRetailer, List<UserWalletHistory> history,
|
911 |
private ByteArrayOutputStream populateData(InputStream is, float openingBalance, float closingBalance, CustomRetailer customRetailer, List<UserWalletHistory> history,
|
| 918 |
LocalDateTime startLocalDateTime, LocalDateTime endLocalDateTime, List<StatementDetailModel> invoiceDetails,
|
912 |
LocalDateTime startLocalDateTime, LocalDateTime endLocalDateTime, List<StatementDetailModel> invoiceDetails,
|
| 919 |
List<StatementDetailModel> returnDetails, List<UserWalletHistory> schemeEntries, float interestAccruedCost) throws Exception {
|
913 |
List<StatementDetailModel> returnDetails, List<UserWalletHistory> schemeEntries) throws Exception {
|
| 920 |
Map<LocalDate, List<StatementDetailModel>> dateInvoiceMap = invoiceDetails.stream().collect(Collectors.groupingBy(x -> x.getOnDate().toLocalDate()));
|
914 |
Map<LocalDate, List<StatementDetailModel>> dateInvoiceMap = invoiceDetails.stream().collect(Collectors.groupingBy(x -> x.getOnDate().toLocalDate()));
|
| 921 |
Map<LocalDate, List<StatementDetailModel>> dateReturnMap = returnDetails == null ? new HashMap<>()
|
915 |
Map<LocalDate, List<StatementDetailModel>> dateReturnMap = returnDetails == null ? new HashMap<>()
|
| 922 |
: returnDetails.stream().collect(Collectors.groupingBy(x -> x.getOnDate().toLocalDate()));
|
916 |
: returnDetails.stream().collect(Collectors.groupingBy(x -> x.getOnDate().toLocalDate()));
|
| 923 |
//LOGGER.info("dateInvoiceMap - {}", dateInvoiceMap);
|
917 |
//LOGGER.info("dateInvoiceMap - {}", dateInvoiceMap);
|
| 924 |
Map<LocalDate, List<UserWalletHistory>> dateWalletMap = history.stream().collect(Collectors.groupingBy(x -> x.getTimestamp().toLocalDate()));
|
918 |
Map<LocalDate, List<UserWalletHistory>> dateWalletMap = history.stream().collect(Collectors.groupingBy(x -> x.getTimestamp().toLocalDate()));
|
| Line 1032... |
Line 1026... |
| 1032 |
}
|
1026 |
}
|
| 1033 |
// Month-end scheme/margin: one "Margin / Scheme Payout" line PER credit note (MARGINS credit; CN_CANCELLATION
|
1027 |
// Month-end scheme/margin: one "Margin / Scheme Payout" line PER credit note (MARGINS credit; CN_CANCELLATION
|
| 1034 |
// debit, reverses a MARGINS CN), keyed by margin_month. RETURNS are sourced from returnorderinfo and skipped.
|
1028 |
// debit, reverses a MARGINS CN), keyed by margin_month. RETURNS are sourced from returnorderinfo and skipped.
|
| 1035 |
// Per CN: net >=0 => credit (CN); net <0 => debit (DN), reducing the balance.
|
1029 |
// Per CN: net >=0 => credit (CN); net <0 => debit (DN), reducing the balance.
|
| 1036 |
if (YearMonth.from(date).atEndOfMonth().equals(date)) {
|
1030 |
if (YearMonth.from(date).atEndOfMonth().equals(date)) {
|
| - |
|
1031 |
YearMonth marginMonth = YearMonth.from(date);
|
| - |
|
1032 |
double cnTotalForMonth = 0d;
|
| 1037 |
List<CreditNote> creditNotes = creditNoteRepository.selectByMarginMonth(customRetailer.getPartnerId(), YearMonth.from(date));
|
1033 |
List<CreditNote> creditNotes = creditNoteRepository.selectByMarginMonth(customRetailer.getPartnerId(), marginMonth);
|
| 1038 |
if (creditNotes != null) {
|
1034 |
if (creditNotes != null) {
|
| 1039 |
for (CreditNote creditNote : creditNotes) {
|
1035 |
for (CreditNote creditNote : creditNotes) {
|
| 1040 |
if (creditNote.getType() == CreditNoteType.RETURNS) {
|
1036 |
if (creditNote.getType() == CreditNoteType.RETURNS) {
|
| 1041 |
continue;
|
1037 |
continue;
|
| 1042 |
}
|
1038 |
}
|
| Line 1061... |
Line 1057... |
| 1061 |
sheet.getRow(row).createCell(3).setCellValue(-cnAmount);
|
1057 |
sheet.getRow(row).createCell(3).setCellValue(-cnAmount);
|
| 1062 |
sheet.getRow(row).createCell(4).setCellValue(0);
|
1058 |
sheet.getRow(row).createCell(4).setCellValue(0);
|
| 1063 |
grandTotalDebit += (long) -cnAmount;
|
1059 |
grandTotalDebit += (long) -cnAmount;
|
| 1064 |
}
|
1060 |
}
|
| 1065 |
currentOpening += cnAmount;
|
1061 |
currentOpening += cnAmount;
|
| - |
|
1062 |
cnTotalForMonth += cnAmount;
|
| 1066 |
sheet.getRow(row).createCell(5).setCellValue(currentOpening);
|
1063 |
sheet.getRow(row).createCell(5).setCellValue(currentOpening);
|
| 1067 |
sheet.getRow(row).createCell(6).setCellValue(isCredit ? "Credit Note (CN)" : "Debit Note (DN)");
|
1064 |
sheet.getRow(row).createCell(6).setCellValue(isCredit ? "Credit Note (CN)" : "Debit Note (DN)");
|
| 1068 |
row += 1;
|
1065 |
row += 1;
|
| 1069 |
}
|
1066 |
}
|
| 1070 |
}
|
1067 |
}
|
| - |
|
1068 |
// Reconcile the margin CNs to the actual wallet scheme net for this month. MARGINS CNs are built from
|
| - |
|
1069 |
// GROSS scheme credits and miss same-month scheme REVERSALS (e.g. a "Live Demo ... Converted" -X), but
|
| - |
|
1070 |
// the wallet (and the formula closing) reflect the NET. Post the delta so the running balance lands on
|
| - |
|
1071 |
// the wallet net rather than the CN gross; otherwise it leaks into the FLOAT DIFF line mislabeled as rounding.
|
| - |
|
1072 |
if (schemeEntries != null) {
|
| - |
|
1073 |
double walletSchemeNet = 0d;
|
| - |
|
1074 |
for (UserWalletHistory s : schemeEntries) {
|
| - |
|
1075 |
if (YearMonth.from(s.getTimestamp()).equals(marginMonth)) {
|
| - |
|
1076 |
walletSchemeNet += s.getAmount();
|
| - |
|
1077 |
}
|
| - |
|
1078 |
}
|
| - |
|
1079 |
double schemeRecon = walletSchemeNet - cnTotalForMonth;
|
| - |
|
1080 |
if (Math.abs(schemeRecon) > 0.01) {
|
| - |
|
1081 |
boolean isReconCredit = schemeRecon >= 0;
|
| - |
|
1082 |
Cell reconDateCell = sheet.createRow(row).createCell(0);
|
| - |
|
1083 |
reconDateCell.setCellValue(new Date(date.atStartOfDay().atZone(ZoneId.systemDefault()).toInstant().toEpochMilli()));
|
| - |
|
1084 |
reconDateCell.setCellStyle(dateStyle);
|
| - |
|
1085 |
sheet.getRow(row).createCell(1).setCellValue("Scheme adjustment (per wallet)");
|
| - |
|
1086 |
sheet.getRow(row).createCell(2).setCellValue("");
|
| - |
|
1087 |
if (isReconCredit) {
|
| - |
|
1088 |
sheet.getRow(row).createCell(3).setCellValue(0);
|
| - |
|
1089 |
sheet.getRow(row).createCell(4).setCellValue(schemeRecon);
|
| - |
|
1090 |
grandTotalCredit += (long) schemeRecon;
|
| - |
|
1091 |
} else {
|
| - |
|
1092 |
sheet.getRow(row).createCell(3).setCellValue(-schemeRecon);
|
| - |
|
1093 |
sheet.getRow(row).createCell(4).setCellValue(0);
|
| - |
|
1094 |
grandTotalDebit += (long) -schemeRecon;
|
| - |
|
1095 |
}
|
| - |
|
1096 |
currentOpening += schemeRecon;
|
| - |
|
1097 |
sheet.getRow(row).createCell(5).setCellValue(currentOpening);
|
| - |
|
1098 |
sheet.getRow(row).createCell(6).setCellValue("Scheme reversals not captured in margin CN (reconciled to wallet)");
|
| - |
|
1099 |
row += 1;
|
| - |
|
1100 |
}
|
| - |
|
1101 |
}
|
| 1071 |
}
|
1102 |
}
|
| 1072 |
}
|
1103 |
}
|
| 1073 |
|
1104 |
|
| 1074 |
// Partial-month fallback (net computed here, rendered as ONE line just before the FLOAT DIFF below): if the
|
1105 |
// Partial-month fallback (net computed here, rendered as ONE line just before the FLOAT DIFF below): if the
|
| 1075 |
// statement does NOT end on a month-end, the trailing month's MARGINS CN isn't issued yet, so consolidate that
|
1106 |
// statement does NOT end on a month-end, the trailing month's MARGINS CN isn't issued yet, so consolidate that
|
| Line 1084... |
Line 1115... |
| 1084 |
}
|
1115 |
}
|
| 1085 |
}
|
1116 |
}
|
| 1086 |
}
|
1117 |
}
|
| 1087 |
|
1118 |
|
| 1088 |
// --- Reconciling lines at period end ---
|
1119 |
// --- Reconciling lines at period end ---
|
| 1089 |
// Interest accrued (charged, non-cash) is the only standing-moving loan piece. Principal movement and interest
|
1120 |
// Loan and interest are excluded from the account statement entirely (closing = wallet + pending indent);
|
| 1090 |
// PAID are funded by wallet cash (the excluded CREDIT_LIMIT/CREDIT_UTILIZED legs) and net to ~0, so they are not
|
- |
|
| 1091 |
// shown as balance lines. Accrued interest is a debit (the partner's liability grows).
|
1121 |
// the loan ledger is tracked separately, not in this statement.
|
| 1092 |
if (Math.abs(interestAccruedCost) > 0.01f) {
|
- |
|
| 1093 |
Cell dateCell = sheet.createRow(row).createCell(0);
|
- |
|
| 1094 |
dateCell.setCellValue(new Date(endLocalDateTime.atZone(ZoneId.systemDefault()).toInstant().toEpochMilli()));
|
- |
|
| 1095 |
dateCell.setCellStyle(dateStyle);
|
- |
|
| 1096 |
sheet.getRow(row).createCell(1).setCellValue("INTEREST ACCRUED");
|
- |
|
| 1097 |
sheet.getRow(row).createCell(2).setCellValue("");
|
- |
|
| 1098 |
sheet.getRow(row).createCell(3).setCellValue(interestAccruedCost);
|
- |
|
| 1099 |
sheet.getRow(row).createCell(4).setCellValue(0);
|
- |
|
| 1100 |
grandTotalDebit += (long) interestAccruedCost;
|
- |
|
| 1101 |
currentOpening -= interestAccruedCost;
|
- |
|
| 1102 |
sheet.getRow(row).createCell(5).setCellValue(currentOpening);
|
- |
|
| 1103 |
sheet.getRow(row).createCell(6).setCellValue("SD Credit — Interest accrued (financing cost)");
|
- |
|
| 1104 |
row++;
|
- |
|
| 1105 |
}
|
- |
|
| 1106 |
// Single consolidated scheme/margin line for a partial (non-month-end) trailing month (computed above). Placed
|
1122 |
// Single consolidated scheme/margin line for a partial (non-month-end) trailing month (computed above). Placed
|
| 1107 |
// here, at the end of the statement just before the float adjustment. Net >=0 => credit (CN); net <0 => debit (DN).
|
1123 |
// here, at the end of the statement just before the float adjustment. Net >=0 => credit (CN); net <0 => debit (DN).
|
| 1108 |
if (Math.abs(schemeFallbackNet) > 0.01) {
|
1124 |
if (Math.abs(schemeFallbackNet) > 0.01) {
|
| 1109 |
boolean isCredit = schemeFallbackNet >= 0;
|
1125 |
boolean isCredit = schemeFallbackNet >= 0;
|
| 1110 |
Cell dateCell = sheet.createRow(row).createCell(0);
|
1126 |
Cell dateCell = sheet.createRow(row).createCell(0);
|