<?php

namespace App\Http\Controllers;

use App\Models\Programas;
use App\Models\SedesProgramas;
use App\Models\EstadiosProgramas;
use App\Models\CostoPrgActividades;
use App\Models\CostoPrgSuministros;
use App\Models\CostoPrgPaquetes;
use App\Models\CostoEsperadoProgramas;
use App\Models\CostoPrgEjActividades;
use App\Models\CostoPrgEjSuministros;
use App\Models\CostoPrgEjPaquetes;
use App\Models\CostoEjecutadoProgramas;
use Illuminate\Http\Request;

class ProgramasController extends Controller
{
    public function getConteoProgramas(Request $request) {
        return Programas::on('costos_principal')
                        ->where('con_fk_id', $request->input('con_fk_id'))
                        ->where('ins_fk_id', $request->input('ins_fk_id'))
                        ->where('prg_ano', $request->input('prg_ano'))
                        ->count();
    }

    public function getProgramas(Request $request) {
        return Programas::on('costos_principal')
                        ->join('contratos_eps', 'coe_pk_id', 'coe_fk_id')
                        ->where('programas.con_fk_id', $request->input('con_fk_id'))
                        ->where('programas.ins_fk_id', $request->input('ins_fk_id'))
                        ->where('prg_ano', $request->input('prg_ano'))
                        ->orderBy('prg_descripcion')
                        ->get()->toArray();

    }

    public function getSedesPrograma(Request $request) {
        return SedesProgramas::on('costos_principal')
                             ->join('sedes', function($join) {
                                $join->on('sed_fk_id', 'sed_pk_id');
                                $join->on('sedes.con_fk_id', 'sedes_programas.con_fk_id');
                             })
                             ->where('prg_fk_id', $request->input('prg_fk_id'))
                             ->orderBy('sed_nombre_sede')
                             ->get()->toArray();

    }

    public function getSedesProgramasCompleto(Request $request) {
        return SedesProgramas::on('costos_principal')
                             ->join('programas', 'prg_fk_id', 'prg_pk_id')
                             ->join('contratos_eps', 'coe_fk_id', 'coe_pk_id')
                             ->join('sedes', function($join) {
                                $join->on('sedes.sed_pk_id', 'sedes_programas.sed_fk_id');
                                $join->on('sedes.con_fk_id', 'sedes_programas.con_fk_id');
                             })
                             ->where('sedes_programas.con_fk_id', $request->input('con_fk_id'))
                             ->where('sedes_programas.ins_fk_id', $request->input('ins_fk_id'))
                             ->where('programas.prg_ano', $request->input('prg_ano'))
                             ->orderBy('prg_codigo')
                             ->orderBy('coe_numero_contrato')
                             ->orderBy('sed_nombre_sede')
                             ->get()->toArray();

    }

    public function getEstadiosPrograma(Request $request) {
        return EstadiosProgramas::on('costos_principal')
                                ->where('prg_fk_id', $request->input('prg_fk_id'))
                                ->orderBy('esp_codigo')
                                ->get()->toArray();

    }

    public function buscarCodigoPrograma(Request $request) {
        return Programas::where('con_fk_id', $request->input('con_fk_id'))
                        ->where('ins_fk_id', $request->input('ins_fk_id'))
                        ->where('prg_ano', $request->input('prg_ano'))
                        ->where('prg_codigo', $request->input('prg_codigo'))
                        ->where('coe_fk_id', $request->input('coe_fk_id'))
                        ->count();
    }

    public function getProgramasEstadios(Request $request) {
        return EstadiosProgramas::on('costos_principal')
                                ->selectRaw('coe_pk_id, coe_numero_contrato, coe_nombre_eps, prg_pk_id, prg_codigo, prg_descripcion, esp_codigo, esp_descripcion')
                                ->join('programas', 'prg_fk_id', 'prg_pk_id')
                                ->join('contratos_eps', 'coe_fk_id', 'coe_pk_id')
                                ->where('programas.con_fk_id', $request->input('con_fk_id'))
                                ->where('programas.ins_fk_id', $request->input('ins_fk_id'))
                                ->where('prg_ano', $request->input('prg_ano'))
                                ->orderBy('coe_numero_contrato')
                                ->orderBy('prg_descripcion')
                                ->orderBy('esp_descripcion')
                                ->get()->toArray();
    }

