Subversion Repositories SmartDukaan

Rev

Rev 37505 | Rev 37533 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
33917 ranu 1 
package com.spice.profitmandi.service;
2 
 
35631 ranu 3 
import com.spice.profitmandi.common.enumuration.ActivationType;
4 
import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;
5 
import com.spice.profitmandi.common.model.CustomRetailer;
33917 ranu 6 
import com.spice.profitmandi.common.model.ProfitMandiConstants;
35631 ranu 7 
import com.spice.profitmandi.dao.entity.auth.AuthUser;
8 
import com.spice.profitmandi.dao.entity.auth.PartnerCollectionRemark;
9 
import com.spice.profitmandi.dao.entity.auth.RbmCallSequenceLog;
35761 ranu 10 
import com.spice.profitmandi.dao.entity.cs.AgentCallLog;
35631 ranu 11 
import com.spice.profitmandi.dao.entity.cs.Position;
12 
import com.spice.profitmandi.dao.entity.cs.Ticket;
13 
import com.spice.profitmandi.dao.entity.fofo.FofoStore;
34397 ranu 14 
import com.spice.profitmandi.dao.entity.fofo.MonthlyTarget;
35759 ranu 15 
import com.spice.profitmandi.dao.entity.fofo.RetailerContact;
33985 ranu 16 
import com.spice.profitmandi.dao.entity.inventory.RbmAchievements;
33926 ranu 17 
import com.spice.profitmandi.dao.entity.inventory.RbmTargets;
37497 ranu 18 
import com.spice.profitmandi.dao.entity.inventory.SalesAchievements;
19 
import com.spice.profitmandi.dao.entity.inventory.SalesTargets;
35759 ranu 20 
import com.spice.profitmandi.dao.entity.user.Address;
35631 ranu 21 
import com.spice.profitmandi.dao.enumuration.auth.CollectionRemark;
22 
import com.spice.profitmandi.dao.enumuration.cs.EscalationType;
33917 ranu 23 
import com.spice.profitmandi.dao.model.*;
35631 ranu 24 
import com.spice.profitmandi.dao.repository.auth.AuthRepository;
25 
import com.spice.profitmandi.dao.repository.auth.PartnerCollectionRemarkRepository;
26 
import com.spice.profitmandi.dao.repository.auth.RbmCallSequenceLogRepository;
33985 ranu 27 
import com.spice.profitmandi.dao.repository.catalog.RbmAchievementsRepository;
33926 ranu 28 
import com.spice.profitmandi.dao.repository.catalog.RbmTargetsRepository;
37497 ranu 29 
import com.spice.profitmandi.dao.repository.catalog.SalesAchievementsRepository;
30 
import com.spice.profitmandi.dao.repository.catalog.SalesTargetsRepository;
35631 ranu 31 
import com.spice.profitmandi.dao.repository.cs.CsService;
32 
import com.spice.profitmandi.dao.repository.cs.PositionRepository;
33 
import com.spice.profitmandi.dao.repository.dtr.FofoStoreRepository;
35759 ranu 34 
import com.spice.profitmandi.dao.repository.dtr.RetailerContactRepository;
34397 ranu 35 
import com.spice.profitmandi.dao.repository.fofo.MonthlyTargetRepository;
34880 ranu 36 
import com.spice.profitmandi.dao.repository.logistics.PublicHolidaysRepository;
35631 ranu 37 
import com.spice.profitmandi.dao.repository.transaction.LoanRepository;
34397 ranu 38 
import com.spice.profitmandi.dao.repository.transaction.OrderRepository;
35759 ranu 39 
import com.spice.profitmandi.dao.repository.user.AddressRepository;
35631 ranu 40 
import com.spice.profitmandi.service.user.RetailerService;
33917 ranu 41 
import org.apache.logging.log4j.LogManager;
42 
import org.apache.logging.log4j.Logger;
43 
import org.hibernate.Session;
44 
import org.hibernate.SessionFactory;
45 
import org.hibernate.query.NativeQuery;
46 
import org.springframework.beans.factory.annotation.Autowired;
36334 ranu 47 
import org.springframework.cache.annotation.Cacheable;
33917 ranu 48 
import org.springframework.stereotype.Component;
49 
 
50 
import javax.persistence.TypedQuery;
34880 ranu 51 
import java.time.*;
35631 ranu 52 
import java.time.format.DateTimeFormatter;
34880 ranu 53 
import java.time.temporal.ChronoUnit;
35631 ranu 54 
import java.util.*;
33997 ranu 55 
import java.util.stream.Collectors;
33917 ranu 56 
 
