예제 #1
0
        protected virtual async Task <ResponseApi> DefaultQuery(F query, int?page, int?size)
        {
            IQueryable <T> queryable = this.Query();
            //树形列表查询 无条件则可以 树形列表查询 有条件取消
            List <NHibernate.Criterion.AbstractCriterion> wheres = new List <NHibernate.Criterion.AbstractCriterion>();
            List <NHibernate.Criterion.AbstractCriterion> ands   = new List <NHibernate.Criterion.AbstractCriterion>();
            bool res    = this.QueryFilterByOr(ref wheres, query);
            bool andRes = this.QueryFilterByAnd(ands, query);

            if (andRes)
            {
                res = true;
            }
            List <T> data  = null;
            int      total = 0;

            if (res)
            {
                //模糊查询
                using (NHibernate.ISession session = HttpContext.RequestServices.GetService <NHibernate.ISession>())
                {
                    NHibernate.ICriteria criteria = session.CreateCriteria <T>();
                    NHibernate.Criterion.AbstractCriterion abstractCriterion = wheres.Any() ? wheres[0] : ands[0];
                    for (int i = wheres.Any() ? 0 : 1; i < ands.Count; i++)
                    {
                        abstractCriterion &= ands[i];
                    }
                    for (int i = 1; i < wheres.Count; i++)
                    {
                        abstractCriterion |= wheres[i];
                    }

                    criteria = criteria.Add(abstractCriterion);
                    NHibernate.ICriteria pageCriteria = (NHibernate.ICriteria)criteria.Clone();
                    total = pageCriteria.SetProjection(Projections.RowCount()).UniqueResult <int>();
                    OrderBy(ref criteria);
                    data = criteria.SetFirstResult((page.Value - 1) * size.Value).SetMaxResults(size.Value).List <T>().ToList();
                    //数据不规则整理成树形列表信息 如果断层了 断层的下级的成为上级
                    data = this.DataParseIfWhileReference(data, true);
                }
            }
            else
            {
                data  = this.QueryChildFilter(queryable, query).OrderBy(it => it.CreateDate).Skip((page.Value - 1) * size.Value).Take(size.Value).ToList();
                total = queryable.ToFutureValue(it => it.Count()).Value;
                //树形列表查询 去递归引用 automapper 才能正常运行 否则异常
                data = this.DataParseIfWhileReference(data);
            }
            ResultModel <G> result = new ResultModel <G>();

            result.Data   = data.Any() ? ObjectMapper.Map <List <G> >(data) : null;
            result.Result = new PageModel()
            {
                Total = total, Size = size.Value, Page = total == 0 ? 0 : total % size.Value == 0 ? total / size.Value : (total / size.Value + 1)
            };
            ResponseApi response = ResponseApi.Create(GetLanguage(), Code.QuerySuccess);

            response.Data = result;
            return(await Task.FromResult(response));
        }
예제 #2
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="searchExpression"></param>
        /// <param name="searchOrders"></param>
        /// <param name="parentItem"></param>
        public override System.Collections.IEnumerable GetData(ISearchExpression searchExpression, IList <ISearchOrder> searchOrders, object parentItem)
        {
            IList <T> list;
            object    masterItem = parentItem;

            if (masterItem != null)
            {
                if (masterItem.GetType() == typeof(T))
                {
                    list = new List <T>();
                    list.Add((T)masterItem);
                }
                else
                {
                    using (var rep = new Repository(this.RepositoryCfgName))
                    {
                        rep.BeginTransaction();
                        object id = EntityScript.GetPropertyValue(masterItem, TypedEntityMetadata.GenerateEntityInfo(rep.Session.SessionFactory, masterItem.GetType()).IdName);
                        NHibernate.ICriteria criteria = rep.Session.CreateCriteria(typeof(T))
                                                        .Add(NHibernate.Criterion.Expression.IdEq(id));
                        OnCriteriaCreated(criteria);
                        list = criteria.List <T>();

                        rep.CommitTransaction();
                    }
                }
            }
            else
            {
                list = new List <T>();
            }

            return(list);
        }
