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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

function last_id_db() {
	return mysql_insert_id();
}

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

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

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

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

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

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

89
90
91
92
93
94
95
96
97
98
99
100
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;
}

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

101
102
103
104
105
106
107
108
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
109
// ---------------------------------------------------------------------
110

111
112
113
114
115
116
117
118
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'];
}

119
120
// ---------------------------------------------------------------------

121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
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]);
138
		return false;
139
140
141
142
143
144
	}

	if (!preg_match('/\.pdf$/i', $datasheet_filename_upload)) {
		error_log('Error: datasheet file not a pdf - '.$datasheet_filename_upload);
		return false;
	}
145
146
147
148
149

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

Gabriel Moreau's avatar
Gabriel Moreau committed
150
	$datasheet_filename_no_ext = pathinfo($datasheet_filename_upload, PATHINFO_FILENAME);
Gabriel Moreau's avatar
Gabriel Moreau committed
151
	$datasheet_filename_kebab = string_to_filename_kebab($datasheet_filename_no_ext).'.pdf';
152
153
154

	$sql1 = 'INSERT INTO datasheet (description, id_equipment) VALUES (?, ?);';
	$stmt1 = $pdo->prepare($sql1);
Gabriel Moreau's avatar
Gabriel Moreau committed
155
	$stmt1->execute(array($datasheet_filename_no_ext, $equipment_id));
156
157
158
159
160
	$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
161
	$stmt2->execute(array($sub_path.'/'.$datasheet_filename_kebab, $id_datasheet));
162
163
164
165

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

	$status = move_uploaded_file($datasheet_tmp_file, $new_dir.'/'.$datasheet_filename_kebab);
168
	if (!$status) {
169
		error_log('Error: not move datasheet file '.$datasheet_filename_upload.' to '.$datasheet_filename_kebab);
170
171
172
		del_datasheet($pdo, $id_datasheet);
		return false;
	}
173
174
175
176

	return $id_datasheet;
}

177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
// ---------------------------------------------------------------------

function del_datasheet($pdo, $id) {
	$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;
}

198
199
// ---------------------------------------------------------------------
// Equipment
Gabriel Moreau's avatar
Gabriel Moreau committed
200
// ---------------------------------------------------------------------
201

202
function get_equipment_by_id($pdo, $id) {
203
204
205
	$sql = 'SELECT id, nom FROM Listing WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
206
207
208
209
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
210
211
}

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

214
function get_equipment_all_by_id($pdo, $id) {
215
216
217
	$sql = 'SELECT * FROM Listing WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
218
219
220
221
222
223
	$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
224
// ---------------------------------------------------------------------
225

226
function get_equipment_listall($pdo) {
227
228
	// $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;';
229
230
231
232
233
234
235
236
237
	$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) {
238
239
	//$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;';
240
241
242
243
244
245
246
247
248
	$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) {
249
	$sql = 'SELECT * FROM Listing WHERE categorie = ? ORDER BY nom;';
250
251
252
253
254
255
256
257
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id_category));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

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

258
function get_equipment_listshort($pdo) {
259
	$sql = 'SELECT id, nom FROM Listing ORDER BY categorie, nom;';
260
261
	$stmt = $pdo->prepare($sql);
	$stmt->execute();
262
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
263
	return $result_fetch;
264
265
}

Gabriel Moreau's avatar
Gabriel Moreau committed
266
// ---------------------------------------------------------------------
267

Gabriel Moreau's avatar
Gabriel Moreau committed
268
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
269
	$sql = 'INSERT INTO Listing (categorie, nom, modele, gamme, equipe, fournisseur, achat, responsable, reparation, accessoires, inventaire, notice, barcode, loanable)';
270
	$sql .=            ' VALUES (?,         ?,   ?,      ?,     ?,      ?,           ?,     ?,           ?,          ?,           ?,          ?,      ?,       ?);';
271
	$stmt = $pdo->prepare($sql);
Gabriel Moreau's avatar
Gabriel Moreau committed
272
	$status = $stmt->execute(array($categorie, $nom, $modele, $feature, $equipe, $fourn, $achat, $tech, $reparation, $accessoires, $inventaire, $notice, $barcode, $loanable));
273
274
	$err_msg = '';
	if (!$status)
Gabriel Moreau's avatar
Gabriel Moreau committed
275
		$err_msg = $stmt->errorInfo()[2];
276
	return array($pdo->lastInsertId(), $err_msg);
277
278
279
280
}

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

281
282
283
284
285
286
287
288
289
290
291
292
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;
}

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

293
function del_equipment_by_id($pdo, $id) {
294
295
	$sql = 'DELETE LOW_PRIORITY FROM Listing WHERE id = ? LIMIT 1;';
	$stmt = $pdo->prepare($sql);
296
297
	$status = $stmt->execute(array($id));
	return $status;
298
299
300
301
302
303
}

// ---------------------------------------------------------------------
// Category
// ---------------------------------------------------------------------

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

Gabriel Moreau's avatar
Gabriel Moreau committed
312
// ---------------------------------------------------------------------
313

Gabriel Moreau's avatar
Gabriel Moreau committed
314
315
316
317
318
319
320
321
322
323
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;
}

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

324
325
326
327
328
329
330
331
332
333
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;
}

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

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

Gabriel Moreau's avatar
Gabriel Moreau committed
341
342
343
344
345
346
347
348
349
350
// ---------------------------------------------------------------------

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

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

Gabriel Moreau's avatar
Gabriel Moreau committed
351
352
353
function del_category_by_id($pdo, $id) {
	$sql = 'DELETE LOW_PRIORITY FROM categorie WHERE id = ? LIMIT 1';
	$stmt = $pdo->prepare($sql);
354
355
	$status = $stmt->execute(array($id));
	return $status;
Gabriel Moreau's avatar
Gabriel Moreau committed
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
402
	//$sql = 'SELECT * FROM pret;';
	$sql = 'SELECT DISTINCT l.*, e.nom AS equipment_name FROM pret AS l INNER JOIN Listing AS e ON l.nom = e.id ORDER BY e.nom;';
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
439
440
// ---------------------------------------------------------------------

function del_loan($pdo, $id) {
	$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
// ---------------------------------------------------------------------
// Version
722
723
// ---------------------------------------------------------------------

724
725
726
727
function get_version_by_name($pdo, $name) {
	$sql = 'SELECT version FROM version WHERE name = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($name));
728
	$version_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
729
730
731
732
	if (count($version_fetch) > 0)
		return $version_fetch[0];
	return false;
}
Gabriel Moreau's avatar
Gabriel Moreau committed
733
// ---------------------------------------------------------------------
734
735
736
737
738
739
740
741
742

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

743
?>