top.window.location = '../../index.php'"; exit; } require_once __DIR__ . "/" . "../../includes/include_basics_only.php"; require_once __DIR__ . "/" . "../../includes/classes/ConnectPDO.php"; require_once __DIR__ . "/" . "../../includes/classes/worktime/Worktime.php"; include_once __DIR__ . "/" . "../../includes/functions/getWorktimeProfile.php"; use includes\classes\ConnectPDO; $conn = ConnectPDO::getInstance(); $exception = ""; $criteria = []; $criteriaText = ""; $data = []; $data['success'] = true; $data['message'] = ""; $data['total'] = 0; $uareas = explode(',', (string)$_SESSION['s_uareas']); $terms = ""; $termsTotal = ""; $labelsByStates = [ 1 => TRANS('STATE_OPEN_CLOSE_IN_SEARCH_RANGE'), 2 => TRANS('STATE_OPEN_IN_SEARCH_RANGE'), 3 => TRANS('STATE_OPEN_IN_SEARCH_RANGE_CLOSE_ANY_TIME'), 4 => TRANS('STATE_OPEN_ANY_TIME_CLOSE_IN_SEARCH_RANGE'), 5 => TRANS('STATE_JUST_OPEN_IN_SEARCH_RANGE') ]; /* Inicialização dos Critérios da consulta */ $criteria['client'] = TRANS('CLIENT') . ': ' . TRANS('ALL'); $criteria['treater'] = TRANS('WORKER') . ': ' . TRANS('ALL'); $criteria['area'] = TRANS('AREA') . ': ' . TRANS('ALL'); $criteria['state'] = TRANS('CONTEXT') . ': ' . TRANS('STATE_OPEN_CLOSE_IN_SEARCH_RANGE'); $post = $_POST; $hora_inicio = ' 00:00:00'; $hora_fim = ' 23:59:59'; $data['client'] = (isset($post['client']) ? (int)$post['client'] : ""); $data['area'] = (isset($post['area']) ? (int)$post['area'] : ""); $data['treater'] = (isset($post['treater']) ? (int)$post['treater'] : ""); $data['d_ini'] = (isset($post['d_ini']) && isValidDate($post['d_ini']) ? dateDB($post['d_ini'] . $hora_inicio) : ''); $data['d_fim'] = (isset($post['d_fim']) && isValidDate($post['d_fim']) ? dateDB($post['d_fim'] . $hora_fim) : ''); $data['state'] = (isset($post['state']) && !empty($post['state']) ? (int)$post['state'] : 1); $data['generate_chart'] = 1; if (empty($data['d_ini']) || empty($data['d_fim']) || ($data['d_ini'] > $data['d_fim'])) { $data['success'] = false; $data['field_id'] = "d_ini"; $data['message'] = TRANS('MSG_CHECK_PERIOD'); $data['message'] = message('warning', 'Ooops!', $data['message'], '', ''); echo json_encode($data); return false; } if (!empty($data['client'])) { $terms .= " AND o.client = {$data['client']} "; $termsTotal .= $terms; $criteria['client'] = TRANS('CLIENT') . ': ' . getClients($conn, $data['client'])['nickname'] . ''; } $limited_areas = false; $string_area_names = ""; if (empty($data['area']) && isAreasIsolated($conn) && $_SESSION['s_nivel'] != 1) { /* Visibilidade isolada entre áreas para usuários não admin */ $limited_areas = true; $u_areas = $_SESSION['s_uareas']; $terms .= " AND o.sistema IN ({$u_areas}) "; $termsTotal .= $terms; $array_areas_names = getUserAreasNames($conn, $u_areas); $string_area_names = implode(", ", array_filter($array_areas_names)); } elseif (!empty($data['area'])) { $string_area_names = getAreaInfo($conn, $data['area'])['area_name']; $terms .= " AND o.sistema = {$data['area']} "; $termsTotal .= $terms; $criteria['area'] = TRANS('AREA') . ': ' . $string_area_names . ''; } $termsWithoutTreater = $termsTotal; if (!empty($data['treater'])) { $terms .= " AND u.user_id = {$data['treater']} "; $termsTotal .= $terms; $criteria['treater'] = TRANS('WORKER') . ': ' . getUserInfo($conn, $data['treater'])['nome'] . ''; } $client_filter = []; // $termsTotal = ""; $chart_title_sufix = '_chart_title'; $chart_1_prefix = 'chart_01'; $chart_2_prefix = 'chart_02'; $chart_3_prefix = 'chart_03'; $chart_4_prefix = 'chart_04'; $chart_5_prefix = 'chart_05'; $chart_consolidated_prefix = 'chart_06'; /* Período */ $criteria['range'] = TRANS('RANGE_FROM_TO') . ': ' . $post['d_ini'] . ' - ' . $post['d_fim'] . ''; /* Contexto */ $criteria['state'] = TRANS('CONTEXT') . ': ' . $labelsByStates[$data['state']] . ''; $_SESSION['s_rep_filters']['client'] = $data['client']; $_SESSION['s_rep_filters']['area'] = $data['area']; $_SESSION['s_rep_filters']['state'] = $data['state']; $_SESSION['s_rep_filters']['d_ini'] = $post['d_ini']; $_SESSION['s_rep_filters']['d_fim'] = $post['d_fim']; /* Montagem do texto dos critérios */ $criteriaText = implode(' | ', $criteria); if ($limited_areas) { $criteriaText .= "
(". TRANS('RESULT_LIMITED_BY_PERMISSIONS').")"; } $data['criteria'] = $criteriaText; /* Final da montagem dos critérios */ $clausulesByStates = [ 1 => " AND o.data_abertura >= '{$data['d_ini']}' AND o.data_abertura <= '{$data['d_fim']}' AND o.data_fechamento IS NOT NULL AND o.data_fechamento >= '{$data['d_ini']}' AND o.data_fechamento <= '{$data['d_fim']}' ", 2 => " AND o.data_abertura >= '{$data['d_ini']}' AND o.data_abertura <= '{$data['d_fim']}' AND (o.data_fechamento >= '{$data['d_fim']}' OR o.data_fechamento IS NULL) ", 3 => " AND o.data_abertura >= '{$data['d_ini']}' AND o.data_abertura <= '{$data['d_fim']}' AND o.data_fechamento IS NOT NULL ", 4 => " AND o.data_fechamento >= '{$data['d_ini']}' AND o.data_fechamento <= '{$data['d_fim']}' ", 5 => " AND o.data_abertura >= '{$data['d_ini']}' AND o.data_abertura <= '{$data['d_fim']}' " ]; /* Operadores que estão definidos diretamente nas ocorrências */ $data['table_1'] = []; /* Operadores definidos como responsáveis em tickets_x_workers */ $data['table_2'] = []; /* Operadores definidos como auxiliares em tickets_x_workers */ $data['table_3'] = []; /* Operadores adicionados manualmente no ato do encerramento em tickets_treaters_stages */ $data['table_4'] = []; /* Operadores que tiveram chamados em sua fila direta em tickets_stages */ $data['table_5'] = []; /* Tabela auxiliar que concatena as tabelas 1, 2, 3, 4 e 5 */ $data['all_tables_merged'] = []; /* Tabela final processada com a contabilização final - Remove os casos onde operadores que estão definidos diretamente nas ocorrências também aparecem nas outras tabelas */ $data['table_consolidated'] = []; $workers = []; /* Total de chamados do período para qualquer operador */ $sql = "SELECT count(o.numero) AS amount FROM ocorrencias o, usuarios u, `status` st, sistemas s WHERE o.operador = u.user_id AND u.nivel <> 3 AND o.status = st.stat_id AND st.stat_ignored <> 1 AND o.sistema = s.sis_id {$termsWithoutTreater} {$clausulesByStates[$data['state']]} "; try { $res = $conn->prepare($sql); $res->execute(); if ($res->rowCount()) { $data['total'] = $res->fetch()['amount']; } } catch (PDOException $e) { $data['success'] = false; $data['message'] = message('warning', 'Ooops!', $e->getMessage(),''); echo json_encode($data); return false; } $msg_completed_tickets = TRANS('MSG_AMOUNT_OF_COMPLETED_TICKETS') . ': ' . $data['total'] . ''; // $data['total_msg'] = message('info', '', $msg_completed_tickets, '', '', true); $data['total_msg'] = $msg_completed_tickets; /* Consulta nas ocorrências com base no campo `operador` */ $sql = "SELECT u.nome AS operator, count(o.numero) AS amount FROM ocorrencias o, usuarios u, `status` st, sistemas s WHERE o.operador = u.user_id AND u.nivel <> 3 AND o.status = st.stat_id AND st.stat_ignored <> 1 AND o.sistema = s.sis_id {$termsTotal} {$clausulesByStates[$data['state']]} GROUP BY u.nome ORDER BY amount DESC "; try { $res = $conn->prepare($sql); $res->execute(); if ($res->rowCount()) { $data['table_1'] = $res->fetchAll(); } } catch (PDOException $e) { $data['success'] = false; $data['message'] = message('warning', 'Ooops!', $e->getMessage(),''); echo json_encode($data); return false; } $records = count($data['table_1']); $data['all_tables_merged'] = $data['table_1']; $data['table_1']['records'] = $records; $data['table_1']['title'] = TRANS('TITLE_REPORT_TREATERS_FIELD_OPERATOR'); $data['table_1']['sum'] = array_sum(array_column($data['table_1'], 'amount')); $data['table_1']['msg_context'] = message('info', '', TRANS('MSG_REPORT_TREATERS_FIELD_OPERATOR'),'', '', true); $data['table_1']['msg_no_results'] = message('info', '', TRANS('NOT_FOUND_TREATERS_FIELD_OPERATOR'), '', '', true); /** * Todas as consultas seguintes terão duas versões: * 1. para exibição individual no relatório * 2. para agrupamento (merge com a tabela 1), excluindo-se os casos em que cada operador * já estiver marcado como `operador` nas ocorrências * */ /* Tabela 2 para MERGE - consulta na tickets_x_workers - operadores principais */ $sql = "SELECT u.nome AS operator, count(o.numero) AS amount FROM usuarios as u, sistemas as s, `status` st, ocorrencias as o, tickets_extended te, ticket_x_workers txw WHERE -- excluir todos os casos em que o operador já estiver marcado no chamado -- o.operador <> u.user_id AND -- txw.user_id = u.user_id AND txw.ticket = te.ticket AND txw.main_worker = 1 AND o.status = st.stat_id AND o.numero = te.ticket AND st.stat_ignored <> 1 AND o.sistema = s.sis_id {$termsTotal} {$clausulesByStates[$data['state']]} GROUP BY u.nome ORDER BY amount DESC "; try { $res = $conn->prepare($sql); $res->execute(); if ($res->rowCount()) { $data['table_2'] = $res->fetchAll(); } } catch (PDOException $e) { $data['success'] = false; $data['message'] = message('warning', 'Ooops!', $e->getMessage(),''); echo json_encode($data); return false; } $data['all_tables_merged'] = array_merge($data['all_tables_merged'], $data['table_2']); /* Tabela 2 para EXIBIÇÃO - consulta na tickets_x_workers - operadores principais */ $sql = "SELECT u.nome AS operator, count(o.numero) AS amount FROM usuarios as u, sistemas as s, `status` st, ocorrencias as o, tickets_extended te, ticket_x_workers txw WHERE txw.user_id = u.user_id AND txw.ticket = te.ticket AND txw.main_worker = 1 AND o.status = st.stat_id AND o.numero = te.ticket AND st.stat_ignored <> 1 AND o.sistema = s.sis_id {$termsTotal} {$clausulesByStates[$data['state']]} GROUP BY u.nome ORDER BY amount DESC "; try { $res = $conn->prepare($sql); $res->execute(); if ($res->rowCount()) { $data['table_2'] = $res->fetchAll(); } } catch (PDOException $e) { $data['success'] = false; $data['message'] = message('warning', 'Ooops!', $e->getMessage(),''); echo json_encode($data); return false; } $records = count($data['table_2']); $data['table_2']['records'] = $records; $data['table_2']['title'] = $TRANS["TITLE_REPORT_TREATERS_FIELD_MAIN_WORKER"]; $data['table_2']['sum'] = array_sum(array_column($data['table_2'], 'amount')); $data['table_2']['msg_context'] = message('info', '', $TRANS["MSG_REPORT_TREATERS_FIELD_MAIN_WORKER"],'', '', true); $data['table_2']['msg_no_results'] = message('info', '', $TRANS["NOT_FOUND_TREATERS_FIELD_MAIN_WORKER"], '', '', true); /* Tabela 3 para MERGE - consulta na tickets_x_workers - operadores auxiliares*/ $sql = "SELECT u.nome AS operator, count(o.numero) AS amount FROM usuarios as u, sistemas as s, `status` st, ocorrencias as o, tickets_extended te, ticket_x_workers txw WHERE -- excluir todos os casos em que o operador já estiver marcado no chamado -- o.operador <> u.user_id AND -- txw.user_id = u.user_id AND txw.ticket = te.ticket AND txw.main_worker = 0 AND o.status = st.stat_id AND o.numero = te.ticket AND st.stat_ignored <> 1 AND o.sistema = s.sis_id {$termsTotal} {$clausulesByStates[$data['state']]} GROUP BY u.nome ORDER BY amount DESC "; try { $res = $conn->prepare($sql); $res->execute(); if ($res->rowCount()) { $data['table_3'] = $res->fetchAll(); } } catch (PDOException $e) { $data['success'] = false; $data['message'] = message('warning', 'Ooops!', $e->getMessage(),''); echo json_encode($data); return false; } $data['all_tables_merged'] = array_merge($data['all_tables_merged'], $data['table_3']); /* Tabela 3 para EXIBIÇÃO - consulta na tickets_x_workers - operadores auxiliares*/ $sql = "SELECT u.nome AS operator, count(o.numero) AS amount FROM usuarios as u, sistemas as s, `status` st, ocorrencias as o, tickets_extended te, ticket_x_workers txw WHERE txw.user_id = u.user_id AND txw.ticket = te.ticket AND txw.main_worker = 0 AND o.status = st.stat_id AND o.numero = te.ticket AND st.stat_ignored <> 1 AND o.sistema = s.sis_id {$termsTotal} {$clausulesByStates[$data['state']]} GROUP BY u.nome ORDER BY amount DESC "; try { $res = $conn->prepare($sql); $res->execute(); if ($res->rowCount()) { $data['table_3'] = $res->fetchAll(); } } catch (PDOException $e) { $data['success'] = false; $data['message'] = message('warning', 'Ooops!', $e->getMessage(),''); echo json_encode($data); return false; } $records = count($data['table_3']); $data['table_3']['records'] = $records; $data['table_3']['title'] = TRANS('TITLE_REPORT_TREATERS_FIELD_AUX_WORKER'); $data['table_3']['sum'] = array_sum(array_column($data['table_3'], 'amount')); $data['table_3']['msg_context'] = message('info', '', TRANS('MSG_REPORT_TREATERS_FIELD_AUX_WORKER'),'', '', true); $data['table_3']['msg_no_results'] = message('info', '', TRANS('NOT_FOUND_TREATERS_FIELD_AUX_WORKER'), '', '', true); /* Tabela 4 para MERGE - Chamados em que o operador foi adicionado como participante no ato da conclusão do atendimento */ $sql = "SELECT u.nome as operator, COUNT(DISTINCT tts.ticket) AS amount FROM tickets_treaters_stages tts, ocorrencias o, usuarios u, sistemas s, `status` st WHERE -- excluir todos os casos em que o operador já estiver marcado no chamado -- o.operador <> u.user_id AND -- tts.ticket = o.numero AND tts.treater_id = u.user_id AND o.sistema = s.sis_id AND o.status = st.stat_id AND st.stat_ignored <> 1 {$termsTotal} {$clausulesByStates[$data['state']]} GROUP BY u.nome ORDER BY amount DESC "; try { $res = $conn->prepare($sql); $res->execute(); if ($res->rowCount()) { $data['table_4'] = $res->fetchAll(); } } catch (PDOException $e) { $data['success'] = false; $data['message'] = message('warning', 'Ooops!', $e->getMessage(),''); echo json_encode($data); return false; } $data['all_tables_merged'] = array_merge($data['all_tables_merged'], $data['table_4']); /* Tabela 4 para EXIBIÇÃO - Chamados em que o operador foi adicionado como participante no ato da conclusão do atendimento */ $sql = "SELECT u.nome as operator, COUNT(DISTINCT tts.ticket) AS amount FROM tickets_treaters_stages tts, ocorrencias o, usuarios u, sistemas s, `status` st WHERE tts.ticket = o.numero AND tts.treater_id = u.user_id AND o.sistema = s.sis_id AND o.status = st.stat_id AND st.stat_ignored <> 1 {$termsTotal} {$clausulesByStates[$data['state']]} GROUP BY u.nome ORDER BY amount DESC "; try { $res = $conn->prepare($sql); $res->execute(); if ($res->rowCount()) { $data['table_4'] = $res->fetchAll(); } } catch (PDOException $e) { $data['success'] = false; $data['message'] = message('warning', 'Ooops!', $e->getMessage(),''); echo json_encode($data); return false; } $records = count($data['table_4']); $data['table_4']['records'] = $records; $data['table_4']['title'] = TRANS('TITLE_REPORT_TREATERS_FIELD_PARTICIPANT'); $data['table_4']['sum'] = array_sum(array_column($data['table_4'], 'amount')); $data['table_4']['msg_context'] = message('info', '', TRANS('MSG_REPORT_TREATERS_FIELD_PARTICIPANT'),'', '', true); $data['table_4']['msg_no_results'] = message('info', '', TRANS('NOT_FOUND_TREATERS_FIELD_PARTICIPANT'), '', '', true); /* Tabela 5 para MERGE - Quantidade de chamados que estiveram ao menos uma vez em fila direta do operador */ $sql = "SELECT u.nome AS operator, COUNT(DISTINCT ts.ticket) AS amount FROM tickets_stages ts, status st, usuarios u, ocorrencias o, sistemas a, clients cl WHERE -- excluir todos os casos em que o operador já estiver marcado no chamado -- o.operador <> u.user_id AND -- ts.status_id = st.stat_id AND ts.treater_id = u.user_id AND st.stat_painel = 1 AND u.nivel < 3 AND o.sistema = a.sis_id AND o.client = cl.id AND ts.ticket = o.numero AND o.data_fechamento IS NOT NULL {$termsTotal} {$clausulesByStates[$data['state']]} GROUP BY u.nome ORDER BY amount DESC "; try { $res = $conn->prepare($sql); $res->execute(); if ($res->rowCount()) { $data['table_5'] = $res->fetchAll(); } } catch (PDOException $e) { $data['success'] = false; $data['message'] = message('warning', 'Ooops!', $e->getMessage(),''); echo json_encode($data); return false; } $data['all_tables_merged'] = array_merge($data['all_tables_merged'], $data['table_5']); /* Tabela 5 para EXIBIÇÃO - Quantidade de chamados que estiveram ao menos uma vez em fila direta do operador */ $sql = "SELECT u.nome AS operator, COUNT(DISTINCT ts.ticket) AS amount FROM tickets_stages ts, status st, usuarios u, ocorrencias o, sistemas a, clients cl WHERE ts.status_id = st.stat_id AND ts.treater_id = u.user_id AND st.stat_painel = 1 AND u.nivel < 3 AND o.sistema = a.sis_id AND o.client = cl.id AND ts.ticket = o.numero AND o.data_fechamento IS NOT NULL {$termsTotal} {$clausulesByStates[$data['state']]} GROUP BY u.nome ORDER BY amount DESC "; try { $res = $conn->prepare($sql); $res->execute(); if ($res->rowCount()) { $data['table_5'] = $res->fetchAll(); } } catch (PDOException $e) { $data['success'] = false; $data['message'] = message('warning', 'Ooops!', $e->getMessage(),''); echo json_encode($data); return false; } $records = count($data['table_5']); $data['table_5']['records'] = $records; $data['table_5']['title'] = TRANS('TITLE_REPORT_TREATERS_DIRECT_QUEUE'); $data['table_5']['sum'] = array_sum(array_column($data['table_5'], 'amount')); $data['table_5']['msg_context'] = message('info', '', TRANS('MSG_REPORT_TREATERS_DIRECT_QUEUE'),'', '', true); $data['table_5']['msg_no_results'] = message('info', '', TRANS('NOT_FOUND_TREATERS_DIRECT_QUEUE'), '', '', true); $data['table_consolidated'] = array_group_sum($data['all_tables_merged'], 'operator', 'amount'); $data['table_consolidated'] = arraySortByColumn($data['table_consolidated'], 'amount', SORT_DESC, SORT_NUMERIC); $records = count($data['table_consolidated']); $data['table_consolidated']['records'] = $records; $data['table_consolidated']['title'] = TRANS('TITLE_REPORT_TREATERS_CONSOLIDATED'); $data['table_consolidated']['sum'] = array_sum(array_column($data['table_consolidated'], 'amount')); $data['table_consolidated']['msg_context'] = message('info', '', TRANS('MSG_REPORT_TREATERS_CONSOLIDATED'),'', '', true); $data['table_consolidated']['msg_no_results'] = message('info', '', TRANS('NOT_FOUND_TREATERS_CONSOLIDATED'), '', '', true); if (empty($data['all_tables_merged'])) { $data['success'] = false; $data['message'] = message('warning', 'Ooops!', TRANS('NO_RECORDS_FOUND') . $exception,''); echo json_encode($data); return false; } // echo json_encode($data); // return true; /* Gráfico 1 */ $data[$chart_1_prefix] = $data['table_1']; $data[$chart_1_prefix . $chart_title_sufix] = $data[$chart_1_prefix]['title']; /* Gráfico 2 */ $data[$chart_2_prefix] = $data['table_2']; $data[$chart_2_prefix . $chart_title_sufix] = $data[$chart_2_prefix]['title']; /* Gráfico 3 */ $data[$chart_3_prefix] = $data['table_3']; $data[$chart_3_prefix . $chart_title_sufix] = $data[$chart_3_prefix]['title']; /* Gráfico 4 */ $data[$chart_4_prefix] = $data['table_4']; $data[$chart_4_prefix . $chart_title_sufix] = $data[$chart_4_prefix]['title']; /* Gráfico 5 */ $data[$chart_5_prefix] = $data['table_5']; $data[$chart_5_prefix . $chart_title_sufix] = $data[$chart_5_prefix]['title']; /* Gráfico Consolidado */ $data[$chart_consolidated_prefix] = $data['table_consolidated']; $data[$chart_consolidated_prefix . $chart_title_sufix] = $data[$chart_consolidated_prefix]['title']; echo json_encode($data); return true;