/// <summary>
/// Method to bridge the Withdraw Transaction and getting the current balance from the db. Uses the customer_accounts_table.
/// </summary>
/// <param name="trans"></param>
/// <returns></returns>
public double WithdrawAmount(Transactions trans)
{
BankDataContext bankDataContext = new BankDataContext();
TransactionTable transTable = new TransactionTable();
Customer_Accounts_Table custAcctTable = new Customer_Accounts_Table();
if (bankDataContext.Customer_Accounts_Tables.Count(a => (a.Account_ID == trans.GetAccountID() && a.Customer_ID == trans.GetCustomerID())) < 1)
{
custAcctTable.Account_ID = trans.GetAccountID();
custAcctTable.Customer_ID = trans.GetCustomerID();
custAcctTable.Balance = Convert.ToDecimal(trans.GetAmount());
bankDataContext.Customer_Accounts_Tables.InsertOnSubmit(custAcctTable);
bankDataContext.SubmitChanges();
return(Convert.ToDouble(custAcctTable.Balance));
}
else
{
var amtQuery = bankDataContext.TransactionTables.Where(t => (t.Account_ID == trans.GetAccountID() && t.Customer_ID == trans.GetCustomerID() && t.Transaction_Type == trans.GetTransactionType().ToString())).ToArray().Last().Amount;
Customer_Accounts_Table myCust = bankDataContext.Customer_Accounts_Tables.SingleOrDefault(p => (p.Customer_ID == trans.GetCustomerID() && p.Account_ID == trans.GetAccountID()));
myCust.Balance = myCust.Balance - amtQuery;
Console.WriteLine(myCust.Balance);
bankDataContext.SubmitChanges();
return(Convert.ToDouble(myCust.Balance));
}
}
/// <summary>
/// Method to get the current balance of a customer based on the accountType
/// </summary>
/// <param name="trans"></param>
/// <returns>Current balance</returns>
public double GetBalance(Transactions trans)
{
BankDataContext bankDataContext = new BankDataContext();
TransactionTable transTable = new TransactionTable();
Customer_Accounts_Table custAcctTable = new Customer_Accounts_Table();
//var checkRecord = bankDataContext.TransactionTables.Where(c => (c.Account_ID == trans.GetAccountID() && c.Customer_ID == trans.GetCustomerID())).Any();
var balance = bankDataContext.Customer_Accounts_Tables.FirstOrDefault(b => (b.Account_ID == trans.GetAccountID() && b.Customer_ID == trans.GetCustomerID()));
if (balance == null)
{
return(0);
}
else
{
var checkBalance = bankDataContext.Customer_Accounts_Tables.Where(b => (b.Account_ID == trans.GetAccountID() && b.Customer_ID == trans.GetCustomerID())).Any();
if (!checkBalance)
{
return(0);
}
else
{
return(Convert.ToDouble(balance.Balance));
}
}
}
public void AddCustomerAccount(int custID, int acctID, double balance)
{
BankDataContext bankDataContext = new BankDataContext();
Customer_Accounts_Table custAcctTable = new Customer_Accounts_Table();
custAcctTable.Account_ID = acctID;
custAcctTable.Customer_ID = custID;
custAcctTable.Balance = Convert.ToDecimal(balance);
bankDataContext.Customer_Accounts_Tables.InsertOnSubmit(custAcctTable);
bankDataContext.SubmitChanges();
}
public void AddTransaction(Transactions trans)
{
BankDataContext bankDataContext = new BankDataContext();
TransactionTable transTable = new TransactionTable();
transTable.Transaction_Type = trans.GetTransactionType().ToString();
transTable.Account_ID = trans.GetAccountID();
transTable.Customer_ID = trans.GetCustomerID();
transTable.Transaction_Date = trans.GetDate().ToString();
transTable.Amount = Convert.ToDecimal(trans.GetAmount());
bankDataContext.TransactionTables.InsertOnSubmit(transTable);
bankDataContext.SubmitChanges();
}
/// <summary>
/// Method which checks the db and validates a customerID
/// </summary>
/// <param name="custID"></param>
/// <returns>A bool value</returns>
public bool ValidateCustomer(int custID, string password)
{
BankDataContext bankDataContext = new BankDataContext();
CustomerTable custTable = new CustomerTable();
var valIDQuery = bankDataContext.CustomerTables.Any(cust => (cust.Customer_ID == custID && cust.Customer_Password == password));
if (!valIDQuery)
{
return(false);
}
else
{
return(Convert.ToBoolean(valIDQuery));
}
}
/// <summary>
/// Method to get the customer name from the db based on the customerID
/// </summary>
/// <param name="custID"></param>
/// <returns>CustomerName</returns>
public string GetCustomerName(int custID)
{
BankDataContext bankDataContext = new BankDataContext();
CustomerTable custTable = new CustomerTable();
var custNameQuery = bankDataContext.CustomerTables.SingleOrDefault(cust => cust.Customer_ID == custID).Customer_Name;
if (custNameQuery == null)
{
return("Customer Name not found");
}
else
{
return(custNameQuery.ToString());
}
}
/// <summary>
/// Method to add a new customer into the db
/// </summary>
/// <param name="customer"></param>
/// <returns>New generated customerID</returns>
public int AddNewCustomer1(Customers customer)
{
BankDataContext bankDataContext = new BankDataContext();
CustomerTable custTable = new CustomerTable();
bankDataContext.Connection.Open();
custTable.Customer_Address = customer.GetCustomerAddress();
custTable.Customer_Name = customer.GetCustomerName();
custTable.Customer_Telephone = customer.GetCustomerTelephone();
bankDataContext.CustomerTables.InsertOnSubmit(custTable);
bankDataContext.SubmitChanges();
// how linq to sql works in fetching the identity column
return(custTable.Customer_ID);
}
/// <summary>
/// Method to get all the balances of a customer from the db
/// </summary>
/// <param name="custID"></param>
/// <returns>List of transactions with accountType and balances</returns>
public List <Transactions> GetAllBalances(int custID)
{
List <Transactions> allBalanceList = new List <Transactions>();
BankDataContext bankDataContext = new BankDataContext();
Customer_Accounts_Table transTable = new Customer_Accounts_Table();
var allBalanceQuery = bankDataContext.Customer_Accounts_Tables.Where(trans => trans.Customer_ID == custID).Select(s => new { s.Account_ID, s.Balance }).ToList();
foreach (var i in allBalanceQuery)
{
var getAcctType = bankDataContext.AccountTables.SingleOrDefault(a => a.Account_ID == i.Account_ID).Account_Type;
AccountType getAccountType = (AccountType)Enum.Parse(typeof(AccountType), getAcctType);
Transactions transObj = new Transactions(getAccountType, Convert.ToDouble(i.Balance));
allBalanceList.Add(transObj);
}
return(allBalanceList);
}
/// <summary>
/// Method to get all the transactions of a customer from the db
/// </summary>
/// <param name="custID"></param>
/// <returns>List of transactions for a customer</returns>
public List <Transactions> GetAllTransactions(int custID)
{
BankDataContext bankDataContext = new BankDataContext();
TransactionTable transTable = new TransactionTable();
var getAllTransQuery = bankDataContext.TransactionTables.Where(t => t.Customer_ID == custID);
List <Transactions> getAllTrans = new List <Transactions>();
foreach (var i in getAllTransQuery)
{
var getAcctType = bankDataContext.AccountTables.SingleOrDefault(a => a.Account_ID == i.Account_ID).Account_Type;
TransactionType getTransactionType = (TransactionType)Enum.Parse(typeof(TransactionType), i.Transaction_Type);
AccountType getAccountType = (AccountType)Enum.Parse(typeof(AccountType), getAcctType);
DateTime getDate = DateTime.ParseExact(i.Transaction_Date, "dd-MM-yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
Transactions getAllTransObj = new Transactions(getTransactionType, getAccountType, getDate, Convert.ToDouble(i.Amount));
getAllTrans.Add(getAllTransObj);
}
return(getAllTrans);
}
