USE Metro
GO

/* формирование расписания для одного состава */
CREATE PROCEDURE onesostav (@ser int, @vetka varchar(20), @start_time time, @k_time time, @serialpas float)
AS
BEGIN
	DECLARE  @rab int, @t_vremya time, @typ int, @stanciya int, @next_stanciya int, @p int, @finish_time time
	SELECT @typ=typkod FROM psostav WHERE serial=@ser
	SET @rab=NULL;
	SELECT @stanciya=start_stanciya FROM vetka WHERE name=@vetka	
	SET @p=0	
	SET @finish_time=@start_time
	WHILE @finish_time<@k_time
	BEGIN		
		IF @p=0
		BEGIN
			SELECT @next_stanciya=kod_vpered FROM stanciya WHERE kod=@stanciya
			IF @next_stanciya is NULL
			BEGIN
				SELECT @next_stanciya=kod_nazad FROM stanciya WHERE kod=@stanciya
				SET @p=1
			END
		END
		ELSE
		BEGIN
			SELECT @next_stanciya=kod_nazad FROM stanciya WHERE kod=@stanciya
			IF (@next_stanciya is NULL)
			BEGIN
				SELECT @next_stanciya=kod_vpered FROM stanciya WHERE kod=@stanciya
				SET @p=0
			END
		END
		SELECT @finish_time=DATEADD(SECOND,dbo.adtime(@typ, @stanciya, @p),@start_time)	
		IF (SELECT finish_time FROM stanciya WHERE kod=@next_stanciya) > @finish_time and (SELECT start_time FROM stanciya WHERE kod=@stanciya) < @start_time
		BEGIN		
			IF ((SELECT stat FROM stanciya WHERE kod=@next_stanciya) = 1) or (@finish_time<@k_time)
			BEGIN
				IF @rab IS NULL
					INSERT INTO raspisanie
						VALUES (@start_time, @stanciya, @finish_time, @next_stanciya, @p, @vetka, @ser, @serialpas)
				ELSE
				BEGIN
					UPDATE raspisanie	
						SET finish_vremya=@finish_time, finish_kod=@next_stanciya, naprav=@p
						WHERE start_vremya=@t_vremya and start_kod=@rab and vetka=@vetka
					SET @rab = NULL
					SET @t_vremya=NULL
				END	
			END
			ELSE
			BEGIN
				IF @rab IS NULL
				BEGIN
					INSERT INTO raspisanie (start_vremya, start_kod, vetka, serial, serialpas)
						VALUES (@start_time, @stanciya, @vetka, @ser, @serialpas)
					SET @rab=@stanciya
					SET @t_vremya=@start_time
				END
			END
		END		
		SET @start_time=@finish_time
		SELECT @start_time=DATEADD(SECOND,15,@start_time)
		SET @stanciya=@next_stanciya
	END
	UPDATE psostav
		SET stat = (SELECT start_status FROM vetka WHERE name=@vetka)
		WHERE serial=@ser
END
GO

/* формирование оптимального рассписания */
CREATE PROCEDURE sostav_raspisanie @vetka nvarchar(20), @n_time time, @k_time time, @kol int, @typ int, @serialpas float
AS
BEGIN
	DECLARE @depo int, @maxi int, @paspotok int, @pasvmestdepo int, @kol_sostav int, @interval int
	SELECT @depo=depo_status FROM vetka WHERE name=@vetka
	SET @maxi=dbo.maxi_sostav(@vetka, @typ)
	SET @paspotok=dbo.paspotok_vetki(@vetka)
	DECLARE @sostavi_depo TABLE (serial int, stat int, kolmest int, typkod int)
		INSERT INTO @sostavi_depo
		SELECT serial, stat, kolmest, typsostav.typkod
			FROM psostav INNER JOIN typsostav ON psostav.typkod=typsostav.typkod
			WHERE stat=@depo and kolmest!=0 and psostav.typkod=@typ
	DELETE @sostavi_depo
		WHERE dbo.proverka_sostav(serial)!=0 or serial not in (SELECT serial FROM mashinist)
	SELECT @pasvmestdepo=SUM(kolmest) FROM @sostavi_depo
	IF (@kol=0)
		SET @kol_sostav = CEILING(@paspotok/(SELECT kolmest FROM typsostav WHERE typkod=@typ))
	ELSE
		SET @kol_sostav=@kol
	IF (@kol_sostav>@maxi)
	BEGIN
		PRINT 'На ветку будет выведено максимальное количество составов!'
		SET @kol_sostav=@maxi
	END
	SET @interval = dbo.start_interval(@vetka, @typ, @kol_sostav)
	DECLARE @CURSOR CURSOR, @t_ser int, @t_kol int, @k int
	SET @k=0
	SET @CURSOR  = CURSOR SCROLL
	FOR
	SELECT serial, kolmest
		FROM @sostavi_depo
	OPEN @CURSOR
	FETCH NEXT FROM @CURSOR INTO @t_ser, @t_kol
	WHILE (@@FETCH_STATUS = 0 and @k<@kol_sostav)
	BEGIN
		DECLARE @start_time time
		SET @start_time=@n_time
		SELECT @start_time=DATEADD(SECOND,@interval*@k,@n_time)	
		IF (@start_time<@k_time)
			EXEC onesostav @t_ser, @vetka, @start_time, @k_time, @serialpas
		ELSE BREAK;
		SET @k=@k+1
		FETCH NEXT FROM @CURSOR INTO @t_ser, @t_kol
	END
	CLOSE @CURSOR	
