public function getActionPlans($id)
{
$i = 0;
$action_plans = array();
//primero obtenemos los planes de acción para los hallazgos que son directamente de la organización
$planes = DB::table('issues')->join('action_plans', 'action_plans.issue_id', '=', 'issues.id')->where('issues.organization_id', '=', $id)->groupBy('action_plans.id')->select('action_plans.id', 'action_plans.description', 'action_plans.stakeholder_id', 'action_plans.final_date', 'action_plans.status', 'action_plans.stakeholder_id', 'issues.name as issue', 'issues.organization_id as org_id')->get();
//ahora los planes de acción para los planes de auditoría que corresponden a la organización
$planes2 = DB::table('issues')->join('action_plans', 'action_plans.issue_id', '=', 'issues.id')->join('audit_audit_plan', 'audit_audit_plan.id', '=', 'issues.audit_audit_plan_id')->join('audit_plans', 'audit_plans.id', '=', 'audit_audit_plan.audit_plan_id')->where('audit_plans.organization_id', '=', $id)->groupBy('action_plans.id')->select('action_plans.id', 'action_plans.description', 'action_plans.stakeholder_id', 'action_plans.final_date', 'action_plans.status', 'action_plans.stakeholder_id', 'issues.name as issue', 'issues.audit_audit_plan_id as audit_plan_id')->get();
//planes de accion asociados a programa de auditoría (que corresponde a un plan de auditoría asociado a una organización)
$planes3 = DB::table('issues')->join('action_plans', 'action_plans.issue_id', '=', 'issues.id')->join('audit_audit_plan_audit_program', 'audit_audit_plan_audit_program.id', '=', 'issues.audit_audit_plan_audit_program_id')->join('audit_audit_plan', 'audit_audit_plan.id', '=', 'audit_audit_plan_audit_program.audit_audit_plan_id')->join('audit_plans', 'audit_plans.id', '=', 'audit_audit_plan.audit_plan_id')->where('audit_plans.organization_id', '=', $id)->groupBy('action_plans.id')->select('action_plans.id', 'action_plans.description', 'action_plans.stakeholder_id', 'action_plans.final_date', 'action_plans.status', 'action_plans.stakeholder_id', 'issues.name as issue', 'issues.audit_audit_plan_audit_program_id as program_id')->get();
//asociados a una prueba de un plan de auditoría
$planes4 = DB::table('issues')->join('action_plans', 'action_plans.issue_id', '=', 'issues.id')->join('audit_tests', 'audit_tests.id', '=', 'issues.audit_test_id')->join('audit_audit_plan_audit_program', 'audit_audit_plan_audit_program.id', '=', 'audit_tests.audit_audit_plan_audit_program_id')->join('audit_audit_plan', 'audit_audit_plan.id', '=', 'audit_audit_plan_audit_program.audit_audit_plan_id')->join('audit_plans', 'audit_plans.id', '=', 'audit_audit_plan.audit_plan_id')->where('audit_plans.organization_id', '=', $id)->groupBy('action_plans.id')->select('action_plans.id', 'action_plans.description', 'action_plans.stakeholder_id', 'action_plans.final_date', 'action_plans.status', 'action_plans.stakeholder_id', 'issues.name as issue', 'issues.audit_test_id as test_id')->get();
//planes de control de entidad asociados a la organización
$planes5 = DB::table('issues')->join('action_plans', 'action_plans.issue_id', '=', 'issues.id')->join('control_objective_risk', 'control_objective_risk.control_id', '=', 'issues.control_id')->join('objective_risk', 'objective_risk.id', '=', 'control_objective_risk.objective_risk_id')->join('objectives', 'objectives.id', '=', 'objective_risk_id')->where('objectives.organization_id', '=', $id)->groupBy('action_plans.id')->select('action_plans.id', 'action_plans.description', 'action_plans.stakeholder_id', 'action_plans.final_date', 'action_plans.status', 'action_plans.stakeholder_id', 'issues.name as issue', 'control_objective_risk.control_id as control_objective_risk_id')->get();
//planes de control de proceso asociados a la organización
$planes6 = DB::table('issues')->join('action_plans', 'action_plans.issue_id', '=', 'issues.id')->join('control_risk_subprocess', 'control_risk_subprocess.control_id', '=', 'issues.control_id')->join('risk_subprocess', 'risk_subprocess.id', '=', 'control_risk_subprocess.risk_subprocess_id')->join('organization_subprocess', 'organization_subprocess.subprocess_id', '=', 'risk_subprocess.subprocess_id')->where('organization_subprocess.organization_id', '=', $id)->groupBy('action_plans.id')->select('action_plans.id', 'action_plans.description', 'action_plans.stakeholder_id', 'action_plans.final_date', 'action_plans.status', 'action_plans.stakeholder_id', 'issues.name as issue', 'control_risk_subprocess.control_id as control_risk_subprocess_id')->get();
//planes asociados a un subproceso perteneciente a la organización
$planes7 = DB::table('issues')->join('action_plans', 'action_plans.issue_id', '=', 'issues.id')->join('subprocesses', 'subprocesses.id', '=', 'issues.subprocess_id')->join('organization_subprocess', 'organization_subprocess.subprocess_id', '=', 'subprocesses.id')->where('organization_subprocess.organization_id', '=', $id)->groupBy('action_plans.id')->select('action_plans.id', 'action_plans.description', 'action_plans.stakeholder_id', 'action_plans.final_date', 'action_plans.status', 'action_plans.stakeholder_id', 'issues.name as issue', 'issues.subprocess_id as subprocess_id')->get();
//planes asociados a un proceso perteneciente a la organización
$planes8 = DB::table('issues')->join('action_plans', 'action_plans.issue_id', '=', 'issues.id')->join('processes', 'processes.id', '=', 'issues.process_id')->join('subprocesses', 'subprocesses.process_id', '=', 'processes.id')->join('organization_subprocess', 'organization_subprocess.subprocess_id', '=', 'subprocesses.id')->where('organization_subprocess.organization_id', '=', $id)->groupBy('action_plans.id')->select('action_plans.id', 'action_plans.description', 'action_plans.stakeholder_id', 'action_plans.final_date', 'action_plans.status', 'action_plans.stakeholder_id', 'issues.name as issue', 'issues.process_id as process_id')->get();
$plans = array_merge($planes, $planes2, $planes3, $planes4, $planes5, $planes6, $planes7, $planes8);
foreach ($plans as $plan) {
$final_date = new DateTime($plan->final_date);
$final_date = date_format($final_date, "d-m-Y");
if (Session::get('languaje') == 'en') {
//obtenemos datos de responsable
if ($plan->stakeholder_id != NULL) {
$resp = \Ermtool\Stakeholder::find($plan->stakeholder_id);
$resp_mail = $resp->mail;
$resp = $resp->name . ' ' . $resp->surnames;
} else {
$resp = 'Responsable is not defined';
$resp_mail = '';
}
if ($plan->status === 0) {
$status = 'In progress';
} else {
if ($plan->status == 1) {
$status = 'Closed';
} else {
if ($plan->status === NULL) {
$status = 'Status is not defined';
}
}
}
//seteamos origen
if (isset($plan->org_id)) {
$origin = 'Organization issue';
} else {
if (isset($plan->audit_plan_id)) {
$origin = 'Audit plan issue';
} else {
if (isset($plan->program_id)) {
$origin = 'Audit program issue';
} else {
if (isset($plan->test_id)) {
$origin = 'Audit test issue';
} else {
if (isset($plan->control_objective_risk_id)) {
$origin = 'Entity control issue';
} else {
if (isset($plan->control_risk_subprocess_id)) {
$origin = 'Process control issue';
} else {
if (isset($plan->subprocess_id)) {
$origin = 'Subprocess issue';
} else {
if (isset($plan->process_id)) {
$origin = 'Process issue';
}
}
}
}
}
}
}
}
} else {
//obtenemos datos de responsable
if ($plan->stakeholder_id != NULL) {
$resp = \Ermtool\Stakeholder::find($plan->stakeholder_id);
$resp_mail = $resp->mail;
$resp = $resp->name . ' ' . $resp->surnames;
} else {
$resp = 'No se ha definido responsable';
$resp_mail = '';
}
if ($plan->status === 0) {
$status = 'En progreso';
} else {
if ($plan->status == 1) {
$status = 'Cerrado';
} else {
if ($plan->status === NULL) {
$status = 'Estado no definido';
}
}
}
//seteamos origen
if (isset($plan->org_id)) {
$origin = 'Hallazgo de organización';
} else {
if (isset($plan->audit_plan_id)) {
$origin = 'Hallazgo de plan de auditoría';
} else {
if (isset($plan->program_id)) {
$origin = 'Hallazgo de programa de auditoría';
} else {
if (isset($plan->test_id)) {
$origin = 'Hallazgo de prueba de auditoría';
} else {
if (isset($plan->control_objective_risk_id)) {
$origin = 'Hallazgo de control de entidad';
} else {
if (isset($plan->control_risk_subprocess_id)) {
$origin = 'Hallazgo de control de proceso';
} else {
if (isset($plan->subprocess_id)) {
$origin = 'Hallazgo asociado a subproceso';
} else {
if (isset($plan->process_id)) {
$origin = 'Hallazgo asociado a proceso';
}
}
}
}
}
}
}
}
}
if (strstr($_SERVER["REQUEST_URI"], 'genexcelplan')) {
$action_plans[$i] = ['Origen del hallazgo' => $origin, 'Hallazgo' => $plan->issue, 'Descripción' => $plan->description, 'Responsable' => $resp, 'Correo responsable' => $resp_mail, 'Estado' => $status, 'Fecha final' => $final_date];
} else {
$action_plans[$i] = ['origin' => $origin, 'id' => $plan->id, 'issue' => $plan->issue, 'description' => $plan->description, 'stakeholder' => $resp, 'stakeholder_mail' => $resp_mail, 'final_date' => $final_date, 'status' => $status, 'status_number' => $plan->status];
}
$i += 1;
//
}
return $action_plans;
}
public function desbloquear($id)
{
if (Auth::guest()) {
return view('login');
} else {
global $id1;
$id1 = $id;
DB::transaction(function () {
$stakeholder = \Ermtool\Stakeholder::find($GLOBALS['id1']);
$stakeholder->status = 0;
$stakeholder->save();
if (Session::get('languaje') == 'en') {
Session::flash('message', 'Stakeholder successfully unblocked');
} else {
Session::flash('message', 'Usuario desbloqueado correctamente');
}
});
return Redirect::to('/stakeholders');
}
}
public function generarMatriz($value, $org)
{
if (Auth::guest()) {
return view('login');
} else {
$i = 0;
//contador de controles/subprocesos o controles/objetivos
$datos = array();
if (!strstr($_SERVER["REQUEST_URI"], 'genexcel')) {
$value = $_GET['kind'];
$org = $_GET['organization_id'];
}
//obtenemos controles
$controls = DB::table('controls')->select('controls.*')->get();
foreach ($controls as $control) {
$risk_obj = NULL;
$risk_sub = NULL;
// -- seteamos datos --//
if (Session::get('languaje') == 'en') {
if ($control->type === NULL) {
$type = "Not defined";
} else {
//Seteamos type. 0=Manual, 1=Semi-automático, 2=Automático
switch ($control->type) {
case 0:
$type = "Manual";
break;
case 1:
$type = "Semi-automatic";
break;
case 2:
$type = "Automatic";
}
}
if ($control->periodicity === NULL) {
$periodicity = "Not defined";
} else {
//Seteamos periodicity. 0=Diario, 1=Semanal, 2=Mensual, 3=Semestral, 4=Anual
switch ($control->periodicity) {
case 0:
$periodicity = "Daily";
break;
case 1:
$periodicity = "Weekly";
break;
case 2:
$periodicity = "Monthly";
break;
case 3:
$periodicity = "Biannual";
break;
case 4:
$periodicity = "Annual";
break;
case 5:
$periodicity = "Each time it occurs";
break;
}
}
if ($control->purpose === NULL) {
$purpose = "Not defined";
} else {
//Seteamos purpose. 0=Preventivo, 1=Detectivo, 2=Correctivo
switch ($control->purpose) {
case 0:
$purpose = "Preventive";
case 1:
$purpose = "Detective";
case 2:
$purpose = "Corrective";
}
}
if ($control->expected_cost === NULL) {
$expected_cost = "Not defined";
} else {
$expected_cost = $control->expected_cost;
}
if ($control->evidence === NULL || $control->evidence == "") {
$evidence = "Without evidence";
} else {
$evidence = $control->evidence;
}
//Seteamos responsable del control
$stakeholder = \Ermtool\Stakeholder::find($control->stakeholder_id);
if ($stakeholder) {
$stakeholder2 = $stakeholder['name'] . ' ' . $stakeholder['surnames'];
} else {
$stakeholder2 = "Not assigned";
}
} else {
if ($control->type === NULL) {
$type = "No definido";
} else {
//Seteamos type. 0=Manual, 1=Semi-automático, 2=Automático
switch ($control->type) {
case 0:
$type = "Manual";
break;
case 1:
$type = "Semi-automático";
break;
case 2:
$type = "Automático";
}
}
if ($control->periodicity === NULL) {
$periodicity = "No definido";
} else {
//Seteamos periodicity. 0=Diario, 1=Semanal, 2=Mensual, 3=Semestral, 4=Anual
switch ($control->periodicity) {
case 0:
$periodicity = "Diario";
break;
case 1:
$periodicity = "Semanal";
break;
case 2:
$periodicity = "Mensual";
break;
case 3:
$periodicity = "Semestral";
break;
case 4:
$periodicity = "Anual";
break;
case 5:
$periodicity = "Cada vez que ocurra";
break;
}
}
if ($control->purpose === NULL) {
$purpose = "No definido";
} else {
//Seteamos purpose. 0=Preventivo, 1=Detectivo, 2=Correctivo
switch ($control->purpose) {
case 0:
$purpose = "Preventivo";
case 1:
$purpose = "Detectivo";
case 2:
$purpose = "Correctivo";
}
}
if ($control->expected_cost === NULL) {
$expected_cost = "No definido";
} else {
$expected_cost = $control->expected_cost;
}
if ($control->evidence === NULL || $control->evidence == "") {
$evidence = "Sin evidencia";
} else {
$evidence = $control->evidence;
}
//Seteamos responsable del control
$stakeholder = \Ermtool\Stakeholder::find($control->stakeholder_id);
if ($stakeholder) {
$stakeholder2 = $stakeholder['name'] . ' ' . $stakeholder['surnames'];
} else {
$stakeholder2 = "No asignado";
}
}
/* IMPORTANTE!!!
Los nombres de las variables serán guardados en español para mostrarlos
en el archivo excel que será exportado
*/
//obtenemos riesgo - objetivo - organización o riesgo - subproceso - organización para cada control
if ($value == 0) {
$risk_subprocess = DB::table('control_risk_subprocess')->join('controls', 'controls.id', '=', 'control_risk_subprocess.control_id')->join('risk_subprocess', 'risk_subprocess.id', '=', 'control_risk_subprocess.risk_subprocess_id')->join('subprocesses', 'subprocesses.id', '=', 'risk_subprocess.subprocess_id')->join('risks', 'risks.id', '=', 'risk_subprocess.risk_id')->join('organization_subprocess', 'organization_subprocess.subprocess_id', '=', 'subprocesses.id')->join('organizations', 'organizations.id', '=', 'organization_subprocess.organization_id')->where('controls.id', '=', $control->id)->where('organizations.id', '=', $org)->select('subprocesses.name as subprocess_name', 'risks.name as risk_name')->get();
if ($risk_subprocess != NULL) {
$last = end($risk_subprocess);
//seteamos cada riesgo, subproceso y organización
foreach ($risk_subprocess as $sub_risk) {
if ($sub_risk != $last) {
if (!strstr($_SERVER["REQUEST_URI"], 'genexcel')) {
$risk_sub .= $sub_risk->risk_name . ' / ' . $sub_risk->subprocess_name . ', ';
} else {
$risk_sub .= $sub_risk->risk_name . ' / ' . $sub_risk->subprocess_name . ', ';
}
} else {
$risk_sub .= $sub_risk->risk_name . ' / ' . $sub_risk->subprocess_name;
}
}
if (Session::get('languaje') == 'en') {
$datos[$i] = ['Control' => $control->name, 'Description' => $control->description, 'Responsable' => $stakeholder2, 'Kind' => $type, 'Periodicity' => $periodicity, 'Purpose' => $purpose, 'Expected_cost' => $expected_cost, 'Evidence' => $evidence, 'Risk_Subprocess' => $risk_sub];
} else {
$datos[$i] = ['Control' => $control->name, 'Descripción' => $control->description, 'Responsable' => $stakeholder2, 'Tipo' => $type, 'Periodicidad' => $periodicity, 'Propósito' => $purpose, 'Costo_control' => $expected_cost, 'Evidencia' => $evidence, 'Riesgo_Subproceso' => $risk_sub];
}
$i += 1;
}
} else {
if ($value == 1) {
//obtenemos riesgos, objetivos y organización
$objective_risk = DB::table('control_objective_risk')->join('controls', 'controls.id', '=', 'control_objective_risk.control_id')->join('objective_risk', 'objective_risk.id', '=', 'control_objective_risk.objective_risk_id')->join('objectives', 'objectives.id', '=', 'objective_risk.objective_id')->join('risks', 'risks.id', '=', 'objective_risk.risk_id')->join('organizations', 'organizations.id', '=', 'objectives.organization_id')->where('controls.id', '=', $control->id)->where('organizations.id', '=', $org)->select('objectives.name as objective_name', 'risks.name as risk_name')->get();
if ($objective_risk != NULL) {
$last = end($objective_risk);
//seteamos cada riesgo, objetivo y organización
foreach ($objective_risk as $obj_risk) {
if ($obj_risk != $last) {
if (!strstr($_SERVER["REQUEST_URI"], 'genexcel')) {
$risk_obj .= $obj_risk->risk_name . ' / ' . $obj_risk->objective_name . ', ';
} else {
$risk_obj .= $obj_risk->risk_name . ' / ' . $obj_risk->objective_name . ', ';
}
} else {
$risk_obj .= $obj_risk->risk_name . ' / ' . $obj_risk->objective_name;
}
}
if (Session::get('languaje') == 'en') {
$datos[$i] = ['Control' => $control->name, 'Description' => $control->description, 'Responsable' => $stakeholder2, 'Kind' => $type, 'Periodicity' => $periodicity, 'Purpose' => $purpose, 'Expected_cost' => $expected_cost, 'Evidence' => $evidence, 'Risk_Objective' => $risk_obj];
} else {
$datos[$i] = ['Control' => $control->name, 'Descripción' => $control->description, 'Responsable' => $stakeholder2, 'Tipo' => $type, 'Periodicidad' => $periodicity, 'Propósito' => $purpose, 'Costo_control' => $expected_cost, 'Evidencia' => $evidence, 'Riesgo_Objetivo' => $risk_obj];
}
$i += 1;
}
}
}
}
if (strstr($_SERVER["REQUEST_URI"], 'genexcel')) {
return $datos;
} else {
$organizations = \Ermtool\Organization::where('status', 0)->lists('name', 'id');
if (Session::get('languaje') == 'en') {
return view('en.reportes.matrices', ['datos' => $datos, 'value' => $value, 'organizations' => $organizations, 'org_selected' => $org]);
} else {
return view('reportes.matrices', ['datos' => $datos, 'value' => $value, 'organizations' => $organizations, 'org_selected' => $org]);
}
}
}
}
public function show($id)
{
if (Auth::guest()) {
return view('login');
} else {
//obtenemos preguntas de encuesta
//$questions = \Ermtool\Question::where('poll_id',$id)->get();
$questions = DB::table('questions')->where('poll_id', $id)->get();
//obtenemos stakeholder para luego mostrar sus datos
$stakeholder = \Ermtool\Stakeholder::find($_GET['stakeholder_id']);
//obtenemos rol o roles del stakeholder
$roles = \Ermtool\Stakeholder::find($_GET['stakeholder_id'])->roles;
//nombre de encuesta
$encuesta = \Ermtool\Poll::where('id', $id)->value('name');
$answers = array();
$i = 0;
foreach ($questions as $question) {
$answers[$i] = DB::table('answers')->where('question_id', $question->id)->where('stakeholder_id', $_GET['stakeholder_id'])->get();
$i += 1;
}
if (Session::get('languaje') == 'en') {
return view('en.identificacion_eventos_riesgos.encuesta2', ['questions' => $questions, 'answers' => $answers, 'stakeholder' => $stakeholder, 'encuesta' => $encuesta, 'roles' => $roles]);
} else {
return view('identificacion_eventos_riesgos.encuesta2', ['questions' => $questions, 'answers' => $answers, 'stakeholder' => $stakeholder, 'encuesta' => $encuesta, 'roles' => $roles]);
}
}
}
public function guardarEvaluacion(Request $request)
{
if (Auth::guest()) {
return view('login');
} else {
//primero verificamos si el rut ingresado corresponde a algún stakeholder
$stakeholder = \Ermtool\Stakeholder::find($request['rut']);
//Validación: Si la validación es pasada, el código continua
//$this->validate($request, [
// 'rut' => 'exists:stakeholders,id'
//]);
if ($stakeholder) {
DB::transaction(function () {
//verificamos si tipo = 0 (significaria que es evaluación manual por lo tanto se debe crear)
$i = 0;
$evaluation_risk = array();
//array que guarda los riesgos que se estarán almacenando
if ($_POST['tipo'] == 0) {
//primero creamos evaluación manual
$eval_id = DB::table('evaluations')->insertGetId(['name' => 'Evaluación Manual', 'consolidation' => 1, 'description' => 'Evaluación Manual', 'created_at' => date('Y-m-d H:i:s'), 'updated_at' => date('Y-m-d H:i:s')]);
//insertamos riesgos de evaluación
foreach ($_POST['evaluation_risk_id'] as $risk_id) {
if (isset($_POST['proba_' . $risk_id . '_subprocess'])) {
//inseratmos riesgo de subproceso en evaluation_risk
$evaluation_risk[$i] = DB::table('evaluation_risk')->insertGetId(['evaluation_id' => $eval_id, 'risk_subprocess_id' => $risk_id, 'avg_probability' => $_POST['proba_' . $risk_id . '_subprocess'], 'avg_impact' => $_POST['criticidad_' . $risk_id . '_subprocess']]);
//insertamos en evaluation_risk_stakeholder
DB::table('evaluation_risk_stakeholder')->insert(['evaluation_risk_id' => $evaluation_risk[$i], 'stakeholder_id' => $_POST['rut'], 'probability' => $_POST['proba_' . $risk_id . '_subprocess'], 'impact' => $_POST['criticidad_' . $risk_id . '_subprocess']]);
$i += 1;
}
if (isset($_POST['proba_' . $risk_id . '_objective'])) {
//inseratmos riesgo de negocio en evaluation_risk
$evaluation_risk[$i] = DB::table('evaluation_risk')->insertGetId(['evaluation_id' => $eval_id, 'objective_risk_id' => $risk_id, 'avg_probability' => $_POST['proba_' . $risk_id . '_objective'], 'avg_impact' => $_POST['criticidad_' . $risk_id . '_objective']]);
//insertamos en evaluation_risk_stakeholder
DB::table('evaluation_risk_stakeholder')->insert(['evaluation_risk_id' => $evaluation_risk[$i], 'stakeholder_id' => $_POST['rut'], 'probability' => $_POST['proba_' . $risk_id . '_objective'], 'impact' => $_POST['criticidad_' . $risk_id . '_objective']]);
$i += 1;
}
}
} else {
foreach ($_POST['evaluation_risk_id'] as $evaluation_risk) {
DB::table('evaluation_risk_stakeholder')->insert(['evaluation_risk_id' => $evaluation_risk, 'stakeholder_id' => $_POST['rut'], 'probability' => $_POST['proba_' . $evaluation_risk], 'impact' => $_POST['criticidad_' . $evaluation_risk]]);
//actualizamos promedio de probabilidad e impacto en tabla evaluation_risk
//para cada riesgo evaluado, identificaremos promedio de probabilidad y de criticidad
$prom_proba = DB::table('evaluation_risk')->join('evaluation_risk_stakeholder', 'evaluation_risk_stakeholder.evaluation_risk_id', '=', 'evaluation_risk.id')->where('evaluation_risk.id', $evaluation_risk)->avg('probability');
$prom_impacto = DB::table('evaluation_risk')->join('evaluation_risk_stakeholder', 'evaluation_risk_stakeholder.evaluation_risk_id', '=', 'evaluation_risk.id')->where('evaluation_risk.id', $evaluation_risk)->avg('impact');
DB::table('evaluation_risk')->join('evaluation_risk_stakeholder', 'evaluation_risk_stakeholder.evaluation_risk_id', '=', 'evaluation_risk.id')->where('evaluation_risk.id', $evaluation_risk)->update(['evaluation_risk.avg_probability' => $prom_proba, 'evaluation_risk.avg_impact' => $prom_impacto]);
}
}
if (Session::get('languaje') == 'en') {
Session::flash('message', 'Answers successfully sent');
} else {
Session::flash('message', 'Respuestas enviadas correctamente');
}
});
return view('evaluacion.encuestaresp');
//print_r($_POST);
} else {
if ($_POST['tipo'] == 0) {
$i = 0;
//volvemos a obtener riesgos para devolver vista correcta
foreach ($_POST['evaluation_risk_id'] as $risk_id) {
if (isset($_POST['proba_' . $risk_id . '_subprocess'])) {
$sub = DB::table('risk_subprocess')->where('risk_subprocess.id', '=', $risk_id)->join('risks', 'risk_subprocess.risk_id', '=', 'risks.id')->join('subprocesses', 'risk_subprocess.subprocess_id', '=', 'subprocesses.id')->join('processes', 'processes.id', '=', 'subprocesses.process_id')->select('risks.name as risk_name', 'risks.description', 'subprocesses.name as subprocess_name', 'processes.name as process_name')->get();
//guardamos el riesgo de subproceso junto a su id de evaluation_risk para crear form de encuesta
foreach ($sub as $sub) {
$riesgos[$i] = array('type' => 'subprocess', 'risk_id' => $risk, 'risk_name' => $sub->risk_name, 'description' => $sub->description, 'subobj' => $sub->subprocess_name, 'orgproc' => $sub->process_name);
$i += 1;
}
}
if (isset($_POST['proba_' . $risk_id . '_objective'])) {
//obtenemos nombre de riesgo y organizacion
$neg = DB::table('objective_risk')->where('objective_risk.id', '=', $risk_id)->join('risks', 'objective_risk.risk_id', '=', 'risks.id')->join('objectives', 'objective_risk.objective_id', '=', 'objectives.id')->join('organizations', 'objectives.organization_id', '=', 'organizations.id')->select('risks.name as risk_name', 'risks.description', 'organizations.name as organization_name', 'objectives.name as objective_name')->get();
foreach ($neg as $neg) {
$riesgos[$i] = array('type' => 'objective', 'risk_id' => $risk_id, 'risk_name' => $neg->risk_name, 'description' => $neg->description, 'subobj' => $neg->objective_name, 'orgproc' => $neg->organization_name);
$i += 1;
}
}
}
if (Session::get('languaje') == 'en') {
Session::flash('message', "The entered Id is not in our database");
$tipos_impacto = ['Despicable', 'Less', 'Moderate', 'Severe', 'Catastrophic'];
$tipos_proba = ['Very improbable', 'Unlikely', 'Possible', 'Likely', 'Very likely'];
return view('en.evaluacion.encuesta', ['encuesta' => 'Manual Evaluation', 'riesgos' => $riesgos, 'tipo' => 0, 'tipos_impacto' => $tipos_impacto, 'tipos_proba' => $tipos_proba, 'id' => 0]);
} else {
Session::flash('message', 'El rut ingresado no se encuentra en nuestra base de datos');
$tipos_impacto = ['Despreciable', 'Menor', 'Moderado', 'Severo', 'Catastrófico'];
$tipos_proba = ['Muy poco probable', 'Poco probable', 'Posible', 'Probable', 'Muy probable'];
return view('evaluacion.encuesta', ['encuesta' => 'Evaluación Manual', 'riesgos' => $riesgos, 'tipo' => 0, 'tipos_impacto' => $tipos_impacto, 'tipos_proba' => $tipos_proba, 'id' => 0])->withInput(Input::all());
//no funcion withInput
}
} else {
return Redirect::to('evaluacion.encuesta.' . $request["evaluation_id"])->withInput();
}
}
}
}
public function getTests($kind, $id)
{
$i = 0;
//contador de pruebas
$audit_plan = \Ermtool\Audit_plan::where('id', $id)->value('name');
$pruebas_ejec = 0;
//pruebas en ejecución
$pruebas_abiertas = 0;
//pruebas abiertas
$pruebas_cerradas = 0;
//pruebas cerradas
$type = NULL;
//identifica si es una prueba asociada a un riesgo, subproceso o control (1=Riesgo, 2=Subproceso, 3=Control)
$audit_tests = array();
$tests = DB::table('audit_tests')->join('audit_audit_plan_audit_program', 'audit_audit_plan_audit_program.id', '=', 'audit_tests.audit_audit_plan_audit_program_id')->join('audit_programs', 'audit_programs.id', '=', 'audit_audit_plan_audit_program.audit_program_id')->join('audit_audit_plan', 'audit_audit_plan.id', '=', 'audit_audit_plan_audit_program.audit_audit_plan_id')->join('audits', 'audits.id', '=', 'audit_audit_plan.audit_id')->join('audit_plans', 'audit_plans.id', '=', 'audit_audit_plan.audit_plan_id')->where('audit_plans.id', '=', $id)->select('audit_plans.name AS audit_plan_name', 'audits.name AS audit_name', 'audit_programs.name as audit_program_name', 'audit_tests.description AS description', 'audit_tests.name AS name', 'audit_tests.type', 'audit_tests.status', 'audit_tests.results', 'audit_tests.hh', 'audit_tests.control_id', 'audit_tests.subprocess_id', 'audit_tests.risk_id', 'audit_tests.stakeholder_id')->get();
foreach ($tests as $test) {
//sumamos a prueba ejec abierta o cerrada según el estado que posea
if ($test->status == 0) {
$pruebas_abiertas += 1;
} else {
if ($test->status == 1) {
$pruebas_ejec += 1;
} else {
if ($test->status == 2) {
$pruebas_cerradas += 1;
}
}
}
//obtenemos nombre de stakeholder
$resp = \Ermtool\Stakeholder::find($test->stakeholder_id);
$resp = $resp['name'] . ' ' . $resp['surnames'];
//obtenemos nombre de riesgo, control o subproceso según corresponda
if ($test->risk_id != NULL) {
$relacionado = \Ermtool\Risk::where('id', $test->risk_id)->value('name');
$type = 1;
} else {
if ($test->subprocess_id != NULL) {
$relacionado = \Ermtool\Subprocess::where('id', $test->subprocess_id)->value('name');
$type = 2;
} else {
if ($test->control_id != NULL) {
$relacionado = \Ermtool\Control::where('id', $test->control_id)->value('name');
$type = 3;
}
}
}
if (strstr($_SERVER["REQUEST_URI"], 'genexcelgraficosdinamicos')) {
if (Session::get('languaje') == 'en') {
if ($kind == 1 && $test->status == 0) {
//tipo
if ($test->type == 0) {
$test_type = 'Design test';
} else {
if ($test->type == 1) {
$test_type = 'Operationa effectiveness test';
} else {
if ($test->type == 2) {
$test_type = 'Compliance test';
} else {
if ($test->type == 3) {
$test_type = 'Sustantive tests';
} else {
$test_type = 'Not defined';
}
}
}
}
//resultado
if ($test->results == 0) {
$results = 'Ineffective';
} else {
if ($test->results == 1) {
$results = 'Effective';
} else {
if ($test->results == 2) {
$results = 'In process';
}
}
}
if ($type == 1) {
$related = 'Risk: ' . $relacionado;
} else {
if ($type == 2) {
$related = 'Subprocess: ' . $relacionado;
} else {
if ($type == 3) {
$related = 'Control: ' . $relacionado;
}
}
}
$audit_tests[$i] = ['Audit plan' => $audit_plan, 'Audit' => $test->audit_name, 'Program' => $test->audit_program_name, 'Test' => $test->name, 'Description' => $test->description, 'Kind' => $test_type, 'Results' => $results, 'Hours-man' => $test->hh, 'Responsable' => $resp, 'Related object' => $related];
} else {
if ($kind == 2 && $test->status == 1) {
//tipo
if ($test->type == 0) {
$test_type = 'Design test';
} else {
if ($test->type == 1) {
$test_type = 'Operationa effectiveness test';
} else {
if ($test->type == 2) {
$test_type = 'Compliance test';
} else {
if ($test->type == 3) {
$test_type = 'Sustantive tests';
} else {
$test_type = 'Not defined';
}
}
}
}
//resultado
if ($test->results == 0) {
$results = 'Ineffective';
} else {
if ($test->results == 1) {
$results = 'Effective';
} else {
if ($test->results == 2) {
$results = 'In process';
}
}
}
if ($type == 1) {
$related = 'Risk: ' . $relacionado;
} else {
if ($type == 2) {
$related = 'Subprocess: ' . $relacionado;
} else {
if ($type == 3) {
$related = 'Control: ' . $relacionado;
}
}
}
$audit_tests[$i] = ['Audit plan' => $audit_plan, 'Audit' => $test->audit_name, 'Program' => $test->audit_program_name, 'Test' => $test->name, 'Description' => $test->description, 'Kind' => $test_type, 'Results' => $results, 'Hours-man' => $test->hh, 'Responsable' => $resp, 'Related object' => $related];
} else {
if ($kind == 3 && $test->status == 2) {
//tipo
if ($test->type == 0) {
$test_type = 'Design test';
} else {
if ($test->type == 1) {
$test_type = 'Operationa effectiveness test';
} else {
if ($test->type == 2) {
$test_type = 'Compliance test';
} else {
if ($test->type == 3) {
$test_type = 'Sustantive tests';
} else {
$test_type = 'Not defined';
}
}
}
}
//resultado
if ($test->results == 0) {
$results = 'Ineffective';
} else {
if ($test->results == 1) {
$results = 'Effective';
} else {
if ($test->results == 2) {
$results = 'In process';
}
}
}
if ($type == 1) {
$related = 'Risk: ' . $relacionado;
} else {
if ($type == 2) {
$related = 'Subprocess: ' . $relacionado;
} else {
if ($type == 3) {
$related = 'Control: ' . $relacionado;
}
}
}
$audit_tests[$i] = ['Audit plan' => $audit_plan, 'Audit' => $test->audit_name, 'Program' => $test->audit_program_name, 'Test' => $test->name, 'Description' => $test->description, 'Kind' => $test_type, 'Results' => $results, 'Hours-man' => $test->hh, 'Responsable' => $resp, 'Related object' => $related];
}
}
}
} else {
if ($kind == 1 && $test->status == 0) {
//tipo
if ($test->type == 0) {
$test_type = 'Prueba de diseño';
} else {
if ($test->type == 1) {
$test_type = 'Prueba de efectividad operativa';
} else {
if ($test->type == 2) {
$test_type = 'Prueba de cumplimiento';
} else {
if ($test->type == 3) {
$test_type = 'Prueba sustantiva';
} else {
$test_type = 'No definido';
}
}
}
}
//resultado
if ($test->results == 0) {
$results = 'Inefectiva';
} else {
if ($test->results == 1) {
$results = 'Efectiva';
} else {
if ($test->results == 2) {
$results = 'En proceso';
}
}
}
if ($type == 1) {
$related = 'Riesgo: ' . $relacionado;
} else {
if ($type == 2) {
$related = 'Subproceso: ' . $relacionado;
} else {
if ($type == 3) {
$related = 'Control: ' . $relacionado;
}
}
}
$audit_tests[$i] = ['Plan de auditoría' => $audit_plan, 'Auditoría' => $test->audit_name, 'Programa' => $test->audit_program_name, 'Prueba' => $test->name, 'Descripción' => $test->description, 'Tipo' => $test_type, 'Resultado' => $results, 'Horas-hombre' => $test->hh, 'Responsable' => $resp, 'Objeto relacionado' => $related];
} else {
if ($kind == 2 && $test->status == 1) {
//tipo
if ($test->type == 0) {
$test_type = 'Prueba de diseño';
} else {
if ($test->type == 1) {
$test_type = 'Prueba de efectividad operativa';
} else {
if ($test->type == 2) {
$test_type = 'Prueba de cumplimiento';
} else {
if ($test->type == 3) {
$test_type = 'Prueba sustantiva';
} else {
$test_type = 'No definido';
}
}
}
}
//resultado
if ($test->results == 0) {
$results = 'Inefectiva';
} else {
if ($test->results == 1) {
$results = 'Efectiva';
} else {
if ($test->results == 2) {
$results = 'En proceso';
}
}
}
if ($type == 1) {
$related = 'Riesgo: ' . $relacionado;
} else {
if ($type == 2) {
$related = 'Subproceso: ' . $relacionado;
} else {
if ($type == 3) {
$related = 'Control: ' . $relacionado;
}
}
}
$audit_tests[$i] = ['Plan de auditoría' => $audit_plan, 'Auditoría' => $test->audit_name, 'Programa' => $test->audit_program_name, 'Prueba' => $test->name, 'Descripción' => $test->description, 'Tipo' => $test_type, 'Resultado' => $results, 'Horas-hombre' => $test->hh, 'Responsable' => $resp, 'Objeto relacionado' => $related];
} else {
if ($kind == 3 && $test->status == 2) {
//tipo
if ($test->type == 0) {
$test_type = 'Prueba de diseño';
} else {
if ($test->type == 1) {
$test_type = 'Prueba de efectividad operativa';
} else {
if ($test->type == 2) {
$test_type = 'Prueba de cumplimiento';
} else {
if ($test->type == 3) {
$test_type = 'Prueba sustantiva';
} else {
$test_type = 'No definido';
}
}
}
}
//resultado
if ($test->results == 0) {
$results = 'Inefectiva';
} else {
if ($test->results == 1) {
$results = 'Efectiva';
} else {
if ($test->results == 2) {
$results = 'En proceso';
}
}
}
if ($type == 1) {
$related = 'Riesgo: ' . $relacionado;
} else {
if ($type == 2) {
$related = 'Subproceso: ' . $relacionado;
} else {
if ($type == 3) {
$related = 'Control: ' . $relacionado;
}
}
}
$audit_tests[$i] = ['Plan de auditoría' => $audit_plan, 'Auditoría' => $test->audit_name, 'Programa' => $test->audit_program_name, 'Prueba' => $test->name, 'Descripción' => $test->description, 'Tipo' => $test_type, 'Resultado' => $results, 'Horas-hombre' => $test->hh, 'Responsable' => $resp, 'Objeto relacionado' => $related];
}
}
}
}
} else {
$audit_tests[$i] = ['audit_name' => $test->audit_name, 'audit_program_name' => $test->audit_program_name, 'name' => $test->name, 'description' => $test->description, 'type' => $test->type, 'status' => $test->status, 'results' => $test->results, 'hh' => $test->hh, 'stakeholder' => $resp, 'related' => $relacionado, 'related_type' => $type];
}
$i += 1;
}
if (strstr($_SERVER["REQUEST_URI"], 'genexcelgraficos')) {
return $audit_tests;
} else {
return json_encode(['audit_plan' => $audit_plan, 'audit_tests' => $audit_tests, 'pruebas_abiertas' => $pruebas_abiertas, 'pruebas_ejec' => $pruebas_ejec, 'pruebas_cerradas' => $pruebas_cerradas]);
}
}