protected Archive DataRowToArchive(DataRow row)
{
Archive model = new Archive();
if (row["id"].ToString() != "")
{
model.id = int.Parse(row["id"].ToString());
}
model.idx = row["idx"].ToString();
model.manager = row["manager"].ToString();
model.title = row["title"].ToString();
model.pages = row["pages"].ToString();
model.number = row["number"].ToString();
model.remark = row["remark"].ToString();
model.operateman = row["operateman"].ToString();
model.operatetime = DateTime.Parse(row["operatetime"].ToString());
if (row.Table.Columns.Contains("position"))
{
model.position = row["position"].ToString();
}
if (row.Table.Columns.Contains("floorrfid"))
{
model.floorrfid = row["floorrfid"].ToString();
}
if (row.Table.Columns.Contains("boxrfid"))
{
model.boxrfid = row["boxrfid"].ToString();
}
return model;
}
public Page<Archive> QueryByPage(Archive query, int pageidx, int pagesize = 20)
{
Page<Archive> page = new Page<Archive>();
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_archive 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_archive 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<Archive> list = new List<Archive>();
for (int i = 0; i < count; i++)
{
DataRow row = ds.Tables[0].Rows[i];
Archive model = DataRowToArchive(row);
list.Add( model );
}
page.Data = list;
return page;
}
public List<Archive> Query(Archive query)
{
string where = GetWhere(query);
string orderby = "order by operatetime desc";
string sql = string.Format(" select * from t_archive where {0} {1}", where, orderby);
DataSet ds = MySqlHelper.Query(sql);
if (ds == null || ds.Tables.Count < 1 || ds.Tables[0].Rows.Count < 1) return null;
int count = ds.Tables[0].Rows.Count;
List<Archive> list = new List<Archive>();
for (int i = 0; i < count; i++)
{
DataRow row = ds.Tables[0].Rows[i];
Archive model = DataRowToArchive(row);
list.Add(model);
}
return list;
}
protected string GetWhere(Archive query)
{
query.manager = FilterSpecial(query.manager);
query.title = FilterSpecial(query.title);
query.number = FilterSpecial(query.number);
query.floorrfid = FilterSpecial(query.floorrfid);
query.boxrfid = FilterSpecial(query.boxrfid);
string where = "";
if (string.IsNullOrEmpty(query.manager) == false)
{
if (string.IsNullOrEmpty(where) == false ) where += " or ";
where += " manager like '%" + query.manager + "%'";
}
if (string.IsNullOrEmpty(query.title) == false)
{
if (string.IsNullOrEmpty(where) == false) where += " or ";
where += string.Format(" title like '%{0}%'", query.title);
}
if (string.IsNullOrEmpty(query.number) == false)
{
if (string.IsNullOrEmpty(where) == false) where += " or ";
where += string.Format(" number like '%{0}%' ", query.number);
}
if (string.IsNullOrEmpty(query.floorrfid) == false)
{
if (string.IsNullOrEmpty(where) == false) where += " or ";
where += string.Format(" floorrfid = '{0}' ", query.floorrfid);
}
if (string.IsNullOrEmpty(query.boxrfid) == false)
{
if (string.IsNullOrEmpty(where) == false) where += " or ";
where += string.Format(" boxrfid = '{0}' ", query.boxrfid);
}
if (query.shownoposition)
{
if (string.IsNullOrEmpty(where) == false)
{
where = " boxid = 0 and ( " + where + " )";
}
else
{
where = " boxid=0 ";
}
}
if (string.IsNullOrEmpty(where))
{
where = " 1=1 ";
}
return where;
}
public bool EditArchive(Archive model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update t_archive set ");
strSql.Append("idx=@idx,");
strSql.Append("manager=@manager,");
strSql.Append("title=@title,");
strSql.Append("pages=@pages,");
strSql.Append("number=@number,");
strSql.Append("remark=@remark,");
strSql.Append("operateman=@operateman,");
strSql.Append("operatetime=@operatetime");
strSql.Append(" where id=@id");
MySqlParameter[] parameters = {
new MySqlParameter("@idx", MySqlDbType.VarChar,50),
new MySqlParameter("@manager", MySqlDbType.VarChar,255),
new MySqlParameter("@title", MySqlDbType.VarChar,255),
new MySqlParameter("@pages", MySqlDbType.VarChar,255),
new MySqlParameter("@number", MySqlDbType.VarChar,255),
new MySqlParameter("@remark", MySqlDbType.VarChar,255),
new MySqlParameter("@operateman", MySqlDbType.VarChar,255),
new MySqlParameter("@operatetime", MySqlDbType.Timestamp),
new MySqlParameter("@id",MySqlDbType.Int32)
};
parameters[0].Value = model.idx;
parameters[1].Value = model.manager;
parameters[2].Value = model.title;
parameters[3].Value = model.pages;
parameters[4].Value = model.number;
parameters[5].Value = model.remark;
parameters[6].Value = model.operateman;
parameters[7].Value = model.operatetime;
parameters[8].Value = model.id;
int rows = MySqlHelper.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return true;
}
else
{
return false;
}
}
public bool AddArchive(Archive model)
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into t_archive(");
strSql.Append("idx, manager,title,pages,number,remark,operateman)");
strSql.Append(" values (");
strSql.Append("@idx,@manager,@title,@pages,@number,@remark,@operateman)");
MySqlParameter[] parameters = {
new MySqlParameter("@idx", MySqlDbType.VarChar,45),
new MySqlParameter("@manager", MySqlDbType.VarChar,255),
new MySqlParameter("@title", MySqlDbType.VarChar,255),
new MySqlParameter("@pages", MySqlDbType.VarChar,255),
new MySqlParameter("@number", MySqlDbType.VarChar,100),
new MySqlParameter("@remark", MySqlDbType.VarChar,255),
new MySqlParameter("@operateman", MySqlDbType.VarChar,255)
};
parameters[0].Value = model.idx;
parameters[1].Value = model.manager;
parameters[2].Value = model.title;
parameters[3].Value = model.pages;
parameters[4].Value = model.number;
parameters[5].Value = model.remark;
parameters[6].Value = model.operateman;
int result = MySqlHelper.ExecuteSql(strSql.ToString(), parameters);
return result > 0 ? true : false;
}
catch (Exception ex)
{
return false;
}
}