Subversion Repositories SmartDukaan

package com.spice.profitmandi.web.controller;

import com.spice.profitmandi.common.enumuration.MessageType;

import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;

import com.spice.profitmandi.common.model.CustomRetailer;

import com.spice.profitmandi.common.model.HdfcPaymentModel;

import com.spice.profitmandi.common.model.ProfitMandiConstants;

import com.spice.profitmandi.common.model.UnsettledPaymentModel;

import com.spice.profitmandi.common.util.*;

import com.spice.profitmandi.dao.entity.dtr.CreditAccount;

import com.spice.profitmandi.dao.entity.transaction.*;

import com.spice.profitmandi.dao.entity.user.FullStockPayment;

import com.spice.profitmandi.dao.enumuration.fofo.Gateway;

import com.spice.profitmandi.dao.enumuration.onBorading.onBoardingFormEnums.FullStockPaymentStatus;

import com.spice.profitmandi.dao.enumuration.transaction.AddWalletRequestStatus;

import com.spice.profitmandi.dao.enumuration.transaction.TransactionType;

import com.spice.profitmandi.dao.model.DateRangeModel;

import com.spice.profitmandi.dao.model.StoreTimelineModel;

import com.spice.profitmandi.dao.repository.catalog.AddWalletRequestRepository;

import com.spice.profitmandi.dao.repository.catalog.ManualPaymentRequestRepository;

import com.spice.profitmandi.dao.repository.catalog.UnsettledPaymentsRepository;

import com.spice.profitmandi.dao.repository.dtr.CreditAccountRepository;

import com.spice.profitmandi.dao.repository.dtr.FofoStoreRepository;

import com.spice.profitmandi.dao.repository.dtr.PartnerOnBoardingPanelRepository;

import com.spice.profitmandi.dao.repository.dtr.UserAccountRepository;

import com.spice.profitmandi.dao.repository.transaction.*;

import com.spice.profitmandi.dao.repository.user.FullStockPaymentRepository;

import com.spice.profitmandi.dao.repository.user.LoiFormRepository;

import com.spice.profitmandi.dao.service.SidbiService;

import com.spice.profitmandi.service.NotificationService;

import com.spice.profitmandi.service.authentication.RoleManager;

import com.spice.profitmandi.service.transaction.TransactionService;

import com.spice.profitmandi.service.user.RetailerService;

import com.spice.profitmandi.service.user.StoreTimelineTatService;

import com.spice.profitmandi.service.wallet.WalletService;

import com.spice.profitmandi.web.model.LoginDetails;

import com.spice.profitmandi.web.util.CookiesProcessor;

import com.spice.profitmandi.web.util.MVCResponseSender;

import in.shop2020.model.v1.order.WalletReferenceType;

import org.apache.logging.log4j.LogManager;

import org.apache.logging.log4j.Logger;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.CreationHelper;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.core.io.InputStreamResource;

import org.springframework.http.HttpHeaders;

import org.springframework.http.HttpStatus;

import org.springframework.http.ResponseEntity;

import org.springframework.mail.javamail.JavaMailSender;

import org.springframework.mail.javamail.MimeMessageHelper;

import org.springframework.stereotype.Controller;

import org.springframework.transaction.annotation.Transactional;

import org.springframework.ui.Model;

import org.springframework.web.bind.annotation.*;

import org.springframework.web.multipart.MultipartFile;

import javax.mail.internet.InternetAddress;

import javax.mail.internet.MimeMessage;

import javax.servlet.http.HttpServletRequest;

import javax.swing.*;

import java.io.ByteArrayInputStream;

import java.io.ByteArrayOutputStream;

import java.io.InputStream;

import java.math.BigDecimal;

import java.text.MessageFormat;

import java.time.*;

import java.util.*;

import java.util.stream.Collectors;

import static com.spice.profitmandi.dao.enumuration.transaction.AddWalletRequestStatus.approved;

@Controller

@Transactional(rollbackFor = Throwable.class)

public class WalletController {

    private static final int IDX_PERIOD = 1;

    private static final int OPENING_BALANCE = 2;

    private static final int ACTIVATION_SCHEME = 4;

    private static final int ADVANCE_AMOUNT = 5;

    private static final int AUTOMATED_ADVANCE = 6;

    private static final int BRAND_PAYOUT = 7;

    private static final int INVESTMENT_PAYOUT = 8;

    private static final int OTHERS = 9;

    private static final int PREBOOKING_ORDER = 10;

    private static final int PRICE_DROP = 11;

    private static final int PURCHASE = 12;

    private static final int PURCHASE_BILLED = 13;

    private static final int PURCHASE_PENDING_BILLING = 14;

    private static final int PURCHASE_CANCELLED = 15;

    private static final int RECHARGE = 16;

    private static final int REFUND = 17;

    private static final int SCHEME_IN = 18;

    private static final int SCHEME_OUT = 19;

    private static final Map<WalletReferenceType, Integer> walletReferenceMap = new HashMap<>();

    private static final int GRAND_TOTAL = 20;

    private static final int CLOSING_BALANCE = 22;

    private static final int PENDING_GRN = 23;

    private static final int ACTIVATED_IMEIS = 24;

    private static final Logger LOGGER = LogManager.getLogger(WalletController.class);

    static {

        walletReferenceMap.put(WalletReferenceType.ACTIVATION_SCHEME, ACTIVATION_SCHEME);

        walletReferenceMap.put(WalletReferenceType.ADVANCE_AMOUNT, ADVANCE_AMOUNT);

        walletReferenceMap.put(WalletReferenceType.AUTOMATED_ADVANCE, AUTOMATED_ADVANCE);

        walletReferenceMap.put(WalletReferenceType.BRAND_PAYOUT, BRAND_PAYOUT);

        walletReferenceMap.put(WalletReferenceType.INVESTMENT_PAYOUT, INVESTMENT_PAYOUT);

        walletReferenceMap.put(WalletReferenceType.OTHERS, OTHERS);

        walletReferenceMap.put(WalletReferenceType.PREBOOKING_ORDER, PREBOOKING_ORDER);

        walletReferenceMap.put(WalletReferenceType.PRICE_DROP, PRICE_DROP);

        walletReferenceMap.put(WalletReferenceType.PURCHASE, PURCHASE);

        walletReferenceMap.put(WalletReferenceType.RECHARGE, RECHARGE);

        walletReferenceMap.put(WalletReferenceType.REFUND, REFUND);

        walletReferenceMap.put(WalletReferenceType.SCHEME_IN, SCHEME_IN);

        walletReferenceMap.put(WalletReferenceType.SCHEME_OUT, SCHEME_OUT);

    }

