db-functions.php 38.9 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
require_once('base-functions.php');
5
6
require_once('connect.php');

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

9
10
11
12
define('GESTEX_DB_VERSION', 4);

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

Gabriel Moreau's avatar
Gabriel Moreau committed
13
// connexion au serveur mySQL
14

15
function connect_db_minimal() {
16
17
18
19
20
21
22
23
	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;
	}
24

25
26
27
28
29
30
31
32
33
34
35
36
	return $pdo;
}

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

function connect_db() {
	if ($pdo = connect_db_minimal()) {
		try{
			$datasheet_version = get_version_by_name($pdo, 'datasheet');
			if ($datasheet_version < GESTEX_DB_VERSION) {
				error_log('Database version error: update the database schema');
				echo "Mettre à jour le schéma de la base de données";
37
				return false;
38
39
40
41
42
43
			}
		}
		catch(PDOException $exception){
			error_log('Database version error: '.$exception->getMessage());
			echo $exception->getMessage();
			return false;
44
45
46
		}
	}

47
48
49
	return $pdo;
}

Gabriel Moreau's avatar
Gabriel Moreau committed
50
// ---------------------------------------------------------------------
51

52
53
54
55
56
57
58
59
60
61
function connect_db_or_alert() {
	if ($pdo = connect_db())
		return $pdo;

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

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

62
63
64
65
66
67
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
68
// ---------------------------------------------------------------------
69
70
71
72
73

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

Gabriel Moreau's avatar
Gabriel Moreau committed
74
// ---------------------------------------------------------------------
75
76
77
78
79
80
81
82

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
83
// ---------------------------------------------------------------------
84
85
86
87
88

function last_id_db() {
	return mysql_insert_id();
}

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

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 />";
94
95
96
	//$sql = 'SELECT * FROM ? WHERE ? = ?;';
	$stmt = $pdo->prepare("SELECT * FROM $table WHERE $col = '$value'");
	$stmt->execute();
97
98
99
100
101
102
103
104
105
106
	$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;
}

107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
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
153
154
155
156
157
158
159
160
// ---------------------------------------------------------------------
// Category
// ---------------------------------------------------------------------

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

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

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

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

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

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

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

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

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

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

function del_category_by_id($pdo, $id) {
	$sql = 'DELETE LOW_PRIORITY FROM categorie WHERE id = ? LIMIT 1';
	$stmt = $pdo->prepare($sql);
Gabriel Moreau's avatar
Gabriel Moreau committed
161
162
	$iostat = $stmt->execute(array($id));
	return $iostat;
163
164
}

165
166
// ---------------------------------------------------------------------
// Datasheet
Gabriel Moreau's avatar
Gabriel Moreau committed
167
// ---------------------------------------------------------------------
168

Gabriel Moreau's avatar
Gabriel Moreau committed
169
function get_datasheet_basepath() {
170
171
172
173
174
	return './data/datasheet';
}

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

175
176
177
178
179
180
181
182
183
184
185
186
function get_datasheet_all_by_id($pdo, $id) {
	$sql = 'SELECT * FROM datasheet 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;
}

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

187
188
189
190
191
192
193
194
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
195
// ---------------------------------------------------------------------
196

197
198
199
200
201
202
203
204
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'];
}

205
206
// ---------------------------------------------------------------------

207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
function set_datasheet_new($pdo, $equipment_id, $file_field_name) {
	$datasheet_filename_upload = $_FILES[$file_field_name]['name'];
	$datasheet_tmp_file        = $_FILES[$file_field_name]['tmp_name'];
	$datasheet_io_error        = $_FILES[$file_field_name]['error'];

	$file_upload_errors = array(
		0 => 'There is no error, the file uploaded with success',
		1 => 'The uploaded file exceeds the upload_max_filesize directive in php.ini',
		2 => 'The uploaded file exceeds the MAX_FILE_SIZE directive that was specified in the HTML form',
		3 => 'The uploaded file was only partially uploaded',
		4 => 'No file was uploaded',
		6 => 'Missing a temporary folder',
		7 => 'Failed to write file to disk.',
		8 => 'A PHP extension stopped the file upload.',
	);
	if (!is_uploaded_file($datasheet_tmp_file) or $datasheet_io_error > 0) {
		error_log('Error: not uploaded datasheet file - '.$datasheet_filename_upload.' - '.$file_upload_errors[$datasheet_io_error]);
224
		return false;
225
226
227
228
229
230
	}

	if (!preg_match('/\.pdf$/i', $datasheet_filename_upload)) {
		error_log('Error: datasheet file not a pdf - '.$datasheet_filename_upload);
		return false;
	}
231
232
233
234
235

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

Gabriel Moreau's avatar
Gabriel Moreau committed
236
	$datasheet_filename_no_ext = pathinfo($datasheet_filename_upload, PATHINFO_FILENAME);
Gabriel Moreau's avatar
Gabriel Moreau committed
237
	$datasheet_filename_kebab = string_to_filename_kebab($datasheet_filename_no_ext).'.pdf';
238
239
240

	$sql1 = 'INSERT INTO datasheet (description, id_equipment) VALUES (?, ?);';
	$stmt1 = $pdo->prepare($sql1);
Gabriel Moreau's avatar
Gabriel Moreau committed
241
	$stmt1->execute(array($datasheet_filename_no_ext, $equipment_id));
242
243
244
245
246
	$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
247
	$stmt2->execute(array($sub_path.'/'.$datasheet_filename_kebab, $id_datasheet));
248
249
250
251

	$new_dir = $new_datasheet_path.'/'.$sub_path;
	if (!is_dir($new_dir))
		mkdir($new_dir, 0755);
252

Gabriel Moreau's avatar
Gabriel Moreau committed
253
254
	$iostat = move_uploaded_file($datasheet_tmp_file, $new_dir.'/'.$datasheet_filename_kebab);
	if (!$iostat) {
255
		error_log('Error: not move datasheet file '.$datasheet_filename_upload.' to '.$datasheet_filename_kebab);
256
		del_datasheet_by_id($pdo, $id_datasheet);
257
258
		return false;
	}
259
260
261
262

	return $id_datasheet;
}

