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