getString('mode', ''); $query = $request->getString('query', ''); $limit = $request->getInt('limit', 60); $offset = $request->getInt('offset', 0); $ldb = \Db::pdo(); $caldb = new PDO('sqlite:' . Config::get(Config::CALIBRE_DB)); $ids = array_filter( explode(",", $request->getString('ids', '')), 'intval'); if ($mode == "favorites") { $fav_sth = $ldb->prepare("SELECT bookid FROM epube_favorites WHERE owner = ?"); $fav_sth->execute([$owner]); $fav_ids = []; while ($line = $fav_sth->fetch()) { array_push($fav_ids, (int) $line["bookid"]); } if (empty($fav_ids)) { return (new Response())->withBody(json_encode(["books" => [], "total" => 0])); } $ids_qpart = "books.id IN (" . implode(",", $fav_ids) . ")"; } else if (count($ids) > 0) { $ids_qpart = "books.id IN (" . implode(",", $ids) . ")"; } else { $ids_qpart = "1"; } $order_by = $query ? "author_sort, series_name, series_index, title, books.id" : "books.id DESC"; $sth = $caldb->prepare("SELECT books.*, s.name AS series_name, c.text AS comment, (SELECT id FROM data WHERE book = books.id AND format = 'EPUB' LIMIT 1) AS epub_id FROM books LEFT JOIN books_series_link AS bsl ON (bsl.book = books.id) LEFT JOIN series AS s ON (bsl.series = s.id) LEFT JOIN comments AS c ON (c.book = books.id) WHERE ((:query = '') OR ( LOWER(books.author_sort) LIKE LOWER(:query) OR LOWER(books.title) LIKE LOWER(:query) OR LOWER(series_name) LIKE LOWER(:query) )) AND $ids_qpart ORDER BY $order_by LIMIT :limit OFFSET :offset"); $sth->execute([':limit' => $limit, ':offset' => $offset, ':query' => '%' . $query . '%']); $books = []; $total = 0; while ($line = $sth->fetch()) { $cover_link = ""; if ($line['has_cover']) { $cover_filename = Config::get(Config::BOOKS_DIR) . "/" . $line["path"] . "/" . "cover.jpg"; if (file_exists($cover_filename)) { $cover_mtime = filemtime($cover_filename); $cover_link = "backend.php?" . http_build_query(["op" => "cover", "id" => $line["id"], "ts" => $cover_mtime]); } } $in_progress = false; $is_read = false; if ($line["epub_id"]) { $lastread_sth = $ldb->prepare("SELECT lastread, total_pages FROM epube_books, epube_pagination WHERE epube_pagination.bookid = epube_books.bookid AND epube_books.bookid = ? AND owner = ?"); $lastread_sth->execute([$line['epub_id'], $owner]); if ($lastread_line = $lastread_sth->fetch()) { $lastread = $lastread_line["lastread"]; $total_pages = $lastread_line["total_pages"]; $is_read = $total_pages - $lastread < 5; $in_progress = $lastread > 1; } } $data_sth = $caldb->prepare("SELECT * FROM data WHERE book = ? LIMIT 3"); $data_sth->execute([$line['id']]); $download_formats = []; while ($data_line = $data_sth->fetch()) { if ($data_line["format"] != "ORIGINAL_EPUB") { $download_formats[] = [ "format" => $data_line["format"], "id" => $data_line["id"], ]; } } $is_fav = false; $fav_sth = $ldb->prepare("SELECT id FROM epube_favorites WHERE bookid = ? AND owner = ? LIMIT 1"); $fav_sth->execute([$line['id'], $owner]); if ($fav_sth->fetch()) { $is_fav = true; } $books[] = [ "id" => $line["id"], "title" => $line["title"], "author_sort" => $line["author_sort"], "series_name" => $line["series_name"] ?? "", "series_index" => $line["series_index"] ?? 0, "cover_link" => $cover_link, "has_cover" => $line["has_cover"] == 1, "in_progress" => $in_progress, "is_read" => $is_read, "is_fav" => $is_fav, "epub_id" => $line["epub_id"] ?? 0, "download_formats" => $download_formats, "comment" => \Sanitizer::sanitize($line["comment"] ?? "") ]; } // count total for pagination $count_sth = $caldb->prepare("SELECT COUNT(*) FROM books LEFT JOIN books_series_link AS bsl ON (bsl.book = books.id) LEFT JOIN series AS s ON (bsl.series = s.id) WHERE ((:query = '%%') OR ( LOWER(books.author_sort) LIKE LOWER(:query) OR LOWER(books.title) LIKE LOWER(:query) OR LOWER(s.name) LIKE LOWER(:query) )) AND $ids_qpart"); $count_sth->execute([':query' => '%' . $query . '%']); $total = (int) $count_sth->fetchColumn(); return (new Response())->withBody(json_encode([ "books" => $books, "total" => $total, "offset" => $offset, "limit" => $limit, ])); } }