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

         out.put("range", range);
         out.put("scopeCount", authIds == null ? 0 : authIds.size());
         List<Map<String, Object>> coverage = coverageByLevel(s, start, end, auth);
         Map<String, Object> funnel = funnel(s, start, end, auth, dtr);
+        Map<String, Object> summary = journeySummary(s, start, end, dtr, funnel);
         Map<String, Object> deferral = deferral(s, start, end, auth);
         Map<String, Object> utilization = utilization(s, start, end, dtr);
         Map<String, Object> travel = travel(s, start, end, auth, dtr);
         Map<String, Object> outcomes = outcomes(s, start, end, auth);
         List<Map<String, Object>> scorecard = scorecard(s, start, end, auth, dtr);
         Map<String, Object> kpis = kpis(s, start, end, auth, dtr, coverage, funnel, deferral);
         out.put("kpis", kpis);
         out.put("funnel", funnel);
+        out.put("summary", summary);
         out.put("coverageByLevel", coverage);
         out.put("scorecard", scorecard);
         out.put("utilization", utilization);
         out.put("deferral", deferral);
         out.put("travel", travel);
     private Map<String, Object> kpis(Session s, String ms, String me, List<Integer> auth, List<Integer> dtr,
                                      List<Map<String, Object>> coverage, Map<String, Object> funnel, Map<String, Object> deferral) {
         Map<String, Object> m = new LinkedHashMap<>();
         long planned = asLong(funnel.get("plannedStops"));
+        // Adherence is planned-visit completion: the numerator counts only completed
+        // PLANNED visits (partner franchisee + approved leads), not every check-out
+        // (ad-hoc office/warehouse stops don't count toward planned-visit adherence).
-        long done = asLong(funnel.get("checkedOut"));
+        // Self-assigned stops are excluded: task_name is pipe-delimited and ends with
+        // a "… | SELF" marker for self-assigned visits, which aren't part of the plan.
+        long done = countVisits(s, ms, me, dtr, "task_type IN ('franchisee-visit','lead') AND COALESCE(task_name,'') NOT LIKE '%SELF%' AND mark_type LIKE '%CHECKOUT%'");
         m.put("plannedStops", planned);
         m.put("doneStops", done);
         m.put("adherencePct", planned == 0 ? 0 : Math.round(100.0 * done / planned));
         // L1 coverage row
             "   AND (mark_type='PUNCHIN' OR (task_id=0 AND check_in_time IS NOT NULL AND check_in_time<>'00:00:00'))");
         q.setParameter("ms", ms).setParameter("me", me).setParameterList("dtr", dtr);
         long journeys = asLong(q.uniqueResult());
         m.put("journeys", journeys);
         m.put("avgVisitsPerActiveDay", journeys == 0 ? 0 : round1((double) asLong(funnel.get("checkedIn")) / journeys));
