SIBD - Prova Tipo 2013

/*
    NOTEPAD MASTER RACE

    Nao tenho a certeza de nada.
*/

/*III*/
/*
a
*/
SELECT TOP(5) Nome, NIF, Telefone, '+contas' AS [Tipo]
    FROM Clientes cl JOIN Titulares t
        ON cl.NCliente = t.NCliente
    GROUP BY Nome, NIF, Telefone
    ORDER BY COUNT(c.NConta) DESC

UNION

SELECT TOP(5) Nome, NIF, Telefone, '+movimentos' AS [Tipo]
    FROM Clientes cl JOIN Titulares t
        ON cl.NCliente = t.NCliente JOIN Contas c
            ON t.NConta = c.NConta JOIN Histórico_Movimentos hm
                ON hm.NConta = c.NConta
    WHERE DATEPART(YEAR,hm.DATA) = DATEPART(DATEDIFF(YEAR,-1,getdate())) AND DATEPART(MONTH,hm.DATA) = DATEPART(MONTH,getdate()))
    GROUP BY Nome, NIF, Telefone
    ORDER BY SUM(hm.Valor) DESC

/*
b
*/
UPDATE Clientes SET VáriosBalcões = TRUE WHERE NCliente IN (SELECT NCliente FROM Clientes cl JOIN Titulares t
                                ON cl.NCliente = t.NCliente JOIN Contas c
                                    ON t.NConta = c.NConta JOIN Balcões b
                                        ON c.N_Balcão = b.N_Balcão
                                GROUP BY(cl.NCliente)
                                HAVING COUNT(*) > 1)

/*
c
*/
SELECT cl.Nome, cl.NIF, cl.Telefone FROM Clientes cl JOIN Titulares t
    ON cl.NCliente = t.NCliente JOIN Contas c
        ON t.NConta = c.NConta JOIN Balcões b
            ON b.N_Balcão = c.N_Balcão JOIN Bancos ba
                ON ba.Código = b.CodBanco
    GROUP BY cl.Nome, cl.NIF, cl.Telefone
    HAVING COUNT(*) = (SELECT COUNT(*) FROM Bancos)

/*IV*/
/*
a
*/
CREATE TRIGGER OnInsertMov
ON Movimentos
INSTEAD OF INSERT
AS
BEGIN
    DECLARE @Opsuc BIT = 0
    IF (SELECT COUNT(*) FROM INSERTED) == 1
    BEGIN
        DECLARE @Nmov INT,
            @NCon INT,
            @Tipo CHAR,
            @Val MONEY,
            @DATA DATE

        SELECT @Nmov = NMovimento,
            @NCon = NConta,
            @Tipo = Tipo,
            @Val = Valor,
            @DATA = DATA
        FROM INSERTED

        IF Tipo = 'L' /*Levantamentos*/
        BEGIN
            IF @Val > (SELECT Saldo FROM Contas c JOIN Movimentos m
                    ON c.NConta = m.NConta
                    WHERE(c.NConta = @NCon)) /*Se não tiver saldo*/
            BEGIN
                IF (SELECT * FROM Contas WHERE NConta = @NCon*10) != NULL /*se existir*/
                BEGIN
                    INSERT INTO Movimentos VALUES (@NCon*10,@Tipo,@Val,@DATA)
                    UPDATE Contas SET Saldo = Saldo-@Val WHERE NConta = @NCon*10
                    SET @Opsuc = 1
                    PRINT 'Foi retirado o valor da transação à sua conta a prazo'
                END
                ELSE
                BEGIN
                    INSERT INTO Falhas_lev VALUES (@NCon,@DATA,@Val)
                    IF @Val > 400
                        INSERT INTO Alertas VALUES (@NCon,@DATA,@Val)
                ELSE
            END
            ELSE
            BEGIN
                INSERT INTO Movimentos VALUES (@NCon,@Tipo,@Val,@DATA)
                UPDATE Contas SET Saldo = Saldo-@Val WHERE NConta = @NCon
                SET @Opsuc = 1
            END
        END
        ELSE/*Depositos*/
            INSERT INTO Movimentos VALUES (@NCon,@Tipo,@Val,@DATA)
            UPDATE Contas SET Saldo = Saldo+@Val WHERE NConta = @NCon
            SET @Opsuc = 1
        BEGIN
        END
    END
    ELSE
        RAISERROR('Apenas um movimento por favor.',11,1)

    IF @Opsuc = 0
        PRINT 'OPERAÇÃO SEM SUCESSO'
    ELSE PRINT 'OPERAÇÃO COM SUCESSO'
