public void AddContactNumber(string ContactNumber)
{
DataTable temp = new DataTable();
UserDAO dataAccess = new UserDAO();
Int64? customerID = null;
string query = "use ApteanEdgeBank select * from Customer";
temp = dataAccess.GetData(query, UserDAO.connectionString);
int i = 0;
for (; i < (temp.Rows.Count); i++)
{
if (firstName == (string)temp.Rows[i]["FirstName"] && lastName == (string)temp.Rows[i]["LastName"] && dateOfBirth.ToString() == Convert.ToString(temp.Rows[i]["DateOfBirth"]))
{
customerID = (Int64)temp.Rows[i]["CustomerID"]; break;
}
else
{
continue;
}
}
if (customerID != null)
{
dataAccess.InsertData("use ApteanEdgeBank insert into CustomerContact (CustomerID) values(" + customerID + ")", UserDAO.connectionString);
}
else
{
System.Windows.Forms.MessageBox.Show("Fatal Error: Customer ID not generated ");
}
temp.Clear();
temp = dataAccess.GetData("use ApteanEdgeBank select * from CustomerContact", UserDAO.connectionString);
i = 0;
for (; i < (temp.Rows.Count); i++)
{
if (customerID == (Int64)temp.Rows[i]["CustomerID"])
{
ContactID = temp.Rows[i]["CustomerContactID"].ToString(); break;
}
else
{
continue;
}
}
if (ContactID != null)
{
// System.Windows.Forms.MessageBox.Show(ContactID);
dataAccess.InsertData("use ApteanEdgeBank insert into CustomerContactDetails (CustomerContactID,CustomerContactNumber) values(" + ContactID + "," + ContactNumber + ")", UserDAO.connectionString);
}
else
{
System.Windows.Forms.MessageBox.Show("Fatal Error: Contact ID not generated");
}
}
public Update_Customer_Profile()
{
try
{
InitializeComponent();
dataTable.Clear();
dataTable = dataAccess.GetData("use ApteanEdgeBank select * from Customer", UserDAO.connectionString);
dataGridView1.DataSource = bindingSource1;
bindingSource1.DataSource = dataTable;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public string GetAccountType(int accId)
{
UserDAO dao = new UserDAO();
DataTable dt = new DataTable();
string myQuery = "select * from Account where AccountID=" + accId;
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
dt = dao.GetData(myQuery, connectionstring);
string Type = "";
if (Convert.ToString(dt.Rows[0]["AccountType"]) == "CA")
{
Type = "CA";
return(Type);
}
else if (Convert.ToString(dt.Rows[0]["AccountType"]) == "TFS")
{
Type = "TFS";
return(Type);
}
else if (Convert.ToString(dt.Rows[0]["AccountType"]) == "LA")
{
Type = "LA";
return(Type);
}
else
{
return(Type);
}
}
public bool CustomerAvailabilityCheck()
{
UserDAO udao = new UserDAO();
DataTable dataTable = new DataTable();
bool foundFlag = false;
string customerID = textBox1.Text;
dataTable = udao.GetData("use ApteanEdgeBank select * from Customer", UserDAO.connectionString);
ListOfCustomers newForm = new ListOfCustomers();
for (int i = 0; i < dataTable.Rows.Count; i++)
{
if (Convert.ToString(dataTable.Rows[i]["CustomerID"]) == customerID)
{
foundFlag = true;
}
}
if (foundFlag)
{
return true;
}
else
{
return false;
}
}
public string GetAccountType(int accId)
{
UserDAO dao = new UserDAO();
DataTable dt = new DataTable();
string myQuery = "select * from Account where AccountID=" + accId;
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
dt = dao.GetData(myQuery, connectionstring);
string Type = "";
if (Convert.ToString(dt.Rows[0]["AccountType"]) == "CA")
{
Type="CA";
return Type;
}
else if (Convert.ToString(dt.Rows[0]["AccountType"]) == "TFS")
{
Type="TFS";
return Type;
}
else if (Convert.ToString(dt.Rows[0]["AccountType"]) == "LA")
{
Type = "LA";
return Type;
}
else
{
return Type;
}
}
public void Withdraw(double amount,int accID)
{
UserDAO dao=new UserDAO();
DataTable dt=new DataTable();
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
BankGeneralAccount bg = new BankGeneralAccount();
dt=dao.GetData("Select AccountBalance from Account where AccountID="+accID,connectionstring);
Balance=Convert.ToDouble(dt.Rows[0]["AccountBalance"]);
Balance = Balance - amount;
if (Balance > 0)
{
string myQuery = "Update Account set AccountBalance=" + Balance + " where AccountID="+accID;
//UserDAO dao = new UserDAO();
bg.BgaWithdraw(amount);
dao.UpdateData(myQuery, connectionstring);
AccountActivityLedger Ledger = new AccountActivityLedger();
Ledger.AddAccountActivity(accID, "Withdraw",amount, DateTime.Now);
MessageBox.Show("Amount withdraw Successful!");
}
//string myQuery="up
else
MessageBox.Show("Your balance is too low!");
}
public override void Deposit(double amount, int accID)
{
UserDAO dao = new UserDAO();
DataTable dt = new DataTable();
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
BankGeneralAccount bg = new BankGeneralAccount();
dt = dao.GetData("Select AccountBalance from Account where AccountID=" + accID, connectionstring);
Balance = Convert.ToDouble(dt.Rows[0]["AccountBalance"]);
if ((Balance + amount) <= 5000)
{
Balance = Balance + amount;
string myQuery = "Update Account set AccountBalance=" + Balance + " where AccountID=" + accID;
//UserDAO dao = new UserDAO();
dao.UpdateData(myQuery, connectionstring);
bg.BgaDeposit(amount);
AccountActivityLedger Ledger = new AccountActivityLedger();
Ledger.AddAccountActivity(accID, "Deposit", amount, DateTime.Now);
MessageBox.Show("Amount deposited successfully!");
}
else
{
MessageBox.Show("Balance greater than limit!");
}
}
public void Withdraw(double amount, int accID)
{
UserDAO dao = new UserDAO();
DataTable dt = new DataTable();
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
BankGeneralAccount bg = new BankGeneralAccount();
dt = dao.GetData("Select AccountBalance from Account where AccountID=" + accID, connectionstring);
Balance = Convert.ToDouble(dt.Rows[0]["AccountBalance"]);
Balance = Balance - amount;
if (Balance > 0)
{
string myQuery = "Update Account set AccountBalance=" + Balance + " where AccountID=" + accID;
//UserDAO dao = new UserDAO();
bg.BgaWithdraw(amount);
dao.UpdateData(myQuery, connectionstring);
AccountActivityLedger Ledger = new AccountActivityLedger();
Ledger.AddAccountActivity(accID, "Withdraw", amount, DateTime.Now);
MessageBox.Show("Amount withdraw Successful!");
}
//string myQuery="up
else
{
MessageBox.Show("Your balance is too low!");
}
}
private void button1_Click(object sender, EventArgs e)
{
int res;
if(textBox1.Text.Length==0)
MessageBox.Show("Please enter the Customer ID");
if(textBox1.Text.Length!=4)
MessageBox.Show("Invalid Customer ID");
if(!int.TryParse(textBox1.Text,out res))
{
MessageBox.Show("Only Digits are allowed");
}
if(!(int.Parse(textBox1.Text)>=1233))
{
MessageBox.Show("Invalid Customer ID");
}
UserDAO user = new UserDAO();
DataTable dataTable = new DataTable ();
dataTable = user.GetData("use ApteanEdgeBank select * from customer where CustomerID=" + textBox1.Text,UserDAO.connectionString);
dataGridView1.DataSource = bindingSource1;
bindingSource1.DataSource = dataTable;
if (dataTable.Rows.Count == 0)
{
MessageBox.Show("No records Found");
}
}
private void button1_Click(object sender, EventArgs e)
{
int res;
if (textBox1.Text.Length == 0)
{
MessageBox.Show("Please enter the Customer ID");
}
if (textBox1.Text.Length != 4)
{
MessageBox.Show("Invalid Customer ID");
}
if (!int.TryParse(textBox1.Text, out res))
{
MessageBox.Show("Only Digits are allowed");
}
if (!(int.Parse(textBox1.Text) >= 1233))
{
MessageBox.Show("Invalid Customer ID");
}
UserDAO user = new UserDAO();
DataTable dataTable = new DataTable();
dataTable = user.GetData("use ApteanEdgeBank select * from customer where CustomerID=" + textBox1.Text, UserDAO.connectionString);
dataGridView1.DataSource = bindingSource1;
bindingSource1.DataSource = dataTable;
if (dataTable.Rows.Count == 0)
{
MessageBox.Show("No records Found");
}
}
public void GetAccountBalance()
{
UserDAO dao = new UserDAO();
DataTable dt = new DataTable();
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
dt = dao.GetData("select AccountBalance from BankGeneralAccount where AccountID=1000", connectionstring);
bgaBalance = Convert.ToDouble(dt.Rows[0]["AccountBalance"]);
}
public ListOfCustomers()
{
InitializeComponent();
dataGridView1.DataSource = bindingSource1; UserDAO uao = new UserDAO();
dataTable = uao.GetData("use ApteanEdgeBank select * from customer", UserDAO.connectionString);
bindingSource1.DataSource = dataTable;
}
private void refreshButton_Click(object sender, EventArgs e)
{
UserDAO uao = new UserDAO();
//DataTable dataTable = new DataTable();
dataTable = uao.GetData("use ApteanEdgeBank select * from customer", UserDAO.connectionString);
bindingSource1.DataSource = dataTable;
}
public void GetAccountBalance()
{
UserDAO dao = new UserDAO();
DataTable dt = new DataTable();
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
dt = dao.GetData("select AccountBalance from BankGeneralAccount where AccountID=1000", connectionstring);
bgaBalance = Convert.ToDouble(dt.Rows[0]["AccountBalance"]);
}
public void AddContactNumber(string ContactNumber)
{
DataTable temp = new DataTable();
UserDAO dataAccess = new UserDAO();
Int64? customerID=null;
string query = "use ApteanEdgeBank select * from Customer";
temp = dataAccess.GetData(query, UserDAO.connectionString);
int i = 0;
for (; i < (temp.Rows.Count); i++)
{
if (firstName == (string)temp.Rows[i]["FirstName"] && lastName == (string)temp.Rows[i]["LastName"] && middleName == (string)temp.Rows[i]["MiddleName"] && dateOfBirth.ToString() == Convert.ToString(temp.Rows[i]["DateOfBirth"]))
{ customerID = (Int64)temp.Rows[i]["CustomerID"]; break; }
else
continue;
}
if (customerID != null)
{
dataAccess.InsertData("use ApteanEdgeBank insert into CustomerContact (CustomerID) values(" + customerID + ")", UserDAO.connectionString);
}
else
{
System.Windows.Forms.MessageBox.Show("Fatal Error: Customer ID not generated ");
}
temp.Clear();
temp = dataAccess.GetData("use ApteanEdgeBank select * from CustomerContact",UserDAO.connectionString);
i = 0;
for (; i < (temp.Rows.Count); i++)
{
if (customerID == (Int64)temp.Rows[i]["CustomerID"])
{ ContactID = temp.Rows[i]["CustomerContactID"].ToString(); break; }
else
continue;
}
if (ContactID != null)
{
// System.Windows.Forms.MessageBox.Show(ContactID);
dataAccess.InsertData("use ApteanEdgeBank insert into CustomerContactDetails (CustomerContactID,CustomerContactNumber) values(" + ContactID + "," + ContactNumber + ")", UserDAO.connectionString);
}
else
System.Windows.Forms.MessageBox.Show("Fatal Error: Contact ID not generated");
}
public DataTable ReadAccountActivity(int accId)
{
UserDAO dao = new UserDAO();
string myQuery = "Select * from AccountActivityLedger where AccountID=" + accId;
DataTable activityTable = new DataTable();
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
activityTable = dao.GetData(myQuery, connectionstring);
return(activityTable);
}
private void ReOpenAccount_Load(object sender, EventArgs e)
{
dataTable = dataAccess.GetData(@"use ApteanEdgeBank select Customer.FirstName,CustomerAccount.CustomerID,Account.AccountID,Account.AccountType,AccountBalance,Account.DateOfOpening,DateOfClosing
from Account inner join CustomerAccount
on
Account.AccountID=CustomerAccount.AccountID
inner join Customer
on
Customer.CustomerID = CustomerAccount.CustomerID
where Account.DateOfClosing is not null", UserDAO.connectionString);
}
private DataTable loadTable()
{
dataTable = dataAccess.GetData(@"use ApteanEdgeBank select Customer.FirstName,CustomerAccount.CustomerID,Account.AccountID,Account.AccountType,AccountBalance,Account.DateOfOpening,DateOfClosing
from Account inner join CustomerAccount
on
Account.AccountID=CustomerAccount.AccountID
inner join Customer
on
Customer.CustomerID = CustomerAccount.CustomerID
where Account.DateOfClosing is null", UserDAO.connectionString);
return(dataTable);
}
private void button3_Click(object sender, EventArgs e)
{
UserDAO user = new UserDAO();
DataTable dataTable = new DataTable();
dataTable = user.GetData("use ApteanEdgeBank select * from customer where LastName=" + "'" + textBox3.Text + "'", UserDAO.connectionString);
dataGridView1.DataSource = bindingSource1;
bindingSource1.DataSource = dataTable;
if (dataTable.Rows.Count == 0)
{
MessageBox.Show("No records Found");
}
}
//methods
public void BgaDeposit( double amt)
{
UserDAO dao = new UserDAO();
DataTable dt = new DataTable();
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
dt = dao.GetData("Select AccountBalance from BankGeneralAccount where AccountID=1000", connectionstring);
bgaBalance = Convert.ToDouble(dt.Rows[0]["AccountBalance"]);
bgaBalance = bgaBalance + amt;
string myQuery = "Update BankGeneralAccount set AccountBalance=" + bgaBalance + " where AccountID= 1000";
//UserDAO dao = new UserDAO();
dao.UpdateData(myQuery, connectionstring);
}
private void button3_Click(object sender, EventArgs e)
{
UserDAO user = new UserDAO();
DataTable dataTable = new DataTable();
dataTable = user.GetData("use ApteanEdgeBank select * from customer where LastName=" + "'" + textBox3.Text + "'", UserDAO.connectionString);
dataGridView1.DataSource = bindingSource1;
bindingSource1.DataSource = dataTable;
if (dataTable.Rows.Count == 0)
{
MessageBox.Show("No records Found");
}
}
public bool CheckAccountExistence(int accID)
{
UserDAO dao = new UserDAO();
DataTable accTable = new DataTable();
string myQuery = "select * from Account where AccountID=" + accID;
string connectionstring="Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
accTable = dao.GetData(myQuery, connectionstring);
if (accTable.Rows.Count > 0)
{
return true;
}
return false;
}
//methods
public void BgaDeposit(double amt)
{
UserDAO dao = new UserDAO();
DataTable dt = new DataTable();
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
dt = dao.GetData("Select AccountBalance from BankGeneralAccount where AccountID=1000", connectionstring);
bgaBalance = Convert.ToDouble(dt.Rows[0]["AccountBalance"]);
bgaBalance = bgaBalance + amt;
string myQuery = "Update BankGeneralAccount set AccountBalance=" + bgaBalance + " where AccountID= 1000";
//UserDAO dao = new UserDAO();
dao.UpdateData(myQuery, connectionstring);
}
private void ListAccountDetails_Load(object sender, EventArgs e)
{
UserDAO dataAccess = new UserDAO();
DataTable dataTable = new DataTable();
dataTable = dataAccess.GetData(@"use ApteanEdgeBank select Customer.FirstName,CustomerAccount.CustomerID,Account.AccountID,Account.AccountType,AccountBalance,Account.DateOfOpening,DateOfClosing
from Account inner join CustomerAccount
on
Account.AccountID=CustomerAccount.AccountID
inner join Customer
on
Customer.CustomerID = CustomerAccount.CustomerID", UserDAO.connectionString);
dataGridView1.DataSource = bindingSource1;
bindingSource1.DataSource = dataTable;
}
public bool CheckAccountExistence(int accID)
{
UserDAO dao = new UserDAO();
DataTable accTable = new DataTable();
string myQuery = "select * from Account where AccountID=" + accID;
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
accTable = dao.GetData(myQuery, connectionstring);
if (accTable.Rows.Count > 0)
{
return(true);
}
return(false);
}
public bool CheckCustomerByID(int customerId)
{
UserDAO dao = new UserDAO();
DataTable customerTable = new DataTable();
string myQuery = "select * from Customer where CustomerID=" + customerId;
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
customerTable = dao.GetData(myQuery, connectionstring);
if (customerTable.Rows.Count > 0)
{
return(true);
}
return(false);
}
private void ListAccountDetails_Load(object sender, EventArgs e)
{
UserDAO dataAccess = new UserDAO();
DataTable dataTable = new DataTable();
dataTable = dataAccess.GetData(@"use ApteanEdgeBank select Customer.FirstName,CustomerAccount.CustomerID,Account.AccountID,Account.AccountType,AccountBalance,Account.DateOfOpening,DateOfClosing
from Account inner join CustomerAccount
on
Account.AccountID=CustomerAccount.AccountID
inner join Customer
on
Customer.CustomerID = CustomerAccount.CustomerID", UserDAO.connectionString);
dataGridView1.DataSource = bindingSource1;
bindingSource1.DataSource = dataTable;
}
public double CalculateAccountBalance(int accId)
{
UserDAO dao = new UserDAO();
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
//string myQuery = "Select * from AccountActivityLedger where AccountID=" + accId;
string getCurrentBalance = "Select * from Account where AccountID=" + accId;
double currentBalance=0.0;
DataTable dt = new DataTable();
/*double sum=currentBalance;
DataTable activityLog = new DataTable();
double accountBalance = 0.0;*/
if (CheckAccountExistence(accId))
{
dt = dao.GetData(getCurrentBalance, connectionstring);
// dt.Columns.Add("Closing Balance", typeof(Double));
currentBalance = Convert.ToDouble(dt.Rows[0]["AccountBalance"]);
//dt.Rows[0]["Closing Balance"] = currentBalance;
/* //sumDeposits = currentBalance;
activityLog = dao.GetData(myQuery, connectionstring);
foreach (DataRow row in activityLog.Rows)
{
if (Convert.ToString(row["Activitycode"]) == "Deposit")
{
sum=sum+Convert.ToDouble(row["Amount"]);
}
else
{
sum=sum-Convert.ToDouble(row["Amount"]);
}
}
accountBalance = sum;
string updateNewBalance = "Update Account set AccountBalance=" + accountBalance + " where AccountID=" + accId;
dao.UpdateData(updateNewBalance, connectionstring);
return accountBalance;*/
return currentBalance;
}
else
{
MessageBox.Show("Account Does not exist!");
}
return 0;
}
public double CalculateAccountBalance(int accId)
{
UserDAO dao = new UserDAO();
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
//string myQuery = "Select * from AccountActivityLedger where AccountID=" + accId;
string getCurrentBalance = "Select * from Account where AccountID=" + accId;
double currentBalance = 0.0;
DataTable dt = new DataTable();
dt = dao.GetData(getCurrentBalance, connectionstring);
currentBalance = Convert.ToDouble(dt.Rows[0]["AccountBalance"]);
/*double sum=currentBalance;
* DataTable activityLog = new DataTable();
*
* double accountBalance = 0.0;*/
if (CheckAccountExistence(accId))
{
/* //sumDeposits = currentBalance;
* activityLog = dao.GetData(myQuery, connectionstring);
* foreach (DataRow row in activityLog.Rows)
* {
* if (Convert.ToString(row["Activitycode"]) == "Deposit")
* {
* sum=sum+Convert.ToDouble(row["Amount"]);
* }
* else
* {
* sum=sum-Convert.ToDouble(row["Amount"]);
* }
* }
*
* accountBalance = sum;
* string updateNewBalance = "Update Account set AccountBalance=" + accountBalance + " where AccountID=" + accId;
* dao.UpdateData(updateNewBalance, connectionstring);
* return accountBalance;*/
return(currentBalance);
}
else
{
MessageBox.Show("Account Does not exist!");
}
return(0);
}
public void AddAccountActivity(int accID, string transType, double amount, DateTime transDate)
{
UserDAO dao = new UserDAO();
string queryFetch = "Select * from Account where AccountID=" + accID;
DataTable dt = new DataTable();
dt = dao.GetData(queryFetch, UserDAO.connectionString);
double closingBalance=Convert.ToDouble(dt.Rows[0]["AccountBalance"]);
string myQuery = "insert into AccountActivityLedger values(" + accID + "," + "'" + transDate + "'" + "," + "'" + transType + "'" + "," + amount + ","+closingBalance+")";
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
// if (CheckAccountExistence(accID))
//{
dao.InsertData(myQuery, connectionstring);
//}
/* else
{
MessageBox.Show("Account Does not exist!");
}*/
}
public ListOfCustomers(bool flag) ///true for sort by Name and false for sort by customerID
{
if (flag)
{
InitializeComponent();
dataGridView1.DataSource = bindingSource1; UserDAO uao = new UserDAO();
//DataTable dataTable = new DataTable();
dataTable = uao.GetData("use ApteanEdgeBank select * from customer order by FirstName", UserDAO.connectionString);
bindingSource1.DataSource = dataTable;
}
else
{
InitializeComponent();
dataGridView1.DataSource = bindingSource1; UserDAO uao = new UserDAO();
//DataTable dataTable = new DataTable();
dataTable = uao.GetData("use ApteanEdgeBank select * from customer order by CustomerID", UserDAO.connectionString);
bindingSource1.DataSource = dataTable;
}
}
public override void Deposit(double amount, int accId)
{
UserDAO dao = new UserDAO();
DataTable dt = new DataTable();
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
BankGeneralAccount bg = new BankGeneralAccount();
dt = dao.GetData("Select AccountBalance from Account where AccountID=" + accId, connectionstring);
Balance = Convert.ToDouble(dt.Rows[0]["AccountBalance"]);
Balance = Balance + amount;
string myQuery = "Update Account set AccountBalance=" + Balance + " where AccountID=" + accId;
//UserDAO dao = new UserDAO();
dao.UpdateData(myQuery, connectionstring);
bg.BgaDeposit(amount);
AccountActivityLedger Ledger = new AccountActivityLedger();
Ledger.AddAccountActivity(accId, "Deposit", amount, DateTime.Now);
}
public void BgaWithdraw(double amt)
{
UserDAO dao = new UserDAO();
DataTable dt = new DataTable();
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
dt = dao.GetData("Select AccountBalance from BankGeneralAccount where AccountID=1000", connectionstring);
bgaBalance = Convert.ToDouble(dt.Rows[0]["AccountBalance"]);
bgaBalance = bgaBalance - amt;
if (bgaBalance > 0)
{
string myQuery = "Update BankGeneralAccount set AccountBalance=" + bgaBalance + " where AccountID= 1000";
//UserDAO dao = new UserDAO();
dao.UpdateData(myQuery, connectionstring);
}
//string myQuery="up
else
System.Windows.Forms.MessageBox.Show("Insufficient Balance in Bank General Account");
}
private void button2_Click(object sender, EventArgs e)
{
if (CustomerAvailabilityCheck())
{
CustomerID = textBox1.Text;
initialBalance = textBox2.Text;
MessageBox.Show("Account Type is " + AccountType + "For Customer ID " + CustomerID + " With initial Balance " + initialBalance);
UserDAO udao = new UserDAO();
DataTable dataTable = new DataTable();
udao.InsertData("use ApteanEdgeBank insert into Account (AccountType,AccountBalance,DateOfOpening,DateOfClosing) values(" + AccountType + "," + initialBalance + "," + "cast(getdate() as date),null)", UserDAO.connectionString);
dataTable = udao.GetData("use ApteanEdgeBank select max(AccountID) as max from Account", UserDAO.connectionString);
AccountID = Convert.ToString(dataTable.Rows[0]["max"]);
udao.InsertData("use ApteanEdgeBank insert into CustomerAccount values(" + CustomerID + "," + AccountID + ")", UserDAO.connectionString);
}
else
{
MessageBox.Show("Customer Not Fount, Please Create Customer First");
}
}
public override int Create(string accType, DateTime open, double balance)
{
/* accountID=ID;
* dateOpened=open;
* Balance=balance;
* Console.WriteLine("Chequing Acoount:");
* Console.WriteLine("Account ID:{0}\nDate Opened:{1}\nBalance:{2}", accountID, dateOpened, Balance);*/
string myQuery = "insert into Account values(" + accType + "," + balance + "," + "'" + open + "'" + "," + "null" + ")";
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
BankGeneralAccount bg = new BankGeneralAccount();
UserDAO dao = new UserDAO();
dao.InsertData(myQuery, connectionstring);
bg.BgaDeposit(balance);
DataTable dataTable = dao.GetData("use ApteanEdgeBank select max(AccountID) as max from Account", UserDAO.connectionString);
int AccountID = Convert.ToInt32(dataTable.Rows[0]["max"]);
return(AccountID);
//dao.InsertData("use ApteanEdgeBank insert into CustomerAccount values(" + CustomerID + "," + AccountID + ")", UserDAO.connectionString);
}
public void Withdraw(double amount, int accID)
{
BankGeneralAccount bg = new BankGeneralAccount();
UserDAO dao = new UserDAO();
DataTable dt = new DataTable();
string connectionstring = "Data Source=WS003LT1550PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
dt = dao.GetData("Select AccountBalance from Account where AccountID=" + accID, connectionstring);
Balance = Convert.ToDouble(dt.Rows[0]["AccountBalance"]);
Balance = Balance - amount;
if (Balance > 0)
{
string myQuery = "Update Account set AccountBalance=" + Balance + " where AccountID=" + accID;
//UserDAO dao = new UserDAO();
dao.UpdateData(myQuery, connectionstring);
bg.BgaWithdraw(amount);
}
//string myQuery="up
else
System.Windows.Forms.MessageBox.Show("Your balance is too low!");
}
public bool DoCustomerExists(string Fname, string Lname, string dateOfBirth) // returns true if customer already exists in the database else returns false
{
DataTable temp = new DataTable();
UserDAO dataAccess = new UserDAO();
string query = "use ApteanEdgeBank select * from Customer";
temp = dataAccess.GetData(query, UserDAO.connectionString);
int i = 0;
for (; i < (temp.Rows.Count); i++)
{
if (Fname == (string)temp.Rows[i]["FirstName"] && Lname == (string)temp.Rows[i]["LastName"] && dateOfBirth == Convert.ToString(temp.Rows[i]["DateOfBirth"]))
{
return(true);
}
else
{
continue;
}
}
return(false);
}
public void BgaWithdraw(double amt)
{
UserDAO dao = new UserDAO();
DataTable dt = new DataTable();
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
dt = dao.GetData("Select AccountBalance from BankGeneralAccount where AccountID=1000", connectionstring);
bgaBalance = Convert.ToDouble(dt.Rows[0]["AccountBalance"]);
bgaBalance = bgaBalance - amt;
if (bgaBalance > 0)
{
string myQuery = "Update BankGeneralAccount set AccountBalance=" + bgaBalance + " where AccountID= 1000";
//UserDAO dao = new UserDAO();
dao.UpdateData(myQuery, connectionstring);
}
//string myQuery="up
else
{
System.Windows.Forms.MessageBox.Show("Insufficient Balance in Bank General Account");
}
}
public bool DoesTFSAccountExist(int customerID)
{
UserDAO dao = new UserDAO();
DataTable customerAccounts = new DataTable();
int TFSAccount = 0;
string query = "Select * from Account where AccountID in (select AccountID from CustomerAccount where CustomerID=" + customerID + ")";
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
customerAccounts = dao.GetData(query, connectionstring);
foreach (DataRow row in customerAccounts.Rows)
{
if (Convert.ToString(row["AccountType"]) == "TFS")
{
TFSAccount++;
}
}
if (TFSAccount == 0) //if there is no TFS account for customer
{
return(false); //there is no TFS for this customer
}
return(true);//this customer already has a TFS account
}
public override int Create(string accType, DateTime open, double balance)
{
if (balance <= Limit)
{
dateOpened = open;
Balance = balance;
string myQuery = "insert into Account values(" + accType + "," + balance + "," + "'" + open + "'" + "," + "null" + ")";
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
BankGeneralAccount bg = new BankGeneralAccount();
UserDAO dao = new UserDAO();
dao.InsertData(myQuery, connectionstring);
bg.BgaDeposit(balance);
DataTable dataTable = dao.GetData("use ApteanEdgeBank select max(AccountID) as max from Account", UserDAO.connectionString);
int AccountID = Convert.ToInt32(dataTable.Rows[0]["max"]);
return(AccountID);
}
else
{
MessageBox.Show("Balance is greater than limit!");
return(0);
}
}
public static void Close(int accId)
{
UserDAO dao=new UserDAO();
DataTable dt=new DataTable();
BankGeneralAccount bg = new BankGeneralAccount();
string queryString="Select * from Account where AccountID="+accId;
try
{
/*Balance = 0;
dateClosed = DateTime.Now;
Console.WriteLine("Account closed on {0}", dateClosed.ToShortDateString());*/
dt = dao.GetData(queryString, UserDAO.connectionString);
if (Convert.ToDouble(dt.Rows[0]["AccountBalance"]) != 0.0)
{
DialogResult result= MessageBox.Show("Your account balance is greater than 0! The balance must be 0 to close an account. Would you like to clear your account balance?","Account Balance is not 0!",MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
//set closing date for the account. Deduct the remaining balance from bank general account and set the current balance to 0
dao.InsertData(@"use ApteanEdgeBank update Account
set DateOfClosing= cast(getdate() as date)
where AccountID =" + accId, UserDAO.connectionString);
bg.BgaWithdraw(Convert.ToDouble(dt.Rows[0]["AccountBalance"]));
dao.UpdateData("update Account set AccountBalance=0 where AccountID=" + accId, UserDAO.connectionString);
}
}
else
{
dao.InsertData(@"use ApteanEdgeBank update Account
set DateOfClosing= cast(getdate() as date)
where AccountID =" + accId, UserDAO.connectionString);
}
}
catch (InvalidOperationException e)
{
MessageBox.Show("Account closure failed!");
}
}
public override void Deposit(double amount,int accID)
{
UserDAO dao = new UserDAO();
DataTable dt = new DataTable();
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
BankGeneralAccount bg = new BankGeneralAccount();
dt = dao.GetData("Select AccountBalance from Account where AccountID=" + accID, connectionstring);
Balance = Convert.ToDouble(dt.Rows[0]["AccountBalance"]);
if ((Balance + amount) <= 5000)
{
Balance = Balance + amount;
string myQuery = "Update Account set AccountBalance=" + Balance + " where AccountID="+accID;
//UserDAO dao = new UserDAO();
dao.UpdateData(myQuery, connectionstring);
bg.BgaDeposit(amount);
AccountActivityLedger Ledger = new AccountActivityLedger();
Ledger.AddAccountActivity(accID, "Deposit", amount, DateTime.Now);
MessageBox.Show("Amount deposited successfully!");
}
else
{
MessageBox.Show("Balance greater than limit!");
}
}
public bool CheckCustomerByID(int customerId)
{
UserDAO dao = new UserDAO();
DataTable customerTable = new DataTable();
string myQuery = "select * from Customer where CustomerID=" + customerId;
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
customerTable = dao.GetData(myQuery, connectionstring);
if (customerTable.Rows.Count > 0)
{
return true;
}
return false;
}
public override void Deposit(double amount,int accId)
{
UserDAO dao=new UserDAO();
DataTable dt=new DataTable();
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
BankGeneralAccount bg = new BankGeneralAccount();
dt=dao.GetData("Select AccountBalance from Account where AccountID="+accId,connectionstring);
Balance=Convert.ToDouble(dt.Rows[0]["AccountBalance"]);
Balance=Balance+amount;
string myQuery = "Update Account set AccountBalance=" + Balance + " where AccountID="+accId;
//UserDAO dao = new UserDAO();
dao.UpdateData(myQuery, connectionstring);
bg.BgaDeposit(amount);
AccountActivityLedger Ledger = new AccountActivityLedger();
Ledger.AddAccountActivity(accId, "Deposit", amount, DateTime.Now);
}
public DataTable ReadAccountActivity(int accId)
{
UserDAO dao = new UserDAO();
string myQuery = "Select * from AccountActivityLedger where AccountID="+accId;
DataTable activityTable = new DataTable();
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
activityTable = dao.GetData(myQuery, connectionstring);
return activityTable;
}
public override int Create(string accType,DateTime open,double balance)
{
if (balance <= Limit)
{
dateOpened = open;
Balance = balance;
string myQuery = "insert into Account values(" + accType + "," + balance + "," + "'" + open + "'" + "," + "null" + ")";
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
BankGeneralAccount bg = new BankGeneralAccount();
UserDAO dao = new UserDAO();
dao.InsertData(myQuery, connectionstring);
bg.BgaDeposit(balance);
DataTable dataTable = dao.GetData("use ApteanEdgeBank select max(AccountID) as max from Account", UserDAO.connectionString);
int AccountID = Convert.ToInt32(dataTable.Rows[0]["max"]);
return AccountID;
}
else
{
MessageBox.Show("Balance is greater than limit!");
return 0;
}
}
public bool DoesTFSAccountExist(int customerID)
{
UserDAO dao = new UserDAO();
DataTable customerAccounts = new DataTable();
int TFSAccount = 0;
string query = "Select * from Account where AccountID in (select AccountID from CustomerAccount where CustomerID=" + customerID + ")";
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
customerAccounts = dao.GetData(query, connectionstring);
foreach (DataRow row in customerAccounts.Rows)
{
if (Convert.ToString(row["AccountType"]) == "TFS")
{
TFSAccount++;
}
}
if (TFSAccount == 0)//if there is no TFS account for customer
{
return false;//there is no TFS for this customer
}
return true;//this customer already has a TFS account
}
public override int Create(string accType,DateTime open,double balance)
{
/* accountID=ID;
dateOpened=open;
Balance=balance;
Console.WriteLine("Chequing Acoount:");
Console.WriteLine("Account ID:{0}\nDate Opened:{1}\nBalance:{2}", accountID, dateOpened, Balance);*/
string myQuery = "insert into Account values("+accType+","+balance+","+"'"+open+"'"+","+"null"+")";
string connectionstring = "Data Source=WS003LT1553PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
BankGeneralAccount bg = new BankGeneralAccount();
UserDAO dao = new UserDAO();
dao.InsertData(myQuery, connectionstring);
bg.BgaDeposit(balance);
DataTable dataTable = dao.GetData("use ApteanEdgeBank select max(AccountID) as max from Account", UserDAO.connectionString);
int AccountID = Convert.ToInt32(dataTable.Rows[0]["max"]);
return AccountID;
//dao.InsertData("use ApteanEdgeBank insert into CustomerAccount values(" + CustomerID + "," + AccountID + ")", UserDAO.connectionString);
}
public override void Deposit(double amount, int accID)
{
BankGeneralAccount bg = new BankGeneralAccount();
UserDAO dao = new UserDAO();
DataTable dt = new DataTable();
string connectionstring = "Data Source=WS003LT1550PRD;Initial Catalog=ApteanEdgeBank;User=sa;Password=abc-123";
dt = dao.GetData("Select AccountBalance from Account where AccountID=" + accID, connectionstring);
Balance = Convert.ToDouble(dt.Rows[0]["AccountBalance"]);
if ((Balance + amount) < 5000)
{
Balance = Balance + amount;
string myQuery = "Update Account set AccountBalance=" + Balance + " where AccountID=" + accID;
//UserDAO dao = new UserDAO();
dao.UpdateData(myQuery, connectionstring);
bg.BgaDeposit(amount);
}
else
{
System.Windows.Forms.MessageBox.Show("Balance greater than limit!");
}
}
// returns true if customer already exists in the database else returns false
public bool DoCustomerExists(string Fname, string Lname, string dateOfBirth)
{
DataTable temp = new DataTable();
UserDAO dataAccess = new UserDAO();
string query = "use ApteanEdgeBank select * from Customer";
temp = dataAccess.GetData(query, UserDAO.connectionString);
int i = 0;
for (; i < (temp.Rows.Count); i++)
{
if (Fname == (string)temp.Rows[i]["FirstName"] && Lname == (string)temp.Rows[i]["LastName"] && dateOfBirth == Convert.ToString(temp.Rows[i]["DateOfBirth"]))
return true;
else
continue;
}
return false;
}