Ducks FixGroups Script

duck · Involved Joined: Jul 03, 2006 Posts: 267

Hi all thought I would let you know that during the preparation of PNC 4.5 we worked on some improvements to group management. As such during that improvement process we thought what about all those users that may have messed up groups from what was once a poorly messed system?

Two places for creating groups was bound to be a cause for issues for many users especially since if you created them in PHPbb it was only one way. As well users joining via phpbb was one way. Well as of PNC 4.5 that is no longer a concern but fixing the already messed up groups and users could be.

So I did a quick and dirty script to mesh up the groups. It may not be elegant but it gets the Job done. I even tested it on a live site where I had a bunch of groups that were so messsed up it is not funny. Forums Permissions were out of whack and everything. So far she's done a good job and have had no complaints.

While this Script is available in the PNC 4.5 package (so upgrading users can fix their groups) I have decided to release it as a stand-alone script for other users of other versions of nuke that may wish to use it. It hasn't been tested on anything but PNC 3.2, PNC 4.4 and PNC 4.5 but I don't see any reason it shouldn't work on any version of nuke that uses the $db sql layer. As always though it is recommended to back up your tables first. And of course once finished don't leave the file in your root!

The tables you'll need to back up are:

_bbgroups
_bbuser_group
_nsngr_groups
_nsngr_users
_blocks
_modules
_nsngd_downloads
_nsngd_categories

What does the FixGroups Script do?

This Script is intended to Merge phpBB Groups and NSN Groups ensuring Groups are identical in Both sets of Tables.

BB Groups are considered Priority and all NSN groups are first updated to reflect them after that any NSN groups remaining not existing in phpBB groups are then created in phpBB Groups.

Finally Users are merged and cleaned up as well. All orphan users are deleted from Groups Users tables and any duplicate named groups are deleted as well as any references to those groups within the Modules. Blocks or NSN Downloads groups are removed (if group no longer exists). Blank Usernames are updated to reflect Correct Users and where Moderators are unset or incorrect they are set to the default of user_id "2" you may change this to the user ID you wish at the top of the file.

Single User Groups fix in bbgroups has been added to this file. Your User tale is scanned and any users not having a single user group in bbgroups is added. Based on original fix file titled fixgroups.php modified to work with this script. Don't know who original Author was of that one (nothing included in the file) so sorry for missing credit.

You May Choose to Delete All or Approve All Pending BBusers upon running of the Script.

You may download the file from here:

Only registered users can see links on this board!
Get registered or login to the forums!

Please let me know if you have any issues when using this script.

Enjoy!

ToolBox · Regular Joined: Mar 16, 2005 Posts: 74

This is suggestion to make it better or simple to synchronize phpbb2 (=bbtonuke) with your group scripts.

As you may know well, there are two files where you may change to synchronize groups between nuke front-end and phpbb2.

One of ignored procedure to make groups in nuke front-end is phpbb2 group. The followings are suggestion that come from Enterprise NUKE which our totally futer generation of nuke.

1. when users registered and are confirmed by email (or self-activated, or approved by admin), put some codes under user account and default phpbb2 group to join

2. delete users --> delete phpbb2 groups

3. creation of users by admin --> join the users to the default phpbb2 group.

Now, default groups will be in the two following files:

1. /modules/Forums/admin/admin_groups.php
2. /modules/Forums/groupcp.php

In /modules/Forums/admin/admin_gorups.php

Code:

#
# ----------- [ FIND ] ---------------------
#

$sql = "DELETE FROM " . AUTH_ACCESS_TABLE . "
WHERE group_id = " . $group_id;
if ( !$db->sql_query($sql) )
{
message_die(GENERAL_ERROR, 'Could not update auth_access', '', __LINE__, __FILE__, $sql);
}

#
# ----------- [ AFTER, ADD ] ---------------------
#

