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_regio_id PRIMARY KEY (code),
    CONSTRAINT FK_regio_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 '[А-ЯA-Z][0-9][0-9][0-9][А-ЯA-Z][А-ЯA-Z]' 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_id tinyint, @id tinyint)
RETURNS int
AS
BEGIN
    if ((SELECT COUNT(1) from record WHERE record.id != @id and record.auto_id = @auto_id) = 0)
        return 0

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

    return -1
END
GO

CREATE TABLE auto (
    id tinyint NOT NULL,
    color_id tinyint NOT NULL,
    number NVARCHAR(7) NOT NULL,
    rcode tinyint NOT NULL,
    owner_name NVARCHAR(100) NOT NULL,
    CONSTRAINT PK_auto_id PRIMARY KEY (id),
    CONSTRAINT FK_regi_id 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 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 TABLE record (
    id tinyint NOT NULL,
    post_id tinyint NOT NULL,
    auto_id tinyint NOT NULL,
    catchtime DATETIME NOT NULL,
    direction CHAR NOT NULL,
    CONSTRAINT CK_direction CHECK (direction = '>' or direction = '<'),
    CONSTRAINT CK_record CHECK (dbo.CorrectRecord(catchtime, direction, auto_id, id) = 0),
    CONSTRAINT FK_post_id FOREIGN KEY (post_id) REFERENCES post(id) ON UPDATE CASCADE,
    CONSTRAINT FK_auto_id FOREIGN KEY (auto_id) REFERENCES auto(id) ON UPDATE CASCADE,
)
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 (id, color_id, number, rcode, owner_name) VALUES
(1, 1, 'A123АА', 66, N'Артемов'),
(2, 1, 'В224АА', 63, N'Боровиков'),
(3, 1, 'С114АА', 50, N'Волгин'),
(4, 1, 'А213АА', 63, N'Грузде'),
(5, 1, 'В124АА', 163, N'Домашних'),
(6, 2, 'С223АА', 50, N'Жигалов'),
(7, 2, 'А113АА', 23, N'Зейналов'),
(8, 2, 'В214АА', 93, N'Клепинин'),
(9, 2, 'С123АА', 150, N'Ливанов'),
(10, 3, 'А214АА', 50, N'Миронов'),
(11, 3, 'В113АА', 63, N'Наймин'),
(12, 3, 'С224АА', 163, N'Проскурнин'),
(13, 3, 'А124АА', 96, N'Реванов'),
(14, 3, 'В213АА', 163, N'Сивой'),
(15, 4, 'С124АА', 66, N'Тарабанкин'),
(16, 4, 'А224АА', 174, N'Гайнанов'),
(17, 4, 'Н114АА', 163, N'Хоронякин'),
(18, 4, 'Т223АА', 150, N'Чалганов'),
(19, 5, 'Х123АА', 50, N'Вайс'),
(20, 5, 'А213АА', 74, N'Епифанов')
--(21, 5, 'А21АА', 74, N'Сбиванов')
GO

INSERT INTO record (id, post_id, auto_id, catchtime, direction) VALUES
(1, 1, 1, N'20120618 10:34:08', N'>')
,(2, 2, 1, N'20120618 10:54:09', N'<')
,(3, 2, 2, N'20120618 10:10:00', N'>')
,(4, 1, 2, N'20120618 10:34:09', N'<')
,(5, 3, 3, N'20120618 11:12:13', N'>')
,(6, 4, 3, N'20120618 11:59:12', N'<')
,(7, 4, 4, N'20120618 10:34:08', N'>')
,(8, 3, 4, N'20120618 13:37:15', N'<')
,(9, 5, 5, N'20120618 09:01:05', N'>')
,(10, 1, 5, N'20120618 10:13:49', N'<')
,(11, 1, 6, N'20120618 12:34:08', N'>')
,(12, 1, 6, N'20120618 12:44:09', N'<')
,(13, 2, 7, N'20120618 13:34:08', N'>')
,(14, 2, 7, N'20120618 15:21:09', N'<')
,(15, 3, 8, N'20120618 10:11:18', N'>')
,(16, 3, 8, N'20120618 21:56:21', N'<')
,(17, 4, 9, N'20120618 12:34:08', N'>')
,(18, 5, 9, N'20120618 13:34:09', N'<')
,(19, 5, 10, N'20120618 14:14:14', N'>')
,(20, 1, 10, N'20120618 14:34:14', N'<')
,(21, 2, 11, N'20120618 15:34:08', N'>')
,(22, 2, 11, N'20120618 16:37:38', N'<')
,(23, 1, 12, N'20120618 10:34:08', N'>')
,(24, 2, 12, N'20120618 11:36:09', N'<')
,(25, 1, 13, N'20120618 14:39:59', N'>')
,(26, 3, 13, N'20120618 15:34:59', N'<')
,(27, 1, 14, N'20120618 17:34:08', N'>')
,(28, 3, 14, N'20120618 23:34:09', N'<')
,(29, 5, 15, N'20120618 18:24:00', N'>')
,(30, 1, 15, N'20120618 19:34:03', N'<')
,(31, 2, 16, N'20120618 16:22:08', N'>')
,(32, 1, 16, N'20120618 20:51:09', N'<')
,(33, 2, 17, N'20120618 11:03:08', N'>')
,(34, 1, 17, N'20120618 19:33:09', N'<')
,(35, 3, 18, N'20120618 19:28:17', N'>')
,(36, 3, 18, N'20120618 19:54:49', N'<')
,(37, 1, 19, N'20120618 23:00:00', N'>')
,(38, 4, 19, N'20120618 23:59:59', N'<')
,(39, 5, 20, N'20120618 20:20:18', N'<')
,(40, 4, 20, N'20120618 22:28:41', N'>')
--,(41, 5, 20, 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_id = a.id AND rr1.direction = N'>'
    JOIN record rr2 ON rr2.auto_id = a.id 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_id tinyint,
from_city tinyint,
from_post tinyint,
to_post tinyint
);

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

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.id = t.auto_id
    JOIN record r1 ON r1.direction = '>' and r1.auto_id = t.auto_id
    JOIN record r2 ON r2.direction = '<' and r2.auto_id = t.auto_id
    JOIN rcode rcode ON rcode.code = a.rcode
    JOIN region ON rcode.region_id = region.id
GROUP BY a.owner_name, r1.catchtime,  r2.catchtime, from_post, to_post,
         from_city, region.name, a.number, a.rcode
ORDER BY r1.catchtime
GO