$businessPartnerCode = $_GET['businessPartnerCode'];
$model = $_GET['model'];
$equipmentCode = $_GET['equipmentCode'];
$billingMonth = $_GET['billingMonth'];
$billingYear = $_GET['billingYear'];
$salesPerson = $_GET['salesPerson'];
$searchMethod = $_GET['searchMethod'];
// 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
$billingItemDAO = new BillingItemDAO($dataConnector->mysqlConnection);
$billingItemDAO->showErrors = 1;
// Busca os faturamentos que se enquadram no filtro aplicado
$billingItemArray = array();
if ($searchMethod == 0 || $searchMethod == 2) {
    $filter = "businessPartnerCode='" . $businessPartnerCode . "' AND mesReferencia = " . $billingMonth . " AND anoReferencia = " . $billingYear . " AND incluirRelatorio=1";
    $joins = "JOIN faturamento ON item.codigoFaturamento = faturamento.id";
    $billingItemArray = $billingItemDAO->RetrieveRecordArray2($filter, $joins);
}
if ($searchMethod == 1) {
    $filter = "mesReferencia = " . $billingMonth . " AND anoReferencia = " . $billingYear . " AND incluirRelatorio=1";
    $joins = "JOIN faturamento ON item.codigoFaturamento = faturamento.id";
    $billingItemArray = $billingItemDAO->RetrieveRecordArray2($filter, $joins);
}
if ($searchMethod == 3) {
    $filter = "codigoCartaoEquipamento=" . $equipmentCode . " AND mesReferencia = " . $billingMonth . " AND anoReferencia = " . $billingYear . " AND incluirRelatorio=1";
$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)) {
        $report = "faturamentoCliente.php";
    }
    $reportUrl = 'http://' . $_SERVER['HTTP_HOST'] . $root . '/Frontend/mailing/' . $report . '?' . $billingParams . '&valuesOnly=true';
    $billingContent = file_get_contents($reportUrl);
$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
$invoiceDAO = new InvoiceDAO($dataConnector->sqlserverConnection);
$invoiceDAO->showErrors = 1;
$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;
// Busca as notas canceladas no período
$returnedInvoiceArray = $invoiceDAO->RetrieveReturnedInvoices("(T0.DocDate BETWEEN '" . $startDate . "' AND '" . $endDate . "' OR T3.DocDate BETWEEN '" . $startDate . "' AND '" . $endDate . "') AND T3.DocTotal > 0");
$returnedInvoiceList = "";
foreach ($returnedInvoiceArray as $returnedInvoice) {
    if (!empty($returnedInvoiceList)) {
        $returnedInvoiceList .= ", ";
    }
    $returnedInvoiceList = $returnedInvoiceList . $returnedInvoice->docNum;
Exemple #4
0
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
$billingDAO = new BillingDAO($dataConnector->mysqlConnection);
$billingDAO->showErrors = 1;
$mailingDAO = new MailingDAO($dataConnector->mysqlConnection);
$mailingDAO->showErrors = 1;
$billingItemDAO = new BillingItemDAO($dataConnector->mysqlConnection);
$billingItemDAO->showErrors = 1;
$equipmentDAO = new EquipmentDAO($dataConnector->sqlserverConnection);
$equipmentDAO->showErrors = 1;
// Recupera os dados do faturamento
$billing = $billingDAO->RetrieveRecord($billingId);
$mailing = $mailingDAO->RetrieveRecord($billing->mailing_id);
$billingItemArray = $billingItemDAO->RetrieveRecordArray("codigoFaturamento=" . $billing->id);
$clientName = BusinessPartnerDAO::GetClientName($dataConnector->sqlserverConnection, $mailing->businessPartnerCode);
// Define o filtro do relatório
$filter = 'Data Inicial: ' . $billing->dataInicial . '   Data Final: ' . $billing->dataFinal;
// Busca os contadores cadastrados no sistema
$counterDAO = new CounterDAO($dataConnector->mysqlConnection);
$counterDAO->showErrors = 1;
$counterArray = $counterDAO->RetrieveRecordArray();
?>
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);
            }
        }
    }
}