db-functions.php 16.2 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

Gabriel Moreau's avatar
Gabriel Moreau committed
72
function get_datasheet_basepath() {
73
74
75
76
77
	return './data/datasheet';
}

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

78
79
80
81
82
83
84
85
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
86
// ---------------------------------------------------------------------
87

88
89
90
91
92
93
94
95
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'];
}

96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
// ---------------------------------------------------------------------

function set_datasheet_new($pdo, $equipment_id, $equipment_name, $tmp_file) {
	if (!preg_match('/\.pdf$/i', $tmp_file))
		return false;

	$new_datasheet_path = './data/datasheet';
	if (!is_dir($new_datasheet_path))
		mkdir($new_datasheet_path, 0755);

	$datasheet_filename_kebab = string_to_filename_kebab($equipment_name);

	$sql1 = 'INSERT INTO datasheet (description, id_equipment) VALUES (?, ?);';
	$stmt1 = $pdo->prepare($sql1);
	$stmt1->execute(array($equipment_name, $equipment_id));
	$id_datasheet = $pdo->lastInsertId();

	$sub_path = $id_datasheet.'-'.random_string(8);
	$sql2 = 'UPDATE datasheet SET pathname = ? WHERE id = ?;';
	$stmt2 = $pdo->prepare($sql2);
	$stmt2->execute(array($sub_path.'/'.$datasheet_filename_kebab.'.pdf', $id_datasheet));

	$new_dir = $new_datasheet_path.'/'.$sub_path;
	if (!is_dir($new_dir))
		mkdir($new_dir, 0755);
	move_uploaded_file($tmp_file, $new_dir.'/'.$datasheet_filename_kebab.'.pdf');

	return $id_datasheet;
}

126
127
// ---------------------------------------------------------------------
// Equipment
Gabriel Moreau's avatar
Gabriel Moreau committed
128
// ---------------------------------------------------------------------
129

130
function get_equipment_by_id($pdo, $id) {
131
132
133
	$sql = 'SELECT id, nom FROM Listing WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
134
135
136
137
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
138
139
}

Gabriel Moreau's avatar
Gabriel Moreau committed
140
// ---------------------------------------------------------------------
141

142
function get_equipment_all_by_id($pdo, $id) {
143
144
145
	$sql = 'SELECT * FROM Listing WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
146
147
148
149
150
151
	$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
152
// ---------------------------------------------------------------------
153
154

function get_equipment_listshort($pdo) {
155
	$sql = 'SELECT id, nom FROM Listing ORDER BY nom;';
156
157
	$stmt = $pdo->prepare($sql);
	$stmt->execute();
158
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
159
	return $result_fetch;
160
161
}

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

Gabriel Moreau's avatar
Gabriel Moreau committed
164
function set_equipment_new($pdo, $categorie, $nom, $modele, $feature, $equipe, $fourn, $achat, $tech, $reparation, $accessoires, $inventaire, $notice, $barcode, $loanable) {
Gabriel Moreau's avatar
Gabriel Moreau committed
165
	$sql = 'INSERT INTO Listing (categorie, nom, modele, gamme, equipe, fournisseur, achat, responsable, reparation, accessoires, inventaire, notice, barcode, loanable)';
166
	$sql .=            ' VALUES (?,         ?,   ?,      ?,     ?,      ?,           ?,     ?,           ?,          ?,           ?,          ?,      ?,       ?);';
167
	$stmt = $pdo->prepare($sql);
Gabriel Moreau's avatar
Gabriel Moreau committed
168
	$status = $stmt->execute(array($categorie, $nom, $modele, $feature, $equipe, $fourn, $achat, $tech, $reparation, $accessoires, $inventaire, $notice, $barcode, $loanable));
169
170
	$err_msg = '';
	if (!$status)
Gabriel Moreau's avatar
Gabriel Moreau committed
171
		$err_msg = $stmt->errorInfo()[2];
172
	return array($pdo->lastInsertId(), $err_msg);
173
174
175
176
}

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

177
178
179
180
181
182
183
184
185
186
187
188
function set_equipment_update($pdo, $id_equipment, $categorie, $nom, $modele, $feature, $equipe, $fourn, $achat, $tech, $reparation, $accessoires, $inventaire, $notice, $barcode, $loanable) {
	$sql = 'UPDATE Listing SET categorie = ?, nom = ?, modele = ?, gamme = ?, equipe = ?, fournisseur = ?, achat = ?, responsable = ?, reparation = ?, accessoires = ?, inventaire = ?, notice = ?, barcode = ?, loanable = ? WHERE id = ?;)';
	$stmt = $pdo->prepare($sql);
	$status = $stmt->execute(array($categorie, $nom, $modele, $feature, $equipe, $fourn, $achat, $tech, $reparation, $accessoires, $inventaire, $notice, $barcode, $loanable, $id_equipment));
	$err_msg = '';
	if (!$status)
		$err_msg = $stmt->errorInfo()[2];
	return $err_msg;
}

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

189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
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
// ---------------------------------------------------------------------

204
function get_category_by_id($pdo, $id) {
205
206
207
	$sql = 'SELECT id, nom FROM categorie WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
208
209
	$category_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $category_fetch[0];
210
211
}

Gabriel Moreau's avatar
Gabriel Moreau committed
212
// ---------------------------------------------------------------------
213

Gabriel Moreau's avatar
Gabriel Moreau committed
214
215
216
217
218
219
220
221
222
223
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;
}

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

