db-functions.php 11.3 KB
Newer Older
1
2
3
4
<?php

require_once('connect.php');

Gabriel Moreau's avatar
Gabriel Moreau committed
5
// ---------------------------------------------------------------------
6

Gabriel Moreau's avatar
Gabriel Moreau committed
7
// connexion au serveur mySQL
8
9
10
11
12
13
14
15
16
17
18
19
20

function connect_db() {
	try{
		$pdo = new PDO('mysql:host='.GESTEX_DB_SERVER.'; dbname='.GESTEX_DB_DATABASE, GESTEX_DB_USER, GESTEX_DB_PASSWORD);
	}
	catch(PDOException $exception){
		error_log('Connection error: '.$exception->getMessage());
		echo $exception->getMessage();
		return false;
	}
	return $pdo;
}

Gabriel Moreau's avatar
Gabriel Moreau committed
21
// ---------------------------------------------------------------------
22
23
24
25
26
27
28

function query_db($statement) {
	$result   = mysql_query($statement) or die("<pre>\n\nCan't perform query: " . mysql_error() . " \n\n$statement\n\n</pre>");
	$num_rows = numrows_db($result);
	return array($result, $num_rows);
}

Gabriel Moreau's avatar
Gabriel Moreau committed
29
// ---------------------------------------------------------------------
30
31
32
33
34

function numrows_db($result) {
	return @mysql_num_rows($result);
}

Gabriel Moreau's avatar
Gabriel Moreau committed
35
// ---------------------------------------------------------------------
36
37
38
39
40
41
42
43

function result_db($result,$i=-1) {
	if ($i >= 0) {
		@mysql_data_seek($result,$i);
	}
	return mysql_fetch_array($result);
}

Gabriel Moreau's avatar
Gabriel Moreau committed
44
// ---------------------------------------------------------------------
45
46
47
48
49

function last_id_db() {
	return mysql_insert_id();
}

Gabriel Moreau's avatar
Gabriel Moreau committed
50
// ---------------------------------------------------------------------
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

function check_val_in_db($pdo, $table, $col, $value) {
	//teste l'existence de $value dans le champ $col de la table $table
	//echo "check in:".$table.":".$col." for ".$value."<br />";
	$sql = 'SELECT * FROM ? WHERE ? = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($table, $col, $value));
	$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

	///echo "check_val:".numrows_db($reponse)."<br />";
	//renvoie 0 si non trouve
	//renvoie le nbre d'occurences autrement
	if (count($result) > 0)
		return true;
	return false;
}

Gabriel Moreau's avatar
Gabriel Moreau committed
68
// ---------------------------------------------------------------------
69

70
71
72
73
74
75
76
77
function get_datasheet_listall_by_equipment($pdo, $id_equipment) {
	$sql = 'SELECT * FROM datasheet WHERE id_equipment = ?;' ;
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id_equipment));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

Gabriel Moreau's avatar
Gabriel Moreau committed
78
// ---------------------------------------------------------------------
79

80
81
82
83
84
85
86
87
function get_datasheet_count_by_equipment($pdo, $id_equipment) {
	$sql = 'SELECT COUNT(*) as count FROM datasheet WHERE id_equipment = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id_equipment));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch[0]['count'];
}

Gabriel Moreau's avatar
Gabriel Moreau committed
88
// ---------------------------------------------------------------------
89

90
function get_equipment_by_id($pdo, $id) {
91
92
93
	$sql = 'SELECT id, nom FROM Listing WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
94
95
96
97
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
98
99
}

Gabriel Moreau's avatar
Gabriel Moreau committed
100
// ---------------------------------------------------------------------
101

102
function get_equipment_all_by_id($pdo, $id) {
103
104
105
	$sql = 'SELECT * FROM Listing WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
106
107
108
109
110
111
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
}

Gabriel Moreau's avatar
Gabriel Moreau committed
112
// ---------------------------------------------------------------------
113
114

