Solved

Calculate WEEK number within a CFFORM

Posted on 2006-04-18
770 Views
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.

Bauerchick
0
Question by:Bauerchick

Author Comment

Can I use something like DATEPART(ww, ContactDate) inside a trigger?
0

LVL 75

Accepted Solution

0

Featured Post

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under \$99 per month (considering normal rate of Big Data Cetnters like …
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…