db-functions.php 22.7 KB
Newer Older
Gabriel Moreau's avatar
Gabriel Moreau committed
1
<?php if (!$web_page) exit() ?>
2

Gabriel Moreau's avatar
Gabriel Moreau committed
3
<?php
4
5
require_once('connect.php');

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

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

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
22
// ---------------------------------------------------------------------
23

24
25
26
27
28
29
30
31
32
33
function connect_db_or_alert() {
	if ($pdo = connect_db())
		return $pdo;

	include_once('include/alert-db.php');
	exit();
}

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

34
35
36
37
38
39
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
40
// ---------------------------------------------------------------------
41
42
43
44
45

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

Gabriel Moreau's avatar
Gabriel Moreau committed
46
// ---------------------------------------------------------------------
47
48
49
50
51
52
53
54

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
55
// ---------------------------------------------------------------------
56
57
58
59
60

function last_id_db() {
	return mysql_insert_id();
}

Gabriel Moreau's avatar
Gabriel Moreau committed
61
// ---------------------------------------------------------------------
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

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

79
80
// ---------------------------------------------------------------------
// Datasheet
Gabriel Moreau's avatar
Gabriel Moreau committed
81
// ---------------------------------------------------------------------
82

Gabriel Moreau's avatar
Gabriel Moreau committed
83
function get_datasheet_basepath() {
84
85
86
87
88
	return './data/datasheet';
}

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

89
90
91
92
93
94
95
96
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
97
// ---------------------------------------------------------------------
98

99
100
101
102
103
104
105
106
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'];
}

107
108
// ---------------------------------------------------------------------

Gabriel Moreau's avatar
Gabriel Moreau committed
109
110
function set_datasheet_new($pdo, $equipment_id, $datasheet_filename_upload, $tmp_file) {
	if (!preg_match('/\.pdf$/i', $datasheet_filename_upload))
111
112
113
114
115
116
		return false;

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

Gabriel Moreau's avatar
Gabriel Moreau committed
117
	$datasheet_filename_no_ext = pathinfo($datasheet_filename_upload, PATHINFO_FILENAME);
Gabriel Moreau's avatar
Gabriel Moreau committed
118
	$datasheet_filename_kebab = string_to_filename_kebab($datasheet_filename_no_ext).'.pdf';
119
120
121

	$sql1 = 'INSERT INTO datasheet (description, id_equipment) VALUES (?, ?);';
	$stmt1 = $pdo->prepare($sql1);
Gabriel Moreau's avatar
Gabriel Moreau committed
122
	$stmt1->execute(array($datasheet_filename_no_ext, $equipment_id));
123
124
125
126
127
	$id_datasheet = $pdo->lastInsertId();

	$sub_path = $id_datasheet.'-'.random_string(8);
	$sql2 = 'UPDATE datasheet SET pathname = ? WHERE id = ?;';
	$stmt2 = $pdo->prepare($sql2);
Gabriel Moreau's avatar
Gabriel Moreau committed
128
	$stmt2->execute(array($sub_path.'/'.$datasheet_filename_kebab, $id_datasheet));
129
130
131
132

	$new_dir = $new_datasheet_path.'/'.$sub_path;
	if (!is_dir($new_dir))
		mkdir($new_dir, 0755);
Gabriel Moreau's avatar
Gabriel Moreau committed
133
	move_uploaded_file($tmp_file, $new_dir.'/'.$datasheet_filename_kebab);
134
135
136
137

	return $id_datasheet;
}

138
139
// ---------------------------------------------------------------------
// Equipment
Gabriel Moreau's avatar
Gabriel Moreau committed
140
// ---------------------------------------------------------------------
141

142
function get_equipment_by_id($pdo, $id) {
143
144
145
	$sql = 'SELECT id, nom FROM Listing WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
146
147
148
149
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
150
151
}

Gabriel Moreau's avatar
Gabriel Moreau committed
152
// ---------------------------------------------------------------------
153

154
function get_equipment_all_by_id($pdo, $id) {
155
156
157
	$sql = 'SELECT * FROM Listing WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
158
159
160
161
162
163
	$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
164
// ---------------------------------------------------------------------
165

166
function get_equipment_listall($pdo) {
167
168
	// $sql = 'SELECT * FROM Listing ORDER BY categorie, nom;';
	$sql = 'SELECT DISTINCT e.*, c.nom AS category_name FROM Listing AS e INNER JOIN categorie AS c ON e.categorie = c.id ORDER BY c.nom, e.nom;';
169
170
171
172
173
174
175
176
177
	$stmt = $pdo->prepare($sql);
	$stmt->execute();
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

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

function get_equipment_listall_by_team($pdo, $id_team) {
178
179
	//$sql = 'SELECT * FROM Listing WHERE equipe = ? ORDER BY categorie, nom;';
	$sql = 'SELECT DISTINCT e.*, c.nom AS category_name FROM Listing AS e INNER JOIN categorie AS c ON e.categorie = c.id WHERE e.equipe = ? ORDER BY c.nom, e.nom;';
180
181
182
183
184
185
186
187
188
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id_team));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

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

function get_equipment_listall_by_category($pdo, $id_category) {
189
	$sql = 'SELECT * FROM Listing WHERE categorie = ? ORDER BY nom;';
190
191
192
193
194
195
196
197
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id_category));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

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