    @Autowired

    JavaMailSender gmailRelaySender;

    @Autowired

    com.spice.profitmandi.service.transaction.SDCreditService sdCreditService;

    @Autowired

    AddWalletRequestRepository addWalletRequestRepository;

    @Autowired

    TransactionService transactionService;

    @Autowired

    private CookiesProcessor cookiesProcessor;

    @Autowired

    private WalletService walletService;

    @Autowired

    private UserWalletRepository userWalletRepository;

    @Autowired

    private UserWalletHistoryRepository userWalletHistoryRepository;

    @Autowired

    private MVCResponseSender mvcResponseSender;

    @Autowired

    private UserAccountRepository userAccountRepository;

    @Autowired

    private OrderRepository orderRepository;

    @Autowired

    private UnsettledPaymentsRepository unsettledPaymentsRepository;

    @Autowired

    private RetailerService retailerService;

    @Autowired

    private RoleManager roleManager;

    @Autowired

    private FofoStoreRepository fofoStoreRepository;

    @Autowired

    private ManualPaymentRequestRepository manualPaymentRequestRepository;

    @Autowired

    private CreditAccountRepository creditAccountRepository;

    @Autowired

    private NotificationService notificationService;

    @Autowired

    LoanRepository loanRepository;

    @Autowired

    private LoanStatementRepository loanStatementRepository;

    @Autowired

    private ReturnOrderInfoRepository returnOrderInfoRepository;

    @Autowired

    SDCreditRequirementRepository sdCreditRequirementRepository;

    @Autowired

    LoiFormRepository loiFormRepository;

    @Autowired

    PartnerOnBoardingPanelRepository partnerOnBoardingPanelRepository;

    @Autowired

    StoreTimelineTatService storeTimelineTatService;

    @Autowired

    FullStockPaymentRepository fullStockPaymentRepository;

    @Autowired

    com.spice.profitmandi.dao.repository.auth.AuthRepository authRepository;

    @Autowired

    com.spice.profitmandi.dao.repository.cs.PositionRepository positionRepository;

    @PostMapping(value = "/wallet/upload")

    public String uploadWalletBulk(HttpServletRequest request, @RequestPart("file") MultipartFile file, Model model) throws Exception {

        List<WalletHistoryModel> walletHistoryModelList = ExcelUtils.parseWalletBulkCredit(file.getInputStream());

        for (WalletHistoryModel walletHistoryModel : walletHistoryModelList) {

            if (walletHistoryModel.getReference() == 0) {

                ManualPaymentType paymentType = manualPaymentRequestRepository.selectByReferenceType(walletHistoryModel.getWalletReferenceType());

                if (paymentType == null) {

                    paymentType = new ManualPaymentType();

                    paymentType.setReferenceType(walletHistoryModel.getWalletReferenceType());

                    manualPaymentRequestRepository.persist(paymentType);

                }

                paymentType.setCounter(paymentType.getCounter() + 1);

                int reference = paymentType.getCounter();

                walletService.addAmountToWallet(walletHistoryModel.getFofoId(), reference, walletHistoryModel.getWalletReferenceType(), walletHistoryModel.getDescription(), (float) walletHistoryModel.getAmount(), walletHistoryModel.getBusinessDate());

            } else {

                walletService.addAmountToWallet(walletHistoryModel.getFofoId(), walletHistoryModel.getReference(), walletHistoryModel.getWalletReferenceType(), walletHistoryModel.getDescription(), (float) walletHistoryModel.getAmount(), walletHistoryModel.getBusinessDate());

            }

        }

        model.addAttribute("response1", mvcResponseSender.createResponseString(true));

        return "response";

    }

    @RequestMapping(value = "/walletDetails", method = RequestMethod.GET)

    public String dashboard(HttpServletRequest request, @RequestParam(name = ProfitMandiConstants.START_TIME, required = false) String startTimeString, @RequestParam(name = ProfitMandiConstants.END_TIME, required = false) String endTimeString, @RequestParam(name = "offset", defaultValue = "0") int offset, @RequestParam(name = "limit", defaultValue = "100") int limit, Model model) throws ProfitMandiBusinessException {

        /*

         * boolean underMaintainance = true; if(underMaintainance) { throw new

         * ProfitMandiBusinessException("Wallet", "Wallet",

         * "Wallet is under Maintenance"); }

         */

        LoginDetails fofoDetails = cookiesProcessor.getCookiesObject(request);

        int fofoId = fofoDetails.getFofoId();

        boolean isAdmin = roleManager.isAdmin(fofoDetails.getRoleIds());

        UserWallet userWallet = walletService.getUserWallet(fofoId);

        LocalDateTime startDateTime = StringUtils.toDateTime(startTimeString);

        LocalDateTime endDateTime = StringUtils.toDateTime(endTimeString);

        List<UserWalletHistory> userWalletHistories = new ArrayList<>();

        try {

            userWalletHistories = walletService.getPaginatedUserWalletHistoryByRetailerId(fofoId, startDateTime, endDateTime, offset, limit);

        } catch (ProfitMandiBusinessException profitMandiBusinessException) {

            LOGGER.error("UserWallet History not found : ", profitMandiBusinessException);

        }

        long countItems = 0;

        try {

            countItems = walletService.getSizeByRetailerId(fofoDetails.getFofoId(), startDateTime, endDateTime);

        } catch (ProfitMandiBusinessException profitMandiBusinessException) {

            LOGGER.error("UserWallet not found : ", profitMandiBusinessException);

        }

        com.spice.profitmandi.dao.model.CreditSummary creditSummary = sdCreditService.getCreditSummary(fofoId);

        model.addAttribute("userWallet", userWallet);

        model.addAttribute("walletHistories", userWalletHistories);

        model.addAttribute("walletAttachmentMap", buildWalletHistoryAttachmentMap(userWalletHistories));

        model.addAttribute("start", offset + 1);

        model.addAttribute("size", countItems);

        model.addAttribute("isAdmin", isAdmin);

        model.addAttribute("totalDue", creditSummary.getTotalDue());

        model.addAttribute("utilization", creditSummary.getUtilization());

        model.addAttribute(ProfitMandiConstants.START_TIME, startTimeString);

        model.addAttribute(ProfitMandiConstants.END_TIME, endTimeString);

        if (userWalletHistories.size() < limit) {

            model.addAttribute("end", offset + userWalletHistories.size());

        } else {

            model.addAttribute("end", offset + limit);

        }

        return "wallet-details";

    }

    @RequestMapping(value = "/getPaginatedWalletHistory")

