<?php
$this->layout = false;

use CrmSpreadsheet\CrmSpreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

$motivos = Motivos::model()->findAll("MOT_TIPO IN (2,3,5,6)");
$listaMotivos = CHtml::listData($motivos, 'MOT_IDMOTIVO', 'MOT_IDMOTIVO');
$listaMotivos = implode(',', $listaMotivos);

$spreadsheet = new CrmSpreadsheet();
$spreadsheet->setDefaults();
$spreadsheet->getProperties()->setTitle("Relatório de Interessados");

$abaAnalitico = $spreadsheet->getActiveSheet();
$abaAnalitico->setTitle("Analítico");

// Header
    # A
        $abaAnalitico->setCellValue("A1", "Código");
        $abaAnalitico->mergeCells("A1:A2");
        $abaAnalitico->getColumnDimension("A")->setAutoSize(true);

    # B
        $abaAnalitico->setCellValue("B1", "Data Cadastro");
        $abaAnalitico->mergeCells("B1:B2");
        $abaAnalitico->getColumnDimension("B")->setAutoSize(true);

    # C
        $abaAnalitico->setCellValue("C1", "Tipo");
        $abaAnalitico->mergeCells("C1:C2");
        $abaAnalitico->getColumnDimension("C")->setAutoSize(true);

    # D
        $abaAnalitico->setCellValue("D1", "Status atual");
        $abaAnalitico->mergeCells("D1:D2");
        $abaAnalitico->getColumnDimension("D")->setAutoSize(true);

    # E
        $abaAnalitico->setCellValue("E1", "Unidade de Negócios");
        $abaAnalitico->mergeCells("E1:E2");
        $abaAnalitico->getColumnDimension("E")->setAutoSize(true);

    # F
        $abaAnalitico->setCellValue("F1", "Cidade");
        $abaAnalitico->mergeCells("F1:F2");
        $abaAnalitico->getColumnDimension("F")->setAutoSize(true);

    # G
        $abaAnalitico->setCellValue("G1", "Observação");
        $abaAnalitico->mergeCells("G1:G2");
        $abaAnalitico->getColumnDimension("G")->setWidth(80);
        $abaAnalitico
                     ->getStyle("G")
                     ->getAlignment()
                     ->setWrapText(true)
                     ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);

    $range1 = "A1:G2";
    $styleArray = [
        'fill' => [
            'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
            'rotation' => 90,
            'startColor' => [
                'rgb' => 'BBDBEE',
            ],
        ],
    ];
    $abaAnalitico->getStyle($range1)->applyFromArray($styleArray);
    ###########################
    $abaAnalitico->setCellValue("H1", "Última atividade");
    $abaAnalitico->mergeCells("H1:L1");
    $abaAnalitico
                ->getStyle("H")
                ->getAlignment()
                ->setWrapText(true)
                ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
    # H
        $abaAnalitico->setCellValue("H2", "Código");
        $abaAnalitico->getColumnDimension("H")->setAutoSize(true);

    # I
        $abaAnalitico->setCellValue("I2", "Data");
        $abaAnalitico->getColumnDimension("I")->setAutoSize(true);

    # J
        $abaAnalitico->setCellValue("J2", "Funcionário");
        $abaAnalitico->getColumnDimension("J")->setAutoSize(true);

    # K
        $abaAnalitico->setCellValue("K2", "Motivo");
        $abaAnalitico->getColumnDimension("K")->setAutoSize(true);

    # L
        $abaAnalitico->setCellValue("L2", "Descrição");
        $abaAnalitico->getColumnDimension("L")->setWidth(60);
        $abaAnalitico
                    ->getStyle("L")
                    ->getAlignment()
                    ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
    $range2 = "H1:L2";
    $styleArray = [
        'fill' => [
            'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
            'rotation' => 90,
            'startColor' => [
                'rgb' => 'D0D0D0',
            ],
        ],
    ];
    $abaAnalitico->getStyle($range2)->applyFromArray($styleArray);
    ###########################
    $abaAnalitico->setCellValue("M1", "Penúltima atividade");
    $abaAnalitico->mergeCells("M1:Q1");
    $abaAnalitico
                ->getStyle("M")
                ->getAlignment()
                ->setWrapText(true)
                ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
    # M
        $abaAnalitico->setCellValue("M2", "Código");
        $abaAnalitico->getColumnDimension("M")->setAutoSize(true);

    # N
        $abaAnalitico->setCellValue("N2", "Data");
        $abaAnalitico->getColumnDimension("N")->setAutoSize(true);

    # O
        $abaAnalitico->setCellValue("O2", "Funcionário");
        $abaAnalitico->getColumnDimension("O")->setAutoSize(true);

    # P
        $abaAnalitico->setCellValue("P2", "Motivo");
        $abaAnalitico->getColumnDimension("P")->setAutoSize(true);

    # Q
        $abaAnalitico->setCellValue("Q2", "Descrição");
        $abaAnalitico->getColumnDimension("Q")->setWidth(60);
        $abaAnalitico
                    ->getStyle("Q")
                    ->getAlignment()
                    ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
    $range3 = "M1:Q2";
    $styleArray = [
        'fill' => [
            'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
            'rotation' => 90,
            'startColor' => [
                'rgb' => 'BBDBEE',
            ],
        ],
    ];
    $abaAnalitico->getStyle($range3)->applyFromArray($styleArray);
    ###########################
    $abaAnalitico->setCellValue("R1", "Antepenúltima atividade");
    $abaAnalitico->mergeCells("R1:V1");
    $abaAnalitico
                ->getStyle("R")
                ->getAlignment()
                ->setWrapText(true)
                ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
    # R
        $abaAnalitico->setCellValue("R2", "Código");
        $abaAnalitico->getColumnDimension("R")->setAutoSize(true);

    # S
        $abaAnalitico->setCellValue("S2", "Data");
        $abaAnalitico->getColumnDimension("S")->setAutoSize(true);

    # T
        $abaAnalitico->setCellValue("T2", "Funcionário");
        $abaAnalitico->getColumnDimension("T")->setAutoSize(true);

    # U
        $abaAnalitico->setCellValue("U2", "Motivo");
        $abaAnalitico->getColumnDimension("U")->setAutoSize(true);

    # V
        $abaAnalitico->setCellValue("V2", "Descrição");
        $abaAnalitico->getColumnDimension("V")->setWidth(60);
        $abaAnalitico
                    ->getStyle("V")
                    ->getAlignment()
                    ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
    $range4 = "R1:V2";
    $styleArray = [
        'fill' => [
            'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
            'rotation' => 90,
            'startColor' => [
                'rgb' => 'D0D0D0',
            ],
        ],
    ];
    $abaAnalitico->getStyle($range4)->applyFromArray($styleArray);
    ###########################
    $abaAnalitico->setCellValue("W1", "Próxima atividade");
    $abaAnalitico->mergeCells("W1:AA1");
    $abaAnalitico
                ->getStyle("W")
                ->getAlignment()
                ->setWrapText(true)
                ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
    # W
        $abaAnalitico->setCellValue("W2", "Código");
        $abaAnalitico->getColumnDimension("W")->setAutoSize(true);

    # X
        $abaAnalitico->setCellValue("X2", "Data");
        $abaAnalitico->getColumnDimension("X")->setAutoSize(true);

    # Y
        $abaAnalitico->setCellValue("Y2", "Funcionário");
        $abaAnalitico->getColumnDimension("Y")->setAutoSize(true);

    # Z
        $abaAnalitico->setCellValue("Z2", "Motivo");
        $abaAnalitico->getColumnDimension("Z")->setAutoSize(true);

    # AA
        $abaAnalitico->setCellValue("AA2", "Descrição");
        $abaAnalitico->getColumnDimension("AA")->setWidth(60);
        $abaAnalitico
                    ->getStyle("AA")
                    ->getAlignment()
                    ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
    $range5 = "W1:AA2";
    $styleArray = [
        'fill' => [
            'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
            'rotation' => 90,
            'startColor' => [
                'rgb' => 'BBDBEE',
            ],
        ],
    ];
    $abaAnalitico->getStyle($range5)->applyFromArray($styleArray);
    ###########################
    $abaAnalitico->setCellValue("AB1", "Auditoria");
    $abaAnalitico->mergeCells("AB1:AE1");
    $abaAnalitico
                ->getStyle("AB")
                ->getAlignment()
                ->setWrapText(true)
                ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
    # AB
        $abaAnalitico->setCellValue("AB2", "Funcionário");
        $abaAnalitico->getColumnDimension("AB")->setAutoSize(true);

    # AC
        $abaAnalitico->setCellValue("AC2", "Data");
        $abaAnalitico->getColumnDimension("AC")->setAutoSize(true);

    # AD
        $abaAnalitico->setCellValue("AD2", "Descrição");
        $abaAnalitico->getColumnDimension("AD")->setWidth(60);
        $abaAnalitico
                    ->getStyle("AD")
                    ->getAlignment()
                    ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);

    # AE
        $abaAnalitico->setCellValue("AE2", "Conceito");
        $abaAnalitico->getColumnDimension("AE")->setWidth(60);
        $abaAnalitico
                    ->getStyle("AE")
                    ->getAlignment()
                    ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
    $range5 = "AB1:AE2";
    $styleArray = [
        'fill' => [
            'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
            'rotation' => 90,
            'startColor' => [
                'rgb' => 'D0D0D0',
            ],
        ],
    ];
    $abaAnalitico->getStyle($range5)->applyFromArray($styleArray);
    ###########################

    $rangeFull = "A1:AE2";
    $abaAnalitico->setAutoFilter("A2:AE2");
    $styleArray = [
        'font' => [
            'bold' => true,
        ],
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
        ],
        'borders' => [
            'allBorders' => [
                'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                'color' => ['rgb' => '000000'],
            ],
        ],
    ];
    $abaAnalitico->getStyle($rangeFull)->applyFromArray($styleArray);
    $abaAnalitico->freezePane('A3');

