Rev 36986 | Blame | Compare with Previous | Last modification | View Log | RSS feed
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.IdAmountModel;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.dao.service.solr.FofoSolr;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.time.temporal.TemporalAdjusters;import java.time.DayOfWeek;import java.util.*;import java.util.stream.Collectors;import static java.util.stream.Collectors.toList;@Component@Transactional(rollbackFor = {Throwable.class, ProfitMandiBusinessException.class})public class ScheduledTasksTest {private static final Logger LOGGER = LogManager.getLogger(ScheduledTasksTest.class);@AutowiredTransactionRepository transactionRepository;@Autowired@Qualifier(value = "googleMailSender")private JavaMailSender googleMailSender;@AutowiredLoanRepository loanRepository;@AutowiredSDCreditService sdCreditService;@AutowiredSmartCartSuggestionRepository smartCartSuggestionRepository;@AutowiredUserRepository userRepository;@AutowiredCsService csService;@AutowiredRbmRatingRepository rbmRatingRepository;@Autowiredprivate JavaMailSender gmailRelaySender;@AutowiredSalesRatingRepository salesRatingRepository;@AutowiredFofoStoreRepository fofoStoreRepository;@AutowiredSmartCartService smartCartService;@AutowiredRetailerService retailerService;@AutowiredASTRepository astRepository;@AutowiredAuthRepository authRepository;@AutowiredStateRepository stateRepository;@AutowiredMonthlyTargetRepository monthlyTargetRepository;@AutowiredPartnerTypeChangeService partnerTypeChangeService;@AutowiredReturnOrderInfoRepository returnOrderInfoRepository;@AutowiredOrderRepository orderRepository;@AutowiredFofoOrderItemRepository fofoOrderItemRepository;@AutowiredInventoryService inventoryService;@AutowiredUserWalletRepository userWalletRepository;@AutowiredLoanStatementRepository loanStatementRepository;@AutowiredActivatedImeiRepository activatedImeiRepository;@AutowiredPartnerDailyInvestmentRepository partnerDailyInvestmentRepository;@AutowiredSaholicInventoryService saholicInventoryService;@AutowiredWalletService walletService;@AutowiredRbmTargetService rbmTargetService;@AutowiredPartnerStatsService partnerStatsService;@AutowiredAgeingService ageingService;@AutowiredTagListingRepository tagListingRepository;@AutowiredUserWalletHistoryRepository userWalletHistoryRepository;@AutowiredFofoSolr fofoSolr;public void test() throws Exception {fofoSolr.populateTagItems();System.out.println("test end");}public void generateBiReport() throws Exception {this.generateBiReportExcel();}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 entryBigDecimal adjustAmount = BigDecimal.valueOf(amountSum).negate(); // or multiply by -1LoanStatement 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 = gmailRelaySender.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);gmailRelaySender.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 nulltransaction = 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 {// Weekly date range: Previous Monday to SundayLocalDate today = LocalDate.now();LocalDate previousMonday = today.with(TemporalAdjusters.previous(DayOfWeek.MONDAY));LocalDate previousSunday = previousMonday.plusDays(6);LocalDateTime startOfWeek = previousMonday.atStartOfDay();LocalDateTime endOfWeek = previousSunday.atTime(23, 59, 59);String[] bcc = {"tarun.verma@smartdukaan.com"};DateTimeFormatter dateFormatter = DateTimeFormatter.ofPattern("dd-MMM-yyyy");// Get all RBM usersList<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 the weekList<RbmRating> feedbackList = rbmRatingRepository.selectByRbmIdsAndDateRange(rbmIds, startOfWeek, endOfWeek);if (feedbackList.isEmpty()) {LOGGER.info("No feedback entries found for RBMs for the week.");return;}// Sort feedback by createTimeStamp DESCfeedbackList.sort((a, b) -> b.getCreateTimeStamp().compareTo(a.getCreateTimeStamp()));// Fetch and map FOFO (partner) namesMap<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 lookupMap<Integer, String> rbmNameMap = authUsers.stream().collect(Collectors.toMap(AuthUser::getId, AuthUser::getFullName));// Calculate RBM statistics: average rating and unique partner countMap<Integer, List<RbmRating>> feedbackByRbm = feedbackList.stream().collect(Collectors.groupingBy(RbmRating::getRbmId));List<RbmWeeklyStats> rbmStatsList = new ArrayList<>();for (Map.Entry<Integer, List<RbmRating>> entry : feedbackByRbm.entrySet()) {int rbmId = entry.getKey();List<RbmRating> ratings = entry.getValue();double avgRating = ratings.stream().mapToInt(RbmRating::getRating).average().orElse(0.0);long uniquePartnerCount = ratings.stream().map(RbmRating::getFofoId).distinct().count();String rbmName = rbmNameMap.getOrDefault(rbmId, "Unknown RBM");rbmStatsList.add(new RbmWeeklyStats(rbmId, rbmName, avgRating, (int) uniquePartnerCount));}// Find max partner count for normalizationint maxPartnerCount = rbmStatsList.stream().mapToInt(RbmWeeklyStats::getPartnerCount).max().orElse(1);// Calculate combined score: 65% partner count + 35% rating// Score = (0.65 × partnerCount/maxPartnerCount) + (0.35 × avgRating/5)for (RbmWeeklyStats stats : rbmStatsList) {double partnerScore = (double) stats.getPartnerCount() / maxPartnerCount;double ratingScore = stats.getAvgRating() / 5.0;double combinedScore = (0.65 * partnerScore) + (0.35 * ratingScore);stats.setCombinedScore(combinedScore);}// Sort by combined score DESC and assign rankrbmStatsList.sort((a, b) -> Double.compare(b.getCombinedScore(), a.getCombinedScore()));for (int i = 0; i < rbmStatsList.size(); i++) {rbmStatsList.get(i).setRank(i + 1);}// Create map for quick lookup of stats by RBM IDMap<Integer, RbmWeeklyStats> rbmStatsMap = rbmStatsList.stream().collect(Collectors.toMap(RbmWeeklyStats::getRbmId, s -> s));// Generate HTML contentStringBuilder emailContent = new StringBuilder();emailContent.append("<html><body>");emailContent.append("<p>Dear Team,</p>");emailContent.append("<p>Here is the <b>Weekly RBM Rating and Feedback Summary</b> for the week: <b>").append(previousMonday.format(dateFormatter)).append(" to ").append(previousSunday.format(dateFormatter)).append("</b></p>");// RBM Ranking Summary TableemailContent.append("<h3>RBM Weekly Rankings</h3>");emailContent.append("<p style='font-size: 12px; color: #666;'>Rank = 65% Partner Count + 35% Avg Rating</p>");emailContent.append("<table border='1' cellspacing='0' cellpadding='5' style='border-collapse: collapse;'>");emailContent.append("<tr style='background-color: #4CAF50; color: white;'>").append("<th>Rank</th>").append("<th>RBM Name</th>").append("<th>Partner Count</th>").append("<th>Avg Rating</th>").append("<th>Score</th>").append("</tr>");// Already sorted by rank (combined score DESC)for (RbmWeeklyStats stats : rbmStatsList) {emailContent.append("<tr>").append("<td style='text-align: center; font-weight: bold;'>").append(stats.getRank()).append("</td>").append("<td>").append(stats.getRbmName()).append("</td>").append("<td style='text-align: center;'>").append(stats.getPartnerCount()).append("</td>").append("<td style='text-align: center;'>").append(String.format("%.2f", stats.getAvgRating())).append("</td>").append("<td style='text-align: center;'>").append(String.format("%.2f", stats.getCombinedScore())).append("</td>").append("</tr>");}emailContent.append("</table>");// Detailed Feedback TableemailContent.append("<br><h3>Detailed Feedback</h3>");emailContent.append("<table border='1' cellspacing='0' cellpadding='5' style='border-collapse: collapse;'>");emailContent.append("<tr style='background-color: #2196F3; color: white;'>").append("<th>RBM Name</th>").append("<th>Rank</th>").append("<th>Partner Name</th>").append("<th>Rating</th>").append("<th>Comment</th>").append("<th>Date</th>").append("</tr>");for (RbmRating rating : feedbackList) {int rbmId = rating.getRbmId();String rbmName = rbmNameMap.getOrDefault(rbmId, "Unknown RBM");String partnerName = fofoNameMap.getOrDefault(rating.getFofoId(), "Unknown Partner");RbmWeeklyStats stats = rbmStatsMap.get(rbmId);emailContent.append("<tr>").append("<td>").append(rbmName).append("</td>").append("<td style='text-align: center; font-weight: bold;'>").append(stats != null ? stats.getRank() : "-").append("</td>").append("<td>").append(partnerName).append("</td>").append("<td style='text-align: center;'>").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 = "Weekly RBM Feedback Summary - " + previousMonday.format(dateFormatter) + " to " + previousSunday.format(dateFormatter);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("Weekly RBM feedback summary email sent for week: {} to {}", previousMonday, previousSunday);}// Inner class to hold RBM weekly statisticsprivate static class RbmWeeklyStats {private final int rbmId;private final String rbmName;private final double avgRating;private final int partnerCount;private double combinedScore;private int rank;public RbmWeeklyStats(int rbmId, String rbmName, double avgRating, int partnerCount) {this.rbmId = rbmId;this.rbmName = rbmName;this.avgRating = avgRating;this.partnerCount = partnerCount;}public int getRbmId() { return rbmId; }public String getRbmName() { return rbmName; }public double getAvgRating() { return avgRating; }public int getPartnerCount() { return partnerCount; }public double getCombinedScore() { return combinedScore; }public void setCombinedScore(double combinedScore) { this.combinedScore = combinedScore; }public int getRank() { return rank; }public void setRank(int rank) { this.rank = rank; }}public void sendSalesFeedbackSummaryEmail() throws MessagingException, ProfitMandiBusinessException, IOException {// Weekly date range: Previous Monday to SundayLocalDate today = LocalDate.now();LocalDate previousMonday = today.with(TemporalAdjusters.previous(DayOfWeek.MONDAY));LocalDate previousSunday = previousMonday.plusDays(6);LocalDateTime startOfWeek = previousMonday.atStartOfDay();LocalDateTime endOfWeek = previousSunday.atTime(23, 59, 59);String[] bcc = {"tarun.verma@smartdukaan.com"};DateTimeFormatter dateFormatter = DateTimeFormatter.ofPattern("dd-MMM-yyyy");// Get all Sales usersList<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 Sales L1 for the weekList<SalesRating> feedbackList = salesRatingRepository.selectBySalesL1IdsAndDateRange(salesL1Ids, startOfWeek, endOfWeek);if (feedbackList.isEmpty()) {LOGGER.info("No feedback entries found for Sales for the week.");return;}// Sort feedback by createTimeStamp DESCfeedbackList.sort((a, b) -> b.getCreateTimeStamp().compareTo(a.getCreateTimeStamp()));// Fetch and map FOFO (partner) namesMap<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 Sales L1 ID to name for quick lookupMap<Integer, String> salesL1NameMap = authUsers.stream().collect(Collectors.toMap(AuthUser::getId, AuthUser::getFullName));// Calculate Sales L1 statistics: average rating and unique partner countMap<Integer, List<SalesRating>> feedbackBySales = feedbackList.stream().collect(Collectors.groupingBy(SalesRating::getSalesL1Id));List<SalesWeeklyStats> salesStatsList = new ArrayList<>();for (Map.Entry<Integer, List<SalesRating>> entry : feedbackBySales.entrySet()) {int salesL1Id = entry.getKey();List<SalesRating> ratings = entry.getValue();double avgRating = ratings.stream().mapToInt(SalesRating::getRating).average().orElse(0.0);long uniquePartnerCount = ratings.stream().map(SalesRating::getFofoId).distinct().count();String salesL1Name = salesL1NameMap.getOrDefault(salesL1Id, "Unknown Sales Person");salesStatsList.add(new SalesWeeklyStats(salesL1Id, salesL1Name, avgRating, (int) uniquePartnerCount));}// Find max partner count for normalizationint maxPartnerCount = salesStatsList.stream().mapToInt(SalesWeeklyStats::getPartnerCount).max().orElse(1);// Calculate combined score: 65% partner count + 35% rating// Score = (0.65 × partnerCount/maxPartnerCount) + (0.35 × avgRating/5)for (SalesWeeklyStats stats : salesStatsList) {double partnerScore = (double) stats.getPartnerCount() / maxPartnerCount;double ratingScore = stats.getAvgRating() / 5.0;double combinedScore = (0.65 * partnerScore) + (0.35 * ratingScore);stats.setCombinedScore(combinedScore);}// Sort by combined score DESC and assign ranksalesStatsList.sort((a, b) -> Double.compare(b.getCombinedScore(), a.getCombinedScore()));for (int i = 0; i < salesStatsList.size(); i++) {salesStatsList.get(i).setRank(i + 1);}// Create map for quick lookup of stats by Sales L1 IDMap<Integer, SalesWeeklyStats> salesStatsMap = salesStatsList.stream().collect(Collectors.toMap(SalesWeeklyStats::getSalesL1Id, s -> s));// Generate HTML contentStringBuilder emailContent = new StringBuilder();emailContent.append("<html><body>");emailContent.append("<p>Dear Team,</p>");emailContent.append("<p>Here is the <b>Weekly Sales L1 Rating and Feedback Summary</b> for the week: <b>").append(previousMonday.format(dateFormatter)).append(" to ").append(previousSunday.format(dateFormatter)).append("</b></p>");// Sales L1 Ranking Summary TableemailContent.append("<h3>Sales L1 Weekly Rankings</h3>");emailContent.append("<p style='font-size: 12px; color: #666;'>Rank = 65% Partner Count + 35% Avg Rating</p>");emailContent.append("<table border='1' cellspacing='0' cellpadding='5' style='border-collapse: collapse;'>");emailContent.append("<tr style='background-color: #4CAF50; color: white;'>").append("<th>Rank</th>").append("<th>Sales L1 Name</th>").append("<th>Partner Count</th>").append("<th>Avg Rating</th>").append("<th>Score</th>").append("</tr>");// Already sorted by rank (combined score DESC)for (SalesWeeklyStats stats : salesStatsList) {emailContent.append("<tr>").append("<td style='text-align: center; font-weight: bold;'>").append(stats.getRank()).append("</td>").append("<td>").append(stats.getSalesL1Name()).append("</td>").append("<td style='text-align: center;'>").append(stats.getPartnerCount()).append("</td>").append("<td style='text-align: center;'>").append(String.format("%.2f", stats.getAvgRating())).append("</td>").append("<td style='text-align: center;'>").append(String.format("%.2f", stats.getCombinedScore())).append("</td>").append("</tr>");}emailContent.append("</table>");// Detailed Feedback TableemailContent.append("<br><h3>Detailed Feedback</h3>");emailContent.append("<table border='1' cellspacing='0' cellpadding='5' style='border-collapse: collapse;'>");emailContent.append("<tr style='background-color: #2196F3; color: white;'>").append("<th>Sales L1 Name</th>").append("<th>Rank</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) {int salesL1Id = rating.getSalesL1Id();String salesL1Name = salesL1NameMap.getOrDefault(salesL1Id, "Unknown Sales Person");String partnerName = fofoNameMap.getOrDefault(rating.getFofoId(), "Unknown Partner");SalesWeeklyStats stats = salesStatsMap.get(salesL1Id);PartnerType partnerType = partnerTypeChangeService.getTypeOnDate(rating.getFofoId(), LocalDate.now());emailContent.append("<tr>").append("<td>").append(salesL1Name).append("</td>").append("<td style='text-align: center; font-weight: bold;'>").append(stats != null ? stats.getRank() : "-").append("</td>").append("<td>").append(partnerName).append("</td>").append("<td>").append(partnerType).append("</td>").append("<td style='text-align: center;'>").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 = "Weekly Sales L1 Feedback Summary - " + previousMonday.format(dateFormatter) + " to " + previousSunday.format(dateFormatter);List<String> sendTo = new ArrayList<>();sendTo.add("sm@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("Weekly Sales L1 feedback summary email sent for week: {} to {}", previousMonday, previousSunday);}// Inner class to hold Sales L1 weekly statisticsprivate static class SalesWeeklyStats {private final int salesL1Id;private final String salesL1Name;private final double avgRating;private final int partnerCount;private double combinedScore;private int rank;public SalesWeeklyStats(int salesL1Id, String salesL1Name, double avgRating, int partnerCount) {this.salesL1Id = salesL1Id;this.salesL1Name = salesL1Name;this.avgRating = avgRating;this.partnerCount = partnerCount;}public int getSalesL1Id() { return salesL1Id; }public String getSalesL1Name() { return salesL1Name; }public double getAvgRating() { return avgRating; }public int getPartnerCount() { return partnerCount; }public double getCombinedScore() { return combinedScore; }public void setCombinedScore(double combinedScore) { this.combinedScore = combinedScore; }public int getRank() { return rank; }public void setRank(int rank) { this.rank = rank; }}public Map<String, Set<Integer>> generateBiReportHierarchyWise() throws Exception{List<Integer> categoryIds = Arrays.asList(ProfitMandiConstants.TICKET_CATEGORY_RBM, ProfitMandiConstants.TICKET_CATEGORY_SALES,ProfitMandiConstants.TICKET_CATEGORY_ABM,ProfitMandiConstants.TICKET_CATEGORY_BUSINESSINTELLIGENT);Map<String, Set<Integer>> storeGuyEntry = csService.getAuthUserPartnerIdMappingByCategoryIds(categoryIds, false);return storeGuyEntry;}public void generateBiReportExcel() throws Exception {long __biReportStartMs = System.currentTimeMillis();LOGGER.info("[BI_REPORT] START batch-optimized generateBiReportExcel at {}", LocalDateTime.now());LocalDateTime startOfToday;LocalDateTime previousDay;if (LocalDate.now().getDayOfMonth() == 1) {startOfToday = LocalDate.now().minusDays(1).atStartOfDay();previousDay = startOfToday.with(LocalTime.MAX);} else {startOfToday = LocalDate.now().atStartOfDay();previousDay = startOfToday.with(LocalTime.MAX).minusDays(1);}Map<Integer, CustomRetailer> customRetailers = retailerService.getAllFofoRetailersInternalFalse();List<Integer> retailerIds = customRetailers.values().stream().filter(retailer -> {String storeCode = retailer.getCode();return !storeCode.equalsIgnoreCase("UPGBN640") && !storeCode.equalsIgnoreCase("HRYN039");}).map(CustomRetailer::getPartnerId).collect(Collectors.toList());Set<Integer> retailerIdSet = new HashSet<>(retailerIds);//partner daily investmentList<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(PartnerDailyInvestment::getFofoId, x -> x));}YearMonth currentMonth;LocalDateTime currentMonthStartDate;LocalDateTime currentMonthEndDate;if (LocalDate.now().getDayOfMonth() == 1) {currentMonth = YearMonth.now().minusMonths(1);currentMonthStartDate = currentMonth.atDay(1).atStartOfDay();currentMonthEndDate = currentMonth.atEndOfMonth().atTime(23, 59, 59);} else {currentMonth = YearMonth.now();currentMonthStartDate = currentMonth.atDay(1).atStartOfDay();currentMonthEndDate = LocalDate.now().minusDays(1).atTime(23, 59, 59);}String currentMonthStringValue = String.valueOf(currentMonth);YearMonth lastMonth = currentMonth.minusMonths(1);String lastMonthStringValue = String.valueOf(lastMonth);LocalDateTime lastMontStartDate = lastMonth.atDay(1).atStartOfDay();LocalDateTime lastMonthEndDate = lastMonth.atEndOfMonth().atTime(23, 59, 59);YearMonth twoMonthsAgo = currentMonth.minusMonths(2);String twoMonthAgoStringValue = String.valueOf(twoMonthsAgo);LocalDateTime twoMonthsAgoStartDate = twoMonthsAgo.atDay(1).atStartOfDay();LocalDateTime twoMonthsAgoEndDate = twoMonthsAgo.atEndOfMonth().atTime(23, 59, 59);LocalDateTime dayBeforeStart = previousDay.toLocalDate().atStartOfDay().minusDays(1);LocalDateTime dayBeforeEnd = previousDay.minusDays(1);LocalDateTime yesterdayStart = previousDay.toLocalDate().atStartOfDay();// Returns / RTO — already batchedMap<Integer, Long> currentMonthPartnerReturnOrderInfoModelMap = returnOrderInfoRepository.selectAllByBetweenDate(currentMonthStartDate, currentMonthEndDate).stream().collect(Collectors.groupingBy(ReturnOrderInfoModel::getRetailerId, Collectors.summingLong(x -> Math.round(x.getRefundAmount()))));Map<Integer, Long> currentMonthRtoRefundOrderMap = orderRepository.selectAllRefundOrderDatesBetween(currentMonthStartDate, currentMonthEndDate).stream().collect(Collectors.groupingBy(Order::getRetailerId, Collectors.summingLong(x -> Math.round(x.getTotalAmount()))));Map<Integer, Long> yesterdayReturnOrderInfoModelMap = returnOrderInfoRepository.selectAllByBetweenDate(yesterdayStart, previousDay).stream().collect(Collectors.groupingBy(ReturnOrderInfoModel::getRetailerId, Collectors.summingLong(x -> Math.round(x.getRefundAmount()))));Map<Integer, Long> yesterdayRtoRefundOrderMap = orderRepository.selectAllRefundOrderDatesBetween(yesterdayStart, previousDay).stream().collect(Collectors.groupingBy(Order::getRetailerId, Collectors.summingLong(x -> Math.round(x.getTotalAmount()))));Map<Integer, Long> dayBeforeYesterdayReturnOrderInfoModelMap = returnOrderInfoRepository.selectAllByBetweenDate(dayBeforeStart, dayBeforeEnd).stream().collect(Collectors.groupingBy(ReturnOrderInfoModel::getRetailerId, Collectors.summingLong(x -> Math.round(x.getRefundAmount()))));Map<Integer, Long> dayBeforeYesterdayRtoRefundOrderMap = orderRepository.selectAllRefundOrderDatesBetween(dayBeforeStart, dayBeforeEnd).stream().collect(Collectors.groupingBy(Order::getRetailerId, Collectors.summingLong(x -> Math.round(x.getTotalAmount()))));Map<Integer, Long> lastMonthPartnerReturnOrderInfoModelMap = returnOrderInfoRepository.selectAllByBetweenDate(lastMontStartDate, lastMonthEndDate).stream().collect(Collectors.groupingBy(ReturnOrderInfoModel::getRetailerId, Collectors.summingLong(x -> Math.round(x.getRefundAmount()))));Map<Integer, Long> lastMonthRtoRefundOrderMap = orderRepository.selectAllRefundOrderDatesBetween(lastMontStartDate, lastMonthEndDate).stream().collect(Collectors.groupingBy(Order::getRetailerId, Collectors.summingLong(x -> Math.round(x.getTotalAmount()))));Map<Integer, Long> twoMonthAgoPartnerReturnOrderInfoModelMap = returnOrderInfoRepository.selectAllByBetweenDate(twoMonthsAgoStartDate, twoMonthsAgoEndDate).stream().collect(Collectors.groupingBy(ReturnOrderInfoModel::getRetailerId, Collectors.summingLong(x -> Math.round(x.getRefundAmount()))));Map<Integer, Long> twoMonthAgoRtoRefundOrderMap = orderRepository.selectAllRefundOrderDatesBetween(twoMonthsAgoStartDate, twoMonthsAgoEndDate).stream().collect(Collectors.groupingBy(Order::getRetailerId, Collectors.summingLong(x -> Math.round(x.getTotalAmount()))));// ---- Batch pre-fetch everything else that was previously per-fofo ----// Auth hierarchy (BM / SalesManager / RBM L1+L2 fallback / ABM L1+L2+L3 fallback)Map<Integer, Integer> rbmL1Map = csService.getAuthUserIdsWithoutTicketAssigneeByFofoIds(ProfitMandiConstants.TICKET_CATEGORY_RBM, EscalationType.L1, retailerIdSet);Map<Integer, Integer> rbmL2Map = csService.getAuthUserIdsWithoutTicketAssigneeByFofoIds(ProfitMandiConstants.TICKET_CATEGORY_RBM, EscalationType.L2, retailerIdSet);Map<Integer, Integer> bmMap = csService.getAuthUserIdsWithoutTicketAssigneeByFofoIds(ProfitMandiConstants.TICKET_CATEGORY_SALES, EscalationType.L2, retailerIdSet);Map<Integer, Integer> salesManagerMap = csService.getAuthUserIdsWithoutTicketAssigneeByFofoIds(ProfitMandiConstants.TICKET_CATEGORY_SALES, EscalationType.L1, retailerIdSet);Map<Integer, Integer> abmL1Map = csService.getAuthUserIdsWithoutTicketAssigneeByFofoIds(ProfitMandiConstants.TICKET_CATEGORY_ABM, EscalationType.L1, retailerIdSet);Map<Integer, Integer> abmL2Map = csService.getAuthUserIdsWithoutTicketAssigneeByFofoIds(ProfitMandiConstants.TICKET_CATEGORY_ABM, EscalationType.L2, retailerIdSet);Map<Integer, Integer> abmL3Map = csService.getAuthUserIdsWithoutTicketAssigneeByFofoIds(ProfitMandiConstants.TICKET_CATEGORY_ABM, EscalationType.L3, retailerIdSet);Set<Integer> allAuthUserIds = new HashSet<>();for (Map<Integer, Integer> m : Arrays.asList(rbmL1Map, rbmL2Map, bmMap, salesManagerMap, abmL1Map, abmL2Map, abmL3Map)) {m.values().stream().filter(v -> v != null && v != 0).forEach(allAuthUserIds::add);}Map<Integer, AuthUser> authUserMap = allAuthUserIds.isEmpty() ? new HashMap<>(): authRepository.selectByIds(new ArrayList<>(allAuthUserIds)).stream().collect(Collectors.toMap(AuthUser::getId, u -> u, (a, b) -> a));// AST batchSet<Integer> astIds = customRetailers.values().stream().map(CustomRetailer::getAstId).filter(id -> id != null && id != 0).collect(Collectors.toSet());Map<Integer, AST> astMap = astIds.isEmpty() ? new HashMap<>(): astRepository.selectByIds(new ArrayList<>(astIds)).stream().collect(Collectors.toMap(AST::getId, a -> a, (a, b) -> a));// FofoStore batch (replaces the two duplicate per-fofo calls)Map<Integer, FofoStore> fofoStoreMap = fofoStoreRepository.selectByRetailerIds(retailerIds).stream().collect(Collectors.toMap(FofoStore::getId, s -> s, (a, b) -> a));// Users + wallet-creation datesMap<Integer, User> userMap = userRepository.selectByIds(retailerIds).stream().collect(Collectors.toMap(User::getId, u -> u, (a, b) -> a));Map<Integer, LocalDateTime> walletFirstCreatedMap = userWalletHistoryRepository.selectFirstCreatedDatesForFofoIds(retailerIds);// Partner type for todayMap<Integer, PartnerType> partnerTypeMap = partnerTypeChangeService.getTypesForFofoIds(retailerIds, LocalDate.now());// Monthly targetsMap<Integer, Double> currentTargetMap = monthlyTargetRepository.selectByExactDateAndFofoIds(currentMonth, retailerIds).stream().collect(Collectors.toMap(MonthlyTarget::getFofoId, MonthlyTarget::getPurchaseTarget, (a, b) -> a));Map<Integer, Double> lastTargetMap = monthlyTargetRepository.selectByExactDateAndFofoIds(lastMonth, retailerIds).stream().collect(Collectors.toMap(MonthlyTarget::getFofoId, MonthlyTarget::getPurchaseTarget, (a, b) -> a));Map<Integer, Double> twoMonthAgoTargetMap = monthlyTargetRepository.selectByExactDateAndFofoIds(twoMonthsAgo, retailerIds).stream().collect(Collectors.toMap(MonthlyTarget::getFofoId, MonthlyTarget::getPurchaseTarget, (a, b) -> a));// DRR target: RbmTargetService.calculateFofoIdTodayTarget always uses YearMonth.now() internally, regardless of// the date arg. On the 1st this differs from currentMonth (previous month) — preserve original semantics.Map<Integer, Double> drrTargetMap = YearMonth.now().equals(currentMonth) ? currentTargetMap: monthlyTargetRepository.selectByExactDateAndFofoIds(YearMonth.now(), retailerIds).stream().collect(Collectors.toMap(MonthlyTarget::getFofoId, MonthlyTarget::getPurchaseTarget, (a, b) -> a));// Monthly secondary (order value) — batchedMap<Integer, Double> mtdSecondaryMap = orderRepository.selectOrderValueBetweenBillingDatesGroupByFofoId(retailerIds,startOfToday.withDayOfMonth(1), previousDay).stream().collect(Collectors.toMap(IdAmountModel::getId, IdAmountModel::getAmount, (a, b) -> a));Map<Integer, Double> yesterDaySecondaryMap = orderRepository.selectOrderValueBetweenBillingDatesGroupByFofoId(retailerIds,yesterdayStart, previousDay).stream().collect(Collectors.toMap(IdAmountModel::getId, IdAmountModel::getAmount, (a, b) -> a));Map<Integer, Double> dayBeforeSecondaryMap = orderRepository.selectOrderValueBetweenBillingDatesGroupByFofoId(retailerIds,dayBeforeStart, dayBeforeEnd).stream().collect(Collectors.toMap(IdAmountModel::getId, IdAmountModel::getAmount, (a, b) -> a));Map<Integer, Double> lastMonthSecondaryMap = orderRepository.selectOrderValueBetweenBillingDatesGroupByFofoId(retailerIds,lastMontStartDate, lastMonthEndDate).stream().collect(Collectors.toMap(IdAmountModel::getId, IdAmountModel::getAmount, (a, b) -> a));Map<Integer, Double> twoMonthAgoSecondaryMap = orderRepository.selectOrderValueBetweenBillingDatesGroupByFofoId(retailerIds,twoMonthsAgoStartDate, twoMonthsAgoEndDate).stream().collect(Collectors.toMap(IdAmountModel::getId, IdAmountModel::getAmount, (a, b) -> a));// Tertiary MTD / last-month / two-months-ago (all-partners scan via fofoId=0)LocalDateTime now = LocalDateTime.now();Map<Integer, Double> mtdSaleTillYesterdayMap = fofoOrderItemRepository.selectSumMopGroupByRetailer(startOfToday.withDayOfMonth(1), previousDay, 0, false);Map<Integer, Double> lastMonthSaleMap = fofoOrderItemRepository.selectSumMopGroupByRetailer(lastMontStartDate, lastMonthEndDate, 0, false);Map<Integer, Double> twoMonthAgoSaleMap = fofoOrderItemRepository.selectSumMopGroupByRetailer(twoMonthsAgoStartDate, twoMonthsAgoEndDate, 0, false);// Monthly activated-but-not-billedMap<Integer, Map<YearMonth, PartnerWiseActivatedNotBilledTotal>> activatedNotBilledByFofo = new HashMap<>();for (PartnerWiseActivatedNotBilledTotal t : activatedImeiRepository.getTotalMonthlyActivatedNotBilledForFofoIds(retailerIds, twoMonthsAgoStartDate)) {activatedNotBilledByFofo.computeIfAbsent(t.getFofoId(), k -> new HashMap<>()).put(YearMonth.parse(t.getYearMonth()), t);}// Brand-wise tertiary MTD (per fofo → brand → amount)Map<Integer, Map<String, Double>> brandTertiaryByFofo = fofoOrderItemRepository.selectSumAmountGroupByBrandForFofoIds(currentMonthStartDate, currentMonthEndDate, retailerIds);// Brand-wise secondary billed (MTD)Map<Integer, Map<String, Long>> brandBilledByFofo = new HashMap<>();for (BrandWiseModel m : orderRepository.selectAllBilledByCategoryOrderGroupByBrandFofoIds(retailerIds, currentMonthStartDate, currentMonthEndDate, Arrays.asList(10006, 10001))) {brandBilledByFofo.computeIfAbsent(m.getFofoId(), k -> new HashMap<>()).merge(m.getBrand(), m.getAmount(), Long::sum);}// Brand-wise returns + RTO returns (MTD)Map<Integer, Map<String, Double>> brandReturnByFofo = new HashMap<>();for (BrandWiseReturnInfo r : returnOrderInfoRepository.selectAllBrandWiseByBetweenDateForFofoIds(currentMonthStartDate, currentMonthEndDate.plusDays(1), retailerIds)) {brandReturnByFofo.computeIfAbsent(r.getRetailerId(), k -> new HashMap<>()).merge(r.getBrand(), r.getReturnAmount(), Double::sum);}Map<Integer, Map<String, Double>> brandRtoReturnByFofo = new HashMap<>();for (BrandWiseReturnInfo r : returnOrderInfoRepository.selectAllBrandWiseRTORefundByBetweenDateForFofoIds(currentMonthStartDate, currentMonthEndDate.plusDays(1), retailerIds)) {brandRtoReturnByFofo.computeIfAbsent(r.getRetailerId(), k -> new HashMap<>()).merge(r.getBrand(), r.getReturnAmount(), Double::sum);}// Active loans grouped per fofo, plus batched loanId → sum.// The single-fofo selectAllActiveLoan(fofoId) additionally filters pendingAmount > 0 — must preserve.Map<Integer, List<Loan>> activeLoansByFofo = loanRepository.selectAllActiveLoan().stream().filter(l -> retailerIdSet.contains(l.getFofoId())&& l.getPendingAmount() != null&& l.getPendingAmount().doubleValue() > 0).collect(Collectors.groupingBy(Loan::getFofoId));Set<Integer> loanIdsForSum = new HashSet<>();activeLoansByFofo.values().forEach(list -> list.forEach(l -> loanIdsForSum.add(l.getId())));defaultLoans.forEach(l -> loanIdsForSum.add(l.getId()));Map<Integer, Double> loanStatementSumByLoanId = loanStatementRepository.sumAmountByLoanIds(new ArrayList<>(loanIdsForSum));// Last order per fofo (batched)Map<Integer, Integer> lastOrderIdByFofo = orderRepository.getLastOrderByFofoIds(retailerIds);Set<Integer> lastOrderIds = lastOrderIdByFofo.values().stream().filter(id -> id != null && id != 0).collect(Collectors.toSet());Map<Integer, Order> lastOrderById = new HashMap<>();for (Integer oid : lastOrderIds) {Order o = orderRepository.selectById(oid);if (o != null) lastOrderById.put(oid, o);}// DRR precomputation — the two dates we ever call withlong day1RemainingDays = rbmTargetService.getRemainingDaysInMonth(currentMonth.atDay(1));long todayRemainingDays = rbmTargetService.getRemainingDaysInMonth(startOfToday.toLocalDate());LOGGER.info("[BI_REPORT] batch pre-fetch complete in {}ms; retailers={}, entering per-fofo loop",System.currentTimeMillis() - __biReportStartMs, retailerIds.size());long __biReportLoopStartMs = System.currentTimeMillis();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) {// resolve auth-user names from batched mapsString rbmName = "";int rbmL1 = rbmL1Map.getOrDefault(fofoId, 0);if (rbmL1 != 0 && authUserMap.get(rbmL1) != null) {rbmName = authUserMap.get(rbmL1).getFullName();} else {int rbmL2 = rbmL2Map.getOrDefault(fofoId, 0);if (rbmL2 != 0 && authUserMap.get(rbmL2) != null) {rbmName = authUserMap.get(rbmL2).getFullName();}}String bmName = "";int bmId = bmMap.getOrDefault(fofoId, 0);if (bmId != 0 && authUserMap.get(bmId) != null) {bmName = authUserMap.get(bmId).getFullName();}int managerId = salesManagerMap.getOrDefault(fofoId, 0);String managerName;if (managerId != 0 && authUserMap.get(managerId) != null) {managerName = authUserMap.get(managerId).getFullName();} else {managerName = bmName;}String abmName = "";int abmId = abmL1Map.getOrDefault(fofoId, 0);int abmL2Id = abmL2Map.getOrDefault(fofoId, 0);int abmL3Id = abmL3Map.getOrDefault(fofoId, 0);if (abmId != 0 && authUserMap.get(abmId) != null) {abmName = authUserMap.get(abmId).getFullName();} else if (abmL2Id != 0 && authUserMap.get(abmL2Id) != null) {abmName = authUserMap.get(abmL2Id).getFullName();} else if (abmL3Id != 0 && authUserMap.get(abmL3Id) != null) {abmName = authUserMap.get(abmL3Id).getFullName();}AST ast = astMap.get(customRetailers.get(fofoId).getAstId());PartnerType partnerTypeThisMonth = partnerTypeMap.get(fofoId);BIRetailerModel biRetailerModel = new BIRetailerModel();biRetailerModel.setBmName(bmName);biRetailerModel.setCode(customRetailers.get(fofoId).getCode());biRetailerModel.setArea(ast != null ? ast.getArea() : "-");FofoStore fofoStore = fofoStoreMap.get(fofoId);String retailerStatus;if (fofoStore == null) {retailerStatus = "-";} else if (!fofoStore.isActive()) {retailerStatus = "INACTIVE";} else {retailerStatus = String.valueOf(fofoStore.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);biRetailerModel.setAbm(abmName);biRetailerModelMap.put(fofoId, biRetailerModel);Map<YearMonth, PartnerWiseActivatedNotBilledTotal> partnerWiseActivatedNotBilledTotalMap =activatedNotBilledByFofo.getOrDefault(fofoId, new HashMap<>());// Current-month secondarydouble currentSecondaryTarget = currentTargetMap.getOrDefault(fofoId, 0d);long currentMonthReturn = currentMonthPartnerReturnOrderInfoModelMap.getOrDefault(fofoId, 0L)+ currentMonthRtoRefundOrderMap.getOrDefault(fofoId, 0L);double dayBeforeYesterdayAfterReturnSecondary = dayBeforeSecondaryMap.getOrDefault(fofoId, 0d)- (dayBeforeYesterdayReturnOrderInfoModelMap.getOrDefault(fofoId, 0L) + dayBeforeYesterdayRtoRefundOrderMap.getOrDefault(fofoId, 0L));fofoDayBeforeYesterdaySecondaryMap.put(fofoId, (long) dayBeforeYesterdayAfterReturnSecondary);double yesterDayAfterReturnSecondary = yesterDaySecondaryMap.getOrDefault(fofoId, 0d)- (yesterdayReturnOrderInfoModelMap.getOrDefault(fofoId, 0L) + yesterdayRtoRefundOrderMap.getOrDefault(fofoId, 0L));fofoYesterdaySecondaryMap.put(fofoId, (long) yesterDayAfterReturnSecondary);double secondaryAchievedMtd = mtdSecondaryMap.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;double mtdSale = mtdSaleTillYesterdayMap.getOrDefault(fofoId, 0d);// Last month secondarydouble lastMonthSecondaryTarget = lastTargetMap.getOrDefault(fofoId, 0d);long lastMonthReturn = lastMonthPartnerReturnOrderInfoModelMap.getOrDefault(fofoId, 0L)+ lastMonthRtoRefundOrderMap.getOrDefault(fofoId, 0L);double lastMonthSecondaryAchieved = lastMonthSecondaryMap.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;double lastMonthSale = lastMonthSaleMap.getOrDefault(fofoId, 0d);// Two months ago secondarydouble twoMonthAgoSecondaryTarget = twoMonthAgoTargetMap.getOrDefault(fofoId, 0d);long twoMonthAgoReturn = twoMonthAgoPartnerReturnOrderInfoModelMap.getOrDefault(fofoId, 0L)+ twoMonthAgoRtoRefundOrderMap.getOrDefault(fofoId, 0L);double twoMonthAgoSecondaryAchieved = twoMonthAgoSecondaryMap.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;double twoMonthAgoSale = twoMonthAgoSaleMap.getOrDefault(fofoId, 0d);Map<YearMonth, BiSecondaryModel> monthlySecondaryModels = new HashMap<>();monthlySecondaryModels.put(currentMonth, new BiSecondaryModel(currentSecondaryTarget, secondaryAchievedMtd, currentMonthReturn,currentMonthNetSecondary, currentMonthSecondaryPercent, mtdSale, currentMonthUnbilled));monthlySecondaryModels.put(lastMonth, new BiSecondaryModel(lastMonthSecondaryTarget, lastMonthSecondaryAchieved, lastMonthReturn,lastMonthNetSecondary, lastMonthSecondaryPercent, lastMonthSale, lastMonthUnbilled));monthlySecondaryModels.put(twoMonthsAgo, new BiSecondaryModel(twoMonthAgoSecondaryTarget, twoMonthAgoSecondaryAchieved, twoMonthAgoReturn,twoMonthAgoNetSecondary, twoMonthAgoSecondaryPercent, twoMonthAgoSale, twoMonthAgoUnbilled));allRetailerMonthlyData.put(fofoId, monthlySecondaryModels);// Brand-wise stock value — still per-fofo (per-fofo pricing service, not batchable trivially)Map<String, BrandStockPrice> brandStockPriceMap = inventoryService.getBrandWiseStockValue(fofoId);fofoBrandStockPriceMap.put(fofoId, brandStockPriceMap);fofoTotalStockPriceMap.put(fofoId, brandStockPriceMap.values().stream().mapToDouble(BrandStockPrice::getTotalValue).sum());Map<String, Double> brandMtdTertiaryAmount = brandTertiaryByFofo.getOrDefault(fofoId, new HashMap<>());fofoBrandMtdTertiaryMap.put(fofoId, brandMtdTertiaryAmount);fofoTotalMtdTertiaryMap.put(fofoId, brandMtdTertiaryAmount.values().stream().mapToDouble(Double::doubleValue).sum());Map<String, Long> brandWiseMtdSecondaryMap = brandBilledByFofo.getOrDefault(fofoId, new HashMap<>());Map<String, Double> brandWiseReturnInfoMap = brandReturnByFofo.getOrDefault(fofoId, new HashMap<>());Map<String, Double> brandWiseRTOReturnInfoMap = brandRtoReturnByFofo.getOrDefault(fofoId, new HashMap<>());Set<String> allBrands = new HashSet<>();allBrands.addAll(brandWiseMtdSecondaryMap.keySet());allBrands.addAll(brandWiseReturnInfoMap.keySet());allBrands.addAll(brandWiseRTOReturnInfoMap.keySet());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);brandWiseMtdNetSecondaryMap.put(brand, Math.round(billedAmount - (returnAmount + rtoReturnAmount)));}fofoBrandWiseMtdSecondaryMap.put(fofoId, brandWiseMtdNetSecondaryMap);fofoTotalMtdSecondaryMap.put(fofoId, brandWiseMtdNetSecondaryMap.values().stream().mapToLong(Long::longValue).sum());// Investment infoPartnerDailyInvestment pdi = partnerDailyInvestmentMap.get(fofoId);float shortInvestment = pdi != null ? pdi.getShortInvestment() : 0f;float agreedInvestment = pdi != null ? pdi.getMinInvestment() : 0f;float investmentLevel = pdi != null ? Math.abs(((shortInvestment - agreedInvestment) / agreedInvestment) * 100) : 0f;List<Loan> fofoDefaultLoans = defaultLoanMap.get(fofoId);float defaultLoanAmount = 0f;if (fofoDefaultLoans != null) {for (Loan entry : fofoDefaultLoans) {double amount = loanStatementSumByLoanId.getOrDefault(entry.getId(), 0d);defaultLoanAmount += amount;}}List<Loan> activeLoans = activeLoansByFofo.getOrDefault(fofoId, Collections.emptyList());float activeLoan = 0f;for (Loan entry : activeLoans) {double pendingAmount = loanStatementSumByLoanId.getOrDefault(entry.getId(), 0d);activeLoan += pendingAmount;}float poValue = pdi != null ? pdi.getUnbilledAmount() : 0f;float poAndBilledValue = (float) (currentMonthNetSecondary + poValue);// DRR — inlined equivalent of RbmTargetService.calculateFofoIdTodayTarget.// Note: original always looks up YearMonth.now() target (not currentMonth). On the 1st these differ.double drrTarget = drrTargetMap.getOrDefault(fofoId, 0d);double monthDay1Drr = 0d;if (drrTarget != 0d) {double remainingTarget = drrTarget;monthDay1Drr = day1RemainingDays == 0 ? remainingTarget : (int) Math.ceil(remainingTarget / day1RemainingDays);}double todayRequiredDrr = 0d;if (monthDay1Drr != 0d) {double remainingTarget = drrTarget - currentMonthNetSecondary;todayRequiredDrr = todayRemainingDays == 0 ? remainingTarget : (int) Math.ceil(remainingTarget / todayRemainingDays);}double gotDrrPercent = monthDay1Drr == 0 ? 0 : (todayRequiredDrr / monthDay1Drr) * 100;long drrPercentDisplay = Math.round(Math.abs(gotDrrPercent));int orderId = lastOrderIdByFofo.getOrDefault(fofoId, 0);String alertLevel = "-";int lastPurchaseDays = 0;if (orderId != 0) {Order order = lastOrderById.get(orderId);if (order != null) {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 alertLevel = "OK";}}FofoInvestmentModel fofoInvestmentModel = new FofoInvestmentModel();fofoInvestmentModel.setCounterPotential(fofoStore != null ? fofoStore.getCounterPotential() : 0);fofoInvestmentModel.setShortInvestment(shortInvestment);fofoInvestmentModel.setDefaultLoan(defaultLoanAmount);fofoInvestmentModel.setInvestmentLevel(investmentLevel);fofoInvestmentModel.setActiveLoan(activeLoan);fofoInvestmentModel.setPoValue(poValue);fofoInvestmentModel.setPoAndBilled(poAndBilledValue);fofoInvestmentModel.setAgreedInvestment(agreedInvestment);fofoInvestmentModel.setWallet(pdi != null ? pdi.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);zeroBillingMap.put(fofoId, currentMonthNetSecondary <= 100000 ? "Zero Billing" : "-");billingNeededMap.put(fofoId, drrPercentDisplay >= 110 && todayRequiredDrr > 0 ? (float) todayRequiredDrr : 0f);countAMap.put(fofoId, (defaultLoanAmount > 0 || investmentLevel <= 75 || currentMonthNetSecondary <= 100000 || drrPercentDisplay >= 110) ? 1 : 0);}LOGGER.info("Total BI Retailers processed: {}", biRetailerModelMap.size());LOGGER.info("[BI_REPORT] per-fofo loop finished in {}ms",System.currentTimeMillis() - __biReportLoopStartMs);//generate excel and sent to mailList<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","ABM","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","Motorola", "Samsung", "Realme", "Oppo", "OnePlus", "POCO", "Lava", "Itel", "Almost New","Total Secondary", "Apple", "Xiaomi", "Vivo", "Tecno","Motorola", "Samsung", "Realme", "Oppo", "OnePlus", "POCO", "Lava", "Itel", "Almost New","Total Tertiary", "Apple", "Xiaomi", "Vivo", "Tecno","Motorola", "Samsung", "Realme", "Oppo", "OnePlus", "POCO", "Lava", "Itel", "Almost New","YesterDay Seconday","Day Before Yesterday Secondary");headerGroup.add(headers1);headerGroup.add(headers2);List<List<?>> rows = new ArrayList<>();DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd/MM/yyyy");for (Map.Entry<Integer, BIRetailerModel> entry : biRetailerModelMap.entrySet()) {Integer fofoId = entry.getKey();User user = userMap.get(fofoId);LocalDateTime walletCreationDate = walletFirstCreatedMap.get(fofoId);if (walletCreationDate == null && user != null) {walletCreationDate = user.getCreateTimestamp();}BIRetailerModel retailer = entry.getValue();Map<YearMonth, BiSecondaryModel> monthlyData = allRetailerMonthlyData.get(fofoId);BiSecondaryModel current = monthlyData.getOrDefault(currentMonth, new BiSecondaryModel(0,0,0,0,0,0,0));BiSecondaryModel last = monthlyData.getOrDefault(currentMonth.minusMonths(1), new BiSecondaryModel(0,0,0,0,0,0,0));BiSecondaryModel twoAgo = monthlyData.getOrDefault(currentMonth.minusMonths(2), new BiSecondaryModel(0,0,0,0,0,0,0));List<Object> row = new ArrayList<>();LOGGER.info("fofoId-11 {}",fofoId);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 != null ? walletCreationDate.format(formatter) : "-",(user != null && user.getCreateTimestamp() != null) ? user.getCreateTimestamp().format(formatter) : "-",retailer.getCode(),retailer.getArea(),retailer.getCity(),retailer.getStoreName(),retailer.getStatus(),retailer.getCategory(),retailer.getSalesManager(),retailer.getRbm(),retailer.getAbm()));// Two Months Agorow.addAll(Arrays.asList(twoAgo.getSecondaryTarget(),twoAgo.getSecondaryAchieved(),twoAgo.getSecondaryReturn(),twoAgo.getNetSecondary(),twoAgo.getSecondaryAchievedPercent()+"%",twoAgo.getTertiary(),twoAgo.getTertiaryUnBilled()));// Last Monthrow.addAll(Arrays.asList(last.getSecondaryTarget(),last.getSecondaryAchieved(),last.getSecondaryReturn(),last.getNetSecondary(),last.getSecondaryAchievedPercent()+"%",last.getTertiary(),last.getTertiaryUnBilled()));// Current Monthrow.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);row.addAll(Arrays.asList(fofoTotalStockPriceMap.getOrDefault(fofoId, 0.0),brandStockMap.get("Apple") != null ? brandStockMap.get("Apple").getTotalValue() : 0.0,brandStockMap.get("Xiaomi") != null ? brandStockMap.get("Xiaomi").getTotalValue() : 0.0,brandStockMap.get("Vivo") != null ? brandStockMap.get("Vivo").getTotalValue() : 0.0,brandStockMap.get("Tecno") != null ? brandStockMap.get("Tecno").getTotalValue() : 0.0,brandStockMap.get("Motorola") != null ? brandStockMap.get("Motorola").getTotalValue() : 0.0,brandStockMap.get("Samsung") != null ? brandStockMap.get("Samsung").getTotalValue() : 0.0,brandStockMap.get("Realme") != null ? brandStockMap.get("Realme").getTotalValue() : 0.0,brandStockMap.get("Oppo") != null ? brandStockMap.get("Oppo").getTotalValue() : 0.0,brandStockMap.get("OnePlus") != null ? brandStockMap.get("OnePlus").getTotalValue() : 0.0,brandStockMap.get("POCO") != null ? brandStockMap.get("POCO").getTotalValue() : 0.0,brandStockMap.get("Lava") != null ? brandStockMap.get("Lava").getTotalValue() : 0.0,brandStockMap.get("Itel") != null ? brandStockMap.get("Itel").getTotalValue() : 0.0,brandStockMap.get("Almost New") != null ? brandStockMap.get("Almost New").getTotalValue() : 0.0));Map<String, Long> brandSecondaryMap = fofoBrandWiseMtdSecondaryMap.get(fofoId);row.addAll(Arrays.asList(fofoTotalMtdSecondaryMap.get(fofoId),brandSecondaryMap.getOrDefault("Apple", 0L),brandSecondaryMap.getOrDefault("Xiaomi", 0L),brandSecondaryMap.getOrDefault("Vivo", 0L),brandSecondaryMap.getOrDefault("Tecno", 0L),brandSecondaryMap.getOrDefault("Motorola", 0L),brandSecondaryMap.getOrDefault("Samsung", 0L),brandSecondaryMap.getOrDefault("Realme", 0L),brandSecondaryMap.getOrDefault("Oppo", 0L),brandSecondaryMap.getOrDefault("OnePlus", 0L),brandSecondaryMap.getOrDefault("POCO", 0L),brandSecondaryMap.getOrDefault("Lava", 0L),brandSecondaryMap.getOrDefault("Itel", 0L),brandSecondaryMap.getOrDefault("Almost New", 0L)));Map<String, Double> brandTertiaryMap = fofoBrandMtdTertiaryMap.get(fofoId);row.addAll(Arrays.asList(fofoTotalMtdTertiaryMap.get(fofoId),brandTertiaryMap.getOrDefault("Apple", 0d),brandTertiaryMap.getOrDefault("Xiaomi", 0d),brandTertiaryMap.getOrDefault("Vivo", 0d),brandTertiaryMap.getOrDefault("Tecno", 0d),brandTertiaryMap.getOrDefault("Motorola", 0d),brandTertiaryMap.getOrDefault("Samsung", 0d),brandTertiaryMap.getOrDefault("Realme", 0d),brandTertiaryMap.getOrDefault("Oppo", 0d),brandTertiaryMap.getOrDefault("OnePlus", 0d),brandTertiaryMap.getOrDefault("POCO", 0d),brandTertiaryMap.getOrDefault("Lava", 0d),brandTertiaryMap.getOrDefault("Itel", 0d),brandTertiaryMap.getOrDefault("Almost New", 0d)));row.addAll(Arrays.asList(fofoYesterdaySecondaryMap.get(fofoId),fofoDayBeforeYesterdaySecondaryMap.get(fofoId)));rows.add(row);}Map<String, Set<Integer>> storeGuyMap = this.generateBiReportHierarchyWise();for (Map.Entry<String, Set<Integer>> storeGuyEntry : storeGuyMap.entrySet()) {String storeGuyEmail = storeGuyEntry.getKey();Set<Integer> fofoIds = storeGuyEntry.getValue();String[] sendToArray = new String[]{storeGuyEmail};List<List<?>> filteredRows = rows.stream().filter(row -> row.size() > 5 && fofoIds.contains((Integer) row.get(5))).collect(Collectors.toList());this.sendMailToUser(headerGroup,filteredRows,sendToArray);}this.sendMailToUser(headerGroup,rows,new String[]{"ranu.rajput@smartdukaan.com","niranjan.kala@smartdukaan.com","nivesh.mathur@smartdukaan.com","deena.nath@smartdukaan.com","santosh.giri@smartdukaan.com"});LOGGER.info("[BI_REPORT] DONE batch-optimized generateBiReportExcel; totalMs={}, retailers={}, rows={}",System.currentTimeMillis() - __biReportStartMs, retailerIds.size(), rows.size());}private void sendMailToUser(List<List<String>> headerGroup,List<List<?>> rows, String[] sendToArray ) throws Exception {// Send to email// ByteArrayOutputStream csvStream = FileUtil.getCSVByteStreamWithMultiHeaders(headerGroup, rows);ByteArrayOutputStream csvStream = getExcelStreamWithMultiHeaders(headerGroup, rows);String fileName = "BI-Retailer-Monthly-Report-" + FormattingUtils.formatDate(LocalDateTime.now()) + ".xlsx";Utils.sendMailWithAttachment(googleMailSender, sendToArray, new String[]{}, "BI Retailer Monthly Report", "Please find attached the BI retailer secondary/tertiary monthly report.", fileName, new ByteArrayResource(csvStream.toByteArray()));}public static ByteArrayOutputStream getExcelStreamWithMultiHeaders(List<List<String>> headerGroup, List<List<?>> rows) {Workbook workbook = new XSSFWorkbook();Sheet sheet = workbook.createSheet("BI Report");CreationHelper creationHelper = workbook.getCreationHelper();int rowIndex = 0;CellStyle centeredStyle = workbook.createCellStyle();centeredStyle.setAlignment(HorizontalAlignment.CENTER); // Center horizontallycenteredStyle.setVerticalAlignment(VerticalAlignment.CENTER); // Center vertically// Optional: bold fontFont font1 = workbook.createFont();font1.setBold(true);centeredStyle.setFont(font1);// Create stylesMap<String, CellStyle> headerStyles = new HashMap<>();// fontPurpleStyleCellStyle purpleStyle = workbook.createCellStyle();purpleStyle.setFillForegroundColor(IndexedColors.ROSE.getIndex());purpleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);purpleStyle.setFont(font1);headerStyles.put("Assessment", purpleStyle);headerStyles.put("Zero billing", purpleStyle);headerStyles.put("Billing needed", purpleStyle);headerStyles.put("Counta", purpleStyle);headerStyles.put("MONTH BEGINNING DRR", purpleStyle);headerStyles.put("REQ DRR", purpleStyle);headerStyles.put("Drr %", purpleStyle);// Light BlueCellStyle blueStyle = workbook.createCellStyle();blueStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());blueStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);blueStyle.setFont(font1);headerStyles.put("Code", blueStyle);headerStyles.put("Store Name", blueStyle);headerStyles.put("City", blueStyle);headerStyles.put("Area", blueStyle);headerStyles.put("BM", blueStyle);headerStyles.put("RBM", blueStyle);headerStyles.put("Sales Manager", blueStyle);headerStyles.put("Status", blueStyle);headerStyles.put("Category", blueStyle);headerStyles.put("Wallet Date", blueStyle);headerStyles.put("Creation Date", blueStyle);headerStyles.put("Partner Id", blueStyle);//for link// Create hyperlink styleCellStyle hyperlinkStyle = workbook.createCellStyle();Font hlinkFont = workbook.createFont();hlinkFont.setUnderline(Font.U_SINGLE);hlinkFont.setColor(IndexedColors.BLUE.getIndex());hyperlinkStyle.setFont(hlinkFont);// Light YellowCellStyle yellowStyle = workbook.createCellStyle();yellowStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());yellowStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);yellowStyle.setFont(font1);headerStyles.put("Last billing Done", yellowStyle);headerStyles.put("Total Stock", yellowStyle);// Light OrangeCellStyle orangeStyle = workbook.createCellStyle();orangeStyle.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());orangeStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);orangeStyle.setFont(font1);headerStyles.put("Total Tertiary", orangeStyle);headerStyles.put("Total Secondary", orangeStyle);headerStyles.put("Default", orangeStyle);// Light greenCellStyle lightGreenStyle = workbook.createCellStyle();lightGreenStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());lightGreenStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);lightGreenStyle.setFont(font1);headerStyles.put("Short investment", lightGreenStyle);headerStyles.put("INVESTMENT LEVEL", lightGreenStyle);headerStyles.put("Loan", lightGreenStyle);headerStyles.put("PO value", lightGreenStyle);headerStyles.put("Agreed investment", lightGreenStyle);headerStyles.put("Wallet", lightGreenStyle);headerStyles.put("po+bill", lightGreenStyle);// Light GreenCellStyle secondary1 = createStyle(workbook, IndexedColors.LIGHT_GREEN);CellStyle secondary2 = createStyle(workbook, IndexedColors.LIGHT_YELLOW);CellStyle secondary3 = createStyle(workbook, IndexedColors.LIGHT_ORANGE);Map<String, CellStyle> brandStyles = new HashMap<>();brandStyles.put("Apple", createStyle(workbook, IndexedColors.GREY_25_PERCENT));brandStyles.put("Xiaomi", createStyle(workbook, IndexedColors.ORANGE));brandStyles.put("Vivo", createStyle(workbook, IndexedColors.SKY_BLUE));brandStyles.put("Tecno", createStyle(workbook, IndexedColors.LIGHT_BLUE));brandStyles.put("Motorola", createStyle(workbook, IndexedColors.LIGHT_GREEN));brandStyles.put("Samsung", createStyle(workbook, IndexedColors.ROYAL_BLUE));brandStyles.put("Realme", createStyle(workbook, IndexedColors.YELLOW));brandStyles.put("Oppo", createStyle(workbook, IndexedColors.LIGHT_GREEN));brandStyles.put("OnePlus", createStyle(workbook, IndexedColors.RED));brandStyles.put("POCO", createStyle(workbook, IndexedColors.ORANGE));brandStyles.put("Lava", createStyle(workbook, IndexedColors.LIGHT_YELLOW));brandStyles.put("Itel", createStyle(workbook, IndexedColors.LIGHT_YELLOW));brandStyles.put("Almost New", createStyle(workbook, IndexedColors.WHITE));CellStyle defaultHeaderStyle = workbook.createCellStyle();defaultHeaderStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());defaultHeaderStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);defaultHeaderStyle.setFont(font1);CellStyle numberStyle = workbook.createCellStyle();DataFormat format = workbook.createDataFormat();numberStyle.setDataFormat(format.getFormat("#,##0")); // or "#,##0.00" for two decimalsMap<String, Integer> headerCount = new HashMap<>();for (int headerRowIndex = 0; headerRowIndex < headerGroup.size(); headerRowIndex++) {List<String> headerRow = headerGroup.get(headerRowIndex);Row row = sheet.createRow(rowIndex++);for (int i = 0; i < headerRow.size(); i++) {String headerText = headerRow.get(i);sheet.setColumnWidth(i, 25 * 256);row.setHeightInPoints(20); // 25-point heightCell cell = row.createCell(i);cell.setCellValue(headerText);cell.setCellStyle(centeredStyle);// Count how many times this header has appearedint count = headerCount.getOrDefault(headerText, 0) + 1;headerCount.put(headerText, count);// Apply special style for repeated headersif (headerText.equals("Secondary Target") || headerText.equals("Secondary Achieved") || headerText.equals("Returns") || headerText.equals("Net Secondary") || headerText.equals("Secondary %") || headerText.equals("Tertiary Sale") || headerText.equals("Unbilled")) {if (count == 1) {cell.setCellStyle(secondary1);} else if (count == 2) {cell.setCellStyle(secondary2);} else if (count == 3) {cell.setCellStyle(secondary3);}}// Brand header styling (apply only for the 2nd row of headers)else if (headerRowIndex == 1 && brandStyles.containsKey(headerText)) {cell.setCellStyle(brandStyles.get(headerText));}else if (headerStyles.containsKey(headerText)) {cell.setCellStyle(headerStyles.get(headerText));} else {cell.setCellStyle(defaultHeaderStyle); // default style for others}}}// Write data rowsfor (List<?> dataRow : rows) {Row row = sheet.createRow(rowIndex++);for (int i = 0; i < dataRow.size(); i++) {Cell cell = row.createCell(i);Object value = dataRow.get(i);if (i == 6 && value != null) { // Assuming column 6 is "Link"Hyperlink hyperlink = creationHelper.createHyperlink(HyperlinkType.URL);hyperlink.setAddress(value.toString());cell.setCellValue("View Link"); // Display textcell.setHyperlink(hyperlink);cell.setCellStyle(hyperlinkStyle);} else if (value instanceof Number) {double numeric = ((Number) value).doubleValue();cell.setCellValue(Math.round(numeric));cell.setCellStyle(numberStyle);} else {cell.setCellValue(value != null ? value.toString() : "");}}}// Auto-size columnsif (!rows.isEmpty()) {for (int i = 0; i < rows.get(0).size(); i++) {sheet.autoSizeColumn(i);}}// Output as ByteArraytry (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {workbook.write(outputStream);workbook.close();return outputStream;} catch (IOException e) {throw new RuntimeException("Failed to generate Excel file", e);}}private static CellStyle createStyle(Workbook workbook, IndexedColors color) {CellStyle style = workbook.createCellStyle();style.setFillForegroundColor(color.getIndex());style.setFillPattern(FillPatternType.SOLID_FOREGROUND);Font font = workbook.createFont();font.setBold(true);style.setFont(font);return style;}public void stockAlertMailToRetailer() throws Exception {Map<Integer, CustomRetailer> customRetailers = retailerService.getFofoRetailers(true);List<Integer> retailerIds = customRetailers.values().stream().map(CustomRetailer::getPartnerId).collect(Collectors.toList());for(Integer fofoId : retailerIds){List<String> statusOrder = Arrays.asList("HID", "FASTMOVING", "RUNNING", "SLOWMOVING", "OTHER");FofoStore fofoStore = fofoStoreRepository.selectByRetailerId(fofoId);List<PartnerWarehouseStockSummaryModel> partnerWarehouseStockSummaryModels = saholicInventoryService.getSaholicAndPartnerStock(fofoId, fofoStore.getWarehouseId());List<PartnerWarehouseStockAgingSummaryModel> partnerWarehouseStockAgingSummaryModelList = new ArrayList<>();Set<Integer> catalogIds = partnerWarehouseStockSummaryModels.stream().map(x -> x.getCatalogId()).collect(Collectors.toSet());List<Integer> catalogsList = new ArrayList<>(catalogIds);Map<Integer, TagListing> tagListingsMap = tagListingRepository.selectAllByCatalogIds(catalogsList);List<CatalogAgingModel> catalogAgingModels = ageingService.getCatalogsAgingByWarehouse(catalogIds, fofoStore.getWarehouseId());Map<Integer, CatalogAgingModel> catalogAgingModelMap = catalogAgingModels.stream().collect(Collectors.toMap(x -> x.getCatalogId(), x -> x));for (PartnerWarehouseStockSummaryModel stockSummary : partnerWarehouseStockSummaryModels) {PartnerWarehouseStockAgingSummaryModel partnerWarehouseStockAgingSummaryModel = new PartnerWarehouseStockAgingSummaryModel();partnerWarehouseStockAgingSummaryModel.setCatalogId(stockSummary.getCatalogId());partnerWarehouseStockAgingSummaryModel.setBrand(stockSummary.getBrand());partnerWarehouseStockAgingSummaryModel.setModelNumber(stockSummary.getModelNumber());partnerWarehouseStockAgingSummaryModel.setNetAvailability(stockSummary.getShaholicNetAvailability());partnerWarehouseStockAgingSummaryModel.setPartnerStockAvailability(stockSummary.getPartnerFullFilledQty());partnerWarehouseStockAgingSummaryModel.setPartnerCurrentAvailability(stockSummary.getPartnerCurrentQty());partnerWarehouseStockAgingSummaryModel.setPartnerShortageStock(stockSummary.getPartnerShortageQty());if (catalogAgingModelMap.get(stockSummary.getCatalogId()) != null) {partnerWarehouseStockAgingSummaryModel.setExceedDays(catalogAgingModelMap.get(stockSummary.getCatalogId()).getExceedDays());} else {partnerWarehouseStockAgingSummaryModel.setExceedDays(0);}partnerWarehouseStockAgingSummaryModel.setStatus(stockSummary.getStatus());partnerWarehouseStockAgingSummaryModelList.add(partnerWarehouseStockAgingSummaryModel);}Set<Integer> existingCatalogIdsInAgingSummaryList = partnerWarehouseStockAgingSummaryModelList.stream().map(PartnerWarehouseStockAgingSummaryModel::getCatalogId).collect(Collectors.toSet());}}public void createFofoSmartCartSuggestion(){List<Integer> fofoIds = fofoStoreRepository.selectActiveStores().stream().map(x->x.getId()).collect(toList());LocalDateTime todayDate = LocalDate.now().atStartOfDay();LocalDateTime fortyFiveAgoDate = todayDate.minusDays(45).with(LocalTime.MAX);for(Integer fofoId :fofoIds){smartCartSuggestionRepository.deleteByFofoId(fofoId);List<SoldAllCatalogitemQtyByPartnerModel> soldAllCatalogitemQtyByPartnerModels = smartCartService.getAllSoldCatalogItemByPartner(fofoId,fortyFiveAgoDate,todayDate);for(SoldAllCatalogitemQtyByPartnerModel soldAllCatalogitemQtyByPartnerModel : soldAllCatalogitemQtyByPartnerModels){SmartCartSuggestion smartCartSuggestion = new SmartCartSuggestion();// weekly average = total sold qty / 6 weekslong avgWeeklyQty = Math.round((float) soldAllCatalogitemQtyByPartnerModel.getSoldQty() / 6);// ensure minimum 2long suggestedQty = Math.max(1, avgWeeklyQty);smartCartSuggestion.setCatalogId(soldAllCatalogitemQtyByPartnerModel.getCatalogId());smartCartSuggestion.setFofoId(fofoId);smartCartSuggestion.setSoldQty(soldAllCatalogitemQtyByPartnerModel.getSoldQty());smartCartSuggestion.setSuggestedQty(suggestedQty);smartCartSuggestion.setCreationDate(LocalDate.now());smartCartSuggestionRepository.persist(smartCartSuggestion);}}}}