db-functions.php 28.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
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

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 />";
66
67
68
	//$sql = 'SELECT * FROM ? WHERE ? = ?;';
	$stmt = $pdo->prepare("SELECT * FROM $table WHERE $col = '$value'");
	$stmt->execute();
69
70
71
72
73
74
75
76
77
78
	$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
// ---------------------------------------------------------------------
// 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
133
134
	$iostat = $stmt->execute(array($id));
	return $iostat;
135
136
}

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

Gabriel Moreau's avatar
Gabriel Moreau committed
225
226
	$iostat = move_uploaded_file($datasheet_tmp_file, $new_dir.'/'.$datasheet_filename_kebab);
	if (!$iostat) {
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
	$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
245
		$iostat = unlink($datasheet_basepath.'/'.$datasheet_pathname);
246
247

	if (!empty($datasheet_dirname) and is_dir($datasheet_basepath.'/'.$datasheet_dirname))
Gabriel Moreau's avatar
Gabriel Moreau committed
248
		$iostat = rmdir($datasheet_basepath.'/'.$datasheet_dirname);
249
250
251

	$sql = 'DELETE LOW_PRIORITY FROM datasheet WHERE id = ? LIMIT 1;';
	$stmt = $pdo->prepare($sql);
Gabriel Moreau's avatar
Gabriel Moreau committed
252
253
	$iostat = $stmt->execute(array($id));
	return $iostat;
254
255
}

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
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id_team));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

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

306
307
308
309
310
311
312
313
314
315
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'];
}

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

316
function get_equipment_listall_by_category($pdo, $id_category) {
317
	$sql = 'SELECT * FROM Listing WHERE categorie = ? ORDER BY nom;';
318
319
320
321
322
323
324
325
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id_category));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

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

326
function get_equipment_listshort($pdo) {
327
	$sql = 'SELECT id, nom FROM Listing ORDER BY categorie, nom;';
328
329
	$stmt = $pdo->prepare($sql);
	$stmt->execute();
330
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
331
	return $result_fetch;
332
333
}

Gabriel Moreau's avatar
Gabriel Moreau committed
334
// ---------------------------------------------------------------------
335

Gabriel Moreau's avatar
Gabriel Moreau committed
336
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
337
	$sql = 'INSERT INTO Listing (categorie, nom, modele, gamme, equipe, fournisseur, achat, responsable, reparation, accessoires, inventaire, notice, barcode, loanable)';
338
	$sql .=            ' VALUES (?,         ?,   ?,      ?,     ?,      ?,           ?,     ?,           ?,          ?,           ?,          ?,      ?,       ?);';
339
	$stmt = $pdo->prepare($sql);
Gabriel Moreau's avatar
Gabriel Moreau committed
340
	$iostat = $stmt->execute(array($categorie, $nom, $modele, $feature, $equipe, $fourn, $achat, $tech, $reparation, $accessoires, $inventaire, $notice, $barcode, $loanable));
341
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
342
	if (!$iostat)
Gabriel Moreau's avatar
Gabriel Moreau committed
343
		$err_msg = $stmt->errorInfo()[2];
344
	return array($pdo->lastInsertId(), $err_msg);
345
346
347
348
}

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

349
350
351
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
352
	$iostat = $stmt->execute(array($categorie, $nom, $modele, $feature, $equipe, $fourn, $achat, $tech, $reparation, $accessoires, $inventaire, $notice, $barcode, $loanable, $id_equipment));
353
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
354
	if (!$iostat)
355
356
357
358
359
360
		$err_msg = $stmt->errorInfo()[2];
	return $err_msg;
}

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

361
function del_equipment_by_id($pdo, $id) {
362
363
	$sql = 'DELETE LOW_PRIORITY FROM Listing WHERE id = ? LIMIT 1;';
	$stmt = $pdo->prepare($sql);
Gabriel Moreau's avatar
Gabriel Moreau committed
364
365
	$iostat = $stmt->execute(array($id));
	return $iostat;
366
367
368
369
}

// ---------------------------------------------------------------------
// Loan
370
371
// ---------------------------------------------------------------------

372
373
374
375
376
377
378
379
380
381
382
383
384
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
385
	// recupere l'appareil via l'id qui est mis dans un champs texte (nom) !
386
	$sql = 'SELECT id FROM pret WHERE nom = ?;';
Gabriel Moreau's avatar
Gabriel Moreau committed
387
388
389
390
391
392
393
394
395
396
	$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;
}

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

397
398
399
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
400
	$stmt = $pdo->prepare($sql);
401
	$stmt->execute(array($id_equipment));
Gabriel Moreau's avatar
Gabriel Moreau committed
402
403
404
405
406
407
408
409
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
}

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

410
function get_loan_listall($pdo) {
411
	//$sql = 'SELECT * FROM pret;';
412
	$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;';
413
414
415
416
417
	$stmt = $pdo->prepare($sql);
	$stmt->execute();
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}
Gabriel Moreau's avatar
Gabriel Moreau committed
418
419
420
421
422
423
424
425
426
// ---------------------------------------------------------------------

function get_loan_listall_by_team($pdo, $id_team) {
	$sql = 'SELECT DISTINCT l.*, e.nom AS equipment_name FROM pret AS l INNER JOIN Listing AS e ON l.nom = e.id WHERE e.equipe = ? ORDER BY l.retour DESC, l.emprunt DESC, e.nom;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id_team));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}
427
428
429

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

430
431
432
433
434
435
436
437
438
439
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'];
}

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

440
441
442
443
444
445
446
447
448
449
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;
}

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

450
451
452
453
454
455
456
457
458
459
function get_loan_interval_by_id($pdo, $id_equipment) {
	$sql = 'SELECT emprunt, retour FROM pret WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id_equipment));
	$result_fetch = $stmt->fetcAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

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

Gabriel Moreau's avatar
Gabriel Moreau committed
460
461
462
463
464
465
466
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
467
468
469
470
471
472
473
474
// ---------------------------------------------------------------------

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
475
476
// ---------------------------------------------------------------------

477
function del_loan_by_id($pdo, $id) {
Gabriel Moreau's avatar
Gabriel Moreau committed
478
479
	$sql = 'DELETE LOW_PRIORITY FROM pret WHERE id = ? LIMIT 1;';
	$stmt = $pdo->prepare($sql);
Gabriel Moreau's avatar
Gabriel Moreau committed
480
481
	$iostat = $stmt->execute(array($id));
	return $iostat;
Gabriel Moreau's avatar
Gabriel Moreau committed
482
483
}

484

485
486
// ---------------------------------------------------------------------
// Supplier
Gabriel Moreau's avatar
Gabriel Moreau committed
487
488
// ---------------------------------------------------------------------

489
490
function get_supplier_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM fournisseurs WHERE id = ?;';
491
492
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
493
494
495
496
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
497
498
}

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

501
502
503
504
505
506
507
508
509
510
511
512
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;
}

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

513
function get_supplier_listshort($pdo) {
514
	$sql = 'SELECT id, nom FROM fournisseurs ORDER BY nom;';
515
	$stmt = $pdo->prepare($sql);
516
	$stmt->execute();
517
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
518
	return $supplier_fetch;
519
520
}

Gabriel Moreau's avatar
Gabriel Moreau committed
521
// ---------------------------------------------------------------------
522

523
function get_supplier_listall($pdo) {
524
	$sql = 'SELECT * FROM fournisseurs ORDER BY nom;';
525
	$stmt = $pdo->prepare($sql);
526
	$stmt->execute();
527
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
528
	return $supplier_fetch;
529
530
}

Gabriel Moreau's avatar
Gabriel Moreau committed
531
// ---------------------------------------------------------------------
532

533
534
535
536
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 ?;';
537
	$stmt = $pdo->prepare($sql);
538
	$stmt->execute(array($find, $find));
539
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
540
	return $supplier_fetch;
541
542
}

543
544
// ---------------------------------------------------------------------