END
GO

/*
EXEC sostav_raspisanie 'Красная', '7:00', '7:01', 0, 1, 123456
*/

/* проверка введеной информации, перед формирвоанием оптимального расписания */
CREATE PROC prov_raspisanie @vetka nvarchar(20), @n_time time, @k_time time, @kol int, @typ int, @serialpas float
AS
BEGIN
	IF @vetka in (SELECT name FROM vetka)
		IF NOT EXISTS (SELECT DISTINCT vetka FROM raspisanie WHERE vetka=@vetka)
			IF (@n_time<@k_time)
				IF (@kol>=0)
					IF @typ in (SELECT typkod FROM typsostav)
						IF EXISTS(SELECT serial FROM psostav WHERE stat=(SELECT depo_status FROM vetka WHERE name=@vetka) and typkod=@typ) 
							EXEC sostav_raspisanie @vetka, @n_time, @k_time, @kol, @typ, @serialpas
						ELSE PRINT 'Составов данного типа нет в депо этой станции'
					ELSE PRINT 'Такого типа составов не существует'
				ELSE PRINT 'Количество составов не может быть отрицательным'
			ELSE PRINT 'Задайте правильно время'
		ELSE PRINT 'Расписание для этой ветки уже составлено'
	ELSE PRINT 'Такой ветки не существует'
END
GO

/* оптимальные интервалы для расписания */
CREATE PROCEDURE alignment_interval (@time time, @vetka nvarchar(20), @typ int, @kol_sostav int)
AS
BEGIN		
	DECLARE @CURSOR CURSOR, @t_ser int, @vremya time, @stanciya int
	SELECT @vremya=@time
	SET @CURSOR = CURSOR SCROLL		
	FOR 
	SELECT DISTINCT serial 
		FROM raspisanie
		WHERE start_vremya>=@vremya and vetka=@vetka	
	OPEN @CURSOR
	FETCH NEXT FROM @CURSOR INTO @t_ser
	WHILE @@FETCH_STATUS=0
	BEGIN
		DECLARE @new_interval int, @sled_sostav int, @interval int, @sled_time time, @prom int
		SET @new_interval=dbo.start_interval(@vetka, @typ, @kol_sostav)
		SET @sled_sostav=dbo.sled_sostav (@t_ser, @vremya)
		SELECT @stanciya=start_kod FROM raspisanie WHERE start_vremya<DATEADD(SECOND,15,@vremya) and finish_vremya>DATEADD(SECOND,-15,@vremya) and serial=@sled_sostav
		SELECT TOP 1 @sled_time=start_vremya FROM raspisanie WHERE serial=@t_ser and start_vremya>@vremya and start_kod=@stanciya ORDER BY start_vremya ASC
		SET @interval=DATEDIFF(SECOND,@vremya,@sled_time)
		IF (@interval<@new_interval) SET @prom=-1 ELSE SET @prom=1
		DECLARE @CURSOR2 CURSOR, @t_svremya time, @t_fvremya time, @dob int, @dob2 int
		SET @dob=0 SET @dob2=20
		SET @CURSOR2 = CURSOR SCROLL
		FOR
		SELECT start_vremya, finish_vremya FROM raspisanie WHERE serial=@sled_sostav and start_vremya>@sled_time ORDER BY start_vremya ASC
		OPEN @CURSOR2
		FETCH NEXT FROM @CURSOR2 INTO @t_svremya, @t_fvremya
		WHILE @@FETCH_STATUS=0
		BEGIN
			IF (@prom=-1)
			BEGIN
				IF(@interval<@new_interval)
				BEGIN
					SET @interval=@interval+20
					SET @dob=@dob+20
					SET @vremya=DATEADD(SECOND,-@dob+@dob2,@t_svremya)
				END ELSE SET @dob2=0
				UPDATE raspisanie
					SET start_vremya=DATEADD(SECOND,-@dob+@dob2,start_vremya), finish_vremya=DATEADD(SECOND,-@dob,finish_vremya)
					WHERE start_vremya=@t_svremya and serial=@sled_sostav and finish_vremya=@t_fvremya
			END
			ELSE
			BEGIN
				IF(@interval>@new_interval)
				BEGIN
					SET @interval=@interval-20
					SET @dob=@dob+20
					SET @vremya=DATEADD(SECOND,@dob+@dob2,@t_svremya)
				END ELSE SET @dob2=0
				UPDATE raspisanie
					SET start_vremya=DATEADD(SECOND,@dob,start_vremya), finish_vremya=DATEADD(SECOND,@dob-@dob2,finish_vremya)
					WHERE start_vremya=@t_svremya and serial=@sled_sostav and finish_vremya=@t_fvremya
			END
		FETCH NEXT FROM @CURSOR2 INTO @t_svremya, @t_fvremya
	END
	CLOSE @CURSOR2
	FETCH NEXT FROM @CURSOR INTO @t_ser
	END
	CLOSE @CURSOR
