My cron job is not generating report from my moodle database.
There is no mistake with my query, it just doesn't display the result.
By the way my moodle version is 2.2
Any help?
Here is the php code:
<?
$username="****";
$password="#####";
$database="moodle";
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query = "SELECT user.firstname, user.lastname, stats.userid, stats.roleid, SUM( statsreads ) AS numreads, SUM( statswrites ) AS numwrites, SUM( statsreads ) + SUM( statswrites ) AS totalactivity FROM `mdl_stats_user_daily` stats, `mdl_user` user WHERE userid IN (SELECT userid FROM mdl_role_assignments WHERE roleid IN (1,2,3,4)) AND user.id = stats.userid AND stats.timeend > ".(time() - 604800)." GROUP BY userid ORDER BY totalactivity DESC";
$result=mysql_query($query);
$num_rows = mysql_num_rows($result);
echo "$num_rows Rows
";
echo "To: arjay.almanzor@gmail.com
";
echo "From: \"Arjay Almanzor\" root@sesc.org
";
echo "Subject: Weekly Moodle Report
";
echo "Content-Type: text/html; charset=\"us-ascii\"
";
echo "<html> <body>
";
echo "<h1>Moodle Report</h1>";
echo "as of ".date('l jS \of F Y h:i:s A');
echo "<h2>Top Users This Week</h1>
";
echo "<table>
";
echo "<tr><td>First Name</td><td>Last Name</td><td>Total Activity(Pageviews/Updates) </td>";
while ($row = mysql_fetch_assoc($result)) {
echo "<tr>";
echo "<td>".$row['firstname']."</td>";
echo "<td>".$row['lastname']."</td>";
echo "<td>".$row['totalactivity']."</td>";
echo "</tr>";
echo "
";
}
echo "</table>
";
$query = "SELECT user.firstname, user.lastname, stats.userid, stats.roleid, SUM( statsreads ) AS numreads, SUM( statswrites ) AS numwrites, SUM( statsreads ) + SUM( statswrites ) AS totalactivity FROM `mdl_stats_user_monthly` stats, `mdl_user` user WHERE userid IN (SELECT userid FROM mdl_role_assignments WHERE roleid IN (1,2,3,4)) AND user.id = stats.userid AND stats.timeend > ".(time() - 2419200)." GROUP BY userid ORDER BY totalactivity DESC";
$result=mysql_query($query);
echo "<h2>Top Users This Month</h1>
";
echo "<table>
";
echo "<tr><td>First Name</td><td>Last Name</td><td>Total Activity(Pageviews/Updates) </td>";
while ($row = mysql_fetch_assoc($result)) {
echo "<tr>";
echo "<td>".$row['firstname']."</td>";
echo "<td>".$row['lastname']."</td>";
echo "<td>".$row['totalactivity']."</td>";
echo "</tr>";
echo "
";
}
echo "</table>
";
echo "</body> </html>";
mysql_close();
?>