USE master
GO

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

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

CREATE DATABASE KN_303_shvedov
GO

USE KN_303_shvedov
GO

IF EXISTS(
  SELECT *
    FROM sys.schemas
   WHERE name = N'shvedov'
)
DROP SCHEMA shvedov
GO

CREATE SCHEMA shvedov
GO

IF OBJECT_ID (N'KN_303_shvedov.shvedov.isStateNumberCorrect ', N'FN') IS NOT NULL
   DROP FUNCTION shvedov.isStateNumberCorrect;
GO

CREATE FUNCTION shvedov.isStateNumberCorrect(@stateNumber VARCHAR(9))
RETURNS BIT
AS
BEGIN
    IF (@stateNumber like '[АВЕКМНОРСТУХавекмнорстух][1-9][0-9][0-9][АВЕКМНОРСТУХавекмнорстух][АВЕКМНОРСТУХавекмнорстух][1,2,7][0-9][0-9]' OR
        @stateNumber like '[АВЕКМНОРСТУХ][1-9][0-9][0-9][АВЕКМНОРСТУХавекмнорстух][АВЕКМНОРСТУХавекмнорстух][0-9][0-9]' OR

        @stateNumber like '[АВЕКМНОРСТУХавекмнорстух]0[1-9][0-9][АВЕКМНОРСТУХавекмнорстух][АВЕКМНОРСТУХавекмнорстух][1,2,7][0-9][0-9]' OR
        @stateNumber like '[АВЕКМНОРСТУХавекмнорстух]0[1-9][0-9][АВЕКМНОРСТУХавекмнорстух][АВЕКМНОРСТУХавекмнорстух][0-9][0-9]' OR

        @stateNumber like '[АВЕКМНОРСТУХавекмнорстух]00[1-9][АВЕКМНОРСТУХавекмнорстух][АВЕКМНОРСТУХавекмнорстух][1,2,7][0-9][0-9]' OR
        @stateNumber like '[АВЕКМНОРСТУХавекмнорстух]00[1-9][АВЕКМНОРСТУХавекмнорстух][АВЕКМНОРСТУХавекмнорстух][0-9][0-9]')
        BEGIN
            RETURN 1;
        END
    RETURN 0;
END;
GO

IF OBJECT_ID (N'KN_303_shvedov.shvedov.getRegionNumber', N'FN') IS NOT NULL
   DROP FUNCTION shvedov.getRegionNumber;
GO

CREATE FUNCTION shvedov.getRegionNumber(@stateNumber VARCHAR(9))
RETURNS int
AS
BEGIN
    DECLARE @regionNumber int = CAST(SUBSTRING(@stateNumber, LEN(@stateNumber) - 1, LEN(@stateNumber)) AS INT)
    IF ((SELECT COUNT(*) FROM KN_303_shvedov.shvedov.Regions WHERE regionNumber = @regionNumber) = 1)
        RETURN @regionNumber
    RETURN null
END;
GO

CREATE TABLE KN_303_shvedov.shvedov.TravelInformationForInsert
(
    stateNumber varchar(9) CHECK (shvedov.isStateNumberCorrect(stateNumber) = 1) NOT NULL,
    isInside int NOT NULL,
    postId tinyint NOT NULL,
    crossingTime time NOT NULL
)


CREATE TABLE KN_303_shvedov.shvedov.TravelInformation
(
    stateNumberId int NOT NULL,
    isInside bit NOT NULL,
    postId tinyint NOT NULL,
    crossingTime time NOT NULL
)
GO

CREATE TABLE KN_303_shvedov.shvedov.Post
(
    postId tinyint NOT NULL,
    postName varchar(40) NOT NULL
    CONSTRAINT PK_postId PRIMARY KEY (postId)
)

CREATE TABLE KN_303_shvedov.shvedov.StateNumberInformation
(
    id int NOT NULL IDENTITY PRIMARY KEY,
    stateNumber varchar(9) NOT NULL,
    regionNumber AS shvedov.getRegionNumber(stateNumber),
)

CREATE INDEX stateNumberIndex ON KN_303_shvedov.shvedov.StateNumberInformation (stateNumber);


CREATE TABLE KN_303_shvedov.shvedov.Regions
(
    regionNumber tinyint NOT NULL,
    regionName varchar(60) NOT NULL
    CONSTRAINT PK_regionNumber PRIMARY KEY (regionNumber)
)
GO

ALTER TABLE KN_303_shvedov.shvedov.TravelInformation
WITH CHECK ADD CONSTRAINT FK_postId FOREIGN KEY (postId)
REFERENCES KN_303_shvedov.shvedov.Post (postId)
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE KN_303_shvedov.shvedov.TravelInformation
WITH CHECK ADD CONSTRAINT stateNumberId FOREIGN KEY (stateNumberId)
REFERENCES KN_303_shvedov.shvedov.StateNumberInformation (id)
ON UPDATE CASCADE
ON DELETE CASCADE
GO

INSERT INTO KN_303_shvedov.shvedov.Post (postId, postName) VALUES
(0, 'Юг')
,(1, 'Север')
,(2, 'Запад')
,(3, 'Восток')
GO

