Subversion Repositories SmartDukaan

package com.smartdukaan.cron.scheduled;

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

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

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

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

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

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

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

import com.spice.profitmandi.dao.cart.CartService;

import com.spice.profitmandi.dao.cart.SmartCartService;

import com.spice.profitmandi.dao.entity.auth.AuthUser;

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

import com.spice.profitmandi.dao.entity.logistics.AST;

import com.spice.profitmandi.dao.entity.logistics.ASTRepository;

import com.spice.profitmandi.dao.entity.logistics.AreaRepository;

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

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

import com.spice.profitmandi.dao.enumuration.cs.EscalationType;

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

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

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

import com.spice.profitmandi.dao.repository.cs.CsService;

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

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

import com.spice.profitmandi.dao.repository.inventory.StateRepository;

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

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

import com.spice.profitmandi.service.PartnerStatsService;

import com.spice.profitmandi.service.RbmTargetService;

import com.spice.profitmandi.service.inventory.InventoryService;

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

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

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

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

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

import org.apache.commons.io.output.ByteArrayOutputStream;

import org.apache.logging.log4j.LogManager;

import org.apache.logging.log4j.Logger;

import org.apache.poi.common.usermodel.HyperlinkType;

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

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

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

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

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

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

import org.springframework.core.io.ByteArrayResource;

import org.springframework.mail.javamail.JavaMailSender;

import org.springframework.mail.javamail.MimeMessageHelper;

import org.springframework.stereotype.Component;

import org.springframework.transaction.annotation.Transactional;

import javax.mail.MessagingException;

import javax.mail.internet.InternetAddress;

import javax.mail.internet.MimeMessage;

import java.io.*;

import java.math.BigDecimal;

import java.time.*;

import java.time.temporal.ChronoUnit;

import java.util.*;

import java.util.stream.Collectors;

import java.util.stream.Stream;

@Component

@Transactional(rollbackFor = {Throwable.class, ProfitMandiBusinessException.class})

public class ScheduledTasksTest {

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

    @Autowired

    TransactionRepository transactionRepository;

    @Autowired

    @Qualifier(value = "googleMailSender")

    private JavaMailSender googleMailSender;

    @Autowired

    LoanRepository loanRepository;

    @Autowired

    SDCreditService sdCreditService;

    @Autowired

    UserRepository userRepository;

    @Autowired

    CsService csService;

    @Autowired

    RbmRatingRepository rbmRatingRepository;

    @Autowired

    private JavaMailSender mailSender;

    @Autowired

    SalesRatingRepository salesRatingRepository;

    @Autowired

    FofoStoreRepository fofoStoreRepository;

    @Autowired

    SmartCartService smartCartService;

    @Autowired

    RetailerService retailerService;

    @Autowired

    ASTRepository astRepository;

    @Autowired

    AuthRepository authRepository;

    @Autowired

    StateRepository stateRepository;

    @Autowired

    MonthlyTargetRepository monthlyTargetRepository;

    @Autowired

    PartnerTypeChangeService partnerTypeChangeService;

    @Autowired

    ReturnOrderInfoRepository returnOrderInfoRepository;

    @Autowired

    OrderRepository orderRepository;

    @Autowired

    FofoOrderItemRepository fofoOrderItemRepository;

    @Autowired

    InventoryService inventoryService;

    @Autowired

    UserWalletRepository userWalletRepository;

    @Autowired

    LoanStatementRepository loanStatementRepository;

    @Autowired

    ActivatedImeiRepository activatedImeiRepository;

    @Autowired

    PartnerDailyInvestmentRepository partnerDailyInvestmentRepository;

    @Autowired

    WalletService walletService;

    @Autowired

    RbmTargetService rbmTargetService;

    @Autowired

    PartnerStatsService partnerStatsService;

    @Autowired

    UserWalletHistoryRepository userWalletHistoryRepository;

    public void test() throws Exception {

        System.out.println("test start");

        //partnerStatsService.getAllPartnerStats();

        this.generateBiReportExcel();

        System.out.println("test end");

    }

    public void generateBiReport() throws Exception {

        LOGGER.info("bi report started {-----}");

        this.generateBiReportExcel();

        LOGGER.info("bi report ended {-----}");

    }

    public void createLoanForBillingByTransactionIdAndInvoiceNumber(int transactionId, double invoiceAmount, String invoiceNumber) throws Exception {

        sdCreditService.createLoanForBilling(transactionId, invoiceAmount, invoiceNumber);

    }

    public void loanSettle() throws Exception {

        List<Integer> refrences = Arrays.asList(25807,36003,38938,39506,42219,45084);

        for(Integer ref : refrences){

            List<LoanStatement> loanStatements = loanStatementRepository.selectByLoanId(ref);

            double amountSum = loanStatements.stream().map(LoanStatement::getAmount).mapToDouble(BigDecimal::doubleValue).sum();

            if(amountSum > 0){

                walletService.addAmountToWallet(loanStatements.get(0).getFofoId(),ref, WalletReferenceType.CREDIT_LIMIT,"Amount reversal against credit limit deduction",(float) amountSum,LocalDateTime.now());

//                Loan statement entry

                    BigDecimal adjustAmount = BigDecimal.valueOf(amountSum).negate(); // or multiply by -1

                    LoanStatement loanStatement = new LoanStatement();

                    loanStatement.setAmount(adjustAmount);

                    loanStatement.setFofoId(loanStatements.get(0).getFofoId());

                    loanStatement.setLoanReferenceType(LoanReferenceType.PRINCIPAL);

                    loanStatement.setCreatedAt(LocalDateTime.now());

                    loanStatement.setDescription("Amount reversal due to access debit against limit");

                    loanStatement.setLoanId(ref);

                    loanStatement.setBusinessDate(LocalDateTime.now());

                    loanStatementRepository.persist(loanStatement);

                    Loan loan = loanRepository.selectByLoanId(ref);

                    loan.setPendingAmount(BigDecimal.valueOf(0));

                    loan.setSettledOn(LocalDateTime.now());

                }

        }

    }

