dfwalton
Programmer
- Jul 24, 2002
- 143
Object: assign employees to positions for an event. First select box has list of positions, which are predefined. Second select box shows list of emps. The form contains 30 or so positions, and all are entered with a single Submit button.
The problem is that the form takes 30 secs to a minute to load, because the Employee select box is re-created for each of the 30 rows on the form.
Here are the relevant postions of mycode:
<cfparam name="Session.EventNum" default="25263">
<cfparam name ="Session.EventRegion" default="Bay">
<cfquery name ="qryEPD" datasource="#ds#">
select epd.EmpNum, epd.POS, epd.PosNum, p.position, epd.EmpRate, epd.callTime, convert(char(2), #dbo#.EventsInWeek(epd.empNum,#Session.EventNum#)) as EventsInWeek,
ltrim(rtrim(e.EmpLname)) +','+ltrim(Rtrim(e.EmpFname)) EmpName, eventNum
FROM EventParticDtl epd INNER JOIN
typePosition p ON epd.POS=p.positionCode
LEFT OUTER JOIN employee e
on epd.empNum = e.empNum
where epd.eventNum = #Session.EventNum#
ORDER by P.ranking, EPD.posNum
</cfquery>
<cfquery name="qposition" datasource="#ds#">
select positionCode , position, ltrim(rtrim(convert(char(3),
#dbo#.EventPosCount('#Session.EventRegion#', #Session.EventNum#, positionCode))))+ ' ' + position as position2 from typePosition order by ranking, positionCode
</cfquery>
<cfquery name="AllEmps" datasource="#DS#">
select empNum, ltrim(rtrim(EmpLname)) +','+ ltrim(Rtrim(EmpFname)) +' Assigned: ' + convert(char(2), #dbo#.EventsInWeek(empNum,#Session.EventNum#)) + ' ' + coalesce(empPos,' ') + ' ' + empHomeRegion AS
EmpName
FROM Employee
WHERE empHomeregion= '#Session.EventRegion#'
ORDER BY empLName
</cfquery>
<cfquery name="qryEventInfo" datasource="#ds#">
select E.EventDate, coalesce(E.EventVenue, '') as Venue, eventShowName, rtrim(ltrim(C.ClientCompany)) as Client
from event E Left Outer JOIN Client C ON E.EventClient = C.ClientNum and E.EventRegion
= C.ClientRegion
WHERE E.EventRegion= '#Session.EventRegion#' and E.eventNum
= #SESSION.EventNum#
</cfquery>
</head>
<body >
<table align="center">
<tr>
<td><a href="javascript: parent.mainFrame.location.href=
'dspOneButtonEventStaffPos.cfm';
parent.srchFrame.location.href=
'dspAddPosition.cfm';">
<img src="Images/btn04AddPos.gif" border ="0"></a>
</td>
<td><a href="dspOneButtonhoursEntry.cfm">
<img src="Images/btn04Return.gif"
border = "0"></a>
</td>
</tr>
</table>
<table align="center" width="50%" border="1">
<th width="20%" align="center">Position</th>
<th width="40%" align="center">Assigned to</th>
<th width="10%" align="center">Rate</th>
<th width="15%" align="center">Events for week</th>
<th width="15%">Call Time</th>
<form name="position" action="actOBMaintEventPOs.cfm" method="post" >
<cfoutput>
<input type="hidden" name="eventNum"
value="#Session.EventNum#">
</cfoutput>
<cfloop query="qryEPD">
<tr>
<cfoutput>
<cfset ThisPos = "#qryEPD.POS#">
<cfset ThisEmp = "#qryEPD.EmpNum#">
<cfset ThisPosNum = "#qryEPD.posNUm#">
</cfoutput>
<cfoutput>
<td>
<select name="posCode#thisPosNum#" >
</cfoutput>
<cfoutput query="qposition">
<option value="#qposition.positionCode# "
<cfif #qposition.positionCode# EQ #thisPos#>
selected</cfif>>#qposition.Position#</option>
</cfoutput>
</select>
</td>
<td>
<cfoutput>
<select name="empNum#ThisPOsNum#">
</cfoutput>
<cfoutput>
<option value="0" <cfif #thisEmp# EQ 0>
selected</cfif>>
</option>
</cfoutput>
<cfoutput query="allEmps">
<option value="#allemps.empNum#"
<cfif #trim(allemps.EmpNum)# EQ #trim(ThisEmp)#>
selected
</cfif>>
#allEmps.empName# </option>
</cfoutput>
</select>
</td>
<cfoutput>
<td align = "center">#dollarformat(qryEPD.EmpRate)#</td>
<td align="center">#qryEPD.EventsInWeek#</td>
<td><input type="text" name="CallTime#ThisPosNum#"
size="15" onfocus="this.select(this.form)"
value=<cfif qryEPD.Calltime NEQ "">"#timeformat
(qryEPD.callTime, "h:mm tt"#
"<cfelse>"Unscheduled"</cfif>
onchange="this.value = convert24to1(this.value);">
</td>
<input type="Hidden" name="POSNUM#ThisPosNum#" value="#qryEPD.PosNum#" >
<td>
</td>
</cfoutput>
</tr>
</cfloop>
<tr>
<td colspan="8" align="center"><input type="submit" value = "Save Changes"></td></tr>
</form>
</table>
</body>
</html>
Is there a better way to do this? Any suggestions most welcome.
The problem is that the form takes 30 secs to a minute to load, because the Employee select box is re-created for each of the 30 rows on the form.
Here are the relevant postions of mycode:
<cfparam name="Session.EventNum" default="25263">
<cfparam name ="Session.EventRegion" default="Bay">
<cfquery name ="qryEPD" datasource="#ds#">
select epd.EmpNum, epd.POS, epd.PosNum, p.position, epd.EmpRate, epd.callTime, convert(char(2), #dbo#.EventsInWeek(epd.empNum,#Session.EventNum#)) as EventsInWeek,
ltrim(rtrim(e.EmpLname)) +','+ltrim(Rtrim(e.EmpFname)) EmpName, eventNum
FROM EventParticDtl epd INNER JOIN
typePosition p ON epd.POS=p.positionCode
LEFT OUTER JOIN employee e
on epd.empNum = e.empNum
where epd.eventNum = #Session.EventNum#
ORDER by P.ranking, EPD.posNum
</cfquery>
<cfquery name="qposition" datasource="#ds#">
select positionCode , position, ltrim(rtrim(convert(char(3),
#dbo#.EventPosCount('#Session.EventRegion#', #Session.EventNum#, positionCode))))+ ' ' + position as position2 from typePosition order by ranking, positionCode
</cfquery>
<cfquery name="AllEmps" datasource="#DS#">
select empNum, ltrim(rtrim(EmpLname)) +','+ ltrim(Rtrim(EmpFname)) +' Assigned: ' + convert(char(2), #dbo#.EventsInWeek(empNum,#Session.EventNum#)) + ' ' + coalesce(empPos,' ') + ' ' + empHomeRegion AS
EmpName
FROM Employee
WHERE empHomeregion= '#Session.EventRegion#'
ORDER BY empLName
</cfquery>
<cfquery name="qryEventInfo" datasource="#ds#">
select E.EventDate, coalesce(E.EventVenue, '') as Venue, eventShowName, rtrim(ltrim(C.ClientCompany)) as Client
from event E Left Outer JOIN Client C ON E.EventClient = C.ClientNum and E.EventRegion
= C.ClientRegion
WHERE E.EventRegion= '#Session.EventRegion#' and E.eventNum
= #SESSION.EventNum#
</cfquery>
</head>
<body >
<table align="center">
<tr>
<td><a href="javascript: parent.mainFrame.location.href=
'dspOneButtonEventStaffPos.cfm';
parent.srchFrame.location.href=
'dspAddPosition.cfm';">
<img src="Images/btn04AddPos.gif" border ="0"></a>
</td>
<td><a href="dspOneButtonhoursEntry.cfm">
<img src="Images/btn04Return.gif"
border = "0"></a>
</td>
</tr>
</table>
<table align="center" width="50%" border="1">
<th width="20%" align="center">Position</th>
<th width="40%" align="center">Assigned to</th>
<th width="10%" align="center">Rate</th>
<th width="15%" align="center">Events for week</th>
<th width="15%">Call Time</th>
<form name="position" action="actOBMaintEventPOs.cfm" method="post" >
<cfoutput>
<input type="hidden" name="eventNum"
value="#Session.EventNum#">
</cfoutput>
<cfloop query="qryEPD">
<tr>
<cfoutput>
<cfset ThisPos = "#qryEPD.POS#">
<cfset ThisEmp = "#qryEPD.EmpNum#">
<cfset ThisPosNum = "#qryEPD.posNUm#">
</cfoutput>
<cfoutput>
<td>
<select name="posCode#thisPosNum#" >
</cfoutput>
<cfoutput query="qposition">
<option value="#qposition.positionCode# "
<cfif #qposition.positionCode# EQ #thisPos#>
selected</cfif>>#qposition.Position#</option>
</cfoutput>
</select>
</td>
<td>
<cfoutput>
<select name="empNum#ThisPOsNum#">
</cfoutput>
<cfoutput>
<option value="0" <cfif #thisEmp# EQ 0>
selected</cfif>>
</option>
</cfoutput>
<cfoutput query="allEmps">
<option value="#allemps.empNum#"
<cfif #trim(allemps.EmpNum)# EQ #trim(ThisEmp)#>
selected
</cfif>>
#allEmps.empName# </option>
</cfoutput>
</select>
</td>
<cfoutput>
<td align = "center">#dollarformat(qryEPD.EmpRate)#</td>
<td align="center">#qryEPD.EventsInWeek#</td>
<td><input type="text" name="CallTime#ThisPosNum#"
size="15" onfocus="this.select(this.form)"
value=<cfif qryEPD.Calltime NEQ "">"#timeformat
(qryEPD.callTime, "h:mm tt"#
"<cfelse>"Unscheduled"</cfif>
onchange="this.value = convert24to1(this.value);">
</td>
<input type="Hidden" name="POSNUM#ThisPosNum#" value="#qryEPD.PosNum#" >
<td>
</td>
</cfoutput>
</tr>
</cfloop>
<tr>
<td colspan="8" align="center"><input type="submit" value = "Save Changes"></td></tr>
</form>
</table>
</body>
</html>
Is there a better way to do this? Any suggestions most welcome.