WebSVN – SmartDukaan – Diff – /trunk/profitmandi-cron/src/main/java/com/smartdukaan/cron/scheduled/ScheduledTasksTest.java

 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;
         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("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);
         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()
                 .filter(retailer -> {
-                    String storeCode = retailer.getCode(); // adjust method name if different
+                    String storeCode = retailer.getCode();
                     return !storeCode.equalsIgnoreCase("UPGBN640") && !storeCode.equalsIgnoreCase("HRYN039");
                 })
                 .map(CustomRetailer::getPartnerId)
                 .collect(Collectors.toList());
-//        List<Integer> retailerIds = Arrays.asList(175139993,175139441,175139442,175140143,175140146);
-//        Map<Integer,CustomRetailer> customRetailers = retailerService.getFofoRetailers(retailerIds);
+        Set<Integer> retailerIdSet = new HashSet<>(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));
+                    .collect(Collectors.toMap(PartnerDailyInvestment::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);
+        LocalDateTime dayBeforeStart = previousDay.toLocalDate().atStartOfDay().minusDays(1);
+        LocalDateTime dayBeforeEnd = previousDay.minusDays(1);
+        LocalDateTime yesterdayStart = previousDay.toLocalDate().atStartOfDay();
+        // Returns / RTO — already batched
+        Map<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()))));
-        List<ReturnOrderInfoModel> twoMonthAgoReturnOrderInfoModels = returnOrderInfoRepository.selectAllByBetweenDate(twoMonthsAgoStartDate, twoMonthsAgoEndDate);
+        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)
-        Map<Integer, Long> twoMonthAgoPartnerReturnOrderInfoModelMap = twoMonthAgoReturnOrderInfoModels.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getRefundAmount()))));
+                .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 batch
+        Set<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 dates
+        Map<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 today
+        Map<Integer, PartnerType> partnerTypeMap = partnerTypeChangeService.getTypesForFofoIds(retailerIds, LocalDate.now());
+        // Monthly targets
+        Map<Integer, Double> currentTargetMap = monthlyTargetRepository.selectByDateAndFofoIds(currentMonth, retailerIds).stream()
+                .collect(Collectors.toMap(MonthlyTarget::getFofoId, MonthlyTarget::getPurchaseTarget, (a, b) -> a));
+        Map<Integer, Double> lastTargetMap = monthlyTargetRepository.selectByDateAndFofoIds(lastMonth, retailerIds).stream()
+                .collect(Collectors.toMap(MonthlyTarget::getFofoId, MonthlyTarget::getPurchaseTarget, (a, b) -> a));
+        Map<Integer, Double> twoMonthAgoTargetMap = monthlyTargetRepository.selectByDateAndFofoIds(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.selectByDateAndFofoIds(YearMonth.now(), retailerIds).stream()
+                    .collect(Collectors.toMap(MonthlyTarget::getFofoId, MonthlyTarget::getPurchaseTarget, (a, b) -> a));
+        // Monthly secondary (order value) — batched
+        Map<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);
-        List<Order> twoMonthRtoRefundOrders = orderRepository.selectAllRefundOrderDatesBetween(twoMonthsAgoStartDate, twoMonthsAgoEndDate);
+        Map<Integer, Double> twoMonthAgoSaleMap = fofoOrderItemRepository.selectSumMopGroupByRetailer(twoMonthsAgoStartDate, twoMonthsAgoEndDate, 0, false);
+        // Monthly activated-but-not-billed
+        Map<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, Long> twoMonthAgoRtoRefundOrderMap = twoMonthRtoRefundOrders.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getTotalAmount()))));
+        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 with
+        long day1RemainingDays = rbmTargetService.getRemainingDaysInMonth(currentMonth.atDay(1));
+        long todayRemainingDays = rbmTargetService.getRemainingDaysInMonth(startOfToday.toLocalDate());
         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){
+        for (Integer fofoId : retailerIds) {
+            // resolve auth-user names from batched maps
             String rbmName = "";
-            int rbmL1 = csService.getAuthUserIdWithoutTicketAssignee(ProfitMandiConstants.TICKET_CATEGORY_RBM,EscalationType.L1,fofoId);
+            int rbmL1 = rbmL1Map.getOrDefault(fofoId, 0);
-            if(rbmL1 != 0){
+            if (rbmL1 != 0 && authUserMap.get(rbmL1) != null) {
-                 rbmName = authRepository.selectById(rbmL1).getFullName();
+                rbmName = authUserMap.get(rbmL1).getFullName();
-            }else {
+            } else {
-                int rbmL2 = csService.getAuthUserIdWithoutTicketAssignee(ProfitMandiConstants.TICKET_CATEGORY_RBM, EscalationType.L2, fofoId);
+                int rbmL2 = rbmL2Map.getOrDefault(fofoId, 0);
-                if(rbmL2 != 0){
+                if (rbmL2 != 0 && authUserMap.get(rbmL2) != null) {
-                    rbmName = authRepository.selectById(rbmL2).getFullName();
+                    rbmName = authUserMap.get(rbmL2).getFullName();
+                }
+            }
-            String bmName ="";
+            String bmName = "";
-            int bmId = csService.getAuthUserIdWithoutTicketAssignee(ProfitMandiConstants.TICKET_CATEGORY_SALES,EscalationType.L2,fofoId);
+            int bmId = bmMap.getOrDefault(fofoId, 0);
-            if(bmId !=0){
+            if (bmId != 0 && authUserMap.get(bmId) != null) {
-                bmName = authRepository.selectById(bmId).getFullName();
+                bmName = authUserMap.get(bmId).getFullName();
+            }
-            int managerId = csService.getAuthUserIdWithoutTicketAssignee(ProfitMandiConstants.TICKET_CATEGORY_SALES,EscalationType.L1,fofoId);
+            int managerId = salesManagerMap.getOrDefault(fofoId, 0);
-            String managerName = " ";
+            String managerName;
-            LOGGER.info("managerId {}",managerId);
+            if (managerId != 0 && authUserMap.get(managerId) != null) {
-            if(managerId != 0){
-                 managerName = authRepository.selectById(managerId).getFullName();
+                managerName = authUserMap.get(managerId).getFullName();
-            }else {
+            } else {
                 managerName = bmName;
+            }
-            String abmName ="";
+            String abmName = "";
-            int abmId = csService.getAuthUserIdWithoutTicketAssignee(ProfitMandiConstants.TICKET_CATEGORY_ABM,EscalationType.L1,fofoId);
+            int abmId = abmL1Map.getOrDefault(fofoId, 0);
-            int abmL2Id = csService.getAuthUserIdWithoutTicketAssignee(ProfitMandiConstants.TICKET_CATEGORY_ABM,EscalationType.L2,fofoId);
+            int abmL2Id = abmL2Map.getOrDefault(fofoId, 0);
-            int abmL3Id = csService.getAuthUserIdWithoutTicketAssignee(ProfitMandiConstants.TICKET_CATEGORY_ABM,EscalationType.L3,fofoId);
+            int abmL3Id = abmL3Map.getOrDefault(fofoId, 0);
-            if(abmId !=0){
+            if (abmId != 0 && authUserMap.get(abmId) != null) {
-                abmName = authRepository.selectById(abmId).getFullName();
+                abmName = authUserMap.get(abmId).getFullName();
-            }else if(abmL2Id != 0){
+            } else if (abmL2Id != 0 && authUserMap.get(abmL2Id) != null) {
-                abmName = authRepository.selectById(abmL2Id).getFullName();
+                abmName = authUserMap.get(abmL2Id).getFullName();
-            }else if(abmL3Id != 0){
+            } else if (abmL3Id != 0 && authUserMap.get(abmL3Id) != null) {
-                abmName = authRepository.selectById(abmL3Id).getFullName();
+                abmName = authUserMap.get(abmL3Id).getFullName();
+            }
-            AST ast = astRepository.selectById(customRetailers.get(fofoId).getAstId());
-            LOGGER.info("fofoID--11 {}",fofoId);
-            PartnerType partnerTypeThisMonth = partnerTypeChangeService.getTypeOnDate(fofoId, LocalDate.now());
-//            generate retaile detail
+            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());
-            if(ast != null){
+            biRetailerModel.setArea(ast != null ? ast.getArea() : "-");
-                biRetailerModel.setArea(ast.getArea());
+            FofoStore fofoStore = fofoStoreMap.get(fofoId);
-            }else {
+            String retailerStatus;
-                biRetailerModel.setArea("-");
+            if (fofoStore == null) {
-            }
-            String retailerStatus = "";
+                retailerStatus = "-";
-            FofoStore fofoStore1 = fofoStoreRepository.selectByRetailerId(fofoId);
-            if(!fofoStore1.isActive()){
+            } else if (!fofoStore.isActive()) {
                 retailerStatus = "INACTIVE";
-            }else{
+            } else {
-                retailerStatus =  String.valueOf(fofoStore1.getActivationType());
+                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);
-            biRetailerModelMap.put(fofoId,biRetailerModel);
-//            generate secondary data
-            List<PartnerWiseActivatedNotBilledTotal> partnerWiseActivatedNotBilledMonthlyTotals = activatedImeiRepository.getTotalMonthlyActivatedNotBilled(fofoId,twoMonthsAgoStartDate);
+            Map<YearMonth, PartnerWiseActivatedNotBilledTotal> partnerWiseActivatedNotBilledTotalMap =
-            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);
+                    activatedNotBilledByFofo.getOrDefault(fofoId, new HashMap<>());
+            // Current-month secondary
+            double currentSecondaryTarget = currentTargetMap.getOrDefault(fofoId, 0d);
+            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),
+            double dayBeforeYesterdayAfterReturnSecondary = dayBeforeSecondaryMap.getOrDefault(fofoId, 0d)
-                    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));
+                    - (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),
+            double yesterDayAfterReturnSecondary = yesterDaySecondaryMap.getOrDefault(fofoId, 0d)
-                    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));
+                    - (yesterdayReturnOrderInfoModelMap.getOrDefault(fofoId, 0L) + yesterdayRtoRefundOrderMap.getOrDefault(fofoId, 0L));
             fofoYesterdaySecondaryMap.put(fofoId, (long) yesterDayAfterReturnSecondary);
-            double secondaryAchievedMtd = secondaryMtd.getOrDefault(fofoId, 0.0);
+            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 currentMonthSecondaryPercent = currentSecondaryTarget == 0 ? 0.0
-            double currentMonthUnbilled = partnerWiseActivatedNotBilledTotalMap.get(currentMonth) != null ? partnerWiseActivatedNotBilledTotalMap.get(currentMonth).getTotalUnbilledAmount() : 0d;
-//          this month tertiary----------
-            LocalDateTime now = LocalDateTime.now();
+                    : Math.round(Math.abs((secondaryAchievedMtd / currentSecondaryTarget) * 100));
-            double todaySale = fofoOrderItemRepository.selectSumMopGroupByRetailer(startOfToday, now, fofoId, false).get(fofoId);
+            double currentMonthUnbilled = partnerWiseActivatedNotBilledTotalMap.get(currentMonth) != null
-            double mtdSaleTillYesterDay = fofoOrderItemRepository.selectSumMopGroupByRetailer(startOfToday.withDayOfMonth(1), previousDay, fofoId, false).get(fofoId);
+                    ? partnerWiseActivatedNotBilledTotalMap.get(currentMonth).getTotalUnbilledAmount() : 0d;
-            double mtdSale = mtdSaleTillYesterDay;
+            double mtdSale = mtdSaleTillYesterdayMap.getOrDefault(fofoId, 0d);
-//            last month secondary target
+            // Last month secondary
-            double lastMonthSecondaryTarget = monthlyTargetRepository.selectByDateAndFofoId(lastMonth, fofoId) != null ?  monthlyTargetRepository.selectByDateAndFofoId(lastMonth, fofoId).getPurchaseTarget() : 0;
+            double lastMonthSecondaryTarget = lastTargetMap.getOrDefault(fofoId, 0d);
-            long lastMonthReturn = (lastMonthPartnerReturnOrderInfoModelMap.getOrDefault(fofoId,0L) + lastMonthRtoRefundOrderMap.getOrDefault(fofoId,0L));
+            long lastMonthReturn = lastMonthPartnerReturnOrderInfoModelMap.getOrDefault(fofoId, 0L)
-            Map<Integer, Double> lastMonthSecondary = orderRepository.selectOrderValueBetweenBillingDatesGroupByFofoId(Arrays.asList(fofoId),
-                    lastMontStartDate, lastMonthEndDate).stream().collect(Collectors.toMap(x -> x.getId(), x -> x.getAmount()));
+                    + lastMonthRtoRefundOrderMap.getOrDefault(fofoId, 0L);
-            double lastMonthSecondaryAchieved = lastMonthSecondary.getOrDefault(fofoId, 0.0);
+            double lastMonthSecondaryAchieved = lastMonthSecondaryMap.getOrDefault(fofoId, 0.0);
             double lastMonthNetSecondary = lastMonthSecondaryAchieved - lastMonthReturn;
+            double lastMonthSecondaryPercent = lastMonthSecondaryTarget == 0 ? 0.0
-            double lastMonthSecondaryPercent = lastMonthSecondaryTarget == 0 ? 0.0 : Math.round(Math.abs((lastMonthSecondaryAchieved / lastMonthSecondaryTarget) * 100));
+                    : Math.round(Math.abs((lastMonthSecondaryAchieved / lastMonthSecondaryTarget) * 100));
-            double lastMonthUnbilled = partnerWiseActivatedNotBilledTotalMap.get(lastMonth) != null ? partnerWiseActivatedNotBilledTotalMap.get(lastMonth).getTotalUnbilledAmount() : 0d;
+            double lastMonthUnbilled = partnerWiseActivatedNotBilledTotalMap.get(lastMonth) != null
-//           last month tertiary
-            Double lastMonthSale = fofoOrderItemRepository.selectSumMopGroupByRetailer(
+                    ? partnerWiseActivatedNotBilledTotalMap.get(lastMonth).getTotalUnbilledAmount() : 0d;
-                    lastMontStartDate, lastMonthEndDate, fofoId, false).get(fofoId);
+            double lastMonthSale = lastMonthSaleMap.getOrDefault(fofoId, 0d);
-//            two month ago secondary target
+            // Two months ago secondary
-            double twoMonthAgoSecondaryTarget = monthlyTargetRepository.selectByDateAndFofoId(twoMonthsAgo, fofoId) != null ? monthlyTargetRepository.selectByDateAndFofoId(twoMonthsAgo, fofoId).getPurchaseTarget() : 0;
+            double twoMonthAgoSecondaryTarget = twoMonthAgoTargetMap.getOrDefault(fofoId, 0d);
-            long twoMonthAgoReturn = (twoMonthAgoPartnerReturnOrderInfoModelMap.getOrDefault(fofoId,0L) + twoMonthAgoRtoRefundOrderMap.getOrDefault(fofoId,0L));
+            long twoMonthAgoReturn = twoMonthAgoPartnerReturnOrderInfoModelMap.getOrDefault(fofoId, 0L)
-            Map<Integer, Double> twoMonthAgoSecondary = orderRepository.selectOrderValueBetweenBillingDatesGroupByFofoId(Arrays.asList(fofoId),
-                    twoMonthsAgoStartDate, twoMonthsAgoEndDate).stream().collect(Collectors.toMap(x -> x.getId(), x -> x.getAmount()));
+                    + twoMonthAgoRtoRefundOrderMap.getOrDefault(fofoId, 0L);
-            double twoMonthAgoSecondaryAchieved = twoMonthAgoSecondary.getOrDefault(fofoId, 0.0);
+            double twoMonthAgoSecondaryAchieved = twoMonthAgoSecondaryMap.getOrDefault(fofoId, 0.0);
             double twoMonthAgoNetSecondary = twoMonthAgoSecondaryAchieved - twoMonthAgoReturn;
+            double twoMonthAgoSecondaryPercent = twoMonthAgoSecondaryTarget == 0 ? 0.0
-            double twoMonthAgoSecondaryPercent = twoMonthAgoSecondaryTarget == 0 ? 0.0 : Math.round(Math.abs((twoMonthAgoSecondaryAchieved / twoMonthAgoSecondaryTarget) * 100));
+                    : Math.round(Math.abs((twoMonthAgoSecondaryAchieved / twoMonthAgoSecondaryTarget) * 100));
-            double twoMonthAgoUnbilled = partnerWiseActivatedNotBilledTotalMap.get(twoMonthsAgo) != null ? partnerWiseActivatedNotBilledTotalMap.get(twoMonthsAgo).getTotalUnbilledAmount() : 0d;
+            double twoMonthAgoUnbilled = partnerWiseActivatedNotBilledTotalMap.get(twoMonthsAgo) != null
-//          second Month Tertiary
-            double twoMonthAgoSale = fofoOrderItemRepository.selectSumMopGroupByRetailer(
+                    ? partnerWiseActivatedNotBilledTotalMap.get(twoMonthsAgo).getTotalUnbilledAmount() : 0d;
-                    twoMonthsAgoStartDate, twoMonthsAgoEndDate, fofoId, false).get(fofoId);
+            double twoMonthAgoSale = twoMonthAgoSaleMap.getOrDefault(fofoId, 0d);
             Map<YearMonth, BiSecondaryModel> monthlySecondaryModels = new HashMap<>();
-            BiSecondaryModel currentMonthSecondaryModel = new BiSecondaryModel(
+            monthlySecondaryModels.put(currentMonth, new BiSecondaryModel(
-                    currentSecondaryTarget,
-                    secondaryAchievedMtd,
-                    currentMonthReturn,
-                    currentMonthNetSecondary,
-                    currentMonthSecondaryPercent,
+                    currentSecondaryTarget, secondaryAchievedMtd, currentMonthReturn,
-                    mtdSale,
-                    currentMonthUnbilled // for now, unbilled tertiary value
+                    currentMonthNetSecondary, currentMonthSecondaryPercent, mtdSale, currentMonthUnbilled));
-            );
-            BiSecondaryModel lastMonthSecondaryModel = new BiSecondaryModel(
+            monthlySecondaryModels.put(lastMonth, new BiSecondaryModel(
-                    lastMonthSecondaryTarget,
-                    lastMonthSecondaryAchieved,
+                    lastMonthSecondaryTarget, lastMonthSecondaryAchieved, lastMonthReturn,
-                    lastMonthReturn,
-                    lastMonthNetSecondary,
-                    lastMonthSecondaryPercent,
-                    lastMonthSale,
-                    lastMonthUnbilled // for now, unbilled tertiary value
+                    lastMonthNetSecondary, lastMonthSecondaryPercent, lastMonthSale, lastMonthUnbilled));
-            );
-            BiSecondaryModel twoMonthAgoSecondaryModel = new BiSecondaryModel(
+            monthlySecondaryModels.put(twoMonthsAgo, new BiSecondaryModel(
-                    twoMonthAgoSecondaryTarget,
-                    twoMonthAgoSecondaryAchieved,
-                    twoMonthAgoReturn,
-                    twoMonthAgoNetSecondary,
-                    twoMonthAgoSecondaryPercent,
-                    twoMonthAgoSale,
-                    twoMonthAgoUnbilled // for now, unbilled tertiary value
+                    twoMonthAgoSecondaryTarget, twoMonthAgoSecondaryAchieved, twoMonthAgoReturn,
-            );
-            monthlySecondaryModels.put(currentMonth, currentMonthSecondaryModel);
-            monthlySecondaryModels.put(lastMonth, lastMonthSecondaryModel);
-            monthlySecondaryModels.put(twoMonthsAgo, twoMonthAgoSecondaryModel);
+                    twoMonthAgoNetSecondary, twoMonthAgoSecondaryPercent, twoMonthAgoSale, twoMonthAgoUnbilled));
             allRetailerMonthlyData.put(fofoId, monthlySecondaryModels);
-//            brandwiseStock value price
+            // 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());
-            fofoBrandStockPriceMap.put(fofoId,brandStockPriceMap);
-            double totalStockPrice = brandStockPriceMap.values().stream().mapToDouble(x->x.getTotalValue()).sum();
+            Map<String, Double> brandMtdTertiaryAmount = brandTertiaryByFofo.getOrDefault(fofoId, new HashMap<>());
-            fofoTotalStockPriceMap.put(fofoId,totalStockPrice);
+            fofoBrandMtdTertiaryMap.put(fofoId, brandMtdTertiaryAmount);
-            Map<String, Double> brandMtdTertiaryAmount = fofoOrderItemRepository.selectSumAmountGroupByBrand(
+            fofoTotalMtdTertiaryMap.put(fofoId, brandMtdTertiaryAmount.values().stream().mapToDouble(Double::doubleValue).sum());
-                    currentMonthStartDate, currentMonthEndDate, fofoId);
-            fofoBrandMtdTertiaryMap.put(fofoId,brandMtdTertiaryAmount);
+            Map<String, Long> brandWiseMtdSecondaryMap = brandBilledByFofo.getOrDefault(fofoId, new HashMap<>());
-            double totalMtdTertiaryAmount = brandMtdTertiaryAmount.values().stream().mapToDouble(Double::doubleValue).sum();
+            Map<String, Double> brandWiseReturnInfoMap = brandReturnByFofo.getOrDefault(fofoId, new HashMap<>());
-            fofoTotalMtdTertiaryMap.put(fofoId,totalMtdTertiaryAmount);
+            Map<String, Double> brandWiseRTOReturnInfoMap = brandRtoReturnByFofo.getOrDefault(fofoId, new HashMap<>());
-            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);
+                long billedAmount = brandWiseMtdSecondaryMap.getOrDefault(brand, 0L);
-                Double returnAmount = brandWiseReturnInfoMap.getOrDefault(brand, 0d);
+                double returnAmount = brandWiseReturnInfoMap.getOrDefault(brand, 0d);
-                Double rtoReturnAmount = brandWiseRTOReturnInfoMap.getOrDefault(brand, 0d);
+                double rtoReturnAmount = brandWiseRTOReturnInfoMap.getOrDefault(brand, 0d);
-                double netSecondary = billedAmount - (returnAmount + rtoReturnAmount);
+                brandWiseMtdNetSecondaryMap.put(brand, Math.round(billedAmount - (returnAmount + rtoReturnAmount)));
-                brandWiseMtdNetSecondaryMap.put(brand, (long) Math.round(netSecondary));
+            }
+            fofoBrandWiseMtdSecondaryMap.put(fofoId, brandWiseMtdNetSecondaryMap);
+            fofoTotalMtdSecondaryMap.put(fofoId, brandWiseMtdNetSecondaryMap.values().stream().mapToLong(Long::longValue).sum());
+            // Investment info
+            PartnerDailyInvestment 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;
-            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);
+            List<Loan> fofoDefaultLoans = defaultLoanMap.get(fofoId);
-                LOGGER.info("fofoDefaultLoans {}",fofoDefaultLoans);
-            }
             float defaultLoanAmount = 0f;
