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