лаба 8 / Lab_8

USE Склад_13
GO


CREATE TRIGGER tr_Set_СрокПоставки
ON Заказ
FOR INSERT, UPDATE
AS
IF UPDATE(ДатаЗаказа)
DECLARE @OrderCode INT;
DECLARE @CurrencyCode VARCHAR(3);
SELECT @CurrencyCode = т.КодВалюты
FROM inserted и
JOIN Товар т
ON и.КодТовара = т.КодТовара;
SELECT @OrderCode = КодЗаказа
FROM inserted;
DECLARE @Days INT;
IF @CurrencyCode = 'BYR'
SET @Days = 3;
ELSE IF @CurrencyCode = 'RUR' OR @CurrencyCode = 'GRV'
SET @Days = 7;
ELSE IF @CurrencyCode = 'USD' OR @CurrencyCode = 'EUR'
SET @Days = 10;
ELSE
SET @Days = 14;
UPDATE Заказ
SET СрокПоставки = ДатаЗаказа + @Days
WHERE КодЗаказа = @OrderCode;
GO

SELECT* FROM Заказ
UPDATE Заказ
SET ДатаЗаказа = '2020.12.12'
WHERE КодЗаказа = 9;

CREATE TABLE Отпуск (
КодТовара INT PRIMARY KEY,
Наименование VARCHAR(50) NOT NULL,
ВсегоЗаказано NUMERIC(12, 3) NULL,
CONSTRAINT FK_Отпуск_Товар FOREIGN KEY (КодТовара)
REFERENCES Товар ON UPDATE CASCADE
);
GO

CREATE TRIGGER tr_Кол_ЗаказанногоТовара
ON Заказ
FOR INSERT, UPDATE, DELETE
AS
IF UPDATE(Количество)
DECLARE @Rows INT;
DECLARE @ProductCode INT;
DECLARE @ProductName VARCHAR(50);
DECLARE @Count INT;
SELECT @ProductCode = и.КодТовара, @ProductName = т.Наименование
FROM inserted и
JOIN Товар т
ON и.КодТовара = т.КодТовара;
SELECT @Count = SUM(Количество)
FROM Заказ
WHERE КодТовара = @ProductCode;
SELECT @Rows = COUNT(*)
FROM Отпуск о
JOIN Товар т
ON о.КодТовара = т.КодТовара
JOIN inserted и
ON и.КодТовара = т.КодТовара
WHERE и.КодТовара = о.КодТовара;
IF @Rows = 0
INSERT INTO Отпуск VALUES (@ProductCode, @ProductName, @Count);
ELSE
UPDATE Отпуск SET ВсегоЗаказано = @Count
WHERE КодТовара = @ProductCode;
GO

SELECT SUM(Количество) FROM Заказ
WHERE КодТовара = 111;
SELECT * FROM Отпуск
SELECT * FROM Товар
SELECT* FROM Поставщик
INSERT INTO Заказ (КодКлиента, КодТовара, Количество, КодПоставщика) VALUES (3, 111, 20, 123);
INSERT INTO Заказ (КодКлиента, КодТовара, Количество, КодПоставщика) VALUES (1, 222, 15, 234);

CREATE PROCEDURE pr_Стоимость_ВалютаИнтервал
(@КодВалюты VARCHAR(3),
@НачалоИнтервала DATE,
@КонецИнтервала DATE,
@Стоимость MONEY OUTPUT)
AS
DECLARE @Rate SMALLMONEY;
SELECT @Rate = КурсВалюты
FROM Валюта
WHERE @КодВалюты = КодВалюты;
SET @Стоимость = 0;
DECLARE @OrderPrice MONEY;
DECLARE myCursor CURSOR LOCAL STATIC
FOR
SELECT з.Количество * т.Цена / в.КурсВалюты * @Rate
FROM Заказ з
JOIN Товар т
ON з.КодТовара = т.КодТовара
JOIN Валюта в
ON т.КодВалюты = в.КодВалюты
WHERE з.ДатаЗаказа BETWEEN @НачалоИнтервала AND @КонецИнтервала;
OPEN myCursor;
FETCH FIRST FROM myCursor INTO @OrderPrice;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Стоимость = @Стоимость + @OrderPrice;
FETCH NEXT FROM myCursor INTO @OrderPrice;
END;
CLOSE myCursor;
DEALLOCATE myCursor;
GO

DECLARE @Price MONEY;
EXEC pr_Стоимость_ВалютаИнтервал 'BYR', '2018.04.10', '2019.04.29', @Price OUTPUT;
SELECT @Price;

INSERT INTO Регион
VALUES (102, 'Россия', '', 'Москва', 'пр.Калинина, 50',
'339-62- 10', '(095) 339-62-11');

INSERT INTO Регион
VALUES (401, 'Литва', '', 'Вильнюс', 'ул.Чурлёниса, 19', NULL,
'(055) 33-27-75');

CREATE PROCEDURE clientsSuppliersByCountry
(@НачалоИнтервала DATE,
@КонецИнтервала DATE)
AS
DECLARE @Country VARCHAR(20);
DECLARE @ClientsNumber INT;
DECLARE @SuppliersNumber INT;
DECLARE @table TABLE (
Страна VARCHAR(20),
ЧислоКлиентов INT,
ЧислоПоставщиков INT);
DECLARE myCursor CURSOR LOCAL STATIC
FOR
SELECT DISTINCT Страна
FROM Регион;
OPEN myCursor;
FETCH FIRST FROM myCursor INTO @Country;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC pr_КлиентПоставщик_СтранаИнтервал @Country, @НачалоИнтервала, @КонецИнтервала, @ClientsNumber OUTPUT, @SuppliersNumber OUTPUT;
INSERT INTO @table VALUES (@Country, @ClientsNumber, @SuppliersNumber);
FETCH NEXT FROM myCursor INTO @Country;
END;
SELECT * FROM @table;
CLOSE myCursor;
DEALLOCATE myCursor;
GO

