pluginDescription->name = 'Universal filters in automations'; $this->pluginDescription->category = 'User management'; $this->pluginDescription->description = '- Filter AcyMailing subscribers based on any data from your database
- Filter users based on email addresses in a specified text'; } public function getColumnsForSelectedTable() { $defaultSelect = acym_select( [], acym_getVar('string', 'name', ''), acym_getVar('string', 'value', 'user_id'), [ 'class' => 'acym__select', ] ); $table = acym_getVar('string', 'table', ''); if (empty($table)) { echo wp_kses( $defaultSelect, [ 'select' => ['class' => [], 'name' => [], 'id' => []], 'option' => ['value' => [], 'selected' => [], 'disabled' => []], 'optgroup' => ['label' => []], ] ); exit; } $elements = acym_getColumns($table, false, false); if (empty($elements)) { echo wp_kses( $defaultSelect, [ 'select' => ['class' => [], 'name' => [], 'id' => []], 'option' => ['value' => [], 'selected' => [], 'disabled' => []], 'optgroup' => ['label' => []], ] ); exit; } $columns = []; $columns[0] = acym_getVar('string', 'defaultValue', ''); foreach ($elements as $key => $value) { $columns[$value] = $value; } echo wp_kses( acym_select( $columns, acym_getVar('string', 'name', ''), acym_getVar('string', 'value', 'user_id'), [ 'class' => 'acym__select', ] ), [ 'select' => ['class' => [], 'name' => [], 'id' => []], 'option' => ['value' => [], 'selected' => [], 'disabled' => []], 'optgroup' => ['label' => []], ] ); exit; } public function onAcymDeclareConditions(array &$conditions): void { $operator = new OperatorType(); $conditions['classic']['universalfilter'] = new stdClass(); $conditions['classic']['universalfilter']->name = acym_translation('ACYM_SIMPLE_QUERY'); $conditions['classic']['universalfilter']->option = '

'; $firstTable = new stdClass(); $firstTable->text = acym_translation('ACYM_SELECT_TABLE'); $firstTable->value = 0; $tableChoices = []; $tableChoices[] = $firstTable; $tables = acym_getTableList(); foreach ($tables as $oneTable) { $oneChoice = new stdClass(); $oneChoice->text = $oneTable; $oneChoice->value = $oneTable; $tableChoices[] = $oneChoice; } $conditions['classic']['universalfilter']->option .= '

'; $conditions['classic']['universalfilter']->option .= '

'; $conditions['classic']['universalfilter']->option .= acym_translation('ACYM_TABLE_NAME'); $conditions['classic']['universalfilter']->option .= '

'; $conditions['classic']['universalfilter']->option .= '

'; $conditions['classic']['universalfilter']->option .= acym_select( $tableChoices, 'acym_condition[conditions][__numor__][__numand__][universalfilter][conn_table]', null, [ 'class' => 'acym__select', 'acym-automation-reload' => [ [ 'plugin' => __CLASS__, 'trigger' => 'getColumnsForSelectedTable', 'change' => '#universalfilter_column_tochange___numor_____numand__', 'name' => 'acym_condition[conditions][__numor__][__numand__][universalfilter][column]', 'paramFields' => [ 'table' => 'acym_condition[conditions][__numor__][__numand__][universalfilter][conn_table]', 'defaultValue' => 'acym_condition[conditions][__numor__][__numand__][universalfilter][column]', ], ], [ 'plugin' => __CLASS__, 'trigger' => 'getColumnsForSelectedTable', 'change' => '#universalfilter_where_tochange___numor_____numand__', 'name' => 'acym_condition[conditions][__numor__][__numand__][universalfilter][wherefield]', 'paramFields' => [ 'table' => 'acym_condition[conditions][__numor__][__numand__][universalfilter][conn_table]', 'defaultValue' => 'acym_condition[conditions][__numor__][__numand__][universalfilter][wherefield]', ], ], ], ] ); $conditions['classic']['universalfilter']->option .= '

'; $conditions['classic']['universalfilter']->option .= '

