public static AccountMovementViewModel LoadJournals(AromaContext db, DateTime fromDate, DateTime toDate, int? clientId, Guid? clientAccountId)
{
toDate = toDate.AddDays(1).AddMilliseconds(1);
var model = new AccountMovementViewModel();
var clientAccountIds = new Guid[0];
model.FromDate = fromDate;
model.ToDate = toDate;
model.ClientId = clientId;
model.ClientAccountId = clientAccountId;
model.Journals = new List<finJournal>();
if (clientAccountId.HasValue)
{
clientAccountIds = new Guid[] {clientAccountId.Value };
}
else
{
clientAccountIds = (from item in db.ClientAccounts
where (clientId.HasValue && item.ClientID == clientId.Value)
|| !(clientId.HasValue)
select item.ClientAccountId).ToArray();
}
/*
var test1 = (from item in db.Journals
where clientAccountIds.Contains(item.AccountID)
select item).ToList();
var test2 = (from item in test1
where item.EffectiveDate >= fromDate
orderby item.JournalDate
select item).ToList();
var test3 = (from item in test1
where item.EffectiveDate <= toDate
orderby item.JournalDate
select item).ToList();
*/
var finJournals = (from item in db.Journals
where item.EffectiveDate >= fromDate && item.EffectiveDate <= toDate
&& clientAccountIds.Contains(item.AccountID)
//&& item.Index == 1
orderby item.JournalDate
select item).ToList();
while (finJournals.Count > 0)
{
var leg1 = finJournals[0];
var leg2 = (from finJournal item in finJournals
where leg1.CorrespondingJournalId.Equals(item.JournalId)
select item).FirstOrDefault();
if (leg2 == null)
{
leg2 = db.Journals.First(m => m.JournalId.Equals(leg1.CorrespondingJournalId));
}
else
{
finJournals.Remove(leg2);
}
finJournals.Remove(leg1);
model.Journals.Add(leg1);
model.Journals.Add(leg2);
}
var RelevantAccounts = model.Journals.Select(m => m.AccountID).Distinct().ToArray();
model.Accounts = (from item in db.Accounts.ToArray()
where RelevantAccounts.Count() == 0|| RelevantAccounts.Contains(item.AccountId)
select new AccountViewModel()
{
AccountDescription = item.AccountName,
AccountId = item.AccountId,
IsClientAccount = false
}).ToList();
model.Accounts.AddRange((from item in db.ClientAccounts.Where(m=>(!clientId.HasValue || (clientId.HasValue && m.ClientID == clientId.Value))).ToArray()
where RelevantAccounts.Contains(item.ClientAccountId)
select new AccountViewModel() {
AccountDescription = db.Accounts.First(m=>m.AccountId.Equals(item.AccountId)).AccountName,
AccountId = item.ClientAccountId,
IsClientAccount = true
}) );
int index = 0;
foreach (var acc in model.Accounts)
{
if (clientAccountId.HasValue && !acc.AccountId.Equals(clientAccountId.Value))
{
acc.IsClientAccount = false;
}
AccountBalance openBal = db.Database.SqlQuery<AccountBalance>(string.Format(Generic.sqlBalanceAtDate, fromDate.ToString(dtfmt), acc.AccountId)).First();
AccountBalance closingBal = db.Database.SqlQuery<AccountBalance>(string.Format(Generic.sqlBalanceAtDate, toDate.ToString(dtfmt), acc.AccountId)).First();
/*
AccountBalance openBal = db.Database.SqlQuery<AccountBalance>(
"spBalanceAtDate",
new SqlParameter("date", fromDate),
new SqlParameter("accountId", acc.AccountId)
).First();
AccountBalance closingBal = db.Database.SqlQuery<AccountBalance>("spBalanceAtDate", toDate, acc.AccountId).First();*/
acc.OpenBalance = openBal.Balance;
acc.Balance = closingBal.Balance;
acc.FutureBalance = closingBal.FutureBalance;
acc.FutureBalanceDate = closingBal.FutureDate;
acc.columnIndex = index;
index++;
}
return model;
}
public AccountMovementViewModel LoadJournals(Guid movementSourceId)
{
var model = new AccountMovementViewModel();
model.Journals = new List<finJournal>();
var finJournals = (from item in db.Journals
where item.MovementSource.Equals(movementSourceId)
orderby item.JournalDate
select item).ToList();
while (finJournals.Count > 0)
{
var leg1 = finJournals[0];
var leg2 = (from finJournal item in finJournals
where leg1.CorrespondingJournalId.Equals(item.JournalId)
select item).FirstOrDefault();
if (leg2 == null)
{
leg2 = db.Journals.First(m => m.JournalId.Equals(leg1.CorrespondingJournalId));
}
else
{
finJournals.Remove(leg2);
}
finJournals.Remove(leg1);
model.Journals.Add(leg1);
model.Journals.Add(leg2);
}
var RelevantAccounts = model.Journals.Select(m => m.AccountID).Distinct().ToArray();
model.Accounts = (from item in db.Accounts.ToArray()
where RelevantAccounts.Count() == 0 || RelevantAccounts.Contains(item.AccountId)
select new AccountViewModel()
{
AccountDescription = item.AccountName,
AccountId = item.AccountId,
IsClientAccount = false
}).ToList();
model.Accounts.AddRange((from item in db.ClientAccounts.ToArray()
where RelevantAccounts.Contains(item.ClientAccountId)
select new AccountViewModel()
{
AccountDescription = string.Format("{0} ({1})", db.Accounts.First(m => m.AccountId.Equals(item.AccountId)).AccountName, item.ClientID),
AccountId = item.ClientAccountId,
IsClientAccount = true
}));
int index = 0;
var openBalanceDate = DateTime.Now;
if (model.Journals.Count > 0) openBalanceDate = model.Journals.Select(m => m.JournalDate).Min();
foreach (var acc in model.Accounts)
{
acc.OpenBalance = 0;
try
{
acc.OpenBalance = db.Journals.Where(m => m.AccountID.Equals(acc.AccountId) && m.Active && m.EffectiveDate < openBalanceDate && m.JournalDate < openBalanceDate).Sum(m => m.Amount);
}
catch { }
acc.Balance = model.Journals.Where(m=>m.AccountID.Equals(acc.AccountId) && m.Active).Sum(m=>m.Amount);
if (acc.IsClientAccount)
{
acc.Balance += acc.OpenBalance;
}
acc.FutureBalance = 0;
acc.FutureBalanceDate = DateTime.Now;
acc.columnIndex = index;
index++;
}
return model;
}
public static AccountMovementViewModel LoadGlobalJournals(AromaContext db, DateTime fromDate, DateTime toDate)
{
toDate = toDate.AddDays(1).AddMilliseconds(1);
var model = new AccountMovementViewModel();
model.FromDate = fromDate;
model.ToDate = toDate;
model.Journals = new List<finJournal>();
var finJournals = (from item in db.Journals
where item.EffectiveDate >= fromDate && item.EffectiveDate <= toDate
orderby item.JournalDate
select item).ToList();
while (finJournals.Count > 0)
{
var leg1 = finJournals[0];
var leg2 = (from finJournal item in finJournals
where leg1.CorrespondingJournalId.Equals(item.JournalId)
select item).FirstOrDefault();
if (leg2 == null)
{
leg2 = db.Journals.First(m => m.JournalId.Equals(leg1.CorrespondingJournalId));
}
else
{
finJournals.Remove(leg2);
}
finJournals.Remove(leg1);
model.Journals.Add(leg1);
model.Journals.Add(leg2);
}
model.Accounts = (from item in db.Accounts.Where(m=>m.Active).ToArray()
select new AccountViewModel()
{
AccountDescription = item.AccountName,
AccountId = item.AccountId,
IsClientAccount = false
}).ToList();
int index = 0;
foreach (var acc in model.Accounts)
{
AccountBalance openBal = db.Database.SqlQuery<AccountBalance>(string.Format(Generic.sqlGlobalBalanceAtDate, fromDate.ToString(dtfmt), acc.AccountId)).First();
AccountBalance closingBal = db.Database.SqlQuery<AccountBalance>(string.Format(Generic.sqlGlobalBalanceAtDate, toDate.ToString(dtfmt), acc.AccountId)).First();
/*
AccountBalance openBal = db.Database.SqlQuery<AccountBalance>(
"spBalanceAtDate",
new SqlParameter("date", fromDate),
new SqlParameter("accountId", acc.AccountId)
).First();
AccountBalance closingBal = db.Database.SqlQuery<AccountBalance>("spBalanceAtDate", toDate, acc.AccountId).First();*/
acc.OpenBalance = openBal.Balance;
acc.Balance = closingBal.Balance;
acc.FutureBalance = closingBal.FutureBalance;
acc.FutureBalanceDate = closingBal.FutureDate;
acc.columnIndex = index;
index++;
}
return model;
}