public IEnumerable<Showtime> GetPendingShowtimes(int movieId)
{
var executor = new CommandExecutor("dbo.BrowsePendingShowtimes", connectionString);
executor.SetParam("@MovieId", movieId, SqlDbType.Int);
var result = executor.ExecuteCommand();
result.ThrowIfException();
var dataSet = result as DataSet;
foreach (DataRow row in dataSet.Tables[0].Rows)
{
yield return new Showtime
{
Id = row["Id"].ToInt(),
Time = row["ShowtimeDate"].ToDate(),
Movie = new Movie
{
Id = row["MovieId"].ToInt(),
Title = row["MovieTitle"].ToString()
},
Auditorium = new Auditorium
{
Id = row["AuditoriumId"].ToInt(),
Name = row["AuditoriumName"].ToString(),
Rows = row["AuditoriumRows"].ToInt(),
Seats = row["AuditoriumSeats"].ToInt()
},
Price = row["Price"].ToInt(),
ThreeDee = row["ThreeDee"].ToBool()
};
}
}
public IEnumerable<Ticket> GetTickets()
{
var executor = new CommandExecutor("dbo.BrowseTickets", connectionString);
var result = executor.ExecuteCommand();
result.ThrowIfException();
var dataSet = result as DataSet;
foreach (DataRow row in dataSet.Tables[0].Rows)
{
yield return new Ticket
{
Id = row["Id"].ToInt(),
Seat = new Seat
{
SeatNumber = row["SeatNumber"].ToInt(),
RowNumber = row["RowNumber"].ToInt(),
},
Showtime = new Showtime
{
Auditorium = new Auditorium
{
Name = row["Name"].ToString()
},
Movie = new Movie
{
Title = row["Title"].ToString()
},
Time = row["ShowtimeDate"].ToDate()
},
SellDate = row["SellDate"].ToDate()
};
}
}
public void Delete(Ticket ticket)
{
var executor = new CommandExecutor("dbo.DeleteTicket", connectionString);
executor.SetParam("@Id", ticket.Id, SqlDbType.Int);
executor.ExecuteCommand(true).ThrowIfException();
}
public IEnumerable<User> GetUsers()
{
var executor = new CommandExecutor("dbo.BrowseUsers", connectionString);
var result = executor.ExecuteCommand();
result.ThrowIfException();
var dataSet = result as DataSet;
foreach (DataRow row in dataSet.Tables[0].Rows)
{
var type = UserType.User;
switch (row["Role"].ToInt())
{
case 1:
type = UserType.User;
break;
case 2:
type = UserType.Admin;
break;
case 3:
type = UserType.Superadmin;
break;
}
yield return new User
{
Login = row["User"].ToString(),
Type = type
};
}
}
public void Delete(Auditorium auditorium)
{
var executor = new CommandExecutor("dbo.DeleteAuditorium", connectionString);
executor.SetParam("@Id", auditorium.Id, SqlDbType.Int);
executor.ExecuteCommand(true).ThrowIfException();
}
public void Delete(Showtime showtime)
{
var executor = new CommandExecutor("dbo.DeleteShowtime", connectionString);
executor.SetParam("@Id", showtime.Id, SqlDbType.Int);
executor.ExecuteCommand(true).ThrowIfException();
}
public void SetLogo(BitmapImage logo)
{
var executor = new CommandExecutor("dbo.SetLogo", connectionString);
var logoData = logo == null ? DBNull.Value : (object)logo.ToByteArray();
executor.SetParam("@Logo", logoData, SqlDbType.Image);
executor.ExecuteCommand(true).ThrowIfException();
}
public void Delete(User user)
{
var executor = new CommandExecutor("dbo.DeleteUser", connectionString);
executor.SetParam("@Username", user.Login, SqlDbType.NVarChar);
var result = executor.ExecuteCommand();
result.ThrowIfException();
}
public void Create(User user)
{
var executor = new CommandExecutor("dbo.CreateUser", connectionString);
executor.SetParam("@Username", user.Login, SqlDbType.NVarChar);
executor.SetParam("@Password", user.Password, SqlDbType.NVarChar);
var userType = (int) user.Type + 1;
executor.SetParam("@Usertype", userType, SqlDbType.Int);
var result = executor.ExecuteCommand();
result.ThrowIfException();
}
private void CheckConnectionWorkerOnDoWork(object sender, DoWorkEventArgs doWorkEventArgs)
{
var connectionString = ConnectionStringBuilder.Build(Server, Database, User, Password);
var executor = new CommandExecutor("dbo.CurrentRole", connectionString);
var result = executor.ExecuteCommand();
view.Dispatcher.Invoke(() =>
{
view.IndicateConnectingFinished();
var exception = result as Exception;
if (exception != null)
{
MessageBox.Show(exception.Message);
return;
}
try
{
var userTypeId = (result as DataSet).Tables[0].Rows[0].ItemArray[0].ToInt();
Settings.Default.currentRole = (UserType) (userTypeId - 1);
if (Settings.Default.currentRole == UserType.User)
{
throw new Exception();
}
}
catch(Exception ex)
{
MessageBox.Show(Resources.InvalidUserMessageText);
return;
}
Settings.Default.server = Server;
Settings.Default.database = Database;
Settings.Default.user = User;
Settings.Default.password = Password;
Settings.Default.Save();
view.IndicateSuccess();
});
}
public IEnumerable<Seat> GetOccupiedSeats(int showtimeId)
{
var executor = new CommandExecutor("dbo.GetOccupiedSeats", connectionString);
executor.SetParam("@ShowtimeId", showtimeId, SqlDbType.Int);
var result = executor.ExecuteCommand();
result.ThrowIfException();
var dataSet = result as DataSet;
foreach (DataRow row in dataSet.Tables[0].Rows)
{
yield return new Seat
{
SeatNumber = row["SeatNumber"].ToInt(),
RowNumber = row["RowNumber"].ToInt()
};
}
}
public IEnumerable<Auditorium> GetAuditoriums()
{
var executor = new CommandExecutor("dbo.BrowseAuditoriums", connectionString);
var result = executor.ExecuteCommand();
result.ThrowIfException();
var dataSet = result as DataSet;
foreach (DataRow row in dataSet.Tables[0].Rows)
{
yield return new Auditorium
{
Id = row["Id"].ToInt(),
Name = row["Name"].ToString(),
Rows = row["RowsNumber"].ToInt(),
Seats = row["SeatsNumber"].ToInt()
};
}
}
public IEnumerable<LogEntry> GetLogEntries()
{
var executor = new CommandExecutor("dbo.BrowseLogs", connectionString);
var result = executor.ExecuteCommand();
result.ThrowIfException();
var dataSet = result as DataSet;
foreach (DataRow row in dataSet.Tables[0].Rows)
{
yield return new LogEntry
{
Id = row["Id"].ToInt(),
Date = row["Date"].ToDate(),
User = row["User"].ToString(),
OperationType = (OperationType) (row["OperationType"].ToInt() + 1),
EntitiyId = row["EntityId"].ToNullableInt(),
TableName = row["EntityTable"].ToString()
};
}
}
public BitmapImage GetLogo()
{
var executor = new CommandExecutor("dbo.GetLogo", connectionString);
var result = executor.ExecuteCommand();
result.ThrowIfException();
var dataSet = result as DataSet;
var imageData = dataSet.Tables[0].Rows[0][0];
if (imageData == DBNull.Value)
{
return null;
}
var posterBytes = (byte[]) imageData;
var image = new BitmapImage();
image.BeginInit();
image.StreamSource = new MemoryStream(posterBytes);
image.EndInit();
return image;
}
public void RegisterTickets(int showtimeId, List<Seat> seats)
{
var executor = new CommandExecutor("dbo.RegisterTickets", connectionString);
executor.SetParam("@ShowtimeId", showtimeId, SqlDbType.Int);
var seatList = new DataTable();
seatList.Columns.Add("SeatNumber");
seatList.Columns.Add("RowNumber");
seats.ForEach(seat => seatList.Rows.Add(seat.SeatNumber, seat.RowNumber));
executor.SetParam("@Tickets", seatList, SqlDbType.Structured, "dbo.SeatList");
executor.ExecuteCommand(true).ThrowIfException();
}
public void Save(Showtime showtime, bool update)
{
CommandExecutor executor;
if (update)
{
executor = new CommandExecutor("dbo.UpdateShowtime", connectionString);
executor.SetParam("@Id", showtime.Id, SqlDbType.Int);
}
else
{
executor = new CommandExecutor("dbo.CreateShowtime", connectionString);
}
executor.SetParam("@MovieId", showtime.Movie.Id, SqlDbType.Int);
executor.SetParam("@AuditoriumId", showtime.Auditorium.Id, SqlDbType.Int);
executor.SetParam("@ShowtimeDate", showtime.Time, SqlDbType.DateTime);
executor.SetParam("@Price", showtime.Price, SqlDbType.Money);
executor.SetParam("@ThreeDee", showtime.ThreeDee, SqlDbType.Bit);
executor.ExecuteCommand(true).ThrowIfException();
}
public void Save(Auditorium auditorium, bool update)
{
CommandExecutor executor;
if (update)
{
executor = new CommandExecutor("dbo.UpdateAuditorium", connectionString);
executor.SetParam("@Id", auditorium.Id, SqlDbType.Int);
}
else
{
executor = new CommandExecutor("dbo.CreateAuditorium", connectionString);
}
executor.SetParam("@Name", auditorium.Name, SqlDbType.NVarChar);
executor.SetParam("@Rows", auditorium.Rows, SqlDbType.Int);
executor.SetParam("@Seats", auditorium.Seats, SqlDbType.Int);
executor.ExecuteCommand(true).ThrowIfException();
}