File: /home/cafsindia/cpaqua.cafsinfotech.in/application/controllers/Leave_wizard.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Leave_wizard extends Action_controller{
public function __construct(){
parent::__construct('leave_wizard');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
$from_query = 'select * from cw_form_setting where prime_module_id = "employees" and search_show = "1" and input_view_type != 3 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;
//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" and label_name not in ("emp_name","employee_code") and table_show = 1 and input_view_type != 3 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 ----";
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;
$data['key'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
//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;
}
//EMPLOYEES LEAVE OPENING DATA
public function check_leave_wizard(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
//SEARCH INFO FUNCTION
$this->search_info();
$termination_status = (int)$this->input->post('termination_status');
$category = implode(",",$this->input->post('category') ?? []);
$emp_component = implode('","',$this->input->post('emp_component') ?? []);
$leave_name_arr = $this->input->post('leave_name');
$report_type = $this->input->post('type_of_leave_report');
$from_date = date('Y-m-d',strtotime($this->input->post('from_date')));
$to_date = date('Y-m-d',strtotime($this->input->post('to_date')));
$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;
$fliter_query = "";
//LEAVE CREATION QRY FOR GET A ALL LEAVE NAME for add a
$leave_name_qry = 'SELECT prime_leave_creation_id,leave_description,leave_name 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_names = array_reduce($leave_name_rslt,function($result, $arr){
$result[$arr['leave_name']] = $arr['leave_description'];
return $result;
}, array());
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.'"';
}
}
}
//LEAVE FINANCIAL YEAR GET FUNCTION
$financial_info = $this->get_leave_financial_details();
$prime_financial_id = $financial_info[0]->prime_leave_financial_year_id;
$leave_name_qry = "";
$date_qry = "";
$leave_join_qry = ' inner join cw_leave_opening on cw_leave_opening.employee_code = cw_employees.employee_code';
//LEAVE CREATION RECORDS
foreach($leave_names as $leave_key => $leave_desc){
foreach($leave_name_arr as $key => $leaves){
if($leaves === $leave_key){
$leave_name = strtolower($leaves);
if((int)$report_type === 1){
$leave_name_qry .= ', cw_leave_opening.'.$leave_name.' AS `'.$leave_desc.'`';
$date_qry .= ' and cw_leave_opening.financial_setting_id = "'.$prime_financial_id.'" and cw_leave_opening.trans_status = 1';
}else
if((int)$report_type === 2){
$leave_name_qry .= ', ((cw_leave_opening.'.$leave_name.' + cw_leave_opening.'.$leave_name.'_credit) - (cw_leave_opening.'.$leave_name.'_debit + cw_leave_opening.used_'.$leave_name.' + cw_leave_opening.pending_'.$leave_name.' + cw_leave_opening.encash_'.$leave_name.')) AS `Balance '.$leave_desc.'`';
$date_qry .= ' and cw_leave_opening.financial_setting_id = "'.$prime_financial_id.'" and cw_leave_opening.trans_status = 1';
}else
if((int)$report_type === 3){
// $leave_name_qry .= ',SUM(CASE WHEN LOWER(leave_name) = "'.$leave_name.'" THEN leave_count ELSE 0 END) as '.$leave_name;
$leave_name_qry .= ', SUM(CASE WHEN LOWER(leave_name) = "'.$leave_name.'" THEN leave_count ELSE 0 END) AS `'.$leave_desc.'`';
$leave_join_qry = ' inner join cw_leave_entry on cw_leave_entry.employee_code = cw_employees.employee_code inner join cw_leave_creation on cw_leave_creation.prime_leave_creation_id = cw_leave_entry.leave_type';
$date_qry = ' and cw_leave_entry.leave_date between "'.$from_date.'" and "'.$to_date.'" and cw_leave_entry.leave_status = 2 and cw_leave_entry.trans_status = 1';
}else
if((int)$report_type === 4){
$leave_name_qry .= ', cw_leave_opening.'.$leave_name.' AS `'.$leave_desc.'`,cw_leave_opening.'.$leave_name.' AS `'.$leave_desc.' Credit`,cw_leave_opening.'.$leave_name.' AS `'.$leave_desc.' Debit`,cw_leave_opening.used_'.$leave_name.' AS `'.$leave_desc.' Used`,cw_leave_opening.pending_'.$leave_name.' AS `'.$leave_desc.' Pending`,cw_leave_opening.encash_'.$leave_name.' AS `'.$leave_desc.' Encash`';
$date_qry .= ' and cw_leave_opening.financial_setting_id = "'.$prime_financial_id.'" and cw_leave_opening.trans_status = 1';
}
}
}
}
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;
$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_name.'`';
}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_name`;
}else{
$select_query .= ",$pick_query_as.$pick_list_val_2 as `$label_name`";
}
$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;
}
// $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)){
$select_query .= ",$pick_query_as.$pick_list_val_2 as `$label_name`";
$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_name`";
$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 as `$label_name`";
}else
if($field_isdefault === 2){
$select_query .= ",$pick_sel_table.$label_id as `$label_name`";
}
}
}
}
}
$role_condition = "";
if($this->role_condition){
$role_condition = $this->role_condition;
}
$termination_qry = "";
if((int)$termination_status === 0){
$termination_qry = "and cw_employees.termination_status = 0";
}
$wizard_qry = 'SELECT cw_employees.employee_code as `Employee Code`,cw_employees.emp_name as `Employee Name`'.$select_query.''.$leave_name_qry.' FROM `cw_employees` '.$pick_query_join.' '.$leave_join_qry.' where cw_employees.trans_status = 1 '.$termination_qry.' '.$date_qry.' and cw_employees.role in ('.$category.')'.$fliter_query.' '.$role_condition.' GROUP BY cw_employees.employee_code';
$wizard_info = $this->db->query("CALL sp_a_run ('SELECT','$wizard_qry')");
$wizard_rslt = $wizard_info->result();
$wizard_info->next_result();
$filter_list = $this->get_filter_data($wizard_rslt);
$wizard_rslt = json_decode(json_encode($wizard_rslt),true);
$export_arr = $wizard_rslt;
$column_names = false;
// // run loop through each row in $customers_data
$test = array('test'=>'Leave Wizard Report');
echo implode("\t", array_values($test) ?? []) . "\n";
foreach($export_arr as $row){
if(!$column_names){
echo implode("\t", array_keys($row ?? []) ?? []) . "\n";
$column_names = true;
}
echo implode("\t", array_values($row ?? []) ?? []) . "\n";
}
}
//RETRIEVE OPTION BASED ON TYPE OF LEAVE REPORT !
public function leave_report(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$leave_report = $this->input->post('leave_report');
if((int)$leave_report === 1 || (int)$leave_report === 2 || (int)$leave_report === 4){
$where_cond = "where leave_opening = 1 ";
}
$leave_detail_query = 'SELECT CONCAT("<option value =",leave_name," >",leave_description,"</option>") as result_data FROM cw_leave_creation '.$where_cond.' ';
$leave_name_info = $this->db->query("CALL sp_a_run ('SELECT','$leave_detail_query')");
$leave_name_rslt = $leave_name_info->result();
$leave_name_info->next_result();
$result_data = array_column($leave_name_rslt,'result_data');
$option = "<option value=''>---- Select Employee ----</option>".implode('',$result_data ?? []);
if(empty($leave_name_rslt)){
echo json_encode(array('success'=>FALSE,'message'=>"No Data Found.!"));
}else{
echo json_encode(array('success'=>TRUE,'option'=>$option));
}
}
}
?>