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);
}
public ReportFieldItem[] GetFields(int reportSubCatID)
{
LoginUser loginUser = TSAuthentication.GetLoginUser();
List <ReportFieldItem> result = new List <ReportFieldItem>();
TicketTypes ticketTypes = new TicketTypes(loginUser);
ticketTypes.LoadAllPositions(loginUser.OrganizationID);
ReportSubcategory subCat = ReportSubcategories.GetReportSubcategory(loginUser, reportSubCatID);
ReportTable primaryTable = ReportTables.GetReportTable(loginUser, subCat.ReportCategoryTableID);
ReportTableFields reportTableFields = new ReportTableFields(loginUser);
reportTableFields.LoadByReportTableID(subCat.ReportCategoryTableID);
CRMLinkTable crmLink = new CRMLinkTable(loginUser);
crmLink.LoadByOrganizationID(loginUser.OrganizationID);
bool isJiraActive = crmLink.Where(p => p.CRMType.ToLower() == "jira" && p.Active).Any();
List <string> jiraFields = new List <string>()
{
"DateModifiedByJiraSync", "JiraID", "SyncWithJira", "JiraKey", "JiraLinkURL", "JiraStatus"
};
foreach (ReportTableField reportTableField in reportTableFields)
{
if ((isJiraActive && jiraFields.Where(p => p == reportTableField.FieldName).Any() ||
!jiraFields.Where(p => p == reportTableField.FieldName).Any()))
{
result.Add(new ReportFieldItem(primaryTable.Alias, true, reportTableField));
}
}
if (primaryTable.CustomFieldRefType != ReferenceType.None)
{
CustomFields customFields = new CustomFields(loginUser);
customFields.LoadByReferenceType(loginUser.OrganizationID, primaryTable.CustomFieldRefType, null, "Name");
foreach (CustomField customField in customFields)
{
if (customField.RefType == ReferenceType.Tickets || customField.RefType == ReferenceType.Actions)
{
TicketType ticketType = ticketTypes.FindByTicketTypeID(customField.AuxID);
if (ticketType != null)
{
result.Add(new ReportFieldItem(primaryTable.Alias, true, customField, ticketType.Name));
}
}
else
{
result.Add(new ReportFieldItem(primaryTable.Alias, true, customField, ""));
}
}
}
if (subCat.ReportTableID != null)
{
ReportTable subTable = ReportTables.GetReportTable(loginUser, (int)subCat.ReportTableID);
reportTableFields = new ReportTableFields(loginUser);
reportTableFields.LoadByReportTableID((int)subCat.ReportTableID);
foreach (ReportTableField reportTableField in reportTableFields)
{
result.Add(new ReportFieldItem(subTable.Alias, false, reportTableField));
}
if (subTable.CustomFieldRefType != ReferenceType.None)
{
CustomFields customFields = new CustomFields(loginUser);
customFields.LoadByReferenceType(loginUser.OrganizationID, subTable.CustomFieldRefType, null, "Name");
foreach (CustomField customField in customFields)
{
if (customField.RefType == ReferenceType.Tickets || customField.RefType == ReferenceType.Actions)
{
TicketType ticketType = ticketTypes.FindByTicketTypeID(customField.AuxID);
if (ticketType != null)
{
result.Add(new ReportFieldItem(subTable.Alias, false, customField, ticketType.Name));
}
}
else
{
result.Add(new ReportFieldItem(subTable.Alias, false, customField, ""));
}
}
}
}
return(result.ToArray());
}
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)");
}
}
private void LoadFields(int subcategoryID)
{
cmbFields.Items.Clear();
ReportSubcategory sub = (ReportSubcategory)ReportSubcategories.GetReportSubcategory(UserSession.LoginUser, subcategoryID);
if (sub == null)
{
return;
}
int primaryTableID = sub.ReportCategoryTableID;
int secondaryTableID = sub.ReportTableID != null ? (int)sub.ReportTableID : -1;
ReportTableFields fields = new ReportTableFields(UserSession.LoginUser);
fields.LoadByReportTableID(primaryTableID);
foreach (ReportTableField field in fields)
{
cmbFields.Items.Add(new RadComboBoxItem(field.Row["TableAlias"].ToString() + " :: " + field.Alias, "R" + field.ReportTableFieldID.ToString()));
}
TicketTypes ticketTypes = new TicketTypes(UserSession.LoginUser);
ticketTypes.LoadAllPositions(UserSession.LoginUser.OrganizationID);
ReportTable table = (ReportTable)ReportTables.GetReportTable(UserSession.LoginUser, primaryTableID);
if (table.CustomFieldRefType != ReferenceType.None)
{
CustomFields customFields = new CustomFields(UserSession.LoginUser);
customFields.LoadByReferenceType(UserSession.LoginUser.OrganizationID, (ReferenceType)table.CustomFieldRefType);
foreach (CustomField customField in customFields)
{
if (customField.RefType == ReferenceType.Tickets)
{
TicketType ticketType = ticketTypes.FindByTicketTypeID(customField.AuxID);
if (ticketType != null)
{
cmbFields.Items.Add(new RadComboBoxItem(table.Alias + " :: " + customField.Name + " (" + ticketType.Name + ")", "C" + customField.CustomFieldID.ToString()));
}
}
else
{
cmbFields.Items.Add(new RadComboBoxItem(table.Alias + " :: " + customField.Name, "C" + customField.CustomFieldID.ToString()));
}
}
}
if (secondaryTableID > -1)
{
fields = new ReportTableFields(UserSession.LoginUser);
fields.LoadByReportTableID(secondaryTableID);
foreach (ReportTableField field in fields)
{
cmbFields.Items.Add(new RadComboBoxItem(field.Row["TableAlias"].ToString() + " :: " + field.Alias, "R" + field.ReportTableFieldID.ToString()));
}
table = (ReportTable)ReportTables.GetReportTable(UserSession.LoginUser, secondaryTableID);
if (table.CustomFieldRefType != ReferenceType.None)
{
CustomFields customFields = new CustomFields(UserSession.LoginUser);
customFields.LoadByReferenceType(UserSession.LoginUser.OrganizationID, (ReferenceType)table.CustomFieldRefType);
foreach (CustomField customField in customFields)
{
if (customField.RefType == ReferenceType.Tickets)
{
TicketType ticketType = ticketTypes.FindByTicketTypeID(customField.AuxID);
if (ticketType != null)
{
cmbFields.Items.Add(new RadComboBoxItem(table.Alias + " :: " + customField.Name + " (" + ticketType.Name + ")", "C" + customField.CustomFieldID.ToString()));
}
}
else
{
cmbFields.Items.Add(new RadComboBoxItem(table.Alias + " :: " + customField.Name, "C" + customField.CustomFieldID.ToString()));
}
}
}
}
}
private void RecoverTickets(int orgID, LoginUser loginUser)
{
Tickets badTickets = new Tickets(GetCorrupteLoginUser());
SqlCommand command = new SqlCommand();
command.CommandText = @"
SELECT
*
FROM
Tickets
WHERE
OrganizationID = @OrganizationID
AND DateCreated > '2015-09-17 05:56:00'
AND TicketSource != 'EMail'
AND IgnoreMe = 0
";
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@OrganizationID", orgID);
badTickets.Fill(command, "");
Organizations existingCompanies = new Organizations(loginUser);
existingCompanies.LoadByParentID(orgID, false);
foreach (Ticket badTicket in badTickets)
{
try
{
//Copy Ticket
Ticket goodTicket = (new Tickets(loginUser)).AddNewTicket();
goodTicket.CopyRowData(badTicket);
//Product
if (badTicket.ProductID != null)
{
Product goodProduct = _goodProducts.FindByProductID((int)badTicket.ProductID);
if (goodProduct == null)
{
Product badProduct = _badProducts.FindByProductID((int)badTicket.ProductID);
goodProduct = _goodProducts.FindByName(badProduct.Name);
if (goodProduct != null)
{
goodTicket.ProductID = goodProduct.ProductID;
}
else
{
goodTicket.ProductID = null;
}
}
}
//Version
if (goodTicket.ProductID != null && badTicket.ReportedVersionID != null)
{
ProductVersion goodProductVersion = _goodProductVersions.FindByProductVersionID((int)badTicket.ReportedVersionID);
if (goodProductVersion == null)
{
ProductVersion badProductVersion = _badProductVersions.FindByProductVersionID((int)badTicket.ReportedVersionID);
goodProductVersion = _goodProductVersions.FindByVersionNumber(badProductVersion.VersionNumber, (int)goodTicket.ProductID);
if (goodProductVersion != null)
{
goodTicket.ReportedVersionID = goodProductVersion.ProductVersionID;
}
else
{
goodTicket.ReportedVersionID = null;
}
}
}
//Solved Version
if (goodTicket.ProductID != null && badTicket.SolvedVersionID != null)
{
ProductVersion goodProductVersion = _goodProductVersions.FindByProductVersionID((int)badTicket.SolvedVersionID);
if (goodProductVersion == null)
{
ProductVersion badProductVersion = _badProductVersions.FindByProductVersionID((int)badTicket.SolvedVersionID);
goodProductVersion = _goodProductVersions.FindByVersionNumber(badProductVersion.VersionNumber, (int)goodTicket.ProductID);
if (goodProductVersion != null)
{
goodTicket.SolvedVersionID = goodProductVersion.ProductVersionID;
}
else
{
goodTicket.SolvedVersionID = null;
}
}
}
//Group
if (badTicket.GroupID != null)
{
Group goodGroup = _goodGroups.FindByGroupID((int)badTicket.GroupID);
if (goodGroup == null)
{
Group badGroup = _badGroups.FindByGroupID((int)badTicket.GroupID);
goodGroup = _goodGroups.FindByName(badGroup.Name);
if (goodGroup != null)
{
goodTicket.GroupID = goodGroup.GroupID;
}
else
{
goodTicket.GroupID = null;
}
}
}
//Assigned User
if (badTicket.UserID != null)
{
User goodUser = _goodUsers.FindByUserID((int)badTicket.UserID);
if (goodUser == null)
{
User badUser = _badUsers.FindByUserID((int)badTicket.UserID);
goodUser = _goodUsers.FindByEmail(badUser.Email);
if (goodUser != null)
{
goodTicket.UserID = goodUser.UserID;
}
else
{
goodTicket.UserID = null;
}
}
}
//Type
TicketType goodTicketType = _goodTicketTypes.FindByTicketTypeID(badTicket.TicketTypeID);
if (goodTicketType == null)
{
TicketType badTicketType = _badTicketTypes.FindByTicketTypeID(badTicket.TicketTypeID);
goodTicketType = _goodTicketTypes.FindByName(badTicketType.Name);
if (goodTicketType != null)
{
goodTicket.TicketTypeID = goodTicketType.TicketTypeID;
}
else
{
goodTicket.TicketTypeID = _goodTicketTypes[0].TicketTypeID;
}
}
//Status
TicketStatus goodTicketStatus = _goodTicketStatuses.FindByTicketStatusID(badTicket.TicketStatusID);
if (goodTicketStatus == null)
{
TicketStatus badTicketStatus = _badTicketStatuses.FindByTicketStatusID(badTicket.TicketStatusID);
goodTicketStatus = _goodTicketStatuses.FindByName(badTicketStatus.Name, goodTicket.TicketTypeID);
if (goodTicketStatus != null)
{
goodTicket.TicketStatusID = goodTicketStatus.TicketStatusID;
}
else
{
goodTicketStatus = _goodTicketStatuses.FindTopOne(goodTicket.TicketTypeID);
goodTicket.TicketStatusID = goodTicketStatus.TicketStatusID;
}
}
//Severity
TicketSeverity goodTicketSeverity = _goodTicketSeverities.FindByTicketSeverityID(badTicket.TicketSeverityID);
if (goodTicketSeverity == null)
{
TicketSeverity badTicketSeverity = _badTicketSeverities.FindByTicketSeverityID(badTicket.TicketSeverityID);
goodTicketSeverity = _goodTicketSeverities.FindByName(badTicketSeverity.Name);
if (goodTicketSeverity != null)
{
goodTicket.TicketSeverityID = goodTicketSeverity.TicketSeverityID;
}
else
{
goodTicket.TicketSeverityID = _goodTicketSeverities[0].TicketSeverityID;
}
}
//Knowledgebase Cat
if (badTicket.KnowledgeBaseCategoryID != null)
{
KnowledgeBaseCategory goodKBCategory = _goodTicketKBCategories.FindByCategoryID((int)badTicket.KnowledgeBaseCategoryID);
if (goodKBCategory == null)
{
KnowledgeBaseCategory badKBCategory = _badTicketKBCategories.FindByCategoryID((int)badTicket.KnowledgeBaseCategoryID);
goodKBCategory = _goodTicketKBCategories.FindByName(badKBCategory.CategoryName);
if (goodKBCategory != null)
{
goodTicket.KnowledgeBaseCategoryID = goodKBCategory.CategoryID;
}
else
{
goodTicket.KnowledgeBaseCategoryID = null;
}
}
}
//Parent Ticket (NOTE from MT: we decided as a team to null out this field to ensure no bad relationships can happen since we don't know the ticketID is a preserved field for every ticket.
goodTicket.ParentID = null;
//Closing User
if (badTicket.CloserID != null)
{
User goodUser = _goodUsers.FindByUserID((int)badTicket.CloserID);
if (goodUser == null)
{
User badUser = _badUsers.FindByUserID((int)badTicket.CloserID);
goodUser = _goodUsers.FindByEmail(badUser.Email);
if (goodUser != null)
{
goodTicket.CloserID = goodUser.UserID;
}
else
{
goodTicket.CloserID = null;
}
}
}
//Reset ticket dates
goodTicket.DateCreated = badTicket.DateCreatedUtc;
goodTicket.DateModified = badTicket.DateModifiedUtc;
goodTicket.ParentID = null;
goodTicket.ImportID = _importID;
if (badTicket.CreatorID > 0)
{
User creator = _usersAndContacts.FindByUserID(badTicket.CreatorID);
if (creator != null)
{
goodTicket.CreatorID = creator.UserID;
}
else
{
goodTicket.CreatorID = -5;
}
}
else
{
goodTicket.CreatorID = badTicket.CreatorID;
}
if (badTicket.ModifierID > 0)
{
User modifier = _usersAndContacts.FindByUserID(badTicket.ModifierID);
if (modifier != null)
{
goodTicket.ModifierID = modifier.UserID;
}
else
{
goodTicket.ModifierID = -5;
}
}
else
{
goodTicket.ModifierID = badTicket.ModifierID;
}
goodTicket.TicketNumber = 0;
goodTicket.Collection.Save();
EmailPosts.DeleteImportEmails(loginUser);
Actions badActions = new Actions(GetCorrupteLoginUser());
badActions.LoadByTicketID(badTicket.TicketID);
foreach (TeamSupport.Data.Action badAction in badActions)
{
TeamSupport.Data.Action goodAction = new Actions(loginUser).AddNewAction();
goodAction.CopyRowData(badAction);
goodAction.DateCreated = badAction.DateCreatedUtc;
goodAction.DateModified = badAction.DateCreatedUtc;
goodAction.TicketID = goodTicket.TicketID;
goodAction.ImportID = _importID;
if (badAction.CreatorID > 0)
{
User creator = _usersAndContacts.FindByUserID(badAction.CreatorID);
if (creator != null)
{
goodAction.CreatorID = creator.UserID;
}
else
{
goodAction.CreatorID = -5;
}
}
else
{
goodAction.CreatorID = badAction.CreatorID;
}
if (badAction.ModifierID > 0)
{
User modifier = _usersAndContacts.FindByUserID(badAction.ModifierID);
if (modifier != null)
{
goodAction.ModifierID = modifier.UserID;
}
else
{
goodAction.ModifierID = -5;
}
}
else
{
goodAction.ModifierID = badAction.ModifierID;
}
goodAction.Collection.Save();
EmailPosts.DeleteImportEmails(loginUser);
}
Organizations orgs = new Organizations(GetCorrupteLoginUser());
orgs.LoadBTicketID(badTicket.TicketID);
foreach (Organization org in orgs)
{
Organization goodCompany = existingCompanies.FindByName(org.Name);
if (org.ParentID == orgID && goodCompany != null)
{
goodTicket.Collection.AddOrganization(goodCompany.OrganizationID, goodTicket.TicketID);
EmailPosts.DeleteImportEmails(loginUser);
}
}
RecoverTicketCustomValues(orgID, badTicket.TicketID, goodTicket.TicketID);
EmailPosts.DeleteImportEmails(loginUser);
}
catch (Exception ex)
{
_exceptionOcurred = true;
ExceptionLogs.LogException(GetCorrupteLoginUser(), ex, "recover");
}
}
}
