General Logistics - need advice

Results 1 to 10 of 10

Thread: General Logistics - need advice

  1. #1

    Default General Logistics - need advice

    I need some assistance with the general logistics of what im trying to acheive with my web based application. I think it would be far more appropriate to have this in place before asking more technical asp related questions later on.<BR><BR>*************************************** *******************<BR><BR>My web based application overview.<BR>Events management system allowing users to book events, equipment, venues and so on.<BR><BR>*************************************** *******************<BR><BR>Platform.<BR>Windows 2000 - SQL Server 2000 - IIS 5<BR><BR>***************************************** *****************<BR><BR>What Im trying to acheive.<BR>Im allowing for re-occuring events or sessions. Basically, a user selects a start time and finish time. They then select a re-occurance pattern. Daily, weekly, monthly, or yearly. They then select a start date and a completion date. Im looking at creating something VERY SIMILIAR to MS Outlook&#039;s recurring events or meetings, but web based. The important part is that i want to PREVENT double bookings or overlaps of equipment and venues. Makes sense right?<BR><BR>************************************ **********************<BR><BR>As an example.<BR>Internet Awareness Sessions.<BR>Start time 8:00am - Finish time 11:30am<BR>Start Date 15 Nov 2003 - Finish Date 29 Nov 2003<BR>These sessions are recurring every Saturday of the week.<BR>This then provides dates as such....<BR><BR>15 Nov 2003 8:00am - 11:30am<BR>22 Nov 2003 8:00am - 11:30am<BR>29 Nov 2003 8:00am - 11:30am<BR><BR>*********************************** ***********************<BR><BR>Database structure. (venues booked and equipment booked are basically the same)<BR>ID - primary key<BR>column name - event_masterSTART<BR>type - general short date<BR>dummy value - 15/11/2003 7:00am<BR>equipmentID - 22 (22 may be a laptop or something)<BR><BR>ID - primary key<BR>column name - event_masterFINISH<BR>type - general short date<BR>dummy value - 15/11/2003 9:30am<BR>equipmentID - 22<BR><BR>**************************************** ******************<BR><BR>I have been successful.<BR>In comparing single date entries...such as 15 Nov 2003 8:00am - 4:30pm<BR><BR>Heres some SQL code that is functional. This is looking for overlaps on equipment. (request.form("right") is the array which ccarries the equipment values.<BR><BR>if request.form("right") &#060;&#062; "" then<BR>&#039;Get STRING<BR>DIM sMyString: sMyString = request.form("right")<BR>&#039;Get ARRAY<BR>DIM aMyArray : aMyArray = Split(sMyString,",")<BR>DIM intCounter &#039;Just declare the variable<BR>&#039;Arrays start at zero<BR>For intCounter = 0 to UBound(aMyArray)<BR>sql = "SELECT event_masterSTART, event_masterFINISH, equip_booked, magic_key, date " <BR>sql = sql & "FROM equip_booked WHERE equip_booked = " & aMyArray(intCounter)<BR>sql = sql & " AND &#039;" & MediumDate(cdate(session("start_date_overall"))) & " " & session("start_time") & "&#039; BETWEEN event_masterSTART AND event_masterFINISH " <BR>sql = sql & "Or equip_booked = " & aMyArray(intCounter) & " AND &#039;" & MediumDate(cdate(session("finish_date_overall"))) & " " & session("finish_time") & "&#039; BETWEEN event_masterSTART AND event_masterFINISH " <BR>sql = sql & "Or equip_booked = " & aMyArray(intCounter) & " AND &#039;" & MediumDate(cdate(session("start_date_overall"))) & " " & session("start_time") & "&#039; &#060; event_masterSTART AND &#039;" & MediumDate(cdate(session("finish_date_overall"))) & " " & session("finish_time") & "&#039; &#062; event_masterFINISH "<BR>RS_open <BR><BR>****************************************** ****************<BR><BR>Where Im not sure.<BR>How to run comparitive checks against equipment and venues with multiple dates????<BR>I have been able to identify and prevent double bookings on equipment and venues, for single date instances to this point, however im not sure on how to handle this for multiple date values. <BR><BR>What would be the best approach in doing this? No doubt I would have to run some type of comparison against perhaps an array???? of dates?? Would this be easier? <BR><BR>I hope I have explained this ok.<BR><BR>*************************************** *******************<BR><BR>Thanks,<BR>John

  2. #2

    Default errr...anyone?


  3. #3
    Join Date
    Dec 1969

    Default K.I.S.S.

    Don&#039;t even try that, in my opinion.<BR><BR>Instead, add one record to the Calendar table for each occurrence.<BR><BR>Has lots of advantages: Simpler. Faster to do overlap checks. Easy to cancel an event for one week (day, month, whatever) without impacting rest of schedule. (And, incidentally, that frees up the slot for something else!)<BR><BR>Yes, have the master record that describes the repetition pattern. And maybe schedule things out for one or two or three years ahead. And then a year from now (or 6 months or whatever), you run a batch process that reads all the master records to continue the bookings out for another year (or 6 months or ...).<BR><BR>This also makes it more feasible to set up events for recurrence such as "third Friday of each month" which would be nearly impossible to calculate dynamically using SQL.<BR><BR>My opinion, but it&#039;s the only way I would consider doing it.<BR><BR>

  4. #4

    Default Sorry Bill, I *am* inserting

    one record at a you mention here ---&#062;<BR><BR>"Instead, add one record to the Calendar table for each occurrence." I agree.<BR><BR>This is EXACTLY what Im doing. Im sorry If I wasnt clear on this. The actual problem is I need to run checks against each *individual* date BEFORE inserting them as individual records to the database. I need to be able to ensure that ALL of the requested entries are not going to conflict with previously recorded entries. Then carry out the insertions.<BR><BR>Just to clarify...<BR><BR>Each individual entry has its own unique identifier eg 12987, and a *master* ID of which may be (for example purposes only) 25.<BR><BR>This way...especially with recurring events, even though they areindividual or unique entries, because they are all part of the same recurring event, they are grouped together by the master ID...(25).<BR><BR>So you end up with something like this in the db<BR><BR>ID 12345 - Master ID 25<BR>ID 12346 - Master ID 25<BR>ID 12347 - Master ID 25<BR>ID 12348 - Master ID 25<BR><BR>But HOW can I efficiently run this sql query against the db when I may have a multiple dates?<BR><BR>I hope I have clarified this more so...<BR><BR>John

  5. #5

    Default More Information....

    If a user were to select a start date as 10/11/2003 and a completion date as 20/11/2003 *weekdays only* then using this bit of code on the following page....<BR><BR>StartDate = CDate( request.form("startdate") ) <BR> Finishdate = CDate( request.form("finishdate") ) <BR><BR> daily = ( CINT(request.form("option")) = 1 ) <BR> If daily Then <BR> inter_count = 1 <BR> Else <BR> inter_count = CINT( request.form("daily") ) <BR> End If <BR><BR> Do Until StartDate &#062; Finishdate <BR> If ( Not daily ) OR ( Weekday(StartDate) &#060;&#062; 1 AND Weekday(StartDate) &#060;&#062; 7 ) then<BR><BR>*****obviously this is where the check would go I assume???*****<BR><BR> response.write StartDate<BR> End If <BR> <BR> StartDate = DateAdd("d", inter_count, CDate(StartDate)) <BR> Loop <BR><BR>I end up with something like this....<BR><BR>10/11/200311/11/200312/11/200313/11/200314/11/200317/11/200318/11/200319/11/200320/11/2003 <BR><BR>What I then need to do is somehow break this down and migrate it against the original sql query of which I posted in the first thread....<BR><BR>Does that help?

  6. #6

    Default I would appreciate some further assistance ...

    ..with this.<BR><BR>Still unresolved.

  7. #7
    Join Date
    Dec 1969

    Default My schedule ...

    ...starts no earlier than now in the mornings and ends just about when I posted that message yesterday.<BR><BR>

  8. #8
    Join Date
    Dec 1969

    Default Looks like a good start...

    Turn that long string into this:<BR> &#039;10/11/2003&#039;,&#039;11/11/2003&#039;,&#039;12/11/2003&#039;,&#039;13/11/2003&#039;,&#039;14/11/2003&#039;,&#039;17...<BR><BR>And then do<BR> SELECT * FROM schedule <BR> WHERE timeoverlaps<BR> AND scheduleDate IN (&#039;...&#039;,&#039;...&#039;,...)<BR><BR>Let&# 039;s do we code "timeoverlaps"?<BR><BR>Hmmmm...<BR><BR> e1.startTime &#060; e2.endTime AND e1.endTime &#062; e2.startTime<BR><BR>Yeah, I think that works.<BR><BR> e1: 0900 to 1100<BR> e2a: 0800 to 0900 [no match, e1.startTime NOT &#060; e2.endTime]<BR> e2b: 0800 to 1000 [ conflict]<BR> e2c: 1030 to 1130 [ conflict]<BR> e2d: 1100 to 1200 [no match, e1.endTime NOT &#062; e2.startTime]<BR><BR>Presto.<BR><BR>

  9. #9

    Default Thanks Bill

    ...just as a follow up on this...<BR><BR>I think rather than work with a string or an array...I may infact populate a temporary table with the requested dates. Im not sure if this will be easier, however due to the fact that I need to run comparitive checks at multiple stages within the user input equip overlaps, venue overlaps may be a whole lot easier to simply run checks against a temp table. This way I can then easily move from form action to action without having to carry session variables, strings or arrays. Then I guess when the sql query executes its basically looking between the existing bookings table and the temp table. Once the user has completed the process flush the temp table that has some type of unique identifier against it. Perhaps a session ID or something else....not sure. No doubt it would take some ironing out, but I think it may just work. The scary part is the moment between inserting the requests and the possibility of someone making a booking inbetween.....say just a second beforehand...urrgghh!<BR><BR>Im going to give it a try. If you can see an outright reason *not* to do it this way, Id like to know.<BR><BR>Thanks,<BR>John

  10. #10
    Join Date
    Dec 1969

    Default Don't need a temp table...

    Just populate the main schedule table.<BR><BR>Look for conflicts. Show conflicts to user.<BR><BR>Allow user to cancel the event. If cancelled, it&#039;s simply<BR> DELETE FROM schedule WHERE eventID = xxxx<BR><BR>Conflicts are easy:<BR><BR>SELECT s1.* FROM schedule AS s1, schedule AS s2<BR>WHERE s2.eventID = ###<BR>AND s1.schedDate = s2.schedDate<BR>AND s1.startTime &#060; s2.endTime<BR>AND s1.endTime &#062; s2.startTime<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts