/**
* Get a range of releases. used in admin manage list
*
* @param $start
* @param $num
*
* @return array
*/
public function getFailedRange($start, $num)
{
if ($start === false) {
$limit = '';
} else {
$limit = ' LIMIT ' . $start . ',' . $num;
}
return $this->pdo->query("\n\t\t\tSELECT r.*, CONCAT(cp.title, ' > ', c.title) AS category_name\n\t\t\tFROM releases r\n\t\t\tRIGHT JOIN dnzb_failures df ON df.release_id = r.id\n\t\t\tLEFT OUTER JOIN category c ON c.id = r.categoryid\n\t\t\tLEFT OUTER JOIN category cp ON cp.id = c.parentid\n\t\t\tORDER BY postdate DESC" . $limit);
}
public function getRange($start, $num)
{
if ($start === false) {
$limit = '';
} else {
$limit = ' LIMIT ' . $num . ' OFFSET ' . $start;
}
return $this->pdo->query('SELECT * FROM bookinfo ORDER BY createddate DESC' . $limit);
}
/**
* @param array $excludedcats
*
* @return array
*/
public function getForMenu($excludedcats = [])
{
$ret = [];
$exccatlist = '';
if (count($excludedcats) > 0) {
$exccatlist = ' AND id NOT IN (' . implode(',', $excludedcats) . ')';
}
$arr = $this->pdo->query(sprintf('SELECT * FROM category WHERE status = %d %s', Category::STATUS_ACTIVE, $exccatlist), true, nZEDb_CACHE_EXPIRY_LONG);
foreach ($arr as $a) {
if ($a['parentid'] == '') {
$ret[] = $a;
}
}
foreach ($ret as $key => $parent) {
$subcatlist = [];
$subcatnames = [];
foreach ($arr as $a) {
if ($a['parentid'] == $parent['id']) {
$subcatlist[] = $a;
$subcatnames[] = $a['title'];
}
}
if (count($subcatlist) > 0) {
array_multisort($subcatnames, SORT_ASC, $subcatlist);
$ret[$key]['subcatlist'] = $subcatlist;
} else {
unset($ret[$key]);
}
}
return $ret;
}
/**
* Match added comments to releases.
*
* @access protected
*/
protected function matchComments()
{
$res = $this->pdo->query('
SELECT r.id, r.nzb_guid
FROM releases r
INNER JOIN release_comments rc ON rc.nzb_guid = r.nzb_guid
WHERE rc.releaseid = 0');
$found = count($res);
if ($found > 0) {
foreach ($res as $row) {
$this->pdo->queryExec(sprintf("UPDATE release_comments SET releaseid = %d WHERE nzb_guid = %s", $row['id'], $this->pdo->escapeString($row['nzb_guid'])));
$this->pdo->queryExec(sprintf('UPDATE releases SET comments = comments + 1 WHERE id = %d', $row['id']));
}
if (nZEDb_ECHOCLI) {
echo '(Sharing) Matched ' . $found . ' comments.' . PHP_EOL;
}
}
// Update first time seen.
$siteTimes = $this->pdo->queryDirect('SELECT createddate, siteid FROM release_comments WHERE createddate > \'2005-01-01\' GROUP BY siteid ORDER BY createddate ASC');
if ($siteTimes instanceof \Traversable && $siteTimes->rowCount()) {
foreach ($siteTimes as $site) {
$this->pdo->queryExec(sprintf('UPDATE sharing_sites SET first_time = %s WHERE site_guid = %s', $this->pdo->escapeString($site['createddate']), $this->pdo->escapeString($site['siteid'])));
}
}
}
/**
* @param $cat
* @param $start
* @param $num
* @param $orderby
* @param int $maxage
* @param array $excludedcats
*/
public function getGamesRange($cat, $start, $num, $orderby, $maxage = -1, $excludedcats = [])
{
$browseby = $this->getBrowseBy();
$catsrch = '';
if (count($cat) > 0 && $cat[0] != -1) {
$catsrch = (new Category(['Settings' => $this->pdo]))->getCategorySearch($cat);
}
if ($maxage > 0) {
$maxage = sprintf(' AND r.postdate > NOW() - INTERVAL %d DAY ', $maxage);
} else {
$maxage = '';
}
$exccatlist = "";
if (count($excludedcats) > 0) {
$exccatlist = " AND r.categoryid NOT IN (" . implode(",", $excludedcats) . ")";
}
$order = $this->getGamesOrder($orderby);
$games = $this->pdo->queryCalc(sprintf("\n\t\t\t\tSELECT SQL_CALC_FOUND_ROWS con.id,\n\t\t\t\t\tGROUP_CONCAT(r.id ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_id\n\t\t\t\tFROM gamesinfo con\n\t\t\t\tLEFT JOIN releases r ON con.id = r.gamesinfo_id\n\t\t\t\tWHERE r.nzbstatus = 1\n\t\t\t\tAND con.title != ''\n\t\t\t\tAND con.cover = 1\n\t\t\t\tAND r.passwordstatus %s\n\t\t\t\tAND %s %s %s %s\n\t\t\t\tGROUP BY con.id\n\t\t\t\tORDER BY %s %s %s", Releases::showPasswords($this->pdo), $browseby, $catsrch, $maxage, $exccatlist, $order[0], $order[1], $start === false ? '' : ' LIMIT ' . $num . ' OFFSET ' . $start), true, nZEDb_CACHE_EXPIRY_MEDIUM);
$gameIDs = $releaseIDs = false;
if (is_array($games['result'])) {
foreach ($games['result'] as $game => $id) {
$gameIDs[] = $id['id'];
$releaseIDs[] = $id['grp_release_id'];
}
}
$return = $this->pdo->query(sprintf("\n\t\t\t\tSELECT\n\t\t\t\t\tGROUP_CONCAT(r.id ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_id,\n\t\t\t\t\tGROUP_CONCAT(r.rarinnerfilecount ORDER BY r.postdate DESC SEPARATOR ',') as grp_rarinnerfilecount,\n\t\t\t\t\tGROUP_CONCAT(r.haspreview ORDER BY r.postdate DESC SEPARATOR ',') AS grp_haspreview,\n\t\t\t\t\tGROUP_CONCAT(r.passwordstatus ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_password,\n\t\t\t\t\tGROUP_CONCAT(r.guid ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_guid,\n\t\t\t\t\tGROUP_CONCAT(rn.releaseid ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_nfoid,\n\t\t\t\t\tGROUP_CONCAT(g.name ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_grpname,\n\t\t\t\t\tGROUP_CONCAT(r.searchname ORDER BY r.postdate DESC SEPARATOR '#') AS grp_release_name,\n\t\t\t\t\tGROUP_CONCAT(r.postdate ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_postdate,\n\t\t\t\t\tGROUP_CONCAT(r.size ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_size,\n\t\t\t\t\tGROUP_CONCAT(r.totalpart ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_totalparts,\n\t\t\t\t\tGROUP_CONCAT(r.comments ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_comments,\n\t\t\t\t\tGROUP_CONCAT(r.grabs ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_grabs,\n\t\t\t\t\tGROUP_CONCAT(df.failed ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_failed,\n\t\t\t\tcon.*, YEAR (con.releasedate) as year, r.gamesinfo_id, g.name AS group_name,\n\t\t\t\trn.releaseid AS nfoid\n\t\t\t\tFROM releases r\n\t\t\t\tLEFT OUTER JOIN groups g ON g.id = r.group_id\n\t\t\t\tLEFT OUTER JOIN release_nfos rn ON rn.releaseid = r.id\n\t\t\t\tLEFT OUTER JOIN dnzb_failures df ON df.release_id = r.id\n\t\t\t\tINNER JOIN gamesinfo con ON con.id = r.gamesinfo_id\n\t\t\t\tWHERE con.id IN (%s)\n\t\t\t\tAND r.id IN (%s)\n\t\t\t\tAND %s\n\t\t\t\tGROUP BY con.id\n\t\t\t\tORDER BY %s %s", is_array($gameIDs) ? implode(',', $gameIDs) : -1, is_array($releaseIDs) ? implode(',', $releaseIDs) : -1, $catsrch, $order[0], $order[1]), true, nZEDb_CACHE_EXPIRY_MEDIUM);
if (!empty($return)) {
$return[0]['_totalcount'] = isset($games['total']) ? $games['total'] : 0;
}
return $return;
}
/**
* @param bool $activeOnly
*
* @return array
*/
public function getGenres($activeOnly = false)
{
if ($activeOnly) {
return $this->pdo->query("SELECT musicgenre.* FROM musicgenre INNER JOIN (SELECT DISTINCT musicgenreid FROM musicinfo) x ON x.musicgenreid = musicgenre.id ORDER BY title");
} else {
return $this->pdo->query("SELECT * FROM musicgenre ORDER BY title");
}
}
/**
* @param bool $activeOnly
*
* @return array
*/
public function getGenres($activeOnly = false)
{
if ($activeOnly) {
return $this->pdo->query("\n\t\t\t\tSELECT ge.*\n\t\t\t\tFROM genres ge\n\t\t\t\tINNER JOIN\n\t\t\t\t(\n\t\t\t\t\tSELECT DISTINCT genre_id\n\t\t\t\t\tFROM musicinfo\n\t\t\t\t) x ON x.genre_id = ge.id\n\t\t\t\tWHERE ge.type = 3000\n\t\t\t\tORDER BY title");
} else {
return $this->pdo->query("\n\t\t\t\tSELECT * FROM genres\n\t\t\t\tWHERE type = 3000\n\t\t\t\tORDER BY title");
}
}
public function getCommentsForUserRange($uid, $start, $num)
{
if ($start === false) {
$limit = '';
} else {
$limit = " LIMIT {$num} OFFSET {$start}";
}
return $this->pdo->query(sprintf("\n\t\t\t\tSELECT release_comments.*\n\t\t\t\tFROM release_comments\n\t\t\t\tWHERE user_id = %d\n\t\t\t\tORDER BY release_comments.createddate DESC %s", $uid, $limit));
}
/**
* Retrieves all info for a specific AniDB ID
*
* @param int $anidbID
* @return array|boolean
*/
public function getAnimeInfo($anidbID)
{
$animeInfo = $this->pdo->query(sprintf('SELECT at.anidbid, at.lang, at.title,
ai.startdate, ai.enddate, ai.updated, ai.related, ai.creators, ai.description,
ai.rating, ai.picture, ai.categories, ai.characters, ai.type, ai.similar
FROM anidb_titles AS at LEFT JOIN anidb_info ai USING (anidbid)
WHERE at.anidbid = %d', $anidbID));
return isset($animeInfo[0]) ? $animeInfo[0] : false;
}
public function data_getForMenuByTypeAndRole($id, $role)
{
if ($role == Users::ROLE_ADMIN) {
$role = "";
} else {
$role = sprintf("AND (role = %d OR role = 0)", $role);
}
return $this->pdo->query(sprintf("SELECT * FROM content WHERE showinmenu = 1 AND status = 1 AND contenttype = %d %s ", $id, $role));
}
/**
* Get the regex from the DB, cache them locally for 15 mins.
* Cache them also in the cache server, as this script might be terminated.
*
* @param string $groupName
*/
protected function _fetchRegex($groupName)
{
// Check if we need to do an initial cache or refresh our cache.
if (isset($this->_regexCache[$groupName]['ttl']) && time() - $this->_regexCache[$groupName]['ttl'] < 900) {
return;
}
// Get all regex from DB which match the current group name. Cache them for 15 minutes. #CACHEDQUERY#
$this->_regexCache[$groupName]['regex'] = $this->pdo->query(sprintf('SELECT r.regex%s FROM %s r WHERE %s REGEXP r.group_regex AND r.status = 1 ORDER BY r.ordinal ASC, r.group_regex ASC', $this->tableName === 'category_regexes' ? ', r.category_id' : '', $this->tableName, $this->pdo->escapeString($groupName)), true, 900);
// Set the TTL.
$this->_regexCache[$groupName]['ttl'] = time();
}
/**
* Delete a role by ID.
*
* @param int $id ID of the role.
*
* @return bool|\PDOStatement
*/
public function deleteRole($id)
{
$res = $this->pdo->query(sprintf("SELECT id FROM users WHERE role = %d", $id));
if (sizeof($res) > 0) {
$userids = [];
foreach ($res as $user) {
$userids[] = $user['id'];
}
$defaultrole = $this->getDefaultRole();
$this->pdo->queryExec(sprintf("UPDATE users SET role = %d WHERE id IN (%s)", $defaultrole['id'], implode(',', $userids)));
}
return $this->pdo->queryExec(sprintf("DELETE FROM user_roles WHERE id = %d", $id));
}
/**
* Get all genres for search-filter.tpl
*
* @param bool $activeOnly
*
* @return array|null
*/
public function getAllGenres($activeOnly = false)
{
$ret = null;
if ($activeOnly) {
$res = $this->pdo->query("SELECT title FROM genres WHERE disabled = 0 AND type = 6000 ORDER BY title");
} else {
$res = $this->pdo->query("SELECT title FROM genres WHERE disabled = 1 AND type = 6000 ORDER BY title");
}
foreach ($res as $arr => $value) {
$ret[] = $value['title'];
}
return $ret;
}
/**
* Get all groups in the DB.
*
* @return bool
* @access protected
*/
protected function getAllGroups()
{
$this->allGroups = [];
$groups = $this->pdo->query("SELECT id, name FROM groups");
foreach ($groups as $group) {
$this->allGroups[$group["name"]] = $group["id"];
}
if (count($this->allGroups) === 0) {
$this->echoOut('You have no groups in your database!');
return false;
}
return true;
}
/**
* Reset all groups.
*
* @return bool
*/
public function resetall()
{
$this->pdo->queryExec("TRUNCATE TABLE collections");
$this->pdo->queryExec("TRUNCATE TABLE binaries");
$this->pdo->queryExec("TRUNCATE TABLE parts");
$this->pdo->queryExec("TRUNCATE TABLE missed_parts");
$groups = $this->pdo->query("SELECT id FROM groups");
foreach ($groups as $group) {
$this->pdo->queryExec('DROP TABLE IF EXISTS collections_' . $group['id']);
$this->pdo->queryExec('DROP TABLE IF EXISTS binaries_' . $group['id']);
$this->pdo->queryExec('DROP TABLE IF EXISTS parts_' . $group['id']);
$this->pdo->queryExec('DROP TABLE IF EXISTS missed_parts_' . $group['id']);
}
// Reset the group stats.
return $this->pdo->queryExec("\n\t\t\tUPDATE groups\n\t\t\tSET backfill_target = 0, first_record = 0, first_record_postdate = NULL, last_record = 0,\n\t\t\t\tlast_record_postdate = NULL, last_updated = NULL, active = 0");
}
public function getConsoleRange($cat, $start, $num, $orderby, $excludedcats = [])
{
$browseby = $this->getBrowseBy();
if ($start === false) {
$limit = "";
} else {
$limit = " LIMIT " . $num . " OFFSET " . $start;
}
$catsrch = '';
if (count($cat) > 0 && $cat[0] != -1) {
$catsrch = (new Category(['Settings' => $this->pdo]))->getCategorySearch($cat);
}
$exccatlist = "";
if (count($excludedcats) > 0) {
$exccatlist = " AND r.categoryid NOT IN (" . implode(",", $excludedcats) . ")";
}
$order = $this->getConsoleOrder($orderby);
return $this->pdo->query(sprintf("SELECT GROUP_CONCAT(r.id ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_id, " . "GROUP_CONCAT(r.rarinnerfilecount ORDER BY r.postdate DESC SEPARATOR ',') as grp_rarinnerfilecount, " . "GROUP_CONCAT(r.haspreview ORDER BY r.postdate DESC SEPARATOR ',') AS grp_haspreview, " . "GROUP_CONCAT(r.passwordstatus ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_password, " . "GROUP_CONCAT(r.guid ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_guid, " . "GROUP_CONCAT(rn.id ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_nfoid, " . "GROUP_CONCAT(groups.name ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_grpname, " . "GROUP_CONCAT(r.searchname ORDER BY r.postdate DESC SEPARATOR '#') AS grp_release_name, " . "GROUP_CONCAT(r.postdate ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_postdate, " . "GROUP_CONCAT(r.size ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_size, " . "GROUP_CONCAT(r.totalpart ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_totalparts, " . "GROUP_CONCAT(r.comments ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_comments, " . "GROUP_CONCAT(r.grabs ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_grabs, " . "con.*, r.consoleinfoid, groups.name AS group_name, genres.title as genre, rn.id as nfoid FROM releases r " . "LEFT OUTER JOIN groups ON groups.id = r.group_id " . "LEFT OUTER JOIN release_nfos rn ON rn.releaseid = r.id " . "INNER JOIN consoleinfo con ON con.id = r.consoleinfoid " . "INNER JOIN genres ON con.genre_id = genres.id " . "WHERE r.nzbstatus = 1 AND con.title != '' AND " . "r.passwordstatus %s AND %s %s %s " . "GROUP BY con.id ORDER BY %s %s" . $limit, Releases::showPasswords($this->pdo), $browseby, $catsrch, $exccatlist, $order[0], $order[1]), true, nZEDb_CACHE_EXPIRY_MEDIUM);
}
/**
* Match added comments to releases.
*
* @access protected
*/
protected function matchComments()
{
$res = $this->pdo->query('
SELECT r.id
FROM release_comments rc
INNER JOIN releases r USING (nzb_guid)
WHERE rc.releaseid = 0');
$found = count($res);
if ($found > 0) {
foreach ($res as $row) {
$this->pdo->queryExec(sprintf("\n\t\t\t\t\t\tUPDATE release_comments rc\n\t\t\t\t\t\tINNER JOIN releases r USING (nzb_guid)\n\t\t\t\t\t\tSET rc.releaseid = %d, r.comments = r.comments + 1\n\t\t\t\t\t\tWHERE r.id = %d\n\t\t\t\t\t\tAND rc.releaseid = 0", $row['id'], $row['id']));
}
if (nZEDb_ECHOCLI) {
echo "(Sharing) Matched {$found} comments." . PHP_EOL;
}
}
// Update first time seen.
$this->pdo->queryExec(sprintf("\n\t\t\t\t\tUPDATE sharing_sites ss\n\t\t\t\t\tINNER JOIN\n\t\t\t\t\t\t(SELECT siteid, createddate\n\t\t\t\t\t\tFROM release_comments\n\t\t\t\t\t\tWHERE createddate > '2005-01-01'\n\t\t\t\t\t\tGROUP BY siteid\n\t\t\t\t\t\tORDER BY createddate ASC) rc\n\t\t\t\t\tON ss.site_guid = rc.siteid\n\t\t\t\t\tSET ss.first_time = rc.createddate\n\t\t\t\t\tWHERE ss.first_time IS NULL OR ss.first_time > rc.createddate"));
}
/**
* Retrieves all aliases for given VideoID or VideoID for a given alias
*
* @param int $videoId
* @param string $alias
*
* @return \PDOStatement|false
*/
public function getAliases($videoId = 0, $alias = '')
{
$return = false;
$sql = '';
if ($videoId > 0) {
$sql = 'videos_id = ' . $videoId;
} else {
if ($alias !== '') {
$sql = 'title = ' . $this->pdo->escapeString($alias);
}
}
if ($sql !== '') {
$return = $this->pdo->query('
SELECT *
FROM videos_aliases
WHERE ' . $sql, true, nZEDb_CACHE_EXPIRY_MEDIUM);
}
return empty($return) ? false : $return;
}
/**
* Get all releases that need to be processed.
*
* @param int|string $groupID
* @param string $guidChar
*
* @void
*/
protected function _fetchReleases($groupID, &$guidChar)
{
$this->_releases = $this->pdo->query(sprintf('SELECT r.id, r.guid, r.name, c.disablepreview, r.size, r.group_id, r.nfostatus, r.completion, r.categoryid, r.searchname, r.preid
FROM releases r
LEFT JOIN category c ON c.id = r.categoryid
WHERE r.nzbstatus = 1
%s %s %s %s
AND r.passwordstatus BETWEEN -6 AND -1
AND r.haspreview = -1
AND c.disablepreview = 0
ORDER BY r.passwordstatus ASC, r.postdate DESC
LIMIT %d', $this->_maxSize, $this->_minSize, $groupID === '' ? '' : 'AND r.group_id = ' . $groupID, $guidChar === '' ? '' : 'AND r.guid ' . $this->pdo->likeString($guidChar, false, true), $this->_queryLimit));
if (is_array($this->_releases)) {
$this->_totalReleases = count($this->_releases);
} else {
$this->_releases = [];
$this->_totalReleases = 0;
}
}
/**
* Return all blacklists.
*
* @param bool $activeOnly Only display active blacklists ?
* @param int $opType Optional, get white or black lists (use Binaries constants).
* @param string $groupName Optional, group.
* @param bool $groupRegex Optional Join groups / binaryblacklist using regexp for equals.
*
* @return array
*/
public function getBlacklist($activeOnly = true, $opType = -1, $groupName = '', $groupRegex = false)
{
switch ($opType) {
case self::OPTYPE_BLACKLIST:
$opType = 'AND binaryblacklist.optype = ' . self::OPTYPE_BLACKLIST;
break;
case self::OPTYPE_WHITELIST:
$opType = 'AND binaryblacklist.optype = ' . self::OPTYPE_WHITELIST;
break;
default:
$opType = '';
break;
}
return $this->_pdo->query(sprintf('
SELECT
binaryblacklist.id, binaryblacklist.optype, binaryblacklist.status, binaryblacklist.description,
binaryblacklist.groupname AS groupname, binaryblacklist.regex, groups.id AS group_id, binaryblacklist.msgcol
FROM binaryblacklist
LEFT OUTER JOIN groups ON groups.name %s binaryblacklist.groupname
WHERE 1=1 %s %s %s
ORDER BY coalesce(groupname,\'zzz\')', $groupRegex ? 'REGEXP' : '=', $activeOnly ? 'AND binaryblacklist.status = 1' : '', $opType, $groupName ? 'AND groups.name REGEXP ' . $this->_pdo->escapeString($groupName) : ''));
}
public function getGamesRange($cat, $start, $num, $orderby, $maxage = -1, $excludedcats = [])
{
$browseby = $this->getBrowseBy();
if ($start === false) {
$limit = "";
} else {
$limit = " LIMIT " . $num . " OFFSET " . $start;
}
$catsrch = '';
if (count($cat) > 0 && $cat[0] != -1) {
$catsrch = (new Category(['Settings' => $this->pdo]))->getCategorySearch($cat);
}
if ($maxage > 0) {
$maxage = sprintf(' AND r.postdate > NOW() - INTERVAL %d DAY ', $maxage);
} else {
$maxage = '';
}
$exccatlist = "";
if (count($excludedcats) > 0) {
$exccatlist = " AND r.categoryid NOT IN (" . implode(",", $excludedcats) . ")";
}
$order = $this->getGamesOrder($orderby);
return $this->pdo->query(sprintf("SELECT GROUP_CONCAT(r.id ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_id, " . "GROUP_CONCAT(r.rarinnerfilecount ORDER BY r.postdate DESC SEPARATOR ',') as grp_rarinnerfilecount, " . "GROUP_CONCAT(r.haspreview ORDER BY r.postdate DESC SEPARATOR ',') AS grp_haspreview, " . "GROUP_CONCAT(r.passwordstatus ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_password, " . "GROUP_CONCAT(r.guid ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_guid, " . "GROUP_CONCAT(rn.id ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_nfoid, " . "GROUP_CONCAT(groups.name ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_grpname, " . "GROUP_CONCAT(r.searchname ORDER BY r.postdate DESC SEPARATOR '#') AS grp_release_name, " . "GROUP_CONCAT(r.postdate ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_postdate, " . "GROUP_CONCAT(r.size ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_size, " . "GROUP_CONCAT(r.totalpart ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_totalparts, " . "GROUP_CONCAT(r.comments ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_comments, " . "GROUP_CONCAT(r.grabs ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_grabs, " . "con.*, YEAR (con.releasedate) as year, r.gamesinfo_id, groups.name AS group_name,\n\t\t\t\trn.id as nfoid FROM releases r " . "LEFT OUTER JOIN groups ON groups.id = r.group_id " . "LEFT OUTER JOIN release_nfos rn ON rn.releaseid = r.id " . "INNER JOIN gamesinfo con ON con.id = r.gamesinfo_id " . "WHERE r.nzbstatus = 1 AND con.title != '' AND " . "r.passwordstatus <= (SELECT value FROM settings WHERE setting='showpasswordedrelease') AND %s %s %s %s " . "GROUP BY con.id ORDER BY %s %s" . $limit, $browseby, $catsrch, $maxage, $exccatlist, $order[0], $order[1]));
}
public function getBookRange($cat, $start, $num, $orderby, $excludedcats = [])
{
$browseby = $this->getBrowseBy();
if ($start === false) {
$limit = '';
} else {
$limit = ' LIMIT ' . $num . ' OFFSET ' . $start;
}
$catsrch = '';
if (count($cat) > 0 && $cat[0] != -1) {
$catsrch = (new Category(['Settings' => $this->pdo]))->getCategorySearch($cat);
}
$maxage = '';
if ($maxage > 0) {
$maxage = sprintf(' AND r.postdate > NOW() - INTERVAL %d DAY ', $maxage);
}
$exccatlist = '';
if (count($excludedcats) > 0) {
$exccatlist = ' AND r.categoryid NOT IN (' . implode(',', $excludedcats) . ')';
}
$order = $this->getBookOrder($orderby);
$sql = sprintf("SELECT GROUP_CONCAT(r.id ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_id, " . "GROUP_CONCAT(r.rarinnerfilecount ORDER BY r.postdate DESC SEPARATOR ',') as grp_rarinnerfilecount, " . "GROUP_CONCAT(r.haspreview ORDER BY r.postdate DESC SEPARATOR ',') AS grp_haspreview, " . "GROUP_CONCAT(r.passwordstatus ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_password, " . "GROUP_CONCAT(r.guid ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_guid, " . "GROUP_CONCAT(rn.id ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_nfoid, " . "GROUP_CONCAT(groups.name ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_grpname, " . "GROUP_CONCAT(r.searchname ORDER BY r.postdate DESC SEPARATOR '#') AS grp_release_name, " . "GROUP_CONCAT(r.postdate ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_postdate, " . "GROUP_CONCAT(r.size ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_size, " . "GROUP_CONCAT(r.totalpart ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_totalparts, " . "GROUP_CONCAT(r.comments ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_comments, " . "GROUP_CONCAT(r.grabs ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_grabs, " . "boo.*, r.bookinfoid, groups.name AS group_name, rn.id as nfoid FROM releases r " . "LEFT OUTER JOIN groups ON groups.id = r.group_id " . "LEFT OUTER JOIN release_nfos rn ON rn.releaseid = r.id " . "INNER JOIN bookinfo boo ON boo.id = r.bookinfoid " . "WHERE r.nzbstatus = 1 AND boo.cover = 1 AND boo.title != '' AND " . "r.passwordstatus %s AND %s %s %s %s " . "GROUP BY boo.id ORDER BY %s %s" . $limit, Releases::showPasswords($this->pdo), $browseby, $catsrch, $maxage, $exccatlist, $order[0], $order[1]);
return $this->pdo->query($sql, true, nZEDb_CACHE_EXPIRY_MEDIUM);
}
/**
* Get movies for RSS.
*
* @param int $limit
* @param int $userID
* @param array $excludedCats
*
* @return array
*/
public function getMyMoviesRss($limit, $userID = 0, $excludedCats = [])
{
return $this->pdo->query(sprintf("\n\t\t\t\tSELECT r.*, mi.title AS releasetitle, g.name AS group_name,\n\t\t\t\t\tCONCAT(cp.title, '-', c.title) AS category_name,\n\t\t\t\t\t%s AS category_ids,\n\t\t\t\t\tCOALESCE(cp.id,0) AS parentCategoryid\n\t\t\t\tFROM releases r\n\t\t\t\tINNER JOIN category c ON c.id = r.categoryid\n\t\t\t\tINNER JOIN category cp ON cp.id = c.parentid\n\t\t\t\tINNER JOIN groups g ON g.id = r.group_id\n\t\t\t\tLEFT OUTER JOIN movieinfo mi ON mi.imdbid = r.imdbid\n\t\t\t\tWHERE %s %s\n\t\t\t\tAND r.nzbstatus = %d\n\t\t\t\tAND r.categoryid BETWEEN 2000 AND 2999\n\t\t\t\tAND r.passwordstatus %s\n\t\t\t\tORDER BY postdate DESC %s", $this->releases->getConcatenatedCategoryIDs(), $this->releases->uSQL($this->pdo->query(sprintf('SELECT imdbid, categoryid FROM user_movies WHERE user_id = %d', $userID), true), 'imdbid'), count($excludedCats) ? ' AND r.categoryid NOT IN (' . implode(',', $excludedCats) . ')' : '', NZB::NZB_ADDED, $this->releases->showPasswords, ' LIMIT ' . ($limit > 100 ? 100 : $limit) . ' OFFSET 0'), true, nZEDb_CACHE_EXPIRY_MEDIUM);
}
/**
* Process releases with no IMDB ID's.
*
* @param string $groupID (Optional) ID of a group to work on.
* @param string $guidChar (Optional) First letter of a release GUID to use to get work.
* @param int $lookupIMDB (Optional) 0 Don't lookup IMDB, 1 lookup IMDB, 2 lookup IMDB on releases that were renamed.
*/
public function processMovieReleases($groupID = '', $guidChar = '', $lookupIMDB = 1)
{
if ($lookupIMDB == 0) {
return;
}
// Get all releases without an IMDB id.
$res = $this->pdo->query(sprintf("\n\t\t\t\tSELECT r.searchname, r.id\n\t\t\t\tFROM releases r\n\t\t\t\tWHERE r.imdbid IS NULL\n\t\t\t\tAND r.nzbstatus = 1\n\t\t\t\t%s %s %s %s\n\t\t\t\tLIMIT %d", $this->catWhere, $groupID === '' ? '' : 'AND r.group_id = ' . $groupID, $guidChar === '' ? '' : 'AND r.guid ' . $this->pdo->likeString($guidChar, false, true), $lookupIMDB == 2 ? 'AND r.isrenamed = 1' : '', $this->movieqty));
$movieCount = count($res);
if ($movieCount > 0) {
if (is_null($this->traktTv)) {
$this->traktTv = new TraktTv(['Settings' => $this->pdo]);
}
if ($this->echooutput && $movieCount > 1) {
$this->pdo->log->doEcho($this->pdo->log->header("Processing " . $movieCount . " movie releases."));
}
// Loop over releases.
foreach ($res as $arr) {
// Try to get a name/year.
if ($this->parseMovieSearchName($arr['searchname']) === false) {
//We didn't find a name, so set to all 0's so we don't parse again.
$this->pdo->queryExec(sprintf("UPDATE releases SET imdbid = 0000000 WHERE id = %d %s", $arr["id"], $this->catWhere));
continue;
} else {
$this->currentRelID = $arr['id'];
$movieName = $this->currentTitle;
if ($this->currentYear !== false) {
$movieName .= ' (' . $this->currentYear . ')';
}
if ($this->echooutput) {
$this->pdo->log->doEcho($this->pdo->log->primaryOver("Looking up: ") . $this->pdo->log->headerOver($movieName), true);
}
// Check local DB.
$getIMDBid = $this->localIMDBsearch();
if ($getIMDBid !== false) {
$imdbID = $this->doMovieUpdate('tt' . $getIMDBid, 'Local DB', $arr['id']);
if ($imdbID !== false) {
continue;
}
}
// Check OMDB api.
$buffer = Misc::getUrl(['url' => 'http://www.omdbapi.com/?t=' . urlencode($this->currentTitle) . ($this->currentYear !== false ? '&y=' . $this->currentYear : '') . '&r=json']);
if ($buffer !== false) {
$getIMDBid = json_decode($buffer);
if (isset($getIMDBid->imdbID)) {
$imdbID = $this->doMovieUpdate($getIMDBid->imdbID, 'OMDbAPI', $arr['id']);
if ($imdbID !== false) {
continue;
}
}
}
// Check on trakt.
$data = $this->traktTv->movieSummary($movieName, 'full,images');
if ($data !== false) {
$this->parseTraktTv($data);
if (isset($data['ids']['imdb'])) {
$imdbID = $this->doMovieUpdate($data['ids']['imdb'], 'Trakt', $arr['id']);
if ($imdbID !== false) {
continue;
}
}
}
// Try on search engines.
if ($this->searchEngines && $this->currentYear !== false) {
if ($this->imdbIDFromEngines() === true) {
continue;
}
}
// We failed to get an IMDB id from all sources.
$this->pdo->queryExec(sprintf("UPDATE releases SET imdbid = 0000000 WHERE id = %d %s", $arr["id"], $this->catWhere));
}
}
}
}
<?php
require_once realpath(dirname(dirname(dirname(__DIR__))) . DIRECTORY_SEPARATOR . 'indexer.php');
use nzedb\ConsoleTools;
use nzedb\Groups;
use nzedb\db\Settings;
$pdo = new Settings();
if (!isset($argv[1]) || $argv[1] != 'true') {
exit($pdo->log->error("\nThis script will move all collections, binaries, parts into tables per group.\n\n" . "php {$argv['0']} true ...: To process all parts and leave the parts/binaries/collections tables intact.\n" . "php {$argv['0']} true truncate ...: To process all parts and truncate parts/binaries/collections tables after completed.\n"));
}
$start = time();
$consoleTools = new ConsoleTools(['ColorCLI' => $pdo->log]);
$groups = new Groups(['Settings' => $pdo]);
$actgroups = $pdo->query("SELECT DISTINCT group_id from collections");
echo $pdo->log->info("Creating new collections, binaries, and parts tables for each group that has collections.");
foreach ($actgroups as $group) {
$pdo->queryExec("DROP TABLE IF EXISTS collections_" . $group['group_id']);
$pdo->queryExec("DROP TABLE IF EXISTS binaries_" . $group['group_id']);
$pdo->queryExec("DROP TABLE IF EXISTS parts_" . $group['group_id']);
if ($groups->createNewTPGTables($group['group_id']) === false) {
exit($pdo->log->error("\nThere is a problem creating new parts/files tables for group {$group['name']}.\n"));
}
}
$collections_rows = $pdo->queryDirect("SELECT group_id FROM collections GROUP BY group_id");
echo $pdo->log->info("Counting parts, this could table a few minutes.");
$parts_count = $pdo->queryOneRow("SELECT COUNT(*) AS cnt FROM parts");
$i = 0;
if ($collections_rows instanceof \Traversable) {
foreach ($collections_rows as $row) {
$groupName = $groups->getByNameByID($row['group_id']);
echo $pdo->log->header("Processing {$groupName}");
/**
* Get all the user's "my shows".
*
* @param int $uID ID of user.
*
* @return array
*/
public function getShows($uID)
{
return $this->pdo->query(sprintf("\n\t\t\t\tSELECT userseries.*, tvrage.releasetitle\n\t\t\t\tFROM userseries\n\t\t\t\tINNER JOIN tvrage ON tvrage.rageid = userseries.rageid\n\t\t\t\tWHERE user_id = %d\n\t\t\t\tORDER BY tvrage.releasetitle ASC", $uID));
}
$relcount = 0;
$ri = new ReleaseImage($pdo);
$nzb = new NZB($pdo);
$consoletools = new ConsoleTools(['ColorCLI' => $pdo->log]);
$pdo->queryExec("UPDATE groups SET first_record = 0, first_record_postdate = NULL, last_record = 0, last_record_postdate = NULL, last_updated = NULL");
echo $pdo->log->primary("Reseting all groups completed.");
$arr = ["tvrage_titles", "release_nfos", "release_comments", 'sharing', 'sharing_sites', "users_releases", "user_movies", "user_series", "movieinfo", "musicinfo", "release_files", "audio_data", "release_subtitles", "video_data", "releaseextrafull", "parts", "missed_parts", "binaries", "collections", "releases"];
foreach ($arr as &$value) {
$rel = $pdo->queryExec("TRUNCATE TABLE {$value}");
if ($rel !== false) {
echo $pdo->log->primary("Truncating {$value} completed.");
}
}
unset($value);
$sql = "SHOW table status";
$tables = $pdo->query($sql);
foreach ($tables as $row) {
$tbl = $row['name'];
if (preg_match('/collections_\\d+/', $tbl) || preg_match('/binaries_\\d+/', $tbl) || preg_match('/parts_\\d+/', $tbl) || preg_match('/missed_parts_\\d+/', $tbl) || preg_match('/\\d+_collections/', $tbl) || preg_match('/\\d+_binaries/', $tbl) || preg_match('/\\d+_parts/', $tbl) || preg_match('/\\d+_missed_parts_\\d+/', $tbl)) {
$rel = $pdo->queryDirect(sprintf('DROP TABLE %s', $tbl));
if ($rel !== false) {
echo $pdo->log->primary("Dropping {$tbl} completed.");
}
}
}
(new SphinxSearch())->truncateRTIndex('releases_rt');
$pdo->optimise(false, 'full');
echo $pdo->log->header("Deleting nzbfiles subfolders.");
try {
$files = new \RecursiveIteratorIterator(new \RecursiveDirectoryIterator($pdo->getSetting('nzbpath'), \RecursiveDirectoryIterator::SKIP_DOTS), \RecursiveIteratorIterator::CHILD_FIRST);
foreach ($files as $file) {
$cfg->setSession();
$DbSetup = new \nzedb\db\DbUpdate(['backup' => false, 'db' => $pdo]);
try {
$DbSetup->processSQLFile();
// Setup default schema
$DbSetup->loadTables();
// Load default data files
$DbSetup->processSQLFile(['filepath' => nZEDb_RES . 'db' . DS . 'schema' . DS . 'mysql-data.sql']);
} catch (\PDOException $err) {
$cfg->error = true;
$cfg->emessage = "Error inserting: (" . $err->getMessage() . ")";
}
if (!$cfg->error) {
// Check one of the standard tables was created and has data.
$dbInstallWorked = false;
$reschk = $pdo->query("SELECT COUNT(*) AS num FROM tmux");
if ($reschk === false) {
$cfg->dbCreateCheck = false;
$cfg->error = true;
$cfg->emessage = 'Could not select data from your database, check that tables and data are properly created/inserted.';
} else {
foreach ($reschk as $row) {
if ($row['num'] > 0) {
$dbInstallWorked = true;
break;
}
}
}
$ver = new \nzedb\utility\Versions();
$patch = $ver->getSQLPatchFromFiles();
$pdo->setSetting(['..sqlpatch' => $patch]);
/**
* Attempt to find NFO files inside the NZB's of releases.
*
* @param object $nntp Instance of class NNTP.
* @param string $groupID (optional) Group ID.
* @param string $guidChar (optional) First character of the release GUID (used for multi-processing).
* @param int $processImdb (optional) Attempt to find IMDB id's in the NZB?
* @param int $processTvrage (optional) Attempt to find TvRage id's in the NZB?
*
* @return int How many NFO's were processed?
*
* @access public
*/
public function processNfoFiles($nntp, $groupID = '', $guidChar = '', $processImdb = 1, $processTvrage = 1)
{
$ret = 0;
$guidCharQuery = $guidChar === '' ? '' : 'AND r.guid ' . $this->pdo->likeString($guidChar, false, true);
$groupIDQuery = $groupID === '' ? '' : 'AND r.group_id = ' . $groupID;
$optionsQuery = self::NfoQueryString($this->pdo);
$res = $this->pdo->query(sprintf('
SELECT r.id, r.guid, r.group_id, r.name
FROM releases r
WHERE 1=1 %s %s %s
ORDER BY r.nfostatus ASC, r.postdate DESC
LIMIT %d', $optionsQuery, $guidCharQuery, $groupIDQuery, $this->nzbs));
$nfoCount = count($res);
if ($nfoCount > 0) {
$this->pdo->log->doEcho($this->pdo->log->primary(PHP_EOL . ($guidChar === '' ? '' : '[' . $guidChar . '] ') . ($groupID === '' ? '' : '[' . $groupID . '] ') . 'Processing ' . $nfoCount . ' NFO(s), starting at ' . $this->nzbs . ' * = hidden NFO, + = NFO, - = no NFO, f = download failed.'));
if ($this->echo) {
// Get count of releases per nfo status
$nfoStats = $this->pdo->queryDirect(sprintf('
SELECT r.nfostatus AS status, COUNT(*) AS count
FROM releases r
WHERE 1=1 %s %s %s
GROUP BY r.nfostatus
ORDER BY r.nfostatus ASC', $optionsQuery, $guidCharQuery, $groupIDQuery));
if ($nfoStats instanceof \Traversable) {
$outString = PHP_EOL . 'Available to process';
foreach ($nfoStats as $row) {
$outString .= ', ' . $row['status'] . ' = ' . number_format($row['count']);
}
$this->pdo->log->doEcho($this->pdo->log->header($outString . '.'));
}
}
$groups = new Groups(['Settings' => $this->pdo]);
$nzbContents = new NZBContents(['Echo' => $this->echo, 'NNTP' => $nntp, 'Nfo' => $this, 'Settings' => $this->pdo, 'PostProcess' => new PostProcess(['Echo' => $this->echo, 'Nfo' => $this, 'Settings' => $this->pdo])]);
$movie = new Movie(['Echo' => $this->echo, 'Settings' => $this->pdo]);
foreach ($res as $arr) {
$fetchedBinary = $nzbContents->getNFOfromNZB($arr['guid'], $arr['id'], $arr['group_id'], $groups->getByNameByID($arr['group_id']));
if ($fetchedBinary !== false) {
// Insert nfo into database.
$cp = 'COMPRESS(%s)';
$nc = $this->pdo->escapeString($fetchedBinary);
$ckreleaseid = $this->pdo->queryOneRow(sprintf('SELECT releaseid FROM release_nfos WHERE releaseid = %d', $arr['id']));
if (!isset($ckreleaseid['releaseid'])) {
$this->pdo->queryInsert(sprintf('INSERT INTO release_nfos (nfo, releaseid) VALUES (' . $cp . ', %d)', $nc, $arr['id']));
}
$this->pdo->queryExec(sprintf('UPDATE releases SET nfostatus = %d WHERE id = %d', self::NFO_FOUND, $arr['id']));
$ret++;
$movie->doMovieUpdate($fetchedBinary, 'nfo', $arr['id'], $processImdb);
// If set scan for tvrage info. Disabled for now while TvRage is down. TODO: Add Other Scraper Checks
if ($processTvrage == 1) {
/*$tvRage = new TvRage(['Echo' => $this->echo, 'Settings' => $this->pdo]);
$showId = $this->parseShowId($fetchedBinary);
if ($showId !== false) {
$show = $tvRage->parseNameEpSeason($arr['name']);
if (is_array($show) && $show['name'] != '') {
// Update release with season, ep, and air date info (if available) from release title.
$tvRage->updateEpInfo($show, $arr['id']);
$rid = $tvRage->getByRageID($rageId);
if (!$rid) {
$tvrShow = $tvRage->getRageInfoFromService($rageId);
$tvRage->updateRageInfo($rageId, $show, $tvrShow, $arr['id']);
}
}
}*/
}
}
}
}
// Remove nfo that we cant fetch after 5 attempts.
$releases = $this->pdo->queryDirect(sprintf('SELECT r.id
FROM releases r
WHERE r.nzbstatus = %d
AND r.nfostatus < %d AND r.nfostatus > %d %s %s', NZB::NZB_ADDED, $this->maxRetries, self::NFO_FAILED, $groupIDQuery, $guidCharQuery));
if ($releases instanceof \Traversable) {
foreach ($releases as $release) {
// remove any release_nfos for failed
$this->pdo->queryExec(sprintf('
DELETE FROM release_nfos WHERE nfo IS NULL AND releaseid = %d', $release['id']));
// set release.nfostatus to failed
$this->pdo->queryExec(sprintf('
UPDATE releases r SET r.nfostatus = %d WHERE r.id = %d', self::NFO_FAILED, $release['id']));
}
}
if ($this->echo) {
if ($nfoCount > 0) {
echo PHP_EOL;
}
if ($ret > 0) {
$this->pdo->log->doEcho($ret . ' NFO file(s) found/processed.', true);
}
}
return $ret;
}
public function getRecentlyAdded()
{
return $this->pdo->query("SELECT CONCAT(cp.title, ' > ', category.title) AS title, COUNT(*) AS count\n\t\t\tFROM category\n\t\t\tINNER JOIN category cp on cp.id = category.parentid\n\t\t\tINNER JOIN releases r ON r.categoryid = category.id\n\t\t\tWHERE r.adddate > NOW() - INTERVAL 1 WEEK\n\t\t\tGROUP BY concat(cp.title, ' > ', category.title)\n\t\t\tORDER BY COUNT(*) DESC");
}