INSERT INTO KN_303_shvedov.shvedov.Regions (regionNumber, regionName) VALUES
(1, 'Республика Адыгея')
,(2, 'Республика Башкортостан')
,(3, 'Республика Бурятия')
,(4, 'Республика Алтай')
,(5, 'Республика Дагестан')
,(6, 'Республика Ингушетия')
,(7, 'Кабардино-Балкарская Республика')
,(8, 'Республика Калмыкия')
,(9, 'Карачаево-Черкесская Республика')
,(10, 'Республика Карелия')
,(11, 'Республика Коми')
,(12, 'Республика Марий Эл')
,(13, 'Республика Мордовия')
,(14, 'Республика Саха (Якутия)')
,(15, 'Республика Северная Осетия - Алания')
,(16, 'Республика Татарстан (Татарстан)')
,(17, 'Республика Тыва')
,(18, 'Удмуртская Республика')
,(19, 'Республика Хакасия')
,(20, 'Чеченская Республика')
,(21, 'Чувашская Республика - Чувашия')
,(22, 'Алтайский край')
,(23, 'Краснодарский край')
,(24, 'Красноярский край')
,(25, 'Приморский край')
,(26, 'Ставропольский край')
,(27, 'Хабаровский край')
,(28, 'Амурская область')
,(29, 'Архангельская область')
,(30, 'Астраханская область')
,(31, 'Белгородская область')
,(32, 'Брянская область')
,(33, 'Владимирская область')
,(34, 'Волгоградская область')
,(35, 'Вологодская область')
,(36, 'Воронежская область')
,(37, 'Ивановская область')
,(38, 'Иркутская область')
,(39, 'Калининградская область')
,(40, 'Калужская область')
,(41, 'Камчатский край')
,(42, 'Кемеровская область')
,(43, 'Кировская область')
,(44, 'Костромская область')
,(45, 'Курганская область')
,(46, 'Курская область')
,(47, 'Ленинградская область')
,(48, 'Липецкая область')
,(49, 'Магаданская область')
,(50, 'Московская область')
,(51, 'Мурманская область')
,(52, 'Нижегородская область')
,(53, 'Новгородская область')
,(54, 'Новосибирская область')
,(55, 'Омская область')
,(56, 'Оренбургская область')
,(57, 'Орловская область')
,(58, 'Пензенская область')
,(59, 'Пермский край')
,(60, 'Псковская область')
,(61, 'Ростовская область')
,(62, 'Рязанская область')
,(63, 'Самарская область')
,(64, 'Саратовская область')
,(65, 'Сахалинская область')
,(66, 'Свердловская область')
,(67, 'Смоленская область')
,(68, 'Тамбовская область')
,(69, 'Тверская область')
,(70, 'Томская область')
,(71, 'Тульская область')
,(72, 'Тюменская область')
,(73, 'Ульяновская область')
,(74, 'Челябинская область')
,(75, 'Забайкальский край')
,(76, 'Ярославская область')
,(77, 'г. Москва')
,(78, 'Санкт-Петербург')
,(79, 'Еврейская автономная область')
,(83, 'Ненецкий автономный округ')
,(86, 'Ханты-Мансийский автономный округ - Югра')
,(87, 'Чукотский автономный округ')
,(89, 'Ямало-Ненецкий автономный округ')
,(91, 'Республика Крым')
,(92, 'Севастополь')
,(99, 'Иные территории включая город и космодром Байконур')
GO


IF OBJECT_ID (N'KN_303_shvedov.shvedov.getStateNumber', N'FN') IS NOT NULL
   DROP FUNCTION shvedov.getStateNumber;
GO

CREATE FUNCTION shvedov.getStateNumber(@stateNumberId int)
RETURNS VARCHAR(9)
AS
BEGIN
    DECLARE getStateNumberCursor CURSOR FOR
    SELECT stateNumber
    FROM KN_303_shvedov.shvedov.StateNumberInformation
    WHERE UPPER(@stateNumberId) = id

    OPEN getStateNumberCursor
    DECLARE @result VARCHAR(9)
    FETCH NEXT FROM getStateNumberCursor INTO @result
    CLOSE getStateNumberCursor
    DEALLOCATE getStateNumberCursor
    RETURN @result
END;
GO

IF OBJECT_ID (N'KN_303_shvedov.shvedov.getStateNumberId', N'FN') IS NOT NULL
   DROP FUNCTION shvedov.getStateNumberId;
GO

CREATE FUNCTION shvedov.getStateNumberId(@stateNumber varchar(9))
RETURNS int
AS
BEGIN
    DECLARE getStateNumberIdCursor CURSOR FOR
    SELECT id
    FROM KN_303_shvedov.shvedov.StateNumberInformation
    WHERE UPPER(@stateNumber) = stateNumber

    OPEN getStateNumberIdCursor
    DECLARE @result int
    FETCH NEXT FROM getStateNumberIdCursor INTO @result
    CLOSE getStateNumberIdCursor
    DEALLOCATE getStateNumberIdCursor
    RETURN @result
END;
GO

IF OBJECT_ID (N'KN_303_shvedov.shvedov.formatDirection', N'FN') IS NOT NULL
   DROP FUNCTION shvedov.formatDirection;
GO

CREATE FUNCTION shvedov.formatDirection(@direction bit)
RETURNS varchar(10)
AS
BEGIN
    IF (@direction = 1)
        RETURN 'В город'
    IF (@direction = 0)
        RETURN 'Из города'
    RETURN null
END;
GO


IF OBJECT_ID (N'KN_303_shvedov.shvedov.isCarInCity', N'FN') IS NOT NULL
   DROP FUNCTION shvedov.isCarInCity;
GO

CREATE FUNCTION shvedov.isCarInCity(@stateNumber VARCHAR(9))
RETURNS int
AS
BEGIN
    DECLARE isCarInCityCursor CURSOR FOR
    SELECT isInside
    FROM KN_303_shvedov.shvedov.TravelInformation
    WHERE UPPER(@stateNumber) = shvedov.getstateNumber(stateNumberId)

    OPEN isCarInCityCursor
    DECLARE @result bit
    FETCH NEXT FROM isCarInCityCursor INTO @result
    CLOSE isCarInCityCursor
    DEALLOCATE isCarInCityCursor
    RETURN @result
END;
GO

IF OBJECT_ID (N'KN_303_shvedov.shvedov.getLastTravelTime', N'FN') IS NOT NULL
   DROP FUNCTION shvedov.getLastTravelTime;
GO

CREATE FUNCTION shvedov.getLastTravelTime(@stateNumber VARCHAR(9))
RETURNS time
AS
BEGIN
    DECLARE lastTravelTimeCursor CURSOR FOR
    SELECT crossingTime
    FROM KN_303_shvedov.shvedov.TravelInformation
    WHERE UPPER(@stateNumber) = shvedov.getstateNumber(stateNumberId)

    OPEN lastTravelTimeCursor
    DECLARE @result time
    FETCH NEXT FROM lastTravelTimeCursor INTO @result
    CLOSE lastTravelTimeCursor
    DEALLOCATE lastTravelTimeCursor
    RETURN @result