    private void sendMailHtmlFormat(String email[], String body, String cc[], String bcc[], String subject)

            throws MessagingException, ProfitMandiBusinessException, IOException {

        MimeMessage message = mailSender.createMimeMessage();

        MimeMessageHelper helper = new MimeMessageHelper(message);

        helper.setSubject(subject);

        helper.setText(body, true);

        helper.setTo(email);

        if (cc != null) {

            helper.setCc(cc);

        }

        if (bcc != null) {

            helper.setBcc(bcc);

        }

        InternetAddress senderAddress = new InternetAddress("noreply@smartdukaan.com", "Smart Dukaan");

        helper.setFrom(senderAddress);

        mailSender.send(message);

    }

    public Map<Integer,Integer> findLoanTransactionMapingAccordingLoan(List<Integer> loanIds) throws ProfitMandiBusinessException {

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

        for(int loanId : loanIds){

            Transaction transaction = null;

            Loan loan = loanRepository.selectByLoanId(loanId);

            List<Transaction> transactions = transactionRepository.selectByRetailerId(loan.getFofoId());

            LocalDateTime nearestDateTime = transactions.stream().map(x -> x.getCreateTimestamp())

                    .min(Comparator.comparingLong(x -> Math.abs(ChronoUnit.MILLIS.between(x, loan.getCreatedOn()))))

                    .orElse(null);

            if (nearestDateTime != null && loan.getCreatedOn().plusMinutes(2).isAfter(nearestDateTime) &&

                    loan.getCreatedOn().minusMinutes(1).isBefore(nearestDateTime)) {

                // Here transaction is still null

                transaction = transactions.stream()

                        .filter(x -> x.getCreateTimestamp().equals(nearestDateTime))

                        .findFirst().get();

                transactionLoanMap.put(transaction.getId(), loanId);

            }

        }

        LOGGER.info("transactionLoanMap {}",transactionLoanMap);

        return transactionLoanMap;

    }

    public void sendRbmFeedbackSummaryEmail() throws MessagingException, ProfitMandiBusinessException, IOException {

        LocalDateTime startOfMonth = LocalDate.now().withDayOfMonth(1).atStartOfDay();

        LocalDateTime endOfMonth = LocalDateTime.now();

        String[] bcc = {"tarun.verma@smartdukaan.com"};

        // Get all RBM users

        List<AuthUser> authUsers = csService.getAuthUserIds(

                ProfitMandiConstants.TICKET_CATEGORY_RBM,

                Arrays.asList(EscalationType.L1)

        );

        if (authUsers.isEmpty()) {

            LOGGER.info("No RBMs found.");

            return;

        }

        List<Integer> rbmIds = authUsers.stream().map(AuthUser::getId).collect(Collectors.toList());

        // Fetch ratings for all RBMs for current month

        List<RbmRating> feedbackList = rbmRatingRepository.selectByRbmIdsAndDateRange(rbmIds, startOfMonth, endOfMonth);

        if (feedbackList.isEmpty()) {

            LOGGER.info("No feedback entries found for RBMs.");

            return;

        }

        // Sort feedback by createTimeStamp DESC

        feedbackList.sort((a, b) -> b.getCreateTimeStamp().compareTo(a.getCreateTimeStamp()));

        // Fetch and map FOFO (partner) names

        Map<Integer, String> fofoNameMap = new HashMap<>();

        for (RbmRating rating : feedbackList) {

            int fofoId = rating.getFofoId();

            if (!fofoNameMap.containsKey(fofoId)) {

                User fofoUser = userRepository.selectById(fofoId);

                FofoStore fofoStore = fofoStoreRepository.selectByRetailerId(fofoId);

                String partnerName = fofoUser != null ? fofoUser.getName() : "Unknown Partner";

                String storeCode = fofoStore != null ? fofoStore.getCode() : "Unknown Code";

                String displayName = partnerName + " (" + storeCode + ")";

                fofoNameMap.put(fofoId, displayName);

            }

        }

        // Map RBM ID to name for quick lookup

        Map<Integer, String> rbmNameMap = authUsers.stream()

                .collect(Collectors.toMap(AuthUser::getId, AuthUser::getFullName));

        // Generate HTML content

        StringBuilder emailContent = new StringBuilder();

        emailContent.append("<html><body>");

        emailContent.append("<p>Dear Team,</p>");

        emailContent.append("<p>Here is the <b>latest RBM Rating and Feedback Summary</b> for ")

                .append(LocalDate.now().getMonth()).append(":</p>");

        emailContent.append("<table border='1' cellspacing='0' cellpadding='5'>");

        emailContent.append("<tr>")

                .append("<th>RBM Name</th>")

                .append("<th>Partner Name</th>")

                .append("<th>Rating</th>")

                .append("<th>Comment</th>")

                .append("<th>Date</th>")

                .append("</tr>");

        for (RbmRating rating : feedbackList) {

            String rbmName = rbmNameMap.getOrDefault(rating.getRbmId(), "Unknown RBM");

            String partnerName = fofoNameMap.getOrDefault(rating.getFofoId(), "Unknown Partner");

            emailContent.append("<tr>")

                    .append("<td>").append(rbmName).append("</td>")

                    .append("<td>").append(partnerName).append("</td>")

                    .append("<td>").append(rating.getRating()).append("</td>")

                    .append("<td>").append(rating.getComment() != null ? rating.getComment() : "-").append("</td>")

                    .append("<td>").append(rating.getCreateTimeStamp().toLocalDate()).append("</td>")

                    .append("</tr>");

        }

        emailContent.append("</table>");

        emailContent.append("<br><p>Regards,<br>Smart Dukaan Team</p>");

        emailContent.append("</body></html>");

        String subject = "Monthly RBM Feedback Summary - " + LocalDate.now().getMonth();

        List<String> sendTo = new ArrayList<>();

        sendTo.add("sm@smartdukaan.com"); //

        sendTo.add("chiranjib.sarkar@smartdukaan.com"); //

        sendTo.add("kamini.sharma@smartdukaan.com"); //

        String[] emailRecipients = sendTo.toArray(new String[0]);

        this.sendMailHtmlFormat(emailRecipients, emailContent.toString(), null, bcc, subject);

        LOGGER.info("Consolidated RBM feedback summary email sent.");

    }

