/// <summary>
/// 通过编号找到库位.
/// </summary>
/// <param name="nnum">库位编号.</param>
public Model.Position getPositionByNum(string nnum)
{
Model.Position posi = null;
string sqltext = "select * from position where positionNum=@positionNum";
List <SqlParameter> para = new List <SqlParameter>();
SqlParameter sqlpara1 = new SqlParameter("@positionNum", nnum);
para.Add(sqlpara1);
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, para);
while (sdr.Read())
{
posi = new Model.Position();
posi.PositionNum = sdr["positionNum"].ToString();
posi.RoomNum = sdr["roomNum"].ToString();
posi.ChestNum = sdr["chestNum"].ToString();
posi.PositiontypeId = sdr["positionTypeId"].ToString();
posi.GoodsTypes = sdr["goodsTypes"].ToString();
posi.Remark = sdr["remark"].ToString();
posi.CreateTime = DateTime.Parse(sdr["createTime"].ToString());
posi.UpdateTime = DateTime.Parse(sdr["updateTime"].ToString());
posi.M = sdr["M"].ToString();
posi.Height = sdr["Height"].ToString();
}
sdr.Close();
DBTools.DBClose();
return(posi);
}
/// <summary>
/// 获取所有的库位.
/// </summary>
/// <returns>库位对象泛型集合.</returns>
public List <Model.Position> getAllPositions()
{
List <Model.Position> posi = new List <Model.Position>();
string sqltext = "select * from position";
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, new List <SqlParameter> ());
while (sdr.Read())
{
Model.Position p = new Model.Position();
p.PositionNum = sdr["positionNum"].ToString();
p.RoomNum = sdr["roomNum"].ToString();
p.ChestNum = sdr["chestNum"].ToString();
p.PositiontypeId = sdr["positionTypeId"].ToString();
p.GoodsTypes = sdr["goodsTypes"].ToString();
p.Remark = sdr["remark"].ToString();
p.CreateTime = DateTime.Parse(sdr["createTime"].ToString());
p.UpdateTime = DateTime.Parse(sdr["updateTime"].ToString());
p.M = sdr["M"].ToString();
p.Height = sdr["Height"].ToString();
p.Rest = sdr["Rest"].ToString();
posi.Add(p);
}
sdr.Close();
DBTools.DBClose();
return(posi);
}
/// <summary>
/// 通过编号,获取某个供货商.
/// </summary>
/// <param name="nname">供货商编号.</param>
/// <returns>供货商对象.</returns>
public Model.Provider getProviderByNum(string nnum)
{
Model.Provider pro = null;
string sqltext = "select * from provider where providerNum=@providerNum";
List <SqlParameter> para = new List <SqlParameter>();
SqlParameter sqlpara1 = new SqlParameter("@providerNum", nnum);
para.Add(sqlpara1);
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, para);
while (sdr.Read())
{
pro = new Model.Provider();
pro.ProviderNum = sdr["providerNum"].ToString();
pro.ProviderName = sdr["providerName"].ToString();
pro.Leader = sdr["staffName"].ToString();
pro.Contact = sdr["contact"].ToString();
pro.ContactNumber = sdr["contactNumber"].ToString();
pro.ProviderAddress = sdr["providerAddress"].ToString();
pro.CreateTime = DateTime.Parse(sdr["createTime"].ToString());
pro.UpdateTime = DateTime.Parse(sdr["updateTime"].ToString());
}
sdr.Close();
DBTools.DBClose();
return(pro);
}
/// <summary>
/// 通过管理员ID，获取某个数据备份。
/// </summary>
/// <param name="userid">管理员ID</param>
/// <returns>数据备份对象。</returns>
public Model.DataCopy getCopyByUserId(string userid)
{
Model.DataCopy data = null;
string sqltext = "select * from datacopy where userid=@userid";
List <SqlParameter> para = new List <SqlParameter>();
SqlParameter sqlpara1 = new SqlParameter("@userid", userid);
para.Add(sqlpara1);
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, para);
while (sdr.Read())
{
data = new Model.DataCopy();
data.CopyId = sdr["copyId"].ToString();
data.DataName = sdr["dataName"].ToString();
data.CopyTime = DateTime.Parse(sdr["copyTime"].ToString());
data.CopyType = sdr["copyType"].ToString();
data.CopySize = int.Parse(sdr["copySize"].ToString());
data.SysUser = new DAL.SysUserDAO().getUserById(sdr["userId"].ToString());
data.CopyLocation = sdr["copyLocation"].ToString();
data.CopyState = sdr["copyState"].ToString();
}
sdr.Close();
DBTools.DBClose();
return(data);
}
/// <summary>
/// 获取所有物品。
/// </summary>
public List <Model.Goods> getAllGoods()
{
List <Model.Goods> good = new List <Model.Goods>();
string sqltext = "select * from goods";
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, new List <SqlParameter> ());
while (sdr.Read())
{
Model.Goods g = new Model.Goods();
g.GoodsNum = sdr["goodsNum"].ToString();
g.GoodsTypeNum = sdr["goodsTypeNum"].ToString();
g.GoodsName = sdr["goodsName"].ToString();
g.GoodsStyle = sdr["goodsStyle"].ToString();
g.GoodsColor = sdr["goodsColor"].ToString();
g.GoodsSmell = sdr["goodsSmell"].ToString();
g.GoodsShape = sdr["goodsShape"].ToString();
g.GoodsPer = sdr["per"].ToString();
g.GoodsCondition = sdr["condition"].ToString();
g.GoodsCount = this.GoodsCountByNum(g.GoodsNum);
g.GoodsType = new DAL.GoodsTypeDAO().getGoodsTypeByNum(g.GoodsTypeNum);
good.Add(g);
}
sdr.Close();
DBTools.DBClose();
return(good);
}
/// <summary>
/// 根据编号,获取某个收货商.
/// </summary>
/// <returns>收货商对象</returns>
public Model.Receiver getReceiverByNum(string recenum)
{
Model.Receiver rece = null;
string sqltext = "select * from receiver where receiverNum=@receiverNum";
List <SqlParameter> para = new List <SqlParameter>();
SqlParameter sqlpara1 = new SqlParameter("@receiverNum", recenum);
para.Add(sqlpara1);
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, para);
while (sdr.Read())
{
rece = new Model.Receiver();
rece.ReceiverNum = sdr["receiverNum"].ToString();
rece.ReceiverName = sdr["receiverName"].ToString();
rece.StaffName = sdr["staffName"].ToString();
rece.Contact = sdr["contact"].ToString();
rece.ContactNumber = sdr["contactNumber"].ToString();
rece.ReceiverAddress = sdr["receiverAddress"].ToString();
rece.CreateTime = DateTime.Parse(sdr["createTime"].ToString());
rece.UpdateTime = DateTime.Parse(sdr["updateTime"].ToString());
}
sdr.Close();
DBTools.DBClose();
return(rece);
}
/// <summary>
/// 获取所有的入库信息。
/// </summary>
public List <Model.Inin> getAllIn()
{
List <Model.Inin> inin = new List <Model.Inin>();
string sqltext = "select * from inin";
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, new List <SqlParameter> ());
while (sdr.Read())
{
Model.Inin i = new Model.Inin();
i.InID = sdr["inID"].ToString();
i.PositionNum = sdr["positionNum"].ToString();
i.GoodsNum = sdr["goodsNum"].ToString();
i.InAmount = int.Parse(sdr["inAmount"].ToString());
i.BatchNum = sdr["batchNum"].ToString();
i.Date = DateTime.Parse(sdr["date"].ToString());
i.UserId = sdr["userId"].ToString();
i.Remark = sdr["remark"].ToString();
i.Position = new DAL.PositionDAO().getPositionByNum(i.PositionNum);
i.Goods = new DAL.GoodsDAO().getGoodsByNum(i.GoodsNum);
i.Batch = new DAL.BatchDao().getBatchByNum(i.BatchNum);
i.SysUser = new DAL.SysUserDAO().getUserById(i.UserId);
inin.Add(i);
}
sdr.Close();
DBTools.DBClose();
return(inin);
}
/// <summary>
/// 根据id查找相应的信息
/// </summary>
/// <param name="id">入库id</param>
/// <returns>Inin类对象</returns>
public Model.Inin getInsById(string id)
{
Model.Inin i = null;
string sqltext = "select * from Inin where inID=@inID";
List <SqlParameter> para = new List <SqlParameter>();
SqlParameter sqlpara1 = new SqlParameter("@inID", id);
para.Add(sqlpara1);
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, para);
while (sdr.Read())
{
i = new Model.Inin();
i.InID = sdr["inID"].ToString();
i.PositionNum = sdr["positionNum"].ToString();
i.GoodsNum = sdr["goodsNum"].ToString();
i.InAmount = int.Parse(sdr["inAmount"].ToString());
i.BatchNum = sdr["batchNum"].ToString();
i.Date = DateTime.Parse(sdr["date"].ToString());
i.UserId = sdr["userId"].ToString();
i.Remark = sdr["remark"].ToString();
i.Position = new DAL.PositionDAO().getPositionByNum(i.PositionNum);
i.Goods = new DAL.GoodsDAO().getGoodsByNum(i.GoodsNum);
i.Batch = new DAL.BatchDao().getBatchByNum(i.BatchNum);
i.SysUser = new DAL.SysUserDAO().getUserById(i.UserId);
}
sdr.Close();
DBTools.DBClose();
return(i);
}
/// <summary>
/// 根据Where条件查找相应的信息
/// </summary>
/// <param name="sqlWhere">Where条件字符串集合编号</param>
/// <returns>Inin类型的集合</returns>
public List <Model.Inin> getInsByWhere(List <string> sqlWhere)
{
List <Model.Inin> inin = new List <Model.Inin>();
string sqltext = "select * from Inin,Goods,Goodstype,Position,Provider,Sysuser,Staff,Batch,Chest where position.chestNum=chest.chestNum and Inin.goodsNum=Goods.goodsNum and Goods.goodsTypeNum=Goodstype.goodsTypeNum and Inin.positionNum=Position.positionNum and Inin.batchNum=Batch.batchNum and Batch.proorrecNum=Provider.providerNum and Inin.userId=Sysuser.userId and Sysuser.staffNum=Staff.staffNum";
foreach (string sql in sqlWhere)
{
sqltext += sql;
}
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, new List <SqlParameter>());
while (sdr.Read())
{
Model.Inin i = new Model.Inin();
i.InID = sdr["inID"].ToString();
i.PositionNum = sdr["positionNum"].ToString();
i.GoodsNum = sdr["goodsNum"].ToString();
i.InAmount = int.Parse(sdr["inAmount"].ToString());
i.BatchNum = sdr["batchNum"].ToString();
i.Date = DateTime.Parse(sdr["date"].ToString());
i.UserId = sdr["userId"].ToString();
i.Remark = sdr["remark"].ToString();
i.Position = new DAL.PositionDAO().getPositionByNum(i.PositionNum);
i.Goods = new DAL.GoodsDAO().getGoodsByNum(i.GoodsNum);
i.Batch = new DAL.BatchDao().getBatchByNum(i.BatchNum);
i.SysUser = new DAL.SysUserDAO().getUserById(i.UserId);
inin.Add(i);
}
sdr.Close();
DBTools.DBClose();
return(inin);
}
/// <summary>
/// 根据传进来的供应商编号查找相应的信息
/// </summary>
/// <param name="provideNum">供应商编号</param>
/// <returns>Inin类型的集合</returns>
public List <Model.Inin> getInsByProviderNum(string provideNum)
{
List <Model.Inin> inin = new List <Model.Inin>();
string sqltext = "select inID,Inin.positionNum,Inin.goodsNum,inAmount,Inin.batchNum,[date],userId,Inin.remark from Inin,batch where inin.batchNum=batch.batchNum and batch.proorrecNum='@provideNum'";
List <SqlParameter> para = new List <SqlParameter>();
SqlParameter sqlpara1 = new SqlParameter("@provideNum", provideNum);
para.Add(sqlpara1);
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, para);
while (sdr.Read())
{
Model.Inin i = new Model.Inin();
i.InID = sdr["inID"].ToString();
i.PositionNum = sdr["positionNum"].ToString();
i.GoodsNum = sdr["goodsNum"].ToString();
i.InAmount = int.Parse(sdr["inAmount"].ToString());
i.BatchNum = sdr["batchNum"].ToString();
i.Date = DateTime.Parse(sdr["date"].ToString());
i.UserId = sdr["userId"].ToString();
i.Remark = sdr["remark"].ToString();
i.Position = new DAL.PositionDAO().getPositionByNum(i.PositionNum);
i.Goods = new DAL.GoodsDAO().getGoodsByNum(i.GoodsNum);
i.Batch = new DAL.BatchDao().getBatchByNum(i.BatchNum);
i.SysUser = new DAL.SysUserDAO().getUserById(i.UserId);
inin.Add(i);
}
sdr.Close();
DBTools.DBClose();
return(inin);
}
/// <summary>
/// 根据时间的区间的字符串类型查询一样的Inin类型集合
/// </summary>
/// <param name="starttime">开始时间</param>
/// <param name="endtime">结束时间</param>
/// <returns>Inin类型的集合</returns>
public List <Model.Inin> getInsBetweenDate(string starttime, string endtime)
{
List <Model.Inin> inin = new List <Model.Inin>();
string sqltext = "select * from inin where date between '@date1 0:00:00' and '@date2 23:59:59'";
List <SqlParameter> para = new List <SqlParameter>();
SqlParameter sqlpara1 = new SqlParameter("@date1", starttime);
SqlParameter sqlpara2 = new SqlParameter("@date2", endtime);
para.Add(sqlpara1);
para.Add(sqlpara2);
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, para);
while (sdr.Read())
{
Model.Inin i = new Model.Inin();
i.InID = sdr["inID"].ToString();
i.PositionNum = sdr["positionNum"].ToString();
i.GoodsNum = sdr["goodsNum"].ToString();
i.InAmount = int.Parse(sdr["inAmount"].ToString());
i.BatchNum = sdr["batchNum"].ToString();
i.Date = DateTime.Parse(sdr["date"].ToString());
i.UserId = sdr["userId"].ToString();
i.Remark = sdr["remark"].ToString();
i.Position = new DAL.PositionDAO().getPositionByNum(i.PositionNum);
i.Goods = new DAL.GoodsDAO().getGoodsByNum(i.GoodsNum);
i.Batch = new DAL.BatchDao().getBatchByNum(i.BatchNum);
i.SysUser = new DAL.SysUserDAO().getUserById(i.UserId);
inin.Add(i);
}
sdr.Close();
DBTools.DBClose();
return(inin);
}
/// <summary>
/// 通过种类名称，获取某个名称的物品类别。
/// </summary>
/// <param name="nname">物品种类名称</param>
/// <returns>GoodsType的list集合</returns>
public List <Model.GoodsType> getGoodsTypesByName(string nname)
{
List <Model.GoodsType> goodtype = new List <Model.GoodsType>();
string sqltext = "select * from goodstype where goodsTypeName=@goodsTypeName";
List <SqlParameter> para = new List <SqlParameter>();
SqlParameter sqlpara1 = new SqlParameter("@goodsTypeNum", nname);
para.Add(sqlpara1);
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, para);
while (sdr.Read())
{
Model.GoodsType gt = new Model.GoodsType();
gt.GoodsTypeNum = sdr["goodsTypeNum"].ToString();
gt.GoodsTypeName = sdr["goodsTypeName"].ToString();
gt.ParentTypeNum = sdr["parentTypeNum"].ToString();
gt.GoodsTypeCondition = sdr["goodsTypeCondition"].ToString();
gt.Remark = sdr["remark"].ToString();
gt.CreateTime = DateTime.Parse(sdr["createTime"].ToString());
gt.UpdateTime = DateTime.Parse(sdr["updateTime"].ToString());
goodtype.Add(gt);
}
sdr.Close();
DBTools.DBClose();
return(goodtype);
}
/// <summary>
/// 根据库柜编号，获得某个库柜。
/// </summary>
/// <param name="nnum">库柜编号</param>
public Model.Chest getChestByNum(string nnum)
{
Model.Chest chest = null;
string sqltext = "select * from chest where chestNum=@chestNum";
List <SqlParameter> para = new List <SqlParameter>();
SqlParameter sqlpara1 = new SqlParameter("@chestNum", nnum);
para.Add(sqlpara1);
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, para);
while (sdr.Read())
{
chest = new Model.Chest();
chest.ChestNum = sdr["chestNum"].ToString();
chest.ChestName = sdr["chestName"].ToString();
chest.PositionMax = int.Parse(sdr["positionMax"].ToString());
chest.RoomNum = sdr["roomNum"].ToString();
chest.Remark = sdr["remark"].ToString();
chest.CreateTime = Convert.ToDateTime(sdr["createTime"]);
chest.UpdateTime = Convert.ToDateTime(sdr["updateTime"]);
chest.M = sdr["M"].ToString();
chest.Height = sdr["Height"].ToString();
}
sdr.Close();
DBTools.DBClose();
return(chest);
}
/// <summary>
/// 根据物品编号，获取某种物品
/// </summary>
/// <param name="nnum">物品编号</param>
public Model.Goods getGoodsByNum(string nnum)
{
Model.Goods good = null;
string sqltext = "select * from goods where goodsNum=@goodsNum";
List <SqlParameter> para = new List <SqlParameter>();
SqlParameter sqlpara1 = new SqlParameter("@goodsNum", nnum);
para.Add(sqlpara1);
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, para);
while (sdr.Read())
{
good = new Model.Goods();
good.GoodsNum = sdr["goodsNum"].ToString();
good.GoodsTypeNum = sdr["goodsTypeNum"].ToString();
good.GoodsName = sdr["goodsName"].ToString();
good.GoodsStyle = sdr["goodsStyle"].ToString();
good.GoodsColor = sdr["goodsColor"].ToString();
good.GoodsSmell = sdr["goodsSmell"].ToString();
good.GoodsShape = sdr["goodsShape"].ToString();
good.GoodsPer = sdr["per"].ToString();
good.GoodsCondition = sdr["condition"].ToString();
good.GoodsCount = this.GoodsCountByNum(good.GoodsNum);
good.GoodsType = new DAL.GoodsTypeDAO().getGoodsTypeByNum(good.GoodsTypeNum);
}
sdr.Close();
DBTools.DBClose();
return(good);
}
/// <summary>
/// 根据用户ID查找对应的日志
/// </summary>
/// <param name="userid">用户ID</param>
/// <returns>日志对象泛型集合</returns>
public List <Model.SysLog> getLogsByUserId(string userid)
{
List <Model.SysLog> syss = new List <Model.SysLog>();
string sqltext = "select * from syslog where userId=@userId";
List <SqlParameter> para = new List <SqlParameter>();
SqlParameter sqlpara1 = new SqlParameter("@userId", userid);
para.Add(sqlpara1);
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, para);
while (sdr.Read())
{
Model.SysLog s = new Model.SysLog();
s.LogId = sdr["logId"].ToString();
s.UserId = sdr["userId"].ToString();
s.SysUser = new DAL.SysUserDAO().getUserById(s.UserId);
s.IpAddress = sdr["ipAddress"].ToString();
s.ActionTime = DateTime.Parse(sdr["actionTime"].ToString());
s.Column = sdr["column"].ToString();
s.ActionType = sdr["actionType"].ToString();
syss.Add(s);
}
sdr.Close();
DBTools.DBClose();
return(syss);
}
/// <summary>
/// 获得具有某个员工编号的员工.
/// </summary>
/// <param name="nnum">员工编号.</param>
/// <returns>符合条件的员工类对象泛型集合.</returns>
public Model.staff getStaffByNum(string nnum)
{
Model.staff staff = null;
string sqltext = "select * from staff where staffNum=@staffNum";
List <SqlParameter> para = new List <SqlParameter>();
SqlParameter sqlpara1 = new SqlParameter("@staffNum", nnum);
para.Add(sqlpara1);
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, para);
while (sdr.Read())
{
staff = new Model.staff();
staff.StaffNum = sdr["staffNum"].ToString();
staff.StaffName = sdr["staffName"].ToString();
staff.DepartId = sdr["DepartId"].ToString();
staff.Department = new DAL.DepartmentDAO().getDepartById(staff.DepartId);
staff.Birthday = DateTime.Parse(sdr["birthday"].ToString());
staff.Gender = sdr["gender"].ToString();
staff.Hometown = sdr["hometown"].ToString();
staff.IdCard = sdr["idCard"].ToString();
staff.PhoneNumber = sdr["phoneNumber"].ToString();
staff.EntryTime = DateTime.Parse(sdr["entryTime"].ToString());
}
sdr.Close();
DBTools.DBClose();
return(staff);
}
/// <summary>
/// 根据传进来的供应商编号查找相应的信息
/// </summary>
/// <param name="provideNum">供应商编号</param>
/// <returns>Outout类型的集合</returns>
public List <Model.Outout> getoutsByProviderNum(string provideNum)
{
List <Model.Outout> outout = new List <Model.Outout>();
string sqltext = "select outID,outout.positionNum,outout.goodsNum,outAmount,outout.batchNum,[date],userId,outout.remark from outout,batch where outout.batchNum=batch.batchNum and batch.proorrecNum='@provideNum'";
List <SqlParameter> para = new List <SqlParameter>();
SqlParameter sqlpara1 = new SqlParameter("@provideNum", provideNum);
para.Add(sqlpara1);
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, para);
while (sdr.Read())
{
Model.Outout o = new Model.Outout();
o.OuID = sdr["outID"].ToString();
o.PositionNum = sdr["positionNum"].ToString();
o.GoodsNum = sdr["goodsNum"].ToString();
o.OutAmount = int.Parse(sdr["outAmount"].ToString());
o.BatchNum = sdr["batchNum"].ToString();
o.Date = DateTime.Parse(sdr["date"].ToString());
o.UserId = sdr["userId"].ToString();
o.Remark = sdr["remark"].ToString();
o.Position = new DAL.PositionDAO().getPositionByNum(o.PositionNum);
o.Goods = new DAL.GoodsDAO().getGoodsByNum(o.GoodsNum);
o.Batch = new DAL.BatchDao().getBatchByNum(o.BatchNum);
o.SysUser = new DAL.SysUserDAO().getUserById(o.UserId);
outout.Add(o);
}
sdr.Close();
DBTools.DBClose();
return(outout);
}
/// <summary>
/// 根据ID获取某条公告。
/// </summary>
/// <param name="iid">公告id</param>
public Model.Notice getNoticeById(string iid)
{
Model.Notice notice = null;
string sqltext = "select * from notice where noticeId=@noticeId";
List <SqlParameter> para = new List <SqlParameter>();
SqlParameter sqlpara1 = new SqlParameter("@noticeId", iid);
para.Add(sqlpara1);
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, para);
while (sdr.Read())
{
notice = new Model.Notice();
notice.NoticeId = sdr["noticeId"].ToString();
notice.Subject = sdr["subject"].ToString();
notice.Title = sdr["title"].ToString();
notice.Body = sdr["body"].ToString();
notice.SysUser = new DAL.SysUserDAO().getUserById(sdr["userId"].ToString());
notice.CreateTime = DateTime.Parse(sdr["createTime"].ToString());
notice.UpdateTime = DateTime.Parse(sdr["publishTime"].ToString());
}
sdr.Close();
DBTools.DBClose();
return(notice);
}
/// <summary>
/// 根据Where条件查找相应的信息
/// </summary>
/// <param name="sqlWhere">Where条件字符串集合编号</param>
/// <returns>outout类型的集合</returns>
public List <Model.Outout> getOutsByWhere(List <string> sqlWhere)
{
List <Model.Outout> outout = new List <Model.Outout>();
string sqltext = "select * from Outout,Goods,Goodstype,Position,receiver,Sysuser,Staff,Batch,chest where Position.chestNum=chest.chestNum and Outout.goodsNum=Goods.goodsNum and Goods.goodsTypeNum=Goodstype.goodsTypeNum and Outout.positionNum=Position.positionNum and Outout.batchNum=Batch.batchNum and Batch.proorrecNum=Receiver.receiverNum and Outout.userId=Sysuser.userId and Sysuser.staffNum=Staff.staffNum";
foreach (string sql in sqlWhere)
{
sqltext += sql;
}
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, new List <SqlParameter>());
while (sdr.Read())
{
Model.Outout o = new Model.Outout();
o.OuID = sdr["outID"].ToString();
o.PositionNum = sdr["positionNum"].ToString();
o.GoodsNum = sdr["goodsNum"].ToString();
o.OutAmount = int.Parse(sdr["outAmount"].ToString());
o.BatchNum = sdr["batchNum"].ToString();
o.Date = DateTime.Parse(sdr["date"].ToString());
o.UserId = sdr["userId"].ToString();
o.Remark = sdr["remark"].ToString();
o.Position = new DAL.PositionDAO().getPositionByNum(o.PositionNum);
o.Goods = new DAL.GoodsDAO().getGoodsByNum(o.GoodsNum);
o.Batch = new DAL.BatchDao().getBatchByNum(o.BatchNum);
o.SysUser = new DAL.SysUserDAO().getUserById(o.UserId);
outout.Add(o);
}
sdr.Close();
DBTools.DBClose();
return(outout);
}
/// <summary>
/// 根据时间的区间的字符串类型查询一样的Inin类型集合
/// </summary>
/// <param name="starttime">开始时间</param>
/// <param name="endtime">结束时间</param>
/// <returns>Outout类型的集合</returns>
public List <Model.Outout> getOutsBetweenDate(string starttime, string endtime)
{
List <Model.Outout> outout = new List <Model.Outout>();
string sqltext = "select * from outout where date between '@date1 0:00:00' and '@date2 23:59:59'";
List <SqlParameter> para = new List <SqlParameter>();
SqlParameter sqlpara1 = new SqlParameter("@date1", starttime);
SqlParameter sqlpara2 = new SqlParameter("@date2", endtime);
para.Add(sqlpara1);
para.Add(sqlpara2);
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, para);
while (sdr.Read())
{
Model.Outout o = new Model.Outout();
o.OuID = sdr["outID"].ToString();
o.PositionNum = sdr["positionNum"].ToString();
o.GoodsNum = sdr["goodsNum"].ToString();
o.OutAmount = int.Parse(sdr["outAmount"].ToString());
o.BatchNum = sdr["batchNum"].ToString();
o.Date = DateTime.Parse(sdr["date"].ToString());
o.UserId = sdr["userId"].ToString();
o.Remark = sdr["remark"].ToString();
o.Position = new DAL.PositionDAO().getPositionByNum(o.PositionNum);
o.Goods = new DAL.GoodsDAO().getGoodsByNum(o.GoodsNum);
o.Batch = new DAL.BatchDao().getBatchByNum(o.BatchNum);
o.SysUser = new DAL.SysUserDAO().getUserById(o.UserId);
outout.Add(o);
}
sdr.Close();
DBTools.DBClose();
return(outout);
}
/// <summary>
/// 根据id查找相应的信息
/// </summary>
/// <param name="id">入库id</param>
/// <returns>Outout类对象</returns>
public Model.Outout getOutsById(string id)
{
Model.Outout o = null;
string sqltext = "select * from outout where outID=@ouID";
List <SqlParameter> para = new List <SqlParameter>();
SqlParameter sqlpara1 = new SqlParameter("@ouID", id);
para.Add(sqlpara1);
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, para);
while (sdr.Read())
{
o = new Model.Outout();
o.OuID = sdr["outID"].ToString();
o.PositionNum = sdr["positionNum"].ToString();
o.GoodsNum = sdr["goodsNum"].ToString();
o.OutAmount = int.Parse(sdr["outAmount"].ToString());
o.BatchNum = sdr["batchNum"].ToString();
o.Date = DateTime.Parse(sdr["date"].ToString());
o.UserId = sdr["userId"].ToString();
o.Remark = sdr["remark"].ToString();
o.Position = new DAL.PositionDAO().getPositionByNum(o.PositionNum);
o.Goods = new DAL.GoodsDAO().getGoodsByNum(o.GoodsNum);
o.Batch = new DAL.BatchDao().getBatchByNum(o.BatchNum);
o.SysUser = new DAL.SysUserDAO().getUserById(o.UserId);
}
sdr.Close();
DBTools.DBClose();
return(o);
}
/// <summary>
/// 获取所有的出库信息。
/// </summary>
/// <returns>Outout类型的集合</returns>
public List <Model.Outout> getAllOut()
{
List <Model.Outout> outout = new List <Model.Outout>();
string sqltext = "select * from outout";
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, new List <SqlParameter> ());
while (sdr.Read())
{
Model.Outout o = new Model.Outout();
o.OuID = sdr["outID"].ToString();
o.PositionNum = sdr["positionNum"].ToString();
o.GoodsNum = sdr["goodsNum"].ToString();
o.OutAmount = int.Parse(sdr["outAmount"].ToString());
o.BatchNum = sdr["batchNum"].ToString();
o.Date = DateTime.Parse(sdr["date"].ToString());
o.UserId = sdr["userId"].ToString();
o.Remark = sdr["remark"].ToString();
o.Position = new DAL.PositionDAO().getPositionByNum(o.PositionNum);
o.Goods = new DAL.GoodsDAO().getGoodsByNum(o.GoodsNum);
o.Batch = new DAL.BatchDao().getBatchByNum(o.BatchNum);
o.SysUser = new DAL.SysUserDAO().getUserById(o.UserId);
outout.Add(o);
}
sdr.Close();
DBTools.DBClose();
return(outout);
}
/// <summary>
/// 获取所有的批次对象。
/// </summary>
public List <Model.Batch> getAllBatches()
{
List <Model.Batch> batch = new List <Model.Batch>();
string sqltext = "select * from batch";
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, new List <SqlParameter> ());
while (sdr.Read())
{
Model.Batch b = new Model.Batch();
b.BatchNum = sdr["batchNum"].ToString();
b.OutorinType = sdr["outorinType"].ToString();
b.ProorrecNum = sdr["proorrecNum"].ToString();
if (b.OutorinType == "入库")
{
b.Provider = new DAL.ProviderDAO().getProviderByNum(b.ProorrecNum);
}
else if (b.OutorinType == "出库")
{
b.Receiver = new DAL.ReceiverDAO().getReceiverByNum(b.ProorrecNum);
}
b.Condition = sdr["condition"].ToString();
batch.Add(b);
}
sdr.Close();
DBTools.DBClose();
return(batch);
}
/// <summary>
/// 获得所有的员工对象.
/// </summary>
/// <returns>员工对象泛型集合.</returns>
public List <Model.staff> getStaffs()
{
List <Model.staff> staff = new List <Model.staff>();
string sqltext = "select * from staff";
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, new List <SqlParameter> ());
while (sdr.Read())
{
Model.staff s = new Model.staff();
s.StaffNum = sdr["staffNum"].ToString();
s.StaffName = sdr["staffName"].ToString();
s.DepartId = sdr["DepartId"].ToString();
s.Department = new DAL.DepartmentDAO().getDepartById(s.DepartId);
s.Birthday = DateTime.Parse(sdr["birthday"].ToString());
s.Gender = sdr["gender"].ToString();
s.Hometown = sdr["hometown"].ToString();
s.IdCard = sdr["idCard"].ToString();
s.PhoneNumber = sdr["phoneNumber"].ToString();
s.EntryTime = DateTime.Parse(sdr["entryTime"].ToString());
staff.Add(s);
}
sdr.Close();
DBTools.DBClose();
return(staff);
}
/// <summary>
/// 根据房间编号获得房间.
/// </summary>
/// <param name="nnum">房间编号</param>
/// <returns>房间类对象</returns>
public Model.Room getRoomByNum(string nnum)
{
Model.Room room = null;
string sqltext = "select * from Room where roomNum=@roomNum order by num";
List <SqlParameter> para = new List <SqlParameter>();
SqlParameter sqlpara1 = new SqlParameter("@roomNum", nnum);
para.Add(sqlpara1);
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, para);
while (sdr.Read())
{
room = new Model.Room();
room.Num = sdr["num"].ToString();
room.RoomNum = sdr["roomNum"].ToString();
room.RoomName = sdr["roomName"].ToString();
room.ChestMax = int.Parse(sdr["chestMax"].ToString());
room.Remark = sdr["remark"].ToString();
room.CreateTime = DateTime.Parse(sdr["createTime"].ToString());
room.UpdateTime = DateTime.Parse(sdr["updateTime"].ToString());
room.M = sdr["M"].ToString();
room.Height = sdr["Height"].ToString();
}
sdr.Close();
DBTools.DBClose();
return(room);
}
/// <summary>
/// 查询所有未完成出库信息
/// </summary>
/// <returns>Dataset类型</returns>
public DataSet getOutsWhereFailToOut()
{
string sqltext = "select Outout.outID,Goods.goodsName,Goodstype.goodsTypeName,Position.positionNum,Outout.outAmount,Inin.date as indate,Outout.date as outdate,Staff.staffName from Outout,Position,Goods,Goodstype,Batch,inin,Sysuser,Staff where Outout.positionNum=Position.positionNum and Outout.goodsNum=Goods.goodsNum and Goods.goodsTypeNum=Goodstype.goodsTypeNum and Outout.batchNum=Batch.batchNum and Inin.goodsNum=Goods.goodsNum and Outout.userId=Sysuser.userId and Sysuser.staffNum=Staff.staffNum and (Batch.condition='准备' or Batch.condition='进行中')";
SqlDataAdapter sdr = new SqlDataAdapter(sqltext, DBTools.DBConn());
DataSet ds = new DataSet();
sdr.Fill(ds);
DBTools.DBClose();
return(ds);
}
/// <summary>
/// 查找是否有相同名字的数据包
/// </summary>
/// <param name="dataCopyName"></param>
/// <returns>true为存在，false为不存在</returns>
public bool HaveSameDataCopyName(string dataCopyName)
{
string sqltext = "select * from datacopy where dataName=@dataName";
List <SqlParameter> para = new List <SqlParameter>();
SqlParameter sqlpara1 = new SqlParameter("@dataName", dataCopyName);
para.Add(sqlpara1);
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, para);
while (sdr.Read())
{
sdr.Close();
DBTools.DBClose();
return(true);
}
return(false);
}
/// <summary>
/// 判断是否有相同名称的供货商.
/// </summary>
/// <param name="nname">供货商名称</param>
/// <returns>有,则返回true;没有,则返回false</returns>
public bool hasProviderOfName(string nname)
{
Model.Provider pro = new Model.Provider();
string sqltext = "select * from provider where providerName=@providerName";
List <SqlParameter> para = new List <SqlParameter>();
SqlParameter sqlpara1 = new SqlParameter("@providerName", nname);
para.Add(sqlpara1);
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, para);
while (sdr.Read())
{
sdr.Close();
DBTools.DBClose();
return(true);
}
return(false);
}
/// <summary>
/// 获取所有库存对象。
/// </summary>
public List <Model.Amount> getAllAmounts()
{
List <Model.Amount> amount = new List <Model.Amount>();
string sqltext = "select * from amount";
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, new List <SqlParameter>());
while (sdr.Read())
{
Model.Amount a = new Model.Amount();
a.PositionNum = sdr["positionNum"].ToString();
a.GoodsNum = sdr["goodsNum"].ToString();
a.Amounts = double.Parse(sdr["amount"].ToString());
a.AmountPer = sdr["per"].ToString();
amount.Add(a);
}
sdr.Close();
DBTools.DBClose();
return(amount);
}
/// <summary>
/// 是否已有相同房间编号的房间对象.
/// </summary>
/// <param name="nnum">房间编号</param>
/// <returns>有,则返回True;没有,则返回False.</returns>
public bool hasRoomOfNum(string nnum)
{
Model.Room room = new Model.Room();
string sqltext = "select * from room where roomNum=@roomNum";
List <SqlParameter> para = new List <SqlParameter>();
SqlParameter sqlpara1 = new SqlParameter("@roomNum", nnum);
para.Add(sqlpara1);
SqlDataReader sdr = DBTools.exereaderSQL(sqltext, para);
while (sdr.Read())
{
sdr.Close();
DBTools.DBClose();
return(true);
}
return(false);
}