+        // partner (franchisee) visits per active day — the subset that are partner
+        // store visits, shown alongside the all-types visit average.
+        long partnerCheckins = countVisits(s, ms, me, dtr, "task_type='franchisee-visit' AND mark_type LIKE 'CHECKIN%'");
+        m.put("avgPartnersPerActiveDay", journeys == 0 ? 0 : round1((double) partnerCheckins / journeys));
+        // lead visits per active day, split into planned (on the PJP / lead_route,
+        // no SELF marker) vs unplanned (self-assigned ad-hoc leads, task_name carries
+        // the pipe-delimited "SELF" tag).
+        long plannedLeadCheckins   = countVisits(s, ms, me, dtr, "task_type='lead' AND COALESCE(task_name,'') NOT LIKE '%SELF%' AND mark_type LIKE 'CHECKIN%'");
+        long unplannedLeadCheckins = countVisits(s, ms, me, dtr, "task_type='lead' AND COALESCE(task_name,'') LIKE '%SELF%' AND mark_type LIKE 'CHECKIN%'");
+        m.put("avgPlannedLeadsPerActiveDay",   journeys == 0 ? 0 : round1((double) plannedLeadCheckins / journeys));
+        m.put("avgUnplannedLeadsPerActiveDay", journeys == 0 ? 0 : round1((double) unplannedLeadCheckins / journeys));
-        // avg in-store discussion minutes
+        // avg in-store discussion minutes — overall, plus partner (franchisee) and
-        NativeQuery<?> dq = s.createNativeQuery(
+        // lead split so the card can show each separately.
-            "SELECT ROUND(AVG(NULLIF(TIME_TO_SEC(time_spent),0))/60,0) FROM auth.location_tracking" +
+        m.put("avgDiscussionMin", avgDiscussionMin(s, ms, me, dtr, null));
-            " WHERE task_date>=:ms AND task_date<=:me AND task_id<>0 AND mark_type LIKE '%CHECKOUT%' AND user_id IN (:dtr)");
-        dq.setParameter("ms", ms).setParameter("me", me).setParameterList("dtr", dtr);
+        m.put("avgDiscussionPartnerMin", avgDiscussionMin(s, ms, me, dtr, "franchisee-visit"));
-        m.put("avgDiscussionMin", asLong(dq.uniqueResult()));
+        m.put("avgDiscussionLeadMin", avgDiscussionMin(s, ms, me, dtr, "lead"));
+        // Auto "missed" = situations where planned execution broke down, split into
+        // three causes: execs with no PJP at all, scheduled beat-days with the agenda
+        // left unfilled, and scheduled beat-days the owner never punched in on.
+        Map<String, Object> mb = missedBreakdown(s, ms, me, auth, coverage);
+        m.put("autoMissedUnscheduled", mb.get("unscheduled"));
+        m.put("autoMissedUnfilledAgenda", mb.get("unfilledAgenda"));
+        m.put("autoMissedMissedPunchin", mb.get("missedPunchin"));
-        m.put("autoMissed", asLong(deferral.get("total")));
+        m.put("autoMissed", mb.get("total"));
         m.put("autoMissedSystemPct", asLong(deferral.get("systemPct")));
         // leads created on route
         NativeQuery<?> lq = s.createNativeQuery(
             "SELECT COUNT(*) FROM `user`.lead WHERE created_timestamp>=:ms AND created_timestamp<=CONCAT(:me,' 23:59:59') AND auth_id IN (:auth)");
             " AND user_id IN (:dtr)");
         q.setParameter("ms", ms).setParameter("me", me).setParameterList("dtr", dtr);
         return asLong(q.uniqueResult());