263
264
// ---------------------------------------------------------------------

265
function del_datasheet_by_id($pdo, $id) {
266
267
268
269
270
271
272
	$datasheet_selected = get_datasheet_all_by_id($pdo, $id);

	$datasheet_basepath = get_datasheet_basepath();
	$datasheet_pathname = $datasheet_selected['pathname'];
	$datasheet_dirname  = pathinfo($datasheet_pathname, PATHINFO_DIRNAME);

	if (is_file($datasheet_basepath.'/'.$datasheet_pathname))
Gabriel Moreau's avatar
Gabriel Moreau committed
273
		$iostat = unlink($datasheet_basepath.'/'.$datasheet_pathname);
274
275

	if (!empty($datasheet_dirname) and is_dir($datasheet_basepath.'/'.$datasheet_dirname))
Gabriel Moreau's avatar
Gabriel Moreau committed
276
		$iostat = rmdir($datasheet_basepath.'/'.$datasheet_dirname);
277
278
279

	$sql = 'DELETE LOW_PRIORITY FROM datasheet WHERE id = ? LIMIT 1;';
	$stmt = $pdo->prepare($sql);
Gabriel Moreau's avatar
Gabriel Moreau committed
280
281
	$iostat = $stmt->execute(array($id));
	return $iostat;
282
283
}

284
285
// ---------------------------------------------------------------------
// Equipment
Gabriel Moreau's avatar
Gabriel Moreau committed
286
// ---------------------------------------------------------------------
287

Estéban Ristich's avatar
Estéban Ristich committed
288
289
290
291
292
/**
 * Recupère l'ID et le nom d'un équipement par son ID
 * 
 * @return false|array Le contenu d'un équipement directement
 */
293
function get_equipment_by_id($pdo, $id) {
294
295
296
	$sql = 'SELECT id, nom FROM Listing WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
297
298
299
300
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
301
302
}

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

Estéban Ristich's avatar
Estéban Ristich committed
305
306
307
308
309
/**
 * Récupère tout le contenu d'un équipement par son ID
 * 
 * @return false|array Le contenu d'un équipement directement
 */
310
function get_equipment_all_by_id($pdo, $id) {
311
312
313
	$sql = 'SELECT * FROM Listing WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
314
315
316
317
318
319
	$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
320
// ---------------------------------------------------------------------
321

Estéban Ristich's avatar
Estéban Ristich committed
322
323
324
325
326
327
/**
 * Recupere tout le contenu d'un équipement ainsi que son nom de categorie
 * rangé par nom d'équipement et nom d'équipe
 * 
 * @return array
 */
328
function get_equipment_listall($pdo) {
329
330
	// $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;';
331
332
333
334
335
336
337
338
	$stmt = $pdo->prepare($sql);
	$stmt->execute();
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

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

Estéban Ristich's avatar
Estéban Ristich committed
339
/**
Estéban Ristich's avatar
Estéban Ristich committed
340
341
 * Recupere tout le contenu des équipements ainsi que leurs nom d'équipe via 
 * l'ID d'équipe, rangé par nom d'équipement et nom d'équipe
Estéban Ristich's avatar
Estéban Ristich committed
342
343
344
 * 
 * @return array
 */
345
function get_equipment_listall_by_team($pdo, $id_team) {
346
347
	//$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;';
348
349
350
351
352
353
354
355
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id_team));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

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

Estéban Ristich's avatar
Estéban Ristich committed
356
357
358
359
360
361
/**
 * Recupere le nombre d'équipement empruntable avec la même équipe via 
 * l'ID de l'équipe
 * 
 * @return int
 */
362
363
364
365
366
367
368
369
370
371
function get_equipment_count_loanable_by_team($pdo, $id_team) {
	$sql = 'SELECT COUNT(*) as count FROM Listing AS e WHERE e.loanable = 1 and e.equipe = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id_team));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch[0]['count'];
}

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

Estéban Ristich's avatar
Estéban Ristich committed
372
373
374
375
376
377
/**
 * Recupere tout le contenu des equipements ayant la même 
 * categorie, rangé par nom
 * 
 * @return array
 */
378
function get_equipment_listall_by_category($pdo, $id_category) {
379
	$sql = 'SELECT * FROM Listing WHERE categorie = ? ORDER BY nom;';
380
381
382
383
384
385
386
387
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id_category));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

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

