<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Action_controller.php");
class Leave_year_end_transfer  extends Action_controller{	
	public function __construct(){
		parent::__construct('leave_year_end_transfer');
	}
	
	// LOAD PAGE QUICK LINK,FILTERS AND TABLE HEADERS
	public function index(){
		$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['fliter_list']   = $this->fliter_list;
		$data['freeze_list']   = $this->freeze_list;
		$db_name               = $this->config->item("db_name");
		$prime_table_query     = $this->db->query("SELECT COLUMN_NAME AS label_name, COLUMN_KEY as key_exist FROM information_schema.COLUMNS WHERE TABLE_SCHEMA ='$db_name' and TABLE_NAME = 'cw_leave_financial_year' and COLUMN_NAME NOT IN ('trans_created_by','trans_created_date','trans_updated_by','trans_updated_date','trans_deleted_by','trans_deleted_date','trans_status','prime_leave_financial_year_id') ORDER BY ORDINAL_POSITION");
		$prime_table_col       = $prime_table_query->result();

		$data['table_head']    = $prime_table_col;
		$this->load->view("$this->control_name/manage",$data);
	}
	
	//LOAD TABEL WITH 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']);

		$common_search = "";
		if($search){
			$common_search .= " and (cw_leave_financial_year.starting_date like '%".$search."%' or cw_leave_financial_year.starting_date like '%".$search."%')";
		}
		$search_total       = $this->db->query("SELECT count(*) as allcount from cw_leave_financial_year ");
		$search_total_info  = $search_total->result();
		$total_count        = $search_total_info[0]->allcount;
		
		$search_count       = $this->db->query("SELECT count(*) as allcount from cw_leave_financial_year where trans_status = 1 $common_search");
		$search_info        = $search_count->result();
		$filtered_count     = $search_info[0]->allcount;
		
		$search_query      .= "SELECT prime_leave_financial_year_id,starting_date,ending_date,status_mode_value as set_as_default_financial_year from cw_leave_financial_year inner join cw_status_mode on cw_status_mode.prime_status_mode_id = cw_leave_financial_year.set_as_default_financial_year where cw_leave_financial_year.trans_status = 1 and cw_leave_financial_year.set_as_default_financial_year = 1";

		$search_query      .= "$common_search ";
		if((int)$per_page !== -1){
			$search_query  .= " LIMIT  $start,$per_page";
		}		
		$search_data        = $this->db->query($search_query);
		$search_result      = $search_data->result();
		echo json_encode(array("draw" => intval($draw),"recordsTotal" => $total_count,"recordsFiltered" => $filtered_count,"data" => $search_result));		
	}
	public function year_end_transfer(){
		$prime_id     = $this->input->post('value');
		$password     = md5($this->input->post('password'));		
		//Check Admin Password Exist
		$exist_qry    = 'SELECT employee_code FROM cw_employees WHERE password = "'.$password.'" AND user_right = "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, 'message' => 'Please Enter the Correct Password'));
		}else{
			//Check Logics
			$prime_table_query = $this->db->query("SELECT leave_name FROM cw_leave_creation WHERE trans_status = 1 AND leave_opening = 1 ");
			$columns        = $prime_table_query->result();
			$select_columns = "";
			$ins_columns    = "";
			foreach ($columns as $key => $value) {
				$leave_name =  strtolower($value->leave_name);
				if($leave_name){
					$select_columns .= ",((".$leave_name."+".$leave_name."_credit) - (".$leave_name."_debit +  used_".$leave_name." + pending_".$leave_name." + encash_".$leave_name.")) as ".$leave_name;
					$ins_columns .= ",".$leave_name;
				}				
			}
			//Transfer Financial Setting
			$created_on          = date("Y-m-d H:i:s");
			$current_fin_qry     = 'select starting_date,ending_date from cw_leave_financial_year where prime_leave_financial_year_id = "'.$prime_id.'" and trans_status = "1"';
			$current_fin_data    = $this->db->query("CALL sp_a_run ('SELECT','$current_fin_qry')");
			$current_fin_result  = $current_fin_data->result();
			$exist_data->next_result();
			$starting_date       = $current_fin_result[0]->starting_date;
			$ending_date         = $current_fin_result[0]->ending_date;

			$new_starting_date   = date("Y-m-d", strtotime("+1 Year", strtotime($starting_date)));
			$new_ending_date     = date("Y-m-d", strtotime("+1 Year", strtotime($ending_date)));

			//Update current default year	
			$prime_update_query  = 'UPDATE cw_leave_financial_year SET set_as_default_financial_year = 0 WHERE prime_leave_financial_year_id = "'.$prime_id.'"';
			$this->db->query("CALL sp_a_run ('UPDATE','$prime_update_query')");

			//Insert new default year
			$fin_set_insert_query = 'INSERT INTO cw_leave_financial_year(starting_date,ending_date,set_as_default_financial_year,trans_created_by,trans_created_date) VALUES ("'.$new_starting_date.'","'.$new_ending_date.'","1","'.$this->logged_id.'","'.$created_on.'")';
			$insert_info          = $this->db->query("CALL sp_a_run ('INSERT','$fin_set_insert_query')");
			$insert_result        = $insert_info->result();
			$insert_info->next_result();
			$insert_id            = $insert_result[0]->ins_id;

			$credit_setting_query = 'INSERT INTO cw_leave_credit_setting(manual_credit_type,entry_type,effective_from,mid_calculated_after,category,component_value,leave_financial_year,leave_name,credit_type,credit_based_on,eligible_days,calculation_after,credit_rounding,credit_days,trans_created_by,trans_created_date) SELECT manual_credit_type,entry_type,"'.$new_starting_date.'",mid_calculated_after,category,component_value,"'.$insert_id.'",leave_name,credit_type,credit_based_on,eligible_days,calculation_after,credit_rounding,credit_days,"'.$this->logged_id.'","'.$created_on.'" from cw_leave_credit_setting where leave_financial_year = "'.$prime_id.'"';
			$credit_setting_info  = $this->db->query("CALL sp_a_run ('INSERT','$credit_setting_query')");
			$credit_setting_result= $credit_setting_info->result();
			$credit_setting_info->next_result();

			$leave_opening_query  = 'INSERT INTO cw_leave_opening(component_value,financial_setting_id,employee_code'.$ins_columns.',trans_created_by,trans_created_date) SELECT component_value,"'.$insert_id.'",employee_code '.$select_columns.',"'.$this->logged_id.'","'.$created_on.'" from cw_leave_opening  where financial_setting_id = "'.$prime_id.'"';
			$leave_opening_info   = $this->db->query("CALL sp_a_run ('INSERT','$leave_opening_query')");
			$leave_opening_result = $leave_opening_info->result();
			$leave_opening_info->next_result();

			if($leave_opening_result){
				echo json_encode(array('success' => TRUE, 'message' => 'Year End Transfer Successfully Processed...'));
			}else{
				echo json_encode(array('success' => FALSE, 'message' => 'Please Try After Sometime...'));
			}
		}
	}	
}
?>