source: trunk/services/admins.php @ 693

<?php
/**
 * The Strangecode Codebase - a general application development framework for PHP
 * For details visit the project site: <http://trac.strangecode.com/codebase/>
 * Copyright 2001-2012 Strangecode, LLC
 *
 * This file is part of The Strangecode Codebase.
 *
 * The Strangecode Codebase 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 3 of the License, or (at your option)
 * any later version.
 *
 * The Strangecode Codebase 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
 * The Strangecode Codebase. If not, see <http://www.gnu.org/licenses/>.
 */

/**
 * admins.php
 */

// require_once dirname(__FILE__) . '/_config.inc.php';

$app->sslOn();
$auth->requireLogin();

require_once 'codebase/lib/PageNumbers.inc.php';
require_once 'codebase/lib/Cache.inc.php';
require_once 'codebase/lib/FormValidator.inc.php';
require_once 'codebase/lib/SortOrder.inc.php';
require_once 'codebase/lib/HTML.inc.php';
require_once 'codebase/lib/Prefs.inc.php';
require_once 'codebase/lib/Lock.inc.php';
require_once 'codebase/lib/Version.inc.php';


/********************************************************************
* CONFIG
********************************************************************/

// Titles and navigation header.
$nav->add(_("Administrators"), null);
$nav->set('id', 'admins');

// The object to validate form input.
$fv = new FormValidator();

// Configure the prefs object.
$tmp_prefs = new Prefs('admins');
$tmp_prefs->setParam(array('persistent' => false));

// Configure the cache object.
$cache =& Cache::getInstance('admins');
$cache->setParam(array('enabled' => false)); // Better leave disabled; the list gets out of sync with the db otherwise, somehow.

// Instantiate a sorting object with the default sort and order. Add SQL for each column.
$so = new SortOrder('admin_id', 'DESC');
$so->setColumn('admin_id', $auth->getParam('db_primary_key') . ' ASC', $auth->getParam('db_primary_key') . ' DESC');
$so->setColumn('username', $auth->getParam('db_username_column') . ' ASC', $auth->getParam('db_username_column') . ' DESC');
$so->setColumn('userpass', $auth->getParam('db_table') . '.userpass ASC', $auth->getParam('db_table') . '.userpass DESC');
$so->setColumn('first_name', $auth->getParam('db_table') . '.first_name ASC', $auth->getParam('db_table') . '.first_name DESC');
$so->setColumn('last_name', $auth->getParam('db_table') . '.last_name ASC', $auth->getParam('db_table') . '.last_name DESC');
$so->setColumn('email', $auth->getParam('db_table') . '.email ASC', $auth->getParam('db_table') . '.email DESC');
$so->setColumn('seconds_online', $auth->getParam('db_table') . '.seconds_online ASC', $auth->getParam('db_table') . '.seconds_online DESC');
$so->setColumn('last_login_datetime', $auth->getParam('db_table') . '.last_login_datetime ASC', $auth->getParam('db_table') . '.last_login_datetime DESC');
$so->setColumn('last_access_datetime', $auth->getParam('db_table') . '.last_access_datetime ASC', $auth->getParam('db_table') . '.last_access_datetime DESC');
$so->setColumn('last_login_ip', $auth->getParam('db_table') . '.last_login_ip ASC', $auth->getParam('db_table') . '.last_login_ip DESC');
$so->setColumn('added_by_user_id', $auth->getParam('db_table') . '.added_by_user_id ASC', $auth->getParam('db_table') . '.added_by_user_id DESC');
$so->setColumn('modified_by_user_id', $auth->getParam('db_table') . '.modified_by_user_id ASC', $auth->getParam('db_table') . '.modified_by_user_id DESC');
$so->setColumn('added_datetime', $auth->getParam('db_table') . '.added_datetime ASC', $auth->getParam('db_table') . '.added_datetime DESC');
$so->setColumn('modified_datetime', $auth->getParam('db_table') . '.modified_datetime ASC', $auth->getParam('db_table') . '.modified_datetime DESC');

