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

         out.put("utilization", utilization);
         out.put("deferral", deferral);
         out.put("travel", travel);
         out.put("outcomes", outcomes);
         out.put("findings", findings(kpis, funnel, deferral, utilization, travel, outcomes, coverage, scorecard));
+        // drill-down lists
+        out.put("coverageDetail", coverageDetail(s, start, end, auth));
+        out.put("deferralsList", deferralsList(s, start, end, auth));
+        out.put("leadsList", leadsList(s, start, end, auth));
         return out;
+    }
     // ---- KPIs ---------------------------------------------------------------
         m.put("coverageL1With", l1With);
         m.put("coverageL1Total", l1Total);
         m.put("coverageL1Pct", l1Total == 0 ? 0 : Math.round(100.0 * l1With / l1Total));
         // avg visits / active (punched-in) day, and total punch-in days (journeys)
+        // A "punch-in day" is the attendance row (task_id=0) with a real check-in
+        // time, matching the Today lens. (Literal mark_type='PUNCHIN' rows barely
+        // exist in the data, so relying on them under-counts drastically.)
         NativeQuery<?> q = s.createNativeQuery(
             "SELECT COUNT(DISTINCT CONCAT(user_id,'-',task_date)) FROM auth.location_tracking" +
-            " WHERE task_date>=:ms AND task_date<=:me AND mark_type='PUNCHIN' AND user_id IN (:dtr)");
+            " 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'))");
         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));
         // avg in-store discussion minutes
         NativeQuery<?> dq = s.createNativeQuery(
             "SELECT ROUND(AVG(NULLIF(TIME_TO_SEC(time_spent),0))/60,0) FROM auth.location_tracking" +
-            " WHERE task_date>=:ms AND task_date<=:me AND task_type='franchisee-visit' AND mark_type LIKE '%CHECKOUT%' AND user_id IN (:dtr)");
+            " 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("avgDiscussionMin", asLong(dq.uniqueResult()));
         m.put("autoMissed", asLong(deferral.get("total")));
         m.put("autoMissedSystemPct", asLong(deferral.get("systemPct")));
     // ---- Funnel -------------------------------------------------------------
     private Map<String, Object> funnel(Session s, String ms, String me, List<Integer> auth, List<Integer> dtr) {
         Map<String, Object> m = new LinkedHashMap<>();
-        // planned stops = scheduled beat-days × their routed stops
+        // Planned stops = partner stops (beat_route) + lead stops (lead_route, APPROVED)
-        NativeQuery<?> pq = s.createNativeQuery(
-            "SELECT COUNT(*) FROM `user`.beat_schedule sc" +
-            " JOIN `user`.beat b ON b.id=sc.beat_id AND b.auth_user_id IN (:auth)" +
+        // on each scheduled beat-day, over active beats — exactly how the Today lens
-            " JOIN `user`.beat_route r ON r.beat_id=sc.beat_id AND r.day_number=sc.day_number AND r.active=1" +
+        // (getAllScheduledBeats) counts, so Period reconciles with the day views.
-            " WHERE sc.start_date>=:ms AND sc.start_date<=:me");
+        long planned = plannedCount(s, ms, me, auth, dtr, false, false)
-        pq.setParameter("ms", ms).setParameter("me", me).setParameterList("auth", auth);
+                     + plannedCount(s, ms, me, auth, dtr, true, false);
-        long planned = asLong(pq.uniqueResult());
-        // planned stops that fall on a day the owner actually punched in
+        // ...of those, the planned stops that fall on a day the owner punched in
-        NativeQuery<?> piq = s.createNativeQuery(
-            "SELECT COUNT(*) FROM `user`.beat_schedule sc" +
-            " JOIN `user`.beat b ON b.id=sc.beat_id AND b.auth_user_id IN (:auth)" +
-            " JOIN `user`.beat_route r ON r.beat_id=sc.beat_id AND r.day_number=sc.day_number AND r.active=1" +
-            " JOIN auth.auth_user au ON au.id=b.auth_user_id" +
+        long punchedIn = plannedCount(s, ms, me, auth, dtr, false, true)
-            " JOIN dtr.users du ON du.email=au.email_id" +
-            " JOIN (SELECT DISTINCT user_id, task_date FROM auth.location_tracking" +
-            "       WHERE task_date>=:ms AND task_date<=:me AND mark_type='PUNCHIN' AND user_id IN (:dtr)) p" +
-            "   ON p.user_id=du.id AND p.task_date=sc.start_date" +
-            " WHERE sc.start_date>=:ms AND sc.start_date<=:me");
+                       + plannedCount(s, ms, me, auth, dtr, true, true);
-        piq.setParameter("ms", ms).setParameter("me", me).setParameterList("auth", auth).setParameterList("dtr", dtr);
-        long punchedIn = asLong(piq.uniqueResult());
         long checkedIn = countVisits(s, ms, me, dtr, "mark_type LIKE 'CHECKIN%'");
         long checkedOut = countVisits(s, ms, me, dtr, "(mark_type LIKE '%CHECKOUT%')");
         m.put("plannedStops", planned);
         m.put("dropCheckin", punchedIn == 0 ? 0 : Math.round(100.0 * (punchedIn - checkedIn) / punchedIn));
         m.put("dropCheckout", checkedIn == 0 ? 0 : Math.round(100.0 * (checkedIn - checkedOut) / checkedIn));
         return m;
+    }
+    // Planned stop count over scheduled beat-days of active beats. lead=false counts
+    // partner stops (beat_route by day_number); lead=true counts approved lead stops
+    // (lead_route by schedule_date). onlyPunchInDays restricts to days the owner
+    // actually punched in (the funnel's "planned on punch-in days" stage).
+    private long plannedCount(Session s, String ms, String me, List<Integer> auth,
+                              List<Integer> dtr, boolean lead, boolean onlyPunchInDays) {
+        String stopJoin = lead
+            ? " JOIN `user`.lead_route x ON x.beat_id=sc.beat_id AND x.schedule_date=sc.start_date AND x.status='APPROVED'"
+            : " JOIN `user`.beat_route x ON x.beat_id=sc.beat_id AND x.day_number=sc.day_number";
+        String punchJoin = onlyPunchInDays
+            ? " JOIN auth.auth_user au ON au.id=b.auth_user_id" +
+              " JOIN dtr.users du ON du.email=au.email_id" +
+              " JOIN (SELECT DISTINCT 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'))) p" +
+              "   ON p.user_id=du.id AND p.task_date=sc.start_date"
+            : "";
+        NativeQuery<?> q = 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)" +
+            stopJoin + punchJoin +
+            " WHERE sc.start_date>=:ms AND sc.start_date<=:me");
+        q.setParameter("ms", ms).setParameter("me", me).setParameterList("auth", auth);
+        if (onlyPunchInDays) q.setParameterList("dtr", dtr);
+        return asLong(q.uniqueResult());
+    }
+    // Counts visits across ALL task types (franchisee + lead + office) — task_id<>0
+    // excludes the attendance/punch row — matching the Today lens, which tallies
+    // every check-in/out regardless of type.
     private long countVisits(Session s, String ms, String me, List<Integer> dtr, String markPredicate) {
         NativeQuery<?> q = s.createNativeQuery(
             "SELECT COUNT(*) FROM auth.location_tracking" +
-            " WHERE task_date>=:ms AND task_date<=:me AND task_type='franchisee-visit' AND " + markPredicate +
+            " WHERE task_date>=:ms AND task_date<=:me AND task_id<>0 AND " + markPredicate +
             " AND user_id IN (:dtr)");
         q.setParameter("ms", ms).setParameter("me", me).setParameterList("dtr", dtr);
         return asLong(q.uniqueResult());
+    }
     // ---- Per-executive scorecard -------------------------------------------
     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) planned, COALESCE(ac.done,0) done," +