EXEC clientsSuppliersByCountry '2018.04.10', '2019.04.29';

CREATE TABLE Протокол (
Номер INT IDENTITY(1,1) PRIMARY KEY,
ДатаВремя DATETIME NOT NULL,
Пользователь VARCHAR(80) NOT NULL,
Действие VARCHAR(10) NOT NULL,
ЧислоСтрок INT NOT NULL
);

CREATE TRIGGER tr_insertingProduct
ON Товар
FOR INSERT
AS
DECLARE @RowsNumber INT;
SELECT @RowsNumber = COUNT(*)
FROM inserted;
INSERT INTO Протокол VALUES (GETDATE(), SUSER_NAME(), 'Вставка', @RowsNumber);
GO

CREATE TRIGGER tr_updatingProduct
ON Товар
FOR UPDATE
AS
DECLARE @RowsNumber1 INT;
SELECT @RowsNumber1 = COUNT(*)
FROM inserted;
INSERT INTO Протокол VALUES (GETDATE(), SUSER_NAME(), 'Обновление', @RowsNumber1);
GO

CREATE TRIGGER tr_deletingProduct
ON Товар
FOR DELETE
AS
DECLARE @RowsNumber2 INT;
SELECT @RowsNumber2 = COUNT(*)
FROM deleted;
INSERT INTO Протокол VALUES (GETDATE(), SUSER_NAME(), 'Удаление', @RowsNumber2);
GO

SELECT* FROM Протокол;
SELECT* FROM Товар
INSERT INTO Товар (КодТовара, Наименование, Цена, КодВалюты, Расфасован) VALUES (555, 'Шуруповерт', 150, 'BYR', 'Да');
DELETE FROM Товар WHERE КодТовара = 222;

ALTER TABLE Заказ ADD Стоимость MONEY NULL;
ALTER TABLE Заказ ADD СтоимостьНВ MONEY NULL;

CREATE TRIGGER tr_Товар_Цена
ON Товар
FOR INSERT, UPDATE
AS
IF UPDATE(Цена)
BEGIN
DECLARE @КодТовара INT, @Цена MONEY, @ЦенаНВ MONEY
DECLARE myCursor CURSOR LOCAL STATIC
FOR
SELECT и.КодТовара, и.Цена, и.Цена * в.КурсВалюты
FROM inserted и
JOIN Валюта в
ON и.КодВалюты = в.КодВалюты;
OPEN myCursor;
FETCH FIRST FROM myCursor INTO @КодТовара, @Цена, @ЦенаНВ;
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE Заказ
SET Стоимость = Количество * @Цена, СтоимостьНВ = Количество * @ЦенаНВ
WHERE КодТовара = @КодТовара;
FETCH NEXT FROM myCursor INTO @КодТовара, @Цена, @ЦенаНВ;
END;
CLOSE myCursor;
DEALLOCATE myCursor;
END;
GO

UPDATE Товар
SET Цена = Цена + 10;

CREATE TRIGGER tr_Товар_Количество
ON Заказ
FOR INSERT, UPDATE
AS
IF UPDATE(Количество)
BEGIN
DECLARE @КодЗаказа INT, @Цена MONEY, @ЦенаНВ MONEY
DECLARE myCursor CURSOR LOCAL STATIC
FOR
SELECT и.КодЗаказа, т.Цена, т.Цена * в.КурсВалюты
FROM inserted и
JOIN Товар т
ON и.КодТовара = т.КодТовара
JOIN Валюта в
ON т.КодВалюты = в.КодВалюты;
OPEN myCursor;
FETCH FIRST FROM myCursor INTO @КодЗаказа, @Цена, @ЦенаНВ;
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE Заказ
SET Стоимость = Количество * @Цена, СтоимостьНВ = Количество * @ЦенаНВ
WHERE КодЗаказа = @КодЗаказа;
FETCH NEXT FROM myCursor INTO @КодЗаказа, @Цена, @ЦенаНВ;
END;
CLOSE myCursor;
DEALLOCATE myCursor;
END;
GO

UPDATE Заказ
SET Количество = 5
WHERE КодЗаказа = 9;

посчитать сколько дней нужно чтобы поставить все товары


SELECT* FROM Товар
SELECT* FROM Заказ
GO

CREATE PROCEDURE pr_Товар_день_количество(@Day INT OUTPUT)
AS
DECLARE @OrderTime INT;
SET @Day=0;
DECLARE myCursor CURSOR LOCAL STATIC
FOR
SELECT DAY(СрокПоставки-ДатаЗаказа)
FROM Заказ
OPEN myCursor;
FETCH FIRST FROM myCursor INTO @OrderTime;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Day = @Day + @OrderTime;
FETCH NEXT FROM myCursor INTO @OrderTime;
END;
CLOSE myCursor;
DEALLOCATE myCursor;
GO

DROP PROCEDURE pr_Товар_день_количество;

DECLARE @day INT;
EXECUTE pr_Товар_день_количество @day OUTPUT;
SELECT @day AS CounterDayOrder;

SELECT* FROM Заказ;