224
225
226
227
228
229
230
231
232
233
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;
}

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

234
235
236
237
238
239
240
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
241
242
243
244
245
246
247
248
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;
}

249
250
// ---------------------------------------------------------------------
// Loan
251
252
// ---------------------------------------------------------------------

Gabriel Moreau's avatar
Gabriel Moreau committed
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
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;
}

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

278
279
280
281
282
283
284
285
286
287
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
288
289
290
291
292
293
294
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();
}

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

function set_loan_update($pdo, $id_loan, $id_equipment, $id_team, $date_begin, $date_end, $comment) {
	$sql = 'UPDATE pret SET nom = ?, equipe = ?, emprunt = ?, retour = ?, commentaire = ? WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id_equipment, $id_team, $date_begin, $date_end, $comment, $id_loan));
}

303
304
// ---------------------------------------------------------------------
// Supplier
Gabriel Moreau's avatar
Gabriel Moreau committed
305
306
// ---------------------------------------------------------------------

307
308
function get_supplier_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM fournisseurs WHERE id = ?;';
309
310
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
311
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
312
	return $supplier_fetch[0];
313
314
}

Gabriel Moreau's avatar
Gabriel Moreau committed
315
// ---------------------------------------------------------------------
316

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

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

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

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

337
338
339
340
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 ?;';
341
	$stmt = $pdo->prepare($sql);
342
	$stmt->execute(array($find, $find));
343
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
344
	return $supplier_fetch;
345
346
}

347
348
// ---------------------------------------------------------------------
// Team
Gabriel Moreau's avatar
Gabriel Moreau committed
349
// ---------------------------------------------------------------------
350

351
352
function get_team_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM equipe WHERE id = ?;';
353
354
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
355
356
357
358
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
359
360
}

Gabriel Moreau's avatar
Gabriel Moreau committed
361
// ---------------------------------------------------------------------
362

363
364
365
366
367
368
369
370
371
372
373
374
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;
}

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

375
function get_team_listshort($pdo) {
376
	$sql = 'SELECT id, nom FROM equipe ORDER BY nom;';
377
	$stmt = $pdo->prepare($sql);
378
	$stmt->execute();
379
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
380
	return $team_fetch;
381
382
}

Gabriel Moreau's avatar
Gabriel Moreau committed
383
// ---------------------------------------------------------------------
384

385
function get_team_listall($pdo) {
386
	$sql = 'SELECT * FROM equipe ORDER BY nom;';
387
	$stmt = $pdo->prepare($sql);
388
	$stmt->execute();
389
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
390
	return $team_fetch;
391
392
}

Gabriel Moreau's avatar
Gabriel Moreau committed
393
// ---------------------------------------------------------------------
394

395
function get_team_with_appareil($pdo) {
396
	$sql = 'SELECT DISTINCT equipe.id, equipe.nom FROM equipe INNER JOIN Listing ON equipe.id = Listing.equipe ORDER BY equipe.nom;';
397
	$stmt = $pdo->prepare($sql);
398
	$stmt->execute();
399
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
400
	return $team_fetch;
401
402
}

403
404
// ---------------------------------------------------------------------
// User
Gabriel Moreau's avatar
Gabriel Moreau committed
405
// ---------------------------------------------------------------------
406
407
408
409
410

function get_user_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM users WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
411
412
413
414
415
416
	$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
417
// ---------------------------------------------------------------------
418
419
420
421
422
423
424
425
426

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;
427
428
}

Gabriel Moreau's avatar
Gabriel Moreau committed
429
// ---------------------------------------------------------------------
430

431
432
433
434
435
436
437
438
439
440
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
441
// ---------------------------------------------------------------------
442

443
444
445
446
447
448
449
450
451
452
453
454
455
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;
}

456
457
458
459
460
// ---------------------------------------------------------------------

function get_user_listshort_with_right($pdo, $level_min=1) {
	$sql = 'SELECT id, nom FROM users WHERE valid = 1 and level >= ?;';
	$stmt = $pdo->prepare($sql);
Gabriel Moreau's avatar
Gabriel Moreau committed
461
    $stmt->execute(array($level_min));
462
463
464
465
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

466
467
// ---------------------------------------------------------------------
// Version
468
469
// ---------------------------------------------------------------------

470
471
472
473
function get_version_by_name($pdo, $name) {
	$sql = 'SELECT version FROM version WHERE name = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($name));
474
	$version_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
475
476
477
478
	if (count($version_fetch) > 0)
		return $version_fetch[0];
	return false;
}
Gabriel Moreau's avatar
Gabriel Moreau committed
479
// ---------------------------------------------------------------------
480
481
482
483
484
485
486
487
488

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

489
?>