DAIS_C5

/**
 @Author: Sajmon
 @Date: 7.3.2012
 @DAIS: T-SQL, 1C
**/

-- 1.
CREATE TABLE Student(
    login CHAR(6) PRIMARY KEY NOT NULL,
    fname VARCHAR(30) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL);

CREATE PROCEDURE AddStudent (@p_login CHAR(6), @p_fname VARCHAR(30), @p_lname VARCHAR(50), @p_email VARCHAR(50))
AS
BEGIN
    INSERT INTO Student VALUES(@p_login, @p_fname, @p_lname, @p_email);
END
GO
EXECUTE AddStudent 'BED05','Pavel','Bednář','[email protected]';

-- 2
CREATE PROCEDURE PaddStudent (@p_login CHAR(6), @p_fname VARCHAR(30), @p_lname VARCHAR(50), @p_email VARCHAR(50), @p_vystup VARCHAR(10) OUTPUT)
AS
BEGIN
    BEGIN TRY
        INSERT INTO Student VALUES(@p_login, @p_fname, @p_lname, @p_email);
        SET @p_vystup = 'OK';
    END TRY
    BEGIN CATCH
        SET @p_vystup = 'ERROR';
    END CATCH
END

GO
DECLARE
    @v_vystup VARCHAR(10);
BEGIN
    EXECUTE PAddStudent 'GAJ027','Martin','Gajdiciar','[email protected]',@v_vystup OUT;
    PRINT @v_vystup;
END

-- 3 + 4
CREATE TABLE Teacher (
login CHAR(6) NOT NULL PRIMARY KEY,
fname VARCHAR(30) NOT NULL,
lname VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL,
department INT NOT NULL,
specialization VARCHAR(30) NULL);

CREATE PROCEDURE TStudentBecomeTeacher (@p_login CHAR(6), @p_department INT)
AS
BEGIN TRY
    BEGIN TRAN StudentTransaction
    DECLARE
        @v_fname VARCHAR(30), @v_lname VARCHAR(50), @v_email VARCHAR(50);
        SET @v_fname = (SELECT fname FROM Student WHERE login = @p_login);
        SET @v_lname = (SELECT lname FROM Student WHERE login = @p_login);
        SET @v_email = (SELECT email FROM Student WHERE login = @p_login);

        INSERT INTO Teacher(login, fname, lname, email, department) VALUES(@p_login, @v_fname, @v_lname, @v_email, @p_department);
        PRINT 'COMMIT'
        COMMIT;
END TRY
BEGIN CATCH
    PRINT 'ROLLBACK'
    ROLLBACK;
END CATCH

GO
EXECUTE StudentBecomeTeacher 'BED05','2';
EXECUTE TStudentBecomeTeacher 'DOR023','1';

-- 5
CREATE TABLE Student (
login CHAR(6) PRIMARY KEY,
fname VARCHAR(30) NOT NULL,
lname VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL,
tallness INT NOT NULL);

CREATE PROCEDURE AddStudent2 (@p_fname VARCHAR(30), @p_lname VARCHAR(50), @p_tallness INT)
AS
BEGIN
DECLARE
    @v_login VARCHAR(6), @v_email VARCHAR(50);
    SET @v_login = LOWER(SUBSTRING(@p_lname, 1, 3)) + '000';
    SET @v_email = @v_login + '@vsb.cz';

    INSERT INTO Student VALUES(@v_login, @p_fname, @p_lname, @v_email, @p_tallness);
END

GO
EXECUTE AddStudent2 'Adam','Kosinar','160';

-- 6
ALTER TABLE Student ADD isTall BIT NULL;

ALTER PROCEDURE IsStudentTall (@p_login CHAR(6))
AS
BEGIN
DECLARE
    @v_average REAL, @v_tallness INT;
    SET @v_average = (SELECT AVG(tallness) FROM Student);
    SET @v_tallness = (SELECT tallness FROM Student WHERE login = @p_login);

    IF @v_tallness < @v_average
        UPDATE Student SET isTall = 0 WHERE login = @p_login;
    ELSE
        UPDATE Student SET isTall = 1 WHERE login = @p_login;
END

GO
EXECUTE IsStudentTall 'BED05';

-- 7
CREATE FUNCTION LoginExists (@p_login CHAR(6))
RETURNS BIT
AS
BEGIN
    IF EXISTS (SELECT * FROM Student WHERE login = @p_login)
        RETURN 1;
    RETURN 0; /* nevyzaduje else, ked ho tam date vyhodi to exception, ze ako posledny prikaz ma byt return-type, ktory
                 tam pri pouziti else nevidi */
