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