fetchall() as $row) {
/**
* Quantidade de chamados abertos no período
*/
$sqlOpened = "SELECT
count(*) AS abertos,
s.sistema AS area
FROM
ocorrencias AS o,
sistemas AS s,
`status` AS st
WHERE
o.status = st.stat_id AND
st.stat_ignored <> 1 AND
o.sistema = s.sis_id AND
o.data_abertura >= '{$d_ini}' AND
o.data_abertura <= '{$d_fim}' AND
s.sis_id IN ({$row['sis_id']})
{$clausule}
GROUP BY
area";
$resOpened = $conn->query($sqlOpened);
$rowOpened = $resOpened->fetch();
/**
* Quantidade de chamados com atendimento iniciado no período
*/
$sqlStarted = "SELECT
count(*) AS iniciados,
s.sistema AS area,
s.sis_id
FROM
ocorrencias AS o,
sistemas AS s,
`status` AS st
WHERE
o.status = st.stat_id AND
st.stat_ignored <> 1 AND
o.sistema = s.sis_id AND
o.data_atendimento >= '{$d_ini}' AND
o.data_atendimento <= '{$d_fim}' AND
s.sis_id IN ({$row['sis_id']})
{$clausule}
GROUP BY
area,
s.sis_id";
$resStarted = $conn->query($sqlStarted);
$rowStarted = $resStarted->fetch();
/**
* Quantidade de chamados concluídos (concluidos e encerrados) no período
*/
$sqlDone = "SELECT
count(*) AS concluidos,
s.sistema AS area,
s.sis_id
FROM
ocorrencias AS o,
sistemas AS s,
`status` AS st
WHERE
o.status = st.stat_id AND
o.status IN (4, {$doneStatus}) AND
st.stat_ignored <> 1 AND
o.sistema = s.sis_id AND
o.data_fechamento >= '{$d_ini}' AND
o.data_fechamento <= '{$d_fim}' AND
s.sis_id IN ({$row['sis_id']})
{$clausule}
GROUP BY
area,
s.sis_id";
$resDone = $conn->query($sqlDone);
$rowDone = $resDone->fetch();
/**
* Quantidade de chamados encerrados no período
*/
$sqlClosed = "SELECT
count(*) AS fechados,
s.sistema AS area,
s.sis_id
FROM
ocorrencias AS o,
sistemas AS s,
`status` AS st
WHERE
o.status = st.stat_id AND
o.status = 4 AND
st.stat_ignored <> 1 AND
o.sistema = s.sis_id AND
o.data_fechamento >= '{$d_ini}' AND
o.data_fechamento <= '{$d_fim}' AND
s.sis_id IN ({$row['sis_id']})
{$clausule}
GROUP BY
area,
s.sis_id";
$resClosed = $conn->query($sqlClosed);
$rowClosed = $resClosed->fetch();
/**
* Quantidade de chamados encerrados, de forma automática, no período
*/
$sqlAutoClosed = "SELECT
count(*) AS auto_fechados,
s.sistema AS area,
s.sis_id
FROM
ocorrencias AS o,
tickets_rated tr,
sistemas AS s,
`status` AS st
WHERE
o.status = st.stat_id AND
o.status = 4 AND
st.stat_ignored <> 1 AND
o.sistema = s.sis_id AND
o.numero = tr.ticket AND
tr.rate IS NOT NULL AND
tr.automatic_rate = 1 AND
tr.rate_date >= '{$d_ini}' AND
tr.rate_date <= '{$d_fim}' AND
s.sis_id IN ({$row['sis_id']})
{$clausule}
GROUP BY
area,
s.sis_id";
$resAutoClosed = $conn->query($sqlAutoClosed);
$rowAutoClosed = $resAutoClosed->fetch();
/**
* Quantidade de chamados avaliados no período
*/
$sqlRated = "SELECT
COUNT(tr.rate) AS avaliados,
s.sistema AS area,
s.sis_id
FROM
ocorrencias AS o,
tickets_rated tr,
sistemas AS s,
`status` AS st
WHERE
o.status = st.stat_id AND
o.status = 4 AND
st.stat_ignored <> 1 AND
o.sistema = s.sis_id AND
o.numero = tr.ticket AND
tr.rate IS NOT NULL AND
tr.rate_date >= '{$d_ini}' AND
tr.rate_date <= '{$d_fim}' AND
s.sis_id IN ({$row['sis_id']})
{$clausule}
GROUP BY
area,
s.sis_id";
$resRated = $conn->query($sqlRated);
$rowRated = $resRated->fetch();
/**
* Operações para contabilizar a quantidade de chamados em cada situação para cada área
*/
$tOpened += $totalOpened += $rowOpened['abertos'] ?? 0;
$tStarted += $totalStarted += $rowStarted['iniciados'] ?? 0;
$tDone += $totalDone += $rowDone['concluidos'] ?? 0;
$tClosed += $totalClosed += $rowClosed['fechados'] ?? 0;
$tAutoClosed += $totalAutoClosed += $rowAutoClosed['auto_fechados'] ?? 0;
$tRated += $totalRated += $rowRated['avaliados'] ?? 0;
$data[$i]['area'] = $row['sistema'];
$data[$i]['abertos'] = $totalOpened;
$data[$i]['iniciados'] = $totalStarted;
$data[$i]['concluidos'] = $totalDone;
$data[$i]['fechados'] = $totalClosed;
$data[$i]['auto_fechados'] = $totalAutoClosed;
$data[$i]['avaliados'] = $totalRated;
$totalOpened = 0;
$totalStarted = 0;
$totalDone = 0;
$totalClosed = 0;
$totalAutoClosed = 0;
$totalRated = 0;
?>