public function SearchCondition($query) { $condition = $query->getSearchCondition(); if (empty($condition)) { return null; } $searchConditions = array(); $meta = new WSAL_Adapters_MySQL_Meta($this->connection); $occurrence = new WSAL_Adapters_MySQL_Occurrence($this->connection); if (is_numeric($condition) && strlen($condition) == 4) { $searchConditions['sql'] = $occurrence->GetTable() . '.alert_id LIKE %s'; } else { $searchConditions['sql'] = $occurrence->GetTable() . '.id IN ( SELECT DISTINCT occurrence_id FROM ' . $meta->GetTable() . ' WHERE TRIM(BOTH "\\"" FROM value) LIKE %s )'; } $searchConditions['args'] = "%" . $condition . "%"; return $searchConditions; }
/** * Function used in WSAL reporting extension */ public function GetReporting($_siteId, $_userId, $_roleName, $_alertCode, $_startTimestamp, $_endTimestamp, $_nextDate = null, $_limit = 0) { global $wpdb; $tableUsers = $wpdb->users; $_wpdb = $this->connection; // tables $meta = new WSAL_Adapters_MySQL_Meta($this->connection); $tableMeta = $meta->GetTable(); // metadata $occurrence = new WSAL_Adapters_MySQL_Occurrence($this->connection); $tableOcc = $occurrence->GetTable(); // occurrences $user_names = '0'; if (!empty($_userId) && $_userId != "null") { $sql = 'SELECT user_login FROM ' . $tableUsers . ' WHERE find_in_set(ID, @userId) > 0'; $wpdb->query("SET @userId = {$_userId}"); $result = $wpdb->get_results($sql, ARRAY_A); $aUsers = array(); foreach ($result as $item) { $aUsers[] = '"' . $item['user_login'] . '"'; } $user_names = implode(', ', $aUsers); } $conditionDate = !empty($_nextDate) ? ' AND occ.created_on < ' . $_nextDate : ''; $sql = "SELECT DISTINCT\n occ.id, \n occ.alert_id, \n occ.site_id, \n occ.created_on,\n replace(replace(replace((\n SELECT t1.value FROM {$tableMeta} AS t1 WHERE t1.name = 'CurrentUserRoles' AND t1.occurrence_id = occ.id), '[', ''), ']', ''), '\\'', '') AS roles,\n (SELECT replace(t2.value, '\"','') FROM {$tableMeta} as t2 WHERE t2.name = 'ClientIP' AND t2.occurrence_id = occ.id) AS ip,\n (SELECT replace(t3.value, '\"', '') FROM {$tableMeta} as t3 WHERE t3.name = 'UserAgent' AND t3.occurrence_id = occ.id) AS ua,\n COALESCE(\n (SELECT replace(t4.value, '\"', '') FROM {$tableMeta} as t4 WHERE t4.name = 'Username' AND t4.occurrence_id = occ.id),\n (SELECT replace(t5.value, '\"', '') FROM {$tableMeta} as t5 WHERE t5.name = 'CurrentUserID' AND t5.occurrence_id = occ.id)\n ) as user_id\n FROM {$tableOcc} AS occ\n JOIN {$tableMeta} AS meta ON meta.occurrence_id = occ.id\n WHERE\n (@siteId is NULL OR find_in_set(occ.site_id, @siteId) > 0)\n AND (@userId is NULL OR (\n (meta.name = 'CurrentUserID' AND find_in_set(meta.value, @userId) > 0)\n OR (meta.name = 'Username' AND replace(meta.value, '\"', '') IN ({$user_names})) \n ))\n AND (@roleName is NULL OR (meta.name = 'CurrentUserRoles'\n AND replace(replace(replace(meta.value, ']', ''), '[', ''), '\\'', '') REGEXP @roleName\n ))\n AND (@alertCode is NULL OR find_in_set(occ.alert_id, @alertCode) > 0)\n AND (@startTimestamp is NULL OR occ.created_on >= @startTimestamp)\n AND (@endTimestamp is NULL OR occ.created_on <= @endTimestamp)\n {$conditionDate}\n ORDER BY\n created_on DESC\n "; $_wpdb->query("SET @siteId = {$_siteId}"); $_wpdb->query("SET @userId = {$_userId}"); $_wpdb->query("SET @roleName = {$_roleName}"); $_wpdb->query("SET @alertCode = {$_alertCode}"); $_wpdb->query("SET @startTimestamp = {$_startTimestamp}"); $_wpdb->query("SET @endTimestamp = {$_endTimestamp}"); if (!empty($_limit)) { $sql .= " LIMIT {$_limit}"; } $results = $_wpdb->get_results($sql); foreach ($results as $row) { $sql = "SELECT t6.ID FROM {$tableUsers} AS t6 WHERE t6.user_login = \"{$row->user_id}\""; $userId = $wpdb->get_var($sql); if ($userId == null) { $sql = "SELECT t4.ID FROM {$tableUsers} AS t4 WHERE t4.ID = \"{$row->user_id}\""; $userId = $wpdb->get_var($sql); } $row->user_id = $userId; $results['lastDate'] = $row->created_on; } return $results; }
/** * Function used in WSAL reporting extension */ public function GetReporting($_siteId, $_userId, $_roleName, $_alertCode, $_startTimestamp, $_endTimestamp) { global $wpdb; $tableUsers = $wpdb->users; $_wpdb = $this->connection; // tables $meta = new WSAL_Adapters_MySQL_Meta($this->connection); $tableMeta = $meta->GetTable(); // metadata $occurrence = new WSAL_Adapters_MySQL_Occurrence($this->connection); $tableOcc = $occurrence->GetTable(); // occurrences $user_names = '0'; if (!empty($_userId) && $_userId != "null") { $sql = 'SELECT user_login FROM ' . $tableUsers . ' WHERE find_in_set(ID, @userId) > 0'; $wpdb->query("SET @userId = {$_userId}"); $result = $wpdb->get_results($sql, ARRAY_A); $aUsers = array(); foreach ($result as $item) { $aUsers[] = '"' . $item['user_login'] . '"'; } $user_names = implode(', ', $aUsers); } $sql = "SELECT DISTINCT\n occ.id, \n occ.alert_id, \n occ.site_id, \n occ.created_on,\n replace(replace(replace((\n SELECT t1.value FROM {$tableMeta} AS t1 WHERE t1.name = 'CurrentUserRoles' AND t1.occurrence_id = occ.id), '[', ''), ']', ''), '\\'', '') AS roles,\n (SELECT replace(t2.value, '\"','') FROM {$tableMeta} as t2 WHERE t2.name = 'ClientIP' AND t2.occurrence_id = occ.id) AS ip,\n (SELECT replace(t3.value, '\"', '') FROM {$tableMeta} as t3 WHERE t3.name = 'UserAgent' AND t3.occurrence_id = occ.id) AS ua,\n COALESCE(\n (SELECT replace(t4.value, '\"', '') FROM {$tableMeta} as t4 WHERE t4.name = 'Username' AND t4.occurrence_id = occ.id),\n (SELECT replace(t5.value, '\"', '') FROM {$tableMeta} as t5 WHERE t5.name = 'CurrentUserID' AND t5.occurrence_id = occ.id)\n ) as user_id\n FROM {$tableOcc} AS occ\n JOIN {$tableMeta} AS meta ON meta.occurrence_id = occ.id\n WHERE\n (@siteId is NULL OR find_in_set(occ.site_id, @siteId) > 0)\n AND (@userId is NULL OR (\n (meta.name = 'CurrentUserID' AND find_in_set(meta.value, @userId) > 0)\n OR (meta.name = 'Username' AND replace(meta.value, '\"', '') IN ({$user_names})) \n ))\n AND (@roleName is NULL OR (meta.name = 'CurrentUserRoles'\n AND replace(replace(replace(meta.value, ']', ''), '[', ''), '\\'', '') REGEXP @roleName\n ))\n AND (@alertCode is NULL OR find_in_set(occ.alert_id, @alertCode) > 0)\n AND (@startTimestamp is NULL OR occ.created_on >= @startTimestamp)\n AND (@endTimestamp is NULL OR occ.created_on <= @endTimestamp)\n ORDER BY\n site_id, created_on DESC\n "; $_wpdb->query("SET @siteId = {$_siteId}"); $_wpdb->query("SET @userId = {$_userId}"); $_wpdb->query("SET @roleName = {$_roleName}"); $_wpdb->query("SET @alertCode = {$_alertCode}"); $_wpdb->query("SET @startTimestamp = {$_startTimestamp}"); $_wpdb->query("SET @endTimestamp = {$_endTimestamp}"); $results = $_wpdb->get_results($sql); foreach ($results as $row) { $sql = "SELECT t6.ID FROM {$tableUsers} AS t6 WHERE t6.user_login = \"{$row->user_id}\""; $userId = $wpdb->get_var($sql); if ($userId == null) { $sql = "SELECT t4.ID FROM {$tableUsers} AS t4 WHERE t4.ID = \"{$row->user_id}\""; $userId = $wpdb->get_var($sql); } $row->user_id = $userId; } return $results; /* $query = <<<query SELECT DISTINCT occ.id, occ.alert_id, occ.site_id, occ.created_on, replace(replace(replace(replace((select t1.value from $tableMeta as t1 where t1.name = 'CurrentUserRoles' and t1.occurrence_id = occ.id), '[', ''), ']', ''), '"', ''), '\\'', '') as roles, (select replace(t2.value, '"','') from $tableMeta as t2 where t2.name = 'ClientIP' and t2.occurrence_id = occ.id) as ip, (select replace(t3.value, '"', '') from $tableMeta as t3 where t3.name = 'UserAgent' and t3.occurrence_id = occ.id) as ua, COALESCE( (select t6.ID from $tableUsers as t6 where t6.user_login = (select replace(t7.value, '"', '') from $tableMeta as t7 where t7.name = 'Username' and t7.occurrence_id = occ.id)), (select t4.ID from $tableUsers as t4 where t4.ID = (select t5.value from $tableMeta as t5 where t5.name = 'CurrentUserID' and t5.occurrence_id = occ.id)) ) as user_id FROM $tableOcc as occ JOIN $tableMeta as meta on meta.occurrence_id = occ.id WHERE (@siteId is null or find_in_set(occ.site_id, @siteId) > 0) and (@userId is null or ( (meta.name = 'CurrentUserID' and find_in_set(meta.value, @userId) > 0) or (meta.name = 'Username' and replace(meta.value, '"', '') in (select user_login from $tableUsers where find_in_set(ID, @userId) > 0)) )) and (@roleName is null or (meta.name = 'CurrentUserRoles' and replace(replace(replace(replace(meta.value, '"', ''), ']', ''), '[', ''), '\\'', '') REGEXP @roleName )) and (@alertCode is null or find_in_set(occ.alert_id, @alertCode) > 0) and (@startTimestamp is null or occ.created_on >= @startTimestamp) and (@endTimestamp is null or occ.created_on <= @endTimestamp) order by site_id, created_on DESC; query; //#! Set variables first $_wpdb->query("SET @siteId = $_siteId"); $_wpdb->query("SET @userId = $_userId"); $_wpdb->query("SET @roleName = $_roleName"); $_wpdb->query("SET @alertCode = $_alertCode"); $_wpdb->query("SET @startTimestamp = $_startTimestamp"); $_wpdb->query("SET @endTimestamp = $_endTimestamp"); //#! Then run query return $_wpdb->get_results($query); */ }
public function MigrateBack() { global $wpdb; $_wpdb = $this->getConnection(); // Load data Occurrences from External DB $occurrence = new WSAL_Adapters_MySQL_Occurrence($_wpdb); if (!$occurrence->IsInstalled()) { die("No alerts to import"); } $sql = 'SELECT * FROM ' . $occurrence->GetTable(); $occurrences = $_wpdb->get_results($sql, ARRAY_A); // Insert data to WP $occurrenceWP = new WSAL_Adapters_MySQL_Occurrence($wpdb); $sql = 'INSERT INTO ' . $occurrenceWP->GetWPTable() . ' (site_id, alert_id, created_on, is_read, is_migrated) VALUES '; foreach ($occurrences as $entry) { $sql .= '(' . $entry['site_id'] . ', ' . $entry['alert_id'] . ', ' . $entry['created_on'] . ', ' . $entry['is_read'] . ', 1), '; } $sql = rtrim($sql, ", "); $wpdb->query($sql); // Load data Meta from External DB $meta = new WSAL_Adapters_MySQL_Meta($_wpdb); if (!$meta->IsInstalled()) { die("No alerts to import"); } $sql = 'SELECT * FROM ' . $meta->GetTable(); $metadata = $_wpdb->get_results($sql, ARRAY_A); // Insert data to WP $metaWP = new WSAL_Adapters_MySQL_Meta($wpdb); $sql = 'INSERT INTO ' . $metaWP->GetWPTable() . ' (occurrence_id, name, value) VALUES '; foreach ($metadata as $entry) { $sql .= '(' . $entry['occurrence_id'] . ', \'' . $entry['name'] . '\', \'' . $entry['value'] . '\'), '; } $sql = rtrim($sql, ", "); $wpdb->query($sql); }