2Z4s4r4j

1USE [KN_301_Kazikulov]
2GO
3
4CREATE SCHEMA Kazikulov
5GO
6
7DROP TABLE  [KN_301_Kazikulov].Kazikulov.Records
8CREATE TABLE [KN_301_Kazikulov].Kazikulov.Records
9(
10    PostId TinyInt NOT NULL CHECK(PostId>0 AND PostId<4),
11	Direction SmallInt NOT NULL CHECK(Direction = -1 OR Direction = 1),
12    Number_Letters NVARCHAR(3) NOT NULL CHECK(UPPER(Number_Letters) = Number_Letters  Collate Latin1_General_CS_AI 
13	OR LOWER(Number_Letters) = Number_Letters  Collate Latin1_General_CS_AI),
14	Number_Numbers NVARCHAR(3) NOT NULL CHECK(CAST(Number_Numbers as INT) > 0),
15	Number_Region NVARCHAR(3) NOT NULL CHECK(CAST(Number_Region as INT) > 0),
16    PassageTime TIME NOT NULL,
17	CONSTRAINT PK_Records PRIMARY KEY (PostId, Number_Letters, Number_Numbers, Number_Region, PassageTime) 
18)
19GO
20
21DROP TRIGGER Kazikulov.Records_Insert;
22go
23CREATE TRIGGER Records_Insert
24ON [KN_301_Kazikulov].[Kazikulov].[Records]
25instead of INSERT
26AS 
27BEGIN
28if exists(
29select ii.PostId, ii.direction, ii.Number_Letters, ii.Number_Numbers, ii.Number_Region, ii.PassageTime from inserted ii
30INNER JOIN Records ON ii.Number_Numbers = Records.Number_Numbers AND 
31ii.Number_Letters = Records.Number_Letters AND ii.Number_Region = Records.Number_Region)
32	BEGIN
33		insert INTO [KN_301_Kazikulov].[Kazikulov].[Records]
34		select ii.PostId, ii.direction, ii.Number_Letters, ii.Number_Numbers, ii.Number_Region, ii.PassageTime from inserted ii
35		INNER JOIN Records ON ii.Number_Numbers = Records.Number_Numbers AND ii.Number_Letters = Records.Number_Letters 
36		AND ii.Number_Region = Records.Number_Region
37		WHERE len(ii.Number_Region) = 3 AND SUBSTRING(ii.Number_Region,1,1) IN ('1','2','7') OR 
38			LEN(ii.Number_Region) = 2 
39		GROUP BY ii.PostId, ii.direction, ii.Number_Letters, ii.Number_Numbers, 
40		ii.Number_Region, ii.PassageTime
41		HAVING (SUM(Records.Direction)+ii.Direction) in (-1,0,1);
42
43		Declare @direction Smallint
44		Declare @lastPost tinyint
45		Declare @currentPost tinyint
46		Declare @region NVARCHAR(3)
47		Declare @type TINYint
48		Declare @lastDirection SmallInt
49		Declare @currentDirection SmallInt
50
51		Select @currentPost = (Select ii.PostId from inserted ii)
52		Select @region = (Select ii.Number_Region from inserted ii)
53		Select @direction = (select SUM(records.Direction) from inserted ii
54		INNER JOIN Records ON ii.Number_Letters = Records.Number_Letters AND II.Number_Numbers = records.Number_Numbers 
55		AND II.Number_Region = Records.Number_Region)
56		Select @currentDirection = (Select ii.Direction from inserted ii)
57
58
59		Select @lastPost = (select records.PostId from records 
60		INNER JOIN inserted ii ON ii.Number_Letters = Records.Number_Letters AND II.Number_Numbers = records.Number_Numbers 
61		AND II.Number_Region = Records.Number_Region AND ii.Number_Region = Records.Number_Region
62		order by records.PassageTime desc
63		OFFSET 1 ROWS
64		FETCH NEXT 1 ROW ONLY) 
65
66		Select @lastDirection = (select records.Direction from records 
67		INNER JOIN inserted ii ON ii.Number_Letters = Records.Number_Letters AND II.Number_Numbers = records.Number_Numbers 
68		AND II.Number_Region = Records.Number_Region AND ii.Number_Region = Records.Number_Region
69		order by records.PassageTime desc
70		OFFSET 1 ROWS
71		FETCH NEXT 1 ROW ONLY) 
72
73		IF EXISTS(select ii.Number_Letters, ii.Number_Numbers, ii.Number_Region from inserted ii INNER JOIN Cars ON 
74		ii.Number_Letters = Cars.Number_Letters AND II.Number_Numbers = CARS.Number_Numbers AND II.Number_Region = CARS.Number_Region)
75		BEGIN
76			if(@lastPost != @currentPost AND @direction = 0 AND @region NOT like '06' AND @lastDirection = 1 AND @currentDirection = -1)
77			BEGIN
78				UPDATE Cars
79				SET Car_Type = 1
80				FROM inserted ii inner join Cars on ii.Number_Region = Cars.Number_Region AND ii.Number_Letters = Cars.Number_Letters
81				AND ii.Number_Numbers = Cars.Number_Numbers
82			END;
83			if(@lastPost = @currentPost AND @direction = 0 AND @lastDirection = 1 AND @currentDirection = -1)
84			UPDATE Cars
85				SET Car_Type = 2
86				FROM inserted ii inner join Cars on ii.Number_Region = Cars.Number_Region AND ii.Number_Letters = Cars.Number_Letters
87				AND ii.Number_Numbers = Cars.Number_Numbers
88			if(@direction = 0 AND @region like '06' AND @lastDirection = -1 AND @currentDirection = 1)
89			BEGIN
90				UPDATE Cars
91				SET Car_Type = 3
92				FROM inserted ii inner join Cars on ii.Number_Region = Cars.Number_Region AND ii.Number_Letters = Cars.Number_Letters
93				AND ii.Number_Numbers = Cars.Number_Numbers
94			END;
95			if(@direction = -1 OR @direction = 1)
96			BEGIN
97				UPDATE Cars
98				SET Car_Type = NULL
99				FROM inserted ii inner join Cars on ii.Number_Region = Cars.Number_Region AND ii.Number_Letters = Cars.Number_Letters
100				AND ii.Number_Numbers = Cars.Number_Numbers
101			END;
102		END;
103	END;
104else
105	BEGIN
106	insert INTO [KN_301_Kazikulov].[Kazikulov].[Records]
107	select ii.PostId, ii.direction, ii.Number_Letters, ii.Number_Numbers, ii.Number_Region, ii.PassageTime from inserted ii
108	WHERE len(ii.Number_Region) = 3 AND SUBSTRING(ii.Number_Region,1,1) IN ('1','2','7') OR 
109		LEN(ii.Number_Region) = 2 
110	GROUP BY ii.PostId, ii.direction, ii.Number_Letters, ii.Number_Numbers, 
111	ii.Number_Region, ii.PassageTime
112
113	IF(NOT EXISTS(select ii.Number_Letters, ii.Number_Numbers, ii.Number_Region from inserted ii INNER JOIN Cars ON 
114	ii.Number_Letters = Cars.Number_Letters AND II.Number_Numbers = CARS.Number_Numbers AND II.Number_Region = CARS.Number_Region))
115	BEGIN
116		insert INTO [KN_301_Kazikulov].[Kazikulov].Cars
117		select NULL, regions.RegionName, ii.Number_Letters, ii.Number_Numbers, ii.Number_Region from inserted ii
118		INNER JOIN REgions on ii.Number_Region = regions.RegionId
119	END;
120
121	END;
122END;
123GO
124
125DROP TABLE  [KN_301_Kazikulov].Kazikulov.Posts;
126CREATE TABLE [KN_301_Kazikulov].Kazikulov.Posts
127(
128    PostId TinyInt NOT NULL CHECK(PostId>0 AND PostId<5),
129	Region nvarchar(3) NOT NULL CHECK(CAST(Region as INT) > 0) DEFAULT '06'
130	CONSTRAINT PK_Posts PRIMARY KEY (PostId) 
131)
132GO
133
134DROP TABLE  [KN_301_Kazikulov].Kazikulov.Regions;
135CREATE TABLE [KN_301_Kazikulov].Kazikulov.Regions
136(
137    RegionId NVARCHAR(3) NOT NULL CHECK(CAST(RegionId as INT) > 0),
138	RegionName nvarchar(50) NOT NULL,
139	CONSTRAINT PK_Regions PRIMARY KEY (RegionId) 
140)
141GO	
142
143DROP TABLE  [KN_301_Kazikulov].Kazikulov.Cars;
144CREATE TABLE [KN_301_Kazikulov].Kazikulov.Cars
145(
146    Car_Type TinyInt NULL CHECK((Car_Type>0 AND Car_Type<4) OR Car_Type is NULL),
147	RegionName nvarchar(200) NOT NULL,
148    Number_Letters NVARCHAR(3) NOT NULL CHECK(UPPER(Number_Letters) = Number_Letters  Collate Latin1_General_CS_AI 
149	OR LOWER(Number_Letters) = Number_Letters  Collate Latin1_General_CS_AI),
150	Number_Numbers NVARCHAR(3) NOT NULL CHECK(CAST(Number_Numbers as INT) > 0),
151	Number_Region NVARCHAR(3) NOT NULL CHECK(CAST(Number_Region as INT) > 0),
152	CONSTRAINT PK_Cars PRIMARY KEY (Number_Letters, Number_Numbers, Number_Region) 
153)
154GO
155
156DROP TABLE  [KN_301_Kazikulov].Kazikulov.Types;
157CREATE TABLE [KN_301_Kazikulov].Kazikulov.Types
158(
159    TypeId TinyInt NOT NULL CHECK((TypeId>0 AND TypeId<4) OR TypeId is NULL),
160	TypeName nvarchar(200) NOT NULL,
161	CONSTRAINT PK_Type PRIMARY KEY (TypeId)
162	-- 1 - transit
163	-- 2 - inogorod
164	-- 3 - mesntiye 
165)
166GO
167	
168CREATE SYNONYM Records
169FOR [KN_301_Kazikulov].[Kazikulov].[Records]
170GO 
171
172CREATE SYNONYM Posts
173FOR [KN_301_Kazikulov].[Kazikulov].[Posts]
174GO 
175
176CREATE SYNONYM Regions
177FOR [KN_301_Kazikulov].[Kazikulov].[Regions]
178GO 
179
180CREATE SYNONYM Cars
181FOR [KN_301_Kazikulov].[Kazikulov].Cars
182GO 
183
184CREATE SYNONYM Types
185FOR [KN_301_Kazikulov].[Kazikulov].Types
186GO 
187
188/* DELETE FROM [KN_301_Kazikulov].[Kazikulov].Regions; */
189INSERT INTO Regions
190(RegionId, RegionName)
191 VALUES
192 ('02', 'Республика Башкортостан'),
193 ('102', 'Республика Башкортостан'),
194 ('702', 'Республика Башкортостан'),
195 ('06', 'Республика Ингушетия'),
196 ('16', 'Республика Татарстан	'),
197 ('116', 'Республика Татарстан	'),
198 ('716', 'Республика Татарстан	'),
199 ('66','Свердловская область'),
200 ('96','Свердловская область'),
201 ('196','Свердловская область'),
202 ('20','Чеченская республика'),
203 ('95','Чеченская республика'),
204 ('05', 'Республика Дагестан')
205GO 
206SELECT * FROM REGIONS
207
208/* DELETE FROM [KN_301_Kazikulov].[Kazikulov].Posts; */
209INSERT INTO Posts
210(PostId)
211 VALUES
212 ('1'),
213 ('2'),
214 ('3'),
215 ('4')
216GO 
217SELECT * FROM POSTS
218
219INSERT INTO [Types] 
220(typeId, typeName)
221 VALUES
222 (1, 'Транзитный'),
223 (2, 'Иногородний'),
224 (3, 'Местный')
225GO 
226SELECT * FROM Types
227
228ALTER TABLE [KN_301_Kazikulov].Kazikulov.Records ADD 
229	CONSTRAINT FK_Records_ToPosts FOREIGN KEY (PostId) 
230	REFERENCES [KN_301_Kazikulov].Kazikulov.Posts(PostId)
231GO	
232
233ALTER TABLE [KN_301_Kazikulov].Kazikulov.Cars ADD 
234	CONSTRAINT FK_Cars_ToTypes FOREIGN KEY (Car_Type) 
235	REFERENCES [KN_301_Kazikulov].Kazikulov.Types(TypeId)
236GO	
237
238ALTER TABLE [KN_301_Kazikulov].Kazikulov.Posts ADD 
239	CONSTRAINT FK_Posts_ToRegion FOREIGN KEY (Region) 
240	REFERENCES [KN_301_Kazikulov].Kazikulov.Regions(RegionId)
241GO	
242
243ALTER TABLE [KN_301_Kazikulov].Kazikulov.Records ADD 
244	CONSTRAINT FK_Records_ToRegion FOREIGN KEY (Number_Region) 
245	REFERENCES [KN_301_Kazikulov].Kazikulov.Regions(RegionId)
246GO	
247
248DELETE FROM [KN_301_Kazikulov].[Kazikulov].[Records];
249DELETE FROM [KN_301_Kazikulov].[Kazikulov].[cars];
250
251SELECT * from cars
252SELECT * from records
253
254INSERT INTO Records
255(PostId, Direction, Number_Letters, Number_Numbers, Number_Region, PassageTime)
256 VALUES
257 (1, 1, 'ABC', '007', '05', '18:39:00') -- транзитная
258GO 
259
260INSERT INTO Records
261(PostId, Direction, Number_Letters, Number_Numbers, Number_Region, PassageTime)
262 VALUES
263  (2, -1, 'ABC', '007', '05', '18:40:00')
264go
265
266INSERT INTO Records
267(PostId, Direction, Number_Letters, Number_Numbers, Number_Region, PassageTime)
268 VALUES
269 (2, 1, 'ABC', '008', '116', '18:40:00') -- иногородняя
270GO 
271
272INSERT INTO Records
273(PostId, Direction, Number_Letters, Number_Numbers, Number_Region, PassageTime)
274 VALUES
275 (2, -1, 'ABC', '008', '116', '18:41:00')
276 GO
277
278 INSERT INTO Records
279(PostId, Direction, Number_Letters, Number_Numbers, Number_Region, PassageTime)
280 VALUES
281 (2, 1, 'ABC', '008', '05', '18:42:00') -- пересчет иногородней на транзитную
282 GO
283
284 INSERT INTO Records
285(PostId, Direction, Number_Letters, Number_Numbers, Number_Region, PassageTime)
286 VALUES
287 (1, -1, 'ABC', '008', '05', '18:50:00')
288 GO
289
290 INSERT INTO Records
291(PostId, Direction, Number_Letters, Number_Numbers, Number_Region, PassageTime)
292 VALUES
293 (2, -1, 'ABC', '008', '06', '18:40:00') -- местная
294GO 
295
296INSERT INTO Records
297(PostId, Direction, Number_Letters, Number_Numbers, Number_Region, PassageTime)
298 VALUES
299 (1, 1, 'ABC', '008', '06', '18:42:00')
300 GO
301
302SELECT * from records
303SELECT * from cars
304go
305
306DROP VIEW CarsView;
307GO
308CREATE VIEW CarsView AS 
309SELECT SUBSTRING(cc.Number_Letters, 1,1)+cc.Number_Numbers+SUBSTRING(cc.Number_Letters,2,2)+cc.Number_Region as 'Номер', 
310cc.RegionName as 'Регион', IIF(cc.Car_Type=1, 'Транзитная', IIF(cc.Car_Type=2, 'Иногородняя', 'Местная')) as 'Тип машины'
311from Cars cc
312go
313SELECT * From CarsView;
314
315-- TESTS
316DELETE FROM [KN_301_Kazikulov].[Kazikulov].[Records];
317GO
318/* Тест региона */
319INSERT INTO Records
320(PostId, Direction, Number_Letters, Number_Numbers, Number_Region, PassageTime)
321 VALUES
322 (1, -1, 'ABC', '007', '005', '18:39:00')
323GO 
324SELECT * FROM RECORDS
325
326
327/* Тест въезда/выезда */
328INSERT INTO Records
329(PostId, Direction, Number_Letters, Number_Numbers, Number_Region, PassageTime)
330 VALUES
331 (1, -1, 'ABC', '133', '05','18:36:00')
332GO 
333INSERT INTO Records
334(PostId, Direction, Number_Letters, Number_Numbers, Number_Region, PassageTime)
335 VALUES
336 (1, -1, 'ABC', '133', '05', '18:37:00')
337GO 
338SELECT * from records
339
340/* PostId test */
341INSERT INTO Records
342(PostId, Direction, Number_Letters, Number_Numbers, Number_Region, PassageTime)
343 VALUES
344 (0, -1, 'ABC', '133', '05', '18:37:00')
345GO 
346SELECT * from records
347/* Direction test */
348INSERT INTO Records
349(PostId, Direction, Number_Letters, Number_Numbers, Number_Region, PassageTime)
350 VALUES
351 (1, 2, 'ABC', '133', '05', '18:37:00')
352GO 
353DELETE FROM Records;
354/*Letters test*/
355INSERT INTO Records
356(PostId, Direction, Number_Letters, Number_Numbers, Number_Region, PassageTime)
357 VALUES
358 (1, 1, 'ABс', '133', '05', '18:37:00')
359GO 
360INSERT INTO Records
361(PostId, Direction, Number_Letters, Number_Numbers, Number_Region, PassageTime)
362 VALUES
363 (1, 1, 'abC', '133', '05', '18:37:00')
364GO 
365
366/*Numbers test*/
367INSERT INTO Records
368(PostId, Direction, Number_Letters, Number_Numbers, Number_Region, PassageTime)
369 VALUES
370 (1, 1, 'ABC', '-13', '05', '18:37:00')
371GO