I took your concept and applied it, it works great, thanks.
Thanks
Sophek
On 3/10/06, Daniel Elmore <
[EMAIL PROTECTED]> wrote:
Sorry for the delay. I'm not sure what you are now asking. For a 9AM - 9PM
run your table would contain data like so:
FoodBankID | Year | Month | Day | HourSlot
2 | 2006 | 3 | 10 | 9
2 | 2006 | 3 | 10 | 10
2 | 2006 | 3 | 10 | 11
2 | 2006 | 3 | 10 | 12
2 | 2006 | 3 | 10 | 13
2 | 2006 | 3 | 10 | 14
2 | 2006 | 3 | 10 | 15
2 | 2006 | 3 | 10 | 16
2 | 2006 | 3 | 10 | 17
2 | 2006 | 3 | 10 | 18
2 | 2006 | 3 | 10 | 19
2 | 2006 | 3 | 10 | 20
To add 7AM - 8AM for the same food bank, or whatever grouping you have, just
add more records.
Hope that helps
-Daniel
-----Original Message-----
From: [EMAIL PROTECTED] [mailto: [EMAIL PROTECTED]]
On Behalf Of Sophek Tounn
Sent: Thursday, March 09, 2006 11:28 AM
To: Dallas/Fort Worth ColdFusion User Group Mailing List
Subject: Re: [DFW CFUG] Looping question, need help urgently
Hi Daniel And All,
Thank you all for commenting. Here is what is happening in the App.
The admin will create a slot form 9AM - 9PM for example, if he chooses to
enter only 1 date in the date range field, he'll created 1 slot for 1 day,
but if he enters a date range he can create 1 slot for multiple days.
So if he enters 3/01/06 - 03/04/06 , Reservation Time of 9AM thru 9 PM,
he'll have a slot for 9AM - 9 PM for 3-1,3-2,3-3,3-4, I could use the hour
slot like you use suggest but I want the flexibility for the admin to create
a range of hours without having to enter individual hours.
So the problem is when the admin enters a slot that over lapse the 9AM-9PM
slot, the system should give an error, lets say if he enters:
10AM - 11AM = error
2 PM - 3 PM = error
4 PM - 6 PM = error
All these should error because they are within the slot that is already
allocated.
So , If he enters 7-8 AM it should work, and it does. So now he has 2 slots
:
7AM - 8AM
9AM - 9PM
So with 1 slot I can do something like this:
SELECT ScheduleDate, StartTime, EndTime from schedule_master
where StartTime between 900 AND 1700
But how to I check again the other slot the 7-8 AM in my query?
Thanks
All,
On 3/7/06, Daniel Elmore <[EMAIL PROTECTED] > wrote:
Hey Sophek, what you're doing shouldn't be hard if your data is normalized
further.
If you had a table storing:
FoodBankID | Year | Month | Day | HourSlot
You could check if the time slot for 9 - 12 is free by using a query like
so:
SELECT FoodBankID,HourSlot
FROM that_table
WHERE year = year(form.date)
AND month = month(form.date)
AND day = day(form.date)
AND HourSlot BETWEEN #form.startTime# AND #form.endTime#
If no records are found, the time slot is open, otherwise you get the data
for which bank is scheduled at one/all of those hours. Use military time on
the DB side to avoid messing with some AM/PM field.
Let the database do this kind of work, not CF, it will make your life
easier!
Hope that helps,
Daniel
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] ]
On Behalf Of Sophek Tounn
Sent: Tuesday, March 07, 2006 5:24 PM
To: Dallas/Fort Worth ColdFusion User Group Mailing List
Subject: [DFW CFUG] Looping question, need help urgently
Hi All,
I'm not too good with loop here a question I have and hopefully someone can
help me? I'm building an online scheduler for a Food bank client of ours
where he can schedule for delivery, pickup, shopping etc.
I have an interface where they can pick the schedule type, chose the date,
and begin time and end time, also the time interval and agencies per slot.
Lets say I enter a slot time of 9 AM - 12 AM (midnight), so that 9-12 AM
slot is taken, lets say I also have a 7AM -8AM slot taken as well, If I
have just one slot (9-12) only in my DB then I'm fine, but when there are
more than 1 slot is where it gets complicated.
Here is the URL to test it out:
http://www.aidmatrixeurope.org/scheduler/enterslot.cfm
username and password:
admin
admin
0040
Below is my code:
<cfset FinalStartTime = Form.StartTime & ":" & Form.StartTime_Min & ":00" &
"#Form.ST_TT#">
<cfset FinalEndTime = Form.EndTime & ":" & Form.EndTime_Min & ":00" &
"#Form.ET_TT#">
<!--- If the scheduleDateEnd is not filled in, just do a plain insert
--->
<cfif form.ScheduleDateEnd EQ "">
<cfquery name="insertslotcount" datasource="mydatasource">
Select FoodBankCode , ScheduleDate , StartTime , EndTime ,
SlotInterval , AgenciesPerSlot , ScheduleType , CreatedBy , CreatedDate from
schedule_master
where FoodBankCode = <cfqueryparam
value="#SESSION.Auth.FoodbankCode#" cfsqltype="CF_SQL_VARCHAR"> AND
ScheduleDate = <cfqueryparam
value="#DateFormat(form.ScheduleDate,"yyyy-mm-dd")#"
cfsqltype="CF_SQL_VARCHAR"> <!--- AND StartTime = <cfqueryparam
value="#TimeFormat(FinalStartTime,"HH:MM:SS")#" cfsqltype="CF_SQL_VARCHAR">
OR EndTime = <cfqueryparam value="#TimeFormat(FinalEndTime,"HH:MM:SS")#"
cfsqltype="CF_SQL_VARCHAR"> ---> AND ScheduleType = <cfqueryparam
value="#form.ScheduleType#" cfsqltype="CF_SQL_VARCHAR">;
</cfquery>
<cfdump var="#insertslotcount#">
<!--- Get the Hour of the DBStarTime --->
<!--- If hour is Midnight, convert to 24 because the logic will not work if
I don't do that --->
<cfif Hour(insertslotcount.StartTime) EQ 0>
<cfset dbStartTime = 24>
<cfelse>
<cfset dbStartTime = #Hour(insertslotcount.StartTime)#> <!--- 11 PM--->
</cfif>
<!--- Get the Hour of the EndTime --->
<!--- If hour is Midnight, convert to 24 because the logic will not work if
I don't do that --->
<cfif Hour(insertslotcount.EndTime) EQ 0>
<cfset dbEndTime = 24>
<cfelse>
<cfset dbEndTime = #Hour( insertslotcount.EndTime)#> <!--- 11 PM--->
</cfif>
<!--- Get the Hour of the User FinalStartTime --->
<!--- If hour is Midnight, convert to 24 because the logic will not work if
I don't do that --->
<cfif Hour(FinalStartTime) EQ 0>
<cfset UserStartTime = 24>
<cfelse>
<cfset UserStartTime = #Hour(FinalStartTime)#>
</cfif>
<!--- Get the Hour of the User FinalEndTime --->
<!--- If hour is Midnight, convert to 24 because the logic will not work if
I don't do that --->
<cfif Hour(FinalEndTime) EQ 0>
<cfset UserEndTime = 24>
<cfelse>
<cfset UserEndTime = #Hour(FinalEndTime)#>
</cfif>
<cfif (UserStartTime LTE dbStartTime) AND (UserEndTime LTE dbStartTime) OR
(UserStartTime GTE dbStartTime) AND (UserEndTime GTE dbEndTime)>
<cfset returnRespone = "Insert into the database">
<!--- Insert into the database --->
<cfelse>
<!-- Error slot is already taken -->
<cfset returnRespone = "Your Entry of" & FinalStartTime & " " &
FinalEndTime>
</cfif>
</cfif>
thanks in advance
_______________________________________________
Reply to DFWCFUG:
[email protected]
Subscribe/Unsubscribe:
http://lists1.safesecureweb.com/mailman/listinfo/list
List Archives:
http://www.mail-archive.com/list%40list.dfwcfug.org/
http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors:
www.HostMySite.com
www.teksystems.com/
_______________________________________________
Reply to DFWCFUG:
[email protected]
Subscribe/Unsubscribe:
http://lists1.safesecureweb.com/mailman/listinfo/list
List Archives:
http://www.mail-archive.com/list%40list.dfwcfug.org/
http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors:
www.HostMySite.com
www.teksystems.com/
_______________________________________________ Reply to DFWCFUG: [email protected] Subscribe/Unsubscribe: http://lists1.safesecureweb.com/mailman/listinfo/list List Archives: http://www.mail-archive.com/list%40list.dfwcfug.org/ http://www.mail-archive.com/list%40dfwcfug.org/ DFWCFUG Sponsors: www.HostMySite.com www.teksystems.com/
