public Guid Update(Model.Member memberEntity)
{
var context = new CMS_DataContext();
var member = context.Members.Where(x => x.Id == memberEntity.Id).FirstOrDefault();
member.FirstName = memberEntity.FirstName;
member.MiddleName = memberEntity.MiddleName;
member.LastName = memberEntity.LastName;
member.Age = memberEntity.Age;
member.Gender = memberEntity.Gender;
member.Birthday = string.IsNullOrEmpty(memberEntity.Birthday)? (DateTime?)null: DateTime.Parse(memberEntity.Birthday);
member.MobilePhone = memberEntity.MobilePhone;
member.LandLine = memberEntity.LandLine;
member.Address = memberEntity.Address;
member.MaritalStatus = memberEntity.MaritalStatus;
member.NameOfSpouse = memberEntity.NameOfSpouse;
member.SpouseContact = memberEntity.SpouseContact;
member.ChildrenCount = memberEntity.ChildrenCount;
member.MemberStatus = memberEntity.MemberStatus;
member.BaptizedDate = string.IsNullOrEmpty(memberEntity.BaptizedDate) ? (DateTime?)null : DateTime.Parse(memberEntity.BaptizedDate);
member.BaptizedPlace = memberEntity.BaptizedPlace;
member.BaptizedMinister = memberEntity.BaptizedMinister;
member.BelongsToGroups = memberEntity.BelongsToGroups;
member.Positions = memberEntity.Positions;
context.SubmitChanges();
return(memberEntity.Id);
}
public List <Model.Member> List()
{
var context = new CMS_DataContext();
return(context.Members.Select(x =>
new Model.Member()
{
Id = x.Id,
FirstName = x.FirstName,
MiddleName = x.MiddleName,
LastName = x.LastName,
Age = x.Age.Value,
Gender = x.Gender,
Birthday = x.Birthday.HasValue ? x.Birthday.Value.ToShortDateString():null,
MobilePhone = x.MobilePhone,
LandLine = x.LandLine,
Address = x.Address,
MaritalStatus = x.MaritalStatus,
NameOfSpouse = x.NameOfSpouse,
SpouseContact = x.SpouseContact,
ChildrenCount = x.ChildrenCount.HasValue ? x.ChildrenCount.Value : (int?)null,
MemberStatus = x.MemberStatus,
BaptizedDate = x.BaptizedDate.HasValue ? x.BaptizedDate.Value.ToShortDateString() : null,
BaptizedPlace = x.BaptizedPlace,
BaptizedMinister = x.BaptizedMinister,
BelongsToGroups = x.BelongsToGroups,
Positions = x.Positions
}
).ToList());
}
public Guid Create(Model.Member memberEntity)
{
var context = new CMS_DataContext();
var member = new DataAccess.Member()
{
Id = memberEntity.Id,
FirstName = memberEntity.FirstName,
MiddleName = memberEntity.MiddleName,
LastName = memberEntity.LastName,
Age = memberEntity.Age,
Gender = memberEntity.Gender,
Birthday = string.IsNullOrEmpty(memberEntity.Birthday)? (DateTime?)null: DateTime.Parse(memberEntity.Birthday),
MobilePhone = memberEntity.MobilePhone,
LandLine = memberEntity.LandLine,
Address = memberEntity.Address,
MaritalStatus = memberEntity.MaritalStatus,
NameOfSpouse = memberEntity.NameOfSpouse,
SpouseContact = memberEntity.SpouseContact,
ChildrenCount = memberEntity.ChildrenCount,
MemberStatus = memberEntity.MemberStatus,
BaptizedDate = string.IsNullOrEmpty(memberEntity.BaptizedDate)?(DateTime?)null:DateTime.Parse(memberEntity.BaptizedDate),
BaptizedPlace = memberEntity.BaptizedPlace,
BaptizedMinister = memberEntity.BaptizedMinister,
BelongsToGroups = memberEntity.BelongsToGroups,
Positions = memberEntity.Positions
};
context.Members.InsertOnSubmit(member);
context.SubmitChanges();
return(memberEntity.Id);
}
public void CreateFundAccount(DataAccess.Model.Fund fund)
{
CMS_DataContext context = new CMS_DataContext();
var acct = new DataAccess.Account()
{
Id = Guid.NewGuid(),
Name = fund.Account.Name,
Category = fund.Account.Category,
Type = fund.Account.Type,
Code = null
};
var fnd = new DataAccess.Fund()
{
ID = Guid.NewGuid(),
Parent = fund.Parent.Id,
AccountId = acct.Id,
FundType = fund.FundType
};
context.Accounts.InsertOnSubmit(acct);
if (acct.Category.ToUpper() == "FUNDACTIVITY")
{
context.Funds.InsertOnSubmit(fnd);
}
context.SubmitChanges();
}
public void Update(DataAccess.Model.Bank bank)
{
CMS_DataContext context = new CMS_DataContext();
var acct = context.Accounts.Where(x => x.Id == bank.Id).FirstOrDefault();
acct.Name = bank.Name;
context.SubmitChanges();
}
public List <Model.Bank> GetBankBalance()
{
CMS_DataContext context = new CMS_DataContext();
var items = context.BankBalances
.Select(s => new Model.Bank()
{
Id = s.Id,
Name = s.Name,
Amount = s.Amount
}
).ToList();
return(items);
}
public Guid Update(Model.Transaction tranEntity)
{
var context = new CMS_DataContext();
var trans = context.Transactions.Where(x => x.Id == tranEntity.Id).FirstOrDefault();
trans.Reference = tranEntity.Reference;
trans.Amount = tranEntity.Amount;
trans.Notes = tranEntity.Notes;
trans.CreditAccountId = tranEntity.CreditAccount.Id;
trans.DebitAccountId = tranEntity.DebitAccount.Id;
trans.Date = DateTime.Parse(tranEntity.Date);
context.SubmitChanges();
return(tranEntity.Id);
}
public List <Model.Account> GetBankAccount()
{
CMS_DataContext context = new CMS_DataContext();
var items = context.Accounts.Where(x => x.Type == "ASSET" && x.Code == "BANK")
.Select(s => new Model.Account()
{
Id = s.Id,
Name = s.Name,
Type = s.Type,
Category = s.Category
}
).ToList();
return(items);
}
public void Delete(Guid id)
{
try
{
var context = new CMS_DataContext();
var trans = context.Transactions.Where(x => x.Id == id).FirstOrDefault();
context.Transactions.DeleteOnSubmit(trans);
context.SubmitChanges();
}
catch (Exception ex)
{
throw ex;
}
}
public object GetBankActivity(Guid bankId, int page, DateTime dateFilter)
{
//decimal totalBal = 0;
var pageSize = 10;
CMS_DataContext context = new CMS_DataContext();
var items = (from trans in context.Transactions
join bank in context.Accounts on trans.DebitAccountId equals bank.Id
join acct in context.Accounts on trans.CreditAccountId equals acct.Id
where bank.Code == "BANK" && bank.Id == bankId && (trans.Date >= dateFilter || dateFilter == null)
select new Model.BankActivity()
{
Id = trans.Id,
Reference = trans.Reference,
BankName = bank.Name,
Amount = trans.Amount,
Date = trans.Date,
TransactionType = "Deposit",
AccountName = acct.Name
}).ToList();
items.AddRange((from trans in context.Transactions
join bank in context.Accounts on trans.CreditAccountId equals bank.Id
join acct in context.Accounts on trans.DebitAccountId equals acct.Id
where bank.Code == "BANK" && bank.Id == bankId && (trans.Date >= dateFilter || dateFilter == null)
select new Model.BankActivity()
{
Id = trans.Id,
Reference = trans.Reference,
BankName = bank.Name,
Amount = trans.Amount * -1,
Date = trans.Date,
TransactionType = "Withdraw",
AccountName = acct.Name
}).ToList());
var count = items.Count();
//var pagedItems = items.OrderByDescending(x => x.Date).ToList();
var pagedItems = items.OrderByDescending(x => x.Date).Skip((page - 1) * pageSize).Take(pageSize).ToList();
var balance = GetBankBalance().Where(x => x.Id == bankId).FirstOrDefault().Amount;
return(new JsonTransactionResponse()
{
Count = count,
Items = pagedItems,
TotalBalance = GetBankBalance().Where(x => x.Id == bankId).FirstOrDefault().Amount
});
}
public object GetExpenses(DateTime?dateFilter, int page)
{
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith <Transaction>(t => t.CreditAccount);
decimal totalBal = 0;
var pageSize = 10;
var context = new CMS_DataContext();
context.LoadOptions = dlo;
var allItems = context.Transactions;
var items = context.Transactions.Where(x => (x.DebitAccount.Category.ToUpper() == "FUNDACTIVITY" && x.DebitAccount.Type.ToUpper() == "EXPENSE" && (x.Date == dateFilter || dateFilter == null)) || x.DebitAccount.Fund.FundType == "RESTRICTED");
var count = items.Count();
if (count > 0)
{
totalBal = allItems.Sum(x => x.Amount);
}
var pagedItems = items.Skip((page - 1) * pageSize).Take(pageSize)
.Select(x => new Model.Transaction
{
Id = x.Id,
Reference = x.Reference,
Notes = x.Notes,
CreditAccount = new Model.Account()
{
Id = x.CreditAccount.Id, Name = x.CreditAccount.Name
},
DebitAccount = new Model.Account()
{
Id = x.DebitAccount.Id, Name = x.DebitAccount.Name
},
Amount = x.Amount,
Date = x.Date.ToShortDateString()
}).ToList();
return(new JsonTransactionResponse()
{
Count = count,
Items = pagedItems,
TotalBalance = totalBal
});
}
public void Delete(Guid id)
{
CMS_DataContext context = new CMS_DataContext();
var canDelete = context.Transactions.Where(x => x.CreditAccountId == id || x.DebitAccountId == id).Count() == 0;
if (canDelete)
{
var acct = context.Accounts.Where(x => x.Id == id).FirstOrDefault();
context.Accounts.DeleteOnSubmit(acct);
context.SubmitChanges();
}
else
{
throw new Exception("Cannot delete this bank account, its containing data.");
}
}
public Guid Create(DataAccess.Model.Bank bank)
{
CMS_DataContext context = new CMS_DataContext();
var acct = new DataAccess.Account()
{
Id = Guid.NewGuid(),
Name = bank.Name,
Category = "BALANCE",
Type = "ASSET",
Code = "BANK"
};
context.Accounts.InsertOnSubmit(acct);
context.SubmitChanges();
return(acct.Id);
}
public Guid Create(Model.Transaction transEntity)
{
var context = new CMS_DataContext();
var trans = new DataAccess.Transaction()
{
Id = transEntity.Id,
Reference = transEntity.Reference,
Notes = transEntity.Notes,
CreditAccountId = transEntity.CreditAccount.Id,
DebitAccountId = transEntity.DebitAccount.Id,
Amount = transEntity.Amount,
Date = DateTime.Parse(transEntity.Date)
};
try
{
context.Transactions.InsertOnSubmit(trans);
context.SubmitChanges();
}
catch (Exception ex)
{ throw ex; }
return(transEntity.Id);
}
public List <Model.Fund> GetFundAccount(string fundType)
{
CMS_DataContext context = new CMS_DataContext();
var items = (from fu in context.Funds
join ac in context.Accounts on fu.AccountId equals ac.Id
join parent in context.Accounts on fu.Parent equals parent.Id
where ac.Type.ToUpper() == fundType.ToUpper() || fu.FundType == "RESTRICTED"
select new Model.Fund
{
Id = fu.ID,
Account = new Model.Account()
{
Id = ac.Id, Name = fu.FundType == "RESTRICTED" ? "RES-" + ac.Name : ac.Name, Type = ac.Type, Category = ac.Category
},
FundType = fu.FundType,
Parent = new Model.Account()
{
Id = parent.Id, Name = parent.Name, Type = parent.Type, Category = parent.Category
},
}).OrderBy(o => o.Parent.Name).ThenBy(n => n.FundType).ToList();
return(items);
}
public List <Model.BalanceFund> GetBalanceFundList()
{
try
{
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith <Account>(t => t.Funds);
List <Model.BalanceFund> bf = null;
CMS_DataContext context = new CMS_DataContext();
context.LoadOptions = dlo;
bf = context.Accounts
.Where(x => x.Category.ToUpper() == "BALANCE" && x.Type.ToUpper() == "FUND")
.Select(s => new Model.BalanceFund()
{
Id = s.Id,
Name = s.Name,
Unrestricted = (from f in context.FundActivities
where f.FundType == "UNRESTRICTED" && f.Parent == s.Id
select new Model.Fund()
{
Id = f.Id,
Account = new Model.Account()
{
Id = f.AccountId, Name = f.Name, Type = f.Type
},
FundType = f.FundType,
Amount = f.Amount.HasValue ? f.Amount.Value : 0
}).ToList(),
Restricted = (from f in context.FundActivities
where f.FundType == "RESTRICTED" && f.Parent == s.Id
select new Model.Fund()
{
Id = f.Id,
Account = new Model.Account()
{
Id = f.AccountId, Name = f.Name, Type = f.Type
},
FundType = f.FundType,
Amount = f.Amount.HasValue ? f.Amount.Value : 0
}).ToList(),
//Unrestricted = (from f in context.Funds
// join a in context.Accounts on f.AccountId equals a.Id
// join t in context.Transactions on a.Id equals t.CreditAccountId into trans
// from x in trans.DefaultIfEmpty()
// where f.FundType == "UNRESTRICTED" && f.Parent == s.Id
// select new Model.Fund(){
// Id = f.ID,
// Account = new Model.Account(){Id = a.Id, Name = a.Name, Type = a.Type, Category = a.Category},
// FundType = f.FundType,
// Amount = 222
// }).ToList(),
//Restricted = (from f in context.Funds
// join a in context.Accounts on f.AccountId equals a.Id
// where f.FundType == "RESTRICTED" && f.Parent == s.Id
// select new Model.Fund()
// {
// Id = f.ID,
// Account = new Model.Account() { Id = a.Id, Name = a.Name, Type = a.Type, Category = a.Category },
// FundType = f.FundType,
// Amount = 222
// }).ToList(),
}).ToList();
return(bf);
}
catch (Exception ex)
{
throw ex;
}
}
//public object GetByTransactionType(string type,DateTime? dateFilter, int page)
//{
// DataLoadOptions dlo = new DataLoadOptions();
// dlo.LoadWith<Transaction>(t => t.Account);
// decimal totalBal = 0;
// var pageSize = 10;
// var context = new CMS_DataContext();
// context.LoadOptions = dlo;
// var allItems = context.Transactions;
// var items = context.Transactions.Where(x => x.TransactionType == type && (x.Date == dateFilter || dateFilter == null));
// var count = items.Count();
// if(count > 0)
// totalBal = allItems.Sum(x => x.Amount);
// var pagedItems = items.Skip((page - 1) * pageSize).Take(pageSize)
// .Select(x => new Model.Transaction {
// Id = x.Id,
// Reference = x.Reference,
// Notes = x.Notes,
// TransactionType = x.TransactionType,
// Account = new Model.Account(){ Id= x.Account.Id, Description = x.Account.Description },
// Amount = x.Amount,
// Date = x.Date.ToShortDateString()
// }).ToList();
// return new JsonTransactionResponse()
// {
// Count = count,
// Items = pagedItems,
// TotalBalance = totalBal
// };
//}
public Transaction Get(Guid id)
{
var context = new CMS_DataContext();
return(context.Transactions.Where(x => x.Id == id).FirstOrDefault());
}
public Member Get(Guid id)
{
var context = new CMS_DataContext();
return(context.Members.Where(x => x.Id == id).FirstOrDefault());
}
