New Open Source Project – Room Booking System

[Tom King]

I’m pleased to announce a new open source project for a room booking system – it’s basically a backend to the excellent fullcalendar.js jQuery plugin, using cfWheels as the back end.

Features include:

• All (well most) configuration done via database settings
• Uses the DBMigrate plugin for cfWheels for easy future updates
• Uses the FullCalendar.js month/day/week views
• Allows for multiple locations, which can be filtered from the main view
• Each location can be colour coded, and have room layout options
• Allows you to bulk create events, so easy to add placeholders for repeating series
• Completely configurable via web interface, all you need to do is setup a datasource called ‘roombooking’
• Has a theme switcher so you can use/try any theme on http://bootswatch.com/
• Can send confirmation emails on a new booking

You can download the code from gitHub - https://github.com/neokoenig/RoomBooking or try a demo at http://roombooking.oxalto.co.uk.

Feel free to contribute or log a bug at https://github.com/neokoenig/RoomBooking - hopefully it’ll be useful for someone!

Please feel free to comment on the blog post - http://www.oxalto.co.uk/2013/09/new-open-source-project-room-booking-system/ - some screenshots there too.

Ta!
T

[Thorsten Eilers]

Hi Tom,
your app looks very nice and professional. Congrats.
I think I could use it to manage the use of our bathroom for my wife and daughter. ;-)
Thorsten

[Singgih Cahyono]

Wow, it just works on mobile phone. So, it is truly state of the arts.

--
You received this message because you are subscribed to the Google Groups "ColdFusion on Wheels" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cfwheels+u...@googlegroups.com.
To post to this group, send email to cfwh...@googlegroups.com.
Visit this group at http://groups.google.com/group/cfwheels.
For more options, visit https://groups.google.com/groups/opt_out.

[Tim Badolato]

Love this, great work. I also enjoyed having a look at your code. Very simple and clean.

I wish there was a place where all CFWheels based open source applications could be showcased, besides plugins.

[Tom King]

Well, there's:

http://www.riaforge.org/index.cfm?event=page.search#cfwheels_catid=1

https://www.ohloh.net/tags/cfwheels

https://github.com/search?q=cfwheels&ref=cmdform

http://cfwheels.org/sites (includes commercial sites)

but you're right, no official listing..

[Tom King]

Some may find this useful: I thought it would be good to talk through some bits of the code and explain why they're there:

http://www.oxalto.co.uk/2013/09/things-learned-from-creating-the-roombooking-system/

T

[Tom King]

For those interested , 1.01 has been released : There’s some massive changes, most noticeably the addition of user authentication, roles, permissions, logging & password resets. All documentation has been moved to https://github.com/neokoenig/RoomBooking/wiki, where you can find a breakdown of all the settings, permissions, roles and upgrading instructions.

Some may find the per role permissions system interesting, and also the password/salt hashing/encryption bits.

T

[Brad P.]

Great work, thanks for sharing! I'll have to download and play as soon as I have some free time.

[Tom King]

At the risk of banging on about my own stuff, did another blog post talking about role based permissions in a cfwheels app (i.e, this one)

See http://www.oxalto.co.uk/2013/10/things-learned-from-creating-the-roombooking-system-part-2/

Do have a gander.

Ta!
T

[Edison Esquinas]

Hi,

Thank you for the room booking system. I'm installing the system on my IIS 6.0 + IIRF 2.1 and Tomcat.

After trying 

http://myserver/rewrite.cfm?controller=wheels&action=wheels&view=plugins&name=dbmigrate  

the rewritten URL is:

"GET /rewrite.cfm/rewrite.cfm?controller=wheels&action=wheels&view=plugins&name=dbmigrate HTTP/1.1" 404 122

So I added 

RewriteBase /

To my iirf.ini file which corrects the duplication

/rewrite.cfm?controller=wheels&action=wheels&view=plugins&name=dbmigrate

Anyway I'm getting a:

Error!

Sorry, that caused an unexpected error.

Please try again later. 

When going to http://myserver/rewrite.cfm?controller=wheels&action=wheels&view=plugins&name=dbmigrate.

I'm new to cfwheels, but I made sure my IIS had a rewriting filter. Any hint or workaround for this problem?.