198
function get_equipment_listshort($pdo) {
199
	$sql = 'SELECT id, nom FROM Listing ORDER BY categorie, nom;';
200
201
	$stmt = $pdo->prepare($sql);
	$stmt->execute();
202
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
203
	return $result_fetch;
204
205
}

Gabriel Moreau's avatar
Gabriel Moreau committed
206
// ---------------------------------------------------------------------
207

Gabriel Moreau's avatar
Gabriel Moreau committed
208
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
209
	$sql = 'INSERT INTO Listing (categorie, nom, modele, gamme, equipe, fournisseur, achat, responsable, reparation, accessoires, inventaire, notice, barcode, loanable)';
210
	$sql .=            ' VALUES (?,         ?,   ?,      ?,     ?,      ?,           ?,     ?,           ?,          ?,           ?,          ?,      ?,       ?);';
211
	$stmt = $pdo->prepare($sql);
Gabriel Moreau's avatar
Gabriel Moreau committed
212
	$status = $stmt->execute(array($categorie, $nom, $modele, $feature, $equipe, $fourn, $achat, $tech, $reparation, $accessoires, $inventaire, $notice, $barcode, $loanable));
213
214
	$err_msg = '';
	if (!$status)
Gabriel Moreau's avatar
Gabriel Moreau committed
215
		$err_msg = $stmt->errorInfo()[2];
216
	return array($pdo->lastInsertId(), $err_msg);
217
218
219
220
}

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

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

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

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

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

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

Gabriel Moreau's avatar
Gabriel Moreau committed
258
259
260
261
262
263
264
265
266
267
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;
}

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

268
269
270
271
272
273
274
275
276
277
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;
}

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

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

function set_category_update($pdo, $id_category, $name) {
	$sql = 'UPDATE categorie SET nom = ? WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($name, $id_category));
}

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

Gabriel Moreau's avatar
Gabriel Moreau committed
295
296
297
298
299
300
301
302
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;
}

303
304
// ---------------------------------------------------------------------
// Loan
305
306
// ---------------------------------------------------------------------

307
308
309
310
311
312
313
314
315
316
317
318
319
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;
}

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

function get_loan_short_by_id_equipment($pdo, $id_equipment) {
Gabriel Moreau's avatar
Gabriel Moreau committed
320
	// recupere l'appareil via l'id qui est mis dans un champs texte (nom) !
321
	$sql = 'SELECT id FROM pret WHERE nom = ?;';
Gabriel Moreau's avatar
Gabriel Moreau committed
322
323
324
325
326
327
328
329
330
331
	$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;
}

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

332
333
334
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 = ?;';
Gabriel Moreau's avatar
Gabriel Moreau committed
335
	$stmt = $pdo->prepare($sql);
336
	$stmt->execute(array($id_equipment));
Gabriel Moreau's avatar
Gabriel Moreau committed
337
338
339
340
341
342
343
344
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
}

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

