[BD] Freq 2017

/*p1-a*/
INSERT INTO a_Prova (Ano_letivo, epoca, DATA, Periodo, NPrevistoAlunos, NDisc, Estado)
SELECT Ano_letivo, 'Normal', dateadd(DAY, 7, DATA), Periodo, NPrevistoAlunos-NAprovados, NDisc, 'Normal' FROM a_Prova
WHERE Ano_letivo LIKE '2016/2017' AND epoca LIKE 'Freq'

/*p1-b*/
SELECT Desginacao, Sigla FROM a_Disciplina
INNER JOIN a_Prova ON a_Disciplina.NDisc = a_Prova.Ndisc
INNER JOIN a_SalasProva ON a_Prova.NProva = a_SalasProva.NProva
INNER JOIN a_Sala ON a_SalasProva.NSala = a_Sala.NSala
GROUP BY a_Prova.Nprova, Desginacao, Sigla, a_Prova.NPrevistoAlunos
HAVING SUM (capacidade ) < a_Prova.NPrevistoAlunos

/*p1-c*/
/*listar os docentes candidatos (ou seja, disponiveis) para vigiar uma prova, a realizar no dia seguinte
(a data corrente), no periodo da manha. deve ordenar tal lista, em ordem crescente, pelo numero total
de vigilancias ja atribuidas ao docente.*/
SELECT Nome FROM a_Docente
 LEFT  JOIN a_Vigilante ON a_Docente.NDoc = a_Vigilante.NDoc
 LEFT OUTER JOIN a_Prova ON a_Vigilante.Nprova = a_Prova.Nprova
WHERE a_Prova.Periodo IS NULL OR DATA IS NULL OR (a_Prova.Periodo NOT LIKE ' Manha'  AND Datediff(DAY, DATA, getdate())!=1)
GROUP BY a_Docente.Nome
ORDER BY COUNT(a_Vigilante.NProva) ASC

/*p2-a*/
/*Viabilizar a remoçao de uma disciplina que deixou de ser lecionada, identificada pela designação unica
@DesignDisc, matendo a informaçao constante e sem violar restriçoes de integridade. para esse efeito, é
necessario remover (primeiro) todos os dados relacionados e pela ordem correta.*/
/*um procedimento para remover a disciplina porque nao vamos devolver nada.
se fosse para devolver entao fariamos uma funçao*/
CREATE proc remover_disciplina
@DesignDisc VARCHAR(50)
AS
BEGIN
    DELETE FROM a_SalasProva WHERE Nprova IN
    (SELECT Nprova FROM a_Prova
    INNER JOIN a_Disciplina ON a_Prova.Ndisc =  a_Disciplina.Ndisc
    WHERE Desginacao LIKE @DesignDisc)

    DELETE FROM a_Prova WHERE NDisc IN
    (SELECT NDisc FROM a_Disciplina
    WHERE Desginacao LIKE @DesignDisc)

    DELETE FROM a_Lecionar WHERE NDisc IN
    (SELECT NDisc FROM a_Disciplina
    WHERE Desginacao LIKE @DesignDisc)

    DELETE FROM a_Disciplina
    WHERE Desginacao LIKE @DesignDisc
END

/*p2-b*/
/*testatroca -> funçao*/
/*faztroca -> procedimento*/

/*para faztroca*/

CREATE proc faztroca
@NDiscA INT,
@NDiscB INT,
@dataProvaA DATE,
@dataProvaB DATE,
@nomeA VARCHAR(50),
@nomeB VARCHAR(50)

AS
BEGIN
    DECLARE
        @possivel INT,
        @NprovaA INT,
        @NprovaB INT,
        @NDocA INT,
        @NDocB INT;

    /*o set atribui valores à variavel*/
    SET @NprovaA = (SELECT Nprova FROM a_Prova WHERE DATA = @dataProvaA AND NDisc = @NDiscA);
    SET @NprovaB = (SELECT Nprova FROM a_Prova WHERE DATA = @dataProvaB AND NDisc = @NDiscB);
    SET @NDocA = (SELECT NDoc FROM a_Docente WHERE Nome = @nomeA);
    SET @NDocB = (SELECT NDoc FROM a_Docente WHERE Nome = @nomeB);
    SET @possivel = (SELECT testatroca (@NDocA,@NprovaA, @NDocB, @NprovaB));

    /*verifica se é possivel fazer a troca*/
    IF @possivel = 1
    BEGIN
    /*faz a troca*/
        UPDATE a_Vigilante SET NProva = @NProvaB WHERE NDoc = @NDocA
        AND NProva = @NProvaA

        UPDATE a_Vigilante SET NProva = @NProvaA WHERE NDoc = @NDocB
        AND NProva = @NProvaB
    END;
