PHP QueryBuilder::getSQL方法代码示例

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