    public String getSaleHistoryPaginated(HttpServletRequest request, @RequestParam(name = ProfitMandiConstants.START_TIME, required = false) String startTimeString, @RequestParam(name = ProfitMandiConstants.END_TIME, required = false) String endTimeString, @RequestParam(name = "offset", defaultValue = "0") int offset, @RequestParam(name = "limit", defaultValue = "10") int limit, Model model) throws ProfitMandiBusinessException {

        LoginDetails loginDetails = cookiesProcessor.getCookiesObject(request);

        LocalDateTime startDateTime = StringUtils.toDateTime(startTimeString);

        LocalDateTime endDateTime = StringUtils.toDateTime(endTimeString);

        List<UserWalletHistory> userWalletHistories = new ArrayList<>();

        try {

            userWalletHistories = walletService.getPaginatedUserWalletHistoryByRetailerId(loginDetails.getFofoId(), startDateTime, endDateTime, offset, limit);

        } catch (ProfitMandiBusinessException profitMandiBusinessException) {

            LOGGER.error("UserWallet History not found : ", profitMandiBusinessException);

        }

        model.addAttribute("walletHistories", userWalletHistories);

        model.addAttribute("walletAttachmentMap", buildWalletHistoryAttachmentMap(userWalletHistories));

        return "wallet-history-paginated";

    }

    @RequestMapping(value = "/getAddWalletRequest", method = RequestMethod.GET)

    public String getAddwalletRequest(HttpServletRequest request, @RequestParam(name = "offset", defaultValue = "0") int offset, @RequestParam(name = "limit", defaultValue = "100") int limit, Model model) throws Exception {

        List<AddWalletRequest> walletRequest = null;

        long size = 0;

        walletRequest = addWalletRequestRepository.selectAllByStatus(offset, limit, AddWalletRequestStatus.pending);

        LOGGER.info("walletRequest" + walletRequest);

        size = addWalletRequestRepository.selectCountByStatus(AddWalletRequestStatus.pending);

        if (!walletRequest.isEmpty()) {

            List<Integer> fofoIds = this.getFofoIdsFromWalletRequest(walletRequest);

            Map<Integer, CustomRetailer> customRetailerMap = retailerService.getAllFofoRetailers();

            Map<Integer, CustomRetailer> fofoIdsAndRetailerName = fofoIds.stream().map(x -> customRetailerMap.get(x)).filter(x -> x != null).collect(Collectors.toMap(x -> x.getPartnerId(), x -> x));

            model.addAttribute("fofoIdsAndRetailerName", fofoIdsAndRetailerName);

            model.addAttribute("walletRequest", walletRequest);

            model.addAttribute("fspAttachmentMap", buildFspAttachmentMap(walletRequest));

            model.addAttribute("rStatus", "pending");

            model.addAttribute("start", offset + 1);

            model.addAttribute("size", size);

            model.addAttribute("url", "/getPaginatedWalletRequest");

            if (walletRequest.size() < limit) {

                model.addAttribute("end", offset + walletRequest.size());

            } else {

                model.addAttribute("end", offset + limit);

            }

        } else {

            model.addAttribute("walletRequest", walletRequest);

            model.addAttribute("size", size);

        }

        return "add-wallet-request";

    }

    @RequestMapping(value = "/getPaginatedWalletRequest", method = RequestMethod.GET)

    public String getPaginatedWalletRequest(HttpServletRequest request, @RequestParam(name = "offset", defaultValue = "0") int offset, @RequestParam(name = "limit", defaultValue = "10") int limit, Model model) throws Exception {

        LOGGER.info("requested offset=[{}], limit = [{}]", offset, limit);

        List<AddWalletRequest> walletRequest = null;

        walletRequest = addWalletRequestRepository.selectAllByStatus(offset, limit, AddWalletRequestStatus.pending);

        LOGGER.info("walletRequest" + walletRequest);

        if (!walletRequest.isEmpty()) {

            List<Integer> fofoIds = this.getFofoIdsFromWalletRequest(walletRequest);

            Map<Integer, CustomRetailer> customRetailerMap = retailerService.getAllFofoRetailers();

            Map<Integer, CustomRetailer> fofoIdsAndRetailerName = fofoIds.stream().map(x -> customRetailerMap.get(x)).filter(x -> x != null).collect(Collectors.toList()).stream().collect(Collectors.toMap(x -> x.getPartnerId(), x -> x));

            model.addAttribute("fofoIdsAndRetailerName", fofoIdsAndRetailerName);

            model.addAttribute("walletRequest", walletRequest);

            model.addAttribute("fspAttachmentMap", buildFspAttachmentMap(walletRequest));

            model.addAttribute("rStatus", "pending");

            model.addAttribute("url", "/getPaginatedWalletRequest");

        } else {

            model.addAttribute("walletRequest", walletRequest);

        }

        return "add-wallet-request-paginated";

    }

    @RequestMapping(value = "/getAddWalletApproved", method = RequestMethod.GET)

    public String walletAddnAccept(HttpServletRequest request, Model model) throws Exception {

        return "add-wallet-req";

    }

    @RequestMapping(value = "/getAddWalletApprovedByDate", method = RequestMethod.GET)

    public String getAddwalletRequestApproved(HttpServletRequest request, @RequestParam(name = "offset", defaultValue = "0") int offset, @RequestParam(name = "limit", defaultValue = "10") int limit, @RequestParam(name = "startDate") LocalDateTime startDate,

                                              @RequestParam(name = "endDate") LocalDateTime endDate, Model model) throws ProfitMandiBusinessException {

        LOGGER.info("requested startDate=[{}], EndDate = [{}]", startDate, endDate);

        List<AddWalletRequest> walletRequest = null;

        long size = 0;

        walletRequest = addWalletRequestRepository.selectAllRetailerIdByDateAndStatus(startDate, endDate, AddWalletRequestStatus.approved);

//        walletRequest = addWalletRequestRepository.selectAllByStatus(offset, limit, approved);

        size = addWalletRequestRepository.selectCountByStatus(approved);

        if (!walletRequest.isEmpty()) {

            List<Integer> fofoIds = this.getFofoIdsFromWalletRequest(walletRequest);

            Map<Integer, CustomRetailer> customRetailerMap = retailerService.getAllFofoRetailers();

            Map<Integer, CustomRetailer> fofoIdsAndRetailerName = fofoIds.stream().map(x -> customRetailerMap.get(x)).filter(x -> x != null).collect(Collectors.toList()).stream().collect(Collectors.toMap(x -> x.getPartnerId(), x -> x));

            model.addAttribute("fofoIdsAndRetailerName", fofoIdsAndRetailerName);

            model.addAttribute("walletRequest", walletRequest);

            model.addAttribute("fspAttachmentMap", buildFspAttachmentMap(walletRequest));

            model.addAttribute("start", offset + 1);

            model.addAttribute("size", size);

            model.addAttribute("url", "/getPaginatedWalletApproved");

            if (walletRequest.size() < limit) {

                model.addAttribute("end", offset + walletRequest.size());

            } else {

                model.addAttribute("end", offset + limit);

            }

        } else {

            model.addAttribute("walletRequest", walletRequest);

            model.addAttribute("size", size);

        }

        return "add-wallet-request-paginated";

    }

