DAIS_riesenia

/*
napiste proceduru ktera nastavi cenu u vsech zaznamu v tabulce Train o 1,5 nasobek
vyssi pokud doba jizdy je vyssy nez 10 hodin osetrete pripad kdy neexistuje takovy zaznam v tabulce. - OK
*/

CREATE PROCEDURE SetPrice
AS
BEGIN
DECLARE kurzor CURSOR FOR (SELECT idTrain, price, startTime, endTime FROM Train);
DECLARE
    @v_time TIME, @v_idTrain INT, @v_price REAL, @v_startTime TIME, @v_endTime TIME;
OPEN kurzor;
    FETCH NEXT FROM kurzor INTO @v_idTrain, @v_price, @v_startTime, @v_endTime;
    WHILE @@FETCH_STATUS = 0
        BEGIN
            IF (DATEDIFF(HH, CAST(@v_startTime AS DATETIME), CAST(@v_endTime AS DATETIME)) > 5)
                UPDATE Train SET price = price * 1.5 WHERE idTrain = @v_idTrain;
        FETCH NEXT FROM kurzor INTO @v_idTrain, @v_price, @v_startTime, @v_endTime;
        END
CLOSE kurzor;
DEALLOCATE kurzor;
END

EXECUTE SetPrice;

/*
napis funkci s parametrem idtrainride, která vrací boolean true, jestlize je ve volaném vlaku jak
strojvedouci, pruvodci, tak stejny pocet stevardu, jako je vagonů
*/

ALTER FUNCTION CheckStaff(@p_idTrainRide INT)
RETURNS BIT
AS
BEGIN
DECLARE kurzor CURSOR FOR (SELECT S.position FROM TrainRide AS T JOIN TrainCrew AS Tr ON (T.idTrainRide = Tr.idTrainRide)
          JOIN Staff AS S ON (Tr.idStaff = S.idStaff) WHERE T.idTrainRide = @p_idTrainRide);
DECLARE @v_pos VARCHAR(1), @v_strojveduci BIT, @v_sprievodci BIT, @v_steward BIT, @v_count INT;
    SET @v_strojveduci = 0;
    SET @v_sprievodci = 0;
    SET @v_steward = 0;
    SET @v_count = 0;
OPEN kurzor;
    FETCH NEXT FROM kurzor INTO @v_pos;
    WHILE @@FETCH_STATUS = 0
        BEGIN
            IF (@v_pos = 'd')
                SET @v_strojveduci = 1;
            ELSE IF (@v_pos = 'g')
                SET @v_sprievodci = 1;
            ELSE
                BEGIN
                    SET @v_steward = 1;
                    SET @v_count = @v_count + 1;
                END
        FETCH NEXT FROM kurzor INTO @v_pos;
        END
CLOSE kurzor;
DEALLOCATE kurzor;
IF (@v_strojveduci = 1 AND @v_sprievodci = 1 AND @v_steward = 1
            AND @v_count = (SELECT T.coachCount FROM Train AS T JOIN TrainRide AS Tr ON (T.idTrain = Tr.idTrain) WHERE Tr.idTrainRide = @p_idTrainRide))
            RETURN 1;
        RETURN 0;
END

BEGIN
    IF (dor0023.CheckStaff('1') = 1)
        PRINT 'EXISTS';
    ELSE
        PRINT 'NON EXISTS';
END

/*
proceduru zmenCenu (jmenoStanice1, jmenoStanice2, percentage)
ktorá mala zvýšiť alebo znížiť cenu jízdneho o zadané percento(percentage)
pre danú vlakovú trasu a malo vypísať počet zmenených záznamov.
*/

ALTER PROCEDURE ChangePrice(@p_idStation INT, @p_idStation1 INT, @p_percentage INT)
AS
BEGIN
DECLARE kurzor CURSOR FOR (SELECT idTrain, price FROM Train WHERE idStation = @p_idStation AND idStation1 = @p_idStation1);
DECLARE
    @v_price REAL, @v_idTrain INT, @v_count INT;
    SET @v_count = 0;