END;

GO
BEGIN
    IF (dor0023.LoginExists('DOR023') = 1)
        PRINT 'LOGIN EXISTS';
    ELSE
        PRINT 'LOGIN DOESNT EXIST';
END

-- 8
ALTER PROCEDURE AddStudent2Dynamic (@p_fname VARCHAR(30), @p_lname VARCHAR(50), @p_tallness INT)
AS
DECLARE
    @v_login VARCHAR(6), @v_email VARCHAR(50), @v_counter INT;
    SET @v_counter = 0;
    SET @v_login = LOWER(SUBSTRING(@p_lname,1, 3)) + '0' + CAST(@v_counter AS CHAR);
WHILE dor0023.LoginExists(@v_login) <> 0
    BEGIN
        SET @v_counter = @v_counter + 1;
        IF @v_counter < 100
            SET @v_login = LOWER(SUBSTRING(@p_lname,1, 3)) + '0' + CAST(@v_counter AS CHAR);
        ELSE
            SET @v_login = LOWER(SUBSTRING(@p_lname,1, 3)) + CAST(@v_counter AS CHAR);
    END
SET @v_email = @v_login + '@vsb.cz';
INSERT INTO Student(login, fname, lname, email, tallness) VALUES(@v_login, @p_fname, @p_fname, @v_email, @p_tallness);

GO
EXECUTE AddStudent2Dynamic 'Rudolf','Pecinovsky','178';

SELECT * FROM Student ORDER BY login ASC;

-- 9

CREATE PROCEDURE SetStudentTallness
AS
DECLARE @temp_login CHAR(6);
DECLARE kurzor CURSOR FOR SELECT login FROM Student;
OPEN kurzor
    FETCH NEXT FROM kurzor INTO @temp_login
    WHILE @@FETCH_STATUS = 0
        BEGIN
            EXECUTE IsStudentTall @temp_login;
            FETCH NEXT FROM kurzor INTO @temp_login /* posuvam sa na dalsi zaznam bez neho sa to zacykli */
        END
CLOSE kurzor
DEALLOCATE kurzor

GO
EXECUTE SetStudentTallness;

-- 10
CREATE PROCEDURE CopyTableStructure(@p_table_schema VARCHAR(10), @p_table_name VARCHAR(20))
AS
DECLARE kurzor CURSOR FOR SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @p_table_schema AND TABLE_NAME = @p_table_name;
DECLARE @retazec VARCHAR(500), @column_name VARCHAR(30), @column_type VARCHAR(30), @column_length INT;
SET @retazec = 'CREATE TABLE ' + @p_table_name + '_OLD (';
OPEN kurzor
    FETCH NEXT FROM kurzor INTO @column_name, @column_type, @column_length;
    WHILE @@FETCH_STATUS = 0
        BEGIN
            IF (@column_length IS NOT NULL)
                SET @retazec = @retazec + @column_name + ' ' + @column_type + '(' + CAST(@column_length AS VARCHAR) + '),';
            ELSE
                SET @retazec = @retazec + @column_name + ' ' + @column_type + ',';
        FETCH NEXT FROM kurzor INTO @column_name, @column_type, @column_length;
        END
        SET @retazec = SUBSTRING(@retazec, 1, (LEN(@retazec) - 1)) + ');';
        EXECUTE(@retazec);
CLOSE kurzor;
DEALLOCATE kurzor;
PRINT @retazec;

GO
EXECUTE CopyTableStructure '<LOGIN>','<TABLE NAME>';

-- 11
CREATE TABLE "Statistics" (
    operation VARCHAR(20) NOT NULL,
    operationCount INT NULL
);
INSERT INTO "Statistics"(operation) VALUES('INSERT');
INSERT INTO "Statistics"(operation) VALUES('UPDATE');
INSERT INTO "Statistics"(operation) VALUES('DELETE');
UPDATE "Statistics" SET operationCount = 0;

/* TRIGGER FOR INSERT STATISTICS */
CREATE TRIGGER StatisticsOperationCountForInsert
ON Student
AFTER INSERT
AS
BEGIN
    UPDATE "Statistics" SET operationCount = operationCount + 1 WHERE operation = 'INSERT';
    PRINT 'operationCount was updated';
END

