File: /home/cafsindia/cpaqua.cafsinfotech.in/dump/application/controllers/Unlogged_users.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Unlogged_users extends Action_controller{
public function __construct(){
parent::__construct('unlogged_users');
$this->collect_base_info();
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$from_query = 'select * from cw_form_setting where prime_module_id in("employees","time_sheet") and field_show = "1" and label_name in("team","employee_code","emp_name","entry_date") and field_type !=1 ORDER BY input_for,field_sort asc';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$from_query')");
$form_result = $form_data->result();
$form_data->next_result();
$table_query = 'select label_name,view_name,field_type from cw_form_setting where prime_module_id in("employees","time_sheet") and field_show = "1" and prime_form_id in(3,1029,1059,1060) ORDER BY input_for,field_sort asc';
$table_info = $this->db->query("CALL sp_a_run ('SELECT','$table_query')");
$result = $table_info->result();
$table_info->next_result();
$data['table_headers'] = $result;
$fliter_list = $this->get_filter_data($form_result);
$data['fliter_list'] = $fliter_list;
$data['encKey'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
public function get_filter_data($form_result){
$filter = array();
foreach($form_result as $setting){
$prime_form_id = (int)$setting->prime_form_id;
$prime_module_id = $setting->prime_module_id;
$input_view_type = (int)$setting->input_view_type;
$input_for = (int)$setting->input_for;
$field_type = (int)$setting->field_type;
$label_id = $setting->label_name;
$label_name = ucwords($setting->view_name);
$field_length = $setting->field_length;
$field_decimals = $setting->field_decimals;
$pick_list_type = (int)$setting->pick_list_type;
$pick_list = $setting->pick_list;
$pick_table = $setting->pick_table;
$auto_prime_id = $setting->auto_prime_id;
$auto_dispaly_value = $setting->auto_dispaly_value;
$field_isdefault = (int)$setting->field_isdefault;
$file_type = (int)$setting->file_type;
$mandatory_field = (int)$setting->mandatory_field;
$unique_field = (int)$setting->unique_field;
$search_show = (int)$setting->search_show;
$array_list = array();
if($label_id != 'role' && $label_id != 'emp_name'){
if(($field_type === 5) || ($field_type === 7)){
if($pick_list_type === 1){
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
if($pick_table == "cw_category"){
$qry = " and prime_category_id != 1";
}else{
$qry = "";
}
$pick_query = "select $pick_list from $pick_table where trans_status = 1 $qry";
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$array_list[""] = "---- $label_name ----";
foreach($pick_result as $pick){
$pick_key = $pick->$pick_list_val_1;
$pick_val = $pick->$pick_list_val_2;
if($label_id === 'employee_code'){
$array_list[$pick_key] = $pick_key.' - '.$pick_val;
}else{
$array_list[$pick_key] = $pick_val;
}
}
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
$pick_query = "select $pick_list_val_1,$pick_list_val_2 from $pick_table where $pick_list_val_3 = 1";
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
$array_list[""] = "---- $label_name ----";
foreach($pick_result as $pick){
$pick_key = $pick->$pick_list_val_1;
$pick_val = $pick->$pick_list_val_2;
$array_list[$pick_key] = $pick_val;
}
}
}
if(($input_view_type === 1) || ($input_view_type === 2)){
$filter[] = array('label_id'=> $label_id, 'field_isdefault'=> $field_isdefault, 'array_list'=> $array_list, 'field_type'=> $field_type);
}
}
}
return $filter;
}
//LOAD TABEL WITH FILTERS
public function search(){
$dec_data = $this->cryptoDecrypt($_POST['Payload']);
$_POST = $dec_data['data'];
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..'));
exit(0);
}
$logged_emp_code = $this->session->userdata('logged_emp_code');
$logged_role = $this->session->userdata('logged_role');
$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']);
$filter_label = $this->input->post('filter_label');
$filter_type = $this->input->post('filter_type');
$field_type_list = $this->input->post('field_type');
$filter_cond = $this->input->post('filter_cond');
$filter_val = $this->input->post('filter_val');
$order_col = $this->input->post('columns');
$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));
$fliter_query = "";
$filter_count = count($filter_label);
for($i=0;$i<=(int)$filter_count;$i++){
$db_name = $filter_label[$i];
$table_name = $filter_type[$i];
$db_cond = $filter_cond[$i];
$db_value = $filter_val[$i];
$field_type = (int)$field_type_list[$i];
if(($db_cond !== "") && ($db_value !== "")){
$search_count++;
if($field_type === 4){
$search_val = '"'.date('Y-m-d',strtotime($db_value)).'"';
}else
if(($field_type === 5) || ($field_type === 7)){
$search_val = '('.rtrim($db_value,',').')';
$db_cond = 'IN';
}else
if($field_type === 13){
$search_val = '"'.date('Y-m-d H:i:s',strtotime($db_value)).'"';
}else{
$search_val = $db_value;
}
if($db_name === 'entry_date'){
$db_table_name = 'cw_time_sheet';
}else{
$db_table_name = 'cw_employees';
}
if((int)$table_name === 1){
$fliter_query .= ' and '. $db_table_name .".". $db_name ." ". $db_cond .' '.$search_val.'';
}
}
}
$common_search = "";
if($search){
$get_form_infos = $this->get_form_infos();
$master_pick_info = $this->master_pick_info();
foreach($get_form_infos 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)){
if($label_id === 'entry_date'){
$db_table_name = 'cw_time_sheet';
}else{
$db_table_name = 'cw_employees';
}
$search_label = "$db_table_name.$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)){
if($master_pick_info){
$result = array_filter($master_pick_info[$label_id], function ($item) use ($search) {
if (stripos($item, $search) !== false) {
return true;
}
return false;
});
}
if($result){
$pick_key = implode('", "', array_keys($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);
}
}
$team = $this->session->userdata('logged_team');
$fil_qry = '';
if((int)$logged_role === 3){
$team = str_replace(',', '|', $team);
$fil_qry =' and cw_employees.team REGEXP "(^|,)('.$team.')(,|$)"';
}else
if((int)$logged_role === 4){
$fil_qry =' and cw_employees.reporting = "'.$logged_emp_code.'"';
}else{
$fil_qry ='';
}
$search_total = $this->db->query("select count(*) as allcount from cw_time_sheet where trans_status = 1");
$search_total_info = $search_total->result();
$total_count = $search_total_info[0]->allcount;
$count_query = "select cw_time_sheet.prime_time_sheet_id,entry_date,cw_time_sheet.employee_code as employee_code,cw_employees.emp_name as emp_name, team_name as team FROM cw_time_sheet inner join cw_employees on cw_employees.employee_code = cw_time_sheet.employee_code inner join cw_team on cw_team.prime_team_id = cw_employees.team WHERE cw_time_sheet.prime_time_sheet_id NOT IN (SELECT prime_time_sheet_id FROM cw_time_sheet_time_line where trans_status = 1) and entry_date >= '".$from_date."' and entry_date <= '".$to_date."' and cw_time_sheet.trans_status = 1 and (cw_employees.employee_status = 1 or cw_employees.employee_status = 2 and inactive_date >= cw_time_sheet.entry_date) ".$fil_qry." $fliter_query $common_search ";
$search_count = $this->db->query($count_query);
$search_info = $search_count->result();
if($search_info){
$filtered_count = count($search_info);
}else{
$filtered_count = 0;
}
$search_query = "select cw_time_sheet.prime_time_sheet_id,entry_date,cw_time_sheet.employee_code as employee_code,cw_employees.emp_name as emp_name, team_name as team FROM cw_time_sheet inner join cw_employees on cw_employees.employee_code = cw_time_sheet.employee_code inner join cw_team on cw_team.prime_team_id = cw_employees.team WHERE cw_time_sheet.prime_time_sheet_id NOT IN (SELECT prime_time_sheet_id FROM cw_time_sheet_time_line where trans_status = 1) and entry_date >= '".$from_date."' and entry_date <= '".$to_date."' and cw_time_sheet.trans_status = 1 and (cw_employees.employee_status = 1 or cw_employees.employee_status = 2 and inactive_date >= cw_time_sheet.entry_date) ".$fil_qry." $fliter_query $common_search ";
$search_query .= " ORDER BY cw_time_sheet.prime_time_sheet_id $order_sor";
if((int)$per_page !== -1){
$search_query .= " LIMIT $start,$per_page";
}
// echo "$search_query";die;
$search_data = $this->db->query($search_query);
$search_result = $search_data->result();
echo json_encode(array("draw" => intval($draw),"recordsTotal" => (int)$total_count ,"recordsFiltered" => (int)$filtered_count,"data" => $search_result));
}
public function get_form_infos(){
$from_query = 'select * from cw_form_setting where prime_form_id in(3,1029,1059,1060) and field_show = "1" and trans_status = "1" and FIND_IN_SET("'.$this->logged_role.'",field_for) ORDER BY input_for,field_sort asc';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$from_query')");
$form_result = $form_data->result();
$form_data->next_result();
return $form_result;
}
public function master_pick_info(){
$get_form_infos = $this->get_form_infos();
foreach($get_form_infos as $setting){
$prime_form_id = (int)$setting->prime_form_id;
$prime_module_id = $setting->prime_module_id;
$input_view_type = (int)$setting->input_view_type;
$input_for = (int)$setting->input_for;
$field_type = (int)$setting->field_type;
$label_id = $setting->label_name;
$label_name = ucwords($setting->view_name);
$field_length = $setting->field_length;
$field_decimals = $setting->field_decimals;
$pick_list_type = (int)$setting->pick_list_type;
$pick_list = $setting->pick_list;
$pick_table = $setting->pick_table;
$auto_prime_id = $setting->auto_prime_id;
$auto_dispaly_value = $setting->auto_dispaly_value;
$field_isdefault = (int)$setting->field_isdefault;
$file_type = (int)$setting->file_type;
$mandatory_field = (int)$setting->mandatory_field;
$unique_field = (int)$setting->unique_field;
$search_show = (int)$setting->search_show;
$default_value = (int)$setting->default_value;
if($default_value === 0){
$default_value = "";
}
$pick_drop = array();
$pick_master = array();
$pick_key = array();
$pick_val = array();
$final_pick = array();
if($field_isdefault === 1){
if(($field_type === 5) || ($field_type === 7)){
// $where_condition = $this->get_role_based_picklist($prime_form_id,$prime_module_id);
if($pick_list_type === 1){
$pick_list_val = explode(",",$pick_list);
$pick_list_val_1 = $pick_list_val[0];
$pick_list_val_2 = $pick_list_val[1];
if($pick_table === "cw_category"){
$pick_query = "select $pick_list from $pick_table where trans_status = 1 and prime_category_id != 1";
}else{
$pick_query = "select $pick_list from $pick_table where trans_status = 1";
}
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
if($pick_result){
$pick_key = array_column($pick_result, $pick_list_val_1);
$pick_val = array_column($pick_result, $pick_list_val_2);
$final_pick = array_combine( $pick_key, $pick_val);
}
$final_pick = array("" => "---- $label_name ----") + $final_pick;
$master_pick[$label_id] = $final_pick;
return $master_pick;
//array_unshift($final_pick,"---- $label_name ----");
// $this->all_pick[$prime_form_id] = $final_pick;
}else
if($pick_list_type === 2){
$pick_list_val_1 = $pick_table."_id";
$pick_list_val_2 = $pick_table."_value";
$pick_list_val_3 = $pick_table."_status";
$pick_query = "select $pick_list_val_1,$pick_list_val_2 from $pick_table where $pick_list_val_3 = 1";
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
if($pick_result){
$pick_key = array_column($pick_result, $pick_list_val_1);
$pick_val = array_column($pick_result, $pick_list_val_2);
$final_pick = array_combine( $pick_key, $pick_val);
}
$final_pick = array("" => "---- $label_name ----") + $final_pick;
$master_pick[$label_id] = $final_pick;
return $master_pick;
//array_unshift($final_pick,"---- $label_name ----");
// $this->all_pick[$prime_form_id] = $final_pick;
}
}else
if($field_type === 9){
$pick_query = "select $auto_prime_id,$auto_dispaly_value from $pick_table where trans_status = 1";
$pick_data = $this->db->query("CALL sp_a_run ('SELECT','$pick_query')");
$pick_result = $pick_data->result();
$pick_data->next_result();
if($pick_result){
$pick_key = array_column($pick_result, $auto_prime_id);
$pick_val = array_column($pick_result, $auto_dispaly_value);
$final_pick = array_combine( $pick_key, $pick_val);
}
$master_pick[$label_id] = $final_pick;
return $master_pick;
}
}
}
}
public function export_to_excel(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Invalid Request..','table_data' => ""));
exit(0);
}
$excel_name = $this->input->post('excel_name');
$form_id = $this->input->post('form_id');
$filter_label = $this->input->post('filter_label');
$filter_type = $this->input->post('filter_type');
$field_type_list = $this->input->post('field_type');
$filter_cond = $this->input->post('filter_cond');
$filter_val = $this->input->post('filter_val');
$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'));
//echo "BSK $start_date :: $end_date"; die;
$from_date = date('Y-m-d',strtotime($start_date));
$to_date = date('Y-m-d',strtotime($end_date));
$fliter_query = "";
$filter_count = count($filter_label);
for($i=0;$i<=(int)$filter_count;$i++){
$db_name = $filter_label[$i];
$table_name = $filter_type[$i];
$db_cond = $filter_cond[$i];
$db_value = $filter_val[$i];
$field_type = (int)$field_type_list[$i];
if(($db_cond !== "") && ($db_value !== "")){
$search_count++;
if($field_type === 4){
$search_val = '"'.date('Y-m-d',strtotime($db_value)).'"';
}else
if(($field_type === 5) || ($field_type === 7)){
$search_val = '('.rtrim($db_value,',').')';
$db_cond = 'IN';
}else
if($field_type === 13){
$search_val = '"'.date('Y-m-d H:i:s',strtotime($db_value)).'"';
}else{
$search_val = $db_value;
}
if($db_name === 'entry_date'){
$db_table_name = 'cw_time_sheet';
}else{
$db_table_name = 'cw_employees';
}
if((int)$table_name === 1){
$fliter_query .= ' and '. $db_table_name .".". $db_name ." ". $db_cond .' '.$search_val.'';
}
}
}
$common_search = "";
if($search){
$get_form_infos = $this->get_form_infos();
$master_pick_info = $this->master_pick_info();
foreach($get_form_infos 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)){
if($label_id === 'entry_date'){
$db_table_name = 'cw_time_sheet';
}else{
$db_table_name = 'cw_employees';
}
$search_label = "$db_table_name.$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)){
if($master_pick_info){
$result = array_filter($master_pick_info[$label_id], function ($item) use ($search) {
if (stripos($item, $search) !== false) {
return true;
}
return false;
});
}
if($result){
$pick_key = implode('", "', array_keys($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);
}
}
$logged_emp_code = $this->session->userdata('logged_emp_code');
$logged_role = $this->session->userdata('logged_role');
if((int)$logged_role === 5){
$fil_qry =' and cw_employees.role = "'.$logged_role.'" and cw_employees.employee_code = "'.$logged_emp_code.'"';
}else
if((int)$logged_role === 4){
$fil_qry =' and cw_employees.role = 5 and cw_employees.reporting = "'.$logged_emp_code.'"';
}else
if((int)$logged_role === 3){
$fil_qry =' and cw_employees.role = 4 and cw_employees.reporting = "'.$logged_emp_code.'"';
}else{
$fil_qry ='';
}
$search_query = "select cw_time_sheet.prime_time_sheet_id,entry_date,cw_time_sheet.employee_code as employee_code,cw_employees.emp_name as emp_name, team_name as team FROM cw_time_sheet inner join cw_employees on cw_employees.employee_code = cw_time_sheet.employee_code inner join cw_team on cw_team.prime_team_id = cw_employees.team WHERE cw_time_sheet.prime_time_sheet_id NOT IN (SELECT prime_time_sheet_id FROM cw_time_sheet_time_line where trans_status = 1) and entry_date >= '".$from_date."' and entry_date <= '".$to_date."' and cw_time_sheet.trans_status = 1 and (cw_employees.employee_status = 1 or cw_employees.employee_status = 2 and inactive_date >= cw_time_sheet.entry_date) ".$fil_qry." $fliter_query $common_search ";
$search_query .= " ORDER BY cw_time_sheet.prime_time_sheet_id $order_sor";
$search_data = $this->db->query($search_query);
$search_result = $search_data->result();
echo json_encode(array('success' => true, 'search_result'=>$search_result));
}
}
?>