/**
* 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;
}