END;
GO

CREATE TRIGGER shvedov.AddTravelInformation
    ON KN_303_shvedov.shvedov.TravelInformationForInsert
    INSTEAD OF INSERT
AS BEGIN
    DECLARE @stateNumber VARCHAR(9)
    DECLARE @isInside INT
    DECLARE @postId TINYINT
    DECLARE @crossingTime DATETIME

    DECLARE travleCursor CURSOR FOR
    SELECT stateNumber, isInside, postId, crossingTime
    FROM inserted

    OPEN travleCursor
    FETCH NEXT FROM travleCursor INTO @stateNumber, @isInside, @postId, @crossingTime

    WHILE @@FETCH_STATUS = 0
    BEGIN
            IF NOT EXISTS(SELECT id
                FROM KN_303_shvedov.shvedov.StateNumberInformation
                WHERE stateNumber = UPPER(@stateNumber))
                BEGIN
                    INSERT INTO KN_303_shvedov.shvedov.StateNumberInformation (stateNumber) VALUES
                    (UPPER(@stateNumber))
                END

            DECLARE tmpTableCursor CURSOR FOR
                SELECT TOP 1 id
                FROM KN_303_shvedov.shvedov.StateNumberInformation
                WHERE stateNumber = UPPER(@stateNumber)
            OPEN tmpTableCursor
            DECLARE @stateNumberId INT
            FETCH NEXT FROM tmpTableCursor INTO @stateNumberId
            CLOSE tmpTableCursor
            DEALLOCATE tmpTableCursor
            IF (shvedov.isCarInCity(@stateNumber) IS NULL OR shvedov.isCarInCity(@stateNumber) <> @isInside
                AND datediff(mi, shvedov.getLastTravelTime(@stateNumber), @crossingTime) >= 5)
            BEGIN
                INSERT INTO KN_303_shvedov.shvedov.TravelInformation (stateNumberId, isInside, postId, crossingTime) VALUES
                    (@stateNumberId, @isInside, @postId, @crossingTime)
            END
        FETCH NEXT FROM travleCursor INTO @stateNumber, @isInside, @postId, @crossingTime
    END

    CLOSE travleCursor
    DEALLOCATE travleCursor
END
GO




IF OBJECT_ID (N'KN_303_shvedov.shvedov.getInsidePostId', N'FN') IS NOT NULL
   DROP FUNCTION shvedov.getInsidePostId;
GO

CREATE FUNCTION shvedov.getInsidePostId(@stateNumber varchar(9))
RETURNS tinyint
AS
BEGIN
    DECLARE getInsidePostIdCursor CURSOR FOR
    SELECT postId
    FROM KN_303_shvedov.shvedov.TravelInformation
    WHERE stateNumberId = shvedov.getStateNumberId(@stateNumber) AND isInside = 1

    OPEN getInsidePostIdCursor
    DECLARE @result tinyint
    FETCH NEXT FROM getInsidePostIdCursor INTO @result
    CLOSE getInsidePostIdCursor
    DEALLOCATE getInsidePostIdCursor
    RETURN @result
END;
GO

IF OBJECT_ID (N'KN_303_shvedov.shvedov.getPostName', N'FN') IS NOT NULL
   DROP FUNCTION shvedov.getPostName;
GO

CREATE FUNCTION shvedov.getPostName(@postId tinyint)
RETURNS varchar(30)
AS
BEGIN
    DECLARE getInsidePostIdCursor CURSOR FOR
    SELECT postName
    FROM KN_303_shvedov.shvedov.Post
    WHERE postId = @postId

    OPEN getInsidePostIdCursor
    DECLARE @result varchar(30)
    FETCH NEXT FROM getInsidePostIdCursor INTO @result
    CLOSE getInsidePostIdCursor
    DEALLOCATE getInsidePostIdCursor
    RETURN @result
END;
GO


IF OBJECT_ID (N'KN_303_shvedov.shvedov.getOutsidePostId', N'FN') IS NOT NULL
   DROP FUNCTION shvedov.getOutsidePostId;
GO

CREATE FUNCTION shvedov.getOutsidePostId(@stateNumber varchar(9))
RETURNS tinyint
AS
BEGIN
    DECLARE getOutsidePostIdCursor CURSOR FOR
    SELECT postId
    FROM KN_303_shvedov.shvedov.TravelInformation
    WHERE stateNumberId = shvedov.getStateNumberId(@stateNumber) AND isInside = 0

    OPEN getOutsidePostIdCursor
    DECLARE @result tinyint
    FETCH NEXT FROM getOutsidePostIdCursor INTO @result
    CLOSE getOutsidePostIdCursor
    DEALLOCATE getOutsidePostIdCursor
    RETURN @result
END;
GO

IF OBJECT_ID (N'KN_303_shvedov.shvedov.getOutsideData', N'FN') IS NOT NULL
   DROP FUNCTION shvedov.getOutsidePostId;
GO

CREATE FUNCTION shvedov.getOutsideData(@stateNumber varchar(9))
RETURNS time
AS
BEGIN
    DECLARE getOutsidePostIdCursor CURSOR FOR
    SELECT crossingTime
    FROM KN_303_shvedov.shvedov.TravelInformation
    WHERE stateNumberId = shvedov.getStateNumberId(@stateNumber) AND isInside = 0

    OPEN getOutsidePostIdCursor
    DECLARE @result time
    FETCH NEXT FROM getOutsidePostIdCursor INTO @result
    CLOSE getOutsidePostIdCursor
    DEALLOCATE getOutsidePostIdCursor
    RETURN @result
END;
GO

IF OBJECT_ID (N'KN_303_shvedov.shvedov.getInsideData', N'FN') IS NOT NULL
   DROP FUNCTION shvedov.getOutsidePostId;
