Location: PHPKode > projects > ConPortal > conportal/inc/users/db.php
<?
/*
 *  ConPortal - Pomona College ITS scheduling appplication
 *  Copyright (C) 2005-2006  Pomona College
 *
 *  This program is free software; you can redistribute it and/or modify
 *  it under the terms of version 2 of the GNU General Public License
 *  as published by the Free Software Foundation.
 *
 *  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., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
 */

/*  Functions in this file:
 *     addUserToGroup
 *     createUser
 *     deactivateUser
 *     activateUser
 *     getAllUserDetails
 *     getAllDeactive
 *     getAllSuperUsers
 *     getGroupsForUser
 *     getNameForUser
 *     getUsernameForUser
 *     getPrimaryGroupForUser
 *     getSeniorityForUser
 *     getUserDetails
 *     getUserDetailsByUsername
 *     getUsers
 *     getUsersForPrimaryGroup
 *     getUsersForSecurityGroup
 *     updateUser
 *     removeUserFromGroup
 *     getSeniorityString
 *     getSeniorityLevels
 *     getUsersForEmail
 *    
 */

function addUserToGroup ($userPid, $groupPid)
{
	// Ensure that a duplicate row doesn't already exist
	$result = safeQuery("select * from user_group where user = %d and `group` = %d", $userPid, $groupPid);
	if (mysql_num_rows($result) == 0)
		safeQuery("insert into user_group set user = %d, `group` = %d", $userPid, $groupPid);
}

function createUser ($username, $first, $last, $primary_group, $super, $phone,
                     $cell, $sn, $seniority)
{
    // Check for a user account already around
    $info = getUserDetailsByUsername($username);
    if ($info)
	{
        if ($info['active'])
			error("This user account already exists.");
        else
			error("This user account exists, but is disabled. FIXME");
        return false;
    }
	
    $query = "INSERT INTO users SET username = '%s', first = '%s', ".
             "last = '%s', primary_group = %d, supervisor = '%s'";

    $query = sprintf($query, mysql_real_escape_string($username),
                             mysql_real_escape_string(ucfirst($first)),
                             mysql_real_escape_string(ucfirst($last)),
                             $primary_group,
                             mysql_real_escape_string($super));

    if (isset($phone) && $phone)
        $query .= ", phone = '".mysql_real_escape_string($phone)."'";
    if (isset($cell) && $cell)
        $query .= ", cell = '".mysql_real_escape_string($cell)."'";
    if (isset($sn) && $sn)
        $query .= ", screen_name = '".mysql_real_escape_string($sn)."'";
    if (isset($seniority) && $seniority)
        $query .= ", seniority = '".mysql_real_escape_string($seniority)."'";

	safeQuery($query);
    return mysql_insert_id();
}

// Delete all references to the user (so, look at users and user_group tables)
// and mark the user as NOT active in the users table...
// Also, remove the user from their primary_group because we would otherwise
// not be able to remove primary groups that contained deactivated users.
function deactivateUser ($pid)
{
	//safeQuery("delete from user_group where user = %d", $pid);
	//safeQuery("update users set active = 0, primary_group = 0 where pid = %d", $pid);

	//commented out the above to integrate the kinder, gentler Albany version.  Our brutal deactivation
	//of users has come to an end! :)
	safeQuery("update users set active = 0 where pid = %d", $pid);
}

function activateUser ($pid)
{
	safeQuery("update users set active = 1 where pid = %d", $pid);
}


// note from George: we want to seniority number, not the string, because we do comparisions using that 
// seniority number.  showSeniorityString should translate those id numbers to human-friendly text
function getAllUserDetails ()
{
    $result = safeQuery("select * from users where active = 1");
    $array = array();
    while ($row = mysql_fetch_assoc($result))
    {
        $row['name'] = $row['first'] . " " . $row['last'];
        $row['seniority_level'] = getSeniorityString($row['seniority']);
        $array[] = $row;
    }
    
    return $array;
}

function getAllDeactive ()
{
    $result = safeQuery("select * from users where active = 0");
    $array = array();
    while ($row = mysql_fetch_assoc($result))
    {
        $row['name'] = $row['first'] . " " . $row['last'];
        $row['seniority_level'] = getSeniorityString($row['seniority']);
        $array[] = $row;
    }
    
    return $array;
}

//I like this, but I'm worried that assuming Superusers are always going to be in 
//group 6 is a non-portable assumption.  I may be a worry-wart, however. :) -George
function getAllSuperUsers ()
{
    $result = safeQuery("select * from users where active = 1 and primary_group = 6");
    $array = array();
    while ($row = mysql_fetch_assoc($result))
    {
        $row['name'] = $row['first'] . " " . $row['last'];
        $row['seniority_level'] = getSeniorityString($row['seniority']);
        $array[] = $row;
    }
    
    return $array;
}