+    }
+    // Avg in-store discussion minutes over checked-out visits. taskType null =
+    // all visit types; otherwise restricted to that task_type (e.g. franchisee / lead).
+    private long avgDiscussionMin(Session s, String ms, String me, List<Integer> dtr, String taskType) {
+        NativeQuery<?> q = s.createNativeQuery(
+            "SELECT ROUND(AVG(TIME_TO_SEC(check_out_time)-TIME_TO_SEC(check_in_time))/60,0) FROM auth.location_tracking" +
+            " WHERE task_date>=:ms AND task_date<=:me AND task_id<>0 AND mark_type LIKE '%CHECKOUT%'" +
+            " AND check_in_time IS NOT NULL AND check_in_time<>'00:00:00'" +
+            " AND check_out_time IS NOT NULL AND check_out_time<>'00:00:00'" +
+            " AND TIME_TO_SEC(check_out_time) > TIME_TO_SEC(check_in_time)" +
+            (taskType != null ? " AND task_type=:tt" : "") +
+            " AND user_id IN (:dtr)");
+        q.setParameter("ms", ms).setParameter("me", me).setParameterList("dtr", dtr);
+        if (taskType != null) q.setParameter("tt", taskType);
+        return asLong(q.uniqueResult());
+    }
+    // ---- Journey summary table ---------------------------------------------
+    // A flat per-period scorecard split into Planned (on the PJP / lead_route, no
+    // SELF marker) vs Self Assigned (the ad-hoc "… | SELF" visits the exec added):
+    //   Total Visits   — planned = scheduled planned stops; self = self-assigned check-ins
+    //   Avg Time Spent — avg (check-out − check-in) minutes, each side
+    //   Idle/Break     — average idle minutes per active day (single value)
+    //   Deferred       — total − completed, each side (what was never closed out)
+    //   Completed      — checkouts, each side
+    private Map<String, Object> journeySummary(Session s, String ms, String me, List<Integer> dtr,
+                                               Map<String, Object> funnel) {
+        long plannedVisits   = asLong(funnel.get("plannedStops"));
+        long completedPlanned = countVisits(s, ms, me, dtr, "task_type IN ('franchisee-visit','lead') AND COALESCE(task_name,'') NOT LIKE '%SELF%' AND mark_type LIKE '%CHECKOUT%'");
+        long selfVisits      = countVisits(s, ms, me, dtr, "COALESCE(task_name,'') LIKE '%SELF%' AND mark_type LIKE 'CHECKIN%'");
+        long completedSelf   = countVisits(s, ms, me, dtr, "COALESCE(task_name,'') LIKE '%SELF%' AND mark_type LIKE '%CHECKOUT%'");
+        // idle = working − in-store − travel, averaged over active (punch-in) days
+        NativeQuery<?> uq = s.createNativeQuery(
+            "SELECT" +
+            " (SELECT COALESCE(SUM(TIME_TO_SEC(time_spent)),0) FROM auth.location_tracking WHERE task_date>=:ms AND task_date<=:me AND task_id<>0 AND user_id IN (:dtr)) instore," +
+            " (SELECT COALESCE(SUM(TIME_TO_SEC(transit_time)),0) FROM auth.location_tracking WHERE task_date>=:ms AND task_date<=:me AND task_id<>0 AND user_id IN (:dtr)) travel," +
+            " (SELECT COALESCE(SUM(GREATEST(TIME_TO_SEC(check_out_time)-TIME_TO_SEC(check_in_time),0)),0) FROM auth.location_tracking WHERE task_date>=:ms AND task_date<=:me AND task_id=0 AND check_out_time IS NOT NULL AND check_out_time<>'00:00:00' AND check_in_time IS NOT NULL AND check_in_time<>'00:00:00' AND user_id IN (:dtr)) working," +
+            " (SELECT COUNT(DISTINCT CONCAT(user_id,'-',task_date)) FROM auth.location_tracking WHERE task_date>=:ms AND task_date<=:me AND user_id IN (:dtr) AND (mark_type='PUNCHIN' OR (task_id=0 AND check_in_time IS NOT NULL AND check_in_time<>'00:00:00'))) journeys");
+        uq.setParameter("ms", ms).setParameter("me", me).setParameterList("dtr", dtr);
+        Object[] ur = (Object[]) uq.uniqueResult();
+        double instore = asDouble(ur[0]), travel = asDouble(ur[1]), working = asDouble(ur[2]);
+        long journeys = asLong(ur[3]);
+        if (working < instore + travel) working = instore + travel;
+        double idle = Math.max(working - instore - travel, 0);
+        long idleAvgMin = journeys == 0 ? 0 : Math.round(idle / journeys / 60.0);
+        Map<String, Object> m = new LinkedHashMap<>();
+        m.put("plannedVisits", plannedVisits);
+        m.put("selfVisits", selfVisits);
+        m.put("avgTimePlannedMin", avgTimeSpentMin(s, ms, me, dtr, false));
+        m.put("avgTimeSelfMin", avgTimeSpentMin(s, ms, me, dtr, true));
+        m.put("idleAvgMin", idleAvgMin);
+        m.put("deferredPlanned", Math.max(plannedVisits - completedPlanned, 0));
+        m.put("deferredSelf", Math.max(selfVisits - completedSelf, 0));
+        m.put("completedPlanned", completedPlanned);
+        m.put("completedSelf", completedSelf);
+        return m;
+    }
+    // Avg (check-out − check-in) minutes over checked-out visits, split by whether
+    // the visit carries the pipe-delimited "… | SELF" self-assigned marker.
+    private long avgTimeSpentMin(Session s, String ms, String me, List<Integer> dtr, boolean self) {
+        NativeQuery<?> q = s.createNativeQuery(
+            "SELECT ROUND(AVG(TIME_TO_SEC(check_out_time)-TIME_TO_SEC(check_in_time))/60,0) FROM auth.location_tracking" +
+            " WHERE task_date>=:ms AND task_date<=:me AND task_id<>0 AND mark_type LIKE '%CHECKOUT%'" +
+            " AND check_in_time IS NOT NULL AND check_in_time<>'00:00:00'" +
+            " AND check_out_time IS NOT NULL AND check_out_time<>'00:00:00'" +
+            " AND TIME_TO_SEC(check_out_time) > TIME_TO_SEC(check_in_time)" +
+            (self ? " AND COALESCE(task_name,'') LIKE '%SELF%'" : " AND COALESCE(task_name,'') NOT LIKE '%SELF%'") +
+            " AND user_id IN (:dtr)");
+        q.setParameter("ms", ms).setParameter("me", me).setParameterList("dtr", dtr);
+        return asLong(q.uniqueResult());
+    }
     // ---- Coverage by level --------------------------------------------------
     private List<Map<String, Object>> coverageByLevel(Session s, String ms, String me, List<Integer> auth) {
         NativeQuery<?> q = s.createNativeQuery(
             "SELECT p.escalation_type lvl, COUNT(DISTINCT p.auth_user_id) total_users," +
     private List<Map<String, Object>> scorecard(Session s, String ms, String me, List<Integer> auth, List<Integer> dtr) {
         NativeQuery<?> q = s.createNativeQuery(
             "SELECT au.id uid, TRIM(CONCAT(au.first_name,' ',COALESCE(au.last_name,''))) nm, lvl.lvl," +
             " COALESCE(pl.planned,0)+COALESCE(plL.planned,0) planned, COALESCE(ac.done,0) done," +
             " COALESCE(ds.disc_min,0) disc_min, COALESCE(wk.work_sec,0) work_sec," +
-            " COALESCE(ld.leads,0) leads, du.id duid" +
+            " COALESCE(ld.leads,0) leads, du.id duid, COALESCE(acp.donep,0) donep" +
             " FROM (SELECT DISTINCT auth_user_id FROM `user`.beat WHERE active=1 AND auth_user_id IN (:auth)) o" +
             " JOIN auth.auth_user au ON au.id=o.auth_user_id" +
             " LEFT JOIN dtr.users du ON du.email=au.email_id" +
             " LEFT JOIN (SELECT auth_user_id, MIN(escalation_type) lvl FROM cs.position WHERE category_id=4 GROUP BY auth_user_id) lvl ON lvl.auth_user_id=au.id" +
             " LEFT JOIN (SELECT b.auth_user_id uid, COUNT(*) planned FROM `user`.beat_schedule sc" +
             " LEFT JOIN (SELECT b.auth_user_id uid, COUNT(*) planned FROM `user`.beat_schedule sc" +
             "   JOIN `user`.beat b ON b.id=sc.beat_id AND b.active=1 JOIN `user`.lead_route lr ON lr.beat_id=sc.beat_id AND lr.schedule_date=sc.start_date AND lr.status='APPROVED'" +
             "   WHERE sc.start_date>=:ms AND sc.start_date<=:me GROUP BY b.auth_user_id) plL ON plL.uid=au.id" +
             " LEFT JOIN (SELECT user_id uid, COUNT(*) done FROM auth.location_tracking" +
             "   WHERE task_date>=:ms AND task_date<=:me AND task_id<>0 AND mark_type LIKE 'CHECKIN%' GROUP BY user_id) ac ON ac.uid=du.id" +
+            // Adherence numerator: completed PLANNED visits only (partner franchisee +
+            // approved leads), excluding ad-hoc office/warehouse stops and self-assigned
+            // stops (task_name carries a "… | SELF" marker, which aren't part of the plan).
+            " LEFT JOIN (SELECT user_id uid, COUNT(*) donep FROM auth.location_tracking" +
+            "   WHERE task_date>=:ms AND task_date<=:me AND task_id<>0 AND task_type IN ('franchisee-visit','lead') AND COALESCE(task_name,'') NOT LIKE '%SELF%' AND mark_type LIKE '%CHECKOUT%' GROUP BY user_id) acp ON acp.uid=du.id" +
             " LEFT JOIN (SELECT user_id uid, ROUND(AVG(NULLIF(TIME_TO_SEC(time_spent),0))/60,0) disc_min FROM auth.location_tracking" +
             "   WHERE task_date>=:ms AND task_date<=:me AND task_id<>0 AND mark_type LIKE '%CHECKOUT%' GROUP BY user_id) ds ON ds.uid=du.id" +
             " LEFT JOIN (SELECT user_id uid, SUM(GREATEST(TIME_TO_SEC(check_out_time)-TIME_TO_SEC(check_in_time),0)) work_sec FROM auth.location_tracking" +
             "   WHERE task_date>=:ms AND task_date<=:me AND task_id=0 AND check_out_time IS NOT NULL AND check_out_time<>'00:00:00' AND check_in_time IS NOT NULL AND check_in_time<>'00:00:00' GROUP BY user_id) wk ON wk.uid=du.id" +
             " LEFT JOIN (SELECT auth_id uid, COUNT(*) leads FROM `user`.lead WHERE created_timestamp>=:ms AND created_timestamp<=CONCAT(:me,' 23:59:59') GROUP BY auth_id) ld ON ld.uid=au.id" +
         Map<Integer, Integer> geoByUser = geoOffsiteByUser(s, ms, me, dtr);
         List<Map<String, Object>> list = new ArrayList<>();
         for (Object o : q.getResultList()) {
             Object[] r = (Object[]) o;
-            long planned = asLong(r[3]), done = asLong(r[4]);
+            long planned = asLong(r[3]), done = asLong(r[4]), donePlanned = asLong(r[9]);
             Integer duid = (r[8] == null) ? null : ((Number) r[8]).intValue();
             Map<String, Object> m = new LinkedHashMap<>();
             m.put("authUserId", asLong(r[0]));
             m.put("userId", duid == null ? 0 : duid);
             m.put("name", asStr(r[1]));
             m.put("level", r[2] == null ? "-" : asStr(r[2]));
             m.put("planned", planned);
             m.put("done", done);
+            // Adherence = completed PLANNED visits ÷ planned visits (not all visits).
-            m.put("adherencePct", planned == 0 ? 0 : Math.round(100.0 * done / planned));
+            m.put("adherencePct", planned == 0 ? 0 : Math.round(100.0 * donePlanned / planned));
             m.put("discussionMin", asLong(r[5]));
             m.put("workingHrs", round1(asDouble(r[6]) / 3600.0));
             m.put("leads", asLong(r[7]));
             m.put("geoFlags", duid == null ? 0 : geoByUser.getOrDefault(duid, 0));
             list.add(m);
         m.put("systemPct", total == 0 ? 0 : Math.round(100.0 * system / total));
         m.put("humanPct", total == 0 ? 0 : Math.round(100.0 * human / total));
         return m;
+    }
+    // ---- Auto "missed" breakdown -------------------------------------------
+    // Three distinct execution gaps behind the "Auto missed" card:
+    //   unscheduled    — executives (sales positions) with no PJP at all in range
+    //   unfilledAgenda — scheduled beat-days whose agenda was never filled
+    //   missedPunchin  — scheduled beat-days the owner never punched in on
+    private Map<String, Object> missedBreakdown(Session s, String ms, String me,
+                                                List<Integer> auth, List<Map<String, Object>> coverage) {
+        // unscheduled execs: total at each level minus those with a plan (reuse coverage)
+        long unscheduled = 0;
+        for (Map<String, Object> c : coverage) {
+            unscheduled += Math.max(asLong(c.get("totalUsers")) - asLong(c.get("withPjp")), 0);
+        }
+        NativeQuery<?> ua = s.createNativeQuery(
+            "SELECT COUNT(*) FROM `user`.beat_schedule sc" +
+            " JOIN `user`.beat b ON b.id=sc.beat_id AND b.active=1 AND b.auth_user_id IN (:auth)" +
+            " WHERE sc.start_date>=:ms AND sc.start_date<=:me AND sc.agenda_filled_timestamp IS NULL");
+        ua.setParameter("ms", ms).setParameter("me", me).setParameterList("auth", auth);
+        long unfilledAgenda = asLong(ua.uniqueResult());
+        NativeQuery<?> mp = s.createNativeQuery(
+            "SELECT COUNT(*) FROM `user`.beat_schedule sc" +
+            " JOIN `user`.beat b ON b.id=sc.beat_id AND b.active=1 AND b.auth_user_id IN (:auth)" +
+            " JOIN auth.auth_user au ON au.id=b.auth_user_id" +
+            " JOIN dtr.users du ON du.email=au.email_id" +
+            " WHERE sc.start_date>=:ms AND sc.start_date<=:me AND NOT EXISTS (" +
+            "   SELECT 1 FROM auth.location_tracking lt WHERE lt.user_id=du.id AND lt.task_date=sc.start_date" +
+            "     AND (lt.mark_type='PUNCHIN' OR (lt.task_id=0 AND lt.check_in_time IS NOT NULL AND lt.check_in_time<>'00:00:00')))");
+        mp.setParameter("ms", ms).setParameter("me", me).setParameterList("auth", auth);
+        long missedPunchin = asLong(mp.uniqueResult());
+        Map<String, Object> m = new LinkedHashMap<>();
+        m.put("unscheduled", unscheduled);
+        m.put("unfilledAgenda", unfilledAgenda);
+        m.put("missedPunchin", missedPunchin);
+        m.put("total", unscheduled + unfilledAgenda + missedPunchin);
+        return m;
+    }
     // ---- Drill-down lists ---------------------------------------------------
     // Per-executive PJP coverage (with/without a plan in range) — powers the
     // PJP coverage KPI and the coverage-by-level bar drill-downs.
     private List<Map<String, Object>> coverageDetail(Session s, String ms, String me, List<Integer> auth) {

Subversion Repositories SmartDukaan

(root)/trunk/profitmandi-dao/src/main/java/com/spice/profitmandi/dao/service/BeatJourneyServiceImpl.java – Rev 36913 → 36929