db lab#2

USE master
GO

IF EXISTS (
    SELECT name
    FROM sys.databases
    WHERE name = N'Nagaev' )
ALTER DATABASE [Nagaev] set single_user with rollback immediate
GO

IF EXISTS (
    SELECT name
    FROM sys.databases
    WHERE name = N'Nagaev' )
DROP DATABASE [Nagaev]
GO

CREATE DATABASE [Nagaev]
GO

USE [Nagaev]
GO

CREATE TABLE region (
    id TINYINT,
    name NVARCHAR(30),
    CONSTRAINT PK_region_id PRIMARY KEY (id)
)
GO

CREATE TABLE rcode (
    region_id tinyint,
    code tinyint,
    CONSTRAINT PK_region_code PRIMARY KEY (code),
    CONSTRAINT FK_region_id FOREIGN KEY (region_id) REFERENCES region(id) ON UPDATE CASCADE,
)
GO

CREATE TABLE color
(
    id TINYINT,
    name NVARCHAR(20),
    CONSTRAINT PK_color_id PRIMARY KEY (id)
)
GO

CREATE TABLE post (
    id tinyint NOT NULL,
    name varchar(20) NULL,
    CONSTRAINT PK_post_id PRIMARY KEY (id),
)
GO

CREATE FUNCTION CorrectNumber (@num VARCHAR(30), @rcode tinyint)
RETURNS int
AS
BEGIN
    DECLARE @str_rcode tinyint
    select @str_rcode = CONVERT(varchar, @rcode)
    if (UPPER(@num) like '[АВЕКМНОРСТУХABEKMHOPCTYX][0-9][0-9][0-9][АВЕКМНОРСТУХABEKMHOPCTYX][АВЕКМНОРСТУХABEKMHOPCTYX]'
        and @num not like '%000%'
        and (@str_rcode like '[0-9][0-9]' or @str_rcode like '[127][0-9][0-9]'))
        return 0
    return -1
END
GO

CREATE FUNCTION CorrectRecord (@date DATETIME, @dir VARCHAR(1), @auto_number nvarchar(7), @auto_rcode tinyint, @id tinyint)
RETURNS int
AS
BEGIN
    if NOT EXISTS(
        SELECT * from record
        WHERE record.id != @id and record.auto_number = @auto_number and
              record.auto_rcode = @auto_rcode
        )
        return 0

    if EXISTS(
        SELECT * from record
        INNER JOIN
            (SELECT MAX(catchtime) as max_date, auto_number FROM record
                WHERE catchtime < @date and @auto_number = auto_number and @auto_rcode = auto_rcode
                GROUP BY auto_number) r
        ON record.catchtime = r.max_date and record.auto_number = r.auto_number
        WHERE @dir != record.direction
        )
        return 0

    return -1
END
GO

CREATE TABLE auto (
    color_id tinyint NOT NULL,
    number NVARCHAR(7) NOT NULL,
    rcode tinyint NOT NULL,
    owner_name NVARCHAR(100) NOT NULL,
    CONSTRAINT PK_number PRIMARY KEY CLUSTERED
    (
        number ASC,
        rcode ASC
    ),
    CONSTRAINT FK_region_code FOREIGN KEY (rcode) REFERENCES rcode(code) ON UPDATE CASCADE,
    CONSTRAINT FK_color_id FOREIGN KEY (color_id) REFERENCES color(id) ON UPDATE CASCADE
)
GO

CREATE TABLE record (
    id tinyint NOT NULL,
    post_id tinyint NOT NULL,
    auto_number nvarchar(7) NOT NULL,
    auto_rcode tinyint,
    catchtime DATETIME NOT NULL,
    direction CHAR NOT NULL,
    CONSTRAINT CK_direction CHECK (direction = '>' or direction = '<'),
    CONSTRAINT CK_record CHECK (dbo.CorrectRecord(catchtime, direction, auto_number, auto_rcode, id) = 0),
    CONSTRAINT PK_record_id PRIMARY KEY (id),
    CONSTRAINT FK_post_id FOREIGN KEY (post_id) REFERENCES post(id) ON UPDATE CASCADE,
    CONSTRAINT FK_auto_id FOREIGN KEY (auto_number, auto_rcode) REFERENCES auto(number, rcode) ON UPDATE CASCADE,
)
GO

CREATE TRIGGER on_insert_auto ON auto FOR INSERT AS
BEGIN
    IF EXISTS (
    SELECT *
    FROM inserted WHERE dbo.CorrectNumber(number, rcode) = -1
    )
    BEGIN
        PRINT 'Некорректная запись'
        ROLLBACK TRANSACTION
    END
END
GO

CREATE FUNCTION GetAutoType (
    @fromPost tinyint,
    @toPost tinyint,
    @fromRegion tinyint
)
RETURNS VARCHAR(30)
AS
BEGIN
    if (@fromPost != @toPost AND @fromRegion != 1)
        RETURN 'Транзитный'
    if (@fromPost = @toPost AND @fromRegion != 1)
        RETURN 'Иногородний'
    if (@fromRegion = 1)
        RETURN 'Местный'
    RETURN 'Прочий'
