#248 Fetching spam activities with at least 0 minutes runtime (instead of only the ones longer than 1 day)

[PLP-GTR]

Heads up: I don't know if auto loader for has to be rebuild manually or the new class in src/Doctrine/Functions/TimestampDiff.php is automatically found.

---

I have changed the filter for the admin/spam activities (production link):

from

DATEDIFF(a.ends, a.starts) > 1

to

TIMESTAMPDIFF(MINUTE, a.starts, a.ends) >= 0

Result:

When writing App\Doctrine\Functions\TimestampDiff I tried to stick to the instructions here: → https://www.doctrine-project.org/projects/doctrine-orm/en/2.17/cookbook/dql-user-defined-functions.html

The seemingly simpler approach to use DATEDIFF(a.ends, a.starts) >= 0 would have lead to the issue, that activities with an end time prior to the start time on the same day would show up. Although this shouldn't be possible at storage logic already, the "> 1" filter was preventing those from showing up, so I kept this logic.

---

Fixes #248

[thisismeonmounteverest]

@PLP-GTR Why not just remove the datediff? The original idea was to find all activities that have been created by a member that has been banned because of spamming and that lasted longer than a day. Issue #248 says just show all activities of banned members to remove them if necessary. No need to check anything else.

[thisismeonmounteverest]

@PLP-GTR Regarding the heads up: In the dev environment most of the times newly introduced classes are found automatically. In the few cases that doesn't help you need to call php bin/console cache:clear. (Which is also needed to create the cache on production.)

[PLP-GTR]

@PLP-GTR Why not just remove the datediff? The original idea was to find all activities that have been created by a member that has been banned because of spamming and that lasted longer than a day. Issue #248 says just show all activities of banned members to remove them if necessary. No need to check anything else.

Three reasons:

• I did not know the reason of the initial intention to filter only activities with a date (day) difference of at least +1
• I did not want to include the negative activities since I don't know what the current production database holds (worst case thousands of activities with negative time)
• I think it's a sexy solution to include the TIMESTAMPDIFF method and let the initial intention live on (plus it was fun to learn about doctrine/DQL)

I can change the handling if you want, no problem. Let me update #248 and draft this pull request.