Gabriel Moreau's avatar
Gabriel Moreau committed
545
546
547
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
548
	$iostat = $stmt->execute(array($name, $address, $tel, $fax, $email, $www, $contact, $description));
Gabriel Moreau's avatar
Gabriel Moreau committed
549
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
550
	if (!$iostat)
Gabriel Moreau's avatar
Gabriel Moreau committed
551
552
553
554
555
556
		$err_msg = $stmt->errorInfo()[2];
	return array($pdo->lastInsertId(), $err_msg);
}

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

557
558
559
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
560
	$iostat = $stmt->execute(array($name, $address, $tel, $fax, $email, $www, $contact, $description, $id_supplier));
561
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
562
	if (!$iostat)
563
564
565
566
		$err_msg = $stmt->errorInfo()[2];
	return $err_msg;
}

567
// ---------------------------------------------------------------------
Gabriel Moreau's avatar
Gabriel Moreau committed
568
569
570
571

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
572
573
	$iostat = $stmt->execute(array($id));
	return $iostat;
Gabriel Moreau's avatar
Gabriel Moreau committed
574
575
576
}

// ---------------------------------------------------------------------
577
// Team
Gabriel Moreau's avatar
Gabriel Moreau committed
578
// ---------------------------------------------------------------------
579

580
581
function get_team_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM equipe WHERE id = ?;';
582
583
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
584
585
586
587
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
588
589
}

Gabriel Moreau's avatar
Gabriel Moreau committed
590
// ---------------------------------------------------------------------
591

592
593
594
595
596
597
598
599
600
601
602
603
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;
}

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

604
function get_team_listshort($pdo) {
605
	$sql = 'SELECT id, nom FROM equipe ORDER BY nom;';
606
	$stmt = $pdo->prepare($sql);
607
	$stmt->execute();
608
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
609
	return $team_fetch;
610
611
}

Gabriel Moreau's avatar
Gabriel Moreau committed
612
// ---------------------------------------------------------------------
613

614
function get_team_listall($pdo) {
615
	$sql = 'SELECT * FROM equipe ORDER BY nom;';
616
	$stmt = $pdo->prepare($sql);
617
	$stmt->execute();
618
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
619
	return $team_fetch;
620
621
}

Gabriel Moreau's avatar
Gabriel Moreau committed
622
// ---------------------------------------------------------------------
623

624
function get_team_with_appareil($pdo) {
625
	$sql = 'SELECT DISTINCT equipe.id, equipe.nom FROM equipe INNER JOIN Listing ON equipe.id = Listing.equipe ORDER BY equipe.nom;';
626
	$stmt = $pdo->prepare($sql);
627
	$stmt->execute();
628
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
629
	return $team_fetch;
630
631
}

Gabriel Moreau's avatar
Gabriel Moreau committed
632
633
// ---------------------------------------------------------------------

634
635
636
637
638
639
640
641
642
643
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
644
645
646
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
647
	$iostat = $stmt->execute(array($name, $description, $account, $manager));
Gabriel Moreau's avatar
Gabriel Moreau committed
648
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
649
	if (!$iostat)
Gabriel Moreau's avatar
Gabriel Moreau committed
650
651
		$err_msg = $stmt->errorInfo()[2];
	return array($pdo->lastInsertId(), $err_msg);
Gabriel Moreau's avatar
Gabriel Moreau committed
652
653
}

Gabriel Moreau's avatar
Gabriel Moreau committed
654
655
656
657
658
// ---------------------------------------------------------------------

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
659
	$iostat = $stmt->execute(array($name, $description, $account, $manager, $id_team));
Gabriel Moreau's avatar
Gabriel Moreau committed
660
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
661
	if (!$iostat)
Gabriel Moreau's avatar
Gabriel Moreau committed
662
663
664
665
		$err_msg = $stmt->errorInfo()[2];
	return $err_msg;
}

666
667
668
669
670
// ---------------------------------------------------------------------

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
671
672
	$iostat = $stmt->execute(array($id));
	return $iostat;
673
674
}

