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

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

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

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

function connect_db() {
	try{
		$pdo = new PDO('mysql:host='.GESTEX_DB_SERVER.'; dbname='.GESTEX_DB_DATABASE, GESTEX_DB_USER, GESTEX_DB_PASSWORD);
	}
	catch(PDOException $exception){
		error_log('Connection error: '.$exception->getMessage());
		echo $exception->getMessage();
		return false;
	}
	return $pdo;
}

Gabriel Moreau's avatar
Gabriel Moreau committed
22
// ---------------------------------------------------------------------
23

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

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

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

34
35
36
37
38
39
function query_db($statement) {
	$result   = mysql_query($statement) or die("<pre>\n\nCan't perform query: " . mysql_error() . " \n\n$statement\n\n</pre>");
	$num_rows = numrows_db($result);
	return array($result, $num_rows);
}

Gabriel Moreau's avatar
Gabriel Moreau committed
40
// ---------------------------------------------------------------------
41
42
43
44
45

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

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

function result_db($result,$i=-1) {
	if ($i >= 0) {
		@mysql_data_seek($result,$i);
	}
	return mysql_fetch_array($result);
}

Gabriel Moreau's avatar
Gabriel Moreau committed
55
// ---------------------------------------------------------------------
56
57
58
59
60

function last_id_db() {
	return mysql_insert_id();
}

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

function check_val_in_db($pdo, $table, $col, $value) {
	//teste l'existence de $value dans le champ $col de la table $table
	//echo "check in:".$table.":".$col." for ".$value."<br />";
	$sql = 'SELECT * FROM ? WHERE ? = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($table, $col, $value));
	$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

	///echo "check_val:".numrows_db($reponse)."<br />";
	//renvoie 0 si non trouve
	//renvoie le nbre d'occurences autrement
	if (count($result) > 0)
		return true;
	return false;
}

79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
// ---------------------------------------------------------------------
// 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);
	$status = $stmt->execute(array($id));
	return $status;
}

137
138
// ---------------------------------------------------------------------
// Datasheet
Gabriel Moreau's avatar
Gabriel Moreau committed
139
// ---------------------------------------------------------------------
140

Gabriel Moreau's avatar
Gabriel Moreau committed
141
function get_datasheet_basepath() {
142
143
144
145
146
	return './data/datasheet';
}

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

147
148
149
150
151
152
153
154
155
156
157
158
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;
}

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

159
160
161
162
163
164
165
166
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
167
// ---------------------------------------------------------------------
168

169
170
171
172
173
174
175
176
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'];
}

177
178
// ---------------------------------------------------------------------

179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
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]);
196
		return false;
197
198
199
200
201
202
	}

	if (!preg_match('/\.pdf$/i', $datasheet_filename_upload)) {
		error_log('Error: datasheet file not a pdf - '.$datasheet_filename_upload);
		return false;
	}
203
204
205
206
207

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

Gabriel Moreau's avatar
Gabriel Moreau committed
208
	$datasheet_filename_no_ext = pathinfo($datasheet_filename_upload, PATHINFO_FILENAME);
Gabriel Moreau's avatar
Gabriel Moreau committed
209
	$datasheet_filename_kebab = string_to_filename_kebab($datasheet_filename_no_ext).'.pdf';
210
211
212

	$sql1 = 'INSERT INTO datasheet (description, id_equipment) VALUES (?, ?);';
	$stmt1 = $pdo->prepare($sql1);
Gabriel Moreau's avatar
Gabriel Moreau committed
213
	$stmt1->execute(array($datasheet_filename_no_ext, $equipment_id));
214
215
216
217
218
	$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
219
	$stmt2->execute(array($sub_path.'/'.$datasheet_filename_kebab, $id_datasheet));
220
221
222
223

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

	$status = move_uploaded_file($datasheet_tmp_file, $new_dir.'/'.$datasheet_filename_kebab);
226
	if (!$status) {
227
		error_log('Error: not move datasheet file '.$datasheet_filename_upload.' to '.$datasheet_filename_kebab);
228
		del_datasheet_by_id($pdo, $id_datasheet);
229
230
		return false;
	}
231
232
233
234

	return $id_datasheet;
}

235
236
// ---------------------------------------------------------------------

