// 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);
}
}
}
}
