public List <tb_tasklist_model> GetList(DbConn PubConn, string taskid, string keyword, string CStime, string CEtime, int categoryid, int nodeid, int userid, int state, int pagesize, int pageindex, out int count)
{
int _count = 0;
List <tb_tasklist_model> model = new List <tb_tasklist_model>();
DataSet dsList = SqlHelper.Visit <DataSet>(ps =>
{
string sqlwhere = "";
StringBuilder sql = new StringBuilder();
//sql.Append("select ROW_NUMBER() over(order by T.id desc) as rownum,T.*,C.categoryname,N.nodename,U.username from tb_task T ");
sql.Append("select T.id as rownum,T.*,C.categoryname,N.nodename,U.username from tb_task T ");
sql.Append("left join tb_category C on C.id=T.categoryid ");
sql.Append("left join tb_user U on U.id=T.taskcreateuserid ");
sql.Append("left join tb_node N on N.id=T.nodeid where 1=1 ");
if (!string.IsNullOrWhiteSpace(taskid))
{
ps.Add("taskid", taskid);
sqlwhere += " and ( T.id =@taskid )";
}
if (!string.IsNullOrWhiteSpace(keyword))
{
ps.Add("keyword", keyword);
sqlwhere += " and ( T.taskname like '%'+@keyword+'%' or T.taskremark like '%'+@keyword+'%' )";
}
if (categoryid != -1)
{
ps.Add("categoryid", categoryid);
sqlwhere += " and T.categoryid=@categoryid";
}
if (nodeid != -1)
{
ps.Add("nodeid", nodeid);
sqlwhere += " and T.nodeid=@nodeid";
}
if (state != -999)
{
ps.Add("taskstate", state);
sqlwhere += " and T.taskstate=@taskstate";
}
if (userid != -1)
{
ps.Add("taskcreateuserid", userid);
sqlwhere += " and T.taskcreateuserid=@taskcreateuserid";
}
DateTime d = DateTime.Now;
if (DateTime.TryParse(CStime, out d))
{
ps.Add("CStime", Convert.ToDateTime(CStime));
sqlwhere += " and T.taskcreatetime>=@CStime";
}
if (DateTime.TryParse(CEtime, out d))
{
ps.Add("CEtime", Convert.ToDateTime(CEtime));
sqlwhere += " and T.taskcreatetime<=@CEtime";
}
_count = Convert.ToInt32(PubConn.ExecuteScalar("select count(1) from tb_task T where 1=1 " + sqlwhere, ps.ToParameters()));
DataSet ds = new DataSet();
//string sqlSel = "select * from (" + sql + sqlwhere + ") A where rownum between " + ((pageindex - 1) * pagesize + 1) + " and " + pagesize * pageindex;
string sqlSel = "select * from (" + sql + sqlwhere + ") A order by rownum desc LIMIT " + ((pageindex - 1) * pagesize) + " , " + pagesize;
PubConn.SqlToDataSet(ds, sqlSel, ps.ToParameters());
return(ds);
});
foreach (DataRow dr in dsList.Tables[0].Rows)
{
tb_tasklist_model m = CreateModelList(dr);
model.Add(m);
}
count = _count;
return(model);
}
public virtual tb_tasklist_model CreateModelList(DataRow dr)
{
var o = new tb_tasklist_model();
//
if (dr.Table.Columns.Contains("id"))
{
o.id = dr["id"].Toint();
}
//
if (dr.Table.Columns.Contains("taskname"))
{
o.taskname = dr["taskname"].Tostring();
}
//
if (dr.Table.Columns.Contains("categoryid"))
{
o.categoryid = dr["categoryid"].Toint();
}
//
if (dr.Table.Columns.Contains("nodeid"))
{
o.nodeid = dr["nodeid"].Toint();
}
//
if (dr.Table.Columns.Contains("taskcreatetime"))
{
o.taskcreatetime = dr["taskcreatetime"].ToDateTime();
}
//
if (dr.Table.Columns.Contains("taskupdatetime"))
{
o.taskupdatetime = dr["taskupdatetime"].ToDateTime();
}
//
if (dr.Table.Columns.Contains("tasklaststarttime"))
{
o.tasklaststarttime = dr["tasklaststarttime"].ToDateTime();
}
//
if (dr.Table.Columns.Contains("tasklastendtime"))
{
o.tasklastendtime = dr["tasklastendtime"].ToDateTime();
}
//
if (dr.Table.Columns.Contains("tasklasterrortime"))
{
o.tasklasterrortime = dr["tasklasterrortime"].ToDateTime();
}
//
if (dr.Table.Columns.Contains("taskerrorcount"))
{
o.taskerrorcount = dr["taskerrorcount"].Toint();
}
//
if (dr.Table.Columns.Contains("taskruncount"))
{
o.taskruncount = dr["taskruncount"].Toint();
}
//
if (dr.Table.Columns.Contains("taskcreateuserid"))
{
o.taskcreateuserid = dr["taskcreateuserid"].Toint();
}
//
if (dr.Table.Columns.Contains("taskstate"))
{
o.taskstate = dr["taskstate"].ToByte();
}
//
if (dr.Table.Columns.Contains("taskversion"))
{
o.taskversion = dr["taskversion"].Toint();
}
//
if (dr.Table.Columns.Contains("taskappconfigjson"))
{
o.taskappconfigjson = dr["taskappconfigjson"].Tostring();
}
//
if (dr.Table.Columns.Contains("taskcron"))
{
o.taskcron = dr["taskcron"].Tostring();
}
//
if (dr.Table.Columns.Contains("taskmainclassnamespace"))
{
o.taskmainclassnamespace = dr["taskmainclassnamespace"].Tostring();
}
//
if (dr.Table.Columns.Contains("taskremark"))
{
o.taskremark = dr["taskremark"].Tostring();
}
//
if (dr.Table.Columns.Contains("nodename"))
{
o.nodename = dr["nodename"].Tostring();
}
//
if (dr.Table.Columns.Contains("categoryname"))
{
o.categoryname = dr["categoryname"].Tostring();
}
//
if (dr.Table.Columns.Contains("username"))
{
o.username = dr["username"].Tostring();
}
return(o);
}
