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->getUpdateUserSql(); |
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 getUpdateUserSql() |
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 = " INSERT INTO @@Table ( @@UserId, @@Name, @@Email, @@Username, @@Password, @@Created ) "; |
166
|
|
|
$sql .= " VALUES ( [[userid]], [[name]], [[email]], [[username]], [[password]], [[created]] ) "; |
167
|
|
|
|
168
|
|
|
$sql = $this->_sqlHelper->createSafeSQL($sql, |
169
|
|
|
array( |
170
|
|
|
'@@Table' => $this->getUserTable()->table, |
171
|
|
|
'@@Name' => $this->getUserTable()->name, |
172
|
|
|
'@@Email' => $this->getUserTable()->email, |
173
|
|
|
'@@Username' => $this->getUserTable()->username, |
174
|
|
|
'@@Password' => $this->getUserTable()->password, |
175
|
|
|
'@@Created' => $this->getUserTable()->created, |
176
|
|
|
'@@UserId' => $this->getUserTable()->id |
177
|
|
|
) |
178
|
|
|
); |
179
|
|
|
|
180
|
|
|
$param = array(); |
181
|
|
|
$param['name'] = $name; |
182
|
|
|
$param['email'] = strtolower($email); |
183
|
|
|
$param['username'] = preg_replace('/(?:([\w])|([\W]))/', '\1', strtolower($userName)); |
184
|
|
|
$param['password'] = $this->getPasswordHash($password); |
185
|
|
|
$param['created'] = date("Y-m-d H:i:s"); |
186
|
|
|
$param['userid'] = $this->generateUserId(); |
187
|
|
|
|
188
|
|
|
$this->_db->execSQL($sql, $param); |
189
|
|
|
|
190
|
|
|
return true; |
191
|
|
|
} |
192
|
|
|
|
193
|
|
|
/** |
194
|
|
|
* Get the users database information based on a filter. |
195
|
|
|
* |
196
|
|
|
* @param IteratorFilter $filter Filter to find user |
197
|
|
|
* @param array $param |
198
|
|
|
* @return IteratorInterface |
199
|
|
|
*/ |
200
|
|
|
public function getIterator(IteratorFilter $filter = null, $param = array()) |
201
|
|
|
{ |
202
|
|
|
if (is_null($filter)) { |
203
|
|
|
$filter = new IteratorFilter(); |
204
|
|
|
} |
205
|
|
|
$sql = $filter->getSql($this->getUserTable()->table, $param); |
206
|
|
|
return $this->_db->getIterator($sql, $param); |
207
|
|
|
} |
208
|
|
|
|
209
|
|
|
/** |
210
|
|
|
* Get the user based on a filter. |
211
|
|
|
* Return SingleRow if user was found; null, otherwise |
212
|
|
|
* |
213
|
|
|
* @param IteratorFilter $filter Filter to find user |
214
|
|
|
* @return SingleRow |
215
|
|
|
* */ |
216
|
|
|
public function getUser($filter) |
217
|
|
|
{ |
218
|
|
|
$it = $this->getIterator($filter); |
219
|
|
|
if ($it->hasNext()) { |
220
|
|
|
// Get the Requested User |
221
|
|
|
$sr = $it->moveNext(); |
222
|
|
|
$this->setCustomFieldsInUser($sr); |
223
|
|
|
|
224
|
|
|
// Clone the User Properties |
225
|
|
|
$anyOri = new AnyDataset(); |
226
|
|
|
$anyOri->appendRow(); |
227
|
|
|
foreach ($sr->getFieldNames() as $key => $fieldName) { |
228
|
|
|
$anyOri->addField($fieldName, $sr->getField($fieldName)); |
229
|
|
|
} |
230
|
|
|
$itOri = $anyOri->getIterator(); |
231
|
|
|
$srOri = $itOri->moveNext(); |
232
|
|
|
|
233
|
|
|
// Store and return to the user the proper single row. |
234
|
|
|
$this->_cacheUserOriginal[$sr->getField($this->getUserTable()->id)] = $srOri; |
235
|
|
|
$this->_cacheUserWork[$sr->getField($this->getUserTable()->id)] = $sr; |
236
|
|
|
return $this->_cacheUserWork[$sr->getField($this->getUserTable()->id)]; |
237
|
|
|
} else { |
238
|
|
|
return null; |
239
|
|
|
} |
240
|
|
|
} |
241
|
|
|
|
242
|
|
|
/** |
243
|
|
|
* Remove the user based on his user login. |
244
|
|
|
* |
245
|
|
|
* @param string $login |
246
|
|
|
* @return bool |
247
|
|
|
* */ |
248
|
|
|
public function removeUserName($login) |
249
|
|
|
{ |
250
|
|
|
$user = $this->getByUsername($login); |
251
|
|
|
|
252
|
|
|
return $this->removeUserById($user->getField($this->getUserTable()->id)); |
253
|
|
|
} |
254
|
|
|
|
255
|
|
|
/** |
256
|
|
|
* Remove the user based on his user id. |
257
|
|
|
* |
258
|
|
|
* @param int $userId |
259
|
|
|
* @return bool |
260
|
|
|
* */ |
261
|
|
|
public function removeUserById($userId) |
262
|
|
|
{ |
263
|
|
|
$baseSql = "DELETE FROM @@Table WHERE @@Id = [[id]] "; |
264
|
|
|
$param = array("id" => $userId); |
265
|
|
|
if ($this->getCustomTable()->table != "") { |
266
|
|
|
$sql = $this->_sqlHelper->createSafeSQL($baseSql, |
267
|
|
|
array( |
268
|
|
|
'@@Table' => $this->getCustomTable()->table, |
269
|
|
|
'@@Id' => $this->getUserTable()->id |
270
|
|
|
)); |
271
|
|
|
$this->_db->execSQL($sql, $param); |
272
|
|
|
} |
273
|
|
|
$sql = $this->_sqlHelper->createSafeSQL($baseSql, |
274
|
|
|
array( |
275
|
|
|
'@@Table' => $this->getUserTable()->table, |
276
|
|
|
'@@Id' => $this->getUserTable()->id |
277
|
|
|
)); |
278
|
|
|
$this->_db->execSQL($sql, $param); |
279
|
|
|
return true; |
280
|
|
|
} |
281
|
|
|
|
282
|
|
|
/** |
283
|
|
|
* |
284
|
|
|
* @param int $userId |
285
|
|
|
* @param string $propertyName |
286
|
|
|
* @param string $value |
287
|
|
|
* @return bool|void |
288
|
|
|
*/ |
289
|
|
|
public function addProperty($userId, $propertyName, $value) |
290
|
|
|
{ |
291
|
|
|
//anydataset.SingleRow |
292
|
|
|
$user = $this->getById($userId); |
293
|
|
View Code Duplication |
if ($user !== null) { |
|
|
|
|
294
|
|
|
if (!$this->hasProperty($userId, $propertyName, $value)) { |
295
|
|
|
$sql = " INSERT INTO @@Table ( @@Id, @@Name, @@Value ) "; |
296
|
|
|
$sql .= " VALUES ( [[id]], [[name]], [[value]] ) "; |
297
|
|
|
|
298
|
|
|
$sql = $this->_sqlHelper->createSafeSQL($sql, |
299
|
|
|
array( |
300
|
|
|
"@@Table" => $this->getCustomTable()->table, |
301
|
|
|
"@@Id" => $this->getUserTable()->id, |
302
|
|
|
"@@Name" => $this->getCustomTable()->name, |
303
|
|
|
"@@Value" => $this->getCustomTable()->value |
304
|
|
|
)); |
305
|
|
|
|
306
|
|
|
$param = array(); |
307
|
|
|
$param["id"] = $userId; |
308
|
|
|
$param["name"] = $propertyName; |
309
|
|
|
$param["value"] = $value; |
310
|
|
|
|
311
|
|
|
$this->_db->execSQL($sql, $param); |
312
|
|
|
} |
313
|
|
|
return true; |
314
|
|
|
} |
315
|
|
|
|
316
|
|
|
return false; |
317
|
|
|
} |
318
|
|
|
|
319
|
|
|
/** |
320
|
|
|
* Remove a specific site from user |
321
|
|
|
* Return True or false |
322
|
|
|
* |
323
|
|
|
* @param int $userId User Id |
324
|
|
|
* @param string $propertyName Property name |
325
|
|
|
* @param string $value Property value with a site |
326
|
|
|
* @return bool |
327
|
|
|
* */ |
328
|
|
|
public function removeProperty($userId, $propertyName, $value) |
329
|
|
|
{ |
330
|
|
|
$user = $this->getById($userId); |
331
|
|
View Code Duplication |
if ($user !== null) { |
|
|
|
|
332
|
|
|
$param = array(); |
333
|
|
|
$param["id"] = $userId; |
334
|
|
|
$param["name"] = $propertyName; |
335
|
|
|
|
336
|
|
|
$sql = "DELETE FROM @@Table "; |
337
|
|
|
$sql .= " WHERE @@Id = [[id]] AND @@Name = [[name]] "; |
338
|
|
|
if (!is_null($value)) { |
339
|
|
|
$sql .= " AND @@Value = [[value]] "; |
340
|
|
|
$param["value"] = $value; |
341
|
|
|
} |
342
|
|
|
$sql = $this->_sqlHelper->createSafeSQL($sql, |
343
|
|
|
array( |
344
|
|
|
'@@Table' => $this->getCustomTable()->table, |
345
|
|
|
'@@Name' => $this->getCustomTable()->name, |
346
|
|
|
'@@Id' => $this->getUserTable()->id, |
347
|
|
|
'@@Value' => $this->getCustomTable()->value |
348
|
|
|
) |
349
|
|
|
); |
350
|
|
|
|
351
|
|
|
$this->_db->execSQL($sql, $param); |
352
|
|
|
return true; |
353
|
|
|
} |
354
|
|
|
|
355
|
|
|
return false; |
356
|
|
|
} |
357
|
|
|
|
358
|
|
|
/** |
359
|
|
|
* Remove a specific site from all users |
360
|
|
|
* Return True or false |
361
|
|
|
* |
362
|
|
|
* @param string $propertyName Property name |
363
|
|
|
* @param string $value Property value with a site |
364
|
|
|
* @return bool |
365
|
|
|
* */ |
366
|
|
|
public function removeAllProperties($propertyName, $value) |
367
|
|
|
{ |
368
|
|
|
$param = array(); |
369
|
|
|
$param["name"] = $propertyName; |
370
|
|
|
$param["value"] = $value; |
371
|
|
|
|
372
|
|
|
$sql = "DELETE FROM @@Table WHERE @@Name = [[name]] AND @@Value = [[value]] "; |
373
|
|
|
|
374
|
|
|
$sql = $this->_sqlHelper->createSafeSQL($sql, |
375
|
|
|
array( |
376
|
|
|
"@@Table" => $this->getCustomTable()->table, |
377
|
|
|
"@@Name" => $this->getCustomTable()->name, |
378
|
|
|
"@@Value" => $this->getCustomTable()->value |
379
|
|
|
)); |
380
|
|
|
|
381
|
|
|
$this->_db->execSQL($sql, $param); |
382
|
|
|
} |
383
|
|
|
|
384
|
|
|
/** |
385
|
|
|
* Return all custom's fields from this user |
386
|
|
|
* |
387
|
|
|
* @param SingleRow $userRow |
388
|
|
|
*/ |
389
|
|
|
protected function setCustomFieldsInUser($userRow) |
390
|
|
|
{ |
391
|
|
|
if ($this->getCustomTable()->table == "") { |
392
|
|
|
return; |
393
|
|
|
} |
394
|
|
|
|
395
|
|
|
$userId = $userRow->getField($this->getUserTable()->id); |
396
|
|
|
$sql = "select * from @@Table where @@Id = [[id]]"; |
397
|
|
|
|
398
|
|
|
$sql = $this->_sqlHelper->createSafeSQL($sql, |
399
|
|
|
array( |
400
|
|
|
"@@Table" => $this->getCustomTable()->table, |
401
|
|
|
"@@Id" => $this->getUserTable()->id |
402
|
|
|
)); |
403
|
|
|
|
404
|
|
|
$param = array('id' => $userId); |
405
|
|
|
$it = $this->_db->getIterator($sql, $param); |
406
|
|
|
while ($it->hasNext()) { |
407
|
|
|
$sr = $it->moveNext(); |
408
|
|
|
$userRow->addField($sr->getField($this->getCustomTable()->name), |
409
|
|
|
$sr->getField($this->getCustomTable()->value)); |
410
|
|
|
} |
411
|
|
|
} |
412
|
|
|
} |
413
|
|
|
|
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.