File: /home/cafsindia/hrms_patroniss_com/application/controllers/Monthly_input.php
<?php
/**********************************************************
Filename: Monthly Input
Description: Monthly Input for adding input.
Author: Jaffer Sathik
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');
}
$this->collect_base_info();
$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 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_role.'",field_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 = $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
$data['table_head'] = $this->table_head;
$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;
$this->load->view('monthly_input/manage', $data);
}
// PROVIDE LABEL NAME FOR TABLE HELFPER HEADING
/* public function get_table_info()
{
$table_query = 'SELECT `COLUMN_NAME` as label_name FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`="' . $this->config->item("db_name") . '" AND `TABLE_NAME` IN ("cw_monthly_input") AND COLUMN_NAME NOT LIKE ("%trans%") AND COLUMN_NAME NOT IN ("prime_monthly_input_id", "employees_id", "role")';
$table_info = $this->db->query("CALL sp_a_run ('SELECT','$table_query')");
$result = $table_info->result();
$table_info->next_result();
$table_array = array();
foreach ($result as $table_column) {
$label_name = $table_column->label_name;
$filed_show_qry = 'select * from cw_form_setting where prime_module_id = "employees" and label_name = "' . $label_name . '" and trans_status =1';
$filed_show_data = $this->db->query("CALL sp_a_run ('SELECT','$filed_show_qry')");
$filed_show_result = $filed_show_data->result();
$filed_show_data->next_result();
if (empty($filed_show_result)) {
$filed_show = 1;
} else {
$filed_show = $filed_show_result[0]->field_show;
}
$table_array[] = array(
'label_name' => $label_name,
'filed_show' => $filed_show
);
}
$result = json_decode(json_encode($table_array));
$this->table_info = $result;
}*/
// PROVIDE MODLE FORM INPUT VIEWS
/* public function get_form_info()
{
$from_query = 'select * from cw_form_setting where prime_module_id = "employees" and field_show = "1" and (earn_month_check = "1" OR deduction_month_check = "1")';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$from_query')");
$form_result = $form_data->result();
$form_data->next_result();
$this->form_info = $form_result;
}*/
//LOAD PAGE TABLE VIEW WITH DATA BASED ON SEARCH FILTERS
public function search(){
$draw = $this->input->post('draw');
$start = $this->input->post('start');
$per_page = $this->input->post('length');
$order = $this->input->post('order');
$order_col = $this->input->post('columns');
$search = $this->input->post('search');
$column = $order[0]['column'];
$order_sor = $order[0]['dir'];
$order_col = $order_col[$column]['data'];
$search = trim($search['value']);
$search_query = str_replace("@SELECT@",$this->mi_select_query,$this->base_query);
$access_data = $this->session->userdata('access_data');
$role = $this->input->post('category');
$search_month = $this->input->post('search_month');
$process_mode = $this->input->post('process_mode');
$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);
}
$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 ((int) $process_mode === 1) {
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) {
$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) {
$prev_month = date("Y-m-" . $day_start, strtotime("-1 month", strtotime($search_date)));
$end_month = date("Y-m-" . $day_end, strtotime($search_date));
} else {
$sal_start = '01';
if ((int) $day_conditions === 2) {
$day_end = date("t");
}
$prev_month = date("Y-m-" . $sal_start, strtotime($search_date));
$end_month = date("Y-m-" . $day_end, strtotime($search_date));
}
} else {
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 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 leave count
$salary_days = $this->Process_payroll_model->get_salary_date($role);
$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));
}
// 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) SELECT prime_employees_id,role,employee_code,emp_name,termination_status,date_of_joining,"' . $search_month . '",payroll 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") <= "' . $end_month . '" and cw_employees.termination_status = 0';
$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 cw_transactions.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.termination_status = 0 and $this->prime_table.trans_status = 1 and $this->prime_table.process_month=\"".$search_month."\" and cw_monthly_input.role = \"".$role."\" ".$common_search." 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.termination_status = 0 and $this->prime_table.trans_status = 1 and $this->prime_table.process_month=\"".$search_month."\" and cw_monthly_input.role = \"".$role."\" ".$common_search." 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{
$count_query = str_replace("@SELECT@","count(*) as allcount",$this->base_query);
$count_query .= " where $this->prime_table.termination_status = 0 and $this->prime_table.trans_status = 1 and $this->prime_table.process_month=\"".$search_month."\" and cw_monthly_input.role = \"".$role."\" ".$common_search." 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.termination_status = 0 and $this->prime_table.trans_status = 1 and $this->prime_table.process_month=\"".$search_month."\" and cw_monthly_input.role = \"".$role."\" ".$common_search." 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)$access_data['monthly_input']['access_delete'] === 1){
$btn_array = array();
foreach($this->table_head as $table){
$label_name = $table->label_name;
$view_name = $table->view_name;
$field_type = (int)$table->field_type;
if($label_name === "employee_code" || $label_name === "emp_name" || $label_name === "process_month" || $label_name === "date_of_joining"){
$btn_array[$label_name] = "";
}else{
$btn_array[$label_name] = "<a class='btn btn-xs btn-danger' id='delete_btn_".$label_name."' onclick=delete_column('".$label_name."')>delete</a>";
}
}
array_unshift($search_result, $btn_array);
}
echo json_encode(array("draw" => intval($draw),"recordsTotal" => $num_rows,"recordsFiltered" => $filtered_count,"data" => $search_result));
}
}else
if ((int) $process_mode === 2) {
echo json_encode(array("draw" => intval($draw),"recordsTotal" => 0,"recordsFiltered" => 0,"data" => 0));
}
}
public function check_monthly_input(){
$role = $this->input->post('category');
$search_month = $this->input->post('search_month');
$process_mode = $this->input->post('process_mode');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
//echo "BSK $role :: $search_month :: $process_mode"; die;
//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 . '"';
$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);
}
$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 . '"';
$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 ((int) $process_mode === 1) {
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) {
$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) {
$prev_month = date("Y-m-" . $day_start, strtotime("-1 month", strtotime($search_date)));
$end_month = date("Y-m-" . $day_end, strtotime($search_date));
} else {
$sal_start = '01';
if ((int) $day_conditions === 2) {
$day_end = date("t");
}
$prev_month = date("Y-m-" . $sal_start, strtotime($search_date));
$end_month = date("Y-m-" . $day_end, strtotime($search_date));
}
} else {
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 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..."));
}
}else
if ((int) $process_mode === 2) {
//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 ="' . $role . '"';
$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) {
$logged_id = $this->session->userdata('logged_id');
$where_query = 'and role = "' . $role . '"';
$monthly_input_delete_query = 'UPDATE cw_monthly_input SET trans_status = 0 , trans_deleted_by = "' . $logged_id . '",trans_deleted_date = DATE_FORMAT(NOW(), "%Y-%m-%d %H:%i:%S") WHERE process_month = "' . $search_month . '"' . $where_query;
$this->db->query("CALL sp_a_run ('RUN','$monthly_input_delete_query')");
//$table_view = $this->transaction_data($search_month,$role);
echo json_encode(array('success' => TRUE,'message' =>"Monthly Input Deleted Successfully!!!"));
}else{
echo json_encode(array('success' => false,'status' => "locked",'message' => "Payroll Already Exist!!!"));
}
}
}
}
//VIEW FUNCTION FOR PAYROLL
public function transaction_data($process_month,$process_role){
$select_query = "";
$pick_query = "";
$final_qry = "";
$thead_line = "";
$thead = "";
$form_qry = 'select * from cw_form_setting where prime_module_id = "employees" and (earn_month_check = "1" OR deduction_month_check = "1") and FIND_IN_SET("'.$this->logged_role.'",field_for) ORDER BY monthly_input_sort asc';
$form_data = $this->db->query("CALL sp_a_run ('SELECT','$form_qry')");
$form_result = $form_data->result();
$form_data->next_result();
$query_key = "";
$table_name = "cw_monthly_input";
$i = 1;
foreach($form_result as $form){
$prime_form_id = (int)$form->prime_form_id;
$view_name = $form->view_name;
$label_name = $form->label_name;
$field_type = (int)$form->field_type;
$pick_list_type = (int)$form->pick_list_type;
$pick_list = $form->pick_list;
$pick_table = $form->pick_table;
$auto_prime_id = $form->auto_prime_id;
$auto_dispaly_value = $form->auto_dispaly_value;
if($label_name == "role"){
$view_name = "Category";
}
if((int)$field_type === 4){
$select_query .= 'DATE_FORMAT('.$table_name.'.'.$label_name.', "%d-%m-%Y") 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_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}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_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$pick_list_val_2 as $label_name , ";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$pick_list_val_1 = $table_name.$label_name ";
}
}else
if($field_type === 9){
$pick_query_as = $pick_table."_".$prime_form_id;
$select_query .= "$pick_query_as.$auto_dispaly_value as $label_name,";
$pick_query .= " left join $pick_table as $pick_query_as on $pick_query_as.$auto_prime_id = $table_name.$label_name ";
}else{
$select_query .= "$table_name.$label_name , ";
}
if((int)$i === 1){
$th_style = "class='hard_left'";
}else
if((int)$i === 2){
$th_style = "class='next_left'";
}else{
$th_style = "";
}
$thead_line .= "<th $th_style>$view_name</th>";
$i++;
}
$thead = "<tr>$thead_line </tr>";
$select_query = rtrim($select_query,',');
$select_query = rtrim($select_query,' , ');
$trans_qry = " inner join cw_employees on cw_employees.employee_code =cw_monthly_input.employee_code where cw_monthly_input.termination_status = 0 and ".$table_name.".trans_status = 1 and cw_monthly_input.process_month=\"".$process_month."\"";
$final_qry = "select prime_monthly_input_id,$select_query $query_key from $table_name $pick_query $trans_qry and cw_monthly_input.role = \"".$process_role."\" order by cw_monthly_input.date_of_joining DESC";
$final_data = $this->db->query("CALL sp_a_run ('SELECT','$final_qry')");
$final_result = $final_data->result();
$final_data->next_result();
$tr_line = "";
foreach($final_result as $rslt){
$input_id = $rslt->prime_monthly_input_id;
$td_line = "";
$dlt_btn_line = "";
$j = 1;
foreach($rslt as $label_name => $value){
if($label_name !== "prime_monthly_input_id"){
if((int)$j === 1){
$cls = "class='hard_left'";
}else
if((int)$j === 2){
$cls = "class='next_left'";
}else{
$cls = "";
}
if($label_name === "date_of_joining"){
$doj = $value;
$input_doj_id = "hid_doj_".$input_id;
$input = "<input type='hidden' id='$input_doj_id' value='$doj'>".date('d-m-Y',strtotime($value));
$delete_input = "";
}else
if(($label_name === "process_month") || ($label_name === "employee_code") || ($label_name === "emp_name")){
if(($label_name === "process_month")){
$input_pro_id = "hid_process_month_".$input_id;
$process_month = $search_data->$label_name;
$input = "<input type='hidden' id='$input_pro_id' value='$process_month'>".$value;
}else
if(($label_name === "employee_code")){
$input_pro_id = "hid_employee_code_".$input_id;
$employee_code = $search_data->$label_name;
$input = "<input type='hidden' id='$input_pro_id' value='$employee_code'>".$value;
}else{
$input = "<input type='hidden' id='$input_pro_id' value='$employee_code'>".$value;
}
$delete_input = "";
}else
if($label_name === "supplementary_status"){
$id = $label_name."_$input_id";
$function = "onchange='update_table($input_id,this.name,this.value)'";
if($value === "1"){
$checked = "checked";
}else{
$checked = "";
}
$input = "<span style='display:none;'>$value</span><input type='checkbox' class='save_change' name='$label_name' id='$id' $function value='$value' $read_only $checked/>";
$delete_input = "";
}else{
$id = $label_name."_$input_id";
$function = "onchange='update_table($input_id,this.name,this.value)'";
$delete_function = "onclick='delete_column(\"$label_name\")'";
$input = "<span style='display:none;'>$value</span><input type='text' class='form-control number' name='$label_name' id='$id' $function value='$value' $read_only/>";
$delete_input = "<button class='btn btn-xs btn-danger fliter' id='delete_btn_$label_name' $delete_function style='margin-top:7px'><i class='fa fa-trash' aria-hidden='true'></i> Delete</button>";
}
$td_line .= "<td $cls>$input</td>";
$dlt_btn_line .= "<td>$delete_input</td>";
$j++;
}
}
$tr_line .= "<tr>$td_line</tr>";
}
$table_content = "<div class='outer'><div class='inner' ><table class='table table-bordered col-style' id='detail_list' style='box-shadow:none;'>
<thead>
$thead
</thead>
<tbody>
<tr>$dlt_btn_line</tr>
$tr_line
</tbody>
</table></div></div>";
return $table_content;
}
//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')")) {
$table_view = $this->transaction_data($search_month,$category);
echo json_encode(array(
'success' => true,
'message' => "Column Deleted successfully",'table_content' =>$table_view
));
} 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');
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';
$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);
}
}
$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 . '" 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');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
$import_query = 'insert into cw_month_import (module_id,excel_format,excel_file_path,excel_sheet_name,excel_start_row,excel_end_row,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 . '","' . $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');
$emp_qry = 'select employee_code,date_of_joining,role,emp_name,prime_employees_id from cw_employees where trans_status = 1';
$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');
$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;
$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();
if (!$excel_format_result) {
return json_encode(array(
'success' => false,
'message' => "Please map excel cell column before import"
));
} else {
try {
$excel_obj = PHPExcel_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") {
/* $check_cat_qry = 'select * from cw_category where category_name="'.$get_cell_value.'"';
$check_cat_info = $this->db->query("CALL sp_a_run ('RUN','$check_cat_qry')");
$check_cat_result = $check_cat_info->result();
$check_cat_info->next_result();
$role_num_rows = $check_cat_info->num_rows();
$cat_id = $check_cat_result[0]->prime_category_id;
$get_cell_value = $cat_id; */
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;
/* $emp_id_qry = 'select * from cw_employees where employee_code="'.$get_cell_value.'"';
$emp_id_info = $this->db->query("CALL sp_a_run ('RUN','$emp_id_qry')");
$emp_id_result = $emp_id_info->result();
$emp_id_info->next_result();
$code_num_rows = $emp_id_info->num_rows();
$emp_doj = $emp_id_result[0]->date_of_joining;
$category = $emp_id_result[0]->role; */
//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;
$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));
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";
}
}
}
}
}
$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") {
/* $check_cat_qry = 'select * from cw_category where category_name="'.$get_cell_value.'"';
$check_cat_info = $this->db->query("CALL sp_a_run ('RUN','$check_cat_qry')");
$check_cat_result = $check_cat_info->result();
$check_cat_info->next_result();
$cat_id = $check_cat_result[0]->prime_category_id; */
$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_id_qry = 'select * from cw_employees where employee_code="'.$get_cell_value.'"';
$emp_id_info = $this->db->query("CALL sp_a_run ('RUN','$emp_id_qry')");
$emp_id_result = $emp_id_info->result();
$emp_id_info->next_result();
$emp_id = $emp_id_result[0]->prime_employees_id;
$emp_name = $emp_id_result[0]->emp_name;
$doj = $emp_id_result[0]->date_of_joining;
$cat = $emp_id_result[0]->role; */
$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;
}
$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,role,emp_name,date_of_joining,process_month,trans_created_by,trans_created_date";
$prime_cell_val .= '"' . $emp_cell_val . '","' . $cat . '","' . $emp_name . '","' . $doj . '","' . $process_month . '","' . $this->logged_id . '",' . '"' . $created_on . '"';
$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 . ' WHERE prime_monthly_input_id = "' . $upd_prime_id . '"';
$this->db->query("CALL sp_a_run ('RUN','$upd_query')");
$status_info['status'] = "Changed to active";
} 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_line_column_name,excel_line_value from cw_util_excel_format_line where excel_line_module_id = "' . $module_id . '" and prime_excel_format_id ="' . $excel_format . '" 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();
require_once APPPATH . "/third_party/PHPExcel.php";
$obj = new PHPExcel();
//Set the first row as the header row
foreach ($excel_result as $excel) {
$excel_line_column_name = $excel->excel_line_column_name;
$excel_line_value = $excel->excel_line_value;
$obj->getActiveSheet()->setCellValue($excel_line_value . "1", $excel_line_column_name);
}
// Rename worksheet name
$filename = $module_id . ".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 = PHPExcel_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 = PHPExcel_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'));
//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 trans_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' => "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;
$upd_mi_query = 'UPDATE cw_monthly_input SET supplementary_day ="'.$supp_paid_days.'", supplementary_month ="'.$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;
}
}
?>