BEGIN TRY
    OPEN kurzor;
        FETCH NEXT FROM kurzor INTO @v_idTrain, @v_price;
        WHILE @@FETCH_STATUS = 0
            BEGIN
                UPDATE Train SET price = ROUND((price + ((@v_price * @p_percentage) / 100)),1) WHERE idTrain = @v_idTrain;
                SET @v_count = @v_count + 1;
            FETCH NEXT FROM kurzor INTO @v_idTrain, @v_price;
            END
            PRINT 'Pocet zmenenych zaznamov: ' + CAST(@v_count AS VARCHAR);
    CLOSE kurzor;
    DEALLOCATE kurzor;
END TRY
BEGIN CATCH
    PRINT 'Zaznam neexistuje';
    CLOSE kurzor;
    DEALLOCATE kurzor;
END CATCH
END

EXECUTE ChangePrice '1','2','40';


/*
UDBS Trigger, mělo se při INSERT INTO Staff automaticky dát jobStart a průměrné salary
pro všechny se stejným povoláním (d, g, s)
*/
CREATE TRIGGER BeforeStaff
ON Staff
AFTER INSERT
AS
DECLARE
    @v_avg REAL, @v_pos VARCHAR(1), @v_jobStart DATETIME;
    SET @v_pos = (SELECT position FROM INSERTED);
    SET @v_jobStart = (SELECT jobStart FROM INSERTED);
    SET @v_avg = (SELECT AVG(salary) FROM Staff WHERE position = @v_pos);
BEGIN
    UPDATE Staff SET jobStart = @v_jobStart, salary = @v_avg WHERE position = @v_pos;
    PRINT 'Statement(s) was updated.';
END

/*
1. vytvorte proceduru customerstats s paramentrem email typu varchar ktora na dbms output
vypise statistiku vyuziti jednotlivych spojov zakaznikom urcenym emailom. vzor vystupu :

customer “[email protected]” has used
train no. 231 : 3 TIMES
train no. 256 : 2 TIMES
*/
ALTER PROCEDURE CustomerStats(@p_email VARCHAR(50))
AS
BEGIN
DECLARE kurzor CURSOR FOR (SELECT Tr.idTrain, Tr.name, COUNT(*) AS Pocet FROM [User] AS U
                          JOIN Reservation AS R ON (U.idUser = R.idUser)
                          JOIN TrainRide AS T ON (R.idTrainRide = T.idTrainRide)
                          JOIN Train AS Tr ON (T.idTrain = Tr.idTrain)
                          WHERE (U.email = @p_email) GROUP BY Tr.idTrain, Tr.name);
DECLARE @v_idTrain INT, @v_name VARCHAR(10), @v_count INT;
OPEN kurzor;
    FETCH NEXT FROM kurzor INTO @v_idTrain, @v_name, @v_count;
    PRINT 'User ' + @p_email + ' has used: ';
    WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT 'Train ' + @v_name + ' (ID: ' + CAST(@v_idTrain AS VARCHAR) + ') ' + CAST(@v_count AS VARCHAR) + ' times.';
        FETCH NEXT FROM kurzor INTO @v_idTrain, @v_name, @v_count;
        END
CLOSE kurzor;
DEALLOCATE kurzor;
END

EXECUTE CustomerStats '[email protected]';

/*
trigger ktory v pripade ze sa niekto pokusi zarezervovat si miesto v
neexistujucom vagone(v rezervaci uvede vagon s vyssim cislom ako pocet vagonov),
vypise na dbms output chybouvou hlasku a vyhodi uzivatelsku vynimku.
Pote napiste ulozenu proceduru Reserve s parametry idTrainRide, coachnumber ,
seatnumber a id Customer ktora vlozi do tabulky reservation exception.
*/
CREATE TRIGGER CheckReservation
ON Reservation
FOR INSERT
AS
BEGIN
DECLARE @v_coachNumber INT, @v_idTrainRide INT, @v_coachCount INT;
    SET @v_coachNumber = (SELECT coachNumber FROM INSERTED);
    SET @v_idTrainRide = (SELECT idTrainRide FROM INSERTED);
