From 0b2d0695ae40aa1b75eb48de6a2fa2e81bc9e225 Mon Sep 17 00:00:00 2001
From: "OHASHI, Norikazu" <katz@neko-mori.sakura.ne.jp>
Date: Wed, 14 Aug 2019 22:59:09 +0900
Subject: [PATCH] =?utf8?q?=E4=B8=80=E8=A6=A7=E3=81=AE=E6=89=B1=E3=81=84?=
 =?utf8?q?=E3=82=92View=E3=81=A7=E8=A1=8C=E3=81=86=E3=82=88=E3=81=86?=
 =?utf8?q?=E3=81=AB=E4=BF=AE=E6=AD=A3?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

---
 create_table.sql                   |  6 ++++
 sinatra/app/controllers/web_gui.rb |  7 ++--
 sinatra/app/models/books_db.rb     | 54 +++++++++++++++++++-----------
 3 files changed, 44 insertions(+), 23 deletions(-)

diff --git a/create_table.sql b/create_table.sql
index bffdd53..fc3597a 100644
--- a/create_table.sql
+++ b/create_table.sql
@@ -42,3 +42,9 @@ CREATE TABLE book_covers (
        cover LONGBLOB NOT NULL,
        create_at DATETIME NOT NULL,
        update_at DATETIME NOT NULL);
+
+CREATE VIEW book_fulls (isbn, title, volume, author, publisher, user_id) AS
+       SELECT B.isbn AS isbn, B.title AS title, B.volume AS volume,
+       B.author AS author, B.publisher AS publisher, C.user_id AS user_id
+       FROM (books AS  B left join book_collections AS C
+            ON((B.isbn = C.isbn)))；
diff --git a/sinatra/app/controllers/web_gui.rb b/sinatra/app/controllers/web_gui.rb
index 457cc78..623e614 100644
--- a/sinatra/app/controllers/web_gui.rb
+++ b/sinatra/app/controllers/web_gui.rb
@@ -97,7 +97,7 @@ class WebGui < Sinatra::Base
       # アカウント作成
       id = UserAccount.createAccount(name, full_name, email, passwd)
       session[:userId] = id;
-      redirect "/user_home"
+      redirect to('/user_home')
     rescue UserAccount::AlreadyInstanceError 
       raise WebError.new(status: 406, message: "すでに登録済みのアカウント名が指定されています。")
     end
@@ -120,7 +120,7 @@ class WebGui < Sinatra::Base
       # パスワードチェック
       id = UserAccount.checkPasswd(name, passwd);
       session[:userId] = id;
-      redirect "/user_home"
+      redirect to('/user_home')
     rescue UserAccount::NotFoundInstanceError,
            UserAccount::AuthenticationError
       raise WebError.new(status: 401, message: "認証に失敗しました アカウント、 パスワードを確認してください。")
@@ -515,8 +515,7 @@ class WebGui < Sinatra::Base
     end
     position = list_status[:position]
     find_status = list_status[:find_status]
-    books = BookManager.findBook(find_status)
-    @book_list, @full_size = BookManager.narrowBookOfId(id, books, position[:start], position[:step])
+    @book_list, @full_size = BookManager.narrowBookOfId(id, position[:start], position[:step], find_status)
     @start = position[:start]
     @step = position[:step]
     if (@book_list == nil)
diff --git a/sinatra/app/models/books_db.rb b/sinatra/app/models/books_db.rb
index 46a5a27..a269d31 100644
--- a/sinatra/app/models/books_db.rb
+++ b/sinatra/app/models/books_db.rb
@@ -50,6 +50,18 @@ class BookCover < ActiveRecord::Base
   end
 end
 
+#ユーザ書籍情報
+class BookFull < ActiveRecord::Base
+  # 正規表現によるレコード探索
+  # @param [Symbol] key カラム名
+  # @param [String] pattern 正規表現パターン
+  # @return [Array<User>] 対象となるユーザ一覧
+  def self.with_regexp(key, pattern)
+    column = columns_hash[key.to_s].name
+    where("`#{table_name}`.`#{column}` REGEXP ?", pattern)
+  end
+end
+
 # ユーザ管理
 class BookManager
 
@@ -81,7 +93,7 @@ class BookManager
   SEARCH_ON_GBS = 3
   
   # 国立国会図書館 から取得
-  SEARCH_ON_NDL = 3
+  SEARCH_ON_NDL = 4
 
   # ISBNの文字列から "-" を削除
   # @param [String] isbn 処理対象のISBN
@@ -568,28 +580,32 @@ class BookManager
 
   # ユーザでの絞り込み
   # @param [Integer] user_id ユーザID
-  # @param [Arrray<Book>] books 絞り込み対象の書籍情報一覧
+  # @param [Integer] start 表示開始位置
+  # @param [Integer] step 表示数
+  # @param [Hash]  find_keys 検索対象
   # @return [Array<Hash>] 絞り込んだ書籍情報
-  def self.narrowBookOfId(user_id, books, start, step)
-    if (books.size < start)
-      return nil
-    end
-    if (books.size < start + step)
-      step = books.size - start
-    end
-    narrow_books=Array.new
-    count = 0
-    books.each do |book|
-      book_collect = BookCollection.find_by(user_id: user_id, isbn: book.isbn)
-      if (book_collect != nil)
-        if ((count >= start) && (count < start + step))
-          narrow_book = createBookHash(book, book_collect)
-          narrow_books.push(narrow_book)
+  def self.narrowBookOfId(user_id, start, step, find_keys)
+    # ユーザに関連する書籍の一覧を取得する。
+    find_books = BookFull.where(user_id: user_id)
+
+    # 条件に合う書籍を検索
+    if (find_keys != nil) 
+      find_keys.each do |key, value|
+        if (find_books == nil)
+          break
+        end
+        if (value =~ /^\/(.+)\/$/)
+          reg_value = $~[1]
+          find_books = find_books.with_regexp(key, reg_value)
+        else
+          find_books = books.where(key => value)
         end
-        count += 1
       end
     end
-    return narrow_books, count
+
+    # 表示対象を抽出
+    narrow_books = find_books.limit(step).offset(start)
+    return narrow_books, find_books.size
   end
 
   #蔵書情報の取得
-- 
2.19.2