<?php

class TotalRecuperado
{
    protected $nomina;
    protected $total = null;

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

    public function total($key = null, $format = false)
    {
        if ($this->total === null) {
            $this->total = Yii::app()->getDb()->createCommand('
                select (
                    select coalesce(reembolso.monto, 0) +
                        coalesce(asignaciones.monto,0) +
                        coalesce(remanente.monto,0) +
                        coalesce(no_asociados.monto,0)
                    from retenciones.datos_txt_integrado dti
                    -- reembolso
                    left join (
                        select id_txt,
                            coalesce(sum(monto_cuota), 0) as monto
                        from prestamos.datos_txt_tabla_amortizacion_diferido
                        where id_estatus_amortizacion_diferida in (2, 9)
                        group by id_txt
                    ) as reembolso on reembolso.id_txt=dti.id
                    -- asginaciones
                    left join (
                        SELECT id_txt,
                            coalesce(sum(monto_cuota), 0) as monto
                        FROM prestamos.datos_txt_tabla_amortizacion_diferido
                        where id_estatus_amortizacion_diferida = 11
                        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
                )
                +
                (
                    select sum(capital) + sum(interes) as total
                    from (
                        select case when coalesce(cdc.id::boolean, false) is true  then coalesce(cdc.capital_pagado,0)
                                    when coalesce(cdc.id::boolean, false) is false then coalesce(cta.monto_capital,0)
                            end as capital,
                            case when coalesce(cdc.id::boolean, false) is true  then coalesce(cdc.interes_pagado,0)
                                    when coalesce(cdc.id::boolean, false) is false then coalesce(cta.monto_interes,0)
                            end as interes
                        from prestamos.datos_txt_tabla_amortizacion dtta
                        inner join retenciones.datos_txt_integrado dti on dti.id=dtta.id_txt
                        inner join prestamos.credito_tabla_amortizacion cta on cta.id=dtta.id_tabla_amortizacion
                        left join prestamos.cobro_diferencial_cuota cdc on cdc.id_proceso=dtta.id
                            and cdc.blnborrado is false
                        where dtta.id_txt=:nomina
                            -- 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 tabla
                )
            ')->queryScalar([
                'nomina' => $this->nomina->id,
                'prenomina' => $this->nomina->id_prenomina_credito,
            ]);
        }

        if ($format) {
            return Yii::app()->format->number($this->total);
        }

        return $this->total;
    }
}