GO

CREATE FUNCTION shvedov.getInsideData(@stateNumber varchar(9))
RETURNS time
AS
BEGIN
    DECLARE getOutsidePostIdCursor CURSOR FOR
    SELECT crossingTime
    FROM KN_303_shvedov.shvedov.TravelInformation
    WHERE stateNumberId = shvedov.getStateNumberId(@stateNumber) AND isInside = 1

    OPEN getOutsidePostIdCursor
    DECLARE @result time
    FETCH NEXT FROM getOutsidePostIdCursor INTO @result
    CLOSE getOutsidePostIdCursor
    DEALLOCATE getOutsidePostIdCursor
    RETURN @result
END;
GO

IF OBJECT_ID (N'KN_303_shvedov.shvedov.isResident', N'FN') IS NOT NULL
   DROP FUNCTION shvedov.isResident;
GO

CREATE FUNCTION shvedov.isResident(@stateNumberId int)
RETURNS bit
AS
BEGIN
    if (shvedov.getRegionNumber(shvedov.getStateNumber(@stateNumberId)) = 66)
        RETURN 1
    RETURN 0
END;
GO

IF OBJECT_ID (N'KN_303_shvedov.shvedov.isTransit', N'FN') IS NOT NULL
   DROP FUNCTION shvedov.isTransit;
GO

CREATE FUNCTION shvedov.isTransit(@stateNumberId int)
RETURNS bit
AS
BEGIN
    IF (shvedov.isResident(@stateNumberId) = 0)
    BEGIN
        DECLARE @insidePostId tinyint = shvedov.getInsidePostId(shvedov.getStateNumber(@stateNumberId))
        DECLARE @outsidePostId tinyint = shvedov.getOutsidePostId(shvedov.getStateNumber(@stateNumberId))
        if (@insidePostId != @outsidePostId)
            RETURN 1
    END
    RETURN 0
END;
GO

IF OBJECT_ID (N'KN_303_shvedov.shvedov.isNonresident', N'FN') IS NOT NULL
   DROP FUNCTION shvedov.isNonresident;
GO

CREATE FUNCTION shvedov.isNonresident(@stateNumberId int)
RETURNS bit
AS
BEGIN
    IF (shvedov.isResident(@stateNumberId) = 0)
    BEGIN
        DECLARE @insidePostId tinyint = shvedov.getInsidePostId(shvedov.getStateNumber(@stateNumberId))
        DECLARE @outsidePostId tinyint = shvedov.getOutsidePostId(shvedov.getStateNumber(@stateNumberId))
        if (@insidePostId = @outsidePostId)
            RETURN 1
    END
    RETURN 0
END;
GO

INSERT INTO KN_303_shvedov.shvedov.TravelInformationForInsert (stateNumber, isInside, postId, crossingTime) VALUES
    ('В725ХТ777', 1, 3, CONVERT(time, '0:5'))
