USE Bus_Station;
GO

/*Получить данные (время, цену на билет) о рейсах в город Томск из города
Новосибирск.*/
SELECT Price, Bus_route.Time_Destination, Bus_route.Time_Arrival_Point
FROM Bus_service
    JOIN Bus_route ON Bus_route.ID = Bus_service.FK_Bus_route_ID
WHERE Destination_City_ID = (SELECT ID
    FROM City
    WHERE Name = 'Новосибирск') AND Arrival_City_ID = (SELECT ID
    FROM City
    WHERE Name = 'Томск')

--Получить суммарный доход какой-либо кассы.
IF OBJECT_ID('SumProfit', 'F') is NOT NULL
    DROP FUNCTION SumProfit;
GO

CREATE FUNCTION SumProfit (@busServiceID int)
RETURNS INT AS
BEGIN
    DECLARE @Price int = 0;
    SELECT @Price = @Price + Bus_service.Price
    FROM Buy_ticket JOIN
        Bus_service ON Bus_service.ID = Buy_ticket.FK_Bus_service_ID
    WHERE Buy_ticket.FK_Bus_office_id = @busServiceID
    RETURN @Price
END
GO

SELECT dbo.SumProfit(1);
GO

--Получить суммарные доходы касс в городе Томске.
DECLARE @Price int = 0;
SELECT @Price = @Price + Bus_service.Price
FROM Buy_ticket JOIN
    Bus_service ON Bus_service.ID = Buy_ticket.FK_Bus_service_ID
WHERE Buy_ticket.FK_Bus_office_id = (SELECT ID
FROM Bus_office
WHERE FK_City_ID = (SELECT ID
FROM City
WHERE Name = 'Томск'))
SELECT @Price AS Price

--Получить число рейсов, совершѐнных данным водителем за год.
IF OBJECT_ID('CountTripDriver', 'P') is NOT NULL
    DROP PROCEDURE CountTripDriver;
GO

CREATE PROCEDURE CountTripDriver
    @driver nvarchar(25)
AS
BEGIN
    SELECT COUNT(*) AS Count_Services
    FROM Bus_service
        JOIN Driver ON Driver.ID = Bus_service.FK_Driver_ID
    WHERE Driver.First_Name = @driver AND Bus_service.Departure_date > DATEADD(yy, -1, GETDATE())
END
GO

exec CountTripDriver 'Федоров';

--Назначить водителя на рейс.
UPDATE Bus_service
    SET FK_Driver_ID = 1 WHERE ID = 2;
GO

/*Оформить билет для клиента в город Новосибирск из Томска, с учѐтом занятых
мест в автобусе.*/

IF OBJECT_ID('insPassenger', 'P') is NOT NULL
    DROP PROCEDURE insPassenger;
GO

CREATE PROCEDURE insPassenger
    @Dest int,
    @Arriv int
AS
BEGIN
    DECLARE @TripBus int = (SELECT Bus_service.ID
    FROM Bus_service
        JOIN Bus_route ON Bus_route.ID = Bus_service.FK_Bus_route_ID
    WHERE Destination_City_ID = @Dest AND Arrival_City_ID = @Arriv AND Departure_date = '20210315'),
            @CountPlace int = 1,
            @Condition bit = 'true';

    WHILE (@Condition = 'true' AND @CountPlace <= (SELECT Count_place
        FROM Bus
        WHERE Num = (SELECT FK_Bus_Num
        FROM Bus_service
        WHERE ID = @TripBus)))
    BEGIN
        IF ((SELECT COUNT(ID)
        FROM Buy_ticket
        WHERE FK_Bus_service_ID = @TripBus
            AND Place = @CountPlace
            AND Passenger = 1
            AND Departure_date = '20210315') = 0)
        BEGIN
            INSERT Buy_ticket
            VALUES
                ((SELECT FK_Bus_Num
                    FROM Bus_service
                    WHERE ID = @TripBus), 1, @TripBus, '20210315', @CountPlace);
            SET @Condition = 'false'
        END
        SET @CountPlace = @CountPlace + 1;
    END
END
GO

exec insPassenger 1, 2;
GO

--Получить число рейсов, совершѐнных автобусами «Икарус» и «ПАЗ».
SELECT COUNT(*) AS Count_Services
FROM Bus_service
    JOIN Bus ON Bus.Num = Bus_service.FK_Bus_Num
WHERE Bus.Model IN ('ИКАРУС', 'ПАЗ')

--Проверить все свободные места на рейс.
IF OBJECT_ID('FreePlace', 'P') is NOT NULL
    DROP PROCEDURE FreePlace;
GO

CREATE PROCEDURE FreePlace
    @numberTrip int