END
GO

/* удаление состава из расписания */
CREATE PROCEDURE del_sostav_raspisanie (@ser int, @start_time time)
AS
BEGIN
	DECLARE @vetka varchar(20), @typ int
	SELECT @typ=typkod FROM psostav WHERE serial=@ser
	SELECT TOP 1 @vetka=vetka FROM raspisanie WHERE serial=@ser
	IF (SELECT stat FROM psostav WHERE serial=@ser) IN (SELECT start_status FROM vetka WHERE name=@vetka UNION SELECT finish_status FROM vetka WHERE name=@vetka)
	BEGIN
		DECLARE @kol_sostav int
		SELECT @kol_sostav=COUNT(serial) FROM psostav WHERE stat IN (SELECT start_status FROM vetka WHERE name=@vetka UNION SELECT finish_status FROM vetka WHERE name=@vetka)
		SELECT TOP 1 @start_time=start_vremya
			FROM raspisanie
			WHERE start_vremya>=@start_time and start_kod=(SELECT start_stanciya FROM vetka WHERE name=@vetka) and serial=@ser
			ORDER BY start_vremya ASC
			DELETE FROM raspisanie
				WHERE serial=@ser and start_vremya>=@start_time
			SET @kol_sostav=@kol_sostav-1
			EXEC alignment_interval @start_time, @vetka, @typ, @kol_sostav
		UPDATE psostav
			SET stat = (SELECT depo_status FROM vetka WHERE name=@vetka)
	END
	ELSE PRINT 'Состав не находится на ветке'
END
GO

/* добавление состава в рассписание */
CREATE PROCEDURE add_sostav_raspis (@ser int, @vetka varchar(20), @time time, @serialpas float)
AS
BEGIN
IF EXISTS (SELECT vetka FROM raspisanie WHERE vetka=@vetka)
IF @time> (SELECT MIN(start_vremya) FROM raspisanie) and @time< (SELECT MAX(finish_vremya) FROM raspisanie)
BEGIN
	DECLARE @typ int, @depo int, @kol_sostav int, @depo_stanciya int, @k_time time
	SELECT @depo_stanciya=start_stanciya FROM vetka WHERE name=@vetka
	SELECT @typ=typkod FROM psostav WHERE serial=@ser
	SELECT @depo=depo_status FROM vetka WHERE name=@vetka
	SELECT TOP 1 @k_time=start_vremya FROM raspisanie ORDER BY start_vremya DESC
	SELECT @kol_sostav=COUNT(serial) FROM psostav WHERE stat IN (SELECT start_status FROM vetka WHERE name=@vetka UNION SELECT finish_status FROM vetka WHERE name=@vetka)
	IF EXISTS (SELECT serial FROM mashinist WHERE serial=@ser)
		IF (dbo.proverka_sostav(@ser)=0)
			IF (SELECT stat FROM psostav WHERE serial=@ser) = @depo
				IF EXISTS (SELECT * FROM raspisanie)
				BEGIN
					IF NOT EXISTS (SELECT * FROM raspisanie WHERE start_vremya <= DATEADD(SECOND,90,@time) and start_vremya > DATEADD(SECOND,-90,@time) and vetka=@vetka and start_kod=@depo_stanciya)
						IF (SELECT DISTINCT typkod FROM psostav WHERE serial = (SELECT TOP 1 serial FROM raspisanie)) = @typ
							IF ((SELECT COUNT(DISTINCT serial) FROM raspisanie WHERE vetka=@vetka) < dbo.maxi_sostav(@vetka, @typ))
							BEGIN
								EXEC onesostav @ser, @vetka, @time, @k_time, @serialpas	
								SET @kol_sostav=@kol_sostav+1
								EXEC alignment_interval @time, @vetka, @typ, @kol_sostav
							END
							ELSE PRINT 'На ветке находится макисмально допустимое количество составов'
						ELSE PRINT 'Тип состава не соответствует типу составов, которые находятся на ветке'
					ELSE PRINT 'Нельзя пустить состав в это время, выберете другое'
				END
				ELSE EXEC onesostav @ser, @vetka, @time, @k_time, @serialpas
			ELSE PRINT 'Состав не находится в депо ветки, переведите поезд в депо'
		ELSE PRINT 'Состав нуждается в техническом обслуживании'
	ELSE PRINT 'На данный состав не назначен машинист'
