public Page<BoxLabel> QueryBoxByPage(BoxLabel query, int pageidx, int pagesize = 20)
{
Page<BoxLabel> page = new Page<BoxLabel>();
page.PageIdx = pageidx;
page.PageSize = pagesize;
string where = GetWhere(query);
string limit = string.Format(" limit {0} , {1}", pageidx < 0 ? 0 : pageidx * pagesize, pagesize);
string orderby = "";//"order by operatetime desc , id desc";
string sql = string.Format("select count(1) from v_box where {0} ", where);
int totalrecord = 0;
object obj = MySqlHelper.GetSingle(sql);
if (obj == null) totalrecord = 0;
int.TryParse(obj.ToString(), out totalrecord);
int totalPages = 0;
totalPages = totalrecord / pagesize;
totalPages += totalrecord % pagesize == 0 ? 0 : 1;
page.TotalPages = totalPages;
page.TotalRecords = totalrecord;
sql = string.Format(" select * from v_box where {0} {1} {2}", where, orderby, limit);
DataSet ds = MySqlHelper.Query(sql);
if (ds == null || ds.Tables.Count < 1 || ds.Tables[0].Rows.Count < 1) return page;
int count = ds.Tables[0].Rows.Count;
List<BoxLabel> list = new List<BoxLabel>();
for (int i = 0; i < count; i++)
{
DataRow row = ds.Tables[0].Rows[i];
BoxLabel model = DataRowToBoxLabel(row);
list.Add(model);
}
page.Data = list;
return page;
}
protected BoxLabel DataRowToBoxLabel(DataRow row)
{
BoxLabel model = new BoxLabel();
if (row["id"].ToString() != "")
{
model.id = int.Parse(row["id"].ToString());
}
model.name = row["name"].ToString();
model.rfid = row["rfid"].ToString();
model.number = row["number"].ToString();
if (row.Table.Columns.Contains("floorrfid"))
{
model.floorrfid = row["floorrfid"].ToString();
}
if (row.Table.Columns.Contains("floorname"))
{
model.floorname = row["floorname"].ToString();
}
return model;
}
public bool EditBoxLabel(BoxLabel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update t_boxlabel set ");
strSql.Append(" name=@name,");
strSql.Append(" rfid=@rfid,");
strSql.Append(" number=@number");
strSql.Append(" where id=@id");
MySqlParameter[] parameters = {
new MySqlParameter("@name", MySqlDbType.VarChar,255),
new MySqlParameter("@rfid", MySqlDbType.VarChar,255),
new MySqlParameter("@number", MySqlDbType.VarChar,100),
new MySqlParameter("@id",MySqlDbType.Int32)
};
parameters[0].Value = model.name;
parameters[1].Value = model.rfid;
parameters[2].Value = model.number;
parameters[3].Value = model.id;
int rows = MySqlHelper.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return true;
}
else
{
return false;
}
}
protected string GetWhere(BoxLabel query)
{
query.name = FilterSpecial(query.name);
query.rfid = FilterSpecial(query.rfid);
query.floorrfid = FilterSpecial(query.floorrfid);
string where = "";
if (string.IsNullOrEmpty(query.name) == false)
{
if (string.IsNullOrEmpty(where) == false) where += " or ";
where += " name like '%" + query.name + "%'";
}
if (string.IsNullOrEmpty(query.rfid) == false)
{
if (string.IsNullOrEmpty(where) == false) where += " or ";
where += string.Format(" rfid like '%{0}%'", query.rfid);
}
if (string.IsNullOrEmpty(query.floorrfid) == false)
{
if (string.IsNullOrEmpty(where) == false) where += " or ";
where += string.Format(" floorrfid = '{0}'", query.floorrfid);
}
if (string.IsNullOrEmpty(where) ) where = " 1=1 ";
return where;
}
public bool AddBoxLabel(BoxLabel model)
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into t_boxlabel(");
strSql.Append(" name,rfid, number)");
strSql.Append(" values (");
strSql.Append("@name,@rfid,@number)");
MySqlParameter[] parameters = {
new MySqlParameter("@name", MySqlDbType.VarChar,255),
new MySqlParameter("@rfid", MySqlDbType.VarChar,255),
new MySqlParameter("@number", MySqlDbType.VarChar,100)
};
parameters[0].Value = model.name;
parameters[1].Value = model.rfid;
parameters[2].Value = model.number;
int result = MySqlHelper.ExecuteSql(strSql.ToString(), parameters);
return result > 0 ? true : false;
}
catch (Exception ex)
{
return false;
}
}