    @RequestMapping(value = "/getPaginatedWalletApproved", method = RequestMethod.GET)

    public String getPaginatedWalletApproved(HttpServletRequest request, @RequestParam(name = "offset", defaultValue = "0") int offset, @RequestParam(name = "limit", defaultValue = "10") int limit, @RequestParam(name = "startDate") LocalDateTime startDate,

                                             @RequestParam(name = "endDate") LocalDateTime endDate, Model model) throws ProfitMandiBusinessException {

        LOGGER.info("requested offset=[{}], limit = [{}]", offset, limit);

        List<AddWalletRequest> walletRequest = null;

        long size = 0;

        walletRequest = addWalletRequestRepository.selectAllRetailerIdByDateAndStatus(startDate, endDate, AddWalletRequestStatus.approved);

//        walletRequest = addWalletRequestRepository.selectAllByStatus(offset, limit, AddWalletRequestStatus.approved);

        LOGGER.info("walletRequest" + walletRequest);

        if (!walletRequest.isEmpty()) {

            List<Integer> fofoIds = this.getFofoIdsFromWalletRequest(walletRequest);

            Map<Integer, CustomRetailer> customRetailerMap = retailerService.getAllFofoRetailers();

            Map<Integer, CustomRetailer> fofoIdsAndRetailerName = fofoIds.stream().map(x -> customRetailerMap.get(x)).filter(x -> x != null).collect(Collectors.toList()).stream().collect(Collectors.toMap(x -> x.getPartnerId(), x -> x));

            model.addAttribute("fofoIdsAndRetailerName", fofoIdsAndRetailerName);

            model.addAttribute("walletRequest", walletRequest);

            model.addAttribute("fspAttachmentMap", buildFspAttachmentMap(walletRequest));

            model.addAttribute("url", "/getPaginatedWalletApproved");

        } else {

            model.addAttribute("walletRequest", walletRequest);

        }

        return "add-wallet-request-paginated";

    }

    @RequestMapping(value = "/getAddWalletRequestRejected", method = RequestMethod.GET)

    public String walletAddnReject(HttpServletRequest request, Model model) throws Exception {

        return "add-wallet-rejected";

    }

    @RequestMapping(value = "/getAddWalletRequestRejectedByDate", method = RequestMethod.GET)

    public String getAddwalletRequestRejected(HttpServletRequest request, @RequestParam(name = "offset", defaultValue = "0") int offset, @RequestParam(name = "limit", defaultValue = "10") int limit, @RequestParam(name = "startDate") LocalDateTime startDate,

                                              @RequestParam(name = "endDate") LocalDateTime endDate, Model model) throws ProfitMandiBusinessException {

        List<AddWalletRequest> walletRequest = null;

        long size = 0;

        walletRequest = addWalletRequestRepository.selectAllRetailerIdByDateAndStatus(startDate, endDate, AddWalletRequestStatus.rejected);

        size = addWalletRequestRepository.selectCountByStatus(AddWalletRequestStatus.rejected);

        LOGGER.info("walletRequest" + walletRequest);

        if (!walletRequest.isEmpty()) {

            List<Integer> fofoIds = this.getFofoIdsFromWalletRequest(walletRequest);

            Map<Integer, CustomRetailer> customRetailerMap = retailerService.getAllFofoRetailers();

            Map<Integer, CustomRetailer> fofoIdsAndRetailerName = fofoIds.stream().map(x -> customRetailerMap.get(x)).filter(x -> x != null).collect(Collectors.toList()).stream().collect(Collectors.toMap(x -> x.getPartnerId(), x -> x));

            model.addAttribute("fofoIdsAndRetailerName", fofoIdsAndRetailerName);

            model.addAttribute("walletRequest", walletRequest);

            model.addAttribute("fspAttachmentMap", buildFspAttachmentMap(walletRequest));

            model.addAttribute("start", offset + 1);

            model.addAttribute("size", size);

            model.addAttribute("url", "/getPaginatedWalletRequestRejected");

            if (walletRequest.size() < limit) {

                model.addAttribute("end", offset + walletRequest.size());

            } else {

                model.addAttribute("end", offset + limit);

            }

        } else {

            model.addAttribute("walletRequest", walletRequest);

            model.addAttribute("size", size);

        }

        return "add-wallet-request-paginated";

    }

    @RequestMapping(value = "/getPaginatedWalletRequestRejected", method = RequestMethod.GET)

    public String getPaginatedWalletRequestRejected(HttpServletRequest request, @RequestParam(name = "offset", defaultValue = "0") int offset, @RequestParam(name = "limit", defaultValue = "10") int limit, @RequestParam(name = "startDate") LocalDateTime startDate,

                                                    @RequestParam(name = "endDate") LocalDateTime endDate, Model model) throws ProfitMandiBusinessException {

        LOGGER.info("requested offset=[{}], limit = [{}]", offset, limit);

        List<AddWalletRequest> walletRequest = null;

        walletRequest = addWalletRequestRepository.selectAllRetailerIdByDateAndStatus(startDate, endDate, AddWalletRequestStatus.rejected);

        LOGGER.info("walletRequest" + walletRequest);

        if (!walletRequest.isEmpty()) {

            List<Integer> fofoIds = this.getFofoIdsFromWalletRequest(walletRequest);

            Map<Integer, CustomRetailer> customRetailerMap = retailerService.getAllFofoRetailers();

            Map<Integer, CustomRetailer> fofoIdsAndRetailerName = fofoIds.stream().map(x -> customRetailerMap.get(x)).filter(x -> x != null).collect(Collectors.toList()).stream().collect(Collectors.toMap(x -> x.getPartnerId(), x -> x));

            model.addAttribute("fofoIdsAndRetailerName", fofoIdsAndRetailerName);

            model.addAttribute("walletRequest", walletRequest);

            model.addAttribute("fspAttachmentMap", buildFspAttachmentMap(walletRequest));

            model.addAttribute("url", "/getPaginatedWalletRequestRejected");

        } else {

            model.addAttribute("walletRequest", walletRequest);

        }

        return "add-wallet-request-paginated";

    }

