public bool Save(HL_Account account)
{
DbConnection.Instance().OpenConnection();
string cnnStr = "insert into `account`" +
" (accountNumber, username, password, balance, identityCard, fullname, email, birthday,phoneNumber, address, gender, status, salt)" +
" values (@accountNumber, @username, @password, @balance, @identityCard, @fullname, @email, @birthday, @phoneNumber, @address, @gender, @status, @salt)";
var cmd = new MySqlCommand(cnnStr, DbConnection.Instance().Connection);
cmd.Parameters.AddWithValue("@accountNumber", account.AccountNumber);
cmd.Parameters.AddWithValue("@username", account.Username);
cmd.Parameters.AddWithValue("@password", account.Password);
cmd.Parameters.AddWithValue("@balance", account.Balance);
cmd.Parameters.AddWithValue("@identityCard", account.IdentityCard);
cmd.Parameters.AddWithValue("@email", account.Email);
cmd.Parameters.AddWithValue("@birthday", account.Birthday);
cmd.Parameters.AddWithValue("@fullname", account.Fullname);
cmd.Parameters.AddWithValue("@phoneNumber", account.PhoneNumber);
cmd.Parameters.AddWithValue("@address", account.Address);
cmd.Parameters.AddWithValue("@gender", account.Gender);
cmd.Parameters.AddWithValue("@status", account.Status);
cmd.Parameters.AddWithValue("@salt", account.Salt);
cmd.ExecuteNonQuery();
DbConnection.Instance().CloseConnection();
return(true);
}
public HL_Account GetAccountByUserName(string username)
{
DbConnection.Instance().OpenConnection();
var queryString = "select * from `account` where username = @username and status = 1";
var cmd = new MySqlCommand(queryString, DbConnection.Instance().Connection);
cmd.Parameters.AddWithValue("@username", username);
var reader = cmd.ExecuteReader();
HL_Account account = null;
if (reader.Read())
{
var _username = reader.GetString("username");
var password = reader.GetString("password");
var salt = reader.GetString("salt");
var accountNumber = reader.GetString("accountNumber");
var identityCard = reader.GetString("identityCard");
var balance = reader.GetDecimal("balance");
var phone = reader.GetString("phoneNumber");
var email = reader.GetString("email");
var fullName = reader.GetString("fullName");
var createdAt = reader.GetString("createdAt");
var updatedAt = reader.GetString("updatedAt");
var status = reader.GetInt32("status");
account = new HL_Account(username, password, salt, accountNumber, identityCard, balance, phone, email,
fullName, createdAt, updatedAt, (HL_Account.ActiveStatus)status);
}
reader.Close();
DbConnection.Instance().CloseConnection();
return(account);
}
public HL_Account GetByAccountNumber(string accountNumber)
{
HL_Account account = null;
DbConnection.Instance().OpenConnection();
var queryString = "select * from `account` where accountNumber = @accountNumber and status = 1";
var cmd = new MySqlCommand(queryString, DbConnection.Instance().Connection);
cmd.Parameters.AddWithValue("@accountNumber", accountNumber);
var reader = cmd.ExecuteReader();
var isExist = reader.Read();
if (isExist)
{
account = new HL_Account
{
AccountNumber = reader.GetString("accountNumber"),
Username = reader.GetString("username"),
Password = reader.GetString("password"),
Salt = reader.GetString("salt"),
Fullname = reader.GetString("fullname"),
Balance = reader.GetInt32("balance")
};
reader.Close();
return(account);
}
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 static Dictionary <string, HL_Account> ReadAccounts()
{
var dictionary = new Dictionary <string, HL_Account>();
var lines = File.ReadAllLines("ForgetMeNot.txt");
for (var i = 0; i < lines.Length; i += 1)
{
if (i == 0)
{
continue;
}
var linesSplited = lines[i].Split("|");
if (linesSplited.Length == 6)
{
var acc = new HL_Account()
{
AccountNumber = linesSplited[0],
Username = linesSplited[1],
Fullname = linesSplited[2],
Balance = Decimal.Parse(linesSplited[3]),
Salt = linesSplited[4],
Status = (HL_Account.ActiveStatus)Int32.Parse(linesSplited[5])
};
if (dictionary.ContainsKey(acc.AccountNumber))
{
continue;
}
dictionary.Add(acc.AccountNumber, acc);
}
}
return(dictionary);
}
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 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 HL_Account GetByUsername(string username)
{
HL_Account _account = null;
DbConnection.Instance().OpenConnection();
var cnnStr = "select * from `account` where `username` = @username and status IN (1,2)";
var cmd = new MySqlCommand(cnnStr, DbConnection.Instance().Connection);
cmd.Parameters.AddWithValue("@username", username);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
_account = new HL_Account();
_account.AccountNumber = reader.GetString("accountNumber");
_account.Username = reader.GetString("username");
_account.Password = reader.GetString("password");
_account.Salt = reader.GetString("salt");
_account.Balance = reader.GetInt32("balance");
_account.IdentityCard = reader.GetString("identityCard");
_account.Fullname = reader.GetString("fullname");
_account.Email = reader.GetString("email");
_account.PhoneNumber = reader.GetString("phoneNumber");
_account.Address = reader.GetString("address");
_account.Gender = reader.GetInt32("gender");
var status = reader.GetInt32("status");
switch (status)
{
case 1:
_account.Status = HL_Account.ActiveStatus.ACTIVE;
break;
case 2:
_account.Status = HL_Account.ActiveStatus.LOCKED;
break;
}
}
reader.Close();
DbConnection.Instance().CloseConnection();
return(_account);
}
public bool CheckLogin()
{
Console.WriteLine("Enter username:"******"Enter password:"******"Invalid account info.");
return(false);
}
if (account1.CheckEncryptedPassword(pass) == false)
{
Console.WriteLine("Invalid account info.");
return(false);
}
Program.currentLoggedIn = account1;
return(true);
}
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 bool AddAccount()
{
Console.WriteLine("----Please enter information----");
Console.WriteLine("Enter username:"******"Username existed.");
return(false);
}
Console.WriteLine("Enter password:"******"Enter confirm password:"******"Enter identityCard:");
string identityCard = Console.ReadLine();
Console.WriteLine("Enter fullname:");
string fullname = Console.ReadLine();
Console.WriteLine("Enter email:");
string email = Console.ReadLine();
Console.WriteLine("Enter birthday:");
string birthday = Console.ReadLine();
Console.WriteLine("Enter phoneNumber:");
string phoneNumber = Console.ReadLine();
Console.WriteLine("Enter address:");
string address = Console.ReadLine();
Console.WriteLine("Enter gender");
int gender = ParseChoice.GetInputNumber();
account = new HL_Account(username, password, cpassword, identityCard, fullname, email, birthday, phoneNumber, address, gender);
var errors = account.CheckValid();
if (errors.Count == 0)
{
account.GetMD5WithSalt();
if (_accountModel.Save(account))
{
Console.WriteLine("Save success!!!");
}
else
{
Console.WriteLine("Not success. Please try again.");
}
}
else
{
Console.Error.WriteLine("Please fix following errors and try again.");
foreach (var messagErrorsValue in errors.Values)
{
Console.Error.WriteLine(messagErrorsValue);
}
}
return(true);
}