-            if(fofoDefaultLoans != null ){
+            if (fofoDefaultLoans != null) {
-                if (!fofoDefaultLoans.isEmpty()) {
-                    for (Loan entry : fofoDefaultLoans) {
+                for (Loan entry : fofoDefaultLoans) {
-                        List<LoanStatement> loanStatements = loanStatementRepository.selectByLoanId(entry.getId());
+                    double amount = loanStatementSumByLoanId.getOrDefault(entry.getId(), 0d);
-                        double amount = loanStatements.stream().map(x -> x.getAmount()).collect(Collectors.summingDouble(x -> x.doubleValue()));
-                        defaultLoanAmount += amount;
+                    defaultLoanAmount += amount;
-                    }
+                }
+            }
-            List<Loan> activeLoans = loanRepository.selectAllActiveLoan(fofoId);
+            List<Loan> activeLoans = activeLoansByFofo.getOrDefault(fofoId, Collections.emptyList());
-            LOGGER.info("activeLoans- {}",activeLoans);
             float activeLoan = 0f;
             for (Loan entry : activeLoans) {
-                List<LoanStatement> loanStatements = loanStatementRepository.selectByLoanId(entry.getId());
+                double pendingAmount = loanStatementSumByLoanId.getOrDefault(entry.getId(), 0d);
-                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 poValue = pdi != null ? pdi.getUnbilledAmount() : 0f;
             float poAndBilledValue = (float) (currentMonthNetSecondary + poValue);
-            double monthDay1Drr = rbmTargetService.calculateFofoIdTodayTarget(fofoId,0d,currentMonth.atDay(1));
+            // 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 todayRequiredDrr = 0;
+            double monthDay1Drr = 0d;
-            if(monthDay1Drr != 0d){
+            if (drrTarget != 0d) {
+                double remainingTarget = drrTarget;
-                todayRequiredDrr = rbmTargetService.calculateFofoIdTodayTarget(fofoId, currentMonthNetSecondary,startOfToday.toLocalDate());
+                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 = (todayRequiredDrr / monthDay1Drr) * 100;
+            double gotDrrPercent = monthDay1Drr == 0 ? 0 : (todayRequiredDrr / monthDay1Drr) * 100;
             long drrPercentDisplay = Math.round(Math.abs(gotDrrPercent));
-            int orderId = orderRepository.getLastOrderByFofoId(fofoId);
+            int orderId = lastOrderIdByFofo.getOrDefault(fofoId, 0);
-            // Determine alert level
             String alertLevel = "-";
             int lastPurchaseDays = 0;
             if (orderId != 0) {
-                Order order = orderRepository.selectById(orderId);
+                Order order = lastOrderById.get(orderId);
-                LOGGER.info("last billing order - {}",order);
+                if (order != null) {
-                // Calculate the number of days since the last purchase (billing)
-                lastPurchaseDays = (int) Duration.between(order.getCreateTimestamp().plusDays(1), LocalDateTime.now()).toDays();
+                    lastPurchaseDays = (int) Duration.between(order.getCreateTimestamp().plusDays(1), LocalDateTime.now()).toDays();
-                if (lastPurchaseDays >= 11) {
-                    alertLevel = "Alert for Management";
+                    if (lastPurchaseDays >= 11) alertLevel = "Alert for Management";
-                } else if (lastPurchaseDays >= 10) {
-                    alertLevel = " Alert for RSM/SH";
+                    else if (lastPurchaseDays >= 10) alertLevel = " Alert for RSM/SH";
-                } else if (lastPurchaseDays >= 7) {
-                    alertLevel = "Must be Billed";
+                    else if (lastPurchaseDays >= 7) alertLevel = "Must be Billed";
-                } else if (lastPurchaseDays >= 3) {
-                    alertLevel = "OK";
-                } else {
-                    alertLevel = "OK";
+                    else alertLevel = "OK";
+                }
+            }
-            //investment modal set all related value
             FofoInvestmentModel fofoInvestmentModel = new FofoInvestmentModel();
-            fofoInvestmentModel.setCounterPotential(fofoStore.getCounterPotential());
+            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(partnerDailyInvestmentMap.get(fofoId) != null ? partnerDailyInvestmentMap.get(fofoId).getWalletAmount() : 0);
+            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);
-                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);
+            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());
         //generate excel and sent to mail
         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()) {
-            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd/MM/yyyy");
             Integer fofoId = entry.getKey();
-            User user = userRepository.selectById(fofoId);
+            User user = userMap.get(fofoId);
-            LocalDateTime walletCreationDate = userWalletHistoryRepository.selectFirstCreatedDate(fofoId);
+            LocalDateTime walletCreationDate = walletFirstCreatedMap.get(fofoId);
-            if(walletCreationDate == null){
+            if (walletCreationDate == null && user != null) {
                 walletCreationDate = user.getCreateTimestamp();
+            }
             BIRetailerModel retailer = entry.getValue();
             Map<YearMonth, BiSecondaryModel> monthlyData = allRetailerMonthlyData.get(fofoId);
                     billingNeededMap.get(fofoId),
                     countAMap.get(fofoId),
                     retailer.getBmName(),
                     fofoId ,
                     "https://partners.smartdukaan.com/partnerPerformance?fofoId="+fofoId,
-                    walletCreationDate.format(formatter),
+                    walletCreationDate != null ? walletCreationDate.format(formatter) : "-",
-                    user.getCreateTimestamp() != null ? (user.getCreateTimestamp()).format(formatter) : "-",
+                    (user != null && user.getCreateTimestamp() != null) ? user.getCreateTimestamp().format(formatter) : "-",
                     retailer.getCode(),
                     retailer.getArea(),
                     retailer.getCity(),
                     retailer.getStoreName(),
                     retailer.getStatus(),

Subversion Repositories SmartDukaan

(root)/trunk/profitmandi-cron/src/main/java/com/smartdukaan/cron/scheduled/ScheduledTasksTest.java – Rev 36402 → 36973