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