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);
}
public virtual List<tb_shuntruleconn_config_model> Get(DbConn PubConn,DateTime lastUpdateTime)
{
return XXF.ProjectTool.SqlHelper.Visit(ps =>
{
List<ProcedureParameter> Par = new List<ProcedureParameter>();
StringBuilder stringSql = new StringBuilder();
if (lastUpdateTime == default(DateTime))
{
stringSql.Append(@"select s.* from tb_distribution_rule_config s");
}
else
{
stringSql.Append(@"select s.* from tb_distribution_rule_config s where s.f_last_update_time>=@lastTime");
ps.Add("lastTime", lastUpdateTime);
}
// int rev = PubConn.ExecuteSql(stringSql.ToString(), ps.ToParameters());
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters());
var rs = new List<tb_shuntruleconn_config_model>();
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
var r = CreateModel(dr);
rs.Add(r);
}
}
return rs;
});
}
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 tb_tempdata_model GetByTaskID(DbConn PubConn, int taskid)
{
return SqlHelper.Visit(ps =>
{
ps.Add("taskid", taskid);
string sql = "select * from tb_tempdata where taskid=@taskid";
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, ps.ToParameters());
tb_tempdata_model model = CreateModel(ds.Tables[0].Rows[0]);
return model;
});
}
public virtual tb_node_model Get(DbConn PubConn, int id)
{
List<ProcedureParameter> Par = new List<ProcedureParameter>();
Par.Add(new ProcedureParameter("@id", id));
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"select s.* from tb_node s where s.id=@id");
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), Par);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
return CreateModel(ds.Tables[0].Rows[0]);
}
return null;
}
public virtual string Get2(DbConn PubConn, string key)
{
List<ProcedureParameter> Par = new List<ProcedureParameter>();
Par.Add(new ProcedureParameter("@key", key));
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"select value from tb_config s WITH(NOLOCK) where s.[key]=@key");
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), Par);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
return Convert.ToString(ds.Tables[0].Rows[0][0]);
}
return null;
}
public virtual tb_consumer_model GetByTempId(DbConn PubConn, long tempid)
{
List<ProcedureParameter> Par = new List<ProcedureParameter>();
Par.Add(new ProcedureParameter("@tempid", tempid));
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"select s.* from tb_consumer s WITH(NOLOCK) where s.tempid=@tempid");
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), Par);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
return CreateModel(ds.Tables[0].Rows[0]);
}
return null;
}
public virtual tb_mqpath_model GetByPartitionID(DbConn PubConn, int partitionid)
{
List<ProcedureParameter> Par = new List<ProcedureParameter>();
Par.Add(new ProcedureParameter("@partitionid", partitionid));
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"select m.* from tb_mqpath_partition s,tb_mqpath m WITH(NOLOCK) where s.partitionid=@partitionid and s.mqpathid=m.id");
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), Par);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
return CreateModel(ds.Tables[0].Rows[0]);
}
return null;
}
public List <DbModels.api> GetGradeApis(XXF.Db.DbConn PubConn, int apptype, int appgradeno)
{
string sql = "SELECT apiid,apptype,appgradeno,categoryid,apiname,apititle,area,controller,action,para,apidesc,freeze FROM api where apptype=" + apptype + " and appgradeno=" + appgradeno;
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, null);
List <DbModels.api> list = new List <DbModels.api>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
list.Add(CreateModel(dr));
}
return(list);
}
public virtual List<tb_config_model> List(DbConn PubConn)
{
List<ProcedureParameter> Par = new List<ProcedureParameter>();
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"select s.* from tb_config s WITH(NOLOCK)");
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), Par);
List<tb_config_model> rs = new List<tb_config_model>();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
rs.Add(CreateModel(dr));
}
return rs;
}
public int GetMaxCommandID(DbConn PubConn)
{
return SqlHelper.Visit(ps =>
{
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"select max(id) from tb_command s ");
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters());
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
}
return 0;
});
}
public virtual tb_consumer_client_model GetByClient(DbConn PubConn, string client)
{
return SqlHelper.Visit((ps) =>
{
ps.Add("@client", client);
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"select top 1 s.* from tb_consumer_client s WITH(NOLOCK) where s.client=@client");
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters());
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
return CreateModel(ds.Tables[0].Rows[0]);
}
return null;
});
}
public virtual tb_mqpath_model Get(DbConn PubConn, string mqpath)
{
return SqlHelper.Visit((ps) => {
List<ProcedureParameter> Par = new List<ProcedureParameter>();
Par.Add(new ProcedureParameter("@mqpath", mqpath));
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"select s.* from tb_mqpath s WITH(NOLOCK) where s.mqpath=@mqpath");
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), Par);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
return CreateModel(ds.Tables[0].Rows[0]);
}
return null;
});
}
public virtual string GetTempData(DbConn PubConn, int taskid)
{
return SqlHelper.Visit(ps =>
{
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"select s.* from tb_tempdata s where s.taskid=@taskid");
ps.Add("taskid", taskid);
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters());
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
return Convert.ToString( ds.Tables[0].Rows[0]["tempdatajson"]);
}
return null;
});
}
public CertCenter.Models.DbModels.tb_token GetToken(XXF.Db.DbConn PubConn, string userid, string appid, CertCenter.Models.DbModels.TokenType tokentype)
{
string sql = " select token,userid,id,username,appid,createtime,expires from " + tokentype.ToString() + " where userid=@userid and appid=@appid";
List <ProcedureParameter> Par = new List <ProcedureParameter>();
Par.Add(new ProcedureParameter("@userid", userid));
Par.Add(new ProcedureParameter("@appid", appid));
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, Par);
if (ds.Tables[0].Rows.Count != 1)
{
return(null);
}
return(CreateModel(ds.Tables[0].Rows[0]));
}
public virtual tb_version_model GetCurrentVersion(DbConn PubConn, int taskid,int version)
{
return SqlHelper.Visit(ps =>
{
ps.Add("@taskid", taskid);
ps.Add("@version", version);
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"select s.* from tb_version s where s.taskid=@taskid and s.version=@version");
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters());
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count>0)
{
return CreateModel(ds.Tables[0].Rows[0]);
}
return null;
});
}
public tb_user_model GetUserName(DbConn PubConn, string userstaffno)
{
return SqlHelper.Visit(ps =>
{
ps.Add("userstaffno", userstaffno);
string sql = "select id,username,userrole from tb_user where userstaffno=@userstaffno";
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, ps.ToParameters());
if (ds.Tables[0].Rows.Count > 0)
{
tb_user_model m = CreateModel(ds.Tables[0].Rows[0]);
return m;
}
else
return null;
});
}
public List<tb_version_model> GetTaskVersion(DbConn PubConn, int taskid)
{
return SqlHelper.Visit(ps =>
{
ps.Add("@taskid", taskid);
string sql = "select version,zipfilename from tb_version where taskid=@taskid";
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, ps.ToParameters());
List<tb_version_model> model = new List<tb_version_model>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
tb_version_model m = CreateModel(dr);
model.Add(m);
}
return model;
});
}
public virtual tb_datanode_model Get2(DbConn PubConn, int datanodepartition)
{
return SqlHelper.Visit((ps) =>
{
List<ProcedureParameter> Par = new List<ProcedureParameter>();
Par.Add(new ProcedureParameter("@datanodepartition", datanodepartition));
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"select s.* from tb_datanode s WITH(NOLOCK) where s.datanodepartition=@datanodepartition");
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), Par);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
return CreateModel(ds.Tables[0].Rows[0]);
}
return null;
});
}
public bool CheckMaxPartitionIndexOfMqPathIsRunning(DbConn PubConn, int mqpathid)
{
List<ProcedureParameter> Par = new List<ProcedureParameter>();
Par.Add(new ProcedureParameter("@mqpathid", mqpathid));
Par.Add(new ProcedureParameter("@state", (int)XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.EnumMqPathPartitionState.Running));
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"select top 1 id from tb_mqpath_partition s WITH(NOLOCK) where s.mqpathid=@mqpathid and partitionindex = (select max(partitionindex) from tb_mqpath_partition s WITH(NOLOCK) where s.mqpathid=@mqpathid) and state=@state");
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), Par);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0][0] is DBNull)
return false;
return true;
}
return true;
}
public static string GetConnStr(CertCenter.Models.DbModels.TokenType tokentype, string userid = "")
{
string constr = "";
switch (tokentype)
{
case CertCenter.Models.DbModels.TokenType.usertoken:
constr = XXF.Db.DbConfig.GetConfig("dydDbConn");
break;
case CertCenter.Models.DbModels.TokenType.shoptoken:
using (XXF.Db.DbConn PubConn = XXF.Db.DbConfig.CreateConn(XXF.Db.DbType.SQLSERVER, XXF.Db.DbConfig.GetConfig("dydDbConn")))
{
PubConn.Open();
string sql = "select f_shzh,f_shdqbm,f_shsj from tb_shop_area where f_shzh=@userid or f_shsj=@userid";
XXF.Db.SimpleProcedureParameter para = new SimpleProcedureParameter();
para.Add("@userid", userid);
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, para.ToParameters());
if (ds.Tables[0].Rows.Count == 0)
{
return("");
}
string dqbm = ds.Tables[0].Rows[0]["f_shdqbm"].ToString();
string t = XXF.Db.DbConfig.GetConfig("ShopAreaConnectString");
throw new Exception("no show");
// constr = XXF.Db.DbAreaRule.ShopAreaPartitionRule(t, Convert.ToInt32(dqbm));
}
break;
case CertCenter.Models.DbModels.TokenType.managetoken:
constr = XXF.Db.DbConfig.GetConfig("CrmDbConn");
break;
default:
break;
}
if (constr == "")
{
throw new Exception("车有问题。");
}
return(constr);
}
public List<tb_performanceinfo_model> GetAllWithTask(DbConn PubConn, string nodeid, string taskid, string orderby, DateTime? lastupdatetime)
{
return SqlHelper.Visit(ps =>
{
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"select p.*,n.nodename,t.taskname from tb_performance p,tb_node n,tb_task t where p.nodeid=n.id and p.taskid=t.id ");
if (!string.IsNullOrEmpty(nodeid))
{
stringSql.Append(@" and p.nodeid=@nodeid ");
ps.Add("@nodeid",nodeid);
}
if (!string.IsNullOrEmpty(taskid))
{
stringSql.Append(@" and taskid=@taskid ");
ps.Add("@taskid", taskid);
}
if (lastupdatetime!=null)
{
stringSql.Append(@" and lastupdatetime>@lastupdatetime ");
ps.Add("@lastupdatetime", lastupdatetime);
}
if (!string.IsNullOrEmpty(orderby))
{
stringSql.Append(@" order by " + orderby);
}
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters());
List<tb_performanceinfo_model> rs = new List<tb_performanceinfo_model>();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
tb_performanceinfo_model m = new tb_performanceinfo_model();
m.model = CreateModel(dr);
m.nodename = Convert.ToString(dr["nodename"]);
m.taskname = Convert.ToString(dr["taskname"]);
rs.Add(m);
}
}
return rs;
});
}
public List<tb_user_model> GetAllUsers(DbConn PubConn)
{
return SqlHelper.Visit(ps =>
{
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"select * from tb_user s order by id desc");
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters());
List<tb_user_model> rs = new List<tb_user_model>();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
rs.Add(CreateModel(dr));
}
}
return rs;
});
}
public virtual List<tb_datanode_model> List(DbConn PubConn, List<int> datanodepartitions)
{
return SqlHelper.Visit((ps) =>
{
List<tb_datanode_model> rs = new List<tb_datanode_model>();
if (datanodepartitions.Count > 0)
{
List<ProcedureParameter> Par = new List<ProcedureParameter>();
StringBuilder stringSql = new StringBuilder();
stringSql.Append(string.Format(@"select s.* from tb_datanode s WITH(NOLOCK) where datanodepartition in ({0})", SqlHelper.CmdIn<int>(Par, datanodepartitions)));
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), Par);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
rs.Add(CreateModel(dr));
}
}
return rs;
});
}
public virtual DateTime? GetLastUpdateTimeOfMqPath(DbConn PubConn, string mqpath)
{
return SqlHelper.Visit<DateTime?>((ps) =>
{
List<ProcedureParameter> Par = new List<ProcedureParameter>();
Par.Add(new ProcedureParameter("@mqpath", mqpath));
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"select lastupdatetime from tb_mqpath s WITH(NOLOCK) where s.mqpath=@mqpath");
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), Par);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
var o = ds.Tables[0].Rows[0]["lastupdatetime"];
if (Convert.IsDBNull(o))
return null;
else
return Convert.ToDateTime(o);
}
return null;
});
}
public List<tb_command_model> GetNodeCommands(DbConn PubConn, int nodeid, int lastmaxid)
{
return SqlHelper.Visit(ps =>
{
ps.Add("@nodeid", nodeid);
ps.Add("@id", lastmaxid);
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"select s.* from tb_command s where (s.nodeid=@nodeid or s.nodeid=0) and s.id>@id order by id asc");
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters());
List<tb_command_model> rs = new List<tb_command_model>();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
rs.Add(CreateModel(dr));
}
}
return rs;
});
}
public List<tb_category_model> GetList(DbConn PubConn,string keyword)
{
return SqlHelper.Visit(ps =>
{
string sql = "select id,categoryname,categorycreatetime from tb_category ";
if (!string.IsNullOrWhiteSpace(keyword))
{
ps.Add("keyword", keyword);
sql += "where categoryname like '%'+@keyword+'%'";
}
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, ps.ToParameters());
List<tb_category_model> Model = new List<tb_category_model>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
tb_category_model m = CreateModel(dr);
Model.Add(m);
}
return Model;
});
}
public List<int> GetTaskIDsByState(DbConn PubConn, int taskstate,int nodeid)
{
return SqlHelper.Visit(ps =>
{
ps.Add("@taskstate", taskstate);
ps.Add("@nodeid", nodeid);
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"select id from tb_task s where s.taskstate=@taskstate and s.nodeid=@nodeid");
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters());
List<int> rs = new List<int>();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
rs.Add(Convert.ToInt32(dr[0]));
}
}
return rs;
});
}
public List<tb_task_model> GetLongRunningTaskIDs(DbConn PubConn, int maxrunningtimeseconds)
{
return SqlHelper.Visit(ps =>
{
ps.Add("@maxrunningtime", DateTime.Parse("1900-01-01").AddSeconds((int)maxrunningtimeseconds));
ps.Add("@taskstate", (int)EnumTaskState.Running);
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"select * from tb_task s where (s.tasklastendtime-s.tasklaststarttime)>@maxrunningtime and taskstate=@taskstate");
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters());
List<tb_task_model> rs = new List<tb_task_model>();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
rs.Add(CreateModel(dr));
}
}
return rs;
});
}
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_senderror_model> GetErrors(DbConn PubConn, int lastlogid)
{
return SqlHelper.Visit(ps =>
{
ps.Add("@lastlogid", lastlogid);
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"select top 100 s.*,t.taskcreateuserid,t.taskname from tb_error s,tb_task t where s.id>@lastlogid and s.taskid=t.id order by s.id desc");
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters());
List<tb_senderror_model> rs = new List<tb_senderror_model>();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
tb_senderror_model model = new tb_senderror_model();
model.error_model = CreateModel(dr);
model.taskcreateuserid = Convert.ToInt32(dr["taskcreateuserid"]);
model.taskname = Convert.ToString(dr["taskname"]);
rs.Add(model);
}
}
return rs;
});
}
public Models.DbModels.api Get(DbConn PubConn, string apiname, int apptype)
{
XXF.Db.SimpleProcedureParameter para = new SimpleProcedureParameter();
para.Add("area", apiname ?? "");
para.Add("@apptype", apptype);
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"select s.* from api s where s.appname=@appname and s.apptype=@apptype");
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), para.ToParameters());
if (ds != null && ds.Tables.Count > 0)
{
return CreateModel(ds.Tables[0].Rows[0]);
}
return null;
}
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 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 tb_command_model_Ex GetOneCommand(DbConn PubConn, int id)
{
return SqlHelper.Visit(ps =>
{
ps.Add("id", id);
string sql = "select C.*,T.taskname from tb_command C LEFT JOIN tb_task T on C.taskid=T.id where C.id=@id";
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, ps.ToParameters());
tb_command_model_Ex model = CreateModelEX(ds.Tables[0].Rows[0]);
return model;
});
}