237
function del_datasheet_by_id($pdo, $id) {
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
	$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))
		$status = unlink($datasheet_basepath.'/'.$datasheet_pathname);

	if (!empty($datasheet_dirname) and is_dir($datasheet_basepath.'/'.$datasheet_dirname))
		$status = rmdir($datasheet_basepath.'/'.$datasheet_dirname);

	$sql = 'DELETE LOW_PRIORITY FROM datasheet WHERE id = ? LIMIT 1;';
	$stmt = $pdo->prepare($sql);
	$status = $stmt->execute(array($id));
	return $status;
}

256
257
// ---------------------------------------------------------------------
// Equipment
Gabriel Moreau's avatar
Gabriel Moreau committed
258
// ---------------------------------------------------------------------
259

260
function get_equipment_by_id($pdo, $id) {
261
262
263
	$sql = 'SELECT id, nom FROM Listing WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
264
265
266
267
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
268
269
}

Gabriel Moreau's avatar
Gabriel Moreau committed
270
// ---------------------------------------------------------------------
271

272
function get_equipment_all_by_id($pdo, $id) {
273
274
275
	$sql = 'SELECT * FROM Listing WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
276
277
278
279
280
281
	$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
282
// ---------------------------------------------------------------------
283

284
function get_equipment_listall($pdo) {
285
286
	// $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;';
287
288
289
290
291
292
293
294
295
	$stmt = $pdo->prepare($sql);
	$stmt->execute();
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

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

function get_equipment_listall_by_team($pdo, $id_team) {
296
297
	//$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;';
298
299
300
301
302
303
304
305
306
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id_team));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

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

function get_equipment_listall_by_category($pdo, $id_category) {
307
	$sql = 'SELECT * FROM Listing WHERE categorie = ? ORDER BY nom;';
308
309
310
311
312
313
314
315
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id_category));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

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

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

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

Gabriel Moreau's avatar
Gabriel Moreau committed
326
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
327
	$sql = 'INSERT INTO Listing (categorie, nom, modele, gamme, equipe, fournisseur, achat, responsable, reparation, accessoires, inventaire, notice, barcode, loanable)';
328
	$sql .=            ' VALUES (?,         ?,   ?,      ?,     ?,      ?,           ?,     ?,           ?,          ?,           ?,          ?,      ?,       ?);';
329
	$stmt = $pdo->prepare($sql);
Gabriel Moreau's avatar
Gabriel Moreau committed
330
	$status = $stmt->execute(array($categorie, $nom, $modele, $feature, $equipe, $fourn, $achat, $tech, $reparation, $accessoires, $inventaire, $notice, $barcode, $loanable));
331
332
	$err_msg = '';
	if (!$status)
Gabriel Moreau's avatar
Gabriel Moreau committed
333
		$err_msg = $stmt->errorInfo()[2];
334
	return array($pdo->lastInsertId(), $err_msg);
335
336
337
338
}

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

339
340
341
342
343
344
345
346
347
348
349
350
function set_equipment_update($pdo, $id_equipment, $categorie, $nom, $modele, $feature, $equipe, $fourn, $achat, $tech, $reparation, $accessoires, $inventaire, $notice, $barcode, $loanable) {
	$sql = 'UPDATE Listing SET categorie = ?, nom = ?, modele = ?, gamme = ?, equipe = ?, fournisseur = ?, achat = ?, responsable = ?, reparation = ?, accessoires = ?, inventaire = ?, notice = ?, barcode = ?, loanable = ? WHERE id = ?;)';
	$stmt = $pdo->prepare($sql);
	$status = $stmt->execute(array($categorie, $nom, $modele, $feature, $equipe, $fourn, $achat, $tech, $reparation, $accessoires, $inventaire, $notice, $barcode, $loanable, $id_equipment));
	$err_msg = '';
	if (!$status)
		$err_msg = $stmt->errorInfo()[2];
	return $err_msg;
}

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

351
function del_equipment_by_id($pdo, $id) {
352
353
	$sql = 'DELETE LOW_PRIORITY FROM Listing WHERE id = ? LIMIT 1;';
	$stmt = $pdo->prepare($sql);
354
355
	$status = $stmt->execute(array($id));
	return $status;
356
357
358
359
}

