1/generated.sql

IF DB_ID('ToyWorldDb') IS NULL CREATE DATABASE ToyWorldDb;
GO
USE ToyWorldDb;
GO
IF OBJECT_ID('dbo.OrderItem','U') IS NOT NULL DROP TABLE dbo.OrderItem;
IF OBJECT_ID('dbo.[Order]','U') IS NOT NULL DROP TABLE dbo.[Order];
IF OBJECT_ID('dbo.Product','U') IS NOT NULL DROP TABLE dbo.Product;
IF OBJECT_ID('dbo.[User]','U') IS NOT NULL DROP TABLE dbo.[User];
IF OBJECT_ID('dbo.PickupPoint','U') IS NOT NULL DROP TABLE dbo.PickupPoint;
IF OBJECT_ID('dbo.OrderStatus','U') IS NOT NULL DROP TABLE dbo.OrderStatus;
IF OBJECT_ID('dbo.Category','U') IS NOT NULL DROP TABLE dbo.Category;
IF OBJECT_ID('dbo.Manufacturer','U') IS NOT NULL DROP TABLE dbo.Manufacturer;
IF OBJECT_ID('dbo.Supplier','U') IS NOT NULL DROP TABLE dbo.Supplier;
IF OBJECT_ID('dbo.Unit','U') IS NOT NULL DROP TABLE dbo.Unit;
IF OBJECT_ID('dbo.Role','U') IS NOT NULL DROP TABLE dbo.Role;
GO
CREATE TABLE dbo.Role (RoleId INT IDENTITY(1,1) CONSTRAINT PK_Role PRIMARY KEY, RoleName NVARCHAR(50) NOT NULL CONSTRAINT UQ_Role UNIQUE);
CREATE TABLE dbo.Unit (UnitId INT IDENTITY(1,1) CONSTRAINT PK_Unit PRIMARY KEY, UnitName NVARCHAR(20) NOT NULL CONSTRAINT UQ_Unit UNIQUE);
CREATE TABLE dbo.Supplier (SupplierId INT IDENTITY(1,1) CONSTRAINT PK_Supplier PRIMARY KEY, SupplierName NVARCHAR(100) NOT NULL CONSTRAINT UQ_Supplier UNIQUE);
CREATE TABLE dbo.Manufacturer (ManufacturerId INT IDENTITY(1,1) CONSTRAINT PK_Manufacturer PRIMARY KEY, ManufacturerName NVARCHAR(100) NOT NULL CONSTRAINT UQ_Manufacturer UNIQUE);
CREATE TABLE dbo.Category (CategoryId INT IDENTITY(1,1) CONSTRAINT PK_Category PRIMARY KEY, CategoryName NVARCHAR(100) NOT NULL CONSTRAINT UQ_Category UNIQUE);
CREATE TABLE dbo.OrderStatus (StatusId INT IDENTITY(1,1) CONSTRAINT PK_OrderStatus PRIMARY KEY, StatusName NVARCHAR(50) NOT NULL CONSTRAINT UQ_OrderStatus UNIQUE);
GO
CREATE TABLE dbo.[User] (UserId INT IDENTITY(1,1) CONSTRAINT PK_User PRIMARY KEY, RoleId INT NOT NULL CONSTRAINT FK_User_Role REFERENCES dbo.Role(RoleId), FullName NVARCHAR(150) NOT NULL, Login NVARCHAR(100) NOT NULL CONSTRAINT UQ_User_Login UNIQUE, Password NVARCHAR(100) NOT NULL);
CREATE TABLE dbo.PickupPoint (PickupPointId INT NOT NULL CONSTRAINT PK_PickupPoint PRIMARY KEY, PostalCode NVARCHAR(10) NOT NULL, City NVARCHAR(80) NOT NULL, Street NVARCHAR(120) NOT NULL, House NVARCHAR(20) NOT NULL);
CREATE TABLE dbo.Product (ProductId INT IDENTITY(1,1) CONSTRAINT PK_Product PRIMARY KEY, Article NVARCHAR(10) NOT NULL CONSTRAINT UQ_Product_Article UNIQUE, Name NVARCHAR(255) NOT NULL, UnitId INT NOT NULL CONSTRAINT FK_Product_Unit REFERENCES dbo.Unit(UnitId), Price DECIMAL(10,2) NOT NULL CONSTRAINT CK_Price CHECK (Price>=0), SupplierId INT NOT NULL CONSTRAINT FK_Product_Supplier REFERENCES dbo.Supplier(SupplierId), ManufacturerId INT NOT NULL CONSTRAINT FK_Product_Manufacturer REFERENCES dbo.Manufacturer(ManufacturerId), CategoryId INT NOT NULL CONSTRAINT FK_Product_Category REFERENCES dbo.Category(CategoryId), Discount INT NOT NULL CONSTRAINT CK_Disc CHECK (Discount BETWEEN 0 AND 100), Stock INT NOT NULL CONSTRAINT CK_Stock CHECK (Stock>=0), Description NVARCHAR(MAX) NULL, Photo NVARCHAR(100) NULL);
CREATE TABLE dbo.[Order] (OrderId INT NOT NULL CONSTRAINT PK_Order PRIMARY KEY, OrderDate DATE NULL, DeliveryDate DATE NULL, PickupPointId INT NOT NULL CONSTRAINT FK_Order_Pickup REFERENCES dbo.PickupPoint(PickupPointId), ClientUserId INT NULL CONSTRAINT FK_Order_User REFERENCES dbo.[User](UserId), ReceiveCode INT NULL, StatusId INT NOT NULL CONSTRAINT FK_Order_Status REFERENCES dbo.OrderStatus(StatusId));
CREATE TABLE dbo.OrderItem (OrderItemId INT IDENTITY(1,1) CONSTRAINT PK_OrderItem PRIMARY KEY, OrderId INT NOT NULL CONSTRAINT FK_Item_Order REFERENCES dbo.[Order](OrderId) ON DELETE CASCADE, Article NVARCHAR(10) NOT NULL CONSTRAINT FK_Item_Product REFERENCES dbo.Product(Article), Quantity INT NOT NULL CONSTRAINT CK_Qty CHECK (Quantity>0), CONSTRAINT UQ_Item UNIQUE (OrderId, Article));
GO

