Hi!
I've promised to review my query updates for PostgreSQL again and now I'm
sending the corrected queries.
In the attached file you'll find all my changes and where to place them.
Also I've made an additional feature to view statistics for pages and branch
over the past 4 month (by now it was only possible for the past 4 weeks).
ATTENTION:
I'd to do some database-changed:
In the table statsHours I'd to change the type of field "hour" from int4 to
char(2) and add leading zeros to the numbers < 10. Otherwise a join won't be
possible (and you cannot get working statistics for times earlier than 10
o'clock in the morning).
I also added a table statsdaymonth with the field "day" (char(2)) and
numbers from "01" to "31" to make the new statistics possible (as mentioned
above).
I hope you can use my improvements (I also improved the performance of the
queries)!
Bye,
Fritz
--
Weitersagen: GMX DSL-Flatrates mit Tempo-Garantie!
Ab 4,99 Euro/Monat: http://www.gmx.net/de/go/dsl
FILE: stats.cfc:
<!--- FD: new methods --->
<cffunction name="getPageStatsByMonth" access="public" returntype="query">
<cfargument name="pageId" type="string" required="false">
<cfargument name="day" type="date" required="true">
<cfargument name="showAll" type="boolean" required="false"
default="false">
<cfargument name="dsn" type="string" required="false"
default="#application.dsn#">
<cfinclude template="_stats/getPageStatsByMonth.cfm">
<cfreturn qGetPageStatsByMonth>
</cffunction>
<cffunction name="getBranchStatsByMonth" access="public" returntype="query">
<cfargument name="navid" type="string" required="false">
<cfargument name="day" type="date" required="true">
<cfargument name="showAll" type="boolean" required="false"
default="false">
<cfargument name="dsn" type="string" required="false"
default="#application.dsn#">
<cfinclude template="_stats/getBranchStatsByMonth.cfm">
<cfreturn qGetBranchStatsByMonth>
</cffunction>
--------------------------------------------------------
FILE: getBranchStatsByDay.cfm:
<cfcase value="postgresql">
<!---
adapted by Friedrich Dimmel ([EMAIL PROTECTED])
--->
<cfset today = DateFormat(arguments.day, "YYYY-MM-DD")>
<cfquery datasource="#application.dsn#" name="qGetPageStatsByDay">
SELECT DISTINCT hour, TO_CHAR(j.logdatetime,'HH24') as
loginhour, count(j.logId) as count_views
from #application.dbowner#statsHours
LEFT JOIN (
SELECT logdatetime, logID FROM stats
WHERE 1 = 1
<cfif not arguments.showAll>
AND navid IN (<cfif
qDescendants.recordcount>#QuotedValueList(qDescendants.objectid)#,</cfif>'#arguments.navid#')
</cfif>
) j ON TO_CHAR(j.logdatetime,'HH24') = statsHours.hour
AND TO_CHAR(j.logdatetime, 'YYYY-MM-DD') = '#today#'
GROUP BY hour, loginhour
ORDER BY hour
</cfquery>
</cfcase>
--------------------------------------------------------
FILE: getBranchStatsByWeek.cfm:
<cfcase value="postgresql">
<!---
adapted by Friedrich Dimmel ([EMAIL PROTECTED])
--->
<cfset thisWeek = DateFormat(arguments.day, "yyyy-mm-dd")>
<cfset nextWeek = DateFormat(DateAdd('d', '7', arguments.day),
"yyyy-mm-dd")>
<cfquery datasource="#arguments.dsn#" name="qGetPageStatsByWeek">
SELECT day, name, COUNT(j.logId) AS count_logins
FROM #application.dbowner#statsDays
LEFT JOIN (
SELECT TO_CHAR(logdatetime, 'D') AS loginday, logID,
logdatetime
FROM stats
WHERE 1 = 1
<cfif not arguments.showAll>
AND navid IN (<cfif
qDescendants.recordCount>#QuotedValueList(qDescendants.objectID)#,</cfif>'#arguments.navid#')
</cfif>
) j ON (j.loginday = day)
AND (TO_CHAR(j.logdatetime, 'YYYY-MM-DD') >= '#thisWeek#')
AND (TO_CHAR(j.logdatetime, 'YYYY-MM-DD') < '#nextWeek#')
GROUP BY day, name
ORDER BY day
</cfquery>
</cfcase>
--------------------------------------------------------
FILE: getBranchStatsByMonth.cfm:
<cfcase value="postgresql">
<!---
adapted by Friedrich Dimmel ([EMAIL PROTECTED])
--->
<cfset thisMonth = DateFormat(arguments.day, "yyyy-mm-dd")>
<cfset nextMonth = DateFormat(DateAdd('m', '1', arguments.day),
"yyyy-mm-dd")>
<cfquery datasource="#arguments.dsn#" name="qGetPageStatsByMonth">
SELECT day, COUNT(j.logID) AS count_logins
FROM #application.dbowner#statsDayMonth
LEFT JOIN (
SELECT logID, logdatetime, TO_CHAR(logdatetime,'DD') AS
loginday
FROM stats
WHERE logdatetime >= '#thisMonth#'
AND logdatetime < '#nextMonth#'
<cfif not arguments.showAll>
AND pageid = '#arguments.pageId#'
</cfif>
ORDER BY loginday
) j ON (TO_CHAR(j.logdatetime,'DD') = day)
GROUP BY day
ORDER BY day
</cfquery>
</cfcase>
--------------------------------------------------------
FILE: getPageStatsByDay.cfm:
<cfcase value="postgresql">
<!---
adapted by Friedrich Dimmel ([EMAIL PROTECTED])
--->
<cfset thisDay = DateFormat(arguments.day, "YYYY-MM-DD")>
<cfquery datasource="#arguments.dsn#" name="qGetPageStatsByDay">
SELECT hour, COUNT(j.logID) AS count_views
FROM #application.dbowner#statsHours
LEFT JOIN (
SELECT logdatetime, logID, TO_CHAR(logdatetime, 'HH24')
AS loginhour
FROM stats
WHERE 1=1
<cfif not arguments.showAll>
AND pageid = '#arguments.pageId#'
</cfif>
AND TO_CHAR(logdatetime, 'YYYY-MM-DD') = '#thisDay#'
) j ON (j.loginhour = statsHours.hour)
GROUP BY hour
ORDER BY hour
</cfquery>
</cfcase>
--------------------------------------------------------
FILE: getPageStatsByWeek.cfm:
<cfcase value="postgresql">
<!---
adapted by Friedrich Dimmel ([EMAIL PROTECTED])
--->
<cfset thisWeek = DateFormat(arguments.day, "yyyy-mm-dd")>
<cfset nextWeek = DateFormat(DateAdd('d', '7', arguments.day),
"yyyy-mm-dd")>
<cfquery datasource="#arguments.dsn#" name="qGetPageStatsByWeek">
SELECT day, name, COUNT(j.logId) AS count_logins
FROM #application.dbowner#statsDays
LEFT JOIN (
SELECT TO_CHAR(logdatetime, 'D') AS loginday, logID,
logdatetime
FROM stats
WHERE 1 = 1
<cfif not arguments.showAll>
AND pageid = '#arguments.pageId#'
</cfif>
) j ON (j.loginday = day)
AND (TO_CHAR(j.logdatetime, 'YYYY-MM-DD') >= '#thisWeek#')
AND (TO_CHAR(j.logdatetime, 'YYYY-MM-DD') < '#nextWeek#')
GROUP BY day, name
ORDER BY day
</cfquery>
</cfcase>
--------------------------------------------------------
FILE: getPageStatsByMonth.cfm:
<cfcase value="postgresql">
<!---
adapted by Friedrich Dimmel ([EMAIL PROTECTED])
--->
<cfset thisMonth = DateFormat(arguments.day, "yyyy-mm-dd")>
<cfset nextMonth = DateFormat(DateAdd('m', '1', arguments.day),
"yyyy-mm-dd")>
<cfquery datasource="#arguments.dsn#" name="qGetPageStatsByMonth">
SELECT day, COUNT(j.logID) AS count_logins
FROM #application.dbowner#statsDayMonth
LEFT JOIN (
SELECT logID, logdatetime, TO_CHAR(logdatetime,'DD') AS
loginday
FROM stats
WHERE logdatetime >= '#thisMonth#'
AND logdatetime < '#nextMonth#'
<cfif not arguments.showAll>
AND pageid = '#arguments.pageId#'
</cfif>
ORDER BY loginday
) j ON (TO_CHAR(j.logdatetime,'DD') = day)
GROUP BY day
ORDER BY day
</cfquery>
</cfcase>
-------------------------------------------------------
FILE: edittabStats.cfm
-------------------------- NOW FOR THE DMNAVIGATION STATS
<cfset q1Date = CreateDate(Year(Now()), Month(Now()), 1)>
<cfset q2Date = DateAdd('m', -1, q1Date)>
<cfset q3Date = DateAdd('m', -1, q2Date)>
<cfset q4Date = DateAdd('m', -1, q3Date)>
<!--- Oracle conversion not complete yet for this method --->
<cfif NOT application.dbType is "ora">
<cfscript>
q1 =
application.factory.oStats.getBranchStatsByMonth(navid=url.objectid,day=q1Date);
q2 =
application.factory.oStats.getBranchStatsByMonth(navid=url.objectid,day=q2Date);
q3 =
application.factory.oStats.getBranchStatsByMonth(navid=url.objectid,day=q3Date);
q4 =
application.factory.oStats.getBranchStatsByMonth(navid=url.objectid,day=q4Date);
</cfscript>
<cfoutput>
<p></p>
<div class="formtitle">Ansichten pro Tag während der letzten 4 Monate</div>
<cfchart
format="flash"
chartHeight="400"
chartWidth="600"
scaleFrom="0"
showXGridlines = "yes"
showYGridlines = "yes"
seriesPlacement="default"
showBorder = "no"
fontsize="12"
font="arialunicodeMS"
labelFormat = "number"
xAxisTitle = "Tag"
yAxisTitle =
"#application.adminBundle[session.dmProfile.locale].viewNumbers#"
show3D = "yes"
xOffset = "0.15"
yOffset = "0.15"
rotated = "no"
showLegend = "no"
tipStyle = "MouseOver">
<cfchartseries type="bar" query="q1" itemcolumn="day"
valuecolumn="count_logins" serieslabel="dieses Monat"
paintstyle="shade"></cfchartseries>
<cfchartseries type="bar" query="q2" itemcolumn="day"
valuecolumn="count_logins" serieslabel="letztes Monat"
paintstyle="shade"></cfchartseries>
<cfchartseries type="bar" query="q3" itemcolumn="day"
valuecolumn="count_logins" serieslabel="vorletztes Monat"
paintstyle="shade"></cfchartseries>
<cfchartseries type="bar" query="q4" itemcolumn="day"
valuecolumn="count_logins" serieslabel="vor 3 Monaten"
paintstyle="shade"></cfchartseries>
</cfchart>
</cfoutput>
</cfif>
--------------------- AND NOW PAGE STATS FOR THE LAST 4 MONTHS
<cfset q1Date = CreateDate(Year(Now()), Month(Now()), 1)>
<cfset q2Date = DateAdd('m', -1, q1Date)>
<cfset q3Date = DateAdd('m', -1, q2Date)>
<cfset q4Date = DateAdd('m', -1, q3Date)>
<!--- Oracle conversion not complete yet for this method --->
<cfif NOT application.dbType is "ora">
<cfscript>
q1 =
application.factory.oStats.getPageStatsByMonth(pageid=url.objectid,day=q1Date);
q2 =
application.factory.oStats.getPageStatsByMonth(pageid=url.objectid,day=q2Date);
q3 =
application.factory.oStats.getPageStatsByMonth(pageid=url.objectid,day=q3Date);
q4 =
application.factory.oStats.getPageStatsByMonth(pageid=url.objectid,day=q4Date);
</cfscript>
<cfoutput>
<p></p>
<div class="formtitle">Ansichten pro Tag während der letzten 4 Monate</div>
<cfchart
format="flash"
chartHeight="400"
chartWidth="600"
scaleFrom="0"
showXGridlines = "yes"
showYGridlines = "yes"
seriesPlacement="default"
showBorder = "no"
fontsize="12"
font="arialunicodeMS"
labelFormat = "number"
xAxisTitle = "Tag"
yAxisTitle =
"#application.adminBundle[session.dmProfile.locale].viewNumbers#"
show3D = "yes"
xOffset = "0.15"
yOffset = "0.15"
rotated = "no"
showLegend = "no"
tipStyle = "MouseOver">
<cfchartseries type="bar" query="q1" itemcolumn="day"
valuecolumn="count_logins" serieslabel="dieses Monat"
paintstyle="shade"></cfchartseries>
<cfchartseries type="bar" query="q2" itemcolumn="day"
valuecolumn="count_logins" serieslabel="letztes Monat"
paintstyle="shade"></cfchartseries>
<cfchartseries type="bar" query="q3" itemcolumn="day"
valuecolumn="count_logins" serieslabel="vorletztes Monat"
paintstyle="shade"></cfchartseries>
<cfchartseries type="bar" query="q4" itemcolumn="day"
valuecolumn="count_logins" serieslabel="vor 3 Monaten"
paintstyle="shade"></cfchartseries>
</cfchart>
</cfoutput>
</cfif>
---
You are currently subscribed to farcry-dev as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
Aussie Macromedia Developers: http://lists.daemon.com.au/
