public function getIssue($eval_id)
{
$issue = NULL;
$eval = DB::table('control_evaluation')->where('id', '=', $eval_id)->where('status', '=', 1)->select('issue_id')->first();
$evidence = getEvidences(3, $eval_id);
if ($eval) {
$issue = \Ermtool\Issue::find($eval->issue_id);
$issue = ['issue' => $issue, 'evidence' => $evidence];
}
return json_encode($issue);
}
public static function getIssueByControlEvaluation($id)
{
$results = array();
$issues = DB::table('issues')->where('issues.control_evaluation_id', '=', $id)->select('issues.id', 'issues.name', 'issues.description', 'issues.recommendations', 'issues.classification')->get();
$i = 0;
foreach ($issues as $issue) {
//para cada issue obtenemos plan de acción (si es que hay)
$plan = DB::table('action_plans')->where('issue_id', '=', $issue->id)->select('description', 'final_date', 'status')->first();
//obtenemos evidencias de issue (si es que existen)
$evidences = getEvidences(2, $issue->id);
if ($plan != NULL) {
$results[$i] = ['id' => $issue->id, 'name' => $issue->name, 'description' => $issue->description, 'recommendations' => $issue->recommendations, 'classification' => $issue->classification, 'evidences' => $evidences, 'plan_description' => $plan->description, 'plan_final_date' => $plan->final_date, 'plan_status' => $plan->status];
} else {
$results[$i] = ['id' => $issue->id, 'name' => $issue->name, 'description' => $issue->description, 'recommendations' => $issue->recommendations, 'classification' => $issue->classification, 'evidences' => $evidences, 'plan_description' => NULL, 'plan_final_date' => NULL, 'plan_status' => NULL];
}
$i += 1;
}
return $results;
}
public function getIssues($kind, $org_id, $kind2)
{
$issues = array();
$datos = array();
//se usará sólo para reportes
if ($kind == 0 || $kind == 1) {
//primero seleccionamos los hallazgos obtenidos a través de la evaluación de controles
$issues1 = DB::table('control_evaluation')->join('controls', 'controls.id', '=', 'control_evaluation.control_id')->join('control_risk_subprocess', 'control_risk_subprocess.control_id', '=', 'controls.id')->join('risk_subprocess', 'risk_subprocess.id', '=', 'control_risk_subprocess.risk_subprocess_id')->join('organization_subprocess', 'organization_subprocess.subprocess_id', '=', 'risk_subprocess.subprocess_id')->join('issues', 'issues.control_evaluation_id', '=', 'control_evaluation.id')->where('organization_subprocess.organization_id', '=', $org_id)->select('issues.id', 'issues.name as issue_name', 'issues.classification', 'issues.recommendations', 'risk_subprocess.id as subobj_id')->distinct()->groupBy('issues.id')->get();
//ahora los hallazgos generados a través de auditoría orientada a procesos
$issues2 = DB::table('issues')->join('audit_tests', 'audit_tests.id', '=', 'issues.audit_test_id')->join('subprocesses', 'subprocesses.id', '=', 'audit_tests.subprocess_id')->join('risk_subprocess', 'risk_subprocess.subprocess_id', '=', 'subprocesses.id')->join('organization_subprocess', 'organization_subprocess.subprocess_id', '=', 'subprocesses.id')->where('organization_subprocess.organization_id', '=', $org_id)->distinct()->groupBy('issues.id')->select('issues.id', 'issues.name as issue_name', 'issues.classification', 'issues.recommendations', 'risk_subprocess.id as subobj_id')->get();
//hallazgos de auditoría orientados a riesgos (de proceso)
$issues3 = DB::table('audit_tests')->join('issues', 'issues.audit_test_id', '=', 'audit_tests.id')->join('risk_subprocess', 'risk_subprocess.risk_id', '=', 'audit_tests.risk_id')->join('organization_subprocess', 'organization_subprocess.subprocess_id', '=', 'risk_subprocess.subprocess_id')->where('organization_subprocess.organization_id', '=', $org_id)->select('issues.id', 'issues.name as issue_name', 'issues.classification', 'issues.recommendations', 'risk_subprocess.id as subobj_id')->distinct()->groupBy('issues.id')->get();
//hallazgos de auditoría con pruebas de controles (controles orientados a subproceso)
$issues4 = DB::table('audit_tests')->join('issues', 'issues.audit_test_id', '=', 'audit_tests.id')->join('control_risk_subprocess', 'control_risk_subprocess.control_id', '=', 'audit_tests.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', '=', $org_id)->select('issues.id', 'issues.name as issue_name', 'issues.classification', 'issues.recommendations', 'risk_subprocess.id as subobj_id')->distinct()->groupBy('issues.id')->get();
}
if ($kind == 0) {
//hallazgos de proceso creados directamente
$issues5 = DB::table('issues')->join('processes', 'processes.id', '=', 'issues.process_id')->join('subprocesses', 'subprocesses.process_id', '=', 'processes.id')->join('risk_subprocess', 'risk_subprocess.subprocess_id', '=', 'subprocesses.id')->join('organization_subprocess', 'organization_subprocess.subprocess_id', '=', 'subprocesses.id')->where('organization_subprocess.organization_id', '=', $org_id)->whereNotNull('issues.process_id')->select('issues.id', 'issues.name as issue_name', 'issues.classification', 'issues.recommendations', 'risk_subprocess.id as subobj_id')->distinct()->groupBy('issues.id')->get();
} else {
if ($kind == 1) {
//Hallazgos de subproceso creados directamente
$issues5 = DB::table('issues')->join('subprocesses', 'subprocesses.id', '=', 'issues.subprocess_id')->join('risk_subprocess', 'risk_subprocess.subprocess_id', '=', 'subprocesses.id')->join('organization_subprocess', 'organization_subprocess.subprocess_id', '=', 'subprocesses.id')->where('organization_subprocess.organization_id', '=', $org_id)->whereNotNull('issues.subprocess_id')->select('issues.id', 'issues.name as issue_name', 'issues.classification', 'issues.recommendations', 'risk_subprocess.id as subobj_id')->distinct()->groupBy('issues.id')->get();
} else {
if ($kind == 2) {
//CORRECCIÓN 08-11-2016: SÓLO SE MOSTRARÁN LOS ISSUES PARA LAS ORGANIZACIONES DIRECTAMENTE => PARA EVITAR MAYORES CAMBIOS QUE PUEDAN CONLLEVAR ERRORERS,
//LAS VARIABLES DE ISSUES SERÁN ENVIADAS PERO VACÍAS
//seleccionamos los hallazgos obtenidos a través de la evaluación de controles (que sean para controles de riesgos de negocio)
/*$issues1 = DB::table('control_evaluation')
->join('controls','controls.id','=','control_evaluation.control_id')
->join('control_objective_risk','control_objective_risk.control_id','=','controls.id')
->join('objective_risk','objective_risk.id','=','control_objective_risk.objective_risk_id')
->join('objectives','objectives.id','=','objective_risk.objective_id')
->join('issues','issues.id','=','control_evaluation.issue_id')
->where('objectives.organization_id','=',$org_id)
->select('issues.id','issues.name as issue_name','issues.classification',
'issues.recommendations','objective_risk.id as subobj_id')
->distinct()
->groupBy('issues.id')
->get();
//hallazgos de auditoría orientados a riesgos (de negocio)
$issues2 = DB::table('audit_tests')
->join('issues','issues.audit_test_id','=','audit_tests.id')
->join('objective_risk','objective_risk.risk_id','=','audit_tests.risk_id')
->join('objectives','objectives.id','=','objective_risk.objective_id')
->where('objectives.organization_id','=',$org_id)
->select('issues.id','issues.name as issue_name','issues.classification',
'issues.recommendations','objective_risk.id as subobj_id')
->distinct()
->groupBy('issues.id')
->get();
//hallazgos de auditoría con pruebas de controles (controles orientados a objetivos)
$issues3 = DB::table('audit_tests')
->join('issues','issues.audit_test_id','=','audit_tests.id')
->join('control_objective_risk','control_objective_risk.control_id','=','audit_tests.control_id')
->join('objective_risk','objective_risk.id','=','control_objective_risk.objective_risk_id')
->join('objectives','objectives.id','=','objective_risk.objective_id')
->where('objectives.organization_id','=',$org_id)
->select('issues.id','issues.name as issue_name','issues.classification',
'issues.recommendations','objective_risk.id as subobj_id')
->distinct()
->groupBy('issues.id')
->get(); */
$issues4 = array();
$issues2 = array();
$issues3 = array();
//hallazgos de organización creados directamente
$issues1 = DB::table('issues')->where('issues.organization_id', '=', $org_id)->whereNotNull('issues.organization_id')->select('issues.id', 'issues.name as issue_name', 'issues.classification', 'issues.recommendations', 'issues.organization_id as subobj_id')->groupBy('issues.id')->get();
} else {
if ($kind == 3) {
//primero seleccionamos los hallazgos obtenidos a través de la evaluación de controles (riesgos de subproceso)
$issues1 = DB::table('control_evaluation')->join('controls', 'controls.id', '=', 'control_evaluation.control_id')->join('control_risk_subprocess', 'control_risk_subprocess.control_id', '=', 'controls.id')->join('risk_subprocess', 'risk_subprocess.id', '=', 'control_risk_subprocess.risk_subprocess_id')->join('organization_subprocess', 'organization_subprocess.subprocess_id', '=', 'risk_subprocess.subprocess_id')->join('issues', 'issues.control_evaluation_id', '=', 'control_evaluation.id')->where('organization_subprocess.organization_id', '=', $org_id)->select('issues.id', 'issues.name as issue_name', 'issues.classification', 'issues.recommendations', 'control_risk_subprocess.id as subobj_id')->distinct()->groupBy('issues.id')->get();
//hallazgos de auditoría con pruebas de controles (controles orientados a subprocesos)
$issues2 = DB::table('audit_tests')->join('issues', 'issues.audit_test_id', '=', 'audit_tests.id')->join('control_risk_subprocess', 'control_risk_subprocess.control_id', '=', 'audit_tests.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', '=', $org_id)->select('issues.id', 'issues.name as issue_name', 'issues.classification', 'issues.recommendations', 'control_risk_subprocess.id as subobj_id')->distinct()->groupBy('issues.id')->get();
//hallazgos de control creados directamente (controles de proceso)
$issues3 = DB::table('issues')->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', '=', $org_id)->whereNotNull('issues.control_id')->select('issues.id', 'issues.name as issue_name', 'issues.classification', 'issues.recommendations', 'control_risk_subprocess.id as subobj_id')->distinct()->groupBy('issues.id')->get();
$issues4 = array();
} else {
if ($kind == 4) {
//seleccionamos los hallazgos obtenidos a través de la evaluación de controles (que sean de la organización)
$issues1 = DB::table('control_evaluation')->join('controls', 'controls.id', '=', 'control_evaluation.control_id')->join('control_objective_risk', 'control_objective_risk.control_id', '=', 'controls.id')->join('objective_risk', 'objective_risk.id', '=', 'control_objective_risk.objective_risk_id')->join('objectives', 'objectives.id', '=', 'objective_risk.objective_id')->join('issues', 'issues.control_evaluation_id', '=', 'control_evaluation.id')->where('objectives.organization_id', '=', $org_id)->select('issues.id', 'issues.name as issue_name', 'issues.classification', 'issues.recommendations', 'control_objective_risk.id as subobj_id')->distinct()->groupBy('issues.id')->get();
//hallazgos de auditoría con pruebas de controles (controles orientados a objetivos)
$issues2 = DB::table('audit_tests')->join('issues', 'issues.audit_test_id', '=', 'audit_tests.id')->join('control_objective_risk', 'control_objective_risk.control_id', '=', 'audit_tests.control_id')->join('objective_risk', 'objective_risk.id', '=', 'control_objective_risk.objective_risk_id')->join('objectives', 'objectives.id', '=', 'objective_risk.objective_id')->where('objectives.organization_id', '=', $org_id)->select('issues.id', 'issues.name as issue_name', 'issues.classification', 'issues.recommendations', 'control_objective_risk.id as subobj_id')->distinct()->groupBy('issues.id')->get();
//hallazgos de control creados directamente (controles de negocio)
$issues3 = DB::table('issues')->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.objective_id')->where('objectives.organization_id', '=', $org_id)->whereNotNull('issues.control_id')->select('issues.id', 'issues.name as issue_name', 'issues.classification', 'issues.recommendations', 'control_objective_risk.id as subobj_id')->distinct()->groupBy('issues.id')->get();
$issues4 = array();
} else {
if ($kind == 5) {
//hallazgos de programa de auditoría creados directamente
$issues1 = DB::table('issues')->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', '=', $org_id)->whereNotNull('issues.audit_audit_plan_audit_program_id')->select('issues.id', 'issues.name as issue_name', 'issues.classification', 'issues.recommendations', 'audit_audit_plan_audit_program.id as subobj_id')->distinct()->groupBy('issues.id')->get();
} else {
if ($kind == 6) {
//hallazgos de auditoría creados directamente
$issues1 = DB::table('issues')->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', '=', $org_id)->whereNotNull('issues.audit_audit_plan_id')->select('issues.id', 'issues.name as issue_name', 'issues.classification', 'issues.recommendations', 'audit_audit_plan.id as subobj_id')->distinct()->groupBy('issues.id')->get();
}
}
}
}
}
}
}
$i = 0;
// dd($issues1);
foreach ($issues1 as $issue) {
//para cada issue obtenemos datos de plan de acción (si es que hay)
$plan = DB::table('action_plans')->where('issue_id', '=', $issue->id)->select('description', 'final_date', 'status')->first();
if ($plan != NULL) {
$temp = $this->formatearIssue($issue->id, $issue->issue_name, $issue->classification, $issue->recommendations, $plan->description, $plan->status, $plan->final_date);
} else {
$temp = $this->formatearIssue($issue->id, $issue->issue_name, $issue->classification, $issue->recommendations, NULL, NULL, NULL);
}
if ($kind2 == 2) {
$datos = $this->datosReporte($issue->subobj_id, $kind, NULL);
}
if (strstr($_SERVER["REQUEST_URI"], 'genexcelissues')) {
if (Session::get('languaje') == 'en') {
if ($kind == 0 || $kind == 1) {
$issues[$i] = ['Processes' => $datos['processes'], 'Subprocesses' => $datos['subprocesses'], 'Risks' => $datos['risks'], 'Controls' => $datos['controls'], 'Name' => $temp['name'], 'Classification' => $temp['classification'], 'Recommendations' => $temp['recommendations'], 'Action Plan' => $temp['plan'], 'Status' => $temp['status'], 'Plan Deadline' => $temp['final_date']];
} else {
if ($kind == 2) {
$issues[$i] = ['Objectives' => $datos['objectives'], 'Risks' => $datos['risks'], 'Controls' => $datos['controls'], 'Name' => $temp['name'], 'Classification' => $temp['classification'], 'Recommendations' => $temp['recommendations'], 'Action plan' => $temp['plan'], 'Status' => $temp['status'], 'Plan Deadline' => $temp['final_date']];
} else {
if ($kind == 3) {
$issues[$i] = ['Process' => $datos['processes'], 'Subprocesses' => $datos['subprocesses'], 'Risks' => $datos['risks'], 'Controls' => $datos['controls'], 'Name' => $temp['name'], 'Classification' => $temp['classification'], 'Recommendations' => $temp['recommendations'], 'Action Plan' => $temp['plan'], 'Status' => $temp['status'], 'Plan Deadline' => $temp['final_date']];
} else {
if ($kind == 4) {
$issues[$i] = ['Objectives' => $datos['objectives'], 'Risks' => $datos['risks'], 'Controls' => $datos['controls'], 'Name' => $temp['name'], 'Classification' => $temp['classification'], 'Recommendations' => $temp['recommendations'], 'Action Plan' => $temp['plan'], 'Status' => $temp['status'], 'Plan Deadline' => $temp['final_date']];
} else {
if ($kind == 5) {
$issues[$i] = ['Audit Plan' => $datos['audit_plan'], 'Audit' => $datos['audit'], 'Audit Program' => $datos['audit_program'], 'Name' => $temp['name'], 'Classification' => $temp['classification'], 'Recommendations' => $temp['recommendations'], 'Action Plan' => $temp['plan'], 'Status' => $temp['status'], 'Plan Deadline' => $temp['final_date']];
} else {
if ($kind == 6) {
$issues[$i] = ['Audit Plan' => $datos['audit_plan'], 'Audit' => $datos['audit'], 'Name' => $temp['name'], 'Classification' => $temp['classification'], 'Recommendations' => $temp['recommendations'], 'Action Plan' => $temp['plan'], 'Status' => $temp['status'], 'Plan Deadline' => $temp['final_date']];
}
}
}
}
}
}
} else {
if ($kind == 0 || $kind == 1) {
$issues[$i] = ['Procesos' => $datos['processes'], 'Subprocesos' => $datos['subprocesses'], 'Riesgos' => $datos['risks'], 'Controles' => $datos['controls'], 'Nombre' => $temp['name'], 'Clasificación' => $temp['classification'], 'Recomendaciones' => $temp['recommendations'], 'Plan de acción' => $temp['plan'], 'Estado' => $temp['status'], 'Fecha límite plan' => $temp['final_date']];
} else {
if ($kind == 2) {
$issues[$i] = ['Objetivos' => $datos['objectives'], 'Riesgos' => $datos['risks'], 'Controles' => $datos['controls'], 'Nombre' => $temp['name'], 'Clasificación' => $temp['classification'], 'Recomendaciones' => $temp['recommendations'], 'Plan de acción' => $temp['plan'], 'Estado' => $temp['status'], 'Fecha límite plan' => $temp['final_date']];
} else {
if ($kind == 3) {
$issues[$i] = ['Procesos' => $datos['processes'], 'Subprocesos' => $datos['subprocesses'], 'Riesgos' => $datos['risks'], 'Controles' => $datos['controls'], 'Nombre' => $temp['name'], 'Clasificación' => $temp['classification'], 'Recomendaciones' => $temp['recommendations'], 'Plan de acción' => $temp['plan'], 'Estado' => $temp['status'], 'Fecha límite plan' => $temp['final_date']];
} else {
if ($kind == 4) {
$issues[$i] = ['Objetivos' => $datos['objectives'], 'Riesgos' => $datos['risks'], 'Controles' => $datos['controls'], 'Nombre' => $temp['name'], 'Clasificación' => $temp['classification'], 'Recomendaciones' => $temp['recommendations'], 'Plan de acción' => $temp['plan'], 'Estado' => $temp['status'], 'Fecha límite plan' => $temp['final_date']];
} else {
if ($kind == 5) {
$issues[$i] = ['Plan de auditoría' => $datos['audit_plan'], 'Auditoría' => $datos['audit'], 'Programa de auditoría' => $datos['audit_program'], 'Nombre' => $temp['name'], 'Clasificación' => $temp['classification'], 'Recomendaciones' => $temp['recommendations'], 'Plan de acción' => $temp['plan'], 'Estado' => $temp['status'], 'Fecha límite plan' => $temp['final_date']];
} else {
if ($kind == 6) {
$issues[$i] = ['Plan de auditoría' => $datos['audit_plan'], 'Auditoría' => $datos['audit'], 'Nombre' => $temp['name'], 'Clasificación' => $temp['classification'], 'Recomendaciones' => $temp['recommendations'], 'Plan de acción' => $temp['plan'], 'Estado' => $temp['status'], 'Fecha límite plan' => $temp['final_date']];
}
}
}
}
}
}
}
} else {
//obtenemos posibles evidencias
$evidence = getEvidences(2, $temp['id']);
$origin = Issue::getOrigin($kind, $issue->subobj_id, $org_id);
$issues[$i] = ['id' => $temp['id'], 'origin' => $origin, 'name' => $temp['name'], 'classification' => $temp['classification'], 'recommendations' => $temp['recommendations'], 'plan' => $temp['plan'], 'status' => $temp['status'], 'status_origin' => $temp['status_origin'], 'final_date' => $temp['final_date'], 'datos' => $datos, 'evidence' => $evidence];
}
$i += 1;
}
if ($kind != 5 and $kind != 6) {
foreach ($issues2 as $issue) {
$plan = NULL;
//para cada issue obtenemos datos de plan de acción (si es que hay)
$plan = DB::table('action_plans')->where('issue_id', '=', $issue->id)->select('description', 'final_date', 'status')->get();
if ($plan != NULL) {
$temp = $this->formatearIssue($issue->id, $issue->issue_name, $issue->classification, $issue->recommendations, $plan[0]->description, $plan[0]->status, $plan[0]->final_date);
} else {
$temp = $this->formatearIssue($issue->id, $issue->issue_name, $issue->classification, $issue->recommendations, NULL, NULL, NULL);
}
if ($kind2 == 2) {
$datos = $this->datosReporte($issue->subobj_id, $kind, NULL);
}
if (strstr($_SERVER["REQUEST_URI"], 'genexcelissues')) {
if ($kind == 0 || $kind == 3) {
$issues[$i] = $this->setIssue1($datos['processes'], $datos['subprocesses'], $datos['risks'], $datos['controls'], $temp['name'], $temp['classification'], $temp['recommendations'], $temp['plan'], $temp['status'], $temp['final_date']);
} else {
if ($kind == 2 || $kind == 4) {
$issues[$i] = $this->setIssue2($datos['objectives'], $datos['risks'], $datos['controls'], $temp['name'], $temp['classification'], $temp['recommendations'], $temp['plan'], $temp['status'], $temp['final_date']);
}
}
} else {
//obtenemos posibles evidencias
$evidence = getEvidences(2, $temp['id']);
$origin = Issue::getOrigin($kind, $issue->subobj_id, $org_id);
$issues[$i] = ['id' => $temp['id'], 'origin' => $origin, 'name' => $temp['name'], 'classification' => $temp['classification'], 'recommendations' => $temp['recommendations'], 'plan' => $temp['plan'], 'status' => $temp['status'], 'status_origin' => $temp['status_origin'], 'final_date' => $temp['final_date'], 'datos' => $datos, 'evidence' => $evidence];
}
$i += 1;
}
foreach ($issues3 as $issue) {
$plan = NULL;
//para cada issue obtenemos datos de plan de acción (si es que hay)
$plan = DB::table('action_plans')->where('issue_id', '=', $issue->id)->select('description', 'final_date', 'status')->get();
if ($plan != NULL) {
$temp = $this->formatearIssue($issue->id, $issue->issue_name, $issue->classification, $issue->recommendations, $plan[0]->description, $plan[0]->status, $plan[0]->final_date);
} else {
$temp = $this->formatearIssue($issue->id, $issue->issue_name, $issue->classification, $issue->recommendations, NULL, NULL, NULL);
}
if ($kind2 == 2) {
$datos = $this->datosReporte($issue->subobj_id, $kind, NULL);
}
if (strstr($_SERVER["REQUEST_URI"], 'genexcelissues')) {
if ($kind == 0 || $kind == 3) {
$issues[$i] = $this->setIssue1($datos['processes'], $datos['subprocesses'], $datos['risks'], $datos['controls'], $temp['name'], $temp['classification'], $temp['recommendations'], $temp['plan'], $temp['status'], $temp['final_date']);
} else {
if ($kind == 2 || $kind == 4) {
$issues[$i] = $this->setIssue2($datos['objectives'], $datos['risks'], $datos['controls'], $temp['name'], $temp['classification'], $temp['recommendations'], $temp['plan'], $temp['status'], $temp['final_date']);
}
}
} else {
//obtenemos posibles evidencias
$evidence = getEvidences(2, $temp['id']);
$origin = Issue::getOrigin($kind, $issue->subobj_id, $org_id);
$issues[$i] = ['id' => $temp['id'], 'origin' => $origin, 'name' => $temp['name'], 'classification' => $temp['classification'], 'recommendations' => $temp['recommendations'], 'plan' => $temp['plan'], 'status' => $temp['status'], 'status_origin' => $temp['status_origin'], 'final_date' => $temp['final_date'], 'datos' => $datos, 'evidence' => $evidence];
}
$i += 1;
}
foreach ($issues4 as $issue) {
$plan = NULL;
//para cada issue obtenemos datos de plan de acción (si es que hay)
$plan = DB::table('action_plans')->where('issue_id', '=', $issue->id)->select('description', 'final_date', 'status')->get();
if ($plan != NULL) {
$temp = $this->formatearIssue($issue->id, $issue->issue_name, $issue->classification, $issue->recommendations, $plan[0]->description, $plan[0]->status, $plan[0]->final_date);
} else {
$temp = $this->formatearIssue($issue->id, $issue->issue_name, $issue->classification, $issue->recommendations, NULL, NULL, NULL);
}
if ($kind2 == 2) {
if ($kind == 2) {
$datos = $this->datosReporte($issue->subobj_id, $kind, 1);
}
}
if (strstr($_SERVER["REQUEST_URI"], 'genexcelissues')) {
if ($kind == 0) {
$issues[$i] = $this->setIssue1($datos['processes'], $datos['subprocesses'], $datos['risks'], $datos['controls'], $temp['name'], $temp['classification'], $temp['recommendations'], $temp['plan'], $temp['status'], $temp['final_date']);
} else {
if ($kind == 2) {
$issues[$i] = $this->setIssue2($datos['objectives'], $datos['risks'], $temp['controls'], $temp['name'], $temp['classification'], $temp['recommendations'], $temp['plan'], $temp['status'], $temp['final_date']);
}
}
} else {
//obtenemos posibles evidencias
$evidence = getEvidences(2, $temp['id']);
$origin = Issue::getOrigin($kind, $issue->subobj_id, $org_id);
$issues[$i] = ['id' => $temp['id'], 'origin' => $origin, 'name' => $temp['name'], 'classification' => $temp['classification'], 'recommendations' => $temp['recommendations'], 'plan' => $temp['plan'], 'status' => $temp['status'], 'status_origin' => $temp['status_origin'], 'final_date' => $temp['final_date'], 'datos' => $datos, 'evidence' => $evidence];
}
$i += 1;
}
if ($kind == 0 || $kind == 1) {
foreach ($issues5 as $issue) {
$plan = NULL;
//para cada issue obtenemos datos de plan de acción (si es que hay)
$plan = DB::table('action_plans')->where('issue_id', '=', $issue->id)->select('description', 'final_date', 'status')->get();
if ($plan != NULL) {
$temp = $this->formatearIssue($issue->id, $issue->issue_name, $issue->classification, $issue->recommendations, $plan[0]->description, $plan[0]->status, $plan[0]->final_date);
} else {
$temp = $this->formatearIssue($issue->id, $issue->issue_name, $issue->classification, $issue->recommendations, NULL, NULL, NULL);
}
if ($kind2 == 2) {
$datos = $this->datosReporte($issue->subobj_id, $kind, NULL);
}
if (strstr($_SERVER["REQUEST_URI"], 'genexcelissues')) {
$issues[$i] = $this->setIssue1($datos['processes'], $datos['subprocesses'], $datos['risks'], $datos['controls'], $temp['name'], $temp['classification'], $temp['recommendations'], $temp['plan'], $temp['status'], $temp['final_date']);
} else {
//obtenemos posibles evidencias
$evidence = getEvidences(2, $temp['id']);
$origin = Issue::getOrigin($kind, $issue->subobj_id, $org_id);
$issues[$i] = ['id' => $temp['id'], 'origin' => $origin, 'name' => $temp['name'], 'classification' => $temp['classification'], 'recommendations' => $temp['recommendations'], 'plan' => $temp['plan'], 'status' => $temp['status'], 'status_origin' => $temp['status_origin'], 'final_date' => $temp['final_date'], 'datos' => $datos, 'evidence' => $evidence];
}
$i += 1;
}
}
}
return $issues;
}
public function getNotes($id)
{
if (Auth::guest()) {
return view('login');
} else {
$results = array();
$i = 0;
$notes = DB::table('notes')->where('audit_test_id', '=', $id)->select('notes.id', 'notes.name', 'notes.description', 'notes.created_at', 'notes.status', 'notes.audit_test_id as test_id')->get();
if (empty($notes)) {
$results = NULL;
} else {
foreach ($notes as $note) {
//obtenemos respuestas a la nota (si es que existen)
$answers_notes = DB::table('notes_answers')->where('note_id', $note->id)->select('notes_answers.id', 'notes_answers.answer', 'notes_answers.created_at', 'notes_answers.updated_at')->get();
if (empty($answers_notes)) {
$answers = NULL;
} else {
$j = 0;
//contador de respuestas para las notas
//seteamos cada respuesta de la nota
foreach ($answers_notes as $ans) {
//obtenemos evidencias de la respuesta (si es que existen)
$evidences = getEvidences(1, $ans->id);
$answers[$j] = ['id' => $ans->id, 'answer' => $ans->answer, 'created_at' => $ans->created_at, 'updated_at' => $ans->updated_at, 'ans_evidences' => $evidences];
$j += 1;
}
}
//obtenemos evidencias de la nota (si es que existe)
$evidences = getEvidences(0, $note->id);
$fecha_creacion = date('d-m-Y', strtotime($note->created_at));
$fecha_creacion .= ' a las ' . date('H:i:s', strtotime($note->created_at));
$results[$i] = ['id' => $note->id, 'name' => $note->name, 'description' => $note->description, 'created_at' => $fecha_creacion, 'status' => $note->status, 'status_origin' => $note->status, 'test_id' => $note->test_id, 'answers' => $answers, 'evidences' => $evidences];
$i += 1;
}
}
return json_encode($results);
}
}
