<?php 
/**
 * Leave_requests Page Controller
 * @category  Controller
 */
class Leave_requestsController extends SecureController{
	function __construct(){
		parent::__construct();
		$this->tablename = "leave_requests";
	}
	/**
     * List page records
     * @param $fieldname (filter record by a field) 
     * @param $fieldvalue (filter field value)
     * @return BaseView
     */
	function index($fieldname = null , $fieldvalue = null){
		$request = $this->request;
		$db = $this->GetModel();
		$tablename = $this->tablename;
		$fields = array("id", 
			"emp_id", 
			"reason", 
			"files", 
			"actual_leave_days", 
			"holiday_dates", 
			"holiday_type", 
			"status");
		$pagination = $this->get_pagination(MAX_RECORD_COUNT); // get current pagination e.g array(page_number, page_limit)
	#Statement to execute before list record
	$params = array(USER_EMAIL);
  $empid = $db->rawQueryValue("SELECT id FROM employee_details WHERE email = ? LIMIT 1", $params);
  $db->where('emp_id',$empid);
	# End of before list statement
		//search table record
		if(!empty($request->search)){
			$text = trim($request->search); 
			$search_condition = "(
				leave_requests.id LIKE ? OR 
				leave_requests.emp_id LIKE ? OR 
				leave_requests.reason LIKE ? OR 
				leave_requests.files LIKE ? OR 
				leave_requests.actual_leave_days LIKE ? OR 
				leave_requests.holiday_dates LIKE ? OR 
				leave_requests.holiday_type LIKE ? OR 
				leave_requests.status LIKE ? OR 
				leave_requests.entry_by LIKE ?
			)";
			$search_params = array(
				"%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%"
			);
			//setting search conditions
			$db->where($search_condition, $search_params);
			 //template to use when ajax search
			$this->view->search_template = "leave_requests/search.php";
		}
		if(!empty($request->orderby)){
			$orderby = $request->orderby;
			$ordertype = (!empty($request->ordertype) ? $request->ordertype : ORDER_TYPE);
			$db->orderBy($orderby, $ordertype);
		}
		else{
			$db->orderBy("leave_requests.id", ORDER_TYPE);
		}
		if($fieldname){
			$db->where($fieldname , $fieldvalue); //filter by a single field name
		}
		$tc = $db->withTotalCount();
		$records = $db->get($tablename, $pagination, $fields);
		$records_count = count($records);
		$total_records = intval($tc->totalCount);
		$page_limit = $pagination[1];
		$total_pages = ceil($total_records / $page_limit);
		$data = new stdClass;
		$data->records = $records;
		$data->record_count = $records_count;
		$data->total_records = $total_records;
		$data->total_page = $total_pages;
		if($db->getLastError()){
			$this->set_page_error();
		}
		$page_title = $this->view->page_title = "Leave Requests";
		$this->view->report_filename = date('Y-m-d') . '-' . $page_title;
		$this->view->report_title = $page_title;
		$this->view->report_layout = "report_layout.php";
		$this->view->report_paper_size = "A4";
		$this->view->report_orientation = "portrait";
		$this->render_view("leave_requests/list.php", $data); //render the full page
	}
	/**
     * View record detail 
	 * @param $rec_id (select record by table primary key) 
     * @param $value value (select record by value of field name(rec_id))
     * @return BaseView
     */
	function view($rec_id = null, $value = null){
		$request = $this->request;
		$db = $this->GetModel();
		$rec_id = $this->rec_id = urldecode($rec_id);
		$tablename = $this->tablename;
		$fields = array("leave_requests.id", 
			"leave_requests.emp_id", 
			"leave_requests.status", 
			"leave_requests.reason", 
			"leave_requests.files", 
			"leave_requests.actual_leave_days", 
			"leave_requests.holiday_dates", 
			"leave_requests.holiday_type", 
			"leave_requests.entry_by", 
			"employee_details.id AS employee_details_id", 
			"employee_details.employee_name AS employee_details_employee_name", 
			"employee_details.portfolio_id AS employee_details_portfolio_id", 
			"employee_details.basic_pay AS employee_details_basic_pay", 
			"employee_details.bank_acc_no AS employee_details_bank_acc_no", 
			"employee_details.bank_name AS employee_details_bank_name", 
			"employee_details.email AS employee_details_email", 
			"employee_details.dob AS employee_details_dob", 
			"employee_details.date_of_join AS employee_details_date_of_join", 
			"employee_details.date_of_retirement AS employee_details_date_of_retirement", 
			"employee_details.department AS employee_details_department", 
			"employee_details.IFSC AS employee_details_IFSC", 
			"employee_details.designation AS employee_details_designation", 
			"employee_details.caste AS employee_details_caste", 
			"employee_details.relegion AS employee_details_relegion", 
			"employee_details.nominee AS employee_details_nominee", 
			"employee_details.payscale AS employee_details_payscale", 
			"employee_details.gender AS employee_details_gender", 
			"employee_details.identification_mark AS employee_details_identification_mark", 
			"employee_details.place_of_birth AS employee_details_place_of_birth", 
			"employee_details.balanceleave AS employee_details_balanceleave", 
			"employee_details.mobile AS employee_details_mobile", 
			"employee_details.photo AS employee_details_photo", 
			"employee_details.handicapped AS employee_details_handicapped", 
			"employee_details.blind AS employee_details_blind", 
			"employee_details.employee_type AS employee_details_employee_type", 
			"employee_details.service_book AS employee_details_service_book", 
			"employee_details.service_book_remark AS employee_details_service_book_remark");
		if($value){
			$db->where($rec_id, urldecode($value)); //select record based on field name
		}
		else{
			$db->where("leave_requests.id", $rec_id);; //select record based on primary key
		}
		$db->join("employee_details", "leave_requests.emp_id = employee_details.id", "LEFT ");  
		$record = $db->getOne($tablename, $fields );
		if($record){
			$page_title = $this->view->page_title = "View  Leave Requests";
		$this->view->report_filename = date('Y-m-d') . '-' . $page_title;
		$this->view->report_title = $page_title;
		$this->view->report_layout = "report_layout.php";
		$this->view->report_paper_size = "A4";
		$this->view->report_orientation = "portrait";
		}
		else{
			if($db->getLastError()){
				$this->set_page_error();
			}
			else{
				$this->set_page_error("No record found");
			}
		}
		return $this->render_view("leave_requests/view.php", $record);
	}
	/**
     * Insert new record to the database table
	 * @param $formdata array() from $_POST
     * @return BaseView
     */
	function add($formdata = null){
		if($formdata){
			$db = $this->GetModel();
			$tablename = $this->tablename;
			$request = $this->request;
			//fillable fields
			$fields = $this->fields = array("emp_id","holiday_type","holiday_dates","reason","files");
			$postdata = $this->format_request_data($formdata);
			$this->rules_array = array(
				'holiday_type' => 'required',
				'holiday_dates' => 'required',
				'reason' => 'required',
			);
			$this->sanitize_array = array(
				'holiday_type' => 'sanitize_string',
				'holiday_dates' => 'sanitize_string',
				'reason' => 'sanitize_string',
				'files' => 'sanitize_string',
			);
			$this->filter_vals = true; //set whether to remove empty fields
			$modeldata = $this->modeldata = $this->validate_form($postdata);
			$modeldata['emp_id'] = USER_ID;
			if($this->validated()){
		# Statement to execute before adding record
error_reporting(0);
 $acc_holiday = 0;
$vals = explode("-to-",str_replace(" ","",$modeldata['holiday_dates']));
$from = $vals[0];
$to   = $vals[1];
$to = date('Y-m-d', strtotime($to . ' +1 day'));
//execute sql statement and return a single field value
$params = array(USER_EMAIL);
$PID    = $db->rawQueryValue("SELECT portfolio_id FROM employee_details WHERE email = ? LIMIT 1", $params);
$params = array(USER_EMAIL);
$EMID    = $db->rawQueryValue("SELECT id FROM employee_details WHERE email = ? LIMIT 1", $params);
//GETHOLIDAY
$begin = new DateTime($from);
$end = new DateTime($to);
$interval = DateInterval::createFromDateString('1 day');
$period   = new DatePeriod($begin, $interval, $end);
foreach ($period as $dt) {
    unset($found);
    unset($reject);
    $date_to_chose = $dt->format("Y-m-d");
    //query the database and return the results
$fields = array('*');
$db->where("pid='$PID' OR pid=''");
$arr = $db->get("leave_holidays",99999999999, $fields);
foreach($arr as $k){
    $found  = 1;
    $cng_no = $k['no_week'];$cng_dayno=$k['day'];
$year  = date('Y',strtotime($date_to_chose));  // Change the value to use different Year
$month = date('m',strtotime($date_to_chose)); // Change the value to use different month
//$month=9;
$firstday = new  DateTime("$year-$month-1 0:0:0");
$first_w=$firstday->format('w'); // weekday of firstday 
$saturday1=new DateTime;
$my_number=$cng_dayno + ($cng_no-1)*7;
$saturday1->setDate($year,$month,$my_number-$first_w);
$date_c = $saturday1->format('Y-m-d');
if($date_c==$date_to_chose){
$reject = true;
}
}
if(!isset($found)){
$fields = array('*');
$db->where("pid", "");
$arr = $db->get("leave_holidays",999999, $fields);
foreach($arr as $k){
    $found  = 1;
    $cng_no = $k['no_week'];$cng_dayno=$k['day'];
$year  = date('Y',strtotime($date_to_chose));  // Change the value to use different Year
$month = date('m',strtotime($date_to_chose)); // Change the value to use different month
//$month=9;
$firstday = new  DateTime("$year-$month-1 0:0:0");
$first_w=$firstday->format('w'); // weekday of firstday 
$saturday1=new DateTime;
$saturday1->setDate($year,$month,($cng_no*$cng_dayno)-$first_w);
$date_c = $saturday1->format('d-m-Y');
if($date_c==$date_to_chose){
$reject = true;
}
}
}
//check holidays
//query the database and return the results
$fields = array('*');
$db->where("pid='$PID' OR pid=''");
$arr = $db->get("leave_calendar",999999, $fields);
// print_r($arr);
// exit();
foreach($arr as $k){
    if($k['date']==$date_to_chose){
        $reject = true;
    }
}
if(isset($reject)){
}
unset($reject);//=false; ///@@@@@@@@@@@@@@@@@@@@@@ FALSE @@@@@@@@@@@@
if(!isset($reject)){
    //execute sql statement and return a single field value
$params = array($modeldata['holiday_type'],$EMID );
$value  = $db->rawQueryValue("SELECT SUM(value) FROM leave_grant WHERE leave_type = ? and empid= ? LIMIT 1", $params);
if(round($value)<$acc_holiday+1){
    $reject=true;
    $rejectall=true;
}
    $acc_holiday  ++;
}
}
if(isset($rejectall)||$acc_holiday ==0){
    $this->set_flash_msg("Leaves Insufficient OR Check calendar if holiday", "danger");
    return  $this->redirect("leave_requests");
        exit();
}else{
    $actmp=$acc_holiday;
    $db->where("empid", $EMID);
    $db->where("leave_type", $modeldata['holiday_type']);
    $arr = $db->get("leave_grant",99999999999,"*");
    foreach($arr as $k){
        if($actmp<>0){
            if($k['value']>=$actmp){
                $up=$k['value']-$actmp; 
                $params = array($k['id']);
                $bool = $db->rawQuery("UPDATE leave_grant SET value = $up , used=used+$actmp WHERE id = ?", $params); 
                $actmp=0;
            }
            if($k['value']<$actmp){
                $actmp=$actmp-$k['value'];
                $up=0; 
                $val=$k['value'];
                $params = array($k['id']);
                $bool = $db->rawQuery("UPDATE leave_grant SET value = 0 , used=used+$val WHERE id = ?", $params); 
            }
        }
    }
}
		# End of before add statement
				$rec_id = $this->rec_id = $db->insert($tablename, $modeldata);
				if($rec_id){
		# Statement to execute after adding record
		$params = array(USER_EMAIL);
  $empid = $db->rawQueryValue("SELECT id FROM employee_details WHERE email = ? LIMIT 1", $params);
$table_data = array(
    "emp_id"=>$empid,
    "actual_leave_days" => $acc_holiday 
);
$db->where("id", $rec_id);
$bool = $db->update("leave_requests", $table_data);
$seq = $db->getValue("autorization", "IFNULL(MAX(sequence),0)");
if($seq==0){
    $table_data = array(
    "status" => "LEAVE GRANTED AS PER PROTOCOL." 
    );
    $db->where("id", $rec_id);
    $bool = $db->update("leave_requests", $table_data);
}else{
    $db->where("sequence", 1);
    $app_user_id = $db->getValue("autorization", "user");
    $db->where("id", $modeldata['portfolio_id']);
    $profile = $db->getValue("portfolio", "name");
    $table_data = array(
    "rec_id" => $rec_id, 
    "app_userid" => "$app_user_id",
    "stage" => "1"
    );
    $db->insert("leave_requests_accept_reject", $table_data);
    $db->where("id", $app_user_id);
    $userfullname = $db->getValue("user_info", "user_full_name");
      $db->where("id", $app_user_id);
    $username = $db->getValue("user_info", "username");
    $table_data = array(
    "status" => "SENT TO $userfullname($username) FOR APPROVAL" 
    );
    $db->where("id", $rec_id);
    $bool = $db->update("leave_requests", $table_data);
}
		# End of after add statement
					$this->set_flash_msg("Record added successfully", "success");
					return	$this->redirect("leave_requests");
				}
				else{
					$this->set_page_error();
				}
			}
		}
		$page_title = $this->view->page_title = "Add New Leave Requests";
		$this->render_view("leave_requests/add.php");
	}
	/**
     * Update table record with formdata
	 * @param $rec_id (select record by table primary key)
	 * @param $formdata array() from $_POST
     * @return array
     */
	function edit($rec_id = null, $formdata = null){
		$request = $this->request;
		$db = $this->GetModel();
		$this->rec_id = $rec_id;
		$tablename = $this->tablename;
		 //editable fields
		$fields = $this->fields = array("id");
		if($formdata){
			$postdata = $this->format_request_data($formdata);
			$this->rules_array = array(
			);
			$this->sanitize_array = array(
			);
			$modeldata = $this->modeldata = $this->validate_form($postdata);
			if($this->validated()){
				$db->where("leave_requests.id", $rec_id);;
				$bool = $db->update($tablename, $modeldata);
				$numRows = $db->getRowCount(); //number of affected rows. 0 = no record field updated
				if($bool && $numRows){
		# Statement to execute after adding record
			if($modeldata['status']=="REJECT"){
$holiday_type = $modeldata['holiday_type'];
$empid        = $modeldata['empid'];
$act          = $modeldata['Actual_leave_days'];
}
//execute SQL statement and return the result
//query the database and return the results
$fields = array('id');
$db->where("empid", $empid);
$db->where("leave_type", $holiday_type);
$arr = $db->get("leave_grant", $fields);
foreach($arr as $k){
    $id     = $k['id'];
    $params = array($rec_id);
    $bool   = $db->rawQuery("UPDATE leave_grant SET value = value + $act,used=used-$act WHERE id = ?", $params);
break;
}
		# End of after update statement
					$this->set_flash_msg("Record updated successfully", "success");
					return $this->redirect("leave_requests");
				}
				else{
					if($db->getLastError()){
						$this->set_page_error();
					}
					elseif(!$numRows){
						//not an error, but no record was updated
						$page_error = "No record updated";
						$this->set_page_error($page_error);
						$this->set_flash_msg($page_error, "warning");
						return	$this->redirect("leave_requests");
					}
				}
			}
		}
		$db->where("leave_requests.id", $rec_id);;
		$data = $db->getOne($tablename, $fields);
		$page_title = $this->view->page_title = "Edit  Leave Requests";
		if(!$data){
			$this->set_page_error();
		}
		return $this->render_view("leave_requests/edit.php", $data);
	}
	/**
     * Update single field
	 * @param $rec_id (select record by table primary key)
	 * @param $formdata array() from $_POST
     * @return array
     */
	function editfield($rec_id = null, $formdata = null){
		$db = $this->GetModel();
		$this->rec_id = $rec_id;
		$tablename = $this->tablename;
		//editable fields
		$fields = $this->fields = array("id");
		$page_error = null;
		if($formdata){
			$postdata = array();
			$fieldname = $formdata['name'];
			$fieldvalue = $formdata['value'];
			$postdata[$fieldname] = $fieldvalue;
			$postdata = $this->format_request_data($postdata);
			$this->rules_array = array(
			);
			$this->sanitize_array = array(
			);
			$this->filter_rules = true; //filter validation rules by excluding fields not in the formdata
			$modeldata = $this->modeldata = $this->validate_form($postdata);
			if($this->validated()){
				$db->where("leave_requests.id", $rec_id);;
				$bool = $db->update($tablename, $modeldata);
				$numRows = $db->getRowCount();
				if($bool && $numRows){
					return render_json(
						array(
							'num_rows' =>$numRows,
							'rec_id' =>$rec_id,
						)
					);
				}
				else{
					if($db->getLastError()){
						$page_error = $db->getLastError();
					}
					elseif(!$numRows){
						$page_error = "No record updated";
					}
					render_error($page_error);
				}
			}
			else{
				render_error($this->view->page_error);
			}
		}
		return null;
	}
	/**
     * Delete record from the database
	 * Support multi delete by separating record id by comma.
     * @return BaseView
     */
	function delete($rec_id = null){
		Csrf::cross_check();
		$request = $this->request;
		$db = $this->GetModel();
		$tablename = $this->tablename;
		$this->rec_id = $rec_id;
		//form multiple delete, split record id separated by comma into array
		$arr_rec_id = array_map('trim', explode(",", $rec_id));
		$db->where("leave_requests.id", $arr_rec_id, "in");
		$bool = $db->delete($tablename);
		if($bool){
			$this->set_flash_msg("Record deleted successfully", "success");
		}
		elseif($db->getLastError()){
			$page_error = $db->getLastError();
			$this->set_flash_msg($page_error, "danger");
		}
		return	$this->redirect("leave_requests");
	}
	/**
     * List page records
     * @param $fieldname (filter record by a field) 
     * @param $fieldvalue (filter field value)
     * @return BaseView
     */
	function adminlist($fieldname = null , $fieldvalue = null){
		$request = $this->request;
		$db = $this->GetModel();
		$tablename = $this->tablename;
		$fields = array("leave_requests.id", 
			"employee_details.employee_name AS employee_details_employee_name", 
			"leave_requests.reason", 
			"leave_requests.files", 
			"leave_requests.actual_leave_days", 
			"leave_requests.holiday_dates", 
			"leave_requests.holiday_type", 
			"leave_requests.status", 
			"leave_requests.entry_by");
		$pagination = $this->get_pagination(MAX_RECORD_COUNT); // get current pagination e.g array(page_number, page_limit)
		//search table record
		if(!empty($request->search)){
			$text = trim($request->search); 
			$search_condition = "(
				leave_requests.id LIKE ? OR 
				leave_requests.emp_id LIKE ? OR 
				employee_details.employee_name LIKE ? OR 
				leave_requests.reason LIKE ? OR 
				leave_requests.files LIKE ? OR 
				leave_requests.actual_leave_days LIKE ? OR 
				leave_requests.holiday_dates LIKE ? OR 
				leave_requests.holiday_type LIKE ? OR 
				leave_requests.status LIKE ? OR 
				employee_details.id LIKE ? OR 
				employee_details.portfolio_id LIKE ? OR 
				employee_details.basic_pay LIKE ? OR 
				employee_details.bank_acc_no LIKE ? OR 
				employee_details.bank_name LIKE ? OR 
				employee_details.email LIKE ? OR 
				employee_details.dob LIKE ? OR 
				employee_details.date_of_join LIKE ? OR 
				employee_details.date_of_retirement LIKE ? OR 
				employee_details.department LIKE ? OR 
				employee_details.IFSC LIKE ? OR 
				employee_details.designation LIKE ? OR 
				employee_details.caste LIKE ? OR 
				employee_details.relegion LIKE ? OR 
				employee_details.nominee LIKE ? OR 
				employee_details.payscale LIKE ? OR 
				employee_details.gender LIKE ? OR 
				employee_details.identification_mark LIKE ? OR 
				employee_details.place_of_birth LIKE ? OR 
				employee_details.balanceleave LIKE ? OR 
				employee_details.mobile LIKE ? OR 
				employee_details.photo LIKE ? OR 
				employee_details.handicapped LIKE ? OR 
				employee_details.blind LIKE ? OR 
				employee_details.employee_type LIKE ? OR 
				employee_details.service_book LIKE ? OR 
				employee_details.service_book_remark LIKE ? OR 
				leave_requests.entry_by LIKE ?
			)";
			$search_params = array(
				"%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%"
			);
			//setting search conditions
			$db->where($search_condition, $search_params);
			 //template to use when ajax search
			$this->view->search_template = "leave_requests/search.php";
		}
		$db->join("employee_details", "leave_requests.emp_id = employee_details.id", "LEFT");
		if(!empty($request->orderby)){
			$orderby = $request->orderby;
			$ordertype = (!empty($request->ordertype) ? $request->ordertype : ORDER_TYPE);
			$db->orderBy($orderby, $ordertype);
		}
		else{
			$db->orderBy("leave_requests.id", ORDER_TYPE);
		}
		if($fieldname){
			$db->where($fieldname , $fieldvalue); //filter by a single field name
		}
		$tc = $db->withTotalCount();
		$records = $db->get($tablename, $pagination, $fields);
		$records_count = count($records);
		$total_records = intval($tc->totalCount);
		$page_limit = $pagination[1];
		$total_pages = ceil($total_records / $page_limit);
		$data = new stdClass;
		$data->records = $records;
		$data->record_count = $records_count;
		$data->total_records = $total_records;
		$data->total_page = $total_pages;
		if($db->getLastError()){
			$this->set_page_error();
		}
		$page_title = $this->view->page_title = "Leave Requests";
		$this->view->report_filename = date('Y-m-d') . '-' . $page_title;
		$this->view->report_title = $page_title;
		$this->view->report_layout = "report_layout.php";
		$this->view->report_paper_size = "A4";
		$this->view->report_orientation = "portrait";
		$this->render_view("leave_requests/adminlist.php", $data); //render the full page
	}
	/**
     * List page records
     * @param $fieldname (filter record by a field) 
     * @param $fieldvalue (filter field value)
     * @return BaseView
     */
	function leavecertificate($fieldname = null , $fieldvalue = null){
		$request = $this->request;
		$db = $this->GetModel();
		$tablename = $this->tablename;
		$fields = array("id", 
			"emp_id", 
			"status", 
			"reason", 
			"files", 
			"actual_leave_days", 
			"holiday_dates", 
			"holiday_type", 
			"entry_by");
		$pagination = $this->get_pagination(MAX_RECORD_COUNT); // get current pagination e.g array(page_number, page_limit)
		//search table record
		if(!empty($request->search)){
			$text = trim($request->search); 
			$search_condition = "(
				leave_requests.id LIKE ? OR 
				leave_requests.emp_id LIKE ? OR 
				leave_requests.status LIKE ? OR 
				leave_requests.reason LIKE ? OR 
				leave_requests.files LIKE ? OR 
				leave_requests.actual_leave_days LIKE ? OR 
				leave_requests.holiday_dates LIKE ? OR 
				leave_requests.holiday_type LIKE ? OR 
				leave_requests.entry_by LIKE ?
			)";
			$search_params = array(
				"%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%"
			);
			//setting search conditions
			$db->where($search_condition, $search_params);
			 //template to use when ajax search
			$this->view->search_template = "leave_requests/search.php";
		}
		if(!empty($request->orderby)){
			$orderby = $request->orderby;
			$ordertype = (!empty($request->ordertype) ? $request->ordertype : ORDER_TYPE);
			$db->orderBy($orderby, $ordertype);
		}
		else{
			$db->orderBy("leave_requests.id", ORDER_TYPE);
		}
		if($fieldname){
			$db->where($fieldname , $fieldvalue); //filter by a single field name
		}
		$tc = $db->withTotalCount();
		$records = $db->get($tablename, $pagination, $fields);
		$records_count = count($records);
		$total_records = intval($tc->totalCount);
		$page_limit = $pagination[1];
		$total_pages = ceil($total_records / $page_limit);
		$data = new stdClass;
		$data->records = $records;
		$data->record_count = $records_count;
		$data->total_records = $total_records;
		$data->total_page = $total_pages;
		if($db->getLastError()){
			$this->set_page_error();
		}
		$page_title = $this->view->page_title = "Leave Requests";
		$this->view->report_filename = date('Y-m-d') . '-' . $page_title;
		$this->view->report_title = $page_title;
		$this->view->report_layout = "report_layout.php";
		$this->view->report_paper_size = "A4";
		$this->view->report_orientation = "portrait";
		$this->render_view("leave_requests/leavecertificate.php", $data); //render the full page
	}
	/**
     * Insert new record to the database table
	 * @param $formdata array() from $_POST
     * @return BaseView
     */
	function ad2emp($formdata = null){
		if($formdata){
			$db = $this->GetModel();
			$tablename = $this->tablename;
			$request = $this->request;
			//fillable fields
			$fields = $this->fields = array("emp_id","status","reason","files","actual_leave_days","holiday_dates","holiday_type","entry_by");
			$postdata = $this->format_request_data($formdata);
			$this->rules_array = array(
				'emp_id' => 'required',
				'status' => 'required',
				'reason' => 'required',
				'holiday_dates' => 'required',
				'holiday_type' => 'required',
			);
			$this->sanitize_array = array(
				'emp_id' => 'sanitize_string',
				'status' => 'sanitize_string',
				'reason' => 'sanitize_string',
				'files' => 'sanitize_string',
				'holiday_dates' => 'sanitize_string',
				'holiday_type' => 'sanitize_string',
			);
			$this->filter_vals = true; //set whether to remove empty fields
			$modeldata = $this->modeldata = $this->validate_form($postdata);
			$modeldata['actual_leave_days'] = "0";
$modeldata['entry_by'] = USER_ID;
			if($this->validated()){
		# Statement to execute before adding record
error_reporting(0);
 $acc_holiday = 0;
$vals = explode("-to-",str_replace(" ","",$modeldata['holiday_dates']));
$from = $vals[0];
$to   = $vals[1];
$to = date('Y-m-d', strtotime($to . ' +1 day'));
$empid = $modeldata['emp_id'];
//execute sql statement and return a single field value
$params = array($empid);
$PID    = $db->rawQueryValue("SELECT portfolio_id FROM employee_details WHERE id = ? LIMIT 1", $params);
$params = array($empid);
$EMID   = $db->rawQueryValue("SELECT id FROM employee_details WHERE id = ? LIMIT 1", $params);
//GETHOLIDAY
$begin = new DateTime($from);
$end = new DateTime($to);
$interval = DateInterval::createFromDateString('1 day');
$period   = new DatePeriod($begin, $interval, $end);
foreach ($period as $dt) {
    unset($found);
    unset($reject);
    $date_to_chose = $dt->format("Y-m-d");
    //query the database and return the results
$fields = array('*');
$db->where("pid='$PID' OR pid=''");
$arr = $db->get("leave_holidays",99999999999, $fields);
foreach($arr as $k){
    $found  = 1;
    $cng_no = $k['no_week'];$cng_dayno=$k['day'];
$year  = date('Y',strtotime($date_to_chose));  // Change the value to use different Year
$month = date('m',strtotime($date_to_chose)); // Change the value to use different month
//$month=9;
$firstday = new  DateTime("$year-$month-1 0:0:0");
$first_w=$firstday->format('w'); // weekday of firstday 
$saturday1=new DateTime;
$my_number=$cng_dayno + ($cng_no-1)*7;
$saturday1->setDate($year,$month,$my_number-$first_w);
$date_c = $saturday1->format('Y-m-d');
if($date_c==$date_to_chose){
$reject = true;
}
}
if(!isset($found)){
$fields = array('*');
$db->where("pid", "");
$arr = $db->get("leave_holidays",999999, $fields);
foreach($arr as $k){
    $found  = 1;
    $cng_no = $k['no_week'];$cng_dayno=$k['day'];
$year  = date('Y',strtotime($date_to_chose));  // Change the value to use different Year
$month = date('m',strtotime($date_to_chose)); // Change the value to use different month
//$month=9;
$firstday = new  DateTime("$year-$month-1 0:0:0");
$first_w=$firstday->format('w'); // weekday of firstday 
$saturday1=new DateTime;
$saturday1->setDate($year,$month,($cng_no*$cng_dayno)-$first_w);
$date_c = $saturday1->format('d-m-Y');
if($date_c==$date_to_chose){
$reject = true;
}
}
}
//check holidays
//query the database and return the results
$fields = array('*');
$db->where("pid='$PID' OR pid=''");
$arr = $db->get("leave_calendar",999999, $fields);
// print_r($arr);
// exit();
foreach($arr as $k){
    if($k['date']==$date_to_chose){
        $reject = true;
    }
}
if(isset($reject)){
}
unset($reject);//=false; ///@@@@@@@@@@@@@@@@@@@@@@ FALSE @@@@@@@@@@@@
if(!isset($reject)){
    //execute sql statement and return a single field value
$params = array($modeldata['holiday_type'],$EMID );
$value  = $db->rawQueryValue("SELECT SUM(value) FROM leave_grant WHERE leave_type = ? and empid= ? LIMIT 1", $params);
if(round($value)<$acc_holiday+1){
    $reject=true;
    $rejectall=true;
}
    $acc_holiday  ++;
}
}
if(isset($rejectall)||$acc_holiday ==0){
    $this->set_flash_msg("Leaves Insufficient OR Check calendar if holiday", "danger");
    return  $this->redirect("leave_requests/adminlistadd");
        exit();
}else{
    $actmp=$acc_holiday;
    $db->where("empid", $EMID);
    $db->where("leave_type", $modeldata['holiday_type']);
    $arr = $db->get("leave_grant",99999999999,"*");
    foreach($arr as $k){
        if($actmp<>0){
            if($k['value']>=$actmp){
                $up=$k['value']-$actmp; 
                $params = array($k['id']);
                $bool = $db->rawQuery("UPDATE leave_grant SET value = $up , used=used+$actmp WHERE id = ?", $params); 
                $actmp=0;
            }
            if($k['value']<$actmp){
                $actmp=$actmp-$k['value'];
                $up=0; 
                $val=$k['value'];
                $params = array($k['id']);
                $bool = $db->rawQuery("UPDATE leave_grant SET value = 0 , used=used+$val WHERE id = ?", $params); 
            }
        }
    }
}
		# End of before add statement
				$rec_id = $this->rec_id = $db->insert($tablename, $modeldata);
				if($rec_id){
		# Statement to execute after adding record
		$params = array(USER_EMAIL);
  $empid = $db->rawQueryValue("SELECT id FROM employee_details WHERE email = ? LIMIT 1", $params);
$table_data = array(
    "actual_leave_days" => $acc_holiday 
);
$db->where("id", $rec_id);
$bool = $db->update("leave_requests", $table_data);
$seq = $db->getValue("autorization", "IFNULL(MAX(sequence),0)");
if($seq==0){
    $table_data = array(
    "status" => "LEAVE GRANTED AS PER PROTOCOL." 
    );
    $db->where("id", $rec_id);
    $bool = $db->update("leave_requests", $table_data);
}else{
    $db->where("sequence", 1);
    $app_user_id = $db->getValue("autorization", "user");
    $db->where("id", $modeldata['portfolio_id']);
    $profile = $db->getValue("portfolio", "name");
    $table_data = array(
    "rec_id" => $rec_id, 
    "app_userid" => "$app_user_id",
    "stage" => "1"
    );
    $db->insert("leave_requests_accept_reject", $table_data);
    $db->where("id", $app_user_id);
    $userfullname = $db->getValue("user_info", "user_full_name");
      $db->where("id", $app_user_id);
    $username = $db->getValue("user_info", "username");
    $table_data = array(
    "status" => "SENT TO $userfullname($username) FOR APPROVAL" 
    );
    $db->where("id", $rec_id);
    $bool = $db->update("leave_requests", $table_data);
}
		# End of after add statement
					$this->set_flash_msg("Record added successfully", "success");
					return	$this->redirect("leave_requests/adminlistadd");
				}
				else{
					$this->set_page_error();
				}
			}
		}
		$page_title = $this->view->page_title = "Add New Leave Requests";
		$this->render_view("leave_requests/ad2emp.php");
	}
	/**
     * List page records
     * @param $fieldname (filter record by a field) 
     * @param $fieldvalue (filter field value)
     * @return BaseView
     */
	function adminlistadd($fieldname = null , $fieldvalue = null){
		$request = $this->request;
		$db = $this->GetModel();
		$tablename = $this->tablename;
		$fields = array("leave_requests.id", 
			"employee_details.employee_name AS employee_details_employee_name", 
			"leave_requests.reason", 
			"leave_requests.files", 
			"leave_requests.actual_leave_days", 
			"leave_requests.holiday_dates", 
			"leave_requests.holiday_type", 
			"leave_requests.status", 
			"employee_details.department AS employee_details_department", 
			"employee_details.designation AS employee_details_designation");
		$pagination = $this->get_pagination(MAX_RECORD_COUNT); // get current pagination e.g array(page_number, page_limit)
		//search table record
		if(!empty($request->search)){
			$text = trim($request->search); 
			$search_condition = "(
				leave_requests.id LIKE ? OR 
				leave_requests.emp_id LIKE ? OR 
				employee_details.employee_name LIKE ? OR 
				leave_requests.reason LIKE ? OR 
				leave_requests.files LIKE ? OR 
				leave_requests.actual_leave_days LIKE ? OR 
				leave_requests.holiday_dates LIKE ? OR 
				leave_requests.holiday_type LIKE ? OR 
				leave_requests.status LIKE ? OR 
				leave_requests.entry_by LIKE ? OR 
				employee_details.id LIKE ? OR 
				employee_details.portfolio_id LIKE ? OR 
				employee_details.basic_pay LIKE ? OR 
				employee_details.bank_acc_no LIKE ? OR 
				employee_details.bank_name LIKE ? OR 
				employee_details.email LIKE ? OR 
				employee_details.dob LIKE ? OR 
				employee_details.date_of_join LIKE ? OR 
				employee_details.date_of_retirement LIKE ? OR 
				employee_details.department LIKE ? OR 
				employee_details.IFSC LIKE ? OR 
				employee_details.designation LIKE ? OR 
				employee_details.caste LIKE ? OR 
				employee_details.relegion LIKE ? OR 
				employee_details.nominee LIKE ? OR 
				employee_details.payscale LIKE ? OR 
				employee_details.gender LIKE ? OR 
				employee_details.identification_mark LIKE ? OR 
				employee_details.place_of_birth LIKE ? OR 
				employee_details.balanceleave LIKE ? OR 
				employee_details.mobile LIKE ? OR 
				employee_details.photo LIKE ? OR 
				employee_details.handicapped LIKE ? OR 
				employee_details.blind LIKE ? OR 
				employee_details.employee_type LIKE ? OR 
				employee_details.service_book LIKE ? OR 
				employee_details.service_book_remark LIKE ?
			)";
			$search_params = array(
				"%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%","%$text%"
			);
			//setting search conditions
			$db->where($search_condition, $search_params);
			 //template to use when ajax search
			$this->view->search_template = "leave_requests/search.php";
		}
		$db->join("employee_details", "leave_requests.emp_id = employee_details.id", "LEFT");
		if(!empty($request->orderby)){
			$orderby = $request->orderby;
			$ordertype = (!empty($request->ordertype) ? $request->ordertype : ORDER_TYPE);
			$db->orderBy($orderby, $ordertype);
		}
		else{
			$db->orderBy("leave_requests.id", ORDER_TYPE);
		}
		$db->where("entry_by='".USER_ID."'");
		if($fieldname){
			$db->where($fieldname , $fieldvalue); //filter by a single field name
		}
		$tc = $db->withTotalCount();
		$records = $db->get($tablename, $pagination, $fields);
		$records_count = count($records);
		$total_records = intval($tc->totalCount);
		$page_limit = $pagination[1];
		$total_pages = ceil($total_records / $page_limit);
		$data = new stdClass;
		$data->records = $records;
		$data->record_count = $records_count;
		$data->total_records = $total_records;
		$data->total_page = $total_pages;
		if($db->getLastError()){
			$this->set_page_error();
		}
		$page_title = $this->view->page_title = "Leave Requests";
		$this->view->report_filename = date('Y-m-d') . '-' . $page_title;
		$this->view->report_title = $page_title;
		$this->view->report_layout = "report_layout.php";
		$this->view->report_paper_size = "A4";
		$this->view->report_orientation = "portrait";
		$this->render_view("leave_requests/adminlistadd.php", $data); //render the full page
	}
}
