<?php

class ReporteDetalladoAsociado
{
    protected $asociado;
    protected $excel;
    protected $fila = 0;

    public function __construct($asociado)
    {
        $this->asociado = $asociado;
        $this->excel = new PHPExcel();
    }

    public function make()
    {
        foreach (range('A', 'Z') as $columna) {
            $this->excel->getActiveSheet()->getColumnDimension($columna)->setAutoSize(true);
        }

        return $this->aportesOrdinarios()
            ->aportesExtraOrdinarios()
            ->aportesExtraOrdinariosMasivos()
            ->retirosParciales()
            ->dividendos()
            ->ajusteHaberes()
            ->export();
    }

    protected function aportesOrdinarios()
    {
        $this->excel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, 'Aportes ordinarios');
        $this->excel->getActiveSheet()->mergeCells('A1:I1');
        $this->excel->getActiveSheet()->getStyle('A1:I1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $this->addEncabezado([
            'Fecha aporte',
            'Sueldo base',
            'Aporte patrono',
            'Aporte asociado',
            'Aporte total',
            'Aporte pagado',
            'Aporte pendiente',
            'Estatus aporte',
            'Observación',
        ], 2);

        $this->fila = 3;
        $aportes = $this->getAO();
        $cantidadAportes = count($aportes);
        foreach ($aportes as $aporte) {
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(0, $this->fila, $aporte['fecha_aporte']);
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(1, $this->fila, $aporte['sueldo_base']);
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(1, $this->fila)->getNumberFormat()->setFormatCode('#,##0.00');
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(1, $this->fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(2, $this->fila, $aporte['aporte_patrono']);
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(2, $this->fila)->getNumberFormat()->setFormatCode('#,##0.00');
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(2, $this->fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(3, $this->fila, $aporte['aporte_asociado']);
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(3, $this->fila)->getNumberFormat()->setFormatCode('#,##0.00');
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(3, $this->fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(4, $this->fila, $aporte['total_aporte']);
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(4, $this->fila)->getNumberFormat()->setFormatCode('#,##0.00');
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(4, $this->fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(5, $this->fila, $aporte['aporte_pagado']);
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(5, $this->fila)->getNumberFormat()->setFormatCode('#,##0.00');
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(5, $this->fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(6, $this->fila, $aporte['aporte_x_pagar']);
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(6, $this->fila)->getNumberFormat()->setFormatCode('#,##0.00');
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(6, $this->fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(7, $this->fila, $aporte['nombre_estatus']);
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(8, $this->fila, $aporte['observaciones']);
            $this->fila++;
        }

        // Totales
        $this->excel->getActiveSheet()->setCellValueByColumnAndRow(0, $this->fila, 'Total');
        $this->excel->getActiveSheet()->getStyleByColumnAndRow(0, $this->fila)->getFont()->setBold(true);
        $primeraFila = 3;
        $ultimaFila = $this->fila - 1;

        $this->addTotal(['B', 'C', 'D', 'E', 'F', 'G'], $primeraFila, $ultimaFila);

        return $this;
    }

    protected function aportesExtraOrdinarios()
    {
        $columna = 0;
        $this->fila += 2;
        $this->excel->getActiveSheet()->setCellValueByColumnAndRow(0, $this->fila, 'Aportes extraordinario');
        $this->excel->getActiveSheet()->mergeCells("A{$this->fila}:G{$this->fila}");
        $this->excel->getActiveSheet()->getStyle("A{$this->fila}:G{$this->fila}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->fila++;
        $this->excel->getActiveSheet()->setCellValueByColumnAndRow(0, $this->fila, 'Aportes carga individual');
        $this->excel->getActiveSheet()->mergeCells("A{$this->fila}:G{$this->fila}");
        $this->excel->getActiveSheet()->getStyle("A{$this->fila}:G{$this->fila}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->fila++;
        $filaInicial = $this->fila;

        $this->addEncabezado([
            'Código',
            'Monto Aporte',
            'Aportante',
            'Fecha referencia pago',
            'Tipo operación',
            'Estatus',
            'Fecha estatus',
        ], $this->fila);

        $this->fila++;
        $aportes = $this->getIndividualAE();
        $cantidadAportes = count($aportes);
        foreach ($aportes as $aporte) {
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(0, $this->fila, $aporte['codigo']);
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(1, $this->fila, $aporte['monto']);
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(1, $this->fila)->getNumberFormat()->setFormatCode('#,##0.00');
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(1, $this->fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(2, $this->fila, $aporte['aportante']);
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(3, $this->fila, $aporte['fecha_pago']);
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(4, $this->fila, $aporte['tipo_operacion']);
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(5, $this->fila, $aporte['nombre_estatus']);
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(6, $this->fila, $aporte['fecha_registro']);
            $this->fila++;
        }

        $this->excel->getActiveSheet()->setCellValueByColumnAndRow(0, $this->fila, 'Total');
        $this->excel->getActiveSheet()->getStyleByColumnAndRow(0, $this->fila)->getFont()->setBold(true);
        $primeraFila = $filaInicial + 1;
        $ultimaFila = $this->fila - 1;
        if ($cantidadAportes > 0) {
            $this->addTotal(['B'], $primeraFila, $ultimaFila);
        }

        return $this;
    }

    protected function aportesExtraOrdinariosMasivos()
    {
        $this->fila++;
        $this->excel->getActiveSheet()->setCellValueByColumnAndRow(0, $this->fila, 'Aportes carga masiva');
        $this->excel->getActiveSheet()->mergeCells("A{$this->fila}:L{$this->fila}");
        $this->excel->getActiveSheet()->getStyle("A{$this->fila}:L{$this->fila}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->fila++;
        $filaInicial = $this->fila;

        $this->addEncabezado([
            'Cédula',
            'Nombre',
            'Apellido',
            'Aportante',
            'Tipo operación',
            'Monto cargado',
            'Monto retirado',
            'Comisión retira',
            'Monto capitalizado',
            'Comisión capitalización',
            'Fecha pago',
            'Estatus',
        ], $this->fila);

        $this->fila++;
        $aportes = $this->getMasivoAE();
        $cantidadAportes = count($aportes);
        foreach ($aportes as $aporte) {
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(0, $this->fila, $aporte['cedula']);
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(1, $this->fila, $aporte['nombre']);
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(2, $this->fila, $aporte['apellidos']);
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(3, $this->fila, $aporte['aportante']);
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(4, $this->fila, $aporte['opcion_retiro']);

            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(5, $this->fila, $aporte['monto_cargado']);
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(5, $this->fila)->getNumberFormat()->setFormatCode('#,##0.00');
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(5, $this->fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(6, $this->fila, $aporte['monto_retirado']);
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(6, $this->fila)->getNumberFormat()->setFormatCode('#,##0.00');
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(6, $this->fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(7, $this->fila, $aporte['monto_comision_retira']);
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(7, $this->fila)->getNumberFormat()->setFormatCode('#,##0.00');
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(7, $this->fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(8, $this->fila, $aporte['monto_capitalizado']);
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(8, $this->fila)->getNumberFormat()->setFormatCode('#,##0.00');
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(8, $this->fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(9, $this->fila, $aporte['monto_comision_capitaliza']);
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(9, $this->fila)->getNumberFormat()->setFormatCode('#,##0.00');
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(9, $this->fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(10, $this->fila, $aporte['fecha_pago']);
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(11, $this->fila, $aporte['nombre_estatus']);
            $this->fila++;
        }

        $this->excel->getActiveSheet()->setCellValueByColumnAndRow(0, $this->fila, 'Total');
        $this->excel->getActiveSheet()->getStyleByColumnAndRow(0, $this->fila)->getFont()->setBold(true);
        $primeraFila = $filaInicial + 1;
        $ultimaFila = $this->fila - 1;
        if ($cantidadAportes > 0) {
            $this->addTotal(['F', 'G', 'H', 'I', 'J'], $primeraFila, $ultimaFila);
        }

        return $this;
    }

    protected function retirosParciales()
    {
        $this->fila += 2;
        $this->excel->getActiveSheet()->setCellValueByColumnAndRow(0, $this->fila, 'Retiros parciales');
        $this->excel->getActiveSheet()->mergeCells("A{$this->fila}:E{$this->fila}");
        $this->excel->getActiveSheet()->getStyle("A{$this->fila}:E{$this->fila}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->fila++;
        $filaInicial = $this->fila;
        $this->addEncabezado(['Monto aportes', 'Monto solicitado', 'Monto aprobado', 'Fecha estatus', 'Estatus'], $this->fila);

        $this->fila++;
        $aportes = $this->getRetiroParcial();
        $cantidadAportes = count($aportes);
        foreach ($aportes as $aporte) {
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(0, $this->fila, $aporte['monto_total_disponible']);
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(0, $this->fila)->getNumberFormat()->setFormatCode('#,##0.00');
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(0, $this->fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(1, $this->fila, $aporte['monto_solicitado']);
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(1, $this->fila)->getNumberFormat()->setFormatCode('#,##0.00');
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(1, $this->fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(2, $this->fila, $aporte['monto_aprobado']);
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(2, $this->fila)->getNumberFormat()->setFormatCode('#,##0.00');
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(2, $this->fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(3, $this->fila, $aporte['fecha_registro']);
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(4, $this->fila, $aporte['nombre_estatus']);
            $this->fila++;
        }

        $primeraFila = $filaInicial + 1;
        $ultimaFila = $this->fila - 1;
        if ($cantidadAportes > 0) {
            $this->addTotal(['A', 'B', 'C'], $primeraFila, $ultimaFila);
        }

        return $this;
    }

    protected function dividendos()
    {
        $this->fila += 2;
        $this->excel->getActiveSheet()->setCellValueByColumnAndRow(0, $this->fila, 'Dividendos');
        $this->excel->getActiveSheet()->mergeCells("A{$this->fila}:D{$this->fila}");
        $this->excel->getActiveSheet()->getStyle("A{$this->fila}:D{$this->fila}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->fila++;
        $filaInicial = $this->fila;
        $this->addEncabezado(['Año', 'Dividendo', 'Fecha registro', 'Estatus'], $this->fila);

        $this->fila++;
        $aportes = $this->getDividendos();
        $cantidadAportes = count($aportes);
        foreach ($aportes as $aporte) {
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(0, $this->fila, $aporte['ano']);

            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(1, $this->fila, $aporte['dividendo']);
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(1, $this->fila)->getNumberFormat()->setFormatCode('#,##0.00');
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(1, $this->fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(3, $this->fila, $aporte['fecha_registro']);
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(4, $this->fila, $aporte['estatus']);
            $this->fila++;
        }

        $primeraFila = $filaInicial + 1;
        $ultimaFila = $this->fila - 1;
        if ($cantidadAportes > 0) {
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(0, $this->fila, 'Total');
            $this->addTotal(['B'], $primeraFila, $ultimaFila);
        }

        return $this;
    }

    protected function ajusteHaberes()
    {
        $this->fila += 2;
        $this->excel->getActiveSheet()->setCellValueByColumnAndRow(0, $this->fila, 'Ajuste de haberes');
        $this->excel->getActiveSheet()->mergeCells("A{$this->fila}:J{$this->fila}");
        $this->excel->getActiveSheet()->getStyle("A{$this->fila}:J{$this->fila}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->fila++;
        $filaInicial = $this->fila;
        $columna = 0;
        $this->addEncabezado(['Número de la operación', 'Fecha de la operación', 'Concepto del ajuste', 'Tipo de ajuste', 'Tipo de movimiento', 'Monto asociado', 'Monto patrono', 'Observación', 'Fecha estatus', 'Estatus'], $this->fila);

        $this->fila++;
        $aportes = $this->getAjusteHaberes();
        $cantidadAportes = count($aportes);
        foreach ($aportes as $aporte) {
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(0, $this->fila, $aporte['numero_operacion']);
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(1, $this->fila, $aporte['fecha_operacion']);
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(2, $this->fila, $aporte['concepto']);
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(3, $this->fila, $aporte['tipo_ajuste']);
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(4, $this->fila, $aporte['tipo_movimiento']);

            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(5, $this->fila, $aporte['monto_asociado']);
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(5, $this->fila)->getNumberFormat()->setFormatCode('#,##0.00');
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(5, $this->fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(6, $this->fila, $aporte['monto_patrono']);
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(6, $this->fila)->getNumberFormat()->setFormatCode('#,##0.00');
            $this->excel->getActiveSheet()->getStyleByColumnAndRow(6, $this->fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(7, $this->fila, $aporte['fecha_registro']);
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(8, $this->fila, $aporte['observacion']);
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(9, $this->fila, $aporte['estatus']);
            $this->fila++;
        }

        $primeraFila = $filaInicial + 1;
        $ultimaFila = $this->fila - 1;
        if ($cantidadAportes > 0) {
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow(0, $this->fila, 'Total');
            $this->addTotal(['F', 'G'], $primeraFila, $ultimaFila);
        }

        return $this;
    }

    protected function addEncabezado($titulos, $fila)
    {
        $columna = 0;
        foreach ($titulos as $titulo) {
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow($columna, $fila, $titulo);
            $this->excel->getActiveSheet()->getStyleByColumnAndRow($columna, $fila)->getFont()->setBold(true);
            $this->excel->getActiveSheet()->getStyleByColumnAndRow($columna, $fila)->getFill()->getStartColor()->setARGB('1C336E');
            $this->excel->getActiveSheet()->getStyleByColumnAndRow($columna, $fila)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $this->excel->getActiveSheet()->getStyleByColumnAndRow($columna, $fila)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
            $this->excel->getActiveSheet()->getStyleByColumnAndRow($columna, $fila)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $columna++;
        }
    }

    protected function addTotal($columnas, $primeraFila, $ultimaFila)
    {
        foreach ($columnas as $columna) {
            $this->excel->getActiveSheet()->setCellValue("{$columna}{$this->fila}", "=SUM({$columna}{$primeraFila}:{$columna}{$ultimaFila})");
            $this->excel->getActiveSheet()->getStyle("{$columna}{$this->fila}")->getNumberFormat()->setFormatCode('#,##0.00');
            $this->excel->getActiveSheet()->getStyle("{$columna}{$this->fila}")->getFont()->setBold(true);
        }
    }

    protected function export()
    {
        return $this->excel;
    }

    protected function getAO()
    {
        return Yii::app()->getDb()->createCommand('
            SELECT  t.fecha_aporte
                   ,coalesce(t.sueldo_base, 0) as sueldo_base
                   ,coalesce(t.aporte_patrono, 0) AS aporte_patrono
                   ,COALESCE(t.aporte_asociado, 0) AS aporte_asociado
                   ,(coalesce(t.aporte_patrono, 0) + COALESCE(t.aporte_asociado, 0)) as total_aporte
                   ,CASE coalesce(had.id, 0) > 1
                        when true then (COALESCE(t.aporte_asociado, 0) - COALESCE(had.deuda_actual_socio, 0)) + (coalesce(t.aporte_patrono, 0) - coalesce(had.deuda_actual_patrono, 0))
                        when false then coalesce(t.aporte_patrono, 0) + COALESCE(t.aporte_asociado, 0)
                    end as aporte_pagado
                   ,CASE coalesce(had.id, 0) > 1
                        when true then COALESCE(had.deuda_actual_socio, 0) + coalesce(had.deuda_actual_patrono, 0)
                        when false then 0
                    end as aporte_x_pagar
                   ,et.nombre_estatus
                   ,t.observaciones
            FROM retenciones.aporte_ordinario t
            left JOIN retenciones.estatus_txt_integrado as a on a.id_datos_txt_integrado=t.id_txt
            left join retenciones.historial_aportes_diferidos had on had.id_aporte=t.id
            left join retenciones.tipo_nomina tn on tn.id=t.id_tipo_nomina
            left join retenciones.estatus_txt_integrado eti on eti.id_datos_txt_integrado=t.id_txt
                and eti.actual IS TRUE
            left join retenciones.estatus_txt et on et.id=eti.id_estatus_txt
                and et.blnborrado is false
            WHERE id_asociado=:id
                AND aprobado is true
                AND a.actual is true
                AND a.id_estatus_txt in (3,5,6)
            order by t.fecha_aporte DESC
        ')->bindValues([
            'id' => $this->asociado,
        ])->queryAll();
    }

    protected function getIndividualAE()
    {
        return Yii::app()->getDb()->createCommand("
            SELECT 'Aporte individual' as codigo
                   ,t.monto
                   ,a.nombre as aportante
                   ,t.fecha_pago
                   ,tio.nombre as tipo_operacion
                   ,ea.nombre_estatus
                   ,eav.fecha_registro
            FROM retenciones.aporte_voluntario t
            left join retenciones.aportante a on a.id=t.id_aportante
            left join retenciones.tipo_operacion tio on tio.id=t.opcion_retiro
            left join retenciones.estatus_aporte_voluntario eav on eav.id_aporte_voluntario=t.id
                and eav.actual is true
            left join retenciones.estatus_aporte ea on ea.id=eav.id_status_av
            WHERE id_asociado=:id
                AND t.id_txt_integrado is null
        ")->bindValues([
            'id' => $this->asociado,
        ])->queryAll();
    }

    protected function getMasivoAE()
    {
        return Yii::app()->getDb()->createCommand('
            SELECT  a.cedula
                   ,a.nombre
                   ,a.apellidos
                   ,ap.nombre as aportante
                   ,tio.nombre as opcion_retiro
                   ,coalesce(t.monto_cargado, 0) as monto_cargado
                   ,coalesce(t.monto_retirado, 0) as monto_retirado
                   ,coalesce(t.monto_comision_retira, 0) as monto_comision_retira
                   ,coalesce(t.monto_capitalizado, 0) as monto_capitalizado
                   ,coalesce(t.monto_comision_capitaliza, 0) as monto_comision_capitaliza
                   ,t.fecha_pago
                   ,ea.nombre_estatus
            FROM retenciones.aporte_voluntario t
            LEFT JOIN asociado a ON t.id_asociado=a.idasociado
            left join retenciones.aportante ap on ap.id=t.id_aportante
            LEFT JOIN retenciones.tipo_operacion tio ON t.opcion_retiro=tio.id
            left join retenciones.estatus_aporte_voluntario eav on eav.id_aporte_voluntario=t.id
                and eav.actual is true
            left join retenciones.estatus_aporte ea on ea.id=eav.id_status_av
            WHERE t.blnborrado is false
                and t.id_asociado=:id
                and eav.id_status_av=3 -- pagado
        ')->bindValues([
            'id' => $this->asociado,
        ])->queryAll();
    }

    protected function getRetiroParcial()
    {
        return Yii::app()->getDb()->createCommand('
            SELECT  t.monto_total_disponible
                   ,t.monto_solicitado
                   ,t.monto_aprobado
                   ,erp.fecha_registro::date
                   ,er.nombre_estatus
            FROM retiro.retiro_parcial t
            left join retiro.estatus_retiro_parcial erp on erp.id_retiro_parcial=t.id
                 AND actual is TRUE
            left join retiro.estatus_retiro er on er.id=erp.id_estatus_retiro
            WHERE t.blnborrado is false
                AND t.idasociado=:id
        ')->bindValues([
            'id' => $this->asociado,
        ])->queryAll();
    }

    protected function getPrestamos()
    {
        $prestamos = Yii::app()->getDb()->createCommand('
            SELECT t.idcredito
                   ,t.nombre_titulo
                   ,tc.descripcion
                   ,t.prestamo
                   ,case t.id_estatus_credito = 4 or t.id_estatus_credito = 7 or t.id_estatus_credito = 9
                      when true then t.prestamo
                      when false then 0
                   end as monto_aprobado
                  ,t.fecha_registro
                  ,case t.id_estatus_credito <> 1 or t.id_estatus_credito <> 3
                      when true then coalesce(t.cuotas_pagadas, 0)::text
                      else 0::text
                   end as cuotas_pagadas
                  ,case t.id_estatus_credito <> 4 or t.id_estatus_credito <> 7 or t.id_estatus_credito <> 9
                      when true then (
                            select coalesce(sum(cta.monto_capital), 0) + coalesce(sum(cdc.capital_cobro),0)
                            from
                                prestamos.credito_tabla_amortizacion cta
                            inner join prestamos.credito c on c.id = cta.idcredito
                                and c.idasociado = t.idasociado
                                and c.id = t.idcredito
                                and c.blnborrado is false
                            left join prestamos.datos_txt_tabla_amortizacion dtta on dtta.id_tabla_amortizacion = cta.id
                                and dtta.actual is true
                            left join prestamos.cobro_diferencial_cuota cdc on cdc.id_proceso = dtta.id
                                and cdc.blnborrado is false
                            where cta.blnborrado IS FALSE
                                and (cta.id_estatus_cuota is null or cta.id_estatus_cuota in (1, 3, 6, 7, 8, 9, 12))
                      )
                      else 0
                   end as saldo_deudor
                  ,0 as saldo_para_abono
                  ,t.nombre_estatus_credito
            FROM reporte_creditos t
            LEFT JOIN prestamos.tipo_credito tc ON (t.id_tipo_credito=tc.id)
            WHERE blnborrado=FALSE
                AND idasociado=:id
            ORDER BY fecha_registro DESC LIMIT 10
        ')->bindValues([
            'id' => $this->asociado,
        ])->queryAll();

        return (new Warp($prestamos))->map(function ($prestamo) {
            return [
                'nombre_titulo' => $prestamo['nombre_titulo'],
                'descripcion' => $prestamo['descripcion'],
                'prestamo' => $prestamo['prestamo'],
                'monto_aprobado' => $prestamo['monto_aprobado'],
                'fecha_registro' => $prestamo['fecha_registro'],
                'cuotas_pagadas' => $prestamo['cuotas_pagadas'],
                'saldo_deudor' => $prestamo['saldo_deudor'],
                'saldo_para_abono' => (string) ReporteCreditos::model()->getMontoDeudaActualConfigurado($prestamo['idcredito']),
                'nombre_estatus_credito' => $prestamo['nombre_estatus_credito'],
            ];
        });
    }

    protected function getDividendos()
    {
        return Yii::app()->getDb()->createCommand("
            SELECT ano ,
                   dividendo ,
                   fecha_registro ,
                   CASE activo = TRUE
                       WHEN TRUE THEN 'Aprobado'
                       ELSE 'Cargado'
                   END estatus
            FROM asociado_dividendos
            WHERE idasociado=:id
        ")->bindValues([
            'id' => $this->asociado,
        ])->queryAll();
    }

    protected function getAjusteHaberes()
    {
        return Yii::app()->getDb()->createCommand('
            SELECT numero_operacion ,
                   fecha_operacion::date ,
                   ca.descripcion  as concepto,
                   ta.descripcion as tipo_ajuste,
                   tm.descripcion as tipo_movimiento,
                   coalesce(ah.monto_asociado, 0) as monto_asociado,
                   coalesce(ah.monto_patrono, 0) as monto_patrono,
                   ah.observacion ,
                   ahs.fecha_registro::date,
                   eah.descripcion as estatus
            FROM ajuste_haberes.ajuste_haberes ah
            LEFT JOIN ajuste_haberes.concepto_ajuste ca ON ca.id=ah.id_concepto_ajuste
            LEFT JOIN ajuste_haberes.tipo_ajuste ta ON ta.id=ah.id_tipo_ajuste
            LEFT JOIN ajuste_haberes.tipo_movimiento tm ON tm.id=ah.id_tipo_movimiento
            LEFT JOIN ajuste_haberes.ajuste_haberes_seguimiento ahs ON ahs.id_ajuste_haberes=ah.id
                AND ahs.actual IS TRUE
            LEFT JOIN ajuste_haberes.estatus_ajuste_haberes eah ON eah.id=id_estatus_ajuste_haberes
            WHERE ah.idasociado=:id
        ')->bindValues([
            'id' => $this->asociado,
        ])->queryAll();
    }
}