function GetSerialNumbers($mysqlConnection, $sqlserverConnection, $subContractId)
{
$serialNumbers = "";
$contractItemDAO = new ContractItemDAO($mysqlConnection);
$contractItemDAO->showErrors = 1;
$itemArray = $contractItemDAO->RetrieveRecordArray("subContrato_id = " . $subContractId);
foreach ($itemArray as $contractItem) {
if (!empty($serialNumbers)) {
$serialNumbers = $serialNumbers . ", ";
}
$serialNumbers = $serialNumbers . EquipmentDAO::GetSerialNumber($sqlserverConnection, $contractItem->codigoCartaoEquipamento);
}
return $serialNumbers;
}
$subContractDAO->showErrors = 1;
$subContractArray = $subContractDAO->RetrieveRecordArray("contrato_id = " . $contractId);
$subContractEnumeration = "";
foreach ($subContractArray as $subContract) {
if (!empty($subContractEnumeration)) {
$subContractEnumeration = $subContractEnumeration . ", ";
}
$subContractEnumeration = $subContractEnumeration . $subContract->id;
}
if (empty($subContractEnumeration)) {
$subContractEnumeration = "0";
}
// evita o crash da query, quando a lista está vazia
$contractItemDAO = new ContractItemDAO($dataConnector->mysqlConnection);
$contractItemDAO->showErrors = 1;
$itemArray = $contractItemDAO->RetrieveRecordArray("subContrato_id IN (" . $subContractEnumeration . ")");
$equipmentEnumeration = "";
foreach ($itemArray as $contractItem) {
if (!empty($equipmentEnumeration)) {
$equipmentEnumeration = $equipmentEnumeration . ", ";
}
$equipmentEnumeration = $equipmentEnumeration . $contractItem->codigoCartaoEquipamento;
}
if (empty($equipmentEnumeration)) {
$equipmentEnumeration = "0";
}
// evita o crash da query, quando a lista está vazia
// localiza todos as despesas dos chamados no período
$serviceCallDAO = new ServiceCallDAO($dataConnector->mysqlConnection);
$serviceCallDAO->showErrors = 1;
$pediodFilter = "dataAbertura >= '" . $startDate . " 00:00' AND dataAbertura <= '" . $endDate . " 23:59' ";
$dataConnector->OpenConnection();
if ($dataConnector->mysqlConnection == null) {
echo 'Não foi possível se connectar ao bando de dados!';
exit;
}
// Cria os objetos de mapeamento objeto-relacional
$contractDAO = new ContractDAO($dataConnector->mysqlConnection);
$contractDAO->showErrors = 1;
$contractItemDAO = new ContractItemDAO($dataConnector->mysqlConnection);
$contractItemDAO->showErrors = 1;
$subContractDAO = new SubContractDAO($dataConnector->mysqlConnection);
$subContractDAO->showErrors = 1;
// Busca os contratos pertencentes ao parceiro de negócios
$contractArray = $contractDAO->RetrieveRecordArray("pn='" . $businessPartnerCode . "'");
// Busca os items de contrato pertencentes ao parceiro de negócios
$itemArray = $contractItemDAO->RetrieveRecordArray("businessPartnerCode = '" . $businessPartnerCode . "'");
echo '<option value="0" >Agrupar equips. do cliente(independente do contrato)</option>';
$contractIdArray = array();
foreach ($contractArray as $contract) {
array_push($contractIdArray, $contract->id);
}
foreach ($itemArray as $contractItem) {
$subContract = $subContractDAO->RetrieveRecord($contractItem->codigoSubContrato);
if (!in_array($subContract->codigoContrato, $contractIdArray)) {
array_push($contractIdArray, $subContract->codigoContrato);
}
}
foreach ($contractIdArray as $id) {
$contract = $contractDAO->RetrieveRecord($id);
$status = ContractDAO::GetStatusAsText($contract->status);
$attributes = "";
}
// contrato repetido, pula para o próximo registro
$numero = str_pad($contract->numero, 5, '0', STR_PAD_LEFT);
$clientName = BusinessPartnerDAO::GetClientName($dataConnector->sqlserverConnection, $contract->pn);
$salesPersonName = $salesPersonArray[$contract->vendedor];
$status = $contractDAO->GetStatusAsText($contract->status);
$parcela = $contract->parcelaAtual . '/' . $contract->quantidadeParcelas;
$global = $contract->global == 0 ? 'N' : 'S';
$details = $clientName . ' Vendedor ' . $salesPersonName;
$subContractArray = $subContractDAO->RetrieveRecordArray("contrato_id=" . $contract->id);
foreach ($subContractArray as $subContract) {
if (!empty($details)) {
$details = $details . '<br/>';
}
$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 . '<br/>';
}
$equipmentModel = "";
if (array_key_exists($equipment->model, $modelArray)) {
$equipmentModel = $modelArray[$equipment->model];
}
$details = $details . $equipmentModel . ' Série ' . $equipment->manufacturerSN . ' Data Instalação ' . $installationDate;
}
}
static function GetItemsByOwner($mysqlConnection, $subContractEnumeration)
{
$contractItemDAO = new ContractItemDAO($mysqlConnection);
$contractItemDAO->showErrors = 1;
$itemArray = $contractItemDAO->RetrieveRecordArray("subContrato_id IN (" . $subContractEnumeration . ")");
return $itemArray;
}
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 GetDivisor($mysqlConnection, $contractDTO, $subContractDTO)
{
// Cria o objeto de mapeamento objeto-relacional
$contractItemDAO = new ContractItemDAO($mysqlConnection);
$contractItemDAO->showErrors = 1;
if ($contractDTO->global) {
// Busca os itens(equipamentos) pertencentes ao subContrato
$itemArray = $contractItemDAO->RetrieveRecordArray("subContrato_id = " . $subContractDTO->id);
// Verifica a quantidade de equipamentos ativos ou emprestados
$itemCount = 0;
foreach ($itemArray as $contractItem) {
// if (array_key_exists($contractItem->codigoCartaoEquipamento, $this->statusEquipamentos))
$status = $this->statusEquipamentos[$contractItem->codigoCartaoEquipamento];
if ($status == 'A' || $status == 'L') {
$itemCount++;
}
}
return $itemCount;
}
return 1;
// divisor padrão, caso não seja contrato global
}