345
function get_loan_listall($pdo) {
346
347
	//$sql = 'SELECT * FROM pret;';
	$sql = 'SELECT DISTINCT l.*, e.nom AS equipment_name FROM pret AS l INNER JOIN Listing AS e ON l.nom = e.id ORDER BY e.nom;';
348
349
350
351
352
353
354
355
	$stmt = $pdo->prepare($sql);
	$stmt->execute();
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

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

356
357
358
359
360
361
362
363
364
365
function get_loan_find($pdo, $find) {
	$sql = 'SELECT * FROM pret WHERE commentaire RLIKE ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($find));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

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

Gabriel Moreau's avatar
Gabriel Moreau committed
366
367
368
369
370
371
372
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
373
374
375
376
377
378
379
380
// ---------------------------------------------------------------------

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

381
382
// ---------------------------------------------------------------------
// Supplier
Gabriel Moreau's avatar
Gabriel Moreau committed
383
384
// ---------------------------------------------------------------------

385
386
function get_supplier_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM fournisseurs WHERE id = ?;';
387
388
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
389
390
391
392
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
393
394
}

Gabriel Moreau's avatar
Gabriel Moreau committed
395
// ---------------------------------------------------------------------
396

397
398
399
400
401
402
403
404
405
406
407
408
function get_supplier_all_by_id($pdo, $id) {
	$sql = 'SELECT * FROM fournisseurs 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;
}

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

409
function get_supplier_listshort($pdo) {
410
	$sql = 'SELECT id, nom FROM fournisseurs ORDER BY nom;';
411
	$stmt = $pdo->prepare($sql);
412
	$stmt->execute();
413
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
414
	return $supplier_fetch;
415
416
}

Gabriel Moreau's avatar
Gabriel Moreau committed
417
// ---------------------------------------------------------------------
418

419
function get_supplier_listall($pdo) {
420
	$sql = 'SELECT * FROM fournisseurs ORDER BY nom;';
421
	$stmt = $pdo->prepare($sql);
422
	$stmt->execute();
423
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
424
	return $supplier_fetch;
425
426
}

Gabriel Moreau's avatar
Gabriel Moreau committed
427
// ---------------------------------------------------------------------
428

429
430
431
432
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 ?;';
433
	$stmt = $pdo->prepare($sql);
434
	$stmt->execute(array($find, $find));
435
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
436
	return $supplier_fetch;
437
438
}

439
440
// ---------------------------------------------------------------------

Gabriel Moreau's avatar
Gabriel Moreau committed
441
442
443
444
445
446
447
448
449
450
451
452
function set_supplier_new($pdo, $name, $address, $tel, $fax, $email, $www, $contact, $description) {
	$sql = 'INSERT INTO fournisseurs (nom, adresse, mail, www, tel, fax, contact, descr) VALUES (?, ?, ?, ?, ?, ?, ?, ?)';
	$stmt = $pdo->prepare($sql);
	$status = $stmt->execute(array($name, $address, $tel, $fax, $email, $www, $contact, $description));
	$err_msg = '';
	if (!$status)
		$err_msg = $stmt->errorInfo()[2];
	return array($pdo->lastInsertId(), $err_msg);
}

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

453
454
455
456
457
458
459
460
461
462
function set_supplier_update($pdo, $id_supplier, $name, $address, $tel, $fax, $email, $www, $contact, $description) {
	$sql = 'UPDATE LOW_PRIORITY fournisseurs  SET nom = ?, adresse = ?, tel = ?, fax = ?, mail = ?, www = ?, contact = ?, descr = ? WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$status = $stmt->execute(array($name, $address, $tel, $fax, $email, $www, $contact, $description, $id_supplier));
	$err_msg = '';
	if (!$status)
		$err_msg = $stmt->errorInfo()[2];
	return $err_msg;
}

463
464
// ---------------------------------------------------------------------
// Team
Gabriel Moreau's avatar
Gabriel Moreau committed
465
// ---------------------------------------------------------------------
466

467
468
function get_team_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM equipe WHERE id = ?;';
469
470
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
471
472
473
474
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
475
476
}

Gabriel Moreau's avatar
Gabriel Moreau committed
477
// ---------------------------------------------------------------------
478

479
480
481
482
483
484
485
486
487
488
489
490
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;
}

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

491
function get_team_listshort($pdo) {
492
	$sql = 'SELECT id, nom FROM equipe ORDER BY nom;';
493
	$stmt = $pdo->prepare($sql);
494
	$stmt->execute();
495
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
496
	return $team_fetch;
497
498
}

Gabriel Moreau's avatar
Gabriel Moreau committed
499
// ---------------------------------------------------------------------
500

501
function get_team_listall($pdo) {
502
	$sql = 'SELECT * FROM equipe ORDER BY nom;';
503
	$stmt = $pdo->prepare($sql);
504
	$stmt->execute();
505
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
506
	return $team_fetch;
507
508
}

Gabriel Moreau's avatar
Gabriel Moreau committed
509
// ---------------------------------------------------------------------
510

511
function get_team_with_appareil($pdo) {
512
	$sql = 'SELECT DISTINCT equipe.id, equipe.nom FROM equipe INNER JOIN Listing ON equipe.id = Listing.equipe ORDER BY equipe.nom;';
513
	$stmt = $pdo->prepare($sql);
514
	$stmt->execute();
515
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
516
	return $team_fetch;
517
518
}

Gabriel Moreau's avatar
Gabriel Moreau committed
519
520
// ---------------------------------------------------------------------

521
522
523
524
525
526
527
528
529
530
function get_team_count($pdo) {
	$sql = 'SELECT COUNT(*) as count FROM equipe;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute();
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch[0]['count'];
}

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