    @Autowired

    HdfcPaymentRepository hdfcPaymentRepository;

    @RequestMapping(value = "/addAmountToWallet", method = RequestMethod.PUT)

    public String addAmountToWallet(HttpServletRequest request, @RequestParam(name = "id", defaultValue = "0") int id, @RequestParam(name = "walletRequestid", defaultValue = "0") int walletRequestid, Model model) throws Exception {

        AddWalletRequest addWalletRequest = addWalletRequestRepository.selectById(walletRequestid);

        List<AddWalletRequest> requests = addWalletRequestRepository.selectAllByTransactionReference(addWalletRequest.getTransaction_reference());

        // Scope the duplicate-reference check to the SAME retailer. Reference numbers

        // (e.g. cheque numbers like "000310") are not globally unique and collide across

        // partners; without this filter a legitimate payment is wrongly blocked as

        // "Entry already Approved Please reject" because a different store used the same ref.

        if (requests.stream().filter(x -> x.getId() != addWalletRequest.getId()).filter(x -> x.getRetailerId() == addWalletRequest.getRetailerId()).filter(x -> x.getStatus().equals(approved)).count() != 1) {

            HdfcPayment hdfcPayment = hdfcPaymentRepository.selectByUtrNo(addWalletRequest.getTransaction_reference());

            // Block only when the UTR was actually auto-credited to a wallet. Since r36927,

            // hdfc_payment also holds merely-captured rows (VA missing/unmatched) that never

            // credited — those MUST be approvable here, so guard on the credited flag, not

            // mere row existence.

            if (hdfcPayment != null && hdfcPayment.isCredited()) {

                String rejectReason = "Payment for UTR Already added via Auto Update";

                throw new ProfitMandiBusinessException(rejectReason, rejectReason, rejectReason);

            }

        } else {

            String rejectReason = "Entry already Approved Please reject";

            throw new ProfitMandiBusinessException(rejectReason, rejectReason, rejectReason);

        }

        // FSP wallet addons can only be approved by Account team L3

        if (addWalletRequest.getFspId() > 0) {

            LoginDetails loginDetails = cookiesProcessor.getCookiesObject(request);

            String approverEmail = loginDetails.getEmailId();

            com.spice.profitmandi.dao.entity.auth.AuthUser approverUser = authRepository.selectByEmailOrMobile(approverEmail);

            boolean isAccountL3 = false;

            if (approverUser != null) {

                List<com.spice.profitmandi.dao.entity.cs.Position> positions = positionRepository.selectPositionByAuthId(approverUser.getId());

                for (com.spice.profitmandi.dao.entity.cs.Position position : positions) {

                    if (position.getEscalationType().equals(com.spice.profitmandi.dao.enumuration.cs.EscalationType.L3)

                            && position.getCategoryId() == ProfitMandiConstants.TICKET_CATEGORY_ACCOUNTS) {

                        isAccountL3 = true;

                        break;

                    }

                }

            }

            if (!isAccountL3) {

                throw new ProfitMandiBusinessException(

                        "Only Account team L3 can approve Full Stock Payment entries",

                        approverEmail,

                        "You are not authorized to approve FSP wallet requests");

            }

        }

        if (addWalletRequest.getStatus().equals(AddWalletRequestStatus.pending)) {

            walletService.addAmountToWallet(addWalletRequest.getRetailerId(), walletRequestid, WalletReferenceType.ADVANCE_AMOUNT, "ntfs/rgfs", addWalletRequest.getAmount(), addWalletRequest.getCreateTimestamp());

            addWalletRequest.setStatus(AddWalletRequestStatus.approved);

            addWalletRequest.setUpdateTimestamp(LocalDateTime.now());

            // If this manual top-up recovers a captured HDFC payment (same UTR), mark that row

            // credited so the flag means "credited by any path" — prevents a later duplicate

            // approval or re-pushed HDFC credit from topping up the same UTR again. Managed

            // entity, so the flag flushes as an UPDATE at commit.

            HdfcPayment recoveredHdfcPayment = hdfcPaymentRepository.selectByUtrNo(addWalletRequest.getTransaction_reference());

            if (recoveredHdfcPayment != null) {

                recoveredHdfcPayment.setCredited(true);

            }

            unsettledPaymentsRepository.deleteById(id);

            // check full stock payment if partner comes from Loi process

            String code = fofoStoreRepository.selectByRetailerId(addWalletRequest.getRetailerId()).getCode();

            StoreTimelineModel storeTimelineModel = partnerOnBoardingPanelRepository.selectStoreTimeLine(code);

            if (storeTimelineModel != null && storeTimelineModel.getFullStockTimestamp() == null) {

                UserWallet userWallet = userWalletRepository.selectByRetailerId(addWalletRequest.getRetailerId());

                List<UserWalletHistory> userWalletHistories = userWalletHistoryRepository.selectByWalletIdAndReferenceType(userWallet.getId(), WalletReferenceType.ADVANCE_AMOUNT);

                long totalAdvanceAmount = userWalletHistories.stream().mapToLong(x -> x.getAmount()).sum();

                LOGGER.info("totalAdvanceAmount - " + totalAdvanceAmount);

                if (totalAdvanceAmount >= ProfitMandiConstants.FSP_DIRECT_WALLET_THRESHOLD) {

                    storeTimelineTatService.onFullPaymentReceived(storeTimelineModel.getOnboardingId());

                }

                // Mark linked FullStockPayment record as APPROVED via fspId

                if (addWalletRequest.getFspId() > 0) {

                    FullStockPayment fsp = fullStockPaymentRepository.selectById(addWalletRequest.getFspId());

                    if (fsp != null && fsp.getPaymentStatus() == FullStockPaymentStatus.PENDING) {

                        fsp.setPaymentStatus(FullStockPaymentStatus.APPROVED);

                        fsp.setApproveTimestamp(LocalDateTime.now());

                        fullStockPaymentRepository.persist(fsp);

                        LOGGER.info("Marked FSP id={} as APPROVED via wallet addon approval, onboardingId={}", fsp.getId(), storeTimelineModel.getOnboardingId());

                    }

                }

                // Auto-mark FULL_STOCK_PAYMENT in timeline when total approved FSP amount >= 7 lakh

                double totalApprovedFsp = fullStockPaymentRepository.selectByOnboardingId(storeTimelineModel.getOnboardingId())

                        .stream()

                        .filter(p -> p.getPaymentStatus() == FullStockPaymentStatus.APPROVED)

                        .mapToDouble(FullStockPayment::getCollectedAmount)

                        .sum();

                LOGGER.info("totalApprovedFsp={} for onboardingId={}", totalApprovedFsp, storeTimelineModel.getOnboardingId());

                if (totalApprovedFsp >= ProfitMandiConstants.FSP_DIRECT_WALLET_THRESHOLD) {

                    storeTimelineTatService.onFullPaymentReceived(storeTimelineModel.getOnboardingId());

                    LOGGER.info("Auto-marked FULL_STOCK_PAYMENT done for onboardingId={}, totalApprovedFsp={}", storeTimelineModel.getOnboardingId(), totalApprovedFsp);

                    try {

                        storeTimelineTatService.sendFullStockPaymentPOEmail(storeTimelineModel.getOnboardingId());

                    } catch (Exception ex) {

                        LOGGER.error("Failed to send FSP PO email after approved FSP >= 7L for onboardingId=" + storeTimelineModel.getOnboardingId(), ex);

                    }

                }

            }

            model.addAttribute("response1", mvcResponseSender.createResponseString(true));

            CustomRetailer customRetailer = retailerService.getFofoRetailer(addWalletRequest.getRetailerId());

            String subject = "Request Approved for " + customRetailer.getBusinessName() + " of Rs." + addWalletRequest.getAmount();

            String messageText = MessageFormat.format("User Id - {0}\n Name -{1}\n Email -{2}\n mobile -{3}\n Reference - {4}\n Amount - Rs.{5}", new Integer(addWalletRequest.getRetailerId()), customRetailer.getBusinessName(), customRetailer.getEmail(), customRetailer.getMobileNumber(), addWalletRequest.getTransaction_reference(), new Float(addWalletRequest.getAmount()));

            //	this.sendMailWithAttachments(subject, messageText);

            return "response";

        } else {

            model.addAttribute("response1", mvcResponseSender.createResponseString(false));

            return "response";

        }

    }

