public void CalculateWeight(string connectionstring, string criteria, Interval interval, string excel)
{
DBengine.DBengine dbeng = new DBengine.DBengine();
try
{
dbeng.DBConnect(connectionstring, DataBaseType.sqlserver);
if (!string.IsNullOrEmpty(criteria)) criteria = " where " + criteria;
DateTime startTime = Convert.ToDateTime(dbeng.ExecuteScalar("select min(convert(datetime,FirstCaptureTime)) from StoredProcedure "+ criteria));
DateTime endTime = Convert.ToDateTime(dbeng.ExecuteScalar("select max(convert(datetime,FirstCaptureTime)) from StoredProcedure " + criteria));
TimeSpan step = new TimeSpan(1, 0, 0);
switch (interval)
{
case Interval.Hour:
step = new TimeSpan(1, 0, 0);
break;
case Interval.Day:
step = new TimeSpan(24, 0, 0);
break;
case Interval.Week:
step = new TimeSpan(24 * 7, 0, 0);
break;
}
using (ExcelHelper.ExcelHelper excelHelper = new ExcelHelper.ExcelHelper(excel))
{
for (DateTime timerange = startTime; timerange < endTime; timerange = timerange.Add(step))
{
DataTable dt = dbeng.QuerySQL("SELECT [dbo].[Operation].[Object]" +
" ,[dbo].[Operation].[Table]" +
" ,[dbo].[Operation].[Operation]" +
" ,CONVERT(decimal, Sum([dbo].[Operation].[Count]))/Sum([dbo].[StoredProcedure].[Count]) as Count" +
" FROM [dbo].[Operation] inner join [dbo].[StoredProcedure] on [dbo].[Operation].[Object]=[dbo].[StoredProcedure].[Object] and [dbo].[Operation].[FirstCaptureTime]=[dbo].[StoredProcedure].[FirstCaptureTime] " +
" where convert(datetime,[dbo].[Operation].[FirstCaptureTime])>='" + timerange.ToString("yyyy/MM/dd HH:mm:ss") + "' and " +
" convert(datetime,[dbo].[Operation].[FirstCaptureTime])<'" + timerange.Add(step).ToString("yyyy/MM/dd HH:mm:ss") + "'" +
" group by [dbo].[Operation].[Object],[dbo].[Operation].[Table],[dbo].[Operation].[Operation]");
Dictionary<string, decimal> result = CalculateWeight(dt.Select());
excelHelper.Append("Date" + timerange.ToString("yyyy/MM/dd HH:mm:ss"), result);
}
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine(ex.StackTrace);
System.Diagnostics.Trace.WriteLine(ex.Message);
}
finally
{
dbeng.Close();
}
}
public void CalculateWeight(List<string> SourceFiles, Interval interval, string excel)
{
DataTable tableOperation = new DataTable("Operation");
tableOperation.Columns.Add("Object");
tableOperation.Columns.Add("CaptureTime");
tableOperation.Columns.Add("Table");
tableOperation.Columns.Add("Operation");
tableOperation.Columns.Add("Count",typeof(decimal));
DateTime startTime = DateTime.MaxValue;
DateTime endTime = DateTime.MinValue;
foreach (string file in SourceFiles)
{
DataSet ds = new DataSet();
ds.ReadXml(file);
foreach (DataRow dr in ds.Tables[0].Rows)
{
string captureTime = dr["FirstCaptureTime"].ToString();
if (startTime >Convert.ToDateTime( captureTime)) startTime = Convert.ToDateTime(captureTime);
if (endTime < Convert.ToDateTime(captureTime)) endTime = Convert.ToDateTime(captureTime);
string objectName = dr["Object"].ToString();
int count = Convert.ToInt32(dr["Count"].ToString());
foreach (DataRow opdr in ds.Tables[1].Select(" Object='"+objectName+"'"))
{
decimal avgcount = Convert.ToDecimal(opdr["Count"].ToString())/count;
tableOperation.Rows.Add(new object[] { objectName, captureTime, opdr["Table"].ToString(), opdr["Operation"].ToString(),avgcount });
}
}
}
TimeSpan step= new TimeSpan(1,0,0);
switch (interval)
{
case Interval.Hour:
step = new TimeSpan(1, 0, 0);
break;
case Interval.Day:
step = new TimeSpan(24, 0, 0);
break;
case Interval.Week:
step = new TimeSpan(24*7, 0, 0);
break;
}
using (ExcelHelper.ExcelHelper excelHelper = new ExcelHelper.ExcelHelper(excel))
{
for (DateTime timerange = startTime; timerange < endTime; timerange = timerange.Add(step))
{
var drarray = from dr
in tableOperation.Select()
where Convert.ToDateTime(dr["CaptureTime"].ToString()) >= timerange && Convert.ToDateTime(dr["CaptureTime"].ToString()) < timerange.Add(step)
select dr;
Dictionary<string, decimal> result = CalculateWeight(drarray.ToArray<DataRow>());
excelHelper.Append("Date" + timerange.ToString("yyyy/MM/dd HH:mm:ss"), result);
}
}
}
void br_ResultUpdated(Dictionary<string, decimal> result)
{
using (ExcelHelper.ExcelHelper excelHelper = new ExcelHelper.ExcelHelper(excelname))
{
excelHelper.Append(label, result);
}
br_ProgressUpdated(0, 10);
}