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

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

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

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

import com.spice.profitmandi.dao.entity.catalog.TagListing;

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.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.catalog.TagListingRepository;

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

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

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

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

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.format.DateTimeFormatter;

import java.time.temporal.ChronoUnit;

import java.util.*;

import java.util.stream.Collectors;

@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

    SaholicInventoryService saholicInventoryService;

    @Autowired

    WalletService walletService;

    @Autowired

    RbmTargetService rbmTargetService;

    @Autowired

    PartnerStatsService partnerStatsService;

    @Autowired

    AgeingService ageingService;

    @Autowired

    TagListingRepository tagListingRepository;

    @Autowired

    UserWalletHistoryRepository userWalletHistoryRepository;

    public void test() throws Exception {

        generateBiReport();

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

    }

    public void generateBiReport() throws Exception {

        System.out.println("bi report started");

        this.generateBiReportExcel();

        System.out.println("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");

            LOGGER.info("partnerName11- {}",partnerName);

            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;

        LocalDateTime previousDay;

        if (LocalDate.now().getDayOfMonth() == 1) {

            // If today is 1st, go to yesterday (i.e., last day of previous month)

            startOfToday = LocalDate.now().minusDays(1).atStartOfDay();

            previousDay = startOfToday.with(LocalTime.MAX);

        } else {

            // Otherwise, use today

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

            previousDay = startOfToday.with(LocalTime.MAX).minusDays(1);

        }

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

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

/*        List<Integer> retailerIds = Arrays.asList(175139247,175139660);

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

        //partner daily investment

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

        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), previousDay.toLocalDate());

        if (!partnerDailyInvestments.isEmpty()) {

            partnerDailyInvestmentMap = partnerDailyInvestments.stream()

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

        }

    //  this month return data

        YearMonth currentMonth;

        LocalDateTime currentMonthStartDate;

        LocalDateTime currentMonthEndDate;

        if (LocalDate.now().getDayOfMonth() == 1) {

            // If today is the 1st, use previous month

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

            currentMonthStartDate = currentMonth.atDay(1).atStartOfDay();

            currentMonthEndDate = currentMonth.atEndOfMonth().atTime(23, 59, 59);

        } else {

            // Otherwise, use this month up to yesterday

            currentMonth = YearMonth.now();

            currentMonthStartDate = currentMonth.atDay(1).atStartOfDay();

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

        }

        String currentMonthStringValue = String.valueOf(currentMonth);

        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.selectAllRefundOrderDatesBetween(currentMonthStartDate, currentMonthEndDate);

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

        List<ReturnOrderInfoModel> yesterdayReturnOrderInfoModels = returnOrderInfoRepository.selectAllByBetweenDate(previousDay.toLocalDate().atStartOfDay(), previousDay);

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

        List<Order> yesterdayRtoRefundOrders = orderRepository.selectAllRefundOrderDatesBetween(previousDay.toLocalDate().atStartOfDay(), previousDay);

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

        List<ReturnOrderInfoModel> dayBeforeYesterdayReturnOrderInfoModels = returnOrderInfoRepository.selectAllByBetweenDate(previousDay.toLocalDate().atStartOfDay().minusDays(1), previousDay.minusDays(1));

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

        List<Order> dayBeforeYesterdayRtoRefundOrders = orderRepository.selectAllRefundOrderDatesBetween(previousDay.toLocalDate().atStartOfDay().minusDays(1), previousDay.minusDays(1));

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

        //  last month return data

        YearMonth lastMonth = currentMonth.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.selectAllRefundOrderDatesBetween(lastMontStartDate, lastMonthEndDate);

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

    //  twoMonthsAgo return data

        YearMonth twoMonthsAgo = currentMonth.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()))));

        List<Order> twoMonthRtoRefundOrders = orderRepository.selectAllRefundOrderDatesBetween(twoMonthsAgoStartDate, twoMonthsAgoEndDate);

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

        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,Long> fofoYesterdaySecondaryMap = new HashMap<>();

        Map<Integer,Long> fofoDayBeforeYesterdaySecondaryMap = 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, currentMonth);

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

            }

            String retailerStatus = "";

            FofoStore fofoStore1 = fofoStoreRepository.selectByRetailerId(fofoId);

            if(!fofoStore1.isActive()){

                retailerStatus = "INACTIVE";

            }else{

                retailerStatus =  String.valueOf(fofoStore1.getActivationType());

            }

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

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

            biRetailerModel.setStatus(retailerStatus);

            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(currentMonth, fofoId) != null ? monthlyTargetRepository.selectByDateAndFofoId(currentMonth, 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), previousDay).stream().collect(Collectors.toMap(x -> x.getId(), x -> x.getAmount()));

            //yesterday secondary

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

                    previousDay.toLocalDate().atStartOfDay().minusDays(1), previousDay.minusDays(1)).stream().collect(Collectors.toMap(x -> x.getId(), x -> x.getAmount()));

            double dayBeforeYesterdayAfterReturnSecondary = dayBeforeYesterdaySecondary.getOrDefault(fofoId,0d) - (dayBeforeYesterdayReturnOrderInfoModelMap.getOrDefault(fofoId,0l) + dayBeforeYesterdayRtoRefundOrderMap.getOrDefault(fofoId,0l));

            fofoDayBeforeYesterdaySecondaryMap.put(fofoId, (long) dayBeforeYesterdayAfterReturnSecondary);

            //day before secondary

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

                    previousDay.toLocalDate().atStartOfDay(), previousDay).stream().collect(Collectors.toMap(x -> x.getId(), x -> x.getAmount()));

            double yesterDayAfterReturnSecondary = yesterDaySecondary.getOrDefault(fofoId,0d) - (yesterdayReturnOrderInfoModelMap.getOrDefault(fofoId,0l) + yesterdayRtoRefundOrderMap.getOrDefault(fofoId,0l));

            fofoYesterdaySecondaryMap.put(fofoId, (long) yesterDayAfterReturnSecondary);

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

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

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

            //retrunInfo

            List<BrandWiseReturnInfo> brandWiseReturnInfos = returnOrderInfoRepository.selectAllBrandWiseByBetweenDate(currentMonthStartDate,currentMonthEndDate.plusDays(1),fofoId);

            Map<String,Double> brandWiseReturnInfoMap = brandWiseReturnInfos.stream().collect(Collectors.toMap(BrandWiseReturnInfo::getBrand, x->x.getReturnAmount()));

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

            //Rto retrunInfo

            List<BrandWiseReturnInfo> brandWiseRTOReturnInfos = returnOrderInfoRepository.selectAllBrandWiseRTORefundByBetweenDate(currentMonthStartDate,currentMonthEndDate.plusDays(1),fofoId);

            Map<String,Double> brandWiseRTOReturnInfoMap = brandWiseRTOReturnInfos.stream().collect(Collectors.toMap(BrandWiseReturnInfo::getBrand, x->x.getReturnAmount()));

            // Step 1: Get union of all brand keys

            Set<String> allBrands = new HashSet<>();

            allBrands.addAll(brandWiseMtdSecondaryMap.keySet());

            allBrands.addAll(brandWiseReturnInfoMap.keySet());

            allBrands.addAll(brandWiseRTOReturnInfoMap.keySet());

            // Step 2: Calculate net secondary for each brand

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

            for (String brand : allBrands) {

                Long billedAmount = brandWiseMtdSecondaryMap.getOrDefault(brand, 0L);

                Double returnAmount = brandWiseReturnInfoMap.getOrDefault(brand, 0d);

                Double rtoReturnAmount = brandWiseRTOReturnInfoMap.getOrDefault(brand, 0d);

                double netSecondary = billedAmount - (returnAmount + rtoReturnAmount);

                brandWiseMtdNetSecondaryMap.put(brand, (long) Math.round(netSecondary));

            }

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

            fofoBrandWiseMtdSecondaryMap.put(fofoId,brandWiseMtdNetSecondaryMap);

            long mtdTotalSecondary = brandWiseMtdNetSecondaryMap.values().stream().mapToLong(Long::longValue).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()) {

                    for (Loan entry : fofoDefaultLoans) {

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

                        double amount = loanStatements.stream().map(x -> x.getAmount()).collect(Collectors.summingDouble(x -> x.doubleValue()));

                        defaultLoanAmount += amount;

                    }

                }

            }

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

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

            float activeLoan = 0f;

            for (Loan entry : activeLoans) {

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

                double pendingAmount = loanStatements.stream().map(x -> x.getAmount()).collect(Collectors.summingDouble(x -> x.doubleValue()));

                activeLoan += pendingAmount;

            }

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

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

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

            double todayRequiredDrr = 0;

            if(monthDay1Drr != 0d){

                todayRequiredDrr = rbmTargetService.calculateFofoIdTodayTarget(fofoId, currentMonthNetSecondary,startOfToday.toLocalDate());

            }

            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 >= 0){

                    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 && todayRequiredDrr > 0 ){

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