<cfset date1 = CREATEODBCDATETIME(form.StartDate & '00:00:00')>
<cfset date2 = CREATEODBCDATETIME(form.EndDate & '23:59:59')>

<!--- WHITELIST OF VALID LOCATIONS --->
    <cfset validLocs = "OPERATIONS, CCC, QA, DS, PS, LWR, NR, SDL, FSC">
    <cfset locList = "">
    <cfif structKeyExists(FORM,"location") AND len(FORM.location) GT 0>
        <cfloop list="#FORM.location#" index="vLoc">
            <cfif listContainsNoCase(validLocs, vLoc) GT 0>
            <cfset locList = listAppend(locList,vloc)>
            </cfif>
        </cfloop>
    <cfelse>
    </cfif>

<cfquery datasource="#application.dsn#" name="GetEmployeeInfo">
    SELECT s4.associate /* Associate's ID */
        , s4.assoc_name /* Associate's Name */
        , s4.trans_location /* Associate's Location */
        , s4.checklistsByAssocLoc /* Gives you a count of Checklists by Associate for a specific Location. */
        , s4.assocChecklistsByLoc /* Gives you a count of Total Checklists by All Associates in a Location. */
        , s4.totalChecklistsByAssoc /** Gives you a count of Total Checklists by Specific Associate in All Locations. */
        , s4.totalAssocChecklistsByAllFilteredLoc /* Gives you a count of Total Checklists by Specific Associates in All Locations. */
        , CASE WHEN ( coalesce(s4.assocChecklistsByLoc,0) > 0 ) THEN (CAST(s4.checklistsByAssocLoc AS decimal(8,2))/s4.assocChecklistsByLoc) * 100 ELSE 0 END AS totalChecklistsByAssocLocPct /* This gives you a percent of associate location checklists over count of checklists by Associate in a Location. */
        , CASE WHEN ( coalesce(s4.totalAssocChecklistsByAllFilteredLoc,0) > 0 ) THEN (CAST(s4.totalChecklistsByAssoc AS decimal(8,2))/s4.totalAssocChecklistsByAllFilteredLoc) * 100 ELSE 0 END AS totalChecklistsByLocPct /* This gives you a percent of Total Associate Checklists in All Locations over count of Checklists by All Associate in All Locations. */
        , s4.rnA /* Placeholder for a record to display the Associate Name. */
        , s4.rnL /* Placeholder for a record to display the Location. */
        , s4.rnTotAssoc /* Placeholder for the last Associate Location row. The next row should be an Associate Total. */
    FROM (
    SELECT s3.*
        , SUM(s3.assocChecklistsByLoc) OVER (PARTITION BY s3.associate) AS totalAssocChecklistsByAllFilteredLoc /* Gives you a count of Total Checklists by Specific Associates in All Locations. */
    FROM (

    SELECT s2.*
        FROM (
            SELECT a.assoc_name
                , s1.associate
                , s1.trans_location
                , s1.checklistsByAssocLoc
                , s1.assocChecklistsByLoc
                , s1.totalChecklistsByAssoc
                , ROW_NUMBER() OVER (PARTITION BY s1.associate ORDER BY s1.associate, s1.trans_location) AS rnA /* Placeholder for a record to display the Associate Name */
                , ROW_NUMBER() OVER (PARTITION BY s1.associate, s1.trans_location ORDER BY s1.associate, s1.trans_location) AS rnL /* Placeholder for a record to display the Location */
                , ROW_NUMBER() OVER (PARTITION BY s1.associate ORDER BY s1.trans_location DESC) AS rnTotAssoc /* Placeholder for the last Associate Location row. The next row should be an Associate Total. */
    FROM (
    SELECT c.associate
        , c.trans_location
        , COUNT(*) OVER (PARTITION BY c.associate, c.trans_location) AS checklistsByAssocLoc /* Gives you a count of Checklists by Associate for a specific Location. */
        , COUNT(*) OVER (PARTITION BY c.associate) AS totalChecklistsByAssoc /* Gives you a count of Total Checklists by Associate in All Locations. */
        , COUNT(*) OVER (PARTITION BY c.trans_location) AS assocChecklistsByLoc /* Gives you a count of Total Checklists by All Associates in a Location. */
    FROM cl_checklists c
    LEFT OUTER JOIN tco_associates a ON c.associate = a.assoc_id
        AND a.assoc_id IN ( <cfqueryparam value="#FORM.EmployeeName#" cfsqltype="cf_sql_varchar" list="true" />  ) /* SELECTED ASSOCIATE IDs */
            WHERE c.[DATE] >= <cfqueryparam value="#date1#" cfsqltype="cf_sql_timestamp" /> /* SELECTED DATES */
                AND c.[DATE] <= <cfqueryparam value="#date2#" cfsqltype="cf_sql_timestamp" />
                AND c.trans_location IN ( <cfqueryparam value="#locList#" cfsqltype="cf_sql_varchar" list="true" />  ) /* SELECTED LOCATIONS */
    ) s1
    INNER JOIN tco_associates a ON s1.associate = a.assoc_id
        AND a.assoc_id IN ( <cfqueryparam value="#FORM.EmployeeName#" cfsqltype="cf_sql_varchar" list="true" />  ) /* SELECTED ASSOCIATE IDs */

    ) s2
    WHERE s2.rnA = 1 OR s2.rnL = 1 /* There will be a final Location (rnL=1 and rnTotAssoc=1). This is the final row. */
    ) s3
    ) s4
    ORDER BY s4.assoc_name, s4.trans_location
</cfquery>

<table>
<thead>
<th><strong>Associate Name</strong></th>
<th><strong>Location</strong></th>
<th><strong>Checklists Generated by Associate</strong></th>
<th><strong>Checklists Generated by Selected Location(s)</strong></th>
<th><strong>Associate Percentage of Location Total</strong></th>
<th></th>
</thead>
<cfoutput query="GetEmployeeInfo">
<tr>
<td><cfif rnA EQ 1><strong>#assoc_name#</strong></cfif></td>
<td><cfif rnL EQ 1>#trans_location#</cfif></td>
<td>#checklistsByAssocLoc#</td>
<td>#assocChecklistsByLoc#</td>
<td>#DecimalFormat(totalChecklistsByAssocLocPct)# %</td>
<!---<td> rnA: #rnA# | rnL: #rnL# | rnTotAssoc: #rnTotAssoc# </td> --->
</tr>
<cfif rnTotAssoc EQ 1>
<tr>
<td>Associate Total</td>
<td></td>
<td>#totalChecklistsByAssoc#</td>
<td>#totalAssocChecklistsByAllFilteredLoc#</td>
<td>#DecimalFormat(totalChecklistsByLocPct)# %</td>
<td></td>
</tr>
</cfif>
</cfoutput>
</table>