END
GO

INSERT INTO region(id, name) VALUES
(1, N'Свердловская область'),
(2, N'Самарская область'),
(3, N'Московская область'),
(4, N'Челябинская область'),
(5, N'Краснодарский край')
GO

INSERT INTO rcode(region_id, code) VALUES
(1, 66), (1, 96),
(2, 63), (2, 163),
(3, 50), (3, 150),
(4, 74), (4, 174),
(5, 23), (5, 93)
GO

INSERT INTO color(id, name) VALUES
(1, N'Красный'),
(2, N'Белый'),
(3, N'Синий'),
(4, N'Черный'),
(5, N'Серый')
GO

INSERT INTO post(id, name) VALUES
(1, 'Северный')
,(2, 'Западный')
,(3, 'Востоный')
,(4, 'Южный')
,(5, 'Резервный')
GO

INSERT INTO auto (color_id, number, rcode, owner_name) VALUES
(1, 'А123АА', 66, N'Артемов'),
(1, 'В224АА', 63, N'Боровиков'),
(1, 'С114АА', 50, N'Волгин'),
(1, 'А213АА', 63, N'Грузде'),
(1, 'В124АА', 163, N'Домашних'),
(2, 'С223АА', 50, N'Жигалов'),
(2, 'А113АА', 23, N'Зейналов'),
(2, 'В214АА', 93, N'Клепинин'),
(2, 'С123АА', 150, N'Ливанов'),
(3, 'А214АА', 50, N'Миронов'),
(3, 'В113АА', 63, N'Наймин'),
(3, 'С224АА', 163, N'Проскурнин'),
(3, 'А124АА', 96, N'Реванов'),
(3, 'В213АА', 163, N'Сивой'),
(4, 'С124АА', 66, N'Тарабанкин'),
(4, 'А224АА', 174, N'Гайнанов'),
(4, 'Н114АА', 163, N'Хоронякин'),
(4, 'Т223АА', 150, N'Чалганов'),
(5, 'Х123АА', 50, N'Вайс'),
(5, 'А213АА', 74, N'Епифанов')
GO

INSERT INTO record (id, post_id, auto_number, auto_rcode, catchtime, direction) VALUES
(1, 1, 'А123АА', 66, N'20120618 10:34:08', N'>')
,(2, 2, 'А123АА', 66, N'20120618 10:54:09', N'<')
,(3, 2, 'В224АА', 63, N'20120618 10:10:00', N'>')
,(4, 1, 'В224АА', 63, N'20120618 10:34:09', N'<')
,(5, 3, 'С114АА', 50, N'20120618 11:12:13', N'>')
,(6, 4, 'С114АА', 50, N'20120618 11:59:12', N'<')
,(7, 4, 'А213АА', 63, N'20120618 10:34:08', N'>')
,(8, 3, 'А213АА', 63, N'20120618 13:37:15', N'<')
,(9, 5, 'В124АА', 163, N'20120618 09:01:05', N'>')
,(10, 1, 'В124АА', 163, N'20120618 10:13:49', N'<')
,(11, 1, 'С223АА', 50, N'20120618 12:34:08', N'>')
,(12, 1, 'С223АА', 50, N'20120618 12:44:09', N'<')
,(13, 2, 'А113АА', 23, N'20120618 13:34:08', N'>')
,(14, 2, 'А113АА', 23, N'20120618 15:21:09', N'<')
,(15, 3, 'В214АА', 93, N'20120618 10:11:18', N'>')
,(16, 3, 'В214АА', 93, N'20120618 21:56:21', N'<')
,(17, 4, 'С123АА', 150, N'20120618 12:34:08', N'>')
,(18, 5, 'С123АА', 150, N'20120618 13:34:09', N'<')
,(19, 5, 'А214АА', 50, N'20120618 14:14:14', N'>')
,(20, 1, 'А214АА', 50, N'20120618 14:34:14', N'<')
,(21, 2, 'В113АА', 63, N'20120618 15:34:08', N'>')
,(22, 2, 'В113АА', 63, N'20120618 16:37:38', N'<')
,(23, 1, 'С224АА', 163, N'20120618 10:34:08', N'>')
,(24, 2, 'С224АА', 163, N'20120618 11:36:09', N'<')
,(25, 1, 'А124АА', 96, N'20120618 14:39:59', N'>')
,(26, 3, 'А124АА', 96, N'20120618 15:34:59', N'<')
,(27, 1, 'В213АА', 163, N'20120618 17:34:08', N'>')
,(28, 3, 'В213АА', 163, N'20120618 23:34:09', N'<')
,(29, 5, 'С124АА', 66, N'20120618 18:24:00', N'>')
,(30, 1, 'С124АА', 66, N'20120618 19:34:03', N'<')
,(31, 2, 'А224АА', 174, N'20120618 16:22:08', N'>')
,(32, 1, 'А224АА', 174, N'20120618 20:51:09', N'<')
,(33, 2, 'Н114АА', 163, N'20120618 11:03:08', N'>')
,(34, 1, 'Н114АА', 163, N'20120618 19:33:09', N'<')
,(35, 3, 'Т223АА', 150, N'20120618 19:28:17', N'>')
,(36, 3, 'Т223АА', 150, N'20120618 19:54:49', N'<')
,(37, 1, 'Х123АА', 50, N'20120618 23:00:00', N'>')
,(38, 4, 'Х123АА', 50, N'20120618 23:59:59', N'<')
,(39, 5, 'А213АА', 74, N'20120618 20:20:18', N'>')
,(40, 4, 'А213АА', 74, N'20120618 22:28:41', N'<')
--,(41, 5, 'А213АА', 74, N'20120618 10:34:20', N'<')
GO