/* TRIGGER FOR UPDATE STATISTICS */
CREATE TRIGGER StatistiscOperationCountForUpdate
ON Student
AFTER UPDATE
AS
BEGIN
    UPDATE "Statistics" SET operationCount = operationCount + 1 WHERE operation = 'UPDATE';
    PRINT 'operationCount was updated';
END

/* TRIGGER FOR DELETE STATISTICS */
CREATE TRIGGER StatisticsOperationCountForDelete
ON Student
AFTER DELETE
AS
BEGIN
    UPDATE "Statistics" SET operationCount = operationCount + 1 WHERE operation = 'DELETE';
    PRINT 'operationCount was updated';
END

-- 12
CREATE TABLE Kurz(
    id_kurzu CHAR(6) PRIMARY KEY NOT NULL,
    nazov VARCHAR(50) NOT NULL,
    kapacita INT NOT NULL
);

CREATE TABLE StudijniPlan(
    id_kurzu CHAR(6) NOT NULL,
    login CHAR(6) NOT NULL
);

ALTER TABLE StudijniPlan
    ADD CONSTRAINT fk_login
    FOREIGN KEY (login)
    REFERENCES Student(login);

ALTER TABLE StudijniPlan
    ADD CONSTRAINT fk_id_kurzu
    FOREIGN KEY (id_kurzu)
    REFERENCES Kurz(id_kurzu);

INSERT INTO Kurz VALUES('DAIS','Databázové a informačné systémy','5');
INSERT INTO Kurz VALUES('SWS','Správa windows systémov','10');
INSERT INTO Kurz VALUES('VIS','Vývoj informačných systémov','8');
INSERT INTO Kurz VALUES('JAT','Java technógie','20');
INSERT INTO Kurz VALUES('SWI','Úvod do softwarového inžiniestva','20');

INSERT INTO StudijniPlan VALUES('DAIS','DOR023');
INSERT INTO StudijniPlan VALUES('DAIS','USER1');
INSERT INTO StudijniPlan VALUES('DAIS','USER2');
INSERT INTO StudijniPlan VALUES('DAIS','USER3');
INSERT INTO StudijniPlan VALUES('DAIS','USER4');
INSERT INTO StudijniPlan VALUES('DAIS','USER5');
INSERT INTO StudijniPlan VALUES('DAIS','USER6');
INSERT INTO StudijniPlan VALUES('DAIS','USER7');
INSERT INTO StudijniPlan VALUES('DAIS','USER8');
INSERT INTO StudijniPlan VALUES('DAIS','USER9');
INSERT INTO StudijniPlan VALUES('VIS','USER1');
INSERT INTO StudijniPlan VALUES('VIS','USER2');
INSERT INTO StudijniPlan VALUES('VIS','USER3');
INSERT INTO StudijniPlan VALUES('VIS','USER4');
INSERT INTO StudijniPlan VALUES('JAT','DOR023');


/* POMOCNA PROCEDURKA PRE VLOZENIE X ZAZNAMOV DO TABULKY */
CREATE PROCEDURE AddDataToStudent
AS
DECLARE @login CHAR(6), @fname VARCHAR(30), @lname VARCHAR(50), @email VARCHAR(50), @counter INT;
SET @counter = 0;
WHILE @counter < 30
BEGIN
    SET @counter = @counter + 1;
    SET @login = 'USER' + CAST(@counter AS VARCHAR);
    SET @fname = 'FIRSTNAME' + CAST(@counter AS VARCHAR);
    SET @lname = 'SURNAME' + CAST(@counter AS VARCHAR);
    SET @email = 'FIRST' + CAST(@counter AS VARCHAR) + '.SURNAME' + CAST(@counter AS VARCHAR) + '@VSB.CZ';
    INSERT INTO Student(login, fname, lname, email) VALUES(@login, @fname, @lname, @email);
END
GO
EXECUTE AddDataToStudent;

CREATE TRIGGER ControlOfCapacity
ON StudijniPlan
FOR INSERT
AS
BEGIN
DECLARE @id_kurzu CHAR(6), @pocet_ludi INT;
    SET @id_kurzu = (SELECT id_kurzu FROM INSERTED);
    SET @pocet_ludi = (SELECT COUNT(*) FROM StudijniPlan WHERE (id_kurzu = @id_kurzu));
    IF @pocet_ludi > (SELECT kapacita FROM Kurz WHERE id_kurzu = @id_kurzu)
        PRINT 'WARNING MESSAGE: KAPACITA PREKROCENA';
    ELSE
        PRINT 'KAPACITA NEPREKROCENA';
END