/*
 drop proc insert_TypeEkrana1
 drop proc insert_kinoteatr1
 drop proc insert_kassa
 drop proc insert_check1
 drop proc insert_kinocompany
 drop proc insert_films
 drop proc insert_Seans
 drop proc insert_prodaja
 drop proc Skidka1

-- delete from Type_Ekrana
*/

CREATE PROC insert_TypeEkrana1 AS
	DECLARE @nom INT
	SET @nom=1
	WHILE @nom<=7
	BEGIN
	 INSERT INTO Type_Ekrana (Nazvanie_Ekrana)
	 VALUES ('Тип Экрана '+LTRIM(STR(@nom)))
	 SET @nom=@nom+1
	 END
GO

CREATE PROC insert_kinoteatr1 AS
	DECLARE @nom numeric(4)
	SET @nom=1
	WHILE @nom<=300
	BEGIN

	if @nom <=30
	begin
	INSERT INTO Kinoteatr(kinoteatr_name, city, region)
	VALUES ('Кинотеатр '+LTRIM(STR(@nom)),'UFA', 2)
	end
	if @nom >30 and @nom<=60
	begin
	INSERT INTO Kinoteatr( kinoteatr_name, city, region)
	VALUES ( 'Кинотеатр '+LTRIM(STR(@nom)),'Адыгея', 1)
	end
	if @nom >60 and @nom<=90
	begin
	INSERT INTO Kinoteatr( kinoteatr_name, city, region)
	VALUES ( 'Кинотеатр '+LTRIM(STR(@nom)),'Бурятия', 3)
	end
	if @nom >90 and @nom<=120
	begin
	INSERT INTO Kinoteatr( kinoteatr_name, city, region)
	VALUES ( 'Кинотеатр '+LTRIM(STR(@nom)),'Алтай', 4)
	end
	if @nom >120 and @nom<=150
	begin
	INSERT INTO Kinoteatr( kinoteatr_name, city, region)
	VALUES ( 'Кинотеатр '+LTRIM(STR(@nom)),'Дагестан', 5)
	end
	if @nom >150 and @nom<=180
	begin
	INSERT INTO Kinoteatr( kinoteatr_name, city, region)
	VALUES ( 'Кинотеатр '+LTRIM(STR(@nom)),'Ингушетия', 6)
	end
	if @nom >180 and @nom<=210
	begin
	INSERT INTO Kinoteatr( kinoteatr_name, city, region)
	VALUES ( 'Кинотеатр '+LTRIM(STR(@nom)),'Кабардинка', 7)
	end
	if @nom >210 and @nom<=240
	begin
	INSERT INTO Kinoteatr( kinoteatr_name, city, region)
	VALUES ( 'Кинотеатр '+LTRIM(STR(@nom)),'Калмыкия', 8)
	end
	if @nom >240 and @nom<=270
	begin
	INSERT INTO Kinoteatr( kinoteatr_name, city, region)
	VALUES ( 'Кинотеатр '+LTRIM(STR(@nom)),'Черкессия', 9)
	end
	if @nom >270 and @nom<=300
	begin
	INSERT INTO Kinoteatr( kinoteatr_name, city, region)
	VALUES ( 'Кинотеатр '+LTRIM(STR(@nom)),'Карелия', 10)
	end
	 SET @nom=@nom+1
	 END
GO
/*
INSERT INTO Kinoteatr(kinoteatr_name, city, region)
VALUES ('Кинотеатр', 1,1)
select*from Kinoteatr
*/

CREATE PROC insert_kassa AS
	DECLARE @nom numeric(4)
	DECLARE @id_kinoteatr numeric(2)
	SET @nom=1
	WHILE @nom<=300
	BEGIN
	 INSERT INTO Kassa(kassa_name, id_kinoteatr)
	 VALUES ('Касса'+LTRIM(STR(@nom)), @nom)
	 SET @nom=@nom+1
	 END
GO

