public ActionResult Create(TrainTrip trip)
{
var db = new RzdTicketsDb();
db.InsertTrip(trip);
db.InsertTickets(trip.Id, 16, 37, 3487);
return RedirectToAction("Index");
}
private void SeedDefaultData()
{
using (var connection = OpenDbConnection())
{
if (GetStationsCount(connection) == 0)
{
var stations = new Station[]
{
new Station { Name = "Москва Ленинградская" },
new Station { Name = "Москва Казанская" },
new Station { Name = "Москва Павелецкая" },
new Station { Name = "Санкт-Петербург Ладож." },
new Station { Name = "Казань" },
new Station { Name = "Воронеж" },
new Station { Name = "Нижний Новгород" },
new Station { Name = "Адлер" },
};
foreach (var s in stations)
{
InsertStation(s);
}
var trips = new TrainTrip[]
{
new TrainTrip
{
DepartureStation = stations[3],
ArrivalStation = stations[0],
DepartureTime = new DateTime(2015, 12, 10, 0, 11, 0),
ArrivalTime = new DateTime(2015, 12, 10, 0, 11, 0)
},
new TrainTrip
{
DepartureStation = stations[4],
ArrivalStation = stations[1],
DepartureTime = new DateTime(2015, 12, 14, 19, 45, 0),
ArrivalTime = new DateTime(2015, 12, 15, 7, 10, 0)
}
};
foreach (var t in trips)
{
InsertTrip(t);
InsertTickets(t.Id, wagonsCount: 16, seatsCountPerWagon: 37, cost: 4554);
}
}
}
}
private TrainTrip ReadTrip(SqlDataReader reader)
{
var trip = new TrainTrip();
trip.Id = (int)reader["TripId"];
trip.DepartureTime = (DateTime)reader["DepartureTime"];
trip.ArrivalTime = (DateTime)reader["ArrivalTime"];
trip.DepartureStation = new Station();
trip.DepartureStation.Id = (int)reader["DepartureStationId"];
trip.DepartureStation.Name = (string)reader["DepartureStationName"];
trip.ArrivalStation = new Station();
trip.ArrivalStation.Id = (int)reader["ArrivalStationId"];
trip.ArrivalStation.Name = (string)reader["ArrivalStationName"];
return trip;
}
private Ticket ReadTicket(SqlDataReader reader, TrainTrip trip)
{
var res = new Ticket();
res.Id = (int)reader["TicketId"];
res.Trip = trip;
res.Cost = (double)reader["TicketCost"];
res.BookingTime = reader["TicketBookingTime"] == DBNull.Value ? null : (DateTime?)reader["TicketBookingTime"];
res.SeatNumber = (int)reader["TicketSeatNumber"];
res.WagonNumber = (int)reader["TicketWagonNumber"];
res.Passenger = ReadCustomer(reader);
return res;
}
internal void UpdateTrip(TrainTrip trip)
{
using (var connection = OpenDbConnection())
using (var command = connection.CreateCommand())
{
command.CommandText = @"
UPDATE TrainTrips
SET
DepartureStationId = @did,
DepartureTime = @dt,
ArrivalStationId = @aid,
ArrivalTime = @at)
WHERE Id = @id";
command.Parameters.AddWithValue("@id", trip.Id);
command.Parameters.AddWithValue("@did", trip.DepartureStation.Id);
command.Parameters.AddWithValue("@dt", trip.DepartureTime);
command.Parameters.AddWithValue("@aid", trip.ArrivalStation.Id);
command.Parameters.AddWithValue("@at", trip.ArrivalTime);
command.ExecuteNonQuery();
}
}
public void InsertTrip(TrainTrip trip)
{
using (var connection = OpenDbConnection())
using (var command = connection.CreateCommand())
{
command.CommandText = @"
INSERT INTO TrainTrips(
DepartureStationId,
DepartureTime,
ArrivalStationId,
ArrivalTime)
OUTPUT INSERTED.Id
VALUES(@did, @dt, @aid, @at)";
command.Parameters.AddWithValue("@did", trip.DepartureStation.Id);
command.Parameters.AddWithValue("@dt", trip.DepartureTime);
command.Parameters.AddWithValue("@aid", trip.ArrivalStation.Id);
command.Parameters.AddWithValue("@at", trip.ArrivalTime);
trip.Id = (int)command.ExecuteScalar();
}
}
public Ticket[] GetTickets(TrainTrip trip)
{
var res = new List<Ticket>();
using (var connection = OpenDbConnection())
using (var command = connection.CreateCommand())
{
command.CommandText = @"
SELECT
t.Id AS TicketId,
t.TripId AS TicketTripId,
t.Cost AS TicketCost,
t.BookingTime AS TicketBookingTime,
t.SeatNumber AS TicketSeatNumber,
t.WagonNumber AS TicketWagonNumber,
c.Id AS CustomerId,
c.UserId AS CustomerUserId,
c.Surname AS CustomerSurname,
c.Name AS CustomerName,
c.Fathersname AS CustomerFathersname,
c.BirthDate AS CustomerBirthdate
FROM Tickets t
LEFT JOIN Customers c ON c.Id = t.CustomerId
WHERE t.TripId = @id";
command.Parameters.AddWithValue("@id", trip.Id);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
res.Add(ReadTicket(reader, trip));
}
}
}
return res.ToArray();
}
public TripDetailsViewModel(TrainTrip trip, Ticket[] tickets)
{
Trip = trip;
Tickets = tickets;
}
public ActionResult Edit(TrainTrip trip)
{
var db = new RzdTicketsDb();
db.UpdateTrip(trip);
return RedirectToAction("Index");
}