    public void sendSalesFeedbackSummaryEmail() throws MessagingException, ProfitMandiBusinessException, IOException {

        LocalDateTime startOfMonth = LocalDate.now().withDayOfMonth(1).atStartOfDay();

        LocalDateTime endOfMonth = LocalDateTime.now();

        String[] bcc = {"tarun.verma@smartdukaan.com"};

//        String[] bcc = {"tejus.lohani@smartdukaan.com"};

        // Get all RBM users

        List<AuthUser> authUsers = csService.getAuthUserIds(

                ProfitMandiConstants.TICKET_CATEGORY_SALES,

                Arrays.asList(EscalationType.L1)

        );

        if (authUsers.isEmpty()) {

            LOGGER.info("No sales person found.");

            return;

        }

        List<Integer> salesL1Ids = authUsers.stream().map(AuthUser::getId).collect(Collectors.toList());

        // Fetch ratings for all RBMs for current month

        List<SalesRating> feedbackList = salesRatingRepository.selectBySalesL1IdsAndDateRange(salesL1Ids, startOfMonth, endOfMonth);

        if (feedbackList.isEmpty()) {

            LOGGER.info("No feedback entries found for Sales.");

            return;

        }

        // Sort feedback by createTimeStamp DESC

        feedbackList.sort((a, b) -> b.getCreateTimeStamp().compareTo(a.getCreateTimeStamp()));

        // Fetch and map FOFO (partner) names

        Map<Integer, String> fofoNameMap = new HashMap<>();

        for (SalesRating rating : feedbackList) {

            int fofoId = rating.getFofoId();

            if (!fofoNameMap.containsKey(fofoId)) {

                User fofoUser = userRepository.selectById(fofoId);

                FofoStore fofoStore = fofoStoreRepository.selectByRetailerId(fofoId);

                String partnerName = fofoUser != null ? fofoUser.getName() : "Unknown Partner";

                String storeCode = fofoStore != null ? fofoStore.getCode() : "Unknown Code";

                String displayName = partnerName + " (" + storeCode + ")";

                fofoNameMap.put(fofoId, displayName);

            }

        }

        // Map RBM ID to name for quick lookup

        Map<Integer, String> salesL1NameMap = authUsers.stream()

                .collect(Collectors.toMap(AuthUser::getId, AuthUser::getFullName));

        // Generate HTML content

        StringBuilder emailContent = new StringBuilder();

        emailContent.append("<html><body>");

        emailContent.append("<p>Dear Team,</p>");

        emailContent.append("<p>Here is the <b>latest Sales L1 Rating and Feedback Summary</b> for ")

                .append(LocalDate.now().getMonth()).append(":</p>");

        emailContent.append("<table border='1' cellspacing='0' cellpadding='5'>");

        emailContent.append("<tr>")

                .append("<th>Sales L1 Name</th>")

                .append("<th>Partner Name</th>")

                .append("<th>Partner Category</th>")

                .append("<th>Rating</th>")

                .append("<th>Comment</th>")

                .append("<th>Date</th>")

                .append("</tr>");

        for (SalesRating rating : feedbackList) {

            String salesL1 = salesL1NameMap.getOrDefault(rating.getSalesL1Id(), "Unknown Sales Person");

            String partnerName = fofoNameMap.getOrDefault(rating.getFofoId(), "Unknown Partner");

            PartnerType partnerType = partnerTypeChangeService.getTypeOnDate(rating.getFofoId(), LocalDate.now());

            emailContent.append("<tr>")

                    .append("<td>").append(salesL1).append("</td>")

                    .append("<td>").append(partnerName).append("</td>")

                    .append("<td>").append(partnerType).append("</td>")

                    .append("<td>").append(rating.getRating()).append("</td>")

                    .append("<td>").append(rating.getComment() != null ? rating.getComment() : "-").append("</td>")

                    .append("<td>").append(rating.getCreateTimeStamp().toLocalDate()).append("</td>")

                    .append("</tr>");

        }

        emailContent.append("</table>");

        emailContent.append("<br><p>Regards,<br>Smartdukaan Team</p>");

        emailContent.append("</body></html>");

        String subject = "Monthly Sales L1 Feedback Summary Test test - " + LocalDate.now().getMonth();

        List<String> sendTo = new ArrayList<>();

         sendTo.add("sm@smartdukaan.com"); //

         sendTo.add("chiranjib.sarkar@smartdukaan.com"); //

         sendTo.add("kamini.sharma@smartdukaan.com"); //

        String[] emailRecipients = sendTo.toArray(new String[0]);

        this.sendMailHtmlFormat(emailRecipients, emailContent.toString(), null, bcc, subject);

        LOGGER.info("Consolidated Sales L1 feedback summary email sent.");

    }

