public HL_Transaction GetByUsername(string username)
{
DbConnection.Instance().OpenConnection();
var cnnStr = "SELECT * FROM `transaction` INNER JOIN `account` ON transaction.fromAccountNumber = account.accountNumber WHERE username = @username";
var cmd = new MySqlCommand(cnnStr, DbConnection.Instance().Connection);
cmd.Parameters.AddWithValue("@username", username);
var reader = cmd.ExecuteReader();
if (reader.Read())
{
string id = reader.GetString("id");
string senderAccountNumber = reader.GetString("fromAccountNumber");
decimal amount = reader.GetDecimal("amount");
string content = reader.GetString("content");
string receiverAccountNumber = reader.GetString("toAccountNumber");
var type = reader.GetInt16("type");
HL_Transaction transaction = new HL_Transaction(id, senderAccountNumber, amount, content, receiverAccountNumber, (HL_Transaction.TransactionType)type);
return(transaction);
}
reader.Close();
DbConnection.Instance().CloseConnection();
return(null);
}
public bool TransferAmount(HL_Account account, HL_Transaction historyTransaction)
{
DbConnection.Instance().OpenConnection();
var queryBalance = "select `balance` from `account` where username = @username and status = 1";
MySqlCommand queryBalanceCommand = new MySqlCommand(queryBalance, DbConnection.Instance().Connection);
queryBalanceCommand.Parameters.AddWithValue("@username", account.Username);
var balanceReader = queryBalanceCommand.ExecuteReader();
if (!balanceReader.Read())
{
throw new TransactionException("Invalid username");
}
var currentBalance = balanceReader.GetDecimal("balance");
currentBalance -= historyTransaction.Amount;
balanceReader.Close();
if (UpdateBalanceAndSaveTransaction(account, currentBalance, historyTransaction))
{
return(true);
}
DbConnection.Instance().CloseConnection();
return(false);
}
public HL_Transaction FindByDateTime(string createdAt)
{
DbConnection.Instance().OpenConnection();
var cnnStr = "select * from `transaction` where `createdAt` = @createdAt";
var cmd = new MySqlCommand(cnnStr, DbConnection.Instance().Connection);
cmd.Parameters.AddWithValue("@createdAt", createdAt);
var reader = cmd.ExecuteReader();
if (reader.Read())
{
string id = reader.GetString("id");
string senderAccountNumber = reader.GetString("fromAccountNumber");
decimal amount = reader.GetDecimal("amount");
string content = reader.GetString("content");
string receiverAccountNumber = reader.GetString("toAccountNumber");
var type = reader.GetInt16("type");
HL_Transaction transaction = new HL_Transaction(id, senderAccountNumber, amount, content, receiverAccountNumber, (HL_Transaction.TransactionType)type);
return(transaction);
}
reader.Close();
DbConnection.Instance().CloseConnection();
return(null);
}
public void Deposit()
{
Console.WriteLine("Deposit.");
Console.WriteLine("---------------------------------");
Console.WriteLine("Please enter amount to deposit: ");
var amount = ParseChoice.GetDecimalNumber();
Console.WriteLine("Please enter message content: ");
var content = Console.ReadLine();
var historyTransaction = new HL_Transaction
{
Id = Guid.NewGuid().ToString(),
Type = HL_Transaction.TransactionType.DEPOSIT,
Amount = amount,
Content = content,
SenderAccountNumber = Program.currentLoggedIn.AccountNumber,
ReceiverAccountNumber = Program.currentLoggedIn.AccountNumber,
Status = HL_Transaction.ActiveStatus.DONE
};
if (_accountModel.UpdateBalance(Program.currentLoggedIn, historyTransaction))
{
Console.WriteLine("Transaction success!");
}
else
{
Console.WriteLine("Transaction fails, please try again!");
}
Program.currentLoggedIn = _accountModel.GetAccountByUserName(Program.currentLoggedIn.Username);
Console.WriteLine("Current balance: " + Program.currentLoggedIn.Balance);
Console.WriteLine("Press enter to continue!");
Console.ReadLine();
}
public static List <HL_Transaction> ReadTransactions()
{
var list = new List <HL_Transaction>();
var lines = File.ReadAllLines("NeverEverGetBackTogether.txt");
for (var i = 0; i < lines.Length; i += 1)
{
if (i == 0)
{
continue;
}
var linesSplited = lines[i].Split("|");
if (linesSplited.Length == 8)
{
var tx = new HL_Transaction()
{
Id = linesSplited[0],
SenderAccountNumber = linesSplited[1],
ReceiverAccountNumber = linesSplited[2],
Type = (HL_Transaction.TransactionType)Int32.Parse(linesSplited[3]),
Amount = Decimal.Parse(linesSplited[4]),
Content = linesSplited[5],
CreatedAt = linesSplited[6],
Status = (HL_Transaction.ActiveStatus)Int32.Parse(linesSplited[7])
};
list.Add(tx);
}
}
return(list);
}
public bool TransferAmount(HL_Account account, HL_Transaction historyTransaction)
{
DbConnection.Instance().OpenConnection();
try
{
// ng gui
var queryBalance = "select `balance` from `account` where username = @username and status = 1";
MySqlCommand queryBalanceCommand = new MySqlCommand(queryBalance, DbConnection.Instance().Connection);
queryBalanceCommand.Parameters.AddWithValue("@username", account.Username);
var balanceReader = queryBalanceCommand.ExecuteReader();
if (!balanceReader.Read())
{
throw new TransactionException("Invalid username");
}
var currentBalance = balanceReader.GetDecimal("balance");
balanceReader.Close();
if (historyTransaction.Amount > currentBalance)
{
throw new Exception("Not enough money");
}
currentBalance -= historyTransaction.Amount;
// ng nhan
var queryBalanceRece = "select `balance` from `account` where accountNumber = @accountNumber and status = 1";
MySqlCommand queryBalanceCommand1 = new MySqlCommand(queryBalanceRece, DbConnection.Instance().Connection);
queryBalanceCommand1.Parameters.AddWithValue("@accountNumber", historyTransaction.ReceiverAccountNumber);
var balanceRece = queryBalanceCommand1.ExecuteReader();
if (!balanceRece.Read())
{
throw new TransactionException("Invalid username");
}
var currentBalanceRece = balanceRece.GetDecimal("balance");
Console.WriteLine("tien ng nhaaj : " + currentBalanceRece);
balanceRece.Close();
Console.WriteLine("ok1");
var currentBalanceRece1 = currentBalanceRece + historyTransaction.Amount;
Console.WriteLine("abc: " + currentBalanceRece1);
if (UpdateBalanceAndSaveTransaction(account, currentBalance, currentBalanceRece1, historyTransaction))
{
return(true);
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
DbConnection.Instance().CloseConnection();
return(false);
}
public bool GetTransaction()
{
HL_Transaction transaction = _transactionModel.GetByUsername(Program.currentLoggedIn.Username);
if (transaction == null)
{
Console.WriteLine("loi");
}
Console.WriteLine("ng gui: " + transaction.SenderAccountNumber);
Console.WriteLine("tien: " + transaction.Amount);
Console.WriteLine("nd: " + transaction.Content);
Console.WriteLine("ng nhan: " + transaction.ReceiverAccountNumber);
return(true);
}
public HashSet <HL_Transaction> TransactionLog(string acountNumber)
{
DbConnection.Instance().OpenConnection();
string checkTransacLog = "SELECT id, fromAccountNumber, amount, content, toAccountNumber, type, createdAt, status FROM transaction WHERE fromAccountNumber = '" + Program.currentLoggedIn.AccountNumber + "'";
MySqlCommand cmd = new MySqlCommand(checkTransacLog, DbConnection.Instance().Connection);
string id = "", senderAccountNumber = "", content = "", receiveBankNumber = "";
decimal amount;
int type1 = 0;
int status1 = 0;
string createdAt = "";
HL_Transaction transaction = null;
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
id = reader.GetString("id");
senderAccountNumber = reader.GetString("fromAccountNumber");
amount = reader.GetDecimal("amount");
content = reader.GetString("content");
receiveBankNumber = reader.GetString("toAccountNumber");
type1 = reader.GetInt32("type");
createdAt = reader.GetString("createdAt");
status1 = reader.GetInt32("status");
// cast int sang enum.
TransactionType type = (TransactionType)type1;
ActiveStatus status = (ActiveStatus)status1;
transaction = new HL_Transaction(id, amount, content, senderAccountNumber, receiveBankNumber, type, createdAt, status);
hashList.Add(transaction);
}
}
reader.Close();
return(hashList);
}
public bool UpdateBalanceAndSaveTransaction(HL_Account account, decimal currentBalance, decimal currentBalanceRece1, HL_Transaction historyTransaction)
{
var transaction = DbConnection.Instance().Connection.BeginTransaction();
try
{
//update số dư vào tài khoản người nhận
var updateAccountRece = 0;
var queryUpdateAccountBalance1 = "update `account` set balance = @balance where accountNumber = @accountNumber and status = 1";
var cmdUpdateAccountBalance1 = new MySqlCommand(queryUpdateAccountBalance1, DbConnection.Instance().Connection);
cmdUpdateAccountBalance1.Parameters.AddWithValue("@accountNumber", historyTransaction.ReceiverAccountNumber);
cmdUpdateAccountBalance1.Parameters.AddWithValue("@balance", currentBalanceRece1);
updateAccountRece = cmdUpdateAccountBalance1.ExecuteNonQuery();
// Update số dư vào tài khoản người gửi
var updateAccountResult = 0;
var queryUpdateAccountBalance = "update `account` set balance = @balance where username = @username and status = 1";
var cmdUpdateAccountBalance = new MySqlCommand(queryUpdateAccountBalance, DbConnection.Instance().Connection);
cmdUpdateAccountBalance.Parameters.AddWithValue("@username", account.Username);
cmdUpdateAccountBalance.Parameters.AddWithValue("@balance", currentBalance);
updateAccountResult = cmdUpdateAccountBalance.ExecuteNonQuery();
// Lưu thông tin transaction vào bảng transaction.
var insertTransactionResult = 0;
var queryInsertTransaction = "insert into `transaction` " +
"(id, fromAccountNumber, amount, content, toAccountNumber, type, status) " +
"values (@id, @fromAccountNumber, @amount, @content, @toAccountNumber, @type, @status)";
var cmdInsertTransaction =
new MySqlCommand(queryInsertTransaction, DbConnection.Instance().Connection);
cmdInsertTransaction.Parameters.AddWithValue("@id", historyTransaction.Id);
cmdInsertTransaction.Parameters.AddWithValue("@fromAccountNumber",
historyTransaction.SenderAccountNumber);
cmdInsertTransaction.Parameters.AddWithValue("@amount", historyTransaction.Amount);
cmdInsertTransaction.Parameters.AddWithValue("@content", historyTransaction.Content);
cmdInsertTransaction.Parameters.AddWithValue("@toAccountNumber",
historyTransaction.ReceiverAccountNumber);
cmdInsertTransaction.Parameters.AddWithValue("@type", historyTransaction.Type);
cmdInsertTransaction.Parameters.AddWithValue("@status", historyTransaction.Status);
insertTransactionResult = cmdInsertTransaction.ExecuteNonQuery();
// Kiểm tra lại câu lệnh
Console.WriteLine("Account model: " + updateAccountResult + insertTransactionResult + updateAccountRece);
if (updateAccountResult == 1 && insertTransactionResult == 1 && updateAccountRece == 1)
{
transaction.Commit();
return(true);
}
}
catch (TransactionException e)
{
Console.WriteLine(e);
transaction.Rollback();
}
return(false);
}
public bool UpdateBalance(HL_Account account, HL_Transaction historyTransaction)
{
DbConnection.Instance().OpenConnection();
/**
* 1. Lấy thông tin số dư mới nhất của tài khoản.
* 2. Kiểm tra kiểu transaction. Chỉ chấp nhận deposit và withdraw.
* 2.1. Kiểm tra số tiền rút nếu kiểu transaction là withdraw.
* 3. Update số dư vào tài khoản.
* 3.1. Tính toán lại số tiền trong tài khoản.
* 3.2. Update số tiền vào database.
* 4. Lưu thông tin transaction vào bảng transaction.
*/
// 1. Lấy thông tin số dư mới nhất của tài khoản.
var queryBalance = "select `balance` from `account` where username = @username and status =1";
MySqlCommand queryBalanceCommand = new MySqlCommand(queryBalance, DbConnection.Instance().Connection);
queryBalanceCommand.Parameters.AddWithValue("@username", account.Username);
var balanceReader = queryBalanceCommand.ExecuteReader();
// Không tìm thấy tài khoản tương ứng, throw lỗi.
if (!balanceReader.Read())
{
// Không tồn tại bản ghi tương ứng, lập tức rollback transaction, trả về false.
throw new TransactionException("Invalid username");
}
var currentBalance = balanceReader.GetDecimal("balance");
balanceReader.Close();
// 2. Kiểm tra kiểu transaction. Chỉ chấp nhận deposit và withdraw.
if (historyTransaction.Type != HL_Transaction.TransactionType.DEPOSIT &&
historyTransaction.Type != HL_Transaction.TransactionType.WITHDRAW)
{
throw new TransactionException("Invalid transaction type!");
}
// 2.1. Kiểm tra số tiền rút nếu kiểu transaction là withdraw(rút).
if (historyTransaction.Type == HL_Transaction.TransactionType.WITHDRAW &&
historyTransaction.Amount > currentBalance)
{
throw new TransactionException("Not enough money!");
}
// 3.1. Tính toán lại số tiền trong tài khoản.
if (historyTransaction.Type == HL_Transaction.TransactionType.DEPOSIT)
{
currentBalance += historyTransaction.Amount;
}
else if (historyTransaction.Type == HL_Transaction.TransactionType.WITHDRAW)
{
currentBalance -= historyTransaction.Amount;
}
Console.WriteLine(historyTransaction.Type);
if (UpdateBalanceAndSaveTransaction(account, currentBalance, 0, historyTransaction))
{
return(true);
}
DbConnection.Instance().CloseConnection();
return(false);
}
public void Transfer()
{
Console.WriteLine(Program.currentLoggedIn.Status);
Console.WriteLine("Transfer.");
Console.WriteLine("--------------------------------");
Console.WriteLine("Enter accountNumber to transfer: ");
string accountNumber = Console.ReadLine();
var account = _accountModel.GetByAccountNumber(accountNumber);
if (account == null)
{
Console.WriteLine("Invalid account info");
return;
}
Console.WriteLine("You are doing transaction with account: " + account.Fullname);
Console.WriteLine("Enter amount to transfer: ");
var amount = ParseChoice.GetDecimalNumber();
if (amount > Program.currentLoggedIn.Balance)
{
Console.WriteLine("Amount not enough to perform transaction.");
return;
}
Console.WriteLine("Please enter message content: ");
var content = Console.ReadLine();
Console.WriteLine("Are you sure you want to make a transaction with your account ? (y/n)");
var choice = Console.ReadLine();
if (choice.Equals("n"))
{
return;
}
var historyTransaction = new HL_Transaction()
{
Id = Guid.NewGuid().ToString(),
Type = HL_Transaction.TransactionType.TRANSFER,
Amount = amount,
Content = content,
SenderAccountNumber = Program.currentLoggedIn.AccountNumber,
ReceiverAccountNumber = account.AccountNumber,
Status = HL_Transaction.ActiveStatus.DONE
};
if (_accountModel.TransferAmount(Program.currentLoggedIn, historyTransaction))
{
Console.WriteLine("Transaction success!");
}
else
{
Console.WriteLine("Transaction fails, please try again!");
}
Program.currentLoggedIn = _accountModel.GetByUsername(Program.currentLoggedIn.Username);
Console.WriteLine("Current balance: " + Program.currentLoggedIn.Balance);
Console.WriteLine("Press enter to continue!");
Console.ReadLine();
}
