/// <summary>
/// 修改書籍資料
/// </summary>
/// <param name="bookData">BOOK_DATA</param>
/// <returns>BOOK_DATA.BOOK_ID</returns>
public int EditBook(BOOK_DATA bookData)
{
try
{
string sql = @"UPDATE BOOK_DATA SET BOOK_NAME = @BOOK_NAME, BOOK_CLASS_ID = @BOOK_CLASS_ID, BOOK_AUTHOR =@BOOK_AUTHOR
,BOOK_BOUGHT_DATE=@BOOK_BOUGHT_DATE,BOOK_PUBLISHER=@BOOK_PUBLISHER
,BOOK_NOTE=@BOOK_NOTE,BOOK_STATUS=@BOOK_STATUS,BOOK_KEEPER =@BOOK_KEEPER Where BOOK_ID=@BOOK_ID";
using (SqlConnection conn = new SqlConnection(_conn))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BOOK_ID", bookData.BOOK_ID));
cmd.Parameters.Add(new SqlParameter("@BOOK_NAME", bookData.BOOK_NAME));
cmd.Parameters.Add(new SqlParameter("@BOOK_CLASS_ID", bookData.BOOK_CLASS_ID));
cmd.Parameters.Add(new SqlParameter("@BOOK_AUTHOR", bookData.BOOK_AUTHOR));
cmd.Parameters.Add(new SqlParameter("@BOOK_BOUGHT_DATE", bookData.BOOK_BOUGHT_DATE));
cmd.Parameters.Add(new SqlParameter("@BOOK_PUBLISHER", bookData.BOOK_PUBLISHER));
cmd.Parameters.Add(new SqlParameter("@BOOK_NOTE", bookData.BOOK_NOTE));
cmd.Parameters.Add(new SqlParameter("@BOOK_STATUS", bookData.BOOK_STATUS));
cmd.Parameters.Add(new SqlParameter("@BOOK_KEEPER", bookData.BOOK_KEEPER ?? (Object)DBNull.Value));
cmd.ExecuteScalar();
conn.Close();
}
return(bookData.BOOK_ID);
}
catch (Exception ex)
{
return(404);
}
}
/// <summary>
/// 新增書籍
/// </summary>
/// <param name="bookData">BOOK_DATA</param>
/// <returns>BOOK_DATA.BOOK_ID</returns>
public int InsertBook(BOOK_DATA bookData)
{
string sql = @" INSERT INTO BOOK_DATA
(
BOOK_NAME,BOOK_CLASS_ID,BOOK_AUTHOR,BOOK_BOUGHT_DATE
,BOOK_PUBLISHER,BOOK_NOTE,BOOK_STATUS
)
VALUES
(
@BOOK_NAME,@BOOK_CLASS_ID, @BOOK_AUTHOR, @BOOK_BOUGHT_DATE, @BOOK_PUBLISHER, @BOOK_NOTE,
@BOOK_STATUS
)
Select SCOPE_IDENTITY()" ;
int id;
using (SqlConnection conn = new SqlConnection(_conn))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BOOK_NAME", bookData.BOOK_NAME));
cmd.Parameters.Add(new SqlParameter("@BOOK_CLASS_ID", bookData.BOOK_CLASS_ID));
cmd.Parameters.Add(new SqlParameter("@BOOK_AUTHOR", bookData.BOOK_AUTHOR));
cmd.Parameters.Add(new SqlParameter("@BOOK_BOUGHT_DATE", bookData.BOOK_BOUGHT_DATE));
cmd.Parameters.Add(new SqlParameter("@BOOK_PUBLISHER", bookData.BOOK_PUBLISHER));
cmd.Parameters.Add(new SqlParameter("@BOOK_NOTE", bookData.BOOK_NOTE));
cmd.Parameters.Add(new SqlParameter("@BOOK_STATUS", "A"));
id = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
}
return(id);
}
/// <summary>
/// 使用ID 取得書籍資料
/// </summary>
/// <param name="BookId">書籍ID</param>
/// <returns>BOOK_DATA</returns>
public BOOK_DATA GetBookData(int BookId)
{
DataTable dt = new DataTable();
string sql = @"Select *
From dbo.BOOK_DATA Where BOOK_ID = @BookId";
using (SqlConnection conn = new SqlConnection(_conn))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BookId", BookId));
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
conn.Close();
}
BOOK_DATA _data = new BOOK_DATA();
_data.BOOK_ID = Int32.Parse(dt.Rows[0][0].ToString());
_data.BOOK_NAME = dt.Rows[0][1].ToString();
_data.BOOK_CLASS_ID = dt.Rows[0][2].ToString();
_data.BOOK_AUTHOR = dt.Rows[0][3].ToString();
_data.BOOK_BOUGHT_DATE = DateTime.Parse(dt.Rows[0][4].ToString());
_data.BOOK_PUBLISHER = dt.Rows[0][5].ToString();
_data.BOOK_NOTE = dt.Rows[0][6].ToString();
_data.BOOK_STATUS = dt.Rows[0][7].ToString();
_data.BOOK_KEEPER = dt.Rows[0][8].ToString();
return(_data);
}
public ActionResult DeleteConfirmed(int id)
{
BOOK_DATA bOOK_DATA = db.BOOK_DATA.Find(id);
db.BOOK_DATA.Remove(bOOK_DATA);
db.SaveChanges();
return(RedirectToAction("Index"));
}
/// <summary>
/// 用搜尋條件取得書籍資料
/// </summary>
/// <param name="BOOK_NAME">書籍名稱</param>
/// <param name="BOOK_CLASS_ID">書籍分類ID</param>
/// <param name="BOOK_KEEPER">使用者ID</param>
/// <param name="BOOK_STATUS">書籍狀態碼</param>
/// <returns>BOOK_DATA</returns>
public List <BOOK_DATA> GetBookDataList(string BOOK_NAME, string BOOK_CLASS_ID, string BOOK_KEEPER, string BOOK_STATUS)
{
DataTable dt = new DataTable();
string sql = @"Select *
From dbo.BOOK_DATA";
using (SqlConnection conn = new SqlConnection(_conn))
{
conn.Open();
SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
sda.Fill(dt);
conn.Close();
}
var data = new List <BOOK_DATA>();
var ClassList = GetBookClassList();
var MemberList = GetMemberList();
var CodeList = GetCodeList();
for (int i = 0; i < dt.Rows.Count; i++)
{
BOOK_DATA _data = new BOOK_DATA();
_data.BOOK_ID = Int32.Parse(dt.Rows[i][0].ToString());
_data.BOOK_NAME = dt.Rows[i][1].ToString();
_data.BOOK_CLASS_ID = ClassList.Find(x => x.BOOK_CLASS_ID == dt.Rows[i][2].ToString()).BOOK_CLASS_NAME;
_data.BOOK_AUTHOR = dt.Rows[i][3].ToString();
_data.BOOK_BOUGHT_DATE = DateTime.Parse(dt.Rows[i][4].ToString());
_data.BOOK_PUBLISHER = dt.Rows[i][5].ToString();
_data.BOOK_NOTE = dt.Rows[i][6].ToString();
_data.BOOK_STATUS = CodeList.Find(x => x.CODE_ID == dt.Rows[i][7].ToString()).CODE_NAME;
if (!string.IsNullOrEmpty(dt.Rows[i][8].ToString()))
{
_data.BOOK_KEEPER = MemberList.Find(x => x.USER_ID == dt.Rows[i][8].ToString()).USER_CNAME;
}
data.Add(_data);
}
if (BOOK_NAME != null)
{
data = data.Where(x => x.BOOK_NAME.Contains(BOOK_NAME)).ToList();
}
if (BOOK_CLASS_ID != null)
{
data = data.Where(x => x.BOOK_CLASS_ID == BOOK_CLASS_ID).ToList();
}
if (BOOK_KEEPER != null)
{
data = data.Where(x => x.BOOK_KEEPER == BOOK_KEEPER).ToList();
}
if (BOOK_STATUS != null)
{
data = data.Where(x => x.BOOK_STATUS == BOOK_STATUS).ToList();
}
return(data.OrderByDescending(x => x.BOOK_BOUGHT_DATE).ToList());
}
/// <summary>
/// HtmlEncode防止XSS
/// </summary>
public void ModelHtmlEncode(BOOK_DATA book)
{
book.BOOK_AUTHOR = Server.HtmlEncode(book.BOOK_AUTHOR);
book.BOOK_CLASS_ID = Server.HtmlEncode(book.BOOK_CLASS_ID);
book.BOOK_NAME = Server.HtmlEncode(book.BOOK_NAME);
book.BOOK_NOTE = Server.HtmlEncode(book.BOOK_NOTE);
book.BOOK_PUBLISHER = Server.HtmlEncode(book.BOOK_PUBLISHER);
book.BOOK_STATUS = Server.HtmlEncode(book.BOOK_STATUS);
book.BOOK_KEEPER = Server.HtmlEncode(book.BOOK_KEEPER);
}
/// <summary>
/// 以BookId搜尋此書
/// </summary>
public BOOK_DATA GetBookDetail(int id)
{
BooksSearchArg arg = new BooksSearchArg {
BookId = id
};
BOOK_DATA book = this.booksService.GetBooks(arg).FirstOrDefault();
ModelHtmlDecode(book);
return(book);
}
public ActionResult Edit([Bind(Include = "BOOK_ID,BOOK_NAME,BOOK_CLASS_ID,BOOK_AUTHOR,BOOK_BOUGHT_DATE,BOOK_PUBLISHER,BOOK_NOTE,BOOK_STATUS,BOOK_KEEPER,BOOK_AMOUNT,CREATE_DATE,CREATE_USER,MODIFY_DATE,MODIFY_USER")] BOOK_DATA bOOK_DATA)
{
if (ModelState.IsValid)
{
db.Entry(bOOK_DATA).State = EntityState.Modified;
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(bOOK_DATA));
}
// GET: NewBooks/Delete/5
public ActionResult Delete(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
BOOK_DATA bOOK_DATA = db.BOOK_DATA.Find(id);
if (bOOK_DATA == null)
{
return(HttpNotFound());
}
return(View(bOOK_DATA));
}
public ActionResult BooksInsert(BOOK_DATA book)
{
//HtmlEncode
ModelHtmlEncode(book);
ViewBag.BookClass = this.booksService.GetBookClass();
if (ModelState.IsValid)
{
ViewBag.InsertMessage = "新增失敗";
ViewBag.AlertClass = "alert alert-danger";
int InsertResult = this.booksService.InsertBooks(book);
if (InsertResult != 0)
{
ViewBag.InsertMessage = "新增完成";
ViewBag.AlertClass = "alert alert-success";
}
}
return(View());
}
public ActionResult BooksUpdate(BOOK_DATA book, int id)
{
ModelHtmlEncode(book);
book.BOOK_ID = id;
//驗證以借閱時借閱者不得為空
if (book.BOOK_STATUS != "A" && book.BOOK_STATUS != "U" && book.BOOK_KEEPER == null)
{
ViewBag.UpdateMessage = "已借閱 不得無借閱者";
ViewBag.keeperAlarm = "";
SetDropDownListItmes();
return(View(book));
}
if (ModelState.IsValid)
{
int UpdateResult = booksService.UpdateBooks(book);
if (UpdateResult != 0)
{
ViewBag.UpdateMessage = "修改完成";
}
}
SetDropDownListItmes();
return(View(book));
}
public ActionResult Create(BOOK_DATA table)
{
db.BOOK_DATA.Add(table);
db.SaveChanges();
return(RedirectToAction("Index"));
}
/// <summary>
/// 新增書籍資料
/// </summary>
/// <param name="bookData">BOOK_DATA</param>
/// <returns>JSON 書籍編號 BOOK_DATA.BOOK_ID</returns>
public JsonResult Insert(BOOK_DATA bookData)
{
return(Json(bookService.InsertBook(bookData)));
}
/// <summary>
/// 修改書籍資料
/// </summary>
/// <param name="bookData">BOOK_DATA</param>
/// <returns>JSON 書籍編號 BOOK_DATA.BOOK_ID</returns>
public JsonResult EditBook(BOOK_DATA bookData)
{
return(Json(bookService.EditBook(bookData)));
}