create proc insert_check1 as
	 declare @nom numeric(5)
	 set @nom = 1
	 while @nom <=18001
	 begin
	 declare @x int
		declare @year int
		declare @month int
		declare @day int
		set @month = FLOOR(RAND()*(13-1)+1);
		if @month = 1 or @month = 3 or @month = 5 or @month = 7 or @month = 8 or @month = 10 or @month = 12
		set @day = FLOOR(RAND()*(32-1)+1);
		if @month = 2
		set @day = FLOOR(RAND()*(29-1)+1);
		if @month = 4 or @month = 6 or @month = 9 or @month = 11
		set @day = FLOOR(RAND()*(31-1)+1);
	declare @date datetime
	 if @nom<4000
	 begin
	 set @year = 2016
	 set @date  = STR(@year)+'-'+STR(@day)+'-'+STR(@month)
	 insert into Chek(Data_chek)values(@date)
	 end
	 if @nom>=4001 and @nom<=10000
	 begin
	 set @year = 2017
	 set @date  = STR(@year)+'-'+STR(@day)+'-'+STR(@month)
	 insert into Chek(Data_chek)values(@date)

	 end
	 if @nom>=10001
	 begin
	 set @year = 2018
	 set @date  = STR(@year)+'-'+STR(@day)+'-'+STR(@month)
	 insert into Chek(Data_chek)values(@date)

	 end
	 set @nom = @nom+1
	 end
GO

CREATE PROC insert_kinocompany AS
	DECLARE @nom numeric(2)
	SET @nom=1
	WHILE @nom <= 10
	BEGIN
	INSERT INTO Kinocompany(Nazvanie_kinocompany, Strana)
	VALUES ('Кинокомпания '+LTRIM(STR(@nom)),'Страна'+LTRIM(STR(@nom)))
	SET @nom = @nom + 1
	END
GO

CREATE PROC insert_films AS
	DECLARE @nom numeric(4)
	DECLARE @con numeric(1)
	DECLARE @id_kinocompany numeric(2)
	SET @nom = 1
	WHILE @nom <= 140
	BEGIN
	SET @id_kinocompany = FLOOR(RAND()*(11-1)+1);
	if @nom <=20
	begin
	INSERT INTO Films (id_kinocompany,id_type, Nazvanie_films)
	VALUES (@id_kinocompany,1,'Фильм '+LTRIM(STR(@nom)))
	end
	if @nom >20 and @nom<=40
	begin
	INSERT INTO Films (id_kinocompany,id_type, Nazvanie_films)
	VALUES (@id_kinocompany,2,'Фильм '+LTRIM(STR(@nom)))
	end
	if @nom >40 and @nom<=60
	begin
	INSERT INTO Films (id_kinocompany,id_type, Nazvanie_films)
	VALUES (@id_kinocompany,3,'Фильм '+LTRIM(STR(@nom)))
	end
	if @nom >60 and @nom<=80
	begin
	INSERT INTO Films (id_kinocompany,id_type, Nazvanie_films)
	VALUES (@id_kinocompany,4,'Фильм '+LTRIM(STR(@nom)))
	end
	if @nom >80 and @nom<=100
	begin
	INSERT INTO Films (id_kinocompany,id_type, Nazvanie_films)
	VALUES (@id_kinocompany,5,'Фильм '+LTRIM(STR(@nom)))
	end
	if @nom >100 and @nom<=120
	begin
	INSERT INTO Films (id_kinocompany,id_type, Nazvanie_films)
	VALUES (@id_kinocompany,6,'Фильм '+LTRIM(STR(@nom)))
	end
	if @nom >120 and @nom<=140
	begin
	INSERT INTO Films (id_kinocompany,id_type, Nazvanie_films)
	VALUES (@id_kinocompany,7,'Фильм '+LTRIM(STR(@nom)))
	end

	SET @nom = @nom+1
	END
GO

CREATE PROC insert_Seans AS
	declare @nom numeric(4)
	declare @id_films numeric (4)
	SET @nom=1
	WHILE @nom<=300
	BEGIN
	set @id_films = FLOOR(RAND()*(141-1)+1);
	 INSERT INTO Seans (Nazvanie_seansa, id_films)
	 VALUES ('Сеанс '+LTRIM(STR(@nom)), @id_films)
	 SET @nom=@nom+1
	 END
GO

