public function getLastId() { $qb = new QueryBuilder(); $qb->select($qb->expr()->max(new Field('id'), 'lastId'))->from(Tbl::get('TBL_CHAT_MESSAGES')); $lastId = $this->query->exec($qb->getSQL())->fetchField('lastId'); return empty($lastId) ? 0 : $lastId; }
/** * Check validity of username, password and other auth factors * * @param string $username * @param string $password * @param array $additionalCredentials * @param boolean $writeCookie * @throws UserAuthFailedException * @return User */ public function checkCredentials($username, $password, $additionalCredentials = array(), $writeCookie = false) { $qb = new QueryBuilder(); $qb->select(new Field('id'), new Field('password'), new Field('salt'))->from(Tbl::get('TBL_USERS', 'UserManager'))->where($qb->expr()->equal(new Field('login'), $username)); $this->query->exec($qb->getSQL()); if ($this->query->countRecords() == 1) { $userData = $this->query->fetchRecord(); $hashToCheck = static::getUserPasswordHash($password, $userData['salt']); if ($userData['password'] === $hashToCheck) { $usr = $this->doLogin($userData['id'], $additionalCredentials, $writeCookie); try { $hookParams = array("user" => $usr, "additionalCredentials" => $additionalCredentials); HookManager::callHook("UserAuthSuccess", $hookParams); } catch (UserAuthFailedException $e) { $this->doLogout(); throw $e; } return $usr; } } // Failed login nothing returned from above code $hookParams = array("username" => $username, "password" => $password, "additionalCredentials" => $additionalCredentials); HookManager::callHook("UserAuthFail", $hookParams); throw new UserAuthFailedException("Incorrect login/password combination"); }
public static function getAllLanguages(MysqlPager $pager = null, $cacheMinutes = null) { $languages = array(); $sql = MySqlDbManager::getQueryObject(); $qb = new QueryBuilder(); $qb->select(new Field('*'))->from(Tbl::get('TBL_LANGUAGES')); if ($pager !== null) { $sql = $pager->executePagedSQL($qb->getSQL(), $cacheMinutes); } else { $sql->exec($qb->getSQL(), $cacheMinutes); } while (($lang_data = $sql->fetchRecord()) != false) { $l = new Language(); static::setData($lang_data, $l); $languages[] = $l; } return $languages; }
public static function logCustom($name, $value) { $remoteIP = ""; if (isset($_SERVER['REMOTE_ADDR'])) { $remoteIP = $_SERVER['REMOTE_ADDR']; } $qb = new QueryBuilder(); $qb->insert(Tbl::get('TBL_MIXED_LOG'))->values(array("session_id" => session_id(), "name" => $name, "value" => $value, "ip" => $remoteIP)); Reg::get('sql')->exec($qb->getSQL()); }
/** * Check if given country code is valid * * @param string $countryCode * @param int $cacheMinutes */ public function isValidCountryCode($countryCode = null, $cacheMinutes = null) { $qb = new QueryBuilder(); $qb->select($qb->expr()->count("*", "count"))->from(Tbl::get('TBL_LOCATIONS'))->where($qb->expr(new Field('country'), $countryCode)); $this->query->exec($qb->getSQL(), $cacheMinutes); $count = $this->query->fetchField('count'); if ($count > 0) { return true; } return false; }
/** * Get Mysql's current datetime by selecting NOW() * * @return string */ function getDBCurrentDateTime($isTimestamp = false) { $sql = MySqlDbManager::getQueryObject(); $qb = new QueryBuilder(); if ($isTimestamp) { $qb->select(new Func("UNIX_TIMESTAMP", new Func("NOW"), 'now')); } else { $qb->select(new Func("NOW", null, 'now')); } return $sql->exec($qb->getSQL())->fetchField('now'); }
public static function setControllerTemplateByHost(Host $host, $controller, $template) { $sql = MySqlDbManager::getQueryObject(); $qb = new QueryBuilder(); if (!empty($controller) or !empty($template)) { $qb->insert(Tbl::get('TBL_HOST_CONTROLLER_TEMPLATE'))->values(array('host_id' => $host->id, 'controller' => $controller, 'template' => $template))->onDuplicateKeyUpdate()->set(new Field('controller'), $controller)->set(new Field('template'), $template); } else { $qb->delete(Tbl::get('TBL_HOST_CONTROLLER_TEMPLATE'))->where($qb->expr()->equal(new Field('host_id'), $host->id)); } $sql->exec($qb->getSQL()); return $sql->affected(); }
public function fillUsersGps($userId, $leafId) { $qb = new QueryBuilder(); $qb->delete(Tbl::get('TBL_USERS_GPS'))->where($qb->expr()->equal(new Field('user_id'), $userId)); $this->query->exec($qb->getSQL()); $gpsTree = $this->getNodeTree($leafId); foreach ($gpsTree as $treeNode) { $qb = new QueryBuilder(); $qb->insert(Tbl::get('TBL_USERS_GPS'))->values(array('user_id' => $userId, 'node_id' => $treeNode["node_id"])); $this->query->exec($qb->getSQL()); } }
public function deleteGroup(TextsGroup $group) { if (empty($group->id)) { throw new InvalidArgumentException("Group ID have to be specified"); } if (!is_numeric($group->id)) { throw new InvalidArgumentException("Group ID have to be integer"); } $qb = new QueryBuilder(); $qb->delete(Tbl::get('TBL_TEXTS_GROUPS'))->where($qb->expr()->equal(new Field("id"), $group->id)); $this->query->exec($qb->getSQL()); return $this->query->affected(); }
public function deleteAllAliasesForTextValue(TextValue $textValue) { if (empty($textValue->id)) { throw new InvalidArgumentException("Text Value ID have to be specified"); } if (!is_numeric($textValue->id)) { throw new InvalidArgumentException("Text Value ID have to be integer"); } $qb = new QueryBuilder(); $qb->delete(Tbl::get('TBL_TEXTS_ALIASES'))->where($qb->expr()->equal(new Field("value_id"), $textValue->id)); $this->query->exec($qb->getSQL()); return $this->query->affected(); }
public static function logRequest($dbInstanceKey = null) { $sql = MySqlDbManager::getQueryObject($dbInstanceKey); $userId = "NULL"; $userObjectSerialized = "''"; $userObj = Reg::get(ConfigManager::getConfig("Users", "Users")->ObjectsIgnored->User); if ($userObj->isAuthorized()) { $userId = $userObj->id; $userObjectSerialized = "'" . mysql_real_escape_string(serialize($userObj)) . "'"; } $qb = new QueryBuilder(); $qb->insert(Tbl::get('TBL_REQUEST_LOG'))->values(array("user_id" => $userId, "user_obj" => $userObjectSerialized, "session_id" => session_id(), "get" => serialize($_GET), "post" => serialize($_POST), "server" => serialize($_SERVER), "cookies" => serialize($_COOKIE), "session" => serialize($_SESSION), "response" => ob_get_contents())); $sql->exec($qb->getSQL()); }
function __construct($host_id = null, $cacheMinutes = null, $dbInstanceKey = null) { if ($host_id !== null) { if (!is_numeric($host_id)) { throw new InvalidIntegerArgumentException("host_id argument should be an integer."); } $sql = MySqlDbManager::getQueryObject($dbInstanceKey); $qb = new QueryBuilder(); $qb->select(new Field('*'))->from(Tbl::get('TBL_HOSTS'))->where($qb->expr()->equal(new Field('id'), $host_id)); $sql->exec($qb->getSQL(), $cacheMinutes); if ($sql->countRecords()) { $res = $sql->fetchRecord(); static::setData($res, $this); } else { throw new InvalidArgumentException("Wrong host id is given. No record with id: {$host_id} in table " . Tbl::get('TBL_HOSTS')); } } }
/** * Is remote IP blocked by country * * @return boolean */ private function isBlockedByCountry($cacheMinutes = null) { $myLocation = Reg::get(ConfigManager::getConfig('GeoIP', 'GeoIP')->Objects->GeoIP)->getLocation(); if (empty($myLocation)) { return false; } $countryCode = $myLocation->country; if (empty($countryCode)) { return false; } $qb = new QueryBuilder(); $qb->select($qb->expr()->count('*', 'count'))->from(Tbl::get('TBL_SECURITY_BLACKLISTED_COUNTRIES'))->where($qb->expr()->equal(new Field('country'), $countryCode)); $this->query->exec($qb->getSQL(), $cacheMinutes); $count = $this->query->fetchField('count'); if ($count > 0) { return true; } return false; }
public function addEvent($name, $selfUserId, $userId = null, $data = array()) { if (empty($name)) { throw new InvalidArgumentException("\$name have to be non empty string"); } if (empty($selfUserId) or !is_numeric($selfUserId)) { throw new InvalidArgumentException("\$selfUserId have to be non zero integer"); } if ($userId !== null and (empty($userId) or !is_numeric($userId))) { throw new InvalidArgumentException("\$userId have to be non zero integer"); } if (!is_array($data)) { throw new InvalidArgumentException("\$data have to be array"); } $qb = new QueryBuilder(); $values = array('name' => $name, 'self_user_id' => $selfUserId, 'data' => serialize($data)); if ($userId !== null) { $values['user_id'] = $userId; } $qb->insert(Tbl::get('TBL_COMET_EVENTS'))->values($values); return $this->query->exec($qb->getSQL())->affected(); }
/** * Set user answers by their ids * * @param array $answers an array containing user's answers */ public function setAnswersByIds($answers) { if (is_array($answers)) { $qb = new QueryBuilder(); $qb->delete(Tbl::get('TBL_PROFILE_SAVE'))->where($qb->expr()->equal(new Field("user_id"), $this->userId)); $this->query->exec($qb->getSQL()); foreach ($answers as $answer) { if (is_numeric($answer)) { $qb = new QueryBuilder(); $qb->insert(Tbl::get('TBL_PROFILE_SAVE'))->values(array("user_id" => $this->userId, "profile_id" => $answer)); $this->query->exec($qb->getSQL()); } } $this->initUserAnswers(); } else { throw new UnexpectedValueException("\$answers have to array"); } }
public function removeUserFromGroup(User $user, UserGroup $group) { $qb = new QueryBuilder(); $qb->delete(Tbl::get('TBL_USERS_GROUPS', 'UserManager'))->where($qb->expr()->equal(new Field('user_id'), $user->id))->andWhere($qb->expr()->equal(new Field('group_id'), $group->id)); return $this->query->exec($qb->getSQL())->affected(); }
public function removePermissionFromGroup(Permission $perm, UserGroup $group) { $qb = new QueryBuilder(); $qb->delete(Tbl::get('TBL_GROUPS_PERMISSIONS', 'UserManager'))->where($qb->expr()->equal(new Field('permission_id'), $perm->id))->andWhere($qb->expr()->equal(new Field('group_id'), $group->id)); return $this->query->exec($qb->getSQL())->affected(); }
/** * @param integer $inviterUserId * @param integer $invitedUserId * @deprecated Sessions log insertd by mysql TRIGGER chat_sessions_log */ protected function insertSessionLog($inviterUserId, $invitedUserId) { if ($inviterUserId > $invitedUserId) { $userId1 = $inviterUserId; $userId2 = $invitedUserId; } else { $userId1 = $invitedUserId; $userId2 = $inviterUserId; } $qb = new QueryBuilder(); $qb->select(new Field('id'))->from(Tbl::get('TBL_CHAT_SESSIONS_LOG')); $andClause1 = new Andx(); $andClause1->add($qb->expr()->equal(new Field('user1_id', Tbl::get('TBL_CHAT_SESSIONS_LOG')), $userId1)); $andClause1->add($qb->expr()->equal(new Field('user2_id', Tbl::get('TBL_CHAT_SESSIONS_LOG')), $userId2)); $andClause2 = new Andx(); $andClause2->add($qb->expr()->equal(new Field('user1_id', Tbl::get('TBL_CHAT_SESSIONS_LOG')), $userId2)); $andClause2->add($qb->expr()->equal(new Field('user2_id', Tbl::get('TBL_CHAT_SESSIONS_LOG')), $userId1)); $orClause = new Orx(); $orClause->add($andClause1); $orClause->add($andClause2); $qb->andWhere($orClause); $this->query->exec($qb->getSQL()); $qb = new QueryBuilder(); if ($this->query->countRecords()) { $sesionId = $this->query->fetchField("id"); $qb->update(Tbl::get('TBL_CHAT_SESSIONS_LOG'))->set(new Field('datetime'), date(DEFAULT_DATETIME_FORMAT))->where($qb->expr()->equal(new Field('id'), $sesionId)); } else { $qb->insert(Tbl::get('TBL_CHAT_SESSIONS_LOG'))->values(array('user1_id' => $userId1, 'user2_id' => $userId2, 'datetime' => date(DEFAULT_DATETIME_FORMAT))); } $this->query->exec($qb->getSQL()); return $this->query->affected(); }
/** * Delete saved crop settings from DB * * @param string $fileName * @throws InvalidArgumentException */ public function deleteCropSettings($fileName) { if (empty($fileName)) { throw new InvalidArgumentException("\$fileName have to be non empty string"); } $qb = new QueryBuilder(); $qb->delete(Tbl::get("TBL_CROP_SETTINGS"))->where($qb->expr()->equal(new Field('filename'), $fileName)); $this->query->exec($qb->getSQL()); }
/** * Delete current host and Lang id alias value for config DB * @param ConfigDB $configDB * @param unknown_type $aliasHostLangId * @throws InvalidArgumentException */ public static function deleteDBConfigAlias(ConfigDB $configDB, $aliasHostLangId) { if (empty($configDB)) { throw new InvalidArgumentException("ConfigDB object is empty!"); } if (!is_numeric($configDB->id)) { throw new InvalidArgumentException("ConfigDB object's id is not numeric!"); } if (!is_numeric($aliasHostLangId)) { throw new InvalidArgumentException("Alias Host Language id is not numeric!"); } $qb = new QueryBuilder(); $qb->delete(Tbl::get("TBL_CONFIGS"))->where($qb->expr()->equal(new Field('alias_of'), $configDB->id))->andWhere($qb->expr()->equal(new Field('host_lang_id'), $aliasHostLangId)); $sql = MySqlDbManager::getQueryObject(); $sql->exec($qb->getSQL()); }
public function declinePhoto(UserPhoto $photo) { if (empty($photo->id)) { throw new InvalidArgumentException("UserPhoto object has no id!"); } if (empty($photo->userId)) { $photo = $this->getPhoto($photo->id); } $qb = new QueryBuilder(); $qb->update(Tbl::get('TBL_USERS_PHOTOS'))->set(new Field('status'), static::MODERATION_STATUS_DECLINED)->set(new Field('modification_date'), new Func('NOW'))->where($qb->expr()->equal(new Field('id'), $photo->id)); $this->query->exec($qb->getSQL()); $this->correctDefaultPhoto($photo->userId); }
private function closestParent($node_id, $parents, $cacheMinutes = null) { $my_id = $node_id; foreach ($parents as $parent) { if ($node_id == $parent['node_id']) { return $parent; } } while ($my_id != static::ROOT_NODE) { $qb = new QueryBuilder(); $qb->select(new Field('parent_id'))->from(Tbl::get('TBL_TREE'))->where($qb->expr()->equal(new Field('id'), $my_id)); $this->query->exec($qb->getSQL(), $cacheMinutes); $par_id = $this->query->fetchField('parent_id'); foreach ($parents as $parent) { if ($par_id == $parent['node_id']) { return $parent; } } $my_id = $par_id; } return false; }
/** * Lock job queue for working on current job * Helper funcion * @access private * @param JobQueueObj $job * @return TRUE|FALSE */ private function lockJob(JobQueueObj $job) { if (!is_numeric($job->id)) { throw JobQueueException("Job id is not numeric!"); } $qb = new QueryBuilder(); $qb->update(TBL::get('TBL_JOB_QUEUE'))->set(new Field('status'), self::JOB_STATUS_IN_PROCESS)->set(new Field('start_date'), new Func('NOW'))->where($qb->expr()->equal(new Field('id'), $job->id)); return $this->query->exec($qb->getSQL())->affected(); }
/** * Get all hosts *@return array Set of Host objects */ public static function getAllHosts(MysqlPager $pager = null, $cacheMinutes = null) { $hosts = array(); $sql = MySqlDbManager::getQueryObject(); $qb = new QueryBuilder(); $qb->select(new Field('*'))->from(Tbl::get('TBL_HOSTS', 'Host')); if ($pager !== null) { $sql = $pager->executePagedSQL($qb->getSQL(), $cacheMinutes); } else { $sql->exec($qb->getSQL(), $cacheMinutes); } while (($host_data = $sql->fetchRecord()) != false) { $h = new Host(); Host::setData($host_data, $h); $hosts[] = $h; } return $hosts; }
/** * Get list of blacklisted countries * * @return array */ public function getBlacklistedCountries() { $qb = new QueryBuilder(); $qb->select(new Field('country'))->from(Tbl::get('TBL_SECURITY_BLACKLISTED_COUNTRIES', 'IpFilter')); $this->query->exec($qb->getSQL()); return $this->query->fetchFields('country'); }
private static function updateQueryString(array $pageInfo, $id) { $qb = new QueryBuilder(); $qb->update(Tbl::get('TBL_PAGE_INFO', 'PageInfo'))->set(new Field('title'), $pageInfo['title'])->set(new Field('meta_keywords'), $pageInfo['keywords'])->set(new Field('meta_description'), $pageInfo['description'])->where($qb->expr()->equal(new Field('id'), $id)); return $qb->getSQL(); }
public function clearGarbage() { $db = MySqlDbManager::getDbObject(); $db->lockTables(Tbl::get('TBL_CONVERSATION_ATTACHEMENTS'), "w"); $qb = new QueryBuilder(); $qb->select(new Field("system_filename"))->from(Tbl::get('TBL_CONVERSATION_ATTACHEMENTS', 'ConversationAttachmentManager'))->where($qb->expr()->isNull(new Field('message_id')))->andWhere($qb->expr()->greater($qb->expr()->diff(new Func("NOW"), new Field('date')), 60 * 60 * 24 * $this->config->attachmentsClearTimeout)); $this->query->exec($qb->getSQL()); while (($row = $this->query->fetchRecord()) != null) { try { @unlink($this->config->uploadDir . $row['system_filename']); } catch (ErrorException $e) { } } $qb = new QueryBuilder(); $qb->delete(Tbl::get('TBL_CONVERSATION_ATTACHEMENTS', 'ConversationAttachmentManager'))->where($qb->expr()->isNull(new Field('message_id')))->andWhere($qb->expr()->greater($qb->expr()->diff(new Func("NOW"), new Field('date')), 60 * 60 * 24 * $this->config->attachmentsClearTimeout)); $deletedCount = $this->query->exec($qb->getSQL())->affected(); $db->unlockTables(); return $deletedCount; }
/** * CleanUp codes from DB that are too old */ public function cleanUp() { $time = 60 * 60 * 24 * $this->config->cleanUpTimeOut; $qb = new QueryBuilder(); $orX = new Orx(); $andX1 = new Andx(); $andX2 = new Andx(); $andX1->add($qb->expr()->less(new Func('UNIX_TIMESTAMP', new Field('issue_date')), $qb->expr()->diff(new Func('UNIX_TIMESTAMP', new Func('NOW')), $time))); $andX1->add($qb->expr()->equal(new Field('not_cleanable'), 0)); $andX1->add($qb->expr()->isNull(new Field('valid_until'))); $andX2->add($qb->expr()->isNotNull(new Field('valid_until'))); $andX2->add($qb->expr()->less(new Field('valid_until'), new Func('NOW'))); $orX->add($andX1); $orX->add($andX2); $qb->delete(Tbl::get('TBL_ONE_TIME_CODES'))->where($orX); $this->query->exec($qb->getSQL()); return $this->query->affected(); }
public function deleteTextValue(TextValue $textValue) { if (empty($textValue->id)) { throw new InvalidArgumentException("No ID specified in TextValue object"); } if (!is_numeric($textValue->id)) { throw new InvalidArgumentException("Text ID have to be integer"); } $qb = new QueryBuilder(); $qb->delete(Tbl::get('TBL_TEXTS_VALUES'))->where($qb->expr()->equal(new Field('id'), $textValue->id)); $this->query->exec($qb->getSQL()); return $this->query->affected(); }
protected function getNewUUID() { $found = false; $uuid = ''; while (!$found) { $uuid = generateRandomString(32); $qb = new QueryBuilder(); $qb->select($qb->expr()->count("*", 'cnt'))->from(Tbl::get('TBL_CONVERSATIONS'))->where($qb->expr()->equal(new Field('uuid'), $uuid)); $count = $this->query->exec($qb->getSQL())->fetchField('cnt'); if ($count == 0) { $found = true; } } return $uuid; }