57 
@Component
58 
public class RbmTargetServiceImpl implements RbmTargetService {
59 
    private static final Logger LOGGER = LogManager.getLogger(RbmTargetServiceImpl.class);
60 
 
61 
    @Autowired
62 
    SessionFactory sessionFactory;
63 
 
33926 ranu 64 
    @Autowired
65 
    RbmTargetsRepository rbmTargetsRepository;
33917 ranu 66 
 
33985 ranu 67 
    @Autowired
37497 ranu 68 
    SalesTargetsRepository salesTargetsRepository;
69 
 
70 
    @Autowired
71 
    SalesAchievementsRepository salesAchievementsRepository;
72 
 
73 
    @Autowired
74 
    AuthService authService;
75 
 
76 
    @Autowired
33985 ranu 77 
    RbmAchievementsRepository rbmAchievementsRepository;
78 
 
34397 ranu 79 
    @Autowired
80 
    MonthlyTargetRepository monthlyTargetRepository;
81 
 
34880 ranu 82 
    @Autowired
83 
    PublicHolidaysRepository publicHolidaysRepository;
84 
 
35631 ranu 85 
    @Autowired
86 
    RetailerService retailerService;
87 
 
33917 ranu 88 
    @Override
89 
    public List<WarehouseRbmTargetModel> getWarehouseWiseRbmMonthlyTarget() {
90 
        Session session = sessionFactory.getCurrentSession();
91 
        final TypedQuery<WarehouseRbmTargetModel> typedQuerySimilar = session.createNamedQuery("RbmTarget.getWarehouseWiseMonthlyTarget", WarehouseRbmTargetModel.class);
92 
 
93 
        return typedQuerySimilar.getResultList();
94 
 
95 
    }
96 
 
97 
    @Override
98 
    public List<MTDAchievedTargetModel> getDateWiseAchievedTargetOfRbm(LocalDate startDate, LocalDate endDate) {
99 
        Session session = sessionFactory.getCurrentSession();
100 
        final TypedQuery<MTDAchievedTargetModel> typedQuerySimilar = session.createNamedQuery("RbmTarget.getRbmAchievedMonthlyTarget", MTDAchievedTargetModel.class);
101 
        typedQuerySimilar.setParameter("startDate", startDate);
102 
        typedQuerySimilar.setParameter("endDate", endDate);
103 
        return typedQuerySimilar.getResultList();
104 
 
105 
    }
106 
 
107 
    @Override
34055 ranu 108 
    public List<TodayAchievedMovementModel> getMovementWiseAchievementByDate(LocalDate startDate, LocalDate endDate) {
33917 ranu 109 
        LOGGER.info("start date {}, end date {}", startDate, endDate);
110 
        Session session = sessionFactory.getCurrentSession();
34055 ranu 111 
        final TypedQuery<TodayAchievedMovementModel> typedQuerySimilar = session.createNamedQuery("RBMTarget.TodayAchivementByMovement", TodayAchievedMovementModel.class);
33917 ranu 112 
        typedQuerySimilar.setParameter("startDate", startDate);
113 
        typedQuerySimilar.setParameter("endDate", endDate);
114 
        return typedQuerySimilar.getResultList();
115 
 
116 
    }
117 
 
118 
    @Override
37442 ranu 119 
    public List<RbmBrandSoldModel> getBrandsSoldByRbm(int authId, int warehouseId, LocalDate startDate, LocalDate endDate) {
120 
        Session session = sessionFactory.getCurrentSession();
121 
        final TypedQuery<RbmBrandSoldModel> q = session.createNamedQuery("RbmDrr.BrandSoldByRbm", RbmBrandSoldModel.class);
122 
        q.setParameter("authId", authId);
123 
        q.setParameter("warehouseId", warehouseId);
124 
        q.setParameter("startDate", startDate);
125 
        q.setParameter("endDate", endDate);
126 
        return q.getResultList();
127 
    }
128 
 
129 
    @Override
37460 ranu 130 
    public List<RbmBrandPartnerCountModel> getBrandPartnerCountByRbm(int authId, int warehouseId, LocalDate startDate, LocalDate endDate) {
131 
        Session session = sessionFactory.getCurrentSession();
132 
        final TypedQuery<RbmBrandPartnerCountModel> q = session.createNamedQuery("RbmDrr.BrandPartnerCountByRbm", RbmBrandPartnerCountModel.class);
133 
        q.setParameter("authId", authId);
134 
        q.setParameter("warehouseId", warehouseId);
135 
        q.setParameter("startDate", startDate);
136 
        q.setParameter("endDate", endDate);
137 
        return q.getResultList();
138 
    }
139 
 
140 
    @Override
37487 ranu 141 
    public List<WarehouseBuyingByMovementModel> getWarehouseBuyingByMovement(int warehouseId, LocalDate startDate, LocalDate endDate) {
142 
        Session session = sessionFactory.getCurrentSession();
143 
        final TypedQuery<WarehouseBuyingByMovementModel> q = session.createNamedQuery("RbmDrr.WarehouseBuyingByMovement", WarehouseBuyingByMovementModel.class);
144 
        q.setParameter("warehouseId", warehouseId);
145 
        q.setParameter("startDate", startDate);
146 
        q.setParameter("endDate", endDate);
147 
        return q.getResultList();
148 
    }
149 
 
150 
    @Override
33917 ranu 151 
    public List<WarehouseMobileStockByMovementModel> getWarehouseMobileStockByMovement() {
152 
        Session session = sessionFactory.getCurrentSession();
153 
        final TypedQuery<WarehouseMobileStockByMovementModel> typedQuerySimilar = session.createNamedQuery("WarehouseStock.MovementWiseMobileStock", WarehouseMobileStockByMovementModel.class);
154 
 
155 
        return typedQuerySimilar.getResultList();
156 
 
157 
    }
158 
 
33991 ranu 159 
    @Override
160 
    public List<SoldCatalogsReportModel> getCatalogSoldReport(LocalDate startDate, LocalDate endDate) {
161 
        Session session = sessionFactory.getCurrentSession();
162 
        final TypedQuery<SoldCatalogsReportModel> typedQuerySimilar = session.createNamedQuery("CatalogsReport.SoldCatalogsReport", SoldCatalogsReportModel.class);
163 
        typedQuerySimilar.setParameter("startDate", startDate);
164 
        typedQuerySimilar.setParameter("endDate", endDate);
165 
        return typedQuerySimilar.getResultList();
33917 ranu 166 
 
33991 ranu 167 
    }
168 
 
169 
 
33917 ranu 170 
    public int getWorkingDaysCount(LocalDate startDate) {
171 
        Session session = sessionFactory.getCurrentSession();
172 
 
173 
        // Convert the LocalDate to a format MySQL can interpret
174 
        String startDateString = startDate.toString();
175 
 
176 
        final NativeQuery<?> nativeQuery = session.createNativeQuery(
177 
                "SELECT (DATEDIFF(LAST_DAY(:startDate), :startDate) + 1) " +
178 
                        " - (FLOOR((DATEDIFF(LAST_DAY(:startDate), :startDate) + (WEEKDAY(:startDate) + 1)) / 7)) " +
179 
                        " - (SELECT COUNT(*) " +
180 
                        " FROM logistics.publicholidays " +
181 
                        " WHERE date BETWEEN :startDate AND LAST_DAY(:startDate) " +
182 
                        " AND WEEKDAY(date) != 6) AS working_days"
183 
        );
184 
 
185 
        // Set the start date parameter for each placeholder
186 
        nativeQuery.setParameter("startDate", startDateString);
187 
 
188 
        Object result = nativeQuery.getSingleResult();
189 
        return result != null ? ((Number) result).intValue() : 0;
190 
    }
191 
 
192 
 
35044 ranu 193 
 
33917 ranu 194 
    @Override
195 
    public List<RbmArrViewModel> getRbmTodayArr() throws Exception {
196 
        LocalDate todayDate = LocalDate.now();
197 
        return getRbmTodayArr(todayDate);
198 
    }
199 
 
200 
    @Override
33997 ranu 201 
    public List<RbmTargetAndAchievementsModel> getRbmTargetsAndAchievemnts(LocalDate startDate, LocalDate endDate) {
202 
 
34002 ranu 203 
        List<RbmTargetsModel> rbmTargetsList = rbmTargetsRepository.selectTargetsModelListByDates(startDate.atStartOfDay(), endDate.atTime(LocalTime.MAX));
33997 ranu 204 
 
205 
        LOGGER.info("rbmTargetsList {}", rbmTargetsList);
206 
        // Group Targtes by RBM and Warehouse
34002 ranu 207 
        Map<String, RbmTargetsModel> targetsMap = rbmTargetsList.stream()
33997 ranu 208 
                .collect(Collectors.toMap(
209 
                        a -> a.getRbmAuthId() + "-" + a.getWarehouseId(),
210 
                        a -> a,
211 
                        (a1, a2) -> mergeTargets(a1, a2) // Handle duplicates by merging
212 
                ));
213 
 
214 
 
34002 ranu 215 
        List<RbmAchievementsModel> rbmAchievements = rbmAchievementsRepository.selectAchievementsModelListByDates(startDate.atStartOfDay(), endDate.atTime(LocalTime.MAX));
33997 ranu 216 
        LOGGER.info("rbmTargetsList {}", rbmAchievements);
217 
        // Group achievements by RBM and Warehouse
34002 ranu 218 
        Map<String, RbmAchievementsModel> achievementMap = rbmAchievements.stream()
33997 ranu 219 
                .collect(Collectors.toMap(
220 
                        a -> a.getRbmAuthId() + "-" + a.getWarehouseId(),
221 
                        a -> a,
222 
                        (a1, a2) -> mergeAchievements(a1, a2) // Handle duplicates by merging
223 
                ));
224 
 
225 
        return targetsMap.keySet().stream()
226 
                .map(key -> {
227 
                    String[] parts = key.split("-");
228 
                    int rbmAuthId = Integer.parseInt(parts[0]);
229 
                    int warehouseId = Integer.parseInt(parts[1]);
230 
 
34002 ranu 231 
                    RbmTargetsModel target = targetsMap.get(key);
232 
                    RbmAchievementsModel achievement = achievementMap.getOrDefault(key, new RbmAchievementsModel());
33997 ranu 233 
 
234 
                    RbmTargetAndAchievementsModel model = new RbmTargetAndAchievementsModel();
235 
                    model.setAuthId(rbmAuthId);
236 
                    model.setRbmName(target.getRbmName());
237 
                    model.setWarehouseName(ProfitMandiConstants.WAREHOUSE_MAP.getOrDefault(warehouseId, "Unknown"));
238 
 
239 
                    // Set target values
34006 ranu 240 
                    model.setHidTarget((long) target.getHidTarget());
34098 ranu 241 
                    model.setRunningTarget((long) target.getRunningTarget());
34006 ranu 242 
                    model.setFastMovingTarget((long) target.getFastMovingTarget());
243 
                    model.setSlowMovingTarget((long) target.getSlowMovingTarget());
244 
                    model.setOtherMovingTarget((long) target.getOtherTarget());
33997 ranu 245 
 
246 
                    // Set achievement values
34006 ranu 247 
                    model.setAchievedHid((long) achievement.getAchievedHidTarget());
34098 ranu 248 
                    model.setAchievedRunning((long) achievement.getAchievedRunningTarget());
34006 ranu 249 
                    model.setAchievedFastMoving((long) achievement.getAchievedFastMovingTarget());
250 
                    model.setAchievedSlowMoving((long) achievement.getAchievedSlowMovingTarget());
251 
                    model.setAchievedOtherMoving((long) achievement.getAchievedOtherTarget());
33997 ranu 252 
 
253 
                    model.setTotalTarget(
34006 ranu 254 
                            (long) target.getHidTarget() +
34098 ranu 255 
                                    (long) target.getRunningTarget() +
34006 ranu 256 
                                    (long) target.getFastMovingTarget() +
257 
                                    (long) target.getSlowMovingTarget() +
258 
                                    (long) target.getOtherTarget()
33997 ranu 259 
                    );
260 
                    model.setTotalAchievemnt(
34006 ranu 261 
                            (long) achievement.getAchievedHidTarget() +
34098 ranu 262 
                                    (long) achievement.getAchievedRunningTarget() +
34006 ranu 263 
                                    (long) achievement.getAchievedFastMovingTarget() +
264 
                                    (long) achievement.getAchievedSlowMovingTarget() +
265 
                                    (long) achievement.getAchievedOtherTarget()
33997 ranu 266 
                    );
267 
 
268 
                    return model;
269 
                })
270 
                .collect(Collectors.toList());
271 
 
272 
    }
273 
 
34002 ranu 274 
    private RbmTargetsModel mergeTargets(RbmTargetsModel a1, RbmTargetsModel a2) {
33997 ranu 275 
 
276 
        // Merge logic for achievements (aggregate the target and achieved values)
34006 ranu 277 
        a1.setHidTarget((a1.getHidTarget()) +
278 
                (a2.getHidTarget()));
33997 ranu 279 
 
34006 ranu 280 
        a1.setFastMovingTarget((a1.getFastMovingTarget()) +
281 
                (a2.getFastMovingTarget()));
33997 ranu 282 
 
34006 ranu 283 
        a1.setSlowMovingTarget((a1.getSlowMovingTarget()) +
284 
                (a2.getSlowMovingTarget()));
33997 ranu 285 
 
34098 ranu 286 
        a1.setRunningTarget((a1.getRunningTarget()) +
287 
                (a2.getRunningTarget()));
33997 ranu 288 
 
34006 ranu 289 
        a1.setOtherTarget((a1.getOtherTarget()) +
290 
                (a2.getOtherTarget()));
33997 ranu 291 
        return a1;
292 
    }
293 
 
34002 ranu 294 
    private RbmAchievementsModel mergeAchievements(RbmAchievementsModel a1, RbmAchievementsModel a2) {
33997 ranu 295 
        // Merge logic for achievements (aggregate the target and achieved values)
34006 ranu 296 
        a1.setAchievedHidTarget((a1.getAchievedHidTarget()) +
297 
                (a2.getAchievedHidTarget()));
33997 ranu 298 
 
34098 ranu 299 
        a1.setAchievedRunningTarget((a1.getAchievedRunningTarget()) +
300 
                (a2.getAchievedRunningTarget()));
33997 ranu 301 
 
34006 ranu 302 
        a1.setAchievedFastMovingTarget((a1.getAchievedFastMovingTarget()) +
303 
                (a2.getAchievedFastMovingTarget()));
33997 ranu 304 
 
34006 ranu 305 
        a1.setAchievedSlowMovingTarget((a1.getAchievedSlowMovingTarget()) +
306 
                (a2.getAchievedSlowMovingTarget()));
33997 ranu 307 
 
34006 ranu 308 
        a1.setAchievedOtherTarget((a1.getAchievedOtherTarget()) +
309 
                (a2.getAchievedOtherTarget()));
33997 ranu 310 
 
311 
        return a1;
312 
    }
34002 ranu 313 
 
33997 ranu 314 
    @Override
33917 ranu 315 
    public List<RbmArrViewModel> getRbmTodayArr(LocalDate todayDate) throws Exception {
316 
 
317 
        LocalDate startDateOfMonthDay1 = LocalDate.now().withDayOfMonth(1);
318 
 
34289 ranu 319 
        List<WarehouseRbmTargetModel> warehouseRbmTargetModelList = this.getWarehouseWiseRbmMonthlyTarget();
320 
        List<WarehouseRbmTargetModel> warehouseRbmTargetModels = warehouseRbmTargetModelList.stream().filter(x -> x.getMonthlyTarget() > 0).collect(Collectors.toList());
321 
        LOGGER.info("warehouseRbmTargetModels {}", warehouseRbmTargetModels);
322 
        List<TodayAchievedMovementModel> todayAchievedMovementModels = getMovementWiseAchievementByDate(todayDate, todayDate.plusDays(1));
33917 ranu 323 
 
324 
        List<MTDAchievedTargetModel> mtdAchievedTargetModels = getDateWiseAchievedTargetOfRbm(startDateOfMonthDay1, todayDate);
325 
 
35044 ranu 326 
        int remainingWorkingDaysCount = (int) getRemainingDaysInMonth(todayDate);
33917 ranu 327 
 
33926 ranu 328 
        List<RbmTargets> todayRbmTargetsList = rbmTargetsRepository.selectTargetsByDates(todayDate.atStartOfDay(), todayDate.atTime(LocalTime.MAX));
33917 ranu 329 
 
33926 ranu 330 
        LOGGER.info("todayRbmTargetsList {}", todayRbmTargetsList);
33917 ranu 331 
 
332 
        List<RbmArrViewModel> rbmArrViewModels = new ArrayList<>();
333 
 
34028 ranu 334 
        if (!todayRbmTargetsList.isEmpty()) {
33917 ranu 335 
 
35454 amit 336 
            // OPTIMIZED: Pre-build maps for O(1) lookup instead of O(n) filter in each iteration
337 
            // Map key: "authId-warehouseId"
338 
            Map<String, Double> mtdAchievedMap = mtdAchievedTargetModels.stream()
339 
                    .collect(Collectors.groupingBy(
340 
                            x -> x.getAuthId() + "-" + x.getWarehouseId(),
341 
                            Collectors.summingDouble(MTDAchievedTargetModel::getAcheivedMonthlyTarget)
342 
                    ));
343 
 
344 
            Map<String, TodayAchievedMovementModel> todayAchievedMap = todayAchievedMovementModels.stream()
345 
                    .collect(Collectors.toMap(
346 
                            x -> x.getAuthId() + "-" + x.getWarehouseId(),
347 
                            x -> x,
348 
                            (a, b) -> a
349 
                    ));
350 
 
351 
            Map<String, RbmTargets> todayRbmTargetsMap = todayRbmTargetsList.stream()
352 
                    .collect(Collectors.toMap(
353 
                            x -> x.getRbmAuthId() + "-" + x.getWarehouseId(),
354 
                            x -> x,
355 
                            (a, b) -> a
356 
                    ));
357 
 
34028 ranu 358 
            for (WarehouseRbmTargetModel rbmTarget : warehouseRbmTargetModels) {
33917 ranu 359 
 
35454 amit 360 
                String lookupKey = rbmTarget.getAuthId() + "-" + rbmTarget.getWarehouseId();
361 
 
34028 ranu 362 
                float monthlyTarget = rbmTarget.getMonthlyTarget();
35454 amit 363 
                float achievedSoFar = mtdAchievedMap.getOrDefault(lookupKey, 0.0).floatValue();
33917 ranu 364 
 
34028 ranu 365 
                float remainingTarget = monthlyTarget - achievedSoFar;
33953 ranu 366 
 
34028 ranu 367 
                float todayTarget = (remainingWorkingDaysCount > 0 && remainingTarget > 0) ? remainingTarget / remainingWorkingDaysCount : 0;
368 
 
33926 ranu 369 
                String warehouseName = ProfitMandiConstants.WAREHOUSE_MAP.getOrDefault(rbmTarget.getWarehouseId(), "Unknown");
33917 ranu 370 
 
34288 ranu 371 
                LOGGER.info("rbmTarget ==== {}", rbmTarget);
34281 ranu 372 
 
35454 amit 373 
                TodayAchievedMovementModel todayAchievedMovementModel = todayAchievedMap.get(lookupKey);
34283 ranu 374 
 
35454 amit 375 
                RbmTargets todayRbmTargets = todayRbmTargetsMap.get(lookupKey);
34285 ranu 376 
 
35454 amit 377 
                if (todayRbmTargets != null) {
34034 ranu 378 
                    LOGGER.info("todayRbmTargets {}", todayRbmTargets);
379 
                    RbmArrViewModel viewModel = new RbmArrViewModel();
33917 ranu 380 
 
34034 ranu 381 
                    viewModel.setAuthId(rbmTarget.getAuthId());
382 
                    viewModel.setRbmName(rbmTarget.getRbmName());
383 
                    viewModel.setWarehouseName(warehouseName);
384 
                    viewModel.setTodayTarget(Math.round(todayTarget));
385 
                    viewModel.setMonthlyTarget(Math.round(monthlyTarget));
386 
                    viewModel.setMtdAchievedTarget(Math.round(achievedSoFar));
33926 ranu 387 
 
34034 ranu 388 
                    viewModel.setTodayHidTarget(Math.round((todayRbmTargets.getHidTarget())));
389 
                    viewModel.setTodayFastMovingTarget(Math.round(todayRbmTargets.getFastMovingTarget()));
34098 ranu 390 
                    viewModel.setTodaySlowMovingTarget(Math.round(todayRbmTargets.getSlowMovingtarget()));
391 
                    viewModel.setTodayRunningTarget(Math.round(todayRbmTargets.getRunningtarget()));
34034 ranu 392 
                    viewModel.setTodayOtherMovingTarget(Math.round(todayRbmTargets.getOtherTarget()));
33926 ranu 393 
 
34283 ranu 394 
                    if (todayAchievedMovementModel != null) {
395 
                        viewModel.setTodayAchievedHidTarget(Math.round(todayAchievedMovementModel.getHidBilled()));
396 
                        viewModel.setTodayAchievedFastMovingTarget(Math.round(todayAchievedMovementModel.getFastMovingBilled()));
397 
                        viewModel.setTodayAchievedSlowMovingTarget(Math.round(todayAchievedMovementModel.getSlowMovinBilled()));
398 
                        viewModel.setTodayAchievedRunningTarget(Math.round(todayAchievedMovementModel.getRunningBilled()));
399 
                        viewModel.setTodayAchievedOtherMovingTarget(Math.round(todayAchievedMovementModel.getOtherBilled()));
400 
                        viewModel.setTotalAchievedTarget(Math.round(todayAchievedMovementModel.getHidBilled() + todayAchievedMovementModel.getFastMovingBilled() + todayAchievedMovementModel.getSlowMovinBilled() + todayAchievedMovementModel.getRunningBilled() + todayAchievedMovementModel.getOtherBilled()));
401 
                    } else {
402 
                        viewModel.setTodayAchievedHidTarget(0);
403 
                        viewModel.setTodayAchievedFastMovingTarget(0);
404 
                        viewModel.setTodayAchievedSlowMovingTarget(0);
405 
                        viewModel.setTodayAchievedRunningTarget(0);
406 
                        viewModel.setTodayAchievedOtherMovingTarget(0);
407 
                        viewModel.setTotalAchievedTarget(0);
408 
                    }
34034 ranu 409 
                    rbmArrViewModels.add(viewModel);
410 
                } else {
411 
                    LOGGER.info("No matching RbmTargets found for AuthId: {} and rbmname {} and WarehouseId: {}", rbmTarget.getAuthId(), rbmTarget.getRbmName(), rbmTarget.getWarehouseId());
412 
                }
33917 ranu 413 
 
34034 ranu 414 
 
415 
 
34028 ranu 416 
            }
33917 ranu 417 
        }
418 
 
419 
        LOGGER.info("rbmArrViewModels {}", rbmArrViewModels);
420 
        return rbmArrViewModels;
421 
    }
422 
 
33926 ranu 423 
    @Override
424 
    public void setMovementWiseRbmTargets() {
425 
        LocalDate todayDate = LocalDate.now();
33917 ranu 426 
 
33926 ranu 427 
        LocalDate startDateOfMonthDay1 = LocalDate.now().withDayOfMonth(1);
428 
 
429 
        List<WarehouseRbmTargetModel> warehouseRbmTargetModels = this.getWarehouseWiseRbmMonthlyTarget();
430 
 
431 
        List<MTDAchievedTargetModel> mtdAchievedTargetModels = getDateWiseAchievedTargetOfRbm(startDateOfMonthDay1, todayDate);
432 
 
433 
 
35044 ranu 434 
        int remainingWorkingDaysCount = (int) getRemainingDaysInMonth(todayDate);
33926 ranu 435 
 
436 
        List<WarehouseMobileStockByMovementModel> warehouseMobileStockByMovementModels = getWarehouseMobileStockByMovement();
437 
 
35454 amit 438 
        // OPTIMIZED: Pre-build maps for O(1) lookup instead of O(n) filter in each iteration
439 
        Map<String, Double> mtdAchievedMap = mtdAchievedTargetModels.stream()
440 
                .collect(Collectors.groupingBy(
441 
                        x -> x.getAuthId() + "-" + x.getWarehouseId(),
442 
                        Collectors.summingDouble(MTDAchievedTargetModel::getAcheivedMonthlyTarget)
443 
                ));
444 
 
445 
        Map<Integer, WarehouseMobileStockByMovementModel> warehouseStockMap = warehouseMobileStockByMovementModels.stream()
446 
                .collect(Collectors.toMap(
447 
                        WarehouseMobileStockByMovementModel::getWarehouseId,
448 
                        x -> x,
449 
                        (a, b) -> a
450 
                ));
451 
 
33926 ranu 452 
        for (WarehouseRbmTargetModel rbmTarget : warehouseRbmTargetModels) {
453 
 
35454 amit 454 
            String lookupKey = rbmTarget.getAuthId() + "-" + rbmTarget.getWarehouseId();
455 
 
33926 ranu 456 
            float monthlyTarget = rbmTarget.getMonthlyTarget();
35454 amit 457 
            float achievedSoFar = mtdAchievedMap.getOrDefault(lookupKey, 0.0).floatValue();
33926 ranu 458 
 
459 
 
460 
            float remainingTarget = monthlyTarget - achievedSoFar;
33953 ranu 461 
            LOGGER.info("remainingTarget {}", remainingTarget);
33926 ranu 462 
 
33953 ranu 463 
            float todayTarget = (remainingWorkingDaysCount > 0 && remainingTarget > 0) ? remainingTarget / remainingWorkingDaysCount : 0;
464 
            LOGGER.info("todayTarget {}", todayTarget);
465 
 
33926 ranu 466 
            // Get the warehouse stock data
35454 amit 467 
            WarehouseMobileStockByMovementModel warehouseMobileStockByMovementModel = warehouseStockMap.get(rbmTarget.getWarehouseId());
33926 ranu 468 
 
469 
 
470 
            if (warehouseMobileStockByMovementModel != null) {
471 
 
472 
                // Total stock value for this warehouse
473 
                float totalStockValue = warehouseMobileStockByMovementModel.getTotalAvailabilityPrice();
474 
 
475 
                // Calculate target allocation based on stock value proportion
476 
                float hidTarget = (warehouseMobileStockByMovementModel.getTotalHidCatalogPrice() / totalStockValue) * todayTarget;
477 
                float fastMovingTarget = (warehouseMobileStockByMovementModel.getTotalFastMovingCatalogPrice() / totalStockValue) * todayTarget;
478 
                float slowMovingTarget = (warehouseMobileStockByMovementModel.getTotalSlowMovingCatalogPrice() / totalStockValue) * todayTarget;
34098 ranu 479 
                float runningTarget = (warehouseMobileStockByMovementModel.getTotalRunningCatalogPrice() / totalStockValue) * todayTarget;
33926 ranu 480 
                float otherTarget = (warehouseMobileStockByMovementModel.getTotalOtherCategoryCatalogPrice() / totalStockValue) * todayTarget;
481 
 
482 
                RbmTargets rbmTargets = new RbmTargets();
483 
                rbmTargets.setWarehouseId(rbmTarget.getWarehouseId());
484 
                rbmTargets.setRbmAuthId(rbmTarget.getAuthId());
485 
                rbmTargets.setRbmName(rbmTarget.getRbmName());
34098 ranu 486 
                rbmTargets.setRunningtarget(runningTarget);
33926 ranu 487 
                rbmTargets.setHidTarget(hidTarget);
488 
                rbmTargets.setFastMovingTarget(fastMovingTarget);
34098 ranu 489 
                rbmTargets.setSlowMovingtarget(slowMovingTarget);
33926 ranu 490 
                rbmTargets.setOtherTarget(otherTarget);
491 
                rbmTargets.setCreateTimestamp(LocalDateTime.now());
492 
 
493 
                rbmTargetsRepository.persist(rbmTargets);
494 
 
495 
            }
496 
        }
497 
 
498 
    }
499 
 
500 
 
37497 ranu 501 
    /**
502 
     * Daily writer for Sales-hierarchy targets. Rolls up today's persisted
503 
     * inventory.rbm_targets snapshots (produced by {@link #setMovementWiseRbmTargets})
504 
     * to every active Sales person whose downline includes those RBMs, and
505 
     * inserts one row per (sales, warehouse) into inventory.sales_targets.
506 
     *
507 
     * <p>Chain order matters: run this AFTER pushRbmTargets so today's RBM
508 
     * snapshot rows exist when we roll up.
509 
     */
33985 ranu 510 
    @Override
37497 ranu 511 
    public void setSalesTargets() {
512 
        setSalesTargetsForDate(LocalDate.now());
513 
    }
514 
 
515 
    @Override
516 
    public void setSalesTargetsForDate(LocalDate date) {
37505 ranu 517 
        // Sales target for a person = (own_monthly_target − MTD_own_achievement) /
518 
        // remainingDays, then split by warehouse stock mix — exactly like RBM, but
519 
        // scoped to the Sales person's OWN assigned partners (no RBM roll-up).
520 
        LocalDate monthStart = date.withDayOfMonth(1);
521 
        int remainingWorkingDays = (int) getRemainingDaysInMonth(date);
37497 ranu 522 
 
37505 ranu 523 
        // Warehouse-level stock mix (same source used by setMovementWiseRbmTargets).
524 
        Map<Integer, WarehouseMobileStockByMovementModel> whStockByWh = new HashMap<>();
525 
        for (WarehouseMobileStockByMovementModel s : getWarehouseMobileStockByMovement()) {
526 
            whStockByWh.put(s.getWarehouseId(), s);
37497 ranu 527 
        }
528 
 
529 
        String[][] roles = {
530 
                {"ASM", "4", "L1"}, {"CM", "4", "L2"}, {"DM", "4", "L3"},
531 
                {"BM", "4", "L4"}, {"RSM", "4", "L5"}, {"NSM", "4", "L6"}
532 
        };
533 
 
534 
        LocalDate today = LocalDate.now();
535 
        LocalDateTime stamp = date.equals(today) ? LocalDateTime.now() : date.atTime(java.time.LocalTime.NOON);
37505 ranu 536 
        int written = 0, zeroTargetSkipped = 0;
537 
 
37519 ranu 538 
        // Cache monthly targets + MTD ACH per level so the RSM/NSM roll-up
539 
        // pass can reuse them without repeating queries.
540 
        Map<String, List<SalesHierarchyMonthlyTargetModel>> mtByLevel = new HashMap<>();
541 
        Map<String, Map<String, Double>> mtdByLevelKey = new HashMap<>();
542 
 
37497 ranu 543 
        for (String[] r : roles) {
544 
            String role = r[0];
545 
            int catId = Integer.parseInt(r[1]);
37505 ranu 546 
            String levelStr = r[2];
37497 ranu 547 
 
37505 ranu 548 
            List<SalesHierarchyMonthlyTargetModel> monthlyTgts = getSalesMonthlyTargetByRole(catId, levelStr, date);
37519 ranu 549 
            mtByLevel.put(role, monthlyTgts);
37497 ranu 550 
 
37505 ranu 551 
            Map<String, Double> mtdByKey = new HashMap<>();
552 
            for (SalesHierarchyAchievedByMovementModel a :
553 
                    getSalesAchievementByRole(catId, levelStr, monthStart, date.plusDays(1))) {
554 
                double totBilled =
555 
                        a.getHidBilled()
556 
                                + a.getFastMovingBilled()
557 
                                + (a.getRunningBilled() != null ? a.getRunningBilled() : 0)
558 
                                + (a.getSlowMovingBilled() != null ? a.getSlowMovingBilled() : 0)
559 
                                + (a.getOtherBilled() != null ? a.getOtherBilled() : 0);
560 
                mtdByKey.merge(a.getAuthId() + "-" + a.getWarehouseId(), totBilled, Double::sum);
561 
            }
37519 ranu 562 
            mtdByLevelKey.put(role, mtdByKey);
37505 ranu 563 
 
37519 ranu 564 
            // Direct-role writer — one row per (authId × warehouse) at this role.
565 
            if (monthlyTgts.isEmpty()) continue;
37505 ranu 566 
            for (SalesHierarchyMonthlyTargetModel mt : monthlyTgts) {
567 
                if (mt.getMonthlyTarget() <= 0) {
568 
                    zeroTargetSkipped++;
569 
                    continue;
37497 ranu 570 
                }
37505 ranu 571 
                double achieved = mtdByKey.getOrDefault(mt.getAuthId() + "-" + mt.getWarehouseId(), 0.0);
572 
                double remaining = mt.getMonthlyTarget() - achieved;
573 
                double todayTarget = (remainingWorkingDays > 0 && remaining > 0)
574 
                        ? remaining / remainingWorkingDays : 0;
37497 ranu 575 
 
37505 ranu 576 
                WarehouseMobileStockByMovementModel stock = whStockByWh.get(mt.getWarehouseId());
577 
                if (stock == null || stock.getTotalAvailabilityPrice() <= 0) continue;
578 
                float total = stock.getTotalAvailabilityPrice();
579 
 
580 
                SalesTargets st = new SalesTargets();
581 
                st.setSalesAuthId(mt.getAuthId());
582 
                st.setSalesName(mt.getName());
583 
                st.setRole(role);
584 
                st.setWarehouseId(mt.getWarehouseId());
585 
                st.setHidTarget((float) ((stock.getTotalHidCatalogPrice() / total) * todayTarget));
586 
                st.setFastMovingTarget((float) ((stock.getTotalFastMovingCatalogPrice() / total) * todayTarget));
587 
                st.setSlowMovingTarget((float) ((stock.getTotalSlowMovingCatalogPrice() / total) * todayTarget));
588 
                st.setRunningTarget((float) ((stock.getTotalRunningCatalogPrice() / total) * todayTarget));
589 
                st.setOtherTarget((float) ((stock.getTotalOtherCategoryCatalogPrice() / total) * todayTarget));
590 
                st.setCreateTimestamp(stamp);
591 
                salesTargetsRepository.persist(st);
592 
                written++;
37497 ranu 593 
            }
594 
        }
37519 ranu 595 
 
596 
        // Roll-up pass for RSM (L5) and NSM (L6) — uses distinct-partner dedup
597 
        // queries so partners assigned to multiple positions in the downline
598 
        // aren't counted more than once.
599 
        String[] rollUpRoles = {"RSM", "NSM"};
600 
        for (String rollUpRole : rollUpRoles) {
601 
            String levelStr = "RSM".equals(rollUpRole) ? "L5" : "L6";
602 
            for (AuthUser top : csService.getAuthUserByCategoryId(4, EscalationType.valueOf(levelStr))) {
603 
                if (!top.isActive()) continue;
604 
                Set<Integer> tree = new HashSet<>();
605 
                tree.add(top.getId());
606 
                List<Integer> reportees = authService.getAllReportees(top.getId());
607 
                if (reportees != null) tree.addAll(reportees);
608 
                if (tree.isEmpty()) continue;
609 
 
610 
                // Distinct-partner monthly TGT per warehouse for the downline.
611 
                Map<Integer, Double> monthlyByWh = new LinkedHashMap<>();
612 
                for (com.spice.profitmandi.dao.model.SalesDownlineMonthlyTargetModel d :
613 
                        getSalesDownlineMonthlyTarget(tree, date)) {
614 
                    monthlyByWh.put(d.getWarehouseId(), (double) d.getMonthlyTarget());
615 
                }
616 
                // Distinct-partner MTD ACH per warehouse for the downline.
617 
                Map<Integer, Double> mtdByWh = new HashMap<>();
618 
                for (com.spice.profitmandi.dao.model.SalesDownlineAchievedByMovementModel d :
619 
                        getSalesDownlineAchievement(tree, monthStart, date.plusDays(1))) {
620 
                    double tot = d.getHidBilled() + d.getFastMovingBilled()
621 
                            + (d.getRunningBilled() != null ? d.getRunningBilled() : 0)
622 
                            + (d.getSlowMovingBilled() != null ? d.getSlowMovingBilled() : 0)
623 
                            + (d.getOtherBilled() != null ? d.getOtherBilled() : 0);
624 
                    mtdByWh.put(d.getWarehouseId(), tot);
625 
                }
626 
 
627 
                for (Map.Entry<Integer, Double> e : monthlyByWh.entrySet()) {
628 
                    int whId = e.getKey();
629 
                    double monthlyTgt = e.getValue();
630 
                    if (monthlyTgt <= 0) {
631 
                        zeroTargetSkipped++;
632 
                        continue;
633 
                    }
634 
                    double achieved = mtdByWh.getOrDefault(whId, 0.0);
635 
                    double remaining = monthlyTgt - achieved;
636 
                    double todayTarget = (remainingWorkingDays > 0 && remaining > 0)
637 
                            ? remaining / remainingWorkingDays : 0;
638 
 
639 
                    WarehouseMobileStockByMovementModel stock = whStockByWh.get(whId);
640 
                    if (stock == null || stock.getTotalAvailabilityPrice() <= 0) continue;
641 
                    float total = stock.getTotalAvailabilityPrice();
642 
 
643 
                    SalesTargets st = new SalesTargets();
644 
                    st.setSalesAuthId(top.getId());
645 
                    st.setSalesName(top.getFullName());
646 
                    st.setRole(rollUpRole);
647 
                    st.setWarehouseId(whId);
648 
                    st.setHidTarget((float) ((stock.getTotalHidCatalogPrice() / total) * todayTarget));
649 
                    st.setFastMovingTarget((float) ((stock.getTotalFastMovingCatalogPrice() / total) * todayTarget));
650 
                    st.setSlowMovingTarget((float) ((stock.getTotalSlowMovingCatalogPrice() / total) * todayTarget));
651 
                    st.setRunningTarget((float) ((stock.getTotalRunningCatalogPrice() / total) * todayTarget));
652 
                    st.setOtherTarget((float) ((stock.getTotalOtherCategoryCatalogPrice() / total) * todayTarget));
653 
                    st.setCreateTimestamp(stamp);
654 
                    salesTargetsRepository.persist(st);
655 
                    written++;
656 
                }
657 
            }
658 
        }
37505 ranu 659 
        LOGGER.info("setSalesTargetsForDate: wrote {} sales_targets rows for {} ({} rows skipped due to zero monthly target)",
660 
                written, date, zeroTargetSkipped);
37497 ranu 661 
    }
662 
 
663 
    @Override
664 
    public List<SalesTargets> getSalesTargetsByDates(LocalDate startDate, LocalDate endDate) {
665 
        return salesTargetsRepository.selectTargetsByDates(startDate.atStartOfDay(), endDate.atStartOfDay());
666 
    }
667 
 
668 
    @Override
37505 ranu 669 
    public List<SalesHierarchyAchievedByMovementModel> getSalesAchievementByRole(
670 
            int categoryId, String escalationType, LocalDate startDate, LocalDate endDate) {
671 
        Session session = sessionFactory.getCurrentSession();
672 
        TypedQuery<SalesHierarchyAchievedByMovementModel> q = session.createNamedQuery(
673 
                "RbmDrr.SalesHierarchyAchievedByMovement", SalesHierarchyAchievedByMovementModel.class);
674 
        q.setParameter("categoryId", categoryId);
675 
        q.setParameter("escalationType", escalationType);
676 
        q.setParameter("startDate", startDate);
677 
        q.setParameter("endDate", endDate);
678 
        return q.getResultList();
679 
    }
680 
 
681 
    @Override
682 
    public List<SalesHierarchyMonthlyTargetModel> getSalesMonthlyTargetByRole(
683 
            int categoryId, String escalationType, LocalDate refDate) {
684 
        Session session = sessionFactory.getCurrentSession();
685 
        TypedQuery<SalesHierarchyMonthlyTargetModel> q = session.createNamedQuery(
686 
                "RbmDrr.SalesHierarchyMonthlyTarget", SalesHierarchyMonthlyTargetModel.class);
687 
        q.setParameter("categoryId", categoryId);
688 
        q.setParameter("escalationType", escalationType);
689 
        q.setParameter("refDate", refDate);
690 
        return q.getResultList();
691 
    }
692 
 
693 
    @Override
37519 ranu 694 
    public List<com.spice.profitmandi.dao.model.SalesDownlineAchievedByMovementModel>
695 
    getSalesDownlineAchievement(Collection<Integer> authIds, LocalDate startDate, LocalDate endDate) {
696 
        if (authIds == null || authIds.isEmpty()) return new ArrayList<>();
697 
        Session session = sessionFactory.getCurrentSession();
698 
        TypedQuery<com.spice.profitmandi.dao.model.SalesDownlineAchievedByMovementModel> q =
699 
                session.createNamedQuery("RbmDrr.SalesDownlineAchievedByMovement",
700 
                        com.spice.profitmandi.dao.model.SalesDownlineAchievedByMovementModel.class);
701 
        q.setParameter("authIds", authIds);
702 
        q.setParameter("startDate", startDate);
703 
        q.setParameter("endDate", endDate);
704 
        return q.getResultList();
705 
    }
706 
 
707 
    @Override
708 
    public List<com.spice.profitmandi.dao.model.SalesDownlineMonthlyTargetModel>
709 
    getSalesDownlineMonthlyTarget(Collection<Integer> authIds, LocalDate refDate) {
710 
        if (authIds == null || authIds.isEmpty()) return new ArrayList<>();
711 
        Session session = sessionFactory.getCurrentSession();
712 
        TypedQuery<com.spice.profitmandi.dao.model.SalesDownlineMonthlyTargetModel> q =
713 
                session.createNamedQuery("RbmDrr.SalesDownlineMonthlyTarget",
714 
                        com.spice.profitmandi.dao.model.SalesDownlineMonthlyTargetModel.class);
715 
        q.setParameter("authIds", authIds);
716 
        q.setParameter("refDate", refDate);
717 
        return q.getResultList();
718 
    }
719 
 
720 
    @Override
37497 ranu 721 
    public void setSalesAchievements() {
722 
        setSalesAchievementsForDate(LocalDate.now());
723 
    }
724 
 
725 
    @Override
726 
    public void setSalesAchievementsForDate(LocalDate date) {
37505 ranu 727 
        // Sales ACH per (auth × warehouse) — direct query scoped to each Sales
728 
        // person's OWN partners (no RBM roll-up). Same source-of-truth orders SQL.
37497 ranu 729 
        String[][] roles = {
730 
                {"ASM", "4", "L1"}, {"CM", "4", "L2"}, {"DM", "4", "L3"},
731 
                {"BM", "4", "L4"}, {"RSM", "4", "L5"}, {"NSM", "4", "L6"}
732 
        };
733 
 
734 
        LocalDate today = LocalDate.now();
735 
        LocalDateTime stamp = date.equals(today) ? LocalDateTime.now() : date.atTime(java.time.LocalTime.NOON);
37505 ranu 736 
        int written = 0;
37497 ranu 737 
 
37519 ranu 738 
        // Cache per-level results so the RSM/NSM roll-up pass reuses them.
739 
        Map<String, List<SalesHierarchyAchievedByMovementModel>> byLevel = new HashMap<>();
37497 ranu 740 
        for (String[] r : roles) {
741 
            String role = r[0];
742 
            int catId = Integer.parseInt(r[1]);
37505 ranu 743 
            String levelStr = r[2];
37497 ranu 744 
 
37519 ranu 745 
            List<SalesHierarchyAchievedByMovementModel> list =
746 
                    getSalesAchievementByRole(catId, levelStr, date, date.plusDays(1));
747 
            byLevel.put(role, list);
748 
            for (SalesHierarchyAchievedByMovementModel a : list) {
37505 ranu 749 
                SalesAchievements sa = new SalesAchievements();
750 
                sa.setSalesAuthId(a.getAuthId());
751 
                sa.setSalesName(a.getName());
752 
                sa.setRole(role);
753 
                sa.setWarehouseId(a.getWarehouseId());
754 
                sa.setAchievedHidTarget(a.getHidBilled());
755 
                sa.setAchievedFastMovingTarget(a.getFastMovingBilled());
756 
                sa.setAchievedRunningTarget(a.getRunningBilled() != null ? a.getRunningBilled() : 0f);
757 
                sa.setAchievedSlowMovingTarget(a.getSlowMovingBilled() != null ? a.getSlowMovingBilled() : 0f);
758 
                sa.setAchievedOtherTarget(a.getOtherBilled() != null ? a.getOtherBilled() : 0f);
759 
                sa.setCreateTimestamp(stamp);
760 
                salesAchievementsRepository.persist(sa);
761 
                written++;
37497 ranu 762 
            }
763 
        }
37519 ranu 764 
 
765 
        // Roll-up pass for RSM (L5) and NSM (L6) — uses distinct-partner dedup
766 
        // query so partners assigned to multiple positions in the downline
767 
        // don't have their orders counted more than once.
768 
        String[] rollUpRoles = {"RSM", "NSM"};
769 
        for (String rollUpRole : rollUpRoles) {
770 
            String levelStr = "RSM".equals(rollUpRole) ? "L5" : "L6";
771 
            for (AuthUser top : csService.getAuthUserByCategoryId(4, EscalationType.valueOf(levelStr))) {
772 
                if (!top.isActive()) continue;
773 
                Set<Integer> tree = new HashSet<>();
774 
                tree.add(top.getId());
775 
                List<Integer> reportees = authService.getAllReportees(top.getId());
776 
                if (reportees != null) tree.addAll(reportees);
777 
                if (tree.isEmpty()) continue;
778 
 
779 
                for (com.spice.profitmandi.dao.model.SalesDownlineAchievedByMovementModel d :
780 
                        getSalesDownlineAchievement(tree, date, date.plusDays(1))) {
781 
                    SalesAchievements sa = new SalesAchievements();
782 
                    sa.setSalesAuthId(top.getId());
783 
                    sa.setSalesName(top.getFullName());
784 
                    sa.setRole(rollUpRole);
785 
                    sa.setWarehouseId(d.getWarehouseId());
786 
                    sa.setAchievedHidTarget(d.getHidBilled());
787 
                    sa.setAchievedFastMovingTarget(d.getFastMovingBilled());
788 
                    sa.setAchievedRunningTarget(d.getRunningBilled() != null ? d.getRunningBilled() : 0f);
789 
                    sa.setAchievedSlowMovingTarget(d.getSlowMovingBilled() != null ? d.getSlowMovingBilled() : 0f);
790 
                    sa.setAchievedOtherTarget(d.getOtherBilled() != null ? d.getOtherBilled() : 0f);
791 
                    sa.setCreateTimestamp(stamp);
792 
                    salesAchievementsRepository.persist(sa);
793 
                    written++;
794 
                }
795 
            }
796 
        }
37497 ranu 797 
        LOGGER.info("setSalesAchievementsForDate: wrote {} sales_achievements rows for {}", written, date);
798 
    }
799 
 
800 
    @Override
33985 ranu 801 
    public void setMovementWiseRbmAchievement() {
802 
        LocalDate todayDate = LocalDate.now();
803 
 
34055 ranu 804 
        List<TodayAchievedMovementModel> todayAchievedMovementModels = getMovementWiseAchievementByDate(todayDate, todayDate.plusDays(1));
33985 ranu 805 
 
806 
 
807 
        for (TodayAchievedMovementModel achievement : todayAchievedMovementModels) {
808 
 
809 
            RbmAchievements rbmAchievements = new RbmAchievements();
810 
 
811 
            rbmAchievements.setRbmAuthId(achievement.getAuthId());
812 
            rbmAchievements.setRbmName(achievement.getRbmName());
813 
            rbmAchievements.setWarehouseId(achievement.getWarehouseId());
814 
            rbmAchievements.setAchievedHidTarget(achievement.getHidBilled());
815 
            rbmAchievements.setAchievedFastMovingTarget(achievement.getFastMovingBilled());
34098 ranu 816 
            rbmAchievements.setAchievedSlowMovingTarget(achievement.getSlowMovinBilled());
817 
            rbmAchievements.setAchievedRunningTarget(achievement.getRunningBilled());
34012 ranu 818 
            rbmAchievements.setAchievedOtherTarget(achievement.getOtherBilled());
33985 ranu 819 
            rbmAchievements.setCreateTimestamp(LocalDateTime.now());
820 
 
821 
            rbmAchievementsRepository.persist(rbmAchievements);
822 
 
823 
        }
824 
 
825 
    }
826 
 
34055 ranu 827 
    @Override
828 
    public List<Sold15daysOldAgingModel> getAgingSale(LocalDate startDate, LocalDate endDate) {
829 
        Session session = sessionFactory.getCurrentSession();
830 
        final TypedQuery<Sold15daysOldAgingModel> typedQuerySimilar = session.createNamedQuery("Aging.SoldAgingModel", Sold15daysOldAgingModel.class);
34056 ranu 831 
        typedQuerySimilar.setParameter("startDate", startDate);
832 
        typedQuerySimilar.setParameter("endDate", endDate);
34055 ranu 833 
        return typedQuerySimilar.getResultList();
33985 ranu 834 
 
34055 ranu 835 
    }
836 
 
34103 ranu 837 
    @Override
838 
    public List<RbmBilledFofoIdsModel> getDateWiseBilledFofoIdByRbm(LocalDate startDate, LocalDate endDate) {
839 
        Session session = sessionFactory.getCurrentSession();
840 
        final TypedQuery<RbmBilledFofoIdsModel> typedQuerySimilar = session.createNamedQuery("RBM.RbmBilledFofoId", RbmBilledFofoIdsModel.class);
841 
        typedQuerySimilar.setParameter("startDate", startDate);
842 
        typedQuerySimilar.setParameter("endDate", endDate);
843 
        return typedQuerySimilar.getResultList();
35453 amit 844 
    }
34103 ranu 845 
 
35453 amit 846 
    @Override
36296 amit 847 
    @Cacheable(value = "rbmWeeklyBilling",
36329 amit 848 
            cacheManager = "fiveMintimeoutCacheManager",
849 
            sync = true)
35453 amit 850 
    public List<RbmWeeklyBillingModel> getWeeklyBillingDataForMonth(LocalDate monthStart, LocalDate monthEnd) {
851 
        Session session = sessionFactory.getCurrentSession();
852 
        final TypedQuery<RbmWeeklyBillingModel> typedQuery = session.createNamedQuery("RBM.WeeklyBilling", RbmWeeklyBillingModel.class);
853 
        typedQuery.setParameter("startDate", monthStart);
854 
        typedQuery.setParameter("endDate", monthEnd);
855 
        return typedQuery.getResultList();
34103 ranu 856 
    }
857 
 
34055 ranu 858 
    public List<Our15DaysOldAgingStock> our15DaysAgingStock() {
859 
        Session session = sessionFactory.getCurrentSession();
860 
        final TypedQuery<Our15DaysOldAgingStock> typedQuerySimilar = session.createNamedQuery("Aging.15DaysOurStock", Our15DaysOldAgingStock.class);
861 
        return typedQuerySimilar.getResultList();
36334 ranu 862 
    }
34055 ranu 863 
 
36334 ranu 864 
    @Override
865 
    public List<WarehouseAgingStockModel> getWarehouseWiseAgingStock() {
866 
        Session session = sessionFactory.getCurrentSession();
867 
        final TypedQuery<WarehouseAgingStockModel> typedQuery = session.createNamedQuery("Aging.15DaysWarehouseWiseStock", WarehouseAgingStockModel.class);
868 
        return typedQuery.getResultList();
34055 ranu 869 
    }
870 
 
34397 ranu 871 
    @Autowired
872 
    OrderRepository orderRepository;
34055 ranu 873 
 
34397 ranu 874 
    @Override
34641 ranu 875 
    public double calculateFofoIdTodayTarget(int fofoId, double secondryMtd,LocalDate date) {
34397 ranu 876 
 
877 
        MonthlyTarget monthlyTarget = monthlyTargetRepository.selectByDateAndFofoId(YearMonth.now(), fofoId);
34404 ranu 878 
        if (monthlyTarget == null) {
879 
            // Log or handle as needed
880 
            return 0; // or -1 or some fallback
881 
        }
34397 ranu 882 
 
883 
        double remainingTarget = monthlyTarget.getPurchaseTarget() - secondryMtd;
34880 ranu 884 
//        double remainingWorkingDays = getWorkingDaysCount(date);
885 
        double remainingWorkingDays = (double) getRemainingDaysInMonth(date);
34397 ranu 886 
 
887 
 
34716 ranu 888 
 
34397 ranu 889 
        if (remainingWorkingDays == 0) return remainingTarget; // Last day
34716 ranu 890 
        LOGGER.info("remainingWorkingDays {}", remainingWorkingDays);
891 
        LOGGER.info("remainingTarget {}", remainingTarget);
34397 ranu 892 
 
893 
        return (int) Math.ceil(remainingTarget / remainingWorkingDays);
894 
    }
895 
 
34880 ranu 896 
    @Override
897 
    public long getRemainingDaysInMonth(LocalDate date) {
898 
        LocalDate lastDayOfMonth = YearMonth.from(date).atEndOfMonth();
34397 ranu 899 
 
34880 ranu 900 
        long totalDays = ChronoUnit.DAYS.between(date, lastDayOfMonth) + 1;
34397 ranu 901 
 
34880 ranu 902 
        // Count Sundays manually
903 
        long sundayCount = 0;
904 
        LocalDate current = date;
905 
        while (!current.isAfter(lastDayOfMonth)) {
906 
            if (current.getDayOfWeek() == DayOfWeek.SUNDAY) {
907 
                sundayCount++;
908 
            }
909 
            current = current.plusDays(1);
910 
        }
911 
 
912 
        // Public holidays in the range
913 
        long publicHolidays = publicHolidaysRepository
914 
                .selectAllBetweenDates(date, lastDayOfMonth)
915 
                .size();
916 
 
917 
        long remainingDays = totalDays - sundayCount - publicHolidays;
918 
 
919 
        LOGGER.info("remainingDays {}", remainingDays);
920 
        LOGGER.info("totalDays {}", totalDays);
921 
        LOGGER.info("sundays {}", sundayCount);
922 
        LOGGER.info("publicHolidays {}", publicHolidays);
923 
 
924 
        return remainingDays;
925 
    }
926 
 
35631 ranu 927 
    @Autowired
928 
    PositionRepository positionRepository;
34880 ranu 929 
 
35631 ranu 930 
    @Autowired
931 
    CsService csService;
34880 ranu 932 
 
35631 ranu 933 
    @Autowired
934 
    FofoStoreRepository fofoStoreRepository;
935 
 
936 
    @Autowired
937 
    AuthRepository authRepository;
938 
 
939 
    @Autowired
940 
    LoanRepository loanRepository;
941 
 
942 
    @Autowired
943 
    PartnerCollectionService partnerCollectionService;
944 
 
945 
    @Autowired
946 
    PartnerCollectionRemarkRepository partnerCollectionRemarkRepository;
947 
 
948 
    @Autowired
949 
    RbmCallSequenceLogRepository rbmCallSequenceLogRepository;
950 
 
951 
    @Autowired
952 
    com.spice.profitmandi.dao.repository.cs.TicketRepository ticketRepository;
953 
 
35702 ranu 954 
    @Autowired
955 
    com.spice.profitmandi.dao.repository.cs.AgentCallLogRepository agentCallLogRepository;
956 
 
35759 ranu 957 
    @Autowired
958 
    RetailerContactRepository retailerContactRepository;
959 
 
960 
    @Autowired
961 
    AddressRepository addressRepository;
962 
 
36225 ranu 963 
    @Autowired
964 
    com.spice.profitmandi.dao.repository.cs.PartnerPositionRepository partnerPositionRepository;
965 
 
35631 ranu 966 
    @Override
967 
    public List<RbmCallTargetModel> getRbmCallTargetModels() throws Exception {
36234 ranu 968 
        return getRbmCallTargetModels(LocalDate.now());
969 
    }
970 
 
37010 ranu 971 
    // Matches "No Answer", "NO_ANSWER", "no-answer", "noanswer", etc.
972 
    private static boolean isNoAnswerStatus(String status) {
973 
        if (status == null) return false;
974 
        String s = status.trim().toLowerCase().replace('_', ' ').replace('-', ' ').replaceAll("\\s+", " ");
975 
        return s.equals("no answer") || s.equals("noanswer");
976 
    }
977 
 
978 
    @Override
979 
    public List<OutOfSequenceDetailModel> getOutOfSequenceDetails(int authId) {
980 
 
981 
        LocalDate today = LocalDate.now();
982 
        LocalDateTime start = today.atStartOfDay();
983 
        LocalDateTime end = today.plusDays(1).atStartOfDay();
984 
 
985 
        List<RbmCallSequenceLog> logs =
986 
                rbmCallSequenceLogRepository.selectByAuthIdAndDateRange(authId, start, end);
987 
 
988 
        Map<Integer, RbmCallSequenceLog> uniqueOosLogsByFofoId = new LinkedHashMap<>();
989 
 
990 
        for (RbmCallSequenceLog log : logs) {
991 
            if (log.isOutOfSequence()) {
992 
                // Keep only the first occurrence per fofoId (latest entry since ordered by id DESC)
993 
                uniqueOosLogsByFofoId.putIfAbsent(log.getFofoId(), log);
994 
            }
995 
        }
996 
 
997 
        if (uniqueOosLogsByFofoId.isEmpty()) {
998 
            return Collections.emptyList();
999 
        }
1000 
 
1001 
        Set<Integer> fofoIds = uniqueOosLogsByFofoId.keySet();
1002 
        Map<Integer, CustomRetailer> retailerMap = Collections.emptyMap();
1003 
        try {
1004 
            retailerMap = retailerService.getFofoRetailers(new ArrayList<>(fofoIds));
1005 
        } catch (ProfitMandiBusinessException e) {
1006 
            LOGGER.error("Error fetching fofo stores", e);
1007 
        }
1008 
 
1009 
        DateTimeFormatter timeFormatter = DateTimeFormatter.ofPattern("hh:mm a");
1010 
        List<OutOfSequenceDetailModel> result = new ArrayList<>();
1011 
 
1012 
        for (RbmCallSequenceLog log : uniqueOosLogsByFofoId.values()) {
1013 
            CustomRetailer retailer = retailerMap.get(log.getFofoId());
1014 
            String partyName = retailer != null
1015 
                    ? retailer.getBusinessName()
1016 
                    : "Unknown (" + log.getFofoId() + ")";
1017 
            String code = retailer != null
1018 
                    ? retailer.getCode()
1019 
                    : "-";
1020 
 
1021 
            String time = log.getCreateTimestamp() != null
1022 
                    ? log.getCreateTimestamp().format(timeFormatter)
1023 
                    : "-";
1024 
 
1025 
            result.add(new OutOfSequenceDetailModel(partyName, code, time));
1026 
        }
1027 
 
1028 
        return result;
1029 
    }
1030 
 
1031 
    @Override
1032 
    public List<CalledPartnerDetailModel> getCalledPartnerDetails(int authId) throws ProfitMandiBusinessException {
1033 
        return getCalledPartnerDetails(authId, LocalDate.now());
1034 
    }
1035 
 
1036 
    @Override
1037 
    public List<CalledPartnerDetailModel> getCalledPartnerDetails(int authId, LocalDate date) throws ProfitMandiBusinessException {
1038 
        // Get all call logs for this auth user on this date
1039 
        LOGGER.info("getCalledPartnerDetails: authId={}, date={}", authId, date);
1040 
        List<AgentCallLog> callLogs = agentCallLogRepository.findByAuthIdAndDate(authId, date);
1041 
        LOGGER.info("Found {} call logs for authId={} on date={}", callLogs.size(), authId, date);
1042 
 
1043 
        if (callLogs.isEmpty()) {
1044 
            return Collections.emptyList();
1045 
        }
1046 
 
1047 
        // Build a map of normalized customer number -> fofoId
1048 
        Map<String, Integer> customerToFofoIdMap = new HashMap<>();
1049 
        Set<String> normalizedNumbers = new HashSet<>();
1050 
 
1051 
        for (AgentCallLog callLog : callLogs) {
1052 
            String customerNumber = callLog.getCustomerNumber();
1053 
            if (customerNumber != null) {
1054 
                String normalized = customerNumber.startsWith("+91") ? customerNumber.substring(3) : customerNumber;
1055 
                normalizedNumbers.add(normalized);
1056 
            }
1057 
        }
1058 
 
1059 
        // For each normalized number, find fofoId from retailer_contact first, then address
1060 
        for (String mobile : normalizedNumbers) {
1061 
            Integer fofoId = findFofoIdByMobile(mobile);
1062 
            if (fofoId != null) {
1063 
                customerToFofoIdMap.put(mobile, fofoId);
1064 
            }
1065 
        }
1066 
 
1067 
        // Get unique fofoIds for retailer lookup
1068 
        Set<Integer> fofoIds = new HashSet<>(customerToFofoIdMap.values());
1069 
        Map<Integer, CustomRetailer> retailerMap = Collections.emptyMap();
1070 
        if (!fofoIds.isEmpty()) {
1071 
            try {
1072 
                retailerMap = retailerService.getFofoRetailers(new ArrayList<>(fofoIds));
1073 
            } catch (ProfitMandiBusinessException e) {
1074 
                LOGGER.error("Error fetching fofo stores", e);
1075 
            }
1076 
        }
1077 
 
1078 
        // Get today's remarks for these fofoIds
1079 
        Map<Integer, List<PartnerCollectionRemark>> fofoRemarkMap = new HashMap<>();
1080 
        if (!fofoIds.isEmpty()) {
1081 
            List<PartnerCollectionRemark> todayRemarks = partnerCollectionRemarkRepository
1082 
                    .selectAllByFofoIdsOnDate(new ArrayList<>(fofoIds), date);
1083 
            for (PartnerCollectionRemark remark : todayRemarks) {
1084 
                fofoRemarkMap.computeIfAbsent(remark.getFofoId(), k -> new ArrayList<>()).add(remark);
1085 
            }
1086 
        }
1087 
 
1088 
        DateTimeFormatter timeFormatter = DateTimeFormatter.ofPattern("hh:mm a");
1089 
        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("dd-MM-yyyy hh:mm a");
1090 
        List<CalledPartnerDetailModel> result = new ArrayList<>();
1091 
 
1092 
        for (com.spice.profitmandi.dao.entity.cs.AgentCallLog callLog : callLogs) {
1093 
            String customerNumber = callLog.getCustomerNumber();
1094 
            if (customerNumber == null) {
1095 
                continue;
1096 
            }
1097 
 
1098 
            String normalized = customerNumber.startsWith("+91") ? customerNumber.substring(3) : customerNumber;
1099 
            Integer fofoId = customerToFofoIdMap.get(normalized);
1100 
 
1101 
            String partyName = "Unknown";
1102 
            String code = "-";
1103 
 
1104 
            if (fofoId != null) {
1105 
                CustomRetailer retailer = retailerMap.get(fofoId);
1106 
                if (retailer != null) {
1107 
                    partyName = retailer.getBusinessName();
1108 
                    code = retailer.getCode();
1109 
                } else {
1110 
                    partyName = "Unknown (" + fofoId + ")";
1111 
                }
1112 
            } else {
1113 
                partyName = "Unknown (" + normalized + ")";
1114 
            }
1115 
 
1116 
            // Get remark if available
1117 
            String remarkValue = "-";
1118 
            String messageValue = "-";
1119 
            String remarkTime = "-";
1120 
 
1121 
            if (fofoId != null && fofoRemarkMap.containsKey(fofoId)) {
1122 
                List<PartnerCollectionRemark> remarks = fofoRemarkMap.get(fofoId);
1123 
                if (!remarks.isEmpty()) {
1124 
                    PartnerCollectionRemark remark = remarks.get(0);
1125 
                    remarkValue = remark.getRemark() != null ? remark.getRemark().getValue() : "-";
1126 
                    messageValue = remark.getMessage() != null ? remark.getMessage() : "-";
1127 
                    remarkTime = remark.getCreateTimestamp() != null ? remark.getCreateTimestamp().format(timeFormatter) : "-";
1128 
                }
1129 
            }
1130 
 
1131 
            // Build call log data
1132 
            String recordingUrl = callLog.getRecordingUrl();
1133 
            String callStatus = callLog.getCallStatus();
1134 
            String callDuration = callLog.getCallDuration();
1135 
            String callDateTime = null;
1136 
            if (callLog.getCallDate() != null && callLog.getCallTime() != null) {
1137 
                LocalDateTime callDateTimeObj = LocalDateTime.of(callLog.getCallDate(), callLog.getCallTime());
1138 
                callDateTime = callDateTimeObj.format(dateTimeFormatter);
1139 
            }
1140 
 
1141 
            result.add(new CalledPartnerDetailModel(partyName, code, remarkValue, messageValue, remarkTime,
1142 
                    recordingUrl, callStatus, callDuration, callDateTime));
1143 
        }
1144 
 
1145 
        return result;
1146 
    }
1147 
 
1148 
    private Integer findFofoIdByMobile(String mobile) {
1149 
        // First check retailer_contact
1150 
        List<RetailerContact> contacts = retailerContactRepository.selectByMobile(mobile);
1151 
        if (contacts != null && !contacts.isEmpty()) {
1152 
            return contacts.get(0).getFofoId();
1153 
        }
1154 
 
1155 
        // Fallback to user.address
1156 
        List<Address> addresses = addressRepository.selectAllByPhoneNumber(mobile);
1157 
        if (addresses != null && !addresses.isEmpty()) {
1158 
            return addresses.get(0).getRetaierId();
1159 
        }
1160 
 
1161 
        return null;
1162 
    }
1163 
 
1164 
    private List<CalledPartnerDetailModel> buildCalledPartnerResult(List<PartnerCollectionRemark> allRemarks) {
1165 
        if (allRemarks.isEmpty()) {
1166 
            return Collections.emptyList();
1167 
        }
1168 
 
1169 
        // Get unique fofoIds for retailer lookup
1170 
        Set<Integer> fofoIds = allRemarks.stream()
1171 
                .map(PartnerCollectionRemark::getFofoId)
1172 
                .collect(Collectors.toSet());
1173 
        Map<Integer, CustomRetailer> retailerMap = Collections.emptyMap();
1174 
        try {
1175 
            retailerMap = retailerService.getFofoRetailers(new ArrayList<>(fofoIds));
1176 
        } catch (ProfitMandiBusinessException e) {
1177 
            LOGGER.error("Error fetching fofo stores", e);
1178 
        }
1179 
 
1180 
        // Fetch call logs for remarks that have agentCallLogId
1181 
        Map<Long, com.spice.profitmandi.dao.entity.cs.AgentCallLog> callLogMap = new HashMap<>();
1182 
        try {
1183 
            List<Long> callLogIds = allRemarks.stream()
1184 
                    .filter(r -> r.getAgentCallLogId() > 0)
1185 
                    .map(PartnerCollectionRemark::getAgentCallLogId)
1186 
                    .collect(Collectors.toList());
1187 
 
1188 
            if (!callLogIds.isEmpty()) {
1189 
                List<com.spice.profitmandi.dao.entity.cs.AgentCallLog> callLogs = agentCallLogRepository.findByIds(callLogIds);
1190 
                if (callLogs != null) {
1191 
                    callLogMap = callLogs.stream()
1192 
                            .collect(Collectors.toMap(com.spice.profitmandi.dao.entity.cs.AgentCallLog::getId, c -> c, (a, b) -> a));
1193 
                }
1194 
            }
1195 
        } catch (Exception e) {
1196 
            LOGGER.error("Error fetching call logs by ids", e);
1197 
        }
1198 
 
1199 
        DateTimeFormatter timeFormatter = DateTimeFormatter.ofPattern("hh:mm a");
1200 
        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("dd-MM-yyyy hh:mm a");
1201 
        List<CalledPartnerDetailModel> result = new ArrayList<>();
1202 
 
1203 
        for (PartnerCollectionRemark remark : allRemarks) {
1204 
            CustomRetailer retailer = retailerMap.get(remark.getFofoId());
1205 
            String partyName = retailer != null
1206 
                    ? retailer.getBusinessName()
1207 
                    : "Unknown (" + remark.getFofoId() + ")";
1208 
            String code = retailer != null
1209 
                    ? retailer.getCode()
1210 
                    : "-";
1211 
 
1212 
            String remarkValue = remark.getRemark() != null
1213 
                    ? remark.getRemark().getValue()
1214 
                    : "-";
1215 
 
1216 
            String messageValue = remark.getMessage() != null
1217 
                    ? remark.getMessage()
1218 
                    : "-";
1219 
 
1220 
            String time = remark.getCreateTimestamp() != null
1221 
                    ? remark.getCreateTimestamp().format(timeFormatter)
1222 
                    : "-";
1223 
 
1224 
            // Get call log data if available
1225 
            String recordingUrl = null;
1226 
            String callStatus = null;
1227 
            String callDuration = null;
1228 
            String callDateTime = null;
1229 
 
1230 
            try {
1231 
                if (remark.getAgentCallLogId() > 0 && callLogMap.containsKey(remark.getAgentCallLogId())) {
1232 
                    com.spice.profitmandi.dao.entity.cs.AgentCallLog callLog = callLogMap.get(remark.getAgentCallLogId());
1233 
                    recordingUrl = callLog.getRecordingUrl();
1234 
                    callStatus = callLog.getCallStatus();
1235 
                    callDuration = callLog.getCallDuration();
1236 
                    if (callLog.getCallDate() != null && callLog.getCallTime() != null) {
1237 
                        LocalDateTime callDateTimeObj = LocalDateTime.of(callLog.getCallDate(), callLog.getCallTime());
1238 
                        callDateTime = callDateTimeObj.format(dateTimeFormatter);
1239 
                    }
1240 
                }
1241 
            } catch (Exception e) {
1242 
                LOGGER.error("Error processing call log for remark id: {}", remark.getId(), e);
1243 
            }
1244 
 
1245 
            result.add(new CalledPartnerDetailModel(partyName, code, remarkValue, messageValue, time,
1246 
                    recordingUrl, callStatus, callDuration, callDateTime));
1247 
        }
1248 
 
1249 
        return result;
1250 
    }
1251 
 
1252 
    @Override
1253 
    public List<List<String>> getRbmCallTargetRawDataByAuthId(int authId) throws Exception {
1254 
        List<List<String>> rows = new ArrayList<>();
1255 
 
1256 
        // Get auth user
1257 
        List<AuthUser> authUsers = authRepository.selectByIds(Collections.singletonList(authId));
1258 
        if (authUsers.isEmpty()) {
1259 
            return rows;
1260 
        }
1261 
        AuthUser authUser = authUsers.get(0);
1262 
 
1263 
        // Get positions to determine if L2
1264 
        List<Position> positions = positionRepository.selectPositionByAuthIds(Collections.singletonList(authId));
1265 
        boolean isL2 = positions.stream()
1266 
                .anyMatch(p -> ProfitMandiConstants.TICKET_CATEGORY_RBM == p.getCategoryId()
1267 
                        && EscalationType.L2.equals(p.getEscalationType()));
1268 
 
1269 
        LocalDateTime startDate = LocalDate.now().atStartOfDay();
1270 
        LocalDate firstOfMonth = LocalDate.now().withDayOfMonth(1);
1271 
        LocalDate endOfMonth = LocalDate.now().withDayOfMonth(LocalDate.now().lengthOfMonth()).plusDays(1);
1272 
 
1273 
        // Get fofo IDs from mtdBillingData (same source as Partner Count in getRbmCallTargetModels)
1274 
        List<RbmWeeklyBillingModel> mtdBillingData = getWeeklyBillingDataForMonth(firstOfMonth, endOfMonth);
1275 
 
1276 
        List<Integer> fofoIdList;
1277 
        if (isL2) {
37145 ranu 1278 
            // L2: get fofo IDs from escalated tickets (same as getRbmCallTargetModels).
1279 
            // selectOpenEscalatedTicketsByAuthIds already filters to tickets where authId is the
1280 
            // current assignee AND the ticket has moved past L1 — no post-filter needed.
37010 ranu 1281 
            List<Ticket> escalatedTickets = ticketRepository.selectOpenEscalatedTicketsByAuthIds(Collections.singletonList(authId));
1282 
            fofoIdList = escalatedTickets.stream()
1283 
                    .map(Ticket::getFofoId)
1284 
                    .distinct()
1285 
                    .collect(Collectors.toList());
1286 
        } else {
1287 
            // L1: get fofo IDs from mtdBillingData with isTargetedPartner (same as Partner Count)
1288 
            fofoIdList = mtdBillingData.stream()
1289 
                    .filter(RbmWeeklyBillingModel::isTargetedPartner)
1290 
                    .filter(m -> m.getAuthId() == authId)
1291 
                    .map(RbmWeeklyBillingModel::getFofoId)
1292 
                    .distinct()
1293 
                    .collect(Collectors.toList());
1294 
        }
1295 
 
1296 
        if (fofoIdList.isEmpty()) {
1297 
            return rows;
1298 
        }
1299 
 
1300 
        // MTD billed fofoIds for zero billing check
1301 
        Set<Integer> mtdBilledFofoIds = mtdBillingData.stream()
1302 
                .filter(RbmWeeklyBillingModel::isMtdBilled)
1303 
                .map(RbmWeeklyBillingModel::getFofoId)
1304 
                .collect(Collectors.toSet());
1305 
 
1306 
        // Collection rank map for status calculation
1307 
        Map<Integer, Integer> collectionRankMap = new HashMap<>();
1308 
        try {
1309 
            collectionRankMap = partnerCollectionService.getCollectionRankMap(fofoIdList, startDate);
1310 
        } catch (ProfitMandiBusinessException e) {
1311 
            LOGGER.error("Error fetching collection rank map", e);
1312 
        }
1313 
 
1314 
        // Resolve partner names/codes
1315 
        Map<Integer, CustomRetailer> retailerMap = Collections.emptyMap();
1316 
        if (!fofoIdList.isEmpty()) {
1317 
            try {
1318 
                retailerMap = retailerService.getFofoRetailers(fofoIdList);
1319 
            } catch (ProfitMandiBusinessException e) {
1320 
                LOGGER.error("Error fetching fofo retailers for raw data", e);
1321 
            }
1322 
        }
1323 
 
1324 
        String rbmName = authUser.getFullName() + (isL2 ? " (L2)" : "");
1325 
 
1326 
        // Build rows for ALL partners (same count as Partner Count)
1327 
        for (Integer fofoId : fofoIdList) {
1328 
            // Default to rank 5 (Normal) for partners without collection plan
1329 
            int rank = collectionRankMap.getOrDefault(fofoId, 5);
1330 
            boolean hasZeroBilling = !mtdBilledFofoIds.contains(fofoId);
1331 
 
1332 
            // Status assignment with same priority as getRbmCallTargetModels
1333 
            String status;
1334 
            if (rank == 1) {
1335 
                status = "Plan Today";
1336 
            } else if (rank == 2) {
1337 
                status = "Carry Forward";
1338 
            } else if (hasZeroBilling) {
1339 
                status = "Zero Billing";
1340 
            } else if (rank == 3) {
1341 
                status = "Untouched";
1342 
            } else if (rank == 4) {
1343 
                status = "Future Plan";
1344 
            } else {
1345 
                status = "Normal";
1346 
            }
1347 
 
1348 
            CustomRetailer retailer = retailerMap.get(fofoId);
1349 
            String partnerName = retailer != null ? retailer.getBusinessName() : "Unknown (" + fofoId + ")";
1350 
            String partnerCode = retailer != null ? retailer.getCode() : "-";
1351 
 
1352 
            rows.add(Arrays.asList(partnerName, partnerCode, status, rbmName));
1353 
        }
1354 
 
1355 
        return rows;
1356 
    }
1357 
 
1358 
    @Override
1359 
    public List<List<String>> getAllRbmCallTargetRawData() throws Exception {
1360 
        List<List<String>> rows = new ArrayList<>();
1361 
 
1362 
        // Get all L1 RBM positions (rows are emitted per L1 RBM).
1363 
        // Call-log lookup happens against ALL callers via findAllByDate — so an L2/L3 or admin call
1364 
        // still counts as "tried" for the partner.
1365 
        List<Position> allRbmPositions = positionRepository
1366 
                .selectPositionByCategoryId(ProfitMandiConstants.TICKET_CATEGORY_RBM).stream()
1367 
                .filter(x -> EscalationType.L1.equals(x.getEscalationType()))
1368 
                .collect(Collectors.toList());
1369 
 
1370 
        List<Integer> l1AuthIds = allRbmPositions.stream()
1371 
                .map(Position::getAuthUserId).distinct().collect(Collectors.toList());
1372 
 
1373 
        if (l1AuthIds.isEmpty()) {
1374 
            return rows;
1375 
        }
1376 
 
1377 
        // Get auth user map
1378 
        Map<Integer, AuthUser> authUserMap = authRepository.selectByIds(l1AuthIds).stream()
1379 
                .collect(Collectors.toMap(AuthUser::getId, au -> au));
1380 
 
1381 
        // Positions per auth user (needed early to build L1 partner assignments below)
1382 
        Map<Integer, List<Position>> positionsByAuthId = positionRepository.selectPositionByAuthIds(l1AuthIds).stream()
1383 
                .collect(Collectors.groupingBy(Position::getAuthUserId));
1384 
 
1385 
        // Build L1 partner assignments from partner_position — SAME source the UI summary uses for
1386 
        // Calling Target. storeGuyMap (email-based CS mapping) was returning smaller/different lists
1387 
        // for users who are also L2, causing summary=43 but download=3 for Ebadullah.
1388 
        Map<Integer, Integer> l1PositionIdToAuthId = new HashMap<>();
1389 
        for (int rbmAuthId : l1AuthIds) {
1390 
            List<Position> positions = positionsByAuthId.getOrDefault(rbmAuthId, Collections.emptyList());
1391 
            for (Position p : positions) {
1392 
                if (ProfitMandiConstants.TICKET_CATEGORY_RBM == p.getCategoryId()
1393 
                        && EscalationType.L1.equals(p.getEscalationType())) {
1394 
                    l1PositionIdToAuthId.put(p.getId(), rbmAuthId);
1395 
                }
1396 
            }
1397 
        }
1398 
 
1399 
        Map<Integer, List<Integer>> rbmToFofoIdsMap = new HashMap<>();
1400 
        Set<Integer> allFofoIds = new HashSet<>();
1401 
        if (!l1PositionIdToAuthId.isEmpty()) {
1402 
            List<com.spice.profitmandi.dao.entity.cs.PartnerPosition> allL1PPs =
1403 
                    partnerPositionRepository.selectByPositionIds(new ArrayList<>(l1PositionIdToAuthId.keySet()));
1404 
            // Group by owning RBM; keep partners distinct within each RBM.
1405 
            Map<Integer, Set<Integer>> perRbmSets = new HashMap<>();
1406 
            for (com.spice.profitmandi.dao.entity.cs.PartnerPosition pp : allL1PPs) {
1407 
                Integer ownerAuthId = l1PositionIdToAuthId.get(pp.getPositionId());
1408 
                if (ownerAuthId == null) continue;
1409 
                perRbmSets.computeIfAbsent(ownerAuthId, k -> new HashSet<>()).add(pp.getFofoId());
1410 
                allFofoIds.add(pp.getFofoId());
1411 
            }
1412 
            for (Map.Entry<Integer, Set<Integer>> e : perRbmSets.entrySet()) {
1413 
                rbmToFofoIdsMap.put(e.getKey(), new ArrayList<>(e.getValue()));
1414 
            }
1415 
        }
1416 
 
1417 
        if (allFofoIds.isEmpty()) {
1418 
            return rows;
1419 
        }
1420 
 
1421 
        // Get fofo stores for filtering and name resolution
1422 
        Map<Integer, FofoStore> fofoStoresMap = new HashMap<>();
1423 
        try {
1424 
            fofoStoresMap = fofoStoreRepository.selectByRetailerIds(new ArrayList<>(allFofoIds)).stream()
1425 
                    .collect(Collectors.toMap(FofoStore::getId, x -> x, (a, b) -> a));
1426 
        } catch (ProfitMandiBusinessException e) {
1427 
            LOGGER.error("Error fetching fofo stores for all raw data", e);
1428 
        }
1429 
 
1430 
        // Batch fetch collection rank map
1431 
        LocalDateTime startDate = LocalDate.now().atStartOfDay();
1432 
        Map<Integer, Integer> allCollectionRankMap = new HashMap<>();
1433 
        try {
1434 
            allCollectionRankMap = partnerCollectionService.getCollectionRankMap(new ArrayList<>(allFofoIds), startDate);
1435 
        } catch (ProfitMandiBusinessException e) {
1436 
            LOGGER.error("Error fetching collection rank map for all raw data", e);
1437 
        }
1438 
 
1439 
        // MTD billing data
1440 
        LocalDate firstOfMonth = LocalDate.now().withDayOfMonth(1);
1441 
        LocalDate endOfMonth = LocalDate.now().withDayOfMonth(LocalDate.now().lengthOfMonth()).plusDays(1);
1442 
        List<RbmWeeklyBillingModel> mtdBillingData = getWeeklyBillingDataForMonth(firstOfMonth, endOfMonth);
1443 
        Set<Integer> allMtdBilledFofoIds = mtdBillingData.stream()
1444 
                .filter(RbmWeeklyBillingModel::isMtdBilled)
1445 
                .map(RbmWeeklyBillingModel::getFofoId)
1446 
                .collect(Collectors.toSet());
1447 
 
1448 
        // Batch fetch partner collection remarks for escalation filtering
1449 
        Map<Integer, PartnerCollectionRemark> allPartnerCollectionRemarks = new HashMap<>();
1450 
        if (!allFofoIds.isEmpty()) {
1451 
            List<Integer> allRemarkIds = partnerCollectionRemarkRepository.selectMaxRemarkId(new ArrayList<>(allFofoIds));
1452 
            if (!allRemarkIds.isEmpty()) {
1453 
                allPartnerCollectionRemarks = partnerCollectionRemarkRepository.selectByIds(allRemarkIds).stream()
1454 
                        .collect(Collectors.toMap(PartnerCollectionRemark::getFofoId, x -> x, (a, b) -> a));
1455 
            }
1456 
        }
1457 
 
1458 
        // Resolve partner names/codes in batch
1459 
        Map<Integer, CustomRetailer> retailerMap = Collections.emptyMap();
1460 
        try {
1461 
            retailerMap = retailerService.getFofoRetailers(new ArrayList<>(allFofoIds));
1462 
        } catch (ProfitMandiBusinessException e) {
1463 
            LOGGER.error("Error fetching fofo retailers for all raw data", e);
1464 
        }
1465 
 
1466 
        // Batch-fetch EVERY call log for today (across all callers, not just RBMs) and build a
1467 
        // fofoId -> latest AgentCallLog map. We use findAllByDate here so that calls placed by
1468 
        // admins, escalation agents, or anyone whose authId isn't in the RBM position table are
1469 
        // still surfaced. Downstream we only read this map for fofoIds we actually emit, so
1470 
        // extra entries are harmless.
37013 ranu 1471 
        // Group ALL today's call logs by fofoId so we can apply the Tried rule per partner
1472 
        // (uses customerStatus + persistence, same as the summary's Tried metric).
1473 
        Map<Integer, List<AgentCallLog>> callsByFofoId = new HashMap<>();
37010 ranu 1474 
        try {
1475 
            List<AgentCallLog> todayCallLogs = agentCallLogRepository.findAllByDate(LocalDate.now());
1476 
            Set<String> normalizedMobiles = new HashSet<>();
1477 
            for (AgentCallLog log : todayCallLogs) {
1478 
                if (log.getCustomerNumber() != null) {
1479 
                    String n = log.getCustomerNumber();
1480 
                    normalizedMobiles.add(n.startsWith("+91") ? n.substring(3) : n);
1481 
                }
1482 
            }
1483 
            Map<String, Integer> mobileToFofoIdMap = buildMobileToFofoIdMap(normalizedMobiles);
1484 
            for (AgentCallLog log : todayCallLogs) {
1485 
                if (log.getCustomerNumber() == null) continue;
1486 
                String n = log.getCustomerNumber();
1487 
                String normalized = n.startsWith("+91") ? n.substring(3) : n;
1488 
                Integer fofoId = mobileToFofoIdMap.get(normalized);
1489 
                if (fofoId == null) continue;
37013 ranu 1490 
                callsByFofoId.computeIfAbsent(fofoId, k -> new ArrayList<>()).add(log);
37010 ranu 1491 
            }
1492 
        } catch (Exception e) {
37013 ranu 1493 
            LOGGER.error("Error building call logs map for all raw data", e);
37010 ranu 1494 
        }
1495 
 
1496 
        // Process each L1 RBM
1497 
        // Note: no cross-RBM dedup — a partner assigned to multiple RBMs appears once per RBM,
1498 
        // matching the UI summary counts so row totals per RBM equal that RBM's "Calling Target".
1499 
        for (int rbmAuthId : l1AuthIds) {
1500 
            AuthUser authUser = authUserMap.get(rbmAuthId);
1501 
            if (authUser == null) {
1502 
                continue;
1503 
            }
1504 
 
1505 
            List<Integer> fofoIdList = rbmToFofoIdsMap.getOrDefault(rbmAuthId, Collections.emptyList());
1506 
            if (fofoIdList.isEmpty()) {
1507 
                continue; // No L1 partner assignments for this RBM
1508 
            }
1509 
 
1510 
            // Filter escalated partners for L1 RBMs
1511 
            List<Position> positions = positionsByAuthId.getOrDefault(rbmAuthId, Collections.emptyList());
1512 
            boolean isRBMAndL1 = positions.stream()
1513 
                    .anyMatch(position ->
1514 
                            ProfitMandiConstants.TICKET_CATEGORY_RBM == position.getCategoryId()
1515 
                                    && EscalationType.L1.equals(position.getEscalationType()));
1516 
 
1517 
            List<Integer> fofoIds = fofoIdList;
1518 
            if (isRBMAndL1) {
1519 
                Map<Integer, PartnerCollectionRemark> partnerRemarks = new HashMap<>();
1520 
                for (Integer fofoId : fofoIdList) {
1521 
                    if (allPartnerCollectionRemarks.containsKey(fofoId)) {
1522 
                        partnerRemarks.put(fofoId, allPartnerCollectionRemarks.get(fofoId));
1523 
                    }
1524 
                }
1525 
                Map<Integer, PartnerCollectionRemark> finalPartnerRemarks = partnerRemarks;
1526 
                fofoIds = fofoIdList.stream()
1527 
                        .filter(fofoId -> {
1528 
                            if (!finalPartnerRemarks.containsKey(fofoId)) return true;
1529 
                            PartnerCollectionRemark pcr = finalPartnerRemarks.get(fofoId);
1530 
                            return !(CollectionRemark.RBM_L2_ESCALATION.equals(pcr.getRemark())
1531 
                                    || CollectionRemark.SALES_ESCALATION.equals(pcr.getRemark()));
1532 
                        })
1533 
                        .collect(Collectors.toList());
1534 
            }
1535 
 
1536 
            // Filter to only external, ACTIVE or REVIVAL stores
1537 
            Map<Integer, FofoStore> finalFofoStoresMap = fofoStoresMap;
1538 
            List<Integer> validFofoIds = fofoIds.stream()
1539 
                    .filter(fofoId -> {
1540 
                        FofoStore store = finalFofoStoresMap.get(fofoId);
1541 
                        if (store == null || store.isInternal()) return false;
1542 
                        return ActivationType.ACTIVE.equals(store.getActivationType())
1543 
                                || ActivationType.REVIVAL.equals(store.getActivationType());
1544 
                    })
1545 
                    .collect(Collectors.toList());
1546 
 
1547 
            String rbmName = authUser.getFullName();
1548 
 
1549 
            // Categorize each partner and add a row per RBM assignment.
1550 
            for (Integer fofoId : validFofoIds) {
1551 
                int rank = allCollectionRankMap.getOrDefault(fofoId, 5);
1552 
                boolean hasZeroBilling = !allMtdBilledFofoIds.contains(fofoId);
1553 
 
1554 
                String status;
1555 
                if (rank == 1) {
1556 
                    status = "Plan Today";
1557 
                } else if (rank == 2) {
1558 
                    status = "Carry Forward";
1559 
                } else if (hasZeroBilling) {
1560 
                    status = "Zero Billing";
1561 
                } else if (rank == 3) {
1562 
                    status = "Untouched";
1563 
                } else {
1564 
                    continue; // Skip Future Plan and Normal — only include calling target parties
1565 
                }
1566 
 
1567 
                CustomRetailer retailer = retailerMap.get(fofoId);
1568 
                String partnerName = retailer != null ? retailer.getBusinessName() : "Unknown (" + fofoId + ")";
1569 
                String partnerCode = retailer != null ? retailer.getCode() : "-";
1570 
 
37013 ranu 1571 
                // Show the latest customerStatus from today's call logs for this partner.
1572 
                // "Did Not Try" only when there is no call log at all today.
1573 
                List<AgentCallLog> partnerCalls = callsByFofoId.getOrDefault(fofoId, Collections.emptyList());
37010 ranu 1574 
                String latestCallStatus = "Did Not Try";
37013 ranu 1575 
                if (!partnerCalls.isEmpty()) {
1576 
                    AgentCallLog latest = null;
1577 
                    for (AgentCallLog l : partnerCalls) {
1578 
                        if (latest == null || (l.getId() != null && latest.getId() != null && l.getId() > latest.getId())) {
1579 
                            latest = l;
1580 
                        }
1581 
                    }
1582 
                    if (latest != null && latest.getCustomerStatus() != null && !latest.getCustomerStatus().isEmpty()) {
1583 
                        latestCallStatus = latest.getCustomerStatus();
1584 
                    }
37010 ranu 1585 
                }
1586 
 
1587 
                rows.add(Arrays.asList(partnerName, partnerCode, status, rbmName, latestCallStatus));
1588 
            }
1589 
        }
1590 
 
1591 
        return rows;
1592 
    }
1593 
 
1594 
    /**
1595 
     * Get count of distinct partners called today based on call logs.
1596 
     * Maps customerNumber from call log to fofoId using retailer_contact and address.
1597 
     * If same fofoId is called multiple times, counts only once.
1598 
     * Numbers without fofoId mapping are also counted (by distinct customer number).
1599 
     *
1600 
     * @param authId the RBM auth ID
1601 
     * @return count of distinct partners/numbers called today
1602 
     */
1603 
    public long getCalledCountFromCallLogs(long authId) {
1604 
        return getCalledCountFromCallLogs(authId, LocalDate.now());
1605 
    }
1606 
 
1607 
    public long getCalledCountFromCallLogs(long authId, LocalDate date) {
1608 
        return getCallStats(authId, date)[0];
1609 
    }
1610 
 
1611 
    /**
1612 
     * Batch-builds a mobile (normalized, +91 stripped) -> fofoId mapping for all the given mobiles
1613 
     * in just two queries (retailer_contact, then address fallback for unmapped numbers).
1614 
     * Replaces the previous per-call-log findFofoIdByMobile() N+1 lookups.
1615 
     */
1616 
    private Map<String, Integer> buildMobileToFofoIdMap(Set<String> normalizedMobiles) {
1617 
        Map<String, Integer> result = new HashMap<>();
1618 
        if (normalizedMobiles == null || normalizedMobiles.isEmpty()) {
1619 
            return result;
1620 
        }
1621 
        List<String> mobilesList = new ArrayList<>(normalizedMobiles);
1622 
 
1623 
        // First pass: retailer_contact
1624 
        List<RetailerContact> contacts = retailerContactRepository.selectByMobiles(mobilesList);
1625 
        for (RetailerContact rc : contacts) {
1626 
            // Keep the first fofoId we see per mobile (matches old single-mobile behavior of get(0))
1627 
            result.putIfAbsent(rc.getMobile(), rc.getFofoId());
1628 
        }
1629 
 
1630 
        // Second pass: address fallback for mobiles not yet mapped
1631 
        List<String> unmapped = mobilesList.stream()
1632 
                .filter(m -> !result.containsKey(m))
1633 
                .collect(Collectors.toList());
1634 
        if (!unmapped.isEmpty()) {
1635 
            List<Address> addresses = addressRepository.selectAllByPhoneNumbers(unmapped);
1636 
            for (Address addr : addresses) {
1637 
                result.putIfAbsent(addr.getPhoneNumber(), addr.getRetaierId());
1638 
            }
1639 
        }
1640 
        return result;
1641 
    }
1642 
 
1643 
    /**
1644 
     * In-memory variant of getCallStats() that uses pre-fetched call logs and mobile→fofoId mapping.
1645 
     * Used by the batch-fetched RBM Call Target loop to avoid N+1 query patterns.
1646 
     */
1647 
    private long[] getCallStatsFromLogs(List<AgentCallLog> callLogs, Map<String, Integer> mobileToFofoIdMap) {
1648 
        if (callLogs == null || callLogs.isEmpty()) {
1649 
            return new long[]{0, 0, 0};
1650 
        }
1651 
 
1652 
        Set<Integer> calledFofoIds = new HashSet<>();
1653 
        Set<String> calledNumbersWithoutFofoId = new HashSet<>();
1654 
        long totalRecordingCalls = 0;
1655 
        Set<String> uniqueRecordingNumbers = new HashSet<>();
1656 
 
1657 
        for (AgentCallLog callLog : callLogs) {
1658 
            String customerNumber = callLog.getCustomerNumber();
1659 
            if (customerNumber != null) {
1660 
                String normalized = customerNumber.startsWith("+91") ? customerNumber.substring(3) : customerNumber;
1661 
                Integer fofoId = mobileToFofoIdMap.get(normalized);
1662 
                if (fofoId != null) {
1663 
                    calledFofoIds.add(fofoId);
1664 
                } else {
1665 
                    calledNumbersWithoutFofoId.add(normalized);
1666 
                }
1667 
 
1668 
                if (callLog.getRecordingUrl() != null && !callLog.getRecordingUrl().isEmpty()
1669 
                        && !"None".equalsIgnoreCase(callLog.getRecordingUrl())) {
1670 
                    totalRecordingCalls++;
1671 
                    uniqueRecordingNumbers.add(normalized);
1672 
                }
1673 
            }
1674 
        }
1675 
 
1676 
        long calledCount = calledFofoIds.size() + calledNumbersWithoutFofoId.size();
1677 
        return new long[]{calledCount, totalRecordingCalls, uniqueRecordingNumbers.size()};
1678 
    }
1679 
 
37022 ranu 1680 
    // Same rule as computeUniqueTriedCount but restricted to fofoIds in the target set.
1681 
    // Used to compute "TGT Calls" — how many of THIS RBM's Calling Target partners were
1682 
    // actually engaged today. Off-target activity is silently excluded.
1683 
    private long computeUniqueTriedCountForTargets(List<AgentCallLog> callLogs,
1684 
                                                   Map<String, Integer> mobileToFofoIdMap,
1685 
                                                   Set<Integer> targetFofoIds) {
1686 
        if (callLogs == null || callLogs.isEmpty()) return 0L;
1687 
        if (targetFofoIds == null || targetFofoIds.isEmpty()) return 0L;
1688 
        Map<Integer, List<AgentCallLog>> byFofoId = new HashMap<>();
1689 
        for (AgentCallLog log : callLogs) {
1690 
            String num = log.getCustomerNumber();
1691 
            if (num == null) continue;
1692 
            String normalized = num.startsWith("+91") ? num.substring(3) : num;
1693 
            Integer fofoId = mobileToFofoIdMap != null ? mobileToFofoIdMap.get(normalized) : null;
1694 
            if (fofoId == null) continue;
1695 
            if (!targetFofoIds.contains(fofoId)) continue; // off-target, skip
1696 
            byFofoId.computeIfAbsent(fofoId, k -> new ArrayList<>()).add(log);
1697 
        }
1698 
        long count = 0L;
1699 
        for (List<AgentCallLog> logs : byFofoId.values()) {
1700 
            boolean hasNonMissed = false;
1701 
            for (AgentCallLog l : logs) {
1702 
                if (!isMissedCustomerStatus(l.getCustomerStatus())) {
1703 
                    hasNonMissed = true;
1704 
                    break;
1705 
                }
1706 
            }
1707 
            if (hasNonMissed) count++;
1708 
            else if (logs.size() >= 3) count++;
1709 
        }
1710 
        return count;
1711 
    }
1712 
 
37012 ranu 1713 
    // Matches "Missed", "MISSED", "No Answer", "NO_ANSWER", "no-answer" etc.
1714 
    // Kommuno currently emits "Missed" — kept liberal so we're covered if the tag changes.
1715 
    private static boolean isMissedCustomerStatus(String status) {
1716 
        if (status == null) return false;
1717 
        String s = status.trim().toLowerCase().replace('_', ' ').replace('-', ' ').replaceAll("\\s+", " ");
1718 
        return s.equals("missed") || s.equals("no answer") || s.equals("noanswer");
1719 
    }
1720 
 
37013 ranu 1721 
 
36234 ranu 1722 
    public List<RbmCallTargetModel> getRbmCallTargetModels(LocalDate queryDate) throws Exception {
35631 ranu 1723 
        long methodStart = System.currentTimeMillis();
1724 
        List<RbmCallTargetModel> rbmCallTargetModels = new ArrayList<>();
1725 
 
1726 
        // Get all RBM positions (L1 and L2)
1727 
        long start = System.currentTimeMillis();
1728 
        List<Position> allRbmPositions = positionRepository
1729 
                .selectPositionByCategoryId(ProfitMandiConstants.TICKET_CATEGORY_RBM).stream()
36210 ranu 1730 
                .filter(x -> Arrays.asList(EscalationType.L1, EscalationType.L2, EscalationType.L3).contains(x.getEscalationType()))
35631 ranu 1731 
                .collect(Collectors.toList());
1732 
 
36210 ranu 1733 
        // Separate L1, L2 and L3 auth IDs
35631 ranu 1734 
        List<Integer> l1AuthIds = allRbmPositions.stream()
1735 
                .filter(p -> EscalationType.L1.equals(p.getEscalationType()))
1736 
                .map(Position::getAuthUserId).distinct().collect(Collectors.toList());
1737 
        List<Integer> l2AuthIds = allRbmPositions.stream()
1738 
                .filter(p -> EscalationType.L2.equals(p.getEscalationType()))
1739 
                .map(Position::getAuthUserId).distinct().collect(Collectors.toList());
36210 ranu 1740 
        List<Integer> l3AuthIds = allRbmPositions.stream()
1741 
                .filter(p -> EscalationType.L3.equals(p.getEscalationType()))
1742 
                .map(Position::getAuthUserId).distinct().collect(Collectors.toList());
35631 ranu 1743 
 
1744 
        // Union of all auth IDs for batch fetching
1745 
        List<Integer> rbmPositionsAuthIds = allRbmPositions.stream()
1746 
                .map(Position::getAuthUserId).distinct().collect(Collectors.toList());
36210 ranu 1747 
        LOGGER.info("RBM Call Target - RBM positions fetch: {}ms, L1: {}, L2: {}, L3: {}", System.currentTimeMillis() - start, l1AuthIds.size(), l2AuthIds.size(), l3AuthIds.size());
35631 ranu 1748 
 
1749 
        start = System.currentTimeMillis();
1750 
        Map<String, Set<Integer>> storeGuyMap = csService.getAuthUserPartnerIdMapping();
1751 
        LOGGER.info("RBM Call Target - StoreGuyMap fetch: {}ms", System.currentTimeMillis() - start);
1752 
 
36234 ranu 1753 
        LocalDateTime startDate = queryDate.atStartOfDay();
1754 
        LocalDate firstOfMonth = queryDate.withDayOfMonth(1);
1755 
        LocalDate endOfMonth = queryDate.withDayOfMonth(queryDate.lengthOfMonth()).plusDays(1);
35631 ranu 1756 
 
1757 
        // Get auth user map
1758 
        start = System.currentTimeMillis();
1759 
        Map<Integer, AuthUser> authUserMap = authRepository.selectByIds(rbmPositionsAuthIds).stream()
1760 
                .collect(Collectors.toMap(AuthUser::getId, au -> au));
1761 
        LOGGER.info("RBM Call Target - AuthUser fetch: {}ms", System.currentTimeMillis() - start);
1762 
 
1763 
        // Batch fetch positions by auth IDs (to check if RBM is L1)
1764 
        start = System.currentTimeMillis();
1765 
        Map<Integer, List<Position>> positionsByAuthId = positionRepository.selectPositionByAuthIds(rbmPositionsAuthIds).stream()
1766 
                .collect(Collectors.groupingBy(Position::getAuthUserId));
1767 
        LOGGER.info("RBM Call Target - Positions by AuthId fetch: {}ms", System.currentTimeMillis() - start);
1768 
 
1769 
        // Get all fofo IDs for all RBMs
1770 
        Set<Integer> allFofoIds = new HashSet<>();
1771 
        Map<Integer, List<Integer>> rbmToFofoIdsMap = new HashMap<>();
1772 
        for (int rbmAuthId : rbmPositionsAuthIds) {
1773 
            AuthUser au = authUserMap.get(rbmAuthId);
1774 
            if (au != null && storeGuyMap.containsKey(au.getEmailId())) {
1775 
                List<Integer> fofoIds = new ArrayList<>(storeGuyMap.get(au.getEmailId()));
1776 
                allFofoIds.addAll(fofoIds);
1777 
                rbmToFofoIdsMap.put(rbmAuthId, fofoIds);
1778 
            }
1779 
        }
35816 ranu 1780 
        // Initialize L2 calling list map - will be populated after fetching remarks
35631 ranu 1781 
        Map<Integer, List<Integer>> l2AuthIdToFofoIds = new HashMap<>();
35816 ranu 1782 
        for (int l2AuthId : l2AuthIds) {
1783 
            l2AuthIdToFofoIds.put(l2AuthId, new ArrayList<>());
35631 ranu 1784 
        }
36210 ranu 1785 
        // Initialize L3 calling list map - will be populated after fetching remarks
1786 
        Map<Integer, List<Integer>> l3AuthIdToFofoIds = new HashMap<>();
1787 
        for (int l3AuthId : l3AuthIds) {
1788 
            l3AuthIdToFofoIds.put(l3AuthId, new ArrayList<>());
1789 
        }
35631 ranu 1790 
        LOGGER.info("RBM Call Target - Total fofo IDs to process: {}", allFofoIds.size());
1791 
 
1792 
        // Get only needed fofo stores (OPTIMIZED - was fetching ALL stores before)
1793 
        start = System.currentTimeMillis();
1794 
        Map<Integer, FofoStore> fofoStoresMap = new HashMap<>();
1795 
        if (!allFofoIds.isEmpty()) {
1796 
            try {
1797 
                fofoStoresMap = fofoStoreRepository.selectByRetailerIds(new ArrayList<>(allFofoIds)).stream()
1798 
                        .collect(Collectors.toMap(FofoStore::getId, x -> x, (a, b) -> a));
1799 
            } catch (ProfitMandiBusinessException e) {
1800 
                LOGGER.error("Error fetching fofo stores", e);
1801 
            }
1802 
        }
1803 
        LOGGER.info("RBM Call Target - FofoStores fetch (only needed): {}ms, count: {}", System.currentTimeMillis() - start, fofoStoresMap.size());
1804 
 
1805 
        // Batch fetch max remark ids for all fofoIds (for escalation filtering)
1806 
        start = System.currentTimeMillis();
1807 
        Map<Integer, PartnerCollectionRemark> allPartnerCollectionRemarks = new HashMap<>();
1808 
        if (!allFofoIds.isEmpty()) {
1809 
            List<Integer> allRemarkIds = partnerCollectionRemarkRepository.selectMaxRemarkId(new ArrayList<>(allFofoIds));
1810 
            if (!allRemarkIds.isEmpty()) {
1811 
                allPartnerCollectionRemarks = partnerCollectionRemarkRepository.selectByIds(allRemarkIds).stream()
1812 
                        .collect(Collectors.toMap(PartnerCollectionRemark::getFofoId, x -> x, (a, b) -> a));
1813 
            }
1814 
        }
1815 
        LOGGER.info("RBM Call Target - PartnerCollectionRemarks fetch: {}ms", System.currentTimeMillis() - start);
1816 
 
35816 ranu 1817 
        // Populate L2 calling list based on partners whose latest remark is RBM_L2_ESCALATION
1818 
        // Find the L1 who has the partner and add to that L1's manager (L2) calling list
1819 
        for (Map.Entry<Integer, PartnerCollectionRemark> entry : allPartnerCollectionRemarks.entrySet()) {
1820 
            Integer fofoId = entry.getKey();
1821 
            PartnerCollectionRemark remark = entry.getValue();
1822 
 
1823 
            if (CollectionRemark.RBM_L2_ESCALATION.equals(remark.getRemark())) {
1824 
                // Find which L1 RBM has this partner assigned
1825 
                for (int l1AuthId : l1AuthIds) {
1826 
                    List<Integer> l1FofoIds = rbmToFofoIdsMap.getOrDefault(l1AuthId, Collections.emptyList());
1827 
                    if (l1FofoIds.contains(fofoId)) {
1828 
                        // Get L1's manager (L2)
1829 
                        AuthUser l1User = authUserMap.get(l1AuthId);
1830 
                        if (l1User != null && l2AuthIdToFofoIds.containsKey(l1User.getManagerId())) {
1831 
                            int l2ManagerId = l1User.getManagerId();
1832 
                            l2AuthIdToFofoIds.get(l2ManagerId).add(fofoId);
1833 
                        }
1834 
                        break; // Found the L1 for this fofoId
1835 
                    }
1836 
                }
1837 
            }
1838 
        }
1839 
        LOGGER.info("RBM Call Target - L2 calling lists populated from RBM_L2_ESCALATION remarks");
1840 
 
36210 ranu 1841 
        // Populate L3 calling list based on partners whose latest remark is RBM_L3_ESCALATION
36212 ranu 1842 
        // Find the L1 who originally has the partner, then:
1843 
        // Case 1: L1 -> L3 directly (if L1's manager IS L3)
1844 
        // Case 2: L1 -> L2 -> L3 (if L1's manager is L2, then L2's manager is L3)
36210 ranu 1845 
        for (Map.Entry<Integer, PartnerCollectionRemark> entry : allPartnerCollectionRemarks.entrySet()) {
1846 
            Integer fofoId = entry.getKey();
1847 
            PartnerCollectionRemark remark = entry.getValue();
1848 
 
1849 
            if (CollectionRemark.RBM_L3_ESCALATION.equals(remark.getRemark())) {
1850 
                // Find which L1 RBM originally has this partner assigned
1851 
                for (int l1AuthId : l1AuthIds) {
1852 
                    List<Integer> l1FofoIds = rbmToFofoIdsMap.getOrDefault(l1AuthId, Collections.emptyList());
1853 
                    if (l1FofoIds.contains(fofoId)) {
1854 
                        AuthUser l1User = authUserMap.get(l1AuthId);
1855 
                        if (l1User != null) {
36212 ranu 1856 
                            int l1ManagerId = l1User.getManagerId();
1857 
                            // Case 1: L1's manager IS L3 directly (L1 → L3, no L2 in between)
1858 
                            if (l3AuthIdToFofoIds.containsKey(l1ManagerId)) {
1859 
                                l3AuthIdToFofoIds.get(l1ManagerId).add(fofoId);
1860 
                                LOGGER.debug("L3 Calling List (direct): fofoId={} -> L1={} -> L3={}",
1861 
                                        fofoId, l1AuthId, l1ManagerId);
1862 
                            } else {
1863 
                                // Case 2: L1 -> L2 -> L3
1864 
                                AuthUser l2User = authUserMap.get(l1ManagerId);
1865 
                                if (l2User != null && l3AuthIdToFofoIds.containsKey(l2User.getManagerId())) {
1866 
                                    int l3ManagerId = l2User.getManagerId();
1867 
                                    l3AuthIdToFofoIds.get(l3ManagerId).add(fofoId);
1868 
                                    LOGGER.debug("L3 Calling List: fofoId={} -> L1={} -> L2={} -> L3={}",
1869 
                                            fofoId, l1AuthId, l1ManagerId, l3ManagerId);
1870 
                                }
36210 ranu 1871 
                            }
1872 
                        }
1873 
                        break; // Found the L1 for this fofoId
1874 
                    }
1875 
                }
1876 
            }
1877 
        }
1878 
        LOGGER.info("RBM Call Target - L3 calling lists populated from RBM_L3_ESCALATION remarks");
1879 
 
35631 ranu 1880 
        // Batch fetch collection RANK map for all fofoIds (OPTIMIZED - only fetches rank, not full model)
1881 
        start = System.currentTimeMillis();
1882 
        Map<Integer, Integer> allCollectionRankMap = new HashMap<>();
1883 
        if (!allFofoIds.isEmpty()) {
1884 
            try {
1885 
                allCollectionRankMap = partnerCollectionService.getCollectionRankMap(new ArrayList<>(allFofoIds), startDate);
1886 
            } catch (ProfitMandiBusinessException e) {
1887 
                LOGGER.error("Error fetching collection rank map for all fofoIds", e);
1888 
            }
1889 
        }
1890 
        LOGGER.info("RBM Call Target - CollectionRankMap fetch (OPTIMIZED): {}ms", System.currentTimeMillis() - start);
1891 
 
35669 ranu 1892 
        // Get MTD billing data for zero billing calculation and partner counts
35631 ranu 1893 
        start = System.currentTimeMillis();
1894 
        List<RbmWeeklyBillingModel> mtdBillingData = getWeeklyBillingDataForMonth(firstOfMonth, endOfMonth);
1895 
        Set<Integer> allMtdBilledFofoIds = mtdBillingData.stream()
1896 
                .filter(RbmWeeklyBillingModel::isMtdBilled)
1897 
                .map(RbmWeeklyBillingModel::getFofoId)
1898 
                .collect(Collectors.toSet());
35669 ranu 1899 
        // Build partner count and fofoIds per RBM from mtdBillingData (same source as Today ARR page)
1900 
        Map<Integer, Set<Integer>> mtdFofoIdsByAuthId = mtdBillingData.stream()
1901 
                .filter(RbmWeeklyBillingModel::isTargetedPartner)
1902 
                .collect(Collectors.groupingBy(RbmWeeklyBillingModel::getAuthId,
1903 
                        Collectors.mapping(RbmWeeklyBillingModel::getFofoId, Collectors.toSet())));
35631 ranu 1904 
        LOGGER.info("RBM Call Target - MTD Billing fetch: {}ms", System.currentTimeMillis() - start);
1905 
 
1906 
        // Batch fetch today's remarks for all auth IDs (to calculate Value Achieved)
1907 
        start = System.currentTimeMillis();
1908 
        Map<Integer, List<PartnerCollectionRemark>> remarksByAuthId = partnerCollectionRemarkRepository
1909 
                .selectAllByAuthIdsOnDate(rbmPositionsAuthIds, LocalDate.now()).stream()
1910 
                .collect(Collectors.groupingBy(PartnerCollectionRemark::getAuthId));
1911 
        LOGGER.info("RBM Call Target - Today Remarks fetch: {}ms", System.currentTimeMillis() - start);
1912 
 
1913 
        // Batch fetch today's out-of-sequence logs for all RBMs
1914 
        start = System.currentTimeMillis();
1915 
        LocalDateTime todayStart = LocalDate.now().atStartOfDay();
1916 
        LocalDateTime todayEnd = LocalDate.now().plusDays(1).atStartOfDay();
1917 
        List<RbmCallSequenceLog> outOfSequenceLogs = rbmCallSequenceLogRepository.selectOutOfSequenceByDateRange(todayStart, todayEnd);
1918 
        Map<Integer, Long> outOfSequenceCountByAuthId = outOfSequenceLogs.stream()
35654 ranu 1919 
                .collect(Collectors.groupingBy(RbmCallSequenceLog::getAuthId,
1920 
                        Collectors.mapping(RbmCallSequenceLog::getFofoId, Collectors.collectingAndThen(Collectors.toSet(), s -> (long) s.size()))));
35631 ranu 1921 
        LOGGER.info("RBM Call Target - Out of Sequence fetch: {}ms", System.currentTimeMillis() - start);
1922 
 
36284 ranu 1923 
        // BATCH FETCH: All call logs for all RBMs (L1 + L2 + L3) in a single query.
1924 
        // Replaces the previous N+1 pattern where getCallStats() was called per RBM.
1925 
        start = System.currentTimeMillis();
1926 
        List<AgentCallLog> allCallLogs = agentCallLogRepository.findByAuthIdsAndDate(rbmPositionsAuthIds, queryDate);
1927 
        Map<Long, List<AgentCallLog>> callLogsByAuthId = allCallLogs.stream()
1928 
                .collect(Collectors.groupingBy(AgentCallLog::getAuthId));
1929 
        LOGGER.info("RBM Call Target - Call logs batch fetch: {}ms ({} logs across {} RBMs)",
1930 
                System.currentTimeMillis() - start, allCallLogs.size(), callLogsByAuthId.size());
1931 
 
1932 
        // BATCH FETCH: Build a single mobile -> fofoId map from all unique customer numbers across all call logs.
1933 
        // Replaces the previous N+1 pattern where findFofoIdByMobile() was called per call log entry.
1934 
        start = System.currentTimeMillis();
1935 
        Set<String> allNormalizedMobiles = new HashSet<>();
1936 
        for (AgentCallLog callLog : allCallLogs) {
1937 
            String customerNumber = callLog.getCustomerNumber();
1938 
            if (customerNumber != null) {
1939 
                String normalized = customerNumber.startsWith("+91") ? customerNumber.substring(3) : customerNumber;
1940 
                allNormalizedMobiles.add(normalized);
1941 
            }
1942 
        }
1943 
        Map<String, Integer> mobileToFofoIdMap = buildMobileToFofoIdMap(allNormalizedMobiles);
1944 
        LOGGER.info("RBM Call Target - Mobile→FofoId batch fetch: {}ms ({} unique mobiles, {} mapped)",
1945 
                System.currentTimeMillis() - start, allNormalizedMobiles.size(), mobileToFofoIdMap.size());
1946 
 
36225 ranu 1947 
        // Identify users who are both L1 and L2 — they will be shown only as L2
1948 
        Set<Integer> l2AuthIdSet = new HashSet<>(l2AuthIds);
1949 
 
1950 
        // Process L1 RBMs (skip users who are also L2 — their data will be merged into L2 model)
35631 ranu 1951 
        for (int rbmAuthId : l1AuthIds) {
36225 ranu 1952 
            if (l2AuthIdSet.contains(rbmAuthId)) {
1953 
                continue; // Will be handled in L2 processing with merged L1 data
1954 
            }
35631 ranu 1955 
            AuthUser authUser = authUserMap.get(rbmAuthId);
1956 
            if (authUser == null || !storeGuyMap.containsKey(authUser.getEmailId())) {
1957 
                continue;
1958 
            }
1959 
 
1960 
            List<Integer> fofoIdList = rbmToFofoIdsMap.getOrDefault(rbmAuthId, Collections.emptyList());
1961 
 
1962 
            // Check if RBM is L1 (same logic as getSummaryModel)
1963 
            List<Position> positions = positionsByAuthId.getOrDefault(authUser.getId(), Collections.emptyList());
1964 
            boolean isRBMAndL1 = positions.stream()
1965 
                    .anyMatch(position ->
1966 
                            ProfitMandiConstants.TICKET_CATEGORY_RBM == position.getCategoryId()
1967 
                                    && EscalationType.L1.equals(position.getEscalationType()));
1968 
 
1969 
            // Filter escalated partners for L1 RBMs (same logic as getSummaryModel)
1970 
            List<Integer> fofoIds = fofoIdList;
1971 
            if (isRBMAndL1) {
1972 
                Map<Integer, PartnerCollectionRemark> partnerCollectionRemarks = new HashMap<>();
1973 
                for (Integer fofoId : fofoIdList) {
1974 
                    if (allPartnerCollectionRemarks.containsKey(fofoId)) {
1975 
                        partnerCollectionRemarks.put(fofoId, allPartnerCollectionRemarks.get(fofoId));
1976 
                    }
1977 
                }
1978 
                fofoIds = partnerCollectionRemarks.entrySet().stream()
1979 
                        .filter(entry -> {
1980 
                            PartnerCollectionRemark pcrMap = entry.getValue();
1981 
                            return !(CollectionRemark.RBM_L2_ESCALATION.equals(pcrMap.getRemark())
1982 
                                    || CollectionRemark.SALES_ESCALATION.equals(pcrMap.getRemark()));
1983 
                        })
1984 
                        .map(Map.Entry::getKey)
1985 
                        .collect(Collectors.toList());
1986 
            }
1987 
 
36181 ranu 1988 
            // Filter to only external, ACTIVE or REVIVAL stores (collection plan not required)
35631 ranu 1989 
            Map<Integer, Integer> finalAllCollectionRankMap = allCollectionRankMap;
1990 
            Map<Integer, FofoStore> finalFofoStoresMap = fofoStoresMap;
1991 
            List<Integer> validFofoIds = fofoIds.stream()
1992 
                    .filter(fofoId -> {
1993 
                        FofoStore store = finalFofoStoresMap.get(fofoId);
1994 
                        if (store == null || store.isInternal()) {
1995 
                            return false;
1996 
                        }
36181 ranu 1997 
                        // Only include ACTIVE or REVIVAL partners (not Low Sale, not Disputed, not Billing Pending)
1998 
                        return ActivationType.ACTIVE.equals(store.getActivationType())
1999 
                                || ActivationType.REVIVAL.equals(store.getActivationType());
35631 ranu 2000 
                    })
2001 
                    .collect(Collectors.toList());
2002 
 
2003 
            if (validFofoIds.isEmpty()) {
2004 
                continue;
2005 
            }
2006 
 
2007 
            RbmCallTargetModel targetModel = new RbmCallTargetModel();
2008 
            targetModel.setAuthId(rbmAuthId);
2009 
            targetModel.setRbmName(authUser.getFullName());
35669 ranu 2010 
            // Use partner count from mtdBillingData (same source as Today ARR page)
2011 
            Set<Integer> mtdFofoIds = mtdFofoIdsByAuthId.getOrDefault(rbmAuthId, Collections.emptySet());
2012 
            targetModel.setPartnerCount(mtdFofoIds.size());
35631 ranu 2013 
 
2014 
            // Categorize each partner - each partner belongs to ONE category only
35665 ranu 2015 
            // Priority: PlanToday > CarryForward > ZeroBilling > Untouched > FuturePlan > Normal
36181 ranu 2016 
            // Revival is counted separately (just for display, doesn't affect categorization)
35631 ranu 2017 
            Set<Integer> planTodayPartners = new HashSet<>();
2018 
            Set<Integer> carryForwardPartners = new HashSet<>();
2019 
            Set<Integer> untouchedPartners = new HashSet<>();
2020 
            Set<Integer> zeroBillingPartners = new HashSet<>();
2021 
            Set<Integer> futurePlanPartners = new HashSet<>();
2022 
            Set<Integer> normalPartners = new HashSet<>();
36181 ranu 2023 
            Set<Integer> revivalPartners = new HashSet<>();
35631 ranu 2024 
 
2025 
            for (Integer fofoId : validFofoIds) {
2026 
                // Get collection plan rank (from optimized rank map)
2027 
                int rank = allCollectionRankMap.getOrDefault(fofoId, 5); // default to Normal if no plan
2028 
 
2029 
                // Check if partner has zero billing in MTD
2030 
                boolean hasZeroBilling = !allMtdBilledFofoIds.contains(fofoId);
2031 
 
36181 ranu 2032 
                // Count REVIVAL partners separately (just for display, doesn't affect categorization)
2033 
                FofoStore store = finalFofoStoresMap.get(fofoId);
2034 
                if (store != null && ActivationType.REVIVAL.equals(store.getActivationType())) {
2035 
                    revivalPartners.add(fofoId);
2036 
                }
2037 
 
35631 ranu 2038 
                // Assign to category based on priority
2039 
                if (rank == 1) {
2040 
                    planTodayPartners.add(fofoId);
2041 
                } else if (rank == 2) {
2042 
                    carryForwardPartners.add(fofoId);
35665 ranu 2043 
                } else if (hasZeroBilling) {
2044 
                    zeroBillingPartners.add(fofoId);
35631 ranu 2045 
                } else if (rank == 3) {
2046 
                    untouchedPartners.add(fofoId);
2047 
                } else if (rank == 4) {
2048 
                    futurePlanPartners.add(fofoId);
2049 
                } else {
2050 
                    normalPartners.add(fofoId);
2051 
                }
2052 
            }
2053 
 
2054 
            // Set counts
2055 
            targetModel.setCreditCollection(0); // Credit collection is handled in separate list
2056 
            targetModel.setPlanToday(planTodayPartners.size());
2057 
            targetModel.setCarryForward(carryForwardPartners.size());
2058 
            targetModel.setUntouched(untouchedPartners.size());
2059 
            targetModel.setZeroBilling(zeroBillingPartners.size());
2060 
            targetModel.setFuturePlan(futurePlanPartners.size());
2061 
            targetModel.setNormal(normalPartners.size());
36181 ranu 2062 
            targetModel.setRevival(revivalPartners.size());
35631 ranu 2063 
 
2064 
            // Today Target = PlanToday + CarryForward + ZeroBilling + Untouched
2065 
            // These are mutually exclusive now, so we can sum them
2066 
            long todayTarget = planTodayPartners.size() +
2067 
                    carryForwardPartners.size() + zeroBillingPartners.size() + untouchedPartners.size();
2068 
            targetModel.setTodayTargetOfCall(todayTarget);
2069 
 
2070 
            // Create set of partners in Today Target categories
2071 
            Set<Integer> todayTargetPartners = new HashSet<>();
2072 
            todayTargetPartners.addAll(planTodayPartners);
2073 
            todayTargetPartners.addAll(carryForwardPartners);
2074 
            todayTargetPartners.addAll(zeroBillingPartners);
2075 
            todayTargetPartners.addAll(untouchedPartners);
2076 
 
36284 ranu 2077 
            // Value Achieved = All distinct partners called today (from pre-fetched call logs)
37010 ranu 2078 
            List<AgentCallLog> l1Logs = callLogsByAuthId.get((long) rbmAuthId);
2079 
            long[] callStats = getCallStatsFromLogs(l1Logs, mobileToFofoIdMap);
36276 ranu 2080 
            targetModel.setValueTargetAchieved(callStats[0]);
2081 
            targetModel.setTotalRecordingCalls(callStats[1]);
2082 
            targetModel.setUniqueRecordingCalls(callStats[2]);
37022 ranu 2083 
            targetModel.setTriedUniqueCalls(computeUniqueTriedCount(l1Logs, mobileToFofoIdMap));
2084 
            targetModel.setTgtCalls(computeUniqueTriedCountForTargets(l1Logs, mobileToFofoIdMap, todayTargetPartners));
35631 ranu 2085 
 
37012 ranu 2086 
            // Keep todayRemarks for movedToFuture calculation
35843 ranu 2087 
            List<PartnerCollectionRemark> todayRemarks = remarksByAuthId.getOrDefault(rbmAuthId, Collections.emptyList());
2088 
 
35631 ranu 2089 
            // Moved to Future = Partners in Future Plan category who have a remark today
2090 
            // These are partners who were contacted today but moved to a future date
2091 
            Set<Integer> todayRemarkedFofoIds = todayRemarks.stream()
2092 
                    .map(PartnerCollectionRemark::getFofoId)
2093 
                    .collect(Collectors.toSet());
2094 
            long movedToFuture = futurePlanPartners.stream()
2095 
                    .filter(todayRemarkedFofoIds::contains)
2096 
                    .count();
2097 
            targetModel.setMovedToFuture(movedToFuture);
2098 
 
2099 
            // Set out of sequence count for this RBM
2100 
            targetModel.setOutOfSequenceCount(outOfSequenceCountByAuthId.getOrDefault(rbmAuthId, 0L));
2101 
 
2102 
            rbmCallTargetModels.add(targetModel);
2103 
        }
2104 
 
2105 
        // Process L2 RBMs (escalated ticket logic with categorization)
36225 ranu 2106 
        // For users who are both L1 and L2, merge their L1 calling target into L2 model
35631 ranu 2107 
        for (int l2AuthId : l2AuthIds) {
2108 
            AuthUser authUser = authUserMap.get(l2AuthId);
2109 
            if (authUser == null) {
2110 
                continue;
2111 
            }
2112 
 
2113 
            List<Integer> l2FofoIdList = l2AuthIdToFofoIds.getOrDefault(l2AuthId, Collections.emptyList());
2114 
 
35816 ranu 2115 
            // For L2, use unique fofoIds with RBM_L2_ESCALATION remark as target
35662 ranu 2116 
            Set<Integer> l2TargetFofoIds = new HashSet<>(l2FofoIdList);
35631 ranu 2117 
 
37022 ranu 2118 
            // Collected from the L1-merge branch below when the user is also L1. Used later
2119 
            // for the TGT Calls scope: L2 escalation + L1 target buckets.
2120 
            Set<Integer> l1MergedTargetPartners = new HashSet<>();
2121 
 
35631 ranu 2122 
            RbmCallTargetModel l2Model = new RbmCallTargetModel();
2123 
            l2Model.setAuthId(l2AuthId);
2124 
            l2Model.setRbmName(authUser.getFullName() + " (L2)");
2125 
            l2Model.setL2Position(true);
35816 ranu 2126 
            l2Model.setL2CallingList(l2TargetFofoIds.size());
36228 ranu 2127 
            // Partner count: if user is also L1, use L1 partner count (MTD targeted partners)
2128 
            if (l1AuthIds.contains(l2AuthId)) {
2129 
                Set<Integer> mtdFofoIds = mtdFofoIdsByAuthId.getOrDefault(l2AuthId, Collections.emptySet());
2130 
                l2Model.setPartnerCount(mtdFofoIds.size());
2131 
            } else {
2132 
                List<Integer> l2AssignedFofoIds = rbmToFofoIdsMap.getOrDefault(l2AuthId, Collections.emptyList());
2133 
                l2Model.setPartnerCount(l2AssignedFofoIds.size());
2134 
            }
35631 ranu 2135 
 
36229 ranu 2136 
            // If user is also L1, calculate full L1 breakdown and merge into L2 model
36225 ranu 2137 
            if (l1AuthIds.contains(l2AuthId) && storeGuyMap.containsKey(authUser.getEmailId())) {
36229 ranu 2138 
                // Get only L1 RBM position partners (not L2 partners) from partner_position
36225 ranu 2139 
                List<Position> positions = positionsByAuthId.getOrDefault(l2AuthId, Collections.emptyList());
36229 ranu 2140 
                Set<Integer> l1RbmPositionIds = positions.stream()
2141 
                        .filter(p -> ProfitMandiConstants.TICKET_CATEGORY_RBM == p.getCategoryId()
2142 
                                && EscalationType.L1.equals(p.getEscalationType()))
2143 
                        .map(Position::getId)
2144 
                        .collect(Collectors.toSet());
2145 
                // Fetch partner_position for only L1 RBM positions of this user
2146 
                List<Integer> fofoIdList = partnerPositionRepository
2147 
                        .selectByPositionIds(new ArrayList<>(l1RbmPositionIds)).stream()
2148 
                        .map(pp -> pp.getFofoId())
2149 
                        .distinct()
2150 
                        .collect(Collectors.toList());
2151 
                boolean isRBMAndL1 = !l1RbmPositionIds.isEmpty();
36225 ranu 2152 
                List<Integer> l1FofoIds = new ArrayList<>(fofoIdList);
2153 
                if (isRBMAndL1) {
2154 
                    Map<Integer, PartnerCollectionRemark> partnerCollectionRemarks = new HashMap<>();
2155 
                    for (Integer fofoId : fofoIdList) {
2156 
                        if (allPartnerCollectionRemarks.containsKey(fofoId)) {
2157 
                            partnerCollectionRemarks.put(fofoId, allPartnerCollectionRemarks.get(fofoId));
2158 
                        }
2159 
                    }
2160 
                    l1FofoIds = partnerCollectionRemarks.entrySet().stream()
2161 
                            .filter(entry -> {
2162 
                                PartnerCollectionRemark pcrMap = entry.getValue();
2163 
                                return !(CollectionRemark.RBM_L2_ESCALATION.equals(pcrMap.getRemark())
2164 
                                        || CollectionRemark.SALES_ESCALATION.equals(pcrMap.getRemark()));
2165 
                            })
2166 
                            .map(Map.Entry::getKey)
2167 
                            .collect(Collectors.toList());
2168 
                }
2169 
                Map<Integer, FofoStore> finalFofoStoresMap2 = fofoStoresMap;
2170 
                List<Integer> validL1FofoIds = l1FofoIds.stream()
2171 
                        .filter(fofoId -> {
2172 
                            FofoStore store = finalFofoStoresMap2.get(fofoId);
2173 
                            if (store == null || store.isInternal()) return false;
2174 
                            return ActivationType.ACTIVE.equals(store.getActivationType())
2175 
                                    || ActivationType.REVIVAL.equals(store.getActivationType());
2176 
                        })
2177 
                        .collect(Collectors.toList());
35631 ranu 2178 
 
36229 ranu 2179 
                // Categorize L1 partners — same logic as L1 processing
2180 
                Set<Integer> planTodayPartners = new HashSet<>();
2181 
                Set<Integer> carryForwardPartners = new HashSet<>();
2182 
                Set<Integer> untouchedPartners = new HashSet<>();
2183 
                Set<Integer> zeroBillingPartners = new HashSet<>();
2184 
                Set<Integer> futurePlanPartners = new HashSet<>();
2185 
                Set<Integer> normalPartners = new HashSet<>();
2186 
                Set<Integer> revivalPartners = new HashSet<>();
2187 
 
36225 ranu 2188 
                for (Integer fofoId : validL1FofoIds) {
2189 
                    int rank = allCollectionRankMap.getOrDefault(fofoId, 5);
2190 
                    boolean hasZeroBilling = !allMtdBilledFofoIds.contains(fofoId);
36229 ranu 2191 
                    FofoStore store = finalFofoStoresMap2.get(fofoId);
2192 
                    if (store != null && ActivationType.REVIVAL.equals(store.getActivationType())) {
2193 
                        revivalPartners.add(fofoId);
36225 ranu 2194 
                    }
36229 ranu 2195 
                    if (rank == 1) {
2196 
                        planTodayPartners.add(fofoId);
2197 
                    } else if (rank == 2) {
2198 
                        carryForwardPartners.add(fofoId);
2199 
                    } else if (hasZeroBilling) {
2200 
                        zeroBillingPartners.add(fofoId);
2201 
                    } else if (rank == 3) {
2202 
                        untouchedPartners.add(fofoId);
2203 
                    } else if (rank == 4) {
2204 
                        futurePlanPartners.add(fofoId);
2205 
                    } else {
2206 
                        normalPartners.add(fofoId);
2207 
                    }
36225 ranu 2208 
                }
36229 ranu 2209 
 
2210 
                // Set L1 breakdown fields on L2 model
2211 
                l2Model.setPlanToday(planTodayPartners.size());
2212 
                l2Model.setCarryForward(carryForwardPartners.size());
2213 
                l2Model.setZeroBilling(zeroBillingPartners.size());
2214 
                l2Model.setUntouched(untouchedPartners.size());
2215 
                l2Model.setFuturePlan(futurePlanPartners.size());
2216 
                l2Model.setNormal(normalPartners.size());
2217 
                l2Model.setRevival(revivalPartners.size());
2218 
 
2219 
                long l1OwnTarget = planTodayPartners.size() + carryForwardPartners.size()
2220 
                        + zeroBillingPartners.size() + untouchedPartners.size();
2221 
 
37022 ranu 2222 
                // Feed the L1 target sets up to the outer scope so TGT Calls can include them.
2223 
                l1MergedTargetPartners.addAll(planTodayPartners);
2224 
                l1MergedTargetPartners.addAll(carryForwardPartners);
2225 
                l1MergedTargetPartners.addAll(zeroBillingPartners);
2226 
                l1MergedTargetPartners.addAll(untouchedPartners);
2227 
 
36229 ranu 2228 
                // Today Target = own L1 target + L2 escalation
2229 
                l2Model.setTodayTargetOfCall(l2TargetFofoIds.size() + l1OwnTarget);
2230 
 
2231 
                // Moved to Future from L1 partners
2232 
                List<PartnerCollectionRemark> todayRemarks = remarksByAuthId.getOrDefault(l2AuthId, Collections.emptyList());
2233 
                Set<Integer> todayRemarkedFofoIds = todayRemarks.stream()
2234 
                        .map(PartnerCollectionRemark::getFofoId)
2235 
                        .collect(Collectors.toSet());
2236 
                long movedToFuture = futurePlanPartners.stream()
2237 
                        .filter(todayRemarkedFofoIds::contains)
2238 
                        .count();
2239 
                l2Model.setMovedToFuture(movedToFuture);
2240 
            } else {
2241 
                // Pure L2 (not also L1) — target is only L2 escalation
2242 
                l2Model.setTodayTargetOfCall(l2TargetFofoIds.size());
36225 ranu 2243 
            }
2244 
 
36284 ranu 2245 
            // Value Achieved = All distinct partners called today (from pre-fetched call logs)
37010 ranu 2246 
            List<AgentCallLog> l2Logs = callLogsByAuthId.get((long) l2AuthId);
2247 
            long[] l2CallStats = getCallStatsFromLogs(l2Logs, mobileToFofoIdMap);
36276 ranu 2248 
            l2Model.setValueTargetAchieved(l2CallStats[0]);
2249 
            l2Model.setTotalRecordingCalls(l2CallStats[1]);
2250 
            l2Model.setUniqueRecordingCalls(l2CallStats[2]);
37022 ranu 2251 
            l2Model.setTriedUniqueCalls(computeUniqueTriedCount(l2Logs, mobileToFofoIdMap));
35631 ranu 2252 
 
37022 ranu 2253 
            // TGT Calls scope: L2 escalation + (if user is also L1) the L1 target buckets.
2254 
            Set<Integer> l2TgtSet = new HashSet<>(l2TargetFofoIds);
2255 
            l2TgtSet.addAll(l1MergedTargetPartners);
2256 
            l2Model.setTgtCalls(computeUniqueTriedCountForTargets(l2Logs, mobileToFofoIdMap, l2TgtSet));
2257 
 
35631 ranu 2258 
            l2Model.setOutOfSequenceCount(outOfSequenceCountByAuthId.getOrDefault(l2AuthId, 0L));
2259 
            rbmCallTargetModels.add(l2Model);
2260 
        }
2261 
 
36210 ranu 2262 
        // Process L3 RBMs (escalated ticket logic with categorization)
2263 
        for (int l3AuthId : l3AuthIds) {
2264 
            AuthUser authUser = authUserMap.get(l3AuthId);
2265 
            if (authUser == null) {
2266 
                continue;
2267 
            }
2268 
 
2269 
            List<Integer> l3FofoIdList = l3AuthIdToFofoIds.getOrDefault(l3AuthId, Collections.emptyList());
2270 
 
2271 
            // For L3, use unique fofoIds with RBM_L3_ESCALATION remark as target
2272 
            Set<Integer> l3TargetFofoIds = new HashSet<>(l3FofoIdList);
2273 
 
2274 
            RbmCallTargetModel l3Model = new RbmCallTargetModel();
2275 
            l3Model.setAuthId(l3AuthId);
2276 
            l3Model.setRbmName(authUser.getFullName() + " (L3)");
2277 
            l3Model.setL3Position(true);
2278 
            l3Model.setL3CallingList(l3TargetFofoIds.size());
36359 ranu 2279 
            // Partner count = total assigned partners (excluding internal)
2280 
            Map<Integer, FofoStore> finalFofoStoresMapL3 = fofoStoresMap;
36210 ranu 2281 
            List<Integer> l3AssignedFofoIds = rbmToFofoIdsMap.getOrDefault(l3AuthId, Collections.emptyList());
36359 ranu 2282 
            long l3ExternalPartnerCount = l3AssignedFofoIds.stream()
2283 
                    .filter(fofoId -> {
2284 
                        FofoStore store = finalFofoStoresMapL3.get(fofoId);
2285 
                        return store != null && !store.isInternal();
2286 
                    })
2287 
                    .count();
2288 
            l3Model.setPartnerCount(l3ExternalPartnerCount);
36210 ranu 2289 
 
2290 
            // L3 Target = partners with RBM_L3_ESCALATION as latest remark
2291 
            l3Model.setTodayTargetOfCall(l3TargetFofoIds.size());
2292 
 
36284 ranu 2293 
            // Value Achieved = All distinct partners called today (from pre-fetched call logs)
37010 ranu 2294 
            List<AgentCallLog> l3Logs = callLogsByAuthId.get((long) l3AuthId);
2295 
            long[] l3CallStats = getCallStatsFromLogs(l3Logs, mobileToFofoIdMap);
36276 ranu 2296 
            l3Model.setValueTargetAchieved(l3CallStats[0]);
2297 
            l3Model.setTotalRecordingCalls(l3CallStats[1]);
2298 
            l3Model.setUniqueRecordingCalls(l3CallStats[2]);
37022 ranu 2299 
            l3Model.setTriedUniqueCalls(computeUniqueTriedCount(l3Logs, mobileToFofoIdMap));
2300 
            // L3 TGT Calls scope: L3 escalation partners.
2301 
            l3Model.setTgtCalls(computeUniqueTriedCountForTargets(l3Logs, mobileToFofoIdMap, l3TargetFofoIds));
36210 ranu 2302 
 
2303 
            l3Model.setOutOfSequenceCount(outOfSequenceCountByAuthId.getOrDefault(l3AuthId, 0L));
2304 
            rbmCallTargetModels.add(l3Model);
2305 
        }
2306 
 
36225 ranu 2307 
        // Group models by escalation level
36210 ranu 2308 
        Map<Integer, RbmCallTargetModel> l3ModelsByAuthId = new HashMap<>();
35631 ranu 2309 
        Map<Integer, RbmCallTargetModel> l2ModelsByAuthId = new HashMap<>();
2310 
        Map<Integer, RbmCallTargetModel> l1ModelsByAuthId = new HashMap<>();
2311 
        for (RbmCallTargetModel m : rbmCallTargetModels) {
36210 ranu 2312 
            if (m.isL3Position()) {
2313 
                l3ModelsByAuthId.put(m.getAuthId(), m);
2314 
            } else if (m.isL2Position()) {
35631 ranu 2315 
                l2ModelsByAuthId.put(m.getAuthId(), m);
2316 
            } else {
2317 
                l1ModelsByAuthId.put(m.getAuthId(), m);
2318 
            }
2319 
        }
2320 
 
36225 ranu 2321 
        // Build partner-based hierarchy using partner_position table
2322 
        // Map positionId -> Position for quick lookup
2323 
        Map<Integer, Position> positionByIdMap = allRbmPositions.stream()
2324 
                .collect(Collectors.toMap(Position::getId, p -> p, (a, b) -> a));
2325 
 
2326 
        // Get all RBM position IDs and fetch their partner assignments
2327 
        List<Integer> allRbmPositionIds = allRbmPositions.stream()
2328 
                .map(Position::getId).collect(Collectors.toList());
2329 
        List<com.spice.profitmandi.dao.entity.cs.PartnerPosition> allPartnerPositions =
2330 
                partnerPositionRepository.selectByPositionIds(allRbmPositionIds);
2331 
 
2332 
        // Build partnerId -> map of escalationType -> Set<authUserIds>
2333 
        // This tells us for each partner, who is their L1, L2, L3
2334 
        Map<Integer, Map<EscalationType, Set<Integer>>> partnerToAuthByLevel = new HashMap<>();
2335 
        for (com.spice.profitmandi.dao.entity.cs.PartnerPosition pp : allPartnerPositions) {
2336 
            Position pos = positionByIdMap.get(pp.getPositionId());
2337 
            if (pos != null && pos.getEscalationType() != null) {
2338 
                partnerToAuthByLevel
2339 
                        .computeIfAbsent(pp.getFofoId(), k -> new HashMap<>())
2340 
                        .computeIfAbsent(pos.getEscalationType(), k -> new HashSet<>())
2341 
                        .add(pos.getAuthUserId());
2342 
            }
2343 
        }
2344 
 
2345 
        // Build L2 -> L1 team map based on shared partners
2346 
        // If an L1 and L2 share partners (L1 at L1 level, L2 at L2 level), that L1 belongs under that L2
2347 
        Map<Integer, List<RbmCallTargetModel>> l2TeamMap = new LinkedHashMap<>();
2348 
        for (RbmCallTargetModel l2Model : l2ModelsByAuthId.values()) {
2349 
            l2TeamMap.put(l2Model.getAuthId(), new ArrayList<>());
2350 
        }
2351 
 
2352 
        // Build L3 -> L2 team map based on shared partners
36210 ranu 2353 
        Map<Integer, List<RbmCallTargetModel>> l3TeamMap = new LinkedHashMap<>();
2354 
        for (RbmCallTargetModel l3Model : l3ModelsByAuthId.values()) {
2355 
            l3TeamMap.put(l3Model.getAuthId(), new ArrayList<>());
2356 
        }
2357 
 
36225 ranu 2358 
        // For each L1, find which L2 shares the most partners -> that's their L2
2359 
        Set<Integer> addedL1AuthIds = new HashSet<>();
2360 
        for (RbmCallTargetModel l1Model : l1ModelsByAuthId.values()) {
2361 
            Map<Integer, Integer> l2SharedCount = new HashMap<>(); // l2AuthId -> shared partner count
2362 
            for (Map.Entry<Integer, Map<EscalationType, Set<Integer>>> entry : partnerToAuthByLevel.entrySet()) {
2363 
                Map<EscalationType, Set<Integer>> levelMap = entry.getValue();
2364 
                Set<Integer> l1IdsForPartner = levelMap.getOrDefault(EscalationType.L1, Collections.emptySet());
2365 
                Set<Integer> l2AuthIdsForPartner = levelMap.getOrDefault(EscalationType.L2, Collections.emptySet());
2366 
                if (l1IdsForPartner.contains(l1Model.getAuthId())) {
2367 
                    for (int l2Id : l2AuthIdsForPartner) {
2368 
                        if (l2ModelsByAuthId.containsKey(l2Id) && l2Id != l1Model.getAuthId()) {
2369 
                            l2SharedCount.merge(l2Id, 1, Integer::sum);
2370 
                        }
2371 
                    }
2372 
                }
2373 
            }
2374 
            // Assign L1 to the L2 with most shared partners
2375 
            if (!l2SharedCount.isEmpty()) {
2376 
                int bestL2 = l2SharedCount.entrySet().stream()
2377 
                        .max(Map.Entry.comparingByValue()).get().getKey();
2378 
                l2TeamMap.get(bestL2).add(l1Model);
2379 
                addedL1AuthIds.add(l1Model.getAuthId());
2380 
            }
2381 
        }
2382 
 
2383 
        // For each L2, find which L3 shares the most partners -> that's their L3
36210 ranu 2384 
        Set<Integer> addedL2AuthIds = new HashSet<>();
2385 
        for (RbmCallTargetModel l2Model : l2ModelsByAuthId.values()) {
36225 ranu 2386 
            Map<Integer, Integer> l3SharedCount = new HashMap<>();
2387 
            for (Map.Entry<Integer, Map<EscalationType, Set<Integer>>> entry : partnerToAuthByLevel.entrySet()) {
2388 
                Map<EscalationType, Set<Integer>> levelMap = entry.getValue();
2389 
                Set<Integer> l2AuthIdsForPartner = levelMap.getOrDefault(EscalationType.L2, Collections.emptySet());
2390 
                Set<Integer> l3AuthIdsForPartner = levelMap.getOrDefault(EscalationType.L3, Collections.emptySet());
2391 
                if (l2AuthIdsForPartner.contains(l2Model.getAuthId())) {
2392 
                    for (int l3Id : l3AuthIdsForPartner) {
2393 
                        if (l3ModelsByAuthId.containsKey(l3Id)) {
2394 
                            l3SharedCount.merge(l3Id, 1, Integer::sum);
2395 
                        }
2396 
                    }
2397 
                }
2398 
            }
2399 
            if (!l3SharedCount.isEmpty()) {
2400 
                int bestL3 = l3SharedCount.entrySet().stream()
2401 
                        .max(Map.Entry.comparingByValue()).get().getKey();
2402 
                l3TeamMap.get(bestL3).add(l2Model);
36210 ranu 2403 
                addedL2AuthIds.add(l2Model.getAuthId());
2404 
            }
2405 
        }
2406 
 
36225 ranu 2407 
        // For L1s not mapped to any L2, check if they map directly to an L3 via shared partners
2408 
        Map<Integer, List<RbmCallTargetModel>> l3DirectL1Map = new LinkedHashMap<>();
2409 
        for (RbmCallTargetModel l3Model : l3ModelsByAuthId.values()) {
2410 
            l3DirectL1Map.put(l3Model.getAuthId(), new ArrayList<>());
35631 ranu 2411 
        }
2412 
        for (RbmCallTargetModel l1Model : l1ModelsByAuthId.values()) {
36225 ranu 2413 
            if (addedL1AuthIds.contains(l1Model.getAuthId())) continue;
2414 
            Map<Integer, Integer> l3SharedCount = new HashMap<>();
2415 
            for (Map.Entry<Integer, Map<EscalationType, Set<Integer>>> entry : partnerToAuthByLevel.entrySet()) {
2416 
                Map<EscalationType, Set<Integer>> levelMap = entry.getValue();
2417 
                Set<Integer> l1IdsForPartner = levelMap.getOrDefault(EscalationType.L1, Collections.emptySet());
2418 
                Set<Integer> l3AuthIdsForPartner = levelMap.getOrDefault(EscalationType.L3, Collections.emptySet());
2419 
                if (l1IdsForPartner.contains(l1Model.getAuthId())) {
2420 
                    for (int l3Id : l3AuthIdsForPartner) {
2421 
                        if (l3ModelsByAuthId.containsKey(l3Id)) {
2422 
                            l3SharedCount.merge(l3Id, 1, Integer::sum);
2423 
                        }
2424 
                    }
2425 
                }
2426 
            }
2427 
            if (!l3SharedCount.isEmpty()) {
2428 
                int bestL3 = l3SharedCount.entrySet().stream()
2429 
                        .max(Map.Entry.comparingByValue()).get().getKey();
2430 
                l3DirectL1Map.get(bestL3).add(l1Model);
35631 ranu 2431 
                addedL1AuthIds.add(l1Model.getAuthId());
2432 
            }
2433 
        }
2434 
 
36225 ranu 2435 
        // Build sorted result: L3 -> L2 -> L1 (with direct L1s under L3 if no L2)
35631 ranu 2436 
        List<RbmCallTargetModel> sortedModels = new ArrayList<>();
2437 
 
36210 ranu 2438 
        List<RbmCallTargetModel> l3Sorted = new ArrayList<>(l3ModelsByAuthId.values());
2439 
        l3Sorted.sort(Comparator.comparing(RbmCallTargetModel::getRbmName));
35631 ranu 2440 
 
36210 ranu 2441 
        for (RbmCallTargetModel l3Model : l3Sorted) {
2442 
            sortedModels.add(l3Model);
2443 
            List<RbmCallTargetModel> l2Team = l3TeamMap.getOrDefault(l3Model.getAuthId(), Collections.emptyList());
2444 
            l2Team.sort(Comparator.comparing(RbmCallTargetModel::getRbmName));
2445 
            for (RbmCallTargetModel l2Model : l2Team) {
2446 
                sortedModels.add(l2Model);
2447 
                List<RbmCallTargetModel> l1Team = l2TeamMap.getOrDefault(l2Model.getAuthId(), Collections.emptyList());
2448 
                l1Team.sort(Comparator.comparing(RbmCallTargetModel::getRbmName));
2449 
                sortedModels.addAll(l1Team);
2450 
            }
36225 ranu 2451 
            // Add L1s that report directly to this L3 (no L2 in between)
2452 
            List<RbmCallTargetModel> directL1Team = l3DirectL1Map.getOrDefault(l3Model.getAuthId(), Collections.emptyList());
2453 
            directL1Team.sort(Comparator.comparing(RbmCallTargetModel::getRbmName));
2454 
            sortedModels.addAll(directL1Team);
36210 ranu 2455 
        }
2456 
 
36225 ranu 2457 
        // Add L2s not mapped to any L3
36210 ranu 2458 
        List<RbmCallTargetModel> unmappedL2 = new ArrayList<>();
2459 
        for (RbmCallTargetModel l2Model : l2ModelsByAuthId.values()) {
2460 
            if (!addedL2AuthIds.contains(l2Model.getAuthId())) {
2461 
                unmappedL2.add(l2Model);
2462 
            }
2463 
        }
2464 
        unmappedL2.sort(Comparator.comparing(RbmCallTargetModel::getRbmName));
2465 
        for (RbmCallTargetModel l2Model : unmappedL2) {
35631 ranu 2466 
            sortedModels.add(l2Model);
2467 
            List<RbmCallTargetModel> team = l2TeamMap.getOrDefault(l2Model.getAuthId(), Collections.emptyList());
2468 
            team.sort(Comparator.comparing(RbmCallTargetModel::getRbmName));
2469 
            sortedModels.addAll(team);
2470 
        }
2471 
 
36225 ranu 2472 
        // Add any L1s not mapped to any L2 or L3
35631 ranu 2473 
        List<RbmCallTargetModel> unmappedL1 = new ArrayList<>();
2474 
        for (RbmCallTargetModel m : l1ModelsByAuthId.values()) {
2475 
            if (!addedL1AuthIds.contains(m.getAuthId())) {
2476 
                unmappedL1.add(m);
2477 
            }
2478 
        }
2479 
        unmappedL1.sort(Comparator.comparing(RbmCallTargetModel::getRbmName));
2480 
        sortedModels.addAll(unmappedL1);
2481 
 
2482 
        LOGGER.info("RBM Call Target - TOTAL TIME: {}ms, RBM count: {}", System.currentTimeMillis() - methodStart, sortedModels.size());
2483 
        return sortedModels;
2484 
    }
2485 
 
35843 ranu 2486 
    /**
37011 ranu 2487 
     * Rule: count each distinct customer number this RBM attempted today, based on
37012 ranu 2488 
     * the vendor-reported customer-side outcome (AgentCallLog.customerStatus):
2489 
     *   - If ANY call to this number has customerStatus that is not "Missed"
2490 
     *     → count as 1 (customer was reached at some point).
2491 
     *   - Else (every call to this number was Missed) → count only if attempts >= 3
37011 ranu 2492 
     *     (persistence credit).
37012 ranu 2493 
     * null/empty customerStatus is treated as "not Missed" (favours counting when
2494 
     * the vendor didn't classify the outcome).
35843 ranu 2495 
     */
37022 ranu 2496 
    private long computeUniqueTriedCount(List<AgentCallLog> callLogs,
2497 
                                         Map<String, Integer> mobileToFofoIdMap) {
37010 ranu 2498 
        if (callLogs == null || callLogs.isEmpty()) return 0L;
37022 ranu 2499 
        // Group by fofoId (not raw number) — numbers that don't resolve to a partner via
2500 
        // retailer_contact/address are skipped. Once a number gets added to retailer_contact
2501 
        // it'll automatically resolve and start counting on the next page load.
2502 
        Map<Integer, List<AgentCallLog>> byFofoId = new HashMap<>();
37010 ranu 2503 
        for (AgentCallLog log : callLogs) {
2504 
            String num = log.getCustomerNumber();
2505 
            if (num == null) continue;
2506 
            String normalized = num.startsWith("+91") ? num.substring(3) : num;
37022 ranu 2507 
            Integer fofoId = mobileToFofoIdMap != null ? mobileToFofoIdMap.get(normalized) : null;
2508 
            if (fofoId == null) continue; // unknown number — excluded from Tried
2509 
            byFofoId.computeIfAbsent(fofoId, k -> new ArrayList<>()).add(log);
36284 ranu 2510 
        }
37010 ranu 2511 
        long count = 0L;
37022 ranu 2512 
        for (List<AgentCallLog> logs : byFofoId.values()) {
37012 ranu 2513 
            boolean hasNonMissed = false;
2514 
            for (AgentCallLog l : logs) {
2515 
                if (!isMissedCustomerStatus(l.getCustomerStatus())) {
2516 
                    hasNonMissed = true;
37010 ranu 2517 
                    break;
36284 ranu 2518 
                }
2519 
            }
37012 ranu 2520 
            if (hasNonMissed) count++;
2521 
            else if (logs.size() >= 3) count++;
36284 ranu 2522 
        }
37010 ranu 2523 
        return count;
36284 ranu 2524 
    }
2525 
 
2526 
    /**
36276 ranu 2527 
     * Returns call stats: [0] = called count, [1] = total recording calls, [2] = unique recording calls
2528 
     */
2529 
    public long[] getCallStats(long authId, LocalDate date) {
36234 ranu 2530 
        List<AgentCallLog> callLogs = agentCallLogRepository.findByAuthIdAndDate(authId, date);
2531 
 
35843 ranu 2532 
        if (callLogs == null || callLogs.isEmpty()) {
36276 ranu 2533 
            return new long[]{0, 0, 0};
35843 ranu 2534 
        }
2535 
 
2536 
        Set<Integer> calledFofoIds = new HashSet<>();
2537 
        Set<String> calledNumbersWithoutFofoId = new HashSet<>();
36276 ranu 2538 
        long totalRecordingCalls = 0;
2539 
        Set<String> uniqueRecordingNumbers = new HashSet<>();
35843 ranu 2540 
 
2541 
        for (AgentCallLog callLog : callLogs) {
2542 
            String customerNumber = callLog.getCustomerNumber();
2543 
            if (customerNumber != null) {
2544 
                // Normalize the phone number (remove +91 prefix if present)
2545 
                String normalized = customerNumber.startsWith("+91") ? customerNumber.substring(3) : customerNumber;
2546 
 
2547 
                // Find fofoId from retailer_contact or address
2548 
                Integer fofoId = findFofoIdByMobile(normalized);
2549 
                if (fofoId != null) {
2550 
                    calledFofoIds.add(fofoId);
2551 
                } else {
2552 
                    // Number not found in retailer_contact or address, count by distinct number
2553 
                    calledNumbersWithoutFofoId.add(normalized);
2554 
                }
36276 ranu 2555 
 
2556 
                // Count calls with recordings
2557 
                if (callLog.getRecordingUrl() != null && !callLog.getRecordingUrl().isEmpty()
2558 
                        && !"None".equalsIgnoreCase(callLog.getRecordingUrl())) {
2559 
                    totalRecordingCalls++;
2560 
                    uniqueRecordingNumbers.add(normalized);
2561 
                }
35843 ranu 2562 
            }
2563 
        }
2564 
 
2565 
        // Total called = distinct fofoIds + distinct numbers without fofoId mapping
36276 ranu 2566 
        long calledCount = calledFofoIds.size() + calledNumbersWithoutFofoId.size();
2567 
        return new long[]{calledCount, totalRecordingCalls, uniqueRecordingNumbers.size()};
35843 ranu 2568 
    }
2569 
 
35852 ranu 2570 
    @Override
2571 
    public List<List<String>> getAllCallDataByDate(LocalDate date) throws Exception {
2572 
        List<List<String>> rows = new ArrayList<>();
2573 
 
2574 
        // Add header row
2575 
        rows.add(Arrays.asList("RBM Name", "Partner Name", "Code", "Remark", "Call Status", "Call Duration", "Call Date Time", "Recording URL"));
2576 
 
2577 
        // Get all call logs for the date
2578 
        List<AgentCallLog> allCallLogs = agentCallLogRepository.findAllByDate(date);
2579 
        LOGGER.info("getAllCallDataByDate: Found {} call logs for date {}", allCallLogs.size(), date);
2580 
 
2581 
        if (allCallLogs.isEmpty()) {
2582 
            return rows;
2583 
        }
2584 
 
2585 
        // Get unique authIds from call logs
2586 
        Set<Long> authIds = allCallLogs.stream()
2587 
                .map(AgentCallLog::getAuthId)
2588 
                .collect(Collectors.toSet());
2589 
 
2590 
        // Get auth users for RBM names
2591 
        List<Integer> authIdInts = authIds.stream().map(Long::intValue).collect(Collectors.toList());
2592 
        Map<Integer, AuthUser> authUserMap = authRepository.selectByIds(authIdInts).stream()
2593 
                .collect(Collectors.toMap(AuthUser::getId, au -> au, (a, b) -> a));
2594 
 
2595 
        // Build a map of normalized customer number -> fofoId
2596 
        Map<String, Integer> customerToFofoIdMap = new HashMap<>();
2597 
        Set<String> normalizedNumbers = new HashSet<>();
2598 
 
2599 
        for (AgentCallLog callLog : allCallLogs) {
2600 
            String customerNumber = callLog.getCustomerNumber();
2601 
            if (customerNumber != null) {
2602 
                String normalized = customerNumber.startsWith("+91") ? customerNumber.substring(3) : customerNumber;
2603 
                normalizedNumbers.add(normalized);
2604 
            }
2605 
        }
2606 
 
2607 
        // For each normalized number, find fofoId
2608 
        for (String mobile : normalizedNumbers) {
2609 
            Integer fofoId = findFofoIdByMobile(mobile);
2610 
            if (fofoId != null) {
2611 
                customerToFofoIdMap.put(mobile, fofoId);
2612 
            }
2613 
        }
2614 
 
2615 
        // Get unique fofoIds for retailer lookup
2616 
        Set<Integer> fofoIds = new HashSet<>(customerToFofoIdMap.values());
2617 
        Map<Integer, CustomRetailer> retailerMap = Collections.emptyMap();
2618 
        if (!fofoIds.isEmpty()) {
2619 
            try {
2620 
                retailerMap = retailerService.getFofoRetailers(new ArrayList<>(fofoIds));
2621 
            } catch (ProfitMandiBusinessException e) {
2622 
                LOGGER.error("Error fetching fofo stores", e);
2623 
            }
2624 
        }
2625 
 
2626 
        // Get remarks for these fofoIds on this date
2627 
        Map<Integer, List<PartnerCollectionRemark>> fofoRemarkMap = new HashMap<>();
2628 
        if (!fofoIds.isEmpty()) {
2629 
            List<PartnerCollectionRemark> dateRemarks = partnerCollectionRemarkRepository
2630 
                    .selectAllByFofoIdsOnDate(new ArrayList<>(fofoIds), date);
2631 
            for (PartnerCollectionRemark remark : dateRemarks) {
2632 
                fofoRemarkMap.computeIfAbsent(remark.getFofoId(), k -> new ArrayList<>()).add(remark);
2633 
            }
2634 
        }
2635 
 
2636 
        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("dd-MM-yyyy hh:mm a");
2637 
 
2638 
        // Build rows
2639 
        for (AgentCallLog callLog : allCallLogs) {
2640 
            String customerNumber = callLog.getCustomerNumber();
2641 
            if (customerNumber == null) {
2642 
                continue;
2643 
            }
2644 
 
2645 
            // Get RBM Name
2646 
            AuthUser authUser = authUserMap.get((int) callLog.getAuthId());
2647 
            String rbmName = authUser != null ? authUser.getFullName() : "Unknown";
2648 
 
2649 
            String normalized = customerNumber.startsWith("+91") ? customerNumber.substring(3) : customerNumber;
2650 
            Integer fofoId = customerToFofoIdMap.get(normalized);
2651 
 
2652 
            String partyName = "Unknown";
2653 
            String code = "-";
2654 
 
2655 
            if (fofoId != null) {
2656 
                CustomRetailer retailer = retailerMap.get(fofoId);
2657 
                if (retailer != null) {
2658 
                    partyName = retailer.getBusinessName();
2659 
                    code = retailer.getCode();
2660 
                } else {
2661 
                    partyName = "Unknown (" + fofoId + ")";
2662 
                }
2663 
            } else {
2664 
                partyName = "Unknown (" + normalized + ")";
2665 
            }
2666 
 
2667 
            // Get remark if available
2668 
            String remarkValue = "-";
2669 
            if (fofoId != null && fofoRemarkMap.containsKey(fofoId)) {
2670 
                List<PartnerCollectionRemark> remarks = fofoRemarkMap.get(fofoId);
2671 
                if (!remarks.isEmpty()) {
2672 
                    PartnerCollectionRemark remark = remarks.get(0);
2673 
                    remarkValue = remark.getRemark() != null ? remark.getRemark().getValue() : "-";
2674 
                    if (remark.getMessage() != null && !remark.getMessage().isEmpty()) {
2675 
                        remarkValue = remarkValue + " - " + remark.getMessage();
2676 
                    }
2677 
                }
2678 
            }
2679 
 
2680 
            // Call status from customerStatus field
2681 
            String callStatus = callLog.getCustomerStatus() != null ? callLog.getCustomerStatus() : "-";
2682 
            String callDuration = callLog.getCallDuration() != null ? callLog.getCallDuration() : "-";
2683 
 
2684 
            String callDateTime = "-";
2685 
            if (callLog.getCallDate() != null && callLog.getCallTime() != null) {
2686 
                LocalDateTime callDateTimeObj = LocalDateTime.of(callLog.getCallDate(), callLog.getCallTime());
2687 
                callDateTime = callDateTimeObj.format(dateTimeFormatter);
2688 
            }
2689 
 
2690 
            String recordingUrl = callLog.getRecordingUrl() != null ? callLog.getRecordingUrl() : "-";
2691 
 
2692 
            rows.add(Arrays.asList(rbmName, partyName, code, remarkValue, callStatus, callDuration, callDateTime, recordingUrl));
2693 
        }
2694 
 
2695 
        return rows;
2696 
    }
2697 
 
33917 ranu 2698 
}