Calculate WEEK number within a CFFORM

I have a form that collects employee daily project logs and hours and posts them to a SQL table that is linked to the project table. I would like to add a small table off to the side that shows the total hours that the employee has entered into the db for TODAY, THIS WEEK, and LAST WEEK. The date of the log is entered by the employee in a form field called ContactDate. The default on the ContactDate field is today's date.
My first idea was to create a trigger that posted the week number to a field in the SQL table, but I couldn't figure out how to convert the javascript I found into something I could use in SQL.

My next idea was to calculate the week number before the entry form is submitted (using a postback kind of idea). I could then query the db for the employee's name and sum for all hour entries for today (easy to do), this week (calculate today's week number and query for this number in the db), and last week (same method but query for this week's number -1).

However, I am stuck on the execution. If I put in a hidden field and call the #form.ContactDate#, it croaks and says it is undefined.

Here is the script I found to do the calculate week number:
******************************************************
function getWeekNumber(d1)
{
      var month = Date.getMonth(d1);
      var day = Date.getDay(d1);
      var year = Date.getYear(d1);
      
      var now = new Date(year, month, day + 1);
      var then = new Date(year, 0, 1);
      var firstDay = new Date(year, 0, 1);      
      var compensation = firstDay.getDay() + 3;      
      return Math.round((((now - then) / 86400000) + compensation) / 7);
}
******************************************************

Here is the main form code which all works fine - except now I want to add the additional information in a table at the top right of the page. I don't know if anyone will need this to help answer my question but here it is:


===================================================================
<cfset LogProjectSelected ="Yup" />
<cfset LogProjectNumber = '#getProjects.ProjectNumber#' />
<cfform action ="MapRequest_LogAction.cfm?ProjectNumber=#myProjectNumber#" name="LogEntry" method="Post" enctype="multipart/form-data">
<cfoutput>
    <input type="hidden" name="CreateDate" value="#DateFormat(Now())#"/>
</cfoutput>

      <select name="ProjectNumber" style="width:450px; text-align:left ">
        <cfoutput query="getProjects">
          <option value=#ProjectNumber#>#ProjectNumber# [#ProjectTitle#] - (CustomerName#)</option>
        </cfoutput> <cfoutput query="getAllProjects">
          <option value=#ProjectNumber#>#ProjectNumber#[#ProjectTitle#] - (#CustomerName#)</option>
        </cfoutput>
      </select>
  <!--- THIS IS THE FIELD WHERE THEY SELECT THEIR NAME --->    
    <select name="StaffName" style="font-size:medium; color:#006699; ">
        <cfoutput>
             <option value="#getProjects.staffnameassign#">#getProjects.staffnameassign#</option>
        </cfoutput>
             <option value="Joe">Joe</option>
             <option value="Jim">Jim</option>
             <option value="Tom">Tom</option>
      </select>
<!---THIS IS THE DATE FIELD IN QUESTION --->
<input type="text" value="<cfoutput>#DateFormat(prestime, "mm/dd/yyyy")#</cfoutput>" name="ContactDate" required="no" size="10" style="text-align:left; vertical-align:middle; color:#CC3300; font-size:medium" />
      <a href="javascript:show_calendar('LogEntry.ContactDate');" onMouseOver="window.status='Date Picker';return true;" onMouseOut="window.status='';return true;"><img border=0 src="NewMapRequest/images/calendar_icon.gif" align="absbottom"></a>
<!---THIS IS THE HOURS FIELD THAT COLLECTS THE HOURS --->
<input name="hrs" type="text" style="font-size:large; color:CC3300; width:100; font-weight:bold;">
      </input>

<textarea name="LogNotes" cols="45" rows="8"></textarea>

<input type="submit" value="Submit Log Entry" onMouseDown="">
<input type="reset" value="Reset Form">
===================================================================

I am interested in any way to accomplish my goal... total hours by week for this week and last week.
SQL - COLDFUSION - JAVASCRIPT - or whatever... I have no preference.

BauerchickStart Free Trial