root/trunk/website/forums/admin/stats_charts.cfm @ 5

<!---
        Name         : stats_charts.cfm
        Author       : Raymond Camden 
        Created      : August 30, 2004
        Last Updated : December 8, 2006
        History      : Slight change to how I get data (rkc 12/8/06)
        Purpose          : 
--->

<cfquery name="conferences" datasource="#application.galleon.settings.dsn#">
select  id, name
from    #application.galleon.settings.tableprefix#conferences
</cfquery>

<cfquery name="forums" datasource="#application.galleon.settings.dsn#">
select  id, name, conferenceidfk
from    #application.galleon.settings.tableprefix#forums
</cfquery>

<cfset threads = application.galleon.thread.getThreads()>
<cfset users = application.galleon.user.getUsers()>

<cfoutput>
<p>
<table class="adminListTable" width="500">
<tr class="adminListHeader">
        <td><b>Number of Forums Per Conference</b></td>
</tr>
<tr>
        <td>
        <cfchart format="flash" chartheight="400" chartwidth="400" seriesplacement="default" 
                         labelformat="number" tipstyle="mouseOver" pieslicestyle="sliced">
                <cfchartseries type="pie">
                        <cfloop query="conferences">
                                <cfquery name="fcount" dbtype="query">
                                select  count(id) as total
                                from    forums
                                where   conferenceidfk = '#id#'
                                </cfquery>
                                <cfif fcount.total is "">
                                        <cfset total = 0>
                                <cfelse>
                                        <cfset total = fcount.total>
                                </cfif>
                                <cfchartdata item="#name#" value="#total#">
                        </cfloop>
                </cfchartseries>
        </cfchart>
        </td>
</tr>
</table>
</p>

<p>
<table class="adminListTable" width="500">
<tr class="adminListHeader">
        <td><b>Number of Threads Per Forum</b></td>
</tr>
<tr>
        <td>
        <cfchart format="flash" chartheight="400" chartwidth="400" seriesplacement="default" 
                         labelformat="number" tipstyle="mouseOver" pieslicestyle="sliced">
                <cfchartseries type="pie">
                        <cfloop query="forums">                 
                                <cfquery name="fcount" dbtype="query">
                                select  count(id) as total
                                from    threads
                                where   forumidfk = '#id#'
                                </cfquery>
                                <cfif fcount.total is "">
                                        <cfset total = 0>
                                <cfelse>
                                        <cfset total = fcount.total>
                                </cfif>
                                <cfchartdata item="#name#" value="#total#">
                        </cfloop>
                </cfchartseries>
        </cfchart>
        </td>
</tr>
</table>
</p>

<cfquery name="sortedThreads" dbtype="query">
        select          *
        from            threads
        order by        messagecount desc
</cfquery>

<p>
<table class="adminListTable" width="500">
<tr class="adminListHeader">
        <td colspan="2"><b>Top 10 Threads by Message Count</b></td>
</tr>
<tr>
        <td><b>Thread Name</b></td>
        <td><b>Message Count</b></td>
</tr>
<cfloop query="sortedThreads" endrow="10">
<tr>
        <td>#name#</td>
        <td>#messagecount#
</tr>
</cfloop>
</table>
</p>

<cfquery name="sortedUsers" dbtype="query">
        select          *
        from            users
        order by        postcount desc
</cfquery>

<p>
<table class="adminListTable" width="500">
<tr class="adminListHeader">
        <td colspan="2"><b>Top 10 Users by Post Count</b></td>
</tr>
<tr>
        <td><b>User Name</b></td>
        <td><b>Post Count</b></td>
</tr>
<cfloop query="sortedUsers" endrow="10">
<tr>
        <td>#username#</td>
        <td>#postcount#
</tr>
</cfloop>
</table>
</p>

</cfoutput>