PHP UserConfig::getDB方法代码示例

 public function getDailyRegistrations()
 {
     $db = UserConfig::getDB();
     $dailyregs = array();
     if ($stmt = $db->prepare('SELECT regdate, count(*) AS reqs FROM (SELECT CAST(regtime AS DATE) AS regdate, id AS regs FROM ' . UserConfig::$mysql_prefix . 'users u LEFT JOIN ' . UserConfig::$mysql_prefix . 'googlefriendconnect g ON u.id = g.user_id WHERE g.google_id IS NOT NULL GROUP BY id) agg group by agg.regdate')) {
         if (!$stmt->execute()) {
             throw new Exception("Can't execute statement: " . $stmt->error);
         }
         if (!$stmt->bind_result($regdate, $regs)) {
             throw new Exception("Can't bind result: " . $stmt->error);
         }
         while ($stmt->fetch() === TRUE) {
             $dailyregs[] = array('regdate' => $regdate, 'regs' => $regs);
         }
         $stmt->close();
     } else {
         throw new Exception("Can't prepare statement: " . $db->error);
     }
     return $dailyregs;
 }

function aggregatePoints()
{
    $db = UserConfig::getDB();
    if ($db->query('CREATE TEMPORARY TABLE activity_points (
	     activity_id int(2) UNSIGNED NOT NULL,
	     points int(4) UNSIGNED NOT NULL)') === TRUE) {
        $query = 'INSERT INTO activity_points VALUES';
        $pairs = array();
        foreach (UserConfig::$activities as $id => $activity) {
            $pairs[] = "({$id}, " . $activity[1] . ')';
        }
        $query .= ' ' . implode(', ', $pairs);
        if ($db->query($query) === TRUE) {
            if ($db->query('CREATE TEMPORARY TABLE user_activity_points
					SELECT u.id AS user_id, SUM(p.points) AS points
					FROM ' . UserConfig::$mysql_prefix . 'users u
					INNER JOIN ' . UserConfig::$mysql_prefix . 'activity a ON u.id = a.user_id
					INNER JOIN activity_points p ON a.activity_id = p.activity_id
					GROUP BY u.id')) {
                if ($stmt = $db->prepare('UPDATE ' . UserConfig::$mysql_prefix . 'users u
						INNER JOIN user_activity_points up ON u.id = up.user_id
						SET u.points = up.points')) {
                    if (!$stmt->execute()) {
                        throw new Exception("Can't execute statement: " . $stmt->error);
                    }
                    $stmt->close();
                } else {
                    throw new Exception("Can't prepare statement: " . $db->error);
                }
            } else {
                throw new Exception("Can't prepare statement: " . $db->error);
            }
        } else {
            throw new Exception("Can't prepare statement: " . $db->error);
        }
    } else {
        throw new Exception("Can't prepare statement: " . $db->error);
    }
}

 public function enableForUser($user)
 {
     if (!$this->enabled) {
         return;
     }
     // if feature is forced, return true
     if ($this->enabled_for_all) {
         return;
     }
     // now, let's see if user has it enabled
     $db = UserConfig::getDB();
     $userid = $user->getID();
     if ($stmt = $db->prepare('REPLACE INTO ' . UserConfig::$mysql_prefix . 'user_features (user_id, feature_id) VALUES (?, ?)')) {
         if (!$stmt->bind_param('ii', $userid, $this->id)) {
             throw new Exception("Can't bind parameter" . $stmt->error);
         }
         if (!$stmt->execute()) {
             throw new Exception("Can't execute statement: " . $stmt->error);
         }
         $stmt->close();
     } else {
         throw new Exception("Can't prepare statement: " . $db->error);
     }
 }

 public function save()
 {
     $db = UserConfig::getDB();
     $comment = mb_convert_encoding($this->comment, 'UTF-8');
     if ($stmt = $db->prepare('UPDATE ' . UserConfig::$mysql_prefix . 'invitation SET sentto = ?, user = ? WHERE code = ?')) {
         if (!$stmt->bind_param('sis', $this->comment, $comment, $this->code)) {
             throw new Exception("Can't bind parameter" . $stmt->error);
         }
         if (!$stmt->execute()) {
             throw new Exception("Can't execute statement: " . $stmt->error);
         }
         $stmt->close();
     } else {
         throw new Exception("Can't prepare statement: " . $db->error);
     }
     return;
 }

 public function processLogin($data, &$remember)
 {
     $remember = UserConfig::$allowRememberMe && array_key_exists('remember', $data);
     $db = UserConfig::getDB();
     $user = null;
     if ($stmt = $db->prepare('SELECT id, name, username, email, pass, salt, temppass, requirespassreset, fb_id FROM ' . UserConfig::$mysql_prefix . 'users WHERE username = ?')) {
         if (!$stmt->bind_param('s', $data['username'])) {
             throw new Exception("Can't bind parameter" . $stmt->error);
         }
         if (!$stmt->execute()) {
             throw new Exception("Can't execute statement: " . $stmt->error);
         }
         if (!$stmt->bind_result($id, $name, $username, $email, $pass, $salt, $temppass, $requirespassreset, $fb_id)) {
             throw new Exception("Can't bind result: " . $stmt->error);
         }
         if ($stmt->fetch() === TRUE) {
             if (sha1($salt . $data['pass']) == $pass) {
                 $user = new User($id, $name, $username, $email, $requirespassreset, $fb_id);
             }
         }
         $stmt->close();
         // if user used password recovery and remembered his old password
         // then clean temporary password and password reset flag
         // (don't reset the flag if was was set for some other reasons)
         if (!is_null($user) && !is_null($temppass) && $user->requiresPasswordReset()) {
             $user->setRequiresPasswordReset(false);
             $user->save();
             $user->resetTemporaryPassword();
         }
     } else {
         throw new Exception("Can't prepare statement: " . $db->error);
     }
     if (is_null($user)) {
         if ($stmt = $db->prepare('SELECT id, name, username, email, fb_id FROM ' . UserConfig::$mysql_prefix . 'users WHERE username = ? AND temppass = ? AND temppasstime > DATE_SUB(NOW(), INTERVAL 1 DAY)')) {
             if (!$stmt->bind_param('ss', $data['username'], $data['pass'])) {
                 throw new Exception("Can't bind parameter" . $stmt->error);
             }
             if (!$stmt->execute()) {
                 throw new Exception("Can't execute statement: " . $stmt->error);
             }
             if (!$stmt->bind_result($id, $name, $username, $email, $fb_id)) {
                 throw new Exception("Can't bind result: " . $stmt->error);
             }
             if ($stmt->fetch() === TRUE) {
                 $user = new User($id, $name, $username, $email, null, $fb_id);
             }
             $stmt->close();
             if (!is_null($user)) {
                 $user->setRequiresPasswordReset(true);
                 $user->save();
             }
         } else {
             throw new Exception("Can't prepare statement: " . $db->error);
         }
     } else {
         $user->resetTemporaryPassword();
     }
     if (!is_null($user)) {
         $user->recordActivity(USERBASE_ACTIVITY_LOGIN_UPASS);
     }
     return $user;
 }

 public function getTotalConnectedUsers()
 {
     $db = UserConfig::getDB();
     $module_id = $this->getID();
     $conns = 0;
     if ($stmt = $db->prepare('SELECT count(*) AS conns FROM ' . UserConfig::$mysql_prefix . 'users u LEFT JOIN ' . UserConfig::$mysql_prefix . 'user_oauth_identity oa ON u.id = oa.user_id WHERE oa.oauth_user_id IS NOT NULL AND oa.module = ?')) {
         if (!$stmt->bind_param('s', $module_id)) {
             throw new Exception("Can't bind parameter" . $stmt->error);
         }
         if (!$stmt->execute()) {
             throw new Exception("Can't execute statement: " . $stmt->error);
         }
         if (!$stmt->bind_result($conns)) {
             throw new Exception("Can't bind result: " . $stmt->error);
         }
         $stmt->fetch();
         $stmt->close();
     } else {
         throw new Exception("Can't prepare statement: " . $db->error);
     }
     return $conns;
 }

 public function recordActivity($activity_id)
 {
     $db = UserConfig::getDB();
     if ($stmt = $db->prepare('INSERT INTO ' . UserConfig::$mysql_prefix . 'activity (user_id, activity_id) VALUES (?, ?)')) {
         if (!$stmt->bind_param('ii', $this->userid, $activity_id)) {
             throw new Exception("Can't bind parameter" . $stmt->error);
         }
         if (!$stmt->execute()) {
             throw new Exception("Can't execute statement: " . $stmt->error);
         }
         $stmt->close();
     } else {
         throw new Exception("Can't prepare statement: " . $db->error);
     }
     if ($stmt = $db->prepare('UPDATE ' . UserConfig::$mysql_prefix . 'users SET points = points + ? WHERE id = ?')) {
         if (!$stmt->bind_param('ii', UserConfig::$activities[$activity_id][1], $this->userid)) {
             throw new Exception("Can't bind parameter" . $stmt->error);
         }
         if (!$stmt->execute()) {
             throw new Exception("Can't execute statement: " . $stmt->error);
         }
         $stmt->close();
     } else {
         throw new Exception("Can't prepare statement: " . $db->error);
     }
 }

 /**
  * Returns a list of generation cohorts
  *
  * @return array $cohorts an array of Cohort objects
  */
 public function getCohorts()
 {
     $db = UserConfig::getDB();
     $cohort_titles = array();
     foreach (UserConfig::$authentication_modules as $module) {
         $cohort_titles[$module->getID()] = $module->getTitle();
     }
     /**
      * The query must return a unique cohort_id, title and total members
      */
     $query = "SELECT regmodule AS cohort_id, COUNT(*) AS totals\n\t\t\tFROM " . UserConfig::$mysql_prefix . 'users';
     $siteadminsstring = null;
     if (count(UserConfig::$admins) > 0) {
         $siteadminsstring = implode(", ", UserConfig::$admins);
     }
     if (!is_null($siteadminsstring)) {
         $query .= "\nWHERE id NOT IN ({$siteadminsstring})";
     }
     $cohorts = array();
     $query .= ' GROUP BY cohort_id ORDER BY regtime DESC';
     if ($stmt = $db->prepare($query)) {
         if (!$stmt->execute()) {
             throw new Exception("Can't execute statement: " . $stmt->error);
         }
         if (!$stmt->bind_result($cohort_id, $total)) {
             throw new Exception("Can't bind result: " . $stmt->error);
         }
         while ($stmt->fetch() === TRUE) {
             $cohorts[] = new Cohort($cohort_id, $cohort_titles[$cohort_id], $total);
         }
         $stmt->close();
     } else {
         throw new Exception("Can't prepare statement: " . $db->error);
     }
     return $cohorts;
 }

 public function setAsCurrent($user)
 {
     $db = UserConfig::getDB();
     $accounts = self::getUserAccounts($user);
     $valid_account = false;
     foreach ($accounts as $account) {
         if ($this->isTheSameAs($account)) {
             $valid_account = true;
             break;
         }
     }
     if (!$valid_account) {
         return;
         // silently ignore if user is not connected to this account
     }
     if ($stmt = $db->prepare('UPDATE ' . UserConfig::$mysql_prefix . 'user_preferences SET current_account_id = ? WHERE user_id = ?')) {
         $userid = $user->getID();
         if (!$stmt->bind_param('ii', $this->id, $userid)) {
             throw new Exception("Can't bind parameter");
         }
         if (!$stmt->execute()) {
             throw new Exception("Can't update user preferences (set current account)");
         }
         $stmt->close();
     } else {
         throw new Exception("Can't update user preferences (set current account)");
     }
 }

    public static function getCampaignNameID($name)
    {
        $name = mb_convert_encoding($name, 'UTF-8');
        $db = UserConfig::getDB();
        $cmp_name_id = null;
        if ($stmt = $db->prepare('INSERT IGNORE INTO ' . UserConfig::$mysql_prefix . 'cmp (name) VALUES (?)')) {
            if (!$stmt->bind_param('s', $name)) {
                throw new Exception("Can't bind parameter");
            }
            if (!$stmt->execute()) {
                throw new Exception("Can't insert compaign name");
            }
            $stmt->close();
        } else {
            throw new Exception("Can't insert compaign name");
        }
        if ($stmt = $db->prepare('SELECT id FROM ' . UserConfig::$mysql_prefix . 'cmp
						WHERE name = ?')) {
            if (!$stmt->bind_param('s', $name)) {
                throw new Exception("Can't bind parameter" . $stmt->error);
            }
            if (!$stmt->execute()) {
                throw new Exception("Can't execute statement: " . $stmt->error);
            }
            if (!$stmt->bind_result($cmp_name_id)) {
                throw new Exception("Can't bind result: " . $stmt->error);
            }
            $stmt->fetch();
            $stmt->close();
        } else {
            throw new Exception("Can't prepare statement: " . $db->error);
        }
        return $cmp_name_id;
    }

*/
$versions[3]['up'][] = 'ALTER TABLE ' . UserConfig::$mysql_prefix . 'users ADD points INT(10) UNSIGNED NOT NULL DEFAULT 0';
$versions[3]['down'][] = 'ALTER TABLE ' . UserConfig::$mysql_prefix . 'users DROP COLUMN points';
/* -------------------------------------------------------------------------------------------------------
 * VERSION 2
 * Adding a field to indicate last time current user was retrieved
*/
$versions[2]['up'][] = 'ALTER TABLE ' . UserConfig::$mysql_prefix . 'users ADD last_accessed TIMESTAMP';
$versions[2]['down'][] = 'ALTER TABLE ' . UserConfig::$mysql_prefix . 'users DROP COLUMN last_accessed';
/* -------------------------------------------------------------------------------------------------------
 * VERSION 1
 * initial setup, mimicking tables.sql
*/
$versions[1]['up'][] = "CREATE TABLE `" . UserConfig::$mysql_prefix . "users` (\n  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n  `regtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Time of registration',\n  `name` text NOT NULL,\n  `username` varchar(25) DEFAULT NULL,\n  `email` varchar(255) DEFAULT NULL,\n  `pass` varchar(40) NOT NULL COMMENT 'Password digest',\n  `salt` varchar(13) NOT NULL COMMENT 'Salt',\n  `temppass` varchar(13) DEFAULT NULL COMMENT 'Temporary password used for password recovery',\n  `temppasstime` timestamp NULL DEFAULT NULL COMMENT 'Temporary password generation time',\n  `requirespassreset` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Flag indicating that user must reset their password before using the site',\n  `fb_id` bigint(20) unsigned DEFAULT NULL COMMENT 'Facebook user ID',\n  PRIMARY KEY (`id`),\n  UNIQUE KEY `username` (`username`),\n  UNIQUE KEY `email` (`email`),\n  UNIQUE KEY `fb_id` (`fb_id`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1";
$versions[1]['down'][] = "DROP TABLE `" . UserConfig::$mysql_prefix . "users`";
$versions[1]['up'][] = "CREATE TABLE `" . UserConfig::$mysql_prefix . "accounts` (\n  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n  `name` text,\n  `plan` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Payment plan ID',\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1";
$versions[1]['down'][] = "DROP TABLE `" . UserConfig::$mysql_prefix . "accounts`";
$versions[1]['up'][] = "CREATE TABLE `" . UserConfig::$mysql_prefix . "account_users` (\n  `account_id` int(10) unsigned NOT NULL DEFAULT '0',\n  `user_id` int(10) unsigned NOT NULL DEFAULT '0',\n  `role` tinyint(4) unsigned NOT NULL DEFAULT '0',\n  KEY `user_account` (`account_id`),\n  KEY `account_user` (`user_id`),\n  CONSTRAINT `account_user` FOREIGN KEY (`user_id`)\n\tREFERENCES `" . UserConfig::$mysql_prefix . "users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,\n  CONSTRAINT `" . UserConfig::$mysql_prefix . "account_users_ibfk_1` FOREIGN KEY (`account_id`)\n\tREFERENCES `" . UserConfig::$mysql_prefix . "accounts` (`id`),\n  CONSTRAINT `" . UserConfig::$mysql_prefix . "account_users_ibfk_2` FOREIGN KEY (`user_id`)\n\tREFERENCES `" . UserConfig::$mysql_prefix . "users` (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1";
$versions[1]['down'][] = "DROP TABLE `" . UserConfig::$mysql_prefix . "account_users`";
$versions[1]['up'][] = "CREATE TABLE `" . UserConfig::$mysql_prefix . "activity` (\n  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Time of activity',\n  `user_id` int(10) unsigned NOT NULL COMMENT 'User ID',\n  `activity_id` int(2) unsigned NOT NULL COMMENT 'Activity ID',\n  KEY `time` (`time`),\n  KEY `user_id` (`user_id`),\n  KEY `activity_id` (`activity_id`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Stores user activities'";
$versions[1]['down'][] = "DROP TABLE `" . UserConfig::$mysql_prefix . "activity`";
$versions[1]['up'][] = "CREATE TABLE `" . UserConfig::$mysql_prefix . "googlefriendconnect` (\n  `user_id` int(10) unsigned NOT NULL COMMENT 'User ID',\n  `google_id` varchar(255) NOT NULL COMMENT 'Google Friend Connect ID',\n  `userpic` text NOT NULL COMMENT 'Google Friend Connect User picture',\n  PRIMARY KEY (`user_id`,`google_id`),\n  CONSTRAINT `gfc_user` FOREIGN KEY (`user_id`)\n\tREFERENCES `" . UserConfig::$mysql_prefix . "users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE\n) ENGINE=InnoDB DEFAULT CHARSET=latin1";
$versions[1]['down'][] = "DROP TABLE `" . UserConfig::$mysql_prefix . "googlefriendconnect`";
$versions[1]['up'][] = "CREATE TABLE `" . UserConfig::$mysql_prefix . "invitation` (\n  `code` char(10) NOT NULL COMMENT 'Code',\n  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When invitation was created',\n  `issuedby` bigint(10) unsigned NOT NULL DEFAULT '1' COMMENT 'User who issued the invitation',\n  `sentto` text COMMENT 'Note about who this invitation was sent to',\n  `user` bigint(10) unsigned DEFAULT NULL COMMENT 'User name',\n  PRIMARY KEY (`code`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1";
$versions[1]['down'][] = "DROP TABLE `" . UserConfig::$mysql_prefix . "invitation`";
$versions[1]['up'][] = "CREATE TABLE `" . UserConfig::$mysql_prefix . "user_preferences` (\n  `user_id` int(10) unsigned NOT NULL DEFAULT '0',\n  `current_account_id` int(10) unsigned DEFAULT NULL,\n  PRIMARY KEY (`user_id`),\n  KEY `preference_current_account` (`current_account_id`),\n  CONSTRAINT `preference_user` FOREIGN KEY (`user_id`)\n\tREFERENCES `" . UserConfig::$mysql_prefix . "users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,\n  CONSTRAINT `user_preferences_ibfk_1` FOREIGN KEY (`user_id`)\n\tREFERENCES `" . UserConfig::$mysql_prefix . "users` (`id`),\n  CONSTRAINT `user_preferences_ibfk_2` FOREIGN KEY (`current_account_id`)\n\tREFERENCES `" . UserConfig::$mysql_prefix . "accounts` (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1";
$versions[1]['down'][] = "DROP TABLE `" . UserConfig::$mysql_prefix . "user_preferences`";
// creating DBUpgrade object with your database credentials and $versions defined above
// using 'UserBase' namespace to make sure we don't conflict with parent project's dbupgrade
$dbupgrade = new DBUpgrade(UserConfig::getDB(), $versions, 'UserBase');
require_once dirname(__FILE__) . '/dbupgrade/client.php';