function getGroupsForUser ($userPid)
{
    $result = safeQuery("select groups.pid from user_group, groups where " .
	      "groups.pid = user_group.group and user_group.user = %d", $userPid);
    $array = array();
    while ($row = mysql_fetch_row($result))
        $array[] = $row[0];
    return $array;
}

function getNameForUser ($userPid)
{
    $t = getUserDetails($userPid);
    if ($t)
        return $t['name'];
    return "Unknown User";
}

function getUsernameForUser ($userPid) {
	$t = getUserDetails($userpid);
	if ($t)
		return $t['username'];
	return "Unknown User";
}

function getPrimaryGroupForUser ($userPid)
{
    $t = getUserDetails($userPid);
    if ($t)
        return $t['primary_group'];
    return NULL;
}

function getSeniorityForUser ($userPid)
{
    $t = getUserDetails($userPid);
    if ($t)
        return $t['seniority'];
    return NULL;
}

function getUserDetails ($pid)
{
	$result = safeQuery("select * from users where pid = %d", $pid);
    if (mysql_num_rows($result) == 0)
        return NULL;
    $ret = mysql_fetch_assoc($result);
    // everything and its mother needs a full name...
    $ret['name'] = $ret['first'] . " " . $ret['last'];
    $ret['seniority_level'] = getSeniorityString($ret['seniority']);
    return $ret;
}

function getUserDetailsByUsername ($username)
{
	$result = safeQuery("select * from users where username = '%s'",
		mysql_real_escape_string($username));
    if (mysql_num_rows($result) == 0)
        return NULL;
    $ret = mysql_fetch_assoc($result);
    $ret['name'] = $ret['first'] . " " . $ret['last'];
    $ret['seniority_level'] = getSeniorityString($ret['seniority']);
    return $ret;
}

/* Array of PIDs of all (active) users
 */
function getUsers ()
{
	$result = safeQuery("select pid from users where active = 1");
    $array = array();
    while ($row = mysql_fetch_row($result))
        $array[] = $row[0];
    return $array;
}

function getUsersForPrimaryGroup ($groupPid)
{
	$result = safeQuery("select pid from users where primary_group = %d", $groupPid);
    $a = array();
    while ($row = mysql_fetch_row($result))
        $a[] = $row[0];
    return $a;
}

function getUsersForSecurityGroup ($groupPid)
{
	$result = safeQuery("select users.pid from user_group, users where " .
		"users.pid = user_group.user and user_group.group = %d", $groupPid);
    $array = array();
    while ($row = mysql_fetch_row($result))
        $array[] = $row[0];
    return $array;
}

function updateUser ($pid, $user, $first, $last, $primary_group, $super,
                     $phone, $cell, $sn, $seniority)
{
    $query = "update users set username = '%s', first = '%s', last = '%s', ".
             "primary_group = %d, supervisor = '%s'";
    $query = sprintf($query, mysql_real_escape_string($user),
                             mysql_real_escape_string(ucfirst($first)),
                             mysql_real_escape_string(ucfirst($last)),
	                         $primary_group,
                             mysql_real_escape_string($super));

    if (isset($phone) && $phone)
        $query .= ", phone = '" . mysql_real_escape_string($phone) . "'";
    else
        $query .= ", phone = NULL";

    if (isset($cell) && $cell)
        $query .= ", cell = '" . mysql_real_escape_string($cell) . "'";
    else
        $query .= ", cell = NULL";

    if (isset($sn) && $sn)
        $query .= ", screen_name = '" . mysql_real_escape_string($sn) . "'";
    else
        $query .= ", screen_name = NULL";
	
    if (isset($seniority) && $seniority)
        $query .= ", seniority = '" . mysql_real_escape_string($seniority) . "'";
    else
        $query .= ", seniority = NULL";

    $query .= " where pid = '". intval($pid) . "'";

	safeQuery($query);    
}

// add user submitted data.
// ASSERT: data has already been checked for correctness!
function updateFromUser($whatField, $whatData, $whatUser)
{
	safeQuery("UPDATE users SET " . $whatField . " = \"". $whatData . "\" WHERE pid = " . $whatUser);
}

function removeUserFromGroup ($userPid, $groupPid)
{
    safeQuery("delete from user_group where user = %d AND `group` = %d", $userPid, $groupPid);
}

function getUsersForEmail($kind)
{
	$result = safeQuery("SELECT username FROM users WHERE active = 1 AND " . $kind . " = 1");
    $array = array();
    while ($row = mysql_fetch_assoc($result))
    {
        $row['email'] = $row['username'] . EMAIL_DOMAIN;
        $array[] = $row;
    }
    return $array;
}



?>
Return current item: ConPortal