public void UseTicketRights(int subCAtID, ReportTables tables, SqlCommand command, StringBuilder builder)
{
ReportSubcategory subCat = ReportSubcategories.GetReportSubcategory(_loginUser, subCAtID);
if (subCat != null)
{
ReportTable catTable = tables.FindByReportTableID((int)subCat.ReportCategoryTableID);
if (catTable.UseTicketRights)
{
GetUserRightsClause(command, builder, catTable.TableName);
return;
}
else if (catTable.ReportTableID == 6)
{
GetCustomerUserRightsClause(command, builder, catTable.TableName);
}
if (subCat.ReportTableID != null)
{
ReportTable reportTable = tables.FindByReportTableID((int)subCat.ReportTableID);
if (reportTable.UseTicketRights)
{
GetUserRightsClause(command, builder, reportTable.TableName);
return;
}
else if (reportTable.ReportTableID == 6)
{
GetCustomerUserRightsClause(command, builder, reportTable.TableName);
}
}
}
}
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);
}
public static string GetReportSubcategory(RestCommand command, int reportSubcategoryID)
{
ReportSubcategory reportSubcategory = ReportSubcategories.GetReportSubcategory(command.LoginUser, reportSubcategoryID);
if (reportSubcategory.OrganizationID != command.Organization.OrganizationID)
{
throw new RestException(HttpStatusCode.Unauthorized);
}
return(reportSubcategory.GetXml("ReportSubcategory", true));
}
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 void GetSummarySql(SqlCommand command, SummaryReport summaryReport, bool isSchemaOnly, int?reportID, bool useUserFilter, bool useDefaultOrderBy)
{
LoginUser loginUser = _userRights._loginUser;
StringBuilder builder = new StringBuilder();
ReportSubcategory sub = ReportSubcategories.GetReportSubcategory(loginUser, summaryReport.Subcategory);
ReportTables tables = new ReportTables(loginUser);
tables.LoadAll();
List <DescriptiveClauseItem> descFields = GetSummaryDescFields(loginUser, summaryReport);
List <CalculatedClauseItem> calcFields = GetSummaryCalcFields(loginUser, summaryReport);
builder.Append("WITH x AS (");
bool flag = true;
foreach (DescriptiveClauseItem descField in descFields)
{
if (flag)
{
builder.Append(string.Format(" SELECT {0} AS [{1}]", descField.Field, descField.Alias));
}
else
{
builder.Append(string.Format(", {0} AS [{1}]", descField.Field, descField.Alias));
}
flag = false;
}
foreach (CalculatedClauseItem calcField in calcFields)
{
builder.Append(string.Format(", {0} AS [{1}]", calcField.Field, calcField.Alias));
}
// from + where clause
builder.Append(" " + sub.BaseQuery);
ReportTable mainTable = tables.FindByReportTableID(sub.ReportCategoryTableID);
_organizationIDFieldName = mainTable.OrganizationIDFieldName;
builder.Append(" WHERE (" + mainTable.TableName + "." + mainTable.OrganizationIDFieldName + " = @OrganizationID)");
//add user rights where needed
_userRights.UseTicketRights((int)summaryReport.Subcategory, tables, command, builder);
if (isSchemaOnly)
{
builder.Append(" AND (0=1)");
}
// filters
if (!isSchemaOnly)
{
Report.GetWhereClause(loginUser, command, builder, summaryReport.Filters);
if (useUserFilter == true && reportID != null)
{
Report report = Reports.GetReport(loginUser, (int)reportID, loginUser.UserID);
if (report != null && report.Row["Settings"] != DBNull.Value)
{
try
{
UserTabularSettings userFilters = JsonConvert.DeserializeObject <UserTabularSettings>((string)report.Row["Settings"]);
if (userFilters != null)
{
Report.GetWhereClause(loginUser, command, builder, userFilters.Filters);
}
}
catch (Exception ex)
{
ExceptionLogs.LogException(loginUser, ex, "Summary SQL - User filters");
}
}
}
}
flag = true;
builder.Append(")"); // end with
flag = true;
foreach (DescriptiveClauseItem descField in descFields)
{
if (flag)
{
builder.Append(string.Format(" SELECT [{0}]", descField.Alias));
}
else
{
builder.Append(string.Format(", [{0}]", descField.Alias));
}
flag = false;
}
foreach (CalculatedClauseItem calcField in calcFields)
{
builder.Append(string.Format(", {0} AS [{1}]", calcField.AggField, calcField.Alias));
}
builder.Append(" FROM x ");
// group by
flag = true;
foreach (DescriptiveClauseItem descField in descFields)
{
if (flag)
{
builder.Append(string.Format(" GROUP BY [{0}]", descField.Alias));
}
else
{
builder.Append(string.Format(", [{0}]", descField.Alias));
}
flag = false;
}
// having
flag = true;
foreach (CalculatedClauseItem calcField in calcFields)
{
if (calcField.Comparator == null)
{
continue;
}
if (flag)
{
builder.Append(string.Format(" HAVING {0}", calcField.Comparator));
}
else
{
builder.Append(string.Format(" AND {0}", calcField.Comparator));
}
flag = false;
}
if (useDefaultOrderBy)
{
// order by
/* flag = true;
* foreach (DescriptiveClauseItem descField in descFields)
* {
* if (flag)
* builder.Append(string.Format(" ORDER BY [{0}]", descField.Alias));
* else
* builder.Append(string.Format(", [{0}]", descField.Alias));
*
* flag = false;
* }*/
// order by
for (int i = descFields.Count - 1; i > -1; i--)
{
if (i == descFields.Count - 1)
{
builder.Append(string.Format(" ORDER BY [{0}]", descFields[i].Alias));
}
else
{
builder.Append(string.Format(", [{0}]", descFields[i].Alias));
}
}
}
command.CommandText = builder.ToString();
}
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()));
}
}
}
}
}