sbdkuba

CREATE PROCEDURE GiveDiscount
AS
BEGIN
    SELECT * INTO #TempReservationsTable FROM Rezerwacja

    DECLARE
    @Id int

    WHILE (SELECT Count(*) FROM #TempReservationsTable) > 0
    BEGIN
        SELECT TOP 1 @Id = IdRezerwacja FROM #TempReservationsTable

        DECLARE
            @GuestID as INT = (SELECT IdGosc FROM Rezerwacja where IdRezerwacja = @Id),
            @Discount as INT = (SELECT Procent_rabatu FROM Rezerwacja R INNER JOIN Gosc G ON R.IdGosc = G.IdGosc WHERE IdRezerwacja = @Id),
            @HowManyReservationsPaid as INT = (SELECT COUNT(Zaplacona) FROM Rezerwacja where Zaplacona = 1 AND IdGosc = @GuestID)

        IF (@HowManyReservationsPaid >= 2)
            UPDATE Gosc SET Procent_rabatu = Procent_rabatu * 1.1 where IdGosc = @GuestID
            IF (EXISTS(SELECT Procent_rabatu FROM Gosc WHERE IdGosc = @GuestID AND (@Discount = NULL OR @Discount = 0 )))
                UPDATE Gosc SET Procent_rabatu = 10 where IdGosc = @GuestID
                              IF (EXISTS(SELECT Procent_rabatu FROM Gosc where IdGosc = @GuestID AND @Discount > 30))
                                    UPDATE Gosc SET Procent_rabatu = 30 where IdGosc = @GuestID

        Delete #TempReservationsTable Where IdRezerwacja = @Id
    END
END
GO

CREATE TRIGGER UpdateDiscount
ON dbo.Rezerwacja
FOR UPDATE
AS BEGIN

    SELECT * INTO #TempReservationsTable FROM Rezerwacja

    DECLARE
    @Id int

    WHILE (SELECT Count(*) FROM #TempReservationsTable) > 0
    BEGIN
        SELECT TOP 1 @Id = IdRezerwacja FROM #TempReservationsTable

        DECLARE
            @GuestID as INT = (SELECT IdGosc FROM Rezerwacja where IdRezerwacja = @Id),
            @HowManyReservationsPaid as INT = (SELECT COUNT(Zaplacona) FROM Rezerwacja where Zaplacona = 1 AND IdGosc = @GuestID),
            @Discount as INT = (SELECT Procent_rabatu FROM Rezerwacja R INNER JOIN Gosc G ON R.IdGosc = G.IdGosc WHERE IdRezerwacja = @Id),
            @ReservationYear as datetime = (SELECT DATEPART(year, rezerwacja.DataDo) FROM Rezerwacja where IdRezerwacja = @Id)

        IF (@HowManyReservationsPaid >= 2 AND @ReservationYear = DATEPART(year, GETDATE()))
            UPDATE Gosc SET Procent_rabatu = Procent_rabatu * (1 + 0.01 *@HowManyReservationsPaid) where IdGosc = @GuestID
            IF (EXISTS(SELECT Procent_rabatu FROM Gosc WHERE IdGosc = @GuestID AND (@Discount = 0 OR @Discount = NULL)))
                UPDATE Gosc SET Procent_rabatu = 10 where IdGosc = @GuestID

        Delete #TempReservationsTable Where IdRezerwacja = @Id
    END
END
GO