Office365 Room Calendar Displays and ScreenCloud Dashboards

2 Aug

I setup all the rooms in the head office to have their own calendar in Office365 so users can book meetings without fear of double booking etc. This is done simply in Office365 and then from within Outlook calendars for end users.

This system works well out of the box, the users book the room and if its free, they receive an email back with confirmation.

But I really wanted a combined view of all the bookings and then quite liked the idea of each room having a small display near the door showing the status of the room with some information.

I already use ScreenCloud (which is used on some LG commercial TV’, these are also locked down in Hotel mode and have On/Off timers and then into these I have Amazon Fire Sticks with the ScreenCloud app installed) which works great as our digital signage solution.

In ScreenCloud you control all the playlists and schedules form their website and so you never need to touch the TV once it’s setup. Each TV has its own schedule, which then plays different playlists at different preset times. Each playlist contains various items and apps. You can add in any of their apps (Facebook, Twitter, RSS feeds, Weather, Clock, YouTube, BBC, Dropbox, Google Docs, PowerBi and many more, they are also constantly adding to this great list) or add webpages, images, videos etc.

You then build up your own content library of these apps, or webpages and images etc. and then put these into a playlist. Each item can of course have a set time before it moves to the next one.

We also have a weekly fire alarm test and so I have the schedule set to display a fire alarm warning image 2 minutes before until 2 minutes after the test, after this it just goes back to its normal playlist. I also have an image for the Guest WiFi along with the latest password.Note, a lot of this code was done rather quickly, as such It needs a bit of work and is more a proof of concept than a final end product.

Ultimately, I wanted to get the rooms and bookings in to a SQL database as I was then more comfortable writing code in C# in Visual Studio with that as a back end.

So first, get a list of the rooms and pop them in a table.

Next, let’s get all today’s booking for all the rooms. For this I popped all the bookings in a text file, this PowerShell then runs on schedule so it’s updated frequently.

This then creates a CSV file like this:

Which has the organiser, meeting title, start and end date time, room name and the last bit is the attendees, I later work out the number of attendees from the number of values here.

I then take this CSV and pop it into SQL

The SQL file is as follows:

So now the room booking data is all in SQL, albeit with a bit of Blu Tack and sticky tape.

I then wrote a few pages in C#. The first is essentially a view of all the bookings in all rooms for one of the large displays. There’s a bit of code to work out the duration of the meeting, the progress (in %) if it’s already started. I also have all user photos and so it displays a small thumbnail version of this on each row.

The progress column also shows a flashing “Starting soon” if the booking begins in under 8 minutes. If the booking is active, it highlights the row, if it has finished it greys it out lightly.

I then have another page that lists all the rooms and the one you select passes the room email address to the next page in the query string, so you could just go directly to the page you want.

This smaller screen then displays Booked/Available in large letters and is colour coded to make it a bit easier to see at a glance if it’s free or not. It shows a cut down version of the list of bookings, and only shows a maximum of 2. If one is in progress, it shows that one and the next, otherwise it shows the next 2.

The “Free until” time was easy too work out via SQL. The “Booked until” time however meant looping through all the following meetings until there was a gap between them.

The idea being that now I can use some old iPad Mini’s in mounts, hard wired in and set to display their page 24/7. I’m currently in the process of testing a few hardware solutions for these smaller screens and looking at available mounts, something like this perhaps:

Or one of these:

Or one of these:

What I’m after is a wall mount (in white) that ideally locks in the iPad mini (or android tablet perhaps) so that I can run the wires behind it out of view and leave it on permanently. I really need the home button covered and also a glass/perspex screen over the top so that nobody can use the touch screen. If anyone knows or has something that would fit the bill, please get in touch! (For any suppliers that might have one, I’ll happily write you a little review!)

All in all, I’m rather happy with the result. Yes the code needs a bit of tidying, but it’s worked well for a while now and seems like a good start.

I also wrote a few more pages which are displayed on the digital signage screens too.

If a user is set Out Of Office, it displays their first name and photo:

Another page selects a random member of staff (“ORDER BY NEWID()”) from AD and displays a short bio:

Another page lists the upcoming birthdays, and also has some JavaScript fireworks of it matches the current date!

Oh and one that works out if any member of staff currently has a work anniversary (same month) and displays this to:

All this information is stored in Active Directory, a few of the items are stored using the extension attributes.

There’s a META refresh tag in these pages too so that they update every minute.

I’ll update this post once I have the hardware setup for each room.