END

/*
B
*/
CREATE PROCEDURE ListMov
    @NC INT
AS
BEGIN
    DECLARE @Tot INT = 0,
        @NumM INT,
        @DATA DATE,
        @Val MONEY,
        @Tipo CHAR

    PRINT 'Número de Movimento - Data - Valor'

    DECLARE C_Mov CURSOR
    FOR (SELECT TOP 10 NMovimento, DATA, Valor, Tipo FROM Movimentos WHERE NConta = @NC OREDER BY DATA DESC)
    OPEN C_Mov
    FETCH FIRST FROM C_Mov INTO @NumM, @DATA, @Val, @Tipo
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @Tipo = 'L'
            SET @Val = @Val*-1

        PRINT CONVERT(VARCHAR,@NumM) + ' - ' + CONVERT(VARCHAR,@DATA) + ' - ' + CONVERT(VARCHAR,@Val)
        SET @Tot = @Tot+1
        FETCH NEXT FROM C_Mov INTO @NumM, @DATA, @Val, @Tipo
    END
    CLOSE C_Mov
    DEALLOCATE C_Mov

    IF @Tot < 10
    BEGIN
        DECLARE C_HistMov CURSOR
        FOR (SELECT TOP 10 NMovimento, DATA, Valor, Tipo FROM Histórico_Movimentos WHERE NConta = @NC OREDER BY DATA DESC)
        OPEN C_HistMov
        FETCH FIRST FROM C_Mov INTO @NumM, @DATA, @Val, @Tipo
        WHILE @@FETCH_STATUS = 0 AND @Tot < 10
        BEGIN
            IF @Tipo = 'L'
                SET @Val = @Val*-1

            PRINT CONVERT(VARCHAR,@NumM) + ' - ' + CONVERT(VARCHAR,@DATA) + ' - ' + CONVERT(VARCHAR,@Val)
            SET @Tot = @Tot+1
            FETCH NEXT FROM C_HistMov INTO @NumM, @DATA, @Val, @Tipo
        END
        CLOSE C_HistMov
        DEALLOCATE C_HistMov
    END
END
/*
c
*/
CREATE PROCEDURE EstatMovAux
    @B INT,
    @Balc INT,
    @Mes INT,
    @Ano INT OUTPUT,
    @Per1 FLOAT OUTPUT,
    @S1 MONEY OUTPUT,
    @Per11 FLOAT OUTPUT,
    @S11 MONEY OUTPUT,
    @Per22 FLOAT OUTPUT,
    @S22 MONEY OUTPUT
