Help with QofQ to check schedule for conflicting appointments - maybe use loop?
Posted on 2006-04-27
I am building a scheduling application for test equipment, using ColdFusion MX and MS Access. I've already built the templates for adding and viewing appointments. What I'm working on now is the code to check for conflicting appointments. When a user wants to schedule time with test equipment (the appointment) they select a tester, a date, a start time and end time. I want CF to check and see if there are any conflicting appointments already scheduled.
My first query checks to see if there are any appointments on the selected date with selected equipment:
<CFQUERY NAME="qGetAppts" DATASOURCE="#variables.EVT#">
SELECT schedID, testEquip, evtDate, startTime, endTime
WHERE evtDate = #createODBCDate(CLIENT.apptDate)#
AND testEquip = #form.tester#;
If no records are returned, then I don't need to check for conflicts, and I can continue with adding the new appointment to the database. This part I know how to do.
If there are appointments on said date/equipment, then I would need to check for conflicting appointments.
Basically, I'm thinking the logic should be:
- start time cannot begin within an already scheduled appointment
- end time cannot end within an already scheduled appointment
- the new appointment cannot overlap an already scheduled appointment (meaning if an appointment is already scheduled from 1pm to 3pm with Tester A, a new appointment cannot be scheduled for Tester A from 10am to 5pm)
Here's the QofQ that I started. Should I build on this or scrap it and go with a cfloop? Either way I need help, please :) I don't know much about loops.
<cfif qGetAppts.recordCount GT 0>
<CFQUERY name="qGetConflict" dbtype="query">
WHERE <cfqueryparam cfsqltype="cf_sql_timestamp" value="#createODBCDateTime(sDateTime)#"> >= startTime
AND <cfqueryparam cfsqltype="cf_sql_timestamp" value="#createODBCDateTime(sDateTime)#"> < endTime;
<!--- if above QofQ returns any records, then code goes here to tell user they need to change their appointment day or time. This I can do. --->
<!--- Code to update database goes here. I know how to do this. --->
Hopefully I've given enough explanation.
Thanks in advance!