<?php
class Item{

	private $conn;
	private $table_name = "item";

	public $id;		
	public $category_code;
	public $category;
	public $type_code;
	public $type;
    public $item_code;
    public $item_name;
	public $barcode;
	public $capital_price;
	public $sales_price;
    public $supplier;
	public $entry_by;
	public $update_by;

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

	function create(){
        $query = "INSERT INTO " . $this->table_name . " SET category_code=:category_code, category=:category, type_code=:type_code
		, type=:type, item_code=:item_code, item_name=:item_name, barcode=:barcode, capital_price=:capital_price, sales_price=:sales_price
		, supplier=:supplier, entry_by=:entry_by";
		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":category_code", $this->category_code);
		$stmt->bindParam(":category", $this->category);
		$stmt->bindParam(":type_code", $this->type_code);
		$stmt->bindParam(":type", $this->type);
		$stmt->bindParam(":item_code", $this->item_code);
		$stmt->bindParam(":item_name", $this->item_name);
		$stmt->bindParam(":barcode", $this->barcode);
        $stmt->bindParam(":capital_price", $this->capital_price);
        $stmt->bindParam(":sales_price", $this->sales_price);
		$stmt->bindParam(":supplier", $this->supplier);
		$stmt->bindParam(":entry_by", $this->entry_by);

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

	function update(){
        $query = "UPDATE " . $this->table_name . " SET category_code=:category_code, category=:category, type_code=:type_code
		, type=:type, item_code=:item_code, item_name=:item_name, barcode=:barcode, capital_price=:capital_price, sales_price=:sales_price
		, supplier=:supplier, update_by=:update_by where id=:id";
		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":id", $this->id);
		$stmt->bindParam(":category_code", $this->category_code);
		$stmt->bindParam(":category", $this->category);
		$stmt->bindParam(":type_code", $this->type_code);
		$stmt->bindParam(":type", $this->type);
		$stmt->bindParam(":item_code", $this->item_code);
		$stmt->bindParam(":item_name", $this->item_name);
		$stmt->bindParam(":barcode", $this->barcode);
        $stmt->bindParam(":capital_price", $this->capital_price);
        $stmt->bindParam(":sales_price", $this->sales_price);
		$stmt->bindParam(":supplier", $this->supplier);
		$stmt->bindParam(":update_by", $this->update_by);

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

	function updateCapital(){
		$query = "UPDATE " . $this->table_name . " SET capital_price=:capital_price WHERE item_code=:item_code";
		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":item_code", $this->item_code);
		$stmt->bindParam(":capital_price", $this->capital_price);

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

	// function update(){
	// 	// category_code=:category_code, category=:category, type_code=:type_code, type=:type, brand_code=:brand_code, brand=:brand,
    //     $query = "UPDATE " . $this->table_name . " SET  model=:model, country_of_origin=:country_of_origin, item_code=:item_code
    //     , item_name=:item_name, description=:description, `unit`=:unit, guarantee=:guarantee, warranty=:warranty, min_stock_level=:min_stock_level, image_path=:image_path, entry_by=:entry_by, entry_date_time=:entry_date_time where id=:id";
	// 	$stmt = $this->conn->prepare($query);

	// 	$stmt->bindParam(":id", $this->id);
	// 	// $stmt->bindParam(":category_code", $this->category_code);
	// 	// $stmt->bindParam(":category", $this->category);
	// 	// $stmt->bindParam(":type_code", $this->type_code);
	// 	// $stmt->bindParam(":type", $this->type);
	// 	// $stmt->bindParam(":brand_code", $this->brand_code);
	// 	// $stmt->bindParam(":brand", $this->brand);
	// 	$stmt->bindParam(":model", $this->model);
	// 	$stmt->bindParam(":country_of_origin", $this->country_of_origin);
    //     $stmt->bindParam(":item_code", $this->item_code);
    //     $stmt->bindParam(":item_name", $this->item_name);
	// 	$stmt->bindParam(":description", $this->description);
	// 	$stmt->bindParam(":unit", $this->unit);
    //     $stmt->bindParam(":guarantee", $this->guarantee);
    //     $stmt->bindParam(":warranty", $this->warranty);
	// 	$stmt->bindParam(":min_stock_level", $this->min_stock_level);
	// 	$stmt->bindParam(":image_path", $this->image_path);
	// 	$stmt->bindParam(":entry_by", $this->entry_by);
	// 	$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  item_code= :item_code or barcode=:barcode LIMIT 0,1";
		$stmt = $this->conn->prepare( $query );	
	 
