<?php

namespace App\Http\Controllers;

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

class CostosIngresosController extends Controller
{
    public function getBalanceGeneral(Request $request) {
        $sede = $request->input('sed_fk_id');

        if ($sede === "0") {
            return \DB::select("select coi_mes,
                                       sum(coi_costo) as coi_costo,
                                       sum(coi_ingresos) as coi_ingresos,
                                       sum(coi_ingresos) - sum(coi_costo) as coi_resultado,
                                       coalesce((sum(coi_ingresos) - sum(coi_costo)) / nullif(sum(coi_ingresos), 0), 0) as coi_margen
                                from costos_principal.costos_ingresos
                                where con_fk_id = ".$request->input('con_fk_id')." and
                                      ins_fk_id = '".$request->input('ins_fk_id')."' and
                                      coi_ano = ".$request->input('coi_ano')." and
                                      coi_mes in(".$request->input('meses').")
                                group by 1
                                order by 1");
        } else {
            return \DB::select("select coi_mes,
                                       sum(coi_costo) as coi_costo,
                                       sum(coi_ingresos) as coi_ingresos,
                                       sum(coi_ingresos) - sum(coi_costo) as coi_resultado,
                                       coalesce((sum(coi_ingresos) - sum(coi_costo)) / nullif(sum(coi_ingresos), 0), 0) as coi_margen
                                from costos_principal.costos_ingresos as coi
                                join costos_principal.centros_costo on (cco_fk_id = cco_pk_id)
                                where coi.con_fk_id = ".$request->input('con_fk_id')." and
                                      coi.ins_fk_id = '".$request->input('ins_fk_id')."' and
                                      coi_ano = ".$request->input('coi_ano')." and
                                      coi_mes in(".$request->input('meses').") and
                                      sed_fk_id = ".$sede."
                                group by 1
                                order by 1");
        }
    }

    public function getBalanceGeneralAcumulado(Request $request) {
        return \DB::select("select sum(coi_costo) as coi_costo,
                                   sum(coi_ingresos) as coi_ingresos,
                                   sum(coi_ingresos) - sum(coi_costo) as coi_resultado,
                                   coalesce((sum(coi_ingresos) - sum(coi_costo)) / nullif(sum(coi_ingresos), 0), 0) as coi_margen
                            from costos_principal.costos_ingresos
                            where con_fk_id = ".$request->input('con_fk_id')." and
                                    ins_fk_id = '".$request->input('ins_fk_id')."' and
                                    coi_ano = ".$request->input('coi_ano')." and
                                    coi_mes in(".$request->input('meses').")");
    }

    public function getBalanceSedes(Request $request) {
        return \DB::select("select coi.coi_mes,
                                   ser.ser_nombre_sede as ser_nombre_sede,
                                   sum(coi.coi_costo) as coi_costo,
                                   sum(coi.coi_ingresos) as coi_ingresos,
                                   sum(coi.coi_ingresos) - sum(coi.coi_costo) as coi_resultado,
                                   coalesce((sum(coi.coi_ingresos) - sum(coi.coi_costo)) / nullif(sum(coi.coi_ingresos), 0), 0) as coi_margen
                            from costos_principal.costos_ingresos as coi
                            join costos_principal.centros_costo as cco on (coi.cco_fk_id = cco.cco_pk_id)
                            join costos_principal.sedes_reps as ser on (cco.sed_fk_id = ser.ser_pk_id)
                            where coi.con_fk_id = ".$request->input('con_fk_id')." and
                                  coi.ins_fk_id = '".$request->input('ins_fk_id')."' and
                                  coi_ano = ".$request->input('coi_ano')." and
                                  coi_mes in(".$request->input('meses').")
                            group by 1,2
                            order by 1,2");
    }

    public function getBalanceSedesAcumulado(Request $request) {
        return \DB::select("select ser.ser_nombre_sede as ser_nombre_sede,
                                   sum(coi.coi_costo) as coi_costo,
                                   sum(coi.coi_ingresos) as coi_ingresos,
                                   sum(coi.coi_ingresos) - sum(coi.coi_costo) as coi_resultado,
                                   coalesce((sum(coi.coi_ingresos) - sum(coi.coi_costo)) / nullif(sum(coi.coi_ingresos), 0), 0) as coi_margen
                            from costos_principal.costos_ingresos as coi
                            join costos_principal.centros_costo as cco on (coi.cco_fk_id = cco.cco_pk_id)
                            join costos_principal.sedes_reps as ser on (cco.sed_fk_id = ser.ser_pk_id)
                            where coi.con_fk_id = ".$request->input('con_fk_id')." and
                                  coi.ins_fk_id = '".$request->input('ins_fk_id')."' and
                                  coi_ano = ".$request->input('coi_ano')." and
                                  coi_mes in(".$request->input('meses').")
                            group by 1
                            order by 1");
    }

    public function getBalanceCentrosUtilidad(Request $request) {
        return \DB::select("select coi.coi_mes,
                                   cut.cut_codigo || ' - ' || cut.cut_descripcion  as centro_utilidad,
                                   sum(coi.coi_costo) as coi_costo,
                                   sum(coi.coi_ingresos) as coi_ingresos,
                                   sum(coi.coi_ingresos) - sum(coi.coi_costo) as coi_resultado,
                                   coalesce((sum(coi.coi_ingresos) - sum(coi.coi_costo)) / nullif(sum(coi.coi_ingresos), 0), 0) as coi_margen
                            from costos_principal.costos_ingresos as coi
                            join costos_principal.centros_costo as cco on (coi.cco_fk_id = cco.cco_pk_id)
                            join costos_principal.centros_utilidad as cut on (cco.cut_fk_id = cut.cut_pk_id)
                            where coi.con_fk_id = ".$request->input('con_fk_id')." and
                                coi.ins_fk_id = '".$request->input('ins_fk_id')."' and
                                coi_ano = ".$request->input('coi_ano')." and
                                coi_mes in(".$request->input('meses').")
                            group by 1,2
                            order by 1,2");
    }

    public function getBalanceCentrosUtilidadAcumulado(Request $request) {
        return \DB::select("select cut.cut_codigo || ' - ' || cut.cut_descripcion  as centro_utilidad,
                                   sum(coi.coi_costo) as coi_costo,
                                   sum(coi.coi_ingresos) as coi_ingresos,
                                   sum(coi.coi_ingresos) - sum(coi.coi_costo) as coi_resultado,
                                   coalesce((sum(coi.coi_ingresos) - sum(coi.coi_costo)) / nullif(sum(coi.coi_ingresos), 0), 0) as coi_margen
                            from costos_principal.costos_ingresos as coi
                            join costos_principal.centros_costo as cco on (coi.cco_fk_id = cco.cco_pk_id)
                            join costos_principal.centros_utilidad as cut on (cco.cut_fk_id = cut.cut_pk_id)
                            where coi.con_fk_id = ".$request->input('con_fk_id')." and
                                coi.ins_fk_id = '".$request->input('ins_fk_id')."' and
                                coi_ano = ".$request->input('coi_ano')." and
                                coi_mes in(".$request->input('meses').")
                            group by 1
                            order by 1");
    }

    public function getBalanceCentrosCosto(Request $request) {
        return \DB::select("select coi.coi_mes,
                                   cco.cco_cod_homologado || ' - ' || cco.cco_descripcion  as centro_costo,
                                   sum(cpr.cpr_total) as coi_costo_primario,
	                               sum(cot.cot_costo_total) as coi_costo_total,
                                   sum(coi.coi_costo) as coi_costo,
                                   sum(coi.coi_ingresos) as coi_ingresos,
                                   sum(coi.coi_ingresos) - sum(coi.coi_costo) as coi_resultado,
                                   coalesce((sum(coi.coi_ingresos) - sum(coi.coi_costo)) / nullif(sum(coi.coi_ingresos), 0), 0) as coi_margen
                            from costos_principal.costos_ingresos as coi
                            join costos_principal.centros_costo as cco on (coi.cco_fk_id = cco.cco_pk_id)
                            join costos_principal.costo_primario as cpr on (coi.cco_fk_id = cpr.cco_fk_id and cpr_ano = ".$request->input('coi_ano')." and cpr_mes = coi_mes)
                            join costos_principal.costo_total as cot on (coi.cco_fk_id = cot.cco_fk_id and cot_ano = ".$request->input('coi_ano')." and cot_mes = coi_mes)
                            where coi.con_fk_id = ".$request->input('con_fk_id')." and
                                  coi.ins_fk_id = '".$request->input('ins_fk_id')."' and
                                  coi_ano = ".$request->input('coi_ano')." and
                                  coi_mes in(".$request->input('meses').")
                            group by 1,2
                            order by 1,2");
    }

    public function getBalanceCentrosCostoAcumulado(Request $request) {
        return \DB::select("select cco.cco_cod_homologado || ' - ' || cco.cco_descripcion  as centro_costo,
                                   sum(cpr.cpr_total) as coi_costo_primario,
	                               sum(cot.cot_costo_total) as coi_costo_total,
                                   sum(coi.coi_costo) as coi_costo,
                                   sum(coi.coi_ingresos) as coi_ingresos,
                                   sum(coi.coi_ingresos) - sum(coi.coi_costo) as coi_resultado,
                                   coalesce((sum(coi.coi_ingresos) - sum(coi.coi_costo)) / nullif(sum(coi.coi_ingresos), 0), 0) as coi_margen
                            from costos_principal.costos_ingresos as coi
                            join costos_principal.centros_costo as cco on (coi.cco_fk_id = cco.cco_pk_id)
                            join costos_principal.costo_primario as cpr on (coi.cco_fk_id = cpr.cco_fk_id and cpr_ano = ".$request->input('coi_ano')." and cpr_mes = coi_mes)
                            join costos_principal.costo_total as cot on (coi.cco_fk_id = cot.cco_fk_id and cot_ano = ".$request->input('coi_ano')." and cot_mes = coi_mes)
                            where coi.con_fk_id = ".$request->input('con_fk_id')." and
                                  coi.ins_fk_id = '".$request->input('ins_fk_id')."' and
                                  coi_ano = ".$request->input('coi_ano')." and
                                  coi_mes in(".$request->input('meses').")
                            group by 1
                            order by 1");
    }
}
