<?php
class Attendance{
	private $conn;
	private $table_name = "attendance";

	public $id;
	public $staff_id;
	public $att_date;
	public $duty_in;
	public $duty_in_photo;
	public $duty_out;
	public $duty_out_photo;
	public $second_duty_in;
	public $second_duty_in_photo;
	public $second_duty_out;
	public $second_duty_out_photo;
	public $entry_date_time;

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

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

		$stmt->bindParam(":staff_id", $this->staff_id);
		$stmt->bindParam(":att_date", $this->att_date);
		$stmt->bindParam(":duty_in", $this->duty_in);
		$stmt->bindParam(":duty_in_photo", $this->duty_in_photo);

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

	function createMiss(){
		$query = "INSERT INTO " . $this->table_name . " SET att_date=:att_date, staff_id=:staff_id, duty_in=:duty_in, duty_out=:duty_out, second_duty_in=:second_duty_in, second_duty_out=:second_duty_out, entry_date_time=:entry_date_time";
		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":att_date", $this->att_date);
		$stmt->bindParam(":staff_id", $this->staff_id);
		$stmt->bindParam(":duty_in", $this->duty_in);
		$stmt->bindParam(":duty_out", $this->duty_out);
		$stmt->bindParam(":second_duty_in", $this->second_duty_in);
		$stmt->bindParam(":second_duty_out", $this->second_duty_out);
		$stmt->bindParam(":entry_date_time", $this->entry_date_time);

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

	function updateMiss(){
		$query = "UPDATE " . $this->table_name . " SET duty_in=:duty_in, duty_out=:duty_out, second_duty_in=:second_duty_in, second_duty_out=:second_duty_out, entry_date_time=:entry_date_time where id=:id";
		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":id", $this->id);
		$stmt->bindParam(":duty_in", $this->duty_in);
		$stmt->bindParam(":duty_out", $this->duty_out);
		$stmt->bindParam(":second_duty_in", $this->second_duty_in);
		$stmt->bindParam(":second_duty_out", $this->second_duty_out);
		$stmt->bindParam(":entry_date_time", $this->entry_date_time);

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

	function exist(){
		$query = "SELECT id FROM " . $this->table_name . " WHERE  att_date= ? and staff_id=? LIMIT 0,1";
		$stmt = $this->conn->prepare( $query );	
	 
		$stmt->bindParam(1, $this->att_date);
		$stmt->bindParam(2, $this->staff_id);
		
		$stmt->execute();
		if($stmt->rowCount()>0){
			return true;
		}
		return false;
	}

	function updateDutyOut(){
		$query = "UPDATE " . $this->table_name . " SET duty_out=:duty_out, duty_out_photo=:duty_out_photo WHERE staff_id=:staff_id AND att_date=:att_date";
		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":staff_id", $this->staff_id);
		$stmt->bindParam(":att_date", $this->att_date);
		$stmt->bindParam(":duty_out", $this->duty_out);
		$stmt->bindParam(":duty_out_photo", $this->duty_out_photo);

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

	function updateSecondDutyIn(){
		$query = "UPDATE " . $this->table_name . " SET second_duty_in=:second_duty_in, second_duty_in_photo=:second_duty_in_photo WHERE staff_id=:staff_id AND att_date=:att_date";
		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":staff_id", $this->staff_id);
		$stmt->bindParam(":att_date", $this->att_date);
		$stmt->bindParam(":second_duty_in", $this->second_duty_in);
		$stmt->bindParam(":second_duty_in_photo", $this->second_duty_in_photo);

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

	function updateSecondDutyOut(){
		$query = "UPDATE " . $this->table_name . " SET second_duty_out=:second_duty_out, second_duty_out_photo=:second_duty_out_photo WHERE staff_id=:staff_id AND att_date=:att_date";
		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":staff_id", $this->staff_id);
		$stmt->bindParam(":att_date", $this->att_date);
		$stmt->bindParam(":second_duty_out", $this->second_duty_out);
		$stmt->bindParam(":second_duty_out_photo", $this->second_duty_out_photo);

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

