$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($categoryIDs) > 0) {
if ($where != 'where ') {
$where .= ' and ';
}
if ($having != 'having ') {
$having .= ' and ';
}
$where .= " InfoCategoryID in (" . implode(',', $categoryIDs) . ')';
$wherejoins .= "join StoryInfoCategoryTag cat on cat.StoryID=Story.StoryID ";
$hitcounts .= "count(distinct cat.InfoCategoryID) CategoryHitCount,";
$having .= "CategoryHitCount=" . sizeof($categoryIDs);
}
}
//Keyword filter
if (isset($_GET['keywordfilter'])) {
$keywords = naiveStemming(getSafeValues($_GET['keywordfilter']));
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'])) {
//Get IDs for inserted values
$addedEntitiesResult = mysql_query("select InfoEntityID, Entity from InfoEntity where Entity in ('" . implode("','", $addedEntityNames) . "')", $db_conn);
$entityTags = array();
$outputData['entities'] = array();
while ($row = mysql_fetch_array($addedEntitiesResult)) {
$entityTags[$row['InfoEntityID']] = $row['Entity'];
$outputData['entities'][] = array('id' => $row['InfoEntityID'], 'name' => $row['Entity']);
}
foreach (array_keys($entityTags) as $tagID) {
mysql_query("call AddRemoveStoryTag(1, 'entity', {$userID}, INET_ATON('{$ip}'), {$storyID}, {$tagID}, null, null);", $db_conn);
}
}
}
//Insert added keywords
if (isset($_GET['addedkeywords'])) {
$addedKeywordNames = naiveStemming(getSafeValues($_GET['addedkeywords']));
$addedKeywordNames = array_diff($addedKeywordNames, array(''));
if (count($addedKeywordNames) > 0) {
//Insert new values
mysql_query("insert ignore into InfoKeyword (Keyword) values ('" . implode("'),('", $addedKeywordNames) . "')", $db_conn);
//Get IDs for inserted values
$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);
}
