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);
}
/// <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 <PeanutParticipation> FindParticipationsOfUser(IPageable pageRequest, User forUser, DateTime from, DateTime to,
IList <PeanutParticipationState> participationStates = null)
{
HibernateDelegate <IPage <PeanutParticipation> > finder = delegate(ISession session) {
IQueryOver <PeanutParticipation, PeanutParticipation> queryOver = session.QueryOver <PeanutParticipation>();
IQueryOver <PeanutParticipation, UserGroupMembership> joinUserGroupMembership =
queryOver.JoinQueryOver(participation => participation.UserGroupMembership);
/*Nur für den Nutzer*/
joinUserGroupMembership.Where(userGroupMembership => userGroupMembership.User == forUser);
IQueryOver <PeanutParticipation, Peanut> joinPeanut = queryOver.JoinQueryOver(participation => participation.Peanut);
joinPeanut
/*Am oder nach dem ab Datum*/
.And(peanut => peanut.Day >= from)
.And(peanut => peanut.PeanutState != PeanutState.Canceled)
/*Nicht nach dem To Datum*/
.AndNot(peanut => peanut.Day > to);
if (participationStates != null && participationStates.Any())
{
/*Nur wenn die Teilnahme einen bestimmten Status hat.*/
queryOver.AndRestrictionOn(part => part.ParticipationState).IsIn(participationStates.ToArray());
}
return(FindPage(queryOver, pageRequest));
};
return(HibernateTemplate.Execute(finder));
}
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 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);
}
private static IQueryOver<Menus, Menus> LoadIndividualMenus(IList<Menu> menus, IQueryOver<Menus, Menus> query)
{
var ids = menus.Where(m => m.Id.HasValue).Select(m => m.Id.Value).ToArray();
query = query.AndRestrictionOn(p => p.MenuId).IsInG(ids);
return query;
}
private static bool LoadIndividualLists(
IList<ShoppingList> lists,
bool defaultsLoaded,
ref IQueryOver<ShoppingLists, ShoppingLists> query)
{
if (lists != null)
{
defaultsLoaded = lists.Contains(ShoppingList.Default);
var ids = lists.Where(list => list.Id.HasValue).Select(l => l.Id.Value).ToArray();
query = query.AndRestrictionOn(x => x.ShoppingListId).IsInG(ids);
}
return defaultsLoaded;
}
public IList <ProviderVm> QueryallDelistedProviderforJson(out int totalRecord, out int totalcountinresult, string search, int start, int lenght, string sortColumn, string sortOrder, string srcProviderName, string scrAddress, int state, int zone, bool useDate, DateTime scrFromDate, DateTime scrToDate, string scrUsers, int otherFilters, int plantype)
{
IQueryOver <Provider, Provider> query = _session.QueryOver <Provider>().Where(x => x.IsDeleted == false && x.isDelisted);
List <ProviderVm> response = new List <ProviderVm>();
if (!string.IsNullOrEmpty(srcProviderName))
{
srcProviderName = "%" + srcProviderName + "%";
query.AndRestrictionOn(x => x.Name).IsInsensitiveLike(srcProviderName);
}
if (!string.IsNullOrEmpty(scrAddress))
{
scrAddress = "%" + scrAddress + "%";
query.AndRestrictionOn(x => x.Address).IsInsensitiveLike(scrAddress);
}
if (state > 0)
{
query.Where(x => x.State.Id == state);
}
if (zone > 0)
{
query.Where(x => x.State.Zone == zone);
}
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))
{
query.Where(x => x.CreatedBy == scrUsers);
}
if (plantype > -1)
{
string planid = "%" + Convert.ToString(plantype) + "%";
IQueryOver <Provider, Provider> query2 = _session.QueryOver <Provider>().Where(x => x.IsDeleted == false && x.AuthorizationStatus == 1);
query2.AndRestrictionOn(x => x.Providerplans).IsInsensitiveLike(planid);
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.Providerplans.Split(',');
output.AddRange(from plan in allplans where plan.Trim() == Convert.ToString(plantype) select item.Id);
}
query.WhereRestrictionOn(bp => bp.Id)
.IsIn(output);
}
//sort order
//return normal list.
totalRecord = query.RowCount();
totalcountinresult = totalRecord;
IList <Provider> list = query.Skip(start).Take(lenght).List();
if (list != null)
{
response.AddRange(from item in list
let split = !string.IsNullOrEmpty(item.Providerservices) ? item.Providerservices.Split(',') : new string[] { }
let split2 = !string.IsNullOrEmpty(item.Providerplans) ? item.Providerplans.Split(',') : new string[] { }
let servicelist = split.Select(serv => _servicevc.GetService(Convert.ToInt32(serv)).Name).ToList()
let planlist = split2.Select(plan => _plansvc.GetPlan(Convert.ToInt32(plan)).Name).ToList()
let singleOrDefault = _userService.GetAllUsers().SingleOrDefault(x => x.Guid.ToString().ToLower() == Convert.ToString(item.CreatedBy).ToLower())
where singleOrDefault != null
let providerAccount = item.Provideraccount
where providerAccount != null
select new ProviderVm()
{
Id = item.Id,
Name = item.Name.ToUpper(),
Code = item.Code,
SubCode = item.SubCode,
Phone = item.Phone,
Phone2 = item.Phone2,
Email = item.Email,
Website = item.Website,
Address = item.Address,
Provideraccount = providerAccount ?? new ProviderAccount(),
Assignee = item.Assignee,
AssigneeName = _userService.GetUser(item.Assignee).Name,
State = item.State,
Lganame = item.Lga.Name,
Lgaid = item.Id,
AuthorizationStatus = item.AuthorizationStatus,
AuthorizationNote = item.AuthorizationNote,
AuthorizedBy = item.AuthorizedBy,
Status = item.Status,
Providerplans = planlist,
Providerservices = servicelist,
CreatedBy = item.CreatedBy != null ? singleOrDefault.Name : "",
Zone = _helpersvc.GetzonebyId(Convert.ToInt32(item.State.Zone)).Name,
BankName = _helpersvc.Getbank(providerAccount.BankId).Name,
CreatedDate = item.CreatedOn.ToString("dd MMM yyyy"),
ProviderplansStr = item.Providerplans,
ProviderservicesStr = item.Providerservices,
AuthorizedDate = item.AuthorizedDate,
AuthorizedByString = item.AuthorizedBy > 0 ? _userService.GetUser(item.AuthorizedBy).Name : "--",
AuthorizationStatusStr = Enum.GetName(typeof(AuthorizationStatus), item.AuthorizationStatus)
});
return(response);
}
return(new List <ProviderVm>());
}