// Instantiate page numbers. Total items are set and calculation is done in the getRecordList function.
$page = new PageNumbers();
$page->setPerPage(getFormData('per_page'), 50);
$page->setPageNumber(getFormData('page_number', (getFormData('sort') ? 1 : null)));

// Query parameters to retain only locally.
$locally_carried_queries = array(
    'search_query',
    'break_list_cache' => $app->validBoomerangURL('accounts'),
);

/********************************************************************
* MAIN
********************************************************************/

// We may want to use the add/edit interface from another script, so this
// allows us to remember which page we came from so we can go back there.
if (getFormData('boomerang', false) && isset($_SERVER['HTTP_REFERER'])) {
    $app->setBoomerangURL($_SERVER['HTTP_REFERER'], 'admins');
}

if (getFormData('break_list_cache', false)) {
    // Remove any stale cached list data.
    $cache->delete('list');
}

// What action to take.
switch (getFormData('op')) {

case 'add' :
    // Initialize variables for the form template.
    $frm =& addRecordForm();
    $nav->add(_("Add Administrator"));
    $main_template = 'admin_form.ihtml';
    break;

case 'edit' :
    // Initialize variables for the form template.
    $frm =& editRecordForm(getFormData('admin_id'));
    $nav->add(_("Edit Administrator"));
    $main_template = 'admin_form.ihtml';
    break;

case 'del' :
    deleteRecord(getFormData('admin_id'));
    if ($app->validBoomerangURL('admins')) {
        // Display boomerang page.
        $app->dieBoomerangURL('admins', $locally_carried_queries);
    }
    // Display default page.
    $app->dieURL($_SERVER['PHP_SELF'], $locally_carried_queries);
    break;

case 'insert' :
    if (getFormdata('btn_cancel', false)) {
        if ($app->validBoomerangURL('admins')) {
            // Display boomerang page.
            $app->dieBoomerangURL('admins', $locally_carried_queries);
        }
        // Display default page.
        $app->dieURL($_SERVER['PHP_SELF'], $locally_carried_queries);
    }
    validateInput();
    if ($fv->anyErrors()) {
        $frm =& addRecordForm();
        $frm = array_merge($frm, getFormData());
        $nav->add(_("Add Administrator"));
        $main_template = 'admin_form.ihtml';
    } else {
        $admin_id = insertRecord(getFormData());
        if (getFormdata('btn_repeat', false)) {
            // Display function again.
            $app->dieURL($_SERVER['PHP_SELF'] . '?op=add', $locally_carried_queries);
        } else if ($app->validBoomerangURL('admins')) {
            // Display boomerang page.
            $app->dieBoomerangURL('admins', $locally_carried_queries);
        }
        // Display default page.
        $app->dieURL($_SERVER['PHP_SELF'], $locally_carried_queries);
    }
    break;

case 'update' :
    if (getFormdata('btn_reset', false)) {
        $app->raiseMsg(_("Saved values have been reloaded."), MSG_NOTICE, __FILE__, __LINE__);
        $app->dieURL($_SERVER['PHP_SELF'] . '?op=edit&admin_id=' . getFormData('admin_id'), $locally_carried_queries);
    }
    if (getFormdata('btn_cancel', false)) {
        // Remove lock
        $lock->select($auth->getParam('db_table'), $auth->getParam('db_primary_key'), getFormData('admin_id'));
        $lock->remove();
        if ($app->validBoomerangURL('admins')) {
            // Display boomerang page.
            $app->dieBoomerangURL('admins', $locally_carried_queries);
        }
        // Display default page.
        $app->dieURL($_SERVER['PHP_SELF'], $locally_carried_queries);
    }
    validateInput();
    if ($fv->anyErrors()) {
        $frm =& editRecordForm(getFormData('admin_id'));
        $frm = array_merge($frm, getFormData());
        $nav->add(_("Edit Administrator"));
        $main_template = 'admin_form.ihtml';
    } else {
        updateRecord(getFormData());
        if (getFormdata('btn_repeat', false)) {
            // Display edit function with next available ID.
            $qid = $db->query("SELECT " . $auth->getParam('db_primary_key') . " FROM " . $auth->getParam('db_table') . " WHERE " . $auth->getParam('db_primary_key') . " > '" . $db->escapeString(getFormData('admin_id')) . "' ORDER BY " . $auth->getParam('db_primary_key') . " ASC LIMIT 1");
            if (list($next_id) = mysql_fetch_row($qid)) {
                $app->dieURL($_SERVER['PHP_SELF'] . '?op=edit&admin_id=' . $next_id, $locally_carried_queries);
            } else {
                $app->raiseMsg(_("Cannot edit next, the end of the list was reached"), MSG_NOTICE, __FILE__, __LINE__);
            }
        } else if ($app->validBoomerangURL('admins')) {
            // Display boomerang page.
            $app->dieBoomerangURL('admins', $locally_carried_queries);
        }
        // Display default page.
        $app->dieURL($_SERVER['PHP_SELF'], $locally_carried_queries);
    }
    break;

default :
    $list =& getRecordList();
    $main_template = 'admin_list.ihtml';
    break;
}

