private static Dictionary <string, double> CreateAmountDictionary(
Scenario scenario,
ChartDescriptor descriptor,
DataStore store)
{
string tag = GetChartCacheTag(descriptor);
Dictionary <string, double> dict = new Dictionary <string, double>();
string query = CreateAmountQuery(scenario, descriptor);
DataTable dt = StochasticTime.ChartCache.GetCachedData(scenario, query, tag);
if (dt == null)
{
dt = store.CreateDataTableFromQuery(query, "AmountData");
StochasticTime.ChartCache.SetCachedData(scenario, query, dt, tag);
}
foreach (DataRow dr in dt.Rows)
{
if (dr[Strings.DATASHEET_SUMOFAMOUNT_COLUMN_NAME] != DBNull.Value)
{
int it = Convert.ToInt32(dr[Strings.DATASHEET_ITERATION_COLUMN_NAME], CultureInfo.InvariantCulture);
int ts = Convert.ToInt32(dr[Strings.DATASHEET_TIMESTEP_COLUMN_NAME], CultureInfo.InvariantCulture);
string k = string.Format(CultureInfo.InvariantCulture, "{0}-{1}", it, ts);
dict.Add(k, Convert.ToDouble(dr[Strings.DATASHEET_SUMOFAMOUNT_COLUMN_NAME], CultureInfo.InvariantCulture));
}
}
return(dict);
}
private static string CreateRawAttributeDataQuery(
Scenario scenario,
ChartDescriptor descriptor,
string tableName,
string attributeTypeColumnName,
int attributeTypeId)
{
string ScenarioClause = string.Format(CultureInfo.InvariantCulture,
"([{0}]={1})",
Strings.DATASHEET_SCENARIOID_COLUMN_NAME, scenario.Id);
string WhereClause = string.Format(CultureInfo.InvariantCulture,
"{0} AND ([{1}]={2})",
ScenarioClause, attributeTypeColumnName, attributeTypeId);
if (!string.IsNullOrEmpty(descriptor.DisaggregateFilter))
{
WhereClause = string.Format(CultureInfo.InvariantCulture,
"{0} AND ({1})",
WhereClause, descriptor.DisaggregateFilter);
}
if (!string.IsNullOrEmpty(descriptor.IncludeDataFilter))
{
WhereClause = string.Format(CultureInfo.InvariantCulture,
"{0} AND ({1})",
WhereClause, descriptor.IncludeDataFilter);
}
string query = string.Format(CultureInfo.InvariantCulture,
"SELECT Iteration, Timestep, SUM(Amount) AS SumOfAmount FROM {0} WHERE ({1}) GROUP BY Iteration, Timestep",
tableName, WhereClause);
return(query);
}
private static string CreateAmountQuery(Scenario scenario, ChartDescriptor descriptor, string variableName)
{
Debug.Assert(variableName.EndsWith("DensityVariable", StringComparison.Ordinal));
string ScenarioClause = string.Format(CultureInfo.InvariantCulture,
"([{0}]={1})",
Constants.SCENARIO_ID_COLUMN_NAME, scenario.Id);
string WhereClause = ScenarioClause;
string Disagg = RemoveUnwantedColumnReferences(descriptor.DisaggregateFilter, variableName);
string IncData = RemoveUnwantedColumnReferences(descriptor.IncludeDataFilter, variableName);
if (!string.IsNullOrEmpty(Disagg))
{
WhereClause = string.Format(CultureInfo.InvariantCulture, "{0} AND ({1})", WhereClause, Disagg);
}
if (!string.IsNullOrEmpty(IncData))
{
WhereClause = string.Format(CultureInfo.InvariantCulture, "{0} AND ({1})", WhereClause, IncData);
}
string query = string.Format(CultureInfo.InvariantCulture,
"SELECT Iteration, Timestep, SUM(Amount) AS SumOfAmount FROM stsim_OutputStratum WHERE ({0}) GROUP BY Iteration, Timestep",
WhereClause);
return(query);
}
public static Dictionary <string, double> CreateAmountDictionary(
Scenario scenario,
ChartDescriptor descriptor,
string variableName,
DataStore store)
{
Dictionary <string, double> dict = new Dictionary <string, double>();
string query = CreateAmountQuery(scenario, descriptor, variableName);
DataTable dt = StochasticTime.ChartCache.GetCachedData(scenario, query, null);
if (dt == null)
{
dt = store.CreateDataTableFromQuery(query, "AmountData");
StochasticTime.ChartCache.SetCachedData(scenario, query, dt, null);
}
foreach (DataRow dr in dt.Rows)
{
int it = Convert.ToInt32(dr[Constants.ITERATION_COLUMN_NAME], CultureInfo.InvariantCulture);
int ts = Convert.ToInt32(dr[Constants.TIMESTEP_COLUMN_NAME], CultureInfo.InvariantCulture);
string k = string.Format(CultureInfo.InvariantCulture, "{0}-{1}", it, ts);
dict.Add(k, Convert.ToDouble
(dr[Constants.SUM_OF_AMOUNT_COLUMN_NAME],
CultureInfo.InvariantCulture));
}
return(dict);
}
private static string CreateAmountQuery(Scenario scenario, ChartDescriptor descriptor)
{
string ScenarioClause = string.Format(CultureInfo.InvariantCulture,
"([{0}]={1})",
Strings.DATASHEET_SCENARIOID_COLUMN_NAME, scenario.Id);
string WhereClause = ScenarioClause;
string Disagg = RemoveUnwantedColumnReferences(descriptor.DisaggregateFilter);
string IncData = RemoveUnwantedColumnReferences(descriptor.IncludeDataFilter);
if (!string.IsNullOrEmpty(Disagg))
{
WhereClause = string.Format(CultureInfo.InvariantCulture,
"{0} And ({1})",
WhereClause, Disagg);
}
if (!string.IsNullOrEmpty(IncData))
{
WhereClause = string.Format(CultureInfo.InvariantCulture,
"{0} And ({1})",
WhereClause, IncData);
}
string query = string.Format(CultureInfo.InvariantCulture,
"SELECT Iteration, Timestep, SUM(Amount) AS SumOfAmount FROM stsim_OutputStratum WHERE ({0}) GROUP BY Iteration, Timestep",
WhereClause);
return(query);
}
private static string CreateRawChartDataQueryForGroup(
DataSheet dataSheet,
ChartDescriptor descriptor,
string variableName)
{
Debug.Assert(dataSheet.Scenario.Id > 0);
Debug.Assert(
variableName == STOCK_GROUP_VAR_NAME ||
variableName == STOCK_GROUP_DENSITY_VAR_NAME ||
variableName == FLOW_GROUP_VAR_NAME ||
variableName == FLOW_GROUP_DENSITY_VAR_NAME);
string ScenarioClause = string.Format(CultureInfo.InvariantCulture,
"([{0}]={1})",
Constants.SCENARIO_ID_COLUMN_NAME, dataSheet.Scenario.Id);
string SumStatement = string.Format(CultureInfo.InvariantCulture,
"SUM([{0}]) AS {1}",
descriptor.ColumnName, Constants.SUM_OF_AMOUNT_COLUMN_NAME);
string WhereClause = ScenarioClause;
if (!string.IsNullOrEmpty(descriptor.DisaggregateFilter))
{
WhereClause = string.Format(CultureInfo.InvariantCulture,
"{0} AND ({1})",
WhereClause, descriptor.DisaggregateFilter);
}
if (!string.IsNullOrEmpty(descriptor.IncludeDataFilter))
{
WhereClause = string.Format(CultureInfo.InvariantCulture,
"{0} AND ({1})",
WhereClause, descriptor.IncludeDataFilter);
}
string query = string.Format(CultureInfo.InvariantCulture,
"SELECT {0},{1},{2} FROM {3} WHERE {4} GROUP BY [{5}],[{6}]",
Constants.ITERATION_COLUMN_NAME,
Constants.TIMESTEP_COLUMN_NAME,
SumStatement,
descriptor.DatasheetName,
WhereClause,
Constants.ITERATION_COLUMN_NAME,
Constants.TIMESTEP_COLUMN_NAME);
return(query);
}
public override string GetCacheTag(ChartDescriptor descriptor)
{
if (ChartingUtilities.DescriptorHasAgeReference(descriptor))
{
return(Constants.AGE_QUERY_CACHE_TAG);
}
else if (ChartingUtilities.DescriptorHasTSTReference(descriptor))
{
return(Constants.TST_QUERY_CACHE_TAG);
}
else
{
return(null);
}
}
// =======================================================================================
// Descriptor references
// =======================================================================================
private static bool DescriptorHasReference(ChartDescriptor descriptor, string columnName)
{
if (descriptor.IncludeDataFilter != null &&
descriptor.IncludeDataFilter.Contains(columnName))
{
return(true);
}
if (descriptor.DisaggregateFilter != null &&
descriptor.DisaggregateFilter.Contains(columnName))
{
return(true);
}
return(false);
}
private static string GetChartCacheTag(ChartDescriptor descriptor)
{
if (DescriptorHasAgeReference(descriptor))
{
Debug.Assert(!DescriptorHasTSTReference(descriptor));
return(Constants.AGE_QUERY_CACHE_TAG);
}
else if (DescriptorHasTSTReference(descriptor))
{
Debug.Assert(!DescriptorHasAgeReference(descriptor));
return(Constants.TST_QUERY_CACHE_TAG);
}
else
{
return(null);
}
}
public static DataTable CreateRawExternalVariableData(
Scenario scenario,
ChartDescriptor descriptor,
DataStore store)
{
string tag = GetChartCacheTag(descriptor);
string query = CreateRawExternalVariableDataQuery(scenario, descriptor);
DataTable dt = StochasticTime.ChartCache.GetCachedData(scenario, query, tag);
if (dt == null)
{
dt = store.CreateDataTableFromQuery(query, "RawData");
StochasticTime.ChartCache.SetCachedData(scenario, query, dt, tag);
}
return(dt);
}
public static DataTable CreateRawAttributeChartData(
Scenario scenario,
ChartDescriptor descriptor,
string tableName,
string attributeTypeColumnName,
int attributeTypeId,
bool isDensity,
DataStore store)
{
string tag = GetChartCacheTag(descriptor);
string query = CreateRawAttributeDataQuery(scenario, descriptor, tableName, attributeTypeColumnName, attributeTypeId);
DataTable dt = StochasticTime.ChartCache.GetCachedData(scenario, query, tag);
if (dt == null)
{
dt = store.CreateDataTableFromQuery(query, "RawData");
StochasticTime.ChartCache.SetCachedData(scenario, query, dt, tag);
}
if (isDensity)
{
Dictionary <string, double> dict = CreateAmountDictionary(scenario, descriptor, store);
if (dict.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
if (dr[Strings.DATASHEET_SUMOFAMOUNT_COLUMN_NAME] != DBNull.Value)
{
int it = Convert.ToInt32(dr[Strings.DATASHEET_ITERATION_COLUMN_NAME], CultureInfo.InvariantCulture);
int ts = Convert.ToInt32(dr[Strings.DATASHEET_TIMESTEP_COLUMN_NAME], CultureInfo.InvariantCulture);
string k = string.Format(CultureInfo.InvariantCulture, "{0}-{1}", it, ts);
dr[Strings.DATASHEET_SUMOFAMOUNT_COLUMN_NAME] =
Convert.ToDouble(dr[Strings.DATASHEET_SUMOFAMOUNT_COLUMN_NAME],
CultureInfo.InvariantCulture) / dict[k];
}
}
}
}
return(dt);
}
private static DataTable CreateRawChartData(
DataSheet dataSheet,
ChartDescriptor descriptor,
DataStore store,
string variableName)
{
Debug.Assert(
variableName == STOCK_GROUP_VAR_NAME ||
variableName == STOCK_GROUP_DENSITY_VAR_NAME ||
variableName == FLOW_GROUP_VAR_NAME ||
variableName == FLOW_GROUP_DENSITY_VAR_NAME);
string query = CreateRawChartDataQueryForGroup(dataSheet, descriptor, variableName);
DataTable dt = StochasticTime.ChartCache.GetCachedData(dataSheet.Scenario, query, null);
if (dt == null)
{
dt = store.CreateDataTableFromQuery(query, "RawData");
StochasticTime.ChartCache.SetCachedData(dataSheet.Scenario, query, dt, null);
}
if (variableName.EndsWith("DensityVariable", StringComparison.Ordinal))
{
Dictionary <string, double> dict = CreateAmountDictionary(dataSheet.Scenario, descriptor, variableName, store);
if (dict.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
int it = Convert.ToInt32(dr[Constants.ITERATION_COLUMN_NAME], CultureInfo.InvariantCulture);
int ts = Convert.ToInt32(dr[Constants.TIMESTEP_COLUMN_NAME], CultureInfo.InvariantCulture);
string k = string.Format(CultureInfo.InvariantCulture, "{0}-{1}", it, ts);
dr[Constants.SUM_OF_AMOUNT_COLUMN_NAME] =
Convert.ToDouble(dr[Constants.SUM_OF_AMOUNT_COLUMN_NAME],
CultureInfo.InvariantCulture) / dict[k];
}
}
}
return(dt);
}
public override DataTable GetData(DataStore store, ChartDescriptor descriptor, DataSheet dataSheet)
{
if (descriptor.DatasheetName == Constants.DATASHEET_OUTPUT_STOCK_NAME ||
descriptor.DatasheetName == Constants.DATASHEET_OUTPUT_FLOW_NAME)
{
string[] v = descriptor.VariableName.Split('-');
string VarName = v[0];
if (
VarName == STOCK_GROUP_VAR_NAME ||
VarName == STOCK_GROUP_DENSITY_VAR_NAME ||
VarName == FLOW_GROUP_VAR_NAME ||
VarName == FLOW_GROUP_DENSITY_VAR_NAME)
{
return(CreateRawChartData(dataSheet, descriptor, store, VarName));
}
}
return(null);
}
private static string CreateRawExternalVariableDataQuery(Scenario scenario, ChartDescriptor descriptor)
{
string[] s = descriptor.VariableName.Split('-');
Debug.Assert(s.Count() == 2 && s[0] == "stsim_ExternalVariable");
int ExtVarTypeId = int.Parse(s[1], CultureInfo.InvariantCulture);
string ScenarioClause = string.Format(CultureInfo.InvariantCulture,
"([{0}]={1})",
Strings.DATASHEET_SCENARIOID_COLUMN_NAME, scenario.Id);
string WhereClause = string.Format(CultureInfo.InvariantCulture,
"{0} AND ([{1}]={2})",
ScenarioClause, Strings.OUTPUT_EXTERNAL_VARIABLE_VALUE_TYPE_ID_COLUMN_NAME, ExtVarTypeId);
string query = string.Format(CultureInfo.InvariantCulture,
"SELECT Iteration, Timestep, Value AS SumOfAmount FROM {0} WHERE ({1}) ORDER BY Iteration, Timestep",
Strings.OUTPUT_EXTERNAL_VARIABLE_VALUE_DATASHEET_NAME, WhereClause);
return(query);
}
public static bool DescriptorHasTSTReference(ChartDescriptor descriptor)
{
return(DescriptorHasReference(descriptor, Strings.DATASHEET_TST_CLASS_COLUMN_NAME));
}
public override DataTable GetData(DataStore store, ChartDescriptor descriptor, DataSheet dataSheet)
{
if (ChartingUtilities.HasAgeClassUpdateTag(dataSheet.Project))
{
WinFormSession sess = (WinFormSession)dataSheet.Session;
sess.SetStatusMessageWithEvents("Updating age related data...");
dataSheet.Library.Save(store);
sess.SetStatusMessageWithEvents(string.Empty);
Debug.Assert(!ChartingUtilities.HasAgeClassUpdateTag(dataSheet.Project));
}
if (ChartingUtilities.HasTSTClassUpdateTag(dataSheet.Project))
{
WinFormSession sess = (WinFormSession)dataSheet.Session;
sess.SetStatusMessageWithEvents("Updating TST related data...");
dataSheet.Library.Save(store);
sess.SetStatusMessageWithEvents(string.Empty);
Debug.Assert(!ChartingUtilities.HasTSTClassUpdateTag(dataSheet.Project));
}
if (
descriptor.DatasheetName == Strings.DATASHEET_OUTPUT_STRATUM_STATE_NAME ||
descriptor.DatasheetName == Strings.DATASHEET_OUTPUT_STRATUM_TRANSITION_NAME)
{
if (descriptor.VariableName == Strings.STATE_CLASS_PROPORTION_VARIABLE_NAME)
{
return(ChartingUtilities.CreateProportionChartData(
dataSheet.Scenario, descriptor, Strings.DATASHEET_OUTPUT_STRATUM_STATE_NAME, store));
}
else if (descriptor.VariableName == Strings.TRANSITION_PROPORTION_VARIABLE_NAME)
{
return(ChartingUtilities.CreateProportionChartData(
dataSheet.Scenario, descriptor, Strings.DATASHEET_OUTPUT_STRATUM_TRANSITION_NAME, store));
}
else
{
return(null);
}
}
else if (
descriptor.DatasheetName == Strings.DATASHEET_OUTPUT_STATE_ATTRIBUTE_NAME ||
descriptor.DatasheetName == Strings.DATASHEET_OUTPUT_TRANSITION_ATTRIBUTE_NAME)
{
string[] s = descriptor.VariableName.Split('-');
Debug.Assert(s.Count() == 2);
Debug.Assert(s[0] == "stsim_AttrNormal" || s[0] == "stsim_AttrDensity");
int AttrId = int.Parse(s[1], CultureInfo.InvariantCulture);
bool IsDensity = (s[0] == "stsim_AttrDensity");
string ColumnName = null;
if (descriptor.DatasheetName == Strings.DATASHEET_OUTPUT_STATE_ATTRIBUTE_NAME)
{
ColumnName = Strings.DATASHEET_STATE_ATTRIBUTE_TYPE_ID_COLUMN_NAME;
}
else
{
ColumnName = Strings.DATASHEET_TRANSITION_ATTRIBUTE_TYPE_ID_COLUMN_NAME;
}
return(ChartingUtilities.CreateRawAttributeChartData(
dataSheet.Scenario, descriptor, dataSheet.Name, ColumnName, AttrId, IsDensity, store));
}
else if (descriptor.DatasheetName == Strings.OUTPUT_EXTERNAL_VARIABLE_VALUE_DATASHEET_NAME)
{
return(ChartingUtilities.CreateRawExternalVariableData(
dataSheet.Scenario, descriptor, store));
}
return(null);
}
/// <summary>
/// 生成图表
/// </summary>
/// <param name="queryId">视图记录</param>
/// <param name="chartId">图表记录</param>
/// <param name="filter">附加过滤条件</param>
/// <returns></returns>
public ChartContext Build(QueryView.Domain.QueryView queryView, Chart chartEntity, FilterExpression filter = null, string drillGroup = "")
{
ChartContext context = new ChartContext();
//图表对象
ChartDescriptor chart = new ChartDescriptor();
chart = chart.DeserializeFromJson(chartEntity.PresentationConfig);
chart.Title = chartEntity.Name;
//引用视图查询数据
_fetchDataService.GetMetaDatas(queryView.FetchConfig);
if (filter != null)
{
_fetchDataService.QueryExpression.Criteria.AddFilter(filter);
}
//图表数据描述
ChartDataDescriptor chartData = new ChartDataDescriptor();
chartData = chartData.DeserializeFromJson(chartEntity.DataConfig);
if (drillGroup.IsNotEmpty())
{
var categoryChartData = chartData.Fetch.Find(n => n.Type == ChartItemType.Category);
categoryChartData.Attribute = drillGroup;
}
var dataSource = _chartRepository.GetChartDataSource(chartData, _fetchDataService.QueryExpression, _fetchDataService.QueryResolver);
chart.Legend = new List <string>();
var categories = new List <string>();//分类标签
var emptyStr = _loc["chart_nodata"];
foreach (var item in chartData.Fetch)
{
var attr = _fetchDataService.QueryResolver.AttributeList.Find(n => n.EntityId == chartEntity.EntityId && n.Name.IsCaseInsensitiveEqual(item.Attribute));
var name = item.Attribute;
if (item.Type == ChartItemType.Series)
{
//legend
chart.Legend.Add(attr.LocalizedName);
var seriesData = new List <string>();
var seriesName = name + Enum.GetName(typeof(AggregateType), item.Aggregate);
foreach (var d in dataSource)
{
var line = d as IDictionary <string, object>;
seriesData.Add(line[seriesName] != null ? line[seriesName].ToString() : "0");
}
var s = chart.Series.Find(n => n.Name.IsCaseInsensitiveEqual(item.Attribute));
s.Name = attr.LocalizedName;
s.Data = seriesData;
}
else if (item.Type == ChartItemType.Category)
{
if (attr.TypeIsLookUp() || attr.TypeIsOwner() || attr.TypeIsCustomer())
{
name += "name";
}
else if (attr.TypeIsPrimaryKey())
{
name = attr.Name + "name";
}
foreach (var d in dataSource)
{
var line = d as IDictionary <string, object>;
//选项类型，获取选项显示名称
if (line[name] != null && (attr.TypeIsPickList() || attr.TypeIsStatus()))
{
var oname = _optionSetDetailFinder.GetOptionName(attr.OptionSetId.Value, int.Parse(line[name].ToString()));
categories.Add(oname.IfEmpty(emptyStr));
}
//是否类型，获取选项显示名称
else if (line[name] != null && (attr.TypeIsBit() || attr.TypeIsState()))
{
var oname = _stringMapFinder.GetOptionName(attr.AttributeId, int.Parse(line[name].ToString()));
categories.Add(oname.IfEmpty(emptyStr));
}
else
{
categories.Add(line[name] != null ? line[name].ToString() : emptyStr);
}
}
chart.XAxis = new XAxis()
{
Type = AxisType.Category,
Data = categories
};
}
chart.SubTitle = queryView.Name;
context.Chart = chart;
context.ChartData = chartData;
context.Attributes = _fetchDataService.QueryResolver.AttributeList;
context.DataSource = dataSource;
}
return(context);
}