Estéban Ristich's avatar
Estéban Ristich committed
388
389
390
391
392
393
/**
 * Recupere l'ID et le nom de tout les equipements rangé par
 * catégorie et nom
 * 
 * @return array
 */
394
function get_equipment_listshort($pdo) {
395
	$sql = 'SELECT id, nom FROM Listing ORDER BY categorie, nom;';
396
397
	$stmt = $pdo->prepare($sql);
	$stmt->execute();
398
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
399
	return $result_fetch;
400
401
}

Gabriel Moreau's avatar
Gabriel Moreau committed
402
// ---------------------------------------------------------------------
403

Estéban Ristich's avatar
Estéban Ristich committed
404
405
406
407
408
409
/**
 * Récupere l'ID d'un équipement depuis la table pret, 
 * de par l'ID du pret
 * 
 * @return ID de l'equipement
 */
410
411
412
413
414
415
416
417
418
419
function get_equipment_by_loan_id($pdo, $id_loan) {
	$sql = 'SELECT nom FROM pret WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id_loan));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch[0]["nom"];
}

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

Estéban Ristich's avatar
Estéban Ristich committed
420
421
422
423
424
425
/**
 * Créer un équipement
 * 
 * @return array|string Renvoie une chaine si echec sinon 
 * un tableau avec l'ID et un msg d'erreur eventuellement
 */
Gabriel Moreau's avatar
Gabriel Moreau committed
426
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
427
	$sql = 'INSERT INTO Listing (categorie, nom, modele, gamme, equipe, fournisseur, achat, responsable, reparation, accessoires, inventaire, notice, barcode, loanable)';
428
	$sql .=            ' VALUES (?,         ?,   ?,      ?,     ?,      ?,           ?,     ?,           ?,          ?,           ?,          ?,      ?,       ?);';
429
	$stmt = $pdo->prepare($sql);
Gabriel Moreau's avatar
Gabriel Moreau committed
430
	$iostat = $stmt->execute(array($categorie, $nom, $modele, $feature, $equipe, $fourn, $achat, $tech, $reparation, $accessoires, $inventaire, $notice, $barcode, $loanable));
431
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
432
	if (!$iostat)
Gabriel Moreau's avatar
Gabriel Moreau committed
433
		$err_msg = $stmt->errorInfo()[2];
434
	return array($pdo->lastInsertId(), $err_msg);
435
436
437
438
}

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

Estéban Ristich's avatar
Estéban Ristich committed
439
440
441
442
443
444
/**
 * Met à jour un équipement par son ID
 *
 * @return string Renvoie une chaine vide
 * si réussite sinon une chaine d'erreurs
 */
445
446
447
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);
Gabriel Moreau's avatar
Gabriel Moreau committed
448
	$iostat = $stmt->execute(array($categorie, $nom, $modele, $feature, $equipe, $fourn, $achat, $tech, $reparation, $accessoires, $inventaire, $notice, $barcode, $loanable, $id_equipment));
449
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
450
	if (!$iostat)
451
452
453
454
455
456
		$err_msg = $stmt->errorInfo()[2];
	return $err_msg;
}

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

Estéban Ristich's avatar
Estéban Ristich committed
457
/**
Estéban Ristich's avatar
Estéban Ristich committed
458
 * Supprime un seul équipement par son ID
Estéban Ristich's avatar
Estéban Ristich committed
459
 */
460
function del_equipment_by_id($pdo, $id) {
461
462
	$sql = 'DELETE LOW_PRIORITY FROM Listing WHERE id = ? LIMIT 1;';
	$stmt = $pdo->prepare($sql);
Gabriel Moreau's avatar
Gabriel Moreau committed
463
464
	$iostat = $stmt->execute(array($id));
	return $iostat;
465
466
467
468
}

// ---------------------------------------------------------------------
// Loan
469
470
// ---------------------------------------------------------------------

Estéban Ristich's avatar
Estéban Ristich committed
471
472
473
474
475
476
/**
 * Récupère tout le contenu d'un pret par son ID
 * 
 * @return array|false S'il n'est pas "false", le 
 * retour ne sera qu'un seul objet
 */
477
478
479
480
481
482
483
484
485
486
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;
}

487
488
// ---------------------------------------------------------------------

Estéban Ristich's avatar
Estéban Ristich committed
489
490
491
492
493
/**
 * Récupere tout les prets liés à un équipement qui ne sont pas retourné
 * 
 * @deprecated Remplacé par la fonction get_loan_active_listall_by_equipment()
 */
494
function get_loans_by_equipment($pdo, $equipment_id) {
495
	$sql = 'SELECT * FROM pret WHERE nom = ? AND NOT status = ?;';
496
	$stmt = $pdo->prepare($sql);
497
	$stmt->execute(array($equipment_id, STATUS_LOAN_RETURNED));
498
499
500
501
502
503
504
505
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch;
	return false;
}

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

