Hi.
There are massive problems in the page (and nav) stats in farcry when using
PostgreSQL.
Some Queries are not correct (as marked in the source code). I've reviewed
these queries and corrected them.
Within my mail, I'll add these files.
All files should be placed into:
/farcry/farcry_core/packages/farcry/_stats
I've documentated the changes (at the top of the files, and where my changes
are placed).
I would be pleased finding my changes in the CVS / next release.
Bye,
Fritz
--
�=================================
| Fritz Dimmel
| Gassnergasse 14
| 2020 Hollabrunn
| AUSTRIA
|---------------------------------
| phone: +43-2952-3231
| mobile: +43-650-6779776
| web: http://www.dimmel.at
| mail: [EMAIL PROTECTED]
|---------------------------------
| Enterity Cantabs
|
| *punkrockgrungesemiska*
| aus dem Weinviertel
�---------------------------------
| http://www.enterity-cantabs.com
�=================================
// get descendants over object
qDescendants = request.factory.oTree.getDescendants(arguments.navId);
select distinct hour, TO_CHAR(fq.logdatetime,'hh') as loginhour, count(fq.logId) as count_views
from #application.dbowner#statsHours
left join (
select * from stats
where 1 = 1
AND navid IN (#QuotedValueList(qDescendants.objectid)#,'#arguments.navid#')
)fq on TO_CHAR(fq.logdatetime,'hh') = statsHours.hour
and TO_CHAR(fq.logdatetime,'dd' ) = #DatePart("d", arguments.day)# and TO_CHAR(fq.logdatetime,'mm') = #DatePart("m", arguments.day)# and TO_CHAR(fq.logdatetime,'yyyy') = #DatePart("yyyy", arguments.day)#
group by hour, TO_CHAR(fq.logdatetime,'hh')
order by 1
SELECT DISTINCT hour, TO_CHAR(fq.logdatetime,'HH24') as loginhour, count(fq.logId) as count_views
from #application.dbowner#statsHours
LEFT JOIN (
SELECT * FROM stats
WHERE 1 = 1
AND navid IN (#QuotedValueList(qDescendants.objectid)#,'#arguments.navid#')
) fq ON TO_CHAR(fq.logdatetime,'HH24') = statsHours.hour
AND TO_CHAR(fq.logdatetime,'DD' ) = '#DateFormat(arguments.day, "dd")#' AND TO_CHAR(fq.logdatetime, 'MM') = '#DateFormat(arguments.day, "mm")#' AND TO_CHAR(fq.logdatetime,'YYYY') = '#DateFormat(arguments.day, "yyyy")#'
GROUP BY hour, TO_CHAR(fq.logdatetime,'HH24')
ORDER BY 1
DROP TABLE IF EXISTS tblTemp1
create temporary table `tblTemp1`
(
`LOGID` VARCHAR(255) NOT NULL ,
`LOGDATETIME` DATETIME NOT NULL
)
INSERT INTO tblTemp1 (LOGID,LOGDATETIME)
SELECT LOGID, LOGDATETIME FROM #application.dbowner#stats
WHERE 1 = 1
AND navid IN (#QuotedValueList(qDescendants.objectid)#,'#arguments.navid#')
select distinct hour, HOUR(fq.logdatetime) as loginhour, count(fq.logId) as count_views
from #application.dbowner#statsHours
left join tblTemp1 fq on HOUR(fq.logdatetime) = statsHours.hour
and DAYOFMONTH(fq.logdatetime) = #DatePart("d", arguments.day)# and MONTH(fq.logdatetime) = #DatePart("m", arguments.day)# and YEAR(fq.logdatetime) = #DatePart("yyyy", arguments.day)#
group by hour, loginhour
order by 1
select distinct hour, datepart(hh, fq.logdatetime) as loginhour, count(fq.logId) as count_views
from #application.dbowner#statsHours
left join (
select * from stats
where 1 = 1
AND navid IN (#QuotedValueList(qDescendants.objectid)#,'#arguments.navid#')
)fq on datepart(hh, fq.logdatetime) = statsHours.hour
and datepart(dd, fq.logdatetime) = #DatePart("d", arguments.day)# and datepart(mm, fq.logdatetime) = #DatePart("m", arguments.day)# and datepart(yyyy, fq.logdatetime) = #DatePart("yyyy", arguments.day)#
group by hour, datepart(hh, fq.logdatetime)
order by 1 ;
select distinct hour, TO_CHAR(fq.logdatetime,'hh') as loginhour, count(fq.logId) as count_views
from #application.dbowner#statsHours
left join (
select * from stats
where 1 = 1
and pageid = '#arguments.pageId#'
)fq on TO_CHAR(fq.logdatetime,'hh') = statsHours.hour
and TO_CHAR(fq.logdatetime,'dd' ) = #DatePart("d", arguments.day)# and TO_CHAR(fq.logdatetime,'mm') = #DatePart("m", arguments.day)# and TO_CHAR(fq.logdatetime,'yyyy') = #DatePart("yyyy", arguments.day)#
group by hour, TO_CHAR(fq.logdatetime,'hh')
order by 1
SELECT DISTINCT hour, TO_CHAR(fq.logdatetime,'HH24') as loginhour, count(fq.logId) as count_views
from #application.dbowner#statsHours
LEFT JOIN (
SELECT * FROM stats
WHERE 1 = 1
AND pageid = '#arguments.pageId#'
) fq ON TO_CHAR(fq.logdatetime,'HH24') = statsHours.hour
AND TO_CHAR(fq.logdatetime,'DD' ) = '#DateFormat(arguments.day, "dd")#' AND TO_CHAR(fq.logdatetime, 'MM') = '#DateFormat(arguments.day, "mm")#' AND TO_CHAR(fq.logdatetime,'YYYY') = '#DateFormat(arguments.day, "yyyy")#'
GROUP BY hour, TO_CHAR(fq.logdatetime,'HH24')
ORDER BY 1
DROP TABLE IF EXISTS tblTemp1
create temporary table `tblTemp1`
(
`LOGID` VARCHAR(255) NOT NULL ,
`LOGDATETIME` DATETIME NOT NULL
)
INSERT INTO tblTemp1 (LOGID,LOGDATETIME)
SELECT LOGID, LOGDATETIME FROM #application.dbowner#stats
WHERE 1 = 1
and pageid = '#arguments.pageId#'
select distinct hour, HOUR(fq.logdatetime) as loginhour, count(fq.logId) as count_views
from #application.dbowner#statsHours
left join tblTemp1 fq on HOUR(fq.logdatetime) = statsHours.hour
and DAYOFMONTH(fq.logdatetime) = #DatePart("d", arguments.day)# and MONTH(fq.logdatetime) = #DatePart("m", arguments.day)# and YEAR(fq.logdatetime) = #DatePart("yyyy", arguments.day)#
group by hour, loginhour
order by 1
select distinct hour, datepart(hh, fq.logdatetime) as loginhour, count(fq.logId) as count_views
from #application.dbowner#statsHours
left join (
select * from stats
where 1 = 1
and pageid = '#arguments.pageId#'
)fq on datepart(hh, fq.logdatetime) = statsHours.hour
and datepart(dd, fq.logdatetime) = #DatePart("d", arguments.day)# and datepart(mm, fq.logdatetime) = #DatePart("m", arguments.day)# and datepart(yyyy, fq.logdatetime) = #DatePart("yyyy", arguments.day)#
group by hour, datepart(hh, fq.logdatetime)
order by 1 ;
select distinct day, statsDays.name,TO_CHAR(fq.logdatetime,'dy') as loginday, count(fq.logId) as count_logins
from #application.dbowner#statsDays
left join (
select * from stats
where 1 = 1
AND navid IN (#QuotedValueList(qDescendants.objectid)#,'#arguments.navid#')
)fq on UPPER(TO_CHAR(fq.logdatetime,'dy')) = UPPER(SUBSTR(statsDays.day,1,3))
and (fq.logdatetime - TO_DATE('#arguments.day#','dd/mon/yy') <=0) and (TO_DATE('#dateadd('d','7',arguments.day)#','dd/mon/yy') - fq.logdatetime >=0))
group by day, statsDays.name, TO_CHAR(fq.logdatetime,'dy')
order by 1
SELECT DISTINCT day, statsDays.name, TO_CHAR(fq.logdatetime, 'D') AS loginday, COUNT(fq.logId) AS count_logins
FROM #application.dbowner#statsDays
LEFT JOIN (
SELECT * FROM stats
WHERE 1 = 1
AND navid IN (#QuotedValueList(qDescendants.objectID)#,'#arguments.navid#')
) fq ON (UPPER(TO_CHAR(fq.logdatetime, 'D')) = UPPER(SUBSTR(statsDays.day, 1, 3)))
AND (fq.logdatetime >= '#thisWeek#') AND ('#nextWeek#' >= fq.logdatetime)
GROUP BY day, statsDays.name, TO_CHAR(fq.logdatetime, 'D')
ORDER BY 1
DROP TABLE IF EXISTS tblTemp1
create temporary table `tblTemp1`
(
`LOGID` VARCHAR(255) NOT NULL ,
`LOGDATETIME` DATETIME NOT NULL
)
INSERT INTO tblTemp1 (LOGID,LOGDATETIME)
SELECT LOGID, LOGDATETIME FROM #application.dbowner#stats
WHERE 1 = 1
AND navid IN (#QuotedValueList(qDescendants.objectid)#,'#arguments.navid#')
select distinct day, statsDays.name,DAYOFWEEK(fq.logdatetime) as loginday, count(fq.logId) as count_logins
from #application.dbowner#statsDays
left join tblTemp1 fq on DAYOFWEEK(fq.logdatetime) = statsDays.day
and fq.logdatetime - DATE_ADD(#arguments.day#, INTERVAL 0 DAY) >=0 and DATE_ADD(#arguments.day#, INTERVAL 7 DAY) - fq.logdatetime >=0
group by day, statsDays.name, DAYOFWEEK(fq.logdatetime)
order by 1
-- now join our days table to the fqaudit table, to get the set we want. Note the query requires a day, month and year to be specified, for
-- which we return the logins by day (nulls are returned if no logins during the day )
select distinct day, statsDays.name,datepart(dw, fq.logdatetime) as loginday, count(fq.logId) as count_logins
from #application.dbowner#statsDays
left join (
select * from stats
where 1 = 1
AND navid IN (#QuotedValueList(qDescendants.objectid)#,'#arguments.navid#')
)fq on datepart(dw, fq.logdatetime) = statsDays.day
and datediff(day,fq.logdatetime,#createodbcdate(arguments.day)#) <=0 and datediff(day,fq.logdatetime,#createodbcdate(arguments.day+7)#) >=0
group by day, statsDays.name, datepart(dw, fq.logdatetime)
order by 1
---
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/