,('У130ЕА777', 0, 3, CONVERT(time, '0:10'))
,('В771КВ777', 0, 1, CONVERT(time, '0:19'))
,('Х424АХ66', 1, 1, CONVERT(time, '0:22'))
,('Е700МВ77', 1, 1, CONVERT(time, '0:30'))
,('Н677СО102', 1, 2, CONVERT(time, '0:39'))
,('М642АА777', 1, 1, CONVERT(time, '0:41'))
,('М233ХЕ66', 1, 2, CONVERT(time, '0:49'))
,('Н649ХТ777', 1, 2, CONVERT(time, '0:50'))
,('У671НО777', 0, 0, CONVERT(time, '0:59'))
,('А587КЕ77', 0, 0, CONVERT(time, '1:1'))
,('С886НЕ77', 1, 0, CONVERT(time, '1:10'))
,('В771КВ777', 1, 1, CONVERT(time, '1:12'))
,('У544ВН777', 0, 0, CONVERT(time, '1:17'))
,('Е700МВ77', 1, 2, CONVERT(time, '1:22'))
,('Н322ХЕ77', 1, 0, CONVERT(time, '1:23'))
,('Е700МВ77', 1, 2, CONVERT(time, '1:26'))
,('У730СВ177', 1, 3, CONVERT(time, '1:32'))
,('О683РЕ66', 1, 0, CONVERT(time, '1:32'))
,('С253УО66', 1, 0, CONVERT(time, '1:39'))
,('Х891МУ102', 1, 3, CONVERT(time, '1:45'))
,('М722СР66', 1, 0, CONVERT(time, '1:50'))
,('М070РР77', 0, 2, CONVERT(time, '1:53'))
,('К186ХМ66', 0, 2, CONVERT(time, '1:56'))
,('М117ВР777', 1, 2, CONVERT(time, '2:3'))
,('Н227СН02', 1, 2, CONVERT(time, '2:10'))
,('Х329СВ66', 1, 1, CONVERT(time, '2:17'))
,('М305ТА102', 1, 3, CONVERT(time, '2:20'))
,('В771КВ777', 1, 2, CONVERT(time, '2:29'))
,('С444РО77', 0, 1, CONVERT(time, '2:33'))
,('С842ВМ66', 0, 3, CONVERT(time, '2:35'))
,('Х891МУ102', 0, 0, CONVERT(time, '2:36'))
,('У544ВН777', 1, 2, CONVERT(time, '2:43'))
,('Х424АХ66', 0, 0, CONVERT(time, '2:46'))
,('У544ВН777', 1, 1, CONVERT(time, '2:50'))
,('В416МХ177', 0, 3, CONVERT(time, '2:53'))
,('Х976РО77', 0, 3, CONVERT(time, '2:54'))
,('Т732ЕС102', 1, 1, CONVERT(time, '2:59'))
,('К787ОХ102', 0, 0, CONVERT(time, '3:4'))
,('Е253ХХ66', 1, 0, CONVERT(time, '3:12'))
,('О683РЕ66', 0, 1, CONVERT(time, '3:13'))
,('О721СВ02', 0, 1, CONVERT(time, '3:22'))
,('А631КН777', 1, 2, CONVERT(time, '3:23'))
,('А593ЕХ66', 0, 1, CONVERT(time, '3:23'))
,('Н044ВС66', 0, 0, CONVERT(time, '3:32'))
,('О409ХК102', 1, 0, CONVERT(time, '3:36'))
,('К485ОС177', 0, 3, CONVERT(time, '3:42'))
,('Е665АТ177', 1, 1, CONVERT(time, '3:45'))
,('О409ХК102', 0, 1, CONVERT(time, '3:49'))
,('А393СЕ777', 0, 1, CONVERT(time, '3:55'))
,('О409ХК102', 0, 3, CONVERT(time, '4:3'))
,('Х329СВ66', 1, 2, CONVERT(time, '4:9'))
,('О407УХ777', 0, 3, CONVERT(time, '4:15'))
,('О601ВУ02', 1, 1, CONVERT(time, '4:21'))
,('Т732ЕС102', 1, 1, CONVERT(time, '4:25'))
,('Н544КТ77', 0, 2, CONVERT(time, '4:33'))
,('Т034КВ02', 1, 2, CONVERT(time, '4:35'))
,('М913ХН177', 0, 0, CONVERT(time, '4:38'))
,('Т732ЕС102', 0, 3, CONVERT(time, '4:39'))
,('Х329СВ66', 0, 0, CONVERT(time, '4:48'))
,('Н592ХМ777', 1, 2, CONVERT(time, '4:54'))
,('Е076ЕВ177', 1, 2, CONVERT(time, '5:3'))
,('М913ХН177', 0, 0, CONVERT(time, '5:8'))
,('В561ТН177', 1, 0, CONVERT(time, '5:16'))
,('К361ВН102', 1, 2, CONVERT(time, '5:22'))
,('Н544КТ77', 0, 1, CONVERT(time, '5:25'))
,('Х891МУ102', 1, 0, CONVERT(time, '5:33'))
,('Р292ХО66', 0, 1, CONVERT(time, '5:34'))
,('У209СМ777', 1, 0, CONVERT(time, '5:38'))
,('М225УУ777', 1, 1, CONVERT(time, '5:40'))
,('Х329СВ66', 0, 2, CONVERT(time, '5:49'))
,('К958МО66', 1, 1, CONVERT(time, '5:55'))
,('Н649ХТ777', 1, 3, CONVERT(time, '5:57'))
,('С416ТА102', 0, 3, CONVERT(time, '6:4'))
,('А436ХО777', 0, 2, CONVERT(time, '6:5'))
,('С444РО77', 0, 1, CONVERT(time, '6:7'))
,('А593ЕХ66', 1, 1, CONVERT(time, '6:14'))
,('К958МО66', 0, 2, CONVERT(time, '6:14'))
,('К583НР02', 0, 2, CONVERT(time, '6:17'))
,('А785ТН02', 0, 2, CONVERT(time, '6:20'))
,('Х012РЕ102', 0, 2, CONVERT(time, '6:24'))
,('А631КН777', 1, 0, CONVERT(time, '6:27'))
,('С416ТА102', 1, 3, CONVERT(time, '6:36'))
,('А587КЕ77', 1, 1, CONVERT(time, '6:42'))
,('Н322ХЕ77', 1, 1, CONVERT(time, '6:49'))
,('О906МВ777', 0, 2, CONVERT(time, '6:49'))
,('Р983НМ777', 0, 2, CONVERT(time, '6:55'))
,('Н255КК102', 0, 2, CONVERT(time, '6:55'))
,('Р983НМ777', 0, 0, CONVERT(time, '7:3'))
,('С842ВМ66', 1, 2, CONVERT(time, '7:10'))
,('О135ТО177', 1, 1, CONVERT(time, '7:11'))
,('С253УО66', 0, 1, CONVERT(time, '7:17'))
,('О601ВУ02', 1, 0, CONVERT(time, '7:23'))
,('Е700МВ77', 1, 1, CONVERT(time, '7:27'))
,('С253УО66', 0, 1, CONVERT(time, '7:36'))
,('Р544ОС777', 1, 1, CONVERT(time, '7:45'))
,('С886НЕ77', 1, 0, CONVERT(time, '7:49'))
,('В771КВ777', 0, 2, CONVERT(time, '7:55'))
,('К485ОС177', 0, 1, CONVERT(time, '7:55'))
,('К583НР02', 1, 3, CONVERT(time, '7:59'))
,('В416МХ177', 0, 0, CONVERT(time, '8:6'))
,('С416ТА102', 0, 0, CONVERT(time, '8:8'))
,('У181АН66', 0, 0, CONVERT(time, '8:10'))
,('Х703РН77', 0, 0, CONVERT(time, '8:12'))
,('А194ОС66', 1, 1, CONVERT(time, '8:14'))
,('К800МУ177', 0, 2, CONVERT(time, '8:15'))
,('К485ОС177', 1, 0, CONVERT(time, '8:16'))
,('Е253ХХ66', 0, 1, CONVERT(time, '8:16'))
,('Х329СВ66', 0, 2, CONVERT(time, '8:21'))
,('М786СС177', 1, 3, CONVERT(time, '8:28'))
,('К186ХМ66', 0, 0, CONVERT(time, '8:37'))
,('К485ОС177', 0, 2, CONVERT(time, '8:44'))
,('М437МУ777', 1, 3, CONVERT(time, '8:46'))
,('К186ХМ66', 0, 1, CONVERT(time, '8:50'))
,('Х621ЕО66', 0, 0, CONVERT(time, '8:55'))
,('Р931КВ177', 0, 2, CONVERT(time, '8:56'))
,('К958МО66', 0, 2, CONVERT(time, '8:59'))
,('У097МЕ102', 1, 1, CONVERT(time, '9:6'))
,('К800МУ177', 1, 1, CONVERT(time, '9:9'))
,('Е076ЕВ177', 0, 2, CONVERT(time, '9:12'))
,('Х424АХ66', 0, 2, CONVERT(time, '9:20'))
,('В561ТН177', 1, 2, CONVERT(time, '9:20'))
,('А631КН777', 0, 0, CONVERT(time, '9:22'))
,('Х621ЕО66', 1, 1, CONVERT(time, '9:29'))
,('О601ВУ02', 0, 2, CONVERT(time, '9:36'))
,('Х329СВ66', 0, 0, CONVERT(time, '9:40'))
,('К223УС777', 1, 2, CONVERT(time, '9:46'))
,('Т034КВ02', 0, 3, CONVERT(time, '9:47'))
,('К787ОХ102', 1, 0, CONVERT(time, '9:47'))
,('Н649ХТ777', 1, 0, CONVERT(time, '9:48'))
,('У097МЕ102', 1, 3, CONVERT(time, '9:49'))
,('Н544КТ77', 1, 3, CONVERT(time, '9:56'))
,('Н771КН177', 1, 1, CONVERT(time, '10:5'))
,('Х928УУ177', 1, 1, CONVERT(time, '10:13'))
,('М722СР66', 0, 2, CONVERT(time, '10:14'))
,('К800МУ177', 0, 1, CONVERT(time, '10:22'))
,('В876ТЕ77', 1, 3, CONVERT(time, '10:31'))
,('А371КМ02', 1, 3, CONVERT(time, '10:34'))
,('С030ХЕ66', 1, 2, CONVERT(time, '10:41'))
,('Е076ЕВ177', 1, 1, CONVERT(time, '10:50'))
,('У671НО777', 0, 0, CONVERT(time, '10:54'))
,('М070РР77', 1, 2, CONVERT(time, '10:59'))
,('С886НЕ77', 1, 2, CONVERT(time, '11:4'))
,('К583НР02', 0, 3, CONVERT(time, '11:6'))
,('В416МХ177', 1, 0, CONVERT(time, '11:7'))
,('Н028АЕ777', 0, 1, CONVERT(time, '11:7'))
,('К787ОХ102', 1, 0, CONVERT(time, '11:16'))
,('Н482НР177', 0, 3, CONVERT(time, '11:25'))
,('М913ХН177', 1, 2, CONVERT(time, '11:33'))
,('А936ТО66', 1, 3, CONVERT(time, '11:36'))
,('А936ТО66', 1, 3, CONVERT(time, '11:41'))
,('Х891МУ102', 1, 2, CONVERT(time, '11:43'))
,('В416МХ177', 0, 0, CONVERT(time, '11:50'))
,('Н482НР177', 0, 3, CONVERT(time, '11:53'))
,('К485ОС177', 1, 0, CONVERT(time, '11:58'))
,('Н482НР177', 0, 1, CONVERT(time, '12:2'))
,('Н649ХТ777', 0, 1, CONVERT(time, '12:7'))
,('Х891МУ102', 1, 1, CONVERT(time, '12:11'))
,('М642АА777', 0, 3, CONVERT(time, '12:12'))
,('С253УО66', 0, 0, CONVERT(time, '12:13'))
,('Р292ХО66', 0, 0, CONVERT(time, '12:18'))
,('А416ТТ102', 0, 2, CONVERT(time, '12:22'))
,('Р983НМ777', 0, 1, CONVERT(time, '12:29'))
,('У730СВ177', 0, 1, CONVERT(time, '12:32'))
,('О683РЕ66', 0, 0, CONVERT(time, '12:35'))
,('О721СВ02', 0, 2, CONVERT(time, '12:38'))
,('В416МХ177', 0, 0, CONVERT(time, '12:46'))
,('Н227СН02', 1, 0, CONVERT(time, '12:52'))
,('Н592ХМ777', 1, 3, CONVERT(time, '12:58'))
,('М225УУ777', 0, 0, CONVERT(time, '12:59'))
,('М225УУ777', 0, 2, CONVERT(time, '13:8'))
,('Х928УУ177', 1, 3, CONVERT(time, '13:9'))
,('О601ВУ02', 0, 3, CONVERT(time, '13:18'))
,('Х928УУ177', 0, 3, CONVERT(time, '13:23'))
,('С253УО66', 1, 1, CONVERT(time, '13:32'))
,('У097МЕ102', 1, 1, CONVERT(time, '13:38'))
,('Н322ХЕ77', 1, 2, CONVERT(time, '13:44'))
,('Н028АЕ777', 1, 1, CONVERT(time, '13:46'))
,('Т391ВН02', 0, 3, CONVERT(time, '13:46'))
,('В561ТН177', 1, 0, CONVERT(time, '13:53'))
,('Х239АУ77', 1, 3, CONVERT(time, '14:2'))
,('А194ОС66', 1, 3, CONVERT(time, '14:3'))
,('В789АО77', 0, 0, CONVERT(time, '14:10'))
,('А631КН777', 1, 1, CONVERT(time, '14:17'))
,('Р931КВ177', 0, 2, CONVERT(time, '14:20'))
,('Н592ХМ777', 1, 3, CONVERT(time, '14:23'))
,('Х424АХ66', 0, 2, CONVERT(time, '14:23'))
,('А785ТН02', 1, 3, CONVERT(time, '14:28'))
,('Р544ОС777', 1, 3, CONVERT(time, '14:31'))
,('М411СМ66', 1, 0, CONVERT(time, '14:34'))
,('Т391ВН02', 1, 0, CONVERT(time, '14:36'))
,('С253УО66', 1, 3, CONVERT(time, '14:40'))
,('О407УХ777', 1, 1, CONVERT(time, '14:42'))
,('К787ОХ102', 0, 0, CONVERT(time, '14:47'))
,('К361ВН102', 0, 3, CONVERT(time, '14:51'))
,('Е700МВ77', 0, 0, CONVERT(time, '14:51'))
,('Р983НМ777', 1, 1, CONVERT(time, '15:0'))
,('Х012РЕ102', 1, 3, CONVERT(time, '15:3'))
,('В725ХТ777', 0, 1, CONVERT(time, '15:11'))
,('О792НМ02', 1, 3, CONVERT(time, '15:13'))
,('Н677СО102', 1, 1, CONVERT(time, '15:21'))
,('О528ТХ66', 0, 0, CONVERT(time, '15:27'))
,('Е253ХХ66', 0, 1, CONVERT(time, '15:30'))
,('Н649ХТ777', 1, 0, CONVERT(time, '15:31'))
,('Т391ВН02', 1, 1, CONVERT(time, '15:40'))
,('Х424АХ66', 0, 1, CONVERT(time, '15:49'))
,('А194ОС66', 0, 2, CONVERT(time, '15:57'))
,('О528ТХ66', 0, 3, CONVERT(time, '16:2'))
,('У130ЕА777', 1, 2, CONVERT(time, '16:5'))
,('Н677СО102', 1, 2, CONVERT(time, '16:12'))
,('Н592СЕ777', 0, 2, CONVERT(time, '16:16'))
,('А436ХО777', 0, 3, CONVERT(time, '16:19'))
,('У544ВН777', 1, 3, CONVERT(time, '16:25'))
,('М722СР66', 0, 2, CONVERT(time, '16:26'))
,('О288УВ102', 0, 2, CONVERT(time, '16:27'))
,('М437МУ777', 0, 1, CONVERT(time, '16:35'))
,('К787ОХ102', 0, 0, CONVERT(time, '16:43'))
,('У544ВН777', 0, 1, CONVERT(time, '16:50'))
,('М233ХЕ66', 1, 2, CONVERT(time, '16:56'))
,('О135ТО177', 1, 0, CONVERT(time, '17:0'))
,('О906МВ777', 0, 2, CONVERT(time, '17:1'))
,('С842ВМ66', 1, 3, CONVERT(time, '17:1'))
,('К361ВН102', 1, 2, CONVERT(time, '17:7'))
,('К186ХМ66', 0, 0, CONVERT(time, '17:7'))
,('С842ВМ66', 0, 0, CONVERT(time, '17:14'))
,('Н255КК102', 0, 2, CONVERT(time, '17:23'))
,('Т391ВН02', 1, 2, CONVERT(time, '17:28'))
,('А785ТН02', 1, 3, CONVERT(time, '17:37'))
,('С416ТА102', 0, 1, CONVERT(time, '17:43'))
,('Т732ЕС102', 0, 0, CONVERT(time, '17:47'))
,('У130ЕА777', 1, 3, CONVERT(time, '17:51'))
,('М786СС177', 0, 2, CONVERT(time, '17:54'))
,('А371КМ02', 1, 2, CONVERT(time, '18:2'))
,('Т194АК777', 0, 2, CONVERT(time, '18:8'))
,('К583НР02', 1, 2, CONVERT(time, '18:9'))
,('М117ВР777', 1, 1, CONVERT(time, '18:17'))
,('М411СМ66', 0, 2, CONVERT(time, '18:18'))
,('М913ХН177', 0, 2, CONVERT(time, '18:22'))
,('У097МЕ102', 1, 3, CONVERT(time, '18:31'))
,('Н771КН177', 1, 0, CONVERT(time, '18:34'))
,('А593ЕХ66', 0, 2, CONVERT(time, '18:42'))
,('М786СС177', 1, 2, CONVERT(time, '18:43'))
,('С842ВМ66', 0, 2, CONVERT(time, '18:47'))
,('Т034КВ02', 0, 1, CONVERT(time, '18:47'))
,('М722СР66', 1, 3, CONVERT(time, '18:47'))
,('М642АА777', 0, 1, CONVERT(time, '18:48'))
,('Р983НМ777', 1, 0, CONVERT(time, '18:48'))
,('Х703РН77', 1, 0, CONVERT(time, '18:54'))
,('М437МУ777', 0, 0, CONVERT(time, '18:55'))
,('А371КМ02', 0, 3, CONVERT(time, '19:1'))
,('А936ТО66', 1, 3, CONVERT(time, '19:10'))
,('А936ТО66', 0, 3, CONVERT(time, '19:15'))
,('Н028АЕ777', 0, 0, CONVERT(time, '19:22'))
,('Х239АУ77', 1, 3, CONVERT(time, '19:25'))
,('М233ХЕ66', 1, 3, CONVERT(time, '19:26'))
,('В725ХТ777', 1, 1, CONVERT(time, '19:35'))
,('А936ТО66', 0, 3, CONVERT(time, '19:39'))
,('К958МО66', 0, 3, CONVERT(time, '19:39'))
,('А785ТН02', 0, 0, CONVERT(time, '19:42'))
,('Х703РН77', 0, 2, CONVERT(time, '19:51'))
,('У770СС66', 1, 1, CONVERT(time, '19:58'))
,('М642АА777', 1, 0, CONVERT(time, '20:2'))
,('В771КВ777', 0, 2, CONVERT(time, '20:10'))
,('Р544ОС777', 0, 1, CONVERT(time, '20:15'))
,('О407УХ777', 0, 2, CONVERT(time, '20:23'))
,('О906МВ777', 1, 0, CONVERT(time, '20:23'))
,('О528ТХ66', 0, 2, CONVERT(time, '20:23'))
,('Р931КВ177', 1, 3, CONVERT(time, '20:26'))
,('К485ОС177', 1, 1, CONVERT(time, '20:28'))
,('Р931КВ177', 0, 3, CONVERT(time, '20:31'))
,('М411СМ66', 0, 3, CONVERT(time, '20:37'))
,('О456ТО66', 1, 2, CONVERT(time, '20:37'))
,('У209СМ777', 0, 3, CONVERT(time, '20:41'))
,('О906МВ777', 1, 3, CONVERT(time, '20:49'))
,('У544ВН777', 1, 3, CONVERT(time, '20:49'))
,('Р931КВ177', 0, 1, CONVERT(time, '20:50'))
,('Т034КВ02', 1, 2, CONVERT(time, '20:58'))
,('Е950ЕР777', 0, 0, CONVERT(time, '21:2'))
,('Р931КВ177', 1, 2, CONVERT(time, '21:4'))
,('О792НМ02', 1, 3, CONVERT(time, '21:10'))
,('О683РЕ66', 1, 3, CONVERT(time, '21:19'))
,('М642АА777', 1, 3, CONVERT(time, '21:23'))
,('В771КВ777', 0, 2, CONVERT(time, '21:25'))
,('О683РЕ66', 0, 0, CONVERT(time, '21:30'))
,('М411СМ66', 1, 2, CONVERT(time, '21:36'))
,('Т034КВ02', 1, 3, CONVERT(time, '21:42'))
,('Х891МУ102', 1, 0, CONVERT(time, '21:49'))
,('Р544ОС777', 0, 0, CONVERT(time, '21:52'))
,('Е665АТ177', 1, 1, CONVERT(time, '21:53'))
,('Х928УУ177', 1, 1, CONVERT(time, '22:2'))
,('М305ТА102', 1, 2, CONVERT(time, '22:4'))
,('К787ОХ102', 0, 0, CONVERT(time, '22:7'))
,('М305ТА102', 1, 2, CONVERT(time, '22:13'))
,('О456ТО66', 1, 3, CONVERT(time, '22:19'))
,('М722СР66', 1, 0, CONVERT(time, '22:25'))
,('К485ОС177', 1, 1, CONVERT(time, '22:29'))
,('Н044ВС66', 0, 3, CONVERT(time, '22:31'))
,('Н592СЕ777', 0, 1, CONVERT(time, '22:35'))
,('Х329СВ66', 0, 3, CONVERT(time, '22:38'))
,('А785ТН02', 0, 3, CONVERT(time, '22:40'))
,('Н592ХМ777', 1, 2, CONVERT(time, '22:47'))
,('С253УО66', 0, 3, CONVERT(time, '22:51'))
,('Х891МУ102', 0, 1, CONVERT(time, '22:51'))
,('Е253ХХ66', 0, 2, CONVERT(time, '22:53'))
,('Х239АУ77', 0, 3, CONVERT(time, '22:57'))
,('М070РР77', 0, 0, CONVERT(time, '23:6'))
,('Е937ХР777', 1, 0, CONVERT(time, '23:6'))
,('А785ТН02', 1, 0, CONVERT(time, '23:12'))
,('А936ТО66', 0, 1, CONVERT(time, '23:20'))
,('О407УХ777', 0, 0, CONVERT(time, '23:20'))
,('М225УУ777', 1, 1, CONVERT(time, '23:25'))
,('Н677СО102', 0, 0, CONVERT(time, '23:32'))
,('М411СМ66', 0, 0, CONVERT(time, '23:36'))
,('Р544ОС777', 0, 1, CONVERT(time, '23:36'))
,('К223УС777', 0, 2, CONVERT(time, '23:41'))
,('В771КВ777', 0, 0, CONVERT(time, '23:47'))
,('Н028АЕ777', 1, 1, CONVERT(time, '23:48'))
,('У209СМ777', 0, 1, CONVERT(time, '23:51'))

