root/trunk/website/blog/statsbyyear.cfm @ 15

<cfsetting enablecfoutputonly=true>
<cfprocessingdirective pageencoding="utf-8">
<!---
        Name         : Stats
        Author       : Raymond Camden 
        Created      : November 19, 2004
        Last Updated : December 31, 2008 (by Aaron S. West)
        History      : reset for 5.0
                                 : gettopviews didnt filter by blog. gettotalviews added (rkc 7/17/06)
                                 : rb use, and subscriber count wsn't filtering by verified (rkc 8/20/06)
                                 : comment mod support (rkc 12/7/06)
        Purpose          : Stats
--->

<cfmodule template="tags/layout.cfm" title="#rb("stats")#">
        
        <cfset dsn = application.blog.getProperty("dsn")>
        <cfset dbtype = application.blog.getProperty("blogdbtype")>
        <cfset blog = application.blog.getProperty("name")>
        <cfset username = application.blog.getProperty("username")>
        <cfset password = application.blog.getProperty("password")>
        <cfif isDefined("URL.statsYear") AND isNumericDate(URL.statsYear)>
                <cfset statsYear = URL.statsYear>
        <cfelse>
                <cfset statsYear = year(now())>
        </cfif>
        
        <!--- get a bunch of crap --->
        <cfquery name="getTotalEntries" datasource="#dsn#" username="#username#" password="#password#">
                select  count(id) as totalentries, 
                                min(posted) as firstentry,
                                max(posted) as lastentry
                from    tblblogentries
                where   tblblogentries.blog = <cfqueryparam cfsqltype="cf_sql_varchar" value="#blog#">
                and             year(posted) = <cfqueryparam cfsqltype="cf_sql_numeric" value="#statsYear#">
        </cfquery>

        <cfquery name="getTotalSubscribers" datasource="#dsn#" username="#username#" password="#password#">
                select  count(email) as totalsubscribers 
                from    tblblogsubscribers
                where   tblblogsubscribers.blog = <cfqueryparam cfsqltype="cf_sql_varchar" value="#blog#">
                and             verified = 1
        </cfquery>

        <cfquery name="getTotalViews" datasource="#dsn#" username="#username#" password="#password#">
                select          sum(views) as total
                from            tblblogentries
                where   tblblogentries.blog = <cfqueryparam cfsqltype="cf_sql_varchar" value="#blog#">
                and             year(posted) = <cfqueryparam cfsqltype="cf_sql_numeric" value="#statsYear#">
        </cfquery>

        <cfquery name="getTopViews" datasource="#dsn#" username="#username#" password="#password#">
                select          <cfif dbtype is not "mysql">top 10</cfif> id, title, views
                from            tblblogentries
                where   tblblogentries.blog = <cfqueryparam cfsqltype="cf_sql_varchar" value="#blog#">
                and             year(posted) = <cfqueryparam cfsqltype="cf_sql_numeric" value="#statsYear#">
                order by        views desc
                <cfif dbtype is "mysql">limit 10</cfif>         
        </cfquery>
        
        <cfquery name="getTotalComments" datasource="#dsn#" username="#username#" password="#password#">
                select  count(tblblogcomments.id) as totalcomments
                from    tblblogcomments, tblblogentries
                where   tblblogcomments.entryidfk = tblblogentries.id
                and             tblblogentries.blog = <cfqueryparam cfsqltype="cf_sql_varchar" value="#blog#">
                <cfif application.commentmoderation>
                and             tblblogcomments.moderated = 1
                </cfif>
                and             year(tblblogcomments.posted) = <cfqueryparam cfsqltype="cf_sql_numeric" value="#statsYear#">
        </cfquery>
        
        <cfif application.blog.getProperty("allowtrackbacks")>
                <!--- RBB: 1/20/2006: get trackbacks --->
                <cfquery name="getTotalTrackbacks" datasource="#dsn#" username="#username#" password="#password#">
                        select count(tblblogtrackbacks.id) as totaltrackbacks
                        from    tblblogtrackbacks, tblblogentries
                        where   tblblogtrackbacks.entryid = tblblogentries.id
                        and             tblblogentries.blog = <cfqueryparam cfsqltype="cf_sql_varchar" value="#blog#">
                </cfquery>
        </cfif>
                
        <!--- gets num of entries per category --->
        <cfquery name="getCategoryCount" datasource="#dsn#" username="#username#" password="#password#">
                select  categoryid, categoryname, count(categoryidfk) as total
                from    tblblogcategories, tblblogentriescategories, tblblogentries
                where   tblblogentriescategories.categoryidfk = tblblogcategories.categoryid
                and             tblblogcategories.blog = <cfqueryparam cfsqltype="cf_sql_varchar" value="#blog#">
                and     tblblogentriescategories.entryidfk = tblblogentries.id
                and             year(tblblogentries.posted) = <cfqueryparam cfsqltype="cf_sql_numeric" value="#statsYear#">
                group by tblblogcategories.categoryid, tblblogcategories.categoryname
                <cfif dbtype is not "msaccess">
                        order by total desc
                <cfelse>
                        order by count(categoryidfk) desc
                </cfif>
        </cfquery>
        
        <!--- gets num of comments per entry, top 10 --->
        <cfquery name="topCommentedEntries" datasource="#dsn#" username="#username#" password="#password#">
                select 
                <cfif dbtype is not "mysql">top 10</cfif>
                tblblogentries.id, tblblogentries.title, count(tblblogcomments.id) as commentcount
                from                    tblblogentries, tblblogcomments
                where                   tblblogcomments.entryidfk = tblblogentries.id
                and                             tblblogentries.blog = <cfqueryparam cfsqltype="cf_sql_varchar" value="#blog#">
                <cfif application.commentmoderation>
                and                             tblblogcomments.moderated = 1
                </cfif>
                and             year(tblblogcomments.posted) = <cfqueryparam cfsqltype="cf_sql_numeric" value="#statsYear#">
                
                group by                tblblogentries.id, tblblogentries.title
                <cfif dbtype is not "msaccess">
                        order by        commentcount desc
                <cfelse>
                        order by        count(tblblogcomments.id) desc
                </cfif>
                <cfif dbtype is "mysql">limit 10</cfif>
        </cfquery>

        <!--- gets num of comments per category, top 10 --->
        <cfquery name="topCommentedCategories" datasource="#dsn#" username="#username#" password="#password#">
                select 
                <cfif dbtype is not "mysql">top 10</cfif>
                                                tblblogcategories.categoryid, 
                                                tblblogcategories.categoryname, 
                                                count(tblblogcomments.id) as commentcount
                from                    tblblogcategories, tblblogcomments, tblblogentriescategories
                where                   tblblogcomments.entryidfk = tblblogentriescategories.entryidfk
                and                             tblblogentriescategories.categoryidfk = tblblogcategories.categoryid
                and                             tblblogcategories.blog = <cfqueryparam cfsqltype="cf_sql_varchar" value="#blog#">
                and                             year(tblblogcomments.posted) = <cfqueryparam cfsqltype="cf_sql_numeric" value="#statsYear#">
                <cfif application.commentmoderation>
                and                             tblblogcomments.moderated = 1
                </cfif>         
                group by                tblblogcategories.categoryid, tblblogcategories.categoryname
                <cfif dbtype is not "msaccess">
                        order by        commentcount desc
                <cfelse>
                        order by        count(tblblogcomments.id) desc
                </cfif>
                <cfif dbtype is "mysql">limit 10</cfif>
        </cfquery>
        
        <cfif application.blog.getProperty("allowtrackbacks")>
                <!--- RBB 1/20/2006: gets num of trackbacks per entry, top 10 --->
                <cfquery name="topTrackbackedEntries" datasource="#dsn#" username="#username#" password="#password#">
                        select 
                        <cfif dbtype is not "mysql">top 10</cfif>
                        tblblogentries.id, tblblogentries.title, count(tblblogtrackbacks.id) as trackbackcount
                        from                    tblblogentries, tblblogtrackbacks
                        where                   tblblogtrackbacks.entryid = tblblogentries.id
                        and                             tblblogentries.blog = <cfqueryparam cfsqltype="cf_sql_varchar" value="#blog#">
                        and                             year(tblblogtrackbacks.created) = <cfqueryparam cfsqltype="cf_sql_numeric" value="#statsYear#">
                        group by                tblblogentries.id, tblblogentries.title
                        <cfif dbtype is not "msaccess">
                                order by        trackbackcount desc
                        <cfelse>
                                order by        count(tblblogtrackbacks.id) desc
                        </cfif>
                        <cfif dbtype is "mysql">limit 10</cfif>
                </cfquery>      
        </cfif>
                
        <cfquery name="topSearchTerms" datasource="#dsn#" username="#username#" password="#password#">
                select          
                <cfif dbtype is not "mysql">top 10</cfif>
                                        searchterm, count(searchterm) as total
                from            tblblogsearchstats
                where           blog = <cfqueryparam cfsqltype="cf_sql_varchar" value="#blog#">
                and                     year(searched) = <cfqueryparam cfsqltype="cf_sql_numeric" value="#statsYear#">

                group by        searchterm
                <cfif dbtype is not "msaccess">
                        order by        total desc
                <cfelse>
                        order by        count(searchterm) desc
                </cfif>
                <cfif dbtype is "mysql">limit 10</cfif>
        </cfquery>
                
        <cfset averageCommentsPerEntry = 0>     
        <cfif getTotalEntries.totalEntries>
                <cfset dur = dateDiff("d",getTotalEntries.firstEntry, now())>
                <cfset averageCommentsPerEntry = getTotalComments.totalComments / getTotalEntries.totalEntries>
        </cfif>
        
        <cfoutput>
        <div class="date"><b>Stats for #statsYear#</b></div>
        <div class="body">
        <a href="##generalstats">#rb("generalstats")#</a><br>
        <a href="##topviews">#rb("topviews")#</a><br>
        <a href="##categorystats">#rb("categorystats")#</a><br>
        <a href="##topentriesbycomments">#rb("topentriesbycomments")#</a><br>
        <a href="##topcategoriesbycomments">#rb("topcategoriesbycomments")#</a><br>
        <cfif application.blog.getProperty("allowtrackbacks")><a href="##topentriesbytrackbacks">#rb("topentriesbytrackbacks")#</a><br></cfif>
        <a href="##topsearchterms">#rb("topsearchterms")#</a><br>
        </div>
        
        <p />
        
        <div class="date"><a name="generalstats"></a><b>#rb("generalstats")#</b></div>
        <div class="body">
        <table border="1" width="100%">
                <tr>
                        <td><b>#rb("totalnumentries")#:</b></td>
                        <td>#getTotalEntries.totalEntries#</td>
                </tr>                   
                <tr>
                        <td><b>#rb("firstentry")#:</b></td>
                        <td><cfif len(getTotalEntries.firstEntry)>#dateFormat(getTotalEntries.firstEntry,"mm/dd/yy")#<cfelse>&nbsp;</cfif></td>
                </tr>
                <tr>
                        <td><b>#rb("lastentry")#:</b></td>
                        <td><cfif len(getTotalEntries.lastEntry)>#dateFormat(getTotalEntries.lastEntry,"mm/dd/yy")#<cfelse>&nbsp;</cfif></td>
                </tr>
                <tr>
                        <td><b>#rb("totalcomments")#:</b></td>
                        <td>#getTotalComments.totalComments#</td>
                </tr>
                <tr>
                        <td><b>#rb("avgcommentsperentry")#:</b></td>
                        <td>#numberFormat(averageCommentsPerEntry,"999.99")#</td>
                </tr>
                <cfif application.blog.getProperty("allowtrackbacks")>
                <!--- RBB: 1/20/06: Added total trackbacks --->
                <tr>
                        <td><b>#rb("totaltrackbacks")#:</b></td>
                        <td>#getTotalTrackbacks.totalTrackbacks#</td>
                </tr>           
                </cfif>
                <tr>
                        <td><b>#rb("totalviews")#:</b></td>
                        <td>#getTotalViews.total#</td>
                </tr>
                <tr>
                        <td width="50%"><b>Average Views:</b></td>
                        <td>
                                <cfif gettotalentries.totalentries gt 0 and gettotalviews.total gt 0>
                                #numberFormat(gettotalviews.total/gettotalentries.totalentries,"999.99")#
                                <cfelse>
                                0
                                </cfif>
                        </td>
                </tr>
                <tr>
                        <td><b>#rb("totalsubscribers")#:</b></td>
                        <td>#getTotalSubscribers.totalsubscribers#</td>
                </tr>
                
        </table>
        </div>

        <p />
        
        <div class="date"><a name="topviews"></a><b>#rb("topviews")#</b></div>
        <div class="body">
        <table border="1" width="100%">
                <cfloop query="getTopViews">
                <tr>
                        <td><b><a href="#application.blog.makeLink(id)#" rel="nofollow">#title#</a></b></td>
                        <td>#views#</td>
                </tr>
                </cfloop>
        </table>
        </div>
        
        <p />
        
        <div class="date"><a name="categorystats"></a><b>#rb("categorystats")#</b></div>
        <div class="body">
        <table border="1" width="100%">
                <cfloop query="getCategoryCount">
                <tr>
                        <td>#categoryname#</td>
                        <td>#total#</td>
                </tr>
                </cfloop>
        </table>
        </div>
        
        <p />
        
        <div class="date"><a name="topentriesbycomments"></a><b>#rb("topentriesbycomments")#</b></div>
        <div class="body">
        <table border="1" width="100%">
                <cfloop query="topCommentedEntries">
                <tr>
                        <td><b><a href="#application.blog.makeLink(id)#" rel="nofollow">#title#</a></b></td>
                        <td>#commentCount#</td>
                </tr>
                </cfloop>
        </table>
        </div>
        
        <p />
        
        <div class="date"><a name="topcategoriesbycomments"></a><b>#rb("topcategoriesbycomments")#</b></div>
        <div class="body">
        <table border="1" width="100%">
                <cfloop query="topCommentedCategories">
                        <!--- 
                                This is ugly code.
                                I want to find the avg number of posts
                                per entry for this category.
                        --->
                        <cfquery name="getTotalForThisCat" dbtype="query">
                                select  total
                                from    getCategoryCount
                                where   categoryid = '#categoryid#'
                        </cfquery>
                        <cftry>
                        <cfif getTotalForThisCat.total neq 0 and getTotalForThisCat.total neq "">
                                <cfset avg = commentCount / getTotalForThisCat.total>
                                <cfset avg = numberFormat(avg,"___.___")>
                        <cfelse>
                                <cfset avg = 0>
                        </cfif>
                        <tr>
                                <td><b><a href="index.cfm?mode=cat&catid=#categoryid#" rel="nofollow">#categoryname#</a></b></td>
                                <td>#commentCount# (#rb("avgcommentperentry")#: #avg#)</td>
                        </tr>
                        <cfcatch><cfdump var="#getTotalForThisCat#"></cfcatch>
                        </cftry>
                </cfloop>
        </table>
        </div>

        <p />
        
        <cfif application.blog.getProperty("allowtrackbacks")>
        <!--- RBB 1/20/2006: Added top entriex by trackbacks --->
        <div class="date"><a name="topentriesbytrackbacks"></a><b>#rb("topentriesbytrackbacks")#</b></div>
        <div class="body">
        <table border="1" width="100%">
                <cfloop query="topTrackbackedEntries">
                <tr>
                        <td><b><a href="#application.blog.makeLink(id)#" rel="nofollow">#title#</a></b></td>
                        <td>#trackbackCount#</td>
                </tr>
                </cfloop>
        </table>
        </div>
        
        <p />
        </cfif>
        
        <div class="date"><a name="topsearchterms"></a><b>#rb("topsearchterms")#</b></div>
        <div class="body">
        <table border="1" width="100%">
                <cfloop query="topSearchTerms">
                <tr>
                        <td><b><a href="#application.rooturl#/index.cfm?mode=search&search=#urlEncodedFormat(searchterm)#" rel="nofollow">#searchterm#</a></b></td>
                        <td>#total#</td>
                </tr>
                </cfloop>
        </table>
        </div>
        
        </cfoutput>
        
</cfmodule>

<cfsetting enablecfoutputonly=false>