WebSVN – SmartDukaan – Diff – /trunk/profitmandi-fofo/src/main/java/com/spice/profitmandi/web/controller/WalletController.java

         float closingBalance = walletService.getOpeningTill(fofoId, endDate);
         UserWallet uw = walletService.getUserWallet(fofoId);
         LOGGER.info("Start date - {}, end Date - {}", startDate, endDate);
         List<UserWalletHistory> history = userWalletHistoryRepository.selectPaginatedByWalletId(uw.getId(), startDate, endDate, 0, 0);
         //LOGGER.info("Data - {}", history.stream().filter(x -> x.getReferenceType().equals(WalletReferenceType.OTHERS)).collect(Collectors.groupingBy(x -> x.getBusinessTimestamp().toLocalDate())));
+        // Raw scheme entries (CN_WALLET_REFERENCES) are normally shown as the month-end MARGINS credit-note line; but for a
+        // statement that does NOT end on a month-end the trailing month's CN isn't issued yet, so we fall back to these
+        // raw entries for that partial month (see populateData).
+        List<UserWalletHistory> schemeEntries = history.stream()
+                .filter(x -> WalletService.CN_WALLET_REFERENCES.contains(x.getReferenceType()))
+                .sorted(Comparator.comparing(UserWalletHistory::getId)).collect(Collectors.toList());
         // Body shows genuine money movements. Exclude: PURCHASE (rebuilt via billing); the loan cash legs
         // CREDIT_LIMIT/CREDIT_UTILIZED (represented by the Interest Accrued line + opening/closing loan); and the
