<?php
class Customer{
    private $conn;
    private $table_name = "customer";
 
	public $id;
	public $name;
	public $phone;
	public $member_type;
	public $nrc;
	public $address;
	public $city;
	public $entry_by;
	public $entry_date_time;
	
    public function __construct($db){
        $this->conn = $db;
    }

    function getTotalCustomer(){
		$query = "SELECT count(id) AS total_customer FROM " . $this->table_name;
		$stmt = $this->conn->prepare( $query );
		
		if($stmt->execute()){
			$row = $stmt->fetch(PDO::FETCH_ASSOC);
			return $row['total_customer'];
		}
		return false;
	}
	
	function create(){
		$query = "INSERT INTO " . $this->table_name . " SET name=:name, phone=:phone, phone2=:phone2, member_type=:member_type, nrc=:nrc, address=:address, city=:city, entry_by=:entry_by, entry_date_time=:entry_date_time";
		$stmt = $this->conn->prepare($query);

		$stmt->bindParam(":name", $this->name);
		$stmt->bindParam(":phone", $this->phone);
		$stmt->bindParam(":phone2", $this->phone2);
		$stmt->bindParam(":member_type", $this->member_type);
		$stmt->bindParam(":nrc", $this->nrc);
		$stmt->bindParam(":address", $this->address);
		$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 name=:name, phone=:phone, phone2=:phone2, member_type=:member_type, nrc=:nrc, address=:address WHERE id=:id";
		
		$stmt = $this->conn->prepare($query);
		
		$stmt->bindParam(":id", $this->id);	
		$stmt->bindParam(":name", $this->name);
		$stmt->bindParam(":phone", $this->phone);
		$stmt->bindParam(":phone2", $this->phone2);
		$stmt->bindParam(":member_type", $this->member_type);
		$stmt->bindParam(":nrc", $this->nrc);
		$stmt->bindParam(":address", $this->address);
		
		if($stmt->execute()){
			return true;
		}
		return false;
	}

		function updateStatus(){
		$query = "UPDATE  " . $this->table_name . " SET c_status=:c_status WHERE id=:id";
		
		$stmt = $this->conn->prepare($query);
		
		$stmt->bindParam(":id", $this->id);	
		$stmt->bindParam(":c_status", $this->c_status);
		
		if($stmt->execute()){
			return true;
		}
		return false;
	}

	function search(){
		$condition = "";

		if($this->name){
			if($condition!=""){ $condition .= " AND "; }
			$condition .= " (customer.name LIKE  :name '%' or customer.name LIKE '%' :name '%' or customer.name Like '%' :name ) ";
		}
		if($this->phone){
			if($condition!=""){ $condition .= " AND "; }
			$condition .= " (customer.phone LIKE  :phone '%' or customer.phone LIKE '%' :phone '%' or customer.phone Like '%' :phone  ) ";
		}
		if($condition!=""){
			$condition = " AND " . $condition;
		}
		
		$query = "SELECT * from customer WHERE city=:city " . $condition . " order by name";
		$stmt = $this->conn->prepare($query);
		$this->name=htmlspecialchars(strip_tags($this->name));


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

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

	function getAllRows(){
		$query = "SELECT * FROM " . $this->table_name . " where city=:city ORDER BY id";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":city", $this->city);
		$stmt->execute();
		return $stmt;
	}


	function getUnSalesCustomer(){
		$query = "SELECT customer.*,member.member_discount,member.member_install_discount FROM customer left join sales on customer.id=sales.customer_id left join member on customer.member_type=member.member_type where customer.city=:city and sales.id is null  GROUP BY customer.id ORDER BY customer.id";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":city", $this->city);
		$stmt->execute();
		return $stmt;
	}

	function getAllRowsSales(){
		$query = "SELECT id_no,customer.* from sales left join " . $this->table_name . " on sales.customer_id=customer.id where sales.city=:city and S_R='Sales' ORDER BY id_no";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":city", $this->city);
		$stmt->execute();
		return $stmt;
	}

	function getAllRowsLastSales(){
		$query = "SELECT id_no,customer_id,sales.name,invoice_no,package,phone FROM (SELECT MAX(id) AS mxid FROM sales WHERE city=:city GROUP BY id_no) AS a
		LEFT JOIN sales ON a.mxid=sales.id
		LEFT JOIN customer ON sales.customer_id=customer.id";
		$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 getAllCardByCustomer(){
		$query = "SELECT customer.id as id, customer.name as name, card_no, current_package AS package, price, expire_date FROM " . $this->table_name . " LEFT JOIN  register ON customer.id=register.customer_id WHERE customer.id=:id ORDER BY register.id";

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

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

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

	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 getToActivate(){
		$query = "SELECT sales.id,sales.customer_id,sales.name,sales.id_no,package,phone,sales.start_date,'' AS end_date,sales.invoice_no FROM sales 
		LEFT JOIN customer ON sales.customer_id=customer.id
		LEFT JOIN (SELECT id_no,COUNT(*) AS c FROM installation GROUP BY id_no) AS a ON sales.id_no=a.id_no
		WHERE sales.city=:city AND sales.start_date='' AND c>=2";

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

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

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

	function getToDeActivate(){
		$query = "SELECT sales.id,sales.customer_id,sales.name,sales.id_no,package,phone,start_date,DATE_ADD(start_date, INTERVAL days+extra_days+extra_reason DAY) as end_date,sales.invoice_no FROM (SELECT MAX(id) AS mi FROM sales 
		WHERE DATE_ADD(start_date, INTERVAL days+extra_days+extra_reason DAY)<>'' and city=:city GROUP BY id_no) AS a
		LEFT JOIN sales ON a.mi=sales.id
		LEFT JOIN customer ON sales.customer_id=customer.id
		WHERE DATE_ADD(start_date, INTERVAL days+extra_days+extra_reason DAY) < :date AND status<>2";

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


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

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

	function getToTerminate(){
		$query = "SELECT sales.id,customer_id,sales.name,sales.id_no,package,phone,start_date,IFNULL(DATE_ADD(start_date, INTERVAL days DAY),'') as end_date,sales.invoice_no FROM sales  
		where sales.id=0
		";

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


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

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

	function getToPackageChange(){
		$query = "SELECT sales.id,sales.customer_id,sales.name,sales.id_no,package,phone,start_date,DATE_ADD(start_date, INTERVAL days+extra_days+extra_reason DAY) as end_date,sales.invoice_no FROM (SELECT MAX(id) AS mi FROM sales 
		WHERE refund_amount>0 and city=:city AND package_status<>4 GROUP BY id_no) AS a
		LEFT JOIN sales ON a.mi=sales.id
		LEFT JOIN customer ON sales.customer_id=customer.id

		";

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


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


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

	function getByID($id){

		$query = "SELECT * from customer WHERE id='$id'";
		$stmt = $this->conn->prepare($query);
		$stmt->execute();
		return $stmt;
	}

	function getOrderCustomer(){
		$query = "SELECT customer.* FROM customer LEFT JOIN order_list ON customer.id=order_list.customer_id WHERE customer.city=:city and customer_id IS null";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":city", $this->city);
		$stmt->execute();
		return $stmt;
	}

	function getItemSalesCustomer(){

		$query = "SELECT * from customer where city=:city order by name";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":city", $this->city);
		$stmt->execute();
		return $stmt;
	}

}
?>
