public DtResult <Address> GetAll(PagingQueryDto input)
{
var query = _addressRepo.GetAll();
if (!string.IsNullOrWhiteSpace(input.City))
{
query = query.Where(q => q.City == input.City);
}
var filteredResultsCount = query.Count();
var totalResultsCount = query.Count();
var result = new DtResult <Address>
{
Draw = input.Draw,
RecordsTotal = totalResultsCount,
RecordsFiltered = filteredResultsCount,
Data = query
.Skip(input.Start)
.Take(input.Length)
.ToList()
};
return(result);
}
public async Task <DtResult <TVm> > Query <TE>(IQueryable <TE> queryable, DtParameters parameters)
where TE : class, IEntity
{
EnsureValidColumns(parameters);
var result = new DtResult <TVm> {
Draw = parameters.Draw
};
var query = queryable;
result.RecordsTotal = await query.CountAsync();
result.RecordsFiltered = result.RecordsTotal;
query = ChainOrder(query, parameters);
query = ChainFilter(query, parameters, out var isFiltered);
if (isFiltered)
{
result.RecordsFiltered = await query.CountAsync();
}
query = ChainPagination(query, parameters);
var entities = await query.ToListAsync();
result.Data = _mapper.Map <List <TVm> >(entities);
return(result);
}
internal DtResult <Reason> GetList(DTParameters param)
{
var result = new DtResult <Reason>();
var query = (from v in _dbContext.Reason
where v.Status.Equals("1")
select new Reason
{
Id = v.Id,
CreatedDate = v.CreatedDate,
Name = v.Name,
});
result.data = new List <Reason>();
result.recordsTotal = query.Count();
var searchColumn = (from sr in param.Columns where !string.IsNullOrWhiteSpace(sr.Search.Value) select sr).ToList();
if (searchColumn?.Count() > 0)
{
foreach (var item in searchColumn)
{
if (item.Name.ToLower().Equals("name"))
{
if (!string.IsNullOrWhiteSpace(item.Search.Value))
{
query = query.Where(w => w.Name.Contains(item.Search.Value));
}
}
}
}
if (param.Search != null && !string.IsNullOrEmpty(param.Search.Value))
{
var keyword = param.Search.Value;
query = query.Where(w => w.Name.Contains(keyword));
//w.Entity.Value.Contains(keyword) || w.PromoterName.Contains(keyword));
}
if (param.Order != null && param.Order.Length > 0)
{
foreach (var item in param.Order)
{
if (param.Columns[item.Column].Data.Equals("name"))
{
query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.Name) : query.OrderBy(o => o.Name);
}
}
}
result.recordsFiltered = query.Count();
if (param.Length > 0)
{
query = query.Skip(param.Start).Take(param.Length);
}
var entries = query.ToList();
foreach (var e in entries)
{
result.data.Add(e);
}
return(result);
}
public static Task <DtResult <TResult> > GetResultDatatablesAsync <TResult, TEntity>(IQueryable <TEntity> query,
string search, int?draw, string sortOrder, int?start, int?length, List <string> columnFilters) where TResult : BaseDto where TEntity : BaseEntity
{
try
{
//var mapper = DependencyResolver.Current.GetService<IMapper>();
IMapper mapper = new CustomMapper();
var filter = FilterResult(search, query, columnFilters);
//var count = filter.CountAsync().Result;
var count = filter.Count();
if (sortOrder.StartsWith("String", StringComparison.Ordinal))
{
sortOrder = sortOrder.Replace("String", "");
}
var type = typeof(TEntity).GetTypeInfo().GetProperties().FirstOrDefault(a => sortOrder.StartsWith(a.Name, StringComparison.Ordinal));
//var resultFilter = type != null ? filter.SortBy(sortOrder)
//.Skip(start ?? 0)
//.Take(length ?? 0).ToListAsync().Result : Activator.CreateInstance(typeof(List<>).MakeGenericType(typeof(TResult)));
var resultFilter = type != null?filter.OrderBy(a => sortOrder)
.Skip(start ?? 0)
.Take(length ?? 0) : Activator.CreateInstance(typeof(List <>).MakeGenericType(typeof(TResult)));
var result = new DtResult <TResult>
{
draw = draw,
data = mapper.MapTo <List <TResult> >(resultFilter),
recordsFiltered = count,
recordsTotal = count
};
return(Task.FromResult(result));
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
internal DtResult <Vendor> GetList(DTParameters param)
{
var result = new DtResult <Vendor>();
var query = (from v in _dbContext.Vendor
where v.Status.Equals("1")
select new Vendor
{
Id = v.Id,
CreatedDate = v.CreatedDate,
Name = v.Name,
Address = v.Address,
EmailId = v.EmailId,
EmailId2 = v.EmailId2,
ContactNo = v.ContactNo,
ContactPerson = v.ContactPerson
});
result.data = new List <Vendor>();
result.recordsTotal = query.Count();
var searchColumn = (from sr in param.Columns where !string.IsNullOrWhiteSpace(sr.Search.Value) select sr).ToList();
if (searchColumn?.Count() > 0)
{
foreach (var item in searchColumn)
{
if (item.Name.ToLower().Equals("name"))
{
if (!string.IsNullOrWhiteSpace(item.Search.Value))
{
query = query.Where(w => w.Name.Contains(item.Search.Value));
}
}
}
}
if (param.Search != null && !string.IsNullOrEmpty(param.Search.Value))
{
var keyword = param.Search.Value;
query = query.Where(w => w.Name.Contains(keyword) || w.Address.Contains(keyword) || w.EmailId.Contains(keyword) || w.ContactNo.Contains(keyword) || w.ContactPerson.Contains(keyword));
//w.Entity.Value.Contains(keyword) || w.PromoterName.Contains(keyword));
}
if (param.Order != null && param.Order.Length > 0)
{
foreach (var item in param.Order)
{
//if (param.Columns[item.Column].Data.Equals("id"))
// query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.Id) : query.OrderBy(o => o.Id);
if (param.Columns[item.Column].Data.Equals("name"))
{
query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.Name) : query.OrderBy(o => o.Name);
}
else if (param.Columns[item.Column].Data.Equals("address"))
{
query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.Address) : query.OrderBy(o => o.Address);
}
else if (param.Columns[item.Column].Data.Equals("emailId"))
{
query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.EmailId) : query.OrderBy(o => o.EmailId);
}
else if (param.Columns[item.Column].Data.Equals("emailId2"))
{
query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.EmailId2) : query.OrderBy(o => o.EmailId2);
}
else if (param.Columns[item.Column].Data.Equals("contactNo"))
{
query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.ContactNo) : query.OrderBy(o => o.ContactNo);
}
else if (param.Columns[item.Column].Data.Equals("contactPerson"))
{
query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.ContactPerson) : query.OrderBy(o => o.ContactPerson);
}
}
}
result.recordsFiltered = query.Count();
if (param.Length > 0)
{
query = query.Skip(param.Start).Take(param.Length);
}
var entries = query.ToList();
foreach (var e in entries)
{
result.data.Add(e);
}
return(result);
}
internal DtResult <TaskItemViewModel> GetList(DTParameters param)
{
var result = new DtResult <TaskItemViewModel>();
var query = (from s in _dbContext.Bills
join v in _dbContext.Vendor on s.Vendor equals v.Id
join sp in _dbContext.Users on s.SalesPerson equals sp.Id
join r in _dbContext.Reason on s.Rejectreason equals r.Id.ToString() into abc
from r in abc.DefaultIfEmpty()
orderby s.Id descending
select new TaskItemViewModel
{
No = s.No,
Id = s.Id,
Date = s.Date,
SalesPersoName = sp.Name,
VendorName = v.Name,
DeliveryType = s.DeliveryType,
DelieveryPlaceId = s.DelieveryPlaceId,
PaymentTerm = s.PaymentTerm,
PaymentValue = s.PaymentValue,
Purchase = s.Purchase,
Accounts = s.Accounts,
Approver = s.Approver,
Recstatus = s.Recstatus,
Rejectreason = r.Name,
});
result.data = new List <TaskItemViewModel>();
result.recordsTotal = query.Count();
var searchColumn = (from sr in param.Columns where !string.IsNullOrWhiteSpace(sr.Search.Value) select sr).ToList();
if (searchColumn?.Count() > 0)
{
foreach (var item in searchColumn)
{
if (item.Name.ToLower().Equals("start"))
{
if (!string.IsNullOrWhiteSpace(item.Search.Value))
{
query = query.Where(w => w.Date >= DateTime.ParseExact(item.Search.Value.Trim(), "dd-MM-yyyy", CultureInfo.InvariantCulture)).OrderByDescending(o => o.Id);
}
}
if (item.Name.ToLower().Equals("end"))
{
if (!string.IsNullOrWhiteSpace(item.Search.Value))
{
query = query.Where(w => w.Date <= DateTime.ParseExact(item.Search.Value.Trim(), "dd-MM-yyyy", CultureInfo.InvariantCulture).AddHours(23)).OrderByDescending(o => o.Id);
}
}
}
}
if (param.Search != null && !string.IsNullOrEmpty(param.Search.Value))
{
var keyword = param.Search.Value;
query = query.Where(w => w.SalesPersoName.Contains(keyword)); // ||
//w.Entity.Value.Contains(keyword) || w.PromoterName.Contains(keyword));
}
if (param.Order != null && param.Order.Length > 0)
{
foreach (var item in param.Order)
{
//if (param.Columns[item.Column].Data.Equals("no"))
// query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.Id) : query.OrderBy(o => o.Id);
//else if (param.Columns[item.Column].Data.Equals("vendorName"))
// query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.VendorName) : query.OrderBy(o => o.VendorName);
//else if (param.Columns[item.Column].Data.Equals("deliveryType"))
// query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.DeliveryType) : query.OrderBy(o => o.DeliveryType);
//else if (param.Columns[item.Column].Data.Equals("paymentTerm"))
// query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.PaymentTerm) : query.OrderBy(o => o.PaymentTerm);
}
}
result.recordsFiltered = query.Count();
if (param.Length > 0)
{
query = query.Skip(param.Start).Take(param.Length);
}
var entries = query.ToList();
foreach (var e in entries)
{
result.data.Add(e);
}
return(result);
}
internal DtResult <TaskItemViewModel> GetList(DTParameters param)
{
var result = new DtResult <TaskItemViewModel>();
var query = (from s in _dbContext.Bills
group s by(DateTime.ParseExact(s.Date.ToString(), "dd-MM-yyyy", CultureInfo.InvariantCulture)) into g
select new TaskItemViewModel
{
Date = g.Key,
Purchase = g.Sum(x => x.Purchase),
Accounts = g.Sum(x => x.Accounts),
Approver = g.Sum(x => x.Approver),
});
result.data = new List <TaskItemViewModel>();
result.recordsTotal = query.Count();
var searchColumn = (from sr in param.Columns where !string.IsNullOrWhiteSpace(sr.Search.Value) select sr).ToList();
if (searchColumn?.Count() > 0)
{
foreach (var item in searchColumn)
{
if (item.Name.ToLower().Equals("start"))
{
if (!string.IsNullOrWhiteSpace(item.Search.Value))
{
query = query.Where(w => w.Date >= DateTime.ParseExact(item.Search.Value.Trim(), "dd-MM-yyyy", CultureInfo.InvariantCulture)).OrderByDescending(o => o.Id);
}
}
if (item.Name.ToLower().Equals("end"))
{
if (!string.IsNullOrWhiteSpace(item.Search.Value))
{
query = query.Where(w => w.Date <= DateTime.ParseExact(item.Search.Value.Trim(), "dd-MM-yyyy", CultureInfo.InvariantCulture).AddHours(23)).OrderByDescending(o => o.Id);
}
}
}
}
if (param.Search != null && !string.IsNullOrEmpty(param.Search.Value))
{
var keyword = param.Search.Value;
query = query.Where(w => w.SalesPersoName.Contains(keyword)); // ||
//w.Entity.Value.Contains(keyword) || w.PromoterName.Contains(keyword));
}
if (param.Order != null && param.Order.Length > 0)
{
foreach (var item in param.Order)
{
//if (param.Columns[item.Column].Data.Equals("no"))
// query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.Id) : query.OrderBy(o => o.Id);
//else if (param.Columns[item.Column].Data.Equals("vendorName"))
// query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.VendorName) : query.OrderBy(o => o.VendorName);
//else if (param.Columns[item.Column].Data.Equals("deliveryType"))
// query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.DeliveryType) : query.OrderBy(o => o.DeliveryType);
//else if (param.Columns[item.Column].Data.Equals("paymentTerm"))
// query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.PaymentTerm) : query.OrderBy(o => o.PaymentTerm);
}
}
result.recordsFiltered = query.Count();
if (param.Length > 0)
{
query = query.Skip(param.Start).Take(param.Length);
}
var entries = query.ToList();
foreach (var e in entries)
{
result.data.Add(e);
}
return(result);
}
internal DtResult <Item> GetList(DTParameters param)
{
var result = new DtResult <Item>();
var query = (from s in _dbContext.Item
where s.Status.Equals("1")
select new Item
{
Id = s.Id,
Name = s.Name
});
//var query = (from s in _dbContext.Item
// join st in _dbContext.ItemGroup on s.ItemGroupId equals st.Id
// where (s.Status.Equals("1"))
// select new ItemViewModel
// {
// Id = s.Id,
// Name = s.Name,
// ItemGroupName = st.ItemGroupName
// });
result.data = new List <Item>();
result.recordsTotal = query.Count();
var searchColumn = (from sr in param.Columns where !string.IsNullOrWhiteSpace(sr.Search.Value) select sr).ToList();
if (searchColumn?.Count() > 0)
{
foreach (var item in searchColumn)
{
if (item.Name.ToLower().Equals("name"))
{
if (!string.IsNullOrWhiteSpace(item.Search.Value))
{
query = query.Where(w => w.Name.Contains(item.Search.Value));
}
}
}
}
if (param.Search != null && !string.IsNullOrEmpty(param.Search.Value))
{
var keyword = param.Search.Value;
query = query.Where(w => w.Name.Contains(keyword)); // ||
//w.Entity.Value.Contains(keyword) || w.PromoterName.Contains(keyword));
}
if (param.Order != null && param.Order.Length > 0)
{
foreach (var item in param.Order)
{
if (param.Columns[item.Column].Data.Equals("name"))
{
query = item.Dir == DTOrderDir.DESC ? query.OrderByDescending(o => o.Name) : query.OrderBy(o => o.Name);
}
}
}
result.recordsFiltered = query.Count();
if (param.Length > 0)
{
query = query.Skip(param.Start).Take(param.Length);
}
var entries = query.ToList();
foreach (var e in entries)
{
result.data.Add(e);
}
return(result);
}