db-functions.php 16.4 KB
Newer Older
1
2
3
4
<?php

require_once('connect.php');

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

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

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
21
// ---------------------------------------------------------------------
22
23
24
25
26
27
28

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
29
// ---------------------------------------------------------------------
30
31
32
33
34

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

Gabriel Moreau's avatar
Gabriel Moreau committed
35
// ---------------------------------------------------------------------
36
37
38
39
40
41
42
43

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
44
// ---------------------------------------------------------------------
45
46
47
48
49

function last_id_db() {
	return mysql_insert_id();
}

Gabriel Moreau's avatar
Gabriel Moreau committed
50
// ---------------------------------------------------------------------
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

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

68
69
// ---------------------------------------------------------------------
// Datasheet
Gabriel Moreau's avatar
Gabriel Moreau committed
70
// ---------------------------------------------------------------------
71

Gabriel Moreau's avatar
Gabriel Moreau committed
72
function get_datasheet_basepath() {
73
74
75
76
77
	return './data/datasheet';
}

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

78
79
80
81
82
83
84
85
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
86
// ---------------------------------------------------------------------
87

88
89
90
91
92
93
94
95
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'];
}

96
97
// ---------------------------------------------------------------------

Gabriel Moreau's avatar
Gabriel Moreau committed
98
99
function set_datasheet_new($pdo, $equipment_id, $datasheet_filename_upload, $tmp_file) {
	if (!preg_match('/\.pdf$/i', $datasheet_filename_upload))
100
101
102
103
104
105
		return false;

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

Gabriel Moreau's avatar
Gabriel Moreau committed
106
107
	$datasheet_filename_kebab = string_to_filename_kebab($datasheet_filename_upload);
	$datasheet_filename_no_ext = pathinfo($datasheet_filename_upload, PATHINFO_FILENAME);
108
109
110

	$sql1 = 'INSERT INTO datasheet (description, id_equipment) VALUES (?, ?);';
	$stmt1 = $pdo->prepare($sql1);
Gabriel Moreau's avatar
Gabriel Moreau committed
111
	$stmt1->execute(array($datasheet_filename_no_ext, $equipment_id));
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
	$id_datasheet = $pdo->lastInsertId();

	$sub_path = $id_datasheet.'-'.random_string(8);
	$sql2 = 'UPDATE datasheet SET pathname = ? WHERE id = ?;';
	$stmt2 = $pdo->prepare($sql2);
	$stmt2->execute(array($sub_path.'/'.$datasheet_filename_kebab.'.pdf', $id_datasheet));

	$new_dir = $new_datasheet_path.'/'.$sub_path;
	if (!is_dir($new_dir))
		mkdir($new_dir, 0755);
	move_uploaded_file($tmp_file, $new_dir.'/'.$datasheet_filename_kebab.'.pdf');

	return $id_datasheet;
}

127
128
// ---------------------------------------------------------------------
// Equipment
Gabriel Moreau's avatar
Gabriel Moreau committed
129
// ---------------------------------------------------------------------
130

131
function get_equipment_by_id($pdo, $id) {
132
133
134
	$sql = 'SELECT id, nom FROM Listing WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
135
136
137
138
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
139
140
}

Gabriel Moreau's avatar
Gabriel Moreau committed
141
// ---------------------------------------------------------------------
142