    public void generateBiReportExcel() throws Exception {

        LocalDateTime startOfToday = LocalDate.now().atStartOfDay();

        /*Map<Integer, CustomRetailer> customRetailers = retailerService.getAllFofoRetailersInternalFalse();

        List<Integer> retailerIds = customRetailers.values().stream().map(CustomRetailer::getPartnerId).collect(Collectors.toList());*/

        List<Integer> retailerIds = Arrays.asList(175139615,175139391,175135707,175139854,175139302,175139710,175139749);

        Map<Integer,CustomRetailer> customRetailers = retailerService.getFofoRetailers(retailerIds);

        //partner daily investment

        List<Loan> defaultLoans = sdCreditService.getDefaultLoan();

        Map<Integer,List<Loan>> defaultLoanMap = defaultLoans.stream().collect(Collectors.groupingBy(Loan::getFofoId));

        Map<Integer, PartnerDailyInvestment> partnerDailyInvestmentMap = new HashMap<>();

        List<PartnerDailyInvestment> partnerDailyInvestments = partnerDailyInvestmentRepository

                .selectAll(new ArrayList<>(retailerIds), LocalDate.now().minusDays(1));

        if (!partnerDailyInvestments.isEmpty()) {

            partnerDailyInvestmentMap = partnerDailyInvestments.stream()

                    .collect(Collectors.toMap(x -> x.getFofoId(), x -> x));

        }

    //  this month return data

        YearMonth currentMonth = YearMonth.now();

        String currentMonthStringValue = String.valueOf(currentMonth);

        LocalDateTime currentMonthStartDate = YearMonth.now().atDay(1).atStartOfDay();

        LocalDateTime currentMonthEndDate = LocalDateTime.now().minusDays(1).toLocalDate().atTime(23, 59, 59);

        List<ReturnOrderInfoModel> currentMonthReturnOrderInfoModels = returnOrderInfoRepository.selectAllByBetweenDate(currentMonthStartDate, currentMonthEndDate);

        Map<Integer, Long> currentMonthPartnerReturnOrderInfoModelMap = currentMonthReturnOrderInfoModels.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getRefundAmount()))));

        List<Order> currentMonthRtoRefundOrders = orderRepository.selectAllOrderDatesBetweenByStatus(currentMonthStartDate, currentMonthEndDate, OrderStatus.RTO_REFUNDED);

        Map<Integer, Long> currentMonthRtoRefundOrderMap = currentMonthRtoRefundOrders.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getTotalAmount()))));

//  last month return data

        YearMonth lastMonth = YearMonth.now().minusMonths(1);

        String lastMonthStringValue = String.valueOf(lastMonth);

        LocalDateTime lastMontStartDate = lastMonth.atDay(1).atStartOfDay();

        LocalDateTime lastMonthEndDate = lastMonth.atEndOfMonth().atTime(23, 59, 59);

        List<ReturnOrderInfoModel> lastMonthReturnOrderInfoModels = returnOrderInfoRepository.selectAllByBetweenDate(lastMontStartDate, lastMonthEndDate);

        Map<Integer, Long> lastMonthPartnerReturnOrderInfoModelMap = lastMonthReturnOrderInfoModels.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getRefundAmount()))));

        List<Order> lastMonthRtoRefundOrders = orderRepository.selectAllOrderDatesBetweenByStatus(lastMontStartDate, lastMonthEndDate, OrderStatus.RTO_REFUNDED);

        Map<Integer, Long> lastMonthRtoRefundOrderMap = lastMonthRtoRefundOrders.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getTotalAmount()))));

//  twoMonthsAgo return data

        YearMonth twoMonthsAgo = YearMonth.now().minusMonths(2);

        String twoMonthAgoStringValue = String.valueOf(twoMonthsAgo);

        LocalDateTime twoMonthsAgoStartDate = twoMonthsAgo.atDay(1).atStartOfDay();

        LocalDateTime twoMonthsAgoEndDate = twoMonthsAgo.atEndOfMonth().atTime(23, 59, 59);

        List<ReturnOrderInfoModel> twoMonthAgoReturnOrderInfoModels = returnOrderInfoRepository.selectAllByBetweenDate(twoMonthsAgoStartDate, twoMonthsAgoEndDate);

        Map<Integer, Long> twoMonthAgoPartnerReturnOrderInfoModelMap = twoMonthAgoReturnOrderInfoModels.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getRefundAmount()))));

