inet_shop ddl

DDL.sql

create database Inet_shop
use Inet_shop

--Пользователи
create table Users
(
    id           INT IDENTITY,
    login        NVARCHAR(50)  not null,
    password     nvarchar(100) not null,
    name         nvarchar(100) not null,
    surname      nvarchar(100),
    patronym     nvarchar(100),
    email        nvarchar(255) not null,
    phone_number nvarchar(20),
    reg_date     datetime,
    constraint PK_User_Id PRIMARY KEY (id),
    constraint CK_User_Password CHECK (len(password) >= 8),
    constraint UQ_User_Email UNIQUE (email),
    constraint UQ_User_Login UNIQUE (login),

)

--Товары
create table Goods
(
    id          int identity,
    name        nvarchar(200) not null,
    description nvarchar(1000),
    price       int           not null,
    stock_qty   int,
    add_date    datetime,
    category    int,
    constraint PK_Good_Id primary key (id),
    constraint CK_Good_Price check (price > 0),
    constraint CK_Good_Stock check (stock_qty >= 0)

)
--Категории
create table Category
(
    id          int identity,
    name        nvarchar(200) not null,
    description nvarchar(1000),
    constraint PK_Category_Id primary key (id),
)

--связующая Товары-Категории
create table Categories_Goods
(
    id_category int,
    id_good     int,
    constraint PK_Good_To_Category primary key (id_category, id_good),
    constraint FK_Good_To_Category foreign key (id_good) references Goods (id),
    constraint FK_Category_To_Good foreign key (id_category) references Category (id)
)

--Статусы заказа
create table Statuses
(
    id     int identity,
    status nvarchar(500) not null,
    constraint PK_Status_Id primary key (id),
)


-- Заказы
create table Orders
(
    id              int identity not null,
    customer_id     int          not null,
    creation_date   datetime,
    grand_total     int,
    order_status    int default 1,
    ship_to_address nvarchar(500)
        constraint PK_Order_Id primary key (id),
    constraint CK_Order_Grand_Total check (grand_total >= 0),
    constraint FK_Order_Status foreign key (order_status) references Statuses (id),
    constraint FK_Order_User foreign key (customer_id) references Users (id)
)

-- история заказов
create table History
(
    id              int identity not null,
    order_id        int          not null,
    previous_status int,
    current_status  int          not null,
    edit_date       datetime,
    comment         nvarchar(300),
    constraint PK_History_Id primary key (id),
)

--связующая Заказы-Товары
create table Order_Items
(
    id             int identity not null,
    order_id       int          not null,
    good_id        int          not null,
    order_quantity int          not null,
    order_price    int          not null, --цена на момент заказа
    constraint PK_Order_Items_Id primary key (id),
    constraint CK_Order_Items_Quantity check (order_quantity > 0),
    constraint FK_Order_Items_Order_Id foreign key (order_id) references Orders (id) on delete cascade,
    constraint FK_Order_Items_Good_Id foreign key (good_id) references Goods (id)
)

--процЫдурки

--добавление пользователя
create procedure sp_add_user @login NVARCHAR(50),
                             @password nvarchar(100),
                             @name nvarchar(100),
                             @email nvarchar(255),
                             @phone_number nvarchar(20) = null,
                             @surname nvarchar(100) = null,
                             @patronym nvarchar(100) = null
as
begin try
    begin transaction
        insert into Users(login, password, name, email, reg_date, phone_number, surname, patronym)
        values (@login, @password, @name, @email, getdate(),
                @phone_number, @surname, @patronym)
    commit
end try
begin catch
    print N'Ошибка'
end catch;
go

--добавление товара
alter procedure sp_add_item @name NVARCHAR(200),
                            @price int
as
begin try
    begin transaction
        insert into Goods(name, price, add_date) values (@name, @price, getdate())
    commit
end try
begin catch
    print N'Ошибка'
end catch;
go

--изменение количества на складе
create procedure sp_alter_quantity @good_id int,
                                   @stock_quantity int
as
begin try
    begin transaction
        update Goods
        set stock_qty = @stock_quantity
        where id = @good_id
    commit
end try
begin catch
    print N'Ошибка'
end catch
go

--триггер для автоматической регистрации времени изменения заказа
create trigger History_Modify_Record
    on History
    after INSERT, update
    as update History
       set edit_date = getdate()
       where id = (select id from inserted)
go

--поиск товара по названию
create procedure sp_find_good_by_name @name nvarchar(200)
as
select *
from Goods
where name like @name;
go
exec sp_find_good_by_name N'%лька'

--присвоение категории товару
create procedure sp_product_add_category --присвоение категории товару
    @id_good int,
    @id_category int
as
begin try
    begin transaction
        update Categories_Goods
        set id_category = @id_category
        where id_good = @id_good
    commit
end try
begin catch
    print N'Ошибка'
end catch;
go

--товары по категории
create procedure sp_get_goods_by_category @id_category int --товары по категории
as
begin try
    begin transaction
        select *
        from Goods as g
                 join Categories_Goods as cg on g.id = cg.id_good
        where cg.id_category = @id_category
    commit
end try
begin catch
    print N'Ошибка'
end catch;
go


--Вьюха для пользователей. Скрывает почту и телефон.
create view user_info
as
select id,
       login,
       name,
       surname,
       patronym,
       concat
       (substring(email, 1, 2), '****', substring(email, 7, 250)) as email,
       concat(substring(phone_number, 1, 3), '*****',
              substring(phone_number, 9, 10))                     as phone,
       reg_date
from Users;
go


--автоматически пересчитывает итоговую стоимость заказа при изменении
create trigger TR_Order_Items_UpdateGT
    on Order_Items
    after insert , update , delete
    as
begin

    update o
    set grand_total = (select ISNULL(SUM(oi.order_quantity * oi.order_price), 0) --если в заказе есть товар, то вернет общую сумму
                       from Order_Items as oi
                       where oi.id = o.id)
    from Orders as o
    where o.id in (select order_id
                   from Inserted
                   union
                   select order_id
                   from Deleted);
end ;
    select top(10)
    from  Goods;