db-functions.php 9.42 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;
}

Gabriel Moreau's avatar
Gabriel Moreau committed
68
// ---------------------------------------------------------------------
69

70
71
72
73
74
75
76
77
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
78
// ---------------------------------------------------------------------
79

80
81
82
83
84
85
86
87
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'];
}

Gabriel Moreau's avatar
Gabriel Moreau committed
88
// ---------------------------------------------------------------------
89

90
function get_equipment_by_id($pdo, $id) {
91
92
93
	$sql = 'SELECT id, nom FROM Listing WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
94
95
96
97
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (count($result_fetch) > 0)
		return $result_fetch[0];
	return false;
98
99
}

Gabriel Moreau's avatar
Gabriel Moreau committed
100
// ---------------------------------------------------------------------
101

102
function get_equipment_all_by_id($pdo, $id) {
103
104
105
	$sql = 'SELECT * FROM Listing WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
106
107
108
109
110
111
	$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
112
// ---------------------------------------------------------------------
113
114

function get_equipment_listshort($pdo) {
115
	$sql = 'SELECT id, nom FROM Listing ORDER BY nom;';
116
117
	$stmt = $pdo->prepare($sql);
	$stmt->execute();
118
	$result_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
119
	return $result_fetch;
120
121
}

Gabriel Moreau's avatar
Gabriel Moreau committed
122
// ---------------------------------------------------------------------
123

124
function get_category_by_id($pdo, $id) {
125
126
127
	$sql = 'SELECT id, nom FROM categorie WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
128
129
	$category_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
	return $category_fetch[0];
130
131
}

Gabriel Moreau's avatar
Gabriel Moreau committed
132
// ---------------------------------------------------------------------
133

Gabriel Moreau's avatar
Gabriel Moreau committed
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
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;
}

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

159
160
161
162
163
164
165
166
167
168
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
169
170
171
172
173
174
175
176
177
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();
}

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

178
179
function get_supplier_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM fournisseurs WHERE id = ?;';
180
181
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
182
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
183
	return $supplier_fetch[0];
184
185
}

Gabriel Moreau's avatar
Gabriel Moreau committed
186
// ---------------------------------------------------------------------
187

188
function get_supplier_listshort($pdo) {
189
	$sql = 'SELECT id, nom FROM fournisseurs ORDER BY nom;';
190
	$stmt = $pdo->prepare($sql);
191
	$stmt->execute();
192
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
193
	return $supplier_fetch;
194
195
}

Gabriel Moreau's avatar
Gabriel Moreau committed
196
// ---------------------------------------------------------------------
197

198
function get_supplier_listall($pdo) {
199
	$sql = 'SELECT * FROM fournisseurs ORDER BY nom;';
200
	$stmt = $pdo->prepare($sql);
201
	$stmt->execute();
202
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
203
	return $supplier_fetch;
204
205
}

Gabriel Moreau's avatar
Gabriel Moreau committed
206
// ---------------------------------------------------------------------
207

208
209
210
211
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 ?;';
212
	$stmt = $pdo->prepare($sql);
213
	$stmt->execute(array($find, $find));
214
	$supplier_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
215
	return $supplier_fetch;
216
217
}

Gabriel Moreau's avatar
Gabriel Moreau committed
218
// ---------------------------------------------------------------------
219

220
221
function get_team_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM equipe WHERE id = ?;';
222
223
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
224
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
225
	return $team_fetch[0];
226
227
}

Gabriel Moreau's avatar
Gabriel Moreau committed
228
// ---------------------------------------------------------------------
229

230
function get_team_listshort($pdo) {
231
	$sql = 'SELECT id, nom FROM equipe ORDER BY nom;';
232
	$stmt = $pdo->prepare($sql);
233
	$stmt->execute();
234
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
235
	return $team_fetch;
236
237
}

Gabriel Moreau's avatar
Gabriel Moreau committed
238
// ---------------------------------------------------------------------
239

240
function get_team_listall($pdo) {
241
	$sql = 'SELECT * FROM equipe ORDER BY nom;';
242
	$stmt = $pdo->prepare($sql);
243
	$stmt->execute();
244
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
245
	return $team_fetch;
246
247
}

Gabriel Moreau's avatar
Gabriel Moreau committed
248
// ---------------------------------------------------------------------
249

250
251
function get_team_with_appareil($pdo) {
	$sql = 'SELECT id, nom FROM equipe INNER JOIN Listing ON equipe.id = Listing.equipe;';
252
	$stmt = $pdo->prepare($sql);
253
	$stmt->execute();
254
	$team_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
255
	return $team_fetch;
256
257
}

Gabriel Moreau's avatar
Gabriel Moreau committed
258
// ---------------------------------------------------------------------
259
260
261
262
263

function get_user_by_id($pdo, $id) {
	$sql = 'SELECT id, nom FROM users WHERE id = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($id));
264
265
266
267
268
269
	$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
270
// ---------------------------------------------------------------------
271
272
273
274
275
276
277
278
279

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

Gabriel Moreau's avatar
Gabriel Moreau committed
282
// ---------------------------------------------------------------------
283

284
285
286
287
288
289
290
291
292
293
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
294
// ---------------------------------------------------------------------
295

296
297
298
299
function get_version_by_name($pdo, $name) {
	$sql = 'SELECT version FROM version WHERE name = ?;';
	$stmt = $pdo->prepare($sql);
	$stmt->execute(array($name));
300
	$version_fetch = $stmt->fetchAll(PDO::FETCH_ASSOC);
301
302
303
304
	if (count($version_fetch) > 0)
		return $version_fetch[0];
	return false;
}
Gabriel Moreau's avatar
Gabriel Moreau committed
305
// ---------------------------------------------------------------------
306
307
308
309
310
311
312
313
314

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

315
?>