----------------------
-- Первая процедура --
----------------------

CREATE PROCEDURE calculate_order_price
    @order_id INT,
    @price FLOAT OUTPUT
AS BEGIN
    SET @price = 0.0;

    DECLARE @delivery_id INT;
    SELECT @delivery_id=delivery_id FROM Shipments WHERE order_id=@order_id;
    IF @delivery_id IS NULL BEGIN
        RAISERROR (N'Доставка для данного заказа ещё не оформлена', 10, 1);
        RETURN;
    END

    DECLARE @gas_price FLOAT, @tonnage INT;
    SELECT @gas_price=gas_consumption / 100.0 * distance, @tonnage=tonnage
    FROM Deliveries
    JOIN Vehicles ON Deliveries.vehicle_id = Vehicles.id
    JOIN Routes ON Deliveries.route_id = Routes.id;

    DECLARE boxes_cursor CURSOR LOCAL
    FOR SELECT volume, weight, price wrap_price
        FROM Boxes JOIN Wrappings ON Boxes.wrapping_id = Wrappings.id
        WHERE order_id=@order_id
    OPEN boxes_cursor

    DECLARE @volume FLOAT, @weight FLOAT, @wrap_price INT;
    FETCH NEXT FROM boxes_cursor INTO @volume, @weight, @wrap_price;
    WHILE @@FETCH_STATUS=0 BEGIN
        SET @price = @price + @weight / CAST(@tonnage AS FLOAT) * @gas_price + @wrap_price;
        FETCH NEXT FROM boxes_cursor INTO @volume, @weight, @wrap_price;
    END
    SET @price = @price + 1000;
END
GO

DROP PROCEDURE calculate_order_price
GO

INSERT INTO Boxes (order_id, warehouse_id, wrapping_id, volume, weight)
VALUES (100022, 1, 1, 0.1, 10);

DECLARE @price INT;
EXEC calculate_order_price 100022, @price OUTPUT;
PRINT @price
GO

----------------------
-- Вторая процедура --
----------------------

CREATE PROCEDURE calculate_delivery_date
    @route_id INT,
    @order_date DATETIME,
    @delivery_date DATETIME OUTPUT
AS BEGIN
    DECLARE @distance FLOAT;
    SELECT @distance=distance FROM Routes WHERE id=@route_id;

    DECLARE @hours_driving FLOAT, @days_driving INT;
    -- Скорость перевозки 70 километров в час
    SET @hours_driving = @distance / 70.0;
    -- 8 часов сна на 12 часов езды
    SET @hours_driving = @hours_driving / 12.0 * 8.0;
    SET @days_driving = CAST(@hours_driving / 24.0 + 1 AS INT);

    -- Добавляем к дате заказа количество дней на поездку, один день
    -- на обработку посылки в терминале отправки и один в терминале
    -- приёма.
    SET @delivery_date = DATEADD(day, @days_driving + 2, @order_date);
END
GO

DROP PROCEDURE calculate_delivery_date
GO

DECLARE @order_date DATETIME, @delivery_date DATETIME;
SET @order_date = '2019-09-07';
EXEC calculate_delivery_date 1, @order_date, @delivery_date OUTPUT;
PRINT @delivery_date
GO

----------------------
-- Третья процедура --
----------------------

CREATE PROCEDURE calculate_discount_for_customer
    @customer_id INT
AS BEGIN
    DECLARE @money_spent INT;
    SELECT @money_spent=SUM(amount)
    FROM Payments JOIN Orders ON Payments.order_id = Orders.id
    WHERE customer_id=@customer_id;
    IF @money_spent IS NULL BEGIN
        RAISERROR (N'Данный клиент ещё не оплатил ни одного заказа', 10, 1);
        RETURN 0;
    END

    SET @money_spent = @money_spent - 10000;
    SET @money_spent = IIF(@money_spent < 0, 0, @money_spent);
    SET @money_spent = IIF(@money_spent > 40000, 40000, @money_spent);
    RETURN CAST(@money_spent / 40000 * 15 + 5 AS INT);
END
GO

DROP PROCEDURE calculate_discount_for_customer
GO

DECLARE @discount INT;
EXEC @discount = calculate_discount_for_customer 10;
PRINT @discount
GO

DECLARE @discount INT;
EXEC @discount = calculate_discount_for_customer 1;
PRINT @discount
GO

-------------------------
-- Четвёртая процедура --
-------------------------

CREATE PROCEDURE calculate_full_expenses_month
    @date DATE,
    @expenses FLOAT OUTPUT
AS BEGIN
    SET @expenses = 0.0;

    DECLARE @salary INT;
    SELECT @salary=SUM(salary) FROM Drivers;

    DECLARE @gas_price FLOAT = 0.0;
    SELECT @gas_price=SUM(gas_consumption / 100.0 * distance)
    FROM Deliveries
    JOIN Routes ON Deliveries.route_id = Routes.id
    JOIN Vehicles ON Deliveries.vehicle_id = Vehicles.id
    WHERE DATEPART(year, date) = DATEPART(year, @date) AND
          DATEPART(month, date) = DATEPART(month, @date);
    SET @gas_price = IIF(@gas_price IS NULL, 0.0, @gas_price);

    SET @expenses = CAST(@salary AS FLOAT) + @gas_price;
END
GO

DROP PROCEDURE calculate_full_expenses_month
GO

DECLARE @expenses FLOAT;
EXEC calculate_full_expenses_month '2021-12-01', @expenses OUTPUT;
PRINT CAST(@expenses AS INT)
GO

---------------------
-- Пятая процедура --
---------------------

CREATE PROCEDURE calculate_full_returns_month
    @date DATE,
    @returns FLOAT OUTPUT
AS SELECT @returns=SUM(amount) FROM Payments
   WHERE DATEPART(year, date) = DATEPART(year, @date) AND
         DATEPART(month, date) = DATEPART(month, @date);
GO

DROP PROCEDURE calculate_full_returns_month
GO

DECLARE @returns FLOAT;
EXEC calculate_full_returns_month '2021-12-01', @returns OUTPUT;
PRINT CAST(@returns AS INT)
GO