/// <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;
}