IF (@v_coachNumber > (SELECT DISTINCT Tr.coachCount FROM Reservation AS R
                      JOIN TrainRide AS T ON (R.idTrainRide = T.idTrainRide)
                      JOIN Train AS Tr ON (T.idTrain = Tr.idTrain) WHERE R.idTrainRide = @v_idTrainRide))
    PRINT 'Vagon neexistuje.';
ELSE
    PRINT 'Vagon existuje.';
END

/*
Přidejte do tabulky Reservation atribut confirm, který bude cizím klíčem na tabulku Staff a který může být NULL.
Nastavení atributu znamená, že rezervace uživatele byla potvrzena daným člověkem z tabulky Staff.
Napište uloženou funkci CheckStaff(p idUser Integer, p idTrainRide Integer), která bude vracet true,
pokud všechny rezervace pro p idTrainRide uživatele p idUser byly potvrzeny nějakým člověkem, který je v posádce vlaku.
V opačném případě vraťte false.
*/

ALTER TABLE Reservation ADD confirm INT NULL;

ALTER TABLE Reservation
ADD CONSTRAINT fk_staff
FOREIGN KEY (confirm)
REFERENCES Staff(idStaff);

CREATE FUNCTION CheckStaffFunction(@p_idUser INT, @p_idTrainRide INT)
RETURNS BIT
AS
BEGIN
DECLARE kurzor CURSOR FOR (SELECT confirm FROM Reservation WHERE idUser = @p_idUser AND idTrainRide = @p_idTrainRide);
DECLARE
    @v_result BIT, @v_confirm INT;
OPEN kurzor;
    FETCH NEXT FROM kurzor INTO @v_confirm
    WHILE @@FETCH_STATUS = 0
        BEGIN
            IF (@v_confirm IS NULL)
                SET @v_result = 0;
            ELSE
                SET @v_result = 1;
        FETCH NEXT FROM kurzor INTO @v_confirm;
        END
CLOSE kurzor;
DEALLOCATE kurzor;
IF (@v_result = 1)
    RETURN 1;
RETURN 0;
END

BEGIN
    IF (dor0023.CheckStaffFunction('1','1') = 1)
        PRINT 'TRUE';
    ELSE
        PRINT 'FALSE';
END


CREATE PROCEDURE PocetJizd
AS
BEGIN
DECLARE kurzor CURSOR FOR SELECT S.idStaff, S.fname, S.lname, COUNT(S.idStaff) AS Pocet FROM Staff AS S
                           JOIN TrainCrew AS Tr ON (S.idStaff = Tr.idStaff) GROUP BY S.idStaff, S.fname, S.lname ORDER BY Pocet DESC;
DECLARE
    @v_idStaff INT, @v_fname VARCHAR(30), @v_lname VARCHAR(50), @v_count INT, @v_max INT, @v_topWorkerId INT;
    SET @v_max = 0;
OPEN kurzor;
    FETCH NEXT FROM kurzor INTO @v_idStaff, @v_fname, @v_lname, @v_count;
    WHILE @@FETCH_STATUS = 0
        BEGIN
            IF (@v_count > @v_max)
                BEGIN
                    SET @v_max = @v_count;
                    SET @v_topWorkerId = @v_idStaff;
                END
            PRINT @v_fname + ' ' + @v_lname + ' ' + CAST(@v_count AS VARCHAR);
        FETCH NEXT FROM kurzor INTO @v_idStaff, @v_fname, @v_lname, @v_count;
        END
CLOSE kurzor;
DEALLOCATE kurzor;
DECLARE tKurzor CURSOR FOR SELECT T.idTrain FROM Staff AS S JOIN TrainCrew AS Tr ON (S.idStaff = Tr.idStaff)
                           JOIN TrainRide AS T ON (Tr.idTrainRide = T.idTrainRide) WHERE Tr.idStaff = @v_topWorkerId;
