public static void upgradeCustomer(int pCustomerId)
{
string memberShipExpiryDate = ShowingsClass.formatDateTimeToSqlLiteDateString(DateTime.Now.AddMonths(12));
queryString = "UPDATE Customers SET Membership_Expiry_Date = @membershipExpiryDate" +
" WHERE Customer_Id = @customerId";
SQLiteConnection connection = new SQLiteConnection(SqlClassBase.getConnectionString());
SQLiteCommand command = new SQLiteCommand(queryString, connection);
connection.Open();
command.Parameters.AddWithValue("@membershipExpiryDate", memberShipExpiryDate);
command.Parameters.AddWithValue("@customerId", pCustomerId);
command.ExecuteNonQuery();
connection.Close();
}
public static DataSet getCustomersMembershipExpiry()
{
string startDate = ShowingsClass.formatDateTimeToSqlLiteDateString(DateTime.Now);
string endDate = ShowingsClass.formatDateTimeToSqlLiteDateString(DateTime.Now.AddMonths(1));
DataSet dataSet = new DataSet();
string query = "Select * From Customers Where Membership_Expiry_Date BETWEEN date(@startDate) AND date(@endDate)";
using (SQLiteConnection connection = new SQLiteConnection(SqlClassBase.getConnectionString()))
{
SQLiteDataAdapter adapter = new SQLiteDataAdapter();
SQLiteCommand command = new SQLiteCommand(query, connection);
command.Parameters.AddWithValue("@startDate", startDate + " 00:00");
command.Parameters.AddWithValue("@endDate", endDate + " 24:00");
adapter.SelectCommand = command;
adapter.Fill(dataSet);
}
return(dataSet);
}
public static DataSet getAllCustomerBookingsForShowingsPastCurrentDate(int pCustomerId)
{
string currentDateString = ShowingsClass.formatDateTimeToSqlLiteDateString(DateTime.Now);
DataSet dataSet = new DataSet();
string queryString = "Select Bookings.Booking_Id From Bookings, Customers, Seats, Showings" +
" Where Bookings.Customer_Id = Customers.Customer_Id" +
" AND Bookings.Booking_Id = Seats.Booking_Id" +
" AND Seats.Showing_Id = Showings.Showing_Id" +
" AND Customers.Customer_Id = @customerId" +
" AND Showings.Date > date(@currentDate)";
using (SQLiteConnection connection = new SQLiteConnection(SqlClassBase.getConnectionString()))
{
SQLiteDataAdapter adapter = new SQLiteDataAdapter();
SQLiteCommand command = new SQLiteCommand(queryString, connection);
command.Parameters.AddWithValue("@currentDate", currentDateString);
command.Parameters.AddWithValue("@customerId", pCustomerId);
adapter.SelectCommand = command;
adapter.Fill(dataSet);
}
return(dataSet);
}
