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
}