// ---------------------------------------------------------------------
// Loan
360
361
// ---------------------------------------------------------------------

362
363
364
365
366
367
368
369
370
371
372
373
374
function get_loan_all_by_id($pdo, $id) {
	$sql = 'SELECT * FROM pret WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
}

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

function get_loan_short_by_id_equipment($pdo, $id_equipment) {
Gabriel Moreau's avatar
Gabriel Moreau committed
375
	// recupere l'appareil via l'id qui est mis dans un champs texte (nom) !
376
	$sql = 'SELECT id FROM pret WHERE nom = ?;';
Gabriel Moreau's avatar
Gabriel Moreau committed
377
378
379
380
381
382
383
384
385
386
	$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;
}

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

387
388
389
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
390
	$stmt = $pdo->prepare($sql);
391
	$stmt->execute(array($id_equipment));
Gabriel Moreau's avatar
Gabriel Moreau committed
392
393
394
395
396
397
398
399
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
}

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

400
function get_loan_listall($pdo) {
401
	//$sql = 'SELECT * FROM pret;';
402
	$sql = 'SELECT DISTINCT l.*, e.nom AS equipment_name FROM pret AS l INNER JOIN Listing AS e ON l.nom = e.id ORDER BY l.retour DESC, l.emprunt DESC, e.nom;';
403
404
405
406
407
408
409
410
	$stmt = $pdo->prepare($sql);
	$stmt->execute();
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

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

411
412
413
414
415
416
417
418
419
420
function get_loan_find($pdo, $find) {
	$sql = 'SELECT * FROM pret WHERE commentaire RLIKE ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($find));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

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

Gabriel Moreau's avatar
Gabriel Moreau committed
421
422
423
424
425
426
427
function set_loan_new($pdo, $id_equipment, $id_team, $date_begin, $date_end, $comment) {
	$sql = 'INSERT INTO pret (nom, equipe, emprunt, retour, commentaire) VALUES (?, ?, ?, ?, ?);';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id_equipment, $id_team, $date_begin, $date_end, $comment));
	return $pdo->lastInsertId();
}

Gabriel Moreau's avatar
Gabriel Moreau committed
428
429
430
431
432
433
434
435
// ---------------------------------------------------------------------

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
436
437
// ---------------------------------------------------------------------

438
function del_loan_by_id($pdo, $id) {
Gabriel Moreau's avatar
Gabriel Moreau committed
439
440
	$sql = 'DELETE LOW_PRIORITY FROM pret WHERE id = ? LIMIT 1;';
	$stmt = $pdo->prepare($sql);
Gabriel Moreau's avatar
Gabriel Moreau committed
441
442
	$status = $stmt->execute(array($id));
	return $status;
Gabriel Moreau's avatar
Gabriel Moreau committed
443
444
}

445
446
// ---------------------------------------------------------------------
// Supplier
Gabriel Moreau's avatar
Gabriel Moreau committed
447
448
// ---------------------------------------------------------------------

449
450
function get_supplier_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM fournisseurs WHERE id = ?;';
451
452
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
453
454
455
456
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
457
458
}

Gabriel Moreau's avatar
Gabriel Moreau committed
459
// ---------------------------------------------------------------------
460

461
462
463
464
465
466
467
468
469
470
471
472
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;
}

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

473
function get_supplier_listshort($pdo) {
474
	$sql = 'SELECT id, nom FROM fournisseurs ORDER BY nom;';
475
	$stmt = $pdo->prepare($sql);
476
	$stmt->execute();
477
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
478
	return $supplier_fetch;
479
480
}

Gabriel Moreau's avatar
Gabriel Moreau committed
481
// ---------------------------------------------------------------------
482

483
function get_supplier_listall($pdo) {
484
	$sql = 'SELECT * FROM fournisseurs ORDER BY nom;';
485
	$stmt = $pdo->prepare($sql);
486
	$stmt->execute();
487
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
488
	return $supplier_fetch;
489
490
}

Gabriel Moreau's avatar
Gabriel Moreau committed
491
// ---------------------------------------------------------------------
492

493
494
495
496
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 ?;';
497
	$stmt = $pdo->prepare($sql);
498
	$stmt->execute(array($find, $find));
