public BudgetList SyncBudgets(BudgetList budgets)
{
try
{
var budgetsList = new BudgetList();
var budgetIDs = budgets.Select(x => x.BudgetId).ToList();
using (EntityContext context = new EntityContext())
{
var query = (from i in context.Budget
where budgetIDs.Contains(i.BudgetId)
select i).ToList();
//investigate if there is a better way to convert the generic list to ObservableCollection
foreach (var item in query)
{
var tempItem = budgets.FirstOrDefault(x => x.BudgetId == item.BudgetId);
if (tempItem.ModifiedDate < item.ModifiedDate)
budgets.Remove(tempItem);
}
}
return SaveBudgets(budgets);
}
catch (Exception)
{
throw;
}
}
public TransactionList SyncTransactions(TransactionList transactions)
{
try
{
var transList = new TransactionList();
var transIDs = transactions.Select(x => x.TransactionId).ToList();
using (EntityContext context = new EntityContext())
{
var query = (from i in context.Transaction
where transIDs.Contains(i.TransactionId)
select i).ToList();
//investigate if there is a better way to convert the generic list to ObservableCollection
foreach (var item in query)
{
var tempItem = transactions.FirstOrDefault(x => x.TransactionId == item.TransactionId);
if (tempItem.ModifiedDate < item.ModifiedDate)
transactions.Remove(tempItem);
}
}
return SaveTransactions(transactions);
}
catch (Exception)
{
throw;
}
}
public bool GetDBStatus()
{
var result = false;
using (var context = new EntityContext())
{
var user = context.User.Take(1);
result = user != null;
}
return result;
}
public DateTime GetLatestTransactionDate()
{
try
{
var date = DateTime.Now;
using (EntityContext context = new EntityContext())
date = context.Transaction.OrderByDescending(x => x.ModifiedDate).FirstOrDefault().ModifiedDate;
return date;
}
catch
{
throw;
}
}
private List<Transaction> ReportTransaction(DateTime dateFrom, DateTime dateTo, int transTypeId, double amountFrom, double amountTo, int userId)
{
try
{
var result = new List<Transaction>();
using (EntityContext context = new EntityContext())
{
var query = (from i in context.Transaction
.Include(i => i.Category)
.Include(i => i.TransactionReasonType)
where i.TransactionDate >= dateFrom && i.TransactionDate <= dateTo &&
i.TransactionType.TypeTransactionId == transTypeId &&
(amountFrom <= 0 || i.Amount >= amountFrom) && (amountTo <= 0 || i.Amount <= amountTo) &&
!i.IsDeleted && i.ModifiedUser.UserId == userId
orderby i.Amount descending
select i).ToList();
query.ForEach(x => result.Add(x));
return result;
}
}
catch (Exception)
{
throw;
}
}
public bool SaveTransactionImages(TransactionImageList transactionImages)
{
try
{
bool updateFound = false;
using (EntityContext context = new EntityContext())
{
foreach (var item in transactionImages)
{
if (item.TransactionImageId > 0) //Update
{
var original = context.TransactionImage.Where(t =>
t.TransactionImageId == item.TransactionImageId &&
t.ModifiedDate < item.ModifiedDate &&
!t.IsDeleted).FirstOrDefault();
if (original != null)
{
item.HasChanges = false;
original.CreatedUser = context.User.Where(k => !k.IsDeleted).Single(p => p.UserId == item.CreatedUser.UserId);
original.ModifiedUser = context.User.Where(k => !k.IsDeleted).Single(p => p.UserId == item.ModifiedUser.UserId);
context.Entry(original).CurrentValues.SetValues(item);
updateFound = true;
}
}
else //Insert
{
item.CreatedUser = context.User.Where(k => !k.IsDeleted).Single(p => p.UserId == item.CreatedUser.UserId);
item.ModifiedUser = context.User.Where(k => !k.IsDeleted).Single(p => p.UserId == item.ModifiedUser.UserId);
item.Transaction.Category = null;
item.Transaction.TransactionImages = null;
item.Transaction.TransactionReasonType = null;
item.Transaction.TransactionType = null;
item.Transaction.ModifiedUser = null;
item.Transaction.CreatedUser = null;
context.Entry(item.Transaction).State = EntityState.Unchanged;
context.TransactionImage.Add(item);
updateFound = true;
}
}
if (updateFound)
context.SaveChanges();
}
return true;
}
catch (DbEntityValidationException e)
{
StringBuilder s = new StringBuilder();
foreach (var eve in e.EntityValidationErrors)
{
Console.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
eve.Entry.Entity.GetType().Name, eve.Entry.State);
foreach (var ve in eve.ValidationErrors)
{
Console.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
ve.PropertyName, ve.ErrorMessage);
s.Append(string.Format("{0} - {1}", ve.PropertyName, ve.ErrorMessage));
}
}
throw new DbEntityValidationException(s.ToString());
}
catch (Exception)
{
throw;
}
}
public BudgetList SaveBudgets(BudgetList budgets)
{
try
{
bool updateFound = false;
using (EntityContext context = new EntityContext())
{
foreach (var item in budgets)
{
if (item.BudgetId > 0) //Update
{
var original = context.Budget.Where(t =>
t.BudgetId == item.BudgetId &&
t.ModifiedDate < item.ModifiedDate &&
!t.IsDeleted).FirstOrDefault();
//update database only if the transaction in DB is older
if (original != null)
{
item.HasChanges = false;
original.CreatedUser = context.User.Where(k => !k.IsDeleted).Single(p => p.UserId == item.CreatedUser.UserId);
original.ModifiedUser = context.User.Where(k => !k.IsDeleted).Single(p => p.UserId == item.ModifiedUser.UserId);
item.FromDate = new DateTime(item.FromDate.Year, item.FromDate.Month, item.FromDate.Day, 0, 0, 0);
item.ToDate = new DateTime(item.ToDate.Year, item.ToDate.Month, item.ToDate.Day, 0, 0, 0);
context.Entry(original).CurrentValues.SetValues(item);
updateFound = true;
}
}
else //Insert
{
item.CreatedUser = context.User.Where(k => !k.IsDeleted).Single(p => p.UserId == item.CreatedUser.UserId);
item.ModifiedUser = context.User.Where(k => !k.IsDeleted).Single(p => p.UserId == item.ModifiedUser.UserId);
item.FromDate = new DateTime(item.FromDate.Year, item.FromDate.Month, item.FromDate.Day, 0, 0, 0);
item.ToDate = new DateTime(item.ToDate.Year, item.ToDate.Month, item.ToDate.Day, 0, 0, 0);
context.Budget.Add(item);
updateFound = true;
}
}
if (updateFound)
context.SaveChanges();
}
GetTransactionsForBudgets(budgets);
budgets.PrepareForServiceSerialization();
return budgets;
}
catch (DbEntityValidationException e)
{
StringBuilder s = new StringBuilder();
foreach (var eve in e.EntityValidationErrors)
{
Console.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
eve.Entry.Entity.GetType().Name, eve.Entry.State);
foreach (var ve in eve.ValidationErrors)
{
Console.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
ve.PropertyName, ve.ErrorMessage);
s.Append(string.Format("{0} - {1}", ve.PropertyName, ve.ErrorMessage));
}
}
throw new DbEntityValidationException(s.ToString());
}
catch (Exception)
{
throw;
}
}
public TransactionList SaveTransactions(TransactionList transactions)
{
try
{
bool updateFound = false;
using (EntityContext context = new EntityContext())
{
foreach (var item in transactions)
{
if (item.TransactionId > 0) //Update
{
//update database only if the transaction in DB is older
var original = context.Transaction
.Include(x => x.Category)
.Include(x => x.TransactionType)
.Include(x => x.TransactionImages)
.Include(x => x.TransactionReasonType)
.Where(t => t.TransactionId == item.TransactionId &&
t.ModifiedDate < item.ModifiedDate &&
!t.IsDeleted).FirstOrDefault();
if (original !=null)
{
item.HasChanges = false;
original.Category = context.Category.Where(k => !k.IsDeleted).Single(p => p.CategoryId == item.Category.CategoryId);
//original.Category.CreatedUser = null;
//original.Category.ModifiedUser = null;
//context.Entry(original).Collection(x => x.TransactionImages).Load();
original.TransactionType = context.TypeTransaction.Where(k => !k.IsDeleted).Single(p => p.TypeTransactionId == item.TransactionType.TypeTransactionId);
original.TransactionReasonType = context.TransactionReason.Where(k => !k.IsDeleted).Single(p => p.TypeTransactionReasonId == item.TransactionReasonType.TypeTransactionReasonId);
original.CreatedUser = context.User.Where(k => !k.IsDeleted).Single(p => p.UserId == item.CreatedUser.UserId);
original.ModifiedUser = context.User.Where(k => !k.IsDeleted).Single(p => p.UserId == item.ModifiedUser.UserId);
if (item.TransactionImages != null)
{
foreach (var x in item.TransactionImages)
{
if (x.TransactionImageId > 0)
original.TransactionImages.Where(k => k.TransactionImageId == x.TransactionImageId).Select(k =>
{
k.Name = x.Name; k.Path = x.Path; k.ModifiedDate = x.ModifiedDate; k.IsDeleted = x.IsDeleted;
k.ModifiedUser = context.User.Where(p => !p.IsDeleted).Single(p => p.UserId == x.ModifiedUser.UserId);
return k;
}).ToList();
else
{
x.CreatedUser = context.User.Where(p => !p.IsDeleted).Single(p => p.UserId == x.CreatedUser.UserId);
x.ModifiedUser = context.User.Where(p => !p.IsDeleted).Single(p => p.UserId == x.ModifiedUser.UserId);
original.TransactionImages.Add(x);
}
}
}
context.Entry(original).CurrentValues.SetValues(item);
updateFound = true;
}
}
else //Insert
{
item.Category = context.Category.Where(k => !k.IsDeleted).Single(p => p.CategoryId == item.Category.CategoryId);
item.TransactionType = context.TypeTransaction.Where(k => !k.IsDeleted).Single(p => p.TypeTransactionId == item.TransactionType.TypeTransactionId);
if (item.TransactionReasonType != null)
item.TransactionReasonType = context.TransactionReason.Where(k => !k.IsDeleted).Single(p => p.TypeTransactionReasonId == item.TransactionReasonType.TypeTransactionReasonId);
item.CreatedUser = context.User.Where(k => !k.IsDeleted).Single(p => p.UserId == item.CreatedUser.UserId);
item.ModifiedUser = context.User.Where(k => !k.IsDeleted).Single(p => p.UserId == item.ModifiedUser.UserId);
if (item.TransactionImages != null)
{
foreach (var x in item.TransactionImages)
{
x.CreatedUser = context.User.Where(p => !p.IsDeleted).Single(p => p.UserId == x.CreatedUser.UserId);
x.ModifiedUser = context.User.Where(p => !p.IsDeleted).Single(p => p.UserId == x.ModifiedUser.UserId);
//item.TransactionImages.Add(x);
}
item.HasPhotos = item.TransactionImages.Count > 0;
}
context.Transaction.Add(item);
updateFound = true;
}
}
if (updateFound)
context.SaveChanges();
}
transactions.PrepareForServiceSerialization();
//return GetLatestTransactions();
return transactions;
}
catch (DbEntityValidationException e)
{
StringBuilder s = new StringBuilder();
foreach (var eve in e.EntityValidationErrors)
{
Console.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
eve.Entry.Entity.GetType().Name, eve.Entry.State);
foreach (var ve in eve.ValidationErrors)
{
Console.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
ve.PropertyName, ve.ErrorMessage);
s.Append(string.Format("{0} - {1}", ve.PropertyName, ve.ErrorMessage));
}
}
throw new DbEntityValidationException(s.ToString());
}
catch (Exception)
{
throw;
}
}
public StartupInfo LoadItemCounts(int userId)
{
try
{
StartupInfo startupInfo = new StartupInfo();
using (EntityContext context = new EntityContext())
{
startupInfo.BudgetCount = context.Budget.Where(k => !k.IsDeleted && k.ModifiedUser.UserId == userId).Count();
startupInfo.TransactionCount = context.Transaction.Where(k => !k.IsDeleted && k.ModifiedUser.UserId == userId).Count();
//startupInfo.TargetCount = context.Target.Where(k => !k.IsDeleted).Count();
}
return startupInfo;
}
catch (Exception)
{
throw;
}
}
private void GetTransactionsForBudgets(BudgetList budgetList)
{
try
{
using (EntityContext context = new EntityContext())
{
foreach (var item in budgetList)
{
var inQuery = from t in context.Transaction
.Include(t => t.TransactionType).Where(k => !k.IsDeleted)
where t.TransactionDate >= item.FromDate && t.TransactionDate <= item.ToDate
&& !t.IsDeleted && t.ModifiedUser.UserId == item.ModifiedUser.UserId
select t;
TransactionList tl = new TransactionList();
foreach (var inItem in inQuery.ToList())
tl.Add(inItem);
item.Transactions = tl;
////Apply the rule to create a new budget if the expired one is set to Repeat
//if (item.Repeat && item.ToDate < DateTime.Now)
//{
// var dataSpanRange = item.ToDate - item.FromDate;
// var dataSpanToday = DateTime.Now - item.ToDate;
// var dateDif = dataSpanRange.Days;
// var diff = dataSpanToday.Days % dataSpanRange.Days;
// var newFromDate = DateTime.Now.AddDays(-diff + 1);
// var newToDate = newFromDate.AddDays(dataSpanRange.Days);
// var test = newToDate - newFromDate;
// var newBudget = item.Clone();
// newBudget.FromDate = newFromDate;
// newBudget.ToDate = newToDate;
// item.Repeat = false;
// context.Budget.Add(newBudget);
// //context.SaveChanges();
//}
}
//return budgetList;
}
}
catch (Exception)
{
throw;
}
}
public BudgetList GetAllBudgets(int userId)
{
try
{
BudgetList budgetList = new BudgetList();
using (EntityContext context = new EntityContext())
{
var query = (from i in context.Budget
.Include(i => i.CreatedUser)
.Include(i => i.ModifiedUser)
where !i.IsDeleted && i.ModifiedUser.UserId == userId
select i).ToList();
//investigate if there is a better way to convert the generic list to ObservableCollection
foreach (var item in query)
budgetList.Add(item);
GetTransactionsForBudgets(budgetList);
budgetList.PrepareForServiceSerialization();
return budgetList;
}
}
catch (Exception)
{
throw;
}
}
public TransactionImageList GetImagesForTransaction(int transactionId)
{
TransactionImageList result = new TransactionImageList();
using(EntityContext context = new EntityContext())
{
var query = context.TransactionImage
.Include(x => x.Transaction)
.Include(x => x.CreatedUser)
.Include(x => x.ModifiedUser)
.Where(x =>x.Transaction.TransactionId == transactionId && !x.IsDeleted);
query.ToList().ForEach(x=>
{
x.Transaction.TransactionImages = null;
result.Add(x);
});
result.AcceptChanges();
}
return result;
}
public TransactionList GetTransactionsForBudget(int budgetId)
{
try
{
TransactionList transactionList = new TransactionList();
using (EntityContext context = new EntityContext())
{
// DateTime d = DateTime.Now.AddDays(100);
var query = from i in context.Transaction
.Include(i => i.TransactionType).Where(k => !k.IsDeleted)
.Include(i => i.TransactionReasonType).Where(k => !k.IsDeleted)
.Include(i => i.Category).Where(k => !k.IsDeleted)
//.Include(i => i.CreatedUser)
//.Include(i => i.ModifiedUser)
where i.CreatedDate >= context.Budget.Where(b => b.BudgetId == budgetId).Select(b => b.FromDate).FirstOrDefault()
&& i.CreatedDate <= context.Budget.Where(b => b.BudgetId == budgetId).Select(b => b.ToDate).FirstOrDefault()
&& !i.IsDeleted
orderby i.CreatedDate descending
select i;
foreach (var item in query)
transactionList.Add(item);
transactionList.AcceptChanges();
return transactionList;
}
}
catch (Exception)
{
throw;
}
}
public TransactionList GetLatestTransactionsLimit(int latestRecs, int userId)
{
try
{
TransactionList transList = new TransactionList();
using (EntityContext context = new EntityContext())
{
context.Configuration.LazyLoadingEnabled = true;
var query1 = (from i in context.Transaction
.Include(i => i.TransactionType)
.Include(i => i.TransactionReasonType)
.Include(i => i.Category)
.Include(i => i.Category.TypeTransactionReasons)
.Include(i => i.ModifiedUser)
.Include(i => i.CreatedUser)
where !i.IsDeleted && i.ModifiedUser.UserId == userId
orderby i.TransactionDate descending
select i).Take(latestRecs == 0 ? int.MaxValue : latestRecs).ToList();
//investigate if there is a better way to convert the generic list to ObservableCollection
foreach (var item in query1)
{
//item.trans.HasPhotos = item.hasPhotos;
item.HasPhotos = context.TransactionImage.Count(x => x.Transaction.TransactionId == item.TransactionId && !x.IsDeleted) > 0;
//var trans = item.item;
if (item.Category.IsDeleted)
{
item.Category = context.Category.FirstOrDefault(x => x.Name.Equals("other", StringComparison.InvariantCultureIgnoreCase));
item.TransactionReasonType = context.TransactionReason.FirstOrDefault(x => x.Name.Equals("other", StringComparison.InvariantCultureIgnoreCase));
}
if (item.TransactionReasonType.IsDeleted)
{
item.TransactionReasonType = context.TransactionReason.FirstOrDefault(x => x.Name.Equals("other", StringComparison.InvariantCultureIgnoreCase));
}
transList.Add(item);
}
transList.PrepareForServiceSerialization();
return transList;
}
}
catch (Exception)
{
throw;
}
}
public TransactionList GetAllTransactions(int userId)
{
return null;
try
{
TransactionList transList = new TransactionList();
using (EntityContext context = new EntityContext())
{
var query = (from i in context.Transaction
.Include(i => i.Category)
.Include(i => i.CreatedUser)
.Include(i => i.ModifiedUser)
.Include(i => i.TransactionReasonType)
.Include(i => i.TransactionType)
where !i.IsDeleted && i.ModifiedUser.UserId == userId
select new
{
transaction = i,
category = i.Category.IsDeleted ? null : i.Category,
categoryOther = context.Category.FirstOrDefault(x => x.Name.Equals("other", StringComparison.InvariantCultureIgnoreCase)),
transReason = i.TransactionReasonType.IsDeleted ? null : i.TransactionReasonType,
transReasonOther = context.TransactionReason.FirstOrDefault(x => x.Name.Equals("other", StringComparison.InvariantCultureIgnoreCase))
}).ToList();
foreach (var item in query)
{
var trans = item.transaction;
if (item.category == null)
{
trans.Category = item.categoryOther;
trans.TransactionReasonType = item.transReasonOther;
}
if (item.transReason == null)
{
trans.TransactionReasonType = item.transReasonOther;
}
transList.Add(trans);
}
transList.AcceptChanges();
return transList;
}
}
catch (Exception)
{
throw;
}
}
