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