asked on

ColdFusion help

I need to update the database based on the booking. It turns out to be recursive call.

I have the schedule if somebody book a car (car can be at more than 1 location, but once it booked it attached to the booked location) it will attache the next contiguous session to the same location (contiguous considered a session withing 59 minutes from start and the end). I have the code but I think I need a recursion here

<cfif isdefined("url.lockey") and url.lockey neq "" and getsession.locationkeylist neq "" >
				<cfquery name="qGetSessionDetail" datasource="#datasource#">
					SELECT SU.sessionStart,SU.sessionEnd,SU.instructorKey,S.carKey
					FROM SessionUnit SU
					inner join SESSION S on s.sessionKey = SU.sessionKey
					WHERE SU.sessionKey = <cfqueryparam cfsqltype="cf_sql_integer" value="#URL.selected#">
				</cfquery>

                <!---Check if the location is the hub and if it has remote locations. --->
                
                <cfquery name="qryCheckLocation" datasource="#datasource#">
                	SELECT locCatkey,locHubID from Location L 
                	WHERE locationKEy = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.lockey#">
                </cfquery> 
                
                <cfset isHub = 0>
                <cfif qryCheckLocation.locCatKey eq 4>
                    <cfset isHub = 1>
                </cfif>

				<cfquery name="qGetContiguousSessions" datasource="#datasource#">
					SELECT S.sessionKey,SU.sessionStart,SU.sessionEnd
					FROM  Session S
					INNER JOIN Sessionunit SU on SU.sessionKey = S.sessionKey
					inner join SessionMap SM on SM.sessionKey = S.sessionKey
					WHERE
					0=0
					and SM.userKey <> 0 
					
					AND (SU.instructorKey = <cfqueryparam cfsqltype="cf_sql_integer" value="#qGetSessionDetail.instructorKey#"> OR S.carKey = <cfqueryparam cfsqltype="cf_sql_integer" value="#qGetSessionDetail.carKey#">)
					<!-----and s.sessionKey <> <cfqueryparam cfsqltype="cf_sql_integer" value="#URL.selected#">--->
					and Convert(DATE,SU.sessionStart) = <cfqueryparam cfsqltype="cf_sql_date" value="#qGetSessionDetail.sessionStart#">
				</cfquery>
<Cfdump var="#qGetContiguousSessions#" label="qGetContiguousSessions">
				<cfif qGetContiguousSessions.recordcount gt 0>

					<cfloop query="qGetContiguousSessions">
								<cfquery name="qGetContiguousSessionsForEachSession" datasource="#datasource#">
									SELECT S.sessionKey,SU.instructorKey,S.locationKey
									FROM  Session S
									INNER JOIN Sessionunit SU on SU.sessionKey = S.sessionKey
									WHERE
									 0=0
									 and (SU.instructorKey = <cfqueryparam cfsqltype="cf_sql_integer" value="#qGetSessionDetail.instructorKey#"> OR S.carKey = <cfqueryparam cfsqltype="cf_sql_integer" value="#qGetSessionDetail.carKey#">)
									 AND s.sessionKey <> <cfqueryparam cfsqltype="cf_sql_integer" value="#url.selected#">
									 AND (
											((<cfqueryparam value="#qGetContiguousSessions.sessionStart#" cfsqltype="cf_sql_timestamp"> between DateAdd(minute, -59, SU.sessionStart) and DateAdd(minute, 59, SU.sessionEnd)
												OR <cfqueryparam value="#qGetContiguousSessions.sessionEnd#" cfsqltype="cf_sql_timestamp"> between DateAdd(minute, -59, SU.sessionStart) and DateAdd(minute, 59, SU.sessionEnd))
											)
										    )
								</cfquery>
					<cfdump var="#qGetContiguousSessionsForEachSession#" label="qGetContiguousSessionsForEachSession">		
								<cfloop query="qGetContiguousSessionsForEachSession">
									<cfif qGetContiguousSessionsForEachSession.instructorKey eq qGetSessionDetail.instructorKey>
    									<cfquery datasource="#datasource#">
    										UPDATE session
    										SET locationkey = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.lockey#">
    										WHERE sessionkey = <cfqueryparam cfsqltype="cf_sql_integer" value="#qGetContiguousSessionsForEachSession.sessionkey#">
    									</cfquery>
    								<cfelse>
    								    <!---if it is a different instructor the session must be at the hub. --->
    								    <cfif isHub eq 0>
        								    <cfquery datasource="#datasource#">
                                                UPDATE session
                                                SET locationkey = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.lockey#">
                                                WHERE sessionkey = <cfqueryparam cfsqltype="cf_sql_integer" value="#qGetContiguousSessionsForEachSession.sessionkey#">
                                            </cfquery>
                                        <cfelse>
                                            <cfquery datasource="#datasource#">
                                                UPDATE session
                                                SET locationkey = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.lockey#">
                                                WHERE sessionkey = <cfqueryparam cfsqltype="cf_sql_integer" value="#qGetContiguousSessionsForEachSession.sessionkey#">
                                            </cfquery>
                                        </cfif>
									</cfif>
								</cfloop>

					</cfloop>

				</cfif>

			 </cfif>

