public static ClubCard GetCardByID(int clubCardID)
{
SQLiteConnection connection = new SQLiteConnection(DatabaseManager.CONNECTION_STRING);
SQLiteCommand command = new SQLiteCommand("SELECT * FROM clubcards WHERE clubcard_id=@itemID", connection);
command.Parameters.AddWithValue("@itemID", clubCardID);
try
{
connection.Open();
}
catch
{
return null;
}
SQLiteDataReader reader = command.ExecuteReader();
ClubCard card = null;
while (reader.Read())
{
card = new ClubCard();
card.CardID = clubCardID;
card.CustomerID = Convert.ToInt32(reader["customer_id"]);
card.Balance = Convert.ToSingle(reader["balance"]);
}
connection.Close();
return card;
}
public static bool PayByClubcard(Receipt receipt, ClubCard card)
{
SQLiteConnection connection = new SQLiteConnection(DatabaseManager.CONNECTION_STRING);
SQLiteCommand command = new SQLiteCommand(@"INSERT INTO clubcard_transactions(transaction_time,clubcard_id,receipt_it, amount)
VALUES(CURRENT_TIMESTAMP, @cardId, @receiptId, @amount);", connection);
command.Parameters.AddWithValue("@amount", receipt.Total);
command.Parameters.AddWithValue("@receiptId", receipt.ID);
command.Parameters.AddWithValue("@cardId", card.CardID);
try
{
connection.Open();
}
catch (SQLiteException ex)
{
return false;
}
try
{
command.ExecuteNonQuery();
}
catch (SQLiteException ex)
{
return false;
}
return true;
}
public static ClubCard CreateCard(Customer customer, float amount=0)
{
SQLiteConnection connection = new SQLiteConnection(DatabaseManager.CONNECTION_STRING);
SQLiteCommand command = new SQLiteCommand("INSERT INTO clubcards (customer_id, balance) VALUES (@customerId,@amount)", connection);
command.Parameters.AddWithValue("@customerId", customer.ID);
command.Parameters.AddWithValue("@amount", amount);
try
{
connection.Open();
}
catch
{
return null;
}
try
{
command.ExecuteNonQuery();
}
catch
{
return null;
}
command.CommandText = @"select last_insert_rowid()";
int cardID = 0;
try
{
cardID = Convert.ToInt32(command.ExecuteScalar());
}
catch { }
connection.Close();
ClubCard c = new ClubCard();
c.Balance = amount;
c.CardID = cardID;
c.CustomerID = customer.ID;
return c;
}