<?php
/**
 * Model for Item_request
 *
 * @author Mills ABeiku Godfred
 */
class ItemRequest
{
	
	public $conn;
	

	
	function __construct()
	{
		$this->conn = Database::connect();
    }
    
    public function add_item_request(array $data){
		$sql = "INSERT INTO item_request ";
		$fields = array_keys($data);
		$vals = array_values($data);

		$sql .= "(".implode(",", $fields).")";
		$arr = array();
		foreach ($fields as $f) {
			$arr[] = "?";
		}
		$sql .= " VALUES(".implode(",", $arr).")";

		$statement = $this->conn->prepare($sql);

		foreach ($vals as $i => $v) {
			$statement->bindValue($i + 1, $v);
		}

		return $statement->execute();

    }
    
    public function update_item_request(array $key_col_vals, array $values) {
        
        $sql = "UPDATE item_request SET ";
        $fields = array_keys($values);
        $vals = array_values($values);
        $key_cols = array_keys($key_col_vals);
        $key_vals = array_values($key_col_vals);
        foreach ($fields as $i=>$f) {
            $fields[$i] .= " = ? ";
        }

        $sql .= implode(",", $fields);
        $sql .= " WHERE " ;
        
        foreach ($key_cols as $i=>$f) {
            $key_cols[$i] .= " = ? ";
        }
        
        $sql .= implode(" AND ", $key_cols);

        $statement = $this->conn->prepare($sql);
        foreach ($vals as $i=>$v) {
            $statement->bindValue($i+1, $v);
        }
        
        foreach ($key_vals as $i=>$v) {
           if($i==0){
               $statement->bindValue((count($fields)+$i+1), $v);
            }else{
               $statement->bindValue((count($fields)+$i), $v);
            }
        }
        
       return  $statement->execute();

    }
	
	
	public function read_all_item_requests(){
		$sql = "SELECT a.*,b.*,c.* FROM item_request a 
		JOIN employee b ON a.item_approved_by=b.employee_id 
		JOIN customer c ON a.request_customer=c.customer_id";
		$statement = $this->conn->prepare($sql);
		$statement->execute();
		if ($statement->rowCount() > 0) {
			return $statement->fetchAll(PDO::FETCH_ASSOC);
		}
		return false;
    }

	public function read_my_item_requests($employee_id){
		$sql = "SELECT a.*,b.*,c.* FROM item_request a 
		JOIN employee b ON a.item_approved_by=b.employee_id 
		JOIN customer c ON a.request_customer=c.customer_id 
		WHERE item_requested_by=:employee_id";
		$statement = $this->conn->prepare($sql);
		$statement->bindParam(":employee_id",$employee_id);
		$statement->execute();
		if ($statement->rowCount() > 0) {
			return $statement->fetchAll(PDO::FETCH_ASSOC);
		}
		return false;
    }

	public function read_customer_item_requests($customer_id){
		$sql = "SELECT a.*,b.*,c.*,d.* FROM item_request a 
		JOIN employee b ON a.item_approved_by=b.employee_id 
		JOIN customer c ON a.request_customer=c.customer_id 
		JOIN installment d ON a.item_request_id=d.item_request_id 
		WHERE a.request_customer=:customer_id";
		$statement = $this->conn->prepare($sql);
		$statement->bindParam(":customer_id",$customer_id);
		$statement->execute();
		if ($statement->rowCount() > 0) {
			return $statement->fetchAll(PDO::FETCH_ASSOC);
		}
		return false;
    }

    public function read_item_request($item_request_id){
		$sql = "SELECT a.*,b.*,c.* FROM item_request a 
		JOIN customer b ON a.request_customer=b.customer_id 
		JOIN employee c ON a.item_requested_by=c.employee_id 
		WHERE item_request_id=:item_request_id";
		$statement = $this->conn->prepare($sql);
        $statement->bindParam(":item_request_id",$item_request_id);
		$statement->execute();
		if ($statement->rowCount() > 0) {
			return $statement->fetchAll(PDO::FETCH_ASSOC);
		}
		return false;
	}

	public function read_item_request_for_installment($installment_id){
		$sql = "SELECT a.*,b.*,c.*,d.* FROM installment a 
		JOIN item_request b ON a.item_request_id=b.item_request_id 
		JOIN employee c ON b.item_approved_by=c.employee_id 
		JOIN customer d ON b.request_customer=d.customer_id 
		WHERE a.installment_id=:installment_id";
		$statement = $this->conn->prepare($sql);
        $statement->bindParam(":installment_id",$installment_id);
		$statement->execute();
		if ($statement->rowCount() > 0) {
			return $statement->fetchAll(PDO::FETCH_ASSOC);
		}
		return false;
	}

	public function find_invoice($id){
		$sql = "SELECT * FROM payment WHERE payment_id=:id ";
		$statement = $this->conn->prepare($sql);
        $statement->bindParam(":id",$id);
		$statement->execute();
		if ($statement->rowCount() > 0) {
			return $statement->fetchAll(PDO::FETCH_ASSOC);
		}
		return false;
	}

	public function read_grouped_items($item_category_id,$item_level_id){
		$sql = "SELECT * FROM items WHERE item_category_id=:item_category_id 
		AND item_level_id=:item_level_id";
		$statement = $this->conn->prepare($sql);
        $statement->bindParam(":item_category_id",$item_category_id);
		$statement->bindParam(":item_level_id",$item_level_id);
		$statement->execute();
		if ($statement->rowCount() > 0) {
			return $statement->fetchAll(PDO::FETCH_ASSOC);
		}
		return false;
	}

