<?php

class RecuperadoPorDefinicion
{
    protected $nomina;
    protected $datos = null;

    public function __construct(DatosTxtIntegrado $nomina)
    {
        $this->nomina = $nomina;
    }

    public function datos($key = null, $format = false)
    {
        if ($this->datos === null) {
            $this->datos = Yii::app()->getDb()->createCommand('
                select coalesce(reembolso.monto, 0) as reembolso,
                       coalesce(asignaciones.monto,0) as asignacion,
                       coalesce(remanente.monto,0) as remanente,
                       coalesce(no_asociados.monto,0) as no_asociado,
                       coalesce(reembolso.monto, 0) +
                       coalesce(asignaciones.monto,0) +
                       coalesce(remanente.monto,0) +
                       coalesce(no_asociados.monto,0) as total
                from retenciones.datos_txt_integrado dti
                -- reembolso
                left join (
                    select id_txt
                          ,sum(monto) as monto
                    from (
                        select id_txt,
                            coalesce(monto_cuota, 0) as monto
                        from prestamos.datos_txt_tabla_amortizacion_diferido
                        where id_estatus_amortizacion_diferida in (2, 9)
                            and id_txt=:nomina
                        union all
                        select dtta.id_txt
                            ,dtta.monto_remanente as monto
                        from prestamos.datos_txt_tabla_amortizacion dtta
                        inner join prestamos.datos_txt_tabla_amortizacion_seguimiento dttas on dttas.id_cuota=dtta.id
                            and dttas.actual is true
                        where dtta.id_txt=:nomina
                            and dttas.id_estatus=9 -- reembolso
                            and dtta.monto_remanente is not null
                            -- remueve cuotas afectadas por subprocesos
                            and dtta.id_tabla_amortizacion not in (
                                select id_tabla_amortizacion
                                from prestamos.pre_nomina_tabla_amortizacion
                                where id_pre_nomina_credito=:prenomina
                                    and blnborrado is true
                            )
                    ) as t
                    group by id_txt
                ) as reembolso on reembolso.id_txt=dti.id
                -- asginaciones
                left join (
                    select id_txt
                          ,sum(monto) as monto
                    from (
                        SELECT id_txt
                            ,coalesce(monto_cuota, 0) as monto
                        FROM prestamos.datos_txt_tabla_amortizacion_diferido
                        where id_estatus_amortizacion_diferida = 11
                            and id_txt=:nomina
                        union all
                        select dtta.id_txt
                            ,dtta.monto_remanente as monto
                        from prestamos.datos_txt_tabla_amortizacion dtta
                        inner join prestamos.datos_txt_tabla_amortizacion_seguimiento dttas on dttas.id_cuota=dtta.id
                            and dttas.actual is true
                        where dtta.id_txt=:nomina
                            and dttas.id_estatus=11 -- asignacion
                            and dtta.monto_remanente is not null
                            -- remueve cuotas afectadas por subprocesos
                            and dtta.id_tabla_amortizacion not in (
                                select id_tabla_amortizacion
                                from prestamos.pre_nomina_tabla_amortizacion
                                where id_pre_nomina_credito=:prenomina
                                    and blnborrado is true
                            )
                    ) as t
                    group by id_txt
                ) as asignaciones on asignaciones.id_txt=dti.id
                -- no asociado
                left join (
                    SELECT id_txt,
                        coalesce(sum(monto_cuota), 0) as monto
                    FROM prestamos.datos_txt_tabla_amortizacion_diferido
                    where id_estatus_amortizacion_diferida = 1
                    group by id_txt
                ) as no_asociados on no_asociados.id_txt=dti.id
                -- remanente
                left join (
                    select id_txt,
                        coalesce(sum(monto_remanente), 0) as monto
                    from prestamos.datos_txt_tabla_amortizacion
                    where actual is true
                    group by id_txt
                ) as remanente on remanente.id_txt=dti.id
                where dti.id=:nomina
            ')->bindValues([
                'nomina' => $this->nomina->id,
                'prenomina' => $this->nomina->id_prenomina_credito,
            ])->queryRow();
        }

        if ($format) {
            return Yii::app()->format->number(
                (new Warp($this->datos))->get($key, 0)
            );
        }

        return (new Warp($this->datos))->get($key, 0);
    }
}