PHP Settings::queryCalc方法代碼示例

 /**
  * Get movie releases with covers for movie browse page.
  *
  * @param       $cat
  * @param       $start
  * @param       $num
  * @param       $orderBy
  * @param       $maxAge
  * @param array $excludedCats
  *
  * @return bool|\PDOStatement
  */
 public function getMovieRange($cat, $start, $num, $orderBy, $maxAge = -1, $excludedCats = [])
 {
     $catsrch = '';
     if (count($cat) > 0 && $cat[0] != -1) {
         $catsrch = (new Category(['Settings' => $this->pdo]))->getCategorySearch($cat);
     }
     $order = $this->getMovieOrder($orderBy);
     $movies = $this->pdo->queryCalc(sprintf("\n\t\t\t\t\tSELECT SQL_CALC_FOUND_ROWS\n\t\t\t\t\t\tm.imdbid,\n\t\t\t\t\t\tGROUP_CONCAT(r.id ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_id\n\t\t\t\t\tFROM movieinfo m\n\t\t\t\t\tLEFT JOIN releases r USING (imdbid)\n\t\t\t\t\tWHERE r.nzbstatus = 1\n\t\t\t\t\tAND m.title != ''\n\t\t\t\t\tAND m.imdbid != '0000000'\n\t\t\t\t\tAND r.passwordstatus %s\n\t\t\t\t\tAND %s %s %s %s\n\t\t\t\t\tGROUP BY m.imdbid\n\t\t\t\t\tORDER BY %s %s %s", $this->showPasswords, $this->getBrowseBy(), $catsrch, $maxAge > 0 ? 'AND r.postdate > NOW() - INTERVAL ' . $maxAge . 'DAY ' : '', count($excludedCats) > 0 ? ' AND r.categoryid NOT IN (' . implode(',', $excludedCats) . ')' : '', $order[0], $order[1], $start === false ? '' : ' LIMIT ' . $num . ' OFFSET ' . $start), true, nZEDb_CACHE_EXPIRY_MEDIUM);
     $movieIDs = $releaseIDs = false;
     if (is_array($movies['result'])) {
         foreach ($movies['result'] as $movie => $id) {
             $movieIDs[] = $id['imdbid'];
             $releaseIDs[] = $id['grp_release_id'];
         }
     }
     $catsrchCheck = '';
     if (!empty($catsrch)) {
         $catsrchCheck = 'AND ' . $catsrch;
     }
     $sql = sprintf("\n\t\t\tSELECT\n\t\t\t\tGROUP_CONCAT(r.id ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_id,\n\t\t\t\tGROUP_CONCAT(r.rarinnerfilecount ORDER BY r.postdate DESC SEPARATOR ',') AS grp_rarinnerfilecount,\n\t\t\t\tGROUP_CONCAT(r.haspreview ORDER BY r.postdate DESC SEPARATOR ',') AS grp_haspreview,\n\t\t\t\tGROUP_CONCAT(r.passwordstatus ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_password,\n\t\t\t\tGROUP_CONCAT(r.guid ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_guid,\n\t\t\t\tGROUP_CONCAT(rn.releaseid ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_nfoid,\n\t\t\t\tGROUP_CONCAT(g.name ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_grpname,\n\t\t\t\tGROUP_CONCAT(r.searchname ORDER BY r.postdate DESC SEPARATOR '#') AS grp_release_name,\n\t\t\t\tGROUP_CONCAT(r.postdate ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_postdate,\n\t\t\t\tGROUP_CONCAT(r.size ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_size,\n\t\t\t\tGROUP_CONCAT(r.totalpart ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_totalparts,\n\t\t\t\tGROUP_CONCAT(r.comments ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_comments,\n\t\t\t\tGROUP_CONCAT(r.grabs ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_grabs,\n\t\t\t\tGROUP_CONCAT(df.failed ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_failed,\n\t\t\t\tGROUP_CONCAT(cp.title, ' > ', c.title ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_catname,\n\t\t\tm.*,\n\t\t\tg.name AS group_name,\n\t\t\trn.releaseid AS nfoid\n\t\t\tFROM releases r\n\t\t\tLEFT OUTER JOIN groups g ON g.id = r.group_id\n\t\t\tLEFT OUTER JOIN release_nfos rn ON rn.releaseid = r.id\n\t\t\tLEFT OUTER 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\tINNER JOIN movieinfo m ON m.imdbid = r.imdbid\n\t\t\tWHERE m.imdbid IN (%s)\n\t\t\tAND r.id IN (%s) %s\n\t\t\tGROUP BY m.imdbid\n\t\t\tORDER BY %s %s", is_array($movieIDs) ? implode(',', $movieIDs) : -1, is_array($releaseIDs) ? implode(',', $releaseIDs) : -1, $catsrchCheck, $order[0], $order[1]);
     $return = $this->pdo->query($sql, true, nZEDb_CACHE_EXPIRY_MEDIUM);
     if (!empty($return)) {
         $return[0]['_totalcount'] = isset($movies['total']) ? $movies['total'] : 0;
     }
     return $return;
 }

 /**
  * @param       $cat
  * @param       $start
  * @param       $num
  * @param       $orderby
  * @param array $excludedcats
  *
  * @return array
  */
 public function getMusicRange($cat, $start, $num, $orderby, array $excludedcats = [])
 {
     $browseby = $this->getBrowseBy();
     $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->getMusicOrder($orderby);
     $music = $this->pdo->queryCalc(sprintf("\n\t\t\t\tSELECT SQL_CALC_FOUND_ROWS\n\t\t\t\t\tm.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 musicinfo m\n\t\t\t\tLEFT JOIN releases r ON r.musicinfoid = m.id\n\t\t\t\tWHERE r.nzbstatus = 1\n\t\t\t\tAND m.title != ''\n\t\t\t\tAND m.cover = 1\n\t\t\t\tAND r.passwordstatus %s\n\t\t\t\tAND %s %s %s\n\t\t\t\tGROUP BY m.id\n\t\t\t\tORDER BY %s %s %s", Releases::showPasswords($this->pdo), $browseby, $catsrch, $exccatlist, $order[0], $order[1], $start === false ? '' : ' LIMIT ' . $num . ' OFFSET ' . $start), true, nZEDb_CACHE_EXPIRY_MEDIUM);
     $musicIDs = $releaseIDs = false;
     if (is_array($music['result'])) {
         foreach ($music['result'] as $mus => $id) {
             $musicIDs[] = $id['id'];
             $releaseIDs[] = $id['grp_release_id'];
         }
     }
     $sql = sprintf("\n\t\t\tSELECT\n\t\t\t\tGROUP_CONCAT(r.id ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_id,\n\t\t\t\tGROUP_CONCAT(r.rarinnerfilecount ORDER BY r.postdate DESC SEPARATOR ',') as grp_rarinnerfilecount,\n\t\t\t\tGROUP_CONCAT(r.haspreview ORDER BY r.postdate DESC SEPARATOR ',') AS grp_haspreview,\n\t\t\t\tGROUP_CONCAT(r.passwordstatus ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_password,\n\t\t\t\tGROUP_CONCAT(r.guid ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_guid,\n\t\t\t\tGROUP_CONCAT(rn.releaseid ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_nfoid,\n\t\t\t\tGROUP_CONCAT(g.name ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_grpname,\n\t\t\t\tGROUP_CONCAT(r.searchname ORDER BY r.postdate DESC SEPARATOR '#') AS grp_release_name,\n\t\t\t\tGROUP_CONCAT(r.postdate ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_postdate,\n\t\t\t\tGROUP_CONCAT(r.size ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_size,\n\t\t\t\tGROUP_CONCAT(r.totalpart ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_totalparts,\n\t\t\t\tGROUP_CONCAT(r.comments ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_comments,\n\t\t\t\tGROUP_CONCAT(r.grabs ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_grabs,\n\t\t\t\tGROUP_CONCAT(df.failed ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_failed,\n\t\t\t\tm.*,\n\t\t\t\tr.musicinfoid, r.haspreview,\n\t\t\t\tg.name AS group_name,\n\t\t\t\trn.releaseid AS nfoid\n\t\t\tFROM releases r\n\t\t\tLEFT OUTER JOIN groups g ON g.id = r.group_id\n\t\t\tLEFT OUTER JOIN release_nfos rn ON rn.releaseid = r.id\n\t\t\tLEFT OUTER JOIN dnzb_failures df ON df.release_id = r.id\n\t\t\tINNER JOIN musicinfo m ON m.id = r.musicinfoid\n\t\t\tWHERE m.id IN (%s)\n\t\t\tAND r.id IN (%s)\n\t\t\tAND %s\n\t\t\tGROUP BY m.id\n\t\t\tORDER BY %s %s", is_array($musicIDs) ? implode(',', $musicIDs) : -1, is_array($releaseIDs) ? implode(',', $releaseIDs) : -1, $catsrch, $order[0], $order[1]);
     $return = $this->pdo->query($sql, true, nZEDb_CACHE_EXPIRY_MEDIUM);
     if (!empty($return)) {
         $return[0]['_totalcount'] = isset($music['total']) ? $music['total'] : 0;
     }
     return $return;
 }

 /**
  * Get movie releases with covers for xxx browse page.
  *
  * @param       $cat
  * @param       $start
  * @param       $num
  * @param       $orderBy
  * @param       $maxAge
  * @param array $excludedCats
  *
  * @return array
  */
 public function getXXXRange($cat, $start, $num, $orderBy, $maxAge = -1, $excludedCats = [])
 {
     $catsrch = '';
     if (count($cat) > 0 && $cat[0] != -1) {
         $catsrch = (new Category(['Settings' => $this->pdo]))->getCategorySearch($cat);
     }
     $order = $this->getXXXOrder($orderBy);
     $xxxmovies = $this->pdo->queryCalc(sprintf("\n\t\t\t\tSELECT SQL_CALC_FOUND_ROWS\n\t\t\t\t\txxx.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 xxxinfo xxx\n\t\t\t\tLEFT JOIN releases r ON xxx.id = r.xxxinfo_id\n\t\t\t\tWHERE r.nzbstatus = 1\n\t\t\t\tAND xxx.title != ''\n\t\t\t\tAND r.passwordstatus %s\n\t\t\t\tAND %s %s %s %s\n\t\t\t\tGROUP BY xxx.id\n\t\t\t\tORDER BY %s %s %s", $this->showPasswords, $this->getBrowseBy(), $catsrch, $maxAge > 0 ? 'AND r.postdate > NOW() - INTERVAL ' . $maxAge . 'DAY ' : '', count($excludedCats) > 0 ? ' AND r.categoryid NOT IN (' . implode(',', $excludedCats) . ')' : '', $order[0], $order[1], $start === false ? '' : ' LIMIT ' . $num . ' OFFSET ' . $start), true, nZEDb_CACHE_EXPIRY_MEDIUM);
     $xxxIDs = $releaseIDs = false;
     if (is_array($xxxmovies['result'])) {
         foreach ($xxxmovies['result'] as $xxx => $id) {
             $xxxIDs[] = $id['id'];
             $releaseIDs[] = $id['grp_release_id'];
         }
     }
     $sql = sprintf("\n\t\t\tSELECT\n\t\t\t\tGROUP_CONCAT(r.id ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_id,\n\t\t\t\tGROUP_CONCAT(r.rarinnerfilecount ORDER BY r.postdate DESC SEPARATOR ',') as grp_rarinnerfilecount,\n\t\t\t\tGROUP_CONCAT(r.haspreview ORDER BY r.postdate DESC SEPARATOR ',') AS grp_haspreview,\n\t\t\t\tGROUP_CONCAT(r.passwordstatus ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_password,\n\t\t\t\tGROUP_CONCAT(r.guid ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_guid,\n\t\t\t\tGROUP_CONCAT(rn.releaseid ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_nfoid,\n\t\t\t\tGROUP_CONCAT(g.name ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_grpname,\n\t\t\t\tGROUP_CONCAT(r.searchname ORDER BY r.postdate DESC SEPARATOR '#') AS grp_release_name,\n\t\t\t\tGROUP_CONCAT(r.postdate ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_postdate,\n\t\t\t\tGROUP_CONCAT(r.size ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_size,\n\t\t\t\tGROUP_CONCAT(r.totalpart ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_totalparts,\n\t\t\t\tGROUP_CONCAT(r.comments ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_comments,\n\t\t\t\tGROUP_CONCAT(r.grabs ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_grabs,\n\t\t\txxx.*, UNCOMPRESS(xxx.plot) AS plot,\n\t\t\tg.name AS group_name,\n\t\t\trn.releaseid AS nfoid\n\t\t\tFROM releases r\n\t\t\tLEFT OUTER JOIN groups g ON g.id = r.group_id\n\t\t\tLEFT OUTER JOIN release_nfos rn ON rn.releaseid = r.id\n\t\t\tINNER JOIN xxxinfo xxx ON xxx.id = r.xxxinfo_id\n\t\t\tWHERE r.nzbstatus = 1\n\t\t\tAND xxx.id IN (%s)\n\t\t\tAND xxx.title != ''\n\t\t\tAND r.passwordstatus %s\n\t\t\tAND %s %s %s %s\n\t\t\tGROUP BY xxx.id\n\t\t\tORDER BY %s %s", is_array($xxxIDs) ? implode(',', $xxxIDs) : -1, $this->showPasswords, $this->getBrowseBy(), $catsrch, $maxAge > 0 ? 'AND r.postdate > NOW() - INTERVAL ' . $maxAge . 'DAY ' : '', count($excludedCats) > 0 ? ' AND r.categoryid NOT IN (' . implode(',', $excludedCats) . ')' : '', $order[0], $order[1]);
     $return = $this->pdo->query($sql, true, nZEDb_CACHE_EXPIRY_MEDIUM);
     if (!empty($return)) {
         $return[0]['_totalcount'] = isset($xxxmovies['total']) ? $xxxmovies['total'] : 0;
     }
     return $return;
 }