protected static function queryString($lang_id = null, $host_id = null, $module = null, $page = null, $cacheMinutes = null)
{
$qb = new QueryBuilder();
$qb->select(new Field('title'), new Field('meta_keywords'), new Field('meta_description'))->from(Tbl::get('TBL_PAGE_INFO'));
if ($lang_id === null) {
$qb->andWhere($qb->expr()->isNull(new Field('lang_id')));
} else {
$qb->andWhere($qb->expr()->equal(new Field('lang_id'), $lang_id));
}
if ($host_id === null) {
$qb->andWhere($qb->expr()->isNull(new Field('host_id')));
} else {
$qb->andWhere($qb->expr()->equal(new Field('host_id'), $host_id));
}
if ($module === null) {
$qb->andWhere($qb->expr()->isNull(new Field('module')));
} else {
$qb->andWhere($qb->expr()->equal(new Field('module'), $module));
}
if ($page === null) {
$qb->andWhere($qb->expr()->isNull(new Field('page')));
} else {
$qb->andWhere($qb->expr()->equal(new Field('page'), $page));
}
return $qb->getSQL();
}
/**
* Function returns array of nodes by given node's latitude and longitude.
* $distance is a max distance from center point to get nodes.
* $bounding_distance is a measurement if the square in which we seek for the nodes.
* This is done for performance for not to calculate distances for all nodes in DB.
*
* @param array $node array("lat" => integer, "lng" => integer, optional("id" => nodId))
* @param Integer $distance
* @param Integer $bounding_distance
* @throws InvalidIntegerArgumentException
* @return Array|boolean
*/
public function getNearestNodes($node, $distance = 10, $bounding_distance = 3)
{
if (empty($node)) {
throw new InvalidIntegerArgumentException("Node is mepty");
}
$lat = $node["lat"];
$long = $node["lng"];
$qb = new QueryBuilder();
$qb->select('*', new Field($qb->expr()->prod($qb->expr()->prod($qb->expr()->quot($qb->expr()->prod(new Func('ACOS', $qb->expr()->sum($qb->expr()->prod(new Func('SIN', $qb->expr()->quot($qb->expr()->prod($lat, new Func('PI')), 180)), new Func('SIN', $qb->expr()->quot($qb->expr()->prod(new Field('lat'), new Func('PI')), 180))), $qb->expr()->prod($qb->expr()->prod(new Func('COS', $qb->expr()->quot($qb->expr()->prod($lat, new Func('PI')), 180)), new Func('COS', $qb->expr()->quot($qb->expr()->prod(new Field('lat'), new Func('PI')), 180))), new Func('COS', $qb->expr()->quot($qb->expr()->prod($qb->expr()->diff($long, new Field('lng')), new Func('PI')), 180))))), 180), new Func('PI')), 60), 1.1515), null, 'distance'))->from(Tbl::get('TBL_TREE'), 'tree')->leftJoin(Tbl::get('TBL_ZIP_CODES'), 'zips', $qb->expr()->equal(new Field('gps_id', 'zips'), new Field('id', 'tree')))->where($qb->expr()->between(new Field('lat'), $qb->expr()->diff($lat, $bounding_distance), $qb->expr()->sum($lat, $bounding_distance)))->andWhere($qb->expr()->between(new Field('lng'), $qb->expr()->diff($long, $bounding_distance), $qb->expr()->sum($long, $bounding_distance)))->andWhere($qb->expr()->in(new Field('type_id'), array(30, 35, 40)))->having($qb->expr()->less(new Field('distance'), $distance))->orderBy(new Field('distance'), "ASC");
if (!empty($node["id"])) {
$qb->andWhere($qb->expr()->notEqual(new Field('id'), $node["id"]));
}
$this->query->exec($qb->getSQL());
if ($this->query->countRecords() > 0) {
return $this->query->fetchRecords();
}
return false;
}
/**
* Reimplementing findBy features… with extra things.
*
* * key => array('<=', $value)
* * key => array('<', $value)
* * key => array('>=', $value)
* * key => array('>', $value)
* * key => array('BETWEEN', $value, $value)
* * key => array('LIKE', $value)
* * key => array('NOT IN', $array)
* * key => 'NOT NULL'
*
* You even can filter with node fields, examples:
*
* * `node.published => true`
* * `node.nodeName => 'page1'`
*
* @param array $criteria
* @param QueryBuilder $qb
* @param boolean $joinedNode
* @param boolean $joinedNodeType
*/
protected function filterByCriteria(&$criteria, &$qb, &$joinedNode = false, &$joinedNodeType = false)
{
/*
* Reimplementing findBy features…
*/
foreach ($criteria as $key => $value) {
if ($key == "tags" || $key == "tagExclusive") {
continue;
}
/*
* compute prefix for
* filtering node relation fields
*/
$prefix = 'ns.';
// Dots are forbidden in field definitions
$baseKey = str_replace('.', '_', $key);
if (false !== strpos($key, 'node.nodeType.')) {
if (!$joinedNode) {
$qb->innerJoin('ns.node', 'n');
$joinedNode = true;
}
if (!$joinedNodeType) {
$qb->innerJoin('n.nodeType', 'nt');
$joinedNodeType = true;
}
$prefix = 'nt.';
$key = str_replace('node.nodeType.', '', $key);
}
if (false !== strpos($key, 'node.')) {
if (!$joinedNode) {
$qb->innerJoin('ns.node', 'n');
$joinedNode = true;
}
$prefix = 'n.';
$key = str_replace('node.', '', $key);
}
$qb->andWhere($this->buildComparison($value, $prefix, $key, $baseKey, $qb));
}
}
public function whereCurrentYear(QueryBuilder $qb)
{
$qb->andWhere('a.date BETWEEN :start AND :end')->setParameter('start', new \Datetime(date('Y') . '-01-01'))->setParameter('end', new \Datetime(date('Y') . '-12-31'));
}
/**
* @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();
}
/**
* Update DB Config
* @param ConfigDB $oldDBCOnfig
* @param ConfigDB $newDBConfig
* @throws InvalidArgumentException
*/
public static function updateConfigValue(ConfigDB $oldDBCOnfig, ConfigDB $newDBConfig)
{
if (empty($oldDBCOnfig) || empty($newDBConfig)) {
throw new InvalidArgumentException("new or old DB config object is empty");
}
if (!isset($oldDBCOnfig->location) or empty($oldDBCOnfig->location)) {
throw new InvalidArgumentException("odl Location of config should be non empty array");
}
if (!isset($newDBConfig->location) or empty($newDBConfig->location)) {
throw new InvalidArgumentException("New Location of config should be non empty array");
}
if (!isset($oldDBCOnfig->name) or empty($oldDBCOnfig->name)) {
throw new InvalidArgumentException("Old Name of config should be specified");
}
if (!isset($newDBConfig->name) or empty($newDBConfig->name)) {
throw new InvalidArgumentException("New Name of config should be specified");
}
if (!isset($newDBConfig->value)) {
throw new InvalidArgumentException("Value of new config should be specified");
}
$odlHostLangid = null;
if (!empty($oldDBCOnfig->host) && !empty($oldDBCOnfig->language)) {
$odlHostLangid = HostLanguageManager::getHostLanguageId($oldDBCOnfig->host, $oldDBCOnfig->language);
}
$newHostLangId = null;
if (!empty($newDBConfig->host) && !empty($newDBConfig->language)) {
$newHostLangId = HostLanguageManager::getHostLanguageId($newDBConfig->host, $newDBConfig->language);
}
$sql = MySqlDbManager::getQueryObject();
$qb = new QueryBuilder();
$qb->update(Tbl::get("TBL_CONFIGS"))->set(new Field("location"), implode(":", $newDBConfig->location))->set(new Field("name"), $newDBConfig->name)->set(new Field("value"), $newDBConfig->value)->where($qb->expr()->equal(new Field("location"), implode(":", $oldDBCOnfig->location)))->andWhere($qb->expr()->equal(new Field("name"), $oldDBCOnfig->name));
if ($newHostLangId !== null) {
$qb->set(new Field("host_lang_id"), $newHostLangId);
} else {
$qb->set(new Field("host_lang_id"), new Literal("null"));
}
if ($odlHostLangid !== null) {
$qb->andWhere($qb->expr()->equal(new Field("host_lang_id"), $odlHostLangid));
} else {
$qb->andWhere($qb->expr()->isNull(new Field("host_lang_id")));
}
$sql->exec($qb->getSQL());
}
/**
* Reimplementing findBy features… with extra things
*
* * key => array('<=', $value)
* * key => array('<', $value)
* * key => array('>=', $value)
* * key => array('>', $value)
* * key => array('BETWEEN', $value, $value)
* * key => array('LIKE', $value)
* * key => array('NOT IN', $array)
* * key => 'NOT NULL'
*
* You can filter with translations relation, examples:
*
* * `translation => $object`
* * `translation.locale => 'fr_FR'`
*
* @param array $criteria
* @param QueryBuilder $qb
*/
protected function filterByCriteria(&$criteria, &$qb)
{
/*
* Reimplementing findBy features…
*/
foreach ($criteria as $key => $value) {
if ($key == "folders" || $key == "folderExclusive") {
continue;
}
/*
* compute prefix for
* filtering node, and sources relation fields
*/
$prefix = 'd.';
// Dots are forbidden in field definitions
$baseKey = str_replace('.', '_', $key);
/*
* Search in translation fields
*/
if (false !== strpos($key, 'translation.')) {
$prefix = 't.';
$key = str_replace('translation.', '', $key);
}
/*
* Search in translation fields
*/
if (false !== strpos($key, 'documentTranslations.')) {
$prefix = 'dt.';
$key = str_replace('documentTranslations.', '', $key);
}
if ($key == 'translation') {
$prefix = 'dt.';
}
$qb->andWhere($this->buildComparison($value, $prefix, $key, $baseKey, $qb));
}
}
/**
* Direct bind one single parameter without preparation.
*
* @param string $key
* @param mixed $value
* @param QueryBuilder $qb
* @param string $alias
*
* @return QueryBuilder
*/
protected function singleDirectComparison($key, &$value, &$qb, $alias)
{
if (is_object($value) && $value instanceof PersistableInterface) {
$res = $qb->expr()->eq($alias . '.' . $key, $value->getId());
} elseif (is_array($value)) {
/*
* array
*
* ['<=', $value]
* ['<', $value]
* ['>=', $value]
* ['>', $value]
* ['BETWEEN', $value, $value]
* ['LIKE', $value]
* in [$value, $value]
*/
if (count($value) > 1) {
switch ($value[0]) {
case '!=':
# neq
$res = $qb->expr()->neq($alias . '.' . $key, $value[1]);
break;
case '<=':
# lte
$res = $qb->expr()->lte($alias . '.' . $key, $value[1]);
break;
case '<':
# lt
$res = $qb->expr()->lt($alias . '.' . $key, $value[1]);
break;
case '>=':
# gte
$res = $qb->expr()->gte($alias . '.' . $key, $value[1]);
break;
case '>':
# gt
$res = $qb->expr()->gt($alias . '.' . $key, $value[1]);
break;
case 'BETWEEN':
$res = $qb->expr()->between($alias . '.' . $key, $value[1], $value[2]);
break;
case 'LIKE':
$res = $qb->expr()->like($alias . '.' . $key, $qb->expr()->literal($value[1]));
break;
default:
$res = $qb->expr()->in($alias . '.' . $key, $value);
break;
}
} else {
$res = $qb->expr()->in($alias . '.' . $key, $value);
}
} elseif (is_array($value)) {
$res = $qb->expr()->in($alias . '.' . $key, $value);
} elseif (is_bool($value)) {
$res = $qb->expr()->eq($alias . '.' . $key, (bool) $value);
} else {
$res = $qb->expr()->eq($alias . '.' . $key, $value);
}
$qb->andWhere($res);
return $qb;
}
public function deleteAnswers($profileId)
{
if (empty($profileId)) {
throw new InvalidArgumentException("Given Profile Id is empty");
}
$qb = new QueryBuilder();
$qb->delete(Tbl::get('TBL_PROFILE_SAVE'))->where($qb->expr()->equal(new Field('user_id'), $this->userId));
if (is_array($profileId)) {
$qb->andWhere($qb->expr()->in(new Field('profile_id'), $profileId));
} else {
$qb->andWhere($qb->expr()->equal(new Field('profile_id'), $profileId));
}
$this->query->exec($qb->getSQL());
}
/**
* Check if record exists
*
* @param array $pageInfo
* @param Language $lang
* @param Host $host
* @param string $module
* @param string $page
* @return bool
*/
private static function exists($lang_id = null, $host_id = null, $module = null, $page = null, $cacheMinutes = null)
{
$sql = MySqlDbManager::getQueryObject();
$qb = new QueryBuilder();
$qb->select(new Field('id'))->from(Tbl::get('TBL_PAGE_INFO', 'PageInfo'));
if ($lang_id === null) {
$qb->andWhere($qb->expr()->isNull(new Field('lang_id')));
} else {
$qb->andWhere($qb->expr()->equal(new Field('lang_id'), $lang_id));
}
if ($host_id === null) {
$qb->andWhere($qb->expr()->isNull(new Field('host_id')));
} else {
$qb->andWhere($qb->expr()->equal(new Field('host_id'), $host_id));
}
if ($module === null) {
$qb->andWhere($qb->expr()->isNull(new Field('module')));
} else {
$qb->andWhere($qb->expr()->equal(new Field('module'), $module));
}
if ($page === null) {
$qb->andWhere($qb->expr()->isNull(new Field('page')));
} else {
$qb->andWhere($qb->expr()->equal(new Field('page'), $page));
}
$sql->exec($qb->getSQL(), $cacheMinutes);
if ($sql->countRecords()) {
return $sql->fetchField("id");
}
return false;
}
public function changeConversationHasAttachmentStatus($uuid, $status, $userId = null)
{
if (empty($uuid) or !is_numeric($uuid)) {
throw new InvalidIntegerArgumentException("\$uuid have to be non zero integer.");
}
if (!is_numeric($status) or !in_array($status, $this->getConstsArray("STATUS_HAS_ATTACHMENT"))) {
throw new InvalidIntegerArgumentException("Invalid \$status specified");
}
if ($userId !== null and (empty($userId) or !is_numeric($userId))) {
throw new InvalidIntegerArgumentException("\$userId have to be non zero integer.");
}
$qb = new QueryBuilder();
$qb->update(Tbl::get('TBL_CONVERSATIONS'))->set(new Field('has_attachment'), $status)->where($qb->expr()->equal(new Field('uuid'), $uuid));
if ($userId !== null and !empty($userId) and is_numeric($userId)) {
$qb->andWhere($qb->expr()->equal(new Field('user_id'), $userId));
}
$this->query->exec($qb->getSQL());
$hookParams = array('type' => 'hasAttach', 'uuid' => $uuid, 'hasAttach' => $status);
HookManager::callHook("ConversationUpdate", $hookParams);
}
