-- 5.7
create procedure vloz_vyucujiciho2
@jmeno varchar(50),
@prijmeni varchar(50),
@predmet varchar(50),
@id_ucitel int OUTPUT,
@id_predmet int OUTPUT
AS
BEGIN

	SELECT @id_ucitel=u.id_ucitel FROM ucitel u
	WHERE u.jmeno = @jmeno AND u.prijmeni = @prijmeni

	IF @id_ucitel IS NULL
	BEGIN
		INSERT INTO ucitel VALUES(@jmeno, @prijmeni)
		SET @id_ucitel = IDENT_CURRENT('ucitel')
	END

	-- DECLARE @id_predmet int

	SELECT @id_predmet=id_predmet FROM predmet
	WHERE nazev = @predmet

	IF @id_predmet IS NULL
	BEGIN
		INSERT INTO predmet VALUES(@predmet)
		SET @id_predmet = IDENT_CURRENT('predmet')
	END


	INSERT INTO vyucujici VALUES(@id_ucitel, @id_predmet)
END

-- EXEC vloz_vyucujiciho 'Jan', 'Novák', 'Tělocvik'

go

create procedure body_znamka
@body int,
@znamka varchar(1) OUTPUT
AS
BEGIN
	IF(@body >= 90)
	BEGIN
		SET @znamka = 'A'
	END
	ELSE IF(@body >= 80)
	BEGIN
		SET @znamka = 'B'
	END
	ELSE IF(@body >= 70)
	BEGIN
		SET @znamka = 'C'
	END
	ELSE IF(@body >= 60)
	BEGIN
		SET @znamka = 'D'
	END
	ELSE IF(@body >= 50)
	BEGIN
		SET @znamka = 'E'
	END
	ELSE
	BEGIN
		SET @znamka = 'F'
	END
END

go

create procedure save_hodnoceni
@jmeno_u varchar(50),
@prijmeni_u varchar(50),
@jmeno_s varchar(50),
@prijmeni_s varchar(50),
@predmet varchar(50),
@body int
AS
BEGIN

	DECLARE @id_ucitel int
	DECLARE @id_predmet int
	EXEC vloz_vyucujiciho2 @jmeno_u, @prijmeni_u, @predmet, @id_ucitel OUTPUT, @id_predmet OUTPUT

	DECLARE @id_student int
	EXEC save_student @jmeno_s, @prijmeni_s, @id_student OUTPUT


	DECLARE @exists int
	SELECT @exists=count(id_student) FROM znamka
	WHERE id_student = @id_student AND id_ucitel = @id_ucitel AND id_predmet = @id_predmet
	print @exists

	IF (@exists = 0)
	BEGIN
		INSERT INTO znamka VALUES(@id_student, @id_ucitel, @id_predmet, @body)
		DECLARE @znamka varchar
		EXEC body_znamka 85, @znamka OUTPUT
		PRINT 'Znamka: '+@znamka
	END
	ELSE
	BEGIN
		PRINT 'Známka již byla zadána'
	END
END

EXEC save_hodnoceni 'Michal', 'Vrana', 'Marek', 'Mrkva', 'matematika', 75