/// <summary>
/// 增加一条数据
/// </summary>
public int Add(tb_EasyConsume model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into tb_EasyConsume(");
strSql.Append("name,type,unit,amount,product,price,remark,createUser,createDate,updateUser,updateDate,temp1,temp2)");
strSql.Append(" values (");
strSql.Append("@name,@type,@unit,@amount,@product,@price,@remark,@createUser,@createDate,@updateUser,@updateDate,@temp1,@temp2)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@name", SqlDbType.NVarChar, 200),
new SqlParameter("@type", SqlDbType.NVarChar, 50),
new SqlParameter("@unit", SqlDbType.Int, 4),
new SqlParameter("@amount", SqlDbType.Int, 4),
new SqlParameter("@product", SqlDbType.NVarChar, -1),
new SqlParameter("@price", SqlDbType.Decimal, 9),
new SqlParameter("@remark", SqlDbType.NVarChar, -1),
new SqlParameter("@createUser", SqlDbType.Int, 4),
new SqlParameter("@createDate", SqlDbType.DateTime),
new SqlParameter("@updateUser", SqlDbType.Int, 4),
new SqlParameter("@updateDate", SqlDbType.DateTime),
new SqlParameter("@temp1", SqlDbType.NVarChar, -1),
new SqlParameter("@temp2", SqlDbType.NVarChar, -1)
};
parameters[0].Value = model.name;
parameters[1].Value = model.type;
parameters[2].Value = model.unit;
parameters[3].Value = model.amount;
parameters[4].Value = model.product;
parameters[5].Value = model.price;
parameters[6].Value = model.remark;
parameters[7].Value = model.createUser;
parameters[8].Value = model.createDate;
parameters[9].Value = model.updateUser;
parameters[10].Value = model.updateDate;
parameters[11].Value = model.temp1;
parameters[12].Value = model.temp2;
object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public tb_EasyConsume GetModel(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 id,name,type,unit,amount,product,price,remark,createUser,createDate,updateUser,updateDate,temp1,temp2 from tb_EasyConsume ");
strSql.Append(" where id=@id");
SqlParameter[] parameters =
{
new SqlParameter("@id", SqlDbType.Int, 4)
};
parameters[0].Value = id;
tb_EasyConsume model = new tb_EasyConsume();
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 获取所有数据列表
/// 作者：章建国
/// </summary>
/// <returns>将DataTable转换为Json数据格式通过string类型返回</returns>
public string GetEasyConsumeOUTList(int pageNumber, int pageSize, int _cid, string _searchtext, string _searchtype, string _datetype, string _txtdate)
{
DataTable dt = new DataTable();
int total = 0;
try
{
string where = " 1 = 1 ";
switch (_searchtype)
{
case "名称":
where += string.Format(" and name like '%%{0}%%' ", _searchtext);
break;
case "厂家与场地":
where += string.Format(" and product like '%%{0}%%' ", _searchtext);
break;
case "经手人":
var listperson = new BLL.PersonnelManage.T_tb_InPersonnel().GetModelList(" PersonnelName like '%%" + _searchtext + "%%'");
string personids = "";
foreach (var item in listperson)
{
personids += item.PersonnelID + ",";
}
if (!string.IsNullOrEmpty(personids))
{
where += string.Format(" and user1 in({0}) ", personids.Substring(0, personids.Length - 1));
}
break;
}
switch (_datetype)
{
case "年":
where += string.Format(" and year(createDate) ={0} ", _txtdate);
break;
case "月":
DateTime _dtime = DateTime.Parse("1949-10-01"); DateTime.TryParse(_txtdate, out _dtime);
where += _dtime.Year > 1949 ? string.Format(" and year(createDate) = {0} and month(createDate) = {1} ", _dtime.Year, _dtime.Month) : "";
break;
}
if (CurrentUserInfo.DataRange == 3)
{
int _userid = CurrentUserInfo.PersonnelID;
where += string.Format(" and CreateUser = (select PersonnelID from tb_InPersonnel where AreaID = {0} and PersonnelID = {1}) ", CurrentUserInfo.AreaID.Value, _userid);
}
else if (_cid > 0 || CurrentUserInfo.DataRange == 2)
{
int _userid = CurrentUserInfo.PersonnelID;
where += string.Format(" and createUser in (select PersonnelID from tb_InPersonnel where AreaID = {0}) ", _cid);
}
dt = _easyconsumeoutbll.GetListByPage(where, "updateDate desc", pageNumber * pageSize - (pageSize - 1), pageNumber * pageSize).Tables[0];
dt.Columns.Add("name");
dt.Columns.Add("type");
dt.Columns.Add("danwei");
dt.Columns.Add("PersonnelName");
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
int eid = Convert.ToInt32(dr["eId"]);
tb_EasyConsume _easyconsume = _easyconsumebll.GetModel(eid);
dt.Rows[i]["name"] = _easyconsume.name;
dt.Rows[i]["type"] = _easyconsume.type;
dt.Rows[i]["danwei"] = new tb_BaseBLL().GetModel(_easyconsume.unit.Value).baseName;
dt.Rows[i]["PersonnelName"] = new BLL.PersonnelManage.T_tb_InPersonnel().GetModel(Convert.ToInt32(dr["user1"])).PersonnelName;
}
total = dt.Rows.Count;
total = _easyconsumeoutbll.GetEasyConsumeOUTCount(where);
}
catch { }
return(PublicClass.ToJson(dt, total));
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public tb_EasyConsume DataRowToModel(DataRow row)
{
tb_EasyConsume model = new tb_EasyConsume();
if (row != null)
{
if (row["id"] != null && row["id"].ToString() != "")
{
model.id = int.Parse(row["id"].ToString());
}
if (row["name"] != null)
{
model.name = row["name"].ToString();
}
if (row["type"] != null)
{
model.type = row["type"].ToString();
}
if (row["unit"] != null && row["unit"].ToString() != "")
{
model.unit = int.Parse(row["unit"].ToString());
}
if (row["amount"] != null && row["amount"].ToString() != "")
{
model.amount = int.Parse(row["amount"].ToString());
}
if (row["product"] != null)
{
model.product = row["product"].ToString();
}
if (row["price"] != null && row["price"].ToString() != "")
{
model.price = decimal.Parse(row["price"].ToString());
}
if (row["remark"] != null)
{
model.remark = row["remark"].ToString();
}
if (row["createUser"] != null && row["createUser"].ToString() != "")
{
model.createUser = int.Parse(row["createUser"].ToString());
}
if (row["createDate"] != null && row["createDate"].ToString() != "")
{
model.createDate = DateTime.Parse(row["createDate"].ToString());
}
if (row["updateUser"] != null && row["updateUser"].ToString() != "")
{
model.updateUser = int.Parse(row["updateUser"].ToString());
}
if (row["updateDate"] != null && row["updateDate"].ToString() != "")
{
model.updateDate = DateTime.Parse(row["updateDate"].ToString());
}
if (row["temp1"] != null)
{
model.temp1 = row["temp1"].ToString();
}
if (row["temp2"] != null)
{
model.temp2 = row["temp2"].ToString();
}
}
return(model);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(tb_EasyConsume model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update tb_EasyConsume set ");
strSql.Append("name=@name,");
strSql.Append("type=@type,");
strSql.Append("unit=@unit,");
strSql.Append("amount=@amount,");
strSql.Append("product=@product,");
strSql.Append("price=@price,");
strSql.Append("remark=@remark,");
strSql.Append("createUser=@createUser,");
strSql.Append("createDate=@createDate,");
strSql.Append("updateUser=@updateUser,");
strSql.Append("updateDate=@updateDate,");
strSql.Append("temp1=@temp1,");
strSql.Append("temp2=@temp2");
strSql.Append(" where id=@id");
SqlParameter[] parameters =
{
new SqlParameter("@name", SqlDbType.NVarChar, 200),
new SqlParameter("@type", SqlDbType.NVarChar, 50),
new SqlParameter("@unit", SqlDbType.Int, 4),
new SqlParameter("@amount", SqlDbType.Int, 4),
new SqlParameter("@product", SqlDbType.NVarChar, -1),
new SqlParameter("@price", SqlDbType.Decimal, 9),
new SqlParameter("@remark", SqlDbType.NVarChar, -1),
new SqlParameter("@createUser", SqlDbType.Int, 4),
new SqlParameter("@createDate", SqlDbType.DateTime),
new SqlParameter("@updateUser", SqlDbType.Int, 4),
new SqlParameter("@updateDate", SqlDbType.DateTime),
new SqlParameter("@temp1", SqlDbType.NVarChar, -1),
new SqlParameter("@temp2", SqlDbType.NVarChar, -1),
new SqlParameter("@id", SqlDbType.Int, 4)
};
parameters[0].Value = model.name;
parameters[1].Value = model.type;
parameters[2].Value = model.unit;
parameters[3].Value = model.amount;
parameters[4].Value = model.product;
parameters[5].Value = model.price;
parameters[6].Value = model.remark;
parameters[7].Value = model.createUser;
parameters[8].Value = model.createDate;
parameters[9].Value = model.updateUser;
parameters[10].Value = model.updateDate;
parameters[11].Value = model.temp1;
parameters[12].Value = model.temp2;
parameters[13].Value = model.id;
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}