/** * Update the specified resource in storage. * * @param \Illuminate\Http\Request $request * @param int $id * @return \Illuminate\Http\Response */ public function update(Request $request, $id) { if (Auth::guest()) { return view('login'); } else { global $id1; $id1 = $id; //creamos una transacción para cumplir con atomicidad DB::transaction(function () { $riesgo = \Ermtool\Risk::find($GLOBALS['id1']); //vemos si se agrego alguna causa nueva if (isset($_POST['causa_nueva'])) { $new_causa = \Ermtool\Cause::create(['name' => $_POST['causa']]); //guardamos en cause_risk DB::table('cause_risk')->insert(['risk_id' => $riesgo->id, 'cause_id' => $new_causa->id]); } else { if (isset($_POST['cause_id'])) { foreach ($_POST['cause_id'] as $cause_id) { //primero buscamos si es que existe previamente $cause = DB::table('cause_risk')->where('cause_id', '=', $cause_id)->where('risk_id', '=', $riesgo->id)->first(); if (!$cause) { DB::table('cause_risk')->insert(['risk_id' => $riesgo->id, 'cause_id' => $cause_id]); } } } } //vemos si se agrego algún efecto nuevo if (isset($_POST['efecto_nuevo'])) { $new_effect = \Ermtool\Effect::create(['name' => $_POST['efecto']]); //guardamos en cause_risk DB::table('effect_risk')->insert(['risk_id' => $riesgo->id, 'effect_id' => $new_effect->id]); } else { if (isset($_POST['effect_id'])) { foreach ($_POST['effect_id'] as $effect_id) { //primero buscamos si es que existe previamente $effect = DB::table('effect_risk')->where('effect_id', '=', $effect_id)->where('risk_id', '=', $riesgo->id)->first(); if (!$effect) { //insertamos cada causa en cause_risk DB::table('effect_risk')->insert(['risk_id' => $riesgo->id, 'effect_id' => $effect_id]); } } } } //ahora recorreremos todas las causas y efectos de este riesgo, para saber si es que no se borró alguna $causas = DB::table('cause_risk')->where('risk_id', '=', $riesgo->id)->select('cause_id')->get(); foreach ($causas as $cause) { $cont = 0; //si se mantiene en cero, nunca habrán sido iguales, por lo que significa que se habria borrado //ahora recorremos todas las causas que se agregaron para comparar foreach ($_POST['cause_id'] as $cause_add) { if ($cause_add == $cause->cause_id) { $cont += 1; } } if ($cont == 0) { DB::table('cause_risk')->where('risk_id', '=', $riesgo->id)->where('cause_id', '=', $cause->cause_id)->delete(); } } //lo mismo ahora para efectos $efectos = DB::table('effect_risk')->where('risk_id', '=', $riesgo->id)->select('effect_id')->get(); foreach ($efectos as $effect) { $cont = 0; //si se mantiene en cero, nunca habrán sido iguales, por lo que significa que se habria borrado //ahora recorremos todas las causas que se agregaron para comparar foreach ($_POST['effect_id'] as $effect_add) { if ($effect_add == $effect->effect_id) { $cont += 1; } } if ($cont == 0) { DB::table('effect_risk')->where('risk_id', '=', $riesgo->id)->where('effect_id', '=', $effect->effect_id)->delete(); } } if (!isset($_POST['stakeholder_id']) || $_POST['stakeholder_id'] == "") { $stake = NULL; } else { $stake = $_POST['stakeholder_id']; } if ($riesgo->type == 0) { //primero eliminamos relaciones previas DB::table('risk_subprocess')->where('risk_id', '=', $riesgo->id)->delete(); //agregamos en tabla risk_subprocess foreach ($_POST['subprocess_id'] as $subprocess_id) { $subprocess = \Ermtool\Subprocess::find($subprocess_id); $subprocess->risks()->attach($riesgo); } } else { if ($riesgo->type == 1) { //primero eliminamos relaciones previas DB::table('objective_risk')->where('risk_id', '=', $riesgo->id)->delete(); //agregamos en tabla objective_risk foreach ($_POST['objective_id'] as $objective_id) { $objective = \Ermtool\Objective::find($objective_id); $objective->risks()->attach($riesgo); } } } //eliminamos salto de linea del final de cada una de las textarea (en este caso solo descripción) $riesgo->name = $_POST['name']; $riesgo->description = $_POST['description']; $riesgo->expiration_date = $_POST['expiration_date']; $riesgo->type2 = 1; $riesgo->risk_category_id = $_POST['risk_category_id']; $riesgo->expected_loss = $_POST['expected_loss']; $riesgo->stakeholder_id = $stake; $riesgo->save(); if (Session::get('languaje') == 'en') { Session::flash('message', 'Risk successfully updated'); } else { Session::flash('message', 'Riesgo actualizado correctamente'); } }); return Redirect::to('riesgos.index2?organization_id=' . $_POST['org_id']); } }
public function generarHeatmap(Request $request) { if (Auth::guest()) { return view('login'); } else { //print_r($_POST); //Nombre y descripción de la encuesta u organización $nombre = ""; $descripcion = ""; //inherente $prom_proba_in = array(); $prom_criticidad_in = array(); //controlado $prom_proba_ctrl = array(); $prom_criticidad_ctrl = array(); $riesgo_temp = array(); $riesgos = array(); $i = 0; $ano = $_GET['ano']; if ($_GET['mes'] == NULL) { $mes = "12"; } else { $mes = $_GET['mes']; } //obtenemos nombre y descripción de organización $datos = DB::table('organizations')->where('id', $_GET['organization_id'])->select('name', 'description')->get(); foreach ($datos as $datos) { $nombre = $datos->name; $descripcion = $datos->description; } if ($_GET['kind'] == 0) { //---- consulta multiples join para obtener los subprocesos evaluados relacionados a la organización ----// //para riesgos inherente $evaluations = DB::table('evaluation_risk')->join('evaluations', 'evaluations.id', '=', 'evaluation_risk.evaluation_id')->join('risk_subprocess', 'risk_subprocess.id', '=', 'evaluation_risk.risk_subprocess_id')->join('organization_subprocess', 'organization_subprocess.subprocess_id', '=', 'risk_subprocess.subprocess_id')->join('risks', 'risks.id', '=', 'risk_subprocess.risk_id')->whereNotNull('evaluation_risk.risk_subprocess_id')->where('organization_subprocess.organization_id', '=', $_GET['organization_id'])->where('evaluations.updated_at', '<=', date($ano . '-' . $mes) . '-31 23:59:59')->where('evaluations.consolidation', '=', 1)->select('evaluation_risk.risk_subprocess_id as risk_id', 'risks.id as risk')->groupBy('risks.id')->get(); foreach ($evaluations as $evaluation) { //obtenemos promedio de probabilidad e impacto (INHERENTE Y CONTROLADO) $updated_at_in = DB::table('evaluation_risk')->join('evaluations', 'evaluations.id', '=', 'evaluation_risk.evaluation_id')->where('evaluation_risk.risk_subprocess_id', $evaluation->risk_id)->where('evaluations.consolidation', '=', 1)->where('evaluations.type', '=', 1)->where('evaluations.updated_at', '<=', date($ano . '-' . $mes . '-31 23:59:59'))->max('evaluations.updated_at'); if ($_GET['kind2'] == 1) { //ACTUALIZACIÓN 22-11-16: Obtendremos los riesgos controlados a través de la tabla controlled_risk sólo para la organización y el tipo seleccionado $updated_at_ctrl = DB::table('controlled_risk')->join('risk_subprocess', 'risk_subprocess.id', '=', 'controlled_risk.risk_subprocess_id')->join('organization_subprocess', 'organization_subprocess.subprocess_id', '=', 'risk_subprocess.subprocess_id')->where('organization_subprocess.organization_id', '=', $_GET['organization_id'])->where('controlled_risk.risk_subprocess_id', '=', $evaluation->risk_id)->where('controlled_risk.created_at', '<=', date($ano . '-' . $mes . '-31 23:59:59'))->max('controlled_risk.created_at'); } $proba_impacto_in = DB::table('evaluation_risk')->join('evaluations', 'evaluations.id', '=', 'evaluation_risk.evaluation_id')->where('evaluations.updated_at', '=', $updated_at_in)->where('evaluation_risk.risk_subprocess_id', '=', $evaluation->risk_id)->select('evaluation_risk.avg_probability', 'evaluation_risk.avg_impact')->first(); //proba controlado (si es que hay) if (isset($updated_at_ctrl) && $updated_at_ctrl != NULL) { //ACTUALIZACIÓN 01-12: Obtenemos valor de riesgo controlado de controlled_risk_criteria, según la evaluación de controlled_risk $eval = DB::table('controlled_risk')->where('controlled_risk.risk_subprocess_id', '=', $evaluation->risk_id)->where('controlled_risk.created_at', '=', $updated_at_ctrl)->select('results')->first(); //obtenemos valor de evaluación controlada, para este resultado y con los valores del riesgo inherente $proba_ctrl = DB::table('controlled_risk_criteria')->where('dim_eval', '=', 1)->where('eval_in_risk', '=', $proba_impacto_in->avg_probability)->where('control_evaluation', '=', $eval->results)->select('eval_ctrl_risk as eval')->first(); $impacto_ctrl = DB::table('controlled_risk_criteria')->where('dim_eval', '=', 2)->where('eval_in_risk', '=', $proba_impacto_in->avg_impact)->where('control_evaluation', '=', $eval->results)->select('eval_ctrl_risk as eval')->first(); } //guardamos proba en $prom_proba_in para inherente $prom_proba_in[$i] = $proba_impacto_in->avg_probability; $prom_criticidad_in[$i] = $proba_impacto_in->avg_impact; //prom_proba_ctrl para controlado (si es que hay) if (isset($proba_impacto_ctrl)) { $prom_proba_ctrl[$i] = $proba_ctrl->eval; $prom_criticidad_ctrl[$i] = $impacto_ctrl->eval; } else { $prom_proba_ctrl[$i] = NULL; $prom_criticidad_ctrl[$i] = NULL; } //unseteamos variable de proba_impacto_ctrl para que no se repita unset($proba_impacto_ctrl); //obtenemos nombre del riesgo y lo guardamos en array de riesgo junto al nombre de organización //ACTUALIZACIÓN 25-07: OBTENEMOS DATOS DEL RIESGO Y LOS POSIBLES SUBPROCESOS ASOCIADOS $riesgo_temp = \Ermtool\Risk::find($evaluation->risk); //$subprocesses = $riesgo_temp->subprocesses; ---> NO SIRVE MUESTRA SUBPR. DE OTRAS ORGS. $subprocesses = DB::table('subprocesses')->join('organization_subprocess', 'organization_subprocess.subprocess_id', '=', 'subprocesses.id')->join('risk_subprocess', 'risk_subprocess.subprocess_id', '=', 'subprocesses.id')->where('risk_subprocess.risk_id', '=', $riesgo_temp->id)->where('organization_subprocess.organization_id', '=', $_GET['organization_id'])->select('subprocesses.name')->get(); //foreach ($riesgo_temp as $temp) //el riesgo recién obtenido es almacenado en riesgos //{ //probamos eliminar espacios en descripcion $description = preg_replace('(\\n)', ' ', $riesgo_temp->description); $description = preg_replace('(\\r)', ' ', $description); $riesgos[$i] = array('name' => $riesgo_temp->name, 'subobj' => $subprocesses, 'description' => $description); //} $i += 1; } } else { if ($_GET['kind'] == 1) { //---- consulta multiples join para obtener los objective_risk evaluados relacionados a la organización ----// $evaluations = DB::table('evaluation_risk')->join('evaluations', 'evaluations.id', '=', 'evaluation_risk.evaluation_id')->join('objective_risk', 'objective_risk.id', '=', 'evaluation_risk.objective_risk_id')->join('risks', 'risks.id', '=', 'objective_risk.risk_id')->join('objectives', 'objectives.id', '=', 'objective_risk.objective_id')->where('objectives.organization_id', '=', $_GET['organization_id'])->where('evaluations.consolidation', '=', 1)->where('evaluations.updated_at', '<=', date($ano . '-' . $mes) . '-31 23:59:59')->select('evaluation_risk.objective_risk_id as risk_id', 'risks.id as risk')->groupBy('risks.id')->get(); foreach ($evaluations as $evaluation) { $updated_at_in = DB::table('evaluation_risk')->join('evaluations', 'evaluations.id', '=', 'evaluation_risk.evaluation_id')->where('evaluation_risk.objective_risk_id', '=', $evaluation->risk_id)->where('evaluations.consolidation', '=', 1)->where('evaluations.type', '=', 1)->where('evaluations.updated_at', '<=', date($ano . '-' . $mes . '-31 23:59:59'))->max('evaluations.updated_at'); if ($_GET['kind2'] == 1) { //ACTUALIZACIÓN 22-11-16: Obtendremos los riesgos controlados a través de la tabla controlled_risk sólo para la organización y el tipo seleccionado $updated_at_ctrl = DB::table('controlled_risk')->join('objective_risk', 'objective_risk.id', '=', 'controlled_risk.objective_risk_id')->join('objectives', 'objectives.id', '=', 'objective_risk.objective_id')->where('objectives.organization_id', '=', $_GET['organization_id'])->where('controlled_risk.objective_risk_id', '=', $evaluation->risk_id)->where('controlled_risk.created_at', '<=', date($ano . '-' . $mes . '-31 23:59:59'))->max('controlled_risk.created_at'); } //obtenemos promedio de probabilidad e impacto $proba_impacto_in = DB::table('evaluation_risk')->join('evaluations', 'evaluations.id', '=', 'evaluation_risk.evaluation_id')->where('evaluations.updated_at', '=', $updated_at_in)->where('evaluation_risk.objective_risk_id', '=', $evaluation->risk_id)->select('evaluation_risk.avg_probability', 'evaluation_risk.avg_impact')->first(); //proba controlado (si es que hay) if (isset($updated_at_ctrl) && $updated_at_ctrl != NULL) { //ACTUALIZACIÓN 01-12: Obtenemos valor de riesgo controlado de controlled_risk_criteria, según la evaluación de controlled_risk $eval = DB::table('controlled_risk')->where('controlled_risk.objective_risk_id', '=', $evaluation->risk_id)->where('controlled_risk.created_at', '=', $updated_at_ctrl)->select('results')->first(); //obtenemos valor de evaluación controlada, para este resultado y con los valores del riesgo inherente $proba_ctrl = DB::table('controlled_risk_criteria')->where('dim_eval', '=', 1)->where('eval_in_risk', '=', $proba_impacto_in->avg_probability)->where('control_evaluation', '=', $eval->results)->select('eval_ctrl_risk as eval')->first(); $impacto_ctrl = DB::table('controlled_risk_criteria')->where('dim_eval', '=', 2)->where('eval_in_risk', '=', $proba_impacto_in->avg_impact)->where('control_evaluation', '=', $eval->results)->select('eval_ctrl_risk as eval')->first(); } //guardamos proba en $prom_proba $prom_proba_in[$i] = $proba_impacto_in->avg_probability; $prom_criticidad_in[$i] = $proba_impacto_in->avg_impact; //prom_proba_ctrl para controlado (si es que hay) if (isset($proba_ctrl) && isset($impacto_ctrl)) { $prom_proba_ctrl[$i] = $proba_ctrl->eval; $prom_criticidad_ctrl[$i] = $impacto_ctrl->eval; } else { $prom_proba_ctrl[$i] = NULL; $prom_criticidad_ctrl[$i] = NULL; } //unseteamos variable de proba_impacto_ctrl para que no se repita unset($proba_ctrl); unset($impacto_ctrl); //ACTUALIZACIÓN 25-07: OBTENEMOS DATOS DEL RIESGO Y LOS POSIBLES RIESGOS ASOCIADOS $riesgo_temp = \Ermtool\Risk::find($evaluation->risk); //$objectives = $riesgo_temp->objectives ----> NO SIRVE MUESTRA OBJ. DE OTRAS ORGANIZACIONES $objectives = DB::table('objectives')->join('objective_risk', 'objective_risk.objective_id', '=', 'objectives.id')->where('objective_risk.risk_id', '=', $riesgo_temp->id)->where('objectives.organization_id', '=', $_GET['organization_id'])->select('objectives.name')->get(); //eliminamos posibles espacios que puedan llevar a error en descripción $description = preg_replace('(\\n)', ' ', $riesgo_temp->description); $description = preg_replace('(\\r)', ' ', $description); $riesgos[$i] = array('name' => $riesgo_temp->name, 'subobj' => $objectives, 'description' => $description); $i += 1; } } } if ($_GET['kind2'] == 1) { if (Session::get('languaje') == 'en') { //retornamos la misma vista con datos (inglés) return view('en.reportes.heatmap', ['nombre' => $nombre, 'descripcion' => $descripcion, 'riesgos' => $riesgos, 'prom_proba_in' => $prom_proba_in, 'prom_criticidad_in' => $prom_criticidad_in, 'prom_proba_ctrl' => $prom_proba_ctrl, 'prom_criticidad_ctrl' => $prom_criticidad_ctrl, 'kind' => $_GET['kind'], 'kind2' => $_GET['kind2']]); } else { return view('reportes.heatmap', ['nombre' => $nombre, 'descripcion' => $descripcion, 'riesgos' => $riesgos, 'prom_proba_in' => $prom_proba_in, 'prom_criticidad_in' => $prom_criticidad_in, 'prom_proba_ctrl' => $prom_proba_ctrl, 'prom_criticidad_ctrl' => $prom_criticidad_ctrl, 'kind' => $_GET['kind'], 'kind2' => $_GET['kind2']]); } } else { if (Session::get('languaje') == 'en') { //retornamos la misma vista con datos pero solo de riesgos inherentes (inglés) return view('en.reportes.heatmap', ['nombre' => $nombre, 'descripcion' => $descripcion, 'riesgos' => $riesgos, 'prom_proba_in' => $prom_proba_in, 'prom_criticidad_in' => $prom_criticidad_in, 'kind' => $_GET['kind'], 'kind2' => $_GET['kind2']]); } else { return view('reportes.heatmap', ['nombre' => $nombre, 'descripcion' => $descripcion, 'riesgos' => $riesgos, 'prom_proba_in' => $prom_proba_in, 'prom_criticidad_in' => $prom_criticidad_in, 'kind' => $_GET['kind'], 'kind2' => $_GET['kind2']]); } } } }
public function desbloquear($id) { if (Auth::guest()) { return view('login'); } else { global $id1; $id1 = $id; DB::transaction(function () { $riesgo = \Ermtool\Risk::find($GLOBALS['id1']); $riesgo->status = 0; $riesgo->save(); if (Session::get('languaje') == 'en') { Session::flash('message', 'Template risk successfully updated'); } else { Session::flash('message', 'Riesgo tipo desbloqueado correctamente'); } }); return Redirect::to('/riskstype'); } }