gistfile1.txt

--STORED PROCEDURES:
USE Warehouse
--1. Написать хранимую процедуру, которая показывает общее количество проданных товаров в каждой из категорий и от каждого производителя.
CREATE PROCEDURE ProducerCategorySaleCount AS
SELECT prod.name [Производитель], c.name [Категория], SUM(s.quantity) [продано товара]
FROM Product p
JOIN Sale s ON s.product_id = p.id
JOIN Category c ON c.id = p.category_id
JOIN Producer prod ON prod.id =p.producer_id
GROUP BY prod.name, c.name
GO

EXEC ProducerCategorySaleCount

--2. Написать хранимую процедуру, которая показывает список товаров, отвечающих набору критериев. Критерии: название продукта,
--категория, производитель. Кроме того, список должен быть отсортирован по номеру поля, указанному в 4-м параметре,
--в направлении, указанном в 5-м параметре (пригодится sp_executesql).

--3. Написать хранимую процедуру, которая показывает список производителей и общее количество товаров, произведённых каждым из них.
CREATE PROCEDURE ShowProduceCount AS
SELECT temp.name [Producer name], SUM(p.quantity) [Count of goods]
FROM Product p
JOIN (SELECT prod.id, prod.name
FROM Producer prod) AS Temp ON Temp.id = p.producer_id
GROUP BY Temp.name
GO

EXEC ShowProduceCount

--4. Создать хранимую процедуру, которая показывает название продукта, который был продан в максимальном количестве.
CREATE PROCEDURE ShowMaxSaleGood AS
SELECT p.name [Максимально проданный товар]
FROM Product p
WHERE p.id = (SELECT TOP 1 s.product_id
			  FROM Sale s
			  ORDER BY s.quantity DESC)
GO

EXEC ShowMaxSaleGood

--5. Создать хранимую процедуру, которая возвращает общее количество проданных товаров и общего дохода.
CREATE PROCEDURE SalesAll AS
SELECT SUM(s.quantity) [Кол-во проданных товаров], SUM(s.price_product*s.quantity) [Доход]
FROM Sale s
GO

EXEC SalesAll
--=============================================================================================================================

--TRIGGERS:

--1. При продаже товара (добавлении записи в таблицу Sale), его количество должно уменьшаться (в таблице Product).
ALTER TRIGGER OnSale ON Sale FOR INSERT --CREATE
AS
DECLARE @insertProduct int
DECLARE @insertCount int

SELECT @insertProduct = inserted.product_id,
	   @insertCount = inserted.quantity
FROM INSERTED

IF ((SELECT p.quantity FROM Product p WHERE p.id = @insertProduct)-@insertCount) > 0
BEGIN
	UPDATE Product
	SET quantity -=@insertCount
	WHERE Product.id = @insertProduct
END
ELSE
ROLLBACK TRANSACTION

--проверяю триггер
INSERT INTO Sale(product_id, quantity, delivery_id, price_product, date_of_sale)
VALUES (1, 1, 2, 2000, GETDATE())

--2. Нельзя продать товар, которого нет на складе магазина (по количеству).
ALTER TRIGGER BeforeSale ON Sale FOR INSERT 
AS
DECLARE @saleProduct int
DECLARE @saleCount int

SELECT @saleProduct = inserted.product_id,
	   @saleCount = inserted.quantity
FROM INSERTED

IF (SELECT p.quantity FROM Product p WHERE p.id = @saleProduct) = 0
BEGIN
	PRINT 'Нет на складе!!!'
END

INSERT INTO Sale(product_id, quantity, delivery_id, price_product, date_of_sale)
VALUES (1, 1, 2, 2000, GETDATE())

--3. Нельзя продать больше 100 единиц одного товара одному покупателю.
CREATE TRIGGER Sale100 ON Sale FOR INSERT 
AS
DECLARE @saleProduct int
DECLARE @saleCount int
IF @saleCount >= 100
ROLLBACK TRANSACTION
ELSE
PRINT 'OK'

INSERT INTO Sale(product_id, quantity, delivery_id, price_product, date_of_sale)
VALUES (8, 10, 2, 2000, GETDATE())

--4. При удалении товара из таблицы Product, данные о нём должны копироваться в резервную таблицу ProductArchive.

--5. Если новый товар добавляется в таблицу Product, он должен быть удалён из таблицы ProductArchive (если такой товар там есть).

--6. Если продаётся товар производителя "MSI", то в таблицу Sale пишем на 1 товар больше, но только если
--такое количество есть в наличии.
ALTER TRIGGER MSISale ON Sale FOR INSERT 
AS
DECLARE @insertProduct int
DECLARE @insertProducer int
DECLARE @insertCount int


SELECT @insertProduct = inserted.product_id,
	   @insertProducer = (SELECT p.producer_id FROM Product p WHERE p.id = @insertProduct),
	   @insertCount = inserted.quantity
FROM INSERTED

IF @insertProducer = (SELECT prod.id FROM Producer prod WHERE prod.name LIKE '%MSI%')
BEGIN
	SET @insertCount+=1

	IF ((SELECT p.quantity FROM Product p WHERE p.id = @insertProduct)-@insertCount) > 0
	BEGIN
		UPDATE Product
		SET quantity -=@insertCount
		WHERE Product.id = @insertProduct
	END
END


INSERT INTO Sale(product_id, quantity, delivery_id, price_product, date_of_sale)
VALUES (1, 1, 2, 2000, GETDATE())

ENABLE TRIGGER MSISale ON Sale
--7. Если нет нужного товара для продажи, продать случайный товар. В случае, если склад (таблица Product) опустел, вывести
--сообщение об этом.