/// <summary> /// ユーザの未返却本一覧をDBから取得 /// </summary> /// <param name="_user">ユーザ</param> /// <returns>取得した貸出履歴のリスト</returns> public static List<LendingHistoryRecord> GetUnreturnedBookFromUser(User _user) { List<LendingHistoryRecord> result = new List<LendingHistoryRecord>(); using (SQLiteConnection cn = new SQLiteConnection(dbConStr)) { cn.Open(); SQLiteCommand cmd = cn.CreateCommand(); cmd.CommandText = "SELECT * FROM " + TABLE_NAME + " INNER JOIN book ON lending_history.book_id = book.id" + " WHERE completion_date IS NULL" + " AND user_id = @USER_ID" + ";"; cmd.Parameters.Add(new SQLiteParameter("@USER_ID", _user.Id)); using (SQLiteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Book _book = new Book( reader[7].ToString(), // id reader[8].ToString(), // isbn reader[9].ToString(), // title reader[10].ToString(), // author reader[11].ToString(), // publisher reader[12].ToString(), // series reader[13].ToString(), // caption reader[14].ToString(), // image_url reader[15].ToString(), // created_at reader[16].ToString() // edited_at ); LendingHistoryRecord history = new LendingHistoryRecord( reader[0].ToString(), // id _user, _book, reader[3].ToString(), // return_date reader[4].ToString(), // completion_date reader[5].ToString(), // created_at reader[6].ToString() // edited_at ); history.Show(); result.Add(history); } } cn.Close(); } return result; }
/// <summary> /// 本の返却状況を返す /// </summary> /// <param name="_book"></param> /// <returns>成功か否か</returns> public static List<LendingHistoryRecord> GetDueDateOfBook(Book _book) { List<LendingHistoryRecord> result = new List<LendingHistoryRecord>(); using (SQLiteConnection cn = new SQLiteConnection(dbConStr)) { cn.Open(); SQLiteCommand cmd = cn.CreateCommand(); cmd.CommandText = "SELECT * FROM " + TABLE_NAME + " WHERE completion_date IS NULL" + " AND book_id = @BOOK_ID" + ";"; cmd.Parameters.Add(new SQLiteParameter("@BOOK_ID", _book.Id)); using (SQLiteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { LendingHistoryRecord history = new LendingHistoryRecord( reader[0].ToString(), // id reader[1].ToString(), // user_id _book, reader[3].ToString(), // return_date reader[4].ToString(), // completion_date reader[5].ToString(), // created_at reader[6].ToString() // edited_at ); history.Show(); result.Add(history); } } cn.Close(); } return result; }