<?php

namespace App\Http\Controllers;

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

class ValorMinutoController extends Controller
{
    public function consolidarValorMinuto(Request $request) {
        $contrato = $request->input('con_fk_id');
        $institucion = $request->input('ins_fk_id');
        $ano = $request->input('vam_ano');
        $mes = $request->input('vam_mes');

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

                // Consolidar el valor por minuto
                \DB::select("insert into costos_principal.valor_minuto (con_fk_id, ins_fk_id, vam_ano, vam_mes, mob_fk_id, cco_fk_id, vam_valor_minuto)
                    select ".$contrato.",
                           '".$institucion."',
                           ".$ano.",
                           ".$mes.",
                           vc.mob_fk_id,
                           vc.cco_fk_id,
                           coalesce(avg((nullif(vc.costo, 0) / nullif(vc.imo_horas, 0) / 60)), 0) as valor_minuto
                    from (
                        select cco_fk_id,
                               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 vc
                    group by 1,2,3,4,5,6");
        });

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

    public function getValorMinuto(Request $request) {
        return ValorMinuto::on('costos_principal')
                          ->where('con_fk_id', $request->input('con_fk_id'))
                          ->where('ins_fk_id', $request->input('ins_fk_id'))
                          ->where('vam_ano', $request->input('vam_ano'))
                          ->where('vam_mes', $request->input('vam_mes'))
                          ->get()->toArray();
    }

    public function getValorMinutoCargo(Request $request) {
        return ValorMinuto::on('costos_principal')
                          ->selectRaw('mob_fk_id, avg(vam_valor_minuto) as vam_valor_minuto')
                          ->where('con_fk_id', $request->input('con_fk_id'))
                          ->where('ins_fk_id', $request->input('ins_fk_id'))
                          ->where('vam_ano', $request->input('vam_ano'))
                          ->where('vam_mes', $request->input('vam_mes'))
                          ->groupBy('mob_fk_id')
                          ->get()->toArray();
    }

    public function getValorMinutoCentroCosto(Request $request) {
        return ValorMinuto::on('costos_principal')
                          ->selectRaw('cco_fk_id, avg(vam_valor_minuto) as vam_valor_minuto')
                          ->where('con_fk_id', $request->input('con_fk_id'))
                          ->where('ins_fk_id', $request->input('ins_fk_id'))
                          ->where('vam_ano', $request->input('vam_ano'))
                          ->where('vam_mes', $request->input('vam_mes'))
                          ->groupBy('cco_fk_id')
                          ->get()->toArray();
    }

    public function getDistribucionHorasCentro(Request $request) {
        $contrato = $request->input('con_fk_id');
        $institucion = $request->input('ins_fk_id');
        $ano = $request->input('vam_ano');
        $mes = $request->input('vam_mes');

        return \DB::select("select cco_cod_homologado || ' - ' || cco_descripcion as centro_costo,
                                   mob_cargo as cargo,
                                   imo_horas as horas,
                                   imo_valor as 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_distribuido
                            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)
                            join costos_principal.centros_costo as cco on (cco_pk_id = cco_fk_id)
                            join costos_principal.mano_obra as mob on (imo.mob_fk_id = mob_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,4 asc");
    }

    public function getRankingValorMinuto(Request $request) {
        $sede = $request->input('sed_fk_id');

        if ($sede === "0") {
            return \DB::select("select vam.vam_mes,
                                    mob.mob_cargo,
                                    vam_valor_minuto,
                                    vam.ranking
                                from (
                                    select cam.vam_mes,
                                        cam.mob_fk_id,
                                        cam.vam_valor_minuto,
                                        row_number() over (partition by cam.vam_mes order by vam_mes, vam_valor_minuto desc) as ranking
                                    from (
                                        select distinct vam_mes,
                                                        mob_fk_id,
                                                        cast(avg(vam_valor_minuto) as decimal(20,2)) as vam_valor_minuto
                                        from costos_principal.valor_minuto
                                        where con_fk_id = ".$request->input('con_fk_id')." and
                                            ins_fk_id = '".$request->input('ins_fk_id')."' and
                                            vam_ano = ".$request->input('vam_ano')."
                                        group by 1,2
                                        order by 1 asc, 3 desc
                                    ) as cam
                                ) as vam
                                join costos_principal.mano_obra as mob on (vam.mob_fk_id = mob.mob_pk_id)
                                where vam.ranking <= 3
                                order by 1 asc, 4 asc");
        } else {
            return \DB::select("select vam.vam_mes,
                                    mob.mob_cargo,
                                    vam_valor_minuto,
                                    vam.ranking
                                from (
                                    select cam.vam_mes,
                                           cam.mob_fk_id,
                                           cam.vam_valor_minuto,
                                           row_number() over (partition by cam.vam_mes order by vam_mes, vam_valor_minuto desc) as ranking
                                    from (
                                        select distinct vam_mes,
                                                        mob_fk_id,
                                                        cast(avg(vam_valor_minuto) as decimal(20,2)) as vam_valor_minuto
                                        from costos_principal.valor_minuto as vam
                                        join costos_principal.centros_costo on (cco_fk_id = cco_pk_id)
                                        where vam.con_fk_id = ".$request->input('con_fk_id')." and
                                              vam.ins_fk_id = '".$request->input('ins_fk_id')."' and
                                              vam_ano = ".$request->input('vam_ano')." and
                                              sed_fk_id = ".$sede."
                                        group by 1,2
                                        order by 1 asc, 3 desc
                                    ) as cam
                                ) as vam
                                join costos_principal.mano_obra as mob on (vam.mob_fk_id = mob.mob_pk_id)
                                where vam.ranking <= 3
                                order by 1 asc, 4 asc");
        }
    }
}
