File: /home/cafsindia/hrms_cafsindia_com/application/controllers/Supplementary_day.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Base_controller.php");
class Supplementary_day extends Base_controller{
public function __construct(){
parent::__construct('supplementary_day');
$this->load->model('Process_payroll_model');
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
$this->collect_base_info();
}
// LOAD PAGE WITH TABLE DATA
public function index(){
//category list
$cat_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1 and prime_category_id!=1')");
$cat_result = $cat_info->result();
$cat_info->next_result();
$category_list[""] = "---- Select Category ----";
foreach($cat_result as $cat_for){
$role_id = $cat_for->prime_category_id;
$category_name = $cat_for->category_name;
$category_list[$role_id] = $category_name;
}
$data['category_list'] = $category_list;
$this->load->view("$this->control_name/manage",$data);
}
//SEARCH FUNCTION FOR SUPPLEMENTARY DAY
public function search_supplementary(){
$category = (int)$this->input->post('category');
$select_month = $this->input->post('select_month');
$process_date = "01-".$select_month;
$process_date = strtotime($process_date);
$cur_process_date = date("Y-m-d", $process_date);
$exist_trans_qry = 'select count(*) as trans_count from cw_transactions where transactions_month = "'.$select_month.'" and role = "'.$category.'" and trans_status = 1';
$exist_trans_data = $this->db->query("CALL sp_a_run ('SELECT','$exist_trans_qry')");
$exist_trans_result = $exist_trans_data->result();
$exist_trans_data->next_result();
$trans_count = $exist_trans_result[0]->trans_count;
$supplymentry_exit_qry = 'select count(*) as supply_count from cw_supplementary_detail where supplementary_month = "'.$select_month.'" and category = "'.$category.'" 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_count = $supplymentry_result[0]->supply_count;
if((int)$trans_count === 0){
$check_supp_set_qry = 'select back_month from cw_supplementary_settings where trans_status = 1 and category="'.$category.'"';
$check_supp_set_data = $this->db->query("CALL sp_a_run ('SELECT','$check_supp_set_qry')");
$check_supp_set_result = $check_supp_set_data->result();
$check_supp_set_data->next_result();
$back_month = $check_supp_set_result[0]->back_month;
$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){
$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){
$back_month_date = date("Y-m-".$day_start, strtotime("-$back_month month", $process_date));
$final_back_month = date("Y-m-".$day_start, strtotime("-1 month", strtotime($back_month_date)));
$back_month_end_date = date('Y-m-'.$day_end, strtotime("-1 month",$process_date));
}else{
$sal_start = '01';
$back_month_date = date("Y-m-".$sal_start, strtotime("-$back_month month", $process_date));
$final_back_month = date("Y-m-".$sal_start, strtotime("-$back_month month", $process_date));
$back_month_end_date = date('Y-m-t', strtotime("-1 month",$process_date));
}
}
if($back_month){//one month salary start to end month
if((int)$supply_count === 0){
$search_emp_qry = 'select prime_employees_id,role,employee_code,emp_name,date_of_joining,lop_days from cw_employees where trans_status=1 and employee_code not in (select employee_code from cw_transactions where trans_status=1) and date_of_joining between "'.$final_back_month.'" AND "'.$back_month_end_date.'"';
$search_emp_data = $this->db->query("CALL sp_a_run ('SELECT','$search_emp_qry')");
$search_emp_result = $search_emp_data->result();
$search_emp_data->next_result();
$logged_id = $this->logged_id;
$created_date = date('Y-m-d H:i:s');
if(!empty($search_emp_result)){
$emp_data = array();
foreach($search_emp_result as $search_emp){
$emp_id = $search_emp->prime_employees_id;
$category = $search_emp->role;
$emp_code = str_replace('"',"",$search_emp->employee_code);
$emp_name = $search_emp->emp_name;
$doj = $search_emp->date_of_joining;
$doj_month = date("m-Y",strtotime($doj));
$lop_days = $search_emp->lop_days;
$months = $this->get_months($cur_process_date,$back_month_date);//start month to current month
foreach($months as $dt){
$month = $dt->format("m-Y");
$paid_days = 0;
if($doj_month <= $month){
$month_days = $this->Process_payroll_model->get_total_work_days($category,$month);
$dd_days = $this->Process_payroll_model->get_differential_day($emp_code,$category,$month);
$paid_days = $month_days - $dd_days;
}
}
$emp_data[] = "('".$category."','".$select_month."','".$emp_id."','".$emp_code."','".$emp_name."','".$doj."','".$month."','".$paid_days."','".$lop_days."','".$month_days."','".$paid_days."','".$logged_id."','".$created_date."')";
}
$supp_insert_query = '"INSERT into cw_supplementary_detail(category,supplementary_month,employees_id,emp_code,emp_name,doj,back_month,paid_days,lop_days,supp_month_days,supp_paid_days,trans_created_by,trans_created_date) values '.implode(',',$emp_data).'"';
$save_supplymentry = $this->db->query("CALL sp_a_run ('INSERT',$supp_insert_query)");
$save_supplymentry_result = $save_supplymentry->result();
$save_supplymentry->next_result();
if(!empty($save_supplymentry_result)){
$table_info = $this->supplementary_table_view($category,$select_month);
echo json_encode(array('success' => TRUE, 'msg' => "Success to see the supplementary", 'table_info' => $table_info));
}
}else{
echo json_encode(array('success' => False, 'msg' => "No Data Found on this search!!"));
}
}else{
$table_info = $this->supplementary_table_view($category,$select_month);
echo json_encode(array('success' => TRUE, 'msg' => "Success to see the supplementary", 'table_info' => $table_info));
}
}else{
echo json_encode(array('success' => False, 'msg' => "Back Month not set this category?"));
}
}else{
echo json_encode(array('success' => False, 'msg' => "Already payroll process is completed?"));
}
}
//TABLE VIEW FUNCTION FOR SUPPLEMENTARY DAY
public function supplementary_table_view($category,$select_month){
$select_supp_qry = 'select * from cw_supplementary_detail where supplementary_month = "'.$select_month.'" and category = "'.$category.'" and trans_status = 1';
$select_supp_info = $this->db->query("CALL sp_a_run ('SELECT','$select_supp_qry')");
$select_supp_result = $select_supp_info->result();
$select_supp_info->next_result();
$tr_line = "";
foreach ($select_supp_result as $rslt){
$supp_detail_id = $rslt->prime_supplementary_detail_id;
$employees_id = $rslt->employees_id;
$emp_code = $rslt->emp_code;
$emp_name = $rslt->emp_name;
$doj = date("d-m-Y", strtotime($rslt->doj));
$back_month = $rslt->back_month;
$paid_days = $rslt->paid_days;
$lop_days = $rslt->lop_days;
$tr_line .= "<tr>
<td>$emp_code</td>
<td>$emp_name</td>
<td>$doj</td>
<td>$back_month</td>
<td>$paid_days</td>
<td><input type='text' id='lop_days_$supp_detail_id' name='lop_days_$supp_detail_id' onchange='update_supplementry($supp_detail_id,$paid_days)' value='$lop_days'/></td>
</tr>";
}
$th_line = "<tr>
<th>Employee Code</th>
<th>Employee Name</th>
<th>Date of Joining</th>
<th>Back Month</th>
<th>Paid Days</th>
<th>Lop Days</th>
</tr>";
$table_info = "<table id='supplementary_day' class='table table-bordered table-hover table-striped'>
<thead>
$th_line
</thead>
<tbody>
$tr_line
</tbody>
</table>";
return $table_info;
}
//SUPPLEMENTARY DAY LOP UPDATE BY MANUALLY
public function update_supplementry(){
$supp_id = (int)$this->input->post('supp_id');
$lop_days = $this->input->post('lop_days');
$paid_days = $this->input->post('paid_days');
$supp_paid_days = $paid_days;
if($lop_days > 0){
$supp_paid_days = $supp_paid_days - $lop_days;
}
$update_on = date("Y-m-d H:i:s");
$logged_id = $this->session->userdata('logged_id');
if(!empty($supp_id)){
$upd_query = 'UPDATE cw_supplementary_detail SET lop_days ="'.$lop_days.'", supp_paid_days ="'.$supp_paid_days.'", trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$update_on.'" WHERE prime_supplementary_detail_id = "'.$supp_id.'"';
$result = $this->db->query("CALL sp_a_run ('UPDATE','$upd_query')");
echo json_encode(array('success' => true, 'msg' => "Updated successfully!!!"));
}else{
echo json_encode(array('success' => true, 'msg' => "Server Timeout!!!"));
}
}
//FIND MONTH DIFFERENCE COUNT
public function get_months($apply_date,$effective_date){
$start = new DateTime($effective_date);
$start->modify('first day of this month');
$end = new DateTime($apply_date);
$end->modify('first day of this month');
$interval = DateInterval::createFromDateString('1 month');
$period = new DatePeriod($start, $interval, $end);
return $period;
}
}
?>