-        // scheme set CN_WALLET_REFERENCES (shown instead as the month-end MARGINS credit-note line).
+        // scheme set CN_WALLET_REFERENCES (shown instead as the month-end MARGINS credit-note line / partial fallback).
         history = history.stream().filter(x -> !x.getReferenceType().equals(WalletReferenceType.PURCHASE))
                 .filter(x -> !x.getReferenceType().equals(WalletReferenceType.CREDIT_LIMIT))
                 .filter(x -> !x.getReferenceType().equals(WalletReferenceType.CREDIT_UTILIZED))
                 .filter(x -> !WalletService.CN_WALLET_REFERENCES.contains(x.getReferenceType()))
                 .sorted(Comparator.comparing(UserWalletHistory::getId)).collect(Collectors.toList());
         float openingLoan = loanStatementRepository.getLoanPrincipalAtDate(fofoId, startDate) + loanStatementRepository.getLoanInterestAtDate(fofoId, startDate);
         float closingLoan = loanStatementRepository.getLoanPrincipalAtDate(fofoId, endDate) + loanStatementRepository.getLoanInterestAtDate(fofoId, endDate);
         float interestAccruedCost = loanStatementRepository.getInterestAccruedBetween(fofoId, startDate, endDate);
         LOGGER.info("OpeningPI - {}, ClosingPI - {}, OpeningLoan - {}, ClosingLoan - {}, InterestAccrued - {}", openingPendingAmount, closingPendingAmount, openingLoan, closingLoan, interestAccruedCost);
         ByteArrayOutputStream byteArrayOutputStream = this.populateData(is, openingBalance + openingPendingAmount - openingLoan, closingBalance + closingPendingAmount - closingLoan,
-                customRetailer, history, startDate, endDate, billedStatementDetails, returnDetails, interestAccruedCost);
+                customRetailer, history, startDate, endDate, billedStatementDetails, returnDetails, schemeEntries, interestAccruedCost);
         final HttpHeaders headers = new HttpHeaders();
         headers.set("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
         headers.set("Content-disposition", "inline; filename=account-statement." + StringUtils.toHyphenatedString(startDate.toLocalDate()) + "-" + StringUtils.toHyphenatedString(endDate.toLocalDate()) + ".xlsx");
         headers.setContentLength(byteArrayOutputStream.toByteArray().length);
+        }
+    }
     private ByteArrayOutputStream populateData(InputStream is, float openingBalance, float closingBalance, CustomRetailer customRetailer, List<UserWalletHistory> history,
                                                LocalDateTime startLocalDateTime, LocalDateTime endLocalDateTime, List<StatementDetailModel> invoiceDetails,
-                                               List<StatementDetailModel> returnDetails, float interestAccruedCost) throws Exception {
+                                               List<StatementDetailModel> returnDetails, List<UserWalletHistory> schemeEntries, float interestAccruedCost) throws Exception {
         Map<LocalDate, List<StatementDetailModel>> dateInvoiceMap = invoiceDetails.stream().collect(Collectors.groupingBy(x -> x.getOnDate().toLocalDate()));
         Map<LocalDate, List<StatementDetailModel>> dateReturnMap = returnDetails == null ? new HashMap<>()
                 : returnDetails.stream().collect(Collectors.groupingBy(x -> x.getOnDate().toLocalDate()));
         //LOGGER.info("dateInvoiceMap - {}", dateInvoiceMap);
         Map<LocalDate, List<UserWalletHistory>> dateWalletMap = history.stream().collect(Collectors.groupingBy(x -> x.getTimestamp().toLocalDate()));
                     sheet.getRow(row).createCell(5).setCellValue(currentOpening);
                     sheet.getRow(row).createCell(6).setCellValue("Return (Against Invoice " + ret.getInvoiceNumber() + ")");
                     row += 1;
+                }
+            }
-            // Month-end credit notes (shown before the interest line): all CN types EXCEPT RETURNS (returns come from
+            // Month-end scheme/margin: one "Margin / Scheme Payout" line PER credit note (MARGINS credit; CN_CANCELLATION
-            // returnorderinfo via the billing query). MARGINS = scheme/margin credit; CN_CANCELLATION = debit (reverses a
+            // debit, reverses a MARGINS CN), keyed by margin_month. RETURNS are sourced from returnorderinfo and skipped.
-            // MARGINS CN). Any CN→raw-scheme or cross-period timing difference lands in the reconciling diff line below.
+            // Per CN: net >=0 => credit (CN); net <0 => debit (DN), reducing the balance.
             if (YearMonth.from(date).atEndOfMonth().equals(date)) {
                 List<CreditNote> creditNotes = creditNoteRepository.selectByMarginMonth(customRetailer.getPartnerId(), YearMonth.from(date));
                 if (creditNotes != null) {
                     for (CreditNote creditNote : creditNotes) {
-                        // RETURNS are sourced from returnorderinfo (billing query), not from the CN; skip them here.
                         if (creditNote.getType() == CreditNoteType.RETURNS) {
                             continue;
+                        }
                         double cnAmount = creditNoteLineRepository.selectAllByCreditNote(creditNote.getId()).stream().collect(Collectors.summingDouble(x -> x.getAmount()));
-                        // CN_CANCELLATION reverses a previously-issued MARGINS credit note; line amount is stored positive but must debit.
                         if (creditNote.getType() == CreditNoteType.CN_CANCELLATION) {
                             cnAmount = -cnAmount;
+                        }
+                        if (Math.abs(cnAmount) <= 0.01) {
+                            continue;
+                        }
+                        boolean isCredit = cnAmount >= 0;
                         Cell dateCell = sheet.createRow(row).createCell(0);
                         dateCell.setCellValue(new Date(date.atStartOfDay().atZone(ZoneId.systemDefault()).toInstant().toEpochMilli()));
                         dateCell.setCellStyle(dateStyle);
-                        // Net -ve (e.g. negative net margin, or a cancellation) is a debit note.
-                        sheet.getRow(row).createCell(1).setCellValue(cnAmount >= 0 ? "CREDIT NOTE" : "DEBIT NOTE");
+                        sheet.getRow(row).createCell(1).setCellValue("Margin / Scheme Payout");
                         sheet.getRow(row).createCell(2).setCellValue(creditNote.getCreditNoteNumber());
-                        if (cnAmount > 0) {
+                        if (isCredit) {
                             sheet.getRow(row).createCell(3).setCellValue(0);
                             sheet.getRow(row).createCell(4).setCellValue(cnAmount);
-                            grandTotalCredit += cnAmount;
+                            grandTotalCredit += (long) cnAmount;
                         } else {
                             sheet.getRow(row).createCell(3).setCellValue(-cnAmount);
                             sheet.getRow(row).createCell(4).setCellValue(0);
-                            grandTotalDebit += -cnAmount;
+                            grandTotalDebit += (long) -cnAmount;
+                        }
                         currentOpening += cnAmount;
                         sheet.getRow(row).createCell(5).setCellValue(currentOpening);
-                        sheet.getRow(row).createCell(6).setCellValue(creditNote.getType() == CreditNoteType.CN_CANCELLATION
+                        sheet.getRow(row).createCell(6).setCellValue(isCredit ? "Credit Note (CN)" : "Debit Note (DN)");
-                                ? "Margin Cancellation" : "Scheme / Margin");
                         row += 1;
+                    }
+                }
+            }
+        }
+        // Partial-month fallback (net computed here, rendered as ONE line just before the FLOAT DIFF below): if the
+        // statement does NOT end on a month-end, the trailing month's MARGINS CN isn't issued yet, so consolidate that
+        // month's raw scheme entries into a single line. Full months already used their month-end CN above; only the
+        // trailing month's entries are summed here.
+        double schemeFallbackNet = 0d;
+        if (!YearMonth.from(endDate).atEndOfMonth().equals(endDate) && schemeEntries != null) {
+            LocalDate partialStart = YearMonth.from(endDate).atDay(1);
+            for (UserWalletHistory s : schemeEntries) {
+                if (!s.getTimestamp().toLocalDate().isBefore(partialStart)) {
+                    schemeFallbackNet += s.getAmount();
+                }
+            }
+        }
         // --- Reconciling lines at period end ---
         // Interest accrued (charged, non-cash) is the only standing-moving loan piece. Principal movement and interest
         // PAID are funded by wallet cash (the excluded CREDIT_LIMIT/CREDIT_UTILIZED legs) and net to ~0, so they are not
         // shown as balance lines. Accrued interest is a debit (the partner's liability grows).
         if (Math.abs(interestAccruedCost) > 0.01f) {
             currentOpening -= interestAccruedCost;
             sheet.getRow(row).createCell(5).setCellValue(currentOpening);
             sheet.getRow(row).createCell(6).setCellValue("SD Credit — Interest accrued (financing cost)");
             row++;
+        }
+        // Single consolidated scheme/margin line for a partial (non-month-end) trailing month (computed above). Placed
+        // here, at the end of the statement just before the float adjustment. Net >=0 => credit (CN); net <0 => debit (DN).
+        if (Math.abs(schemeFallbackNet) > 0.01) {
+            boolean isCredit = schemeFallbackNet >= 0;
+            Cell dateCell = sheet.createRow(row).createCell(0);
+            dateCell.setCellValue(new Date(endLocalDateTime.atZone(ZoneId.systemDefault()).toInstant().toEpochMilli()));
+            dateCell.setCellStyle(dateStyle);
+            sheet.getRow(row).createCell(1).setCellValue("Margin / Scheme Payout");
+            sheet.getRow(row).createCell(2).setCellValue("");
+            if (isCredit) {
+                sheet.getRow(row).createCell(3).setCellValue(0);
+                sheet.getRow(row).createCell(4).setCellValue(schemeFallbackNet);
+                grandTotalCredit += (long) schemeFallbackNet;
+            } else {
+                sheet.getRow(row).createCell(3).setCellValue(-schemeFallbackNet);
+                sheet.getRow(row).createCell(4).setCellValue(0);
+                grandTotalDebit += (long) -schemeFallbackNet;
+            }
+            currentOpening += schemeFallbackNet;
+            sheet.getRow(row).createCell(5).setCellValue(currentOpening);
+            sheet.getRow(row).createCell(6).setCellValue(isCredit ? "Credit Note (CN)" : "Debit Note (DN)");
+            row++;
+        }
         // Float-difference line: lands the running balance exactly on the formula closing. With clean data this is only
         // a few rupees of rounding/float; a large value signals a data issue (e.g. MARGINS CN not yet rolled out) and is
         // surfaced by the reconciliation warning below rather than being a real ledger movement.
         double floatDiff = closingBalance - currentOpening;
         if (Math.abs(floatDiff) > 0.01) {

Subversion Repositories SmartDukaan

(root)/trunk/profitmandi-fofo/src/main/java/com/spice/profitmandi/web/controller/WalletController.java – Rev 36769 → 36772