'; ob_start(); acym_info(['textShownInTooltip' => 'ACYM_IDENTIFIER_DESC']); $tooltip = ob_get_clean(); $conditions['classic']['universalfilter']->option .= acym_translation('ACYM_IDENTIFIER').$tooltip; $conditions['classic']['universalfilter']->option .= '

'; $conditions['classic']['universalfilter']->option .= '

'; $conditions['classic']['universalfilter']->option .= ''; $conditions['classic']['universalfilter']->option .= '

'; $conditions['classic']['universalfilter']->option .= '

'; ob_start(); acym_info(['textShownInTooltip' => 'ACYM_CONDITION_DESC']); $tooltip = ob_get_clean(); $conditions['classic']['universalfilter']->option .= acym_translation('ACYM_CONDITION').$tooltip; $conditions['classic']['universalfilter']->option .= '

'; $conditions['classic']['universalfilter']->option .= '

'; $conditions['classic']['universalfilter']->option .= ''; $conditions['classic']['universalfilter']->option .= '

'; $conditions['classic']['universalfilter']->option .= '

'; $conditions['classic']['universalfilter']->option .= $operator->display('acym_condition[conditions][__numor__][__numand__][universalfilter][operator]'); $conditions['classic']['universalfilter']->option .= '

'; $conditions['classic']['universalfilter']->option .= '

'; $conditions['classic']['universalfilter']->option .= ''; $conditions['classic']['universalfilter']->option .= '

'; $conditions['classic']['universalfilter']->option .= '

'; $conditions['classic']['sqladvanced'] = new stdClass(); $conditions['classic']['sqladvanced']->name = acym_translation('ACYM_ADVANCED_QUERY'); $conditions['classic']['sqladvanced']->option = '

'; $conditions['classic']['sqladvanced']->option .= '

'; $conditions['classic']['sqladvanced']->option .= acym_select( [ 'email' => 'ACYM_EMAIL', 'cms_id' => 'ACYM_USER_CMSID', 'id' => 'ACYM_USER_ID', ], 'acym_condition[conditions][__numor__][__numand__][sqladvanced][source]', null, ['class' => 'acym__select'], 'value', 'text', false, true ); $conditions['classic']['sqladvanced']->option .= '

'; $operatorIn = new OperatorInType(); $conditions['classic']['sqladvanced']->option .= '

'; $conditions['classic']['sqladvanced']->option .= $operatorIn->display('acym_condition[conditions][__numor__][__numand__][sqladvanced][type]'); $conditions['classic']['sqladvanced']->option .= '

'; $conditions['classic']['sqladvanced']->option .= '

'; $conditions['classic']['sqladvanced']->option .= ''; $conditions['classic']['sqladvanced']->option .= '

'; $conditions['classic']['sqladvanced']->option .= '

'.acym_translation('ACYM_EXTERNAL_SERVER_DESC').'

'; $conditions['classic']['sqladvanced']->option .= '

'; $conditions['classic']['extractaddresses'] = new stdClass(); $conditions['classic']['extractaddresses']->name = acym_translation('ACYM_EXTRACT_ADDRESSES'); $conditions['classic']['extractaddresses']->option = '

'; $conditions['classic']['extractaddresses']->option .= '

'; $conditions['classic']['extractaddresses']->option .= ''; $conditions['classic']['extractaddresses']->option .= '

'; $conditions['classic']['extractaddresses']->option .= '

