/** * Returns a table mapping country codes to page views from the country in each row, for the specified time range. * range is half inclusive : [). * * $site_id site id to work on, or false for all sites, or true for current site in options table. (see not_excluded() doc). * $is_unix_time true if the start and end times are unix time, false for mysql datetime * $start_time timestamp of start time * $end_time timestamp of end time. */ function fs_get_views_per_country_range($site_id, $is_unix_time, $start_time, $end_time) { $fsdb =& fs_get_db_conn(); $hits = fs_hits_table(); $ua = fs_useragents_table(); $archive_countries = fs_archive_countries(); $ranges = fs_archive_ranges(); $not_excluded = not_excluded(); $valid_country_code = "`country_code` IS NOT NULL AND `country_code` != '0'"; if (fs_mysql_newer_than("4.1.14")) { $from_site = fs_get_site_id_query($site_id); $timestamp_between = fs_timestamp_between($is_unix_time, $start_time, $end_time); $select1 = "SELECT `site_id`,`country_code`, count(`country_code`) c\n\t \t\t\tFROM `{$hits}` h, `{$ua}` ua \n\t \t\t\tWHERE h.useragent_id = ua.id AND {$not_excluded} AND {$timestamp_between} AND {$valid_country_code}\n\t \t\t\tGROUP BY `site_id` , `country_code`"; $timerange_between = fs_time_range_between($is_unix_time, $start_time, $end_time); $select2 = "SELECT `site_id`,`country_code` ,`views` AS c FROM `{$archive_countries}` d, `{$ranges}` r " . "WHERE d.range_id = r.range_id AND {$from_site} AND {$timerange_between} AND {$valid_country_code}"; $sql = "SELECT `country_code`, sum( u.c ) c " . "FROM ({$select1} UNION ALL {$select2})\n\t\t\t\t`u` GROUP BY `site_id` , `country_code`\n\t\t\t\tORDER BY c DESC"; } else { $sql = "SELECT `country_code`, count(`country_code`) c\n\t\t\t\t\t\tFROM `{$hits}` h,`{$ua}` ua\n\t\t\t\t\t\tWHERE ua.id = h.useragent_id AND \n\t\t\t\t\t\t{$not_excluded} AND {$valid_country_code}"; $sql .= "AND " . fs_timestamp_between($is_unix_time, $start_time, $end_time); $sql .= " GROUP BY `country_code` ORDER BY c DESC"; } return $fsdb->get_results($sql); }
function fs_create_archive_tables(&$fsdb) { $ranges = fs_archive_ranges(); $sql = "CREATE TABLE IF NOT EXISTS `{$ranges}` (\n\t\t`range_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY " . fs_comment('Range ID') . ",\n\t\t`range_start` DATETIME NOT NULL " . fs_comment('Range start time') . ",\n\t\t`range_end` DATETIME NOT NULL " . fs_comment('Range end time') . ",\n\t\tUNIQUE `ranges index` (`range_start`,`range_end`)\n\t\t)" . fs_comment('Archive ranges table') . fs_engine("InnoDB"); $r = $fsdb->query($sql); if ($r === false) { $fsdb->debug(); return false; } // create baseline range $r = $fsdb->query("INSERT IGNORE INTO `{$ranges}` (`range_id`,`range_start`,`range_end`) VALUES ('1' , '1000-01-01 00:00:00', '1000-01-01 00:00:00')"); if ($r === false) { $fsdb->debug(); return false; } $r = $fsdb->query(fs_get_create_site_archive()); if ($r === false) { $fsdb->debug(); return false; } $r = $fsdb->query(fs_get_create_archive_with_id(fs_archive_pages(), 'url_id', 'Archive for pages')); if ($r === false) { $fsdb->debug(); return false; } $r = $fsdb->query(fs_get_create_archive_with_id(fs_archive_referrers(), 'url_id', 'Archive for referrers')); if ($r === false) { $fsdb->debug(); return false; } $r = $fsdb->query(fs_get_create_archive_with_id(fs_archive_useragents(), 'useragent_id', 'Archive for useragents')); if ($r === false) { $fsdb->debug(); return false; } $countries_archive = fs_archive_countries(); $sql = "CREATE TABLE IF NOT EXISTS `{$countries_archive}` (\n\t\t`range_id` INT NOT NULL " . fs_comment('Range ID') . ",\n\t\t`site_id` INTEGER NOT NULL " . fs_comment('Site ID of this data') . ",\n\t\t`country_code` INTEGER NOT NULL " . fs_comment('Country code for this data') . ",\n\t\t`views` INTEGER NOT NULL " . fs_comment('Number of views from country in time range') . ",\n\t\t`visits` INTEGER NOT NULL " . fs_comment('Number of visits from country in time range') . ",\n\t\tUNIQUE `index` (`range_id`,`site_id`,`country_code`)\n\t\t) " . fs_comment("Countries archive table") . fs_engine("InnoDB"); $r = $fsdb->query($sql); if ($r === false) { $fsdb->debug(); return false; } return true; }
function fs_get_tables_list() { $a = array(fs_version_table(), fs_hits_table(), fs_useragents_table(), fs_urls_table(), fs_excluded_ips_table(), fs_bots_table(), fs_options_table(), fs_sites_table(), fs_archive_ranges(), fs_archive_sites(), fs_archive_pages(), fs_archive_referrers(), fs_archive_useragents(), fs_archive_countries(), fs_users_table(), fs_pending_date_table(), fs_url_metadata_table()); return $a; }