private static void addTestData()
{
PlaysClass.addPlay("DeletedPlay", 0);
ShowingsClass.addShowing(1, "2017-06-24 17:00", 50, 25, 12.5);
CustomerClass.newCustomer("DeletedCustomerFirstName", "DeletedCustomerLastName", "", "");
//BookingsClass bookingClass = new BookingsClass();
//SeatBookingClass seatBookingClass = new SeatBookingClass("UpperCircle","A",10,1);
//List<SeatBookingClass> seatBookingClassList = new List<SeatBookingClass>();
//bookingClass.newBooking(1, "18/06/2017 12:00", 0, seatBookingClassList);
}
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);
}
private static void changeShowingsOfPlayToDeletedShowing(int pPlay_Id)
{
int deletedPlayId = getIdOfDeletePlayRecord();
DataSet showingsToDelete = ShowingsClass.getAllShowingsPastCurrentDate();
foreach (DataRow row in showingsToDelete.Tables[0].Rows)
{
ShowingsClass.deleteShowing(int.Parse(row["Showing_Id"].ToString()));
}
queryString = "UPDATE Showings Set Play_Id = @deletedPlayId WHERE Play_Id = @play_Id";
SQLiteConnection connection = new SQLiteConnection(SqlClassBase.getConnectionString());
SQLiteCommand command = new SQLiteCommand(queryString, connection);
connection.Open();
command.Parameters.AddWithValue("@play_Id", pPlay_Id);
command.Parameters.AddWithValue("@deletedPlayId", deletedPlayId);
command.ExecuteNonQuery();
connection.Close();
}
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);
}
