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

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

382
383
384
385
386
387
388
389
390
391
function get_all_reservations_equipment($pdo, $id_equipment) {
	$sql = 'SELECT * FROM pret WHERE nom = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id_equipment));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch;
	return false;
}

392
393
394
// ---------------------------------------------------------------------

function get_loan_short_by_id_equipment($pdo, $id_equipment) {
Gabriel Moreau's avatar
Gabriel Moreau committed
395
	// recupere l'appareil via l'id qui est mis dans un champs texte (nom) !
396
	$sql = 'SELECT id FROM pret WHERE nom = ?;';
Gabriel Moreau's avatar
Gabriel Moreau committed
397
398
399
400
401
402
403
404
405
406
	$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;
}

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

407
408
409
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
410
	$stmt = $pdo->prepare($sql);
411
	$stmt->execute(array($id_equipment));
Gabriel Moreau's avatar
Gabriel Moreau committed
412
413
414
415
416
417
418
419
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
}

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

420
function get_loan_listall($pdo) {
421
	//$sql = 'SELECT * FROM pret;';
422
	$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;';
423
424
425
426
427
	$stmt = $pdo->prepare($sql);
	$stmt->execute();
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}
Gabriel Moreau's avatar
Gabriel Moreau committed
428
429
430
431
432
433
434
435
436
// ---------------------------------------------------------------------

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;
}
437
438
439

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

440
441
// ---------------------------------------------------------------------

442
443
444
445
446
447
448
449
450
451
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'];
}

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

452
453
454
455
456
457
458
459
460
461
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;
}

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

462
463
464
465
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));
Estéban Ristich's avatar
Fix bug    
Estéban Ristich committed
466
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
467
468
469
470
471
	return $result_fetch;
}

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

Gabriel Moreau's avatar
Gabriel Moreau committed
472
473
474
475
476
477
478
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
479
480
481
482
483
484
485
486
// ---------------------------------------------------------------------

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
487
488
// ---------------------------------------------------------------------

489
function del_loan_by_id($pdo, $id) {
Gabriel Moreau's avatar
Gabriel Moreau committed
490
491
	$sql = 'DELETE LOW_PRIORITY FROM pret WHERE id = ? LIMIT 1;';
	$stmt = $pdo->prepare($sql);
Gabriel Moreau's avatar
Gabriel Moreau committed
492
493
	$iostat = $stmt->execute(array($id));
	return $iostat;
Gabriel Moreau's avatar
Gabriel Moreau committed
494
495
}

496

497
498
// ---------------------------------------------------------------------
// Supplier
Gabriel Moreau's avatar
Gabriel Moreau committed
499
500
// ---------------------------------------------------------------------

501
502
function get_supplier_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM fournisseurs WHERE id = ?;';
503
504
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
505
506
507
508
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
509
510
}

Gabriel Moreau's avatar
Gabriel Moreau committed
511
// ---------------------------------------------------------------------
512

513
514
515
516
517
518
519
520
521
522
523
524
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;
}

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

525
function get_supplier_listshort($pdo) {
526
	$sql = 'SELECT id, nom FROM fournisseurs ORDER BY nom;';
527
	$stmt = $pdo->prepare($sql);
528
	$stmt->execute();
529
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
530
	return $supplier_fetch;
531
532
}

Gabriel Moreau's avatar
Gabriel Moreau committed
533
// ---------------------------------------------------------------------
534

535
function get_supplier_listall($pdo) {
536
	$sql = 'SELECT * FROM fournisseurs ORDER BY nom;';
537
	$stmt = $pdo->prepare($sql);
538
	$stmt->execute();
539
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
540
	return $supplier_fetch;
541
542
}

Gabriel Moreau's avatar
Gabriel Moreau committed
543
// ---------------------------------------------------------------------
544

545
546
547
548
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 ?;';
549
	$stmt = $pdo->prepare($sql);
550
	$stmt->execute(array($find, $find));
551
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
552
	return $supplier_fetch;
553
554
}

555
556
// ---------------------------------------------------------------------

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

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

569
570
571
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
572
	$iostat = $stmt->execute(array($name, $address, $tel, $fax, $email, $www, $contact, $description, $id_supplier));
573
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
574
	if (!$iostat)
575
576
577
578
		$err_msg = $stmt->errorInfo()[2];
	return $err_msg;
}

579
// ---------------------------------------------------------------------
Gabriel Moreau's avatar
Gabriel Moreau committed
580
581
582
583

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
584
585
	$iostat = $stmt->execute(array($id));
	return $iostat;
Gabriel Moreau's avatar
Gabriel Moreau committed
586
587
588
}

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

592
593
function get_team_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM equipe WHERE id = ?;';
594
595
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
596
597
598
599
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
600
601
}

Gabriel Moreau's avatar
Gabriel Moreau committed
602
// ---------------------------------------------------------------------
603

604
605
606
607
608
609
610
611
612
613
614
615
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;
}

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

616
function get_team_listshort($pdo) {
617
	$sql = 'SELECT id, nom FROM equipe ORDER BY nom;';
618
	$stmt = $pdo->prepare($sql);
619
	$stmt->execute();
620
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
621
	return $team_fetch;
622
623
}

Gabriel Moreau's avatar
Gabriel Moreau committed
624
// ---------------------------------------------------------------------
625

626
function get_team_listall($pdo) {
627
	$sql = 'SELECT * FROM equipe ORDER BY nom;';
628
	$stmt = $pdo->prepare($sql);
629
	$stmt->execute();
630
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
631
	return $team_fetch;
632
633
}

Gabriel Moreau's avatar
Gabriel Moreau committed
634
// ---------------------------------------------------------------------
635

