public DataTable GetReportTable(string sortField, bool isDesc, bool useUserFilter, bool includeHiddenFields)
{
if (_report.IsDisabled || SystemSettings.GetIsReportsDisabled())
{
return(null);
}
DateTime timeStart = DateTime.Now;
DataTable result = null;
try { result = GetReportTableAll(sortField, isDesc, useUserFilter, includeHiddenFields); }
catch (Exception) { result = null; }
// try without the sort
if (result == null)
{
try { result = GetReportTableAll(null, isDesc, useUserFilter, includeHiddenFields); }
catch (Exception) { result = null; }
}
// try without the user filters
if (result == null)
{
result = GetReportTableAll(null, isDesc, false, includeHiddenFields);
UserTabularSettings userFilters = JsonConvert.DeserializeObject <UserTabularSettings>((string)_report.Row["Settings"]);
userFilters.Filters = null;
_report.Row["Settings"] = JsonConvert.SerializeObject(userFilters);
_report.Collection.Save();
}
_report.LastTimeTaken = (int)(DateTime.Now - timeStart).TotalSeconds;
_report.Collection.Save();
return(result);
}
public GridResult GetReportData(int from, int to, string sortField, bool isDesc, bool useUserFilter)
{
if (_report.IsDisabled || SystemSettings.GetIsReportsDisabled())
{
return(null);
}
DateTime timeStart = DateTime.Now;
GridResult result;
try { result = GetReportDataPage(from, to, sortField, isDesc, useUserFilter); }
catch (Exception) { result = null; }
// try without the sort
if (result == null)
{
try { result = GetReportDataPage(from, to, null, isDesc, useUserFilter); }
catch (Exception) { result = null; }
}
// try without the user filters
if (result == null)
{
result = GetReportDataPage(from, to, null, isDesc, false);
UserTabularSettings userFilters = JsonConvert.DeserializeObject <UserTabularSettings>((string)_report.Row["Settings"]);
userFilters.Filters = null;
_report.Row["Settings"] = JsonConvert.SerializeObject(userFilters);
_report.Collection.Save();
}
_report.LastTimeTaken = (int)(DateTime.Now - timeStart).TotalSeconds;
_report.Collection.Save();
return(result);
}
public static void GetTabularSqlForExports(LoginUser loginUser, SqlCommand command, TabularReport tabularReport, bool inlcudeHiddenFields, bool isSchemaOnly, int?reportID, bool useUserFilter, string sortField = null, string sortDir = null)
{
StringBuilder builder = new StringBuilder();
TabularReportSql tabularReportSql = new TabularReportSql(loginUser);
tabularReportSql.GetTabluarSelectClause(command, builder, tabularReport, inlcudeHiddenFields, isSchemaOnly, sortField, sortDir);
if (isSchemaOnly)
{
command.CommandText = builder.ToString();
}
else
{
string primaryTableKeyFieldName = null;
if (tabularReport.Subcategory == 70)
{
primaryTableKeyFieldName = "UserTicketsView.TicketID";
}
GetWhereClauseForExport(loginUser, command, builder, tabularReport.Filters, primaryTableKeyFieldName);
if (useUserFilter && 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)
{
GetWhereClauseForExport(loginUser, command, builder, userFilters.Filters);
}
}
catch (Exception ex)
{
ExceptionLogs.LogException(loginUser, ex, "Tabular SQL - User filters");
}
}
}
command.CommandText = builder.ToString();
}
}
public static void GetCustomSqlForExport(LoginUser loginUser, SqlCommand command, bool useUserFilter, Report report)
{
//if (isSchemaOnly)
//{
// command.CommandText = string.Format("WITH q AS ({0}) SELECT * FROM q WHERE (0=1)", Query);
// return;
//}
report = Reports.GetReport(loginUser, report.ReportID, loginUser.UserID);
if (report != null && report.Row["Settings"] != DBNull.Value)
{
try
{
UserTabularSettings userFilters = JsonConvert.DeserializeObject <UserTabularSettings>((string)report.Row["Settings"]);
StringBuilder builder = new StringBuilder();
if (userFilters != null && userFilters.Filters != null && userFilters.Filters.Length > 0)
{
Report.GetWhereClause(loginUser, command, builder, userFilters.Filters);
builder.Remove(0, 4);
//command.CommandText = string.Format("c AS ({0}), q AS (SELECT * FROM c WHERE {1})", Query, builder.ToString());
command.CommandText = string.Format("WITH c AS ({0}) SELECT * FROM c WHERE {1}", report.Query, builder.ToString());
}
else
{
//command.CommandText = string.Format("c AS ({0}), q AS (SELECT * FROM c)", Query);
command.CommandText = report.Query;
}
}
catch (Exception ex)
{
ExceptionLogs.LogException(loginUser, ex, "Tabular SQL - User filters");
throw;
}
}
else
{
//command.CommandText = string.Format("c AS ({0}), q AS (SELECT * FROM c)", Query);
command.CommandText = report.Query;
}
}
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();
}