499
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
500
	return $supplier_fetch;
501
502
}

503
504
// ---------------------------------------------------------------------

Gabriel Moreau's avatar
Gabriel Moreau committed
505
506
507
508
509
510
511
512
513
514
515
516
function set_supplier_new($pdo, $name, $address, $tel, $fax, $email, $www, $contact, $description) {
	$sql = 'INSERT INTO fournisseurs (nom, adresse, mail, www, tel, fax, contact, descr) VALUES (?, ?, ?, ?, ?, ?, ?, ?)';
	$stmt = $pdo->prepare($sql);
	$status = $stmt->execute(array($name, $address, $tel, $fax, $email, $www, $contact, $description));
	$err_msg = '';
	if (!$status)
		$err_msg = $stmt->errorInfo()[2];
	return array($pdo->lastInsertId(), $err_msg);
}

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

517
518
519
520
521
522
523
524
525
526
function set_supplier_update($pdo, $id_supplier, $name, $address, $tel, $fax, $email, $www, $contact, $description) {
	$sql = 'UPDATE LOW_PRIORITY fournisseurs  SET nom = ?, adresse = ?, tel = ?, fax = ?, mail = ?, www = ?, contact = ?, descr = ? WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$status = $stmt->execute(array($name, $address, $tel, $fax, $email, $www, $contact, $description, $id_supplier));
	$err_msg = '';
	if (!$status)
		$err_msg = $stmt->errorInfo()[2];
	return $err_msg;
}

527
// ---------------------------------------------------------------------
Gabriel Moreau's avatar
Gabriel Moreau committed
528
529
530
531
532
533
534
535
536

function del_supplier_by_id($pdo, $id) {
	$sql = 'DELETE LOW_PRIORITY FROM fournisseurs WHERE id = ? LIMIT 1;';
	$stmt = $pdo->prepare($sql);
	$status = $stmt->execute(array($id));
	return $status;
}

// ---------------------------------------------------------------------
537
// Team
Gabriel Moreau's avatar
Gabriel Moreau committed
538
// ---------------------------------------------------------------------
539

540
541
function get_team_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM equipe WHERE id = ?;';
542
543
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
544
545
546
547
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
548
549
}

Gabriel Moreau's avatar
Gabriel Moreau committed
550
// ---------------------------------------------------------------------
551

552
553
554
555
556
557
558
559
560
561
562
563
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;
}

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

564
function get_team_listshort($pdo) {
565
	$sql = 'SELECT id, nom FROM equipe ORDER BY nom;';
566
	$stmt = $pdo->prepare($sql);
567
	$stmt->execute();
568
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
569
	return $team_fetch;
570
571
}

Gabriel Moreau's avatar
Gabriel Moreau committed
572
// ---------------------------------------------------------------------
573

574
function get_team_listall($pdo) {
575
	$sql = 'SELECT * FROM equipe ORDER BY nom;';
576
	$stmt = $pdo->prepare($sql);
577
	$stmt->execute();
578
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
579
	return $team_fetch;
580
581
}

Gabriel Moreau's avatar
Gabriel Moreau committed
582
// ---------------------------------------------------------------------
583

584
function get_team_with_appareil($pdo) {
585
	$sql = 'SELECT DISTINCT equipe.id, equipe.nom FROM equipe INNER JOIN Listing ON equipe.id = Listing.equipe ORDER BY equipe.nom;';
586
	$stmt = $pdo->prepare($sql);
587
	$stmt->execute();
588
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
589
	return $team_fetch;
590
591
}

Gabriel Moreau's avatar
Gabriel Moreau committed
592
593
// ---------------------------------------------------------------------

594
595
596
597
598
599
600
601
602
603
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
604
605
606
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
607
608
609
610
611
	$status = $stmt->execute(array($name, $description, $account, $manager));
	$err_msg = '';
	if (!$status)
		$err_msg = $stmt->errorInfo()[2];
	return array($pdo->lastInsertId(), $err_msg);
Gabriel Moreau's avatar
Gabriel Moreau committed
612
613
}

Gabriel Moreau's avatar
Gabriel Moreau committed
614
615
616
617
618
619
620
621
622
623
624
625
// ---------------------------------------------------------------------

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

