/**
* 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');
}
}
