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;
}