DECLARE @Temp TABLE
(
    from_city tinyint,
    from_post tinyint,
    to_post tinyint
);

INSERT INTO
@Temp
SELECT rcode.region_id, rr1.post_id, rr2.post_id FROM auto a
    JOIN record rr1 ON rr1.auto_number = a.number and rr1.auto_rcode = a.rcode AND rr1.direction = N'>'
    JOIN record rr2 ON rr2.auto_number = a.number and rr2.auto_rcode = a.rcode AND rr2.direction = N'<'
    JOIN rcode ON rcode.code = a.rcode
GROUP BY a.owner_name, a.number, rr1.post_id, rr2.post_id, rcode.region_id

SELECT dbo.GetAutoType(from_post, to_post, from_city) as 'Тип', COUNT(*) as 'Количество' FROM @Temp
GROUP BY dbo.GetAutoType(from_post, to_post, from_city)
GO

DECLARE @Temp TABLE
(
    auto_number nvarchar(7),
    auto_rcode tinyint,
    from_city tinyint,
    from_post tinyint,
    to_post tinyint
);

INSERT INTO
@Temp
SELECT a.number, a.rcode, rr3.region_id, rr1.post_id, rr2.post_id FROM auto a
    JOIN record rr1 ON rr1.auto_number = a.number and rr1.auto_rcode = a.rcode AND rr1.direction = N'>'
    JOIN record rr2 ON rr2.auto_number = a.number AND rr2.auto_rcode = a.rcode and rr2.direction = N'<'
    JOIN rcode rr3 ON rr3.code = a.rcode
GROUP BY a.owner_name, rr1.post_id, rr2.post_id, rr3.region_id, a.number, a.rcode

SELECT
       a.owner_name as 'Владелец',
       a.number + CAST(a.rcode AS VARCHAR) as 'Номер авто',
       FORMAT (r1.catchtime, 'HH\:mm\:ss', 'ru-RU' ) as 'Время въезда',
       FORMAT (r2.catchtime, 'HH\:mm\:ss', 'ru-RU' ) as 'Время выезда',
       dbo.GetAutoType(from_post, to_post, from_city) as 'Тип',
       region.name as 'Родной регион'
FROM @Temp t
    JOIN auto a ON a.number = t.auto_number and a.rcode  = t.auto_rcode
    JOIN record r1 ON r1.direction = '>' and r1.auto_number = t.auto_number and r1.auto_rcode = t.auto_rcode
    JOIN record r2 ON r2.direction = '<' and r2.auto_number = t.auto_number and r2.auto_rcode = t.auto_rcode
    JOIN rcode rcode ON rcode.code = a.rcode
    JOIN region ON rcode.region_id = region.id
WHERE dbo.GetAutoType(from_post, to_post, from_city) like '%'
GROUP BY a.owner_name, r1.catchtime,  r2.catchtime, from_post, to_post,
         from_city, region.name, a.number, a.rcode
ORDER BY a.owner_name
GO




USE [Nagaev]
GO

INSERT INTO auto (color_id, number, rcode, owner_name) VALUES
(3, 'А21АА', 74, N'Сбиванов')
GO

INSERT INTO auto (color_id, number, rcode, owner_name) VALUES
(3, 'А212АG', 74, N'Сбиванов')
GO

INSERT INTO auto (color_id, number, rcode, owner_name) VALUES
(3, 'А000АA', 74, N'Сбиванов')
GO

INSERT INTO auto (color_id, number, rcode, owner_name) VALUES
(3, 'А100АA', 215, N'Сбиванов')
GO

INSERT INTO record (id, post_id, auto_number, auto_rcode, catchtime, direction) VALUES
 (41, 5, 'А213BC', 74, N'20120618 20:34:10', N'<')
,(42, 5, 'А213BC', 74, N'20120618 20:35:20', N'<')
GO

INSERT INTO record (id, post_id, auto_number, auto_rcode, catchtime, direction) VALUES
 (41, 5, 'А213BC', 74, N'20120618 20:34:10', N'>')
,(42, 5, 'А213BC', 74, N'20120618 20:35:20', N'>')
GO