PHP dbi_execute函数代码示例

function check_email($uemail)
{
    global $control, $error;
    if (!strlen($uemail)) {
        $error = translate('Email address cannot be blank.');
        return false;
    }
    $res = dbi_execute('SELECT cal_email FROM webcal_user WHERE cal_email = ?', array($uemail));
    if ($res) {
        $row = dbi_fetch_row($res);
        if ($row[0] == $uemail) {
            $control = '';
            $error = translate('Email address already exists.');
            return false;
        }
    }
    return true;
}

function db_load_config()
{
    global $webcalConfig;
    while (list($key, $val) = each($webcalConfig)) {
        $res = dbi_execute('SELECT cal_value FROM webcal_config
      WHERE cal_setting = ?', array($key), false, false);
        $sql = 'INSERT INTO webcal_config ( cal_setting, cal_value ) VALUES (?,?)';
        if (!$res) {
            dbi_execute($sql, array($key, $val));
        } else {
            // SQLite returns $res always.
            $row = dbi_fetch_row($res);
            if (!isset($row[0])) {
                dbi_execute($sql, array($key, $val));
            }
            dbi_free_result($res);
        }
    }
}

function delete_palm_events($login)
{
    $res = dbi_execute('SELECT cal_id FROM webcal_import_data
    WHERE cal_login = ? AND cal_import_type = ?', array($login, 'palm'));
    if ($res) {
        while ($row = dbi_fetch_row($res)) {
            dbi_execute('DELETE FROM webcal_blob WHERE cal_id = ?', array($row[0]));
            dbi_execute('DELETE FROM webcal_entry_log WHERE cal_entry_id = ?', array($row[0]));
            dbi_execute('DELETE FROM webcal_entry_repeats WHERE cal_id = ?', array($row[0]));
            dbi_execute('DELETE FROM webcal_entry_repeats_not WHERE cal_id = ?', array($row[0]));
            dbi_execute('DELETE FROM webcal_import_data WHERE cal_id = ?', array($row[0]));
            dbi_execute('DELETE FROM webcal_reminders WHERE cal_id = ?', array($row[0]));
            dbi_execute('DELETE FROM webcal_site_extras WHERE cal_id = ?', array($row[0]));
            dbi_execute('DELETE FROM webcal_entry_user WHERE cal_id = ?', array($row[0]));
            dbi_execute('DELETE FROM webcal_entry WHERE cal_id = ?', array($row[0]));
        }
    }
    dbi_free_result($res);
    return 1;
}

function save_layer($layer_user, $layeruser, $layercolor, $dups, $id)
{
    global $error, $layers;
    if ($layer_user == $layeruser) {
        $error = translate('You cannot create a layer for yourself.');
    }
    load_user_layers($layer_user, 1);
    if (!empty($layeruser) && $error == '') {
        // Existing layer entry.
        if (!empty($layers[$id]['cal_layeruser'])) {
            // Update existing layer entry for this user.
            $layerid = $layers[$id]['cal_layerid'];
            dbi_execute('UPDATE webcal_user_layers SET cal_layeruser = ?,
        cal_color = ?, cal_dups = ? WHERE cal_layerid = ?', array($layeruser, $layercolor, $dups, $layerid));
        } else {
            // New layer entry.
            // Check for existing layer for user. Can only have one layer per user.
            $res = dbi_execute('SELECT COUNT( cal_layerid ) FROM webcal_user_layers
        WHERE cal_login = ? AND cal_layeruser = ?', array($layer_user, $layeruser));
            if ($res) {
                $row = dbi_fetch_row($res);
                if ($row[0] > 0) {
                    $error = translate('You can only create one layer for each user.');
                }
                dbi_free_result($res);
            }
            if ($error == '') {
                $res = dbi_execute('SELECT MAX( cal_layerid ) FROM webcal_user_layers');
                if ($res) {
                    $row = dbi_fetch_row($res);
                    $layerid = $row[0] + 1;
                } else {
                    $layerid = 1;
                }
                dbi_execute('INSERT INTO webcal_user_layers ( cal_layerid, cal_login,
          cal_layeruser, cal_color, cal_dups ) VALUES ( ?, ?, ?, ?, ? )', array($layerid, $layer_user, $layeruser, $layercolor, $dups));
            }
        }
    }
}

function doDbSanityCheck()
{
    global $db_database, $db_host, $db_login;
    $dieMsgStr = 'Error finding WebCalendar tables in database "' . $db_database . '" using db login "' . $db_login . '" on db server "' . $db_host . '".<br /><br />
Have you created the database tables as specified in the
<a href="docs/WebCalendar-SysAdmin.html" ' . '  target="other">WebCalendar System Administrator\'s Guide</a>?';
    $res = @dbi_execute('SELECT COUNT( cal_value ) FROM webcal_config', array(), false, false);
    if ($res) {
        if ($row = dbi_fetch_row($res)) {
            // Found database. All is peachy.
            dbi_free_result($res);
        } else {
            // Error accessing table.
            // User has wrong db name or has not created tables.
            // Note: can't translate this since translate.php is not included yet.
            dbi_free_result($res);
            die_miserable_death($dieMsgStr);
        }
    } else {
        die_miserable_death($dieMsgStr);
    }
}

function save_pref($prefs, $src)
{
    global $my_theme, $prefuser;
    while (list($key, $value) = each($prefs)) {
        if ($src == 'post') {
            $setting = substr($key, 5);
            $prefix = substr($key, 0, 5);
            if ($prefix != 'pref_') {
                continue;
            }
            // validate key name.  should start with "pref_" and not include
            // any unusual characters that might cause SQL injection
            if (!preg_match('/pref_[A-Za-z0-9_]+$/', $key)) {
                die_miserable_death(str_replace('XXX', $key, translate('Invalid setting name XXX.')));
            }
        } else {
            $setting = $key;
            $prefix = 'pref_';
        }
        //echo "Setting = $setting, key = $key, prefix = $prefix<br />\n";
        if (strlen($setting) > 0 && $prefix == 'pref_') {
            if ($setting == 'THEME' && $value != 'none') {
                $my_theme = strtolower($value);
            }
            $sql = 'DELETE FROM webcal_user_pref WHERE cal_login = ? ' . 'AND cal_setting = ?';
            dbi_execute($sql, array($prefuser, $setting));
            if (strlen($value) > 0) {
                $setting = strtoupper($setting);
                $sql = 'INSERT INTO webcal_user_pref ' . '( cal_login, cal_setting, cal_value ) VALUES ' . '( ?, ?, ? )';
                if (!dbi_execute($sql, array($prefuser, $setting, $value))) {
                    $error = 'Unable to update preference: ' . dbi_error() . '<br /><br /><span class="bold">SQL:</span>' . $sql;
                    break;
                }
            }
        }
    }
}

function save_pref($prefs, $src)
{
    global $error, $my_theme, $prad;
    if (!$prad) {
        global $prefuser;
    }
    $pos = $prad ? 6 : 5;
    while (list($key, $value) = each($prefs)) {
        if ($src == 'post') {
            $prefix = substr($key, 0, $pos);
            $setting = substr($key, $pos);
            if (!$prad && $prefix != 'pref_' || $prad && $key == 'currenttab') {
                continue;
            }
            // .
            // Validate key name.
            // If $prad not true, should start with "pref_"
            // else should start with "admin_",
            // and not include any unusual characters that might be an SQL injection attack.
            if (!$prad && !preg_match('/pref_[A-Za-z0-9_]+$/', $key) || $prad && !preg_match('/admin_[A-Za-z0-9_]+$/', $key)) {
                die_miserable_death(str_replace('XXX', $key, translate('Invalid setting name XXX.')));
            }
        } else {
            $prefix = $prad ? 'admin_' : 'pref_';
            $setting = $key;
        }
        if (strlen($setting) > 0 && $prefix == 'pref_' || $prefix == 'admin_') {
            if ($setting == 'THEME' && $value != 'none') {
                $my_theme = strtolower($value);
            }
            if ($prad) {
                $setting = strtoupper($setting);
                $sql = 'DELETE FROM webcal_config WHERE cal_setting = ?';
                if (!dbi_execute($sql, array($setting))) {
                    $error = db_error(false, $sql);
                    break;
                }
                if (strlen($value) > 0) {
                    $sql = 'INSERT INTO webcal_config ( cal_setting, cal_value ) VALUES ( ?, ? )';
                    if (!dbi_execute($sql, array($setting, $value))) {
                        $error = db_error(false, $sql);
                        break;
                    }
                }
            } else {
                dbi_execute('DELETE FROM webcal_user_pref WHERE cal_login = ?
          AND cal_setting = ?', array($prefuser, $setting));
                if (strlen($value) > 0) {
                    $setting = strtoupper($setting);
                    $sql = 'INSERT INTO webcal_user_pref ( cal_login, cal_setting,
            cal_value ) VALUES ( ?, ?, ? )';
                    if (!dbi_execute($sql, array($prefuser, $setting, $value))) {
                        $error = 'Unable to update preference: ' . dbi_error() . '<br /><br /><span class="bold">SQL:</span>' . $sql;
                        break;
                    }
                }
            }
        }
    }
    // Reload preferences so any CSS changes will take effect.
    load_global_settings();
    load_user_preferences();
}

function access_load_user_functions($user)
{
    global $is_admin;
    static $permissions;
    if (!empty($permissions[$user])) {
        return $permissions[$user];
    }
    $ret = '';
    $rets = array();
    $users = array($user, '__default__');
    for ($i = 0, $cnt = count($users); $i < $cnt && empty($ret); $i++) {
        $res = dbi_execute('SELECT cal_permissions FROM webcal_access_function
      WHERE cal_login = ?', array($users[$i]));
        assert('$res');
        if ($row = dbi_fetch_row($res)) {
            $rets[$users[$i]] = $row[0];
        }
        dbi_free_result($res);
    }
    // If still no setting found, then assume access to everything
    // if an admin user, otherwise access to all non-admin functions.
    if (!empty($rets[$user])) {
        $ret = $rets[$user];
    } else {
        if (!empty($rets['__default__'])) {
            $ret = $rets['__default__'];
        } else {
            for ($i = 0; $i < ACCESS_NUMBER_FUNCTIONS; $i++) {
                $ret .= get_default_function_access($i, $user);
            }
        }
    }
    // do_debug ( $user . " " . $ret);
    $permissions[$user] = $ret;
    return $ret;
}

}
// Parse $users.
$exp = split(',', $users);
$groups = $selected = $sql_params = array();
for ($i = 0, $cnt = count($exp); $i < $cnt; $i++) {
    $selected[$exp[$i]] = 1;
}
$owner = $is_nonuser_admin || $is_assistant ? $user : $login;
// Load list of groups.
$sql = 'SELECT wg.cal_group_id, wg.cal_name FROM webcal_group wg';
if ($USER_SEES_ONLY_HIS_GROUPS == 'Y') {
    $sql .= ', webcal_group_user wgu WHERE wg.cal_group_id = wgu.cal_group_id
    AND wgu.cal_login = ?';
    $sql_params[] = $owner;
}
$res = dbi_execute($sql . ' ORDER BY wg.cal_name', $sql_params);
if ($res) {
    while ($row = dbi_fetch_row($res)) {
        $groups[] = array('cal_group_id' => $row[0], 'cal_name' => $row[1]);
    }
    dbi_free_result($res);
}
print_header('', '', '', true, false, true);
ob_start();
echo '
    <script language="javascript" type="text/javascript">';
include 'includes/js/usersel.php';
echo '
    </script>
    <center>
      <form action="#" name="userselform">

    }
}
// TODO: Move this SQL along with the SQL in activity_log.php to a shared function.
$sql_params = array();
$sql = 'SELECT wel.cal_login, wel.cal_user_cal, wel.cal_type, wel.cal_date,
  wel.cal_time, we.cal_name, wel.cal_log_id
  FROM webcal_entry_log wel, webcal_entry we WHERE wel.cal_entry_id = we.cal_id ';
if (!empty($startid)) {
    $sql .= 'AND wel.cal_log_id <= ? ';
    $sql_params[] = $startid;
}
$sql .= 'ORDER BY wel.cal_log_id DESC';
if (!empty($WS_DEBUG) && $WS_DEBUG) {
    ws_log_message('SQL> ' . $sql . "\n\n");
}
$res = dbi_execute($sql, $sql_params);
$out = '
<activitylog>';
if ($res) {
    $out .= '
<!-- in if -->';
    $cnt = 0;
    while (($row = dbi_fetch_row($res)) && $cnt < $num) {
        $out .= '
<!-- in while type: $row[2] -->
  <log>
    <login>' . ws_escape_xml($row[0]) . '</login>
    <calendar>' . ws_escape_xml($row[1]) . '</calendar>
    <type>' . ws_escape_xml($row[2]) . '</type>
    <date>' . ws_escape_xml($row[3]) . '</date>
    <time>' . ws_escape_xml($row[4]) . '</time>

        if ($nlastname) {
            $sql .= ' cal_lastname = ?,';
            $sql_params[] = $nlastname;
        }
        if ($nfirstname) {
            $sql .= ' cal_firstname = ?,';
            $sql_params[] = $nfirstname;
        }
        $sql_params[] = $nadmin;
        $sql_params[] = $nid;
        if (!dbi_execute($sql . ' cal_admin = ? WHERE cal_login = ?', $sql_params)) {
            $error = db_error();
        }
    } else {
        // Adding
        if (preg_match('/^[\\w]+$/', $nid)) {
            $nid = $NONUSER_PREFIX . $nid;
            if (!dbi_execute('INSERT INTO webcal_nonuser_cals ( cal_login,
        cal_firstname, cal_lastname, cal_admin ) VALUES ( ?, ?, ?, ? )', array($nid, $nfirstname, $nlastname, $nadmin))) {
                $error = db_error();
            }
        } else {
            $error = translate('Calendar ID') . ' ' . translate('word characters only') . '.';
        }
    }
}
if (empty($error)) {
    do_redirect('nonusers.php');
}
print_header();
echo print_error($error) . print_trailer();

/**
 * Get a list of users and return info in an array.
 *
 * @return array Array of user info
 */
function user_get_users($publicOnly = false)
{
    global $PUBLIC_ACCESS, $PUBLIC_ACCESS_FULLNAME, $USER_SORT_ORDER;
    $count = 0;
    $ret = array();
    if ($PUBLIC_ACCESS == 'Y') {
        $ret[$count++] = array('cal_login' => '__public__', 'cal_lastname' => '', 'cal_firstname' => '', 'cal_is_admin' => 'N', 'cal_email' => '', 'cal_password' => '', 'cal_fullname' => $PUBLIC_ACCESS_FULLNAME);
    }
    if ($publicOnly) {
        return $ret;
    }
    0 - ($order1 = empty($USER_SORT_ORDER) ? 'cal_lastname, cal_firstname,' : "{$USER_SORT_ORDER},");
    $res = dbi_execute('SELECT cal_login, cal_lastname, cal_firstname,
    cal_is_admin, cal_email, cal_passwd FROM webcal_user
    ORDER BY $order1 cal_login');
    if ($res) {
        while ($row = dbi_fetch_row($res)) {
            if (strlen($row[1]) && strlen($row[2])) {
                $fullname = $order1 == 'cal_lastname, cal_firstname,' ? "{$row['1']} {$row['2']}" : "{$row['2']} {$row['1']}";
            } else {
                $fullname = $row[0];
            }
            $ret[$count++] = array('cal_login' => $row[0], 'cal_lastname' => $row[1], 'cal_firstname' => $row[2], 'cal_is_admin' => $row[3], 'cal_email' => empty($row[4]) ? '' : $row[4], 'cal_password' => $row[5], 'cal_fullname' => $fullname);
        }
        dbi_free_result($res);
    }
    //no need to call sort_users () as the sql can sort for us
    return $ret;
}

function user_delete_user($user)
{
    // Get event ids for all events this user is a participant
    $events = get_users_event_ids($user);
    // Now count number of participants in each event...
    // If just 1, then save id to be deleted
    $delete_em = array();
    for ($i = 0; $i < count($events); $i++) {
        $res = dbi_execute('SELECT COUNT( * ) FROM webcal_entry_user WHERE cal_id = ?', array($events[$i]));
        if ($res) {
            if ($row = dbi_fetch_row($res)) {
                if ($row[0] == 1) {
                    $delete_em[] = $events[$i];
                }
            }
            dbi_free_result($res);
        }
    }
    // Now delete events that were just for this user
    for ($i = 0; $i < count($delete_em); $i++) {
        dbi_execute("DELETE FROM webcal_entry_repeats WHERE cal_id = ?", array($delete_em[$i]));
        dbi_execute("DELETE FROM webcal_entry_repeats_not WHERE cal_id = ?", array($delete_em[$i]));
        dbi_execute("DELETE FROM webcal_entry_log WHERE cal_entry_id = ?", array($delete_em[$i]));
        dbi_execute("DELETE FROM webcal_import_data WHERE cal_id = ?", array($delete_em[$i]));
        dbi_execute("DELETE FROM webcal_site_extras WHERE cal_id = ?", array($delete_em[$i]));
        dbi_execute("DELETE FROM webcal_entry_ext_user WHERE cal_id = ?", array($delete_em[$i]));
        dbi_execute("DELETE FROM webcal_reminders WHERE cal_id = ?", array($delete_em[$i]));
        dbi_execute("DELETE FROM webcal_blob WHERE cal_id = ?", array($delete_em[$i]));
        dbi_execute("DELETE FROM webcal_entry WHERE cal_id = ?", array($delete_em[$i]));
    }
    // Delete user participation from events
    dbi_execute("DELETE FROM webcal_entry_user WHERE cal_login = ?", array($user));
    // Delete preferences
    dbi_execute("DELETE FROM webcal_user_pref WHERE cal_login = ?", array($user));
    // Delete from groups
    dbi_execute("DELETE FROM webcal_group_user WHERE cal_login = ?", array($user));
    // Delete bosses & assistants
    dbi_execute("DELETE FROM webcal_asst WHERE cal_boss = ?", array($user));
    dbi_execute("DELETE FROM webcal_asst WHERE cal_assistant = ?", array($user));
    // Delete user's views
    $delete_em = array();
    $res = dbi_execute("SELECT cal_view_id FROM webcal_view WHERE cal_owner = ?", array($user));
    if ($res) {
        while ($row = dbi_fetch_row($res)) {
            $delete_em[] = $row[0];
        }
        dbi_free_result($res);
    }
    for ($i = 0; $i < count($delete_em); $i++) {
        dbi_execute("DELETE FROM webcal_view_user WHERE cal_view_id = ?", array($delete_em[$i]));
    }
    dbi_execute("DELETE FROM webcal_view WHERE cal_owner = ?", array($user));
    //Delete them from any other user's views
    dbi_execute("DELETE FROM webcal_view_user WHERE cal_login = ?", array($user));
    // Delete layers
    dbi_execute("DELETE FROM webcal_user_layers WHERE cal_login = ?", array($user));
    // Delete any layers other users may have that point to this user.
    dbi_execute("DELETE FROM webcal_user_layers WHERE cal_layeruser = ?", array($user));
    // Delete user
    dbi_execute("DELETE FROM webcal_user WHERE cal_login = ?", array($user));
    // Delete function access
    dbi_execute("DELETE FROM webcal_access_function WHERE cal_login = ?", array($user));
    // Delete user access
    dbi_execute("DELETE FROM webcal_access_user WHERE cal_login = ?", array($user));
    dbi_execute("DELETE FROM webcal_access_user WHERE cal_other_user = ?", array($user));
    // Delete user's categories
    dbi_execute("DELETE FROM webcal_categories WHERE cat_owner = ?", array($user));
    dbi_execute("DELETE FROM webcal_entry_categories WHERE cat_owner = ?", array($user));
    // Delete user's reports
    $delete_em = array();
    $res = dbi_execute("SELECT cal_report_id FROM webcal_report WHERE cal_login = ?", array($user));
    if ($res) {
        while ($row = dbi_fetch_row($res)) {
            $delete_em[] = $row[0];
        }
        dbi_free_result($res);
    }
    for ($i = 0; $i < count($delete_em); $i++) {
        dbi_execute("DELETE FROM webcal_report_template WHERE cal_report_id = ?", array($delete_em[$i]));
    }
    dbi_execute("DELETE FROM webcal_report WHERE cal_login = ?", array($user));
    //not sure about this one???
    dbi_execute("DELETE FROM webcal_report WHERE cal_user = ?", array($user));
    // Delete user templates
    dbi_execute("DELETE FROM webcal_user_template WHERE cal_login = ?", array($user));
}

          <td class="aligntop bold">' . translate('Created by') . ':</td>
          <td>' . $groupowner . '</td>
        </tr>' : '') . '
        <tr>
          <td class="aligntop bold"><label for="users">' . translate('Users') . ':</label></td>
          <td>
            <select name="users[]" id="users" size="10" multiple="multiple">';
// Get list of all users.
$users = user_get_users();
if ($NONUSER_ENABLED == 'Y') {
    $nonusers = get_nonuser_cals();
    $users = $NONUSER_AT_TOP == 'Y' ? array_merge($nonusers, $users) : array_merge($users, $nonusers);
}
// Get list of users for this group.
if (!$newgroup) {
    $res = dbi_execute('SELECT cal_login FROM webcal_group_user
    WHERE cal_group_id = ?', array($id));
    if ($res) {
        while ($row = dbi_fetch_row($res)) {
            $groupuser[$row[0]] = 1;
        }
        dbi_free_result($res);
    }
}
for ($i = 0, $cnt = count($users); $i < $cnt; $i++) {
    $u = $users[$i]['cal_login'];
    echo '
              <option value="' . $u . '" ' . (!empty($groupuser[$u]) ? ' selected="selected"' : '') . '>' . $users[$i]['cal_fullname'] . '</option>';
}
echo '
            </select>
          </td>

include_once 'includes/init.php';
load_user_layers();
$status = getValue('status', '(on|off)', true);
$public = getValue('public');
if ($ALLOW_VIEW_OTHER != 'Y') {
    print_header();
    echo print_not_auth(7) . print_trailer();
    exit;
}
$updating_public = false;
$url = 'layers.php';
if ($is_admin && !empty($public) && $PUBLIC_ACCESS == 'Y') {
    $updating_public = true;
    $layer_user = '__public__';
    $url .= '?public=1';
} else {
    $layer_user = $login;
}
dbi_execute('DELETE FROM webcal_user_pref WHERE cal_login = ?
  AND cal_setting = \'LAYERS_STATUS\'', array($layer_user));
$sql = 'INSERT INTO webcal_user_pref ( cal_login, cal_setting, cal_value )
  VALUES ( ?, \'LAYERS_STATUS\', ? )';
if (!dbi_execute($sql, array($layer_user, $status == 'off' ? 'N' : 'Y'))) {
    $error = translate('Unable to update preference') . ': ' . dbi_error() . '<br /><br /><span class="bold">SQL:</span> ' . $sql;
    break;
}
if (empty($error)) {
    do_redirect($url);
}
print_header();
echo print_error($error, true) . print_trailer();