INSERT INTO dbo.Role(RoleName) VALUES (N'Администратор');
INSERT INTO dbo.Role(RoleName) VALUES (N'Менеджер');
INSERT INTO dbo.Role(RoleName) VALUES (N'Авторизированный клиент');
INSERT INTO dbo.Unit(UnitName) VALUES (N'шт.');
INSERT INTO dbo.Supplier(SupplierName) VALUES (N'Pikeshop');
INSERT INTO dbo.Supplier(SupplierName) VALUES (N'Playbig');
INSERT INTO dbo.Supplier(SupplierName) VALUES (N'Knauf');
INSERT INTO dbo.Supplier(SupplierName) VALUES (N'CHILITOY');
INSERT INTO dbo.Supplier(SupplierName) VALUES (N'Vinylon');
INSERT INTO dbo.Manufacturer(ManufacturerName) VALUES (N'ABSпластик');
INSERT INTO dbo.Manufacturer(ManufacturerName) VALUES (N'BambiniFelici');
INSERT INTO dbo.Manufacturer(ManufacturerName) VALUES (N'Junion');
INSERT INTO dbo.Category(CategoryName) VALUES (N'Игровой набор');
INSERT INTO dbo.Category(CategoryName) VALUES (N'Конструктор');
INSERT INTO dbo.Category(CategoryName) VALUES (N'Детский музыкальный инструмент');
INSERT INTO dbo.Category(CategoryName) VALUES (N'Машинка');
INSERT INTO dbo.OrderStatus(StatusName) VALUES (N'Завершен');
INSERT INTO dbo.OrderStatus(StatusName) VALUES (N'Новый');
GO
INSERT INTO dbo.[User](RoleId,FullName,Login,Password) VALUES ((SELECT RoleId FROM dbo.Role WHERE RoleName=N'Администратор'),N'Ворсин Петр Евгеньевич',N'94d5ous@gmail.com',N'uzWC67');
INSERT INTO dbo.[User](RoleId,FullName,Login,Password) VALUES ((SELECT RoleId FROM dbo.Role WHERE RoleName=N'Администратор'),N'Старикова Елена Павловна',N'uth4iz@mail.com',N'2L6KZG');
INSERT INTO dbo.[User](RoleId,FullName,Login,Password) VALUES ((SELECT RoleId FROM dbo.Role WHERE RoleName=N'Администратор'),N'Одинцов Серафим Артёмович',N'yzls62@outlook.com',N'JlFRCZ');
INSERT INTO dbo.[User](RoleId,FullName,Login,Password) VALUES ((SELECT RoleId FROM dbo.Role WHERE RoleName=N'Менеджер'),N'Михайлюк Анна Вячеславовна',N'1diph5e@tutanota.com',N'8ntwUp');
INSERT INTO dbo.[User](RoleId,FullName,Login,Password) VALUES ((SELECT RoleId FROM dbo.Role WHERE RoleName=N'Менеджер'),N'Ситдикова Елена Анатольевна',N'tjde7c@yahoo.com',N'YOyhfR');
INSERT INTO dbo.[User](RoleId,FullName,Login,Password) VALUES ((SELECT RoleId FROM dbo.Role WHERE RoleName=N'Менеджер'),N'Никифорова Весения Николаевна',N'wpmrc3do@tutanota.com',N'RSbvHv');
INSERT INTO dbo.[User](RoleId,FullName,Login,Password) VALUES ((SELECT RoleId FROM dbo.Role WHERE RoleName=N'Авторизированный клиент'),N'Степанов Михаил Артёмович',N'5d4zbu@tutanota.com',N'rwVDh9');
INSERT INTO dbo.[User](RoleId,FullName,Login,Password) VALUES ((SELECT RoleId FROM dbo.Role WHERE RoleName=N'Авторизированный клиент'),N'Ворсин Петр Евгеньевич',N'ptec8ym@yahoo.com',N'LdNyos');
INSERT INTO dbo.[User](RoleId,FullName,Login,Password) VALUES ((SELECT RoleId FROM dbo.Role WHERE RoleName=N'Авторизированный клиент'),N'Старикова Елена Павловна',N'1qz4kw@mail.com',N'gynQMT');
INSERT INTO dbo.[User](RoleId,FullName,Login,Password) VALUES ((SELECT RoleId FROM dbo.Role WHERE RoleName=N'Авторизированный клиент'),N'Сазонов Руслан Германович',N'4np6se@mail.com',N'AtnDjr');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (1,N'420151',N'Лесной',N'Вишневая',N'32');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (2,N'125061',N'Лесной',N'Подгорная',N'8');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (3,N'630370',N'Лесной',N'Шоссейная',N'24');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (4,N'400562',N'Лесной',N'Зеленая',N'32');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (5,N'614510',N'Лесной',N'Маяковского',N'47');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (6,N'410542',N'Лесной',N'Светлая',N'46');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (7,N'620839',N'Лесной',N'Цветочная',N'8');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (8,N'443890',N'Лесной',N'Коммунистическая',N'1');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (9,N'603379',N'Лесной',N'Спортивная',N'46');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (10,N'603721',N'Лесной',N'Гоголя',N'41');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (11,N'410172',N'Лесной',N'Северная',N'13');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (12,N'614611',N'Лесной',N'Молодежная',N'50');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (13,N'454311',N'Лесной',N'Новая',N'19');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (14,N'660007',N'Лесной',N'Октябрьская',N'19');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (15,N'603036',N'Лесной',N'Садовая',N'4');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (16,N'394060',N'Лесной',N'Фрунзе',N'43');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (17,N'410661',N'Лесной',N'Школьная',N'50');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (18,N'625590',N'Лесной',N'Коммунистическая',N'20');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (19,N'625683',N'Лесной',N'8 Марта',N'');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (20,N'450983',N'Лесной',N'Комсомольская',N'26');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (21,N'394782',N'Лесной',N'Чехова',N'3');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (22,N'603002',N'Лесной',N'Дзержинского',N'28');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (23,N'450558',N'Лесной',N'Набережная',N'30');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (24,N'344288',N'Лесной',N'Чехова',N'1');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (25,N'614164',N'Лесной',N'   Степная',N'30');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (26,N'394242',N'Лесной',N'Коммунистическая',N'43');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (27,N'660540',N'Лесной',N'Солнечная',N'25');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (28,N'125837',N'Лесной',N'Шоссейная',N'40');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (29,N'125703',N'Лесной',N'Партизанская',N'49');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (30,N'625283',N'Лесной',N'Победы',N'46');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (31,N'614753',N'Лесной',N'Полевая',N'35');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (32,N'426030',N'Лесной',N'Маяковского',N'44');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (33,N'450375',N'Лесной ул. Клубная',N'44',N'');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (34,N'625560',N'Лесной',N'Некрасова',N'12');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (35,N'630201',N'Лесной',N'Комсомольская',N'17');
INSERT INTO dbo.PickupPoint(PickupPointId,PostalCode,City,Street,House) VALUES (36,N'190949',N'Лесной',N'Мичурина',N'26');
INSERT INTO dbo.Product(Article,Name,UnitId,Price,SupplierId,ManufacturerId,CategoryId,Discount,Stock,Description,Photo) VALUES (N'PMEZMH',N'Детский игровой набор машинок Щенячий патруль / Dogs mini . 9 героев + 9 инерфионных машинок',(SELECT UnitId FROM dbo.Unit WHERE UnitName=N'шт.'),1414,(SELECT SupplierId FROM dbo.Supplier WHERE SupplierName=N'Pikeshop'),(SELECT ManufacturerId FROM dbo.Manufacturer WHERE ManufacturerName=N'ABSпластик'),(SELECT CategoryId FROM dbo.Category WHERE CategoryName=N'Игровой набор'),22,50,N'Детский набор машинок с героями мультсериала «Щенячий патруль» подойдет как для мальчиков, так и для девочек. В детский набор входит 9 фигурок щенков спасателей. ',N'1.jpg');
INSERT INTO dbo.Product(Article,Name,UnitId,Price,SupplierId,ManufacturerId,CategoryId,Discount,Stock,Description,Photo) VALUES (N'BPV4MM',N'Конструктор Гарри Поттер Сова Букля 630 деталей совместим с lego harry potter, лего совместимый)',(SELECT UnitId FROM dbo.Unit WHERE UnitName=N'шт.'),771,(SELECT SupplierId FROM dbo.Supplier WHERE SupplierName=N'Playbig'),(SELECT ManufacturerId FROM dbo.Manufacturer WHERE ManufacturerName=N'ABSпластик'),(SELECT CategoryId FROM dbo.Category WHERE CategoryName=N'Конструктор'),15,26,N'Коллекционная модель Букля состоит из множества потрясающих элементов, а также специального механизма внутри. С его помощью можно плавно поднимать-опускать крылья птицы.',N'2.jpg');
INSERT INTO dbo.Product(Article,Name,UnitId,Price,SupplierId,ManufacturerId,CategoryId,Discount,Stock,Description,Photo) VALUES (N'JVL42J',N'Музыкальные инструменты для детей, ксилофон, барабаны, развивающие игрушки, игрушки для детей',(SELECT UnitId FROM dbo.Unit WHERE UnitName=N'шт.'),2750,(SELECT SupplierId FROM dbo.Supplier WHERE SupplierName=N'Playbig'),(SELECT ManufacturerId FROM dbo.Manufacturer WHERE ManufacturerName=N'BambiniFelici'),(SELECT CategoryId FROM dbo.Category WHERE CategoryName=N'Детский музыкальный инструмент'),15,0,N'Откройте мир музыки для вашего ребенка с этой уникальной игрушкой! Это многофункциональное музыкальное чудо объединяет в себе всё, что нужно для творческого развития.',N'3.jpg');
INSERT INTO dbo.Product(Article,Name,UnitId,Price,SupplierId,ManufacturerId,CategoryId,Discount,Stock,Description,Photo) VALUES (N'F895RB',N'Машинка игрушка диско шар светящаяся музыкальная',(SELECT UnitId FROM dbo.Unit WHERE UnitName=N'шт.'),368,(SELECT SupplierId FROM dbo.Supplier WHERE SupplierName=N'Knauf'),(SELECT ManufacturerId FROM dbo.Manufacturer WHERE ManufacturerName=N'ABSпластик'),(SELECT CategoryId FROM dbo.Category WHERE CategoryName=N'Машинка'),6,7,N'Светящаяся музыкальная машина с диско шаром переливается разными цветами, играет ритмичные мелодии, объезжает препятствия и крутится, поэтому с ней точно не будет скучно.',N'4.jpg');
INSERT INTO dbo.Product(Article,Name,UnitId,Price,SupplierId,ManufacturerId,CategoryId,Discount,Stock,Description,Photo) VALUES (N'3XBOTN',N'Игровой набор Hot Wheels Action Loop Cyclone Challenge Track, с машинкой и удобным хранением, HTK16',(SELECT UnitId FROM dbo.Unit WHERE UnitName=N'шт.'),3426,(SELECT SupplierId FROM dbo.Supplier WHERE SupplierName=N'Knauf'),(SELECT ManufacturerId FROM dbo.Manufacturer WHERE ManufacturerName=N'BambiniFelici'),(SELECT CategoryId FROM dbo.Category WHERE CategoryName=N'Игровой набор'),10,21,N'Игровой набор Hot Wheels Action Loop Cyclone Challenge Track - это уникальная игра, которая позволит вам испытать себя и своих друзей в скорости и ловкости. Этот набор состоит из металлической дорожки с циклоном, которая создает потрясающий эффект и добавляет дополнительную сложность в игру.',N'5.jpg');
INSERT INTO dbo.Product(Article,Name,UnitId,Price,SupplierId,ManufacturerId,CategoryId,Discount,Stock,Description,Photo) VALUES (N'3L7RCZ',N'Игровой набор с деревянными машинками Стройплощадка Кран-Паркс, Junion',(SELECT UnitId FROM dbo.Unit WHERE UnitName=N'шт.'),7400,(SELECT SupplierId FROM dbo.Supplier WHERE SupplierName=N'Knauf'),(SELECT ManufacturerId FROM dbo.Manufacturer WHERE ManufacturerName=N'Junion'),(SELECT CategoryId FROM dbo.Category WHERE CategoryName=N'Игровой набор'),15,0,N'Игровой набор «Стройплощадка Кран-Паркс Junion» — это большая игрушечная парковка с деревянными машинками и настоящим подъёмным краном, придуманная в Яндексе настоящими родителями.',N'6.jpg');
INSERT INTO dbo.Product(Article,Name,UnitId,Price,SupplierId,ManufacturerId,CategoryId,Discount,Stock,Description,Photo) VALUES (N'S72AM3',N'Синтезатор детский с микрофоном 61 клавиша',(SELECT UnitId FROM dbo.Unit WHERE UnitName=N'шт.'),1749,(SELECT SupplierId FROM dbo.Supplier WHERE SupplierName=N'CHILITOY'),(SELECT ManufacturerId FROM dbo.Manufacturer WHERE ManufacturerName=N'Junion'),(SELECT CategoryId FROM dbo.Category WHERE CategoryName=N'Детский музыкальный инструмент'),10,35,N'Откройте для ребенка дверь в мир музыки с детским синтезатором! Этот компактный инструмент с микрофоном станет верным другом для юных музыкантов, помогая им развивать творческий потенциал и получать удовольствие от игры.',N'7.jpg');
INSERT INTO dbo.Product(Article,Name,UnitId,Price,SupplierId,ManufacturerId,CategoryId,Discount,Stock,Description,Photo) VALUES (N'2G3280',N'Деревянный игровой набор JUNION Стройплощадка "Кран-Паркс" с подъёмным, строительным краном и машинками, 18 предметов, подвижные элементы',(SELECT UnitId FROM dbo.Unit WHERE UnitName=N'шт.'),1624,(SELECT SupplierId FROM dbo.Supplier WHERE SupplierName=N'Vinylon'),(SELECT ManufacturerId FROM dbo.Manufacturer WHERE ManufacturerName=N'Junion'),(SELECT CategoryId FROM dbo.Category WHERE CategoryName=N'Игровой набор'),9,20,N'Игровой набор «Стройплощадка Кран-Паркс Junion» — это большая игрушечная парковка с деревянными машинками и настоящим подъёмным краном, придуманная в Яндексе настоящими родителями.',N'8.jpg');
INSERT INTO dbo.Product(Article,Name,UnitId,Price,SupplierId,ManufacturerId,CategoryId,Discount,Stock,Description,Photo) VALUES (N'MIO8YV',N'Музыкальная игрушка интерактивная Пульт, детский прорезыватель для малышей',(SELECT UnitId FROM dbo.Unit WHERE UnitName=N'шт.'),305,(SELECT SupplierId FROM dbo.Supplier WHERE SupplierName=N'Vinylon'),(SELECT ManufacturerId FROM dbo.Manufacturer WHERE ManufacturerName=N'BambiniFelici'),(SELECT CategoryId FROM dbo.Category WHERE CategoryName=N'Детский музыкальный инструмент'),9,31,N'Музыкальная игрушка интерактивная Пульт, детский прорезыватель для малышей',N'9.jpg');
INSERT INTO dbo.Product(Article,Name,UnitId,Price,SupplierId,ManufacturerId,CategoryId,Discount,Stock,Description,Photo) VALUES (N'UER2QD',N'Большой набор опытов и экспериментов для детей 14 в 1',(SELECT UnitId FROM dbo.Unit WHERE UnitName=N'шт.'),2506,(SELECT SupplierId FROM dbo.Supplier WHERE SupplierName=N'Vinylon'),(SELECT ManufacturerId FROM dbo.Manufacturer WHERE ManufacturerName=N'BambiniFelici'),(SELECT CategoryId FROM dbo.Category WHERE CategoryName=N'Игровой набор'),8,27,N'Большой набор опытов и экспериментов для детей 14 в 1',N'10.jpg');
INSERT INTO dbo.[Order](OrderId,OrderDate,DeliveryDate,PickupPointId,ClientUserId,ReceiveCode,StatusId) VALUES (1,'2025-02-27','2025-04-20',1,(SELECT TOP 1 u.UserId FROM dbo.[User] u JOIN dbo.Role rl ON u.RoleId=rl.RoleId WHERE u.FullName=N'Степанов Михаил Артёмович' ORDER BY CASE WHEN rl.RoleName=N'Авторизированный клиент' THEN 0 ELSE 1 END, u.UserId),901,(SELECT StatusId FROM dbo.OrderStatus WHERE StatusName=N'Завершен'));
INSERT INTO dbo.OrderItem(OrderId,Article,Quantity) VALUES (1,N'PMEZMH',2);
INSERT INTO dbo.OrderItem(OrderId,Article,Quantity) VALUES (1,N'BPV4MM',2);
INSERT INTO dbo.[Order](OrderId,OrderDate,DeliveryDate,PickupPointId,ClientUserId,ReceiveCode,StatusId) VALUES (2,'2024-09-28','2025-04-21',11,(SELECT TOP 1 u.UserId FROM dbo.[User] u JOIN dbo.Role rl ON u.RoleId=rl.RoleId WHERE u.FullName=N'Ворсин Петр Евгеньевич' ORDER BY CASE WHEN rl.RoleName=N'Авторизированный клиент' THEN 0 ELSE 1 END, u.UserId),902,(SELECT StatusId FROM dbo.OrderStatus WHERE StatusName=N'Завершен'));
INSERT INTO dbo.OrderItem(OrderId,Article,Quantity) VALUES (2,N'JVL42J',1);
INSERT INTO dbo.OrderItem(OrderId,Article,Quantity) VALUES (2,N'F895RB',1);
INSERT INTO dbo.[Order](OrderId,OrderDate,DeliveryDate,PickupPointId,ClientUserId,ReceiveCode,StatusId) VALUES (3,'2025-03-21','2025-04-22',2,(SELECT TOP 1 u.UserId FROM dbo.[User] u JOIN dbo.Role rl ON u.RoleId=rl.RoleId WHERE u.FullName=N'Старикова Елена Павловна' ORDER BY CASE WHEN rl.RoleName=N'Авторизированный клиент' THEN 0 ELSE 1 END, u.UserId),903,(SELECT StatusId FROM dbo.OrderStatus WHERE StatusName=N'Завершен'));
INSERT INTO dbo.OrderItem(OrderId,Article,Quantity) VALUES (3,N'3XBOTN',10);
INSERT INTO dbo.OrderItem(OrderId,Article,Quantity) VALUES (3,N'3L7RCZ',10);
INSERT INTO dbo.[Order](OrderId,OrderDate,DeliveryDate,PickupPointId,ClientUserId,ReceiveCode,StatusId) VALUES (4,'2025-02-20','2025-04-23',11,(SELECT TOP 1 u.UserId FROM dbo.[User] u JOIN dbo.Role rl ON u.RoleId=rl.RoleId WHERE u.FullName=N'Сазонов Руслан Германович' ORDER BY CASE WHEN rl.RoleName=N'Авторизированный клиент' THEN 0 ELSE 1 END, u.UserId),904,(SELECT StatusId FROM dbo.OrderStatus WHERE StatusName=N'Завершен'));
INSERT INTO dbo.OrderItem(OrderId,Article,Quantity) VALUES (4,N'S72AM3',5);
INSERT INTO dbo.OrderItem(OrderId,Article,Quantity) VALUES (4,N'2G3280',4);
INSERT INTO dbo.[Order](OrderId,OrderDate,DeliveryDate,PickupPointId,ClientUserId,ReceiveCode,StatusId) VALUES (5,'2025-03-17','2025-04-24',2,(SELECT TOP 1 u.UserId FROM dbo.[User] u JOIN dbo.Role rl ON u.RoleId=rl.RoleId WHERE u.FullName=N'Степанов Михаил Артёмович' ORDER BY CASE WHEN rl.RoleName=N'Авторизированный клиент' THEN 0 ELSE 1 END, u.UserId),905,(SELECT StatusId FROM dbo.OrderStatus WHERE StatusName=N'Завершен'));
INSERT INTO dbo.OrderItem(OrderId,Article,Quantity) VALUES (5,N'MIO8YV',2);
INSERT INTO dbo.OrderItem(OrderId,Article,Quantity) VALUES (5,N'UER2QD',2);
INSERT INTO dbo.[Order](OrderId,OrderDate,DeliveryDate,PickupPointId,ClientUserId,ReceiveCode,StatusId) VALUES (6,'2025-03-01','2025-04-25',15,(SELECT TOP 1 u.UserId FROM dbo.[User] u JOIN dbo.Role rl ON u.RoleId=rl.RoleId WHERE u.FullName=N'Ворсин Петр Евгеньевич' ORDER BY CASE WHEN rl.RoleName=N'Авторизированный клиент' THEN 0 ELSE 1 END, u.UserId),906,(SELECT StatusId FROM dbo.OrderStatus WHERE StatusName=N'Завершен'));
INSERT INTO dbo.OrderItem(OrderId,Article,Quantity) VALUES (6,N'PMEZMH',2);
INSERT INTO dbo.OrderItem(OrderId,Article,Quantity) VALUES (6,N'BPV4MM',2);
INSERT INTO dbo.[Order](OrderId,OrderDate,DeliveryDate,PickupPointId,ClientUserId,ReceiveCode,StatusId) VALUES (7,NULL,'2025-04-26',3,(SELECT TOP 1 u.UserId FROM dbo.[User] u JOIN dbo.Role rl ON u.RoleId=rl.RoleId WHERE u.FullName=N'Старикова Елена Павловна' ORDER BY CASE WHEN rl.RoleName=N'Авторизированный клиент' THEN 0 ELSE 1 END, u.UserId),907,(SELECT StatusId FROM dbo.OrderStatus WHERE StatusName=N'Завершен'));
INSERT INTO dbo.OrderItem(OrderId,Article,Quantity) VALUES (7,N'JVL42J',1);
INSERT INTO dbo.OrderItem(OrderId,Article,Quantity) VALUES (7,N'F895RB',1);
INSERT INTO dbo.[Order](OrderId,OrderDate,DeliveryDate,PickupPointId,ClientUserId,ReceiveCode,StatusId) VALUES (8,'2025-03-31','2025-04-27',19,(SELECT TOP 1 u.UserId FROM dbo.[User] u JOIN dbo.Role rl ON u.RoleId=rl.RoleId WHERE u.FullName=N'Сазонов Руслан Германович' ORDER BY CASE WHEN rl.RoleName=N'Авторизированный клиент' THEN 0 ELSE 1 END, u.UserId),908,(SELECT StatusId FROM dbo.OrderStatus WHERE StatusName=N'Новый '));
INSERT INTO dbo.OrderItem(OrderId,Article,Quantity) VALUES (8,N'3XBOTN',10);
INSERT INTO dbo.OrderItem(OrderId,Article,Quantity) VALUES (8,N'3L7RCZ',10);
INSERT INTO dbo.[Order](OrderId,OrderDate,DeliveryDate,PickupPointId,ClientUserId,ReceiveCode,StatusId) VALUES (9,'2025-04-02','2025-04-28',5,(SELECT TOP 1 u.UserId FROM dbo.[User] u JOIN dbo.Role rl ON u.RoleId=rl.RoleId WHERE u.FullName=N'Старикова Елена Павловна' ORDER BY CASE WHEN rl.RoleName=N'Авторизированный клиент' THEN 0 ELSE 1 END, u.UserId),909,(SELECT StatusId FROM dbo.OrderStatus WHERE StatusName=N'Новый '));
INSERT INTO dbo.OrderItem(OrderId,Article,Quantity) VALUES (9,N'S72AM3',5);
INSERT INTO dbo.OrderItem(OrderId,Article,Quantity) VALUES (9,N'2G3280',4);
INSERT INTO dbo.[Order](OrderId,OrderDate,DeliveryDate,PickupPointId,ClientUserId,ReceiveCode,StatusId) VALUES (10,'2025-04-03','2025-04-29',19,(SELECT TOP 1 u.UserId FROM dbo.[User] u JOIN dbo.Role rl ON u.RoleId=rl.RoleId WHERE u.FullName=N'Сазонов Руслан Германович' ORDER BY CASE WHEN rl.RoleName=N'Авторизированный клиент' THEN 0 ELSE 1 END, u.UserId),910,(SELECT StatusId FROM dbo.OrderStatus WHERE StatusName=N'Новый '));
INSERT INTO dbo.OrderItem(OrderId,Article,Quantity) VALUES (10,N'MIO8YV',2);
INSERT INTO dbo.OrderItem(OrderId,Article,Quantity) VALUES (10,N'UER2QD',2);
GO
IF OBJECT_ID('dbo.vCatalog','V') IS NOT NULL DROP VIEW dbo.vCatalog;
GO
CREATE VIEW dbo.vCatalog AS SELECT p.Article, p.Photo AS [Фото], c.CategoryName AS [Категория товара], p.Name AS [Наименование товара], p.Description AS [Описание товара], m.ManufacturerName AS [Производитель], s.SupplierName AS [Поставщик], p.Price AS [Цена], u.UnitName AS [Единица измерения], p.Stock AS [Кол-во на складе], p.Discount AS [Действующая скидка] FROM dbo.Product p JOIN dbo.Category c ON p.CategoryId=c.CategoryId JOIN dbo.Manufacturer m ON p.ManufacturerId=m.ManufacturerId JOIN dbo.Supplier s ON p.SupplierId=s.SupplierId JOIN dbo.Unit u ON p.UnitId=u.UnitId;
GO
IF OBJECT_ID('dbo.vOrders','V') IS NOT NULL DROP VIEW dbo.vOrders;
GO
CREATE VIEW dbo.vOrders AS SELECT o.OrderId, STRING_AGG(oi.Article + N' x' + CAST(oi.Quantity AS nvarchar(10)), N', ') AS [Артикул заказа], st.StatusName AS [Статус заказа], (pp.PostalCode + ', г. ' + pp.City + ', ул. ' + pp.Street + ', д. ' + pp.House) AS [Адрес пункта выдачи], o.OrderDate AS [Дата заказа], o.DeliveryDate AS [Дата доставки] FROM dbo.[Order] o JOIN dbo.OrderStatus st ON o.StatusId=st.StatusId JOIN dbo.PickupPoint pp ON o.PickupPointId=pp.PickupPointId LEFT JOIN dbo.OrderItem oi ON oi.OrderId=o.OrderId GROUP BY o.OrderId, st.StatusName, pp.PostalCode, pp.City, pp.Street, pp.House, o.OrderDate, o.DeliveryDate;
GO
IF OBJECT_ID('dbo.vw_UsersLogin','V') IS NOT NULL DROP VIEW dbo.vw_UsersLogin;
GO
CREATE VIEW dbo.vw_UsersLogin AS SELECT u.UserId, u.FullName AS [ФИО], u.Login AS [Логин], u.Password AS [Пароль], r.RoleName AS [Роль] FROM dbo.[User] u JOIN dbo.Role r ON u.RoleId=r.RoleId;
GO