I appreciate any help.

[Tom King]

I've only ever used ISAPI rewrite on IIS6, not IIRF I'm afraid;

A few things spring to mind:

1) Technically speaking, you don't *need* url rewriting on (although naturally, it's usually preferable), /index.cfm?controller=wheels&action=wheels&view=plugins&name=dbmigrate accessed directly should work. Turn off URL rewriting in config/settings.cfm

2) The error you're seeing is actually a wheels error; it's probably the install error (https://github.com/neokoenig/RoomBooking/issues/13) where not having the DB installed causes a loop. This was 'my bad' and I really need to fix it. Essentially you need to comment out some stuff in applicationStart.cfm (see the git issue), run DBmigrate, then, reload the app and re-add the code.

3) Try and get the app running in design mode, not production mode using ?reload=design&password=roombooking (from memory, might be a different reload password) - that will at least mean you can see the errors being generated, rather than relying on the emails from production mode.

Let me know how you get on.T

[Edison Esquinas]

I appreciate your help Tom.

I tried #1 and it keeps showing the error screen. I also set the environment = design at config/environment but the log doesn't show anything relevant yet.

Added a <cfdump var="#exception#" abort="true"> at events/onerror.cfm onError function, and it shows: 

Message Element WHEELS.DISPATCH is undefined in a Java object of type class [Ljava.lang.String; referenced as ''

coldfusion.runtime.UndefinedElementException: Element WHEELS.DISPATCH is undefined in a Java object of type class [Ljava.lang.String; referenced as '' at coldfusion.runtime.CfJspPage.resolveCanonicalName(CfJspPage.java:1759) at coldfusion.runtime.CfJspPage._resolve(CfJspPage.java:1677) at coldfusion.runtime.CfJspPage._resolve(CfJspPage.java:1665) at cfindex2ecfm1680001962.runPage(D:\inetpub\wwwroot\RoomBooking\wheels\index.cfm:1)

Regarding #2, Issue https://github.com/neokoenig/RoomBooking/issues/13 says uncomment all onapplicationstart when installing, but everything is uncommented. Am I getting it right?. Also, I can't reach the DBmigrate link. 

Apparently I'm missing some config/settings.cfm params (?).

[Tom King]

Ok, 

so in onApplicationStart.cfm, comment out:

 <cftry>
 	<cfset _loadSettings()>
 	<cfcatch type="any">
 		<cflocation url="/rewrite.cfm?controller=wheels&action=wheels&view=plugins&name=dbmigrate">
 	</cfcatch>
 </cftry>
 
 <cfset _createInitialUser()>

Then go to  /index.cfm?controller=wheels&action=wheels&view=plugins&name=dbmigrate

You should be able to run the DBmigrate script;
If so, run it, then undo the commenting and restart the application.

Note, your app is still running in production mode - whenver you see that "friendly" error message (Which doesn't tell you anything) then you've not reloaded into design mode properly.
T

[Edison Esquinas]

Ok, after commenting those lines it gives me:

Element WHEELS.EVENTPATH is undefined in APPLICATION.

The error occurred in D:\inetpub\wwwroot\RoomBooking\wheels\events\onsessionstart.cfm: line 10 Called from D:\inetpub\wwwroot\RoomBooking\wheels\global\cfml.cfm: line 35 Called from D:\inetpub\wwwroot\RoomBooking\wheels\events\onsessionstart.cfm: line 3

8 : <cfscript> 9 : $initializeRequestScope(); 10 : $include(template="#application.wheels.eventPath#/onsessionstart.cfm"); 11 : </cfscript> 12 : </cffunction>

[Tom King]

Ok I think wheels hasn’t correctly loaded into your application scope, so you need to restart the application – possibly the easiest way to do this is to go into config/app.cfm and set an applicationname, i.e

<cfset this.name = "MyAppName">

[Edison Esquinas]

Added:

<cfset this.name = "RoomBooking"> on app.cfm

It showed:

D1 is already defined in argument scope.

Use local to define a local variable with same name.

The error occurred in D:\inetpub\wwwroot\RoomBooking\wheels\controller.cfm: line 5 Called from D:\inetpub\wwwroot\RoomBooking\controllers\Wheels.cfc: line 6 Called from D:\inetpub\wwwroot\RoomBooking\root.cfm: line 1 Called from D:\inetpub\wwwroot\RoomBooking\wheels\global\objects.cfm: line 13 Called from D:\inetpub\wwwroot\RoomBooking\wheels\events\onapplicationstart.cfm: line 119

3 : <cfinclude template="view/functions.cfm"> 4 : <cfinclude template="plugins/injection.cfm"> 5 : <cfinclude template="../#application.wheels.viewPath#/helpers.cfm">

I went to the helpers.cfm file and noticed that the function "_formatDateRange" has d1 and d2 declared as parameters as well as local variables. I changed the parameter names to d1a and d2a and its references and it works now!, I can see the DBMigrate screen.

Just so you know I'm running the application on a Coldfusion 9 server, not Railo. But I'm afraid I should move to Railo to avoid other compiler-related issues (?).

Thanks a lot Tom.

Edison.

[Tom King]

Awesome. Glad you got somewhere, sorry for the journey :)

You might come across some more CF9 stuff, I gave up on CF8 compatibility, then skipped to CF10/Railo;

But it *should* work on CF9!

Feel free to put a pull request in the repo too...

[Edison Esquinas]

Hi again,

DBMigrate is giving me a:

[Macromedia][SQLServer JDBC Driver][SQLServer]Table 'settings' does not have the identity property. Cannot perform SET operation.

When executing the second script. Searching on this error I found

The SET IDENTITY_INSERT statement can only be issued to tables with an identity column. If the table name passed to this statement does not have an identity column, this error message (Msg 8106) will be generated

I'm not sure where is the "SET IDENTITY_INSERT" being scripted or if there is a param to set this value ?.

Edison.

[Tom King]

Hmm. 

I don't use SQL Server, so I fear I'm not going to be much help here.

It's being called from /plugins/dbmigrate/adapters/MicrosoftSQLServer.cfc lines 246 - > end, probably.

"prepends sql server identity_insert on to allow inserting primary key values" according to the function hint.

http://www.sql-server-helper.com/error-messages/msg-8106.aspx

I didn't write DBMigrate, nor do I have SQL Server - sorry!

[Edison Esquinas]

No probs.

I will search for help in the CFWheels forums. BTW I downloaded a zip copy of the code so I'm not sure on how to make the pull request now. If it's ok I can send you later the files I've modified.

Edison.

[Edison Esquinas]

I commented out a couple lines in the Migration.cfc file, addRecord function:

$execute(this.adapter.addRecordPrefix(arguments.table));

$execute(this.adapter.addRecordSuffix(arguments.table));

This lines were executing the SET IDENTITY_INSERT OFF/ON sentence before and after every INSERT sentence. Since the Room Booking System tables don't declare the "id" columns as "IDENTITY" the prefix and suffix were not necessary.

Migration done. Next thing I tried was ?reload=design&password=roombooking and I got:

The value returned from the checkPermission function is not of type boolean.

If the component name is specified as a return type, it is possible that either a definition file for the component cannot be found or is not accessible.

The error occurred in D:\inetpub\wwwroot\RoomBooking\events\functions.cfm: line 113 Called from D:\inetpub\wwwroot\RoomBooking\wheels\global\cfml.cfm: line 182 Called from D:\inetpub\wwwroot\RoomBooking\wheels\controller\filters.cfm: line 125 Called from D:\inetpub\wwwroot\RoomBooking\wheels\controller\processing.cfm: line 12 Called from D:\inetpub\wwwroot\RoomBooking\wheels\dispatch\request.cfm: line 167 Called from D:\inetpub\wwwroot\RoomBooking\wheels\index.cfm: line 1 Called from D:\inetpub\wwwroot\RoomBooking\index.cfm: line 1 Called from D:\inetpub\wwwroot\RoomBooking\wheels\events\onrequest.cfm: line 1

111 : <cfargument name="permission" required="true" hint="The permission name to check against"> 112 : <cfscript> 113 : if(!checkPermission(arguments.permission)){ 114 : redirectTo(route="denied", error="Sorry, you have insufficient permission to access this. If you believe this to be an error, please contact an administrator."); 115 : }

I'm posting the issue here without having debugged too much anyway. Any suggestions Tom?.

Edison

[Tom King]

This looks familiar:

What does your events/functions.cfm checkPermission function look like?

It's quite a specific thing: how CF deals with 0 or 1: railo treats it as a boolean;

Someone else had this issue (but in CF10) and suggested this - which is what is in the repo, as it works in both railo and CF10:

<cffunction name="checkPermission" hint="Checks a permission against permissions loaded into application scope for the user" returntype="boolean">

	<cfargument name="permission" required="true" hint="The permission name to check against">

	<cfscript> 
	var retValue=0;
		if(_permissionsSetup() AND structKeyExists(application.rbs.permission, arguments.permission)){
			retValue = application.rbs.permission[arguments.permission][_returnUserRole()];
			if(retValue == 1){
				return true;
			} else {
				return false;
			}
		} 
	</cfscript>
</cffunction>

Now, in Railo, the entire function is much samller: all you need is this:

if(_permissionsSetup() AND structKeyExists(application.rbs.permission, arguments.permission)){

return application.rbs.permission[arguments.permission][_returnUserRole()];

}

So the issue is what is CF9 returning from that function?

[Tom King]

PS, just thought of something else it might be: dump application.rbs.settings and check the actual values of what's in that struct. a null field will trigger this too.

[Edison Esquinas]

Ok,

I have the latest version with the "if retValue == 1", but it doesn't seem to be reaching that point.

...

dumped application.rbs.settings and it shows 

Element RBS is undefined in APPLICATION.

I'm completely clueless on this one :).

[Tom King]

Ah, did you put the code back in in /events/onapplicationstart.cfm?

That's where the whole RBS struct gets populated with the settings + permissions.

[Edison Esquinas]

OOPS!. Sorry, fixed that.

Now It sends me to /rewrite.cfm?controller=wheels&action=wheels&view=plugins&name=dbmigrate&CFID=21000&CFTOKEN=19515035 and when I look at the users table it's empty.

I'm checking to delete some dumps I added when debugging.

[Edison Esquinas]

PS: I restarted my tomcat and IIS, and now my browser shows "This webpage has a redirect loop"

[Tom King]

Yeah, you’re back to the original install issue: if the database isn’t setup properly and DB migrate hasn’t run, it’ll throw you back to DBMigrate.

So in short, DBMigrate doesn’t seem to be working with MSSQL. Boo.

T

--
You received this message because you are subscribed to a topic in the Google Groups "ColdFusion on Wheels" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/cfwheels/dk_JbtmLRi4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to cfwheels+u...@googlegroups.com.

To post to this group, send email to cfwh...@googlegroups.com.
Visit this group at http://groups.google.com/group/cfwheels.

For more options, visit https://groups.google.com/d/optout.

[Tom King]

PS, it might be easier to take this off list to prevent annoying the rest of the cfwheels peeps :)
 

[Edison Esquinas]

Ouch that's discouraging.. 

Ultimately, I think I could try configuring the same environment as you did Tom, what are your recommended settings to run the RoomBooking app?

[Tom King]

Re: offlist comment - Not meant to be - quite the opposite - was offering to give more detailed help where I could look at your hosting environment etc in closer detail via Skype if that helps.

The environment I run it in usually is Linux + Apache/Railo 4.x + MySQL;

I do however need to get it running in a CF10 (but linux / mysql) environment soon, so I will be more actively supporting that in the future.

MSSQL is a mystery though I'm afraid :(

[Simon Allard]

All my apps are running on ACF9, IIS 7 and MSSQL 2008. I'm using your permission concept for a number apps and I do not need to return true/false with the checkPermission() function.

I'm pretty sure the database is not setup properly and the permission struct is not populated correctly.

I've used DBmigrate in the past without any problems. Though if I remember, DBmigrate is not compatible with CFWheels 1.2.

I'll try to take some time to install RoomBooking on my server to see what's the problem. Maybe I'll be able to locate the problem (if any).

A+

Simon

--
You received this message because you are subscribed to the Google Groups "ColdFusion on Wheels" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cfwheels+u...@googlegroups.com.

[Edison Esquinas]

Cool thanks. Didn't get that one :).

[Tom King]

Well, this is running on 1.2 - although to be brutally honest, there's no real reason it has to run on 1.2;

You could (quite easily) do a find and replace for renderView back to renderPage and then replace the wheels folder (and change the error email settings) and voila, it should run on 1.1.8.]

