| Line 289... |
Line 289... |
| 289 |
"SELECT p.escalation_type lvl, COUNT(DISTINCT p.auth_user_id) total_users," +
|
289 |
"SELECT p.escalation_type lvl, COUNT(DISTINCT p.auth_user_id) total_users," +
|
| 290 |
" COUNT(DISTINCT CASE WHEN sc.id IS NOT NULL THEN p.auth_user_id END) with_pjp" +
|
290 |
" COUNT(DISTINCT CASE WHEN sc.id IS NOT NULL THEN p.auth_user_id END) with_pjp" +
|
| 291 |
" FROM cs.position p" +
|
291 |
" FROM cs.position p" +
|
| 292 |
" LEFT JOIN `user`.beat b ON b.auth_user_id=p.auth_user_id AND b.active=1" +
|
292 |
" LEFT JOIN `user`.beat b ON b.auth_user_id=p.auth_user_id AND b.active=1" +
|
| 293 |
" LEFT JOIN `user`.beat_schedule sc ON sc.beat_id=b.id AND sc.start_date>=:ms AND sc.start_date<=:me" +
|
293 |
" LEFT JOIN `user`.beat_schedule sc ON sc.beat_id=b.id AND sc.start_date>=:ms AND sc.start_date<=:me" +
|
| 294 |
" WHERE p.category_id=4 AND p.auth_user_id IN (:auth) GROUP BY p.escalation_type ORDER BY p.escalation_type");
|
294 |
" WHERE p.category_id=4 AND p.auth_user_id IN (:auth) AND LOWER(p.escalation_type) NOT IN ('l5','final') GROUP BY p.escalation_type ORDER BY p.escalation_type");
|
| 295 |
q.setParameter("ms", ms).setParameter("me", me).setParameterList("auth", auth);
|
295 |
q.setParameter("ms", ms).setParameter("me", me).setParameterList("auth", auth);
|
| 296 |
List<Map<String, Object>> list = new ArrayList<>();
|
296 |
List<Map<String, Object>> list = new ArrayList<>();
|
| 297 |
for (Object o : q.getResultList()) {
|
297 |
for (Object o : q.getResultList()) {
|
| 298 |
Object[] r = (Object[]) o;
|
298 |
Object[] r = (Object[]) o;
|
| 299 |
long total = asLong(r[1]), with = asLong(r[2]);
|
299 |
long total = asLong(r[1]), with = asLong(r[2]);
|
| Line 316... |
Line 316... |
| 316 |
" COALESCE(ds.disc_min,0) disc_min, COALESCE(wk.work_sec,0) work_sec," +
|
316 |
" COALESCE(ds.disc_min,0) disc_min, COALESCE(wk.work_sec,0) work_sec," +
|
| 317 |
" COALESCE(ld.leads,0) leads, du.id duid, COALESCE(acp.donep,0) donep" +
|
317 |
" COALESCE(ld.leads,0) leads, du.id duid, COALESCE(acp.donep,0) donep" +
|
| 318 |
" 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" +
|
| 319 |
" 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" +
|
| 320 |
" LEFT JOIN dtr.users du ON du.email=au.email_id" +
|
320 |
" LEFT JOIN dtr.users du ON du.email=au.email_id" +
|
| 321 |
" 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" +
|
321 |
" LEFT JOIN (SELECT auth_user_id, MIN(escalation_type) lvl FROM cs.position WHERE category_id=4 AND LOWER(escalation_type) NOT IN ('l5','final') GROUP BY auth_user_id) lvl ON lvl.auth_user_id=au.id" +
|
| 322 |
" 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" +
|
| 323 |
" 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" +
|
323 |
" 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" +
|
| 324 |
" WHERE sc.start_date>=:ms AND sc.start_date<=:me GROUP BY b.auth_user_id) pl ON pl.uid=au.id" +
|
324 |
" WHERE sc.start_date>=:ms AND sc.start_date<=:me GROUP BY b.auth_user_id) pl ON pl.uid=au.id" +
|
| 325 |
" LEFT JOIN (SELECT b.auth_user_id uid, COUNT(*) planned FROM `user`.beat_schedule sc" +
|
325 |
" LEFT JOIN (SELECT b.auth_user_id uid, COUNT(*) planned FROM `user`.beat_schedule sc" +
|
| 326 |
" 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'" +
|
326 |
" 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'" +
|
| Line 511... |
Line 511... |
| 511 |
// PJP coverage KPI and the coverage-by-level bar drill-downs.
|
511 |
// PJP coverage KPI and the coverage-by-level bar drill-downs.
|
| 512 |
private List<Map<String, Object>> coverageDetail(Session s, String ms, String me, List<Integer> auth) {
|
512 |
private List<Map<String, Object>> coverageDetail(Session s, String ms, String me, List<Integer> auth) {
|
| 513 |
NativeQuery<?> q = s.createNativeQuery(
|
513 |
NativeQuery<?> q = s.createNativeQuery(
|
| 514 |
"SELECT au.id uid, TRIM(CONCAT(au.first_name,' ',COALESCE(au.last_name,''))) nm, p.lvl," +
|
514 |
"SELECT au.id uid, TRIM(CONCAT(au.first_name,' ',COALESCE(au.last_name,''))) nm, p.lvl," +
|
| 515 |
" MAX(CASE WHEN sc.id IS NOT NULL THEN 1 ELSE 0 END) has_plan" +
|
515 |
" MAX(CASE WHEN sc.id IS NOT NULL THEN 1 ELSE 0 END) has_plan" +
|
| 516 |
" 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" +
|
516 |
" FROM (SELECT auth_user_id, MIN(escalation_type) lvl FROM cs.position WHERE category_id=4 AND auth_user_id IN (:auth) AND LOWER(escalation_type) NOT IN ('l5','final') GROUP BY auth_user_id) p" +
|
| 517 |
" JOIN auth.auth_user au ON au.id=p.auth_user_id" +
|
517 |
" JOIN auth.auth_user au ON au.id=p.auth_user_id" +
|
| 518 |
" LEFT JOIN `user`.beat b ON b.auth_user_id=p.auth_user_id AND b.active=1" +
|
518 |
" LEFT JOIN `user`.beat b ON b.auth_user_id=p.auth_user_id AND b.active=1" +
|
| 519 |
" LEFT JOIN `user`.beat_schedule sc ON sc.beat_id=b.id AND sc.start_date>=:ms AND sc.start_date<=:me" +
|
519 |
" LEFT JOIN `user`.beat_schedule sc ON sc.beat_id=b.id AND sc.start_date>=:ms AND sc.start_date<=:me" +
|
| 520 |
" GROUP BY au.id, nm, p.lvl ORDER BY p.lvl, nm");
|
520 |
" GROUP BY au.id, nm, p.lvl ORDER BY p.lvl, nm");
|
| 521 |
q.setParameter("ms", ms).setParameter("me", me).setParameterList("auth", auth);
|
521 |
q.setParameter("ms", ms).setParameter("me", me).setParameterList("auth", auth);
|