public string ConvertToWhereClause()
{
StringBuilder resultBuilder = new StringBuilder();
ReportTableFields ticketsViewFields = new ReportTableFields(base.LoginUser);
ticketsViewFields.LoadByReportTableID(10);
foreach (SearchCustomFilter filter in this)
{
if (filter.TableID == 10)
{
string fieldName = ticketsViewFields.FindByReportTableFieldID(filter.FieldID).FieldName;
if (string.Equals(filter.Measure, "contains", StringComparison.OrdinalIgnoreCase))
{
resultBuilder.Append(" AND tv.[" + fieldName + "] LIKE '%" + filter.TestValue + "%'");
}
else
{
resultBuilder.Append(" AND tv.[" + fieldName + "] " + filter.Measure + " '" + filter.TestValue + "'");
}
}
}
return(resultBuilder.ToString());
}
public static ReportTableField GetReportTableField(LoginUser loginUser, int reportTableFieldID)
{
ReportTableFields reportTableFields = new ReportTableFields(loginUser);
reportTableFields.LoadByReportTableFieldID(reportTableFieldID);
if (reportTableFields.IsEmpty)
{
return(null);
}
else
{
return(reportTableFields[0]);
}
}
public static LookupFieldValues GetValues(LoginUser loginUser, int reportTableFieldID, string term, int maxRows)
{
LookupFieldValues result = new LookupFieldValues();
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);
foreach (DataRow row in dataTable.Rows)
{
result.Add((int)row[1], row[0].ToString());
}
return(result);
}
public string ConvertToWaterCoolerEquivalentWhereClause()
{
StringBuilder resultBuilder = new StringBuilder();
ReportTableFields ticketsViewFields = new ReportTableFields(base.LoginUser);
ticketsViewFields.LoadByReportTableID(10);
foreach (SearchCustomFilter filter in this)
{
string fieldName = ticketsViewFields.FindByReportTableFieldID(filter.FieldID).FieldName;
string waterCoolerEquivalentFieldName = DataUtils.GetWaterCoolerEquivalentFieldName(fieldName);
resultBuilder.Append(" AND wcv.[" + waterCoolerEquivalentFieldName + "] " + filter.Measure + " '" + filter.TestValue + "'");
}
return(resultBuilder.ToString());
}
public string ConvertToWikiEquivalentWhereClause()
{
StringBuilder resultBuilder = new StringBuilder();
ReportTableFields ticketsViewFields = new ReportTableFields(base.LoginUser);
ticketsViewFields.LoadByReportTableID(10);
foreach (SearchCustomFilter filter in this)
{
string fieldName = ticketsViewFields.FindByReportTableFieldID(filter.FieldID).FieldName;
string wikiEquivalentFieldName = DataUtils.GetWikiEquivalentFieldName(fieldName);
resultBuilder.Append(" AND wav.[" + wikiEquivalentFieldName + "] " + filter.Measure + " '" + filter.TestValue + "'");
// It is pending to code around custom fields and any other fieldName that is not part of the ticketsView
}
return(resultBuilder.ToString());
}
public CustomPortalColumnProxy GetProxy()
{
CustomPortalColumnProxy result = new CustomPortalColumnProxy();
result.CustomFieldID = this.CustomFieldID;
result.StockFieldID = this.StockFieldID;
result.Position = this.Position;
result.OrganizationID = this.OrganizationID;
result.CustomColumnID = this.CustomColumnID;
if (result.CustomFieldID != null)
{
CustomFields cf = new CustomFields(BaseCollection.LoginUser);
cf.LoadByCustomFieldID((int)result.CustomFieldID);
TicketTypes ticketTypes = new TicketTypes(BaseCollection.LoginUser);
ticketTypes.LoadAllPositions(BaseCollection.LoginUser.OrganizationID);
TicketType ticketType = ticketTypes.FindByTicketTypeID(cf[0].AuxID);
if (ticketType == null)
{
result.FieldText = cf[0].Name;
}
else
{
result.FieldText = string.Format("{0} ({1})", cf[0].Name, ticketType.Name);
}
}
else
{
ReportTableFields rt = new ReportTableFields(BaseCollection.LoginUser);
rt.LoadByReportTableFieldID((int)result.StockFieldID);
result.FieldText = rt[0].Alias;
}
return(result);
}
public ReportTableField(DataRow row, ReportTableFields reportTableFields) : base(row, reportTableFields)
{
_reportTableFields = reportTableFields;
}
public string ConvertToOrderByClause(ref string tempItemsTableFieldsDefinition,
ref string tempItemsTableFields,
ref string selectTicketsFields,
ref string selectWikisFields,
ref string selectNotesFields,
ref string selectProductVersionsFields,
ref string selectTasksFields,
ref string selectWaterCoolerFields)
{
StringBuilder resultBuilder = new StringBuilder();
if (this.Count > 0)
{
StringBuilder tempItemsTableFieldsDefinitionBuilder = new StringBuilder();
StringBuilder tempItemsTableFieldsBuilder = new StringBuilder();
StringBuilder selectTicketsFieldsBuilder = new StringBuilder();
StringBuilder selectWikisFieldsBuilder = new StringBuilder();
StringBuilder selectNotesFieldsBuilder = new StringBuilder();
StringBuilder selectProductVersionsFieldsBuilder = new StringBuilder();
StringBuilder selectTasksFieldsBuilder = new StringBuilder();
StringBuilder selectWaterCoolerFieldsBuilder = new StringBuilder();
WikiArticlesView wikiArticleViewFields = new WikiArticlesView(base.LoginUser);
wikiArticleViewFields.LoadColumnNames();
resultBuilder.Append(" ORDER BY ");
ReportTableFields ticketsViewFields = new ReportTableFields(base.LoginUser);
ticketsViewFields.LoadByReportTableID(10);
for (int i = 0; i < this.Count; i++)
{
if (i > 0)
{
resultBuilder.Append(", ");
}
string fieldName = ticketsViewFields.FindByReportTableFieldID(this[i].FieldID).FieldName;
resultBuilder.Append("[" + fieldName + "] " + (this[i].Descending ? "DESC" : string.Empty));
if (!string.Equals(fieldName, "DateModified", StringComparison.OrdinalIgnoreCase))
{
string fieldType = ticketsViewFields.FindByReportTableFieldID(this[i].FieldID).DataType;
if (string.Equals(fieldType, "varchar", StringComparison.OrdinalIgnoreCase))
{
fieldType += "(8000)";
}
tempItemsTableFieldsDefinitionBuilder.Append(", " + fieldName + " " + fieldType);
tempItemsTableFieldsBuilder.Append(", " + fieldName);
selectTicketsFieldsBuilder.Append(", tv." + fieldName);
string wikiEquivalentFieldName = DataUtils.GetWikiEquivalentFieldName(fieldName);
if (!DataUtils.GetIsColumnInBaseCollection(wikiArticleViewFields, wikiEquivalentFieldName))
{
selectWikisFieldsBuilder.Append(", NULL AS " + fieldName);
}
else
{
selectWikisFieldsBuilder.Append(", wav." + wikiEquivalentFieldName + " AS " + fieldName);
}
string notesEquivalentFieldName = DataUtils.GetNotesEquivalentFieldName(fieldName);
NotesView notesView = new NotesView(base.LoginUser);
NotesViewItem notesViewItem = notesView.AddNewNotesViewItem();
if (!DataUtils.GetIsColumnInBaseCollection(notesViewItem.Collection, notesEquivalentFieldName))
{
selectNotesFieldsBuilder.Append(", NULL AS " + fieldName);
}
else
{
selectNotesFieldsBuilder.Append(", nv." + notesEquivalentFieldName + " AS " + fieldName);
}
string productVersionsEquivalentFieldName = DataUtils.GetProductVersionsEquivalentFieldName(fieldName);
ProductVersionsView productVersionsView = new ProductVersionsView(base.LoginUser);
ProductVersionsViewItem productVersionsViewItem = productVersionsView.AddNewProductVersionsViewItem();
if (!DataUtils.GetIsColumnInBaseCollection(productVersionsViewItem.Collection, productVersionsEquivalentFieldName))
{
selectProductVersionsFieldsBuilder.Append(", NULL AS " + fieldName);
}
else
{
selectProductVersionsFieldsBuilder.Append(", pvv." + productVersionsEquivalentFieldName + " AS " + fieldName);
}
string tasksEquivalentFieldName = DataUtils.GetTasksEquivalentFieldName(fieldName);
TasksView tasksView = new TasksView(base.LoginUser);
TasksViewItem tasksViewItem = tasksView.AddNewTasksViewItem();
if (!DataUtils.GetIsColumnInBaseCollection(tasksViewItem.Collection, tasksEquivalentFieldName))
{
selectTasksFieldsBuilder.Append(", NULL AS " + fieldName);
}
else
{
selectTasksFieldsBuilder.Append(", tsk." + tasksEquivalentFieldName + " AS " + fieldName);
}
string waterCoolerEquivalentFieldName = DataUtils.GetWaterCoolerEquivalentFieldName(fieldName);
WaterCoolerView waterCoolerView = new WaterCoolerView(base.LoginUser);
WaterCoolerViewItem waterCoolerViewItem = waterCoolerView.AddNewWaterCoolerViewItem();
if (!DataUtils.GetIsColumnInBaseCollection(waterCoolerViewItem.Collection, waterCoolerEquivalentFieldName))
{
selectWaterCoolerFieldsBuilder.Append(", NULL AS " + fieldName);
}
else
{
selectWaterCoolerFieldsBuilder.Append(", wcv." + waterCoolerEquivalentFieldName + " AS " + fieldName);
}
}
}
tempItemsTableFieldsDefinition = tempItemsTableFieldsDefinitionBuilder.ToString();
tempItemsTableFields = tempItemsTableFieldsBuilder.ToString();
selectTicketsFields = selectTicketsFieldsBuilder.ToString();
selectWikisFields = selectWikisFieldsBuilder.ToString();
selectNotesFields = selectNotesFieldsBuilder.ToString();
selectProductVersionsFields = selectProductVersionsFieldsBuilder.ToString();
selectTasksFields = selectTasksFieldsBuilder.ToString();
selectWaterCoolerFields = selectWaterCoolerFieldsBuilder.ToString();
}
else
{
resultBuilder.Append(@"
ORDER BY
relevance DESC
, DateModified DESC
");
}
return(resultBuilder.ToString());
}
public string GetSQL()
{
StringBuilder builder = new StringBuilder();
ReportTables tables = new ReportTables(_loginUser);
tables.LoadAll();
string con = _matchAll ? " AND " : " OR ";
foreach (ReportCondition condition in _items)
{
string value = "";
if (condition.Value1 is string && condition.Value1.ToString().ToLower() == "self")
{
User user = Users.GetUser(_loginUser, _loginUser.UserID);
condition.Value1 = user.FirstLastName;
}
if (condition.Value2 is string && condition.Value2.ToString().ToLower() == "self")
{
User user = Users.GetUser(_loginUser, _loginUser.UserID);
condition.Value2 = user.FirstLastName;
}
if (!condition.IsCustomField)
{
ReportTableField field = (ReportTableField)ReportTableFields.GetReportTableField(_loginUser, condition.FieldID);
if (condition.Value1.ToString().Trim() == "" && (condition.ConditionOperator == ConditionOperator.IsEqualTo || condition.ConditionOperator == ConditionOperator.IsNotEqualTo))
{
//ticketsview.customers will not be null
if (field.ReportTableFieldID == 251)
{
if (condition.ConditionOperator == ConditionOperator.IsEqualTo)
{
value = " = ''";
}
else if (condition.ConditionOperator == ConditionOperator.IsNotEqualTo)
{
value = " <> ''";
}
}
else
{
if (condition.ConditionOperator == ConditionOperator.IsEqualTo)
{
value = " IS NULL";
}
else if (condition.ConditionOperator == ConditionOperator.IsNotEqualTo)
{
value = " IS NOT NULL";
}
}
}
else
{
switch (field.DataType)
{
case "bit":
if (condition.ConditionOperator == ConditionOperator.IsEqualTo)
{
value = " = ";
}
else
{
value = " <> ";
}
if ((bool)condition.Value1)
{
value = value + "1";
}
else
{
value = value + "0";
}
break;
case "datetime":
if (condition.ConditionOperator == ConditionOperator.IsGreaterThan || condition.ConditionOperator == ConditionOperator.IsLessThan)
{
switch (condition.ConditionOperator)
{
case ConditionOperator.IsGreaterThan:
value = " > ";
break;
case ConditionOperator.IsLessThan:
value = " < ";
break;
default:
break;
}
DateTime date = (DateTime)condition.Value1;
value = value + "'" + DataUtils.DateToUtc(_loginUser, date).ToString("g", CultureInfo.GetCultureInfo("en-US")) + "'";
}
else
{
DateTime date1 = (DateTime)condition.Value1;
DateTime date2;
if (condition.ConditionOperator == ConditionOperator.IsEqualTo || condition.ConditionOperator == ConditionOperator.IsNotEqualTo)
{
date2 = date1.AddDays(1);
}
else
{
date2 = (DateTime)condition.Value2;
}
value = " BETWEEN '" + DataUtils.DateToUtc(_loginUser, date1).ToString("g", CultureInfo.GetCultureInfo("en-US")) + "' AND '" + DataUtils.DateToUtc(_loginUser, date2).ToString("g", CultureInfo.GetCultureInfo("en-US")) + "'";
if (condition.ConditionOperator == ConditionOperator.IsNotInBetween || condition.ConditionOperator == ConditionOperator.IsNotEqualTo)
{
value = " NOT" + value;
}
}
break;
case "int":
if (condition.ConditionOperator != ConditionOperator.IsInBetween && condition.ConditionOperator != ConditionOperator.IsNotInBetween)
{
switch (condition.ConditionOperator)
{
case ConditionOperator.IsEqualTo:
value = " = ";
break;
case ConditionOperator.IsNotEqualTo:
value = " <> ";
break;
case ConditionOperator.IsGreaterThan:
value = " > ";
break;
case ConditionOperator.IsLessThan:
value = " < ";
break;
default:
break;
}
int i = (int)condition.Value1;
value = value + i.ToString();
}
else
{
int i1 = (int)condition.Value1;
int i2 = (int)condition.Value2;
value = " BETWEEN " + i1.ToString() + " AND " + i2.ToString();
if (condition.ConditionOperator == ConditionOperator.IsNotInBetween)
{
value = " NOT" + value;
}
}
break;
case "float":
if (condition.ConditionOperator != ConditionOperator.IsInBetween && condition.ConditionOperator != ConditionOperator.IsNotInBetween)
{
switch (condition.ConditionOperator)
{
case ConditionOperator.IsEqualTo:
value = " = ";
break;
case ConditionOperator.IsNotEqualTo:
value = " <> ";
break;
case ConditionOperator.IsGreaterThan:
value = " > ";
break;
case ConditionOperator.IsLessThan:
value = " < ";
break;
default:
break;
}
double d = (double)condition.Value1;
value = value + d.ToString();
}
else
{
int i1 = (int)condition.Value1;
int i2 = (int)condition.Value2;
value = " BETWEEN " + i1.ToString() + " AND " + i2.ToString();
if (condition.ConditionOperator == ConditionOperator.IsNotInBetween)
{
value = " NOT" + value;
}
}
break;
default:
switch (condition.ConditionOperator)
{
case ConditionOperator.IsEqualTo:
value = " = '" + condition.Value1.ToString() + "'";
break;
case ConditionOperator.IsNotEqualTo:
value = " <> '" + condition.Value1.ToString() + "'";
break;
case ConditionOperator.StartsWith:
value = " LIKE '" + condition.Value1.ToString() + "%'";
break;
case ConditionOperator.EndsWith:
value = " LIKE '%" + condition.Value1.ToString() + "'";
break;
case ConditionOperator.Contains:
value = " LIKE '%" + condition.Value1.ToString() + "%'";
break;
default:
break;
}
break;
}
}
value = tables.FindByReportTableID(field.ReportTableID).TableName + ".[" + field.FieldName + "]" + value;
}
else
{
CustomField field = (CustomField)CustomFields.GetCustomField(_loginUser, condition.FieldID);
if (condition.Value1.ToString().Trim() == "")
{
if (condition.ConditionOperator == ConditionOperator.IsEqualTo)
{
value = " IS NULL";
}
else if (condition.ConditionOperator == ConditionOperator.IsNotEqualTo)
{
value = " IS NOT NULL";
}
}
else
{
switch (field.FieldType)
{
case CustomFieldType.Text:
case CustomFieldType.PickList:
switch (condition.ConditionOperator)
{
case ConditionOperator.IsEqualTo:
value = " = '" + condition.Value1.ToString() + "'";
break;
case ConditionOperator.IsNotEqualTo:
value = " <> '" + condition.Value1.ToString() + "'";
break;
case ConditionOperator.StartsWith:
value = " LIKE '" + condition.Value1.ToString() + "%'";
break;
case ConditionOperator.EndsWith:
value = " LIKE '%" + condition.Value1.ToString() + "'";
break;
case ConditionOperator.Contains:
value = " LIKE '%" + condition.Value1.ToString() + "%'";
break;
default:
break;
}
break;
case CustomFieldType.Date:
case CustomFieldType.Time:
case CustomFieldType.DateTime:
if (condition.ConditionOperator != ConditionOperator.IsInBetween && condition.ConditionOperator != ConditionOperator.IsNotInBetween)
{
switch (condition.ConditionOperator)
{
case ConditionOperator.IsEqualTo:
value = " = ";
break;
case ConditionOperator.IsNotEqualTo:
value = " <> ";
break;
case ConditionOperator.IsGreaterThan:
value = " > ";
break;
case ConditionOperator.IsLessThan:
value = " < ";
break;
default:
break;
}
DateTime date = (DateTime)condition.Value1;
value = value + "'" + DataUtils.DateToUtc(_loginUser, date).ToString("g", _loginUser.CultureInfo) + "'";
}
else
{
DateTime date1 = (DateTime)condition.Value1;
DateTime date2 = (DateTime)condition.Value2;
value = " BETWEEN '" + DataUtils.DateToUtc(_loginUser, date1).ToString("g", _loginUser.CultureInfo) + "' AND '" + DataUtils.DateToUtc(_loginUser, date2).ToString("g", _loginUser.CultureInfo) + "'";
if (condition.ConditionOperator == ConditionOperator.IsNotInBetween)
{
value = " NOT" + value;
}
}
break;
case CustomFieldType.Boolean:
if (condition.ConditionOperator == ConditionOperator.IsEqualTo)
{
value = " = ";
}
else
{
value = " <> ";
}
if ((bool)condition.Value1)
{
value = value + "'True'";
}
else
{
value = value + "'False'";
}
break;
case CustomFieldType.Number:
if (condition.ConditionOperator != ConditionOperator.IsInBetween && condition.ConditionOperator != ConditionOperator.IsNotInBetween)
{
switch (condition.ConditionOperator)
{
case ConditionOperator.IsEqualTo:
value = " = ";
break;
case ConditionOperator.IsNotEqualTo:
value = " <> ";
break;
case ConditionOperator.IsGreaterThan:
value = " > ";
break;
case ConditionOperator.IsLessThan:
value = " < ";
break;
default:
break;
}
int i = (int)condition.Value1;
value = value + i.ToString();
}
else
{
int i1 = (int)condition.Value1;
int i2 = (int)condition.Value2;
value = " BETWEEN " + i1.ToString() + " AND " + i2.ToString();
if (condition.ConditionOperator == ConditionOperator.IsNotInBetween)
{
value = " NOT" + value;
}
}
break;
default:
break;
}
}
value = DataUtils.GetCustomFieldColumn(_loginUser, field, DataUtils.GetReportPrimaryKeyFieldName(field.RefType), true, false) + value;
}
if (builder.Length < 1)
{
builder.Append("((" + value + ")");
}
else
{
builder.Append(con + "(" + value + ")");
}
}
if (builder.Length > 0)
{
builder.Append(")");
}
return(builder.ToString().Trim());
}