<?php
class Installation{

	private $conn;
	private $table_name = "installation";

	public $id;	
	public $order_no;	
	public $customer_id;
	public $customer_name;	
	public $customer_phone;		
	public $install_type;	
	public $install_date;	
	public $city;	
	public $id_no;	
	public $install_address;
	public $geolocation;
	public $slot_id;
	public $pon_port;
	public $odb;
	public $service_port;
	public $technician1;	
	public $technician2;	
	public $technician3;	
	public $remark;	
	public $entry_by;	
	public $entry_date_time;	

	public $olt_port;
	public $odb_no;
	public $port;
	public $dnsn;
	public $a;
	public $df;
	public $dt;
	public $dn;
	public $sn;
	public $id_no_old;
	public $id_no_new;

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

	function create(){
		$query = "INSERT INTO " . $this->table_name . " SET order_no=:order_no, customer_id=:customer_id, customer_name=:customer_name, customer_phone=:customer_phone, install_type=:install_type, install_date=:install_date
		, city=:city, id_no=:id_no, install_address=:install_address, geolocation=:geolocation, slot_id=:slot_id, pon_port=:pon_port, odb=:odb, service_port=:service_port, technician1=:technician1, technician2=:technician2, technician3=:technician3, remark=:remark
		, entry_by=:entry_by, entry_date_time=:entry_date_time";
		$stmt = $this->conn->prepare($query);


		$stmt->bindParam(":order_no", $this->order_no);
		$stmt->bindParam(":customer_id", $this->customer_id);
		$stmt->bindParam(":customer_name", $this->customer_name);
		$stmt->bindParam(":customer_phone", $this->customer_phone);
		$stmt->bindParam(":install_type", $this->install_type);
		$stmt->bindParam(":install_date", $this->install_date);
		$stmt->bindParam(":city", $this->city);
		$stmt->bindParam(":id_no", $this->id_no);
		$stmt->bindParam(":install_address", $this->install_address);
		$stmt->bindParam(":geolocation", $this->geolocation);
		$stmt->bindParam(":slot_id", $this->slot_id);
		$stmt->bindParam(":pon_port", $this->pon_port);
		$stmt->bindParam(":odb", $this->odb);
		$stmt->bindParam(":service_port", $this->service_port);
		$stmt->bindParam(":technician1", $this->technician1);
		$stmt->bindParam(":technician2", $this->technician2);
		$stmt->bindParam(":technician3", $this->technician3);
		$stmt->bindParam(":remark", $this->remark);
		$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 install_date=:install_date
		, install_address=:install_address, geolocation=:geolocation, pon_port=:pon_port, olt_port=:olt_port, odb=:odb, odb_no=:odb_no, port=:port, technician1=:technician1, technician2=:technician2, technician3=:technician3, remark=:remark
		, 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(":install_date", $this->install_date);
		$stmt->bindParam(":install_address", $this->install_address);
		$stmt->bindParam(":geolocation", $this->geolocation);
		$stmt->bindParam(":pon_port", $this->pon_port);
		$stmt->bindParam(":olt_port", $this->olt_port);
		$stmt->bindParam(":odb", $this->odb);
		$stmt->bindParam(":odb_no", $this->odb_no);
		$stmt->bindParam(":port", $this->port);
		$stmt->bindParam(":technician1", $this->technician1);
		$stmt->bindParam(":technician2", $this->technician2);
		$stmt->bindParam(":technician3", $this->technician3);
		$stmt->bindParam(":remark", $this->remark);
		$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 getTitle(){
	// 	$query = "SELECT item_name FROM installation 
	// 	LEFT JOIN installation_detail ON installation.id=installation_detail.installation_id
	// 	 where install_type='Cable' group by item_name order by item_name";
	// 	$stmt = $this->conn->prepare( $query );
	// 	$stmt->execute();
	// 	return $stmt;
	// }

	function search(){

		$condition = "";
		$condition1 = "";

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

		if($this->dnsn){
			$condition .= " and concat(dn,sn)=:dnsn ";
		}

		if($this->install_type){
			$condition .= " and install_type=:install_type  ";
			$condition1 .= " where install_type=:install_type1  ";
		}

		$q = "SELECT
		GROUP_CONCAT(DISTINCT
		  CONCAT(
			'max(case when item_name = ''',
			item_name,
			''' then quantity end) ',
			CONCAT('`',item_name,'`')
		  )
		) as a
	  FROM
	  (SELECT installation.*,item_code,item_name,quantity,unit FROM installation 
	  LEFT JOIN installation_detail ON installation.id=installation_detail.installation_id
	   ".$condition1.") AS b ";

		$stmt1 = $this->conn->prepare( $q );
		if($this->install_type) $stmt1->bindParam(":install_type1", $this->install_type);
		$stmt1->execute();

		if($stmt1->rowCount()>0){
			$row = $stmt1->fetch(PDO::FETCH_ASSOC);
			$this->a = $row['a'];
		}

		$query = "SELECT install_date AS `Install Date`,id AS `Install ID.`,install_type As `Install Type`,id_no AS `ID No.`,name AS `Customer Name`, phone AS `Customer Phone`, install_address AS `Install Address`, DN,SN,technician1 AS `Technician 1`,technician2 AS `Technician 2`,technician3 AS `Technician 3`,remark AS `Remark`,".$this->a."
		FROM (
		SELECT installation.*,item_code,item_name,quantity,unit,NAME,phone
		FROM installation
		LEFT JOIN installation_detail ON installation.id=installation_detail.installation_id
		LEFT JOIN customer ON installation.customer_id=customer.id
		WHERE installation.city=:city AND install_date>=:df AND install_date<=:dt ".$condition.") AS c
		GROUP BY c.id
		ORDER BY id_no,install_type";
		$stmt = $this->conn->prepare($query);
		$stmt->bindParam(":city", $this->city);
		$stmt->bindParam(":df", $this->df);
		$stmt->bindParam(":dt", $this->dt);
		if($this->id_no)  $stmt->bindParam(":id_no", $this->id_no);
		if($this->install_type) $stmt->bindParam(":install_type", $this->install_type);
		if($this->dnsn) $stmt->bindParam(":dnsn", $this->dnsn);
		$stmt->execute();
		return $stmt;

	}

	function search_(){

		$condition = "";
		$condition1 = "";

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

		if($this->dnsn){
			$condition .= " and concat(dn,sn)=:dnsn ";
		}

		if($this->install_type){
			$condition .= " and install_type=:install_type  ";
			$condition1 .= " where install_type=:install_type1  ";
		}

		$q = "SELECT
		GROUP_CONCAT(DISTINCT
		  CONCAT(
			'max(case when item_name = ''',
			item_name,
			''' then quantity end) ',
			CONCAT('`',item_name,'`')
		  )
		) as a
	  FROM
	  (SELECT installation.*,item_code,item_name,quantity,unit FROM installation 
	  LEFT JOIN installation_detail ON installation.id=installation_detail.installation_id
	   ".$condition1.") AS b ";

		$stmt1 = $this->conn->prepare( $q );
		if($this->install_type) $stmt1->bindParam(":install_type1", $this->install_type);
		$stmt1->execute();

		if($stmt1->rowCount()>0){
			$row = $stmt1->fetch(PDO::FETCH_ASSOC);
			$this->a = $row['a'];
		}

		$query = "SELECT install_date AS `Install Date`,id AS `Install ID.`,install_type As `Install Type`,id_no AS `ID No.`,name AS `Customer Name`, phone AS `Customer Phone`, install_address AS `Install Address`, DN,SN,technician1 AS `Technician 1`,technician2 AS `Technician 2`,technician3 AS `Technician 3`,remark AS `Remark`,".$this->a."
		FROM (
		SELECT installation.*,item_code,item_name,quantity,unit,NAME,phone
		FROM installation
		LEFT JOIN installation_detail ON installation.id=installation_detail.installation_id
		LEFT JOIN customer ON installation.customer_id=customer.id
		WHERE installation.city=:city AND install_date>=:df AND install_date<=:dt ".$condition.") AS c
		GROUP BY c.id
		ORDER BY id_no,install_type";
		$stmt = $this->conn->prepare($query);
		$stmt->bindParam(":city", $this->city);
		$stmt->bindParam(":df", $this->df);
		$stmt->bindParam(":dt", $this->dt);
		if($this->id_no)  $stmt->bindParam(":id_no", $this->id_no);
		if($this->install_type) $stmt->bindParam(":install_type", $this->install_type);
		if($this->dnsn) $stmt->bindParam(":dnsn", $this->dnsn);
		$stmt->execute();
		return $stmt;

	}

	function getOneRow(){
		$query = "SELECT * FROM " . $this->table_name . " WHERE	id=:id LIMIT 0,1";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":id", $this->id);
		$stmt->execute();
		if($stmt->rowCount()>0){
			$row = $stmt->fetch(PDO::FETCH_ASSOC);
			$this->id = $row['id'];
			$this->install_date = $row['install_date'];
			$this->install_address = $row['install_address'];
			$this->geolocation = $row['geolocation'];
			$this->dn = $row['DN'];
			$this->sn = $row['SN'];
			$this->technician1 = $row['technician1'];	
			$this->technician2 = $row['technician2'];	
			$this->technician3 = $row['technician3'];
			$this->remark = $row['remark'];
		}
	}

	function getOneRowByIDNO(){
		$query = "SELECT * FROM " . $this->table_name . " WHERE	id_no=:id_no LIMIT 0,1";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":id_no", $this->id_no);
		$stmt->execute();
		if($stmt->rowCount()>0){
			$row = $stmt->fetch(PDO::FETCH_ASSOC);
			$this->id_no = $row['id_no'];
			$this->slot_id = $row['slot_id'];
			$this->pon_port = $row['pon_port'];
			$this->odb = $row['odb'];
			$this->service_port = $row['service_port'];

		}
	}

	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 getDNSN(){
		$condition = "";
		if($this->city){
			$condition .= " where city=:city ";
		}

		$query = "SELECT CONCAT(dn,sn) as dnsn FROM installation " . $condition . " GROUP BY dnsn";
		$stmt = $this->conn->prepare($query);
		if($this->city) $stmt->bindParam(":city", $this->city);
		$stmt->execute();
		return $stmt;
	}


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

		if($this->install_type){
			$condition .= " and install_type_=:install_type  ";
		}
		$query = "SELECT * from installation_detail WHERE city_=:city AND install_date_>=:df AND install_date_<=:dt ".$condition." group by installation_id,item_code order by item_name";
		
		$stmt = $this->conn->prepare($query);
		$stmt->bindParam(":city", $this->city);
		$stmt->bindParam(":df", $this->df);
		$stmt->bindParam(":dt", $this->dt);
		if($this->id_no)  $stmt->bindParam(":id_no", $this->id_no);
		if($this->install_type) $stmt->bindParam(":install_type", $this->install_type);

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

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

		if($this->install_type){
			$condition .= " and install_type=:install_type  ";
		}

		$query = "SELECT install_date AS `Install Date`,installation.id,install_type As `Install Type`,id_no AS `ID No.`,customer_name AS `Customer Name`, customer_phone AS `Customer Phone`, install_address AS `Install Address`, geolocation AS `Geolocation`,technician1 AS `Technician 1`,technician2 AS `Technician 2`,technician3 AS `Technician 3`,remark AS `Remark`
			FROM installation 
			LEFT JOIN customer ON installation.customer_id=customer.id
			WHERE installation.city=:city AND install_date>=:df AND install_date<=:dt ".$condition." ORDER BY id_no,install_type
		";
		
		$stmt = $this->conn->prepare($query);
		$stmt->bindParam(":city", $this->city);
		$stmt->bindParam(":df", $this->df);
		$stmt->bindParam(":dt", $this->dt);
		if($this->id_no)  $stmt->bindParam(":id_no", $this->id_no);
		if($this->install_type) $stmt->bindParam(":install_type", $this->install_type);
		$stmt->execute();
		return $stmt;	
	}

	function updateInDe(){
		$query = "UPDATE installation_detail
		LEFT JOIN installation ON installation_detail.installation_id=installation.id
		SET city_=city,id_no_=id_no,install_date_=install_date,install_type_=install_type
		 WHERE id_no_ IS null or id_no_=''";
		$stmt = $this->conn->prepare($query);

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

	function getDeviceForSales(){
		$query = "SELECT installation.id_no,installation.customer_id,customer_name,phone,install_address,customer.member_type,member.member_discount,member.member_install_discount FROM installation left join customer on installation.customer_id=customer.id 
		left join member on customer.member_type=member.member_type 
		left join sales on installation.id_no=sales.id_no and installation.customer_id=sales.customer_id WHERE install_type='onu' and installation.city=:city and sales.id_no is null";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":city", $this->city);
		$stmt->execute();
		return $stmt;
	}

function updateIDNO(){
    $query1 = "UPDATE installation SET id_no=:id_no_new, slot_id=:slot_id, pon_port=:pon_port, odb=:odb, service_port=:service_port where id_no=:id_no_old";

    $query2 = "UPDATE ont SET id_no=:id_no_new where id_no=:id_no_old";

    $query3 = "UPDATE sales SET id_no=:id_no_new where id_no=:id_no_old";

    $query4 = "UPDATE complaint SET id_no=:id_no_new where id_no=:id_no_old";

    $query5 = "UPDATE ont_status SET id_no=:id_no_new where id_no=:id_no_old";

    $stmt1 = $this->conn->prepare($query1);
    $stmt2 = $this->conn->prepare($query2);
    $stmt3 = $this->conn->prepare($query3);
    $stmt4 = $this->conn->prepare($query4);
    $stmt5 = $this->conn->prepare($query5);

    $stmt1->bindParam(":id_no_old", $this->id_no_old);
    $stmt1->bindParam(":id_no_new", $this->id_no_new);
    $stmt1->bindParam(":slot_id", $this->slot_id);
    $stmt1->bindParam(":pon_port", $this->pon_port);
    $stmt1->bindParam(":odb", $this->odb);
    $stmt1->bindParam(":service_port", $this->service_port);

    $stmt2->bindParam(":id_no_old", $this->id_no_old);
    $stmt2->bindParam(":id_no_new", $this->id_no_new);

    $stmt3->bindParam(":id_no_old", $this->id_no_old);
    $stmt3->bindParam(":id_no_new", $this->id_no_new);

    $stmt4->bindParam(":id_no_old", $this->id_no_old);
    $stmt4->bindParam(":id_no_new", $this->id_no_new);

    $stmt5->bindParam(":id_no_old", $this->id_no_old);
    $stmt5->bindParam(":id_no_new", $this->id_no_new);

    if($stmt1->execute() && $stmt2->execute() && $stmt3->execute() && $stmt4->execute() && $stmt5->execute()){
        return true;
    }	 
    return false;
}

	function checkidno(){
		$query = "SELECT * FROM installation WHERE id_no =:id_no_new LIMIT 0,1";
		$stmt = $this->conn->prepare( $query );
	 
		$stmt->bindParam(":id_no_new", $this->id_no_new);
		
		$stmt->execute();
		if($stmt->rowCount()>0){
			return true;
		}
		return false;
	}



}
?>
