db.php 8.32 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
29
	public function apiList($admin, $utclogin) {
		$sql = 'SELECT *, is_available(:utclogin, week, year) AS is_available
30
						FROM vapi
31
						WHERE semester=:semester AND year=:year';
stc's avatar
stc committed
32
		$st = $this->conn->prepare($sql);
33
34
		$st->bindValue(':semester',$admin->activeSemester(),PDO::PARAM_STR);
		$st->bindValue(':year',$admin->activeYear(),PDO::PARAM_INT);
35
		$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
36
		$st->execute();
stc's avatar
stc committed
37
		$res = $st->fetchAll(PDO::FETCH_ASSOC);
38
		return $res;
stc's avatar
stc committed
39
40
	}

41
42
43
44
45
46
47
	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' ;
48
		$st = $this->conn->prepare($sql);
49
		$st->bindValue(':api',$api,PDO::PARAM_INT);
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
		$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
68
69
	}

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

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

	/** 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;
	}
115

116
117
118
119
120
121
122
123
124
125
126
127
	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);
128
		$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
129
130
131
		$st->execute();
		$res = $st->fetch(PDO::FETCH_ASSOC);
		return $res['utclogin'];
132
133
	}

134
	public function apiListResp($admin, $utclogin) {
Stephane Crozat's avatar
Stephane Crozat committed
135
		$sql = 'SELECT *, indicator(utclogin, week, year) AS indicator
136
						FROM vsubscription
137
						WHERE semester=:semester AND year=:year AND resplogin=:resp'  ;
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
		$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();
173
174
175
			$sql = "UPDATE subscribe
							SET validation='TRUE', validationdate=:today
							WHERE api=:api AND utclogin=:utclogin";
176
177
178
179
180
181
182
			$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
183
184
185
186
			$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)";
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
			$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;
			}
	}

Stephane Crozat's avatar
Stephane Crozat committed
206
<<<<<<< HEAD
Stephane Crozat's avatar
Stephane Crozat committed
207
208
209
210
211
212
	public function apiStudents($utclogin, $api) {		
		if ($this->isAdmin($utclogin)) {
			$sql = 'SELECT *
							FROM vsubscription
							WHERE id=:api AND resplogin=:utclogin AND validation
							ORDER BY surname, firstname' ; //TODO donner accès aux admins même si ne sont pas les resp
Stephane Crozat's avatar
Stephane Crozat committed
213
=======
Stephane Crozat's avatar
Stephane Crozat committed
214
215
216
217
218
219
	public function apiStudents($utclogin, $api) {
		if ($this->isAdmin($utclogin)) {
			$sql = 'SELECT *
							FROM vsubscription
							WHERE id=:api AND resplogin=:utclogin
							ORDER BY surname, firstname' ; //TODO
Stephane Crozat's avatar
Stephane Crozat committed
220
>>>>>>> 10d1bbf37f4ad6b12420efdfcb6831c4670fe24b
Stephane Crozat's avatar
Stephane Crozat committed
221
222
223
224
		}
		else {
			$sql = 'SELECT *
							FROM vsubscription
Stephane Crozat's avatar
Stephane Crozat committed
225
<<<<<<< HEAD
Stephane Crozat's avatar
Stephane Crozat committed
226
							WHERE id=:api AND resplogin=:utclogin AND validation
Stephane Crozat's avatar
Stephane Crozat committed
227
=======
Stephane Crozat's avatar
Stephane Crozat committed
228
							WHERE id=:api AND resplogin=:utclogin
Stephane Crozat's avatar
Stephane Crozat committed
229
>>>>>>> 10d1bbf37f4ad6b12420efdfcb6831c4670fe24b
Stephane Crozat's avatar
Stephane Crozat committed
230
231
232
233
234
235
236
237
238
239
							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;
	}

240

stc's avatar
stc committed
241
}