db.php 7.08 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
83
84
85
86
		// Function used to create a local copy of surname and firstname of each user, in order to link to DFP files without utclogin
		$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, firstname, surname, email) VALUES (:utclogin, :firstname, :surname, :email)';
90
91
92
93
			$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);
94
			$st2->bindValue(':email',$email,PDO::PARAM_STR);
95
96
97
98
99
			$res = $st2->execute();
			return $res;
		}
		else {
			return 0;
100
101
102
103
104
105
106
107
108
109
110
		}
	}

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

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

130
131
132
	public function apiListResp($admin, $utclogin) {
		$sql = 'SELECT *
						FROM vsubscription
133
						WHERE semester=:semester AND year=:year AND resplogin=:resp'  ;
134
135
136
137
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
		$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();
169
170
171
			$sql = "UPDATE subscribe
							SET validation='TRUE', validationdate=:today
							WHERE api=:api AND utclogin=:utclogin";
172
173
174
175
176
177
178
			$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
179
180
181
182
			$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)";
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
			$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;
			}
	}


stc's avatar
stc committed
203
}