END
ELSE PRINT 'Нельзя добавить состав в это время'
ELSE PRINT 'Для этой ветки не сформировано расписание. Добавление не возможно!'
END
GO

/* удаление расписания для ветки */
CREATE PROC del_rasp @vetka nvarchar(20)
AS
BEGIN
	IF @vetka in (SELECT name FROM vetka)
		IF EXISTS (SELECT vetka FROM raspisanie WHERE vetka=@vetka)
		BEGIN
			UPDATE psostav
				SET stat=(SELECT depo_status FROM vetka WHERE name=@vetka)
				WHERE serial in (SELECT serial FROM raspisanie WHERE vetka=@vetka)
		DELETE raspisanie
			WHERE vetka=@vetka
		END
		ELSE PRINT 'Для этой ветки нет расписания'
	ELSE PRINT 'Такой ветки не существует'
END
GO


/* Добавление станции */
CREATE PROCEDURE add_stanciya (@kod int, @name nvarchar(20), @paspotok int, @kod_nazad int, @rast_nazad int, @rast_vpered int, @stat int, @start_time time, @finish_time time)
AS
BEGIN
IF (@kod!=0)
	IF NOT EXISTS (SELECT * FROM stanciya WHERE @kod=kod)
		IF (EXISTS (SELECT * FROM stanciya WHERE @kod_nazad=kod) or @kod_nazad IS NULL or @kod_nazad=0)
			IF (@kod_nazad is not NULL or @kod_nazad!=0) and ((SELECT kod_vpered FROM stanciya WHERE kod=@kod_nazad) is not NULL) and (@rast_vpered is null or @rast_vpered=0)
				PRINT 'Невозможно добавить станцию без расстояния вперед, если она не конечная.'
			ELSE
				IF (@start_time<@finish_time)
					IF (@kod_nazad is not null and @kod_nazad!=0) and (@rast_nazad=0 or @rast_nazad is null)
						PRINT 'Добавльте расстояние назад'
					ELSE
						INSERT INTO stanciya
							VALUES (@kod, @name, @paspotok, NULL, @rast_vpered, @kod_nazad, @rast_nazad, @stat, @start_time, @finish_time)
				ELSE PRINT 'Исправьте время открытия/закрытия станции'
		ELSE PRINT 'Не существует станции с кодом: ' + cast(@kod_nazad as nvarchar(10))
	ELSE PRINT 'Станция с таким кодом уже существует'
ELSE PRINT 'Код станции не может быть 0'
END
GO

/* редактирование станции */
CREATE PROCEDURE edit_stanciya (@kod int, @name nvarchar(20), @paspotok int, @rast_nazad int, @rast_vpered int, @stat int, @start_time time, @finish_time time)
AS
BEGIN
	IF EXISTS (SELECT kod FROM stanciya WHERE kod=@kod)
		IF @paspotok>=0
			IF @rast_nazad>=0 or @rast_nazad is NULL
				IF @rast_vpered>=0 or @rast_vpered is NULL
					IF (@finish_time>@start_time)
						IF (SELECT kod_nazad FROM stanciya WHERE kod=@kod) is not null and (@rast_nazad>0 or @rast_nazad is not null)
						BEGIN
							UPDATE stanciya
								SET paspotok=@paspotok, name=@name, rast_nazad=@rast_nazad, rast_vpered=@rast_vpered, stat=@stat, start_time=@start_time, finish_time=@finish_time
								WHERE kod=@kod
							UPDATE stanciya
								SET rast_vpered=@rast_nazad
								WHERE kod=(SELECT kod_nazad FROM stanciya WHERE kod=@kod)
							UPDATE stanciya
								SET rast_nazad=@rast_vpered
								WHERE kod=(SELECT kod_vpered FROM stanciya WHERE kod=@kod)
						END
						ELSE PRINT 'Нельзя добавить расстояние назад'
					ELSE PRINT 'Некорректное время работы станции'
				ELSE PRINT 'Расстояние вперед не может быть отрицательным'
			ELSE PRINT 'Расстояние назад не может быть отрицательным'
		ELSE PRINT 'Пассажиропоток не может быть отрицательным'
	ELSE PRINT 'Станции с таким кодом не существует'
