<?php
$this->layout = false;

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

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

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

$header = array(
    array(
        'name' => 'Título',
        'width' => 'auto',
        'wrap' => false,
    ),
    array(
        'name' => 'Série',
        'width' => 'auto',
        'wrap' => false,
    ),
    array(
        'name' => 'Cliente',
        'width' => 'auto',
        'wrap' => false,
    ),
    array(
        'name' => 'Emissão',
        'width' => 'auto',
        'wrap' => false,
    ),
    array(
        'name' => 'Vencimento',
        'width' => 'auto',
        'wrap' => false,
    ),
    array(
        'name' => 'Pagamento',
        'width' => 'auto',
        'wrap' => false,
    ),
    array(
        'name' => 'Mensal',
        'width' => 'auto',
        'wrap' => false,
    ),
    array(
        'name' => 'Juros',
        'width' => 'auto',
        'wrap' => false,
    ),
    array(
        'name' => 'Total',
        'width' => 'auto',
        'wrap' => false,
    ),
);
$spreadsheet->createHeader($header, true);

$revendas = array();
$linha = 2;
foreach ($data as $row) {

    $revendaResponsavel = $row->Cliente->getRevendaResponsavel2();
    $revenda = ExcelUtil::parseValue($revendaResponsavel);

    if (!in_array($revenda, $revendas)) {
        $id = "";
        if ($revendaResponsavel) {
            $id .= $revendaResponsavel->REV_TIPO;
        }
        $id .= $revenda;
        $id = ExcelUtil::parseId($id);

        $revendas[$revendaResponsavel->REV_IDREVENDA] = $revenda;
    }

    $coluna = 1;
    # A
        $valor = ExcelUtil::parseValue($row->TIT_TITULO);
        $abaAnalitico->setCellValueByColumnAndRow($coluna, $linha, $valor);
        $coluna++;

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

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

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

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

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

    # G
        $valor = ExcelUtil::parseValue($row->TIT_VALOR);
        $abaAnalitico->setCellValueByColumnAndRow($coluna, $linha, $valor);
        $abaAnalitico
                    ->getStyleByColumnAndRow($coluna, $linha)
                    ->getNumberFormat()
                    ->setFormatCode('"R$ "#,##0.00_-');
        $coluna++;

    # H
        $valor = ExcelUtil::parseValue($row->TIT_VALORPAGO);
        $abaAnalitico->setCellValueByColumnAndRow($coluna, $linha, $valor);
        $abaAnalitico
                    ->getStyleByColumnAndRow($coluna, $linha)
                    ->getNumberFormat()
                    ->setFormatCode('"R$ "#,##0.00_-');
        $coluna++;

    # I
        $valor = ExcelUtil::parseValue($row->TIT_VALOR_TOTAL);
        $abaAnalitico->setCellValueByColumnAndRow($coluna, $linha, $valor);
        $abaAnalitico
                    ->getStyleByColumnAndRow($coluna, $linha)
                    ->getNumberFormat()
                    ->setFormatCode('"R$ "#,##0.00_-');
        $coluna++;

    $linha++;
}

if (!empty($revendas)) {
    ksort($revendas);

    $title = "Valores Totais";

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

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

    $header = array(
        array(
            'name' => 'Unidade de Negócio',
            'width' => 'auto',
            'wrap' => false
        ),
        array(
            'name' => 'Total',
            'width' => 'auto',
            'wrap' => false
        ),
        array(
            'name' => 'Comissão',
            'width' => 'auto',
            'wrap' => false
        ),
    );
    $spreadsheet->createHeader($header);

    $linha = 2;
    $totalFinal = 0;
    $totalComissao = 0;
    foreach ($revendas as $key => $revenda) {

        # A
            $aba->setCellValueByColumnAndRow(1, $linha, $revenda);

        # B
            $aba->setCellValueByColumnAndRow(2, $linha, $total[$key]);
            $aba
                ->getStyleByColumnAndRow(2, $linha)
                ->getNumberFormat()
                ->setFormatCode('"R$ "#,##0.00_-');

        # C
            $aba->setCellValueByColumnAndRow(3, $linha, $comissao[$key]);
            $aba
                ->getStyleByColumnAndRow(3, $linha)
                ->getNumberFormat()
                ->setFormatCode('"R$ "#,##0.00_-');

        $totalFinal += $total[$key];
        $totalComissao += $comissao[$key];

        $linha++;

        if (!empty($model->TIT_REVENDA)) {

            $revendaTb = Revendas::model()->findByPk($model->TIT_REVENDA);
            $valor = ExcelUtil::parseValue($revendaTb);

            if($valor == $revenda){
                break;
            } else {
                continue;
            }
        }
    }

    $aba->setCellValueByColumnAndRow(1, $linha+2, "TOTAL");
    $aba->setCellValueByColumnAndRow(2, $linha+2,$totalFinal);
    $aba->getStyleByColumnAndRow(2,  $linha+2)->getNumberFormat()->setFormatCode('"R$ "#,##0.00_-');

    $aba->setCellValueByColumnAndRow(3, $linha+2,$totalComissao);
    $aba->getStyleByColumnAndRow(3,  $linha+2)->getNumberFormat()->setFormatCode('"R$ "#,##0.00_-');

    ##################################################
    $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->TIT_REVENDA)) {
        $aba->setCellValueByColumnAndRow(1, $linha, "Unidade de Negócios");

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

        $linha++;
    }

    if (!empty($model->TIT_DTPAGAMENTO)) {
        $aba->setCellValueByColumnAndRow(1, $linha, "Data Pagamento");

        $valor = ExcelUtil::parseValue($model->TIT_DTPAGAMENTO);
        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->TIT_DTPAGAMENTO_FINAL)) {
        $aba->setCellValueByColumnAndRow(1, $linha, "Data Pagamento Final");

        $valor = ExcelUtil::parseValue($model->TIT_DTPAGAMENTO_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++;
    }
}

$spreadsheet->setActiveSheetIndex(0);

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