<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

class AnalisisCostoVolumenController extends Controller
{
    public function getAnalisisCostoVolumen(Request $request) {
        return \DB::select("select acv2.cot_mes,
                                   acv2.ser_nombre_sede,
                                   acv2.cco_pk_id,
                                   acv2.centro_costo,
                                   acv2.costo_fijo,
                                   acv2.costo_variable,
                                   acv2.ipr_cantidad,
                                   acv2.precio_promedio_un,
                                   acv2.costo_directo_un,
                                   (acv2.precio_promedio_un - acv2.costo_directo_un) as margen_contribucion,
                                   coalesce(acv2.costo_fijo / nullif((acv2.precio_promedio_un - acv2.costo_directo_un), 0), 0) as punto_equilibrio
                            from (
                                select acv.cot_mes,
                                       acv.ser_nombre_sede,
                                       acv.cco_pk_id,
                                       acv.centro_costo,
                                       acv.costo_fijo,
                                       coalesce(acv.costo_variable / nullif(acv.ipr_cantidad, 0), 0) as costo_variable,
                                       acv.ipr_cantidad,
                                       coalesce(acv.mco_ventas / nullif(acv.ipr_cantidad, 0), 0) as precio_promedio_un,
                                       coalesce(acv.costo_variable / nullif(acv.ipr_cantidad, 0), 0) as costo_directo_un
                                from (
                                    select cot.cot_mes,
                                           ser_nombre_sede,
                                           cco.cco_pk_id,
                                           cco.cco_cod_homologado || ' - ' || cco.cco_descripcion as centro_costo,
                                           (sum(cot.cot_cp_fijo) + sum(cot.cot_dist_total)) as costo_fijo,
                                           sum(cot.cot_cp_variable) as costo_variable,
                                           coalesce(sum(ipr.ipr_cantidad), 0) as ipr_cantidad,
                                           coalesce(sum(mco.mco_ventas), 0) as mco_ventas,
                                           sum(cot.cot_cp_directo) as costo_directo
                                    from costos_principal.costo_total as cot
                                    join costos_principal.centros_costo as cco on (cot.cco_fk_id = cco.cco_pk_id)
                                    join costos_principal.sedes_reps as ser on (ser.ser_pk_id = cco.sed_fk_id)
                                    left join ( /* Cantidad de produccion */
                                        select ipr_mes,
                                               ipr.cco_fk_id,
                                               sum(ipr_cantidad) as ipr_cantidad
                                        from costos_principal.informacion_produccion as ipr
                                        join costos_principal.actividades_centro_costo as acc on (
                                            ipr.con_fk_id = acc.con_fk_id and ipr.ins_fk_id = acc.ins_fk_id and
                                            ipr.ipr_ano = acc.acc_ano and ipr.act_fk_id = acc.act_fk_id and
                                            ipr.cco_fk_id = acc.cco_fk_id
                                        )
                                        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('cot_ano')." and
                                              ipr_mes in(".$request->input('meses').") and
                                              ipr.act_fk_id is not null and
                                              acc_suma_produccion = true
                                        group by 1,2
                                    ) as ipr on (cot.cco_fk_id = ipr.cco_fk_id and cot.cot_mes = ipr.ipr_mes)
                                    left join ( /* Ventas (recursos por centro de costo) */
                                        select rcc_mes as mco_mes,
                                               cco_fk_id,
                                               sum(rcc_valor) as mco_ventas
                                        from costos_principal.recursos_centro_costo
                                        where con_fk_id = ".$request->input('con_fk_id')." and
                                              ins_fk_id = '".$request->input('ins_fk_id')."' and
                                              rcc_ano = ".$request->input('cot_ano')." and
                                              rcc_mes in(".$request->input('meses').") and
                                              elm_fk_id = 1
                                        group by 1,2
                                    ) as mco on (cot.cco_fk_id = mco.cco_fk_id and cot.cot_mes = mco.mco_mes)
                                    where cot.con_fk_id = ".$request->input('con_fk_id')." and
                                          cot.ins_fk_id = '".$request->input('ins_fk_id')."' and
                                          cot_ano = ".$request->input('cot_ano')." and
                                          cot_mes in(".$request->input('meses').")
                                    group by 1,2,3,4
                                    order by 1,2,4
                                ) as acv
                            ) as acv2");
    }
}
