public List<RegisterdConsumersModel> GetRegisterdConsumers(DbConn PubConn)
{
return SqlHelper.Visit((ps) =>
{
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"select 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
from tb_consumer s WITH(NOLOCK),tb_consumer_client c WITH(NOLOCK),tb_consumer_partition p WITH(NOLOCK) where s.consumerclientid=c.id and s.consumerclientid=p.consumerclientid and s.tempid=p.lastconsumertempid");
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters());
List<RegisterdConsumersModel> rs = new List<RegisterdConsumersModel>();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
RegisterdConsumersModel m = new RegisterdConsumersModel(dr);
rs.Add(m);
}
}
return rs;
});
}
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;
}
