acymailing / back / Helpers / AutomationHelper.php

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

167 lines 6.4 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	public function addFlag(int $id, bool $reset = false): void
56	{
57	if (!empty($this->orderBy) \|\| !empty($this->limit)) {
58	$flagQuery = 'UPDATE #__acym_user';
59	$flagQuery .= ' SET automation = CONCAT(automation, "a'.intval($id).'a")';
60	$flagQuery .= ' WHERE id IN (
61	SELECT id FROM (SELECT user.id FROM #__acym_user AS user';
62	if (!empty($this->join)) $flagQuery .= ' JOIN '.implode(' JOIN ', $this->join);
63	if (!empty($this->leftjoin)) $flagQuery .= ' LEFT JOIN '.implode(' LEFT JOIN ', $this->leftjoin);
64	if (!empty($this->where)) $flagQuery .= ' WHERE ('.implode(') AND (', $this->where).')';
65	if (!empty($this->orderBy)) $flagQuery .= ' ORDER BY '.$this->orderBy;
66	if (!empty($this->limit)) $flagQuery .= ' LIMIT '.$this->limit;
67	$flagQuery .= ') tmp);';
68	} else {
69	$flagQuery = 'UPDATE #__acym_user AS user ';
70	if (!empty($this->join)) $flagQuery .= ' JOIN '.implode(' JOIN ', $this->join);
71	if (!empty($this->leftjoin)) $flagQuery .= ' LEFT JOIN '.implode(' LEFT JOIN ', $this->leftjoin);
72	$flagQuery .= ' SET user.automation = CONCAT(user.automation, "a'.intval($id).'a")';
73	if (!empty($this->where)) $flagQuery .= ' WHERE ('.implode(') AND (', $this->where).')';
74	}
75	acym_query($flagQuery);
76
77	$this->join = [];
78	$this->leftjoin = [];
79	$this->where = $reset ? [] : ['user.automation '.($this->excludeSelected ? 'NOT ' : '').'LIKE "%a'.intval($id).'a%"'];
80	$this->orderBy = '';
81	$this->limit = '';
82	}
83
84	public function removeFlag(int $id): void
85	{
86	acym_query(
87	'UPDATE #__acym_user
88	SET `automation` = REPLACE(`automation`, "a'.$id.'a", "")
89	WHERE `automation` LIKE "%a'.$id.'a%"'
90	);
91	}
92
93	public function convertQuery(string $table, string $column, string $operator, $value, string $type = ''): string
94	{
95	$operator = str_replace(['<', '>'], ['<', '>'], $operator);
96
97	if ($operator === 'CONTAINS' \|\| ($type === self::TYPE_PHONE && $operator === '=')) {
98	$operator = 'LIKE';
99	$value = '%'.$value.'%';
100	} elseif ($operator === 'BEGINS') {
101	$operator = 'LIKE';
102	$value = $value.'%';
103	} elseif ($operator === 'END') {
104	$operator = 'LIKE';
105	$value = '%'.$value;
106	} elseif ($operator === 'NOTCONTAINS' \|\| ($type === self::TYPE_PHONE && $operator === '!=')) {
107	$operator = 'NOT LIKE';
108	$value = '%'.$value.'%';
109	} elseif ($operator === 'REGEXP') {
110	if ($value === '') {
111	return '1 = 1';
112	}
113	} elseif ($operator === 'NOT REGEXP') {
114	if ($value === '') {
115	return '0 = 1';
116	}
117	} elseif (!in_array($operator, ['IS NULL', 'IS NOT NULL', 'NOT LIKE', 'LIKE', '=', '!=', '>', '<', '>=', '<='])) {
118	die(acym_translationSprintf('ACYM_UNKNOWN_OPERATOR', $operator));
119	}
120
121	if (strpos($value, '[time]') !== false) {
122	$value = acym_replaceDate($value);
123	$value = date('Y-m-d H:i:s', $value);
124	}
125
126	$value = acym_replaceDateTags($value);
127
128	if (!is_numeric($value) \|\| in_array($operator, ['REGEXP', 'NOT REGEXP', 'NOT LIKE', 'LIKE', '=', '!='])) {
129	$value = acym_escapeDB($value);
130	}
131
132	if (in_array($operator, ['IS NULL', 'IS NOT NULL'])) {
133	$value = '';
134	}
135
136	if (!empty($table)) {
137	$table = acym_secureDBColumn($table).'.';
138	}
139	if ($type === self::TYPE_DATETIME && in_array($operator, ['=', '!='])) {
140	return 'DATE_FORMAT('.$table.'`'.acym_secureDBColumn($column).'`, "%Y-%m-%d") '.$operator.' '.'DATE_FORMAT('.$value.', "%Y-%m-%d")';
141	}
142	if ($type === self::TYPE_TIMESTAMP && in_array($operator, ['=', '!='])) {
143	return 'FROM_UNIXTIME('.$table.'`'.acym_secureDBColumn($column).'`, "%Y-%m-%d") '.$operator.' '.'FROM_UNIXTIME('.$value.', "%Y-%m-%d")';
144	}
145
146	return $table.'`'.acym_secureDBColumn($column).'` '.$operator.' '.$value;
147	}
148
149	public function deleteUnusedEmails(): void
150	{
151	$automationEmails = acym_loadResultArray('SELECT id FROM #__acym_mail WHERE type = "automation"');
152
153	$emailsToDelete = [];
154	foreach ($automationEmails as $email) {
155	$search = '"acy_add_queue":{"mail_id":"'.$email.'"';
156	$action = acym_loadResult('SELECT id FROM #__acym_action WHERE actions LIKE '.acym_escapeDB('%'.$search.'%'));
157
158	if (empty($action)) $emailsToDelete[] = $email;
159	}
160
161	if (!empty($emailsToDelete)) {
162	$mailClass = new MailClass();
163	$mailClass->delete($emailsToDelete);
164	}
165	}
166	}
167