<?php

namespace App\Http\Controllers;

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

class CostoUnitarioPromedioController extends Controller
{
    public function getCostoUnitarioPromedio(Request $request) {
        return \DB::select("select cot.*,
                                   coalesce(cot.costo_total / nullif(cot.ipr_cantidad, 0), 0) as unitario_promedio
                            from (
                                select cot.cot_mes,
                                       ser_nombre_sede,
                                       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,
                                       sum(cot.cot_costo_total) as costo_total,
                                       coalesce(sum(ipr.ipr_cantidad), 0) as ipr_cantidad
                                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 (
                                    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)
                                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
                                order by 1,2,3
                            ) as cot");
    }
}