예제 #3
0
        public override System.Collections.IList GetPropertyValueList(string propertyName, FrameWork.DataBusinessModel.DataModel.Persistent persistent)
        {
            if (propertyName == "TipoContribuyente")
            {
                NHibernate.ICriteria crit = DBConnection.Session.CreateCriteria(typeof(TiposContribuyentes));
                NHibernate.Expression.EqExpression cod01 = new NHibernate.Expression.EqExpression("Codigo", "01");
                NHibernate.Expression.EqExpression cod06 = new NHibernate.Expression.EqExpression("Codigo", "06");
                crit.Add(new NHibernate.Expression.OrExpression(cod01, cod06));
                return(crit.List());
            }

            return(base.GetPropertyValueList(propertyName, persistent));
        }
예제 #4
0
        public virtual NHibernate.ICriteria PrepareOrder(ref NHibernate.ICriteria criteria)
        {
            if (this.IsClientSort())
            {
                criteria.ClearOrders();
                if (string.Compare(this.Order.Trim(), Asc, true) == 0)
                {
                    criteria.AddOrder(NHibernate.Criterion.Order.Asc(this.Sort.Trim()));
                }
                if (string.Compare(this.Order.Trim(), Desc, true) == 0)
                {
                    criteria.AddOrder(NHibernate.Criterion.Order.Desc(this.Sort.Trim()));
                }
            }

            return(criteria);
        }
예제 #5
0
        private void GetTransporters()
        {
            var session = DataConnector.NHibernateConnector.OpenSession();

            NHibernate.ICriteria criteria = session.CreateCriteria(typeof(Models.Logistics.Planning));
            criteria.SetProjection(
                Projections.Distinct(Projections.ProjectionList()
                                     .Add(Projections.Alias(Projections.Property("Transporter"), "Transporter"))
                                     ));

            criteria.SetResultTransformer(
                new NHibernate.Transform.AliasToBeanResultTransformer(typeof(Models.Logistics.Planning)));
            var res = criteria.List <Models.Logistics.Planning>();

            cmbTransporter.DataSource    = res;
            cmbTransporter.DisplayMember = "Transporter";
        }
