public List <LogDTO> getAllLog(string cardNo)
{
try {
List <LogDTO> arrLog = new List <LogDTO>();
string query = "select * from Log where CardNo = @cardNo";
ConnectDatabase.open();
SqlCommand cmd = new SqlCommand(query, ConnectDatabase.connect);
cmd.Parameters.AddWithValue("cardNo", cardNo);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
LogDTO log = new LogDTO(
dr["LogID"].ToString(),
DateTime.Parse(dr["LogDate"].ToString()),
Convert.ToInt32(dr["Amount"]),
dr["Details"].ToString(),
dr["CardNoTo"].ToString(),
dr["LogTypeID"].ToString(),
dr["ATMID"].ToString(),
dr["CardNo"].ToString());
arrLog.Add(log);
}
ConnectDatabase.close();
return(arrLog);
}
catch {
if (ConnectDatabase.CHECK_OPEN)
{
ConnectDatabase.close();
}
return(null);
}
}
public string getNameCustomer(string cardNo)
{
try
{
string name = "";
string query = "select Customer.Name from Account inner join Customer on Account.CustID = Customer.CustID inner join Card on Account.AccountID = Card.AccountID where CardNo = @cardNo";
ConnectDatabase.open();
SqlCommand cmd = new SqlCommand(query, ConnectDatabase.connect);
cmd.Parameters.AddWithValue("cardNo", cardNo);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
name = dr["Name"].ToString();
}
ConnectDatabase.close();
return(name);
}
catch {
if (ConnectDatabase.CHECK_OPEN)
{
ConnectDatabase.close();
}
return("");
}
}
public bool createLog(string logId, string logDate, int amount, string details, string cardNoTo, string logType, string atmID, string cardNo)
{
try
{
string query = "insert into Log values(@logId, @date,@amount,@details,@cardTo,@logType,@atmID,@cardNo)";
ConnectDatabase.open();
SqlCommand cmd1 = new SqlCommand(query, ConnectDatabase.connect);
cmd1.Parameters.AddWithValue("logId", logId);
cmd1.Parameters.AddWithValue("date", logDate);
cmd1.Parameters.AddWithValue("amount", amount);
cmd1.Parameters.AddWithValue("details", details);
cmd1.Parameters.AddWithValue("cardTo", cardNoTo);
cmd1.Parameters.AddWithValue("logType", logType);
cmd1.Parameters.AddWithValue("atmID", atmID);
cmd1.Parameters.AddWithValue("cardNo", cardNo);
cmd1.ExecuteNonQuery();
ConnectDatabase.close();
return(true);
}
catch {
if (ConnectDatabase.CHECK_OPEN)
{
ConnectDatabase.close();
}
return(false);
}
}
public string checkPIN(string cardNo)
{
try
{
string PIN = "";
string query = "select PIN from Card where CardNo=@cardNo";
ConnectDatabase.open();
SqlCommand cmd = new SqlCommand(query, ConnectDatabase.connect);
cmd.Parameters.AddWithValue("cardNo", cardNo);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
PIN = dr["PIN"].ToString();
}
ConnectDatabase.close();
return(PIN);
}
catch
{
if (ConnectDatabase.CHECK_OPEN)
{
ConnectDatabase.close();
}
return("");
}
}
public bool updateAttempt(string cardNo)
{
try
{
int count = 0;
string query = "select Attempt from Card where CardNo=@cardNo";
ConnectDatabase.open();
SqlCommand cmd = new SqlCommand(query, ConnectDatabase.connect);
cmd.Parameters.AddWithValue("cardNo", cardNo);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
count = Convert.ToInt32(dr["Attempt"]);
}
count++;
ConnectDatabase.close();
string query1 = "update Card set Attempt = @count where CardNo = @cardNo";
ConnectDatabase.open();
SqlCommand cmd1 = new SqlCommand(query1, ConnectDatabase.connect);
cmd1.Parameters.AddWithValue("count", count);
cmd1.Parameters.AddWithValue("cardNo", cardNo);
cmd1.ExecuteNonQuery();
ConnectDatabase.close();
return(true);
}
catch {
if (ConnectDatabase.CHECK_OPEN)
{
ConnectDatabase.close();
}
return(false);
}
}
public int getAttempt(string cardNo)
{
try
{
int count = 0;
string query = "select Attempt from Card where CardNo=@cardNo";
ConnectDatabase.open();
SqlCommand cmd = new SqlCommand(query, ConnectDatabase.connect);
cmd.Parameters.AddWithValue("cardNo", cardNo);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
count = Convert.ToInt32(dr["Attempt"]);
}
ConnectDatabase.close();
if (count == 3)
{
string query1 = "update Card set Status = 'block' where CardNo = @cardNo";
ConnectDatabase.open();
SqlCommand cmd1 = new SqlCommand(query1, ConnectDatabase.connect);
cmd1.Parameters.AddWithValue("cardNo", cardNo);
cmd1.ExecuteNonQuery();
ConnectDatabase.close();
}
return(count);
}
catch {
if (ConnectDatabase.CHECK_OPEN)
{
ConnectDatabase.close();
}
return(-1);
}
}
public string getExpiredDate(string cardNo)
{
try
{
string exDate = "";
string query = "select ExpiredDate from Card where CardNo=@cardNo";
ConnectDatabase.open();
SqlCommand cmd = new SqlCommand(query, ConnectDatabase.connect);
cmd.Parameters.AddWithValue("cardNo", cardNo);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
exDate = dr["ExpiredDate"].ToString();
}
ConnectDatabase.close();
return(exDate);
}
catch {
if (ConnectDatabase.CHECK_OPEN)
{
ConnectDatabase.close();
}
return("");
}
}
public int getBalance(string cardNo)
{
try {
int balance = -1;
string query = "select Account.Balance from Account inner join Card on Account.AccountID = Card.AccountID where CardNo = @cardNo";
ConnectDatabase.open();
SqlCommand cmd = new SqlCommand(query, ConnectDatabase.connect);
cmd.Parameters.AddWithValue("cardNo", cardNo);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
balance = Convert.ToInt32(dr["Balance"]);
}
ConnectDatabase.close();
return(balance);
}
catch
{
if (ConnectDatabase.CHECK_OPEN)
{
ConnectDatabase.close();
}
return(-1);
}
}
public bool compareBalance(int money, string cardNo)
{
try
{
int balance = -1;
string query = "select sum(ac.Balance+ov.Value) as Balance from Account as ac left join OverDraft ov on ac.ODID = ov.ODID left join Card as c on c.AccountID = ac.AccountID where CardNo = @cardNo group by ac.AccountID, c.AccountID, ov.ODID";
ConnectDatabase.open();
SqlCommand cmd = new SqlCommand(query, ConnectDatabase.connect);
cmd.Parameters.AddWithValue("cardNo", cardNo);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
balance = Convert.ToInt32(dr["Balance"]);
}
ConnectDatabase.close();
if (money <= balance)
{
return(true);
}
else
{
return(false);
}
}
catch
{
if (ConnectDatabase.CHECK_OPEN)
{
ConnectDatabase.close();
}
return(false);
}
}
public bool checkCardNo(string cardNo)
{
try
{
List <CardDTO> arrCard = new List <CardDTO>();
string query = "select * from Card where CardNo=@cardNo";
ConnectDatabase.open();
SqlCommand cmd = new SqlCommand(query, ConnectDatabase.connect);
cmd.Parameters.AddWithValue("cardNo", cardNo);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CardDTO card = new CardDTO(
dr["CardNo"].ToString(),
dr["PIN"].ToString(),
dr["Status"].ToString(),
dr["StartDate"].ToString(),
dr["ExpiredDate"].ToString(),
Convert.ToInt32(dr["Attempt"]),
dr["AccountID"].ToString());
arrCard.Add(card);
}
ConnectDatabase.close();
if (arrCard.Count == 0)
{
return(false);
}
else
{
return(true);
}
}
catch {
if (ConnectDatabase.CHECK_OPEN)
{
ConnectDatabase.close();
}
return(false);
}
}
public bool changePIN(string cardNo, string newPIN)
{
try
{
string query1 = "update Card set PIN = @newPIN where CardNo = @cardNo";
ConnectDatabase.open();
SqlCommand cmd1 = new SqlCommand(query1, ConnectDatabase.connect);
cmd1.Parameters.AddWithValue("newPIN", newPIN);
cmd1.Parameters.AddWithValue("cardNo", cardNo);
cmd1.ExecuteNonQuery();
ConnectDatabase.close();
return(true);
}
catch
{
if (ConnectDatabase.CHECK_OPEN)
{
ConnectDatabase.close();
}
return(false);
}
}
public bool updateBalanceTo(int money, string cardNo)
{
try
{
int balance = -1;
string query = "select Account.Balance from Account inner join Card on Account.AccountID = Card.AccountID where CardNo = @cardNo";
ConnectDatabase.open();
SqlCommand cmd = new SqlCommand(query, ConnectDatabase.connect);
cmd.Parameters.AddWithValue("cardNo", cardNo);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
balance = Convert.ToInt32(dr["Balance"]);
}
ConnectDatabase.close();
int newBalance = balance + money;
string queryUpdate = "update Account set Account.Balance = @newBalance from Account inner join Card on Account.AccountID = Card.AccountID where Card.CardNo = @cardNo ";
ConnectDatabase.open();
SqlCommand cmd1 = new SqlCommand(queryUpdate, ConnectDatabase.connect);
cmd1.Parameters.AddWithValue("newBalance", newBalance);
cmd1.Parameters.AddWithValue("cardNo", cardNo);
cmd1.ExecuteNonQuery();
ConnectDatabase.close();
return(true);
}
catch
{
if (ConnectDatabase.CHECK_OPEN)
{
ConnectDatabase.close();
}
return(false);
}
}
public bool updateQuantity(string atmId, int money, string moneyID)
{
try
{
int total = -1;
string query = "select Quantity from Stock where ATMID = @atmId and MoneyID = @moneyID";
ConnectDatabase.open();
SqlCommand cmd = new SqlCommand(query, ConnectDatabase.connect);
cmd.Parameters.AddWithValue("atmId", atmId);
cmd.Parameters.AddWithValue("moneyID", moneyID);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
total = Convert.ToInt32(dr["Quantity"]);
}
ConnectDatabase.close();
int newTotal = total - money;
string queryUpdate = "update Stock set Quantity = @newTotal where ATMID = @atmId and MoneyID = @moneyID";
ConnectDatabase.open();
SqlCommand cmd1 = new SqlCommand(queryUpdate, ConnectDatabase.connect);
cmd1.Parameters.AddWithValue("newTotal", newTotal);
cmd1.Parameters.AddWithValue("atmId", atmId);
cmd1.Parameters.AddWithValue("moneyID", moneyID);
cmd1.ExecuteNonQuery();
ConnectDatabase.close();
return(true);
}
catch
{
if (ConnectDatabase.CHECK_OPEN)
{
ConnectDatabase.close();
}
return(false);
}
}
public int getNumPerPage()
{
try
{
int number = -1;
string query = "select NumPerPage from Config";
ConnectDatabase.open();
SqlCommand cmd = new SqlCommand(query, ConnectDatabase.connect);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
number = Convert.ToInt32(dr["NumPerPage"]);
}
ConnectDatabase.close();
return(number);
}
catch {
if (ConnectDatabase.CHECK_OPEN)
{
ConnectDatabase.close();
}
return(-1);
}
}