public ActionResult CheckedOutBooks()
{
var userId = User.Identity.GetUserId();
var userEmail = UserManager.GetEmail(userId);
var listOfBooks = new List <BookInfoModel>();
if (userEmail != null)
{
var userQueries = Queries.SetConnectionStringForUsers();
var checkoutQueries = Queries.SetConnectionStringForCheckOuts();
var bookQueries = Queries.SetConnectionStringForBooks();
var userDBID = userQueries.GetUserIDByEmail(userEmail);
var userBooks = checkoutQueries.GetCheckOutsByUserID(userDBID);
foreach (var book in userBooks)
{
var bookInfo = bookQueries.GetBooksById(book.BookID).SingleOrDefault();
var bookInfoModel = new BookInfoModel
{
BookName = bookInfo.BookName,
Checkouts = userBooks.Select(c => new CheckoutViewModel
{
DueDate = c.DueDate
}).ToList()
};
listOfBooks.Add(bookInfoModel);
}
}
return(View(listOfBooks));
}
public static void UpdateBook(BookInfoModel book)
{
string sql = @"update bookinfo set
title=@title,
origintitle=@origintitle,
subtitle=@subtitle,
image=@image,
category_id=@categoryid,author_id=@author,isbn=@isbn,publisher_id=@publisher,pubdate=@pubdate,pages=@pages,price=@price
where id=@id";
int a = SqlHelper.ExecuteNonQuery(sql, new SqlParameter[]
{
new SqlParameter("title", book.title),
new SqlParameter("origintitle", book.origintitle),
new SqlParameter("subtitle", book.subtitle),
new SqlParameter("image", book.image),
new SqlParameter("categoryid", book.category_id),
new SqlParameter("author", book.author_id),
new SqlParameter("isbn", book.isbn),
new SqlParameter("publisher", book.publisher_id),
new SqlParameter("pages", book.pages),
new SqlParameter("price", book.price),
new SqlParameter("id", book.id),
new SqlParameter("pubdate", book.pubdate),
});
}
/// <summary>
/// 根据类型返回图书集(bookType)
/// </summary>
/// <param name="typeID"></param>
/// <param name="startNum"></param>
/// <param name="endNum"></param>
/// <returns></returns>
public List <BookInfoModel> getBookInfoByPage(string typeID, int startNum, int endNum)
{
string comd = "select * from BookType,(select *,row_number() over(order by bookName) as number from BookInfo where bookType = @bookType) as tb where BookType.typeID = tb.BookType and number >= @startNum and number <= @endNum";
SqlParameter[] ps =
{
new SqlParameter("@bookType", typeID),
new SqlParameter("@startNum", startNum),
new SqlParameter("@endNum", endNum)
};
SQLHelper h = new SQLHelper();
List <BookInfoModel> list = new List <BookInfoModel>();
using (SqlDataReader read = h.getDataReader(comd, ps))
{
if (read.HasRows)
{
while (read.Read())
{
BookInfoModel model = new BookInfoModel();
model.Author = read["author"].ToString();
model.BookID = read["bookID"].ToString();
model.BookName = read["bookName"].ToString();
model.BookStatus = Convert.ToBoolean(read["bookStatus"])? "可借" : "借出";
model.BookType = read["bookType"].ToString();
model.PicPath = read["picPath"].ToString();
model.Publish = read["publish"].ToString();
model.TypeName = read["typeName"].ToString();
list.Add(model);
}
}
}
return(list);
}
public ActionResult BookInfoPage(string id)
{
if (id != null)
{
ViewBag.Message = "Book stuff";
var bookQueries = Queries.SetConnectionStringForBooks();
var checkOutQueries = Queries.SetConnectionStringForCheckOuts();
var bookInfo = bookQueries.GetBooksByName(id).SingleOrDefault();
var checkOutInfo = checkOutQueries.GetCheckOutsByBookID(bookInfo.ID);
var bookInfoModel = new BookInfoModel
{
BookName = bookInfo.BookName,
BookCopies = bookInfo.BookCopies,
Checkouts = checkOutInfo.Select(c => new CheckoutViewModel
{
UserName = c.UserName, DueDate = c.DueDate
}).ToList()
};
return(View(bookInfoModel));
}
else
{
return(View(new BookInfoModel()));
}
}
/// <summary>
/// 删除内容(快捷菜单)
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void BookDelete_Click(object sender, EventArgs e)
{
if (dgvBook.SelectedRows.Count == 0)
{
return;
}
if (MessageBox.Show("确定要删除图书吗？", "提示", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes)
{
BookInfoModel book = dgvBook.SelectedRows[0].DataBoundItem as BookInfoModel;
BLL.BookInfoBLL bll = new BLL.BookInfoBLL();
int result = bll.deleteBookInfo(book.BookID);
if (result == 1)
{
MessageBox.Show("删除成功");
}
else if (result > 1)
{
MessageBox.Show("删除了多条记录");
}
else if (result == 0)
{
MessageBox.Show("未执行删除操作");
}
else
{
MessageBox.Show("删除失败");
}
//更新数据源
this.dgvBook.DataSource = bll.getAllBookInfoList(BookPage.PageIndex);
}
}
/// <summary>
/// 确定修改
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnBookAdd_Click(object sender, EventArgs e)
{
BookInfoModel book = this.pnlModify.Tag as BookInfoModel;
book.BookName = txtBookName.Text;
book.Author = txtAuthor.Text;
book.Publish = txtPublish.Text;
book.BookStatus = "可借";
book.BookType = txtBookType.Text;
BookInfoBLL bll = new BookInfoBLL();
int res = bll.updateBookInfoModel(book);
if (res == 1)
{
MessageBox.Show("修改成功");
this.pnlModify.Visible = false;
}
else if (res < 1)
{
MessageBox.Show("修改失败");
}
else if (res > 1)
{
MessageBox.Show("修改多于一条记录");
this.pnlModify.Visible = false;
}
}
public static string AddBook(BookInfoModel book)
{
string msg = "图书添加失败";
if (BookInfoDAL.InsertBook(book) > 0)
{
msg = "图书添加成功";
}
return(msg);
}
/// <summary>
/// 根据图书类更新图书的大量信息
/// </summary>
/// <param name="book"></param>
/// <returns></returns>
public int updateBookInfoByModel(BookInfoModel book)
{
string comd = "update BookInfo set publish=@publish, author=@author, bookName=@bookName where bookID=@bookID ";
SqlParameter[] ps =
{
new SqlParameter("@publish", book.Publish),
new SqlParameter("@author", book.Author),
new SqlParameter("@bookName", book.BookName),
new SqlParameter("@bookID", book.BookID)
};
SQLHelper h = new SQLHelper();
return(h.ExecuteNonQuery(comd, ps));
}
///<summary>
///delete bookInfo by model( many fields fuzzy query )
///</summary>
public int deleteBookInfo(BookInfoModel book)
{
List <BookInfoModel> list = new List <BookInfoModel>();
string comd = "delete from BookInfo where bookName=@bookName or bookID =@bookID or publish=@publish or bookType=@bookType ot author=@author ";
SqlParameter[] ps =
{
new SqlParameter("@bookName", book.BookName),
new SqlParameter("@bookID", book.BookID),
new SqlParameter("@publish", book.Publish),
new SqlParameter("@bookType", book.BookType),
new SqlParameter("@author", book.Author)
};
SQLHelper h = new SQLHelper();
return(h.ExecuteNonQuery(comd, ps));
}
///<summary>
///insert one bookInfo by model
///</summary>
public int insertBookInfo(BookInfoModel book)
{
List <BookInfoModel> list = new List <BookInfoModel>();
string comd = "insert into BookInfo values( @publish, @author, @bookType, @bookID, @bookName, @bookStatus, @picPath ) ";
SqlParameter[] ps =
{
new SqlParameter("@publish", book.Publish),
new SqlParameter("@author", book.Author),
new SqlParameter("@bookType", book.BookType),
new SqlParameter("@bookID", book.BookID),
new SqlParameter("@bookName", book.BookName),
new SqlParameter("@bookStatus", 1),
new SqlParameter("@picPath", book.PicPath)
};
SQLHelper h = new SQLHelper();
return(h.ExecuteNonQuery(comd, ps));
}
/// <summary>
/// 加载图书信息
/// </summary>
private void LoadPnlModify()
{
BookInfoModel model = (dgvBook.SelectedRows[0].DataBoundItem as BookInfoModel);
pnlModify.Tag = model;
FillText(model);
pnlModify.Visible = true;
//初始化只读状态
txtAuthor.ReadOnly = true;
txtBookName.ReadOnly = true;
txtBookStatus.ReadOnly = true;
txtBookStatus.ReadOnly = true;
txtBookType.ReadOnly = true;
txtHisTimes.ReadOnly = true;
btnBookAdd.Visible = false;
btnBookReset.Visible = false;
}
protected void btnSubmmit_Click(object sender, EventArgs e)
{
if (Request["bookid"] != null)
{
BookInfoModel book = new BookInfoModel();
book.id = int.Parse(Request["bookid"]);
book.title = txttitle.Text;
book.origintitle = txtorigin.Text;
book.subtitle = txtsub.Text;
book.category_id = int.Parse(DropDownList2.SelectedValue);
book.author_id = int.Parse(txtauthorid.Text);
book.publisher_id = int.Parse(txtpublisherid.Text);
book.isbn = txtisbn.Text;
book.pubdate = txtpubdate.Text;
book.pages = txtpages.Text;
book.price = txtOriginPrice.Text;
BookInfoBLL.SetBook(book);
}
}
public static int InsertBook(BookInfoModel book)
{
string sql = "insert into bookinfo values(@isbn,@title,@origintitle,@subtitle,@image,@author_id,@category_id,@publisher_id,@pubdate,@pages,@price,@summary,@catalog)";
return(SqlHelper.ExecuteNonQuery(sql, new SqlParameter[] {
new SqlParameter("@isbn", book.isbn),
new SqlParameter("@title", book.title),
new SqlParameter("@origintitle", book.origintitle),
new SqlParameter("@subtitle", book.subtitle),
new SqlParameter("@image", book.image),
new SqlParameter("@author_id", book.author_id),
new SqlParameter("@category_id", book.category_id),
new SqlParameter("@publisher_id", book.publisher_id),
new SqlParameter("@pubdate", book.pubdate),
new SqlParameter("@pages", book.pages),
new SqlParameter("@price", book.price),
new SqlParameter("@summary", book.summary),
new SqlParameter("@catalog", book.catalog),
}));
}
/// <summary>
/// 增加
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnAdd_Click(object sender, EventArgs e)
{
BookInfoModel book = new BookInfoModel();
book.Author = txtAuthor.Text.Trim();
book.Publish = txtPublish.Text.Trim();
book.BookID = txtBookID.Text.Trim();
book.BookName = txtBookName.Text.Trim();
book.BookType = txtBookType.Text.ToString();
book.PicPath = txtBookPic.Text.Trim();
BookInfoBLL bll = new BookInfoBLL();
if (bll.insertBookInfo(book) == 1)
{
this.Close();
}
else
{
MessageBox.Show("添加错误，请检查一下内容");
}
}
protected void Button3_Click(object sender, EventArgs e)
{
BookInfoModel book = new BookInfoModel();
book.author_id = int.Parse(txtauthorid.Text);
book.catalog = txtcatalog.Text;
book.category_id = int.Parse(DropDownList2.SelectedValue);
book.image = "";
book.isbn = txtisbn.Text;
book.origintitle = txtorigin.Text;
book.pages = txtpages.Text;
book.price = txtprice.Text;
book.pubdate = txtpubdate.Text;
book.publisher_id = int.Parse(txtpublisherid.Text);
book.subtitle = txtsub.Text;
book.summary = txtsummary.Text;
book.title = txttitle.Text;
string msg = BookInfoBLL.AddBook(book);
ClientScript.RegisterStartupScript(ClientScript.GetType(), "", "<script>showModal('" + msg + "');</script>");
}
/// <summary>
/// 填充文本
/// </summary>
/// <param name="model"></param>
private void FillText(BookInfoModel model)
{
txtBookName.Text = model.BookName;
txtAuthor.Text = model.Author;
txtPublish.Text = model.Publish;
//图书借阅次数--------
BookHistoryBLL bll = new BookHistoryBLL();
txtHisTimes.Text = bll.getBookBorrowedCount(model.BookID).ToString();
for (int i = 0; i < types.Count; i++)
{
if (types[i].TypeID == model.BookType)
{
txtBookType.Text = types[i].TypeName;
}
}
if (!string.IsNullOrEmpty(model.PicPath))
{
picAdd.Image = Image.FromFile(model.PicPath);
}
txtBookStatus.Text = model.BookStatus;
}
/// <summary>
/// 填充对象
/// </summary>
/// <param name="list"></param>
/// <param name="read"></param>
private static void FillModel(List <BookInfoModel> list, SqlParameter[] ps, string comd)
{
SQLHelper h = new SQLHelper();
using (SqlDataReader read = h.getDataReader(comd, ps))
{
if (read.HasRows)
{
while (read.Read())
{
BookInfoModel book = new BookInfoModel();
book.BookID = read["bookID"].ToString();
book.BookName = read["bookName"].ToString();
book.BookStatus = Convert.ToBoolean(read["bookStatus"]) ? "可借" : "借出";
book.BookType = read["bookType"].ToString();
book.Publish = read["publish"].ToString();
book.Author = read["author"].ToString();
book.PicPath = read["picPath"].ToString();
book.TypeName = read["typeName"].ToString();
list.Add(book);
}
}
}
}
public static void SetBook(BookInfoModel book)
{
BookInfoDAL.UpdateBook(book);
}
///<summary>
///update bookInfo infomation
///</summary>
public int updateBookInfoModel(BookInfoModel model)
{
BookInfoDAL dal = new BookInfoDAL();
return(dal.updateBookInfoByModel(model));
}
///<summary>
///delete bookInfo by model( many fields fuzzy query )
///</summary>
public int deleteBookInfo(BookInfoModel book)
{
BookInfoDAL dal = new BookInfoDAL();
return(dal.deleteBookInfo(book));
}
///<summary>
///insert one bookInfo by model
///</summary>
public int insertBookInfo(BookInfoModel book)
{
BookInfoDAL dal = new BookInfoDAL();
return(dal.insertBookInfo(book));
}
/// <inheritdoc />
public async Task <LibraryStatsModel> GetStats()
{
var num = await _bookColl.CountDocumentsAsync(x => true);
var cutoff = DateTime.UtcNow.AddDays(-7);
var latest = await _bookColl.Find(x => x.CreatedOn >= cutoff).Sort(Builders <Book> .Sort.Descending(x => x.CreatedOn)).Limit(15).Project(BookInfoModel.GetMap()).ToListAsync();
return(new LibraryStatsModel {
NumBooks = num, LatestUploaded = latest
});
}
