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

     @Autowired
     LoanRepository loanRepository;
     @Autowired
     private LoanStatementRepository loanStatementRepository;
     @Autowired
+    private ReturnOrderInfoRepository returnOrderInfoRepository;
+    @Autowired
     SDCreditRequirementRepository sdCreditRequirementRepository;
     @Autowired
     LoiFormRepository loiFormRepository;
     @Autowired
     PartnerOnBoardingPanelRepository partnerOnBoardingPanelRepository;
         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())));
+        // 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).
         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());
+                .filter(x -> !WalletService.CN_WALLET_REFERENCES.contains(x.getReferenceType()))
-        //LOGGER.info("Data - {}", history.stream().filter(x -> x.getReferenceType().equals(WalletReferenceType.OTHERS)).collect(Collectors.groupingBy(x -> x.getBusinessTimestamp().toLocalDate())));
+                .sorted(Comparator.comparing(UserWalletHistory::getId)).collect(Collectors.toList());
         InputStream is = getClass().getClassLoader().getResourceAsStream("account-statement.xlsx");
         CustomRetailer customRetailer = retailerService.getAllFofoRetailers().get(fofoId);
         List<StatementDetailModel> billedStatementDetails = orderRepository.selectDetailsBetween(fofoId, startDate, endDate);
+        List<StatementDetailModel> returnDetails = returnOrderInfoRepository.selectReturnsBetween(fofoId, startDate, endDate);
         Map<Integer, Float> openingAmountMap = transactionService.getPendingIndentValueMap(startDate, Optional.of(fofoId));
         Map<Integer, Float> closingAmountMap = transactionService.getPendingIndentValueMap(endDate, Optional.of(fofoId));
         float openingPendingAmount = openingAmountMap.get(fofoId) == null ? 0 : openingAmountMap.get(fofoId);
         float closingPendingAmount = closingAmountMap.get(fofoId) == null ? 0 : closingAmountMap.get(fofoId);
+        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("Opening - {}, Closing - {}", openingPendingAmount, closingPendingAmount);
+        LOGGER.info("OpeningPI - {}, ClosingPI - {}, OpeningLoan - {}, ClosingLoan - {}, InterestAccrued - {}", openingPendingAmount, closingPendingAmount, openingLoan, closingLoan, interestAccruedCost);
-        ByteArrayOutputStream byteArrayOutputStream = this.populateData(is, openingBalance + openingPendingAmount, closingBalance + closingPendingAmount,
+        ByteArrayOutputStream byteArrayOutputStream = this.populateData(is, openingBalance + openingPendingAmount - openingLoan, closingBalance + closingPendingAmount - closingLoan,
-                customRetailer, history, startDate, endDate, billedStatementDetails);
+                customRetailer, history, startDate, endDate, billedStatementDetails, returnDetails, 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);
     @Autowired
     CreditNoteRepository creditNoteRepository;
     @Autowired
     CreditNoteLineRepository creditNoteLineRepository;
+    // Friendly category for a statement line, keyed on the wallet reference-type or billing sub-type string.
+    private String statementCategory(String type) {
+        if (type == null) return "Other";
+        switch (type) {
+            case "ADVANCE_AMOUNT": case "AUTOMATED_ADVANCE": return "Advance / Payment";
+            case "PAYMENT_GATEWAY": case "RECHARGE": return "Payment";
+            case "SCHEME_IN": case "SCHEME_OUT": case "ADDITIONAL_SCHEME": case "ACTIVATION_SCHEME": return "Scheme / Margin";
+            case "CREDIT_LIMIT": case "CREDIT_UTILIZED": return "SD Credit (Loan)";
+            case "PURCHASE": return "Purchase / Order Refund";
+            case "REFUND": return "Refund";
+            case "PRICE_DROP": return "Price Drop";
+            case "BRAND_PAYOUT": case "INVESTMENT_PAYOUT": return "Payout";
+            case "SPECIAL_SUPPORT": return "Special Support";
+            case "DAMAGE_PROTECTION": return "Damage Protection";
+            case "PREBOOKING_ORDER": return "Prebooking Order";
+            // billing sub-types from selectDetailsBetween
+            case "BILLED": return "Billing / Invoice";
+            case "RTO": return "Cancellation (RTO)";
+            case "RETURNED": return "Return";
+            case "INVOICE_CANCELLED": return "Invoice Cancelled";
+            case "RETURNS_CN": return "Return (Credit Note)";
+            default: return "Other";
+        }
+    }
     private ByteArrayOutputStream populateData(InputStream is, float openingBalance, float closingBalance, CustomRetailer customRetailer, List<UserWalletHistory> history,
-                                               LocalDateTime startLocalDateTime, LocalDateTime endLocalDateTime, List<StatementDetailModel> invoiceDetails) throws Exception {
+                                               LocalDateTime startLocalDateTime, LocalDateTime endLocalDateTime, List<StatementDetailModel> invoiceDetails,
+                                               List<StatementDetailModel> returnDetails, 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()));
         XSSFWorkbook workbook = new XSSFWorkbook(is);
         CreationHelper creationHelper = workbook.getCreationHelper();
         CellStyle dateStyle = workbook.createCellStyle();
                         grandTotalDebit -= walletEntry.getAmount();
