// 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 o objeto de mapeamento objeto-relacional $contractItem = new ContractItemDTO(); // Busca o código do Parceiro de Negócios $businessPartnerCode = ""; $subContractDAO = new SubContractDAO($dataConnector->mysqlConnection); $subContractDAO->showErrors = 1; $subContract = $subContractDAO->RetrieveRecord($subContractId); if ($subContract != null) { $contractDAO = new ContractDAO($dataConnector->mysqlConnection); $contractDAO->showErrors = 1; $contract = $contractDAO->RetrieveRecord($subContract->codigoContrato); $businessPartnerCode = $contract->pn; } ?> <script type="text/javascript" > function GetEquipmentInfo() { var equipmentCode = $("select[name=equipmentCode]").val(); if (!equipmentCode) equipmentCode = 0; var targetUrl = "AjaxCalls/GetEquipmentInfo.php?equipmentCode=" + equipmentCode; $("form[name=fDados]").append("<div id='popup'></div>"); $("#popup").load(targetUrl).dialog({modal:true, width: 560, height: 340, close: function(event, ui) { $(this).dialog('destroy').remove(); }}); }
/** * show table confirm information of ingredient * @param array $inIDs * @param array $unitPrices * @param array $amounts * @return html code. */ public function htmlShowIngredientInfoConfirmTable($MaCTHDs, $unitPrices, $amounts) { $idSize = count($MaCTHDs); $unitSize = count($unitPrices); $amountSize = count($amounts); if ($idSize != $unitSize || $idSize != $amountSize || $unitSize != $amountSize) { return; } $dao = new ContractDAO(); $result = array(); $temp = array(); for ($i = 0; $i < $idSize; $i++) { $data = $dao->getMaterialByContractDetailID($MaCTHDs[$i]); $temp["MaCTHD"] = $data["MaCTHD"]; $temp["TenNL"] = $data["TenNL"]; $temp["DonGia"] = $unitPrices[$i]; $temp["SoLuong"] = $amounts[$i]; $result[] = $temp; } $html = '<p>Xin xác nhận lại thông tin đã nhập và nhấp "Lưu"</p> <table> <tr> <th>Nguyên liệu</th> <th>Đơn giá</th> <th>Số lượng nhập vào</th> </tr>'; foreach ($result as $ingredient) { // generate html $html .= "<tr><td class='cell_import' value=\"" . $ingredient["MaCTHD"] . "\">" . $ingredient["TenNL"] . "</td>"; $html .= ' <td class="cell_import">' . $ingredient["DonGia"] . '</td> <td class="cell_import">' . $ingredient["SoLuong"] . '</td> </tr> '; } $html .= '</table>'; return $html; }
include_once "DataAccessObjects/ContractDAO.php"; include_once "DataTransferObjects/ContractDTO.php"; include_once "DataAccessObjects/SubContractDAO.php"; include_once "DataTransferObjects/SubContractDTO.php"; include_once "DataAccessObjects/BusinessPartnerDAO.php"; include_once "DataTransferObjects/BusinessPartnerDTO.php"; $slpCode = $_REQUEST["slpCode"]; // 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; // Busca os contratos pertencentes ao vendedor $contractArray = $contractDAO->RetrieveRecordArray("vendedor=" . $slpCode . " AND id > 0 ORDER BY convert(numero, signed)"); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-type" content="text/html; charset=UTF-8" /> <meta http-equiv="Content-Language" content="pt-br" /> <meta http-equiv="X-UA-Compatible" content="IE=edge" > <title><?php echo $appTitle;
function GetCommissionRules2() { global $commissionRuleArray2; echo '<table border="0" cellpadding="0" cellspacing="0" >'; echo '<thead>'; echo ' <tr><th> Categoria de contrato</th><th> Quantidade de Contratos</th><th> Valor dos faturamentos</th><th> Comissão</th></tr>'; echo '</thead>'; echo '<tbody>'; if (sizeof($commissionRuleArray2) == 0) { echo '<tr><td colspan="4" align="center" >Nenhum registro encontrado!</td></tr>'; } foreach ($commissionRuleArray2 as $commissionRule) { ?> <tr> <td > <?php echo ContractDAO::GetCategoryAsText($commissionRule->categoriaContrato); ?> </td> <td > <?php $quantContratosDe = $commissionRule->quantContratosDe; $quantContratosAte = $commissionRule->quantContratosAte; echo $quantContratosDe . ' até ' . $quantContratosAte; ?> </td> <td > <?php $valorFaturamentoDe = number_format($commissionRule->valorFaturamentoDe, 2, ',', '.'); $valorFaturamentoAte = number_format($commissionRule->valorFaturamentoAte, 2, ',', '.'); echo $valorFaturamentoDe . ' até ' . $valorFaturamentoAte; ?> </td> <td > <?php echo $commissionRule->comissao . '%'; ?> </td> </tr> <?php } echo '</tbody>'; echo '</table>'; }
$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["sinteseFaturamento"]); if ($nivelAutorizacao <= 1) { DisplayNotAuthorizedWarning(); exit; } // Cria os objetos de mapeamento objeto-relacional $billingDAO = new BillingDAO($dataConnector->mysqlConnection); $billingDAO->showErrors = 1; $mailingDAO = new MailingDAO($dataConnector->mysqlConnection); $mailingDAO->showErrors = 1; $contractDAO = new ContractDAO($dataConnector->mysqlConnection); $contractDAO->showErrors = 1; // Traz os faturamentos de acordo com o mês e ano de referência $mesFaturamento = ConfigDAO::GetConfigurationParam($dataConnector->mysqlConnection, "mesFaturamento"); $anoFaturamento = ConfigDAO::GetConfigurationParam($dataConnector->mysqlConnection, "anoFaturamento"); $billingArray = $billingDAO->RetrieveRecordArray("mesReferencia = '" . $mesFaturamento . "' AND anoReferencia = '" . $anoFaturamento . "'"); ?> <h1>Síntese de Faturamento (<?php $calendar = new Calendar(); echo $calendar->GetMonthName($mesFaturamento) . ' de ' . $anoFaturamento; ?> )</h1><br/> <h1><?php echo str_pad('_', 64, '_', STR_PAD_LEFT); ?> </h1>
$contractTypeDAO = new ContractTypeDAO($dataConnector->mysqlConnection); $contractTypeDAO->showErrors = 1; $contractTypes = $contractTypeDAO->RetrieveRecordArray(); foreach ($contractTypes as $type) { echo '<option value=' . $type->id . ' >' . $type->nome . '</option>'; } ?> </select> </label> <div style="clear:both;"> <br/> </div> <table class="slimTable" style="width:99%"; float:"left"; > <tr><th class="slimTableHeader" ><input type="checkbox" id="selectall" /></th><th class="slimTableHeader" >Status do contrato</th></tr> <?php $contractStatuses = ContractDAO::GetStatusArray(); foreach ($contractStatuses as $id => $status) { $selectionBox = '<input type="checkbox" class="contractStatus" name="contractStatus" value="' . $id . '" />'; echo '<tr><td class="slimTableData" align="center">' . $selectionBox . '</td><td class="slimTableData" >' . $status . '</td></tr>'; } ?> </table> <div style="clear:both;"> <br/> </div> <div class="left" style="width:99%; text-align: center;"> <input id="btnOK" type="button" value="OK" style="width:50px; height:30px;"></input> </div> </form>
foreach ($commissionRuleArray as $commissionRule) { ?> <tr> <td align="center" > <input type="checkbox" value= "<?php echo $commissionRule->id; ?> " name="reg[]"/> </td> <td > <a href="<?php echo 'Frontend/' . $currentDir . '/editar.php?id=' . $commissionRule->id; ?> " > <?php echo ContractDAO::GetCategoryAsText($commissionRule->categoriaContrato); ?> </a> </td> <td > <?php $quantContratosDe = $commissionRule->quantContratosDe; $quantContratosAte = $commissionRule->quantContratosAte; echo $quantContratosDe . ' até ' . $quantContratosAte; ?> </td> <td > <?php $valorFaturamentoDe = number_format($commissionRule->valorFaturamentoDe, 2, ',', '.'); $valorFaturamentoAte = number_format($commissionRule->valorFaturamentoAte, 2, ',', '.'); echo $valorFaturamentoDe . ' até ' . $valorFaturamentoAte;
include_once "../DataTransferObjects/BusinessPartnerDTO.php"; include_once "../DataAccessObjects/ContactPersonDAO.php"; include_once "../DataTransferObjects/ContactPersonDTO.php"; include_once "../DataAccessObjects/ReadingDAO.php"; include_once "../DataTransferObjects/ReadingDTO.php"; $dayOffset = $_GET['dayOffset']; $counterId = $_GET['counterId']; // 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; $readingDAO = new ReadingDAO($dataConnector->mysqlConnection); $readingDAO->showErrors = 1; // Busca os contratos que possuem leitura marcada para hoje $contractArray = $contractDAO->RetrieveRecordArray("status <> 3 AND status <> 4 AND diaLeitura = DAY(DATE_SUB( NOW(), INTERVAL " . $dayOffset . " DAY))"); $contractEnumeration = ""; foreach ($contractArray as $contract) { if (!empty($contractEnumeration)) { $contractEnumeration = $contractEnumeration . ", "; } $contractEnumeration = $contractEnumeration . $contract->id; } if (empty($contractEnumeration)) { $contractEnumeration = "0"; }
include_once "../../DataAccessObjects/ActionLogDAO.php"; include_once "../../DataTransferObjects/ActionLogDTO.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('mySql'); $dataConnector->OpenConnection(); if ($dataConnector->mysqlConnection == 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; $actionLogDAO = new ActionLogDAO($dataConnector->mysqlConnection); $actionLogDAO->showErrors = 1; if ($acao == "store") { $id = 0; $oldContract = new ContractDTO(); $newContract = new ContractDTO(); $transactionType = "INSERT"; if (isset($_REQUEST["id"]) && $_REQUEST["id"] != 0) { $id = $_REQUEST["id"]; $oldContract = $contractDAO->RetrieveRecord($id); $newContract = $contractDAO->RetrieveRecord($id); $transactionType = "UPDATE"; } $lastMonth = mktime(0, 0, 0, date("m") - 1, 1, date("Y"));
include_once "../../DataAccessObjects/BusinessPartnerDAO.php"; include_once "../../DataTransferObjects/BusinessPartnerDTO.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["gerenciamentoContratos"]); if ($nivelAutorizacao <= 1) { DisplayNotAuthorizedWarning(); exit; } // Cria os objetos de mapeamento objeto-relacional $contractDAO = new ContractDAO($dataConnector->mysqlConnection); $contractDAO->showErrors = 1; $subContractDAO = new SubContractDAO($dataConnector->mysqlConnection); $subContractDAO->showErrors = 1; // Traz os contratos cadastrados no sistema $filter = ""; if (isset($_SESSION["slpCode"])) { $filter .= "vendedor=" . $_SESSION["slpCode"] . " AND "; } $filter .= "id > 0 ORDER BY convert(numero, signed)"; $contractArray = $contractDAO->RetrieveRecordArray($filter); ?> <h1>Administração - Contratos</h1> <script type="text/javascript" >
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 "../defines.php"; include_once "../ClassLibrary/DataConnector.php"; include_once "../DataAccessObjects/ContractDAO.php"; include_once "../DataTransferObjects/ContractDTO.php"; include_once "../DataAccessObjects/BusinessPartnerDAO.php"; include_once "../DataTransferObjects/BusinessPartnerDTO.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; } // Cria os objetos de mapeamento objeto-relacional $contractDAO = new ContractDAO($dataConnector->mysqlConnection); $contractDAO->showErrors = 1; $businessPartnerDAO = new BusinessPartnerDAO($dataConnector->sqlserverConnection); $businessPartnerDAO->showErrors = 1; // Traz os contratos que estão fazendo aniversário (a partir da primeira parcela) $dateFilter = "(dataReajuste IS NULL) OR (dataReajuste NOT BETWEEN DATE_SUB(NOW(), INTERVAL 2 MONTH) AND DATE_ADD(NOW(), INTERVAL 2 MONTH))"; $contractArray = $contractDAO->RetrieveRecordArray("(parcelaAtual % 12 = 0 AND parcelaAtual <> quantidadeParcelas) AND (" . $dateFilter . ") AND categoria < 5"); if (sizeof($contractArray) == 0) { echo '<tr><td colspan="2" align="center">Nenhum registro encontrado!</td></tr>'; exit; } foreach ($contractArray as $contract) { // Caso o status do contrato seja finalizado ou cancelado pula para o próximo if ($contract->status == 3 || $contract->status == 4) { continue; }
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; } }
function GetContract($mysqlConnection, $subContractId) { // Cria os objetos de mapeamento objeto-relacional $contractDAO = new ContractDAO($mysqlConnection); $contractDAO->showErrors = 1; $subContractDAO = new SubContractDAO($mysqlConnection); $subContractDAO->showErrors = 1; // Busca o contrato a que pertence $subContract = $subContractDAO->RetrieveRecord($subContractId); $contract = $contractDAO->RetrieveRecord($subContract->codigoContrato); return $contract; }
include_once "../defines.php"; include_once "../ClassLibrary/DataConnector.php"; include_once "../DataAccessObjects/ContractDAO.php"; include_once "../DataTransferObjects/ContractDTO.php"; include_once "../DataAccessObjects/BusinessPartnerDAO.php"; include_once "../DataTransferObjects/BusinessPartnerDTO.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; } // Cria os objetos de mapeamento objeto-relacional $contractDAO = new ContractDAO($dataConnector->mysqlConnection); $contractDAO->showErrors = 1; $businessPartnerDAO = new BusinessPartnerDAO($dataConnector->sqlserverConnection); $businessPartnerDAO->showErrors = 1; // Traz os contratos que estão na última parcela $contractArray = $contractDAO->RetrieveRecordArray("parcelaAtual=quantidadeParcelas AND status <> 3 AND status <> 4 AND categoria < 5"); if (sizeof($contractArray) == 0) { echo '<tr><td colspan="2" align="center">Nenhum registro encontrado!</td></tr>'; exit; } foreach ($contractArray as $contract) { $businessPartner = $businessPartnerDAO->RetrieveRecord($contract->pn); ?> <tr> <td> <a href="Frontend/contrato/editar.php?id=<?php
$contractType = $_GET['contractType']; $contractStatus = $_GET['contractStatus']; $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 $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'";
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); }
include_once "../DataTransferObjects/ContractDTO.php"; include_once "../DataAccessObjects/SubContractDAO.php"; include_once "../DataTransferObjects/SubContractDTO.php"; include_once "../DataAccessObjects/ContractItemDAO.php"; include_once "../DataTransferObjects/ContractItemDTO.php"; $businessPartnerCode = $_GET['businessPartnerCode']; $contractId = $_GET['contractId']; // Abre a conexao com o banco de dados $dataConnector = new DataConnector('mySql'); $dataConnector->OpenConnection(); if ($dataConnector->mysqlConnection == 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; $contractItemDAO = new ContractItemDAO($dataConnector->mysqlConnection); $contractItemDAO->showErrors = 1; $subContractDAO = new SubContractDAO($dataConnector->mysqlConnection); $subContractDAO->showErrors = 1; // Busca os contratos pertencentes ao parceiro de negócios $contractArray = $contractDAO->RetrieveRecordArray("pn='" . $businessPartnerCode . "'"); // Busca os items de contrato pertencentes ao parceiro de negócios $itemArray = $contractItemDAO->RetrieveRecordArray("businessPartnerCode = '" . $businessPartnerCode . "'"); echo '<option value="0" >Agrupar equips. do cliente(independente do contrato)</option>'; $contractIdArray = array(); foreach ($contractArray as $contract) { array_push($contractIdArray, $contract->id); } foreach ($itemArray as $contractItem) {
include_once "../../DataTransferObjects/CounterDTO.php"; $id = $_REQUEST["id"]; // 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 $contractDAO = new ContractDAO($dataConnector->mysqlConnection); $contractDAO->showErrors = 1; $subContractDAO = new SubContractDAO($dataConnector->mysqlConnection); $subContractDAO->showErrors = 1; $contractChargeDAO = new ContractChargeDAO($dataConnector->mysqlConnection); $contractChargeDAO->showErrors = 1; $contractBonusDAO = new ContractBonusDAO($dataConnector->mysqlConnection); $contractBonusDAO->showErrors = 1; $adjustmentRateDAO = new AdjustmentRateDAO($dataConnector->mysqlConnection); $adjustmentRateDAO->showErrors = 1; // Busca os dados do contrato $contract = $contractDAO->RetrieveRecord($id); // Busca o índice de reajuste $adjustmentRate = $adjustmentRateDAO->RetrieveRecord($contract->indiceReajuste); // Busca todos os subcontratos pertencentes ao contrato $subContractArray = $subContractDAO->RetrieveRecordArray("contrato_id = " . $id);
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); } } } }