	public function read_item_request_hash($item_request_hash){
		$sql = "SELECT * FROM item_request WHERE item_request_hash=:item_request_hash";
		$statement = $this->conn->prepare($sql);
        $statement->bindParam(":item_request_hash",$item_request_hash);
		$statement->execute();
		if ($statement->rowCount() > 0) {
			return $statement->fetchAll(PDO::FETCH_ASSOC);
		}
		return false;
	}

	public function remove_item_request($item_request_id){
		$sql = "DELETE FROM item_request WHERE item_request_id=:item_request_id";
		$statement = $this->conn->prepare($sql);
        $statement->bindParam(":item_request_id",$item_request_id);
		$statement->execute();
		if ($statement->rowCount() > 0) {
            $statement->fetchAll(PDO::FETCH_ASSOC);
		}
		return false;
    }

	// Item Request Order
	public function add_item_request_order(array $data){
		$sql = "INSERT INTO item_request_order ";
		$fields = array_keys($data);
		$vals = array_values($data);

		$sql .= "(".implode(",", $fields).")";
		$arr = array();
		foreach ($fields as $f) {
			$arr[] = "?";
		}
		$sql .= " VALUES(".implode(",", $arr).")";

		$statement = $this->conn->prepare($sql);

		foreach ($vals as $i => $v) {
			$statement->bindValue($i + 1, $v);
		}

		return $statement->execute();

    }

	public function read_item_request_order($item_request_id){
		$sql = "SELECT * FROM item_request_order a 
		JOIN item_request g ON a.item_request_id=g.item_request_id
		JOIN item b ON a.item_id=b.item_id 
		JOIN item_category c ON b.item_category_id=c.item_category_id 
        JOIN item_level d ON b.item_level_id=d.item_level_id 
        JOIN supplier e ON b.item_supplier_id=e.supplier_id 
		JOIN item_type f ON b.item_type_id=f.item_type_id 
		WHERE a.item_request_id=:item_request_id";
		$statement = $this->conn->prepare($sql);
        $statement->bindParam(":item_request_id",$item_request_id);
		$statement->execute();
		if ($statement->rowCount() > 0) {
			return $statement->fetchAll(PDO::FETCH_ASSOC);
		}
		return false;
	}

	public function read_item_request_order_only($item_request_id){
		$sql = "SELECT * FROM item_request_order  
		WHERE item_request_order_id=:item_request_id";
		$statement = $this->conn->prepare($sql);
        $statement->bindParam(":item_request_id",$item_request_id);
		$statement->execute();
		if ($statement->rowCount() > 0) {
			return $statement->fetchAll(PDO::FETCH_ASSOC);
		}
		return false;
	}

	public function remove_item_request_order($item_request_id){
		$sql = "DELETE FROM item_request_order WHERE item_request_id=:item_request_id";
		$statement = $this->conn->prepare($sql);
        $statement->bindParam(":item_request_id",$item_request_id);
		$statement->execute();
		if ($statement->rowCount() > 0) {
            $statement->fetchAll(PDO::FETCH_ASSOC);
		}
		return false;
    }

	// Installment
	public function add_installment(array $data){
		$sql = "INSERT INTO installment ";
		$fields = array_keys($data);
		$vals = array_values($data);

		$sql .= "(".implode(",", $fields).")";
		$arr = array();
		foreach ($fields as $f) {
			$arr[] = "?";
		}
		$sql .= " VALUES(".implode(",", $arr).")";

		$statement = $this->conn->prepare($sql);

		foreach ($vals as $i => $v) {
			$statement->bindValue($i + 1, $v);
		}

		return $statement->execute();

    }

	public function update_item_request_order(array $key_col_vals, array $values) {
        
        $sql = "UPDATE item_request_order SET ";
        $fields = array_keys($values);
        $vals = array_values($values);
        $key_cols = array_keys($key_col_vals);
        $key_vals = array_values($key_col_vals);
        foreach ($fields as $i=>$f) {
            $fields[$i] .= " = ? ";
        }

        $sql .= implode(",", $fields);
        $sql .= " WHERE " ;
        
        foreach ($key_cols as $i=>$f) {
            $key_cols[$i] .= " = ? ";
        }
        
        $sql .= implode(" AND ", $key_cols);

        $statement = $this->conn->prepare($sql);
        foreach ($vals as $i=>$v) {
            $statement->bindValue($i+1, $v);
        }
        
        foreach ($key_vals as $i=>$v) {
           if($i==0){
               $statement->bindValue((count($fields)+$i+1), $v);
            }else{
               $statement->bindValue((count($fields)+$i), $v);
            }
        }
        
       return  $statement->execute();

    }

	// Report Summary
	public function read_report_summary($start,$end){
		$sql = "SELECT * FROM payment
		 WHERE payment_date BETWEEN :start AND :end 
		 ";
		$statement = $this->conn->prepare($sql);
		$statement->bindParam(":start",$start);
		$statement->bindParam(":end",$end);
		$statement->execute();
		if ($statement->rowCount() > 0) {
			return $statement->fetchAll(PDO::FETCH_ASSOC);
		}
		return false;
	}

	public function read_report_summary_for_month($m){
		$sql = "SELECT *,SUM(amount) AS total_amount
		 FROM payment
		 WHERE MONTH(payment_date)=:m  
		 ";
		$statement = $this->conn->prepare($sql);
		$statement->bindParam(":m",$m);
		$statement->execute();
		if ($statement->rowCount() > 0) {
			return $statement->fetchAll(PDO::FETCH_ASSOC);
		}
		return false;
	}


	public function read_report_summary_for_sales(){
		$sql = "SELECT *,SUM(amount) AS total_amount
		FROM payment";
		$statement = $this->conn->prepare($sql);
		$statement->execute();
		if ($statement->rowCount() > 0) {
			return $statement->fetchAll(PDO::FETCH_ASSOC);
		}
		return false;
	}

}