'; } public function onAcymProcessCondition_universalfilter(&$query, $options, $num, &$conditionNotValid) { $this->processConditionFilter_universalfilter($query, $options, $num); $affectedRows = $query->count(); if (empty($affectedRows)) $conditionNotValid++; } private function processConditionFilter_universalfilter(&$query, $options, $num) { if (empty($options['conn_table']) || !in_array($options['conn_table'], acym_getTableList()) || empty($options['column'])) return; $columns = acym_getColumns($options['conn_table'], false, false); if (empty($columns) || !in_array($options['column'], $columns)) return; $acyOperator = in_array(strtolower($options['column']), ['email', 'e-mail', 'mail', 'e_mail', 'courriel', 'user_email']) ? 'email' : 'cms_id'; $options['conn_table'] = acym_secureDBColumn($options['conn_table']); $options['column'] = acym_secureDBColumn($options['column']); $query->join['universalfilter'.$num] = $options['conn_table'].' AS universalfiltertable'.$num.' ON universalfiltertable'.$num.'.'.$options['column'].' = user.'.$acyOperator; if (!empty($options['wherefield']) && in_array($options['wherefield'], $columns)) { $options['wherevalue'] = acym_replaceDate($options['wherevalue']); $query->where[] = $query->convertQuery('universalfiltertable'.$num, $options['wherefield'], $options['operator'], $options['wherevalue']); } } public function onAcymProcessCondition_sqladvanced(&$query, $options, $num, &$conditionNotValid) { $this->processConditionFilter_sqladvanced($query, $options, $num); $affectedRows = $query->count(); if (empty($affectedRows)) $conditionNotValid++; } private function filterError(&$query, $options, $message) { if (empty($options['countTotal'])) { acym_display(acym_translation($message), 'error'); } $query->where[] = '1 = 0'; } private function processConditionFilter_sqladvanced(&$query, $options, $num) { if (empty($options['query'])) return; $sqlQuery = strtolower($options['query']); if (strpos($sqlQuery, 'select') !== 0) { $this->filterError($query, $options, 'ACYM_WRONG_SQL_QUERY'); return; } $blacklistedTerms = ['truncate', 'drop', 'alter', 'update', 'insert']; foreach ($blacklistedTerms as $blacklistedTerm) { if (strpos($sqlQuery, $blacklistedTerm) !== false) { $this->filterError($query, $options, 'ACYM_WRONG_SQL_QUERY'); return; } } if (!in_array($options['source'], ['id', 'email', 'cms_id'])) $options['source'] = 'cms_id'; $options['query'] = acym_replaceDate($options['query']); $where = 'user.'.$options['source']; $where .= $options['type'] == 'in' ? ' IN ' : ' NOT IN '; // Connecting to the current db if (empty($options['conn_host'])) { $query->where[] = $where.'('.$options['query'].')'; return; } if (empty($options['conn_db']) || empty($options['conn_login']) || empty($options['conn_pwd'])) { $this->filterError($query, $options, 'ACYM_MISSING_PARAMETERS'); return; } // We need to connect to another database // phpcs:ignore WordPress.DB.RestrictedFunctions.mysql_mysqli_connect $conn = @mysqli_connect($options['conn_host'], $options['conn_login'], $options['conn_pwd'], $options['conn_db']); if (empty($conn)) { // phpcs:ignore WordPress.DB.RestrictedFunctions.mysql_mysqli_connect_error $this->filterError($query, $options, acym_translationSprintf('ACYM_ERROR_CONNECTING', $options['conn_host'].' '.$options['conn_db']).': '.mysqli_connect_error()); return; } // phpcs:ignore WordPress.DB.RestrictedFunctions.mysql_mysqli_query $myQuery = mysqli_query($conn, $options['query']); if (!$myQuery) { // phpcs:ignore WordPress.DB.RestrictedFunctions.mysql_mysqli_error $this->filterError($query, $options, acym_translation('ACYM_ERROR').': '.mysqli_error($conn)); return; } $resultArray = []; // phpcs:ignore WordPress.DB.RestrictedFunctions.mysql_mysqli_fetch_row while ($row = mysqli_fetch_row($myQuery)) { $resultArray[] = acym_escapeDB($row[0]); } if (empty($resultArray)) $resultArray = ['"-1"']; $myQuery->close(); $conn->close(); $query->where[] = $where.' ('.implode(',', $resultArray).') '; } public function onAcymProcessCondition_extractaddresses(&$query, $options, $num, &$conditionNotValid) { $this->processConditionFilter_extractaddresses($query, $options, $num); $affectedRows = $query->count(); if (empty($affectedRows)) $conditionNotValid++; } private function processConditionFilter_extractaddresses(&$query, $options, $num) { if (empty($options['text'])) return; preg_match_all('/'.acym_getEmailRegex().'/i', $options['text'], $results); $allEmails = empty($results[0]) ? ['-1'] : $results[0]; $query->where[] = 'user.email IN ("'.implode('","', $allEmails).'")'; } public function onAcymDeclareSummary_conditions(&$automationCondition) { $this->summaryConditionFilters($automationCondition); } private function summaryConditionFilters(&$automationCondition) { if (!empty($automationCondition['universalfilter'])) { $finalText = acym_translationSprintf('ACYM_UNIVERSAL_FILTER_SUMMARY', $automationCondition['universalfilter']['conn_table']); if (!empty($automationCondition['universalfilter']['wherefield'])) { $finalText .= '
'.acym_translationSprintf( 'ACYM_CONDITION_ACY_FIELD_SUMMARY', $automationCondition['universalfilter']['wherefield'], $automationCondition['universalfilter']['operator'], $automationCondition['universalfilter']['wherevalue'] ); } $automationCondition = $finalText; } if (!empty($automationCondition['sqladvanced'])) { $automationCondition = acym_translation('ACYM_ADVANCED_QUERY_SUMMARY'); } if (!empty($automationCondition['extractaddresses'])) { $automationCondition = acym_translation('ACYM_EXTRACT_ADDRESSES_SUMMARY'); } if (!empty($automationCondition['import'])) { $automationCondition = acym_translation('ACYM_IMPORT_FILTER_SUMMARY'); } } public function onAcymDeclareFilters(array &$filters): void { $this->filtersFromConditions($filters); $filters['import'] = new stdClass(); $filters['import']->name = acym_translation('ACYM_IMPORT_DATABASE'); $filters['import']->option = '

