File: /home/cafsindia/hrms_allyindian_com/application_bk/controllers/Leave_export.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Leave_export extends Action_controller{
public function __construct(){
parent::__construct('leave_export');
}
// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
public function index(){
//PAGE INFO FUNCTION
$this->page_info();
$data['module_sts'] = (int)$this->module_sts;
$data['quick_link'] = $this->quick_link;
$data['pick_list'] = $this->pick_list;
$data['form_info'] = $this->form_info;
$data['table_head'] = $this->table_head;
$data['fliter_list'] = $this->fliter_list;
$data['freeze_list'] = $this->freeze_list;
$role_info = $this->db->query("CALL sp_a_run ('SELECT','SELECT * FROM `cw_category` where trans_status = 1 and prime_category_id !=1')");
$role_result = $role_info->result();
$role_info->next_result();
$category_list[""] = "---- Select Category ----";
foreach ($role_result as $for) {
$role_id = $for->prime_category_id;
$category_name = $for->category_name;
$category_list[$role_id] = $category_name;
}
$data['category_list'] = $category_list;
$data['key'] = $this->generateKey();
$this->load->view("$this->control_name/manage",$data);
}
public function leave_export(){
$encString = file_get_contents('php://input');
$_POST = $this->cryptoDecrypt($encString);
if(!$_POST){
echo json_encode(array('success' => false,'message' => 'Url Expired.. Please refresh the page and try again....','table_data' => ""));
exit(0);
}
$process_month = $this->input->post('process_month');
$category = $this->input->post('category');
$process_date = date("Y-m-d",strtotime("01-".$process_month));
$check_lock_count = $this->check_posting_lock($category,$process_month);
if((int)$check_lock_count > 0){
echo json_encode(array('success' => FALSE, 'msg' => "Payroll Posting Locked for This Month!!"));
exit(0);
}
$leave_export_query = 'SELECT count(*) as count,MAX(str_to_date(CONCAT("01-", process_month), "%d-%m-%Y")) AS process_date from cw_leave_export where cw_leave_export.category = "'.$category.'" and cw_leave_export.trans_status = 1';
$leave_export_info = $this->db->query("CALL sp_a_run ('SELECT','$leave_export_query')");
$leave_export_result = $leave_export_info->result();
$leave_export_info->next_result();
$leave_export_count = $leave_export_result[0]->count;
if($leave_export_count){
$export_date = $leave_export_result[0]->process_date;
$export_month = date('m-Y',strtotime($export_date));
$past_process_month = date('m-Y',strtotime("-1 month",strtotime($export_date)));
$future_process_month = date('m-Y',strtotime("+1 month",strtotime($export_date)));
if(($process_month > $future_process_month) || ($export_month > $process_month)){
echo json_encode(array('success' => FALSE, 'msg' => "Invalid Month Choose? Please Check it!!"));
exit(0);
}
}
$emp_category_qry = $this->db->query("CALL sp_a_run ('SELECT','SELECT lower(category_name) as category from cw_category where trans_status = 1 and prime_category_id = ".$category."')");
$emp_category_result = $emp_category_qry->result();
$emp_category_qry->next_result();
$category_name = $emp_category_result[0]->category;
$month_day_info = $this->db->query("CALL sp_a_run ('SELECT','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_result = $month_day_info->result();
$month_day_info->next_result();
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;
$today_date = date("Y-m-d");
if((int)$day_conditions === 3){
$sal_start = $day_start;
//For Current month between days increment
$date = new DateTime("01-$process_month 00:00:00");
$date->modify('-1 month');
$salary_start_date = $date->format("Y-m-$sal_start");
$salary_end_date = date("Y-m-d",strtotime($day_end."-".$process_month));
}else{
$sal_start = '01';
$date = new DateTime("01-$process_month 00:00:00");
$salary_start_date = $date->format("Y-m-$sal_start");
$salary_end_date = date("Y-m-d",strtotime($day_end."-".$process_month));
}
//LEAVE FINANCIAL YEAR GET FUNCTION
$financial_info = $this->get_leave_financial_details();
$fin_start_date = date("Y-m-d",strtotime($financial_info[0]->starting_date));
$fin_end_date = date("Y-m-d",strtotime($financial_info[0]->ending_date));
//check salary start date and end date in between fin year date
if($fin_start_date <= $salary_start_date && $fin_end_date >= $salary_end_date){
//CHECK LEAVE EXPORT DATE <= TO SALARY END DATE
if($today_date > $salary_end_date){
$leave_entry_pending_qry = 'select count(*) as count from cw_leave_entry where cw_leave_entry.trans_status = 1 and cw_leave_entry.leave_status = 1 and component_value = "'.$category.'" and cw_leave_entry.leave_date >= "'.$salary_start_date.'" AND cw_leave_entry.leave_date <= "'.$salary_end_date.'"';
$leave_entry_pending_info = $this->db->query("CALL sp_a_run ('SELECT','$leave_entry_pending_qry')");
$leave_entry_pending_result = $leave_entry_pending_info->result();
$leave_entry_pending_info->next_result();
$leave_entry_pending_count = $leave_entry_pending_result[0]->count;
if((int)$leave_entry_pending_count){
echo json_encode(array('success' => FALSE, 'msg' => "Leave Status was Pending check it??"));
}else{
//LEAVE CREATION QRY FOR GET A ALL LEAVE NAME
$leave_creation_qry = $this->db->query("SELECT prime_leave_creation_id,lower(leave_name) as leave_name FROM cw_leave_creation WHERE trans_status = 1");
$leave_creation_rslt = $leave_creation_qry->result();
foreach ($leave_creation_rslt as $key => $value) {
$leave_id = $value->prime_leave_creation_id;
$leave_name = $value->leave_name;
if($leave_name){
$select_columns .= "SUM(CASE WHEN leave_type = $leave_id THEN leave_count ELSE 0 END) ".$leave_name.",";
}
}
$select_columns = rtrim($select_columns,",");
$leave_entry_qry = 'select employee_code,'.$select_columns.' from cw_leave_entry where cw_leave_entry.trans_status = 1 and cw_leave_entry.leave_status = 2 and component_value = "'.$category.'" and cw_leave_entry.leave_date >= "'.$salary_start_date.'" AND cw_leave_entry.leave_date <= "'.$salary_end_date.'" group by employee_code';
$leave_entry_info = $this->db->query("CALL sp_a_run ('SELECT','$leave_entry_qry')");
$leave_entry_result = $leave_entry_info->result_array();
$leave_entry_info->next_result();
if(!$leave_entry_result){
echo json_encode(array('success' => FALSE, 'msg' => "No data found for this month...!!"));
}else{
$insert_leave_export_qry = 'insert into cw_leave_export (category,process_month,trans_created_by,trans_created_date) values ("'.$category.'","'.$process_month.'","'.$this->logged_id.'","'.$created_on.'")';
$insert_leave_export_info = $this->db->query("CALL sp_a_run ('INSERT','$insert_leave_export_qry')");
$insert_leave_export_rslt = $insert_leave_export_info->result();
$insert_leave_export_info->next_result();
$payroll_lock = $this->set_posting_lock($category,$process_month);
if($payroll_lock){
echo json_encode(array('success' => TRUE,'export_result'=>$leave_entry_result));
}
}
}
}else{
echo json_encode(array('success' => FALSE, 'msg' => "Yet to Reach our Salary Date. Please Try After Sometime."));
}
}else{
echo json_encode(array('success' => FALSE, 'msg' => "Please Check Your Financial Year Setting..!!"));
}
}
}
public function check_posting_lock($category,$process_month) {
$posting_lock_qry = 'SELECT COUNT(*) as count FROM cw_payroll_posting_lock WHERE trans_status = 1 and posting_month = "'.$process_month.'" and posting_lock = 1';
$posting_lock_info = $this->db->query("CALL sp_a_run ('SELECT','$posting_lock_qry')");
$posting_lock_result = $posting_lock_info->result();
$posting_lock_info->next_result();
$lock_count = $posting_lock_result[0]->count;
return $lock_count;
}
public function set_posting_lock($category,$process_month){
$posting_lock_qry = 'SELECT COUNT(*) as count FROM cw_payroll_posting_lock WHERE trans_status = 1 and posting_month = "'.$process_month.'" and trans_status = 1';
$posting_lock_info = $this->db->query("CALL sp_a_run ('SELECT','$posting_lock_qry')");
$posting_lock_result = $posting_lock_info->result();
$posting_lock_info->next_result();
$lock_count = $posting_lock_result[0]->count;
$created_on = date("Y-m-d H:i:s");
if((int)$lock_count > 0){
$update_query = 'UPDATE cw_payroll_posting_lock SET posting_lock = "1",trans_updated_by = "'.$this->logged_id.'", trans_updated_date = "'.$created_on.'" WHERE posting_month = "'. $process_month.'" and category = "'.$category.'"';
$this->db->query("CALL sp_a_run ('UPDATE','$update_query')");
return true;
}else{
$insert_posting_lock_query = 'insert into cw_payroll_posting_lock (category,posting_month,posting_lock,trans_created_by,trans_created_date) values ("'.$category.'","'.$process_month.'","1","'.$this->logged_id.'","'.$created_on.'")';
$posting_lock_info = $this->db->query("CALL sp_a_run ('INSERT','$insert_posting_lock_query')");
$posting_lock_result = $posting_lock_info->result();
$posting_lock_info->next_result();
return true;
}
}
}
?>