using System.Data; using System.Data.SqlClient; using Business.Supply.Entity; using Common; namespace Business.Supply.Vo { public class PurchaseVo { /// /// 参数赋值 /// /// 实体类PurchaseEty /// 参数数组 private SqlParameter[] SetPara(PurchaseEty purchase) { return new[] { new SqlParameter("@id", purchase.Id), new SqlParameter("@name", purchase.Name), new SqlParameter("@price", purchase.Price), new SqlParameter("@time", purchase.Time), new SqlParameter("@remark", purchase.Remark), new SqlParameter("@delflag", purchase.DelFlag), new SqlParameter("@addUserID", purchase.AddUserId), new SqlParameter("@addtime", purchase.AddTime), new SqlParameter("@updateUserID", purchase.UpdateUserId), new SqlParameter("@lastTime", purchase.LastTime), new SqlParameter("@quantity",purchase.Quantity) }; } /// /// 新增purchase记录 /// /// 实体类PurchaseEty /// 成功返回空字符串,失败返回错误信息 public string InsertPurchase(PurchaseEty purchase) { var sql = "insert into purchase(id,name,price,time,remark,delflag,addUserID,addtime,updateUserID,lastTime,quantity) values(@id,@name,@price,@time,@remark,@delflag,@addUserID,@addtime,@updateUserID,@lastTime,@quantity)"; return SqlHelper.ExecSql(sql, SetPara(purchase)); } /// /// 获取全部采购信息 /// /// 查询字段 /// 数据表 public DataTable AllPurchase(string searchText) { var sql = "select p.id,p.name,p.price,p.quantity,p.remark,p.time,p.lastTime,s.username as addusername ,ss.username as updateusername from purchase p left join SysUser s on s.userid=p.addUserID left join SysUser ss on ss.userid=p.updateUserID where p.delflag ='false' "; if (!string.IsNullOrEmpty(searchText)) { //根据查询字段 进行查询 sql += string.Format( "and( p.name like '%{0}%' or p.price like '%{0}%' or p.remark like '%{0}%' or p.quantity like '%{0}%' or s.username like '%{0}%' or ss.username like '%{0}%')", searchText); } sql += " order by p.lastTime DESC"; return SqlHelper.ExecSqlDateTable(sql); } /// /// 更新采购信息 /// /// 实体类PurchaseEty /// 成功返回空字符串,失败返回错误信息 public string UpdatePurchase(PurchaseEty purchase) { var sq1 = "update purchase set name=@name,price=@price,quantity=@quantity,remark=@remark,lastTime=@lastTime,updateUserID=@updateUserID,time=@time where id=@id"; return SqlHelper.ExecSql(sq1, SetPara(purchase)); } /// /// 根据id删除采购信息 /// /// 采购id /// 成功返回空字符串,失败返回错误信息 public string DelPurchase(string id) { var sql = "update purchase set delflag='true' where id in ("+id+")"; return SqlHelper.ExecSql(sql); } } }