Statistics: number of visitors per week is missing

Creating and modifying plugins.
Post Reply
jwoude
Regular
Posts: 10
Joined: Sun May 12, 2013 3:20 pm

Statistics: number of visitors per week is missing

Post by jwoude » Sun May 12, 2013 3:39 pm

Hi all,

Today I upgraded from 1.6.2 to 1.7.
All is fine, no upgrade problems. :D

However, I have a little issue with the statistics plugin which is still not working in 1.7 (did not work in 1.6.2 either).
So I am reporting it now:

The number of visitors per week is not showing at all. I am using Postgresql as the backend, so probably it has to do with that. I have messed with the code in the plugin myself, but to no avail.

The number of visitors this month and today are showing just fine.

The relevant lines of code (in serendipity_plugin_statistics.php) are:

Code: Select all

            
if (serendipity_db_bool($this->get_config('show_weekvisitors'))) {
   $res = serendipity_db_query("SELECT sum(visits) AS weekvisitors FROM {$serendipity['dbPrefix']}visitors_count WHERE CONCAT(year,month,day) >= '".$lastmonday."' AND CONCAT(year,month,day) <= '".$nextsunday."'", true, 'assoc');
   if (is_array($res) && isset($res['weekvisitors'])) {
      $content .= '<div class="stat_weekhvisitors">' . sprintf($this->get_config('text_weekvisitors'), '<span class="stat_number">' . $res['weekvisitors'] . '</span>') . "</div>\n";
   }
}
Does anyone recognize this and perhaps even know of a solution?

Regards
Jos

User avatar
Timbalu
Regular
Posts: 4598
Joined: Sun May 02, 2004 3:04 pm

Re: Statistics: number of visitors per week is missing

Post by Timbalu » Sun May 12, 2013 4:22 pm

I just had a quick look into ... v1.50

I am using MySQL and I have to state I have never recognized there should be a number of visitors per week even if I put on the showall option (then the source code does not show any weekvisitors div). :shock:

I would say it is this:

Code: Select all