AS
BEGIN
    DECLARE @CountPlace int = 1;
    WHILE (@CountPlace <= (SELECT Count_place
    FROM Bus
    WHERE Num = (SELECT FK_Bus_Num
    FROM Bus_service
    WHERE ID = @numberTrip)))
    BEGIN
        IF (@CountPlace NOT IN (SELECT Place
        FROM Buy_ticket
        WHERE FK_Bus_service_ID = @numberTrip))
            PRINT(@CountPlace)

        SET @CountPlace = @CountPlace + 1;
    END
END
GO

exec FreePlace 1;

--Показать число билетов, проданных кассами.
SELECT COUNT(*) AS CountTicket
FROM Buy_ticket

--Показать среднюю цену билетов.
SELECT SUM(Price)/COUNT(PRICE) AS AveragePrice
FROM Bus_service

--Показать рейсы, цена на которые выше средней цены на билеты.
SELECT FK_Bus_Num, Price, Departure_date, FK_Bus_route_ID
FROM Bus_service
WHERE Price > (SELECT SUM(Price)/COUNT(PRICE)
FROM Bus_service)
--GROUP BY Bus, Price, Departure_date, Bus_route

--Отменить клиенту билет, после того как он его вернул.
DELETE FROM Buy_ticket
    WHERE Passenger = (SELECT ID
FROM Passenger
WHERE First_Name = 'Шостакович' AND Second_Name = 'Дмитрий')

/*Показать информацию о рейсах из города Томск (включая цену на билет, время
рейса).*/
SELECT FK_Bus_Num, Price, Departure_date, Bus_route.Destination_City_ID, Bus_route.Arrival_City_ID, Bus_route.Time_Destination, Bus_route.Time_Arrival_Point
FROM Bus_service
    JOIN Bus_route ON Bus_route.ID = Bus_service.FK_Bus_route_ID
WHERE Bus_route.Destination_City_ID = (SELECT ID
FROM City
WHERE Name = 'Томск')

/*Посчитать количество билетов, проданных по рейсам Томск – Новосибирск за
2020-2021.*/
SELECT COUNT(Buy_ticket.ID) AS COUNT_TICKET
FROM Bus_service
    JOIN Buy_ticket ON Buy_ticket.FK_Bus_service_ID= Bus_service.ID
WHERE FK_Bus_route_ID IN
            (SELECT ID
    FROM Bus_route
    WHERE Destination_City_ID = 1 AND Arrival_City_ID = 2)
    AND '20220101' > Buy_ticket.Purchase_date
    AND Buy_ticket.Purchase_date >= '20200101'

--Назначить автобус на рейс.
IF OBJECT_ID('insBusTrip', 'P') is NOT NULL
    DROP PROCEDURE insBusTrip;
GO

CREATE PROCEDURE insBusTrip
    @Bus int,
    @Trip int
AS
BEGIN
    IF (SELECT Count_place
    FROM Bus
    WHERE Num = @Bus) >
       (SELECT MIN_QuantityPlace
    FROM Bus_route
    WHERE ID = (SELECT FK_Bus_route_ID
    FROM Bus_service
    WHERE ID = @Trip))
    BEGIN
        UPDATE Bus_service
            SET FK_Bus_Num = @Bus WHERE ID = @Trip;
    END
END
GO

exec insBusTrip 2, 3;

/*Получить список рейсов, на которые может быть назначен данный водитель и
данный автобус с числом мест более 30.*/
IF OBJECT_ID('TripBusDriver', 'V') is NOT NULL
    DROP VIEW TripBusDriver;
GO

CREATE VIEW TripBusDriver
AS
    SELECT Bus_service.ID, Bus_route.Destination_City_ID, Bus_route.Arrival_City_ID, Bus_route.MIN_QuantityPlace
    FROM Bus_service
        JOIN Bus_route ON Bus_route.ID = Bus_service.FK_Bus_route_ID
GO

IF OBJECT_ID('SeachTrip', 'P') is NOT NULL
    DROP PROCEDURE SeachTrip;
GO

CREATE PROCEDURE SeachTrip
    @Driver nvarchar(25),
    @bus int
AS
BEGIN
    SELECT ID
    FROM TripBusDriver
    WHERE Destination_City_ID = (SELECT FK_City_ID
        FROM Driver
        WHERE First_Name = @Driver)
        AND MIN_QuantityPlace <= (SELECT Count_place
        FROM Bus
        WHERE Num = @bus)
END
GO

exec SeachTrip 'Федоров', 2;
GO

--После ремонта в автобусе добавили 3 места, добавить эти места и в БД
UPDATE Bus
    SET Count_place = Count_place + 3
    WHERE Num = 2
GO