function fs_get_recent_referers_range($num_limit, $is_unix_time, $start_time, $end_time, $site_id, $order_by = ORDER_BY_FIRST_SEEN, $exclude_internal = true)
{
$fsdb =& fs_get_db_conn();
$num_limit = $fsdb->escape($num_limit);
$hits = fs_hits_table();
$urls = fs_urls_table();
$ua = fs_useragents_table();
$ranges = fs_archive_ranges();
$archive_referrers = fs_archive_referrers();
$order_by_str = '';
switch ($order_by) {
case ORDER_BY_HIGH_COUNT_FIRST:
$order_by_str = "ORDER BY `refcount` DESC";
break;
case ORDER_BY_RECENT_FIRST:
$order_by_str = "ORDER BY `ts` DESC,`referer_id`";
break;
case ORDER_BY_FIRST_SEEN:
$order_by_str = "ORDER BY `add_time` DESC";
break;
}
$and_exclude_internal = $exclude_internal ? "AND u2.site_id IS NULL" : "";
$not_excluded = not_excluded($site_id, 'h');
if (fs_mysql_newer_than("4.1.14")) {
$timestamp_between = fs_timestamp_between($is_unix_time, $start_time, $end_time);
$select1 = "SELECT `site_id`,`referer_id`,COUNT(`referer_id`) `c`,MAX(SUBSTRING(timestamp,1,10)) `ts` \n\t\t\t\t\tFROM `{$hits}` h,`{$ua}` ua \n\t\t\t\t\tWHERE h.useragent_id = ua.id AND {$not_excluded} AND {$timestamp_between}\n\t\t\t\t\tGROUP BY `referer_id`";
$range_between = fs_time_range_between($is_unix_time, $start_time, $end_time);
$from_site = fs_get_site_id_query($site_id);
$select2 = "SELECT `site_id`,`url_id` AS `referer_id`, SUM(`views`) `c`,SUBSTRING(`range_start`,1,10) AS `ts` \n\t\t\t\t\tFROM `{$archive_referrers}` `d`,`{$ranges}` `r` \n\t\t\t\t\tWHERE d.range_id = r.range_id AND {$range_between} AND {$from_site}\n\t\t\t\t\tGROUP BY `url_id`";
$limit = $num_limit ? " LIMIT {$num_limit}" : "";
$sql = "SELECT u2.`add_time`,`url` ,SUM(`c`) `refcount`,`ts`,u2.title\n\t\t\t\tFROM ({$select1} UNION ALL {$select2}) `u`,`{$urls}` `u2` \n\t\t\t\tWHERE u2.id = u.referer_id AND `url` != '' AND `url` != 'unknown' {$and_exclude_internal}\n\t\t\t\tAND u2.search_engine_id IS NULL\n\t\t\t\tGROUP BY `referer_id` \n\t\t\t\t{$order_by_str} {$limit}";
return $fsdb->get_results($sql);
} else {
$timestamp_between = fs_timestamp_between($is_unix_time, $start_time, $end_time);
$sql = "SELECT u2.add_time,`url`,count(url) `refcount`,MAX(SUBSTRING(timestamp,1,10)) `ts`,u2.title\n\t\t\t\t\t\tFROM `{$hits}` h,`{$ua}` ua,`{$urls}` u2 \n\t\t\t\t\t\tWHERE h.referer_id = u2.id AND h.useragent_id = ua.id\n\t\t\t\t\t\tAND {$not_excluded} AND url != 'unknown' AND url != '' \n\t\t\t\t\t\tAND {$timestamp_between} {$and_exclude_internal}\n\t\t\t\t\t\tAND u2.search_engine_id IS NULL";
$sql .= " GROUP BY url {$order_by_str}" . ($num_limit ? " LIMIT {$num_limit}" : "");
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;
}