if (serendipity_db_bool($this->get_config('show_weekvisitors'))) {
I do not have an option show_weekvisitors!

We will correct this.
Thank you.

EDIT:
Sorry - forget my comment - I missed that you where talking about the serendipity_plugin_statistics, which is frontend view only.
Do you still state 'weekvisitors' does not show up in the frontend sidebar, if enabled?

Edit2:
If this is still an issue with PostgreSQL, try this:
The concat operator is ||, so 'year' || 'month' || 'day' should work - or update to PostgreSQL >= 9.1 which should know about CONCAT. Please post, if this could help?
Regards,
Ian

Serendipity Styx Edition and additional_plugins @ https://ophian.github.io/ @ https://github.com/ophian

jwoude
Regular
Posts: 10
Joined: Sun May 12, 2013 3:20 pm

Re: Statistics: number of visitors per week is missing

Post by jwoude » Sun May 12, 2013 6:03 pm

Hi Ian,

I am using PostgreSQL 9.1.9, so CONCAT is not the problem. I did try the || option you suggested, but it did not work. And yes, I am talking about the frontend sidebar.

Visitors in this week is enabled, just as visitors this month and today. Only the last two show up.

Regards,

Jos

User avatar
Timbalu
Regular
Posts: 4598
Joined: Sun May 02, 2004 3:04 pm

Re: Statistics: number of visitors per week is missing

Post by Timbalu » Sun May 12, 2013 6:27 pm

There is a cache file involved. May it help to clean this up first? (templates_c/statistics_cache.html)

From first sight, I cannot see something that looks like a bug... Could you also get into frontends sidebar browser sourcecode and have a look if at least the <div class="stat_weekhvisitors"> is available?

Or use phpmyadmin or console and (rewrite to your table prefix):

Code: Select all

SELECT sum(visits) AS weekvisitors FROM serendipity_visitors_count WHERE CONCAT(year,month,day) >= 1367857256 AND CONCAT(year,month,day) <= 1368980456
and see what it returns?
(Which is $lastmonday = 06.05.2013 and $nextsunday = 19.05.2013.)

edit: ups, I used unixtimestamps here, so possibly you need to

Code: Select all

SELECT sum(visits) AS weekvisitors FROM serendipity_visitors_count WHERE CONCAT(year,month,day) >= FROM_UNIXTIME(1367857256) AND CONCAT(year,month,day) <= FROM_UNIXTIME(1368980456)
Regards,
Ian

Serendipity Styx Edition and additional_plugins @ https://ophian.github.io/ @ https://github.com/ophian

jwoude
Regular
Posts: 10
Joined: Sun May 12, 2013 3:20 pm

Re: Statistics: number of visitors per week is missing

Post by jwoude » Sun May 12, 2013 7:00 pm

Hi Ian,

Yes, I already deleted the cache file, it did not work.
For good measure, I also deleted the entire mod_pagespeed cache, it did not work.
See attached picture. (I commented out the conditional if so that you can see the visitor(s) this week line)

I will look at the source code later.

Thanks for looking into this!

Jos
Attachments
statistics.jpg
statistics.jpg (7.78 KiB) Viewed 5789 times

User avatar
Timbalu
Regular
Posts: 4598
Joined: Sun May 02, 2004 3:04 pm

Re: Statistics: number of visitors per week is missing

Post by Timbalu » Sun May 12, 2013 8:56 pm

So you have the output, but not the sum from the select!

I have discovered until now that CONCAT(year,month,day) returns 2013512 for today, missing the month and day to be returned as ('%m' and '%d') a 2-digit format.

Now, while the select works with

Code: Select all

SELECT sum(visits) AS weekvisitors FROM s9y_visitors_count WHERE CONCAT(year,month,day) >= 201356 AND CONCAT(year,month,day) <= 2013519
we should find a way around.

The best would be to rework storing to the database, (using int, or something) but this isn't an option if working with old data, until there is an automated conversion, so we need to change on line 183

Code: Select all

            $lastmonday = date('Ymd', strtotime('last monday'));
            $nextsunday = date('Ymd', strtotime('next sunday'));
            if (date('w', strtotime('today') ) == "1" ) { // now it is monday
                $lastmonday = date('Ymd', strtotime('today'));
            } else if (date('w', strtotime('today') ) == "0" ) { // now it is sunday
                $nextsunday = date('Ymd', strtotime('today'));
            }
to

Code: Select all

            $lastmonday = date('Ynj', strtotime('last monday'));
            $nextsunday = date('Ynj', strtotime('next sunday'));
            if (date('w', strtotime('today') ) == "1" ) { // now it is monday
                $lastmonday = date('Ynj', strtotime('today'));
            } else if (date('w', strtotime('today') ) == "0" ) { // now it is sunday
                $nextsunday = date('Ynj', strtotime('today'));
            }
which should do the trick.

This turns Ymd into Ynj, which is 201356 and 2013519. Please give that a try. ;-)
Regards,
Ian

Serendipity Styx Edition and additional_plugins @ https://ophian.github.io/ @ https://github.com/ophian

jwoude
Regular
Posts: 10
Joined: Sun May 12, 2013 3:20 pm

Re: Statistics: number of visitors per week is missing

Post by jwoude » Sun May 12, 2013 9:01 pm

Hi Ian,

I grepped <div class="stat_weekhvisitors"> and only found it referenced in the statistics_cache.html and serendipity_plugin_statistics.php files, nowhere else.
Same goes for <div class="stat_dayhvisitors"> and that works ok. So I am not sure if this is meaningful.

I tired the two variations with the unix timestamps in stead of $lastmonday and $nextsunday.

I made sure to delete the cache and reloaded the blog. No luck, the week visitors remain blank.

I did look into the PostgresSQL tables directly using the pgsql utility. The relevant columns (year, month, day) in table visitors_count are populated with a lot of data.

So, this remains a mystery...

Regards,

Jos

User avatar
Timbalu
Regular
Posts: 4598
Joined: Sun May 02, 2004 3:04 pm

Re: Statistics: number of visitors per week is missing

Post by Timbalu » Sun May 12, 2013 9:15 pm

Did you really read my last post?!!! ;-)
Regards,
Ian

