db.php 5.7 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
						WHERE	utclogin=:utclogin';
stc's avatar
stc committed
20
		$st = $this->conn->prepare($sql);
21
22
		$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
		$st->execute();
stc's avatar
stc committed
23
		$res = $st->fetchAll(PDO::FETCH_ASSOC);
24
		return $res;
stc's avatar
stc committed
25
26
	}

27
	public function apiList($admin) {
28
		$sql = 'SELECT *
29
						FROM vapi
30
						WHERE semester=:semester AND year=:year';
stc's avatar
stc committed
31
		$st = $this->conn->prepare($sql);
32
33
		$st->bindValue(':semester',$admin->activeSemester(),PDO::PARAM_STR);
		$st->bindValue(':year',$admin->activeYear(),PDO::PARAM_INT);
34
		$st->execute();
stc's avatar
stc committed
35
		$res = $st->fetchAll(PDO::FETCH_ASSOC);
36
		return $res;
stc's avatar
stc committed
37
38
	}

39
	public function subToApi($utclogin, $api) {
40
41
42
		$today = date('Ymd');
		$sql = 'INSERT INTO subscribe(utclogin, api, subdate) VALUES (:utclogin, :api, :today)';
		$st = $this->conn->prepare($sql);
43
44
45
46
		$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
		$st->bindValue(':api',$api,PDO::PARAM_INT);
		$st->bindValue(':today',$today,PDO::PARAM_STR);
		$res = $st->execute();
47
		return $res;
stc's avatar
stc committed
48
49
	}

50
	public function unsubToApi($utclogin, $api) {
51
52
		$sql = 'DELETE FROM subscribe WHERE utclogin=:utclogin AND api=:api';
		$st = $this->conn->prepare($sql);
53
54
55
		$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
		$st->bindValue(':api',$api,PDO::PARAM_INT);
		$res = $st->execute();
56
57
		return $res;
	}
58

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

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

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

108
109
110
111
112
113
114
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
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
	public function apiListResp($admin, $utclogin) {
		$sql = 'SELECT *
						FROM vsubscription
						WHERE semester=:semester AND year=:year AND resplogin=:resp' ;
		$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();
			$sql = "UPDATE subscribe SET validation='TRUE', 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);
			$res = $st->execute();

			// Cancel other subscriptions the same week
			$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)";
			$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
178
}