private static string BuildChartData(LoginUser loginUser, DataTable table, SummaryReport summaryReport) { DataResult[] result = new DataResult[table.Columns.Count]; for (int i = 0; i < table.Columns.Count; i++) { result[i] = new DataResult(); result[i].name = table.Columns[i].ColumnName; result[i].data = new object[table.Rows.Count]; for (int j = 0; j < table.Rows.Count; j++) { object data = table.Rows[j][i]; result[i].data[j] = data == null || data == DBNull.Value ? null : data; } if (i < summaryReport.Fields.Descriptive.Length) { result[i].fieldType = summaryReport.Fields.Descriptive[i].Field.FieldType; result[i].format = summaryReport.Fields.Descriptive[i].Value1; if (result[i].fieldType == "datetime") { FixChartDateNames(loginUser, result[i].data, summaryReport.Fields.Descriptive[i].Value1); } } } return(JsonConvert.SerializeObject(result)); }
public static string GetHubChartData(LoginUser loginUser, Report report) { SummaryReport summaryReport = JsonConvert.DeserializeObject <SummaryReport>(report.ReportDef); DataTable table = GetSummaryData(loginUser, summaryReport, true, report); return(BuildChartData(loginUser, table, summaryReport)); }
private static DataTable GetSummaryData(LoginUser loginUser, SummaryReport summaryReport, bool useDefaultOrderBy, Report report = null) { SqlCommand command = new SqlCommand(); SummaryReportSql.GetSummaryCommand(loginUser, command, summaryReport, false, false, useDefaultOrderBy); BaseCollection.FixCommandParameters(command); if (report != null) { report.LastSqlExecuted = DataUtils.GetCommandTextSql(command); report.Collection.Save(); } DataTable table = new DataTable(); using (SqlConnection connection = new SqlConnection(loginUser.ConnectionString)) { connection.Open(); command.Connection = connection; using (SqlDataAdapter adapter = new SqlDataAdapter(command)) { try { adapter.Fill(table); } catch (Exception ex) { ExceptionLogs.LogException(loginUser, ex, "GetSummaryData"); throw; } } connection.Close(); } return(table); }
/// <summary> Summary Report </summary> public ReportTicketsViewTempTable(LoginUser loginUser, SummaryReport summaryReport) : this(loginUser) { // SELECT fields foreach (ReportSummaryDescriptiveField field in summaryReport.Fields.Descriptive) { Add(field.Field.FieldID); } foreach (ReportSummaryCalculatedField field in summaryReport.Fields.Calculated) { Add(field.Field.FieldID); } // filter fields (WHERE, ORDER BY...) foreach (ReportFilter filter in summaryReport.Filters) { AddFields(filter); } if (_fields.Count > 0) // include TicketID and OrganizationID { Add(EField.TicketID); Add(EField.OrganizationID); } }
public static string GetChartData(LoginUser loginUser, string summaryReportFields) { SummaryReport summaryReport = JsonConvert.DeserializeObject <SummaryReport>(summaryReportFields); DataTable table = GetSummaryData(loginUser, summaryReport, true); return(BuildChartData(loginUser, table, summaryReport)); }
public static string GetChartReportData(LoginUser loginUser, int reportID) { Report report = Reports.GetReport(loginUser, reportID, loginUser.UserID); Reports.UpdateReportView(loginUser, report.ReportID); SummaryReport summaryReport = JsonConvert.DeserializeObject <SummaryReport>(report.ReportDef); DataTable table = GetSummaryData(loginUser, summaryReport, true, report); return(BuildChartData(loginUser, table, summaryReport)); }
private void AddReportTicketsViewTempTable(SqlCommand command) { if (!_summaryReportSql.IsOrganizationID || // not Parent organizationID report (_report.ReportDefType == ReportType.Custom) || // not a custom report !ReportTicketsViewTempTable.Enable) { return; } _summaryReport = JsonConvert.DeserializeObject <SummaryReport>(_report.ReportDef); _reportTicketsView = new ReportTicketsViewTempTable(_report.Collection.LoginUser, _summaryReport); string tempTable = _reportTicketsView.ToSql(); if (!String.IsNullOrEmpty(tempTable)) { command.CommandText = (tempTable + command.CommandText).Replace("ReportTicketsView", "#ReportTicketsView"); } }
private void GetCommand(SqlCommand command, bool inlcudeHiddenFields = true, bool isSchemaOnly = false, bool useUserFilter = true, string sortField = null, string sortDir = null) { _report.MigrateToNewReport(); _summaryReport = JsonConvert.DeserializeObject <SummaryReport>(_report.ReportDef); command.CommandType = CommandType.Text; command.CommandTimeout = SystemSettings.GetReportTimeout(); switch (_report.ReportDefType) { case ReportType.Chart: _summaryReportSql.GetSummarySql(command, _summaryReport, isSchemaOnly, null, false, true); break; case ReportType.Summary: _summaryReportSql.GetSummarySql(command, _summaryReport, isSchemaOnly, _report.ReportID, useUserFilter, false); break; default: break; } Report.AddCommandParametersForExport(command, _report.Collection.LoginUser); command.CommandText = $" /* ReportID: {_report.ReportID.ToString()} OrganizationID: {_report.OrganizationID.ToString()} */ " + command.CommandText; }
public static void GetSummarySql(LoginUser loginUser, SqlCommand command, SummaryReport summaryReport, bool isSchemaOnly, int?reportID, bool useUserFilter, bool useDefaultOrderBy) { SummaryReportSql summaryReportSql = new SummaryReportSql(loginUser); summaryReportSql.GetSummarySql(command, summaryReport, isSchemaOnly, reportID, useUserFilter, useDefaultOrderBy); }
private static List <CalculatedClauseItem> GetSummaryCalcFields(LoginUser loginUser, SummaryReport summaryReport) { List <CalculatedClauseItem> result = new List <CalculatedClauseItem>(); ReportSubcategory sub = ReportSubcategories.GetReportSubcategory(loginUser, summaryReport.Subcategory); ReportTables tables = new ReportTables(loginUser); tables.LoadAll(); ReportTableFields tableFields = new ReportTableFields(loginUser); tableFields.LoadAll(false); TimeSpan offset = loginUser.Offset; foreach (ReportSummaryCalculatedField field in summaryReport.Fields.Calculated) { StringBuilder builder = new StringBuilder(); if (field.Field.IsCustom) { CustomField customField = (CustomField)CustomFields.GetCustomField(loginUser, field.Field.FieldID); if (customField == null) { continue; } string fieldName = DataUtils.GetReportPrimaryKeyFieldName(customField.RefType); if (fieldName != "") { fieldName = DataUtils.GetCustomFieldColumn(loginUser, customField, fieldName, true, false); if (customField.FieldType == CustomFieldType.DateTime) { fieldName = string.Format("CAST(SWITCHOFFSET(TODATETIMEOFFSET({0}, '+00:00'), '{1}{2:D2}:{3:D2}') AS DATETIME)", fieldName, offset < TimeSpan.Zero ? "-" : "+", Math.Abs(offset.Hours), Math.Abs(offset.Minutes)); } result.Add(GetCalcItem(fieldName, customField.Name, field)); } } else { ReportTableField tableField = tableFields.FindByReportTableFieldID(field.Field.FieldID); ReportTable table = tables.FindByReportTableID(tableField.ReportTableID); string fieldName = table.TableName + "." + tableField.FieldName; if (tableField.DataType.Trim().ToLower() == "datetime") { fieldName = string.Format("CAST(SWITCHOFFSET(TODATETIMEOFFSET({0}, '+00:00'), '{1}{2:D2}:{3:D2}') AS DATETIME)", fieldName, offset < TimeSpan.Zero ? "-" : "+", Math.Abs(offset.Hours), Math.Abs(offset.Minutes)); } result.Add(GetCalcItem(fieldName, tableField.Alias, field)); } } return(result); }
private static List <DescriptiveClauseItem> GetSummaryDescFields(LoginUser loginUser, SummaryReport summaryReport) { List <DescriptiveClauseItem> result = new List <DescriptiveClauseItem>(); ReportSubcategory sub = ReportSubcategories.GetReportSubcategory(loginUser, summaryReport.Subcategory); ReportTables tables = new ReportTables(loginUser); tables.LoadAll(); ReportTableFields tableFields = new ReportTableFields(loginUser); tableFields.LoadAll(); TimeSpan offset = loginUser.Offset; TicketTypes ticketTypes = new TicketTypes(loginUser); ticketTypes.LoadByOrganizationID(loginUser.OrganizationID); foreach (ReportSummaryDescriptiveField field in summaryReport.Fields.Descriptive) { if (field.Field.IsCustom) { CustomField customField = (CustomField)CustomFields.GetCustomField(loginUser, field.Field.FieldID); if (customField == null) { continue; } string fieldName = DataUtils.GetReportPrimaryKeyFieldName(customField.RefType); if (fieldName != "") { fieldName = DataUtils.GetCustomFieldColumn(loginUser, customField, fieldName, true, false); if (customField.FieldType == CustomFieldType.DateTime) { fieldName = string.Format("CAST(SWITCHOFFSET(TODATETIMEOFFSET({0}, '+00:00'), '{1}{2:D2}:{3:D2}') AS DATETIME)", fieldName, offset < TimeSpan.Zero ? "-" : "+", Math.Abs(offset.Hours), Math.Abs(offset.Minutes)); fieldName = GetDateGroupField(fieldName, field.Value1); } string alias = customField.Name; if (customField.AuxID > 0 && customField.RefType == ReferenceType.Tickets) { TicketType ticketType = ticketTypes.FindByTicketTypeID(customField.AuxID); if (ticketType != null && ticketType.OrganizationID == customField.OrganizationID) { alias = string.Format("{1} ({2})", fieldName, customField.Name, ticketType.Name); } } result.Add(new DescriptiveClauseItem(fieldName, alias)); } } else { ReportTableField tableField = tableFields.FindByReportTableFieldID(field.Field.FieldID); ReportTable table = tables.FindByReportTableID(tableField.ReportTableID); string fieldName = table.TableName + "." + tableField.FieldName; if (tableField.DataType.Trim().ToLower() == "datetime") { fieldName = string.Format("CAST(SWITCHOFFSET(TODATETIMEOFFSET({0}, '+00:00'), '{1}{2:D2}:{3:D2}') AS DATETIME)", fieldName, offset < TimeSpan.Zero ? "-" : "+", Math.Abs(offset.Hours), Math.Abs(offset.Minutes)); fieldName = GetDateGroupField(fieldName, field.Value1); } result.Add(new DescriptiveClauseItem(fieldName, tableField.Alias)); } } return(result); }
public static void GetSummaryCommand(LoginUser loginUser, SqlCommand command, SummaryReport summaryReport, bool isSchemaOnly, bool useUserFilter, bool useDefaultOrderBy) { command.CommandType = CommandType.Text; GetSummarySql(loginUser, command, summaryReport, isSchemaOnly, null, useUserFilter, useDefaultOrderBy); Report.AddCommandParameters(command, loginUser); }
public void GetSummarySql(SqlCommand command, SummaryReport summaryReport, bool isSchemaOnly, int?reportID, bool useUserFilter, bool useDefaultOrderBy) { LoginUser loginUser = _userRights._loginUser; StringBuilder builder = new StringBuilder(); ReportSubcategory sub = ReportSubcategories.GetReportSubcategory(loginUser, summaryReport.Subcategory); ReportTables tables = new ReportTables(loginUser); tables.LoadAll(); List <DescriptiveClauseItem> descFields = GetSummaryDescFields(loginUser, summaryReport); List <CalculatedClauseItem> calcFields = GetSummaryCalcFields(loginUser, summaryReport); builder.Append("WITH x AS ("); bool flag = true; foreach (DescriptiveClauseItem descField in descFields) { if (flag) { builder.Append(string.Format(" SELECT {0} AS [{1}]", descField.Field, descField.Alias)); } else { builder.Append(string.Format(", {0} AS [{1}]", descField.Field, descField.Alias)); } flag = false; } foreach (CalculatedClauseItem calcField in calcFields) { builder.Append(string.Format(", {0} AS [{1}]", calcField.Field, calcField.Alias)); } // from + where clause builder.Append(" " + sub.BaseQuery); ReportTable mainTable = tables.FindByReportTableID(sub.ReportCategoryTableID); _organizationIDFieldName = mainTable.OrganizationIDFieldName; builder.Append(" WHERE (" + mainTable.TableName + "." + mainTable.OrganizationIDFieldName + " = @OrganizationID)"); //add user rights where needed _userRights.UseTicketRights((int)summaryReport.Subcategory, tables, command, builder); if (isSchemaOnly) { builder.Append(" AND (0=1)"); } // filters if (!isSchemaOnly) { Report.GetWhereClause(loginUser, command, builder, summaryReport.Filters); if (useUserFilter == true && reportID != null) { Report report = Reports.GetReport(loginUser, (int)reportID, loginUser.UserID); if (report != null && report.Row["Settings"] != DBNull.Value) { try { UserTabularSettings userFilters = JsonConvert.DeserializeObject <UserTabularSettings>((string)report.Row["Settings"]); if (userFilters != null) { Report.GetWhereClause(loginUser, command, builder, userFilters.Filters); } } catch (Exception ex) { ExceptionLogs.LogException(loginUser, ex, "Summary SQL - User filters"); } } } } flag = true; builder.Append(")"); // end with flag = true; foreach (DescriptiveClauseItem descField in descFields) { if (flag) { builder.Append(string.Format(" SELECT [{0}]", descField.Alias)); } else { builder.Append(string.Format(", [{0}]", descField.Alias)); } flag = false; } foreach (CalculatedClauseItem calcField in calcFields) { builder.Append(string.Format(", {0} AS [{1}]", calcField.AggField, calcField.Alias)); } builder.Append(" FROM x "); // group by flag = true; foreach (DescriptiveClauseItem descField in descFields) { if (flag) { builder.Append(string.Format(" GROUP BY [{0}]", descField.Alias)); } else { builder.Append(string.Format(", [{0}]", descField.Alias)); } flag = false; } // having flag = true; foreach (CalculatedClauseItem calcField in calcFields) { if (calcField.Comparator == null) { continue; } if (flag) { builder.Append(string.Format(" HAVING {0}", calcField.Comparator)); } else { builder.Append(string.Format(" AND {0}", calcField.Comparator)); } flag = false; } if (useDefaultOrderBy) { // order by /* flag = true; * foreach (DescriptiveClauseItem descField in descFields) * { * if (flag) * builder.Append(string.Format(" ORDER BY [{0}]", descField.Alias)); * else * builder.Append(string.Format(", [{0}]", descField.Alias)); * * flag = false; * }*/ // order by for (int i = descFields.Count - 1; i > -1; i--) { if (i == descFields.Count - 1) { builder.Append(string.Format(" ORDER BY [{0}]", descFields[i].Alias)); } else { builder.Append(string.Format(", [{0}]", descFields[i].Alias)); } } } command.CommandText = builder.ToString(); }