← All changes | back/Helpers/AutomationHelper.php +0 -175 11.0.1 → 11.1.1 View file →

		@@ -1,175 +1,0 @@
1		-<?php
2		-
3		-namespace AcyMailing\Helpers;
4		-
5		-use AcyMailing\Classes\MailClass;
6		-use AcyMailing\Core\AcymObject;
7		-
8		-class AutomationHelper extends AcymObject
9		-{
10		- const TYPE_PHONE = 'phone';
11		- const TYPE_DATETIME = 'datetime';
12		- const TYPE_TIMESTAMP = 'timestamp';
13		-
14		- public string $from = ' `#__acym_user` AS `user`';
15		- public array $leftjoin = [];
16		- public array $join = [];
17		- public array $where = [];
18		- public string $orderBy = '';
19		- public string $groupBy = '';
20		- public string $limit = '';
21		- public bool $excludeSelected = false;
22		-
23		- public function getQuery(array $select = []): string
24		- {
25		- $query = '';
26		- if (!empty($select)) {
27		- $query = ' SELECT ';
28		- if (strpos($select[0], 'COUNT') === false) {
29		- $query .= 'DISTINCT ';
30		- }
31		- $query .= implode(', ', $select);
32		- }
33		- if (!empty($this->from)) $query .= ' FROM '.$this->from;
34		- if (!empty($this->join)) $query .= ' JOIN '.implode(' JOIN ', $this->join);
35		- if (!empty($this->leftjoin)) $query .= ' LEFT JOIN '.implode(' LEFT JOIN ', $this->leftjoin);
36		- if (!empty($this->where)) $query .= ' WHERE ('.implode(') AND (', $this->where).')';
37		- if (!empty($this->groupBy)) $query .= ' GROUP BY '.$this->groupBy;
38		- if (!empty($this->orderBy)) $query .= ' ORDER BY '.$this->orderBy;
39		- if (!empty($this->limit)) $query .= ' LIMIT '.$this->limit;
40		-
41		- return $query;
42		- }
43		-
44		- public function count(): int
45		- {
46		- $result = intval(acym_loadResult($this->getQuery(['COUNT(DISTINCT user.id)'])));
47		-
48		- if (empty($this->limit)) {
49		- return $result;
50		- } else {
51		- return min($result, $this->limit);
52		- }
53		- }
54		-
55		- /**
56		- * The main point is to simplify the queries executed on automation actions.
57		- * It also avoids issues when an action modifies the matching users and they don't match anymore (so following actions won't be executed)
58		- */
59		- public function addFlag(int $id, bool $reset = false): void
60		- {
61		- // In MySQL, the ORDER BY and LIMIT are not supported with "IN" or in UPDATE queries... If you know how to optimize it, feel free to do so
62		- if (!empty($this->orderBy) || !empty($this->limit)) {
63		- $flagQuery = 'UPDATE #__acym_user';
64		- $flagQuery .= ' SET automation = CONCAT(automation, "a'.intval($id).'a")';
65		- $flagQuery .= ' WHERE id IN (
66		- SELECT id FROM (SELECT user.id FROM #__acym_user AS user';
67		- if (!empty($this->join)) $flagQuery .= ' JOIN '.implode(' JOIN ', $this->join);
68		- if (!empty($this->leftjoin)) $flagQuery .= ' LEFT JOIN '.implode(' LEFT JOIN ', $this->leftjoin);
69		- if (!empty($this->where)) $flagQuery .= ' WHERE ('.implode(') AND (', $this->where).')';
70		- if (!empty($this->orderBy)) $flagQuery .= ' ORDER BY '.$this->orderBy;
71		- if (!empty($this->limit)) $flagQuery .= ' LIMIT '.$this->limit;
72		- $flagQuery .= ') tmp);';
73		- } else {
74		- $flagQuery = 'UPDATE #__acym_user AS user ';
75		- if (!empty($this->join)) $flagQuery .= ' JOIN '.implode(' JOIN ', $this->join);
76		- if (!empty($this->leftjoin)) $flagQuery .= ' LEFT JOIN '.implode(' LEFT JOIN ', $this->leftjoin);
77		- $flagQuery .= ' SET user.automation = CONCAT(user.automation, "a'.intval($id).'a")';
78		- if (!empty($this->where)) $flagQuery .= ' WHERE ('.implode(') AND (', $this->where).')';
79		- }
80		- acym_query($flagQuery);
81		-
82		- $this->join = [];
83		- $this->leftjoin = [];
84		- $this->where = $reset ? [] : ['user.automation '.($this->excludeSelected ? 'NOT ' : '').'LIKE "%a'.intval($id).'a%"'];
85		- $this->orderBy = '';
86		- $this->limit = '';
87		- }
88		-
89		- public function removeFlag(int $id): void
90		- {
91		- acym_query(
92		- 'UPDATE #__acym_user
93		- SET `automation` = REPLACE(`automation`, "a'.$id.'a", "")
94		- WHERE `automation` LIKE "%a'.$id.'a%"'
95		- );
96		- }
97		-
98		- public function convertQuery(string $table, string $column, string $operator, $value, string $type = ''): string
99		- {
100		- //Fix operator issue...
101		- $operator = str_replace(['&lt;', '&gt;'], ['<', '>'], $operator);
102		-
103		- if ($operator === 'CONTAINS' || ($type === self::TYPE_PHONE && $operator === '=')) {
104		- $operator = 'LIKE';
105		- $value = '%'.$value.'%';
106		- } elseif ($operator === 'BEGINS') {
107		- $operator = 'LIKE';
108		- $value = $value.'%';
109		- } elseif ($operator === 'END') {
110		- $operator = 'LIKE';
111		- $value = '%'.$value;
112		- } elseif ($operator === 'NOTCONTAINS' || ($type === self::TYPE_PHONE && $operator === '!=')) {
113		- $operator = 'NOT LIKE';
114		- $value = '%'.$value.'%';
115		- } elseif ($operator === 'REGEXP') {
116		- if ($value === '') {
117		- return '1 = 1';
118		- }
119		- } elseif ($operator === 'NOT REGEXP') {
120		- if ($value === '') {
121		- return '0 = 1';
122		- }
123		- } elseif (!in_array($operator, ['IS NULL', 'IS NOT NULL', 'NOT LIKE', 'LIKE', '=', '!=', '>', '<', '>=', '<='])) {
124		- die(esc_html(acym_translationSprintf('ACYM_UNKNOWN_OPERATOR', $operator)));
125		- }
126		-
127		- //Is the value a time field?
128		- //If so, we replace it properly and we convert it into the right time field
129		- if (strpos($value, '[time]') !== false) {
130		- $value = acym_replaceDate($value);
131		- $value = gmdate('Y-m-d H:i:s', $value);
132		- }
133		-
134		- $value = acym_replaceDateTags($value);
135		-
136		- //If it's a number, it does not bother us to db quote if we use = or !=, but if we use < or >=, it could make '20' > '100' as it begins by '2' and 100 begins by '1'...
137		- if (!is_numeric($value) || in_array($operator, ['REGEXP', 'NOT REGEXP', 'NOT LIKE', 'LIKE', '=', '!='])) {
138		- $value = acym_escapeDB($value);
139		- }
140		-
141		- if (in_array($operator, ['IS NULL', 'IS NOT NULL'])) {
142		- $value = '';
143		- }
144		-
145		- if (!empty($table)) {
146		- $table = acym_secureDBColumn($table).'.';
147		- }
148		- if ($type === self::TYPE_DATETIME && in_array($operator, ['=', '!='])) {
149		- return 'DATE_FORMAT('.$table.'`'.acym_secureDBColumn($column).'`, "%Y-%m-%d") '.$operator.' DATE_FORMAT('.$value.', "%Y-%m-%d")';
150		- }
151		- if ($type === self::TYPE_TIMESTAMP && in_array($operator, ['=', '!='])) {
152		- return 'FROM_UNIXTIME('.$table.'`'.acym_secureDBColumn($column).'`, "%Y-%m-%d") '.$operator.' FROM_UNIXTIME('.$value.', "%Y-%m-%d")';
153		- }
154		-
155		- return $table.'`'.acym_secureDBColumn($column).'` '.$operator.' '.$value;
156		- }
157		-
158		- public function deleteUnusedEmails(): void
159		- {
160		- $automationEmails = acym_loadResultArray('SELECT id FROM #__acym_mail WHERE type = "automation"');
161		-
162		- $emailsToDelete = [];
163		- foreach ($automationEmails as $email) {
164		- $search = '"acy_add_queue":{"mail_id":"'.$email.'"';
165		- $action = acym_loadResult('SELECT id FROM #__acym_action WHERE actions LIKE '.acym_escapeDB('%'.$search.'%'));
166		-
167		- if (empty($action)) $emailsToDelete[] = $email;
168		- }
169		-
170		- if (!empty($emailsToDelete)) {
171		- $mailClass = new MailClass();
172		- $mailClass->delete($emailsToDelete);
173		- }
174		- }
175		-}