public function getConsoleInfoByName($title, $platform)
{
//only used to get a count of words
$searchwords = $searchsql = '';
$ft = $this->pdo->queryDirect("SHOW INDEX FROM consoleinfo WHERE key_name = 'ix_consoleinfo_title_platform_ft'");
if ($ft->rowCount() !== 2) {
$searchsql .= sprintf(" title %s AND platform %s'", $this->pdo->likeString($title, true, true), $this->pdo->likeString($platform, true, true));
} else {
$title = preg_replace('/( - | -|\\(.+\\)|\\(|\\))/', ' ', $title);
$title = preg_replace('/[^\\w ]+/', '', $title);
$title = trim(preg_replace('/\\s\\s+/i', ' ', $title));
$title = trim($title);
$words = explode(' ', $title);
foreach ($words as $word) {
$word = trim(rtrim(trim($word), '-'));
if ($word !== '' && $word !== '-') {
$word = '+' . $word;
$searchwords .= sprintf('%s ', $word);
}
}
$searchwords = trim($searchwords);
$searchsql .= sprintf(" MATCH(title, platform) AGAINST(%s IN BOOLEAN MODE)", $this->pdo->escapeString($searchwords));
}
return $this->pdo->queryOneRow(sprintf("SELECT * FROM consoleinfo WHERE %s", $searchsql));
}
/**
* Retrives the count of Anime titles for pager functions optionally filtered by title
*
* @param string $animetitle
* @return int
*/
public function getAnimeCount($animetitle = '')
{
$rsql = '';
if ($animetitle != '') {
$rsql .= sprintf('AND at.title %s', $this->pdo->likeString($animetitle, true, true));
}
$res = $this->pdo->queryOneRow(sprintf('SELECT COUNT(at.anidbid) AS num
FROM anidb_titles AS at LEFT JOIN anidb_info AS ai USING (anidbid)
WHERE 1=1 %s', $rsql));
return $res['num'];
}
/**
* Retrieve alternate release with same or similar searchname
*
* @param string $guid
* @param string $searchname
* @param string $userid
* @return string
*/
public function getAlternate($guid, $searchname, $userid)
{
//status values
// 0/false = successfully downloaded
// 1/true = failed download
$this->pdo->queryInsert(sprintf("INSERT IGNORE INTO dnzb_failures (userid, guid) VALUES (%d, %s)", $userid, $this->pdo->escapeString($guid)));
$alternate = $this->pdo->queryOneRow(sprintf('SELECT * FROM releases r
WHERE r.searchname %s
AND r.guid NOT IN (SELECT guid FROM failed_downloads WHERE userid = %d)', $this->pdo->likeString($searchname), $userid));
return $alternate;
}
public function getBrowseBy()
{
$like = 'LIKE';
$browseby = ' ';
$browsebyArr = $this->getBrowseByOptions();
foreach ($browsebyArr as $bbk => $bbv) {
if (isset($_REQUEST[$bbk]) && !empty($_REQUEST[$bbk])) {
$bbs = stripslashes($_REQUEST[$bbk]);
$browseby .= 'boo.' . $bbv . ' ' . $this->pdo->likeString($bbs, true, true) . ' AND ';
}
}
return $browseby;
}
/**
* @return string
*/
public function getBrowseBy()
{
$browseby = ' ';
$browsebyArr = $this->getBrowseByOptions();
foreach ($browsebyArr as $bbk => $bbv) {
if (isset($_REQUEST[$bbk]) && !empty($_REQUEST[$bbk])) {
$bbs = stripslashes($_REQUEST[$bbk]);
if (preg_match('/id/i', $bbv)) {
$browseby .= 'm.' . $bbv . ' = ' . $bbs . ' AND ';
} else {
$browseby .= 'm.' . $bbv . ' ' . $this->pdo->likeString($bbs, true, true) . ' AND ';
}
}
}
return $browseby;
}
/**
* @return string
*/
protected function getBrowseBy()
{
$browseBy = ' ';
$browseByArr = ['title', 'director', 'actors', 'genre', 'id'];
foreach ($browseByArr as $bb) {
if (isset($_REQUEST[$bb]) && !empty($_REQUEST[$bb])) {
$bbv = stripslashes($_REQUEST[$bb]);
if ($bb == "genre") {
$bbv = $this->getgenreid($bbv);
}
if ($bb == 'id') {
$browseBy .= 'xxx.' . $bb . '=' . $bbv . ' AND ';
} else {
$browseBy .= 'xxx.' . $bb . ' ' . $this->pdo->likeString($bbv, true, true) . ' AND ';
}
}
}
return $browseBy;
}
/**
* 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.groupid, r.nfostatus, r.completion, r.categoryid, r.searchname, r.prehashid
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.groupid = ' . $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;
}
}
public function matchPredbFT($pre, $echo, $namestatus, $echooutput, $show)
{
$matching = $total = 0;
$join = $this->_preFTsearchQuery($pre['title']);
//Find release matches with fulltext and then identify exact matches with cleaned LIKE string
$res = $this->pdo->queryDirect(sprintf("\n\t\t\t\t\t\t\tSELECT r.id AS releaseid, r.name, r.searchname,\n\t\t\t\t\t\t\t\tr.groupid, r.categoryid\n\t\t\t\t\t\t\tFROM releases r\n\t\t\t\t\t\t\t%1\$s\n\t\t\t\t\t\t\tAND (r.name %2\$s OR r.searchname %2\$s)\n\t\t\t\t\t\t\tAND r.prehashid = 0\n\t\t\t\t\t\t\tLIMIT 21", $join, $this->pdo->likeString($pre['title'], true, true)));
if ($res !== false) {
$total = $res->rowCount();
}
// Run if row count is positive, but do not run if row count exceeds 10 (as this is likely a failed title match)
if ($total > 0 && $total <= 15 && $res instanceof Traversable) {
foreach ($res as $row) {
if ($pre['title'] !== $row['searchname']) {
$this->updateRelease($row, $pre['title'], $method = "Title Match source: " . $pre['source'], $echo, "PreDB FT Exact, ", $namestatus, $show, $pre['prehashid']);
$matching++;
} else {
$this->_updateSingleColumn('prehashid', $pre['prehashid'], $row['releaseid']);
}
}
} elseif ($total >= 16) {
$matching = -1;
}
return $matching;
}
/**
* 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.groupid = ' . $groupID;
$optionsQuery = self::NfoQueryString($this->pdo);
$res = $this->pdo->query(sprintf('
SELECT r.id, r.guid, r.groupid, 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]);
$tvRage = new TvRage(['Echo' => $this->echo, 'Settings' => $this->pdo]);
foreach ($res as $arr) {
$fetchedBinary = $nzbContents->getNFOfromNZB($arr['guid'], $arr['id'], $arr['groupid'], $groups->getByNameByID($arr['groupid']));
if ($fetchedBinary !== false) {
// Insert nfo into database.
$cp = 'COMPRESS(%s)';
$nc = $this->pdo->escapeString($fetchedBinary);
$ckreleaseid = $this->pdo->queryOneRow(sprintf('SELECT id FROM releasenfo WHERE releaseid = %d', $arr['id']));
if (!isset($ckreleaseid['id'])) {
$this->pdo->queryInsert(sprintf('INSERT INTO releasenfo (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.
if ($processTvrage == 1) {
$rageId = $this->parseRageId($fetchedBinary);
if ($rageId !== 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 %s %s', NZB::NZB_ADDED, $this->maxRetries, $groupIDQuery, $guidCharQuery));
if ($releases instanceof Traversable) {
foreach ($releases as $release) {
$this->pdo->queryExec(sprintf('DELETE FROM releasenfo WHERE nfo IS NULL AND releaseid = %d', $release['id']));
}
}
// Set releases with no NFO.
$this->pdo->queryExec(sprintf('
UPDATE releases r
SET r.nfostatus = %d
WHERE r.nzbstatus = %d
AND r.nfostatus < %d %s %s', self::NFO_FAILED, NZB::NZB_ADDED, $this->maxRetries, $groupIDQuery, $guidCharQuery));
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;
}
$maxperrun = $pieces[2];
$thread = $pieces[3];
switch (true) {
case $pieces[0] === 'nfo' && isset($guidChar) && isset($maxperrun) && is_numeric($maxperrun):
$releases = $pdo->queryDirect(sprintf('
SELECT r.id AS releaseid, r.guid, r.groupid, r.categoryid, r.name, r.searchname,
uncompress(nfo) AS textstring
FROM releases r
INNER JOIN releasenfo rn ON r.id = rn.releaseid
WHERE r.guid %s
AND r.nzbstatus = 1
AND r.proc_nfo = 0
AND r.nfostatus = 1
AND r.prehashid = 0
ORDER BY r.postdate DESC
LIMIT %s', $pdo->likeString($guidChar, false, true), $maxperrun));
if ($releases instanceof Traversable) {
foreach ($releases as $release) {
if (preg_match('/^=newz\\[NZB\\]=\\w+/', $release['textstring'])) {
$namefixer->done = $namefixer->matched = false;
$pdo->queryDirect(sprintf('UPDATE releases SET proc_nfo = 1 WHERE id = %d', $release['releaseid']));
$namefixer->checked++;
echo '.';
} else {
$namefixer->done = $namefixer->matched = false;
if ($namefixer->checkName($release, true, 'NFO, ', 1, 1) !== true) {
echo '.';
}
$namefixer->checked++;
}
}
/**
* Retrieves AniDB Info using a cleaned name
*
* @param string $searchName
*
* @return array|bool
*/
private function getAnidbByName($searchName = '')
{
return $this->pdo->queryOneRow(sprintf("\n\t\t\t\t\t\t\tSELECT at.anidbid, at.title\n\t\t\t\t\t\t\tFROM anidb_titles AS at\n\t\t\t\t\t\t\tWHERE at.title %s", $this->pdo->likeString($searchName, true, true)));
}
public function processTvReleases($groupID = '', $guidChar = '', $lookupTvRage = 1, $local = false)
{
$ret = 0;
if ($lookupTvRage == 0) {
return $ret;
}
$trakt = new TraktTv(['Settings' => $this->pdo]);
// Get all releases without a rageid which are in a tv category.
$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.nzbstatus = 1\n\t\t\t\tAND r.rageid = -1\n\t\t\t\tAND r.size > 1048576\n\t\t\t\tAND %s\n\t\t\t\t%s %s %s\n\t\t\t\tORDER BY r.postdate DESC\n\t\t\t\tLIMIT %d", $this->catWhere, $groupID === '' ? '' : 'AND r.groupid = ' . $groupID, $guidChar === '' ? '' : 'AND r.guid ' . $this->pdo->likeString($guidChar, false, true), $lookupTvRage == 2 ? 'AND r.isrenamed = 1' : '', $this->rageqty));
$tvcount = count($res);
if ($this->echooutput && $tvcount > 1) {
echo $this->pdo->log->header("Processing TV for " . $tvcount . " release(s).");
}
foreach ($res as $arr) {
$show = $this->parseNameEpSeason($arr['searchname']);
if (is_array($show) && $show['name'] != '') {
// Update release with season, ep, and airdate info (if available) from releasetitle.
$this->updateEpInfo($show, $arr['id']);
// Find the rageID.
$id = $this->getByTitle($show['cleanname']);
// Force local lookup only
if ($local == true) {
$lookupTvRage = false;
}
if ($id === false && $lookupTvRage) {
// If it doesnt exist locally and lookups are allowed lets try to get it.
if ($this->echooutput) {
echo $this->pdo->log->primaryOver("TVRage ID for ") . $this->pdo->log->headerOver($show['cleanname']) . $this->pdo->log->primary(" not found in local db, checking web.");
}
$tvrShow = $this->getRageMatch($show);
if ($tvrShow !== false && is_array($tvrShow)) {
// Get all tv info and add show.
$this->updateRageInfo($tvrShow['showid'], $show, $tvrShow, $arr['id']);
} else {
if ($tvrShow === false) {
// If tvrage fails, try trakt.
$traktArray = $trakt->episodeSummary($show['name'], $show['season'], $show['episode']);
if ($traktArray !== false) {
if (isset($traktArray['ids']['tvrage']) && $traktArray['ids']['tvrage'] !== 0) {
if ($this->echooutput) {
echo $this->pdo->log->primary('Found TVRage ID on trakt:' . $traktArray['ids']['tvrage']);
}
$this->updateRageInfoTrakt($traktArray['ids']['tvrage'], $show, $traktArray, $arr['id']);
} else {
$this->add(-2, $show['cleanname'], '', '', '', '');
}
} else {
$this->add(-2, $show['cleanname'], '', '', '', '');
}
}
}
} else {
if ($id > 0) {
$tvtitle = "NULL";
$tvairdate = isset($show['airdate']) && !empty($show['airdate']) ? $this->pdo->escapeString($this->checkDate($show['airdate'])) : "NULL";
if ($lookupTvRage) {
$epinfo = $this->getEpisodeInfo($id, $show['season'], $show['episode']);
if ($epinfo !== false) {
if (isset($epinfo['airdate'])) {
$tvairdate = $this->pdo->escapeString($this->checkDate($epinfo['airdate']));
}
if (!empty($epinfo['title'])) {
$tvtitle = $this->pdo->escapeString(trim($epinfo['title']));
}
}
}
if ($tvairdate == "NULL") {
$this->pdo->queryExec(sprintf('
UPDATE releases
SET tvtitle = %s, rageid = %d
WHERE %s
AND id = %d', $tvtitle, $id, $this->catWhere, $arr['id']));
} else {
$this->pdo->queryExec(sprintf('
UPDATE releases
SET tvtitle = %s, tvairdate = %s, rageid = %d
WHERE %s
AND id = %d', $tvtitle, $tvairdate, $id, $this->catWhere, $arr['id']));
}
// Cant find rageid, so set rageid to n/a.
} else {
$this->setRageNotFound($arr['id']);
}
}
// Not a tv episode, so set rageid to n/a.
} else {
$this->setRageNotFound($arr['id']);
}
$ret++;
}
return $ret;
}
