<?php

namespace App\Http\Controllers;

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

class MonitorContratosController extends Controller
{
    public function consolidarInformacionContratos(Request $request) {
        \DB::transaction(function() use($request) {
            MonitorContratos::on('costos_principal')
                            ->where('con_fk_id', $request->input('con_fk_id'))
                            ->where('ins_fk_id', $request->input('ins_fk_id'))
                            ->where('mco_ano', $request->input('mco_ano'))
                            ->where('mco_mes', $request->input('mco_mes'))
                            ->delete();

            \DB::select("insert into costos_principal.monitor_contratos (con_fk_id, ins_fk_id, mco_ano, mco_mes, coe_fk_id, cco_fk_id, mco_ventas, mco_costos, mco_resultado)
                        select ".$request->input('con_fk_id').",
                               '".$request->input('ins_fk_id')."',
                               ".$request->input('mco_ano').",
                               ".$request->input('mco_mes').",
                               con.coe_pk_id as coe_fk_id,
                               con.cco_fk_id,
                               con.ventas,
                               (con.costos_actividades + con.costos_suministros) as costos,
                               con.ventas - (con.costos_actividades + con.costos_suministros) as resultado
                        from (
                            select ipr.ipr_mes,
                                   coe.coe_pk_id,
                                   ipr.cco_fk_id,
                                   coalesce(sum(ipr.ipr_cantidad * ipr.ipr_valor), 0) as ventas,
                                   coalesce(sum(ipr.ipr_cantidad * (cun_mob_directa + cun_mob_indirecta + cun_sum_directo + cun_sum_indirecto + cun_gastos_gen + cun_costo_dist)), 0) as costos_actividades,
                                   coalesce(sum(ipr.ipr_cantidad * des.valor_suministro), 0) as costos_suministros
                            from (
                                select ipr_mes,
                                       cco_fk_id,
                                       act_fk_id,
                                       sum_fk_id,
                                       coe_fk_id,
                                       sum(ipr_cantidad) as ipr_cantidad,
                                       sum(ipr_valor) as ipr_valor
                                from costos_principal.informacion_produccion
                                where con_fk_id = ".$request->input('con_fk_id')." and
                                      ins_fk_id = '".$request->input('ins_fk_id')."' and
                                      ipr_ano = ".$request->input('mco_ano')." and
                                      ipr_mes = ".$request->input('mco_mes')."
                                group by 1,2,3,4,5
                            ) as ipr
                            join costos_principal.contratos_eps as coe on (ipr.coe_fk_id = coe.coe_pk_id)
                            left join costos_principal.costo_unitario as cun on (ipr.act_fk_id = cun.act_fk_id and ipr.cco_fk_id = cun.cco_fk_id and
                                                                                 cun.cun_ano = ".$request->input('mco_ano')." and cun.cun_mes = ipr.ipr_mes)
                            left join (
                                select des_mes,
                                       cco_fk_id,
                                       sum_fk_id,
                                       sum(des_valor_unitario) as valor_suministro
                                from costos_principal.detalle_suministros
                                where con_fk_id = ".$request->input('con_fk_id')." and
                                      ins_fk_id = '".$request->input('ins_fk_id')."' and
                                      des_ano = ".$request->input('mco_ano')." and
                                      des_mes = ".$request->input('mco_mes')."
                                group by 1,2,3
                            ) as des on (ipr.cco_fk_id = des.cco_fk_id and ipr.sum_fk_id = des.sum_fk_id and des.des_mes = ipr.ipr_mes)
                            group by 1,2,3
                        ) as con
                        order by 1,2,3");
        });

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

    public function getDetalleMonitorActividades(Request $request) {
        return \DB::select("
            select cco_cod_homologado,
                   cco_descripcion,
                   act_codigo,
                   act_descripcion,
                   coe_pk_id || ' - ' || coe_numero_contrato as contrato,
                   coe_nit_eps || ' - ' || coe_nombre_eps as eps,
                   ipr_cantidad as cantidad_produccion,
                   coalesce(round(ipr_valor::numeric, 2), 0) as valor_unitario_produccion,
                   round(coalesce(ipr_cantidad * ipr_valor, 0)::numeric, 2) as valor_total_produccion,
                   round(costos::numeric, 2) as costo_unitario,
                   round(coalesce(ipr_cantidad * costos, 0)::numeric, 2) as costo_total
            from (
                select ipr.cco_fk_id,
                       ipr.act_fk_id,
                       coe_fk_id,
                       sum(ipr_cantidad) as ipr_cantidad,
                       sum(ipr_valor) as ipr_valor,
                       coalesce(sum((cun_mob_directa + cun_mob_indirecta + cun_sum_directo + cun_sum_indirecto + cun_gastos_gen + cun_costo_dist)), 0) as costos
                from costos_principal.informacion_produccion as ipr
                left join costos_principal.costo_unitario as cun on (ipr.act_fk_id = cun.act_fk_id and ipr.cco_fk_id = cun.cco_fk_id and
                                                                     cun.cun_ano = ".$request->input('moc_ano')." and
                                                                     cun.cun_mes = ".$request->input('moc_mes').")
                where ipr.con_fk_id = ".$request->input('con_fk_id')." and
                      ipr.ins_fk_id = '".$request->input('ins_fk_id')."' and
                      ipr_ano = ".$request->input('moc_ano')." and
                      ipr_mes = ".$request->input('moc_mes')." and
                      ipr.act_fk_id is not null
                group by 1,2,3
            ) as dma
            join costos_principal.centros_costo as cco on (dma.cco_fk_id = cco.cco_pk_id)
            join costos_principal.actividades as act on (dma.act_fk_id = act.act_pk_id)
            join costos_principal.contratos_eps as coe on (dma.coe_fk_id = coe.coe_pk_id)
        ");
    }

    public function getDetalleMonitorSuministros(Request $request) {
        return \DB::select("
            select cco_cod_homologado,
                   cco_descripcion,
                   sum_codigo,
                   sum_nombre,
                   coe_pk_id || ' - ' || coe_numero_contrato as contrato,
                   coe_nit_eps || ' - ' || coe_nombre_eps as eps,
                   ipr_cantidad as cantidad_produccion,
                   round(ipr_valor::numeric, 2) as valor_unitario_produccion,
                   round(coalesce(ipr_cantidad * ipr_valor, 0)::numeric, 2) as valor_total_produccion,
                   coalesce(round(valor_suministro::numeric, 2), 0) as valor_suministro,
                   round(costos::numeric, 2) as costo_unitario,
                   round(coalesce(ipr_cantidad * costos, 0)::numeric, 2) as costo_total
            from (
                select ipr.cco_fk_id,
                       ipr.sum_fk_id,
                       coe_fk_id,
                       sum(ipr_cantidad) as ipr_cantidad,
                       sum(ipr_valor) as ipr_valor,
                       sum(des.valor_suministro) as valor_suministro,
                       coalesce(sum(ipr.ipr_cantidad * des.valor_suministro), 0) as costos
                from costos_principal.informacion_produccion as ipr
                left join (
                    select des_mes,
                           cco_fk_id,
                           sum_fk_id,
                           sum(des_valor_unitario) as des_valor,
                           sum(des_valor_unitario) as valor_suministro
                    from costos_principal.detalle_suministros
                    where con_fk_id = ".$request->input('con_fk_id')." and
                          ins_fk_id = '".$request->input('ins_fk_id')."' and
                          des_ano = ".$request->input('moc_ano')." and
                          des_mes = ".$request->input('moc_mes')."
                    group by 1,2,3
                ) as des on (ipr.cco_fk_id = des.cco_fk_id and ipr.sum_fk_id = des.sum_fk_id and des.des_mes = ipr.ipr_mes)
                where ipr.con_fk_id = ".$request->input('con_fk_id')." and
                      ipr.ins_fk_id = '".$request->input('ins_fk_id')."' and
                      ipr_ano = ".$request->input('moc_ano')." and
                      ipr_mes = ".$request->input('moc_mes')." and
                      ipr.sum_fk_id is not null
                group by 1,2,3
            ) as dms
            join costos_principal.centros_costo as cco on (dms.cco_fk_id = cco.cco_pk_id)
            join costos_principal.suministros as sum on (dms.sum_fk_id = sum.sum_pk_id)
            join costos_principal.contratos_eps as coe on (dms.coe_fk_id = coe.coe_pk_id)
        ");
    }

    public function getAnalisisResultadoMargen(Request $request) {
        $cco = $request->input('cco_fk_id');

        return \DB::select("select mco.*,
                                   coalesce(mco.mco_resultado / nullif(mco.mco_ventas, 0), 0) as mco_margen
                            from (
                                select mco.mco_mes,
                                       coe.coe_nit_eps,
                                       coe.coe_nombre_eps,
                                       sum(mco.mco_ventas) as mco_ventas,
                                       sum(mco.mco_costos) as mco_costos,
                                       sum(mco.mco_resultado) as mco_resultado
                                from costos_principal.monitor_contratos as mco
                                join costos_principal.contratos_eps as coe on (mco.coe_fk_id = coe.coe_pk_id)
                                where mco.con_fk_id = ".$request->input('con_fk_id')." and
                                      mco.ins_fk_id = '".$request->input('ins_fk_id')."' and
                                      mco.mco_ano = ".$request->input('mco_ano')." and
                                      mco.mco_mes in(".$request->input('meses').")
                                      ".(intval($cco) === 0 ? '' : (' and mco.cco_fk_id = '.$cco))." 
                                group by 1,2,3
                            ) as mco
                            order by 1");
    }
 
    public function getResumenCostosVentas(Request $request) {
        return \DB::select("select mco_mes,
                                   sum(mco_ventas) as mco_ventas,
                                   sum(mco_costos) as mco_costos,
                                   sum(mco_resultado) as mco_resultado
                            from costos_principal.monitor_contratos
                            where con_fk_id = ".$request->input('con_fk_id')." and
                                  ins_fk_id = '".$request->input('ins_fk_id')."' and
                                  mco_ano = ".$request->input('mco_ano')."
                            group by 1
                            order by 1");
    }

    public function getAnalisisResultadoMargenCco(Request $request) {
        return \DB::select("select mco.*,
                                   coalesce(mco.mco_resultado / nullif(mco.mco_ventas, 0), 0) as mco_margen
                            from (
                                select mco.mco_mes,
                                       coe.coe_nit_eps,
                                       coe.coe_nombre_eps,
                                       cco.cco_pk_id,
                                       cco.cco_cod_homologado || ' - ' || cco.cco_descripcion as centro_costo,
                                       sum(mco.mco_ventas) as mco_ventas,
                                       sum(mco.mco_costos) as mco_costos,
                                       sum(mco.mco_resultado) as mco_resultado
                                from costos_principal.monitor_contratos as mco
                                join costos_principal.contratos_eps as coe on (mco.coe_fk_id = coe.coe_pk_id)
                                join costos_principal.centros_costo as cco on (mco.cco_fk_id = cco.cco_pk_id)
                                where mco.con_fk_id = ".$request->input('con_fk_id')." and
                                      mco.ins_fk_id = '".$request->input('ins_fk_id')."' and
                                      mco.mco_ano = ".$request->input('mco_ano')." and
                                      mco.mco_mes in(".$request->input('meses').")
                                group by 1,2,3,4,5
                            ) as mco
                            order by 1");
    }

    public function getAnalisisEjecucion(Request $request) {
        $cco = $request->input('cco_fk_id');

        return \DB::select("select mco.*,
                                   coalesce(mco.mco_costos / nullif(mco.coe_monto_mensual, 0), 0) as mco_ejecucion
                            from (
                                select mco.mco_mes,
                                       coe.coe_nit_eps,
                                       coe.coe_nombre_eps,
                                       coe.coe_numero_contrato,
                                       coe.coe_monto_mensual,
                                       sum(mco.mco_costos) as mco_costos
                                from costos_principal.monitor_contratos as mco
                                join costos_principal.contratos_eps as coe on (mco.coe_fk_id = coe.coe_pk_id)
                                where mco.con_fk_id = ".$request->input('con_fk_id')." and
                                      mco.ins_fk_id = '".$request->input('ins_fk_id')."' and
                                      mco.mco_ano = ".$request->input('mco_ano')." and
                                      mco.mco_mes in (".$request->input('meses').")
                                      ".(intval($cco) === 0 ? '' : (' and mco.cco_fk_id = '.$cco))." 
                                group by 1,2,3,4,5
                            ) as mco
                            order by 1");
    }
}