/******************************************************************************
 * TEMPLATE INITIALIZATION
 *****************************************************************************/

$nav->set('body_id', 'admins');

include 'header.ihtml';
$app->carryQuery($locally_carried_queries);
include 'codebase/services/templates/' . $main_template;
include 'footer.ihtml';

/********************************************************************
* FUNCTIONS
********************************************************************/


function validateInput()
{
    global $fv, $auth;

    // If the username was changed during edit, verify.
    if (((getFormData('username') != getFormData('old_username')) && 'update' == getFormData('op'))
    || 'insert' == getFormData('op')) {
        if ($auth->usernameExists(getFormData('username'))) {
            $fv->addError('username', sprintf(_("The username %s already exists. Please choose another."), getFormData('username')));
        }
    }

    $fv->numericRange('admin_id', 0, 32767, _("<strong>Admin ID</strong> must be a valid number between 0 and 32767."));

    $fv->isEmpty('username', _("<strong>Username</strong> cannot be blank."));
    $fv->stringLength('username', 0, 255, _("<strong>Username</strong> must contain less than 256 characters."));

    if ('insert' == getFormData('op')) {
        $fv->isEmpty('userpass', _("<strong>Password</strong> cannot be blank."));
    }
    if ('insert' == getFormData('op') || ('update' == getFormData('op') && strlen(getFormData('userpass')) > 0)) {
        $fv->stringLength('userpass', 8, 100, _("<strong>Password</strong> must be between 8 and 100 characters long."));
    }

    $fv->stringLength('first_name', 0, 255, _("<strong>First name</strong> must contain less than 256 characters."));

    $fv->stringLength('last_name', 0, 255, _("<strong>Last name</strong> must contain less than 256 characters."));

    $fv->isEmpty('email', _("<strong>Email</strong> cannot be blank."));
    $fv->validateEmail('email');
}

function &addRecordForm()
{
    // Set default values for the reset of the fields.
    $frm = array(
        'admin_id' => '',
        'old_username' => '',
        'username' => '',
        'userpass' => '',
        'first_name' => '',
        'last_name' => '',
        'email' => '',
        'seconds_online' => '0',
        'last_login_datetime' => '',
        'last_access_datetime' => '',
        'last_login_ip' => '0.0.0.0',
        'added_by_user_id' => '',
        'modified_by_user_id' => '',
        'added_datetime' => '',
        'modified_datetime' => '',
        'new_op' => 'insert',
        'user_type' => '',
        'submit_buttons' => array(
            array('name' => 'btn_submit', 'value' => _("Add Administrator"), 'accesskey' => 's'),
            array('name' => 'btn_repeat', 'class' => 'secondary', 'value' => _("Add &amp; repeat"), 'accesskey' => 'r'),
            array('name' => 'btn_cancel', 'class' => 'secondary', 'value' => _("Cancel"), 'accesskey' => 'c'),
        ),
    );

    return $frm;
}

