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;
}
public function getDailyRegistrations()
{
$db = UserConfig::getDB();
$dailyregs = array();
if ($stmt = $db->prepare('SELECT CAST(regtime AS DATE) AS regdate, count(*) AS regs FROM ' . UserConfig::$mysql_prefix . 'users WHERE fb_id IS NOT NULL GROUP BY 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;
}
*/ $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';