<?php
$dataProvider = $_SESSION['data_retiro_parcial']->getData();

$phpExcelPath = Yii::getPathOfAlias('ext.Excel.PHPExcel.Classes');
$phpExcelPath2 = Yii::getPathOfAlias('application.extensions.Excel.PHPExcel.Classes.PHPExcel');

spl_autoload_unregister(['YiiBase', 'autoload']);
include $phpExcelPath.DIRECTORY_SEPARATOR.'PHPExcel.php';
include $phpExcelPath2.DIRECTORY_SEPARATOR.'IOFactory.php';
spl_autoload_register(['YiiBase', 'autoload']);

$PHPExcel = new PHPExcel();

//Ancho de las columnas
foreach (range('A', 'U') as $columnID) {
    $PHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
}

//Primera columna
$PHPExcel->getActiveSheet()->SetCellValue('A1', 'LISTADO DE SOLICITUDES');
$PHPExcel->getActiveSheet()->SetCellValue('A2', 'Resultados: '.count($dataProvider));

//Estilo del header de las columnas
$PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$PHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$PHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$PHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('004413');

$PHPExcel->getActiveSheet()->getStyle('A3:U3')->getFont()->setBold(true);
$PHPExcel->getActiveSheet()->getStyle('A3:U3')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$PHPExcel->getActiveSheet()->getStyle('A3:U3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$PHPExcel->getActiveSheet()->getStyle('A3:U3')->getFill()->getStartColor()->setARGB('004413');

//Titulo de las columnas
$PHPExcel->getActiveSheet()->SetCellValue('A3', 'Nacionalidad');
$PHPExcel->getActiveSheet()->SetCellValue('B3', 'Cédula');
$PHPExcel->getActiveSheet()->SetCellValue('C3', 'Nombres y apellidos');
$PHPExcel->getActiveSheet()->SetCellValue('D3', 'Empresa');
$PHPExcel->getActiveSheet()->SetCellValue('E3', 'Estatus');
$PHPExcel->getActiveSheet()->SetCellValue('F3', 'Cuenta');
$PHPExcel->getActiveSheet()->SetCellValue('G3', 'Monto disponible');
$PHPExcel->getActiveSheet()->SetCellValue('M3', 'Monto solicitado');
$PHPExcel->getActiveSheet()->SetCellValue('H3', 'Monto aprobado');
$PHPExcel->getActiveSheet()->SetCellValue('I3', 'Comisión administrativa');
$PHPExcel->getActiveSheet()->SetCellValue('J3', 'Comisión bancaria');
$PHPExcel->getActiveSheet()->SetCellValue('K3', 'Deuda capital pendiente');
$PHPExcel->getActiveSheet()->SetCellValue('L3', 'Cuenta por cobrar capital');
$PHPExcel->getActiveSheet()->SetCellValue('M3', 'Cuenta por cobrar interés');
$PHPExcel->getActiveSheet()->SetCellValue('N3', 'Monto a depositar');
$PHPExcel->getActiveSheet()->SetCellValue('O3', 'Fecha de aprobación');
$PHPExcel->getActiveSheet()->SetCellValue('P3', 'Observaciones');
$PHPExcel->getActiveSheet()->SetCellValue('Q3', 'Nro. de solicitud');
$PHPExcel->getActiveSheet()->SetCellValue('R3', 'Banco origen');
$PHPExcel->getActiveSheet()->SetCellValue('S3', 'Cuenta origen');
$PHPExcel->getActiveSheet()->SetCellValue('T3', 'Referencia del pago');
$PHPExcel->getActiveSheet()->SetCellValue('U3', 'Fecha del pago');

// Datos impresos
// Para los casos donde hay muchos registros +6000
ini_set('max_execution_time', 300);
foreach ($dataProvider as $key => $value) {
    $PHPExcel->getActiveSheet()->SetCellValue('A'.($key + 4), $value->idAsociado->nacionalidad);
    $PHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
    $PHPExcel->getActiveSheet()->SetCellValue('B'.($key + 4), $value->idAsociado->cedula);
    $PHPExcel->getActiveSheet()->SetCellValue('C'.($key + 4), strtoupper($value->idAsociado->nombre.' '.$value->idAsociado->apellidos));
    $PHPExcel->getActiveSheet()->SetCellValue('D'.($key + 4), strtoupper($value->idAsociado->lugartrabajo0->descripcion));
    $PHPExcel->getActiveSheet()->SetCellValue('E'.($key + 4), strtoupper($value->nombre_estatus));

    $PHPExcel->getActiveSheet()->setCellValueExplicit('F'.($key + 4), $value->cuenta, PHPExcel_Cell_DataType::TYPE_STRING);
    $PHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

    $PHPExcel->getActiveSheet()->getStyle('G'.($key + 4))->getNumberFormat()->setFormatCode('#,##0.00');
    $PHPExcel->getActiveSheet()->SetCellValue('G'.($key + 4), $value->monto_total_disponible);

    $PHPExcel->getActiveSheet()->getStyle('M')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
    $PHPExcel->getActiveSheet()->getStyle('M'.($key + 4))->getNumberFormat()->setFormatCode('#,##0.00');
    $PHPExcel->getActiveSheet()->SetCellValue('M'.($key + 4), $value->monto_solicitado);

    $PHPExcel->getActiveSheet()->getStyle('H')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
    $PHPExcel->getActiveSheet()->getStyle('H'.($key + 4))->getNumberFormat()->setFormatCode('#,##0.00');
    $PHPExcel->getActiveSheet()->SetCellValue('H'.($key + 4), $value->monto_aprobado);

    $PHPExcel->getActiveSheet()->getStyle('I')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
    $PHPExcel->getActiveSheet()->getStyle('I'.($key + 4))->getNumberFormat()->setFormatCode('#,##0.00');
    $PHPExcel->getActiveSheet()->SetCellValue('I'.($key + 4), $value->monto_gasto_administrativo);

    $PHPExcel->getActiveSheet()->getStyle('J')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
    $PHPExcel->getActiveSheet()->getStyle('J'.($key + 4))->getNumberFormat()->setFormatCode('#,##0.00');
    $PHPExcel->getActiveSheet()->SetCellValue('J'.($key + 4), $value->monto_comision_bancaria);

    $prestamoSeleccionados = json_decode($value->prestamos_pago, true);

    if ($prestamoSeleccionados == null) {
        $prestamoSeleccionados = [];
    }

    $PHPExcel->getActiveSheet()->getStyle('K')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
    $PHPExcel->getActiveSheet()->getStyle('K'.($key + 4))->getNumberFormat()->setFormatCode('#,##0.00');
    $PHPExcel->getActiveSheet()->SetCellValue('K'.($key + 4), (new Warp($prestamoSeleccionados))->sum('saldo_deudor_capital'));

    $PHPExcel->getActiveSheet()->getStyle('L')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
    $PHPExcel->getActiveSheet()->getStyle('L'.($key + 4))->getNumberFormat()->setFormatCode('#,##0.00');
    $PHPExcel->getActiveSheet()->SetCellValue('L'.($key + 4), (new Warp($prestamoSeleccionados))->sum('cuenta_x_cobrar_capital'));

    $PHPExcel->getActiveSheet()->getStyle('M')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
    $PHPExcel->getActiveSheet()->getStyle('M'.($key + 4))->getNumberFormat()->setFormatCode('#,##0.00');
    $PHPExcel->getActiveSheet()->SetCellValue('M'.($key + 4), (new Warp($prestamoSeleccionados))->sum('cuenta_x_cobrar_interes'));

    $PHPExcel->getActiveSheet()->getStyle('N')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
    $PHPExcel->getActiveSheet()->getStyle('N'.($key + 4))->getNumberFormat()->setFormatCode('#,##0.00');
    $PHPExcel->getActiveSheet()->SetCellValue('N'.($key + 4), $value->monto_pagar);

    $PHPExcel->getActiveSheet()->getStyle('O')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $PHPExcel->getActiveSheet()->SetCellValue('O'.($key + 4), ($value->fecha_solicita) ? date('d-m-Y', strtotime($value->fecha_solicita)) : '');

    $observaciones = EstatusRetiroParcial::model()->find('id_retiro_parcial=:id AND id_estatus_retiro IN(2, 3)', [':id' => $value->id]);
    $PHPExcel->getActiveSheet()->SetCellValue('P'.($key + 4), $observaciones
        ? $observaciones->observacion == '' 
            ? strtoupper('Sin observaciones') 
            : strtoupper($observaciones->observacion)
        : strtoupper('Sin observaciones'));

    $PHPExcel->getActiveSheet()->SetCellValue('Q'.($key + 4), $value->id);

    $info_pago = ProcesoPago::getDatosPago($value->id);

    $PHPExcel->getActiveSheet()->getStyle('R')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $PHPExcel->getActiveSheet()->SetCellValue('R'.($key + 4), $info_pago['banco']);

    $PHPExcel->getActiveSheet()->getStyle('S')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $PHPExcel->getActiveSheet()->SetCellValue('S'.($key + 4), $info_pago['cuenta']);

    $PHPExcel->getActiveSheet()->getStyle('T')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $PHPExcel->getActiveSheet()->SetCellValue('T'.($key + 4), $info_pago['referencia']);

    $PHPExcel->getActiveSheet()->getStyle('U')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $PHPExcel->getActiveSheet()->SetCellValue('U'.($key + 4), ($info_pago['fecha_pago'])
        ? date('d-m-Y', strtotime($info_pago['fecha_pago']))
        : '');
}

$objWriter = new PHPExcel_Writer_Excel2007($PHPExcel);
$objWriter->setOffice2003Compatibility(true);
$objWriter->save('php://output');