CREATE PROC insert_prodaja AS
	DECLARE cur1 CURSOR FOR SELECT id_chek FROM Chek
	 DECLARE @id_chek NUMERIC(5)
	 DECLARE @x int
	 DECLARE @mesto int = 1
	OPEN cur1
	FETCH cur1 INTO @id_chek
	WHILE @@FETCH_STATUS = 0
	BEGIN
		if @mesto = 70
		set @mesto = 1
		declare @id_seans int
		set @id_seans = FLOOR(RAND()*(301-1)+1);
		declare @id_kassa int
		set @id_kassa = FLOOR(RAND()*(301-1)+1);
		set @x = FLOOR(RAND()*(101-1)+1);
		declare @kolvo int
		if(@x<=70)
		set @kolvo = 1
		if @x>70 and @x<=90
		set @kolvo = 2
		if @x>90
		set @kolvo = 3
		declare @price int = 199
		set @price = @price * @kolvo
		insert into Prodaja_Bilets(id_seans,id_chek,id_kassa,price,Kolichestvo,mesto)
		values(@id_seans,@id_chek,@id_kassa,@price,@kolvo, @mesto)
	 FETCH cur1 INTO @id_chek
	 set @mesto = @mesto+1
	 END
	 close cur1
	 DEALLOCATE cur1
GO

create proc Skidka1 AS
	DECLARE cur1 CURSOR FOR SELECT id_chek FROM Chek
	 DECLARE @id_chek NUMERIC(5),
			 @sumChek NUMERIC(5),
			 @coeff INT
	OPEN cur1
	FETCH cur1 INTO @id_chek
	WHILE @@FETCH_STATUS = 0
	BEGIN
	SELECT @sumChek = price FROM Prodaja_Bilets where @id_chek = id_chek
	 if @sumChek>=600 
	 set @coeff = 5
	 if @sumChek<600 and @sumChek>=400
	 set @coeff = 3
	 if @sumChek<400 and @sumChek>=200
	 set @coeff = 1
	 UPDATE Chek SET Skidka = @coeff WHERE @id_chek= id_chek
	 UPDATE Prodaja_Bilets SET price=price*(100.0-@coeff)/100.0
	 WHERE @id_chek=id_chek
	 FETCH cur1 INTO @id_chek
	 END
	 CLOSE cur1
	 DEALLOCATE cur1
GO




 exec insert_TypeEkrana1;
 GO
	select * from Type_Ekrana
	GO
 exec insert_kinoteatr1;
 GO
	select * from Kinoteatr
	GO
 exec insert_kassa;
 GO
	select * from Kassa
	GO
  exec insert_check1;
 GO
	select * from Chek
	GO
 exec insert_kinocompany;
 GO
	select * from Kinocompany
	GO
 exec insert_films;
 GO
 	select * from Films
	GO
 exec insert_Seans;
 GO
	select * from Seans
	GO
 exec insert_prodaja;
 GO
	select * from Prodaja_Bilets
	GO

-- exec Skidka1;
-- GO


 
--select * from films

/*
drop table Prodaja_Bilets
drop table  Kassa

drop table Chek
drop table Seans
drop table  Films
drop table Kinocompany
drop table Type_Ekrana
drop table Kinoteatr
*/
create table Kinoteatr (
id_kinoteatr int Identity(1,1) primary key,
kinoteatr_name varchar(20) not null,
city varchar(20),
region varchar(20)
);


create table Kassa     (
                        id_kassa int Identity(1,1) primary key,
						kassa_name varchar(20) not null,
						id_kinoteatr int
						constraint fk_id_kinoteatr foreign key(id_kinoteatr)
						references Kinoteatr(id_kinoteatr)
                       );
create table Chek      (
                        id_chek int Identity(1,1) primary key,
						Skidka numeric(1) ,
						Data_chek datetime not null
                       );

create table Type_Ekrana ( 
                         id_type int Identity(1,1) primary key,
						 Nazvanie_Ekrana varchar(20)					
                         );

create table Kinocompany (
                         id_kinocompany int Identity(1,1) primary key,
						 Nazvanie_kinocompany varchar(20),
						 Strana varchar(20)
						 );

