public List<CreateBookSystem> createBookSystemDataBase(CreateBookSystem bookSystemData)
{
List<CreateBookSystem> returnValue = new List<CreateBookSystem>();
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
string[] checkBookData = getBookDataName(bookSystemData.bookCode);
if (int.Parse(checkBookData[0]) > 0 && int.Parse(checkBookData[1]) == 1)
{
StaffDataBase sDB = new StaffDataBase();
List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
Sqlconn.Open();
string sql = "INSERT INTO BookManage (Unit, Status, ClassID, BorrowerIdentity, BorrowerID, BookID, BorrowDate, MaturityDate, " +
"CreateFileBy, CreateFileDate, UpFileBy, UpFileDate, isDeleted) VALUES " +
"(@Unit, @Status, @ClassID, @BorrowerIdentity, @BorrowerID, @BookID, (getDate()), DATEADD(day,7,getDate()), " +
"@CreateFileBy, (getDate()), @UpFileBy, (getDate()), 0)";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@Unit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(CreateFileName[2]);
cmd.Parameters.Add("@Status", SqlDbType.TinyInt).Value = 1;
//cmd.Parameters.Add("@ClassID", SqlDbType.BigInt).Value = Chk.CheckStringtoIntFunction(bookSystemData.borrowerClassID);
cmd.Parameters.Add("@ClassID", SqlDbType.BigInt).Value = 0;
cmd.Parameters.Add("@BorrowerIdentity", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(bookSystemData.borrowerStatus);
cmd.Parameters.Add("@BorrowerID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(bookSystemData.borrowerID);
cmd.Parameters.Add("@BookID", SqlDbType.BigInt).Value = Chk.CheckStringtoIntFunction(checkBookData[2]);
cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]);
string numberStr = cmd.ExecuteNonQuery().ToString();
if (numberStr != "0")
{
Int64 Column = 0;
sql = "select IDENT_CURRENT('BookManage') AS bID";
cmd = new SqlCommand(sql, Sqlconn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Column = Int64.Parse(dr["bID"].ToString());
}
dr.Close();
sql = "UPDATE BookDatabase SET Status=@Status WHERE BookID=@bookID AND isDeleted=0";
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@Status", SqlDbType.TinyInt).Value = 2;
cmd.Parameters.Add("@bookID", SqlDbType.BigInt).Value = Chk.CheckStringtoIntFunction(checkBookData[2]);
string numberStr2 = cmd.ExecuteNonQuery().ToString();
if (Column != 0 && numberStr2 != "0")
{
if (bookSystemData.borrowerStatus == "1")
{
sql = "SELECT BookManage.*, BookDatabase.BookCodeID, BookDatabase.BookName, StaffDatabase.StaffName AS BorrowerName FROM BookManage " +
"RIGHT JOIN StaffDatabase ON BookManage.BorrowerID=StaffDatabase.StaffID AND StaffDatabase.isDeleted=0 " +
"RIGHT JOIN BookDatabase ON BookManage.BookID=BookDatabase.BookID AND BookDatabase.isDeleted=0 " +
"WHERE BookManage.isDeleted=0 AND BookManage.ID=@BID";
}
else if (bookSystemData.borrowerStatus == "2")
{
sql = "SELECT BookManage.*, BookDatabase.BookCodeID, BookDatabase.BookName, StudentDatabase.StudentName AS BorrowerName FROM BookManage " +
"RIGHT JOIN StudentDatabase ON BookManage.BorrowerID=StudentDatabase.StudentID AND StudentDatabase.isDeleted=0 " +
"RIGHT JOIN BookDatabase ON BookManage.BookID=BookDatabase.BookID AND BookDatabase.isDeleted=0 " +
"WHERE BookManage.isDeleted=0 AND BookManage.ID=@BID";
}
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@BID", SqlDbType.BigInt).Value = Column;
dr = cmd.ExecuteReader();
while (dr.Read())
{
CreateBookSystem addValue = new CreateBookSystem();
addValue.bID = Column.ToString();
addValue.borrowStatus = dr["Status"].ToString();
addValue.borrowerClassID = dr["ClassID"].ToString();
addValue.borrowerName = dr["BorrowerName"].ToString();
addValue.borrowerID = dr["BorrowerID"].ToString();
addValue.borrowerStatus = dr["BorrowerIdentity"].ToString();
addValue.bookCode = dr["BookCodeID"].ToString();
addValue.bookName = dr["BookName"].ToString();
addValue.borrowDate = DateTime.Parse(dr["BorrowDate"].ToString()).ToString("yyyy-MM-dd");
addValue.expireDate = DateTime.Parse(dr["MaturityDate"].ToString()).ToString("yyyy-MM-dd");
addValue.checkNo = "1";
returnValue.Add(addValue);
}
dr.Close();
Sqlconn.Close();
}
}
Sqlconn.Close();
}
else if (int.Parse(checkBookData[1]) == 2)
{
CreateBookSystem addValue = new CreateBookSystem();
addValue.checkNo = "0";
addValue.errorMsg = "此本圖書已借出";
returnValue.Add(addValue);
}
else if (int.Parse(checkBookData[0]) == 0)
{
CreateBookSystem addValue = new CreateBookSystem();
addValue.checkNo = checkBookData[0];
addValue.errorMsg = "查無此本圖書";
returnValue.Add(addValue);
}
else
{
CreateBookSystem addValue = new CreateBookSystem();
addValue.checkNo = checkBookData[0];
addValue.errorMsg = checkBookData[1];
returnValue.Add(addValue);
}
}
catch (Exception e)
{
CreateBookSystem addValue = new CreateBookSystem();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message.ToString();
returnValue.Add(addValue);
}
}
return returnValue;
}
public List<CreateBookSystem> SearchBookRecord(int indexpage, SearchBookStatistics BookStatisticsData)
{
List<CreateBookSystem> returnValue = new List<CreateBookSystem>();
DataBase Base = new DataBase();
string ConditionReturn = this.SearchBookStatisticsConditionReturn(BookStatisticsData);
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
StaffDataBase sDB = new StaffDataBase();
List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
Sqlconn.Open();
string sql = "";
sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY BookManage.BookID DESC) " +
"AS RowNum, BookManage.*, BookDatabase.BookCodeID, BookDatabase.BookName, StaffDatabase.StaffName AS BorrowerName1, StudentDatabase.StudentName AS BorrowerName2 FROM BookManage " +
"LEFT JOIN StaffDatabase ON BookManage.BorrowerID=StaffDatabase.StaffID AND StaffDatabase.isDeleted=0 " +
"LEFT JOIN StudentDatabase ON BookManage.BorrowerID=StudentDatabase.StudentID AND StudentDatabase.isDeleted=0 " +
"RIGHT JOIN BookDatabase ON BookManage.BookID=BookDatabase.BookID " +
"WHERE BookManage.isDeleted=0 " + ConditionReturn + " ) " +
"AS NewTable " +
"WHERE RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage)";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage;
cmd.Parameters.Add("@txtrecordBookID", SqlDbType.BigInt).Value = Chk.CheckStringtoIntFunction(BookStatisticsData.txtrecordBookID);
cmd.Parameters.Add("@txtrecordBookStartDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtrecordBookStartDate);
cmd.Parameters.Add("@txtrecordBookEndDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtrecordBookEndDate);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CreateBookSystem addValue = new CreateBookSystem();
addValue.borrowerClassID = dr["ClassID"].ToString();
if (dr["BorrowerName1"].ToString() == null || (dr["BorrowerName1"].ToString()).Length == 0)
{
addValue.borrowerName = dr["BorrowerName2"].ToString();
}
else
{
addValue.borrowerName = dr["BorrowerName1"].ToString();
}
addValue.borrowerID = dr["BorrowerID"].ToString();
addValue.borrowerStatus = dr["BorrowerIdentity"].ToString();
addValue.bookCode = dr["BookCodeID"].ToString();
addValue.bookName = dr["BookName"].ToString();
addValue.borrowDate = DateTime.Parse(dr["BorrowDate"].ToString()).ToString("yyyy-MM-dd");
addValue.restoreDate = DateTime.Parse(dr["ReturnDate"].ToString()).ToString("yyyy-MM-dd");
addValue.checkNo = "1";
returnValue.Add(addValue);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
CreateBookSystem addValue = new CreateBookSystem();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message.ToString();
returnValue.Add(addValue);
}
}
return returnValue;
}
public List<CreateBookSystem> setBookReturnDataBase(CreateBookSystem bookSystemData)
{
List<CreateBookSystem> returnValue = new List<CreateBookSystem>();
DataBase Base = new DataBase();
using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString()))
{
try
{
string[] checkBookData = getBookDataName(bookSystemData.bookReturnCode);
if (int.Parse(checkBookData[0]) > 0 && int.Parse(checkBookData[1]) == 2)
{
StaffDataBase sDB = new StaffDataBase();
List<string> CreateFileName = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
Sqlconn.Open();
string sql = "SELECT ID,BorrowerIdentity FROM BookManage WHERE BookID=@bookID AND Status=1 AND isDeleted=0";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@Status", SqlDbType.TinyInt).Value = 2;
cmd.Parameters.Add("@bookID", SqlDbType.BigInt).Value = Chk.CheckStringtoIntFunction(checkBookData[2]);
SqlDataReader dr = cmd.ExecuteReader();
Int64 Column = 0;
string borrowerStatus = "0";
if (dr.Read())
{
Column = Int64.Parse(dr["ID"].ToString());
borrowerStatus = dr["BorrowerIdentity"].ToString();
}
dr.Close();
if (Column != 0)
{
sql = "UPDATE BookManage SET Status=@Status,ReturnDate=(getDate()) WHERE ID=@ID AND Status=1 AND isDeleted=0";
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = Column;
cmd.Parameters.Add("@Status", SqlDbType.TinyInt).Value = 2;
string numberStr = cmd.ExecuteNonQuery().ToString();
if (numberStr != "0")
{
sql = "UPDATE BookDatabase SET Status=@Status WHERE BookID=@bookID AND isDeleted=0";
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@Status", SqlDbType.TinyInt).Value = 1;
cmd.Parameters.Add("@bookID", SqlDbType.BigInt).Value = Chk.CheckStringtoIntFunction(checkBookData[2]);
string numberStr2 = cmd.ExecuteNonQuery().ToString();
if (numberStr2 != "0")
{
if (borrowerStatus == "1")
{
sql = "SELECT BookManage.*, BookDatabase.BookCodeID, BookDatabase.BookName, StaffDatabase.StaffName AS BorrowerName FROM BookManage " +
"RIGHT JOIN StaffDatabase ON BookManage.BorrowerID=StaffDatabase.StaffID AND StaffDatabase.isDeleted=0 " +
"RIGHT JOIN BookDatabase ON BookManage.BookID=BookDatabase.BookID AND BookDatabase.isDeleted=0 " +
"WHERE BookManage.isDeleted=0 AND BookManage.ID=@BID";
}
else if (borrowerStatus == "2")
{
sql = "SELECT BookManage.*, BookDatabase.BookCodeID, BookDatabase.BookName, StudentDatabase.StudentName AS BorrowerName FROM BookManage " +
"RIGHT JOIN StudentDatabase ON BookManage.BorrowerID=StudentDatabase.StudentID AND StudentDatabase.isDeleted=0 " +
"RIGHT JOIN BookDatabase ON BookManage.BookID=BookDatabase.BookID AND BookDatabase.isDeleted=0 " +
"WHERE BookManage.isDeleted=0 AND BookManage.ID=@BID";
}
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@BID", SqlDbType.BigInt).Value = Column;
dr = cmd.ExecuteReader();
while (dr.Read())
{
CreateBookSystem addValue = new CreateBookSystem();
addValue.bID = Column.ToString();
addValue.borrowStatus = dr["Status"].ToString();
addValue.borrowerClassID = dr["ClassID"].ToString();
addValue.borrowerName = dr["BorrowerName"].ToString();
addValue.borrowerID = dr["BorrowerID"].ToString();
addValue.borrowerStatus = dr["BorrowerIdentity"].ToString();
addValue.bookCode = dr["BookCodeID"].ToString();
addValue.bookName = dr["BookName"].ToString();
addValue.borrowDate = DateTime.Parse(dr["BorrowDate"].ToString()).ToString("yyyy-MM-dd");
addValue.expireDate = DateTime.Parse(dr["MaturityDate"].ToString()).ToString("yyyy-MM-dd");
addValue.restoreDate = DateTime.Parse(dr["ReturnDate"].ToString()).ToString("yyyy-MM-dd");
addValue.checkNo = "1";
returnValue.Add(addValue);
}
dr.Close();
Sqlconn.Close();
}
}
}
Sqlconn.Close();
}
else if (int.Parse(checkBookData[1]) == 1)
{
CreateBookSystem addValue = new CreateBookSystem();
addValue.checkNo = "0";
addValue.errorMsg = "此本圖書已歸還";
returnValue.Add(addValue);
}
else if (int.Parse(checkBookData[0]) == 0)
{
CreateBookSystem addValue = new CreateBookSystem();
addValue.checkNo = checkBookData[0];
addValue.errorMsg = "查無此本圖書";
returnValue.Add(addValue);
}
else
{
CreateBookSystem addValue = new CreateBookSystem();
addValue.checkNo = checkBookData[0];
addValue.errorMsg = checkBookData[1];
returnValue.Add(addValue);
}
}
catch (Exception e)
{
CreateBookSystem addValue = new CreateBookSystem();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message.ToString();
returnValue.Add(addValue);
}
}
return returnValue;
}
public List<CreateBookSystem> setBookReturnDataBase(CreateBookSystem bookSystemData)
{
AdministrationDataBase aDB = new AdministrationDataBase();
aDB.libraryFunction();
List<CreateBookSystem> returnValue = new List<CreateBookSystem>();
if (int.Parse(aDB._StaffhaveRoles[3]) == 1)
{
returnValue=aDB.setBookReturnDataBase(bookSystemData);
}
else
{
CreateBookSystem addVlaue = new CreateBookSystem();
addVlaue.checkNo = _noRole;
addVlaue.errorMsg = _errorMsg;
returnValue.Add(addVlaue);
}
return returnValue;
}