506
507
508
509
510
/**
* Récupere tout les prets liés à un équipement qui ne sont pas retourné,
* rangé dans un certaine ordre de priorité.
*/
function get_loan_active_listall_by_equipment($pdo, $id_equipment) {
511
	$sql = 'SELECT * FROM pret WHERE nom = ? AND status != ? ORDER BY status DESC, emprunt ASC, retour ASC;';
512
	$stmt = $pdo->prepare($sql);
513
	$stmt->execute(array($id_equipment, STATUS_LOAN_RETURNED));
514
515
516
517
518
519
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch;
	return false;
}

520
521
// ---------------------------------------------------------------------

Estéban Ristich's avatar
Estéban Ristich committed
522
523
524
525
526
527
/**
 * Recupère l'ID et le status d'un pret via l'ID d'équipement
 * 
 * @return false|array Le contenu du pret directement
 * @deprecated
 */
528
function get_loan_short_by_id_equipment($pdo, $id_equipment) {
Gabriel Moreau's avatar
Gabriel Moreau committed
529
	// recupere l'appareil via l'id qui est mis dans un champs texte (nom) !
Estéban Ristich's avatar
Estéban Ristich committed
530
	$sql = 'SELECT id, status FROM pret WHERE nom = ?;';
Gabriel Moreau's avatar
Gabriel Moreau committed
531
532
533
534
535
536
537
538
539
540
	$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;
}

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

Estéban Ristich's avatar
Estéban Ristich committed
541
542
543
544
545
/**
 * Recupère tout le contenu d'un pret via l'ID d'équipement
 * 
 * @return false|array Le contenu du pret directement
 */
546
547
548
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
549
	$stmt = $pdo->prepare($sql);
550
	$stmt->execute(array($id_equipment));
Gabriel Moreau's avatar
Gabriel Moreau committed
551
552
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
553
		return $result_fetch;
Gabriel Moreau's avatar
Gabriel Moreau committed
554
555
556
557
558
	return false;
}

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

Estéban Ristich's avatar
Estéban Ristich committed
559
560
561
/**
 * @todo effectuer la documentation de la jointure
 */
562
function get_loan_listall($pdo) {
563
	//$sql = 'SELECT * FROM pret;';
564
	$sql = 'SELECT DISTINCT l.*, e.nom AS equipment_name FROM pret AS l INNER JOIN Listing AS e ON l.nom = e.id WHERE status = ? ORDER BY l.retour DESC, l.emprunt DESC, e.nom;';
565
	$stmt = $pdo->prepare($sql);
566
	$stmt->execute(array(STATUS_LOAN_BORROWED));
567
568
569
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}
Gabriel Moreau's avatar
Gabriel Moreau committed
570
571
// ---------------------------------------------------------------------

Estéban Ristich's avatar
Estéban Ristich committed
572
573
574
/**
 * @todo effectuer la documentation de la jointure
 */
Gabriel Moreau's avatar
Gabriel Moreau committed
575
function get_loan_listall_by_team($pdo, $id_team) {
576
	$sql = 'SELECT DISTINCT l.*, e.nom AS equipment_name FROM pret AS l INNER JOIN Listing AS e ON l.nom = e.id WHERE l.equipe = ? AND status = ? ORDER BY l.retour DESC, l.emprunt DESC, e.nom;';
Gabriel Moreau's avatar
Gabriel Moreau committed
577
	$stmt = $pdo->prepare($sql);
578
	$stmt->execute(array($id_team, STATUS_LOAN_BORROWED));
Gabriel Moreau's avatar
Gabriel Moreau committed
579
580
581
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}
582
583
584

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

Estéban Ristich's avatar
Estéban Ristich committed
585
586
587
588
589
/**
 * Récupère le nombre de pret d'une équipe correspondante
 * 
 * @return int
 */
590
591
592
593
594
595
596
597
598
599
function get_loan_count_by_team($pdo, $id_team) {
	$sql = 'SELECT COUNT(*) as count FROM pret AS l INNER JOIN Listing AS e ON l.nom = e.id WHERE e.equipe = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id_team));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch[0]['count'];
}

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

Estéban Ristich's avatar
Estéban Ristich committed
600
601
602
603
604
605
606
/**
 * Récupère tout le contenu des prets d'un emprunteur en utilisant
 * la directive "RLIKE" pour détécter l'utilisateur dans le champ
 * commentaire des prets
 * 
 * @return array 
 */
607
608
609
610
611
612
613
614
615
616
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;
}

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

Estéban Ristich's avatar
Estéban Ristich committed
617
618
619
620
621
622
623
/**
 * Récupère tout le contenu des prets qui possède le même ID d'équipement
 * et étant actuellement en emprunt
 * 
 * @return false|array
 */
function get_loans_by_equipment_and_borrowed($pdo, $id_equipment) {
624
	$sql = 'SELECT * FROM pret WHERE nom = ? AND status = ?;';
625
	$stmt = $pdo->prepare($sql);
626
	$stmt->execute(array($id_equipment, STATUS_LOAN_BORROWED));
627
628
629
630
631
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch;
	return false;
}
632

633
634
// ---------------------------------------------------------------------

