}
// 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;
}
// Cria os objetos de mapeamento objeto-relacional
$laborExpenseDAO = new LaborExpenseDAO($dataConnector->sqlserverConnection);
$laborExpenseDAO->showErrors = 1;
$serviceCallDAO = new ServiceCallDAO($dataConnector->mysqlConnection);
$serviceCallDAO->showErrors = 1;
$serviceStatisticsDAO = new ServiceStatisticsDAO($dataConnector->mysqlConnection);
$serviceStatisticsDAO->showErrors = 1;
$stats = $serviceStatisticsDAO->RetrieveRecord($month, $year);
$tempoTotalAtendimento = $stats->tempoEmAtendimento;
$totalEmSegundos = $stats->totalEmSegundos;
// Busca os registros que se enquadram no filtro aplicado
if ($searchMethod == 0) {
$filter = "codigoCliente='" . $businessPartnerCode . "'";
if (empty($businessPartnerCode)) {
$filter = "codigoCliente <> ''";
}
// qualquer cliente
}
if ($searchMethod == 1) {
$filter = "codigoModelo=" . $model;
if (empty($model)) {
$filter = "codigoModelo <> ''";
}
function BuildReportTable($startColumn, $startRow)
{
global $dataConnector;
global $objPhpExcel;
global $month;
global $year;
global $despesaMensal;
global $laborExpenseArray;
$serviceCallDAO = new ServiceCallDAO($dataConnector->mysqlConnection);
$serviceCallDAO->showErrors = 1;
$serviceStatisticsDAO = new ServiceStatisticsDAO($dataConnector->mysqlConnection);
$serviceStatisticsDAO->showErrors = 1;
// Define o titulo da tabela
$currentRow = $startRow;
$activeSheet = $objPhpExcel->getActiveSheet();
$activeSheet->setCellValue($startColumn . $startRow, 'CUSTOS DE MÃO DE OBRA');
$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('Cliente', 'Nº Série Fabricante', 'Modelo', 'Fabricante', 'Número do Chamado', 'Tempo de Atendimento', 'Tempo Total Area Técnica', 'Valor Despesa (R$)');
$offset = 0;
foreach ($headers as $header) {
$activeSheet->getColumnDimension(chr($colNum + $offset))->setWidth(25);
$offset++;
}
$activeSheet->getColumnDimension(chr($colNum + 3))->setWidth(60);
$currentRow++;
InsereLinhaPlanilha($currentRow, $startColumn, $headers, PHPExcel_Style_Color::COLOR_YELLOW, 30, PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// Busca as estatísticas de atendimento do mês escolhido
$stats = $serviceStatisticsDAO->RetrieveRecord($month, $year);
$tempoTotalAtendimento = $stats->tempoEmAtendimento;
$totalEmSegundos = $stats->totalEmSegundos;
// Gera as linhas da tabela
$grandTotal = 0;
$valorPrevio = "";
$totalCliente = 0;
foreach ($laborExpenseArray as $laborExpense) {
$serviceCall = $serviceCallDAO->RetrieveRecord($laborExpense->numeroChamado);
$valorDespesa = $serviceCall->duracaoEmSegundos / $totalEmSegundos * $despesaMensal;
$valorAtual = $laborExpense->nomeCliente;
if ($valorPrevio != $valorAtual) {
if (!empty($valorPrevio)) {
$currentRow++;
$subTotal = array($valorPrevio, '', '', '', '', '', '', number_format($totalCliente, 2, ',', '.'));
InsereLinhaPlanilha($currentRow, $startColumn, $subTotal, '80AAFFFF', 20, PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$totalCliente = 0;
}
$valorPrevio = $valorAtual;
}
$currentRow++;
$row = array();
$row[0] = $laborExpense->nomeCliente;
$row[1] = $laborExpense->serieEquipamento;
$row[2] = $laborExpense->tagModelo;
$row[3] = $laborExpense->fabricante;
$row[4] = $laborExpense->numeroChamado;
$row[5] = $serviceCall->tempoAtendimento;
$row[6] = $tempoTotalAtendimento;
$row[7] = number_format($valorDespesa, 2, ',', '.');
InsereLinhaPlanilha($currentRow, $startColumn, $row);
$grandTotal += $valorDespesa;
$totalCliente += $valorDespesa;
}
$currentRow++;
$subTotal = array($valorPrevio, '', '', '', '', '', '', number_format($totalCliente, 2, ',', '.'));
InsereLinhaPlanilha($currentRow, $startColumn, $subTotal, '80AAFFFF', 20, PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$currentRow++;
$total = array('Total Geral: ' . number_format($grandTotal, 2, ',', '.'), '0', '0', '0', '0', '0', '0', '0');
InsereLinhaPlanilha($currentRow, $startColumn, $total, 'FFAA2040', 32, PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->mergeCells(chr($colNum + 0) . $currentRow . ':' . chr($colNum + sizeof($headers) - 1) . $currentRow);
}