| Line 309... |
Line 309... |
| 309 |
private List<Map<String, Object>> scorecard(Session s, String ms, String me, List<Integer> auth, List<Integer> dtr) {
|
309 |
private List<Map<String, Object>> scorecard(Session s, String ms, String me, List<Integer> auth, List<Integer> dtr) {
|
| 310 |
NativeQuery<?> q = s.createNativeQuery(
|
310 |
NativeQuery<?> q = s.createNativeQuery(
|
| 311 |
"SELECT au.id uid, TRIM(CONCAT(au.first_name,' ',COALESCE(au.last_name,''))) nm, lvl.lvl," +
|
311 |
"SELECT au.id uid, TRIM(CONCAT(au.first_name,' ',COALESCE(au.last_name,''))) nm, lvl.lvl," +
|
| 312 |
" COALESCE(pl.planned,0)+COALESCE(plL.planned,0) planned, COALESCE(ac.done,0) done," +
|
312 |
" COALESCE(pl.planned,0)+COALESCE(plL.planned,0) planned, COALESCE(ac.done,0) done," +
|
| 313 |
" COALESCE(ds.disc_min,0) disc_min, COALESCE(wk.work_sec,0) work_sec," +
|
313 |
" COALESCE(ds.disc_min,0) disc_min, COALESCE(wk.work_sec,0) work_sec," +
|
| 314 |
" COALESCE(ld.leads,0) leads, du.id duid, COALESCE(acp.donep,0) donep" +
|
314 |
" COALESCE(ld.leads,0) leads, du.id duid, COALESCE(acp.donep,0) donep," +
|
| - |
|
315 |
// Active days + lead / franchisee check-ins, for the per-active-day
|
| - |
|
316 |
// visit averages (same definitions as the Period KPI cards).
|
| - |
|
317 |
" COALESCE(ad.days,0) act_days, COALESCE(lv.lvis,0) lvis, COALESCE(fv.fvis,0) fvis" +
|
| 315 |
" FROM (SELECT DISTINCT auth_user_id FROM `user`.beat WHERE active=1 AND auth_user_id IN (:auth)) o" +
|
318 |
" FROM (SELECT DISTINCT auth_user_id FROM `user`.beat WHERE active=1 AND auth_user_id IN (:auth)) o" +
|
| 316 |
" JOIN auth.auth_user au ON au.id=o.auth_user_id" +
|
319 |
" JOIN auth.auth_user au ON au.id=o.auth_user_id" +
|
| 317 |
" LEFT JOIN dtr.users du ON du.email=au.email_id" +
|
320 |
" LEFT JOIN dtr.users du ON du.email=au.email_id" +
|
| 318 |
" LEFT JOIN (SELECT auth_user_id, MIN(escalation_type) lvl FROM cs.position WHERE category_id=4 AND LOWER(escalation_type) NOT IN ('l4','l5','final') GROUP BY auth_user_id) lvl ON lvl.auth_user_id=au.id" +
|
321 |
" LEFT JOIN (SELECT auth_user_id, MIN(escalation_type) lvl FROM cs.position WHERE category_id=4 AND LOWER(escalation_type) NOT IN ('l4','l5','final') GROUP BY auth_user_id) lvl ON lvl.auth_user_id=au.id" +
|
| 319 |
" LEFT JOIN (SELECT b.auth_user_id uid, COUNT(*) planned FROM `user`.beat_schedule sc" +
|
322 |
" LEFT JOIN (SELECT b.auth_user_id uid, COUNT(*) planned FROM `user`.beat_schedule sc" +
|
| Line 332... |
Line 335... |
| 332 |
" LEFT JOIN (SELECT user_id uid, ROUND(AVG(NULLIF(TIME_TO_SEC(time_spent),0))/60,0) disc_min FROM auth.location_tracking" +
|
335 |
" LEFT JOIN (SELECT user_id uid, ROUND(AVG(NULLIF(TIME_TO_SEC(time_spent),0))/60,0) disc_min FROM auth.location_tracking" +
|
| 333 |
" 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" +
|
336 |
" 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" +
|
| 334 |
" 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" +
|
337 |
" 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" +
|
| 335 |
" 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" +
|
338 |
" 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" +
|
| 336 |
" 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" +
|
339 |
" 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" +
|
| - |
|
340 |
// active (punched-in) days — same rule as the KPI 'journeys' count.
|
| - |
|
341 |
" LEFT JOIN (SELECT user_id uid, COUNT(DISTINCT task_date) days FROM auth.location_tracking" +
|
| - |
|
342 |
" WHERE task_date>=:ms AND task_date<=:me AND (mark_type='PUNCHIN' OR (task_id=0 AND check_in_time IS NOT NULL AND check_in_time<>'00:00:00')) GROUP BY user_id) ad ON ad.uid=du.id" +
|
| - |
|
343 |
" LEFT JOIN (SELECT user_id uid, COUNT(*) lvis FROM auth.location_tracking" +
|
| - |
|
344 |
" WHERE task_date>=:ms AND task_date<=:me AND task_id<>0 AND task_type='lead' AND mark_type LIKE 'CHECKIN%' GROUP BY user_id) lv ON lv.uid=du.id" +
|
| - |
|
345 |
" LEFT JOIN (SELECT user_id uid, COUNT(*) fvis FROM auth.location_tracking" +
|
| - |
|
346 |
" WHERE task_date>=:ms AND task_date<=:me AND task_id<>0 AND task_type='franchisee-visit' AND mark_type LIKE 'CHECKIN%' GROUP BY user_id) fv ON fv.uid=du.id" +
|
| 337 |
" ORDER BY done DESC, planned DESC");
|
347 |
" ORDER BY done DESC, planned DESC");
|
| 338 |
q.setParameter("ms", ms).setParameter("me", me).setParameterList("auth", auth);
|
348 |
q.setParameter("ms", ms).setParameter("me", me).setParameterList("auth", auth);
|
| 339 |
|
349 |
|
| 340 |
// Geofence-offsite check-ins per user are computed in Java (not SQL): the
|
350 |
// Geofence-offsite check-ins per user are computed in Java (not SQL): the
|
| 341 |
// coords are stored as "lat,lng" strings, and the inline parsing/haversine
|
351 |
// coords are stored as "lat,lng" strings, and the inline parsing/haversine
|
| Line 357... |
Line 367... |
| 357 |
// Adherence = completed PLANNED visits ÷ planned visits (not all visits).
|
367 |
// Adherence = completed PLANNED visits ÷ planned visits (not all visits).
|
| 358 |
m.put("adherencePct", planned == 0 ? 0 : Math.round(100.0 * donePlanned / planned));
|
368 |
m.put("adherencePct", planned == 0 ? 0 : Math.round(100.0 * donePlanned / planned));
|
| 359 |
m.put("discussionMin", asLong(r[5]));
|
369 |
m.put("discussionMin", asLong(r[5]));
|
| 360 |
m.put("workingHrs", round1(asDouble(r[6]) / 3600.0));
|
370 |
m.put("workingHrs", round1(asDouble(r[6]) / 3600.0));
|
| 361 |
m.put("leads", asLong(r[7]));
|
371 |
m.put("leads", asLong(r[7]));
|
| - |
|
372 |
// avg lead / franchisee visits per active (punched-in) day
|
| - |
|
373 |
long actDays = asLong(r[10]);
|
| - |
|
374 |
m.put("avgLeadVisitsPerDay", actDays == 0 ? 0 : round1((double) asLong(r[11]) / actDays));
|
| - |
|
375 |
m.put("avgFranchiseeVisitsPerDay", actDays == 0 ? 0 : round1((double) asLong(r[12]) / actDays));
|
| 362 |
m.put("geoFlags", duid == null ? 0 : geoByUser.getOrDefault(duid, 0));
|
376 |
m.put("geoFlags", duid == null ? 0 : geoByUser.getOrDefault(duid, 0));
|
| 363 |
list.add(m);
|
377 |
list.add(m);
|
| 364 |
}
|
378 |
}
|
| 365 |
return list;
|
379 |
return list;
|
| 366 |
}
|
380 |
}
|