143
function get_equipment_all_by_id($pdo, $id) {
144
145
146
	$sql = 'SELECT * FROM Listing WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
147
148
149
150
151
152
	$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
153
// ---------------------------------------------------------------------
154
155

function get_equipment_listshort($pdo) {
156
	$sql = 'SELECT id, nom FROM Listing ORDER BY nom;';
157
158
	$stmt = $pdo->prepare($sql);
	$stmt->execute();
159
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
160
	return $result_fetch;
161
162
}

Gabriel Moreau's avatar
Gabriel Moreau committed
163
// ---------------------------------------------------------------------
164

Gabriel Moreau's avatar
Gabriel Moreau committed
165
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
166
	$sql = 'INSERT INTO Listing (categorie, nom, modele, gamme, equipe, fournisseur, achat, responsable, reparation, accessoires, inventaire, notice, barcode, loanable)';
167
	$sql .=            ' VALUES (?,         ?,   ?,      ?,     ?,      ?,           ?,     ?,           ?,          ?,           ?,          ?,      ?,       ?);';
168
	$stmt = $pdo->prepare($sql);
Gabriel Moreau's avatar
Gabriel Moreau committed
169
	$status = $stmt->execute(array($categorie, $nom, $modele, $feature, $equipe, $fourn, $achat, $tech, $reparation, $accessoires, $inventaire, $notice, $barcode, $loanable));
170
171
	$err_msg = '';
	if (!$status)
Gabriel Moreau's avatar
Gabriel Moreau committed
172
		$err_msg = $stmt->errorInfo()[2];
173
	return array($pdo->lastInsertId(), $err_msg);
174
175
176
177
}

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

178
179
180
181
182
183
184
185
186
187
188
189
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;
}

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

190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
function del_equipment($pdo, $id) {
	$sql = 'DELETE LOW_PRIORITY FROM Listing WHERE id = ? LIMIT 1;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
	$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (!$result)
		return false;
	else
		return true;
}

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

205
function get_category_by_id($pdo, $id) {
206
207
208
	$sql = 'SELECT id, nom FROM categorie WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
209
210
	$category_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $category_fetch[0];
211
212
}

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

Gabriel Moreau's avatar
Gabriel Moreau committed
215
216
217
218
219
220
221
222
223
224
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;
}

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

225
226
227
228
229
230
231
232
233
234
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;
}

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

235
236
237
238
239
240
241
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
242
243
244
245
246
247
248
249
function del_category_by_id($pdo, $id) {
	$sql = 'DELETE LOW_PRIORITY FROM categorie WHERE id = ? LIMIT 1';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch ? true : false;
}

250
251
// ---------------------------------------------------------------------
// Loan
252
253
// ---------------------------------------------------------------------

Gabriel Moreau's avatar
Gabriel Moreau committed
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
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 = ?;';
	$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;
}

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

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

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

279
280
281
282
283
284
285
286
287
288
function get_loan_listall($pdo) {
	$sql = 'SELECT * FROM pret;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute();
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

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

Gabriel Moreau's avatar
Gabriel Moreau committed
289
290
291
292
293
294
295
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
296
297
298
299
300
301
302
303
// ---------------------------------------------------------------------

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

304
305
// ---------------------------------------------------------------------
// Supplier
Gabriel Moreau's avatar
Gabriel Moreau committed
306
307
// ---------------------------------------------------------------------

308
309
function get_supplier_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM fournisseurs WHERE id = ?;';
310
311
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
312
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
313
	return $supplier_fetch[0];
314
315
}

Gabriel Moreau's avatar
Gabriel Moreau committed
316
// ---------------------------------------------------------------------
317

318
function get_supplier_listshort($pdo) {
319
	$sql = 'SELECT id, nom FROM fournisseurs ORDER BY nom;';
320
	$stmt = $pdo->prepare($sql);
321
	$stmt->execute();
322
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
323
	return $supplier_fetch;
324
325
}

Gabriel Moreau's avatar
Gabriel Moreau committed
326
// ---------------------------------------------------------------------
327

328
function get_supplier_listall($pdo) {
329
	$sql = 'SELECT * FROM fournisseurs ORDER BY nom;';
330
	$stmt = $pdo->prepare($sql);
331
	$stmt->execute();
332
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
333
	return $supplier_fetch;
334
335
}

Gabriel Moreau's avatar
Gabriel Moreau committed
336
// ---------------------------------------------------------------------
337

338
339
340
341
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 ?;';
342
	$stmt = $pdo->prepare($sql);
343
	$stmt->execute(array($find, $find));
