public static DamageReason queryReasonByIndex(string index)
{
string sql = "SELECT * FROM damageReason WHERE damageReasonIndex = @index";
SqlParameter param = new SqlParameter("@index", index);
SQLHelper.CoverToObject cto = new SQLHelper.CoverToObject(ReaderToDamageReason);
return(SQLHelper.Query(sql, cto, param)[0] as DamageReason);
}
public static ReaderType QueryReaderTypeInfo(int typeID)
{
string sql = "SELECT * FROM ReaderType WHERE typeID= @typeID";
SqlParameter para = new SqlParameter("@typeID", typeID.ToString());
SQLHelper.CoverToObject cto = new SQLHelper.CoverToObject(ReaderToBorrowRecordClass);
var list = SQLHelper.Query(sql, cto, para);
ReaderType Info = list[0] as ReaderType;
return(Info);
}
public static LibraryCard QueryLibraryCardIfo(string LibraryCardID)
{
string sql = "SELECT * FROM libraryCard WHERE libraryCardID= @libraryCardID";
SqlParameter para = new SqlParameter("@libraryCardID", LibraryCardID);
SQLHelper.CoverToObject cto = new SQLHelper.CoverToObject(ReaderToBorrowRecordClass);
var list = SQLHelper.Query(sql, cto, para);
LibraryCard cardInfo = list[0] as LibraryCard;
return(cardInfo);
}
public static decimal getPrice(string circuBookNo)
{
string sql = "select price from circuBookClass,circuBook where circuBook.isbn=circuBookClass.isbn and circuBook.circuBookNo=@circuBookNo";
SqlParameter para = new SqlParameter("@circuBookNo", circuBookNo);
SQLHelper.CoverToObject cto = new SQLHelper.CoverToObject(ReaderToPrice);
var list = SQLHelper.Query(sql, cto, para);
decimal price = (decimal)list[0];
return(price);
}
public static int getOverDueDay(string circuBookNo)
{
string sql = "select datediff(DAY,GETDATE(),borrowRecord.dateToReturn)as atime from borrowRecord where circuBookNo=@circuBookNo and returnTime is null";
SqlParameter para = new SqlParameter("@circuBookNo", circuBookNo);
SQLHelper.CoverToObject cto = new SQLHelper.CoverToObject(ReaderToOverDue);
var list = SQLHelper.Query(sql, cto, para);
int overDueDay = (int)list[0];
return(overDueDay);
}
public static CircuBookClass queryBookInfo(string circuBookNo)
{
string sql = "select bookName,publishingHouse from circuBookClass, circuBook where circuBook.isbn = circuBookClass.isbn and circuBook.circuBookNo =(select circuBookNo from borrowRecord where circuBookNo=@circuBookNo and returnTime is null)";
SqlParameter para = new SqlParameter("@circuBookNo", circuBookNo);
SQLHelper.CoverToObject cto = new SQLHelper.CoverToObject(ReaderToBkPH);
var list = SQLHelper.Query(sql, cto, para);
CircuBookClass cBookc = list[0] as CircuBookClass;
return(cBookc);
}
/// <summary>
/// 根据书籍标识码查询对应书籍类的全部信息
/// </summary>
/// <param name="circuBookNo"></param>
/// <returns></returns>
public static CircuBookClass queryABookInfo(string circuBookNo)
{
// 注意连接字符串之间的空格
string sql = "SELECT circuBook.isbn,bookName,mainAuthor,otherAuthor,publicationYear,publishingHouse,price,bookNum " +
" FROM circuBook JOIN circuBookClass ON (circuBook.isbn = circuBookClass.isbn)" +
" WHERE circuBookNo = @cNo";
SqlParameter para = new SqlParameter("@cNo", circuBookNo);
SQLHelper.CoverToObject cto = new SQLHelper.CoverToObject(ReaderToCircuBookClass);
var cbookc = SQLHelper.Query(sql, cto, para)[0];
return(cbookc as CircuBookClass);
}
public static void InsertDamageRecord(int damageReasonIndex, string circuBookNo, decimal damageMoney)
{
string sql1 = "select libraryCardID from borrowRecord where circuBookNo=@circuBookNo and returnTime is NULL";
SQLHelper.CoverToObject cto1 = new SQLHelper.CoverToObject(ReaderToID);
SqlParameter para1 = new SqlParameter("@circuBookNo", circuBookNo);
var list = SQLHelper.Query(sql1, cto1, para1);
int ID = (int)list[0];
SqlParameter para2 = new SqlParameter("@damageReasonIndex", damageReasonIndex);
string sql2 = "insert into damageRecord(damageReasonIndex,libraryCardID,circuBookNo,damageTime,damageMoney) values(@damageReasonIndex,@libraryCardID,@circuBookNo,GETDATE(),@damageMoney)";
SqlParameter para3 = new SqlParameter("@libraryCardID", ID);
SqlParameter para4 = new SqlParameter("@damageMoney", damageMoney);
SqlParameter para5 = new SqlParameter("@circuBookNo", circuBookNo);
SQLHelper.Update(sql2, para2, para3, para5, para4);
}
public static List <DamageReason> getAllReason()
{
string sql = "SELECT * FROM damageReason";
SQLHelper.CoverToObject cto = new SQLHelper.CoverToObject(ReaderToDamageReason);
var objList = SQLHelper.Query(sql, cto);
List <DamageReason> list = new List <DamageReason>();
foreach (var i in objList)
{
list.Add(i as DamageReason);
}
return(list);
}
