Question

Photo of Hunter Goolsbey

0

Room Management Add-In tool: Querying Start Date - Issues

2

Hello Rock Community!

I am looking for more information on the RoomManagement add-in, specifically in the area of querying the Scheduled start date for a given occurrence in a reoccurring schedule.


To preface a little, my code will go into a workflow that will eventually query a "Daily" and "Tomorrow's" report for our facilities team.


Recently I have used "EffectiveStartDate" but this only stores the first start date of a reoccurring event. (EX: if weekly art class begins this thursday, querying EffectiveStartDate for that following thursday, returns the first instance only.

My goal is to query the Individual Start Date for each reoccurring-scheduled-item.


To work around this, I have tried using the following SQL code:


DECLARE @i INT

SET @i = -1

WHILE @i <= 52

BEGIN


Select WeeklyDayOfWeek, EffectiveStartDate,

CASE

   WHEN Schedule.WeeklyDayOfWeek IS NOT null

    THEN CAST(DATEADD(day, -7*@i, CAST(GETDATE() as Date)) as Date)

    ELSE CAST(Schedule.EffectiveStartDate as Date)

    END EventStart

INTO #TempTable

FROM Schedule

SET @i = @i + 1


DROP TABLE IF EXISTS #TempTable

END



My desire is to iterate through weekly-occurring events, by subtracting 7 from the current date.

Is there a better solution out there to replace a While(If()) nested statement in SQL?

Or, would it be more time saving to create a new table within the DB to begin importing those dates and call them directly from a stored table?


Thank you in advance for your help,

-Hunter

  • Photo of Sarah Boota

    0

    Hi Hunter, 

    Sarah Boota on behalf of BEMA Software services here.  There's not really a clean way to get occurrence dates in SQL because there are no tools in SQL to loop through the calendar string.

    We'd recommend, if you're workflows, to use the GetReservationOccurrences API call in a Web Request API action, and loop through the results. Both BEMA room reservation versions should have this.  

    https://bema-software.atlassian.net/wiki/external/573898796/ZWQzZTIwYTMyMWUxNGYyMmJiYzljYmJmOTMyYzljMzM?atlOrigin=eyJpIjoiNzQ0MjhiMDViNDE1NDVlYmE3Y2U2MzFlOTY1ZDQ2MDciLCJwIjoiYyJ9

    Thanks!

    Sarah Boota

  • Photo of Hunter Goolsbey

    0

    Sarah,

    Thank you very much for your help and the link to BEMA's Room Management Documentation.

    My apologies it took a while to circle back on this.


    I ditched the SQL from the last message in order to query the Room Management API, per BEMA documentation.  Unfortunately, I have not found a good way to do the following:

    1) Pass the .ROCK='...' cookie (received in a POST request to "https://rock.reallifeministries.com/api/Auth/Login") into the following GET request from a Rock workflow via WebRequest: https://rock.reallifeministries.com/api/Reservations/GetReservationOccurrences?startDateTime?EndDateTime

    2) Effectively yield the response as a PDF using the To_PDF Action.


    Here is a sample of my main Lava workflow.  

    <head>    

            <style></style>

        </head>

        <body>

                {% webrequest url:'https://rock.[...].com/api/Auth/Login' method:'POST' body:'{"Username" : {{username}}, "Password": {{pass}}, "Persisted": "true"}' requestcontenttype:'application/json' %}

                {% assign cooky= '.ROCK' | ReadCookie %}

                {{ results | ToJSON }}

                

                

            {% webrequest url:'https://rock.[...].com/api/Reservations/GetReservationOccurrences?startDateTime?EndDateTime' headers:'Cookie:.ROCK={{ cooky }}'%}

            

            {{'.ROCK' | WriteCookie:cooky}}

                <strong>Attribute Key</strong>

                {{ results | ToJSON }}

                <ul>

                    {% for item in results %}

                     <li>

                            <strong>{{ item.ReservationType.Name }}</strong><br/>

                            {{ item.ReservationId }}

                        </li>

                    {% endfor %}

                </ul>

                {% endwebrequest %}

            {% endwebrequest %}

            {{ Workflow | Attribute:'AttributeKey' }}

        </body>

    </html>


    I'm still familiarizing myself with Lava syntax as well.  I would greatly appreciate any help on this issue.

    Thank you!

    -Hunter