File: /home/cafsindia/cpaqua.cafsinfotech.in/application/controllers/Monthly_input_bk.php
<?php
/**********************************************************
Filename: Monthly Input
Description: Monthly Input for adding input.
Author: Jaffer Sathikz
Created on: 28 December 2018
Reviewed by: udhayakumar Anandhan
Reviewed on: 09-FEB-2019
Approved by:
Approved on:
-------------------------------------------------------
Modification Details
Changed by:
Change Info:
-------------------------------------------------------
***********************************************************/
if (!defined('BASEPATH'))
exit('No direct script is allowed');
require_once("Action_controller.php");
class Monthly_input extends Action_controller{
public function __construct(){
parent::__construct('monthly_input');
$this->load->model('Process_payroll_model');
if (!$this->Appconfig->isAppvalid()) {
redirect('config');
}
$lock_query = 'select GROUP_CONCAT(previous_column) as previous_column from cw_monthly_input_previous inner join cw_form_setting on cw_form_setting.label_name = previous_column where cw_monthly_input_previous.trans_status = 1 and column_status in (1)';
$lock_data = $this->db->query("CALL sp_a_run ('SELECT','$lock_query')");
$lock_result = $lock_data->result();
$lock_data->next_result();
$previous_column = $lock_result[0]->previous_column;
$prev_qry = "";
if($previous_column){
$previous_column = '"'.str_replace(',', '","', $previous_column).'"';
$prev_qry = 'and label_name not in ('.$previous_column.')';
}
$table_query = 'select label_name,view_name,field_type,date_type from cw_form_setting where prime_module_id = "employees" and trans_status = "1" and (earn_month_check = "1" OR deduction_month_check = "1") and FIND_IN_SET("'.$this->logged_user_role.'",user_right_for) '.$prev_qry.' ORDER BY monthly_input_sort asc';
$table_info = $this->db->query("CALL sp_a_run ('SELECT','$table_query')");
$result = $table_info->result();
$table_info->next_result();
$this->table_head_val = $result;
$select_key = array_column($result, "label_name");
$this->mi_select_query = "$this->prime_table.$this->prime_id,";
$this->mi_select_query .= "cw_monthly_input.".implode(",cw_monthly_input.",$select_key);
}
// LOAD PAGE WITH TABLE DATA
public function index(){
//GET Monthly Input Columns
//PAGE INFO FUNCTION
$data['table_head'] = $this->table_head_val;
$this->page_info();
$data['module_sts'] = (int)$this->module_sts;
$data['pick_list_arr'] = $this->pick_list;
$data['fliter_list'] = $this->fliter_list;
$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;
$lock_query = 'select previous_column,column_status from cw_monthly_input_previous inner join cw_form_setting on cw_form_setting.label_name = previous_column where cw_monthly_input_previous.trans_status = 1 and column_status in (1,2)';
$lock_data = $this->db->query("CALL sp_a_run ('SELECT','$lock_query')");
$lock_result = $lock_data->result_array();
$lock_data->next_result();
$lock_result = array_reduce($lock_result, function ($result, $arr) {
$result[$arr['previous_column']] = $arr['column_status'];
return $result;
}, array());
$data['lock_result'] = $lock_result;
$data['module_id'] = $this->control_name;
//Add Additional Information from the settings
if($this->company_info[0]->mi_process_type !== 'role' && $this->company_info[0]->mi_process_type !== '0'){
$get_components = 'select pick_list,pick_list_type,view_name,pick_table,label_name from `cw_form_setting` where prime_module_id = "employees" and input_view_type in (1,2) and field_type = 5 and label_name = "'.$this->company_info[0]->mi_process_type.'"';
$get_components_info = $this->db->query("CALL sp_a_run ('SELECT','$get_components')");
$get_components_result = $get_components_info->result();
$get_components_info->next_result();
$pick_table = $get_components_result[0]->pick_table;
$pick_list = $get_components_result[0]->pick_list;
$pick_list_type = (int)$get_components_result[0]->pick_list_type;
$label_name = $get_components_result[0]->label_name;
$view_name = $get_components_result[0]->view_name;
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_display){
$pick_list = "$pick_list_val_1,CONCAT_WS(\" - \", $pick_display) as $pick_list_val_2";
}
$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("ALL" => "---- Select All ----") + $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);
}
//$all_option = array("ALL" => "--- Select All ---");
$final_pick = array("ALL" => "---- Select All ----") + $final_pick;
}
$mi_process_type = array("form_label"=>form_label($view_name, 'add_component', array('class' => '')),"form_input"=>form_dropdown(array('name' =>'add_component','id' =>'add_component','class' =>'form-control input-sm select2'),$final_pick));
}else{
$mi_process_type = "";
}
$data['mi_process_type'] = $mi_process_type;
$this->load->view('monthly_input/manage', $data);
}
//LOAD PAGE TABLE VIEW WITH DATA BASED ON SEARCH FILTERS
public function search(){
$this->search_info();
$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->mi_select_query,$this->base_query);
$access_data = $this->session->userdata('access_data');
$tab_name = $this->input->post('tab_name');
$role = $this->input->post('category');
$search_month = $this->input->post('search_month');
$process_mode = $this->input->post('process_mode');
$add_component = $this->input->post('add_component');
$add_query = "";
$emp_qry = "";
if($add_component && $add_component !== 'undefined' && $add_component !== 'ALL'){
$add_query = ' and '.$this->prime_table.'.'.$this->company_info[0]->mi_process_type.' = "' . $add_component . '"';
$emp_qry = ' and cw_employees.'.$this->company_info[0]->mi_process_type;
$emp_qry = ' and cw_employees.'.$this->company_info[0]->mi_process_type;
}
$common_search = "";
if($search){
$common_search .= 'and (cw_monthly_input.emp_name like "'.$search.'%" or cw_monthly_input.date_of_joining like "'.date('Y-m-d',strtotime($search)).'%" or cw_monthly_input.employee_code like "'.$search.'%")';
}
if($tab_name === 'view' || $tab_name === 'edit'){
$add_column = "";
if($this->company_info[0]->mi_process_type !== 'role' && $this->company_info[0]->mi_process_type !== ''){
$add_column = ",".$this->company_info[0]->mi_process_type;
}
$status_qry = " and input_status = 0";
if($tab_name === 'edit'){
$status_qry = " and input_status in(0,2)";
}
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
if($role === ""){
echo json_encode(array("draw" => intval($draw),"recordsTotal" => 0,"recordsFiltered" => 0,"data" => 0));
exit(0);
}
if($search_month){
$date_search = explode("-", $search_month);
$search_date = $date_search[1] . "-" . $date_search[0] . "-01";
//Check monthy input data exist Start
$month_input_exist_qry = 'select employee_code from cw_monthly_input where trans_status = 1 and cw_monthly_input.role = "' . $role . '" and process_month = "' . $search_month . '" and cw_monthly_input.termination_status = 0 and cw_monthly_input.payroll = 1 limit 0,1';
$month_input_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$month_input_exist_qry')");
$month_input_exist_rslt = $month_input_exist_data->result();
$month_input_exist_data->next_result();
$month_input_exist_count = (int) $month_input_exist_data->num_rows();
//Check monthy input data exist END
//Get Month Days
$salary_days = $this->Process_payroll_model->get_salary_date($role);
if($salary_days){
$day_conditions = $salary_days['day_conditions'];
if($day_conditions === 3){
$sal_start_date = date("Y-m", strtotime("-1 month", strtotime($search_date)))."-".$salary_days['day_start'];
$sal_end_date = date("Y-m-" . $salary_days['day_end'], strtotime($search_date));
}else{
$sal_start_date = date("Y-m-".$salary_days['day_start'], strtotime($search_date));
$sal_end_date = date("Y-m-".$salary_days['day_end'], strtotime($search_date));
if($day_conditions === 2){
$sal_end_date = date("Y-m-t", strtotime($search_date));
}
}
}else{
echo json_encode(array(
'success' => false,
'status' => "Monthday",
'message' => "Month days Not Set"
));
exit(0);
}
//and (cw_employees.termination_status = 0 or cw_employees.resignation_date >= "'.$sal_start_date.'")
// Zero Entry in monthly input then insert data Start
if ($month_input_exist_count === 0){
$save_monthly_query = 'INSERT INTO cw_monthly_input(employees_id,role,employee_code,emp_name,termination_status,date_of_joining,process_month,payroll '.$add_column.') SELECT prime_employees_id,role,employee_code,emp_name,termination_status,date_of_joining,"' . $search_month . '",payroll '.$add_column.' FROM cw_employees WHERE cw_employees.trans_status = 1 and cw_employees.role = "' . $role . '" and DATE_FORMAT(cw_employees.date_of_joining, "%Y-%m-%d") <= "' . $sal_end_date . '" and (cw_employees.termination_status = 0 or cw_employees.last_working_date >= "'.$sal_start_date.'")';
$save_monthly_input = $this->db->query("CALL sp_a_run ('INSERT','$save_monthly_query')");
$save_monthly_input_result = $save_monthly_input->result();
$save_monthly_input->next_result();
// Zero Entry in monthly input then insert data END
//Check Previous month tabe data exist Start
$previous_column_qry = 'SELECT GROUP_CONCAT(CONCAT("cw_monthly_input.",previous_column, "=cw_transactions.", matching_column)) AS update_columns FROM cw_monthly_input_previous where cw_monthly_input_previous.trans_status=1';
$previous_column_data = $this->db->query("CALL sp_a_run ('SELECT','$previous_column_qry')");
$previous_column_result = $previous_column_data->result();
$previous_column_data->next_result();
$previous_column_count = (int) $previous_column_data->num_rows();
if ($previous_column_count > 0){
$update_columns = $previous_column_result[0]->update_columns;
$datestring = "01-$search_month first day of last month";
$dt = date_create($datestring);
$last_month = $dt->format('m-Y');
//Check previous month data exist
$trans_exist_qry = 'select employee_code from cw_transactions where trans_status = 1 and cw_transactions.role = "' . $role . '" and transactions_month = "' . $last_month . '" and termination_status = 0 limit 0,1';
$trans_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$trans_exist_qry')");
$trans_exist_rslt = $trans_exist_data->result();
$trans_exist_data->next_result();
$trans_exist_count = (int) $trans_exist_data->num_rows();
if ($trans_exist_count > 0){
//GET Previous transaction data of matching column
if ($update_columns){
$get_previous_val_qry = 'UPDATE cw_monthly_input
INNER JOIN cw_transactions ON cw_monthly_input.employee_code = cw_transactions.employee_code
SET ' . $update_columns . '
WHERE cw_transactions.trans_status=1 and cw_transactions.role = "' . $role . '" and cw_transactions.transactions_month = "' . $last_month . '"';
$update_previous_val_qry = $this->db->query("CALL sp_a_run ('UPDATE','$get_previous_val_qry')");
}
}
}
$ret_rslt = $this->update_supplymentry($role,$search_month);
if($ret_rslt){
//gratuity updates for fandf employees
$gratuity_update_qry = "select employees_id,employee_code,termination_status,role,process_month from cw_monthly_input where trans_status = 1 and process_month = '$search_month'";
$gratuity_update_data = $this->db->query('CALL sp_a_run ("SELECT","' . $gratuity_update_qry . '")');
$gratuity_update_result = $gratuity_update_data->result();
$gratuity_update_data->next_result();
foreach($gratuity_update_result as $gratuity_update){
if((int)$gratuity_update->termination_status === 1){
$gratuity_rslt = $this->Process_payroll_model->get_gratuity($gratuity_update->employee_code, $gratuity_update->role);
if ($gratuity_rslt) {
$upd_query = 'UPDATE cw_monthly_input SET gratuity =' . $gratuity_rslt . ' WHERE process_month ="' . $search_month . '" and trans_status =1 and employee_code = "' . $gratuity_update->employee_code . '"';
$this->db->query("CALL sp_a_run ('RUN','$upd_query')");
}
}
}
$count_query = str_replace("@SELECT@","count(*) as allcount",$this->base_query);
$count_query .= " where $this->prime_table.trans_status = 1 and $this->prime_table.process_month=\"".$search_month."\" and cw_monthly_input.role = \"".$role."\" ".$common_search." $add_query $status_qry order by $this->prime_table.date_of_joining DESC";
$search_count = $this->db->query($count_query);
$search_info = $search_count->result();
$filtered_count = $search_info[0]->allcount;
$search_query .= " inner join cw_employees on cw_employees.employee_code = $this->prime_table.employee_code where $this->prime_table.trans_status = 1 and $this->prime_table.process_month=\"".$search_month."\" and cw_monthly_input.role = \"".$role."\" ".$common_search." $add_query $status_qry order by $this->prime_table.date_of_joining DESC";
if((int)$per_page !== -1){
$search_query .= " LIMIT $start,$per_page";
}
$search_data = $this->db->query($search_query);
$search_result = $search_data->result();
$num_rows = $search_data->num_rows();
}
}else{
//Insert and Update missing employees or new joiners
$missed_emp_qry = 'select GROUP_CONCAT(employee_code) as employee_codes from cw_employees where cw_employees.trans_status = 1 and DATE_FORMAT(cw_employees.date_of_joining, "%Y-%m-%d") <= "' . $sal_end_date . '" and (cw_employees.termination_status = 0 or DATE_FORMAT(cw_employees.last_working_date, "%Y-%m-%d") >= "' . $sal_start_date . '") and role = "' . $role . '" and employee_code not in(SELECT employee_code from cw_monthly_input WHERE cw_monthly_input.trans_status = 1 and cw_monthly_input.process_month = "' . $search_month . '" and role = "' . $role . '" )';
//echo $missed_emp_qry; die;
$missed_emp_data = $this->db->query("CALL sp_a_run ('SELECT','$missed_emp_qry')");
$missed_emp_rslt = $missed_emp_data->result();
$missed_emp_data->next_result();
$employee_codes = str_replace(',', '","', $missed_emp_rslt[0]->employee_codes);
if($missed_emp_rslt){
$save_monthly_query = 'INSERT INTO cw_monthly_input(employees_id,role,employee_code,emp_name,termination_status,date_of_joining,process_month,payroll '.$add_column.') SELECT prime_employees_id,role,employee_code,emp_name,termination_status,date_of_joining,"' . $search_month . '",payroll '.$add_column.' FROM cw_employees WHERE cw_employees.trans_status = 1 and cw_employees.role = "' . $role . '" and DATE_FORMAT(cw_employees.date_of_joining, "%Y-%m-%d") <= "' . $sal_end_date . '" and (cw_employees.termination_status = 0 or cw_employees.last_working_date >= "'.$sal_start_date.'") and employee_code in ("'.$employee_codes.'") ';
$save_monthly_input = $this->db->query("CALL sp_a_run ('INSERT','$save_monthly_query')");
$save_monthly_input_result = $save_monthly_input->result();
$save_monthly_input->next_result();
//Check Previous month tabe data exist Start
$previous_column_qry = 'SELECT GROUP_CONCAT(CONCAT("cw_monthly_input.",previous_column, "=cw_transactions.", matching_column)) AS update_columns FROM cw_monthly_input_previous where cw_monthly_input_previous.trans_status=1';
$previous_column_data = $this->db->query("CALL sp_a_run ('SELECT','$previous_column_qry')");
$previous_column_result = $previous_column_data->result();
$previous_column_data->next_result();
$previous_column_count = (int) $previous_column_data->num_rows();
if ($previous_column_count > 0){
$update_columns = $previous_column_result[0]->update_columns;
$datestring = "01-$search_month first day of last month";
$dt = date_create($datestring);
$last_month = $dt->format('m-Y');
//Check previous month data exist
$trans_exist_qry = 'select employee_code from cw_transactions where trans_status = 1 and cw_transactions.role = "' . $role . '" and transactions_month = "' . $last_month . '" and employee_code in ("'.$employee_codes.'") and termination_status = 0 limit 0,1';
$trans_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$trans_exist_qry')");
$trans_exist_rslt = $trans_exist_data->result();
$trans_exist_data->next_result();
$trans_exist_count = (int) $trans_exist_data->num_rows();
if ($trans_exist_count > 0) {
//GET Previous transaction data of matching column
if ($update_columns){
$get_previous_val_qry = 'UPDATE cw_monthly_input
INNER JOIN cw_transactions ON cw_monthly_input.employee_code = cw_transactions.employee_code
SET ' . $update_columns . '
WHERE cw_transactions.trans_status=1 and cw_transactions.role = "' . $role . '" and cw_transactions.transactions_month = "' . $last_month . '" and cw_transactions.employee_code in ("'.$employee_codes.'")';
$update_previous_val_qry = $this->db->query("CALL sp_a_run ('UPDATE','$get_previous_val_qry')");
}
}
$ret_rslt = $this->update_supplymentry($role,$search_month);
}
}
$count_query = str_replace("@SELECT@","count(*) as allcount",$this->base_query);
$count_query .= " where $this->prime_table.trans_status = 1 and $this->prime_table.process_month=\"".$search_month."\" and cw_monthly_input.role = \"".$role."\" ".$common_search." $add_query $status_qry order by $this->prime_table.date_of_joining DESC";
$search_count = $this->db->query($count_query);
$search_info = $search_count->result();
$filtered_count = $search_info[0]->allcount;
$search_query .= " inner join cw_employees on cw_employees.employee_code = $this->prime_table.employee_code where $this->prime_table.trans_status = 1 and $this->prime_table.process_month=\"".$search_month."\" and cw_monthly_input.role = \"".$role."\" ".$common_search." $add_query $status_qry order by $this->prime_table.date_of_joining DESC";
if((int)$per_page !== -1){
$search_query .= " LIMIT $start,$per_page";
}
$search_data = $this->db->query($search_query);
$search_result = $search_data->result();
$num_rows = $search_data->num_rows();
}
if((int)$process_mode === 2 && (int)$num_rows > 0){
if((int)$access_data['monthly_input']['access_delete'] === 1){
$btn_array = array();
foreach($this->table_head_val as $table){
$label_name = $table->label_name;
$view_name = $table->view_name;
$field_type = (int)$table->field_type;
if($field_type === 2){
$btn_array[$label_name] = "<a class='btn btn-xs btn-danger' id='delete_btn_".$label_name."' onclick=delete_column('".$label_name."','".$tab_name."')>delete</a>";
}else{
$btn_array[$label_name] = "";
}
}
array_unshift($search_result, $btn_array);
}
}
}
}else
if($tab_name === 'input'){
$count_query = str_replace("@SELECT@","count(*) as allcount",$this->base_query);
$count_query .= " where $this->prime_table.trans_status = 1 and $this->prime_table.process_month=\"".$search_month."\" $add_query and cw_monthly_input.role = \"".$role."\" ".$common_search." and $this->prime_table.input_status = 1 and payroll_status = 0 order by $this->prime_table.date_of_joining DESC";
$search_count = $this->db->query($count_query);
$search_info = $search_count->result();
$filtered_count = $search_info[0]->allcount;
$search_query .= " inner join cw_employees on cw_employees.employee_code = $this->prime_table.employee_code where $this->prime_table.trans_status = 1 and $this->prime_table.process_month=\"".$search_month."\" and cw_monthly_input.role = \"".$role."\" ".$common_search." $add_query and $this->prime_table.input_status = 1 and payroll_status = 0 order by $this->prime_table.date_of_joining DESC";
if((int)$per_page !== -1){
$search_query .= " LIMIT $start,$per_page";
}
$search_data = $this->db->query($search_query);
$search_result = $search_data->result();
$num_rows = $search_data->num_rows();
if((int)$process_mode === 2 && (int)$num_rows > 0){
if((int)$access_data['monthly_input']['access_delete'] === 1){
$btn_array = array();
foreach($this->table_head_val as $table){
$label_name = $table->label_name;
$view_name = $table->view_name;
$field_type = (int)$table->field_type;
if($field_type === 2){
$btn_array[$label_name] = "<a class='btn btn-xs btn-danger' id='delete_btn_".$label_name."' onclick=delete_column('".$label_name."','".$tab_name."')>delete</a>";
}else{
$btn_array[$label_name] = "";
}
}
array_unshift($search_result, $btn_array);
}
}
}else{
$count_query = str_replace("@SELECT@","count(*) as allcount",$this->base_query);
$count_query .= " where $this->prime_table.trans_status = 1 and $this->prime_table.process_month=\"".$search_month."\" and cw_monthly_input.role = \"".$role."\" $add_query ".$common_search." and payroll_status = 1 order by $this->prime_table.date_of_joining DESC";
$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 and $this->prime_table.process_month=\"".$search_month."\" and cw_monthly_input.role = \"".$role."\" $add_query ".$common_search." and payroll_status = 1 order by $this->prime_table.date_of_joining DESC";
if((int)$per_page !== -1){
$search_query .= " LIMIT $start,$per_page";
}
$search_data = $this->db->query($search_query);
$search_result = $search_data->result();
$num_rows = $search_data->num_rows();
}
echo json_encode(array("draw" => intval($draw),"recordsTotal" => $num_rows,"recordsFiltered" => $filtered_count,"data" => $search_result));
}
public function save_processed_data(){
$category = $this->input->post('category');
$process_month = $this->input->post('process_month');
$add_component = $this->input->post('add_component');
$type = $this->input->post('type');
if($type === 'all'){
$input_status = ' ';
}else{
$input_status = ' and input_status = 2';
}
$mi_query = "";
$payroll_query = "";
if($add_component && $add_component !== 'undefined' && $add_component !== 'ALL'){
$mi_query = ' and '.$this->prime_table.'.'.$this->company_info[0]->mi_process_type.' = "' . $add_component . '"';
$payroll_query = ' and cw_transactions.'.$this->company_info[0]->mi_process_type.' = "' . $add_component . '"';
}
//Check Payroll Exist
$payroll_exist_qry = 'select count(*) as exist_count from cw_transactions where cw_transactions.trans_status =1 and cw_transactions.termination_status = 0 and role = "'.$category.'" and transactions_month = "'.$process_month.'"';
$payroll_exist_info = $this->db->query("CALL sp_a_run ('SELECT','$payroll_exist_qry')");
$payroll_exist_rslt = $payroll_exist_info->result();
$payroll_exist_info->next_result();
$exist_count = $payroll_exist_rslt[0]->exist_count;
if((int)$exist_count > 0){
echo json_encode(array("success" => FALSE,'message' => 'Payroll Already Exist This Month'));
exit(0);
}
$created_on = date("Y-m-d h:i:s");
$upd_query .= 'trans_updated_by = "' . $this->logged_id . '",trans_updated_date = "' . $created_on . '"';
$upd_query = 'UPDATE ' . $this->prime_table . ' SET input_status = 1 WHERE role = "' . $category . '" and process_month = "'.$process_month.'" '.$input_status.' '.$mi_query;
$this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
echo json_encode(array(
'success' => true,
'message' => "Updated successfully!!!"
));
}
public function check_monthly_input(){
$role = $this->input->post('category');
$search_month = $this->input->post('search_month');
$process_mode = $this->input->post('process_mode');
$add_component = $this->input->post('add_component');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
$add_query = "";
if($add_component && $add_component !== 'undefined' && $add_component !== 'ALL'){
$add_query = ' and '.$this->company_info[0]->mi_process_type.' = "' . $add_component . '"';
}
//Chech Employees there in the catgory.
$check_emp_count_qry = 'select count(employee_code) as emp_count from cw_employees where trans_status = 1 and role="' . $role . '" '.$add_query;
$check_emp_count_data = $this->db->query("CALL sp_a_run ('SELECT','$check_emp_count_qry')");
$check_emp_count_result = $check_emp_count_data->result();
$check_emp_count_data->next_result();
$emp_count = $check_emp_count_result[0]->emp_count;
if ((int)$emp_count === 0){
echo json_encode(array('success' => false,'status' => "category",'message' => "Employees Not present this category"));
exit(0);
}
$formula_qry = 'SELECT count(*) as formula_count FROM cw_payroll_formula where formula_for = "' . $role . '" and trans_status = 1 and fandf_only = 0 order by prime_payroll_id limit 0,1';
$formula_data = $this->db->query("CALL sp_a_run ('SELECT','$formula_qry')");
$formula_result = $formula_data->result();
$formula_data->next_result();
$formula_count = $formula_result[0]->formula_count;
if((int)$formula_count === 0){
echo json_encode(array('success' => false,'status' => "category",'message' => "Payroll Formula not Created for this category"));
exit(0);
}
$check_eligibilty_qry = 'select working_days,number_of_years,pay_days,year_rounding,formula_detail,formula_rounding from cw_gratuity where trans_status = 1 and category="' . $role . '"';
$check_eligibilty_data = $this->db->query("CALL sp_a_run ('SELECT','$check_eligibilty_qry')");
$check_eligibilty_result = $check_eligibilty_data->result();
$check_eligibilty_data->next_result();
$gratuity_formula = $check_eligibilty_result[0]->formula_detail;
if (!$gratuity_formula) {
echo json_encode(array('success' => false,'status' => "gratuity",'message' => "Gratuity setting is not done!!!"));
exit(0);
}
//Check Payroll Count
$check_payroll_exit_qry = 'select count(employee_code) as emp_count from cw_transactions where termination_status = 0 and trans_status = 1 and transactions_month="' . $search_month . '" and role="' . $role . '" '.$add_query;
$check_payroll_data = $this->db->query("CALL sp_a_run ('SELECT','$check_payroll_exit_qry')");
$check_payroll_result = $check_payroll_data->result();
$check_payroll_data->next_result();
$payroll_count = $check_payroll_result[0]->emp_count;
//Check Lock Month
$exist_qry = 'select lock_month from cw_monthly_input_lock where lock_month = "' . $search_month . '" and status = 1 and trans_status = 1';
$exist_data = $this->db->query("CALL sp_a_run ('SELECT','$exist_qry')");
$exist_result = $exist_data->result();
$num_rows = $exist_data->num_rows();
$exist_data->next_result();
if ((int) $num_rows > 0) {
echo json_encode(array('success' => false,'status' => "locked",'message' => "Monthly Input Locked For this Month!!!"
));
exit(0);
}else{
if($search_month){
$date_search = explode("-", $search_month);
$search_date = $date_search[1] . "-" . $date_search[0] . "-01";
//Get Month Days
$month_day_qry = 'select category,day_conditions,day_count,day_start,day_end from cw_month_day where cw_month_day.trans_status = 1 and category ="' . $role . '"';
$month_day_data = $this->db->query("CALL sp_a_run ('SELECT','$month_day_qry')");
$month_day_result = $month_day_data->result();
$month_day_data->next_result();
if (!$month_day_result) {
echo json_encode(array(
'success' => false,
'status' => "Monthday",
'message' => "Month days Not Set"
));
exit(0);
}
//Check monthy input data exist Start
$month_input_exist_qry = 'select employee_code from cw_monthly_input where trans_status = 1 and cw_monthly_input.role = "' . $role . '" and process_month = "' . $search_month . '" and cw_monthly_input.termination_status = 0 and cw_monthly_input.payroll = 1 '.$add_query.' limit 0,1 ';
$month_input_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$month_input_exist_qry')");
$month_input_exist_rslt = $month_input_exist_data->result();
$month_input_exist_data->next_result();
$month_input_exist_count = (int) $month_input_exist_data->num_rows();
if($month_input_exist_count === 0){
echo json_encode(array('success' => true,'message' => "Monthly Input Successfully Saved..."));
}else{
echo json_encode(array('success' => true,'message' => "Monthly Input Already Exist for this Month..."));
}
}
}
}
//DETELE COLUMN
public function delete_column(){
$category = $this->input->post('category');
$search_month = $this->input->post('search_month');
$label_name = $this->input->post('label_name');
$created_on = date("Y-m-d H:i:s");
$prime_upd_query .= '';
$upd_query = 'UPDATE cw_monthly_input SET ' . $label_name . ' = 0,trans_updated_by = "' . $this->logged_id . '",trans_updated_date = "' . $created_on . '" WHERE cw_monthly_input.role = "' . $category . '" and process_month = "' . $search_month . '"';
if ($this->db->query("CALL sp_a_run ('RUN','$upd_query')")) {
echo json_encode(array(
'success' => true,
'message' => "Column Deleted successfully"
));
} else {
echo json_encode(array(
'success' => false,
'message' => "Unable to Delete, Please contact admin..!"
));
}
}
public function update_table(){
$label_name = $this->input->post('label_name');
$value = $this->input->post('value');
$prime_id = $this->input->post('prime_id');
$hid_doj = date("Y-m-d", strtotime($this->input->post('hid_doj')));
$hid_process_month = date("Y-m-d", strtotime("01-" . $this->input->post('hid_process_month')));
$category = $this->input->post('category');
$process_month = $this->input->post('hid_process_month');
$employee_code = $this->input->post('hid_emp_code');
$tab_name = $this->input->post('tab_name');
$payroll_exist_qry = 'select count(*) as exist_count from cw_transactions where cw_transactions.trans_status =1 and cw_transactions.termination_status = 0 and role = "'.$category.'" and transactions_month = "'.$process_month.'" and employee_code = "'.$employee_code.'"';
$payroll_exist_info = $this->db->query("CALL sp_a_run ('SELECT','$payroll_exist_qry')");
$payroll_exist_rslt = $payroll_exist_info->result();
$payroll_exist_info->next_result();
$exist_count = $payroll_exist_rslt[0]->exist_count;
if((int)$exist_count > 0){
echo json_encode(array("success" => FALSE,'message' => 'Payroll Already Exist This Month'));
exit(0);
}
if($label_name === "supplementary_status" && (int) $value === 1){
$month_day_qry = 'select category,day_conditions,day_count,day_start,day_end from cw_month_day where cw_month_day.trans_status = 1 and category ="' . $category . '"';
$month_day_data = $this->db->query("CALL sp_a_run ('SELECT','$month_day_qry')");
$month_day_result = $month_day_data->result();
$month_day_data->next_result();
//Get Salary Start Date
if ($month_day_result){
$role = $month_day_result[0]->category;
$day_conditions = $month_day_result[0]->day_conditions;
$day_count = $month_day_result[0]->day_count;
$day_start = $month_day_result[0]->day_start;
$day_end = $month_day_result[0]->day_end;
if((int)$day_conditions === 3) {
//As Discussed with jaffer on 23rdJuly2019
$prev_month = date("Y-m-" . $day_start, strtotime("-1 month", strtotime($hid_process_month)));
$end_month = date("Y-m-" . $day_end, strtotime($hid_process_month));
}else{
$sal_start = '01';
if((int)$day_conditions === 2){
$day_end = date("t");
}
$prev_month = date("Y-m-" . $sal_start, strtotime($hid_process_month));
$end_month = date("Y-m-" . $day_end, strtotime($hid_process_month));
}
}
$end_date = strtotime($end_month);
$prev_date = strtotime($prev_month);
$mydate = strtotime($hid_doj);
if ($mydate < $prev_date || $mydate > $end_date) {
echo json_encode(array(
'success' => false,
'status' => "DOJ",
'message' => "This Employee not Eligible for Supplementary Process!!!"
));
exit(0);
}
}
if($tab_name === 'edit'){
$input_status = ',input_status = 2';
}else{
$input_status = ',input_status = 1';
}
$created_on = date("Y-m-d h:i:s");
$upd_query .= 'trans_updated_by = "' . $this->logged_id . '",trans_updated_date = "' . $created_on . '"';
$upd_query = 'UPDATE ' . $this->prime_table . ' SET ' . $label_name . ' = "' . $value . '" '.$input_status.' WHERE ' . $this->prime_id . ' = "' . $prime_id . '"';
$this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
echo json_encode(array(
'success' => true,
'message' => "Updated successfully!!!"
));
}
//IMPORT FILE VIEW INFORMATION
public function import(){
$data['module_id'] = $this->control_name;
$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;
$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('monthly_input/import', $data);
}
/* ==============================================================*/
/* ============ MONHTLY IMPORT OPERATION - START ================*/
/* ==============================================================*/
//SAVE MONTHLY IMPORT FILE PATH
public function save_import(){
$module_id = $this->input->post('module_id');
$excel_format = $this->input->post('excel_format');
$excel_file_path = $this->input->post('excel_file_path');
$excel_sheet_name = $this->input->post('excel_sheet_name');
$excel_start_row = $this->input->post('excel_start_row');
$excel_end_row = $this->input->post('excel_end_row');
$process_month = $this->input->post('process_month');
$category = $this->input->post('category_id');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
//Check Validations
$formula_qry = 'SELECT count(*) as formula_count FROM cw_payroll_formula where formula_for = "' . $category . '" and trans_status = 1 and fandf_only = 0 order by prime_payroll_id limit 0,1';
$formula_data = $this->db->query("CALL sp_a_run ('SELECT','$formula_qry')");
$formula_result = $formula_data->result();
$formula_data->next_result();
$formula_count = $formula_result[0]->formula_count;
if((int)$formula_count === 0){
echo json_encode(array('success' => false,'status' => "category",'message' => "Payroll Formula not Created for this category"));
exit(0);
}
$import_query = 'insert into cw_month_import (module_id,excel_format,excel_file_path,excel_sheet_name,excel_start_row,excel_end_row,category,process_month,trans_created_by,trans_created_date) value ("' . $module_id . '","' . $excel_format . '","' . $excel_file_path . '","' . $excel_sheet_name . '","' . $excel_start_row . '","' . $excel_end_row . '","' . $category . '","' . $process_month . '","' . $logged_id . '","' . $today_date . '")';
$import_info = $this->db->query("CALL sp_a_run ('INSERT','$import_query')");
$import_result = $import_info->result();
$import_info->next_result();
$import_id = $import_result[0]->ins_id;
echo $this->do_monthly_excel_import($import_id);
}
//IMPORT DATA FROM FILE PATH
public function do_monthly_excel_import($import_id){
$filename = dirname(__FILE__) . "/php_excel/PHPExcel/IOFactory.php";
include($filename);
if ($import_id < 0) {
return json_encode(array(
'success' => false,
'message' => "Invalid file upload"
));
}
//EDIT START
$cat_qry = 'select * from cw_category where trans_status =1';
$cat_data = $this->db->query("CALL sp_a_run ('SELECT','$cat_qry')");
$cat_data_result = $cat_data->result();
$cat_data->next_result();
$cat_data = array_column($cat_data_result, 'category_name', 'prime_category_id');
$excel_path_qry = 'select * from cw_month_import where import_id = "' . $import_id . '"';
$excel_path_info = $this->db->query("CALL sp_a_run ('SELECT','$excel_path_qry')");
$excel_path_result = $excel_path_info->result();
$excel_path_info->next_result();
if (!$excel_path_result) {
return json_encode(array(
'success' => false,
'message' => "Invalid file upload"
));
} else {
$excel_file_path = $excel_path_result[0]->excel_file_path;
$module_id = $excel_path_result[0]->module_id;
$excel_format = $excel_path_result[0]->excel_format;
$excel_sheet_name = (int) $excel_path_result[0]->excel_sheet_name;
$excel_row_start = (int) $excel_path_result[0]->excel_start_row;
$excel_row_end = (int) $excel_path_result[0]->excel_end_row;
$process_month = $excel_path_result[0]->process_month;
$category_id = $excel_path_result[0]->category;
//Add Additional Column
$add_input = "";
$mi_process_type = "";
$add_value = "";
$upd_value = "";
if($this->company_info[0]->mi_process_type !== 'role' && $this->company_info[0]->mi_process_type !== '0'){
$mi_process_type = $this->company_info[0]->mi_process_type;
$add_input = ','.$this->company_info[0]->mi_process_type;
//$add_value = '".$add_value."';
}
//check payroll count
$payroll_exist_qry = 'select count(*) payroll_count from cw_transactions where trans_status = 1 and transactions_month ="' . $process_month . '" and role ="' . $category_id . '"';
$payroll_exist_info = $this->db->query("CALL sp_a_run ('RUN','$payroll_exist_qry')");
$payroll_exist_result = $payroll_exist_info->result();
$payroll_exist_info->next_result();
$payroll_count = $payroll_exist_result[0]->payroll_count;
if((int)$payroll_count > 0){
return json_encode(array(
'success' => false,
'message' => "Payroll Already Processed for this Month.."
));
exit(0);
}
//employee check in employee master
$emp_qry = 'select employee_code,date_of_joining,role,emp_name,prime_employees_id,resignation_date,termination_status '.$add_input.' from cw_employees where trans_status = 1 and role = "'.$category_id.'"';
$emp_data = $this->db->query("CALL sp_a_run ('SELECT','$emp_qry')");
$emp_data_result = $emp_data->result();
$emp_data->next_result();
$emp_code_result = array_map(function($v)
{
$return_array['employee_data'] = $v;
$return_array['employee_code'] = $v->employee_code;
return $return_array;
}, $emp_data_result);
$emp_code_data = array_column($emp_code_result, 'employee_data', 'employee_code');
//print_r($emp_code_data); die;
$lock_month_qry = 'select * from cw_monthly_input_lock where lock_month = "' . $process_month . '" and status = 1 and trans_status = 1';
$lock_month_data = $this->db->query("CALL sp_a_run ('SELECT','$lock_month_qry')");
$lock_month_result = $lock_month_data->result();
$lock_month_data->next_result();
$lock_num_rows = $lock_month_data->num_rows();
if ((int) $lock_num_rows > 0) {
return json_encode(array(
'success' => false,
'message' => "Monthly input is locked this month, please unlocked to upload the files"
));
exit(0);
}
$format_qry = 'select * from cw_util_excel_format where prime_excel_format_id = "' . $excel_format . '" and cw_util_excel_format.trans_status = 1';
$format_info = $this->db->query("CALL sp_a_run ('SELECT','$format_qry')");
$format_rslt = $format_info->result();
$format_info->next_result();
if (!$format_rslt) {
return json_encode(array(
'success' => false,
'message' => "Please add excel format before import"
));
}else{
$exist_column_name = explode(",", $format_rslt[0]->exist_column_name);
$excel_format_qry = 'select * from cw_util_excel_format_line inner join cw_form_setting on label_name = excel_line_column_name where excel_line_module_id = "' . $module_id . '" and prime_excel_format_id = "' . $excel_format . '" and cw_util_excel_format_line.trans_status = 1 group by excel_line_column_name';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_format_result = $excel_format->result();
$excel_format->next_result();
$excel_columns = array_column($excel_format_result, 'excel_line_column_name');
$lock_query = 'select GROUP_CONCAT(previous_column) as previous_column from cw_monthly_input_previous inner join cw_form_setting on cw_form_setting.label_name = previous_column where cw_monthly_input_previous.trans_status = 1 and column_status in (1,2)';
$lock_data = $this->db->query("CALL sp_a_run ('SELECT','$lock_query')");
$lock_result = $lock_data->result();
$lock_data->next_result();
$previous_columns = explode(",",$lock_result[0]->previous_column);
$result_arr = array_intersect($excel_columns,$previous_columns);
$error_string = implode(",", $result_arr);
if(!empty($result_arr)){
return json_encode(array(
'success' => false,
'message' => " Columns $error_string are not Allowed to import.. These are Carry Forward Inputs.."
));
exit(0);
}
if (!$excel_format_result) {
return json_encode(array(
'success' => false,
'message' => "Please map excel cell column before import"
));
} else {
try {
$excel_obj = \PhpOffice\PhpSpreadsheet\IOFactory::load($excel_file_path);
}
catch (Exception $e) {
die('Error loading file "' . pathinfo($excel_file_path, PATHINFO_BASENAME) . '": ' . $e->getMessage());
return json_encode(array(
'success' => false,
'message' => "Invalid file or path"
));
}
$sheet = $excel_obj->getSheet($excel_sheet_name);
if($excel_row_end){
$total_rows = $excel_row_end;
}else{
$total_rows = $sheet->getHighestRow();
}
$highest_column = $sheet->getHighestColumn();
$error_info = array();
for($row = $excel_row_start; $row <= $total_rows; $row++){
$exist_val = "";
$prime_column_val = "";
$prime_cell_val = "";
$status_info = array();
$status_info["Excel Row"] = $row;
$prime_upd_query = "";
foreach ($excel_format_result as $excel_info){
$column_name = $excel_info->excel_line_column_name;
$column_value = $excel_info->excel_line_value;
$field_type = (int) $excel_info->field_type;
//$get_cell_value = trim($sheet->getCell("$column_value$row")->getValue());
$get_cell_value = trim($sheet->getCell("$column_value$row")->getCalculatedValue());
if($column_name === "role"){
if(!in_array($get_cell_value, $cat_data)){
$error_info[$row] = "Category Not Exist in DB";
}
}else if ($column_name === "employee_code"){
$employee_code = $get_cell_value;
//Get Salary Start and End Date;
$emp_data = $emp_code_data[$get_cell_value];
$emp_doj = $emp_data->date_of_joining;
$category = $emp_data->role;
$resign_date = $emp_data->resignation_date;
$emp_status = (int)$emp_data->termination_status;
$salary_dates = $this->Process_payroll_model->get_salary_date($category);
$day_start = $salary_dates['day_start'];
$day_end = $salary_dates['day_end'];
$process_date = date("Y-m-d", strtotime($day_end . "-" . $process_month));
$sal_start_date = date("Y-m-d", strtotime($day_start . "-" . $process_month));
if (!array_key_exists($get_cell_value, $emp_code_data)) {
$error_info[$row] = "Employee Code Not Exist in DB";
}else
if(strtotime($emp_doj) > strtotime($process_date)) {
$error_info[$row] = "Date of Joining Greater than Process date";
}else
if((strtotime($resign_date) < strtotime($sal_start_date)) && $emp_status === 1){
$error_info[$row] = "Already Resigned Employee..";
}
}
}
}
$can_process_count = count($error_info);
if((int) $can_process_count > 0){
return json_encode(array('success' => true,'message' => "File Has Error",'error_info' => $error_info));
} else {
$status_array = array();
for ($row = $excel_row_start; $row <= $total_rows; $row++) {
$exist_val = "";
$prime_column_val = "";
$prime_cell_val = "";
$status_info = array();
$status_info["Excel Row"] = $row;
$prime_upd_query = "";
foreach ($excel_format_result as $excel_info) {
$column_name = $excel_info->excel_line_column_name;
$column_value = $excel_info->excel_line_value;
//$get_cell_value = trim($sheet->getCell("$column_value$row")->getValue());
$get_cell_value = trim($sheet->getCell("$column_value$row")->getCalculatedValue());
if ($column_name === "role"){
$cat_id = array_search("$get_cell_value", $cat_data);
$get_cell_value = $cat_id;
}
if ($column_name === "employee_code"){
$employee_code = $get_cell_value;
$emp_data = $emp_code_data[$get_cell_value];
$doj = $emp_data->date_of_joining;
$emp_id = $emp_data->prime_employees_id;
$cat = $emp_data->role;
$emp_name = $emp_data->emp_name;
$emp_cell_val = $emp_id;
if($mi_process_type){
$add_value = ",".$emp_data->$mi_process_type;
$upd_value = $add_input.' = "'.$emp_data->$mi_process_type.'"';
}
}
$prime_column_val .= $column_name . ",";
$prime_cell_val .= '"' . $get_cell_value . '",';
if (in_array($column_name, $exist_column_name)) {
$exist_val .= $column_name . ' = "' . $get_cell_value . '" and ';
}
$update_column_val = $column_name;
$update_cell_val = '"' . $get_cell_value . '",';
$prime_upd_query .= $update_column_val . "=" . $update_cell_val;
}
$exist_val = rtrim($exist_val, " and ");
$exist_query = 'select count(*) exist_count,trans_status,prime_monthly_input_id from cw_monthly_input where trans_status = 1 and ' . $exist_val . ' and process_month ="' . $process_month . '"';
$exist_info = $this->db->query("CALL sp_a_run ('RUN','$exist_query')");
$exist_result = $exist_info->result();
$exist_info->next_result();
$exist_count = $exist_result[0]->exist_count;
$created_on = date("Y-m-d h:i:s");
if ((int)$exist_count === 0){
$prime_column_val .= "employees_id,emp_name,role,date_of_joining,process_month,trans_created_by,trans_created_date,input_status $add_input";
$prime_cell_val .= '"' . $emp_cell_val . '","' . $emp_name . '","' . $category_id . '","' . $doj . '","' . $process_month . '","' . $this->logged_id . '",' . '"' . $created_on . '","1"'.$add_value;
$prime_column_val = rtrim($prime_column_val, ",");
$prime_cell_val = rtrim($prime_cell_val, ",");
$prime_query = "insert into cw_monthly_input ($prime_column_val) VALUES ($prime_cell_val)";
$insert_info = $this->db->query("CALL sp_a_run ('RUN','$prime_query')");
$status_info['Status'] = "Inserted to DB";
}else
if((int) $exist_count > 0) {
$trans_status = (int)$exist_result[0]->trans_status;
$upd_prime_id = (int)$exist_result[0]->prime_monthly_input_id;
$prime_upd_query .= 'trans_updated_by = "' . $this->logged_id . '",trans_updated_date = "' . $created_on . '"';
if($trans_status === 1){
$upd_query = 'UPDATE cw_monthly_input SET ' . $prime_upd_query . ',input_status = 1 '.$upd_value.' WHERE prime_monthly_input_id = "' . $upd_prime_id . '" and role = "'.$category_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_query')");
$status_info['status'] = "Updated to DB";
}else{
$status_info['status'] = "Already Exist in DB";
}
}
$status_array[] = $status_info;
}
}
}
$table_info = $this->get_excel_import_ui($status_array);
return json_encode(array(
'success' => true,
'message' => "Successfully file imported",
'table_info' => $table_info
));
}
}
}
//NEHA EDIT START 06APR2020
public function excel($module_id, $excel_format){
$excel_format_qry = 'select excel_name,view_name,excel_line_column_name,excel_line_value from cw_util_excel_format_line inner join cw_form_setting on cw_form_setting.label_name = excel_line_column_name inner join cw_util_excel_format on cw_util_excel_format.prime_excel_format_id = cw_util_excel_format_line.prime_excel_format_id where excel_line_module_id = "'.$module_id.'" and cw_util_excel_format_line.prime_excel_format_id ="'.$excel_format.'" and cw_util_excel_format_line.trans_status = 1 and cw_util_excel_format.trans_status = 1 GROUP BY cw_form_setting.label_name';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_result = $excel_format->result();
$excel_format->next_result();
$excel_name = str_replace(' ', '_', $excel_result[0]->excel_name);
// require_once APPPATH."/controllers/php_excel/PHPExcel.php";;
$obj = new Spreadsheet();
//Set the first row as the header row
foreach ($excel_result as $excel) {
$excel_line_column_name = $excel->view_name;
$excel_line_value = $excel->excel_line_value;
$obj->getActiveSheet()->setCellValue($excel_line_value . "1", $excel_line_column_name);
}
// Rename worksheet name
$filename= $excel_name.".xls"; //save our workbook as this file name
header('Content-Type: application/vnd.ms-excel'); //mime type
header('Content-Disposition: attachment;filename="' . $filename . '"'); //tell browser what's the file name
header('Cache-Control: max-age=0'); //no cache
$objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($obj, 'Excel5');
//force user to download the Excel file without writing it to server's HD
$objWriter->save('php://output');
echo json_encode(array(
'success' => TRUE,
'output' => $excelOutput
));
}
//Sheet Name display in import page
public function sheet_name()
{
$file_path = $this->input->post('file_path');
$filename = dirname(__FILE__) . "/php_excel/PHPExcel/IOFactory.php";
include($filename);
$excel_obj = \PhpOffice\PhpSpreadsheet\IOFactory::load($file_path);
$sheet_count = $excel_obj->getSheetCount();
$sheet_name = array();
for ($i = 0; $i < $sheet_count; $i++) {
$sheet = $excel_obj->getSheet($i);
$sheet_name[] = $sheet->getTitle();
}
echo json_encode(array(
'sheet_name' => $sheet_name
));
}
//CUSTOM DELETE IS UPDATED
public function delete_month_input(){
$month_ids = implode(",", $this->input->post('delete_ids'));
$search_month = $this->input->post('search_month');
$category = $this->input->post('category');
//Get Emp Counts
$emp_count_qry = 'select GROUP_CONCAT(employee_code separator ",") as emp_code_list from cw_monthly_input where trans_status = 1 and prime_monthly_input_id in (' . $month_ids . ')';
$emp_count_info = $this->db->query("CALL sp_a_run ('RUN','$emp_count_qry')");
$emp_count_result = $emp_count_info->result();
$emp_count_info->next_result();
$emp_code_list = $emp_count_result[0]->emp_code_list;
$emp_code_list = '"'.str_replace(',', '","', $emp_code_list).'"';
//check payroll is count
$payroll_exist_qry = 'select count(*) payroll_count from cw_transactions where trans_status = 1 and transactions_month ="' . $search_month . '" and role ="' . $category . '" and employee_code in (' . $emp_code_list . ')';
$payroll_exist_info = $this->db->query("CALL sp_a_run ('RUN','$payroll_exist_qry')");
$payroll_exist_result = $payroll_exist_info->result();
$payroll_exist_info->next_result();
$payroll_count = $payroll_exist_result[0]->payroll_count;
if ((int)$payroll_count === 0){
$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 input_status = 0,' . $prime_upd_query . ' WHERE ' . $this->prime_id . ' in (' . $month_ids . ')';
$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' => "Payroll Already Exist Please delete the payroll for this month $search_month"
));
}
}
//check supplymentry proces 17 APR 2020
public function check_supplymentry(){
$category = $this->input->post('category');
$search_month = $this->input->post('search_month');
$month_input_exist_qry = 'select employee_code from cw_monthly_input where trans_status = 1 and role = "' . $category . '" and process_month = "' . $search_month . '" limit 0,1';
$month_input_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$month_input_exist_qry')");
$month_input_exist_rslt = $month_input_exist_data->result();
$month_input_exist_data->next_result();
$month_input_exist_count = (int) $month_input_exist_data->num_rows();
if ($month_input_exist_count == 0) {
$supplymentry_ext_qry = 'select count(*) supply_count from cw_supplementary_detail where trans_status = 1 and supplementary_month ="' . $search_month . '" and category ="' . $category . '"';
$supplymentry_ext_info = $this->db->query("CALL sp_a_run ('RUN','$supplymentry_ext_qry')");
$supplymentry_ext_result = $supplymentry_ext_info->result();
$supplymentry_ext_info->next_result();
$supply_count = $supplymentry_ext_result[0]->supply_count;
if ((int) $supply_count === 0) {
echo json_encode(array(
'success' => TRUE,
'message' => "Supplementary Not Processed?"
));
} else {
echo json_encode(array(
'success' => FALSE,
'message' => "Ok Processed"
));
}
} else {
echo json_encode(array(
'success' => FALSE,
'message' => "Ok Processed"
));
}
}
public function process_month_check(){
$process_month = $this->input->post('process_month');
$category = $this->input->post('category');
$mi_exit_qry = 'select count(employee_code) as emp_count from cw_monthly_input where trans_status = 1 and process_month="'.$process_month.'" and termination_status = 0 and role="'.$category.'"';
$mi_data = $this->db->query("CALL sp_a_run ('SELECT','$mi_exit_qry')");
$mi_result = $mi_data->result();
$mi_data->next_result();
$mi_count = $mi_result[0]->emp_count;
if((int)$mi_count > 0){
$check_payroll_exit_qry = 'select count(employee_code) as emp_count from cw_transactions where trans_status = 1 and transactions_month="'.$process_month.'" and termination_status = 0 and role="'.$category.'"';
$check_payroll_data = $this->db->query("CALL sp_a_run ('SELECT','$check_payroll_exit_qry')");
$check_payroll_result = $check_payroll_data->result();
$check_payroll_data->next_result();
$payroll_count = $check_payroll_result[0]->emp_count;
if((int)$payroll_count === 0){
$mi_lock_qry = 'select lock_month from cw_monthly_input_lock where lock_month = "'.$process_month.'" and status = 1 and trans_status = 1';
$mi_lock_data = $this->db->query("CALL sp_a_run ('SELECT','$mi_lock_qry')");
$mi_lock_result = $mi_lock_data->result();
$mi_lock_count = $mi_lock_data->num_rows();
$mi_lock_data->next_result();
if((int)$mi_lock_count === 0){
echo json_encode(array('success' => TRUE, 'message' => "Ok Processed"));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Monthly Input is already locked?"));
}
}else{
echo json_encode(array('success' => FALSE, 'message' => "Payroll already exit?"));
}
}else{
echo json_encode(array('success' => FALSE, 'message' => "Please generate the monthly input first in monthly input screen?"));
}
}
public function update_supplymentry($role,$search_month){
$back_month_date = "01-".$search_month;
$back_month = date("m-Y", strtotime("-1 month", strtotime($back_month_date)));
$supplymentry_sts_qry = 'select count(*) as rslt_count from cw_monthly_input where process_month="'.$back_month.'" and supplementary_status = 1 and trans_status = 1';
$supplymentry_sts_data = $this->db->query("CALL sp_a_run ('SELECT','$supplymentry_sts_qry')");
$supplymentry_sts_result = $supplymentry_sts_data->result();
$supplymentry_sts_data->next_result();
$supply_mi_count = $supplymentry_sts_result[0]->rslt_count;
$supplymentry_exit_qry = 'select count(*) as supply_count from cw_supplementary_detail where supplementary_month = "'.$search_month.'" and category = "'.$role.'" and trans_status = 1';
$supplymentry_data = $this->db->query("CALL sp_a_run ('SELECT','$supplymentry_exit_qry')");
$supplymentry_result = $supplymentry_data->result();
$supplymentry_data->next_result();
$supply_exit_count = $supplymentry_result[0]->supply_count;
if((int)$supply_mi_count > 0){
if((int)$supply_exit_count > 0){
$get_supplymentry_qry = 'select IFNULL(sum(supp_month_days),0) as supp_month_days,IFNULL(sum(supp_paid_days),0) as supp_paid_days,emp_code from cw_supplementary_detail inner join cw_monthly_input on cw_monthly_input.employee_code =cw_supplementary_detail.emp_code where cw_monthly_input.trans_status=1 and cw_supplementary_detail.supplementary_month="'.$search_month.'" and category = "'.$role.'" and supplementary_status = 1 group by emp_code';
$get_supplymentry_data = $this->db->query("CALL sp_a_run ('SELECT','$get_supplymentry_qry')");
$get_supplymentry_result = $get_supplymentry_data->result();
$get_supplymentry_data->next_result();
$update_on = date("Y-m-d H:i:s");
if(!empty($get_supplymentry_result)){
foreach($get_supplymentry_result as $sup_rslt){
$emp_code = $sup_rslt->emp_code;
$supp_paid_days = $sup_rslt->supp_paid_days;
$supp_month_days = $sup_rslt->supp_month_days;
$s_pd_column = $this->get_map_arr['supp_paid_days'];
$s_md_column = $this->get_map_arr['supp_month_days'];
$upd_mi_query = 'UPDATE cw_monthly_input SET '.$s_pd_column.' ="'.$supp_paid_days.'", '.$s_md_column.' ="'.$supp_month_days.'", trans_updated_by = "'.$this->logged_id.'",trans_updated_date = "'.$update_on.'" WHERE employee_code = "'.$emp_code.'" and process_month="'.$search_month.'" and trans_status=1';
$this->db->query("CALL sp_a_run ('UPDATE','$upd_mi_query')");
}
}
}
}
return true;
}
public function check_import_month(){
$process_month = $this->input->post("process_month");
$role = $this->input->post("category");
$payroll_exist_qry = 'select count(*) as exist_count from cw_transactions where cw_transactions.trans_status =1 and cw_transactions.termination_status = 0 and role = "'.$role.'" and transactions_month = "'.$process_month.'"';
$payroll_exist_info = $this->db->query("CALL sp_a_run ('SELECT','$payroll_exist_qry')");
$payroll_exist_rslt = $payroll_exist_info->result();
$payroll_exist_info->next_result();
$exist_count = $payroll_exist_rslt[0]->exist_count;
if((int)$exist_count === 0){
echo json_encode(array("success" => TRUE,'message' => 'Ok Proceed'));
}else{
echo json_encode(array("success" => FALSE,'message' => 'Payroll Already Exist This Month'));
}
}
}
?>