/acao.php" method="post" >
<input type="hidden" name="acao" value="store" />
<input type="hidden" name="id" value="<?php
echo $id;
?>
" />
<input type="hidden" name="equipmentCode" value="<?php
echo $equipmentCode;
?>
" />
<label>Contador<br />
<select name="contador_id">
<?php
$counterArray = $counterDAO->RetrieveRecordArray();
foreach ($counterArray as $counter) {
$attributes = "";
if ($counter->id == $reading->codigoContador) {
$attributes = "selected='selected'";
}
echo '<option value=' . $counter->id . ' ' . $attributes . ' >' . $counter->nome . '</option>';
}
?>
</select>
</label>
<div class="left" style="font-weight:bold; margin-right:10px; margin-top:10px;">
Data<br/>
<input type="text" class="datepick" name="data" size="30" value="<?php
echo empty($reading->data) ? "" : $reading->data;
exit;
}
$nivelAutorizacao = GetAuthorizationLevel($dataConnector->mysqlConnection, $functionalities["gerenciamentoEquipmtPecas"]);
if ($nivelAutorizacao <= 1) {
DisplayNotAuthorizedWarning();
exit;
}
// Cria os objetos de mapeamento objeto-relacional
$inventoryItemDAO = new InventoryItemDAO($dataConnector->sqlserverConnection);
$inventoryItemDAO->showErrors = 1;
$counterDAO = new CounterDAO($dataConnector->mysqlConnection);
$counterDAO->showErrors = 1;
// Traz os grupos existentes no cadastro de items
$itemGroupArray = $inventoryItemDAO::GetItemGroups($dataConnector->sqlserverConnection);
// Traz os contadores cadastrados no sistema
$counterArray = $counterDAO->RetrieveRecordArray("id > 0 LIMIT 0, 3");
// exibe apenas os 3 primeiros
$itemCode = "";
$inventoryItem = new InventoryItemDTO();
if (isset($_REQUEST["itemCode"]) && $_REQUEST["itemCode"] != "") {
$itemCode = urldecode($_REQUEST["itemCode"]);
$inventoryItem = $inventoryItemDAO->RetrieveRecord($itemCode);
}
function BuildSerializedContent($content)
{
global $counterArray;
$xml = simplexml_load_string($content);
foreach ($counterArray as $counter) {
$aditionalAttributes = '';
foreach ($xml as $element) {
if ($counter->id == $element["id"]) {
function BuildReportTable($startColumn, $startRow)
{
global $dataConnector;
global $objPhpExcel;
global $billingItemArray;
global $salesPerson;
global $model;
global $searchMethod;
$billingItemDAO = new BillingItemDAO($dataConnector->mysqlConnection);
$billingItemDAO->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;
$counterDAO = new CounterDAO($dataConnector->mysqlConnection);
$counterDAO->showErrors = 1;
$salesPersonDAO = new SalesPersonDAO($dataConnector->sqlserverConnection);
$salesPersonDAO->showErrors = 1;
// Define o titulo da tabela
$currentRow = $startRow;
$activeSheet = $objPhpExcel->getActiveSheet();
$activeSheet->setCellValue($startColumn . $startRow, 'ITENS DE FATURAMENTO');
$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) - 65;
// startColumn é uma letra no intervalo A-Z
$headers = array('Cliente', 'Descrição Equipamento', 'Modelo', 'Fabricante', 'Série Fabricante', 'Nosso Núm. Série', 'Data Instalação', 'Inicio Atendimento', 'Fim Atendimento', 'Medidor', 'Data Leitura', 'Medição Final', 'Medição Inicial', 'Ajuste Medição(Acrésc/Desc)', 'Consumo', 'Franquia', 'Excedente', 'Tarifa sobre exced.', 'Fixo (R$)', 'Variável (R$)', 'Acrésc/Desc (R$)', 'Total (R$)', 'Parcela Atual', 'Vendedor');
$offset = 0;
foreach ($headers as $header) {
$activeSheet->getColumnDimension(GetNameFromNumber($colNum + $offset))->setWidth(30);
$offset++;
}
$currentRow++;
InsereLinhaPlanilha($currentRow, $startColumn, $headers, PHPExcel_Style_Color::COLOR_YELLOW, 30, PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// 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 vendedores cadastrados no sistema
$retrievedArray = $salesPersonDAO->RetrieveRecordArray();
$salesPersonArray = array();
foreach ($retrievedArray as $salesPersonDTO) {
$salesPersonArray[$salesPersonDTO->slpCode] = $salesPersonDTO->slpName;
}
// Gera as linhas da tabela
$grandTotal = 0;
foreach ($billingItemArray as $billingItem) {
$equipment = $equipmentDAO->RetrieveRecord($billingItem->codigoCartaoEquipamento);
$contractCoveragePeriod = ContractDAO::GetContractCoveragePeriod($dataConnector->mysqlConnection, $billingItem->contrato_id);
$inicioAtendimento = isset($contractCoveragePeriod) ? $contractCoveragePeriod["inicioAtendimento"] : '';
$fimAtendimento = isset($contractCoveragePeriod) ? $contractCoveragePeriod["fimAtendimento"] : '';
$parcelaAtual = isset($contractCoveragePeriod) ? $contractCoveragePeriod["parcelaAtual"] : '';
$receitaTotal = $billingItem->total + $billingItem->acrescimoDesconto;
if ($salesPerson > 0) {
if ($equipment->salesPerson != $salesPerson) {
continue;
}
}
if ($searchMethod == 1 || $searchMethod == 2) {
if (!empty($model)) {
$modelMatched = false;
if (strpos($equipment->itemName, $model)) {
$modelMatched = true;
}
if (!$modelMatched) {
continue;
}
}
}
$installationDate = empty($equipment->installationDate) ? '' : $equipment->installationDate->format('d/m/Y');
$salesPersonCode = $equipment->salesPerson;
if (empty($salesPersonCode)) {
$salesPersonCode = -1;
}
$salesPersonName = $salesPersonArray[$salesPersonCode];
$currentRow++;
$row = array();
$row[0] = $equipment->custmrName;
$row[1] = $equipment->itemName;
$row[2] = $modelArray[$equipment->model];
$row[3] = $associativeList[$equipment->model];
// Recupera o fabricante do modelo de equipamento
$row[4] = $equipment->manufacturerSN;
$row[5] = $equipment->internalSN;
$row[6] = $installationDate;
$row[7] = $inicioAtendimento;
$row[8] = $fimAtendimento;
$row[9] = $counterArray[$billingItem->counterId];
$row[10] = $billingItem->dataLeitura;
$row[11] = $billingItem->medicaoFinal;
$row[12] = $billingItem->medicaoInicial;
$row[13] = $billingItem->ajuste;
$row[14] = $billingItem->consumo;
$row[15] = $billingItem->franquia;
$row[16] = $billingItem->excedente;
$row[17] = number_format($billingItem->tarifaSobreExcedente, 6, ',', '.');
$row[18] = number_format($billingItem->fixo, 2, ',', '.');
$row[19] = number_format($billingItem->variavel, 2, ',', '.');
$row[20] = number_format($billingItem->acrescimoDesconto, 2, ',', '.');
$row[21] = number_format($receitaTotal, 2, ',', '.');
$row[22] = $parcelaAtual;
$row[23] = $salesPersonName;
InsereLinhaPlanilha($currentRow, $startColumn, $row);
$grandTotal += $receitaTotal;
// Soma a receita total do equipamento ( fixo mais variável )
}
$currentRow++;
$total = array('Total da Receita: ' . number_format($grandTotal, 2, ',', '.'), '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0');
InsereLinhaPlanilha($currentRow, $startColumn, $total, '80BB80FF', 45, PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->mergeCells(GetNameFromNumber($colNum + 0) . $currentRow . ':' . GetNameFromNumber($colNum + sizeof($headers) - 1) . $currentRow);
}
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);
}
}
function BuildReportTable($startColumn, $startRow)
{
global $root;
global $dataConnector;
global $objPhpExcel;
global $billingItemArray;
$businessPartnerDAO = new BusinessPartnerDAO($dataConnector->sqlserverConnection);
$businessPartnerDAO->showErrors = 1;
$industryDAO = new IndustryDAO($dataConnector->sqlserverConnection);
$industryDAO->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;
$contractDAO = new ContractDAO($dataConnector->mysqlConnection);
$contractDAO->showErrors = 1;
$inventoryItemDAO = new InventoryItemDAO($dataConnector->sqlserverConnection);
$inventoryItemDAO->showErrors = 1;
$counterDAO = new CounterDAO($dataConnector->mysqlConnection);
$counterDAO->showErrors = 1;
$salesPersonDAO = new SalesPersonDAO($dataConnector->sqlserverConnection);
$salesPersonDAO->showErrors = 1;
// Busca os segmentos/ramos de atividade cadastrados no sistema
$industryArray = array(0 => "");
$tempArray = $industryDAO->RetrieveRecordArray();
foreach ($tempArray as $industry) {
$industryArray[$industry->id] = $industry->name;
}
// 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;
}
// Cria um array para as estatísticas dos equipamentos (por modelo)
$statsArray = array();
$equipmentModelArray = $equipmentModelDAO->RetrieveRecordArray("id > 0 ORDER BY fabricante, modelo");
foreach ($equipmentModelArray as $modelDTO) {
$statsArray[$modelDTO->id] = new EquipmentModelStats($modelDTO->id, $modelDTO->modelo, $modelDTO->fabricante);
}
// Cria um array para as receitas de cada equipamento
$equipRevenueArray = array();
// Cria um array para as receitas provenientes de cada cliente
$customerRevenueArray = array();
$associativeList = array(0 => "");
foreach ($equipmentModelArray as $model) {
$associativeList[$model->id] = $manufacturerArray[$model->fabricante];
}
// Busca os contadores cadastrados no sistema
$retrievedArray = $counterDAO->RetrieveRecordArray();
$counterArray = array();
foreach ($retrievedArray as $counter) {
$counterArray[$counter->id] = $counter->nome;
}
// Busca os vendedores cadastrados no sistema
$retrievedArray = $salesPersonDAO->RetrieveRecordArray();
$salesPersonArray = array();
foreach ($retrievedArray as $salesPersonDTO) {
$salesPersonArray[$salesPersonDTO->slpCode] = $salesPersonDTO->slpName;
}
// Define o titulo da tabela
$currentRow = $startRow;
$activeSheet = $objPhpExcel->getActiveSheet();
$activeSheet->setCellValue($startColumn . $startRow, 'SÍNTESE POR EQUIPAMENTO');
$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) - 65;
// startColumn é uma letra no intervalo A-Z
$headers = array('Cliente', 'Segmento', 'Modelo', 'Fabricante', 'Série Fabricante', 'Nosso Núm. Série', 'Obs. Item', 'Data Instalação', 'Inicio Atendimento', 'Fim Atendimento', 'Parcela Atual', 'Vendedor', 'Medidor', 'Data Leitura', 'Medição Final', 'Medição Inicial', 'Ajuste Medição(Acrésc/Desc)', 'Consumo', 'Franquia', 'Excedente (Págs.)', 'Tarifa sobre exced.', 'Valor Fixo (R$)', 'Valor Variável (R$)', 'Acrésc/Desc (R$)', 'Valor Total (R$)', 'Custo Aquisição', 'Vida Útil', 'Custo Pág. do Equip.', 'Custo Pág. Peças/Mat.', 'Custo Pág. Total', 'Custo/Receita', 'Despesas');
$offset = 0;
foreach ($headers as $header) {
$activeSheet->getColumnDimension(GetNameFromNumber($colNum + $offset))->setWidth(30);
$offset++;
}
$currentRow++;
InsereLinhaPlanilha($currentRow, $startColumn, $headers, PHPExcel_Style_Color::COLOR_YELLOW, 30, PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// Gera as linhas da tabela
$totalFixo = 0;
$totalVariavel = 0;
$totalAcrescDesc = 0;
$grandTotal = 0;
foreach ($billingItemArray as $billingItem) {
$equipment = $equipmentDAO->RetrieveRecord($billingItem->codigoCartaoEquipamento);
$businessPartner = $businessPartnerDAO->RetrieveRecord($equipment->customer);
$contractCoveragePeriod = ContractDAO::GetContractCoveragePeriod($dataConnector->mysqlConnection, $billingItem->contrato_id);
$installationDate = empty($equipment->installationDate) ? '' : $equipment->installationDate->format('d/m/Y');
$inicioAtendimento = isset($contractCoveragePeriod) ? $contractCoveragePeriod["inicioAtendimento"] : '';
$fimAtendimento = isset($contractCoveragePeriod) ? $contractCoveragePeriod["fimAtendimento"] : '';
$parcelaAtual = isset($contractCoveragePeriod) ? $contractCoveragePeriod["parcelaAtual"] : '';
$receitaTotal = $billingItem->total + $billingItem->acrescimoDesconto;
$inventoryItem = $inventoryItemDAO->RetrieveRecord($equipment->itemCode);
$obsItem = $inventoryItem->userText;
$custoAquisicao = $inventoryItem->avgPrice;
$custoPagPecas = $inventoryItem->expenses;
$vidaUtil = $inventoryItem->durability;
if (empty($vidaUtil)) {
$custoPagEquip = 0;
$custoTotal = 0;
$custoSobreReceita = 0;
} else {
$custoPagEquip = $custoAquisicao / $vidaUtil * 1.4;
$custoTotal = ($custoPagEquip + $custoPagPecas) * $billingItem->consumo;
$custoSobreReceita = 0;
if ($receitaTotal != 0) {
$custoSobreReceita = $custoTotal / $receitaTotal;
}
}
// Busca os dados do vendedor
$salesPersonCode = $equipment->salesPerson;
if (empty($salesPersonCode)) {
$salesPersonCode = -1;
}
$salesPersonName = $salesPersonArray[$salesPersonCode];
// Busca os dados do segmento/ramo de atividade
$industryCode = $businessPartner->industry;
if (empty($industryCode)) {
$industryCode = 0;
}
$industryName = $industryArray[$industryCode];
// Busca o modelo e fabricante do equipamento
$modelName = "";
if (array_key_exists($equipment->model, $modelArray)) {
$modelName = $modelArray[$equipment->model];
}
$manufacturerName = "";
if (array_key_exists($equipment->model, $modelArray)) {
$manufacturerName = $associativeList[$equipment->model];
}
if (!array_key_exists($equipment->manufacturerSN, $equipRevenueArray)) {
$equipRevenueArray[$equipment->manufacturerSN] = new EquipmentRevenue($equipment->manufacturerSN, $equipment->model, $modelName, $manufacturerName, 0);
}
$equipRevenue = $equipRevenueArray[$equipment->manufacturerSN];
$equipRevenue->revenue += $receitaTotal;
if (!array_key_exists($equipment->customer, $customerRevenueArray)) {
$customerRevenueArray[$equipment->customer] = new CustomerRevenue($equipment->customer, 0);
}
$customerRevenue = $customerRevenueArray[$equipment->customer];
$customerRevenue->revenue += $receitaTotal;
$row = array();
$row[0] = $businessPartner->cardName . ' (' . $businessPartner->cardCode . ')';
$row[1] = $industryName;
$row[2] = $modelName;
$row[3] = $manufacturerName;
$row[4] = $equipment->manufacturerSN;
$row[5] = $equipment->internalSN;
$row[6] = $obsItem;
$row[7] = $installationDate;
$row[8] = $inicioAtendimento;
$row[9] = $fimAtendimento;
$row[10] = $parcelaAtual;
$row[11] = $salesPersonName;
$row[12] = $counterArray[$billingItem->counterId];
// Recupera o nome do medidor/contador
$row[13] = $billingItem->dataLeitura;
$row[14] = $billingItem->medicaoFinal;
$row[15] = $billingItem->medicaoInicial;
$row[16] = $billingItem->ajuste;
$row[17] = $billingItem->consumo;
$row[18] = $billingItem->franquia;
$row[19] = $billingItem->excedente;
$row[20] = formatDecimal($billingItem->tarifaSobreExcedente, null);
$row[21] = formatBrCurrency($billingItem->fixo, 2);
$row[22] = formatBrCurrency($billingItem->variavel, 2);
$row[23] = formatBrCurrency($billingItem->acrescimoDesconto, 2);
$row[24] = formatBrCurrency($receitaTotal, 2);
$row[25] = formatBrCurrency($custoAquisicao, 2);
$row[26] = $vidaUtil;
$row[27] = formatDecimal($custoPagEquip, 6);
$row[28] = formatDecimal($custoPagPecas, 6);
$row[29] = formatBrCurrency($custoTotal, 2);
$row[30] = formatDecimal($custoSobreReceita, 6);
$row[31] = 'http://datadb/contratos/AjaxCalls/GetEquipmentExpenses.php?equipmentCode=' . $equipment->insID . '&billingId=' . $billingItem->codigoFaturamento . '&showDetails=true';
$currentRow++;
InsereLinhaPlanilha($currentRow, $startColumn, $row);
$totalFixo += $billingItem->fixo;
// Soma os valores fixos dos equipamentos
$totalVariavel += $billingItem->variavel;
// Soma os valores variáveis dos equipamentos
$totalAcrescDesc += $billingItem->acrescimoDesconto;
// Soma os acrescimos descontos dos equipamentos
$grandTotal += $receitaTotal;
// Soma a receita total dos equipamentos ( fixo mais variável )
}
$currentRow++;
$summary = array('Quant. Clientes: ' . sizeof($customerRevenueArray), '', '', '', 'Quant. Equipamentos: ' . sizeof($equipRevenueArray), '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'Total Fixo: ' . number_format($totalFixo, 2, ',', '.'), 'Total Variável: ' . number_format($totalVariavel, 2, ',', '.'), 'Total Acrésc/Desc: ' . number_format($totalAcrescDesc, 2, ',', '.'), 'Total Receita: ' . number_format($grandTotal, 2, ',', '.'), '', '', '', '', '', '', '');
InsereLinhaPlanilha($currentRow, $startColumn, $summary, '80BB80FF', 32, PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
foreach ($equipRevenueArray as $equipRevenue) {
if (array_key_exists($equipRevenue->modelId, $statsArray)) {
$modelStats = $statsArray[$equipRevenue->modelId];
$modelStats->revenue += $equipRevenue->revenue;
$modelStats->equipmentCount += 1;
}
}
$currentRow += 3;
// pula 3 linhas
$startColumn = 'G';
// fixa o quadro resumo na setima coluna da planilha
// 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('Modelo', 'Fabricante', 'Quantidade', 'Receita');
$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);
$valorPrevio = "";
$equipCount = 0;
$somaReceita = 0;
foreach ($statsArray as $equipmentModelStats) {
if ($equipmentModelStats->equipmentCount > 0) {
$manufacturerName = $manufacturerArray[$equipmentModelStats->fabricante];
$valorAtual = $manufacturerName;
if ($valorPrevio != $valorAtual) {
if (!empty($valorPrevio)) {
$subTotal = array('', '', $equipCount, number_format($somaReceita, 2, ',', '.'));
$currentRow++;
InsereLinhaPlanilha($currentRow, $startColumn, $subTotal, '80AAFFFF', 20, PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$equipCount = 0;
$somaReceita = 0;
}
$valorPrevio = $valorAtual;
}
$currentRow++;
$row = array();
$row[0] = $equipmentModelStats->model;
$row[1] = $manufacturerName;
$row[2] = $equipmentModelStats->equipmentCount;
$row[3] = number_format($equipmentModelStats->revenue, 2, ',', '.');
InsereLinhaPlanilha($currentRow, $startColumn, $row);
$equipCount = $equipCount + $equipmentModelStats->equipmentCount;
$somaReceita = $somaReceita + $equipmentModelStats->revenue;
}
}
$subTotal = array('', '', $equipCount, number_format($somaReceita, 2, ',', '.'));
$currentRow++;
InsereLinhaPlanilha($currentRow, $startColumn, $subTotal, '80AAFFFF', 20, PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
function BillingEngine($dataConnector, $listaSubContratos, $itemArray, $startDate = null, $endDate = null)
{
$this->listaSubContratos = $listaSubContratos;
$this->arrayItens = $itemArray;
$this->consumos = array();
$this->franquias = array();
// Busca os contadores cadastrados no sistema
$counterDAO = new CounterDAO($dataConnector->mysqlConnection);
$counterDAO->showErrors = 1;
$counterArray = $counterDAO->RetrieveRecordArray();
$this->contadores = array();
foreach ($counterArray as $counter) {
$this->contadores[$counter->id] = $counter->nome;
}
// Busca os tipos de contrato cadastrados no sistema
$contractTypeDAO = new ContractTypeDAO($dataConnector->mysqlConnection);
$contractTypeDAO->showErrors = 1;
$contractTypeArray = $contractTypeDAO->RetrieveRecordArray();
$this->tiposContrato = array();
foreach ($contractTypeArray as $contractType) {
$this->tiposContrato[$contractType->id] = $contractType->nome . ' (' . $contractType->sigla . ')';
}
// Busca as formas de cobrança cadastradas nos subcontratos
$contractChargeDAO = new ContractChargeDAO($dataConnector->mysqlConnection);
$contractChargeDAO->showErrors = 1;
$chargeArray = $contractChargeDAO->RetrieveRecordArray("subContrato_id IN (" . $listaSubContratos . ")");
$this->formasCobranca = array();
foreach ($chargeArray as $contractCharge) {
$key = $contractCharge->codigoSubContrato . '-' . $contractCharge->codigoContador;
$this->formasCobranca[$key] = new ChargeFormula($contractCharge->modalidadeMedicao, $contractCharge->fixo, $contractCharge->variavel, $contractCharge->franquia, $contractCharge->individual);
if (!array_key_exists($contractCharge->codigoContador, $this->franquias)) {
$this->franquias[$contractCharge->codigoContador] = 0;
}
$this->franquias[$contractCharge->codigoContador] += $contractCharge->franquia;
}
// Busca os preços por faixa de consumo
$contractBonusDAO = new ContractBonusDAO($dataConnector->mysqlConnection);
$contractBonusDAO->showErrors = 1;
$bonusArray = $contractBonusDAO->RetrieveRecordArray("subContrato_id IN (" . $listaSubContratos . ")");
$this->precosFaixaConsumo = array();
foreach ($bonusArray as $bonus) {
$this->precosFaixaConsumo[$bonus->id] = new PriceByVolume($bonus->codigoSubContrato, $bonus->codigoContador, $bonus->de, $bonus->ate, $bonus->valor);
}
// Busca o consumo para os contadores de cada equipamento
$readingDAO = new ReadingDAO($dataConnector->mysqlConnection);
$readingDAO->showErrors = 1;
foreach ($itemArray as $contractItem) {
$equipamento_id = $contractItem->codigoCartaoEquipamento;
$keys = array_keys($this->contadores);
foreach ($keys as $contador_id) {
$ajusteReset = 0;
$readingArray = array();
$arrLen = 0;
if ($startDate != null && $endDate != null) {
// Recupera as leituras no período, desconsiderando resets de contador como leitura final
$query = "codigoCartaoEquipamento=" . $equipamento_id . " AND contador_id=" . $contador_id . " AND data >= '" . $startDate . " 00:00' AND data <= '" . $endDate . " 23:59' AND origemLeitura_id=2 ORDER BY leitura.data DESC";
$tempArray = $readingDAO->RetrieveRecordArray($query);
$firstReading = null;
$lastReading = null;
foreach ($tempArray as $dto) {
if ($arrLen == 1 && $dto->reset) {
// procura a leitura inicial, depois de ter adicionado a final
$firstReading = $dto;
array_push($readingArray, $dto);
$arrLen++;
}
if ($arrLen == 0 && !$dto->reset) {
// procura a leitura final
$lastReading = $dto;
array_push($readingArray, $dto);
$arrLen++;
}
}
if ($lastReading != null) {
// Completa a faixa de leituras
$tempLen = sizeof($tempArray);
$firstReadingBeforeReset = $tempArray[$tempLen - 1];
if ($firstReading != null && $firstReading->data != $firstReadingBeforeReset->data) {
$lastReadingBeforeReset = null;
foreach (array_reverse($tempArray) as $dto) {
if ($dto->reset) {
break;
}
$lastReadingBeforeReset = $dto;
}
$ajusteReset = $lastReadingBeforeReset->contagem - $firstReadingBeforeReset->contagem;
}
if ($firstReading == null) {
array_push($readingArray, $firstReadingBeforeReset);
$arrLen++;
}
}
} else {
// Recupera as duas últimas leituras, desconsiderando resets de contador como leitura final
$query = "codigoCartaoEquipamento=" . $equipamento_id . " AND contador_id=" . $contador_id . " AND origemLeitura_id=2 ORDER BY leitura.data DESC LIMIT 0, 10";
$tempArray = $readingDAO->RetrieveRecordArray($query);
foreach ($tempArray as $dto) {
if ($arrLen == 1) {
// procura a leitura inicial, depois de ter adicionado a final
array_push($readingArray, $dto);
$arrLen++;
}
if ($arrLen == 0 && !$dto->reset) {
// procura a leitura final
array_push($readingArray, $dto);
$arrLen++;
}
}
}
if ($arrLen == 1) {
// caso tenha apenas uma leitura
$reading = $readingArray[0];
$dataLeitura = strtotime($reading->data);
$medicaoInicial = 0;
$medicaoFinal = $reading->contagem;
$key = $equipamento_id . '-' . $contador_id;
$this->consumos[$key] = new ConsumptionMeasure($equipamento_id, $contador_id, $dataLeitura, $medicaoInicial, $medicaoFinal);
$this->consumos[$key]->ajusteMedicaoInicial = 0;
$this->consumos[$key]->ajusteMedicaoFinal = $reading->ajusteContagem;
}
if ($arrLen > 1) {
// caso tenha mais de uma leitura (ordenação decrescente)
$initialReading = $readingArray[$arrLen - 1];
$finalReading = $readingArray[0];
$dataLeitura = strtotime($finalReading->data);
$medicaoInicial = $initialReading->contagem;
$medicaoFinal = $finalReading->contagem;
$key = $equipamento_id . '-' . $contador_id;
$this->consumos[$key] = new ConsumptionMeasure($equipamento_id, $contador_id, $dataLeitura, $medicaoInicial, $medicaoFinal);
$this->consumos[$key]->ajusteMedicaoInicial = $initialReading->ajusteContagem;
$this->consumos[$key]->ajusteMedicaoFinal = $finalReading->ajusteContagem;
}
if ($arrLen > 0) {
// adiciona o total (consumo) caso exista alguma leitura
$formaCobranca = $this->GetFormaCobranca($contractItem->codigoSubContrato, $contador_id);
if ($formaCobranca != null && $formaCobranca->modalidadeMedicao == 2) {
// Leitura simples (apenas a final)
$this->consumos[$key]->medicaoInicial = 0;
}
// o ajuste na medição inicial foi computado no faturamento anterior
$ajuste = $this->consumos[$key]->ajusteMedicaoFinal + $ajusteReset;
$this->consumos[$key]->total = $this->consumos[$key]->medicaoFinal + $ajuste - $this->consumos[$key]->medicaoInicial;
$this->consumos[$key]->ajusteTotal = $ajuste;
}
}
// foreach de contadores
}
// foreach de itens
}
