<?php
class StockBalance{
 
    // database connection and table name
    private $conn;
    private $table_name = "stock_balance";
 
	// object properties
    public $id;
    public $stock_balance_date;
    public $store;
	public $item_code;
    public $item_name;
	public $total_stock;
	
	public $date;
	
    public function __construct($db){
        $this->conn = $db;
	}


	function search(){

		$condition = "";
		if($this->store){
			$condition .= "and store =:store ";
		}

		$query = "SELECT store,category,type,item.item_code,item_name,total,capital_price FROM (
			SELECT store,item_code,SUM(qty) AS total FROM 
			(SELECT 
				item_code,
				store,
				SUM(quantity) AS qty
			FROM (SELECT pd.*,date FROM purchase_detail pd LEFT JOIN purchase p ON pd.purchase_id=p.id) AS ppd
			WHERE is_deleted is null and date <= :date " . $condition . " 
			GROUP BY item_code, store
			UNION ALL
			SELECT 
				item_code,
				store,
				SUM(quantity) AS qty
			FROM stock_in
			WHERE is_deleted is null and stock_in_date <= :date " . $condition . " 
			GROUP BY item_code, store
			UNION ALL
			SELECT 
				item_code,
				store,
				SUM(quantity*-1) AS qty
			FROM stock_out
			WHERE is_deleted is null and stock_out_date <= :date " . $condition . " 
			GROUP BY item_code, store
			) AS a GROUP BY item_code,store
			) AS b LEFT JOIN item ON b.item_code=item.item_code  order by b.store,item.item_code
		";
		$stmt = $this->conn->prepare($query);

		$this->store=htmlspecialchars(strip_tags($this->store));

		$stmt->bindParam(":date", $this->date);
		if($this->store) $stmt->bindParam(":store", $this->store);
	    $stmt->execute();
		return $stmt;

	}

}
?>