Estéban Ristich's avatar
Estéban Ristich committed
635
636
/**
 * Récupère tout le contenu des prets étant dans l'intervalle d'emprunt
Estéban Ristich's avatar
Estéban Ristich committed
637
 * des dates d'un équipement spécifier. Renverra "false" s'il n'y en a pas
Estéban Ristich's avatar
Estéban Ristich committed
638
639
640
 * 
 * @return false|array
 */
641
function get_loans_interval_by_id($pdo, $id_equipment, $from, $to) {
642
	$sql = 'SELECT * FROM pret WHERE ((`emprunt` <= ? AND `retour` >= ?) AND `nom` = ?) OR ((`emprunt` <= ? AND `retour` >= ?) AND `nom` = ?) OR ((`emprunt` >= ? AND `retour` <= ?) AND `nom` = ?);';
643
	$stmt = $pdo->prepare($sql);
644
	$stmt->execute(array($from, $from, $id_equipment, $to, $to, $id_equipment, $from, $to, $id_equipment));
Estéban Ristich's avatar
Fix bug    
Estéban Ristich committed
645
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
646
647
648
	if (count($result_fetch) > 0)
		return $result_fetch;
	return false;
649
650
651
652
}

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

Estéban Ristich's avatar
Estéban Ristich committed
653
654
655
656
657
658
659
/**
 * Récupère tout le contenu des prets étant dans l'intervalle d'emprunt
 * des dates d'un équipement spécifier excepter un emprunt en particulier.
 * Renverra "false" s'il n'y en a pas
 * 
 * @return false|array
 */
Estéban Ristich's avatar
Estéban Ristich committed
660
function get_loans_interval_by_id_except_loan($pdo, $id_equipment, $from, $to, $except_id) {
661
662
663
664
665
666
667
668
669
670
671
	$sql = 'SELECT * FROM pret WHERE (((`emprunt` <= ? AND `retour` >= ?) AND `nom` = ?) OR ((`emprunt` <= ? AND `retour` >= ?) AND `nom` = ?) OR ((`emprunt` >= ? AND `retour` <= ?) AND `nom` = ?)) AND NOT id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($from, $from, $id_equipment, $to, $to, $id_equipment, $from, $to, $id_equipment, $except_id));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch;
	return false;
}

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

Estéban Ristich's avatar
Estéban Ristich committed
672
673
674
675
676
/**
 * Récupère le status du pret via son ID
 * 
 * @return string 
 */
677
678
679
680
681
682
683
684
685
686
function get_loan_status_by_id($pdo, $id_loan) {
	$sql = 'SELECT status FROM pret WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id_loan));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch[0]["status"];
}

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

Estéban Ristich's avatar
Estéban Ristich committed
687
688
689
690
691
/**
 * Récupère tout le contenu du dernier prêt emprunter
 * 
 * @return false|array
 */
Estéban Ristich's avatar
Estéban Ristich committed
692
function get_reserved_listall_last_loan($pdo, $id_equipment) {
693
	$sql = 'SELECT * FROM pret WHERE nom = ? AND status = ? ORDER BY retour DESC LIMIT 1;';
694
	$stmt = $pdo->prepare($sql);
695
	$stmt->execute(array($id_equipment, STATUS_LOAN_RETURNED));
696
697
698
699
700
701
702
703
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch;
	return false;
}

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

Estéban Ristich's avatar
Estéban Ristich committed
704
705
706
707
708
709
/**
 * Ajoute un nouveau pret défini comme étant actuellement en emprunt
 * 
 * @deprecated
 * @return int
 */
710
function set_loan_borrowed_new($pdo, $id_equipment, $id_team, $date_begin, $date_end, $comment) {
711
	$sql = 'INSERT INTO pret (nom, equipe, emprunt, retour, commentaire, status) VALUES (?, ?, ?, ?, ?, ?);';
712
	$stmt = $pdo->prepare($sql);
713
	$stmt->execute(array($id_equipment, $id_team, $date_begin, $date_end, $comment, STATUS_LOAN_BORROWED));
714
715
716
717
718
	return $pdo->lastInsertId();
}

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

Estéban Ristich's avatar
Estéban Ristich committed
719
720
721
722
723
724
/**
 * Ajoute un nouveau pret défini comme étant en réservation
 * 
 * @deprecated
 * @return int
 */
725
726
function set_loan_reserved_new($pdo, $id_equipment, $id_team, $date_begin, $date_end, $comment) {
	$sql = 'INSERT INTO pret (nom, equipe, emprunt, retour, commentaire, status) VALUES (?, ?, ?, ?, ?, ?);';
727
	$stmt = $pdo->prepare($sql);
728
729
	$stmt->execute(array($id_equipment, $id_team, $date_begin, $date_end, $comment, STATUS_LOAN_RESERVED));
	return $pdo->lastInsertId();
730
731
732
733
}

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

Estéban Ristich's avatar
Estéban Ristich committed
734
735
736
737
738
739
/**
 * Met à jour un emprunt (en spécifiant son ID) comme étant emprunter et bloque également
 * sa date d'emprunt au jour même de son appel
 * 
 * @deprecated
 */