GO

-- Иногородние автомобили
CREATE VIEW NotResidentCars AS
SELECT stateNumber as 'Гос. номер',
        r.regionName as 'Регион',
        shvedov.getPostName(shvedov.getInsidePostId(stateNumber)) as 'Въезд через пост',
        shvedov.getPostName(shvedov.getOutsidePostId(stateNumber)) as 'Выезд через пост'
FROM KN_303_shvedov.shvedov.StateNumberInformation as sni,
     KN_303_shvedov.shvedov.Regions as r
WHERE  sni.regionNumber = r.regionNumber AND shvedov.isNonresident(id) = 1
GO

-- Транзитные автомобили
CREATE VIEW TransitCars AS
SELECT stateNumber as 'Гос. номер',
        r.regionName as 'Регион',
        shvedov.getPostName(shvedov.getInsidePostId(stateNumber)) as 'Въезд через пост',
        shvedov.getPostName(shvedov.getOutsidePostId(stateNumber)) as 'Выезд через пост'
FROM KN_303_shvedov.shvedov.StateNumberInformation as sni,
     KN_303_shvedov.shvedov.Regions as r
WHERE  sni.regionNumber = r.regionNumber AND shvedov.isTransit(id) = 1
GO

--Местные атомобили
CREATE VIEW LocalCars AS
SELECT stateNumber as 'Гос. номер',
        r.regionName as 'Регион'
