<?php
class Purchase{

    // database connection and table name
	private $conn;
	private $table_name = "purchase";

	public $id;
	public $date;
	public $voucher_no;
	public $supplier;
	public $entry_by;
	public $is_deleted;
	
	public function __construct($db){
		$this->conn = $db;
	}
	
	function create(){
		$query = "INSERT INTO " . $this->table_name . " SET date=:date, voucher_no=:voucher_no, supplier=:supplier, entry_by=:entry_by";
		$stmt = $this->conn->prepare($query);

		$this->date=htmlspecialchars(strip_tags($this->date));
		$this->voucher_no=htmlspecialchars(strip_tags($this->voucher_no));
		$this->supplier=htmlspecialchars(strip_tags($this->supplier));

		$stmt->bindParam(":date", $this->date);
		$stmt->bindParam(":voucher_no", $this->voucher_no);
		$stmt->bindParam(":supplier", $this->supplier);
		$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 purchase_by=:purchase_by, date=:date, shop=:shop, voucher_no=:voucher_no where id=:id";
	// 	$stmt = $this->conn->prepare($query);

	// 	$this->id=htmlspecialchars(strip_tags($this->id));
	// 	$this->purchase_by=htmlspecialchars(strip_tags($this->purchase_by));
	// 	$this->date=htmlspecialchars(strip_tags($this->date));
	// 	$this->shop=htmlspecialchars(strip_tags($this->shop));
	// 	$this->voucher_no=htmlspecialchars(strip_tags($this->voucher_no));

	// 	$stmt->bindParam(":id", $this->id);
	// 	$stmt->bindParam(":purchase_by", $this->purchase_by);
	// 	$stmt->bindParam(":date", $this->date);
	// 	$stmt->bindParam(":shop", $this->shop);
	// 	$stmt->bindParam(":voucher_no", $this->voucher_no);

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

	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;
	}
	
	// function getOnePurchase(){
	// 	$query = "SELECT * FROM " . $this->table_name . " WHERE	id = ? LIMIT 0,1";

