I am trying to output the table where I want to list people and their coourse
completion . and it working . The problem that I am having It outputs the same
Emp Id twice or more becaused each employee can signed up for multiple courses.
How can i do that so each employee get to show up once and get the completion
on the same row
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<CFSET lOrganization = 69>
<cfparam name="selworkshop" default="0">
<!--- variables for standard Header --->
<cfset hdr_RptID = "TRS-GTLP">
<cfset hdr_SysID = "GTL Participation">
<cfset hdr_RptName = "GTL Participation Report">
<html>
<head>
<title>#hdr_RptName#</title>
<script language="javascript" type="text/javascript">
function goMenu() {
self.location.replace('#session.menu#');
}
function goProcessAnotherReport() {
self.location.replace('#CGI.SCRIPT_NAME#');
}
function printpage()
{
window.print()
}
</script>
<link rel="stylesheet" href="../css/style.css" />
<script language="javascript" src="../includes/datePicker.js"></script>
<title>#hdr_RptName#</title>
<style type="text/css">
<!--
..style1 {
color: #0000FF;
font-weight: bold;
}
..style17 {font-size: 16px; font-family: "Times New Roman", Times, serif;
font-weight: bold; }
-->
</style>
</head>
<CFIF CGI.SERVER_NAME is "enoch"> <!--- DEVELOPMENT SYSTEM --->
<CFSET lDocDir =
"f:\inetpub\wwwroot\cfdocs\cfelhome\Sunriseportal\TRS\">
<CFELSEIF CGI.SERVER_NAME is "testing.omnilearn.com"> <!--- TESTING WEBSITE
--->
<CFSET lDocDir =
"d:\inetpub\wwwroot\cfdocs\cfelhome\Sunriseportal\TRS\">
<CFELSE>
<!--- PRODUCTION SYSTEM --->
<CFSET lDocDir =
"c:\inetpub\wwwroot\cfdocs\cfelhome\Sunriseportal\TRS\">
</CFIF>
<body>
<div id="hdr_ctr">
<cfinclude template="../includes/stdHeader.cfm">
<!--- <font color="gray">Survey Status Report ss a ....
</font><br> --->
<br>
</div>
<cfset lcoursename = 'FacSklWk'> <!--- this should be fetched from the course
catalog.name --->
<!--- change this to choose the course from the course catalog - on the
dropdown display all courses where Category2 = 'Workshop'--->
<cfset lcounter = 0>
<cfset lastregion = '*'>
<cfset lastcommunity = '*'>
<!--- make a list of all GTLs in the system --->
<cfquery name="getGTLs" datasource="cfelsunrise" dbtype="ODBC">
select ea.EmployeeID, ea.ActivityID, ea.JobRecordKey, e.EmployeeID,
e.Last_Name, e.First_Name, e.Email_Address, e.TRS_Region,
h.EmployeeID, h.sequence, h.EmplRcd, j.LocationID, j.Sequence,
j.EndDate, j.EmplRcd, l.LocationID, l.Name, l.state,
e2.EmployeeID, e2.Last_Name as regionlast, e2.First_Name as
regionfirst,g.courseID
from EmployeeActivity ea
inner join Employee e on e.EmployeeID = ea.EmployeeID
inner join EmployeeJobHistory h on h.EmployeeID = ea.EmployeeID
inner join JobAssignment j on j.Sequence = h.Sequence
inner join Location l on l.locationID =
j.locationID
inner join Employee e2 on e2.EmployeeID =
ea.JobRecordKey
inner join GTLCoach g on g.GTLID=e.employeeid
where ea.ActivityID = 17 and j.EndDate is NULL and j.EmplRcd = 0 and
g.Hide is null
<cfif isDefined("form.sel") and form.Sel eq "Region">
<!--- order by e2.Last_Name, e.Last_Name--->
order by regionfirst
</cfif>
<cfif isDefined("form.sel") and form.Sel eq "Location">
<!--- order by e2.Last_Name, e.Last_Name--->
order by l.name
</cfif>
<cfif isDefined("form.sel") and form.Sel eq "GTL">
<!--- order by e2.Last_Name, e.Last_Name--->
order by e.last_name
</cfif>
</cfquery>
<form name="frm" id="frm" action="" method="post">
<table width="95%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td width="50" bgcolor="#009900"> </td>
<td> Completed Knowledge Check</td>
</tr>
<tr>
<td width="50" bgcolor="#FF0000"> </td>
<td> Incompleted Knowledge Check</td>
</tr>
</table>
<br>
<div align="center"><label for="sel"><b>Sort by:</b></label>
<select name="sel">
<option value="Region">Region</option>
<option value="Location">Location</option>
<option value="GTL">GTL</option>
</select>
<input type="submit" name="btnSubmit" id="btnSubmit" value="Sort" style="
font-size:12px;">
</div>
<br>
<table width="100%" border="1" align="center" cellpadding="0" cellspacing="0">
<tr align="center">
<td rowspan="2" bgcolor="#FFFF99"><span
class="style1">Region</span></td>
<td rowspan="2" bgcolor="#FFFF99"><span
class="style1">Location</span></td>
<td rowspan="2" bgcolor="#FFFF99" width="20%"><span
class="style1">GTL</span></td>
<td rowspan="2" bgcolor="#FFFF99"><span
class="style1">FacSklWk</span></td>
<td colspan="2" bgcolor="#FFFF99"><span class="style1">GTL
HR</span></td>
<td colspan="2" bgcolor="#FFFF99"><span class="style1">GTL
Leaders</span></td>
<td colspan="2" bgcolor="#FFFF99"><span class="style1">GTL
Nursing</span></td>
<td colspan="2" bgcolor="#FFFF99"><span class="style1">GTL
Reminiscence</span></td>
<td colspan="2" bgcolor="#FFFF99"><span class="style1">GTL
Resident Life</span></td>
<td colspan="2" bgcolor="#FFFF99"><span class="style1">GTL
Scheduling</span></td>
</tr>
<tr>
<td bgcolor="#FFFF99"><span class="style17">Fac Guide
</span></td>
<td bgcolor="#FFFF99"><span
class="style17">DVD</span></td>
<td bgcolor="#FFFF99"><span class="style17">Fac Guide
</span></td>
<td bgcolor="#FFFF99"><span
class="style17">DVD</span></td>
<td bgcolor="#FFFF99"><span class="style17">Fac Guide
</span></td>
<td bgcolor="#FFFF99"><span
class="style17">DVD</span></td>
<td bgcolor="#FFFF99"><span class="style17">Fac Guide
</span></td>
<td bgcolor="#FFFF99"><span
class="style17">DVD</span></td>
<td bgcolor="#FFFF99"><span class="style17">Fac Guide
</span></td>
<td bgcolor="#FFFF99"><span
class="style17">DVD</span></td>
<td bgcolor="#FFFF99"><span class="style17">Fac Guide
</span></td>
<td bgcolor="#FFFF99"><span
class="style17">DVD</span></td>
</tr>
<cfset LastEmpID = "">
<cfoutput query="getGTLs">
<cfif LastEmpID is not '#EmployeeID#'>
<cfset lcounter = lcounter + 1>
<cfset lregion = regionfirst&' '®ionlast>
<cfset llocation = name&' ('&state&')'>
<cfset lGTL = Last_Name&', '&First_Name>
<cfset lempID = EmployeeID>
<cfset linvited = ''>
<cfset ldate = ''>
<cfset lattended = ''>
<cfset lKC = ''>
<cfquery name="getPairs" datasource="cfelsunrise" dbtype="ODBC">
select c.Name as Course, c.CourseID, c.description, g.GTLID,
e.last_name as Lname, e.first_name as Fname, e.Middle_name as Mname,
g.CoachID, e2.Last_Name as CoachLname, e2.First_Name as
CoachFname, e2.Middle_Name as CoachMname, g.CertifyDate, g.Hide, g.Sequence
from Employee e
inner join GTLCoach g on e.employeeID =
#lempID#
inner join Employee e2 on e2.employeeID =
g.CoachID
inner join Course_Catalog c on g.CourseID = c.courseID
where GTLID = #lempID# and
g.Hide is NULL
</cfquery>
<!--- <cfset LastEmpID = "">--->
<cfloop query="getPairs">
<!--- <cfif LastEmpID is not '#GTLID#'>--->
<cfquery name="getStepsCompleted" datasource="cfelsunrise"
dbtype="ODBC">
select CourseID, CompletionDate
from employeetraining
where EmployeeID = #getPairs.GTLID#
<cfif getPairs.CourseID eq 1634>
and CourseID in (1600, 1647,
1648)
<cfelseif getPairs.CourseID eq 1635>
and CourseID in (1600, 1645,
1646)
<cfelseif getPairs.CourseID eq 1636>
and CourseID in (1600, 1649,
1650)
<cfelseif getPairs.CourseID eq 1632>
and CourseID in (1600, 1651,
1652)
<cfelseif getPairs.CourseID eq 1637>
and CourseID in (1600, 1653,
1654)
<cfelseif getPairs.CourseID eq 1633>
and CourseID in (1600, 1655,
1656)
</cfif>
order by CourseID asc
</cfquery>
<cfscript>
arrStepsCompleted = ArrayNew(1);
arrStepsCompleted[1] = 0;
arrStepsCompleted[2] = 0;
arrStepsCompleted[3] = 0;
for(i=1; i lte getStepsCompleted.recordcount;
i=i+1) {
if (getStepsCompleted.CourseID[i] eq
1600) {
arrStepsCompleted[1] =
getStepsCompleted.CompletionDate[i];
}
else if (getStepsCompleted.CourseID[i]
eq 1647) {
arrStepsCompleted[2] =
getStepsCompleted.CompletionDate[i];
}
else if (getStepsCompleted.CourseID[i]
eq 1648) {
arrStepsCompleted[3] =
getStepsCompleted.CompletionDate[i];
}
else if (getStepsCompleted.CourseID[i]
eq 1645) {
arrStepsCompleted[2] =
getStepsCompleted.CompletionDate[i];
}
else if (getStepsCompleted.CourseID[i]
eq 1646) {
arrStepsCompleted[3] =
getStepsCompleted.CompletionDate[i];
}
else if (getStepsCompleted.CourseID[i]
eq 1649) {
arrStepsCompleted[2] =
getStepsCompleted.CompletionDate[i];
}
else if (getStepsCompleted.CourseID[i]
eq 1650) {
arrStepsCompleted[3] =
getStepsCompleted.CompletionDate[i];
}
else if (getStepsCompleted.CourseID[i]
eq 1651) {
arrStepsCompleted[2] =
getStepsCompleted.CompletionDate[i];
}
else if (getStepsCompleted.CourseID[i]
eq 1652) {
arrStepsCompleted[3] =
getStepsCompleted.CompletionDate[i];
}
else if (getStepsCompleted.CourseID[i]
eq 1653) {
arrStepsCompleted[2] =
getStepsCompleted.CompletionDate[i];
}
else if (getStepsCompleted.CourseID[i]
eq 1654) {
arrStepsCompleted[3] =
getStepsCompleted.CompletionDate[i];
}
else if (getStepsCompleted.CourseID[i]
eq 1655) {
arrStepsCompleted[2] =
getStepsCompleted.CompletionDate[i];
}
else if (getStepsCompleted.CourseID[i]
eq 1656) {
arrStepsCompleted[3] =
getStepsCompleted.CompletionDate[i];
}
}
</cfscript>
<tr>
<td>#lregion#</td>
<td>#llocation#</td>
<td>#lGTL# (#getGTLs.EmployeeID#)</td>
<cfif isDate(arrStepsCompleted[1])>
<td width="25%" valign="top"
align="right" bgcolor="##009900"> </td>
<cfelse>
<td width="25%" valign="top"
align="right" bgcolor="##FF0000"> </td>
</cfif>
<cfif getPairs.CourseID eq 1635>
<cfif isDate(arrStepsCompleted[2])>
<td width="25%" valign="top"
align="right" bgcolor="##009900"> </td>
<cfelse>
<td width="25%" valign="top"
align="right" bgcolor="##FF0000"> </td>
</cfif>
<cfif isDate(arrStepsCompleted[3])>
<td width="25%" valign="top"
align="right" bgcolor="##009900"> </td>
<cfelse>
<td width="25%" valign="top"
align="right" bgcolor="##FF0000"> </td>
</cfif>
<cfelse>
<td> </td>
<td> </td>
</cfif>
<cfif getPairs.CourseID eq 1634>
<cfif isDate(arrStepsCompleted[2])>
<td width="25%" valign="top"
align="right" bgcolor="##009900"> </td>
<cfelse>
<td width="25%" valign="top"
align="right" bgcolor="##FF0000"> </td>
</cfif>
<cfif isDate(arrStepsCompleted[3])>
<td width="25%" valign="top"
align="right" bgcolor="##009900"> </td>
<cfelse>
<td width="25%" valign="top"
align="right" bgcolor="##FF0000"> </td>
</cfif>
<cfelse>
<td> </td>
<td> </td>
</cfif>
<cfif getPairs.CourseID eq 1636>
<cfif isDate(arrStepsCompleted[2])>
<td width="25%" valign="top"
align="right" bgcolor="##009900"> </td>
<cfelse>
<td width="25%" valign="top"
align="right" bgcolor="##FF0000"> </td>
</cfif>
<cfif isDate(arrStepsCompleted[3])>
<td width="25%" valign="top"
align="right" bgcolor="##009900"> </td>
<cfelse>
<td width="25%" valign="top"
align="right" bgcolor="##FF0000"> </td>
</cfif>
<cfelse>
<td> </td>
<td> </td>
</cfif>
<cfif getPairs.CourseID eq 1632>
<cfif isDate(arrStepsCompleted[2])>
<td width="25%" valign="top"
align="right" bgcolor="##009900"> </td>
<cfelse>
<td width="25%" valign="top"
align="right" bgcolor="##FF0000"> </td>
</cfif>
<cfif isDate(arrStepsCompleted[3])>
<td width="25%" valign="top"
align="right" bgcolor="##009900"> </td>
<cfelse>
<td width="25%" valign="top"
align="right" bgcolor="##FF0000"> </td>
</cfif>
<cfelse>
<td> </td>
<td> </td>
</cfif>
<cfif getPairs.CourseID eq 1637>
<cfif isDate(arrStepsCompleted[2])>
<td width="25%" valign="top"
align="right" bgcolor="##009900"> </td>
<cfelse>
<td width="25%" valign="top"
align="right" bgcolor="##FF0000"> </td>
</cfif>
<cfif isDate(arrStepsCompleted[3])>
<td width="25%" valign="top"
align="right" bgcolor="##009900"> </td>
<cfelse>
<td width="25%" valign="top"
align="right" bgcolor="##FF0000"> </td>
</cfif>
<cfelse>
<td> </td>
<td> </td>
</cfif>
<cfif getPairs.CourseID eq 1633>
<cfif isDate(arrStepsCompleted[2])>
<td width="25%" valign="top"
align="right" bgcolor="##009900"> </td>
<cfelse>
<td width="25%" valign="top"
align="right" bgcolor="##FF0000"> </td>
</cfif>
<cfif isDate(arrStepsCompleted[3])>
<td width="25%" valign="top"
align="right" bgcolor="##009900"> </td>
<cfelse>
<td width="25%" valign="top"
align="right" bgcolor="##FF0000"> </td>
</cfif>
<cfelse>
<td> </td>
<td> </td>
</cfif>
<!--- <cfset LastEmpID = '#GTLID#'>
</cfif>--->
</cfloop>
<cfset LastEmpID = '#EmployeeID#'>
</cfif>
</tr>
</cfoutput>
</table>
<!---<cfoutput>
<table width="95%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td><div align="center">Records: #lcounter#</div></td>
</tr>
</table>
</cfoutput>--->
</form>
</body>
</html>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:307076
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
