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 <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 <IncomeStatementViewModel> Handle(GetIncomeStatementQuery request, CancellationToken cancellationToken)
{
var query = (from account_type in _database.AccountType
.Where(a => a.TypeOfNavigation != null && (a.TypeOfNavigation.Type.ToUpper() == "EXPENSE" || a.TypeOfNavigation.Type.ToUpper() == "REVENUE" || a.TypeOfNavigation.Type.ToUpper() == "COST OF GOODS SOLD")) join account_category in _database.AccountCatagory on account_type.Id equals account_category.AccountTypeId join account in _database.Account.Where(a => a.Year == request.Year) on account_category.Id equals account.CatagoryId join ledger_entry in _database.LedgerEntry on account.Id equals ledger_entry.AccountId select new {
AccountType = account_type,
Category = account_category,
Credit = ledger_entry.Credit,
Entry = ledger_entry,
Debit = ledger_entry.Debit,
type = account_type.TypeOfNavigation.Type
});
if (request.StartDate != null)
{
query = query.Where(q => q.Entry.DateAdded >= dateConverter.EthiopicToGregorian(request.StartDate));
}
if (request.EndDate != null)
{
query = query.Where(q => q.Entry.DateAdded <= dateConverter.EthiopicToGregorian(request.EndDate));
}
var result = query.GroupBy(ef => ef.AccountType.IsSummery == 1 ? ef.AccountType.Type : ef.Category.Catagory).ToList()
.Select(g => new {
CreditSum = g.Sum(t => t.Credit),
DebitSum = g.Sum(t => t.Debit),
AccountCategory = g.Key,
Type = g.Select(d => d.type)
});
IncomeStatementViewModel incomeStateMent = new IncomeStatementViewModel();
float?totalRevenue = 0;
float?totalExpence = 0;
float?revenue = 0;
float?expense = 0;
foreach (var item in result)
{
if (item.Type.FirstOrDefault().ToString().ToUpper() == "REVENUE")
{
revenue = item.CreditSum - item.DebitSum;
totalRevenue += revenue;
incomeStateMent.Revenue.Add(new IncomeStatementItemModel()
{
AccountType = item.AccountCategory,
Amount = revenue
});
}
else if (item.Type.FirstOrDefault().ToString().ToUpper() == "EXPENSE")
{
expense = item.DebitSum - item.CreditSum;
totalExpence += expense;
incomeStateMent.Expense.Add(new IncomeStatementItemModel()
{
AccountType = item.AccountCategory,
Amount = expense
});
}
else
{
incomeStateMent.CostOfGoodsSold = item.DebitSum - item.CreditSum;
}
}
incomeStateMent.NetSurplus = totalRevenue - totalExpence;
return(Task.FromResult(incomeStateMent));
}
public async Task <Unit> Handle(CreateNewYearCommand request, CancellationToken cancellationToken)
{
var lastYear = _database.Account.Max(b => b.Year);
CustomDateConverter converter = new CustomDateConverter();
var date = converter.EthiopicToGregorian($"1/11/{lastYear}");
if (date > DateTime.Now)
{
throw new NotFoundException("date", lastYear);
}
int lastYearINT = Int32.Parse(lastYear);
var accounts = await _database.Account.Where(y => y.Year == lastYear)
.Include(l => l.LedgerEntry)
.Include(a => a.Catagory)
.ThenInclude(a => a.AccountType)
.Select(f => new {
Category = f.CatagoryId,
Name = f.AccountName,
AccountId = f.AccountId,
Parent = f.ParentAccount,
CostCenter = f.CostCenterId,
CreditSum = (decimal?)f.LedgerEntry.Sum(c => (decimal?)c.Credit),
DebitSum = (decimal?)f.LedgerEntry.Sum(d => (decimal?)d.Debit),
Type = f.Catagory.AccountType.TypeOfNavigation.Type,
Balance = (decimal?)f.OpeningBalance,
Year = f.Year
})
.ToListAsync();
IList <Account> accountsList = new List <Account> ();
var grouped = accounts.GroupBy(g => g.Type);
foreach (var item in grouped)
{
foreach (var i in item)
{
Account account = new Account()
{
AccountId = i.AccountId,
ParentAccount = i.Parent,
AccountName = i.Name,
CostCenterId = i.CostCenter,
CatagoryId = i.Category,
OpeningBalance = 0,
Year = (lastYearINT + 1).ToString()
};
if (item.Key.ToUpper() == "ASSET")
{
account.OpeningBalance = (float?)i.Balance + ((float?)i.DebitSum - (float?)i.CreditSum);
}
else if (item.Key.ToUpper() == "LIABILITY" || item.Key.ToUpper() == "CAPITAL")
{
account.OpeningBalance = (float?)i.Balance + ((float?)i.CreditSum - (float?)i.DebitSum);
}
accountsList.Add(account);
}
}
await _database.Account.AddRangeAsync(accountsList);
await _database.SaveAsync();
return(Unit.Value);
}
public async Task <Unit> Handle(UpdateLedgerEntryCommand request, CancellationToken cancellationToken)
{
var entry = await _database.Ledger
.Include(x => x.LedgerEntry)
.FirstOrDefaultAsync(i => i.Id == request.Id);
CustomDateConverter c = new CustomDateConverter();
if (entry == null)
{
throw new NotFoundException("Ledger", request.Id);
}
entry.IsPosted = request.Posted;
entry.Date = c.EthiopicToGregorian(request.Date);
entry.DateEt = request.Date;
float?totalCredit = 0;
float?totalDebit = 0;
foreach (var item in request.DeletedIds)
{
var deleted = await _database.LedgerEntry.FindAsync(item);
if (deleted == null)
{
throw new NotFoundException("Ledger Entry", item);
}
_database.LedgerEntry.Remove(deleted);
}
foreach (var item in request.Entries)
{
if (item.Id != 0)
{
var updated = entry.LedgerEntry.Where(x => x.Id == item.Id).First();
updated.AccountId = item.AccountId;
updated.Credit = item.Credit;
updated.Debit = item.Debit;
_database.LedgerEntry.Update(updated);
}
else
{
_database.LedgerEntry.Add(new LedgerEntry()
{
AccountId = item.AccountId,
LedgerId = entry.Id,
Credit = item.Credit,
Debit = item.Debit
});
}
totalCredit += item.Credit;
totalDebit += item.Debit;
}
if (totalCredit != totalDebit && request.Posted != 0)
{
hasError = true;
validationFailures.Add(new ValidationFailure("Balance", "Can not post unbalanced entry"));
}
if (hasError)
{
throw new ValidationException(validationFailures);
}
await _database.SaveAsync();
return(Unit.Value);
}
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));
}
public async Task <int> Handle(CreateLedgerEntryCommand request, CancellationToken cancellationToken)
{
bool error = false;
List <ValidationFailure> validationFailures = new List <ValidationFailure> ();
if (await _database.Ledger.AnyAsync(v => v.VoucherId.ToLower().Trim() == request.VoucherId.ToLower().Trim()))
{
error = true;
validationFailures.Add(new ValidationFailure("VoucherId", "Voucher Id provided has already been used for anouther entry, use another Id"));
}
CustomDateConverter c = new CustomDateConverter();
Ledger ledger = new Ledger()
{
Description = request.Description,
Date = c.EthiopicToGregorian(request.Date),
VoucherId = request.VoucherId.Trim(),
IsPosted = request.Posted,
Reference = request.Reference.Trim(),
DateAdded = DateTime.Now,
DateUpdated = DateTime.Now,
DateEt = request.Date
};
float?totalCredit = 0;
float?totalDebit = 0;
if (request.Entries.Count() < 2)
{
error = true;
validationFailures.Add(new ValidationFailure("Number of Entries", $"Atleast two accounts must be involved to have a successful ledger entry only {request.Entries.Count()} Given"));
}
foreach (var item in request.Entries)
{
ledger.LedgerEntry.Add(new LedgerEntry()
{
AccountId = item.AccountId,
Credit = item.Credit,
Debit = item.Debit
});
totalCredit += item.Credit;
totalDebit += item.Debit;
}
Console.WriteLine($" {Math.Round ((decimal) totalCredit, 2)} {Math.Round ((decimal) totalDebit, 2)} ");
if (Math.Round((decimal)totalCredit, 2) != Math.Round((decimal)totalDebit, 2))
{
error = true;
validationFailures.Add(new ValidationFailure("Balance", "Credit and debit amount for this are not balanced"));
}
if (error)
{
throw new ValidationException(validationFailures);
}
_database.Ledger.Add(ledger);
await _database.SaveAsync();
return(ledger.Id);
}