예제 #6
0
        /*
         *
         * Added two versions of NHibernateHqlQueryStrongTypeRepository.GetSongsByArtist() - GetSongsByArtist2() and GetSongsByArtist3()
         *
         * the idea is:
         * 1. the original version does 3 trips to DB running 3 queries (at very minimum) due to the dot (.) notation / lazy loading of the graphs
         * 2. we can select all the  Song data in one trip to DB running one query.
         *
         * Simple output:
         *
         * Loading from the original GetSongsByArtist.
         * NHibernate: select track0_.TrackId as TrackId2_, track0_.AlbumId as AlbumId2_, track0_.Bytes as Bytes2_, track0_.Composer as Composer2_, track0_.GenreId as GenreId2_, track0_.MediaTypeId as MediaTyp6_2_, track0_.Milliseconds as Millisec7_2_, track0_.Name as Name2_, track0_.UnitPrice as UnitPrice2_ from Track track0_ inner join Album album1_ on track0_.AlbumId=album1_.AlbumId inner join Artist artist2_ on album1_.ArtistId=artist2_.ArtistId where artist2_.Name='Aerosmith'
         * NHibernate: SELECT album0_.AlbumId as AlbumId1_0_, album0_.Title as Title1_0_, album0_.ArtistId as ArtistId1_0_ FROM Album album0_ WHERE album0_.AlbumId=@p0;@p0 = 5 [Type: Int32 (0)]
         * NHibernate: SELECT artist0_.ArtistId as ArtistId0_0_, artist0_.Name as Name0_0_ FROM Artist artist0_ WHERE artist0_.ArtistId=@p0;@p0 = 3 [Type: Int32 (0)]
         * Aerosmith - Big Ones - Walk On Water
         * Aerosmith - Big Ones - Love In An Elevator
         * Aerosmith - Big Ones - Rag Doll
         * Aerosmith - Big Ones - What It Takes
         * Aerosmith - Big Ones - Dude (Looks Like A Lady)
         * Aerosmith - Big Ones - Janie's Got A Gun
         * Aerosmith - Big Ones - Cryin'
         * Aerosmith - Big Ones - Amazing
         * Aerosmith - Big Ones - Blind Man
         * Aerosmith - Big Ones - Deuces Are Wild
         * Aerosmith - Big Ones - The Other Side
         * Aerosmith - Big Ones - Crazy
         * Aerosmith - Big Ones - Eat The Rich
         * Aerosmith - Big Ones - Angel
         * Aerosmith - Big Ones - Livin' On The Edge
         * Loading from the new GetSongsByArtist2.
         * NHibernate: SELECT album1_.Title as y0_, this_.Name as y1_, artist2_.Name as y2_ FROM Track this_ inner join Album album1_ on this_.AlbumId=album1_.AlbumId inner join Artist artist2_ on album1_.ArtistId=artist2_.ArtistId WHERE artist2_.Name = @p0;@p0 = 'Aerosmith' [Type: String (4000)]
         * Aerosmith - Big Ones - Walk On Water
         * Aerosmith - Big Ones - Love In An Elevator
         * Aerosmith - Big Ones - Rag Doll
         * Aerosmith - Big Ones - What It Takes
         * Aerosmith - Big Ones - Dude (Looks Like A Lady)
         * Aerosmith - Big Ones - Janie's Got A Gun
         * Aerosmith - Big Ones - Cryin'
         * Aerosmith - Big Ones - Amazing
         * Aerosmith - Big Ones - Blind Man
         * Aerosmith - Big Ones - Deuces Are Wild
         * Aerosmith - Big Ones - The Other Side
         * Aerosmith - Big Ones - Crazy
         * Aerosmith - Big Ones - Eat The Rich
         * Aerosmith - Big Ones - Angel
         * Aerosmith - Big Ones - Livin' On The Edge
         * Loading from the new GetSongsByArtist3.
         * NHibernate: select album1_.Title as col_0_0_, track0_.Name as col_1_0_, artist2_.Name as col_2_0_ from Track track0_ inner join Album album1_ on track0_.AlbumId=album1_.AlbumId inner join Artist artist2_ on album1_.ArtistId=artist2_.ArtistId where artist2_.Name='Aerosmith'
         * Aerosmith - Big Ones - Walk On Water
         * Aerosmith - Big Ones - Love In An Elevator
         * Aerosmith - Big Ones - Rag Doll
         * Aerosmith - Big Ones - What It Takes
         * Aerosmith - Big Ones - Dude (Looks Like A Lady)
         * Aerosmith - Big Ones - Janie's Got A Gun
         * Aerosmith - Big Ones - Cryin'
         * Aerosmith - Big Ones - Amazing
         * Aerosmith - Big Ones - Blind Man
         * Aerosmith - Big Ones - Deuces Are Wild
         * Aerosmith - Big Ones - The Other Side
         * Aerosmith - Big Ones - Crazy
         * Aerosmith - Big Ones - Eat The Rich
         * Aerosmith - Big Ones - Angel
         * Aerosmith - Big Ones - Livin' On The Edge
         * Press <Enter> to finish.
         *
         */


        public IEnumerable <Song> GetSongsByArtist2(string name)
        {
            using (var session = NHibernateHelper.OpenSession())
            {
                NHibernate.ICriteria qry = session.CreateCriteria <Track>("track")
                                           .CreateAlias("Album", "album")
                                           .CreateAlias("album.Artist", "artist")
                                           .Add(NHibernate.Criterion.Expression.Eq("artist.Name", name))
                                           .SetProjection(NHibernate.Criterion.Projections.ProjectionList()
                                                          .Add(NHibernate.Criterion.Projections.Property("album.Title"), "AlbumName")
                                                          .Add(NHibernate.Criterion.Projections.Property("track.Name"), "SongName")
                                                          .Add(NHibernate.Criterion.Projections.Property("artist.Name"), "ArtistName")
                                                          );

                qry.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean <Song>());
                return(qry.List <Song>());
            }
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="searchExpression"></param>
        /// <param name="searchOrders"></param>
        /// <param name="parentItem"></param>
        /// <returns></returns>
        public override System.Collections.IEnumerable GetData(ISearchExpression searchExpression, IList <ISearchOrder> searchOrders, object parentItem)
        {
            IList <T> list;
            var       child = parentItem as S;

            if (child != null && child.ParentEntity != null)
            {
                if (base.IsReload || NHibernateHelper.IsProxy(child.ParentEntity))
                {
                    //NHibernateHelper.Initialize(child.ParentEntity, child, reload);
                    //List<T> list = new List<T>();
                    //list.Add(child.ParentEntity);

                    using (var rep = new Repository(this.RepositoryCfgName))
                    {
                        rep.BeginTransaction();
                        //rep.Session.Lock(child, NHibernate.LockMode.None);
                        NHibernate.ICriteria criteria = rep.Session.CreateCriteria(typeof(T))
                                                        .Add(NHibernate.Criterion.Expression.IdEq(EntityScript.GetPropertyValue(child, TypedEntityMetadata.GenerateEntityInfo(rep.Session.SessionFactory, typeof(T)).IdName)));
                        OnCriteriaCreated(criteria);
                        list = criteria.List <T>();

                        rep.CommitTransaction();
                    }
                    if (list.Count > 0)
                    {
                        list[0].ChildEntity = child;
                        child.ParentEntity  = list[0];
                    }
                }
                else
                {
                    list = new List <T>();
                    list.Add(child.ParentEntity);
                }
            }
            else
            {
                list = new List <T>();
            }

            return(list);
        }
