public void GetTabularSql(SqlCommand command, TabularReport tabularReport, bool inlcudeHiddenFields, bool isSchemaOnly, int?reportID, bool useUserFilter, string sortField = null, string sortDir = null) { LoginUser loginUser = _userRights._loginUser; StringBuilder builder = new StringBuilder(); GetTabluarSelectClause(command, builder, tabularReport, inlcudeHiddenFields, isSchemaOnly, sortField, sortDir); if (isSchemaOnly) { command.CommandText = builder.ToString(); } else { string primaryTableKeyFieldName = null; if (tabularReport.Subcategory == 70) { primaryTableKeyFieldName = "UserTicketsView.TicketID"; } Report.GetWhereClause(loginUser, command, builder, tabularReport.Filters, primaryTableKeyFieldName); if (useUserFilter && 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, "Tabular SQL - User filters"); } } } command.CommandText = builder.ToString(); } }
/// <summary> Tabular Report </summary> public ReportTicketsViewTempTable(LoginUser loginUser, TabularReport tabularReport) : this(loginUser) { // SELECT foreach (ReportSelectedField field in tabularReport.Fields) { Add(field.FieldID); } // filter fields (WHERE, ORDER BY...) foreach (ReportFilter filter in tabularReport.Filters) { AddFields(filter); } if (_fields.Count > 0) // include TicketID and OrganizationID { Add(EField.TicketID); Add(EField.OrganizationID); } }
private void AddReportTicketsViewTempTable(SqlCommand command) { if (!_tabularReportSql.IsOrganizationID || // not Parent organizationID report (_report.ReportDefType == ReportType.Custom)) // not a custom report { return; } if (!ReportTicketsViewTempTable.Enable) { return; } _tabularReport = JsonConvert.DeserializeObject <TabularReport>(_report.ReportDef); _reportTicketsView = new ReportTicketsViewTempTable(_report.Collection.LoginUser, _tabularReport); string tempTable = _reportTicketsView.ToSql(); if (!String.IsNullOrEmpty(tempTable)) { command.CommandText = (tempTable + command.CommandText).Replace("ReportTicketsView", "#ReportTicketsView"); } }
public static DataTable GetPortalTicketsTablePage(LoginUser loginUser, int from, int to, string sortField, TabularReport tabReport) { from++; to++; SqlCommand command = new SqlCommand(); GetPortalTicketsSQL(loginUser, command, tabReport, true, false); command.Parameters.AddWithValue("@From", from); command.Parameters.AddWithValue("@To", to); Report.AddCommandParameters(command, loginUser); DataTable table = new DataTable(); using (SqlConnection connection = new SqlConnection(loginUser.ConnectionString)) { connection.Open(); SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted); command.Connection = connection; command.Transaction = transaction; try { using (SqlDataAdapter adapter = new SqlDataAdapter(command)) { adapter.Fill(table); } transaction.Commit(); table = DataUtils.DecodeDataTable(table); table = DataUtils.StripHtmlDataTable(table); } catch (Exception ex) { transaction.Rollback(); ExceptionLogs.LogException(loginUser, ex, "Portal Tickets Data", DataUtils.GetCommandTextSql(command)); throw; } connection.Close(); } return(table); }
private void GetTabluarSelectClause(SqlCommand command, StringBuilder builder, TabularReport tabularReport, bool includeHiddenFields, bool isSchemaOnly, string sortField = null, string sortDir = null) { LoginUser loginUser = _userRights._loginUser; ReportSubcategory sub = ReportSubcategories.GetReportSubcategory(loginUser, tabularReport.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); string sortClause = ""; foreach (ReportSelectedField field in tabularReport.Fields) { if (field.IsCustom) { CustomField customField = (CustomField)CustomFields.GetCustomField(loginUser, field.FieldID); if (customField == null) { continue; } string fieldName = DataUtils.GetReportPrimaryKeyFieldName(customField.RefType); if (fieldName != "") { //handle the ticket views custom fields if (tabularReport.Subcategory == 70) { fieldName = "UserTicketsView.TicketID"; } else if (tabularReport.Subcategory == 74) { fieldName = "TicketsView.TicketID"; } fieldName = DataUtils.GetCustomFieldColumn(loginUser, customField, fieldName, true, false); string colName = fieldName; 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)); } else if (customField.FieldType == CustomFieldType.Boolean) { fieldName = string.Format("(SELECT ISNULL(({0}),0))", fieldName); } if (!string.IsNullOrWhiteSpace(sortField) && colName == sortField) { sortClause = fieldName; } builder.Append(builder.Length < 1 ? "SELECT " : ", "); string displayName = customField.Name; if (customField.AuxID > 0 && customField.RefType == ReferenceType.Tickets) { TicketType ticketType = ticketTypes.FindByTicketTypeID(customField.AuxID); if (ticketType != null && ticketType.OrganizationID == customField.OrganizationID) { displayName = $"{customField.Name} ({ticketType.Name})"; } } builder.Append($"{fieldName} AS [{displayName}]"); if (!string.IsNullOrWhiteSpace(sortField) && displayName == sortField) { sortClause = fieldName; } } } else { ReportTableField tableField = tableFields.FindByReportTableFieldID(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)); } if (!string.IsNullOrWhiteSpace(sortField) && tableField.Alias == sortField) { sortClause = fieldName; } if (builder.Length < 1) { builder.Append("SELECT " + fieldName + " AS [" + tableField.Alias + "]"); } else { builder.Append(", " + fieldName + " AS [" + tableField.Alias + "]"); } } } if (!string.IsNullOrWhiteSpace(sortClause)) { builder.Append($", ROW_NUMBER() OVER (ORDER BY {sortClause} {sortDir}) AS [RowNum]"); } if (includeHiddenFields) { ReportTable hiddenTable = tables.FindByReportTableID(sub.ReportCategoryTableID); if (!string.IsNullOrWhiteSpace(hiddenTable.LookupKeyFieldName)) { builder.Append(string.Format(", {1}.{0} AS [hidden{0}]", hiddenTable.LookupKeyFieldName, hiddenTable.TableName)); } if (sub.ReportTableID != null) { hiddenTable = tables.FindByReportTableID((int)sub.ReportTableID); if (!string.IsNullOrWhiteSpace(hiddenTable.LookupKeyFieldName)) { builder.Append(string.Format(", {1}.{0} AS [hidden{0}]", hiddenTable.LookupKeyFieldName, hiddenTable.TableName)); } } if (tabularReport.Subcategory == 70) { string dueDateField = hiddenTable.TableName + ".DueDate"; dueDateField = string.Format("CAST(SWITCHOFFSET(TODATETIMEOFFSET({0}, '+00:00'), '{1}{2:D2}:{3:D2}') AS DATETIME)", dueDateField, offset < TimeSpan.Zero ? "-" : "+", Math.Abs(offset.Hours), Math.Abs(offset.Minutes)); builder.Append(string.Format(", {0} AS [hiddenDueDate]", dueDateField)); string dateModifiedField = hiddenTable.TableName + ".DateModified"; dateModifiedField = string.Format("CAST(SWITCHOFFSET(TODATETIMEOFFSET({0}, '+00:00'), '{1}{2:D2}:{3:D2}') AS DATETIME)", dateModifiedField, offset < TimeSpan.Zero ? "-" : "+", Math.Abs(offset.Hours), Math.Abs(offset.Minutes)); builder.Append(string.Format(", {0} AS [hiddenDateModified]", dateModifiedField)); builder.Append(string.Format(", {1}.{0} AS [hidden{0}]", "SlaWarningTime", hiddenTable.TableName)); builder.Append(string.Format(", {1}.{0} AS [hidden{0}]", "SlaViolationTime", hiddenTable.TableName)); builder.Append(string.Format(", {1}.{0} AS [hidden{0}]", "IsRead", hiddenTable.TableName)); builder.Append(string.Format(", {1}.{0} AS [hidden{0}]", "IsClosed", hiddenTable.TableName)); builder.Append(string.Format(", {1}.{0} AS [hidden{0}]", "TicketTypeID", hiddenTable.TableName)); builder.Append(string.Format(", {1}.{0} AS [hidden{0}]", "UserID", hiddenTable.TableName)); builder.Append(string.Format(", {1}.{0} AS [hidden{0}]", "SeverityPosition", hiddenTable.TableName)); builder.Append(string.Format(", {1}.{0} AS [hidden{0}]", "StatusPosition", hiddenTable.TableName)); } } builder.Append(" " + sub.BaseQuery); ReportTable mainTable = tables.FindByReportTableID(sub.ReportCategoryTableID); _organizationIDFieldName = mainTable.OrganizationIDFieldName; builder.Append(" WHERE (" + mainTable.TableName + "." + mainTable.OrganizationIDFieldName + " = @OrganizationID)"); if (tabularReport.Subcategory == 70) { builder.Append(" AND (" + mainTable.TableName + ".ViewerID = @UserID)"); } _userRights.UseTicketRights((int)tabularReport.Subcategory, tables, command, builder); if (isSchemaOnly) { builder.Append(" AND (0=1)"); } }
public static void GetPortalTicketsSQL(LoginUser loginUser, SqlCommand command, TabularReport tabularReport, bool inlcudeHiddenFields, bool isSchemaOnly) { StringBuilder builder = new StringBuilder(); TabularReportSql tabularReportSql = new TabularReportSql(loginUser); tabularReportSql.GetTabluarSelectClause(command, builder, tabularReport, inlcudeHiddenFields, isSchemaOnly); if (isSchemaOnly) { command.CommandText = builder.ToString(); } else { string primaryTableKeyFieldName = null; if (tabularReport.Subcategory == 70) { primaryTableKeyFieldName = "UserTicketsView.TicketID"; } else if (tabularReport.Subcategory == 74) { primaryTableKeyFieldName = "TicketsView.TicketID"; } Report.GetWhereClause(loginUser, command, builder, tabularReport.Filters, primaryTableKeyFieldName); command.CommandText = builder.ToString(); } }