Автоматизированная система бронирования авиабилетов
FROM Flights,Aircrafts WHERE Flights.FlightID=@flightID
AND Flights.AircraftID=Aircrafts.AircraftID
ELSE
SELECT @count=Aircrafts.Count2
FROM Flights,Aircrafts WHERE Flights.FlightID=@flightID
AND Flights.AircraftID=Aircrafts.AircraftID
RETURN @count
END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Insert_FlightString]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- Вставка новой записи в таблицу Flight
CREATE PROCEDURE [dbo].[Insert_FlightString]
@dateDeparture datetime,
@dateArrival datetime,
@price1 decimal(18,0),
@price2 decimal(18,0),
@company int,
@aircraft int,
@cityDeparture int,
@cityArrival int
AS
BEGIN
INSERT INTO [Flights]
([DateDeparture],[DateArrival],
[Price1],[Price2],
[CompanyID],[AircraftID],
[CityDepartureID],[CityArrivalID])
VALUES (
@dateDeparture,@dateArrival,
@price1,@price2,
@company,@aircraft,
@cityDeparture,@cityArrival)
END
;
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Update_FlightString]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- Обновление записи в таблице Flights
CREATE PROCEDURE [dbo].[Update_FlightString]
@FlightID int,
@dateDeparture datetime,
@dateArrival datetime,
@price1 decimal(18,0),
@price2 decimal(18,0),
@companyID int,
@aircraftID int,
@cityDepartureID int,
@cityArrivalID int
AS
BEGIN
UPDATE Flights SET
dateDeparture = @dateDeparture,
dateArrival = @dateArrival,
price1 = @price1,
price2 = @price2,
cityDepartureID = @cityDepartureID,
cityArrivalID = @cityArrivalID,
companyID = @companyID,
aircraftID = @aircraftID
WHERE FlightID = @FlightID
END
;
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Delete_Order]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- Удаление записи из таблицы Orders по OrderID
CREATE PROCEDURE [dbo].[Delete_Order]
@orderID int
AS
BEGIN
DELETE FROM Orders WHERE [OrderID]=@orderID
END
;
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Delete_OrderByUserID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- Удаление записей из таблицы Orders по UserID
CREATE PROCEDURE [dbo].[Delete_OrderByUserID]
@UserID int
AS
BEGIN
DELETE FROM Orders WHERE [UserID]=@UserID
END
;
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FlightView]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[FlightView] AS
SELECT
FlightID,
DateDeparture,
DateArrival,
dbo.timeFlight(DateArrival,DateDeparture) AS TimeFlight,
CityDepartureID,
CityArrivalID,
DepartureCities.CityName AS CityDeparture,
ArrivalCities.CityName AS CityArrival,
CompanyName,
AircraftModel,
Price1,
Price2,
dbo.CountEmptyPlaces1cl(FlightID) AS EmptyPlace1cl,
dbo.CountEmptyPlaces2cl(FlightID) AS EmptyPlace2cl
FROM dbo.Flights INNER JOIN dbo.Companies
ON Group0703b.dbo.Flights.CompanyID = Group0703b.dbo.Companies.CompanyID
INNER JOIN Group0703b.dbo.Aircrafts
ON Group0703b.dbo.Flights.AircraftID = Group0703b.dbo.Aircrafts.AircraftID
LEFT OUTER JOIN Group0703b.dbo.Cities AS DepartureCities
ON Group0703b.dbo.Flights.CityDepartureID = DepartureCities.CityID
LEFT OUTER JOIN Group0703b.dbo.Cities AS ArrivalCities
ON Group0703b.dbo.Flights.CityArrivalID = ArrivalCities.CityID '
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Flights_Aircrafts]') AND parent_object_id = OBJECT_ID(N'[dbo].[Flights]'))
ALTER TABLE [dbo].[Flights] WITH CHECK ADD CONSTRAINT [FK_Flights_Aircrafts] FOREIGN KEY([AircraftID])
REFERENCES [dbo].[Aircrafts] ([AircraftID])
GO
ALTER TABLE [dbo].[Flights] CHECK CONSTRAINT [FK_Flights_Aircrafts]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Flights_Cities_Arrival]') AND parent_object_id = OBJECT_ID(N'[dbo].[Flights]'))
ALTER TABLE [dbo].[Flights] WITH CHECK ADD CONSTRAINT [FK_Flights_Cities_Arrival] FOREIGN KEY([CityArrivalID])
REFERENCES [dbo].[Cities] ([CityID])
GO
ALTER TABLE [dbo].[Flights] CHECK CONSTRAINT [FK_Flights_Cities_Arrival]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Flights_Cities_Departure]') AND parent_object_id = OBJECT_ID(N'[dbo].[Flights]'))
ALTER TABLE [dbo].[Flights] WITH CHECK ADD CONSTRAINT [FK_Flights_Cities_Departure] FOREIGN KEY([CityDepartureID])
REFERENCES [dbo].[Cities] ([CityID])
GO
ALTER TABLE [dbo].[Flights] CHECK CONSTRAINT [FK_Flights_Cities_Departure]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Flights_Companies]') AND parent_object_id = OBJECT_ID(N'[dbo].[Flights]'))
ALTER TABLE [dbo].[Flights] WITH CHECK ADD CONSTRAINT [FK_Flights_Companies] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[Companies] ([CompanyID])
GO
ALTER TABLE [dbo].[Flights] CHECK CONSTRAINT [FK_Flights_Companies]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Orders_Flights]') AND parent_object_id = OBJECT_ID(N'[dbo].[Orders]'))
ALTER TABLE [dbo].[Orders] WITH CHECK ADD CONSTRAINT [FK_Orders_Flights] FOREIGN KEY([FlightID])
REFERENCES [dbo].[Flights] ([FlightID])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Flights]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Orders_Users]') AND parent_object_id = OBJECT_ID(N'[dbo].[Orders]'))
ALTER TABLE [dbo].[Orders] WITH CHECK ADD CONSTRAINT [FK_Orders_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[Users] ([UserID])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Users]