DECLARE
    @v_idTrain INT, @v_name VARCHAR(30);
    SET @v_name = (SELECT fname FROM Staff WHERE idStaff = @v_topWorkerId);
OPEN tKurzor;
    FETCH NEXT FROM tKurzor INTO @v_idTrain;
    PRINT 'TOP EMPLOYER: ' + @v_name;
    PRINT 'RIDES:';
    WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT 'TRAIN WITH ID: ' + CAST(@v_idTrain AS VARCHAR);
        FETCH NEXT FROM tKurzor INTO @v_idTrain;
        END
CLOSE tKurzor;
DEALLOCATE tKurzor;
END

EXECUTE PocetJizd;


/*
Vložte trigger nad tabulkou TrainCrew, Trigger bude při pokusu vložit nebo upravit záznam
v této tabulce kontrolovat, jestli může být takovýto záznam vytvořen,
aby nebyly porušeny podmínky ze zadaání: Každý vlak má svogji posádku přičemž
posádku může tvořit jeden strojvůdce (Staff.position=’d’), jeden průvodčí (Staff.position=’g’) a
na každý vagon připadaá jeden palubní stevard (Staff.position=’s’).
Takže, například aby jeden vlak neměl 2 strojvůdce. Pokud se záznam do tabulky povede vytvořit,
trigger vypíše an obrazovku OK, pokud ne, trigger vypíše záznam nelze vložit, protože tento vlak již
 obsahuje posádku na pozici …(pozice vlkádaného záznamu).
*/
ALTER TRIGGER CheckStaffForTrain
ON TrainCrew
FOR INSERT
AS
BEGIN
DECLARE
    @v_idTrainRideFromInserted INT, @v_idStaffFromInserted INT, @v_positionFromInserted VARCHAR(1), @v_coachCount INT, @v_countOfStewards INT;
    SET @v_idTrainRideFromInserted = (SELECT idTrainRide FROM INSERTED);
    SET @v_idStaffFromInserted = (SELECT idStaff FROM INSERTED);
    SET @v_countOfStewards = 0;
    SET @v_positionFromInserted = (SELECT position FROM Staff WHERE idStaff = @v_idStaffFromInserted);
    SET @v_coachCount = (SELECT DISTINCT coachCount FROM TrainCrew AS Tr
                         JOIN TrainRide AS T ON (Tr.idTrainRide = T.idTrainRide)
                         JOIN Train AS Tra ON (T.idTrain = Tra.idTrain) WHERE Tr.idTrainRide = @v_idTrainRideFromInserted);

DECLARE kurzor CURSOR FOR SELECT S.position FROM Staff AS S
                          JOIN TrainCrew AS Tr ON (S.idStaff = Tr.idStaff) WHERE idTrainRide = @v_idTrainRideFromInserted;
DECLARE
    @v_position VARCHAR(1);
OPEN kurzor;
    FETCH NEXT FROM kurzor INTO @v_position;
    WHILE @@FETCH_STATUS = 0
        BEGIN
            IF (@v_position = @v_positionFromInserted AND @v_position = 'd')
                PRINT 'Vlak uz ma svojho strojvodca!';
            ELSE IF (@v_position = @v_positionFromInserted AND @v_position = 'g')
                PRINT 'Vlak uz ma svojho sprievodciho!';
            ELSE IF (@v_position = @v_positionFromInserted AND @v_position = 's')
                SET @v_countOfStewards = @v_countOfStewards + 1;
        FETCH NEXT FROM kurzor INTO @v_position;
        END
        IF (@v_countOfStewards > @v_coachCount)
            PRINT 'Vlak uz ma potrebny pocet stewardov!';
CLOSE kurzor;
DEALLOCATE kurzor;
END

INSERT INTO TrainCrew VALUES('9','2');