// 5. Thay doi thong tin ca nhan
public bool ChangeUser(string email, string phoneNumber, string fullName)
{
var account = _guestModel.GetActiveAccountByUserName(Program.currentLogin.Username);
if (account == null)
{
Console.WriteLine("Không tìm thấy tài khoản hoặc tài khoản đã bị khóa!");
return(false);
}
var cnn = ConnectionHelpers.GetConnection();
cnn.Open();
try
{
var stringCmdUpdateAccount =
$"UPDATE `accounts` SET `phoneNumber` = '{phoneNumber}', `fullName` = '{fullName}', `email` = '{email}' WHERE username = '******' AND status = {(int) AccountStatus.ACTIVE}";
var cmdUpdateAccount = new MySqlCommand(stringCmdUpdateAccount, cnn);
cmdUpdateAccount.ExecuteNonQuery();
cnn.Close();
Console.WriteLine("Thay đổi thông tin thành công!");
return(true);
}
catch (Exception e)
{
Console.WriteLine(e);
cnn.Close();
}
return(false);
}
// 6. Thay doi thong tin mat khau
public bool ChangePassword(string password)
{
Account account = _guestModel.GetActiveAccountByUserName(Program.currentLogin.Username);
if (account == null)
{
Console.WriteLine("Không tìm thấy tài khoản hoặc tài khoản đã bị khóa!");
return(false);
}
var cnn = ConnectionHelpers.GetConnection();
cnn.Open();
try
{
var salt = account.Salt;
var passwordHash = _passwordHelper.MD5Hash(password + salt);
var stringCmdUpdateAccount =
$"UPDATE `accounts` SET `passwordHash` = '{passwordHash}' WHERE username = '******' AND status = {(int) AccountStatus.ACTIVE}";
var cmdUpdateAccount = new MySqlCommand(stringCmdUpdateAccount, cnn);
cmdUpdateAccount.ExecuteNonQuery();
cnn.Close();
Console.WriteLine("Đổi mật khẩu thành công!");
return(true);
}
catch (Exception e)
{
Console.WriteLine(e);
cnn.Close();
}
return(false);
}
// 3. Tim kiem theo ten
public Account GetUserByAccountName(string name)
{
Account account = null;
var cnn = ConnectionHelpers.GetConnection();
cnn.Open();
var stringCmdGetAccount = (
$"SELECT * FROM `accounts` WHERE fullName = '{name}'"
);
var cmdGetAccount = new MySqlCommand(stringCmdGetAccount, cnn);
var accountReader = cmdGetAccount.ExecuteReader();
if (accountReader.Read())
{
account = new Account()
{
AccountNumber = accountReader.GetString("accountNumber"),
Balance = accountReader.GetDouble("balance"),
Username = accountReader.GetString("userName"),
PhoneNumber = accountReader.GetString("phoneNumber"),
Role = (AccountRole)accountReader.GetInt32("role"),
FullName = accountReader.GetString("fullName"),
Email = accountReader.GetString("email"),
Status = (AccountStatus)accountReader.GetInt32("status")
};
}
cnn.Close();
return(account);
}
// 9. Thay doi thong tin tai khoan
public bool EditAccount(string phoneNumber, string fullName, string email)
{
var account = GetUserByAccountAccountNumber(Program.currentLogin.AccountNumber);
if (account == null)
{
Console.WriteLine("Không tìm thấy tài khoản hoặc tài khoản đã bị khóa!");
return(false);
}
try
{
var cnn = ConnectionHelpers.GetConnection();
cnn.Open();
var stringCmdEditAccount =
$"UPDATE `accounts` SET `phoneNumber` = '{phoneNumber}', `fullName` = '{fullName}', `email` = '{email}' WHERE accountNumber = '{Program.currentLogin.AccountNumber}' AND status = {(int) AccountStatus.ACTIVE}";
var cmdEditAccount = new MySqlCommand(stringCmdEditAccount, cnn);
cmdEditAccount.ExecuteNonQuery();
cnn.Close();
Console.WriteLine("Thay đổi thành công.");
return(true);
}
catch (Exception e)
{
Console.WriteLine(e);
}
return(false);
}
public Account GetActiveAccountByUserName(string username)
{
Account account = null;
var cnn = ConnectionHelpers.GetConnection();
cnn.Open();
var stringCmdGetAccount = $"select * from accounts where username = '******' and " +
$"status = {(int)AccountStatus.ACTIVE}";
var cmdGetAccount = new MySqlCommand(stringCmdGetAccount, cnn);
var readerGetAccount = cmdGetAccount.ExecuteReader();
if (readerGetAccount.Read())
{
account = new Account()
{
AccountNumber = readerGetAccount.GetString("accountNumber"),
Username = readerGetAccount.GetString("userName"),
Balance = readerGetAccount.GetDouble("balance"),
PasswordHash = readerGetAccount.GetString("passwordHash"),
Email = readerGetAccount.GetString("email"),
PhoneNumber = readerGetAccount.GetString("phoneNumber"),
Salt = readerGetAccount.GetString("salt"),
FullName = readerGetAccount.GetString("fullName"),
Role = (AccountRole)readerGetAccount.GetInt32("role"),
Status = (AccountStatus)readerGetAccount.GetInt32("status")
};
}
cnn.Close();
return(account);
}
// 7. Truy vấn lịch sử giao dịch
public List <SHBTransaction> TransactionHistory()
{
List <SHBTransaction> list = null;
SHBTransaction shbTransaction;
Account account = _guestModel.GetActiveAccountByUserName(Program.currentLogin.Username);
if (account == null)
{
Console.WriteLine("Không tìm thấy tài khoản hoặc tài khoản đã bị khóa!");
return(null);
}
var cnn = ConnectionHelpers.GetConnection();
cnn.Open();
try
{
list = new List <SHBTransaction>();
var stringCmdGetTransaction =
$"SELECT * FROM `transactions` WHERE senderAccountNumber = '{account.AccountNumber}' OR receiverAccountNumber = '{account.AccountNumber}'";
var cmdGetTransaction = new MySqlCommand(stringCmdGetTransaction, cnn);
var readerGetTransaction = cmdGetTransaction.ExecuteReader();
while (readerGetTransaction.Read())
{
shbTransaction = new SHBTransaction()
{
TransactionCode = readerGetTransaction.GetString("transactionCode"),
SenderAccountNumber = readerGetTransaction.GetString("senderAccountNumber"),
ReceiverAccountNumber = readerGetTransaction.GetString("receiverAccountNumber"),
Type = (TransactionType)readerGetTransaction.GetInt32("type"),
Amount = readerGetTransaction.GetDouble("amount"),
Fee = readerGetTransaction.GetDouble("fee"),
Message = readerGetTransaction.GetString("message"),
CreateAt = readerGetTransaction.GetDateTime("createAt"),
UpdatedAt = readerGetTransaction.GetDateTime("updatedAt"),
Status = (TransactionStatus)readerGetTransaction.GetInt32("status")
};
list.Add(shbTransaction);
}
cnn.Close();
}
catch (Exception e)
{
Console.WriteLine(e);
cnn.Close();
}
return(list);
}
public string ValidateAccountNumber(string accountNumber)
{
string newAccountNumber = null;
var cnn = ConnectionHelpers.GetConnection();
cnn.Open();
var strGetAccount =
$"select * from accounts where accountNumber = '{accountNumber}'";
var cmdGetAccountNumber = new MySqlCommand(strGetAccount, cnn);
var accountReader = cmdGetAccountNumber.ExecuteReader();
if (accountReader.Read())
{
var firstAccountNumber = "9704";
newAccountNumber = firstAccountNumber + _passwordHelper.GenerateAccountNumber();
}
cnn.Close();
return(newAccountNumber);
}
public string ValidateUsername(string username)
{
string newUserName = null;
var cnn = ConnectionHelpers.GetConnection();
cnn.Open();
var strGetUsername =
$"select * from accounts where userName = '******'";
var cmdGetUsername = new MySqlCommand(strGetUsername, cnn);
var usernameReader = cmdGetUsername.ExecuteReader();
if (usernameReader.Read())
{
Console.WriteLine("Username đã tồn tại, vui lòng nhập lại user của bạn: ");
newUserName = Console.ReadLine();
}
cnn.Close();
return(newUserName);
}
public bool Save(Account account)
{
try
{
var cnn = ConnectionHelpers.GetConnection();
cnn.Open();
var strCmdRegister = $"insert into accounts (accountNumber, balance, userName, passwordHash, phoneNumber, salt, role, fullName, email, status) values ('{account.AccountNumber}', {Convert.ToDouble(account.Balance)}, '{account.Username}', '{account.PasswordHash}', '{account.PhoneNumber}', '{account.Salt}', " +
$"{Convert.ToInt32(account.Role)}, '{account.FullName}', '{account.Email}', {Convert.ToInt32(account.Status)})";
var cmdRegister = new MySqlCommand(strCmdRegister, cnn);
cmdRegister.ExecuteNonQuery();
cnn.Close();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e);
return(false);
}
}
// 2. Danh sách lịch sử giao dịch.
public List <SHBTransaction> ListTransaction()
{
List <SHBTransaction> listTran = null;
SHBTransaction transaction;
var cnn = ConnectionHelpers.GetConnection();
cnn.Open();
try
{
listTran = new List <SHBTransaction>();
var stringCmdGetAccount = $"select * from `transactions`";
var cmd = new MySqlCommand(stringCmdGetAccount, cnn);
var getListHistory = cmd.ExecuteReader();
while (getListHistory.Read())
{
transaction = new SHBTransaction()
{
TransactionCode = getListHistory.GetString("transactionCode"),
SenderAccountNumber = getListHistory.GetString("senderAccountNumber"),
ReceiverAccountNumber = getListHistory.GetString("receiverAccountNumber"),
Type = (TransactionType)getListHistory.GetInt32("type"),
Amount = getListHistory.GetDouble("amount"),
Fee = getListHistory.GetDouble("fee"),
Message = getListHistory.GetString("message"),
CreateAt = getListHistory.GetDateTime("createAt"),
UpdatedAt = getListHistory.GetDateTime("updatedAt"),
Status = (TransactionStatus)getListHistory.GetInt32("status")
};
listTran.Add(transaction);
}
cnn.Close();
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
return(listTran);
}
// 1. danh sách người dùng
public List <Account> ListUser()
{
List <Account> listAccount = null;
Account account;
var cnn = ConnectionHelpers.GetConnection();
cnn.Open();
try
{
listAccount = new List <Account>();
var stringCmdGetAccount = $"select * from `accounts` where role = {(int) AccountRole.USER} and status = {(int) AccountStatus.ACTIVE}";
var cmdGetAccount = new MySqlCommand(stringCmdGetAccount, cnn);
var readerGetAccount = cmdGetAccount.ExecuteReader();
while (readerGetAccount.Read())
{
account = new Account()
{
AccountNumber = readerGetAccount.GetString("accountNumber"),
Username = readerGetAccount.GetString("userName"),
Balance = readerGetAccount.GetDouble("balance"),
PasswordHash = readerGetAccount.GetString("passwordHash"),
Email = readerGetAccount.GetString("email"),
PhoneNumber = readerGetAccount.GetString("phoneNumber"),
Salt = readerGetAccount.GetString("salt"),
FullName = readerGetAccount.GetString("fullName"),
Role = (AccountRole)readerGetAccount.GetInt32("role"),
Status = (AccountStatus)readerGetAccount.GetInt32("status")
};
listAccount.Add(account);
}
cnn.Close();
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
return(listAccount);
}
// 8. Tim lich su giao dich theo stk
public List <SHBTransaction> TransactionHistory(string accountNumber)
{
List <SHBTransaction> list = new List <SHBTransaction>();
var cnn = ConnectionHelpers.GetConnection();
cnn.Open();
try
{
var cmdStringTransactionHistory =
$"SELECT * FROM transactions WHERE senderAccountNumber = '{accountNumber}' OR receiverAccountNumber = '{accountNumber}'";
var cmd = new MySqlCommand(cmdStringTransactionHistory, cnn);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var transactionHistory = new SHBTransaction()
{
TransactionCode = reader.GetString("transactionCode"),
SenderAccountNumber = reader.GetString("senderAccountNumber"),
ReceiverAccountNumber = reader.GetString("receiverAccountNumber"),
Type = (TransactionType)reader.GetInt32("type"),
Amount = reader.GetDouble("amount"),
Fee = reader.GetDouble("fee"),
Message = reader.GetString("message"),
CreateAt = (DateTime)reader.GetMySqlDateTime("createAt"),
UpdatedAt = (DateTime)reader.GetMySqlDateTime("updatedAt"),
Status = (TransactionStatus)reader.GetInt32("status"),
};
list.Add(transactionHistory);
}
cnn.Close();
return(list);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
// 2. Rút tiền
public bool Withdraw(string accountNumber, double amount)
{
var cnn = ConnectionHelpers.GetConnection();
cnn.Open();
var transaction = cnn.BeginTransaction();
try
{
if (amount <= 0)
{
throw new Exception("Giá trị không hợp !");
}
var stringCmdGetAccount =
$"SELECT balance FROM `accounts` WHERE accountNumber = '{accountNumber}' AND status = {(int) AccountStatus.ACTIVE}";
var cmdGetAccount = new MySqlCommand(stringCmdGetAccount, cnn);
var accountReader = cmdGetAccount.ExecuteReader();
if (!accountReader.Read())
{
throw new Exception("Không tìm thấy tài khoản hoặc tài khoản đã bị khóa!");
}
var currentBalance = accountReader.GetDouble("balance");
accountReader.Close();
// check balance
if (currentBalance < amount + 50000)
{
Console.WriteLine("Số dư tài khoản không đủ!");
cnn.Close();
return(false);
}
currentBalance -= amount;
var stringCmdUpdateAccount =
$"UPDATE `accounts` SET balance = '{currentBalance}' WHERE accountNumber = '{accountNumber}' AND status = 1";
var cmdUpdateAccount = new MySqlCommand(stringCmdUpdateAccount, cnn);
cmdUpdateAccount.ExecuteNonQuery();
var shbtransaction = new SHBTransaction()
{
TransactionCode = Guid.NewGuid().ToString(),
SenderAccountNumber = accountNumber,
ReceiverAccountNumber = accountNumber,
Type = TransactionType.DEPOSIT,
Amount = amount,
Fee = 0,
Message = "Withdraw: " + amount,
CreateAt = DateTime.Now,
UpdatedAt = DateTime.Now,
Status = TransactionStatus.DONE
};
var stringCmdInsertTransaction = $"INSERT INTO `transactions`(`transactionCode`, `senderAccountNumber`, `receiverAccountNumber`, " +
$"`type`, `amount`, `fee`, `message`, `createAt`, `updatedAt`, `status`) " +
$"VALUES ('{shbtransaction.TransactionCode}', '{shbtransaction.SenderAccountNumber}', " +
$"'{shbtransaction.ReceiverAccountNumber}', {(int)shbtransaction.Type}," +
$"{shbtransaction.Amount}, {shbtransaction.Fee}, '{shbtransaction.Message}', " +
$"'{shbtransaction.CreateAt:yyyy-MM-dd hh:mm:ss}', '{shbtransaction.UpdatedAt: yyyy-MM-dd hh:mm:ss}'," +
$"{(int)shbtransaction.Status})";
var cmdInsertTransaction = new MySqlCommand(stringCmdInsertTransaction, cnn);
cmdInsertTransaction.ExecuteNonQuery();
transaction.Commit();
cnn.Close();
Console.WriteLine($"Rút {amount} thành công!");
return(true);
}
catch (Exception e)
{
Console.WriteLine(e);
transaction.Rollback();
cnn.Close();
}
return(false);
}
// 1. Gui tien
public bool Deposit(string accountNumber, double amount)
{
var cnn = ConnectionHelpers.GetConnection();
cnn.Open();
var transaction = cnn.BeginTransaction();
try
{
if (amount <= 0)
{
throw new Exception("Giá trị không hợp lệ");
}
var stringCmdGetAccount = $"select balance from `accounts` where accountNumber = '{accountNumber}'" +
$"and status = 1";
var cmdGetAccount = new MySqlCommand(stringCmdGetAccount, cnn);
var accountReader = cmdGetAccount.ExecuteReader();
if (!accountReader.Read())
{
throw new Exception("Không tìm thấy tài khoản hoặc tài khoản đã bị khóa!");
}
var currentBalance = accountReader.GetDouble("balance");
accountReader.Close();
currentBalance += amount;
var stringCmdUpdateAccount = $"update `accounts` set balance = {currentBalance} where " +
$"accountNumber = '{accountNumber}' and status = 1";
var cmdUpdateAccount = new MySqlCommand(stringCmdUpdateAccount, cnn);
cmdUpdateAccount.ExecuteNonQuery();
var shbTransaction = new SHBTransaction()
{
TransactionCode = Guid.NewGuid().ToString(),
SenderAccountNumber = accountNumber,
ReceiverAccountNumber = accountNumber,
Type = TransactionType.DEPOSIT,
Amount = amount,
Fee = 0,
Message = "Deposit " + amount,
CreateAt = DateTime.Now,
UpdatedAt = DateTime.Now,
Status = TransactionStatus.DONE
};
var stringCmdInsertTransaction =
$"INSERT INTO `transactions`(`transactionCode`, `senderAccountNumber`, `receiverAccountNumber`, " +
$"`type`, `amount`, `fee`, `message`, `createAt`, `updatedAt`, `status`) " +
$"VALUES ('{shbTransaction.TransactionCode}', '{shbTransaction.SenderAccountNumber}', " +
$"'{shbTransaction.ReceiverAccountNumber}', {(int)shbTransaction.Type}," +
$"{shbTransaction.Amount}, {shbTransaction.Fee}, '{shbTransaction.Message}', " +
$"'{shbTransaction.CreateAt:yyyy-MM-dd hh:mm:ss}', '{shbTransaction.UpdatedAt: yyyy-MM-dd hh:mm:ss}'," +
$"{(int)shbTransaction.Status})";
var cmdInsertTransaction = new MySqlCommand(stringCmdInsertTransaction, cnn);
cmdInsertTransaction.ExecuteNonQuery();
transaction.Commit();
cnn.Close();
Console.WriteLine("Gửi tiền vào tài khoản thành công!");
return(true);
}
catch (Exception e)
{
Console.WriteLine(e);
transaction.Rollback();
cnn.Close();
}
return(false);
}
// 7. lock and unlock user
public bool ChangStatusUser(string accountNumber, int status)
{
if (status == 1)
{
Console.WriteLine($"Tài khoản {accountNumber} đang hoạt động, bạn có muốn khóa tài khoản này không?");
Console.WriteLine("1. Có.");
Console.WriteLine("2. Không.");
Console.WriteLine("Vui lòng nhập lựa chọn của bạn: ");
var choice = int.Parse(Console.ReadLine());
switch (choice)
{
case 1:
try
{
var cnn = ConnectionHelpers.GetConnection();
cnn.Open();
var stringCmdGetAccount =
$"SELECT * FROM `accounts` WHERE accountNumber = '{accountNumber}'";
var cmdGetAccount = new MySqlCommand(stringCmdGetAccount, cnn);
var accountReader = cmdGetAccount.ExecuteReader();
if (!accountReader.Read())
{
throw new Exception("Không tìm thấy tài khoản hoặc tài khoản đã bị xóa!");
}
accountReader.Close();
var updateStatus = $"UPDATE `accounts` SET `status`= {(int) AccountStatus.LOCK} WHERE accountNumber = '{accountNumber}'";
var cmdStatus = new MySqlCommand(updateStatus, cnn);
cmdStatus.ExecuteNonQuery();
cnn.Close();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e);
return(false);
}
case 2:
Console.WriteLine("Hủy thao tác!");
break;
default:
Console.WriteLine("Giá trị không phù hợp");
break;
}
}
else if (status == 2)
{
Console.WriteLine($"Tài khoản {accountNumber} đang bị khóa, bạn có muốn mở khóa tài khoản này không?");
Console.WriteLine("1. Có.");
Console.WriteLine("2. Không.");
var choice = int.Parse(Console.ReadLine());
switch (choice)
{
case 1:
try
{
var cnn = ConnectionHelpers.GetConnection();
cnn.Open();
var stringCmdGetAccount =
$"SELECT * FROM `accounts` WHERE accountNumber = '{accountNumber}'";
var cmdGetAccount = new MySqlCommand(stringCmdGetAccount, cnn);
var accountReader = cmdGetAccount.ExecuteReader();
if (!accountReader.Read())
{
throw new Exception("Không tìm thấy tài khoản hoặc tài khoản đã bị xóa!");
}
accountReader.Close();
var updateStatus = $"UPDATE `accounts` SET `status`= {(int) AccountStatus.ACTIVE} WHERE accountNumber = '{accountNumber}'";
var cmdStatus = new MySqlCommand(updateStatus, cnn);
cmdStatus.ExecuteNonQuery();
cnn.Close();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e);
return(false);
}
case 2:
Console.WriteLine("Hủy thao tác!");
break;
default:
Console.WriteLine("Giá trị không phù hợp");
break;
}
}
else
{
Console.WriteLine("Tài khoản đã bị xóa!");
}
return(false);
}