function renderAtividade(&$abaAnalitico, &$coluna, $linha, $sql, $order = false, $offset = false)
{
    $options = array(
        'condition' => $sql
        );
    if($order) {
        $options['order'] = $order;
    }
    if($offset) {
        $options['offset'] = $offset;
    }
    $atividade = Atendimentos::model()->find($options);
    if ($atividade) {
        $valor = ExcelUtil::parseValue($atividade->ATD_IDATENDIMENTO);
        $abaAnalitico->setCellValueByColumnAndRow($coluna, $linha, $valor);
        $coluna++;
        ###
        $valor = ExcelUtil::parseValue($atividade->ATD_DTCADASTRO);
        if ($valor != "-") {
            $valor = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($valor);
        }
        $abaAnalitico->setCellValueByColumnAndRow($coluna, $linha, $valor);
        $abaAnalitico
                    ->getStyleByColumnAndRow($coluna, $linha)
                    ->getNumberFormat()
                    ->setFormatCode('dd/mm/yyyy');
        $coluna++;
        ###
        $valor = ExcelUtil::parseValue($atividade->Atendente);
        $abaAnalitico->setCellValueByColumnAndRow($coluna, $linha, $valor);
        $coluna++;
        ###
        $valor = ExcelUtil::parseValue($atividade->Motivo);
        $abaAnalitico->setCellValueByColumnAndRow($coluna, $linha, $valor);
        $coluna++;
        ###
        $valor = ExcelUtil::parseValue($atividade->ATD_OBS);
        if ($valor != "-") {
            $valor = strip_tags($valor);
            $busca = strpos($valor, "=");
            if ($busca === 0) {
                echo $valor;
                var_dump($linha);
                echo "<hr>";
            }
        }
        $abaAnalitico->setCellValueByColumnAndRow($coluna, $linha, $valor);
        $coluna++;
    } else {
        $abaAnalitico->setCellValueByColumnAndRow($coluna, $linha, "-");
        $coluna++;

        $abaAnalitico->setCellValueByColumnAndRow($coluna, $linha, "-");
        $coluna++;

        $abaAnalitico->setCellValueByColumnAndRow($coluna, $linha, "-");
        $coluna++;

        $abaAnalitico->setCellValueByColumnAndRow($coluna, $linha, "-");
        $coluna++;

        $abaAnalitico->setCellValueByColumnAndRow($coluna, $linha, "-");
        $coluna++;
    }
}

