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

	// object properties

	public $id;
	public $city;
	public $package;
	public $mbps;
	public $days;
	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, package=:package, mbps=:mbps, days=:days, entry_by=:entry_by, entry_date_time=:entry_date_time";

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

		$stmt->bindParam(":city", $this->city);
		$stmt->bindParam(":package", $this->package);
		$stmt->bindParam(":mbps", $this->mbps);
		$stmt->bindParam(":days", $this->days);
		$stmt->bindParam(":entry_by", $this->entry_by);
		$stmt->bindParam(":entry_date_time", $this->entry_date_time);

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

	function exist(){
		$query = "SELECT * FROM `" . $this->table_name . "` WHERE city=:city and package=:package ORDER BY city, package";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":city", $this->city);
		$stmt->bindParam(":package", $this->package);
		$stmt->execute();
		if($stmt->rowCount()>0){
			return true;
		}
		return false;
	}


	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 getAllPackages(){
		$condition = "";

		if($this->city){
			if($condition!=""){ $condition .= " AND "; }
			$condition .= " city=:city ";
		}
		if($this->mbps){
			if($condition!=""){ $condition .= " AND "; }
			$condition .= " mbps=:mbps ";
		}

		if($condition!=""){
			$condition = " WHERE " . $condition;
		}
		$query = "SELECT * FROM " . $this->table_name . $condition . " ORDER BY city,mbps";
		$stmt = $this->conn->prepare( $query );
		if($this->city) $stmt->bindParam(":city", $this->city);
		if($this->package) $stmt->bindParam(":package", $this->package);
		if($this->mbps) $stmt->bindParam(":mbps", $this->mbps);
		$stmt->execute();
		return $stmt;
	}

	function getAllPackagesPriceChange(){
		$query = "SELECT package.*,price_change_date,ifnull(package_price,0) AS package_price,ifnull(fee,0) as fee FROM package LEFT JOIN
		(SELECT * FROM price_change WHERE price_change_date=(SELECT MAX(price_change_date) FROM price_change WHERE city=:city AND price_change_date<=:pcd)) AS a ON
		package.package=a.package and package.city=a.city WHERE package.city=:city order by mbps,days";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":city", $this->city);
		$stmt->bindParam(":pcd", $this->pcd);
		$stmt->execute();
		return $stmt;
	}

	function getLastPackagePrice(){

		$query = "SELECT package.*,price_change_date,ifnull(package_price,0) AS package_price,ifnull(fee,0) as fee FROM package LEFT JOIN
		(SELECT * FROM price_change WHERE price_change_date=(SELECT MAX(price_change_date) FROM price_change WHERE city=:city)) AS a ON
		package.package=a.package and package.city=a.city WHERE package.city=:city order by mbps,days";
		$stmt = $this->conn->prepare( $query );
		$stmt->bindParam(":city", $this->city);
		$stmt->execute();
		return $stmt;
	}

}
?>