private void Balance_Click(object sender, EventArgs e)
{
dbConnect = new DBconnect();
MySqlDataReader myReader;
string query = "SELECT * ,(SELECT SUM(Deposit)+SUM(Withdraw)+SUM(MoneySent)+SUM(MoneyReceived) FROM transactions Lin WHERE Lin.AccountNumber = @AccountNumber) as Balance FROM transactions Lout ORDER BY AccountNumber";
//open connection
if (this.dbConnect.OpenConnection() == true)
{
//create command and assign the query and connection from the constructor
MySqlCommand cmd = new MySqlCommand(query, dbConnect.connection);
txtAccountNumber.Text = Class1.AccountNumber;
cmd.Parameters.AddWithValue("@AccountNumber", txtAccountNumber.Text);
//Execute command
cmd.ExecuteNonQuery();
myReader = cmd.ExecuteReader();
myReader.Read();
String x = myReader[7].ToString();
MessageBox.Show("Your Balance is R" + x);
//close connection
this.dbConnect.CloseConnection();
}
}
public void WithdrawMoney()
{
dbConnect = new DBconnect();
string query = "INSERT INTO transactions (AccountNumber,Deposit , Withdraw, MoneySent, MoneyReceived) VALUES (@AccountNumber, 0, @Withdraw, 0, 0)";
//open connection
if (this.dbConnect.OpenConnection() == true)
{
if (string.IsNullOrWhiteSpace(txtWithdraw.Text))
{
MessageBox.Show("Withdrawl feild empty");
}
else
{
//create command and assign the query and connection from the constructor
MySqlCommand cmd = new MySqlCommand(query, dbConnect.connection);
txtAccountNumber.Text = Class1.AccountNumber;
cmd.Parameters.AddWithValue("@AccountNumber", txtAccountNumber.Text);
cmd.Parameters.AddWithValue("@Withdraw", "-" + txtWithdraw.Text);
//Execute command
cmd.ExecuteNonQuery();
MessageBox.Show("Withdrawl Made!!");
//close connection
this.dbConnect.CloseConnection();
}
}
}
public List <string>[] select()
{
dbConnect = new DBconnect();
string query = "SELECT *FROM userinfo WHERE EXISTS(SELECT * FROM transactions WHERE userinfo.AccountNumber = transactions.AccountNumber)";
//Create a list to store the result in th columns
List <string>[] list = new List <string> [9];
list[0] = new List <string>();
list[1] = new List <string>();
list[2] = new List <string>();
list[3] = new List <string>();
list[4] = new List <string>();
list[5] = new List <string>();
list[6] = new List <string>();
list[7] = new List <string>();
list[8] = new List <string>();
//Open connection
if (this.dbConnect.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, dbConnect.connection);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
list[0].Add(dataReader["Name"] + "");
list[1].Add(dataReader["Surname"] + "");
list[2].Add(dataReader["DOB"] + "");
list[3].Add(dataReader["IDNumber"] + "");
list[4].Add(dataReader["Address"] + "");
list[5].Add(dataReader["Town"] + "");
list[6].Add(dataReader["City"] + "");
list[7].Add(dataReader["CellNumber"] + "");
list[8].Add(dataReader["AccountNumber"] + "");
}
//close Data Reader
dataReader.Close();
//close Connection
this.dbConnect.CloseConnection();
//return list to be displayed
return(list);
}
else
{
return(list);
}
}
public List <string>[] select()
{
dbConnect = new DBconnect();
string query = "SELECT * FROM transactions WHERE AccountNumber = @AccountNumber";
txtAccountNumber.Text = Class1.AccountNumber;
//Create a list to store the result
List <string>[] list = new List <string> [6];
list[0] = new List <string>();
list[1] = new List <string>();
list[2] = new List <string>();
list[3] = new List <string>();
list[4] = new List <string>();
//Open connection
if (this.dbConnect.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, dbConnect.connection);
cmd.Parameters.AddWithValue("@AccountNumber", txtAccountNumber.Text);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
list[0].Add(dataReader["Deposit"] + "");
list[1].Add(dataReader["Withdraw"] + "");
list[2].Add(dataReader["MoneySent"] + "");
list[3].Add(dataReader["MoneyReceived"] + "");
list[4].Add(dataReader["DateOfTransaction"] + "");
}
//close Data Reader
dataReader.Close();
//close Connection
this.dbConnect.CloseConnection();
//return list to be displayed
return(list);
}
else
{
return(list);
}
}
public void RegisterCustomer()
{
dbConnect = new DBconnect();
string GenerateAccountNumber = String.Format("{0:d16}", (DateTime.Now.Ticks) % 7000000000000);
string query = "INSERT INTO userinfo (Name, Surname, DOB, IDNumber, Address, Town, City, CellNumber, AccountNumber , Pin) VALUES (@Name, @Surname, @DOB, @IDNumber, @Address, @Town, @City, @CellNumber, @AccountNumber , @Pin)";
//open connection
if (this.dbConnect.OpenConnection() == true)
{
if (string.IsNullOrWhiteSpace(txtName.Text) || string.IsNullOrWhiteSpace(txtSurname.Text) || string.IsNullOrWhiteSpace(txtDOB.Text) || string.IsNullOrWhiteSpace(txtIDNum.Text) || string.IsNullOrWhiteSpace(txtTown.Text) || string.IsNullOrWhiteSpace(txtCity.Text) || string.IsNullOrWhiteSpace(txtCellNum.Text) || string.IsNullOrWhiteSpace(txtPin.Text) || string.IsNullOrWhiteSpace(txtReTypePin.Text))
{
MessageBox.Show("Fill in all fields");
}
else
{
if (txtPin.Text == txtReTypePin.Text)
{
//create command and assign the query and connection from the constructor
MySqlCommand cmd = new MySqlCommand(query, dbConnect.connection);
cmd.Parameters.AddWithValue("@Name", txtName.Text);
cmd.Parameters.AddWithValue("@Surname", txtSurname.Text);
cmd.Parameters.AddWithValue("@DOB", txtDOB.Text);
cmd.Parameters.AddWithValue("@IDNumber", txtIDNum.Text);
cmd.Parameters.AddWithValue("@Address", txtAddress.Text);
cmd.Parameters.AddWithValue("@Town", txtTown.Text);
cmd.Parameters.AddWithValue("@City", txtCity.Text);
cmd.Parameters.AddWithValue("@CellNumber", txtCellNum.Text);
cmd.Parameters.AddWithValue("@AccountNumber", GenerateAccountNumber);
cmd.Parameters.AddWithValue("@Pin", txtPin.Text);
//Execute command
cmd.ExecuteNonQuery();
MessageBox.Show("Customer Created!!");
//close connection
this.dbConnect.CloseConnection();
}
else
{
MessageBox.Show("Pins do not match");
}
}
}
}
public void InsertEmployee()
{
dbConnect = new DBconnect();
string query = "INSERT INTO employee (Name, Surname, Username, Password) VALUES (@Name, @Surname, @Username, @Password)";
//open connection
if (this.dbConnect.OpenConnection() == true)
{
if (string.IsNullOrWhiteSpace(txtName.Text) || string.IsNullOrWhiteSpace(txtSurname.Text) || string.IsNullOrWhiteSpace(txtUsername.Text) || string.IsNullOrWhiteSpace(txtPassword.Text) || string.IsNullOrWhiteSpace(txtReTypePass.Text))
{
MessageBox.Show("Fill in all fields");
}
else
{
//create command and assign the query and connection from the constructor
MySqlCommand cmd = new MySqlCommand(query, dbConnect.connection);
cmd.Parameters.AddWithValue("@Name", txtName.Text);
cmd.Parameters.AddWithValue("@Surname", txtSurname.Text);
cmd.Parameters.AddWithValue("@Username", txtUsername.Text);
cmd.Parameters.AddWithValue("@Password", txtPassword.Text);
if (txtPassword.Text == txtReTypePass.Text)
{
//Execute command
cmd.ExecuteNonQuery();
MessageBox.Show("User Added!!");
//close connection
this.dbConnect.CloseConnection();
}
else
{
MessageBox.Show("Passwords do not match");
}
}
}
}
public CustomerLogin()
{
InitializeComponent();
dbConnect = new DBconnect();
dbConnect.Create();
}
public AdminLogin()
{
InitializeComponent();
dbConnect = new DBconnect();
dbConnect.Create();
}
