<?php
class ONT{
    private $conn;
    private $table_name = "ont";
 
	public $id;
	public $id_no;
	public $ont_snno;
	public $mme_id;
	public $pppoe_name;
	public $ont_password;
	public $city;
	public $port;
	public $ont_id;
	public $status;
	public $package_change;
	public $entry_by;
	public $entry_date_time;
	
    public function __construct($db){
        $this->conn = $db;
    }

    function getTotalONT(){
		$query = "SELECT count(id) AS total_ont FROM " . $this->table_name;
		$stmt = $this->conn->prepare( $query );
		
		if($stmt->execute()){
			$row = $stmt->fetch(PDO::FETCH_ASSOC);
			return $row['total_ont'];
		}
		return false;
	}
	
	function create(){
		$query = "INSERT INTO " . $this->table_name . " SET id_no=:id_no, ont_snno=:ont_snno, mme_id=:mme_id, pppoe_name=:pppoe_name, ont_password=:ont_password, city=:city, entry_by=:entry_by, entry_date_time=:entry_date_time";
		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":id_no", $this->id_no);
		$stmt->bindParam(":ont_snno", $this->ont_snno);
		$stmt->bindParam(":mme_id", $this->mme_id);
		$stmt->bindParam(":pppoe_name", $this->pppoe_name);
		$stmt->bindParam(":ont_password", $this->ont_password);
		$stmt->bindParam(":city", $this->city);
		$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 createFromInstall(){
		$query = "INSERT INTO " . $this->table_name . " SET id_no=:id_no, city=:city, entry_by=:entry_by, entry_date_time=:entry_date_time";
		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":id_no", $this->id_no);
		$stmt->bindParam(":city", $this->city);
		$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 ont_snno=:ont_snno, mme_id=:mme_id, pppoe_name=:pppoe_name, ont_password=:ont_password WHERE id=:id";
		
		$stmt = $this->conn->prepare($query);
		
		$stmt->bindParam(":id", $this->id);
		// $stmt->bindParam(":id_no", $this->id_no);
		$stmt->bindParam(":ont_snno", $this->ont_snno);
		$stmt->bindParam(":mme_id", $this->mme_id);
		$stmt->bindParam(":pppoe_name", $this->pppoe_name);
		$stmt->bindParam(":ont_password", $this->ont_password);
		
		if($stmt->execute()){
			return true;
		}
		return false;
	}

	function updatePort(){
		$query = "UPDATE  " . $this->table_name . " SET ont_snno=:onu_sn,port=:port, ont_id=:ont_id WHERE id_no=:id_no";
		
		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":id_no", $this->id_no);
		$stmt->bindParam(":onu_sn", $this->onu_sn);
		$stmt->bindParam(":port", $this->port);
		$stmt->bindParam(":ont_id", $this->ont_id);

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

	function updateStatus(){
		$query = "UPDATE  " . $this->table_name . " SET `status`=:status WHERE id_no=:id_no";
		
		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":id_no", $this->id_no);
		$stmt->bindParam(":status", $this->status);
		
		if($stmt->execute()){
			return true;
		}
		return false;
	}

	function updatePackageChange(){
		$query = "UPDATE  " . $this->table_name . " SET `package_change`=:package_change WHERE id_no=:id_no";
		
		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":id_no", $this->id_no);
		$stmt->bindParam(":package_change", $this->package_change);
		
		if($stmt->execute()){
			return true;
		}
		return false;
	}

	function search(){
		$condition = "";

		if($this->customer_name){

			$condition .= " and (name LIKE  :customer_name '%' or name LIKE '%' :customer_name '%' or name Like '%' :customer_name ) ";
		}
		if($this->id_no){

			$condition .= " and (ont.id_no LIKE  :id_no '%' or ont.id_no LIKE '%' :id_no '%' or ont.id_no Like '%' :id_no ) ";
		}

		if($this->status_=="Active"){
			$condition .= " and status=1 ";
		}else if($this->status_=="Disable"){
			$condition .= " and status=2 ";
		}else if($this->status_=="New"){
			$condition .= " and status=0 ";
		}
		
		$query = "SELECT ont.*,name,package,ont.status as ss from ont left join (
			SELECT *
			FROM sales
			RIGHT JOIN (
			SELECT MAX(id) AS mxid
			FROM sales
			GROUP BY id_no) AS a ON sales.id=a.mxid
			GROUP BY sales.id_no) AS sales_ on ont.id_no=sales_.id_no
					WHERE ont.city=:city " . $condition . "  group by ont.id_no
					ORDER BY ont.id_no 
		";
		$stmt = $this->conn->prepare($query);
		$this->customer_name=htmlspecialchars(strip_tags($this->customer_name));
		$this->id_no=htmlspecialchars(strip_tags($this->id_no));


		if($this->customer_name) $stmt->bindParam(":customer_name", $this->customer_name);
		if($this->id_no) $stmt->bindParam(":id_no", $this->id_no);
		// if($this->status_) $stmt->bindParam(":status_", $this->status_);
		$stmt->bindParam(":city", $this->city);

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

	function getAllRows(){
		$query = "SELECT ont.*,customer_name FROM ont LEFT JOIN (SELECT * from installation right JOIN (SELECT MAX(id) AS mxid FROM installation GROUP BY id_no) AS a ON installation.id=a.mxid) as installation ON ont.id_no=installation.id_no where ont.city=:city GROUP BY ont.id order by ont.id_no";
		$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 deleteFromInstallation(){
		$query = "DELETE ont
		FROM ont 
		LEFT JOIN (
		    SELECT id_no, city
		    FROM installation 
		    WHERE INSTALL_type = 'cable'
		    GROUP BY id_no, city
		) AS a 
		ON ont.id_no = a.id_no AND ont.city = a.city 
		WHERE a.id_no IS NULL";
		$stmt = $this->conn->prepare($query);
		if($stmt->execute()){
			return true;
		}
		return false;
	}


	function exist(){
		$query = "SELECT * FROM " . $this->table_name . " WHERE id_no = ? and city = ?  LIMIT 0,1";
		$stmt = $this->conn->prepare( $query );
		$this->id_no=htmlspecialchars(strip_tags($this->id_no));
	 
		$stmt->bindParam(1, $this->id_no);
		$stmt->bindParam(2, $this->city);

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

	function exist1(){
		$query = "SELECT * FROM " . $this->table_name . " WHERE id_no = ? and city = ?  LIMIT 0,1";
		$stmt = $this->conn->prepare( $query );
		$this->id_no=htmlspecialchars(strip_tags($this->id_no));
	 
		$stmt->bindParam(1, $this->id_no);
		$stmt->bindParam(2, $this->city);

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

	function getDeviceForSales(){
		$query = "SELECT ont.id_no,STATUS from ont LEFT JOIN (SELECT * FROM sales GROUP BY id_no) AS a ON
				ont.id_no=a.id_no WHERE ont.city=:city AND (a.id_no IS NULL || STATUS=3) order BY id_no";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":city", $this->city);
		$stmt->execute();
		return $stmt;
	}

	function getDeviceForInstall(){
		$query = "SELECT ont.id_no,STATUS from ont LEFT JOIN (SELECT * FROM sales GROUP BY id_no) AS a ON
		ont.id_no=a.id_no WHERE ont.city=:city AND (a.id_no IS NULL || STATUS=3) order by id_no";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":city", $this->city);
		$stmt->execute();
		return $stmt;
	}

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

}
?>
