public static function init($_filters = '')
{
// Reset MySQL timezone settings, our dates and times are recorded using WP settings
wp_slimstat::$wpdb->query("SET @@session.time_zone = '+00:00'");
date_default_timezone_set('UTC');
// Decimal and thousand separators
if (wp_slimstat::$options['use_european_separators'] == 'no') {
self::$formats['decimal'] = '.';
self::$formats['thousand'] = ',';
}
// Use WordPress' settings for date and time format
self::$formats['date_format'] = get_option('date_format', 'd-m-Y');
self::$formats['time_format'] = get_option('time_format', 'd-m-Y');
// Filters are defined as: browser equals Chrome|country starts_with en
if (!is_string($_filters) || empty($_filters)) {
$_filters = '';
}
// List of supported filters and their friendly names
self::$filter_names = array('no_filter_selected_1' => ' ', 'browser' => __('Browser', 'wp-slimstat'), 'country' => __('Country Code', 'wp-slimstat'), 'ip' => __('IP Address', 'wp-slimstat'), 'searchterms' => __('Search Terms', 'wp-slimstat'), 'language' => __('Language Code', 'wp-slimstat'), 'platform' => __('Operating System', 'wp-slimstat'), 'resource' => __('Permalink', 'wp-slimstat'), 'domain' => __('Domain', 'wp-slimstat'), 'referer' => __('Referer', 'wp-slimstat'), 'user' => __('Visitor\'s Name', 'wp-slimstat'), 'page_performance' => __('Page Speed', 'wp-slimstat'), 'no_filter_selected_2' => ' ', 'no_filter_selected_3' => __('-- Advanced filters --', 'wp-slimstat'), 'plugins' => __('Browser Capabilities', 'wp-slimstat'), 'version' => __('Browser Version', 'wp-slimstat'), 'type' => __('Browser Type', 'wp-slimstat'), 'user_agent' => __('User Agent', 'wp-slimstat'), 'colordepth' => __('Color Depth', 'wp-slimstat'), 'css_version' => __('CSS Version', 'wp-slimstat'), 'notes' => __('Pageview Attributes', 'wp-slimstat'), 'server_latency' => __('Server Latency', 'wp-slimstat'), 'outbound_resource' => __('Outbound Link', 'wp-slimstat'), 'author' => __('Post Author', 'wp-slimstat'), 'category' => __('Post Category ID', 'wp-slimstat'), 'other_ip' => __('Originating IP', 'wp-slimstat'), 'content_type' => __('Resource Content Type', 'wp-slimstat'), 'content_id' => __('Resource ID', 'wp-slimstat'), 'resolution' => __('Screen Resolution', 'wp-slimstat'), 'visit_id' => __('Visit ID', 'wp-slimstat'), 'hour' => __('Hour', 'wp-slimstat'), 'day' => __('Day', 'wp-slimstat'), 'month' => __('Month', 'wp-slimstat'), 'year' => __('Year', 'wp-slimstat'), 'interval' => __('days', 'wp-slimstat'), 'direction' => __('Order Direction', 'wp-slimstat'), 'limit_results' => __('Limit Results', 'wp-slimstat'), 'start_from' => __('Start From', 'wp-slimstat'), 'strtotime' => 0);
// Hook for the... filters
$_filters = apply_filters('slimstat_db_pre_filters', $_filters);
// Normalize the input (filters)
self::$filters_normalized = self::parse_filters($_filters);
// Hook for the array of normalized filters
self::$filters_normalized = apply_filters('slimstat_db_filters_normalized', self::$filters_normalized, $_filters);
if (empty(self::$filters_normalized['date']['interval'])) {
if (!empty(self::$filters_normalized['date']['hour'])) {
self::$filters_normalized['utime']['start'] = mktime(self::$filters_normalized['date']['hour'], 0, 0, !empty(self::$filters_normalized['date']['month']) ? self::$filters_normalized['date']['month'] : date_i18n('n'), !empty(self::$filters_normalized['date']['day']) ? self::$filters_normalized['date']['day'] : date_i18n('j'), !empty(self::$filters_normalized['date']['year']) ? self::$filters_normalized['date']['year'] : date_i18n('Y'));
self::$filters_normalized['utime']['end'] = self::$filters_normalized['utime']['start'] + 3599;
self::$filters_normalized['utime']['type'] = 'H';
} else {
if (!empty(self::$filters_normalized['date']['day'])) {
self::$filters_normalized['utime']['start'] = mktime(0, 0, 0, !empty(self::$filters_normalized['date']['month']) ? self::$filters_normalized['date']['month'] : date_i18n('n'), self::$filters_normalized['date']['day'], !empty(self::$filters_normalized['date']['year']) ? self::$filters_normalized['date']['year'] : date_i18n('Y'));
self::$filters_normalized['utime']['end'] = self::$filters_normalized['utime']['start'] + 86399;
self::$filters_normalized['utime']['type'] = 'd';
} else {
if (!empty(self::$filters_normalized['date']['year']) && empty(self::$filters_normalized['date']['month'])) {
self::$filters_normalized['utime']['start'] = mktime(0, 0, 0, 1, 1, self::$filters_normalized['date']['year']);
self::$filters_normalized['utime']['end'] = mktime(0, 0, 0, 1, 1, self::$filters_normalized['date']['year'] + 1) - 1;
self::$filters_normalized['utime']['type'] = 'Y';
} else {
self::$filters_normalized['utime']['start'] = mktime(0, 0, 0, !empty(self::$filters_normalized['date']['month']) ? self::$filters_normalized['date']['month'] : date_i18n('n'), 1, !empty(self::$filters_normalized['date']['year']) ? self::$filters_normalized['date']['year'] : date_i18n('Y'));
self::$filters_normalized['utime']['end'] = strtotime((!empty(self::$filters_normalized['date']['year']) ? self::$filters_normalized['date']['year'] : date_i18n('Y')) . '-' . (!empty(self::$filters_normalized['date']['month']) ? self::$filters_normalized['date']['month'] : date_i18n('n')) . '-01 00:00 +1 month UTC') - 1;
self::$filters_normalized['utime']['type'] = 'm';
}
}
}
} else {
self::$filters_normalized['utime']['type'] = 'interval';
if (self::$filters_normalized['date']['interval'] > 0) {
self::$filters_normalized['utime']['start'] = mktime(0, 0, 0, !empty(self::$filters_normalized['date']['month']) ? self::$filters_normalized['date']['month'] : date_i18n('n'), !empty(self::$filters_normalized['date']['day']) ? self::$filters_normalized['date']['day'] : date_i18n('j'), !empty(self::$filters_normalized['date']['year']) ? self::$filters_normalized['date']['year'] : date_i18n('Y'));
self::$filters_normalized['utime']['end'] = strtotime((!empty(self::$filters_normalized['date']['year']) ? self::$filters_normalized['date']['year'] : date_i18n('Y')) . '-' . (!empty(self::$filters_normalized['date']['month']) ? self::$filters_normalized['date']['month'] : date_i18n('n')) . '-' . (!empty(self::$filters_normalized['date']['day']) ? self::$filters_normalized['date']['day'] : date_i18n('j')) . ' 00:00:00 +' . self::$filters_normalized['date']['interval'] . ' days UTC') - 1;
} else {
// Swap boundaries, if interval is negative
self::$filters_normalized['utime']['start'] = strtotime((!empty(self::$filters_normalized['date']['year']) ? self::$filters_normalized['date']['year'] : date_i18n('Y')) . '-' . (!empty(self::$filters_normalized['date']['month']) ? self::$filters_normalized['date']['month'] : date_i18n('n')) . '-' . (!empty(self::$filters_normalized['date']['day']) ? self::$filters_normalized['date']['day'] : date_i18n('j')) . ' 00:00:00 ' . (self::$filters_normalized['date']['interval'] + 1) . ' days UTC');
self::$filters_normalized['utime']['end'] = mktime(23, 59, 59, !empty(self::$filters_normalized['date']['month']) ? self::$filters_normalized['date']['month'] : date_i18n('n'), !empty(self::$filters_normalized['date']['day']) ? self::$filters_normalized['date']['day'] : date_i18n('j'), !empty(self::$filters_normalized['date']['year']) ? self::$filters_normalized['date']['year'] : date_i18n('Y'));
}
}
// If end is in the future, set it to now
if (self::$filters_normalized['utime']['end'] > date_i18n('U')) {
self::$filters_normalized['utime']['end'] = date_i18n('U');
}
// If start is after end, set it to first of month
if (self::$filters_normalized['utime']['start'] > self::$filters_normalized['utime']['end']) {
self::$filters_normalized['utime']['start'] = mktime(0, 0, 0, date_i18n('n', self::$filters_normalized['utime']['end']), 1, date_i18n('Y', self::$filters_normalized['utime']['end']));
self::$filters_normalized['date']['hour'] = self::$filters_normalized['date']['day'] = self::$filters_normalized['date']['month'] = self::$filters_normalized['date']['year'] = 0;
}
// Now let's translate our filters into SQL clauses
self::$sql_filters = array('from' => array('browsers' => '', 'screenres' => '', 'content_info' => '', 'outbound' => '', 'all_tables' => '', 'all_other_tables' => ''), 'where' => '', 'where_time_range' => ' AND (t1.dt BETWEEN ' . self::$filters_normalized['utime']['start'] . ' AND ' . self::$filters_normalized['utime']['end'] . ')');
foreach (self::$filters_normalized['columns'] as $a_filter_column => $a_filter_data) {
$a_filter_empty = '0';
// Add-ons can set their own custom filters, which are ignored here
if (strpos($a_filter_column, 'addon_') !== false) {
continue;
}
// Some columns are in separate tables, so we need to join them
switch (self::get_table_identifier($a_filter_column)) {
case 'tb.':
self::$sql_filters['from']['browsers'] = "INNER JOIN {$GLOBALS['wpdb']->base_prefix}slim_browsers tb ON t1.browser_id = tb.browser_id";
break;
case 'tci.':
self::$sql_filters['from']['content_info'] = "INNER JOIN {$GLOBALS['wpdb']->base_prefix}slim_content_info tci ON t1.content_info_id = tci.content_info_id";
break;
case 'tss.':
self::$sql_filters['from']['screenres'] = "LEFT JOIN {$GLOBALS['wpdb']->base_prefix}slim_screenres tss ON t1.screenres_id = tss.screenres_id";
break;
case 'tob.':
self::$sql_filters['from']['outbound'] = "LEFT JOIN {$GLOBALS['wpdb']->prefix}slim_outbound tob ON (t1.id = tob.id)";
break;
default:
}
// Some columns require a special treatment
switch ($a_filter_column) {
case 'ip':
case 'other_ip':
$a_filter_column = "INET_NTOA({$a_filter_column})";
$a_filter_empty = '0.0.0.0';
break;
default:
$a_filter_column = self::get_table_identifier($a_filter_column) . $a_filter_column;
break;
}
switch ($a_filter_data[0]) {
case 'is_not_equal_to':
self::$sql_filters['where'] .= $GLOBALS['wpdb']->prepare(" AND {$a_filter_column} <> %s", $a_filter_data[1]);
break;
case 'contains':
self::$sql_filters['where'] .= $GLOBALS['wpdb']->prepare(" AND {$a_filter_column} LIKE %s", '%' . $a_filter_data[1] . '%');
break;
case 'does_not_contain':
self::$sql_filters['where'] .= $GLOBALS['wpdb']->prepare(" AND {$a_filter_column} NOT LIKE %s", '%' . $a_filter_data[1] . '%');
break;
case 'starts_with':
self::$sql_filters['where'] .= $GLOBALS['wpdb']->prepare(" AND {$a_filter_column} LIKE %s", $a_filter_data[1] . '%');
break;
case 'ends_with':
self::$sql_filters['where'] .= $GLOBALS['wpdb']->prepare(" AND {$a_filter_column} LIKE %s", '%' . $a_filter_data[1]);
break;
case 'sounds_like':
self::$sql_filters['where'] .= $GLOBALS['wpdb']->prepare(" AND SOUNDEX({$a_filter_column}) = SOUNDEX(%s)", $a_filter_data[1]);
break;
case 'is_empty':
self::$sql_filters['where'] .= " AND ({$a_filter_column} = '' OR {$a_filter_column} = '{$a_filter_empty}')";
break;
case 'is_not_empty':
self::$sql_filters['where'] .= " AND {$a_filter_column} <> '' AND {$a_filter_column} <> '{$a_filter_empty}'";
break;
case 'is_greater_than':
self::$sql_filters['where'] .= $GLOBALS['wpdb']->prepare(" AND {$a_filter_column} > %s", $a_filter_data[1]);
break;
case 'is_less_than':
self::$sql_filters['where'] .= $GLOBALS['wpdb']->prepare(" AND {$a_filter_column} < %s", $a_filter_data[1]);
break;
case 'between':
$range = explode(',', $a_filter_data[1]);
self::$sql_filters['where'] .= $GLOBALS['wpdb']->prepare(" AND {$a_filter_column} BETWEEN %s AND %s", $range[0], $range[1]);
break;
case 'matches':
self::$sql_filters['where'] .= $GLOBALS['wpdb']->prepare(" AND {$a_filter_column} REGEXP %s", $a_filter_data[1]);
break;
case 'does_not_match':
self::$sql_filters['where'] .= $GLOBALS['wpdb']->prepare(" AND {$a_filter_column} NOT REGEXP %s", $a_filter_data[1]);
break;
default:
self::$sql_filters['where'] .= $GLOBALS['wpdb']->prepare(" AND {$a_filter_column} = %s", $a_filter_data[1]);
}
}
self::$sql_filters['from']['all_other_tables'] = trim(self::$sql_filters['from']['browsers'] . ' ' . self::$sql_filters['from']['screenres'] . ' ' . self::$sql_filters['from']['content_info'] . ' ' . self::$sql_filters['from']['outbound']);
self::$sql_filters['from']['all_tables'] = "{$GLOBALS['wpdb']->prefix}slim_stats t1 " . self::$sql_filters['from']['all_other_tables'];
}