// --- BEGIN : HACK - ADVANCED USER GROUP VER 1.0.0 ---
         //
         // Delete Nuke Front End Group
         //
         $sql_group = "DELETE FROM ".$prefix."_groups WHERE id='".$group_id."'";
if (!$db->sql_query($sql_group))
{
message_die(GENERAL_ERROR, 'Could not update PHP Nuke front-end group', '', __LINE__, __FILE__, $sql);
}

         $sql = "DELETE FROM ".$prefix."_groups_superusers WHERE group_id='".$group_id."'";
if (!$db->sql_query($sql_group))
{
message_die(GENERAL_ERROR, 'Could not update PHP Nuke front-end group superusers', '', __LINE__, __FILE__, $sql);
}

         $sql = "DELETE FROM ".$prefix."_groups_moderators WHERE group_id='".$group_id."'";
if (!$db->sql_query($sql_group))
{
message_die(GENERAL_ERROR, 'Could not update PHP Nuke front-end group moderators', '', __LINE__, __FILE__, $sql);
}

         $sql = "DELETE FROM ".$prefix."_groups_langs WHERE group_id='".$group_id."'";
if (!$db->sql_query($sql_group))
{
message_die(GENERAL_ERROR, 'Could not update PHP Nuke front-end group languages', '', __LINE__, __FILE__, $sql);
}

         $sql = "DELETE FROM ".$prefix."_groups_users WHERE group_id='".$group_id."'";
if (!$db->sql_query($sql_group))
{
message_die(GENERAL_ERROR, 'Could not update PHP Nuke front-end group users', '', __LINE__, __FILE__, $sql);
}
            // DELETE 01: access permission group - advertising

            // DELETE 02: access permission group - contents
            $db->sql_query("DELETE FROM ".$prefix."_pages_access WHERE group_id='".$group_id."'");

// --- END : HACK - ADVANCED USER GROUP VER 1.0.0 ---

#
# ----------- [ FIND ] ---------------------
#

$sql = "UPDATE " . GROUPS_TABLE . "
SET group_type = $group_type, group_name = '" . str_replace("\'", "''", $group_name) . "', group_description = '" . str_replace("\'", "''", $group_description) . "', group_moderator = $group_moderator
WHERE group_id = $group_id";
if ( !$db->sql_query($sql) )
{
message_die(GENERAL_ERROR, 'Could not update group', '', __LINE__, __FILE__, $sql);
}

#
# ----------- [ AFTER, ADD ] ---------------------
#

// --- BEGIN : HACK - ADVANCED USER GROUP VER 1.0.0 ---
               $sql_group = "UPDATE ".$prefix."_groups SET type=$group_type, name='".str_replace("\'", "''", $group_name)."', description='".str_replace("\'", "''", $group_description)."', moderator=$group_moderator WHERE id=$group_id";
               if (!$db->sql_query($sql_group))
               {
                  message_die(GENERAL_ERROR, 'Could not update group', '', __LINE__, __FILE__, $sql);
               };

               $numrows = $db->sql_numrows($db->sql_query("SELECT * ".$prefix."_groups_moderators WHERE user_id='$group_moderator' AND group_id='$group_id'"));
               if (!$numrows)
               {
                  $db->sql_query("INSERT INTO ".$prefix."_groups_moderators VALUES ('".$group_moderator."', '".$group_id."')");
               };
// --- END : HACK - ADVANCED USER GROUP VER 1.0.0 ---

#
# ----------- [ FIND ] ---------------------
#

