<?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 $use;
    public $lost;
	public $damage;
    public $expire;

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

	function createSB(){
		$query = "INSERT INTO " . $this->table_name . " SET stock_balance_date=:stock_balance_date, store=:store, item_code=:item_code, item_name=:item_name, total_stock=:quantity";
		$stmt = $this->conn->prepare($query);

        $stmt->bindParam(":stock_balance_date", $this->stock_balance_date);
        $stmt->bindParam(":store", $this->store);
        $stmt->bindParam(":item_code", $this->item_code);
        $stmt->bindParam(":item_name", $this->item_name);
		$stmt->bindParam(":quantity", $this->quantity);
	 
		$stmt->execute();
		if($stmt->rowCount()>0){
			return true;
		}
		return false;
	}

	function checkMaterial(){
		$query = "SELECT * FROM " . $this->table_name . " WHERE store=:store AND item_code=:item_code";
		$stmt = $this->conn->prepare($query);

		$this->store=htmlspecialchars(strip_tags($this->store));
        $this->item_code=htmlspecialchars(strip_tags($this->item_code));
		$stmt->bindParam(":store", $this->store);
        $stmt->bindParam(":item_code", $this->item_code);
	 
		$stmt->execute();
		if($stmt->rowCount()>0){
			return true;
		}
		return false;

	}



	function getAllRows(){

		$condition = "";
		
		if($this->store){
			if($condition!=""){ $condition .= " AND "; } $condition .= " stock_balance.store =:store ";
		}	
		if($condition!=""){
			$condition = " WHERE " . $condition;
		}

		$query = "SELECT stock_balance.store,(total_stock+ifnull(in_qty,0)-ifnull(out_qty,0)) as quantity,item.* FROM stock_balance 
		left join (select item_code,sum(quantity) as in_qty,store from stock_in where stock_in_date <=:stock_balance_date group by item_code,store) as s_in on stock_balance.item_code=s_in.item_code and stock_balance.store=s_in.store
		left join (select item_code,sum(quantity) as out_qty,store from stock_out where stock_out_date <=:stock_balance_date group by item_code,store) as s_out on stock_balance.item_code=s_out.item_code and stock_balance.store=s_out.store
		LEFT JOIN item ON stock_balance.item_code=item.item_code " . $condition ." ORDER BY stock_balance.store,item.item_name
		";
		$stmt = $this->conn->prepare($query);

		$this->store=htmlspecialchars(strip_tags($this->store));
		$stmt->bindParam(":stock_balance_date", $this->stock_balance_date);
		if($this->store) $stmt->bindParam(":store", $this->store);
	    $stmt->execute();
		return $stmt;

	}

	function search(){

		$condition = "";
		$store_query = "SELECT name AS store FROM city";
		
		if($this->store){
			$store_query = "SELECT :store AS store";
		}	

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

		if($this->item_name){
			if($condition!=""){ $condition .= " AND "; } $condition .= " (item.item_name LIKE  :item_name '%' or item.item_name LIKE '%' :item_name '%' or item.item_name Like '%' :item_name ) ";
		}	

		if($this->chk_qty==1){
			if($this->sign){
				if($condition!=""){
					$condition .= " AND ";
				}
				if($this->sign =="between"){	
					$condition .= " ((total_stock+ifnull(in_qty,0)-ifnull(out_qty,0)+ifnull(tin_qty,0)-ifnull(tout_qty,0)-ifnull(install_qty,0)-ifnull(isqty,0)) between :qtyfrom and :qtyto) ";
				}	
				else if($this->sign =="<"){
					$condition .= " ((total_stock+ifnull(in_qty,0)-ifnull(out_qty,0)+ifnull(tin_qty,0)-ifnull(tout_qty,0)-ifnull(install_qty,0)-ifnull(isqty,0)) < :qtyfrom) ";
				}
				else if($this->sign ==">"){
					$condition .= " ((total_stock+ifnull(in_qty,0)-ifnull(out_qty,0)+ifnull(tin_qty,0)-ifnull(tout_qty,0)-ifnull(install_qty,0)-ifnull(isqty,0)) > :qtyfrom) ";
				}
				else{
					$condition .= " ((total_stock+ifnull(in_qty,0)-ifnull(out_qty,0)+ifnull(tin_qty,0)-ifnull(tout_qty,0)-ifnull(install_qty,0)-ifnull(isqty,0)) = :qtyfrom) ";
				}

			}
		}

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


		$query = "SELECT stock_balance.store,(total_stock+ifnull(in_qty,0)-ifnull(out_qty,0)+ifnull(tin_qty,0)-ifnull(tout_qty,0)-ifnull(install_qty,0)-ifnull(isqty,0)) as quantity,item.* FROM (SELECT store_source.store,item.item_code,item.item_name,0 AS total_stock FROM (" . $store_query . ") AS store_source CROSS JOIN item
		ORDER BY store_source.store,item.item_code) AS stock_balance 
		left join (select item_code,sum(quantity) as in_qty,store from stock_in where stock_in_date <=:stock_balance_date group by item_code,store) as s_in on stock_balance.item_code=s_in.item_code and stock_balance.store=s_in.store
		left join (select item_code,sum(quantity) as out_qty,store from stock_out where stock_out_date <=:stock_balance_date group by item_code,store) as s_out on stock_balance.item_code=s_out.item_code and stock_balance.store=s_out.store
		LEFT JOIN (select item_code,sum(quantity) as tin_qty,store_to as store from stock_transfer where stock_transfer_date <=:stock_balance_date group by item_code,store_to) AS t_in ON stock_balance.item_code=t_in.item_code and stock_balance.store=t_in.store
		LEFT JOIN (select item_code,sum(quantity) as tout_qty,store_from as store from stock_transfer where stock_transfer_date <=:stock_balance_date group by item_code,store_from) AS t_out ON stock_balance.item_code=t_out.item_code and stock_balance.store=t_out.store
		left join (select item_code,sum(quantity) as install_qty,city_ as store from installation_detail where install_date_ <=:stock_balance_date group by item_code,city_) as install_out on stock_balance.item_code=install_out.item_code and stock_balance.store=install_out.store
		left join (SELECT item_code,SUM(quantity) AS isqty,store FROM item_sales_detail where sales_date <=:stock_balance_date GROUP BY store,item_code) as item_sales on stock_balance.item_code=item_sales.item_code and stock_balance.store=item_sales.store
		LEFT JOIN item ON stock_balance.item_code=item.item_code " . $condition . " ORDER BY stock_balance.store,item.item_code
		
		";

		// $query = "SELECT stock_balance.store,(total_stock+ifnull(in_qty,0)-ifnull(out_qty,0)-ifnull(s_qty,0)) as quantity,item.* FROM stock_balance 
		// left join (select item_code,sum(quantity) as in_qty,store from stock_in where stock_in_date <=:stock_balance_date group by item_code,store) as s_in on stock_balance.item_code=s_in.item_code and stock_balance.store=s_in.store
		// left join (select item_code,sum(quantity) as out_qty,store from stock_out where stock_out_date <=:stock_balance_date group by item_code,store) as s_out on stock_balance.item_code=s_out.item_code and stock_balance.store=s_out.store
		// left join (select item_code,sum(quantity) as s_qty,store from sales_detail where sales_date <=:stock_balance_date group by item_code,store) as sales_out on stock_balance.item_code=sales_out.item_code and stock_balance.store=sales_out.store
		// LEFT JOIN item ON stock_balance.item_code=item.item_code " . $condition ." ORDER BY stock_balance.store,category,brand,item.item_code
		// ";
		$stmt = $this->conn->prepare($query);

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

		$stmt->bindParam(":stock_balance_date", $this->stock_balance_date);
		if($this->store) $stmt->bindParam(":store", $this->store);
		// if($this->category) $stmt->bindParam(":category", $this->category);
		// if($this->brand) $stmt->bindParam(":brand", $this->brand);
		if($this->item_code) $stmt->bindParam(":item_code", $this->item_code);
		if($this->item_name) $stmt->bindParam(":item_name", $this->item_name);
		if($this->chk_qty==1){
			$stmt->bindParam(":qtyfrom", $this->qtyfrom);
			if($this->sign == "between"){
				$stmt->bindParam(":qtyto", $this->qtyto);
			}
		} 
	    $stmt->execute();
		return $stmt;

	}
	



}
?>