	function search(){
        $condition = "";
        
        // if($this->df){
        //     if($condition!=""){
        //         $condition .= " AND ";
        //     }
        //     $condition .= " att_date >= :df";
        // }

        // if($this->dt){
        //     if($condition!=""){
        //         $condition .= " AND ";
        //     }
        //     $condition .= " att_date <= :dt";
        // }

        // if($this->staff_id){
        //     if($condition!=""){
        //         $condition .= " AND ";
        //     }
        //     $condition .= " staff_id = :staff_id";
        // }

        // if($condition!=""){
        //     $condition = " WHERE " . $condition;
        // }

        $query = "SELECT a.*,name,ds.duty_in AS schedule_in,ds.duty_out AS schedule_out,
		CASE WHEN off_day = 1 THEN ' (Off Day) ' ELSE '' END as off_day,
		aa.duty_in,aa.duty_in_photo,aa.duty_out,aa.duty_out_photo,
		aa.second_duty_in,aa.second_duty_in_photo,aa.second_duty_out,aa.second_duty_out_photo
		FROM (WITH RECURSIVE date_range AS (
		SELECT DATE(:df) AS att_date, :staff_id AS staff_id
		UNION ALL
		SELECT att_date + INTERVAL 1 DAY, :staff_id AS staff_id
		FROM date_range
		WHERE att_date < DATE(:dt)
		)
		SELECT att_date, staff_id
		FROM date_range) AS a
		LEFT JOIN staff ON a.staff_id=staff.id
		LEFT JOIN duty_schedule ds ON a.att_date=ds.date and a.staff_id=ds.staff_id
		LEFT JOIN attendance aa ON a.att_date=aa.att_date AND a.staff_id=aa.staff_id
		";
        $stmt = $this->conn->prepare($query);

        $stmt->bindParam(":df", $this->df);
        $stmt->bindParam(":dt", $this->dt);
        $stmt->bindParam(":staff_id", $this->staff_id);

        $stmt->execute();
        return $stmt;
    }

    // function getAttendanceByStaff(){
    //     $query = "SELECT att_date, duty_in, duty_out, if(duty_out='', 0, total_min) AS total_min FROM (SELECT *, TIMESTAMPDIFF(MINUTE, CONCAT('2023-11-01 ', IF(duty_in<'07:00', '07:00', duty_in)), CONCAT('2023-11-01 ', IF(duty_out>'17:30', '17:30', duty_out))) AS total_min FROM attendance WHERE att_date>=:df AND att_date<=:dt AND staff_id=:staff_id) AS att ORDER BY att_date";
    //     $stmt = $this->conn->prepare($query);

    //     $stmt->bindParam(":df", $this->df);
    //     $stmt->bindParam(":dt", $this->dt);
    //     $stmt->bindParam(":staff_id", $this->staff_id);

    //     $stmt->execute();
    //     return $stmt;
    // }

	function getAttendanceByStaff(){
        $query = "select *,first_min+second_min as total_min from (SELECT 
			att_date, 
			duty_in, 
			duty_out,
			second_duty_in, 
			second_duty_out, 
			if(duty_out='', 0, first_min) AS first_min,
			if(second_duty_out='', 0, second_min) AS second_min

		FROM 
			(SELECT 
				att_date, 
				duty_in, 
				duty_out, 
				second_duty_in, 
				second_duty_out, 
				TIMESTAMPDIFF(
					MINUTE, 
					CONCAT('2024-09-19 ', duty_in), 
					CONCAT('2024-09-19 ', duty_out)
				) AS first_min, 
				TIMESTAMPDIFF(
					MINUTE, 
					CONCAT('2024-09-19 ', second_duty_in), 
					CONCAT('2024-09-19 ', second_duty_out)
				) AS second_min 
			FROM 
				attendance 
			WHERE 
				att_date >= :df 
				AND att_date <= :dt 
				AND staff_id = :staff_id
			) AS att 
		ORDER BY 
			att_date) as a
		";
        $stmt = $this->conn->prepare($query);

        $stmt->bindParam(":df", $this->df);
        $stmt->bindParam(":dt", $this->dt);
        $stmt->bindParam(":staff_id", $this->staff_id);

