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;
}
}
function GetSerialNumbers($mysqlConnection, $sqlserverConnection, $subContractId)
{
$serialNumbers = "";
$contractItemDAO = new ContractItemDAO($mysqlConnection);
$contractItemDAO->showErrors = 1;
$itemArray = $contractItemDAO->RetrieveRecordArray("subContrato_id = " . $subContractId);
foreach ($itemArray as $contractItem) {
if (!empty($serialNumbers)) {
$serialNumbers = $serialNumbers . ", ";
}
$serialNumbers = $serialNumbers . EquipmentDAO::GetSerialNumber($sqlserverConnection, $contractItem->codigoCartaoEquipamento);
}
return $serialNumbers;
}
function GetEquipmentInfo($equipment, $contractType)
{
$equipmentInfo = "";
// Busca os dados do equipamento
$modelo = "";
$codigoModelo = "";
$serie = "";
$instLocation = "";
if ($equipment != null) {
$modelo = $equipment->itemName;
$codigoModelo = $equipment->itemCode;
$serie = EquipmentDAO::GetShortDescription($equipment);
$instLocation = $equipment->instLocation;
}
$spacing = ' ';
$equipmentInfo = 'Cartão Equipamento: ' . $equipment->insID . $spacing . 'Modelo: ' . $modelo . $spacing . 'Série: ' . $serie . $spacing . 'Departamento: ' . $instLocation . $spacing . 'Tipo: ' . $contractType;
return $equipmentInfo;
}
?>
</h1>
</div>
<div style="clear:both;">
<br/><br/>
</div>
<?php
foreach ($subContractArray as $subContract) {
$subContractItems = ContractItemDAO::GetItemsByOwner($dataConnector->mysqlConnection, $subContract->id);
$serialNumbers = '';
foreach ($subContractItems as $contractItem) {
if (!empty($serialNumbers)) {
$serialNumbers .= ', ';
}
$serialNumbers .= EquipmentDAO::GetSerialNumber($dataConnector->sqlserverConnection, $contractItem->codigoCartaoEquipamento);
}
if (empty($serialNumbers)) {
$serialNumbers = 'Nenhum item encontrado';
}
$chargeArray = $contractChargeDAO->RetrieveRecordArray("subContrato_id = " . $subContract->id);
$bonusArray = $contractBonusDAO->RetrieveRecordArray("subcontrato_id = " . $subContract->id . " ORDER BY de, ate");
echo '<fieldset style="width:650px;" >';
echo ' <legend>' . ContractTypeDAO::GetAlias($dataConnector->mysqlConnection, $subContract->codigoTipoContrato) . '</legend>';
echo ' Itens:<br/>';
echo ' - ' . $serialNumbers;
echo ' <br/><br/>';
echo ' Cobrança:<br/>';
foreach ($chargeArray as $charge) {
$counterName = CounterDAO::GetCounterName($dataConnector->mysqlConnection, $charge->codigoContador);
$fixoReajustado = $charge->fixo * (1 + $adjustmentRate->aliquota / 100);
if (!empty($codigoInsumo)) {
$productionInput = $productionInputDAO->RetrieveRecord($codigoInsumo);
$inputType = $inputTypeArray[$productionInput->tipoInsumo];
$numeroChamado = str_pad($expense->codigoChamado, 4, '0', STR_PAD_LEFT);
$descricao = "<a href='Frontend/chamados/editar.php?id=" . $expense->codigoChamado . "'>" . $inputType . " ( Número do Chamado: " . $numeroChamado . " )</a>";
}
echo '<tr>';
echo ' <td>' . $serviceCall->dataAbertura . '</td>';
echo ' <td>' . $serieEquipamento . '</td>';
echo ' <td>' . $descricao . '</td>';
echo ' <td>' . number_format($expense->totalDespesa, 2, ',', '.') . '</td>';
echo '</tr>';
$somaTotais += $expense->totalDespesa;
}
foreach ($supplyRequestArray as $supplyRequest) {
$equipmentSN = EquipmentDAO::GetSerialNumber($dataConnector->sqlserverConnection, $supplyRequest->codigoCartaoEquipamento);
$requestItemArray = $requestItemDAO->RetrieveRecordArray("pedidoConsumivel_id=" . $supplyRequest->id);
foreach ($requestItemArray as $requestItem) {
$description = $requestItem->quantidade . ' ' . $requestItem->nomeItem;
echo '<tr>';
echo ' <td>' . $supplyRequest->data . '</td>';
echo ' <td>' . $equipmentSN . '</td>';
echo ' <td>' . $description . '</td>';
echo ' <td>' . number_format($requestItem->total, 2, ',', '.') . '</td>';
echo '</tr>';
$somaTotais += $requestItem->total;
}
}
foreach ($indirectCostArray as $indirectCost) {
$serviceCallArray = $indirectCostDAO->GetDistributedExpenses($indirectCost->id);
$serviceCallCount = sizeof($serviceCallArray);
$subContract = $_REQUEST["subContract"];
}
// Abre a conexao com o banco de dados
$dataConnector = new DataConnector('both');
$dataConnector->OpenConnection();
if ($dataConnector->mysqlConnection == null || $dataConnector->sqlserverConnection == null) {
echo 'Não foi possível se connectar ao bando de dados!';
exit;
}
$nivelAutorizacao = GetAuthorizationLevel($dataConnector->mysqlConnection, $functionalities["gerenciamentoEquipmtPecas"]);
if ($nivelAutorizacao <= 1) {
DisplayNotAuthorizedWarning();
exit;
}
// Cria o objeto de mapeamento objeto-relacional
$equipmentDAO = new EquipmentDAO($dataConnector->sqlserverConnection);
$equipmentDAO->showErrors = 1;
$equipment = new EquipmentDTO();
$equipmentArray = array();
// Histórico do equipamento (cartões de equipamento com este número de série)
if ($equipmentCode != 0) {
$equipment = $equipmentDAO->RetrieveRecord($equipmentCode);
$equipmentArray = $equipmentDAO->RetrieveRecordArray("manufSN='" . $equipment->manufacturerSN . "'");
}
if ($serial != '') {
$serial = urldecode($serial);
$equipmentArray = $equipmentDAO->RetrieveRecordArray("manufSN='" . $serial . "'");
if (sizeof($equipmentArray) > 0) {
$equipment = $equipmentArray[0];
$equipmentCode = $equipment->insID;
}
$serviceCallId = $_GET['serviceCallId'];
$sendToPrinter = null;
if (isset($_GET['sendToPrinter'])) {
$sendToPrinter = $_GET['sendToPrinter'];
}
// Abre a conexao com o banco de dados
$dataConnector = new DataConnector('both');
$dataConnector->OpenConnection();
if ($dataConnector->mysqlConnection == null || $dataConnector->sqlserverConnection == null) {
echo 'Não foi possível se connectar ao bando de dados!';
exit;
}
// Cria os objetos de mapeamento objeto-relacional
$serviceCallDAO = new ServiceCallDAO($dataConnector->mysqlConnection);
$serviceCallDAO->showErrors = 1;
$equipmentDAO = new EquipmentDAO($dataConnector->sqlserverConnection);
$equipmentDAO->showErrors = 1;
// Busca os dados do chamado
$serviceCall = $serviceCallDAO->RetrieveRecord($serviceCallId);
// Recupera dados do cartão de equipamento
$equipment = $equipmentDAO->RetrieveRecord($serviceCall->codigoCartaoEquipamento);
// Busca os dados do cliente
$clientName = BusinessPartnerDAO::GetClientName($dataConnector->sqlserverConnection, $serviceCall->businessPartnerCode);
?>
<script type='text/javascript'>
$(document).ready(function() {
<?php
if (isset($sendToPrinter)) {
echo 'window.print();';
}
$acao = $_REQUEST["acao"];
if (!isset($_REQUEST["cartaoEquipamento"]) && $acao == "store") {
echo "Favor informar o equipamento a que se refere o chamado.";
exit;
}
// Abre a conexao com o banco de dados
$dataConnector = new DataConnector('both');
$dataConnector->OpenConnection();
if ($dataConnector->mysqlConnection == null || $dataConnector->sqlserverConnection == null) {
echo 'Não foi possível se connectar ao bando de dados!';
exit;
}
// Cria os objetos de mapeamento objeto-relacional
$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;
/logo_datacopy.png" alt="Datacopy Trade" style="width:150px; height:50px; margin-top:10px; margin-left: 10px; margin-right: 10px; float:left;" />
<div style="height:50px; margin-top:10px; margin-left: 50px; float:left;">
<h3 style="border:0; margin:0;" >TEMPO EM ATENDIMENTO</h3><br/>
<h3 style="border:0; margin:0;" >Data inicial: <?php
echo $startDate;
?>
Data final: <?php
echo $endDate;
?>
</h3>
</div>
<div style="clear:both;"><br/><br/></div>
<table>
<?php
// Busca os dados dos equipamentos
$equipmentDAO = new EquipmentDAO($dataConnector->sqlserverConnection);
$equipmentDAO->showErrors = 1;
$equipmentArray = $equipmentDAO->RetrieveRecordArray("InsId IN (" . $equipmentList . ")");
foreach ($equipmentArray as $equipment) {
$equipmentModel = $equipment->itemName;
$serialNumber = EquipmentDAO::GetShortDescription($equipment);
$salesPersonName = SalesPersonDAO::GetSalesPersonName($dataConnector->sqlserverConnection, $equipment->salesPerson);
$spacing = ' ';
echo '<tr bgcolor=LIGHTGRAY ><td colspan=5 >Cartão Equipamento: ' . $equipment->insID . $spacing . 'Modelo: ' . $equipmentModel . $spacing . 'Série: ' . $serialNumber . $spacing . 'Departamento: ' . $equipment->instLocation . $spacing . 'Vendedor: ' . $salesPersonName . '</td></tr>';
$serviceCallDAO = new ServiceCallDAO($dataConnector->mysqlConnection);
$serviceCallDAO->showErrors = 1;
$query = "cartaoEquipamento = " . $equipment->insID . " AND dataAbertura >= '" . $startDate . " 00:00' AND dataAbertura <= '" . $endDate . " 23:59' ";
$serviceCallArray = $serviceCallDAO->RetrieveRecordArray($query);
$tempoTotalAtendimento = 0;
if (sizeof($serviceCallArray) > 0) {
echo '<tr bgcolor=WHITE ><td>Chamado</td><td>Defeito</td><td>Data Abertura</td><td>Técnico</td><td>Tempo Atendimento</td></tr>';
static function GetShortDescription($equipment)
{
$serialNumber = $equipment->manufacturerSN . " (" . $equipment->internalSN . ") ";
$status = EquipmentDAO::GetStatusDescription($equipment->status);
return $serialNumber . $status;
}
// Abre a conexao com o banco de dados
$dataConnector = new DataConnector('both');
$dataConnector->OpenConnection();
if ($dataConnector->mysqlConnection == null || $dataConnector->sqlserverConnection == null) {
echo 'Não foi possível se connectar ao bando de dados!';
exit;
}
$nivelAutorizacao = GetAuthorizationLevel($dataConnector->mysqlConnection, $functionalities["gerenciamentoContratos"]);
if ($nivelAutorizacao <= 1) {
DisplayNotAuthorizedWarning();
exit;
}
// Cria os objetos de mapeamento objeto-relacional
$contractItemDAO = new ContractItemDAO($dataConnector->mysqlConnection);
$contractItemDAO->showErrors = 1;
$equipmentDAO = new EquipmentDAO($dataConnector->sqlserverConnection);
$equipmentDAO->showErrors = 1;
//localiza os itens do subcontrato
$itemArray = $contractItemDAO->RetrieveRecordArray("subcontrato_id=" . $subContractId);
if (sizeof($itemArray) == 0) {
echo '<tr><td colspan="7" align="center" >Nenhum registro encontrado!</td></tr>';
}
foreach ($itemArray as $contractItem) {
$shortDescription = '<span style="color: CadetBlue;">(Equipamento não encontrado)</span>';
$sla = "";
$comments = "";
$equipment = $equipmentDAO->RetrieveRecord($contractItem->codigoCartaoEquipamento);
if ($equipment != null) {
$shortDescription = '<span style="color: CadetBlue;">' . EquipmentDAO::GetShortDescription($equipment) . '</span>';
if (!empty($equipment->sla)) {
$sla = $equipment->sla . ' horas';
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;
}
}
include_once "../DataTransferObjects/EquipmentDTO.php";
include_once "../DataAccessObjects/ConfigDAO.php";
include_once "../DataTransferObjects/ConfigDTO.php";
$businessPartnerCode = $_GET['businessPartnerCode'];
$equipmentCode = $_GET['equipmentCode'];
// Abre a conexao com o banco de dados
$dataConnector = new DataConnector('both');
$dataConnector->OpenConnection();
if ($dataConnector->mysqlConnection == null || $dataConnector->sqlserverConnection == null) {
echo 'Não foi possível se connectar ao bando de dados!';
exit;
}
// Recupera a configuração de ordenação dos equipamentos
$ordenarPorSerieFabrica = ConfigDAO::GetConfigurationParam($dataConnector->mysqlConnection, 'ordenarPorSerieFabrica');
// Busca os equipamentos cadastrados para o parceiro de negócios
$equipmentDAO = new EquipmentDAO($dataConnector->sqlserverConnection);
$equipmentDAO->showErrors = 1;
if ($ordenarPorSerieFabrica == 'true') {
$orderBy = "ORDER BY manufSN";
} else {
$orderBy = "ORDER BY internalSN";
}
$equipmentArray = $equipmentDAO->RetrieveRecordArray("Customer = '" . $businessPartnerCode . "' " . $orderBy);
foreach ($equipmentArray as $equipment) {
$attributes = "";
if ($equipment->insID == $equipmentCode) {
$attributes = "selected='selected'";
}
if ($ordenarPorSerieFabrica == 'true') {
$serialNumber = $equipment->manufacturerSN . " (" . $equipment->internalSN . ") ";
} else {
$dataConnector->OpenConnection();
if ($dataConnector->mysqlConnection == null || $dataConnector->sqlserverConnection == null) {
echo 'Não foi possível se connectar ao bando de dados!';
exit;
}
// Busca os dados 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);
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 $serviceCallArray;
$serviceCallDAO = new ServiceCallDAO($dataConnector->mysqlConnection);
$serviceCallDAO->showErrors = 1;
$equipmentDAO = new EquipmentDAO($dataConnector->sqlserverConnection);
$equipmentDAO->showErrors = 1;
$equipmentModelDAO = new EquipmentModelDAO($dataConnector->mysqlConnection);
$equipmentModelDAO->showErrors = 1;
$manufacturerDAO = new ManufacturerDAO($dataConnector->sqlserverConnection);
$manufacturerDAO->showErrors = 1;
$readingDAO = new ReadingDAO($dataConnector->mysqlConnection);
$readingDAO->showErrors = 1;
$counterDAO = new CounterDAO($dataConnector->mysqlConnection);
$counterDAO->showErrors = 1;
$employeeDAO = new EmployeeDAO($dataConnector->sqlserverConnection);
$employeeDAO->showErrors = 1;
// Define o titulo da tabela
$currentRow = $startRow;
$activeSheet = $objPhpExcel->getActiveSheet();
$activeSheet->setCellValue($startColumn . $startRow, 'CHAMADOS');
$styleArray = array('font' => array('bold' => true, 'size' => 16));
$activeSheet->getStyle($startColumn . $startRow . ':' . $startColumn . $startRow)->applyFromArray($styleArray);
// Cria o cabeçalho da tabela
$colNum = ord($startColumn);
$headers = array('Data Abertura', 'Nº do chamado', 'Status', 'Cliente', 'Depto.', 'Modelo', 'Série', 'Fabricante', 'Defeito', 'Data Atendimento', 'Horário/Duração', 'Sintoma', 'Causa', 'Ação', 'Observação Técnica', 'Contadores', 'Aberto Por', 'Técnico');
$offset = 0;
foreach ($headers as $header) {
$activeSheet->getColumnDimension(chr($colNum + $offset))->setWidth(25);
$offset++;
}
$activeSheet->getColumnDimension(chr($colNum + 3))->setWidth(50);
$activeSheet->getColumnDimension(chr($colNum + 4))->setWidth(35);
$activeSheet->getColumnDimension(chr($colNum + 8))->setWidth(60);
$activeSheet->getColumnDimension(chr($colNum + 11))->setWidth(60);
$activeSheet->getColumnDimension(chr($colNum + 12))->setWidth(60);
$activeSheet->getColumnDimension(chr($colNum + 13))->setWidth(60);
$activeSheet->getColumnDimension(chr($colNum + 14))->setWidth(35);
$activeSheet->getColumnDimension(chr($colNum + 16))->setWidth(40);
$activeSheet->getColumnDimension(chr($colNum + 17))->setWidth(40);
$currentRow++;
InsereLinhaPlanilha($currentRow, $startColumn, $headers, PHPExcel_Style_Color::COLOR_YELLOW, 30, PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// Busca os Status de chamado cadastrados no sistema
$statusArray = ServiceCallDAO::RetrieveServiceCallStatuses($dataConnector->sqlserverConnection);
// Busca os fabricantes cadastrados no sistema
$manufacturerArray = array(0 => "");
$tempArray = $manufacturerDAO->RetrieveRecordArray();
foreach ($tempArray as $manufacturer) {
$manufacturerArray[$manufacturer->FirmCode] = $manufacturer->FirmName;
}
// Busca os modelos de equipamento cadastrados no sistema
$modelArray = array(0 => "");
$equipmentModelArray = $equipmentModelDAO->RetrieveRecordArray();
foreach ($equipmentModelArray as $modelDTO) {
$modelArray[$modelDTO->id] = $modelDTO->modelo;
}
$associativeList = array(0 => "");
foreach ($equipmentModelArray as $modelDTO) {
$associativeList[$modelDTO->id] = $manufacturerArray[$modelDTO->fabricante];
}
// Busca os contadores cadastrados no sistema
$retrievedArray = $counterDAO->RetrieveRecordArray();
$counterArray = array();
foreach ($retrievedArray as $counter) {
$counterArray[$counter->id] = $counter->nome;
}
// Busca os funcionários cadastrados no sistema
$retrievedArray = $employeeDAO->RetrieveRecordArray("empID IS NOT NULL ORDER BY empID");
$employeeArray = array();
foreach ($retrievedArray as $employee) {
$employeeArray[$employee->empID] = $employee->firstName . " " . $employee->middleName . " " . $employee->lastName;
}
// Cria um array para as estatísticas dos técnicos
$statsArray = array(0 => "");
foreach ($employeeArray as $id => $name) {
// considera todos os funcionários como possíveis técnicos, depois filtra somente os que realizaram atendimentos
$statsArray[$id] = new Technician($id, $name);
}
// Gera as linhas da tabela
$quantChamados = 0;
foreach ($serviceCallArray as $serviceCall) {
$equipment = $equipmentDAO->RetrieveRecord($serviceCall->codigoCartaoEquipamento);
if (!isset($equipment)) {
$equipment = new EquipmentDTO();
}
// cria objeto vazio em caso de erro
$readingArray = $readingDAO->RetrieveRecordArray("chamadoServico_id=" . $serviceCall->id);
$counters = "";
$LFCR = chr(10) . chr(13);
foreach ($readingArray as $reading) {
if (!empty($counters)) {
$counters = $counters . $LFCR;
}
$counters = $counters . $counterArray[$reading->codigoContador] . ' ' . $reading->contagem;
}
$modelName = "";
if (array_key_exists($equipment->model, $modelArray)) {
$modelName = $modelArray[$equipment->model];
}
$manufacturerName = "";
if (array_key_exists($equipment->model, $modelArray)) {
$manufacturerName = $associativeList[$equipment->model];
}
$creator = " - ";
if ($serviceCall->abertoPor > 0) {
$creator = $employeeArray[$serviceCall->abertoPor];
}
$technicianName = " - ";
if ($serviceCall->tecnico > 0) {
$technicianName = $employeeArray[$serviceCall->tecnico];
}
$technicianStats = $statsArray[$serviceCall->tecnico];
if (!isset($technicianStats->statistics)) {
$technicianStats->statistics = array();
}
if (!array_key_exists($equipment->model, $technicianStats->statistics)) {
$technicianStats->statistics[$equipment->model] = new EquipmentModelStats($equipment->model, $modelName, $manufacturerName);
}
$equipmentModelStat = $technicianStats->statistics[$equipment->model];
$equipmentModelStat->serviceCallCount++;
$parts = explode(":", $serviceCall->tempoAtendimento, 2);
$equipmentModelStat->tempoTotalAtendimento += (int) $parts[0] + (int) $parts[1] / 60;
$currentRow++;
$row = array();
$row[0] = $serviceCall->dataAbertura;
$row[1] = str_pad($serviceCall->id, 5, '0', STR_PAD_LEFT);
$row[2] = $statusArray[$serviceCall->status];
$row[3] = $equipment->custmrName;
$row[4] = $equipment->instLocation;
$row[5] = $modelName;
$row[6] = $equipment->manufacturerSN;
$row[7] = $manufacturerName;
$row[8] = $serviceCall->defeito;
$row[9] = $serviceCall->dataAtendimento;
$row[10] = $serviceCall->horaAtendimento . " (Duração " . $serviceCall->tempoAtendimento . ")";
$row[11] = $serviceCall->sintoma;
$row[12] = $serviceCall->causa;
$row[13] = $serviceCall->acao;
$row[14] = $serviceCall->observacaoTecnica;
$row[15] = $counters;
$row[16] = $creator;
$row[17] = $technicianName;
InsereLinhaPlanilha($currentRow, $startColumn, $row);
$quantChamados++;
// Faz a contagem dos chamados técnicos
}
$currentRow++;
$footer = array('Quantidade de Chamados: ' . $quantChamados . ' (Depende do filtro escolhido)', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0');
InsereLinhaPlanilha($currentRow, $startColumn, $footer, '80BB80FF', 32, PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->mergeCells(chr($colNum + 0) . $currentRow . ':' . chr($colNum + sizeof($headers) - 1) . $currentRow);
$currentRow += 3;
// pula 3 linhas
// Define o titulo do quadro resumo
$activeSheet = $objPhpExcel->getActiveSheet();
$activeSheet->setCellValue($startColumn . $currentRow, 'QUADRO RESUMO');
$styleArray = array('font' => array('bold' => true, 'size' => 16));
$activeSheet->getStyle($startColumn . $currentRow . ':' . $startColumn . $currentRow)->applyFromArray($styleArray);
// Cria o cabeçalho do quadro resumo
$colNum = ord($startColumn);
$headers = array('Técnico', 'Modelo', 'Fabricante', 'Quant. Chamados', 'Tempo Médio Atendimento');
$offset = 0;
foreach ($headers as $header) {
$activeSheet->getColumnDimension(chr($colNum + $offset))->setWidth(25);
$offset++;
}
$currentRow++;
InsereLinhaPlanilha($currentRow, $startColumn, $headers, PHPExcel_Style_Color::COLOR_YELLOW, 30, PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$callCount = 0;
$timeSum = 0;
foreach ($statsArray as $technicianStats) {
if (!isset($technicianStats->statistics)) {
continue;
}
$valorPrevio = "";
$serviceCallCount = 0;
$tempoTotalAtendimento = 0;
foreach ($technicianStats->statistics as $equipmentModelStats) {
if ($equipmentModelStats->serviceCallCount > 0) {
$valorAtual = $equipmentModelStats->fabricante;
if ($valorPrevio != $valorAtual) {
if (!empty($valorPrevio)) {
$subTotal = array('', '', '', $callCount, UnixTime::ConvertToTime($timeSum / $callCount));
$currentRow++;
InsereLinhaPlanilha($currentRow, $startColumn, $subTotal, '80AAFFFF', 20, PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$callCount = 0;
$timeSum = 0;
}
$valorPrevio = $valorAtual;
}
$currentRow++;
$row = array();
$row[0] = $technicianStats->name;
$row[1] = $equipmentModelStats->model;
$row[2] = $equipmentModelStats->fabricante;
$row[3] = $equipmentModelStats->serviceCallCount;
$row[4] = UnixTime::ConvertToTime($equipmentModelStats->tempoTotalAtendimento / $equipmentModelStats->serviceCallCount);
InsereLinhaPlanilha($currentRow, $startColumn, $row);
$callCount = $callCount + $equipmentModelStats->serviceCallCount;
$timeSum = $timeSum + $equipmentModelStats->tempoTotalAtendimento;
$serviceCallCount = $serviceCallCount + $equipmentModelStats->serviceCallCount;
$tempoTotalAtendimento = $tempoTotalAtendimento + $equipmentModelStats->tempoTotalAtendimento;
}
}
$subTotal = array('', '', '', $callCount, UnixTime::ConvertToTime($timeSum / $callCount));
$currentRow++;
InsereLinhaPlanilha($currentRow, $startColumn, $subTotal, '80AAFFFF', 20, PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$callCount = 0;
$timeSum = 0;
$subTotal = array($technicianStats->name, '', '', $serviceCallCount, UnixTime::ConvertToTime($tempoTotalAtendimento / $serviceCallCount));
$currentRow++;
InsereLinhaPlanilha($currentRow, $startColumn, $subTotal, 'FFAA2040', 20, PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->mergeCells(chr($colNum + 0) . $currentRow . ':' . chr($colNum + 2) . $currentRow);
}
}
if ($nivelAutorizacao <= 1) {
DisplayNotAuthorizedWarning();
exit;
}
// Cria o objeto de mapeamento objeto-relacional
$supplyRequestDAO = new SupplyRequestDAO($dataConnector->mysqlConnection);
$supplyRequestDAO->showErrors = 1;
$id = 0;
$supplyRequest = new SupplyRequestDTO();
if (isset($_REQUEST["id"]) && $_REQUEST["id"] != 0) {
$id = $_REQUEST["id"];
$supplyRequest = $supplyRequestDAO->RetrieveRecord($id);
$equipmentCode = $supplyRequest->codigoCartaoEquipamento;
// sobrescreve o parâmetro recebido na url caso a solicitação esteja sendo alterada/editada
}
$equipmentInfo = EquipmentDAO::GetSerialNumber($dataConnector->sqlserverConnection, $equipmentCode);
$defaultEmailAddress = ConfigDAO::GetConfigurationParam($dataConnector->mysqlConnection, "emailPadrao");
?>
<h1>Solicitação de Consumível <?php
echo $equipmentInfo;
?>
</h1><br/>
<h1><?php
echo str_pad('_', 60, '_', STR_PAD_LEFT);
?>
</h1>
<div style="clear:both;">
<br/><br/>
</div>
<?php
include_once "../defines.php";
include_once "../ClassLibrary/DataConnector.php";
include_once "../DataAccessObjects/EquipmentDAO.php";
include_once "../DataTransferObjects/EquipmentDTO.php";
// Abre a conexao com o banco de dados
$dataConnector = new DataConnector('sqlServer');
$dataConnector->OpenConnection();
if ($dataConnector->sqlserverConnection == null) {
echo 'Não foi possível se connectar ao bando de dados!';
exit;
}
// Traz os equipamentos duplicados ( mesmo equipamento ativo em clientes diferentes )
$duplicates = EquipmentDAO::GetDuplicates($dataConnector->sqlserverConnection);
if (sizeof($duplicates) == 0) {
echo '<tr><td colspan="2" align="center">Nenhum registro encontrado!</td></tr>';
exit;
}
foreach ($duplicates as $serial => $quantidade) {
?>
<tr>
<td>
<a href="Frontend/equipamentos/editar.php?serial=<?php
echo $serial;
?>
" >
<?php
echo $serial;
?>
</a>
}
// Abre a conexao com o banco de dados
$dataConnector = new DataConnector('both');
$dataConnector->OpenConnection();
if ($dataConnector->mysqlConnection == null || $dataConnector->sqlserverConnection == null) {
echo 'Não foi possível se connectar ao bando de dados!';
exit;
}
// Cria os objetos de mapeamento objeto-relacional
$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;
// Busca os contratos que se enquadram no filtro aplicado
$contractArray = array();
if ($searchMethod == 0 || $searchMethod == 2) {
$filter = "contrato.pn='" . $businessPartnerCode . "' AND contrato.encerramento >= '" . $startDate . " 00:00' AND contrato.encerramento <= '" . $endDate . " 23:59'";
if ($contractType > 0) {
$filter = $filter . " AND subcontrato.tipoContrato_id=" . $contractType;
}
if (!empty($contractStatus)) {
$filter = $filter . " AND contrato.status IN (" . $contractStatus . ")";
}
include_once "../../DataAccessObjects/EquipmentDAO.php";
include_once "../../DataTransferObjects/EquipmentDTO.php";
// Abre a conexao com o banco de dados
$dataConnector = new DataConnector('both');
$dataConnector->OpenConnection();
if ($dataConnector->mysqlConnection == null || $dataConnector->sqlserverConnection == null) {
echo 'Não foi possível se connectar ao bando de dados!';
exit;
}
$nivelAutorizacao = GetAuthorizationLevel($dataConnector->mysqlConnection, $functionalities["gerenciamentoEquipmtPecas"]);
if ($nivelAutorizacao <= 1) {
DisplayNotAuthorizedWarning();
exit;
}
// Cria os objetos de mapeamento objeto-relacional
$equipmentDAO = new EquipmentDAO($dataConnector->sqlserverConnection);
$equipmentDAO->showErrors = 1;
// Traz a lista de equipamentos
$cutoffDate = ConfigDAO::GetConfigurationParam($dataConnector->mysqlConnection, "limiteListaEquipamentos");
$equipmentArray = $equipmentDAO->RetrieveRecordArray("(status = 'A' OR status = 'L') AND U_InstallationDate > '" . $cutoffDate . "' ORDER BY manufSN");
?>
<h1>Equipamentos (instalados a partir de <?php
echo $cutoffDate;
?>
)</h1>
<script type="text/javascript" >
// Traz a lista de equipamentos selecionados pelo usuário (checkboxes marcados)
function ObterListaEquipamentos()
$indirectCostDAO = new IndirectCostDAO($dataConnector->mysqlConnection);
$indirectCostDAO->showErrors = 1;
$serviceCallDAO = new ServiceCallDAO($dataConnector->mysqlConnection);
$serviceCallDAO->showErrors = 1;
// Busca os chamados relacionados ao custo indireto
$serviceCallArray = $indirectCostDAO->GetDistributedExpenses($indirectCostId);
if (sizeof($serviceCallArray) == 0) {
echo "<tr>";
echo " <td colspan='5' align='center' >Nenhum registro encontrado!</td>";
echo "</tr>";
exit;
}
foreach ($serviceCallArray as $serviceCallId) {
$serviceCall = $serviceCallDAO->RetrieveRecord($serviceCallId);
$subject = new Text($serviceCall->defeito);
$serialNumber = EquipmentDAO::GetSerialNumber($dataConnector->sqlserverConnection, $serviceCall->codigoCartaoEquipamento);
?>
<tr>
<td >
<?php
echo str_pad($serviceCall->id, 5, '0', STR_PAD_LEFT);
?>
</td>
<td >
<?php
echo $subject->Truncate(34);
?>
</td>
<td >
<?php
echo $serialNumber;
$searchMethod = $_GET['searchMethod'];
$sendToPrinter = null;
if (isset($_GET['sendToPrinter'])) {
$sendToPrinter = $_GET['sendToPrinter'];
}
// Abre a conexao com o banco de dados
$dataConnector = new DataConnector('both');
$dataConnector->OpenConnection();
if ($dataConnector->mysqlConnection == null || $dataConnector->sqlserverConnection == null) {
echo 'Não foi possível se connectar ao bando de dados!';
exit;
}
// Cria os objetos de mapeamento objeto-relacional
$billingItemDAO = new BillingItemDAO($dataConnector->mysqlConnection);
$billingItemDAO->showErrors = 1;
$equipmentDAO = new EquipmentDAO($dataConnector->sqlserverConnection);
$equipmentDAO->showErrors = 1;
$counterDAO = new CounterDAO($dataConnector->mysqlConnection);
$counterDAO->showErrors = 1;
$salesPersonDAO = new SalesPersonDAO($dataConnector->sqlserverConnection);
$salesPersonDAO->showErrors = 1;
// Busca os faturamentos que se enquadram no filtro aplicado
$billingItemArray = array();
if ($searchMethod == 0 || $searchMethod == 2) {
$filter = "businessPartnerCode='" . $businessPartnerCode . "' AND mesReferencia = " . $billingMonth . " AND anoReferencia = " . $billingYear . " AND incluirRelatorio=1";
$joins = "JOIN faturamento ON item.codigoFaturamento = faturamento.id";
$billingItemArray = $billingItemDAO->RetrieveRecordArray2($filter, $joins);
}
if ($searchMethod == 1) {
$filter = "mesReferencia = " . $billingMonth . " AND anoReferencia = " . $billingYear . " AND incluirRelatorio=1";
$joins = "JOIN faturamento ON item.codigoFaturamento = faturamento.id";
$searchMethod = $_GET['searchMethod'];
$sendToPrinter = null;
if (isset($_GET['sendToPrinter'])) {
$sendToPrinter = $_GET['sendToPrinter'];
}
// Abre a conexao com o banco de dados
$dataConnector = new DataConnector('both');
$dataConnector->OpenConnection();
if ($dataConnector->mysqlConnection == null || $dataConnector->sqlserverConnection == null) {
echo 'Não foi possível se connectar ao bando de dados!';
exit;
}
// Cria os objetos de mapeamento objeto-relacional
$serviceCallDAO = new ServiceCallDAO($dataConnector->mysqlConnection);
$serviceCallDAO->showErrors = 1;
$equipmentDAO = new EquipmentDAO($dataConnector->sqlserverConnection);
$equipmentDAO->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;
// Busca os chamados que se enquadram no filtro aplicado
if ($searchMethod == 0) {
$filter = "businessPartnerCode='" . $businessPartnerCode . "'";
if (empty($businessPartnerCode)) {
$filter = "businessPartnerCode <> ''";
}
// qualquer cliente
}
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);
}
include_once "../../DataAccessObjects/EquipmentDAO.php";
include_once "../../DataTransferObjects/EquipmentDTO.php";
if (!isset($_REQUEST["acao"])) {
echo "Erro no processamento da requisição.";
exit;
}
$acao = $_REQUEST["acao"];
// Abre a conexao com o banco de dados
$dataConnector = new DataConnector('sqlServer');
$dataConnector->OpenConnection();
if ($dataConnector->sqlserverConnection == null) {
echo 'Não foi possível se connectar ao bando de dados!';
exit;
}
// Cria o objeto de mapeamento objeto-relacional
$equipmentDAO = new EquipmentDAO($dataConnector->sqlserverConnection);
$equipmentDAO->showErrors = 1;
if ($acao == "store") {
if (isset($_REQUEST["equipmentCode"]) && $_REQUEST["equipmentCode"] != "") {
$equipmentCode = $_REQUEST["equipmentCode"];
$equipment = $equipmentDAO->RetrieveRecord($equipmentCode);
}
$equipment->installationDate = $_REQUEST["installationDate"];
$equipment->installationDocNum = $_REQUEST["installationDocNum"];
$equipment->counterInitialVal = $_REQUEST["counterInitialVal"];
$equipment->removalDate = $_REQUEST["removalDate"];
$equipment->removalDocNum = $_REQUEST["removalDocNum"];
$equipment->counterFinalVal = $_REQUEST["counterFinalVal"];
$equipment->technician = $_REQUEST["technician"];
$equipment->model = $_REQUEST["model"];
$equipment->capacity = $_REQUEST["capacity"];
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 $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);
}
// Busca os respectivos subcontratos
$subContractEnumeration = SubContractDAO::GetSubcontractsByOwner($dataConnector->mysqlConnection, $contractEnumeration);
// Busca os respectivos equipamentos (itens de contrato)
$itemArray = ContractItemDAO::GetItemsByOwner($dataConnector->mysqlConnection, $subContractEnumeration);
// Verifica se o array está vazio no final, juntamente com $rowCount
$rowCount = 0;
foreach ($itemArray as $contractItem) {
// Verifica se o equipamento já possui leitura nos últimos 5 dias
$filter = "codigoCartaoEquipamento=" . $contractItem->codigoCartaoEquipamento . " AND contador_id=" . $counterId . " AND origemLeitura_id=2 AND data > DATE_SUB( NOW(), INTERVAL 5 DAY)";
$readingArray = $readingDAO->RetrieveRecordArray($filter);
if (sizeof($readingArray) == 0) {
// Nenhuma leitura do equipamento hoje
$equipmentOperator = new EquipmentOperator($dataConnector->sqlserverConnection, $contractItem->businessPartnerCode, $contractItem->codigoCartaoEquipamento);
if ($equipmentOperator->equipmentStatus == 'A' || $equipmentOperator->equipmentStatus == 'L') {
$serialNumber = '<a rel="' . $equipmentOperator->equipmentCode . '" class="itemInfo" >' . $equipmentOperator->serialNumber . '</a>';
$equipmentStatus = EquipmentDAO::GetStatusDescription($equipmentOperator->equipmentStatus);
$telephoneNumber = $equipmentOperator->telephoneNumber;
$reading = '<a href="Frontend/_leitura/editar.php?equipmentCode=' . $equipmentOperator->equipmentCode . '&subContract=0" ><span class="ui-icon ui-icon-alert"></span></a>';
echo '<tr><td>' . $serialNumber . ' (' . $equipmentStatus . ')</td><td>' . $telephoneNumber . '</td><td>' . $reading . '</td></tr>';
$rowCount++;
}
}
}
if (sizeof($itemArray) == 0 || $rowCount == 0) {
// Todas as leituras de hoje foram completadas
echo '<tr><td colspan="3" align="center" >Nenhum registro encontrado!</td></tr>';
}
// Fecha a conexão com o banco de dados
$dataConnector->CloseConnection();
?>
$dataConnector->OpenConnection();
if ($dataConnector->mysqlConnection == null || $dataConnector->sqlserverConnection == null) {
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;
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);
}
}
}
}
