<?php
class DutySchedule{

	private $conn;
	private $table_name = "duty_schedule";

	public $id;	
	public $staff_id;	
	public $date;	
	public $duty_in;	
	public $duty_out;	
	public $off_day;	
	public $entry_by;
	public $entry_date_time;

	public function __construct($db){
		$this->conn = $db;
	}

	function create(){
		$query = "INSERT INTO " . $this->table_name . " SET staff_id=:staff_id, date=:date, duty_in=:duty_in, duty_out=:duty_out, off_day=:off_day, entry_by=:entry_by";
		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":staff_id", $this->staff_id);
		$stmt->bindParam(":date", $this->date);
		$stmt->bindParam(":duty_in", $this->duty_in);
		$stmt->bindParam(":duty_out", $this->duty_out);
		$stmt->bindParam(":off_day", $this->off_day);
		$stmt->bindParam(":entry_by", $this->entry_by);

		if($stmt->execute()){
			return true;
		}	 
		return false;
	}

	// function update(){
	// 	$query = "UPDATE " . $this->table_name . " SET type_of_expense=:type_of_expense, account_head=:account_head, debit=:debit, credit=:credit, entry_by=:entry_by, entry_date_time=:entry_date_time where id=:id";
	// 	$stmt = $this->conn->prepare($query);

	// 	$stmt->bindParam(":id", $this->id);
	// 	$stmt->bindParam(":type_of_expense", $this->type_of_expense);
	// 	$stmt->bindParam(":account_head", $this->account_head);
	// 	$stmt->bindParam(":debit", $this->debit);
	// 	$stmt->bindParam(":credit", $this->credit);
	// 	$stmt->bindParam(":entry_by", $this->entry_by);
	// 	$stmt->bindParam(":entry_date_time", $this->entry_date_time);
		
	// 	if($stmt->execute()){
	// 		$this->id = $this->conn->lastInsertId();
	// 		return true;
	// 	}
	// 	return false;
	// }

	// function exist(){
	// 	$query = "SELECT id FROM " . $this->table_name . " WHERE  type_of_expense= :type_of_expense and account_type= :account_type and account_head= :account_head LIMIT 0,1";
	// 	$stmt = $this->conn->prepare( $query );	
	 
	// 	$stmt->bindParam(":type_of_expense", $this->type_of_expense);
	// 	$stmt->bindParam(":account_type", $this->account_type);
	// 	$stmt->bindParam(":account_head", $this->account_head);
		
	// 	$stmt->execute();
	// 	if($stmt->rowCount()>0){
	// 		return true;
	// 	}
	// 	return false;
	// }

	function getAllRows(){
		$condition = "---";
		if($this->staff_id){
			$condition = "and staff_id=:staff_id";
		}

		$query = "WITH RECURSIVE dates AS (
		SELECT CONCAT(:year_month,'-','01') AS date
		UNION ALL
		SELECT DATE_ADD(date, INTERVAL 1 DAY)
		FROM dates
		WHERE date < LAST_DAY(CONCAT(:year_month,'-','01'))
	)
	SELECT 
		d.date,
		COALESCE(ds.duty_in, '') AS duty_in,
		COALESCE(ds.duty_out, '') AS duty_out,
		COALESCE(ds.off_day, '') AS off_day
	FROM 
		dates d
	LEFT JOIN 
		(select * from duty_schedule where substring(date,1,7)=:year_month " . $condition . ") ds 
	ON 
		d.date = ds.date AND ds.staff_id = :staff_id
	ORDER BY 
		d.date";
		$stmt = $this->conn->prepare($query);

		if($this->year_month) $stmt->bindParam(":year_month", $this->year_month);
		if($this->staff_id) $stmt->bindParam(":staff_id", $this->staff_id);
		$stmt->execute();
		return $stmt;
	}

    function delete(){
		$query = "DELETE FROM " . $this->table_name . " WHERE staff_id = :staff_id and substring(date,1,7)=:year_month";
		$stmt = $this->conn->prepare($query);
		$stmt->bindParam(":year_month", $this->year_month);
		$stmt->bindParam(":staff_id", $this->staff_id);
		if($stmt->execute()){
			return true;
		}
		return false;
    }

}
?>
