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

		@@ -1,0 +1,175 @@
	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	+}