1
|
|
|
<?php |
2
|
|
|
/** |
3
|
|
|
* sysPass |
4
|
|
|
* |
5
|
|
|
* @author nuxsmin |
6
|
|
|
* @link https://syspass.org |
7
|
|
|
* @copyright 2012-2019, Rubén Domínguez nuxsmin@$syspass.org |
8
|
|
|
* |
9
|
|
|
* This file is part of sysPass. |
10
|
|
|
* |
11
|
|
|
* sysPass is free software: you can redistribute it and/or modify |
12
|
|
|
* it under the terms of the GNU General Public License as published by |
13
|
|
|
* the Free Software Foundation, either version 3 of the License, or |
14
|
|
|
* (at your option) any later version. |
15
|
|
|
* |
16
|
|
|
* sysPass is distributed in the hope that it will be useful, |
17
|
|
|
* but WITHOUT ANY WARRANTY; without even the implied warranty of |
18
|
|
|
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
19
|
|
|
* GNU General Public License for more details. |
20
|
|
|
* |
21
|
|
|
* You should have received a copy of the GNU General Public License |
22
|
|
|
* along with sysPass. If not, see <http://www.gnu.org/licenses/>. |
23
|
|
|
*/ |
24
|
|
|
|
25
|
|
|
namespace SP\Repositories\Account; |
26
|
|
|
|
27
|
|
|
use RuntimeException; |
28
|
|
|
use SP\Core\Exceptions\ConstraintException; |
29
|
|
|
use SP\Core\Exceptions\QueryException; |
30
|
|
|
use SP\Core\Exceptions\SPException; |
31
|
|
|
use SP\DataModel\AccountData; |
32
|
|
|
use SP\DataModel\AccountExtData; |
33
|
|
|
use SP\DataModel\AccountPassData; |
34
|
|
|
use SP\DataModel\AccountSearchVData; |
35
|
|
|
use SP\DataModel\AccountVData; |
36
|
|
|
use SP\DataModel\ItemData; |
37
|
|
|
use SP\DataModel\ItemSearchData; |
38
|
|
|
use SP\Mvc\Model\QueryAssignment; |
39
|
|
|
use SP\Mvc\Model\QueryCondition; |
40
|
|
|
use SP\Mvc\Model\QueryJoin; |
41
|
|
|
use SP\Repositories\Repository; |
42
|
|
|
use SP\Repositories\RepositoryItemInterface; |
43
|
|
|
use SP\Repositories\RepositoryItemTrait; |
44
|
|
|
use SP\Services\Account\AccountPasswordRequest; |
45
|
|
|
use SP\Services\Account\AccountRequest; |
46
|
|
|
use SP\Services\Account\AccountSearchFilter; |
47
|
|
|
use SP\Storage\Database\QueryData; |
48
|
|
|
use SP\Storage\Database\QueryResult; |
49
|
|
|
use stdClass; |
50
|
|
|
|
51
|
|
|
/** |
52
|
|
|
* Class AccountRepository |
53
|
|
|
* |
54
|
|
|
* @package Services |
55
|
|
|
*/ |
56
|
|
|
final class AccountRepository extends Repository implements RepositoryItemInterface |
57
|
|
|
{ |
58
|
|
|
use RepositoryItemTrait; |
59
|
|
|
|
60
|
|
|
/** |
61
|
|
|
* Devolver el número total de cuentas |
62
|
|
|
* |
63
|
|
|
* @return stdClass |
64
|
|
|
* @throws QueryException |
65
|
|
|
* @throws ConstraintException |
66
|
|
|
*/ |
67
|
|
|
public function getTotalNumAccounts() |
68
|
|
|
{ |
69
|
|
|
$query = /** @lang SQL */ |
70
|
|
|
'SELECT SUM(n) AS num FROM |
71
|
|
|
(SELECT COUNT(*) AS n FROM Account UNION SELECT COUNT(*) AS n FROM AccountHistory) a'; |
72
|
|
|
|
73
|
|
|
$queryData = new QueryData(); |
74
|
|
|
$queryData->setQuery($query); |
75
|
|
|
|
76
|
|
|
return $this->db->doSelect($queryData)->getData(); |
77
|
|
|
} |
78
|
|
|
|
79
|
|
|
/** |
80
|
|
|
* @param $id |
81
|
|
|
* @param QueryCondition $queryCondition |
82
|
|
|
* |
83
|
|
|
* @return QueryResult |
84
|
|
|
* @throws ConstraintException |
85
|
|
|
* @throws QueryException |
86
|
|
|
*/ |
87
|
|
|
public function getPasswordForId($id, QueryCondition $queryCondition) |
88
|
|
|
{ |
89
|
|
|
$queryCondition->addFilter('Account.id = ?', [$id]); |
90
|
|
|
|
91
|
|
|
$queryData = new QueryData(); |
92
|
|
|
$queryData->setMapClassName(AccountPassData::class); |
93
|
|
|
$queryData->setLimit(1); |
94
|
|
|
$queryData->setSelect('Account.id, Account.name, Account.login, Account.pass, Account.key, Account.parentId'); |
95
|
|
|
$queryData->setFrom('Account'); |
96
|
|
|
$queryData->setWhere($queryCondition->getFilters()); |
97
|
|
|
$queryData->setParams($queryCondition->getParams()); |
98
|
|
|
|
99
|
|
|
return $this->db->doSelect($queryData); |
100
|
|
|
} |
101
|
|
|
|
102
|
|
|
/** |
103
|
|
|
* @param QueryCondition $queryCondition |
104
|
|
|
* |
105
|
|
|
* @return QueryResult |
106
|
|
|
* @throws ConstraintException |
107
|
|
|
* @throws QueryException |
108
|
|
|
*/ |
109
|
|
|
public function getPasswordHistoryForId(QueryCondition $queryCondition) |
110
|
|
|
{ |
111
|
|
|
$query = /** @lang SQL */ |
112
|
|
|
'SELECT |
113
|
|
|
AccountHistory.id, |
114
|
|
|
AccountHistory.name, |
115
|
|
|
AccountHistory.login, |
116
|
|
|
AccountHistory.pass, |
117
|
|
|
AccountHistory.key, |
118
|
|
|
AccountHistory.parentId, |
119
|
|
|
AccountHistory.mPassHash |
120
|
|
|
FROM AccountHistory |
121
|
|
|
WHERE ' . $queryCondition->getFilters(); |
122
|
|
|
|
123
|
|
|
$queryData = new QueryData(); |
124
|
|
|
$queryData->setMapClassName(AccountPassData::class); |
125
|
|
|
$queryData->setQuery($query); |
126
|
|
|
$queryData->setParams($queryCondition->getParams()); |
127
|
|
|
|
128
|
|
|
return $this->db->doSelect($queryData); |
129
|
|
|
} |
130
|
|
|
|
131
|
|
|
/** |
132
|
|
|
* Incrementa el contador de vista de clave de una cuenta en la BBDD |
133
|
|
|
* |
134
|
|
|
* @param int $id |
135
|
|
|
* |
136
|
|
|
* @return bool |
137
|
|
|
* @throws ConstraintException |
138
|
|
|
* @throws QueryException |
139
|
|
|
*/ |
140
|
|
|
public function incrementDecryptCounter($id) |
141
|
|
|
{ |
142
|
|
|
$query = /** @lang SQL */ |
143
|
|
|
'UPDATE Account SET countDecrypt = (countDecrypt + 1) WHERE id = ? LIMIT 1'; |
144
|
|
|
|
145
|
|
|
$queryData = new QueryData(); |
146
|
|
|
$queryData->setQuery($query); |
147
|
|
|
$queryData->addParam($id); |
148
|
|
|
|
149
|
|
|
return $this->db->doQuery($queryData)->getAffectedNumRows() === 1; |
150
|
|
|
} |
151
|
|
|
|
152
|
|
|
/** |
153
|
|
|
* Crea una nueva cuenta en la BBDD |
154
|
|
|
* |
155
|
|
|
* @param AccountRequest $itemData |
156
|
|
|
* |
157
|
|
|
* @return int |
158
|
|
|
* @throws ConstraintException |
159
|
|
|
* @throws QueryException |
160
|
|
|
*/ |
161
|
|
|
public function create($itemData) |
162
|
|
|
{ |
163
|
|
|
$query = /** @lang SQL */ |
164
|
|
|
'INSERT INTO Account SET |
165
|
|
|
clientId = ?, |
166
|
|
|
categoryId = ?, |
167
|
|
|
`name` = ?, |
168
|
|
|
login = ?, |
169
|
|
|
url = ?, |
170
|
|
|
pass = ?, |
171
|
|
|
`key` = ?, |
172
|
|
|
notes = ?, |
173
|
|
|
dateAdd = NOW(), |
174
|
|
|
userId = ?, |
175
|
|
|
userGroupId = ?, |
176
|
|
|
userEditId = ?, |
177
|
|
|
isPrivate = ?, |
178
|
|
|
isPrivateGroup = ?, |
179
|
|
|
passDate = UNIX_TIMESTAMP(), |
180
|
|
|
passDateChange = ?, |
181
|
|
|
parentId = ?'; |
182
|
|
|
|
183
|
|
|
$queryData = new QueryData(); |
184
|
|
|
$queryData->setQuery($query); |
185
|
|
|
$queryData->setParams([ |
186
|
|
|
$itemData->clientId, |
187
|
|
|
$itemData->categoryId, |
188
|
|
|
$itemData->name, |
189
|
|
|
$itemData->login, |
190
|
|
|
$itemData->url, |
191
|
|
|
$itemData->pass, |
192
|
|
|
$itemData->key, |
193
|
|
|
$itemData->notes, |
194
|
|
|
$itemData->userId, |
195
|
|
|
$itemData->userGroupId, |
196
|
|
|
$itemData->userId, |
197
|
|
|
$itemData->isPrivate, |
198
|
|
|
$itemData->isPrivateGroup, |
199
|
|
|
$itemData->passDateChange, |
200
|
|
|
$itemData->parentId |
201
|
|
|
]); |
202
|
|
|
$queryData->setOnErrorMessage(__u('Error while creating the account')); |
203
|
|
|
|
204
|
|
|
return $this->db->doQuery($queryData)->getLastId(); |
205
|
|
|
} |
206
|
|
|
|
207
|
|
|
/** |
208
|
|
|
* Actualiza la clave de una cuenta en la BBDD. |
209
|
|
|
* |
210
|
|
|
* @param AccountRequest $accountRequest |
211
|
|
|
* |
212
|
|
|
* @return int |
213
|
|
|
* @throws ConstraintException |
214
|
|
|
* @throws QueryException |
215
|
|
|
*/ |
216
|
|
|
public function editPassword(AccountRequest $accountRequest) |
217
|
|
|
{ |
218
|
|
|
$query = /** @lang SQL */ |
219
|
|
|
'UPDATE Account SET |
220
|
|
|
pass = ?, |
221
|
|
|
`key` = ?, |
222
|
|
|
userEditId = ?, |
223
|
|
|
dateEdit = NOW(), |
224
|
|
|
passDate = UNIX_TIMESTAMP(), |
225
|
|
|
passDateChange = ? |
226
|
|
|
WHERE id = ?'; |
227
|
|
|
|
228
|
|
|
$queryData = new QueryData(); |
229
|
|
|
$queryData->setQuery($query); |
230
|
|
|
$queryData->setParams([ |
231
|
|
|
$accountRequest->pass, |
232
|
|
|
$accountRequest->key, |
233
|
|
|
$accountRequest->userEditId, |
234
|
|
|
$accountRequest->passDateChange, |
235
|
|
|
$accountRequest->id |
236
|
|
|
]); |
237
|
|
|
$queryData->setOnErrorMessage(__u('Error while updating the password')); |
238
|
|
|
|
239
|
|
|
return $this->db->doQuery($queryData)->getAffectedNumRows(); |
240
|
|
|
} |
241
|
|
|
|
242
|
|
|
/** |
243
|
|
|
* Actualiza la clave de una cuenta en la BBDD. |
244
|
|
|
* |
245
|
|
|
* @param AccountPasswordRequest $request |
246
|
|
|
* |
247
|
|
|
* @return bool |
248
|
|
|
* @throws ConstraintException |
249
|
|
|
* @throws QueryException |
250
|
|
|
*/ |
251
|
|
|
public function updatePassword(AccountPasswordRequest $request) |
252
|
|
|
{ |
253
|
|
|
$query = /** @lang SQL */ |
254
|
|
|
'UPDATE Account SET |
255
|
|
|
pass = ?, |
256
|
|
|
`key` = ? |
257
|
|
|
WHERE id = ?'; |
258
|
|
|
|
259
|
|
|
$queryData = new QueryData(); |
260
|
|
|
$queryData->setQuery($query); |
261
|
|
|
$queryData->setParams([$request->pass, $request->key, $request->id]); |
262
|
|
|
$queryData->setOnErrorMessage(__u('Error while updating the password')); |
263
|
|
|
|
264
|
|
|
return $this->db->doQuery($queryData)->getAffectedNumRows() === 1; |
265
|
|
|
} |
266
|
|
|
|
267
|
|
|
/** |
268
|
|
|
* Restaurar una cuenta desde el histórico. |
269
|
|
|
* |
270
|
|
|
* @param int $historyId El Id del registro en el histórico |
271
|
|
|
* @param int $userId User's Id |
272
|
|
|
* |
273
|
|
|
* @return bool |
274
|
|
|
* @throws ConstraintException |
275
|
|
|
* @throws QueryException |
276
|
|
|
*/ |
277
|
|
|
public function editRestore($historyId, $userId) |
278
|
|
|
{ |
279
|
|
|
$query = /** @lang SQL */ |
280
|
|
|
'UPDATE Account dst, |
281
|
|
|
(SELECT * FROM AccountHistory AH WHERE AH.id = ?) src SET |
282
|
|
|
dst.clientId = src.clientId, |
283
|
|
|
dst.categoryId = src.categoryId, |
284
|
|
|
dst.name = src.name, |
285
|
|
|
dst.login = src.login, |
286
|
|
|
dst.url = src.url, |
287
|
|
|
dst.notes = src.notes, |
288
|
|
|
dst.userGroupId = src.userGroupId, |
289
|
|
|
dst.userEditId = ?, |
290
|
|
|
dst.dateEdit = NOW(), |
291
|
|
|
dst.pass = src.pass, |
292
|
|
|
dst.key = src.key, |
293
|
|
|
dst.passDate = src.passDate, |
294
|
|
|
dst.passDateChange = src.passDateChange, |
295
|
|
|
dst.parentId = src.parentId, |
296
|
|
|
dst.isPrivate = src.isPrivate, |
297
|
|
|
dst.isPrivateGroup = src.isPrivateGroup |
298
|
|
|
WHERE dst.id = src.accountId'; |
299
|
|
|
|
300
|
|
|
$queryData = new QueryData(); |
301
|
|
|
$queryData->setQuery($query); |
302
|
|
|
$queryData->setParams([$historyId, $userId]); |
303
|
|
|
$queryData->setOnErrorMessage(__u('Error on restoring the account')); |
304
|
|
|
|
305
|
|
|
return $this->db->doQuery($queryData)->getAffectedNumRows() === 1; |
306
|
|
|
} |
307
|
|
|
|
308
|
|
|
/** |
309
|
|
|
* Elimina los datos de una cuenta en la BBDD. |
310
|
|
|
* |
311
|
|
|
* @param int $id |
312
|
|
|
* |
313
|
|
|
* @return int EL número de cuentas eliminadas |
314
|
|
|
* @throws ConstraintException |
315
|
|
|
* @throws QueryException |
316
|
|
|
*/ |
317
|
|
|
public function delete($id) |
318
|
|
|
{ |
319
|
|
|
$queryData = new QueryData(); |
320
|
|
|
|
321
|
|
|
$queryData->setQuery('DELETE FROM Account WHERE id = ? LIMIT 1'); |
322
|
|
|
$queryData->addParam($id); |
323
|
|
|
$queryData->setOnErrorMessage(__u('Error while deleting the account')); |
324
|
|
|
|
325
|
|
|
return $this->db->doQuery($queryData)->getAffectedNumRows(); |
326
|
|
|
} |
327
|
|
|
|
328
|
|
|
/** |
329
|
|
|
* Updates an item |
330
|
|
|
* |
331
|
|
|
* @param AccountRequest $itemData |
332
|
|
|
* |
333
|
|
|
* @return mixed |
334
|
|
|
* @throws SPException |
335
|
|
|
*/ |
336
|
|
|
public function update($itemData) |
337
|
|
|
{ |
338
|
|
|
$queryAssignment = new QueryAssignment(); |
339
|
|
|
|
340
|
|
|
$queryAssignment->setFields([ |
341
|
|
|
'clientId', |
342
|
|
|
'categoryId', |
343
|
|
|
'name', |
344
|
|
|
'login', |
345
|
|
|
'url', |
346
|
|
|
'notes', |
347
|
|
|
'userEditId', |
348
|
|
|
'dateEdit = NOW()', |
349
|
|
|
'passDateChange', |
350
|
|
|
'isPrivate', |
351
|
|
|
'isPrivateGroup', |
352
|
|
|
'parentId' |
353
|
|
|
], [ |
354
|
|
|
$itemData->clientId, |
355
|
|
|
$itemData->categoryId, |
356
|
|
|
$itemData->name, |
357
|
|
|
$itemData->login, |
358
|
|
|
$itemData->url, |
359
|
|
|
$itemData->notes, |
360
|
|
|
$itemData->userEditId, |
361
|
|
|
$itemData->passDateChange, |
362
|
|
|
$itemData->isPrivate, |
363
|
|
|
$itemData->isPrivateGroup, |
364
|
|
|
$itemData->parentId |
365
|
|
|
]); |
366
|
|
|
|
367
|
|
|
|
368
|
|
|
$queryData = new QueryData(); |
369
|
|
|
|
370
|
|
|
if ($itemData->changeUserGroup) { |
371
|
|
|
$queryAssignment->addField('userGroupId', $itemData->userGroupId); |
372
|
|
|
} |
373
|
|
|
|
374
|
|
|
if ($itemData->changeOwner) { |
375
|
|
|
$queryAssignment->addField('userId', $itemData->userId); |
376
|
|
|
} |
377
|
|
|
|
378
|
|
|
$query = /** @lang SQL */ |
379
|
|
|
'UPDATE Account SET ' . $queryAssignment->getAssignments() . ' WHERE id = ?'; |
380
|
|
|
|
381
|
|
|
$queryData->setQuery($query); |
382
|
|
|
$queryData->setParams($queryAssignment->getValues()); |
383
|
|
|
$queryData->addParam($itemData->id); |
384
|
|
|
$queryData->setOnErrorMessage(__u('Error while updating the account')); |
385
|
|
|
|
386
|
|
|
return $this->db->doQuery($queryData)->getAffectedNumRows(); |
387
|
|
|
} |
388
|
|
|
|
389
|
|
|
/** |
390
|
|
|
* Updates an item for bulk action |
391
|
|
|
* |
392
|
|
|
* @param AccountRequest $itemData |
393
|
|
|
* |
394
|
|
|
* @return mixed |
395
|
|
|
* @throws SPException |
396
|
|
|
*/ |
397
|
|
|
public function updateBulk($itemData) |
398
|
|
|
{ |
399
|
|
|
$queryAssignment = new QueryAssignment(); |
400
|
|
|
|
401
|
|
|
$queryAssignment->setFields([ |
402
|
|
|
'userEditId', |
403
|
|
|
'dateEdit = NOW()' |
404
|
|
|
], [ |
405
|
|
|
$itemData->userEditId, |
406
|
|
|
]); |
407
|
|
|
|
408
|
|
|
$queryData = new QueryData(); |
409
|
|
|
|
410
|
|
|
$optional = ['clientId', 'categoryId', 'userId', 'userGroupId', 'passDateChange']; |
411
|
|
|
|
412
|
|
|
$optionalCount = 0; |
413
|
|
|
|
414
|
|
|
foreach ($optional as $field) { |
415
|
|
|
if (isset($itemData->{$field}) && !empty($itemData->{$field})) { |
416
|
|
|
$queryAssignment->addField($field, $itemData->{$field}); |
417
|
|
|
$optionalCount++; |
418
|
|
|
} else { |
419
|
|
|
logger(sprintf('Field \'%s\' not found in $itemData', $field), 'ERROR'); |
420
|
|
|
} |
421
|
|
|
} |
422
|
|
|
|
423
|
|
|
if ($optionalCount === 0) { |
424
|
|
|
return 0; |
425
|
|
|
} |
426
|
|
|
|
427
|
|
|
$query = /** @lang SQL */ |
428
|
|
|
'UPDATE Account SET ' . $queryAssignment->getAssignments() . ' WHERE id = ?'; |
429
|
|
|
|
430
|
|
|
$queryData->setQuery($query); |
431
|
|
|
$queryData->setParams($queryAssignment->getValues()); |
432
|
|
|
$queryData->addParam($itemData->id); |
433
|
|
|
$queryData->setOnErrorMessage(__u('Error while updating the account')); |
434
|
|
|
|
435
|
|
|
return $this->db->doQuery($queryData)->getAffectedNumRows(); |
436
|
|
|
} |
437
|
|
|
|
438
|
|
|
/** |
439
|
|
|
* Returns the item for given id |
440
|
|
|
* |
441
|
|
|
* @param int $id |
442
|
|
|
* |
443
|
|
|
* @return QueryResult |
444
|
|
|
* @throws QueryException |
445
|
|
|
* @throws ConstraintException |
446
|
|
|
*/ |
447
|
|
|
public function getById($id) |
448
|
|
|
{ |
449
|
|
|
$queryData = new QueryData(); |
450
|
|
|
$queryData->setQuery('SELECT * FROM account_data_v WHERE id = ? LIMIT 1'); |
451
|
|
|
$queryData->setMapClassName(AccountVData::class); |
452
|
|
|
$queryData->addParam($id); |
453
|
|
|
$queryData->setOnErrorMessage(__u('Error while retrieving account\'s data')); |
454
|
|
|
|
455
|
|
|
return $this->db->doSelect($queryData); |
456
|
|
|
} |
457
|
|
|
|
458
|
|
|
/** |
459
|
|
|
* Returns all the items |
460
|
|
|
* |
461
|
|
|
* @return QueryResult |
462
|
|
|
* @throws ConstraintException |
463
|
|
|
* @throws QueryException |
464
|
|
|
*/ |
465
|
|
|
public function getAll() |
466
|
|
|
{ |
467
|
|
|
$queryData = new QueryData(); |
468
|
|
|
$queryData->setMapClassName(AccountData::class); |
469
|
|
|
$queryData->setQuery('SELECT * FROM Account ORDER BY id'); |
470
|
|
|
|
471
|
|
|
return $this->db->doSelect($queryData); |
|
|
|
|
472
|
|
|
} |
473
|
|
|
|
474
|
|
|
/** |
475
|
|
|
* Returns all the items for given ids |
476
|
|
|
* |
477
|
|
|
* @param array $ids |
478
|
|
|
*/ |
479
|
|
|
public function getByIdBatch(array $ids) |
480
|
|
|
{ |
481
|
|
|
throw new RuntimeException('Not implemented'); |
482
|
|
|
} |
483
|
|
|
|
484
|
|
|
/** |
485
|
|
|
* Deletes all the items for given ids |
486
|
|
|
* |
487
|
|
|
* @param array $ids |
488
|
|
|
* |
489
|
|
|
* @return int |
490
|
|
|
* @throws ConstraintException |
491
|
|
|
* @throws QueryException |
492
|
|
|
*/ |
493
|
|
|
public function deleteByIdBatch(array $ids) |
494
|
|
|
{ |
495
|
|
|
if (empty($ids)) { |
496
|
|
|
return 0; |
|
|
|
|
497
|
|
|
} |
498
|
|
|
|
499
|
|
|
$queryData = new QueryData(); |
500
|
|
|
|
501
|
|
|
$queryData->setQuery('DELETE FROM Account WHERE id IN (' . $this->getParamsFromArray($ids) . ')'); |
502
|
|
|
$queryData->setParams($ids); |
503
|
|
|
$queryData->setOnErrorMessage(__u('Error while deleting the accounts')); |
504
|
|
|
|
505
|
|
|
return $this->db->doQuery($queryData)->getAffectedNumRows(); |
|
|
|
|
506
|
|
|
} |
507
|
|
|
|
508
|
|
|
/** |
509
|
|
|
* Checks whether the item is in use or not |
510
|
|
|
* |
511
|
|
|
* @param $id int |
512
|
|
|
*/ |
513
|
|
|
public function checkInUse($id) |
514
|
|
|
{ |
515
|
|
|
throw new RuntimeException('Not implemented'); |
516
|
|
|
} |
517
|
|
|
|
518
|
|
|
/** |
519
|
|
|
* Checks whether the item is duplicated on updating |
520
|
|
|
* |
521
|
|
|
* @param mixed $itemData |
522
|
|
|
*/ |
523
|
|
|
public function checkDuplicatedOnUpdate($itemData) |
524
|
|
|
{ |
525
|
|
|
throw new RuntimeException('Not implemented'); |
526
|
|
|
} |
527
|
|
|
|
528
|
|
|
/** |
529
|
|
|
* Checks whether the item is duplicated on adding |
530
|
|
|
* |
531
|
|
|
* @param mixed $itemData |
532
|
|
|
*/ |
533
|
|
|
public function checkDuplicatedOnAdd($itemData) |
534
|
|
|
{ |
535
|
|
|
throw new RuntimeException('Not implemented'); |
536
|
|
|
} |
537
|
|
|
|
538
|
|
|
/** |
539
|
|
|
* Searches for items by a given filter |
540
|
|
|
* |
541
|
|
|
* @param ItemSearchData $itemSearchData |
542
|
|
|
* |
543
|
|
|
* @return QueryResult |
544
|
|
|
* @throws ConstraintException |
545
|
|
|
* @throws QueryException |
546
|
|
|
*/ |
547
|
|
|
public function search(ItemSearchData $itemSearchData) |
548
|
|
|
{ |
549
|
|
|
$queryData = new QueryData(); |
550
|
|
|
$queryData->setSelect('id, name, clientName, categoryName, userName, userGroupName'); |
551
|
|
|
$queryData->setFrom('account_search_v'); |
552
|
|
|
$queryData->setOrder('name, clientName'); |
553
|
|
|
|
554
|
|
|
if ($itemSearchData->getSeachString() !== '') { |
555
|
|
|
$queryData->setWhere('name LIKE ? |
556
|
|
|
OR clientName LIKE ? |
557
|
|
|
OR categoryName LIKE ? |
558
|
|
|
OR userName LIKE ? |
559
|
|
|
OR userGroupName LIKE ?'); |
560
|
|
|
|
561
|
|
|
$search = '%' . $itemSearchData->getSeachString() . '%'; |
562
|
|
|
$queryData->addParam($search); |
563
|
|
|
$queryData->addParam($search); |
564
|
|
|
$queryData->addParam($search); |
565
|
|
|
$queryData->addParam($search); |
566
|
|
|
$queryData->addParam($search); |
567
|
|
|
} |
568
|
|
|
|
569
|
|
|
$queryData->setLimit( |
570
|
|
|
'?,?', |
571
|
|
|
[$itemSearchData->getLimitStart(), $itemSearchData->getLimitCount()] |
572
|
|
|
); |
573
|
|
|
|
574
|
|
|
return $this->db->doSelect($queryData, true); |
575
|
|
|
} |
576
|
|
|
|
577
|
|
|
/** |
578
|
|
|
* Incrementa el contador de visitas de una cuenta en la BBDD |
579
|
|
|
* |
580
|
|
|
* @param int $id |
581
|
|
|
* |
582
|
|
|
* @return bool |
583
|
|
|
* @throws ConstraintException |
584
|
|
|
* @throws QueryException |
585
|
|
|
*/ |
586
|
|
|
public function incrementViewCounter($id) |
587
|
|
|
{ |
588
|
|
|
$queryData = new QueryData(); |
589
|
|
|
$queryData->setQuery('UPDATE Account SET countView = (countView + 1) WHERE id = ? LIMIT 1'); |
590
|
|
|
$queryData->addParam($id); |
591
|
|
|
|
592
|
|
|
return $this->db->doQuery($queryData)->getAffectedNumRows() === 1; |
593
|
|
|
} |
594
|
|
|
|
595
|
|
|
/** |
596
|
|
|
* Obtener los datos de una cuenta. |
597
|
|
|
* |
598
|
|
|
* @param $id |
599
|
|
|
* |
600
|
|
|
* @return QueryResult |
601
|
|
|
* @throws QueryException |
602
|
|
|
* @throws ConstraintException |
603
|
|
|
*/ |
604
|
|
|
public function getDataForLink($id) |
605
|
|
|
{ |
606
|
|
|
$query = /** @lang SQL */ |
607
|
|
|
'SELECT Account.id, |
608
|
|
|
Account.name, |
609
|
|
|
Account.login, |
610
|
|
|
Account.pass, |
611
|
|
|
Account.key, |
612
|
|
|
Account.url, |
613
|
|
|
Account.notes, |
614
|
|
|
Client.name AS clientName, |
615
|
|
|
Category.name AS categoryName |
616
|
|
|
FROM Account |
617
|
|
|
INNER JOIN Client ON Account.clientId = Client.id |
618
|
|
|
INNER JOIN Category ON Account.categoryId = Category.id |
619
|
|
|
WHERE Account.id = ? LIMIT 1'; |
620
|
|
|
|
621
|
|
|
$queryData = new QueryData(); |
622
|
|
|
$queryData->setQuery($query); |
623
|
|
|
$queryData->setMapClassName(AccountExtData::class); |
624
|
|
|
$queryData->addParam($id); |
625
|
|
|
$queryData->setOnErrorMessage(__u('Error while retrieving account\'s data')); |
626
|
|
|
|
627
|
|
|
return $this->db->doSelect($queryData); |
628
|
|
|
} |
629
|
|
|
|
630
|
|
|
/** |
631
|
|
|
* Obtener las cuentas de una búsqueda. |
632
|
|
|
* |
633
|
|
|
* @param AccountSearchFilter $accountSearchFilter |
634
|
|
|
* @param QueryCondition $queryFilterUser |
635
|
|
|
* |
636
|
|
|
* @return QueryResult |
637
|
|
|
* @throws ConstraintException |
638
|
|
|
* @throws QueryException |
639
|
|
|
* @throws SPException |
640
|
|
|
*/ |
641
|
|
|
public function getByFilter(AccountSearchFilter $accountSearchFilter, QueryCondition $queryFilterUser) |
642
|
|
|
{ |
643
|
|
|
$queryFilters = new QueryCondition(); |
644
|
|
|
|
645
|
|
|
// Sets the search text depending on if special search filters are being used |
646
|
|
|
$searchText = $accountSearchFilter->getCleanTxtSearch(); |
647
|
|
|
|
648
|
|
|
if (!empty($searchText)) { |
649
|
|
|
$queryFilters->addFilter( |
650
|
|
|
'Account.name LIKE ? OR Account.login LIKE ? OR Account.url LIKE ? OR Account.notes LIKE ?', |
651
|
|
|
array_fill(0, 4, '%' . $searchText . '%') |
652
|
|
|
); |
653
|
|
|
} |
654
|
|
|
|
655
|
|
|
// Gets special search filters |
656
|
|
|
$stringFilters = $accountSearchFilter->getStringFilters(); |
657
|
|
|
|
658
|
|
|
if ($stringFilters->hasFilters()) { |
659
|
|
|
$queryFilters->addFilter($stringFilters->getFilters($accountSearchFilter->getFilterOperator()), $stringFilters->getParams()); |
660
|
|
|
} |
661
|
|
|
|
662
|
|
|
if (!empty($accountSearchFilter->getCategoryId())) { |
663
|
|
|
$queryFilters->addFilter('Account.categoryId = ?', [$accountSearchFilter->getCategoryId()]); |
664
|
|
|
} |
665
|
|
|
|
666
|
|
|
if (!empty($accountSearchFilter->getClientId())) { |
667
|
|
|
$queryFilters->addFilter('Account.clientId = ?', [$accountSearchFilter->getClientId()]); |
668
|
|
|
} |
669
|
|
|
|
670
|
|
|
$where = []; |
671
|
|
|
|
672
|
|
|
if ($queryFilterUser->hasFilters()) { |
673
|
|
|
$where[] = $queryFilterUser->getFilters(); |
674
|
|
|
} |
675
|
|
|
|
676
|
|
|
$queryData = new QueryData(); |
677
|
|
|
$queryJoins = new QueryJoin(); |
678
|
|
|
|
679
|
|
|
if ($accountSearchFilter->isSearchFavorites() === true) { |
680
|
|
|
$queryJoins->addJoin( |
681
|
|
|
'INNER JOIN AccountToFavorite ON (AccountToFavorite.accountId = Account.id AND AccountToFavorite.userId = ?)', |
682
|
|
|
[$this->context->getUserData()->getId()] |
683
|
|
|
); |
684
|
|
|
} |
685
|
|
|
|
686
|
|
|
if ($accountSearchFilter->hasTags()) { |
687
|
|
|
$queryJoins->addJoin('INNER JOIN AccountToTag ON AccountToTag.accountId = Account.id'); |
688
|
|
|
$queryFilters->addFilter( |
689
|
|
|
'AccountToTag.tagId IN (' . $this->getParamsFromArray($accountSearchFilter->getTagsId()) . ')', |
690
|
|
|
$accountSearchFilter->getTagsId() |
691
|
|
|
); |
692
|
|
|
|
693
|
|
|
if (QueryCondition::CONDITION_AND === $accountSearchFilter->getFilterOperator()) { |
694
|
|
|
$queryData->setGroupBy('Account.id HAVING COUNT(DISTINCT AccountToTag.tagId) = ' . count($accountSearchFilter->getTagsId())); |
695
|
|
|
} |
696
|
|
|
} |
697
|
|
|
|
698
|
|
|
if ($queryFilters->hasFilters()) { |
699
|
|
|
$where[] = $queryFilters->getFilters($accountSearchFilter->getFilterOperator()); |
700
|
|
|
} |
701
|
|
|
|
702
|
|
|
$queryData->setWhere($where); |
703
|
|
|
$queryData->setParams(array_merge($queryJoins->getParams(), $queryFilterUser->getParams(), $queryFilters->getParams())); |
704
|
|
|
$queryData->setSelect('DISTINCT Account.*'); |
705
|
|
|
$queryData->setFrom('account_search_v Account ' . $queryJoins->getJoins()); |
706
|
|
|
$queryData->setOrder($accountSearchFilter->getOrderString()); |
707
|
|
|
|
708
|
|
|
if ($accountSearchFilter->getLimitCount() > 0) { |
709
|
|
|
$queryLimit = '?, ?'; |
710
|
|
|
|
711
|
|
|
$queryData->addParam($accountSearchFilter->getLimitStart()); |
712
|
|
|
$queryData->addParam($accountSearchFilter->getLimitCount()); |
713
|
|
|
$queryData->setLimit($queryLimit); |
714
|
|
|
} |
715
|
|
|
|
716
|
|
|
$queryData->setMapClassName(AccountSearchVData::class); |
717
|
|
|
|
718
|
|
|
return $this->db->doSelect($queryData, true); |
719
|
|
|
} |
720
|
|
|
|
721
|
|
|
/** |
722
|
|
|
* @param QueryCondition $queryFilter |
723
|
|
|
* |
724
|
|
|
* @return QueryResult |
725
|
|
|
* @throws ConstraintException |
726
|
|
|
* @throws QueryException |
727
|
|
|
*/ |
728
|
|
|
public function getForUser(QueryCondition $queryFilter) |
729
|
|
|
{ |
730
|
|
|
$query = /** @lang SQL */ |
731
|
|
|
'SELECT Account.id, Account.name, C.name AS clientName |
732
|
|
|
FROM Account |
733
|
|
|
LEFT JOIN Client C ON Account.clientId = C.id |
734
|
|
|
WHERE ' . $queryFilter->getFilters() . ' ORDER BY name'; |
735
|
|
|
|
736
|
|
|
$queryData = new QueryData(); |
737
|
|
|
$queryData->setMapClassName(ItemData::class); |
738
|
|
|
$queryData->setQuery($query); |
739
|
|
|
$queryData->setParams($queryFilter->getParams()); |
740
|
|
|
|
741
|
|
|
return $this->db->doSelect($queryData); |
742
|
|
|
} |
743
|
|
|
|
744
|
|
|
/** |
745
|
|
|
* @param QueryCondition $queryFilter |
746
|
|
|
* |
747
|
|
|
* @return QueryResult |
748
|
|
|
* @throws ConstraintException |
749
|
|
|
* @throws QueryException |
750
|
|
|
*/ |
751
|
|
|
public function getLinked(QueryCondition $queryFilter) |
752
|
|
|
{ |
753
|
|
|
$query = /** @lang SQL */ |
754
|
|
|
'SELECT Account.id, Account.name, Client.name AS clientName |
755
|
|
|
FROM Account |
756
|
|
|
INNER JOIN Client ON Account.clientId = Client.id |
757
|
|
|
WHERE ' . $queryFilter->getFilters() . ' ORDER BY Account.name'; |
758
|
|
|
|
759
|
|
|
$queryData = new QueryData(); |
760
|
|
|
$queryData->setQuery($query); |
761
|
|
|
$queryData->setParams($queryFilter->getParams()); |
762
|
|
|
|
763
|
|
|
return $this->db->doSelect($queryData); |
764
|
|
|
} |
765
|
|
|
|
766
|
|
|
/** |
767
|
|
|
* Obtener los datos relativos a la clave de todas las cuentas. |
768
|
|
|
* |
769
|
|
|
* @return array Con los datos de la clave |
770
|
|
|
* @throws ConstraintException |
771
|
|
|
* @throws QueryException |
772
|
|
|
*/ |
773
|
|
|
public function getAccountsPassData() |
774
|
|
|
{ |
775
|
|
|
$queryData = new QueryData(); |
776
|
|
|
$queryData->setQuery('SELECT id, `name`, pass, `key` FROM Account WHERE BIT_LENGTH(pass) > 0'); |
777
|
|
|
|
778
|
|
|
return $this->db->doSelect($queryData)->getDataAsArray(); |
779
|
|
|
} |
780
|
|
|
} |
In the issue above, the returned value is violating the contract defined by the mentioned interface.
Let's take a look at an example: