private IQueryOver <WorkflowActivity, WorkflowActivity> FilterBySearchField(IQueryOver <WorkflowActivity, WorkflowActivity> queryOver)
{
WorkflowRepository workflowRepository = null;
try
{
// Filtro sul nome del wf
if (!string.IsNullOrEmpty(WorkflowActivityName))
{
queryOver.WhereRestrictionOn(x => x.Name).IsLike(WorkflowActivityName, MatchMode.Anywhere);
}
if (!string.IsNullOrEmpty(WorkflowInstanceName))
{
queryOver.WhereRestrictionOn(x => workflowRepository.Name).IsLike(WorkflowInstanceName, MatchMode.Anywhere);
}
if (!string.IsNullOrEmpty(WorkflowSubject))
{
queryOver.WhereRestrictionOn(x => x.Subject).IsLike(WorkflowSubject, MatchMode.Anywhere);
}
}
catch (Exception ex)
{
throw new DocSuiteException("Errore in FilterBySearchFields", ex);
}
return(queryOver);
}
public override IQueryOver <CuttingOrder, CuttingOrder> Filtering(IQueryOver <CuttingOrder, CuttingOrder> query, CuttingOrderFilter filter)
{
query.FindByRn(filter.Rn);
query.IsBetween(x => x.AssumeDate, filter.AssumeDate);
query.IsBetween(x => x.CreationDate, filter.CreationDate);
query.IsBetween(x => x.DateDocumentIntegration, filter.DateDocumentIntegration);
if (!string.IsNullOrWhiteSpace(filter.Note))
{
query.WhereRestrictionOn(x => x.Note)
.IsLike(filter.Note, MatchMode.Start);
}
if (filter.Numb != null)
{
query.Where(x => x.Numb == filter.Numb);
}
if (!string.IsNullOrWhiteSpace(filter.Pref))
{
query.WhereRestrictionOn(x => x.Pref)
.IsLike(filter.Pref, MatchMode.Start);
}
return(query);
}
private IQueryOver <Desk, Desk> FilterBySearchField(IQueryOver <Desk, Desk> queryOver)
{
try
{
// Filtro sul nome del tavolo
if (!string.IsNullOrEmpty(DeskName))
{
queryOver.WhereRestrictionOn(x => x.Name).IsLike(DeskName, MatchMode.Anywhere);
}
if (!string.IsNullOrEmpty(DeskDescription))
{
queryOver.WhereRestrictionOn(x => x.Description).IsLike(DeskDescription, MatchMode.Anywhere);
}
if (DeskNotExprired)
{
queryOver.Where(x => x.ExpirationDate >= new DateTime(DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day, 0, 0, 0));
}
if (DeskContainerId != default(int))
{
queryOver.Where(desk => container.Id == DeskContainerId);
}
}
catch (Exception ex)
{
throw new DocSuiteException("Errore in FilterByUserPermission", ex);
}
return(queryOver);
}
private IQueryOver <CustomerGroup, CustomerGroup> getCustomerGroupsCriterias(IQueryOver <CustomerGroup, CustomerGroup> queryCustomer, CustomerGroupSearchCriteria criteria)
{
var dbProfile = Session.Load <Profile>(SecurityInfo.SessionData.Profile.GlobalId);
queryCustomer = queryCustomer.Where(x => x.Profile == dbProfile);
if (criteria.MembersCriteria == CustomerGroupMembersCriteria.WithMembersOnly)
{
queryCustomer = queryCustomer.WhereRestrictionOn(x => x.Customers).IsNotEmpty;
}
else if (criteria.MembersCriteria == CustomerGroupMembersCriteria.WithoutMembersOnly)
{
queryCustomer = queryCustomer.WhereRestrictionOn(x => x.Customers).IsEmpty;
}
return(queryCustomer);
}
Filtering(IQueryOver <DictionaryMechanicalIndicator, DictionaryMechanicalIndicator> query, DictionaryMechanicalIndicatorFilter filter)
{
query.FindByRn(filter.Rn);
if (!string.IsNullOrWhiteSpace(filter.Code))
{
query.WhereRestrictionOn(x => x.Code).IsLike(filter.Code.ReplaceStar());
}
if (!string.IsNullOrWhiteSpace(filter.Name))
{
query.WhereRestrictionOn(x => x.Name).IsLike(filter.Name.ReplaceStar());
}
return(query);
}
/// <summary>
/// The filtering.
/// </summary>
/// <param name="query">
/// The query.
/// </param>
/// <param name="filter">
/// The filter.
/// </param>
/// <returns>
/// The <see cref="IQueryOver"/>.
/// </returns>
public override IQueryOver <PlanAndSpecification, PlanAndSpecification> Filtering(IQueryOver <PlanAndSpecification, PlanAndSpecification> query, PlanAndSpecificationFilter filter)
{
query.FindByRn(filter.Rn);
query.IsBetween(x => x.BeginDate, filter.BeginDate);
query.IsBetween(x => x.EndDate, filter.EndDate);
if (filter.ModificationNomenclature != null)
{
if (!string.IsNullOrWhiteSpace(filter.ModificationNomenclature.Code))
{
query.JoinQueryOver(x => x.ModificationNomenclature, JoinType.LeftOuterJoin);
//NomenclatureNumberModification nommodifAlias = null;
//query.JoinAlias((x) => filter.ModificationNomenclature, () => nommodifAlias, JoinType.LeftOuterJoin);
query.WhereRestrictionOn(x => x.ModificationNomenclature.Code)
.IsLike(filter.ModificationNomenclature.Code.ReplaceStar());
}
}
if (filter.PersonalAccount != null)
{
// query.JoinQueryOver(x => x.PersonalAccount, JoinType.LeftOuterJoin);
query.Where(x => x.PersonalAccount.Rn == filter.PersonalAccount.Rn);
}
return(query);
}
/// <summary>
/// Crea i criteri base per l'interrogazione
/// </summary>
/// <param name="queryOver"></param>
/// <returns></returns>
protected override IQueryOver <PECMailLog, PECMailLog> DecorateCriteria(IQueryOver <PECMailLog, PECMailLog> queryOver)
{
if (PECMail != null)
{
queryOver.Where(x => x.Mail == PECMail);
}
if (PECMailIDIn != null && PECMailIDIn.Count > 0)
{
queryOver.AndRestrictionOn(x => x.Id).IsIn(PECMailIDIn);
}
if (!string.IsNullOrEmpty(LogType))
{
queryOver.Where(x => x.Type == LogType);
}
if (!string.IsNullOrEmpty(DescriptionLike))
{
queryOver.WhereRestrictionOn(x => x.Description).IsInsensitiveLike(DescriptionLike, MatchMode.Anywhere);
}
innerDateTimeRange();
if (DateFrom.HasValue)
{
queryOver.Where(x => x.Date > DateFrom);
}
if (DateTo.HasValue)
{
queryOver.Where(x => x.Date < DateTo.Value.AddDays(1).AddSeconds(-1));
}
return(queryOver);
}
public IPage <UserGroupMembership> FindOtherMembershipsInUsersGroups(IPageable pageRequest, User user, IList <UserGroupMembershipType> membershipTypes = null)
{
Require.NotNull(pageRequest, "pageRequest");
Require.NotNull(user, "user");
HibernateDelegate <IPage <UserGroupMembership> > finder = delegate(ISession session) {
IQueryOver <UserGroupMembership, UserGroupMembership> queryOver = session.QueryOver <UserGroupMembership>();
/*Die Mitgliedschaften des Nutzers nicht!*/
queryOver.Where(membership => membership.User != user);
/*Nur die der Gruppen des Nutzers*/
var userGroupQueryOver = QueryOver.Of <UserGroupMembership>().Where(mem => mem.User == user).And(mem => mem.MembershipType == UserGroupMembershipType.Administrator || mem.MembershipType == UserGroupMembershipType.Member).Select(mem => mem.UserGroup.Id);
queryOver.WithSubquery.WhereProperty(membership => membership.UserGroup).In(userGroupQueryOver);
if (membershipTypes != null && membershipTypes.Any())
{
/*Einschränkung der Mitgliedschaften*/
queryOver.WhereRestrictionOn(memberShip => memberShip.MembershipType).IsIn(membershipTypes.ToArray());
}
return(FindPage(queryOver, pageRequest));
};
return(HibernateTemplate.Execute(finder));
}
public IPage <Peanut> FindPeanutsInGroups(IPageable pageRequest, IList <UserGroup> userGroups, DateTime?from, DateTime?to)
{
Require.NotNull(userGroups, "userGroups");
Require.NotNull(pageRequest, "pageRequest");
HibernateDelegate <IPage <Peanut> > finder = delegate(ISession session) {
IQueryOver <Peanut, Peanut> queryOver = session.QueryOver <Peanut>();
queryOver.WhereRestrictionOn(p => p.UserGroup).IsIn(userGroups.ToList());
if (from != null)
{
/*Am oder nach dem ab Datum*/
queryOver.And(peanut => peanut.Day >= from);
}
if (to != null)
{
/*Nicht nach dem To Datum*/
queryOver.AndNot(peanut => peanut.Day > to);
}
return(FindPage(queryOver, pageRequest));
};
return(HibernateTemplate.Execute(finder));
}
/// <summary>
/// The filtering.
/// </summary>
/// <param name="query">
/// The query.
/// </param>
/// <param name="filter">
/// The filter.
/// </param>
/// <returns>
/// The <see cref="IQueryOver"/>.
/// </returns>
public override IQueryOver <StoreGasStationOilDepot, StoreGasStationOilDepot> Filtering(
IQueryOver <StoreGasStationOilDepot, StoreGasStationOilDepot> query, StoreGasStationOilDepotFilter filter)
{
query.FindByRn(filter.Rn);
if (filter.AzsNumber != null)
{
query.WhereRestrictionOn(x => x.Number).IsLike(filter.AzsNumber.ReplaceStar());
}
if (filter.AzsName != null)
{
query.WhereRestrictionOn(x => x.Name).IsLike(filter.AzsName.ReplaceStar());
}
return(query);
}
/// <summary>
/// The filtering.
/// </summary>
/// <param name="query">
/// The query.
/// </param>
/// <param name="filter">
/// The filter.
/// </param>
/// <returns>
/// The <see cref="IQueryOver"/>.
/// </returns>
public override IQueryOver <TypeOfDocument, TypeOfDocument> Filtering(IQueryOver <TypeOfDocument, TypeOfDocument> query, TypeOfDocumentFilter filter)
{
query.FindByRn(filter.Rn);
if (!string.IsNullOrWhiteSpace(filter.DocCode))
{
query.WhereRestrictionOn(x => x.DocumentCode).IsLike(filter.DocCode.ReplaceStar());
}
if (!string.IsNullOrWhiteSpace(filter.DocName))
{
query.WhereRestrictionOn(x => x.DocumentName).IsLike(filter.DocName.ReplaceStar());
}
query = query.OrderBy(x => x.DocumentCode).Asc;
return(query);
}
protected override IQueryOver <DeskStoryBoard, DeskStoryBoard> DecorateCriteria(IQueryOver <DeskStoryBoard, DeskStoryBoard> queryOver)
{
queryOver.JoinAlias(x => x.Desk, () => desk)
.Left.JoinAlias(o => o.DeskDocumentVersion, () => deskDocumentVersion)
.Left.JoinAlias(() => deskDocumentVersion.DeskDocument, () => deskDocument);
if (DeskId.HasValue)
{
SetDeskIdFilter(queryOver);
}
if (DeskDocumentId.HasValue && FindDocumentComments)
{
SetDocumentIdFilter(queryOver);
}
//Filtro per autore commento
if (!string.IsNullOrEmpty(this.Author))
{
queryOver.WhereRestrictionOn(x => x.Author).IsLike(this.Author, MatchMode.Anywhere);
}
//Filtro per descrizione commento
if (!string.IsNullOrEmpty(this.Description))
{
queryOver.WhereRestrictionOn(x => x.Comment).IsLike(this.Description, MatchMode.Anywhere);
}
//Filtro per data
if (this.DateFrom.HasValue && !this.DateFrom.Equals(DateTime.MinValue))
{
queryOver.Where(x => x.DateBoard >= this.DateFrom.Value);
}
if (this.DateTo.HasValue && !this.DateTo.Equals(DateTime.MinValue))
{
queryOver.Where(x => x.DateBoard <= this.DateTo.Value);
}
if (this.CommentType.HasValue)
{
queryOver.Where(x => x.BoardType == this.CommentType.Value);
}
return(queryOver);
}
public static IQueryOver <TEntity, TEntity> IsInEmpty <TEntity, U>(
this IQueryOver <TEntity, TEntity> query,
Expression <Func <TEntity, object> > property,
IList <U> parameters)
{
return
(query.WhereRestrictionOn(property)
.IsIn(parameters.ToArray()));
}
/// <summary>
/// The filtering.
/// </summary>
/// <param name="query">
/// The query.
/// </param>
/// <param name="filter">
/// The filter.
/// </param>
/// <returns>
/// The <see cref="IQueryOver"/>.
/// </returns>
public override IQueryOver <Acatalog, Acatalog> Filtering(IQueryOver <Acatalog, Acatalog> query, AcatalogFilter filter)
{
if (!string.IsNullOrEmpty(filter.Name))
{
query.WhereRestrictionOn(x => x.Name).IsLike(filter.Name);
}
if (filter.SectionOfSystem != null && !string.IsNullOrWhiteSpace(filter.SectionOfSystem.UnitCode))
{
query.JoinQueryOver(x => x.SectionOfSystem, JoinType.LeftOuterJoin)
.Where(x => x.Rn == filter.SectionOfSystem.UnitCode);
}
if (filter.UserPrivilege != null)
{
UserPrivilege userPrivilegeALias = null;
query.JoinAlias(x => x.UserPrivileges, () => userPrivilegeALias);
query.JoinQueryOver(() => userPrivilegeALias.Role, JoinType.InnerJoin);
if (filter.UserPrivilege.Role != null)
{
if (filter.UserPrivilege.Role.Rn != 0)
{
query.Where(() => userPrivilegeALias.Role.Rn == filter.UserPrivilege.Role.Rn);
}
if (filter.UserPrivilege.Role.UserRoles != null)
{
UserRole userRoleAlias = null;
query.JoinAlias(() => userPrivilegeALias.Role.UserRoles, () => userRoleAlias)
.EqualsUser(x => userRoleAlias.UserList.AUTHID);
// var fil = Restrictions.EqProperty(
// Projections.SqlFunction("User", NHibernateUtil.String),
// Projections.Property<UserRole>(x => userRoleAlias.UserList.AUTHID));
// query.Where(fil);
}
}
if (filter.UserPrivilege.UnitFunction != null)
{
UnitFunction unitfuncAlias = null;
query.JoinAlias(() => userPrivilegeALias.UnitFunctions, () => unitfuncAlias);
query.Where(x => unitfuncAlias.Standard == filter.UserPrivilege.UnitFunction.Standard);
if (filter.UserPrivilege.UnitFunction.SectionOfSystemUnitcode != null)
{
query.JoinQueryOver(() => unitfuncAlias.SectionOfSystemUnitcode, JoinType.InnerJoin);
if (!string.IsNullOrWhiteSpace(filter.UserPrivilege.UnitFunction.SectionOfSystemUnitcode.UnitCode))
{
query.Where(x => unitfuncAlias.SectionOfSystemUnitcode.Rn == filter.UserPrivilege.UnitFunction.SectionOfSystemUnitcode.UnitCode);
}
}
}
}
return(query);
}
static public PagedResult <DTO> ToExPagedResults <DTO, Model>(this IQueryOver <Model, Model> query, PartialRetrievingInfo retrievingInfo, IQueryOver <Model, Model> idQuery, Func <IEnumerable <Model>, DTO[]> mappingMethod = null)
where Model : FMGlobalObject
{
IEnumerable <Model> queryEnumerable = null;
int count = 0;
query = query.TransformUsing(Transformers.DistinctRootEntity);
if (retrievingInfo.PageSize > PartialRetrievingInfo.AllElementsPageSize)
{
ICriteria countCriteria = CriteriaTransformer.Clone(idQuery.RootCriteria);
countCriteria.SetProjection(Projections.CountDistinct("GlobalId"));
IFutureValue <int> rowCountQuery = countCriteria.FutureValue <int>();
idQuery = (QueryOver <Model, Model>)idQuery.ApplyPaging(retrievingInfo);
var ids = idQuery.Select(Projections.Distinct(Projections.Property("GlobalId"))).Future <Guid>();
count = rowCountQuery.Value;
query = query.WhereRestrictionOn(x => x.GlobalId).IsIn(ids.ToList());
queryEnumerable = query.List();
}
else
{
var ids = idQuery.Select(Projections.Distinct(Projections.Property("GlobalId"))).Future <Guid>();
query = query.WhereRestrictionOn(x => x.GlobalId).IsIn(ids.ToList());
queryEnumerable = query.List();
count = queryEnumerable.Count();
}
DTO[] list = null;
if (mappingMethod == null)
{
var temp = queryEnumerable.ToList();
list = Mapper.Map <IEnumerable <Model>, DTO[]>(temp);
}
else
{
list = mappingMethod(queryEnumerable);
}
PagedResult <DTO> res = new PagedResult <DTO>(list, count, retrievingInfo.PageIndex);
res.RetrievedDateTime = DateTime.UtcNow;
Log.WriteInfo("Paged result. AllCount:{0},PageIndex:{1},PageSize:{2}", res.AllItemsCount, res.PageIndex, res.Items.Count);
return(res);
}
private static void SetUpFilter(string filter, IQueryOver <Product, Product> query)
{
if (string.IsNullOrEmpty(filter))
{
return;
}
query
.WhereRestrictionOn(x => x.Name)
.IsInsensitiveLike($"%{filter}%");
}
public IPage <UserGroupMembership> FindMembershipsByGroups(IPageable pageRequest, IList <UserGroup> userGroups,
IList <UserGroupMembershipType> membershipTypes = null)
{
Require.NotNull(pageRequest, "pageRequest");
Require.NotNull(userGroups, "userGroup");
HibernateDelegate <IPage <UserGroupMembership> > finder = delegate(ISession session) {
IQueryOver <UserGroupMembership, UserGroupMembership> queryOver = session.QueryOver <UserGroupMembership>();
queryOver.WhereRestrictionOn(membership => membership.UserGroup).IsIn(userGroups.ToList());
if (membershipTypes != null && membershipTypes.Any())
{
queryOver.WhereRestrictionOn(memberShip => memberShip.MembershipType).IsIn(membershipTypes.ToArray());
}
return(FindPage(queryOver, pageRequest));
};
return(HibernateTemplate.Execute(finder));
}
/// <summary>
/// The filtering.
/// </summary>
/// <param name="query">
/// The query.
/// </param>
/// <param name="filter">
/// The filter.
/// </param>
/// <returns>
/// The <see cref="IQueryOver"/>.
/// </returns>
public override IQueryOver <Acatalog, Acatalog> Filtering(IQueryOver <Acatalog, Acatalog> query, CatalogFilter filter)
{
query.FindByRn(filter.Rn);
if (!string.IsNullOrEmpty(filter.Name))
{
return(query.WhereRestrictionOn(x => x.Name).IsLike(filter.Name));
}
return(query);
}
private IQueryOver <Category, Category> FilterByName(IQueryOver <Category, Category> queryOver)
{
IQueryOver <Category, Category> query = queryOver.WhereRestrictionOn(x => x.Name).IsLike(Name, MatchMode.Anywhere);
if (ParentId.HasValue)
{
query = FilterByParent(query);
}
return(query);
}
/// <summary>
/// The filtering.
/// </summary>
/// <param name="query">
/// The query.
/// </param>
/// <param name="filter">
/// The filter.
/// </param>
/// <returns>
/// The <see cref="IQueryOver"/>.
/// </returns>
public override IQueryOver <ActInputControl, ActInputControl> Filtering(
IQueryOver <ActInputControl, ActInputControl> query, ActInputControlFilter filter)
{
query.FindByRn(filter.Rn);
if (filter.State != null)
{
query.WhereRestrictionOn(x => x.State).IsInG(filter.State);
}
if (!string.IsNullOrWhiteSpace(filter.Note))
{
query.WhereRestrictionOn(x => x.Note).IsLike(filter.Note, MatchMode.Start);
}
if (filter.Numb != null)
{
query.Where(x => x.Numb == filter.Numb);
}
if (!string.IsNullOrWhiteSpace(filter.ViewTareStamp))
{
query.WhereRestrictionOn(x => x.ViewTareStamp)
.IsLike(filter.ViewTareStamp.ReplaceStar());
}
query.IsBetween(x => x.OpenningTareDate, filter.OpenningTareDate);
if ((filter.TheMoveActs != null) && (filter.TheMoveActs.Any()))
{
TheMoveAct theMoveActAlias = null;
StaffingDivision staffingDivisionDepartmentReciverAlias = null;
StaffingDivision staffingDivisionDepartmentCreatorAlias = null;
query.JoinAlias(x => x.TheMoveActs, () => theMoveActAlias);
query.JoinAlias(() => theMoveActAlias.DepartmentCreator, () => staffingDivisionDepartmentCreatorAlias);
query.JoinAlias(() => theMoveActAlias.DepartmentReciver, () => staffingDivisionDepartmentReciverAlias);
query.WhereRestrictionOn(x => staffingDivisionDepartmentCreatorAlias.Code)
.IsIn(
filter.TheMoveActs.Where(x => x.DepartmentCreator != null)
.Select(x => x.DepartmentCreator.Code)
.ToArray());
query.WhereRestrictionOn(x => staffingDivisionDepartmentReciverAlias.Code)
.IsIn(
filter.TheMoveActs.Where(x => x.DepartmentReciver != null)
.Select(x => x.DepartmentReciver.Code)
.ToArray());
query.WhereRestrictionOn(() => theMoveActAlias.UserCreator.Rn)
.IsIn(filter.TheMoveActs.Select(x => x.UserCreator.Rn).ToArray());
}
if ((filter.QualityStateControlOfTheMakes != null) && (filter.QualityStateControlOfTheMakes.Any()))
{
QualityStateControlOfTheMake qualityStateControlOfTheMakeAlias = null;
query.JoinAlias(x => x.QualityStateControlOfTheMakes, () => qualityStateControlOfTheMakeAlias);
}
return(query);
}
public static IQueryOver <TEntity, TEntity> IsLike <TEntity>(
this IQueryOver <TEntity, TEntity> query,
Expression <Func <TEntity, object> > property,
string value,
MatchMode matchMode)
{
if (string.IsNullOrWhiteSpace(value))
{
return(query);
}
return(query.WhereRestrictionOn(property).IsLike(value, matchMode));
}
public void AllowSingleCallSyntax()
{
ICriteria expected = CreateTestCriteria(typeof(Person));
expected.Add(Restrictions.IsNotEmpty("Children"));
expected.AddOrder(Order.Asc("Name"));
expected.SetFetchMode("PersonList", FetchMode.Eager);
expected.SetLockMode(LockMode.UpgradeNoWait);
IQueryOver <Person, Person> actual = CreateTestQueryOver <Person>();
actual.WhereRestrictionOn(p => p.Children).IsNotEmpty();
actual.OrderBy(p => p.Name).Asc();
actual.Fetch(p => p.PersonList).Eager();
actual.Lock().UpgradeNoWait();
AssertCriteriaAreEqual(expected, actual);
}
public IList <TariffGenericReponse> GetDrugTariff(int Id, string phrase)
{
//var groups = _session.QueryOver<TariffCategory>().Where(x => x.TariffId == Id && x.IsDeleted == false).List<TariffCategory>();
IList <int> idlist = _session.QueryOver <TariffCategory>().Where(x => x.TariffId == Id && x.IsDeleted == false && x.Type == 0).SelectList(list => list
.Select(p => p.Id)
).List <int>();
List <int> output = new List <int>();
//added ?? new List<int>()
foreach (int item in idlist ?? new List <int>())
{
output.Add(item);
}
IQueryOver <DrugTariff, DrugTariff> query = _session.QueryOver <DrugTariff>().WhereRestrictionOn(bp => bp.GroupId)
.IsIn(output);
if (!string.IsNullOrEmpty(phrase))
{
phrase = phrase + "%";
query.WhereRestrictionOn(x => x.Name).IsInsensitiveLike(phrase);
}
else
{
return(null);
}
List <TariffGenericReponse> outputlistobj = new List <TariffGenericReponse>();
foreach (DrugTariff item in query.List <DrugTariff>())
{
TariffGenericReponse itemo = new TariffGenericReponse()
{
Id = item.Id.ToString(),
Name = item.Name,
Price = item.Price.ToString("N")
};
outputlistobj.Add(itemo);
}
return(outputlistobj);
}
public static IQueryOver <TEntity, TEntity> IsBetween <TEntity, U>(
this IQueryOver <TEntity, TEntity> query,
Expression <Func <TEntity, object> > property,
Between <U> between)
{
if (between == null)
{
return(query);
}
if (object.Equals(between.From, null) || object.Equals(between.To, null))
{
return(query);
}
return(query.WhereRestrictionOn(property)
.IsBetween(between.From)
.And(between.To));
}
/// <summary>
/// 自定义条件检索对象信息
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="property">实体属性列名</param>
/// <param name="whereList">多个值</param>
/// <param name="where">Lamdba表达式</param>
/// <returns></returns>
public IList <T> GetListCustom <T>(string property, int[] whereList,
Expression <Func <T, bool> > where = null) where T : class
{
IQueryOver <T, T> iQueryOver = Session.QueryOver <T>();
T model = null;
Expression <Func <object> > whereProperty = () => model;
var propertyExpression = System.Linq.Expressions.Expression
.Property(System.Linq.Expressions.Expression.Parameter(typeof(T)), property);
var body = System.Linq.Expressions.Expression.Convert(propertyExpression, typeof(object));
if (where != null)
{
iQueryOver = iQueryOver.Where(where);
}
iQueryOver = iQueryOver.WhereRestrictionOn(System.Linq.Expressions.Expression.Lambda <Func <object> >(body))
.IsIn(whereList);
//if (where != null)
//{
// return iQueryOver.Where(where).List<T>();
//}
return(iQueryOver.List <T>());
}
public override IQueryOver <Menu, Menu> CreateQueryForRetrieval(IQueryOver <Menu, Menu> query, string filter, IDictionary <string, object> additionalParameters)
{
var menuId = InputProcessingMethods.GetValue <string>(additionalParameters, "MenuId");
if (!String.IsNullOrWhiteSpace(menuId))
{
query = query.Where(m => m.ParentMenu.Id == menuId);
}
else
{
query = query.Where(m => m.ParentMenu == null);
}
if (!String.IsNullOrWhiteSpace(filter))
{
query = query.WhereRestrictionOn(x => x.Name).IsLike(filter, MatchMode.Anywhere);
}
query = query.OrderBy(m => m.Position).Asc();
return(query);
}
public IList <Provider> GetallProviderByService(int serviceType)
{
IQueryOver <Provider, Provider> query = _session.QueryOver <Provider>().Where(x => x.IsDeleted == false && x.AuthorizationStatus == 2 && x.isDelisted == false);
string serviceid = "%" + Convert.ToString(serviceType) + "%";
IQueryOver <Provider, Provider> query2 = _session.QueryOver <Provider>().Where(x => x.IsDeleted == false && x.AuthorizationStatus == 2);
query2.AndRestrictionOn(x => x.Providerservices).IsInsensitiveLike(serviceid);
List <int> output = new List <int>();
int count = query2.RowCount();
for (int i = 0; i < count; i++)
{
Provider item = query2.Skip(i).Take(1).SingleOrDefault();
string[] allplans = item.Providerservices.Split(',');
output.AddRange(from plan in allplans where plan.Trim() == Convert.ToString(serviceType) select item.Id);
}
query.WhereRestrictionOn(bp => bp.Id)
.IsIn(output);
return(query.List <Provider>());
}
public IQueryOver <TEntity, TEntity> FilterBySubdivisionsAccess <TEntity>(IQueryOver <TEntity, TEntity> baseQuery)
where TEntity : class, IDomainObject, ISubdivisionEntity
{
return(baseQuery.WhereRestrictionOn(x => x.RelatedToSubdivision.Id).IsIn(Subdivisions.Select(x => x.Id).ToArray()));
}
public IList <Enrollee> QueryAllenrollee(out int totalRecord, out int totalcountinresult, string search, int start, int lenght, string sortColumn, string sortOrder, string srcPolicynumber, string scrOthername, string scrLastname, string scrMobilenumber, string scrProvider, string scrCompany, string scrCompanySubsidiary, bool useDate, DateTime scrFromDate, DateTime scrToDate, int showExpunge, string scrUsers, int enrolleetype, int otherFilters, int zones, int state, int plantype, int planmode)
{
IQueryOver <Enrollee, Enrollee> query = _session.QueryOver <Enrollee>().Where(x => x.IsDeleted == false);
if (!string.IsNullOrEmpty(srcPolicynumber))
{
//search policy number
srcPolicynumber = "%" + srcPolicynumber + "%";
query = query.Where(Restrictions.On <Enrollee>(x => x.Policynumber).IsInsensitiveLike(srcPolicynumber) || Restrictions.On <Enrollee>(x => x.RefPolicynumber).IsInsensitiveLike(srcPolicynumber));
//.JoinAlias(p => p.Primaryprovider, () => providerAlias)
//.JoinAlias(p => p.Stateid, () => stateAlias)
//.JoinAlias(p=>p.Staffprofileid,()=> staffAlias)
//.JoinAlias(()=>staffAlias.CompanySubsidiary,()=> subsidiaryAlias)
//.Where(()=>companyAlias.Name =="")
//Restrictions.On<Enrollee>(x => x.Policynumber).IsInsensitiveLike(search) ||
// Restrictions.On<Enrollee>(x => x.Surname).IsInsensitiveLike(search) ||
// Restrictions.On<Enrollee>(x => x.Othernames).IsInsensitiveLike(search) ||
// Restrictions.On<Enrollee>(x => x.Occupation).IsInsensitiveLike(search) ||
// Restrictions.On<Enrollee>(x => x.Mobilenumber).IsInsensitiveLike(search) ||
// Restrictions.On<Enrollee>(x => x.Residentialaddress).IsInsensitiveLike(search));
;
}
if (!string.IsNullOrEmpty(scrOthername))
{
string content = Regex.Replace(scrOthername.Trim(), @"\s+", ":");
string[] namelist = content.Split(':');
foreach (string namesearch in namelist)
{
string seartxt = "%" + namesearch + "%";
query.Where(Restrictions.InsensitiveLike(
Projections.SqlFunction("concat",
NHibernateUtil.String,
Projections.Property("Othernames"),
Projections.Constant(" "),
Projections.Property("Surname")),
seartxt,
MatchMode.Anywhere));
}
}
if (!string.IsNullOrEmpty(scrMobilenumber))
{
scrMobilenumber = "%" + scrMobilenumber + "%";
query = query.Where(Restrictions.On <Enrollee>(x => x.Mobilenumber).IsInsensitiveLike(scrMobilenumber));
}
if (!string.IsNullOrEmpty(scrProvider) && Convert.ToInt32(scrProvider) > -1)
{
int provider = Convert.ToInt32(scrProvider);
query = query.Where(x => x.Primaryprovider == provider);
}
if (!string.IsNullOrEmpty(scrCompany) && Convert.ToInt32(scrCompany) > -1)
{
int company = Convert.ToInt32(scrCompany);
query = query.Where(x => x.Companyid == company);
}
if (!string.IsNullOrEmpty(scrCompanySubsidiary) && Convert.ToInt32(scrCompanySubsidiary) > -1)
{
int companysub = Convert.ToInt32(scrCompanySubsidiary);
List <int> stafflist = (List <int>)_session.QueryOver <Staff>().Where(x => x.IsDeleted == false && x.CompanySubsidiary == companysub).SelectList(a => a.Select(p => p.Id)).List <int>();
query = query.WhereRestrictionOn(w => w.Staffprofileid).IsIn(stafflist);
}
if (plantype > -1 && !string.IsNullOrEmpty(scrCompany) && Convert.ToInt32(scrCompany) > 0)
{
List <int> companyplan = (List <int>)_session.QueryOver <CompanyPlan>().Where(x => x.Planid == plantype && x.Companyid == Convert.ToInt32(scrCompany)).Select(a => a.Id).List <int>();
if (planmode == 0)
{
//single
companyplan = (List <int>)_session.QueryOver <CompanyPlan>().Where(x => x.Planid == plantype && x.AllowChildEnrollee == false && x.Companyid == Convert.ToInt32(scrCompany)).Select(a => a.Id).List <int>();
}
if (planmode == 1)
{
//family
companyplan = (List <int>)_session.QueryOver <CompanyPlan>().Where(x => x.Planid == plantype && x.AllowChildEnrollee == true && x.Companyid == Convert.ToInt32(scrCompany)).Select(a => a.Id).List <int>();
}
List <int> stafflist = (List <int>)_session.QueryOver <Staff>().Where(x => x.IsDeleted == false).WhereRestrictionOn(w => w.StaffPlanid).IsIn(companyplan).SelectList(a => a.Select(p => p.Id)).List <int>();
query = query.WhereRestrictionOn(w => w.Staffprofileid).IsIn(stafflist);
}
if (useDate)
{
DateTime datete = Convert.ToDateTime(scrToDate);
int dd = datete.Day;
int month = datete.Month;
int year = datete.Year;
string time = "23:59";
DateTime enddate = Convert.ToDateTime(string.Format("{0}/{1}/{2} {3}", month, dd, year, time));
query.Where(Restrictions.On <Enrollee>(a => a.CreatedOn).IsBetween(scrFromDate).And(enddate));
}
if (!string.IsNullOrEmpty(scrUsers))
{
int toint = Convert.ToInt32(scrUsers);
if (toint > 0)
{
query.Where(x => x.Createdby == toint);
}
}
if (showExpunge > -1)
{
switch (showExpunge)
{
case 0:
//all
break;
case 1:
//not expunged
query = query.Where(x => x.Isexpundged == false);
break;
case 2:
query = query.Where(x => x.Isexpundged == true);
break;
}
}
if (enrolleetype > -1)
{
switch (enrolleetype)
{
case 0:
//all
break;
case 1:
//not expunged
query = query.Where(x => x.Parentid <= 0);
break;
case 2:
query = query.Where(x => x.Parentid > 0);
break;
}
}
if (zones > -1)
{
List <int> states = (List <int>)_session.QueryOver <State>().Where(x => x.Zone == zones).SelectList(a => a.Select(p => p.Id)).List <int>();
//var ids = new List<int> { 1, 2, 5, 7 };
//var query2 = _session.QueryOver<Provider>().Where(x => x.IsDeleted == false && x.AuthorizationStatus == 2 );
query
.WhereRestrictionOn(w => w.Stateid).IsIn(states);
}
if (state > -1)
{
query = query.Where(x => x.Stateid == state);
}
DateTime today = CurrentRequestData.Now;
DateTime childagelimit = today.Date.AddYears(-21);
DateTime parentagelimit = today.Date.AddYears(-70);
if (otherFilters > -1)
{
switch (otherFilters)
{
case 0:
//all
break;
case 1:
//female only
query = query.Where(x => x.Sex == (int)Sex.Female);
break;
case 2:
//Male only
query = query.Where(x => x.Sex == (int)Sex.Male);
break;
case 3:
//With Mobile Numbers only
query = query.Where(Restrictions.Gt(
Projections.SqlFunction("length", NHibernateUtil.String,
Projections.Property <Enrollee>(b => b.Mobilenumber)),
9
));
break;
case 4:
//Without Mobile Numbers only
query.Where(Restrictions.Lt(
Projections.SqlFunction("length", NHibernateUtil.String,
Projections.Property <Enrollee>(b => b.Mobilenumber)),
9
));
break;
case 5:
//With email only
query.Where(Restrictions.Gt(
Projections.SqlFunction("length", NHibernateUtil.String,
Projections.Property <Enrollee>(b => b.Emailaddress)),
5
));
break;
case 6:
//Without email only
query.Where(Restrictions.Lt(
Projections.SqlFunction("length", NHibernateUtil.String,
Projections.Property <Enrollee>(b => b.Emailaddress)),
5
));
break;
case 7:
//with age
query = query.Where(x => x.Dob <= childagelimit && x.Parentid > 0 && x.Parentrelationship == (int)Relationship.Child || x.Dob <= parentagelimit && x.Parentid == 0);
break;
case 8:
//with birthdays for the day
string etoday = today.ToString("MM-dd");
query = query.Where(x => x.Dob.Month == today.Month && x.Dob.Day == today.Day);
break;
case 9:
//idcard not printed
query = query.Where(x => x.IdCardPrinted == false);
break;
case 10:
query = query.Where(x => x.IdCardPrinted == true);
break;
}
}
//sort order
//return normal list.
totalRecord = query.RowCount();
totalcountinresult = totalRecord;
return(query.Skip(start).Take(lenght).List());
}
public IList <IncomingClaims> QueryAllIncomingClaims(out int totalRecord, out int totalcountinresult, string search, int start, int lenght, string sortColumn, string sortOrder, int scrProvider, int addedBy, string scrDeliveredBy, int month, int year, int transferedTo, bool useDate, DateTime scrFromDate, DateTime scrToDate, int otherFilters, int trackingid)
{
IQueryOver <IncomingClaims, IncomingClaims> query = _session.QueryOver <IncomingClaims>().Where(x => x.IsDeleted == false);
//if(addedBy > -1)
//{
// query.Where(x => x.receivedBy == addedBy);
//}
if (scrProvider > -1)
{
query.Where(x => x.providerid == scrProvider);
}
//added else
else
{
}
if (!string.IsNullOrEmpty(scrDeliveredBy))
{
query.Where(x => x.deliveredby == scrDeliveredBy.ToUpper() && x.deliveredby == scrDeliveredBy.ToLower());
}
else
{
}
if (useDate)
{
DateTime datete = Convert.ToDateTime(scrToDate);
int dd = datete.Day;
int dmonth = datete.Month;
int dyear = datete.Year;
string time = "23:59";
DateTime enddate = Convert.ToDateTime(string.Format("{0}/{1}/{2} {3}", dmonth, dd, dyear, time));
query.Where(Restrictions.On <Enrollee>(a => a.CreatedOn).IsBetween(scrFromDate).And(enddate));
}
else
{
}
if (year > -1 && trackingid < 0)
{
query.Where(x => x.year == year);
}
else
{
}
if (month > -1)
{
//query.Where(x => x.month == month);
string monthstr = month.ToString();
query.WhereRestrictionOn(x => x.month_string).IsInsensitiveLike(monthstr, MatchMode.Anywhere);
//if(query.WhereRestrictionOn(x => x.month_string).IsInsensitiveLike(",", MatchMode.Anywhere).RowCount() > 0)
//{
// //contains the coma guys
// foreach(var item in )
// var states = (List<int>)query.WhereRestrictionOn(x => x.month_string).IsInsensitiveLike(",", MatchMode.Anywhere).SelectList(a => a.Select(p => p.Id)).List<int>();
//}
}
//added else
else
{
}
if (trackingid > -1)
{
IList <Entities.ClaimBatch> claimlist = _session.QueryOver <Entities.ClaimBatch>().Where(v => v.Id == trackingid).List <Entities.ClaimBatch>();
List <int> intlist = new List <int>();
if (claimlist.Any())
{
IncomingClaims first = claimlist.FirstOrDefault().IncomingClaims.FirstOrDefault();
if (first != null)
{
intlist.Add(first.Id);
}
//added else
else
{
}
}
query.WhereRestrictionOn(x => x.Id).IsIn(intlist);
}
totalRecord = query.RowCount();
totalcountinresult = totalRecord;
IList <IncomingClaims> list = query.OrderBy(x => x.CreatedOn).Desc.Skip(start).Take(lenght).List();
return(list);
}