Code:
<!-- METADATA TYPE="typelib" FILE="C:\Program Files\Common Files\System\ado\msado15.dll"-->
<%
connstr = "Provider=SQLOLEDB.1;UID=xxxxxxxxxxxxxxxxx;Password=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx;Persist Security Info=False;Initial Catalog=xxxxxxxxxxxxxxxxxx;Data Source=xxxxxxxxxxxxxxxxxxxx"
'localize the vars
dim stringbuffer,startdate,enddate,agents,arrayagents,x,y,z,rst,comm
startdate = request.form("startdate")
enddate = request.form("enddate")
agents = request.form("agents")
arrayagents = split(agents, ",")
for z=0 to ubound(arrayagents)
' response.write Cstr(arrayagents(z)) & "<br>"
stringbuffer = stringbuffer & "'" & trim(arrayagents(z)) & "',"
next
stringbuffer = left(stringbuffer, len(stringbuffer) -1)
'response.write stringbuffer
'------------------------------------
'and now, for the looooooooooooooooop
'sp_scholastic_qa_report
'------------------------------------
server.ScriptTimeout = 3600
set comm = Server.CreateObject ("ADODB.Command")
set rst = Server.CreateObject("ADODB.Recordset")
comm.CommandTimeout = 0
comm.ActiveConnection = connstr
comm.CommandText = "sp_scholastic_qa_report"
comm.CommandType = adCmdStoredProc
comm.Parameters("@startdate") = startdate
comm.Parameters("@enddate") = enddate
comm.Parameters("@agents") = stringbuffer
set rst = comm.Execute
'set comm = Nothing
' Build the table -->
dim tablevar, alert2
y=0
fcount = rst.Fields.count-1
if not rst.EOF then
'response.write rst.recordcount
do until rst.eof
tablevar = tablevar & "<tr>" & vbcrlf
for x = 0 to rst.Fields.count-1
if x < 14 then
tablevar = tablevar & "<td id=""row_"& y & "_col_" & x &""">" & rst.Fields(x).Value & "</td>" & vbcrlf
else
if x < 17 then
tablevar = tablevar & "<td id=""row_"& y & "_col_" & x &""">0</td>" & vbcrlf
else
'grab the na's
tablevar = tablevar & "<td style=""visibility:hidden; width: 1px;"" id=""row_"& y & "_col_" & x &""">" & rst.Fields(x).Value & "</td>" & vbcrlf
end if
end if
next
tablevar = tablevar & "</tr>" & vbcrlf
y=y+1
rst.MoveNext
loop
end if
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xml:lang="en" lang="en" xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Scholastic QA Report</title>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
<link rel="stylesheet" type="text/css" media="screen" href="../style.css" />
<style type="text/css" media="screen">
body { font-size: .7em; }
th { background: #f1f1f1; text-align: center; }
.subhead { background: #678; color: #fff; font-weight: bold; text-align: center;}
.divider { background: #333; }
</style>
<script type="text/javascript">
function initvalues() {
var theform = document.getElementById("frmdefault");
//ok, here, we need to assign all the values to the form after we do all kinds of crazy math.
var fcount = theform.fcount.value;
var ycount = theform.ycount.value;
//ok, first the agent name. no need for math. loop it out for col0
// also don't need math for col1, total calls
for(var a=0; a < ycount; a++)
{
for(var f=0; f < 2; f++)
{
eval('document.getElementById("datarow_'+a+'_col_'+f+'").innerHTML = document.getElementById("row_'+a+'_col_'+f+'").innerHTML');
}
}
// Q1 - find NA's, subtract, then set to value percentage
for(var a=0; a < ycount; a++)
{
for(var f=2; f < 3; f++)
{
var na, field, rowcount
na = document.getElementById('row_'+a+'_col_14').innerHTML;
field = document.getElementById('row_'+a+'_col_2').innerHTML;
rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;
totala = Number(rowcount) - Number(na);
totalb = (Number(field)/totala)*100;
totalc = formatNumber(totalb, "00.00") + '%';
document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totalc;
}
}
// Q2 - find NA's, subtract, then set to value percentage
for(var a=0; a < ycount; a++)
{
for(var f=3; f < 4; f++)
{
var na, field, rowcount
na = document.getElementById('row_'+a+'_col_15').innerHTML;
field = document.getElementById('row_'+a+'_col_3').innerHTML;
rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;
totala = Number(rowcount) - Number(na);
totalb = (Number(field)/totala)*100;
totalc = formatNumber(totalb, "00.00") + '%';
document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totalc;
}
}
// Q3 - find NA's, subtract, then set to value percentage
for(var a=0; a < ycount; a++)
{
for(var f=4; f < 5; f++)
{
var na, field, rowcount
na = document.getElementById('row_'+a+'_col_16').innerHTML;
field = document.getElementById('row_'+a+'_col_4').innerHTML;
rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;
totala = Number(rowcount) - Number(na);
totalb = (Number(field)/totala)*100;
totalc = formatNumber(totalb, "00.00") + '%';
document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totalc;
}
}
// Q4 - find NA's, subtract, then set to value percentage
for(var a=0; a < ycount; a++)
{
for(var f=5; f < 6; f++)
{
var na, field, rowcount
na = document.getElementById('row_'+a+'_col_17').innerHTML;
field = document.getElementById('row_'+a+'_col_5').innerHTML;
rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;
totala = Number(rowcount) - Number(na);
totalb = (Number(field)/totala)*100;
totalc = formatNumber(totalb, "00.00") + '%';
document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totalc;
}
}
// Q5 - find NA's, subtract, then set to value percentage
for(var a=0; a < ycount; a++)
{
for(var f=6; f < 7; f++)
{
var na, field, rowcount
na = document.getElementById('row_'+a+'_col_18').innerHTML;
field = document.getElementById('row_'+a+'_col_6').innerHTML;
rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;
totala = Number(rowcount) - Number(na);
totalb = (Number(field)/totala)*100;
totalc = formatNumber(totalb, "00.00") + '%';
document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totalc;
}
}
// Q6 - find NA's, subtract, then set to value percentage
for(var a=0; a < ycount; a++)
{
for(var f=7; f < 8; f++)
{
var na, field, rowcount
na = document.getElementById('row_'+a+'_col_19').innerHTML;
field = document.getElementById('row_'+a+'_col_7').innerHTML;
rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;
totala = Number(rowcount) - Number(na);
totalb = (Number(field)/totala)*100;
totalc = formatNumber(totalb, "00.00") + '%';
document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totalc;
}
}
// Q7 - find NA's, subtract, then set to value percentage
for(var a=0; a < ycount; a++)
{
for(var f=8; f < 9; f++)
{
var na, field, rowcount
na = document.getElementById('row_'+a+'_col_20').innerHTML;
field = document.getElementById('row_'+a+'_col_8').innerHTML;
rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;
totala = Number(rowcount) - Number(na);
totalb = (Number(field)/totala)*100;
totalc = formatNumber(totalb, "00.00") + '%';
document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totalc;
}
}
// Q8 - find NA's, subtract, then set to value percentage
for(var a=0; a < ycount; a++)
{
for(var f=9; f < 10; f++)
{
var na, field, rowcount
na = document.getElementById('row_'+a+'_col_21').innerHTML;
field = document.getElementById('row_'+a+'_col_9').innerHTML;
rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;
totala = Number(rowcount) - Number(na);
totalb = (Number(field)/totala)*100;
totalc = formatNumber(totalb, "00.00") + '%';
document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totalc;
}
}
// Q9 - find NA's, subtract, then set to value percentage
for(var a=0; a < ycount; a++)
{
for(var f=10; f < 11; f++)
{
var na, field, rowcount
na = document.getElementById('row_'+a+'_col_22').innerHTML;
field = document.getElementById('row_'+a+'_col_10').innerHTML;
rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;
totala = Number(rowcount) - Number(na);
totalb = (Number(field)/totala)*100;
totalc = formatNumber(totalb, "00.00") + '%';
document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totalc;
}
}
// Q10 - find NA's, subtract, then set to value percentage
for(var a=0; a < ycount; a++)
{
for(var f=11; f < 12; f++)
{
var na, field, rowcount
na = document.getElementById('row_'+a+'_col_23').innerHTML;
field = document.getElementById('row_'+a+'_col_11').innerHTML;
rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;
totala = Number(rowcount) - Number(na);
totalb = (Number(field)/totala)*100;
totalc = formatNumber(totalb, "00.00") + '%';
document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totalc;
}
}
// Q11 - find NA's, subtract, then set to value percentage
for(var a=0; a < ycount; a++)
{
for(var f=12; f < 13; f++)
{
var na, field, rowcount
na = document.getElementById('row_'+a+'_col_24').innerHTML;
field = document.getElementById('row_'+a+'_col_12').innerHTML;
rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;
totala = Number(rowcount) - Number(na);
totalb = (Number(field)/totala)*100;
totalc = formatNumber(totalb, "00.00") + '%';
document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totalc;
}
}
// Q12 - find NA's, subtract, then set to value percentage
for(var a=0; a < ycount; a++)
{
for(var f=13; f < 14; f++)
{
var na, field, rowcount
na = document.getElementById('row_'+a+'_col_25').innerHTML;
field = document.getElementById('row_'+a+'_col_13').innerHTML;
rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;
totala = Number(rowcount) - Number(na);
totalb = (Number(field)/totala)*100;
totalc = formatNumber(totalb, "00.00") + '%';
document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totalc;
}
}
//--------------------------------------------------------------------------------------------------------
//ok,,,,,,,,,,,,,,, now, for the total's columns....
// Q1 - Q4 Phone pres.
for(var a=0; a < ycount; a++)
{
for(var f=14; f < 15; f++)
{
var fielda, fieldb, fieldc, fieldd
fielda = document.getElementById('row_'+a+'_col_2').innerHTML;
fieldb = document.getElementById('row_'+a+'_col_3').innerHTML;
fieldc = document.getElementById('row_'+a+'_col_4').innerHTML;
fieldd = document.getElementById('row_'+a+'_col_5').innerHTML;
rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;
na1 = document.getElementById('row_'+a+'_col_14').innerHTML;
na2 = document.getElementById('row_'+a+'_col_15').innerHTML;
na3 = document.getElementById('row_'+a+'_col_16').innerHTML;
na4 = document.getElementById('row_'+a+'_col_17').innerHTML;
totala = Number(fielda) + Number(fieldb) + Number(fieldc) + Number(fieldd);
totalna = Number(na1) + Number(na2) + Number(na3) + Number(na4);
//alert(totala);
totalb = Number(totala);
totalnab = Number(totalna);
rows = (rowcount * 4) - totalnab;
totalc = (totalb / rows) *100;
totald = formatNumber(totalc, "00.00") + '%';
document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totald;
}
}
// Q5 - Q10 Req El's
for(var a=0; a < ycount; a++)
{
for(var f=15; f < 16; f++)
{
var fielda, fieldb, fieldc, fieldd, filede, fieldf
fielda = document.getElementById('row_'+a+'_col_6').innerHTML;
fieldb = document.getElementById('row_'+a+'_col_7').innerHTML;
fieldc = document.getElementById('row_'+a+'_col_8').innerHTML;
fieldd = document.getElementById('row_'+a+'_col_9').innerHTML;
fielde = document.getElementById('row_'+a+'_col_10').innerHTML;
fieldf = document.getElementById('row_'+a+'_col_11').innerHTML;
rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;
na1 = document.getElementById('row_'+a+'_col_18').innerHTML;
na2 = document.getElementById('row_'+a+'_col_19').innerHTML;
na3 = document.getElementById('row_'+a+'_col_20').innerHTML;
na4 = document.getElementById('row_'+a+'_col_21').innerHTML;
na5 = document.getElementById('row_'+a+'_col_22').innerHTML;
na6 = document.getElementById('row_'+a+'_col_23').innerHTML;
totalna = Number(na1) + Number(na2) + Number(na3) + Number(na4) + Number(na5) + Number(na6);
totala = Number(fielda) + Number(fieldb) + Number(fieldc) + Number(fieldd) + Number(fielde) + Number(fieldf);
//alert(totala);
totalb = Number(totala);
totalnab = Number(totalna);
rows = (rowcount * 6) - totalnab;
totalc = (totalb / rows) *100;
totald = formatNumber(totalc, "00.00") + '%';
document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totald;
}
}
// Q11 - Q12 Closing
for(var a=0; a < ycount; a++)
{
for(var f=16; f < 17; f++)
{
var fielda, fieldb
fielda = document.getElementById('row_'+a+'_col_12').innerHTML;
fieldb = document.getElementById('row_'+a+'_col_13').innerHTML;
rowcount = document.getElementById('row_'+a+'_col_1').innerHTML;
na1 = document.getElementById('row_'+a+'_col_24').innerHTML;
na2 = document.getElementById('row_'+a+'_col_25').innerHTML;
totalna = Number(na1) + Number(na2);
totala = Number(fielda) + Number(fieldb);
totalb = Number(totala);
totalnab = Number(totalna);
rows = (rowcount * 2) - totalnab;
totalc = (totalb / rows) *100;
totald = formatNumber(totalc, "00.00") + '%';
document.getElementById('datarow_'+a+'_col_'+f+'').innerHTML = totald;
}
}
}
////////////////// number format script
// CONSTANTS
var separator = ","; // use comma as 000's separator
var decpoint = "."; // use period as decimal point
var percent = "%";
var currency = "$"; // use dollar sign for currency
function formatNumber(number, format, print) { // use: formatNumber(number, "format")
if (print) document.write("formatNumber(" + number + ", \"" + format + "\")<br>");
if (number - 0 != number) return null; // if number is NaN return null
var useSeparator = format.indexOf(separator) != -1; // use separators in number
var usePercent = format.indexOf(percent) != -1; // convert output to percentage
var useCurrency = format.indexOf(currency) != -1; // use currency format
var isNegative = (number < 0);
number = Math.abs (number);
if (usePercent) number *= 100;
format = strip(format, separator + percent + currency); // remove key characters
number = "" + number; // convert number input to string
// split input value into LHS and RHS using decpoint as divider
var dec = number.indexOf(decpoint) != -1;
var nleftEnd = (dec) ? number.substring(0, number.indexOf(".")) : number;
var nrightEnd = (dec) ? number.substring(number.indexOf(".") + 1) : "";
// split format string into LHS and RHS using decpoint as divider
dec = format.indexOf(decpoint) != -1;
var sleftEnd = (dec) ? format.substring(0, format.indexOf(".")) : format;
var srightEnd = (dec) ? format.substring(format.indexOf(".") + 1) : "";
// adjust decimal places by cropping or adding zeros to LHS of number
if (srightEnd.length < nrightEnd.length) {
var nextChar = nrightEnd.charAt(srightEnd.length) - 0;
nrightEnd = nrightEnd.substring(0, srightEnd.length);
if (nextChar >= 5) nrightEnd = "" + ((nrightEnd - 0) + 1); // round up
// patch provided by Patti Marcoux 1999/08/06
while (srightEnd.length > nrightEnd.length) {
nrightEnd = "0" + nrightEnd;
}
if (srightEnd.length < nrightEnd.length) {
nrightEnd = nrightEnd.substring(1);
nleftEnd = (nleftEnd - 0) + 1;
}
} else {
for (var i=nrightEnd.length; srightEnd.length > nrightEnd.length; i++) {
if (srightEnd.charAt(i) == "0") nrightEnd += "0"; // append zero to RHS of number
else break;
}
}
// adjust leading zeros
sleftEnd = strip(sleftEnd, "#"); // remove hashes from LHS of format
while (sleftEnd.length > nleftEnd.length) {
nleftEnd = "0" + nleftEnd; // prepend zero to LHS of number
}
if (useSeparator) nleftEnd = separate(nleftEnd, separator); // add separator
var output = nleftEnd + ((nrightEnd != "") ? "." + nrightEnd : ""); // combine parts
output = ((useCurrency) ? currency : "") + output + ((usePercent) ? percent : "");
if (isNegative) {
// patch suggested by Tom Denn 25/4/2001
output = (useCurrency) ? "(" + output + ")" : "-" + output;
}
return output;
}
function strip(input, chars) { // strip all characters in 'chars' from input
var output = ""; // initialise output string
for (var i=0; i < input.length; i++)
if (chars.indexOf(input.charAt(i)) == -1)
output += input.charAt(i);
return output;
}
function separate(input, separator) { // format input using 'separator' to mark 000's
input = "" + input;
var output = ""; // initialise output string
for (var i=0; i < input.length; i++) {
if (i != 0 && (input.length - i) % 3 == 0) output += separator;
output += input.charAt(i);
}
return output;
}
</script>
</head>
<body onload="initvalues()">
<div id="wrapper">
<div id="header">
<h1>Scholastic QA Report</h1>
</div>
<div id="padder">
<div id="content">
<form name="frmdefault" id="frmdefault" action="" method="post">
<input type="hidden" name="startdate" value="<%= request.form("startdate") %>" />
<input type="hidden" name="enddate" value="<%= request.form("enddate") %>" />
<input type="hidden" name="agents" value="<%= request.form("agents") %>" />
<input type="hidden" name="ycount" value="<%= y %>" />
<input type="hidden" name="fcount" value="<%= fcount %>" />
<table align="center" border="1" bordercolor="#334455" cellpadding="2" cellspacing="0">
<tr>
<th colspan="2"><%= startdate %> to <%= enddate %></th>
<th colspan="4">Phone Presence / Listening</th>
<th colspan="6">Required Elements</th>
<th colspan="2">Close Call</th>
<th colspan="3">Totals</th>
</tr>
<tr>
<td class="subhead">CSR Name</td>
<td class="subhead">Total Calls Monitored</td>
<td class="subhead">Friendly and Enthusiastic (q1)</td>
<td class="subhead">Maintains Control (q2)</td>
<td class="subhead">Listened, Respected and Responded (q3)</td>
<td class="subhead">Apologized (q4)</td>
<td class="subhead">Asked Relevant Questions (q5)</td>
<td class="subhead">Mentioned Annual Account (q6)</td>
<td class="subhead">Ask for Payment (q7)</td>
<td class="subhead">Opportunity to Save Account (q8)</td>
<td class="subhead">Opportunity to have Customer Keep Product (q9)</td>
<td class="subhead">Cross-enroll (q10)</td>
<td class="subhead">Ask to assist further / Thanked Customer (q11)</td>
<td class="subhead">All Issues resolved (q12)</td>
<td class="subhead">Phone Presence / Listening Total Average</td>
<td class="subhead">Required Elements / Total Average</td>
<td class="subhead">Close Call Total Average</td>
</tr>
<%
for z=0 to y-1
response.write "<tr>" &vbcrlf
for f=0 to fcount
if f < 17 then
if f = 0 then
response.write "<td id=""datarow_"& z &"_col_" & f & """></td>" & vbcrlf
else
if f = 1 OR f=14 OR f=15 OR f=16 then
response.write "<td id=""datarow_"& z &"_col_" & f & """></td>" & vbcrlf
else
response.write "<td id=""datarow_"& z &"_col_" & f & """></td>" & vbcrlf
end if
end if
else
response.write "<td style=""visibility: hidden; width:1px;"" id=""datarow_"& z &"_col_" & f & """></td>" & vbcrlf
end if
next
response.write "</tr>" &vbcrlf
next
%>
</table>
<div style="visibility:hidden; height: 1px;">
<table>
<%= tablevar %>
</table>
</div>
</form>
</div>
</div>
</div>
</body>
</html>