private Customer CreateCustomer(string userId, SqlConnection connection)
{
Customer res = new Customer
{
UserId = userId,
Surname = "",
Name = "",
Fathersname = "",
BirthDate = DateTime.Now,
PassportNumber = ""
};
using (var command = connection.CreateCommand())
{
command.CommandText = @"
INSERT INTO Customers(
UserId,
Surname,
Name,
Fathersname,
BirthDate,
PassportNumber)
OUTPUT INSERTED.Id
VALUES(@uid, @s, @n, @f, @bd, @p)";
command.Parameters.AddWithValue("@uid", userId);
command.Parameters.AddWithValue("@s", res.Surname);
command.Parameters.AddWithValue("@n", res.Name);
command.Parameters.AddWithValue("@f", res.Fathersname);
command.Parameters.AddWithValue("@bd", res.BirthDate);
command.Parameters.AddWithValue("@p", res.PassportNumber);
res.Id = (int)command.ExecuteScalar();
}
return res;
}
private Customer ReadCustomer(SqlDataReader reader)
{
if (reader["CustomerId"] == DBNull.Value)
return null;
Customer res = new Customer();
res.Id = (int)reader["CustomerId"];
res.UserId = (string)reader["CustomerUserId"];
res.Surname = (string)reader["CustomerSurname"];
res.Name = (string)reader["CustomerName"];
res.Fathersname = (string)reader["CustomerFathersname"];
res.BirthDate = (DateTime)reader["CustomerBirthdate"];
return res;
}
public Ticket[] GetTickets(Customer customer)
{
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,
tt.Id AS TripId,
tt.DepartureTime AS DepartureTime,
tt.ArrivalTime AS ArrivalTime,
sd.Id AS DepartureStationId,
sd.Name AS DepartureStationName,
sa.Id AS ArrivalStationId,
sa.Name AS ArrivalStationName
FROM Tickets t
LEFT JOIN Customers c ON c.Id = t.CustomerId
JOIN TrainTrips tt ON tt.Id = t.TripId
JOIN Stations sd ON sd.Id = tt.DepartureStationId
JOIN Stations sa ON sa.Id = tt.ArrivalStationId
WHERE t.CustomerId = @id";
command.Parameters.AddWithValue("@id", customer.Id);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var trip = ReadTrip(reader);
res.Add(ReadTicket(reader, trip));
}
}
}
return res.ToArray();
}