'; $filters['import']->option .= '

'.acym_translation('ACYM_IMPORT_DATABASE').'

'; $filters['import']->option .= '

'; $filters['import']->option .= acym_translation('ACYM_TABLE_NAME').'*'; $filters['import']->option .= '

'; // When selecting the table, all the dropdowns will be loaded to let the user select the column he wants $tableSelectRefresh = [ [ 'plugin' => __CLASS__, 'trigger' => 'getColumnsForSelectedTable', 'change' => '#import_where_tochange___numor_____numand__', 'name' => 'acym_action[filters][__numor__][__numand__][import][wherefield]', 'paramFields' => [ 'table' => 'acym_action[filters][__numor__][__numand__][import][conn_table]', 'defaultValue' => 'acym_action[filters][__numor__][__numand__][import][wherefield]', ], ], ]; $columnsAssignmentOptions = ''; $userFields = acym_getColumns('user'); // Show the email field first unset($userFields[array_search('email', $userFields)]); array_unshift($userFields, 'email'); foreach ($userFields as $oneUserField) { if (in_array($oneUserField, ['id', 'key', 'automation'])) continue; $tableSelectRefresh[] = [ 'plugin' => __CLASS__, 'trigger' => 'getColumnsForSelectedTable', 'change' => '#import_'.$oneUserField.'_tochange___numor_____numand__', 'name' => 'acym_action[filters][__numor__][__numand__][import]['.$oneUserField.']', 'paramFields' => [ 'table' => 'acym_action[filters][__numor__][__numand__][import][conn_table]', 'defaultValue' => 'acym_action[filters][__numor__][__numand__][import]['.$oneUserField.']', ], ]; $columnsAssignmentOptions .= '

'; $columnsAssignmentOptions .= '

'; $columnsAssignmentOptions .= $oneUserField.($oneUserField === 'email' ? '*' : ''); $columnsAssignmentOptions .= '

'; $columnsAssignmentOptions .= '

'; $columnsAssignmentOptions .= ''; $columnsAssignmentOptions .= '

'; $columnsAssignmentOptions .= '

'; } $firstTable = new stdClass(); $firstTable->text = acym_translation('ACYM_SELECT_TABLE'); $firstTable->value = 0; $tableChoices = []; $tableChoices[] = $firstTable; $tables = acym_getTableList(); foreach ($tables as $oneTable) { $oneChoice = new stdClass(); $oneChoice->text = $oneTable; $oneChoice->value = $oneTable; $tableChoices[] = $oneChoice; } $filters['import']->option .= '

