WebSVN – SmartDukaan – Diff – /trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/RbmTargetServiceImpl.java

         Map<Integer, Long> outOfSequenceCountByAuthId = outOfSequenceLogs.stream()
                 .collect(Collectors.groupingBy(RbmCallSequenceLog::getAuthId,
                         Collectors.mapping(RbmCallSequenceLog::getFofoId, Collectors.collectingAndThen(Collectors.toSet(), s -> (long) s.size()))));
         LOGGER.info("RBM Call Target - Out of Sequence fetch: {}ms", System.currentTimeMillis() - start);
+        // BATCH FETCH: All call logs for all RBMs (L1 + L2 + L3) in a single query.
+        // Replaces the previous N+1 pattern where getCallStats() was called per RBM.
+        start = System.currentTimeMillis();
+        List<AgentCallLog> allCallLogs = agentCallLogRepository.findByAuthIdsAndDate(rbmPositionsAuthIds, queryDate);
+        Map<Long, List<AgentCallLog>> callLogsByAuthId = allCallLogs.stream()
+                .collect(Collectors.groupingBy(AgentCallLog::getAuthId));
+        LOGGER.info("RBM Call Target - Call logs batch fetch: {}ms ({} logs across {} RBMs)",
+                System.currentTimeMillis() - start, allCallLogs.size(), callLogsByAuthId.size());
+        // BATCH FETCH: Build a single mobile -> fofoId map from all unique customer numbers across all call logs.
+        // Replaces the previous N+1 pattern where findFofoIdByMobile() was called per call log entry.
+        start = System.currentTimeMillis();
+        Set<String> allNormalizedMobiles = new HashSet<>();
+        for (AgentCallLog callLog : allCallLogs) {
+            String customerNumber = callLog.getCustomerNumber();
+            if (customerNumber != null) {
+                String normalized = customerNumber.startsWith("+91") ? customerNumber.substring(3) : customerNumber;
+                allNormalizedMobiles.add(normalized);
+            }
+        }
+        Map<String, Integer> mobileToFofoIdMap = buildMobileToFofoIdMap(allNormalizedMobiles);
+        LOGGER.info("RBM Call Target - Mobile→FofoId batch fetch: {}ms ({} unique mobiles, {} mapped)",
+                System.currentTimeMillis() - start, allNormalizedMobiles.size(), mobileToFofoIdMap.size());
         // Identify users who are both L1 and L2 — they will be shown only as L2
         Set<Integer> l2AuthIdSet = new HashSet<>(l2AuthIds);
         // Process L1 RBMs (skip users who are also L2 — their data will be merged into L2 model)
         for (int rbmAuthId : l1AuthIds) {
             todayTargetPartners.addAll(planTodayPartners);
             todayTargetPartners.addAll(carryForwardPartners);
             todayTargetPartners.addAll(zeroBillingPartners);
             todayTargetPartners.addAll(untouchedPartners);
-            // Value Achieved = All distinct partners called today (from call logs)
+            // Value Achieved = All distinct partners called today (from pre-fetched call logs)
-            long[] callStats = getCallStats(rbmAuthId, queryDate);
+            long[] callStats = getCallStatsFromLogs(callLogsByAuthId.get((long) rbmAuthId), mobileToFofoIdMap);
             targetModel.setValueTargetAchieved(callStats[0]);
             targetModel.setTotalRecordingCalls(callStats[1]);
             targetModel.setUniqueRecordingCalls(callStats[2]);
             // Keep todayRemarks for movedToFuture calculation
             } else {
                 // Pure L2 (not also L1) — target is only L2 escalation
                 l2Model.setTodayTargetOfCall(l2TargetFofoIds.size());
+            }
-            // Value Achieved = All distinct partners called today (from call logs)
+            // Value Achieved = All distinct partners called today (from pre-fetched call logs)
-            long[] l2CallStats = getCallStats(l2AuthId, queryDate);
+            long[] l2CallStats = getCallStatsFromLogs(callLogsByAuthId.get((long) l2AuthId), mobileToFofoIdMap);
             l2Model.setValueTargetAchieved(l2CallStats[0]);
             l2Model.setTotalRecordingCalls(l2CallStats[1]);
             l2Model.setUniqueRecordingCalls(l2CallStats[2]);
             l2Model.setOutOfSequenceCount(outOfSequenceCountByAuthId.getOrDefault(l2AuthId, 0L));
             l3Model.setPartnerCount(l3AssignedFofoIds.size());
             // L3 Target = partners with RBM_L3_ESCALATION as latest remark
             l3Model.setTodayTargetOfCall(l3TargetFofoIds.size());