+                    }
                     //Running balance
                     currentOpening += walletEntry.getAmount();
                     sheet.getRow(row).createCell(5).setCellValue(currentOpening);
-                    //Description
+                    //Category + Description
+                    String wCategory = statementCategory(walletEntry.getReferenceType().toString());
-                    sheet.getRow(row).createCell(6).setCellValue(walletEntry.getDescription());
+                    String wDesc = walletEntry.getDescription();
+                    sheet.getRow(row).createCell(6).setCellValue(wDesc == null || wDesc.isEmpty() ? wCategory : wCategory + " — " + wDesc);
                     row++;
+                }
+            }
             List<StatementDetailModel> statementDetailModels = dateInvoiceMap.get(date);
             LOGGER.info("statementDetailModels - {}", statementDetailModels);
             if (statementDetailModels != null) {
                 for (StatementDetailModel statementDetailModel : statementDetailModels) {
+                    // Returns are shown individually from returnorderinfo below; skip the billing query's aggregated
+                    // RETURNED and the RETURNS_CN (returns are not taken from credit notes).
+                    if ("RETURNED".equals(statementDetailModel.getReferenceType())
+                            || "RETURNS_CN".equals(statementDetailModel.getReferenceType())) {
+                        continue;
+                    }
                     Cell dateCell = sheet.createRow(row).createCell(0);
                     dateCell.setCellValue(statementDetailModel.getOnDate());
                     dateCell.setCellStyle(dateStyle);
                     //Transact Type
                     sheet.getRow(row).createCell(1).setCellValue("BILLING");
                     //Running Balance
                     currentOpening -= statementDetailModel.getAmount();
                     sheet.getRow(row).createCell(5).setCellValue(currentOpening);
-                    //Narration
+                    //Category (Narration)
-                    sheet.getRow(row).createCell(6).setCellValue(statementDetailModel.getReferenceType());
+                    sheet.getRow(row).createCell(6).setCellValue(statementCategory(statementDetailModel.getReferenceType()) + " (" + statementDetailModel.getReferenceType() + ")");
                     row += 1;
+                }
+            }
+            // Individual returns (returnorderinfo): one credit line per return, against its invoice, on refundedAt.
+            List<StatementDetailModel> dateReturns = dateReturnMap.get(date);
+            if (dateReturns != null) {
+                for (StatementDetailModel ret : dateReturns) {
+                    Cell dateCell = sheet.createRow(row).createCell(0);
+                    dateCell.setCellValue(ret.getOnDate());
+                    dateCell.setCellStyle(dateStyle);
+                    sheet.getRow(row).createCell(1).setCellValue("RETURN");
+                    sheet.getRow(row).createCell(2).setCellValue(ret.getInvoiceNumber());
+                    sheet.getRow(row).createCell(3).setCellValue(0);
+                    sheet.getRow(row).createCell(4).setCellValue(ret.getAmount());
+                    grandTotalCredit += ret.getAmount();
+                    currentOpening += ret.getAmount();
+                    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
+            // returnorderinfo via the billing query). MARGINS = scheme/margin credit; CN_CANCELLATION = debit (reverses a
+            // MARGINS CN). Any CN→raw-scheme or cross-period timing difference lands in the reconciling diff line below.
             if (YearMonth.from(date).atEndOfMonth().equals(date)) {
                 List<CreditNote> creditNotes = creditNoteRepository.selectAll(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;
+                        }
                         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("CREDIT NOTE");
+                        sheet.getRow(row).createCell(1).setCellValue(cnAmount >= 0 ? "CREDIT NOTE" : "DEBIT NOTE");
                         sheet.getRow(row).createCell(2).setCellValue(creditNote.getCreditNoteNumber());
                         if (cnAmount > 0) {
                             sheet.getRow(row).createCell(3).setCellValue(0);
                             sheet.getRow(row).createCell(4).setCellValue(cnAmount);
                             grandTotalCredit += cnAmount;
                         } else {
                             sheet.getRow(row).createCell(3).setCellValue(-cnAmount);
                             sheet.getRow(row).createCell(4).setCellValue(0);
                             grandTotalDebit += -cnAmount;
+                        }
-                        //Running balance
                         currentOpening += cnAmount;
                         sheet.getRow(row).createCell(5).setCellValue(currentOpening);
-                        //Narration
+                        sheet.getRow(row).createCell(6).setCellValue(creditNote.getType() == CreditNoteType.CN_CANCELLATION
-                        sheet.getRow(row).createCell(6).setCellValue("Credit Note Issued");
+                                ? "Margin Cancellation" : "Scheme / Margin");
                         row += 1;
+                    }
+                }
+            }
+        }
+        // --- 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) {
+            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("INTEREST ACCRUED");
+            sheet.getRow(row).createCell(2).setCellValue("");
+            sheet.getRow(row).createCell(3).setCellValue(interestAccruedCost);
+            sheet.getRow(row).createCell(4).setCellValue(0);
+            grandTotalDebit += (long) interestAccruedCost;
+            currentOpening -= interestAccruedCost;
+            sheet.getRow(row).createCell(5).setCellValue(currentOpening);
+            sheet.getRow(row).createCell(6).setCellValue("SD Credit — Interest accrued (financing cost)");
+            row++;
+        }
+        // Pending-indent + floating-diff line: lands the running balance exactly on the formula closing. For a clean
+        // monthly statement this is ~float; it also absorbs legitimate cross-period residuals (scheme→CN timing, a
+        // CN_CANCELLATION reversing a prior month, a return whose sale was billed earlier).
+        double pendingIndentLine = closingBalance - currentOpening;
+        if (Math.abs(pendingIndentLine) > 0.01) {
+            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("NET PENDING INDENT / DIFF");
+            sheet.getRow(row).createCell(2).setCellValue("");
+            if (pendingIndentLine > 0) {
+                sheet.getRow(row).createCell(3).setCellValue(0);
+                sheet.getRow(row).createCell(4).setCellValue(pendingIndentLine);
+                grandTotalCredit += (long) pendingIndentLine;
+            } else {
+                sheet.getRow(row).createCell(3).setCellValue(-pendingIndentLine);
+                sheet.getRow(row).createCell(4).setCellValue(0);
+                grandTotalDebit += (long) -pendingIndentLine;
+            }
+            currentOpening += pendingIndentLine;
+            sheet.getRow(row).createCell(5).setCellValue(currentOpening);
+            sheet.getRow(row).createCell(6).setCellValue("Net Pending Indent + reconciling diff (cross-period / timing / float)");
+            row++;
+        }
         sheet.createRow(row).createCell(2).setCellValue("Grand Total");
         sheet.getRow(row).createCell(3).setCellValue(grandTotalDebit);
         sheet.getRow(row).createCell(4).setCellValue(grandTotalCredit);
-        /*row += 2;
+        row += 2;
         sheet.createRow(row).createCell(0).setCellValue("Closing Balance");
-        sheet.getRow(row).createCell(3).setCellValue(closingBalance);*/
+        sheet.getRow(row).createCell(5).setCellValue(closingBalance);
         row++;
+        double diff = Math.abs(currentOpening - closingBalance);
-        /*
+        if (diff > 1) {
-         * sheet.createRow(row).createCell(0).setCellValue("Pending Grns");
+            LOGGER.warn("Account statement reconciliation mismatch for {}: computed={}, expected={}, diff={}",
-         * sheet.getRow(row).createCell(2).setCellValue(closingBalance); row++;
+                    customRetailer.getPartnerId(), currentOpening, closingBalance, diff);
-         */
+        }
         try {
             workbook.write(bos);
         } finally {
             workbook.close();
             bos.close();

Subversion Repositories SmartDukaan

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