+            " 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" +
             " 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" +
-            "   JOIN `user`.beat b ON b.id=sc.beat_id JOIN `user`.beat_route r ON r.beat_id=sc.beat_id AND r.day_number=sc.day_number AND r.active=1" +
+            "   JOIN `user`.beat b ON b.id=sc.beat_id AND b.active=1 JOIN `user`.beat_route r ON r.beat_id=sc.beat_id AND r.day_number=sc.day_number" +
             "   WHERE sc.start_date>=:ms AND sc.start_date<=:me GROUP BY b.auth_user_id) pl ON pl.uid=au.id" +
+            " 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_type='franchisee-visit' AND mark_type LIKE 'CHECKIN%' GROUP BY user_id) ac ON ac.uid=du.id" +
+            "   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" +
             " 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_type='franchisee-visit' AND mark_type LIKE '%CHECKOUT%' GROUP BY user_id) ds ON ds.uid=du.id" +
+            "   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" +
             " ORDER BY done DESC, planned DESC");
         q.setParameter("ms", ms).setParameter("me", me).setParameterList("auth", auth);
         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]);
+            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);
             m.put("adherencePct", planned == 0 ? 0 : Math.round(100.0 * done / planned));
             m.put("discussionMin", asLong(r[5]));
             m.put("workingHrs", round1(asDouble(r[6]) / 3600.0));
             m.put("leads", asLong(r[7]));
-            Integer duid = (r[8] == null) ? null : ((Number) r[8]).intValue();
             m.put("geoFlags", duid == null ? 0 : geoByUser.getOrDefault(duid, 0));
             list.add(m);
+        }
         return list;
+    }
     // ---- Working-hour utilization ------------------------------------------
     private Map<String, Object> utilization(Session s, String ms, String me, List<Integer> dtr) {
         NativeQuery<?> q = 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_type='franchisee-visit' AND user_id IN (:dtr)) instore," +
+            " (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_type='franchisee-visit' AND user_id IN (:dtr)) travel," +
+            " (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");
         q.setParameter("ms", ms).setParameter("me", me).setParameterList("dtr", dtr);
         Object[] r = (Object[]) q.uniqueResult();
         double instore = asDouble(r[0]), travel = asDouble(r[1]), working = asDouble(r[2]);
         if (working < instore + travel) working = instore + travel; // guard: working must cover its parts
         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;
