Differences

This shows you the differences between two versions of the page.

--- custom_reservatie_check [2017/09/28 23:57]
kristof
+++ — (current)
@@ Line 1: / Line 1: @@
-====== Custom check voor reservaties ======
-Stop een database trigger met de custom check in de database, de error die hierin wordt geraised zal netjes getoond worden in alle applicaties.
-**Voorbeeld 1: Niet op dag zelf laten reserveren**
-CREATE TRIGGER [Member].[TRMEMBERSET_CHECK_RESERVATION] ON [Member].[STMEMBERSET] FOR INSERT
-AS
-DECLARE @member_id int
-DECLARE @count_reserverations int
-DECLARE @seq int
-DECLARE @reservation_date datetime
-DECLARE @settype int
-BEGIN
-select @member_id = member_id from inserted
-select @seq = set_sequence_no from inserted
-select @settype = log_type from inserted
-select @reservation_date = b.start_date
-from inserted a, planner.stcalendar b
-where a.cal_id = b.cal_id
-IF
-convert(datetime, floor(convert(float, @reservation_date))) <= convert(datetime, floor(convert(float, getdate())))
-AND @settype <> 0
-BEGIN
-        RAISERROR ('Niet toegelaten van te reserveren op de dag zelf.', 16, 1)
-        DELETE from member.stmemberset where set_sequence_no = @seq
-        RETURN
-    END
-END
-**Voorbeeld 2: max 1 reservatie per locatie/dag en max 3verschillende dagen per week **
-Create TRIGGER [Member].[TRMEMBERSET_CHECK_RESERVATION] ON [Member].[STMEMBERSET] FOR INSERT
-AS
-DECLARE @member_id int
-DECLARE @count_reserverations int
-DECLARE @seq int
-DECLARE @reservation_date datetime
-DECLARE @settype int
-DECLARE @room_id int
-DECLARE @weekstart datetime
-DECLARE @WeekEnd datetime
-BEGIN
-select @member_id = member_id from inserted
-select @seq = set_sequence_no from inserted
-select @settype = log_type from inserted
-select @reservation_date = b.start_date
-from inserted a, planner.stcalendar b
-where a.cal_id = b.cal_id
-select @room_id = b.room_id
-from inserted a, planner.stcalendar b
-where a.cal_id = b.cal_id
-select @count_reserverations = (select count(*)
-from member.stmemberset a, planner.stcalendar b
-where convert(date, b.start_date) = convert(date, @reservation_date)
-and a.member_id = @member_id
-and b.cal_group_id = 1
-and b.room_id = @room_id
-and a.cal_id = b.cal_id
-and a.cancelled = 0)
-IF @count_reserverations > 1
-BEGIN
-    RAISERROR ('Niet toegelaten om meer dan 1 reservatie per toestel te doen per dag.' , 16, 1)
-    DELETE from member.stmemberset where set_sequence_no = @seq
-    RETURN
-END
-SELECT @weekstart = DATEADD(day, DATEDIFF(day, 0, @reservation_date) /7*7, 0)
-SELECT @WeekEnd =   DATEADD(day, DATEDIFF(day, 6, @reservation_date-1) /7*7 + 8, 6)
-select @count_reserverations =  (select count(dt.x) from (select convert(date, b.start_date) x
-from member.stmemberset a, planner.stcalendar b
-where convert(date, b.start_date) >= @weekstart and  convert(date, b.start_date) < @WeekEnd
-and a.member_id = 1
-and b.cal_group_id = 1
-and a.cal_id = b.cal_id
-and a.cancelled = 0
-group by convert(date, b.start_date)) dt)
-IF @count_reserverations > 3
-BEGIN
-    RAISERROR ('Niet toegelaten op meer dan 3 verschillende dagen per week te reserveren.' , 16, 1)
-    DELETE from member.stmemberset where set_sequence_no = @seq
-    RETURN
-END
-END

API WIKI

User Tools

Site Tools

Differences

Page Tools