Dictionary <int, EField> MapFieldIdToEField()
{
// load all 39 reporting tables to find ReportTicketsView
ReportTables tables = new ReportTables(_loginUser);
tables.LoadAll();
int reportTableID = tables.Where(t => t.TableName == "ReportTicketsView").Select(t => t.ReportTableID).Min(); // Min is faster than First or Single
// load all 964 and find the ReportTicketsView fields
ReportTableFields tableFields = new ReportTableFields(_loginUser);
tableFields.LoadAll();
var queryable = tableFields.Where(f => f.ReportTableID == reportTableID);
Dictionary <int, EField> mapIdToField = new Dictionary <int, EField>();
foreach (ReportTableField row in queryable)
{
if (!Enum.TryParse(row.FieldName, out EField eField))
{
if (System.Diagnostics.Debugger.IsAttached)
{
System.Diagnostics.Debugger.Break();
}
}
mapIdToField[row.ReportTableFieldID] = eField;
}
return(mapIdToField);
}
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 static string GetReportTableFields(RestCommand command)
{
ReportTableFields reportTableFields = new ReportTableFields(command.LoginUser);
reportTableFields.LoadByOrganizationID(command.Organization.OrganizationID);
if (command.Format == RestFormat.XML)
{
return(reportTableFields.GetXml("ReportTableFields", "ReportTableField", true, command.Filters));
}
else
{
throw new RestException(HttpStatusCode.BadRequest, "Invalid data format");
}
}
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 FieldItem[] GetAllFields(ReferenceType refType, int?auxID, bool isReadOnly)
{
List <FieldItem> items = new List <FieldItem>();
//The fields that are synched by 'default' (stock) should not be available in the dropdown list for the mappings because this creates issues with the sync (data being overwritten). Add them to this list in lowercase.
List <string> excludedFields = new List <string>();
int tableID;
switch (refType)
{
case ReferenceType.Organizations: tableID = 6; break;
case ReferenceType.Tickets: tableID = 10; break;
case ReferenceType.Users: tableID = 11; break;
case ReferenceType.Contacts:
tableID = 12;
//reference: Integration.vb UpdateContactInfo()
excludedFields = new List <string>()
{
"name", "title", "email"
};
break;
case ReferenceType.TicketTypes: tableID = 16; break;
default: return(null);
}
TicketTypes ticketTypes = new TicketTypes(TSAuthentication.GetLoginUser());
ticketTypes.LoadByOrganizationID(TSAuthentication.OrganizationID);
if (refType == ReferenceType.TicketTypes)
{
foreach (TicketType ticketType in ticketTypes)
{
items.Add(new FieldItem(ticketType.TicketTypeID, false, ticketType.Name));
}
}
else
{
ReportTableFields fields = new ReportTableFields(TSAuthentication.GetLoginUser());
fields.LoadByReportTableID(tableID, isReadOnly);
CustomFields customs = new CustomFields(fields.LoginUser);
customs.LoadByReferenceType(TSAuthentication.OrganizationID, refType, auxID);
foreach (ReportTableField field in fields)
{
if (excludedFields.Count == 0 ||
(tableID == 12 &&
!excludedFields.Contains(field.FieldName.ToLower())))
{
items.Add(new FieldItem(field.ReportTableFieldID, false, field.FieldName));
}
}
foreach (CustomField custom in customs)
{
string ticketTypeName = ticketTypes.Where(p => p.TicketTypeID == custom.AuxID).Select(t => t.Name).SingleOrDefault();
items.Add(new FieldItem(custom.CustomFieldID,
true,
string.Format("{0}{1}", custom.Name,
string.IsNullOrEmpty(ticketTypeName) ? "" : " (" + ticketTypeName + ")")));
}
}
return(items.ToArray());
}
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());
}
public static PlaceHolders[] GetPlaceHolders(int emailTemplateID)
{
EmailTemplate template = EmailTemplates.GetEmailTemplate(UserSession.LoginUser, emailTemplateID);
List <PlaceHolders> result = new List <PlaceHolders>();
EmailTemplateParameters paramaters = new EmailTemplateParameters(UserSession.LoginUser);
paramaters.LoadByTemplate(emailTemplateID);
List <PlaceHolder> list = new List <PlaceHolder>();
PlaceHolders phs = new PlaceHolders();
if (!paramaters.IsEmpty)
{
phs.Name = "Miscellaneous";
phs.Description = "";
foreach (EmailTemplateParameter parameter in paramaters)
{
PlaceHolder ph = new PlaceHolder();
ph.Name = parameter.Name;
ph.Description = parameter.Description ?? "";
list.Add(ph);
}
PlaceHolder phTo = new PlaceHolder();
phTo.Name = "ToEmailAddress";
phTo.Description = "This adds the recipient's email address.";
list.Add(phTo);
phTo = new PlaceHolder();
phTo.Name = "ToFirstName";
phTo.Description = "This adds the recipient's first name.";
list.Add(phTo);
phTo = new PlaceHolder();
phTo.Name = "ToLastName";
phTo.Description = "This adds the recipient's last name.";
list.Add(phTo);
phs.Items = list.ToArray();
result.Add(phs);
}
EmailTemplateTables tables = new EmailTemplateTables(UserSession.LoginUser);
tables.LoadByTemplate(emailTemplateID);
foreach (EmailTemplateTable table in tables)
{
phs = new PlaceHolders();
phs.Name = table.Alias;
phs.Description = table.Description ?? "";
list.Clear();
ReportTable reportTable = ReportTables.GetReportTable(UserSession.LoginUser, table.ReportTableID);
ReportTableFields fields = new ReportTableFields(UserSession.LoginUser);
fields.LoadByReportTableID(table.ReportTableID);
foreach (ReportTableField field in fields)
{
PlaceHolder ph = new PlaceHolder();
ph.Name = table.Alias + '.' + field.FieldName;
ph.Description = field.Description ?? "";
list.Add(ph);
}
//do custom fields here
ReferenceType refType = reportTable.CustomFieldRefType;
if (refType != ReferenceType.None)
{
//TicketTypes ticketTypes = new TicketTypes(UserSession.LoginUser);
//ticketTypes.LoadAllPositions(UserSession.LoginUser.OrganizationID);
CustomFields customFields = new CustomFields(UserSession.LoginUser);
customFields.LoadByReferenceType(UserSession.LoginUser.OrganizationID, refType, null, "Name");
foreach (CustomField customField in customFields)
{
string customFieldName = table.Alias + "." + customField.Name;
bool flag = false;
foreach (PlaceHolder existingPH in list)
{
if (existingPH.Name == customFieldName)
{
flag = true;
break;
}
}
if (flag)
{
continue;
}
PlaceHolder ph = new PlaceHolder();
ph.Name = customFieldName;
ph.Description = "";
list.Add(ph);
/*
* if (customField.RefType == ReferenceType.Tickets || customField.RefType == ReferenceType.Actions)
* {
* TicketType ticketType = ticketTypes.FindByTicketTypeID(customField.AuxID);
* if (ticketType != null)
* {
* //
* }
* }
* else
* {
* }*/
}
}
phs.Items = list.ToArray();
result.Add(phs);
}
phs = new PlaceHolders();
phs.Name = "MyCompany";
phs.Description = "Your company's information";
list.Clear();
ReportTableFields companyFields = new ReportTableFields(UserSession.LoginUser);
companyFields.LoadByReportTableID(6);
foreach (ReportTableField field in companyFields)
{
PlaceHolder ph = new PlaceHolder();
ph.Name = "MyCompany." + field.FieldName;
ph.Description = field.Description ?? "";
list.Add(ph);
}
phs.Items = list.ToArray();
result.Add(phs);
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()));
}
}
}
}
}