File: /home/cafsindia/ntc_cafsinfotech_in_bk/application/controllers/Incident_management.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Incident_management extends Action_controller{
public function __construct(){
parent::__construct('incident_management');
$this->collect_base_info();
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$data['quick_link'] = $this->quick_link;
$data['table_head'] = json_decode(json_encode($this->get_table_head()));
// INCIDENT STATUS
$this->db->select('prime_incident_status_id,incident_status');
$this->db->from('cw_incident_status');
$this->db->where('cw_incident_status.trans_status', 1);
$incident_status_rslt = $this->db->get()->result();
foreach($incident_status_rslt as $incident_rslt){
$prime_incident_status_id = $incident_rslt->prime_incident_status_id;
$incident_name = $incident_rslt->incident_status;
$incident_status[$prime_incident_status_id] = $incident_name;
}
$data['incident_status'] = $incident_status;
// INVESTIGATION STATUS
$this->db->select('prime_investigation_risk_status_id,investigation_risk_status');
$this->db->from('cw_investigation_risk_status');
$this->db->where('cw_investigation_risk_status.trans_status', 1);
$incident_status_rslt = $this->db->get()->result();
foreach($incident_status_rslt as $rlst){
$prime_investigation_risk_status_id = $rlst->prime_investigation_risk_status_id;
$investigation_risk_status = $rlst->investigation_risk_status;
$investigation_status[$prime_investigation_risk_status_id] = $investigation_risk_status;
}
$data['investigation_status'] = $investigation_status;
// ACTION STATUS
$this->db->select('prime_action_training_status_id,action_training_status');
$this->db->from('cw_action_training_status');
$this->db->where('cw_action_training_status.trans_status', 1);
$action_status_rslt = $this->db->get()->result();
foreach($action_status_rslt as $rlst){
$prime_action_training_status_id = $rlst->prime_action_training_status_id;
$action_training_status = $rlst->action_training_status;
$action_status[$prime_action_training_status_id] = $action_training_status;
}
$data['action_status'] = $action_status;
$this->load->view("$this->control_name/manage",$data);
}
//LOAD TABEL WITH FILTERS
public function search(){
$draw = $this->input->post('draw');
$start = $this->input->post('start');
$per_page = $this->input->post('length');
$order = $this->input->post('order');
$order_col = $this->input->post('columns');
$search = $this->input->post('search');
$column = $order[0]['column'];
$order_sor = $order[0]['dir'];
$order_col = $order_col[$column]['data'];
$start_date = str_replace("/","-",$this->input->post('start_date'));
$end_date = str_replace("/","-",$this->input->post('end_date'));
$from_date = date('Y-m-d',strtotime($start_date));
$to_date = date('Y-m-d',strtotime($end_date));
$incident_status = $this->input->post('incident_status');
$investigation_status = $this->input->post('investigation_status');
$incident_category_filter = $this->input->post('incident_category_filter');
$action_status = $this->input->post('action_status');
$search_query = 'select prime_incident_management_id,incident_category,incident_car_no,incident_description,incident_date,cw_investigation_risk_status.investigation_risk_status as investigation_status,cw_incident_status.incident_status as incident_status,cw_incident_management.action_status from cw_incident_management left join cw_investigation_risk_status on cw_investigation_risk_status.prime_investigation_risk_status_id = cw_incident_management.investigation_status join cw_incident_status on cw_incident_status.prime_incident_status_id = cw_incident_management.incident_status where cw_incident_management.trans_status = 1';
$search = trim($search['value']);
if($search){
if(strtotime($search)){
$search_val = date('Y-m-d',strtotime($search));
$common_search .= ' or incident_date like "'.$search_val.'%"';
}else{
$common_search .= ' or incident_car_no like "'.$search_val.'%" or incident_description like "'.$search_val.'%"';
}
}
if($common_search){
$common_search = ltrim($common_search,' or ');
$common_search = " and ($common_search)";
}
if($from_date != '1970-01-01' && $to_date != '1970-01-01'){
$common_search .= ' and DATE_FORMAT(incident_date, "%Y-%m-%d") BETWEEN \''.$from_date.'\' AND \''.$to_date.'\'';
}
if($incident_status[0] > 0){
$incident_status = '"'.implode('","',$incident_status).'"';
$common_search .= " and cw_incident_management.incident_status IN ($incident_status)";
}
if($investigation_status[0] > 0){
$investigation_status = '"'.implode('","',$investigation_status).'"';
$common_search .= " and cw_incident_management.investigation_status IN ($investigation_status)";
}
if($incident_category_filter[0] > 0){
$incident_category_filter = '"'.implode('","',$incident_category_filter).'"';
$common_search .= " and cw_incident_management.incident_category IN ($incident_category_filter)";
}
if($action_status[0] > 0){
$action_status = '"'.implode('","',$action_status).'"';
$common_search .= " and cw_incident_management.action_status IN ($action_status)";
}
$count_all_query = str_replace("@SELECT@","count(*) as allcount",$this->base_query);
$search_total = $this->db->query($count_all_query);
$search_total_info = $search_total->result();
$total_count = $search_total_info[0]->allcount;
$count_query = str_replace("@SELECT@","count(*) as allcount",$this->base_query);
$count_query .= " where $this->prime_table.trans_status = 1 $common_search";
$search_count = $this->db->query($count_query);
$search_info = $search_count->result();
$filtered_count = $search_info[0]->allcount;
if((int)$per_page !== -1){
$search_query .= " $common_search LIMIT $start,$per_page";
}
$search_data = $this->db->query($search_query);
$search_result = $search_data->result();
echo json_encode(array("draw" => intval($draw),"recordsTotal" => $total_count,"recordsFiltered" => $filtered_count,"data" => $search_result));
}
//LOAD MODEL PAGE VIEW WITH DATA
public function view($form_view_id = -1){
$select_query = "select cw_incident_management.incident_car_no,investigation_status,incident_category,incident_description,incident_date,emp6.employee_name as employee_name,cw_incident_management.employee_name as employee_id,employee_dept,phone_number,cw_incident_management.email_id,address,cw_incident_management.incident_status,prime_incident_management_id,incident_car_type,audit_number,specification,q_m_s_element,details_of_problem,problem_identified_date,problem_identified_by as problem_identified_by_id,emp1.employee_name as problem_identified_by,problem_knowledged_by as problem_knowledged_by_id,emp2.employee_name as problem_knowledged_by,problem_agreed_date_of_response,problem_date_followed_up,problem_extension_requested_by as problem_extension_requested_by_id,emp3.employee_name as problem_extension_requested_by,correction_taken_to_resolve_problem,correction_taken_by as correction_taken_by_id,emp4.employee_name as correction_taken_by,correction_taken_date,root_cause_of_problem,root_cause_completed_by as root_cause_completed_by_id,emp5.employee_name as root_cause_completed_by,root_cause_completed_date,verification_date,verification,action_prevent,prevent_action_taken_date,prevent_action_taken_by as prevent_action_taken_by_id,emp7.employee_name as prevent_action_taken_by,verification_done_by as verification_done_by_id,emp8.employee_name as verification_done_by
from cw_incident_management join cw_employees emp6 on emp6.prime_employees_id = cw_incident_management.employee_name left join cw_employees emp7 on emp7.prime_employees_id = cw_incident_management.prevent_action_taken_by left join cw_employees emp8 on emp8.prime_employees_id = cw_incident_management.verification_done_by left join cw_employees emp1 on emp1.prime_employees_id = cw_incident_management.problem_identified_by left join cw_employees emp2 on emp2.prime_employees_id = cw_incident_management.problem_knowledged_by left join cw_employees emp3 on emp3.prime_employees_id = cw_incident_management.problem_extension_requested_by left join cw_employees emp4 on emp4.prime_employees_id = cw_incident_management.correction_taken_by left join cw_employees emp5 on emp5.prime_employees_id = cw_incident_management.root_cause_completed_by where cw_incident_management.trans_status = 1 and cw_incident_management.prime_incident_management_id = $form_view_id";
$select_data = $this->db->query("CALL sp_a_run ('SELECT','$select_query')");
$select_result = $select_data->result();
$select_data->next_result();
$data['incident_management'] = $select_result;
// INCIDENT STATUS
$this->db->select('prime_incident_status_id,incident_status');
$this->db->from('cw_incident_status');
$this->db->where('cw_incident_status.trans_status', 1);
$incident_status_rslt = $this->db->get()->result();
$incident_status[""] = "--- Select ---";
foreach($incident_status_rslt as $incident_rslt){
$prime_incident_status_id = $incident_rslt->prime_incident_status_id;
$incident_name = $incident_rslt->incident_status;
$incident_status[$prime_incident_status_id] = $incident_name;
}
$data['incident_status'] = $incident_status;
// EMPLOYEE DEPARTMENT
$this->db->select('prime_employee_department_id,employee_department_name');
$this->db->from('cw_employee_department');
$this->db->where('cw_employee_department.trans_status', 1);
$employee_dept_rslt = $this->db->get()->result();
$employee_dept[""] = "--- Select ---";
foreach($employee_dept_rslt as $dept_rslt){
$prime_employee_department_id = $dept_rslt->prime_employee_department_id;
$employee_department_name = $dept_rslt->employee_department_name;
$employee_dept[$prime_employee_department_id] = $employee_department_name;
}
$data['employee_dept'] = $employee_dept;
// INVESTIGATION STATUS
$this->db->select('prime_investigation_risk_status_id,investigation_risk_status');
$this->db->from('cw_investigation_risk_status');
$this->db->where('cw_investigation_risk_status.trans_status', 1);
$investigation_status_rslt = $this->db->get()->result();
$investigation_status[""] = "--- Select ---";
foreach($investigation_status_rslt as $investigation_rslt){
$prime_investigation_risk_status_id = $investigation_rslt->prime_investigation_risk_status_id;
$investigation_risk_status = $investigation_rslt->investigation_risk_status;
$investigation_status[$prime_investigation_risk_status_id] = $investigation_risk_status;
}
$data['investigation_status'] = $investigation_status;
// INCIDENT TYPE
$this->db->select('prime_incident_type_id,incident_type as incident_name');
$this->db->from('cw_incident_type');
$this->db->where('cw_incident_type.trans_status', 1);
$incident_type_rslt = $this->db->get()->result();
$incident_type[""] = "--- Select ---";
foreach($incident_type_rslt as $incident_rslt){
$prime_incident_type_id = $incident_rslt->prime_incident_type_id;
$incident_name = $incident_rslt->incident_name;
$incident_type[$prime_incident_type_id] = $incident_name;
}
$data['incident_type'] = $incident_type;
// CORRECTIVE TYPE
$this->db->select('prime_corrective_type_id,corrective_type as corrective_name');
$this->db->from('cw_corrective_type');
$this->db->where('cw_corrective_type.trans_status', 1);
$corrective_type_rslt = $this->db->get()->result();
$corrective_type[""] = "--- Select ---";
foreach($corrective_type_rslt as $corrective_rslt){
$prime_corrective_type_id = $corrective_rslt->prime_corrective_type_id;
$corrective_name = $corrective_rslt->corrective_name;
$corrective_type[$prime_corrective_type_id] = $corrective_name;
}
$data['corrective_type'] = $corrective_type;
// ALL EMPLOYEE
$this->db->select('prime_employees_id,employee_name');
$this->db->from('cw_employees');
$this->db->where('cw_employees.trans_status', 1);
$this->db->where('cw_employees.role !=', 1);
$employee_dept_rslt = $this->db->get()->result();
$enquired_by[""] = "--- Select ---";
foreach($employee_dept_rslt as $dept_rslt){
$prime_employees_id = $dept_rslt->prime_employees_id;
$employee_name = $dept_rslt->employee_name;
$enquired_by[$prime_employees_id] = $employee_name;
}
$data['enquired_by'] = $enquired_by;
$data['investigation_info_table'] = $this->get_incident_investigation_info_table($form_view_id);
$data['enquiry_info_table'] = $this->get_incident_enquiry_info_table($form_view_id);
$this->load->view("$this->control_name/form",$data);
}
//SAVE INCIDENT REQUEST
public function save(){
$prime_incident_management_id = (int)$this->input->post('prime_incident_management_id');
$investigation_status = (int)$this->input->post('investigation_status');
$incident_category = (int)$this->input->post('incident_category');
$incident_manage_data = array(
'incident_category' => $incident_category,
'incident_description' => $this->input->post('incident_description'),
'incident_date' => date("Y-m-d",strtotime($this->input->post('incident_date'))),
'employee_name' => $this->input->post('employee_name_id'),
'employee_dept' => $this->input->post('employee_dept'),
'phone_number' => $this->input->post('phone_number'),
'email_id' => $this->input->post('email_id'),
'address' => $this->input->post('address'),
'incident_status' => $this->input->post('incident_status'),
'investigation_status' => $investigation_status,
'incident_car_type' => $this->input->post('incident_car_type'),
'audit_number' => $this->input->post('audit_number'),
'specification' => $this->input->post('specification'),
'q_m_s_element' => $this->input->post('q_m_s_element'),
'details_of_problem' => $this->input->post('details_of_problem'),
'problem_identified_date' => date("Y-m-d",strtotime($this->input->post('problem_identified_date'))),
'problem_identified_by' => $this->input->post('problem_identified_by_id'),
'problem_knowledged_by' => $this->input->post('problem_knowledged_by_id'),
'problem_agreed_date_of_response' => date("Y-m-d",strtotime($this->input->post('problem_agreed_date_of_response'))),
'problem_date_followed_up' => date("Y-m-d",strtotime($this->input->post('problem_date_followed_up'))),
'problem_extension_requested_by' => $this->input->post('problem_extension_requested_by_id'),
'correction_taken_to_resolve_problem' => $this->input->post('correction_taken_to_resolve_problem'),
'correction_taken_by' => $this->input->post('correction_taken_by_id'),
'correction_taken_date' => date("Y-m-d",strtotime($this->input->post('correction_taken_date'))),
'root_cause_of_problem' => $this->input->post('root_cause_of_problem'),
'root_cause_completed_by' => $this->input->post('root_cause_completed_by_id'),
'root_cause_completed_date' => date("Y-m-d",strtotime($this->input->post('root_cause_completed_date'))),
'action_prevent' => $this->input->post('action_prevent'),
'prevent_action_taken_by' => $this->input->post('prevent_action_taken_by_id'),
'prevent_action_taken_date' => date("Y-m-d",strtotime($this->input->post('prevent_action_taken_date'))),
'verification' => $this->input->post('verification'),
'verification_done_by' => $this->input->post('verification_done_by_id'),
'verification_date' => date("Y-m-d",strtotime($this->input->post('verification_date'))),
);
if((int)$prime_incident_management_id > 0){
if($this->check_action_status($prime_incident_management_id)){
$incident_manage_data['trans_updated_by'] = $this->session->userdata('logged_id');
$incident_manage_data['trans_updated_date'] = date("Y-m-d H:i:s");
if((int)$investigation_status === 2){
$incident_manage_data['action_status'] = 1;
}
$this->db->where('prime_incident_management_id',$prime_incident_management_id);
if($this->db->update('cw_incident_management',$incident_manage_data)){
$this->update_investigation_status($incident_category,$investigation_status,$prime_incident_management_id);
$this->update_incident_status($prime_incident_management_id);
echo json_encode(array('success' => TRUE, 'message' => "Successfully Updated",'insert_id' => $prime_incident_management_id));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Update failed, Please Try Again",'insert_id' => $prime_incident_management_id));
}
}else{
echo json_encode(array('success' => FALSE, 'message' => "Action Management On Process",'insert_id' => $prime_incident_management_id));
}
}else{
echo json_encode(array('success' => False, 'message' => "Please contact Admin.!"));
}
}
//UPDATE STATUS TO DELETE IN MODULE PRIMARY TABLE
public function delete(){
$delete_ids = implode(",",$this->input->post('delete_ids'));
$can_process = TRUE;
$delete_status = FALSE;
if($this->check_delete_status()){
$delete_status = TRUE;
$check_table_query = 'SELECT GROUP_CONCAT(prime_module_id) as prime_module_id,GROUP_CONCAT(label_name) as label_name from cw_form_setting WHERE pick_table = "'. $this->prime_table .'" and trans_status = 1 ';
$check_table_info = $this->db->query("CALL sp_a_run ('SELECT','$check_table_query')");
$check_table_rlst = $check_table_info->row();
$check_table_info->next_result();
if($check_table_rlst->prime_module_id){
$prime_module_id = explode(",",$check_table_rlst->prime_module_id);
$label_name = explode(",",$check_table_rlst->label_name);
$i = 0;
$select_table = '';
$select_label = '';
$select_trans_status = '';
$select_where = '';
foreach($prime_module_id as $check_modules){
$table_name = "cw_".$check_modules;
$table_rename = $table_name."_$i";
$select_table .= "$table_rename.$label_name[$i],";
$select_label .= " $table_name $table_rename,";
if((int)$i === 0){
$select_trans_status .= "( $table_rename.trans_status = 1";
$select_where .= " and ($table_rename.$label_name[$i] in ($delete_ids)";
}else{
$select_trans_status .= " and $table_rename.trans_status = 1";
$select_where .= " or $table_rename.$label_name[$i] in ($delete_ids)";
}
$i++;
}
$select_trans_status .= ")";
$select_where .= ")";
$select_table = rtrim($select_table,',');
$select_label = rtrim($select_label,',');
$check_module_query .= 'SELECT '.$select_table.' from '.$select_label.' WHERE '.$select_trans_status.' '.$select_where.' LIMIT 0,1';
$check_module_info = $this->db->query("CALL sp_a_run ('SELECT','$check_module_query')");
$values_count = $check_module_info->num_rows();
$check_module_info->next_result();
if((int)$values_count > 0){
$can_process = False;
$delete_status = False;
}
}
if($delete_status){
$delete_query = 'DELETE FROM '. $this->prime_table .' WHERE '. $this->prime_id .' in ('. $delete_ids .')';
if($this->db->query("CALL sp_a_run ('RUN','$delete_query')")){
$prime_table_query = 'SELECT * from '. $this->prime_table .'';
$prime_table_info = $this->db->query("CALL sp_a_run ('SELECT','$prime_table_query')");
$row_count = $prime_table_info->num_rows();
$prime_table_info->next_result();
$alter_query = 'ALTER TABLE '. $this->prime_table .' AUTO_INCREMENT = '. $row_count .'';
$this->db->query("CALL sp_a_run ('RUN','$alter_query')");
$can_process = False;
}
}
}
if($can_process){
$created_on = date("Y-m-d h:i:s");
$prime_upd_query .= 'trans_deleted_by = "'. $this->logged_id .'",trans_deleted_date = "'.$created_on.'"';
$prime_update_query = 'UPDATE '. $this->prime_table .' SET trans_status = 0,'. $prime_upd_query .' WHERE '. $this->prime_id .' in ('. $delete_ids .')';
if($this->db->query("CALL sp_a_run ('UPDATE','$prime_update_query')")){
echo json_encode(array('success' => TRUE, 'message' => "Successfully Deleted"));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Unable to delete"));
}
}else
if($delete_status){
echo json_encode(array('success' => TRUE, 'message' => "Successfully Deleted"));
}else{
$modules = ucwords($check_table_rlst->prime_module_id);
echo json_encode(array('success' => FALSE, 'message' => "Unable to delete, This value is already used in $modules modules"));
}
}
//CHECK UNIQUE FIELD STATUS
public function check_delete_status(){
$check_delete_query = 'SELECT GROUP_CONCAT(unique_field) as unique_field from cw_form_setting WHERE prime_module_id = "'. $this->control_name .'" and trans_status = 1 ';
$check_delete_info = $this->db->query("CALL sp_a_run ('SELECT','$check_delete_query')");
$check_delete_rlst = $check_delete_info->row();
$check_delete_info->next_result();
$unique_info = explode(",",$check_delete_rlst->unique_field);
if(in_array('1', $unique_info)){
return TRUE;
}else{
return FALSE;
}
}
//UPDATE STATUS TO DELETE FOR UPLOAD FILES or DOCUMENTS
public function remove_file(){
$prime_id_val = $this->input->post('prime_id_val');
$is_defult = (int)$this->input->post('is_defult');
$input_name = $this->input->post('input_name');
$table_name = '';
if($is_defult === 1){
$table_name = $this->prime_table;
}else
if($is_defult === 2){
$table_name = $this->cf_table;
}
if($table_name){
$created_on = date("Y-m-d h:i:s");
$set_query = $input_name .' = "" ,trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'.$created_on.'"';
$update_query = 'UPDATE '.$table_name .' SET '. $set_query .' WHERE '. $this->prime_id .' = "'. $prime_id_val .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
echo json_encode(array('success' => TRUE, 'message' => "Successfully updated"));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Unable to process your request"));
}
}
//TABLE HEAD
public function get_table_head(){
$table_head = array(array(
'label_name' => 'incident_date',
'view_name' => 'Incident Date',
'field_type' => 4
),array(
'label_name' => 'incident_car_no',
'view_name' => 'Incident/CAR No',
'field_type' => 1
),array(
'label_name' => 'incident_category',
'view_name' => 'Incident Category',
'field_type' => 1
),array(
'label_name' => 'incident_description',
'view_name' => 'Incident Description',
'field_type' => 1
),array(
'label_name' => 'incident_status',
'view_name' => 'Incident Status',
'field_type' => 1
),array(
'label_name' => 'investigation_status',
'view_name' => 'Investigation Status',
'field_type' => 1
),array(
'label_name' => 'action_status',
'view_name' => 'Action Status',
'field_type' => 1
)
);
return $table_head;
}
//SAVE INVESTIGATION TEAM
public function save_investigation_team(){
$prime_incident_management_id = (int)$this->input->post('prime_management_id');
$prime_incident_investigation_line_id = $this->input->post('prime_incident_investigation_line_id');
$investigator_team_data = array(
'prime_incident_management_id' =>$prime_incident_management_id,
'investigator_name' =>$this->input->post('investigator_name'),
'investigator_dept' =>$this->input->post('investigator_dept'),
'investigator_phone_number' =>$this->input->post('investigator_phone_number'),
'investigator_email' =>$this->input->post('investigator_email'),
'investigator_address' =>$this->input->post('investigator_address')
);
if((int)$prime_incident_investigation_line_id === 0){
$investigator_team_data['trans_created_by'] = $this->session->userdata('logged_id');
$investigator_team_data['trans_created_date'] = date("Y-m-d H:i:s");
if($this->db->insert('cw_incident_investigation_line',$investigator_team_data)){
$insert_id = $this->db->insert_id();
$table_info = $this->get_incident_investigation_info_table($prime_incident_management_id);
echo json_encode(array('success' => TRUE, 'message' => "Investigator Added successfully",
'insert_id' => $insert_id,'table_info' =>$table_info));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Insertion failed, Please Try Again"));
}
}else
if((int)$prime_incident_investigation_line_id > 0){
$investigator_team_data['trans_updated_by'] = $this->session->userdata('logged_id');
$investigator_team_data['trans_updated_date'] = date("Y-m-d H:i:s");
$this->db->where('prime_incident_investigation_line_id',$prime_incident_investigation_line_id);
if($this->db->update('cw_incident_investigation_line',$investigator_team_data)){
$table_info = $this->get_incident_investigation_info_table($prime_incident_management_id);
echo json_encode(array('success' => TRUE, 'message' => "Investigator Updated successfully",'insert_id' => $prime_incident_investigation_line_id,'table_info' =>$table_info));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Update failed, Please Try Again",'insert_id' => $prime_incident_investigation_line_id));
}
}else{
echo json_encode(array('success' => False, 'message' => "Please contact Admin.!"));
}
}
//SAVE ENQUIRE TEAM
public function save_enquiry_team(){
$prime_incident_management_id = (int)$this->input->post('prime_incident_id');
$prime_incident_enquiry_line_id = $this->input->post('prime_incident_enquiry_line_id');
$enquiry_team_data = array(
'prime_incident_management_id' =>$prime_incident_management_id,
'date_of_enquiry' =>date('Y-m-d',strtotime($this->input->post('date_of_enquiry'))),
'venue_of_enquiry' =>$this->input->post('venue_of_enquiry'),
'witness_name' =>$this->input->post('witness_name'),
'witness_department' =>$this->input->post('witness_department'),
'witness_email_id' =>$this->input->post('witness_email_id'),
'witness_phone_number' =>$this->input->post('witness_phone_number'),
'witness_address' =>$this->input->post('witness_address'),
'doc_record_title' =>$this->input->post('doc_record_title'),
'doc_record_number' =>$this->input->post('doc_record_number'),
'doc_record_version_no' =>$this->input->post('doc_record_version_no'),
'enquired_by' =>$this->input->post('enquired_by')
);
if((int)$prime_incident_enquiry_line_id === 0){
$enquiry_team_data['trans_created_by'] = $this->session->userdata('logged_id');
$enquiry_team_data['trans_created_date'] = date("Y-m-d H:i:s");
if($this->db->insert('cw_incident_enquiry_line',$enquiry_team_data)){
$insert_id = $this->db->insert_id();
$table_info = $this->get_incident_enquiry_info_table($prime_incident_management_id);
echo json_encode(array('success' => TRUE, 'message' => "Enquiry Inserted Successfully",
'insert_id' => $insert_id,'table_info' =>$table_info));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Insertion failed, Please Try Again"));
}
}else
if((int)$prime_incident_enquiry_line_id > 0){
$enquiry_team_data['trans_updated_by'] = $this->session->userdata('logged_id');
$enquiry_team_data['trans_updated_date'] = date("Y-m-d H:i:s");
$this->db->where('prime_incident_enquiry_line_id',$prime_incident_enquiry_line_id);
if($this->db->update('cw_incident_enquiry_line',$enquiry_team_data)){
$table_info = $this->get_incident_enquiry_info_table($prime_incident_management_id);
echo json_encode(array('success' => TRUE, 'message' => "Enquiry Updated Successfully",'insert_id' => $prime_incident_enquiry_line_id,'table_info' =>$table_info));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Update failed, Please Try Again",'insert_id' => $prime_incident_enquiry_line_id));
}
}else{
echo json_encode(array('success' => False, 'message' => "Please contact Admin.!"));
}
}
//SEARCH EMPLOYEE LIST
public function search_employee(){
$search_employee_name = $this->input->post_get('search_employee_name');
$suggestions = array();
if($search_employee_name){
$this->db->select('prime_employees_id,employee_name,employee_code,department,mobile_number,email_id,residential_address');
$this->db->from('cw_employees');
$this->db->where('cw_employees.trans_status', 1);
$this->db->where('cw_employees.role !=', 1);
$this->db->group_start();
$this->db->like('cw_employees.employee_name',$search_fault_name);
$this->db->or_like('cw_employees.user_name',$search_fault_name);
$this->db->group_end();
$search_fault_info = $this->db->get()->result();
foreach($search_fault_info as $search_rlst){
$prime_employees_id = $search_rlst->prime_employees_id;
$employee_code = $search_rlst->employee_code;
$employee_name = $search_rlst->employee_name;
$department = $search_rlst->department;
$mobile_number = $search_rlst->mobile_number;
$address = $search_rlst->residential_address;
$email_id = $search_rlst->email_id;
$label = "$employee_code - $employee_name";
$suggestions[] = array('value'=>$employee_name,'label'=>$label,'prime_employees_id'=>$prime_employees_id,'department'=>$department,'mobile_number'=>$mobile_number,'email_id'=>$email_id,'address'=>$address);
}
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search");
}
echo json_encode($suggestions);
}
//LIST INVESTIGATION TEAM INFOMATION
public function get_incident_investigation_info_table($prime_incident_management_id){
$this->db->select('cw_employees.employee_name as investigator_name,cw_employee_department.employee_department_name as investigator_dept,prime_incident_investigation_line_id,prime_incident_management_id,investigator_phone_number,investigator_email,investigator_address');
$this->db->from('cw_incident_investigation_line');
$this->db->join('cw_employees','cw_employees.prime_employees_id = cw_incident_investigation_line.investigator_name');
$this->db->join('cw_employee_department','cw_employee_department.prime_employee_department_id = cw_incident_investigation_line.investigator_dept');
$this->db->where('cw_incident_investigation_line.prime_incident_management_id', (int)$prime_incident_management_id);
$this->db->where('cw_incident_investigation_line.trans_status', 1);
$send_request_info = $this->db->get()->result();
$tble_line = '';
foreach($send_request_info as $send_rlst){
$investigator_name = $send_rlst->investigator_name;
$investigator_dept = $send_rlst->investigator_dept;
$prime_incident_investigation_line_id = $send_rlst->prime_incident_investigation_line_id;
$investigator_phone_number = $send_rlst->investigator_phone_number;
$investigator_email = $send_rlst->investigator_email;
if(empty($investigator_email)){
$investigator_email = '-';
}
$investigator_address = $send_rlst->investigator_address;
if(empty($investigator_address)){
$investigator_address = '-';
}
$tble_line .= " <tr>
<td>$investigator_name</td>
<td>$investigator_dept</td>
<td>$investigator_phone_number</td>
<td>$investigator_email</td>
<td>$investigator_address</td>
<td>
<a class='btn btn-warning btn-sm' onclick='edit_investigator_info($prime_incident_investigation_line_id)'>Edit</a>
<a class='btn btn-danger btn-sm' onclick='delete_investigator_info($prime_incident_investigation_line_id,$prime_incident_management_id)'>Delete</a>
</td>
</tr>";
}
if($tble_line === ''){
$tble_line .= " <tr><td colspan='6' style='text-align:center;'>No data available in table</td></tr>";
}
$table_data = " <h4 class='m-t-0 header-title'><b>Investigation Information List</b></h4>
<table class='table table-striped table-bordered' id='investigation_info_table'>
<thead>
<tr>
<th>Investigator Name</th>
<th>Investigator Department</th>
<th>Investigator phone Number</th>
<th>Investigator Email</th>
<th>Investigator Address</th>
<th>Action</th>
</tr>
</thead>
<tbody>
$tble_line
</tbody>
</table>";
return $table_data;
}
//GET INVESTIGATION INFORMATION
public function edit_investigator_info(){
$prime_incident_investigation_line_id = (int)$this->input->post('prime_incident_investigation_line_id');
$this->db->select('investigator_name as investigator_name_id,cw_employees.employee_name as investigator_name,investigator_dept,prime_incident_investigation_line_id,prime_incident_management_id,investigator_phone_number,investigator_email,investigator_address');
$this->db->from('cw_incident_investigation_line');
$this->db->join('cw_employees','cw_employees.prime_employees_id = cw_incident_investigation_line.investigator_name');
$this->db->where('cw_incident_investigation_line.prime_incident_investigation_line_id',$prime_incident_investigation_line_id);
$this->db->where('cw_incident_investigation_line.trans_status',1);
$return_data = $this->db->get()->result();
if($return_data){
echo json_encode(array('success'=>true,'message'=>'Edit Info','update_data'=>$return_data));
}else{
echo json_encode(array('success'=>false,'message'=>'Please Contact Admin..!'));
}
}
//DELETE INVESTIGATOR INFORMATION
public function delete_investigator_info(){
$prime_incident_investigation_line_id = $this->input->post('prime_incident_investigation_line_id');
$prime_incident_management_id = $this->input->post('prime_incident_management_id');
$update_data['trans_deleted_by'] = $this->session->userdata('logged_id');
$update_data['trans_deleted_date'] = date("Y-m-d H:i:s");
$update_data['trans_status'] = 0;
$this->db->where('prime_incident_investigation_line_id ',$prime_incident_investigation_line_id);
if($this->db->update('cw_incident_investigation_line',$update_data)){
$table_info = $this->get_incident_investigation_info_table($prime_incident_management_id);
echo json_encode(array("success" => TRUE,'message' => 'Deleted successfully','table_info'=>$table_info));
}else{
echo json_encode(array("success" => False,'message' => "Please Try Again"));
}
}
//LIST ENQUIRY TEAM INFOMATION
public function get_incident_enquiry_info_table($prime_incident_management_id){
$this->db->select('emp2.employee_name as enquired_by,emp1.employee_name as witness_name,cw_employee_department.employee_department_name as witness_department,witness_phone_number,witness_email_id,witness_address,date_of_enquiry,venue_of_enquiry,prime_incident_enquiry_line_id');
$this->db->from('cw_incident_enquiry_line');
$this->db->join('cw_employees emp1','emp1.prime_employees_id = cw_incident_enquiry_line.witness_name');
$this->db->join('cw_employees emp2','emp2.prime_employees_id = cw_incident_enquiry_line.enquired_by');
$this->db->join('cw_employee_department','cw_employee_department.prime_employee_department_id = cw_incident_enquiry_line.witness_department');
$this->db->where('cw_incident_enquiry_line.prime_incident_management_id', (int)$prime_incident_management_id);
$this->db->where('cw_incident_enquiry_line.trans_status', 1);
$send_request_info = $this->db->get()->result();
$tble_line = '';
foreach($send_request_info as $send_rlst){
$prime_incident_enquiry_line_id = $send_rlst->prime_incident_enquiry_line_id;
$enquired_by = $send_rlst->enquired_by;
$witness_name = $send_rlst->witness_name;
$witness_department = $send_rlst->witness_department;
$witness_phone_number = $send_rlst->witness_phone_number;
$witness_email = $send_rlst->witness_email_id;
$date_of_enquiry = date('d-m-Y',strtotime($send_rlst->date_of_enquiry));
if(empty($witness_email)){
$witness_email = '-';
}
$witness_address = $send_rlst->witness_address;
if(empty($witness_address)){
$witness_address = '-';
}
$tble_line .= " <tr>
<td>$witness_name</td>
<td>$witness_department</td>
<td>$witness_phone_number</td>
<td>$witness_email</td>
<td>$enquired_by</td>
<td>
<a class='btn btn-warning btn-sm' onclick='edit_enquiry_info($prime_incident_enquiry_line_id)'>Edit</a>
<a class='btn btn-danger btn-sm' onclick='delete_enquiry_info($prime_incident_enquiry_line_id,$prime_incident_management_id)'>Delete</a>
</td>
</tr>";
}
if($tble_line === ''){
$tble_line .= "<tr><td colspan='6' style='text-align:center;'>No data available in table</td></tr>";
}
$table_data = " <h4 class='m-t-0 header-title'><b>Enquiry Team Information List</b></h4>
<table class='table table-striped table-bordered' id='enquiry_info_table'>
<thead>
<tr>
<th>Witness Name</th>
<th>Witness Department</th>
<th>Witness phone Number</th>
<th>Witness Email</th>
<th>Enquiry By</th>
<th>Action</th>
</tr>
</thead>
<tbody>
$tble_line
</tbody>
</table>";
return $table_data;
}
//GET ENQUIRY TEAM INFORMATION
public function edit_enquiry_info(){
$prime_incident_enquiry_line_id = (int)$this->input->post('prime_incident_enquiry_line_id');
$this->db->select('enquired_by,witness_name as witness_name_id,cw_employees.employee_name as witness_name,witness_department,witness_phone_number,witness_email_id,witness_address,date_of_enquiry,venue_of_enquiry,prime_incident_enquiry_line_id,doc_record_title,doc_record_number,doc_record_version_no');
$this->db->from('cw_incident_enquiry_line');
$this->db->join('cw_employees','cw_employees.prime_employees_id = cw_incident_enquiry_line.witness_name');
$this->db->where('cw_incident_enquiry_line.prime_incident_enquiry_line_id',$prime_incident_enquiry_line_id);
$this->db->where('cw_incident_enquiry_line.trans_status',1);
$return_data = $this->db->get()->result();
if($return_data){
echo json_encode(array('success'=>true,'message'=>'Edit Info','update_data'=>$return_data));
}else{
echo json_encode(array('success'=>false,'message'=>'Please Contact Admin..!'));
}
}
//DELETE INVESTIGATOR INFORMATION
public function delete_enquiry_info(){
$prime_incident_enquiry_line_id = $this->input->post('prime_incident_enquiry_line_id');
$prime_incident_management_id = $this->input->post('prime_incident_management_id');
$update_data['trans_deleted_by'] = $this->session->userdata('logged_id');
$update_data['trans_deleted_date'] = date("Y-m-d H:i:s");
$update_data['trans_status'] = 0;
$this->db->where('prime_incident_enquiry_line_id ',$prime_incident_enquiry_line_id);
if($this->db->update('cw_incident_enquiry_line',$update_data)){
$table_info = $this->get_incident_enquiry_info_table($prime_incident_management_id);
echo json_encode(array("success" => TRUE,'message' => 'Deleted successfully','table_info'=>$table_info));
}else{
echo json_encode(array("success" => False,'message' => "Please Try Again"));
}
}
//UPDATE INVESTIGATION IN ACTION MANAGEMENT
public function update_investigation_status($incident_category,$investigation_status,$prime_id){
$created_on = date("Y-m-d h:i:s");
$logged_id = $this->logged_id;
if((int)$investigation_status === 2){
$exist_qry = 'SELECT cw_incident_management.prime_incident_management_id,cw_incident_management.incident_car_no,cw_incident_management.employee_name,cw_incident_management.phone_number,cw_incident_management.email_id,cw_incident_management.address,incident_description,incident_date from cw_incident_management join cw_action_management on cw_action_management.risk_incident_car_no = cw_incident_management.incident_car_no where prime_incident_management_id = "'.$prime_id.'" and cw_incident_management.trans_status = 1';
$exist_data = $this->db->query("CALL sp_a_run ('SELECT','$exist_qry')");
$exist_count = $exist_data->num_rows();
$exist_data->next_result();
if((int)$incident_category === 1){
$action_category = 2;
}else{
$action_category = 3;
}
if((int)$exist_count === 0){
$insert_query = "INSERT INTO cw_action_management(action_category,risk_incident_car_no,employee_name,phone_number,email,address,trans_created_by,trans_created_date,action_creation_date,action_status,description,risk_incident_car_date) SELECT \"$action_category\",incident_car_no,employee_name,phone_number,email_id,address,\"$logged_id\",\"$created_on\",\"$created_on\",1,incident_description,incident_date FROM cw_incident_management WHERE cw_incident_management.prime_incident_management_id = '\"$prime_id\"'";
$insert_info = $this->db->query("CALL sp_a_run ('RUN','$insert_query')");
$insert_info->next_result();
}else{
$exist_result = $exist_data->row();
$incident_car_no = $exist_result->incident_car_no;
$incident_description = $exist_result->incident_description;
$incident_date = date('Y-m-d',strtotime($exist_result->incident_date));
$employee_name = $exist_result->employee_name;
$phone_number = $exist_result->phone_number;
$email_id = $exist_result->email_id;
$address = $exist_result->address;
$update_query = "UPDATE cw_action_management SET cw_action_management.employee_name = \"$employee_name\",cw_action_management.phone_number = \"$phone_number\",cw_action_management.email = \"$email_id\",cw_action_management.address = \"$address\",cw_action_management.description = \"$incident_description\",cw_action_management.risk_incident_car_date = \"$incident_date\",cw_action_management.action_status = 1,trans_updated_by = \"$logged_id\",trans_updated_date = \"$created_on\",cw_action_management.action_category = \"$action_category\" WHERE cw_action_management.risk_incident_car_no = \"$incident_car_no\"";
$update_info = $this->db->query("CALL sp_a_run ('RUN','$update_query')");
$update_info->next_result();
}
}
}
//UPDATE INCIDENT STATUS
public function update_incident_status($prime_id){
$created_on = date("Y-m-d h:i:s");
$logged_id = $this->logged_id;
$this->db->select('incident_car_no,incident_status,investigation_status');
$this->db->from('cw_incident_management');
$this->db->where('cw_incident_management.prime_incident_management_id',$prime_id);
$incident_management = $this->db->get()->row();
$incident_car_no = $incident_management->incident_car_no;
$incident_status = $incident_management->incident_status;
$investigation_status = $incident_management->investigation_status;
$update_incident_request['trans_updated_by'] = $logged_id;
$update_incident_request['trans_updated_date'] = $created_on;
$update_incident_request['investigation_status'] = $investigation_status;
$update_incident_request['incident_status'] = $incident_status;
$this->db->where('incident_no',$incident_car_no);
$this->db->update('cw_incident_request',$update_incident_request);
}
//CHECK ACTION Status
public function check_action_status($prime_incident_management_id){
$exist_qry = 'SELECT count(*) as result_count from cw_incident_management join cw_action_management on cw_action_management.risk_incident_car_no = cw_incident_management.incident_car_no where prime_incident_management_id = "'.$prime_incident_management_id.'" and cw_incident_management.trans_status = 1';
$exist_data = $this->db->query("CALL sp_a_run ('SELECT','$exist_qry')");
$exist_result = $exist_data->row();
$exist_data->next_result();
$result_count = (int)$exist_result->result_count;
if($result_count === 0){
return true;
}else{
$exist_qry = 'SELECT count(*) as result_count from cw_incident_management join cw_action_management on cw_action_management.risk_incident_car_no = cw_incident_management.incident_car_no where prime_incident_management_id = "'.$prime_incident_management_id.'" and cw_action_management.action_status = 1 and cw_incident_management.trans_status = 1';
$exist_data = $this->db->query("CALL sp_a_run ('SELECT','$exist_qry')");
$exist_result = $exist_data->row();
$exist_data->next_result();
$result_count = (int)$exist_result->result_count;
if($result_count === 1){
return true;
}else{
return false;
}
}
}
}
?>