function get_equipment_listshort($pdo) {
115
	$sql = 'SELECT id, nom FROM Listing ORDER BY nom;';
116
117
	$stmt = $pdo->prepare($sql);
	$stmt->execute();
118
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
119
	return $result_fetch;
120
121
}

Gabriel Moreau's avatar
Gabriel Moreau committed
122
// ---------------------------------------------------------------------
123

124
function get_category_by_id($pdo, $id) {
125
126
127
	$sql = 'SELECT id, nom FROM categorie WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
128
129
	$category_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $category_fetch[0];
130
131
}

Gabriel Moreau's avatar
Gabriel Moreau committed
132
// ---------------------------------------------------------------------
133

Gabriel Moreau's avatar
Gabriel Moreau committed
134
135
136
137
138
139
140
141
142
143
function get_category_listshort($pdo) {
	$sql = 'SELECT id, nom FROM categorie ORDER BY nom;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute();
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

// ---------------------------------------------------------------------

144
145
146
147
148
149
150
151
152
153
function check_category_by_name($pdo, $name) {
	$sql = 'SELECT COUNT(*) as count FROM categorie WHERE nom = ?';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($name));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch[0]['count'] > 0 ? true : false;
}

// ---------------------------------------------------------------------

154
155
156
157
158
159
160
function set_category_new($pdo, $name) {
	$sql = 'INSERT INTO categorie (nom) VALUE (?);';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($name));
	return $pdo->lastInsertId();
}

Gabriel Moreau's avatar
Gabriel Moreau committed
161
162
163
164
165
166
167
168
function del_category_by_id($pdo, $id) {
	$sql = 'DELETE LOW_PRIORITY FROM categorie WHERE id = ? LIMIT 1';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch ? true : false;
}

169
170
// ---------------------------------------------------------------------

Gabriel Moreau's avatar
Gabriel Moreau committed
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
function get_loan_all_by_id_equipment($pdo, $id_equipment) {
	// recupere l'appareil via l'id qui est mis dans un champs texte (nom) !
	$sql = 'SELECT * FROM pret WHERE nom = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id_equipment));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
}

// ---------------------------------------------------------------------

function get_loan_all_by_id($pdo, $id) {
	$sql = 'SELECT * FROM pret WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
}

// ---------------------------------------------------------------------

196
197
198
199
200
201
202
203
204
205
function get_loan_listall($pdo) {
	$sql = 'SELECT * FROM pret;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute();
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

// ---------------------------------------------------------------------

Gabriel Moreau's avatar
Gabriel Moreau committed
206
207
208
209
210
211
212
213
214
function set_loan_new($pdo, $id_equipment, $id_team, $date_begin, $date_end, $comment) {
	$sql = 'INSERT INTO pret (nom, equipe, emprunt, retour, commentaire) VALUES (?, ?, ?, ?, ?);';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id_equipment, $id_team, $date_begin, $date_end, $comment));
	return $pdo->lastInsertId();
}

// ---------------------------------------------------------------------

215
216
function get_supplier_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM fournisseurs WHERE id = ?;';
217
218
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
219
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
220
	return $supplier_fetch[0];
221
222
}

Gabriel Moreau's avatar
Gabriel Moreau committed
223
// ---------------------------------------------------------------------
224

225
function get_supplier_listshort($pdo) {
226
	$sql = 'SELECT id, nom FROM fournisseurs ORDER BY nom;';
227
	$stmt = $pdo->prepare($sql);
228
	$stmt->execute();
229
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
230
	return $supplier_fetch;
231
232
}

Gabriel Moreau's avatar
Gabriel Moreau committed
233
// ---------------------------------------------------------------------
234

235
function get_supplier_listall($pdo) {
236
	$sql = 'SELECT * FROM fournisseurs ORDER BY nom;';
237
	$stmt = $pdo->prepare($sql);
238
	$stmt->execute();
239
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
240
	return $supplier_fetch;
241
242
}

Gabriel Moreau's avatar
Gabriel Moreau committed
243
// ---------------------------------------------------------------------
244

245
246
247
248
function get_supplier_find($pdo, $find='') {
	if (empty($find) or ($find === true))
		return get_supplier_listall($pdo);
	$sql = 'SELECT * FROM fournisseurs WHERE nom RLIKE ? OR descr RLIKE ?;';
249
	$stmt = $pdo->prepare($sql);
250
	$stmt->execute(array($find, $find));
251
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
252
	return $supplier_fetch;
253
254
}

Gabriel Moreau's avatar
Gabriel Moreau committed
255
// ---------------------------------------------------------------------
256

257
258
function get_team_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM equipe WHERE id = ?;';
259
260
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
261
262
263
264
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
265
266
}

Gabriel Moreau's avatar
Gabriel Moreau committed
267
// ---------------------------------------------------------------------
268

269
function get_team_listshort($pdo) {
270
	$sql = 'SELECT id, nom FROM equipe ORDER BY nom;';
271
	$stmt = $pdo->prepare($sql);
272
	$stmt->execute();
273
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
274
	return $team_fetch;
275
276
}

Gabriel Moreau's avatar
Gabriel Moreau committed
277
// ---------------------------------------------------------------------
278

279
function get_team_listall($pdo) {
280
	$sql = 'SELECT * FROM equipe ORDER BY nom;';
281
	$stmt = $pdo->prepare($sql);
282
	$stmt->execute();
283
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
284
	return $team_fetch;
285
286
}

Gabriel Moreau's avatar
Gabriel Moreau committed
287
// ---------------------------------------------------------------------
288

289
290
function get_team_with_appareil($pdo) {
	$sql = 'SELECT id, nom FROM equipe INNER JOIN Listing ON equipe.id = Listing.equipe;';
291
	$stmt = $pdo->prepare($sql);
292
	$stmt->execute();
293
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
294
	return $team_fetch;
295
296
}

Gabriel Moreau's avatar
Gabriel Moreau committed
297
// ---------------------------------------------------------------------
298
299
300
301
302

function get_user_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM users WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
303
304
305
306
307
308
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
}

Gabriel Moreau's avatar
Gabriel Moreau committed
309
// ---------------------------------------------------------------------
310
311
312
313
314
315
316
317
318

function get_user_all_by_id($pdo, $id) {
	$sql = 'SELECT * FROM users WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
319
320
}

Gabriel Moreau's avatar
Gabriel Moreau committed
321
// ---------------------------------------------------------------------
322

323
324
325
326
327
328
329
330
331
332
function get_user_all_by_login($pdo, $login) {
	$sql = 'SELECT * FROM users WHERE loggin = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($login));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
}

Gabriel Moreau's avatar
Gabriel Moreau committed
333
// ---------------------------------------------------------------------
334

335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
function get_user_listall_by_logged_level($pdo, $logged_level) {
	if ($logged_level > 3)       // lorsqu'on est haut place, on voit tout le monde
		$sql = 'SELECT * FROM users;';
	else if ($logged_level == 3) // losrqu'on est de niveau 3, on voit tout le monde sauf les users de plus haut level
		$sql = 'SELECT * FROM users WHERE level < 4;';
	else                         // lorsqu'on est < 3, on voit tout le monde sauf le suser de level > 3 et les users non valides
		$sql = 'SELECT * FROM users WHERE valid = 1 and level < 3;';
	$stmt = $pdo->prepare($sql);
    $stmt->execute();
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

// ---------------------------------------------------------------------

350
351
352
353
function get_version_by_name($pdo, $name) {
	$sql = 'SELECT version FROM version WHERE name = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($name));
354
	$version_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
355
356
357
358
	if (count($version_fetch) > 0)
		return $version_fetch[0];
	return false;
}
Gabriel Moreau's avatar
Gabriel Moreau committed
359
// ---------------------------------------------------------------------
360
361
362
363
364
365
366
367
368

function set_version_by_name($pdo, $name, $version) {
	$sql = 'INSERT INTO version (name, version) VALUES (?, ?);';
	if (get_version_by_name($pdo, $name))
		$sql = 'UPDATE version SET version = ? WHERE name = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($name, $version));
}

369
?>