public virtual bool Edit(DbConn PubConn, tb_timewatchlog_api_dayreport_model model)
{
List <ProcedureParameter> Par = new List <ProcedureParameter>()
{
//接口api url
new ProcedureParameter("@url", model.url),
//日期
new ProcedureParameter("@date", model.date),
//平均耗时
new ProcedureParameter("@avgtime", model.avgtime),
//最大耗时
new ProcedureParameter("@maxtime", model.maxtime),
//最小耗时
new ProcedureParameter("@mintime", model.mintime),
//上一次扫描最大id
new ProcedureParameter("@lastmaxid", model.lastmaxid),
//上次扫描更新时间
new ProcedureParameter("@lastupdatetime", model.lastupdatetime),
//调用次数
new ProcedureParameter("@count", model.count)
};
Par.Add(new ProcedureParameter("@id", model.id));
int rev = PubConn.ExecuteSql("update tb_timewatchlog_api_dayreport set url=@url,date=@date,avgtime=@avgtime,maxtime=@maxtime,mintime=@mintime,lastmaxid=@lastmaxid,lastupdatetime=@lastupdatetime,count=@count where id=@id", Par);
return(rev == 1);
}
public virtual bool Add(DbConn PubConn, tb_timewatchlog_api_dayreport_model model)
{
List <ProcedureParameter> Par = new List <ProcedureParameter>()
{
//接口api url
new ProcedureParameter("@url", model.url),
//日期
new ProcedureParameter("@date", model.date),
//平均耗时
new ProcedureParameter("@avgtime", model.avgtime),
//最大耗时
new ProcedureParameter("@maxtime", model.maxtime),
//最小耗时
new ProcedureParameter("@mintime", model.mintime),
//上一次扫描最大id
new ProcedureParameter("@lastmaxid", model.lastmaxid),
//上次扫描更新时间
new ProcedureParameter("@lastupdatetime", model.lastupdatetime),
//调用次数
new ProcedureParameter("@count", model.count)
};
int rev = PubConn.ExecuteSql(@"insert into tb_timewatchlog_api_dayreport(url,date,avgtime,maxtime,mintime,lastmaxid,lastupdatetime,count)
values(@url,@date,@avgtime,@maxtime,@mintime,@lastmaxid,@lastupdatetime,@count)" , Par);
return(rev == 1);
}
public virtual tb_timewatchlog_api_dayreport_model CreateModel(DataRow dr)
{
var o = new tb_timewatchlog_api_dayreport_model();
//
if (dr.Table.Columns.Contains("id"))
{
o.id = dr["id"].Toint();
}
//接口api url
if (dr.Table.Columns.Contains("url"))
{
o.url = dr["url"].Tostring();
}
//日期
if (dr.Table.Columns.Contains("date"))
{
o.date = dr["date"].ToDateTime();
}
//平均耗时
if (dr.Table.Columns.Contains("avgtime"))
{
o.avgtime = dr["avgtime"].Todouble();
}
//最大耗时
if (dr.Table.Columns.Contains("maxtime"))
{
o.maxtime = dr["maxtime"].Todouble();
}
//最小耗时
if (dr.Table.Columns.Contains("mintime"))
{
o.mintime = dr["mintime"].Todouble();
}
//上一次扫描最大id
if (dr.Table.Columns.Contains("lastmaxid"))
{
o.lastmaxid = dr["lastmaxid"].Toint();
}
//上次扫描更新时间
if (dr.Table.Columns.Contains("lastupdatetime"))
{
o.lastupdatetime = dr["lastupdatetime"].ToDateTime();
}
//调用次数
if (dr.Table.Columns.Contains("count"))
{
o.count = dr["count"].Toint();
}
return(o);
}
public bool StatisApiMinitor()
{
tb_timewatchlog_api_dayreport_model staticDayReport = new tb_timewatchlog_api_dayreport_model();
List <string> sqlConnList = new List <string>();
string day = "";
string date = "";
using (DbConn PubConn = DbConfig.CreateConn(XXFConfig.MonitorPlatformConnectionString))
{
PubConn.Open();
DateTime dateNow = PubConn.GetServerDate();
day = dateNow.ToString("yyyyMMdd");
date = dateNow.ToString("yyyy-MM-dd");
sqlConnList = new tb_database_config_dal().GetDataBaseSqlConnList(PubConn, (int)DataBaseType.Timewatch);
}
foreach (string conn in sqlConnList)
{
using (DbConn PubConn = DbConfig.CreateConn(conn))
{
PubConn.Open();
int oldMaxID = GetOldDayReportMaxID(PubConn, date);
Dictionary <string, tb_timewatchlog_api_dayreport_model> oldDayReportList = GetOldDayReport(PubConn, date);
List <tb_timewatchlog_api_dayreport_model> newDayReportList = GetNewDayReport(PubConn, day, oldMaxID);
foreach (tb_timewatchlog_api_dayreport_model m in newDayReportList)
{
if (oldDayReportList.Keys.Contains(m.url))
{
tb_timewatchlog_api_dayreport_model oldStaticDayReport = oldDayReportList[m.url];
tb_timewatchlog_api_dayreport_model newStaticDayReport = new tb_timewatchlog_api_dayreport_model()
{
id = oldStaticDayReport.id,
avgtime = (oldStaticDayReport.avgtime * oldStaticDayReport.count + m.avgtime * m.count) / (oldStaticDayReport.count + m.count),
maxtime = oldStaticDayReport.maxtime > m.maxtime ? oldStaticDayReport.maxtime : m.maxtime,
mintime = oldStaticDayReport.mintime < m.mintime ? oldStaticDayReport.mintime : m.mintime,
lastmaxid = m.lastmaxid,
url = m.url,
count = oldStaticDayReport.count + m.count,
lastupdatetime = DateTime.Now
};
Update(PubConn, newStaticDayReport);
}
else
{
Insert(PubConn, m);
}
}
}
}
return(true);
}
public Dictionary <string, tb_timewatchlog_api_dayreport_model> GetOldDayReport(DbConn PubConn, string day)
{
return(SqlHelper.Visit(ps =>
{
ps.Add("day", day);
string sql = "select * from tb_timewatchlog_api_dayreport with (nolock) where date=@day";
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, ps.ToParameters());
Dictionary <string, tb_timewatchlog_api_dayreport_model> dic = new Dictionary <string, tb_timewatchlog_api_dayreport_model>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
tb_timewatchlog_api_dayreport_model m = CreateModel(dr);
dic.Add(m.url, m);
}
return dic;
}));
}
public int Update(DbConn PubConn, tb_timewatchlog_api_dayreport_model model)
{
return(SqlHelper.Visit(ps =>
{
ps.Add("id", model.id);
ps.Add("avgtime", model.avgtime);
ps.Add("maxtime", model.maxtime);
ps.Add("mintime", model.mintime);
ps.Add("lastmaxid", model.lastmaxid);
ps.Add("count", model.count);
string sql = @"update tb_timewatchlog_api_dayreport set
avgtime=@avgtime,maxtime=@maxtime,mintime=@mintime,
lastmaxid=@lastmaxid,lastupdatetime=getdate(),count=@count where id=@id";
int rev = PubConn.ExecuteSql(sql, ps.ToParameters());
return rev;
}));
}
public List <tb_timewatchlog_api_dayreport_model> GetList(DbConn PubConn, string url, string timebegin, string timeend, string orderby, int pagesize, int pageindex, out int count)
{
int _count = 0;
List <tb_timewatchlog_api_dayreport_model> model = new List <tb_timewatchlog_api_dayreport_model>();
DataSet dsList = SqlHelper.Visit <DataSet>(ps =>
{
string sqlwhere = " where 1=1 ";
DateTime dtimebegin = DateTime.Now;
if (DateTime.TryParse(timebegin, out dtimebegin))
{
ps.Add("timebegin", dtimebegin);
sqlwhere += " and d.date>=@timebegin ";
}
DateTime dtimeend = DateTime.Now;
if (DateTime.TryParse(timeend, out dtimeend))
{
ps.Add("timeend", timeend);
sqlwhere += " and d.date<=@timeend ";
}
if (!string.IsNullOrWhiteSpace(url))
{
ps.Add("url", url);
sqlwhere += " and d.url=@url ";
}
if (string.IsNullOrWhiteSpace(orderby))
{
orderby = "date";
}
StringBuilder sql = new StringBuilder();
sql.Append("select ROW_NUMBER() over(order by " + orderby + " desc) as rownum,* from tb_timewatchlog_api_dayreport d with (nolock) " + sqlwhere);
_count = Convert.ToInt32(PubConn.ExecuteScalar("select count(1) from tb_timewatchlog_api_dayreport d with (nolock) " + sqlwhere, ps.ToParameters()));
DataSet ds = new DataSet();
string sqlSel = "select * from (" + sql + ") 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_timewatchlog_api_dayreport_model m = CreateModel(dr);
model.Add(m);
}
count = _count;
return(model);
}
public int Insert(DbConn PubConn, tb_timewatchlog_api_dayreport_model model)
{
return(SqlHelper.Visit(ps =>
{
ps.Add("url", model.url);
ps.Add("avgtime", model.avgtime);
ps.Add("maxtime", model.maxtime);
ps.Add("mintime", model.mintime);
ps.Add("lastmaxid", model.lastmaxid);
ps.Add("count", model.count);
string sql = @"insert into tb_timewatchlog_api_dayreport
(url,date,avgtime,maxtime,mintime,lastmaxid,lastupdatetime,count)
values
(@url,getdate(),@avgtime,@maxtime,@mintime,@lastmaxid,getdate(),@count)";
int rev = PubConn.ExecuteSql(sql, ps.ToParameters());
return rev;
}));
}
private List <tb_timewatchlog_api_dayreport_model> GetNewDayReport(DbConn PubConn, string day, int oldmaxid)
{
return(SqlHelper.Visit(ps =>
{
ps.Add("id", oldmaxid);
ps.Add("day", day);
string sql = "select url,AVG(time) avgtime,MAX(time) maxtime,MIN(time) mintime, count(1) count,MAX(id) lastmaxid from tb_timewatchlog_api" + day + " with (nolock) where id>@id Group By Url";
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, ps.ToParameters());
List <tb_timewatchlog_api_dayreport_model> model = new List <tb_timewatchlog_api_dayreport_model>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
tb_timewatchlog_api_dayreport_model m = CreateModel(dr);
model.Add(m);
}
return model;
}));
}