675
676
// ---------------------------------------------------------------------
// User
Gabriel Moreau's avatar
Gabriel Moreau committed
677
// ---------------------------------------------------------------------
678
679

function get_user_by_id($pdo, $id) {
Gabriel Moreau's avatar
Gabriel Moreau committed
680
	$sql = 'SELECT id, nom, prenom FROM users WHERE id = ?;';
681
682
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
683
684
685
686
687
688
	$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
689
// ---------------------------------------------------------------------
690
691
692
693
694
695
696
697
698

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

Gabriel Moreau's avatar
Gabriel Moreau committed
701
// ---------------------------------------------------------------------
702

703
704
705
706
707
708
709
710
711
712
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
713
// ---------------------------------------------------------------------
714

715
716
function get_user_listall_by_logged_level($pdo, $logged_level) {
	if ($logged_level > 3)       // lorsqu'on est haut place, on voit tout le monde
717
		$sql = 'SELECT * FROM users ORDER BY nom, prenom;';
718
	else if ($logged_level == 3) // losrqu'on est de niveau 3, on voit tout le monde sauf les users de plus haut level
719
		$sql = 'SELECT * FROM users WHERE level < 4 ORDER BY nom, prenom;';
720
	else                         // lorsqu'on est < 3, on voit tout le monde sauf le suser de level > 3 et les users non valides
721
		$sql = 'SELECT * FROM users WHERE valid = 1 and level < 3 ORDER BY nom, prenom;';
722
723
724
725
726
727
	$stmt = $pdo->prepare($sql);
    $stmt->execute();
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

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

730
731
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;';
732
	$stmt = $pdo->prepare($sql);
733
    $stmt->execute(array($level_min, $id_bonus));
734
735
736
737
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
// ---------------------------------------------------------------------

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
753
	$iostat = $stmt->execute(array($familyname, $firstname, $login, $password, $email, $level, $tel, $team_id, $theme));
754
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
755
	if (!$iostat)
756
757
758
759
		$err_msg = $stmt->errorInfo()[2];
	return array($pdo->lastInsertId(), $err_msg);
}

760
761
// ---------------------------------------------------------------------

762
763
764
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
765
766
	$iostat = $stmt->execute(array($user_password, $user_id));
	return $iostat;
767
768
769
770
}

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

771
772
773
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
774
775
	$iostat = $stmt->execute(array($user_status, $user_id));
	return $iostat;
776
777
}

Gabriel Moreau's avatar
Gabriel Moreau committed
778
779
780
// ---------------------------------------------------------------------

function set_user_update($pdo, $user_id, $familyname, $firstname, $email, $level, $tel, $team_id, $theme) {
Gabriel Moreau's avatar
Gabriel Moreau committed
781
	$sql = 'UPDATE LOW_PRIORITY users SET nom = ?, prenom = ?, email = ?, level = ?, tel = ?, equipe = ?, theme = ? WHERE id = ?;';
Gabriel Moreau's avatar
Gabriel Moreau committed
782
	$stmt = $pdo->prepare($sql);
Estéban Ristich's avatar
Estéban Ristich committed
783
	$iostat = $stmt->execute(array($familyname, $firstname, $email, $level, $tel, $team_id, $theme, $user_id));
Gabriel Moreau's avatar
Gabriel Moreau committed
784
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
785
	if (!$iostat)
Gabriel Moreau's avatar
Gabriel Moreau committed
786
787
788
789
		$err_msg = $stmt->errorInfo()[2];
	return $err_msg;
}

790
791
// ---------------------------------------------------------------------
// Version
792
793
// ---------------------------------------------------------------------

794
795
796
797
function get_version_by_name($pdo, $name) {
	$sql = 'SELECT version FROM version WHERE name = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($name));
798
	$version_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
799
800
801
802
	if (count($version_fetch) > 0)
		return $version_fetch[0];
	return false;
}
Gabriel Moreau's avatar
Gabriel Moreau committed
803
// ---------------------------------------------------------------------
804
805
806
807
808
809
810
811
812

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

813
?>