USE [projectofinalatestes2]
GO
/****** Object:  StoredProcedure [dbo].[insert_update_aulas]    Script Date: 15/12/2018 03:33:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[insert_aulas]
    @st_date datetime,
    @ed_date datetime,
    @id_turma INT,
    @id_form INT,
    @id_ufcd INT,
    @text VARCHAR(MAX),
    @horas FLOAT,
    @flag INT output
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
   IF(EXISTS(SELECT * FROM c_form WHERE id_formador= @id_form AND id_turma=@id_turma AND start_date=@st_date ))
   BEGIN
        SET @flag = -1 --formador já alocado para esta turma, neste bloco
   END
   ELSE IF(EXISTS(SELECT * FROM c_form WHERE id_formador= @id_form AND id_turma!=@id_turma AND start_date=@st_date))
   BEGIN
        SET @flag = -2 --formador já alocado para outra turma, neste bloco
   END
   ELSE IF(EXISTS(SELECT * FROM c_form WHERE id_formador!= @id_form AND id_turma=@id_turma AND start_date=@st_date AND end_date=@ed_date ))
   BEGIN
        SET @flag = -3 --outro formador já alocado para esta turma, neste bloco
   END
   ELSE IF(EXISTS(SELECT * FROM c_form WHERE  id_turma=@id_turma AND start_date=@st_date AND end_date!=@ed_date ))
   BEGIN
        DECLARE @tempStart datetime = (SELECT end_date FROM c_form WHERE id_turma=@id_turma AND start_date=@st_date AND end_date!=@ed_date)
        DECLARE @HORAS_TESTE FLOAT
        SET @HORAS_TESTE =(SELECT CAST(DATEDIFF(SECOND ,@tempStart, @ed_date) AS FLOAT))
        SET @HORAS_TESTE = @HORAS_TESTE/3600.0
        INSERT INTO c_form (start_date, end_date, id_formador, id_turma, id_ufcd, text ) VALUES (@tempStart, @ed_date, @id_form, @id_turma, @id_ufcd, @text)
        UPDATE turma_ufcd SET horas_lecionadas+= @HORAS_TESTE WHERE id_turma = @id_turma AND id_ufcd = @id_ufcd
        SET @flag = 1 --Inserido, mas já existia UFCD na hora anterior, adicionado com horas reduzidas
   END
   ELSE IF(EXISTS(SELECT * FROM c_form WHERE  id_turma=@id_turma AND start_date=@st_date AND end_date!=@ed_date ))
   BEGIN
        DECLARE @tempEnd datetime = (SELECT start_date FROM c_form WHERE id_turma=@id_turma AND start_date=@st_date AND end_date!=@ed_date)
        DECLARE @HORASEND_TESTE FLOAT
        SET @HORASEND_TESTE =(SELECT CAST(DATEDIFF(SECOND ,@st_date, @tempEnd) AS FLOAT))
        SET @HORASEND_TESTE = @HORAS_TESTE/3600.0
        INSERT INTO c_form (start_date, end_date, id_formador, id_turma, id_ufcd, text ) VALUES (@st_date, @tempEnd, @id_form, @id_turma, @id_ufcd, @text)
        UPDATE turma_ufcd SET horas_lecionadas+= @HORASEND_TESTE WHERE id_turma = @id_turma AND id_ufcd = @id_ufcd
        SET @flag = 2 --Inserido, mas já existia UFCD na hora seguinte, adicionado com horas reduzidas
   END
   ELSE IF(SELECT n_horas FROM ufcd WHERE id = @id_ufcd) < ((SELECT horas_lecionadas FROM turma_ufcd WHERE id_turma = @id_turma AND id_ufcd = @id_ufcd) +@horas)
   BEGIN
        SET @horas = (SELECT horas_lecionadas FROM turma_ufcd WHERE id_turma = @id_turma AND id_ufcd = @id_ufcd)+@horas-(SELECT n_horas FROM ufcd WHERE id = @id_ufcd)
        INSERT INTO c_form (start_date, end_date , id_formador, id_turma, id_ufcd, text ) VALUES (@st_date, DATEDIFF(HOUR, @horas, @ed_date), @id_form, @id_turma, @id_ufcd, @text)
        UPDATE turma_ufcd SET horas_lecionadas+= @horas WHERE id_turma = @id_turma AND id_ufcd = @id_ufcd
        SET @flag = 3 --Inserido, fim de UFCD, horas reduzidas
   END
   ELSE
   BEGIN
        INSERT INTO c_form (start_date, end_date , id_formador, id_turma, id_ufcd, text ) VALUES (@st_date, @ed_date, @id_form, @id_turma, @id_ufcd, @text)
        UPDATE turma_ufcd SET horas_lecionadas+= @horas WHERE id_turma = @id_turma AND id_ufcd = @id_ufcd
        SET @flag = 4 -- Insert normal
   END
 commit
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK

        END
END CATCH
END