public object FindByUsernameAndPassword(string username, string password)
{
var _connection = ConnectionHelper.GetConnection();
var sqlQuery = "select * from SHBAccount where Username = @username and Password = @password";
var cmd = new MySqlCommand(sqlQuery, _connection);
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@password", password);
var result = cmd.ExecuteReader();
if (result.Read())
{
var account = new SHBAccount
{
Username = result.GetString("Username"),
AccountNumber = result.GetString("AccountNumber"),
Balance = result.GetDouble("Balance"),
Password = result.GetString("Password"),
CreatedAtMLS = result.GetDateTime("CreatedAtMLS"),
UpdatedAtMLS = result.GetDateTime("UpdatedAtMLS")
};
_connection.Close();
return(account);
}
_connection.Close();
return(null);
}
public object GetAccountWithAccountNumber(string accountNumber)
{
var connection = ConnectionHelper.GetConnection();
var queryString = "select * from `shbaccount` where AccountNumber = @accountNumber";
var cmd = new MySqlCommand(queryString, connection);
cmd.Parameters.AddWithValue("@AccountNumber", accountNumber);
var reader = cmd.ExecuteReader();
SHBAccount shbAccount = null;
if (reader.Read())
{
var username = reader.GetString("Username");
var password = reader.GetString("Password");
var accountNumber2 = reader.GetString("AccountNumber");
var balance = reader.GetDouble("Balance");
var createdAt = reader.GetDateTime("CreatedAtMLS");
var updatedAt = reader.GetDateTime("UpdatedAtMLS");
shbAccount = new SHBAccount(accountNumber2, username, password, balance, createdAt, updatedAt);
}
reader.Close();
connection.Close();
return(shbAccount);
}
public SHBAccount FindByUsernameAndPassword(string username, string password)
{
var cmd = new MySqlCommand("select * from account where username = @username And password = @password",
ConnectionHelper.OpenConnection());
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@password", password);
SHBAccount shbAccount = null;
var reader = cmd.ExecuteReader();
if (reader.Read())
{
shbAccount = new SHBAccount
{
Username = reader.GetString("username"),
Password = reader.GetString("password"),
Balance = reader.GetDouble("balance")
};
}
ConnectionHelper.CloseConnection();
return(shbAccount);
}
{ // Bình thường không làm theo cách này,
// phải mã hoá mật khẩu, kiểm tra tài khoản theo username sau đó so sánh mật khẩu sau khi mã hoá.
public SHBAccount FindByUsernameAndPassword(string username, string password)
{
// Tạo connection đến db, lấy ra trong bảng shb account những tài khoản có username, password trùng.
var cmd = new MySqlCommand(
"select * from SHBAccount where username = @username and password = @password",
ConnectionHelper.GetConnection());
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@password", password);
// Tạo ra một đối tượng của lớp shbAccount.
SHBAccount shbAccount = null;
// Đóng Connection và trả về đối tượng này.
var dataReader = cmd.ExecuteReader();
if (dataReader.Read())
{
shbAccount = new SHBAccount
{
AccountNumber = dataReader.GetString("accountNumber"),
Username = dataReader.GetString("username"),
Password = dataReader.GetString("password"),
Balance = dataReader.GetDouble("balance")
};
}
ConnectionHelper.CloseConnection();
// Trong trường hợp không tìm thấy tài khoản thì trả về null.
return(shbAccount);
}
public void Register()
{
Console.WriteLine("Vui lòng nhập đầy đủ các thông tin bên dưới.");
Console.WriteLine("Tài khoản đăng nhập: ");
var username = Console.ReadLine();
Console.WriteLine("Mật khẩu: ");
var password = Console.ReadLine();
Console.WriteLine("Nhập lại mật khẩu: ");
var cpassword = Console.ReadLine();
Console.WriteLine("Số dư trong tài khoản: ");
var balance = Utility.GetUnsignedDecimalNumber();
SHBAccount shbAccount = new SHBAccount(username, password, balance);
// validate trước khi save.
if (model.Save(shbAccount))
{
Console.WriteLine(
"Đăng ký tài khoản thành công.");
}
Console.WriteLine("Ấn enter để tiếp tục.");
Console.ReadLine();
}
public bool Save(SHBAccount shbAccount)
{
var queryString = "insert into `shbaccount` (accountNumber, username, password, balance)" +
" values (@accountNumber, @username, @password, @balance)";
var cmd = new MySqlCommand(queryString, ConnectionHelper.GetConnection());
cmd.Parameters.AddWithValue("@accountNumber", shbAccount.AccountNumber);
cmd.Parameters.AddWithValue("@username", shbAccount.UserName);
cmd.Parameters.AddWithValue("@password", shbAccount.Password);
cmd.Parameters.AddWithValue("@balance", shbAccount.Balance);
var result = cmd.ExecuteNonQuery();
ConnectionHelper.CloseConnection();
return(result == 1);
}
private static void GenerateTransactionMenu(GiaoDich giaoDich)
{
while (true)
{
Console.WriteLine("Please select the transaction type.");
Console.WriteLine("==================================");
Console.WriteLine("1. Withdraw.");
Console.WriteLine("2. Deposit.");
Console.WriteLine("3. Transfer.");
Console.WriteLine("4. Exit.");
Console.WriteLine("===================================");
Console.WriteLine("Please enter your choice: ");
var choice1 = int.Parse(Console.ReadLine());
switch (choice1)
{
case 1:
giaoDich.Withdraw();
break;
case 2:
giaoDich.Deposit();
break;
case 3:
giaoDich.Transfer();
break;
case 4:
break;
default:
Console.WriteLine("Wrong choice, please try again!");
break;
}
if (choice1 == 4)
{
Console.WriteLine("Bye! see you again!");
currentLoggedInAccount = null;
currentLoggedInAddress = null;
break;
}
}
}
public SHBAccount GetAccountByUsername(string username)
{
var queryString = "select * from `blkaccount` where `username` = @username";
var cmd = new MySqlCommand(queryString, ConnectionHelper.GetConnection());
cmd.Parameters.AddWithValue("@username", username);
var reader = cmd.ExecuteReader();
SHBAccount shbAccount = null;
if (reader.Read())
{
shbAccount = new SHBAccount();
shbAccount.UserName = reader.GetString("username");
shbAccount.Balance = reader.GetInt32("balance");
}
reader.Close();
ConnectionHelper.GetConnection();
return(shbAccount);
}
public void Login()
{
Program.currentLoggedInAccount = null;
Console.Clear();
Console.WriteLine("Tiến hành đăng nhập Hệ thống Ngân hàng SHB.");
Console.WriteLine("Vui lòng điền tên truy cập: ");
var username = Console.ReadLine();
Console.WriteLine("Vui lòng nhập mật khẩu: ");
var password = Console.ReadLine();
SHBAccount shbAccount = shbAccountModel.FindByUsernameAndPassword(username, password);
if (shbAccount == null)
{
Console.WriteLine("Sai tài khoản, vui lòng thử lại.");
Console.WriteLine("Ấn enter để tiếp tục.");
Console.Read();
return;
}
Program.currentLoggedInAccount = shbAccount;
}
public void Login()
{
Program.CurrentLoggedInAccount = null;
Console.Clear();
Console.WriteLine("Login SHB System");
Console.WriteLine("Username: "******"Password: "******"Login failed. Please try again");
Console.WriteLine("Press any key to continue");
Console.Read();
return;
}
Program.CurrentLoggedInAccount = shbAccount;
}
public SHBAccount GetAccountByUsername(string username)
{
ConnectionHelper.GetConnection();
var queryString = "select * from `SHBAccount` where `username` = @username";
var cmd = new MySqlCommand(queryString, ConnectionHelper.GetConnection());
cmd.Parameters.AddWithValue("@username", username);
var dataReader = cmd.ExecuteReader();
SHBAccount shbAccount = null;
if (dataReader.Read())
{
shbAccount = new SHBAccount();
shbAccount.AccountNumber = dataReader.GetString("accountNumber");
shbAccount.Username = dataReader.GetString("username");
shbAccount.Password = dataReader.GetString("password");
shbAccount.Balance = dataReader.GetDouble("balance");
}
dataReader.Close();
ConnectionHelper.CloseConnection();
return(shbAccount);
}
public bool DoLogin()
{
Console.Clear();
Console.Out.Flush();
// Lấy thông tin từ người dùng nhập vào.
Console.WriteLine("============= ĐĂNG NHẬP ============");
Console.WriteLine("TÀI KHOẢN: ");
var username = Console.ReadLine();
Console.WriteLine("MẬT KHẨU: ");
var password = Console.ReadLine();
var shbAccount = new SHBAccount(username, password);
// Bắt đầu kiểm tra Valid user và password length khác null và lớn hơn 0.
var errors = shbAccount.ValidLoginInformation();
if (errors.Count > 0)
{
Console.WriteLine("Vui lòng kiểm tra lại.");
foreach (var messagErrorsValue in errors.Values)
{
Console.Error.WriteLine(messagErrorsValue);
}
Console.ReadLine();
return(false);
}
shbAccount = (SHBAccount)_model.FindByUsernameAndPassword(username, password);
if (shbAccount == null)
{
// Sai thông tin username, trả về thông báo lỗi cụ thể.
Console.WriteLine("Thông tin tài khoản không hợp lệ, vui lòng thử lại.");
return(false);
}
// Login thành công, lưu thông tin vào biến static trong lớp Program.
Program._SHB_CurrentLoggedIn = shbAccount;
return(true);
}
public SHBAccount GetAccountByAccountNumber(string accountNumber)
{
var queryString = "select * from `shbAccount` where `accountNumber` = @accountNumber ";
var cmd = new MySqlCommand(queryString, ConnectionHelper.GetConnection());
cmd.Parameters.AddWithValue("@accountNumber", accountNumber);
var reader = cmd.ExecuteReader();
SHBAccount shbAccount = null;
if (reader.Read())
{
shbAccount = new SHBAccount
{
AccountNumber = reader.GetString("accountNumber"),
UserName = reader.GetString("username"),
Password = reader.GetString("password"),
Balance = reader.GetInt32("balance"),
};
}
reader.Close();
ConnectionHelper.CloseConnection();
return(shbAccount);
}
public static bool UpdateBalance(SHBAccount currentLoggedInAccount, object blockchainTransaction)
{
throw new NotImplementedException();
}
public bool Transfer(SHBAccount currentLoggedInAccount, SHBTransaction transactionHistory)
{
ConnectionHelper.CloseConnect();
var mySqlTransaction = ConnectionHelper.GetConnect().BeginTransaction();
try
{
var selectBalance =
"select balance from `accounts` where username = @username";
var cmdSelect = new MySqlCommand(selectBalance, ConnectionHelper.GetConnect());
cmdSelect.Parameters.AddWithValue("@username", currentLoggedInAccount.UserName);
var reader = cmdSelect.ExecuteReader();
decimal currentAccountBalance = 0;
if (reader.Read())
{
currentAccountBalance = reader.GetDecimal("balance");
}
reader.Close();
if (currentAccountBalance < transactionHistory.Amount)
{
throw new Exception("Không đủ tiền trong tài khoản.");
}
currentAccountBalance -= transactionHistory.Amount;
var updateQuery =
"update `accounts` set `balance` = @balance where username = @username";
var sqlCmd = new MySqlCommand(updateQuery, ConnectionHelper.GetConnect());
sqlCmd.Parameters.AddWithValue("@balance", currentAccountBalance);
sqlCmd.Parameters.AddWithValue("@username", currentLoggedInAccount.UserName);
var updateResult = sqlCmd.ExecuteNonQuery();
var selectBalanceReceiver =
"select balance from `accounts` where username = @username";
var cmdSelectReceiver = new MySqlCommand(selectBalanceReceiver, ConnectionHelper.GetConnect());
cmdSelectReceiver.Parameters.AddWithValue("@username", transactionHistory.ReceiverAccountNumber);
var readerReceiver = cmdSelectReceiver.ExecuteReader();
decimal receiverBalance = 0;
if (readerReceiver.Read())
{
receiverBalance = readerReceiver.GetDecimal("balance");
}
readerReceiver.Close();
receiverBalance += transactionHistory.Amount;
var updateQueryReceiver =
"update `accounts` set `balance` = @balance where username = @username";
var sqlCmdReceiver = new MySqlCommand(updateQueryReceiver, ConnectionHelper.GetConnect());
sqlCmdReceiver.Parameters.AddWithValue("@balance", receiverBalance);
sqlCmdReceiver.Parameters.AddWithValue("@username", transactionHistory.ReceiverAccountNumber);
var updateResultReceiver = sqlCmdReceiver.ExecuteNonQuery();
var historyTransactionQuery =
"insert into `transactions` (id, type, senderId, receiverId, amount, message) " +
"values (@id, @type, @senderAccountNumber, @receiverAccountNumber, @amount, @message)";
var historyTransactionCmd =
new MySqlCommand(historyTransactionQuery, ConnectionHelper.GetConnect());
historyTransactionCmd.Parameters.AddWithValue("@id", transactionHistory.TransactionId);
historyTransactionCmd.Parameters.AddWithValue("@amount", transactionHistory.Amount);
historyTransactionCmd.Parameters.AddWithValue("@type", transactionHistory.Type);
historyTransactionCmd.Parameters.AddWithValue("@message", transactionHistory.Message);
historyTransactionCmd.Parameters.AddWithValue("@senderAccountNumber",
transactionHistory.SenderAccountNumber);
historyTransactionCmd.Parameters.AddWithValue("@receiverAccountNumber",
transactionHistory.ReceiverAccountNumber);
var historyResult = historyTransactionCmd.ExecuteNonQuery();
if (updateResult != 1 || historyResult != 1 || updateResultReceiver != 1)
{
throw new Exception("Không thể thêm giao dịch hoặc update tài khoản.");
}
mySqlTransaction.Commit();
return(true);
}
catch (Exception e)
{
mySqlTransaction.Rollback();
return(false);
}
finally
{
ConnectionHelper.CloseConnect();
}
}
public bool DoRegister()
{
Console.Clear();
Console.Out.Flush();
Console.WriteLine("Nhập thông tin tài khoản.");
Console.WriteLine("-----------------------------------");
Console.WriteLine("Tài khoản: ");
var username = Console.ReadLine();
Console.WriteLine("Mật khẩu: ");
var password = Console.ReadLine();
var shbAccount = new SHBAccount(username, password);
var errors = shbAccount.CheckValid();
if (errors.Count == 0 && _model.FindByUsernameAndPassword(username, password) == null)
{
if (!_model.SaveAccount(shbAccount))
{
return(false);
}
Console.WriteLine("Đăng ký thành công.");
Console.WriteLine("--------------------------------");
int countLoop = 0;
while (true)
{
if (countLoop > 1)
{
Console.Clear();
Console.Out.Flush();
}
Console.WriteLine("Bạn có muốn đăng nhập không? Y/N");
Console.WriteLine("Vui lòng nhập lựa chọn của bạn:");
var choice = Console.ReadLine();
if (choice != null && choice.Equals("N"))
{
DoLogin();
break;
}
if (choice != null && choice.Equals("Y"))
{
return(false);
}
Console.WriteLine("Lựa chọn không hợp lệ, vui lòng thử lại.");
}
}
else
{
Console.Error.WriteLine("Thông tin tài khoản không hợp lệ, vui lòng thử lại.");
foreach (var messagErrorsValue in errors.Values)
{
Console.Error.WriteLine(messagErrorsValue);
}
Console.ReadLine();
}
return(false);
}
public bool UpdateBalance(SHBAccount currentLoggedInAccount, SHBTransaction transaction)
{
ConnectionHelper.CloseConnect();
var tran = ConnectionHelper.GetConnect().BeginTransaction();
try
{
var cmd = new MySqlCommand("select * from accounts where username = @Username",
ConnectionHelper.GetConnect());
cmd.Parameters.AddWithValue("@Username", currentLoggedInAccount.UserName);
SHBAccount shbAccount = null;
var reader = cmd.ExecuteReader();
decimal currentAccountBalance = 0;
if (reader.Read())
{
currentAccountBalance = reader.GetDecimal("balance");
}
reader.Close();
if (currentAccountBalance < 0)
{
Console.WriteLine("Không đủ tiền trong tài khoản.");
return(false);
}
if (transaction.Type == SHBTransaction.TransactionType.WITHDRAW)
{
if (currentAccountBalance < transaction.Amount)
{
Console.WriteLine("Khong du tien thuc hien giao dich");
return(false);
}
currentAccountBalance -= transaction.Amount;
}
else if (transaction.Type == SHBTransaction.TransactionType.DEPOSIT)
{
currentAccountBalance += transaction.Amount;
}
var updateQuery =
"update `accounts` set `balance` = @balance where username = @username";
var sqlCmd = new MySqlCommand(updateQuery, ConnectionHelper.GetConnect());
sqlCmd.Parameters.AddWithValue("@balance", currentAccountBalance);
sqlCmd.Parameters.AddWithValue("@username", currentLoggedInAccount.UserName);
var updateResult = sqlCmd.ExecuteNonQuery();
var historyTransactionQuery =
"insert into `transactions` (id, type, senderId, receiverId, amount, message) " +
"values (@id, @type, @senderAccountNumber, @receiverAccountNumber, @amount, @message)";
var historyTransactionCmd =
new MySqlCommand(historyTransactionQuery, ConnectionHelper.GetConnect());
historyTransactionCmd.Parameters.AddWithValue("@id", transaction.TransactionId);
historyTransactionCmd.Parameters.AddWithValue("@amount", transaction.Amount);
historyTransactionCmd.Parameters.AddWithValue("@type", transaction.Type);
historyTransactionCmd.Parameters.AddWithValue("@message", transaction.Message);
historyTransactionCmd.Parameters.AddWithValue("@senderAccountNumber",
transaction.SenderAccountNumber);
historyTransactionCmd.Parameters.AddWithValue("@receiverAccountNumber",
transaction.ReceiverAccountNumber);
var historyResult = historyTransactionCmd.ExecuteNonQuery();
if (updateResult != 1 || historyResult != 1)
{
throw new Exception("Không thể thêm giao dịch hoặc update tài khoản.");
}
tran.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
tran.Rollback();
return(false);
}
ConnectionHelper.CloseConnect();
return(true);
}
public bool UpdateBalance(SHBAccount currentLoggedInAccount, SHBTransaction transaction)
{
// 1. Kiểm tra số dư tài khoản hiện tại.
// 2. Update số dư tài khoản hiện tại.
// 3. Lưu thông tin giao dịch.
// 4. Commit transaction.
ConnectionHelper.GetConnection();
var transaction1 = ConnectionHelper.GetConnection().BeginTransaction(); // mở giao dịch.
try
{
// Kiểm tra số dư tài khoản.
var cmd = new MySqlCommand("select balance from SHBAccount where accountNumber = @accountNumber",
ConnectionHelper.GetConnection());
cmd.Parameters.AddWithValue("@accountNumber", currentLoggedInAccount.AccountNumber);
// SHBAccount shbAccount = null;
var dataReader = cmd.ExecuteReader();
double currentAccountBalance = 0;
if (dataReader.Read())
{
currentAccountBalance = dataReader.GetDouble("balance");
}
dataReader.Close();
if (currentAccountBalance < transaction.Amount)
{
throw new Exception("Không đủ tiền trong tài khoản.");
}
if (transaction.Type == SHBTransaction.TransactionType.WITHDRAW &&
currentAccountBalance < transaction.Amount)
{
throw new Exception("Không đủ tiền trong tài khoản.");
}
if (transaction.Type == SHBTransaction.TransactionType.WITHDRAW)
{
currentAccountBalance -= transaction.Amount;
}
else if (transaction.Type == SHBTransaction.TransactionType.DEPOSIT)
{
currentAccountBalance += transaction.Amount;
}
var updateQuery =
"update `SHBAccount` set `balance` = @balance where accountNumber = @accountNumber";
var sqlCmd = new MySqlCommand(updateQuery, ConnectionHelper.GetConnection());
sqlCmd.Parameters.AddWithValue("@balance", currentAccountBalance);
sqlCmd.Parameters.AddWithValue("@accountNumber", currentLoggedInAccount.AccountNumber);
var updateResult = sqlCmd.ExecuteNonQuery();
var historyTransactionQuery =
"insert into `SHBTransaction` (transactionId, type, senderAccountNumber, receiverAccountNumber, amount, message) " +
"values (@transactionId, @type, @senderAccountNumber, @receiverAccountNumber, @amount, @message)";
var historyTransactionCmd =
new MySqlCommand(historyTransactionQuery, ConnectionHelper.GetConnection());
historyTransactionCmd.Parameters.AddWithValue("@transactionId", transaction.TransactionId);
historyTransactionCmd.Parameters.AddWithValue("@amount", transaction.Amount);
historyTransactionCmd.Parameters.AddWithValue("@type", transaction.Type);
historyTransactionCmd.Parameters.AddWithValue("@message", transaction.Message);
historyTransactionCmd.Parameters.AddWithValue("@senderAccountNumber",
transaction.SenderAccountNumber);
historyTransactionCmd.Parameters.AddWithValue("@receiverAccountNumber",
transaction.ReceiverAccountNumber);
var historyResult = historyTransactionCmd.ExecuteNonQuery();
if (updateResult != 1 || historyResult != 1)
{
throw new Exception("Không thể thêm giao dịch hoặc update tài khoản.");
}
transaction1.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
transaction1.Rollback(); // lưu giao dịch vào.
return(false);
}
ConnectionHelper.CloseConnection();
return(true);
}
public bool Transfer(SHBAccount currentLoggedInAccount, SHBTransaction transaction)
{
ConnectionHelper.GetConnection();
var transaction1 = ConnectionHelper.GetConnection().BeginTransaction(); // mở giao dịch.
try
{
// Kiểm tra số dư tài khoản.
var selectBalance =
"select balance from SHBAccount where accountNumber = @accountNumber";
var cmdSelect = new MySqlCommand(selectBalance, ConnectionHelper.GetConnection());
cmdSelect.Parameters.AddWithValue("@accountNumber", currentLoggedInAccount.AccountNumber);
var dataReader = cmdSelect.ExecuteReader();
double currentAccountBalance = 0;
if (dataReader.Read())
{
currentAccountBalance = dataReader.GetDouble("balance");
}
dataReader.Close();
if (currentAccountBalance < transaction.Amount)
{
throw new Exception("Không đủ tiền trong tài khoản.");
}
currentAccountBalance -= transaction.Amount;
//Tiến hành trừ tiền tài khoản gửi.
// Update tài khoản.
var updateQuery =
"update `SHBAccount` set `balance` = @balance where accountNumber = @accountNumber";
var sqlCmd = new MySqlCommand(updateQuery, ConnectionHelper.GetConnection());
sqlCmd.Parameters.AddWithValue("@balance", currentAccountBalance);
sqlCmd.Parameters.AddWithValue("@accountNumber", currentLoggedInAccount.AccountNumber);
var updateResult = sqlCmd.ExecuteNonQuery();
// Kiểm tra số dư tài khoản.
var selectBalanceReceiver =
"select balance from `SHBAccount` where accountNumber = @accountNumber";
var cmdSelectReceiver = new MySqlCommand(selectBalanceReceiver, ConnectionHelper.GetConnection());
cmdSelectReceiver.Parameters.AddWithValue("@accountNumber", transaction.ReceiverAccountNumber);
var readerReceiver = cmdSelectReceiver.ExecuteReader();
double receiverBalance = 0;
if (readerReceiver.Read())
{
receiverBalance = readerReceiver.GetDouble("balance");
}
readerReceiver.Close(); // important.
//Tiến hành cộng tiền tài khoản nhận.
receiverBalance += transaction.Amount;
// Update tài khoản.
var updateQueryReceiver =
"update `SHBAccount` set `balance` = @balance where accountNumber = @accountNumber";
var sqlCmdReceiver = new MySqlCommand(updateQueryReceiver, ConnectionHelper.GetConnection());
sqlCmdReceiver.Parameters.AddWithValue("@balance", receiverBalance);
sqlCmdReceiver.Parameters.AddWithValue("@accountNumber", transaction.ReceiverAccountNumber);
var updateResultReceiver = sqlCmdReceiver.ExecuteNonQuery();
// Lưu lịch sử giao dịch.
var historyTransactionQuery =
"insert into `SHBTransaction` (transactionId, amount, type, message, senderAccountNumber, receiverAccountNumber) " +
"values (@transactionId, @amount, @type, @message, @senderAccountNumber, @receiverAccountNumber)";
var historyTransactionCmd =
new MySqlCommand(historyTransactionQuery, ConnectionHelper.GetConnection());
historyTransactionCmd.Parameters.AddWithValue("@transactionId", transaction.TransactionId);
historyTransactionCmd.Parameters.AddWithValue("@amount", transaction.Amount);
historyTransactionCmd.Parameters.AddWithValue("@type", transaction.Type);
historyTransactionCmd.Parameters.AddWithValue("@message", transaction.Message);
historyTransactionCmd.Parameters.AddWithValue("@senderAccountNumber",
transaction.SenderAccountNumber);
historyTransactionCmd.Parameters.AddWithValue("@receiverAccountNumber",
transaction.ReceiverAccountNumber);
var historyResult = historyTransactionCmd.ExecuteNonQuery();
if (updateResult != 1 || historyResult != 1 || updateResultReceiver != 1)
{
throw new Exception("Không thể thêm giao dịch hoặc update tài khoản.");
}
transaction1.Commit();
return(true);
}
catch (Exception e)
{
transaction1.Rollback();
Console.WriteLine(e.StackTrace);
Console.WriteLine(e.Message);
Console.WriteLine(e.Source);
Console.WriteLine(e.ToString());
return(false);
}
finally
{
ConnectionHelper.CloseConnection();
}
}
public bool Tranfer(SHBAccount currentLoggedInAccount, SHBTransaction shbTransaction)
{
var conn = ConnectionHelper.GetConnection();
var myTransaction = conn.BeginTransaction();
try
{
var balanceSender = new MySqlCommand("select * from accounts where AccountNumber = @AccountNumber ",
conn);
balanceSender.Parameters.AddWithValue("@AccountNumber", currentLoggedInAccount.AccountNumber);
double currentAccountBalance = 0;
var reader = balanceSender.ExecuteReader();
if (reader.Read())
{
currentAccountBalance = reader.GetDouble("Balance");
}
reader.Close();
if (shbTransaction.Type == SHBTransaction.TransactionType.TRANFER &&
currentAccountBalance < shbTransaction.Amount)
{
throw new Exception("Không đủ tiền trong tài khoản.");
}
currentAccountBalance -= shbTransaction.Amount;
var updateQuery = ("update accounts set Balance = @balance where AccountNumber = @AccountNumber");
var sqlCmd = new MySqlCommand(updateQuery, conn);
sqlCmd.Parameters.AddWithValue("@balance", currentAccountBalance);
sqlCmd.Parameters.AddWithValue("@AccountNumber", currentLoggedInAccount.AccountNumber);
var updateRs = sqlCmd.ExecuteNonQuery();
var balanceReceiver = new MySqlCommand("select * from accounts where AccountNumber = @AccountNumber ",
conn);
balanceReceiver.Parameters.AddWithValue("@AccountNumber", shbTransaction.ReceiverAccountNumber);
double receiverBalance = 0;
var readerReceiver = balanceReceiver.ExecuteReader();
if (readerReceiver.Read())
{
receiverBalance = readerReceiver.GetDouble("balance");
}
readerReceiver.Close();
receiverBalance += shbTransaction.Amount;
var updateQueryReceiver =
("update accounts set Balance = @balance where AccountNumber = @AccountNumber");
var sqlCmdReceiver = new MySqlCommand(updateQueryReceiver, conn);
sqlCmdReceiver.Parameters.AddWithValue("@balance", receiverBalance);
sqlCmdReceiver.Parameters.AddWithValue("@AccountNumber", shbTransaction.ReceiverAccountNumber);
var updateResultReceiver = sqlCmdReceiver.ExecuteNonQuery();
var historyTransactionQuery =
"insert into shbtransaction (transaction_id, type, sender_account_number, receiver_account_number, amount, message, createdAt, updatedAt, status) " +
"values (@id, @type, @senderAccountNumber, @receiverAccountNumber, @amount, @message, @createdAtMLS, @updatedAtMLS, @status)";
var historyTransactionCmd =
new MySqlCommand(historyTransactionQuery, conn);
historyTransactionCmd.Parameters.AddWithValue("@id", shbTransaction.TransactionId);
historyTransactionCmd.Parameters.AddWithValue("@type", shbTransaction.Type);
historyTransactionCmd.Parameters.AddWithValue("@senderAccountNumber",
shbTransaction.SenderAccountNumber);
historyTransactionCmd.Parameters.AddWithValue("@receiverAccountNumber",
shbTransaction.ReceiverAccountNumber);
historyTransactionCmd.Parameters.AddWithValue("@amount", shbTransaction.Amount);
historyTransactionCmd.Parameters.AddWithValue("@message", shbTransaction.Message);
historyTransactionCmd.Parameters.AddWithValue("@createdAtMLS", shbTransaction.CreatedAtMLS);
historyTransactionCmd.Parameters.AddWithValue("@updatedAtMLS", shbTransaction.UpdatedAtMLS);
historyTransactionCmd.Parameters.AddWithValue("@status", shbTransaction.Status);
var historyResult = historyTransactionCmd.ExecuteNonQuery();
if (updateRs != 1 || historyResult != 1 || updateResultReceiver != 1)
{
throw new Exception("Không thể thêm giao dịch hoặc update tài khoản.");
}
myTransaction.Commit();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
myTransaction.Rollback();
return(false);
}
finally
{
conn.Close();
}
}
public bool UpdateBalance(SHBAccount currentLoggedInAccount, SHBTransaction transaction)
{
var trans = ConnectionHelper.GetConnection().BeginTransaction();
try
{
var cmd = new MySqlCommand("select * from accounts where Username = @username ",
ConnectionHelper.GetConnection());
cmd.Parameters.AddWithValue("@username", currentLoggedInAccount.Username);
double currentAccountBalance = 0;
var reader = cmd.ExecuteReader();
if (reader.Read())
{
currentAccountBalance = reader.GetDouble("Balance");
}
reader.Close();
if (transaction.Type == SHBTransaction.TransactionType.WITHDRAW &&
currentAccountBalance < transaction.Amount)
{
throw new Exception("Không đủ tiền trong tài khoản.");
}
if (transaction.Type == SHBTransaction.TransactionType.WITHDRAW)
{
currentAccountBalance -= transaction.Amount;
}
else if (transaction.Type == SHBTransaction.TransactionType.DEPOSIT)
{
currentAccountBalance += transaction.Amount;
}
var updateQuery = ("update accounts set Balance = @balance where Username = @username");
var sqlCmd = new MySqlCommand(updateQuery, ConnectionHelper.GetConnection());
sqlCmd.Parameters.AddWithValue("@balance", currentAccountBalance);
sqlCmd.Parameters.AddWithValue("@username", currentLoggedInAccount.Username);
var updateRs = sqlCmd.ExecuteNonQuery();
var transactionQuery =
"insert into shbtransaction (transaction_id, type, sender_account_number, receiver_account_number, amount, message, createdAt, updatedAt, status) " +
"values (@id, @type, @senderAccountNumber, @receiverAccountNumber, @amount, @message, @createdAtMLS, @updatedAtMLS, @status)";
var historyTransactionCmd =
new MySqlCommand(transactionQuery, ConnectionHelper.GetConnection());
historyTransactionCmd.Parameters.AddWithValue("@id", transaction.TransactionId);
historyTransactionCmd.Parameters.AddWithValue("@type", transaction.Type);
historyTransactionCmd.Parameters.AddWithValue("@senderAccountNumber",
transaction.SenderAccountNumber);
historyTransactionCmd.Parameters.AddWithValue("@receiverAccountNumber",
transaction.ReceiverAccountNumber);
historyTransactionCmd.Parameters.AddWithValue("@amount", transaction.Amount);
historyTransactionCmd.Parameters.AddWithValue("@message", transaction.Message);
historyTransactionCmd.Parameters.AddWithValue("@createdAtMLS", transaction.CreatedAtMLS);
historyTransactionCmd.Parameters.AddWithValue("@updatedAtMLS", transaction.UpdatedAtMLS);
historyTransactionCmd.Parameters.AddWithValue("@status", transaction.Status);
var historyResult = historyTransactionCmd.ExecuteNonQuery();
Console.WriteLine("query status: " + historyResult);
if (updateRs != 1 || historyResult != 1)
{
throw new Exception("Không thể thêm giao dịch hoặc update tài khoản.");
}
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine("Err: " + e.Message);
try
{
trans.Rollback();
}
catch (Exception exception)
{
Console.WriteLine(exception);
throw;
}
return(false);
}
ConnectionHelper.CloseConnection();
return(true);
}
public bool UpdateBalance(SHBAccount currentLoggedInAccount, SHBTransaction transaction)
{
ConnectionHelper.OpenConnection();
MySqlConnection mySqlConnection;
var tran = ConnectionHelper.OpenConnection().BeginTransaction();
try
{
var cmd = new MySqlCommand("select * from account where username = @username",
ConnectionHelper.OpenConnection());
cmd.Parameters.AddWithValue("@Username", currentLoggedInAccount.AccountNumber);
SHBAccount shbAccount = null;
var reader = cmd.ExecuteReader();
double currentAccountBalance = 0;
if (reader.Read())
{
currentAccountBalance = reader.GetDouble("balance");
}
reader.Close();
if (currentAccountBalance < 0)
{
Console.WriteLine("You have not enough money");
return(false);
}
if (transaction.Type == 1)
{
if (currentAccountBalance < transaction.Amount)
{
Console.WriteLine("Not enough money");
return(false);
}
currentAccountBalance -= transaction.Amount;
}
else if (transaction.Type == 2)
{
currentAccountBalance += transaction.Amount;
}
var updateQuery =
"update `account` set `balance` = @balance where accountId = @accountId";
var sqlCmd = new MySqlCommand(updateQuery, ConnectionHelper.OpenConnection());
sqlCmd.Parameters.AddWithValue("@balance", currentAccountBalance);
sqlCmd.Parameters.AddWithValue("@accountId", currentLoggedInAccount.AccountNumber);
var updateResult = sqlCmd.ExecuteNonQuery();
var historyTransactionQuery =
"insert into `SHB` (transactionId, type, senderId, receiverId, amount, message) " +
"values (@transactionId, @type, @senderId, @receiverId, @amount, @message)";
var historyTransactionCmd =
new MySqlCommand(historyTransactionQuery, ConnectionHelper.OpenConnection());
historyTransactionCmd.Parameters.AddWithValue("@transactionId", transaction.TransactionId);
historyTransactionCmd.Parameters.AddWithValue("@amount", transaction.Amount);
historyTransactionCmd.Parameters.AddWithValue("@type", transaction.Type);
historyTransactionCmd.Parameters.AddWithValue("@message", transaction.Message);
historyTransactionCmd.Parameters.AddWithValue("@senderId",
transaction.SenderAccountId);
historyTransactionCmd.Parameters.AddWithValue("@receiverId",
transaction.ReceiverAccountId);
var historyResult = historyTransactionCmd.ExecuteNonQuery();
if (updateResult != 1 || historyResult != 1)
{
throw new Exception("Can not transaction or update your account");
}
tran.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
tran.Rollback();
return(false);
}
ConnectionHelper.CloseConnection();
return(true);
}
public bool UpdateBalance(SHBAccount currentLoggedInAccount, TransactionHistory transactionHistory)
{
try
{
// Kiểm tra số dư tài khoản.
var selectBalance =
"select balance from `blkaccount` where accountNumber = @accountNumber ";
var cmdSelect = new MySqlCommand(selectBalance, ConnectionHelper.GetConnection());
cmdSelect.Parameters.AddWithValue("@accountNumber", currentLoggedInAccount.AccountNumber);
var reader = cmdSelect.ExecuteReader();
decimal currentAccountBalance = 0;
if (reader.Read())
{
currentAccountBalance = reader.GetDecimal("balance");
}
reader.Close(); // important.
if (transactionHistory.Type == TransactionHistory.TransactionType.WITHDRAW &&
currentAccountBalance < transactionHistory.Amount)
{
throw new Exception("Không đủ tiền trong tài khoản.");
}
if (transactionHistory.Type == TransactionHistory.TransactionType.WITHDRAW)
{
currentAccountBalance -= transactionHistory.Amount;
}
else if (transactionHistory.Type == TransactionHistory.TransactionType.DEPOSIT)
{
currentAccountBalance += transactionHistory.Amount;
}
// Update tài khoản.
var updateQuery =
"update `blkaccount` set `balance` = @balance where accountNumber = @accountNumber ";
var sqlCmd = new MySqlCommand(updateQuery, ConnectionHelper.GetConnection());
sqlCmd.Parameters.AddWithValue("@balance", currentAccountBalance);
sqlCmd.Parameters.AddWithValue("@accountNumber", currentLoggedInAccount.AccountNumber);
var updateResult = sqlCmd.ExecuteNonQuery();
// Lưu lịch sử giao dịch.
var historyTransactionQuery =
"insert into `blktransaction` (TransactionId,Type, SenderAccountNumber, ReceiverAccountNumber, Amount, Message ) " +
"values (@id, @type, @senderAccountNumber, @receiverAccountNumber, @amount @content)";
var historyTransactionCmd =
new MySqlCommand(historyTransactionQuery, ConnectionHelper.GetConnection());
historyTransactionCmd.Parameters.AddWithValue("@id", transactionHistory.Id);
historyTransactionCmd.Parameters.AddWithValue("@amount", transactionHistory.Amount);
historyTransactionCmd.Parameters.AddWithValue("@type", transactionHistory.Type);
historyTransactionCmd.Parameters.AddWithValue("@content", transactionHistory.Content);
historyTransactionCmd.Parameters.AddWithValue("@senderAccountNumber",
transactionHistory.SenderAccountNumber);
historyTransactionCmd.Parameters.AddWithValue("@receiverAccountNumber",
transactionHistory.ReceiverAccountNumber);
var historyResult = historyTransactionCmd.ExecuteNonQuery();
if (updateResult != 1 || historyResult != 1)
{
throw new Exception("Không thể thêm giao dịch hoặc update tài khoản.");
}
transaction.Comit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
transaction.Rollback(); // lưu giao dịch vào.
return(false);
}
ConnectionHelper.CloseConnection();
return(true);
}
