EGSaQLCU

1USE [Stroeva]
2
3IF OBJECT_ID ('Journal') IS NOT NULL
4	DROP TABLE [Journal]
5GO
6IF OBJECT_ID ('CarInOutJournal') IS NOT NULL
7	DROP TABLE [CarInOutJournal]
8GO
9IF OBJECT_ID ('Direct') IS NOT NULL
10	DROP TABLE [Direct]
11GO
12
13IF OBJECT_ID ('PolicePost') IS NOT NULL
14	DROP TABLE [PolicePost]
15GO
16IF OBJECT_ID ('Car') IS NOT NULL
17	DROP TABLE [Car]
18GO
19IF OBJECT_ID ('Driver') IS NOT NULL
20	DROP TABLE [Driver]
21GO
22IF OBJECT_ID ('CarModel') IS NOT NULL
23	DROP TABLE [CarModel]
24GO
25IF OBJECT_ID ('CarRegNumber') IS NOT NULL
26	DROP TABLE [CarRegNumber]
27GO
28IF OBJECT_ID ('Region') IS NOT NULL
29	DROP TABLE [Region]
30GO
31
32CREATE TABLE Region (
33	RegionId INT PRIMARY KEY NOT NULL 
34	,RegionName VARCHAR(100)
35);
36GO
37
38CREATE TABLE CarRegNumber(
39	CarRegNumberId TINYINT NOT NULL PRIMARY KEY
40	,Letters CHAR(3)
41	,Number CHAR(3)
42	,RegionId INT
43--	,CHECK(
44	--	(substring(Letters, 1, 1) in ('A', 'B', 'E', 'K', 'M', 'H', 'O', 'P', 'C', 'T', 'Y', 'X'))
45	--	and (substring(Letters, 2, 1) in ('A', 'B', 'E', 'K', 'M', 'H', 'O', 'P', 'C', 'T', 'Y', 'X'))
46	--	and (substring(Letters, 3, 1) in ('A', 'B', 'E', 'K', 'M', 'H', 'O', 'P', 'C', 'T', 'Y', 'X'))
47--	)
48	--,CHECK(
49	--(len(convert(varchar(5), RegionId)) = 2) or 
50	--((len(convert(varchar(5), RegionId)) = 3) and (substring((convert(varchar(5), RegionId)),1,1) in ('1', '2', '7')))
51	--)
52	
53);
54GO
55
56CREATE TABLE CarModel(
57	CarModelId INT PRIMARY KEY
58	,Model VARCHAR(30)
59);
60GO
61
62CREATE TABLE Driver (
63	DriverId INT NOT NULL PRIMARY KEY
64	,Name VARCHAR(30) NOT NULL
65);
66GO
67
68CREATE TABLE Car (
69	CarId INT PRIMARY KEY NOT NULL
70	,CarModelId INT
71	,CarRegNumberId TINYINT
72	,FOREIGN KEY (CarModelId) REFERENCES CarModel (CarModelId)
73	,FOREIGN KEY (CarRegNumberId) REFERENCES CarRegNumber (CarRegNumberId)
74);
75GO
76
77CREATE TABLE PolicePost (
78	PolicePostId INT PRIMARY KEY NOT NULL
79	,PostName VARCHAR(30)
80	,RegionId INT
81);
82GO
83
84CREATE TABLE Direct(
85	DirectId TINYINT PRIMARY KEY NOT NULL
86	,DirectName VARCHAR(30)
87);
88GO
89
90CREATE TABLE CarInOutJournal (
91	RecordId INT IDENTITY(0,1) PRIMARY KEY NOT NULL
92	,CarId INT NOT NULL
93	,DirectId TINYINT NOT NULL
94	,DriverId INT NOT NULL
95	,RecordTime DATETIME
96	,FOREIGN KEY (CarId) REFERENCES [dbo].[Car](carId)
97	,FOREIGN KEY (DirectId) REFERENCES [dbo].[Direct](DirectId)
98	,FOREIGN KEY (DriverId) REFERENCES [dbo].[Driver](DriverId)
99);
100GO
101
102
103ALTER TABLE [dbo].[PolicePost]
104ADD CONSTRAINT FK_RegionId FOREIGN KEY (RegionId) REFERENCES Region(RegionId)
105GO
106
107
108CREATE TABLE Journal (
109	RecordId int IDENTITY(0,1) NOT NULL PRIMARY KEY
110	,RecordTime datetime NOT NULL
111	,CarId int
112	,DirectId tinyint
113	,PolicePostId int
114	,CONSTRAINT FK_CarId FOREIGN KEY (CarId) REFERENCES Car(CarId)
115	,CONSTRAINT FK_DirectId FOREIGN KEY (DirectId) REFERENCES Direct(DirectId)
116	,CONSTRAINT FK_PolicePostId FOREIGN KEY (PolicePostId) REFERENCES PolicePost(PolicePostId)
117);
118GO
119
120
121
122CREATE TRIGGER checkCarNumber on [dbo].[CarRegNumber] AFTER INSERT
123AS
124	BEGIN
125		declare @letters CHAR(3), @number CHAR(3), @regionId INT, @regionCode NVARCHAR(10)
126
127		DECLARE insertCursor CURSOR LOCAL
128		FOR (SELECT Letters, Number, RegionId
129				FROM inserted)
130
131		OPEN insertCursor
132
133		FETCH NEXT FROM insertCursor
134		INTO @letters, @number, @regionId
135
136		WHILE @@FETCH_STATUS = 0
137			BEGIN
138				set @regionCode = convert(nvarchar(10), @regionId) 
139
140				if not ((substring(@letters, 1, 1) in ('A', 'B', 'E', 'K', 'M', 'H', 'O', 'P', 'C', 'T', 'Y', 'X'))
141				and (substring(@letters, 2, 1) in ('A', 'B', 'E', 'K', 'M', 'H', 'O', 'P', 'C', 'T', 'Y', 'X'))
142				and (substring(@letters, 3, 1) in ('A', 'B', 'E', 'K', 'M', 'H', 'O', 'P', 'C', 'T', 'Y', 'X'))
143				and ((len(@regionCode) = 2) or ((len(@regionCode) = 3) and (substring(@regionCode,1,1) in ('1', '2', '7'))))
144				)
145					BEGIN
146						PRINT 'Ошибка! Недопустимое значение: ' + CAST(@letters as varchar(30))
147						ROLLBACK TRANSACTION
148					END
149
150				if not exists (select RegionId from [dbo].Region where RegionId = @regionId) 
151				begin
152    				print('Ошибка!') 
153				end
154					FETCH NEXT FROM insertCursor
155					INTO @letters, @number, @regionId
156			END
157	END;
158GO
159
160
161INSERT INTO [dbo].[Direct]
162          ([DirectId]
163          ,[DirectName])
164    VALUES
165          (0, N'В город'),
166		   (1, N'Из города')
167GO
168
169INSERT INTO [dbo].[Driver]
170          ([DriverId]
171          ,[Name])
172    VALUES
173          (0, N'Иванов')
174		   ,(1, N'Петров')
175		   ,(2, N'Сидоров')
176		   ,(3, N'Васечкин')
177		   ,(4, N'Козлов')
178		   ,(5, N'Дураков')
179GO
180
181INSERT INTO [dbo].[Region]
182          ([RegionId]
183          ,[RegionName])
184     VALUES
185			(1,
186			 'Республика Адыгея'),
187			 
188			(2,
189			 'Республика Башкирия'),
190			 
191			(3,
192			 'Республика Бурятия'),
193			 
194			(4,
195			 'Республика Алтай '),
196			 
197			(5,
198			 'Республика Дагестан '),
199			 
200			(6,
201			 'Республика Ингушетия '),
202			 
203			(7,
204			 'Кабардино-Балкарская Республика '),
205			 
206			(8,
207			 'Республика Калмыкия '),
208			 
209			(9,
210			 'Республика Карачаево-Черкессия '),
211			 
212			(10,
213			 'Республика Карелия '),
214			 
215			(11,
216			 'Республика Коми '),
217			 
218			(12,
219			 'Республика Марий Эл '),
220			 
221			(13,
222			 'Республика Мордовия '),
223			 
224			(14,
225			 'Республика Саха (Якутия) '),
226			 
227			(15,
228			 'Республика Северная Осетия-Алания '),
229			 
230			(16,
231			 'Республика Татарстан '),
232			 
233			(17,
234			 'Республика Тыва (Тува) '),
235			 
236			(18,
237			 'Удмуртская Республика '),
238			 
239			(19,
240			 'Республика Хакасия '),
241			 
242			(21,
243			 'Чувашская Республика '),
244			 
245			(22,
246			 'Алтайский край '),
247			 
248			(23,
249			 'Краснодарский край '),
250			 
251			(24,
252			 'Красноярский край '),
253			 
254			(25,
255			 'Приморский край '),
256			 
257			(26,
258			 'Ставропольский край '),
259			 
260			(27,
261			 'Хабаровский край '),
262			 
263			(28,
264			 'Амурская область '),
265			 
266			(29,
267			 'Архангельская область '),
268			 
269			(30,
270			 'Астраханская область '),
271			 
272			(31,
273			 'Белгородская область '),
274			 
275			(32,
276			 'Брянская область '),
277			 
278			(33,
279			 'Владимирская область '),
280			 
281			(34,
282			 'Волгоградская область '),
283			 
284			(35,
285			 'Вологодская область '),
286			 
287			(36,
288			 'Воронежская область '),
289			 
290			(37,
291			 'Ивановская область '),
292			 
293			(38,
294			 'Иркутская область '),
295			 
296			(39,
297			 'Калининградская область '),
298			 
299			(40,
300			 'Калужская область '),
301			 
302			(41,
303			 'Камчатская область '),
304			 
305			(42,
306			 'Кемеровская область '),
307			 
308			(43,
309			 'Кировская область '),
310			 
311			(44,
312			 'Костромская область '),
313			 
314			(45,
315			 'Курганская область '),
316			 
317			(46,
318			 'Курская область '),
319			 
320			(47,
321			 'Ленинградская область '),
322			 
323			(48,
324			 'Липецкая область '),
325			 
326			(49,
327			 'Магаданская область '),
328			 
329			(50,
330			 'Московская область '),
331			 
332			(51,
333			 'Мурманская область '),
334			 
335			(52,
336			 'Нижегородская область '),
337			 
338			(53,
339			 'Новгородская область '),
340			 
341			(54,
342			 'Новосибирская область '),
343			 
344			(55,
345			 'Омская область '),
346			 
347			(56,
348			 'Оренбургская область '),
349			 
350			(57,
351			 'Орловская область '),
352			 
353			(58,
354			 'Пензенская область '),
355			 
356			(59,
357			 'Пермский край '),
358			 
359			(60,
360			 'Псковская область '),
361			 
362			(61,
363			 'Ростовская область '),
364			 
365			(62,
366			 'Рязанская область '),
367			 
368			(63,
369			 'Самарская область '),
370			 
371			(64,
372			 'Саратовская область '),
373			 
374			(65,
375			 'Сахалинская область '),
376			 
377			(66,
378			 'Свердловская область '),
379			 
380			(67,
381			 'Смоленская область '),
382			 
383			(68,
384			 'Тамбовская область '),
385			 
386			(69,
387			 'Тверская область '),
388			 
389			(70,
390			 'Томская область '),
391			 
392			(71,
393			 'Тульская область '),
394			 
395			(72,
396			 'Тюменская область '),
397			 
398			(73,
399			 'Ульяновская область '),
400			 
401			(74,
402			 'Челябинская область '),
403			 
404			(75,
405			 'Читинская область '),
406			 
407			(76,
408			 'Ярославская область '),
409			 
410			(77,
411			 'г. Москва '),
412			 
413			(78,
414			 'г. Санкт-Петербург '),
415			 
416			(79,
417			 'Еврейская автономная область '),
418			 
419			(80,
420			 'Агинский Бурятский АО. '),
421			 
422			(81,
423			 'Пермский край '),
424			 
425			(82,
426			 'Корякский АО '),
427			 
428			(83,
429			 'Ненецкий АО '),
430			 
431			(84,
432			 'Таймырский АО '),
433			 
434			(85,
435			 'Усть-Ордынский Бурятский АО '),
436			 
437			(86,
438			 'Ханты-Мансийский АО '),
439			 
440			(87,
441			 'Чукотский АО '),
442			 
443			(88,
444			 'Эвенкийский АО '),
445			 
446			(89,
447			 'Ямало-Ненецкий АО '),
448			 
449			(90,
450			 'Московская область '),
451			 
452			(91,
453			 'Калининградская область '),
454			 
455			(93,
456			 'Краснодарский край '),
457			 
458			(94,
459			 'Территории, находящиеся за пределами РФ и обслуживаемые Управлением режимных объектов МВД России '),
460			 
461			(95,
462			 'Чеченская республика - новые номера'),
463			 
464			(96,
465			 'Свердловская область '),
466			 
467			(97,
468			 'г. Москва '),
469			 
470			(98,
471			 'г. Санкт-Петербург '),
472			 
473			(99,
474			 'г. Москва '),
475			 
476			(102,
477			 ' Республика Башкирия '),
478			 
479			(116,
480			 'Республика Татарстан '),
481			 
482			(118,
483			 'Удмуртская Республика '),
484			 
485			(121,
486			 'Чувашская Республика '),
487			 
488			(125,
489			 'Приморский край '),
490			 
491			(138,
492			 'Иркутская область '),
493			 
494			(150,
495			 'Московская область '),
496			 
497			(152,
498			 'Нижегородская область '),
499			 
500			(154,
501			 'Новосибирская область '),
502			 
503			(159,
504			 'Пермский край '),
505			 
506			(161,
507			 'Ростовская область '),
508			 
509			(163,
510			 'Самарская область '),
511			 
512			(164,
513			 'Саратовская область '),
514			 
515			(173,
516			 'Ульяновская область '),
517			 
518			(174,
519			 'Челябинская область '),
520			 
521			(177,
522			 'г. Москва '),
523			 
524			(178,
525			 'г. Санкт-Петербург '),
526			 
527			(197,
528			 'г. Москва '),
529			 
530			(199,
531			 'г. Москва ')
532GO
533
534INSERT INTO [dbo].[PolicePost]
535          ([PolicePostId]
536          ,[PostName]
537          ,[RegionId])
538    VALUES
539          (0,N'Пост №1',74)
540		   ,(1,N'Пост №2',74)
541		   ,(2,N'Пост №3',74)
542GO
543
544INSERT INTO [dbo].[CarModel]
545          ([CarModelId]
546          ,[Model])
547    VALUES
548          (0,N'BMW')
549		   ,(1,N'Mercedes')
550		   ,(2,N'Audi')
551		   ,(3,N'Nissan')
552GO
553
554INSERT INTO [dbo].[CarRegNumber]
555          ([CarRegNumberId]
556          ,[Letters]
557          ,[Number]
558          ,[RegionId])
559    VALUES
560          (0,N'ABE',123,174)
561		   ,(1,N'AAP',777,196)
562		   ,(2,N'AAX',666,96)
563		   ,(3,N'AAA',123,96)
564		   ,(4,N'ATE',123,159)
565		   ,(5,N'AHE',123,96)
566		   ,(6, N'ABC', 676, 59)
567		   ,(7, N'KAM', 323, 74)
568		   ,(8, N'KAA', 322, 159)
569/*	   
570		   INSERT INTO [dbo].[CarRegNumber]
571          ([CarRegNumberId]
572          ,[Letters]
573          ,[Number]
574          ,[RegionId])
575    VALUES
576		   (9, N'KCA', 342, 559)
577*/
578GO
579         
580INSERT INTO [dbo].[Car]
581          ([CarId]
582          ,[CarModelId]
583		  ,[CarRegNumberId])
584    VALUES
585          (0, 0, 0)
586		  ,(1, 1, 1)
587		  ,(2, 2, 2)
588		  ,(3, 0, 3)
589		  ,(4, 1, 6)
590		  ,(5, 2, 7)
591		  ,(6, 3, 8)
592GO
593
594INSERT INTO [dbo].[Journal]
595          ([RecordTime]
596          ,[CarId]
597          ,[DirectId]
598          ,[PolicePostId])
599    VALUES
600          ('2013-15-04 08:15:30.0', 0, 0, 0)
601		   ,('2013-15-04 08:20:30.0', 1, 1, 0)
602		   ,('2013-21-04 08:25:30.0', 2, 1, 0)
603		   ,('2013-22-04 08:30:30.0', 2, 0, 1)
604		   ,('2013-27-04 08:15:30.0', 1, 0, 1)
605		   ,('2013-25-04 08:20:30.0', 1, 0, 0)
606		   ,('2013-26-04 08:25:30.0', 0, 1, 0)
607		   ,('2013-25-04 08:30:30.0', 0, 0, 1)
608		   ,('2013-24-04 08:15:30.0', 2, 0, 1)
609		   ,('2013-23-04 08:20:30.0', 1, 1, 0)
610		   ,('2013-25-04 08:25:30.0', 1, 1, 0)
611		   ,('2013-06-04 08:30:30.0', 2, 0, 1)
612		   ,('2013-08-05 08:15:30.0', 0, 1, 1)
613
614		   ,('2013-23-04 08:20:30.0', 4, 0, 0) --transit
615		   ,('2013-25-04 08:25:30.0', 5, 0, 0) --inogorod
616		   ,('2013-06-04 08:30:30.0', 6, 0, 1) -- transit
617
618		   ,('2013-23-04 08:21:30.0', 4, 1, 1) --transit
619		   ,('2013-25-04 08:26:30.0', 5, 1, 0) --inogorod
620		   ,('2013-06-04 08:35:30.0', 6, 1, 0) -- transit
621GO
622
623IF OBJECT_ID ('TempDB') IS NOT NULL
624	DROP TABLE [#TempDB]
625GO
626
627SELECT Journal.RecordId AS '№', Journal.RecordTime AS 'Время', Car.CarId AS 'ID машины',CarModel.Model AS 'Модель машины', 
628		CarRegion.RegionName AS 'Регион авто', CarRegNumber.RegionId as 'Номер региона', 
629		(substring(CarRegNumber.Letters, 1, 1) + CarRegNumber.Number + substring(CarRegNumber.Letters, 2, 2)) AS 'Номера', 
630		Direct.DirectName AS 'Направление движения'
631		
632INTO #TempDB
633
634FROM Journal INNER JOIN Car ON Journal.CarId = Car.CarId
635 			 INNER JOIN CarModel ON Car.CarModelId = CarModel.CarModelId
636			 INNER JOIN Direct ON Journal.DirectId = Direct.DirectId
637			 INNER JOIN CarRegNumber ON Car.CarRegNumberId = CarRegNumber.CarRegNumberId
638			 INNER JOIN Region AS CarRegion ON CarRegNumber.RegionId = CarRegion.RegionId
639			 INNER JOIN PolicePost ON Journal.PolicePostId = PolicePost.PolicePostId
640			 INNER JOIN Region AS PolicePostRegion ON PolicePost.RegionId = PolicePostRegion.RegionId
641
642SELECT * FROM #TempDB
643ORDER BY #TempDB.[Номер региона]