OpenSql();
SqlCommand com = new(sql, con);
com.Parameters.AddWithValue("FromTime", model.Time);
com.Parameters.AddWithValue("FromCity", model.FromCityCode);
com.Parameters.AddWithValue("ToCity", model.ToCityCode);
SqlDataAdapter daReader = new SqlDataAdapter();
daReader.SelectCommand = com;
DataSet dataSet = new();
daReader.Fill(dataSet, "OneFlight");
List<Model.StopFlight> list = new();
Model.StopFlight[] stopModel = new Model.StopFlight[2];
for(int i=0;i<stopModel.Length;i++)
{
stopModel[i] = new();
stopModel[i].Time = (TimeSpan)dataSet.Tables["OneFlight"].Rows[i]["Time"];
stopModel[i].EconomyPrice = Convert.ToDecimal(dataSet.Tables["OneFlight"].Rows[i]["EconomyPrice"]);
stopModel[i].FlightNumber = Convert.ToInt32(dataSet.Tables["OneFlight"].Rows[i]["FlightNumber"]);
stopModel[i].FlightTime = Convert.ToInt32(dataSet.Tables["OneFlight"].Rows[i]["FlightTime"]);
stopModel[i].DepartureAirportIATA = (string)dataSet.Tables["OneFlight"].Rows[i]["DepartureAirportIATA"];
stopModel[i].ArrivalAirportIATA = (string)dataSet.Tables["OneFlight"].Rows[i]["ArrivalAirportIATA"];
list.Add(stopModel[i]);
}
return list;
string sql = $"SELECT [Schedule].[Time],[Schedule].[EconomyPrice],[Schedule].[FlightNumber]," +
$"[Route].[FlightTime],[Route].[DepartureAirportIATA],[Route].[ArrivalAirportIATA] " +
$"FROM [dbo].[Schedule] LEFT JOIN [dbo].[Route] " +
$"ON [Schedule].[RouteId]=[Route].[RouteId] " +
$"WHERE [Schedule].[Date]=@FromTime AND [Schedule].[RouteId] " +
$"IN (SELECT [Route].[RouteId] FROM [Route] " +
$"WHERE [Route].[DepartureAirportIATA]=@FromCity " +
$"AND [Route].[ArrivalAirportIATA] " +
$"IN (SELECT [Route].[DepartureAirportIATA] " +
$"FROM [Route] WHERE [Route].[ArrivalAirportIATA]=@ToCity " +
$"AND [Route].[DepartureAirportIATA] " +
$"IN (SELECT [Route].[ArrivalAirportIATA] " +
$"FROM [Route] WHERE [Route].[DepartureAirportIATA]=@FromCity)))" +
$"UNION\n" +
$"SELECT [Schedule].[Time],[Schedule].[EconomyPrice],[Schedule].[FlightNumber]," +
$"[Route].[FLightTime],[Route].[DepartureAirportIATA]," +
$"[Route].[ArrivalAirportIATA] FROM [Schedule] LEFT JOIN [Route] " +
$"ON [Schedule].[RouteId]=[Route].[RouteId] " +
$"WHERE [Schedule].[Date]=@FromTime AND [Schedule].[RouteId] " +
$"IN (SELECT [Route].[RouteId] FROM [Route] " +
$"WHERE [Route].[ArrivalAirportIATA]=@FromCity AND [Route].[DepartureAirportIATA] " +
$"IN (SELECT [Route].[ArrivalAirportIATA] FROM [Route] WHERE [Route].[DepartureAirportIATA]=@ToCity))";