public ActionResult Index(Models.BookSearchArg bookSearchArg)
{
var books = Models.BookService.bookData;
if (bookSearchArg.BOOK_NAME != null)
{
books = books.FindAll(x => x.BOOK_NAME.Contains(bookSearchArg.BOOK_NAME));
}
if (bookSearchArg.BOOK_CLASS_ID != null)
{
books = books.FindAll(x => x.BOOK_CLASS_ID == bookSearchArg.BOOK_CLASS_ID);
}
if (bookSearchArg.USER_ID != null)
{
books = books.FindAll(x => x.BOOK_KEEPER == bookSearchArg.USER_ID);
}
if (bookSearchArg.CODE_ID != null)
{
books = books.FindAll(x => x.BOOK_STATUS == bookSearchArg.CODE_ID);
}
ViewBag.Books = books;
ViewBag.BookClassSelectList = bookClassService.GetBookClassSelectList();
ViewBag.MemberSelectList = memberService.GetMemberSelectList();
ViewBag.BookStatusSelectList = bookStatusService.GetBookStatusSelectList();
return(View("Index"));
}
public ActionResult Index(Models.BookSearchArg arg)
{
Models.LibraryService libraryService = new Models.LibraryService();
ViewBag.SearchResult = libraryService.GetEmployeeByCondtioin(arg);
//ViewBag.JobTitleCodeData = this.codeService.GetCodeTable("TITLE");
return(View("Index"));
}
/// <summary>
/// 依照條件取得書本資料
/// </summary>
/// <returns></returns>
public List <Models.Books> GetBookByCondition(Models.BookSearchArg arg)
{
DataTable dt = new DataTable();
string sql = @"SELECT BOOK_CLASS_NAME, BOOK_NAME, BOOK_BOUGHT_DATE, CODE_NAME, USER_ENAME
FROM BOOK_DATA
LEFT JOIN BOOK_LEND_RECORD
ON BOOK_DATA.BOOK_ID = BOOK_LEND_RECORD.BOOK_ID
LEFT JOIN BOOK_CLASS
ON BOOK_DATA.BOOK_CLASS_ID = BOOK_CLASS.BOOK_CLASS_ID
LEFT JOIN MEMBER_M
ON KEEPER_ID = USER_ID
LEFT JOIN BOOK_CODE
ON BOOK_STATUS = CODE_ID
WHERE CODE_TYPE = 'BOOK_STATUS' AND BOOK_NAME != '' AND
(BOOK_CLASS_NAME = @BookClassName or @BookClassName='') AND
(UPPER(BOOK_NAME) LIKE UPPER('%' + @BookName + '%')or @BookName='') AND
(CODE_NAME = @Status or @Status='') AND
(USER_ENAME = @UserName or @UserName='')";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BookClassName", arg.BookClassName == null ? string.Empty : arg.BookClassName));
cmd.Parameters.Add(new SqlParameter("@BookName", arg.BookName == null ? string.Empty : arg.BookName));
cmd.Parameters.Add(new SqlParameter("@Status", arg.Status == null ? string.Empty : arg.Status));
cmd.Parameters.Add(new SqlParameter("@UserName", arg.UserName == null ? string.Empty : arg.UserName));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
return(this.MapBookDataToList(dt));
}
// /// <summary>
/// 依照條件取得圖書資料
/// </summary>
/// <returns></returns>
public List <Models.Library> GetEmployeeByCondtioin(Models.BookSearchArg arg)
{
DataTable dt = new DataTable();
string sql = @"SELECT bd.BOOK_NAME,
bd.BOOK_AUTHOR,
bd.BOOK_PUBLISHER,
bd.BOOK_NOTE,
bd.BOOK_BOUGHT_DATE,
bc.CODE_NAME,
bcl.BOOK_CLASS_NAME,
m.USER_ENAME
FROM BOOK_LEND_RECORD AS blr
INNER JOIN MEMBER_M AS m
ON blr.KEEPER_ID = m.USER_ID
INNER JOIN BOOK_DATA AS bd
ON blr.BOOK_ID = bd.BOOK_ID
INNER JOIN BOOK_CODE AS bc
ON bc.CODE_ID = bd.BOOK_STATUS
INNER JOIN BOOK_CLASS AS bcl
ON bd.BOOK_CLASS_ID = bcl.BOOK_CLASS_ID";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BOOK_CLASS_NAME", arg.BOOK_CLASS_NAME == null ? string.Empty : arg.BOOK_CLASS_NAME));
cmd.Parameters.Add(new SqlParameter("@BOOK_NAME", arg.BOOK_NAME == null ? string.Empty : arg.BOOK_NAME));
cmd.Parameters.Add(new SqlParameter("@CODE_NAME", arg.CODE_NAME == null ? string.Empty : arg.CODE_NAME));
cmd.Parameters.Add(new SqlParameter("@USER_ENAME", arg.USER_ENAME == null ? "1900/01/01" : arg.USER_ENAME));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
return(this.MapEmployeeDataToList(dt));
}
public List <Models.BookSearchArg> GetBookByCondtioin(Models.BookSearchArg Book)
{
DataTable dt = new DataTable();
string sql = @"Select BOOK_ID,
BOOKCLASS.BOOK_CLASS_NAME,
BOOK_NAME,
BOOK_BOUGHT_DATE,
MEMBER.USER_CNAME,
CODE.CODE_NAME
From [dbo].[BOOK_DATA] as b
LEFT JOIN [dbo].[BOOK_CLASS] AS BOOKCLASS ON b.BOOK_CLASS_ID=BOOKCLASS.BOOK_CLASS_ID
LEFT JOIN [dbo].[MEMBER_M] AS MEMBER ON b.BOOK_KEEPER=MEMBER.USER_ID
LEFT JOIN [dbo].[BOOK_CODE] AS CODE ON b.BOOK_STATUS=CODE.CODE_ID
Where(b.BOOK_CLASS_ID = @Book_Class_Id OR @Book_Class_Id='') AND
(b.BOOK_NAME LIKE ('%'+ @Book_Name +'%') OR @Book_Name='') AND
(b.BOOK_STATUS = @Book_Status OR @Book_Status='') AND
(b.BOOK_KEEPER = @Book_Keeper OR @Book_Keeper='')";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@Book_Class_Id", Book.BOOK_CLASS_ID == null ? string.Empty : Book.BOOK_CLASS_ID));
cmd.Parameters.Add(new SqlParameter("@Book_Name", Book.BOOK_NAME == null ? string.Empty : Book.BOOK_NAME));
cmd.Parameters.Add(new SqlParameter("@Book_Status", Book.BOOK_STATUS == null ? string.Empty : Book.BOOK_STATUS));
cmd.Parameters.Add(new SqlParameter("@Book_Keeper", Book.BOOK_KEEPER == null ? string.Empty : Book.BOOK_KEEPER));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
return(this.MapBookDataToList(dt));
}
public ActionResult Index(Models.BookSearchArg bookSearchArg)
{
ViewBag.BookClassSelectList = bookClassService.GetBookClassSelectList();
ViewBag.MemberSelectList = memberService.GetMemberSelectList();
ViewBag.BookStatusSelectList = bookStatusService.GetBookStatusSelectList();
return(View("Index"));
}
public ActionResult Index(Models.BookSearchArg arg)
{
Models.BookService bookService = new Models.BookService();
ViewBag.SearchResult = bookService.GetBookByCondition(arg);
ViewBag.JobTitleCodeData = this.codeService.GetCodeTable("TITLE");
return(View("Index"));
}
/// <summary>
/// 查詢書籍資料
/// </summary>
/// <returns></returns>
public List <Models.BookData> GetBookData(Models.BookSearchArg arg)
{
DataTable dt = new DataTable();
string sql = @"SELECT b.BOOK_ID, b.BOOK_NAME, b.BOOK_CLASS_ID, c.BOOK_CLASS_NAME,
b.BOOK_BOUGHT_DATE, b.BOOK_STATUS, d.CODE_NAME, b.BOOK_KEEPER,
m.USER_CNAME
FROM dbo.BOOK_DATA as b
LEFT JOIN dbo.BOOK_CLASS as c
ON b.BOOK_CLASS_ID = c.BOOK_CLASS_ID
LEFT JOIN dbo.BOOK_CODE as d
ON b.BOOK_STATUS = d.CODE_ID
LEFT JOIN dbo.MEMBER_M as m
ON b.BOOK_KEEPER = m.USER_ID
WHERE (UPPER(b.BOOK_NAME) LIKE UPPER( '%' + @BookName + '%' ) OR @BookName='' )
AND ( b.BOOK_CLASS_ID = @BookClassId OR @BookClassId = '' )
AND ( b.BOOK_KEEPER = @BookKeeper OR @BookKeeper = '' )
AND ( b.BOOK_STATUS = @BookStatus OR @BookStatus = '' )
ORDER BY BOOK_BOUGHT_DATE DESC";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BookName", arg.BOOK_NAME == null ? string.Empty : arg.BOOK_NAME));
cmd.Parameters.Add(new SqlParameter("@BookClassId", arg.BOOK_CLASS_ID == null ? string.Empty : arg.BOOK_CLASS_ID));
cmd.Parameters.Add(new SqlParameter("@BookKeeper", arg.BOOK_KEEPER == null ? string.Empty : arg.BOOK_KEEPER));
cmd.Parameters.Add(new SqlParameter("@BookStatus", arg.BOOK_STATUS == null ? string.Empty : arg.BOOK_STATUS));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
return(MapBookDataToList(dt));
}
public JsonResult Index(Models.BookSearchArg book)
{
Models.BookServices bookService = new Models.BookServices();
ViewBag.SearchResult = bookService.GetBookByCondtioin(book);
var result = bookService.GetBookByCondtioin(book);
return(Json(result));
}
public JsonResult Insert(Models.BookSearchArg Book)
{
Models.BookServices bookService = new Models.BookServices();
bookService.InsertBook(Book);
return(Json(Book));
}
public ActionResult Index(Models.BookSearchArg arg)
{
ViewBag.UserNameData = this.codeService.GetUserName();
ViewBag.BookClassNameData = this.codeService.GetBookClassName();
ViewBag.CodeNameData = this.codeService.GetCodeName();
ViewBag.SearchResult = bookService.GetBookByCondtioin(arg);
return(View("Index"));
}
public ActionResult QueryBook(Models.BookSearchArg arg)
{
Models.BookService bookService = new Models.BookService();
ViewBag.BookStatusCodeTable = codeService.GetStatusCodeTable();
ViewBag.BookClassCodeTable = codeService.GetBookClassCodeTable();
ViewBag.MemberCodeTable = codeService.GetMemberCodeTable();
ViewBag.SearchResult = bookService.GetBookByCondtioin(arg);
return(View());
}
public JsonResult Insert(Models.BookSearchArg Book)
{
Models.BookServices bookService = new Models.BookServices();
bookService.InsertBook(Book);
//TempData["message"] = "存檔成功";
return(Json(Book));
}
public JsonResult Index(Models.BookSearchArg book)
{
Models.BookServices bookService = new Models.BookServices();
ViewBag.SearchResult = bookService.GetBookByCondtioin(book);
var result = bookService.GetBookByCondtioin(book);
return(Json(result));
//return View();
//return this.Json(ViewBag.SearchResult ,JsonRequestBehavior.AllowGet);
//return Json(result, JsonRequestBehavior.AllowGet);
}
public JsonResult GetSearchResult(Models.BookSearchArg book)
{
Models.BookSearchArg getResult = book;
//getResult.BookName = bookName;
//getResult.BookClassName = bookClassName;
//getResult.UserName = userName;
//getResult.CodeName = codeName;
List <Models.Books> getBook = new List <Models.Books>();
getBook = bookService.GetBookByCondtioin(getResult);
return(Json(getBook));
}
public ActionResult Index(Models.BookSearchArg arg)
{
Models.CodeService codeService = new Models.CodeService();
ViewBag.BookClassNameItem = codeService.GetBookClassName();
ViewBag.BookBoughterItem = codeService.GetBookBoughter();
ViewBag.BookStatusItem = codeService.GetBookStatus();
Models.BookService bookService = new Models.BookService();
ViewBag.BookSearchResult = bookService.GetBookByCondition(arg);
return(InsertBook());
}
public JsonResult Update(Models.BookSearchArg Book)
{
try
{
Models.BookServices bookService = new Models.BookServices();
bookService.UpdateBookById(Book);
return(this.Json(true));
}
catch (Exception ex)
{
return(this.Json(false));
}
}
public List <Models.Book> GetBookByCondtioin(Models.BookSearchArg arg)
{
DataTable dt = new DataTable();
string sql = @"SELECT
Book.BOOK_ID AS '流水號',
Book.BOOK_NAME AS '書名',
Book.BOOK_AUTHOR AS '作者',
Book.BOOK_PUBLISHER AS '出版商',
Book.BOOK_NOTE AS '內容簡介',
FORMAT(Book.BOOK_BOUGHT_DATE, 'yyyy/MM/dd') AS '購買日期',
BookClass.BOOK_CLASS_NAME AS '書籍類別',
BookClass.BOOK_CLASS_ID AS '書籍類別代號',
BookStatus.CODE_NAME AS '借閱狀態',
BookStatus.CODE_ID AS '借閱狀態代號',
ISNULL(Mem.USER_ENAME, '') AS '借閱人',
ISNULL(Mem.[USER_ID], '') AS '借閱人代號'
FROM BOOK_DATA AS Book
INNER JOIN BOOK_CLASS AS BookClass
ON Book.BOOK_CLASS_ID = BookClass.BOOK_CLASS_ID
INNER JOIN BOOK_CODE AS BookStatus
ON Book.BOOK_STATUS = BookStatus.CODE_ID
AND BookStatus.CODE_TYPE = 'BOOK_STATUS'
LEFT JOIN MEMBER_M AS Mem
ON Book.BOOK_KEEPER = Mem.[USER_ID]
WHERE
(ISNULL(@BookClassID, '') = '' OR BookClass.BOOK_CLASS_ID = @BookClassID)
AND Book.BOOK_NAME LIKE '%' + @BookName + '%'
AND (ISNULL(@KeeperID, '') = '' OR Mem.[USER_ID] = @KeeperID)
AND (ISNULL(@StatusCodeID, '') = '' OR BookStatus.CODE_ID = @StatusCodeID)
ORDER BY [購買日期] DESC;";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BookClassID", arg.BookClassID == null ? string.Empty : arg.BookClassID));
cmd.Parameters.Add(new SqlParameter("@BookName", arg.BookName == null ? string.Empty : arg.BookName));
cmd.Parameters.Add(new SqlParameter("@KeeperID", arg.KeeperID == null ? string.Empty : arg.KeeperID));
cmd.Parameters.Add(new SqlParameter("@StatusCodeID", arg.StatusCodeID == null ? string.Empty : arg.StatusCodeID));
SqlDataAdapter sqladapter = new SqlDataAdapter(cmd);
sqladapter.Fill(dt);
conn.Close();
}
return(this.MapBookDataToList(dt));
}
/// <summary>
/// 依照條件取得書籍資料
/// </summary>
/// <returns></returns>
public List <Models.Book> GetBookByCondtioin(Models.BookSearchArg arg)
{
DataTable dt = new DataTable();
string sql = @"SELECT a.BOOK_ID , a.BOOK_NAME
,a.BOOK_CLASS_ID , b.BOOK_CLASS_NAME as CLASS_NAME
,a.BOOK_AUTHOR , CONVERT( varchar(12), a.BOOK_BOUGHT_DATE, 111) as BOOK_BOUGHT_DATE
,a.BOOK_PUBLISHER , a.BOOK_NOTE
,a.BOOK_STATUS , c.CODE_TYPE , c.CODE_NAME as STATUS_NAME
,a.BOOK_KEEPER , case(a.BOOK_STATUS)
WHEN 'A' THEN ' '
WHEN 'U' THEN ' '
ELSE d.USER_ENAME + '(' + d.USER_CNAME + ')'
END as USER_NAME
,a.CREATE_DATE , a.CREATE_USER
,a.MODIFY_DATE , a.MODIFY_USER
FROM BOOK_DATA as a
Left JOIN BOOK_CLASS as b
ON a.BOOK_CLASS_ID = b.BOOK_CLASS_ID
Left JOIN BOOK_CODE as c
ON a.BOOK_STATUS = c.CODE_ID
Left JOIN MEMBER_M as d
ON a.BOOK_KEEPER = d.USER_ID
WHERE (a.BOOK_NAME LIKE '%' + @BookName + '%' ) AND
(a.BOOK_CLASS_ID = @BookClassId OR @BookClassId = '' ) AND
(a.BOOK_KEEPER = @BookKeeper OR @BookKeeper = '' ) AND
(a.BOOK_STATUS = @BookStatus OR @BookStatus = '' ) AND
(c.CODE_TYPE = 'BOOK_STATUS')
ORDER BY BOOK_BOUGHT_DATE DESC
";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BookName", arg.BookName == null ?string.Empty : arg.BookName));
cmd.Parameters.Add(new SqlParameter("@BookClassId", arg.BookClassId == null ? string.Empty : arg.BookClassId));
cmd.Parameters.Add(new SqlParameter("@BookKeeper", arg.BookKeeper == null ?string.Empty : arg.BookKeeper));
cmd.Parameters.Add(new SqlParameter("@BookStatus", arg.BookStatus == null ? string.Empty : arg.BookStatus));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
return(this.MapBookDataToList(dt));
}
/// <summary>
/// 取得書籍資料
/// </summary>
/// <returns></returns>
public List <Models.BOOK_DATA> GetBookByCondition(Models.BookSearchArg arg)
{
string sql = @"Select *
From [dbo].[BOOK_CLASS]
Where BOOK_CLASS_ID =@BOOK_CLASS_ID";
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BOOK_CLASS_ID", arg.BOOK_CLASS_ID == null ? string.Empty : arg.BOOK_CLASS_ID));
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
dataAdapter.Fill(dt);
conn.Close();
}
return(this.MapBookDataToList(dt));
}
/// <summary>
/// 依照條件取得圖書資料
/// </summary>
/// <returns></returns>
public List <Models.BookData> GetBookByCondtioin(Models.BookSearchArg arg)
{
DataTable dt = new DataTable();
string sql = @"SELECT bd.BOOK_ID AS BookID,
bd.BOOK_NAME AS BookName,
bcl.BOOK_CLASS_NAME AS BookClassName,
bcl.BOOK_CLASS_ID AS BookClassID,
CONVERT(VARCHAR,bd.BOOK_BOUGHT_DATE,111) AS BookBoughtDate,
mm.USER_ENAME+'-'+mm.USER_CNAME AS UserName,
mm.USER_ID AS UserID,
bc.CODE_NAME AS CodeName,
bc.CODE_ID AS CodeID
FROM BOOK_DATA AS bd
LEFT JOIN MEMBER_M AS mm
ON bd.BOOK_KEEPER = mm.USER_ID
INNER JOIN BOOK_CLASS AS bcl
ON bd.BOOK_CLASS_ID = bcl.BOOK_CLASS_ID
INNER JOIN BOOK_CODE AS bc
ON (bd.BOOK_STATUS=bc.CODE_ID) AND bc.CODE_TYPE='BOOK_STATUS'
WHERE (UPPER(bd.BOOK_NAME)LIKE UPPER('%'+@BookName+'%') OR @BookName='') AND
(bcl.BOOK_CLASS_ID= @BookClassID OR @BookClassID='') AND
(mm.USER_ID=@UserName OR @UserName='') AND
(bc.CODE_ID= @CodeID OR @CodeID='')
ORDER BY BookBoughtDate DESC";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BookName", arg.BookName == null ? string.Empty : arg.BookName));
cmd.Parameters.Add(new SqlParameter("@BookClassID", arg.ClassNameID == null ? string.Empty : arg.ClassNameID));
cmd.Parameters.Add(new SqlParameter("@UserName", arg.UserName == null ? string.Empty : arg.UserName));
cmd.Parameters.Add(new SqlParameter("@CodeID", arg.CodeID == null ? string.Empty : arg.CodeID));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
return(this.MapBookDataToListForSearch(dt));
}
// list field end
// Book Search
public List <Models.Book> GetBookByCondtioin(Models.BookSearchArg arg)
{
DataTable dt = new DataTable();
string sql = @"SELECT BD.BOOK_CLASS_ID AS BookClassId,
BCL.BOOK_CLASS_NAME AS BookClassName,
BD.BOOK_ID AS BookId,
BD.BOOK_NAME AS BookName,
ISNULL(BD.BOOK_KEEPER, '') AS KeeperId,
ISNULL(MM.USER_ENAME, '') AS Keeper,
BD.BOOK_STATUS AS BookStatusId,
BCO.CODE_NAME AS BookStatus,
FORMAT(BD.BOOK_BOUGHT_DATE, 'yyyy/MM/dd') AS BuyDate
FROM BOOK_DATA AS BD
INNER JOIN BOOK_CLASS BCL ON BD.BOOK_CLASS_ID = BCL.BOOK_CLASS_ID
INNER JOIN BOOK_CODE AS BCO ON BD.BOOK_STATUS = BCO.CODE_ID AND BCO.CODE_TYPE = 'BOOK_STATUS'
LEFT JOIN MEMBER_M AS MM ON BD.BOOK_KEEPER = MM.[USER_ID]
WHERE BD.BOOK_NAME LIKE '%'+@BookName+'%' AND
(BD.BOOK_CLASS_ID = @BookClassId OR '' = @BookClassId) AND
(ISNULL(BD.BOOK_KEEPER, '') = @KeeperId OR '' = @KeeperId) AND
(BD.BOOK_STATUS = @BookStatusId OR '' = @BookStatusId)
ORDER BY BuyDate DESC";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BookName", arg.BookName == null ? string.Empty : arg.BookName));
cmd.Parameters.Add(new SqlParameter("@BookClassId", arg.BookClassId == null ? string.Empty : arg.BookClassId));
cmd.Parameters.Add(new SqlParameter("@KeeperId", arg.KeeperId == null ? string.Empty : arg.KeeperId));
cmd.Parameters.Add(new SqlParameter("@BookStatusId", arg.BookStatusId == null ? string.Empty : arg.BookStatusId));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
return(this.MapBookData(dt));
}
/// <summary>
/// 依照條件取得書籍資料(搜尋)
/// </summary>
public List <Models.BookSearchArg> GetBookByCondtioin(Models.BookSearchArg arg)
{
DataTable dt = new DataTable();
string sql = @"select
BD.BOOK_ID as BookID,
BC.BOOK_CLASS_NAME as BookClassName,
BC.BOOK_CLASS_ID as BookClassID,
BD.BOOK_NAME as BookName,
CONVERT(varchar(12),BD.BOOK_BOUGHT_DATE, 23) as BoughtDate,
BD.BOOK_STATUS as BookStatus,
BCD.CODE_NAME as StatusName,
M.USER_ENAME as UserName,
M.USER_ID as UserID,
M.USER_CNAME as UserCName
FROM BOOK_DATA AS BD
LEFT JOIN MEMBER_M AS M ON BD.BOOK_KEEPER = M.USER_ID
INNER JOIN BOOK_CLASS AS BC ON BD.BOOK_CLASS_ID = BC.BOOK_CLASS_ID
INNER JOIN BOOK_CODE AS BCD ON BD.BOOK_STATUS=BCD.CODE_ID
where BCD.CODE_TYPE='BOOK_STATUS' AND
(UPPER(BD.BOOK_NAME) LIKE UPPER('%' + @BOOK_NAME + '%')or @BOOK_NAME='') AND
(BC.BOOK_CLASS_ID = @BOOK_CLASS_ID OR @BOOK_CLASS_ID='')AND
(M.USER_ID = @USER_ID OR @USER_ID='')AND
(BD.BOOK_STATUS = @BOOK_STATUS OR @BOOK_STATUS='')
ORDER BY BoughtDate DESC";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BOOK_NAME", arg.BOOK_NAME == null ? string.Empty : arg.BOOK_NAME));
cmd.Parameters.Add(new SqlParameter("@BOOK_CLASS_ID", arg.BOOK_CLASS_ID == null ? string.Empty : arg.BOOK_CLASS_ID));
cmd.Parameters.Add(new SqlParameter("@USER_ID", arg.USER_ID == null ? string.Empty : arg.USER_ID));
cmd.Parameters.Add(new SqlParameter("@BOOK_STATUS", arg.BOOK_STATUS == null ? string.Empty : arg.BOOK_STATUS));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
}
return(this.MapBookDataToList(dt));
}
/// <summary>
/// 查詢書籍
/// </summary>
/// <returns></returns>
public List <Models.BookData> GetBookByCondtioin(Models.BookSearchArg arg)
{
DataTable dt = new DataTable();
string sql = @"SELECT BOOK_ID, BOOK_CLASS_NAME ,BOOK_NAME ,BOOK_BOUGHT_DATE ,CODE_NAME ,USER_ENAME
FROM [dbo].[BOOK_DATA] as da
left join [dbo].[BOOK_CLASS] as cl
on da.BOOK_CLASS_ID = cl.BOOK_CLASS_ID
left join [dbo].[BOOK_CODE] as co
on da.BOOK_STATUS = co.CODE_ID
left join [dbo].[MEMBER_M] as m
on da.BOOK_KEEPER = m.USER_ID
Where (da.BOOK_STATUS = co.CODE_ID) AND
(da.BOOK_ID = @Book_ID OR @Book_ID='') AND
(BOOK_NAME LIKE ('%' + @Book_Name + '%')or @Book_Name='') AND
(UPPER(da.BOOK_CLASS_ID) LIKE UPPER('%' + @Book_Class_Name + '%')or @Book_Class_Name='') AND
(BOOK_STATUS LIKE ('%' + @Book_Status + '%')or @Book_Status='') AND
(BOOK_KEEPER LIKE ('%' + @Book_Keeper + '%')or @Book_Keeper='')
ORDER BY BOOK_BOUGHT_DATE DESC";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@Book_ID", arg.Book_ID == null ? string.Empty : arg.Book_ID));
cmd.Parameters.Add(new SqlParameter("@Book_Name", arg.Book_Name == null ? string.Empty : arg.Book_Name));
cmd.Parameters.Add(new SqlParameter("@Book_Class_Name", arg.Book_Class_Name == null ? string.Empty : arg.Book_Class_Name));
cmd.Parameters.Add(new SqlParameter("@Book_Status", arg.Book_Status == null ? string.Empty : arg.Book_Status));
cmd.Parameters.Add(new SqlParameter("@Book_Keeper", arg.Book_Keeper == null ? string.Empty : arg.Book_Keeper));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
return(this.MapBookDataToList(dt));
}
public int InsertBook(Models.BookSearchArg Book)
{
string sql = @" INSERT INTO [dbo].[BOOK_DATA]
(
BOOK_NAME,
BOOK_AUTHOR,
BOOK_PUBLISHER,
BOOK_NOTE,
BOOK_BOUGHT_DATE,
BOOK_CLASS_ID,
BOOK_STATUS,
BOOK_KEEPER
)
VALUES
(
@BOOK_NAME,@BOOK_AUTHOR, @BOOK_PUBLISHER, @BOOK_NOTE, @BOOK_BOUGHT_DATE, @BOOK_CLASS_ID,
@BOOK_STATUS,@BOOK_KEEPER
)" ;
int BookId;
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BOOK_NAME", Book.BOOK_NAME));
cmd.Parameters.Add(new SqlParameter("@BOOK_AUTHOR", Book.BOOK_AUTHOR));
cmd.Parameters.Add(new SqlParameter("@BOOK_PUBLISHER", Book.BOOK_PUBLISHER));
cmd.Parameters.Add(new SqlParameter("@BOOK_NOTE", Book.BOOK_NOTE));
cmd.Parameters.Add(new SqlParameter("@BOOK_BOUGHT_DATE", Book.BOOK_BOUGHT_DATE));
cmd.Parameters.Add(new SqlParameter("@BOOK_CLASS_ID", Book.BOOK_CLASS_ID));
cmd.Parameters.Add(new SqlParameter("@BOOK_STATUS", Book.BOOK_STATUS));
cmd.Parameters.Add(new SqlParameter("@BOOK_KEEPER", Book.BOOK_KEEPER));
BookId = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
}
return(BookId);
}
/// <summary>
/// 依照條件取得客戶資料
/// </summary>
/// <returns></returns>
public List <Models.Books> GetBookByCondition(Models.BookSearchArg arg)
{
//LEFT JOIN dbo.BOOK_CODE as BCD
// ON(BD.BOOK_STATUS = BCD.CODE_ID)
// and BCD.CODE_TYPE = 'STATUS'
DataTable dt = new DataTable();
string sql = @"SELECT BD.BOOK_ID As BookId , BD.BOOK_CLASS_ID As BookClassId, BD.BOOK_NAME As BookName,
CONVERT( varchar(12), BD.BOOK_BOUGHT_DATE, 111) AS BookBoughtDate,
BCD.CODE_NAME AS BookStatusName, M.USER_ENAME AS BookKeeperEName,
M.USER_CNAME As BookKeeperCName, BCL.BOOK_CLASS_NAME As BookClassName
FROM dbo.BOOK_DATA as BD
JOIN dbo.BOOK_CODE as BCD
ON (BD.BOOK_STATUS = BCD.CODE_ID) And BCD.CODE_TYPE = 'BOOK_STATUS'
JOIN dbo.BOOK_CLASS as BCL
ON (BD.BOOK_CLASS_ID = BCL.BOOK_CLASS_ID)
LEFT JOIN dbo.MEMBER_M as M
ON (BD.BOOK_KEEPER = M.USER_ID)
Where (BD.BOOK_CLASS_ID = @BookClassId Or @BookClassId = '') AND
(BD.BOOK_NAME = @BookName Or @BookName = '')
ORDER BY BookBoughtDate DESC";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BookClassId", arg.BOOK_CLASS_ID == null ? string.Empty : arg.BOOK_CLASS_ID));
cmd.Parameters.Add(new SqlParameter("@BookName", arg.BOOK_NAME == null ? string.Empty : arg.BOOK_NAME));
cmd.Parameters.Add(new SqlParameter("@BookKeeper", arg.BOOK_KEEPER == null ? string.Empty : arg.BOOK_KEEPER));
cmd.Parameters.Add(new SqlParameter("@BookStatus", arg.BOOK_CODE_ID == null ? string.Empty : arg.BOOK_CODE_ID));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
//the above are search conditions
return(this.MapBookDataToList(dt));
}
public void UpdateBookById(Models.BookSearchArg Book)
{
try
{
string sql = @"Update [dbo].[BOOK_DATA]
Set BOOK_CLASS_ID=@BOOK_CLASS_ID ,
BOOK_NAME=@BOOK_NAME,
BOOK_BOUGHT_DATE=@BOOK_BOUGHT_DATE,
BOOK_STATUS=@BOOK_STATUS,
BOOK_KEEPER=@BOOK_KEEPER,
BOOK_AUTHOR=@BOOK_AUTHOR,
BOOK_PUBLISHER=@BOOK_PUBLISHER,
BOOK_NOTE=@BOOK_NOTE
Where BOOK_ID = @BOOK_ID";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BOOK_ID", Book.BOOK_ID));
cmd.Parameters.Add(new SqlParameter("@BOOK_CLASS_ID", Book.BOOK_CLASS_ID));
cmd.Parameters.Add(new SqlParameter("@BOOK_NAME", Book.BOOK_NAME));
cmd.Parameters.Add(new SqlParameter("@BOOK_BOUGHT_DATE", Book.BOOK_BOUGHT_DATE));
cmd.Parameters.Add(new SqlParameter("@BOOK_STATUS", Book.BOOK_STATUS));
cmd.Parameters.Add(new SqlParameter("@BOOK_KEEPER", Book.BOOK_KEEPER));
cmd.Parameters.Add(new SqlParameter("@BOOK_AUTHOR", Book.BOOK_AUTHOR));
cmd.Parameters.Add(new SqlParameter("@BOOK_PUBLISHER", Book.BOOK_PUBLISHER));
cmd.Parameters.Add(new SqlParameter("@BOOK_NOTE", Book.BOOK_NOTE));
cmd.ExecuteNonQuery();
conn.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
public JsonResult GetGridData(Models.BookSearchArg arg)
{
return(Json(this.bookService.GetBookByCondtioin(arg)));
}
public JsonResult QueryBook(Models.BookSearchArg arg)
{
Models.BookService bookService = new Models.BookService();
return(this.Json(bookService.GetBookByCondtioin(arg)));
}
public JsonResult GetBookData(Models.BookSearchArg arg)
{
return(Json(BookService.GetBookByCondition(arg)));
}