740
741
function set_booking_update_to_loan($pdo, $id_loan) {
	$sql = 'UPDATE pret SET status = ?, emprunt = CURRENT_DATE WHERE id = ?;';
Gabriel Moreau's avatar
Gabriel Moreau committed
742
	$stmt = $pdo->prepare($sql);
743
	$stmt->execute(array(STATUS_LOAN_BORROWED, $id_loan));
Gabriel Moreau's avatar
Gabriel Moreau committed
744
745
}

Gabriel Moreau's avatar
Gabriel Moreau committed
746
747
// ---------------------------------------------------------------------

Estéban Ristich's avatar
Estéban Ristich committed
748
749
750
/**
 * Met à jour le pret en spécifiant son ID sans modifier par défaut le status
 */
Gabriel Moreau's avatar
Gabriel Moreau committed
751
752
753
754
755
756
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));
}

Gabriel Moreau's avatar
Gabriel Moreau committed
757
758
// ---------------------------------------------------------------------

Estéban Ristich's avatar
Estéban Ristich committed
759
760
761
/**
 * Supprime un seul pret via son ID
 */
762
function del_loan_by_id($pdo, $id_loan) {
Gabriel Moreau's avatar
Gabriel Moreau committed
763
764
	$sql = 'DELETE LOW_PRIORITY FROM pret WHERE id = ? LIMIT 1;';
	$stmt = $pdo->prepare($sql);
765
	$iostat = $stmt->execute(array($id_loan));
Gabriel Moreau's avatar
Gabriel Moreau committed
766
	return $iostat;
Gabriel Moreau's avatar
Gabriel Moreau committed
767
768
}

769
770
// ---------------------------------------------------------------------

Estéban Ristich's avatar
Estéban Ristich committed
771
772
773
774
/**
 * Met à jour un pret (en spécifiant son ID) comme étant retourné et
 * bloque sa date de retour au jour de son appel
 */
775
776
function set_loan_to_returned($pdo, $id_loan) {
	$sql = 'UPDATE LOW_PRIORITY pret SET status = ?, retour = CURRENT_DATE WHERE id = ?;';
777
	$stmt = $pdo->prepare($sql);
778
	$iostat = $stmt->execute(array(STATUS_LOAN_RETURNED, $id_loan));
779
780
781
	return $iostat;
}

782

783
784
// ---------------------------------------------------------------------
// Supplier
Gabriel Moreau's avatar
Gabriel Moreau committed
785
786
// ---------------------------------------------------------------------

Estéban Ristich's avatar
Estéban Ristich committed
787
788
789
790
791
/**
 * Récupère l'ID et le nom d'un fournisseur via son ID
 * 
 * @return false|array Retourne directement le fournisseur
 */
Estéban Ristich's avatar
Estéban Ristich committed
792
function get_supplier_listshort_by_id($pdo, $id) {
793
	$sql = 'SELECT id, nom FROM fournisseurs WHERE id = ?;';
794
795
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
796
797
798
799
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
800
801
}

Gabriel Moreau's avatar
Gabriel Moreau committed
802
// ---------------------------------------------------------------------
803

Estéban Ristich's avatar
Estéban Ristich committed
804
805
806
807
808
/**
 * Récupère tout le contenu d'un seul fournisseur via son ID
 * 
 * @return false|array Retourne directement le fournisseur
 */
809
810
811
812
813
814
815
816
817
818
819
820
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;
}

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

Estéban Ristich's avatar
Estéban Ristich committed
821
822
823
824
825
/**
 * Récupère l'ID et le nom de tout les fournisseurs trier par nom croissant
 * 
 * @return array
 */
826
function get_supplier_listshort($pdo) {
827
	$sql = 'SELECT id, nom FROM fournisseurs ORDER BY nom;';
828
	$stmt = $pdo->prepare($sql);
829
	$stmt->execute();
830
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
831
	return $supplier_fetch;
832
833
}

Gabriel Moreau's avatar
Gabriel Moreau committed
834
// ---------------------------------------------------------------------
835

Estéban Ristich's avatar
Estéban Ristich committed
836
837
838
839
840
/**
 * Récupère tout le contenu de tout les fournisseurs trier par nom croissant
 * 
 * @return array
 */
841
function get_supplier_listall($pdo) {
842
	$sql = 'SELECT * FROM fournisseurs ORDER BY nom;';
843
	$stmt = $pdo->prepare($sql);
844
	$stmt->execute();
845
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
846
	return $supplier_fetch;
847
848
}

Gabriel Moreau's avatar
Gabriel Moreau committed
849
// ---------------------------------------------------------------------
850

Estéban Ristich's avatar
Estéban Ristich committed
851
852
853
854
855
856
857
858
/**
 * Retourne la liste de tout les fournisseurs si la valeurs $find est "true"
 * ou une chaîne vide. Sinon elle récuperera tout le contenu des fournisseurs
 * où le chaine demandé a le plus de similitude avec le nom ou la description
 * du fournisseur
 * 
 * @return array
 */
859
860
861
862
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 ?;';
863
	$stmt = $pdo->prepare($sql);
864
	$stmt->execute(array($find, $find));
865
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
866
	return $supplier_fetch;