create table Films    (
                        id_films int Identity(1,1) primary key,
						id_type int
						constraint fk_id_type foreign key (id_type)
						references Type_Ekrana (id_type),
						id_kinocompany int
						constraint fk_id_kinocompany foreign key (id_kinocompany)
						references Kinocompany(id_kinocompany),
						Nazvanie_films varchar(20)
                        );
create table Seans(
                         id_seans int Identity(1,1) primary key,
						 Nazvanie_seansa varchar(20),
						 id_films int
						constraint fk_id_films foreign key (id_films)
						references Films (id_films)
						 );
create table Prodaja_Bilets (
                        id_seans int
					    constraint fk_id_seans foreign key (id_seans)
						references Seans(id_seans), 
						id_chek int
						constraint fk_id_chek foreign key (id_chek)
						references Chek(id_chek),
						id_kassa int
						constraint fk_id_kassa foreign key (id_kassa)
						references Kassa(id_kassa),
						price numeric(6),
						Kolichestvo numeric(2),
						mesto int not null, 
						CONSTRAINT id_prodaja PRIMARY KEY CLUSTERED(id_seans ASC, id_kassa ASC, id_chek ASC)

						);

/*
 drop proc insert_TypeEkrana1
 drop proc insert_kinoteatr1
 drop proc insert_kassa
 drop proc insert_check1
 drop proc insert_kinocompany
 drop proc insert_films
 drop proc insert_Seans
 drop proc insert_prodaja
 drop proc Skidka1

-- delete from Type_Ekrana
*/

CREATE PROC insert_TypeEkrana1 AS
	DECLARE @nom INT
	SET @nom=1
	WHILE @nom<=7
	BEGIN
	 INSERT INTO Type_Ekrana (Nazvanie_Ekrana)
	 VALUES ('Тип Экрана '+LTRIM(STR(@nom)))
	 SET @nom=@nom+1
	 END
GO

CREATE PROC insert_kinoteatr1 AS
	DECLARE @nom numeric(4)
	SET @nom=1
	WHILE @nom<=300
	BEGIN

	if @nom <=30
	begin
	INSERT INTO Kinoteatr(kinoteatr_name, city, region)
	VALUES ('Кинотеатр '+LTRIM(STR(@nom)),'UFA', 2)
	end
	if @nom >30 and @nom<=60
	begin
	INSERT INTO Kinoteatr( kinoteatr_name, city, region)
	VALUES ( 'Кинотеатр '+LTRIM(STR(@nom)),'Адыгея', 1)
	end
	if @nom >60 and @nom<=90
	begin
	INSERT INTO Kinoteatr( kinoteatr_name, city, region)
	VALUES ( 'Кинотеатр '+LTRIM(STR(@nom)),'Бурятия', 3)
	end
	if @nom >90 and @nom<=120
	begin
	INSERT INTO Kinoteatr( kinoteatr_name, city, region)
	VALUES ( 'Кинотеатр '+LTRIM(STR(@nom)),'Алтай', 4)
	end
	if @nom >120 and @nom<=150
	begin
	INSERT INTO Kinoteatr( kinoteatr_name, city, region)
	VALUES ( 'Кинотеатр '+LTRIM(STR(@nom)),'Дагестан', 5)
	end
	if @nom >150 and @nom<=180
	begin
	INSERT INTO Kinoteatr( kinoteatr_name, city, region)
	VALUES ( 'Кинотеатр '+LTRIM(STR(@nom)),'Ингушетия', 6)
	end
	if @nom >180 and @nom<=210
	begin
	INSERT INTO Kinoteatr( kinoteatr_name, city, region)
	VALUES ( 'Кинотеатр '+LTRIM(STR(@nom)),'Кабардинка', 7)
	end
	if @nom >210 and @nom<=240
	begin
	INSERT INTO Kinoteatr( kinoteatr_name, city, region)
	VALUES ( 'Кинотеатр '+LTRIM(STR(@nom)),'Калмыкия', 8)
	end
	if @nom >240 and @nom<=270
	begin
	INSERT INTO Kinoteatr( kinoteatr_name, city, region)
	VALUES ( 'Кинотеатр '+LTRIM(STR(@nom)),'Черкессия', 9)
	end
	if @nom >270 and @nom<=300
	begin
	INSERT INTO Kinoteatr( kinoteatr_name, city, region)
	VALUES ( 'Кинотеатр '+LTRIM(STR(@nom)),'Карелия', 10)
	end
	 SET @nom=@nom+1
	 END
