<?php
class ItemSales{
	    // database connection and table name
	private $conn;
	private $table_name = "item_sales";

	// object properties

	public $id;
	public $sales_date;
	public $store;
	public $voucher_no;
	public $invoice_no;
	public $name;
	public $total_amount;
	public $discount;
	public $payment;
	public $entry_by;
	public $entry_date_time;

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

	function create(){
		$query = "INSERT INTO `" . $this->table_name . "` SET sales_date=:sales_date, store=:store, voucher_no=:voucher_no, invoice_no=:invoice_no, name=:name
        , total_amount=:total_amount, discount=:discount, payment=:payment, entry_by=:entry_by, entry_date_time=:entry_date_time ";

		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":sales_date", $this->sales_date);
		$stmt->bindParam(":store", $this->store);
		$stmt->bindParam(":voucher_no", $this->voucher_no);
		$stmt->bindParam(":invoice_no", $this->invoice_no);
        $stmt->bindParam(":name", $this->name);
		$stmt->bindParam(":total_amount", $this->total_amount);
		$stmt->bindParam(":discount", $this->discount);
		$stmt->bindParam(":payment", $this->payment);
		$stmt->bindParam(":entry_by", $this->entry_by);
		$stmt->bindParam(":entry_date_time", $this->entry_date_time);

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

	function update(){
		$query = "UPDATE `" . $this->table_name . "` SET sales_date=:sales_date, car_no=:car_no, name=:name
        , phone=:phone, address=:address, total_amount=:total_amount, payment=:payment, entry_by=:entry_by, entry_date_time=:entry_date_time where voucher_no=:voucher_no";

		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":sales_date", $this->sales_date);
		$stmt->bindParam(":voucher_no", $this->voucher_no);
		$stmt->bindParam(":car_no", $this->car_no);
        $stmt->bindParam(":name", $this->name);
        $stmt->bindParam(":phone", $this->phone);
		$stmt->bindParam(":address", $this->address);
		$stmt->bindParam(":total_amount", $this->total_amount);
		$stmt->bindParam(":payment", $this->payment);
		$stmt->bindParam(":entry_by", $this->entry_by);
		$stmt->bindParam(":entry_date_time", $this->entry_date_time);

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

	function getGenerateVoucherNo($prefix){
		$query = "SELECT voucher_no FROM `" . $this->table_name . "` WHERE voucher_no like ?  ORDER BY voucher_no 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("%'04d", ((int)str_replace($prefix,"",$row['voucher_no']) + 1));
		}else{
			return $prefix . "0001";
		}
	}

	function getOnesales(){
		$query = "SELECT item_sales.*,id_no,phone FROM item_sales left join sales on item_sales.invoice_no=sales.invoice_no left join customer on sales.customer_id=customer.id WHERE voucher_no=:voucher_no LIMIT 0,1";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":voucher_no", $this->voucher_no);
		$stmt->execute();
		if($stmt->rowCount()>0){
			$row = $stmt->fetch(PDO::FETCH_ASSOC);
			$this->id = $row['id'];
			$this->sales_date = $row['sales_date'];
			$this->voucher_no = $row['voucher_no'];
			$this->store = $row['store'];
			$this->name = $row['name'];
			$this->total_amount = $row['total_amount'];
			$this->discount = $row['discount'];
			$this->payment = $row['payment'];
			$this->id_no = $row['id_no'];
			$this->phone = $row['phone'];

		}
	}

	function getOnePrint(){
		$query = "SELECT * FROM " . $this->table_name . " WHERE	voucher_no=:voucher_no LIMIT 0,1";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":voucher_no", $this->voucher_no);
		$stmt->execute();
		if($stmt->rowCount()>0){
			$row = $stmt->fetch(PDO::FETCH_ASSOC);
			$this->id = $row['id'];
			$this->sales_date = $row['sales_date'];
			$this->voucher_no = $row['voucher_no'];
			$this->car_no = $row['car_no'];
			$this->name = $row['name'];
			$this->phone = $row['phone'];	
			$this->address = $row['address'];	
			$this->payment = $row['payment'];
			$this->total_amount = $row['total_amount'];

		}
	}

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

	function search(){
		$condition = "";
		if($this->df){
			if($condition!=""){
				$condition .= " AND ";
			}
			$condition .= " `sales_date`>=:df ";
		}

		if($this->dt){
			if($condition!=""){
				$condition .= " AND ";
			}
			$condition .= " `sales_date`<=:dt ";
		}

		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 ) ";
			// $condition .= " (sales.sales_no LIKE  :sales_no '%' or sales.sales_no LIKE '%' :sales_no '%' or sales.sales_no Like '%' :sales_no ) ";
		}

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

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

		$query = "SELECT * FROM item_sales " . $condition . " order by sales_date,voucher_no";
		$stmt = $this->conn->prepare($query);
		if($this->df) $stmt->bindParam(":df", $this->df);
		if($this->dt) $stmt->bindParam(":dt", $this->dt);
		if($this->voucher_no) $stmt->bindParam(":voucher_no", $this->voucher_no);
		if($this->city) $stmt->bindParam(":city", $this->city);
		if($this->name) $stmt->bindParam(":name", $this->name);

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


	function getTotalSales(){
		$query = "SELECT sum(total_amount) AS total_sales FROM " . $this->table_name ;
		$stmt = $this->conn->prepare( $query );
		if($stmt->execute()){
			$row = $stmt->fetch(PDO::FETCH_ASSOC);
			return $row['total_sales'];
		}
		return false;
	}

}
?>