public IList <AuthorizationRequest> QueryallAuthRequest(out int totalRecord, out int totalcountinresult, string search, int start, int lenght, string sortColumn, string sortOrder, string Search, bool useDate, DateTime scrFromDate, DateTime scrToDate) { IQueryOver <AuthorizationRequest, AuthorizationRequest> query = _session.QueryOver <AuthorizationRequest>().Where(x => x.IsDeleted == false); if (!string.IsNullOrEmpty(Search)) { Search = "%" + Search + "%"; query.AndRestrictionOn(x => x.fullname).IsInsensitiveLike(search, MatchMode.Anywhere); } 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 <AuthorizationRequest>(a => a.CreatedOn).IsBetween(scrFromDate).And(enddate)); } totalRecord = query.RowCount(); totalcountinresult = totalRecord; IList <AuthorizationRequest> list = query.OrderBy(x => x.CreatedOn).Desc.Skip(start).Take(lenght).List(); return(list); }
void UpdateCountEntries(WordType wordType = null) { using (ISession session = DataHelper.OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { Word w = null; Lesson l = null; IQueryOver <Word, WordType> words = session.QueryOver <Word>(() => w) .JoinQueryOver(pr => pr.Entry) .JoinQueryOver(pr => pr.Lesson, () => l) .Where(() => l.Theme == host.CurrentTheme) .JoinQueryOver(pr => w.WordType); if (wordType != null) { words.And(() => w.WordType == wordType); } int count = words.RowCount(); TreeNodeColumn node = wordType == null ? (TreeNodeColumn)treeWordTypes.Nodes[0] : GetNode(treeWordTypes.Nodes[0].Nodes, wordType); node.FarText = count.ToString(); } } }
public IList <Enrollee> GetallenrolleeRange(out int totalRecord, int start, int lenght) { IQueryOver <Enrollee, Enrollee> query = _session.QueryOver <Enrollee>().Where(x => x.IsDeleted == false && x.Isexpundged == false); totalRecord = query.RowCount(); return(query.Skip(start).Take(lenght).List()); }
public IList <ConnectCareSponsor> QueryConnectCareSponsor(out int totalRecord, out int totalcountinresult, string search, int start, int lenght, string fullname, string Policynumber, int senttomatontine, bool useDate, DateTime scrFromDate, DateTime scrToDate) { IQueryOver <ConnectCareSponsor, ConnectCareSponsor> query = _session.QueryOver <ConnectCareSponsor>().Where(x => x.IsDeleted == false); if (senttomatontine > -1) { bool sent = Convert.ToBoolean(senttomatontine); query.Where(x => x.pushedtoMatontine == sent); } if (!string.IsNullOrEmpty(fullname)) { fullname = "%" + fullname + "%"; query.Where(Restrictions.On <ConnectCareSponsor>(x => x.fullname).IsInsensitiveLike(fullname)); } if (!string.IsNullOrEmpty(Policynumber)) { query.Where(x => x.policynumber == Policynumber || x.policynumber == Policynumber.ToLower()); } if (useDate) { DateTime newenddate = Convert.ToDateTime(string.Format("{0}/{1}/{2} {3}", scrToDate.Month, scrToDate.Day, scrToDate.Year, "23:59")); query.Where(Restrictions.On <ConnectCareSponsor>(a => a.CreatedOn).IsBetween(scrFromDate).And(newenddate)); } //return normal list. totalRecord = query.RowCount(); totalcountinresult = totalRecord; return(query.OrderBy(x => x.CreatedOn).Desc.Skip(start).Take(lenght).List()); }
public virtual (IEnumerable <TEntity>, int) QueryByPage(IQueryOver <TEntity, TEntity> queryOver, Expression <Func <TEntity, object> > orderby, bool isDesc = false, int pageIndex = 1, int pageSize = 0) { var totalCount = queryOver.RowCount(); var orderQuery = queryOver.OrderBy(orderby); var orderByQuery = isDesc ? orderQuery.Desc : orderQuery.Asc; return(orderByQuery.Skip((pageIndex > 0 ? (pageIndex - 1) : 0) * (pageSize <= 0 ? totalCount : pageSize)).Take((pageSize <= 0 ? totalCount : pageSize)).List(), totalCount); }
public PagedObject <T> Paginate(IQueryOver <T> query, int pageSize, int pageNumber) { this.rowCount = query.RowCount(); this.pageCount = Math.Ceiling(this.rowCount / pageSize); this.pageSize = pageSize; this.ResultQuery = query.Skip((pageNumber - 1) * pageSize).Take(pageSize); return(this); }
public int Count <T>(params Expression <Func <T, bool> >[] where) where T : class { IQueryOver <T, T> query = session.QueryOver <T>(); foreach (var w in where) { query = query.And(w); } return(query.RowCount()); }
public IList <Supervise> QuerySupervise(Paging info, out int total) { using ISession session = DbFactory.OpenSession(); IQueryOver <Supervise> queries = session.QueryOver <Supervise>(); SetQueryArg(queries.RootCriteria, info.Get()); total = queries.RowCount(); return(queries .Skip((info.Page - 1) * info.Limit) .Take(info.Limit) .List()); }
public int GetNotificationCount() { Entities.People.User user = CurrentRequestData.CurrentUser; IQueryOver <Notification, Notification> queryOver = _session.QueryOver <Notification>(); if (user.LastNotificationReadDate.HasValue) { queryOver = queryOver.Where(notification => notification.CreatedOn >= user.LastNotificationReadDate); } return(queryOver.RowCount()); }
public IList <WorkflowStructure> Query(int pageIndex, int pageSize, out int total, Dictionary <string, string> queryArg) { using ISession session = DbFactory.OpenSession(); IQueryOver <WorkflowStructure> queries = session.QueryOver <WorkflowStructure>(); SetQueryArg(queries.RootCriteria, queryArg); total = queries.RowCount(); return(queries .Skip((pageIndex - 1) * pageSize) .Take(pageSize) .List()); }
public IList <EnrolleeVerificationCode> QueryVerificationCode(out int totalRecord, out int totalcountinresult, string search, int start, int lenght, string sortColumn, string sortOrder, string srcPolicynumber, int providerid, string verificationcode, string scrMobilenumber, bool useDate, DateTime scrFromDate, DateTime scrToDate, evsvisittype visittype) { IQueryOver <EnrolleeVerificationCode, EnrolleeVerificationCode> query = _session.QueryOver <EnrolleeVerificationCode>().Where(x => x.IsDeleted == false); if (!string.IsNullOrEmpty(srcPolicynumber)) { Enrollee enrolll = GetEnrolleeByPolicyNumber(srcPolicynumber); if (enrolll != null) { query.Where(x => x.EnrolleeId == enrolll.Id); } } if (visittype != evsvisittype.All) { query.Where(x => x.visittype == visittype); } if (providerid > -1) { query.Where(x => x.ProviderId == providerid); } if (!string.IsNullOrEmpty(verificationcode)) { query.Where(x => x.VerificationCode == verificationcode); } if (!string.IsNullOrEmpty(scrMobilenumber)) { query.Where(x => x.RequestPhoneNumber == scrMobilenumber); } 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 <EnrolleeVerificationCode>(a => a.CreatedOn).IsBetween(scrFromDate).And(enddate)); } totalRecord = query.RowCount(); totalcountinresult = totalRecord; return(query.OrderBy(x => x.CreatedOn).Desc().Skip(start).Take(lenght).List <EnrolleeVerificationCode>()); }
public IList <DownloadFile> QueryDownloadFiles(out int totalRecord, out int totalcountinresult, string search, int start, int lenght, string searchText) { IQueryOver <DownloadFile, DownloadFile> query = _session.QueryOver <DownloadFile>().Where(x => x.IsDeleted == false); if (!string.IsNullOrEmpty(searchText)) { searchText = "%" + searchText + "%"; query = query.Where(Restrictions.On <DownloadFile>(x => x.fileName).IsInsensitiveLike(searchText)); } totalRecord = query.RowCount(); totalcountinresult = totalRecord; return(query.OrderBy(x => x.CreatedOn).Desc.Skip(start).Take(lenght).List()); }
public int CountByFilter(string type, bool?handled) { ISession session = DatabaseSessionFactory.OpenIndependentSession(); try { IQueryOver <Error, Error> criteria = GetCriteria(session, type, handled); return(criteria.RowCount()); } finally { session.Close(); } }
public IList <ServiceTariff> QueryAllServiceTariff(out int totalRecord, out int totalcountinresult, string search, int start, int lenght, string sortColumn, int groupid, string sortOrder, string scrProvider, string scrUsers, int otherFilters) { IQueryOver <ServiceTariff, ServiceTariff> query = _session.QueryOver <ServiceTariff>().Where(x => x.IsDeleted == false && x.GroupId == groupid); if (!string.IsNullOrEmpty(search)) { search = "%" + search + "%"; query.Where(Restrictions.On <ServiceTariff>(x => x.Name).IsInsensitiveLike(search)); } //return normal list. totalRecord = query.RowCount(); totalcountinresult = totalRecord; return(query.Skip(start).Take(lenght).List()); }
/// <summary> /// Metodo che consente di contare i workflow assegnati all'utente. /// Questo metodo viene utilizzato nel frameset per riportare il numero dei workflow assegnati all'utente corrente. /// </summary> /// <returns></returns> public override int Count() { int counter = default(int); IQueryOver <WorkflowActivity, WorkflowActivity> queryOver = CreateQueryOver(); queryOver = DecorateCriteria(queryOver); using (ITransaction transaction = NHibernateSession.BeginTransaction()) { try { counter = queryOver.RowCount(); transaction.Commit(); } catch (Exception) { transaction.Rollback(); throw; } return(counter); } }
public int GetNotificationCount() { User user = CurrentRequestData.CurrentUser; if (user != null) { IQueryOver <UserNotification, UserNotification> queryOver = _session.QueryOver <UserNotification>().Where(notification => notification.UserId == user.Guid.ToString()); if (user.LastNotificationReadDate.HasValue) { queryOver = queryOver.Where(notification => notification.CreatedOn >= user.LastNotificationReadDate && notification.UserId == user.Guid.ToString()); } int count = queryOver.RowCount(); return(count); } else { return(0); } }
public IList <Enrollee> GetFamilyTreeByPolicyNumber(string policyNumber) { List <Enrollee> response = new List <Enrollee>(); if (!string.IsNullOrEmpty(policyNumber)) { policyNumber = policyNumber.ToLower(); IQueryOver <Enrollee, Enrollee> enrollee = _session.QueryOver <Enrollee>().Where(x => x.Policynumber == policyNumber || x.Policynumber == policyNumber.ToUpper()); if (enrollee.RowCount() > 0) { Enrollee enrolleeObj = enrollee.SingleOrDefault(); //check if principal if (enrolleeObj.Parentid > 0) { //child //get the parent Enrollee parent = GetEnrollee(enrolleeObj.Parentid); IList <Enrollee> dependants = GetDependentsEnrollee(parent.Id); response.Add(parent); response.AddRange(dependants); } else { //parent IList <Enrollee> dependants = GetDependentsEnrollee(enrolleeObj.Id); response.Add(enrolleeObj); response.AddRange(dependants); } } } return(response); }
public IncomingClaims getincomingClaimByMonthandYear(int providerid, int month, int year) { string monthstring = month.ToString(); IQueryOver <IncomingClaims, IncomingClaims> query = _session.QueryOver <IncomingClaims>().Where(x => x.IsDeleted == false && x.month_string == monthstring && x.year == year && x.providerid == providerid && x.IsRemoteSubmission == true); IncomingClaims response = new IncomingClaims(); if (query.RowCount() > 0) { foreach (IncomingClaims item in query.List().OrderByDescending(x => x.CreatedOn)) { if (item.ClaimBatch.status == ClaimBatchStatus.Capturing) { //found our guy response = item; break; } } } return(response); }
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 ActionResult Archive(int year, int month, int?page = 1) { DateTime startDate = new DateTime(year, month, 1); DateTime endDate = startDate.AddMonths(1); IQueryOver <PostExcerpt, PostExcerpt> query = DbSession.QueryOver <PostExcerpt>() .Where(p => p.PostDate >= startDate) .And(p => p.PostDate < endDate); int start = (page.Value - 1) * PageSize; int count = query.RowCount(); IList <PostExcerpt> excerpts = query .OrderBy(p => p.PostDate).Desc .Skip(start) .Take(PageSize) .List(); ViewBag.Title = String.Format("{0}年{1}月归档 - 宅居 - 宅并技术着", year, month); ViewBag.PageCount = (int)Math.Ceiling((double)count / (double)PageSize); ViewBag.PageIndex = page; ViewBag.BaseUrl = Url.Content(String.Format("~/archive/{0}/{1}/", year, month)); return(View("List", excerpts.Select(RenderExcerpt))); }
public void RowCount() { SetupPagingData(); using (ISession s = OpenSession()) { IQueryOver <Person> query = s.QueryOver <Person>() .JoinQueryOver(p => p.Children) .OrderBy(c => c.Age).Desc .Skip(2) .Take(1); IList <Person> results = query.List(); int rowCount = query.RowCount(); object bigRowCount = query.RowCountInt64(); Assert.That(results.Count, Is.EqualTo(1)); Assert.That(results[0].Name, Is.EqualTo("Name 3")); Assert.That(rowCount, Is.EqualTo(4)); Assert.That(bigRowCount, Is.TypeOf <long>()); Assert.That(bigRowCount, Is.EqualTo(4)); } }
public IList <ShortCodeMsg> QueryShortCodeMsgs(out int totalRecord, out int totalcountinresult, string search, int start, int lenght, string scrMobilenumber, string scrMessage, bool useDate, DateTime scrFromDate, DateTime scrToDate) { IQueryOver <ShortCodeMsg, ShortCodeMsg> query = _session.QueryOver <ShortCodeMsg>().Where(x => x.IsDeleted == false); if (!string.IsNullOrEmpty(scrMobilenumber)) { //search policy number scrMobilenumber = "%" + scrMobilenumber + "%"; query = query.Where(Restrictions.On <ShortCodeMsg>(x => x.Mobile).IsInsensitiveLike(scrMobilenumber)); } if (!string.IsNullOrEmpty(scrMessage)) { scrMessage = "%" + scrMessage + "%"; query = query.Where(Restrictions.On <ShortCodeMsg>(x => x.Msg).IsInsensitiveLike(scrMessage)); } if (useDate) { DateTime newenddate = Convert.ToDateTime(string.Format("{0}/{1}/{2} {3}", scrToDate.Month, scrToDate.Day, scrToDate.Year, "23:59")); query.Where(Restrictions.On <ShortCodeMsg>(a => a.CreatedOn).IsBetween(scrFromDate).And(newenddate)); } //return normal list. totalRecord = query.RowCount(); totalcountinresult = totalRecord; return(query.OrderBy(x => x.CreatedOn).Desc.Skip(start).Take(lenght).List()); }
public IList <PaymentBatch> Queryallpaymentbatch(out int totalRecord, out int totalcountinresult, string search, int start, int lenght, string sortColumn, string sortOrder, string Title, bool usestatus, PaymentStatus paymentstatus, bool useDate, DateTime scrFromDate, DateTime scrToDate) { IQueryOver <PaymentBatch, PaymentBatch> query = _session.QueryOver <PaymentBatch>().Where(x => x.IsDeleted == false); if (!string.IsNullOrEmpty(Title)) { Title = "%" + Title + "%"; query.AndRestrictionOn(x => x.Title).IsInsensitiveLike(Title, MatchMode.Anywhere); } if (usestatus) { query.Where(x => x.status == paymentstatus); } 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)); } totalRecord = query.RowCount(); totalcountinresult = totalRecord; IList <PaymentBatch> list = query.OrderBy(x => x.CreatedOn).Desc.Skip(start).Take(lenght).List(); return(list); }
/// <summary> /// The export by query. /// </summary> /// <param name="query"> /// The query. /// </param> /// <param name="sessionAtl"> /// The session atl. /// </param> /// <param name="log"> /// The log. /// </param> /// <param name="context"> /// The context. /// </param> private void ExportByQuery( IQueryOver <StatementMaximum, StatementMaximum> query, ISession sessionAtl, Przlog log, IJobExecutionContext context) { var session = sessionFactoryPvp.GetCurrentSession(); var count = query.RowCount(); var step = (count / 5000) + 1; for (var i = 0; i < step; i++) { var list = query.OrderBy(x => x.DateFiling) .Asc.ThenBy(x => x.Id) .Asc.Skip(i * 5000) .Take(5000) .RootCriteria.SetTimeout(int.MaxValue) .List <StatementMaximum>(); ExportByList(session, sessionAtl, log, list, context); sessionAtl.Flush(); } }
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); }
public long GetNumberofAdmission(DateTime start, DateTime End, ref Dictionary <int, int> theplan) { IQueryOver <AuthorizationCode, AuthorizationCode> query = _session.QueryOver <AuthorizationCode>().Where(x => x.IsDeleted == false && x.Isadmission == true); IList <Plan> genericlist = _session.QueryOver <Plan>().Where(x => x.IsDeleted == false).List(); //var placount = new Dictionary<int, int>(); foreach (Plan item in genericlist) { theplan.Add(item.Id, 0); } if (true) { DateTime datete = Convert.ToDateTime(End); 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 <AuthorizationCode>(a => a.AdmissionDate).IsBetween(start).And(enddate)); } //added else else { } IList <AuthorizationCode> totaladd = query.List(); foreach (AuthorizationCode item in totaladd) { Enrollee enrollee = _session.QueryOver <Enrollee>().Where(x => x.IsDeleted == false && x.Id == item.enrolleeID).SingleOrDefault(); if (enrollee != null) { Staff staff = _session.QueryOver <Staff>().Where(x => x.Id == enrollee.Staffprofileid).SingleOrDefault(); if (staff != null) { CompanyPlan plan = _session.QueryOver <CompanyPlan>().Where(x => x.Id == staff.StaffPlanid).SingleOrDefault(); if (plan != null) { int count = theplan[plan.Planid]; count++; theplan[plan.Planid] = count; } else { } } else { } } //added else else { } } return(query.RowCount()); }
public IList <AuthorizationCode> QueryAuthorization(out int totalRecord, out int totalcountinresult, string search, int start, int lenght, string sortColumn, string sortOrder, int scrProvider, int addedBy, int authorizedby, string Policynumber, int companyid, bool useDate, DateTime scrFromDate, DateTime scrToDate, int otherFilters, int opmode) { IQueryOver <AuthorizationCode, AuthorizationCode> query = _session.QueryOver <AuthorizationCode>().Where(x => x.IsDeleted == false); 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)); if (opmode > 0) { query.Where(Restrictions.On <AuthorizationCode>(a => a.AdmissionDate).IsBetween(scrFromDate).And(enddate)); } else { query.Where(Restrictions.On <AuthorizationCode>(a => a.CreatedOn).IsBetween(scrFromDate).And(enddate)); } } if (scrProvider > 0) { query.Where(x => x.provider == scrProvider); } if (addedBy > 0) { query.Where(x => x.generatedby == addedBy); } if (authorizedby > 0) { query.Where(x => x.Authorizedby == authorizedby); } if (!string.IsNullOrEmpty(Policynumber)) { query.Where(x => x.policyNumber == Policynumber); } if (companyid > 0) { query.Where(x => x.EnrolleeCompany == Convert.ToString(companyid)); } if (!string.IsNullOrEmpty(search)) { search = "%" + search + "%"; query.WhereRestrictionOn(x => x.authorizationCode).IsInsensitiveLike(search); } if (opmode > 0) { query.Where(x => x.Isadmission); } totalRecord = query.RowCount(); totalcountinresult = totalRecord; IList <AuthorizationCode> list = query.OrderBy(x => x.Id).Desc.Skip(start).Take(lenght).List(); return(list); }
public IList <Entities.ClaimBatch> QueryAllClaimBatch(out int totalRecord, out int totalcountinresult, string search, int start, int lenght, string sortColumn, string sortOrder, int scrProvider, int month, int year, Utility.ClaimBatch Batch, string zone, int otherFilters, ClaimBatchStatus status, out int noofprovider, out decimal totalamountcaptured, out decimal totalproccessed, int remoteonly, int batchid) { IQueryOver <Entities.ClaimBatch, Entities.ClaimBatch> query = _session.QueryOver <Entities.ClaimBatch>().Where(x => x.IsDeleted == false); if (batchid > 0) { query.Where(x => x.Id == batchid); } else { if (Batch != Utility.ClaimBatch.All) { query.Where(x => x.Batch == Batch); } if (status == ClaimBatchStatus.Default) { query.Where(x => x.status == status || x.status == ClaimBatchStatus.Capturing); } else { if (status == ClaimBatchStatus.Vetting) { //get the capturing query.Where(x => x.status == status); } else if (status == ClaimBatchStatus.ReviewingANDAwaitingApproval) { query.Where(x => x.status == ClaimBatchStatus.AwaitingApproval || status == ClaimBatchStatus.Reviewing); } else if (status == ClaimBatchStatus.All) { //dont add this shit if its all //do nothing bro. } else { query.Where(x => x.status == status); } } if (month > -1) { query.Where(x => x.month == month); } if (year > -1) { query.Where(x => x.year == year); } if (scrProvider > -1) { query.Where(x => x.ProviderId == scrProvider); } //query.Where(x=>x.) if (remoteonly == 2) { if (status == ClaimBatchStatus.Vetting) { query.Where(x => x.isremote); } } else if (remoteonly == 3) { if (status == ClaimBatchStatus.Vetting) { query.Where(x => x.isremote == false); } } if (!string.IsNullOrEmpty(zone)) { List <int> splitted = new List <int>(); foreach (string item in zone.Split(',')) { int intt = -1; int.TryParse(item, out intt); if (intt > -1) { splitted.Add(intt); } //added else else { } } if (splitted.Any()) { List <int> states = (List <int>)_session.QueryOver <State>().WhereRestrictionOn(x => x.Zone).IsIn(splitted).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 ); List <int> providers = (List <int>)_session.QueryOver <Provider>().WhereRestrictionOn(w => w.State).IsIn(states).SelectList(a => a.Select(p => p.Id)).List <int>(); query .WhereRestrictionOn(w => w.ProviderId).IsIn(providers); } //added else else { } } } totalRecord = query.RowCount(); noofprovider = totalRecord; totalamountcaptured = 0m; totalproccessed = 0m; IList <Entities.ClaimBatch> buffer = query.List(); foreach (Entities.ClaimBatch item in buffer) { foreach (Claim claim in item.Claims) { totalamountcaptured = totalamountcaptured + Convert.ToDecimal(claim.DrugList.Sum(x => x.InitialAmount) + claim.ServiceList.Sum(x => x.InitialAmount)); totalproccessed = totalproccessed + Convert.ToDecimal(claim.DrugList.Sum(x => x.VettedAmount) + claim.ServiceList.Sum(x => x.VettedAmount)); } } totalcountinresult = totalRecord; //var list = new List<Entities.ClaimBatch>(); if (status == ClaimBatchStatus.Vetting) { return(query.OrderBy(x => x.VetDate).Asc.Skip(start).Take(lenght).List()); } else { return(query.OrderBy(x => x.CreatedOn).Desc.Skip(start).Take(lenght).List()); } //return list; }
public void Should_Find_Page_And_Return_ViewModel_Successfully() { // DEBUG START var request = new SearchableGridOptions { SearchQuery = "a", PageNumber = 2, PageSize = 3, Column = "PageUrl", Direction = SortDirection.Descending }; var sessionFactory = this.Container.Resolve <ISessionFactoryProvider>(); // DEBUG END SearchableGridViewModel <SiteSettingRedirectViewModel> model; using (var session = sessionFactory.OpenSession()) { BetterCms.Module.Pages.Models.Redirect alias = null; SiteSettingRedirectViewModel modelAlias = null; IQueryOver <BetterCms.Module.Pages.Models.Redirect, BetterCms.Module.Pages.Models.Redirect> query = session.QueryOver(() => alias); // Filter if (!string.IsNullOrWhiteSpace(request.SearchQuery)) { var searchQuery = string.Format("%{0}%", request.SearchQuery); query = query .Where( Restrictions.InsensitiveLike(Projections.Property(() => alias.PageUrl), searchQuery) || Restrictions.InsensitiveLike(Projections.Property(() => alias.RedirectUrl), searchQuery)); } // Select fields query = query .SelectList(select => select .Select(() => alias.Id).WithAlias(() => modelAlias.Id) .Select(() => alias.PageUrl).WithAlias(() => modelAlias.PageUrl) .Select(() => alias.RedirectUrl).WithAlias(() => modelAlias.RedirectUrl) .Select(() => alias.Version).WithAlias(() => modelAlias.Version)) .TransformUsing(Transformers.AliasToBean <SiteSettingRedirectViewModel>()); // Count var count = query.RowCount(); // Sorting and paging query.AddSortingAndPaging(request); // Get results var redirects = query.List <SiteSettingRedirectViewModel>(); model = new SearchableGridViewModel <SiteSettingRedirectViewModel> { GridOptions = request, Items = new CustomPagination <SiteSettingRedirectViewModel>(redirects, request.PageNumber, request.PageSize, count) }; } // DEBUG START Assert.IsNotNull(model); // DEBUG END }