• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 118
  • Last Modified:

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

0
erikTsomik
Asked:
erikTsomik
  • 8
  • 6
1 Solution
 
erikTsomikSystem Architect, CF programmer Author Commented:
any suggestions
0
 
LajuanTaylorCommented:
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
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
but regardless my issue would you have any recommendations
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
erikTsomikSystem Architect, CF programmer Author Commented:
Please advice I have to drill down 10 levels deep. So i have fixed number of iterations. I got stuck on this issue
0
 
LajuanTaylorCommented:
Are the fixed number of iterations occurring at the cfloop?
<cfloop query="qGetContiguousSessionsForEachSession">

Open in new window

0
 
erikTsomikSystem Architect, CF programmer Author Commented:
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
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
Any updates on this
0
 
LajuanTaylorCommented:
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" ?
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
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
0
 
LajuanTaylorCommented:
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...
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
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
0
 
LajuanTaylorCommented:
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
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
Yes  I think your got it
0
 
LajuanTaylorCommented:
I think you might be able to accomplish what need by leveraging a junction table in your database design. I included some screen shots, a SQL script with data, and a CF snippet.

For your reference I included an article about "many-to-many" relationships which, is what the junction table is functioning as...
http://www.tomjewett.com/dbdesign/dbdesign.php?page=manymany.php

CF Code used in sample:
<cfparam name="datasource" default="YourDatabaseNameHere">
<cfparam name="sessionStart" default="2015-04-11 20:00:00.000">
<cfparam name="sessionEnd" default="2015-04-11 20:59:00.000">

<cfquery name="qGetContiguousSessionsForEachSession" datasource="#datasource#">
				SELECT *
				FROM  vw_SelectReservations
				WHERE
				0=0
				AND (
				((<cfqueryparam value="#sessionStart#" cfsqltype="cf_sql_timestamp"> between DateAdd(minute, -59, sessionStart) and DateAdd(minute, 59, sessionEnd)
				OR <cfqueryparam value="#sessionEnd#" cfsqltype="cf_sql_timestamp"> between DateAdd(minute, -59, sessionStart) and DateAdd(minute, 59, sessionEnd))
				)
				)
</cfquery>

<cfdump var="#qGetContiguousSessionsForEachSession#">

Open in new window

sampleDB.txt
2015-04-12-0143-MultipleSessions.png
2015-04-12-0148-Junction-Table.png
2015-04-11-2311-ContiguousSessionExample
2015-04-12-0145-Session-Table.png
2015-04-12-0146-Reservation-Table.png
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now