FROM KN_303_shvedov.shvedov.StateNumberInformation as sni,
     KN_303_shvedov.shvedov.Regions as r
WHERE  r.regionNumber = '66'
GO




--Всего автомобилей
CREATE VIEW AllCars AS
SELECT count(*) AS 'Всего автомобилей'
from KN_303_shvedov.shvedov.StateNumberInformation
GO

--Количество прочих автомобилей
CREATE VIEW AnotherCars AS
    SELECT COUNT(*) AS 'Количество других автомобилей'
    from KN_303_shvedov.shvedov.StateNumberInformation as sni,
        KN_303_shvedov.shvedov.Regions as r
    WHERE  sni.regionNumber = r.regionNumber AND shvedov.isResident(sni.id) != 1 AND shvedov.isTransit(sni.id) != 1 AND shvedov.isNonresident(sni.id) != 1
GO

--Все автомобили
CREATE VIEW AllCarsData AS
    SELECT sni.stateNumber AS 'Гос номер',
        r.regionName AS 'Регион',
        shvedov.getInsideData(sni.stateNumber) AS 'Время въезда',
        shvedov.getOutsideData(sni.stateNumber) AS 'Время выезда'
    from KN_303_shvedov.shvedov.StateNumberInformation as sni,
        KN_303_shvedov.shvedov.Regions as r,
        KN_303_shvedov.shvedov.TravelInformation ti
    WHERE  sni.regionNumber = r.regionNumber AND ti.stateNumberId = sni.id
GO

select * from AllCarsData
ORDER BY 'Регион';