        $stmt->execute();
        return $stmt;
    }

	function getInOut(){
		$query = "SELECT * FROM attendance WHERE att_date=:date AND staff_id=:staff_id ";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":date", $this->date);
        $stmt->bindParam(":staff_id", $this->staff_id);
		$stmt->execute();
		if($stmt->rowCount()>0){
			$row = $stmt->fetch(PDO::FETCH_ASSOC);
			$this->duty_in = $row['duty_in'];
			$this->duty_out = $row['duty_out'];
			$this->second_duty_in = $row['second_duty_in'];
			$this->second_duty_out = $row['second_duty_out'];

		}
	}

	function getOneAttendance(){
		$query = "SELECT * FROM attendance WHERE att_date=:date AND staff_id=:staff_id ";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":date", $this->date);
        $stmt->bindParam(":staff_id", $this->staff_id);
		$stmt->execute();
		if($stmt->rowCount()>0){
			$row = $stmt->fetch(PDO::FETCH_ASSOC);
			$this->id = $row['id'];
			$this->duty_in = $row['duty_in'];
			$this->duty_out = $row['duty_out'];
			$this->second_duty_in = $row['second_duty_in'];
			$this->second_duty_out = $row['second_duty_out'];


		}
	}

    function getSummary(){
		$query = "SELECT staff.id, staff.name, SUM(TIMESTAMPDIFF(MINUTE, CONCAT('2023-11-01 ', IF(duty_in<'07:00', '07:00', duty_in)), CONCAT('2023-11-01 ', IF(duty_out>'17:30', '17:30', duty_out)))) AS att_min, IFNULL(ot_min, 0) AS ot_min
FROM staff LEFT JOIN attendance ON staff.id=attendance.staff_id
LEFT JOIN (SELECT staff_id, SUM(ot_min) AS ot_min
FROM OT WHERE staff_id=:staff_id AND `date`>=:df AND `date`<=:dt) AS ot_tbl ON staff.id=ot_tbl.staff_id
WHERE staff.id=:staff_id AND att_date>=:df AND att_date<=:dt AND duty_in<>'' AND duty_out<>''";
		$stmt = $this->conn->prepare( $query );
	 
		$stmt->bindParam(":staff_id", $this->staff_id);
		$stmt->bindParam(":df", $this->df);
		$stmt->bindParam(":dt", $this->dt);
		
		$stmt->execute();
		if($stmt->rowCount()>0){
			$row = $stmt->fetch(PDO::FETCH_ASSOC);
			extract($row);
			$this->att_min = $att_min;	
			$this->ot_min = $ot_min;
		}else{
			$this->att_min = 0;	
			$this->ot_min = 0;
		}
		return true;
	}

	function getAllAttendanceByMonth(){
		$query = "SELECT staff.id, staff.name, IFNULL(att_min, 0) AS att_min, IFNULL(ot_min, 0) AS ot_min FROM staff LEFT JOIN (SELECT staff.id, staff.name, SUM(TIMESTAMPDIFF(MINUTE, CONCAT('2023-11-01 ', IF(duty_in<'07:00', '07:00', duty_in)), CONCAT('2023-11-01 ', IF(duty_out>'17:30', '17:30', duty_out)))) AS att_min, IFNULL(ot_min, 0) AS ot_min
FROM staff LEFT JOIN attendance ON staff.id=attendance.staff_id
LEFT JOIN (SELECT staff_id, SUM(ot_min) AS ot_min
FROM OT WHERE `date`>=:df AND `date`<=:dt GROUP BY staff_id) AS ot_tbl ON staff.id=ot_tbl.staff_id
WHERE att_date>=:df AND att_date<=:dt AND duty_in<>'' AND duty_out<>'' GROUP BY staff.id) AS att ON staff.id=att.id";
		$stmt = $this->conn->prepare( $query );
	 
		$stmt->bindParam(":df", $this->df);
		$stmt->bindParam(":dt", $this->dt);
		
		$stmt->execute();
        return $stmt;
	}
}
?>