db.php 9.26 KB
Newer Older
stc's avatar
stc committed
1 2 3 4 5 6
<?php

class DB {

	private $conn;

Rémy Huet's avatar
Rémy Huet committed
7
	public function __construct () {
stc's avatar
stc committed
8 9 10 11 12 13 14 15
		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());
		}
	}

16
	public function subList($utclogin) {
17
		$sql = 'SELECT *
Stephane Crozat's avatar
Stephane Crozat committed
18
						FROM vsubscription
19 20
						WHERE	utclogin=:utclogin
						AND (validation OR validation IS NULL)';
stc's avatar
stc committed
21
		$st = $this->conn->prepare($sql);
22 23
		$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
		$st->execute();
stc's avatar
stc committed
24
		$res = $st->fetchAll(PDO::FETCH_ASSOC);
25
		return $res;
stc's avatar
stc committed
26 27
	}

28
	public function apiList($admin, $utclogin) {
stph's avatar
Add #28  
stph committed
29 30 31 32 33 34 35 36 37 38 39
		$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
40
						WHERE semester=:semester AND year=:year';
stc's avatar
stc committed
41
		$st = $this->conn->prepare($sql);
42 43
		$st->bindValue(':semester',$admin->activeSemester(),PDO::PARAM_STR);
		$st->bindValue(':year',$admin->activeYear(),PDO::PARAM_INT);
44
		$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
45
		$st->execute();
stc's avatar
stc committed
46
		$res = $st->fetchAll(PDO::FETCH_ASSOC);
47
		return $res;
stc's avatar
stc committed
48 49
	}

50 51 52 53 54 55 56
	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' ;
57
		$st = $this->conn->prepare($sql);
58
		$st->bindValue(':api',$api,PDO::PARAM_INT);
59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76
		$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;
		}
stc's avatar
stc committed
77 78
	}

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

90
	public function copyUser($utclogin, $surname, $firstname, $email) {
91
		// Function used to create a local copy of each user, in order to use data directly in database
92 93 94 95
		$sql = 'SELECT utclogin FROM localuser WHERE utclogin=:utclogin';
		$st1 = $this->conn->prepare($sql);
		$st1->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
		$st1->execute();
Stephane Crozat's avatar
Fix #9  
Stephane Crozat committed
96
		if (!$st1->fetch(PDO::FETCH_ASSOC)) {
97
			// If user has never logged in yet, he is added to local copy
98
			$sql = 'INSERT INTO localuser(utclogin) VALUES (:utclogin)';
99 100 101
			$st2 = $this->conn->prepare($sql);
			$st2->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
			$res = $st2->execute();
102
		}
103 104 105 106 107 108 109 110 111 112 113
		// 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;
114 115
	}

Stephane Crozat's avatar
Stephane Crozat committed
116 117 118 119 120
	/** Returns configuration parameters as:
		**        openbegin        |         openend          | isactive
		** ------------------------+--------------------------+----------
		**  mercredi 10 avril 2019 | mercredi 31 juillet 2019 | t
	**/
121 122 123 124 125 126 127
	public function config() {
		$sql = 'SELECT * FROM vconfig';
		$st = $this->conn->prepare($sql);
		$st->execute();
		$res = $st->fetch(PDO::FETCH_ASSOC);
		return $res;
	}
128

129 130 131 132 133 134 135 136 137 138 139 140
	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);
141
		$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
142 143 144
		$st->execute();
		$res = $st->fetch(PDO::FETCH_ASSOC);
		return $res['utclogin'];
145 146
	}

147
	public function apiListResp($admin, $utclogin) {
Stephane Crozat's avatar
Stephane Crozat committed
148
		$sql = 'SELECT *, indicator(utclogin, week, year) AS indicator
149
						FROM vsubscription
150
						WHERE semester=:semester AND year=:year AND resplogin=:resp'  ;
151 152 153 154 155 156 157 158 159
		$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;
	}

stph's avatar
Add #28  
stph committed
160 161
	// Validation that $resp is resp of $api
	private function validateResp($resp, $api) {
162 163 164 165 166 167
		$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);
stph's avatar
Add #28  
stph committed
168 169 170 171 172
		if ($res['c']==1)
			return true;
		else
			return false;
	}
173

stph's avatar
Add #28  
stph committed
174 175 176 177 178 179
	/** 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');
180

stph's avatar
Add #28  
stph committed
181 182 183 184 185
		// 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;
		}
186

stph's avatar
Add #28  
stph committed
187 188 189 190 191 192 193 194
		// 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'];
195

stph's avatar
Add #28  
stph committed
196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212
		// 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') {
213 214 215 216
			$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)";
217 218 219 220 221 222 223 224
			$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();
		}
stph's avatar
Add #28  
stph committed
225 226 227 228 229

		$sql = "COMMIT";
		$st = $this->conn->prepare($sql);
		$st->execute();
		return true;
230 231
	}

232
	public function apiStudents($utclogin, $api) {
233 234 235 236
		$sql = 'SELECT *
						FROM vsubscription
						WHERE id=:api AND resplogin=:utclogin AND validation
						ORDER BY surname, firstname';
Stephane Crozat's avatar
Stephane Crozat committed
237 238 239 240 241 242 243 244
		$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;
	}

245 246 247 248
	public function apiStudentsAll() {
		$sql = 'SELECT *
						FROM vsubscription
						WHERE validation
249
						ORDER BY week, code, surname, firstname';
250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267
		$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;
	}



268

stc's avatar
stc committed
269
}