public static List <PandLReportDto> PandL(DateTime fromDate, DateTime toDate)
{
using (var db = new GLDbContext())
{
var debitResults = db.Transactions
.Where(x => x.TranHeader.Date >= fromDate && x.TranHeader.Date <= toDate & !x.DebitAccount.Category.IsBalanceSheet)
.GroupBy(x => x.DebitAccount)
.Select(r => new PandLReportDto
{
Account = r.FirstOrDefault().DebitAccount.Code,
Amount = r.Sum(x => 0 - x.Amount)
}).ToList();
var creditResults = db.Transactions
.Where(x => x.TranHeader.Date >= fromDate &&
x.TranHeader.Date <= toDate & !x.CreditAccount.Category.IsBalanceSheet)
.GroupBy(x => x.CreditAccount)
.Select(r => new PandLReportDto
{
Account = r.FirstOrDefault().CreditAccount.Code,
Amount = r.Sum(x => x.Amount)
}).ToList();
var results = debitResults;
results.AddRange(creditResults);
var refined = results.GroupBy(x => x.Account).Select(r => new PandLReportDto
{
Account = r.First().Account,
Amount = r.Sum(x => x.Amount)
}).ToList();
return(refined);
}
}
public static decimal GetOpeningBalance(TranHeader header)
{
using (var db = new GLDbContext())
{
try
{
if (header.LinkedAccount == null)
{
return(0);
}
var credits = db.Transactions
.Where(x => x.CreditAccount.Id == header.LinkedAccount.Id &&
x.TranHeader.StatementNumber < header.StatementNumber);
var creditTotal = credits.Any() ? credits.Sum(y => y.Amount) : 0;
var debits = db.Transactions
.Where(x => x.DebitAccount.Id == header.LinkedAccount.Id &&
x.TranHeader.StatementNumber < header.StatementNumber);
var debitTotal = debits.Any() ? debits.Sum(y => y.Amount) : 0;
return(header.LinkedAccount.OpeningBalance + creditTotal - debitTotal);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
}
public static List <BalanceSheetDto> BalanceSheet(DateTime AsAtDate)
{
var data = new List <BalanceSheetDto>();
using (var db = new GLDbContext())
{
var debitResults = db.Transactions.
Where(x => x.TranHeader.Date <= AsAtDate && x.DebitAccount.Category.IsBalanceSheet)
.GroupBy(x => x.DebitAccount).Select(r => new BalanceSheetDto()
{
Account = r.FirstOrDefault().DebitAccount.Code,
Amount = r.Sum(x => 0 - x.Amount) + r.FirstOrDefault().DebitAccount.OpeningBalance
}).ToList();
var creditResults = db.Transactions.
Where(x => x.TranHeader.Date <= AsAtDate && x.CreditAccount.Category.IsBalanceSheet)
.GroupBy(x => x.CreditAccount).Select(r => new BalanceSheetDto()
{
Account = r.FirstOrDefault().DebitAccount.Code,
Amount = r.Sum(x => 0 - x.Amount) + r.FirstOrDefault().CreditAccount.OpeningBalance
}).ToList();
var results = debitResults;
results.AddRange(creditResults);
var refined = results.GroupBy(x => x.Account).Select(r => new BalanceSheetDto()
{
Account = r.First().Account,
Amount = r.Sum(x => x.Amount)
}).ToList();
return(refined);
}
}
public static void Seed(GLDbContext context)
{
var balanceSheetGst = context.GstCategories.Add(new GstCategory {
Code = "N-T", Percent = 0
});
var pandlGst = context.GstCategories.Add(new GstCategory {
Code = "GST", Percent = 10
});
for (int i = (int)GLCategoryEnum.Asset; i <= (int)GLCategoryEnum.OtherIncome; i++)
{
var IsBalSheet = IsBalanceSheet(i);
var cat = context.GLCategories.Add(new GLCategory {
Category = i, IsBalanceSheet = IsBalSheet
});
var gstCategory = IsBalSheet ? balanceSheetGst : pandlGst;
var account = new Account {
Code = $"0{i}", Category = cat, GstCategory = gstCategory
};
var child1 = new Account {
Code = $"0{i}-0100", Category = cat, GstCategory = gstCategory, Parent = account
};
account.Children.Add(child1);
var child2 = new Account {
Code = $"0{i}-0200", Category = cat, GstCategory = gstCategory, Parent = account
};
account.Children.Add(child2);
var child3 = new Account {
Code = $"0{i}-0300", Category = cat, GstCategory = gstCategory, Parent = account
};
account.Children.Add(child3);
context.Accounts.Add(account);
context.Accounts.Add(child1);
context.Accounts.Add(child2);
context.Accounts.Add(child3);
for (int j = 0; j < 20; j++)
{
var grandchild = new Account {
Code = $"0{i}-gc{j:D2}", Category = cat, GstCategory = gstCategory, Parent = child3
};
child3.Children.Add(grandchild);
}
}
context.SaveChanges();
}
public static void EnsureDatabaseIsCreated()
{
try
{
using (var db = new GLDbContext())
{
var account = db.Accounts.FirstOrDefault();
if (account == null)
{
HandyDataFunctions.Seed(db);
}
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
private static Setting MakeSetting(string settingName, string defaultValue)
{
using (var connect = new GLDbContext())
{
var setting = new Setting {
Name = settingName, Value = defaultValue
};
connect.Settings.Add(setting);
connect.SaveChanges();
var newSetting = connect.Settings.SingleOrDefault(x => x.Name == setting.Name);
if (newSetting == null)
{
throw new Exception("Expected setting to be found");
}
return(newSetting);
}
}
public static decimal GetNextStatementNumber(Account linkedAccount)
{
using (var db = new GLDbContext())
{
try
{
var lastStatement = db.TranHeaders.Where(x => x.LinkedAccount.Id == linkedAccount.Id)
.OrderByDescending(x => x.StatementNumber).FirstOrDefault();
if (lastStatement == null)
{
return(1);
}
return(lastStatement.StatementNumber + 1);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
}