-            // Value Achieved = All distinct partners called today (from call logs)
+            // Value Achieved = All distinct partners called today (from pre-fetched call logs)
-            long[] l3CallStats = getCallStats(l3AuthId, queryDate);
+            long[] l3CallStats = getCallStatsFromLogs(callLogsByAuthId.get((long) l3AuthId), mobileToFofoIdMap);
             l3Model.setValueTargetAchieved(l3CallStats[0]);
             l3Model.setTotalRecordingCalls(l3CallStats[1]);
             l3Model.setUniqueRecordingCalls(l3CallStats[2]);
             l3Model.setOutOfSequenceCount(outOfSequenceCountByAuthId.getOrDefault(l3AuthId, 0L));
     public long getCalledCountFromCallLogs(long authId, LocalDate date) {
         return getCallStats(authId, date)[0];
+    }
     /**
+     * Batch-builds a mobile (normalized, +91 stripped) -> fofoId mapping for all the given mobiles
+     * in just two queries (retailer_contact, then address fallback for unmapped numbers).
+     * Replaces the previous per-call-log findFofoIdByMobile() N+1 lookups.
+     */
+    private Map<String, Integer> buildMobileToFofoIdMap(Set<String> normalizedMobiles) {
+        Map<String, Integer> result = new HashMap<>();
+        if (normalizedMobiles == null || normalizedMobiles.isEmpty()) {
+            return result;
+        }
+        List<String> mobilesList = new ArrayList<>(normalizedMobiles);
+        // First pass: retailer_contact
+        List<RetailerContact> contacts = retailerContactRepository.selectByMobiles(mobilesList);
+        for (RetailerContact rc : contacts) {
+            // Keep the first fofoId we see per mobile (matches old single-mobile behavior of get(0))
+            result.putIfAbsent(rc.getMobile(), rc.getFofoId());
+        }
+        // Second pass: address fallback for mobiles not yet mapped
+        List<String> unmapped = mobilesList.stream()
+                .filter(m -> !result.containsKey(m))
+                .collect(Collectors.toList());
+        if (!unmapped.isEmpty()) {
+            List<Address> addresses = addressRepository.selectAllByPhoneNumbers(unmapped);
+            for (Address addr : addresses) {
+                result.putIfAbsent(addr.getPhoneNumber(), addr.getRetaierId());
+            }
+        }
+        return result;
+    }
+    /**
+     * In-memory variant of getCallStats() that uses pre-fetched call logs and mobile→fofoId mapping.
+     * Used by the batch-fetched RBM Call Target loop to avoid N+1 query patterns.
+     */
+    private long[] getCallStatsFromLogs(List<AgentCallLog> callLogs, Map<String, Integer> mobileToFofoIdMap) {
+        if (callLogs == null || callLogs.isEmpty()) {
+            return new long[]{0, 0, 0};
+        }
+        Set<Integer> calledFofoIds = new HashSet<>();
+        Set<String> calledNumbersWithoutFofoId = new HashSet<>();
+        long totalRecordingCalls = 0;
+        Set<String> uniqueRecordingNumbers = new HashSet<>();
+        for (AgentCallLog callLog : callLogs) {
+            String customerNumber = callLog.getCustomerNumber();
+            if (customerNumber != null) {
+                String normalized = customerNumber.startsWith("+91") ? customerNumber.substring(3) : customerNumber;
+                Integer fofoId = mobileToFofoIdMap.get(normalized);
+                if (fofoId != null) {
+                    calledFofoIds.add(fofoId);
+                } else {
+                    calledNumbersWithoutFofoId.add(normalized);
+                }
+                if (callLog.getRecordingUrl() != null && !callLog.getRecordingUrl().isEmpty()
+                        && !"None".equalsIgnoreCase(callLog.getRecordingUrl())) {
+                    totalRecordingCalls++;
+                    uniqueRecordingNumbers.add(normalized);
+                }
+            }
+        }
+        long calledCount = calledFofoIds.size() + calledNumbersWithoutFofoId.size();
+        return new long[]{calledCount, totalRecordingCalls, uniqueRecordingNumbers.size()};
+    }
+    /**
      * Returns call stats: [0] = called count, [1] = total recording calls, [2] = unique recording calls
      */
     public long[] getCallStats(long authId, LocalDate date) {
         List<AgentCallLog> callLogs = agentCallLogRepository.findByAuthIdAndDate(authId, date);

Subversion Repositories SmartDukaan

(root)/trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/service/RbmTargetServiceImpl.java – Rev 36276 → 36284