<?php
class OrderList{
    private $conn;
    private $table_name = "order_list";
 
	public $id;
	public $city;
	public $order_no;
	public $order_date;
	public $plan;
	public $month;
	public $customer_id;
	public $name;
	public $phone;
	public $member_type;
	public $address;
	public $entry_by;
	public $entry_date_time;
	
    public function __construct($db){
        $this->conn = $db;
    }
	
	function create(){
		$query = "INSERT INTO " . $this->table_name . " SET city=:city, order_no=:order_no, order_date=:order_date, plan=:plan, month=:month, customer_id=:customer_id, name=:name, phone=:phone, member_type=:member_type, address=:address, entry_by=:entry_by, entry_date_time=:entry_date_time";
		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":city", $this->city);
		$stmt->bindParam(":order_no", $this->order_no);
		$stmt->bindParam(":order_date", $this->order_date);
		$stmt->bindParam(":plan", $this->plan);
		$stmt->bindParam(":month", $this->month);
		$stmt->bindParam(":customer_id", $this->customer_id);
		$stmt->bindParam(":name", $this->name);
		$stmt->bindParam(":phone", $this->phone);
		$stmt->bindParam(":member_type", $this->member_type);
		$stmt->bindParam(":address", $this->address);
		$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 getAllRows(){
		$query = "SELECT order_list.* FROM `order_list`
LEFT JOIN (SELECT * FROM installation GROUP BY order_no) AS installation ON order_list.order_no=installation.order_no
 where order_list.city=:city AND installation.id IS null order by order_list.id desc";
		$stmt = $this->conn->prepare($query);
		$stmt->bindParam(":city", $this->city);
		$stmt->execute();
		return $stmt;
	}

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

	function exist(){
		$query = "SELECT * FROM " . $this->table_name . " WHERE name = ? and city = ? LIMIT 0,1";
		$stmt = $this->conn->prepare( $query );
		$this->name=htmlspecialchars(strip_tags($this->name));
	 
		$stmt->bindParam(1, $this->name);
		$stmt->bindParam(2, $this->city);
		
		$stmt->execute();
		if($stmt->rowCount()>0){
			return true;
		}
		return false;
	}

    function getGenerateOrderNo($prefix){
		$query = "SELECT order_no FROM `order_list` WHERE order_no like ? ORDER BY order_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("%'05d", ((int)str_replace($prefix,"",$row['order_no']) + 1));
			// return $prefix . "00002";
		}else{
			return $prefix . "00001";
		}
	}

	function getToInstall(){

		if($this->ty=="Cable"){
			$query = "SELECT order_list.*,id_no,slot_id,pon_port,odb,service_port,geolocation FROM order_list 
			LEFT JOIN installation ON order_list.order_no=installation.order_no
			WHERE order_list.city=:city AND installation.id IS null";
		}else if($this->ty=="ONU"){
			$query = "SELECT order_list.*,id_no,slot_id,pon_port,odb,service_port,geolocation FROM order_list 
			LEFT JOIN installation ON order_list.order_no=installation.order_no
			LEFT JOIN (SELECT order_no,COUNT(*) AS c FROM installation GROUP BY order_no) AS a ON order_list.order_no=a.order_no
						WHERE order_list.city=:city AND c=1";
		}
		
		$stmt = $this->conn->prepare($query);

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

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


}
?>
