public ActionResult Index(SortingData sort = null)
{
using (Context context = Context.Create())
{
var havePets = context.Owners
.Join(context.OwnerPets, owner => owner.Id, ownerpet => ownerpet.OwnerId,
((o, op) => new { op.OwnerId, op.PetId, o.Name }))
.GroupBy(entity => entity.OwnerId).Select(g => new PetsGroupingViewModel
{
OwnerId = g.FirstOrDefault().OwnerId,
Count = g.Count(),
OwnerName = g.FirstOrDefault().Name
});
var dontHavePets = from o in context.Owners
join op in context.OwnerPets on o.Id equals op.OwnerId into resInner
from subpet in resInner.DefaultIfEmpty()
where !resInner.Any()
select new PetsGroupingViewModel {
OwnerId = o.Id, Count = 0, OwnerName = o.Name
};
var data = havePets.Concat(dontHavePets).OrderBy(x => x.OwnerId);
IQueryable <PetsGroupingViewModel> result = data.ToList().AsQueryable();
if (sort != null && !string.IsNullOrEmpty(sort.sortDirection) && !string.IsNullOrEmpty(sort.fieldName))
{
result = result.OrderBy($"{sort.fieldName} {sort.sortDirection}");
}
ViewBag.SortOwners = sort;
return(View(new MainPageViewModel {
Groups = result.ToList()
}));
}
}
async void GetSortList()
{
SortingList.Clear();
var sortItems = await SortingData.GetSortList();
foreach (var item in sortItems)
{
if (item.id == 0)
{
Sort = item;
}
SortingList.Add(item);
}
}
private SortingData GetSortingData(string orderBy)
{
var sortingData = new SortingData();
if (orderBy != null)
{
sortingData.OrderByColumn = orderBy.Split(" ")[0];
sortingData.OrderByDirection = orderBy.Split(" ")[1] == Constants.GridAscendingKeyword
? SortingDirection.Ascending
: sortingData.OrderByDirection = SortingDirection.Descending;
}
return(sortingData);
}
public async Task <IActionResult> SearchAsync(Status status, Student student, DateRangeFilter dateRange, SortingData sortData)
{
var results = await _studentService.SearchAsync(status, student, dateRange, sortData);
_pagination = new Pagination()
{
PageSize = (sortData.PageSize != 0) ? sortData.PageSize : DefaultPageSize.PageSize,
PageNumber = (sortData.PageNumber != 0) ? sortData.PageNumber : 1,
TotalRows = results != null ? results.TotalRows : 0
};
return(CreateResponseWithStatus(status.Code, objResponse.ResponseModel(results.Rows, status, _pagination)));
}
public async Task <SearchResult> SearchAsync(Status status, Student student, DateRangeFilter dateRange, SortingData sortData)
{
var books = await _studentRepository.SearchAsync(student, status, dateRange, sortData);
List <object> rows = null;
rows = books.Select(a => new
{
a.ID,
a.StudentName,
a.BranchID,
a.BranchName,
Gender = (a.Gender == "M")?"Male":"Female",
a.Mobile,
//a.DateOfBirth,
DateOfBirth = a.DateOfBirth.ToString("dddd, dd MMMM yyyy"),
a.City,
a.Pincode,
a.Email,
a.Password,
a.Address,
a.CreatedBy,
a.CreatedByName,
a.CreatedDate,
a.UpdatedBy,
a.UpdatedByName,
a.UpdatedDate,
a.ApprovedBy,
a.ApprovedByName,
a.ApprovedDate,
}).ToList <object>();
return(new SearchResult
{
Rows = rows,
TotalRows = books.FirstOrDefault() != null?books.FirstOrDefault().TotalCount : 0
});
}
public async Task <List <Student> > SearchAsync(Student student, Status status, DateRangeFilter dateRange, SortingData sortData)
{
var publications = new List <Student>();
try
{
var parameters = new DynamicParameters();
parameters.Add("@in_DML", "Search", dbType: DbType.String, direction: ParameterDirection.Input, size: 10);
if (student.ID > 0)
{
parameters.Add("@in_ID", student.ID, dbType: DbType.Int32, direction: ParameterDirection.Input, size: 10);
}
if (!string.IsNullOrEmpty((student.StudentName)))
{
parameters.Add("@in_StudentName", student.StudentName, dbType: DbType.String, direction: ParameterDirection.Input, size: 120);
}
if (student.BranchID > 0)
{
parameters.Add("@in_BranchID", student.BranchID, dbType: DbType.Int32, direction: ParameterDirection.Input, size: 10);
}
if (student.CreatedBy > 0)
{
parameters.Add("@in_CreatedBy", student.CreatedBy, dbType: DbType.Int32, direction: ParameterDirection.Input, size: 10);
}
if (dateRange.CreatedDateStart != null && dateRange.CreatedDateEnd != null)
{
parameters.Add("@in_CreatedDateStart", dateRange.CreatedDateStart, dbType: DbType.DateTime, direction: ParameterDirection.Input, size: 20);
parameters.Add("@in_CreatedDateEnd", dateRange.CreatedDateEnd, dbType: DbType.DateTime, direction: ParameterDirection.Input, size: 20);
}
if (dateRange.UpdatedDateStart != null && dateRange.UpdatedDateEnd != null)
{
parameters.Add("@in_UpdatedDateStart", dateRange.UpdatedDateStart, dbType: DbType.DateTime, direction: ParameterDirection.Input, size: 20);
parameters.Add("@in_UpdatedDateEnd", dateRange.UpdatedDateEnd, dbType: DbType.DateTime, direction: ParameterDirection.Input, size: 20);
}
if (sortData.PageNumber > 0)
{
parameters.Add("@in_PageNumber", sortData.PageNumber, dbType: DbType.Int32, direction: ParameterDirection.Input, size: 10);
}
if (sortData.PageSize > 0)
{
parameters.Add("@in_PageSize", sortData.PageSize, dbType: DbType.Int32, direction: ParameterDirection.Input, size: 10);
}
if (sortData.SortColumn != null && sortData.SortColumn != "")
{
parameters.Add("@in_SortColumn", sortData.SortColumn, dbType: DbType.String, direction: ParameterDirection.Input, size: 20);
}
if (sortData.SortOrder != null && sortData.SortOrder != "")
{
parameters.Add("@in_SortOrder", sortData.SortOrder, dbType: DbType.String, direction: ParameterDirection.Input, size: 20);
}
publications = (await _dataProvider.ExecuteReaderAsync <Student>("[LMS_USP_Student]", parameters)).ToList();
status.Code = parameters.Get <int>("@ou_ResultNo");
status.Message = parameters.Get <string>("@ou_ResultMessage");
}
catch (Exception ex)
{
status.Code = 500;
status.Message = ex.Message;
}
return(publications);
}
