/// <summary>
/// 导出方法
/// </summary>
/// <param name="bo"></param>
private void MoreExport(object bo)
{
ExcelHelper.ExcelHelper ExcelExport = new ExcelHelper.ExcelHelper();
DataSet ds = new DataSet();
Percent = 0;
try
{
SaveParameter1 sp = (SaveParameter1)bo;
if (sp.PARAM != null)
{
BaseService bs = new BaseService();
foreach (Dictionary <string, Newtonsoft.Json.Linq.JArray> a in sp.PARAM)
{
DataTable dt1 = bs.GetDataTableToParamID(a);
ds.Tables.Add(dt1);
}
ExcelExport.ExprotExcel("", sp.TempName, ds, sp.UserName);
State = 2;
}
}
catch (Exception e)
{
FileLog.WriteLog("Error：调用CMDI.Service.SaveExcel/Export()方法。" + e.Message);
ErrorTime = DateTime.Now;
Percent = 0;
}
finally
{
FinishTime = DateTime.Now;
}
}
void br_ResultUpdated(Dictionary<string, decimal> result)
{
using (ExcelHelper.ExcelHelper excelHelper = new ExcelHelper.ExcelHelper(excelname))
{
excelHelper.Append(label, result);
}
br_ProgressUpdated(0, 10);
}
/// <summary>
/// 导出方法
/// </summary>
/// <param name="bo"></param>
private void Export(object bo)
{
ExcelHelper.ExcelHelper ExcelExport = new ExcelHelper.ExcelHelper();
DataSet ds = new DataSet();
Percent = 0;
try
{
SaveParameter sp = (SaveParameter)bo;
if (sp.PARAM != null)
{
BaseService bs = new BaseService();
DataTable dt1 = bs.GetDataTableToParamID(sp.PARAM);
ds.Tables.Add(dt1);
ExcelExport.ExprotExcel(dt1.TableName, sp.TempName, ds, sp.UserName);
State = 2;
}
// 指标甄核规则-导入模板下载
if (sp.PARAMGP != null)
{
BaseService bs_gp = new BaseService();
DataTable dt1 = bs_gp.GetDataTableToParamID(sp.PARAMGP);
ds.Tables.Add(dt1);
ExcelExport.ExprotExcel(dt1.TableName, sp.TempName, ds, sp.UserName);
State = 2;
}
// 导出采集报表 Author:彭皓 2017-4-14
if (sp.Ds != null)
{
foreach (string tempname in sp.TempNames)
{
ExcelExport.ExportExcel(tempname, sp.Ds, sp.dicAuditRules, sp.UserName);
FileLog.WriteLog("测试(" + tempname + ")");
}
State = 2;
FileLog.WriteLog("测试(success)：SaveExcel.State=" + State);
}
}
catch (Exception e)
{
FileLog.WriteLog("Error：调用CMDI.Service.SaveExcel/Export()方法。" + e);
ErrorTime = DateTime.Now;
Percent = 0;
State = 3;
FileLog.WriteLog("测试(catch)：SaveExcel.State=" + State);
}
finally
{
FinishTime = DateTime.Now;
FileLog.WriteLog("测试(finally)：SaveExcel.State=" + State);
}
}
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);
}
}
}
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();
}
}
