top.window.location = '../../index.php'";
exit;
}
require_once __DIR__ . "/" . "../../includes/include_basics_only.php";
require_once __DIR__ . "/" . "../../includes/classes/ConnectPDO.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['units'] = TRANS('COL_UNIT') . ': ' . TRANS('ALL');
$criteria['area'] = TRANS('AREA') . ': ' . TRANS('ALL');
$criteria['custom_field'] = TRANS('CUSTOM_FIELD') . ': ' . 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['units'] = (isset($post['unit']) && !empty(array_filter($post['unit'], function($v) { return !empty($v); })) ? array_map('noHtml', $post['unit']) : []);
$data['custom_field'] = (isset($post['custom_field']) ? (int)$post['custom_field'] : "");
$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;
}
$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 . '';
// $onlyView = ($isRequester && !in_array($row['area_cod'], $uareas));
// if (empty($data['custom_field'])) {
// $data['success'] = false;
// $data['message'] = "Confira a informação referente ao campo personalizado!";
// echo json_encode($data);
// return false;
// }
$client_filter = [];
$units_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';
/* Unidades com base no filtro de clientes */
if (!empty($data['client'])) {
$terms .= " AND o.client = {$data['client']}";
$termsTotal .= $terms;
$client_filter = getClients($conn, $data['client']);
$criteria['client'] = TRANS('CLIENT') . ': ' . $client_filter['nickname'] . '';
if (empty($data['units'])) {
$units_filter = getUnits($conn, null, null, $data['client']);
$units_filter = (!empty($units_filter) ? $units_filter : [['inst_cod' => 0]]);
} else {
foreach ($data['units'] as $unit) {
$units_filter[] = getUnits($conn, null, $unit);
}
$criteria['units'] = TRANS('COL_UNIT') . ': ' . implode(', ', array_column($units_filter, 'inst_nome')) . '';
$units_text_ids = implode(', ', array_column($units_filter, 'inst_cod'));
$terms .= " AND o.instituicao IN ({$units_text_ids})";
$termsTotal .= $terms;
}
} elseif (!empty($data['units'])) {
$criteria['client'] = "";
foreach ($data['units'] as $unit) {
$units_filter[] = getUnits($conn, null, $unit);
}
$unitsString = implode(',', $data['units']);
$terms .= " AND o.instituicao IN ({$unitsString})";
$termsTotal .= $terms;
}
/* Campo customizado */
if (!empty($data['custom_field'])) {
$data['generate_chart'] = 1;
$terms .= " AND c.id = {$data['custom_field']} ";
$custom_field_info = getCustomFields($conn, $data['custom_field']);
$criteria['custom_field'] = TRANS('FIELD_TO_GROUP') . ': ' . $custom_field_info['field_label'] . '';
}
/* 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']}' "
];
/* Retorna o total de registro com base nos campos de filtro */
$sqlTotalRecords = "SELECT
count(*) as total
FROM
sistemas ar,
sistemas uar,
usuarios ua,
`status` st,
ocorrencias o
LEFT JOIN clients cl ON cl.id = o.client
LEFT JOIN instituicao un ON un.inst_cod = o.instituicao
WHERE
o.sistema = ar.sis_id AND
o.aberto_por = ua.user_id AND
uar.sis_id = ua.AREA AND
o.status = st.stat_id AND
st.stat_ignored <> 1
{$termsTotal}
{$clausulesByStates[$data['state']]}
";
try {
$res = $conn->prepare($sqlTotalRecords);
$res->execute();
$data['totalRecords'] = $res->fetchAll();
$data['totalRecords'] = (!empty($data['totalRecords']) ? $data['totalRecords'][0]['total'] : 0);
} catch (Exception $e) {
$exception .= "