636
function get_team_with_appareil($pdo) {
637
	$sql = 'SELECT DISTINCT equipe.id, equipe.nom FROM equipe INNER JOIN Listing ON equipe.id = Listing.equipe ORDER BY equipe.nom;';
638
	$stmt = $pdo->prepare($sql);
639
	$stmt->execute();
640
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
641
	return $team_fetch;
642
643
}

Gabriel Moreau's avatar
Gabriel Moreau committed
644
645
// ---------------------------------------------------------------------

646
647
648
649
650
651
652
653
654
655
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
656
657
658
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
659
	$iostat = $stmt->execute(array($name, $description, $account, $manager));
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
		$err_msg = $stmt->errorInfo()[2];
	return array($pdo->lastInsertId(), $err_msg);
Gabriel Moreau's avatar
Gabriel Moreau committed
664
665
}

Gabriel Moreau's avatar
Gabriel Moreau committed
666
667
668
669
670
// ---------------------------------------------------------------------

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
671
	$iostat = $stmt->execute(array($name, $description, $account, $manager, $id_team));
Gabriel Moreau's avatar
Gabriel Moreau committed
672
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
673
	if (!$iostat)
Gabriel Moreau's avatar
Gabriel Moreau committed
674
675
676
677
		$err_msg = $stmt->errorInfo()[2];
	return $err_msg;
}

678
679
680
681
682
// ---------------------------------------------------------------------

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
683
684
	$iostat = $stmt->execute(array($id));
	return $iostat;
685
686
}

687
688
// ---------------------------------------------------------------------
// User
Gabriel Moreau's avatar
Gabriel Moreau committed
689
// ---------------------------------------------------------------------
690
691

function get_user_by_id($pdo, $id) {
Gabriel Moreau's avatar
Gabriel Moreau committed
692
	$sql = 'SELECT id, nom, prenom FROM users WHERE id = ?;';
693
694
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
695
696
697
698
699
700
	$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
701
// ---------------------------------------------------------------------
702
703
704
705
706
707
708
709
710

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

Gabriel Moreau's avatar
Gabriel Moreau committed
713
// ---------------------------------------------------------------------
714

715
716
717
718
719
720
721
722
723
724
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
725
// ---------------------------------------------------------------------
726

727
728
function get_user_listall_by_logged_level($pdo, $logged_level) {
	if ($logged_level > 3)       // lorsqu'on est haut place, on voit tout le monde
729
		$sql = 'SELECT * FROM users ORDER BY nom, prenom;';
730
	else if ($logged_level == 3) // losrqu'on est de niveau 3, on voit tout le monde sauf les users de plus haut level
731
		$sql = 'SELECT * FROM users WHERE level < 4 ORDER BY nom, prenom;';
732
	else                         // lorsqu'on est < 3, on voit tout le monde sauf le suser de level > 3 et les users non valides
733
		$sql = 'SELECT * FROM users WHERE valid = 1 and level < 3 ORDER BY nom, prenom;';
734
735
736
737
738
739
	$stmt = $pdo->prepare($sql);
    $stmt->execute();
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

740
741
// ---------------------------------------------------------------------

742
743
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;';
744
	$stmt = $pdo->prepare($sql);
745
    $stmt->execute(array($level_min, $id_bonus));
746
747
748
749
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
// ---------------------------------------------------------------------

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
765
	$iostat = $stmt->execute(array($familyname, $firstname, $login, $password, $email, $level, $tel, $team_id, $theme));
766
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
767
	if (!$iostat)
768
769
770
771
		$err_msg = $stmt->errorInfo()[2];
	return array($pdo->lastInsertId(), $err_msg);
}

772
773
// ---------------------------------------------------------------------

774
775
776
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
777
778
	$iostat = $stmt->execute(array($user_password, $user_id));
	return $iostat;
779
780
781
782
}

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

783
784
785
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
786
787
	$iostat = $stmt->execute(array($user_status, $user_id));
	return $iostat;
788
789
}

Gabriel Moreau's avatar
Gabriel Moreau committed
790
791
792
// ---------------------------------------------------------------------

function set_user_update($pdo, $user_id, $familyname, $firstname, $email, $level, $tel, $team_id, $theme) {
Gabriel Moreau's avatar
Gabriel Moreau committed
793
	$sql = 'UPDATE LOW_PRIORITY users SET nom = ?, prenom = ?, email = ?, level = ?, tel = ?, equipe = ?, theme = ? WHERE id = ?;';
Gabriel Moreau's avatar
Gabriel Moreau committed
794
	$stmt = $pdo->prepare($sql);
Estéban Ristich's avatar
Estéban Ristich committed
795
	$iostat = $stmt->execute(array($familyname, $firstname, $email, $level, $tel, $team_id, $theme, $user_id));
Gabriel Moreau's avatar
Gabriel Moreau committed
796
	$err_msg = '';
Gabriel Moreau's avatar
Gabriel Moreau committed
797
	if (!$iostat)
Gabriel Moreau's avatar
Gabriel Moreau committed
798
799
800
801
		$err_msg = $stmt->errorInfo()[2];
	return $err_msg;
}

802
803
// ---------------------------------------------------------------------
// Version
804
805
// ---------------------------------------------------------------------

806
807
808
809
function get_version_by_name($pdo, $name) {
	$sql = 'SELECT version FROM version WHERE name = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($name));
810
	$version_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
811
812
813
814
	if (count($version_fetch) > 0)
		return $version_fetch[0];
	return false;
}
Gabriel Moreau's avatar
Gabriel Moreau committed
815
// ---------------------------------------------------------------------
816
817
818
819
820
821
822
823
824

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

825
?>