867
868
}

869
870
// ---------------------------------------------------------------------

Estéban Ristich's avatar
Estéban Ristich committed
871
872
873
874
875
/**
 * Ajoute un nouveau fournisseur
 * 
 * @return array Avec chaine d'erreur au deuxième index
 */
Gabriel Moreau's avatar
Gabriel Moreau committed
876
877
878
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);
Gabriel Moreau's avatar
Gabriel Moreau committed
879
	$iostat = $stmt->execute(array($name, $address, $tel, $fax, $email, $www, $contact, $description));
Gabriel Moreau's avatar
Gabriel Moreau committed
880
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
881
	if (!$iostat)
Gabriel Moreau's avatar
Gabriel Moreau committed
882
883
884
885
886
887
		$err_msg = $stmt->errorInfo()[2];
	return array($pdo->lastInsertId(), $err_msg);
}

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

Estéban Ristich's avatar
Estéban Ristich committed
888
889
890
891
892
/**
 * Met à jour un fournisseur via son ID
 * 
 * @return array Avec chaine d'erreur au deuxième index
 */
893
894
895
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);
Gabriel Moreau's avatar
Gabriel Moreau committed
896
	$iostat = $stmt->execute(array($name, $address, $tel, $fax, $email, $www, $contact, $description, $id_supplier));
897
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
898
	if (!$iostat)
899
900
901
902
		$err_msg = $stmt->errorInfo()[2];
	return $err_msg;
}

903
// ---------------------------------------------------------------------
Gabriel Moreau's avatar
Gabriel Moreau committed
904

Estéban Ristich's avatar
Estéban Ristich committed
905
906
907
/**
 * Supprime un fournisseur via son ID
 */
Gabriel Moreau's avatar
Gabriel Moreau committed
908
909
910
function del_supplier_by_id($pdo, $id) {
	$sql = 'DELETE LOW_PRIORITY FROM fournisseurs WHERE id = ? LIMIT 1;';
	$stmt = $pdo->prepare($sql);
Gabriel Moreau's avatar
Gabriel Moreau committed
911
912
	$iostat = $stmt->execute(array($id));
	return $iostat;
Gabriel Moreau's avatar
Gabriel Moreau committed
913
914
915
}

// ---------------------------------------------------------------------
916
// Team
Gabriel Moreau's avatar
Gabriel Moreau committed
917
// ---------------------------------------------------------------------
918

Estéban Ristich's avatar
Estéban Ristich committed
919
920
921
922
923
/**
 * Récupère l'ID et le nom d'une seul équipe via son ID
 * 
 * @return array Retourne directement l'équipe
 */
924
925
function get_team_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM equipe WHERE id = ?;';
926
927
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
928
929
930
931
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
932
933
}

Gabriel Moreau's avatar
Gabriel Moreau committed
934
// ---------------------------------------------------------------------
935

Estéban Ristich's avatar
Estéban Ristich committed
936
937
938
939
940
/**
 * Récupère tout le contenu d'une seul équipe via son ID
 * 
 * @return array Retourne directement l'équipe
 */
941
942
943
944
945
946
947
948
949
950
951
952
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;
}

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

Estéban Ristich's avatar
Estéban Ristich committed
953
954
955
956
957
/**
 * Récupère l'ID et le nom de toutes les équipes par ordre de nom croissant
 * 
 * @return array
 */
958
function get_team_listshort($pdo) {
959
	$sql = 'SELECT id, nom FROM equipe ORDER BY nom;';
960
	$stmt = $pdo->prepare($sql);
961
	$stmt->execute();
962
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
963
	return $team_fetch;
964
965
}

Gabriel Moreau's avatar
Gabriel Moreau committed
966
// ---------------------------------------------------------------------
967

Estéban Ristich's avatar
Estéban Ristich committed
968
969
970
971
972
/**
 * Récupère tout le contenu de toutes les équipe par ordre de nom croissant
 * 
 * @return array
 */
973
function get_team_listall($pdo) {
974
	$sql = 'SELECT * FROM equipe ORDER BY nom;';
975
	$stmt = $pdo->prepare($sql);
976
	$stmt->execute();
977
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
978
	return $team_fetch;
979
980
}

Gabriel Moreau's avatar
Gabriel Moreau committed
981
// ---------------------------------------------------------------------
982

Estéban Ristich's avatar
Estéban Ristich committed
983
984
985
986
987
988
/**
 * Récupère l'ID et le nom des équipe par ordre croissant de nom,
 * possèdant au moins un équipement
 * 
 * @return array
 */
989
function get_team_with_appareil($pdo) {
990
	$sql = 'SELECT DISTINCT equipe.id, equipe.nom FROM equipe INNER JOIN Listing ON equipe.id = Listing.equipe ORDER BY equipe.nom;';
991
	$stmt = $pdo->prepare($sql);
992
	$stmt->execute();
993
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
994
	return $team_fetch;
995
996
}

Gabriel Moreau's avatar
Gabriel Moreau committed
997
998
// ---------------------------------------------------------------------

999
1000
1001
1002
1003
1004
1005
1006
1007
1008
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
1009
1010
1011
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
1012
	$iostat = $stmt->execute(array($name, $description, $account, $manager));
