Ejemplo n.º 1
0
        public static string GET_FILTERS_SQL(FilterList filters, string tableAlias, CultureInfo cultureInfo, Dictionary <String, ForeignField> foreignFields = null)
        {
            string queryPattern      = "(TRUE {0})";
            string stringPattern     = @"{0}.""{1}"" {2} '{3}'";
            string intPattern        = @"{0}.""{1}"" {2} {3}";
            string likePattern       = @"{0}.""{1}"" {2} '%{3}%'";
            string likeIntPattern    = @"TRIM(TO_CHAR({0}.""{1}"", '999999999999')) {2} '%{3}%'";
            string likeDatePattern   = @"TO_CHAR({0}.""{1}"", '" + cultureInfo.DateTimeFormat.ShortDatePattern + "') {2} '%{3}%'";
            string StartPattern      = @"{0}.""{1}"" {2} '{3}%'";
            string StartIntPattern   = @"TRIM(TO_CHAR({0}.""{1}"", '999999999999')) {2} '{3}%'";
            string StartDatePattern  = @"TO_CHAR({0}.""{1}"", '" + cultureInfo.DateTimeFormat.ShortDatePattern + "') {2} '{3}%'";
            string betweenPattern    = @"{0}.""{1}"" {2} '{3}' AND '{4}'";
            string betweenIntPattern = @"{0}.""{1}"" {2} {3} AND {4}";
            string query             = string.Empty;

            if (filters != null)
            {
                foreach (FilterItem item in filters)
                {
                    switch (item.FilterProperty)
                    {
                    case IFilterProperty.All:
                    {
                        query += @" 
									AND (FALSE "                                    ;

                        ICollection <Column> cols = nHManager.Instance.GetTableColumns(item.EntityType);
                        foreach (Column col in nHManager.Instance.GetTableColumns(item.EntityType))
                        {
                            if (col.Type is NHibernate.Type.StringType)
                            {
                                query += " OR ";
                                query += String.Format(likePattern, tableAlias, col.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
                            }
                            else if ((col.Type is NHibernate.Type.DateTimeType) ||
                                     (col.Type is NHibernate.Type.DateType))
                            {
                                query += " OR ";
                                query += String.Format(likeDatePattern, tableAlias, col.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
                            }
                            else if ((col.Type is NHibernate.Type.Int32Type) ||
                                     (col.Type is NHibernate.Type.Int64Type))
                            {
                                query += " OR ";
                                query += String.Format(likeIntPattern, tableAlias, col.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
                            }
                        }

                        if (foreignFields != null)
                        {
                            NHibernate.Mapping.Column fcol;
                            foreach (KeyValuePair <String, ForeignField> field in foreignFields)
                            {
                                fcol = field.Value.Column;
                                if (fcol != null)
                                {
                                    if (fcol.Type is NHibernate.Type.StringType)
                                    {
                                        query += " OR ";
                                        query += String.Format(likePattern, field.Value.TableAlias, fcol.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
                                    }
                                    else if ((fcol.Type is NHibernate.Type.DateTimeType) ||
                                             (fcol.Type is NHibernate.Type.DateType))
                                    {
                                        query += " OR ";
                                        query += String.Format(likeDatePattern, field.Value.TableAlias, fcol.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
                                    }
                                    else if ((fcol.Type is NHibernate.Type.Int32Type) ||
                                             (fcol.Type is NHibernate.Type.Int64Type))
                                    {
                                        query += " OR ";
                                        query += String.Format(likeIntPattern, field.Value.TableAlias, fcol.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
                                    }
                                }
                            }
                        }
                        query += ")";
                    }
                    break;

                    case IFilterProperty.ByParamenter:
                    {
                        Column col = nHManager.Instance.GetTableColumn(item.EntityType, item.Property);
                        switch (item.Operation)
                        {
                        case Operation.Contains:

                            query += " AND ";

                            if (col.Type is NHibernate.Type.StringType)
                            {
                                query += String.Format(likePattern, tableAlias, col.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
                            }
                            else if ((col.Type is NHibernate.Type.DateTimeType) ||
                                     (col.Type is NHibernate.Type.DateType))
                            {
                                query += String.Format(likeDatePattern, tableAlias, col.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
                            }
                            else if ((col.Type is NHibernate.Type.Int32Type) ||
                                     (col.Type is NHibernate.Type.Int64Type))
                            {
                                query += String.Format(likeIntPattern, tableAlias, col.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
                            }

                            break;

                        case Operation.StartsWith:

                            query += " AND ";

                            if (col.Type is NHibernate.Type.StringType)
                            {
                                query += String.Format(StartPattern, tableAlias, col.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
                            }
                            else if ((col.Type is NHibernate.Type.DateTimeType) ||
                                     (col.Type is NHibernate.Type.DateType))
                            {
                                query += String.Format(StartDatePattern, tableAlias, col.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
                            }
                            else if ((col.Type is NHibernate.Type.Int32Type) ||
                                     (col.Type is NHibernate.Type.Int64Type))
                            {
                                query += String.Format(StartIntPattern, tableAlias, col.Name, GET_OPERATOR(item.Operation), item.ValueToString.ToLower());
                            }

                            break;

                        case Operation.Between:

                            query += " AND ";

                            // TODO : Aggregate second item value to the filter
                            // Necessary second item value in order to this work
                            if ((item.TableColumn.Type is NHibernate.Type.DateTimeType) ||
                                (item.TableColumn.Type is NHibernate.Type.DateType) ||
                                (item.TableColumn.Type is NHibernate.Type.StringType))
                            {
                                query += String.Format(betweenPattern, tableAlias, item.TableColumn.Name, GET_OPERATOR(item.Operation), item.Value.ToString(), item.Value.ToString());
                            }
                            else
                            {
                                query += String.Format(betweenIntPattern, tableAlias, item.TableColumn.Name, GET_OPERATOR(item.Operation), item.Value, item.Value);
                            }

                            break;

                        default:

                            query += " AND ";

                            if ((item.TableColumn.Type is NHibernate.Type.DateTimeType) ||
                                (item.TableColumn.Type is NHibernate.Type.DateType) ||
                                (item.TableColumn.Type is NHibernate.Type.StringType))
                            {
                                query += String.Format(stringPattern, tableAlias, item.TableColumn.Name, GET_OPERATOR(item.Operation), item.Value.ToString());
                            }
                            else
                            {
                                query += String.Format(intPattern, tableAlias, item.TableColumn.Name, GET_OPERATOR(item.Operation), item.Value);
                            }

                            break;
                        }
                    }
                    break;
                    }
                }
            }

            return(String.Format(queryPattern, query));
        }
Ejemplo n.º 2
0
 public static string GET_FILTERS_SQL(FilterList filters, string tableAlias, Dictionary <String, ForeignField> foreignFields = null)
 {
     return(GET_FILTERS_SQL(filters, tableAlias, CultureInfo.CurrentCulture, foreignFields));
 }