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('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
$invoiceDAO = new InvoiceDAO($dataConnector->sqlserverConnection);
$invoiceDAO->showErrors = 1;
$billingDAO = new BillingDAO($dataConnector->mysqlConnection);
$billingDAO->showErrors = 1;
$mailingDAO = new MailingDAO($dataConnector->mysqlConnection);
$mailingDAO->showErrors = 1;
$billingItemDAO = new BillingItemDAO($dataConnector->mysqlConnection);
$billingItemDAO->showErrors = 1;
$contractItemDAO = new ContractItemDAO($dataConnector->mysqlConnection);
$contractItemDAO->showErrors = 1;
if ($acao == "store") {
$mailing_id = $_REQUEST["mailingId"];
$startDate = $_REQUEST["dataInicial"];
$endDate = $_REQUEST["dataFinal"];
$mailing = $mailingDAO->RetrieveRecord($mailing_id);
$billingParams = 'businessPartnerCode=' . $mailing->businessPartnerCode . '&contractId=' . $mailing->contrato_id . '&subContractId=' . $mailing->subContrato_id . '&startDate=' . $startDate . '&endDate=' . $endDate . '&acrescimo=&obs=';
$report = "faturamentoContrato.php";
if (empty($mailing->contrato_id)) {
include_once "../DataTransferObjects/InvoiceDTO.php";
$mailingId = $_GET['mailingId'];
// 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["envioFaturamento"]);
if ($nivelAutorizacao <= 1) {
DisplayNotAuthorizedWarning();
exit;
}
// Cria os objetos de mapeamento objeto-relacional
$billingDAO = new BillingDAO($dataConnector->mysqlConnection);
$billingDAO->showErrors = 1;
$invoiceDAO = new InvoiceDAO($dataConnector->sqlserverConnection);
$invoiceDAO->showErrors = 1;
// Busca os registros de faturamento cadastrados no mailing
$billingArray = $billingDAO->RetrieveRecordArray("mailing_id=" . $mailingId);
if (sizeof($billingArray) == 0) {
echo "<tr>";
echo " <td colspan='7' align='center' >Nenhum registro encontrado!</td>";
echo "</tr>";
exit;
}
foreach ($billingArray as $billing) {
$invoiceNum = 0;
$invoiceArray = $invoiceDAO->RetrieveRecordArray(null, "U_demFaturamento = '" . $billing->id . "'");
if (sizeof($invoiceArray) > 0) {
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["envioFaturamento"]);
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;
$invoiceDAO = new InvoiceDAO($dataConnector->sqlserverConnection);
$invoiceDAO->showErrors = 1;
$id = 0;
$billing = new BillingDTO();
$mailing = new MailingDTO();
$invoiceNum = 0;
if (isset($_REQUEST["id"]) && $_REQUEST["id"] != 0) {
$id = $_REQUEST["id"];
$billing = $billingDAO->RetrieveRecord($id);
if ($billing == null) {
echo '<br/><h1>Demonstrativo de faturamento não encontrado</h1><br/>';
exit;
include_once "../../DataAccessObjects/ContractDAO.php";
include_once "../../DataTransferObjects/ContractDTO.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["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/>
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);
}
}
}
}
