File: /home/cafsindia/cloud_cafsinfotech_in/application/controllers/Muster_roll_wizard.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Muster_roll_wizard extends Action_controller{
public function __construct(){
parent::__construct('muster_roll_wizard');
$this->prime_table = "cw_muster_transaction";
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$this->load->view("$this->control_name/manage");
}
//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']);
$common_search = "";
if($search){
$common_search .= ' or '.$this->prime_table.'.prime_muster_setting_id like "'.$search.'%"';
$common_search .= ' or '.$this->prime_table.'.setting_name 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 = "select count(*) as allcount from $this->prime_table where $this->prime_table.trans_status = 1";
$search_total = $this->db->query($count_all_query);
$search_total_info = $search_total->result();
$total_count = $search_total_info[0]->allcount;
$count_query = "select count(*) as allcount from $this->prime_table 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;
$search_query = "select * from $this->prime_table where $this->prime_table.trans_status = 1 $common_search";
$search_query .= " ORDER BY $order_col $order_sor";
if((int)$per_page !== -1){
$search_query .= " 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($prime_muster_transaction_id=-1){
//VIEW, FORM INPUT
$muster_transaction_qry = $this->db->query("CALL sp_a_run ('SELECT','select * from cw_muster_transaction where cw_muster_transaction.prime_muster_transaction_id = \"$prime_muster_transaction_id\" and cw_muster_transaction.trans_status = 1')");
$muster_transaction_rlt = $muster_transaction_qry->row();
$muster_transaction_qry->next_result();
$data['muster_transaction_info'] = $muster_transaction_rlt;
//get all the earning columns without tax column
$get_match_columns = 'SELECT prime_form_id,prime_module_id,label_name,view_name FROM `cw_form_setting` WHERE prime_module_id = "employees" order by view_name asc';
$match_column_info = $this->db->query("CALL sp_a_run ('SELECT','$get_match_columns')");
$match_column_result = $match_column_info->result();
$match_column_info->next_result();
$match_columns[""] = "---- Select Column ----";
foreach($match_column_result as $match_column){
$prime_form_id = $match_column->prime_form_id;
$column_value = $match_column->label_name;
$view_name = $match_column->view_name;
$match_columns[$this->xss_clean($column_value)] = $this->xss_clean($view_name);
}
$data['emp_component_list'] = $match_columns;
$role_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1 and prime_category_id !=1')");
$role_result = $role_info->result();
$role_info->next_result();
$category_list[""] = "---- Select Category ----";
// $category_list[1] = "All Category";
foreach($role_result as $for){
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$category_list[$role_id] = $category_name;
}
$data['category_list'] = $category_list;
//Get All Deduction Component
$get_comp_qry = 'SELECT prime_day_status_legends_id,legends,custom_value FROM `cw_day_status_legends` WHERE trans_status = 1';
$get_comp_info = $this->db->query("CALL sp_a_run ('SELECT','$get_comp_qry')");
$get_comp_result = $get_comp_info->result();
$get_comp_info->next_result();
$muster_component[""] = "---- Select Column ----";
foreach($get_comp_result as $comp_column){
$prime_form_id = $comp_column->prime_day_status_legends_id;
$column_value = $comp_column->legends;
$custom_val = $comp_column->custom_value;
$muster_component[$this->xss_clean($column_value)] = $this->xss_clean($custom_val);
}
$data['muster_component_list'] = $muster_component;
$data['time_office_component_list'] = $this->get_time_office_components();
$this->load->view("$this->control_name/form",$data);
}
//SAVE MODEL DATA TO DATA BASE
public function save_setting(){
$prime_muster_transaction_id = $this->input->post('prime_muster_transaction_id');
$setting_name = $this->input->post('setting_name');
$created_on = date("Y-m-d H:i:s");
if((int)$prime_muster_transaction_id === 0){
$insert_query = 'insert into cw_muster_transaction (setting_name,trans_created_by,trans_created_date) values ("'.$setting_name.'","'.$this->session->userdata('logged_id').'","'.$created_on.'")';
$insert_info = $this->db->query("CALL sp_a_run ('INSERT','$insert_query')");
$insert_result = $insert_info->result();
$insert_info->next_result();
$insert_id = $insert_result[0]->ins_id;
if($insert_result){
echo json_encode(array('success' => TRUE, 'prime_id' => $insert_id, 'message' => "Successfully Inserted"));
}else{
echo json_encode(array('success' => FALSe, 'message' => "Insertion failed, Please Try Again"));
}
}else
if((int)$prime_muster_transaction_id > 0){
$update_muster_transaction = 'trans_updated_by = "'. $this->session->userdata('logged_id') .'",trans_updated_date = "'.$created_on.'",setting_name = "'.$setting_name.'"';
//UPDATE QRY
$update_query = 'UPDATE cw_muster_transaction SET '. $update_muster_transaction .' WHERE cw_muster_transaction.prime_muster_transaction_id = "'. $prime_muster_transaction_id .'"';
if($this->db->query("CALL sp_a_run ('UPDATE','$update_query')")){
echo json_encode(array('success' => TRUE, 'prime_id' => $prime_muster_transaction_id, 'message' => "Successfully Updated"));
}else{
echo json_encode(array('success' => FALSe, 'message' => "Update failed, Please Try Again"));
}
}else{
echo json_encode(array('success' => False, 'message' => "Please contact Admin.!"));
}
}
public function save_muster_info(){
$muster_transaction_id = (int)$this->input->post('muster_transaction_id');
$category = ltrim(implode(",",$this->input->post('category[]')),',');
$emp_component = ltrim(implode(",",$this->input->post('emp_component[]')),',');
$time_office_component = ltrim(implode(",",$this->input->post('time_office_component[]')),',');
$set_default = (int)$this->input->post('set_default');
// $count_type = ltrim(implode(",",$this->input->post('count_type[]')),',');
$created_on = date("Y-m-d H:i:s");
if($muster_transaction_id > 0){
//UPDATE A DEFAULT CHOOSE FOR SET 1 OR 0
if((int)$set_default || $muster_transaction_id === 1){
$set_default = 1;
}else{
$set_default = 0;
}
$update_transaction_info = 'trans_updated_by = "'. $this->session->userdata('logged_id') .'",trans_updated_date = "'.$created_on.'",category = "'.$category.'",emp_component = "'.$emp_component.'",time_office_component = "'.$time_office_component.'",set_default = "'.$set_default.'"';
//UPDATYE QRY
$update_query = 'UPDATE cw_muster_transaction SET '. $update_transaction_info .' WHERE cw_muster_transaction.prime_muster_transaction_id = "'. $muster_transaction_id .'"';
if($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' => "Update failed, Please Try Again"));
}
}else{
echo json_encode(array('success' => False, 'message' => "Please Fill Previous Tab Info.!"));
}
}
//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;
}
}
//GENERATE EXCELS
public function generate_excel($prime_muster_transaction_id = -1){
$muster_transaction_qry = $this->db->query("CALL sp_a_run ('SELECT','select * from cw_muster_transaction where cw_muster_transaction.prime_muster_transaction_id = \"$prime_muster_transaction_id\" and cw_muster_transaction.trans_status = 1')");
$muster_transaction_rlt = $muster_transaction_qry->result();
$muster_transaction_qry->next_result();
$data['muster_transaction_info'] = $muster_transaction_rlt;
$category = $muster_transaction_rlt[0]->category;
//for fliter option
$from_query = 'select * from cw_form_setting where prime_module_id = "employees" and field_show = "1" and label_name != "role" 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();
$this->fliter_list = $this->get_filter_data($form_result);
$data['fliter_list'] = $this->fliter_list;
$this->load->view("$this->control_name/generate_excel",$data);
}
public function emp_code_list(){
$muster_transaction_id = (int)$this->input->post('muster_transaction_id');
$report_period = (int)$this->input->post('report_period');
$process_month = $this->input->post('process_month');
$process_date = date("Y-m-d",strtotime("01-".$process_month));
if($report_period === 1){
//report type 1
$start_date = $process_date;
$end_date = date('Y-m-t',strtotime($process_date));
}else{
//report type 2
$start_date = date("Y-m-d",strtotime($this->input->post('start_date')));
$end_date = date("Y-m-d",strtotime($this->input->post('end_date')));
}
$muster_transaction_qry = 'select * from cw_muster_transaction where cw_muster_transaction.prime_muster_transaction_id = "'.$muster_transaction_id.'" and cw_muster_transaction.trans_status = 1';
$muster_transaction_info = $this->db->query("CALL sp_a_run ('SELECT','$muster_transaction_qry')");
$muster_transaction_rslt = $muster_transaction_info->result();
$muster_transaction_info->next_result();
$category = $muster_transaction_rslt[0]->category;
$emp_code_qry = 'SELECT cw_employees.employee_code,cw_employees.emp_name FROM `cw_employees` where cw_employees.trans_status = 1 and (cw_employees.termination_status = 0 or (cw_employees.resignation_date between "'.$start_date.'" and "'.$end_date.'" or cw_employees.resignation_date >= "'.$end_date.'")) and role in ('.$category.') order by employee_code asc';
$emp_code_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_code_qry')");
$emp_code_rslt = $emp_code_info->result();
$emp_code_info->next_result();
$emp_code_arr = "<option value = ''>Select Code</option>";
foreach($emp_code_rslt as $rslt){
$emp_code_arr .= "<option value = '".$rslt->employee_code."'>".$rslt->employee_code." - ".$rslt->emp_name."</option>";
}
echo $emp_code_arr;
}
public function get_muster_report(){
//SEARCH INFO FUNCTION
$this->search_info();
$muster_transaction_id = (int)$this->input->post('muster_transaction_id');
$report_period = (int)$this->input->post('report_period');
$employee_type = (int)$this->input->post('employee_type');
$employee_code = $this->input->post('employee_code');
//report type 1
$process_month = $this->input->post('process_month');
$process_date = date("Y-m-d",strtotime("01-".$process_month));
//report type 2
$start_date = date("Y-m-d",strtotime($this->input->post('start_date')));
$end_date = date("Y-m-d",strtotime($this->input->post('end_date')));
$fliter_query = "";
//Muster transaction qry
$muster_transaction_qry = 'select * from cw_muster_transaction where cw_muster_transaction.prime_muster_transaction_id = "'.$muster_transaction_id.'" and cw_muster_transaction.trans_status = 1';
$muster_transaction_info = $this->db->query("CALL sp_a_run ('SELECT','$muster_transaction_qry')");
$muster_transaction_rslt = $muster_transaction_info->result();
$muster_transaction_info->next_result();
$category = $muster_transaction_rslt[0]->category;
$emp_component = implode('","',explode(",",$muster_transaction_rslt[0]->emp_component));
$time_office_comp_arr = explode(",",$muster_transaction_rslt[0]->time_office_component);
$time_office_comp_arr1 = explode(",",$muster_transaction_rslt[0]->time_office_component);
//for key and value would change from same array
$show_time_office_comp_arr = array_combine($time_office_comp_arr, $time_office_comp_arr1);
//time office components count array build codes
$count_type_arr = array(""=>"---- Select Column----","P"=>"Present Count","A"=>"Absend Count","L"=>"Leave Count","H"=>"Holiday Count","W"=>"Weekoff Count","OD"=>"Onduty Count","M"=>"Manualpunch Count");
//LEAVE CREATION QRY FOR GET A ALL LEAVE NAME for add a
$leave_name_qry = 'SELECT prime_leave_creation_id,leave_name,CONCAT_WS(" ",leave_name,"count") as leave_count FROM cw_leave_creation WHERE trans_status = 1';
$leave_name_info = $this->db->query("CALL sp_a_run ('SELECT','$leave_name_qry')");
$leave_name_rslt = $leave_name_info->result_array();
$leave_name_info->next_result();
$leave_name_arr = array_reduce($leave_name_rslt, function ($result, $arr) {
$result[$arr['leave_name']] = $arr['leave_count'];
return $result;
}, array());
//sHIFT MASTER QRY FOR GET A ALL SHIFT NAME
$shift_name_qry = 'SELECT prime_shift_master_id,shift_name,CONCAT_WS(" ",shift_name,"count") as shift_count FROM cw_shift_master WHERE trans_status = 1';
$shift_name_info = $this->db->query("CALL sp_a_run ('SELECT','$shift_name_qry')");
$shift_name_rslt = $shift_name_info->result_array();
$shift_name_info->next_result();
$shift_name_arr = array_reduce($shift_name_rslt, function ($result, $arr) {
$result[$arr['shift_name']] = $arr['shift_name'];
return $result;
}, array());
$all_time_office_comp_arr = $count_type_arr + $leave_name_arr + $shift_name_arr;
if($report_period === 1){
$start_date = $process_date;
$end_date = date('Y-m-t',strtotime($process_date));
}
if($employee_type === 1){
$fliter_query = ' and employee_code = "'.$employee_code.'"';
}else
if($employee_type === 2){
$fliter_label = $this->input->post('fliter_label');
$fliter_type = $this->input->post('fliter_type');
$filter_cond = $this->input->post('filter_cond');
$fliter_val = $this->input->post('fliter_val');
$filter_count = count($fliter_label);
$search_count = 0;
for($i=0;$i<=(int)$filter_count;$i++){
$db_name = $fliter_label[$i];
$table_name = $fliter_type[$i];
$db_cond = $filter_cond[$i];
$db_value = $fliter_val[$i];
$field_type = $field_type[$i];
if(($db_cond) && ($db_value)){
$search_count++;
if((int)$field_type === 4){
$search_val = date("Y-m-d",strtotime($db_value));
}else{
$search_val = $db_value;
}
if($db_cond === "LIKE"){ $search_val = "$db_value%"; }
if((int)$table_name === 1){
$this->prime_table = "cw_employees";
$fliter_query .= ' and '. $this->prime_table .".". $db_name ." ". $db_cond .' "'.$search_val.'"';
}
}
}
}
if($emp_component){
//FOR DATE FORMAT AND PICKLIST VALUE SHOW IN TABLE ROW OR EXCEL ROW
$find_label_qry = 'select * from cw_form_setting where trans_status = 1 and prime_module_id = "employees" and label_name in ("'.$emp_component.'")';
$find_label_info = $this->db->query("CALL sp_a_run ('SELECT','$find_label_qry')");
$label_result = $find_label_info->result();
$find_label_info->next_result();
$select_query = "";
foreach($label_result as $rslt){
$prime_form_id = (int)$rslt->prime_form_id;
$prime_module_id = $rslt->prime_module_id;
$input_view_type = (int)$rslt->input_view_type;
$input_for = (int)$rslt->input_for;
$field_type = (int)$rslt->field_type;
$label_id = $rslt->label_name;
$label_name = ucwords($rslt->view_name);
$field_length = $rslt->field_length;
$field_decimals = $rslt->field_decimals;
$pick_list_type = (int)$rslt->pick_list_type;
$pick_list = $rslt->pick_list;
$pick_table = $rslt->pick_table;
$auto_prime_id = $rslt->auto_prime_id;
$auto_dispaly_value = $rslt->auto_dispaly_value;
$field_isdefault = (int)$rslt->field_isdefault;
$pick_display = $rslt->pick_display_value;
$table_name = "cw_employees";
if($field_isdefault === 1){
$pick_sel_table = "$table_name";
}
if((int)$prime_form_id === 0){
$label_name = ucwords(str_replace("_"," ",$label_list));
$label_id = $label_list;
}
//TABLE HEADER
$table_array[] = array('field_type'=>$field_type,'label_name'=>$label_id,'view_name'=>$label_name);
$array_list = array();
//SEARCH FILTERS
if($field_type === 4){
$select_query .= 'CASE WHEN '.$pick_sel_table.'.'.$label_id.' IS NULL or '.$pick_sel_table.'.'.$label_id.' = "1970-01-01" or '.$pick_sel_table.'.'.$label_id.' = "0000-00-00" then "" else DATE_FORMAT('.$pick_sel_table.'.'.$label_id.', "%d-%m-%Y") end as '.$label_id.',';
}else
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];
//pick query for get a picklist data
$pick_query = "select $pick_list from $pick_table where trans_status = 1 $where_condition";
$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;
}
// $this->all_pick[$prime_form_id] = $array_list;
$pick_query_as = $pick_table."_".$prime_form_id;
if(($input_view_type === 1) || ($input_view_type === 2)){
if($pick_table === "cw_employees"){
$select_query .= 'CONCAT_WS(" - ", '.$pick_query_as.'.'.$pick_list_val_1.','.$pick_query_as.'.'.$pick_list_val_2.') as '.$label_id.',';
}else{
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_id,";
}
$pick_query_join .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $pick_sel_table.$label_id ";
}
}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;
}
$pick_query_as = $pick_table."_".$prime_form_id;
if(($input_view_type === 1) || ($input_view_type === 2)){
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_id,";
$pick_query_join .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $pick_sel_table.$label_id ";
}
}
}else
if($field_type === 9){
$pick_query_as = $pick_table."_".$prime_form_id;
if(($input_view_type === 1) || ($input_view_type === 2)){
$select_query .= "$pick_query_as.$auto_dispaly_value as $label_id,";
$pick_query_join .= " left join $pick_table as $pick_query_as on $pick_query_as.$auto_prime_id = $pick_sel_table.$label_id ";
}
}else
if($field_type === 13){
$select_query .= 'DATE_FORMAT('.$pick_sel_table.'.'.$label_id.', "%d-%m-%Y %H:%i:%s") as '.$label_id.',';
}else{
if(($input_view_type === 1) || ($input_view_type === 2)){
if($field_isdefault === 1){
$select_query .= "$pick_sel_table.$label_id,";
}else
if($field_isdefault === 2){
$select_query .= "$pick_sel_table.$label_id,";
}
}
}
}
}
$role_condition = "";
if($this->role_condition){
$role_condition = $this->role_condition;
}
// $select_query = rtrim($select_query,",");
$emp_qry = 'SELECT '.$select_query.'cw_employees.employee_code,cw_employees.emp_name FROM `cw_employees` '.$pick_query_join.' where cw_employees.trans_status = 1 and (cw_employees.termination_status = 0 or cw_employees.resignation_date >= "'.$start_date.'") and cw_employees.role in ('.$category.')'.$fliter_query.' '.$role_condition;
// between "'.$start_date.'" and "'.$end_date.'" or cw_employees.resignation_date >= "'.$end_date.'"))
$emp_info = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
$emp_rslt = $emp_info->result_array();
$emp_info->next_result();
$emp_rslt_arr = array_reduce($emp_rslt, function ($result, $arr) {
$result[$arr['employee_code']] = $arr;
return $result;
}, array());
//$muster_report = $this->get_muster_details($emp_rslt_arr,$start_date,$end_date,$category,$emp_component,$show_time_office_comp_arr,$all_time_office_comp_arr);
$muster_report = $this->get_muster_details($emp_rslt_arr,$start_date,$end_date,$category,$emp_component,$show_time_office_comp_arr,'report',$process_month,'');
echo json_encode(array('success'=>TURE, 'muster_report' => $muster_report, 'message'=>'Successfully Report Generated..!!'));
}
//PROVIDE QUERY AND DROPDOWN VALUES
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 != 'employee_code' && $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 = "";
}
if($pick_table == "cw_payroll_formula"){
$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();
$array_list[""] = "---- $label_name ----";
foreach($pick_result as $pick){
$pick_key = $pick->$pick_list_val_1;
$pick_val = ucwords(str_replace("_"," ",$pick->$pick_list_val_2));
$array_list[$pick_key] = $pick_val;
}
}else{
if($label_id === "excemption_component"){
$pick_query = "select $pick_list from $pick_table where trans_status = 1 and tax_section = 1 $qry";
}else{
$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;
$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;
}
//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');
$input_val = $this->input->post('input_val');
if($input_val){
chmod($input_val, 0777);
unlink($input_val);
}
$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"));
}
}
//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);
}
}
?>