[Updated] Add page to explore all changes of gyms over time

[michikrug]

Updated version of PR #283

Description

Since I was always curious of what is happening in the gyms in my town, but no historical data is tracked by RocketMap, I have added a new SQL table that does so. Using the MySQL scheduler, every 20secs the data from the "gym" table is added to the new "gym_history" one. This data is then nicely represented on a separate new page. /gymhistory The data is searchable, sortable and filterable.

Motivation and Context

Now you are able to see, when gyms are leveled up or down and which Pokémon are placed in or thrown out. (This will also show you all those losers, who are using multiple accounts to shave their spot into high level gyms. There is a separate branch that extracts such behavior.)

How Has This Been Tested?

Tested in my own Worldopole online instance. Does not interfere with any other functionality.

It is necessary to execute some SQL statements that I have documented in readme.gymhistory.md to create the new table and the update events. Furthermore, you have to enable the MySQL event scheduler. This is also documented in the readme for a running server, but you should also update your server configuration to enable the scheduler on start.

Remark: it does not include any localization so far -> english only

Screenshots (if appropriate):

Types of changes

• [ ] Bug fix (non-breaking change which fixes an issue)
• [x] New feature (non-breaking change which adds functionality)
• [ ] Breaking change (fix or feature that would cause existing functionality to change)

TODO

• [ ] Localization

[Obihoernchen]

Ahh again ONLY_FULL_GROUP_BY. This was introduced with mysql 5.7 as a default. @michikrug can you have a look at this please?

[michikrug]

Sure

[javisabalete]

Tested with mysql5.6 and it's working as expected

[javisabalete]

Tested new commits with gymhistory_hide_cp_changes option, works :+1:

[Obihoernchen]

Does it work with new mysql now?

[ghost]

@michikrug Everything seems to be working fine except for the URL and redirects.

When I load /gymhistory/ I get a 404. When I load the URL as https://mywebsite.com/?page=gymhistory it loads the correct page, but when it loads, the URL changes to https://mywebsite.com/gymhistory?name=. Navigating to that page and/or refreshing it throws the 404 again. I have to use the ?page=gymhistory to get back to the page.

[michikrug]

@blakewenloe this seems like a configuration problem of your web server

[ghost]

Ah. I'll look into Nginx again. 

[ghost]

For anyone else running into the issue with the /gymhistory/ URI on Nginx, you can add this to /etc/nginx/sites-available/your-site/:

rewrite ^/gymhistory/?$ /index.php?page=gymhistory&name= break;

That resolved it for me.

[darkelement1987]

i had to rename "htaccess" to ".htaccess" (including the dot) to make the url in the navbar work.

Also i think the PR needs Responsive design. the page doesn't fit on mobile screens and you're unable to scroll sideways. (Huawei P10 Lite / Chrome app)

Works fine in Desktop-mode and on PC.

[michikrug]

It is responsive and there is nothing to side scroll, since the Pokemon are hidden on mobile. The detailed entries will only show the changed ones.

[Obihoernchen]

is this ready to be merged?

[michikrug]

I would say yes :)

[javisabalete]

I just tested this PR with #353 and json_encode returns error 5 (JSON_ERROR_UTF8)

[darkelement1987]

I'm using this for a week or something without problems so my review will be positive.

[darkelement1987]

I just realised my page doesnt work the way it should at all. If i click a gym no history is shown at all.

I followed all the steps from the gymhistory readme.

Ran all the sql stuff, added the scheduler to my.ini

Windows 7 Mariadb Latest Worldopole build

[javisabalete]

Maybe SET GLOBAL event_scheduler = ON; ?

[darkelement1987]

Never mind. After hours of trying to fix it, it was just the wrong db i was in -_- this can be closed again. I'm sorry

Only 1 more problem: