public int ExistGroupId(RLib.DB.DbConn dbconn, string groupid)
{
string sql = "select count(1) from dispatch where groupid=@groupid and dispatchState<>-1 ;";
int r = dbconn.ExecuteScalar <int>(sql, new { groupid = groupid });
return(r);
}
public int GroupWaitDispatchCount(RLib.DB.DbConn dbconn, string groupid)
{
string sql = "select count(1) from dispatch where groupid=@groupid and dispatchState=0 and expireTime>getdate() ;";
int r = dbconn.ExecuteScalar <int>(sql, new { groupid = groupid });
return(r);
}
public string GetConfig(RLib.DB.DbConn dbconn)
{
string sql = "select [Value] from RuanalCfg where [Key]=@key;";
var val = dbconn.ExecuteScalar <string>(sql, new { key = KEY });
return(val ?? "");
}
public List <Model.TaskRunLog> GetRunLogPage(RLib.DB.DbConn dbconn, int?taskid, int?nodeid, int?resultType,
string runguid, string keywords, DateTime?begintime, DateTime?endtime,
int pno, int pagesize, out int totalcount)
{
string sqltp = "select {0} from taskrunlog with(nolock) {1} {2}";
string fields = "row_number() over(order by runServerTime desc) as rownum,*";
string wherecon = " where 1=1 ";
if (taskid != null && taskid > 0)
{
wherecon += " and taskid=@taskid ";
}
if (nodeid != null && nodeid > 0)
{
wherecon += " and nodeid=@nodeid ";
}
if (resultType != null)
{
wherecon += " and resultType=@resulttype ";
}
if (!string.IsNullOrWhiteSpace(keywords))
{
wherecon += " and (logtext like '%'+@keywords+'%') ";
}
if (!string.IsNullOrWhiteSpace(runguid))
{
wherecon += " and runGuid = @runguid ";
}
if (begintime != null)
{
wherecon += " and runServerTime>=@begintime ";
}
if (endtime != null)
{
wherecon += " and runServerTime<=@endtime ";
}
var paras = new
{
begintime = begintime,
endtime = endtime,
taskid = taskid,
nodeid = nodeid,
resulttype = resultType,
runguid = runguid ?? "",
keywords = keywords ?? "",
startindex = (pno - 1) * pagesize + 1,
pagesize = pagesize
};
string countsql = string.Format(sqltp, "count(1)", wherecon, "");
string querysql = string.Format("select * from ( {0} ) A where A.rownum>=@startindex and A.rownum<@startindex+@pagesize ",
string.Format(sqltp, fields, wherecon, ""));
totalcount = dbconn.ExecuteScalar <int>(countsql, paras);
var models = dbconn.Query <Model.TaskRunLog>(querysql, paras);
return(models);
}
public List <Model.OperationLog> GetLogPage(RLib.DB.DbConn dbconn, int pno, int pagesize, string keywords, string begintime, string endtime, out int totalcount)
{
string sql = @"select * from (select ROW_NUMBER() over(order by id desc ) as rownum, * from OperationLog where
(OperationName like ('%'+@keywords+'%')
or OperationTitle like ('%'+@keywords+'%')
or Module like ('%'+@keywords+'%'))
and Createtime between @begintime and @endtime ) A
where A.rownum> @startindex and A.rownum<=@startindex+@pagesize;";
string countsql = @"select count(1) from OperationLog where
(OperationName like ('%'+@keywords+'%')
or OperationTitle like ('%'+@keywords+'%')
or Module like ('%'+@keywords+'%'))
and Createtime between @begintime and @endtime";
var models = dbconn.Query <Model.OperationLog>(sql, new
{
keywords = keywords ?? "",
begintime = begintime == "" ? DateTime.Now.AddMonths(-3).ToString() : begintime,
endtime = endtime == "" ? DateTime.Now.AddDays(1).ToString() : endtime,
startindex = (pno - 1) * pagesize,
pagesize = pagesize
});
totalcount = dbconn.ExecuteScalar <int>(countsql, new
{
keywords = keywords ?? "",
begintime = begintime == "" ? DateTime.Now.AddMonths(-3).ToString() : begintime,
endtime = endtime == "" ? DateTime.Now.AddDays(1).ToString() : endtime
});
return(models);
}
public bool ExistClientId(RLib.DB.DbConn dbconn, string clientId, int currNodeId)
{
string sql = "select count(1) from dbo.node where [state]=0 and clientid=@clientId and nodeId<>@nodeId;";
int r = dbconn.ExecuteScalar <int>(sql, new { clientId = clientId, nodeId = currNodeId });
return(r > 0);
}
public int HasNewCmd(RLib.DB.DbConn dbconn, int nodeId)
{
string sql = "select count(1) from cmd where nodeid=@nodeid and cmdstate=0 ";
int count = dbconn.ExecuteScalar <int>(sql, new { nodeid = nodeId });
return(count);
}
public static bool ExistTableInDB(RLib.DB.DbConn dbconn, string tbname)
{
string sqlexist = "SELECT COUNT(1) FROM dbo.SysObjects where [type]='U' and name=@tablename";
int r = dbconn.ExecuteScalar <int>(sqlexist, new { tablename = tbname });
return(r > 0);
}
public int DeleteDispatchLog(RLib.DB.DbConn dbconn, DateTime endtime)
{
int timeOut = 2;//mins
string sql = "delete from dispatch where createTime<@endtime ";
int r = dbconn.ExecuteSql(sql, new { endtime = endtime }, timeOut);
var obj = dbconn.ExecuteScalar("select min(dispatchId) from dispatch", null);
if (RLib.Utils.Converter.ObjToInt(obj) > 5000 * 10000)
{
dbconn.ExecuteSql("DBCC CHECKIDENT (dispatch,reseed,1) ", new { });
}
return(r);
}
public int DeleteRunLog(RLib.DB.DbConn dbconn, DateTime endtime)
{
int timeOut = 2;//mins
string sql = "delete from taskRunLog where runServerTime<@endtime ";
int r = dbconn.ExecuteSql(sql, new { endtime = endtime }, timeOut);
var obj = dbconn.ExecuteScalar("select min(logid) from taskRunLog", null);
if (RLib.Utils.Converter.ObjToInt(obj) > 5000 * 10000)
{
dbconn.ExecuteSql("DBCC CHECKIDENT (taskrunlog,reseed,1) ", new { });
}
return(r);
}
public DateTime GetMinLogDate(RLib.DB.DbConn dbconn)
{
string sql = @"select top 1 createTime from dispatch(nolock) order by createTime asc";
var obj = dbconn.ExecuteScalar(sql, null);
if (obj == null)
{
return(DateTime.Now);
}
if (string.IsNullOrEmpty(obj.ToString()))
{
return(DateTime.Now);
}
return(Convert.ToDateTime(obj.ToString()));
}
public Model.TaskWorkLog AddWorkLog(RLib.DB.DbConn dbconn, DateTime date, Model.TaskWorkLog model)
{
string sql = "insert into " + Pub.BuildWTbName(date) + "(taskid,nodeid,dispatchid,logtype,logtext,servertime) values " +
" (@taskid,@nodeid,@dispatchid,@logtype,@logtext,@servertime);select @@identity;";
model.LogId = dbconn.ExecuteScalar <int>(sql, new
{
taskid = model.TaskId,
nodeid = model.NodeId,
dispatchid = model.DispatchId ?? "",
logtype = model.LogType,
logtext = model.LogText ?? "",
servertime = model.ServerTime
});
// dbconn.GetIdentity();
return(model);
}
public DateTime GetMinWorkLogDate(RLib.DB.DbConn dbconn)
{
string sql = @"select top 1 name from sys.tables where type='U' and name like 'taskWorkLog%' order by name asc";
var obj = dbconn.ExecuteScalar(sql, null);
if (obj == null)
{
return(DateTime.Now);
}
if (string.IsNullOrEmpty(obj.ToString()))
{
return(DateTime.Now);
}
var dtstring = obj.ToString().Substring("taskWorkLog".Length);
return(DateTime.Parse("20" + dtstring.Substring(0, 2) + "-" + dtstring.Substring(2, 2) + "-" + dtstring.Substring(4, 2)));
}
public List <Model.Cmd> GetNodeNewCmd(RLib.DB.DbConn dbconn, int nodeid, int topcount, out int totalcount)
{
string sqltp = "select {0} from cmd {1} {2}";
string fields = "row_number() over(order by createTime asc) as rownum,*";
string wherecon = " where cmdstate=0 and nodeid=@nodeid ";
var paras = new
{
nodeid = nodeid,
startindex = 1,
pagesize = topcount
};
string countsql = string.Format(sqltp, "count(1)", wherecon, "");
string querysql = string.Format("select * from ( {0} ) A where A.rownum>=@startindex and A.rownum<@startindex+@pagesize ",
string.Format(sqltp, fields, wherecon, ""));
totalcount = dbconn.ExecuteScalar <int>(countsql, paras);
var models = dbconn.Query <Model.Cmd>(querysql, paras);
return(models);
}
public List <Model.Cmd> GetCmdList(RLib.DB.DbConn dbconn, DateTime?begintime, DateTime?endtime, int?nodeid, int?cmdstate, int pno, int pagesize, out int totalcount)
{
string sqltp = "select {0} from cmd with(nolock) {1} {2}";
string fields = "row_number() over(order by createTime desc) as rownum,*";
string wherecon = " where 1=1 ";
if (nodeid != null && nodeid > 0)
{
wherecon += " and nodeid=@nodeid ";
}
if (cmdstate != null && cmdstate >= 0)
{
wherecon += " and cmdState=@cmdstate ";
}
if (begintime != null)
{
wherecon += " and createtime>=@begintime ";
}
if (endtime != null)
{
wherecon += " and createtime<=@endtime ";
}
var paras = new
{
cmdstate = cmdstate ?? 0,
begintime = begintime,
endtime = endtime,
nodeid = nodeid,
startindex = (pno - 1) * pagesize + 1,
pagesize = pagesize
};
string countsql = string.Format(sqltp, "count(1)", wherecon, "");
string querysql = string.Format("select * from ( {0} ) A where A.rownum>=@startindex and A.rownum<@startindex+@pagesize ",
string.Format(sqltp, fields, wherecon, ""));
totalcount = dbconn.ExecuteScalar <int>(countsql, paras);
var models = dbconn.Query <Model.Cmd>(querysql, paras);
return(models);
}
public List <Model.TaskWorkLog> GetWorkLogPage(RLib.DB.DbConn dbconn,
DateTime date, int?taskid, int?nodeid, int?logtype, bool isorderbyId,
List <string> notContainText,
string runguid, string keywords, DateTime?begintime, DateTime?endtime,
int pno, int pagesize, out int totalcount)
{
List <RLib.DB.ProcedureParameter> paras = new List <RLib.DB.ProcedureParameter>();
paras.Add(new RLib.DB.ProcedureParameter("begintime", begintime == null ? System.DBNull.Value : (object)begintime.Value));
paras.Add(new RLib.DB.ProcedureParameter("endtime", endtime == null ? System.DBNull.Value : (object)endtime.Value));
paras.Add(new RLib.DB.ProcedureParameter("taskid", taskid ?? 0));
paras.Add(new RLib.DB.ProcedureParameter("nodeid", nodeid ?? 0));
paras.Add(new RLib.DB.ProcedureParameter("logtype", logtype ?? 0));
paras.Add(new RLib.DB.ProcedureParameter("runguid", runguid ?? ""));
paras.Add(new RLib.DB.ProcedureParameter("keywords", keywords ?? ""));
paras.Add(new RLib.DB.ProcedureParameter("startindex", (pno - 1) * pagesize + 1));
paras.Add(new RLib.DB.ProcedureParameter("pagesize", pagesize));
string sqltp = "select {0} from " + Pub.BuildWTbName(date) + " with(nolock) {1} {2}";
string fields = "row_number() over(order by " + (isorderbyId ? " logid asc " : " servertime desc,logid desc") + " ) as rownum,*";
string wherecon = " where 1=1 ";
if (taskid != null && taskid > 0)
{
wherecon += " and taskid=@taskid ";
}
if (nodeid != null && nodeid > 0)
{
wherecon += " and nodeid=@nodeid ";
}
if (logtype != null)
{
wherecon += " and logtype=@logtype ";
}
if (!string.IsNullOrWhiteSpace(keywords))
{
wherecon += " and ( logtext like '%'+@keywords+'%' ) ";
}
if (!string.IsNullOrWhiteSpace(runguid))
{
wherecon += " and dispatchId=@runguid ";
}
if (begintime != null)
{
wherecon += " and createtime>=@begintime ";
}
if (endtime != null)
{
wherecon += " and createtime<=@endtime ";
}
if (notContainText != null && notContainText.Count > 0)
{
int notlikeindex = 0;
foreach (var a in notContainText)
{
if (string.IsNullOrWhiteSpace(a))
{
continue;
}
notlikeindex++;
wherecon += " and logText not like '%'+@notlikeindex" + (notlikeindex) + "+'%'";
paras.Add(new RLib.DB.ProcedureParameter("notlikeindex" + notlikeindex, a));
}
}
string countsql = string.Format(sqltp, "count(1)", wherecon, "");
string querysql = string.Format("select * from ( {0} ) A where A.rownum>=@startindex and A.rownum<@startindex+@pagesize ",
string.Format(sqltp, fields, wherecon, ""));
totalcount = dbconn.ExecuteScalar <int>(countsql, paras);
var models = dbconn.Query <Model.TaskWorkLog>(querysql, paras);
return(models);
}
public List <Model.Dispatch> GetDispatchs(RLib.DB.DbConn dbconn, int?taskid, int?nodeid, int?dispatchState,
string keywords, DateTime?begintime, DateTime?endtime, int pno, int pagesize, out int totalcount)
{
string sqltp = "select {0} from dispatch with(nolock) {1} {2}";
string fields = "row_number() over(order by dispatchId desc) as rownum,*";
string wherecon = string.Empty;
if (dispatchState == -1)
{
wherecon = " where 1=1 ";
}
else
{
wherecon = " where dispatchState<>-1 ";
}
if (taskid != null && taskid > 0)
{
wherecon += " and taskid=@taskid ";
}
if (nodeid != null)
{
wherecon += " and nodeid=@nodeid ";
}
if (dispatchState != null)
{
wherecon += " and dispatchState=@dispatchstate ";
}
if (!string.IsNullOrWhiteSpace(keywords))
{
wherecon += " and ( nickname like '%'+@keywords+'%' or " +
" runkey like '%'+@keywords+'%' or " +
"groupId like '%'+@keywords+'%' or invokeId like '%'+@keywords+'%' )";
}
if (begintime != null)
{
wherecon += " and createtime>=@begintime ";
}
if (endtime != null)
{
wherecon += " and createtime<=@endtime ";
}
var paras = new
{
begintime = begintime,
endtime = endtime,
taskid = taskid,
nodeid = nodeid,
dispatchstate = dispatchState,
keywords = keywords ?? "",
startindex = (pno - 1) * pagesize + 1,
pagesize = pagesize
};
string countsql = string.Format(sqltp, "count(1)", wherecon, "");
string querysql = string.Format("select * from ( {0} ) A where A.rownum>=@startindex and A.rownum<@startindex+@pagesize ",
string.Format(sqltp, fields, wherecon, ""));
totalcount = dbconn.ExecuteScalar <int>(countsql, paras);
var models = dbconn.Query <Model.Dispatch>(querysql, paras);
return(models);
}