<?php
    date_default_timezone_set('Asia/Bangkok');
class Expense{
    private $conn;
    private $table_name = "`expense`";
 
	public $id;
	public $date;
	public $voucher_no;
	public $type;
	public $account_code;
	public $account_head;
	public $particular;
	public $quantity;
	public $price;
	public $remark;
	public $entry_by;
	public $update_by;
	public $is_deleted;

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

	function create(){
		$query = "INSERT INTO " . $this->table_name . " SET date=:date, voucher_no=:voucher_no, `type`=:type, account_code=:account_code, account_head=:account_head, particular=:particular, quantity=:quantity, price=:price, remark=:remark,  entry_by=:entry_by";
		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":date", $this->date);
		$stmt->bindParam(":voucher_no", $this->voucher_no);
		$stmt->bindParam(":type", $this->type);
		$stmt->bindParam(":account_code", $this->account_code);
		$stmt->bindParam(":account_head", $this->account_head);
		$stmt->bindParam(":particular", $this->particular);
		$stmt->bindParam(":quantity", $this->quantity);
		$stmt->bindParam(":price", $this->price);
		$stmt->bindParam(":remark", $this->remark);
		$stmt->bindParam(":entry_by", $this->entry_by);

		if($stmt->execute()){
			$this->id = $this->conn->lastInsertId();
			return true;
		}
		return false;
	}

	function update(){
		$query = "UPDATE " . $this->table_name . " SET date=:date, voucher_no=:voucher_no,type=:type, account_code =:account_code, account_head=:account_head, particular=:particular, quantity=:quantity, price=:price, remark=:remark, update_by=:update_by where id=:id ";
		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":id", $this->id);
		$stmt->bindParam(":date", $this->date);
		$stmt->bindParam(":voucher_no", $this->voucher_no);
		$stmt->bindParam(":type", $this->type);
		$stmt->bindParam(":account_code", $this->account_code);
		$stmt->bindParam(":account_head", $this->account_head);
		$stmt->bindParam(":particular", $this->particular);
		$stmt->bindParam(":quantity", $this->quantity);
		$stmt->bindParam(":price", $this->price);
		$stmt->bindParam(":remark", $this->remark);
		$stmt->bindParam(":update_by", $this->update_by);
		
		if($stmt->execute()){
			$this->id = $this->conn->lastInsertId();
			return true;
		}
		return false;
	}

	function getAllRows(){
		$query = "SELECT expense.*,coa.account_head as ah from " . $this->table_name . " left join coa on expense.account_code=coa.account_code where date=:date and is_deleted is null ";
		$stmt = $this->conn->prepare( $query );
        $stmt->bindParam(":date", $this->date);
		$stmt->execute();
		return $stmt;
	}

	function search(){
		$condition = "";

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

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

		if($this->account_type){
			// if($condition!=""){
			// 	$condition .= " AND ";
			// }
			$condition .= " and expense.type=:account_type ";
		}

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

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

		$query = "SELECT expense.*,coa.account_head as ah from " . $this->table_name . " left join coa on expense.account_code=coa.account_code where is_deleted is null " . $condition . " order by date,account_code";
		$stmt = $this->conn->prepare($query);
		if($this->df) $stmt->bindParam(":df", $this->df);
		if($this->dt) $stmt->bindParam(":dt", $this->dt);
		if($this->account_type) $stmt->bindParam(":account_type", $this->account_type);
		if($this->account_code) $stmt->bindParam(":account_code", $this->account_code);

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

	function delete(){
		$query = "Update " . $this->table_name . " set is_deleted=:is_deleted WHERE id=:id";
		$stmt = $this->conn->prepare($query);
		$this->id=htmlspecialchars(strip_tags($this->id));
		$stmt->bindParam(":id", $this->id);
		$stmt->bindParam(":is_deleted", $this->is_deleted);
		if($stmt->execute()){
			return true;
		}
		return false;
	}

	
}
?>
