More advanced ADMIN panel

[Justinas Rumbinas]

Hi everyone,

recently our company adopted SemanticScuttle. We made several enhancements and we to share our work with community.
The most important is advanced admin panel, you can take a look at it here: http://picasaweb.google.com/lh/photo/P_SYWAkCtSg6k85pjnjnzQ

We use SemanticScuttle 9.0 as background. Main changes:

total user count
total public bookmark count for each user (updated by administrator)
user pagination and sorting
user search
complete user deletion (all information, related with the user is shreded)

Instalation guidlines:

1. Update database
2. Modify file admin.php
3. Modify file bookmarkservice.php
4. Modify file userservice.php
5. Add missing templates
6. Create/Add administration account
7. Apply Bugfix 2440121

Step 1. Updating database:

Execute this SQL query in your database

ALTER TABLE database.user_table ADD COLUMN bCount int(11) NOT NULL default '0'

NOTICE: database.user_table by default should be replaced by database name and user table name (prefexis might be different), i.e. scuttle.sc_users

Step 2. Modifying file admin.php

Replace file content with the following one.

<<< admin.php
<?php
/***********
Copyright (C) 2007 - 2008 SemanticScuttle project (fork from Scuttle)
http://sourceforge.net/projects/semanticscuttle/

This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
***********/

require_once('header.inc.php');

$userservice = & ServiceFactory :: getServiceInstance('UserService');
$bookmark2tagservice = & ServiceFactory :: getServiceInstance('Bookmark2Tagservice');
$bookmarkservice = & ServiceFactory :: getServiceInstance('BookmarkService');
$tag2tagservice = & ServiceFactory :: getServiceInstance('Tag2TagService');
$cdservice = & ServiceFactory :: getServiceInstance('CommonDescriptionService');
$shservice =& ServiceFactory::getServiceInstance('SearchHistoryService');
$tagstatservice =& ServiceFactory::getServiceInstance('TagStatService');
$templateservice = & ServiceFactory :: getServiceInstance('TemplateService');

// Header variables

$tplVars['loadjs'] = true;

if ( !$userservice->isLoggedOn() ) {
header('Location: '. createURL('login', ''));
exit();
}

$currentUser = $userservice->getCurrentUser();
$currentUserID = $userservice->getCurrentUserId();
$currentUsername = $currentUser[$userservice->getFieldName('username')];

if ( !$userservice->isAdmin($currentUserID) ) {
header('Location: '. createURL('bookmarks', $currentUsername));
exit();
}
if (isset($_POST['terms'])) {
// Redirect to GET
header('Location: '. createURL('admin', "search/". filter($_POST['terms'], 'url')));
// GET
} else {
if (isset($_SESSION['msg'])){
$tplVars['msg'] = $_SESSION['msg'];
unset($_SESSION['msg']);
}

@list($url, $action, $param) = isset($_SERVER['PATH_INFO']) ? explode('/', $_SERVER['PATH_INFO']) : NULL;

if ( $action ) {
    switch ( $action ) {
        case 'delete':
            $user = $param;
            if ( $user && ($userinfo = $userservice->getUserByUsername($user)) ) {
                $uId = $userinfo['uId'];

                $tag2tagservice->removeLinkedTags('','','',$uId);
                $userservice->deleteUser($uId);
                $bookmark2tagservice->deleteTagsForUser($uId);
                // XXX: don't delete bookmarks before tags, else tags can't be deleted !!!
                $bookmarkservice->deleteBookmarksForUser($uId);
                $cdservice->deleteDesctiptionsForUser($uId);
                $shservice->deleteSearchHistoryForUser($uId);
                $tagstatservice->deleteTagStatForUser($uId);

                $_SESSION['msg'] = sprintf(T_('%s and all his bookmarks and tags were deleted.'), $user);
                header('Location: '. createURL('admin'));
            }
            break;
        case 'update':
            $bookmarkservice->updateBookmarkCount();
            $_SESSION['msg'] = T_('Bookmark count was successfully updated.');
            header('Location: '. createURL('admin'));
            break;
        case 'search':
            $term = $param;

            break;
        default:
            // DO NOTHING
    }
}
$templatename = 'admin.tpl';

// Pagination
$perpage = getPerPageCount();
if (isset($_GET['page']) && intval($_GET['page']) > 1) {
    $page = $_GET['page'];
    $start = ($page - 1) * $perpage;
} else {
    $page = 0;
    $start = 0;
}

$users =& $userservice->getAllUsers($start,$perpage,getSortOrder(),$term);
if (isset($term)){
    $shservice->addSearch($term, "user", $users['total'], $currentUserID);
}
if ( !is_array($users) ) {
    $users = array();
}

$tplVars['page'] = $page;
$tplVars['start'] = $start;
$tplVars['pagetitle'] = isset($term)? T_("Search Users") : T_("Manage users");
$tplVars['subtitle'] = $tplVars['pagetitle']; 
$tplVars['users'] =& $users['users'];
$tplVars['total'] = $users['total'];
$tplVars['sidebar_blocks'][] = "admin";
$tplVars['sidebar_blocks'][] = "user.search";
$templateservice->loadTemplate($templatename, $tplVars);

}
?>
>>> admin.php

