Approved event not showing on calendar or list view

[yiengly]

Hi,

I have created an event which is approved but it is not showing up on the calendar. If I use the list view, the following message is returned: Sorry!, No events returned for that date range.

However I know there are approved events. I have checked the logs (from Settings menu) and it says the event was successfully created. I check the database, and the event has status of 'approved'.

Could you please provide some guidance. Thanks.

[neokoenig]

This is fixed in the master branch; I think it was the american vs UK style dates I was using in the datepicker (so it would work for say, 1/1/2016, but 31/1/2016 etc ) - Exactly which version are you using? the fix is in here: https://github.com/neokoenig/RoomBooking/commit/c09bac06551ca1f4ed11fce19e559c63b4b75260

[yiengly]

We are on version 1.2.

[neokoenig]

Can you confirm what date format is being passed through the URL? It should work like this - http://roombooking.oxalto.co.uk/index.cfm?filterActive=1&controller=Bookings&action=list&datefrom=01+Sep+2015&dateto=01+Jan+2016&q=&status=

As you can see the version at http://roombooking.oxalto.co.uk/ works as expected (which is running the latest master branch code - i.e, 1.2.1 in the releases section).

If you did manage to get 1.2 as opposed to 1.2.1, the date format in the URL and date Picker will probably be wrong.

[yiengly]

This is what is passed through the URL for the list filtering:

index.cfm?filterActive=1&controller=Bookings&action=list&datefrom=01+Feb+2016&dateto=02+Mar+2016&q=&status=

Selections: Date from: 01 Feb 2016 Date to: 02 Mar 2016

Yieng

[neokoenig]

hmm. Looks ok. If you add:

<cfdump var="#events#"><cfabort>

to the top of /views/bookings/list.cfm, what do you get?

[yiengly]

SQL:

SELECT locations.id,locations.name,locations.description,locations.class,locations.colour,locations.building,locations.layouts,events.id AS eventid, events.title, events.start, events.endDate, events.allday, events.url, events.className, events.description AS eventdescription, events.locationid, events.contactname, events.contactno, events.contactemail, events.layoutstyle, events.createdat,events.updatedat, events.deletedat,events.status FROM locations LEFT OUTER JOIN events ON locations.id = events.locationid AND events.deletedat IS NULL WHERE events.endDate > ? AND events.start < ? ORDER BY events.start ASC

SQL PARAMETERS:

array 1 2016-02-01 00:00:00.0 2 2016-03-02 23:59:59.0

[neokoenig]

Again - looks fine; Are you saying that if you run that SQL against the database directly, it returns results, but in context, it doesn't?

[yiengly]

It returns nothing if I run the query on the database side directly using MS SQL Server Management Studio.

I have events entered for 1 February 2016.

[yiengly]

In the events table, the end date is null when the event is inserted. start date is ok. Here is a sample from my test event entries:

title start endDate Test 2016-02-01 17:50:00.000 NULL Test Event 2016-02-01 00:00:00.000 NULL Multiple day event 2016-02-01 00:00:00.000 NULL

if I remove the conditions for the end date, it will return the correct events.

[yiengly]

I think the problem is that I changed 'end' to 'endDate' but the model still uses 'end'. So when a new event is created, this value is not saved because the database table has 'endDate' but no 'end' field for the events table.

If I revert to use 'end', then on new event inserts it needs to be escaped as 'end' is a reserved word in MS SQL Server. [end] will be ok when naming the column in SQL Server, but I don't think you can specify that in the model in CFWheels.

So another options is to replace 'end' with 'enddate' for example. In this case I would need to replace it application wide, including in the model (not just the files you initially advised).

I would be happy to use the name you plan to use for the replacement of 'end' (i.e. event end date) in future version so I don't have to make custom changes every time there is a version upgrade.

We can't use the application until this issue is resolved. So I would need your help on this. Thanks for your help to date.

Yieng

[neokoenig]

Ah ok; I remember you've done lots of changes to get it to work with MSSQL; what's happening is that if you don't set an end Date when creating the booking, the model (https://github.com/neokoenig/RoomBooking/blob/master/models/Event.cfc ) tries to add a default one hour to generate the end date automatically - events need an end date!

So in models/Event.cfc, alter this

/**
    *  @hint If there's no end date, add a default end date 1hour into future
    */
    public void function checkDates() {
        // Have a double check for a start date
        if(!isDate(this.start)){
            this.start=now();
        }
        if(!isDate(this.end)){
            // Bookings are for 1 hour by default, so increment if not passed in
            this.end=dateAdd("h", 1, this.start);
        }
        // Don't allow end date to be before start date
        // We check for -1 to allow for end dates on the same day
        if((DateCompare("#this.end#", "#this.start#")) EQ -1 ){
             addError(property="end", message="End Date can not be before Start Date.");
        }
    }

So that this.end is this.endDate (or whatever you've set your end date column name to be). Also, make the endDate column not allow null values. You may need to change this.start to this.startdate too.

[yiengly]

Thanks Tom. I will give that a try and see how it goes.

[yiengly]

Tom,

Seems ok now. But I had to change it in many files. These were the files where I had to change 'end' to 'endDate' and 'event-end' to 'event-endDate':

•/controllers/eventdata.cfc •/controllers/Booking.cfc •/controllers/Api.cfc •/views/eventdata/_details.cfm •/models/Event.cfc •/views/bookings/ _form.cfm •/views/email/bookingNotify.cfm •/javascripts/main.js •/javascripts/rbs.js •/javascripts/rbs.min.js

I have changed /views/bookings/ _form.cfm on line 36 to use 'endDate' instead of 'end', i.e. [field id='endDate']

Am I correct to say that I also need to change 'params.end' to 'params.endDate' in /controllers/Eventdata.cfc and /controllers/Resources.cfc?

[yiengly]

Tom,

I know you've a very busy schedule, but is there any chance the booking event end date property 'end' could be rename in the code base to something else in a future release as 'end' is a reserve word in MS SQL Server.

There would be a lot of places in the code base to replace them, not to mention that we have to do this every time the room booking system has a new release.

This is a great product so it would be a pity if we have to find an alternative. Also I am sure the booking event end date property 'end' rename would be most welcome for those of us on MS SQL Server.

[neokoenig]

Yep, it's on my list for 1.3; Because 1.3 is a major change, I'll probably do a release candidate first so you can test it for me

[yiengly]

Thanks Tom! Much appreciated.