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