Gabriel Moreau's avatar
Gabriel Moreau committed
531
532
533
function set_team_new($pdo, $name, $description, $account, $manager) {
	$sql = 'INSERT INTO equipe (nom, descr, compte, chef) VALUES (?,  ?, ?, ?);';
	$stmt = $pdo->prepare($sql);
Gabriel Moreau's avatar
Gabriel Moreau committed
534
535
536
537
538
	$status = $stmt->execute(array($name, $description, $account, $manager));
	$err_msg = '';
	if (!$status)
		$err_msg = $stmt->errorInfo()[2];
	return array($pdo->lastInsertId(), $err_msg);
Gabriel Moreau's avatar
Gabriel Moreau committed
539
540
}

Gabriel Moreau's avatar
Gabriel Moreau committed
541
542
543
544
545
546
547
548
549
550
551
552
// ---------------------------------------------------------------------

function set_team_update($pdo, $id_team, $name, $description, $account, $manager) {
	$sql = 'UPDATE LOW_PRIORITY equipe SET nom = ?, descr = ?, compte = ?, chef = ? WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$status = $stmt->execute(array($name, $description, $account, $manager, $id_team));
	$err_msg = '';
	if (!$status)
		$err_msg = $stmt->errorInfo()[2];
	return $err_msg;
}

553
554
// ---------------------------------------------------------------------
// User
Gabriel Moreau's avatar
Gabriel Moreau committed
555
// ---------------------------------------------------------------------
556
557

function get_user_by_id($pdo, $id) {
Gabriel Moreau's avatar
Gabriel Moreau committed
558
	$sql = 'SELECT id, nom, prenom FROM users WHERE id = ?;';
559
560
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
561
562
563
564
565
566
	$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
567
// ---------------------------------------------------------------------
568
569
570
571
572
573
574
575
576

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;
577
578
}

Gabriel Moreau's avatar
Gabriel Moreau committed
579
// ---------------------------------------------------------------------
580

581
582
583
584
585
586
587
588
589
590
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
591
// ---------------------------------------------------------------------
592

593
594
function get_user_listall_by_logged_level($pdo, $logged_level) {
	if ($logged_level > 3)       // lorsqu'on est haut place, on voit tout le monde
595
		$sql = 'SELECT * FROM users ORDER BY nom, prenom;';
596
	else if ($logged_level == 3) // losrqu'on est de niveau 3, on voit tout le monde sauf les users de plus haut level
597
		$sql = 'SELECT * FROM users WHERE level < 4 ORDER BY nom, prenom;';
598
	else                         // lorsqu'on est < 3, on voit tout le monde sauf le suser de level > 3 et les users non valides
599
		$sql = 'SELECT * FROM users WHERE valid = 1 and level < 3 ORDER BY nom, prenom;';
600
601
602
603
604
605
	$stmt = $pdo->prepare($sql);
    $stmt->execute();
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

606
607
// ---------------------------------------------------------------------

608
609
function get_user_listshort_with_right($pdo, $level_min=1, $id_bonus=0) {
	$sql = 'SELECT id, nom, prenom FROM users WHERE (valid = 1 and level >= ?) or id = ? ORDER BY nom, prenom;';
610
	$stmt = $pdo->prepare($sql);
611
    $stmt->execute(array($level_min, $id_bonus));
612
613
614
615
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
// ---------------------------------------------------------------------

function get_user_count($pdo) {
	$sql = 'SELECT COUNT(*) as count FROM users;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute();
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch[0]['count'];
}

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

function set_user_new($pdo, $familyname, $firstname, $login, $password, $email, $level, $tel, $team_id, $theme) {
	$sql = 'INSERT INTO users (nom, prenom, loggin, password, email, level, tel, equipe, valid, theme) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 0, ?);';
	$stmt = $pdo->prepare($sql);
	$status = $stmt->execute(array($familyname, $firstname, $login, $password, $email, $level, $tel, $team_id, $theme));
	$err_msg = '';
	if (!$status)
		$err_msg = $stmt->errorInfo()[2];
	return array($pdo->lastInsertId(), $err_msg);
}

638
639
// ---------------------------------------------------------------------
// Version
640
641
// ---------------------------------------------------------------------

642
643
644
645
function get_version_by_name($pdo, $name) {
	$sql = 'SELECT version FROM version WHERE name = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($name));
646
	$version_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
647
648
649
650
	if (count($version_fetch) > 0)
		return $version_fetch[0];
	return false;
}
Gabriel Moreau's avatar
Gabriel Moreau committed
651
// ---------------------------------------------------------------------
652
653
654
655
656
657
658
659
660

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

661
?>