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