Esempio n. 1
0
/**
 * 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);
}
Esempio n. 2
0
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;
}
Esempio n. 3
0
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;
}