3 var _ = require('lodash');
5 module.exports = function() {
15 field: 'calleridname',
16 alias: 'CallerIdName',
23 field: 'queuecallerjoinAt',
27 field: 'queuecallerleaveAt',
28 alias: 'QueueLeaveAt',
31 field: 'queuecallerabandon',
32 alias: 'QueueAbandon',
35 field: 'queuecallerabandonAt',
36 alias: 'QueueAbandonAt',
39 field: 'queuecallercomplete',
43 field: 'queuecallercompleteAt',
47 field: 'queuecallerexit',
51 field: 'queuecallerexitAt',
55 field: 'queuecallerexitreason',
56 alias: 'QueueExitReason',
63 field: 'UNIX_TIMESTAMP(queuecallercompleteAt)-UNIX_TIMESTAMP(queuecallerleaveAt)',
65 format: 'SEC_TO_TIME',
69 field: 'UNIX_TIMESTAMP(queuecallerAbandonAt)-UNIX_TIMESTAMP(queuecallerjoinAt)',
71 format: 'SEC_TO_TIME',
75 field: 'IF(queuecallercomplete=1,holdtime,null)',
77 format: 'SEC_TO_TIME',
89 field: 'queuecallerjoinAt',
93 field: 'CASE WHEN queuecallercomplete=1 THEN queuecallercompleteAt ELSE queuecallerLeaveAt END',
100 format: 'SEC_TO_TIME',
103 field: 'CASE WHEN queuecallercomplete=1 THEN \'YES\' ELSE \'NO\' END',
112 field: 'calleridnum',
113 alias: 'CallerIdNum',
116 field: 'calleridname',
117 alias: 'CallerIdName',
128 field: 'agentcalledAt',
129 alias: 'AgentCalledAt',
132 field: 'agentconnectAt',
133 alias: 'AgentConnectAt',
136 field: 'agentcompleteAt',
137 alias: 'AgentCompleteAt',
153 alias: 'AgentACWTime',
156 field: 'agentcomplete',
157 alias: 'AgentComplete',
161 alias: 'HangupReason',
177 field: 'agentcomplete',
183 alias: 'TotTalkTime',
185 format: 'SEC_TO_TIME',
191 format: 'SEC_TO_TIME',
194 field: 'talktime + acwtime',
195 alias: 'TotHandleTime',
197 format: 'SEC_TO_TIME',
201 field: 'ROUND(AVG(talktime))',
202 alias: 'AvgTalkTime',
203 format: 'SEC_TO_TIME',
207 field: 'ROUND(AVG(acwtime))',
209 format: 'SEC_TO_TIME',
213 field: 'ROUND(SUM(talktime + acwtime)/COUNT(agentcomplete))',
214 alias: 'AvgHandleTime',
215 format: 'SEC_TO_TIME',
224 field: 'agentcomplete',
230 alias: 'Tot TalkTime',
232 format: 'SEC_TO_TIME',
236 alias: 'Tot HoldTime',
238 format: 'SEC_TO_TIME',
242 alias: 'Tot ACWTime',
244 format: 'SEC_TO_TIME',
247 field: 'talktime + acwtime',
248 alias: 'Tot Handle Time',
250 format: 'SEC_TO_TIME',
254 field: 'ROUND(AVG(talktime))',
255 alias: 'Avg TalkTime',
256 format: 'SEC_TO_TIME',
260 field: 'ROUND(AVG(holdtime))',
261 alias: 'Avg HoldTime',
262 format: 'SEC_TO_TIME',
266 field: 'ROUND(AVG(acwtime))',
267 alias: 'Avg ACWTime',
268 format: 'SEC_TO_TIME',
272 field: 'ROUND(SUM(talktime + acwtime)/COUNT(agentcomplete))',
273 alias: 'Avg Handle Time',
274 format: 'SEC_TO_TIME',
283 field: 'agentcomplete',
284 alias: 'Tot Answered',
288 field: 'COUNT(IF(holdtime<10,1,NULL))',
289 alias: 'Answered <10s',
293 field: 'ROUND(COUNT(IF(holdtime<10,1,NULL)) / SUM(agentcomplete) *100,1)',
294 alias: 'Answer Rate <10s',
298 field: 'COUNT(IF(holdtime >=10 AND holdtime <20,1,NULL))',
299 alias: 'Answered 10-20s',
303 field: 'ROUND(COUNT(IF(holdtime >=10 AND holdtime <20,1,NULL)) / SUM(agentcomplete) *100,1)',
304 alias: 'Answer Rate 10-20s',
308 field: 'COUNT(IF(holdtime >=20 AND holdtime <30,1,NULL))',
309 alias: 'Answered 20-30s',
313 field: 'ROUND(COUNT(IF(holdtime >=20 AND holdtime <30,1,NULL)) / SUM(agentcomplete) *100,1)',
314 alias: 'Answer Rate 20-30s',
318 field: 'COUNT(IF(holdtime >=30,1,NULL))',
319 alias: 'Answered >30s',
323 field: 'ROUND(COUNT(IF(holdtime >=30,1,NULL)) / SUM(agentcomplete) *100,1)',
324 alias: 'Answer Rate >30s',
338 field: 'agentconnectAt',
344 field: 'agentcomplete',
345 alias: 'Tot Answered',
349 field: 'COUNT(IF(holdtime<10,1,NULL))',
350 alias: 'Answered <10s',
354 field: 'COUNT(IF(holdtime >=10 AND holdtime <20,1,NULL))',
355 alias: 'Answered 10-20s',
359 field: 'COUNT(IF(holdtime >=20 AND holdtime <30,1,NULL))',
360 alias: 'Answered 20-30s',
364 field: 'COUNT(IF(holdtime >=30,1,NULL))',
365 alias: 'Answered >30s',
373 field: 'calleridnum',
374 alias: 'CallerIdNum',
381 field: 'queuecallerjoinAt',
385 field: 'queuecallerleaveAt',
389 field: 'queuecallerabandon',
393 field: 'queuecallerabandonAt',
397 field: 'queuecallerexit',
401 field: 'queuecallerexitAt',
402 alias: 'UnmanagedAt',
405 field: 'queuecallerexitreason',
406 alias: 'UnmanagedReason',
410 alias: 'QueuePosition',
417 field: 'UNIX_TIMESTAMP(queuecallerAbandonAt)-UNIX_TIMESTAMP(queuecallerjoinAt)',
418 alias: 'Abandon Time',
419 format: 'SEC_TO_TIME',
423 field: 'UNIX_TIMESTAMP(queuecallerExitAt)-UNIX_TIMESTAMP(queuecallerjoinAt)',
424 alias: 'Unmanaged HoldTime',
425 format: 'SEC_TO_TIME',
434 field: 'queuecallerabandon',
439 field: 'queuecallerexit',
444 field: 'UNIX_TIMESTAMP(queuecallerAbandonAt)-UNIX_TIMESTAMP(queuecallerjoinAt)',
445 alias: 'Tot AbandonTime',
447 format: 'SEC_TO_TIME',
451 field: 'ROUND(AVG(UNIX_TIMESTAMP(queuecallerAbandonAt)-UNIX_TIMESTAMP(queuecallerjoinAt)))',
452 alias: 'AvgAbandonTime',
453 format: 'SEC_TO_TIME',
457 field: 'UNIX_TIMESTAMP(queuecallerExitAt)-UNIX_TIMESTAMP(queuecallerjoinAt)',
458 alias: 'Tot Unmanaged Time',
460 format: 'SEC_TO_TIME',
464 field: 'ROUND(AVG(UNIX_TIMESTAMP(queuecallerExitAt)-UNIX_TIMESTAMP(queuecallerjoinAt)))',
465 alias: 'Avg Unmanaged TIme',
466 format: 'SEC_TO_TIME',
470 field: 'queuecallerexitreason',
475 field: 'queuecallerexit',
480 field: 'UNIX_TIMESTAMP(queuecallerExitAt)-UNIX_TIMESTAMP(queuecallerjoinAt)',
481 alias: 'Tot Unmanaged Time',
491 field: 'queuecallerabandon',
492 alias: 'Tot Abandoned',
496 field: 'COUNT(IF(UNIX_TIMESTAMP(queuecallerAbandonAt)-UNIX_TIMESTAMP(queuecallerjoinAt) <10,1,NULL))',
497 alias: 'Abandoned<10s',
501 field: 'ROUND(COUNT(IF(UNIX_TIMESTAMP(queuecallerAbandonAt)-UNIX_TIMESTAMP(queuecallerjoinAt) <10,1,NULL)) / SUM(queuecallerabandon) *100,1)',
502 alias: 'Abandoned Rate<10s',
506 field: 'COUNT(IF(UNIX_TIMESTAMP(queuecallerAbandonAt)-UNIX_TIMESTAMP(queuecallerjoinAt) >=10 AND UNIX_TIMESTAMP(queuecallerAbandonAt)-UNIX_TIMESTAMP(queuecallerjoinAt) <60,1,NULL))',
507 alias: 'Abandoned 10-60s',
511 field: 'ROUND(COUNT(IF(UNIX_TIMESTAMP(queuecallerAbandonAt)-UNIX_TIMESTAMP(queuecallerjoinAt) >=10 AND UNIX_TIMESTAMP(queuecallerAbandonAt)-UNIX_TIMESTAMP(queuecallerjoinAt) <60,1,NULL)) / SUM(queuecallerabandon) *100,1)',
512 alias: 'Abandoned Rate 10-60s',
516 field: 'COUNT(IF(UNIX_TIMESTAMP(queuecallerAbandonAt)-UNIX_TIMESTAMP(queuecallerjoinAt) >=60 AND UNIX_TIMESTAMP(queuecallerAbandonAt)-UNIX_TIMESTAMP(queuecallerjoinAt) <120,1,NULL))',
517 alias: 'Abandoned 60-120s',
521 field: 'ROUND(COUNT(IF(UNIX_TIMESTAMP(queuecallerAbandonAt)-UNIX_TIMESTAMP(queuecallerjoinAt) >=60 AND UNIX_TIMESTAMP(queuecallerAbandonAt)-UNIX_TIMESTAMP(queuecallerjoinAt) <120,1,NULL)) / SUM(queuecallerabandon) *100,1)',
522 alias: 'Abandoned Rate 60-120s',
526 field: 'COUNT(IF(UNIX_TIMESTAMP(queuecallerAbandonAt)-UNIX_TIMESTAMP(queuecallerjoinAt) >=120,1,NULL))',
527 alias: 'Abandoned>120s',
531 field: 'ROUND(COUNT(IF(UNIX_TIMESTAMP(queuecallerAbandonAt)-UNIX_TIMESTAMP(queuecallerjoinAt) >=120,1,NULL)) / SUM(queuecallerabandon) *100,1)',
532 alias: 'Abandoned Rate>120s',
536 field: 'queuecallerjoinat',
545 function: 'COUNT DISTINCT',
548 field: 'queuecallercomplete',
553 field: 'queuecallerabandon',
559 field: 'queuecallerexit',
565 field: 'UNIX_TIMESTAMP(queuecallercompleteAt)-UNIX_TIMESTAMP(queuecallerleaveAt)',
566 alias: 'Tot TalkTime',
568 format: 'SEC_TO_TIME',
572 field: 'ROUND(AVG(UNIX_TIMESTAMP(queuecallercompleteAt)-UNIX_TIMESTAMP(queuecallerleaveAt)))',
573 alias: 'AvgTalkTime',
574 format: 'SEC_TO_TIME',
578 field: 'ROUND(AVG(UNIX_TIMESTAMP(queuecallerAbandonAt)-UNIX_TIMESTAMP(queuecallerjoinAt)))',
579 alias: 'AvgAbandonTime',
580 format: 'SEC_TO_TIME',
584 field: 'queuecallerjoinat',
593 function: 'COUNT DISTINCT',
596 field: 'queuecallercomplete',
601 field: 'ROUND(SUM(queuecallercomplete)/COUNT(DISTINCT uniqueid)*100,1)',
602 alias: 'Answer Rate',
606 field: 'queuecallerabandon',
611 field: 'queuecallerexit',
616 field: 'UNIX_TIMESTAMP(queuecallercompleteAt)-UNIX_TIMESTAMP(queuecallerleaveAt)',
617 alias: 'Tot TalkTime',
619 format: 'SEC_TO_TIME',
623 field: 'ROUND(AVG(UNIX_TIMESTAMP(queuecallercompleteAt)-UNIX_TIMESTAMP(queuecallerleaveAt)))',
624 alias: 'AvgTalkTime',
625 format: 'SEC_TO_TIME',
629 field: 'ROUND(AVG(UNIX_TIMESTAMP(queuecallerAbandonAt)-UNIX_TIMESTAMP(queuecallerjoinAt)))',
630 alias: 'AvgAbandonTime',
631 format: 'SEC_TO_TIME',
635 field: 'DAY(queuecallerjoinat)',
643 function: 'COUNT DISTINCT',
646 field: 'queuecallercomplete',
651 field: 'ROUND(SUM(queuecallercomplete)/COUNT(DISTINCT uniqueid)*100,1)',
652 alias: 'Answer Rate',
656 field: 'queuecallerabandon',
661 field: 'queuecallerexit',
666 field: 'UNIX_TIMESTAMP(queuecallercompleteAt)-UNIX_TIMESTAMP(queuecallerleaveAt)',
667 alias: 'Tot TalkTime',
669 format: 'SEC_TO_TIME',
673 field: 'ROUND(AVG(UNIX_TIMESTAMP(queuecallercompleteAt)-UNIX_TIMESTAMP(queuecallerleaveAt)))',
674 alias: 'AvgTalkTime',
675 format: 'SEC_TO_TIME',
679 field: 'ROUND(AVG(UNIX_TIMESTAMP(queuecallerAbandonAt)-UNIX_TIMESTAMP(queuecallerjoinAt)))',
680 alias: 'AvgAbandonTime',
681 format: 'SEC_TO_TIME',
685 field: 'DAYNAME(queuecallerjoinat)',
693 function: 'COUNT DISTINCT',
696 field: 'queuecallercomplete',
701 field: 'ROUND(SUM(queuecallercomplete)/COUNT(DISTINCT uniqueid)*100,1)',
702 alias: 'Answer Rate',
706 field: 'queuecallerabandon',
712 field: 'queuecallerexit',
717 field: 'UNIX_TIMESTAMP(queuecallercompleteAt)-UNIX_TIMESTAMP(queuecallerleaveAt)',
718 alias: 'Tot TalkTime',
720 format: 'SEC_TO_TIME',
724 field: 'ROUND(AVG(UNIX_TIMESTAMP(queuecallercompleteAt)-UNIX_TIMESTAMP(queuecallerleaveAt)))',
725 alias: 'AvgTalkTime',
726 format: 'SEC_TO_TIME',
730 field: 'ROUND(AVG(UNIX_TIMESTAMP(queuecallerAbandonAt)-UNIX_TIMESTAMP(queuecallerjoinAt)))',
731 alias: 'AvgAbandonTime',
732 format: 'SEC_TO_TIME',
743 function: 'COUNT DISTINCT',
746 field: 'queuecallercomplete',
751 field: 'ROUND(SUM(queuecallercomplete)/COUNT(DISTINCT uniqueid)*100,1)',
752 alias: 'Answer Rate',
756 field: 'queuecallerabandon',
761 field: 'queuecallerexit',
766 field: 'UNIX_TIMESTAMP(queuecallercompleteAt)-UNIX_TIMESTAMP(queuecallerleaveAt)',
767 alias: 'Tot TalkTime',
769 format: 'SEC_TO_TIME',
773 field: 'ROUND(AVG(UNIX_TIMESTAMP(queuecallercompleteAt)-UNIX_TIMESTAMP(queuecallerleaveAt)))',
774 alias: 'AvgTalkTime',
775 format: 'SEC_TO_TIME',
779 field: 'ROUND(AVG(UNIX_TIMESTAMP(queuecallerAbandonAt)-UNIX_TIMESTAMP(queuecallerjoinAt)))',
780 alias: 'AvgAbandonTime',
781 format: 'SEC_TO_TIME',
801 field: 'destination',
802 alias: 'Destination',
809 field: 'destinationchannel',
810 alias: 'DestChannel',
813 field: 'lastapplication',
837 field: 'disposition',
838 alias: 'Disposition',
845 field: 'accountcode',
846 alias: 'AgentExtension',
853 field: 'destination',
854 alias: 'Destination',
875 format: 'SEC_TO_TIME',
878 field: 'billableseconds',
880 format: 'SEC_TO_TIME',
883 field: 'disposition',
884 alias: 'Disposition',
902 alias: 'Tot Duration',
904 format: 'SEC_TO_TIME',
907 field: 'ROUND(AVG(duration))',
908 alias: 'Avg Duration',
909 format: 'SEC_TO_TIME',
913 field: 'disposition',
914 alias: 'Disposition',
924 alias: 'Tot Duration',
926 format: 'SEC_TO_TIME',
929 field: 'ROUND(AVG(duration))',
930 alias: 'Avg Duration',
931 format: 'SEC_TO_TIME',
935 field: 'SUBSTRING_INDEX(channel, "-", 1)',
947 alias: 'Tot Duration',
949 format: 'SEC_TO_TIME',
952 field: 'ROUND(AVG(duration))',
953 alias: 'Avg Duration',
954 format: 'SEC_TO_TIME',
970 alias: 'Tot Duration',
972 format: 'SEC_TO_TIME',
975 field: 'ROUND(AVG(duration))',
976 alias: 'Avg Duration',
977 format: 'SEC_TO_TIME',
987 field: 'UNIX_TIMESTAMP(exitAt)-UNIX_TIMESTAMP(enterAt)',
990 format: 'SEC_TO_TIME',
999 field: 'CASE WHEN type=\'LOGIN\' THEN UNIX_TIMESTAMP(exitAt)-UNIX_TIMESTAMP(enterAt) END',
1000 alias: 'Login Time',
1002 format: 'SEC_TO_TIME',
1006 field: 'CASE WHEN type=\'TALKING\' THEN UNIX_TIMESTAMP(exitAt)-UNIX_TIMESTAMP(enterAt) END',
1009 format: 'SEC_TO_TIME',
1013 field: 'CASE WHEN type=\'PAUSE\' THEN UNIX_TIMESTAMP(exitAt)-UNIX_TIMESTAMP(enterAt) END',
1014 alias: 'Pause Time',
1016 format: 'SEC_TO_TIME',
1020 field: 'membername',
1038 format: 'SEC_TO_TIME',
1041 field: 'membername',
1065 field: 'UNIX_TIMESTAMP(exitAt)-UNIX_TIMESTAMP(enterAt)',
1067 format: 'SEC_TO_TIME',
1071 field: 'membername',
1083 field: 'UNIX_TIMESTAMP(exitAt)-UNIX_TIMESTAMP(enterAt)',
1086 format: 'SEC_TO_TIME',
1090 field: 'project_name',
1091 alias: 'Project Name',
1107 alias: 'Start Time',
1115 field: 'UNIX_TIMESTAMP(leaveAt)-UNIX_TIMESTAMP(joinAt)',
1117 format: 'SEC_TO_TIME',
1125 field: 'project_name',
1126 alias: 'Project Name',
1141 field: 'connectedlinename',
1147 alias: 'Recorded Calls',
1152 alias: 'Total Rating',
1157 alias: 'Avg Rating',
1167 alias: 'Rated Calls',
1172 alias: 'Total Rating',
1177 alias: 'Avg Rating',
1182 alias: 'Campaign Name',
1185 field: 'calleridnum',
1186 alias: 'CallerIdNum',
1190 alias: 'LastCallAt',
1198 field: 'countglobal',
1199 alias: 'Global Count',
1202 field: 'dropreason',
1203 alias: 'Drop Reason',
1210 field: 'membername',
1218 field: 'disposition',
1219 alias: 'Disposition',
1237 field: 'ROUND(count(id)*100/(SELECT COUNT(*) FROM cm_hopper_history WHERE campaigntype=\'queue\'),2)',
1243 alias: 'Campaign Name',
1254 alias: 'Total Calls',
1268 field: 'SUM(IF(state=4,1,0))',
1273 field: 'ROUND(SUM(IF(state=4,1,0))/COUNT(*)*100,1)',
1274 alias: 'Penetration Rate',
1278 field: 'membername',
1289 field: 'SEC_TO_TIME(AVG(talktime))',
1290 alias: 'AVG Talk Time',
1294 field: 'disposition',
1295 alias: 'Disposition',
1304 field: 'ROUND(COUNT(id)*100/(SELECT COUNT(*) FROM cm_hopper_final WHERE state=4 and campaigntype=\'queue\'),2)',
1309 field: 'membername',
1314 field: 'disposition',
1315 alias: 'Disposition',
1324 field: 'ROUND(COUNT(id)*100/(SELECT COUNT(*) FROM cm_hopper_final WHERE state=4 and campaigntype=\'queue\'),2)',
1330 alias: 'Campaign Name',
1333 field: 'calleridnum',
1334 alias: 'CallerIdNum',
1338 alias: 'LastCallAt',
1346 field: 'countglobal',
1347 alias: 'Global Count',
1350 field: 'dropreason',
1351 alias: 'Drop Reason',
1377 field: 'ROUND(count(id)*100/(SELECT COUNT(*) FROM cm_hopper_history WHERE campaigntype=\'ivr\'),2)',
1383 alias: 'Campaign Name',
1394 alias: 'Total Calls',
1408 field: 'SUM(IF(state=4,1,0))',
1413 field: 'ROUND(SUM(IF(state=4,1,0))/COUNT(*)*100,1)',
1414 alias: 'Penetration Rate',
1418 field: 'chat_websites.name',
1423 field: 'chat_interactions.id',
1424 alias: 'Interactions',
1428 field: 'COUNT(IF(closed=0,1,NULL))',
1433 field: 'COUNT(IF(closed=1 && ((disposition !=\'unmanaged\' && disposition !=\'abandoned\') || disposition IS NULL),1,NULL))',
1438 field: 'COUNT(IF(closed=1 && disposition =\'unmanaged\',1,NULL))',
1443 field: 'COUNT(IF(closed=1 && disposition =\'abandoned\',1,NULL))',
1448 field: 'chat_websites.name',
1453 field: 'UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(chat_interactions.createdAt)',
1454 alias: 'Interaction Time',
1456 format: 'SEC_TO_TIME',
1460 field: 'ROUND(AVG(UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(chat_interactions.createdAt)))',
1461 alias: 'AVG Interaction Time',
1462 format: 'SEC_TO_TIME',
1466 field: 'users.fullname',
1471 field: 'chat_interactions.closed',
1476 field: 'UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(chat_interactions.createdAt)',
1477 alias: 'Interaction Time',
1479 format: 'SEC_TO_TIME',
1483 field: 'ROUND(AVG(UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(chat_interactions.createdAt)))',
1484 alias: 'AVG Interaction Time',
1485 format: 'SEC_TO_TIME',
1489 field: 'chat_interactions.createdAt',
1496 field: 'chat_interactions.id',
1497 alias: 'Interactions',
1501 field: 'COUNT(IF(closed=1 && ((disposition !=\'unmanaged\' && disposition !=\'abandoned\') || disposition IS NULL),1,NULL))',
1506 field: 'ROUND(COUNT(IF(closed=1 && ((disposition !=\'unmanaged\' && disposition !=\'abandoned\') || disposition IS NULL),1,NULL))/COUNT(id) *100,1)',
1507 alias: 'Completed (%)',
1511 field: 'COUNT(IF(closed=1 && disposition =\'unmanaged\',1,NULL))',
1516 field: 'ROUND(COUNT(IF(closed=1 && disposition=\'unmanaged\',1,NULL))/COUNT(id) *100,1)',
1517 alias: 'Unmanaged (%)',
1521 field: 'COUNT(IF(closed=1 && disposition =\'abandoned\',1,NULL))',
1526 field: 'ROUND(COUNT(IF(closed=1 && disposition=\'abandoned\',1,NULL))/COUNT(id) *100,1)',
1527 alias: 'Abandoned (%)',
1531 field: 'UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(createdAt)',
1532 alias: 'Interaction Time',
1534 format: 'SEC_TO_TIME',
1538 field: 'ROUND(AVG(UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(createdAt)))',
1539 alias: 'AVG Interaction Time',
1540 format: 'SEC_TO_TIME',
1544 field: 'chat_queues.name',
1549 field: 'report_chat_queue.id',
1550 alias: 'Interactions',
1554 field: 'COUNT(IF(reason=\'accepted\',1,NULL))',
1559 field: 'COUNT(IF(reason=\'timeout\',1,NULL))',
1564 field: 'UNIX_TIMESTAMP(leaveAt)-UNIX_TIMESTAMP(joinAt)',
1567 format: 'SEC_TO_TIME',
1571 field: 'ROUND(AVG(UNIX_TIMESTAMP(leaveAt)-UNIX_TIMESTAMP(joinAt)))',
1572 alias: 'AVG Hold Time',
1573 format: 'SEC_TO_TIME',
1577 field: 'mail_accounts.name',
1578 alias: 'Email Account',
1582 field: 'mail_interactions.id',
1583 alias: 'Interactions',
1587 field: 'COUNT(IF(closed=0,1,NULL))',
1592 field: 'COUNT(IF(closed=1,1,NULL))',
1597 field: 'mail_accounts.name',
1598 alias: 'Email Account',
1602 field: 'UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(mail_interactions.createdAt)',
1603 alias: 'Interaction Time',
1605 format: 'SEC_TO_TIME',
1609 field: 'ROUND(AVG(UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(mail_interactions.createdAt)))',
1610 alias: 'AVG Interaction Time',
1611 format: 'SEC_TO_TIME',
1615 field: 'users.fullname',
1620 field: 'mail_interactions.closed',
1625 field: 'UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(mail_interactions.createdAt)',
1626 alias: 'Interaction Time',
1628 format: 'SEC_TO_TIME',
1632 field: 'ROUND(AVG(UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(mail_interactions.createdAt)))',
1633 alias: 'AVG Interaction Time',
1634 format: 'SEC_TO_TIME',
1638 field: 'mail_interactions.createdAt',
1645 field: 'mail_interactions.id',
1646 alias: 'Interactions',
1650 field: 'COUNT(IF(closed=1,1,NULL))',
1655 field: 'ROUND(COUNT(IF(closed=1,1,NULL))/COUNT(id) *100,1)',
1656 alias: 'Completed (%)',
1660 field: 'UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(createdAt)',
1661 alias: 'Interaction Time',
1663 format: 'SEC_TO_TIME',
1667 field: 'ROUND(AVG(UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(createdAt)))',
1668 alias: 'AVG Interaction Time',
1669 format: 'SEC_TO_TIME',
1673 field: 'mail_queues.name',
1678 field: 'report_mail_queue.id',
1679 alias: 'Interactions',
1683 field: 'COUNT(IF(reason=\'accepted\',1,NULL))',
1688 field: 'COUNT(IF(reason=\'timeout\',1,NULL))',
1693 field: 'UNIX_TIMESTAMP(leaveAt)-UNIX_TIMESTAMP(joinAt)',
1696 format: 'SEC_TO_TIME',
1700 field: 'ROUND(AVG(UNIX_TIMESTAMP(leaveAt)-UNIX_TIMESTAMP(joinAt)))',
1701 alias: 'AVG Hold Time',
1702 format: 'SEC_TO_TIME',
1706 field: 'openchannel_accounts.name',
1707 alias: 'Openchannel Account',
1711 field: 'openchannel_interactions.id',
1712 alias: 'Interactions',
1716 field: 'COUNT(IF(closed=0,1,NULL))',
1721 field: 'COUNT(IF(closed=1,1,NULL))',
1726 field: 'openchannel_accounts.name',
1727 alias: 'Openchannel Account',
1731 field: 'UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(openchannel_interactions.createdAt)',
1732 alias: 'Interaction Time',
1734 format: 'SEC_TO_TIME',
1738 field: 'ROUND(AVG(UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(openchannel_interactions.createdAt)))',
1739 alias: 'AVG Interaction Time',
1740 format: 'SEC_TO_TIME',
1744 field: 'users.fullname',
1749 field: 'openchannel_interactions.closed',
1754 field: 'UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(openchannel_interactions.createdAt)',
1755 alias: 'Interaction Time',
1757 format: 'SEC_TO_TIME',
1761 field: 'ROUND(AVG(UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(openchannel_interactions.createdAt)))',
1762 alias: 'AVG Interaction Time',
1763 format: 'SEC_TO_TIME',
1767 field: 'openchannel_interactions.createdAt',
1774 field: 'openchannel_interactions.id',
1775 alias: 'Interactions',
1779 field: 'COUNT(IF(closed=1,1,NULL))',
1784 field: 'ROUND(COUNT(IF(closed=1,1,NULL))/COUNT(id) *100,1)',
1785 alias: 'Completed (%)',
1789 field: 'UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(createdAt)',
1790 alias: 'Interaction Time',
1792 format: 'SEC_TO_TIME',
1796 field: 'ROUND(AVG(UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(createdAt)))',
1797 alias: 'AVG Interaction Time',
1798 format: 'SEC_TO_TIME',
1802 field: 'openchannel_queues.name',
1807 field: 'report_openchannel_queue.id',
1808 alias: 'Interactions',
1812 field: 'COUNT(IF(reason=\'accepted\',1,NULL))',
1817 field: 'COUNT(IF(reason=\'timeout\',1,NULL))',
1822 field: 'UNIX_TIMESTAMP(leaveAt)-UNIX_TIMESTAMP(joinAt)',
1825 format: 'SEC_TO_TIME',
1829 field: 'ROUND(AVG(UNIX_TIMESTAMP(leaveAt)-UNIX_TIMESTAMP(joinAt)))',
1830 alias: 'AVG Hold Time',
1831 format: 'SEC_TO_TIME',
1835 field: 'sms_accounts.name',
1836 alias: 'Sms Account',
1840 field: 'sms_interactions.id',
1841 alias: 'Interactions',
1845 field: 'COUNT(IF(closed=0,1,NULL))',
1850 field: 'COUNT(IF(closed=1,1,NULL))',
1855 field: 'sms_accounts.name',
1856 alias: 'Sms Account',
1860 field: 'UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(sms_interactions.createdAt)',
1861 alias: 'Interaction Time',
1863 format: 'SEC_TO_TIME',
1867 field: 'ROUND(AVG(UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(sms_interactions.createdAt)))',
1868 alias: 'AVG Interaction Time',
1869 format: 'SEC_TO_TIME',
1873 field: 'users.fullname',
1878 field: 'sms_interactions.closed',
1883 field: 'UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(sms_interactions.createdAt)',
1884 alias: 'Interaction Time',
1886 format: 'SEC_TO_TIME',
1890 field: 'ROUND(AVG(UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(sms_interactions.createdAt)))',
1891 alias: 'AVG Interaction Time',
1892 format: 'SEC_TO_TIME',
1896 field: 'sms_interactions.createdAt',
1903 field: 'sms_interactions.id',
1904 alias: 'Interactions',
1908 field: 'COUNT(IF(closed=1,1,NULL))',
1913 field: 'ROUND(COUNT(IF(closed=1,1,NULL))/COUNT(id) *100,1)',
1914 alias: 'Completed (%)',
1918 field: 'UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(createdAt)',
1919 alias: 'Interaction Time',
1921 format: 'SEC_TO_TIME',
1925 field: 'ROUND(AVG(UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(createdAt)))',
1926 alias: 'AVG Interaction Time',
1927 format: 'SEC_TO_TIME',
1931 field: 'sms_queues.name',
1936 field: 'report_sms_queue.id',
1937 alias: 'Interactions',
1941 field: 'COUNT(IF(reason=\'accepted\',1,NULL))',
1946 field: 'COUNT(IF(reason=\'timeout\',1,NULL))',
1951 field: 'UNIX_TIMESTAMP(leaveAt)-UNIX_TIMESTAMP(joinAt)',
1954 format: 'SEC_TO_TIME',
1958 field: 'ROUND(AVG(UNIX_TIMESTAMP(leaveAt)-UNIX_TIMESTAMP(joinAt)))',
1959 alias: 'AVG Hold Time',
1960 format: 'SEC_TO_TIME',
1964 field: 'fax_queues.name',
1969 field: 'report_fax_queue.id',
1970 alias: 'Interactions',
1974 field: 'COUNT(IF(reason=\'accepted\',1,NULL))',
1979 field: 'COUNT(IF(reason=\'timeout\',1,NULL))',
1984 field: 'UNIX_TIMESTAMP(leaveAt)-UNIX_TIMESTAMP(joinAt)',
1987 format: 'SEC_TO_TIME',
1991 field: 'ROUND(AVG(UNIX_TIMESTAMP(leaveAt)-UNIX_TIMESTAMP(joinAt)))',
1992 alias: 'AVG Hold Time',
1993 format: 'SEC_TO_TIME',
1997 field: 'fax_interactions.createdAt',
2004 field: 'fax_interactions.id',
2005 alias: 'Interactions',
2009 field: 'COUNT(IF(closed=1,1,NULL))',
2014 field: 'ROUND(COUNT(IF(closed=1,1,NULL))/COUNT(id) *100,1)',
2015 alias: 'Completed (%)',
2019 field: 'UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(createdAt)',
2020 alias: 'Interaction Time',
2022 format: 'SEC_TO_TIME',
2026 field: 'ROUND(AVG(UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(createdAt)))',
2027 alias: 'AVG Interaction Time',
2028 format: 'SEC_TO_TIME',
2032 field: 'users.fullname',
2037 field: 'fax_interactions.closed',
2042 field: 'UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(fax_interactions.createdAt)',
2043 alias: 'Interaction Time',
2045 format: 'SEC_TO_TIME',
2049 field: 'ROUND(AVG(UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(fax_interactions.createdAt)))',
2050 alias: 'AVG Interaction Time',
2051 format: 'SEC_TO_TIME',
2055 field: 'fax_accounts.name',
2056 alias: 'Fax Account',
2060 field: 'UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(fax_interactions.createdAt)',
2061 alias: 'Interaction Time',
2063 format: 'SEC_TO_TIME',
2067 field: 'ROUND(AVG(UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(fax_interactions.createdAt)))',
2068 alias: 'AVG Interaction Time',
2069 format: 'SEC_TO_TIME',
2073 field: 'fax_accounts.name',
2074 alias: 'Fax Account',
2078 field: 'fax_interactions.id',
2079 alias: 'Interactions',
2083 field: 'COUNT(IF(closed=0,1,NULL))',
2088 field: 'COUNT(IF(closed=1,1,NULL))',
2093 field: 'voice_recordings.UserId',
2099 field: 'users.fullname',
2105 field: 'COUNT (IF(voice_recordings.sentiment = \'POSITIVE\',1,NULL))',
2111 field: 'COUNT (IF(voice_recordings.sentiment = \'NEGATIVE\',1,NULL))',
2117 field: 'COUNT (IF(voice_recordings.sentiment = \'NEUTRAL\',1,NULL))',
2123 field: 'COUNT (IF(voice_recordings.sentiment = \'MIXED\',1,NULL))',
2129 field: 'voice_recordings.sentiment',
2136 field: 'CONCAT(ROUND(( (SELECT COUNT(IF(voice_recordings.sentiment = \'POSITIVE\',1,NULL)))*100/ COUNT (voice_recordings.sentiment)),2),\'%\')',
2137 alias: 'Positive(%)',
2143 field: 'CONCAT(ROUND(( (SELECT COUNT(IF(voice_recordings.sentiment = \'NEGATIVE\',1,NULL)))*100/ COUNT (voice_recordings.sentiment)),2),\'%\')',
2144 alias: 'Negative(%)',
2150 field: 'CONCAT(ROUND(( (SELECT COUNT(IF(voice_recordings.sentiment = \'NEUTRAL\',1,NULL)))*100/ COUNT (voice_recordings.sentiment)),2),\'%\')',
2151 alias: 'Neutral(%)',
2157 field: 'CONCAT(ROUND(( (SELECT COUNT(IF(voice_recordings.sentiment = \'MIXED\',1,NULL)))*100/ COUNT (voice_recordings.sentiment)),2),\'%\')',
2163 field: 'voice_recordings.sentiment',
2164 alias: 'Sentiment TOT',
2170 field: 'COUNT (IF(voice_recordings.sentiment = \'POSITIVE\',1,NULL))',
2176 field: 'COUNT (IF(voice_recordings.sentiment = \'NEGATIVE\',1,NULL))',
2182 field: 'COUNT (IF(voice_recordings.sentiment = \'NEUTRAL\',1,NULL))',
2188 field: 'COUNT (IF(voice_recordings.sentiment = \'MIXED\',1,NULL))',
2194 field: 'CONCAT(ROUND(( (SELECT COUNT(IF(voice_recordings.sentiment = \'POSITIVE\',1,NULL)))*100/ COUNT (voice_recordings.sentiment)),2),\'%\')',
2195 alias: 'Positive(%)',
2201 field: 'CONCAT(ROUND(( (SELECT COUNT(IF(voice_recordings.sentiment = \'NEGATIVE\',1,NULL)))*100/ COUNT (voice_recordings.sentiment)),2),\'%\')',
2202 alias: 'Negative(%)',
2208 field: 'CONCAT(ROUND(( (SELECT COUNT(IF(voice_recordings.sentiment = \'NEUTRAL\',1,NULL)))*100/ COUNT (voice_recordings.sentiment)),2),\'%\')',
2209 alias: 'Neutral(%)',
2215 field: 'CONCAT(ROUND(( (SELECT COUNT(IF(voice_recordings.sentiment = \'MIXED\',1,NULL)))*100/ COUNT (voice_recordings.sentiment)),2),\'%\')',
2221 field: 'whatsapp_accounts.name',
2222 alias: 'Whatsapp Account',
2226 field: 'whatsapp_interactions.id',
2227 alias: 'Interactions',
2231 field: 'COUNT(IF(closed=0,1,NULL))',
2236 field: 'COUNT(IF(closed=1,1,NULL))',
2241 field: 'whatsapp_accounts.name',
2242 alias: 'Whatsapp Account',
2246 field: 'UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(whatsapp_interactions.createdAt)',
2247 alias: 'Interaction Time',
2249 format: 'SEC_TO_TIME',
2253 field: 'ROUND(AVG(UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(whatsapp_interactions.createdAt)))',
2254 alias: 'AVG Interaction Time',
2255 format: 'SEC_TO_TIME',
2259 field: 'users.fullname',
2264 field: 'whatsapp_interactions.closed',
2269 field: 'UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(whatsapp_interactions.createdAt)',
2270 alias: 'Interaction Time',
2272 format: 'SEC_TO_TIME',
2276 field: 'ROUND(AVG(UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(whatsapp_interactions.createdAt)))',
2277 alias: 'AVG Interaction Time',
2278 format: 'SEC_TO_TIME',
2282 field: 'whatsapp_interactions.createdAt',
2289 field: 'whatsapp_interactions.id',
2290 alias: 'Interactions',
2294 field: 'COUNT(IF(closed=1,1,NULL))',
2299 field: 'ROUND(COUNT(IF(closed=1,1,NULL))/COUNT(id) *100,1)',
2300 alias: 'Completed (%)',
2304 field: 'UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(createdAt)',
2305 alias: 'Interaction Time',
2307 format: 'SEC_TO_TIME',
2311 field: 'ROUND(AVG(UNIX_TIMESTAMP(closedAt)-UNIX_TIMESTAMP(createdAt)))',
2312 alias: 'AVG Interaction Time',
2313 format: 'SEC_TO_TIME',
2317 field: 'whatsapp_queues.name',
2322 field: 'report_whatsapp_queue.id',
2323 alias: 'Interactions',
2327 field: 'COUNT(IF(reason=\'accepted\',1,NULL))',
2332 field: 'COUNT(IF(reason=\'timeout\',1,NULL))',
2337 field: 'UNIX_TIMESTAMP(leaveAt)-UNIX_TIMESTAMP(joinAt)',
2340 format: 'SEC_TO_TIME',
2344 field: 'ROUND(AVG(UNIX_TIMESTAMP(leaveAt)-UNIX_TIMESTAMP(joinAt)))',
2345 alias: 'AVG Hold Time',
2346 format: 'SEC_TO_TIME',
2352 return _.groupBy(fields, 'ReportId');