344
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
345
	return $supplier_fetch;
346
347
}

348
349
// ---------------------------------------------------------------------
// Team
Gabriel Moreau's avatar
Gabriel Moreau committed
350
// ---------------------------------------------------------------------
351

352
353
function get_team_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM equipe WHERE id = ?;';
354
355
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
356
357
358
359
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
360
361
}

Gabriel Moreau's avatar
Gabriel Moreau committed
362
// ---------------------------------------------------------------------
363

364
365
366
367
368
369
370
371
372
373
374
375
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;
}

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

376
function get_team_listshort($pdo) {
377
	$sql = 'SELECT id, nom FROM equipe ORDER BY nom;';
378
	$stmt = $pdo->prepare($sql);
379
	$stmt->execute();
380
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
381
	return $team_fetch;
382
383
}

Gabriel Moreau's avatar
Gabriel Moreau committed
384
// ---------------------------------------------------------------------
385

386
function get_team_listall($pdo) {
387
	$sql = 'SELECT * FROM equipe ORDER BY nom;';
388
	$stmt = $pdo->prepare($sql);
389
	$stmt->execute();
390
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
391
	return $team_fetch;
392
393
}

Gabriel Moreau's avatar
Gabriel Moreau committed
394
// ---------------------------------------------------------------------
395

396
function get_team_with_appareil($pdo) {
397
	$sql = 'SELECT DISTINCT equipe.id, equipe.nom FROM equipe INNER JOIN Listing ON equipe.id = Listing.equipe ORDER BY equipe.nom;';
398
	$stmt = $pdo->prepare($sql);
399
	$stmt->execute();
400
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
401
	return $team_fetch;
402
403
}

404
405
// ---------------------------------------------------------------------
// User
Gabriel Moreau's avatar
Gabriel Moreau committed
406
// ---------------------------------------------------------------------
407
408
409
410
411

function get_user_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM users WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
412
413
414
415
416
417
	$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
418
// ---------------------------------------------------------------------
419
420
421
422
423
424
425
426
427

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

Gabriel Moreau's avatar
Gabriel Moreau committed
430
// ---------------------------------------------------------------------
431

432
433
434
435
436
437
438
439
440
441
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
442
// ---------------------------------------------------------------------
443

444
445
446
447
448
449
450
451
452
453
454
455
456
function get_user_listall_by_logged_level($pdo, $logged_level) {
	if ($logged_level > 3)       // lorsqu'on est haut place, on voit tout le monde
		$sql = 'SELECT * FROM users;';
	else if ($logged_level == 3) // losrqu'on est de niveau 3, on voit tout le monde sauf les users de plus haut level
		$sql = 'SELECT * FROM users WHERE level < 4;';
	else                         // lorsqu'on est < 3, on voit tout le monde sauf le suser de level > 3 et les users non valides
		$sql = 'SELECT * FROM users WHERE valid = 1 and level < 3;';
	$stmt = $pdo->prepare($sql);
    $stmt->execute();
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

457
458
459
460
461
// ---------------------------------------------------------------------

function get_user_listshort_with_right($pdo, $level_min=1) {
	$sql = 'SELECT id, nom FROM users WHERE valid = 1 and level >= ?;';
	$stmt = $pdo->prepare($sql);
Gabriel Moreau's avatar
Gabriel Moreau committed
462
    $stmt->execute(array($level_min));
463
464
465
466
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

467
468
// ---------------------------------------------------------------------
// Version
469
470
// ---------------------------------------------------------------------

471
472
473
474
function get_version_by_name($pdo, $name) {
	$sql = 'SELECT version FROM version WHERE name = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($name));
475
	$version_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
476
477
478
479
	if (count($version_fetch) > 0)
		return $version_fetch[0];
	return false;
}
Gabriel Moreau's avatar
Gabriel Moreau committed
480
// ---------------------------------------------------------------------
481
482
483
484
485
486
487
488
489

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

490
?>