function EquipmentOperator($sqlserverConnection, $businessPartnerCode, $equipmentCode) { // Cria os objetos de mapeamento objeto relacional $businessPartnerDAO = new BusinessPartnerDAO($sqlserverConnection); $businessPartnerDAO->showErrors = 1; $equipmentDAO = new EquipmentDAO($sqlserverConnection); $equipmentDAO->showErrors = 1; $contactPersonDAO = new ContactPersonDAO($sqlserverConnection); $contactPersonDAO->showErrors = 1; // Recupera os dados do parceiro de negócios $businessPartner = $businessPartnerDAO->RetrieveRecord($businessPartnerCode); if ($businessPartner != null) { $this->businessPartnerCode = $businessPartner->cardCode; $this->businessPartnerName = $businessPartner->cardName; $this->telephoneNumber = $businessPartner->telephoneNumber; } // Recupera os dados do equipamento $equipment = $equipmentDAO->RetrieveRecord($equipmentCode); if ($equipment != null) { $contactPerson = null; if (!empty($equipment->contactPerson)) { $contactPerson = $contactPersonDAO->RetrieveRecord($equipment->contactPerson); } if ($contactPerson != null) { $contactTel = trim($contactPerson->phoneNumber); if (!empty($contactTel)) { $this->operatorName = $contactPerson->name; $this->telephoneNumber = $contactPerson->phoneNumber; } } $this->equipmentCode = $equipment->insID; $this->serialNumber = $equipment->manufacturerSN; $this->equipmentStatus = $equipment->status; } }
echo 'Não foi possível se connectar ao bando de dados!'; exit; } $nivelAutorizacao = GetAuthorizationLevel($dataConnector->mysqlConnection, $functionalities["gerenciamentoLeituras"]); if ($nivelAutorizacao <= 1) { DisplayNotAuthorizedWarning(); exit; } // Cria os objetos de mapeamento objeto-relacional $readingDAO = new ReadingDAO($dataConnector->mysqlConnection); $readingDAO->showErrors = 1; $counterDAO = new CounterDAO($dataConnector->mysqlConnection); $counterDAO->showErrors = 1; $equipmentDAO = new EquipmentDAO($dataConnector->sqlserverConnection); $equipmentDAO->showErrors = 1; $equipment = $equipmentDAO->RetrieveRecord($equipmentCode); $capacity = $equipment->capacity; $serialNumber = $equipment->manufacturerSN . " ( " . $equipment->internalSN . " ) "; $clientName = new Text(BusinessPartnerDAO::GetClientName($dataConnector->sqlserverConnection, $equipment->customer)); $id = 0; $reading = new ReadingDTO(); if (isset($_REQUEST["id"]) && $_REQUEST["id"] != 0) { $id = $_REQUEST["id"]; $reading = $readingDAO->RetrieveRecord($id); } ?> <h1>Administração - Leitura - <?php echo $serialNumber; ?> </h1><br/>
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); }
} // qualquer cliente $filter2 = "modelo='" . $modelName . "'"; if (empty($model)) { $filter2 = "modelo <> ''"; } // qualquer modelo $filter = $filter1 . " AND " . $filter2; } if ($searchMethod == 3) { $filter1 = "businessPartnerCode='" . $businessPartnerCode . "'"; if (empty($businessPartnerCode)) { $filter1 = "businessPartnerCode <> ''"; } // qualquer cliente $equipment = $equipmentDAO->RetrieveRecord($equipmentCode); $manufSN = $equipment->manufacturerSN; $equipmentArray = $equipmentDAO->RetrieveRecordArray("manufSN='" . $manufSN . "'"); $equipmentEnumeration = ""; foreach ($equipmentArray as $equipment) { if (!empty($equipmentEnumeration)) { $equipmentEnumeration = $equipmentEnumeration . ", "; } $equipmentEnumeration = $equipmentEnumeration . $equipment->insID; } if (empty($equipmentEnumeration)) { $equipmentEnumeration = "0"; } // evita o crash da query, quando a lista está vazia $filter2 = "cartaoEquipamento IN (" . $equipmentEnumeration . ")"; $filter = $filter1 . " AND " . $filter2;
} // Cria os objetos de mapeamento objeto-relacional $serviceCallDAO = new ServiceCallDAO($dataConnector->mysqlConnection); $serviceCallDAO->showErrors = 1; $equipmentDAO = new EquipmentDAO($dataConnector->sqlserverConnection); $equipmentDAO->showErrors = 1; $equipmentModelDAO = new EquipmentModelDAO($dataConnector->mysqlConnection); $equipmentModelDAO->showErrors = 1; if ($acao == "store") { $id = 0; $serviceCall = new ServiceCallDTO(); if (isset($_REQUEST["id"]) && $_REQUEST["id"] != 0) { $id = $_REQUEST["id"]; $serviceCall = $serviceCallDAO->RetrieveRecord($id); } $equipment = $equipmentDAO->RetrieveRecord($_REQUEST["cartaoEquipamento"]); $equipmentModel = $equipmentModelDAO->RetrieveRecord($equipment->model); if (!isset($equipmentModel)) { echo 'O equipamento ' . $equipment->manufacturerSN . ' possui um erro de cadastro. Favor corrigir o modelo do equipamento.'; exit; } $modelName = $equipmentModel->modelo; $manufacturerName = ManufacturerDAO::GetManufacturerName($dataConnector->sqlserverConnection, $equipmentModel->fabricante); $serviceCall->defeito = $_REQUEST["defeito"]; $serviceCall->dataAbertura = $_REQUEST["dataAbertura"]; $serviceCall->horaAbertura = $_REQUEST["horaAbertura"]; $serviceCall->dataFechamento = $_REQUEST["dataFechamento"]; $serviceCall->horaFechamento = $_REQUEST["horaFechamento"]; $serviceCall->dataAtendimento = $_REQUEST["dataAtendimento"]; $serviceCall->horaAtendimento = $_REQUEST["horaAtendimento"]; $serviceCall->tempoAtendimento = $_REQUEST["tempoAtendimento"];
$numero = str_pad($contract->numero, 5, '0', STR_PAD_LEFT); $clientName = BusinessPartnerDAO::GetClientName($dataConnector->sqlserverConnection, $contract->pn); $salesPersonName = $salesPersonArray[$contract->vendedor]; $status = $contractDAO->GetStatusAsText($contract->status); $parcela = $contract->parcelaAtual . '/' . $contract->quantidadeParcelas; $global = $contract->global == 0 ? 'N' : 'S'; $details = $clientName . ' Vendedor ' . $salesPersonName; $subContractArray = $subContractDAO->RetrieveRecordArray("contrato_id=" . $contract->id); foreach ($subContractArray as $subContract) { if (!empty($details)) { $details = $details . '<br/>'; } $details = $details . $subContract->siglaTipoContrato; $itemArray = $contractItemDAO->RetrieveRecordArray("subContrato_id=" . $subContract->id); foreach ($itemArray as $contractItem) { $equipment = $equipmentDAO->RetrieveRecord($contractItem->codigoCartaoEquipamento); $installationDate = empty($equipment->installationDate) ? '' : $equipment->installationDate->format('d/m/Y'); // filtra apenas os items ativos e emprestados if ($equipment->status == 'A' || $equipment->status == 'L') { if (!empty($details)) { $details = $details . '<br/>'; } $equipmentModel = ""; if (array_key_exists($equipment->model, $modelArray)) { $equipmentModel = $modelArray[$equipment->model]; } $details = $details . $equipmentModel . ' Série ' . $equipment->manufacturerSN . ' Data Instalação ' . $installationDate; } } } if ($searchMethod == 1 || $searchMethod == 2) {
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 BuildRows($valuesOnly) { global $dataConnector; global $itemArray; global $calculoFaturamento; global $totalContadores; // Cria os objetos de mapeamento objeto-relacional $subContractDAO = new SubContractDAO($dataConnector->mysqlConnection); $subContractDAO->showErrors = 1; $businessPartnerDAO = new BusinessPartnerDAO($dataConnector->sqlserverConnection); $businessPartnerDAO->showErrors = 1; $equipmentDAO = new EquipmentDAO($dataConnector->sqlserverConnection); $equipmentDAO->showErrors = 1; $inventoryItemDAO = new InventoryItemDAO($dataConnector->sqlserverConnection); $inventoryItemDAO->showErrors = 1; // Para cada equipamento monta os dados de faturamento foreach ($itemArray as $contractItem) { $equipmentId = $contractItem->codigoCartaoEquipamento; $subContract = $subContractDAO->RetrieveRecord($contractItem->codigoSubContrato); $contractType = $subContract->codigoTipoContrato; $contractDTO = $calculoFaturamento->GetContract($dataConnector->mysqlConnection, $subContract->id); $divisor = $calculoFaturamento->GetDivisor($dataConnector->mysqlConnection, $contractDTO, $subContract); // divisor para calculo da média (no caso de contrato global) // Caso o status do contrato seja finalizado ou cancelado não fatura, passa para o proximo equipamento if ($contractDTO->status == 3 || $contractDTO->status == 4) { continue; } $equipmentDTO = $equipmentDAO->RetrieveRecord($equipmentId); $inventoryItem = $inventoryItemDAO->RetrieveRecord($equipmentDTO->itemCode); $contractTypeInfo = $calculoFaturamento->GetTipoContratoAsText($contractType); $equipmentInfo = GetEquipmentInfo($equipmentDTO, $contractTypeInfo); echo '<tr bgcolor="LIGHTGRAY" ><td colspan="11" >' . $equipmentInfo . '</td></tr>'; $counterIdArray = array_keys($calculoFaturamento->GetContadores()); foreach ($counterIdArray as $counterId) { $billingSummary = $totalContadores[$counterId]; $formaCobranca = $calculoFaturamento->GetFormaCobranca($subContract->id, $counterId); $consumo = $calculoFaturamento->GetConsumo($equipmentId, $counterId); $consumoGlobal = 0; $excedenteGlobal = 0; if ($contractDTO->global) { $consumoGlobal = $calculoFaturamento->GetConsumoGlobal($dataConnector->mysqlConnection, $contractDTO->id, $counterId); $franquiaGlobal = $calculoFaturamento->GetFranquiaGlobal($dataConnector->mysqlConnection, $counterId); $excedenteGlobal = $consumoGlobal > $franquiaGlobal ? $consumoGlobal - $franquiaGlobal : 0; if ($franquiaGlobal == 0) { $excedenteGlobal = 0; } } $modalidadeMedicao = 0; $fixo = 0.0; $variavelDefault = 0.0; $franquia = 0; $individualPorCapacidade = 0; if ($formaCobranca != null) { $modalidadeMedicao = $formaCobranca->modalidadeMedicao; $fixo = $formaCobranca->fixo / $divisor; $variavelDefault = $formaCobranca->variavel; $franquia = $formaCobranca->franquia / $divisor; $individualPorCapacidade = $formaCobranca->individual; if ($individualPorCapacidade) { $franquia = $equipmentDTO->capacity; } } if ($modalidadeMedicao == 1) { // Sem leituras $rowData = '<td>' . $calculoFaturamento->GetContadorAsText($counterId) . '</td><td>Sem leitura</td><td>Sem leitura</td><td>Sem leitura</td><td>0</td><td>0</td><td>0</td><td>0</td><td>' . formatBrCurrency($fixo, 2) . '</td><td>0</td><td>' . formatBrCurrency($fixo, 2) . '</td>'; if ($valuesOnly) { $rowData = '<td>' . $equipmentId . '</td><td>' . $contractTypeInfo . '</td><td>' . $counterId . '</td><td>Sem leitura</td><td>Sem leitura</td><td>Sem leitura</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>' . $fixo . '</td><td>0</td><td>' . $fixo . '</td>'; } echo '<tr bgcolor="WHITE" >' . $rowData . '</tr>'; $billingSummary->valorFixo += $fixo; $billingSummary->excedente += 0; $billingSummary->valorTotal += $fixo; } if ($consumo != null && $modalidadeMedicao != 1) { $dataLeitura = $consumo->dataLeitura; $medicaoInicial = $consumo->medicaoInicial; $medicaoFinal = $consumo->medicaoFinal; $consumoMedido = $consumo->total; $ajuste = $consumo->ajusteTotal; $excedente = $consumoMedido > $franquia ? $consumoMedido - $franquia : 0; if ($contractDTO->global && !$individualPorCapacidade) { $excedente = $excedenteGlobal / sizeof($itemArray); } if ($franquia == 0) { $excedente = 0; } $tarifaExcedente = $calculoFaturamento->GetCustoVariavel($subContract->id, $counterId, $consumoMedido, $variavelDefault); if ($contractDTO->global && !$individualPorCapacidade) { $tarifaExcedente = $calculoFaturamento->GetCustoVariavel($subContract->id, $counterId, $consumoGlobal, $variavelDefault); } $valorFixo = $fixo; $valorVariavel = $franquia != 0 ? $excedente * $tarifaExcedente : $consumoMedido * $tarifaExcedente; $valorTotal = $valorFixo + $valorVariavel; $rowData = '<td>' . $calculoFaturamento->GetContadorAsText($counterId) . '</td><td>' . date("d/m/Y", $dataLeitura) . '</td><td>' . $medicaoFinal . '</td><td>' . $medicaoInicial . '</td><td>' . $consumoMedido . '<br/>(Acrésc/Desc = ' . $ajuste . ')' . '</td><td>' . formatDecimal($franquia, 2) . '</td><td>' . round($excedente) . '</td><td>' . formatDecimal($tarifaExcedente, null) . '</td><td>' . formatBrCurrency($valorFixo, 2) . '</td><td>' . formatBrCurrency($valorVariavel, 2) . '</td><td>' . formatBrCurrency($valorTotal, 2) . '</td>'; if ($valuesOnly) { $rowData = '<td>' . $equipmentId . '</td><td>' . $contractTypeInfo . '</td><td>' . $counterId . '</td><td>' . date("Y-m-d", $dataLeitura) . '</td><td>' . $medicaoFinal . '</td><td>' . $medicaoInicial . '</td><td>' . $consumoMedido . '</td><td>' . $ajuste . '</td><td>' . $franquia . '</td><td>' . $excedente . '</td><td>' . $tarifaExcedente . '</td><td>' . $valorFixo . '</td><td>' . $valorVariavel . '</td><td>' . $valorTotal . '</td>'; } echo '<tr bgcolor="WHITE" >' . $rowData . '</tr>'; $billingSummary->consumo += $consumoMedido; $billingSummary->franquia += $franquia; $billingSummary->excedente += $excedente; $billingSummary->valorFixo += $valorFixo; $billingSummary->valorVariavel += $valorVariavel; $billingSummary->valorTotal += $valorTotal; } } // foreach de tipos de contador } // foreach de itens de faturamento (equipamentos) }
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); }
echo $calendar->GetMonthName($billingMonth); ?> Ano: <?php echo $billingYear; ?> </h3> </div> <div style="clear:both;"><br/><br/></div> <hr/> <div style="clear:both;"><br/></div> <table> <tr bgcolor="YELLOW" style="height:30px;" ><td>Cliente</td><td>Descrição Equipamento</td><td>Série Fabricante</td><td>Nosso Núm. Série</td><td>Medidor</td><td>Data Leitura</td><td>Medição Final</td><td>Medição Inicial</td><td>Consumo (com Acresc/Desc)</td><td>Franquia</td><td>Excedente</td><td>Tarifa sobre exced.</td><td>Fixo (R$)</td><td>Variável (R$)</td><td>Total (com Acresc/Desc)</td><td>Vendedor</td></tr> <?php $grandTotal = 0; foreach ($billingItemArray as $billingItem) { $equipment = $equipmentDAO->RetrieveRecord($billingItem->codigoCartaoEquipamento); $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; }
static function GetSerialNumber($sqlserverConnection, $equipmentCode) { $serialNumber = ""; if (empty($equipmentCode)) { return $serialNumber; } $equipmentDAO = new EquipmentDAO($sqlserverConnection); $equipmentDAO->showErrors = 1; $equipment = $equipmentDAO->RetrieveRecord($equipmentCode); if ($equipment != null) { $serialNumber = $equipment->manufacturerSN . " (" . $equipment->internalSN . ") "; } return $serialNumber; }
function BuildReportTable($startColumn, $startRow) { global $dataConnector; global $objPhpExcel; global $contractArray; global $model; global $searchMethod; $contractDAO = new ContractDAO($dataConnector->mysqlConnection); $contractDAO->showErrors = 1; $subContractDAO = new SubContractDAO($dataConnector->mysqlConnection); $subContractDAO->showErrors = 1; $contractItemDAO = new ContractItemDAO($dataConnector->mysqlConnection); $contractItemDAO->showErrors = 1; $equipmentDAO = new EquipmentDAO($dataConnector->sqlserverConnection); $equipmentDAO->showErrors = 1; $equipmentModelDAO = new EquipmentModelDAO($dataConnector->mysqlConnection); $equipmentModelDAO->showErrors = 1; $salesPersonDAO = new SalesPersonDAO($dataConnector->sqlserverConnection); $salesPersonDAO->showErrors = 1; // Define o titulo da tabela $currentRow = $startRow; $activeSheet = $objPhpExcel->getActiveSheet(); $activeSheet->setCellValue($startColumn . $startRow, 'CONTRATOS'); $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('Número', 'Cliente', 'Detalhes', 'Assinatura', 'Encerramento', 'Inicio do Atendimento', 'Fim do Atendimento', 'Parcela', 'Vendedor', 'Status', 'Global(S OU N)'); $offset = 0; foreach ($headers as $header) { $activeSheet->getColumnDimension(chr($colNum + $offset))->setWidth(25); $offset++; } $activeSheet->getColumnDimension(chr($colNum + 1))->setWidth(50); $activeSheet->getColumnDimension(chr($colNum + 2))->setWidth(50); $currentRow++; InsereLinhaPlanilha($currentRow, $startColumn, $headers, PHPExcel_Style_Color::COLOR_YELLOW, 30, PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // Busca os modelos de equipamento cadastrados no sistema $modelArray = array(0 => ""); $equipmentModelArray = $equipmentModelDAO->RetrieveRecordArray(); foreach ($equipmentModelArray as $modelDTO) { $modelArray[$modelDTO->id] = $modelDTO->modelo; } // 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 $identifierArray = array(); $LFCR = chr(10) . chr(13); foreach ($contractArray as $contract) { if (array_key_exists($contract->id, $identifierArray)) { continue; } // contrato repetido, pula para o próximo registro $clientName = BusinessPartnerDAO::GetClientName($dataConnector->sqlserverConnection, $contract->pn); $salesPersonName = $salesPersonArray[$contract->vendedor]; $details = ""; $subContractArray = $subContractDAO->RetrieveRecordArray("contrato_id=" . $contract->id); foreach ($subContractArray as $subContract) { if (!empty($details)) { $details = $details . $LFCR; } $details = $details . $subContract->siglaTipoContrato; $itemArray = $contractItemDAO->RetrieveRecordArray("subContrato_id=" . $subContract->id); foreach ($itemArray as $contractItem) { $equipment = $equipmentDAO->RetrieveRecord($contractItem->codigoCartaoEquipamento); $installationDate = empty($equipment->installationDate) ? '' : $equipment->installationDate->format('d/m/Y'); // filtra apenas os items ativos e emprestados if ($equipment->status == 'A' || $equipment->status == 'L') { if (!empty($details)) { $details = $details . $LFCR; } $equipmentModel = ""; if (array_key_exists($equipment->model, $modelArray)) { $equipmentModel = $modelArray[$equipment->model]; } $details = $details . $equipmentModel . ' Série ' . $equipment->manufacturerSN . ' Data Instalação ' . $installationDate; } } } if ($searchMethod == 1 || $searchMethod == 2) { if (!empty($model)) { $modelMatched = false; if (strpos($details, $model)) { $modelMatched = true; } if (!$modelMatched) { continue; } } } $currentRow++; $row = array(); $row[0] = str_pad($contract->numero, 5, '0', STR_PAD_LEFT); $row[1] = $clientName; $row[2] = $details; $row[3] = $contract->dataAssinatura; $row[4] = $contract->dataEncerramento; $row[5] = $contract->inicioAtendimento; $row[6] = $contract->fimAtendimento; $row[7] = $contract->parcelaAtual . '/' . $contract->quantidadeParcelas; $row[8] = $salesPersonName; $row[9] = $contractDAO->GetStatusAsText($contract->status); $row[10] = $contract->global == 0 ? 'N' : 'S'; InsereLinhaPlanilha($currentRow, $startColumn, $row); $identifierArray[$contract->id] = $contract->numero; } }
echo 'Não foi possível se connectar ao bando de dados!'; exit; } // Busca os dados da solicitação de consumível $supplyRequestDAO = new SupplyRequestDAO($dataConnector->mysqlConnection); $supplyRequestDAO->showErrors = 1; $supplyRequest = $supplyRequestDAO->RetrieveRecord($supplyRequestId); // Traz as informações do equipamento $shortDescription = ''; $model = ''; $businessPartnerCode = ''; $address = ''; $instLocation = ''; $equipmentDAO = new EquipmentDAO($dataConnector->sqlserverConnection); $equipmentDAO->showErrors = 1; $equipment = $equipmentDAO->RetrieveRecord($supplyRequest->codigoCartaoEquipamento); if ($equipment != null) { $shortDescription = EquipmentDAO::GetShortDescription($equipment); $model = $equipment->itemName; $businessPartnerCode = $equipment->customer; $address = $equipment->addressType . " " . $equipment->street . " " . $equipment->streetNo . " " . $equipment->building . " CEP: " . $equipment->zip; $address = $address . " " . "Bairro: " . $equipment->block . " " . $equipment->city . " " . $equipment->state . " " . $equipment->country; $instLocation = $equipment->instLocation; } // Recupera os itens da solicitação $itens = ""; $requestItemDAO = new RequestItemDAO($dataConnector->mysqlConnection); $requestItemDAO->showErrors = 1; $requestItemArray = $requestItemDAO->RetrieveRecordArray("pedidoConsumivel_id=" . $supplyRequest->id); foreach ($requestItemArray as $requestItem) { if (!empty($itens)) {
function BuildReportTable($startColumn, $startRow) { global $dataConnector; global $objPhpExcel; global $invoiceArray; global $salesPerson; global $model; global $equipmentCode; 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; $salesPersonDAO = new SalesPersonDAO($dataConnector->sqlserverConnection); $salesPersonDAO->showErrors = 1; // Define o titulo da tabela $currentRow = $startRow; $activeSheet = $objPhpExcel->getActiveSheet(); $activeSheet->setCellValue($startColumn . $startRow, 'FATURAS'); $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('Nº do documento', 'Data', 'Cliente', 'Observações', 'Detalhes', 'Vencimento', 'Total Nota (R$)', 'Nº Demonstrativo'); $offset = 0; foreach ($headers as $header) { $activeSheet->getColumnDimension(chr($colNum + $offset))->setWidth(25); $offset++; } $activeSheet->getColumnDimension(chr($colNum + 2))->setWidth(60); $activeSheet->getColumnDimension(chr($colNum + 4))->setWidth(60); $currentRow++; InsereLinhaPlanilha($currentRow, $startColumn, $headers, PHPExcel_Style_Color::COLOR_YELLOW, 30, PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // Busca os vendedores cadastrados no sistema $retrievedArray = $salesPersonDAO->RetrieveRecordArray(); $salesPersonArray = array(); foreach ($retrievedArray as $salesPersonDTO) { $salesPersonArray[$salesPersonDTO->slpCode] = $salesPersonDTO->slpName; } // Busca os modelos cadastrados no sistema $modelArray = array(0 => ""); $equipmentModelArray = $equipmentModelDAO->RetrieveRecordArray(); foreach ($equipmentModelArray as $modelDTO) { $modelArray[$modelDTO->id] = $modelDTO->modelo; } // Gera as linhas da tabela $grandTotal = 0; $LFCR = chr(10) . chr(13); foreach ($invoiceArray as $invoice) { $docDate = empty($invoice->docDate) ? '' : $invoice->docDate->format('d/m/Y'); $docDueDate = empty($invoice->docDueDate) ? '' : $invoice->docDueDate->format('d/m/Y'); $docTotal = number_format($invoice->docTotal, 2, ',', '.'); $details = ""; $salesPersons = array(); $itemNames = ""; $equipmentIds = array(); $billingItemArray = $billingItemDAO->RetrieveRecordArray("codigoFaturamento=" . $invoice->demFaturamento); foreach ($billingItemArray as $billingItem) { $equipment = $equipmentDAO->RetrieveRecord($billingItem->codigoCartaoEquipamento); $equipmentSerial = $equipment->manufacturerSN; $salesPersonCode = $equipment->salesPerson; if (empty($salesPersonCode)) { $salesPersonCode = -1; } $salesPersonName = $salesPersonArray[$salesPersonCode]; $details .= $equipmentSerial . ' ' . $salesPersonName . $LFCR; if ($salesPersonCode > 0) { array_push($salesPersons, $salesPersonCode); } $itemNames .= $equipment->itemName; if ($equipment->insID > 0) { array_push($equipmentIds, $equipment->insID); } } if ($salesPerson > 0) { if (!in_array($salesPerson, $salesPersons)) { continue; } } if ($searchMethod == 1 || $searchMethod == 2) { if (!empty($model)) { $modelMatched = false; if (strpos($itemNames, $model)) { $modelMatched = true; } if (!$modelMatched) { continue; } } } if ($searchMethod == 3) { if (!in_array($equipmentCode, $equipmentIds)) { continue; } } $currentRow++; $row = array(); $row[0] = $invoice->docNum; $row[1] = $docDate; $row[2] = $invoice->cardName; $row[3] = $invoice->comments; $row[4] = $details; $row[5] = $docDueDate; $row[6] = $docTotal; $row[7] = $invoice->demFaturamento; InsereLinhaPlanilha($currentRow, $startColumn, $row); $grandTotal += $invoice->docTotal; } $currentRow++; $total = array('Total Geral: ' . number_format($grandTotal, 2, ',', '.'), '0', '0', '0', '0', '0', '0', '0'); InsereLinhaPlanilha($currentRow, $startColumn, $total, '80BB80FF', 45, PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $activeSheet->mergeCells(chr($colNum + 0) . $currentRow . ':' . chr($colNum + sizeof($headers) - 1) . $currentRow); }
function BuildReportTable($startColumn, $startRow) { global $dataConnector; global $objPhpExcel; global $invoicePaymentArray; $salesPersonDAO = new SalesPersonDAO($dataConnector->sqlserverConnection); $salesPersonDAO->showErrors = 1; $equipmentDAO = new EquipmentDAO($dataConnector->sqlserverConnection); $equipmentDAO->showErrors = 1; $businessPartnerDAO = new BusinessPartnerDAO($dataConnector->sqlserverConnection); $businessPartnerDAO->showErrors = 1; $industryDAO = new IndustryDAO($dataConnector->sqlserverConnection); $industryDAO->showErrors = 1; $contractDAO = new ContractDAO($dataConnector->mysqlConnection); $contractDAO->showErrors = 1; $billingDAO = new BillingDAO($dataConnector->mysqlConnection); $billingDAO->showErrors = 1; $billingItemDAO = new BillingItemDAO($dataConnector->mysqlConnection); $billingItemDAO->showErrors = 1; $commissionPerSignatureDAO = new CommissionPerSignatureDAO($dataConnector->mysqlConnection); $commissionPerSignatureDAO->showErrors = 1; // Define o titulo da tabela $currentRow = $startRow; $activeSheet = $objPhpExcel->getActiveSheet(); $activeSheet->setCellValue($startColumn . $startRow, 'PAGAMENTOS'); $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('Nº NF', 'Tipo', 'Cliente', 'Segmento', 'Nº Boleto', 'Valor Cobrado', 'Valor Recebido', 'Data Pagamento', 'Demonstrativo Fat.', 'Descritivo Faturamento'); $offset = 0; foreach ($headers as $header) { $activeSheet->getColumnDimension(chr($colNum + $offset))->setWidth(25); $offset++; } $activeSheet->getColumnDimension(chr($colNum + $offset - 1))->setWidth(50); $currentRow++; InsereLinhaPlanilha($currentRow, $startColumn, $headers, PHPExcel_Style_Color::COLOR_YELLOW, 30, PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // Cria um array para as estatísticas dos vendedores $statsArray = array(); $salesPersonArray = $salesPersonDAO->RetrieveRecordArray(); foreach ($salesPersonArray as $salesPerson) { $statsArray[$salesPerson->slpCode] = new SalesPerson($salesPerson->slpCode, $salesPerson->slpName); } // Cria um array para as regras de comissão $rulesArray = array(0 => new CommissionRule(0, "", "", 0)); $commissionRuleArray = $commissionPerSignatureDAO->RetrieveRecordArray(); foreach ($commissionRuleArray as $commissionRuleDTO) { $rulesArray[$commissionRuleDTO->id] = new CommissionRule($commissionRuleDTO->segmento, $commissionRuleDTO->dataAssinaturaDe, $commissionRuleDTO->dataAssinaturaAte, $commissionRuleDTO->comissao); } // Busca os segmentos/ramos de atividade cadastrados no sistema $industryArray = array(-1 => "- Nenhum Segmento -"); $tempArray = $industryDAO->RetrieveRecordArray(); foreach ($tempArray as $industry) { $industryArray[$industry->id] = $industry->name; } foreach ($invoicePaymentArray as $payment) { $billingDescription = ""; $billing = new BillingDTO(); if ($payment->demFaturamento >= 1) { $billing = $billingDAO->RetrieveRecord($payment->demFaturamento); if (!isset($billing)) { $billing = new BillingDTO(); } } $industry = -1; // Busca os itens de faturamento $billingItemArray = $billingItemDAO->RetrieveRecordArray("codigoFaturamento = '" . $billing->id . "'"); $quantidadeItens = sizeof($billingItemArray); if ($quantidadeItens < 1) { $quantidadeItens = 1; } $multaRecisoria = $billing->multaRecisoria / $quantidadeItens; foreach ($billingItemArray as $billingItem) { $equipment = $equipmentDAO->RetrieveRecord($billingItem->codigoCartaoEquipamento); $businessPartner = $businessPartnerDAO->RetrieveRecord($payment->cardCode); if (isset($businessPartner->industry)) { $industry = $businessPartner->industry; } if ($equipment->salesPerson > 0) { $contract = $contractDAO->RetrieveRecord($billingItem->contrato_id); $salesPersonStats = $statsArray[$equipment->salesPerson]; if (!isset($salesPersonStats->statistics)) { $salesPersonStats->statistics = array(); } $ruleKey = 0; $comissao = 0; $commissionRuleArray = $commissionPerSignatureDAO->RetrieveRecordArray("(segmento = 0 OR segmento = " . $industry . ") AND dataAssinaturaDe <= '" . $contract->dataAssinatura . "' AND dataAssinaturaAte >= '" . $contract->dataAssinatura . "' ORDER BY segmento DESC, comissao DESC"); if (sizeof($commissionRuleArray) > 0) { $rule = $commissionRuleArray[0]; $ruleKey = $rule->id; $comissao = $rule->comissao; } if (!array_key_exists($ruleKey, $salesPersonStats->statistics)) { $salesPersonStats->statistics[$ruleKey] = new SalesPersonStats($ruleKey); } $ruleStats = $salesPersonStats->statistics[$ruleKey]; $ruleStats->comissionRate = $comissao; $ruleStats->revenue += $billingItem->total + $billingItem->acrescimoDesconto - $multaRecisoria; if (!IsContractAdded($contract, $ruleStats)) { $ruleStats->contractCount++; if (!isset($ruleStats->contractArray)) { $ruleStats->contractArray = array(); } $ruleStats->contractArray[$contract->id] = $contract->id; } } $LFCR = chr(10) . chr(13); $salesPersonName = SalesPersonDAO::GetSalesPersonName($dataConnector->sqlserverConnection, $equipment->salesPerson); $billingDescription .= $salesPersonName . ' Serial ' . $equipment->manufacturerSN . ' R$ ' . $billingItem->total . $LFCR; } $currentRow++; $row = array(); $row[0] = $payment->serial; $row[1] = GetInvoiceTypeAsText($payment->tipo); $row[2] = $payment->cardName; $row[3] = $industryArray[$industry]; $row[4] = GetBOENumber($payment); $row[5] = GetDocTotal($payment); $row[6] = GetReceivedAmount($payment); $row[7] = empty($payment->date) ? '' : $payment->date->format('d/m/Y'); $row[8] = empty($billing->id) ? ' - ' : str_pad($billing->id, 5, '0', STR_PAD_LEFT); $row[9] = $billingDescription; InsereLinhaPlanilha($currentRow, $startColumn, $row); } $currentRow += 3; // pula 3 linhas // Define o titulo do quadro resumo $activeSheet = $objPhpExcel->getActiveSheet(); $activeSheet->setCellValue($startColumn . $currentRow, 'COMISSÕES'); $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('Vendedor', 'Segmento', 'Data de Assinatura', 'Quantidade de Contratos', 'Faturamento (R$)', 'Comissão (%)', 'Comissão (R$)'); $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); foreach ($statsArray as $salesPersonStats) { if (isset($salesPersonStats->statistics)) { $statistics = $salesPersonStats->statistics; foreach ($statistics as $stat) { $commissionRule = $rulesArray[$stat->index]; $industryName = "Todos"; if ($commissionRule->segmento > 0) { $industryName = $industryArray[$commissionRule->segmento]; } $period = ""; if ($commissionRule->comissao > 0) { $period = $commissionRule->dataAssinaturaDe . ' até ' . $commissionRule->dataAssinaturaAte; } $comissionPercentage = $stat->comissionRate; $comissionValue = $stat->comissionRate / 100 * $stat->revenue; $currentRow++; $row = array(); $row[0] = $salesPersonStats->name; $row[1] = $industryName; $row[2] = $period; $row[3] = $stat->contractCount; $row[4] = number_format($stat->revenue, 2, ',', '.'); $row[5] = $comissionPercentage; $row[6] = number_format($comissionValue, 2, ',', '.'); InsereLinhaPlanilha($currentRow, $startColumn, $row); } } } }