else if( $mode == 'newgroup' )
{
$sql = "INSERT INTO " . GROUPS_TABLE . " (group_type, group_name, group_description, group_moderator, group_single_user)
VALUES ($group_type, '" . str_replace("\'", "''", $group_name) . "', '" . str_replace("\'", "''", $group_description) . "', $group_moderator, '0')";

#
# ----------- [ AFTER, ADD ] ---------------------
#

// --- BEGIN : HACK - ADVANCED USER GROUP VER 1.0.0 ---
$sql_group = "INSERT INTO ".$prefix."_groups VALUES (NULL, '$group_type', '".str_replace("\'", "''", $group_name)."', '".str_replace("\'", "''", $group_description)."', $group_moderator, '0', '', '')";
// --- END : HACK - ADVANCED USER GROUP VER 1.0.0 ---

#
# ----------- [ FIND ] ---------------------
#

$sql = "INSERT INTO " . USER_GROUP_TABLE . " (group_id, user_id, user_pending)
VALUES ($new_group_id, $group_moderator, 0)";
if ( !$db->sql_query($sql) )
{
message_die(GENERAL_ERROR, 'Could not insert new user-group info', '', __LINE__, __FILE__, $sql);
}

#
# ----------- [ AFTER, ADD ] ---------------------
#

// --- BEGIN : HACK - ADVANCED USER GROUP VER 1.0.0 ---
if ( !$db->sql_query($sql_group))
{
message_die(GENERAL_ERROR, 'Could not insert new group', '', __LINE__, __FILE__, $sql);
}
$new_nuke_group_id = $db->sql_nextid();

$sql_group = "UPDATE ".$prefix."_groups SET id='$new_group_id' WHERE id='".$new_nuke_group_id."'";
if ( !$db->sql_query($sql_group) )
{
message_die(GENERAL_ERROR, 'Could not insert new user-group info', '', __LINE__, __FILE__, $sql);
}

               $db->sql_query("INSERT INTO ".$prefix."_groups_superusers VALUES ('', '".$new_group_id."')");
               $db->sql_query("INSERT INTO ".$prefix."_groups_moderators VALUES ('".$group_moderator."', '".$new_group_id."')");

// --- END : HACK - ADVANCED USER GROUP VER 1.0.0 ---

The above code is a rough sample of phpbb2 (bbtonuke) blocks to change. I used old version we wired up.

the following codes are for groupcp.php

Code:

#
# ----------- [ FIND ] ---------------------
#

if ( $userdata['user_level'] != ADMIN && $userdata['user_level'] == MOD )
{
$sql = "SELECT COUNT(auth_mod) AS is_auth_mod
FROM " . AUTH_ACCESS_TABLE . " aa, " . USER_GROUP_TABLE . " ug
WHERE ug.user_id = " . $userdata['user_id'] . "
AND aa.group_id = ug.group_id
AND aa.auth_mod = '1'";
if ( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, 'Could not obtain moderator status', '', __LINE__, __FILE__, $sql);
}

#
# ----------- [ AFTER, ADD ] ---------------------
#

// --- BEGIN: HACK - ADVANCED USER GROUP VER 1.0.0 ---
         $db->sql_query("DELETE FROM ".$prefix."_groups_users WHERE user_id='".$userdata['user_id']."' AND group_id='".$group_id."'");
// --- END: HACK - ADVANCED USER GROUP VER 1.0.0 ---

#
# ----------- [ FIND ] ---------------------
#

if ( $row['user_level'] != ADMIN && $row['user_level'] != MOD && $group_info['auth_mod'] )
{
$sql = "UPDATE " . USERS_TABLE . "
SET user_level = " . MOD . "
WHERE user_id = " . $row['user_id'];
if ( !$db->sql_query($sql) )
{
message_die(GENERAL_ERROR, 'Could not update user level', '', __LINE__, __FILE__, $sql);
}
}

//
// Get the group name
// Email the user and tell them they're in the group
//

#
# ----------- [ BEFORE, ADD ] ---------------------
#

// --- BEGIN: HACK - ADVANCED USER GROUP VER 1.0.0 ---
                           $db->sql_query("INSERT INTO ".$prefix."_groups_users VALUES ('".$row['user_id']."' '".$group_id."', '0')");
// --- END: HACK - ADVANCED USER GROUP VER 1.0.0 ---

#
# ----------- [ FIND ] ---------------------
#

}
else if ( isset($HTTP_POST_VARS['deny']) || isset($HTTP_POST_VARS['remove']) )
{
if ( $group_info['auth_mod'] )
{

#
# ----------- [ BEFORE, ADD ] ---------------------
#

// --- BEGIN: HACK - ADVANCED USER GROUP VER 1.0.0 ---
                                 $db->sql_query("UPDATE ".$prefix."_groups_users SET user_pending = 0 WHERE user_id IN ($sql_in) AND group_id = '$group_id'");
// --- END: HACK - ADVANCED USER GROUP VER 1.0.0 ---

#
# ----------- [ FIND ] ---------------------
#

}
}
}
}