예제 #8
0
 protected virtual void OrderBy(ref NHibernate.ICriteria criteria)
 {
     criteria = criteria.AddOrder(Order.Asc("CreateDate"));
 }
예제 #9
0
        public override System.Collections.IList GetPropertyValueList(string propertyName, FrameWork.DataBusinessModel.DataModel.Persistent persistent)
        {
            int idEmpresaDefault = GeneralSettings.Instance.IdEmpresaDefault;

            NHibernate.ICriteria critEmpresa = DBConnection.Session.CreateCriteria(typeof(Empresas));
            critEmpresa.Add(new NHibernate.Expression.EqExpression("Id", idEmpresaDefault));
            Empresas empresa = (Empresas)critEmpresa.UniqueResult();

            if (empresa.TipoContribuyente.Codigo != "01" && empresa.TipoContribuyente.Codigo != "06")
            {
                throw new Exception("La empresa " + empresa.RazonSocial + " no tiene asociado un tipo de contribuyente válido." + Environment.NewLine + "Debe ser Responsable Inscripto o Monotributista.");
            }
            if (propertyName == "TipoFactura")
            {
                NHibernate.ICriteria crit = DBConnection.Session.CreateCriteria(typeof(TiposComprobantes));
                crit.Add(new NHibernate.Expression.EqExpression("Tipo", TiposComprobantesList.Factura));
                if (empresa.TipoContribuyente.Codigo == "01")                 //R. Inscripto - a - b - e
                {
                    EqExpression cod01       = new NHibernate.Expression.EqExpression("Codigo", "01");
                    EqExpression cod06       = new NHibernate.Expression.EqExpression("Codigo", "06");
                    EqExpression cod19       = new NHibernate.Expression.EqExpression("Codigo", "19");
                    OrExpression c01or06     = new OrExpression(cod01, cod06);
                    OrExpression c01or06or19 = new OrExpression(c01or06, cod19);
                    crit.Add(c01or06or19);
                }
                else if (empresa.TipoContribuyente.Codigo == "06")                 //Monotributista  -c - e
                {
                    EqExpression cod11   = new NHibernate.Expression.EqExpression("Codigo", "11");
                    EqExpression cod19   = new NHibernate.Expression.EqExpression("Codigo", "19");
                    OrExpression c11or19 = new OrExpression(cod11, cod19);
                    crit.Add(c11or19);
                }
                return(crit.List());
            }
            if (propertyName == "TipoNotaDebito")
            {
                NHibernate.ICriteria crit = DBConnection.Session.CreateCriteria(typeof(TiposComprobantes));
                crit.Add(new NHibernate.Expression.EqExpression("Tipo", TiposComprobantesList.NotaDebito));
                if (empresa.TipoContribuyente.Codigo == "01")                 //R. Inscripto
                {
                    EqExpression cod02       = new NHibernate.Expression.EqExpression("Codigo", "02");
                    EqExpression cod07       = new NHibernate.Expression.EqExpression("Codigo", "07");
                    EqExpression cod20       = new NHibernate.Expression.EqExpression("Codigo", "20");
                    OrExpression c02or07     = new OrExpression(cod02, cod07);
                    OrExpression c02or07or20 = new OrExpression(c02or07, cod20);
                    crit.Add(c02or07or20);
                }
                else if (empresa.TipoContribuyente.Codigo == "06")                 //Monotributista
                {
                    EqExpression cod12   = new NHibernate.Expression.EqExpression("Codigo", "12");
                    EqExpression cod20   = new NHibernate.Expression.EqExpression("Codigo", "20");
                    OrExpression c12or20 = new OrExpression(cod12, cod20);
                    crit.Add(c12or20);
                }
                return(crit.List());
            }
            if (propertyName == "TipoNotaCredito")
            {
                NHibernate.ICriteria crit = DBConnection.Session.CreateCriteria(typeof(TiposComprobantes));
                crit.Add(new NHibernate.Expression.EqExpression("Tipo", TiposComprobantesList.NotaCredito));
                if (empresa.TipoContribuyente.Codigo == "01")                 //R. Inscripto
                {
                    EqExpression cod03       = new NHibernate.Expression.EqExpression("Codigo", "03");
                    EqExpression cod08       = new NHibernate.Expression.EqExpression("Codigo", "08");
                    EqExpression cod21       = new NHibernate.Expression.EqExpression("Codigo", "21");
                    OrExpression c03or08     = new OrExpression(cod03, cod08);
                    OrExpression c03or08or21 = new OrExpression(c03or08, cod21);
                    crit.Add(c03or08or21);
                }
                else if (empresa.TipoContribuyente.Codigo == "06")                 //Monotributista
                {
                    EqExpression cod13   = new NHibernate.Expression.EqExpression("Codigo", "13");
                    EqExpression cod21   = new NHibernate.Expression.EqExpression("Codigo", "21");
                    OrExpression c13or21 = new OrExpression(cod13, cod21);
                    crit.Add(c13or21);
                }
                return(crit.List());
            }

            return(base.GetPropertyValueList(propertyName, persistent));
        }
        public void BusinessListBaseFactory_Fetch_WithCriteria_ReturnsObject()
        {
            _criteria = MockRepository.GenerateStub<NHibernate.ICriteria>();

            ProductList list = new ProductList();
            list.Add(new Product() { Name = "Test" });
            list.Add(new Product() { Name = "Test1" });
            list.Add(new Product() { Name = "Test2" });

            NHibernate.Criterion.SimpleExpression expression = Restrictions.Eq("Name", "Test");
            _criteria.Expect(c => c.Add(expression)).Return(_criteria);
            _repository.Expect(r => r.CreateCriteria()).Return(_criteria);
            _criteria.Expect(c => c.List<Product>()).Return(list);

            ProductList products = _factory.Fetch(new SingleCriteria<ProductList, string>("Test"));

            _repository.AssertWasCalled(r => r.CreateCriteria());
            _criteria.AssertWasCalled(c => c.List<Product>());
            Assert.AreEqual(3, products.Count);
        }