Thanks for any help Simon - I really need to sort out the installer for this one, but I made my life much more complex by having installation specific hashing for the passwords.

T

[Edison Esquinas]

Thanks, I appreciate your feedback if you get the chance to do it.

[Simon Allard]

Good morning!

I've been able to make the database work, but I had to go directly in MSSQL to make it work. 

Things I had to tweek to be able to make RoomBooking work:

- Use an old version (0.9) of DBmigrate plugin (the latest version doesn't work)

- Make some changes to migration files. limit="" seems to cause problems and primaryKey function doesn't exists in the old DBmigrate plugin.

- The permissions table contain a column named "user" which seems to be a reserved word in MSSQL and causing problem when trying to populate the permission struct. If I change the column name the error disapear but cause problem else where.

- I haven't been able to make RoomBooking work with wheels 1.2, I had to use wheels 1.1.8 to make it work.

In the end there's too much things going on for me to make it work properly. I've been able to log in with the admin creadential but the calendar was not working and I got some error messages when trying to add events. I'm sure the app must run smoothly when properly installed/configured, but after 2-3 hours I stopped trying.

The good thing is I wasn't lost in Tom's code.

A+

Simon

[Tom King]

Thank you for your monumental effort!

As you say, there's a lot of stuff there which has knock-on effects. The question is whether it's worth attempting to  back port all these fixes - I've got about 10 instances of this running in production (and have been for sometime), so must confess, a little reluctant to try and fix DBMigrate in addition to the various other issues.

Had no idea user was a reserved column name in MSSQL. Learn something new everyday.

Pleasing to hear at least the code itself was legible :) 

