Gebruikershulpmiddelen

Site-hulpmiddelen


custom_reservatie_check

Custom check voor reservaties

In het geval dat standaard Club Planner niet voldoet voor de noden van een klant op gebied van reservaties toelaten kan je dit op deze manier oplossen. Stop een database trigger met de custom check in de database op de tabel vd reservaties STMEMBERSET, 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

Voorbeeld vd tekst die de klant ziet bij het afgaan vd error:

custom_reservatie_check.txt · Laatst gewijzigd: 2017/09/29 09:40 door kristof