예제 #11
0
 public System.Collections.IList GetDataSource(TiposComprobantesList tipo)
 {
     NHibernate.ICriteria crit = DBConnection.Session.CreateCriteria(PersistentType);
     crit.Add(new NHibernate.Expression.EqExpression("Tipo", tipo));
     return(crit.List());
 }
예제 #12
0
 public IList <NomBodyPart> GetAllSizeIn(int[] array)
 {
     NHibernate.ICriteria criteria = Session.CreateCriteria(typeof(NomBodyPart));
     criteria.Add(Expression.In("Id", array));
     return(criteria.List <NomBodyPart>());
 }
예제 #13
0
        /*
         * Для числовых полей доступны следующие условия:
         * []  - null
         * [!] - не null
         * [>] - больше
         * [<] - меньше
         * [!=] - не равно
         * [>=] - больше или равно
         * [<=] - меньше или равно
         * [in] - принадлежит множеству
         * [!in]- не принадлежит множеству
         */
        public IList <T> GetByCriteria(Dictionary <string, object> queryParams, Dictionary <string, object> orderParams = null)
        {
            string alias;
            IList  aliases = new List <string>();

            NHibernate.ICriteria criteria = Session.CreateCriteria(typeof(T));
            foreach (KeyValuePair <string, object> kvp in queryParams)
            {
                string kvpKey = kvp.Key;
                string param  = null;
                if (kvpKey.StartsWith("["))
                {
                    param = kvpKey.Substring(0, kvpKey.IndexOf("]") + 1);
                    //Убираем спец. символы
                    if ((param == "[!]") || (param == "[]") || (param == "[!=]") || (param == "[>]") || (param == "[<]") || (param == "[>=]") || (param == "[<=]") || (param == "[in]") || (param == "[!in]"))
                    {
                        kvpKey = kvpKey.Substring(kvpKey.IndexOf("]") + 1);
                    }
                }
                if (kvpKey.Contains("."))
                {
                    alias = kvpKey.Substring(0, kvpKey.IndexOf("."));
                    if (!aliases.Contains(alias))
                    {
                        aliases.Add(alias);
                        criteria.CreateAlias(alias, alias);
                    }
                }
                if (param == null)
                {
                    criteria.Add(Restrictions.Eq(kvpKey, kvp.Value));
                }
                else
                {
                    if (param == "[]")
                    {
                        criteria.Add(Restrictions.IsNull(kvpKey));
                    }
                    else
                    if (param == "[!]")
                    {
                        criteria.Add(Restrictions.Not(Restrictions.IsNull(kvpKey)));
                    }
                    else
                    if (param == "[!=]")
                    {
                        criteria.Add(Restrictions.Not(Restrictions.Eq(kvpKey, kvp.Value)));
                    }
                    else
                    if (param == "[>]")
                    {
                        criteria.Add(Restrictions.Gt(kvpKey, kvp.Value));
                    }
                    else
                    if (param == "[in]")
                    {
                        ICollection collection;
                        String      val = (string)kvp.Value;
                        if (val.Contains("'"))
                        {
                            collection = GetStringCollection((string)kvp.Value);
                        }
                        else
                        {
                            collection = GetIntCollection((string)kvp.Value);
                        }
                        criteria.Add(Restrictions.In(kvpKey, collection));
                    }
                    else
                    if (param == "[!in]")
                    {
                        ICollection collection;
                        String      val = (string)kvp.Value;
                        if (val.Contains("'"))
                        {
                            collection = GetStringCollection((string)kvp.Value);
                        }
                        else
                        {
                            collection = GetIntCollection((string)kvp.Value);
                        }
                        criteria.Add(Restrictions.Not(Restrictions.In(kvpKey, collection)));
                    }
                    else
                    if (param == "[<]")
                    {
                        criteria.Add(Restrictions.Lt(kvpKey, kvp.Value));
                    }
                    else
                    if (param == "[>=]")
                    {
                        criteria.Add(Restrictions.Ge(kvpKey, kvp.Value));
                    }
                    else
                    if (param == "[<=]")
                    {
                        criteria.Add(Restrictions.Le(kvpKey, kvp.Value));
                    }
                    else
                    {
                        criteria.Add(Restrictions.Eq(kvpKey, kvp.Value));
                    }
                }
            }
            if (orderParams != null)
            {
                foreach (KeyValuePair <string, object> ovp in orderParams)
                {
                    if (ovp.Key.Contains("."))
                    {
                        alias = ovp.Key.Substring(0, ovp.Key.IndexOf("."));
                        if (!aliases.Contains(alias))
                        {
                            aliases.Add(alias);
                            criteria.CreateAlias(alias, alias);
                        }
                    }
                    criteria.AddOrder(new Order(ovp.Key, (bool)ovp.Value));
                }
            }
            return(criteria.List <T>());
        }