+    }
+    // ---- 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) {
+        NativeQuery<?> q = s.createNativeQuery(
+            "SELECT au.id uid, TRIM(CONCAT(au.first_name,' ',COALESCE(au.last_name,''))) nm, p.lvl," +
+            " MAX(CASE WHEN sc.id IS NOT NULL THEN 1 ELSE 0 END) has_plan" +
+            " FROM (SELECT auth_user_id, MIN(escalation_type) lvl FROM cs.position WHERE category_id=4 AND auth_user_id IN (:auth) GROUP BY auth_user_id) p" +
+            " JOIN auth.auth_user au ON au.id=p.auth_user_id" +
+            " LEFT JOIN `user`.beat b ON b.auth_user_id=p.auth_user_id AND b.active=1" +
+            " LEFT JOIN `user`.beat_schedule sc ON sc.beat_id=b.id AND sc.start_date>=:ms AND sc.start_date<=:me" +
+            " GROUP BY au.id, nm, p.lvl ORDER BY p.lvl, nm");
+        q.setParameter("ms", ms).setParameter("me", me).setParameterList("auth", auth);
+        List<Map<String, Object>> list = new ArrayList<>();
+        for (Object o : q.getResultList()) {
+            Object[] r = (Object[]) o;
+            Map<String, Object> m = new LinkedHashMap<>();
+            m.put("authUserId", asLong(r[0]));
+            m.put("name", asStr(r[1]));
+            m.put("level", asStr(r[2]));
+            m.put("hasPlan", asLong(r[3]) > 0);
+            list.add(m);
+        }
+        return list;
+    }
+    // Every deferral in range (exec, partner, reason, date, status, system flag).
+    private List<Map<String, Object>> deferralsList(Session s, String ms, String me, List<Integer> auth) {
+        NativeQuery<?> q = s.createNativeQuery(
+            "SELECT TRIM(CONCAT(au.first_name,' ',COALESCE(au.last_name,''))) nm, d.display_name, d.reason," +
+            " d.deferred_date, d.status, d.task_type," +
+            " (d.reason LIKE 'Beat missed%' OR d.reason LIKE 'Not marked%') sys" +
+            " FROM `user`.beat_deferred_visit d JOIN auth.auth_user au ON au.id=d.auth_user_id" +
+            " WHERE d.deferred_date>=:ms AND d.deferred_date<=:me AND d.auth_user_id IN (:auth)" +
+            " ORDER BY d.deferred_date, nm");
+        q.setParameter("ms", ms).setParameter("me", me).setParameterList("auth", auth);
+        List<Map<String, Object>> list = new ArrayList<>();
+        for (Object o : q.getResultList()) {
+            Object[] r = (Object[]) o;
+            Map<String, Object> m = new LinkedHashMap<>();
+            m.put("exec", asStr(r[0]));
+            m.put("partner", asStr(r[1]));
+            m.put("reason", asStr(r[2]));
+            m.put("date", asStr(r[3]));
+            m.put("status", asStr(r[4]));
+            m.put("taskType", asStr(r[5]));
+            m.put("system", asLong(r[6]) > 0);
+            list.add(m);
+        }
+        return list;
+    }
+    // Every lead created in range (exec, outlet, city, date, status).
+    private List<Map<String, Object>> leadsList(Session s, String ms, String me, List<Integer> auth) {
+        NativeQuery<?> q = s.createNativeQuery(
+            "SELECT TRIM(CONCAT(au.first_name,' ',COALESCE(au.last_name,''))) nm," +
+            " COALESCE(NULLIF(l.outlet_name,''), TRIM(CONCAT(COALESCE(l.first_name,''),' ',COALESCE(l.last_name,'')))) lead," +
+            " l.city, DATE(l.created_timestamp) dt, l.status" +
+            " FROM `user`.lead l JOIN auth.auth_user au ON au.id=l.auth_id" +
+            " WHERE l.created_timestamp>=:ms AND l.created_timestamp<=CONCAT(:me,' 23:59:59') AND l.auth_id IN (:auth)" +
+            " ORDER BY dt, nm");
+        q.setParameter("ms", ms).setParameter("me", me).setParameterList("auth", auth);
+        List<Map<String, Object>> list = new ArrayList<>();
+        for (Object o : q.getResultList()) {
+            Object[] r = (Object[]) o;
+            Map<String, Object> m = new LinkedHashMap<>();
+            m.put("exec", asStr(r[0]));
+            m.put("lead", asStr(r[1]));
+            m.put("city", asStr(r[2]));
+            m.put("date", asStr(r[3]));
+            m.put("status", asStr(r[4]));
+            list.add(m);
+        }
+        return list;
+    }
     // ---- Travel & route quality --------------------------------------------
     private Map<String, Object> travel(Session s, String ms, String me, List<Integer> auth, List<Integer> dtr) {
         Map<String, Object> m = new LinkedHashMap<>();

Subversion Repositories SmartDukaan

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