$sql = "DELETE FROM " . USER_GROUP_TABLE . "
WHERE user_id IN ($sql_in)
AND group_id = '$group_id'";

#
# ----------- [ AFTER, ADD ] ---------------------
#

// --- BEGIN: HACK - ADVANCED USER GROUP VER 1.0.0 ---
                                 $db->sql_query("DELETE FROM ".$prefix."_groups_users WHERE user_id IN ($sql_in) AND group_id = '$group_id'");
// --- END: HACK - ADVANCED USER GROUP VER 1.0.0 ---

I think those codes are enoguh for you to synchronize your groups and phpbb2 or bbtonuke groups automatically.
With the above chaanges in your bbtonuke files, you will no worry about such auto-synch between two different identies of group defined in phpnuke front end and bbtonuke.

I believe you knew them already.

Only I did not write is to hand SID, which stands for Session ID, for your phpbb2.
I think you know how to handle the SID. That's not difficult.

Good Luck in your project.

duck · Involved Joined: Jul 03, 2006 Posts: 267

Thanks for your input but actually we already have made the changes to nsn and bbgroups within the PNC 4.5 package.

Your code above I don't think is talking about the same thing cause I don't see any queries related to nsn groups.

The reason for the Above FixGroups Script is for people who are upgrading or don't already have the changes to bbtonuke applied. They may have groups that are out of whack. Anyone using our new package will not have to worry about the groups getting out of whack again. When they try to make groups via bbAdmin the frame actually opens NSN admin to create the groups. There they can add edit users groups via NSN and no longer have to do via bbadmin. They just use bbadmin for group permissions in forums.

Now inside the forum where you have User Groups in Forum header. All the functions inside there have been update to add the user to NSN as well as phpbb. So when a user joins from there instead of nsn it is ok. Same if a moderator or admin adds/edits users from there it is also now ok.

But I notice in many versions of nuke including Ravennuke the user groups are pretty much one way which is easy for users to get their groups messed up over time. The script above should align the groups if they are out of whack. But without applying your suggested patch (or the ones I did for PNC) the groups may eventually get out of whack again and they may need to rerun this script to fix em again.

Other things we fixed in NSN groups admin was the ability to add a single user to groups. Instead of long list possibly with several pages to wade through to find a user to add you can just enter/search a user name and add to a group.
Also I noticed that when creating a group in NSN there is no actual check that the moderator exist when you type in a name. So if you mistype or something then you'll have an improper moderator on the group so we added a search button to the Add moderator as well on the save side I check the users table to make sure the moderator exist if not send the user back to change it before creating the group.

Also of course since BBgroups has 3 group types we added the extra type to NSN since the groups are now created from there.

But this is all minor to the changes I would like to make to groups. But that is another story! lol

ToolBox · Regular Joined: Mar 16, 2005 Posts: 74

That's a sample code fractions that I hope you capture the idea.
That is not actual code.

skram · New Member Joined: Jul 21, 2009 Posts: 2

Duck would there be any chance of you posting the script here as everytime I have tried to register with that site I never receive an email to confirm.

Palbin · Posted: Wed Aug 05, 2009 10:47 pm

Skram should be activated.

skram · New Member Joined: Jul 21, 2009 Posts: 2

It's ok my mistake Very Happy