function &editRecordForm($id)
{
    global $auth;
    global $lock;
    global $locally_carried_queries;
    $app =& App::getInstance();
    $db =& DB::getInstance();

    $lock->select($auth->getParam('db_table'), $auth->getParam('db_primary_key'), $id);
    if ($lock->isLocked() && !$lock->isMine()) {
        $lock->dieErrorPage();
    }

    // Get the information for the form.
    $qid = $db->query("
        SELECT *,
        " . $auth->getParam('db_primary_key') . " AS admin_id
        FROM " . $auth->getParam('db_table') . "
        WHERE " . $auth->getParam('db_primary_key') . " = '" . $db->escapeString($id) . "'
    ");
    if (!$frm = mysql_fetch_assoc($qid)) {
        $app->logMsg('Could not find record with admin_id: ' . $id, LOG_WARNING, __FILE__, __LINE__);
        $app->raiseMsg(sprintf(_("The requested user %s could not be found."), $id), MSG_ERR, __FILE__, __LINE__);
        $app->dieBoomerangURL('admins', $locally_carried_queries);
    }

    // Lock this record.
    $lock->set($auth->getParam('db_table'), $auth->getParam('db_primary_key'), $id, $frm['username']);

    // Set misc values for the form.
    $frm = array_merge(array(
        'admin_id' => '',
        'old_username' => $frm['username'],
        'username' => '',
        'userpass' => '',
        'first_name' => '',
        'last_name' => '',
        'email' => '',
        'seconds_online' => '0',
        'last_login_datetime' => '',
        'last_access_datetime' => '',
        'last_login_ip' => '',
        'added_by_user_id' => '',
        'modified_by_user_id' => '',
        'added_datetime' => '',
        'modified_datetime' => '',
        'new_op' => 'update',
        'old_username' => $frm['username'],
        'submit_buttons' => array(
            array('name' => 'btn_submit', 'value' => _("Save changes"), 'accesskey' => 's'),
            array('name' => 'btn_repeat', 'class' => 'secondary', 'value' => _("Save & edit next"), 'accesskey' => 'e'),
            array('name' => 'btn_reset', 'class' => 'secondary', 'value' => _("Reset"), 'accesskey' => 'r'),
            array('name' => 'btn_cancel', 'class' => 'secondary', 'value' => _("Cancel"), 'accesskey' => 'c'),
        ),
    ), $frm);

    return $frm;
}

function deleteRecord($id)
{
    global $auth;
    global $lock;
    global $cache;
    global $locally_carried_queries;
    $app =& App::getInstance();
    $db =& DB::getInstance();

    $lock->select($auth->getParam('db_table'), $auth->getParam('db_primary_key'), $id);
    if ($lock->isLocked() && !$lock->isMine()) {
        $lock->dieErrorPage();
    }

    // Remove any stale cached list data.
    $cache->delete('list');

    // Get the information for this object.
    $qid = $db->query("
        SELECT " . $auth->getParam('db_username_column') . "
        FROM " . $auth->getParam('db_table') . "
        WHERE " . $auth->getParam('db_primary_key') . " = '" . $db->escapeString($id) . "'
    ");
    if (! list($name) = mysql_fetch_row($qid)) {
        $app->logMsg('Could not find record with admin_id: ' . $id, LOG_WARNING, __FILE__, __LINE__);
        $app->raiseMsg(sprintf(_("The requested user %s could not be found."), $id), MSG_ERR, __FILE__, __LINE__);
        $app->dieBoomerangURL('admins', $locally_carried_queries);
    }

    // Get the information for this object.
    $qid = $db->query("SELECT COUNT(*) from " . $auth->getParam('db_table') . "");
    list($num_admins) = mysql_fetch_row($qid);
    if ($num_admins <= 1) {
        // There must always be at least one administrator!
        $app->raiseMsg(_("You cannot delete the only user in the database. There must be at least one to log in and create other users."), MSG_NOTICE, __FILE__, __LINE__);
    } else if ($auth->get('user_id') == $id) {
        // Do not delete yourself!
        $app->raiseMsg(_("You cannot delete yourself."), MSG_NOTICE, __FILE__, __LINE__);
    } else {
        // Delete the record.
        $db->query("DELETE FROM " . $auth->getParam('db_table') . " WHERE " . $auth->getParam('db_primary_key') . " = '" . $db->escapeString($id) . "'");
        $app->raiseMsg(sprintf(_("The admin <em>%s</em> has been deleted."), $name), MSG_SUCCESS, __FILE__, __LINE__);
    }

    // Unlock record.
    $lock->remove();
}

function insertRecord($frm)
{
    global $auth;
    global $cache;
    $app =& App::getInstance();
    $db =& DB::getInstance();

    // Remove any stale cached list data.
    $cache->delete('list');

    // Insert record data.
    $db->query("
        INSERT INTO " . $auth->getParam('db_table') . " (
            " . $auth->getParam('db_username_column') . ",
            first_name,
            last_name,
            email,
            added_by_user_id,
            added_datetime
        ) VALUES (
            '" . $db->escapeString($frm['username']) . "',
            '" . $db->escapeString($frm['first_name']) . "',
            '" . $db->escapeString($frm['last_name']) . "',
            '" . $db->escapeString($frm['email']) . "',
            '" . $db->escapeString($auth->get('user_id')) . "',
            NOW()
        )
    ");
    $last_insert_id = mysql_insert_id($db->getDBH());

    // Set admin password.
    $auth->setPassword($last_insert_id, $frm['userpass']);

    // Create version.
    $version = Version::getInstance($auth);
    $version->create($auth->getParam('db_table'), $auth->getParam('db_primary_key'), $last_insert_id, $frm['username']);

    $app->raiseMsg(sprintf(_("The user <em>%s</em> has been added."), $frm['username']), MSG_SUCCESS, __FILE__, __LINE__);

    return $last_insert_id;
}

function updateRecord($frm)
{
    global $auth;
    global $lock;
    global $cache;
    $app =& App::getInstance();
    $db =& DB::getInstance();

    $lock->select($auth->getParam('db_table'), $auth->getParam('db_primary_key'), $frm['admin_id']);
    if ($lock->isLocked() && !$lock->isMine()) {
        $lock->dieErrorPage();
    }

    // Remove any stale cached list data.
    $cache->delete('list');

    // If the userpass is left blank or with the filler **** characters, we don't want to update it.
    if (!empty($frm['userpass']) && !preg_match('/[\*]{4,}/', $frm['userpass'])) {
        // Set user password.
        $auth->setPassword($frm['admin_id'], $frm['userpass']);
    }

    // Update record data.
    $db->query("
        UPDATE " . $auth->getParam('db_table') . " SET
            " . $auth->getParam('db_username_column') . " = '" . $db->escapeString($frm['username']) . "',
            first_name = '" . $db->escapeString($frm['first_name']) . "',
            last_name = '" . $db->escapeString($frm['last_name']) . "',
            email = '" . $db->escapeString($frm['email']) . "',
            modified_by_user_id = '" . $db->escapeString($auth->get('user_id')) . "',
            modified_datetime = NOW()
        WHERE " . $auth->getParam('db_primary_key') . " = '" . $db->escapeString($frm['admin_id']) . "'
    ");

    // Create version.
    $version = Version::getInstance($auth);
    $version->create($auth->getParam('db_table'), $auth->getParam('db_primary_key'), $frm['admin_id'], $frm['username']);

    $app->raiseMsg(sprintf(_("The user <em>%s</em> has been updated."), $frm['username']), MSG_SUCCESS, __FILE__, __LINE__);

    // Unlock record.
    $lock->remove();
}

function &getRecordList()
{
    global $page;
    global $so;
    global $tmp_prefs;
    global $cache;
    global $auth;
    $db =& DB::getInstance();

    $where_clause = '';

    // Build search query if available.
    if (getFormData('search_query', false)) {
        $qry_words = preg_split('/[^\w]/', getFormData('search_query'));
        for ($i=0; $i<sizeof($qry_words); $i++) {
            $where_clause .= (empty($where_clause) ? 'WHERE' : 'AND') . "
                (
                    " . $auth->getParam('db_table') . "." . $auth->getParam('db_username_column') . " LIKE '%" . $db->escapeString($qry_words[$i]) . "%'
                    OR " . $auth->getParam('db_table') . ".first_name LIKE '%" . $db->escapeString($qry_words[$i]) . "%'
                    OR " . $auth->getParam('db_table') . ".last_name LIKE '%" . $db->escapeString($qry_words[$i]) . "%'
                    OR " . $auth->getParam('db_table') . ".email LIKE '%" . $db->escapeString($qry_words[$i]) . "%'
                )
            ";
        }
    }

    if (getFormData('filter_user_type', false)) {
        // Limit by filter.
        $where_clause .= (empty($where_clause) ? 'WHERE' : 'AND') . " " . $auth->getParam('db_table') . ".user_type = '" . $db->escapeString(getFormData('filter_user_type')) . "'";
    }

    // Count the total number of records so we can do something about the page numbers.
    $qid = $db->query("
        SELECT COUNT(*)
        FROM " . $auth->getParam('db_table') . "
        $where_clause
    ");
    list($num_results) = mysql_fetch_row($qid);

    // Set page numbers now we know (needed for next step).
    $page->setTotalItems($num_results);
    $page->calculate();

    // Final SQL, with sort and page limiters.
    $sql = "
        SELECT
            " . $auth->getParam('db_table') . ".*,
            " . $auth->getParam('db_table') . "." . $auth->getParam('db_primary_key') . " AS admin_id,
            a1." . $auth->getParam('db_username_column') . " AS added_admin_username,
            a2." . $auth->getParam('db_username_column') . " AS modified_admin_username
        FROM " . $auth->getParam('db_table') . "
        LEFT JOIN " . $auth->getParam('db_table') . " a1 ON (" . $auth->getParam('db_table') . ".added_by_user_id = a1." . $auth->getParam('db_primary_key') . ")
        LEFT JOIN " . $auth->getParam('db_table') . " a2 ON (" . $auth->getParam('db_table') . ".modified_by_user_id = a2." . $auth->getParam('db_primary_key') . ")
        $where_clause
        " . $so->getSortOrderSQL() . "
        " . $page->getLimitSQL() . "
    ";

    // Use a cache hash to determine if the result-set has changed.
    // A unique key for this query, with the total_items in case db records
    // were added since the last cache. This identifies a unique set of
    // cached data, but we must refer to the list that is cached by a more
    // generic name. so that we can flush the cache (if records updated)
    // without knowing the hash.
    $cache_hash = md5($sql . '|' . $page->total_items);
    if ($tmp_prefs->get('cache_hash') != $cache_hash) {
        $cache->delete('list');
        $tmp_prefs->set('cache_hash', $cache_hash);
    }

    // First try to return from the cache.
    if ($cache->exists('list')) {
        return $cache->get('list');
    }

    // The list was not cached, so issue the real query.
    $qid = $db->query($sql);
    $list = array();
    while ($row = mysql_fetch_assoc($qid)) {
        $list[] = $row;
    }

    // Save this list into the cache.
    if (isset($list) && !empty($list)) {
        $cache->set('list', $list);
    }

    return $list;
}