| 1 |
<?php |
| 2 |
|
| 3 |
namespace AcyMailing\Classes; |
| 4 |
|
| 5 |
use AcyMailing\Helpers\MailerHelper; |
| 6 |
use AcyMailing\Helpers\PaginationHelper; |
| 7 |
use AcyMailing\Core\AcymClass; |
| 8 |
|
| 9 |
class ListClass extends AcymClass |
| 10 |
{ |
| 11 |
const LIST_TYPE_STANDARD = 'standard'; |
| 12 |
const LIST_TYPE_FRONT = 'front'; |
| 13 |
const LIST_TYPE_FOLLOWUP = 'followup'; |
| 14 |
const COLOR_PARTS = ['0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'a', 'b', 'c', 'd', 'e', 'f']; |
| 15 |
|
| 16 |
public function __construct() |
| 17 |
{ |
| 18 |
parent::__construct(); |
| 19 |
|
| 20 |
$this->table = 'list'; |
| 21 |
$this->pkey = 'id'; |
| 22 |
} |
| 23 |
|
| 24 |
public function getMatchingElements(array $settings = []): array |
| 25 |
{ |
| 26 |
$columns = 'list.*'; |
| 27 |
if (!empty($settings['columns'])) { |
| 28 |
foreach ($settings['columns'] as $key => $value) { |
| 29 |
if ($value == 'lists.list_id') { |
| 30 |
unset($settings['columns'][$key]); |
| 31 |
continue; |
| 32 |
} |
| 33 |
$settings['columns'][$key] = $key === 'join' ? acym_secureDBColumn($value) : 'list.'.acym_secureDBColumn($value); |
| 34 |
} |
| 35 |
$columns = implode(', ', $settings['columns']); |
| 36 |
} |
| 37 |
|
| 38 |
$query = 'SELECT '.$columns.' FROM #__acym_list AS list'; |
| 39 |
$queryCount = 'SELECT COUNT(list.id) AS total FROM #__acym_list AS list'; |
| 40 |
if (!empty($settings['join'])) $query .= $this->getJoinForQuery($settings['join']); |
| 41 |
/* |
| 42 |
This query will return for example: |
| 43 |
|
| 44 |
array( |
| 45 |
6 => 0, |
| 46 |
2 => 1, |
| 47 |
5 => 2, |
| 48 |
12 => 3 |
| 49 |
) |
| 50 |
|
| 51 |
It would mean that: |
| 52 |
|
| 53 |
6 lists are disabled and invisible 0 + (0*2) = 0 |
| 54 |
2 lists are active and invisible 1 + (0*2) = 1 |
| 55 |
5 lists are inactive and visible 0 + (1*2) = 2 |
| 56 |
12 lists are active and visible 1 + (1*2) = 3 |
| 57 |
|
| 58 |
So there are 2 + 12 active lists and 5 + 12 visible lists, get it? |
| 59 |
*/ |
| 60 |
$queryStatus = 'SELECT COUNT(id) AS number, active + (visible*2) AS score FROM #__acym_list AS list'; |
| 61 |
$filters = []; |
| 62 |
$listsId = []; |
| 63 |
|
| 64 |
if (!empty($settings['tag'])) { |
| 65 |
$tagJoin = ' JOIN #__acym_tag AS tag ON list.id = tag.id_element'; |
| 66 |
$query .= $tagJoin; |
| 67 |
$queryCount .= $tagJoin; |
| 68 |
$queryStatus .= $tagJoin; |
| 69 |
$filters[] = 'tag.name = '.acym_escapeDB($settings['tag']); |
| 70 |
$filters[] = 'tag.type = "list"'; |
| 71 |
} |
| 72 |
|
| 73 |
if (!empty($settings['search'])) { |
| 74 |
$filters[] = 'list.name LIKE '.acym_escapeDB('%'.$settings['search'].'%'); |
| 75 |
} |
| 76 |
|
| 77 |
if (!empty($settings['creator_id']) && !acym_isAdmin()) { |
| 78 |
$userGroups = acym_getGroupsByUser($settings['creator_id']); |
| 79 |
$groupCondition = '(list.access LIKE "%,'.implode(',%" OR list.access LIKE "%,', $userGroups).',%")'; |
| 80 |
|
| 81 |
$filters[] = 'list.cms_user_id = '.intval($settings['creator_id']).' OR '.$groupCondition.''; |
| 82 |
} |
| 83 |
|
| 84 |
$filters[] = 'list.type = '.acym_escapeDB(self::LIST_TYPE_STANDARD); |
| 85 |
|
| 86 |
if (!empty($filters)) { |
| 87 |
$queryStatus .= ' WHERE ('.implode(') AND (', $filters).')'; |
| 88 |
} |
| 89 |
|
| 90 |
if (!empty($settings['status'])) { |
| 91 |
$allowedStatus = [ |
| 92 |
'active' => 'active = 1', |
| 93 |
'inactive' => 'active = 0', |
| 94 |
'visible' => 'visible = 1', |
| 95 |
'invisible' => 'visible = 0', |
| 96 |
]; |
| 97 |
if (empty($allowedStatus[$settings['status']])) { |
| 98 |
die('Injection denied'); |
| 99 |
} |
| 100 |
$filters[] = 'list.'.$allowedStatus[$settings['status']]; |
| 101 |
} |
| 102 |
|
| 103 |
if (!empty($settings['where'])) { |
| 104 |
$filters[] = $settings['where']; |
| 105 |
} |
| 106 |
|
| 107 |
|
| 108 |
if (!empty($filters)) { |
| 109 |
$query .= ' WHERE ('.implode(') AND (', $filters).')'; |
| 110 |
$queryCount .= ' WHERE ('.implode(') AND (', $filters).')'; |
| 111 |
} |
| 112 |
|
| 113 |
if (!empty($settings['ordering']) && !empty($settings['ordering_sort_order'])) { |
| 114 |
$query .= ' ORDER BY list.'.acym_secureDBColumn($settings['ordering']).' '.acym_secureDBColumn(strtoupper($settings['ordering_sort_order'])); |
| 115 |
} |
| 116 |
|
| 117 |
if (empty($settings['offset']) || $settings['offset'] < 0) { |
| 118 |
$settings['offset'] = 0; |
| 119 |
} |
| 120 |
|
| 121 |
if (empty($settings['elementsPerPage']) || $settings['elementsPerPage'] < 1) { |
| 122 |
$pagination = new PaginationHelper(); |
| 123 |
$settings['elementsPerPage'] = $pagination->getListLimit(); |
| 124 |
} |
| 125 |
|
| 126 |
$results['elements'] = acym_loadObjectList($query, '', $settings['offset'], $settings['elementsPerPage']); |
| 127 |
if (!empty($settings['join'])) { |
| 128 |
$this->setSelectedList($results['elements'], $settings['join']); |
| 129 |
} |
| 130 |
foreach ($results['elements'] as $i => $oneList) { |
| 131 |
$listsId[] = $oneList->id; |
| 132 |
$results['elements'][$i]->sendable_users = 0; |
| 133 |
$results['elements'][$i]->unconfirmed_users = 0; |
| 134 |
$results['elements'][$i]->unsubscribed_users = 0; |
| 135 |
$results['elements'][$i]->inactive_users = 0; |
| 136 |
$results['elements'][$i]->newSub = 0; |
| 137 |
$results['elements'][$i]->newUnsub = 0; |
| 138 |
} |
| 139 |
|
| 140 |
$countUserByList = []; |
| 141 |
$countEvolByList = []; |
| 142 |
if (!empty($listsId) && empty($settings['entitySelect'])) { |
| 143 |
$countUserByList = $this->getSubscribersCountPerStatusByListIds($listsId); |
| 144 |
$countEvolByList = $this->getSubscribersEvolutionByListIds($listsId); |
| 145 |
} |
| 146 |
|
| 147 |
foreach ($results['elements'] as $i => $list) { |
| 148 |
$results['elements'][$i]->tags = []; |
| 149 |
foreach ($countUserByList as $userList) { |
| 150 |
if ($list->id == $userList->list_id) { |
| 151 |
$results['elements'][$i]->sendable_users = $userList->sendable_users; |
| 152 |
$results['elements'][$i]->unconfirmed_users = $userList->unconfirmed_users; |
| 153 |
$results['elements'][$i]->unsubscribed_users = $userList->unsubscribed_users; |
| 154 |
$results['elements'][$i]->inactive_users = $userList->inactive_users; |
| 155 |
} |
| 156 |
} |
| 157 |
if (isset($countEvolByList[$list->id]->newSub)) $results['elements'][$i]->newSub = $countEvolByList[$list->id]->newSub; |
| 158 |
if (isset($countEvolByList[$list->id]->newUnsub)) $results['elements'][$i]->newUnsub = $countEvolByList[$list->id]->newUnsub; |
| 159 |
} |
| 160 |
|
| 161 |
$results['total'] = acym_loadObject($queryCount); |
| 162 |
|
| 163 |
$listsPerStatus = acym_loadObjectList($queryStatus.' GROUP BY score', 'score'); |
| 164 |
for ($i = 0; $i < 4; $i++) { |
| 165 |
$listsPerStatus[$i] = empty($listsPerStatus[$i]) ? 0 : $listsPerStatus[$i]->number; |
| 166 |
} |
| 167 |
|
| 168 |
$results['status'] = [ |
| 169 |
'all' => array_sum($listsPerStatus), |
| 170 |
'active' => $listsPerStatus[1] + $listsPerStatus[3], |
| 171 |
'inactive' => $listsPerStatus[0] + $listsPerStatus[2], |
| 172 |
'visible' => $listsPerStatus[2] + $listsPerStatus[3], |
| 173 |
'invisible' => $listsPerStatus[0] + $listsPerStatus[1], |
| 174 |
]; |
| 175 |
|
| 176 |
return $results; |
| 177 |
} |
| 178 |
|
| 179 |
public function getOneById(int $id): ?object |
| 180 |
{ |
| 181 |
$list = parent::getOneById($id); |
| 182 |
|
| 183 |
if (!empty($list)) { |
| 184 |
$list->translation = empty($list->translation) ? [] : json_decode($list->translation, true); |
| 185 |
} |
| 186 |
|
| 187 |
return $list; |
| 188 |
} |
| 189 |
|
| 190 |
private function setSelectedList(array $elements, string $join): void |
| 191 |
{ |
| 192 |
if (strpos($join, 'join_list') === false) { |
| 193 |
return; |
| 194 |
} |
| 195 |
|
| 196 |
$listsIds = explode('-', $join); |
| 197 |
$listsIds = $listsIds[1]; |
| 198 |
$listsIds = explode(',', $listsIds); |
| 199 |
foreach ($elements as $element) { |
| 200 |
$element->list_id = in_array($element->id, $listsIds) ? $element->id : null; |
| 201 |
} |
| 202 |
} |
| 203 |
|
| 204 |
private function getJoinForQuery(string $joinType): string |
| 205 |
{ |
| 206 |
if (strpos($joinType, 'join_mail') !== false) { |
| 207 |
$mailId = explode('-', $joinType); |
| 208 |
|
| 209 |
return ' LEFT JOIN #__acym_mail_has_list as maillist ON list.id = maillist.list_id AND maillist.mail_id = '.intval($mailId[1]); |
| 210 |
} |
| 211 |
if (strpos($joinType, 'join_user') !== false) { |
| 212 |
$userId = explode('-', $joinType); |
| 213 |
|
| 214 |
return ' LEFT JOIN #__acym_user_has_list as userlist ON list.id = userlist.list_id AND userlist.status = 1 AND userlist.user_id = '.intval($userId[1]); |
| 215 |
} |
| 216 |
|
| 217 |
return ''; |
| 218 |
} |
| 219 |
|
| 220 |
public function getListsWithIdNameCount(array $settings): array |
| 221 |
{ |
| 222 |
$filters = []; |
| 223 |
|
| 224 |
if (isset($settings['ids'])) { |
| 225 |
if (empty($settings['ids'])) { |
| 226 |
return ['lists' => [], 'total' => 0]; |
| 227 |
} else { |
| 228 |
acym_arrayToInteger($settings['ids']); |
| 229 |
$filters[] = 'list.id IN ('.implode(',', $settings['ids']).')'; |
| 230 |
} |
| 231 |
} |
| 232 |
|
| 233 |
$query = 'SELECT list.id, list.name, list.color, list.active, COUNT(user.id) AS subscribers |
| 234 |
FROM #__acym_list AS list |
| 235 |
LEFT JOIN #__acym_user_has_list AS userList |
| 236 |
ON list.id = userList.list_id |
| 237 |
AND userList.status = 1 |
| 238 |
LEFT JOIN #__acym_user AS user |
| 239 |
ON user.id = userList.user_id |
| 240 |
AND user.active = 1 '; |
| 241 |
|
| 242 |
if (intval($this->config->get('require_confirmation', 1)) === 1) { |
| 243 |
$query .= ' AND user.confirmed = 1 '; |
| 244 |
} |
| 245 |
|
| 246 |
$queryCount = 'SELECT COUNT(list.id) FROM #__acym_list AS list'; |
| 247 |
|
| 248 |
if (!acym_isAdmin()) { |
| 249 |
$currentUserId = acym_currentUserId(); |
| 250 |
if (empty($currentUserId)) { |
| 251 |
return [ |
| 252 |
'lists' => [], |
| 253 |
'total' => 0, |
| 254 |
]; |
| 255 |
} |
| 256 |
|
| 257 |
$userGroups = acym_getGroupsByUser($currentUserId); |
| 258 |
$groupCondition = '(list.access LIKE "%,'.implode(',%" OR list.access LIKE "%,', $userGroups).',%")'; |
| 259 |
|
| 260 |
$filters[] = 'list.cms_user_id = '.intval($currentUserId).' OR '.$groupCondition; |
| 261 |
} |
| 262 |
|
| 263 |
if (!empty($settings['search'])) { |
| 264 |
$filters[] = 'list.name LIKE '.acym_escapeDB('%'.$settings['search'].'%'); |
| 265 |
} |
| 266 |
|
| 267 |
if (!empty($settings['already'])) { |
| 268 |
acym_arrayToInteger($settings['already']); |
| 269 |
$filters[] = 'list.id NOT IN('.implode(',', $settings['already']).')'; |
| 270 |
} |
| 271 |
$filters[] = 'list.type = '.acym_escapeDB(self::LIST_TYPE_STANDARD); |
| 272 |
|
| 273 |
if (!empty($filters)) { |
| 274 |
$query .= ' WHERE ('.implode(') AND (', $filters).')'; |
| 275 |
$queryCount .= ' WHERE ('.implode(') AND (', $filters).')'; |
| 276 |
} |
| 277 |
|
| 278 |
$query .= ' GROUP BY list.id '; |
| 279 |
|
| 280 |
return [ |
| 281 |
'lists' => acym_loadObjectList($query, '', $settings['offset'], $settings['listsPerPage']), |
| 282 |
'total' => (int)acym_loadResult($queryCount), |
| 283 |
]; |
| 284 |
} |
| 285 |
|
| 286 |
public function getListsByIds(array $ids): array |
| 287 |
{ |
| 288 |
acym_arrayToInteger($ids); |
| 289 |
if (empty($ids)) { |
| 290 |
return []; |
| 291 |
} |
| 292 |
|
| 293 |
$query = 'SELECT * FROM #__acym_list WHERE id IN ('.implode(', ', $ids).')'; |
| 294 |
|
| 295 |
return acym_loadObjectList($query); |
| 296 |
} |
| 297 |
|
| 298 |
public function getSubscribersCount(array $listsIds): int |
| 299 |
{ |
| 300 |
acym_arrayToInteger($listsIds); |
| 301 |
if (empty($listsIds)) { |
| 302 |
return 0; |
| 303 |
} |
| 304 |
|
| 305 |
$query = 'SELECT COUNT(DISTINCT user.id) |
| 306 |
FROM #__acym_user AS user |
| 307 |
JOIN #__acym_user_has_list AS userList ON user.id = userList.user_id |
| 308 |
WHERE userList.list_id IN ('.implode(",", $listsIds).') AND userList.status = 1 AND user.active = 1'; |
| 309 |
|
| 310 |
if ($this->config->get('require_confirmation', 1) == 1) { |
| 311 |
$query .= ' AND user.confirmed = 1'; |
| 312 |
} |
| 313 |
|
| 314 |
return (int)acym_loadResult($query); |
| 315 |
} |
| 316 |
|
| 317 |
public function getSubscribersForList(array $options = []): array |
| 318 |
{ |
| 319 |
$listIds = $options['listIds'] ?? []; |
| 320 |
$offset = $options['offset'] ?? 0; |
| 321 |
$limit = $options['limit'] ?? 100; |
| 322 |
$status = $options['status'] ?? null; |
| 323 |
$orderBy = $options['orderBy'] ?? ''; |
| 324 |
$orderBySort = $options['orderBySort'] ?? ''; |
| 325 |
$subscribedAfter = $options['subscribed_after'] ?? null; |
| 326 |
$unsubscribedAfter = $options['unsubscribed_after'] ?? null; |
| 327 |
|
| 328 |
if (empty($listIds)) { |
| 329 |
return []; |
| 330 |
} |
| 331 |
|
| 332 |
acym_arrayToInteger($listIds); |
| 333 |
|
| 334 |
$statusCondition = ''; |
| 335 |
if (!is_null($status)) { |
| 336 |
$statusCondition .= ' AND user_list.status = '.intval($status); |
| 337 |
} |
| 338 |
|
| 339 |
if (!empty($subscribedAfter)) { |
| 340 |
$statusCondition .= ' AND user_list.subscription_date > '.acym_escapeDB($subscribedAfter); |
| 341 |
} |
| 342 |
|
| 343 |
if (!empty($unsubscribedAfter)) { |
| 344 |
$statusCondition .= ' AND user_list.unsubscribe_date > '.acym_escapeDB($unsubscribedAfter); |
| 345 |
} |
| 346 |
|
| 347 |
$requestSub = 'SELECT user.*, user_list.status, user_list.subscription_date, user_list.unsubscribe_date FROM #__acym_user AS user'; |
| 348 |
$requestSub .= ' LEFT JOIN #__acym_user_has_list AS user_list ON user.id = user_list.user_id'; |
| 349 |
$requestSub .= ' WHERE user.active = 1 AND user_list.list_id IN ('.implode(', ', $listIds).')'.$statusCondition; |
| 350 |
|
| 351 |
if (!empty($orderBy)) { |
| 352 |
if (empty($orderBySort)) $orderBySort = 'desc'; |
| 353 |
$requestSub .= ' ORDER BY '.acym_secureDBColumn($orderBy).' '.acym_secureDBColumn($orderBySort); |
| 354 |
} |
| 355 |
|
| 356 |
return acym_loadObjectList( |
| 357 |
$requestSub, |
| 358 |
'', |
| 359 |
$offset, |
| 360 |
$limit |
| 361 |
); |
| 362 |
} |
| 363 |
|
| 364 |
public function delete(array $elements, bool $force = false): int |
| 365 |
{ |
| 366 |
if (!$force) { |
| 367 |
$this->onlyManageableLists($elements); |
| 368 |
} |
| 369 |
|
| 370 |
if (empty($elements)) { |
| 371 |
return 0; |
| 372 |
} |
| 373 |
|
| 374 |
foreach ($elements as $id) { |
| 375 |
acym_query('DELETE FROM #__acym_mail_has_list WHERE list_id = '.intval($id)); |
| 376 |
acym_query('DELETE FROM #__acym_user_has_list WHERE list_id = '.intval($id)); |
| 377 |
acym_query('DELETE FROM #__acym_tag WHERE `id_element` = '.intval($id).' AND `type` = "list"'); |
| 378 |
} |
| 379 |
|
| 380 |
return parent::delete($elements); |
| 381 |
} |
| 382 |
|
| 383 |
public function synchDeleteCmsList(int $userId): void |
| 384 |
{ |
| 385 |
$query = 'SELECT * FROM #__acym_list WHERE type = "'.self::LIST_TYPE_FRONT.'" AND cms_user_id = '.intval($userId); |
| 386 |
$listFrontManagement = acym_loadObject($query); |
| 387 |
if (!empty($listFrontManagement)) { |
| 388 |
$this->delete([$listFrontManagement->id]); |
| 389 |
} |
| 390 |
} |
| 391 |
|
| 392 |
public function save(object $list): ?int |
| 393 |
{ |
| 394 |
if (isset($list->tags)) { |
| 395 |
$tags = $list->tags; |
| 396 |
unset($list->tags); |
| 397 |
} |
| 398 |
|
| 399 |
$oldList = null; |
| 400 |
|
| 401 |
if (empty($list->id)) { |
| 402 |
if (empty($list->cms_user_id)) { |
| 403 |
$list->cms_user_id = acym_currentUserId(); |
| 404 |
} |
| 405 |
|
| 406 |
$list->creation_date = acym_date('now', 'Y-m-d H:i:s', false); |
| 407 |
} else { |
| 408 |
$oldList = $this->getOneById($list->id); |
| 409 |
} |
| 410 |
|
| 411 |
if (!empty($list->translation)) { |
| 412 |
if (is_array($list->translation)) $list->translation = json_encode($list->translation); |
| 413 |
} else { |
| 414 |
$list->translation = ''; |
| 415 |
} |
| 416 |
|
| 417 |
foreach ($list as $oneAttribute => $value) { |
| 418 |
if (empty($value)) continue; |
| 419 |
|
| 420 |
$list->$oneAttribute = acym_stripTags($value); |
| 421 |
} |
| 422 |
|
| 423 |
if (empty($list->description)) { |
| 424 |
$list->description = ''; |
| 425 |
} |
| 426 |
|
| 427 |
if (!isset($list->access) && empty($list->id)) { |
| 428 |
$list->access = ''; |
| 429 |
} |
| 430 |
|
| 431 |
if (empty($list->id)) { |
| 432 |
acym_trigger('onAcymBeforeListCreate', [&$list]); |
| 433 |
} else { |
| 434 |
acym_trigger('onAcymBeforeListModify', [&$list]); |
| 435 |
} |
| 436 |
|
| 437 |
$listID = parent::save($list); |
| 438 |
|
| 439 |
if (empty($listID)) { |
| 440 |
return null; |
| 441 |
} |
| 442 |
|
| 443 |
if (isset($tags)) { |
| 444 |
$tagClass = new TagClass(); |
| 445 |
$tagClass->setTags(TagClass::TYPE_LIST, intval($listID), $tags); |
| 446 |
} |
| 447 |
|
| 448 |
$list->id = $listID; |
| 449 |
|
| 450 |
if (is_null($oldList)) { |
| 451 |
acym_trigger('onAcymAfterListCreate', [&$list]); |
| 452 |
} else { |
| 453 |
acym_trigger('onAcymAfterListModify', [&$list, &$oldList]); |
| 454 |
} |
| 455 |
|
| 456 |
return $listID; |
| 457 |
} |
| 458 |
|
| 459 |
public function getAllWithIdName(): array |
| 460 |
{ |
| 461 |
$endQuery = ''; |
| 462 |
if (!acym_isAdmin()) { |
| 463 |
$creatorId = acym_currentUserId(); |
| 464 |
|
| 465 |
$userGroups = acym_getGroupsByUser($creatorId); |
| 466 |
$groupCondition = '(access LIKE "%,'.implode(',%" OR access LIKE "%,', $userGroups).',%")'; |
| 467 |
|
| 468 |
$endQuery = ' AND (cms_user_id = '.intval($creatorId).' OR '.$groupCondition.')'; |
| 469 |
} |
| 470 |
$lists = acym_loadObjectList('SELECT id, name FROM #__acym_list WHERE type = '.acym_escapeDB(self::LIST_TYPE_STANDARD).$endQuery, 'id'); |
| 471 |
|
| 472 |
$listsToReturn = []; |
| 473 |
|
| 474 |
foreach ($lists as $key => $list) { |
| 475 |
$listsToReturn[$key] = $list->name; |
| 476 |
} |
| 477 |
|
| 478 |
return $listsToReturn; |
| 479 |
} |
| 480 |
|
| 481 |
public function getAllForSelect(bool $emptyFirst = true, int $userFrontID = 0, bool $needTranslation = false, bool $needFrontLabel = false): array |
| 482 |
{ |
| 483 |
$groupCondition = ''; |
| 484 |
if (!empty($userFrontID)) { |
| 485 |
$userGroups = acym_getGroupsByUser($userFrontID); |
| 486 |
$groupCondition = ' AND (cms_user_id = '.intval($userFrontID).' OR (access LIKE "%,'.implode(',%" OR access LIKE "%,', $userGroups).',%"))'; |
| 487 |
} |
| 488 |
|
| 489 |
$lists = acym_loadObjectList('SELECT * FROM #__acym_list WHERE type = '.acym_escapeDB(self::LIST_TYPE_STANDARD).$groupCondition, 'id'); |
| 490 |
|
| 491 |
if (acym_isMultilingual() && $needTranslation) { |
| 492 |
$lists = $this->getTranslatedNameDescription($lists); |
| 493 |
} |
| 494 |
|
| 495 |
$return = []; |
| 496 |
|
| 497 |
if ($emptyFirst) { |
| 498 |
$return[''] = acym_translation('ACYM_SELECT_A_LIST'); |
| 499 |
} |
| 500 |
|
| 501 |
foreach ($lists as $key => $list) { |
| 502 |
$return[$key] = $needFrontLabel && !empty($list->display_name) ? $list->display_name : $list->name; |
| 503 |
} |
| 504 |
|
| 505 |
return $return; |
| 506 |
} |
| 507 |
|
| 508 |
public function getAllWithoutManagement(bool $needTranslation = false): array |
| 509 |
{ |
| 510 |
$lists = acym_loadObjectList('SELECT * FROM #__acym_list WHERE `type` = '.acym_escapeDB(self::LIST_TYPE_STANDARD), 'id'); |
| 511 |
|
| 512 |
if (acym_isMultilingual() && $needTranslation) { |
| 513 |
$lists = $this->getTranslatedNameDescription($lists); |
| 514 |
} |
| 515 |
|
| 516 |
return $lists; |
| 517 |
} |
| 518 |
|
| 519 |
public function getTranslatedNameDescription(array $lists): array |
| 520 |
{ |
| 521 |
$currentLanguageTag = acym_getLanguageTag(); |
| 522 |
foreach ($lists as $id => $list) { |
| 523 |
if (empty($list->translation)) continue; |
| 524 |
|
| 525 |
$list->translation = json_decode($list->translation, true); |
| 526 |
if (!empty($list->translation[$currentLanguageTag])) { |
| 527 |
if (!empty($list->translation[$currentLanguageTag]['name'])) { |
| 528 |
$list->name = $list->translation[$currentLanguageTag]['name']; |
| 529 |
} |
| 530 |
|
| 531 |
if (!empty($list->translation[$currentLanguageTag]['display_name'])) { |
| 532 |
$list->display_name = $list->translation[$currentLanguageTag]['display_name']; |
| 533 |
} |
| 534 |
|
| 535 |
if (!empty($list->translation[$currentLanguageTag]['description'])) { |
| 536 |
$list->description = $list->translation[$currentLanguageTag]['description']; |
| 537 |
} |
| 538 |
} |
| 539 |
} |
| 540 |
|
| 541 |
return $lists; |
| 542 |
} |
| 543 |
|
| 544 |
/** |
| 545 |
* Sends the welcome emails attached to the specified lists |
| 546 |
*/ |
| 547 |
public function sendWelcome(int $userID, array $listIDs, bool $forceFront = false): void |
| 548 |
{ |
| 549 |
if (!$forceFront && acym_isAdmin()) { |
| 550 |
return; |
| 551 |
} |
| 552 |
|
| 553 |
acym_arrayToInteger($listIDs); |
| 554 |
if (empty($listIDs)) { |
| 555 |
return; |
| 556 |
} |
| 557 |
|
| 558 |
$messages = acym_loadObjectList('SELECT `welcome_id` FROM #__acym_list WHERE `id` IN ('.implode(',', $listIDs).') AND `active` = 1'); |
| 559 |
|
| 560 |
if (empty($messages)) { |
| 561 |
return; |
| 562 |
} |
| 563 |
|
| 564 |
$alreadySent = []; |
| 565 |
$mailerHelper = new MailerHelper(); |
| 566 |
$mailerHelper->report = (bool)$this->config->get('welcome_message', 1); |
| 567 |
foreach ($messages as $oneMessage) { |
| 568 |
$mailid = $oneMessage->welcome_id; |
| 569 |
if (empty($mailid)) continue; |
| 570 |
|
| 571 |
//We don't send twice the same message |
| 572 |
if (isset($alreadySent[$mailid])) { |
| 573 |
continue; |
| 574 |
} |
| 575 |
|
| 576 |
$mailerHelper->trackEmail = true; |
| 577 |
$mailerHelper->sendOne($mailid, $userID); |
| 578 |
$alreadySent[$mailid] = true; |
| 579 |
} |
| 580 |
} |
| 581 |
|
| 582 |
/** |
| 583 |
* Sends the unsubscribe emails attached to the specified lists |
| 584 |
*/ |
| 585 |
public function sendUnsubscribe(int $userID, array $listIDs): void |
| 586 |
{ |
| 587 |
if (acym_isAdmin()) { |
| 588 |
return; |
| 589 |
} |
| 590 |
|
| 591 |
acym_arrayToInteger($listIDs); |
| 592 |
if (empty($listIDs)) { |
| 593 |
return; |
| 594 |
} |
| 595 |
|
| 596 |
$messages = acym_loadObjectList('SELECT `unsubscribe_id` FROM #__acym_list WHERE `id` IN ('.implode(',', $listIDs).') AND `active` = 1'); |
| 597 |
|
| 598 |
if (empty($messages)) { |
| 599 |
return; |
| 600 |
} |
| 601 |
|
| 602 |
$alreadySent = []; |
| 603 |
$mailerHelper = new MailerHelper(); |
| 604 |
$mailerHelper->report = (bool)$this->config->get('unsub_message', 1); |
| 605 |
foreach ($messages as $oneMessage) { |
| 606 |
if (!empty($oneMessage->unsubscribe_id)) { |
| 607 |
$mailid = $oneMessage->unsubscribe_id; |
| 608 |
|
| 609 |
//We don't send twice the same message |
| 610 |
if (isset($alreadySent[$mailid])) { |
| 611 |
continue; |
| 612 |
} |
| 613 |
|
| 614 |
$mailerHelper->trackEmail = true; |
| 615 |
$mailerHelper->sendOne($mailid, $userID); |
| 616 |
$alreadySent[$mailid] = true; |
| 617 |
} |
| 618 |
} |
| 619 |
} |
| 620 |
|
| 621 |
public function addDefaultList() |
| 622 |
{ |
| 623 |
$listId = acym_loadResult('SELECT `id` FROM #__acym_list LIMIT 1'); |
| 624 |
if (empty($listId)) { |
| 625 |
$defaultList = new \stdClass(); |
| 626 |
$defaultList->name = 'Newsletters'; |
| 627 |
$defaultList->color = '#3366ff'; |
| 628 |
$defaultList->description = ''; |
| 629 |
|
| 630 |
$this->save($defaultList); |
| 631 |
} |
| 632 |
} |
| 633 |
|
| 634 |
public function getTotalSubCount(array $ids): int |
| 635 |
{ |
| 636 |
if (empty($ids)) { |
| 637 |
return 0; |
| 638 |
} |
| 639 |
|
| 640 |
acym_arrayToInteger($ids); |
| 641 |
$this->onlyManageableLists($ids); |
| 642 |
$query = 'SELECT COUNT(DISTINCT hasList.user_id) |
| 643 |
FROM #__acym_user_has_list AS hasList |
| 644 |
JOIN #__acym_user AS user |
| 645 |
ON hasList.user_id = user.id |
| 646 |
WHERE hasList.status = 1 |
| 647 |
AND user.active = 1 |
| 648 |
AND hasList.list_id IN ('.implode(',', $ids).')'; |
| 649 |
|
| 650 |
|
| 651 |
if ($this->config->get('require_confirmation', 1) == 1) { |
| 652 |
$query .= ' AND user.confirmed = 1 '; |
| 653 |
} |
| 654 |
|
| 655 |
return intval(acym_loadResult($query)); |
| 656 |
} |
| 657 |
|
| 658 |
/** |
| 659 |
* Get all mails attached to a list |
| 660 |
*/ |
| 661 |
public function getMailsByListId(int $listId): array |
| 662 |
{ |
| 663 |
$mailIds = acym_loadResultArray('SELECT mail_id FROM #__acym_mail_has_list WHERE list_id = '.intval($listId)); |
| 664 |
|
| 665 |
return empty($mailIds) ? [] : $mailIds; |
| 666 |
} |
| 667 |
|
| 668 |
public function getSubscribersCountPerStatusByListIds(array $listIds = []): array |
| 669 |
{ |
| 670 |
$condList = ''; |
| 671 |
if (!empty($listIds)) { |
| 672 |
acym_arrayToInteger($listIds); |
| 673 |
$condList = 'AND userList.list_id IN ('.implode(',', $listIds).')'; |
| 674 |
} |
| 675 |
|
| 676 |
/* |
| 677 |
* score: |
| 678 |
* An unconfirmed and active user will have a score of 0 + 1*2 = 2 |
| 679 |
* A confirmed and active user will have a score of 1 + 1*2 = 3 |
| 680 |
* A confirmed and inactive user will have a score of 1 + 0*2 = 1 |
| 681 |
*/ |
| 682 |
$query = 'SELECT userList.list_id, COUNT(userList.user_id) AS users, acyuser.confirmed + acyuser.active*2 AS score |
| 683 |
FROM #__acym_user_has_list AS userList |
| 684 |
JOIN #__acym_user AS acyuser |
| 685 |
ON acyuser.id = userList.user_id |
| 686 |
WHERE userList.status = 1 |
| 687 |
'.$condList.' |
| 688 |
GROUP BY score, userList.list_id'; |
| 689 |
$results = acym_loadObjectList($query); |
| 690 |
|
| 691 |
$confirmationRequired = $this->config->get('require_confirmation', 1); |
| 692 |
$listsUserStats = []; |
| 693 |
foreach ($results as $oneResult) { |
| 694 |
if (!isset($listsUserStats[$oneResult->list_id])) { |
| 695 |
$listsUserStats[$oneResult->list_id] = $this->initList($oneResult->list_id); |
| 696 |
} |
| 697 |
|
| 698 |
// Joomla 4 casts the result into an int |
| 699 |
$oneResult->score = (string)$oneResult->score; |
| 700 |
|
| 701 |
if (in_array($oneResult->score, ['0', '1'])) { |
| 702 |
$listsUserStats[$oneResult->list_id]->inactive_users += $oneResult->users; |
| 703 |
} |
| 704 |
|
| 705 |
if (in_array($oneResult->score, ['0', '2'])) { |
| 706 |
$listsUserStats[$oneResult->list_id]->unconfirmed_users += $oneResult->users; |
| 707 |
} |
| 708 |
|
| 709 |
if ($oneResult->score === '3' || ($confirmationRequired != 1 && $oneResult->score == '2')) { |
| 710 |
$listsUserStats[$oneResult->list_id]->sendable_users += $oneResult->users; |
| 711 |
} |
| 712 |
} |
| 713 |
|
| 714 |
$query = 'SELECT userList.list_id, COUNT(userList.user_id) AS users |
| 715 |
FROM #__acym_user_has_list AS userList |
| 716 |
WHERE userList.status = 0 |
| 717 |
'.$condList.' |
| 718 |
GROUP BY userList.list_id'; |
| 719 |
$unsubscribed = acym_loadObjectList($query); |
| 720 |
|
| 721 |
foreach ($unsubscribed as $oneList) { |
| 722 |
if (!isset($listsUserStats[$oneList->list_id])) { |
| 723 |
$listsUserStats[$oneList->list_id] = $this->initList($oneList->list_id); |
| 724 |
} |
| 725 |
$listsUserStats[$oneList->list_id]->unsubscribed_users = $oneList->users; |
| 726 |
} |
| 727 |
|
| 728 |
return $listsUserStats; |
| 729 |
} |
| 730 |
|
| 731 |
public function getSubscribersEvolutionByListIds(array $listIds): array |
| 732 |
{ |
| 733 |
$condList = ''; |
| 734 |
if (!empty($listIds)) { |
| 735 |
acym_arrayToInteger($listIds); |
| 736 |
$condList = ' AND list_id IN ('.implode(',', $listIds).')'; |
| 737 |
} |
| 738 |
$dateNow = acym_loadResult('SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH)'); |
| 739 |
$queryEvolSub = 'SELECT list_id, COUNT(*) AS newSub FROM #__acym_user_has_list'; |
| 740 |
$queryEvolSub .= ' WHERE subscription_date > '.acym_escapeDB($dateNow).' '.$condList; |
| 741 |
$queryEvolSub .= ' GROUP BY list_id'; |
| 742 |
$evolSubscibers = acym_loadObjectList($queryEvolSub, 'list_id'); |
| 743 |
|
| 744 |
$queryEvolUnsub = 'SELECT list_id, COUNT(*) AS newUnsub FROM #__acym_user_has_list'; |
| 745 |
$queryEvolUnsub .= ' WHERE unsubscribe_date > '.acym_escapeDB($dateNow).' '.$condList; |
| 746 |
$queryEvolUnsub .= ' GROUP BY list_id'; |
| 747 |
$newUnsubscribers = acym_loadObjectList($queryEvolUnsub, 'list_id'); |
| 748 |
|
| 749 |
foreach ($newUnsubscribers as $listId => $oneUnsub) { |
| 750 |
if (empty($evolSubscibers[$listId])) { |
| 751 |
$evolSubscibers[$listId] = new \stdClass(); |
| 752 |
} |
| 753 |
$evolSubscibers[$listId]->newUnsub = $oneUnsub->newUnsub; |
| 754 |
} |
| 755 |
|
| 756 |
return $evolSubscibers; |
| 757 |
} |
| 758 |
|
| 759 |
public function getYearSubEvolutionPerList(int $listId): array |
| 760 |
{ |
| 761 |
// Get next month from 1 year ago |
| 762 |
$month = gmdate('n') + 1; |
| 763 |
$year = gmdate('Y') - 1; |
| 764 |
$initDate = $year.'-'.$month.'-01'; |
| 765 |
if ($month == 13) { |
| 766 |
$initDate = gmdate('Y').'-01-01'; |
| 767 |
} |
| 768 |
|
| 769 |
// Get new subscribers per month |
| 770 |
$queryEvolSub = 'SELECT MONTH(subscription_date) as monthSub, DATE_FORMAT(subscription_date,"%Y_%m") AS unit, COUNT(user_id) AS nbUser'; |
| 771 |
$queryEvolSub .= ' FROM `#__acym_user_has_list`'; |
| 772 |
$queryEvolSub .= ' WHERE subscription_date >= "'.$initDate.'" AND list_id = '.$listId; |
| 773 |
$queryEvolSub .= ' GROUP BY unit'; |
| 774 |
$evolSubscibers = acym_loadObjectList($queryEvolSub, 'unit'); |
| 775 |
|
| 776 |
// Get unsubscribers per month |
| 777 |
$queryEvolUnsub = 'SELECT MONTH(unsubscribe_date) as monthUnsub, DATE_FORMAT(unsubscribe_date,"%Y_%m") AS unit, COUNT(user_id) AS nbUser'; |
| 778 |
$queryEvolUnsub .= ' FROM `#__acym_user_has_list`'; |
| 779 |
$queryEvolUnsub .= ' WHERE unsubscribe_date >= "'.$initDate.'" AND list_id = '.$listId; |
| 780 |
$queryEvolUnsub .= ' GROUP BY unit'; |
| 781 |
$evolUnsubscibers = acym_loadObjectList($queryEvolUnsub, 'unit'); |
| 782 |
|
| 783 |
return [ |
| 784 |
'subscribers' => $evolSubscibers, |
| 785 |
'unsubscribers' => $evolUnsubscibers, |
| 786 |
]; |
| 787 |
} |
| 788 |
|
| 789 |
private function initList(int $listId): object |
| 790 |
{ |
| 791 |
$list = new \stdClass(); |
| 792 |
$list->list_id = $listId; |
| 793 |
$list->sendable_users = 0; |
| 794 |
$list->unconfirmed_users = 0; |
| 795 |
$list->inactive_users = 0; |
| 796 |
$list->unsubscribed_users = 0; |
| 797 |
|
| 798 |
return $list; |
| 799 |
} |
| 800 |
|
| 801 |
public function getManageableLists(): array |
| 802 |
{ |
| 803 |
$idCurrentUser = acym_currentUserId(); |
| 804 |
if (empty($idCurrentUser)) { |
| 805 |
return []; |
| 806 |
} |
| 807 |
|
| 808 |
$userGroups = acym_getGroupsByUser($idCurrentUser); |
| 809 |
|
| 810 |
return acym_loadResultArray( |
| 811 |
'SELECT id |
| 812 |
FROM #__acym_list |
| 813 |
WHERE cms_user_id = '.intval($idCurrentUser).' |
| 814 |
OR (access LIKE "%,'.implode(',%" OR access LIKE "%,', $userGroups).',%")' |
| 815 |
); |
| 816 |
} |
| 817 |
|
| 818 |
public function onlyManageableLists(&$elements): void |
| 819 |
{ |
| 820 |
if (acym_isAdmin()) { |
| 821 |
return; |
| 822 |
} |
| 823 |
|
| 824 |
$manageableLists = $this->getManageableLists(); |
| 825 |
$elements = array_intersect($elements, $manageableLists); |
| 826 |
} |
| 827 |
|
| 828 |
public function hasUserAccess(int $listId): bool |
| 829 |
{ |
| 830 |
if (acym_isAdmin() && acym_isAllowed('lists')) { |
| 831 |
return true; |
| 832 |
} |
| 833 |
|
| 834 |
return in_array($listId, $this->getManageableLists()); |
| 835 |
} |
| 836 |
|
| 837 |
public function getfrontManagementList(): int |
| 838 |
{ |
| 839 |
$idCurrentUser = acym_currentUserId(); |
| 840 |
if (empty($idCurrentUser)) { |
| 841 |
return 0; |
| 842 |
} |
| 843 |
|
| 844 |
$frontListId = acym_loadResult('SELECT id FROM #__acym_list WHERE type = '.acym_escapeDB(self::LIST_TYPE_FRONT).' AND cms_user_id = '.intval($idCurrentUser)); |
| 845 |
|
| 846 |
if (!empty($frontListId)) { |
| 847 |
return $frontListId; |
| 848 |
} |
| 849 |
|
| 850 |
$frontList = new \stdClass(); |
| 851 |
$frontList->name = 'frontlist_'.$idCurrentUser; |
| 852 |
$frontList->active = 1; |
| 853 |
$frontList->visible = 0; |
| 854 |
$frontList->cms_user_id = $idCurrentUser; |
| 855 |
$frontList->type = self::LIST_TYPE_FRONT; |
| 856 |
|
| 857 |
return (int)$this->save($frontList); |
| 858 |
} |
| 859 |
|
| 860 |
public function setWelcomeUnsubEmail(array $listIds, int $mailId, string $type): bool |
| 861 |
{ |
| 862 |
if (!in_array($type, [MailClass::TYPE_WELCOME, MailClass::TYPE_UNSUBSCRIBE]) || empty($mailId)) { |
| 863 |
return false; |
| 864 |
} |
| 865 |
|
| 866 |
$column = esc_attr($type).'_id'; |
| 867 |
$columnsList = acym_getColumns('list'); |
| 868 |
if (!in_array($column, $columnsList)) { |
| 869 |
return false; |
| 870 |
} |
| 871 |
|
| 872 |
if (!$this->removeWelcomeUnsubByMailId($mailId, $listIds, $column)) { |
| 873 |
return false; |
| 874 |
} |
| 875 |
|
| 876 |
foreach ($listIds as $listId) { |
| 877 |
$list = $this->getOneById($listId); |
| 878 |
if (empty($list)) { |
| 879 |
return false; |
| 880 |
} |
| 881 |
|
| 882 |
$list->{$column} = $mailId; |
| 883 |
|
| 884 |
$savedListId = $this->save($list); |
| 885 |
if (empty($savedListId)) { |
| 886 |
return false; |
| 887 |
} |
| 888 |
} |
| 889 |
|
| 890 |
return true; |
| 891 |
} |
| 892 |
|
| 893 |
private function removeWelcomeUnsubByMailId(int $mailId, array $listIds, string $column): bool |
| 894 |
{ |
| 895 |
if (empty($mailId)) { |
| 896 |
return false; |
| 897 |
} |
| 898 |
|
| 899 |
$query = 'SELECT * FROM #__acym_list WHERE '.acym_secureDBColumn($column).' = '.$mailId; |
| 900 |
|
| 901 |
if (!empty($listIds)) { |
| 902 |
acym_arrayToInteger($listIds); |
| 903 |
$query .= ' AND `id` NOT IN ('.implode(',', $listIds).')'; |
| 904 |
} |
| 905 |
|
| 906 |
$lists = acym_loadObjectList($query); |
| 907 |
if (empty($lists)) { |
| 908 |
return true; |
| 909 |
} |
| 910 |
|
| 911 |
foreach ($lists as $list) { |
| 912 |
$list->{$column} = null; |
| 913 |
|
| 914 |
$savedListId = $this->save($list); |
| 915 |
if (empty($savedListId)) { |
| 916 |
return false; |
| 917 |
} |
| 918 |
} |
| 919 |
|
| 920 |
return true; |
| 921 |
} |
| 922 |
|
| 923 |
public function getListIdsByWelcomeUnsub(int $mailId, bool $welcome = true): array |
| 924 |
{ |
| 925 |
$type = $welcome ? 'welcome_id' : 'unsubscribe_id'; |
| 926 |
|
| 927 |
$return = acym_loadResultArray('SELECT id FROM #__acym_list WHERE '.$type.' = '.intval($mailId)); |
| 928 |
|
| 929 |
return empty($return) ? [] : $return; |
| 930 |
} |
| 931 |
|
| 932 |
public function getUsersForSummaryModal(int $id, int $offset, int $limit, string $search): array |
| 933 |
{ |
| 934 |
$whereQuery = ' AND user_list.status = 1 AND user.active = 1'; |
| 935 |
|
| 936 |
if (!empty($search)) { |
| 937 |
$search = acym_escapeDB('%'.$search.'%'); |
| 938 |
$whereQuery .= ' AND (user.email LIKE '.$search.' OR user.name LIKE '.$search.' OR user.id LIKE '.$search.') '; |
| 939 |
} |
| 940 |
|
| 941 |
if ($this->config->get('require_confirmation', '1') === '1') { |
| 942 |
$whereQuery .= ' AND user.confirmed = 1'; |
| 943 |
} |
| 944 |
|
| 945 |
$query = 'SELECT user.email, user.name, user.id |
| 946 |
FROM #__acym_user as user |
| 947 |
JOIN #__acym_user_has_list as user_list ON user.id = user_list.user_id |
| 948 |
WHERE user_list.list_id = '.intval($id).$whereQuery.' LIMIT '.intval($offset).', '.intval($limit); |
| 949 |
|
| 950 |
|
| 951 |
return acym_loadObjectList($query, 'id'); |
| 952 |
} |
| 953 |
|
| 954 |
public function getXLists(array $options = []): array |
| 955 |
{ |
| 956 |
$limit = $options['limit'] ?? 10; |
| 957 |
$offset = $options['offset'] ?? 0; |
| 958 |
$filters = $options['filters'] ?? ['type' => self::LIST_TYPE_STANDARD]; |
| 959 |
|
| 960 |
$conditions = []; |
| 961 |
foreach ($filters as $column => $filter) { |
| 962 |
switch ($column) { |
| 963 |
case 'id': |
| 964 |
case 'active': |
| 965 |
case 'visible': |
| 966 |
case 'clean': |
| 967 |
case 'welcome_id': |
| 968 |
case 'unsubscribe_id': |
| 969 |
case 'cms_user_id': |
| 970 |
case 'tracking': |
| 971 |
$conditions[] = acym_secureDBColumn($column).' = '.intval($filter); |
| 972 |
break; |
| 973 |
default: |
| 974 |
$conditions[] = acym_secureDBColumn($column).' LIKE '.acym_escapeDB('%'.$filter.'%'); |
| 975 |
} |
| 976 |
} |
| 977 |
|
| 978 |
$query = 'SELECT * FROM #__acym_list'; |
| 979 |
if (!empty($conditions)) { |
| 980 |
$query .= ' WHERE '.implode(' AND ', $conditions); |
| 981 |
} |
| 982 |
|
| 983 |
return acym_loadObjectList($query, $this->pkey, $offset, $limit); |
| 984 |
} |
| 985 |
|
| 986 |
public function getOneList(): bool |
| 987 |
{ |
| 988 |
$firstList = acym_loadObject('SELECT * FROM #__acym_list WHERE `id` != 1'); |
| 989 |
|
| 990 |
return !empty($firstList); |
| 991 |
} |
| 992 |
|
| 993 |
public function getMostUsedLists(): array |
| 994 |
{ |
| 995 |
return acym_loadObjectList( |
| 996 |
'SELECT list_id |
| 997 |
FROM #__acym_mail_has_list |
| 998 |
GROUP BY list_id |
| 999 |
ORDER BY COUNT(*) DESC |
| 1000 |
LIMIT 5' |
| 1001 |
); |
| 1002 |
} |
| 1003 |
|
| 1004 |
public function getListsSubscribersAndUnsubscribeUsersFromUserHasList(): array |
| 1005 |
{ |
| 1006 |
$query = 'SELECT list_id, |
| 1007 |
SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS subscribers, |
| 1008 |
SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) AS unsubscribed_users |
| 1009 |
FROM `#__acym_user_has_list` |
| 1010 |
GROUP BY list_id'; |
| 1011 |
|
| 1012 |
return acym_loadObjectList($query); |
| 1013 |
} |
| 1014 |
|
| 1015 |
public function updateListsSubscribersAndUnsubscribeUsers(int $listId, int $subscribers, int $unsubscribeUsers, int $new_sub, int $new_unsub): void |
| 1016 |
{ |
| 1017 |
acym_query( |
| 1018 |
'UPDATE `#__acym_list` |
| 1019 |
SET `subscribers` = '.$subscribers.', `unsubscribed_users` = '.$unsubscribeUsers.', `new_sub` = '.$new_sub.', `new_unsub` = '.$new_unsub.' |
| 1020 |
WHERE id = '.$listId |
| 1021 |
); |
| 1022 |
} |
| 1023 |
|
| 1024 |
public function getMostUsedListsByIdsWithSubscribersData(array $ids = []): array |
| 1025 |
{ |
| 1026 |
if (empty($ids)) { |
| 1027 |
return []; |
| 1028 |
} |
| 1029 |
acym_arrayToInteger($ids); |
| 1030 |
$query = 'SELECT name, subscribers, unsubscribed_users, new_sub, new_unsub FROM `#__acym_list` WHERE id IN ('.implode(', ', $ids).')'; |
| 1031 |
|
| 1032 |
return acym_loadObjectList($query); |
| 1033 |
} |
| 1034 |
|
| 1035 |
public function getUserListEntries(array $userIds, array $listIds): array |
| 1036 |
{ |
| 1037 |
if (empty($userIds) || empty($listIds)) { |
| 1038 |
return []; |
| 1039 |
} |
| 1040 |
|
| 1041 |
$userIdsStr = implode(',', array_map('intval', $userIds)); |
| 1042 |
$listIdsStr = implode(',', array_map('intval', $listIds)); |
| 1043 |
|
| 1044 |
$query = 'SELECT * FROM `#__acym_user_has_list` |
| 1045 |
WHERE user_id IN ('.$userIdsStr.') AND list_id IN ('.$listIdsStr.')'; |
| 1046 |
|
| 1047 |
$results = acym_loadObjectList($query); |
| 1048 |
|
| 1049 |
$entries = []; |
| 1050 |
foreach ($results as $row) { |
| 1051 |
$key = $row->user_id.'_'.$row->list_id; |
| 1052 |
$entries[$key] = $row; |
| 1053 |
} |
| 1054 |
|
| 1055 |
return $entries; |
| 1056 |
} |
| 1057 |
} |
| 1058 |
|
