db-functions.php 13.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;
}

68
69
// ---------------------------------------------------------------------
// Datasheet
Gabriel Moreau's avatar
Gabriel Moreau committed
70
// ---------------------------------------------------------------------
71

72
73
74
75
76
77
78
79
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
80
// ---------------------------------------------------------------------
81

82
83
84
85
86
87
88
89
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'];
}

90
91
// ---------------------------------------------------------------------
// Equipment
Gabriel Moreau's avatar
Gabriel Moreau committed
92
// ---------------------------------------------------------------------
93

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

Gabriel Moreau's avatar
Gabriel Moreau committed
104
// ---------------------------------------------------------------------
105

106
function get_equipment_all_by_id($pdo, $id) {
107
108
109
	$sql = 'SELECT * FROM Listing WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
110
111
112
113
114
115
	$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
116
// ---------------------------------------------------------------------
117
118

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

Gabriel Moreau's avatar
Gabriel Moreau committed
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
function set_equipment_new($pdo, $categorie, $nom, $modele, $gamme, $equipe, $fourn, $achat, $tech, $reparation, $accessoires, $inventaire, $notice, $barcode, $loanable) {
	$sql = 'INSERT INTO Listing (categorie, nom, modele, gamme, equipe, fournisseur, achat, responsable, reparation, accessoires, inventaire, notice, barcode, loanable)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($categorie, $nom, $modele, $gamme, $equipe, $fourn, $achat, $tech, $reparation, $accessoires, $inventaire, $notice, $barcode, $loanable));
	return $pdo->lastInsertId();
}

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

function del_equipment($pdo, $id) {
	$sql = 'DELETE LOW_PRIORITY FROM Listing WHERE id = ? LIMIT 1;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
	$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (!$result)
		return false;
	else
		return true;
}

// ---------------------------------------------------------------------
// Category
// ---------------------------------------------------------------------

153
function get_category_by_id($pdo, $id) {
154
155
156
	$sql = 'SELECT id, nom FROM categorie WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
157
158
	$category_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $category_fetch[0];
159
160
}

Gabriel Moreau's avatar
Gabriel Moreau committed
161
// ---------------------------------------------------------------------
162

Gabriel Moreau's avatar
Gabriel Moreau committed
163
164
165
166
167
168
169
170
171
172
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;
}

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

173
174
175
176
177
178
179
180
181
182
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;
}

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

183
184
185
186
187
188
189
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
190
191
192
193
194
195
196
197
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;
}

198
199
// ---------------------------------------------------------------------
// Loan
200
201
// ---------------------------------------------------------------------

Gabriel Moreau's avatar
Gabriel Moreau committed
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
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;
}

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

227
228
229
230
231
232
233
234
235
236
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
237
238
239
240
241
242
243
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();
}

244
245
// ---------------------------------------------------------------------
// Supplier
Gabriel Moreau's avatar
Gabriel Moreau committed
246
247
// ---------------------------------------------------------------------

248
249
function get_supplier_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM fournisseurs WHERE id = ?;';
250
251
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
252
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
253
	return $supplier_fetch[0];
254
255
}

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

258
function get_supplier_listshort($pdo) {
259
	$sql = 'SELECT id, nom FROM fournisseurs ORDER BY nom;';
260
	$stmt = $pdo->prepare($sql);
261
	$stmt->execute();
262
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
263
	return $supplier_fetch;
264
265
}

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

268
function get_supplier_listall($pdo) {
269
	$sql = 'SELECT * FROM fournisseurs ORDER BY nom;';
270
	$stmt = $pdo->prepare($sql);
271
	$stmt->execute();
272
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
273
	return $supplier_fetch;
274
275
}

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

278
279
280
281
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 ?;';
282
	$stmt = $pdo->prepare($sql);
283
	$stmt->execute(array($find, $find));
284
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
285
	return $supplier_fetch;
286
287
}

288
289
// ---------------------------------------------------------------------
// Team
Gabriel Moreau's avatar
Gabriel Moreau committed
290
// ---------------------------------------------------------------------
291

292
293
function get_team_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM equipe WHERE id = ?;';
294
295
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
296
297
298
299
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
300
301
}

Gabriel Moreau's avatar
Gabriel Moreau committed
302
// ---------------------------------------------------------------------
303

304
305
306
307
308
309
310
311
312
313
314
315
function get_team_all_by_id($pdo, $id) {
	$sql = 'SELECT * FROM equipe 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;
}

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

316
function get_team_listshort($pdo) {
317
	$sql = 'SELECT id, nom FROM equipe ORDER BY nom;';
318
	$stmt = $pdo->prepare($sql);
319
	$stmt->execute();
320
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
321
	return $team_fetch;
322
323
}

Gabriel Moreau's avatar
Gabriel Moreau committed
324
// ---------------------------------------------------------------------
325

326
function get_team_listall($pdo) {
327
	$sql = 'SELECT * FROM equipe ORDER BY nom;';
328
	$stmt = $pdo->prepare($sql);
329
	$stmt->execute();
330
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
331
	return $team_fetch;
332
333
}

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

336
337
function get_team_with_appareil($pdo) {
	$sql = 'SELECT id, nom FROM equipe INNER JOIN Listing ON equipe.id = Listing.equipe;';
338
	$stmt = $pdo->prepare($sql);
339
	$stmt->execute();
340
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
341
	return $team_fetch;
342
343
}

344
345
// ---------------------------------------------------------------------
// User
Gabriel Moreau's avatar
Gabriel Moreau committed
346
// ---------------------------------------------------------------------
347
348
349
350
351

function get_user_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM users WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
352
353
354
355
356
357
	$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
358
// ---------------------------------------------------------------------
359
360
361
362
363
364
365
366
367

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;
368
369
}

Gabriel Moreau's avatar
Gabriel Moreau committed
370
// ---------------------------------------------------------------------
371

372
373
374
375
376
377
378
379
380
381
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
382
// ---------------------------------------------------------------------
383

384
385
386
387
388
389
390
391
392
393
394
395
396
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;
}

397
398
// ---------------------------------------------------------------------
// Version
399
400
// ---------------------------------------------------------------------

401
402
403
404
function get_version_by_name($pdo, $name) {
	$sql = 'SELECT version FROM version WHERE name = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($name));
405
	$version_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
406
407
408
409
	if (count($version_fetch) > 0)
		return $version_fetch[0];
	return false;
}
Gabriel Moreau's avatar
Gabriel Moreau committed
410
// ---------------------------------------------------------------------
411
412
413
414
415
416
417
418
419

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));
}

420
?>