db.php 3.94 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
	}

Stephane Crozat's avatar
Fix #9    
Stephane Crozat committed
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
	public function getExplanation($utclogin) {
		$sql = 'SELECT explanation
						FROM localuser
						WHERE	utclogin=:utclogin';
		$st = $this->conn->prepare($sql);
		$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
		$st->execute();
		$res = $st->fetch(PDO::FETCH_ASSOC);
		return $res['explanation'];
	}

	public function setExplanation($utclogin,$explanation) {
		$sql = 'UPDATE localuser
						SET explanation=:explanation
						WHERE	utclogin=:utclogin';
		$st = $this->conn->prepare($sql);
		$st->bindValue(':explanation',$explanation,PDO::PARAM_STR);
		$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
		$res = $st->execute();
		return $res;
	}

49
	public function apiList($semester, $year) {
50
		$sql = 'SELECT *
51
						FROM vapi
52
						WHERE semester=:semester AND year=:year';
stc's avatar
stc committed
53
		$st = $this->conn->prepare($sql);
54
55
56
		$st->bindValue(':semester',$semester,PDO::PARAM_STR);
		$st->bindValue(':year',$year,PDO::PARAM_INT);
		$st->execute();
stc's avatar
stc committed
57
		$res = $st->fetchAll(PDO::FETCH_ASSOC);
58
		return $res;
stc's avatar
stc committed
59
60
	}

61
	public function subToApi($utclogin, $api) {
62
63
64
		$today = date('Ymd');
		$sql = 'INSERT INTO subscribe(utclogin, api, subdate) VALUES (:utclogin, :api, :today)';
		$st = $this->conn->prepare($sql);
65
66
67
68
		$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
		$st->bindValue(':api',$api,PDO::PARAM_INT);
		$st->bindValue(':today',$today,PDO::PARAM_STR);
		$res = $st->execute();
69
		return $res;
stc's avatar
stc committed
70
71
	}

72
	public function unsubToApi($utclogin, $api) {
73
74
		$sql = 'DELETE FROM subscribe WHERE utclogin=:utclogin AND api=:api';
		$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
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);
		$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);		
		$st->execute();
		$res = $st->fetch(PDO::FETCH_ASSOC);
		return $res['utclogin'];
128
129
	}

stc's avatar
stc committed
130
}