//修改管理员
public int ExitAdmin(User a)
{
string sql = "update Admin set LoginId=@LoginId,LoginPwd=@LoginPwd,LoginType=@LoginType,LoginRemark=@LoginRemark where LoginId=@LoginId";
SqlParameter[] sp =
{
new SqlParameter("@LoginId", a.LoginId),
new SqlParameter("@LoginPwd", a.LoginPwd),
new SqlParameter("@LoginType", a.LoginType),
new SqlParameter("@LoginRemark", a.LoginRemark),
};
return(DBhelp.Create().ExecuteNonQuery(sql, sp: sp));
}
/// <summary>
/// 获取房间
/// </summary>
/// <returns></returns>
public List <Room> SelectRoom()
{
string sqlStr = "select RoomID,RoomName from tb_Room";
List <Room> rtnList = new List <Room>();
SqlDataReader reader = DBhelp.Create().ExecuteReader(sqlStr);
while (reader.Read())
{
rtnList.Add(new Room(reader.GetInt32(0), reader.GetString(1)));
}
reader.Close();
return(rtnList);
}
/// <summary>
/// 无条件查询读者信息
/// </summary>
/// <returns></returns>
public List <PersonInfo> SelectPersonName()
{
string sql = @"select PersonID,PersonName from tb_PersonInfo";
List <PersonInfo> list = new List <PersonInfo>();
SqlDataReader reader = DBhelp.Create().ExecuteReader(sql);
while (reader.Read())
{
list.Add(new PersonInfo(reader.GetGuid(0).ToString(), reader.GetInt32(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5)));
}
reader.Close();
return(list);
}
/// <summary>
/// 查询部门信息
/// </summary>
/// <returns></returns>
public List <DeptInfo> SelectDeptInfo()
{
string sql = "select * from tb_DeptInfo";
List <DeptInfo> rtnList = new List <DeptInfo>();
SqlDataReader reader = DBhelp.Create().ExecuteReader(sql);
while (reader.Read())
{
rtnList.Add(new DeptInfo(reader.GetString(0), reader.GetString(1)));
}
reader.Close();
return(rtnList);
}
/// <summary>
/// 查询资料计量单位
/// </summary>
/// <returns></returns>
public List <MatUnit> SelectMatUnit()
{
string sql = "select * from tb_MatUnit order by MatUnitID desc";
List <MatUnit> list = new List <MatUnit>();
SqlDataReader reader = DBhelp.Create().ExecuteReader(sql);
while (reader.Read())
{
MatUnit tempMatType = new MatUnit(reader.GetInt32(0), reader.GetString(1));
list.Add(tempMatType);
}
reader.Close();
return(list);
}
/// <summary>
/// 查询资料计量单位
/// </summary>
/// <returns></returns>
public List <EquipType> SelectEquipType()
{
string sql = "select * from tb_EquipType order by EquipTypeID asc";
List <EquipType> list = new List <EquipType>();
SqlDataReader reader = DBhelp.Create().ExecuteReader(sql);
while (reader.Read())
{
EquipType tempEquipType = new EquipType(reader.GetInt32(0), reader.GetString(1));
list.Add(tempEquipType);
}
reader.Close();
return(list);
}
/// <summary>
/// 查询所有的密级信息
/// </summary>
/// <returns></returns>
public List <SecurityInfo> SelectSecurityInfo()
{
List <SecurityInfo> rtnList = new List <SecurityInfo>();
string sqlStr = @"SELECT * FROM [db_RMS].[dbo].[tb_SecurityInfo]";
SqlDataReader reader = DBhelp.Create().ExecuteReader(sqlStr);
while (reader.Read())
{
rtnList.Add(new SecurityInfo(reader.GetString(0), reader.GetString(1)));
}
reader.Close();
return(rtnList);
}
/// <summary>
/// 查询图书类型
/// </summary>
/// <returns></returns>
public List <BookType> SelectBookType()
{
string sql = "select [BookTypeGuid],[BookTypeName] from tb_BookType order by BookTypeName asc";
List <BookType> list = new List <BookType>();
SqlDataReader reader = DBhelp.Create().ExecuteReader(sql);
while (reader.Read())
{
BookType tempBookType = new BookType(reader.GetGuid(0).ToString(), reader.GetString(1));
list.Add(tempBookType);
}
reader.Close();
return(list);
}
//查询借还表信息
public DataSet selectHostory(BorrowReturn b, string radioName, String cboBorrowTimeType, Boolean checkTime)
{
string sql = string.Format(@"select BookInfo.BookId as 'BookId',Reader.ReaderId as 'ReaderId',BookName,ReaderName,
BookTypeName,ReaderTypeName,Gender,IdentityCard,
BorrowTime,ReturnTime,FactReturnTime,Fine,RenewCount
from BorrowReturn
inner join BookInfo on BookInfo.BookId=BorrowReturn.BookId
inner join Reader on Reader.ReaderId=BorrowReturn.ReaderId
inner join BookType on BookType.BookTypeId=BookInfo.BookTypeId
inner join ReaderType on ReaderType.ReaderTypeId=Reader.ReaderTypeId
where BookInfo.BookId like '%{0}%' and
Reader.ReaderId like '%{1}%' ", b.BookId, b.ReaderId);
if (radioName == "全部")
{
}
else if (radioName == "已借")
{
sql += " and FactReturnTime is null ";
}
else if (radioName == "已还")
{
sql += " and FactReturnTime is not null ";
}
if (checkTime)
{
if (cboBorrowTimeType == "日期")
{
sql += string.Format(@" and BorrowId in(
select BorrowId from BorrowReturn where BorrowTime between '{0}' and '{1}' union
select BorrowId from BorrowReturn where ReturnTime between '{0}' and '{1}' union
select BorrowId from BorrowReturn where FactReturnTime between '{0}' and '{1}'
) ", b.TimeIn, b.TimeOut);
}
else if (cboBorrowTimeType == "借书日期")
{
sql += @" and BorrowTime between '" + b.TimeIn + "' and '" + b.TimeOut + "' ";
}
else if (cboBorrowTimeType == "应还书日期")
{
sql += @" and ReturnTime between '" + b.TimeIn + "' and '" + b.TimeOut + "' ";
}
else if (cboBorrowTimeType == "实际还书日期")
{
sql += @" and FactReturnTime between '" + b.TimeIn + "' and '" + b.TimeOut + "' ";
}
}
return(DBhelp.Create().ExecuteAdater(sql));
}
public List <Class> selectClass()
{
string sql = "select * from Class";
List <Class> list = new List <Class>();
SqlDataReader reader = DBhelp.Create().ExecuteReader(sql);
while (reader.Read())
{
Class c = new Class();
c.ClassId = reader.GetInt32(0);
c.ClassName = reader.GetString(1);
list.Add(c);
}
reader.Close();
return(list);
}
public int AddClass(Class c)
{
string sql = "proc_addClass";
SqlParameter[] sp =
{
new SqlParameter("@ClassId", DbType.Int32),
new SqlParameter("@ClassName", c.ClassName),
new SqlParameter("@ReturnValue", DbType.Int32)
};
sp[0].Direction = ParameterDirection.Output;
sp[2].Direction = ParameterDirection.ReturnValue;
DBhelp.Create().ExecuteNonQuery(sql, CommandType.StoredProcedure, sp);
c.ClassId = (int)sp[0].Value;
return((int)sp[2].Value);
}
/// <summary>
///
/// </summary>
/// <param name="matID"></param>
/// <returns></returns>
public BookInfo_View SelectBookInfo_V(string bookGuid)
{
BookInfo_View bookInfo_V = new BookInfo_View();
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 BookGuid='" + bookGuid + "'";
SqlDataReader reader = DBhelp.Create().ExecuteReader(sqlStr);
if (reader.Read())
{
return(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)));
}
return(bookInfo_V);
}
//返回编号，姓名
public List <PersonInfo> selectReaderId(string ReaderId)
{
string sql = "select ReaderId,ReaderName from Reader where ReaderId like '%'+@ReaderId+'%' ";
SqlParameter[] sp =
{
new SqlParameter("@ReaderId", ReaderId)
};
SqlDataReader reader = DBhelp.Create().ExecuteReader(sql, sp: sp);
List <PersonInfo> list = new List <PersonInfo>();
while (reader.Read())
{
PersonInfo r = new PersonInfo(reader.GetGuid(0).ToString(), reader.GetInt32(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5));
list.Add(r);
}
reader.Close();
return(list);
}
/// <summary>
/// 根据资料类型查询资料
/// 并按照配属装备类型筛选
/// </summary>
/// <param name="checkedMatTypeList"></param>
/// <param name="equipTypeName"></param>
/// <returns></returns>
public List <BookInfo_View> SelectMatInfoByEquipType(List <string> checkedMatTypeList, string equipTypeName)
{
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 EquipTypeName='" + equipTypeName + "'";
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>
/// 修改资料信息
/// 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));
}
//借书
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="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);
}
/// <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>
/// 查询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="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="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));
}
public DataSet selectClass1()
{
string sql = "select * from Class";
return(DBhelp.Create().ExecuteAdater(sql));
}
/// <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="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="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>
/// 删除图书类型
/// 此为软删除
/// </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>
/// 查询构建视图模型，查询图书信息
/// 用于显示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="bookTypeName"></param>
/// <returns></returns>
public int CountBookType(string bookTypeName)
{
string sqlStr = "select count(*) from tb_BookType where BookTypeName='" + bookTypeName + "'";
return(DBhelp.Create().ExecuteScalar(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));
}