END;

/*funçao testatroca*/

CREATE FUNCTION testatroca(
@NDocA INT,
@NprovaA INT,
@NDocB INT,
@NprovaB INT)
RETURNS INT
AS
BEGIN
    DECLARE @countA INT,
            @countB INT,
            @countAB INT,
            @countBA INT;

    SET @countA = (SELECT COUNT(*) FROM a_Vigilante WHERE Ndoc = @NDocA AND NProva = @NprovaA);
    SET @countB = (SELECT COUNT(*) FROM a_Vigilante WHERE Ndoc = @NDocB AND NProva = @NprovaB);

    /*conta quantas vigilancias existem com aquele professor e prova*/
    IF(@countA != 1 AND @countB != 1)
        RETURN 0;

    /*countAB conta provas do docente A na data da prova b */
    SET @countAB = (SELECT COUNT(*) FROM a_Vigilante JOIN a_prova ON a_Prova.NProva = a_Vigilante.Nprova
            WHERE NDoc = @NDocA AND DATA = (SELECT DATA FROM a_Prova WHERE Nprova = @NprovaB));
    SET @countBA = (SELECT COUNT(*) FROM a_Vigilante JOIN a_prova ON a_Prova.NProva = a_Vigilante.Nprova
            WHERE NDoc = @NDocB AND  DATA = (SELECT DATA FROM a_Prova WHERE Nprova = @NprovaA));


    IF(@countAB != 0 OR @countBA != 0)
        RETURN 0;

    RETURN 1;
END;

/*p2-c*/
CREATE FUNCTION estatisticas
RETURNS TABLE
AS
BEGIN
/*o primeiro declare é da tabela*/
     DECLARE @stats TABLE (Disciplina VARCHAR(50), Ano_letivo VARCHAR(50), epoca CHAR(1), n_aprovados INT, pct_aprovados INT, pct_aprovados_anoletivo_anterior VARCHAR(50));
     DECLARE @anoatual VARCHAR(50),
            @anoanterior VARCHAR(50),
            @Design VARCHAR(50),
            @Ndisc INT,
            @Epoca CHAR(1),
            @NAprov INT,
            @Nprev INT,
            @NAprovAnterior INT,
            @NprevAnterior INT;

            /*atribui os valores do ano atual e ano anterior*/
    SET @anoatual = (SELECT top 1 ano_letivo FROM a_Prova ORDER BY ano_letivo DESC);
    SET @anoanterior = (SELECT top 1 ano_letivo FROM a_Prova WHERE ano_letivo != @anoatual ORDER BY ano_letivo DESC);


    DECLARE C_stats CURSOR
    /*select valores necessarios para preencher a tabela*/
    FOR (SELECT designacao, a_Prova.NDisc, epoca, NAprovados, NPrevistoAlunos FROM a_Disciplina
    JOIN a_Prova  ON a_Disciplina.NDisc = a_Prova.Ndisc)
    OPEN C_stats
    /*vai buscar uma linha do cursor e atribui às variaveis*/
    FETCH FIRST FROM C_stats INTO @Design, @Ndisc, @Epoca, @NAprov, @Nprev
    WHILE @@FETCH_STATUS = 0
    BEGIN
    /*vai buscar o numero de aprovados do ano anterior*/
        SELECT @NAprovAnterior = NAprovados, @NprevAnterior = NPrevistoAlunos FROM a_Prova
                WHERE ano_letivo = @anoanterior AND epoca = @Epoca AND NDisc = @Ndisc;

                /*insere na tabela*/
        INSERT INTO @stats VALUES(@Design, @anoatual, @Epoca, @NAprov, @NAprov * 100 / @Nprev, @NAprovAnterior * 100 / @NprevAnterior);

       /*vai buscar a proxima linha do cursor*/
        FETCH NEXT FROM C_stats INTO @Design, @Ndisc, @Epoca, @NAprov, @Nprev
    END;

     RETURN SELECT * FROM @stats;
END;

/*comando SQL*/
EXEC estatisticas