/**
* Retrieve alternate release with same or similar searchname
*
* @param string $guid
* @param string $userid
* @return string
*/
public function getAlternate($guid, $userid)
{
$rel = $this->pdo->queryOneRow(sprintf('
SELECT id, searchname, categoryid
FROM releases
WHERE guid = %s', $this->pdo->escapeString($guid)));
if ($rel === false) {
return false;
}
$insert = $this->pdo->queryInsert(sprintf('
INSERT IGNORE INTO dnzb_failures (release_id, userid, failed)
VALUES (%d, %d, 1)', $rel['id'], $userid));
// If we didn't actually insert the row, don't add a comment
if (is_numeric($insert) && $insert > 0) {
$this->postComment($rel['id'], $userid);
}
$alternate = $this->pdo->queryOneRow(sprintf('
SELECT r.guid
FROM releases r
LEFT JOIN dnzb_failures df ON r.id = df.release_id
WHERE r.searchname %s
AND df.release_id IS NULL
AND r.categoryid = %d
AND r.id != %d
ORDER BY r.postdate DESC', $this->pdo->likeString($rel['searchname'], true, true), $rel['categoryid'], $rel['id']));
return $alternate;
}
/**
* 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)
{
$rel = $this->pdo->queryOneRow(sprintf('
SELECT id, categoryid
FROM releases
WHERE guid = %s', $this->pdo->escapeString($guid)));
// Specifying LAST_INSERT_ID on releaseid will return the releaseid
// if the row was actually inserted and not updated
$insert = $this->pdo->queryInsert(sprintf('
INSERT INTO dnzb_failures (release_id, userid, failed)
VALUES (LAST_INSERT_ID(%d), %d, 1)
ON DUPLICATE KEY UPDATE failed = failed + 1', $rel['id'], $userid));
// If we didn't actually insert the row, don't add a comment
if ((int) $insert > 0) {
$this->postComment($rel['id'], $userid);
}
$alternate = $this->pdo->queryOneRow(sprintf('
SELECT r.*
FROM releases r
LEFT JOIN dnzb_failures df ON r.id = df.release_id
WHERE r.searchname %s
AND df.release_id IS NULL
AND r.categoryid = %d', $this->pdo->likeString($searchname, true, true), $rel['categoryid'], $userid));
return $alternate;
}
/**
* Get video info from a Site ID and column.
*
* @param string $siteColumn
* @param integer $siteID
*
* @return array|false False if invalid site, or ID not found; video.id value otherwise.
*/
protected function getVideoIDFromSiteID($siteColumn, $siteID)
{
if (in_array($siteColumn, $this->sites)) {
$result = $this->pdo->queryOneRow("SELECT id FROM videos WHERE {$siteColumn} = {$siteID}");
return isset($result['id']) ? $result['id'] : false;
}
return false;
}
/**
* @param $relid
* @param $uid
*/
public function postComment($relid, $uid)
{
$text = 'This release has failed to download properly. It might fail for other users too.
This comment is automatically generated.';
$dbl = $this->pdo->queryOneRow(sprintf('SELECT text FROM release_comments WHERE releaseid = %d AND userid = %d', $relid, $uid));
if ($dbl['text'] != $text) {
$this->rc->addComment($relid, $text, $uid, '');
}
}
/**
* Add new files for a release ID.
*
* @param int $id The ID of the release.
* @param string $name Name of the file.
* @param int $size Size of the file.
* @param int $createdTime Unix time the file was created.
* @param int $hasPassword Does it have a password (see Releases class constants)?
*
* @return mixed
*/
public function add($id, $name, $size, $createdTime, $hasPassword)
{
$duplicateCheck = $this->pdo->queryOneRow(sprintf('
SELECT id
FROM release_files
WHERE releaseid = %d AND name = %s', $id, $this->pdo->escapeString(utf8_encode($name))));
if ($duplicateCheck === false) {
return $this->pdo->queryInsert(sprintf("\n\t\t\t\t\tINSERT INTO release_files\n\t\t\t\t\t(releaseid, name, size, createddate, passworded)\n\t\t\t\t\tVALUES\n\t\t\t\t\t(%d, %s, %s, %s, %d)", $id, $this->pdo->escapeString(utf8_encode($name)), $this->pdo->escapeString($size), $this->pdo->from_unixtime($createdTime), $hasPassword));
}
return 0;
}
/**
* Update the list of newsgroups and return an array of messages.
*
* @param string $groupList
* @param int $active
* @param int $backfill
*
* @return array
*/
public function addBulk($groupList, $active = 1, $backfill = 1)
{
if (preg_match('/^\\s*$/m', $groupList)) {
$ret = "No group list provided.";
} else {
$nntp = new NNTP(['Echo' => false]);
if ($nntp->doConnect() !== true) {
return 'Problem connecting to usenet.';
}
$groups = $nntp->getGroups();
$nntp->doQuit();
if ($nntp->isError($groups)) {
return 'Problem fetching groups from usenet.';
}
$regFilter = '/' . $groupList . '/i';
$ret = [];
foreach ($groups as $group) {
if (preg_match($regFilter, $group['group']) > 0) {
$res = $this->pdo->queryOneRow(sprintf('SELECT id FROM groups WHERE name = %s', $this->pdo->escapeString($group['group'])));
if ($res === false) {
$this->pdo->queryInsert(sprintf('INSERT INTO groups (name, active, backfill) VALUES (%s, %d, %d)', $this->pdo->escapeString($group['group']), $active, $backfill));
$ret[] = ['group' => $group['group'], 'msg' => 'Created'];
}
}
}
if (count($ret) === 0) {
$ret = 'No groups found with your regex, try again!';
}
}
return $ret;
}
public function addFull($id, $xml)
{
$ckid = $this->pdo->queryOneRow(sprintf('SELECT releaseid FROM releaseextrafull WHERE releaseid = %s', $id));
if (!isset($ckid['releaseid'])) {
return $this->pdo->queryExec(sprintf('INSERT INTO releaseextrafull (releaseid, mediainfo) VALUES (%d, %s)', $id, $this->pdo->escapeString($xml)));
}
}
/**
* Supplementary function for getByTitle that queries for a like match
*
* @param $title
* @param $type
* @param int $source
*
* @return array|false
*/
public function getTitleLoose($title, $type, $source = 0)
{
$return = false;
if (!empty($title)) {
$return = $this->pdo->queryOneRow(sprintf("\n\t\t\t\t\tSELECT v.id\n\t\t\t\t\tFROM videos v\n\t\t\t\t\tLEFT JOIN videos_aliases va ON v.id = va.videos_id\n\t\t\t\t\tWHERE (v.title %1\$s\n\t\t\t\t\tOR va.title %1\$s)\n\t\t\t\t\tAND type = %2\$d %3\$s", $this->pdo->likeString(rtrim($title, '%'), false, false), $type, $source > 0 ? 'AND v.source = ' . $source : ''));
}
return $return;
}
public function getCount($ragename = "")
{
$rsql = '';
if ($ragename != "") {
$rsql .= sprintf("AND tvrage.releasetitle LIKE %s ", $this->pdo->escapeString("%" . $ragename . "%"));
}
$res = $this->pdo->queryOneRow(sprintf("SELECT COUNT(id) AS num FROM tvrage WHERE 1=1 %s", $rsql));
return $res["num"];
}
/**
* Get upcoming movies.
*
* @param $type
* @param string $source
*
* @return array|bool
*/
public function getUpcoming($type, $source = 'rottentomato')
{
$list = $this->pdo->queryOneRow(sprintf('SELECT * FROM upcoming_releases WHERE source = %s AND typeid = %d', $this->pdo->escapeString($source), $type));
if ($list === false) {
$this->updateUpcoming();
$list = $this->pdo->queryOneRow(sprintf('SELECT * FROM upcoming_releases WHERE source = %s AND typeid = %d', $this->pdo->escapeString($source), $type));
}
return $list;
}
/**
* Formulate part of a query to prevent deletion of currently inserting parts / binaries / collections.
*
* @param string $groupName
* @param int $difference
*
* @return string
* @access private
*/
private function minMaxQueryFormulator($groupName, $difference)
{
$minMaxId = $this->pdo->queryOneRow(sprintf('SELECT MIN(id) AS min, MAX(id) AS max FROM %s', $groupName));
if ($minMaxId === false) {
$minMaxId = '';
} else {
$minMaxId = ' AND id < ' . ($minMaxId['max'] - $minMaxId['min'] >= $difference ? $minMaxId['max'] - $difference : 1);
}
return $minMaxId;
}
/**
* 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;
}
/**
* Check the array using regex for a clean name.
*
* @param $release
* @param boolean $echo
* @param string $type
* @param $namestatus
* @param $show
* @param boolean $preid
*
* @return boolean
*/
public function checkName($release, $echo, $type, $namestatus, $show, $preid = false)
{
// Get pre style name from releases.name
if (preg_match_all(self::PREDB_REGEX, $release['textstring'], $matches) && !preg_match('/Source\\s\\:/i', $release['textstring'])) {
foreach ($matches as $match) {
foreach ($match as $val) {
$title = $this->pdo->queryOneRow("SELECT title, id from predb WHERE title = " . $this->pdo->escapeString(trim($val)));
if ($title !== false) {
$this->updateRelease($release, $title['title'], $method = "preDB: Match", $echo, $type, $namestatus, $show, $title['id']);
$preid = true;
}
}
}
}
// if only processing for PreDB match skip to return
if ($preid !== true) {
switch ($type) {
case "PAR2, ":
$this->fileCheck($release, $echo, $type, $namestatus, $show);
break;
case "NFO, ":
$this->nfoCheckTV($release, $echo, $type, $namestatus, $show);
$this->nfoCheckMov($release, $echo, $type, $namestatus, $show);
$this->nfoCheckMus($release, $echo, $type, $namestatus, $show);
$this->nfoCheckTY($release, $echo, $type, $namestatus, $show);
$this->nfoCheckG($release, $echo, $type, $namestatus, $show);
continue;
case "Filenames, ":
$this->fileCheck($release, $echo, $type, $namestatus, $show);
continue;
default:
$this->tvCheck($release, $echo, $type, $namestatus, $show);
$this->movieCheck($release, $echo, $type, $namestatus, $show);
$this->gameCheck($release, $echo, $type, $namestatus, $show);
$this->appCheck($release, $echo, $type, $namestatus, $show);
}
// set NameFixer process flags after run
if ($namestatus == 1 && $this->matched === false) {
switch ($type) {
case "NFO, ":
$this->_updateSingleColumn('proc_nfo', self::PROC_NFO_DONE, $release['releaseid']);
break;
case "Filenames, ":
$this->_updateSingleColumn('proc_files', self::PROC_FILES_DONE, $release['releaseid']);
break;
case "PAR2, ":
$this->_updateSingleColumn('proc_par2', self::PROC_FILES_DONE, $release['releaseid']);
break;
}
}
}
return $this->matched;
}
/**
* Get file info from inside PAR2, store it in DB, attempt to get a release name.
*
* @param string $fileLocation
*/
protected function _siftPAR2Info($fileLocation)
{
$this->_par2Info->open($fileLocation);
if ($this->_par2Info->error) {
return;
}
$releaseInfo = $this->pdo->queryOneRow(sprintf('
SELECT UNIX_TIMESTAMP(postdate) AS postdate, proc_pp
FROM releases
WHERE id = %d', $this->_release['id']));
if ($releaseInfo === false) {
return;
}
// Only get a new name if the category is OTHER.
$foundName = true;
if (nZEDb_RENAME_PAR2 && $releaseInfo['proc_pp'] == 0 && in_array((int) $this->_release['categoryid'], [Category::CAT_BOOKS_OTHER, Category::CAT_GAME_OTHER, Category::CAT_MOVIE_OTHER, Category::CAT_MUSIC_OTHER, Category::CAT_PC_PHONE_OTHER, Category::CAT_TV_OTHER, Category::CAT_OTHER_HASHED, Category::CAT_XXX_OTHER, Category::CAT_MISC])) {
$foundName = false;
}
$filesAdded = 0;
$files = $this->_par2Info->getFileList();
foreach ($files as $file) {
if (!isset($file['name'])) {
continue;
}
// If we found a name and added 10 files, stop.
if ($foundName === true && $filesAdded > 10) {
break;
}
// Add to release files.
if ($this->_addPAR2Files) {
if ($filesAdded < 11 && $this->pdo->queryOneRow(sprintf('SELECT id FROM release_files WHERE releaseid = %d AND name = %s', $this->_release['id'], $this->pdo->escapeString($file['name']))) === false) {
// Try to add the files to the DB.
if ($this->_releaseFiles->add($this->_release['id'], $file['name'], $file['size'], $releaseInfo['postdate'], 0)) {
$filesAdded++;
}
}
} else {
$filesAdded++;
}
// Try to get a new name.
if ($foundName === false) {
$this->_release['textstring'] = $file['name'];
$this->_release['releaseid'] = $this->_release['id'];
if ($this->_nameFixer->checkName($this->_release, $this->_echoCLI ? true : false, 'PAR2, ', 1, 1) === true) {
$foundName = true;
}
}
}
// Update the file count with the new file count + old file count.
$this->pdo->queryExec(sprintf('UPDATE releases SET rarinnerfilecount = rarinnerfilecount + %d WHERE id = %d', $filesAdded, $this->_release['id']));
$this->_foundPAR2Info = true;
}
/**
* Checks if a user is a specific role.
*
* @notes Uses type of $user to denote identifier. if string: username, if int: userid
* @param int $roleID
* @param string|int $user
* @return bool
*/
public function roleCheck($roleID, $user)
{
if (is_string($user) && strlen($user) > 0) {
$user = $this->pdo->escapeString($user);
$querySuffix = "username = {$user}";
} elseif (is_int($user) && $user >= 0) {
$querySuffix = "id = {$user}";
} else {
return false;
}
$result = $this->pdo->queryOneRow(sprintf("SELECT role FROM users WHERE %s", $querySuffix));
return (int) $result['role'] == (int) $roleID ? true : false;
}
/**
* Update Sphinx Relases index for given releaseid.
*
* @param int $releaseID
* @param \nzedb\db\Settings $pdo
*/
public function updateRelease($releaseID, Settings $pdo)
{
if (!is_null($this->sphinxQL)) {
$new = $pdo->queryOneRow(sprintf('
SELECT r.id, r.name, r.searchname, r.fromname, IFNULL(GROUP_CONCAT(rf.name SEPARATOR " "),"") filename
FROM releases r
LEFT JOIN release_files rf ON (r.id=rf.releaseid)
WHERE r.id = %d
GROUP BY r.id LIMIT 1', $releaseID));
if ($new !== false) {
$this->insertRelease($new);
}
}
}
/**
* Delete release from Sphinx RT table.
* @param array $identifiers ['g' => Release GUID(mandatory), 'id => ReleaseID(optional, pass false)]
* @param \nzedb\db\Settings $pdo
*/
public function deleteRelease($identifiers, Settings $pdo)
{
if (!is_null($this->sphinxQL)) {
if ($identifiers['i'] === false) {
$identifiers['i'] = $pdo->queryOneRow(sprintf('SELECT id FROM releases WHERE guid = %s', $pdo->escapeString($identifiers['g'])));
if ($identifiers['i'] !== false) {
$identifiers['i'] = $identifiers['i']['id'];
}
}
if ($identifiers['i'] !== false) {
$this->sphinxQL->queryExec(sprintf('DELETE FROM releases_rt WHERE id = %s', $identifiers['i']));
}
}
}
/**
* Directs flow for updating child AniDB tables
*
* @param array $AniDBInfoArray
*/
private function updateAniChildTables($AniDBInfoArray = [])
{
$check = $this->pdo->queryOneRow(sprintf('
SELECT ai.anidbid AS info
FROM anidb_info ai
WHERE ai.anidbid = %d', $this->anidbId));
if ($check === false) {
$picture = $this->insertAniDBInfoEps($AniDBInfoArray);
} else {
$picture = $this->updateAniDBInfoEps($AniDBInfoArray);
}
if (!empty($picture) && !file_exists($this->imgSavePath . $this->anidbId . ".jpg")) {
(new ReleaseImage($this->pdo))->saveImage($this->anidbId, 'http://img7.anidb.net/pics/anime/' . $picture, $this->imgSavePath);
}
}
/**
* Get Genre ID's Of the title
*
* @param $arr - Array or String
*
* @return string - If array .. 1,2,3,4 if string .. 1
*/
private function getGenreID($arr)
{
$ret = null;
if (!is_array($arr)) {
$res = $this->pdo->queryOneRow("SELECT id FROM genres WHERE title = " . $this->pdo->escapeString($arr));
if ($res !== false) {
return $res["id"];
}
}
foreach ($arr as $key => $value) {
$res = $this->pdo->queryOneRow("SELECT id FROM genres WHERE title = " . $this->pdo->escapeString($value));
if ($res !== false) {
$ret .= "," . $res["id"];
} else {
$ret .= "," . $this->insertGenre($value);
}
}
$ret = ltrim($ret, ",");
return $ret;
}
/**
* @param array $con
*
* @return false|int|string
*/
protected function _updateConsoleTable($con = [])
{
$ri = new ReleaseImage($this->pdo);
$check = $this->pdo->queryOneRow(sprintf('
SELECT id
FROM consoleinfo
WHERE asin = %s', $this->pdo->escapeString($con['asin'])));
if ($check === false) {
$consoleId = $this->pdo->queryInsert(sprintf("INSERT INTO consoleinfo (title, asin, url, salesrank, platform, publisher, genre_id, esrb, releasedate, review, cover, createddate, updateddate)\n\t\t\t\t\tVALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %d, NOW(), NOW())", $this->pdo->escapeString($con['title']), $this->pdo->escapeString($con['asin']), $this->pdo->escapeString($con['url']), $con['salesrank'], $this->pdo->escapeString($con['platform']), $this->pdo->escapeString($con['publisher']), $con['consolegenreID'] == -1 ? "null" : $con['consolegenreID'], $this->pdo->escapeString($con['esrb']), $con['releasedate'] != "" ? $this->pdo->escapeString($con['releasedate']) : "null", $this->pdo->escapeString(substr($con['review'], 0, 3000)), $con['cover']));
if ($con['cover'] === 1) {
$con['cover'] = $ri->saveImage($consoleId, $con['coverurl'], $this->imgSavePath, 250, 250);
}
} else {
$consoleId = $check['id'];
if ($con['cover'] === 1) {
$con['cover'] = $ri->saveImage($consoleId, $con['coverurl'], $this->imgSavePath, 250, 250);
}
$this->update($consoleId, $con['title'], $con['asin'], $con['url'], $con['salesrank'], $con['platform'], $con['publisher'], isset($con['releasedate']) ? $con['releasedate'] : null, $con['esrb'], $con['cover'], $con['consolegenreID'], isset($con['review']) ? $con['review'] : null);
}
return $consoleId;
}
/**
* Insert the NZB details into the database.
*
* @param $nzbDetails
*
* @return bool
*
* @access protected
*/
protected function insertNZB($nzbDetails)
{
// Make up a GUID for the release.
$this->relGuid = $this->releases->createGUID();
// Remove part count from subject.
$partLess = preg_replace('/(\\(\\d+\\/\\d+\\))*$/', 'yEnc', $nzbDetails['subject']);
// Remove added yEnc from above and anything after.
$subject = utf8_encode(trim(preg_replace('/yEnc.*$/i', 'yEnc', $partLess)));
$renamed = 0;
if ($nzbDetails['useFName']) {
// If the user wants to use the file name.. use it.
$cleanName = $nzbDetails['useFName'];
$renamed = 1;
} else {
// Pass the subject through release cleaner to get a nicer name.
$cleanName = $this->releaseCleaner->releaseCleaner($subject, $nzbDetails['from'], $nzbDetails['totalSize'], $nzbDetails['groupName']);
if (isset($cleanName['properlynamed'])) {
$cleanName = $cleanName['cleansubject'];
$renamed = isset($cleanName['properlynamed']) && $cleanName['properlynamed'] === true ? 1 : 0;
}
}
$escapedSubject = $this->pdo->escapeString($subject);
$escapedFromName = $this->pdo->escapeString($nzbDetails['from']);
// Look for a duplicate on name, poster and size.
$dupeCheck = $this->pdo->queryOneRow(sprintf('SELECT id FROM releases WHERE name = %s AND fromname = %s AND size BETWEEN %s AND %s', $escapedSubject, $escapedFromName, $this->pdo->escapeString($nzbDetails['totalSize'] * 0.99), $this->pdo->escapeString($nzbDetails['totalSize'] * 1.01)));
if ($dupeCheck === false) {
$escapedSearchName = $this->pdo->escapeString($cleanName);
// Insert the release into the DB.
$relID = $this->releases->insertRelease(['name' => $escapedSubject, 'searchname' => $escapedSearchName, 'totalpart' => $nzbDetails['totalFiles'], 'group_id' => $nzbDetails['group_id'], 'guid' => $this->pdo->escapeString($this->relGuid), 'postdate' => $this->pdo->escapeString($nzbDetails['postDate']), 'fromname' => $escapedFromName, 'size' => $this->pdo->escapeString($nzbDetails['totalSize']), 'categoryid' => $this->category->determineCategory($nzbDetails['group_id'], $cleanName), 'isrenamed' => $renamed, 'reqidstatus' => 0, 'preid' => 0, 'nzbstatus' => NZB::NZB_ADDED]);
} else {
//$this->echoOut('This release is already in our DB so skipping: ' . $subject);
return false;
}
if (isset($relID) && $relID === false) {
$this->echoOut('ERROR: Problem inserting: ' . $subject);
return false;
}
return true;
}
/**
* Return the parent and category name from the supplied categoryID.
* @param $ID
*
* @return string
*/
public function getNameByID($ID)
{
$parent = $this->pdo->queryOneRow(sprintf("SELECT title FROM category WHERE id = %d", substr($ID, 0, 1) . "000"));
$cat = $this->pdo->queryOneRow(sprintf("SELECT title FROM category WHERE id = %d", $ID));
return $parent["title"] . " " . $cat["title"];
}
/**
* Process each game, updating game information from Giantbomb
*
* @param $gameInfo
*
* @return bool
*/
public function updateGamesInfo($gameInfo)
{
$gen = new Genres(['Settings' => $this->pdo]);
$ri = new ReleaseImage($this->pdo);
$con = [];
// Process Steam first before giantbomb
// Steam has more details
$this->_gameResults = [];
$this->_getGame = new Steam();
$this->_classUsed = "steam";
$this->_getGame->cookie = $this->cookie;
$this->_getGame->searchTerm = $gameInfo['title'];
if ($this->_getGame->search() !== false) {
$this->_gameResults = $this->_getGame->getAll();
}
if (count($this->_gameResults) < 1) {
$this->_getGame = new Desura();
$this->_classUsed = "desura";
$this->_getGame->cookie = $this->cookie;
$this->_getGame->searchTerm = $gameInfo['title'];
if ($this->_getGame->search() !== false) {
$this->_gameResults = $this->_getGame->getAll();
}
}
if (count($this->_gameResults) < 1) {
$this->_getGame = new Greenlight();
$this->_classUsed = "gl";
$this->_getGame->cookie = $this->cookie;
$this->_getGame->searchTerm = $gameInfo['title'];
if ($this->_getGame->search() !== false) {
$this->_gameResults = $this->_getGame->getAll();
}
}
if (count($this->_gameResults) < 1) {
$this->_gameResults = (array) $this->fetchGiantBombID($gameInfo['title']);
if ($this->maxHitRequest === true) {
return false;
}
}
if (empty($this->_gameResults['title'])) {
return false;
}
if (!is_array($this->_gameResults)) {
return false;
}
if (count($this->_gameResults) > 1) {
$genreName = '';
switch ($this->_classUsed) {
case "desura":
if (isset($this->_gameResults['cover'])) {
$con['coverurl'] = (string) $this->_gameResults['cover'];
}
if (isset($this->_gameResults['backdrop'])) {
$con['backdropurl'] = (string) $this->_gameResults['backdrop'];
}
$con['title'] = (string) $this->_gameResults['title'];
$con['asin'] = $this->_gameResults['desuragameid'];
$con['url'] = (string) $this->_gameResults['directurl'];
if (isset($this->_gameResults['gamedetails']['Publisher'])) {
$con['publisher'] = (string) $this->_gameResults['gamedetails']['Publisher'];
} else {
$con['publisher'] = "Unknown";
}
if (isset($this->_gameResults['rating'])) {
$con['esrb'] = (string) $this->_gameResults['rating'];
} else {
$con['esrb'] = "Not Rated";
}
if (isset($this->_gameResults['description'])) {
$con['review'] = trim(strip_tags((string) $this->_gameResults['description']));
}
if (isset($this->_gameResults['trailer'])) {
$con['trailer'] = (string) $this->_gameResults['trailer'];
}
if (isset($this->_gameResults['gamedetails']['Genre'])) {
$genres = (string) $this->_gameResults['gamedetails']['Genre'];
$genreName = $this->_matchGenre($genres);
}
break;
case "gb":
$con['coverurl'] = (string) $this->_gameResults['image']['super_url'];
$con['title'] = (string) $this->_gameResults['name'];
$con['asin'] = $this->_gameID;
$con['url'] = (string) $this->_gameResults['site_detail_url'];
if (is_array($this->_gameResults['publishers'])) {
while (list($key) = each($this->_gameResults['publishers'])) {
if ($key == 0) {
$con['publisher'] = (string) $this->_gameResults['publishers'][$key]['name'];
}
}
} else {
$con['publisher'] = "Unknown";
}
if (is_array($this->_gameResults['original_game_rating'])) {
$con['esrb'] = (string) $this->_gameResults['original_game_rating'][0]['name'];
} else {
$con['esrb'] = (string) $this->_gameResults['original_game_rating']['name'];
}
$con['releasedate'] = (string) $this->_gameResults['original_release_date'];
if (isset($this->_gameResults['description'])) {
$con['review'] = trim(strip_tags((string) $this->_gameResults['description']));
}
if (isset($this->_gameResults['genres'][0]['name'])) {
$genres = (string) $this->_gameResults['genres'][0]['name'];
$genreName = $this->_matchGenre($genres);
}
break;
case "gl":
if (isset($this->_gameResults['cover'])) {
$con['coverurl'] = (string) $this->_gameResults['cover'];
}
if (isset($this->_gameResults['backdrop'])) {
$con['backdropurl'] = (string) $this->_gameResults['backdrop'];
}
$con['title'] = (string) $this->_gameResults['title'];
$con['asin'] = $this->_gameResults['greenlightgameid'];
$con['url'] = (string) $this->_gameResults['directurl'];
$con['publisher'] = "Unknown";
$con['esrb'] = "Not Rated";
if (isset($this->_gameResults['description'])) {
$con['review'] = trim(strip_tags((string) $this->_gameResults['description']));
}
if (isset($this->_gameResults['trailer'])) {
$con['trailer'] = (string) $this->_gameResults['trailer'];
}
if (isset($this->_gameResults['gamedetails']['Genre'])) {
$genres = (string) $this->_gameResults['gamedetails']['Genre'];
$genreName = $this->_matchGenre($genres);
}
break;
case "steam":
if (isset($this->_gameResults['cover'])) {
$con['coverurl'] = (string) $this->_gameResults['cover'];
}
if (isset($this->_gameResults['backdrop'])) {
$con['backdropurl'] = (string) $this->_gameResults['backdrop'];
}
$con['title'] = (string) $this->_gameResults['title'];
$con['asin'] = $this->_gameResults['steamgameid'];
$con['url'] = (string) $this->_gameResults['directurl'];
if (isset($this->_gameResults['gamedetails']['Publisher'])) {
$con['publisher'] = (string) $this->_gameResults['gamedetails']['Publisher'];
} else {
$con['publisher'] = "Unknown";
}
if (isset($this->_gameResults['rating'])) {
$con['esrb'] = (string) $this->_gameResults['rating'];
} else {
$con['esrb'] = "Not Rated";
}
if (!empty($this->_gameResults['gamedetails']['Release Date'])) {
$dateReleased = $this->_gameResults['gamedetails']['Release Date'];
if (!preg_match('#^\\s*(?P<month>\\w+)\\s+(?P<day>\\d{1,2}),?\\s+(?P<year>\\d{4})\\s*$#', $dateReleased)) {
if (preg_match('#^\\s*(?P<month>\\w+)\\s+(?P<year>\\d{4})\\s*$#', $dateReleased, $matches)) {
$dateReleased = "{$matches['month']} 1, {$matches['year']}";
}
}
$date = \DateTime::createFromFormat('M/j/Y', $dateReleased);
if ($date instanceof \DateTime) {
$con['releasedate'] = (string) $date->format('Y-m-d');
}
}
if (isset($this->_gameResults['description'])) {
$con['review'] = trim(strip_tags((string) $this->_gameResults['description']));
}
if (isset($this->_gameResults['trailer'])) {
$con['trailer'] = (string) $this->_gameResults['trailer'];
}
if (isset($this->_gameResults['gamedetails']['Genre'])) {
$genres = (string) $this->_gameResults['gamedetails']['Genre'];
$genreName = $this->_matchGenre($genres);
}
break;
default:
return false;
}
} else {
return false;
}
// Load genres.
$defaultGenres = $gen->getGenres(Genres::GAME_TYPE);
$genreassoc = [];
foreach ($defaultGenres as $dg) {
$genreassoc[$dg['id']] = strtolower($dg['title']);
}
// Prepare database values.
if (isset($con['coverurl'])) {
$con['cover'] = 1;
} else {
$con['cover'] = 0;
}
if (isset($con['backdropurl'])) {
$con['backdrop'] = 1;
} else {
$con['backdrop'] = 0;
}
if (!isset($con['trailer'])) {
$con['trailer'] = 0;
}
if (empty($con['title'])) {
$con['title'] = $gameInfo['title'];
}
if (!isset($con['releasedate'])) {
$con['releasedate'] = "";
}
if ($con['releasedate'] == "''") {
$con['releasedate'] = "";
}
if (!isset($con['review'])) {
$con['review'] = 'No Review';
}
$con['classused'] = $this->_classUsed;
if (empty($genreName)) {
$genreName = 'Unknown';
}
if (in_array(strtolower($genreName), $genreassoc)) {
$genreKey = array_search(strtolower($genreName), $genreassoc);
} else {
$genreKey = $this->pdo->queryInsert(sprintf("\n\t\t\t\t\tINSERT INTO genres (title, type)\n\t\t\t\t\tVALUES (%s, %d)", $this->pdo->escapeString($genreName), Genres::GAME_TYPE));
}
$con['gamesgenre'] = $genreName;
$con['gamesgenreID'] = $genreKey;
$check = $this->pdo->queryOneRow(sprintf('
SELECT id
FROM gamesinfo
WHERE asin = %s', $this->pdo->escapeString($con['asin'])));
if ($check === false) {
$gamesId = $this->pdo->queryInsert(sprintf("\n\t\t\t\t\tINSERT INTO gamesinfo\n\t\t\t\t\t\t(title, asin, url, publisher, genre_id, esrb, releasedate, review, cover, backdrop, trailer, classused, createddate, updateddate)\n\t\t\t\t\tVALUES (%s, %s, %s, %s, %s, %s, %s, %s, %d, %d, %s, %s, NOW(), NOW())", $this->pdo->escapeString($con['title']), $this->pdo->escapeString($con['asin']), $this->pdo->escapeString($con['url']), $this->pdo->escapeString($con['publisher']), $con['gamesgenreID'] == -1 ? "null" : $con['gamesgenreID'], $this->pdo->escapeString($con['esrb']), $con['releasedate'] != "" ? $this->pdo->escapeString($con['releasedate']) : "null", $this->pdo->escapeString(substr($con['review'], 0, 3000)), $con['cover'], $con['backdrop'], $this->pdo->escapeString($con['trailer']), $this->pdo->escapeString($con['classused'])));
} else {
$gamesId = $check['id'];
$this->pdo->queryExec(sprintf('
UPDATE gamesinfo
SET
title = %s, asin = %s, url = %s, publisher = %s, genre_id = %s,
esrb = %s, releasedate = %s, review = %s, cover = %d, backdrop = %d, trailer = %s, classused = %s, updateddate = NOW()
WHERE id = %d', $this->pdo->escapeString($con['title']), $this->pdo->escapeString($con['asin']), $this->pdo->escapeString($con['url']), $this->pdo->escapeString($con['publisher']), $con['gamesgenreID'] == -1 ? "null" : $con['gamesgenreID'], $this->pdo->escapeString($con['esrb']), $con['releasedate'] != "" ? $this->pdo->escapeString($con['releasedate']) : "null", $this->pdo->escapeString(substr($con['review'], 0, 3000)), $con['cover'], $con['backdrop'], $this->pdo->escapeString($con['trailer']), $this->pdo->escapeString($con['classused']), $gamesId));
}
if ($gamesId) {
if ($this->echoOutput) {
$this->pdo->log->doEcho($this->pdo->log->header("Added/updated game from " . $this->_classUsed . ": ") . $this->pdo->log->alternateOver(" Title: ") . $this->pdo->log->primary($con['title']));
}
if ($con['cover'] === 1) {
$con['cover'] = $ri->saveImage($gamesId, $con['coverurl'], $this->imgSavePath, 250, 250);
}
if ($con['backdrop'] === 1) {
$con['backdrop'] = $ri->saveImage($gamesId . '-backdrop', $con['backdropurl'], $this->imgSavePath, 1920, 1024);
}
} else {
if ($this->echoOutput) {
$this->pdo->log->doEcho($this->pdo->log->headerOver("Nothing to update: ") . $this->pdo->log->primary($con['title'] . ' (PC)'));
}
}
return $gamesId;
}
/**
* @param $column
* @param $table
*
* @return array|bool
*/
public function getFirstInstance($column, $table)
{
return $this->pdo->queryOneRow(sprintf("\n\t\t\t\t\t\tSELECT %1\$s\n\t\t\t\t\t\tFROM %2\$s\n\t\t\t\t\t\tORDER BY %1\$s ASC", $column, $table));
}
public function data_getIndex()
{
return $this->pdo->queryOneRow(sprintf("SELECT * FROM content WHERE status = 1 AND contenttype = %d", Contents::TYPEINDEX));
}
/**
* @param $title
* @param $year
* @param object|null $amazdata
*
* @return bool
*/
public function updateMusicInfo($title, $year, $amazdata = null)
{
$gen = new Genres(['Settings' => $this->pdo]);
$ri = new ReleaseImage($this->pdo);
$titlepercent = 0;
$mus = [];
if ($title != '') {
$amaz = $this->fetchAmazonProperties($title);
} else {
if ($amazdata != null) {
$amaz = $amazdata;
} else {
$amaz = false;
}
}
if (!$amaz) {
return false;
}
if (isset($amaz->Items->Item->ItemAttributes->Title)) {
$mus['title'] = (string) $amaz->Items->Item->ItemAttributes->Title;
if (empty($mus['title'])) {
return false;
}
} else {
return false;
}
// Load genres.
$defaultGenres = $gen->getGenres(Genres::MUSIC_TYPE);
$genreassoc = [];
foreach ($defaultGenres as $dg) {
$genreassoc[$dg['id']] = strtolower($dg['title']);
}
// Get album properties.
$mus['coverurl'] = (string) $amaz->Items->Item->LargeImage->URL;
if ($mus['coverurl'] != "") {
$mus['cover'] = 1;
} else {
$mus['cover'] = 0;
}
$mus['asin'] = (string) $amaz->Items->Item->ASIN;
$mus['url'] = (string) $amaz->Items->Item->DetailPageURL;
$mus['url'] = str_replace("%26tag%3Dws", "%26tag%3Dopensourceins%2D21", $mus['url']);
$mus['salesrank'] = (string) $amaz->Items->Item->SalesRank;
if ($mus['salesrank'] == "") {
$mus['salesrank'] = 'null';
}
$mus['artist'] = (string) $amaz->Items->Item->ItemAttributes->Artist;
if (empty($mus['artist'])) {
$mus['artist'] = (string) $amaz->Items->Item->ItemAttributes->Creator;
if (empty($mus['artist'])) {
$mus['artist'] = "";
}
}
$mus['publisher'] = (string) $amaz->Items->Item->ItemAttributes->Publisher;
$mus['releasedate'] = $this->pdo->escapeString((string) $amaz->Items->Item->ItemAttributes->ReleaseDate);
if ($mus['releasedate'] == "''") {
$mus['releasedate'] = 'null';
}
$mus['review'] = "";
if (isset($amaz->Items->Item->EditorialReviews)) {
$mus['review'] = trim(strip_tags((string) $amaz->Items->Item->EditorialReviews->EditorialReview->Content));
}
$mus['year'] = $year;
if ($mus['year'] == "") {
$mus['year'] = $mus['releasedate'] != 'null' ? substr($mus['releasedate'], 1, 4) : date("Y");
}
$mus['tracks'] = "";
if (isset($amaz->Items->Item->Tracks)) {
$tmpTracks = (array) $amaz->Items->Item->Tracks->Disc;
$tracks = $tmpTracks['Track'];
$mus['tracks'] = is_array($tracks) && !empty($tracks) ? implode('|', $tracks) : '';
}
similar_text($mus['artist'] . " " . $mus['title'], $title, $titlepercent);
if ($titlepercent < 60) {
return false;
}
$genreKey = -1;
$genreName = '';
if (isset($amaz->Items->Item->BrowseNodes)) {
// Had issues getting this out of the browsenodes obj.
// Workaround is to get the xml and load that into its own obj.
$amazGenresXml = $amaz->Items->Item->BrowseNodes->asXml();
$amazGenresObj = simplexml_load_string($amazGenresXml);
$amazGenres = $amazGenresObj->xpath("//BrowseNodeId");
foreach ($amazGenres as $amazGenre) {
$currNode = trim($amazGenre[0]);
if (empty($genreName)) {
$genreMatch = $this->matchBrowseNode($currNode);
if ($genreMatch !== false) {
$genreName = $genreMatch;
break;
}
}
}
if (in_array(strtolower($genreName), $genreassoc)) {
$genreKey = array_search(strtolower($genreName), $genreassoc);
} else {
$genreKey = $this->pdo->queryInsert(sprintf("\n\t\t\t\t\t\t\t\t\t\tINSERT INTO genres (title, type)\n\t\t\t\t\t\t\t\t\t\tVALUES (%s, %d)", $this->pdo->escapeString($genreName), Genres::MUSIC_TYPE));
}
}
$mus['musicgenre'] = $genreName;
$mus['musicgenreid'] = $genreKey;
$check = $this->pdo->queryOneRow(sprintf('
SELECT id
FROM musicinfo
WHERE asin = %s', $this->pdo->escapeString($mus['asin'])));
if ($check === false) {
$musicId = $this->pdo->queryInsert(sprintf("\n\t\t\t\t\tINSERT INTO musicinfo\n\t\t\t\t\t\t(title, asin, url, salesrank, artist, publisher,\n\t\t\t\t\t\treleasedate, review, year, genre_id, tracks, cover, createddate, updateddate)\n\t\t\t\t\tVALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %d, now(), now())", $this->pdo->escapeString($mus['title']), $this->pdo->escapeString($mus['asin']), $this->pdo->escapeString($mus['url']), $mus['salesrank'], $this->pdo->escapeString($mus['artist']), $this->pdo->escapeString($mus['publisher']), $mus['releasedate'], $this->pdo->escapeString($mus['review']), $this->pdo->escapeString($mus['year']), $mus['musicgenreid'] == -1 ? "null" : $mus['musicgenreid'], $this->pdo->escapeString($mus['tracks']), $mus['cover']));
} else {
$musicId = $check['id'];
$this->pdo->queryExec(sprintf('
UPDATE musicinfo
SET title = %s, asin = %s, url = %s, salesrank = %s, artist = %s,
publisher = %s, releasedate = %s, review = %s, year = %s, genre_id = %s, tracks = %s, cover = %s,
updateddate = NOW()
WHERE id = %d', $this->pdo->escapeString($mus['title']), $this->pdo->escapeString($mus['asin']), $this->pdo->escapeString($mus['url']), $mus['salesrank'], $this->pdo->escapeString($mus['artist']), $this->pdo->escapeString($mus['publisher']), $mus['releasedate'], $this->pdo->escapeString($mus['review']), $this->pdo->escapeString($mus['year']), $mus['musicgenreid'] == -1 ? "null" : $mus['musicgenreid'], $this->pdo->escapeString($mus['tracks']), $mus['cover'], $musicId));
}
if ($musicId) {
if ($this->echooutput) {
$this->pdo->log->doEcho($this->pdo->log->header("\nAdded/updated album: ") . $this->pdo->log->alternateOver(" Artist: ") . $this->pdo->log->primary($mus['artist']) . $this->pdo->log->alternateOver(" Title: ") . $this->pdo->log->primary($mus['title']) . $this->pdo->log->alternateOver(" Year: ") . $this->pdo->log->primary($mus['year']));
}
$mus['cover'] = $ri->saveImage($musicId, $mus['coverurl'], $this->imgSavePath, 250, 250);
} else {
if ($this->echooutput) {
if ($mus["artist"] == "") {
$artist = "";
} else {
$artist = "Artist: " . $mus['artist'] . ", Album: ";
}
$this->pdo->log->doEcho($this->pdo->log->headerOver("Nothing to update: ") . $this->pdo->log->primary($artist . $mus['title'] . " (" . $mus['year'] . ")"));
}
}
return $musicId;
}
<?php
require_once dirname(__FILE__) . '/../../../www/config.php';
use nzedb\db\Settings;
$pdo = new Settings();
$covers = $updated = $deleted = 0;
if ($argc == 1 || $argv[1] != 'true') {
exit($pdo->log->error("\nThis script will check all images in covers/xxx and compare to db->xxxinfo.\nTo run:\nphp {$argv['0']} true\n"));
}
$row = $pdo->queryOneRow("SELECT value FROM settings WHERE setting = 'coverspath'");
if ($row !== false) {
\nzedb\utility\Utility::setCoversConstant($row['value']);
} else {
die("Unable to set Covers' constant!\n");
}
$path2covers = nZEDb_COVERS . 'xxx' . DS;
$dirItr = new RecursiveDirectoryIterator($path2covers);
$itr = new RecursiveIteratorIterator($dirItr, RecursiveIteratorIterator::LEAVES_ONLY);
foreach ($itr as $filePath) {
if (is_file($filePath) && preg_match('/-cover\\.jpg/', $filePath)) {
preg_match('/(\\d+)-cover\\.jpg/', basename($filePath), $match);
if (isset($match[1])) {
$run = $pdo->queryDirect("UPDATE xxxinfo SET cover = 1 WHERE cover = 0 AND id = " . $match[1]);
if ($run->rowCount() >= 1) {
$covers++;
} else {
$run = $pdo->queryDirect("SELECT id FROM xxxinfo WHERE id = " . $match[1]);
if ($run->rowCount() == 0) {
echo $pdo->log->info($filePath . " not found in db.");
}
}
$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}");
//collection
$pdo->queryExec("INSERT IGNORE INTO collections_" . $row['group_id'] . " (subject, fromname, date, xref, totalfiles, group_id, collectionhash, dateadded, filecheck, filesize, releaseid) " . "SELECT subject, fromname, date, xref, totalfiles, group_id, collectionhash, dateadded, filecheck, filesize, releaseid FROM collections WHERE group_id = {$row['group_id']}");
$collections = $pdo->queryOneRow("SELECT COUNT(*) AS cnt FROM collections where group_id = " . $row['group_id']);
$ncollections = $pdo->queryOneRow("SELECT COUNT(*) AS cnt FROM collections_" . $row['group_id']);
echo $pdo->log->primary("Group {$groupName}, Collections = {$collections['cnt']} [{$ncollections['cnt']}]");
//binaries
$pdo->queryExec("INSERT IGNORE INTO binaries_{$row['group_id']} (name, filenumber, totalparts, currentparts, binaryhash, partcheck, partsize, collection_id) " . "SELECT name, filenumber, totalparts, currentparts, binaryhash, partcheck, partsize, n.id FROM binaries b " . "INNER JOIN collections c ON b.collection_id = c.id " . "INNER JOIN collections_{$row['group_id']} n ON c.collectionhash = n.collectionhash AND c.group_id = {$row['group_id']}");
$binaries = $pdo->queryOneRow("SELECT COUNT(*) AS cnt FROM binaries b INNER JOIN collections c ON b.collection_id = c.id where c.group_id = {$row['group_id']}");
$nbinaries = $pdo->queryOneRow("SELECT COUNT(*) AS cnt FROM binaries_{$row['group_id']}");
echo $pdo->log->primary("Group {$groupName}, Binaries = {$binaries['cnt']} [{$nbinaries['cnt']}]");
/**
* Get tv show information for a user.
*
* @param int $uID ID of the user.
* @param int $rageID ID of the TV show.
*
* @return array|bool
*/
public function getShow($uID, $rageID)
{
return $this->pdo->queryOneRow(sprintf("\n\t\t\t\tSELECT userseries.*, tvr.releasetitle\n\t\t\t\tFROM userseries\n\t\t\t\tLEFT OUTER JOIN tvrage tvr ON tvr.rageid = userseries.rageid\n\t\t\t\tWHERE userseries.user_id = %d\n\t\t\t\tAND userseries.rageid = %d", $uID, $rageID));
}