GO
/*
INSERT INTO Kinoteatr(kinoteatr_name, city, region)
VALUES ('Кинотеатр', 1,1)
select*from Kinoteatr
*/

CREATE PROC insert_kassa AS
	DECLARE @nom numeric(4)
	DECLARE @id_kinoteatr numeric(2)
	SET @nom=1
	WHILE @nom<=300
	BEGIN
	 INSERT INTO Kassa(kassa_name, id_kinoteatr)
	 VALUES ('Касса'+LTRIM(STR(@nom)), @nom)
	 SET @nom=@nom+1
	 END
GO

create proc insert_check1 as
	 declare @nom numeric(5)
	 set @nom = 1
	 while @nom <=18001
	 begin
	 declare @x int
		declare @year int
		declare @month int
		declare @day int
		set @month = FLOOR(RAND()*(13-1)+1);
		if @month = 1 or @month = 3 or @month = 5 or @month = 7 or @month = 8 or @month = 10 or @month = 12
		set @day = FLOOR(RAND()*(32-1)+1);
		if @month = 2
		set @day = FLOOR(RAND()*(29-1)+1);
		if @month = 4 or @month = 6 or @month = 9 or @month = 11
		set @day = FLOOR(RAND()*(31-1)+1);
	declare @date datetime
	 if @nom<4000
	 begin
	 set @year = 2016
	 set @date  = STR(@year)+'-'+STR(@day)+'-'+STR(@month)
	 insert into Chek(Data_chek)values(@date)
	 end
	 if @nom>=4001 and @nom<=10000
	 begin
	 set @year = 2017
	 set @date  = STR(@year)+'-'+STR(@day)+'-'+STR(@month)
	 insert into Chek(Data_chek)values(@date)

	 end
	 if @nom>=10001
	 begin
	 set @year = 2018
	 set @date  = STR(@year)+'-'+STR(@day)+'-'+STR(@month)
	 insert into Chek(Data_chek)values(@date)

	 end
	 set @nom = @nom+1
	 end
GO

CREATE PROC insert_kinocompany AS
	DECLARE @nom numeric(2)
	SET @nom=1
	WHILE @nom <= 10
	BEGIN
	INSERT INTO Kinocompany(Nazvanie_kinocompany, Strana)
	VALUES ('Кинокомпания '+LTRIM(STR(@nom)),'Страна'+LTRIM(STR(@nom)))
	SET @nom = @nom + 1
	END
GO

CREATE PROC insert_films AS
	DECLARE @nom numeric(4)
	DECLARE @con numeric(1)
	DECLARE @id_kinocompany numeric(2)
	SET @nom = 1
	WHILE @nom <= 140
	BEGIN
	SET @id_kinocompany = FLOOR(RAND()*(11-1)+1);
	if @nom <=20
	begin
	INSERT INTO Films (id_kinocompany,id_type, Nazvanie_films)
	VALUES (@id_kinocompany,1,'Фильм '+LTRIM(STR(@nom)))
	end
	if @nom >20 and @nom<=40
	begin
	INSERT INTO Films (id_kinocompany,id_type, Nazvanie_films)
	VALUES (@id_kinocompany,2,'Фильм '+LTRIM(STR(@nom)))
	end
	if @nom >40 and @nom<=60
	begin
	INSERT INTO Films (id_kinocompany,id_type, Nazvanie_films)
	VALUES (@id_kinocompany,3,'Фильм '+LTRIM(STR(@nom)))
	end
	if @nom >60 and @nom<=80
	begin
	INSERT INTO Films (id_kinocompany,id_type, Nazvanie_films)
	VALUES (@id_kinocompany,4,'Фильм '+LTRIM(STR(@nom)))
	end
	if @nom >80 and @nom<=100
	begin
	INSERT INTO Films (id_kinocompany,id_type, Nazvanie_films)
	VALUES (@id_kinocompany,5,'Фильм '+LTRIM(STR(@nom)))
	end
	if @nom >100 and @nom<=120
	begin
	INSERT INTO Films (id_kinocompany,id_type, Nazvanie_films)
	VALUES (@id_kinocompany,6,'Фильм '+LTRIM(STR(@nom)))
	end
	if @nom >120 and @nom<=140
	begin
	INSERT INTO Films (id_kinocompany,id_type, Nazvanie_films)
	VALUES (@id_kinocompany,7,'Фильм '+LTRIM(STR(@nom)))
	end

	SET @nom = @nom+1
	END
