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;
}
/**
* Gets occurrence by Post_id
* @param int $post_id
*/
public function GetByPostID($post_id)
{
$tt2 = new WSAL_Adapters_MySQL_Meta($this->connection);
return self::LoadMultiQuery('SELECT occurrence.* FROM `' . $this->GetTable() . '`AS occurrence
INNER JOIN `' . $tt2->GetTable() . '`AS postMeta ON postMeta.occurrence_id = occurrence.id
and postMeta.name = "PostID"
and postMeta.value = %d
GROUP BY occurrence.id
ORDER BY created_on DESC', array($post_id));
}
/**
* 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);
*/
}
protected function prepareOccurrenceQuery($query)
{
$searchQueryParameters = array();
$searchConditions = array();
$conditions = $query->getConditions();
//BUG: not all conditions are occurence related. maybe it's just a field site_id. need seperate arrays
if (!empty($conditions)) {
$tmp = new WSAL_Adapters_MySQL_Meta($this->connection);
$sWhereClause = "";
foreach ($conditions as $field => $value) {
if (!empty($sWhereClause)) {
$sWhereClause .= " AND ";
}
$sWhereClause .= "name = %s AND value = %s";
$searchQueryParameters[] = $field;
$searchQueryParameters[] = $value;
}
$searchConditions[] = 'id IN (
SELECT DISTINCT occurrence_id
FROM ' . $tmp->GetTable() . '
WHERE ' . $sWhereClause . '
)';
}
//do something with search query parameters and search conditions - give them to the query adapter?
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;
}
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);
}