LOGGER.info("twoMonthAgoReturnOrderInfoModels {}",twoMonthAgoReturnOrderInfoModels);

        List<Order> twoMonthRtoRefundOrders = orderRepository.selectAllOrderDatesBetweenByStatus(twoMonthsAgoStartDate, twoMonthsAgoEndDate, OrderStatus.RTO_REFUNDED);

        Map<Integer, Long> twoMonthAgoRtoRefundOrderMap = twoMonthRtoRefundOrders.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getTotalAmount()))));

        LOGGER.info("twoMonthRtoRefundOrders {}",twoMonthRtoRefundOrders);

        Map<Integer , String> assessmentMap = new HashMap<>();

        Map<Integer , String> zeroBillingMap = new HashMap<>();

        Map<Integer , Float> billingNeededMap = new HashMap<>();

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

        Map<Integer , BIRetailerModel> biRetailerModelMap = new HashMap<>();

        Map<Integer , FofoInvestmentModel> biInvestmentModelMap = new HashMap<>();

        Map<Integer, Map<YearMonth, BiSecondaryModel>> allRetailerMonthlyData = new HashMap<>();

        Map<Integer,Double> fofoTotalStockPriceMap = new HashMap<>();

        Map<Integer,Map<String, BrandStockPrice>> fofoBrandStockPriceMap = new HashMap<>();

        Map<Integer,Long> fofoTotalMtdSecondaryMap = new HashMap<>();

        Map<Integer,Map<String, Long>> fofoBrandWiseMtdSecondaryMap = new HashMap<>();

        Map<Integer,Double> fofoTotalMtdTertiaryMap = new HashMap<>();

        Map<Integer,Map<String, Double>> fofoBrandMtdTertiaryMap = new HashMap<>();

        for(Integer fofoId: retailerIds){

            String rbmName = "";

            int rbmL1 = csService.getAuthUserId(ProfitMandiConstants.TICKET_CATEGORY_RBM,EscalationType.L1,fofoId);

            if(rbmL1 != 0){

                 rbmName = authRepository.selectById(rbmL1).getFullName();

            }else {

                int rbmL2 = csService.getAuthUserId(ProfitMandiConstants.TICKET_CATEGORY_RBM, EscalationType.L2, fofoId);

                if(rbmL2 != 0){

                    rbmName = authRepository.selectById(rbmL2).getFullName();

                }

            }

            String bmName ="";

            int bmId = csService.getAuthUserId(ProfitMandiConstants.TICKET_CATEGORY_SALES,EscalationType.L2,fofoId);

            if(bmId !=0){

                bmName = authRepository.selectById(bmId).getFullName();

            }

            int managerId = csService.getAuthUserId(ProfitMandiConstants.TICKET_CATEGORY_SALES,EscalationType.L1,fofoId);

            String managerName = " ";

            if(managerId != 0){

                 managerName = authRepository.selectById(managerId).getFullName();

            }else {

                managerName = bmName;

            }

            AST ast = astRepository.selectById(customRetailers.get(fofoId).getAstId());

            PartnerType partnerTypeThisMonth = partnerTypeChangeService.getTypeOnMonth(fofoId, YearMonth.now());

//            generate retaile detail

            BIRetailerModel biRetailerModel = new BIRetailerModel();

            biRetailerModel.setBmName(bmName);

            biRetailerModel.setCode(customRetailers.get(fofoId).getCode());

            if(ast != null){

                biRetailerModel.setArea(ast.getArea());

            }else {

                biRetailerModel.setArea("-");

            }

            biRetailerModel.setCity(customRetailers.get(fofoId).getAddress().getCity());

            biRetailerModel.setStoreName(customRetailers.get(fofoId).getBusinessName());

            biRetailerModel.setStatus(String.valueOf(customRetailers.get(fofoId).getActivationType()));

            biRetailerModel.setCategory(String.valueOf(partnerTypeThisMonth));

            biRetailerModel.setSalesManager(managerName);

            biRetailerModel.setRbm(rbmName);

            biRetailerModelMap.put(fofoId,biRetailerModel);

//            generate secondary data

            List<PartnerWiseActivatedNotBilledTotal> partnerWiseActivatedNotBilledMonthlyTotals = activatedImeiRepository.getTotalMonthlyActivatedNotBilled(fofoId,twoMonthsAgoStartDate);

            Map<YearMonth , PartnerWiseActivatedNotBilledTotal> partnerWiseActivatedNotBilledTotalMap = partnerWiseActivatedNotBilledMonthlyTotals.stream().collect(Collectors.toMap(x-> YearMonth.parse(x.getYearMonth()),x->x));

//            this month secondary target

            double currentSecondaryTarget =  monthlyTargetRepository.selectByDateAndFofoId(YearMonth.now(), fofoId) != null ? monthlyTargetRepository.selectByDateAndFofoId(YearMonth.now(), fofoId).getPurchaseTarget() : 0;

            long currentMonthReturn = currentMonthPartnerReturnOrderInfoModelMap.getOrDefault(fofoId, 0L) + currentMonthRtoRefundOrderMap.getOrDefault(fofoId, 0L);

            Map<Integer, Double> secondaryMtd = orderRepository.selectOrderValueBetweenBillingDatesGroupByFofoId(Arrays.asList(fofoId),

                    startOfToday.withDayOfMonth(1), startOfToday.with(LocalTime.MAX).minusDays(1)).stream().collect(Collectors.toMap(x -> x.getId(), x -> x.getAmount()));

            double secondaryAchievedMtd = secondaryMtd.getOrDefault(fofoId, 0.0);

            double currentMonthNetSecondary = secondaryAchievedMtd - currentMonthReturn;

            double currentMonthSecondaryPercent = currentSecondaryTarget == 0 ? 0.0 : Math.round(Math.abs((secondaryAchievedMtd / currentSecondaryTarget) * 100));

            double currentMonthUnbilled = partnerWiseActivatedNotBilledTotalMap.get(currentMonth) != null ? partnerWiseActivatedNotBilledTotalMap.get(currentMonth).getTotalUnbilledAmount() : 0d;

//          this month tertiary----------

            LocalDateTime now = LocalDateTime.now();

            double todaySale = fofoOrderItemRepository.selectSumMopGroupByRetailer(startOfToday, now, fofoId, false).get(fofoId);

            double mtdSaleTillYesterDay = fofoOrderItemRepository.selectSumMopGroupByRetailer(startOfToday.withDayOfMonth(1), startOfToday, fofoId, false).get(fofoId);

            double mtdSale = mtdSaleTillYesterDay;

//            last month secondary target

            double lastMonthSecondaryTarget = monthlyTargetRepository.selectByDateAndFofoId(lastMonth, fofoId) != null ?  monthlyTargetRepository.selectByDateAndFofoId(lastMonth, fofoId).getPurchaseTarget() : 0;

            long lastMonthReturn = (lastMonthPartnerReturnOrderInfoModelMap.getOrDefault(fofoId,0L) + lastMonthRtoRefundOrderMap.getOrDefault(fofoId,0L));

            Map<Integer, Double> lastMonthSecondary = orderRepository.selectOrderValueBetweenBillingDatesGroupByFofoId(Arrays.asList(fofoId),

                    lastMontStartDate, lastMonthEndDate).stream().collect(Collectors.toMap(x -> x.getId(), x -> x.getAmount()));

            double lastMonthSecondaryAchieved = lastMonthSecondary.getOrDefault(fofoId, 0.0);

            double lastMonthNetSecondary = lastMonthSecondaryAchieved - lastMonthReturn;

            double lastMonthSecondaryPercent = lastMonthSecondaryTarget == 0 ? 0.0 : Math.round(Math.abs((lastMonthSecondaryAchieved / lastMonthSecondaryTarget) * 100));

            double lastMonthUnbilled = partnerWiseActivatedNotBilledTotalMap.get(lastMonth) != null ? partnerWiseActivatedNotBilledTotalMap.get(lastMonth).getTotalUnbilledAmount() : 0d;

//           last month tertiary

            Double lastMonthSale = fofoOrderItemRepository.selectSumMopGroupByRetailer(

                    lastMontStartDate, lastMonthEndDate, fofoId, false).get(fofoId);

//            two month ago secondary target

            double twoMonthAgoSecondaryTarget = monthlyTargetRepository.selectByDateAndFofoId(twoMonthsAgo, fofoId) != null ? monthlyTargetRepository.selectByDateAndFofoId(twoMonthsAgo, fofoId).getPurchaseTarget() : 0;

            long twoMonthAgoReturn = (twoMonthAgoPartnerReturnOrderInfoModelMap.getOrDefault(fofoId,0L) + twoMonthAgoRtoRefundOrderMap.getOrDefault(fofoId,0L));

            Map<Integer, Double> twoMonthAgoSecondary = orderRepository.selectOrderValueBetweenBillingDatesGroupByFofoId(Arrays.asList(fofoId),

                    twoMonthsAgoStartDate, twoMonthsAgoEndDate).stream().collect(Collectors.toMap(x -> x.getId(), x -> x.getAmount()));

            double twoMonthAgoSecondaryAchieved = twoMonthAgoSecondary.getOrDefault(fofoId, 0.0);

            double twoMonthAgoNetSecondary = twoMonthAgoSecondaryAchieved - twoMonthAgoReturn;

            double twoMonthAgoSecondaryPercent = twoMonthAgoSecondaryTarget == 0 ? 0.0 : Math.round(Math.abs((twoMonthAgoSecondaryAchieved / twoMonthAgoSecondaryTarget) * 100));

            double twoMonthAgoUnbilled = partnerWiseActivatedNotBilledTotalMap.get(twoMonthsAgo) != null ? partnerWiseActivatedNotBilledTotalMap.get(twoMonthsAgo).getTotalUnbilledAmount() : 0d;

//          second Month Tertiary

            double twoMonthAgoSale = fofoOrderItemRepository.selectSumMopGroupByRetailer(

                    twoMonthsAgoStartDate, twoMonthsAgoEndDate, fofoId, false).get(fofoId);

            Map<YearMonth, BiSecondaryModel> monthlySecondaryModels = new HashMap<>();

            BiSecondaryModel currentMonthSecondaryModel = new BiSecondaryModel(

                    currentSecondaryTarget,

                    secondaryAchievedMtd,

                    currentMonthReturn,

                    currentMonthNetSecondary,

                    currentMonthSecondaryPercent,

                    mtdSale,

                    currentMonthUnbilled // for now, unbilled tertiary value

            );

            BiSecondaryModel lastMonthSecondaryModel = new BiSecondaryModel(

                    lastMonthSecondaryTarget,

                    lastMonthSecondaryAchieved,

                    lastMonthReturn,

                    lastMonthNetSecondary,

                    lastMonthSecondaryPercent,

                    lastMonthSale,

                    lastMonthUnbilled // for now, unbilled tertiary value

            );

            BiSecondaryModel twoMonthAgoSecondaryModel = new BiSecondaryModel(

                    twoMonthAgoSecondaryTarget,

                    twoMonthAgoSecondaryAchieved,

                    twoMonthAgoReturn,

                    twoMonthAgoNetSecondary,

                    twoMonthAgoSecondaryPercent,

                    twoMonthAgoSale,

                    twoMonthAgoUnbilled // for now, unbilled tertiary value

            );

            monthlySecondaryModels.put(currentMonth, currentMonthSecondaryModel);

            monthlySecondaryModels.put(lastMonth, lastMonthSecondaryModel);

            monthlySecondaryModels.put(twoMonthsAgo, twoMonthAgoSecondaryModel);

            allRetailerMonthlyData.put(fofoId, monthlySecondaryModels);

//            brandwiseStock value price

            Map<String, BrandStockPrice> brandStockPriceMap = inventoryService.getBrandWiseStockValue(fofoId);

            fofoBrandStockPriceMap.put(fofoId,brandStockPriceMap);

            double totalStockPrice = brandStockPriceMap.values().stream().mapToDouble(x->x.getTotalValue()).sum();

            fofoTotalStockPriceMap.put(fofoId,totalStockPrice);

            Map<String, Double> brandMtdTertiaryAmount = fofoOrderItemRepository.selectSumAmountGroupByBrand(

                    currentMonthStartDate, currentMonthEndDate, fofoId);

            fofoBrandMtdTertiaryMap.put(fofoId,brandMtdTertiaryAmount);

            double totalMtdTertiaryAmount = brandMtdTertiaryAmount.values().stream().mapToDouble(Double::doubleValue).sum();

            fofoTotalMtdTertiaryMap.put(fofoId,totalMtdTertiaryAmount);

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

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

            List<BrandWiseModel> brandWiseMtdSecondary = orderRepository.selectAllBilledByCategoryOrderGroupByBrandFofoId(fofoId, currentMonthStartDate,Arrays.asList(10006,10009,10010));

            Map<String,Long> brandWiseMtdSecondaryMap = brandWiseMtdSecondary.stream().collect(Collectors.toMap(BrandWiseModel::getBrand,BrandWiseModel::getAmount));

            fofoBrandWiseMtdSecondaryMap.put(fofoId,brandWiseMtdSecondaryMap);

            long mtdTotalSecondary = brandWiseMtdSecondary.stream().mapToLong(x -> x.getAmount() != 0 ? x.getAmount() : 0L).sum();

            fofoTotalMtdSecondaryMap.put(fofoId,mtdTotalSecondary);

            //            generate investment info

            FofoStore fofoStore = fofoStoreRepository.selectByRetailerId(fofoId);

            float shortInvestment = partnerDailyInvestmentMap.get(fofoId) != null ? partnerDailyInvestmentMap.get(fofoId).getShortInvestment() : 0f;

            float agreedInvestment = partnerDailyInvestmentMap.get(fofoId) != null ? partnerDailyInvestmentMap.get(fofoId).getMinInvestment() : 0f;

            float investmentLevel = partnerDailyInvestmentMap.get(fofoId) != null

                    ? Math.abs(((shortInvestment - agreedInvestment) / agreedInvestment) * 100)

                    : 0f;

            List<Loan> fofoDefaultLoans = new ArrayList<>();

            if(defaultLoanMap != null){

                 fofoDefaultLoans  =  defaultLoanMap.get(fofoId);

                LOGGER.info("fofoDefaultLoans {}",fofoDefaultLoans);

            }

            float defaultLoanAmount = 0f;

            if(fofoDefaultLoans != null ){

                if (!fofoDefaultLoans.isEmpty()) {

                    defaultLoanAmount =  fofoDefaultLoans.stream().map(Loan::getPendingAmount).reduce(BigDecimal.ZERO, BigDecimal::add).floatValue(); // or .floatValue() directly

                }

            }

            List<Loan> activeLoans = loanRepository.selectAllActiveLoan(fofoId);

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

            float activeLoan = activeLoans.stream().map(Loan::getPendingAmount).reduce(BigDecimal.ZERO,BigDecimal::add).floatValue();

            float poValue = partnerDailyInvestmentMap.get(fofoId) != null ?  partnerDailyInvestmentMap.get(fofoId).getUnbilledAmount() : 0f;

            float poAndBilledValue = (float) (currentMonthNetSecondary + poValue);

            double todayRequiredDrr = rbmTargetService.calculateFofoIdTodayTarget(fofoId, currentMonthNetSecondary,LocalDate.now());

            double monthDay1Drr = rbmTargetService.calculateFofoIdTodayTarget(fofoId,0d,YearMonth.now().atDay(1));

            double gotDrrPercent = (todayRequiredDrr / monthDay1Drr) * 100;

            long drrPercentDisplay = Math.round(Math.abs(gotDrrPercent));

            int orderId = orderRepository.getLastOrderByFofoId(fofoId);

            // Determine alert level

            String alertLevel = "-";

            int lastPurchaseDays = 0;

            if (orderId != 0) {

                Order order = orderRepository.selectById(orderId);

                LOGGER.info("last billing order - {}",order);

                // Calculate the number of days since the last purchase (billing)

                lastPurchaseDays = (int) Duration.between(order.getCreateTimestamp().plusDays(1), LocalDateTime.now()).toDays();

                if (lastPurchaseDays >= 11) {

                    alertLevel = "Alert for Management";

                } else if (lastPurchaseDays >= 10) {

                    alertLevel = " Alert for RSM/SH";

                } else if (lastPurchaseDays >= 7) {

                    alertLevel = "Must be Billed";

                } else if (lastPurchaseDays >= 3) {

                    alertLevel = "OK";

                } else {

                    alertLevel = "OK";

                }

            }

            //investment modal set all related value

            FofoInvestmentModel fofoInvestmentModel = new FofoInvestmentModel();

            fofoInvestmentModel.setCounterPotential(fofoStore.getCounterPotential());

            fofoInvestmentModel.setShortInvestment(shortInvestment);

            fofoInvestmentModel.setDefaultLoan(defaultLoanAmount);

            fofoInvestmentModel.setInvestmentLevel(investmentLevel);

            fofoInvestmentModel.setActiveLoan(activeLoan);

            fofoInvestmentModel.setPoValue(poValue);

            fofoInvestmentModel.setPoAndBilled(poAndBilledValue);

            fofoInvestmentModel.setAgreedInvestment(agreedInvestment);

            fofoInvestmentModel.setWallet(partnerDailyInvestmentMap.get(fofoId) != null ? partnerDailyInvestmentMap.get(fofoId).getWalletAmount() : 0);

            fofoInvestmentModel.setMonthBeginingDrr(monthDay1Drr);

            fofoInvestmentModel.setRequiredDrr(todayRequiredDrr);

            fofoInvestmentModel.setDrrPercent(drrPercentDisplay);

            fofoInvestmentModel.setLastBillingDone(lastPurchaseDays);

            fofoInvestmentModel.setSlab(alertLevel);

            biInvestmentModelMap.put(fofoId, fofoInvestmentModel);

                String assessment = "";

                if(defaultLoanAmount > 0 ){

                    assessment = "Loan Default";

                }else if(investmentLevel <= 75 && defaultLoanAmount < 1){

                    assessment = "Low Invest";

                }else {

                    assessment = "-";

                }

                assessmentMap.put(fofoId,assessment);

                String zeroBilling = "";

                if(currentMonthNetSecondary <= 100000 ){

                    zeroBilling = "Zero Billing";

                }else {

                    zeroBilling = "-";

                }

                zeroBillingMap.put(fofoId,zeroBilling);

                float billingNeeded = 0f;

                if(drrPercentDisplay >= 110 ){

                    billingNeeded = (float) todayRequiredDrr;

                }else {

                    billingNeeded = 0f;

                }

                billingNeededMap.put(fofoId,billingNeeded);

                int counta = 0;

                if(defaultLoanAmount > 0 || investmentLevel <= 75 || currentMonthNetSecondary <= 100000 || drrPercentDisplay >= 110 ){

                    counta = 1;

                }else {

                    counta = 0;

                }

                countAMap.put(fofoId,counta);

        }

        LOGGER.info("Total BI Retailers processed: {}", biRetailerModelMap.size());

        //generate excel and sent to mail

        List<List<String>> headerGroup = new ArrayList<>();

        List<String> headers1 = Arrays.asList(

                "","","","",

                "Retailer Detail", "","", "", "", "", "", "", "", "","","","",

                twoMonthAgoStringValue, "", "", "", "", "", "",

                lastMonthStringValue, "", "", "", "", "", "",

                currentMonthStringValue, "", "", "", "", "", "",

                "","", "", "", "", "", "", "", "", "", "", "", "", "",

                "", "", "", "", "", "", "", "", "", "", "", "", "",

                "", "", "", "", "", "", "", "", "", "", "", "", ""

        );

        List<String> headers2 = Arrays.asList(

                "Assessment","Zero billing","Billing needed","Counta",

                "BM","Partner Id","Link","Wallet Date","Creation Date","Code","Area",  "City", "Store Name", "Status","Category","Sales Manager", "RBM",

                "Secondary Target", "Secondary Achieved", "Returns", "Net Secondary", "Secondary %",

                "Tertiary Sale", "Unbilled",

                "Secondary Target", "Secondary Achieved", "Returns", "Net Secondary", "Secondary %",

                "Tertiary Sale", "Unbilled",

                "Secondary Target", "Secondary Achieved", "Returns", "Net Secondary", "Secondary %",

                "Tertiary Sale", "Unbilled",

                "Counter Potential", "Short investment", "Default", "INVESTMENT LEVEL", "Loan", "PO value", "Agreed investment",

                "Wallet", "po+bill", "MONTH BEGINNING DRR", "REQ DRR", "Drr %", "Last billing Done", "Slab",

              "Total Stock",  "Apple","Xiaomi", "Vivo", "Tecno", "Samsung", "Realme", "Oppo", "OnePlus", "POCO", "Lava", "Itel", "Almost New",

              "Total Secondary", "Apple", "Xiaomi", "Vivo", "Tecno", "Samsung", "Realme", "Oppo", "OnePlus", "POCO", "Lava", "Itel", "Almost New",

              "Total Tertiary",  "Apple", "Xiaomi", "Vivo", "Tecno", "Samsung", "Realme", "Oppo", "OnePlus", "POCO", "Lava", "Itel", "Almost New"

        );

        headerGroup.add(headers1);

        headerGroup.add(headers2);

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

        for (Map.Entry<Integer, BIRetailerModel> entry : biRetailerModelMap.entrySet()) {

            Integer fofoId = entry.getKey();

            User user = userRepository.selectById(fofoId);

            LocalDateTime walletCreationDate = userWalletHistoryRepository.selectFirstCreatedDate(fofoId);

            BIRetailerModel retailer = entry.getValue();

            Map<YearMonth, BiSecondaryModel> monthlyData = allRetailerMonthlyData.get(fofoId);

            BiSecondaryModel current = monthlyData.getOrDefault(YearMonth.now(), new BiSecondaryModel(0,0,0,0,0,0,0));

            BiSecondaryModel last = monthlyData.getOrDefault(YearMonth.now().minusMonths(1), new BiSecondaryModel(0,0,0,0,0,0,0));

            BiSecondaryModel twoAgo = monthlyData.getOrDefault(YearMonth.now().minusMonths(2), new BiSecondaryModel(0,0,0,0,0,0,0));

            List<Object> row = new ArrayList<>();

            row.addAll(Arrays.asList(

                    assessmentMap.get(fofoId),zeroBillingMap.get(fofoId),billingNeededMap.get(fofoId),countAMap.get(fofoId),

                    retailer.getBmName(),fofoId ,"https://partners.smartdukaan.com/partnerPerformance?fofoId="+fofoId,walletCreationDate,user.getCreateTimestamp(), retailer.getCode(), retailer.getArea(), retailer.getCity(),  retailer.getStoreName(), retailer.getStatus(),

                    retailer.getCategory(),  retailer.getSalesManager(), retailer.getRbm()

            ));

            // Two Months Ago

            row.addAll(Arrays.asList(

                    twoAgo.getSecondaryTarget(),

                    twoAgo.getSecondaryAchieved(),

                    twoAgo.getSecondaryReturn(),

                    twoAgo.getNetSecondary(),

                    twoAgo.getSecondaryAchievedPercent()+"%",

                    twoAgo.getTertiary(),

                    twoAgo.getTertiaryUnBilled()

            ));

            // Last Month

            row.addAll(Arrays.asList(

                    last.getSecondaryTarget(),

                    last.getSecondaryAchieved(),

                    last.getSecondaryReturn(),

                    last.getNetSecondary(),

                    last.getSecondaryAchievedPercent()+"%",

                    last.getTertiary(),

                    last.getTertiaryUnBilled()

            ));

            // Current Month

            row.addAll(Arrays.asList(

                    current.getSecondaryTarget(),

                    current.getSecondaryAchieved(),

                    current.getSecondaryReturn(),

                    current.getNetSecondary(),

                    current.getSecondaryAchievedPercent()+"%",

                    current.getTertiary(),

                    current.getTertiaryUnBilled()

            ));

            FofoInvestmentModel fofoInvestmentModelValue = biInvestmentModelMap.get(fofoId);

            if(fofoInvestmentModelValue != null){

                row.addAll(Arrays.asList(

                        fofoInvestmentModelValue.getCounterPotential(),

                        fofoInvestmentModelValue.getShortInvestment(),

                        fofoInvestmentModelValue.getDefaultLoan(),

                        fofoInvestmentModelValue.getInvestmentLevel(),

                       "-"+fofoInvestmentModelValue.getActiveLoan(),

                        fofoInvestmentModelValue.getPoValue(),

                        fofoInvestmentModelValue.getAgreedInvestment(),

                        fofoInvestmentModelValue.getWallet(),

                        fofoInvestmentModelValue.getPoAndBilled(),

                        fofoInvestmentModelValue.getMonthBeginingDrr(),

                        fofoInvestmentModelValue.getRequiredDrr(),

                        fofoInvestmentModelValue.getDrrPercent()+"%",

                        fofoInvestmentModelValue.getLastBillingDone(),

                        fofoInvestmentModelValue.getSlab()

                ));

            }else {

                row.addAll(Arrays.asList(

                        "-","-","-","-","-","-","-","-","-","-","-",""

                ));

            }

            Map<String, BrandStockPrice> brandStockMap = fofoBrandStockPriceMap.get(fofoId);