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

		@@ -1,166 +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		- 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(['&lt;', '&gt;'], ['<', '>'], $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		-}