public function objectiveKpi($id) { if (Auth::guest()) { return view('login'); } else { $obj_selected = \Ermtool\Objective::name($id); $kpi = array(); $kpiquery = DB::table('kpi')->join('kpi_objective', 'kpi_objective.kpi_id', '=', 'kpi.id')->join('objectives', 'objectives.id', '=', 'kpi_objective.objective_id')->where('objectives.id', '=', $id)->select('kpi.id', 'kpi.name', 'kpi.description', 'kpi.stakeholder_id', 'kpi.goal', 'objectives.name as obj_name', 'objectives.perspective as perspective', 'kpi.measurement_unit', 'kpi.periodicity', 'kpi.calculation_method')->get(); $i = 0; foreach ($kpiquery as $k) { //hacemos ciclo para obtener última medición de cada kpi //para esto primero obtenemos la fecha de la última medición (si es que hay mediciones) $max_date = DB::table('kpi_measurements')->where('kpi_id', '=', $k->id)->where('status', '=', 1)->max('updated_at'); //ahora si es que hay fecha, obtenemos datos de última eval if ($max_date) { $last_eval = DB::table('kpi_measurements')->where('kpi_id', '=', $k->id)->where('updated_at', '=', $max_date)->select('value', 'status')->first(); $last_eval_value = $last_eval->value; $last_eval_status = $last_eval->status; $date_last = new DateTime($max_date); $date_last_eval = date_format($date_last, 'd-m-Y'); } else { //Resulta más fácil configurarlo los mensajes aquí que en la vista (en este caso) if (Session::get('languaje') == 'en') { $last_eval_value = "No valid assessments"; $date_last_eval = "No valid assessments"; } else { $last_eval_value = "No hay evaluaciones validadas"; $date_last_eval = "No hay evaluaciones validadas"; } $last_eval_status = NULL; } //vemos si existe eval para validar $id_eval = DB::table('kpi_measurements')->where('kpi_id', '=', $k->id)->where('status', '=', 0)->select('id')->first(); if ($id_eval) { $status = TRUE; } else { $status = FALSE; } $stakeholder = DB::table('stakeholders')->where('id', '=', $k->stakeholder_id)->select('name', 'surnames')->first(); if ($stakeholder) { $stake = $stakeholder->name . ' ' . $stakeholder->surnames; } else { if (Session::get('languaje') == 'en') { $stake = "No responsable assigned"; } else { $stake = "No se ha asignado responsable"; } } if ($k->goal == NULL) { if (Session::get('languaje') == 'en') { $goal = "No defined goals"; } else { $goal = "No se han definido metas"; } } else { $goal = $k->goal; } $kpi[$i] = ['id' => $k->id, 'name' => $k->name, 'description' => $k->description, 'stakeholder' => $stake, 'last_eval' => $last_eval_value, 'date_last_eval' => $date_last_eval, 'status' => $last_eval_status, 'goal' => $goal, 'objective' => $k->obj_name, 'periodicity' => $k->periodicity, 'calculation_method' => $k->calculation_method, 'measurement_unit' => $k->measurement_unit, 'status_validate' => $status]; $i += 1; } if (Session::get('languaje') == 'en') { return view('en.gestion_estrategica.kpi_objective', ['obj_selected' => $obj_selected, 'kpi' => $kpi, 'obj_id' => $id]); } else { return view('gestion_estrategica.kpi_objective', ['obj_selected' => $obj_selected, 'kpi' => $kpi, 'obj_id' => $id]); } } }