php mailing an avg(column)

[mcerveni]

What my script does:

- Select a date range and email the stats to the employee.

 

It works. But now i'm trying to make it show the avg(whatever column) in the email.

 

I'll give you some pictures to break it down for you. Then the code will be at the bottom.

 

 

 

 

There are 2 problems happening.

 

1. The avg(columns) are incorrect.

2. When I select March 2nd - March 8th , it will never show March 2nd. only the row after that. Even if i choose March 6th - March 10th, it will email March 7th - March 10th. It will never grab the first start date.

 

 

My code is below:

 

$agentID = $_GET['aID'];
$agentName = $_GET['n'];

$start_year = $_GET['start_year'];
$start_month = $_GET['start_month'];
$start_day = $_GET['start_day'];
$end_year = $_GET['end_year'];
$end_month = $_GET['end_month'];
$end_day = $_GET['end_day'];
$order = $_GET['orderby'];
$comment = $_GET['comment'];



$start = $start_year . "-" . $start_month ."-".$start_day;
$end = $end_year . "-" . $end_month ."-".$end_day;


$sql = "SELECT * FROM teams LEFT JOIN import_stats ON teams.EmpID = import_stats.EmpID WHERE import_stats.EmpID = '$agentID' and import_stats.date BETWEEN '$start' AND '$end' ORDER BY import_stats.$order";

$sql2 = "SELECT TeamLeader, AgentName, EmpID, AvayaID, date, StaffedTime, AHT , ACDCalls,  ACDTime , AvgACDTime, ACWTime,  AvgACWTime,  HoldCalls, HoldTime,  AvgHoldTime,  OutboundCalls , OutboundTime , AvailTime,  RingTime,  AvgRingTime,  AVG(utilization) FROM import_stats WHERE EmpID = '$agentID' and date BETWEEN '$start' AND '$end' ";

$result = mysql_query($sql);
$row=mysql_fetch_array($result);


$result2 = mysql_query($sql2);
$row2= mysql_fetch_array($result2);


$utilization = $row2['AVG(utilization)'];
$avgUtilization = round($utilization);

$email = $row['email']; //this must be here, else it won't grab the email. 
$TeamCoach = $row['TeamCoach'];

while($row=mysql_fetch_array($result))
{

$array_s['date'][]= $row['date'];
$array_s['StaffedTime'][]=$row['StaffedTime'];
$array_s['AHT'][]=$row['AHT'];
$array_s['ACDCalls'][]=$row['ACDCalls'];
$array_s['ACDTime'][]=$row['ACDTime'];
$array_s['AvgACDTime'][]=$row['AvgACDTime'];
$array_s['ACWTime'][]=$row['ACWTime'];
$array_s['AvgACWTime'][]=$row['AvgACWTime'];
$array_s['HoldCalls'][]=$row['HoldCalls'];
$array_s['HoldTime'][]=$row['HoldTime'];
$array_s['AvgHoldTime'][]=$row['AvgHoldTime'];
$array_s['OutboundCalls'][]=$row['OutboundCalls'];
$array_s['OutboundTime'][]=$row['OutboundTime'];
$array_s['AvailTime'][]=$row['AvailTime'];
$array_s['RingTime'][]=$row['RingTime'];
$array_s['AvgRingTime'][]=$row['AvgRingTime'];
$array_s['utilization'][]=$row['utilization'];

}



$to= $email;
$subject = " $agentName's Stats";

$body="  $agentName,  below are your current stats.
<br><br>
<b> Avg. Utilization = $avgUtilization </b>
<br><br>

<table style = 'color:#212732;'>
<tr> <th style='background-color:#b9cbe9;'> Date </th> <th style='background-color:#b9cbe9;'>  Staffed Time  </th>  <th style='background-color:#b9cbe9;'>  AHT  </th> <th style='background-color:#b9cbe9;'>  ACD Calls  </th>  <th style='background-color:#b9cbe9;'>  ACD Time  </th>  
<th style='background-color:#b9cbe9;'>  Avg. ACD Time  </th>  <th style='background-color:#b9cbe9;'>  ACW Time </th> <th style='background-color:#b9cbe9;'>  Avg. ACW Time  </th>  <th style='background-color:#b9cbe9;'>  Hold Calls  </th>  <th style='background-color:#b9cbe9;'>  Hold Time  </th>  <th style='background-color:#b9cbe9;'> Avg. Hold Time </th>  <th style='background-color:#b9cbe9;'>  Outbound Calls  </th>  
<th style='background-color:#b9cbe9;'>  Outbound Time  </th>  <th style='background-color:#b9cbe9;'>  Avail Time  </th>  <th style='background-color:#b9cbe9;'> Ring Time  </th>  <th style='background-color:#b9cbe9;'>   Avg. Ring Time </th>  <th style='background-color:#b9cbe9;'>  Utilization   </th> 
</tr>";


for($j=0;$j<count($array_s['date']);$j++)
{

$body .= "<tr> <td style='text-align:center'>  ".$array_s['date'][$j]." </td> <td style='text-align:center'> ".$array_s['StaffedTime'][$j]."   </td>
<td style='text-align:center'> ".$array_s['AHT'][$j]."  </td>  <td style='text-align:center'>  ".$array_s['ACDCalls'][$j]."  </td>  <td>  ".$array_s['ACDTime'][$j]."  </td>  <td style='text-align:center'>  ".$array_s['AvgACDTime'][$j]." </td> 
<td style='text-align:center'> ".$array_s['ACWTime'][$j]."  </td>  <td style='text-align:center'>  ".$array_s['AvgACWTime'][$j]."  </td> <td style='text-align:center'> ".$array_s['HoldCalls'][$j]."  </td> <td style='text-align:center'> ".$array_s['HoldTime'][$j]."</td> 
<td style='text-align:center'> ".$array_s['AvgHoldTime'][$j]."  </td> <td style='text-align:center'> ".$array_s['OutboundCalls'][$j]." </td style='text-align:center'>  <td style='text-align:center'>  ".$array_s['OutboundTime'][$j]."</td>  
<td style='text-align:center'>  ".$array_s['AvailTime'][$j]."</td> <td style='text-align:center'> ".$array_s['RingTime'][$j]." </td>  <td style='text-align:center'> ".$array_s['AvgRingTime'][$j]."</td> <td style='text-align:center'>  ".$array_s['utilization'][$j]."  </td> 
</tr>
";
}

$body .= "</table>
<br><br><br>
Your Team Coach: <b> $TeamCoach </b>

";