db-functions.php 36.3 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
458
459
/**
 * Supprime un equipement par son ID
 */
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
637
638
639
640
/**
 * Récupère tout le contenu des prets étant dans l'intervalle d'emprunt
 * des autres prets d'un équipement. Renverra "false" s'il n'y en a pas
 * 
 * @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
function get_loans_interval_by_id_except_loan($pdo, $id_equipment, $from, $to, $except_id) {
654
655
656
657
658
659
660
661
662
663
664
	$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;
}

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

665
666
667
668
669
670
671
672
673
674
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"];
}

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

675
function get_last_reserved_loan($pdo, $id_equipment) {
676
	$sql = 'SELECT * FROM pret WHERE nom = ? AND status = ? ORDER BY retour DESC LIMIT 1;';
677
	$stmt = $pdo->prepare($sql);
678
	$stmt->execute(array($id_equipment, STATUS_LOAN_RETURNED));
679
680
681
682
683
684
685
686
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch;
	return false;
}

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

687
function set_loan_borrowed_new($pdo, $id_equipment, $id_team, $date_begin, $date_end, $comment) {
688
	$sql = 'INSERT INTO pret (nom, equipe, emprunt, retour, commentaire, status) VALUES (?, ?, ?, ?, ?, ?);';
689
	$stmt = $pdo->prepare($sql);
690
	$stmt->execute(array($id_equipment, $id_team, $date_begin, $date_end, $comment, STATUS_LOAN_BORROWED));
691
692
693
694
695
	return $pdo->lastInsertId();
}

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

696
697
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 (?, ?, ?, ?, ?, ?);';
698
	$stmt = $pdo->prepare($sql);
699
700
	$stmt->execute(array($id_equipment, $id_team, $date_begin, $date_end, $comment, STATUS_LOAN_RESERVED));
	return $pdo->lastInsertId();
701
702
703
704
}

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

705
706
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
707
	$stmt = $pdo->prepare($sql);
708
	$stmt->execute(array(STATUS_LOAN_BORROWED, $id_loan));
Gabriel Moreau's avatar
Gabriel Moreau committed
709
710
}

Gabriel Moreau's avatar
Gabriel Moreau committed
711
712
713
714
715
716
717
718
// ---------------------------------------------------------------------

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
719
720
// ---------------------------------------------------------------------

721
function del_loan_by_id($pdo, $id_loan) {
Gabriel Moreau's avatar
Gabriel Moreau committed
722
723
	$sql = 'DELETE LOW_PRIORITY FROM pret WHERE id = ? LIMIT 1;';
	$stmt = $pdo->prepare($sql);
724
	$iostat = $stmt->execute(array($id_loan));
Gabriel Moreau's avatar
Gabriel Moreau committed
725
	return $iostat;
Gabriel Moreau's avatar
Gabriel Moreau committed
726
727
}

728
729
// ---------------------------------------------------------------------

730
731
function set_loan_to_returned($pdo, $id_loan) {
	$sql = 'UPDATE LOW_PRIORITY pret SET status = ?, retour = CURRENT_DATE WHERE id = ?;';
732
	$stmt = $pdo->prepare($sql);
733
	$iostat = $stmt->execute(array(STATUS_LOAN_RETURNED, $id_loan));
734
735
736
	return $iostat;
}

737

738
739
// ---------------------------------------------------------------------
// Supplier
Gabriel Moreau's avatar
Gabriel Moreau committed
740
741
// ---------------------------------------------------------------------

742
743
function get_supplier_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM fournisseurs WHERE id = ?;';
744
745
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
746
747
748
749
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
750
751
}

Gabriel Moreau's avatar
Gabriel Moreau committed
752
// ---------------------------------------------------------------------
753

754
755
756
757
758
759
760
761
762
763
764
765
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;
}

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

766
function get_supplier_listshort($pdo) {
767
	$sql = 'SELECT id, nom FROM fournisseurs ORDER BY nom;';
768
	$stmt = $pdo->prepare($sql);
769
	$stmt->execute();
770
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
771
	return $supplier_fetch;
772
773
}

Gabriel Moreau's avatar
Gabriel Moreau committed
774
// ---------------------------------------------------------------------
775

776
function get_supplier_listall($pdo) {
777
	$sql = 'SELECT * FROM fournisseurs ORDER BY nom;';
778
	$stmt = $pdo->prepare($sql);
779
	$stmt->execute();
780
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
781
	return $supplier_fetch;
782
783
}

Gabriel Moreau's avatar
Gabriel Moreau committed
784
// ---------------------------------------------------------------------
785

786
787
788
789
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 ?;';
790
	$stmt = $pdo->prepare($sql);
791
	$stmt->execute(array($find, $find));
792
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
793
	return $supplier_fetch;
794
795
}

796
797
// ---------------------------------------------------------------------

Gabriel Moreau's avatar
Gabriel Moreau committed
798
799
800
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
801
	$iostat = $stmt->execute(array($name, $address, $tel, $fax, $email, $www, $contact, $description));
Gabriel Moreau's avatar
Gabriel Moreau committed
802
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
803
	if (!$iostat)
Gabriel Moreau's avatar
Gabriel Moreau committed
804
805
806
807
808
809
		$err_msg = $stmt->errorInfo()[2];
	return array($pdo->lastInsertId(), $err_msg);
}

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

810
811
812
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
813
	$iostat = $stmt->execute(array($name, $address, $tel, $fax, $email, $www, $contact, $description, $id_supplier));
814
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
815
	if (!$iostat)
816
817
818
819
		$err_msg = $stmt->errorInfo()[2];
	return $err_msg;
}

820
// ---------------------------------------------------------------------
Gabriel Moreau's avatar
Gabriel Moreau committed
821
822
823
824

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
825
826
	$iostat = $stmt->execute(array($id));
	return $iostat;
Gabriel Moreau's avatar
Gabriel Moreau committed
827
828
829
}

// ---------------------------------------------------------------------
830
// Team
Gabriel Moreau's avatar
Gabriel Moreau committed
831
// ---------------------------------------------------------------------
832

833
834
function get_team_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM equipe WHERE id = ?;';
835
836
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
837
838
839
840
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
841
842
}

Gabriel Moreau's avatar
Gabriel Moreau committed
843
// ---------------------------------------------------------------------
844

845
846
847
848
849
850
851
852
853
854
855
856
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;
}

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

857
function get_team_listshort($pdo) {
858
	$sql = 'SELECT id, nom FROM equipe ORDER BY nom;';
859
	$stmt = $pdo->prepare($sql);
860
	$stmt->execute();
861
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
862
	return $team_fetch;
863
864
}

Gabriel Moreau's avatar
Gabriel Moreau committed
865
// ---------------------------------------------------------------------
866

867
function get_team_listall($pdo) {
868
	$sql = 'SELECT * FROM equipe ORDER BY nom;';
869
	$stmt = $pdo->prepare($sql);
870
	$stmt->execute();
871
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
872
	return $team_fetch;
873
874
}

Gabriel Moreau's avatar
Gabriel Moreau committed
875
// ---------------------------------------------------------------------
876

877
function get_team_with_appareil($pdo) {
878
	$sql = 'SELECT DISTINCT equipe.id, equipe.nom FROM equipe INNER JOIN Listing ON equipe.id = Listing.equipe ORDER BY equipe.nom;';
879
	$stmt = $pdo->prepare($sql);
880
	$stmt->execute();
881
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
882
	return $team_fetch;
883
884
}

Gabriel Moreau's avatar
Gabriel Moreau committed
885
886
// ---------------------------------------------------------------------

887
888
889
890
891
892
893
894
895
896
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
897
898
899
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
900
	$iostat = $stmt->execute(array($name, $description, $account, $manager));
Gabriel Moreau's avatar
Gabriel Moreau committed
901
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
902
	if (!$iostat)
Gabriel Moreau's avatar
Gabriel Moreau committed
903
904
		$err_msg = $stmt->errorInfo()[2];
	return array($pdo->lastInsertId(), $err_msg);
Gabriel Moreau's avatar
Gabriel Moreau committed
905
906
}

Gabriel Moreau's avatar
Gabriel Moreau committed
907
908
909
910
911
// ---------------------------------------------------------------------

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
912
	$iostat = $stmt->execute(array($name, $description, $account, $manager, $id_team));
Gabriel Moreau's avatar
Gabriel Moreau committed
913
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
914
	if (!$iostat)
Gabriel Moreau's avatar
Gabriel Moreau committed
915
916
917
918
		$err_msg = $stmt->errorInfo()[2];
	return $err_msg;
}

919
920
921
922
923
// ---------------------------------------------------------------------

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
924
925
	$iostat = $stmt->execute(array($id));
	return $iostat;
926
927
}

928
929
// ---------------------------------------------------------------------
// User
Gabriel Moreau's avatar
Gabriel Moreau committed
930
// ---------------------------------------------------------------------
931
932

function get_user_by_id($pdo, $id) {
Gabriel Moreau's avatar
Gabriel Moreau committed
933
	$sql = 'SELECT id, nom, prenom FROM users WHERE id = ?;';
934
935
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
936
937
938
939
940
941
	$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
942
// ---------------------------------------------------------------------
943
944
945
946
947
948
949
950
951

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

Gabriel Moreau's avatar
Gabriel Moreau committed
954
// ---------------------------------------------------------------------
955

956
957
958
959
960
961
962
963
964
965
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
966
// ---------------------------------------------------------------------
967

968
969
function get_user_listall_by_logged_level($pdo, $logged_level) {
	if ($logged_level > 3)       // lorsqu'on est haut place, on voit tout le monde
970
		$sql = 'SELECT * FROM users ORDER BY nom, prenom;';
971
	else if ($logged_level == 3) // losrqu'on est de niveau 3, on voit tout le monde sauf les users de plus haut level
972
		$sql = 'SELECT * FROM users WHERE level < 4 ORDER BY nom, prenom;';
973
	else                         // lorsqu'on est < 3, on voit tout le monde sauf le suser de level > 3 et les users non valides
974
		$sql = 'SELECT * FROM users WHERE valid = 1 and level < 3 ORDER BY nom, prenom;';
975
976
977
978
979
980
	$stmt = $pdo->prepare($sql);
    $stmt->execute();
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

981
982
// ---------------------------------------------------------------------

983
984
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;';
985
	$stmt = $pdo->prepare($sql);
986
    $stmt->execute(array($level_min, $id_bonus));
987
988
989
990
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
// ---------------------------------------------------------------------

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
1006
	$iostat = $stmt->execute(array($familyname, $firstname, $login, $password, $email, $level, $tel, $team_id, $theme));
1007
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
1008
	if (!$iostat)
1009
1010
1011
1012
		$err_msg = $stmt->errorInfo()[2];
	return array($pdo->lastInsertId(), $err_msg);
}

1013
1014
// ---------------------------------------------------------------------

1015
1016
1017
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
1018
1019
	$iostat = $stmt->execute(array($user_password, $user_id));
	return $iostat;
1020
1021
1022
1023
}

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

1024
1025
1026
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
1027
1028
	$iostat = $stmt->execute(array($user_status, $user_id));
	return $iostat;
1029
1030
}

Gabriel Moreau's avatar
Gabriel Moreau committed
1031
1032
1033
// ---------------------------------------------------------------------

function set_user_update($pdo, $user_id, $familyname, $firstname, $email, $level, $tel, $team_id, $theme) {
Gabriel Moreau's avatar
Gabriel Moreau committed
1034
	$sql = 'UPDATE LOW_PRIORITY users SET nom = ?, prenom = ?, email = ?, level = ?, tel = ?, equipe = ?, theme = ? WHERE id = ?;';
Gabriel Moreau's avatar
Gabriel Moreau committed
1035
	$stmt = $pdo->prepare($sql);
Estéban Ristich's avatar
Estéban Ristich committed
1036
	$iostat = $stmt->execute(array($familyname, $firstname, $email, $level, $tel, $team_id, $theme, $user_id));
Gabriel Moreau's avatar
Gabriel Moreau committed
1037
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
1038
	if (!$iostat)
Gabriel Moreau's avatar
Gabriel Moreau committed
1039
1040
1041
1042
		$err_msg = $stmt->errorInfo()[2];
	return $err_msg;
}

1043
1044
// ---------------------------------------------------------------------
// Version
1045
1046
// ---------------------------------------------------------------------

1047
function get_version_by_name($pdo, $name) {
Estéban Ristich's avatar
Estéban Ristich committed
1048
	$sql = 'SELECT version FROM version WHERE soft = ?;';
1049
1050
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($name));
1051
	$version_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
1052
1053
1054
1055
	if (count($version_fetch) > 0)
		return $version_fetch[0];
	return false;
}
Gabriel Moreau's avatar
Gabriel Moreau committed
1056
// ---------------------------------------------------------------------
1057
1058

function set_version_by_name($pdo, $name, $version) {
Estéban Ristich's avatar
Estéban Ristich committed
1059
	$sql = 'INSERT INTO version (soft, version) VALUES (?, ?);';
1060
	if (get_version_by_name($pdo, $name))
Estéban Ristich's avatar
Estéban Ristich committed
1061
		$sql = 'UPDATE version SET version = ? WHERE soft = ?;';
1062
1063
1064
1065
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($name, $version));
}

1066
?>