END
GO

/* удаление станции */
CREATE PROC del_stanciya (@kod int)
AS
BEGIN
	IF EXISTS (SELECT kod FROM stanciya WHERE kod=@kod)
		IF NOT EXISTS (SELECT start_kod FROM raspisanie WHERE start_kod=@kod)
			DELETE stanciya
				WHERE kod=@kod
		ELSE PRINT 'Нельзя удалить станцию, пока она в расписании'
	ELSE PRINT 'Станции с таким кодом нет'
END
GO

/* Добавление ветки */
CREATE PROCEDURE add_vetka (@name varchar(20), @start_stanciya int, @depo_status int, @start_status int, @finish_status int)
AS
BEGIN
	IF NOT EXISTS (SELECT name FROM vetka WHERE name=@name)
		IF @start_stanciya not in (SELECT start_stanciya FROM vetka) and (SELECT kod_vpered FROM stanciya WHERE kod=@start_stanciya) is NOT NULL
			IF EXISTS (SELECT * FROM stanciya WHERE kod=@start_stanciya)
				IF ((SELECT kod_nazad FROM stanciya WHERE kod=@start_stanciya) is null)
					IF @depo_status NOT IN (SELECT depo_status FROM vetka) 
						IF @start_status NOT IN (SELECT start_status FROM vetka) 
							IF @finish_status NOT IN (SELECT finish_status FROM vetka)
								IF @finish_status!=@start_status and @start_status!=@depo_status and @depo_status!=@finish_status
								BEGIN
									DECLARE @finish_stanciya int
									SELECT @finish_stanciya=kod_vpered FROM stanciya WHERE kod=@start_stanciya
									WHILE (SELECT kod_vpered FROM stanciya WHERE kod=@finish_stanciya) is NOT NULL
										SELECT @finish_stanciya=kod_vpered FROM stanciya WHERE kod=@finish_stanciya
									INSERT INTO vetka (name, start_stanciya, finish_stanciya, depo_status, start_status, finish_status)
									VALUES (@name, @start_stanciya, @finish_stanciya, @depo_status, @start_status, @finish_status)	
								END	
								ELSE PRINT 'Значения статусов должны быть отличными друг от друга'
							ELSE PRINT 'Выберете другой статус движения состава к депо'
						ELSE PRINT 'Выберете другой статус движения состава от депо'
					ELSE PRINT 'Выберете другой идентификатор статуса депо'
				ELSE PRINT 'Станция не может быть депо. Выберете или создайте другую станцию'
			ELSE PRINT 'Станции для депо нет, создайте станцию депо'
		ELSE PRINT 'Выберете другую станцию для депо'
	ELSE PRINT 'Такая ветка уже существует'
END
GO

/* Удаление ветки */
CREATE PROC del_vetka (@name nvarchar(20), @name2 nvarchar(20))
AS
BEGIN
	IF EXISTS (SELECT name FROM vetka WHERE name=@name)
	BEGIN
		DECLARE @depo int, @depo2 int
		SELECT @depo=depo_status FROM vetka WHERE name=@name
		SELECT @depo2=depo_status FROM vetka WHERE name=@name2
		IF @name in (SELECT vetka FROM raspisanie WHERE vetka=@name)
			EXEC del_rasp @name
		IF EXISTS (SELECT serial FROM psostav WHERE typkod=@depo) and @name2 is not null
			UPDATE psostav SET stat = @depo2 WHERE stat = @depo
		ELSE
			UPDATE psostav SET stat = NULL WHERE stat = @depo
		DELETE vetka
			WHERE name=@name
	END
	ELSE PRINT 'Ветки с таким названием не сущетсвует'
END
GO

/* добавление состава в таблицу */
CREATE PROCEDURE add_sostav (@serial int, @typkod int, @vetka nvarchar(20), @probeg float, @iznos_motor float, @iznos_tk float)
AS
BEGIN
	IF NOT EXISTS (SELECT * FROM psostav WHERE serial=@serial)
		IF @typkod IN (SELECT typkod FROM typsostav)
			IF EXISTS (SELECT name FROM vetka WHERE name=@vetka)
				INSERT INTO psostav
					VALUES (@serial, @typkod, (SELECT depo_status FROM vetka WHERE name=@vetka), @probeg, @iznos_motor, @iznos_tk)
			ELSE PRINT 'Такой ветки не существет'
		ELSE PRINT 'Такого типа состава не существует'
	ELSE PRINT 'Такой серийный номер уже существует'