		$stmt->bindParam(":item_code", $this->item_code);
		$stmt->bindParam(":barcode", $this->barcode);
		
		$stmt->execute();
		if($stmt->rowCount()>0){
			return true;
		}
		return false;
	}

	// function getAllRows(){

	// 	$query = "SELECT item.*,REPLACE(a.mcp, 'x', '\n') AS mcp,REPLACE(b.mmd, 'x', '\n') AS mmd FROM item
	// 	LEFT JOIN 
	// 	(SELECT 
	// 		item_code,
	// 		GROUP_CONCAT(CONCAT(member, '  -  ', FORMAT(price, 0) , ' ', currency) ORDER BY member SEPARATOR 'x') AS mcp
	// 	FROM 
	// 		selling_price
	// 	GROUP BY 
	// 		item_code
	// 	) AS a ON item.item_code=a.item_code
	// 	LEFT join
	// 	(SELECT item_code,GROUP_CONCAT(CONCAT(member, '-', currency, ' ( ', min_quantity, '-', max_quantity, ' ) ', discount, '%') ORDER BY min_quantity SEPARATOR 'x') AS mmd
	// 	FROM selling_price
	// 	right JOIN wholesale_price ON selling_price.id = wholesale_price.selling_price_id
	// 	GROUP BY selling_price.id
	// 	) AS b ON item.item_code=b.item_code";
	// 	$stmt = $this->conn->prepare($query);
	// 	$stmt->execute();
	// 	return $stmt;
	// }

	// function search(){
	// 	$condition = "";
	// 	if($this->category_code){
	// 		if($condition!=""){
	// 			$condition .= " AND ";
	// 		}
	// 		$condition .= " category_code=:category_code ";
	// 	}
	// 	if($this->brand_code){
	// 		if($condition!=""){
	// 			$condition .= " AND ";
	// 		}
	// 		$condition .= " brand_code=:brand_code ";
	// 	}
	// 	if($this->type_code){
	// 		if($condition!=""){
	// 			$condition .= " AND ";
	// 		}
	// 		$condition .= " type_code=:type_code ";
	// 	}
	// 	if($this->model){
	// 		if($condition!=""){
	// 			$condition .= " AND ";
	// 		}
	// 		$condition .= " model=:model ";
	// 	}
	// 	if($this->country_of_origin){
	// 		if($condition!=""){
	// 			$condition .= " AND ";
	// 		}
	// 		$condition .= " country_of_origin=:country_of_origin ";
	// 	}
	// 	if($this->item_code){
	// 		if($condition!=""){
	// 			$condition .= " AND ";
	// 		}
	// 		$condition .= " item.item_code=:item_code ";
	// 	}
	// 	if($this->item_name){
	// 		if($condition!=""){
	// 			$condition .= " AND ";
	// 		}
	// 		$condition .= " (item_name LIKE  :item_name '%' or item_name LIKE '%' :item_name '%' or item_name Like '%' :item_name ) ";
	// 	}
	// 	if($this->description){
	// 		if($condition!=""){
	// 			$condition .= " AND ";
	// 		}
	// 		$condition .= " (description LIKE  :description '%' or description LIKE '%' :description '%' or description Like '%' :description ) ";
	// 	}
	// 	if($this->unit){
	// 		if($condition!=""){
	// 			$condition .= " AND ";
	// 		}
	// 		$condition .= " unit=:unit ";
	// 	}
	// 	if($this->guarantee){
	// 		if($condition!=""){
	// 			$condition .= " AND ";
	// 		}
	// 		$condition .= " guarantee=:guarantee ";
	// 	}
	// 	if($this->warranty){
	// 		if($condition!=""){
	// 			$condition .= " AND ";
	// 		}
	// 		$condition .= " warranty=:warranty ";
	// 	}
	// 	if($this->min_stock_level){
	// 		if($condition!=""){
	// 			$condition .= " AND ";
	// 		}
	// 		$condition .= " min_stock_level=:min_stock_level ";
	// 	}
	// 	if($condition!=""){
	// 		$condition = " WHERE " . $condition;
	// 	}

	// 	$query = "SELECT item.*,REPLACE(a.mcp, 'x', '\n') AS mcp,REPLACE(b.mmd, 'x', '\n') AS mmd FROM item
	// 	LEFT JOIN 
	// 	(SELECT 
	// 		item_code,
	// 		GROUP_CONCAT(CONCAT(member, '  -  ', FORMAT(price, 0) , ' ', currency) ORDER BY member SEPARATOR 'x') AS mcp
	// 	FROM 
	// 		selling_price
	// 	GROUP BY 
	// 		item_code
	// 	) AS a ON item.item_code=a.item_code
	// 	LEFT join
	// 	(SELECT item_code,GROUP_CONCAT(CONCAT(member, '-', currency, ' ( ', min_quantity, '-', max_quantity, ' ) ', discount, '%') ORDER BY min_quantity SEPARATOR 'x') AS mmd
	// 	FROM selling_price
	// 	right JOIN wholesale_price ON selling_price.id = wholesale_price.selling_price_id
	// 	GROUP BY selling_price.id
	// 	) AS b ON item.item_code=b.item_code " . $condition;
	// 	$stmt = $this->conn->prepare($query);
	// 	if($this->category_code) $stmt->bindParam(":category_code", $this->category_code);
	// 	if($this->brand_code) $stmt->bindParam(":brand_code", $this->brand_code);
	// 	if($this->type_code) $stmt->bindParam(":type_code", $this->type_code);
	// 	if($this->model) $stmt->bindParam(":model", $this->model);
	// 	if($this->country_of_origin) $stmt->bindParam(":country_of_origin", $this->country_of_origin);
	// 	if($this->item_code) $stmt->bindParam(":item_code", $this->item_code);
	// 	if($this->item_name) $stmt->bindParam(":item_name", $this->item_name);
	// 	if($this->description) $stmt->bindParam(":description", $this->description);
	// 	if($this->unit) $stmt->bindParam(":unit", $this->unit);
	// 	if($this->guarantee) $stmt->bindParam(":guarantee", $this->guarantee);
	// 	if($this->warranty) $stmt->bindParam(":warranty", $this->warranty);
	// 	if($this->min_stock_level) $stmt->bindParam(":min_stock_level", $this->min_stock_level);

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

    function delete(){
		$query = "DELETE FROM " . $this->table_name . " WHERE id = ?";
		$stmt = $this->conn->prepare($query);
		$stmt->bindParam(1, $this->id);
		if($stmt->execute()){
			return true;
		}
		return false;
	}

	function getOneRow() {
        $query = "SELECT * FROM " . $this->table_name . "  WHERE id = ?";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $this->id);
        $stmt->execute();
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($row) {
            $this->id = $row['id'];
            $this->category_code = $row['category_code'];
            $this->category = $row['category'];
            $this->type_code = $row['type_code'];
            $this->type = $row['type'];
            $this->item_code = $row['item_code'];
            $this->item_name = $row['item_name'];
			$this->barcode = $row['barcode'];
            $this->capital_price = $row['capital_price'];
            $this->sales_price = $row['sales_price'];
			$this->supplier = $row['supplier'];
        }
    }
	
	// function getItemDetail($code){
	// 	$query = "SELECT * FROM `" . $this->table_name . "` WHERE item_code = ? LIMIT 0,1";
	// 	$stmt = $this->conn->prepare( $query );
	// 	$stmt->bindParam(1, $code);
	// 	$stmt->execute();
	// 	if($stmt->rowCount()>0){
	// 		$row = $stmt->fetch(PDO::FETCH_ASSOC);
	// 		$this->id = $row['id'];	
	// 		$this->item_code = $row['item_code'];
	// 		$this->item_name = $row['item_name'];
	// 		$this->min_stock_level = $row['min_stock_level'];
	// 		$this->price = $row['price'];
	// 		$this->sales_price = $row['sales_price'];
	// 	}
	// }



	// function updateSalesPrice(){
    //     $query = "UPDATE " . $this->table_name . " SET sales_price=:sales_price where item_code=:item_code";
	// 	$stmt = $this->conn->prepare($query);

	// 	$stmt->bindParam(":item_code", $this->item_code);
	// 	$stmt->bindParam(":sales_price", $this->price);


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

	// function updatePrice(){
    //     $query = "UPDATE " . $this->table_name . " SET price=:price where item_code=:item_code";
	// 	$stmt = $this->conn->prepare($query);

	// 	$stmt->bindParam(":item_code", $this->item_code);
	// 	$stmt->bindParam(":price", $this->price);


	// 	if($stmt->execute()){
	// 		return true;
	// 	}	 
	// 	return false;
	// }
    
    // function updateMinusStock(){
    //     $query = "UPDATE " . $this->table_name . " SET minus_stock=:status where item_code=:item_code";
	// 	$stmt = $this->conn->prepare($query);

	// 	$stmt->bindParam(":item_code", $this->item_code);
	// 	$stmt->bindParam(":status", $this->status);


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

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

	// 		$condition .= " (concat(item_code,item_name) LIKE  :item_name '%' or concat(item_code,item_name) LIKE '%' :item_name '%' or concat(item_code,item_name) Like '%' :item_name )";
	// 	}
	// 	if($condition!=""){
	// 		$condition = " WHERE " . $condition;
	// 	}

	// 	$query = "SELECT * FROM " . $this->table_name . $condition . " ORDER BY item_code";
	// 	$stmt = $this->conn->prepare($query);
	// 	if($this->item_name) $stmt->bindParam(":item_name", $this->item_name);
	// 	$stmt->execute();
	// 	return $stmt;
	// }
	function getAllRows(){
		// $condition = "";
		// if($this->category){
		// 	$condition .= " WHERE category=:category ";
		// }
		$query = "SELECT * FROM item  order by item_code";
		$stmt = $this->conn->prepare($query);
		if($this->category) $stmt->bindParam(":category", $this->category);
		$stmt->execute();
		return $stmt;
	}

	function getGenerateItemCode($prefix){
		$query = "SELECT item_code FROM `" . $this->table_name . "` WHERE item_code like ? ORDER BY item_code desc limit 0,1";
		$stmt = $this->conn->prepare($query);
		$keywords = htmlspecialchars(strip_tags($prefix));
		$keywords = "{$keywords}%";
		
		$stmt->bindParam(1, $keywords);
		$stmt->execute();
		if($stmt->rowCount()>0){
			$row = $stmt->fetch(PDO::FETCH_ASSOC);
			return $prefix . sprintf("%'03d", ((int)str_replace($prefix,"",$row['item_code']) + 1));
		}else{
			return $prefix . "001";
		}
	}

	// function getAllRowsForMemberType(){
	// 		// $query = "SELECT batch_no,balance,item.* from item right JOIN (SELECT a.batch_no,a.item_code,IFNULL(pqty,0)-IFNULL(sqty,0) AS balance FROM (SELECT batch_no,item_code,SUM(quantity) AS pqty FROM purchase_detail pd WHERE pd.is_deleted=0 GROUP BY batch_no,item_code) AS a
	// 		// LEFT JOIN
	// 		// (SELECT batch_no,item_code,SUM(quantity) AS sqty FROM sales_detail sd WHERE sd.is_deleted=0 GROUP BY batch_no,item_code) AS b 
	// 		// ON a.batch_no=b.batch_no and a.item_code=b.item_code WHERE IFNULL(pqty,0)-IFNULL(sqty,0) <>0) AS c ON item.item_code=c.item_code ORDER BY batch_no,item_code
	// 		// ";
	// 	$query = "SELECT * FROM item ";
	// 	// $query = "SELECT item.*,REPLACE(a.mcp, 'x', '\n') AS mcp,REPLACE(b.mmd, 'x', '\n') AS mmd FROM item
	// 	// LEFT JOIN 
	// 	// (SELECT 
	// 	// 	item_code,
	// 	// 	GROUP_CONCAT(CONCAT(member, '  -  ', FORMAT(price, 0) , ' ', currency) ORDER BY member SEPARATOR 'x') AS mcp
	// 	// FROM 
	// 	// 	selling_price where member=:member and currency=:currency
	// 	// GROUP BY 
	// 	// 	item_code
	// 	// ) AS a ON item.item_code=a.item_code
	// 	// LEFT join
	// 	// (SELECT item_code,GROUP_CONCAT(CONCAT(member, '-', currency, ' ( ', min_quantity, '-', max_quantity, ' ) ', discount, '%') ORDER BY min_quantity SEPARATOR 'x') AS mmd
	// 	// FROM (SELECT * FROM selling_price WHERE member=:member and currency=:currency) AS selling_price
	// 	// right JOIN wholesale_price ON selling_price.id = wholesale_price.selling_price_id
	// 	// GROUP BY selling_price.id 
	// 	// ) AS b ON item.item_code=b.item_code";
	// 	$stmt = $this->conn->prepare($query);
	// 	// $stmt->bindParam(":member", $this->member);
	// 	// $stmt->bindParam(":currency", $this->currency);
	// 	$stmt->execute();
	// 	return $stmt;
	// }

}
?>
