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);
}
}
}