AS
BEGIN
    DECLARE @Tot INT,
        @Tot2 INT
    SET @Tot = (SELECT COUNT(*) FROM Banco b JOIN Balcões bal
            ON b.Código = bal.CodBanco JOIN Contas c
                ON c.N_Balcão = bal.N_Balcao JOIN Movimentos m
                    ON c.NConta = m.NConta
            WHERE b.Código = @B AND bal.N_Balcão = @Balc DATEPART(MONTH,m.DATA) = @Mes AND DATEPART(YEAR,m.DATA) = MAX(DATEPART(YEAR,m.DATA)))

    SET @Tot2 = (SELECT COUNT(*) FROM Banco b JOIN Balcões bal
            ON b.Código = bal.CodBanco JOIN Contas c
                ON c.N_Balcão = bal.N_Balcao JOIN Movimentos m
                    ON c.NConta = m.NConta
            WHERE b.Código = @B AND bal.N_Balcão = @Balc DATEPART(MONTH,m.DATA) = @Mes-1 AND DATEPART(YEAR,m.DATA) = MAX(DATEPART(YEAR,m.DATA)))

    SELECT @Per1 = COUNT(*), @S1 = SUM(m.Valor), @Ano = MAX(DATEPART(YEAR,m.DATA)
        FROM Banco b JOIN Balcões bal
            ON b.Código = bal.CodBanco JOIN Contas c
                ON c.N_Balcão = bal.N_Balcao JOIN Movimentos m
                    ON c.NConta = m.NConta
        WHERE b.Código = @B
            AND bal.N_Balcão = @Balc
            AND DATEPART(MONTH,m.DATA) = @Mes
            AND DATEPART(YEAR,m.DATA) = @Ano)
            AND m.Tipol = 'L')

    SELECT @Per11 = COUNT(*), @S11 = SUM(m.Valor), @Ano = MAX(DATEPART(YEAR,m.DATA)
        FROM Banco b JOIN Balcões bal
            ON b.Código = bal.CodBanco JOIN Contas c
                ON c.N_Balcão = bal.N_Balcao JOIN Movimentos m
                    ON c.NConta = m.NConta
        WHERE b.Código = @B
            AND bal.N_Balcão = @Balc
            AND DATEPART(MONTH,m.DATA) = @Mes-1
            AND DATEPART(YEAR,m.DATA) = @Ano)
            AND m.Tipol = 'L')

    SELECT @Per2 = COUNT(*), @S2 = SUM(m.Valor)
        FROM Banco b JOIN Balcões bal
            ON b.Código = bal.CodBanco JOIN Contas c
                ON c.N_Balcão = bal.N_Balcao JOIN Movimentos m
                    ON c.NConta = m.NConta
        WHERE b.Código = @B
            AND bal.N_Balcão = @Balc
            AND DATEPART(MONTH,m.DATA) = @Mes
            AND DATEPART(YEAR,m.DATA) = @Ano)
            AND m.Tipol = 'D')

    SELECT @Per22 = COUNT(*), @S22= SUM(m.Valor)
        FROM Banco b JOIN Balcões bal
            ON b.Código = bal.CodBanco JOIN Contas c
                ON c.N_Balcão = bal.N_Balcao JOIN Movimentos m
                    ON c.NConta = m.NConta
        WHERE b.Código = @B
            AND bal.N_Balcão = @Balc
            AND DATEPART(MONTH,m.DATA) = @Mes-1
            AND DATEPART(YEAR,m.DATA) = @Ano)
            AND m.Tipol = 'D')

    SET @Per1 = 100*@Per1/@Tot
    SET @Per2 = 100*@Per2/@Tot
    SET @Per11 = 100*@Per11/@Tot
    SET @Per22 = 100*@Per22/@Tot
END

GO

CREATE PROCEDURE PEstatMov
    @Mes INT
AS
BEGIN
    DECLARE @EstatMov TABLE (Banco VARCHAR(50), Balcão INT, Ano INT, Mês INT, Tipo CHAR(1), PerMov INT, Tot MONEY, PerMovAnt INT, TotAnt MONEY)

    DECLARE @Cod INT,
        @Nome VARCHAR(50),
        @NBal INT,
        @Ano INT,
        @Per1 FLOAT,
        @S1 MONEY,
        @Per11 FLOAT,
        @S11 MONEY,
        @Per2 FLOAT,
        @S2 MONEY
        @Per22 FLOAT,
        @S22 MONEY

    DECLARE C_Mov CURSOR
    FOR (SELECT b.Código, b.Nome, bal.N_Balcão FROM Banco b JOIN Balcões bal
        ON b.Código = bal.CodBanco
        ORDER BY Banco b.Nome, bal.N_Balcão)
    OPEN C_Mov
    FETCH FIRST FROM C_Mov INTO @Cod,@Nome,@NBal
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC EstatMovAux @Cod, @NBal, @Mes,@Ano OUTPUT,@Per1 OUTPUT,@S1 OUTPUT,@Per2 OUTPUT,@S2 OUTPUT,@Per11 OUTPUT,@S11 OUTPUT,@Per22 OUTPUT,@S22 OUTPUT
        INSERT INTO @EstatMov VALUES(@Nome,@NBal,@Ano,@Mes,'L',@Per1,@S1,@Per11,@S11)
        INSERT INTO @EstatMov VALUES(@Nome,@NBal,@Ano,@Mes,'D',@Per2,@S2,@Per22,@S22)
        FETCH NEXT FROM C_Mov INTO @Cod,@Nome,@NBal
    END
    CLOSE C_Mov
    DEALLOCATE C_Mov

    SELECT * FROM @EstatMov
END

GO

EXEC PEstatMov 1