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); } }