END
GO

/* удаление состава из таблицы */
CREATE PROCEDURE del_sostav (@serial int)
AS
BEGIN
	IF EXISTS (SELECT serial FROM psostav WHERE serial=@serial)
		IF NOT EXISTS (SELECT serial FROM raspisanie WHERE serial=@serial)
		BEGIN
			IF EXISTS (SELECT serial FROM remont WHERE serial=@serial)
				UPDATE remont
					SET serial=NULL, start=NULL, kod=NULL
					WHERE serial=@serial 
			IF EXISTS (SELECT serial FROM mashinist WHERE serial=@serial)
				UPDATE mashinist
					SET serial=NULL
					WHERE serial=@serial
			DELETE psostav
				WHERE serial=@serial
		END
		ELSE PRINT 'Невозможно удалить состав, так как он числится в расписании'
	ELSE PRINT 'Состава с таким серийным номером нет'
END
GO

/* изменение состава */
CREATE PROC edit_psostav (@serial int, @typkod int, @vetka nvarchar(20), @probeg float, @iznos_motor float, @iznos_tk float)
AS
BEGIN
	IF EXISTS (SELECT name FROM vetka WHERE name=@vetka)
		IF (@probeg>=0 and @iznos_motor>=0 and @iznos_tk>=0)
			IF EXISTS (SELECT * FROM psostav WHERE serial=@serial)
				IF @typkod IN (SELECT typkod FROM typsostav)
					IF ((SELECT stat FROM psostav WHERE serial=@serial) in (SELECT depo_status FROM vetka)) or (SELECT stat FROM psostav WHERE serial=@serial) is NULL
						UPDATE psostav
							SET typkod=@typkod, stat=(SELECT depo_status FROM vetka WHERE name=@vetka), probeg=@probeg, iznos_motor=@iznos_motor, iznos_tk=@iznos_tk
							WHERE serial=@serial
					ELSE PRINT 'Нельзя поменять статус данного состава'
				ELSE PRINT 'Такого типа состава не существует'
			ELSE PRINT 'Такого серийного номера не существует'
		ELSE PRINT 'Введите корректные данные'
	ELSE PRINT 'Такой ветки не существет'
END
GO

/* отправление состава на ремонт */
CREATE PROCEDURE add_remont (@typik int, @serial int, @date datetime, @serialpass float, @zametka nvarchar(100))
AS
BEGIN
	IF EXISTS (SELECT typik FROM remont WHERE typik=@typik)
		IF EXISTS (SELECT serial FROM psostav WHERE serial=@serial)
		BEGIN
			DECLARE @kod int
			SET @kod=dbo.proverka_sostav(@serial)
			IF (@kod!=0)
				IF (SELECT stat FROM psostav WHERE serial=@serial) IN (SELECT depo_status FROM vetka)
					IF EXISTS (SELECT typik FROM remont WHERE serial IS NULL and typik=@typik)
					BEGIN
						UPDATE remont
							SET kod=@kod, serial=@serial, start=@date
							WHERE typik=@typik
						INSERT INTO jurnal
							VALUES	(@date, @serialpass, @serial, @typik, @zametka)
						UPDATE psostav SET stat=0 WHERE serial=@serial
					END
					ELSE PRINT 'Этот тупик уже занят!'
				ELSE PRINT 'Поезд находится не в депо. Сначала верните его в депо'
			ELSE PRINT 'Состав не нуждается в техническом обслуживании'
		END
		ELSE PRINT 'Такого состава не существует'
	ELSE PRINT 'Такого тупика не существует. Выберете другой.'
END
GO

/* Удаление из ремонта */
CREATE PROC del_remont (@serial int, @vetka nvarchar(20), @serialpass float)
AS
BEGIN
	IF EXISTS (SELECT serial FROM psostav WHERE serial=@serial)
		IF EXISTS (SELECT name FROM vetka WHERE name=@vetka)
			IF EXISTS (SELECT serialpas FROM dispetcher WHERE serialpas=@serialpass)
				IF EXISTS (SELECT serial FROM remont WHERE serial=@serial)
				BEGIN
					UPDATE remont
						SET serial=NULL, start=NULL, kod=NULL
						WHERE serial=@serial
					UPDATE psostav
						SET stat=(SELECT depo_status FROM vetka WHERE name=@vetka)
						WHERE serial=@serial
				END
				ELSE PRINT 'Данный состав не находится на ремонтной станции'
			ELSE PRINT 'Такого диспетчера не существует'
		ELSE PRINT 'Такой ветки не существует'
	ELSE PRINT 'Такого состава не существует'
