/// <summary>
/// 修改资料信息
/// tb_MatInfo
/// </summary>
/// <param name="bookInfo"></param>
/// <returns></returns>
public int UpdateMatInfo(BookInfo_Table bookInfo)
{
string sqlStr = @"UPDATE [db_RMS].[dbo].[tb_BookInfo] "
+ "SET [BookGuid] ='" + bookInfo.BookGuid + "'"
+ ",[BookISBN]='" + bookInfo.BookISBN + "'"
+ ",[BookRawName]='" + bookInfo.BookRawName + "'"
+ ",[BookCNName]='" + bookInfo.BookCNName + "'"
+ ",[BookPubDate]='" + bookInfo.BookPubDate + "'"
+ ",[BookNumber] ='" + bookInfo.BookNumber + "'"
+ ",[BookBuyDate] ='" + bookInfo.BookBuyDate + "'"
+ ",[BookBuyPerson] ='" + bookInfo.BookBuyPerson + "'"
+ ",[BookBuyShop] = '" + bookInfo.BookBuyShop + "'"
+ ",[BookTypeGuid] ='" + bookInfo.BookTypeGuid + "'"
+ ",[BookLanType]='" + bookInfo.BookLanType + "'"
+ ",[BookAnnotation] ='" + bookInfo.BookAnnotation + "'"
+ ",[BookIsDelete]='" + bookInfo.BookIsDelete + "'"
+ " WHERE [BookGuid] = '" + bookInfo.BookGuid + "'";
return(DBhelp.Create().ExecuteNonQuery(sqlStr));
}
/// <summary>
/// 根据资料类型查询资料
/// 并按照年份筛选
/// </summary>
/// <param name="checkedMatTypeList"></param>
/// <param name="year"></param>
/// <returns></returns>
public List <BookInfo_View> SelectMatInfoByYear(List <string> checkedMatTypeList, string year)
{
List <BookInfo_View> rtnList = new List <BookInfo_View>();
foreach (string matTypeName in checkedMatTypeList)
{
string sqlStr = @"SELECT [MatGuid],[MatID],[MatName],[MatUnitName],[MatNumber],[SendDept],[StartTime],[EndTime],[RoomName],[PersonName],"
+ "[MatTypeName],[EquipTypeName],[Annotation] FROM [db_RMS].[dbo].[View_MatInfo] where MatTypeName='" + matTypeName + "' and datename(year,StartTime)='" + year + "'";
SqlDataReader reader = DBhelp.Create().ExecuteReader(sqlStr);
while (reader.Read())
{
rtnList.Add(new BookInfo_View(reader.GetGuid(0).ToString(), reader.GetString(1).Trim().ToString(), reader.GetString(2).Trim().ToString(),
reader.GetString(3).ToString(), reader.GetString(4).Trim().ToString(), reader.GetString(5).Trim().ToString(), reader.GetDateTime(6),
reader.GetInt32(7), reader.GetDateTime(8), reader.GetString(9).Trim().ToString(), reader.GetString(10).Trim().ToString(),
reader.GetString(11).Trim().ToString(), reader.GetString(12).Trim().ToString(), reader.GetString(13).Trim().ToString(), reader.GetBoolean(14)));
}
reader.Close();
}
return(rtnList);
}
/// <summary>
/// 无筛选状态下，根据图书类型查询资料
/// </summary>
/// <param name="checkedBookTypeList"></param>
/// <returns></returns>
public List <BookInfo_View> SelectBookInfo(List <string> checkedBookTypeList)
{
List <BookInfo_View> rtnList = new List <BookInfo_View>();
foreach (string bookTypeName in checkedBookTypeList)
{
string sqlStr = @"SELECT [BookGuid],[BookISBN],[BookRawName],[BookCNName],"
+ "[BookAuthor],[BookPress],[BookPubDate],[BookNumber],[BookBuyDate],"
+ "[BookBuyPerson],[BookBuyShop],[BookTypeName],[BookLanType],[BookAnnotation],"
+ "[BookIsDelete] FROM [db_RMS].[dbo].[View_BookInfo] where BookTypeName='" + bookTypeName + "'";
SqlDataReader reader = DBhelp.Create().ExecuteReader(sqlStr);
while (reader.Read())
{
rtnList.Add(new BookInfo_View(reader.GetGuid(0).ToString(), reader.GetString(1).Trim().ToString(), reader.GetString(2).Trim().ToString(),
reader.GetString(3).ToString(), reader.GetString(4).Trim().ToString(), reader.GetString(5).Trim().ToString(), reader.GetDateTime(6),
reader.GetInt32(7), reader.GetDateTime(8), reader.GetString(9).Trim().ToString(), reader.GetString(10).Trim().ToString(),
reader.GetString(11).Trim().ToString(), reader.GetString(12).Trim().ToString(), reader.GetString(13).Trim().ToString(), reader.GetBoolean(14)));
}
reader.Close();
}
return(rtnList);
}
//借书
public int BorrowBook(BorrowReturn b)
{
string sql = "proc_BorrowBook";
SqlParameter[] sp =
{
new SqlParameter("@BorrowId", DbType.Int32),
new SqlParameter("@BookId", b.BookId),
new SqlParameter("@ReaderId", b.ReaderId),
new SqlParameter("@BorrowTime", b.BorrowTime),
new SqlParameter("@ReturnTime", b.ReturnTime),
new SqlParameter("@Fine", b.Fine),
new SqlParameter("@RenewCount", b.RenewCount),
new SqlParameter("@BorrowRemark", b.BorrowRemark),
new SqlParameter("@ReturnValue", DbType.Int32)
};
sp[0].Direction = ParameterDirection.Output;
sp[sp.Length - 1].Direction = ParameterDirection.ReturnValue;
DBhelp.Create().ExecuteNonQuery(sql, CommandType.StoredProcedure, sp);
b.BorrowId = (int)sp[0].Value;
return((int)sp[sp.Length - 1].Value);
}
/// <summary>
/// 添加图书信息
/// </summary>
/// <param name="bookInfo"></param>
/// <returns></returns>
public int AddBookInfo(BookInfo_Table bookInfo)
{
string sql = @"INSERT INTO tb_BookInfo ([BookGuid],[BookISBN],[BookRawName],[BookCNName],[BookAuthor],[BookPress],[BookPubDate],"
+ "[BookNumber],[BookBuyDate],[BookBuyPerson],[BookBuyShop],[BookTypeGuid],[BookLanType],[BookAnnotation],[BookIsDelete]) "
+ "VALUES ('"
+ bookInfo.BookGuid + "','"
+ bookInfo.BookISBN + "','"
+ bookInfo.BookRawName + "','"
+ bookInfo.BookCNName + "','"
+ bookInfo.BookAuthor + "','"
+ bookInfo.BookPress + "','"
+ bookInfo.BookPubDate + "','"
+ bookInfo.BookNumber + "','"
+ bookInfo.BookBuyDate + "','"
+ bookInfo.BookBuyPerson + "','"
+ bookInfo.BookBuyShop + "','"
+ bookInfo.BookTypeGuid + "','"
+ bookInfo.BookLanType + "','"
+ bookInfo.BookAnnotation + "','"
+ bookInfo.BookIsDelete + "')";
return(DBhelp.Create().ExecuteNonQuery(sql));
}
/// <summary>
/// 修改房间
/// </summary>
/// <param name="matUnit"></param>
/// <returns></returns>
public int UpdateRoom(Room room)
{
string sqlStr = "update tb_Room set RoomName='" + room.RoomName + "' where RoomID='" + room.RoomID + "'";
return(DBhelp.Create().ExecuteNonQuery(sqlStr));
}
public DataSet selectClass1()
{
string sql = "select * from Class";
return(DBhelp.Create().ExecuteAdater(sql));
}
/// <summary>
/// 修改材料类型
/// </summary>
/// <param name="matUnit"></param>
/// <returns></returns>
public int UpdateMatUnit(MatUnit matUnit)
{
string sqlStr = "update tb_MatType set MatUnitName='" + matUnit.MatUnitName + "' where MatUnitID='" + matUnit.MatUnitID + "'";
return(DBhelp.Create().ExecuteNonQuery(sqlStr));
}
/// <summary>
/// 查询是否存在该资料类型
/// </summary>
/// <param name="matTypeName"></param>
/// <returns></returns>
public int CountMatUnit(string matUnitName)
{
string sqlStr = "select count(*) from tb_MatUnit where MatUnitName='" + matUnitName + "'";
return(DBhelp.Create().ExecuteScalar(sqlStr));
}
/// <summary>
/// 添加计量单位
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public int AddEquipType(string equipTypeName)
{
string sqlStr = "insert into tb_EquipType(EquipTypeName) values ('" + equipTypeName + "')";
return(DBhelp.Create().ExecuteNonQuery(sqlStr));
}
/// <summary>
/// 查询是否存在该人员编号
/// </summary>
/// <param name="personID"></param>
/// <returns></returns>
public int CountPersonID(string personID)
{
string sqlStr = "select count(*) from tb_PersonInfo where PersonID='" + personID + "'";
return(DBhelp.Create().ExecuteScalar(sqlStr));
}
/// <summary>
/// 查询BookInfo表，是否存在bookISBN
/// </summary>
/// <param name="matID"></param>
/// <returns></returns>
public int IsBookISBNHaving(string bookISBN)
{
string sql = "select count(*) from tb_BookInfo where BookISBN='" + bookISBN + "'";
return(DBhelp.Create().ExecuteNonQuery(sql));
}
/// <summary>
/// 添加房间
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public int AddRoom(string roomName)
{
string sqlStr = "insert into tb_Room(RoomName) values ('" + roomName + "')";
return(DBhelp.Create().ExecuteNonQuery(sqlStr));
}
//查询全部的读者类型
public DataSet selectReaderType1()
{
string sql = @"select * from ReaderType";
return(DBhelp.Create().ExecuteAdater(sql));
}
/// <summary>
/// 删除人员类型
/// </summary>
/// <param name="personTypeGuid"></param>
/// <returns></returns>
public int deletePersonType(string personTypeGuid)
{
string sql = "delete from tb_PersonType where PersonTypeGuid='" + personTypeGuid + "'";
return(DBhelp.Create().ExecuteNonQuery(sql));
}
/// <summary>
/// 查询是否存在该资料类型
/// </summary>
/// <param name="matTypeName"></param>
/// <returns></returns>
public int CountEquipType(string equipTypeName)
{
string sqlStr = "select count(*) from tb_EquipType where EquipTypeName='" + equipTypeName + "'";
return(DBhelp.Create().ExecuteScalar(sqlStr));
}
/// <summary>
/// 修改材料类型
/// </summary>
/// <param name="equipType"></param>
/// <returns></returns>
public int UpdateEquipType(EquipType equipType)
{
string sqlStr = "update tb_EquipType set EquipTypeName='" + equipType.EquipTypeName + "' where EquipTypeID='" + equipType.EquipTypeID + "'";
return(DBhelp.Create().ExecuteNonQuery(sqlStr));
}
/// <summary>
/// 删除资料类型
/// </summary>
/// <param name="matUnitID"></param>
/// <returns></returns>
public int DeleteEquipType(int equipTypeID)
{
string sqlStr = "delete from tb_EquipType where EquipTypeID='" + equipTypeID + "'";
return(DBhelp.Create().ExecuteNonQuery(sqlStr));
}
/// <summary>
/// 删除房间
/// </summary>
/// <param name="matUnitID"></param>
/// <returns></returns>
public int DeleteRoom(int roomID)
{
string sqlStr = "delete from tb_Room where RoomID='" + roomID + "'";
return(DBhelp.Create().ExecuteNonQuery(sqlStr));
}
/// <summary>
/// 添加图书类型
/// </summary>
/// <param name="bookType"></param>
/// <returns></returns>
public int AddBookType(BookType bookType)
{
string sqlStr = "insert into tb_BookType([BookTypeGuid],[BookTypeName]) values ('" + bookType.BookTypeGuid + "','" + bookType.BookTypeName.ToString() + "')";
return(DBhelp.Create().ExecuteNonQuery(sqlStr));
}
/// <summary>
/// 查询构建视图模型，查询图书信息
/// 用于显示dgv的信息
/// </summary>
/// <returns></returns>
public DataSet SelectBookInfo()
{
string sqlStr = @"SELECT * FROM [db_RMS].[dbo].[View_BookInfo]";
return(DBhelp.Create().ExecuteAdater(sqlStr));
}
/// <summary>
/// 修改材料类型
/// </summary>
/// <param name="bookType"></param>
/// <returns></returns>
public int UpdateBookType(BookType bookType)
{
string sqlStr = "update tb_BookType set BookTypeName='" + bookType.BookTypeName + "' where BookTypeGuid='" + bookType.BookTypeGuid + "'";
return(DBhelp.Create().ExecuteNonQuery(sqlStr));
}
/// <summary>
/// 修改人员类型
/// </summary>
/// <param name="persontype"></param>
/// <returns></returns>
public int UpdatePersonType(UserType persontype)
{
string sqlStr = "update tb_PersonType set MatTypeName='" + persontype.UserTypeName + "' where PersonTypeGuid='" + persontype.UserTypeGuid + "'";
return(DBhelp.Create().ExecuteNonQuery(sqlStr));
}
/// <summary>
/// 查询是否存在该房间
/// </summary>
/// <param name="matTypeName"></param>
/// <returns></returns>
public int CountRoom(string roomName)
{
string sqlStr = "select count(*) from tb_Room where RoomName='" + roomName + "'";
return(DBhelp.Create().ExecuteScalar(sqlStr));
}
/// <summary>
/// 查询是否存在该人员类型
/// </summary>
/// <param name="personTypeName"></param>
/// <returns></returns>
public int CountPersonType(string personTypeName)
{
string sqlStr = "select count(*) from tb_PersonType where PersonTypeName='" + personTypeName + "'";
return(DBhelp.Create().ExecuteScalar(sqlStr));
}
/// <summary>
/// 添加计量单位
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public int AddMatUnit(string matUnitName)
{
string sqlStr = "insert into tb_MatUnit(MatUnitName) values ('" + matUnitName + "')";
return(DBhelp.Create().ExecuteNonQuery(sqlStr));
}
/// <summary>
/// 删除图书类型
/// 此为软删除
/// </summary>
/// <param name="bookTypeGuid"></param>
/// <returns></returns>
public int DeleteBookType(string bookTypeGuid)
{
string sqlStr = "delete from tb_BookType where BookTypeGuid='" + bookTypeGuid + "'";
return(DBhelp.Create().ExecuteNonQuery(sqlStr));
}
/// <summary>
/// 删除资料类型
/// </summary>
/// <param name="matUnitID"></param>
/// <returns></returns>
public int DeleteMatUnit(int matUnitID)
{
string sqlStr = "delete from tb_MatUnit where MatUnitID='" + matUnitID + "'";
return(DBhelp.Create().ExecuteNonQuery(sqlStr));
}
/// <summary>
/// 查询是否存在该资料类型
/// </summary>
/// <param name="bookTypeName"></param>
/// <returns></returns>
public int CountBookType(string bookTypeName)
{
string sqlStr = "select count(*) from tb_BookType where BookTypeName='" + bookTypeName + "'";
return(DBhelp.Create().ExecuteScalar(sqlStr));
}
public DataSet selectAdmin()
{
string sql = @"select * from Admin";
return(DBhelp.Create().ExecuteAdater(sql));
}