File: /home/cafsindia/hrms_patroniss_com/application/controllers/Monthly_input_bk05oct2020.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("Base_controller.php");
class Monthly_input extends Base_controller
{
public function __construct()
{
parent::__construct('monthly_input');
$this->load->model('Process_payroll_model');
if (!$this->Appconfig->isAppvalid()) {
redirect('config');
}
$this->collect_base_info();
}
// LOAD PAGE WITH TABLE DATA
public function index()
{
//GET Monthly Input Columns
$lock_query = 'select * 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';
$lock_data = $this->db->query("CALL sp_a_run ('SELECT','$lock_query')");
$lock_result = $lock_data->result();
$lock_data->next_result();
$data['table_headers'] = $this->xss_clean(get_monthly_headers($this->monthly_info, $lock_result));
$data['fliter_list'] = $this->fliter_list;
$data['link_info'] = $this->quick_link;
$role_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1 and prime_category_id !=1')");
$role_result = $role_info->result();
$role_info->next_result();
$category_list[""] = "---- Select Category ----";
foreach ($role_result as $for) {
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$category_list[$role_id] = $category_name;
}
$data['category_list'] = $category_list;
$data['module_id'] = $this->control_name;
$excel_format_qry = 'select prime_excel_format_id,excel_name from cw_util_excel_format where excel_module_id = "' . $this->control_name . '" and trans_status = 1';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_result = $excel_format->result();
$excel_format->next_result();
$excel_format_drop[""] = "---- Excel Format ----";
foreach ($excel_result as $excel) {
$prime_excel_format_id = $excel->prime_excel_format_id;
$excel_name = $excel->excel_name;
$excel_format_drop[$prime_excel_format_id] = $excel_name;
}
$data['excel_format_drop'] = $excel_format_drop;
$this->load->view('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()
{
$role = $this->input->get('category');
$search_month = $this->input->get('search_month');
$process_mode = $this->input->get('process_mode');
$search = $this->input->get('search');
$limit = $this->input->get('limit');
$offset = $this->input->get('offset');
$sort = $this->input->get('sort');
$order = $this->input->get('order');
$logged_id = $this->session->userdata('logged_id');
$today_date = date("Y-m-d h:i:s");
$num_rows = 0;
if ($search) {
$search_qry = "(cw_monthly_input.emp_name like '" . $search . "%' or cw_monthly_input.employee_code like '" . $search . "%') and ";
} else {
$search_qry = "";
}
if ($role) {
$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_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;
$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 (!$sort) {
$sort = $this->prime_table . "." . $this->prime_id;
}
if (!$order) {
$order = "asc";
}
$search_query = "select * from " . $this->prime_table;
if ($role) {
$search_query .= " where $this->prime_table.trans_status = 1 and $this->prime_table.termination_status = 0 and $search_qry process_month = '$search_month' and role = $role";
} else {
$search_query .= " where $this->prime_table.trans_status = 1 and $this->prime_table.termination_status = 0 and $search_qry process_month = '$search_month'";
}
//ADDED FILTER QUERY HERE
$search_query .= " ORDER BY $sort $order";
$search_query .= " LIMIT $offset,$limit";
$lock_query = 'select * 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';
$lock_data = $this->db->query("CALL sp_a_run ('SELECT','$lock_query')");
$lock_result = $lock_data->result();
$lock_data->next_result();
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));
}
/*$leave_count_qry = 'SELECT COUNT(CASE WHEN cw_dailyunpunch.manager_status = "P" then 1 ELSE NULL END) as "present",COUNT(CASE WHEN cw_dailyunpunch.manager_status = "L" then 1 ELSE NULL END) as "leave" FROM cw_dailyunpunch inner join cw_employees on cw_employees.thumb_no = cw_dailyunpunch.dcode where cw_employees.role = "' . $role . '" and cw_employees.termination_status = 0 and cw_employees.payroll = 1 and cw_dailyunpunch.att_date between "'.$sal_start_date.'" and "'.$sal_end_date.'"';
$leave_count_data = $this->db->query("CALL sp_a_run ('SELECT','$leave_count_qry')");
$leave_count_rslt = $leave_count_data->result();
$leave_count_data->next_result();*/
// 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');
//echo "BSK $update_columns"; die;
//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';
//echo "BSK $trans_exist_qry"; die;
$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();
//echo $trans_exist_count; die;
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 . '"';
$this->db->query("CALL sp_a_run ('UPDATE','$get_previous_val_qry')");
}
}
}
}
//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')");
}
}
}
//echo $search_query; die;
$search_data = $this->db->query('CALL sp_a_run ("SELECT","' . $search_query . '")');
$search_result = $search_data->result();
$num_rows = $search_data->num_rows();
$search_data->next_result();
$data_rows = array();
$data_rows[] = get_delete_column_row($this->table_info);
foreach ($search_result as $search) {
$data_rows[] = get_monthly_row($search, $this->table_info, $this, $lock_result, $payroll_count);
}
//FETCH RECORDS COUNT
$base_query = "select count(*) as rslt_count from " . $this->prime_table;
$base_query .= " where $this->prime_table.trans_status = 1 and $this->prime_table.termination_status = 0 and process_month = '$search_month' and role='$role'";
$count_data = $this->db->query('CALL sp_a_run ("SELECT","' . $base_query . '")');
$count_result = $count_data->result();
$count_data->next_result();
echo json_encode(array(
'total' => $count_result[0]->rslt_count,
'rows' => $data_rows
));
} else {
$data_rows = array();
$search = 1;
$this->table_info = "";
$data_rows[] = get_monthly_row($search, $this->table_info, $this, $lock_result, $payroll_count);
echo json_encode(array(
'total' => $search,
'rows' => $data_rows
));
}
} 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')");
$data_rows = array();
$search = 1;
$this->table_info = "";
$data_rows[] = get_monthly_row($search, $this->table_info, $this, $lock_result, $payroll_count);
echo json_encode(array(
'total' => $search,
'rows' => $data_rows
));
} else {
$data_rows['prime_monthly_input_id'] = 0;
$this->table_info = "";
$search = 1;
$data_rows[] = get_monthly_row($search, $this->table_info, $this, $lock_result, $payroll_count);
echo json_encode(array(
'total' => $search,
'rows' => $data_rows
));
}
}
}
}
}
//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()
{
$send_data = $this->input->post('send_data');
$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');
$upd_query = "";
foreach ($send_data as $key => $value) {
if ($key === "supplementary_status") {
$supplementary_status = $value;
}
$upd_query .= $key . ' = "' . $value . '",';
}
if ((int) $supplementary_status === 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
/*$end_month = date("Y-m-".$day_start, strtotime("-1 month", strtotime($hid_process_month)));
$prev_month = date("Y-m-d",strtotime("-1 month", strtotime($end_month)));*/
$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';
/*$end_month = date("Y-m-".$sal_start,strtotime($hid_process_month));
$prev_month = date("Y-m-d",strtotime("-1 month", strtotime($end_month)));*/
$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 ($upd_query) {
$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 ' . $upd_query . ' 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!!!"
));
} else {
echo json_encode(array(
'success' => false,
'status' => "Unable",
'message' => "Unable to process your request"
));
}
}
//IMPORT FILE VIEW INFORMATION
public function import()
{
$data['module_id'] = $this->control_name;
$excel_format_qry = 'select prime_excel_format_id,excel_name from cw_util_excel_format where excel_module_id = "' . $this->control_name . '" and trans_status = 1';
$excel_format = $this->db->query("CALL sp_a_run ('SELECT','$excel_format_qry')");
$excel_result = $excel_format->result();
$excel_format->next_result();
$excel_format_drop[""] = "---- Excel Format ----";
foreach ($excel_result as $excel) {
$prime_excel_format_id = $excel->prime_excel_format_id;
$excel_name = $excel->excel_name;
$excel_format_drop[$prime_excel_format_id] = $excel_name;
}
$data['excel_format_drop'] = $excel_format_drop;
$this->load->view('monthly_input/', $data);
}
/* ==============================================================*/
/* ============ MONHTLY IMPORT OPERATION - START ================*/
/* ==============================================================*/
//SAVE MONTHLY IMPORT FILE PATH
public function save_import()
{
$module_id = $this->input->post('module_id');
$category = $this->input->post('category_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,category,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 . '","' . $category . '","' . $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"
));
}
$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;
$role = (int)$excel_path_result[0]->category;
$emp_qry = 'select employee_code,date_of_joining,role,emp_name,prime_employees_id from cw_employees where trans_status = 1 and role='.$role;
$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');
$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 === "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;
$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 or this category";
} 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 === "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;
}
$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'] = "Updated Successfully";
} 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 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 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 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();
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->view_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()
{
$category = $this->input->post('category');
$search_month = $this->input->post('search_month');
$emp_code_list = implode(",", $this->input->post('emp_code_list'));
$month_id = implode(",", $this->input->post('month_id'));
$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_id . ')';
$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');
$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';
$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';
$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?"
));
}
}
}
?>