function BuildReportTable($startColumn, $startRow) { global $dataConnector; global $objPhpExcel; global $serviceCallArray; $serviceCallDAO = new ServiceCallDAO($dataConnector->mysqlConnection); $serviceCallDAO->showErrors = 1; $equipmentDAO = new EquipmentDAO($dataConnector->sqlserverConnection); $equipmentDAO->showErrors = 1; $equipmentModelDAO = new EquipmentModelDAO($dataConnector->mysqlConnection); $equipmentModelDAO->showErrors = 1; $manufacturerDAO = new ManufacturerDAO($dataConnector->sqlserverConnection); $manufacturerDAO->showErrors = 1; $readingDAO = new ReadingDAO($dataConnector->mysqlConnection); $readingDAO->showErrors = 1; $counterDAO = new CounterDAO($dataConnector->mysqlConnection); $counterDAO->showErrors = 1; $employeeDAO = new EmployeeDAO($dataConnector->sqlserverConnection); $employeeDAO->showErrors = 1; // Define o titulo da tabela $currentRow = $startRow; $activeSheet = $objPhpExcel->getActiveSheet(); $activeSheet->setCellValue($startColumn . $startRow, 'CHAMADOS'); $styleArray = array('font' => array('bold' => true, 'size' => 16)); $activeSheet->getStyle($startColumn . $startRow . ':' . $startColumn . $startRow)->applyFromArray($styleArray); // Cria o cabeçalho da tabela $colNum = ord($startColumn); $headers = array('Data Abertura', 'Nº do chamado', 'Status', 'Cliente', 'Depto.', 'Modelo', 'Série', 'Fabricante', 'Defeito', 'Data Atendimento', 'Horário/Duração', 'Sintoma', 'Causa', 'Ação', 'Observação Técnica', 'Contadores', 'Aberto Por', 'Técnico'); $offset = 0; foreach ($headers as $header) { $activeSheet->getColumnDimension(chr($colNum + $offset))->setWidth(25); $offset++; } $activeSheet->getColumnDimension(chr($colNum + 3))->setWidth(50); $activeSheet->getColumnDimension(chr($colNum + 4))->setWidth(35); $activeSheet->getColumnDimension(chr($colNum + 8))->setWidth(60); $activeSheet->getColumnDimension(chr($colNum + 11))->setWidth(60); $activeSheet->getColumnDimension(chr($colNum + 12))->setWidth(60); $activeSheet->getColumnDimension(chr($colNum + 13))->setWidth(60); $activeSheet->getColumnDimension(chr($colNum + 14))->setWidth(35); $activeSheet->getColumnDimension(chr($colNum + 16))->setWidth(40); $activeSheet->getColumnDimension(chr($colNum + 17))->setWidth(40); $currentRow++; InsereLinhaPlanilha($currentRow, $startColumn, $headers, PHPExcel_Style_Color::COLOR_YELLOW, 30, PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // Busca os Status de chamado cadastrados no sistema $statusArray = ServiceCallDAO::RetrieveServiceCallStatuses($dataConnector->sqlserverConnection); // Busca os fabricantes cadastrados no sistema $manufacturerArray = array(0 => ""); $tempArray = $manufacturerDAO->RetrieveRecordArray(); foreach ($tempArray as $manufacturer) { $manufacturerArray[$manufacturer->FirmCode] = $manufacturer->FirmName; } // Busca os modelos de equipamento cadastrados no sistema $modelArray = array(0 => ""); $equipmentModelArray = $equipmentModelDAO->RetrieveRecordArray(); foreach ($equipmentModelArray as $modelDTO) { $modelArray[$modelDTO->id] = $modelDTO->modelo; } $associativeList = array(0 => ""); foreach ($equipmentModelArray as $modelDTO) { $associativeList[$modelDTO->id] = $manufacturerArray[$modelDTO->fabricante]; } // Busca os contadores cadastrados no sistema $retrievedArray = $counterDAO->RetrieveRecordArray(); $counterArray = array(); foreach ($retrievedArray as $counter) { $counterArray[$counter->id] = $counter->nome; } // Busca os funcionários cadastrados no sistema $retrievedArray = $employeeDAO->RetrieveRecordArray("empID IS NOT NULL ORDER BY empID"); $employeeArray = array(); foreach ($retrievedArray as $employee) { $employeeArray[$employee->empID] = $employee->firstName . " " . $employee->middleName . " " . $employee->lastName; } // Cria um array para as estatísticas dos técnicos $statsArray = array(0 => ""); foreach ($employeeArray as $id => $name) { // considera todos os funcionários como possíveis técnicos, depois filtra somente os que realizaram atendimentos $statsArray[$id] = new Technician($id, $name); } // Gera as linhas da tabela $quantChamados = 0; foreach ($serviceCallArray as $serviceCall) { $equipment = $equipmentDAO->RetrieveRecord($serviceCall->codigoCartaoEquipamento); if (!isset($equipment)) { $equipment = new EquipmentDTO(); } // cria objeto vazio em caso de erro $readingArray = $readingDAO->RetrieveRecordArray("chamadoServico_id=" . $serviceCall->id); $counters = ""; $LFCR = chr(10) . chr(13); foreach ($readingArray as $reading) { if (!empty($counters)) { $counters = $counters . $LFCR; } $counters = $counters . $counterArray[$reading->codigoContador] . ' ' . $reading->contagem; } $modelName = ""; if (array_key_exists($equipment->model, $modelArray)) { $modelName = $modelArray[$equipment->model]; } $manufacturerName = ""; if (array_key_exists($equipment->model, $modelArray)) { $manufacturerName = $associativeList[$equipment->model]; } $creator = " - "; if ($serviceCall->abertoPor > 0) { $creator = $employeeArray[$serviceCall->abertoPor]; } $technicianName = " - "; if ($serviceCall->tecnico > 0) { $technicianName = $employeeArray[$serviceCall->tecnico]; } $technicianStats = $statsArray[$serviceCall->tecnico]; if (!isset($technicianStats->statistics)) { $technicianStats->statistics = array(); } if (!array_key_exists($equipment->model, $technicianStats->statistics)) { $technicianStats->statistics[$equipment->model] = new EquipmentModelStats($equipment->model, $modelName, $manufacturerName); } $equipmentModelStat = $technicianStats->statistics[$equipment->model]; $equipmentModelStat->serviceCallCount++; $parts = explode(":", $serviceCall->tempoAtendimento, 2); $equipmentModelStat->tempoTotalAtendimento += (int) $parts[0] + (int) $parts[1] / 60; $currentRow++; $row = array(); $row[0] = $serviceCall->dataAbertura; $row[1] = str_pad($serviceCall->id, 5, '0', STR_PAD_LEFT); $row[2] = $statusArray[$serviceCall->status]; $row[3] = $equipment->custmrName; $row[4] = $equipment->instLocation; $row[5] = $modelName; $row[6] = $equipment->manufacturerSN; $row[7] = $manufacturerName; $row[8] = $serviceCall->defeito; $row[9] = $serviceCall->dataAtendimento; $row[10] = $serviceCall->horaAtendimento . " (Duração " . $serviceCall->tempoAtendimento . ")"; $row[11] = $serviceCall->sintoma; $row[12] = $serviceCall->causa; $row[13] = $serviceCall->acao; $row[14] = $serviceCall->observacaoTecnica; $row[15] = $counters; $row[16] = $creator; $row[17] = $technicianName; InsereLinhaPlanilha($currentRow, $startColumn, $row); $quantChamados++; // Faz a contagem dos chamados técnicos } $currentRow++; $footer = array('Quantidade de Chamados: ' . $quantChamados . ' (Depende do filtro escolhido)', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0'); InsereLinhaPlanilha($currentRow, $startColumn, $footer, '80BB80FF', 32, PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $activeSheet->mergeCells(chr($colNum + 0) . $currentRow . ':' . chr($colNum + sizeof($headers) - 1) . $currentRow); $currentRow += 3; // pula 3 linhas // Define o titulo do quadro resumo $activeSheet = $objPhpExcel->getActiveSheet(); $activeSheet->setCellValue($startColumn . $currentRow, 'QUADRO RESUMO'); $styleArray = array('font' => array('bold' => true, 'size' => 16)); $activeSheet->getStyle($startColumn . $currentRow . ':' . $startColumn . $currentRow)->applyFromArray($styleArray); // Cria o cabeçalho do quadro resumo $colNum = ord($startColumn); $headers = array('Técnico', 'Modelo', 'Fabricante', 'Quant. Chamados', 'Tempo Médio Atendimento'); $offset = 0; foreach ($headers as $header) { $activeSheet->getColumnDimension(chr($colNum + $offset))->setWidth(25); $offset++; } $currentRow++; InsereLinhaPlanilha($currentRow, $startColumn, $headers, PHPExcel_Style_Color::COLOR_YELLOW, 30, PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $callCount = 0; $timeSum = 0; foreach ($statsArray as $technicianStats) { if (!isset($technicianStats->statistics)) { continue; } $valorPrevio = ""; $serviceCallCount = 0; $tempoTotalAtendimento = 0; foreach ($technicianStats->statistics as $equipmentModelStats) { if ($equipmentModelStats->serviceCallCount > 0) { $valorAtual = $equipmentModelStats->fabricante; if ($valorPrevio != $valorAtual) { if (!empty($valorPrevio)) { $subTotal = array('', '', '', $callCount, UnixTime::ConvertToTime($timeSum / $callCount)); $currentRow++; InsereLinhaPlanilha($currentRow, $startColumn, $subTotal, '80AAFFFF', 20, PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $callCount = 0; $timeSum = 0; } $valorPrevio = $valorAtual; } $currentRow++; $row = array(); $row[0] = $technicianStats->name; $row[1] = $equipmentModelStats->model; $row[2] = $equipmentModelStats->fabricante; $row[3] = $equipmentModelStats->serviceCallCount; $row[4] = UnixTime::ConvertToTime($equipmentModelStats->tempoTotalAtendimento / $equipmentModelStats->serviceCallCount); InsereLinhaPlanilha($currentRow, $startColumn, $row); $callCount = $callCount + $equipmentModelStats->serviceCallCount; $timeSum = $timeSum + $equipmentModelStats->tempoTotalAtendimento; $serviceCallCount = $serviceCallCount + $equipmentModelStats->serviceCallCount; $tempoTotalAtendimento = $tempoTotalAtendimento + $equipmentModelStats->tempoTotalAtendimento; } } $subTotal = array('', '', '', $callCount, UnixTime::ConvertToTime($timeSum / $callCount)); $currentRow++; InsereLinhaPlanilha($currentRow, $startColumn, $subTotal, '80AAFFFF', 20, PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $callCount = 0; $timeSum = 0; $subTotal = array($technicianStats->name, '', '', $serviceCallCount, UnixTime::ConvertToTime($tempoTotalAtendimento / $serviceCallCount)); $currentRow++; InsereLinhaPlanilha($currentRow, $startColumn, $subTotal, 'FFAA2040', 20, PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $activeSheet->mergeCells(chr($colNum + 0) . $currentRow . ':' . chr($colNum + 2) . $currentRow); } }
if (sizeof($serviceCallArray) > 0) { echo '<tr bgcolor=WHITE ><td>Chamado</td><td>Defeito</td><td>Data Abertura</td><td>Técnico</td><td>Tempo Atendimento</td></tr>'; } else { echo '<tr bgcolor=WHITE ><td colspan=5 >Nenhum chamado encontrado</td></tr>'; } foreach ($serviceCallArray as $serviceCall) { $dataAbertura = strtotime($serviceCall->dataAbertura); $dataAbertura = date("d/m/Y", $dataAbertura); $tecnico = GetTechnicianName($dataConnector->sqlserverConnection, $serviceCall->tecnico); $tempoAtendimento = $serviceCall->tempoAtendimento; $parts = explode(":", $tempoAtendimento, 2); $tempoTotalAtendimento += (int) $parts[0] + (int) $parts[1] / 60; echo '<tr bgcolor=WHITE ><td>' . $serviceCall->id . '</td><td>' . $serviceCall->defeito . '</td><td>' . $dataAbertura . '</td><td>' . $tecnico . '</td><td>' . $tempoAtendimento . '</td></tr>'; } if (sizeof($serviceCallArray) > 0) { echo '<tr bgcolor=WHITE ><td colspan=5 >Tempo total de atendimento: ' . UnixTime::ConvertToTime($tempoTotalAtendimento) . '</td></tr>'; } } ?> </table> <div style="clear:both;"><br/></div> </div> <div style="clear:both;"><br/></div> <div id="pageBottom" style="height:12px;"></div> </div> <?php // Fecha a conexão com o banco de dados $dataConnector->CloseConnection(); ?>
<div style='width:8%;height:20px;float:left;'> <?php $assistanceDuration = $serviceCall->tempoAtendimento; if (isset($assistanceDuration)) { $split1 = explode(":", $assistanceDuration, 2); } else { $split1 = array(0, 0); } $entryTime = $serviceCall->horaAtendimento; if (isset($entryTime)) { $split2 = explode(":", $entryTime, 2); } else { $split2 = array(0, 0); } $exitTime = (int) $split1[0] + (int) $split1[1] / 60 + (int) $split2[0] + (int) $split2[1] / 60; $exitTime = UnixTime::ConvertToTime($exitTime); if ($assistanceDuration == "00:00") { echo " <hr style='margin:0;' />"; } else { echo " " . $exitTime; } ?> </div> <div style="clear:both;"><br/></div> <p style='margin:0; float:left;'> Observações:</p> <div style='width:80%;height:20px;float:left;'> <?php $comments = $serviceCall->observacaoTecnica; if (empty($comments)) { echo " <hr style='margin:0;' />"; } else {