$storycount = '10';
if (isset($_GET['storycount'])) {
$storycount = max(1, min(200, intval($_GET['storycount'])));
}
$hours = '8';
if (isset($_GET['hours'])) {
$hours = max(1, min(30 * 24, intval($_GET['hours'])));
}
$where = "";
$wherejoin = "";
$hitcounts = "";
$having = "";
$useAND = 1;
//Keyword filter
if (isset($_GET['keywordfilter'])) {
$keywords = naiveStemming(getSafeValues(explode(',', $_GET['keywordfilter'])));
if (sizeof($keywords) > 0) {
if (isset($_GET['keywordoperator'])) {
if ($_GET['keywordoperator'] == 'or') {
$useAND = 0;
}
}
$where .= "and InfoKeywordID in (select distinct InfoKeywordID from InfoKeyword where Keyword in ('" . strtolower(implode("','", $keywords)) . "'))";
$wherejoin .= "join StoryInfoKeywordTag keyword on keyword.StoryID=s.StoryID";
if ($useAND) {
$hitcounts .= ",count(distinct keyword.InfoKeywordID) KeywordHitCount";
$having .= "having KeywordHitCount=" . sizeof($keywords);
}
}
}
$sqlQuery = "select\n T.*,\n count(distinct TagID) as LocationCount,\n count(distinct InfoCategoryID) as CategoryCount,\n count(distinct InfoEntityID) as EntityCount,\n count(distinct InfoKeywordID) as KeywordCount\nfrom (\n select\n s.StoryID,\n StartTime as StartTimeRaw,\n ShortDate(StartTime) as StartTime,\n ceil(exp(Importance)) as Popularity,\n MaxGrowth,\n Title,\n CustomTitle\n {$hitcounts}\n from Story s\n left join PendingStoryMerges psm on psm.StoryID2=s.StoryID\n left join StoryCustomTitle sct on sct.StoryID=s.StoryID\n {$wherejoin}\n where\n StartTime > utc_timestamp()-interval {$hours} hour\n and psm.StoryID1 is null\n {$where}\n group by s.StoryID\n {$having}\n order by MaxGrowth desc \n limit {$storycount}\n) T\nleft join StoryInfoCategoryTag c on c.StoryID=T.StoryID\nleft join StoryInfoEntityTag e on e.StoryID=T.StoryID\nleft join StoryInfoKeywordTag k on k.StoryID=T.StoryID\nleft join StoryLocationTag l on l.StoryID=T.StoryID\ngroup by T.StoryID\norder by StartTimeRaw desc;";
if (sizeof($keywords) > 0) {
if ($where != 'where ') {
$where .= ' and ';
}
if ($having != 'having ') {
$having .= ' and ';
}
$where .= " InfoKeywordID in (select InfoKeywordID from InfoKeyword where Keyword in ('" . strtolower(implode("','", $keywords)) . "'))";
$wherejoins .= "join StoryInfoKeywordTag keyword on keyword.StoryID=Story.StoryID ";
$hitcounts .= "count(distinct keyword.InfoKeywordID) KeywordHitCount,";
$having .= "KeywordHitCount=" . sizeof($keywords);
}
}
//Entity filter
if (isset($_GET['entityfilter'])) {
$entities = getSafeValues($_GET['entityfilter']);
if (sizeof($entities) > 0) {
if ($where != 'where ') {
$where .= ' and ';
}
if ($having != 'having ') {
$having .= ' and ';
}
$where .= " InfoEntityID in (select InfoEntityID from InfoEntity where Entity in ('" . strtolower(implode("','", $entities)) . "'))";
$wherejoins .= "join StoryInfoEntityTag entity on entity.StoryID=Story.StoryID ";
$hitcounts .= "count(distinct entity.InfoEntityID) EntityHitCount,";
$having .= "EntityHitCount=" . sizeof($entities);
}
}
//Location filter
if (isset($_GET['locationfilter'])) {
$addedKeywordResult = mysql_query("select InfoKeywordID, Keyword from InfoKeyword where Keyword in ('" . implode("','", $addedKeywordNames) . "')", $db_conn);
$keywordTags = array();
$outputData['keywords'] = array();
while ($row = mysql_fetch_array($addedKeywordResult)) {
$keywordTags[$row['InfoKeywordID']] = $row['Keyword'];
$outputData['keywords'][] = array('id' => $row['InfoKeywordID'], 'name' => $row['Keyword']);
}
foreach (array_keys($keywordTags) as $tagID) {
mysql_query("call AddRemoveStoryTag(1, 'keyword', {$userID}, INET_ATON('{$ip}'), {$storyID}, {$tagID}, null, null);", $db_conn);
}
}
}
//Insert added locations
if (isset($_GET['addedlocationslatitude']) && isset($_GET['addedlocationslongitude'])) {
$addedLongitudes = getSafeValues($_GET['addedlocationslongitude']);
$addedLatitudes = getSafeValues($_GET['addedlocationslatitude']);
if (count($addedLatitudes) != count($addedLongitudes)) {
exit("Lengths of latitude and longiude does not match.");
}
if (count($addedLatitudes) > 0) {
//Insert locations
for ($i = 0; $i < count($addedLatitudes); $i++) {
$addedLatitudes[$i] = round($addedLatitudes[$i], 6);
$addedLongitudes[$i] = round($addedLongitudes[$i], 6);
mysql_query("call AddRemoveStoryTag(1, 'location', {$userID}, INET_ATON('{$ip}'), {$storyID}, null, " . doubleval($addedLongitudes[$i]) . ',' . doubleval($addedLatitudes[$i]) . ');', $db_conn);
}
//Get IDs
$locationIDsResult = mysql_query("select TagID, Longitude, Latitude from StoryLocationTag where StoryID={$storyID} and Longitude in (" . implode(',', $addedLongitudes) . ") and Latitude in (" . implode(',', $addedLatitudes) . ")");
$outputData['locations'] = array();
while ($row = mysql_fetch_array($locationIDsResult)) {
$outputData['locations'][] = array('id' => $row['TagID'], 'longitude' => $row['Longitude'], 'latitude' => $row['Latitude']);