public function testCountAllEmployeeShouldBe2()
{
$employeeDAO = new EmployeeDAO();
$employeeDAO->setDatabaseConnection($this->pdo);
$allEmployee = $employeeDAO->countAll();
$this->assertEquals(2, $allEmployee);
}
function GetTechnicianName($sqlserverConnection, $technicianId)
{
$technicianName = "";
$employeeDAO = new EmployeeDAO($sqlserverConnection);
$employeeDAO->showErrors = 1;
$employee = $employeeDAO->RetrieveRecord($technicianId);
if ($employee != null) {
$technicianName = $employee->firstName . " " . $employee->middleName . " " . $employee->lastName;
}
return $technicianName;
}
</label>
<label class="left">Reset?<br/>
<input type="checkbox" name="reset" <?php
echo $reading->reset ? 'checked="checked"' : '';
?>
/>
</label>
<div style="clear:both;">
</div>
<label>Assinatura DataCopy<br />
<select name="assinaturaDatacopy">
<?php
$employeeDAO = new EmployeeDAO($dataConnector->sqlserverConnection);
$employeeArray = $employeeDAO->RetrieveRecordArray();
foreach ($employeeArray as $employee) {
$attributes = "";
if ($employee->empID == $reading->assinaturaDatacopy) {
$attributes = "selected='selected'";
}
echo "<option " . $attributes . " value=" . $employee->empID . ">" . $employee->firstName . " " . $employee->middleName . " " . $employee->lastName . "</option>";
}
?>
</select>
</label>
<label>Assinatura <?php
echo $clientName->Truncate(75);
?>
echo $equipment->counterFinalVal;
?>
" />
</label>
<div style="clear:both;">
<br/><br/><br/>
</div>
<label class="left">Responsável Técnico<br/>
<select name="technician" style="width: 350px;">
<?php
//SELECT OHEM.empID id, (OHEM.firstName + ' ' + OHEM.lastName) nome FROM HEM6
//JOIN OHEM ON HEM6.empID = OHEM.empID
//JOIN OHTY ON HEM6.roleID = OHTY.typeID WHERE roleID = -2 -- Técnico
echo '<option value="0" >-Nenhum técnico-</option>';
$employeeDAO = new EmployeeDAO($dataConnector->sqlserverConnection);
$employeeDAO->showErrors = 1;
$employeeArray = $employeeDAO->RetrieveEmployeesByPosition('Técnico', 'Tecnico');
foreach ($employeeArray as $employee) {
$attributes = "";
if ($employee->empID == $equipment->technician) {
$attributes = "selected='selected'";
}
$employeeName = $employee->firstName . " " . $employee->middleName . " " . $employee->lastName;
echo "<option " . $attributes . " value=" . $employee->empID . ">" . $employeeName . "</option>";
}
?>
</select>
</label>
<div style="clear:both;">
<br/>
<?php
require_once '../dao/EmployeeDAO.php';
require_once '../dto/EmployeeDTO.php';
$employeeDAO = new EmployeeDAO();
$employee = $employeeDAO->getEmployee();
?>
<html>
<head>
<meta charset="UTF-8">
<title></title>
</head>
<body>
<h1>Employers</h1>
<table border="1">
<thead>
<tr>
<th>ID</th>
<th>Fisrt Name</th>
<th>Last Name</th>
</tr>
</thead>
<tbody>
<?php
foreach ($employee as $value) {
?>
<tr>
<td><?php
echo $value->getEmployeeID();
?>
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);
}
}
$recipients = $_GET['recipients'];
$serviceCallId = $_GET['serviceCallId'];
// Abre a conexao com o banco de dados
$dataConnector = new DataConnector('both');
$dataConnector->OpenConnection();
if ($dataConnector->mysqlConnection == null || $dataConnector->sqlserverConnection == null) {
echo 'Não foi possível se connectar ao bando de dados!';
exit;
}
// Busca os dados do chamado
$serviceCallDAO = new ServiceCallDAO($dataConnector->mysqlConnection);
$serviceCallDAO->showErrors = 1;
$serviceCall = $serviceCallDAO->RetrieveRecord($serviceCallId);
// Busca os dados do técnico
$technicianName = "";
$employeeDAO = new EmployeeDAO($dataConnector->sqlserverConnection);
$employeeDAO->showErrors = 1;
$employee = $employeeDAO->RetrieveRecord($serviceCall->tecnico);
if ($employee != null) {
$technicianName = $employee->firstName . " " . $employee->middleName . " " . $employee->lastName;
}
// Traz as informações do equipamento
$shortDescription = '';
$model = '';
$equipmentDAO = new EquipmentDAO($dataConnector->sqlserverConnection);
$equipmentDAO->showErrors = 1;
$equipment = $equipmentDAO->RetrieveRecord($serviceCall->codigoCartaoEquipamento);
if ($equipment != null) {
$shortDescription = EquipmentDAO::GetShortDescription($equipment);
$model = $equipment->itemName;
}