'; $filters['import']->option .= acym_select( $tableChoices, 'acym_action[filters][__numor__][__numand__][import][conn_table]', null, [ 'class' => 'acym__select', 'acym-automation-reload' => $tableSelectRefresh, ] ); $filters['import']->option .= '

'; $filters['import']->option .= '

'; $filters['import']->option .= acym_translation('ACYM_WHERE'); $filters['import']->option .= '

'; $filters['import']->option .= '

'; $filters['import']->option .= ''; $filters['import']->option .= '

'; $filters['import']->option .= '

'; $operator = new OperatorType(); $filters['import']->option .= $operator->display('acym_action[filters][__numor__][__numand__][import][operator]'); $filters['import']->option .= '

'; $filters['import']->option .= '

'; $filters['import']->option .= ''; $filters['import']->option .= '

'; $filters['import']->option .= '

'.acym_translation('ACYM_FIELD_MATCHING').'

'; $filters['import']->option .= '

'; $filters['import']->option .= $columnsAssignmentOptions; $filters['import']->option .= '

'; } public function onAcymProcessFilterCount_universalfilter(&$query, $options, $num) { $this->onAcymProcessFilter_universalfilter($query, $options, $num); return acym_translationSprintf('ACYM_SELECTED_USERS', $query->count()); } public function onAcymProcessFilter_universalfilter(&$query, $options, $num) { $this->processConditionFilter_universalfilter($query, $options, $num); } public function onAcymProcessFilterCount_sqladvanced(&$query, $options, $num) { $this->onAcymProcessFilter_sqladvanced($query, $options, $num); return acym_translationSprintf('ACYM_SELECTED_USERS', $query->count()); } public function onAcymProcessFilter_sqladvanced(&$query, $options, $num) { $this->processConditionFilter_sqladvanced($query, $options, $num); } public function onAcymProcessFilterCount_extractaddresses(&$query, $options, $num) { $this->onAcymProcessFilter_extractaddresses($query, $options, $num); return acym_translationSprintf('ACYM_SELECTED_USERS', $query->count()); } public function onAcymProcessFilter_extractaddresses(&$query, $options, $num) { $this->processConditionFilter_extractaddresses($query, $options, $num); } public function onAcymProcessFilterCount_import(&$query, $options, $num) { return acym_translation('ACYM_NO_PREVIEW_AVAILABLE'); } public function onAcymProcessFilter_import(&$query, $options, $num) { // Make sure we have the needed information if (empty($options['conn_table']) || empty($options['email']) || !empty($options['countTotal'])) { $query->where[] = '1 = 0'; return; } $columns = acym_getColumns($options['conn_table'], false, false); if (empty($columns)) { $query->where[] = '1 = 0'; return; } $importHelper = new ImportHelper(); $importHelper->tableName = acym_secureDBColumn($options['conn_table']); // Filter the imported users in case the user selected some actions $newFilter = []; $newFilter['query'] = 'SELECT import.`'.acym_secureDBColumn($options['email']).'` FROM '.acym_secureDBColumn($options['conn_table']).' AS import '; $newFilter['query'] .= 'WHERE import.`'.acym_secureDBColumn($options['email']).'` LIKE "%@%"'; $newFilter['type'] = 'in'; $newFilter['source'] = 'email'; if (!empty($options['wherefield']) && in_array($options['wherefield'], $columns)) { $options['wherevalue'] = acym_replaceDate($options['wherevalue']); $newFilter['query'] .= ' AND '.$query->convertQuery('import', $options['wherefield'], $options['operator'], $options['wherevalue']); $importHelper->dbWhere[] = $query->convertQuery('', $options['wherefield'], $options['operator'], $options['wherevalue']); } // Only keep the fields mapping options unset($options['conn_table']); unset($options['wherefield']); unset($options['operator']); unset($options['wherevalue']); $importHelper->fieldsMap = $options; if (!$importHelper->database(true)) { $query->where[] = '1 = 0'; return; } $this->onAcymProcessFilter_sqladvanced($query, $newFilter, $num); } public function onAcymDeclareSummary_filters(&$automationFilter) { $this->summaryConditionFilters($automationFilter); } }