    @RequestMapping(value = "/addAmountToWalletRequestRejected", method = RequestMethod.PUT)

    public String addAmountToWalletRequestRejected(HttpServletRequest request, @RequestParam(name = "id", defaultValue = "0") int id, Model model) throws Exception {

        AddWalletRequest addWalletRequest = addWalletRequestRepository.selectById(id);

        if (addWalletRequest.getStatus().equals(AddWalletRequestStatus.pending)) {

            addWalletRequest.setStatus(AddWalletRequestStatus.rejected);

            addWalletRequest.setUpdateTimestamp(LocalDateTime.now());

            addWalletRequestRepository.persist(addWalletRequest);

            // Mark linked FSP as REJECTED via fspId

            if (addWalletRequest.getFspId() > 0) {

                try {

                    FullStockPayment fsp = fullStockPaymentRepository.selectById(addWalletRequest.getFspId());

                    if (fsp != null && fsp.getPaymentStatus() == FullStockPaymentStatus.PENDING) {

                        fsp.setPaymentStatus(FullStockPaymentStatus.REJECTED);

                        fsp.setDescription("Wallet addon request rejected");

                        fsp.setApproveTimestamp(LocalDateTime.now());

                        fullStockPaymentRepository.persist(fsp);

                        LOGGER.info("Marked FSP id={} as REJECTED via wallet addon rejection", fsp.getId());

                    }

                } catch (Exception e) {

                    LOGGER.error("Error marking FSP as REJECTED on wallet addon rejection", e);

                }

            }

            model.addAttribute("response1", mvcResponseSender.createResponseString(true));

            CustomRetailer customRetailer = retailerService.getFofoRetailer(addWalletRequest.getRetailerId());

            String subject = "Request Rejected for " + customRetailer.getBusinessName() + " of Rs." + addWalletRequest.getAmount();

            String messageText = MessageFormat.format("User Id - {0}\n Name -{1}\n Email -{2}\n mobile -{3}\n Reference - {4}\n Amount - Rs.{5}", new Integer(addWalletRequest.getRetailerId()), customRetailer.getBusinessName(), customRetailer.getEmail(), customRetailer.getMobileNumber(), addWalletRequest.getTransaction_reference(), new Float(addWalletRequest.getAmount()));

            //this.sendMailWithAttachments(subject, messageText);

            return "response";

        } else {

            model.addAttribute("response1", mvcResponseSender.createResponseString(false));

            return "response";

        }

    }

    private void sendMailWithAttachments(String subject, String messageText) throws Exception {

        MimeMessage message = gmailRelaySender.createMimeMessage();

        MimeMessageHelper helper = new MimeMessageHelper(message, true);

        String[] email = {"neerajgupta2021@gmail.com", "adeel.yazdani@smartdukaan.com", "kamini.sharma@smartdukaan.com", "care@smartdukaan.com", "mohinder.mutreja@smartdukaan.com"};

        helper.setSubject(subject);

        helper.setText(messageText);

        helper.setTo(email);

        InternetAddress senderAddress = new InternetAddress("noreply@smartdukaan.com", "Smartdukaan Alerts");

        helper.setFrom(senderAddress);

        gmailRelaySender.send(message);

    }

    private List<Integer> getFofoIdsFromWalletRequest(List<AddWalletRequest> walletRequest) {

        return walletRequest.stream().map(x -> x.getRetailerId()).distinct().collect(Collectors.toList());

    }

    /**

     * Builds a map of AddWalletRequest.id -> FullStockPayment.attachment for requests linked to FSP.

     */

    private Map<Integer, Integer> buildFspAttachmentMap(List<AddWalletRequest> walletRequests) {

        Map<Integer, Integer> attachmentMap = new HashMap<>();

        List<Integer> fspIds = walletRequests.stream()

                .filter(r -> r.getFspId() > 0)

                .map(AddWalletRequest::getFspId)

                .distinct()

                .collect(Collectors.toList());

        if (fspIds.isEmpty()) {

            return attachmentMap;

        }

        List<FullStockPayment> fspList = fullStockPaymentRepository.selectByIds(fspIds);

        Map<Integer, Integer> fspIdToAttachment = fspList.stream()

                .filter(f -> f.getAttachment() > 0)

                .collect(Collectors.toMap(FullStockPayment::getId, FullStockPayment::getAttachment));

        for (AddWalletRequest r : walletRequests) {

            if (r.getFspId() > 0 && fspIdToAttachment.containsKey(r.getFspId())) {

                attachmentMap.put(r.getId(), fspIdToAttachment.get(r.getFspId()));

            }

        }

        return attachmentMap;

    }

