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