// trả về lịch sử giao dịch khi nhập ngày vào
public static List <Transaction> GetTransactionByDate(string[] startDate, string[] endDate)
{
DbConnection.Instance().OpenConnection();
var listTransaction = new List <Transaction>();
var sqlQuery =
"SELECT * FROM `transactions` WHERE (`receiverAccountNumber` = @accountnumber OR `senderAccountNumber` = @accountnumber) AND `createdAt` BETWEEN @startdate and @enddate ORDER BY `createdAt` DESC";
var cmd = new MySqlCommand(sqlQuery, DbConnection.Instance().Connection);
cmd.Parameters.AddWithValue("@accountnumber", Program.currentLoggedIn.AccountNumber);
cmd.Parameters.AddWithValue("@startdate", startDate[0] + "-" + startDate[1] + "-" + startDate[2] + "*");
cmd.Parameters.AddWithValue("@enddate", endDate[0] + "-" + endDate[1] + "-" + endDate[2] + "*");
var transactionReader = cmd.ExecuteReader();
while (transactionReader.Read())
{
// khi đọc nếu trùng ngày thang năm thì tạo ra lịch sử giao dịch với thông tin ở đó add vào
// rồi cho vào listtransaction.
Transaction transaction = new Transaction()
{
Amount = transactionReader.GetDecimal("amount"),
Content = transactionReader.GetString("content"),
Id = transactionReader.GetString("id"),
Type = (Transaction.TransactionType)transactionReader.GetInt32("type"),
SenderAccountNumber = transactionReader.GetString("senderAccountNumber"),
ReceiverAccountNumber = transactionReader.GetString("receiverAccountNumber"),
Status = (Transaction.ActiveStatus)transactionReader.GetInt32("status"),
CreatedAt = transactionReader.GetMySqlDateTime("createdAt").ToString()
};
listTransaction.Add(transaction);
}
DbConnection.Instance().CloseConnection();
return(listTransaction);
}
// trả về 1 danh sách lịch sử giao dịch trong 10 ngày gần nhất
public static List <Transaction> GetTransactionsIn10Days()
{
DbConnection.Instance().OpenConnection();
var listTransaction = new List <Transaction>();
// trong câu lệnh này đã tính 10 ngày.
var sqlQuery =
"SELECT * FROM `transactions` WHERE (`receiverAccountNumber` = @accountnumber OR `senderAccountNumber` = @accountnumber) AND `createdAt` BETWEEN adddate(now(),-10) and now() ORDER BY `createdAt` DESC";
var cmd = new MySqlCommand(sqlQuery, DbConnection.Instance().Connection);
cmd.Parameters.AddWithValue("@accountnumber", Program.currentLoggedIn.AccountNumber);
var transactionReader = cmd.ExecuteReader();
while (transactionReader.Read())
{
Transaction transaction = new Transaction()
{
Amount = transactionReader.GetDecimal("amount"),
Content = transactionReader.GetString("content"),
Id = transactionReader.GetString("id"),
Type = (Transaction.TransactionType)transactionReader.GetInt32("type"),
SenderAccountNumber = transactionReader.GetString("senderAccountNumber"),
ReceiverAccountNumber = transactionReader.GetString("receiverAccountNumber"),
Status = (Transaction.ActiveStatus)transactionReader.GetInt32("status"),
CreatedAt = transactionReader.GetMySqlDateTime("createdAt").ToString()
};
listTransaction.Add(transaction);
}
DbConnection.Instance().CloseConnection();
return(listTransaction);
}
// trả về lịch sử giao ich với số tài khoản
public List <Transaction> getTransactionByAccountNumber(string accountNumber)
{
DbConnection.Instance().OpenConnection();
var listTransaction = new List <Transaction>();
var sqlQuery =
"select * from `transactions` where receiverAccountNumber = @accountnumber or senderAccountNumber = @accountnumber";
var cmd = new MySqlCommand(sqlQuery, DbConnection.Instance().Connection);
cmd.Parameters.AddWithValue("@accountnumber", accountNumber);
var transactionReader = cmd.ExecuteReader();
while (transactionReader.Read())
{
Transaction transaction = new Transaction()
{
Amount = transactionReader.GetDecimal("amount"),
Content = transactionReader.GetString("content"),
Id = transactionReader.GetString("id"),
Type = (Transaction.TransactionType)transactionReader.GetInt32("type"),
SenderAccountNumber = transactionReader.GetString("senderAccountNumber"),
ReceiverAccountNumber = transactionReader.GetString("receiverAccountNumber"),
Status = (Transaction.ActiveStatus)transactionReader.GetInt32("status"),
CreatedAt = transactionReader.GetMySqlDateTime("createdAt").ToString()
};
listTransaction.Add(transaction);
}
DbConnection.Instance().CloseConnection();
return(listTransaction);
}