/// <summary>
/// DBから履歴取得時にインスタンス初期化するため
/// </summary>
/// <param name="_id"></param>
/// <param name="_user"></param>
/// <param name="_book"></param>
/// <param name="_returnDate"></param>
/// <param name="_completionDate"></param>
/// <param name="_created_at"></param>
/// <param name="_edited_at"></param>
private LendingHistoryRecord(
string _id,
User _user,
Book _book,
string _returnDate,
string _completionDate,
string _created_at,
string _edited_at
)
: base(_id, _created_at, _edited_at)
{
this.user = _user;
this.book = _book;
this.returnDate = _returnDate;
this.completionDate = _completionDate;
}
/// <summary>
/// 本の詳細
/// </summary>
/// <param name="_book">詳細を表示する図書</param>
/// <param name="_user">ログインしていればユーザのデータ、ログインしていなければnull</param>
public BookDetailsWindow(Book _book, User _user)
{
InitializeComponent();
book = _book;
user = _user;
if (_book == null)
{
return;
}
if (_user == null)
{
InitOfGuestOnly();
}
var history = LendingHistoryRecord.GetDueDateOfBook(_book);
if (history.Count == 1)
{
CanNotBorrow(history[0].DueDate);
}
else
{
this.dueDateContent = "You can borrow.";
}
this.DataContext = new {
Title = book.Title,
Author = book.Author,
Isbn = book.Isbn,
Publisher = book.Publisher,
LendInfo = dueDateContent,
Caption = book.Caption,
};
if (book.ImageUrl != null)
{
this.imageBox.Source = new BitmapImage(new Uri(book.ImageUrl));
}
}
/// <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;
}
/// <summary>
/// 貸し出し情報を追加する
/// </summary>
/// <param name="_book">貸し出す本</param>
/// <param name="_userId">貸し出す相手のユーザID</param>
/// <returns>成功か否か</returns>
public static Result Create(Book _book, string _userId)
{
int c = GetDueDateOfBook(_book).Count;
if (c != 0)
{
Console.WriteLine(c);
return Result.Failed;
}
using (SQLiteConnection cn = new SQLiteConnection(dbConStr))
{
cn.Open();
SQLiteCommand cmd = cn.CreateCommand();
cmd.CommandText =
"INSERT INTO "
+ TABLE_NAME + " (user_id, book_id, return_date, created_at, edited_at) "
+ "VALUES (@USER_ID, @BOOK_ID, DATETIME('now', '+7 days'), DATETIME('now'), DATETIME('now'))";
cmd.Parameters.Add(new SQLiteParameter("@USER_ID", _userId));
cmd.Parameters.Add(new SQLiteParameter("@BOOK_ID", _book.Id));
cmd.ExecuteNonQuery();
cn.Close();
}
return Result.Success;
}
/// <summary>
/// 貸し出し情報を追加する
/// </summary>
/// <param name="_book">貸し出す本</param>
/// <param name="_user">貸し出す相手のアカウント</param>
/// <returns>成功か否か</returns>
public static Result Create(Book _book, User _user)
{
return Create(_book, _user.Id);
}
/// <summary>
/// 本を追加する
/// delegateの関数
/// </summary>
/// <param name="book"></param>
public void AddBook(Book book)
{
booksToBeDisplayed.Add(book);
}
protected override void Seed(ApplicationDbContext context)
{
Book AliceInWonderland = new Book
{
Isbn = 123456,
Title = "Alice's Adventure In Wonderland",
Year = 1865,
Author = "Charles Lutwidge Dodgson",
ImageUrl = "~/Content/Images/Aiw.png"
};
Book Dracula = new Book
{
Isbn = 246802,
Title = "Dracula",
Year = 1992,
Author = "Bram Stoker",
ImageUrl = "~/Content/Images/Dracula.png"
};
Book Frankenstein = new Book
{
Isbn = 369258,
Title = "Frankenstein",
Year = 1818,
Author = "Mary Wollstonecraft",
ImageUrl = "~/Content/Images/Frankenstein.png"
};
Book PrideAndPrejudice = new Book
{
Isbn = 482604,
Title = "Pride and Prejudice",
Year = 1813,
Author = "Jane Austen",
ImageUrl = "~/Content/Images/Pap.png"
};
Book SherlockHolmes = new Book
{
Isbn = 505050,
Title = "The Adventures of Sherlock Holmes",
Year = 1892,
Author = "Arthur Conan Doyle",
ImageUrl = "~/Content/Images/Sherlock.png"
};
Book TomSawyer = new Book
{
Isbn = 628406,
Title = "The Adventures of Tom Sawyer",
Year = 1876,
Author = "Mark Twain",
ImageUrl = "~/Content/Images/Tom.png"
};
LibraryCard GCBC200300001 = new LibraryCard
{
FirstName = "Jack",
LastName = "Jill",
DateOfBirth = DateTime.Parse("08-22-1955"),
Phone = "(705) 536 4353",
Email = "*****@*****.**"
};
LibraryCard GCBC200300002 = new LibraryCard
{
FirstName = "Bob",
LastName = "Ross",
DateOfBirth = DateTime.Parse("05-15-1977"),
Phone = "(705) 453 8355",
Email = "*****@*****.**"
};
LibraryCard GCBC200300003 = new LibraryCard
{
FirstName = "Suzy",
LastName = "Sheer",
DateOfBirth = DateTime.Parse("01-10-2011"),
Phone = "(705) 365 3452",
Email = "*****@*****.**"
};
LibraryCard GCBC200300004 = new LibraryCard
{
FirstName = "Zack",
LastName = "Bell",
DateOfBirth = DateTime.Parse("07-28-2004"),
Phone = "(705) 257 5856",
Email = "*****@*****.**"
};
LibraryBranch BarrieBranch = new LibraryBranch
{
Name = "Georgian College, Barrie",
Address = "1 Georgian Dr, Barrie, ON L4M 3X9",
Phone = "(705) 728-1968",
Hours = "8-4:30"
};
LibraryBranch OrilliaBranch = new LibraryBranch
{
Name = "Georgian College, Orillia",
Address = "825 Memorial Ave, Orillia, ON L3V 6S2",
Phone = "(705) 325-2740",
Hours = "7:30-8"
};
LibraryBranch MidlandBranch = new LibraryBranch
{
Name = "Midland",
Address = "649 Prospect Blvd, Midland, ON L4R 4K6",
Phone = "(705) 526-3666",
Hours = "8-4"
};
context.Books.Add(AliceInWonderland);
context.Books.Add(Dracula);
context.Books.Add(Frankenstein);
context.Books.Add(PrideAndPrejudice);
context.Books.Add(SherlockHolmes);
context.Books.Add(TomSawyer);
context.LibraryCards.Add(GCBC200300001);
context.LibraryCards.Add(GCBC200300002);
context.LibraryCards.Add(GCBC200300003);
context.LibraryCards.Add(GCBC200300004);
context.LibraryBranches.Add(BarrieBranch);
context.LibraryBranches.Add(OrilliaBranch);
context.LibraryBranches.Add(MidlandBranch);
base.Seed(context);
}