$linha = 3;
foreach ($data as $key => $row) {
    $tipo = ExcelUtil::parseValue($row->getTipo());
    $situacao = ExcelUtil::parseValue($row->getSituacao());
    $revenda = ExcelUtil::parseValue($row->Revenda);
    $cidade = ExcelUtil::parseValue($row->Cidade->CidadeEstado);

    $coluna = 1;

    # A
        $valor = $row->INT_CODIGO;
        $abaAnalitico->setCellValueByColumnAndRow($coluna, $linha, $valor);
        $coluna++;

    # B
        $valor = ExcelUtil::parseValue($row->INT_DATA);
        if ($valor != "-") {
            $valor = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($valor);
        }
        $abaAnalitico->setCellValueByColumnAndRow($coluna, $linha, $valor);
        $abaAnalitico
            ->getStyleByColumnAndRow($coluna, $linha)
            ->getNumberFormat()
            ->setFormatCode('dd/mm/yyyy');
        $coluna++;

    # C
        $valor = $tipo;
        $abaAnalitico->setCellValueByColumnAndRow($coluna, $linha, $valor);
        $coluna++;

    # D
        $valor = $situacao;
        $abaAnalitico->setCellValueByColumnAndRow($coluna, $linha, $valor);
        $coluna++;

    # E
        $valor = $revenda;
        $abaAnalitico->setCellValueByColumnAndRow($coluna, $linha, $valor);
        $coluna++;

    # F
        $valor = $cidade;
        $abaAnalitico->setCellValueByColumnAndRow($coluna, $linha, $valor);
        $coluna++;

    # G
        $valor = ExcelUtil::parseValue($row->INT_OBSERVACOES);
        $abaAnalitico->setCellValueByColumnAndRow($coluna, $linha, $valor);
        $coluna++;

    # Atividades
        renderAtividade($abaAnalitico, $coluna, $linha, "ATD_IDCLIENTE = '{$row->INT_IDPROSPECTO}' and ATD_IDMOTIVO IN ({$listaMotivos}) and ATD_DTCADASTRO <= now() and ATD_SITUACAO = 'R'", "ATD_IDATENDIMENTO DESC");
        renderAtividade($abaAnalitico, $coluna, $linha, "ATD_IDCLIENTE = '{$row->INT_IDPROSPECTO}' AND ATD_IDMOTIVO IN ({$listaMotivos}) and ATD_DTCADASTRO <= now() and ATD_SITUACAO = 'R'", "ATD_IDATENDIMENTO DESC", 1);
        renderAtividade($abaAnalitico, $coluna, $linha, "ATD_IDCLIENTE = '{$row->INT_IDPROSPECTO}' AND ATD_IDMOTIVO IN ({$listaMotivos}) and ATD_DTCADASTRO <= now() and ATD_SITUACAO = 'R'", "ATD_IDATENDIMENTO DESC", 2);
        renderAtividade($abaAnalitico, $coluna, $linha, "ATD_IDCLIENTE = '{$row->INT_IDPROSPECTO}' AND ATD_IDMOTIVO IN ({$listaMotivos}) AND ATD_SITUACAO = 'A' and ATD_DTCADASTRO >= now()", "ATD_IDATENDIMENTO ASC");

    $linha++;
}
$title = "Filtros Aplicados";

