/// <summary> /// 查询分组表 /// </summary> /// <param name="ReportSource"></param> /// <param name="ReportTemplate"></param> /// <param name="Parameters"></param> private JsonResult QueryGroupTable(ReportSource ReportSource, ReportTemplate ReportTemplate, ReportTemplateParameter[] Parameters) { Analytics.AnalyticalQuery Query = this.Engine.BPAQuery.GetAnalyticalQuery(this.Engine, ReportSource); DataTable dataTable = Query.QueryGroupTable(ReportSource.ExecuteTableName, ReportSource.Columns, ReportTemplate, Parameters); object QueryResult; if (string.IsNullOrWhiteSpace(ReportTemplate.ColumnTitle)) {//没有列标题 QueryResult = QueryNoneColumnTitle(ReportTemplate, dataTable, ReportSource.Columns); } else { //有行标题 if (string.IsNullOrWhiteSpace(ReportTemplate.RowTitle)) { //没有配置行标题的 QueryResult = QueryNoneRowTitle(ReportTemplate, dataTable, ReportSource.Columns); } else {//有配置行标题 QueryResult = QueryExistRowTitle(ReportTemplate, dataTable, ReportSource.Columns); } } return(Json(QueryResult, JsonRequestBehavior.AllowGet)); }
public JsonResult ExportEchartsReport(string filterDatas, string reportTemplateSetting) { return(ExecuteFunctionRun(() => { ReportTemplate ReportTemplate = JsonConvert.DeserializeObject <ReportTemplate>(reportTemplateSetting); ReportTemplateParameter[] FilterDatas = JsonConvert.DeserializeObject <ReportTemplateParameter[]>(filterDatas); ReportSource ReportSource = this.Engine.Analyzer.GetReportSourceByCode(ReportTemplate.SourceCode); Analytics.AnalyticalQuery Query = this.Engine.BPAQuery.GetAnalyticalQuery(this.Engine, ReportSource); DataTable dataTable = Query.QueryGroupTable(ReportSource.ExecuteTableName, ReportSource.Columns, ReportTemplate, FilterDatas); string FileUrl = string.Empty; if (string.IsNullOrWhiteSpace(ReportTemplate.RowTitle)) {//没有配置行标题的 FileUrl = ExportEchartsReportWithoutRowTitle(ReportTemplate, dataTable, ReportSource.Columns); } else {//有配置行标题 if (string.IsNullOrWhiteSpace(ReportTemplate.ColumnTitle)) { FileUrl = ExportEchartsReportWithoutColumnTitle(ReportTemplate, dataTable, ReportSource.Columns); } else { FileUrl = ExportEchartsReportQueryExistRowTitle(ReportTemplate, dataTable, ReportSource.Columns); } } var result = new { FileUrl = FileUrl }; return Json(result, JsonRequestBehavior.AllowGet);; })); }
public JsonResult SqlValidation(string sqlData, string dbCode) { return(ExecuteFunctionRun(() => { ActionResult result = new ActionResult(); Regex reg = new Regex("/order\\s+by/"); if (string.IsNullOrWhiteSpace(sqlData)) { result.Success = false; } else if (reg.IsMatch(sqlData.ToLower())) { result.Success = false; } else { try { BizDbConnectionConfig dbConfig = this.Engine.SettingManager.GetBizDbConnectionConfig(dbCode); Analytics.AnalyticalQuery Query = new Analytics.AnalyticalQuery(dbConfig.DbType, dbConfig.DbConnectionString); Query.CommandFactory.CreateCommand().ExecuteDataTable(sqlData); result.Success = true; } catch { result.Success = false; } } return Json(result, JsonRequestBehavior.AllowGet); })); }
/// <summary> /// 加载报表模板数据 /// </summary> /// <param name="ReportSource">报表数据源</param> /// <returns>报表模板数据</returns> private object LoadSourceData(ReportSource ReportSource) { Analytics.AnalyticalQuery Query = this.Engine.BPAQuery.GetAnalyticalQuery(this.Engine, ReportSource); List <Dictionary <string, object> > objList = new List <Dictionary <string, object> >(); Dictionary <string, object> obj; DataTable dataTable = Query.QueryTable(ReportSource.ExecuteTableName, ReportSource.Columns, null, 1, 5); foreach (DataRow row in dataTable.Rows) { obj = new Dictionary <string, object>(); foreach (ReportSourceColumn col in ReportSource.Columns) { if (col.DataType == DataType.DateTime)//时间类型做转换 { obj.Add(col.ColumnCode, row[col.ColumnCode].ToString()); } else if (col.ReportSourceColumnType != ReportSourceColumnType.CustomColumn) { obj.Add(col.ColumnCode, row[col.ColumnCode]); } } objList.Add(obj); } return(new { Rows = objList, Total = objList.Count }); }
/// <summary> /// 读取配置最原始的的列 /// </summary> /// <param name="ReportSource"></param> /// <returns></returns> private List <ReportSourceColumn> GetOriginalColumnDataBySetting(ReportSource ReportSource) { List <ReportSourceColumn> ReportSourceColumns = new List <ReportSourceColumn>(); if (ReportSource.ReportSourceType == ReportSourceType.H3System && !string.IsNullOrWhiteSpace(ReportSource.SchemaCode)) {//数据模型 DataModel.BizObjectSchema schema = this.Engine.BizObjectManager.GetPublishedSchema(ReportSource.SchemaCode); if (schema != null) { ReportSourceColumns.AddRange(CreateColumnsBySchema(schema)); } } else {//业务数据配置 //BizDbConnectionConfig DBConfig = this.Engine.SettingManager.GetBizDbConnectionConfig(ReportSource.DbCode); Analytics.AnalyticalQuery Query = this.Engine.BPAQuery.GetAnalyticalQuery(this.Engine, ReportSource); if (null != Query) { ReportSourceColumns = Query.GetTableColumns(ReportSource.TableNameOrCommandText, ReportSource.DataSourceType); } } return(ReportSourceColumns); }
/// <summary> /// 查询表 /// </summary>; /// <param name="ReportSource"></param> /// <param name="TemplateColumns"></param> private SummaryReportResult QueryTable(ReportSource ReportSource, int page, int pageSize, ReportTemplateColumn[] TemplateColumns = null, ReportTemplateParameter[] Parameters = null) { Analytics.AnalyticalQuery Query = this.Engine.BPAQuery.GetAnalyticalQuery(this.Engine, ReportSource); ReportSourceColumn[] Columns = ReportSource.Columns; int dataCount = Query.QueryTableCount(ReportSource.ExecuteTableName, Parameters); int startIndex = 0; int endIndex = dataCount; if (pageSize > 0) { startIndex = ((page - 1) * pageSize + 1) < 1 ? 1 : ((page - 1) * pageSize) + 1; endIndex = page * pageSize >= dataCount ? dataCount : page * pageSize; } //报表数据 List <Dictionary <string, object> > ReportDatas = new List <Dictionary <string, object> >(); //汇总列数据 Dictionary <string, double> SummaryColumns = new Dictionary <string, double>(); Dictionary <string, object> ReportData; DataTable dataTable = Query.QueryTable(ReportSource.ExecuteTableName, Columns, Parameters, startIndex, endIndex); //提取需要被装换的列名 List <string> convertCodes = new List <string>(); //批量查询组织 Unit[] units = this.GetConvertCodesUnits(out convertCodes, TemplateColumns, dataTable.Rows, Columns); //填充返回单元格 foreach (DataRow row in dataTable.Rows) { ReportData = new Dictionary <string, object>(); foreach (ReportSourceColumn col in Columns) { if (col.ReportSourceColumnType != ReportSourceColumnType.CustomColumn) { //从Units中找到对应的Unit if (convertCodes.Contains(col.ColumnCode)) { Unit user = null; foreach (var u in units) { if (u.ObjectID == row[col.ColumnCode].ToString()) { user = u; break; } } if (user != null && user.Name != null) { row[col.ColumnCode] = user.Name; } } ReportData.Add(col.ColumnCode, row[col.ColumnCode]); } //汇总列结算 if (col.ReportSourceColumnType != ReportSourceColumnType.CustomColumn && col.DataType == DataType.Numeric) { double count = 0; if (double.TryParse(row[col.ColumnCode].ToString(), out count)) { if (SummaryColumns.ContainsKey(col.ColumnCode)) { SummaryColumns[col.ColumnCode] += count; } else { SummaryColumns.Add(col.ColumnCode, count); } } } } ReportDatas.Add(ReportData); } SummaryReportResult SummaryReportResult = new SummaryReportResult(ReportDatas, dataCount, SummaryColumns); return(SummaryReportResult); }