File: /home/cafsindia/rms.cafsjobs.com/application/controllers/Candidate_tracker.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Candidate_tracker extends Action_controller{
public function __construct(){
parent::__construct('candidate_tracker');
$this->collect_base_info();
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$data['quick_link'] = $this->quick_link;
$data['table_head'] = $this->table_head;
$data['master_pick'] = $this->master_pick;
$data['fliter_list'] = $this->fliter_list;
$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'];
$search = trim($search['value']);
$search_query = str_replace("@SELECT@",$this->select_query,$this->base_query);
//ADDED BASIC,FILTER,COMMON QUERY HERE
$role_condition = "";
if($this->role_condition){
$role_condition = $this->role_condition;
}
$fliter_query = "";
foreach($this->fliter_list as $fliter){
$label_id = $fliter['label_id'];
$label_name = $fliter['label_name'];
$field_isdefault = (int)$fliter['field_isdefault'];
$array_list = $fliter['array_list'];
$field_type = (int)$fliter['field_type'];
if($field_isdefault === 1){
$column_name = $this->prime_table .".$label_id";
$search_val = $this->input->post("$label_id");
if($search_val){
if($field_type === 4){
$search_val = date('Y-m-d',strtotime($search_val));
$fliter_query .= " and $column_name = '$search_val'";
}else
if(($field_type === 5) || ($field_type === 7)){
$search_val = trim(implode('","',$search_val));
$fliter_query .= ' and '.$column_name.' in ("'.$search_val.'")';
}else
if($field_type === 13){
$search_val = date('Y-m-d H:i:s',strtotime($search_val));
$fliter_query .= " and $column_name = '$search_val'";
}else{
$fliter_query .= " and $column_name LIKE '%$search_val%'";
}
}
}
}
$common_search = "";
if($search){
foreach($this->form_info as $setting){
$prime_form_id = $setting->prime_form_id;
$field_type = (int)$setting->field_type;
$pick_list = $setting->pick_list;
$pick_table = $setting->pick_table;
$pick_list_type = $setting->pick_list_type;
$input_view_type = (int)$setting->input_view_type;
$auto_prime_id = $setting->auto_prime_id;
$auto_dispaly_value = $setting->auto_dispaly_value;
$label_id = strtolower(str_replace(" ","_",$setting->label_name));
$field_isdefault = (int)$setting->field_isdefault;
if($field_isdefault === 1){
if(($input_view_type === 1) || ($input_view_type === 2)){
$search_label = "$this->prime_table.$label_id";
$search_val = "";
if($field_type === 4){ // having issues in date search
if(strtotime($search)){
$search_val = date('Y-m-d',strtotime($search));
$common_search .= ' or '. $search_label .' like "%'.$search_val.'%"';
}
}else
if(($field_type === 5) || ($field_type === 7) || ($field_type === 9)){
$result = array_filter($this->master_pick[$label_id], function ($item) use ($search) {
if (stripos($item, $search) !== false) {
return true;
}
return false;
});
if($result){
$pick_key = implode('", "', array_keys($result));
$common_search .= ' or '. $search_label .' in("'.$pick_key.'")';
}
}else{
$common_search .= ' or '. $search_label .' like "'.$search.'%"';
}
}
}
}
if($common_search){
$common_search = ltrim($common_search,' or ');
$common_search = " and ($common_search)";
$common_search = str_replace("(,","(",$common_search);
$common_search = str_replace("()","(0)",$common_search);
}
}
$count_all_query = str_replace("@SELECT@","count(*) as allcount",$this->base_query);
$count_all_query .= " where $this->prime_table.trans_status = 1 $role_condition $fliter_query $common_search";
$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 $role_condition $fliter_query $common_search";
$search_count = $this->db->query($count_query);
$search_info = $search_count->result();
$filtered_count = $search_info[0]->allcount;
$search_query .= " where $this->prime_table.trans_status = 1 $role_condition $fliter_query $common_search";
$search_query .= " ORDER BY $order_col $order_sor";
//$search_query .= " ORDER BY STR_TO_DATE(interview_time , '%l:%i %p') DESC";
if((int)$per_page !== -1){
$search_query .= " LIMIT $start,$per_page";
}
$search_data = $this->db->query($search_query);
$search_result = $search_data->result();
//echo "search_query :: \n$search_query\n";
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){
//VIEW, FORM INPUT
$data['view_info'] = $this->view_info;
$data['form_info'] = $this->form_info;
//VIEW DATA
$base_query = str_replace("@SELECT@",$this->view_select,$this->base_query);
$view_query = $base_query ." where $this->prime_table.$this->prime_id = $form_view_id and $this->prime_table.trans_status = 1";
$view_data = $this->db->query("CALL sp_a_run ('SELECT','$view_query')");
$view_result = $view_data->result();
$view_data->next_result();
$data['form_view'] = $view_result[0];
//AUTO COMPLTE,PICK LIST AND CONDITION
foreach($this->form_info as $from){
$prime_form_id = (int)$from->prime_form_id;
$field_type = (int)$from->field_type;
$pick_table = $from->pick_table;
$auto_prime_id = $from->auto_prime_id;
$auto_dispaly_value = $from->auto_dispaly_value;
$label_id = $from->label_name;
if($field_type === 9){
if($view_result[0]){
$get_value = $view_result[0]->$label_id;
if($get_value){
$pick_query = 'select '.$auto_dispaly_value.' from '.$pick_table.' where '.$auto_prime_id.' = "'.$get_value.'" and trans_status = 1';
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$this->all_pick[$prime_form_id] = $pick_result[0]->$auto_dispaly_value;
}
}
}
}
$data['all_pick'] = $this->all_pick;
$data['condition_list'] = $this->condition_list;
$view_qry = 'select * from cw_form_view_setting where prime_view_module_id = "'.$this->control_name.'" and form_view_type = "3" and trans_status = 1';
$view_data = $this->db->query("CALL sp_a_run ('SELECT','$view_qry')");
$view_result = $view_data->result();
$view_data->next_result();
$row_view_list = array();
foreach($view_result as $view){
$prime_form_view_id = $view->prime_form_view_id;
$row_set_data = $this->get_row_set_data($prime_form_view_id,$form_view_id);
$row_view_list[$prime_form_view_id] = $row_set_data;
}
$data['row_view_list'] = $row_view_list;
//Role Based Condition
$role_based_query = 'SELECT * FROM cw_role_base_condition WHERE role_module_id = "'.$this->control_name.'" and FIND_IN_SET("'.$this->logged_role.'",role_condition_for) and trans_status = 1';
$role_based_info = $this->db->query("CALL sp_a_run ('SELECT','$role_based_query')");
$role_based_result = $role_based_info->result();
$role_based_info->next_result();
$role_based_condition = array();
foreach ($role_based_result as $key => $condition) {
$role_based_condition[$condition->user_condition_type] = $condition->input_columns;
}
$data['role_based_condition'] = $role_based_condition;
/* Custom Code START */
$department = $data['form_view']->department;
$emp_query = 'SELECT * FROM cw_employees WHERE role in("3","6") and department in("'.$department.'","6") and trans_status =1';
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_query')");
$emp_result = $emp_info->result();
$emp_info->next_result();
$emp_list = "";
foreach($emp_result as $result){
$id = $result->prime_employees_id;
$name = ucwords($result->emp_name);
$emp_code = $result->employee_code;
$emp_list .= "<option value='$id'>$emp_code - $name</option>";
}
$data['emp_list'] = $emp_list;
/* Custom Code END */
$assign_data = $this->get_assign_data($form_view_id);
$data['assign_list'] = $assign_data['tbl_data'];
$data['assign_count'] = $assign_data['tbl_count'];
$position = $data['form_view']->post_applied_for;
$candidate_status = $data['form_view']->candidate_status;
$data['question_list'] = $this->get_question_list($position,$candidate_status,$department);
$this->load->view("$this->control_name/form",$data);
}
//SAVE MODEL DATA TO DATA BASE
public function save(){
$unq_chk = array();
$prime_qry_key = "";
$prime_qry_value = "";
$prime_upd_query = "";
$cf_qry_key = "";
$cf_qry_value = "";
$cf_upd_query = "";
$cf_has = false;
$form_id = (int)$this->input->post($this->prime_id);
$employee_type = (int)$this->input->post('employee_type');
$selected_status = (int)$this->input->post('selected_status');
$post_data = array();
$prefix_code = date("y").date("m").date("d");
if($form_id === 0){
$last_count_qry = 'select max(candidate_code) as candidate_code from '.$this->prime_table.' where candidate_code like "'.$prefix_code.'%"';
$code_info = $this->db->query("CALL sp_a_run ('SELECT','$last_count_qry')");
$code_result = $code_info->result();
$code_info->next_result();
$candidate_code = $code_result[0]->candidate_code;
if($candidate_code){
$candidate_code = $candidate_code + 1;
}else{
$candidate_code = $prefix_code."0001";
}
}else{
$candidate_code = $this->input->post('candidate_code');
}
//Check Education count
$rowset_edu_count_qry = 'select count(*) as count from cw_candidate_tracker_educational_qualification where prime_candidate_tracker_id ='.$form_id;
$rowset_edu_info = $this->db->query("CALL sp_a_run ('SELECT','$rowset_edu_count_qry')");
$rowset_edu_result = $rowset_edu_info->result();
$rowset_edu_info->next_result();
$education_count = $rowset_edu_result[0]->count;
if((int)$education_count < 1){
echo json_encode(array('success' => false, 'message' => "Education Tab Should Be Filed",'from'=>"education"));
exit(0);
}
//Check Experience count
if((int)$employee_type === 2){
$rowset_count_qry = 'select count(*) as count from cw_candidate_tracker_working_experience where prime_candidate_tracker_id ='.$form_id;
$rowset_info = $this->db->query("CALL sp_a_run ('SELECT','$rowset_count_qry')");
$rowset_result = $rowset_info->result();
$rowset_info->next_result();
$work_experience_count = $rowset_result[0]->count;
if((int)$work_experience_count === 0){
echo json_encode(array('success' => false, 'message' => "Work Experience Tab Should Be Filed if you are Experienced",'from'=>"work_experience"));
exit(0);
}
}
foreach($this->form_info as $setting){
$field_type = $setting->field_type;
$input_view_type = (int)$setting->input_view_type;
$label_id = strtolower(str_replace(" ","_",$setting->label_name));
$field_isdefault = $setting->field_isdefault;
$unique_field = (int)$setting->unique_field;
$view_name = $setting->view_name;
if((int)$field_type === 7){
$multi_name = $label_id."[]";
$value = trim(implode(",",$this->input->post($multi_name)));
}else{
$value = trim($this->input->post($label_id));
}
if((int)$field_type === 4){
$value = date('Y-m-d',strtotime($value));
}else
if((int)$field_type === 13){
$value = date('Y-m-d H:i:s',strtotime($value));
}else
if((int)$field_type === 8){//textbox only
$value = str_replace('"',"xdbquot",$value);
$value = str_replace("'","xquot",$value);
$value = str_replace("&","xxamp",$value);
}
if($label_id === "candidate_code"){
$value = $candidate_code;
}
if(($input_view_type === 1) || ($input_view_type === 2)){
if((int)$field_isdefault === 1){
$prime_qry_key .= $label_id.",";
$prime_qry_value .= '"'.$value.'",';
$prime_upd_query .= $label_id.' = "'.$value.'",';
if($unique_field === 1){
$prime_unq_chk = $label_id.'= "'.$value.'"';
$query = "select count(*) as rslt_count from $this->prime_table where $this->prime_id != $form_id and $prime_unq_chk";
$unq_chk[] = array('label_id'=>$label_id,'view_name'=>$view_name,'query'=>$query,);
}
}
$post_data[$label_id] = $value;
}
}
$rslt_count = 0;
$can_process = array();
foreach($unq_chk as $unq_rslt){
$query = $unq_rslt['query'];
$label_id = $unq_rslt['label_id'];
$view_name = $unq_rslt['view_name'];
$unq_info = $this->db->query("CALL sp_a_run ('RUN','$query')");
$unq_result = $unq_info->result();
$unq_info->next_result();
if($unq_result){
$rslt_count = (int)$unq_result[0]->rslt_count;
if($rslt_count !== 0){
$can_process[] = $view_name." already exist";
}
}
}
if(count($can_process) > 0){
$can_process = array_values($can_process);
$can_process = implode(",<br/>", $can_process);
echo json_encode(array('success' => false, 'message' => $can_process,));
}else{
$created_on = date("Y-m-d h:i:s");
if((int)$form_id === 0){
$prime_qry_key .= "trans_created_by,trans_created_date";
$prime_qry_value .= '"'.$this->logged_id.'",'.'"'.$created_on.'"';
$prime_insert_query = "insert into $this->prime_table ($prime_qry_key) values ($prime_qry_value)";
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$prime_insert_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
$insert_id = $insert_result[0]->ins_id;
echo json_encode(array('success' => TRUE, 'message' => "Successfully added", 'insert_id' => $insert_id));
}else{
if($selected_status === 5){ // Insert Into HRMS Offer Through API
$this->hrms_save($post_data);
}
$prime_upd_query .= 'trans_updated_by = "'. $this->logged_id .'",trans_updated_date = "'.$created_on.'"';
$prime_update_query = 'UPDATE '. $this->prime_table .' SET '. $prime_upd_query .' WHERE '. $this->prime_id .' = "'. $form_id .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$prime_update_query')");
echo json_encode(array('success' => TRUE, 'message' => "Successfully updated",'insert_id' => $form_id));
}
}
}
//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')")){
$row_set_query = 'SELECT form_view_label_name from cw_form_view_setting where form_view_type = "3" and prime_view_module_id = "'. $this->control_name .'" and trans_status = 1';
$row_set_info = $this->db->query("CALL sp_a_run ('SELECT','$row_set_query')");
$row_count = (int)$row_set_info->num_rows();
$row_set_info->next_result();
if($row_count !== 0){
$row_set_result = $row_set_info->result();
$delete_table_name = '';
$delete_table_condition = '';
foreach($row_set_result as $row_set){
$row_set_table_name = "cw_".$this->control_name."_".$row_set->form_view_label_name;
$delete_table_name .= "$row_set_table_name,";
$delete_table_condition .= " $row_set_table_name.$this->prime_id in ('$delete_ids') and";
}
$delete_table_name = rtrim($delete_table_name,',');
$delete_table_condition = rtrim($delete_table_condition,'and');
$delete_row_set_query = 'DELETE FROM '. $delete_table_name .' WHERE '. $delete_table_condition.'';
$this->db->query("CALL sp_a_run ('RUN','$delete_row_set_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"));
}
}
public function save_assign_log(){
$prime_id = $this->input->post('prime_id');
$assign_id = $this->input->post('assign_id');
$assign_to = $this->input->post('assign_to');
if((int)$assign_id === 0){
$prime_insert_query = 'insert into cw_assign_log (prime_candidate_tracker_id,interviewer,interview_status,trans_created_by,trans_created_date) values ("'.$prime_id.'",'.'"'.$assign_to.'",'.'"1","'.$this->logged_id.'",'.'"'.$created_on.'")';
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$prime_insert_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
}else{
$assign_update_query = 'UPDATE cw_assign_log SET interviewer = "'.$assign_to.'" WHERE prime_assign_log_id = "'. $assign_id .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$assign_update_query')");
}
$update_query = 'UPDATE cw_candidate_tracker SET candidate_status = "1",interviewer = "'.$assign_to.'" WHERE prime_candidate_tracker_id = "'. $prime_id .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
$assign_data = $this->get_assign_data($prime_id);
$assign_data = $assign_data['tbl_data'];
echo json_encode(array('success' => TRUE, 'message' => "Successfully updated",'tbl_data'=>$assign_data));
}
public function get_assign_data($prime_id){
$view_qry = 'select *,cw_assign_log.interviewer as interviewer_id from cw_assign_log inner join cw_candidate_status on cw_candidate_status.candidate_status_id = cw_assign_log.interview_status inner join cw_employees on cw_employees.prime_employees_id = cw_assign_log.interviewer where prime_candidate_tracker_id = "'.$prime_id.'" and cw_assign_log.trans_status = 1 order by prime_assign_log_id ASC';
$view_data = $this->db->query("CALL sp_a_run ('SELECT','$view_qry')");
$view_result = $view_data->result();
$view_data->next_result();
$tot_rows = $view_data->num_rows();
$tr_line = "";
$edit_btn = "";
$i = 0;
foreach ($view_result as $key => $value) {
$i++;
$assign_log_id = $value->prime_assign_log_id;
$interviewer = $value->emp_name;
$interviewer_id = $value->interviewer_id;
$candidate_status = $value->candidate_status;
$interview_status = $value->interview_status;
$remarks = $value->remarks;
$overall_rating = $value->overall_rating;
if((int)$i === (int)$tot_rows){
if((int)$interview_status === 1){
$edit_btn = "<a class='btn btn-edit btn-xs row_btn' onclick = edit_assign_log('$assign_log_id','$interviewer_id');>Edit</a>";
}
}
$tr_line .= "<tr><td>$i</td><td>$interviewer</td><td>$candidate_status</td><td>$remarks</td><td>$overall_rating</td><td>$edit_btn</td></tr>";
}
$tbl_data = "<table class='table table-bordered' style='background-color: #FFFFFF; box-shadow: none;'>
<thead>
<tr>
<th>SNo</th>
<th>Interviewer</th>
<th>Candidate Status</th>
<th>Remarks</th>
<th>Overall rating</th>
<th>Option</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
return array('tbl_data' => $tbl_data,'tbl_count' => $i);
}
public function get_question_list($position,$candidate_status,$department){
/*$position = $this->input->post('position');
$candidate_status = $this->input->post('candidate_status');
$department = $this->input->post('department');*/
$view_qry = 'select * from cw_position_questions where prime_position_id = "'.$position.'" and trans_status = 1';
$view_data = $this->db->query("CALL sp_a_run ('SELECT','$view_qry')");
$view_result = $view_data->result();
$view_data->next_result();
$emp_query = 'SELECT * FROM cw_employees WHERE role in("3","6") and department in("'.$department.'","6") and trans_status =1';
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_query')");
$emp_result = $emp_info->result();
$emp_info->next_result();
$emp_list = "";
foreach($emp_result as $result){
$id = $result->prime_employees_id;
$name = ucwords($result->emp_name);
$emp_code = $result->employee_code;
if($id !== $this->logged_id){
$emp_list .= "<option value='$id'>$emp_code - $name</option>";
}
}
//Get Candidate Status
$sts_query = 'SELECT * FROM cw_candidate_status WHERE candidate_status_id not in (1,6,10) and trans_status =1';
$sts_info = $this->db->query("CALL sp_a_run ('SELECT','$sts_query')");
$sts_result = $sts_info->result();
$sts_info->next_result();
$sts_list = "";
foreach($sts_result as $result){
$id = $result->candidate_status_id;
$status = $result->candidate_status;
if((int)$this->logged_dept === 6){
$sts_list .= "<option value='$id'>$status</option>";
}else{
if((int)$id !== 3){
$sts_list .= "<option value='$id'>$status</option>";
}
}
}
$tr_line = "";
$i = 1;
foreach ($view_result as $key => $value) {
$question = $value->question;
$tr_line .= "<tr><td style='width:10%'>$i</td><td>$question<input type='hidden' class='form-control' id = 'question' name = 'question[]' value='$question'></td><td style='width:10%'><select type='text' class='form-control' id = 'rating' name = 'rating[]'><option value=''>--- Select Rating ---</option><option value='1'>1</option><option value='2'>2</option><option value='3'>3</option><option value='4'>4</option><option value='5'>5</option></select></td><td style='width:30%'><textarea id='qu_remarks' name='qu_remarks[]' class='form-control'></textarea></td></tr>";
$i++;
}
$tbl = "";
if((int)$candidate_status !== 2){
$tbl = "<table class='table table-bordered' id='ques_tbl' style='background-color: #FFFFFF; box-shadow: none;'>
<thead>
<tr>
<th>SNo</th>
<th>Question</th>
<th>Rating</th>
<th>Remarks</th>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
}
$tbl_data = "$tbl<div class='row'>
<div class='form-group'>
<label for='interview_status' class='control-label required' aria-required='true'>Interview Status</label>
<select name = 'interview_status' id = 'interview_status' class='form-control input-sm select2'>
<option value=''>--- Select Status ---</option>
$sts_list
</select>
</div>
<div class='form-group'>
<label for='interview_status' class='control-label required' aria-required='true'>Next Interview Date</label>
<input name = 'interview_date' id = 'interview_date' class='form-control datepicker'>
</div>
<div class='form-group' style='display:none;'>
<label for='int_ncd' class='control-label required' aria-required='true'>NCD</label>
<input name = 'int_ncd' id = 'int_ncd' class='form-control datepicker'>
</div>
<div class='form-group' style='display:none;'>
<label for='next_interviewer' class='control-label required' aria-required='true'>Next Interviewer</label>
<select name = 'next_interviewer' id = 'next_interviewer' class='form-control input-sm select2'>
<option value=''>--- Select Employee ---</option>
$emp_list
</select>
</div>
<div class='form-group' style='display:none;'>
<label for='int_joining_date' class='control-label required' aria-required='true'>Joining Date</label>
<input name = 'int_joining_date' id = 'int_joining_date' class='form-control datepicker'>
</div>
<div class='form-group' style='display:none;'>
<label for='salary_agreed' class='control-label required' aria-required='true'>Salary Agreed</label>
<input name = 'salary_agreed' id = 'salary_agreed' class='form-control number'>
</div>
<div class='form-group'>
<label for='int_remarks' class='control-label required' aria-required='true'>Remarks</label>
<textarea id='int_remarks' class='form-control'></textarea>
</div>
<div class='form-group'>
<button type='submit' class='btn btn-primary btn-sm' id='interviewer_submit' onclick=save_interviewer_log(event)>Submit</button>
</div>
</div>
</div>";
return $tbl_data;
}
public function save_interviewer_log(){
$prime_id = $this->input->post('prime_id');
$interview_date = $this->input->post('interview_date');
// echo $interview_date;exit;
$interview_status = $this->input->post('interview_status');
// echo $interview_status;exit;
$candidate_status = $this->input->post('candidate_status');
$remarks = $this->input->post('remarks');
$question = $this->input->post('question');
$rating = $this->input->post('rating');
$next_interviewer = $this->input->post('next_interviewer');
$qu_remarks_arr = $this->input->post('qu_remarks');
$total_rating = array_sum($rating);
$overall_rating = $total_rating/count(array_filter($rating));
$logged_id = $this->logged_id;
$created_on = date("Y-m-d h:i:s");
$final_array = array_combine($question, $rating);
$remarks = str_replace('"',"xdbquot",$remarks);
$remarks = str_replace("'","xquot",$remarks);
$remarks = str_replace("&","xxamp",$remarks);
$insert_array = array();
$j = 0;
foreach ($final_array as $key => $value) {
if((int)$value > 0){
$qu_remarks = $qu_remarks_arr[$j];
$qu_remarks = str_replace('"',"xdbquot",$qu_remarks);
$qu_remarks = str_replace("'","xquot",$qu_remarks);
$qu_remarks = str_replace("&","xxamp",$qu_remarks);
$insert_array[] = '("'.$prime_id.'","'.$logged_id.'","'.$key.'","'.$value.'","'.$qu_remarks.'","'.$logged_id.'","'.$created_on.'")';
}
$j++;
}
if(!empty($insert_array)){
$prime_insert_query = 'insert into cw_interviewer_log (prime_candidate_tracker_id,interviewer,question,rating,remarks,trans_created_by,trans_created_date) values '.implode(",",$insert_array);
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$prime_insert_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
}
$next_int = '';
if(((int)$interview_status === 4) || ((int)$interview_status === 7) || ((int)$interview_status === 9)){
$prime_insert_query = 'insert into cw_assign_log (prime_candidate_tracker_id,interviewer,interview_status,trans_created_by,trans_created_date) values ("'.$prime_id.'",'.'"'.$next_interviewer.'",'.'"1","'.$this->logged_id.'",'.'"'.$created_on.'")';
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$prime_insert_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
$next_int = 'interviewer = "'.$next_interviewer.'",';
}else
if((int)$interview_status === 2){
$int_ncd = date('Y-m-d',strtotime($this->input->post('int_ncd')));
$update_query = 'UPDATE cw_candidate_tracker SET ncd = "'.$int_ncd.'",candidate_status = "'.$interview_status.'",interviewer_remarks = "'.$remarks.'" WHERE prime_candidate_tracker_id = "'. $prime_id .'"';
// echo $update_query; die;
$this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
$next_int = 'ncd = "'.$int_ncd.'",';
}else
if((int)$interview_status === 3){
$joining_date = date("Y-m-d",strtotime($this->input->post('joining_date')));
$salary_commited = $this->input->post('salary_commited');
$next_int = 'date_of_joining = "'.$joining_date.'",salary_commited = "'.$salary_commited.'",';
//$this->send_email($interview_status,$remarks,$prime_id);
}
/*else
if((int)$interview_status === 3){
$this->send_email($interview_status,$remarks,$prime_id);
}*/
$update_query = 'UPDATE cw_candidate_tracker SET '.$next_int.' candidate_status = "'.$interview_status.'",interviewer_remarks = "'.$remarks.'" WHERE prime_candidate_tracker_id = "'. $prime_id .'"';
//echo $update_query; die;
$this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
if((int)$candidate_status === 2){
$prime_insert_query = 'insert into cw_assign_log (prime_candidate_tracker_id,interviewer,interview_status,remarks,trans_created_by,trans_created_date) values ("'.$prime_id.'",'.'"'.$this->logged_id.'",'.'"'.$interview_status.'","'.$remarks.'","'.$this->logged_id.'",'.'"'.$created_on.'")';
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$prime_insert_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
}else{
// $this->send_email($candidate_status,$remarks,$prime_id);
$update_query = 'UPDATE cw_assign_log SET overall_rating = "'.$overall_rating.'",interview_status = "'.$interview_status.'",remarks = "'.$remarks.'" WHERE prime_candidate_tracker_id = "'. $prime_id .'" and interviewer = "'. $logged_id .'"';
$this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
}
$assign_data = $this->get_assign_data($prime_id);
$assign_data = $assign_data['tbl_data'];
echo json_encode(array('success' => TRUE, 'message' => "Successfully updated",'tbl_data'=>$assign_data));
}
public function suggest_area(){
$term = $this->input->get('term');
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, 'https://places.cit.api.here.com/places/v1/autosuggest?at=13.0801721,80.2838331&q='.$term.'&app_id=aNznL8t8pNdayQeDFril&app_code=ld75RJzjmYnduhwjqFC7Bw');
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$suggestions = curl_exec($ch);
$suggestionsArray = json_decode($suggestions, true);
$count = count($suggestionsArray["results"]);
$data = array();
for($i=0; $i<=5;$i++){
$title = $suggestionsArray["results"][$i]['title'];
$data[] = array('label' => "$title", 'title' => $title);
}
echo json_encode($data);
}
//IMPORT FILE VIEW INFORMATION
public function import(){
$data['module_id'] = $this->control_name;
$excel_format_qry = 'select prime_excel_format_id,excel_name from cw_util_excel_format where excel_module_id = "'.$this->control_name.'" and trans_status = 1';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_result = $excel_format->result();
$excel_format->next_result();
$excel_format_drop[""] = "---- Excel Format ----";
foreach($excel_result as $excel){
$prime_excel_format_id = $excel->prime_excel_format_id;
$excel_name = $excel->excel_name;
$excel_format_drop[$prime_excel_format_id] = $excel_name;
}
$data['excel_format_drop'] = $excel_format_drop;
$this->load->view("$this->control_name/import",$data);
}
//Print candidate details MRJ Start 30aug2020
public function candidate_print($print_id=-1){
//candidate info
$candidate_info_qry = 'select cw_candidate_tracker.candidate_name, cw_position_873.position_name as post_applied_for , cw_candidate_status.candidate_status as candidate_status , cw_candidate_tracker.mobile_number , cw_candidate_tracker.alternate_number , cw_candidate_tracker.email_id , DATE_FORMAT(cw_candidate_tracker.date_of_birth, "%d-%m-%Y") as date_of_birth , cw_candidate_tracker.age , cw_zct_5_879.cw_zct_5_value as applied_by , cw_zct_6_880.cw_zct_6_value as marital_status , cw_candidate_tracker.father_or_husband_name , cw_candidate_tracker.occupation , cw_candidate_tracker.income , cw_candidate_tracker.no_of_siblings , cw_candidate_tracker.current_salary , cw_candidate_tracker.expected_salary , cw_candidate_tracker.permanent_location , cw_candidate_tracker.current_location , cw_consultancy_904.consultancy_name as consultancy,date_of_joining,date_of_available,interview_time,cw_zct_8_value as employee_type,salary_commited from cw_candidate_tracker left join cw_position as cw_position_873 on cw_position_873.prime_position_id = cw_candidate_tracker.post_applied_for left join cw_candidate_status as cw_candidate_status on cw_candidate_status.candidate_status_id = cw_candidate_tracker.candidate_status left join cw_zct_5 as cw_zct_5_879 on cw_zct_5_879.cw_zct_5_id = cw_candidate_tracker.applied_by left join cw_zct_6 as cw_zct_6_880 on cw_zct_6_880.cw_zct_6_id = cw_candidate_tracker.marital_status left join cw_consultancy as cw_consultancy_904 on cw_consultancy_904.prime_consultancy_id = cw_candidate_tracker.consultancy inner join cw_zct_8 on cw_zct_8.cw_zct_8_id = cw_candidate_tracker.employee_type where cw_candidate_tracker.trans_status = 1 and prime_candidate_tracker_id ='.$print_id;
$candidate_info_data = $this->db->query("CALL sp_a_run ('SELECT','$candidate_info_qry')");
$candidate_info_result = $candidate_info_data->result();
$candidate_info_data->next_result();
$data['candidate_info'] = $candidate_info_result[0];
//education info
$education_info_qry = 'select cw_candidate_tracker_educational_qualification.prime_candidate_tracker_educational_qualification_id,cw_candidate_tracker_educational_qualification.prime_candidate_tracker_id,cw_zct_7_889.cw_zct_7_value as standard , cw_candidate_tracker_educational_qualification.board_university , cw_candidate_tracker_educational_qualification.degree , cw_candidate_tracker_educational_qualification.year_of_passing, cw_candidate_tracker_educational_qualification.percentage from cw_candidate_tracker_educational_qualification left join cw_zct_7 as cw_zct_7_889 on cw_zct_7_889.cw_zct_7_id = cw_candidate_tracker_educational_qualification.standard where cw_candidate_tracker_educational_qualification.prime_candidate_tracker_id = '.$print_id.' and cw_candidate_tracker_educational_qualification.trans_status = "1" order by abs(cw_candidate_tracker_educational_qualification.prime_candidate_tracker_educational_qualification_id) desc';
$education_info_data = $this->db->query("CALL sp_a_run ('SELECT','$education_info_qry')");
$education_info_result = $education_info_data->result();
$education_info_data->next_result();
$data['education_info'] = $education_info_result;
//work info
$work_info_qry = 'select cw_candidate_tracker_working_experience.prime_candidate_tracker_working_experience_id,cw_candidate_tracker_working_experience.prime_candidate_tracker_id,cw_candidate_tracker_working_experience.organization_name , cw_candidate_tracker_working_experience.work_location , cw_candidate_tracker_working_experience.current_designation , DATE_FORMAT(cw_candidate_tracker_working_experience.joined_date, "%d-%m-%Y") as joined_date , cw_candidate_tracker_working_experience.salary_start , cw_zct_9_910.cw_zct_9_value as resigned , DATE_FORMAT(cw_candidate_tracker_working_experience.relived_date, "%d-%m-%Y") as relived_date , cw_candidate_tracker_working_experience.salary_left,reasone_for_relieving from cw_candidate_tracker_working_experience left join cw_zct_9 as cw_zct_9_910 on cw_zct_9_910.cw_zct_9_id = cw_candidate_tracker_working_experience.resigned where cw_candidate_tracker_working_experience.prime_candidate_tracker_id = '.$print_id.' and cw_candidate_tracker_working_experience.trans_status = "1" order by cw_candidate_tracker_working_experience.joined_date desc';
$work_info_data = $this->db->query("CALL sp_a_run ('SELECT','$work_info_qry')");
$work_info_result = $work_info_data->result();
$work_info_data->next_result();
$data['work_info'] = $work_info_result;
//interview status info
$interview_status_qry = 'select * from cw_assign_log inner join cw_candidate_status on cw_candidate_status.candidate_status_id = cw_assign_log.interview_status inner join cw_employees on cw_employees.prime_employees_id = cw_assign_log.interviewer where prime_candidate_tracker_id = "'.$print_id.'" and cw_assign_log.trans_status = 1';
$interview_status_data = $this->db->query("CALL sp_a_run ('SELECT','$interview_status_qry')");
$interview_status_result = $interview_status_data->result();
$interview_status_data->next_result();
$data['interview_status'] = $interview_status_result;
//question info
$question_status_qry = 'select question,rating,remarks,cw_employees.emp_name from cw_interviewer_log inner join cw_employees on cw_employees.prime_employees_id = cw_interviewer_log.interviewer where cw_interviewer_log.trans_status = 1 and prime_candidate_tracker_id = "'.$print_id.'" order by cw_interviewer_log.interviewer, prime_interviewer_log_id';
$question_status_data = $this->db->query("CALL sp_a_run ('SELECT','$question_status_qry')");
$question_status_result = $question_status_data->result();
$question_status_data->next_result();
$data['question_status'] = $question_status_result;
$this->load->view("$this->control_name/candidate_print",$data);
}
//Print candidate details MRJ end 30aug2020
public function get_position(){
$department = $this->input->post('department');
$position_qry = 'select prime_position_id,position_name from cw_position where department ="'.$department.'" and trans_status = 1';
$position_info = $this->db->query("CALL sp_a_run ('SELECT','$position_qry')");
$position_result = $position_info->result();
$position_info->next_result();
$position_list = "<option value=''>--- Select Post Applied For ---</option>";
foreach($position_result as $result){
$id = $result->prime_position_id;
$position = $result->position_name;
$position_list .= "<option value='$id'> $position </option>";
}
echo $position_list;
}
//USED TO SEARCH
public function hrms_save($post_data){
$post_url = "http://hrms.allyindian.com/app/api_controller.php";
$post_data['frm'] = "offer_save";
$response = $this->curl($post_url,$post_data);
return json_encode($response);
//echo $response;
}
public function send_email($interview_status,$remarks,$prime_id){
$query = "select * from cw_candidate_tracker inner join cw_candidate_status on cw_candidate_status.candidate_status_id=cw_candidate_tracker.candidate_status inner join cw_position on cw_position.prime_position_id=cw_candidate_tracker.post_applied_for where prime_candidate_tracker_id=$prime_id";
$candidate_info = $this->db->query("CALL sp_a_run ('SELECT','$query')");
$candidate_result = $candidate_info->result();
$candidate_info->next_result();
$querys = "select * from cw_candidate_status where candidate_status_id=$interview_status";
$candidate_infos = $this->db->query("CALL sp_a_run ('SELECT','$querys')");
$candidate_results = $candidate_infos->result();
$candidate_infos->next_result();
$candidate_status = $candidate_results[0]->candidate_status;
$candidate_email = $candidate_result[0]->email_id;
$role_title = $candidate_result[0]->position_name;
$bodys = "<p> Thank you very much for devoting your time and effort in the interview for the ".$role_title." position at CAFS.</p>
<p> We truly enjoyed meeting you, which gives us brief details on your skills and knowledge, and hope you too enjoyed the same. Unfortunately, we decided to proceed with another candidate selected for the same position.</p>
<p> We will keep your resume in our talent database, and we would post you the job openings in the future that fit your profile better, we will make sure to get in touch with you.</p>";
require('./phpmailer/class.phpmailer.php');
//Get Email Configuration data
$config_query = 'SELECT smtp_server,sender_name,bcc,port_no,sender_email,mail_username,mail_password FROM cw_mail_configurations WHERE prime_mail_configurations_id =1 and trans_status = 1 and mail_status = 1';
// print_r($config_query);exit;
$config_info = $this->db->query("CALL sp_a_run ('SELECT','$config_query')");
$config_result = $config_info->result();
$config_info->next_result();
$smtp_server = $config_result[0]->smtp_server;
$sender_name = $config_result[0]->sender_name;
$bcc = explode(",",$config_result[0]->bcc);
$port_no = $config_result[0]->port_no;
$sender_email = $config_result[0]->sender_email;
$username = $config_result[0]->mail_username;
$mail_password = $config_result[0]->mail_password;
$to_email = $candidate_email; //Test Email
$bcc_mail = "";
$mail = new PHPMailer();
try{
//$mail->SMTPDebug = 3;
$mail->IsSMTP();
$mail->Host = $smtp_server; // Your SMTP PArameter
$mail->Port = $port_no; // Your Outgoing Port
$mail->SMTPAuth = true; // This Must Be True
$mail->Username = $username; // Your Email Address
$mail->Password = $mail_password; // Your Password
$mail->SMTPSecure = 'tls'; // Check Your Server's Connections for TLS or SSL
$mail->From = $sender_email;
$mail->FromName = $sender_name;
if($to_email){
$mail->AddAddress($to_email);
}
$mail->IsHTML(true);
$mail->Subject = "Interview Feedback";
$mail->Body = $bodys;
$mail= $mail->Send();
if($mail){
$msg = "Mail Sent Successfully";
}
}catch(phpmailerException $e){
$msg = "Mail Not Sent";
}catch(Exception $e){
$msg = "Mail Not Sent";
}
}
}
?>