END
GO

/* добавление нового тупика для ремонта */
CREATE PROC add_typik (@typik int)
AS
BEGIN
	IF NOT EXISTS (SELECT typik FROM remont WHERE typik=@typik)
		INSERT INTO remont
			VALUES (@typik, NULL, NULL, NULL)
	ELSE PRINT 'Тупик с таким номером уже существует'
END
GO

/* удаление тупика */
CREATE PROC del_typik (@typik int)
AS
BEGIN
	IF EXISTS (SELECT typik FROM remont WHERE typik=@typik)
		IF EXISTS (SELECT typik FROM remont WHERE serial is NULL and typik=@typik)
			DELETE remont
				WHERE typik=@typik
		ELSE PRINT 'Тупик не пуст, переведите поезд в депо'
	ELSE PRINT 'Тупика с таким кодом нет'
END
GO

/* добавление диспетчера */
CREATE PROCEDURE add_dispetcher (@serialpas float, @fio t_fio, @tel nvarchar(16), @doljnost nvarchar(20), @pas varchar(1000))
AS
BEGIN
	IF NOT EXISTS (SELECT serialpas FROM dispetcher WHERE serialpas=@serialpas)
		IF (@fio like '% %')
			IF @doljnost in ('Стажер', 'Диспетчер', 'Старший диспетчер')
				INSERT INTO dispetcher
					VALUES (@serialpas, @fio, @tel, @doljnost, HASHBYTES('MD5', @pas))
			ELSE PRINT 'Введите корректную должность!'
		ELSE PRINT 'Введите корректные значения ФИО'
	ELSE PRINT 'Такой номер паспорта уже есть в базе!'
END
GO

/* обновление диспетчеров */
CREATE PROC edit_dispetcher (@serialpas float, @fio t_fio, @tel nvarchar(16), @doljnost nvarchar(20), @pas varchar(1000))
AS
BEGIN
	IF EXISTS (SELECT serialpas FROM dispetcher WHERE serialpas=@serialpas)
		IF @doljnost in ('Стажер', 'Диспетчер', 'Старший диспетчер')
			IF (@fio like '% %')
			BEGIN
				UPDATE dispetcher
					SET fio=@fio, tel=@tel, doljnost=@doljnost
					WHERE serialpas=@serialpas
				IF (@pas IS NOT NULL)
					UPDATE dispetcher
						SET pas=HASHBYTES('MD5',@pas)
						WHERE serialpas=@serialpas
			END
			ELSE PRINT 'Введите корректные значения ФИО'
		ELSE PRINT 'Введите корректную должность!'
	ELSE PRINT 'Диспетчера с такими паспортными данными не существует'
END
GO

/* удаление диспетчера */
CREATE PROC del_dispetcher (@serialpas float)
AS
BEGIN
	IF EXISTS (SELECT serialpas FROM dispetcher WHERE serialpas=@serialpas)
		DELETE dispetcher
			WHERE serialpas=@serialpas
	ELSE PRINT 'Диспетчера с такими пасортными данными нет в базе'
END
GO

/* добавление машиниста */
CREATE PROCEDURE add_mashinist @serialpas float, @serial int, @fio nvarchar(20), @tel nvarchar(16), @staj int, @kvalif nvarchar(10)
AS
BEGIN
	IF NOT EXISTS (SELECT serialpas FROM mashinist WHERE serialpas=@serialpas)
		IF EXISTS (SELECT serial FROM psostav WHERE serial=@serial)
			IF (@fio like '% %')
				IF @kvalif in ('Первая', 'Вторая', 'Третья')
					IF (@staj>=0)
						INSERT INTO mashinist
							VALUES (@serialpas, @serial, @fio, @tel, @staj, @kvalif)
					ELSE PRINT 'Стаж не может быть отрицательным!'
				ELSE PRINT 'Введите корректные данные для квалификации!'
			ELSE PRINT 'Введите коректные значения ФИО'
		ELSE PRINT 'Состава с таким серийным номером в базе нет!'
	ELSE PRINT 'Такой номер паспорта уже есть'
END
GO