--
You received this message because you are subscribed to a topic in the Google Groups "ColdFusion on Wheels" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/cfwheels/dk_JbtmLRi4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to cfwheels+u...@googlegroups.com.

To post to this group, send email to cfwh...@googlegroups.com.
Visit this group at http://groups.google.com/group/cfwheels.
For more options, visit https://groups.google.com/d/optout.

--

Tom King

Web Development Consultant

+44(0)7775 926480 | oxalto.co.uk | @neokoenig | linkedIn

[Simon Allard]

No problem Tom. 

I never thanked you for your blog post about permissions (http://www.oxalto.co.uk/2013/10/things-learned-from-creating-the-roombooking-system-part-2/). I've been using your concept since and it's the best thing I've used since wheels itself. 

Thanks again!

A+

Simon

[Edison Esquinas]

Thank you for sharing your experience,

I need a room booking solution working on IIS6, CF9 and MSSQL and as a newbie to the CF world it's good to know that Tom's code is legible. I'm going to keep trying with this and see where I get.

Edison.

[Tom King]

To get you started, here's a mySQL dump:

http://pastebin.com/raw.php?i=wVpcRqKk

At the worst, you should be able to feed it into one of those MSSQL converters/wizards... Just find a way to get it into MSSQL.

FYI You'll need to delete user/1 and restart the app to recreate the admin user with correctly encrypted password.

To downgrade to 1.1.8, I think all you'll need to do is find + replace renderView to renderPage, and change/check the errorEmailAdress setting.

For Permissions:

You can just delete the 'user' column in the permissions table (or rename it) but then either delete or rename the line in /events/onapplicationstart.cfm (line 66) which references user.

This should at least get you a working app, but you'll not be in the DBMigrate workflow, so any future updates you'll have to do the DB aspect manually.

HTH

[Phord]

Reserved Words for column names:

This seems to be the cause of many frustrations, because DBMS often report obscure errors that don't indicate that a reserved word was used.

I think all the cfwheels database adapters should surround all the column names with their respective escape characters.

We don't need to know all the reserved words for all the DBMSs, just surround every column name with the escape chars anyway.

E.G. MSSQL can accept  [columnName], MYSQL can accept `columnName`, Oracle & Postgres can accept "columnName"

The MSAccess adapter plugin I wrote some time back, successfully allows reserved words as column names, so I know it can be done easily.