<?php

namespace App\Http\Controllers;

use App\Models\RecursosCentroCosto;
use App\Models\RecursosCentroCostoMob;
use App\Models\CostoPrimario;
use App\Models\InformacionBasesDistribucion;
use App\Models\InformacionBasesDistribucion2;
use App\Models\InformacionFinanciera;
use Illuminate\Http\Request;

class RecursosCentroCostoController extends Controller
{
    public function consolidarRecursosCentroCosto(Request $request) {
        $contrato = $request->input('con_fk_id');
        $institucion = $request->input('ins_fk_id');
        $ano = $request->input('rcc_ano');
        $mes = $request->input('rcc_mes');

        // Respuesta hacia el usuario
        $response = new \stdClass();
        $response->estado = 1;
        $response->errores = "";

        \DB::transaction(function() use($contrato, $institucion, $ano, $mes, $response) {
            // Revision de la informacion de bases de distribucion
            $revision = $this->verificarEstadoDistribucion($contrato, $institucion, $ano, $mes);

            if (count($revision) != 0) {
                $response->estado = 0;
                $response->errores = json_encode($revision);
            } else {
                // Borrar la informacion consolidada previamente
                RecursosCentroCosto::on('costos_principal')
                                ->where('con_fk_id', $contrato)
                                ->where('ins_fk_id', $institucion)
                                ->where('rcc_ano', $ano)
                                ->where('rcc_mes', $mes)
                                ->delete();

                RecursosCentroCostoMob::on('costos_principal')
                                ->where('con_fk_id', $contrato)
                                ->where('ins_fk_id', $institucion)
                                ->where('rcc_ano', $ano)
                                ->where('rcc_mes', $mes)
                                ->delete();

                CostoPrimario::on('costos_principal')
                            ->where('con_fk_id', $contrato)
                            ->where('ins_fk_id', $institucion)
                            ->where('cpr_ano', $ano)
                            ->where('cpr_mes', $mes)
                            ->delete();

                \DB::select("
                    insert into costos_principal.recursos_centro_costo (con_fk_id, ins_fk_id, rcc_ano, rcc_mes, cuc_fk_id, elm_fk_id, tre_fk_id, cre_fk_id, cco_fk_id, rcc_valor)
                    select ".$contrato.",
                           '".$institucion."',
                           ".$ano.",
                           ".$mes.",
                           s1.cuc_fk_id,
                           s1.elm_fk_id,
                           s1.tre_fk_id,
                           s1.cre_fk_id,
                           s1.cco_fk_id,
                           sum(s1.ifm_valor * (s1.ibd_valor / valor_base_dist) * (s1.dib_ponderacion / 100))  as rcc_valor
                    from (
                        select ifm.ifm_pk_id,
                               ifm.cuc_fk_id,
                               cuc.elm_fk_id,
                               cuc.tre_fk_id,
                               cuc.cre_fk_id,
                               dcc.cco_fk_id,
                               dcb.bdi_fk_id,
                               ibd.ibd_valor,
                               sum(ibd.ibd_valor) over (partition by ifm.ifm_pk_id, dcb.bdi_fk_id order by dcb.bdi_fk_id) as valor_base_dist,
                               dcb.dib_ponderacion,
                               ifm_valor
                        from costos_principal.informacion_financiera as ifm
                        join costos_principal.cuentas_contables as cuc on (ifm.cuc_fk_id = cuc.cuc_pk_id)
                        join costos_principal.distribucion_ifm_costos_cuentas as dcc on (dcc.ifm_fk_id = ifm.ifm_pk_id)
                        join costos_principal.distribucion_ifm_costos_bases as dcb on (dcb.ifm_fk_id = ifm.ifm_pk_id and dcb.bdi_fk_id not in (1,2))
                        join costos_principal.informacion_bases_distribucion as ibd on (ibd.ibd_ano = ".$ano." and ibd.ibd_mes = ".$mes." and
                                                                                        ibd.con_fk_id = ".$contrato." and ibd.ins_fk_id = '".$institucion."' and
                                                                                        ibd.cco_fk_id = dcc.cco_fk_id and ibd.bdi_fk_id = dcb.bdi_fk_id)
                        where ifm.con_fk_id = ".$contrato." and ifm.ins_fk_id = '".$institucion."' and ifm_ano = ".$ano." and ifm_mes = ".$mes." and ifm_mano_obra = false and
                              ifm_asignado = true
                        order by ifm_pk_id, cco_fk_id, bdi_fk_id
                    ) as s1
                    group by 1, 2, 3, 4, 5, 6, 7, 8, 9
                ");

                // Consolidar los recursos por centro de costo
                /*\DB::select("insert into costos_principal.recursos_centro_costo (con_fk_id, ins_fk_id, rcc_ano, rcc_mes, cuc_fk_id, elm_fk_id, tre_fk_id, cre_fk_id, cco_fk_id, rcc_valor)
                            select ".$contrato.",
                                '".$institucion."',
                                ".$ano.",
                                ".$mes.",
                                cp.cuc_fk_id,
                                cp.elm_fk_id,
                                cp.tre_fk_id,
                                cp.cre_fk_id,
                                cp.cco_fk_id,
                                sum((cast(ifm_valor as double precision) * cast(cp.dib_ponderacion as double precision) * cast(cp.ponderacion_bd as double precision))) as costo
                            from (
                                select ifm_pk_id,
                                    ifm_valor,
                                    cuc_fk_id,
                                    elm_fk_id,
                                    tre_fk_id,
                                    cre_fk_id,
                                    dcc.cco_fk_id,
                                    dcb.bdi_fk_id,
                                    dib_ponderacion / 100 as dib_ponderacion,
                                    ibd_valor,
                                    ibd_valor / cast((
                                        select sum(ibd_valor)
                                        from costos_principal.informacion_bases_distribucion
                                        where con_fk_id = ".$contrato." and
                                            ins_fk_id = '".$institucion."' and 
                                            ibd_ano = ".$ano." and
                                            ibd_mes = ".$mes." and
                                            bdi_fk_id = dcb.bdi_fk_id and
                                            cco_fk_id in (
                                                    select cco_fk_id
                                                    from costos_principal.distribucion_ifm_costos_cuentas
                                                    where con_fk_id = ".$contrato." and
                                                        ins_fk_id = '".$institucion."' and
                                                        dcu_ano = ".$ano." and
                                                        dcu_mes = ".$mes." and
                                                        ifm_fk_id = ifm_pk_id
                                            )
                                    ) as double precision) as ponderacion_bd
                                from costos_principal.distribucion_ifm_costos_cuentas as dcc
                                join costos_principal.informacion_financiera on (ifm_fk_id = ifm_pk_id)
                                join costos_principal.cuentas_contables on (informacion_financiera.cuc_fk_id = cuc_pk_id)
                                join costos_principal.distribucion_ifm_costos_bases as dcb on (dcb.ifm_fk_id = dcc.ifm_fk_id)
                                join costos_principal.informacion_bases_distribucion on (informacion_bases_distribucion.bdi_fk_id = dcb.bdi_fk_id and
                                                                                        informacion_bases_distribucion.cco_fk_id = dcc.cco_fk_id and
                                                                                        informacion_bases_distribucion.ibd_ano = ".$ano." and
                                                                                        informacion_bases_distribucion.ibd_mes = ".$mes.")
                                where dcc.con_fk_id = ".$contrato." and
                                    dcc.ins_fk_id = '".$institucion."' and
                                    dcu_ano = ".$ano." and
                                    dcu_mes = ".$mes." and
                                    ifm_asignado = true and
                                    dcb.bdi_fk_id not in (1,2) and 
                                    ifm_mano_obra = false
                            ) as cp
                            group by 1, 2, 3, 4, 5, 6, 7, 8, 9");*/

                // Agregar distribuciones por partes iguales
                $this->agregarDistribucionPartesIguales($contrato, $institucion, $ano, $mes);

                // Agregar distribucion por valor directo
                $this->agregarDistribucionValorDirecto($contrato, $institucion, $ano, $mes);

                // Agregar mano de obra devuelta
                $this->agregarDistribucionManoObraDeveulta($contrato, $institucion, $ano, $mes);

                // Agregar distribucion mano de obra
                $this->agregarDistribucionManoObra($contrato, $institucion, $ano, $mes);

                // Consolidar el costo primario
                $this->consolidarCostoPrimario($contrato, $institucion, $ano, $mes);
            }
        });

        return array("response" => json_encode($response));
    }

    // Mirar si todas los movimientos tienen al menos un centro de costo asignado por base de distribucion
    private function verificarEstadoDistribucion($contrato, $institucion, $ano, $mes) {
        return \DB::select('select mov.ifm_fk_id, bdi_descripcion
                            from (
                                select dcc.ifm_fk_id, dcb.bdi_fk_id, sum(ibd_valor)
                                from costos_principal.distribucion_ifm_costos_cuentas as dcc
                                join costos_principal.distribucion_ifm_costos_bases as dcb on (dcc.ifm_fk_id = dcb.ifm_fk_id)
                                left join costos_principal.informacion_bases_distribucion as ibd on (dcb.bdi_fk_id = ibd.bdi_fk_id and
                                                                                                    ibd.cco_fk_id = dcc.cco_fk_id and
                                                                                                    ibd.ibd_ano = '.$ano.' and ibd.ibd_mes = '.$mes.')
                                where dcc.con_fk_id = '.$contrato.' and
                                    dcc.ins_fk_id = \''.$institucion.'\' and
                                    dcc.dcu_ano = '.$ano.' and
                                    dcc.dcu_mes = '.$mes.' and
                                    dcb.bdi_fk_id != 1 and
                                    dcb.bdi_fk_id != 2
                                group by 1, 2
                                having sum(ibd_valor) is null
                                order by 1 asc
                            ) as mov
                            join costos_principal.bases_distribucion on (bdi_pk_id = mov.bdi_fk_id)');
    }

    public function getRecursosCentroCosto(Request $request) {
        return RecursosCentroCosto::on('costos_principal')
                                  ->where('con_fk_id', $request->input('con_fk_id'))
                                  ->where('ins_fk_id', $request->input('ins_fk_id'))
                                  ->where('rcc_ano', $request->input('rcc_ano'))
                                  ->where('rcc_mes', $request->input('rcc_mes'))
                                  ->get()->toArray();
    }

    private function agregarDistribucionPartesIguales($contrato, $institucion, $ano, $mes) {
        $partesIguales = \DB::select("select cuc.cuc_pk_id,
                                             cuc.elm_fk_id,
                                             cuc.tre_fk_id,
                                             cuc.cre_fk_id,
                                             dcc.cco_fk_id,
                                             ifm.ifm_valor * (dcb.dib_ponderacion / 100) / (
                                                select count(*)
                                                from costos_principal.distribucion_ifm_costos_cuentas
                                                where con_fk_id = ".$contrato." and
                                                        ins_fk_id = '".$institucion."' and
                                                        dcu_ano = ".$ano." and
                                                        dcu_mes = ".$mes." and
                                                        ifm_fk_id = ifm.ifm_pk_id
                                             ) as costo_distribuido
                                    from costos_principal.informacion_financiera as ifm
                                    join costos_principal.cuentas_contables as cuc on (ifm.cuc_fk_id = cuc.cuc_pk_id)
                                    join costos_principal.distribucion_ifm_costos_cuentas as dcc on (dcc.ifm_fk_id = ifm.ifm_pk_id)
                                    join costos_principal.distribucion_ifm_costos_bases as dcb on (dcb.ifm_fk_id = ifm.ifm_pk_id)
                                    where ifm.con_fk_id = ".$contrato." and
                                          ifm.ins_fk_id = '".$institucion."' and
                                          ifm_ano = ".$ano." and
                                          ifm_mes = ".$mes." and
                                          dcb.bdi_fk_id = 2");

        // Determinar asignaciones realizadas
        $rccDb = RecursosCentroCosto::on('costos_principal')
                                    ->where('con_fk_id', $contrato)
                                    ->where('ins_fk_id', $institucion)
                                    ->where('rcc_ano', $ano)
                                    ->where('rcc_mes', $mes)
                                    ->get()->toArray();

        $rccExistentes = [];

        foreach ($rccDb as $rcc) {
            $rccExistentes[$rcc['cuc_fk_id']][$rcc['cco_fk_id']] = $rcc['rcc_valor'];
        }

        // Agregar dsitribucion a recursos por centro de costo
        foreach ($partesIguales as $pi) {
            // Existe la asignacion
            if (isset($rccExistentes[$pi->cuc_pk_id][$pi->cco_fk_id])) {
                RecursosCentroCosto::on('costos_principal')
                                   ->where('con_fk_id', $contrato)
                                   ->where('ins_fk_id', $institucion)
                                   ->where('rcc_ano', $ano)
                                   ->where('rcc_mes', $mes)
                                   ->where('cuc_fk_id', $pi->cuc_pk_id)
                                   ->where('cco_fk_id', $pi->cco_fk_id)
                ->update(
                    [
                        "rcc_valor" => doubleval($rccExistentes[$pi->cuc_pk_id][$pi->cco_fk_id]) +
                                       doubleval($pi->costo_distribuido)
                    ]
                );

                $rccExistentes[$pi->cuc_pk_id][$pi->cco_fk_id] = doubleval($rccExistentes[$pi->cuc_pk_id][$pi->cco_fk_id]) +
                                                                 doubleval($pi->costo_distribuido);
            } else { // Toca crear la asignacion
                RecursosCentroCosto::on('costos_principal')->create(
                    [
                        "con_fk_id" => $contrato,
                        "ins_fk_id" => $institucion,
                        "rcc_ano" => $ano,
                        "rcc_mes" => $mes,
                        "cuc_fk_id" => $pi->cuc_pk_id,
                        "elm_fk_id" => $pi->elm_fk_id,
                        "tre_fk_id" => $pi->tre_fk_id,
                        "cre_fk_id" => $pi->cre_fk_id,
                        "cco_fk_id" => $pi->cco_fk_id,
                        "rcc_valor" => $pi->costo_distribuido
                    ]
                );

                $rccExistentes[$pi->cuc_pk_id][$pi->cco_fk_id] = $pi->costo_distribuido;
            }
        }
    }

    private function agregarDistribucionValorDirecto($contrato, $institucion, $ano, $mes) {
        $valorDirecto = \DB::select("select cuc.cuc_pk_id,
                                            cuc.elm_fk_id,
                                            cuc.tre_fk_id,
                                            cuc.cre_fk_id,
                                            dvd.cco_fk_id,
                                            dvd.dvd_valor
                                    from costos_principal.distribucion_valor_directo as dvd
                                    join costos_principal.informacion_financiera as ifm on (dvd.ifm_fk_id = ifm.ifm_pk_id)
                                    join costos_principal.cuentas_contables as cuc on (ifm.cuc_fk_id = cuc.cuc_pk_id)
                                    where dvd.con_fk_id = ".$contrato." and
                                          dvd.ins_fk_id = '".$institucion."' and
                                          dvd_ano = ".$ano." and
                                          dvd_mes = ".$mes);

        // Determinar asignaciones realizadas
        $rccDb = RecursosCentroCosto::on('costos_principal')
                                    ->where('con_fk_id', $contrato)
                                    ->where('ins_fk_id', $institucion)
                                    ->where('rcc_ano', $ano)
                                    ->where('rcc_mes', $mes)
                                    ->get()->toArray();

        $rccExistentes = [];

        foreach ($rccDb as $rcc) {
            $rccExistentes[$rcc['cuc_fk_id']][$rcc['cco_fk_id']] = $rcc['rcc_valor'];
        }

        // Agregar dsitribucion a recursos por centro de costo
        foreach ($valorDirecto as $vd) {
            // Existe la asignacion
            if (isset($rccExistentes[$vd->cuc_pk_id][$vd->cco_fk_id])) {
                RecursosCentroCosto::on('costos_principal')
                                   ->where('con_fk_id', $contrato)
                                   ->where('ins_fk_id', $institucion)
                                   ->where('rcc_ano', $ano)
                                   ->where('rcc_mes', $mes)
                                   ->where('cuc_fk_id', $vd->cuc_pk_id)
                                   ->where('cco_fk_id', $vd->cco_fk_id)
                ->update(
                    [
                        "rcc_valor" => doubleval($rccExistentes[$vd->cuc_pk_id][$vd->cco_fk_id]) +
                                       doubleval($vd->dvd_valor)
                    ]
                );

                $rccExistentes[$vd->cuc_pk_id][$vd->cco_fk_id] = doubleval($rccExistentes[$vd->cuc_pk_id][$vd->cco_fk_id]) +
                                                                 doubleval($vd->dvd_valor);
            } else { // Toca crear la asignacion
                RecursosCentroCosto::on('costos_principal')->create(
                    [
                        "con_fk_id" => $contrato,
                        "ins_fk_id" => $institucion,
                        "rcc_ano" => $ano,
                        "rcc_mes" => $mes,
                        "cuc_fk_id" => $vd->cuc_pk_id,
                        "elm_fk_id" => $vd->elm_fk_id,
                        "tre_fk_id" => $vd->tre_fk_id,
                        "cre_fk_id" => $vd->cre_fk_id,
                        "cco_fk_id" => $vd->cco_fk_id,
                        "rcc_valor" => $vd->dvd_valor
                    ]
                );

                $rccExistentes[$vd->cuc_pk_id][$vd->cco_fk_id] = $vd->dvd_valor;
            }
        }
    }

    private function agregarDistribucionManoObraDeveulta($contrato, $institucion, $ano, $mes) {
        \DB::statement("
            insert into costos_principal.recursos_centro_costo_mob (con_fk_id, ins_fk_id, rcc_ano, rcc_mes, cuc_fk_id, elm_fk_id, tre_fk_id, cre_fk_id, cco_fk_id, rcc_valor)
            select con_fk_id, ins_fk_id, rcc_ano, rcc_mes, cuc_fk_id, elm_fk_id, tre_fk_id, cre_fk_id, cco_fk_id, rcc_valor
            from costos_principal.recursos_centro_costo
            where con_fk_id = ".$contrato." and ins_fk_id = '".$institucion."' and rcc_ano = ".$ano." and rcc_mes = ".$mes." and elm_fk_id = 3
        ");
    }

    private function agregarDistribucionManoObra($contrato, $institucion, $ano, $mes) {
        $manoObra = \DB::select("select rcc.cuc_pk_id,
                                        rcc.elm_fk_id,
                                        case when mcc.tre_fk_id is null then rcc.tre_fk_id else mcc.tre_fk_id end as tre_fk_id,
                                        case when mcc.cre_fk_id is null then rcc.cre_fk_id else mcc.cre_fk_id end as cre_fk_id,
                                        rcc.cco_fk_id,
                                        rcc.mob_fk_id,
                                        rcc.imo_horas,
                                        rcc.imo_valor,
                                        rcc.costo_inicial,
                                        rcc.porcentaje_cuenta,
                                        rcc.costo
                                from (
                                    select pcu.cuc_fk_id as cuc_pk_id,
                                           cuc.elm_fk_id,
                                           cuc.tre_fk_id,
                                           cuc.cre_fk_id,
                                           mot.cco_fk_id,
                                           mot.mob_fk_id,
                                           imo_horas,
                                           imo_valor,
                                           costo as costo_inicial,
                                           pcu.porcentaje_cuenta,
                                           costo * pcu.porcentaje_cuenta as costo
                                    from (
                                        select cco_fk_id,
                                            mot_nit_tercero,
                                            mot_razon_social,
                                            imo.mob_fk_id,
                                            imo_horas,
                                            imo_valor,
                                            coalesce(imo_valor * imo_horas / nullif((
                                                    select sum(imo_horas)
                                                    from costos_principal.informacion_mano_obra
                                                    where imo.con_fk_id = ".$contrato." and
                                                        imo.ins_fk_id = '".$institucion."' and
                                                        imo_ano = ".$ano." and
                                                        imo_mes = ".$mes." and
                                                        mob_fk_id = imo.mob_fk_id and
                                                        mot_fk_id in (
                                                            select mot_pk_id
                                                            from costos_principal.mano_obra_terceros
                                                            where con_fk_id = ".$contrato." and
                                                                    ins_fk_id = '".$institucion."' and
                                                                    mot_ano = ".$ano." and
                                                                    mot_mes = ".$mes." and
                                                                    mot_nit_tercero = mot.mot_nit_tercero
                                                        )
                                            ), 0), 0) as costo
                                            from costos_principal.informacion_mano_obra as imo
                                            join costos_principal.mano_obra_terceros as mot on (imo.mot_fk_id = mot.mot_pk_id)
                                            where imo.con_fk_id = ".$contrato." and
                                                  imo.ins_fk_id = '".$institucion."' and
                                                  imo_ano = ".$ano." and
                                                  imo_mes = ".$mes."
                                            order by 2,3
                                    ) as mot
                                    join (
                                        select ifm.ifm_nit_tercero,
                                               ifm.ifm_razon_social,
                                               cuc_fk_id,
                                               coalesce(ifm_valor / nullif(valor, 0), 0) as porcentaje_cuenta
                                        from (
                                            select ifm.ifm_nit_tercero,
                                                   ifm.ifm_razon_social,
                                                   cuc_fk_id,
                                                   sum(ifm_valor) as ifm_valor
                                            from costos_principal.informacion_financiera as ifm
                                            where con_fk_id = ".$contrato." and
                                                  ins_fk_id = '".$institucion."' and
                                                  ifm_ano = ".$ano." and
                                                  ifm_mes = ".$mes." and
                                                  ifm_mano_obra = true
                                            group by 1,2,3
                                            order by 1,2,3
                                        ) as ifm
                                        join (
                                            select ifm_nit_tercero, ifm_razon_social, sum(ifm_valor) as valor
                                            from costos_principal.informacion_financiera
                                            where con_fk_id = ".$contrato." and
                                                  ins_fk_id = '".$institucion."' and
                                                  ifm_ano = ".$ano." and
                                                  ifm_mes = ".$mes." and
                                                  ifm_mano_obra = true
                                            group by 1,2
                                            order by 1,2
                                        ) as tot on (ifm.ifm_nit_tercero = tot.ifm_nit_tercero and ifm.ifm_razon_social = tot.ifm_razon_social)
                                    ) as pcu on (mot.mot_nit_tercero = pcu.ifm_nit_tercero and mot.mot_razon_social = pcu.ifm_razon_social)
                                    join costos_principal.cuentas_contables as cuc on (cuc.cuc_pk_id = pcu.cuc_fk_id)
                                ) as rcc
                                left join costos_principal.mano_obra_centro_costo as mcc on (mcc.con_fk_id = ".$contrato." and mcc.ins_fk_id = '".$institucion."' and mcc_ano = ".$ano." and
                                                                                             rcc.cco_fk_id = mcc.cco_fk_id and rcc.mob_fk_id = mcc.mob_fk_id)");

        // Determinar asignaciones realizadas
        $rccDb = RecursosCentroCosto::on('costos_principal')
                                    ->where('con_fk_id', $contrato)
                                    ->where('ins_fk_id', $institucion)
                                    ->where('rcc_ano', $ano)
                                    ->where('rcc_mes', $mes)
                                    ->get()->toArray();

        $rccExistentes = [];

        foreach ($rccDb as $rcc) {
            $rccExistentes[$rcc['cuc_fk_id']][$rcc['tre_fk_id']][$rcc['cre_fk_id']][$rcc['cco_fk_id']] = $rcc['rcc_valor'];
        }

        // Agregar dsitribucion a recursos por centro de costo
        foreach ($manoObra as $mo) {
            // Existe la asignacion
            if (isset($rccExistentes[$mo->cuc_pk_id][$mo->tre_fk_id][$mo->cre_fk_id][$mo->cco_fk_id])) {
                RecursosCentroCosto::on('costos_principal')
                                   ->where('con_fk_id', $contrato)
                                   ->where('ins_fk_id', $institucion)
                                   ->where('rcc_ano', $ano)
                                   ->where('rcc_mes', $mes)
                                   ->where('cuc_fk_id', $mo->cuc_pk_id)
                                   ->where('tre_fk_id', $mo->tre_fk_id)
                                   ->where('cre_fk_id', $mo->cre_fk_id)
                                   ->where('cco_fk_id', $mo->cco_fk_id)
                ->update(
                    [
                        "rcc_valor" => doubleval($rccExistentes[$mo->cuc_pk_id][$mo->tre_fk_id][$mo->cre_fk_id][$mo->cco_fk_id]) +
                                       doubleval($mo->costo)
                    ]
                );

                $rccExistentes[$mo->cuc_pk_id][$mo->tre_fk_id][$mo->cre_fk_id][$mo->cco_fk_id] = doubleval($rccExistentes[$mo->cuc_pk_id][$mo->tre_fk_id][$mo->cre_fk_id][$mo->cco_fk_id]) +
                                                                                                 doubleval($mo->costo);
            } else { // Toca crear la asignacion
                RecursosCentroCosto::on('costos_principal')->create(
                    [
                        "con_fk_id" => $contrato,
                        "ins_fk_id" => $institucion,
                        "rcc_ano" => $ano,
                        "rcc_mes" => $mes,
                        "cuc_fk_id" => $mo->cuc_pk_id,
                        "elm_fk_id" => $mo->elm_fk_id,
                        "tre_fk_id" => $mo->tre_fk_id,
                        "cre_fk_id" => $mo->cre_fk_id,
                        "cco_fk_id" => $mo->cco_fk_id,
                        "rcc_valor" => $mo->costo
                    ]
                );

                $rccExistentes[$mo->cuc_pk_id][$mo->tre_fk_id][$mo->cre_fk_id][$mo->cco_fk_id] = doubleval($mo->costo);
            }
        }
    }

    private function consolidarCostoPrimario($contrato, $institucion, $ano, $mes) {
        \DB::select("insert into costos_principal.costo_primario (con_fk_id, ins_fk_id, cpr_ano, cpr_mes, cco_fk_id, cpr_suministros_directos,
                                                                  cpr_suministros_indirectos, cpr_suministros_fijos, cpr_suministros_variables,
                                                                  cpr_suministros_total, cpr_mano_obra_directa, cpr_mano_obra_indirecta,
                                                                  cpr_mano_obra_fija, cpr_mano_obra_variable, cpr_mano_obra_total,
                                                                  cpr_gastos_gen_directos, cpr_gastos_gen_indirectos, cpr_gastos_gen_fijos,
                                                                  cpr_gastos_gen_variables, cpr_gastos_gen_total, cpr_costos_directos,
                                                                  cpr_costos_indirectos, cpr_costos_fijos, cpr_costos_variables, cpr_total)
                    select ".$contrato.",
                            '".$institucion."',
                            ".$ano.",
                            ".$mes.",
                            cco_fk_id,
                            coalesce(sum(rcc_valor) filter (where elm_fk_id = 4 and tre_fk_id = 1), 0) as suministros_directos,
                            coalesce(sum(rcc_valor) filter (where elm_fk_id = 4 and tre_fk_id = 2), 0) as suministros_indirectos,
                            coalesce(sum(rcc_valor) filter (where elm_fk_id = 4 and cre_fk_id = 1), 0) as suministros_fijos,
                            coalesce(sum(rcc_valor) filter (where elm_fk_id = 4 and cre_fk_id = 2), 0) as suministros_variables,
                            coalesce(sum(rcc_valor) filter (where elm_fk_id = 4), 0) as suministros_total,
                            coalesce(sum(rcc_valor) filter (where elm_fk_id = 3 and tre_fk_id = 1), 0) as mano_obra_directa,
                            coalesce(sum(rcc_valor) filter (where elm_fk_id = 3 and tre_fk_id = 2), 0) as mano_obra_indirecta,
                            coalesce(sum(rcc_valor) filter (where elm_fk_id = 3 and cre_fk_id = 1), 0) as mano_obra_fija,
                            coalesce(sum(rcc_valor) filter (where elm_fk_id = 3 and cre_fk_id = 2), 0) as mano_obra_variable,
                            coalesce(sum(rcc_valor) filter (where elm_fk_id = 3), 0) as mano_obra_total,
                            coalesce(sum(rcc_valor) filter (where elm_fk_id = 2 and tre_fk_id = 1), 0) as gastos_generales_directos,
                            coalesce(sum(rcc_valor) filter (where elm_fk_id = 2 and tre_fk_id = 2), 0) as gastos_generales_indirectos,
                            coalesce(sum(rcc_valor) filter (where elm_fk_id = 2 and cre_fk_id = 1), 0) as gastos_generales_fijos,
                            coalesce(sum(rcc_valor) filter (where elm_fk_id = 2 and cre_fk_id = 2), 0) as gastos_generales_variables,
                            coalesce(sum(rcc_valor) filter (where elm_fk_id = 2), 0) as gastos_generales,
                            coalesce(sum(rcc_valor) filter (where tre_fk_id = 1), 0) as costos_directos,
                            coalesce(sum(rcc_valor) filter (where tre_fk_id = 2), 0) as costos_indirectos,
                            coalesce(sum(rcc_valor) filter (where cre_fk_id = 1), 0) as costos_fijos,
                            coalesce(sum(rcc_valor) filter (where cre_fk_id = 2), 0) as costos_variables,
                            coalesce(nullif(sum(rcc_valor) filter (where elm_fk_id != 1), 0), 0) as total
                    from costos_principal.recursos_centro_costo
                    where con_fk_id = ".$contrato." and
                          ins_fk_id = '".$institucion."' and
                          rcc_ano = ".$ano." and
                          rcc_mes = ".$mes."
                    group by 1,2,3,4,5");

        // Generar bases de distribución del costo primario
        $this->generarBasesDistribucionCostoPrimario($contrato, $institucion, $ano, $mes);
    }

    private function generarBasesDistribucionCostoPrimario($contrato, $institucion, $ano, $mes) {
        // Costo primario
        InformacionBasesDistribucion2::on('costos_principal')
                                     ->where('con_fk_id', $contrato)
                                     ->where('ins_fk_id', $institucion)
                                     ->where('ibd2_ano', $ano)
                                     ->where('ibd2_mes', $mes)
                                     ->where('bdi_fk_id', 7)
                                     ->where('ibd2_automatico', true)
                                     ->delete();

        \DB::select('insert into costos_principal.informacion_bases_distribucion2(con_fk_id, ins_fk_id, ibd2_ano, ibd2_mes, cco_fk_id, bdi_fk_id,
                                                                                  ibd2_valor, ibd2_automatico)
                    select '.$contrato.',
                            \''.$institucion.'\',
                            '.$ano.',
                            '.$mes.',
                            cco_fk_id,
                            7,
                            (cpr_suministros_total + cpr_mano_obra_total + cpr_gastos_gen_total),
                            true
                    from costos_principal.costo_primario
                    where con_fk_id = '.$contrato.' and
                          ins_fk_id = \''.$institucion.'\' and
                          cpr_ano = '.$ano.' and
                          cpr_mes = '.$mes);

        // Suminsitros
        InformacionBasesDistribucion2::on('costos_principal')
                                     ->where('con_fk_id', $contrato)
                                     ->where('ins_fk_id', $institucion)
                                     ->where('ibd2_ano', $ano)
                                     ->where('ibd2_mes', $mes)
                                     ->where('bdi_fk_id', 4)
                                     ->where('ibd2_automatico', true)
                                     ->delete();

        \DB::select('insert into costos_principal.informacion_bases_distribucion2(con_fk_id, ins_fk_id, ibd2_ano, ibd2_mes, cco_fk_id, bdi_fk_id,
                                                                                  ibd2_valor, ibd2_automatico)
                    select '.$contrato.',
                            \''.$institucion.'\',
                            '.$ano.',
                            '.$mes.',
                            cco_fk_id,
                            4,
                            cpr_suministros_total,
                            true
                    from costos_principal.costo_primario
                    where con_fk_id = '.$contrato.' and
                          ins_fk_id = \''.$institucion.'\' and
                          cpr_ano = '.$ano.' and
                          cpr_mes = '.$mes.' and
                          cpr_suministros_total != 0');

        // Mano de obra
        InformacionBasesDistribucion2::on('costos_principal')
                                     ->where('con_fk_id', $contrato)
                                     ->where('ins_fk_id', $institucion)
                                     ->where('ibd2_ano', $ano)
                                     ->where('ibd2_mes', $mes)
                                     ->where('bdi_fk_id', 5)
                                     ->where('ibd2_automatico', true)
                                     ->delete();

        \DB::select('insert into costos_principal.informacion_bases_distribucion2(con_fk_id, ins_fk_id, ibd2_ano, ibd2_mes, cco_fk_id, bdi_fk_id,
                                                                                  ibd2_valor, ibd2_automatico)
                    select '.$contrato.',
                            \''.$institucion.'\',
                            '.$ano.',
                            '.$mes.',
                            cco_fk_id,
                            5,
                            cpr_mano_obra_total,
                            true
                    from costos_principal.costo_primario
                    where con_fk_id = '.$contrato.' and
                          ins_fk_id = \''.$institucion.'\' and
                          cpr_ano = '.$ano.' and
                          cpr_mes = '.$mes.' and
                          cpr_mano_obra_total != 0');
                        
        // Gastos generales
        InformacionBasesDistribucion2::on('costos_principal')
                                     ->where('con_fk_id', $contrato)
                                     ->where('ins_fk_id', $institucion)
                                     ->where('ibd2_ano', $ano)
                                     ->where('ibd2_mes', $mes)
                                     ->where('bdi_fk_id', 6)
                                     ->where('ibd2_automatico', true)
                                     ->delete();

        \DB::select('insert into costos_principal.informacion_bases_distribucion2(con_fk_id, ins_fk_id, ibd2_ano, ibd2_mes, cco_fk_id, bdi_fk_id,
                                                                                  ibd2_valor, ibd2_automatico)
                    select '.$contrato.',
                            \''.$institucion.'\',
                            '.$ano.',
                            '.$mes.',
                            cco_fk_id,
                            6,
                            cpr_gastos_gen_total,
                            true
                    from costos_principal.costo_primario
                    where con_fk_id = '.$contrato.' and
                          ins_fk_id = \''.$institucion.'\' and
                          cpr_ano = '.$ano.' and
                          cpr_mes = '.$mes.' and
                          cpr_gastos_gen_total != 0');

        // Ingresos
        InformacionBasesDistribucion2::on('costos_principal')
                                     ->where('con_fk_id', $contrato)
                                     ->where('ins_fk_id', $institucion)
                                     ->where('ibd2_ano', $ano)
                                     ->where('ibd2_mes', $mes)
                                     ->where('bdi_fk_id', 8)
                                     ->where('ibd2_automatico', true)
                                     ->delete();

        \DB::select('insert into costos_principal.informacion_bases_distribucion2(con_fk_id, ins_fk_id, ibd2_ano, ibd2_mes, cco_fk_id, bdi_fk_id,
                                                                                  ibd2_valor, ibd2_automatico)
                    select '.$contrato.',
                            \''.$institucion.'\',
                            '.$ano.',
                            '.$mes.',
                            cco_fk_id,
                            8,
                            sum(rcc_valor),
                            true
                    from costos_principal.recursos_centro_costo
                    where con_fk_id = '.$contrato.' and
                          ins_fk_id = \''.$institucion.'\' and
                          rcc_ano = '.$ano.' and
                          rcc_mes = '.$mes.' and
                          elm_fk_id = 1
                          group by 1,2,3,4,5');
    }
}
