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']; }