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
							a.*,
							is_available(:utclogin, week, year) AS is_available,
							CASE
								WHEN s.api IS NULL THEN 0 /* student has not yet subscribed */
								WHEN s.validation IS NULL THEN 0.5 /* student has subscribed and wait for valdiation */
								WHEN s.validation THEN 1 /* student subscription has been validated */
								ELSE -1 END AS validation /* student subscription has been declined or other subscription has been accepted the same week */
						FROM vapi a
						LEFT JOIN subscribe s
						ON a.id = s.api
						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 configuration parameters as:
		**        openbegin        |         openend          | isactive
		** ------------------------+--------------------------+----------
		**  mercredi 10 avril 2019 | mercredi 31 juillet 2019 | t
	**/
	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 *, indicator(utclogin, week, year) AS indicator
						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;
	}

	// Validation that $resp is resp of $api
	private function validateResp($resp, $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)
			return true;
		else
			return false;
	}

	/** This function either validate or decline a student participation to an Api.
	** If $validation is true : student participation is unvalidated
	** Else : student participation is declined
	**/
	public function validate($resp, $api, $student, $validation) {
		$today = date('Ymd');

		// Check user is Api responsible
		if ($this->validateResp($resp, $api) == false) {
			echo "<p>Erreur : le responsable $resp n'est pas autorisé à valider l'inscription de $student à l'Api $api</p>" ;
			return -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=:validation, 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);
		$st->bindValue(':validation',$validation,PDO::PARAM_STR);
		$res = $st->execute();

		// Cancel other subscriptions the same week (if validation only)
		if ($validation == 'TRUE') {
			$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 true;
	}

	public function apiStudents($utclogin, $api) {
		$sql = 'SELECT *
						FROM vsubscription
						WHERE id=:api AND resplogin=:utclogin AND validation
						ORDER BY surname, firstname';
		$st = $this->conn->prepare($sql);
		$st->bindValue(':api',$api,PDO::PARAM_STR);
		$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
		$st->execute();
		$res = $st->fetchAll(PDO::FETCH_ASSOC);
		return $res;
	}

	public function apiStudentsAll() {
		$sql = 'SELECT *
						FROM vsubscription
						WHERE validation
						ORDER BY week, code, surname, firstname';
		$st = $this->conn->prepare($sql);
		$st->execute();
		$res = $st->fetchAll(PDO::FETCH_ASSOC);
		return $res;
	}

	public function mailResp() {
		$sql = "SELECT DISTINCT u.email AS email
						FROM vapi a JOIN localuser u ON a.resplogin=u.utclogin
						ORDER BY u.email";
		$st = $this->conn->prepare($sql);
		$st->execute();
		$res = $st->fetchAll(PDO::FETCH_ASSOC);
		return $res;
	}




}