db.php

<?php

class DB {

	private $conn;

	public function __construct () {
		include 'connexion.php';
		try {
			$this->conn = new PDO('pgsql:host=localhost;port=5432;dbname=apisub', $db_user, $db_pass);
		} catch (PDOException $e) {
			die('Connection failed: ' . $e->getMessage());
		}
	}

	public function subList($utclogin) {
		$sql = 'SELECT *
						FROM vsubscription
						WHERE	utclogin=:utclogin
						AND (validation OR validation IS NULL)';
		$st = $this->conn->prepare($sql);
		$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
		$st->execute();
		$res = $st->fetchAll(PDO::FETCH_ASSOC);
		return $res;
	}

	public function apiList($admin, $utclogin) {
		$sql = 'SELECT *, is_available(:utclogin, week, year) AS is_available
						FROM vapi
						WHERE semester=:semester AND year=:year';
		$st = $this->conn->prepare($sql);
		$st->bindValue(':semester',$admin->activeSemester(),PDO::PARAM_STR);
		$st->bindValue(':year',$admin->activeYear(),PDO::PARAM_INT);
		$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
		$st->execute();
		$res = $st->fetchAll(PDO::FETCH_ASSOC);
		return $res;
	}

	public function subToApi($admin, $utclogin, $api) {
		// Case 1 : Student had not yet subscribe to this Api and no other Api is validated same week, insertion expected to work
		// Case 2 : Student had already subscribe to this Api, key constraint will prevent from adding new record, so, if subscription has been unvalidated it will remain so AND is_available test will block inserting before previous rule anyway
		// Case 3 : Student had not subscribe to this Api, but another Api has been validated same week, insertion expected to fail (test with is_avaiblable)
		$sql = 'SELECT is_available(:utclogin, week, year) AS is_available
						FROM vapi
						WHERE semester=:semester AND year=:year AND id=:api' ;
		$st = $this->conn->prepare($sql);
		$st->bindValue(':api',$api,PDO::PARAM_INT);
		$st->bindValue(':semester',$admin->activeSemester(),PDO::PARAM_STR);
		$st->bindValue(':year',$admin->activeYear(),PDO::PARAM_INT);
		$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
		$st->execute();
		$res = $st->fetch(PDO::FETCH_ASSOC);
		if ($res['is_available']) {
			$today = date('Ymd');
			$sql = 'INSERT INTO subscribe(utclogin, api, subdate) VALUES (:utclogin, :api, :today)';
			$st = $this->conn->prepare($sql);
			$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
			$st->bindValue(':api',$api,PDO::PARAM_INT);
			$st->bindValue(':today',$today,PDO::PARAM_STR);
			$res = $st->execute();
			return $res;
		}
		else {
			return null;
		}
	}

	public function unsubToApi($utclogin, $api) {
		#TODO add superpower to unsubscribe if admin
		/** Testing validation IS NULL prevent from unsubscribing to validated Api **/
		$sql = 'DELETE FROM subscribe WHERE utclogin=:utclogin AND api=:api AND validation IS NULL';
		$st = $this->conn->prepare($sql);
		$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
		$st->bindValue(':api',$api,PDO::PARAM_INT);
		$res = $st->execute();
		return $res;
	}

	public function copyUser($utclogin, $surname, $firstname, $email) {
		// Function used to create a local copy of each user, in order to use data directly in database
		$sql = 'SELECT utclogin FROM localuser WHERE utclogin=:utclogin';
		$st1 = $this->conn->prepare($sql);
		$st1->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
		$st1->execute();
		if (!$st1->fetch(PDO::FETCH_ASSOC)) {
			// If user has never logged in yet, he is added to local copy
			$sql = 'INSERT INTO localuser(utclogin) VALUES (:utclogin)';
			$st2 = $this->conn->prepare($sql);
			$st2->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
			$res = $st2->execute();
		}
		// Update data in any case
		$sql = 'UPDATE localuser
						SET firstname=:firstname, surname=:surname, email=:email
						WHERE utclogin=:utclogin';
		$st2 = $this->conn->prepare($sql);
		$st2->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
		$st2->bindValue(':firstname',$firstname,PDO::PARAM_STR);
		$st2->bindValue(':surname',$surname,PDO::PARAM_STR);
		$st2->bindValue(':email',$email,PDO::PARAM_STR);
		$res = $st2->execute();
		return $res;
	}

	/** Returns T is subscriptions and unsubscription are allowed **/
	public function config() {
		$sql = 'SELECT * FROM vconfig';
		$st = $this->conn->prepare($sql);
		$st->execute();
		$res = $st->fetch(PDO::FETCH_ASSOC);
		return $res;
	}

	public function isResp ($resplogin) {
		$sql = 'SELECT resplogin FROM api WHERE resplogin=:resplogin';
		$st = $this->conn->prepare($sql);
		$st->bindValue(':resplogin',$resplogin,PDO::PARAM_STR);
		$st->execute();
		$res = $st->fetch(PDO::FETCH_ASSOC);
		return $res['resplogin'];
	}

	public function isAdmin ($utclogin) {
		$sql = 'SELECT utclogin FROM admins WHERE utclogin=:utclogin';
		$st = $this->conn->prepare($sql);
		$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
		$st->execute();
		$res = $st->fetch(PDO::FETCH_ASSOC);
		return $res['utclogin'];
	}

	public function apiListResp($admin, $utclogin) {
		$sql = 'SELECT *
						FROM vsubscription
						WHERE semester=:semester AND year=:year AND resplogin=:resp'  ;
		$st = $this->conn->prepare($sql);
		$st->bindValue(':semester',$admin->activeSemester(),PDO::PARAM_STR);
		$st->bindValue(':year',$admin->activeYear(),PDO::PARAM_INT);
		$st->bindValue(':resp',$utclogin,PDO::PARAM_STR);
		$st->execute();
		$res = $st->fetchAll(PDO::FETCH_ASSOC);
		return $res;
	}

	public function validate($resp, $api, $student) {
		$today = date('Ymd');
		// Validation that $resp is resp of $api
		$sql = 'SELECT COUNT(*) AS c FROM vapi WHERE resplogin=:resp AND id=:api';
		$st = $this->conn->prepare($sql);
		$st->bindValue(':resp',$resp,PDO::PARAM_STR);
		$st->bindValue(':api',$api,PDO::PARAM_STR);
		$st->execute();
		$res = $st->fetch(PDO::FETCH_ASSOC);

		if ($res['c']==1) {

			// Year and week calculation for Api
			$sql = "SELECT year, week FROM vapi WHERE id=:api";
			$st = $this->conn->prepare($sql);
			$st->bindValue(':api',$api,PDO::PARAM_STR);
			$st->execute();
			$res = $st->fetch(PDO::FETCH_ASSOC);
			$year = $res['year'];
			$week = $res['week'];

			// Validate subscription
			$st = $this->conn->prepare($sql);
			$sql = "BEGIN";
			$st = $this->conn->prepare($sql);
			$st->execute();
			$sql = "UPDATE subscribe
							SET validation='TRUE', validationdate=:today
							WHERE api=:api AND utclogin=:utclogin";
			$st = $this->conn->prepare($sql);
			$st->bindValue(':utclogin',$student,PDO::PARAM_STR);
			$st->bindValue(':api',$api,PDO::PARAM_STR);
			$st->bindValue(':today',$today,PDO::PARAM_STR);
			$res = $st->execute();

			// Cancel other subscriptions the same week
			$sql = "UPDATE subscribe
							SET validation='FALSE', validationdate=:today
							WHERE api<>:api AND utclogin=:utclogin
							AND api IN (SELECT id FROM vapi WHERE week=:week AND year=:year)";
			$st = $this->conn->prepare($sql);
			$st->bindValue(':utclogin',$student,PDO::PARAM_STR);
			$st->bindValue(':api',$api,PDO::PARAM_STR);
			$st->bindValue(':today',$today,PDO::PARAM_STR);
			$st->bindValue(':year',$year,PDO::PARAM_STR);
			$st->bindValue(':week',$week,PDO::PARAM_STR);
			$res = $st->execute();
			$sql = "COMMIT";
			$st = $this->conn->prepare($sql);
			$st->execute();
			return $res;
		}
		else {
			echo "<p>Erreur : le responsable $resp n'est pas autorisé à valider l'inscription de $student</p>" ;
			// ajouter le nom de l'Api
			return -1;
			}
	}


}