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.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.enumuration.fofo.Gateway;

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.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.json.JSONObject;

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.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 javax.transaction.Transactional;

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(rollbackOn = 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 mailSender;

    @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

    SDCreditRequirementRepository sdCreditRequirementRepository;

    @Autowired

    LoiFormRepository loiFormRepository;

    @Autowired

    PartnerOnBoardingPanelRepository partnerOnBoardingPanelRepository;

    @Autowired

    StoreTimelineTatService storeTimelineTatService;

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

        }

        SDCreditRequirement sdCreditRequirement = sdCreditRequirementRepository.selectByFofoId(fofoId);

        List<Loan> loans = loanRepository.selectActiveLoan(fofoId);

        BigDecimal utilization = sdCreditRequirement.getUtilizedAmount();

        BigDecimal availableLimit = sdCreditRequirement.getAvailableLimit();

        BigDecimal creditlimit = sdCreditRequirement.getLimit();

        BigDecimal totalDue = new BigDecimal(0);

        for (Loan loan : loans) {

            BigDecimal pendingAmount = loan.getPendingAmount();

            BigDecimal interestAccrued = loan.getInterestAccrued();

            BigDecimal interestPaid = loan.getInterestPaid();

            totalDue = totalDue.add(interestAccrued.subtract(interestPaid).add(pendingAmount));

            availableLimit = creditlimit.subtract(totalDue);

        }

        model.addAttribute("userWallet", userWallet);

        model.addAttribute("walletHistories", userWalletHistories);

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

        model.addAttribute("size", countItems);

        model.addAttribute("isAdmin", isAdmin);

        model.addAttribute("loans", loans);

        model.addAttribute("totalDue", totalDue);

        model.addAttribute("utilization", utilization);

        model.addAttribute("availableLimit", availableLimit);

        model.addAttribute("creditlimit", creditlimit);

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

        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 = "10") 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("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("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("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("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("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("url", "/getPaginatedWalletRequestRejected");

        } else {

            model.addAttribute("walletRequest", walletRequest);

        }

        return "add-wallet-request-paginated";

    }

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

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

            addWalletRequestRepository.persist(addWalletRequest);

            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.MIN_FULL_STOCK_PAYMENT) {

                    storeTimelineTatService.onFullPaymentReceived(storeTimelineModel.getOnboardingId());

                }

            }

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

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

        mailSender.send(message);

    }

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

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

    }

    @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 (fofoId > 0) {

            if (!isAdmin) {

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

            }

        } else {

            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 {

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

        if (fofoId > 0) {

            if (!isAdmin) {

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

            }

        } else {

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

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

        //LOGGER.info("Data - {}", history.stream().filter(x -> x.getReferenceType().equals(WalletReferenceType.OTHERS)).collect(Collectors.groupingBy(x -> x.getBusinessTimestamp().toLocalDate())));

        InputStream is = getClass().getClassLoader().getResourceAsStream("account-statement.xlsx");

        CustomRetailer customRetailer = retailerService.getAllFofoRetailers().get(fofoId);

        List<StatementDetailModel> billedStatementDetails = orderRepository.selectDetailsBetween(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("Opening - {}, Closing - {}", openingPendingAmount, closingPendingAmount);

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

                customRetailer, history, startDate, endDate, billedStatementDetails);

        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;

    private ByteArrayOutputStream populateData(InputStream is, float openingBalance, float closingBalance, CustomRetailer customRetailer, List<UserWalletHistory> history,

                                               LocalDateTime startLocalDateTime, LocalDateTime endLocalDateTime, List<StatementDetailModel> invoiceDetails) throws Exception {

        Map<LocalDate, List<StatementDetailModel>> dateInvoiceMap = invoiceDetails.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;

        for (LocalDate date = startDate; date.isBefore(endDate.plusDays(1)); date = date.plusDays(1)) {

            List<UserWalletHistory> datewiseWalletHistory = dateWalletMap.get(date);

            LOGGER.info("date - {}, datewiseWalletHistory - {}", date, datewiseWalletHistory);

            if (datewiseWalletHistory != null) {

                for (UserWalletHistory walletEntry : datewiseWalletHistory) {

                    Cell dateCell = sheet.createRow(row).createCell(0);

                    //Date

                    dateCell.setCellValue(new Date(walletEntry.getTimestamp().atZone(ZoneId.systemDefault()).toInstant().toEpochMilli()));

                    dateCell.setCellStyle(dateStyle);

                    //TransactType

                    sheet.getRow(row).createCell(1).setCellValue(walletEntry.getReferenceType().toString());

                    //Reference Id

                    sheet.getRow(row).createCell(2).setCellValue(walletEntry.getReference());

                    if (walletEntry.getAmount() > 0) {

                        //Debit

                        sheet.getRow(row).createCell(3).setCellValue(0);

                        //Credit

                        sheet.getRow(row).createCell(4).setCellValue(walletEntry.getAmount());

                        grandTotalCredit += walletEntry.getAmount();

                    } else {

                        sheet.getRow(row).createCell(3).setCellValue(-walletEntry.getAmount());

                        sheet.getRow(row).createCell(4).setCellValue(0);

                        grandTotalDebit -= walletEntry.getAmount();

                    }

                    //Running balance

                    currentOpening += walletEntry.getAmount();

                    sheet.getRow(row).createCell(5).setCellValue(currentOpening);

                    //Description

                    sheet.getRow(row).createCell(6).setCellValue(walletEntry.getDescription());

                    row++;

                }

            }

            List<StatementDetailModel> statementDetailModels = dateInvoiceMap.get(date);

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

            if (statementDetailModels != null) {

                for (StatementDetailModel statementDetailModel : statementDetailModels) {

                    Cell dateCell = sheet.createRow(row).createCell(0);

                    dateCell.setCellValue(statementDetailModel.getOnDate());

                    dateCell.setCellStyle(dateStyle);

                    //Transact Type

                    sheet.getRow(row).createCell(1).setCellValue("BILLING");

                    //Transact Reference

                    sheet.getRow(row).createCell(2).setCellValue(statementDetailModel.getInvoiceNumber());

                    if (statementDetailModel.getAmount() > 0) {

                        sheet.getRow(row).createCell(3).setCellValue(statementDetailModel.getAmount());

                        sheet.getRow(row).createCell(4).setCellValue(0);

                        grandTotalDebit += statementDetailModel.getAmount();

                    } else {

                        sheet.getRow(row).createCell(3).setCellValue(0);

                        sheet.getRow(row).createCell(4).setCellValue(-statementDetailModel.getAmount());

                        grandTotalCredit += -statementDetailModel.getAmount();

                    }

                    //Running Balance

                    currentOpening -= statementDetailModel.getAmount();

                    sheet.getRow(row).createCell(5).setCellValue(currentOpening);

                    //Narration

                    sheet.getRow(row).createCell(6).setCellValue(statementDetailModel.getReferenceType());

                    row += 1;

                }

            }

            if (YearMonth.from(date).atEndOfMonth().equals(date)) {

                List<CreditNote> creditNotes = creditNoteRepository.selectAll(customRetailer.getPartnerId(), YearMonth.from(date));

                if (creditNotes != null) {

                    for (CreditNote creditNote : creditNotes) {

                        double cnAmount = creditNoteLineRepository.selectAllByCreditNote(creditNote.getId()).stream().collect(Collectors.summingDouble(x -> x.getAmount()));

                        Cell dateCell = sheet.createRow(row).createCell(0);

                        dateCell.setCellValue(new Date(date.atStartOfDay().atZone(ZoneId.systemDefault()).toInstant().toEpochMilli()));

                        dateCell.setCellStyle(dateStyle);

                        sheet.getRow(row).createCell(1).setCellValue("CREDIT NOTE");

                        sheet.getRow(row).createCell(2).setCellValue(creditNote.getCreditNoteNumber());

                        if (cnAmount > 0) {

                            sheet.getRow(row).createCell(3).setCellValue(0);

                            sheet.getRow(row).createCell(4).setCellValue(cnAmount);

                            grandTotalCredit += cnAmount;

                        } else {

                            sheet.getRow(row).createCell(3).setCellValue(-cnAmount);

                            sheet.getRow(row).createCell(4).setCellValue(0);

                            grandTotalDebit += -cnAmount;

                        }

                        //Running balance

                        currentOpening += cnAmount;

                        sheet.getRow(row).createCell(5).setCellValue(currentOpening);

                        //Narration

                        sheet.getRow(row).createCell(6).setCellValue("Credit Note Issued");

                        row += 1;

                    }

                }

            }

        }

        sheet.createRow(row).createCell(2).setCellValue("Grand Total");

        sheet.getRow(row).createCell(3).setCellValue(grandTotalDebit);

        sheet.getRow(row).createCell(4).setCellValue(grandTotalCredit);

        /*row += 2;

        sheet.createRow(row).createCell(0).setCellValue("Closing Balance");

        sheet.getRow(row).createCell(3).setCellValue(closingBalance);*/

        row++;

        /*

         * sheet.createRow(row).createCell(0).setCellValue("Pending Grns");

         * sheet.getRow(row).createCell(2).setCellValue(closingBalance); row++;

         */

        try {

            workbook.write(bos);

        } finally {

            workbook.close();

            bos.close();

        }

        return bos;

    }

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

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

        List<Integer> fofoIds = fofoStoreRepository.selectAll().stream().map(x -> x.getId()).collect(Collectors.toList());

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

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

        String customRetailers = JSONObject.valueToString(customRetailersMap.values());

        model.addAttribute("customRetailers", customRetailers);

        model.addAttribute("referenceTypes", WalletReferenceType.referenceType);

        model.addAttribute("transactionTypes", TransactionType.values());

        return "wallet-edit";

    }

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

    public String getWalletHistory(HttpServletRequest request, @RequestParam(name = "reference", defaultValue = "0") int reference, @RequestParam WalletReferenceType referenceType, Model model) throws Exception {

        LOGGER.info("type" + referenceType);

        List<UserWalletHistory> userWalletHistory = userWalletHistoryRepository.selectAllByreferenceIdandreferenceType(reference, referenceType);

        if (userWalletHistory.isEmpty()) {

            throw new ProfitMandiBusinessException("RefrenceId", reference, "Reference Id not found");

        }

        UserWallet userWallet = userWalletRepository.selectById(userWalletHistory.get(0).getWalletId());