Serendipity Styx Edition and additional_plugins @ https://ophian.github.io/ @ https://github.com/ophian

jwoude
Regular
Posts: 10
Joined: Sun May 12, 2013 3:20 pm

Re: Statistics: number of visitors per week is missing

Post by jwoude » Sun May 12, 2013 9:23 pm

Hi Ian,

Sorry, our last posts crossed. I have now tried the Ynj variation with a fresh copy of the original source file, cleared the cache and reloaded.

Sorry to say that the week entry remains blank!

Regards
Jos

jwoude
Regular
Posts: 10
Joined: Sun May 12, 2013 3:20 pm

Re: Statistics: number of visitors per week is missing

Post by jwoude » Mon May 13, 2013 7:17 am

Hi Ian,

Guess what, this morning I looked and the count of weekly visitors is working!
Obviously, it was reset last night (Sunday), so I didn't notice it.

The number of visitors online is also shown now. I did not realize it was there until I loooked at the source code, added a cast necessary for PostgreSQL and voila!

Nice, thanks for the help!

Regards
Jos
Attachments
statistics.jpg
statistics.jpg (9.58 KiB) Viewed 5759 times

User avatar
Timbalu
Regular
Posts: 4598
Joined: Sun May 02, 2004 3:04 pm

Re: Statistics: number of visitors per week is missing

Post by Timbalu » Mon May 13, 2013 8:59 am

Please paste the code you added for PostgreSQL, so we can update the statistic plugin within an "upcoming" Serendipity 1.7.1 release, or other.
Regards,
Ian

Serendipity Styx Edition and additional_plugins @ https://ophian.github.io/ @ https://github.com/ophian

jwoude
Regular
Posts: 10
Joined: Sun May 12, 2013 3:20 pm

Re: Statistics: number of visitors per week is missing

Post by jwoude » Mon May 13, 2013 7:11 pm

Hi all,

See Dif attached.
Attachments
serendipity_plugin_statistics.txt
Dif of original and modified code to work with PostgreSQL
(2.2 KiB) Downloaded 147 times

User avatar
Timbalu
Regular
Posts: 4598
Joined: Sun May 02, 2004 3:04 pm

Re: Statistics: number of visitors per week is missing

Post by Timbalu » Mon May 13, 2013 7:20 pm

thank you!
What is the difference in ?

Code: Select all

            if (serendipity_db_bool($this->get_config('show_dayvisitors'))) {
                $res = serendipity_db_query("SELECT sum(visits) AS dayvisitors FROM {$serendipity['dbPrefix']}visitors_count WHERE year='".$year."' AND month='".$month."' AND day='".$day."'", true, 'assoc');
                if (is_array($res) && isset($res['dayvisitors'])) {
                    $content .= '<div class="stat_dayhvisitors">' . sprintf($this->get_config('text_dayvisitors'), '<span class="stat_number">' . $res['dayvisitors'] . '</span>') . "</div>\n";
                }
            }
I did not see any and already made the commit (https://github.com/s9y/Serendipity/comm ... f46b99df52). Is that ok?

btw, what is ?
jwoude wrote:For good measure, I also deleted the entire mod_pagespeed cache, it did not work.
Regards,
Ian

Serendipity Styx Edition and additional_plugins @ https://ophian.github.io/ @ https://github.com/ophian

jwoude
Regular
Posts: 10
Joined: Sun May 12, 2013 3:20 pm

Re: Statistics: number of visitors per week is missing

Post by jwoude » Mon May 13, 2013 7:41 pm

Hi Ian,

I switched day and week visitors around. Week visitors come in my code before day visitors. That seems more logical. That's it.
Code itself is identical.

Regards
Jos

User avatar
Timbalu
Regular
Posts: 4598
Joined: Sun May 02, 2004 3:04 pm

Re: Statistics: number of visitors per week is missing

Post by Timbalu » Mon May 13, 2013 7:49 pm

Ahhhhhhhh I see ;-)
Welcome!
Regards,
Ian

Serendipity Styx Edition and additional_plugins @ https://ophian.github.io/ @ https://github.com/ophian

Post Reply