Step 3. Modifying file bookmarkservice.php:

Add following lines to the end of file (a line before these simbols "} ?>" (which may be in different lines) in the end of file):
<<< bookmarkservice.php
function updateBookmarkCount(){
$query = "
INSERT INTO sc_users (uId, bCount)
(
SELECT
uId,
Count(bId) AS count
FROM
". $this->getTableName() ."
WHERE
bStatus = 0
GROUP BY
sc_bookmarks.uId
) ON DUPLICATE KEY UPDATE bCount = VALUES(bCount)";

    if (! ($dbresult =& $this->db->sql_query($query)) ) {
        message_die(GENERAL_ERROR, 'Could not update bookmark count', '', __LINE__, __FILE__, $query, $this->db);    
        return false;
    }
    return true;
}

>>> bookmarkservice.php

Step 4. Modifying file userservice.php:

Find function getAllUsers() (function should be located between 339-354 lines) and replace it with the new one, specified bellow.

<<< userservice.php
function getAllUsers ($start = 0, $perpage, $sortOrder, $terms = null) {
$query = 'SELECT * FROM '. $this->getTableName();

        // Search terms
        if ($terms) {
            // Multiple search terms okay
            $aTerms = explode(' ', $terms);
            $aTerms = array_map('trim', $aTerms);
            $query .= ' WHERE '; 
            for ($i = 0; $i < count($aTerms); $i++) {
                $query .= ' (username LIKE "%'. $this->db->sql_escape($aTerms[$i]) .'%"';
                $query .= ' OR name LIKE "%'. $this->db->sql_escape($aTerms[$i]) .'%") OR ';
            }
            // Let's cut last OR
            $query = substr($query, 0, -3);
        }

        switch($sortOrder) {
            case 'login_desc':
                $query.= ' ORDER BY username DESC ';
                break;
            case 'name_asc':
                $query.= ' ORDER BY name ASC ';
                break;
            case 'name_desc':
                $query.= ' ORDER BY name DESC ';
                break;
            case 'count_asc':
                $query.= ' ORDER BY bCount ASC ';
                break;
            case 'count_desc':
                $query.= ' ORDER BY bCount DESC ';
                break;
            default:
                $query.= ' ORDER BY username ASC ';
        }

        if (!($dbresult = & $this->db->sql_query_limit($query, intval($perpage), intval($start)))) {
            message_die(GENERAL_ERROR, 'Could not get users', '', __LINE__, __FILE__, $query, $this->db);
            return false;
        }

        $rows = array();

        while ( $row = $this->db->sql_fetchrow($dbresult) ) {
            $rows[] = $row;
        }
        $users = $rows;

        if (isset($terms)) {
            $totalquery = 'SELECT FOUND_ROWS() AS total';
        } else {
            $totalquery = 'SELECT COUNT(*) AS total FROM '. $this->getTableName();
        }
        if (!($totalresult = & $this->db->sql_query($totalquery)) || (!($row = & $this->db->sql_fetchrow($totalresult)))) {
            message_die(GENERAL_ERROR, 'Could not get total users', '', __LINE__, __FILE__, $totalquery, $this->db);
            return false;
        }

        $total = $row['total'];
            return array ("users" => $users, "total" => $total);
        }

>>> userservice.php

Step 5. Adding missing templates:

<<< admin.tpl.php
<?php

$userservice =& ServiceFactory::getServiceInstance('UserService');

$currentUser = $userservice->getCurrentUser();
$currentUserID = $userservice->getCurrentUserId();
$currentUsername = $currentUser[$userservice->getFieldName('username')];

$this->includeTemplate($GLOBALS['top_include']);
?>
<form id="search" action="<?php echo createURL('admin'); ?>" method="post">
<table>
<tr>
<td><?php echo T_('Search user' / Search ... for /); ?></td>
<td><input type="text" name="terms" size="30" value="<?php $terms=!isset($terms)?'':$terms; echo filter($terms); ?>" /></td>
<td><input type="submit" name="search" value="<?php echo T_('Search' / Submit button /); ?>" /></td>
</tr>
</table>
</form>
<?
if (count($users) > 0){
?>
<p id="sort">
<?php echo $total.' '.T_("user(s)"); ?> -
<?php echo T_("Sort by:"); ?>
<?php
$dateSort = (getSortOrder()=='name_asc')? 'name_desc':'name_asc';
$titleSort = (getSortOrder()=='login_asc')? 'login_desc':'login_asc';
$urlSort = (getSortOrder()=='count_asc')? 'count_desc':'count_asc';
?>
<a href="?sort=<?php echo $dateSort ?>"><?php echo T_("Name"); ?></a><span> / </span>
<a href="?sort=<?php echo $titleSort ?>"><?php echo T_("Login"); ?></a><span> / </span>
<a href="?sort=<?php echo $urlSort ?>"><?php echo T_("Bookmark count"); ?></a>
</p>
<?
echo '<ol id="bookmarks">';

foreach(array_keys($users) as $key) {

echo '<li class="xfolkentry">'."\n";

echo '<div class="link">';
echo '<a href="'.createURL('profile', $users[$key][$userservice->getFieldname('username')]).'">'.$users[$key][$userservice->getFieldName('username')].'</a>';
echo '</div>';

echo '<div class="meta">';
echo T_('Total bookmark count: ') . $users[$key]['bCount'];         
echo '</div>';
echo '<div class="actions">';
echo "Actions : ";
$actions = array();
if($users[$key][$userservice->getFieldName('username')] != $currentUsername) {
    $actions[] = '<a href="'.createURL('bookmarks', $users[$key][$userservice->getFieldname('username')]).'">'.T_('Read bookmarks').'</a>';
    $actions[] = '<a href="'.createURL('admin','delete/'.$users[$key][$userservice->getFieldname('username')]).'" onclick="return confirm(\''.T_('Are you sure?').'\');">'.T_('Delete').'</a>';
    //$actions[] = '<a href="'.createURL('admin','block/'.$users[$key][$userservice->getFieldname('username')]).'" onclick="return confirm(\''.T_('Are you sure?').'\');">'.T_('Block').'</a>';
} 
for ($i = 0; $i < count($actions); $i++){
    echo (count($actions) - 1 > $i)? $actions[$i]. " - " : $actions[$i];
}
echo '</div>';
echo '</li>'."\n";

}

// Ordering
$sortOrder = '';
if (isset($_GET['sort'])) {
    $sortOrder = 'sort='. $_GET['sort'];
}

$sortAmp = (($sortOrder) ? '&'. $sortOrder : '');
$sortQue = (($sortOrder) ? '?'. $sortOrder : '');
$nav_url = "?page=";

// Previous
$perpage = getPerPageCount();
if (!$page || $page < 2) {
    $page = 1;
    $start = 0;
    $bfirst = '<span class="disable">'. T_('First') .'</span>';
    $bprev = '<span class="disable">'. T_('Previous') .'</span>';
} else {
    $prev = $page - 1;
    $prev = 'page='. $prev;
    $start = ($page - 1) * $perpage;
    $bfirst= '<a href="'. $nav_url . 1 . $sortQue .'">'. T_('First') .'</a>';
    $bprev = '<a href="'. $nav_url . $prev . $sortAmp .'">'. T_('Previous') .'</a>';
}

// Next
$next = $page + 1;
$totalpages = ceil($total / $perpage);
if (count($users) < $perpage || $perpage * $page == $total) {
    $bnext = '<span class="disable">'. T_('Next') .'</span>';
    $blast = '<span class="disable">'. T_('Last') ."</span>\n";
} else {
    $bnext = '<a href="'. $nav_url . $next . $sortAmp .'">'. T_('Next') .'</a>';
    $blast = '<a href="'. $nav_url . $totalpages . $sortAmp .'">'. T_('Last') ."</a>\n";
}
echo '<p class="paging">'. $bfirst .'<span> / </span>'. $bprev .'<span> / </span>'. $bnext .'<span> / </span>'. $blast .'<span> / </span>'. sprintf(T_('Page %d of %d'), $page, $totalpages) ." </p>\n";

} else {
echo '<p class="error">'.T_('No users found').'</p>';
}

