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