db.php 5.99 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
	public function subToApi($utclogin, $api) {
42
43
44
		$today = date('Ymd');
		$sql = 'INSERT INTO subscribe(utclogin, api, subdate) VALUES (:utclogin, :api, :today)';
		$st = $this->conn->prepare($sql);
45
46
47
48
		$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
		$st->bindValue(':api',$api,PDO::PARAM_INT);
		$st->bindValue(':today',$today,PDO::PARAM_STR);
		$res = $st->execute();
49
		return $res;
stc's avatar
stc committed
50
51
	}

52
	public function unsubToApi($utclogin, $api) {
53
54
		/** Testing validation IS NULL prevent from unsubscribing to validated Api **/
		$sql = 'DELETE FROM subscribe WHERE utclogin=:utclogin AND api=:api AND validation IS NULL';
55
		$st = $this->conn->prepare($sql);
56
57
58
		$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
		$st->bindValue(':api',$api,PDO::PARAM_INT);
		$res = $st->execute();
59
60
		return $res;
	}
61

62
	public function copyUser($utclogin, $surname, $firstname, $email) {
63
64
65
66
67
		// 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
68
		if (!$st1->fetch(PDO::FETCH_ASSOC)) {
69
			// If user has never logged in yet, he is added to local copy
70
			$sql = 'INSERT INTO localuser(utclogin, firstname, surname, email) VALUES (:utclogin, :firstname, :surname, :email)';
71
72
73
74
			$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);
75
			$st2->bindValue(':email',$email,PDO::PARAM_STR);
76
77
78
79
80
			$res = $st2->execute();
			return $res;
		}
		else {
			return 0;
81
82
83
84
85
86
87
88
89
90
91
		}
	}

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

93
94
95
96
97
98
99
100
101
102
103
104
	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);
105
		$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
106
107
108
		$st->execute();
		$res = $st->fetch(PDO::FETCH_ASSOC);
		return $res['utclogin'];
109
110
	}

111
112
113
	public function apiListResp($admin, $utclogin) {
		$sql = 'SELECT *
						FROM vsubscription
114
						WHERE semester=:semester AND year=:year AND resplogin=:resp'  ;
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
		$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();
150
151
152
			$sql = "UPDATE subscribe
							SET validation='TRUE', validationdate=:today
							WHERE api=:api AND utclogin=:utclogin";
153
154
155
156
157
158
159
			$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
160
161
162
163
			$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)";
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
			$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
184
}