1 files changed, 171 insertions, 0 deletions
diff --git a/task7_procedures.sql b/task7_procedures.sql
new file mode 100644
index 0000000..a93dd3f
--- /dev/null
+++ b/task7_procedures.sql
@@ -0,0 +1,171 @@
+----------------------
+-- Первая процедура --
+----------------------
+
+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
+
+DECLARE @price INT;
+EXEC calculate_order_price 12, @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