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