public ListOfPlacesInHalls ReadAllPlacesOfAllHalls()
{
List <PlaceInHall> placesList = new List <PlaceInHall>();
SqlConnection connectToDateBase = new SqlConnection(pathOfDataBase);
using (connectToDateBase)
{
SqlCommand command = new SqlCommand(
"SELECT PLACE_ID, HALL_ID, ROW_PLACES, PLACE FROM [PLACESINHALLS];",
connectToDateBase);
connectToDateBase.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
PlaceInHall place = new PlaceInHall(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetInt32(3));
placesList.Add(place);
}
}
reader.Close();
}
ListOfPlacesInHalls places = new ListOfPlacesInHalls(placesList);
return(places);
}
/// <summary>
/// Конструктор для конвертации из объекта доменной модели
/// </summary>
public PlaceInHallDto(PlaceInHall placeInHall)
{
Id = placeInHall.Id;
Row = placeInHall.Row;
Column = placeInHall.Column;
UserId = placeInHall.UserId;
}
public void DeleteFromDatabase(PlaceInHall place)
{
SqlConnection connectToDateBase = new SqlConnection(pathOfDataBase);
using (connectToDateBase)
{
SqlCommand command = new SqlCommand(
("DELETE FROM [PLACESINHALLS] WHERE PLACE_ID = @PLACE_ID"),
connectToDateBase);
command.Connection.Open();
command.Parameters.AddWithValue("@PLACE_ID", place.PlaceId);
command.ExecuteNonQuery();
}
}
public void WriteInDatabase(PlaceInHall place)
{
SqlConnection connectToDateBase = new SqlConnection(pathOfDataBase);
using (connectToDateBase)
{
SqlCommand command = new SqlCommand(
("INSERT INTO [PLACESINHALLS] (HALL_ID, ROW_PLACES, PLACE)" +
"VALUES (@HALL_ID, @ROW_PLACES, @PLACES);"),
connectToDateBase);
command.Connection.Open();
command.Parameters.AddWithValue("@HALL_ID", place.HallId);
command.Parameters.AddWithValue("@ROW_PLACES", place.Row);
command.Parameters.AddWithValue("@PLACE", place.PlaceNumber);
command.ExecuteNonQuery();
}
}
public ListOfTickets ReadTickets()
{
Hall hall;
Film film;
Session session;
PlaceInHall place;
List <Ticket> ticketsList = new List <Ticket>();
SqlConnection connectToDateBase = new SqlConnection(pathOfDataBase);
using (connectToDateBase)
{
SqlCommand command = new SqlCommand(
"SELECT TICKET_ID, [TICKETSALES].SESSION_ID, [TICKETSALES].PLACE_ID, AGE_VISITOR, TOTAL_COST, " + // 4
"[SESSIONS].SESSION_ID, [SESSIONS].HALL_ID, [SESSIONS].[FILM_ID], DATE_SESSION, TIME_SESSION, " + // 9
"NAME_HALL, SEATING_CAPACITY, PLACES_IN_ROW, " + //12
"[PLACESINHALLS].PLACE_ID, ROW_PLACES, PLACE, " + //15
"NAME_FILM, LENGTH_FILM, AGE_LIMIT, TICKET_PRICE " + //19
"FROM [TICKETSALES], [SESSIONS], [PLACESINHALLS], [HALLS], [FILMS]" +
"WHERE [TICKETSALES].SESSION_ID = [SESSIONS].SESSION_ID AND [TICKETSALES].PLACE_ID = [PLACESINHALLS].PLACE_ID " +
"AND [SESSIONS].HALL_ID = [HALLS].HALL_ID AND [SESSIONS].[FILM_ID] = [FILMS].[FILM_ID] " +
"AND [PLACESINHALLS].HALL_ID = [HALLS].HALL_ID;",
connectToDateBase);
connectToDateBase.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
hall = new Hall(reader.GetInt32(6), reader.GetString(10), reader.GetInt32(11), reader.GetInt32(12));
place = new PlaceInHall(reader.GetInt32(13), reader.GetInt32(6), reader.GetInt32(14), reader.GetInt32(15));
film = new Film(reader.GetInt32(7), reader.GetString(16), reader.GetInt32(17),
reader.GetInt32(18), reader.GetInt32(19));
session = new Session(reader.GetInt32(5), film, hall, reader.GetDateTime(8), reader.GetTimeSpan(9));
Ticket ticket = new Ticket(reader.GetInt32(0), session, place.PlaceNumber, place.Row, reader.GetDateTime(3), reader.GetInt32(4));
ticketsList.Add(ticket);
}
}
reader.Close();
}
ListOfTickets tickets = new ListOfTickets(ticketsList);
return(tickets);
}
public void UpdateInDatabase(PlaceInHall place)
{
SqlConnection connectToDateBase = new SqlConnection(pathOfDataBase);
using (connectToDateBase)
{
SqlCommand command = new SqlCommand(
("UPDATE [PLACESINHALLS]" +
"SET HALL_ID = @HALL_ID, ROW_PLACES = @ROW_PLACES, PLACE = @PLACE" +
" WHERE @PLACE_ID = PLACE_ID;"),
connectToDateBase);
command.Connection.Open();
command.Parameters.AddWithValue("@PLACE_ID", place.PlaceId);
command.Parameters.AddWithValue("@HALL_ID", place.HallId);
command.Parameters.AddWithValue("@ROW_PLACES", place.Row);
command.Parameters.AddWithValue("@PLACE", place.PlaceNumber);
command.ExecuteNonQuery();
}
}
private void FillingPlaces(Hall hall)
{
int hallId = hall.HallId;
int seatingCapacity = hall.SeatingCapacity;
int placesInRowLimit = hall.PlacesInRow;
WritingInDatabase writing = new WritingInDatabase();
int placeId = placesList.Places.Count() + 1;
int rows = 0;
bool notEven = false;
int leftPlaces = seatingCapacity % placesInRowLimit;
if (leftPlaces != 0)
{
rows = (seatingCapacity / placesInRowLimit) + 1;
notEven = true;
}
else
{
rows = seatingCapacity / placesInRowLimit;
}
for (int i = 1; i <= rows; i++)
{
if (i == rows && notEven)
{
placesInRowLimit = leftPlaces;
}
for (int j = 1; j <= placesInRowLimit; j++)
{
PlaceInHall place = new PlaceInHall(placeId, hallId, i, j);
hall.Places.Add(place);
placesList.Places.Add(place);
placeId++;
}
}
writing.WriteInDatabase(hall.Places);
}
/// <summary>
/// Конструктор для конвертации из объекта доменной модели
/// </summary>
public PlaceDto(PlaceInHall placeInHall)
{
Id = placeInHall.Id;
Row = placeInHall.Row;
Column = placeInHall.Column;
}