$sheet = $spreadsheet->createSheet();
$sheet->setTitle($title);

$spreadsheet->setActiveSheetIndexByName($title);
$aba = $spreadsheet->getActiveSheet();

$header = array(
    array(
        'name' => 'Filtro',
        'width' => 'auto',
        'wrap' => false
    ),
    array(
        'name' => 'Valor Informado',
        'width' => 'auto',
        'wrap' => false
    ),
);
$spreadsheet->createHeader($header);

$linha = 2;
if (!empty($model->INT_CODIGO)) {
    $aba->setCellValueByColumnAndRow(1, $linha, "Código");

    $valor = ExcelUtil::parseValue($model->INT_CODIGO);
    $aba->setCellValueByColumnAndRow(2, $linha, $valor);

    $linha++;
}
if (!empty($model->INT_EMPRESA)) {
    $aba->setCellValueByColumnAndRow(1, $linha, "Razão social");

    $valor = ExcelUtil::parseValue($model->INT_EMPRESA);
    $aba->setCellValueByColumnAndRow(2, $linha, $valor);

    $linha++;
}
if (!empty($model->INT_IDREVENDA)) {
    $aba->setCellValueByColumnAndRow(1, $linha, "Unidade de Negócios");

    $revenda = Revendas::model()->findByPk($model->INT_IDREVENDA);
    $valor = ExcelUtil::parseValue($revenda);
    $aba->setCellValueByColumnAndRow(2, $linha, $valor);

    $linha++;
}
if (!empty($model->INT_IDVENDEDOR)) {
    $aba->setCellValueByColumnAndRow(1, $linha, "Vendedor");

    $funcionario = Funcionarios::model()->findByPk($model->INT_IDVENDEDOR);
    $valor = ExcelUtil::parseValue($funcionario);
    $aba->setCellValueByColumnAndRow(2, $linha, $valor);

    $linha++;
}
if (!empty($model->INT_CIDADE)) {
    $aba->setCellValueByColumnAndRow(1, $linha, "Cidade");

    $cidade = Cidades::model()->findByPk($model->INT_CIDADE);
    $valor = ExcelUtil::parseValue($cidade->CidadeEstado);
    $aba->setCellValueByColumnAndRow(2, $linha, $valor);

    $linha++;
}
if (!empty($model->INT_TIPO)) {
    $aba->setCellValueByColumnAndRow(1, $linha, "Tipo");

    $valor = ExcelUtil::parseValue($model->getTipo());
    $aba->setCellValueByColumnAndRow(2, $linha, $valor);

    $linha++;
}
if (!empty($model->INT_SITUACAO)) {
    $aba->setCellValueByColumnAndRow(1, $linha, "Status");

    $valor = ExcelUtil::parseValue($model->getSituacao());
    $aba->setCellValueByColumnAndRow(2, $linha, $valor);

    $linha++;
}
if (!empty($model->data_inicial)) {
    $aba->setCellValueByColumnAndRow(1, $linha, "Data Inicial");

    $valor = ExcelUtil::parseValue($model->data_inicial);
    if ($valor != "-") {
        $valor = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($valor);
    }
    $aba->setCellValueByColumnAndRow(2, $linha, $valor);
    $aba
        ->getStyleByColumnAndRow(2, $linha)
        ->getNumberFormat()
        ->setFormatCode('dd/mm/yyyy');

    $linha++;
}
if (!empty($model->data_final)) {
    $aba->setCellValueByColumnAndRow(1, $linha, "Data Final");

    $valor = ExcelUtil::parseValue($model->data_final);
    if ($valor != "-") {
        $valor = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($valor);
    }
    $aba->setCellValueByColumnAndRow(2, $linha, $valor);
    $aba
        ->getStyleByColumnAndRow(2, $linha)
        ->getNumberFormat()
        ->setFormatCode('dd/mm/yyyy');

    $linha++;
}
if (!empty($model->INT_TELEFONE1)) {
    $aba->setCellValueByColumnAndRow(1, $linha, "Telefone");

    $valor = ExcelUtil::parseValue($model->INT_TELEFONE1);
    $aba->setCellValueByColumnAndRow(2, $linha, $valor);

    $linha++;
}
if (!empty($model->INT_NOME)) {
    $aba->setCellValueByColumnAndRow(1, $linha, "Contato");

    $valor = ExcelUtil::parseValue($model->INT_NOME);
    $aba->setCellValueByColumnAndRow(2, $linha, $valor);

    $linha++;
}
if (!empty($model->INT_MOTIVOS)) {
    $aba->setCellValueByColumnAndRow(1, $linha, "Como conheceu");

    $motivo = Motivos::model()->findByPk($model->INT_MOTIVOS);
    $valor = ExcelUtil::parseValue($motivo);
    $aba->setCellValueByColumnAndRow(2, $linha, $valor);

    $linha++;
}

$aba->setCellValueByColumnAndRow(1, $linha, "Somente pendentes");
if (!empty($model->INT_MOTIVOS)) {
    $valor = "Sim";
} else {
    $valor = "Não";
}
$aba->setCellValueByColumnAndRow(2, $linha, $valor);

$linha++;

$spreadsheet->setActiveSheetIndex(0);

$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
