/// <summary>
/// Selects the series by data set.
/// </summary>
/// <param name="axis">The axis.</param>
/// <param name="siteId">The site id.</param>
/// <returns></returns>
public Dictionary <string, string> SelectSeriesByDataSet(tbl_AnalyticAxis axis, Guid siteId)
{
var result = new Dictionary <string, string>();
var dataSet = Regex.Replace(axis.DataSet, "\\d", string.Empty);
using (var connection = new SqlConnection(Configuration.Settings.ADONetConnectionString))
{
connection.Open();
var query = string.Empty;
if (string.IsNullOrEmpty(axis.Query))
{
query = string.Format(@"if exists (select * from information_schema.columns where table_name = '{0}' and column_name = 'SiteID')
EXEC dbo.sp_executesql @statement = N'SELECT * FROM {0} WHERE SiteID = ''{1}'' ORDER BY Title ASC '
ELSE
SELECT * FROM {0} ORDER BY Title ASC " , dataSet, siteId.ToString());
}
else
{
query = axis.Query.Replace("#DataSet#", dataSet).Replace("#SiteID#", siteId.ToString());
}
using (var command = new SqlCommand(query, connection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader["ID"] is Guid)
{
result.Add(((Guid)reader["ID"]).ToString(), (string)reader["Title"]);
}
else if (reader["ID"] is int)
{
result.Add(((int)reader["ID"]).ToString(), (string)reader["Title"]);
}
else
{
result.Add((reader["ID"]).ToString(), (string)reader["Title"]);
}
}
}
}
}
return(result);
}
/// <summary>
/// Binds the data.
/// </summary>
/// <param name="startDate">The start date.</param>
/// <param name="endDate">The end date.</param>
public void BindData(DateTime?startDate = null, DateTime?endDate = null)
{
if (!AnalyticReportId.HasValue)
{
return;
}
var reportUserSettings =
DataManager.AnalyticReportUserSettings.SelectByAnalyticReportId(CurrentUser.Instance.ID,
(Guid)AnalyticReportId);
if (IsLoadFromSettings && Page != null && !Page.IsPostBack && !string.IsNullOrEmpty(UserSettingsClassName))
{
var userSettings = DataManager.UserSettings.SelectByClassName(CurrentUser.Instance.ID,
UserSettingsClassName + AnalyticReportId);
if (userSettings != null && !string.IsNullOrEmpty(userSettings.UserSettings))
{
var settings = userSettings.UserSettings.Split('$');
CurrentChartSeriesType = (ChartSeriesType)int.Parse(settings[0]);
startDate = DateTime.Parse(settings[1]);
endDate = DateTime.Parse(settings[2]).AddDays(1);
rdpStartDate.SelectedDate = startDate;
rdpEndDate.SelectedDate = endDate.Value.AddDays(-1);
var tab = rtsFilters.Tabs.FindTabByValue(settings[3]);
if (tab != null)
{
rtsFilters.SelectedIndex = tab.Index;
}
else
{
rtsFilters.SelectedIndex = -1;
}
if (tab != null)
{
var stDate = DateTime.Now;
var enDate = DateTime.Now;
DateTimeHelper.GetPeriod((DateTimePeriod)int.Parse(rtsFilters.SelectedTab.Value), ref stDate,
ref enDate);
startDate = stDate;
endDate = enDate;
rdpStartDate.SelectedDate = startDate;
rdpEndDate.SelectedDate = endDate.Value.AddDays(-1);
}
}
}
if (IsLoadFromSettings && reportUserSettings != null)
{
AxisToBuildChart = reportUserSettings.AxisToBuildID;
}
if (!startDate.HasValue && !endDate.HasValue)
{
var date = DateTime.Now;
var firstDayOfWeek = DateTimeHelper.GetFirstDayOfWeek(date);
startDate = firstDayOfWeek.Date;
endDate = DateTime.Now.AddDays(1).Date;
}
rcChart.DefaultType = CurrentChartSeriesType;
if (Width.HasValue)
{
rcChart.Width = new System.Web.UI.WebControls.Unit(((int)Width), UnitType.Pixel);
}
var dataManager = new BusinessLogicLayer.DataManager();
var analyticReport = dataManager.AnalyticReport.SelectById(CurrentUser.Instance.SiteID, (Guid)AnalyticReportId);
rdpStartDate.SelectedDate = startDate;
rdpEndDate.SelectedDate = endDate.Value.AddDays(-1);
rcChart.Clear();
rcChart.ChartTitle.TextBlock.Text = string.Empty;
rcChart.Series.Clear();
var analyticReportBase =
dataManager.AnalyticReportSystem.SelectByAnalyticReportId(analyticReport.ID).ToList();
var analyticReportYAxis = analyticReportBase.Where(o => o.AxisTypeID == (int)AxisType.YAxis &&
(o.tbl_AnalyticAxis.AxisRoleID ==
(int)AxisRole.IsLegend ||
o.tbl_AnalyticAxis.AxisRoleID ==
(int)AxisRole.IsFilterAndLegend)).ToList();
if (analyticReportYAxis.Any() && !string.IsNullOrEmpty(analyticReportYAxis[0].tbl_AnalyticAxis.DataSet))
{
tbl_AnalyticAxis defaultYAxis = null;
if (AxisToBuildChart.HasValue)
{
defaultYAxis = DataManager.AnalyticAxis.SelectById((Guid)AxisToBuildChart);
}
else
{
defaultYAxis = analyticReportYAxis.FirstOrDefault(o => o.IsDefault).tbl_AnalyticAxis;
}
var seriesValues = new List <string>();
if (IsLoadFromSettings && reportUserSettings != null &&
!string.IsNullOrEmpty(reportUserSettings.DataSetValues))
{
seriesValues = reportUserSettings.DataSetValues.Split('#').ToList();
}
tbl_AnalyticReportSystem valueAxis = null;
if (!string.IsNullOrEmpty(ColumnSystemNameValueToBuildChart) && AnalyticReportId.HasValue)
{
ValueToBuildChart = dataManager.AnalyticAxis.SelectByReportAndSystemName(AnalyticReportId.Value, ColumnSystemNameValueToBuildChart).ID;
}
if (ValueToBuildChart.HasValue && ValueToBuildChart.Value != Guid.Empty)
{
valueAxis = analyticReportBase.SingleOrDefault(o => o.tbl_AnalyticAxis.ID == ValueToBuildChart);
}
else
{
valueAxis =
analyticReportBase.FirstOrDefault(
x => x.tbl_AnalyticAxis.AxisRoleID == (int)AxisRole.Value && x.IsDefault);
if (valueAxis == null)
{
valueAxis =
analyticReportBase.FirstOrDefault(x => x.tbl_AnalyticAxis.AxisRoleID == (int)AxisRole.Value);
}
}
AnalyticSeriesList =
DataManager.AnalyticAxis.SelectSeriesByDataSet(defaultYAxis, CurrentUser.Instance.SiteID).Where(
o => seriesValues.Count == 0 || (seriesValues.Contains(o.Key))).
Select(o => new AnalyticSeries()
{
Title = o.Value,
SystemNameValue = valueAxis.tbl_AnalyticAxis.SystemName,
Dictionary = defaultYAxis.SystemName
}).ToList();
}
else
{
AnalyticSeriesList =
analyticReportYAxis.Select(o => new AnalyticSeries()
{
Title = o.tbl_AnalyticAxis.Title, SystemNameValue = o.tbl_AnalyticAxis.SystemName
}).ToList();
}
AnalyticXAxisList = new List <AnalyticAxis>();
AnalyticYAxisList = new List <AnalyticAxis>();
GetData(analyticReport, startDate, endDate);
//Определение оси X
AnalyticAxis xAxis = null;
tbl_AnalyticReportSystem analyticReportSystem = null;
if (XAxis.HasValue)
{
analyticReportSystem = analyticReportBase.FirstOrDefault(o => o.AnalyticAxisID == (Guid)XAxis);
}
else
{
analyticReportSystem = analyticReportBase.FirstOrDefault(o => (o.AxisTypeID == (int)AxisType.XAxis && o.IsDefault));
}
if (analyticReportSystem == null)
{
analyticReportSystem = analyticReportBase.FirstOrDefault(o => o.AxisTypeID == (int)AxisType.XAxis);
}
xAxis = new AnalyticAxis {
SystemName = dataManager.AnalyticAxis.SelectById(analyticReportSystem.AnalyticAxisID).SystemName
};
foreach (var analyticSeries in AnalyticSeriesList)
{
if (CurrentChartSeriesType != ChartSeriesType.Pie)
{
var chartSeries = new ChartSeries
{
Name = analyticSeries.Title,
Type = CurrentChartSeriesType,
DefaultLabelValue = string.Empty
};
chartSeries.Appearance.PointMark.Visible = true;
chartSeries.Appearance.PointMark.Dimensions.AutoSize = false;
chartSeries.Appearance.PointMark.Dimensions.Width = 4;
chartSeries.Appearance.PointMark.Dimensions.Height = 4;
chartSeries.Appearance.PointMark.FillStyle.FillType = FillType.Solid;
chartSeries.Appearance.PointMark.FillStyle.MainColor = Color.DodgerBlue;
rcChart.Series.Add(chartSeries);
}
AnalyticXAxisList.Add(xAxis);
AnalyticYAxisList.Add(new AnalyticAxis()
{
SystemName = analyticSeries.SystemNameValue
});
//Для того чтобы в легенде не отображать надписи для которых нет данных
AnalyticSeriesSum.Add(-1);
}
rcChart.PlotArea.XAxis.Items.Clear();
rcChart.PlotArea.YAxis.Items.Clear();
if (CurrentChartSeriesType != ChartSeriesType.Pie)
{
ProceedNonPieType(startDate.Value, endDate.Value, Data);
}
else
{
ProceedPieType(Data);
}
var newSeries = rcChart.Series.ToArray();
foreach (ChartSeries chartSeries in newSeries)
{
if (chartSeries.Items.Count == 0)
{
rcChart.Series.Remove(chartSeries);
}
}
if (Page != null &&
!Page.ClientScript.IsStartupScriptRegistered(string.Concat("UpdateTypesBtn", AnalyticReportId)))
{
ScriptManager.RegisterStartupScript(Page, typeof(Page),
string.Concat("UpdateTypesBtn", AnalyticReportId),
string.Format("UpdateTypesBtn($('#{0}'));",
this.FindControl(CurrentChartSeriesType.ToString()).
ClientID), true);
}
}
