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 ReportDataType GetDataType(string value) { bool isCustomField = IsValueCustomField(value); int id = GetValueFieldID(value); ReportDataType dataType = ReportDataType.String; if (isCustomField) { CustomField field = (CustomField)CustomFields.GetCustomField(UserSession.LoginUser, id); switch (field.FieldType) { case CustomFieldType.Date: case CustomFieldType.Time: case CustomFieldType.DateTime: dataType = ReportDataType.DateTime; break; case CustomFieldType.Boolean: dataType = ReportDataType.Boolean; break; case CustomFieldType.Number: dataType = ReportDataType.Int; break; default: break; } } else { ReportTableField field = (ReportTableField)ReportTableFields.GetReportTableField(UserSession.LoginUser, id); switch (field.DataType) { case "bit": dataType = ReportDataType.Boolean; break; case "datetime": dataType = ReportDataType.DateTime; break; case "int": dataType = ReportDataType.Int; break; case "float": dataType = ReportDataType.Float; break; default: break; } } return(dataType); }
public static string GetReportTableField(RestCommand command, int reportTableFieldID) { ReportTableField reportTableField = ReportTableFields.GetReportTableField(command.LoginUser, reportTableFieldID); if (reportTableField.OrganizationID != command.Organization.OrganizationID) { throw new RestException(HttpStatusCode.Unauthorized); } return(reportTableField.GetXml("ReportTableField", true)); }
public static Dictionary <int, string> GetLookupValues(LoginUser loginUser, int reportTableFieldID, string term, int maxRows) { Dictionary <int, string> result = new Dictionary <int, string>(); ReportTableField field = ReportTableFields.GetReportTableField(loginUser, reportTableFieldID); if (field == null || field.LookupTableID == null) { return(null); } ReportTable table = ReportTables.GetReportTable(loginUser, (int)field.LookupTableID); SqlCommand command = new SqlCommand(); string[] orgs = table.OrganizationIDFieldName.Split(','); StringBuilder orgFields = new StringBuilder("("); foreach (String s in orgs) { if (orgFields.Length > 1) { orgFields.Append(" OR " + s + " = @OrganizationID"); } else { orgFields.Append(s + " = @OrganizationID"); } } orgFields.Append(")"); string text = "SELECT TOP {0} {1} AS Label, {2} AS ID FROM {3} WHERE {4} AND {1} LIKE '%' + @Term + '%' ORDER BY {5}"; command.CommandText = string.Format(text, maxRows.ToString(), table.LookupDisplayClause, table.LookupKeyFieldName, table.TableName, orgFields.ToString(), table.LookupOrderBy); command.CommandType = CommandType.Text; command.Parameters.AddWithValue("@Term", term); command.Parameters.AddWithValue("@OrganizationID", loginUser.OrganizationID); DataTable dataTable = SqlExecutor.ExecuteQuery(loginUser, command); if (field.LookupTableID == 11 || field.LookupTableID == 17) { result.Add(-2, "The Report Viewer"); } //result.Add(-1, "Unassigned"); foreach (DataRow row in dataTable.Rows) { result.Add((int)row[1], row[0].ToString()); } return(result); }
public AutoFieldItem(ReportTableField field) { this.FieldID = field.ReportTableFieldID; this.RefType = ReferenceType.Tickets; this.AuxID = null; this.IsCustom = false; this.ListValues = null; this.FieldName = field.FieldName; this.Alias = field.Alias; this.DataType = field.DataType; this.Size = field.Size; this.TableID = field.ReportTableID; this.IsVisible = field.IsVisible; this.Description = field.Description; this.LookupTableID = field.LookupTableID; }
public ReportFieldItem(string table, bool isPrimary, ReportTableField field) { this.IsPrimary = isPrimary; this.ID = field.ReportTableFieldID; this.Name = field.Alias; this.LookupTableID = field.LookupTableID; this.IsCustom = false; this.Table = table; switch (field.DataType) { case "datetime": this.DataType = "datetime"; break; case "bit": this.DataType = "bool"; break; case "float": this.DataType = "number"; break; case "int": this.DataType = "number"; break; default: this.DataType = "text"; break; } }
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 AutomationData GetData() { AutomationData result = new AutomationData(); TicketAutomationPossibleActions actions = new TicketAutomationPossibleActions(UserSession.LoginUser); actions.LoadActive(); result.Actions = actions.GetTicketAutomationPossibleActionProxies(); List <AutoFieldItem> fieldItems = new List <AutoFieldItem>(); ReportTableFields fields = new ReportTableFields(TSAuthentication.GetLoginUser()); fields.LoadByReportTableID(10); CustomFields customs = new CustomFields(fields.LoginUser); customs.LoadByReferenceType(TSAuthentication.OrganizationID, ReferenceType.Tickets); CustomFields orgfields = new CustomFields(fields.LoginUser); orgfields.LoadByReferenceType(TSAuthentication.OrganizationID, ReferenceType.Organizations); List <string> orgCustomFields = new List <string>(); foreach (CustomField c in orgfields) { orgCustomFields.Add(c.Name + ":" + c.CustomFieldID); } TicketTypes ticketTypes = new TicketTypes(fields.LoginUser); ticketTypes.LoadAllPositions(TSAuthentication.OrganizationID); foreach (ReportTableField field in fields) { fieldItems.Add(new AutoFieldItem(field)); } List <AutoFieldItem> customFieldsItems = new List <AutoFieldItem>(); foreach (CustomField custom in customs) { TicketType ticketType = ticketTypes.FindByTicketTypeID(custom.AuxID); if (ticketType == null) { fieldItems.Add(new AutoFieldItem(custom)); customFieldsItems.Add(new AutoFieldItem(custom)); } else { fieldItems.Add(new AutoFieldItem(custom, string.Format("{0} ({1})", custom.Name, ticketType.Name))); customFieldsItems.Add(new AutoFieldItem(custom, string.Format("{0} ({1})", custom.Name, ticketType.Name))); } } result.CustomFields = customFieldsItems.ToArray(); ReportTableField actionsViewDescription = ReportTableFields.GetReportTableField(fields.LoginUser, 6); actionsViewDescription.Alias = "Action Text"; fieldItems.Add(new AutoFieldItem(actionsViewDescription)); ReportTableField actionsViewName = ReportTableFields.GetReportTableField(fields.LoginUser, 5); fieldItems.Add(new AutoFieldItem(actionsViewName)); ReportTableField actionsViewType = ReportTableFields.GetReportTableField(fields.LoginUser, 18); fieldItems.Add(new AutoFieldItem(actionsViewType)); AutoFieldItem afiDayOfWeekCreated = new AutoFieldItem(); afiDayOfWeekCreated.Alias = "Day of Week Created"; afiDayOfWeekCreated.DataType = "list"; afiDayOfWeekCreated.FieldID = 101001; afiDayOfWeekCreated.FieldName = "Day of Week Created"; afiDayOfWeekCreated.IsCustom = false; afiDayOfWeekCreated.IsVisible = true; afiDayOfWeekCreated.ListValues = new string[] { "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday" }; afiDayOfWeekCreated.LookupTableID = null; afiDayOfWeekCreated.Size = 0; afiDayOfWeekCreated.Description = ""; afiDayOfWeekCreated.TableID = -2; afiDayOfWeekCreated.RefType = ReferenceType.Tickets; afiDayOfWeekCreated.AuxID = null; afiDayOfWeekCreated.OtherTrigger = "ticketsview.dayofweekcreated"; fieldItems.Add(afiDayOfWeekCreated); AutoFieldItem afiHourOfDayCreated = new AutoFieldItem(); afiHourOfDayCreated.Alias = "Hour of Day Created"; afiHourOfDayCreated.DataType = "text"; afiHourOfDayCreated.FieldID = 101002; afiHourOfDayCreated.FieldName = "Hour of Day Created"; afiHourOfDayCreated.IsCustom = false; afiHourOfDayCreated.IsVisible = true; afiHourOfDayCreated.ListValues = null; afiHourOfDayCreated.LookupTableID = null; afiHourOfDayCreated.Size = 0; afiHourOfDayCreated.Description = ""; afiHourOfDayCreated.TableID = -2; afiHourOfDayCreated.RefType = ReferenceType.Tickets; afiHourOfDayCreated.AuxID = null; afiHourOfDayCreated.OtherTrigger = "ticketsview.hourofdaycreated"; fieldItems.Add(afiHourOfDayCreated); AutoFieldItem afiMinSinceLastAction = new AutoFieldItem(); afiMinSinceLastAction.Alias = "Minutes Since last Action Added"; afiMinSinceLastAction.DataType = "text"; afiMinSinceLastAction.FieldID = 101003; afiMinSinceLastAction.FieldName = "Minutes Since last Action Added"; afiMinSinceLastAction.IsCustom = false; afiMinSinceLastAction.IsVisible = true; afiMinSinceLastAction.ListValues = null; afiMinSinceLastAction.LookupTableID = null; afiMinSinceLastAction.Size = 0; afiMinSinceLastAction.Description = ""; afiMinSinceLastAction.TableID = -2; afiMinSinceLastAction.RefType = ReferenceType.Tickets; afiMinSinceLastAction.AuxID = null; afiMinSinceLastAction.OtherTrigger = "ticketsview.minutessincelastactionadded"; fieldItems.Add(afiMinSinceLastAction); AutoFieldItem afiHoursSinceAction = new AutoFieldItem(); afiHoursSinceAction.Alias = "Hours Since Last Action Added"; afiHoursSinceAction.DataType = "text"; afiHoursSinceAction.FieldID = 101004; afiHoursSinceAction.FieldName = "Hours Since Last Action Added"; afiHoursSinceAction.IsCustom = false; afiHoursSinceAction.IsVisible = true; afiHoursSinceAction.ListValues = null; afiHoursSinceAction.LookupTableID = null; afiHoursSinceAction.Size = 0; afiHoursSinceAction.Description = ""; afiHoursSinceAction.TableID = -2; afiHoursSinceAction.RefType = ReferenceType.Tickets; afiHoursSinceAction.AuxID = null; afiHoursSinceAction.OtherTrigger = "ticketsview.hourssincelastactionadded"; fieldItems.Add(afiHoursSinceAction); AutoFieldItem afiCurrentDayOfWeek = new AutoFieldItem(); afiCurrentDayOfWeek.Alias = "Current Day of Week"; afiCurrentDayOfWeek.DataType = "list"; afiCurrentDayOfWeek.FieldID = 101005; afiCurrentDayOfWeek.FieldName = "Current Day of Week"; afiCurrentDayOfWeek.IsCustom = false; afiCurrentDayOfWeek.IsVisible = true; afiCurrentDayOfWeek.ListValues = new string[] { "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday" }; afiCurrentDayOfWeek.LookupTableID = null; afiCurrentDayOfWeek.Size = 0; afiCurrentDayOfWeek.Description = ""; afiCurrentDayOfWeek.TableID = -2; afiCurrentDayOfWeek.RefType = ReferenceType.Tickets; afiCurrentDayOfWeek.AuxID = null; afiCurrentDayOfWeek.OtherTrigger = "ticketsview.currentdayofweek"; fieldItems.Add(afiCurrentDayOfWeek); AutoFieldItem afiCurrentHourOfDay = new AutoFieldItem(); afiCurrentHourOfDay.Alias = "Current Hour of Day"; afiCurrentHourOfDay.DataType = "text"; afiCurrentHourOfDay.FieldID = 101006; afiCurrentHourOfDay.FieldName = "Current Hour of Day"; afiCurrentHourOfDay.IsCustom = false; afiCurrentHourOfDay.IsVisible = true; afiCurrentHourOfDay.ListValues = null; afiCurrentHourOfDay.LookupTableID = null; afiCurrentHourOfDay.Size = 0; afiCurrentHourOfDay.Description = ""; afiCurrentHourOfDay.TableID = -2; afiCurrentHourOfDay.RefType = ReferenceType.Tickets; afiCurrentHourOfDay.AuxID = null; afiCurrentHourOfDay.OtherTrigger = "ticketsview.currenthourofday"; fieldItems.Add(afiCurrentHourOfDay); AutoFieldItem afiAssignedUserIsAvailable = new AutoFieldItem(); afiAssignedUserIsAvailable.Alias = "Assigned User Is Available"; afiAssignedUserIsAvailable.DataType = "bit"; afiAssignedUserIsAvailable.FieldID = 101007; afiAssignedUserIsAvailable.FieldName = "Assigned User Is Available"; afiAssignedUserIsAvailable.IsCustom = false; afiAssignedUserIsAvailable.IsVisible = true; afiAssignedUserIsAvailable.ListValues = null; afiAssignedUserIsAvailable.LookupTableID = null; afiAssignedUserIsAvailable.Size = 0; afiAssignedUserIsAvailable.Description = ""; afiAssignedUserIsAvailable.TableID = -2; afiAssignedUserIsAvailable.RefType = ReferenceType.Tickets; afiAssignedUserIsAvailable.AuxID = null; afiAssignedUserIsAvailable.OtherTrigger = "ticketsview.assigneduseravailable"; fieldItems.Add(afiAssignedUserIsAvailable); AutoFieldItem afiAssignedUserIsBusy = new AutoFieldItem(); afiAssignedUserIsBusy.Alias = "Assigned User Is Busy"; afiAssignedUserIsBusy.DataType = "bit"; afiAssignedUserIsBusy.FieldID = 101008; afiAssignedUserIsBusy.FieldName = "Assigned User Is Busy"; afiAssignedUserIsBusy.IsCustom = false; afiAssignedUserIsBusy.IsVisible = true; afiAssignedUserIsBusy.ListValues = null; afiAssignedUserIsBusy.LookupTableID = null; afiAssignedUserIsBusy.Size = 0; afiAssignedUserIsBusy.Description = ""; afiAssignedUserIsBusy.TableID = -2; afiAssignedUserIsBusy.RefType = ReferenceType.Tickets; afiAssignedUserIsBusy.AuxID = null; afiAssignedUserIsBusy.OtherTrigger = "ticketsview.assignedusernotavailable"; fieldItems.Add(afiAssignedUserIsBusy); AutoFieldItem afiAgentRatings = new AutoFieldItem(); afiAgentRatings.Alias = "Agent Rating"; afiAgentRatings.DataType = "list"; afiAgentRatings.FieldID = 101009; afiAgentRatings.FieldName = "AgentRating"; afiAgentRatings.IsCustom = false; afiAgentRatings.IsVisible = true; afiAgentRatings.ListValues = new string[] { "Positive", "Negative", "Neutral" }; afiAgentRatings.LookupTableID = null; afiAgentRatings.Size = 0; afiAgentRatings.Description = ""; afiAgentRatings.TableID = -2; afiAgentRatings.RefType = ReferenceType.Tickets; afiAgentRatings.AuxID = null; afiAgentRatings.OtherTrigger = "ticketsview.AgentRating"; fieldItems.Add(afiAgentRatings); AutoFieldItem afiCDIValue = new AutoFieldItem(); afiCDIValue.Alias = "Customer CDI Value"; afiCDIValue.DataType = "text"; afiCDIValue.FieldID = 101010; afiCDIValue.FieldName = "Customer CDI Value"; afiCDIValue.IsCustom = false; afiCDIValue.IsVisible = true; afiCDIValue.ListValues = null; afiCDIValue.LookupTableID = null; afiCDIValue.Size = 0; afiCDIValue.Description = ""; afiCDIValue.TableID = -2; afiCDIValue.RefType = ReferenceType.Tickets; afiCDIValue.AuxID = null; afiCDIValue.OtherTrigger = "organizations.cdivalue"; fieldItems.Add(afiCDIValue); AutoFieldItem afiCDITrend = new AutoFieldItem(); afiCDITrend.Alias = "Customer CDI Trend"; afiCDITrend.DataType = "text"; afiCDITrend.FieldID = 101011; afiCDITrend.FieldName = "Customer CDI Trend"; afiCDITrend.IsCustom = false; afiCDITrend.IsVisible = true; afiCDITrend.ListValues = new string[] { "1", "0", "-1" };; afiCDITrend.LookupTableID = null; afiCDITrend.Size = 0; afiCDITrend.Description = ""; afiCDITrend.TableID = -2; afiCDITrend.RefType = ReferenceType.Tickets; afiCDITrend.AuxID = null; afiCDITrend.OtherTrigger = "organizations.cditrend"; fieldItems.Add(afiCDITrend); AutoFieldItem afiServiceLevelName = new AutoFieldItem(); afiServiceLevelName.Alias = "Service Level Name"; afiServiceLevelName.DataType = "text"; afiServiceLevelName.FieldID = 101012; afiServiceLevelName.FieldName = "Service Level Name"; afiServiceLevelName.IsCustom = false; afiServiceLevelName.IsVisible = true; afiServiceLevelName.ListValues = null; afiServiceLevelName.LookupTableID = null; afiServiceLevelName.Size = 0; afiServiceLevelName.Description = ""; afiServiceLevelName.TableID = -2; afiServiceLevelName.RefType = ReferenceType.Tickets; afiServiceLevelName.AuxID = null; afiServiceLevelName.OtherTrigger = "organizations.SLAName"; fieldItems.Add(afiServiceLevelName); AutoFieldItem afiServiceExpirationDate = new AutoFieldItem(); afiServiceExpirationDate.Alias = "Service Expiration Date"; afiServiceExpirationDate.DataType = "datetime"; afiServiceExpirationDate.FieldID = 101013; afiServiceExpirationDate.FieldName = "Service Expiration Date"; afiServiceExpirationDate.IsCustom = false; afiServiceExpirationDate.IsVisible = true; afiServiceExpirationDate.ListValues = null; afiServiceExpirationDate.LookupTableID = null; afiServiceExpirationDate.Size = 0; afiServiceExpirationDate.Description = ""; afiServiceExpirationDate.TableID = -2; afiServiceExpirationDate.RefType = ReferenceType.Tickets; afiServiceExpirationDate.AuxID = null; afiServiceExpirationDate.OtherTrigger = "organizations.serviceexpirationdate"; fieldItems.Add(afiServiceExpirationDate); AutoFieldItem afiCustomerIsActive = new AutoFieldItem(); afiCustomerIsActive.Alias = "Customer Is Active"; afiCustomerIsActive.DataType = "bit"; afiCustomerIsActive.FieldID = 101014; afiCustomerIsActive.FieldName = "Customer Is Active"; afiCustomerIsActive.IsCustom = false; afiCustomerIsActive.IsVisible = true; afiCustomerIsActive.ListValues = null; afiCustomerIsActive.LookupTableID = null; afiCustomerIsActive.Size = 0; afiCustomerIsActive.Description = ""; afiCustomerIsActive.TableID = -2; afiCustomerIsActive.RefType = ReferenceType.Tickets; afiCustomerIsActive.AuxID = null; afiCustomerIsActive.OtherTrigger = "organizations.active"; fieldItems.Add(afiCustomerIsActive); AutoFieldItem afiCustomerCustomValue = new AutoFieldItem(); afiCustomerCustomValue.Alias = "Customer Custom Value"; afiCustomerCustomValue.DataType = "text"; afiCustomerCustomValue.FieldID = -999; afiCustomerCustomValue.FieldName = "Customer Custom Value"; afiCustomerCustomValue.IsCustom = false; afiCustomerCustomValue.IsVisible = true; afiCustomerCustomValue.ListValues = orgCustomFields.ToArray(); afiCustomerCustomValue.LookupTableID = null; afiCustomerCustomValue.Size = 0; afiCustomerCustomValue.Description = ""; afiCustomerCustomValue.TableID = -2; afiCustomerCustomValue.RefType = ReferenceType.Tickets; afiCustomerCustomValue.AuxID = null; afiCustomerCustomValue.OtherTrigger = "organizations.customvalue"; fieldItems.Add(afiCustomerCustomValue); AutoFieldItem afiCustomerServiceExpired = new AutoFieldItem(); afiCustomerServiceExpired.Alias = "Customer Service Expired"; afiCustomerServiceExpired.DataType = "bit"; afiCustomerServiceExpired.FieldID = 101015; afiCustomerServiceExpired.FieldName = "Customer Service Expired"; afiCustomerServiceExpired.IsCustom = false; afiCustomerServiceExpired.IsVisible = true; afiCustomerServiceExpired.ListValues = null; afiCustomerServiceExpired.LookupTableID = null; afiCustomerServiceExpired.Size = 0; afiCustomerServiceExpired.Description = ""; afiCustomerServiceExpired.TableID = -2; afiCustomerServiceExpired.RefType = ReferenceType.Tickets; afiCustomerServiceExpired.AuxID = null; afiCustomerServiceExpired.OtherTrigger = "organizations.serviceexpired"; fieldItems.Add(afiCustomerServiceExpired); result.Fields = fieldItems.ToArray(); Users users = new Users(UserSession.LoginUser); users.LoadByOrganizationID(UserSession.LoginUser.OrganizationID, true); List <AutocompleteItem> userItems = new List <AutocompleteItem>(); foreach (User user in users) { userItems.Add(new AutocompleteItem(user.DisplayName, user.UserID.ToString())); } result.Users = userItems.ToArray(); Groups groups = new Groups(UserSession.LoginUser); groups.LoadByOrganizationID(UserSession.LoginUser.OrganizationID); List <AutocompleteItem> groupItems = new List <AutocompleteItem>(); foreach (Group group in groups) { groupItems.Add(new AutocompleteItem(group.Name, group.GroupID.ToString())); } result.Groups = groupItems.ToArray(); TicketSeverities severities = new TicketSeverities(UserSession.LoginUser); severities.LoadByOrganizationID(UserSession.LoginUser.OrganizationID); List <AutocompleteItem> severityItems = new List <AutocompleteItem>(); foreach (TicketSeverity severity in severities) { severityItems.Add(new AutocompleteItem(severity.Name, severity.TicketSeverityID.ToString())); } result.Severities = severityItems.ToArray(); List <AutocompleteItem> statusItems = new List <AutocompleteItem>(); List <AutocompleteItem> ticketTypeItems = new List <AutocompleteItem>(); foreach (TicketType ticketType in ticketTypes) { ticketTypeItems.Add(new AutocompleteItem(ticketType.Name, ticketType.TicketTypeID.ToString())); TicketStatuses statuses = new TicketStatuses(UserSession.LoginUser); statuses.LoadAllPositions(ticketType.TicketTypeID); foreach (TicketStatus status in statuses) { statusItems.Add(new AutocompleteItem(ticketType.Name + " - " + status.Name, status.TicketStatusID.ToString())); } } result.Statuses = statusItems.ToArray(); result.TicketTypes = ticketTypeItems.ToArray(); return(result); }
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)"); } }