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)); }
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)); }
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 System.Collections.IList GetDataSource(TiposComprobantesList tipo) { NHibernate.ICriteria crit = DBConnection.Session.CreateCriteria(PersistentType); crit.Add(new NHibernate.Expression.EqExpression("Tipo", tipo)); return(crit.List()); }
public IList <NomBodyPart> GetAllSizeIn(int[] array) { NHibernate.ICriteria criteria = Session.CreateCriteria(typeof(NomBodyPart)); criteria.Add(Expression.In("Id", array)); return(criteria.List <NomBodyPart>()); }
/* * Для числовых полей доступны следующие условия: * [] - 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>()); }
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); }