/// <summary>
/// 根据图书条形码查询借阅信息
/// </summary>
/// <param name="bookordernum">图书条形码</param>
/// <param name="conn">数据库连接</param>
/// <returns>借阅信息</returns>
public RrturnBookinfo QueryReturnBook(Guid bookordernum, IDbConnection conn)
{
//--借阅查询
string sqltxt = @"select LoanInfo.UserName,LoanInfo.OrderNum,BookInfo.Title, LoanInfo.LoanDate,LoanInfo.IsPaied,LoanInfo.Fine
FROM LoanInfo INNER JOIN BookItem ON LoanInfo.OrderNum = BookItem.OrderNum
INNER JOIN BookInfo ON BookItem.BookID = BookInfo.BookID
where BookItem.ISLoan = 1 and LoanInfo.OrderNum = @Ordernum";
RrturnBookinfo rturnBookinfo = new RrturnBookinfo();
SqlParameter[] parameters = { new SqlParameter("@Ordernum", SqlDbType.UniqueIdentifier) };
parameters[0].Value = bookordernum;
using (SqlDataReader result = SqlHelper.ExecuteReader(null, conn as SqlConnection, sqltxt, parameters))
{
if (result.HasRows)
{
while (result.Read())
{
// 借阅人账号
if (!result.IsDBNull(0))
{
rturnBookinfo.UserName = result.GetString(0);
}
// 图书条形码
if (!result.IsDBNull(1))
{
rturnBookinfo.BookOrderNum = result.GetGuid(1);
}
// 图书名称
if (!result.IsDBNull(2))
{
rturnBookinfo.Booktitle = result.GetString(2);
}
// 借阅日期
if (!result.IsDBNull(3))
{
rturnBookinfo.BorrowDate = result.GetDateTime(3);
}
// 是否缴纳罚款
if (!result.IsDBNull(4))
{
rturnBookinfo.BookState = result.GetInt32(4);
}
// 罚款金额
if (!result.IsDBNull(5))
{
rturnBookinfo.PayMoney = result.GetDecimal(5);
}
}
}
}
return(rturnBookinfo);
}
/// <summary>
/// 获取(未归还)图书信息
/// </summary>
/// <param name="bookguid">书di</param>
/// <param name="conn">数据库连接</param>
/// <returns>该书信息</returns>
private OutTimeBookInfo GetReturnBookInfo(Guid bookguid, IDbConnection conn)
{
RrturnBookinfo retbookinfo = this.loanInfoAccess.GetReturnBookInfo(false, bookguid, conn);
OutTimeBookInfo outTimeBookInfo = new OutTimeBookInfo();
if (!string.IsNullOrEmpty(retbookinfo.UserName))
{
outTimeBookInfo.UserName = retbookinfo.UserName;
outTimeBookInfo.BookOrderNum = retbookinfo.BookOrderNum;
outTimeBookInfo.Booktitle = retbookinfo.Booktitle;
outTimeBookInfo.BorrowDate = retbookinfo.BorrowDate;
}
return(outTimeBookInfo);
}
/// <summary>
/// 获取借阅信息
/// </summary>
/// <param name="isback">是否已还</param>
/// <param name="username">用户id</param>
/// <param name="conn">数据库连接</param>
/// <returns>借阅信息表</returns>
public List <RrturnBookinfo> GetBorrowBookInfo(bool isback, string username, IDbConnection conn)
{
string sqltext = @"select LoanInfo.UserName, LoanInfo.OrderNum,BookInfo.Title, LoanInfo.LoanDate , LoanInfo.ReturnDate, LoanInfo.IsPaied, LoanInfo.Fine FROM LoanInfo INNER JOIN BookItem ON LoanInfo.OrderNum = BookItem.OrderNum INNER JOIN BookInfo ON BookItem.BookID = BookInfo.BookID where LoanInfo.UserName = @username";
StringBuilder sqlsb = new StringBuilder(sqltext);
if (isback)
{
// 获取归还的图书
sqlsb.Append(" and LoanInfo.ReturnDate IS NOT NULL");
}
else
{
// 获取未归还的图书
sqlsb.Append(" and LoanInfo.ReturnDate IS NULL");
}
SqlParameter[] parameters = { new SqlParameter("@username", SqlDbType.VarChar, 50) };
parameters[0].Value = username;
List <RrturnBookinfo> rturnBookinfolist = new List <RrturnBookinfo>();
using (SqlDataReader result = SqlHelper.ExecuteReader(null, conn as SqlConnection, sqlsb.ToString(), parameters))
{
if (result.HasRows)
{
while (result.Read())
{
RrturnBookinfo rturnBookinfo = new RrturnBookinfo();
// 借阅人账号
if (!result.IsDBNull(0))
{
rturnBookinfo.UserName = result.GetString(0);
}
// 图书条形码
if (!result.IsDBNull(1))
{
rturnBookinfo.BookOrderNum = result.GetGuid(1);
}
// 图书名称
if (!result.IsDBNull(2))
{
rturnBookinfo.Booktitle = result.GetString(2);
}
// 借阅日期
if (!result.IsDBNull(3))
{
rturnBookinfo.BorrowDate = result.GetDateTime(3);
}
if (isback)
{
// 还书日期
if (!result.IsDBNull(4))
{
rturnBookinfo.ReturnDate = result.GetDateTime(4);
}
}
else
{
rturnBookinfo.ReturnDate = rturnBookinfo.BorrowDate.AddDays(30);
}
// 罚款状态
if (!result.IsDBNull(5))
{
rturnBookinfo.BookState = result.GetBoolean(5) ? 1 : 0;
}
// 罚款金额
if (!result.IsDBNull(6))
{
rturnBookinfo.PayMoney = result.GetDecimal(6);
}
rturnBookinfolist.Add(rturnBookinfo);
}
}
result.Close();
}
return(rturnBookinfolist);
}
/// <summary>
/// 获取(归还/未归还)图书信息
/// </summary>
/// <param name="isreturn">是否归还</param>
/// <param name="bookguid">书di</param>
/// <param name="conn">数据库连接</param>
/// <returns>该书信息</returns>
public RrturnBookinfo GetReturnBookInfo(bool isreturn, Guid bookguid, IDbConnection conn)
{
string sqltext = @"select LoanInfo.UserName, LoanInfo.OrderNum,BookInfo.Title, LoanInfo.LoanDate , LoanInfo.ReturnDate, LoanInfo.Fine FROM LoanInfo INNER JOIN BookItem ON LoanInfo.OrderNum = BookItem.OrderNum INNER JOIN BookInfo ON BookItem.BookID = BookInfo.BookID where LoanInfo.OrderNum = @bookguid ";
StringBuilder sqlsb = new StringBuilder(sqltext);
if (isreturn)
{
// 获取归还的图书
sqlsb.Append(" and LoanInfo.ReturnDate IS NOT NULL");
}
else
{
// 获取未归还的图书
sqlsb.Append(" and LoanInfo.ReturnDate IS NULL");
}
SqlParameter[] parameters = { new SqlParameter("@bookguid", SqlDbType.UniqueIdentifier) };
parameters[0].Value = bookguid;
RrturnBookinfo rturnBookinfo = new RrturnBookinfo();
using (SqlDataReader result = SqlHelper.ExecuteReader(null, conn as SqlConnection, sqlsb.ToString(), parameters))
{
if (result.HasRows)
{
while (result.Read())
{
// 借阅人账号
if (!result.IsDBNull(0))
{
rturnBookinfo.UserName = result.GetString(0);
}
// 图书条形码
if (!result.IsDBNull(1))
{
rturnBookinfo.BookOrderNum = result.GetGuid(1);
}
// 图书名称
if (!result.IsDBNull(2))
{
rturnBookinfo.Booktitle = result.GetString(2);
}
// 借阅日期
if (!result.IsDBNull(3))
{
rturnBookinfo.BorrowDate = result.GetDateTime(3);
}
if (isreturn)
{
// 还书日期
if (!result.IsDBNull(4))
{
rturnBookinfo.ReturnDate = result.GetDateTime(4);
}
}
// 罚款金额
if (!result.IsDBNull(5))
{
rturnBookinfo.PayMoney = result.GetDecimal(5);
}
}
}
result.Close();
}
return(rturnBookinfo);
}
/// <summary>
/// 查询借书人 已还图书中还未付清罚款的信息
/// </summary>
/// <param name="name">借书人</param>
/// <param name="conn">数据库连接</param>
/// <returns>欠款信息</returns>
public List <RrturnBookinfo> GetReturnUserInfo(string name, IDbConnection conn)
{
string sqltext = @"select LoanInfo.UserName, LoanInfo.OrderNum,BookInfo.Title, LoanInfo.LoanDate , LoanInfo.ReturnDate, LoanInfo.Fine FROM LoanInfo INNER JOIN BookItem ON LoanInfo.OrderNum = BookItem.OrderNum INNER JOIN BookInfo ON BookItem.BookID = BookInfo.BookID where LoanInfo.UserName = @Username and LoanInfo.IsPaied = 0 and LoanInfo.ReturnDate IS NOT NULL";
SqlParameter[] parameters = { new SqlParameter("@Username", SqlDbType.VarChar, 50) };
parameters[0].Value = name;
List <RrturnBookinfo> bookinfoList = new List <RrturnBookinfo>();
using (SqlDataReader result = SqlHelper.ExecuteReader(null, conn as SqlConnection, sqltext, parameters))
{
if (result.HasRows)
{
while (result.Read())
{
RrturnBookinfo rturnBookinfo = new RrturnBookinfo();
// 借阅人账号
if (!result.IsDBNull(0))
{
rturnBookinfo.UserName = result.GetString(0);
}
// 图书条形码
if (!result.IsDBNull(1))
{
rturnBookinfo.BookOrderNum = result.GetGuid(1);
}
// 图书名称
if (!result.IsDBNull(2))
{
rturnBookinfo.Booktitle = result.GetString(2);
}
// 借阅日期
if (!result.IsDBNull(3))
{
rturnBookinfo.BorrowDate = result.GetDateTime(3);
}
// 还书日期
if (!result.IsDBNull(4))
{
rturnBookinfo.ReturnDate = result.GetDateTime(4);
}
// 罚款金额
if (!result.IsDBNull(5))
{
rturnBookinfo.PayMoney = result.GetDecimal(5);
}
bookinfoList.Add(rturnBookinfo);
}
}
result.Close();
}
return(bookinfoList);
}
/// <summary>
/// 借阅查询
/// </summary>
/// <param name="username">借阅人</param>
/// <param name="isdisreturn">是否查询已还图书</param>
/// <param name="conn">数据库连接</param>
/// <returns>借阅信息</returns>
public List <RrturnBookinfo> QueryBorrowBookInfo(string username, bool isdisreturn, IDbConnection conn)
{
//--借阅查询
string sqltxt = @"select LoanInfo.UserName,LoanInfo.OrderNum,BookInfo.Title, LoanInfo.LoanDate, LoanInfo.IsPaied, LoanInfo.Fine FROM LoanInfo INNER JOIN BookItem ON LoanInfo.OrderNum = BookItem.OrderNum INNER JOIN BookInfo ON BookItem.BookID = BookInfo.BookID where LoanInfo.UserName = @Username";
StringBuilder sqlsb = new StringBuilder(sqltxt);
// 如果要查询已还图书
if (!isdisreturn)
{
sqlsb.Append("and BookItem.ISLoan = 1");
}
SqlParameter[] parameters = { new SqlParameter("@Username", SqlDbType.UniqueIdentifier) };
parameters[0].Value = username;
List <RrturnBookinfo> bookinfoList = new List <RrturnBookinfo>();
using (SqlDataReader result = SqlHelper.ExecuteReader(null, conn as SqlConnection, sqltxt, parameters))
{
if (result.HasRows)
{
while (result.Read())
{
RrturnBookinfo rturnBookinfo = new RrturnBookinfo();
// 借阅人账号
if (!result.IsDBNull(0))
{
rturnBookinfo.UserName = result.GetString(0);
}
// 图书条形码
if (!result.IsDBNull(1))
{
rturnBookinfo.BookOrderNum = result.GetGuid(1);
}
// 图书名称
if (!result.IsDBNull(2))
{
rturnBookinfo.Booktitle = result.GetString(2);
}
// 借阅日期
if (!result.IsDBNull(3))
{
rturnBookinfo.BorrowDate = result.GetDateTime(3);
}
// 还书日期
if (!result.IsDBNull(4))
{
rturnBookinfo.ReturnDate = result.GetDateTime(4);
}
// 是否缴纳罚款
if (!result.IsDBNull(5))
{
rturnBookinfo.BookState = result.GetInt32(5);
}
// 罚款金额
if (!result.IsDBNull(6))
{
rturnBookinfo.PayMoney = result.GetDecimal(6);
}
bookinfoList.Add(rturnBookinfo);
}
}
}
return(bookinfoList);
}