07-26-2023, 10:37 PM
I am looking at a very simple PHP code that uses our billing system's API to generate a graph. As of right now, it shows average ticket rating for every staff member. I would like to change it to instead just show the average ticket rating per month for the current year.
There is a 'date' field in the same table that has values formatted for each ticket like '2010-08-23 00:48:22'.
How would I alter this code to show average ticket ratings per month for the given year?
<?php
if (!defined("WHMCS"))
die("This file cannot be accessed directly");
$description = "This graph shows average support ratings by staff member";
if ($statsonly) { return false; }
$chartdata = array();
$query = "SELECT DATE_FORMAT(date, '%m-%Y') AS theMonth, AVG(rating) AS avgRating ";
$query .= "FROM tblticketreplies WHERE admin != '' AND rating !='0' ";
$query .= "GROUP BY DATE_FORMAT(date, '%m-%Y') ";
$query .= "ORDER BY avgRating ASC";
$result = mysql_query($query);
//$query = "SELECT admin, AVG(rating) AS avgrating FROM `tblticketreplies` WHERE admin != '' AND rating!='0' GROUP BY admin ORDER BY avgrating ASC";
//$result = mysql_query($query);
while ($data = mysql_fetch_array($result))
{
$chartdata[$data[0]] = round($data[1],2);
}
$graph=new WHMCSGraph(650,400);
$graph->addData($chartdata);
$graph->setTitle("Average Support Ticket Ratings by Month");
$graph->setGradient("lime", "green");
$graph->setDataValues(true);
$graph->setXValuesHorizontal(true);
?>
There is a 'date' field in the same table that has values formatted for each ticket like '2010-08-23 00:48:22'.
How would I alter this code to show average ticket ratings per month for the given year?
<?php
if (!defined("WHMCS"))
die("This file cannot be accessed directly");
$description = "This graph shows average support ratings by staff member";
if ($statsonly) { return false; }
$chartdata = array();
$query = "SELECT DATE_FORMAT(date, '%m-%Y') AS theMonth, AVG(rating) AS avgRating ";
$query .= "FROM tblticketreplies WHERE admin != '' AND rating !='0' ";
$query .= "GROUP BY DATE_FORMAT(date, '%m-%Y') ";
$query .= "ORDER BY avgRating ASC";
$result = mysql_query($query);
//$query = "SELECT admin, AVG(rating) AS avgrating FROM `tblticketreplies` WHERE admin != '' AND rating!='0' GROUP BY admin ORDER BY avgrating ASC";
//$result = mysql_query($query);
while ($data = mysql_fetch_array($result))
{
$chartdata[$data[0]] = round($data[1],2);
}
$graph=new WHMCSGraph(650,400);
$graph->addData($chartdata);
$graph->setTitle("Average Support Ticket Ratings by Month");
$graph->setGradient("lime", "green");
$graph->setDataValues(true);
$graph->setXValuesHorizontal(true);
?>