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

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

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

            if (hdfcPayment != null) {

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

            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, Float> closingPurchaseMap = orderRepository.selectOpeningAmount(closingDateTime, retailersSet);

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

        Map<Integer, Float> closingBalanceMap = userWalletHistoryRepository.getSumTillDateExcludingPurchase(closingDateTime, walletSet);

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

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

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

        for (Map.Entry<Integer, Float> closingBalance : closingBalanceMap.entrySet()) {

            int walletId = closingBalance.getKey();

            int retailerId = walletRetailerMap.get(walletId);

            float accountClosing = 0f;

            if (!closingPurchaseMap.containsKey(retailerId)) {

                accountClosing = closingBalance.getValue();

            } else {

                accountClosing = closingBalance.getValue() - closingPurchaseMap.get(retailerId);

            }

            CustomRetailer cr = customRetailerMap.get(retailerId);

            rows.add(Arrays.asList(retailerId, cr.getBusinessName(), cr.getAddress().getCity(), cr.getAddress().getState(), accountClosing, closingWalletMap.get(walletId), peindingIndentMap.get(retailerId)));

        }

        org.apache.commons.io.output.ByteArrayOutputStream byteArrayOutputStream = FileUtil.getCSVByteStream(Arrays.asList("Id", "Partner Name", "City", "State", "Closing Balance", "Closing Wallet", "In Transit"), 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/closing-statements", method = RequestMethod.GET)

    public ResponseEntity<?> getAccountStatement(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.atStartOfDay().plusDays(1);

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

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

        Map<Integer, Float> closingPurchaseMap = orderRepository.selectOpeningAmount(closingDateTime, retailersSet);

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

        Map<Integer, Float> closingBalanceMap = userWalletHistoryRepository.getSumTillDateExcludingPurchase(closingDateTime, walletSet);

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

        for (Map.Entry<Integer, Float> closingWalletBalance : closingBalanceMap.entrySet()) {

            int walletId = closingWalletBalance.getKey();

            int retailerId = walletRetailerMap.get(walletId);

            if (!closingPurchaseMap.containsKey(retailerId)) {

                closingPurchaseMap.put(retailerId, closingWalletBalance.getValue());

            } else {

                closingPurchaseMap.put(retailerId, closingWalletBalance.getValue() - closingPurchaseMap.get(retailerId));

            }

            float closingValue = closingPurchaseMap.get(retailerId);

            CustomRetailer cr = customRetailerMap.get(retailerId);

            rows.add(Arrays.asList(retailerId, cr.getBusinessName(), cr.getAddress().getCity(), cr.getAddress().getState(), closingValue));

        }

        org.apache.commons.io.output.ByteArrayOutputStream byteArrayOutputStream = FileUtil.getCSVByteStream(Arrays.asList("Id", "Partner Name", "City", "State", "Closing Balance"), 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())));

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

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

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