function BuildReportTable($startColumn, $startRow)
{
    global $dataConnector;
    global $objPhpExcel;
    global $equipmentReadingArray;
    $businessPartnerDAO = new BusinessPartnerDAO($dataConnector->sqlserverConnection);
    $businessPartnerDAO->showErrors = 1;
    // Define o titulo da tabela
    $currentRow = $startRow;
    $activeSheet = $objPhpExcel->getActiveSheet();
    $activeSheet->setCellValue($startColumn . $startRow, 'LEITURAS');
    $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('Data de Leitura', 'Cliente', 'Série', 'Modelo', 'Fabricante', 'Tipo do Medidor', 'Medição', 'Ajuste de Leitura', 'Reset(S OU N)', 'Assinatura Datacopy', 'Assinatura Cliente', 'Observação', 'Forma de Leitura', 'Origem');
    $offset = 0;
    foreach ($headers as $header) {
        $activeSheet->getColumnDimension(chr($colNum + $offset))->setWidth(25);
        $offset++;
    }
    $activeSheet->getColumnDimension(chr($colNum + 1))->setWidth(60);
    $currentRow++;
    InsereLinhaPlanilha($currentRow, $startColumn, $headers, PHPExcel_Style_Color::COLOR_YELLOW, 30, PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    // Gera as linhas da tabela
    foreach ($equipmentReadingArray as $equipmentReading) {
        $currentRow++;
        $row = array();
        $row[0] = empty($equipmentReading->dataLeitura) ? '' : $equipmentReading->dataLeitura->format('d/m/Y');
        $row[1] = $equipmentReading->nomeCliente;
        $row[2] = $equipmentReading->serieEquipamento;
        $row[3] = $equipmentReading->tagModelo;
        $row[4] = $equipmentReading->fabricante;
        $row[5] = $equipmentReading->tipoMedidor;
        $row[6] = $equipmentReading->medicao;
        $row[7] = $equipmentReading->ajusteLeitura;
        $row[8] = $equipmentReading->reset;
        $row[9] = $equipmentReading->assinaturaDatacopy;
        $row[10] = $equipmentReading->assinaturaCliente;
        $row[11] = $equipmentReading->observacao;
        $row[12] = $equipmentReading->formaLeitura;
        $row[13] = $equipmentReading->origemLeitura;
        InsereLinhaPlanilha($currentRow, $startColumn, $row);
    }
}
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);
}
function BuildReportTable($startColumn, $startRow)
{
    global $dataConnector;
    global $objPhpExcel;
    global $month;
    global $year;
    global $despesaMensal;
    global $laborExpenseArray;
    $serviceCallDAO = new ServiceCallDAO($dataConnector->mysqlConnection);
    $serviceCallDAO->showErrors = 1;
    $serviceStatisticsDAO = new ServiceStatisticsDAO($dataConnector->mysqlConnection);
    $serviceStatisticsDAO->showErrors = 1;
    // Define o titulo da tabela
    $currentRow = $startRow;
    $activeSheet = $objPhpExcel->getActiveSheet();
    $activeSheet->setCellValue($startColumn . $startRow, 'CUSTOS DE MÃO DE OBRA');
    $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('Cliente', 'Nº Série Fabricante', 'Modelo', 'Fabricante', 'Número do Chamado', 'Tempo de Atendimento', 'Tempo Total Area Técnica', 'Valor Despesa (R$)');
    $offset = 0;
    foreach ($headers as $header) {
        $activeSheet->getColumnDimension(chr($colNum + $offset))->setWidth(25);
        $offset++;
    }
    $activeSheet->getColumnDimension(chr($colNum + 3))->setWidth(60);
    $currentRow++;
    InsereLinhaPlanilha($currentRow, $startColumn, $headers, PHPExcel_Style_Color::COLOR_YELLOW, 30, PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    // Busca as estatísticas de atendimento do mês escolhido
    $stats = $serviceStatisticsDAO->RetrieveRecord($month, $year);
    $tempoTotalAtendimento = $stats->tempoEmAtendimento;
    $totalEmSegundos = $stats->totalEmSegundos;
    // Gera as linhas da tabela
    $grandTotal = 0;
    $valorPrevio = "";
    $totalCliente = 0;
    foreach ($laborExpenseArray as $laborExpense) {
        $serviceCall = $serviceCallDAO->RetrieveRecord($laborExpense->numeroChamado);
        $valorDespesa = $serviceCall->duracaoEmSegundos / $totalEmSegundos * $despesaMensal;
        $valorAtual = $laborExpense->nomeCliente;
        if ($valorPrevio != $valorAtual) {
            if (!empty($valorPrevio)) {
                $currentRow++;
                $subTotal = array($valorPrevio, '', '', '', '', '', '', number_format($totalCliente, 2, ',', '.'));
                InsereLinhaPlanilha($currentRow, $startColumn, $subTotal, '80AAFFFF', 20, PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
                $totalCliente = 0;
            }
            $valorPrevio = $valorAtual;
        }
        $currentRow++;
        $row = array();
        $row[0] = $laborExpense->nomeCliente;
        $row[1] = $laborExpense->serieEquipamento;
        $row[2] = $laborExpense->tagModelo;
        $row[3] = $laborExpense->fabricante;
        $row[4] = $laborExpense->numeroChamado;
        $row[5] = $serviceCall->tempoAtendimento;
        $row[6] = $tempoTotalAtendimento;
        $row[7] = number_format($valorDespesa, 2, ',', '.');
        InsereLinhaPlanilha($currentRow, $startColumn, $row);
        $grandTotal += $valorDespesa;
        $totalCliente += $valorDespesa;
    }
    $currentRow++;
    $subTotal = array($valorPrevio, '', '', '', '', '', '', number_format($totalCliente, 2, ',', '.'));
    InsereLinhaPlanilha($currentRow, $startColumn, $subTotal, '80AAFFFF', 20, PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
    $currentRow++;
    $total = array('Total Geral: ' . number_format($grandTotal, 2, ',', '.'), '0', '0', '0', '0', '0', '0', '0');
    InsereLinhaPlanilha($currentRow, $startColumn, $total, 'FFAA2040', 32, 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 $serviceCallArray;
    $serviceCallDAO = new ServiceCallDAO($dataConnector->mysqlConnection);
    $serviceCallDAO->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;
    $readingDAO = new ReadingDAO($dataConnector->mysqlConnection);
    $readingDAO->showErrors = 1;
    $counterDAO = new CounterDAO($dataConnector->mysqlConnection);
    $counterDAO->showErrors = 1;
    $employeeDAO = new EmployeeDAO($dataConnector->sqlserverConnection);
    $employeeDAO->showErrors = 1;
    // Define o titulo da tabela
    $currentRow = $startRow;
    $activeSheet = $objPhpExcel->getActiveSheet();
    $activeSheet->setCellValue($startColumn . $startRow, 'CHAMADOS');
    $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('Data Abertura', 'Nº do chamado', 'Status', 'Cliente', 'Depto.', 'Modelo', 'Série', 'Fabricante', 'Defeito', 'Data Atendimento', 'Horário/Duração', 'Sintoma', 'Causa', 'Ação', 'Observação Técnica', 'Contadores', 'Aberto Por', 'Técnico');
    $offset = 0;
    foreach ($headers as $header) {
        $activeSheet->getColumnDimension(chr($colNum + $offset))->setWidth(25);
        $offset++;
    }
    $activeSheet->getColumnDimension(chr($colNum + 3))->setWidth(50);
    $activeSheet->getColumnDimension(chr($colNum + 4))->setWidth(35);
    $activeSheet->getColumnDimension(chr($colNum + 8))->setWidth(60);
    $activeSheet->getColumnDimension(chr($colNum + 11))->setWidth(60);
    $activeSheet->getColumnDimension(chr($colNum + 12))->setWidth(60);
    $activeSheet->getColumnDimension(chr($colNum + 13))->setWidth(60);
    $activeSheet->getColumnDimension(chr($colNum + 14))->setWidth(35);
    $activeSheet->getColumnDimension(chr($colNum + 16))->setWidth(40);
    $activeSheet->getColumnDimension(chr($colNum + 17))->setWidth(40);
    $currentRow++;
    InsereLinhaPlanilha($currentRow, $startColumn, $headers, PHPExcel_Style_Color::COLOR_YELLOW, 30, PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    // Busca os Status de chamado cadastrados no sistema
    $statusArray = ServiceCallDAO::RetrieveServiceCallStatuses($dataConnector->sqlserverConnection);
    // 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 funcionários cadastrados no sistema
    $retrievedArray = $employeeDAO->RetrieveRecordArray("empID IS NOT NULL ORDER BY empID");
    $employeeArray = array();
    foreach ($retrievedArray as $employee) {
        $employeeArray[$employee->empID] = $employee->firstName . " " . $employee->middleName . " " . $employee->lastName;
    }
    // Cria um array para as estatísticas dos técnicos
    $statsArray = array(0 => "");
    foreach ($employeeArray as $id => $name) {
        // considera todos os funcionários como possíveis técnicos, depois filtra somente os que realizaram atendimentos
        $statsArray[$id] = new Technician($id, $name);
    }
    // Gera as linhas da tabela
    $quantChamados = 0;
    foreach ($serviceCallArray as $serviceCall) {
        $equipment = $equipmentDAO->RetrieveRecord($serviceCall->codigoCartaoEquipamento);
        if (!isset($equipment)) {
            $equipment = new EquipmentDTO();
        }
        // cria objeto vazio em caso de erro
        $readingArray = $readingDAO->RetrieveRecordArray("chamadoServico_id=" . $serviceCall->id);
        $counters = "";
        $LFCR = chr(10) . chr(13);
        foreach ($readingArray as $reading) {
            if (!empty($counters)) {
                $counters = $counters . $LFCR;
            }
            $counters = $counters . $counterArray[$reading->codigoContador] . ' ' . $reading->contagem;
        }
        $modelName = "";
        if (array_key_exists($equipment->model, $modelArray)) {
            $modelName = $modelArray[$equipment->model];
        }
        $manufacturerName = "";
        if (array_key_exists($equipment->model, $modelArray)) {
            $manufacturerName = $associativeList[$equipment->model];
        }
        $creator = " - ";
        if ($serviceCall->abertoPor > 0) {
            $creator = $employeeArray[$serviceCall->abertoPor];
        }
        $technicianName = " - ";
        if ($serviceCall->tecnico > 0) {
            $technicianName = $employeeArray[$serviceCall->tecnico];
        }
        $technicianStats = $statsArray[$serviceCall->tecnico];
        if (!isset($technicianStats->statistics)) {
            $technicianStats->statistics = array();
        }
        if (!array_key_exists($equipment->model, $technicianStats->statistics)) {
            $technicianStats->statistics[$equipment->model] = new EquipmentModelStats($equipment->model, $modelName, $manufacturerName);
        }
        $equipmentModelStat = $technicianStats->statistics[$equipment->model];
        $equipmentModelStat->serviceCallCount++;
        $parts = explode(":", $serviceCall->tempoAtendimento, 2);
        $equipmentModelStat->tempoTotalAtendimento += (int) $parts[0] + (int) $parts[1] / 60;
        $currentRow++;
        $row = array();
        $row[0] = $serviceCall->dataAbertura;
        $row[1] = str_pad($serviceCall->id, 5, '0', STR_PAD_LEFT);
        $row[2] = $statusArray[$serviceCall->status];
        $row[3] = $equipment->custmrName;
        $row[4] = $equipment->instLocation;
        $row[5] = $modelName;
        $row[6] = $equipment->manufacturerSN;
        $row[7] = $manufacturerName;
        $row[8] = $serviceCall->defeito;
        $row[9] = $serviceCall->dataAtendimento;
        $row[10] = $serviceCall->horaAtendimento . " (Duração " . $serviceCall->tempoAtendimento . ")";
        $row[11] = $serviceCall->sintoma;
        $row[12] = $serviceCall->causa;
        $row[13] = $serviceCall->acao;
        $row[14] = $serviceCall->observacaoTecnica;
        $row[15] = $counters;
        $row[16] = $creator;
        $row[17] = $technicianName;
        InsereLinhaPlanilha($currentRow, $startColumn, $row);
        $quantChamados++;
        // Faz a contagem dos chamados técnicos
    }
    $currentRow++;
    $footer = array('Quantidade de Chamados: ' . $quantChamados . ' (Depende do filtro escolhido)', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0');
    InsereLinhaPlanilha($currentRow, $startColumn, $footer, '80BB80FF', 32, PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $activeSheet->mergeCells(chr($colNum + 0) . $currentRow . ':' . chr($colNum + sizeof($headers) - 1) . $currentRow);
    $currentRow += 3;
    // pula 3 linhas
    // 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('Técnico', 'Modelo', 'Fabricante', 'Quant. Chamados', 'Tempo Médio Atendimento');
    $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);
    $callCount = 0;
    $timeSum = 0;
    foreach ($statsArray as $technicianStats) {
        if (!isset($technicianStats->statistics)) {
            continue;
        }
        $valorPrevio = "";
        $serviceCallCount = 0;
        $tempoTotalAtendimento = 0;
        foreach ($technicianStats->statistics as $equipmentModelStats) {
            if ($equipmentModelStats->serviceCallCount > 0) {
                $valorAtual = $equipmentModelStats->fabricante;
                if ($valorPrevio != $valorAtual) {
                    if (!empty($valorPrevio)) {
                        $subTotal = array('', '', '', $callCount, UnixTime::ConvertToTime($timeSum / $callCount));
                        $currentRow++;
                        InsereLinhaPlanilha($currentRow, $startColumn, $subTotal, '80AAFFFF', 20, PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                        $callCount = 0;
                        $timeSum = 0;
                    }
                    $valorPrevio = $valorAtual;
                }
                $currentRow++;
                $row = array();
                $row[0] = $technicianStats->name;
                $row[1] = $equipmentModelStats->model;
                $row[2] = $equipmentModelStats->fabricante;
                $row[3] = $equipmentModelStats->serviceCallCount;
                $row[4] = UnixTime::ConvertToTime($equipmentModelStats->tempoTotalAtendimento / $equipmentModelStats->serviceCallCount);
                InsereLinhaPlanilha($currentRow, $startColumn, $row);
                $callCount = $callCount + $equipmentModelStats->serviceCallCount;
                $timeSum = $timeSum + $equipmentModelStats->tempoTotalAtendimento;
                $serviceCallCount = $serviceCallCount + $equipmentModelStats->serviceCallCount;
                $tempoTotalAtendimento = $tempoTotalAtendimento + $equipmentModelStats->tempoTotalAtendimento;
            }
        }
        $subTotal = array('', '', '', $callCount, UnixTime::ConvertToTime($timeSum / $callCount));
        $currentRow++;
        InsereLinhaPlanilha($currentRow, $startColumn, $subTotal, '80AAFFFF', 20, PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $callCount = 0;
        $timeSum = 0;
        $subTotal = array($technicianStats->name, '', '', $serviceCallCount, UnixTime::ConvertToTime($tempoTotalAtendimento / $serviceCallCount));
        $currentRow++;
        InsereLinhaPlanilha($currentRow, $startColumn, $subTotal, 'FFAA2040', 20, PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $activeSheet->mergeCells(chr($colNum + 0) . $currentRow . ':' . chr($colNum + 2) . $currentRow);
    }
}
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 $equipmentExpenseArray;
    $businessPartnerDAO = new BusinessPartnerDAO($dataConnector->sqlserverConnection);
    $businessPartnerDAO->showErrors = 1;
    // Define o titulo da tabela
    $currentRow = $startRow;
    $activeSheet = $objPhpExcel->getActiveSheet();
    $activeSheet->setCellValue($startColumn . $startRow, 'DESPESAS');
    $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('Cliente', 'Série', 'Modelo', 'Fabricante', 'Data', 'Descrição', 'Valor (R$)');
    $offset = 0;
    foreach ($headers as $header) {
        $activeSheet->getColumnDimension(chr($colNum + $offset))->setWidth(25);
        $offset++;
    }
    $activeSheet->getColumnDimension(chr($colNum + 1))->setWidth(50);
    $currentRow++;
    InsereLinhaPlanilha($currentRow, $startColumn, $headers, PHPExcel_Style_Color::COLOR_YELLOW, 30, PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    // Gera as linhas da tabela
    $previousCustomer = '';
    $currentCustomer = '';
    $currentValue = 0;
    $grandTotal = 0;
    foreach ($equipmentExpenseArray as $equipmentExpense) {
        $currentCustomer = $equipmentExpense->nomeCliente;
        if ($currentCustomer != $previousCustomer) {
            if (!empty($previousCustomer)) {
                $currentRow++;
                $subtotal = array('SUBTOTAL', '', '', '', '', '', number_format($currentValue, 2, ',', '.'));
                InsereLinhaPlanilha($currentRow, $startColumn, $subtotal, '80AAFFFF');
                $currentValue = 0;
            }
            $previousCustomer = $currentCustomer;
        }
        $currentRow++;
        $row = array();
        $row[0] = $equipmentExpense->nomeCliente;
        $row[1] = $equipmentExpense->serieEquipamento;
        $row[2] = $equipmentExpense->tagModelo;
        $row[3] = $equipmentExpense->fabricante;
        $row[4] = empty($equipmentExpense->dataDespesa) ? '' : $equipmentExpense->dataDespesa->format('d/m/Y');
        $row[5] = $equipmentExpense->descricaoDespesa;
        $row[6] = number_format($equipmentExpense->totalDespesa, 2, ',', '.');
        InsereLinhaPlanilha($currentRow, $startColumn, $row);
        $currentValue += $equipmentExpense->totalDespesa;
        $grandTotal += $equipmentExpense->totalDespesa;
    }
    $currentRow++;
    $subtotal = array('SUBTOTAL', '', '', '', '', '', number_format($currentValue, 2, ',', '.'));
    InsereLinhaPlanilha($currentRow, $startColumn, $subtotal, '80AAFFFF');
    $currentRow++;
    $total = array('TOTAL', '', '', '', '', '', number_format($grandTotal, 2, ',', '.'));
    InsereLinhaPlanilha($currentRow, $startColumn, $total, 'FFAA2040');
}
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);
            }
        }
    }
}