| Line 108... |
Line 108... |
| 108 |
m.put("avgVisitsPerActiveDay", journeys == 0 ? 0 : round1((double) asLong(funnel.get("checkedIn")) / journeys));
|
108 |
m.put("avgVisitsPerActiveDay", journeys == 0 ? 0 : round1((double) asLong(funnel.get("checkedIn")) / journeys));
|
| 109 |
// partner (franchisee) visits per active day — the subset that are partner
|
109 |
// partner (franchisee) visits per active day — the subset that are partner
|
| 110 |
// store visits, shown alongside the all-types visit average.
|
110 |
// store visits, shown alongside the all-types visit average.
|
| 111 |
long partnerCheckins = countVisits(s, ms, me, dtr, "task_type='franchisee-visit' AND mark_type LIKE 'CHECKIN%'");
|
111 |
long partnerCheckins = countVisits(s, ms, me, dtr, "task_type='franchisee-visit' AND mark_type LIKE 'CHECKIN%'");
|
| 112 |
m.put("avgPartnersPerActiveDay", journeys == 0 ? 0 : round1((double) partnerCheckins / journeys));
|
112 |
m.put("avgPartnersPerActiveDay", journeys == 0 ? 0 : round1((double) partnerCheckins / journeys));
|
| 113 |
// lead visits per active day, split into planned (on the PJP / lead_route,
|
113 |
// lead visits per active day — planned (on the PJP / lead_route) and unplanned
|
| 114 |
// no SELF marker) vs unplanned (self-assigned ad-hoc leads, task_name carries
|
114 |
// (self-assigned ad-hoc) leads combined.
|
| 115 |
// the pipe-delimited "SELF" tag).
|
- |
|
| 116 |
long plannedLeadCheckins = countVisits(s, ms, me, dtr, "task_type='lead' AND COALESCE(task_name,'') NOT LIKE '%SELF%' AND mark_type LIKE 'CHECKIN%'");
|
- |
|
| 117 |
long unplannedLeadCheckins = countVisits(s, ms, me, dtr, "task_type='lead' AND COALESCE(task_name,'') LIKE '%SELF%' AND mark_type LIKE 'CHECKIN%'");
|
115 |
long leadCheckins = countVisits(s, ms, me, dtr, "task_type='lead' AND mark_type LIKE 'CHECKIN%'");
|
| 118 |
m.put("avgPlannedLeadsPerActiveDay", journeys == 0 ? 0 : round1((double) plannedLeadCheckins / journeys));
|
116 |
m.put("avgLeadsPerActiveDay", journeys == 0 ? 0 : round1((double) leadCheckins / journeys));
|
| 119 |
m.put("avgUnplannedLeadsPerActiveDay", journeys == 0 ? 0 : round1((double) unplannedLeadCheckins / journeys));
|
- |
|
| 120 |
|
117 |
|
| 121 |
// avg in-store discussion minutes — overall, plus partner (franchisee) and
|
118 |
// avg in-store discussion minutes — overall, plus partner (franchisee) and
|
| 122 |
// lead split so the card can show each separately.
|
119 |
// lead split so the card can show each separately.
|
| 123 |
m.put("avgDiscussionMin", avgDiscussionMin(s, ms, me, dtr, null));
|
120 |
m.put("avgDiscussionMin", avgDiscussionMin(s, ms, me, dtr, null));
|
| 124 |
m.put("avgDiscussionPartnerMin", avgDiscussionMin(s, ms, me, dtr, "franchisee-visit"));
|
121 |
m.put("avgDiscussionPartnerMin", avgDiscussionMin(s, ms, me, dtr, "franchisee-visit"));
|
| Line 255... |
Line 252... |
| 255 |
long idleAvgMin = journeys == 0 ? 0 : Math.round(idle / journeys / 60.0);
|
252 |
long idleAvgMin = journeys == 0 ? 0 : Math.round(idle / journeys / 60.0);
|
| 256 |
|
253 |
|
| 257 |
Map<String, Object> m = new LinkedHashMap<>();
|
254 |
Map<String, Object> m = new LinkedHashMap<>();
|
| 258 |
m.put("plannedVisits", plannedVisits);
|
255 |
m.put("plannedVisits", plannedVisits);
|
| 259 |
m.put("selfVisits", selfVisits);
|
256 |
m.put("selfVisits", selfVisits);
|
| 260 |
m.put("avgTimePlannedMin", avgTimeSpentMin(s, ms, me, dtr, false));
|
- |
|
| 261 |
m.put("avgTimeSelfMin", avgTimeSpentMin(s, ms, me, dtr, true));
|
257 |
m.put("avgTimeAllMin", avgTimeSpentMin(s, ms, me, dtr, null)); // planned + self combined
|
| 262 |
m.put("idleAvgMin", idleAvgMin);
|
258 |
m.put("idleAvgMin", idleAvgMin);
|
| 263 |
m.put("deferredPlanned", Math.max(plannedVisits - completedPlanned, 0));
|
259 |
m.put("deferredPlanned", Math.max(plannedVisits - completedPlanned, 0));
|
| 264 |
m.put("deferredSelf", Math.max(selfVisits - completedSelf, 0));
|
260 |
m.put("deferredSelf", Math.max(selfVisits - completedSelf, 0));
|
| 265 |
m.put("completedPlanned", completedPlanned);
|
261 |
m.put("completedPlanned", completedPlanned);
|
| 266 |
m.put("completedSelf", completedSelf);
|
262 |
m.put("completedSelf", completedSelf);
|
| 267 |
return m;
|
263 |
return m;
|
| 268 |
}
|
264 |
}
|
| 269 |
|
265 |
|
| 270 |
// Avg (check-out − check-in) minutes over checked-out visits, split by whether
|
266 |
// Avg (check-out − check-in) minutes over checked-out visits. self==null covers
|
| 271 |
// the visit carries the pipe-delimited "… | SELF" self-assigned marker.
|
267 |
// all visits (planned + self); true/false restricts to the pipe-delimited
|
| - |
|
268 |
// "… | SELF" self-assigned visits or the planned ones respectively.
|
| 272 |
private long avgTimeSpentMin(Session s, String ms, String me, List<Integer> dtr, boolean self) {
|
269 |
private long avgTimeSpentMin(Session s, String ms, String me, List<Integer> dtr, Boolean self) {
|
| 273 |
NativeQuery<?> q = s.createNativeQuery(
|
270 |
NativeQuery<?> q = s.createNativeQuery(
|
| 274 |
"SELECT ROUND(AVG(TIME_TO_SEC(check_out_time)-TIME_TO_SEC(check_in_time))/60,0) FROM auth.location_tracking" +
|
271 |
"SELECT ROUND(AVG(TIME_TO_SEC(check_out_time)-TIME_TO_SEC(check_in_time))/60,0) FROM auth.location_tracking" +
|
| 275 |
" WHERE task_date>=:ms AND task_date<=:me AND task_id<>0 AND mark_type LIKE '%CHECKOUT%'" +
|
272 |
" WHERE task_date>=:ms AND task_date<=:me AND task_id<>0 AND mark_type LIKE '%CHECKOUT%'" +
|
| 276 |
" AND check_in_time IS NOT NULL AND check_in_time<>'00:00:00'" +
|
273 |
" AND check_in_time IS NOT NULL AND check_in_time<>'00:00:00'" +
|
| 277 |
" AND check_out_time IS NOT NULL AND check_out_time<>'00:00:00'" +
|
274 |
" AND check_out_time IS NOT NULL AND check_out_time<>'00:00:00'" +
|
| 278 |
" AND TIME_TO_SEC(check_out_time) > TIME_TO_SEC(check_in_time)" +
|
275 |
" AND TIME_TO_SEC(check_out_time) > TIME_TO_SEC(check_in_time)" +
|
| 279 |
(self ? " AND COALESCE(task_name,'') LIKE '%SELF%'" : " AND COALESCE(task_name,'') NOT LIKE '%SELF%'") +
|
276 |
(self == null ? "" : self ? " AND COALESCE(task_name,'') LIKE '%SELF%'" : " AND COALESCE(task_name,'') NOT LIKE '%SELF%'") +
|
| 280 |
" AND user_id IN (:dtr)");
|
277 |
" AND user_id IN (:dtr)");
|
| 281 |
q.setParameter("ms", ms).setParameter("me", me).setParameterList("dtr", dtr);
|
278 |
q.setParameter("ms", ms).setParameter("me", me).setParameterList("dtr", dtr);
|
| 282 |
return asLong(q.uniqueResult());
|
279 |
return asLong(q.uniqueResult());
|
| 283 |
}
|
280 |
}
|
| 284 |
|
281 |
|