<?php

namespace App\Http\Controllers;

use App\Models\TasaRecursosIndirectos;
use Illuminate\Http\Request;

class TasaRecursosIndirectosController extends Controller
{
    public function consolidarTasaRecursosIndirectos(Request $request) {
        $contrato = $request->input('con_fk_id');
        $institucion = $request->input('ins_fk_id');
        $ano = $request->input('tri_ano');
        $mes = $request->input('tri_mes');

        \DB::transaction(function() use($contrato, $institucion, $ano, $mes) {
                // Borrar la informacion consolidada previamente
                TasaRecursosIndirectos::on('costos_principal')
                                      ->where('con_fk_id', $contrato)
                                      ->where('ins_fk_id', $institucion)
                                      ->where('tri_ano', $ano)
                                      ->where('tri_mes', $mes)
                                      ->delete();

                // Consolidar los recursos por centro de costo
                \DB::select("insert into costos_principal.tasa_recursos_indirectos (con_fk_id, ins_fk_id, tri_ano, tri_mes, cco_fk_id,
                                                                                    tri_tasa_suministros, tri_tasa_mano_obra, tri_tasa_gastos_gen,
                                                                                    tri_tasa_costo, tri_mano_obra_directa, tri_mano_obra_indirecta,
                                                                                    tri_sumin_directos, tri_sumin_indirectos, tri_gast_generales,
                                                                                    tri_costo_dist)
                            select ".$contrato.",
                                    '".$institucion."',
                                    ".$ano.",
                                    ".$mes.",
                                    irc.cco_fk_id,
                                    case when scc2.tasa_suministros_scc is null then irc.tasa_suministros else scc2.tasa_suministros_scc end as tasa_suministros,
                                    case when mcc2.tasa_mano_obra_mcc is null then irc.tasa_mano_obra else mcc2.tasa_mano_obra_mcc end as tasa_mano_obra,
                                    irc.tasa_gastos_generales,
                                    coalesce(cod.costo_distribuido / nullif(irc.mob_directo + irc.sum_directo, 0), 0) * 100 as tasa_costo,
                                    irc.mob_directo,
                                    irc.mob_indirecto,
                                    irc.sum_directo,
                                    irc.sum_indirecto,
                                    irc.gge_indirecto,
                                    cod.costo_distribuido
                            from (
                                select smg.cco_fk_id,
                                       coalesce(smg.sum_indirecto / nullif(smg.sum_directo, 0), 0) * 100 as tasa_suministros,
                                       coalesce(smg.mob_indirecto / nullif(smg.mob_directo, 0), 0) * 100 as tasa_mano_obra,
                                       coalesce(smg.gge_indirecto / nullif(smg.mob_directo + smg.sum_directo, 0), 0) * 100 as tasa_gastos_generales,
                                       smg.mob_directo as mob_directo,
                                       smg.mob_indirecto as mob_indirecto,
                                       smg.sum_directo as sum_directo,
                                       smg.sum_indirecto as sum_indirecto,
                                       smg.gge_indirecto as gge_indirecto
                                from (
                                    select rcc.cco_fk_id,
                                        coalesce(sum(rcc_valor) filter (where rcc.elm_fk_id = 4 and rcc.tre_fk_id = 1), 0) sum_directo,
                                        coalesce(sum(rcc_valor) filter (where rcc.elm_fk_id = 4 and rcc.tre_fk_id = 2), 0) sum_indirecto,
                                        coalesce(sum(rcc_valor) filter (where rcc.elm_fk_id = 3 and rcc.tre_fk_id = 1), 0) mob_directo,
                                        coalesce(sum(rcc_valor) filter (where rcc.elm_fk_id = 3 and rcc.tre_fk_id = 2), 0) mob_indirecto,
                                        coalesce(sum(rcc_valor) filter (where rcc.elm_fk_id = 2 and rcc.tre_fk_id = 2), 0) gge_indirecto
                                    from costos_principal.recursos_centro_costo as rcc
                                    /*join costos_principal.cuentas_contables as cuc on (rcc.cuc_fk_id = cuc.cuc_pk_id)*/
                                    left join costos_principal.centros_costo as cco on (rcc.cco_fk_id = cco.cco_pk_id)
                                    where rcc.con_fk_id = ".$contrato." and
                                          rcc.ins_fk_id = '".$institucion."' and
                                          rcc_ano = ".$ano." and
                                          rcc_mes = ".$mes." and
                                          cco.cco_final = true
                                    group by 1
                                ) as smg
                            ) as irc
                            left join (
                                select cco_fk_id, sum(cot_dist_total) as costo_distribuido
                                from costos_principal.costo_total
                                where con_fk_id = ".$contrato." and
                                      ins_fk_id = '".$institucion."' and
                                      cot_ano = ".$ano." and
                                      cot_mes = ".$mes."
                                group by 1
                            ) as cod on (cod.cco_fk_id = irc.cco_fk_id)
                            left join ( /* Tasa de suministros */
                                select *
                                from (
                                    select scc.cco_fk_id,
                                        sum(des_valor) filter (where scc.tre_fk_id = 2) / nullif(sum(des_valor) filter (where scc.tre_fk_id = 1), 0) * 100 as tasa_suministros_scc
                                    from (
                                        select des.cco_fk_id,
                                            scc.tre_fk_id,
                                            sum(des_cantidad * des_valor_unitario) as des_valor
                                        from costos_principal.detalle_suministros as des
                                        join costos_principal.suministros as sumi on (des.sum_fk_id = sumi.sum_pk_id)
                                        join costos_principal.grupo_suministros as grs on (sumi.grs_fk_id = grs.grs_pk_id)
                                        left join costos_principal.suministros_centro_costo as scc on (
                                            scc.con_fk_id = ".$contrato." and scc.ins_fk_id = '".$institucion."' and scc.scc_ano = ".$ano." and
                                            scc.cco_fk_id = des.cco_fk_id and scc.grs_fk_id = grs_pk_id
                                        )
                                        where des.con_fk_id = ".$contrato." and
                                            des.ins_fk_id = '".$institucion."' and
                                            des_ano = ".$ano." and
                                            des_mes = ".$mes." and
                                            tre_fk_id is not null
                                        group by 1,2
                                    ) as scc
                                    group by 1
                                ) as sca
                                where sca.tasa_suministros_scc is not null
                            ) as scc2 on (irc.cco_fk_id = scc2.cco_fk_id)
                            left join ( /* Tasa de mano de obra */
                                select *
                                from (
                                    select dmo.cco_fk_id,
                                           sum(dmo.costo) filter (where mcc.tre_fk_id = 2) / nullif(sum(dmo.costo) filter (where mcc.tre_fk_id = 1), 0) * 100 as tasa_mano_obra_mcc
                                    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."
                                    ) as dmo
                                    left join costos_principal.mano_obra_centro_costo as mcc on (
                                        mcc.con_fk_id = ".$contrato." and mcc.ins_fk_id = '".$institucion."' and mcc.mcc_ano = ".$ano." and
                                        mcc.cco_fk_id = dmo.cco_fk_id and mcc.mob_fk_id = dmo.mob_fk_id
                                    )
                                    group by 1
                                ) as mca
                                where mca.tasa_mano_obra_mcc is not null
                            ) as mcc2 on (irc.cco_fk_id = mcc2.cco_fk_id)");
        });

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

    public function getTasaRecursosIndirectos(Request $request) {
        return TasaRecursosIndirectos::on('costos_principal')
                                     ->where('con_fk_id', $request->input('con_fk_id'))
                                     ->where('ins_fk_id', $request->input('ins_fk_id'))
                                     ->where('tri_ano', $request->input('tri_ano'))
                                     ->where('tri_mes', $request->input('tri_mes'))
                                     ->get()->toArray();
    }
}
