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 = "";
$termsNoTreater = "";
$termsTotal = "";
$holidays = getHolidays($conn);
$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'] = 0;
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']} ";
$termsNoTreater .= " 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}) ";
$termsNoTreater .= " 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']} ";
$termsNoTreater .= " AND o.sistema = {$data['area']} ";
$termsTotal .= $terms;
$criteria['area'] = TRANS('AREA') . ': ' . $string_area_names . '';
}
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';
/* 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']}' "
];
$data['table_1'] = [];
$data['table_2'] = [];
$data['tickets'] = [];
$data['tickets_2'] = [];
/* Busca na tickets_treaters_stages pelos tempos fornecidos */
$sql = "SELECT
o.numero,
COALESCE(cl.nickname, 'N/A') AS cliente,
a.sistema,
p.problema,
COALESCE (SUM(tts.full_seconds), 0) as seconds,
COALESCE (CONCAT
(
SUM(tts.hours),
':',
FLOOR(SUM(tts.minutes)),
':',
SUM(tts.seconds)
), '00:00:00') as concated_time
-- o.operador AS operador
FROM
sistemas a,
problemas p,
((ocorrencias o
LEFT JOIN
tickets_treaters_stages tts ON tts.ticket = o.numero
LEFT JOIN
usuarios u ON tts.treater_id = u.user_id)
LEFT JOIN
clients cl ON o.client = cl.id)
WHERE
o.sistema = a.sis_id AND
o.problema = p.prob_id AND
o.data_fechamento IS NOT NULL
{$terms}
{$clausulesByStates[$data['state']]}
GROUP BY
o.numero, cl.nickname, a.sistema, p.problema
-- , o.operador
ORDER BY numero
";
// dump($sql); exit;
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;
}
// if (count($data['table_1']) == 0) {
// $data['success'] = false;
// $data['message'] = message('warning', 'Ooops!', TRANS('NO_RECORDS_FOUND') . $exception,'');
// echo json_encode($data);
// return false;
// }
foreach ($data['table_1'] as $row) {
if (is_array($row) && array_key_exists('numero', $row)) {
/* Utilizar a chave 'numero' como chave */
$data['tickets'][$row['numero']] = $row;
}
}
/* Busca geral - não pode filtrar pelo treater pois essa verificação é feita no loop da fila direta */
$sql = "SELECT
o.numero,
COALESCE(cl.nickname, 'N/A') AS cliente,
a.sistema,
p.problema,
-- '00:00:00' as concated_time,
o.operador
FROM
sistemas a,
problemas p,
usuarios u,
(ocorrencias o
LEFT JOIN
clients cl ON o.client = cl.id)
WHERE
o.sistema = a.sis_id AND
o.problema = p.prob_id AND
o.operador = u.user_id AND
o.data_fechamento IS NOT NULL
{$termsNoTreater}
{$clausulesByStates[$data['state']]}
GROUP BY
o.numero, cl.nickname, a.sistema, p.problema, o.operador
ORDER BY numero";
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;
}
foreach ($data['table_2'] as $row) {
if (is_array($row) && array_key_exists('numero', $row)) {
/* Utilizar a chave 'numero' como chave */
$data['tickets_2'][$row['numero']] = $row;
}
}
$data['tickets'] = mergeArrays($data['tickets'], $data['tickets_2']);
foreach ($data['tickets'] as $key => $ticketInfo) {
if (!array_key_exists('concated_time', $ticketInfo)) {
$data['tickets'][$key]['concated_time'] = '00:00:00';
}
}
if (count($data['tickets']) == 0) {
$data['success'] = false;
$data['message'] = message('warning', 'Ooops!', TRANS('NO_RECORDS_FOUND') . $exception,'');
echo json_encode($data);
return false;
}
if (!empty($data['tickets'])) {
/* Realizar os cálculos de tempo em fila direta para cada chamado */
$treaterAbsoluteSecondsSliced = [];
foreach ($data['tickets'] as $key => $ticketInfo) {
$profileCod = getProfileCod($conn, $_SESSION['s_wt_areas'], $key);
$worktimeProfile = getWorktimeProfile($conn, $profileCod);
$arrayObj[$key] = new WorkTime( $worktimeProfile, $holidays );
$stages[$key] = getStagesFromTicket($conn, $key, ['panel' => '1']);
foreach ($stages[$key] as $stageInfo) {
/* date_start, date_stop, status, user_id */
if (empty($data['treater']) || $data['treater'] == $stageInfo['user_id']) {
$arrayObj[$key]->startTimer($stageInfo['date_start']);
$arrayObj[$key]->stopTimer($stageInfo['date_stop'] ?? date('Y-m-d H:i:s'));
$treaterAbsoluteSecondsSliced[$key][] = absoluteTime($stageInfo['date_start'], $stageInfo['date_stop'] ?? date('Y-m-d H:i:s'))['inSeconds'];
} else {
$arrayObj[$key]->startTimer(date('Y-m-d H:i:s'));
$arrayObj[$key]->stopTimer(date('Y-m-d H:i:s'));
$treaterAbsoluteSecondsSliced[$key][] = 0;
}
}
$ticketInfo['abs_secs_in_direct_queue'] = array_sum($treaterAbsoluteSecondsSliced[$key] ?? []);
$ticketInfo['abs_time_in_direct_queue'] = secToTime($ticketInfo['abs_secs_in_direct_queue'])['verbose'];
$ticketInfo['filtered_secs_in_direct_queue'] = $arrayObj[$key]->getSeconds() ?? '0';
$ticketInfo['filtered_time_in_direct_queue'] = $arrayObj[$key]->getTime() ?? '0';
/* Só mostrar os que tiverem tempo */
if ($ticketInfo['abs_secs_in_direct_queue'] > 0 || $ticketInfo['concated_time'] != '00:00:00') {
$data['tickets'][$key] = $ticketInfo;
} else {
unset($data['tickets'][$key]);
}
}
$data['tickets']['title'] = TRANS('REPORT_TICKETS_TREATING_TIMES');
$data['tickets']['totalProvidedInSeconds'] = array_sum(array_column($data['tickets'], 'seconds'));
$data['tickets']['totalProvidedInTime'] = secToTime($data['tickets']['totalProvidedInSeconds'])['verbose'];
$data['tickets']['totalInQueueFilteredInSeconds'] = array_sum(array_column($data['tickets'], 'filtered_secs_in_direct_queue'));
$data['tickets']['totalInQueueFilteredInTime'] = secToTime($data['tickets']['totalInQueueFilteredInSeconds'])['verbose'];
$data['tickets']['totalInQueueAbsoluteInSeconds'] = array_sum(array_column($data['tickets'], 'abs_secs_in_direct_queue'));
$data['tickets']['totalInQueueAbsoluteInTime'] = secToTime($data['tickets']['totalInQueueAbsoluteInSeconds'])['verbose'];
}
// var_dump($data['tickets']);
echo json_encode($data);
return true;