$this->includeTemplate('sidebar.tpl');
$this->includeTemplate($GLOBALS['bottom_include']);

?>
>>> admin.tpl.php

<<< sidebar.block.admin.php
<?php
$userservice =& ServiceFactory::getServiceInstance('UserService');
if ($userservice->isLoggedOn()) {
$currentUser = $userservice->getCurrentUser();
if ($userservice->isAdmin($currentUser[$userservice->getFieldName('primary')])){
$linkText = T_("Update bookmark count");
$linkAddress = createURL("admin","update/bCount");
?>

<h2><?php echo T_('Admin panel'); ?></h2>
<div id="adminpanel">
<ul>
<li><a href="<?php echo $linkAddress ?>"><?php echo $linkText ?></a></li>
</ul>
</div>

<?php
}
}
?>
>>> sidebar.block.admin.php

<<< sidebar.block.user.search.php
<?php
$userservice =& ServiceFactory::getServiceInstance('UserService');
$searchhistoryservice =& ServiceFactory::getServiceInstance('SearchHistoryService');

$logged_on_userid = $userservice->getCurrentUserId();
if ($logged_on_userid === false) {
$logged_on_userid = NULL;
}

$lastSearches = $searchhistoryservice->getAllSearches('user', NULL, 3, NULL, true, true);

if ($lastSearches && count($lastSearches) > 0) {
?>

<h2><?php echo T_('Last Searches'); ?></h2>
<div id="searches">
<table>
<?php
foreach ($lastSearches as $row) {
echo '<tr><td>';
echo '<a href="'.createURL('admin', 'search/'.$row['shTerms']).'">';
echo $row['shTerms'];
echo '</a>';
echo ' <span title="'.T_('Number of users for this query').'">('.$row['shNbResults'].')</span>';
echo '</td></tr>';
}
?>

</table>
</div>
<?php
}
?>
>>> sidebar.block.user.search.php

Step 6. Creating/Adding administrative account

For now administrative account is declared in file config.inc.php.
Go to line 32 ($admin_users = array('admin'); # admin users = array('adminnickname', 'user1nick', 'user2nick');)
and add admin username to $admin_users array.

Step 7. Apply Bugfix 2440121 (Required)

Problem: in previous versions of SemanticScuttle (0.90 and erlier) some information was not removed while deleting user account...
What you need to do is to add additional method for each service (commondescriptionservice.php, searchhistoryservice.php and tagstatservice.php).
I hope this bugfix will be included in the next version of SemanticScuttle.

More information can be found here: https://sourceforge.net/tracker/?func=detail&atid=1017430&aid=2440121&group_id=211356