/* обновление машинистов */
CREATE PROC edit_mashinist (@serialpas float, @serial int, @fio t_fio, @tel nvarchar(16), @staj int, @kvalif nvarchar(10))
AS
BEGIN
	IF EXISTS (SELECT serialpas FROM mashinist WHERE serialpas=@serialpas)
		IF @serial IN (SELECT serial FROM psostav)
			IF @kvalif in ('Первая', 'Вторая', 'Третья')
				IF (@staj>=0)
					UPDATE mashinist
						SET serial=@serial, fio=@fio, tel=@tel, staj=@staj, kvalif=@kvalif
						WHERE serialpas=@serialpas
				ELSE PRINT 'Стаж не может быть отрицательным!'
			ELSE PRINT 'Введите корректные данные для квалификации!'
		ELSE PRINT 'Состава, которым управляет машинист, не существует'
	ELSE PRINT 'Машиниста с такими паспортами данными нет в базе'
END
GO

/* Удаление машиниста */
CREATE PROC del_mashinist (@serialpas float)
AS
BEGIN
	IF EXISTS (SELECT serialpas FROM mashinist WHERE serialpas=@serialpas)
		IF ((SELECT stat FROM psostav WHERE serial=(SELECT serial FROM mashinist WHERE serialpas=@serialpas)) IN (SELECT depo_status FROM vetka) or EXISTS (SELECT serial FROM mashinist WHERE serialpas!=@serialpas and serial=(SELECT serial FROM mashinist WHERE serialpas=@serialpas)))
			DELETE mashinist
				WHERE serialpas=@serialpas
		ELSE PRINT 'Переведите состав в депо станции, перед удалением машиниста'
	ELSE PRINT 'Машиниста с такими паспортными данными нет в базе!'
END
GO

/* Добалвение типа соствавов */
CREATE PROCEDURE add_typsostav (@typkod int, @name nvarchar(20), @kolmest int, @dlina float, @shirina float, @visota float, @koleya int, @naznachenie nvarchar(20), @speed int)
AS
BEGIN
	IF NOT EXISTS (SELECT typkod FROM typsostav WHERE typkod=@typkod)
		IF (@dlina>0 and @shirina>0 and @visota>0 and @koleya>0 and @kolmest>0)
			IF @dlina < 200 
				IF @shirina < 3000
					IF @visota < 5000
						IF @koleya=1520
							IF @speed>0
								INSERT INTO typsostav
									VALUES (@typkod, @name, @kolmest, @dlina, @shirina, @visota, @naznachenie, @koleya, @speed)
							ELSE PRINT 'Скорость не может быть отрицательной'
						ELSE PRINT 'Колея состава не соответствует требованиям метро'
					ELSE PRINT 'Состав слишком высокий'
				ELSE PRINT 'Состав слишком широкий'
			ELSE PRINT 'Состав слишком длинный'
		ELSE PRINT 'Введите корректные значения'
	ELSE PRINT 'Тип с таким кодом уже существует!'
END
GO

/* изменение типа составов */
CREATE PROC edit_typsostav (@typkod int, @name nvarchar(20), @kolmest int, @dlina float, @shirina float, @visota float, @koleya int, @naznachenie nvarchar(20), @speed int)
AS
BEGIN
	IF EXISTS (SELECT typkod FROM typsostav WHERE typkod=@typkod)
		IF (@dlina>0 and @shirina>0 and @visota>0 and @koleya>0 and @kolmest>0)
			IF @dlina < 200
				IF @shirina < 3000
					IF @visota < 5000
						IF @koleya=1520
							IF @speed>0
								UPDATE typsostav
									SET name=@name, kolmest=@kolmest, dlina=@dlina, shirina=@shirina, visota=@visota, koleya=@koleya, naznachenie=@naznachenie, speed=@speed
									WHERE typkod=@typkod
							ELSE PRINT 'Скорость не может быть отрицательной'
						ELSE PRINT 'Колея состава не соответствует требованиям метро'
					ELSE PRINT 'Состав слишком высокий'
				ELSE PRINT 'Состав слишком широкий'
			ELSE PRINT 'Состав слишком длинный'
		ELSE PRINT 'Введите корректные значения'
	ELSE PRINT 'Типа с таким кодом не существует!'
END
GO

/* Удаление типа составов */
CREATE PROC del_typsostav (@typkod int)
AS
BEGIN
	IF EXISTS (SELECT typkod FROM typsostav WHERE typkod=@typkod)
		IF NOT EXISTS (SELECT serial FROM raspisanie WHERE serial=(SELECT TOP 1 serial FROM psostav WHERE typkod=@typkod))
			DELETE typsostav
				WHERE typkod=@typkod
		ELSE PRINT 'Удаление невозможно, так как поезда даного типа присутствуют в расписании!'
	ELSE PRINT 'Такого типа составов не существует'
END
GO