GO

CREATE PROC insert_Seans AS
	declare @nom numeric(4)
	declare @id_films numeric (4)
	SET @nom=1
	WHILE @nom<=300
	BEGIN
	set @id_films = FLOOR(RAND()*(141-1)+1);
	 INSERT INTO Seans (Nazvanie_seansa, id_films)
	 VALUES ('Сеанс '+LTRIM(STR(@nom)), @id_films)
	 SET @nom=@nom+1
	 END
GO

CREATE PROC insert_prodaja AS
	DECLARE cur1 CURSOR FOR SELECT id_chek FROM Chek
	 DECLARE @id_chek NUMERIC(5)
	 DECLARE @x int
	 DECLARE @mesto int = 1
	OPEN cur1
	FETCH cur1 INTO @id_chek
	WHILE @@FETCH_STATUS = 0
	BEGIN
		if @mesto = 70
		set @mesto = 1
		declare @id_seans int
		set @id_seans = FLOOR(RAND()*(301-1)+1);
		declare @id_kassa int
		set @id_kassa = FLOOR(RAND()*(301-1)+1);
		set @x = FLOOR(RAND()*(101-1)+1);
		declare @kolvo int
		if(@x<=70)
		set @kolvo = 1
		if @x>70 and @x<=90
		set @kolvo = 2
		if @x>90
		set @kolvo = 3
		declare @price int = 199
		set @price = @price * @kolvo
		insert into Prodaja_Bilets(id_seans,id_chek,id_kassa,price,Kolichestvo,mesto)
		values(@id_seans,@id_chek,@id_kassa,@price,@kolvo, @mesto)
	 FETCH cur1 INTO @id_chek
	 set @mesto = @mesto+1
	 END
	 close cur1
	 DEALLOCATE cur1
GO

create proc Skidka1 AS
	DECLARE cur1 CURSOR FOR SELECT id_chek FROM Chek
	 DECLARE @id_chek NUMERIC(5),
			 @sumChek NUMERIC(5),
			 @coeff INT
	OPEN cur1
	FETCH cur1 INTO @id_chek
	WHILE @@FETCH_STATUS = 0
	BEGIN
	SELECT @sumChek = price FROM Prodaja_Bilets where @id_chek = id_chek
	 if @sumChek>=600 
	 set @coeff = 5
	 if @sumChek<600 and @sumChek>=400
	 set @coeff = 3
	 if @sumChek<400 and @sumChek>=200
	 set @coeff = 1
	 UPDATE Chek SET Skidka = @coeff WHERE @id_chek= id_chek
	 UPDATE Prodaja_Bilets SET price=price*(100.0-@coeff)/100.0
	 WHERE @id_chek=id_chek
	 FETCH cur1 INTO @id_chek
	 END
	 CLOSE cur1
	 DEALLOCATE cur1
GO




 exec insert_TypeEkrana1;
 GO
	select * from Type_Ekrana
	GO
 exec insert_kinoteatr1;
 GO
	select * from Kinoteatr
	GO
 exec insert_kassa;
 GO
	select * from Kassa
	GO
  exec insert_check1;
 GO
	select * from Chek
	GO
 exec insert_kinocompany;
 GO
	select * from Kinocompany
	GO
 exec insert_films;
 GO
 	select * from Films
	GO
 exec insert_Seans;
 GO
	select * from Seans
	GO
 exec insert_prodaja;
 GO
	select * from Prodaja_Bilets
	GO

-- exec Skidka1;
-- GO


 
--select * from filmsм