/// <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);
}