예제 #14
0
 public virtual NHibernate.ICriteria PrepareCriteria(ref NHibernate.ICriteria criteria)
 {
     return(criteria);
 }
예제 #15
0
        protected override NHibernate.ICriteria ParseCriteria <Y>(SearchCriteria searchCriteria)
        {
            NHibernate.ICriteria         criteria = base.ParseCriteria <Y>(searchCriteria);
            SearchEmpByOrderInfoCriteria searchEmpByOrderCriteria = searchCriteria as SearchEmpByOrderInfoCriteria;

            if (searchEmpByOrderCriteria != null)
            {
                //criteria.CreateAlias("[Order]", "o", NHibernate.SqlCommand.JoinType.LeftOuterJoin);
                StringBuilder sb = new StringBuilder();
                if (searchEmpByOrderCriteria.SearchBy == SearchEmpByOrderInfoCriteria.SEARCH_BY_CASE_MANAGER)
                {
                    sb.Append(" {alias}.ID IN (Select o.CaseManagerID from [Order] o");
                    sb.Append(" WHERE o.CaseManagerID IS NOT NULL  ");
                }
                else
                {
                    sb.Append(" {alias}.ID IN (Select o.OrderTakerID from [Order] o");
                    sb.Append(" WHERE o.OrderTakerID IS NOT NULL ");
                }
                if (searchEmpByOrderCriteria.FromOrderDate != null &&
                    searchEmpByOrderCriteria.FromOrderDate > System.Data.SqlTypes.SqlDateTime.MinValue.Value &&
                    searchEmpByOrderCriteria.FromOrderDate < System.Data.SqlTypes.SqlDateTime.MaxValue.Value)
                {
                    sb.Append(" and o.CreatedDate > '" + searchEmpByOrderCriteria.FromOrderDate.ToShortDateString() + " " + searchEmpByOrderCriteria.FromOrderDate.ToShortTimeString() + "'");
                }
                if (searchEmpByOrderCriteria.ToOrderDate != null &&
                    searchEmpByOrderCriteria.ToOrderDate > System.Data.SqlTypes.SqlDateTime.MinValue.Value &&
                    searchEmpByOrderCriteria.ToOrderDate < System.Data.SqlTypes.SqlDateTime.MaxValue.Value)
                {
                    sb.Append(" and o.CreatedDate < '" + searchEmpByOrderCriteria.ToOrderDate.ToShortDateString() + " " + searchEmpByOrderCriteria.ToOrderDate.ToShortTimeString() + "'");
                }
                if (searchEmpByOrderCriteria.OrderStatus != 0)
                {
                    sb.Append((" and o.OrderStatusID=" + searchEmpByOrderCriteria.OrderStatus.ToString()));
                }
                else if ((searchEmpByOrderCriteria.ListOfOrderStatus != null) && (searchEmpByOrderCriteria.ListOfOrderStatus.Count > 0))
                {
                    String s = "";
                    foreach (Object o in searchEmpByOrderCriteria.ListOfOrderStatus)
                    {
                        if (!String.IsNullOrEmpty(s))
                        {
                            s += ",";
                        }
                        s += o.ToString();
                    }
                    s = "(" + s + ")";
                    sb.Append((" and o.OrderStatusID in " + s));
                }
                sb.Append(")");
                criteria.Add(Expression.Sql(sb.ToString()));

                //if (searchEmpByOrderCriteria.SearchBy == SearchEmpByOrderInfoCriteria.SEARCH_BY_CASE_MANAGER)
                //{
                //  criteria.CreateAlias("EmployeeTitle", "empTitle", NHibernate.SqlCommand.JoinType.LeftOuterJoin);
                //}
                //if (searchEmpByOrderCriteria.SearchBy == SearchEmpByOrderInfoCriteria.SEARCH_BY_ORDER_REP)
                //{
                //  criteria.CreateAlias("Department", "dept", NHibernate.SqlCommand.JoinType.LeftOuterJoin);
                //}

                criteria.Add(Expression.Sql(sb.ToString()));

                if (searchEmpByOrderCriteria.SearchBy == SearchEmpByOrderInfoCriteria.SEARCH_BY_CASE_MANAGER)
                {
                    criteria.Add(Expression.Eq("EmployeeTitle.ID", EmployeeTitle.EMPLOYEE_TITLE.Case_Manager));
                }

                if (searchEmpByOrderCriteria.SearchBy == SearchEmpByOrderInfoCriteria.SEARCH_BY_ORDER_REP)
                {
                    if (searchEmpByOrderCriteria.DepartmentID != Guid.Empty)
                    {
                        criteria.Add(Expression.Eq("Department.ID", searchEmpByOrderCriteria.DepartmentID));
                    }
                }
            }

            return(criteria);
        }