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
}