626
627
628
629
630
631
632
633
634
// ---------------------------------------------------------------------

function del_team_by_id($pdo, $id) {
	$sql = 'DELETE LOW_PRIORITY FROM equipe WHERE id = ? LIMIT 1';
	$stmt = $pdo->prepare($sql);
	$status = $stmt->execute(array($id));
	return $status;
}

635
636
// ---------------------------------------------------------------------
// User
Gabriel Moreau's avatar
Gabriel Moreau committed
637
// ---------------------------------------------------------------------
638
639

function get_user_by_id($pdo, $id) {
Gabriel Moreau's avatar
Gabriel Moreau committed
640
	$sql = 'SELECT id, nom, prenom FROM users WHERE id = ?;';
641
642
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
643
644
645
646
647
648
	$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
649
// ---------------------------------------------------------------------
650
651
652
653
654
655
656
657
658

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

Gabriel Moreau's avatar
Gabriel Moreau committed
661
// ---------------------------------------------------------------------
662

663
664
665
666
667
668
669
670
671
672
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
673
// ---------------------------------------------------------------------
674

675
676
function get_user_listall_by_logged_level($pdo, $logged_level) {
	if ($logged_level > 3)       // lorsqu'on est haut place, on voit tout le monde
677
		$sql = 'SELECT * FROM users ORDER BY nom, prenom;';
678
	else if ($logged_level == 3) // losrqu'on est de niveau 3, on voit tout le monde sauf les users de plus haut level
679
		$sql = 'SELECT * FROM users WHERE level < 4 ORDER BY nom, prenom;';
680
	else                         // lorsqu'on est < 3, on voit tout le monde sauf le suser de level > 3 et les users non valides
681
		$sql = 'SELECT * FROM users WHERE valid = 1 and level < 3 ORDER BY nom, prenom;';
682
683
684
685
686
687
	$stmt = $pdo->prepare($sql);
    $stmt->execute();
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

688
689
// ---------------------------------------------------------------------

690
691
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;';
692
	$stmt = $pdo->prepare($sql);
693
    $stmt->execute(array($level_min, $id_bonus));
694
695
696
697
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
// ---------------------------------------------------------------------

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

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

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

720
721
// ---------------------------------------------------------------------

722
723
724
725
726
727
728
729
730
function set_user_password_by_id($pdo, $user_id, $user_password) {
	$sql = 'UPDATE users SET password = ? WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$status = $stmt->execute(array($user_password, $user_id));
	return $status;
}

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

731
732
733
734
735
736
737
function set_user_status_by_id($pdo, $user_id, $user_status) {
	$sql = 'UPDATE users SET valid = ? WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$status = $stmt->execute(array($user_status, $user_id));
	return $status;
}

Gabriel Moreau's avatar
Gabriel Moreau committed
738
739
740
741
742
743
744
745
746
747
748
749
// ---------------------------------------------------------------------

function set_user_update($pdo, $user_id, $familyname, $firstname, $email, $level, $tel, $team_id, $theme) {
	$sql = 'UPDATE LOW_PRIORITY users SET nom = ?, prenom = ?, email = ?, level = ?, tel= ?, equipe = ?, theme = ? WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$status = $stmt->execute(array($familyname, $firstname, $email, $level, $tel, $team_id, $theme));
	$err_msg = '';
	if (!$status)
		$err_msg = $stmt->errorInfo()[2];
	return $err_msg;
}

750
751
// ---------------------------------------------------------------------
// Version
752
753
// ---------------------------------------------------------------------

754
755
756
757
function get_version_by_name($pdo, $name) {
	$sql = 'SELECT version FROM version WHERE name = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($name));
758
	$version_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
759
760
761
762
	if (count($version_fetch) > 0)
		return $version_fetch[0];
	return false;
}
Gabriel Moreau's avatar
Gabriel Moreau committed
763
// ---------------------------------------------------------------------
764
765
766
767
768
769
770
771
772

function set_version_by_name($pdo, $name, $version) {
	$sql = 'INSERT INTO version (name, version) VALUES (?, ?);';
	if (get_version_by_name($pdo, $name))
		$sql = 'UPDATE version SET version = ? WHERE name = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($name, $version));
}

773
?>