Miscellaneous Hacks - e-steki User ModLog v1.1.3 - An alternative to moderation AutoPM

A big thanks to everyone who nominated or voted this for MOTM September 2007!
e-steki User ModLog

What is this about?
This modification shows the user a table in his UserCP with the latest moderator actions that are performed in his posts/threads.

1. Why this and not Moderation Auto-PM?

1. Its not intruding for the user. In fact, I decided to make this because the Moderation Auto-PMs I was receiving by tons were too annoying to bare.
2. Its not in the Modification Graveyard, so there is a much higher chance of updates and support ;-P
3. It allows more types of actions to show (unfortunately exept hard deletes).
4. Its more visually appealing ;-)
5. Users have to make more clicks to discuss an issue with the moderator, which makes it a little harder for them to protest ;-)
6. Most forums have some "moderation routines" that involve many moderator actions. Especially in this case MAP can be REALLY annoying.
7. You can select to hide the Moderator column, so no hatred will begin after the action as the user will not know who performed it.

2. Features

Admin Features

1. Absolutely NO template modifications and you even get to control where the table will be placed in the UserCP with a simple setting!
2. Ability to hide who the moderator that performed the action was.
3. Fully phrased.
4. Your moderators will not see their own actions, but the actions performed by other moderators on their posts/threads (customizable).
5. You get to control which action types will be shown and how many of them.
6. Ability to set a cutoff limit, so that actions that are performed before X days will not show.
7. Ability to specify which mod/admin usergroups' actions will display.
8. Displays actions from the moment you install it, even old ones, it doesn't display only the actions that are performed afterwards.
9. Supports e-steki Featured threads.
10. A nice, usable and extensive manual instead of an ugly readme.txt ;-)

End user Features

1. Easy spotting of the action type by the nice colorful small icons (provided by famfamfam.com. Thanks!)
2. Actions that were performed after the user's last visit will display in bold. Of course, they are sorted according to the date and time they were performed.
3. There is a link to the thread that the action is about. If the action is about a post, then the link takes the user to that specific post. There is also a link for the forum that contains that thread, in case the user does not remember what it is.
4. The table is collapsible, and if it has nothing to display it doesn't show, just like the other tables in the UserCP.
5. The username of the Moderator (if the admin has selected him to show) is formatted according to usergroup and links to his profile (so the member can send him a pm to talk about it, if he wishes to do so)
6. Full date and time displayed.

Don't forget to Mark as Installed!
Support will be provided only for a few days after each release.
Thanks a lot to famfamfam.com for the icons and to SirAdrian for his answers to my questions.

As the admincp screenshot was resized by those stupid attachment limits that Paul M refuses to change, here is the original one:

[Boothby]

maxXximum from vBGo reported this SQL-error-message.

Database error in vBulletin 3.7.0 Beta 5:

Invalid SQL:

SELECT 
modtable.usergroupid as modgroup,
modtable.username as moderator,
editlog.postid as postid,
editlog.reason as reason,
editlog.dateline as dateline,
editlog.userid as modid,
post.threadid as threadid,
forum.forumid as forumid,
thread.title as threadtitle,
forum.title as forumtitle,
post.title as action
FROM vb7editlog as editlog
LEFT JOIN vb7post as post ON (post.postid = editlog.postid)
LEFT JOIN vb7thread AS thread ON (thread.threadid = post.threadid) 
LEFT JOIN vb7user AS modtable ON (modtable.userid = editlog.userid) 
LEFT JOIN vb7forum AS forum ON (forum.forumid = thread.forumid)
WHERE 
post.userid = 3 AND post.userid != editlog.userid AND 1203889380-editlog.dateline <86400 AND modtable.usergroupid IN () 
ORDER BY editlog.dateline DESC
LIMIT 10;

MySQL Error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') 
ORDER BY editlog.dateline DESC
LIMIT 10' at line 19
Error Number : 1064

I think that happens when $vbulletin->options['eum_groups'] is empty.

[CNBAforo]

In the case of deleted messages/threads, the reason and the deleted content, could be shown in some way to the user, for him to associate the infraction with the content.

[Spinball]

I know this mod is flagged as unsupported, but I want to report an issue with it which I hope Michelle will look in to.
We have a large forum and are experiencing serious SQL slow downs with this query:

# Query_time: 11 Lock_time: 0 Rows_sent: 10 Rows_examined: 23956866
SELECT
modtable.username as moderator,
modtable.usergroupid as modgroup,
modlog.userid as modid,
modlog.action as action,
modlog.type as type,
modlog.dateline as dateline,
modlog.threadid as threadid,
modlog.postid as postid,
forum.forumid as forumid,
thread.title as threadtitle,
forum.title as forumtitle,
deletionlog.reason as delreason
FROM moderatorlog AS modlog
LEFT JOIN user AS modtable ON (modtable.userid = modlog.userid)
LEFT JOIN thread AS thread ON (thread.threadid = modlog.threadid)
LEFT JOIN post AS post ON (post.postid = modlog.postid)
LEFT JOIN forum AS forum ON (forum.forumid = thread.forumid)
LEFT JOIN deletionlog AS deletionlog ON (deletionlog.primaryid = IF(deletionlog.type = 'post', modlog.postid, modlog.threadid))
WHERE
IF(modlog.postid,post.userid=28735,thread.postuser id=28735) AND modlog.type !=20 AND modtable.userid != 28735 AND modlog.type NOT IN (11,12) AN$
ORDER BY dateline DESC
LIMIT 10;

23 million rows examined!!! This mod is grinding our forum to a halt.
Is there an alternative, more efficient way of doing this? I have had to disable the mod until a fix is found, and I would suggest that anyone with a big forum check their SQL performance when using this.

[Lea Verou]

Unless somebody else thinks of a way to fix this, personally I am unable to think of a most efficient query.
Sorry. :/

[Spinball]

Originally Posted by Michelle

Unless somebody else thinks of a way to fix this, personally I am unable to think of a most efficient query.
Sorry. :/

Thanks for the quick reply. Might splitting it into separate queries not be easier on the database?
[Edit : I have added an index on moderatorlog.userid and have re-enabled to see whether that has any effect. It should do, I'm guessing and hoping.]
[Edit 2: Nope, still having problems - disabling.]

[J98680Bxxxxx]

Installed (also marked) on vB 3.7 CR2, works fine. :up: Thanks Michelle.

[Spinball]

Michelle, does this mod only report on certain types of moderator actions?
I ask because it is, by the looks of it, reporting checked posts from this mod:
https://vborg.vbsupport.ru/showthread.php?t=173609

[Konstantinos]

it doesnt work for posts moved to another thread

[Lea Verou]

Originally Posted by Konstantinos

it doesnt work for posts moved to another thread

It does, but it incorrectly notifies the thread author instead of the post authors.
Its a known bug that I will fix sometime, but I don't know when...

[shadowevil]

Does this hack work with vB 3.7 GOLD ??