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
 exec insert_TypeEkrana1;

 drop proc insert_TypeEkrana1
 select*from Type_Ekrana;


 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
 exec insert_kinoteatr1
 select*from Kinoteatr

   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
 exec insert_kassa
 drop proc insert_kassa
 select*from Kassa



  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

 exec insert_check1
 delete proc insert_check
 select*from Chek


 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
exec insert_kinocompany;
select*from Kinocompany;



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
exec insert_films;
select*from Films;


 
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
 exec insert_Seans;
 select*from Seans;


 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
 exec insert_prodaja;
 select*from Prodaja_Bilets;


 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


 exec Skidka1