1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace ByJG\Authenticate; |
4
|
|
|
|
5
|
|
|
use ByJG\AnyDataset\Database\SQLHelper; |
6
|
|
|
use ByJG\AnyDataset\Repository\AnyDataset; |
7
|
|
|
use ByJG\AnyDataset\Repository\DBDataset; |
8
|
|
|
use ByJG\AnyDataset\Repository\IteratorFilter; |
9
|
|
|
use ByJG\AnyDataset\Repository\IteratorInterface; |
10
|
|
|
use ByJG\AnyDataset\Repository\SingleRow; |
11
|
|
|
use ByJG\Authenticate\Exception\UserExistsException; |
12
|
|
|
|
13
|
|
|
class UsersDBDataset extends UsersBase |
14
|
|
|
{ |
15
|
|
|
|
16
|
|
|
/** |
17
|
|
|
* @var DBDataset |
18
|
|
|
*/ |
19
|
|
|
protected $_db; |
20
|
|
|
protected $_sqlHelper; |
21
|
|
|
protected $_cacheUserWork = array(); |
22
|
|
|
protected $_cacheUserOriginal = array(); |
23
|
|
|
|
24
|
|
|
/** |
25
|
|
|
* DBDataset constructor |
26
|
|
|
* @param string $dataBase |
27
|
|
|
* @param UserTable $userTable |
28
|
|
|
* @param CustomTable $customTable |
29
|
|
|
*/ |
30
|
|
|
public function __construct($dataBase, UserTable $userTable = null, CustomTable $customTable = null) |
31
|
|
|
{ |
32
|
|
|
$this->_db = new DBDataset($dataBase); |
33
|
|
|
$this->_sqlHelper = new SQLHelper($this->_db); |
34
|
|
|
$this->_userTable = $userTable; |
35
|
|
|
$this->_customTable = $customTable; |
36
|
|
|
} |
37
|
|
|
|
38
|
|
|
/** |
39
|
|
|
* |
40
|
|
|
* Save the current UsersAnyDataset |
41
|
|
|
*/ |
42
|
|
|
public function save() |
43
|
|
|
{ |
44
|
|
|
foreach ($this->_cacheUserOriginal as $key => $value) { |
45
|
|
|
$srOri = $this->_cacheUserOriginal[$key]; |
46
|
|
|
$srMod = $this->_cacheUserWork[$key]; |
47
|
|
|
|
48
|
|
|
// Look for changes |
49
|
|
|
$changeUser = false; |
50
|
|
|
foreach ($srMod->getFieldNames() as $keyfld => $fieldname) { |
51
|
|
|
$userField = ($fieldname == $this->getUserTable()->name |
52
|
|
|
|| $fieldname == $this->getUserTable()->email |
53
|
|
|
|| $fieldname == $this->getUserTable()->username |
54
|
|
|
|| $fieldname == $this->getUserTable()->password |
55
|
|
|
|| $fieldname == $this->getUserTable()->created |
56
|
|
|
|| $fieldname == $this->getUserTable()->admin |
57
|
|
|
|| $fieldname == $this->getUserTable()->id |
58
|
|
|
); |
59
|
|
|
if ($srOri->getField($fieldname) != $srMod->getField($fieldname)) { |
60
|
|
|
// This change is in the Users table or is a Custom property? |
61
|
|
|
if ($userField) { |
62
|
|
|
$changeUser = true; |
63
|
|
|
} else { |
64
|
|
|
// Erase Old Custom Properties |
65
|
|
|
$this->removeProperty($srMod->getField($this->getUserTable()->id), $fieldname, $srOri->getField($fieldname)); |
66
|
|
|
|
67
|
|
|
// If new Value is_empty does not add |
68
|
|
|
if ($srMod->getField($fieldname) == "") { |
69
|
|
|
continue; |
70
|
|
|
} |
71
|
|
|
|
72
|
|
|
// Insert new Value |
73
|
|
|
$this->addProperty($srMod->getField($this->getUserTable()->id), $fieldname, $srMod->getField($fieldname)); |
74
|
|
|
} |
75
|
|
|
} |
76
|
|
|
} |
77
|
|
|
|
78
|
|
|
if ($changeUser) { |
79
|
|
|
|
80
|
|
|
$this->updateUser( |
81
|
|
|
$srMod->getField($this->getUserTable()->id), |
82
|
|
|
$srMod->getField($this->getUserTable()->name), |
83
|
|
|
$srMod->getField($this->getUserTable()->email), |
84
|
|
|
$srMod->getField($this->getUserTable()->username), |
85
|
|
|
$srMod->getField($this->getUserTable()->password), |
86
|
|
|
$srMod->getField($this->getUserTable()->created), |
87
|
|
|
$srMod->getField($this->getUserTable()->admin) |
88
|
|
|
); |
89
|
|
|
} |
90
|
|
|
} |
91
|
|
|
$this->_cacheUserOriginal = array(); |
92
|
|
|
$this->_cacheUserWork = array(); |
93
|
|
|
} |
94
|
|
|
|
95
|
|
|
/** |
96
|
|
|
* @param int $id |
97
|
|
|
* @param string $name |
98
|
|
|
* @param string $email |
99
|
|
|
* @param string $username |
100
|
|
|
* @param string $password |
101
|
|
|
* @param string $created |
102
|
|
|
* @param string $admin |
103
|
|
|
*/ |
104
|
|
|
protected function updateUser($id, $name, $email, $username, $password, $created, $admin) |
105
|
|
|
{ |
106
|
|
|
$sql = $this->sqlUpdateUser(); |
107
|
|
|
|
108
|
|
|
$sql = $this->_sqlHelper->createSafeSQL($sql, |
109
|
|
|
array( |
110
|
|
|
'@@Table' => $this->getUserTable()->table, |
111
|
|
|
'@@Name' => $this->getUserTable()->name, |
112
|
|
|
'@@Email' => $this->getUserTable()->email, |
113
|
|
|
'@@Username' => $this->getUserTable()->username, |
114
|
|
|
'@@Password' => $this->getUserTable()->password, |
115
|
|
|
'@@Created' => $this->getUserTable()->created, |
116
|
|
|
'@@Admin' => $this->getUserTable()->admin, |
117
|
|
|
'@@Id' => $this->getUserTable()->id |
118
|
|
|
) |
119
|
|
|
); |
120
|
|
|
|
121
|
|
|
$param = array(); |
122
|
|
|
$param['name'] = $name; |
123
|
|
|
$param['email'] = $email; |
124
|
|
|
$param['username'] = $username; |
125
|
|
|
$param['password'] = $password; |
126
|
|
|
$param['created'] = $created; |
127
|
|
|
$param['admin'] = $admin; |
128
|
|
|
$param['id'] = $id; |
129
|
|
|
|
130
|
|
|
$this->_db->execSQL($sql, $param); |
131
|
|
|
} |
132
|
|
|
|
133
|
|
|
protected function sqlUpdateUser() |
134
|
|
|
{ |
135
|
|
|
return |
136
|
|
|
"UPDATE @@Table " . |
137
|
|
|
" SET @@Name = [[name]] " . |
138
|
|
|
", @@Email = [[email]] " . |
139
|
|
|
", @@Username = [[username]] " . |
140
|
|
|
", @@Password = [[password]] " . |
141
|
|
|
", @@Created = [[created]] " . |
142
|
|
|
", @@Admin = [[admin]] " . |
143
|
|
|
" WHERE @@Id = [[id]]"; |
144
|
|
|
} |
145
|
|
|
|
146
|
|
|
/** |
147
|
|
|
* Add new user in database |
148
|
|
|
* |
149
|
|
|
* @param string $name |
150
|
|
|
* @param string $userName |
151
|
|
|
* @param string $email |
152
|
|
|
* @param string $password |
153
|
|
|
* @return bool |
154
|
|
|
* @throws UserExistsException |
155
|
|
|
*/ |
156
|
|
|
public function addUser($name, $userName, $email, $password) |
157
|
|
|
{ |
158
|
|
|
if ($this->getByEmail($email) !== null) { |
159
|
|
|
throw new UserExistsException('Email already exists'); |
160
|
|
|
} |
161
|
|
|
if ($this->getByUsername($userName) !== null) { |
162
|
|
|
throw new UserExistsException('Username already exists'); |
163
|
|
|
} |
164
|
|
|
|
165
|
|
|
$sql = $this->_sqlHelper->createSafeSQL($this->sqlAdUser(), |
166
|
|
|
array( |
167
|
|
|
'@@Table' => $this->getUserTable()->table, |
168
|
|
|
'@@Name' => $this->getUserTable()->name, |
169
|
|
|
'@@Email' => $this->getUserTable()->email, |
170
|
|
|
'@@Username' => $this->getUserTable()->username, |
171
|
|
|
'@@Password' => $this->getUserTable()->password, |
172
|
|
|
'@@Created' => $this->getUserTable()->created, |
173
|
|
|
'@@UserId' => $this->getUserTable()->id |
174
|
|
|
) |
175
|
|
|
); |
176
|
|
|
|
177
|
|
|
$param = array(); |
178
|
|
|
$param['name'] = $name; |
179
|
|
|
$param['email'] = strtolower($email); |
180
|
|
|
$param['username'] = preg_replace('/(?:([\w])|([\W]))/', '\1', strtolower($userName)); |
181
|
|
|
$param['password'] = $this->getPasswordHash($password); |
182
|
|
|
$param['created'] = date("Y-m-d H:i:s"); |
183
|
|
|
$param['userid'] = $this->generateUserId(); |
184
|
|
|
|
185
|
|
|
$this->_db->execSQL($sql, $param); |
186
|
|
|
|
187
|
|
|
return true; |
188
|
|
|
} |
189
|
|
|
|
190
|
|
|
protected function sqlAdUser() |
191
|
|
|
{ |
192
|
|
|
return |
193
|
|
|
" INSERT INTO @@Table ( @@UserId, @@Name, @@Email, @@Username, @@Password, @@Created ) " |
194
|
|
|
. " VALUES ( [[userid]], [[name]], [[email]], [[username]], [[password]], [[created]] ) "; |
195
|
|
|
} |
196
|
|
|
|
197
|
|
|
/** |
198
|
|
|
* Get the users database information based on a filter. |
199
|
|
|
* |
200
|
|
|
* @param IteratorFilter $filter Filter to find user |
201
|
|
|
* @param array $param |
202
|
|
|
* @return IteratorInterface |
203
|
|
|
*/ |
204
|
|
|
public function getIterator(IteratorFilter $filter = null, $param = array()) |
205
|
|
|
{ |
206
|
|
|
if (is_null($filter)) { |
207
|
|
|
$filter = new IteratorFilter(); |
208
|
|
|
} |
209
|
|
|
$sql = $filter->getSql($this->getUserTable()->table, $param); |
210
|
|
|
return $this->_db->getIterator($sql, $param); |
211
|
|
|
} |
212
|
|
|
|
213
|
|
|
/** |
214
|
|
|
* Get the user based on a filter. |
215
|
|
|
* Return SingleRow if user was found; null, otherwise |
216
|
|
|
* |
217
|
|
|
* @param IteratorFilter $filter Filter to find user |
218
|
|
|
* @return SingleRow |
219
|
|
|
* */ |
220
|
|
|
public function getUser($filter) |
221
|
|
|
{ |
222
|
|
|
$it = $this->getIterator($filter); |
223
|
|
|
if ($it->hasNext()) { |
224
|
|
|
// Get the Requested User |
225
|
|
|
$sr = $it->moveNext(); |
226
|
|
|
$this->setCustomFieldsInUser($sr); |
227
|
|
|
|
228
|
|
|
// Clone the User Properties |
229
|
|
|
$anyOri = new AnyDataset(); |
230
|
|
|
$anyOri->appendRow(); |
231
|
|
|
foreach ($sr->getFieldNames() as $key => $fieldName) { |
232
|
|
|
$anyOri->addField($fieldName, $sr->getField($fieldName)); |
233
|
|
|
} |
234
|
|
|
$itOri = $anyOri->getIterator(); |
235
|
|
|
$srOri = $itOri->moveNext(); |
236
|
|
|
|
237
|
|
|
// Store and return to the user the proper single row. |
238
|
|
|
$this->_cacheUserOriginal[$sr->getField($this->getUserTable()->id)] = $srOri; |
239
|
|
|
$this->_cacheUserWork[$sr->getField($this->getUserTable()->id)] = $sr; |
240
|
|
|
return $this->_cacheUserWork[$sr->getField($this->getUserTable()->id)]; |
241
|
|
|
} else { |
242
|
|
|
return null; |
243
|
|
|
} |
244
|
|
|
} |
245
|
|
|
|
246
|
|
|
/** |
247
|
|
|
* Remove the user based on his user login. |
248
|
|
|
* |
249
|
|
|
* @param string $login |
250
|
|
|
* @return bool |
251
|
|
|
* */ |
252
|
|
|
public function removeUserName($login) |
253
|
|
|
{ |
254
|
|
|
$user = $this->getByUsername($login); |
255
|
|
|
|
256
|
|
|
return $this->removeUserById($user->getField($this->getUserTable()->id)); |
257
|
|
|
} |
258
|
|
|
|
259
|
|
|
/** |
260
|
|
|
* Remove the user based on his user id. |
261
|
|
|
* |
262
|
|
|
* @param int $userId |
263
|
|
|
* @return bool |
264
|
|
|
* */ |
265
|
|
|
public function removeUserById($userId) |
266
|
|
|
{ |
267
|
|
|
$baseSql = $this->sqlRemoveUserById(); |
268
|
|
|
$param = array("id" => $userId); |
269
|
|
|
if ($this->getCustomTable()->table != "") { |
270
|
|
|
$sql = $this->_sqlHelper->createSafeSQL($baseSql, |
271
|
|
|
array( |
272
|
|
|
'@@Table' => $this->getCustomTable()->table, |
273
|
|
|
'@@Id' => $this->getUserTable()->id |
274
|
|
|
)); |
275
|
|
|
$this->_db->execSQL($sql, $param); |
276
|
|
|
} |
277
|
|
|
$sql = $this->_sqlHelper->createSafeSQL($baseSql, |
278
|
|
|
array( |
279
|
|
|
'@@Table' => $this->getUserTable()->table, |
280
|
|
|
'@@Id' => $this->getUserTable()->id |
281
|
|
|
)); |
282
|
|
|
$this->_db->execSQL($sql, $param); |
283
|
|
|
return true; |
284
|
|
|
} |
285
|
|
|
|
286
|
|
|
protected function sqlRemoveUserById() |
287
|
|
|
{ |
288
|
|
|
return "DELETE FROM @@Table WHERE @@Id = [[id]]" ; |
289
|
|
|
} |
290
|
|
|
|
291
|
|
|
/** |
292
|
|
|
* |
293
|
|
|
* @param int $userId |
294
|
|
|
* @param string $propertyName |
295
|
|
|
* @param string $value |
296
|
|
|
* @return bool|void |
297
|
|
|
*/ |
298
|
|
View Code Duplication |
public function addProperty($userId, $propertyName, $value) |
|
|
|
|
299
|
|
|
{ |
300
|
|
|
//anydataset.SingleRow |
301
|
|
|
$user = $this->getById($userId); |
302
|
|
|
if ($user !== null) { |
303
|
|
|
if (!$this->hasProperty($userId, $propertyName, $value)) { |
304
|
|
|
|
305
|
|
|
$sql = $this->_sqlHelper->createSafeSQL($this->sqlAddProperty(), |
306
|
|
|
array( |
307
|
|
|
"@@Table" => $this->getCustomTable()->table, |
308
|
|
|
"@@Id" => $this->getUserTable()->id, |
309
|
|
|
"@@Name" => $this->getCustomTable()->name, |
310
|
|
|
"@@Value" => $this->getCustomTable()->value |
311
|
|
|
)); |
312
|
|
|
|
313
|
|
|
$param = array(); |
314
|
|
|
$param["id"] = $userId; |
315
|
|
|
$param["name"] = $propertyName; |
316
|
|
|
$param["value"] = $value; |
317
|
|
|
|
318
|
|
|
$this->_db->execSQL($sql, $param); |
319
|
|
|
} |
320
|
|
|
return true; |
321
|
|
|
} |
322
|
|
|
|
323
|
|
|
return false; |
324
|
|
|
} |
325
|
|
|
|
326
|
|
|
protected function sqlAddProperty() |
327
|
|
|
{ |
328
|
|
|
return |
329
|
|
|
" INSERT INTO @@Table ( @@Id, @@Name, @@Value ) " |
330
|
|
|
. " VALUES ( [[id]], [[name]], [[value]] ) "; |
331
|
|
|
} |
332
|
|
|
|
333
|
|
|
/** |
334
|
|
|
* Remove a specific site from user |
335
|
|
|
* Return True or false |
336
|
|
|
* |
337
|
|
|
* @param int $userId User Id |
338
|
|
|
* @param string $propertyName Property name |
339
|
|
|
* @param string $value Property value with a site |
340
|
|
|
* @return bool |
341
|
|
|
* */ |
342
|
|
View Code Duplication |
public function removeProperty($userId, $propertyName, $value) |
|
|
|
|
343
|
|
|
{ |
344
|
|
|
$user = $this->getById($userId); |
345
|
|
|
if ($user !== null) { |
346
|
|
|
$param = array(); |
347
|
|
|
$param["id"] = $userId; |
348
|
|
|
$param["name"] = $propertyName; |
349
|
|
|
$param["value"] = $value; |
350
|
|
|
|
351
|
|
|
$sql = $this->_sqlHelper->createSafeSQL($this->sqlRemoveProperty(!is_null($value)), |
352
|
|
|
array( |
353
|
|
|
'@@Table' => $this->getCustomTable()->table, |
354
|
|
|
'@@Name' => $this->getCustomTable()->name, |
355
|
|
|
'@@Id' => $this->getUserTable()->id, |
356
|
|
|
'@@Value' => $this->getCustomTable()->value |
357
|
|
|
) |
358
|
|
|
); |
359
|
|
|
|
360
|
|
|
$this->_db->execSQL($sql, $param); |
361
|
|
|
return true; |
362
|
|
|
} |
363
|
|
|
|
364
|
|
|
return false; |
365
|
|
|
} |
366
|
|
|
|
367
|
|
|
protected function sqlRemoveProperty($withValue = false) |
368
|
|
|
{ |
369
|
|
|
return |
370
|
|
|
"DELETE FROM @@Table " |
371
|
|
|
. " WHERE @@Id = [[id]] AND @@Name = [[name]] " |
372
|
|
|
. ($withValue ? " AND @@Value = [[value]] " : ''); |
373
|
|
|
} |
374
|
|
|
|
375
|
|
|
/** |
376
|
|
|
* Remove a specific site from all users |
377
|
|
|
* Return True or false |
378
|
|
|
* |
379
|
|
|
* @param string $propertyName Property name |
380
|
|
|
* @param string $value Property value with a site |
381
|
|
|
* @return bool |
382
|
|
|
* */ |
383
|
|
|
public function removeAllProperties($propertyName, $value) |
384
|
|
|
{ |
385
|
|
|
$param = array(); |
386
|
|
|
$param["name"] = $propertyName; |
387
|
|
|
$param["value"] = $value; |
388
|
|
|
|
389
|
|
|
$sql = $this->_sqlHelper->createSafeSQL($this->sqlRemoveAllProperties(), |
390
|
|
|
array( |
391
|
|
|
"@@Table" => $this->getCustomTable()->table, |
392
|
|
|
"@@Name" => $this->getCustomTable()->name, |
393
|
|
|
"@@Value" => $this->getCustomTable()->value |
394
|
|
|
)); |
395
|
|
|
|
396
|
|
|
$this->_db->execSQL($sql, $param); |
397
|
|
|
} |
398
|
|
|
|
399
|
|
|
protected function sqlRemoveAllProperties() |
400
|
|
|
{ |
401
|
|
|
return "DELETE FROM @@Table WHERE @@Name = [[name]] AND @@Value = [[value]] "; |
402
|
|
|
} |
403
|
|
|
|
404
|
|
|
/** |
405
|
|
|
* Return all custom's fields from this user |
406
|
|
|
* |
407
|
|
|
* @param SingleRow $userRow |
408
|
|
|
*/ |
409
|
|
|
protected function setCustomFieldsInUser($userRow) |
410
|
|
|
{ |
411
|
|
|
if ($this->getCustomTable()->table == "") { |
412
|
|
|
return; |
413
|
|
|
} |
414
|
|
|
|
415
|
|
|
$userId = $userRow->getField($this->getUserTable()->id); |
416
|
|
|
|
417
|
|
|
$sql = $this->_sqlHelper->createSafeSQL($this->sqlSetCustomFieldsInUser(), |
418
|
|
|
array( |
419
|
|
|
"@@Table" => $this->getCustomTable()->table, |
420
|
|
|
"@@Id" => $this->getUserTable()->id |
421
|
|
|
)); |
422
|
|
|
|
423
|
|
|
$param = array('id' => $userId); |
424
|
|
|
$it = $this->_db->getIterator($sql, $param); |
425
|
|
|
while ($it->hasNext()) { |
426
|
|
|
$sr = $it->moveNext(); |
427
|
|
|
$userRow->addField($sr->getField($this->getCustomTable()->name), |
428
|
|
|
$sr->getField($this->getCustomTable()->value)); |
429
|
|
|
} |
430
|
|
|
} |
431
|
|
|
|
432
|
|
|
protected function sqlSetCustomFieldsInUser() |
433
|
|
|
{ |
434
|
|
|
return "select * from @@Table where @@Id = [[id]]"; |
435
|
|
|
} |
436
|
|
|
} |
437
|
|
|
|
Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.
You can also find more detailed suggestions in the “Code” section of your repository.