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); }