	// 	$stmt = $this->conn->prepare( $query );
	// 	$stmt->bindParam(1, $this->id);
	// 	$stmt->execute();
	// 	if($stmt->rowCount()>0){
	// 		$row = $stmt->fetch(PDO::FETCH_ASSOC);
	// 		$this->id = $row['id'];
	// 		$this->purchase_by = $row['purchase_by'];
	// 		$this->date = $row['date'];	
	// 		$this->shop = $row['shop'];
	// 		$this->voucher_no = $row['voucher_no'];
	// 	}
	// }

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

	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->purchase_by){
		// 	if($condition!=""){ $condition .= " AND "; }
		// 	$condition .= " (purchase_by LIKE  :purchase_by '%' or purchase_by LIKE '%' :purchase_by '%' or purchase_by Like '%' :purchase_by ) ";
		// }
		// if($this->shop){
		// 	if($condition!=""){ $condition .= " AND "; }
		// 	$condition .= " (shop LIKE  :shop '%' or shop LIKE '%' :shop '%' or shop Like '%' :shop ) ";
		// }
		// if($this->voucher_no){
		// 	if($condition!=""){ $condition .= " AND "; }
		// 	$condition .= " (voucher_no LIKE  :voucher_no '%' or voucher_no LIKE '%' :voucher_no '%' or voucher_no Like '%' :voucher_no ) ";
		// }
		// if($condition!=""){
		// 	$condition = " WHERE " . $condition;
		// }
		$query = "select purchase.*, sum(quantity*price) as amount from purchase_detail join purchase on purchase.id=purchase_detail.purchase_id where purchase.is_deleted is null and purchase_detail.is_deleted is null " . $condition . " group by purchase_detail.purchase_id order by date";
		$stmt = $this->conn->prepare($query);
		$this->df=htmlspecialchars(strip_tags($this->df));
		$this->dt=htmlspecialchars(strip_tags($this->dt));
		// $this->purchase_by=htmlspecialchars(strip_tags($this->purchase_by));
		// $this->shop=htmlspecialchars(strip_tags($this->shop));
		// $this->voucher_no=htmlspecialchars(strip_tags($this->voucher_no));
		if($this->df) $stmt->bindParam(":df", $this->df);
		if($this->dt) $stmt->bindParam(":dt", $this->dt);
		// if($this->purchase_by) $stmt->bindParam(":purchase_by", $this->purchase_by);
		// if($this->shop) $stmt->bindParam(":shop", $this->shop);
		// if($this->voucher_no) $stmt->bindParam(":voucher_no", $this->voucher_no);
		$stmt->execute();
		return $stmt;
	}

	// function getPurchaseByDate(){
	// 	$condition = "";

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

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

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

	// 	$query = "SELECT pur.id, pur.purchase_by, pur.date, pur.shop, pur.voucher_no, pur.amount, payment.purchase_id, payment.expense_id, payment.supplier, payment.entry_date_time, (pur.amount - COALESCE(payment.amount,0)) as balance from (SELECT purchase.*, SUM(price) AS amount FROM purchase JOIN purchase_detail ON purchase.id=purchase_detail.purchase_id " . $condition . " GROUP BY purchase.id) as pur left join payment on pur.id=payment.purchase_id group by payment.purchase_id";
	// 	// $query = "select purchase.*, sum(price) as amount from purchase join purchase_detail on purchase.id=purchase_detail.purchase_id " . $condition . " group by purchase.id";
	// 	$stmt = $this->conn->prepare($query);

	// 	$this->date=htmlspecialchars(strip_tags($this->date));
	// 	$this->voucher_no=htmlspecialchars(strip_tags($this->voucher_no));

	// 	if($this->date) $stmt->bindParam(":date", $this->date);
	// 	if($this->voucher_no) $stmt->bindParam(":voucher_no", $this->voucher_no);

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

	// function getPurchaseBySupplier(){
	// 	$condition = "";

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

	// 	if($condition!=""){
	// 		$condition = " AND " . $condition;
	// 	}
		
	// 	$query = "select mm.*, COALESCE(sum(payment.amount),0) as payment, mm.amount - COALESCE(sum(payment.amount),0) as balance from (select purchase.*, sum(price) as amount from purchase_detail join purchase on purchase.id=purchase_detail.purchase_id WHERE shop<>'' and shop=:shop group by purchase.shop) as mm left join payment on mm.shop=payment.supplier group by payment.supplier";
	// 	$stmt = $this->conn->prepare($query);

	// 	$this->shop=htmlspecialchars(strip_tags($this->shop));

	// 	if($this->shop) $stmt->bindParam(":shop", $this->shop);

	// 	$stmt->execute();
	// 	if($stmt->rowCount()>0){
	// 		$row = $stmt->fetch(PDO::FETCH_ASSOC);
	// 		$this->id = $row['id'];
	// 		$this->shop = $row['shop'];
	// 		$this->amount = $row['amount'];
	// 		$this->payment = $row['payment'];
	// 		$this->balance = $row['balance'];
	// 	}
	// }

	// function searchByPurchaseDetail(){
	// 	$condition = "";

	// 	if($this->df){
	// 		if($condition!=""){
	// 			$condition .= " AND ";
	// 		}
	// 		$condition .= " date >=:df ";
	// 	}
	// 	if($this->dt){
	// 		if($condition!=""){
	// 			$condition .= " AND ";
	// 		}
	// 		$condition .= " date <=:dt ";
	// 	}
	// 	if($this->purchase_by){
	// 		if($condition!=""){ $condition .= " AND "; }
	// 		$condition .= " (purchase_by LIKE  :purchase_by '%' or purchase_by LIKE '%' :purchase_by '%' or purchase_by Like '%' :purchase_by ) ";
	// 	}
	// 	if($this->shop){
	// 		if($condition!=""){ $condition .= " AND "; }
	// 		$condition .= " (shop LIKE  :shop '%' or shop LIKE '%' :shop '%' or shop Like '%' :shop ) ";
	// 	}
	// 	if($this->voucher_no){
	// 		if($condition!=""){ $condition .= " AND "; }
	// 		$condition .= " (voucher_no LIKE  :voucher_no '%' or voucher_no LIKE '%' :voucher_no '%' or voucher_no Like '%' :voucher_no ) ";
	// 	}
	// 	if($condition!=""){
	// 		$condition = " WHERE " . $condition;
	// 	}
	// 	$query = "select purchase.purchase_by, purchase.date, purchase.shop, purchase.voucher_no, purchase_detail.* from purchase join purchase_detail on purchase.id=purchase_detail.purchase_id " . $condition . " order by date, voucher_no";
	// 	$stmt = $this->conn->prepare($query);
	// 	$this->df=htmlspecialchars(strip_tags($this->df));
	// 	$this->dt=htmlspecialchars(strip_tags($this->dt));
	// 	$this->purchase_by=htmlspecialchars(strip_tags($this->purchase_by));
	// 	$this->shop=htmlspecialchars(strip_tags($this->shop));
	// 	$this->voucher_no=htmlspecialchars(strip_tags($this->voucher_no));
	// 	if($this->df) $stmt->bindParam(":df", $this->df);
	// 	if($this->dt) $stmt->bindParam(":dt", $this->dt);
	// 	if($this->purchase_by) $stmt->bindParam(":purchase_by", $this->purchase_by);
	// 	if($this->shop) $stmt->bindParam(":shop", $this->shop);
	// 	if($this->voucher_no) $stmt->bindParam(":voucher_no", $this->voucher_no);
	// 	$stmt->execute();
	// 	return $stmt;
	// }
}