    /**

     * Builds a map of UserWalletHistory.id -> FullStockPayment.attachment for ADVANCE_AMOUNT entries linked to FSP.

     */

    private Map<Integer, Integer> buildWalletHistoryAttachmentMap(List<UserWalletHistory> histories) {

        Map<Integer, Integer> attachmentMap = new HashMap<>();

        List<Integer> awrIds = histories.stream()

                .filter(h -> h.getReferenceType() == WalletReferenceType.ADVANCE_AMOUNT && h.getReference() > 0)

                .map(UserWalletHistory::getReference)

                .distinct()

                .collect(Collectors.toList());

        if (awrIds.isEmpty()) {

            return attachmentMap;

        }

        List<AddWalletRequest> awrList = addWalletRequestRepository.selectByIds(awrIds);

        Map<Integer, Integer> awrIdToFspId = awrList.stream()

                .filter(r -> r.getFspId() > 0)

                .collect(Collectors.toMap(AddWalletRequest::getId, AddWalletRequest::getFspId));

        List<Integer> fspIds = awrIdToFspId.values().stream().distinct().collect(Collectors.toList());

        if (fspIds.isEmpty()) {

            return attachmentMap;

        }

        List<FullStockPayment> fspList = fullStockPaymentRepository.selectByIds(fspIds);

        Map<Integer, Integer> fspIdToAttachment = fspList.stream()

                .filter(f -> f.getAttachment() > 0)

                .collect(Collectors.toMap(FullStockPayment::getId, FullStockPayment::getAttachment));

        for (UserWalletHistory h : histories) {

            if (h.getReferenceType() == WalletReferenceType.ADVANCE_AMOUNT && h.getReference() > 0) {

                Integer fspId = awrIdToFspId.get(h.getReference());

                if (fspId != null && fspIdToAttachment.containsKey(fspId)) {

                    attachmentMap.put(h.getId(), fspIdToAttachment.get(fspId));

                }

            }

        }

        return attachmentMap;

    }

    @RequestMapping(value = "/getcreateUnsettledPayments", method = RequestMethod.GET)

    public String getcreateUnsettledPayment(HttpServletRequest request, @RequestParam(name = "offset", defaultValue = "0") int offset, @RequestParam(name = "limit", defaultValue = "15") int limit, Model model) throws Exception {

        List<UnsettledPayment> up = null;

        long size = 0;

        up = unsettledPaymentsRepository.selectAllById(offset, limit);

        size = unsettledPaymentsRepository.selectAllCount();

        if (!up.isEmpty()) {

            model.addAttribute("unsettledPayment", up);

            model.addAttribute("start", offset + 1);

            model.addAttribute("size", size);

            model.addAttribute("url", "/getPaginatedUnsettledPayments");

            if (up.size() < limit) {

                model.addAttribute("end", offset + up.size());

            } else {

                model.addAttribute("end", offset + limit);

            }

        } else {

            model.addAttribute("unsettledPayment", up);

            model.addAttribute("size", size);

        }

        LOGGER.info("unsettledPaymentList" + up);

        return "unsettled-payments";

    }

    @RequestMapping(value = "/getUnsettledPaymentsByAmount", method = RequestMethod.GET)

    public String getUnsettledPaymentByAmount(HttpServletRequest request, @RequestParam(name = "amount", defaultValue = "0") float amount, @RequestParam(name = "offset", defaultValue = "0") int offset, @RequestParam(name = "limit", defaultValue = "10") int limit, Model model) throws Exception {

        List<UnsettledPayment> up = null;

        long size = 0;

        up = unsettledPaymentsRepository.selectAllByAmount(amount, offset, limit);

        size = unsettledPaymentsRepository.selectAllCount();

        if (!up.isEmpty()) {

            model.addAttribute("unsettledPayment", up);

            model.addAttribute("start", offset + 1);

            model.addAttribute("size", size);

            model.addAttribute("url", "/getPaginatedUnsettledPayments");

            if (up.size() < limit) {

                model.addAttribute("end", offset + up.size());

            } else {

                model.addAttribute("end", offset + limit);

            }

            return "unsettle-payment-modal";

        } else {

            model.addAttribute("unsettledPayment", up);

            model.addAttribute("size", size);

        }

        return "unsettle-payment-modal";

    }

    @RequestMapping(value = "/createUnsettledPaymentsEntries", method = RequestMethod.POST)

    public String createUnsettledPaymentsEntries(HttpServletRequest request, @RequestBody UnsettledPaymentModel unsettledPaymentModel, Model model) throws Exception {

        UnsettledPayment up = new UnsettledPayment();

        up.setTransaction_reference(unsettledPaymentModel.getTransactionReference());

        up.setAmount(unsettledPaymentModel.getAmount());

        up.setDescription(unsettledPaymentModel.getDescription());

        up.setReference_date(unsettledPaymentModel.getReferenceDate());

        LOGGER.info("uppaynments" + up);

        unsettledPaymentsRepository.persist(up);

        model.addAttribute("response1", mvcResponseSender.createResponseString(true));

        return "response";

    }

    @RequestMapping(value = "/removeUnsettledPaymentsEntries", method = RequestMethod.DELETE)

    public String removeUnsettledPaymentsEntries(HttpServletRequest request, @RequestParam(name = "id", defaultValue = "0") int id, Model model) throws Exception {

        unsettledPaymentsRepository.deleteById(id);

        model.addAttribute("response1", mvcResponseSender.createResponseString(true));

        return "response";

    }

    @RequestMapping(value = "/wallet/statement", method = RequestMethod.GET)

    public String getWalletStatement(HttpServletRequest request, @RequestParam LocalDateTime startDate, @RequestParam LocalDateTime endDate, Model model, @RequestParam(defaultValue = "0") int fofoId) throws Exception {

        boolean isAdmin = roleManager.isAdmin(cookiesProcessor.getCookiesObject(request).getRoleIds());

        if (!isAdmin) {

            fofoId = cookiesProcessor.getCookiesObject(request).getFofoId();

        }

        float openingBalance = walletService.getOpeningTill(fofoId, startDate);

        float closingBalance = walletService.getOpeningTill(fofoId, endDate);

        List<UserWalletHistory> uwhList = walletService.getPaginatedUserWalletHistoryByRetailerId(fofoId, startDate, endDate, 0, 0);

        Collections.reverse(uwhList);

        model.addAttribute("opening", openingBalance);

        model.addAttribute("closing", closingBalance);

        model.addAttribute("history", uwhList);

        model.addAttribute("startDate", startDate);

        model.addAttribute("endDate", endDate);

        return "walletStatement";

    }