Gabriel Moreau's avatar
Gabriel Moreau committed
1013
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
1014
	if (!$iostat)
Gabriel Moreau's avatar
Gabriel Moreau committed
1015
1016
		$err_msg = $stmt->errorInfo()[2];
	return array($pdo->lastInsertId(), $err_msg);
Gabriel Moreau's avatar
Gabriel Moreau committed
1017
1018
}

Gabriel Moreau's avatar
Gabriel Moreau committed
1019
1020
1021
1022
1023
// ---------------------------------------------------------------------

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);
Gabriel Moreau's avatar
Gabriel Moreau committed
1024
	$iostat = $stmt->execute(array($name, $description, $account, $manager, $id_team));
Gabriel Moreau's avatar
Gabriel Moreau committed
1025
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
1026
	if (!$iostat)
Gabriel Moreau's avatar
Gabriel Moreau committed
1027
1028
1029
1030
		$err_msg = $stmt->errorInfo()[2];
	return $err_msg;
}

1031
1032
1033
1034
1035
// ---------------------------------------------------------------------

function del_team_by_id($pdo, $id) {
	$sql = 'DELETE LOW_PRIORITY FROM equipe WHERE id = ? LIMIT 1';
	$stmt = $pdo->prepare($sql);
Gabriel Moreau's avatar
Gabriel Moreau committed
1036
1037
	$iostat = $stmt->execute(array($id));
	return $iostat;
1038
1039
}

1040
1041
// ---------------------------------------------------------------------
// User
Gabriel Moreau's avatar
Gabriel Moreau committed
1042
// ---------------------------------------------------------------------
1043
1044

function get_user_by_id($pdo, $id) {
Gabriel Moreau's avatar
Gabriel Moreau committed
1045
	$sql = 'SELECT id, nom, prenom FROM users WHERE id = ?;';
1046
1047
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
1048
1049
1050
1051
1052
1053
	$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
1054
// ---------------------------------------------------------------------
1055
1056
1057
1058
1059
1060
1061
1062
1063

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

Gabriel Moreau's avatar
Gabriel Moreau committed
1066
// ---------------------------------------------------------------------
1067

1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
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
1078
// ---------------------------------------------------------------------
1079

1080
1081
function get_user_listall_by_logged_level($pdo, $logged_level) {
	if ($logged_level > 3)       // lorsqu'on est haut place, on voit tout le monde
1082
		$sql = 'SELECT * FROM users ORDER BY nom, prenom;';
1083
	else if ($logged_level == 3) // losrqu'on est de niveau 3, on voit tout le monde sauf les users de plus haut level
1084
		$sql = 'SELECT * FROM users WHERE level < 4 ORDER BY nom, prenom;';
1085
	else                         // lorsqu'on est < 3, on voit tout le monde sauf le suser de level > 3 et les users non valides
1086
		$sql = 'SELECT * FROM users WHERE valid = 1 and level < 3 ORDER BY nom, prenom;';
1087
1088
1089
1090
1091
1092
	$stmt = $pdo->prepare($sql);
    $stmt->execute();
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

1093
1094
// ---------------------------------------------------------------------

1095
1096
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;';
1097
	$stmt = $pdo->prepare($sql);
1098
    $stmt->execute(array($level_min, $id_bonus));
1099
1100
1101
1102
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
// ---------------------------------------------------------------------

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);
Gabriel Moreau's avatar
Gabriel Moreau committed
1118
	$iostat = $stmt->execute(array($familyname, $firstname, $login, $password, $email, $level, $tel, $team_id, $theme));
1119
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
1120
	if (!$iostat)
1121
1122
1123
1124
		$err_msg = $stmt->errorInfo()[2];
	return array($pdo->lastInsertId(), $err_msg);
}

1125
1126
// ---------------------------------------------------------------------

1127
1128
1129
function set_user_password_by_id($pdo, $user_id, $user_password) {
	$sql = 'UPDATE users SET password = ? WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
Gabriel Moreau's avatar
Gabriel Moreau committed
1130
1131
	$iostat = $stmt->execute(array($user_password, $user_id));
	return $iostat;
1132
1133
1134
1135
}

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

1136
1137
1138
function set_user_status_by_id($pdo, $user_id, $user_status) {
	$sql = 'UPDATE users SET valid = ? WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
Gabriel Moreau's avatar
Gabriel Moreau committed
1139
1140
	$iostat = $stmt->execute(array($user_status, $user_id));
	return $iostat;
1141
1142
}

Gabriel Moreau's avatar
Gabriel Moreau committed
1143
1144
1145
// ---------------------------------------------------------------------

function set_user_update($pdo, $user_id, $familyname, $firstname, $email, $level, $tel, $team_id, $theme) {
Gabriel Moreau's avatar
Gabriel Moreau committed
1146
	$sql = 'UPDATE LOW_PRIORITY users SET nom = ?, prenom = ?, email = ?, level = ?, tel = ?, equipe = ?, theme = ? WHERE id = ?;';
Gabriel Moreau's avatar
Gabriel Moreau committed
1147
	$stmt = $pdo->prepare($sql);