In the task I need to output the table which contains people and assosiate with
this poeople courses. If the course is required and has been taken then leave
the cell blank, if the course is required and has not been taken then X, the
rest should be N/A
<CFOUTPUT>
<!--- Report Location Training Report --->
<cfset TodayDT = now()>
<!--- variables for standard Header --->
<cfset hdr_RptID = "TTIS-ETR">
<cfset hdr_SysID = "TRAINING RECORDS">
<cfset hdr_RptName = "Employee Training Report">
<!--- end header variables --->
<cfparam name = "errmsg" default = "">
<cfparam name = "community" default = 0>
<cfinclude template="../includes/coursearray2.cfm">
<cfquery name="getLocations" datasource="#session.dbdsn#" >
select l.LocationID, l.Name
from #session.dbname#.dbo.Location l
order by l.Name
</cfquery>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<cfset lastLocation = "">
<html>
<head>
<title>Report Location Training</title>
<script language="javascript">
function goMenu() {
self.location.replace('#session.menu#');
}
function printpage()
{
window.print()
}
function goProcessAnotherReport() {
self.location.replace('#CGI.SCRIPT_NAME#');
}
function validateForm(){
var estr = "";
if (estr > "") {
alert("Please complete the following:\n" + estr);
return false;
}else{
document.reportForm.submit();
}
}
</script>
<link rel="stylesheet" href="../css/style.css" />
</head>
<body >
<div id="hdr_ctr">
<cfinclude template="../includes/stdHeader.cfm">
<!--- <font color="gray">Location Training Report is a summary report
that ...</font><br> --->
</div>
<cfif not isdefined('submitForm')>
<div id="reportCopyForm">
<form action="#CGI.SCRIPT_NAME#?requesttimeout=99500"
name="reportForm" method="post" onSubmit="return doDateCheck(this.cutoffdate);">
<br>
<table width="100%">
<tr><td valign="top" width="100%">
<b>Community:</b><br />
<select name="community">
<option value="0">- All Locations
-</option>
<cfset lastLoc = '***'>
<cfloop query="getLocations">
<cfif locationID is not lastLoc>
<option
value="#locationID#">#name# - #locationID#</option>
<cfset lastLoc = locationID>
</cfif>
</cfloop>
</select>
<br /><br />
<b>Select a Date:</b><br />
<select name="dselected" >
<option value="1">Prior 01/01/2008</option>
<option value="2">Post 01/01/2008</option>
</select>
<cfif LEN(errmsg) GT 0>
<br>
<font color="red"><b>#errmsg#</b></font>
<br>
<cfelse>
<br> <br>
</cfif>
</td></tr>
<tr><td class="tdctr">
<br /><br />
<input type="button" name="btn_submit"
value="Show Report" onClick="validateForm();"/> <input type="button"
name="MainMenu" value="Main Menu" onClick="goMenu();" />
<input type="hidden" name="submitForm"
value="true" />
</td></tr>
</table>
</form>
</div>
<cfelse>
<cfflush interval="10">
<!--- Show report progress bar --->
<cfinclude template="../Includes/_showProgressBar.cfm">
<cfset xls_filename = "#hdr_RptID##session.empID#.csv">
<cfset crslist = "">
<cfset listCourse="31,32,33,136,35,36,37,135,38,39,40">
<cfquery name="getReport"datasource="#session.dbdsn#" dbtype="ODBC">
Select distinct e.employeeid, e.last_name as empLastName,
e.first_name , e.middle_name,
e.last_name,et.completiondate,et.CourseName,et.CourseId,
tr.TrainingID, tr.courseID, tr.StdTimeFrame,
er.last_name as erLastName, er.first_name as
erFirstName, er.middle_name as erMiddleName,
ea.last_name as eaLastName, ea.first_name as
eaFirstName,
ea.middle_name as eaMiddleName, l.name,
l.locationID, l.state, s.StateID, a.areaID, r.regionID, e.hireDate
from employee e
join employeejobhistory h on
h.employeeid = e.employeeid
inner join jobassignment ja on (ja.sequence
= h.sequence and ja.emplrcd = 0 and ja.enddate is NULL)
inner join jobcode_list j on
j.jobcode = h.jobcode
inner join location l on
l.locationid = ja.locationid
Inner join States s
on l.state = s.StateCode
inner join JobTrainLink jtl on
(jtl.JobCode = h.JobCode and jtl.TrainingID is not NULL)
inner Join TrainingReqmt tr ON
(tr.TrainingID = jtl.TrainingID and tr.courseID in(#listCourse#))
left outer Join TrainReqState trs ON
(trs.TrainReqID = tr.TrainReqID and trs.StateID = s.StateID)
left outer join employeeTraining et on (e.employeeid =
et.employeeid and et.courseID = tr.CourseID and et.Errflag is NULL)
left outer join area a on
a.areaid = l.areaid
left outer join employee ea on
ea.employeeid = a.managerEmpID
left outer join region r on
r.regionid = a.regionid
left outer join employee er on
er.employeeid = r.managerEmpID
where e.terminationdate is NULL
and l.excludeflag != 'Y'
<cfif form.community gt 0>and l.LocationID =
'#community#'</cfif> and e.HireDate<cfif form.dSelected eq
1><'01/01/2008'<cfelse>> '01/01/2008'</cfif>
order by er.last_name, er.first_name, ea.last_name,
ea.first_name, l.name, l.locationID,
e.last_name, e.first_name,
e.middle_name, e.employeeID, tr.courseID
</cfquery>
<cfset empcnt = 0>
<cfset Affectedcnt = 0>
<cfset arryEmpCnt = ArrayNew(2)>
<cfset arryEmployees = ArrayNew(1)>
<cfset arryCntTotals = ArrayNew(2)>
<cfset arryEmpCourse = ArrayNew(1)>
<cfset comparray = Arraynew(1)>
<!--- Intialize Info--->
<cfset arryEmployees[1] = " ">
<cfset arryEmployees[2] = " ">
<cfset arryEmpCnt[1][1] = 0> <!--- Community level total --->
<cfset i = 0>
<table border="1" style="border-collapse: collapse" cellpadding="2"
align="center" id="incompleteOrientTbl">
<tr>
<th valign="bottom" >Location</th>
<th valign="bottom" class="tdctr" width="50px">ID</th>
<th valign="bottom" >Employee Name</th>
<!--- Setup Courses and Course Descriptions --->
<cfloop from="1" to="#ArrayLen(arryCourses)#" index="i">
<cfset arryCntTotals[i][1] = 0> <!--- community
counters --->
<cfset arryCntTotals[i][2] = 0> <!---
companywide counters --->
<cfset comparray[i] = ""> <!---
employee line --->
<td valign="middle" class="verticaltext"
height="200" width="50">#arryCourses[i][1]#</td>
</cfloop>
</tr>
<cfset lastLocation = "***">
<cfset lastEmp = "***">
<!--- ==============Loop thru query results
============================ --->
<cfquery name="qryEmpIDs" dbtype="query">
SELECT DISTINCT EmployeeID, First_Name, Last_Name,LocationID FROM getReport
</cfquery>
<cfloop query="qryEmpIDs">
<!---<cfif lastEmp neq EmployeeiD>
<cfif lastEmp neq "***">--->
<tr>
<td valign="top"
>#arryEmployees[1]#</td>
<td valign="top" class="tdctr">#arryEmployees[2]#</td>
<td valign="top">#Last_Name#, #First_Name#</td>
<cfloop from="1"
to="#ArrayLen(arryCourses)#" index="i">
<cfquery name="QoQInline" dbtype="query">
SELECT CompletionDate FROM getReport WHERE
CourseName='#arryCourses[i][3]#'
</cfquery>
<cfif QoQInline.recordcount>
<!-- We have a record returned - therefore this course
is required for this Emp. -->
<cfif isDate(QoQInline.completiondate)>
<td valign="top" class="tdctr"></td>
<cfelse>
<td valign="top" class="tdctr">X</td>
</cfif>
<!-- There was no record in the QofQ - so this is not a
required course for this EmpID -->
<cfelse>
<td valign="top" class="tdctr">N/A</td>
</cfif>
</cfloop>
</tr>
<!---</cfif>
</cfif>
<cfset lastEmp = EmployeeiD>
<cfset lastLocation = LocationID>--->
</cfloop>
</table>
<br><br>
<table align="center">
<tr><td class="tdctr">
<form action="#CGI.SCRIPT_NAME#">
<br /><br />
<input type="button" value="Print this
page" onClick="printpage()" />
<input type="button" name="btn_submit"
value="Respecify Report" onClick="goProcessAnotherReport();"/>
<input type="button" name="MainMenu"
value="Main Menu" onClick="goMenu();" />
</form>
</td>
</tr>
</table>
<!--- stop report progress bar --->
<cfinclude template="../Includes/_stopProgressBar.cfm">
</cfif>
</body>
</html>
</CFOUTPUT>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:303035
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
