public IList <ShiftReportGridVM> GetShiftData(string search, int pageIndex, int pageSize, string sortField, string sortOrder, int?orgId, out int totalCount)
{
IQueryable <ShiftReportGridVM> shiftQueryable = null;
if (string.IsNullOrEmpty(sortField))
{
sortField = "UserName";
sortOrder = "ASC";
}
var searchDetails = JsonConvert.DeserializeObject <SearchShiftReport>(search);
shiftQueryable = GetShiftDetails(orgId, searchDetails);
var paginationRequest = new PaginationRequest
{
PageIndex = (pageIndex - 1),
PageSize = pageSize,
SearchText = search,
Sort = new Sort {
SortDirection = (sortOrder == "ASC" ? SortDirection.Ascending : SortDirection.Descending), SortBy = sortField
}
};
var result =
GenericSorterPager.GetSortedPagedList <ShiftReportGridVM>(shiftQueryable, paginationRequest, out totalCount);
result.ToList().ForEach(item =>
{
// item.ShiftDates = item.FromDate.ToString("dd/MM/yyyy");
//item.strToDate = item.ToDate.ToString("dd/MM/yyyy");
item.ShiftName = item.ShiftName + " (" + item.Fromtime.ToString("hh:mm tt") + " - " + item.Totime.ToString("hh:mm tt") + ")";
});
return(result);
}
public IList <VisitorDetailsVM> GetAllVisitorsData(string globalSearch, int pageIndex, int pageSize, string sortField, string sortOrder, out int totalCount, int?organizationId)
{
var qryVisitors = _genericService.VisitorMaster.GetAll()
.Where(item => (organizationId == null || (item.ApplicationUser.OrganizationId != null && item.ApplicationUser.OrganizationId == organizationId)) &&
(item.Address.Contains(globalSearch) ||
item.ContactNo.Contains(globalSearch) ||
item.EmailId.Contains(globalSearch) ||
item.IdNo.Contains(globalSearch) ||
item.VisitorName.Contains(globalSearch))
)
.Select(item => new VisitorDetailsVM
{
Id = item.Id,
VisitorName = item.VisitorName,
ContactAddress = item.Address,
ContactNo = item.ContactNo,
DOB = item.DOB ?? DateTime.MinValue,
EmailAddress = item.EmailId,
Gender = item.GenderId,
IdNo = item.IdNo,
Nationality = item.Nationality ?? 0,
NationalityVal = item.CountryMaster.LookUpValue,
TypeOfCard = item.TypeOfCardId,
ImagePath = item.ProfilePicPath,
IdentityImage1_Path = item.IdentityImage1_Path,
IdentityImage2_Path = item.IdentityImage2_Path,
IdentityImage3_Path = item.IdentityImage3_Path,
CompanyName = item.CompanyName,
CreatedOn = item.CreatedDate,
OrganizationName = item.ApplicationUser.Organization.CompanyName
})
.AsQueryable();
//Default Sorting
//if (string.IsNullOrEmpty(sortField))
//{
// sortField = "Id";
// sortOrder = "DESC";
//}
//creating pager object to send for filtering and sorting
var paginationRequest = new PaginationRequest
{
PageIndex = pageIndex,
PageSize = pageSize,
SearchText = globalSearch,
Sort = new Sort {
SortDirection = (sortOrder == "ASC" ? SortDirection.Ascending : SortDirection.Descending), SortBy = sortField
}
};
IList <VisitorDetailsVM> result =
GenericSorterPager.GetSortedPagedList <VisitorDetailsVM>(qryVisitors, paginationRequest, out totalCount);
return(result);
}
public IList <Entities.Product> GetProductByCategoryId(int categoryId, PaginationRequest paging, out int totalCount)
{
IQueryable <Entities.Product> query = this.UnitOfWork.Context.Products
.Where(a => a.CategoryID == categoryId)
.Include(a => a.ProductStatusType)
;
//totalCount= 0;
//return query.ToList();
IList <Entities.Product> resultList = GenericSorterPager.GetSortedPagedList <Entities.Product>(query, paging, out totalCount, ChildLoad.Include);
return(resultList);
}
public string GetOrganizationsData(string globalSearch, int pageIndex, int pageSize, string sortField = "", string sortOrder = "DESC")
{
var organizationsList = _genericService.Organization.GetAll().Where(x => x.IsActive == true)
.Select(x => new OrganisationVM
{
Id = x.Id,
CompanyName = x.CompanyName,
CountryId = x.CountryId,
Country = x.CountryMaster.LookUpValue,
CreatedOn = x.CreatedOn.ToString(),
WebSite = x.WebSite
});
if (organizationsList.Count() > 0)
{
if (!string.IsNullOrEmpty(globalSearch))
{
organizationsList = organizationsList.Where(item =>
item.CompanyName.ToLower().Contains(globalSearch.ToLower()) ||
item.Country.ToLower().Contains(globalSearch.ToLower()) ||
item.CreatedOn.ToString().ToLower().Contains(globalSearch.ToLower()) ||
item.WebSite.ToLower().Contains(globalSearch.ToLower())
).AsQueryable();
}
if (string.IsNullOrEmpty(sortField))
{
sortField = "CreatedOn";
sortOrder = "DESC";
}
//creating pager object to send for filtering and sorting
var paginationRequest = new PaginationRequest
{
PageIndex = (pageIndex - 1),
PageSize = pageSize,
SearchText = globalSearch,
Sort = new Sort {
SortDirection = (sortOrder == "ASC" ? SortDirection.Ascending : SortDirection.Descending), SortBy = sortField
}
};
int totalCount = 0;
IList <OrganisationVM> result =
GenericSorterPager.GetSortedPagedList <OrganisationVM>(organizationsList, paginationRequest, out totalCount);
var jsonData = JsonConvert.SerializeObject(result);
return(JsonConvert.SerializeObject(new { totalRows = totalCount, result = jsonData }));
}
return(null);
}
public async Task <string> DisplayAllShift(string globalSearch, int pageIndex, int pageSize, string sortField = "", string sortOrder = "ASC")
{
var user = (await _userService.GetAllAsync()).Where(x => x.Id == HttpContext.Current.User.Identity.GetUserId() && x.IsActive == true).FirstOrDefault();
int orgId = (user == null) ? 0 : (int)user.OrganizationId;
var ShiftDisplay = _genericService.ShiftDetails.GetAll().Where(x => x.IsActive == true &&
((EntityFunctions.TruncateTime(x.ShiftDate) == EntityFunctions.TruncateTime(DateTime.Now)) ||
(EntityFunctions.TruncateTime(x.ShiftDate) == EntityFunctions.TruncateTime(DateTime.Now)))).ToList()
.Select(x => new ShiftAssignmentVM
{
UserId = x.SecurityID,
UserName = x.ApplicationUser.FullName,
BuildingId = x.Gates.BuildingId,
BuildingName = x.Gates.BuildingMaster.BuildingName,
OrganizationId = x.Gates.BuildingMaster.OrganizationId,
GateId = x.GateID,
GateName = x.Gates.GateNumber,
ShitfId = x.ShiftID,
ShiftName = x.Shitfs.ShitfName + " (" + x.Shitfs.FromTime.ToString("hh:mm tt") + " - " + x.Shitfs.ToTime.ToString("hh:mm tt") + ")",
FromDate = x.ShiftDate,
})
.Where(x => x.FromDate.Date == DateTime.Now.Date && (orgId == 0 || x.OrganizationId == orgId)).AsQueryable();
var searchDetails = JsonConvert.DeserializeObject <SearchShiftReport>(globalSearch);
if (string.IsNullOrEmpty(sortField))
{
sortField = "UserName";
//sortOrder = "ASC";
}
var paginationRequest = new PaginationRequest
{
PageIndex = (pageIndex - 1),
PageSize = pageSize,
SearchText = globalSearch,
Sort = new Sort {
SortDirection = (sortOrder == "ASC" ? SortDirection.Ascending : SortDirection.Descending), SortBy = sortField
}
};
int totalCount = 0;
IList <ShiftAssignmentVM> result =
GenericSorterPager.GetSortedPagedList <ShiftAssignmentVM>(ShiftDisplay, paginationRequest, out totalCount);
var jsonData = JsonConvert.SerializeObject(result);
return(JsonConvert.SerializeObject(new { totalRows = totalCount, result = jsonData }));
//return Shift;
}
public IList <Models.ProductCM> GetFullProducts(int categoryId, PaginationRequest paging, out int totalCount, out int newPageIndex)
{
IQueryable <Models.ProductCM> query = this.UnitOfWork.Context.Products
.GroupJoin(this.UnitOfWork.Context.Categories,
p => p.CategoryID, c => c.CategoryID,
(p, c) => new { p, c })
.GroupJoin(this.UnitOfWork.Context.ProductStatusTypes,
p1 => p1.p.StatusCode, s => s.StatusCode,
(p1, s) => new { p1, s })
.SelectMany(p2 => p2.s.DefaultIfEmpty(), (p2, s2) => new { p2 = p2.p1, s2 = s2 })
.Select(f => new Models.ProductCM
{
ProductID = f.p2.p.ProductID,
ProductName = f.p2.p.ProductName,
CategoryID = f.p2.p.CategoryID,
CategoryName = f.p2.p.Category.CategoryName,
UnitPrice = f.p2.p.UnitPrice,
StatusCode = f.p2.p.StatusCode,
StatusDescription = f.s2.Description,
AvailableSince = f.p2.p.AvailableSince
});
//Add predicate for dynamic search
var predicate = PredicateBuilder.True <Models.ProductCM>();
if (categoryId > 0)
{
//predicate = predicate.And(p => p.CategoryID == categoryId);
predicate = predicate.And(p => p.CategoryID == categoryId);
}
query = query.Where(predicate);
IList <Models.ProductCM> resultList = GenericSorterPager.GetSortedPagedList <Models.ProductCM>(query, paging, out totalCount);
//For issue when refreshing data after CRUD causes no row in the current page.
newPageIndex = -1;
while (paging.PageIndex > 0 && resultList.Count < 1)
{
paging.PageIndex -= 1;
newPageIndex = paging.PageIndex;
resultList =
GenericSorterPager.GetSortedPagedList <Models.ProductCM>(query, paging, out totalCount);
}
return(resultList);
}
public IList <Entities.Product> GetProductByCategoryId(int categoryId, PaginationRequest paging, out int totalCount, out int newPageIndex)
{
IQueryable <Entities.Product> query = this.UnitOfWork.Context.Products
.Where(a => a.CategoryID == categoryId)
.Include(a => a.ProductStatusType)
;
//totalCount= 0;
//return query.ToList();
IList <Entities.Product> resultList = GenericSorterPager.GetSortedPagedList <Entities.Product>(query, paging, out totalCount, ChildLoad.Include);
//For issue when refreshing data after CRUD causes no row in the current page.
newPageIndex = -1;
while (paging.PageIndex > 0 && resultList.Count < 1)
{
paging.PageIndex -= 1;
newPageIndex = paging.PageIndex;
resultList =
GenericSorterPager.GetSortedPagedList <Entities.Product>(query, paging, out totalCount, ChildLoad.Include);
}
return(resultList);
}
public async Task <string> GetUsersData(string globalSearch, int pageIndex, int pageSize, string sortField = "", string sortOrder = "ASC")
{
var user = (await _userService.GetAllAsync()).Where(x => x.Id == HttpContext.Current.User.Identity.GetUserId() && x.IsActive == true).FirstOrDefault();
var getUsers = (await _userService.GetAllAsync()).Where(x => x.Organization.IsActive == true &&
(user == null || (user != null && x.OrganizationId == user.OrganizationId &&
x.Id != HttpContext.Current.User.Identity.GetUserId()))).AsQueryable();
var gerUserRoleMappingData = (await _applicationRoleService.GetAllAsync()).AsQueryable();
if (string.IsNullOrEmpty(sortField))
{
sortField = "CreatedOn";
sortOrder = "DESC";
}
ApplicationRole role = null;
if (user != null)
{
role = await _applicationRoleService.FindByIdAsync(user.Roles.FirstOrDefault().RoleId);
}
IQueryable <ApplicationRole> getRoles = (await _applicationRoleService.GetAllAsync()).AsQueryable();
var temp = (from users in getUsers
join roles in getRoles on users.Roles.First().RoleId equals roles.Id
into new_roles
from roles in new_roles.DefaultIfEmpty()
select new UsersVM
{
UserId = users.Id,
FullName = users.FullName,
Email = users.Email,
ContactNumber = users.PhoneNumber,
GenderId = users.GenderId,
UserName = users.UserName,
RoleId = roles.Id,
RoleName = roles.Name,
OrganizationId = users.OrganizationId,
OrganizationName = users.Organization.CompanyName,
Nationality = users.Nationality,
ProfilePicturePath = users.ProfilePicturePath,
CreatedOn = users.CreatedOn,
CreatedBy = users.CreatedBy,
UpdatedBy = users.UpdatedBy,
UpdatedOn = users.UpdatedOn,
IsImageAvailable = false
}).AsQueryable();
if (temp.Count() > 0)
{
if (!string.IsNullOrEmpty(globalSearch))
{
temp = temp.Where(item =>
item.ContactNumber.ToLower().Contains(globalSearch.ToLower()) ||
item.FullName.ToLower().Contains(globalSearch.ToLower()) ||
item.Email.ToLower().Contains(globalSearch.ToLower()) ||
item.RoleName.ToLower().Contains(globalSearch.ToLower()))
.AsQueryable();
}
//creating pager object to send for filtering and sorting
var paginationRequest = new PaginationRequest
{
PageIndex = (pageIndex - 1),
PageSize = pageSize,
SearchText = globalSearch,
Sort = new Sort {
SortDirection = (sortOrder == "ASC" ? SortDirection.Ascending : SortDirection.Descending), SortBy = sortField
}
};
int totalCount = 0;
IList <UsersVM> result =
GenericSorterPager.GetSortedPagedList <UsersVM>(temp, paginationRequest, out totalCount);
result.ToList().ForEach(item =>
{
if (item.ProfilePicturePath != null)
{
var filePath = HttpContext.Current.Server.MapPath("\\") + "" + item.ProfilePicturePath;
item.IsImageAvailable = System.IO.File.Exists(filePath);
}
});
var jsonData = JsonConvert.SerializeObject(result);
return(JsonConvert.SerializeObject(new { totalRows = totalCount, result = jsonData }));
}
return(null);
}
public async Task <string> GetAllshift(string globalSearch, int pageIndex, int pageSize, string sortField = "", string sortOrder = "ASC")
{
var user = (await _userService.GetAllAsync()).Where(x => x.Id == HttpContext.Current.User.Identity.GetUserId() && x.IsActive == true).FirstOrDefault();
if (user == null)
{
LstShiftDetailsVM = _genericService.ShitfMaster.GetAll().Where(x => x.IsActive == true).AsEnumerable()
.Select(x => new ShiftDetailsVM
{
Id = x.Id,
ShitfName = x.ShitfName,
ToTime = (x.ToTime),
FromTime = x.FromTime,
strFromTime = x.FromTime.ToString("hh:mm tt"),
strToTime = x.ToTime.ToString("hh:mm tt"),
CreatedOn = x.CreatedOn.ToString(),
}).AsQueryable();
}
else
{
string userid = user.Id;
if (userid != null)
{
LstShiftDetailsVM = _genericService.ShitfMaster.GetAll().Where(x => x.IsActive == true && x.OrganizationId == null || x.OrganizationId == user.OrganizationId).AsEnumerable()
.Select(x => new ShiftDetailsVM
{
Id = x.Id,
ShitfName = x.ShitfName,
ToTime = (x.ToTime),
FromTime = x.FromTime,
strFromTime = x.FromTime.ToString("hh:mm tt"),
strToTime = x.ToTime.ToString("hh:mm tt"),
CreatedOn = x.CreatedOn.ToString(),
}).AsQueryable();
}
}
if (LstShiftDetailsVM.Count() > 0)
{
if (!string.IsNullOrEmpty(globalSearch))
{
LstShiftDetailsVM = LstShiftDetailsVM.Where(item =>
item.ShitfName.ToLower().Contains(globalSearch.ToLower()) ||
item.strFromTime.ToLower().Contains(globalSearch.ToLower()) ||
item.strToTime.ToLower().Contains(globalSearch.ToLower()) ||
item.CreatedOn.ToLower().Contains(globalSearch.ToLower()))
.AsQueryable();
}
if (string.IsNullOrEmpty(sortField))
{
sortField = "ShitfName";
}
var paginationRequest = new PaginationRequest
{
PageIndex = (pageIndex - 1),
PageSize = pageSize,
SearchText = globalSearch,
Sort = new Sort {
SortDirection = (sortOrder == "ASC" ? SortDirection.Ascending : SortDirection.Descending), SortBy = sortField
}
};
int totalCount = 0;
IList <ShiftDetailsVM> result =
GenericSorterPager.GetSortedPagedList <ShiftDetailsVM>(LstShiftDetailsVM, paginationRequest, out totalCount);
var jsonData = JsonConvert.SerializeObject(result);
return(JsonConvert.SerializeObject(new { totalRows = totalCount, result = jsonData }));
}
return(null);
}
public async Task <string> GetAllShiftAssignment(string globalSearch, int pageIndex, int pageSize, string sortField = "", string sortOrder = "ASC")
{
IList <ShiftAssignmentVM> LSTShiftAssignmentVMTemp = null;
LSTShiftAssignmentVM = new List <ShiftAssignmentVM>();
var user = (await _userService.GetAllAsync()).Where(x => x.Id == HttpContext.Current.User.Identity.GetUserId() && x.IsActive == true).FirstOrDefault();
if (user == null)
{
LSTShiftAssignmentVMTemp = _genericService.ShitfAssignment.GetAll().Where(x => x.IsActive == true).ToList()
.Select(x => new ShiftAssignmentVM
{
BuildingId = x.BuildingId,
BuildingName = x.Gates.BuildingMaster.BuildingName,
GateId = x.GateId,
GateName = x.Gates.GateNumber,
ShitfId = x.ShitfId,
ShiftName = x.Shitfs.ShitfName,
UserId = x.UserId,
UserName = x.ApplicationUser.FullName,
FromDate = x.FromDate,
ToDate = x.ToDate,
strFromDate = x.FromDate.ToString("dd/MM/yyyy"),
strToDate = x.ToDate.ToString("dd/MM/yyyy"),
Id = x.Id,
City = (x.BuildingMaster.CityId == CONST_OTHER_ID) ? x.BuildingMaster.OtherCity : x.BuildingMaster.CityMaster.LookUpValue,
OtherCity = x.BuildingMaster.OtherCity
}).ToList();
LSTShiftAssignmentVMTemp.ToList().ForEach(item =>
{
var shiftDetails = _genericService.ShiftDetails.GetAll().FirstOrDefault(item_db => item_db.ShiftID == item.ShitfId && item_db.SecurityID == item.UserId && item_db.IsActive);
if (shiftDetails != null)
{
LSTShiftAssignmentVM.Add(item);
}
});
}
else
{
int orgId = user.Organization.Id;
if (orgId != null)
{
//var data = _genericService.BuildingMaster.GetAll().Where(x => x.OrganizationId == orgId).FirstOrDefault();
LSTShiftAssignmentVMTemp = _genericService.ShitfAssignment.GetAll().Where(x => x.IsActive == true && x.BuildingMaster.OrganizationId == orgId).ToList()
.Select(x => new ShiftAssignmentVM
{
BuildingId = x.BuildingId,
BuildingName = x.Gates.BuildingMaster.BuildingName,
GateId = x.GateId,
GateName = x.Gates.GateNumber,
ShitfId = x.ShitfId,
ShiftName = x.Shitfs.ShitfName,
UserId = x.UserId,
UserName = x.ApplicationUser.FullName,
FromDate = x.FromDate,
ToDate = x.ToDate,
strFromDate = x.FromDate.ToString("dd/MM/yyyy"),
strToDate = x.ToDate.ToString("dd/MM/yyyy"),
Id = x.Id,
City = x.BuildingMaster.CityMaster.LookUpValue
}).ToList();
LSTShiftAssignmentVMTemp.ToList().ForEach(item =>
{
var shiftDetails = _genericService.ShiftDetails.GetAll().FirstOrDefault(item_db => item_db.ShiftID == item.ShitfId && item_db.SecurityID == item.UserId && item_db.IsActive);
if (shiftDetails != null)
{
LSTShiftAssignmentVM.Add(item);
}
});
}
}
if (LSTShiftAssignmentVM.Count() > 0)
{
if (!string.IsNullOrEmpty(globalSearch))
{
LSTShiftAssignmentVM = LSTShiftAssignmentVM.Where(item =>
item.UserName.ToLower().Contains(globalSearch.ToLower()) ||
item.BuildingName.ToLower().Contains(globalSearch.ToLower()) ||
item.GateName.ToLower().Contains(globalSearch.ToLower()) ||
item.ShiftName.ToLower().Contains(globalSearch.ToLower())
).ToList();
}
if (string.IsNullOrEmpty(sortField))
{
sortField = " ShiftName";
}
var paginationRequest = new PaginationRequest
{
PageIndex = (pageIndex - 1),
PageSize = pageSize,
SearchText = globalSearch,
Sort = new Sort {
SortDirection = (sortOrder == "ASC" ? SortDirection.Ascending : SortDirection.Descending), SortBy = sortField
}
};
int totalCount = 0;
IList <ShiftAssignmentVM> result =
GenericSorterPager.GetSortedPagedList <ShiftAssignmentVM>(LSTShiftAssignmentVM.AsQueryable(), paginationRequest, out totalCount);
var jsonData = JsonConvert.SerializeObject(result.OrderByDescending(x => x.Id));
return(JsonConvert.SerializeObject(new { totalRows = totalCount, result = jsonData }));
}
return(null);
}
public async Task <string> GetBuildingData(string globalSearch, int pageIndex, int pageSize, string sortField = "", string sortOrder = "ASC")
{
var user = (await _userService.GetAllAsync()).Where(x => x.Id == HttpContext.Current.User.Identity.GetUserId() && x.IsActive == true).FirstOrDefault();
if (user == null)
{
lstBuildingVM = _genericService.BuildingMaster.GetAll().Where(x => x.IsActive == true)
// var d = _genericService.BuildingMaster.GetAll().Where(x => x.IsActive == true)
.Select(x => new BuildingVM
{
Id = x.Id,
BuildingName = x.BuildingName,
OrganizationId = x.OrganizationId,
Address = x.Address,
ZipCode = x.ZipCode,
CityId = x.CityId,
//NationalityId = x.CountryId,//x.CityMaster.ParentValues.ParentId,
CountryId = x.CountryId,
StateId = x.StateId,//x.CityMaster.ParentId,
OrganizationName = x.Organization.CompanyName,
EmailId = x.EmailId,
ContactNumber = x.ContactNumber,
FaxNumber = x.FaxNumber,
WebSite = x.WebSite,
txtcountry = x.OtherCountry,
txtstate = x.OtherState,
txtcity = x.OtherCity,
CreatedOn = x.CreatedOn.ToString(),
Country = x.CountryMaster.LookUpValue
//Country=x.CityMaster.ParentValues.ParentValues.LookUpValue
});
}
else
{
int orgId = user.Organization.Id;
if (orgId != 0)
{
lstBuildingVM = _genericService.BuildingMaster.GetAll().Where(x => x.IsActive == true && x.OrganizationId == orgId)
.Select(x => new BuildingVM
{
Id = x.Id,
BuildingName = x.BuildingName,
OrganizationId = x.OrganizationId,
CityId = x.CityId,
Address = x.Address,
ZipCode = x.ZipCode,
//NationalityId = x.CityMaster.ParentValues.ParentId,
//StateId = x.CityMaster.ParentId,
CountryId = x.CountryId,
StateId = x.StateId,
OrganizationName = x.Organization.CompanyName,
EmailId = x.EmailId,
ContactNumber = x.ContactNumber,
FaxNumber = x.FaxNumber,
WebSite = x.WebSite,
txtcountry = x.OtherCountry,
txtstate = x.OtherState,
txtcity = x.OtherCity,
CreatedOn = x.CreatedOn.ToString(),
Country = x.CountryMaster.LookUpValue
// Country = x.CityMaster.ParentValues.ParentValues.LookUpValue
});
}
//int orgId = user.Organization.Id;
//organizations = _genericService.Organization.GetAll()
// .Where(x => x.IsActive == true && x.Id == orgId)
// .Select(x => new GeneralDropDownVM { Id = x.Id, Name = x.CompanyName });
}
if (lstBuildingVM.Count() > 0)
{
if (!string.IsNullOrEmpty(globalSearch))
{
lstBuildingVM = lstBuildingVM.Where(item =>
item.Address.ToLower().Contains(globalSearch.ToLower()) ||
item.BuildingName.ToLower().Contains(globalSearch.ToLower()) ||
//item.ZipCode.ToLower().Contains(globalSearch.ToLower())||
item.OrganizationName.ToLower().Contains(globalSearch.ToLower()) ||
item.Country.ToLower().Contains(globalSearch.ToLower())
).AsQueryable();
}
if (string.IsNullOrEmpty(sortField))
{
sortField = "CreatedOn";
}
var paginationRequest = new PaginationRequest
{
PageIndex = (pageIndex - 1),
PageSize = pageSize,
SearchText = globalSearch,
Sort = new Sort {
SortDirection = (sortOrder == "ASC" ? SortDirection.Ascending : SortDirection.Descending), SortBy = sortField
}
};
int totalCount = 0;
IList <BuildingVM> result =
GenericSorterPager.GetSortedPagedList <BuildingVM>(lstBuildingVM, paginationRequest, out totalCount);
var jsonData = JsonConvert.SerializeObject(result.OrderByDescending(x => x.Id));
return(JsonConvert.SerializeObject(new { totalRows = totalCount, result = jsonData }));
}
return(null);
}
public IList <VisitorsDetailsVM> VisitorData(string search, int pageIndex, int pageSize, string sortField, string sortOrder, out int totalCount)
{
var visitorsDetails = (from vm in _genericService.VisitorMaster.GetAll()
join vd in _genericService.VisitDetails.GetAll()
on vm.Id equals vd.VisitorId
select new VisitorsDetailsVM
{
//VisitorId = vm.Id,
VisitorName = vm.VisitorName,
CheckIn = vd.CheckIn.ToString(),
CheckOut = vd.CheckOut.ToString(),
ContactNumber = vm.ContactNo,
VisitDetails = vd.PurposeOfVisit,
BuildingId = vd.GateMaster.BuildingId,
GateId = vd.CheckInGate,
SecurityId = vd.CreatedBy,
Building = vd.GateMaster.BuildingMaster.BuildingName,
Gate = vd.GateMaster.GateNumber,
Security = vd.CreatedUser.FullName,
CompanyName = vm.CompanyName,
ContactPerson = vd.ContactPerson
}).ToList();
if (string.IsNullOrEmpty(sortField))
{
sortField = "CheckIn";
sortOrder = "DES";
}
var searchDetails = JsonConvert.DeserializeObject <SearchVisitorVM>(search);
string targetDate = "";
if (!string.IsNullOrEmpty(searchDetails.CheckOut))
{
DateTime toDateFormat = Convert.ToDateTime(searchDetails.CheckOut);
targetDate = toDateFormat.AddDays(1).ToString();
}
visitorsDetails = visitorsDetails.Where(
x => (searchDetails == null ||
((string.IsNullOrEmpty(searchDetails.SecurityId) || x.SecurityId == searchDetails.SecurityId) &&
(searchDetails.GateId == 0 || x.GateId == searchDetails.GateId) &&
(searchDetails.BuildingId == 0 || x.BuildingId == searchDetails.BuildingId) &&
(string.IsNullOrEmpty(searchDetails.VisitorName) || x.VisitorName.ToLower().Contains(searchDetails.VisitorName.ToLower())) &&
(string.IsNullOrEmpty(searchDetails.CheckIn) || Convert.ToDateTime(x.CheckIn) >= Convert.ToDateTime(searchDetails.CheckIn)) &&
(string.IsNullOrEmpty(targetDate) || Convert.ToDateTime(x.CheckIn) <= Convert.ToDateTime(targetDate))
)
)).ToList();
// visitorsDetails = visitorsDetails.OrderBy(x => x.CheckIn).ToList();
//}
//creating pager object to send for filtering and sorting
var paginationRequest = new PaginationRequest
{
PageIndex = (pageIndex - 1),
PageSize = pageSize,
SearchText = search,
Sort = new Sort {
SortDirection = (sortOrder == "ASC" ? SortDirection.Ascending : SortDirection.Descending), SortBy = sortField
}
};
IList <VisitorsDetailsVM> result =
GenericSorterPager.GetSortedPagedList <VisitorsDetailsVM>(visitorsDetails.AsQueryable(), paginationRequest, out totalCount);
return(result);
}
public IList <Models.ProductCM> GetProductList(ProductSearchField productSearchField, string productSearchText,
Decimal?priceLow, Decimal?priceHigh, DateTime?dateFrom, DateTime?dateTo, int?statusCode,
PaginationRequest paging, out int totalCount, out int newPageIndex)
{
//Query to join parent and child entities and return custom model
//IQueryable<Models.ProductCM> query = this.UnitOfWork.Context.Products
// .Join(this.UnitOfWork.Context.Categories, p => p.CategoryID, c => c.CategoryID,
// (p, c) => new { p, c })
// .Join(this.UnitOfWork.Context.ProductStatusTypes, p2 => p2.p.StatusCode, ps => ps.StatusCode,
// (p2, ps) => new Models.ProductCM
// {
// ProductID = p2.p.ProductID,
// ProductName = p2.p.ProductName,
// CategoryID = p2.p.CategoryID,
// CategoryName = p2.c.CategoryName,
// UnitPrice = p2.p.UnitPrice,
// StatusCode = p2.p.StatusCode,
// StatusDescription = ps.Description,
// AvailableSince = p2.p.AvailableSince
// });
//Test
//var rtnList = this.UnitOfWork.Context.Database.SqlQuery<Entities.Category>("GetAllCategorisAndProducts").ToList();
//var blogs = ((IObjectContextAdapter)this.UnitOfWork.Context)
//.ObjectContext
//.Translate<Entities.Category>((reader, "Blogs", MergeOption.AppendOnly);
IQueryable <Models.ProductCM> query = this.UnitOfWork.Context.Products
.GroupJoin(this.UnitOfWork.Context.Categories,
p => p.CategoryID, c => c.CategoryID,
(p, c) => new { p, c })
.GroupJoin(this.UnitOfWork.Context.ProductStatusTypes,
p1 => p1.p.StatusCode, s => s.StatusCode,
(p1, s) => new { p1, s })
.SelectMany(p2 => p2.s.DefaultIfEmpty(), (p2, s2) => new { p2 = p2.p1, s2 = s2 })
.Select(f => new Models.ProductCM
{
ProductID = f.p2.p.ProductID,
ProductName = f.p2.p.ProductName,
CategoryID = f.p2.p.CategoryID,
CategoryName = f.p2.p.Category.CategoryName,
UnitPrice = f.p2.p.UnitPrice,
StatusCode = f.p2.p.StatusCode,
StatusDescription = f.s2.Description,
AvailableSince = f.p2.p.AvailableSince
});
//var query =
// from pr in this.UnitOfWork.Context.Products
// join ca in this.UnitOfWork.Context.Categories
// on pr.CategoryID equals ca.CategoryID
// join ps in this.UnitOfWork.Context.ProductStatusTypes
// on pr.StatusCode equals ps.StatusCode into tempJoin
// from t2 in tempJoin.DefaultIfEmpty()
// select new Models.ProductCM
// {
// ProductID = pr.ProductID,
// ProductName = pr.ProductName,
// CategoryID = pr.CategoryID,
// CategoryName = ca.CategoryName,
// UnitPrice = pr.UnitPrice,
// StatusCode = pr.StatusCode,
// StatusDescription = t2.Description,
// AvailableSince = pr.AvailableSince
// };
//More readable code:
//var query =
// from pr in this.UnitOfWork.Context.Products
// from ca in this.UnitOfWork.Context.Categories
// .Where(ca => ca.CategoryID == pr.CategoryID)
// from ps in this.UnitOfWork.Context.ProductStatusTypes
// .Where(ps => ps.StatusCode == pr.StatusCode).DefaultIfEmpty()
// select new Models.ProductCM
// {
// ProductID = pr.ProductID,
// ProductName = pr.ProductName,
// CategoryID = pr.CategoryID,
// CategoryName = ca.CategoryName,
// UnitPrice = pr.UnitPrice,
// StatusCode = pr.StatusCode,
// StatusDescription = ps.Description,
// AvailableSince = pr.AvailableSince
// };
var predicate = PredicateBuilder.True <Models.ProductCM>();
if (!string.IsNullOrEmpty(productSearchText))
{
if (productSearchField == ProductSearchField.CategoryId && Util.IsNumeric(productSearchText))
{
int categoryId = Convert.ToInt32(productSearchText);
predicate = predicate.And(p => p.CategoryID == categoryId);
}
if (productSearchField == ProductSearchField.CategoryName)
{
predicate = predicate.And(p => p.CategoryName.ToLower().Contains(productSearchText.ToLower()));
}
if (productSearchField == ProductSearchField.ProductId && Util.IsNumeric(productSearchText))
{
int productId = Convert.ToInt32(productSearchText);
predicate = predicate.And(p => p.ProductID == productId);
}
if (productSearchField == ProductSearchField.ProductName)
{
predicate = predicate.And(p => p.ProductName.ToLower().Contains(productSearchText.ToLower()));
}
}
if (priceLow != null)
{
predicate = predicate.And(p => p.UnitPrice >= priceLow.Value);
}
if (priceHigh != null)
{
predicate = predicate.And(p => p.UnitPrice <= priceHigh.Value);
}
if (dateFrom != null)
{
predicate = predicate.And(p => p.AvailableSince >= dateFrom.Value);
}
if (dateTo != null)
{
predicate = predicate.And(p => p.AvailableSince <= dateTo.Value);
}
if (statusCode != null)
{
predicate = predicate.And(p => p.StatusCode == statusCode.Value);
}
query = query.Where(predicate);
//IList<Models.ProductCM> resultList1 = query.ToList();
IList <Models.ProductCM> resultList =
GenericSorterPager.GetSortedPagedList <Models.ProductCM>(query, paging, out totalCount);
//For issue when refreshing data after CRUD causes no row in the current page.
newPageIndex = -1;
while (paging.PageIndex > 0 && resultList.Count < 1)
{
paging.PageIndex -= 1;
newPageIndex = paging.PageIndex;
resultList =
GenericSorterPager.GetSortedPagedList <Models.ProductCM>(query, paging, out totalCount);
}
return(resultList);
}
public async Task <string> GetAllGate(string globalSearch, int pageIndex, int pageSize, string sortField = "", string sortOrder = "ASC")
{
var user = (await _userService.GetAllAsync()).Where(x => x.Id == HttpContext.Current.User.Identity.GetUserId() && x.IsActive == true).FirstOrDefault();
if (user == null)
{
lstgateVM = _genericService.GateMaster.GetAll().Where(x => x.IsActive == true)
.Select(x => new GatesVM
{
Id = x.Id,
BuildingId = x.BuildingId,
GateNumber = x.GateNumber,
BuildingName = x.BuildingMaster.BuildingName,
State = x.BuildingMaster.StateMaster.LookUpValue,
Country = x.BuildingMaster.CountryMaster.LookUpValue,
City = x.BuildingMaster.CityMaster.LookUpValue,
OtherCity = x.BuildingMaster.OtherCity
});
}
else
{
int orgId = user.Organization.Id;
if (orgId != null)
{
lstgateVM = _genericService.GateMaster.GetAll().Where(x => x.IsActive == true && x.BuildingMaster.OrganizationId == orgId)
.Select(x => new GatesVM
{
Id = x.Id,
BuildingId = x.BuildingId,
GateNumber = x.GateNumber,
BuildingName = x.BuildingMaster.BuildingName,
State = x.BuildingMaster.StateMaster.LookUpValue,
Country = x.BuildingMaster.CountryMaster.LookUpValue,
City = x.BuildingMaster.CityMaster.LookUpValue,
OtherCity = x.BuildingMaster.OtherCity
});
}
}
if (lstgateVM.Count() > 0)
{
if (!string.IsNullOrEmpty(globalSearch))
{
lstgateVM = lstgateVM.Where(item =>
item.GateNumber.ToLower().Contains(globalSearch.ToLower()) ||
item.BuildingName.ToLower().Contains(globalSearch.ToLower()) ||
item.Country.ToLower().Contains(globalSearch.ToLower()) ||
item.State.ToLower().Contains(globalSearch.ToLower()) ||
item.City.ToLower().Contains(globalSearch.ToLower()) ||
item.OtherCity.ToLower().Contains(globalSearch.ToLower()))
.AsQueryable();
}
if (string.IsNullOrEmpty(sortField))
{
sortField = "GateNumber";
}
var paginationRequest = new PaginationRequest
{
PageIndex = (pageIndex - 1),
PageSize = pageSize,
SearchText = globalSearch,
Sort = new Sort {
SortDirection = (sortOrder == "ASC" ? SortDirection.Ascending : SortDirection.Descending), SortBy = sortField
}
};
int totalCount = 0;
IList <GatesVM> result =
GenericSorterPager.GetSortedPagedList <GatesVM>(lstgateVM, paginationRequest, out totalCount);
result.ToList().ForEach(item =>
{
if (item.City == "Others")
{
item.City = item.OtherCity;
}
});
var jsonData = JsonConvert.SerializeObject(result);
return(JsonConvert.SerializeObject(new { totalRows = totalCount, result = jsonData }));
}
return(null);
}