db-functions.php 16.7 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
	$datasheet_filename_no_ext = pathinfo($datasheet_filename_upload, PATHINFO_FILENAME);
Gabriel Moreau's avatar
Gabriel Moreau committed
107
	$datasheet_filename_kebab = string_to_filename_kebab($datasheet_filename_no_ext).'.pdf';
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
	$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
117
	$stmt2->execute(array($sub_path.'/'.$datasheet_filename_kebab, $id_datasheet));
118
119
120
121

	$new_dir = $new_datasheet_path.'/'.$sub_path;
	if (!is_dir($new_dir))
		mkdir($new_dir, 0755);
Gabriel Moreau's avatar
Gabriel Moreau committed
122
	move_uploaded_file($tmp_file, $new_dir.'/'.$datasheet_filename_kebab);
123
124
125
126

	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
250
251
// ---------------------------------------------------------------------

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

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

Gabriel Moreau's avatar
Gabriel Moreau committed
252
253
254
255
256
257
258
259
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;
}

260
261
// ---------------------------------------------------------------------
// Loan
262
263
// ---------------------------------------------------------------------

Gabriel Moreau's avatar
Gabriel Moreau committed
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
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;
}

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

289
290
291
292
293
294
295
296
297
298
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
299
300
301
302
303
304
305
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
306
307
308
309
310
311
312
313
// ---------------------------------------------------------------------

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

314
315
// ---------------------------------------------------------------------
// Supplier
Gabriel Moreau's avatar
Gabriel Moreau committed
316
317
// ---------------------------------------------------------------------

318
319
function get_supplier_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM fournisseurs WHERE id = ?;';
320
321
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
322
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
323
	return $supplier_fetch[0];
324
325
}

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

328
function get_supplier_listshort($pdo) {
329
	$sql = 'SELECT id, nom 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
function get_supplier_listall($pdo) {
339
	$sql = 'SELECT * FROM fournisseurs ORDER BY nom;';
340
	$stmt = $pdo->prepare($sql);
341
	$stmt->execute();
342
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
343
	return $supplier_fetch;
344
345
}

Gabriel Moreau's avatar
Gabriel Moreau committed
346
// ---------------------------------------------------------------------
347

348
349
350
351
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 ?;';
352
	$stmt = $pdo->prepare($sql);
353
	$stmt->execute(array($find, $find));
354
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
355
	return $supplier_fetch;
356
357
}

358
359
// ---------------------------------------------------------------------
// Team
Gabriel Moreau's avatar
Gabriel Moreau committed
360
// ---------------------------------------------------------------------
361

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

Gabriel Moreau's avatar
Gabriel Moreau committed
372
// ---------------------------------------------------------------------
373

374
375
376
377
378
379
380
381
382
383
384
385
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;
}

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

386
function get_team_listshort($pdo) {
387
	$sql = 'SELECT id, nom 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_listall($pdo) {
397
	$sql = 'SELECT * FROM equipe ORDER BY nom;';
398
	$stmt = $pdo->prepare($sql);
399
	$stmt->execute();
400
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
401
	return $team_fetch;
402
403
}

Gabriel Moreau's avatar
Gabriel Moreau committed
404
// ---------------------------------------------------------------------
405

406
function get_team_with_appareil($pdo) {
407
	$sql = 'SELECT DISTINCT equipe.id, equipe.nom FROM equipe INNER JOIN Listing ON equipe.id = Listing.equipe ORDER BY equipe.nom;';
408
	$stmt = $pdo->prepare($sql);
409
	$stmt->execute();
410
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
411
	return $team_fetch;
412
413
}

414
415
// ---------------------------------------------------------------------
// User
Gabriel Moreau's avatar
Gabriel Moreau committed
416
// ---------------------------------------------------------------------
417
418
419
420
421

function get_user_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM users WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
422
423
424
425
426
427
	$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
428
// ---------------------------------------------------------------------
429
430
431
432
433
434
435
436
437

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

Gabriel Moreau's avatar
Gabriel Moreau committed
440
// ---------------------------------------------------------------------
441

442
443
444
445
446
447
448
449
450
451
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
452
// ---------------------------------------------------------------------
453

454
455
456
457
458
459
460
461
462
463
464
465
466
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;
}

467
468
469
470
471
// ---------------------------------------------------------------------

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
472
    $stmt->execute(array($level_min));
473
474
475
476
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $result_fetch;
}

477
478
// ---------------------------------------------------------------------
// Version
479
480
// ---------------------------------------------------------------------

481
482
483
484
function get_version_by_name($pdo, $name) {
	$sql = 'SELECT version FROM version WHERE name = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($name));
485
	$version_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
486
487
488
489
	if (count($version_fetch) > 0)
		return $version_fetch[0];
	return false;
}
Gabriel Moreau's avatar
Gabriel Moreau committed
490
// ---------------------------------------------------------------------
491
492
493
494
495
496
497
498
499

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

500
?>