public CustomerListViewModel GetCustomerAndLoanList(GetCustomerListQuery request)
{
var query = (from l in _context.Loans.SingleOrDefault(x => x.AccountNumber == request.AccountNumber).AccountNumber
join c in _context.Customers
on l equals c.Id
join a in _context.Accounts
on l equals a.AccountId
group l by new
{
l,
c.Givenname,
c.Id,
c.Surname,
c.NationalId,
c.CustAccNumber,
c.Birthday,
c.UploadDocument,
c.ImageThumbnailUrl,
a.Balance,
c.City,
c.Country,
c.Streetaddress
} into g
select new
{
Surname = g.Key.Surname,
LoanId = g.Key.l,
Givenname = g.Key.Givenname,
Id = g.Key.Id,
NationalId = g.Key.NationalId,
CustAccNumber = g.Key.CustAccNumber,
Birthday = g.Key.Birthday,
UploadDocument = g.Key.UploadDocument,
ImageThumbnailUrl = g.Key.ImageThumbnailUrl,
Balance = g.Key.Balance,
City = g.Key.City,
Country = g.Key.Country,
Streetaddress = g.Key.Streetaddress
}).ToList();
//// var trans = _context.Transactions.Where(d => d.AccountNumber == request.AccountNumber).ToListAsync();
Account acct = _context.Accounts.SingleOrDefault(d => d.AccountNumber == request.AccountNumber);
return(new CustomerListViewModel
{
Customers = query.Select(c => new CustomerListDto
{
CustomerId = c.Id,
Surname = c.Surname,
GivenName = c.Givenname,
NationalId = c.NationalId,
AccountNumber = c.CustAccNumber,
Birthdate = c.Birthday.Value,
ScannThumbnailUrl = c.UploadDocument,
CustImageThumbnailUrl = c.ImageThumbnailUrl,
Balance = acct.Balance.Value,
LoanId = c.LoanId,
Address = new CustomerAddress
{
City = c.City,
StreetAdress = c.Streetaddress,
//ZipCode = c.Zipcode,
Country = c.Country
}
})
//Total = totalCount,
//NumberOfPages = totalCount / request.Limit + 1,
//HasMorePages = request.Offset + request.Limit < totalCount,
//Name = request.Name,
//AccountNumber = request.AccountNumber,
//CurrentPage = request.CurrentPage,
//HasPreviousPages = request.Offset > 0
});
// return result;
}
public async Task <CustomerListViewModel> GetCustomerList(GetCustomerListQuery request)
{
var query = _context.Customers.AsQueryable().AsNoTracking();
var nameExists = !string.IsNullOrWhiteSpace(request.Name);
var accountNoExist = !string.IsNullOrWhiteSpace(request.AccountNumber);
if (nameExists && !accountNoExist)
{
var names = request.Name.Split(" ");
var firstName = names[0];
if (names.Length > 1)
{
var surName = names[1];
query = query
.Where(c => c.Givenname.StartsWith(firstName, StringComparison.OrdinalIgnoreCase) && c.Surname.StartsWith(surName))
.OrderByDescending(c => c.Givenname.Equals(firstName, StringComparison.OrdinalIgnoreCase))
.ThenByDescending(c => c.Surname.Equals(surName, StringComparison.OrdinalIgnoreCase))
.AsNoTracking();
}
else
{
query = query
.Where(c => c.Givenname.StartsWith(firstName, StringComparison.OrdinalIgnoreCase) ||
c.Surname.StartsWith(firstName, StringComparison.OrdinalIgnoreCase))
.OrderByDescending(c => c.Givenname.Equals(firstName, StringComparison.OrdinalIgnoreCase))
.ThenByDescending(c => c.Givenname.StartsWith(firstName, StringComparison.OrdinalIgnoreCase))
.ThenByDescending(c => c.Surname.StartsWith(firstName)).AsNoTracking();
}
}
else if (accountNoExist && !nameExists)
{
query = query.Where(c => c.CustAccNumber
.StartsWith(request.AccountNumber, StringComparison.OrdinalIgnoreCase))
.OrderByDescending(c => c.CustAccNumber.Equals(request.AccountNumber, StringComparison.OrdinalIgnoreCase))
.ThenByDescending(c => c.CustAccNumber.StartsWith(request.AccountNumber, StringComparison.OrdinalIgnoreCase))
.AsNoTracking();
}
else if (accountNoExist && nameExists)
{
var names = request.Name.Split(" ");
var firstName = names[0];
if (names.Length > 1)
{
var surName = names[1];
query = query.Where(c =>
(c.Givenname.StartsWith(firstName, StringComparison.OrdinalIgnoreCase) &&
c.Surname.StartsWith(surName, StringComparison.OrdinalIgnoreCase)) &&
c.CustAccNumber.StartsWith(request.AccountNumber, StringComparison.OrdinalIgnoreCase))
.OrderByDescending(c => c.Givenname.StartsWith(request.Name, StringComparison.OrdinalIgnoreCase))
.ThenByDescending(c => c.Surname.StartsWith(request.Name, StringComparison.OrdinalIgnoreCase))
.ThenByDescending(c => c.CustAccNumber.StartsWith(request.AccountNumber, StringComparison.OrdinalIgnoreCase)).AsNoTracking();
}
else
{
query = query.Where(c =>
(c.Givenname.StartsWith(request.Name, StringComparison.OrdinalIgnoreCase) ||
c.Surname.StartsWith(request.Name, StringComparison.OrdinalIgnoreCase)) &&
c.CustAccNumber.StartsWith(request.AccountNumber, StringComparison.OrdinalIgnoreCase))
.OrderByDescending(c => c.Givenname.StartsWith(request.Name, StringComparison.OrdinalIgnoreCase))
.ThenByDescending(c => c.Surname.StartsWith(request.Name, StringComparison.OrdinalIgnoreCase))
.ThenByDescending(c => c.CustAccNumber.StartsWith(request.AccountNumber, StringComparison.OrdinalIgnoreCase)).AsNoTracking();
}
}
else
{
query = query.OrderBy(c => c.Id).AsNoTracking();
}
// var trans = _context.Transactions.Where(d => d.AccountNumber == request.AccountNumber).ToListAsync();
Account acct = _context.Accounts.SingleOrDefault(d => d.AccountNumber == request.AccountNumber);
var totalCount = query.Count();
return(new CustomerListViewModel
{
Customers = await query.Skip(request.Offset).Take(request.Limit).Select(c => new CustomerListDto
{
CustomerId = c.Id,
Surname = c.Surname,
GivenName = c.Givenname,
NationalId = c.NationalId,
AccountNumber = c.CustAccNumber,
Birthdate = c.Birthday.Value,
ScannThumbnailUrl = c.UploadDocument,
CustImageThumbnailUrl = c.ImageThumbnailUrl,
Balance = acct.Balance.Value,
FullName = c.Surname + "" + c.Givenname + " - " + c.CustAccNumber,
Address = new CustomerAddress
{
City = c.City,
StreetAdress = c.Streetaddress,
//ZipCode = c.Zipcode,
Country = c.Country
}
}).AsNoTracking().ToArrayAsync(),
Total = totalCount,
NumberOfPages = totalCount / request.Limit + 1,
HasMorePages = request.Offset + request.Limit < totalCount,
Name = request.Name,
AccountNumber = request.AccountNumber,
CurrentPage = request.CurrentPage,
HasPreviousPages = request.Offset > 0
});
}
