/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
if (Auth::guest()) {
return view('login');
} else {
$organizations = \Ermtool\Organization::where('status', 0)->lists('name', 'id');
return view('documentos.index', ['organizations' => $organizations]);
}
}
/**
* Show the form for editing the specified resource.
*
* @param int $id
* @return \Illuminate\Http\Response
*/
public function edit($id)
{
if (Auth::guest()) {
return view('login');
} else {
$organizations = \Ermtool\Organization::where('id', '<>', $id)->where('status', 0)->where('organization_id', NULL)->lists('name', 'id');
$org = \Ermtool\Organization::find($id);
if (Session::get('languaje') == 'en') {
return view('en.datos_maestros.organization.edit', ['organizations' => $organizations, 'organization' => $org]);
} else {
return view('datos_maestros.organization.edit', ['organizations' => $organizations, 'organization' => $org]);
}
}
}
/**
* Show the form for editing the specified resource.
*
* @param int $id
* @return \Illuminate\Http\Response
*/
public function edit($id)
{
if (Auth::guest()) {
return view('login');
} else {
$org = \Ermtool\Organization::where('id', $_GET['org'])->value('name');
$org_id = \Ermtool\Organization::where('id', $_GET['org'])->value('id');
//obtenemos stakeholders de la misma organización
$stakes = DB::table('stakeholders')->join('organization_stakeholder', 'organization_stakeholder.stakeholder_id', '=', 'stakeholders.id')->where('organization_stakeholder.organization_id', '=', $_GET['org'])->select('stakeholders.id', DB::raw('CONCAT(name, " ", surnames) AS full_name'))->orderBy('name')->lists('full_name', 'id');
$action_plan = \Ermtool\Action_plan::find($id);
//obtenemos todos los issues y el issue del plan de acción
$issue = DB::table('issues')->where('id', '=', $action_plan->issue_id)->select('id', 'name')->first();
$issues = \Ermtool\Issue::lists('name', 'id');
if (Session::get('languaje') == 'en') {
return view('en.planes_accion.edit', ['org' => $org, 'org_id' => $org_id, 'action_plan' => $action_plan, 'stakeholders' => $stakes, 'action_plan' => $action_plan, 'issues' => $issues, 'issue' => $issue]);
} else {
return view('planes_accion.edit', ['org' => $org, 'org_id' => $org_id, 'action_plan' => $action_plan, 'stakeholders' => $stakes, 'action_plan' => $action_plan, 'issues' => $issues, 'issue' => $issue]);
}
}
}
/**
* Show the form for editing the specified resource.
*
* @param int $id
* @return \Illuminate\Http\Response
*/
public function edit($id)
{
if (Auth::guest()) {
return view('login');
} else {
$types_selected = array();
$orgs_selected = array();
$stakeholder = \Ermtool\Stakeholder::find($id);
$organizations = \Ermtool\Organization::where('status', 0)->lists('name', 'id');
$roles = \Ermtool\Role::all()->lists('name', 'id');
$dv = ['0' => '0', '1' => '1', '2' => '2', '3' => '3', '4' => '4', '5' => '5', '6' => '6', '7' => '7', '8' => '8', '9' => '9', 'k' => 'k'];
//buscamos el o los tipos del stakeholder
$types = DB::table('role_stakeholder')->where('stakeholder_id', '=', $stakeholder->id)->select('role_id')->get();
$i = 0;
foreach ($types as $type) {
$types_selected[$i] = $type->role_id;
$i += 1;
}
//buscamos organizaciones del stakeholder
$orgs = DB::table('organization_stakeholder')->where('stakeholder_id', '=', $stakeholder->id)->select('organization_id')->get();
$i = 0;
foreach ($orgs as $org) {
$orgs_selected[$i] = $org->organization_id;
$i += 1;
}
//si es edit, campo rut estara bloqueado y no habrá required
$disabled = 'disabled';
if (Session::get('languaje') == 'en') {
return view('en.datos_maestros.stakeholders.edit', ['stakeholder' => $stakeholder, 'organizations' => $organizations, 'disabled' => $disabled, 'required' => '', 'roles' => $roles, 'dv' => $dv, 'types_selected' => $types_selected, 'orgs_selected' => $orgs_selected]);
} else {
return view('datos_maestros.stakeholders.edit', ['stakeholder' => $stakeholder, 'organizations' => $organizations, 'disabled' => $disabled, 'required' => '', 'roles' => $roles, 'dv' => $dv, 'types_selected' => $types_selected, 'orgs_selected' => $orgs_selected]);
}
}
}
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]);
}
}
}
}
/**
* Show the form for editing the specified resource.
*
* @param int $id
* @return \Illuminate\Http\Response
*/
public function edit($id)
{
if (Auth::guest()) {
return view('login');
} else {
$orgs_selected = array();
$subproceso = \Ermtool\Subprocess::find($id);
$procesos = \Ermtool\Process::where('status', 0)->lists('name', 'id');
//obtenemos organizaciones del subproceso
$orgs = DB::table('organization_subprocess')->where('subprocess_id', '=', $id)->select('organization_id')->get();
$i = 0;
foreach ($orgs as $org) {
$orgs_selected[$i] = $org->organization_id;
$i += 1;
}
//Seleccionamos subprocesos que pueden ser padres
$subprocesos = \Ermtool\Subprocess::where('subprocess_id', NULL)->where('status', 0)->where('id', '<>', $id)->lists('name', 'id');
$organizaciones = \Ermtool\Organization::where('status', 0)->lists('name', 'id');
if (Session::get('languaje') == 'en') {
return view('en.datos_maestros.subprocesos.edit', ['procesos' => $procesos, 'subprocesos' => $subprocesos, 'subproceso' => $subproceso, 'organizaciones' => $organizaciones, 'orgs_selected' => $orgs_selected]);
} else {
return view('datos_maestros.subprocesos.edit', ['procesos' => $procesos, 'subprocesos' => $subprocesos, 'subproceso' => $subproceso, 'organizaciones' => $organizaciones, 'orgs_selected' => $orgs_selected]);
}
}
}
public function generarReporteIssues()
{
if (Auth::guest()) {
return view('login');
} else {
//volvemos a obtener lista de organizaciones
$organizations = \Ermtool\Organization::lists('name', 'id');
//obtenemos nombre de organización
$org = \Ermtool\Organization::where('id', $_GET['organization_id'])->value('name');
$org_id = $_GET['organization_id'];
$issues = array();
$issues = $this->getIssues($_GET['kind'], $_GET['organization_id'], 2);
//print_r($_POST);
if (Session::get('languaje') == 'en') {
return view('en.reportes.hallazgos', ['issues' => $issues, 'kind' => $_GET['kind'], 'organizations' => $organizations, 'org' => $org, 'org_id' => $org_id]);
} else {
return view('reportes.hallazgos', ['issues' => $issues, 'kind' => $_GET['kind'], 'organizations' => $organizations, 'org' => $org, 'org_id' => $org_id]);
}
}
}
public function evaluacionManual()
{
if (Auth::guest()) {
return view('login');
} else {
//obtenemos organizaciones
$organizations = \Ermtool\Organization::where('status', 0)->lists('name', 'id');
if (Session::get('languaje') == 'en') {
return view('en.evaluacion.evaluacion_manual', ['organizations' => $organizations]);
} else {
return view('evaluacion.evaluacion_manual', ['organizations' => $organizations]);
}
}
}
public function generarReporteAuditorias($org)
{
if (Auth::guest()) {
return view('login');
} else {
$organizations = \Ermtool\Organization::where('status', 0)->lists('name', 'id');
if (strstr($_SERVER["REQUEST_URI"], 'genexcelaudit')) {
$org_id = $org;
} else {
$org_id = $_GET['organization_id'];
}
$results = array();
$i = 0;
$audit_plans = DB::table('audit_plans')->where('audit_plans.organization_id', '=', $org_id)->select('id', 'name', 'description', 'objectives', 'scopes', 'status', 'resources', 'methodology', 'initial_date', 'final_date', 'rules')->get();
foreach ($audit_plans as $plan) {
if ($plan->objectives == "" || $plan->objectives == NULL) {
if (Session::get('languaje') == 'en') {
$objectives = 'No objectives have been added';
} else {
$objectives = 'No se agregaron objetivos';
}
} else {
$objectives = $plan->objectives;
}
if ($plan->scopes == "" || $plan->scopes == NULL) {
if (Session::get('languaje') == 'en') {
$scopes = 'No scopes have been added';
} else {
$scopes = 'No se agregaron alcances';
}
} else {
$scopes = $plan->scopes;
}
if ($plan->resources == "" || $plan->resources == NULL) {
if (Session::get('languaje') == 'en') {
$resources = 'No resources have been added';
} else {
$resources = 'No se agregaron recursos';
}
} else {
$resources = $plan->resources;
}
if ($plan->methodology == "" || $plan->methodology == NULL) {
if (Session::get('languaje') == 'en') {
$methodology = 'No methodology have been added';
} else {
$methodology = 'No se agregó metodología';
}
} else {
$methodology = $plan->methodology;
}
if ($plan->rules == "" || $plan->rules == NULL) {
if (Session::get('languaje') == 'en') {
$rules = 'No rules have been added';
} else {
$rules = 'No se agregaron reglas';
}
} else {
$rules = $plan->rules;
}
/*
if ($plan->hh == "" || $plan->hh == NULL)
{
if (Session::get('languaje') == 'en')
{
$hh = 'No hour-man have been added';
}
else
{
$hh = 'No se agregaron horas-hombre';
}
}
else
$hh = $plan->hh;
*/
//obtenemos horas hombre a través de las pruebas de auditoría
$hh = \Ermtool\Audit_plan::getHH($plan->id);
$hh_plan = 0;
$hh_real = 0;
foreach ($hh as $h) {
if ($h->hh_plan != NULL) {
$hh_plan = $hh_plan + $h->hh_plan;
}
if ($h->hh_real != NULL) {
$hh_real = $hh_real + $h->hh_real;
}
}
$initial_date = new DateTime($plan->initial_date);
$initial_date = date_format($initial_date, 'd-m-Y');
//¡¡¡¡¡¡¡¡¡corregir problema del año 2038!!!!!!!!!!!! //
$fecha_final = date('d-m-Y', strtotime($plan->final_date));
$final_date = new DateTime($plan->final_date);
$final_date = date_format($final_date, 'd-m-Y');
if ($plan->status == 0) {
if (Session::get('languaje') == 'en') {
$estado_plan = 'Open';
} else {
$estado_plan = 'Abierto';
}
} else {
if ($plan->status == 1) {
if (Session::get('languaje') == 'en') {
$estado_plan = 'Closed';
} else {
$estado_plan = 'Cerrado';
}
} else {
if (Session::get('languaje') == 'en') {
$estado_plan = 'Error obtaining status';
} else {
$estado_plan = 'Error al obtener estado';
}
}
}
//obtenemos auditorías
$audits = DB::table('audits')->join('audit_audit_plan', 'audit_audit_plan.audit_id', '=', 'audits.id')->where('audit_audit_plan.audit_plan_id', '=', $plan->id)->select('name', 'audit_audit_plan.id')->get();
if (strstr($_SERVER["REQUEST_URI"], 'genexcelaudit')) {
$auditorias = "";
$last = end($audits);
//guardamos final para no agregarle coma
foreach ($audits as $audit) {
if ($audit != $last) {
$auditorias .= $audit->name . ', ';
} else {
$auditorias .= $audit->name;
}
}
} else {
$j = 0;
$auditorias = array();
foreach ($audits as $audit) {
$auditorias[$j] = ['id' => $audit->id, 'name' => $audit->name];
$j += 1;
}
}
if (Session::get('languaje') == 'en') {
$results[$i] = ['Audit_plan' => $plan->name, 'Description' => $plan->description, 'Audits' => $auditorias, 'Objectives' => $objectives, 'Scopes' => $scopes, 'Resources' => $resources, 'Methodology' => $methodology, 'Rules' => $rules, 'Hours_man_plan' => $hh_plan, 'Hours_man_real' => $hh_real, 'Initial_date' => $initial_date, 'Final_date' => $final_date];
} else {
$results[$i] = ['Plan_de_auditoría' => $plan->name, 'Descripción_plan' => $plan->description, 'Auditorías' => $auditorias, 'Objetivos' => $objectives, 'Alcances' => $scopes, 'Recursos' => $resources, 'Metodología' => $methodology, 'Normas' => $rules, 'Horas_hombre_plan' => $hh_plan, 'Horas_hombre_real' => $hh_real, 'Fecha_inicio' => $initial_date, 'Fecha_fin' => $final_date];
}
$i += 1;
}
if (strstr($_SERVER["REQUEST_URI"], 'genexcelaudit')) {
return $results;
} else {
if (Session::get('languaje') == 'en') {
return view('en.reportes.auditorias', ['audit_plans' => $results, 'organizations' => $organizations, 'org_selected' => $_GET['organization_id']]);
} else {
return view('reportes.auditorias', ['audit_plans' => $results, 'organizations' => $organizations, 'org_selected' => $_GET['organization_id']]);
}
}
}
}
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
if (Auth::guest()) {
return view('login');
} else {
try {
$organizations = \Ermtool\Organization::where('status', 0)->lists('name', 'id');
//select organizaciones desbloqueadas en lista para select
} catch (\Exception $e) {
return response()->view('errors.query');
}
/*
if (isset($_GET['organizacion'])) //se seleccionó la organización para ver objetivos
{
//vemos si la organización seleccionada tiene algún plan activo
$strategic_plan = \Ermtool\Strategic_plan::where('status',1)->where('organization_id',$_GET['organizacion'])->select('id')->first();
if ($strategic_plan != "")
{
$objetivos = \Ermtool\Objective::where('organization_id',(int)$_GET['organizacion'])
->where('strategic_plan_id',$strategic_plan['id'])
->where('status',0)->get();
//seleccionamos todos los datos del plan para mostrarlo
$datos_plan = \Ermtool\Strategic_plan::find($strategic_plan['id']);
$nombre_organizacion = \Ermtool\Organization::name($_GET['organizacion']);
$i=0; //para saber si hay objetivos
$objectives = array(); //almacenará los objetivos con el formato correcto de sus atributos
foreach ($objetivos as $objetivo)
{
$i = $i+1;
//damos formato a fecha expiración
if ($objetivo['expiration_date'] == NULL OR $objetivo['expiration_date'] == "0000-00-00")
{
$fecha_exp = NULL;
}
else
{
$expiration_date = new DateTime($objetivo['expiration_date']);
$fecha_exp = date_format($expiration_date, 'd-m-Y');
}
//damos formato a fecha creación
if ($objetivo['created_at'] != NULL)
{
$fecha_creacion = date_format($objetivo['created_at'],"d-m-Y");
}
else
$fecha_creacion = NULL;
//damos formato a fecha de actualización
if ($objetivo['updated_at'] != NULL)
{
$fecha_act = date_format($objetivo['updated_at'],"d-m-Y");
}
else
$fecha_act = NULL;
//damos formato a categoría de objetivo
if ($objetivo['objective_category_id'] == NULL)
{
$categoria = NULL;
}
else
$categoria = \Ermtool\Objective_category::where('id',$objetivo['objective_category_id'])->value('name');
if ($objetivo['perspective'] == NULL)
{
$perspective = NULL;
}
else
{
$perspective = $objetivo['perspective'];
}
$objectives[$i] = array('id'=>$objetivo['id'],
'nombre'=>$objetivo['name'],
'descripcion'=>$objetivo['description'],
'fecha_creacion'=>$fecha_creacion,
'fecha_act'=>$fecha_act,
'fecha_exp'=>$fecha_exp,
'categoria'=>$categoria,
'estado'=>$objetivo['status'],
'perspective' => $perspective);
$i += 1;
}
if (Session::get('languaje') == 'en')
{
return view('en.datos_maestros.objetivos.index',['organizations'=>$organizations,'objetivos'=>$objectives,'nombre_organizacion'=>$nombre_organizacion,'datos_plan' => $datos_plan, 'probador' => $i,'strategic_plan_id' => $strategic_plan['id']]);
}
else
{
return view('datos_maestros.objetivos.index',['organizations'=>$organizations,'objetivos'=>$objectives,'nombre_organizacion'=>$nombre_organizacion,'datos_plan' => $datos_plan, 'probador' => $i,'strategic_plan_id' => $strategic_plan['id']]);
}
}
else
{
if (Session::get('languaje') == 'en')
{
return view('en.datos_maestros.objetivos.index',['organizations'=>$organizations,'validador' => 1]);
}
else
{
return view('datos_maestros.objetivos.index',['organizations'=>$organizations, 'validador' => 1]);
}
}
} */
//se dejará el if anterior (comentado) en caso que volviera a ser necesario, sin embargo expiró en 04-10-2016; actualmente los objetivos serán vistos por id de plan estratégico (a través de la función objetivosPlan)
if (Session::get('languaje') == 'en') {
return view('en.datos_maestros.objetivos.index', ['organizations' => $organizations]);
} else {
return view('datos_maestros.objetivos.index', ['organizations' => $organizations]);
}
}
}
public function kpiCreate($id)
{
if (Auth::guest()) {
return view('login');
} else {
//obtenemos todos los objetivos de la organización
$objectives = \Ermtool\Objective::where('objectives.organization_id', '=', $id)->join('strategic_plans', 'strategic_plans.id', '=', 'objectives.strategic_plan_id')->where('strategic_plans.status', 1)->where('objectives.status', 0)->lists('objectives.name', 'objectives.id');
$org_selected = \Ermtool\Organization::where('id', $id)->value('name');
$stakeholders = \Ermtool\Stakeholder::where('status', 0)->select('id', DB::raw('CONCAT(name, " ", surnames) AS full_name'))->orderBy('name')->lists('full_name', 'id');
if (Session::get('languaje') == 'en') {
return view('en.gestion_estrategica.createkpi', ['objectives' => $objectives, 'org_selected' => $org_selected, 'org_id' => $id, 'stakeholders' => $stakeholders]);
} else {
return view('gestion_estrategica.createkpi', ['objectives' => $objectives, 'org_selected' => $org_selected, 'org_id' => $id, 'stakeholders' => $stakeholders]);
}
}
}
public function generarMatriz($value, $org)
{
if (Auth::guest()) {
return view('login');
} else {
$organizations = \Ermtool\Organization::where('status', 0)->lists('name', 'id');
if (!strstr($_SERVER["REQUEST_URI"], 'genexcel')) {
$value = $_GET['kind'];
$org = $_GET['organization_id'];
}
$i = 0;
//contador de controles/subprocesos o controles/objetivos
$datos = array();
if (Session::get('languaje') == 'en') {
$proba_string = ['Very low', 'Low', 'Medium', 'High', 'Very high'];
$impact_string = ['Very low', 'Low', 'Medium', 'High', 'Very high'];
} else {
$proba_string = ['Muy poco probable', 'Poco probable', 'Intermedio', 'Probable', 'Muy probable'];
$impact_string = ['Muy poco impacto', 'Poco impacto', 'Intermedio', 'Alto impacto', 'Muy alto impacto'];
}
if ($value == 0) {
//---------- OBS: EXISTE PROBLEMA SI ES QUE EL RIESGO NO CONTIENE CAUSA Y EFECTO --------//
$risks = DB::table('risk_subprocess')->join('subprocesses', 'subprocesses.id', '=', 'risk_subprocess.subprocess_id')->join('organization_subprocess', 'organization_subprocess.subprocess_id', '=', 'subprocesses.id')->join('processes', 'subprocesses.process_id', '=', 'processes.id')->join('risks', 'risks.id', '=', 'risk_subprocess.risk_id')->join('risk_categories', 'risk_categories.id', '=', 'risks.risk_category_id')->where('risks.type2', '=', 1)->where('organization_subprocess.organization_id', '=', $org)->select('risks.*', 'subprocesses.name as subprocess_name', 'processes.name as process_name', 'risk_categories.name as risk_category_name', 'risk_subprocess.id as risk_subprocess_id')->get();
} else {
if ($value == 1) {
$risks = DB::table('objective_risk')->join('objectives', 'objectives.id', '=', 'objective_risk.objective_id')->join('risks', 'risks.id', '=', 'objective_risk.risk_id')->join('risk_categories', 'risk_categories.id', '=', 'risks.risk_category_id')->join('organizations', 'organizations.id', '=', 'objectives.organization_id')->where('risks.type2', '=', 1)->where('objectives.organization_id', '=', $org)->select('risks.*', 'objectives.name as objective_name', 'organizations.name as organization_name', 'risk_categories.name as risk_category_name', 'objective_risk.id as objective_risk_id')->get();
}
}
foreach ($risks as $risk) {
$controles = NULL;
$causas = NULL;
$efectos = NULL;
if (Session::get('languaje') == 'en') {
$probabilidad = "No evaluation";
$impacto = "No evaluation";
$score = "No evaluation";
} else {
$probabilidad = "No tiene evaluación";
$impacto = "No tiene evaluación";
$score = "No tiene evaluación";
}
// -- seteamos datos --//
//seteamos causa y efecto
//obtenemos causas
$causes = DB::table('cause_risk')->join('causes', 'causes.id', '=', 'cause_risk.cause_id')->where('risk_id', '=', $risk->id)->select('causes.name')->get();
if ($causes) {
$last = end($causes);
//guardamos final para no agregarle coma
foreach ($causes as $cause) {
if ($cause != $last) {
$causas .= $cause->name . ', ';
} else {
$causas .= $cause->name;
}
}
} else {
//se realizarán acá los textos (y no en la vista) para el caso en que se esté exportando a excel
if (Session::get('languaje') == 'en') {
$causas = "No defined cause";
} else {
$causas = "No tiene causas definidas";
}
}
//obtenemos efectos
$effects = DB::table('effect_risk')->join('effects', 'effects.id', '=', 'effect_risk.effect_id')->where('risk_id', '=', $risk->id)->select('effects.name')->get();
if ($effects) {
$last = end($effects);
//guardamos final para no agregarle coma
foreach ($effects as $effect) {
if ($effect != $last) {
$efectos .= $effect->name . ', ';
} else {
$efectos .= $effect->name;
}
}
} else {
if (Session::get('languaje') == 'en') {
$efectos = "No defined effects";
} else {
$efectos = "No tiene efectos definidos";
}
}
if ($value == 0) {
//primero obtenemos maxima fecha de evaluacion para el riesgo
$fecha = DB::table('evaluation_risk')->join('evaluations', 'evaluations.id', '=', 'evaluation_risk.evaluation_id')->where('evaluation_risk.risk_subprocess_id', '=', $risk->risk_subprocess_id)->max('evaluations.updated_at');
//obtenemos proba, impacto y score
$eval_risk = DB::table('evaluation_risk')->join('evaluations', 'evaluations.id', '=', 'evaluation_risk.evaluation_id')->where('evaluation_risk.risk_subprocess_id', '=', $risk->risk_subprocess_id)->where('evaluations.updated_at', '=', $fecha)->select('evaluation_risk.avg_probability', 'evaluation_risk.avg_impact')->get();
foreach ($eval_risk as $eval) {
if ($eval->avg_probability != NULL and $eval->avg_impact != NULL) {
$impacto = $eval->avg_impact . ' (' . $impact_string[$eval->avg_impact - 1] . ')';
$probabilidad = $eval->avg_probability . ' (' . $proba_string[$eval->avg_probability - 1] . ')';
$score = $impacto * $probabilidad;
}
}
//obtenemos controles
$controls = DB::table('controls')->join('control_risk_subprocess', 'control_risk_subprocess.control_id', '=', 'controls.id')->join('risk_subprocess', 'risk_subprocess.id', '=', 'control_risk_subprocess.risk_subprocess_id')->join('risks', 'risks.id', '=', 'risk_subprocess.risk_id')->where('risks.id', '=', $risk->id)->select('controls.name')->get();
} else {
if ($value == 1) {
//primero obtenemos maxima fecha de evaluacion para el riesgo
$fecha = DB::table('evaluation_risk')->join('evaluations', 'evaluations.id', '=', 'evaluation_risk.evaluation_id')->where('evaluation_risk.objective_risk_id', '=', $risk->objective_risk_id)->max('evaluations.updated_at');
//obtenemos proba, impacto y score
$eval_risk = DB::table('evaluation_risk')->join('evaluations', 'evaluations.id', '=', 'evaluation_risk.evaluation_id')->where('evaluation_risk.objective_risk_id', '=', $risk->objective_risk_id)->where('evaluations.updated_at', '=', $fecha)->select('evaluation_risk.avg_probability', 'evaluation_risk.avg_impact')->get();
foreach ($eval_risk as $eval) {
$impacto = $eval->avg_impact;
$probabilidad = $eval->avg_probability;
$score = $impacto * $probabilidad;
}
//obtenemos controles
$controls = DB::table('controls')->join('control_objective_risk', 'control_objective_risk.control_id', '=', 'controls.id')->join('objective_risk', 'objective_risk.id', '=', 'control_objective_risk.objective_risk_id')->join('risks', 'risks.id', '=', 'objective_risk.risk_id')->where('risks.id', '=', $risk->id)->select('controls.name')->get();
}
}
//seteamos controles
if ($controls == NULL) {
if (Session::get('languaje') == 'en') {
$controles = "No controls specified";
} else {
$controles = "No se han especificado controles";
}
} else {
$last = end($controls);
//guardamos final para no agregarle coma
foreach ($controls as $control) {
if ($control != $last) {
$controles .= $control->name . ', ';
} else {
$controles .= $control->name;
}
}
}
/* IMPORTANTE!!!
Los nombres de las variables serán guardados en español para mostrarlos
en el archivo excel que será exportado
*/
//damos formato a fecha de creación (se verifica si no es NULL en caso de algún error en la creación)
if ($risk->created_at == NULL or $risk->created_at == "0000-00-00" or $risk->created_at == "") {
if (Session::get('languaje') == 'en') {
$fecha_creacion = "Failed to register creation date";
} else {
$fecha_creacion = "Error al registrar fecha de creación";
}
} else {
//primero sacamos la hora
$fecha_temp1 = explode(' ', $risk->created_at);
//sacamos solo fecha y ordenamos
$fecha_temp2 = explode('-', $fecha_temp1[0]);
//ponemos fecha
$fecha_creacion = $fecha_temp2[2] . '-' . $fecha_temp2[1] . '-' . $fecha_temp2[0] . ' a las ' . $fecha_temp1[1];
}
//damos formato a fecha expiración
if ($risk->expiration_date == NULL or $risk->expiration_date == "0000-00-00") {
if (Session::get('languaje') == 'en') {
$expiration_date = "None";
} else {
$expiration_date = "Ninguna";
}
} else {
//sacamos solo fecha y ordenamos
$fecha_temp1 = explode('-', $risk->expiration_date);
$expiration_date = $fecha_temp1[2] . '-' . $fecha_temp1[1] . '-' . $fecha_temp1[0];
}
if ($risk->expected_loss == 0 || $risk->expected_loss == NULL) {
if (Session::get('languaje') == 'en') {
$expected_loss = "Not assigned expected loss";
} else {
$expected_loss = "No se ha asignado pérdida esperada";
}
} else {
$expected_loss = $risk->expected_loss;
}
//Seteamos datos
if ($value == 0) {
if (Session::get('languaje') == 'en') {
$datos[$i] = ['Process' => $risk->process_name, 'Subprocess' => $risk->subprocess_name, 'Risk' => $risk->name, 'Description' => $risk->description, 'Category' => $risk->risk_category_name, 'Causes' => $causas, 'Effects' => $efectos, 'Expected_loss' => $expected_loss, 'Probability' => $probabilidad, 'Impact' => $impacto, 'Score' => $score, 'Identification_date' => $fecha_creacion, 'Expiration_date' => $expiration_date, 'Controls' => $controles];
} else {
$datos[$i] = ['Proceso' => $risk->process_name, 'Subproceso' => $risk->subprocess_name, 'Riesgo' => $risk->name, 'Descripción' => $risk->description, 'Categoría' => $risk->risk_category_name, 'Causas' => $causas, 'Efectos' => $efectos, 'Pérdida_esperada' => $expected_loss, 'Probabilidad' => $probabilidad, 'Impacto' => $impacto, 'Score' => $score, 'Fecha_identificación' => $fecha_creacion, 'Fecha_expiración' => $expiration_date, 'Controles' => $controles];
}
$i += 1;
} else {
if ($value == 1) {
if (Session::get('languaje') == 'en') {
$datos[$i] = ['Organization' => $risk->organization_name, 'Objective' => $risk->objective_name, 'Risk' => $risk->name, 'Description' => $risk->description, 'Category' => $risk->risk_category_name, 'Causes' => $causas, 'Effects' => $efectos, 'Expected_loss' => $risk->expected_loss, 'Probability' => $probabilidad, 'Impact' => $impacto, 'Score' => $score, 'Identification_date' => $fecha_creacion, 'Expiration_date' => $expiration_date, 'Controls' => $controles];
} else {
$datos[$i] = ['Organización' => $risk->organization_name, 'Objetivo' => $risk->objective_name, 'Riesgo' => $risk->name, 'Descripción' => $risk->description, 'Categoría' => $risk->risk_category_name, 'Causas' => $causas, 'Efectos' => $efectos, 'Pérdida_esperada' => $risk->expected_loss, 'Probabilidad' => $probabilidad, 'Impacto' => $impacto, 'Score' => $score, 'Fecha_identificación' => $fecha_creacion, 'Fecha_expiración' => $expiration_date, 'Controles' => $controles];
}
$i += 1;
}
}
}
if (strstr($_SERVER["REQUEST_URI"], 'genexcel')) {
return $datos;
} else {
if (Session::get('languaje') == 'en') {
return view('en.reportes.matriz_riesgos', ['datos' => $datos, 'value' => $value, 'organizations' => $organizations, 'org_selected' => $org]);
} else {
return view('reportes.matriz_riesgos', ['datos' => $datos, 'value' => $value, 'organizations' => $organizations, 'org_selected' => $org]);
}
//return json_encode($datos);
}
}
}