    @RequestMapping(value = "/account/reco", method = RequestMethod.GET)

    public ResponseEntity<?> accountReco(HttpServletRequest request, @RequestParam LocalDate closingDate, Model model) throws Exception {

        boolean isAdmin = roleManager.isAdmin(cookiesProcessor.getCookiesObject(request).getRoleIds());

        if (!isAdmin) {

            throw new ProfitMandiBusinessException("Unauthorised access", "PartnerId", "Permission Denied");

        }

        LocalDateTime closingDateTime = closingDate.plusDays(1).atStartOfDay();

        Map<Integer, CustomRetailer> customRetailerMap = retailerService.getAllFofoRetailers();

        Set<Integer> retailersSet = customRetailerMap.keySet();

        Map<Integer, UserWallet> retailerWalletMap = walletService.getRetailerIdUserWalletMap(retailersSet);

        Map<Integer, Integer> walletRetailerMap = retailerWalletMap.entrySet().stream().collect(Collectors.toMap(x -> x.getValue().getId(), x -> x.getKey()));

        Set<Integer> walletSet = walletRetailerMap.keySet();

        // Closing uses the SAME model as the individual account statement (/account/statement):

        //   Closing Balance = wallet balance + pending indent (in-transit orders) − loan outstanding (principal + interest)

        Map<Integer, Float> closingWalletMap = userWalletHistoryRepository.getSumTillDate(closingDateTime, walletSet);

        Map<Integer, Float> peindingIndentMap = transactionService.getPendingIndentValueMap(closingDateTime, Optional.empty());

        Map<Integer, Float> loanOutstandingMap = loanStatementRepository.getLoanOutstandingAtDate(retailersSet, closingDateTime);

        List<List<?>> rows = new ArrayList<>();

        for (Map.Entry<Integer, Float> closingWalletEntry : closingWalletMap.entrySet()) {

            int walletId = closingWalletEntry.getKey();

            int retailerId = walletRetailerMap.get(walletId);

            float wallet = closingWalletEntry.getValue() == null ? 0f : closingWalletEntry.getValue();

            float pendingIndent = peindingIndentMap.getOrDefault(retailerId, 0f);

            float loanOutstanding = loanOutstandingMap.getOrDefault(retailerId, 0f);

            float accountClosing = wallet + pendingIndent - loanOutstanding;

            CustomRetailer cr = customRetailerMap.get(retailerId);

            rows.add(Arrays.asList(retailerId, cr.getBusinessName(), cr.getAddress().getCity(), cr.getAddress().getState(), accountClosing, wallet, pendingIndent, loanOutstanding));

        }

        org.apache.commons.io.output.ByteArrayOutputStream byteArrayOutputStream = FileUtil.getCSVByteStream(Arrays.asList("Id", "Partner Name", "City", "State", "Closing Balance", "Closing Wallet", "Unbilled PO", "Loan Outstanding"), rows);

        final HttpHeaders headers = new HttpHeaders();

        headers.set("Content-Type", "text/csv");

        headers.set("Content-disposition", "inline; filename=account-statement-closing-." + StringUtils.toHyphenatedString(closingDate) + ".csv");

        headers.setContentLength(byteArrayOutputStream.toByteArray().length);

        final InputStream inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());

        final InputStreamResource inputStreamResource = new InputStreamResource(inputStream);

        return new ResponseEntity<InputStreamResource>(inputStreamResource, headers, HttpStatus.OK);

    }

    @RequestMapping(value = "/account/statement", method = RequestMethod.GET)

    public ResponseEntity<?> getAccountStatement(HttpServletRequest request, @RequestParam LocalDateTime startDate, @RequestParam LocalDateTime endDate, Model model, @RequestParam(defaultValue = "0") int fofoId) throws Exception {

        LoginDetails loginDetails = cookiesProcessor.getCookiesObject(request);

        boolean isAdmin = roleManager.isAdmin(loginDetails.getRoleIds());

        if (!isAdmin) {

            fofoId = cookiesProcessor.getCookiesObject(request).getFofoId();

        }

        float openingBalance = walletService.getOpeningTill(fofoId, startDate);

        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 wallet movements. Exclude only: PURCHASE (rebuilt via billing) and the scheme set

        // CN_WALLET_REFERENCES (shown instead as the month-end MARGINS credit-note line / partial fallback).

        // Loan/credit legs (CREDIT_LIMIT/CREDIT_UTILIZED/LOAN/LOAN_REPAYMENT) are kept so the body reconciles to

        // the loan-free closing (wallet + pending indent); loan is not represented separately in this statement.

        history = history.stream().filter(x -> !x.getReferenceType().equals(WalletReferenceType.PURCHASE))

                .filter(x -> !WalletService.CN_WALLET_REFERENCES.contains(x.getReferenceType()))

                .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);

        LOGGER.info("OpeningPI - {}, ClosingPI - {}", openingPendingAmount, closingPendingAmount);

        ByteArrayOutputStream byteArrayOutputStream = this.populateData(is, openingBalance + openingPendingAmount, closingBalance + closingPendingAmount,

                customRetailer, history, startDate, endDate, billedStatementDetails, returnDetails, schemeEntries);

        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);

        final InputStream inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());

        final InputStreamResource inputStreamResource = new InputStreamResource(inputStream);

        return new ResponseEntity<InputStreamResource>(inputStreamResource, headers, HttpStatus.OK);

    }

    @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,

                                               List<StatementDetailModel> returnDetails, List<UserWalletHistory> schemeEntries) 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();

        dateStyle.setDataFormat(creationHelper.createDataFormat().getFormat("dd/mm/yyyy"));

        ByteArrayOutputStream bos = new ByteArrayOutputStream();

        Sheet sheet = workbook.getSheetAt(0);

        sheet.getRow(0).getCell(0).setCellValue(customRetailer.getBusinessName());

        sheet.getRow(1).getCell(1).setCellValue(new Date(startLocalDateTime.atZone(ZoneId.systemDefault()).toInstant().toEpochMilli()));

        sheet.getRow(1).getCell(2).setCellValue(new Date(endLocalDateTime.atZone(ZoneId.systemDefault()).toInstant().toEpochMilli()));

        sheet.getRow(2).getCell(2).setCellValue(openingBalance);

        long grandTotalDebit = 0l;

        long grandTotalCredit = 0l;

        int row = 4;

        LocalDate startDate = startLocalDateTime.toLocalDate();

        LocalDate endDate = endLocalDateTime.toLocalDate();

        LOGGER.info(" startDate - {}", startDate);

        LOGGER.info(" endDate - {}", endDate);

        double currentOpening = openingBalance;