Open in new window

erikTsomik

ASKER

any suggestions

LajuanTaylor

As a programmer I know you put some effort into the logic of the application... I was pondering if it would be beneficial to your efforts by leveraging some of the existing design approaches to "Car Reservation Systems".

For example, instead of dealing with recursive calls in the server-side logic leverage the power of your database - stored procs, views, joins, etc.

I thought the following references might be useful to your design process:

Generic database models (See section under Reservations)
http://www.databaseanswers.org/data_models/

University Project Paper on a "Car Reservation System"
http://www.academia.edu/3442775/Car_Rent_and_Online_Reservation_System

URL for PDF Document Attachment a "Database for Car Reservation System"
http://goo.gl/c2Hr7E
final-document.pdf

erikTsomik

ASKER

but regardless my issue would you have any recommendations

erikTsomik

ASKER

Please advice I have to drill down 10 levels deep. So i have fixed number of iterations. I got stuck on this issue

LajuanTaylor

Are the fixed number of iterations occurring at the cfloop?

<cfloop query="qGetContiguousSessionsForEachSession">

Open in new window

erikTsomik

ASKER

It depends it but technically can be more than one , because 2 instructors can sit in a different cars. So it may return 2 contiguous sessions

erikTsomik

ASKER

Any updates on this

LajuanTaylor

Why not use "time" as the primary element to determine where cars are booked and who's driving?

So for any given "sessionStart" and "sessionEnd" i.e. time range select the number of (scheduled, active, inactive) sessions which are linked to a particular car, driver, or location.

I noticed the update for the session "locationkey"... Couldn't you update the session "locationkey" based on where the vehicle is going to be located at during a given "sessionStart" and "sessionEnd" ?

erikTsomik

ASKER

That may change because I do not want the same instructor sitting at the and waiting for the car . There is a hand off issue

LajuanTaylor

Do system updates have to be made from a desktop or can the reservation update be made from a mobile device? I was thinking of possibly a transfer or release of booking feature...

erikTsomik

ASKER

booked session can not be moved anywhere only the contiguous session within 1 hour need to be attached to the same location if its the same instructor and the same car, so the instructor does not have to go anywhere

LajuanTaylor

Drawing myself a visual... Is this the correct way to view the elements in your question?

Two Session Time Blocks: 3:00PM to 3:59PM, 4:00PM to 4:59PM

Cars - C1,C2

Locations - L1,L2,L3,L4

Instructors - I1,I2,I3

erikTsomik

ASKER

Yes I think your got it

ASKER CERTIFIED SOLUTION

LajuanTaylor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial