/// <summary>
/// nodeList
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <returns></returns>
public IList<tb_datanode_model> GetPageList(DbConn conn, int pageIndex, int pageSize, ref int count)
{
int tempCount = 0;
IList<tb_datanode_model> list = new List<tb_datanode_model>();
var result = SqlHelper.Visit((ps) =>
{
string sql = "SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS rownum,* FROM tb_datanode WITH(NOLOCK)";
string countSql = "SELECT COUNT(1) FROM tb_datanode WITH(NOLOCK) ";
object obj = conn.ExecuteScalar(countSql, null);
if (obj != DBNull.Value && obj != null)
{
tempCount = LibConvert.ObjToInt(obj);
}
string sqlPage = string.Concat("SELECT * FROM (", sql.ToString(), ") A WHERE rownum BETWEEN ", ((pageIndex - 1) * pageSize + 1), " AND ", pageSize * pageIndex);
DataTable dt = conn.SqlToDataTable(sqlPage, null);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
tb_datanode_model model = CreateModel(dr);
list.Add(model);
}
}
return list;
});
count = tempCount;
return result;
}
public List <DbModels.certcenterlog> GetPage(XXF.Db.DbConn PubConn, int pno, int pagesize, string keywords, out int totalcount)
{
string basesql = "select {0} from certcenterlog ";
string querysql = string.Format(basesql, " ROW_NUMBER() over (order by reqtime desc ) as rownum,id,url,reqdata,userid,username,reqtime,ip,opecontent");
XXF.Db.SimpleProcedureParameter para = new SimpleProcedureParameter();
string wherecon = "";
if (!string.IsNullOrEmpty(keywords))
{
wherecon = " where userid like '%' +@keywords+'%' or username like '%' +@keywords+'%' or url like '%' +@keywords+'%' or ip like '%' +@keywords+'%' ";
para.Add("@keywords", keywords);
}
DataSet ds = new DataSet();
querysql = string.Concat("select A.* from (", querysql, wherecon, ") A", " where A.rownum between ", (pno - 1) * pagesize + 1, " and ", pagesize * pno);
PubConn.SqlToDataSet(ds, querysql, para.ToParameters());
List <DbModels.certcenterlog> listlog = new List <DbModels.certcenterlog>();
foreach (DataRow a in ds.Tables[0].Rows)
{
listlog.Add(CreateModel(a));
}
totalcount = (int)PubConn.ExecuteScalar(string.Format(basesql, " count(*) ") + wherecon, para.ToParameters());
return(listlog);
}
public List <Models.DbModels.tb_token> GetByPage(XXF.Db.DbConn PubConn, int pno, int pagesize, string keywords, DbModels.TokenType tokentype, out int totalcount)
{
List <DbModels.tb_token> tokenlist = new List <DbModels.tb_token>();
string basesql = "select {0} from " + tokentype.ToString() + " ";
string whercon = "";
XXF.Db.SimpleProcedureParameter para = new SimpleProcedureParameter();
string querysql = string.Format(basesql, "ROW_NUMBER() over (order by createtime desc) as rownum, token,userid,id,username,appid,createtime,expires");
if (!string.IsNullOrEmpty(keywords))
{
para.Add("@keywords", keywords);
whercon = " where token=@keywords or userid like '%' + @keywords + '%' or username like '%' + @keywords + '%' or appid like '%' + @keywords + '%' ";
}
querysql = string.Concat("select A.* from (", querysql, whercon, ") A ", " where A.rownum between ", (pno - 1) * pagesize + 1, " and ", pagesize * pno);
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, querysql, para.ToParameters());
foreach (DataRow dr in ds.Tables[0].Rows)
{
tokenlist.Add(CreateModel(dr));
}
totalcount = (int)PubConn.ExecuteScalar(string.Format(basesql, " count(*) ") + whercon, para.ToParameters());
return(tokenlist);
}
/// <summary>
/// debug日志分页列表
/// </summary>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="count"></param>
/// <returns></returns>
public IList<tb_debuglog_model> GetPageList(DbConn conn, DateTime? startTime, DateTime? endTime, string mqpathid, string mqpath, string methodname, string info, int pageSize, int pageIndex, ref int count)
{
int tempCount = 0;
var result = SqlHelper.Visit((ps) =>
{
IList<tb_debuglog_model> list = new List<tb_debuglog_model>();
StringBuilder where = new StringBuilder();
List<ProcedureParameter> parameters = new List<ProcedureParameter>();
where.Append(" WHERE 1=1 ");
if (startTime != null && endTime != null)
{
parameters.Add(new ProcedureParameter("startTime", startTime.Value.ToString("yyyy-MM-dd")));
parameters.Add(new ProcedureParameter("endTime", endTime.Value.ToString("yyyy-MM-dd")));
where.Append(" AND createtime>=@startTime AND createtime<=@endTime ");
}
if (!string.IsNullOrWhiteSpace(mqpathid))
{
parameters.Add(new ProcedureParameter("mqpathid", mqpathid));
where.Append(" AND mqpathid=@mqpathid ");
}
if (!string.IsNullOrWhiteSpace(mqpath))
{
parameters.Add(new ProcedureParameter("mqpath", mqpath));
where.Append(" AND mqpath=@mqpath ");
}
if (!string.IsNullOrWhiteSpace(methodname))
{
parameters.Add(new ProcedureParameter("methodname", methodname));
where.Append(" AND methodname like '%'+@methodname+'%' ");
}
if (!string.IsNullOrWhiteSpace(info))
{
parameters.Add(new ProcedureParameter("info", info));
where.Append(" AND info like '%'+@info+'%' ");
}
StringBuilder sql = new StringBuilder();
sql.Append("SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS rownum,* FROM tb_debuglog WITH(NOLOCK)");
string countSql = string.Concat("SELECT COUNT(1) FROM tb_debuglog WITH(NOLOCK) ", where.ToString());
object obj = conn.ExecuteScalar(countSql, parameters);
if (obj != DBNull.Value && obj != null)
{
tempCount = LibConvert.ObjToInt(obj);
}
string sqlPage = string.Concat("SELECT * FROM (", sql.ToString(), where.ToString(), ") A WHERE rownum BETWEEN ", ((pageIndex - 1) * pageSize + 1), " AND ", pageSize * pageIndex);
DataTable dt = conn.SqlToDataTable(sqlPage, parameters);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
tb_debuglog_model model = CreateModel(dr);
list.Add(model);
}
}
return list;
});
count = tempCount;
return result;
}
public int GetVersion(DbConn PubConn, int taskid)
{
return SqlHelper.Visit(ps =>
{
ps.Add("taskid", taskid);
string sql = "select max(version) version from tb_version where taskid=@taskid";
int version = Convert.ToInt32(PubConn.ExecuteScalar(sql, ps.ToParameters()));
return version;
});
}
public long GetLastMqIdByPartitionId(DbConn conn, int partitionId)
{
return SqlHelper.Visit((ps) =>
{
string sql = string.Format("SELECT lastmqid FROM tb_consumer_partition WITH(NOLOCK) WHERE partitionId={0}", partitionId.Tostring());
object obj = conn.ExecuteScalar(sql, null);
if (obj != DBNull.Value && obj != null)
{
return LibConvert.ObjToInt64(obj);
}
return 0;
});
}
public IList<tb_producterview_model> GetPageList(DbConn conn, string mqpathid, string name, string ip, int pageIndex, int pageSize, ref int count)
{
int tempCount = 0;
IList<tb_producterview_model> list = new List<tb_producterview_model>();
var result = SqlHelper.Visit((ps) =>
{
StringBuilder where = new StringBuilder("");// WHERE 1=1
if (!string.IsNullOrEmpty(name))
{
where.AppendFormat(" AND p.productername LIKE '%{0}%'", name);
}
if (!string.IsNullOrEmpty(ip))
{
where.AppendFormat(" AND p.ip='{0}'", ip);
}
if (!string.IsNullOrWhiteSpace(mqpathid))
{
int temp = 0;
if (int.TryParse(mqpathid, out temp))
{
where.AppendFormat(" and (p.mqpathid='{0}')", mqpathid);
}
else
{
where.AppendFormat(" and (m.mqpath like '%'+'{0}'+'%')", mqpathid);
}
}
string sql = "SELECT ROW_NUMBER() OVER(ORDER BY p.Id DESC) AS rownum,p.*,m.mqpath FROM tb_producter p WITH(NOLOCK),tb_mqpath m WITH(NOLOCK) where p.mqpathid=m.id ";
string countSql = "SELECT COUNT(1) FROM tb_producter p WITH(NOLOCK),tb_mqpath m WITH(NOLOCK) where p.mqpathid=m.id " + where;
object obj = conn.ExecuteScalar(countSql, null);
if (obj != DBNull.Value && obj != null)
{
tempCount = LibConvert.ObjToInt(obj);
}
string sqlPage = string.Concat("SELECT * FROM (", sql.ToString(), where.ToString(), ") A WHERE rownum BETWEEN ", ((pageIndex - 1) * pageSize + 1), " AND ", pageSize * pageIndex);
DataTable dt = conn.SqlToDataTable(sqlPage, null);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
tb_producterview_model model = new tb_producterview_model();
model.ProducterModel = CreateModel(dr);
model.mqpath = Convert.ToString(dr["mqpath"]);
list.Add(model);
}
}
return list;
});
count = tempCount;
return result;
}
public int GetNonProductCount(DbConn conn, int mqPathId, int sec)
{
return SqlHelper.Visit((ps) =>
{
string sql = string.Format("SELECT COUNT(1) FROM tb_producter WITH(NOLOCK) WHERE mqpathid=@mqpathid AND datediff(s,lastheartbeat,getdate())>={0}", sec);
ps.Add("@mqpathid", mqPathId);
object obj = conn.ExecuteScalar(sql, ps.ToParameters());
if (obj != DBNull.Value && obj != null)
{
return LibConvert.ObjToInt(obj);
}
return 0;
});
}
public int GetActiveConsumerCount(DbConn conn, int mqpathId)
{
return SqlHelper.Visit((ps) =>
{
string sql = "SELECT COUNT (p.consumerclientid) FROM tb_consumer_partition p WITH(NOLOCK),tb_consumer c WITH(NOLOCK),tb_mqpath_partition m WITH(NOLOCK) WHERE p.lastconsumertempid=c.tempid and p.partitionid=m.partitionid and m.mqpathid=@mqpathid";
ps.Add("@mqpathid", mqpathId);
object obj = conn.ExecuteScalar(sql, ps.ToParameters());
if (obj != DBNull.Value && obj != null)
{
return LibConvert.ObjToInt(obj);
}
return 0;
});
}
public bool AddReport(DbConn conn, tb_partition_messagequeue_report_model model)
{
return SqlHelper.Visit((ps) =>
{
string sql = "SELECT ID from tb_partition_messagequeue_report WITH(NOLOCK) WHERE partitionid=@partitionid AND day=@day";
ps.Add("@partitionid", model.partitionid);
ps.Add("@day", model.day);
object obj = conn.ExecuteScalar(sql, ps.ToParameters());
if (obj != DBNull.Value && obj != null)
{
int id = Convert.ToInt32(obj);
return this.UpdateMsgCount(conn, model.mqmaxid, model.mqminid, model.mqcount, id);
}
else
{
return Add2(conn, model);
}
});
}
public List<tb_node_model> GetList(DbConn PubConn, string keyword, string cstime, string cetime, int pagesize, int pageindex, out int count)
{
int _count = 0;
List<tb_node_model> Model = new List<tb_node_model>();
DataSet dsList = SqlHelper.Visit<DataSet>(ps =>
{
string sqlwhere = "";
string sql = "select ROW_NUMBER() over(order by id desc) as rownum,id,nodename,nodecreatetime,nodeip,nodelastupdatetime,ifcheckstate from tb_node where 1=1 ";
if (!string.IsNullOrWhiteSpace(keyword))
{
ps.Add("keyword", keyword);
sqlwhere = " and (nodename like '%'+@keyword+'%' or nodeip like '%'+@keyword+'%') ";
}
DateTime d = DateTime.Now;
if (DateTime.TryParse(cstime, out d))
{
ps.Add("CStime", Convert.ToDateTime(cstime));
sqlwhere += " and nodecreatetime>=@CStime";
}
if (DateTime.TryParse(cetime, out d))
{
ps.Add("CEtime", Convert.ToDateTime(cetime));
sqlwhere += " and nodecreatetime<=@CEtime";
}
_count = Convert.ToInt32(PubConn.ExecuteScalar("select count(1) from tb_node 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;
PubConn.SqlToDataSet(ds, sqlSel, ps.ToParameters());
return ds;
});
foreach (DataRow dr in dsList.Tables[0].Rows)
{
tb_node_model n = CreateModel(dr);
Model.Add(n);
}
count = _count;
return Model;
}
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("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;
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 int GetMaxPartitionIndex(DbConn conn, int mqpathid)
{
return SqlHelper.Visit((ps) =>
{
string sql = "select max(partitionindex) from tb_mqpath_partition p1 WITH(NOLOCK) where p1.mqpathid=@mqpathid";
ps.Add("mqpathid", mqpathid);
object obj = conn.ExecuteScalar(sql, ps.ToParameters());
if (obj != DBNull.Value && obj != null)
{
return LibConvert.ObjToInt(obj);
}
return 0;
});
}
public int GetCountOfPartition(DbConn conn,int mqpathid)
{
return SqlHelper.Visit((ps) =>
{
ps.Add("@mqpathid", mqpathid);
string sql = "select count(0) FROM tb_mqpath_partition WITH(NOLOCK) WHERE mqpathid=@mqpathid";
return Convert.ToInt32(conn.ExecuteScalar(sql,ps.ToParameters()));
});
}
public long GetMaxId(DbConn PubConn)
{
return SqlHelper.Visit((ps) =>
{
string cmd = string.Format("select max(id) from {0} s WITH(NOLOCK)", TableName);
var o = PubConn.ExecuteScalar(cmd, null);
if (o == null || o is DBNull)
return -1;
else
return Convert.ToInt64(o);
});
}
public int GetPartitionCountByState(DbConn conn, int state, int mqPathId)
{
return SqlHelper.Visit((ps) =>
{
string sql = "select COUNT(1) from tb_mqpath_partition WITH(NOLOCK),tb_partition WITH(NOLOCK) where tb_partition.partitionid=tb_mqpath_partition.partitionid and tb_mqpath_partition.state=@state and mqpathid=@mqpathId";
ps.Add("mqpathId", mqPathId);
ps.Add("state", state);
object obj = conn.ExecuteScalar(sql, ps.ToParameters());
if (obj != DBNull.Value && obj != null)
{
return LibConvert.ObjToInt(obj);
}
return 0;
});
}
/// <summary>
/// ��ȡ��Ϣ������
/// </summary>
/// <param name="conn"></param>
/// <param name="tableName"></param>
/// <param name="state"></param>
/// <returns></returns>
public int GetMsgCount(DbConn conn, string tableName, int state)
{
return SqlHelper.Visit((ps) =>
{
string sql = string.Format("SELECT COUNT(1) FROM {0} WITH(NOLOCK) WHERE state=@state", tableName);
ps.Add("@state", state);
object obj = conn.ExecuteScalar(sql, ps.ToParameters());
if (obj != DBNull.Value && obj != null)
{
return LibConvert.ObjToInt(obj);
}
return 0;
});
}
public IList<tb_messagequeue_model> GetPageList(DbConn conn, int pageIndex, int pageSize, string id, ref int count)
{
int tempCount = 0;
IList<tb_messagequeue_model> list = new List<tb_messagequeue_model>();
var result = SqlHelper.Visit((ps) =>
{
StringBuilder where = new StringBuilder(" WHERE 1=1");
if (!string.IsNullOrWhiteSpace(id))
{
where.AppendFormat(" AND id={0}", id);
}
string sql = string.Format("SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS rownum,* FROM {0} WITH(NOLOCK)", TableName) + where;
string countSql = string.Format("SELECT COUNT(1) FROM {0} WITH(NOLOCK)", TableName) + where;
object obj = conn.ExecuteScalar(countSql, null);
if (obj != DBNull.Value && obj != null)
{
tempCount = LibConvert.ObjToInt(obj);
}
string sqlPage = string.Concat("SELECT * FROM (", sql.ToString(), ") A WHERE rownum BETWEEN ", ((pageIndex - 1) * pageSize + 1), " AND ", pageSize * pageIndex);
DataTable dt = conn.SqlToDataTable(sqlPage, null);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
tb_messagequeue_model model = CreateModel(dr);
list.Add(model);
}
}
return list;
});
count = tempCount;
return result;
}
public int SetState(DbConn conn, int partitionid,int state)
{
return SqlHelper.Visit((ps) =>
{
string sql = "update tb_mqpath_partition set state=@state WHERE partitionid=@partitionid";
ps.Add("partitionid", partitionid);
ps.Add("state", state);
object obj = conn.ExecuteScalar(sql, ps.ToParameters());
if (obj != DBNull.Value && obj != null)
{
return LibConvert.ObjToInt(obj);
}
return 0;
});
}
public IList<MqPathPartitionModel> GetPageList(DbConn conn, string mqPathid, string partitionId, int pageIndex, int pageSize, ref int count)
{
int tempCount = 0;
IList<MqPathPartitionModel> list = new List<MqPathPartitionModel>();
var result = SqlHelper.Visit((ps) =>
{
StringBuilder where = new StringBuilder("");
if (!string.IsNullOrWhiteSpace(mqPathid))
{
if (!mqPathid.isint())
where.AppendFormat(" AND m.mqpath like '%'+'{0}'+'%'", mqPathid);
else
where.AppendFormat(" AND m.id ='{0}'", mqPathid);
}
if (!string.IsNullOrWhiteSpace(partitionId))
{
where.AppendFormat(" AND p.partitionId={0}", partitionId);
}
string sql = "SELECT ROW_NUMBER() OVER(ORDER BY p.mqpathid DESC,p.[partitionindex] desc) AS rownum,p.*,m.mqpath,(select max(partitionindex) from tb_mqpath_partition p1 where p.mqpathid=p1.mqpathid) as maxpartitionindex FROM tb_mqpath_partition p WITH(NOLOCK),tb_mqpath m with(nolock) where p.mqpathid=m.id";
string countSql = "SELECT COUNT(1) FROM tb_mqpath_partition p WITH(NOLOCK),tb_mqpath m with(nolock) where p.mqpathid=m.id" + where.ToString();
object obj = conn.ExecuteScalar(countSql, null);
if (obj != DBNull.Value && obj != null)
{
tempCount = LibConvert.ObjToInt(obj);
}
string sqlPage = string.Concat("SELECT * FROM (", sql.ToString(), where.ToString(), ") A WHERE rownum BETWEEN ", ((pageIndex - 1) * pageSize + 1), " AND ", pageSize * pageIndex);
DataTable dt = conn.SqlToDataTable(sqlPage, null);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
MqPathPartitionModel m = new MqPathPartitionModel();
m.mqpath_partition_model = CreateModel(dr);
m.mqpath = Convert.ToString(dr["mqpath"]);
m.maxpartitionindex = Convert.ToInt32(dr["maxpartitionindex"]);
list.Add(m);
}
}
return list;
});
count = tempCount;
return result;
}
/// <summary>
/// 取得未消费的数量
/// </summary>
/// <param name="lastMqId"></param>
/// <returns></returns>
public long GetNonMsgCount(DbConn conn, long lastMqId)
{
return SqlHelper.Visit((ps) =>
{
MQIDInfo info = PartitionRuleHelper.GetMQIDInfo(lastMqId);
var currentday = conn.GetServerDate().Date;
string sql = "SELECT SUM(mqcount) FROM [tb_partition_messagequeue_report] WITH(NOLOCK) WHERE [day]>@day AND partitionid=@partitionid AND [day]<>@currentday";
ps.Add("@day", info.Day); ps.Add("@currentday", currentday);
ps.Add("@partitionid", PartitionRuleHelper.GetPartitionID(new PartitionIDInfo() { DataNodePartition = info.DataNodePartition, TablePartition = info.TablePartition }));
object obj = conn.ExecuteScalar(sql, ps.ToParameters());
long msgCount = 0;
if (obj != DBNull.Value && obj != null)
{
msgCount = LibConvert.ObjToInt64(obj);
}
long firstCount = 0; long lastCount = 0;
using (DbConn nodeConn = DbConfig.CreateConn(DataConfig.DataNodeParConn(PartitionRuleHelper.PartitionNameRule(info.DataNodePartition))))
{
nodeConn.Open();
string firsttableName = PartitionRuleHelper.GetTableName(info.TablePartition, info.Day);
var msgDal = new tb_messagequeue_dal(); msgDal.TableName = firsttableName;
firstCount = msgDal.GetLastDayNonMsgCount(nodeConn, lastMqId);
if (info.Day != currentday)//不是今天
{
string lasttableName = PartitionRuleHelper.GetTableName(info.TablePartition, currentday);
var dal = new tb_messagequeue_dal(); dal.TableName = lasttableName;
long maxmqid = dal.GetMaxID(nodeConn);
if (lastMqId == 0)
lastCount = 0;
else
lastCount = dal.GetLastDayMsgCount(nodeConn, maxmqid);
}
}
//最后一天剩余
return msgCount + firstCount + lastCount;
});
}
public int GetAvailableNode(DbConn PubConn)
{
return SqlHelper.Visit(ps =>
{
string sql = "select top 1 id from tb_node where (nodelastupdatetime='' or DATEDIFF(minute ,nodecreatetime,GETDATE())>20)";
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, null);
if (ds.Tables[0].Rows.Count > 0)
{
int id = Convert.ToInt32(ds.Tables[0].Rows[0]["id"]);
return id;
}
else
{
string sqlinsert = "insert into tb_node (nodename,nodecreatetime,nodeip)values('ÐÂÔö½Úµã',getdate(),'') select @@IDENTITY";
int id = Convert.ToInt32(PubConn.ExecuteScalar(sqlinsert, null));
return id;
}
});
}
public int CheckTaskState(DbConn PubConn, int id)
{
return SqlHelper.Visit(ps =>
{
ps.Add("id", id);
string sql = "select taskstate from tb_task where id=@id";
int i = Convert.ToInt32(PubConn.ExecuteScalar(sql, ps.ToParameters()));
return i;
});
}
public List<tb_command_model_Ex> GetList(DbConn PubConn, int commandstate, int taskid, int nodeid, int pagesize, int pageindex, out int count)
{
int _count = 0;
List<tb_command_model_Ex> Model = new List<tb_command_model_Ex>();
DataSet dsList = SqlHelper.Visit<DataSet>(ps =>
{
string sqlwhere = "";
string sql = "select ROW_NUMBER() over(order by C.id desc) as rownum,C.*,T.taskname,n.nodename from tb_command C LEFT JOIN tb_task T on C.taskid=T.id left join tb_node n on C.nodeid=n.id where 1=1 ";
if (taskid!=-1)
{
ps.Add("taskid", taskid);
sqlwhere = " and C.taskid=@taskid ";
}
if (nodeid != -1)
{
ps.Add("nodeid", nodeid);
sqlwhere = " and C.nodeid=@nodeid ";
}
if (commandstate != -1)
{
ps.Add("commandstate", commandstate);
sqlwhere = " and C.commandstate=@commandstate ";
}
_count = Convert.ToInt32(PubConn.ExecuteScalar("select count(1) from tb_command C 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;
PubConn.SqlToDataSet(ds, sqlSel, ps.ToParameters());
return ds;
});
foreach (DataRow dr in dsList.Tables[0].Rows)
{
tb_command_model_Ex n = CreateModelEX(dr);
Model.Add(n);
}
count = _count;
return Model;
}
public IList<ConsumerModel> GetPageList(DbConn conn, string name, int pageIndex, int pageSize, ref int count)
{
int tempCount = 0;
IList<ConsumerModel> list = new List<ConsumerModel>();
ConsumerModel cm = new ConsumerModel();
var result = SqlHelper.Visit((ps) =>
{
StringBuilder where = new StringBuilder(" WHERE 1=1 ");
if (!string.IsNullOrEmpty(name))
{
where.AppendFormat(" AND clientname LIKE '%{0}%'", name);
}
string sql = "SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS rownum,* FROM tb_consumer WITH(NOLOCK)";
string countSql = "SELECT COUNT(1) FROM tb_consumer WITH(NOLOCK) " + where.ToString();
object obj = conn.ExecuteScalar(countSql, null);
if (obj != DBNull.Value && obj != null)
{
tempCount = LibConvert.ObjToInt(obj);
}
string sqlPage = string.Concat("SELECT * FROM (", sql.ToString(), where.ToString(), ") A WHERE rownum BETWEEN ", ((pageIndex - 1) * pageSize + 1), " AND ", pageSize * pageIndex);
DataTable dt = conn.SqlToDataTable(sqlPage, null);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
ConsumerModel model = cm.CreateModel(dr);
IList<tb_consumer_partition_model> consumerList = partitionDal.GetPartitionByConsumerId(conn, model.consumerclientid);
if (consumerList != null && consumerList.Count > 0)
{
IList<ConsumerPartition> partitionList = new List<ConsumerPartition>();
foreach (var item in consumerList)
{
ConsumerPartition m = new ConsumerPartition();
m.PartitionId = item.partitionid;
PartitionIDInfo partitionInfo = PartitionRuleHelper.GetPartitionIDInfo(item.partitionid);
string node = string.Empty;
if (partitionInfo.DataNodePartition < 10)
{
node = "0" + partitionInfo.DataNodePartition.Tostring();
}
else
{
node = partitionInfo.DataNodePartition.Tostring();
}
using (DbConn nodeConn = DbConfig.CreateConn(DataConfig.DataNodeParConn(node)))
{
nodeConn.Open();
tb_partition_model partitionModel = new tb_partition_dal().Get(conn, item.partitionid);
if (partitionModel != null)
{
m.IsOnline = partitionModel.isused;
}
string table = msgDal.GetMaxMqTable(nodeConn, node);
m.Msg = msgDal.GetMsgCount(nodeConn, table, 0);
m.NonMsg = msgDal.GetMsgCount(nodeConn, table, 1);
partitionList.Add(m);
}
}
model.PartitionList = partitionList;
}
list.Add(model);
}
}
return list;
});
count = tempCount;
return result;
}
public IList<MqPathModel> GetPageList(DbConn conn, string mqpathid, string mqpath, int pageIndex, int pageSize, ref int count)
{
int tempCount = 0;
IList<MqPathModel> list = new List<MqPathModel>();
MqPathModel createM = new MqPathModel();
var result = SqlHelper.Visit((ps) =>
{
StringBuilder where = new StringBuilder(" WHERE 1=1");
if (!string.IsNullOrEmpty(mqpath))
{
where.AppendFormat(" AND mqpath LIKE '%{0}%'", mqpath);
}
if (!string.IsNullOrEmpty(mqpathid))
{
where.AppendFormat(" AND id = '{0}'", mqpathid);
}
string sql = "SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS rownum,* FROM tb_mqpath WITH(NOLOCK)";
string countSql = "SELECT COUNT(1) FROM tb_mqpath WITH(NOLOCK)" + where;
object obj = conn.ExecuteScalar(countSql, null);
if (obj != DBNull.Value && obj != null)
{
tempCount = LibConvert.ObjToInt(obj);
}
string sqlPage = string.Concat("SELECT * FROM (", sql.ToString(), where.ToString(), ") as t WHERE rownum BETWEEN ", ((pageIndex - 1) * pageSize + 1), " AND ", pageSize * pageIndex);
DataTable dt = conn.SqlToDataTable(sqlPage, null);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
MqPathModel model = createM.CreateModel(dr);
model.ProductCount = proDal.GetProductCount(conn, model.id, XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.SystemParamConfig.Producter_HeatBeat_Every_Time);
model.NonProductCount = proDal.GetNonProductCount(conn, model.id, XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.SystemParamConfig.Producter_HeatBeat_Every_Time);
model.Connsumer = new tb_consumer_partition_dal().GetActiveConsumerCount(conn,model.id);
model.NonConnsumer = new tb_consumer_partition_dal().GetLogoutConsumerCount(conn, model.id);
model.Partition = parDal.GetPartitionCountByState(conn, (int)XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.EnumMqPathPartitionState.Running, model.id);
model.NonPartition = parDal.GetPartitionCountByState(conn, (int)XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.EnumMqPathPartitionState.WaitConsumeCompleted, model.id);
list.Add(model);
}
}
return list;
});
count = tempCount;
return result;
}
public IList<RegisterdConsumersModel> GetPageList2(DbConn conn, string partitionid, string consumerclientid, string mqpathid, int pageIndex, int pageSize, ref int count)
{
int tempCount = 0;
IList<RegisterdConsumersModel> list = new List<RegisterdConsumersModel>();
ConsumerModel cm = new ConsumerModel();
var result = SqlHelper.Visit((ps) =>
{
StringBuilder where = new StringBuilder(" WHERE 1=1 ");
if (!string.IsNullOrEmpty(consumerclientid))
{
if (!consumerclientid.isint())
where.AppendFormat(" AND (c.client LIKE '%{0}%')", consumerclientid);
else
where.AppendFormat(" AND (c.id = '{0}')", consumerclientid);
}
if (!string.IsNullOrEmpty(partitionid))
{
where.AppendFormat(" AND (p.partitionid='{0}')", partitionid);
}
if (!string.IsNullOrEmpty(mqpathid))
{
if (!mqpathid.isint())
{
where.AppendFormat(" AND (m.mqpath like '%{0}%')", mqpathid);
}
else
{
where.AppendFormat(" AND (m.id='{0}')", mqpathid);
}
}
string sql = @"SELECT ROW_NUMBER() OVER(ORDER BY p.consumerclientid DESC,p.partitionindex desc) AS rownum, s.id as tb_consumer_id,tempid as tb_consumer_tempid,s.consumerclientid as tb_consumer_consumerclientid,s.partitionindexs as tb_consumer_partitionindexs
,s.clientname as tb_consumer_clientname,s.lastheartbeat as tb_consumer_lastheartbeat,s.lastupdatetime as tb_consumer_lastupdatetime,s.createtime as tb_consumer_createtime,
c.id as tb_consumer_client_id, c.client as tb_consumer_client_client,c.createtime as tb_consumer_client_createtime, p.id as tb_consumer_partition_id,p.consumerclientid as tb_consumer_partition_consumerclientid
,p.partitionindex as tb_consumer_partition_partitionindex,p.partitionid as tb_consumer_partition_partitionid,p.lastconsumertempid as tb_consumer_partition_lastconsumertempid,p.lastmqid as tb_consumer_partition_lastmqid
,p.lastupdatetime as tb_consumer_partition_lastupdatetime,p.createtime as tb_consumer_partition_createtime,m.mqpath as tb_mqpath_mqpath,m.id as tb_mqpath_id
from tb_consumer_partition p WITH(NOLOCK) left join tb_consumer s WITH(NOLOCK) on s.tempid=p.lastconsumertempid left join tb_consumer_client c WITH(NOLOCK) on p.consumerclientid=c.id left join tb_mqpath_partition mp with (nolock) on mp.partitionid=p.partitionid left join tb_mqpath m with (nolock) on m.id=mp.mqpathid ";
string countSql = "SELECT COUNT(p.id) from tb_consumer_partition p WITH(NOLOCK) left join tb_consumer s WITH(NOLOCK) on s.tempid=p.lastconsumertempid left join tb_consumer_client c WITH(NOLOCK) on p.consumerclientid=c.id left join tb_mqpath_partition mp with (nolock) on mp.partitionid=p.partitionid left join tb_mqpath m with (nolock) on m.id=mp.mqpathid " + where.ToString();
object obj = conn.ExecuteScalar(countSql, null);
if (obj != DBNull.Value && obj != null)
{
tempCount = LibConvert.ObjToInt(obj);
}
string sqlPage = string.Concat("SELECT * FROM (", sql.ToString(), where.ToString(), ") A WHERE rownum BETWEEN ", ((pageIndex - 1) * pageSize + 1), " AND ", pageSize * pageIndex);
DataTable dt = conn.SqlToDataTable(sqlPage, null);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
RegisterdConsumersModel model = new RegisterdConsumersModel(dr);
model.msgCount = reportDal.GetMsgCount(conn, model.consumerpartitionmodel.lastmqid);
model.nonMsgCount = reportDal.GetNonMsgCount(conn, model.consumerpartitionmodel.lastmqid);
model.mqpath = dr["tb_mqpath_mqpath"].Tostring();
model.mqpathid = dr["tb_mqpath_id"].Toint();
list.Add(model);
}
}
return list;
});
count = tempCount;
return result;
}
/// <summary>
/// 取得已消费的数量
/// </summary>
/// <param name="conn"></param>
/// <param name="lastMqId"></param>
/// <returns></returns>
public long GetMsgCount(DbConn conn, long lastMqId)
{
return SqlHelper.Visit((ps) =>
{
MQIDInfo info = PartitionRuleHelper.GetMQIDInfo(lastMqId);
string sql = "SELECT SUM(mqcount) FROM [tb_partition_messagequeue_report] WITH(NOLOCK) WHERE [day]<@day AND partitionid=@partitionid";
ps.Add("@day", info.Day);
ps.Add("@partitionid", PartitionRuleHelper.GetPartitionID(new PartitionIDInfo() { DataNodePartition = info.DataNodePartition, TablePartition = info.TablePartition }));
string tableName = PartitionRuleHelper.GetTableName(info.TablePartition, info.Day);
object obj = conn.ExecuteScalar(sql, ps.ToParameters());
long msgCount = 0;
if (obj != DBNull.Value && obj != null)
{
msgCount = LibConvert.ObjToInt64(obj);
}
long lastCount = 0;
using (DbConn nodeConn = DbConfig.CreateConn(DataConfig.DataNodeParConn(PartitionRuleHelper.PartitionNameRule(info.DataNodePartition))))
{
nodeConn.Open();
var dal = new tb_messagequeue_dal(); dal.TableName = tableName;
lastCount = dal.GetLastDayMsgCount(nodeConn, lastMqId);
}
return msgCount + lastCount;
});
}
/// <summary>
/// ���ݷ���id��ȡ��������
/// </summary>
/// <param name="partitionId"></param>
/// <returns></returns>
public int GetPartitionQueue(DbConn conn, int partitionId, EnumMqPathPartitionState state)
{
return SqlHelper.Visit((ps) =>
{
string sql = "SELECT COUNT(1) FROM tb_mqpath_partition WITH(NOLOCK) WHERE partitionId=@partitionId AND stage=@stage";
ps.Add("@partitionId", partitionId);
ps.Add("@stage", Convert.ToInt32(state));
object obj = conn.ExecuteScalar(sql, ps.ToParameters());
if (obj != DBNull.Value && obj != null)
{
return Convert.ToInt32(obj);
}
return 0;
});
}
public List<tb_loginfo_model> GetList(DbConn PubConn, string keyword, int id, string cstime, string cetime, int logtype, int taskid, int nodeid, int pagesize, int pageindex, out int count)
{
List<tb_loginfo_model> model = new List<tb_loginfo_model>();
int _count = 0;
DataSet dsList = SqlHelper.Visit<DataSet>(ps =>
{
string sqlwhere = "";
string sql = "select ROW_NUMBER() over(order by E.id desc) as rownum,E.*,ISNULL(T.taskcreateuserid,0) taskcreateuserid, u.username as taskusername,T.taskname,n.nodename as tasknodename from tb_log E left join tb_task T on E.taskid=T.id left join tb_user u on t.taskcreateuserid=u.id left join tb_node n on n.id=E.nodeid where 1=1 ";
if (!string.IsNullOrWhiteSpace(keyword))
{
ps.Add("keyword", keyword);
sqlwhere = " and msg like '%'+@keyword+'%' ";
}
if (taskid != -1)
{
ps.Add("taskid", taskid);
sqlwhere = " and E.taskid=@taskid";
}
if (nodeid != -1)
{
ps.Add("nodeid", nodeid);
sqlwhere = " and E.nodeid=@nodeid";
}
if (id != -1)
{
ps.Add("id", id);
sqlwhere = " and E.id=@id";
}
DateTime d = DateTime.Now;
if (DateTime.TryParse(cstime, out d))
{
ps.Add("CStime", Convert.ToDateTime(cstime));
sqlwhere += " and E.logcreatetime>=@CStime";
}
if (DateTime.TryParse(cetime, out d))
{
ps.Add("CEtime", Convert.ToDateTime(cetime));
sqlwhere += " and E.logcreatetime<=@CEtime";
}
if (logtype != -1)
{
ps.Add("logtype", logtype);
sqlwhere += " and E.logtype=@logtype";
}
_count = Convert.ToInt32(PubConn.ExecuteScalar("select count(1) from tb_log E 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;
PubConn.SqlToDataSet(ds, sqlSel, ps.ToParameters());
return ds;
});
foreach (DataRow dr in dsList.Tables[0].Rows)
{
tb_loginfo_model m = new tb_loginfo_model();
m.log_model = CreateModel(dr);
m.taskusername = Convert.ToString(dr["taskusername"]);
m.taskname = Convert.ToString(dr["taskname"]);
model.Add(m);
}
count = _count;
return model;
}
public int AddTask(DbConn PubConn, tb_task_model model)
{
return SqlHelper.Visit<int>(ps =>
{
ps.Add("@taskname", model.taskname);
ps.Add("@categoryid", model.categoryid);
ps.Add("@nodeid", model.nodeid);
ps.Add("@taskcreatetime", model.taskcreatetime);
ps.Add("@taskupdatetime", model.taskcreatetime);
ps.Add("@taskerrorcount", 0);
ps.Add("@taskruncount", 0);
ps.Add("@taskcreateuserid", model.taskcreateuserid);
ps.Add("@taskstate",0);
ps.Add("@taskversion", 1);
ps.Add("@taskappconfigjson", model.taskappconfigjson.NullToEmpty());
ps.Add("@taskcron", model.taskcron);
ps.Add("@taskmainclassnamespace", model.taskmainclassnamespace);
ps.Add("@taskremark", model.taskremark);
ps.Add("@taskmainclassdllfilename", model.taskmainclassdllfilename);
int rev = Convert.ToInt32(PubConn.ExecuteScalar(@"insert into tb_task(taskname,categoryid,nodeid,taskcreatetime,taskruncount,taskcreateuserid,taskstate,taskversion,taskappconfigjson,taskcron,taskmainclassnamespace,taskremark,taskmainclassdllfilename)
values(@taskname,@categoryid,@nodeid,@taskcreatetime,@taskruncount,@taskcreateuserid,@taskstate,@taskversion,@taskappconfigjson,@taskcron,@taskmainclassnamespace,@taskremark,@taskmainclassdllfilename) select @@IDENTITY", ps.ToParameters()));
return rev;
});
}