public string[] SearchBookRecordCount(SearchBookStatistics BookStatisticsData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
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 = "SELECT COUNT(*) FROM BookManage WHERE isDeleted=0 " + ConditionReturn;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
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);
returnValue[0] = cmd.ExecuteScalar().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
return returnValue;
}
private string SearchBookStatisticsConditionReturn(SearchBookStatistics BookStatisticsData)
{
string ConditionReturn = "";
if (BookStatisticsData.txtbookStartDay != null && BookStatisticsData.txtbookEndDay != null)
{
ConditionReturn += " AND (SELECT CONVERT(varchar, BookManage.BorrowDate, 23)) BETWEEN CONVERT(varchar, GETDATE() - @txtbookEndDay, 23) AND CONVERT(varchar, GETDATE() - @txtbookStartDay, 23) ";
}
if (BookStatisticsData.txtbookDateStartDate != null && BookStatisticsData.txtbookDateEndDate != null)
{
ConditionReturn += " AND (SELECT CONVERT(varchar, BookManage.BorrowDate, 23)) BETWEEN @txtbookDateStartDate AND @txtbookDateEndDate ";
}
if (BookStatisticsData.txtrecordBookID != null)
{
ConditionReturn += " AND BookManage.BookID=@txtrecordBookID ";
}
if (BookStatisticsData.txtrecordBookStartDate != null && BookStatisticsData.txtrecordBookEndDate != null)
{
ConditionReturn += " AND (SELECT CONVERT(varchar, BookManage.BorrowDate, 23)) BETWEEN @txtrecordBookStartDate AND @txtrecordBookEndDate ";
}
if (BookStatisticsData.txtrecordBorrowerType != null)
{
ConditionReturn += " AND BorrowerIdentity=@txtrecordBorrowerType ";
}
if (BookStatisticsData.txtrecordBorrowerType == "1" && BookStatisticsData.txtrecordBorrowerName != null)
{
ConditionReturn += " AND StaffDatabase.StaffName like @txtrecordBorrowerName ";
}
else if (BookStatisticsData.txtrecordBorrowerType == "2" && BookStatisticsData.txtrecordBorrowerName != null)
{
ConditionReturn += " AND StudentDatabase.StudentName like @txtrecordBorrowerName ";
}
if (BookStatisticsData.txtrecordBorrowerStartDate != null && BookStatisticsData.txtrecordBorrowerEndDate != null)
{
ConditionReturn += " AND (SELECT CONVERT(varchar, BookManage.BorrowDate, 23)) BETWEEN @txtrecordBorrowerStartDate AND @txtrecordBorrowerEndDate ";
}
StaffDataBase sDB = new StaffDataBase();
List<string> UserFile = sDB.getStaffDataName(HttpContext.Current.User.Identity.Name);
if (int.Parse(_StaffhaveRoles[4]) == 0 && UserFile[1].Length > 0)
{
ConditionReturn += " AND BookManage.Unit =" + UserFile[2] + " ";
}
return ConditionReturn;
}
public List<CreateBookRecordBorrower> SearchBookRecordBorrower(int indexpage, SearchBookStatistics BookStatisticsData)
{
List<CreateBookRecordBorrower> returnValue = new List<CreateBookRecordBorrower>();
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.BorrowerID DESC) " +
"AS RowNum, COUNT(BookManage.BorrowerID) AS QCOUNT,BookManage.BorrowerID,BookManage.BorrowerIdentity," +
"StaffDatabase.StaffName AS BorrowerName1,StudentDatabase.StudentName AS BorrowerName2,BookManage.ClassID 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 " +
"WHERE BookManage.isDeleted=0 " + ConditionReturn +
"GROUP BY BookManage.BorrowerID,BookManage.BorrowerIdentity,StaffDatabase.StaffName,StudentDatabase.StudentName,BookManage.ClassID " +
"HAVING COUNT(BookManage.BorrowerID)>=1 ) " +
"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("@txtrecordBorrowerType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(BookStatisticsData.txtrecordBorrowerType);
cmd.Parameters.Add("@txtrecordBorrowerStartDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtrecordBorrowerStartDate);
cmd.Parameters.Add("@txtrecordBorrowerEndDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtrecordBorrowerEndDate);
cmd.Parameters.Add("@txtrecordBorrowerClassID", SqlDbType.BigInt).Value = Chk.CheckStringtoIntFunction(BookStatisticsData.txtrecordBorrowerClassID);
cmd.Parameters.Add("@txtrecordBorrowerName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(BookStatisticsData.txtrecordBorrowerName) + "%";
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CreateBookRecordBorrower addValue = new CreateBookRecordBorrower();
addValue.borrowerClassID = dr["ClassID"].ToString();
if (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.borrowQuantity = dr["QCOUNT"].ToString();
addValue.checkNo = "1";
returnValue.Add(addValue);
}
dr.Close();
Sqlconn.Close();
}
catch (Exception e)
{
CreateBookRecordBorrower addValue = new CreateBookRecordBorrower();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message.ToString();
returnValue.Add(addValue);
}
}
return returnValue;
}
public string[] SearchBookRecordBorrowerCount(SearchBookStatistics BookStatisticsData)
{
string[] returnValue = new string[2];
returnValue[0] = "0";
returnValue[1] = "0";
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 = "SELECT COUNT(*) FROM ( " +
"SELECT COUNT(BookManage.BorrowerID) AS QCOUNT,BorrowerID 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 " +
"WHERE BookManage.isDeleted=0 " + ConditionReturn +
"GROUP BY BorrowerID,BorrowerIdentity " +
"HAVING COUNT(BorrowerID)>=1 " +
") AS NewTable";
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@txtrecordBorrowerType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(BookStatisticsData.txtrecordBorrowerType);
cmd.Parameters.Add("@txtrecordBorrowerStartDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtrecordBorrowerStartDate);
cmd.Parameters.Add("@txtrecordBorrowerEndDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtrecordBorrowerEndDate);
cmd.Parameters.Add("@txtrecordBorrowerClassID", SqlDbType.BigInt).Value = Chk.CheckStringtoIntFunction(BookStatisticsData.txtrecordBorrowerClassID);
cmd.Parameters.Add("@txtrecordBorrowerName", SqlDbType.NVarChar).Value = "%" + Chk.CheckStringFunction(BookStatisticsData.txtrecordBorrowerName) + "%";
returnValue[0] = cmd.ExecuteScalar().ToString();
Sqlconn.Close();
}
catch (Exception e)
{
returnValue[0] = "-1";
returnValue[1] = e.Message.ToString();
}
}
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<CreateBookBorrow> SearchBookDate(SearchBookStatistics BookStatisticsData)
{
List<CreateBookBorrow> returnValue = new List<CreateBookBorrow>();
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 = "SELECT COUNT(*) FROM BookManage WHERE isDeleted=0 AND BorrowerIdentity=1 " + ConditionReturn;
SqlCommand cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@txtbookDateStartDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtbookDateStartDate);
cmd.Parameters.Add("@txtbookDateEndDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtbookDateEndDate);
CreateBookBorrow addValue = new CreateBookBorrow();
addValue.staffBorrowBookSum = cmd.ExecuteScalar().ToString();
sql = "SELECT COUNT(DISTINCT BorrowerID) FROM BookManage WHERE isDeleted=0 AND BorrowerIdentity=1 " + ConditionReturn;
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@txtbookDateStartDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtbookDateStartDate);
cmd.Parameters.Add("@txtbookDateEndDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtbookDateEndDate);
addValue.staffBorrowerSum = cmd.ExecuteScalar().ToString();
sql = "SELECT COUNT(*) FROM BookManage WHERE isDeleted=0 AND BorrowerIdentity=2 " + ConditionReturn;
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@txtbookDateStartDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtbookDateStartDate);
cmd.Parameters.Add("@txtbookDateEndDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtbookDateEndDate);
addValue.studentBorrowBookSum = cmd.ExecuteScalar().ToString();
sql = "SELECT COUNT(DISTINCT BorrowerID) FROM BookManage WHERE isDeleted=0 AND BorrowerIdentity=2 " + ConditionReturn;
cmd = new SqlCommand(sql, Sqlconn);
cmd.Parameters.Add("@txtbookDateStartDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtbookDateStartDate);
cmd.Parameters.Add("@txtbookDateEndDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(BookStatisticsData.txtbookDateEndDate);
addValue.studentBorrowerSum = cmd.ExecuteScalar().ToString();
addValue.bookBorrowStartDate = DateTime.Parse(BookStatisticsData.txtbookDateStartDate).ToString("yyyy-MM-dd");
addValue.bookBorrowEndDate = DateTime.Parse(BookStatisticsData.txtbookDateEndDate).ToString("yyyy-MM-dd");
addValue.checkNo = "1";
returnValue.Add(addValue);
Sqlconn.Close();
}
catch (Exception e)
{
CreateBookBorrow addValue = new CreateBookBorrow();
addValue.checkNo = "-1";
addValue.errorMsg = e.Message.ToString();
returnValue.Add(addValue);
}
}
return returnValue;
}
public string[] searchBookRecordDataCount(SearchBookStatistics BookStatisticsData)
{
AdministrationDataBase aDB = new AdministrationDataBase();
aDB.libraryFunction();
if (int.Parse(aDB._StaffhaveRoles[3]) == 1)
{
return aDB.SearchBookRecordCount(BookStatisticsData);
}
else
{
return new string[2] { _noRole, _errorMsg };
}
}
public List<CreateBookSystem> searchBookRecordData(int index, SearchBookStatistics BookStatisticsData)
{
AdministrationDataBase aDB = new AdministrationDataBase();
return aDB.SearchBookRecord(index, BookStatisticsData);
}
public List<CreateBookBorrow> searchBookDateData(SearchBookStatistics BookStatisticsData)
{
AdministrationDataBase aDB = new AdministrationDataBase();
aDB.libraryFunction();
List<CreateBookBorrow> returnValue = new List<CreateBookBorrow>();
if (int.Parse(aDB._StaffhaveRoles[3]) == 1)
{
returnValue = aDB.SearchBookDate(BookStatisticsData);
}
else
{
CreateBookBorrow addValue=new CreateBookBorrow();
addValue.checkNo = _noRole;
addValue.errorMsg = _errorMsg;
returnValue.Add(addValue);
}
return returnValue;
}