acymailing / back / Helpers / AutomationHelper.php

AutomationHelper.php in AcyMailing – An Ultimate Newsletter Plugin and Marketing Automation Solution for WordPress trunk, at back/Helpers/AutomationHelper.php

176 lines 7.2 KB

Raw Download Zip

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(['<', '>'], ['<', '>'], $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	}
176