/** * 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"); }