Location: PHPKode > projects > Tinbox > accountadmin/activity.php
<?php

echo "<h1>Account Activity</h1>";
echo "<table class=\"tabular\" border=\"0\" align=\"center\" cellpadding=\"0\" cellspacing=\"0\" width=\"98%\">";

echo "<tr><th rowspan=\"2\">Account </th><th rowspan=\"2\">Students</th><th  colspan=\"4\">Courses </th><th colspan=\"2\">Modules</th></tr>";
echo "<tr>              <th>Active</th><th>Scheduled</th><th>Started</th><th>Completed</th><th>Sent</th><th>Completed</th></tr>";

$sql = "select idx as account_idx, name from account_main order by name";
$result = mysql_query($sql);
while($row = mysql_fetch_object($result))
        {
        echo "<tr>";
        echo "<td>".$row->name."</td>";
        $sql = "select count(*) as cnt_students from account_main ac, account_students st where ac.idx=st.account_idx and ac.idx='".$row->account_idx."'";
        $row2 = mysql_fetch_object(mysql_query($sql));
        echo "<td>".$row2->cnt_students."</td>";
        $sql = "select count(*) as cnt_active from account_main ac, account_groups gr, class_main cl, class_schedule sc where ac.idx='".$row->account_idx."' and ac.idx=gr.account_idx and gr.idx=cl.group_idx and cl.idx=sc.class_idx and sc.active='1'";
        $row2 = mysql_fetch_object(mysql_query($sql));
        echo "<td>".$row2->cnt_active."</td>";
        $sql = "select count(*) as cnt_sched from account_main ac, account_groups gr, class_main cl, class_schedule sc where ac.idx='".$row->account_idx."' and ac.idx=gr.account_idx and gr.idx=cl.group_idx and cl.idx=sc.class_idx and sc.active='0'";
        $row2 = mysql_fetch_object(mysql_query($sql));
        echo "<td>".$row2->cnt_sched."</td>";
        $sql = "select sum(cnt_cfinished) as cfinished, sum(cnt_mfinished) as mfinished, sum(cnt_msent) as msent, sum(cnt_cstarted) as cstarted from account_log where account_idx='".$row->account_idx."'";
        $row2 = mysql_fetch_object(mysql_query($sql));
        echo "<td>".$row2->cstarted."</td>";
        echo "<td>".$row2->cfinished."</td>";
        echo "<td>".$row2->msent."</td>";
        echo "<td>".$row2->mfinished."</td>";
        echo "</tr>";
        }
echo "<tr>";
echo "<td><b>Total</b></td>";
$sql = "select count(*) as cnt_students from account_main ac, account_students st where ac.idx=st.account_idx ";
$row2 = mysql_fetch_object(mysql_query($sql));
echo "<td>".$row2->cnt_students."</td>";
$sql = "select count(*) as cnt_active from account_main ac, account_groups gr, class_main cl, class_schedule sc where ac.idx=gr.account_idx and gr.idx=cl.group_idx and cl.idx=sc.class_idx and sc.active='1'";
$row2 = mysql_fetch_object(mysql_query($sql));
echo "<td>".$row2->cnt_active."</td>";
$sql = "select count(*) as cnt_sched from account_main ac, account_groups gr, class_main cl, class_schedule sc where ac.idx=gr.account_idx and gr.idx=cl.group_idx and cl.idx=sc.class_idx and sc.active='0'";
$row2 = mysql_fetch_object(mysql_query($sql));
echo "<td>".$row2->cnt_sched."</td>";
$sql = "select sum(cnt_cfinished) as cfinished, sum(cnt_mfinished) as mfinished, sum(cnt_msent) as msent, sum(cnt_cstarted) as cstarted from account_log";
$row2 = mysql_fetch_object(mysql_query($sql));
echo "<td>".$row2->cstarted."</td>";
echo "<td>".$row2->cfinished."</td>";
echo "<td>".$row2->msent."</td>";
echo "<td>".$row2->mfinished."</td>";
echo "</tr>";
echo "</table>";
echo "<hr>";

