← All changes | back/Helpers/AutomationHelper.php +55 -33 10.0.1 → 11.1.0 View file →

		@@ -2,22 +2,25 @@
2	2
3	3	namespace AcyMailing\Helpers;
4	4
5	5	use AcyMailing\Classes\MailClass;
6		-use AcyMailing\Controllers\SegmentsController;
7	6	use AcyMailing\Core\AcymObject;
8	7
9	8	class AutomationHelper extends AcymObject
10	9	{
11		- public $from = ' `#__acym_user` AS `user`';
12		- public $leftjoin = [];
13		- public $join = [];
14		- public $where = [];
15		- public $orderBy = '';
16		- public $groupBy = '';
17		- public $limit = '';
18		- public $excludeSelected = false;
	10	+ const TYPE_PHONE = 'phone';
	11	+ const TYPE_DATETIME = 'datetime';
	12	+ const TYPE_TIMESTAMP = 'timestamp';
19	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	+
20	23	public function getQuery(array $select = []): string
21	24	{
22	25	$query = '';
23	26	if (!empty($select)) {
		@@ -37,21 +40,26 @@
37	40
38	41	return $query;
39	42	}
40	43
41		- public function count()
	44	+ public function count(): int
42	45	{
	46	+ $result = intval(acym_loadResult($this->getQuery(['COUNT(DISTINCT user.id)'])));
	47	+
43	48	if (empty($this->limit)) {
44		- return acym_loadResult($this->getQuery(['COUNT(DISTINCT user.id)']));
	49	+ return $result;
45	50	} else {
46		- $result = acym_loadResult($this->getQuery(['COUNT(DISTINCT user.id)']));
47		-
48	51	return min($result, $this->limit);
49	52	}
50	53	}
51	54
52		- public function addFlag(int $id, bool $reset = false)
	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
53	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
54	62	if (!empty($this->orderBy) || !empty($this->limit)) {
55	63	$flagQuery = 'UPDATE #__acym_user';
56	64	$flagQuery .= ' SET automation = CONCAT(automation, "a'.intval($id).'a")';
57	65	$flagQuery .= ' WHERE id IN (
		@@ -77,44 +85,56 @@
77	85	$this->orderBy = '';
78	86	$this->limit = '';
79	87	}
80	88
81		- public function removeFlag(int $id)
	89	+ public function removeFlag(int $id): void
82	90	{
83		- acym_query('UPDATE #__acym_user SET automation = REPLACE(automation, "a'.intval($id).'a", "") WHERE automation LIKE "%a'.intval($id).'a%"');
	91	+ acym_query(
	92	+ 'UPDATE #__acym_user
	93	+ SET `automation` = REPLACE(`automation`, "a'.$id.'a", "")
	94	+ WHERE `automation` LIKE "%a'.$id.'a%"'
	95	+ );
84	96	}
85	97
86		- public function convertQuery($table, $column, $operator, $value, $type = '')
	98	+ public function convertQuery(string $table, string $column, string $operator, $value, string $type = ''): string
87	99	{
	100	+ //Fix operator issue...
88	101	$operator = str_replace(['&lt;', '&gt;'], ['<', '>'], $operator);
89	102
90		- if ($operator == 'CONTAINS' || ($type == 'phone' && $operator == '=')) {
	103	+ if ($operator === 'CONTAINS' || ($type === self::TYPE_PHONE && $operator === '=')) {
91	104	$operator = 'LIKE';
92	105	$value = '%'.$value.'%';
93		- } elseif ($operator == 'BEGINS') {
	106	+ } elseif ($operator === 'BEGINS') {
94	107	$operator = 'LIKE';
95	108	$value = $value.'%';
96		- } elseif ($operator == 'END') {
	109	+ } elseif ($operator === 'END') {
97	110	$operator = 'LIKE';
98	111	$value = '%'.$value;
99		- } elseif ($operator == 'NOTCONTAINS' || ($type == 'phone' && $operator == '!=')) {
	112	+ } elseif ($operator === 'NOTCONTAINS' || ($type === self::TYPE_PHONE && $operator === '!=')) {
100	113	$operator = 'NOT LIKE';
101	114	$value = '%'.$value.'%';
102		- } elseif ($operator == 'REGEXP') {
103		- if ($value === '') return '1 = 1';
104		- } elseif ($operator == 'NOT REGEXP') {
105		- if ($value === '') return '0 = 1';
	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	+ }
106	123	} elseif (!in_array($operator, ['IS NULL', 'IS NOT NULL', 'NOT LIKE', 'LIKE', '=', '!=', '>', '<', '>=', '<='])) {
107		- die(acym_translationSprintf('ACYM_UNKNOWN_OPERATOR', $operator));
	124	+ die(esc_html(acym_translationSprintf('ACYM_UNKNOWN_OPERATOR', $operator)));
108	125	}
109	126
	127	+ //Is the value a time field?
	128	+ //If so, we replace it properly and we convert it into the right time field
110	129	if (strpos($value, '[time]') !== false) {
111	130	$value = acym_replaceDate($value);
112		- $value = date('Y-m-d H:i:s', $value);
	131	+ $value = gmdate('Y-m-d H:i:s', $value);
113	132	}
114	133
115	134	$value = acym_replaceDateTags($value);
116	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'...
117	137	if (!is_numeric($value) || in_array($operator, ['REGEXP', 'NOT REGEXP', 'NOT LIKE', 'LIKE', '=', '!='])) {
118	138	$value = acym_escapeDB($value);
119	139	}
120	140
		@@ -121,20 +141,22 @@
121	141	if (in_array($operator, ['IS NULL', 'IS NOT NULL'])) {
122	142	$value = '';
123	143	}
124	144
125		- if (!empty($table)) $table = acym_secureDBColumn($table).'.';
126		- if ($type == 'datetime' && in_array($operator, ['=', '!='])) {
127		- return 'DATE_FORMAT('.$table.'`'.acym_secureDBColumn($column).'`, "%Y-%m-%d") '.$operator.' '.'DATE_FORMAT('.$value.', "%Y-%m-%d")';
	145	+ if (!empty($table)) {
	146	+ $table = acym_secureDBColumn($table).'.';
128	147	}
129		- if ($type == 'timestamp' && in_array($operator, ['=', '!='])) {
130		- return 'FROM_UNIXTIME('.$table.'`'.acym_secureDBColumn($column).'`, "%Y-%m-%d") '.$operator.' '.'FROM_UNIXTIME('.$value.', "%Y-%m-%d")';
	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")';
131	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	+ }
132	154
133	155	return $table.'`'.acym_secureDBColumn($column).'` '.$operator.' '.$value;
134	156	}
135	157
136		- public function deleteUnusedEmails()
	158	+ public function deleteUnusedEmails(): void
137	159	{
138	160	$automationEmails = acym_loadResultArray('SELECT id FROM #__acym_mail WHERE type = "automation"');
139	161
140	162	$emailsToDelete = [];