public Task <FilterResultModel <AccountViewModel> > Handle(GetAccountsListQuery request, CancellationToken cancellationToken)
{
var sortBy = request.SortBy.Trim() != "" ? request.SortBy : "AccountName";
var sortDirection = (request.SortDirection.ToUpper() == "DESCENDING") ? true : false;
FilterResultModel <AccountViewModel> result = new FilterResultModel <AccountViewModel> ();
var accountList = _database.Accounts
.Where(a => a.Year == request.Year)
.Select(AccountViewModel.Projection)
.Select(DynamicQueryHelper.GenerateSelectedColumns <AccountViewModel> (request.SelectedColumns))
.AsQueryable();
if (request.Filter.Count() > 0)
{
accountList = accountList
.Where(DynamicQueryHelper
.BuildWhere <AccountViewModel> (request.Filter)).AsQueryable();
}
result.Count = accountList.Count();
var PageSize = (request.PageSize == 0) ? result.Count : request.PageSize;
var PageNumber = (request.PageSize == 0) ? 1 : request.PageNumber;
result.Items = accountList.OrderBy(sortBy, sortDirection)
.Skip(PageNumber - 1)
.Take(PageSize)
.ToList();
return(Task.FromResult <FilterResultModel <AccountViewModel> > (result));
}
public Task <FilterResultModel <JornalEntryListView> > Handle(GetJornalEntryListQuery request, CancellationToken cancellationToken)
{
var sortBy = request.SortBy.Trim() != "" ? request.SortBy : "VoucherId";
var sortDirection = (request.SortDirection.ToUpper() == "DESCENDING") ? true : false;
FilterResultModel <JornalEntryListView> result = new FilterResultModel <JornalEntryListView> ();
CustomDateConverter converter = new CustomDateConverter();
var start = Convert.ToInt32(request.Year) - 1;
var start_date = converter.EthiopicToGregorian($"1/11/{start}");
var end_date = converter.EthiopicToGregorian($"30/10/{request.Year}");
var jornalEntries = _database.Ledger
.Where(l => l.Date >= start_date && l.Date <= end_date)
.Select(JornalEntryListView.Projection)
.Select(DynamicQueryHelper.GenerateSelectedColumns <JornalEntryListView> (request.SelectedColumns))
.AsQueryable();
if (request.Filter.Count() > 0)
{
jornalEntries = jornalEntries
.Where(DynamicQueryHelper
.BuildWhere <JornalEntryListView> (request.Filter)).AsQueryable();
}
result.Count = jornalEntries.Count();
result.Items = jornalEntries.OrderBy(sortBy, sortDirection).Skip(request.PageNumber)
.Take(request.PageSize)
.ToList();
return(Task.FromResult <FilterResultModel <JornalEntryListView> > (result));
}
public Task <FilterResultModel <PartnersRentHistoryModel> > Handle(GetPartnersRentHistoryQuery request, CancellationToken cancellationToken)
{
var sortBy = request.SortBy.Trim() != "" ? request.SortBy : "PartnerName";
var sortDirection = (request.SortDirection.ToUpper() == "DESCENDING") ? true : false;
FilterResultModel <PartnersRentHistoryModel> result = new FilterResultModel <PartnersRentHistoryModel> ();
var history = _database.Rent
.Where(v => v.Vehicle.Owner != null)
.Select(PartnersRentHistoryModel.Projection)
.Select(DynamicQueryHelper.GenerateSelectedColumns <PartnersRentHistoryModel> (request.SelectedColumns))
.AsQueryable();
if (request.Filter.Count() > 0)
{
history = history
.Where(DynamicQueryHelper
.BuildWhere <PartnersRentHistoryModel> (request.Filter));
}
result.Count = history.Count();
var PageSize = (request.PageSize == 0) ? result.Count : request.PageSize;
var PageNumber = (request.PageSize == 0) ? 1 : request.PageNumber;
result.Items = history.OrderBy(sortBy, sortDirection)
.Skip(PageNumber - 1)
.Take(PageSize)
.ToList();
return(Task.FromResult <FilterResultModel <PartnersRentHistoryModel> > (result));
}
public Task <FilterResultModel <VendorView> > Handle(GetVendorsListQuery request, CancellationToken cancellationToken)
{
var sortBy = request.SortBy.Trim() != "" ? request.SortBy : "Name";
var sortDirection = (request.SortDirection.ToUpper() == "DESCENDING") ? true : false;
FilterResultModel <VendorView> result = new FilterResultModel <VendorView> ();
var vendor = _database.Vendor
.Select(VendorView.Projection)
.Select(DynamicQueryHelper.GenerateSelectedColumns <VendorView> (request.SelectedColumns))
.AsQueryable();
if (request.Filter.Count() > 0)
{
vendor = vendor
.Where(DynamicQueryHelper
.BuildWhere <VendorView> (request.Filter)).AsQueryable();
}
result.Count = vendor.Count();
var PageSize = (request.PageSize == 0) ? result.Count : request.PageSize;
var PageNumber = (request.PageSize == 0) ? 1 : request.PageNumber;
result.Items = vendor.OrderBy(sortBy, sortDirection)
.Skip(PageNumber - 1)
.Take(PageSize)
.ToList();
return(Task.FromResult <FilterResultModel <VendorView> > (result));
}
public Task <FilterResultModel <PurchaseOrderPaymentView> > Handle(GetPurchaseOrderPyamentListQuery request, CancellationToken cancellationToken)
{
var sortBy = request.SortBy.Trim() != "" ? request.SortBy : "DateAdded";
var sortDirection = (request.SortDirection.ToUpper() == "DESCENDING") ? true : false;
FilterResultModel <PurchaseOrderPaymentView> result = new FilterResultModel <PurchaseOrderPaymentView> ();
var payment = _database.InvoicePayments
.Select(PurchaseOrderPaymentView.Projection)
.Select(DynamicQueryHelper.GenerateSelectedColumns <PurchaseOrderPaymentView> (request.SelectedColumns))
.AsQueryable();
if (request.Filter.Count() > 0)
{
payment = payment
.Where(DynamicQueryHelper
.BuildWhere <PurchaseOrderPaymentView> (request.Filter)).AsQueryable();
}
result.Count = payment.Count();
var PageSize = (request.PageSize == 0) ? result.Count : request.PageSize;
var PageNumber = (request.PageSize == 0) ? 1 : request.PageNumber;
result.Items = payment.OrderBy(sortBy, sortDirection)
.Skip(PageNumber - 1)
.Take(PageSize)
.ToList();
return(Task.FromResult <FilterResultModel <PurchaseOrderPaymentView> > (result));
}
public Task <FilterResultModel <UserViewModel> > Handle(GetUsersListViewQuery request, CancellationToken cancellationToken)
{
var sortBy = request.SortBy.Trim() != "" ? request.SortBy : "UserName";
var sortDirection = (request.SortDirection.ToUpper() == "DESCENDING") ? true : false;
FilterResultModel <UserViewModel> result = new FilterResultModel <UserViewModel> ();
var vehicle = _userManager.Users
.Select(UserViewModel.Projection)
.Select(DynamicQueryHelper.GenerateSelectedColumns <UserViewModel> (request.SelectedColumns))
.AsQueryable();
if (request.Filter.Count() > 0)
{
vehicle = vehicle
.Where(DynamicQueryHelper
.BuildWhere <UserViewModel> (request.Filter)).AsQueryable();
}
result.Count = vehicle.Count();
var PageSize = (request.PageSize == 0) ? result.Count : request.PageSize;
var PageNumber = (request.PageSize == 0) ? 1 : request.PageNumber;
result.Items = vehicle.OrderBy(sortBy, sortDirection)
.Skip(PageNumber - 1)
.Take(PageSize)
.ToList();
return(Task.FromResult <FilterResultModel <UserViewModel> > (result));
}
public Task <FilterResultModel <JornalEntryListView> > Handle(GetUnpostedLedgerEntriesQuery request, CancellationToken cancellationToken)
{
var sortBy = request.SortBy.Trim() != "" ? request.SortBy : "VoucherId";
var sortDirection = (request.SortDirection.ToUpper() == "DESCENDING") ? true : false;
var entry = _database.Ledger
.Where(l => l.IsPosted == 0)
.Select(JornalEntryListView.Projection)
.Select(DynamicQueryHelper.GenerateSelectedColumns <JornalEntryListView> (request.SelectedColumns))
.AsQueryable();
FilterResultModel <JornalEntryListView> result = new FilterResultModel <JornalEntryListView> ();
if (request.Filter.Count() > 0)
{
entry = entry
.Where(DynamicQueryHelper
.BuildWhere <JornalEntryListView> (request.Filter)).AsQueryable();
}
result.Count = entry.Count();
var PageSize = request.PageSize;
var PageNumber = (request.PageSize == 0) ? 1 : request.PageNumber;
result.Items = entry.OrderBy(sortBy, sortDirection).Skip(request.PageNumber)
.Take(PageSize)
.ToList();
return(Task.FromResult <FilterResultModel <JornalEntryListView> > (result));
}
public async Task <FilterResultModel <CustomerViewModel> > Handle(GetCustomersListQuery request, CancellationToken cancellationToken)
{
var sortBy = request.SortBy.Trim() != "" ? request.SortBy : "CustomerName";
var sortDirection = (request.SortDirection.ToUpper() == "DESCENDING") ? true : false;
FilterResultModel <CustomerViewModel> result = new FilterResultModel <CustomerViewModel> ();
var customer = _database.Customer
.Select(CustomerViewModel.Projection)
.Select(DynamicQueryHelper.GenerateSelectedColumns <CustomerViewModel> (request.SelectedColumns))
.AsQueryable();
if (request.Filter.Count() > 0)
{
customer = customer
.Where(DynamicQueryHelper
.BuildWhere <CustomerViewModel> (request.Filter)).AsQueryable();
}
result.Count = customer.Count();
var PageSize = (request.PageSize == 0) ? result.Count : request.PageSize;
var PageNumber = (request.PageSize == 0) ? 1 : request.PageNumber;
result.Items = customer.OrderBy(sortBy, sortDirection)
.Skip(PageNumber - 1)
.Take(PageSize)
.ToList();
return(result);
}
public Task <FilterResultModel <StockBatchViewModel> > Handle(GetStockBatchListQuery request, CancellationToken cancellationToken)
{
var sortBy = request.SortBy.Trim() != "" ? request.SortBy : "DateAdded";
var sortDirection = (request.SortDirection.ToUpper() == "DESCENDING") ? false : true;
FilterResultModel <StockBatchViewModel> result = new FilterResultModel <StockBatchViewModel> ();
var batch = _database.StockBatchStorage
.Select(StockBatchViewModel.Projection)
.Select(DynamicQueryHelper.GenerateSelectedColumns <StockBatchViewModel> (request.SelectedColumns))
.AsQueryable();
if (request.Filter.Count() > 0)
{
batch = batch
.Where(DynamicQueryHelper
.BuildWhere <StockBatchViewModel> (request.Filter)).AsQueryable();
}
result.Count = batch.Count();
var PageSize = (request.PageSize == 0) ? result.Count : request.PageSize;
var PageNumber = (request.PageSize == 0) ? 1 : request.PageNumber;
result.Items = batch.OrderBy(sortBy, sortDirection)
.Skip(PageNumber - 1)
.Take(PageSize)
.ToList();
return(Task.FromResult <FilterResultModel <StockBatchViewModel> > (result));
}
public async Task <FilterResultModel <PartnerPaymentListModel> > Handle(GetPartnerPaymentsListQuery request, CancellationToken cancellationToken)
{
var sortBy = request.SortBy.Trim() != "" ? request.SortBy : "Date";
var sortDirection = (request.SortDirection.ToUpper() == "DESCENDING") ? true : false;
FilterResultModel <PartnerPaymentListModel> result = new FilterResultModel <PartnerPaymentListModel> ();
var payments = _database.RentPayment
.Where(r => r.Partner != null)
.Select(PartnerPaymentListModel.Projection)
.Select(DynamicQueryHelper.GenerateSelectedColumns <PartnerPaymentListModel> (request.SelectedColumns))
.AsQueryable();
if (request.Filter.Count() > 0)
{
payments = payments
.Where(DynamicQueryHelper
.BuildWhere <PartnerPaymentListModel> (request.Filter)).AsQueryable();
}
result.Count = payments.Count();
var PageSize = (request.PageSize == 0) ? result.Count : request.PageSize;
var PageNumber = (request.PageSize == 0) ? 1 : request.PageNumber;
result.Items = payments.OrderBy(sortBy, sortDirection)
.Skip(PageNumber - 1)
.Take(PageSize)
.ToList();
return(result);
}
public Task <FilterResultModel <CustomerOrderInvoiceList> > Handle(GetCustomerOrderInvoiceListQuery request, CancellationToken cancellationToken)
{
var sortBy = request.SortBy.Trim() != "" ? request.SortBy : "CustomerName";
var sortDirection = (request.SortDirection.ToUpper() == "DESCENDING") ? false : true;
FilterResultModel <CustomerOrderInvoiceList> result = new FilterResultModel <CustomerOrderInvoiceList> ();
var invoice = _database.Invoice
.Select(CustomerOrderInvoiceList.Projection)
.Select(DynamicQueryHelper.GenerateSelectedColumns <CustomerOrderInvoiceList> (request.SelectedColumns))
.AsQueryable();
if (request.Filter.Count() > 0)
{
invoice = invoice
.Where(DynamicQueryHelper
.BuildWhere <CustomerOrderInvoiceList> (request.Filter)).AsQueryable();
}
result.Count = invoice.Count();
var PageSize = (request.PageSize == 0) ? result.Count : request.PageSize;
var PageNumber = (request.PageSize == 0) ? 1 : request.PageNumber;
result.Items = invoice.OrderBy(sortBy, sortDirection)
.Skip(PageNumber - 1)
.Take(PageSize)
.ToList();
return(Task.FromResult <FilterResultModel <CustomerOrderInvoiceList> > (result));
}
public Task <FilterResultModel <SystemLookupViewModel> > Handle(GetSystemLookupListQuery request, CancellationToken cancellationToken)
{
var sortBy = request.SortBy.Trim() != "" ? request.SortBy : "Type";
var sortDirection = (request.SortDirection.ToUpper() == "DESCENDING") ? true : false;
FilterResultModel <SystemLookupViewModel> result = new FilterResultModel <SystemLookupViewModel> ();
var lookup = _database.SystemLookup
.Where(l => l.Type.ToLower() != "system_lookup")
.Select(SystemLookupViewModel.Projection)
.Select(DynamicQueryHelper.GenerateSelectedColumns <SystemLookupViewModel> (request.SelectedColumns))
.AsQueryable();
if (request.Filter.Count() > 0)
{
lookup = lookup
.Where(DynamicQueryHelper
.BuildWhere <SystemLookupViewModel> (request.Filter)).AsQueryable();
}
result.Count = lookup.Count();
result.Items = lookup.OrderBy(sortBy, sortDirection).Skip(request.PageNumber)
.Take(request.PageSize)
.ToList();
return(Task.FromResult <FilterResultModel <SystemLookupViewModel> > (result));
}
public Task <FilterResultModel <PurchaseOrderView> > Handle(GetShippedPurchaseOrdersListQuery request, CancellationToken cancellationToken)
{
var sortBy = request.SortBy.Trim() != "" ? request.SortBy : "DateAdded";
var sortDirection = (request.SortDirection.ToUpper() == "DESCENDING") ? true : false;
FilterResultModel <PurchaseOrderView> result = new FilterResultModel <PurchaseOrderView> ();
var purchaseTerm = _database.PurchaseOrder
.Where(p => p.Status.ToUpper() == "SHIPPED")
.Include(p => p.PurchaseOrderQuotation)
.Include(p => p.StockBatch)
.Select(PurchaseOrderView.Projection)
.Select(DynamicQueryHelper.GenerateSelectedColumns <PurchaseOrderView> (request.SelectedColumns))
.AsQueryable();
if (request.Filter.Count() > 0)
{
purchaseTerm = purchaseTerm
.Where(DynamicQueryHelper
.BuildWhere <PurchaseOrderView> (request.Filter)).AsQueryable();
}
result.Count = purchaseTerm.Count();
var PageSize = (request.PageSize == 0) ? result.Count : request.PageSize;
var PageNumber = (request.PageSize == 0) ? 1 : request.PageNumber;
result.Items = purchaseTerm.OrderBy(sortBy, sortDirection)
.Skip(PageNumber - 1)
.Take(PageSize)
.ToList();
return(Task.FromResult <FilterResultModel <PurchaseOrderView> > (result));
}
public Task <FilterResultModel <AccountCategoryView> > Handle(GetAccountCategoryListQuery request, CancellationToken cancellationToken)
{
var sortBy = request.SortBy.Trim() != "" ? request.SortBy : "CategoryName";
var sortDirection = (request.SortDirection.ToUpper() == "DESCENDING") ? true : false;
FilterResultModel <AccountCategoryView> result = new FilterResultModel <AccountCategoryView> ();
var categoryList = _database.AccountCatagory
.Select(AccountCategoryView.Projection)
.Select(DynamicQueryHelper.GenerateSelectedColumns <AccountCategoryView> (request.SelectedColumns))
.AsQueryable();
if (request.Filter.Count() > 0)
{
categoryList = categoryList
.Where(DynamicQueryHelper
.BuildWhere <AccountCategoryView> (request.Filter)).AsQueryable();
}
result.Count = categoryList.Count();
result.Items = categoryList.OrderBy(sortBy, sortDirection).Skip(request.PageNumber)
.Take(request.PageSize)
.ToList();
return(Task.FromResult <FilterResultModel <AccountCategoryView> > (result));
}
public Task <FilterResultModel <ProductGroupViewModel> > Handle(GetProductGroupListQuery request, CancellationToken cancellationToken)
{
var sortBy = request.SortBy.Trim() != "" ? request.SortBy : "GroupName";
var sortDirection = (request.SortDirection.ToUpper() == "DESCENDING") ? false : true;
FilterResultModel <ProductGroupViewModel> result = new FilterResultModel <ProductGroupViewModel> ();
var productGroup = _database.ProductGroup
.Select(ProductGroupViewModel.Projection)
.Select(DynamicQueryHelper.GenerateSelectedColumns <ProductGroupViewModel> (request.SelectedColumns))
.AsQueryable();
if (request.Filter.Count() > 0)
{
productGroup = productGroup
.Where(DynamicQueryHelper
.BuildWhere <ProductGroupViewModel> (request.Filter)).AsQueryable();
}
result.Count = productGroup.Count();
var PageSize = (request.PageSize == 0) ? result.Count : request.PageSize;
var PageNumber = (request.PageSize == 0) ? 1 : request.PageNumber;
result.Items = productGroup.OrderBy(sortBy, sortDirection)
.Skip(PageNumber - 1)
.Take(PageSize)
.ToList();
return(Task.FromResult <FilterResultModel <ProductGroupViewModel> > (result));
}
public Task <FilterResultModel <LedgerChecklistModel> > Handle(GetLedgerCheckListQuery request, CancellationToken cancellationToken)
{
var sortBy = request.SortBy.Trim() != "" ? request.SortBy : "Date";
var sortDirection = (request.SortDirection.ToUpper() != "DESCENDING") ? true : false;
FilterResultModel <LedgerChecklistModel> result = new FilterResultModel <LedgerChecklistModel> ();
CustomDateConverter converter = new CustomDateConverter();
var start = Convert.ToInt32(request.Year) - 1;
var start_date = converter.EthiopicToGregorian($"1/11/{start}");
var end_date = converter.EthiopicToGregorian($"30/10/{request.Year}");
var list = _database.Ledger
.Where(x => x.Date >= start_date && x.Date <= end_date);
if (request.FromVoucherId.Trim() != "")
{
list = list.Where(l => l.VoucherId.CompareTo(request.FromVoucherId) > 0 || l.VoucherId.CompareTo(request.FromVoucherId) == 0);
}
if (request.ToVoucherId.Trim() != "")
{
list = list.Where(l => l.VoucherId.CompareTo(request.ToVoucherId) < 0 || l.VoucherId.CompareTo(request.ToVoucherId) == 0);
}
if (request.StartDate != null)
{
list = list.Where(a => a.LedgerEntry
.Any(e => e.Ledger.Date > request.StartDate && e.Ledger.Date < request.EndDate));
}
var filtered = list.Select(LedgerChecklistModel.Projection)
.Select(DynamicQueryHelper.GenerateSelectedColumns <LedgerChecklistModel> (request.SelectedColumns))
.AsQueryable();
if (request.Filter.Count() > 0)
{
filtered = filtered
.Where(DynamicQueryHelper
.BuildWhere <LedgerChecklistModel> (request.Filter));
}
var PageSize = (request.PageSize == 0) ? result.Count : request.PageSize;
var PageNumber = (request.PageSize == 0) ? 1 : request.PageNumber;
result.Count = filtered.Count();
result.Items = filtered.OrderBy(sortBy, sortDirection)
.Skip(PageNumber - 1)
.Take(PageSize)
.ToList();
return(Task.FromResult <FilterResultModel <LedgerChecklistModel> > (result));
}
public Task <FilterResultModel <AccountScheduleModel> > Handle(GetAccountScheduleQuery request, CancellationToken cancellationToken)
{
var sortBy = request.SortBy.Trim() != "" ? request.SortBy : "ControlAccountId";
var sortDirection = (request.SortDirection.ToUpper() == "DESCENDING") ? true : false;
FilterResultModel <AccountScheduleModel> finalResult = new FilterResultModel <AccountScheduleModel> ();
var PageSize = request.PageSize;
var PageNumber = (request.PageSize == 0) ? 1 : request.PageNumber;
var result = _database.Account.Where(a => a.ParentAccountNavigation != null).Where(y => y.Year == request.Year)
.Select(AccountScheduleModel.Projection);
if (request.ControlAccountId != null && request.ControlAccountId.Trim() != "")
{
result = result.Where(d => d.ControlAccountId == request.ControlAccountId);
}
if (request.StartDate != null)
{
result = result.Where(d => d.Date >= dateConverter.EthiopicToGregorian(request.StartDate));
}
if (request.EndDate != null)
{
result = result.Where(d => d.Date <= dateConverter.EthiopicToGregorian(request.EndDate));
}
var filtered = result
.Select(DynamicQueryHelper.GenerateSelectedColumns <AccountScheduleModel> (request.SelectedColumns))
.AsQueryable();
if (request.Filter.Count() > 0)
{
filtered = filtered
.Where(DynamicQueryHelper
.BuildWhere <AccountScheduleModel> (request.Filter)).AsQueryable();
}
finalResult.Items = filtered.OrderBy(sortBy, sortDirection)
.Skip(PageNumber - 1)
.Take(PageSize)
.ToList();
finalResult.Count = filtered.Count();
return(Task.FromResult <FilterResultModel <AccountScheduleModel> > (finalResult));
}
public Task <FilterResultModel <RemainingPartnerPaymentsModel> > Handle(GetRemainingPartnerPaymentsQuery request, CancellationToken cancellationToken)
{
var sortBy = request.SortBy.Trim() != "" ? request.SortBy : "PartnerName";
var sortDirection = (request.SortDirection.ToUpper() == "DESCENDING") ? true : false;
FilterResultModel <RemainingPartnerPaymentsModel> result = new FilterResultModel <RemainingPartnerPaymentsModel> ();
var remaining = _database.Rent
.Where(r => r.Vehicle.Owner != null)
.Select(RemainingPartnerPaymentsModel.Projection)
.Select(DynamicQueryHelper.GenerateSelectedColumns <RemainingPartnerPaymentsModel> (request.SelectedColumns))
.GroupBy(e => new { e.PartnerName, e.PartnerId })
.Select(t => new RemainingPartnerPaymentsModel()
{
PartnerName = t.Key.PartnerName,
PartnerId = t.Key.PartnerId,
Amount = t.Sum(o => o.Amount),
PaidAmount = t.Sum(o => o.PaidAmount)
})
.Where(r => r.RemainingAmount > 0).AsQueryable();
if (request.Filter.Count() > 0)
{
remaining = remaining
.Where(DynamicQueryHelper
.BuildWhere <RemainingPartnerPaymentsModel> (request.Filter)).AsQueryable();
}
result.Count = remaining.Count();
var PageSize = (request.PageSize == 0) ? result.Count : request.PageSize;
var PageNumber = (request.PageSize == 0) ? 1 : request.PageNumber;
result.Items = remaining.OrderBy(sortBy, sortDirection)
.Skip(PageNumber - 1)
.Take(PageSize)
.ToList();
return(Task.FromResult <FilterResultModel <RemainingPartnerPaymentsModel> > (result));
}
public Task <FilterResultModel <CriticalItemsView> > Handle(GetCriticalItemsQuery request, CancellationToken cancellationToken)
{
var sortBy = request.SortBy.Trim() != "" ? request.SortBy : "ItemName";
var sortDirection = (request.SortDirection.ToUpper() == "DESCENDING") ? false : true;
FilterResultModel <CriticalItemsView> result = new FilterResultModel <CriticalItemsView> ();
var itemList = _database.Item
.Include(i => i.PrimaryUom)
.GroupJoin(_database.StockBatchStorage, item => item.Id,
lot => lot.Batch.ItemId,
(product, slot) => new ItemLotJoin()
{
Item = product,
Lot = slot
}).GroupBy(i => i.Item)
.Select(CriticalItemsView.Projection)
.Select(DynamicQueryHelper.GenerateSelectedColumns <CriticalItemsView> (request.SelectedColumns))
.AsQueryable();
if (request.Filter.Count() > 0)
{
itemList = itemList
.Where(DynamicQueryHelper
.BuildWhere <CriticalItemsView> (request.Filter)).AsQueryable();
}
result.Count = itemList.Count();
var PageSize = (request.PageSize == 0) ? result.Count : request.PageSize;
var PageNumber = (request.PageSize == 0) ? 1 : request.PageNumber;
result.Items = itemList.OrderBy(sortBy, sortDirection)
.Skip(PageNumber - 1)
.Take(PageSize)
.ToList();
return(Task.FromResult <FilterResultModel <CriticalItemsView> > (result));
}
public Task <FilterResultModel <TrialBalanceDetailModel> > Handle(GetDetailedTrialBalanceQuery request, CancellationToken cancellationToken)
{
var sortBy = request.SortBy.Trim() != "" ? request.SortBy : "AccountId";
var sortDirection = (request.SortDirection.ToUpper() == "DESCENDING") ? true : false;
FilterResultModel <TrialBalanceDetailModel> finalResult = new FilterResultModel <TrialBalanceDetailModel> ();
var PageSize = request.PageSize;
var PageNumber = (request.PageSize == 0) ? 1 : request.PageNumber;
var fromLedger = _database.Account.Where(a => a.ParentAccountNavigation != null && a.Year == request.Year)
.Join(_database.LedgerEntry, a => a.Id, d => d.AccountId, (a, d) => new SampleModel()
{
Parent = a.ParentAccountNavigation,
Account = a,
Type = a.Catagory.AccountType.TypeOfNavigation.Type,
Id = a.Id,
AccountName = a.AccountName,
AccountId = a.ParentAccountNavigation.AccountId,
Credit = d.Credit,
Debit = d.Debit,
Date = d.Ledger.Date
});
if (request.StartDate != null)
{
fromLedger = fromLedger.Where(d => d.Date >= dateConverter.EthiopicToGregorian(request.StartDate));
}
if (request.EndDate != null)
{
fromLedger = fromLedger.Where(d => d.Date <= dateConverter.EthiopicToGregorian(request.EndDate));
}
if (request.Filter.Count() > 0)
{
fromLedger = fromLedger
.Where(DynamicQueryHelper
.BuildWhere <SampleModel> (request.Filter)).AsQueryable();
}
var grouped = fromLedger.OrderBy(sortBy, sortDirection)
.GroupBy(a => new { a.Id, a.Account, a.Parent })
.Select(d => new {
AccountName = d.Key.Account.AccountName,
AccountId = d.Key.Account.AccountId,
Type = d.Select(e => e.Type).First(),
ControlAccountId = d.Key.Parent.AccountId,
ControlAccountName = d.Key.Parent.AccountName,
CreditSum = d.Sum(c => (decimal?)c.Credit),
DebitSum = d.Sum(c => (decimal?)c.Debit)
}).GroupBy(s => new { s.ControlAccountId, s.ControlAccountName })
.Skip(PageNumber)
.Take(PageSize)
.ToList();
IList <TrialBalanceDetailModel> detail = new List <TrialBalanceDetailModel> ();
decimal?difference = 0;
foreach (var parent in grouped)
{
TrialBalanceDetailModel temp = new TrialBalanceDetailModel()
{
AccountId = parent.Key.ControlAccountId,
AccountName = parent.Key.ControlAccountName,
ControlAccountId = parent.Key.ControlAccountId
};
foreach (var sub in parent.OrderBy(s => s.AccountId))
{
var det = new TrialBalanceDetailListModel()
{
AccountName = sub.AccountName,
ControlAccountId = parent.Key.ControlAccountId,
Credit = sub.CreditSum,
Debit = sub.DebitSum,
AccountId = sub.AccountId
};
if (sub.Type.ToUpper() == "LIABILITY" || sub.Type.ToUpper() == "CAPITAL" || sub.Type.ToUpper() == "REVENUE")
{
difference = sub.CreditSum - sub.DebitSum;
if (difference < 0)
{
det.Debit = difference * -1;
det.Credit = null;
}
else
{
det.Credit = difference;
det.Debit = null;
}
}
else
{
difference = sub.DebitSum - sub.CreditSum;
if (difference < 0)
{
det.Credit = difference * -1;
det.Debit = null;
}
else
{
det.Debit = difference;
det.Credit = null;
}
}
((IList <TrialBalanceDetailListModel>)temp.Entries).Add(det);
}
detail.Add(temp);
}
finalResult.Items = detail;
finalResult.Count = fromLedger.Count();
return(Task.FromResult(finalResult));
}
public Task <FilterResultModel <TrialBalanceModel> > Handle(GetConsolidatedTrialBalanceQuery request, CancellationToken cancellationToken)
{
var sortBy = request.SortBy.Trim() != "" ? request.SortBy : "AccountName";
var sortDirection = (request.SortDirection.ToUpper() == "DESCENDING") ? true : false;
FilterResultModel <TrialBalanceModel> result = new FilterResultModel <TrialBalanceModel> ();
var entry = _database.LedgerEntry.Join(_database.Account.Where(a => a.Year == request.Year && a.ParentAccountNavigation != null), l => l.AccountId, a => a.Id, (l, a) => new {
AccountId = a.ParentAccountNavigation.AccountId,
AccountName = a.ParentAccountNavigation.AccountName,
Type = a.Catagory.AccountType.TypeOfNavigation.Type,
Credit = l.Credit,
Debit = l.Debit,
ledger = l
});
if (request.StartDate != null)
{
entry = entry.Where(d => d.ledger.Ledger.Date >= dateConverter.EthiopicToGregorian(request.StartDate));
}
if (request.EndDate != null)
{
entry = entry.Where(d => d.ledger.Ledger.Date <= dateConverter.EthiopicToGregorian(request.EndDate));
}
var filtered = entry.GroupBy(a => new { a.AccountId, a.Type })
.Select(x => new TrialBalanceModel()
{
AccountId = x.Key.AccountId,
AccountType = x.Key.Type,
Credit = x.Sum(c => (decimal?)c.Credit),
AccountName = x.Select(s => s.AccountName).First(),
Debit = x.Sum(c => (decimal?)c.Debit),
}).AsQueryable();
if (request.Filter.Count() > 0)
{
filtered = filtered
.Where(DynamicQueryHelper
.BuildWhere <TrialBalanceModel> (request.Filter)).AsQueryable();
}
var PageSize = request.PageSize;
var PageNumber = (request.PageSize == 0) ? 1 : request.PageNumber;
result.Count = filtered.Count();
result.Items = filtered.OrderBy(sortBy, sortDirection)
.Skip(PageNumber - 1)
.Take(PageSize)
.ToList();
decimal?difference = 0;
foreach (var item in result.Items)
{
if (item.AccountType.ToUpper() == "LIABILITY" || item.AccountType.ToUpper() == "REVENUE" || item.AccountType.ToUpper() == "CAPITAL")
{
difference = item.Credit - item.Debit;
if (difference < 0)
{
item.Debit = difference * -1;
item.Credit = null;
}
else
{
item.Credit = difference;
item.Debit = null;
}
}
else
{
difference = item.Debit - item.Credit;
if (difference < 0)
{
item.Credit = difference * -1;
item.Debit = null;
}
else
{
item.Debit = difference;
item.Credit = null;
}
}
}
return(Task.FromResult <FilterResultModel <TrialBalanceModel> > (result));
}
public Task <FilterResultModel <SubsidaryLedgerModel> > Handle(GetSubsidaryLedgerQuery request, CancellationToken cancellationToken)
{
var sortBy = request.SortBy.Trim() != "" ? request.SortBy : "ControlAccountId";
var sortDirection = (request.SortDirection.ToUpper() == "DESCENDING") ? true : false;
FilterResultModel <SubsidaryLedgerModel> finalResult = new FilterResultModel <SubsidaryLedgerModel> ();
var PageSize = request.PageSize;
var PageNumber = (request.PageSize == 0) ? 1 : request.PageNumber;
var list = _database.Account
.Include(e => e.ParentAccountNavigation)
.Where(x => x.ParentAccountNavigation != null)
.Where(x => x.Year == request.Year && x.LedgerEntry.Count > 0);
if (request.ControlAccountId.Trim() != "")
{
list = list.Where(l => l.ParentAccountNavigation.AccountId == request.ControlAccountId);
}
if (request.SubsidaryId.Trim() != "")
{
list = list.Where(l => l.AccountId == request.SubsidaryId);
}
if (request.StartDate != null)
{
list = list.Where(a => a.LedgerEntry
.Any(e => e.Ledger.Date <= dateConverter.EthiopicToGregorian(request.EndDate)));
}
if (request.EndDate != null)
{
list = list.Where(a => a.LedgerEntry
.Any(e => e.Ledger.Date <= dateConverter.EthiopicToGregorian(request.EndDate)));
}
var filtered = list.Select(SubsidaryLedgerModel.Projection)
.Select(DynamicQueryHelper.GenerateSelectedColumns <SubsidaryLedgerModel> (request.SelectedColumns))
.AsQueryable();
if (request.Filter.Count() > 0)
{
filtered = filtered
.Where(DynamicQueryHelper
.BuildWhere <SubsidaryLedgerModel> (request.Filter)).AsQueryable();
}
var fil = finalResult.Items = filtered.OrderBy(sortBy, sortDirection)
.Skip(PageNumber - 1)
.Take(PageSize)
.ToList();
List <SubsidaryLedgerModel> adjusted = new List <SubsidaryLedgerModel> ();
foreach (var item in fil)
{
var balance = item.BBF;
SubsidaryLedgerModel mod = new SubsidaryLedgerModel()
{
AccountId = item.ControlAccountId,
Id = item.Id,
AccountName = item.AccountName,
AccountType = item.AccountType,
SubAccountId = item.SubAccountId,
BBF = balance
};
foreach (var entry in item.Entries.OrderBy(s => s.Date).ThenBy(s => s.Id))
{
if (item.AccountType.ToUpper() == "LIABILITY" || item.AccountType.ToUpper() == "REVENUE" || item.AccountType.ToUpper() == "CAPITAL")
{
balance = balance + (entry.Credit - entry.Debit);
}
else
{
balance = balance + (entry.Debit - entry.Credit);
}
if (entry.Credit <= 0)
{
entry.Credit = null;
}
else
{
entry.Debit = null;
}
entry.Balance = balance;
((IList <SubsidaryLedgerDetailModel>)mod.Entries).Add(entry);
}
adjusted.Add(mod);
}
finalResult.Items = adjusted;
finalResult.Count = filtered.Count();
return(Task.FromResult <FilterResultModel <SubsidaryLedgerModel> > (finalResult));
}