// drop down
$sql = "select idx as account_idx, name as account from account_main order by name";
$result = mysql_query($sql);

echo "<h1>Detailed Account Activity</h1>";
echo "<form method=\"post\" action=\"\" name=\"accountform\"><select  name=\"accountidx\" onChange=\"document.accountform.submit()\">";
echo "<option value=\"0\"> ------ </option>";
while($row = mysql_fetch_object($result))
        if($accountidx==$row->account_idx)
                echo "<option value=\"".$row->account_idx."\" selected>".$row->account."</option>";
        else
                echo "<option value=\"".$row->account_idx."\">".$row->account."</option>";
echo "</select>";
echo "<input type=\"hidden\" name=\"idx\" value=\"".$idx."\"><input type=\"hidden\" name=\"menu\" value=\"".$menu."\">";
echo "</form>";

if ($accountidx)
        {
        echo "<h2>Last 30 Days</h2>";
        $sql = "select date_format(date,'%a %D') as date, cnt_mfinished, cnt_cfinished, cnt_msent, cnt_cstarted from account_log where to_days(now())-to_days(date) <= 30 and account_idx='".$accountidx."'";
        $result = mysql_query($sql);
        if(mysql_num_rows($result))
                {
                echo "<table class=\"tabular\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\" >";
                echo "<tr><th></th>";
                while($row = mysql_fetch_object($result))
                        echo "<th>".$row->date."</th>";

                mysql_data_seek($result,0);
                echo "</tr><tr><th>Courses Started</th>";
                while($row = mysql_fetch_object($result))
                        echo "<td>".$row->cnt_cstarted."</td>";

                mysql_data_seek($result,0);
                echo "</tr><tr><th>Courses Completed</th>";
                while($row = mysql_fetch_object($result))
                        echo "<td>".$row->cnt_cfinished."</td>";

                mysql_data_seek($result,0);
                echo "</tr><tr><th>Modules Sent</th>";
                while($row = mysql_fetch_object($result))
                        echo "<td>".$row->cnt_msent."</td>";

                mysql_data_seek($result,0);
                echo "</tr><tr><th>Modules Completed</th>";
                while($row = mysql_fetch_object($result))
                        echo "<td>".$row->cnt_mfinished."</td>";
                echo "</tr>";
                echo "</table>";
                }


        echo "<h2>Last 12 Months</h2>";
        $sql = "select date_format(date,'%b %Y') as date, sum(cnt_cstarted) as cstarted, sum(cnt_cfinished) as cfinished, sum(cnt_mfinished) as mfinished, sum(cnt_msent) as msent from account_log where date>date_sub(now(), interval 12 month) and account_idx='".$accountidx."' group by month(date)";
        $result = mysql_query($sql);
        if(mysql_num_rows($result))
                {
                echo "<table class=\"tabular\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\" >";
                echo "<tr><th></th>";
                while($row = mysql_fetch_object($result))
                        echo "<th>".$row->date."</th>";

                mysql_data_seek($result,0);
                echo "</tr><tr><th>Courses Started</th>";
                while($row = mysql_fetch_object($result))
                        echo "<td>".$row->cstarted."</td>";

                mysql_data_seek($result,0);
                echo "</tr><tr><th>Courses Completed</th>";
                while($row = mysql_fetch_object($result))
                        echo "<td>".$row->cfinished."</td>";

                mysql_data_seek($result,0);
                echo "</tr><tr><th>Modules Sent</th>";
                while($row = mysql_fetch_object($result))
                        echo "<td>".$row->msent."</td>";

                mysql_data_seek($result,0);
                echo "</tr><tr><th>Modules Completed</th>";
                while($row = mysql_fetch_object($result))
                        echo "<td>".$row->mfinished."</td>";

                echo "</tr>";
                echo "</table>";
                }


        }
else
        echo "<h4>Select an account to see detailed statistics</h4>";


?>
Return current item: Tinbox