    public function crearPograma(Request $request) {
        $id = null;

        \DB::transaction(function() use(&$id, $request) {
            $sedes = $request->input('sedes');
            $estadios = $request->input('estadios');

            $id = Programas::on('costos_principal')->create(
                [
                    "con_fk_id" => $request->input("con_fk_id"),
                    "ins_fk_id" => $request->input("ins_fk_id"),
                    "prg_ano" => $request->input("prg_ano"),
                    "prg_codigo" => $request->input("prg_codigo"),
                    "prg_descripcion" => $request->input("prg_descripcion"),
                    "coe_fk_id" => $request->input("coe_fk_id"),
                    "prg_unidad_medicion_pago" => $request->input("prg_unidad_medicion_pago"),
                    "prg_desc_estadios" => $request->input("prg_desc_estadios"),
                    "prg_id_sedes" => $request->input("prg_id_sedes")
                ]
            )->prg_pk_id;

            // Sedes
            foreach ($sedes as $sed) {
                SedesProgramas::on('costos_principal')->create(
                    [
                        'con_fk_id' => $request->input("con_fk_id"),
                        'ins_fk_id' => $request->input("ins_fk_id"),
                        "sep_ano" => $request->input("prg_ano"),
                        'prg_fk_id' => $id,
                        'sed_fk_id' => $sed['sed_fk_id']
                    ]
                );
            }

            // Estadíos
            foreach ($estadios as $esp) {
                EstadiosProgramas::on('costos_principal')->create(
                    [
                        "con_fk_id" => $request->input("con_fk_id"),
                        "ins_fk_id" => $request->input("ins_fk_id"),
                        "esp_ano" => $request->input("prg_ano"),
                        'prg_fk_id' => $id,
                        'esp_codigo' => $esp['esp_codigo'],
                        'esp_descripcion' => $esp['esp_descripcion']
                    ]
                );
            }
        });

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

    public function actualizarPrograma(Request $request) {
        \DB::transaction(function() use(&$id, $request) {
            $sedes = $request->input('sedes');
            $estadios = $request->input('estadios');

            Programas::on('costos_principal')->where('prg_pk_id', $request->input('prg_pk_id'))
            ->update(
                [
                    "prg_codigo" => $request->input("prg_codigo"),
                    "prg_descripcion" => $request->input("prg_descripcion"),
                    "coe_fk_id" => $request->input("coe_fk_id"),
                    "prg_unidad_medicion_pago" => $request->input("prg_unidad_medicion_pago"),
                    "prg_desc_estadios" => $request->input("prg_desc_estadios"),
                    "prg_id_sedes" => $request->input("prg_id_sedes")
                ]
            );

            // Sedes
            foreach ($sedes as $sep) {
                if ($sep['sep_pk_id'] == null) {
                    SedesProgramas::on('costos_principal')->create(
                        [
                            'con_fk_id' => $request->input("con_fk_id"),
                            'ins_fk_id' => $request->input("ins_fk_id"),
                            "sep_ano" => $request->input("prg_ano"),
                            'prg_fk_id' => $request->input('prg_pk_id'),
                            'sed_fk_id' => $sep['sed_fk_id']
                        ]
                    );
                } else if ($sep['sep_pk_id'] != null && $sep['sep_borrar']) {
                    SedesProgramas::on('costos_principal')->where('sep_pk_id', $sep['sep_pk_id'])->delete();
                }
            }

            // Estadíos
            foreach ($estadios as $esp) {
                if ($esp['esp_pk_id'] == null) {
                    EstadiosProgramas::on('costos_principal')->create(
                        [
                            "con_fk_id" => $request->input("con_fk_id"),
                            "ins_fk_id" => $request->input("ins_fk_id"),
                            "esp_ano" => $request->input("prg_ano"),
                            'prg_fk_id' => $request->input('prg_pk_id'),
                            'esp_codigo' => $esp['esp_codigo'],
                            'esp_descripcion' => $esp['esp_descripcion']
                        ]
                    );
                } else if ($esp['esp_pk_id'] != null && !$esp['esp_borrar']) {
                    EstadiosProgramas::on('costos_principal')->where('esp_pk_id', $esp['esp_pk_id'])
                    ->update(
                        [
                            'esp_codigo' => $esp['esp_codigo'],
                            'esp_descripcion' => $esp['esp_descripcion']
                        ]
                    );
                } else if ($esp['esp_pk_id'] != null && $esp['esp_borrar']) {
                    EstadiosProgramas::on('costos_principal')->where('esp_pk_id', $esp['esp_pk_id'])->delete();
                }
            }
        });

        return array("response" => 1);
    }

    public function importarProgramas(Request $request) {
        \DB::transaction(function () use($request) {
            $con_fk_id = $request->input('con_fk_id');
            $ins_fk_id = $request->input('ins_fk_id');
            $anoImportacion = $request->input('anoImportacion');
            $anoObjetivo = $request->input('anoObjetivo');

            // Borrar la información anterior
            Programas::on('costos_principal')
                     ->where('con_fk_id', $con_fk_id)
                     ->where('ins_fk_id', $ins_fk_id)
                     ->where('prg_ano', $anoObjetivo)
                     ->delete();

            // Crear la nueva información
            $programasImportar = Programas::on('costos_principal')
                                          ->where('con_fk_id', $con_fk_id)
                                          ->where('ins_fk_id', $ins_fk_id)
                                          ->where('prg_ano', $anoImportacion)
                                          ->get()->toArray();

            foreach ($programasImportar as $prg) {
                $id = Programas::on('costos_principal')->create(
                    [
                        "con_fk_id" => $con_fk_id,
                        "ins_fk_id" => $ins_fk_id,
                        "prg_ano" => $anoObjetivo,
                        "prg_codigo" => $prg['prg_codigo'],
                        "prg_descripcion" => $prg['prg_descripcion'],
                        "prg_desc_estadios" => $prg['prg_desc_estadios']
                    ]
                )->prg_pk_id;

                $estadiosImportar = EstadiosProgramas::on('costos_principal')
                                                     ->where('prg_fk_id', $prg['prg_pk_id'])
                                                     ->get()->toArray();

                foreach ($estadiosImportar as $esp) {
                    EstadiosProgramas::on('costos_principal')->create(
                        [
                            "con_fk_id" => $con_fk_id,
                            "ins_fk_id" => $ins_fk_id,
                            "prg_fk_id" => $id,
                            "esp_codigo" => $esp['esp_codigo'],
                            "esp_descripcion" => $esp['esp_descripcion']
                        ]
                    );
                }
            }
        });

        return array("response" => 1);
    }

    public function consolidarCostoProgramasPresupuestado(Request $request) {
        \DB::transaction(function () use($request) {
            $contrato = $request->input('con_fk_id');
            $institucion = $request->input('ins_fk_id');
            $ano = $request->input('cpg_ano');
            $mes = $request->input('cpg_mes');

            // Borrar la informacion anterior
            CostoPrgActividades::where('con_fk_id', $contrato)
                               ->where('ins_fk_id', $institucion)
                               ->where('cpa_ano', $ano)
                               ->where('cpa_mes', $mes)
                               ->delete();

            CostoPrgSuministros::where('con_fk_id', $contrato)
                               ->where('ins_fk_id', $institucion)
                               ->where('cps_ano', $ano)
                               ->where('cps_mes', $mes)
                               ->delete();

            CostoPrgPaquetes::where('con_fk_id', $contrato)
                            ->where('ins_fk_id', $institucion)
                            ->where('cpp_ano', $ano)
                            ->where('cpp_mes', $mes)
                            ->delete();

            CostoEsperadoProgramas::where('con_fk_id', $contrato)
                                  ->where('ins_fk_id', $institucion)
                                  ->where('cpg_ano', $ano)
                                  ->where('cpg_mes', $mes)
                                  ->delete();

            // Actividades
            \DB::statement("
                insert into costos_principal.costo_prg_actividades (con_fk_id, ins_fk_id, cpa_ano, cpa_mes, prg_fk_id, coe_fk_id, sed_fk_id, esp_fk_id,
                                                                    cco_fk_id, act_fk_id, cpa_costo_mob_dir_actividad, cpa_costo_mob_indir_actividad,
                                                                    cpa_costo_sum_dir_actividad, cpa_costo_sum_indir_actividad, cpa_costo_ggen_actividad,
                                                                    cpa_costo_cdist_actividad, cpa_costo_actividad)
                select *
                from (
                    select ".$contrato.",
                           '".$institucion."',
                           ".$ano.",
                           ".$mes.",
                           prg_pk_id,
                           prg.coe_fk_id,
                           sed.sed_pk_id,
                           esp.esp_pk_id,
                           acp.cco_fk_id,
                           acp.act_fk_id,
                           acp.acp_cantidad * acp.costo_mob_dir_actividad * dpp_poblacion / nullif(acp.acp_periodicidad, 0) as costo_mob_dir_actividad,
                           acp.acp_cantidad * acp.costo_mob_indir_actividad * dpp_poblacion / nullif(acp.acp_periodicidad, 0) as costo_mob_indir_actividad,
                           acp.acp_cantidad * acp.costo_sum_dir_actividad * dpp_poblacion / nullif(acp.acp_periodicidad, 0) as costo_sum_dir_actividad,
                           acp.acp_cantidad * acp.costo_sum_indir_actividad * dpp_poblacion / nullif(acp.acp_periodicidad, 0) as costo_sum_indir_actividad,
                           acp.acp_cantidad * acp.costo_ggen_actividad * dpp_poblacion / nullif(acp.acp_periodicidad, 0) as costo_ggen_actividad,
                           acp.acp_cantidad * acp.costo_cdist_actividad * dpp_poblacion / nullif(acp.acp_periodicidad, 0) as costo_cdist_actividad,
                           acp.acp_cantidad * acp.costo_actividad * dpp_poblacion / nullif(acp.acp_periodicidad, 0) as costo_actividad
                    from costos_principal.programas as prg
                    join costos_principal.sedes_programas as sep on (sep.prg_fk_id = prg.prg_pk_id)
                    join costos_principal.sedes as sed on (sed.con_fk_id = sep.con_fk_id and sed.ins_fk_id = sep.ins_fk_id and sed.sed_pk_id = sep.sed_fk_id)
                    join costos_principal.estadios_programas as esp on (esp.prg_fk_id = prg.prg_pk_id)
                    join costos_principal.detalle_pob_programa as dpp on (dpp.prg_fk_id = prg.prg_pk_id and dpp.sed_fk_id = sed.sed_pk_id and dpp.esp_fk_id = esp.esp_pk_id)
                    join (
                        select prg_fk_id,
                               sep_fk_id,
                               esp_fk_id,
                               acp.cco_fk_id,
                               acp.act_fk_id,
                               acp_cantidad,
                               acp_periodicidad,
                               cun.cun_mob_directa as costo_mob_dir_actividad,
                               cun.cun_mob_indirecta as costo_mob_indir_actividad,
                               cun.cun_sum_directo as costo_sum_dir_actividad,
                               cun.cun_sum_indirecto as costo_sum_indir_actividad,
                               cun.cun_gastos_gen as costo_ggen_actividad,
                               cun.cun_costo_dist as costo_cdist_actividad,
                               cun.cun_costo_unitario as costo_actividad
                        from costos_principal.actividades_programas as acp
                        join costos_principal.costo_unitario as cun on (cun.con_fk_id = ".$contrato." and cun.ins_fk_id = '".$institucion."' and
                                                                        cun.cun_ano = ".$ano." and cun.cun_mes = ".$mes." and
                                                                        cun.cco_fk_id = acp.cco_fk_id and cun.act_fk_id = acp.act_fk_id)
                        where acp.con_fk_id = ".$contrato." and acp.ins_fk_id = '".$institucion."' and acp_ano = ".$ano."
                        order by 1,2,3,4,5
                    ) as acp on (acp.prg_fk_id = prg.prg_pk_id and sep.sep_pk_id = acp.sep_fk_id and acp.esp_fk_id = esp.esp_pk_id)
                    order by 5, 6, 7, 8, 9, 10
                ) as cpa
                where cpa.costo_actividad is not null
            ");

            // Suministros
            \DB::statement("
                insert into costos_principal.costo_prg_suministros (con_fk_id, ins_fk_id, cps_ano, cps_mes, prg_fk_id, coe_fk_id,
                                                                    sed_fk_id, esp_fk_id, sum_fk_id, cps_costo_suministro)
                select *
                from (
                    select ".$contrato.",
                           '".$institucion."',
                           ".$ano.",
                           ".$mes.",
                           prg_pk_id,
                           prg.coe_fk_id,
                           sed.sed_pk_id,
                           esp.esp_pk_id,
                           sup.sum_fk_id,
                           sup.sup_cantidad * sup.costo_suministro * dpp_poblacion / nullif(sup.sup_periodicidad, 0) as costo_suministro
                    from costos_principal.programas as prg
                    join costos_principal.sedes_programas as sep on (sep.prg_fk_id = prg.prg_pk_id)
                    join costos_principal.sedes as sed on (sed.con_fk_id = sep.con_fk_id and sed.ins_fk_id = sep.ins_fk_id and sed.sed_pk_id = sep.sed_fk_id)
                    join costos_principal.estadios_programas as esp on (esp.prg_fk_id = prg.prg_pk_id)
                    join costos_principal.detalle_pob_programa as dpp on (dpp.prg_fk_id = prg.prg_pk_id and dpp.sed_fk_id = sed.sed_pk_id and dpp.esp_fk_id = esp.esp_pk_id)
                    join (
                        select prg_fk_id, sep_fk_id, esp_fk_id, sup.sum_fk_id, sup_cantidad, sup_periodicidad, des.des_costo as costo_suministro
                        from costos_principal.suministros_programas as sup
                        join (
                            select sum_fk_id, avg(des_valor_unitario) as des_costo
                            from costos_principal.detalle_suministros
                            where con_fk_id = ".$contrato." and ins_fk_id = '".$institucion."' and des_ano = ".$ano." and des_mes = ".$mes."
                            group by 1 order by 1
                        ) as des on (des.sum_fk_id = sup.sum_fk_id)
                        where sup.con_fk_id = ".$contrato." and sup.ins_fk_id = '".$institucion."' and sup_ano = ".$ano."
                        order by 1,2,3,4
                    ) as sup on (sup.prg_fk_id = prg.prg_pk_id and sep.sep_pk_id = sup.sep_fk_id and sup.esp_fk_id = esp.esp_pk_id)
                    where prg.con_fk_id = ".$contrato." and prg.ins_fk_id = '".$institucion."' and prg_ano = ".$ano." and dpp_mes = ".$mes."
                    order by 5, 6, 7, 8, 9
                ) as cps
                where cps.costo_suministro is not null
            ");

            // Paquetes
            \DB::statement("
                insert into costos_principal.costo_prg_paquetes (con_fk_id, ins_fk_id, cpp_ano, cpp_mes, prg_fk_id, coe_fk_id, sed_fk_id, esp_fk_id, act_fk_id,
                                                                 cpp_costo_mob_dir_paquete, cpp_costo_mob_indir_paquete, cpp_costo_sum_dir_paquete,
                                                                 cpp_costo_sum_indir_paquete, cpp_costo_ggen_paquete, cpp_costo_cdist_paquete, cpp_costo_paquete)
                select *
                from (
                    select ".$contrato.",
                           '".$institucion."',
                           ".$ano.",
                           ".$mes.",
                           prg_pk_id,
                           prg.coe_fk_id,
                           sed.sed_pk_id,
                           esp.esp_pk_id,
                           pap.paquete,
                           pap.pap_cantidad * pap.costo_mob_dir_paquete * dpp_poblacion / nullif(pap.pap_periodicidad, 0) as costo_mob_dir_paquete,
                           pap.pap_cantidad * pap.costo_mob_indir_paquete * dpp_poblacion / nullif(pap.pap_periodicidad, 0) as costo_mob_indir_paquete,
                           pap.pap_cantidad * pap.costo_sum_dir_paquete * dpp_poblacion / nullif(pap.pap_periodicidad, 0) as costo_sum_dir_paquete,
                           pap.pap_cantidad * pap.costo_sum_indir_paquete * dpp_poblacion / nullif(pap.pap_periodicidad, 0) as costo_sum_indir_paquete,
                           pap.pap_cantidad * pap.costo_ggen_paquete * dpp_poblacion / nullif(pap.pap_periodicidad, 0) as costo_ggen_paquete,
                           pap.pap_cantidad * pap.costo_cdist_paquete * dpp_poblacion / nullif(pap.pap_periodicidad, 0) as costo_cdist_paquete,
                           pap.pap_cantidad * pap.costo_paquete * dpp_poblacion / nullif(pap.pap_periodicidad, 0) as costo_paquete
                    from costos_principal.programas as prg
                    join costos_principal.sedes_programas as sep on (sep.prg_fk_id = prg.prg_pk_id)
                    join costos_principal.sedes as sed on (sed.con_fk_id = sep.con_fk_id and sed.ins_fk_id = sep.ins_fk_id and sed.sed_pk_id = sep.sed_fk_id)
                    join costos_principal.estadios_programas as esp on (esp.prg_fk_id = prg.prg_pk_id)
                    join costos_principal.detalle_pob_programa as dpp on (dpp.prg_fk_id = prg.prg_pk_id and dpp.sed_fk_id = sed.sed_pk_id and dpp.esp_fk_id = esp.esp_pk_id)
                    join (
                        select prg_fk_id,
                               sep_fk_id,
                               esp_fk_id,
                               pap.act_fk_id as paquete,
                               pap_cantidad,
                               pap_periodicidad,
                               sum(cpq_mob_dir_costo) as costo_mob_dir_paquete,
                               sum(cpq_mob_indir_costo) as costo_mob_indir_paquete,
                               sum(cpq_sum_dir_costo) as costo_sum_dir_paquete,
                               sum(cpq_sum_indir_costo) as costo_sum_indir_paquete,
                               sum(cpq_gastos_gen_costo) as costo_ggen_paquete,
                               sum(cpq_costo_dist_costo) as costo_cdist_paquete,
                               sum(cpq.cpq_costo) as costo_paquete
                        from costos_principal.paquetes_programas as pap
                        join costos_principal.costo_paquetes as cpq on (cpq.con_fk_id = ".$contrato." and cpq.ins_fk_id = '".$institucion."' and
                                                                        cpq.cpq_ano = ".$ano." and cpq.cpq_mes = ".$mes." and
                                                                        pap.act_fk_id = cpq.cpq_paquete)
                        where pap.con_fk_id = ".$contrato." and pap.ins_fk_id = '".$institucion."' and pap_ano = ".$ano."
                        group by 1,2,3,4,5,6
                        order by 1,2,3,4
                    ) as pap on (pap.prg_fk_id = prg.prg_pk_id and sep.sep_pk_id = pap.sep_fk_id and pap.esp_fk_id = esp.esp_pk_id)
                    where prg.con_fk_id = ".$contrato." and prg.ins_fk_id = '".$institucion."' and prg_ano = ".$ano." and dpp_mes = ".$mes."
                    order by 5, 6, 7, 8, 9
                ) as cpp
                where cpp.costo_paquete is not null
            ");

            // Costo de los programas
            \DB::statement("
                insert into costos_principal.costo_esperado_programas (con_fk_id, ins_fk_id, cpg_ano, cpg_mes, prg_fk_id, coe_fk_id, sed_fk_id, esp_fk_id,
                                                                       cpg_poblacion, cpg_mob_dir_costo, cpg_mob_indir_costo, cpg_sum_dir_costo, cpg_sum_indir_costo,
                                                                       cpg_gastos_gen_costo, cpg_costo_dist_costo, cpg_costo_programa, cpg_ventas, cpg_resultado,
                                                                       cpg_margen)
                select ".$contrato.", '".$institucion."', ".$ano.", ".$mes.", cpq2.prg_fk_id, cpq2.coe_fk_id, cpq2.sed_fk_id, cpq2.esp_fk_id, dpp_poblacion,
                       cpq2.cpg_mob_dir_costo, cpq2.cpg_mob_indir_costo, cpq2.cpg_sum_dir_costo, cpq2.cpg_sum_indir_costo, cpq2.cpg_gastos_gen_costo,
                       cpq2.cpg_costo_dist_costo, cpq2.cpg_costo_programa, (dpp_poblacion * dpp_valor_unitario) as cpg_ventas,
                       (dpp_poblacion * dpp_valor_unitario) - cpq2.cpg_costo_programa as cpg_resultado,
                       ((dpp_poblacion * dpp_valor_unitario) - cpq2.cpg_costo_programa) / (dpp_poblacion * dpp_valor_unitario) as cpg_margen
                from (
                    select cpq.prg_fk_id, cpq.coe_fk_id, cpq.sed_fk_id, cpq.esp_fk_id, sum(cpg_mob_dir_costo) as cpg_mob_dir_costo,
                           sum(cpg_mob_indir_costo) as cpg_mob_indir_costo, sum(cpg_sum_dir_costo) as cpg_sum_dir_costo,
                           sum(cpg_sum_indir_costo) as cpg_sum_indir_costo, sum(cpg_gastos_gen_costo) as cpg_gastos_gen_costo,
                           sum(cpg_costo_dist_costo) as cpg_costo_dist_costo, sum(cpg_costo_programa) as cpg_costo_programa
                    from (
                        select prg_fk_id, coe_fk_id, sed_fk_id, esp_fk_id, sum(cpa_costo_mob_dir_actividad) as cpg_mob_dir_costo,
                               sum(cpa_costo_mob_indir_actividad) as cpg_mob_indir_costo, sum(cpa_costo_sum_dir_actividad) as cpg_sum_dir_costo,
                               sum(cpa_costo_sum_indir_actividad) as cpg_sum_indir_costo, sum(cpa_costo_ggen_actividad) as cpg_gastos_gen_costo,
                               sum(cpa_costo_cdist_actividad) as cpg_costo_dist_costo, sum(cpa_costo_actividad) as cpg_costo_programa
                        from costos_principal.costo_prg_actividades
                        where con_fk_id = ".$contrato." and ins_fk_id = '".$institucion."' and cpa_ano = ".$ano." and cpa_mes = ".$mes."
                        group by 1, 2, 3, 4
                        union all
                        select prg_fk_id, coe_fk_id, sed_fk_id, esp_fk_id, 0 as cpg_mob_dir_costo, 0 as cpg_mob_indir_costo,
                               sum(cps_costo_suministro) as cpg_sum_dir_costo, 0 as cpg_sum_indir_costo, 0 cpg_gastos_gen_costo,
                               0 as cpg_costo_dist_costo, sum(cps_costo_suministro) as cpg_costo_programa
                        from costos_principal.costo_prg_suministros
                        where con_fk_id = ".$contrato." and ins_fk_id = '".$institucion."' and cps_ano = ".$ano." and cps_mes = ".$mes."
                        group by 1, 2, 3, 4
                        union all
                        select prg_fk_id, coe_fk_id, sed_fk_id, esp_fk_id, sum(cpp_costo_mob_dir_paquete) as cpg_mob_dir_costo,
                               sum(cpp_costo_mob_indir_paquete) as cpg_mob_indir_costo, sum(cpp_costo_sum_dir_paquete) as cpg_sum_dir_costo,
                               sum(cpp_costo_sum_indir_paquete) as cpg_sum_indir_costo, sum(cpp_costo_ggen_paquete) as cpg_gastos_gen_costo,
                               sum(cpp_costo_cdist_paquete) as cpg_costo_dist_costo, sum(cpp_costo_paquete) as cpg_costo_programa
                        from costos_principal.costo_prg_paquetes
                        where con_fk_id = ".$contrato." and ins_fk_id = '".$institucion."' and cpp_ano = ".$ano." and cpp_mes = ".$mes."
                        group by 1, 2, 3, 4
                    ) as cpq
                    group by 1, 2, 3, 4
                    order by 1, 2, 3, 4
                ) as cpq2
                join costos_principal.detalle_pob_programa as dpp on (dpp.prg_fk_id = cpq2.prg_fk_id and dpp.sed_fk_id = cpq2.sed_fk_id and dpp.esp_fk_id = cpq2.esp_fk_id)
                where cpq2.cpg_costo_programa != 0
            ");
        });
    }

    public function obtenerCostoProgramaPrespuestado(Request $request) {
        $contrato = $request->input('con_fk_id');
        $institucion = $request->input('ins_fk_id');
        $ano = $request->input('cpg_ano');
        $mes = $request->input('cpg_mes');

        return \DB::select("
            select coe_nombre_eps as institucion,
                   coe_numero_contrato as contrato,
                   prg_descripcion,
                   esp_codigo || ' - ' || esp_descripcion as estadio,
                   sed_nombre_sede,
                   cpg_poblacion,
                   cpg_mob_dir_costo,
                   cpg_mob_indir_costo,
                   cpg_sum_dir_costo,
                   cpg_sum_indir_costo,
                   cpg_gastos_gen_costo,
                   cpg_costo_dist_costo,
                   cpg_costo_programa
            from costos_principal.costo_esperado_programas as cpg
            join costos_principal.programas as prg on (cpg.prg_fk_id = prg.prg_pk_id)
            join costos_principal.contratos_eps as coe on (cpg.coe_fk_id = coe.coe_pk_id)
            join costos_principal.sedes as sed on (cpg.con_fk_id = sed.con_fk_id and cpg.ins_fk_id = sed.ins_fk_id and cpg.sed_fk_id = sed.sed_pk_id)
            join costos_principal.estadios_programas as esp on (cpg.esp_fk_id = esp.esp_pk_id)
            where cpg.con_fk_id = ".$contrato." and cpg.ins_fk_id = '".$institucion."' and cpg_ano = ".$ano." and cpg_mes = ".$mes."
        ");
    }

    public function consolidarCostoProgramasEjecutado(Request $request) {
        \DB::transaction(function () use($request) {
            $contrato = $request->input('con_fk_id');
            $institucion = $request->input('ins_fk_id');
            $ano = $request->input('cep_ano');
            $mes = $request->input('cep_mes');

            CostoPrgEjActividades::where('con_fk_id', $contrato)
                                 ->where('ins_fk_id', $institucion)
                                 ->where('cea_ano', $ano)
                                 ->where('cea_mes', $mes)
                                 ->delete();

            CostoPrgEjSuministros::where('con_fk_id', $contrato)
                                 ->where('ins_fk_id', $institucion)
                                 ->where('ces_ano', $ano)
                                 ->where('ces_mes', $mes)
                                 ->delete();

            CostoPrgEjPaquetes::where('con_fk_id', $contrato)
                              ->where('ins_fk_id', $institucion)
                              ->where('cep_ano', $ano)
                              ->where('cep_mes', $mes)
                              ->delete();

            CostoEjecutadoProgramas::where('con_fk_id', $contrato)
                                   ->where('ins_fk_id', $institucion)
                                   ->where('cep_ano', $ano)
                                   ->where('cep_mes', $mes)
                                   ->delete();
                                
            // Actividades
            \DB::statement("
                insert into costos_principal.costo_prg_ej_actividades (con_fk_id, ins_fk_id, cea_ano, cea_mes, prg_fk_id, coe_fk_id, sed_fk_id, esp_fk_id, cco_fk_id,
                                                                       act_fk_id, cea_mob_dir_costo, cea_mob_indir_costo, cea_sum_dir_costo, cea_sum_indir_costo,
                                                                       cea_gastos_gen_costo, cea_costo_dist_costo, cea_costo_actividad)
                select ".$contrato.", '".$institucion."', ".$ano.", ".$mes.", cea2.*
                from (
                    select prg_fk_id,
                           coe_fk_id,
                           sed_fk_id,
                           esp_fk_id,
                           cco_fk_id,
                           act_fk_id,
                           sum(costo_mob_dir) as costo_mob_dir,
                           sum(costo_mob_indir) as costo_mob_indir,
                           sum(costo_sum_dir) as costo_sum_dir,
                           sum(costo_sum_indir) as costo_sum_indir,
                           sum(costo_ggen) as costo_ggen,
                           sum(costo_cdist) as costo_cdist,
                           sum(costo_actividad) as costo_actividad
                    from (
                        select ipr.prg_fk_id,
                               ipr.coe_fk_id,
                               cco.sed_fk_id,
                               ipr.esp_fk_id,
                               ipr.cco_fk_id,
                               ipr.act_fk_id,
                               coalesce(cun.cun_mob_directa * ipr_cantidad, 0) as costo_mob_dir,
                               coalesce(cun.cun_mob_indirecta * ipr_cantidad, 0) as costo_mob_indir,
                               coalesce(cun.cun_sum_directo * ipr_cantidad, 0) as costo_sum_dir,
                               coalesce(cun.cun_sum_indirecto * ipr_cantidad, 0) as costo_sum_indir,
                               coalesce(cun.cun_gastos_gen * ipr_cantidad, 0) as costo_ggen,
                               coalesce(cun.cun_costo_dist * ipr_cantidad, 0) as costo_cdist,
                               coalesce(cun.cun_costo_unitario * ipr_cantidad, 0) as costo_actividad
                        from costos_principal.informacion_produccion as ipr
                        join costos_principal.centros_costo as cco on (cco.cco_pk_id = ipr.cco_fk_id)
                        join costos_principal.actividades as act on (act.act_pk_id = ipr.act_fk_id)
                        join costos_principal.costo_unitario as cun on (cun.con_fk_id = ".$contrato." and cun.ins_fk_id = '".$institucion."' and
                                                                        cun.cun_ano = ".$ano." and cun.cun_mes = ".$mes." and
                                                                        cun.cco_fk_id = ipr.cco_fk_id and cun.act_fk_id = ipr.act_fk_id)
                        where ipr.con_fk_id = ".$contrato." and ipr.ins_fk_id = '".$institucion."' and ipr_ano = ".$ano." and ipr_mes = ".$mes." and
                              ipr.prg_fk_id is not null and ipr_actividad = true and act.act_paquete = false
                        order by 1,2,3,4,5,6
                    ) as cea
                    group by 1,2,3,4,5,6
                ) as cea2
                where cea2.costo_actividad != 0
            ");

            // Suministros
            \DB::statement("
                insert into costos_principal.costo_prg_ej_suministros (con_fk_id, ins_fk_id, ces_ano, ces_mes, prg_fk_id, coe_fk_id, sed_fk_id, esp_fk_id,
                                                                       cco_fk_id, sum_fk_id, ces_costo_suministro)
                select ".$contrato.", '".$institucion."', ".$ano.", ".$mes.", ces2.*
                from (
                    select prg_fk_id,
                           coe_fk_id,
                           sed_fk_id,
                           esp_fk_id,
                           cco_fk_id,
                           sum_fk_id,
                           sum(costo_suministro_directo) as costo_sum_dir
                    from (
                        select ipr.prg_fk_id,
                               ipr.coe_fk_id,
                               cco.sed_fk_id,
                               ipr.esp_fk_id,
                               ipr.cco_fk_id,
                               ipr.sum_fk_id,
                               coalesce(des.des_valor_unitario * ipr_cantidad, 0) as costo_suministro_directo
                        from costos_principal.informacion_produccion as ipr
                        join costos_principal.centros_costo as cco on (cco.cco_pk_id = ipr.cco_fk_id)
                        join costos_principal.detalle_suministros as des on (des.con_fk_id = ".$contrato." and des.ins_fk_id = '".$institucion."' and
                                                                             des.des_ano = ".$ano." and des.des_mes = ".$mes." and
                                                                             des.cco_fk_id = ipr.cco_fk_id and des.sum_fk_id = ipr.sum_fk_id)
                        where ipr.con_fk_id = ".$contrato." and ipr.ins_fk_id = '".$institucion."' and ipr_ano = ".$ano." and ipr_mes = ".$mes." and
                              ipr.prg_fk_id is not null and ipr.ipr_actividad = false
                        order by 1,2,3,4,5,6
                    ) as ces
                    group by 1,2,3,4,5,6
                ) as ces2
                where ces2.costo_sum_dir != 0
            ");

            // Paquetes
            \DB::statement("
                insert into costos_principal.costo_prg_ej_paquetes (con_fk_id, ins_fk_id, cep_ano, cep_mes, prg_fk_id, coe_fk_id, sed_fk_id, esp_fk_id, cco_fk_id,
                                                                    act_fk_id, cep_mob_dir_costo, cep_mob_indir_costo, cep_sum_dir_costo, cep_sum_indir_costo,
                                                                    cep_gastos_gen_costo, cep_costo_dist_costo, cep_costo_actividad)
                select ".$contrato.", '".$institucion."', ".$ano.", ".$mes.", cep2.*
                from (
                    select prg_fk_id,
                           coe_fk_id,
                           sed_fk_id,
                           esp_fk_id,
                           cco_fk_id,
                           act_fk_id,
                           sum(costo_mob_dir) as costo_mob_dir,
                           sum(costo_mob_indir) as costo_mob_indir,
                           sum(costo_sum_dir) as costo_sum_dir,
                           sum(costo_sum_indir) as costo_sum_indir,
                           sum(costo_ggen) as costo_ggen,
                           sum(costo_cdist) as costo_cdist,
                           sum(costo_paquete) as costo_paquete
                    from (
                        select ipr.prg_fk_id,
                               ipr.coe_fk_id,
                               cco.sed_fk_id,
                               ipr.esp_fk_id,
                               ipr.cco_fk_id,
                               ipr.act_fk_id,
                               coalesce(cpq.cpq_mob_dir_costo * ipr_cantidad, 0) as costo_mob_dir,
                               coalesce(cpq.cpq_mob_indir_costo * ipr_cantidad, 0) as costo_mob_indir,
                               coalesce(cpq.cpq_sum_dir_costo * ipr_cantidad, 0) as costo_sum_dir,
                               coalesce(cpq.cpq_sum_indir_costo * ipr_cantidad, 0) as costo_sum_indir,
                               coalesce(cpq.cpq_gastos_gen_costo * ipr_cantidad, 0) as costo_ggen,
                               coalesce(cpq.cpq_costo_dist_costo * ipr_cantidad, 0) as costo_cdist,
                               coalesce(cpq.cpq_costo * ipr_cantidad, 0) as costo_paquete
                        from costos_principal.informacion_produccion as ipr
                        join costos_principal.centros_costo as cco on (cco.cco_pk_id = ipr.cco_fk_id)
                        join costos_principal.actividades as act on (act.act_pk_id = ipr.act_fk_id)
                        join costos_principal.costo_paquetes as cpq on (cpq.con_fk_id = ".$contrato." and cpq.ins_fk_id = '".$institucion."' and
                                                                        cpq.cpq_ano = ".$ano." and cpq.cpq_mes = ".$mes." and
                                                                        ipr.act_fk_id = cpq.cpq_paquete)
                        where ipr.con_fk_id = ".$contrato." and ipr.ins_fk_id = '".$institucion."' and ipr_ano = ".$ano." and ipr_mes = ".$mes." and
                              ipr.prg_fk_id is not null and ipr_actividad = true and act.act_paquete = true
                        order by 1,2,3,4,5,6
                    ) as cea
                    group by 1,2,3,4,5,6
                ) as cep2
                where cep2.costo_paquete != 0
            ");

            // Costo ejecutado programas
            \DB::statement("
                insert into costos_principal.costo_ejecutado_programas (con_fk_id, ins_fk_id, cep_ano, cep_mes, prg_fk_id, coe_fk_id, sed_fk_id, esp_fk_id,
                                                                        cep_mob_dir_costo, cep_mob_indir_costo, cep_sum_dir_costo, cep_sum_indir_costo,
                                                                        cep_gastos_gen_costo, cep_costo_dist_costo, cep_costo_programa, cep_ventas, cep_resultado,
                                                                        cep_margen)
                select ".$contrato.", '".$institucion."', ".$ano.", ".$mes.", cep2.*,
                       (dpp_poblacion * dpp_valor_unitario) as cep_ventas,
                       (dpp_poblacion * dpp_valor_unitario) - cep2.cep_costo_programa as cep_resultado,
                       ((dpp_poblacion * dpp_valor_unitario) - cep2.cep_costo_programa) / (dpp_poblacion * dpp_valor_unitario) as cep_margen
                from (
                    select cep.prg_fk_id, cep.coe_fk_id, cep.sed_fk_id, cep.esp_fk_id, sum(cep_mob_dir_costo) as cep_mob_dir_costo,
                           sum(cep_mob_indir_costo) as cep_mob_indir_costo, sum(cep_sum_dir_costo) as cep_sum_dir_costo,
                           sum(cep_sum_indir_costo) as cep_sum_indir_costo, sum(cep_gastos_gen_costo) as cep_gastos_gen_costo,
                           sum(cep_costo_dist_costo) as cep_costo_dist_costo, sum(cep_costo_programa) as cep_costo_programa
                    from (
                        select prg_fk_id, coe_fk_id, sed_fk_id, esp_fk_id, sum(cea_mob_dir_costo) as cep_mob_dir_costo, sum(cea_mob_indir_costo) as cep_mob_indir_costo,
                               sum(cea_sum_dir_costo) as cep_sum_dir_costo, sum(cea_sum_indir_costo) as cep_sum_indir_costo, sum(cea_gastos_gen_costo) as cep_gastos_gen_costo,
                               sum(cea_costo_dist_costo) as cep_costo_dist_costo, sum(cea_costo_actividad) as cep_costo_programa
                        from costos_principal.costo_prg_ej_actividades
                        where con_fk_id = ".$contrato." and ins_fk_id = '".$institucion."' and cea_ano = ".$ano." and cea_mes = ".$mes."
                        group by 1, 2, 3, 4
                        union all
                        select prg_fk_id, coe_fk_id, sed_fk_id, esp_fk_id, 0 as cep_mob_dir_costo, 0 as cep_mob_indir_costo, sum(ces_costo_suministro) as cep_sum_dir_costo,
                               0 as cep_sum_indir_costo, 0 as cep_gastos_gen_costo, 0 as cep_costo_dist_costo, sum(ces_costo_suministro) as cep_costo_programa
                        from costos_principal.costo_prg_ej_suministros
                        where con_fk_id = ".$contrato." and ins_fk_id = '".$institucion."' and ces_ano = ".$ano." and ces_mes = ".$mes."
                        group by 1, 2, 3, 4
                        union all
                        select prg_fk_id, coe_fk_id, sed_fk_id, esp_fk_id, sum(cep_mob_dir_costo) as cep_mob_dir_costo, sum(cep_mob_indir_costo) as cep_mob_indir_costo,
                               sum(cep_sum_dir_costo) as cep_sum_dir_costo, sum(cep_sum_indir_costo) as cep_sum_indir_costo, sum(cep_gastos_gen_costo) as cep_gastos_gen_costo,
                               sum(cep_costo_dist_costo) as cep_costo_dist_costo, sum(cep_costo_actividad) as cep_costo_programa
                        from costos_principal.costo_prg_ej_paquetes
                        where con_fk_id = ".$contrato." and ins_fk_id = '".$institucion."' and cep_ano = ".$ano." and cep_mes = ".$mes."
                        group by 1, 2, 3, 4
                    ) as cep
                    group by 1, 2, 3, 4
                    order by 1, 2, 3, 4
                ) as cep2
                left join costos_principal.detalle_pob_programa as dpp on (dpp.prg_fk_id = cep2.prg_fk_id and dpp.sed_fk_id = cep2.sed_fk_id and dpp.esp_fk_id = cep2.esp_fk_id)
                where cep2.cep_costo_programa != 0
            ");
        });
    }

    public function obtenerCostoProgramaEjecutado(Request $request) {
        $contrato = $request->input('con_fk_id');
        $institucion = $request->input('ins_fk_id');
        $ano = $request->input('cep_ano');
        $mes = $request->input('cep_mes');

        return \DB::select("
            select coe_nombre_eps as institucion,
                   coe_numero_contrato as contrato,
                   prg_descripcion,
                   esp_codigo || ' - ' || esp_descripcion as estadio,
                   sed_nombre_sede,
                   cep_mob_dir_costo,
                   cep_mob_indir_costo,
                   cep_sum_dir_costo,
                   cep_sum_indir_costo,
                   cep_gastos_gen_costo,
                   cep_costo_dist_costo,
                   cep_costo_programa,
                   cep_ventas,
                   cep_resultado,
                   cep_margen
            from costos_principal.costo_ejecutado_programas as cep
            join costos_principal.programas as prg on (cep.prg_fk_id = prg.prg_pk_id)
            join costos_principal.contratos_eps as coe on (cep.coe_fk_id = coe.coe_pk_id)
            join costos_principal.sedes as sed on (cep.con_fk_id = sed.con_fk_id and cep.ins_fk_id = sed.ins_fk_id and cep.sed_fk_id = sed.sed_pk_id)
            join costos_principal.estadios_programas as esp on (cep.esp_fk_id = esp.esp_pk_id)
            where cep.con_fk_id = ".$contrato." and cep.ins_fk_id = '".$institucion."' and cep_ano = ".$ano." and cep_mes = ".$mes."
        ");
    }

    public function obtenerDetalleCostoProgramaEjecutado(Request $request) {
        $contrato = $request->input('con_fk_id');
        $institucion = $request->input('ins_fk_id');
        $ano = $request->input('cep_ano');
        $mes = $request->input('cep_mes');

        return \DB::select("
            select cep.tipo,
                   coe_nombre_eps as institucion,
                   coe_numero_contrato as contrato,
                   prg_descripcion,
                   esp_codigo || ' - ' || esp_descripcion as estadio,
                   sed_nombre_sede,
                   centro_costo,
                   recurso,
                   cea_mob_dir_costo as cep_mob_dir_costo,
                   cea_mob_indir_costo as cep_mob_indir_costo,
                   cea_sum_dir_costo as cep_sum_dir_costo,
                   cea_sum_indir_costo as cep_sum_indir_costo,
                   cea_gastos_gen_costo as cep_gastos_gen_costo,
                   cea_costo_dist_costo as cep_costo_dist_costo,
                   cea_costo_actividad as cep_costo
            from (
                select 'Actividad' as tipo,
                       prg_fk_id,
                       coe_fk_id,
                       cea.sed_fk_id,
                       esp_fk_id,
                       cco_cod_homologado || ' - ' || cco_descripcion as centro_costo,
                       act_codigo || ' - ' || act_descripcion as recurso,
                       cea_mob_dir_costo,
                       cea_mob_indir_costo,
                       cea_sum_dir_costo,
                       cea_sum_indir_costo,
                       cea_gastos_gen_costo,
                       cea_costo_dist_costo,
                       cea_costo_actividad
                from costos_principal.costo_prg_ej_actividades as cea
                join costos_principal.centros_costo as cco on (cco.cco_pk_id = cea.cco_fk_id)
                join costos_principal.actividades as act on (act.act_pk_id = cea.act_fk_id)
                where cea.con_fk_id = ".$contrato." and cea.ins_fk_id = '".$institucion."' and cea_ano = ".$ano." and cea_mes = ".$mes."
                union all
                select 'Suministrio' as tipo,
                       prg_fk_id,
                       coe_fk_id,
                       ces.sed_fk_id,
                       esp_fk_id,
                       cco_cod_homologado || ' - ' || cco_descripcion as centro_costo,
                       sum_codigo || ' - ' || sum_nombre as recurso,
                       0 as ces_mob_dir_costo,
                       0 as ces_mob_indir_costo,
                       ces_costo_suministro as ces_sum_dir_costo,
                       0 as ces_sum_indir_costo,
                       0 as ces_gastos_gen_costo,
                       0 as ces_costo_dist_costo,
                       ces_costo_suministro
                from costos_principal.costo_prg_ej_suministros as ces
                join costos_principal.centros_costo as cco on (cco.cco_pk_id = ces.cco_fk_id)
                join costos_principal.suministros as sum on (sum.sum_pk_id = ces.sum_fk_id)
                where ces.con_fk_id = ".$contrato." and ces.ins_fk_id = '".$institucion."' and ces_ano = ".$ano." and ces_mes = ".$mes."
                union all
                select 'Paquete' as tipo,
                       prg_fk_id,
                       coe_fk_id,
                       cep.sed_fk_id,
                       esp_fk_id,
                       cco_cod_homologado || ' - ' || cco_descripcion as centro_costo,
                       act_codigo || ' - ' || act_descripcion as recurso,
                       cep_mob_dir_costo,
                       cep_mob_indir_costo,
                       cep_sum_dir_costo,
                       cep_sum_indir_costo,
                       cep_gastos_gen_costo,
                       cep_costo_dist_costo,
                       cep_costo_actividad
                from costos_principal.costo_prg_ej_paquetes as cep
                join costos_principal.centros_costo as cco on (cco.cco_pk_id = cep.cco_fk_id)
                join costos_principal.actividades as act on (act.act_pk_id = cep.act_fk_id)
                where cep.con_fk_id = ".$contrato." and cep.ins_fk_id = '".$institucion."' and cep_ano = ".$ano." and cep_mes = ".$mes."
            ) as cep
            join costos_principal.programas as prg on (cep.prg_fk_id = prg.prg_pk_id)
            join costos_principal.contratos_eps as coe on (cep.coe_fk_id = coe.coe_pk_id)
            join costos_principal.sedes as sed on (sed.con_fk_id = ".$contrato." and sed.ins_fk_id = '".$institucion."' and cep.sed_fk_id = sed.sed_pk_id)
            join costos_principal.estadios_programas as esp on (cep.esp_fk_id = esp.esp_pk_id)
        ");
    }

    public function obtenerComparativoCostoProgramadoEjecutado(Request $request) {
        $contrato = $request->input('con_fk_id');
        $institucion = $request->input('ins_fk_id');
        $ano = $request->input('cep_ano');
        $mes = $request->input('cep_mes');

        return \DB::select("
            select prg_descripcion,
                   coe_numero_contrato as contrato,
                   sed_nombre_sede,
                   esp_codigo || ' - ' || esp_descripcion as estadio,
                   cep_costo_programa,
                   cpg_costo_programa
            from costos_principal.costo_ejecutado_programas as cep
            left join (
                select *
                from costos_principal.costo_esperado_programas as cpg
                where cpg.con_fk_id = ".$contrato." and cpg.ins_fk_id = '".$institucion."' and cpg_ano = ".$ano." and cpg_mes = ".$mes."
            ) as cpg on (cep.prg_fk_id = cpg.prg_fk_id and cep.sed_fk_id = cpg.sed_fk_id and cep.esp_fk_id = cpg.esp_fk_id)
            join costos_principal.programas as prg on (cep.prg_fk_id = prg.prg_pk_id)
            join costos_principal.contratos_eps as coe on (cep.coe_fk_id = coe.coe_pk_id)
            join costos_principal.sedes as sed on (cep.con_fk_id = sed.con_fk_id and cep.ins_fk_id = sed.ins_fk_id and cep.sed_fk_id = sed.sed_pk_id)
            join costos_principal.estadios_programas as esp on (cep.esp_fk_id = esp.esp_pk_id)
            where cep.con_fk_id = ".$contrato." and cep.ins_fk_id = '".$institucion."' and cep_ano = ".$ano." and cep_mes = ".$mes."
        ");
    }
}
