public string BookSeat(DateTime date, string showTitle, int row, int number, string customerName)
{
string connectionString = ConfigurationManager.ConnectionStrings["DbOperaConnectionString"].ToString();
MapDbOperaDataContext dataContext = new MapDbOperaDataContext(connectionString);
// Get ID of the show that title is "showTitle".
int showID = Convert.ToInt32(dataContext.Shows.Where(showEntity => showEntity.Title == showTitle).First().ShowID);
// Get ID of the entertainment that refer to showID and will be performed on "date".
int entertainmentID = Convert.ToInt32(dataContext.Entertainments.Where(entertainmentEntity =>
entertainmentEntity.Date == date && entertainmentEntity.ShowID == showID).First().EntertainmentID);
// Get required seat.
var seat = dataContext.Seats.Where(seatEntity => seatEntity.EntertainmentID == entertainmentID &&
seatEntity.Row == row && seatEntity.Number == number).First();
if (seat.CustomerName == null)
{
seat.CustomerName = customerName;
dataContext.SubmitChanges();
return "success";
}
else
return "fail";
}
/// <summary>
/// Private method, ships number of open seats for particular entertainment.
/// </summary>
/// <param name="entertainmentID">
/// ID for investigating entertainment.
/// </param>
/// <returns>
/// Number of open seats.
/// </returns>
private int OpenSeat(int entertainmentID)
{
string connectionString = ConfigurationManager.ConnectionStrings["DbOperaConnectionString"].ToString();
MapDbOperaDataContext dataContext = new MapDbOperaDataContext(connectionString);
return dataContext.Seats.Where(seatEntity => seatEntity.EntertainmentID == entertainmentID &&
seatEntity.CustomerName == null).Count();
}
public ArrayList GetShowDetails(string showTitle)
{
ArrayList reply = new ArrayList();
string connectionString = ConfigurationManager.ConnectionStrings["DbOperaConnectionString"].ToString();
MapDbOperaDataContext dataContext = new MapDbOperaDataContext(connectionString);
// Identify particular play based on "showTitle".
var opera = dataContext.Shows.Where(showEntity => showEntity.Title == showTitle).First();
// Populate reply array with appropiate data.
reply.Add(opera.Conductor);
reply.Add(opera.Cast);
reply.Add(opera.Language);
reply.Add(opera.MusicBy);
reply.Add(opera.Orchestra);
reply.Add(opera.Libretto);
reply.Add(opera.Runtime.ToString());
return reply;
}
public ArrayList GetSeats(DateTime date, string showTitle)
{
ArrayList reply = new ArrayList();
string connectionString = ConfigurationManager.ConnectionStrings["DbOperaConnectionString"].ToString();
MapDbOperaDataContext dataContext = new MapDbOperaDataContext(connectionString);
// Get ID of the show that title is "showTitle".
int showID = Convert.ToInt32(dataContext.Shows.Where(showEntity => showEntity.Title == showTitle).First().ShowID);
// Get ID of the entertainment that refer to showID and will be performed on "date".
int entertainmentID = Convert.ToInt32(dataContext.Entertainments.Where(entertainmentEntity =>
entertainmentEntity.Date == date && entertainmentEntity.ShowID == showID).First().EntertainmentID);
// Get all seats that refer to entertainment.
var allSeats = dataContext.Seats.Where(seatEntity => seatEntity.EntertainmentID == entertainmentID);
// Extract data from each seat and populate with them final table.
foreach (var item in allSeats)
{
reply.Add(item.Row.ToString() + "," + item.Number.ToString());
if (item.CustomerName == null)
reply.Add("available");
else
reply.Add("unavailable");
reply.Add(item.Price.ToString());
}
return reply;
}
public ArrayList GetScheduleForDate(DateTime date)
{
ArrayList reply = new ArrayList();
// Border dates.
DateTime bottomDate = new DateTime(date.Year, date.Month, date.Day, 0, 0, 0),
upperDate = new DateTime(date.Year, date.Month, date.Day, 23, 59, 59);
string connectionString = ConfigurationManager.ConnectionStrings["DbOperaConnectionString"].ToString();
MapDbOperaDataContext dataContext = new MapDbOperaDataContext(connectionString);
// Collect all entertainments in "date".
var collectionOfEntertainments = dataContext.Entertainments.Where(collectionEntity =>
collectionEntity.Date >= bottomDate && collectionEntity.Date <= upperDate).OrderBy(x => x.Date);
// Populate the final array.
foreach (var item in collectionOfEntertainments)
{
// Get play that entertainment entity refered to.
var opera = dataContext.Shows.Where(operaEntity => operaEntity.ShowID == item.ShowID).First();
reply.Add(opera.Title);
reply.Add(item.Date);
reply.Add(opera.Conductor);
reply.Add(opera.Cast);
reply.Add(opera.Runtime);
reply.Add(OpenSeat(item.EntertainmentID));
}
return reply;
}
public ArrayList GetScheduleForAMonth(DateTime date)
{
ArrayList reply = new ArrayList();
string connectionString = ConfigurationManager.ConnectionStrings["DbOperaConnectionString"].ToString();
MapDbOperaDataContext dataContext = new MapDbOperaDataContext(connectionString);
DateTime bottomDate = new DateTime(date.Year, date.Month, 1, 0, 0, 0),
upperDate = bottomDate.AddMonths(1);
var collectionOfEnt = dataContext.Entertainments.Where(ent => ent.Date >= bottomDate && ent.Date <= upperDate).OrderBy(x => x.Date);
foreach (var item in collectionOfEnt)
{
string title = (dataContext.Shows.Where(showEntity => showEntity.ShowID == item.ShowID).Select(
searchedShow => searchedShow.Title).First());
DateTime dateOfEnt = item.Date;
int runtime = dataContext.Shows.Where(showEntity => showEntity.Title == title).Select(y => y.Runtime).First();
int openSeats = OpenSeat(dataContext.Entertainments.Where(entity => entity.Date == dateOfEnt && entity.ShowID == item.ShowID).Select(z => z.EntertainmentID).First());
reply.Add(title);
reply.Add(dateOfEnt);
reply.Add(runtime);
reply.Add(openSeats);
}
return reply;
}
public ArrayList Get3EarliestEntertainment(DateTime sinceDate)
{
ArrayList reply = new ArrayList();
string connectionString = ConfigurationManager.ConnectionStrings["DbOperaConnectionString"].ToString();
MapDbOperaDataContext dataContext = new MapDbOperaDataContext(connectionString);
var collectionOfEntertainment = dataContext.Entertainments.Where(entertainment => entertainment.Date >= sinceDate);
int counterOfEntertainments = 0;
foreach (var item in collectionOfEntertainment)
{
string title = (dataContext.Shows.Where(showEntity => showEntity.ShowID == item.ShowID).Select(
searchedShow => searchedShow.Title).First());
DateTime date = item.Date;
int runtime = dataContext.Shows.Where(showEntity => showEntity.Title == title).Select(y => y.Runtime).First();
int openSeats = OpenSeat(dataContext.Entertainments.Where(entity => entity.Date == date && entity.ShowID == item.ShowID).Select(z => z.EntertainmentID).First());
reply.Add(title);
reply.Add(date);
reply.Add(runtime);
reply.Add(openSeats);
counterOfEntertainments++;
if (counterOfEntertainments.Equals(3))
return reply;
}
return reply;
}