$employeeName = $employee->firstName . " " . $employee->middleName . " " . $employee->lastName; echo "<option " . $attributes . " value=" . $employee->empID . ">" . $employeeName . "</option>"; } ?> </select> </label> <div style="clear:both;"> <br/> </div> <label class="left">Vendedor<br/> <select name="salesPerson" style="width: 350px;"> <?php $salesPersonDAO = new SalesPersonDAO($dataConnector->sqlserverConnection); $salesPersonDAO->showErrors = 1; $salesPersonArray = $salesPersonDAO->RetrieveRecordArray(); foreach ($salesPersonArray as $salesPerson) { $attributes = ""; if ($salesPerson->slpCode == $equipment->salesPerson) { $attributes = "selected='selected'"; } echo "<option " . $attributes . " value=" . $salesPerson->slpCode . ">" . $salesPerson->slpName . "</option>"; } ?> </select> </label> <div style="clear:both;"> <br/> </div> <label class="left">Modelo<br/>
function BuildReportTable($startColumn, $startRow) { global $dataConnector; global $objPhpExcel; global $billingItemArray; global $salesPerson; global $model; global $searchMethod; $billingItemDAO = new BillingItemDAO($dataConnector->mysqlConnection); $billingItemDAO->showErrors = 1; $equipmentDAO = new EquipmentDAO($dataConnector->sqlserverConnection); $equipmentDAO->showErrors = 1; $equipmentModelDAO = new EquipmentModelDAO($dataConnector->mysqlConnection); $equipmentModelDAO->showErrors = 1; $manufacturerDAO = new ManufacturerDAO($dataConnector->sqlserverConnection); $manufacturerDAO->showErrors = 1; $counterDAO = new CounterDAO($dataConnector->mysqlConnection); $counterDAO->showErrors = 1; $salesPersonDAO = new SalesPersonDAO($dataConnector->sqlserverConnection); $salesPersonDAO->showErrors = 1; // Define o titulo da tabela $currentRow = $startRow; $activeSheet = $objPhpExcel->getActiveSheet(); $activeSheet->setCellValue($startColumn . $startRow, 'ITENS DE FATURAMENTO'); $styleArray = array('font' => array('bold' => true, 'size' => 16)); $activeSheet->getStyle($startColumn . $startRow . ':' . $startColumn . $startRow)->applyFromArray($styleArray); // Cria o cabeçalho da tabela $colNum = ord($startColumn) - 65; // startColumn é uma letra no intervalo A-Z $headers = array('Cliente', 'Descrição Equipamento', 'Modelo', 'Fabricante', 'Série Fabricante', 'Nosso Núm. Série', 'Data Instalação', 'Inicio Atendimento', 'Fim Atendimento', 'Medidor', 'Data Leitura', 'Medição Final', 'Medição Inicial', 'Ajuste Medição(Acrésc/Desc)', 'Consumo', 'Franquia', 'Excedente', 'Tarifa sobre exced.', 'Fixo (R$)', 'Variável (R$)', 'Acrésc/Desc (R$)', 'Total (R$)', 'Parcela Atual', 'Vendedor'); $offset = 0; foreach ($headers as $header) { $activeSheet->getColumnDimension(GetNameFromNumber($colNum + $offset))->setWidth(30); $offset++; } $currentRow++; InsereLinhaPlanilha($currentRow, $startColumn, $headers, PHPExcel_Style_Color::COLOR_YELLOW, 30, PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // Busca os fabricantes cadastrados no sistema $manufacturerArray = array(0 => ""); $tempArray = $manufacturerDAO->RetrieveRecordArray(); foreach ($tempArray as $manufacturer) { $manufacturerArray[$manufacturer->FirmCode] = $manufacturer->FirmName; } // Busca os modelos de equipamento cadastrados no sistema $modelArray = array(0 => ""); $equipmentModelArray = $equipmentModelDAO->RetrieveRecordArray(); foreach ($equipmentModelArray as $modelDTO) { $modelArray[$modelDTO->id] = $modelDTO->modelo; } $associativeList = array(0 => ""); foreach ($equipmentModelArray as $modelDTO) { $associativeList[$modelDTO->id] = $manufacturerArray[$modelDTO->fabricante]; } // Busca os contadores cadastrados no sistema $retrievedArray = $counterDAO->RetrieveRecordArray(); $counterArray = array(); foreach ($retrievedArray as $counter) { $counterArray[$counter->id] = $counter->nome; } // Busca os vendedores cadastrados no sistema $retrievedArray = $salesPersonDAO->RetrieveRecordArray(); $salesPersonArray = array(); foreach ($retrievedArray as $salesPersonDTO) { $salesPersonArray[$salesPersonDTO->slpCode] = $salesPersonDTO->slpName; } // Gera as linhas da tabela $grandTotal = 0; foreach ($billingItemArray as $billingItem) { $equipment = $equipmentDAO->RetrieveRecord($billingItem->codigoCartaoEquipamento); $contractCoveragePeriod = ContractDAO::GetContractCoveragePeriod($dataConnector->mysqlConnection, $billingItem->contrato_id); $inicioAtendimento = isset($contractCoveragePeriod) ? $contractCoveragePeriod["inicioAtendimento"] : ''; $fimAtendimento = isset($contractCoveragePeriod) ? $contractCoveragePeriod["fimAtendimento"] : ''; $parcelaAtual = isset($contractCoveragePeriod) ? $contractCoveragePeriod["parcelaAtual"] : ''; $receitaTotal = $billingItem->total + $billingItem->acrescimoDesconto; if ($salesPerson > 0) { if ($equipment->salesPerson != $salesPerson) { continue; } } if ($searchMethod == 1 || $searchMethod == 2) { if (!empty($model)) { $modelMatched = false; if (strpos($equipment->itemName, $model)) { $modelMatched = true; } if (!$modelMatched) { continue; } } } $installationDate = empty($equipment->installationDate) ? '' : $equipment->installationDate->format('d/m/Y'); $salesPersonCode = $equipment->salesPerson; if (empty($salesPersonCode)) { $salesPersonCode = -1; } $salesPersonName = $salesPersonArray[$salesPersonCode]; $currentRow++; $row = array(); $row[0] = $equipment->custmrName; $row[1] = $equipment->itemName; $row[2] = $modelArray[$equipment->model]; $row[3] = $associativeList[$equipment->model]; // Recupera o fabricante do modelo de equipamento $row[4] = $equipment->manufacturerSN; $row[5] = $equipment->internalSN; $row[6] = $installationDate; $row[7] = $inicioAtendimento; $row[8] = $fimAtendimento; $row[9] = $counterArray[$billingItem->counterId]; $row[10] = $billingItem->dataLeitura; $row[11] = $billingItem->medicaoFinal; $row[12] = $billingItem->medicaoInicial; $row[13] = $billingItem->ajuste; $row[14] = $billingItem->consumo; $row[15] = $billingItem->franquia; $row[16] = $billingItem->excedente; $row[17] = number_format($billingItem->tarifaSobreExcedente, 6, ',', '.'); $row[18] = number_format($billingItem->fixo, 2, ',', '.'); $row[19] = number_format($billingItem->variavel, 2, ',', '.'); $row[20] = number_format($billingItem->acrescimoDesconto, 2, ',', '.'); $row[21] = number_format($receitaTotal, 2, ',', '.'); $row[22] = $parcelaAtual; $row[23] = $salesPersonName; InsereLinhaPlanilha($currentRow, $startColumn, $row); $grandTotal += $receitaTotal; // Soma a receita total do equipamento ( fixo mais variável ) } $currentRow++; $total = array('Total da Receita: ' . number_format($grandTotal, 2, ',', '.'), '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0'); InsereLinhaPlanilha($currentRow, $startColumn, $total, '80BB80FF', 45, PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $activeSheet->mergeCells(GetNameFromNumber($colNum + 0) . $currentRow . ':' . GetNameFromNumber($colNum + sizeof($headers) - 1) . $currentRow); }
<label class="left" style="width:45%; text-align: left;">Data Final<br/> <input class="datepick" type="text" name="dataFinal" style="width:95%;height:25px;" value="<?php echo date("d/m/Y", $currentDate->value); ?> " ></input> </label> <div style="clear:both;"> <br/> </div> <label class="left" style="width:99%; text-align: left;">Vendedor<br/> <select name="salesPerson" style="width: 350px;"> <option value=0 >-- Todos --</option> <?php $salesPersonDAO = new SalesPersonDAO($dataConnector->sqlserverConnection); $salesPersonDAO->showErrors = 1; $salesPersonArray = $salesPersonDAO->RetrieveRecordArray("SlpCode > 0"); foreach ($salesPersonArray as $salesPerson) { echo "<option value=" . $salesPerson->slpCode . ">" . $salesPerson->slpName . "</option>"; } ?> </select> </label> <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>
function BuildReportTable($startColumn, $startRow) { global $root; global $dataConnector; global $objPhpExcel; global $billingItemArray; $businessPartnerDAO = new BusinessPartnerDAO($dataConnector->sqlserverConnection); $businessPartnerDAO->showErrors = 1; $industryDAO = new IndustryDAO($dataConnector->sqlserverConnection); $industryDAO->showErrors = 1; $equipmentDAO = new EquipmentDAO($dataConnector->sqlserverConnection); $equipmentDAO->showErrors = 1; $equipmentModelDAO = new EquipmentModelDAO($dataConnector->mysqlConnection); $equipmentModelDAO->showErrors = 1; $manufacturerDAO = new ManufacturerDAO($dataConnector->sqlserverConnection); $manufacturerDAO->showErrors = 1; $contractDAO = new ContractDAO($dataConnector->mysqlConnection); $contractDAO->showErrors = 1; $inventoryItemDAO = new InventoryItemDAO($dataConnector->sqlserverConnection); $inventoryItemDAO->showErrors = 1; $counterDAO = new CounterDAO($dataConnector->mysqlConnection); $counterDAO->showErrors = 1; $salesPersonDAO = new SalesPersonDAO($dataConnector->sqlserverConnection); $salesPersonDAO->showErrors = 1; // Busca os segmentos/ramos de atividade cadastrados no sistema $industryArray = array(0 => ""); $tempArray = $industryDAO->RetrieveRecordArray(); foreach ($tempArray as $industry) { $industryArray[$industry->id] = $industry->name; } // Busca os fabricantes cadastrados no sistema $manufacturerArray = array(0 => ""); $tempArray = $manufacturerDAO->RetrieveRecordArray(); foreach ($tempArray as $manufacturer) { $manufacturerArray[$manufacturer->FirmCode] = $manufacturer->FirmName; } // Busca os modelos de equipamento cadastrados no sistema $modelArray = array(0 => ""); $equipmentModelArray = $equipmentModelDAO->RetrieveRecordArray(); foreach ($equipmentModelArray as $modelDTO) { $modelArray[$modelDTO->id] = $modelDTO->modelo; } // Cria um array para as estatísticas dos equipamentos (por modelo) $statsArray = array(); $equipmentModelArray = $equipmentModelDAO->RetrieveRecordArray("id > 0 ORDER BY fabricante, modelo"); foreach ($equipmentModelArray as $modelDTO) { $statsArray[$modelDTO->id] = new EquipmentModelStats($modelDTO->id, $modelDTO->modelo, $modelDTO->fabricante); } // Cria um array para as receitas de cada equipamento $equipRevenueArray = array(); // Cria um array para as receitas provenientes de cada cliente $customerRevenueArray = array(); $associativeList = array(0 => ""); foreach ($equipmentModelArray as $model) { $associativeList[$model->id] = $manufacturerArray[$model->fabricante]; } // Busca os contadores cadastrados no sistema $retrievedArray = $counterDAO->RetrieveRecordArray(); $counterArray = array(); foreach ($retrievedArray as $counter) { $counterArray[$counter->id] = $counter->nome; } // Busca os vendedores cadastrados no sistema $retrievedArray = $salesPersonDAO->RetrieveRecordArray(); $salesPersonArray = array(); foreach ($retrievedArray as $salesPersonDTO) { $salesPersonArray[$salesPersonDTO->slpCode] = $salesPersonDTO->slpName; } // Define o titulo da tabela $currentRow = $startRow; $activeSheet = $objPhpExcel->getActiveSheet(); $activeSheet->setCellValue($startColumn . $startRow, 'SÍNTESE POR EQUIPAMENTO'); $styleArray = array('font' => array('bold' => true, 'size' => 16)); $activeSheet->getStyle($startColumn . $startRow . ':' . $startColumn . $startRow)->applyFromArray($styleArray); // Cria o cabeçalho da tabela $colNum = ord($startColumn) - 65; // startColumn é uma letra no intervalo A-Z $headers = array('Cliente', 'Segmento', 'Modelo', 'Fabricante', 'Série Fabricante', 'Nosso Núm. Série', 'Obs. Item', 'Data Instalação', 'Inicio Atendimento', 'Fim Atendimento', 'Parcela Atual', 'Vendedor', 'Medidor', 'Data Leitura', 'Medição Final', 'Medição Inicial', 'Ajuste Medição(Acrésc/Desc)', 'Consumo', 'Franquia', 'Excedente (Págs.)', 'Tarifa sobre exced.', 'Valor Fixo (R$)', 'Valor Variável (R$)', 'Acrésc/Desc (R$)', 'Valor Total (R$)', 'Custo Aquisição', 'Vida Útil', 'Custo Pág. do Equip.', 'Custo Pág. Peças/Mat.', 'Custo Pág. Total', 'Custo/Receita', 'Despesas'); $offset = 0; foreach ($headers as $header) { $activeSheet->getColumnDimension(GetNameFromNumber($colNum + $offset))->setWidth(30); $offset++; } $currentRow++; InsereLinhaPlanilha($currentRow, $startColumn, $headers, PHPExcel_Style_Color::COLOR_YELLOW, 30, PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // Gera as linhas da tabela $totalFixo = 0; $totalVariavel = 0; $totalAcrescDesc = 0; $grandTotal = 0; foreach ($billingItemArray as $billingItem) { $equipment = $equipmentDAO->RetrieveRecord($billingItem->codigoCartaoEquipamento); $businessPartner = $businessPartnerDAO->RetrieveRecord($equipment->customer); $contractCoveragePeriod = ContractDAO::GetContractCoveragePeriod($dataConnector->mysqlConnection, $billingItem->contrato_id); $installationDate = empty($equipment->installationDate) ? '' : $equipment->installationDate->format('d/m/Y'); $inicioAtendimento = isset($contractCoveragePeriod) ? $contractCoveragePeriod["inicioAtendimento"] : ''; $fimAtendimento = isset($contractCoveragePeriod) ? $contractCoveragePeriod["fimAtendimento"] : ''; $parcelaAtual = isset($contractCoveragePeriod) ? $contractCoveragePeriod["parcelaAtual"] : ''; $receitaTotal = $billingItem->total + $billingItem->acrescimoDesconto; $inventoryItem = $inventoryItemDAO->RetrieveRecord($equipment->itemCode); $obsItem = $inventoryItem->userText; $custoAquisicao = $inventoryItem->avgPrice; $custoPagPecas = $inventoryItem->expenses; $vidaUtil = $inventoryItem->durability; if (empty($vidaUtil)) { $custoPagEquip = 0; $custoTotal = 0; $custoSobreReceita = 0; } else { $custoPagEquip = $custoAquisicao / $vidaUtil * 1.4; $custoTotal = ($custoPagEquip + $custoPagPecas) * $billingItem->consumo; $custoSobreReceita = 0; if ($receitaTotal != 0) { $custoSobreReceita = $custoTotal / $receitaTotal; } } // Busca os dados do vendedor $salesPersonCode = $equipment->salesPerson; if (empty($salesPersonCode)) { $salesPersonCode = -1; } $salesPersonName = $salesPersonArray[$salesPersonCode]; // Busca os dados do segmento/ramo de atividade $industryCode = $businessPartner->industry; if (empty($industryCode)) { $industryCode = 0; } $industryName = $industryArray[$industryCode]; // Busca o modelo e fabricante do equipamento $modelName = ""; if (array_key_exists($equipment->model, $modelArray)) { $modelName = $modelArray[$equipment->model]; } $manufacturerName = ""; if (array_key_exists($equipment->model, $modelArray)) { $manufacturerName = $associativeList[$equipment->model]; } if (!array_key_exists($equipment->manufacturerSN, $equipRevenueArray)) { $equipRevenueArray[$equipment->manufacturerSN] = new EquipmentRevenue($equipment->manufacturerSN, $equipment->model, $modelName, $manufacturerName, 0); } $equipRevenue = $equipRevenueArray[$equipment->manufacturerSN]; $equipRevenue->revenue += $receitaTotal; if (!array_key_exists($equipment->customer, $customerRevenueArray)) { $customerRevenueArray[$equipment->customer] = new CustomerRevenue($equipment->customer, 0); } $customerRevenue = $customerRevenueArray[$equipment->customer]; $customerRevenue->revenue += $receitaTotal; $row = array(); $row[0] = $businessPartner->cardName . ' (' . $businessPartner->cardCode . ')'; $row[1] = $industryName; $row[2] = $modelName; $row[3] = $manufacturerName; $row[4] = $equipment->manufacturerSN; $row[5] = $equipment->internalSN; $row[6] = $obsItem; $row[7] = $installationDate; $row[8] = $inicioAtendimento; $row[9] = $fimAtendimento; $row[10] = $parcelaAtual; $row[11] = $salesPersonName; $row[12] = $counterArray[$billingItem->counterId]; // Recupera o nome do medidor/contador $row[13] = $billingItem->dataLeitura; $row[14] = $billingItem->medicaoFinal; $row[15] = $billingItem->medicaoInicial; $row[16] = $billingItem->ajuste; $row[17] = $billingItem->consumo; $row[18] = $billingItem->franquia; $row[19] = $billingItem->excedente; $row[20] = formatDecimal($billingItem->tarifaSobreExcedente, null); $row[21] = formatBrCurrency($billingItem->fixo, 2); $row[22] = formatBrCurrency($billingItem->variavel, 2); $row[23] = formatBrCurrency($billingItem->acrescimoDesconto, 2); $row[24] = formatBrCurrency($receitaTotal, 2); $row[25] = formatBrCurrency($custoAquisicao, 2); $row[26] = $vidaUtil; $row[27] = formatDecimal($custoPagEquip, 6); $row[28] = formatDecimal($custoPagPecas, 6); $row[29] = formatBrCurrency($custoTotal, 2); $row[30] = formatDecimal($custoSobreReceita, 6); $row[31] = 'http://datadb/contratos/AjaxCalls/GetEquipmentExpenses.php?equipmentCode=' . $equipment->insID . '&billingId=' . $billingItem->codigoFaturamento . '&showDetails=true'; $currentRow++; InsereLinhaPlanilha($currentRow, $startColumn, $row); $totalFixo += $billingItem->fixo; // Soma os valores fixos dos equipamentos $totalVariavel += $billingItem->variavel; // Soma os valores variáveis dos equipamentos $totalAcrescDesc += $billingItem->acrescimoDesconto; // Soma os acrescimos descontos dos equipamentos $grandTotal += $receitaTotal; // Soma a receita total dos equipamentos ( fixo mais variável ) } $currentRow++; $summary = array('Quant. Clientes: ' . sizeof($customerRevenueArray), '', '', '', 'Quant. Equipamentos: ' . sizeof($equipRevenueArray), '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'Total Fixo: ' . number_format($totalFixo, 2, ',', '.'), 'Total Variável: ' . number_format($totalVariavel, 2, ',', '.'), 'Total Acrésc/Desc: ' . number_format($totalAcrescDesc, 2, ',', '.'), 'Total Receita: ' . number_format($grandTotal, 2, ',', '.'), '', '', '', '', '', '', ''); InsereLinhaPlanilha($currentRow, $startColumn, $summary, '80BB80FF', 32, PHPExcel_Style_Alignment::HORIZONTAL_CENTER); foreach ($equipRevenueArray as $equipRevenue) { if (array_key_exists($equipRevenue->modelId, $statsArray)) { $modelStats = $statsArray[$equipRevenue->modelId]; $modelStats->revenue += $equipRevenue->revenue; $modelStats->equipmentCount += 1; } } $currentRow += 3; // pula 3 linhas $startColumn = 'G'; // fixa o quadro resumo na setima coluna da planilha // Define o titulo do quadro resumo $activeSheet = $objPhpExcel->getActiveSheet(); $activeSheet->setCellValue($startColumn . $currentRow, 'QUADRO RESUMO'); $styleArray = array('font' => array('bold' => true, 'size' => 16)); $activeSheet->getStyle($startColumn . $currentRow . ':' . $startColumn . $currentRow)->applyFromArray($styleArray); // Cria o cabeçalho do quadro resumo $colNum = ord($startColumn); $headers = array('Modelo', 'Fabricante', 'Quantidade', 'Receita'); $offset = 0; foreach ($headers as $header) { $activeSheet->getColumnDimension(chr($colNum + $offset))->setWidth(25); $offset++; } $currentRow++; InsereLinhaPlanilha($currentRow, $startColumn, $headers, PHPExcel_Style_Color::COLOR_YELLOW, 30, PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $valorPrevio = ""; $equipCount = 0; $somaReceita = 0; foreach ($statsArray as $equipmentModelStats) { if ($equipmentModelStats->equipmentCount > 0) { $manufacturerName = $manufacturerArray[$equipmentModelStats->fabricante]; $valorAtual = $manufacturerName; if ($valorPrevio != $valorAtual) { if (!empty($valorPrevio)) { $subTotal = array('', '', $equipCount, number_format($somaReceita, 2, ',', '.')); $currentRow++; InsereLinhaPlanilha($currentRow, $startColumn, $subTotal, '80AAFFFF', 20, PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $equipCount = 0; $somaReceita = 0; } $valorPrevio = $valorAtual; } $currentRow++; $row = array(); $row[0] = $equipmentModelStats->model; $row[1] = $manufacturerName; $row[2] = $equipmentModelStats->equipmentCount; $row[3] = number_format($equipmentModelStats->revenue, 2, ',', '.'); InsereLinhaPlanilha($currentRow, $startColumn, $row); $equipCount = $equipCount + $equipmentModelStats->equipmentCount; $somaReceita = $somaReceita + $equipmentModelStats->revenue; } } $subTotal = array('', '', $equipCount, number_format($somaReceita, 2, ',', '.')); $currentRow++; InsereLinhaPlanilha($currentRow, $startColumn, $subTotal, '80AAFFFF', 20, PHPExcel_Style_Alignment::HORIZONTAL_CENTER); }
function 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 BuildReportTable($startColumn, $startRow) { global $dataConnector; global $objPhpExcel; global $invoiceArray; global $salesPerson; global $model; global $equipmentCode; global $searchMethod; $billingItemDAO = new BillingItemDAO($dataConnector->mysqlConnection); $billingItemDAO->showErrors = 1; $equipmentDAO = new EquipmentDAO($dataConnector->sqlserverConnection); $equipmentDAO->showErrors = 1; $equipmentModelDAO = new EquipmentModelDAO($dataConnector->mysqlConnection); $equipmentModelDAO->showErrors = 1; $salesPersonDAO = new SalesPersonDAO($dataConnector->sqlserverConnection); $salesPersonDAO->showErrors = 1; // Define o titulo da tabela $currentRow = $startRow; $activeSheet = $objPhpExcel->getActiveSheet(); $activeSheet->setCellValue($startColumn . $startRow, 'FATURAS'); $styleArray = array('font' => array('bold' => true, 'size' => 16)); $activeSheet->getStyle($startColumn . $startRow . ':' . $startColumn . $startRow)->applyFromArray($styleArray); // Cria o cabeçalho da tabela $colNum = ord($startColumn); $headers = array('Nº do documento', 'Data', 'Cliente', 'Observações', 'Detalhes', 'Vencimento', 'Total Nota (R$)', 'Nº Demonstrativo'); $offset = 0; foreach ($headers as $header) { $activeSheet->getColumnDimension(chr($colNum + $offset))->setWidth(25); $offset++; } $activeSheet->getColumnDimension(chr($colNum + 2))->setWidth(60); $activeSheet->getColumnDimension(chr($colNum + 4))->setWidth(60); $currentRow++; InsereLinhaPlanilha($currentRow, $startColumn, $headers, PHPExcel_Style_Color::COLOR_YELLOW, 30, PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // Busca os vendedores cadastrados no sistema $retrievedArray = $salesPersonDAO->RetrieveRecordArray(); $salesPersonArray = array(); foreach ($retrievedArray as $salesPersonDTO) { $salesPersonArray[$salesPersonDTO->slpCode] = $salesPersonDTO->slpName; } // Busca os modelos cadastrados no sistema $modelArray = array(0 => ""); $equipmentModelArray = $equipmentModelDAO->RetrieveRecordArray(); foreach ($equipmentModelArray as $modelDTO) { $modelArray[$modelDTO->id] = $modelDTO->modelo; } // Gera as linhas da tabela $grandTotal = 0; $LFCR = chr(10) . chr(13); foreach ($invoiceArray as $invoice) { $docDate = empty($invoice->docDate) ? '' : $invoice->docDate->format('d/m/Y'); $docDueDate = empty($invoice->docDueDate) ? '' : $invoice->docDueDate->format('d/m/Y'); $docTotal = number_format($invoice->docTotal, 2, ',', '.'); $details = ""; $salesPersons = array(); $itemNames = ""; $equipmentIds = array(); $billingItemArray = $billingItemDAO->RetrieveRecordArray("codigoFaturamento=" . $invoice->demFaturamento); foreach ($billingItemArray as $billingItem) { $equipment = $equipmentDAO->RetrieveRecord($billingItem->codigoCartaoEquipamento); $equipmentSerial = $equipment->manufacturerSN; $salesPersonCode = $equipment->salesPerson; if (empty($salesPersonCode)) { $salesPersonCode = -1; } $salesPersonName = $salesPersonArray[$salesPersonCode]; $details .= $equipmentSerial . ' ' . $salesPersonName . $LFCR; if ($salesPersonCode > 0) { array_push($salesPersons, $salesPersonCode); } $itemNames .= $equipment->itemName; if ($equipment->insID > 0) { array_push($equipmentIds, $equipment->insID); } } if ($salesPerson > 0) { if (!in_array($salesPerson, $salesPersons)) { continue; } } if ($searchMethod == 1 || $searchMethod == 2) { if (!empty($model)) { $modelMatched = false; if (strpos($itemNames, $model)) { $modelMatched = true; } if (!$modelMatched) { continue; } } } if ($searchMethod == 3) { if (!in_array($equipmentCode, $equipmentIds)) { continue; } } $currentRow++; $row = array(); $row[0] = $invoice->docNum; $row[1] = $docDate; $row[2] = $invoice->cardName; $row[3] = $invoice->comments; $row[4] = $details; $row[5] = $docDueDate; $row[6] = $docTotal; $row[7] = $invoice->demFaturamento; InsereLinhaPlanilha($currentRow, $startColumn, $row); $grandTotal += $invoice->docTotal; } $currentRow++; $total = array('Total Geral: ' . number_format($grandTotal, 2, ',', '.'), '0', '0', '0', '0', '0', '0', '0'); InsereLinhaPlanilha($currentRow, $startColumn, $total, '80BB80FF', 45, PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $activeSheet->mergeCells(chr($colNum + 0) . $currentRow . ':' . chr($colNum + sizeof($headers) - 1) . $currentRow); }
function BuildReportTable($startColumn, $startRow) { global $dataConnector; global $objPhpExcel; global $invoicePaymentArray; $salesPersonDAO = new SalesPersonDAO($dataConnector->sqlserverConnection); $salesPersonDAO->showErrors = 1; $equipmentDAO = new EquipmentDAO($dataConnector->sqlserverConnection); $equipmentDAO->showErrors = 1; $businessPartnerDAO = new BusinessPartnerDAO($dataConnector->sqlserverConnection); $businessPartnerDAO->showErrors = 1; $industryDAO = new IndustryDAO($dataConnector->sqlserverConnection); $industryDAO->showErrors = 1; $contractDAO = new ContractDAO($dataConnector->mysqlConnection); $contractDAO->showErrors = 1; $billingDAO = new BillingDAO($dataConnector->mysqlConnection); $billingDAO->showErrors = 1; $billingItemDAO = new BillingItemDAO($dataConnector->mysqlConnection); $billingItemDAO->showErrors = 1; $commissionPerSignatureDAO = new CommissionPerSignatureDAO($dataConnector->mysqlConnection); $commissionPerSignatureDAO->showErrors = 1; // Define o titulo da tabela $currentRow = $startRow; $activeSheet = $objPhpExcel->getActiveSheet(); $activeSheet->setCellValue($startColumn . $startRow, 'PAGAMENTOS'); $styleArray = array('font' => array('bold' => true, 'size' => 16)); $activeSheet->getStyle($startColumn . $startRow . ':' . $startColumn . $startRow)->applyFromArray($styleArray); // Cria o cabeçalho da tabela $colNum = ord($startColumn); $headers = array('Nº NF', 'Tipo', 'Cliente', 'Segmento', 'Nº Boleto', 'Valor Cobrado', 'Valor Recebido', 'Data Pagamento', 'Demonstrativo Fat.', 'Descritivo Faturamento'); $offset = 0; foreach ($headers as $header) { $activeSheet->getColumnDimension(chr($colNum + $offset))->setWidth(25); $offset++; } $activeSheet->getColumnDimension(chr($colNum + $offset - 1))->setWidth(50); $currentRow++; InsereLinhaPlanilha($currentRow, $startColumn, $headers, PHPExcel_Style_Color::COLOR_YELLOW, 30, PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // Cria um array para as estatísticas dos vendedores $statsArray = array(); $salesPersonArray = $salesPersonDAO->RetrieveRecordArray(); foreach ($salesPersonArray as $salesPerson) { $statsArray[$salesPerson->slpCode] = new SalesPerson($salesPerson->slpCode, $salesPerson->slpName); } // Cria um array para as regras de comissão $rulesArray = array(0 => new CommissionRule(0, "", "", 0)); $commissionRuleArray = $commissionPerSignatureDAO->RetrieveRecordArray(); foreach ($commissionRuleArray as $commissionRuleDTO) { $rulesArray[$commissionRuleDTO->id] = new CommissionRule($commissionRuleDTO->segmento, $commissionRuleDTO->dataAssinaturaDe, $commissionRuleDTO->dataAssinaturaAte, $commissionRuleDTO->comissao); } // Busca os segmentos/ramos de atividade cadastrados no sistema $industryArray = array(-1 => "- Nenhum Segmento -"); $tempArray = $industryDAO->RetrieveRecordArray(); foreach ($tempArray as $industry) { $industryArray[$industry->id] = $industry->name; } foreach ($invoicePaymentArray as $payment) { $billingDescription = ""; $billing = new BillingDTO(); if ($payment->demFaturamento >= 1) { $billing = $billingDAO->RetrieveRecord($payment->demFaturamento); if (!isset($billing)) { $billing = new BillingDTO(); } } $industry = -1; // Busca os itens de faturamento $billingItemArray = $billingItemDAO->RetrieveRecordArray("codigoFaturamento = '" . $billing->id . "'"); $quantidadeItens = sizeof($billingItemArray); if ($quantidadeItens < 1) { $quantidadeItens = 1; } $multaRecisoria = $billing->multaRecisoria / $quantidadeItens; foreach ($billingItemArray as $billingItem) { $equipment = $equipmentDAO->RetrieveRecord($billingItem->codigoCartaoEquipamento); $businessPartner = $businessPartnerDAO->RetrieveRecord($payment->cardCode); if (isset($businessPartner->industry)) { $industry = $businessPartner->industry; } if ($equipment->salesPerson > 0) { $contract = $contractDAO->RetrieveRecord($billingItem->contrato_id); $salesPersonStats = $statsArray[$equipment->salesPerson]; if (!isset($salesPersonStats->statistics)) { $salesPersonStats->statistics = array(); } $ruleKey = 0; $comissao = 0; $commissionRuleArray = $commissionPerSignatureDAO->RetrieveRecordArray("(segmento = 0 OR segmento = " . $industry . ") AND dataAssinaturaDe <= '" . $contract->dataAssinatura . "' AND dataAssinaturaAte >= '" . $contract->dataAssinatura . "' ORDER BY segmento DESC, comissao DESC"); if (sizeof($commissionRuleArray) > 0) { $rule = $commissionRuleArray[0]; $ruleKey = $rule->id; $comissao = $rule->comissao; } if (!array_key_exists($ruleKey, $salesPersonStats->statistics)) { $salesPersonStats->statistics[$ruleKey] = new SalesPersonStats($ruleKey); } $ruleStats = $salesPersonStats->statistics[$ruleKey]; $ruleStats->comissionRate = $comissao; $ruleStats->revenue += $billingItem->total + $billingItem->acrescimoDesconto - $multaRecisoria; if (!IsContractAdded($contract, $ruleStats)) { $ruleStats->contractCount++; if (!isset($ruleStats->contractArray)) { $ruleStats->contractArray = array(); } $ruleStats->contractArray[$contract->id] = $contract->id; } } $LFCR = chr(10) . chr(13); $salesPersonName = SalesPersonDAO::GetSalesPersonName($dataConnector->sqlserverConnection, $equipment->salesPerson); $billingDescription .= $salesPersonName . ' Serial ' . $equipment->manufacturerSN . ' R$ ' . $billingItem->total . $LFCR; } $currentRow++; $row = array(); $row[0] = $payment->serial; $row[1] = GetInvoiceTypeAsText($payment->tipo); $row[2] = $payment->cardName; $row[3] = $industryArray[$industry]; $row[4] = GetBOENumber($payment); $row[5] = GetDocTotal($payment); $row[6] = GetReceivedAmount($payment); $row[7] = empty($payment->date) ? '' : $payment->date->format('d/m/Y'); $row[8] = empty($billing->id) ? ' - ' : str_pad($billing->id, 5, '0', STR_PAD_LEFT); $row[9] = $billingDescription; InsereLinhaPlanilha($currentRow, $startColumn, $row); } $currentRow += 3; // pula 3 linhas // Define o titulo do quadro resumo $activeSheet = $objPhpExcel->getActiveSheet(); $activeSheet->setCellValue($startColumn . $currentRow, 'COMISSÕES'); $styleArray = array('font' => array('bold' => true, 'size' => 16)); $activeSheet->getStyle($startColumn . $currentRow . ':' . $startColumn . $currentRow)->applyFromArray($styleArray); // Cria o cabeçalho do quadro resumo $colNum = ord($startColumn); $headers = array('Vendedor', 'Segmento', 'Data de Assinatura', 'Quantidade de Contratos', 'Faturamento (R$)', 'Comissão (%)', 'Comissão (R$)'); $offset = 0; foreach ($headers as $header) { $activeSheet->getColumnDimension(chr($colNum + $offset))->setWidth(25); $offset++; } $currentRow++; InsereLinhaPlanilha($currentRow, $startColumn, $headers, PHPExcel_Style_Color::COLOR_YELLOW, 30, PHPExcel_Style_Alignment::HORIZONTAL_CENTER); foreach ($statsArray as $salesPersonStats) { if (isset($salesPersonStats->statistics)) { $statistics = $salesPersonStats->statistics; foreach ($statistics as $stat) { $commissionRule = $rulesArray[$stat->index]; $industryName = "Todos"; if ($commissionRule->segmento > 0) { $industryName = $industryArray[$commissionRule->segmento]; } $period = ""; if ($commissionRule->comissao > 0) { $period = $commissionRule->dataAssinaturaDe . ' até ' . $commissionRule->dataAssinaturaAte; } $comissionPercentage = $stat->comissionRate; $comissionValue = $stat->comissionRate / 100 * $stat->revenue; $currentRow++; $row = array(); $row[0] = $salesPersonStats->name; $row[1] = $industryName; $row[2] = $period; $row[3] = $stat->contractCount; $row[4] = number_format($stat->revenue, 2, ',', '.'); $row[5] = $comissionPercentage; $row[6] = number_format($comissionValue, 2, ',', '.'); InsereLinhaPlanilha($currentRow, $startColumn, $row); } } } }