/**
* 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;
}
/**
* @param QueryBuilder $q
* @param $filter
* @return array
*/
protected function addCatchAllWhereClause(&$q, $filter)
{
$unique = $this->generateRandomParameterName();
//ensure that the string has a unique parameter identifier
$string = $filter->strict ? $filter->string : "%{$filter->string}%";
$expr = $q->expr()->orX($q->expr()->like('f.label', ':' . $unique), $q->expr()->like('f.alias', ':' . $unique));
if ($filter->not) {
$expr = $q->expr()->not($expr);
}
return array($expr, array("{$unique}" => $string));
}
public static function getHostByName($hostName, $tryToAutoCreateHost = true, $cacheMinutes = null)
{
$sql = MySqlDbManager::getQueryObject();
$qb = new QueryBuilder();
$qb->select(new Field('*'))->from(Tbl::get('TBL_HOSTS', 'Host'))->where($qb->expr()->equal(new Field('host'), $hostName));
$sql->exec($qb->getSQL(), $cacheMinutes);
if ($sql->countRecords()) {
$data = $sql->fetchRecord();
$host = new Host();
Host::setData($data, $host);
return $host;
} else {
$originalHostName = $hostName;
$explodedArray = explode(".", $hostName, 2);
$parentHostName = array_pop($explodedArray);
$wildcardHostName = "*." . $parentHostName;
$qb = new QueryBuilder();
$qb->select(new Field('*'))->from(Tbl::get('TBL_HOSTS', 'Host'))->where($qb->expr()->equal(new Field('host'), $wildcardHostName));
$sql->exec($qb->getSQL(), $cacheMinutes);
if ($sql->countRecords()) {
$data = $sql->fetchRecord();
$data['host'] = $originalHostName;
$data['wildcardOf'] = $parentHostName;
$host = new Host();
Host::setData($data, $host);
return $host;
} elseif ($tryToAutoCreateHost) {
$host = new Host();
$host->host = $hostName;
self::addHost($host);
return self::getHostByName($hostName, false, $cacheMinutes);
}
throw new RuntimeException("There is no such host (" . $originalHostName . ")");
}
}
/**
* 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 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;
}
public function getEventsLastId()
{
$qb = new QueryBuilder();
$qb->select($qb->expr()->max(new Field('id'), 'maxId'))->from(Tbl::get('TBL_COMET_EVENTS'));
$maxId = $this->query->exec($qb->getSQL())->fetchField('maxId');
if (empty($maxId)) {
$maxId = 0;
}
return $maxId;
}
/**
* 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;
}
/**
* @param QueryBuilder $q
* @param $filter
*
* @return array
*/
protected function addSearchCommandWhereClause(&$q, $filter)
{
$command = $field = $filter->command;
$unique = $this->generateRandomParameterName();
$expr = false;
switch ($command) {
case $this->translator->trans('mautic.core.searchcommand.ispublished'):
$expr = $q->expr()->eq('c.isPublished', ":{$unique}");
$string = true;
break;
case $this->translator->trans('mautic.core.searchcommand.isunpublished'):
$expr = $q->expr()->eq('c.isPublished', ":{$unique}");
$string = false;
break;
}
if ($expr && $filter->not) {
$expr = $q->expr()->not($expr);
}
return [$expr, ["{$unique}" => $string]];
}
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 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();
}
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();
}
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();
}
/**
* @param QueryBuilder $q
* @param $filter
* @return array
*/
protected function addSearchCommandWhereClause(&$q, $filter)
{
$command = $filter->command;
$unique = $this->generateRandomParameterName();
$returnParameter = true;
//returning a parameter that is not used will lead to a Doctrine error
$expr = false;
switch ($command) {
case $this->translator->trans('mautic.core.searchcommand.ispublished'):
$expr = $q->expr()->eq("e.isPublished", ":{$unique}");
$forceParameters = array($unique => true);
break;
case $this->translator->trans('mautic.core.searchcommand.isunpublished'):
$expr = $q->expr()->eq("e.isPublished", ":{$unique}");
$forceParameters = array($unique => true);
break;
case $this->translator->trans('mautic.core.searchcommand.isuncategorized'):
$expr = $q->expr()->orX($q->expr()->isNull('e.category'), $q->expr()->eq('e.category', $q->expr()->literal('')));
$returnParameter = false;
break;
case $this->translator->trans('mautic.core.searchcommand.ismine'):
$expr = $q->expr()->eq("IDENTITY(e.createdBy)", $this->currentUser->getId());
$returnParameter = false;
break;
case $this->translator->trans('mautic.core.searchcommand.category'):
$expr = $q->expr()->like('e.alias', ":{$unique}");
$filter->strict = true;
break;
case $this->translator->trans('mautic.core.searchcommand.lang'):
$langUnique = $this->generateRandomParameterName();
$langValue = $filter->string . "_%";
$forceParameters = array($langUnique => $langValue, $unique => $filter->string);
$expr = $q->expr()->orX($q->expr()->eq('e.language', ":{$unique}"), $q->expr()->like('e.language', ":{$langUnique}"));
break;
}
if ($expr && $filter->not) {
$expr = $q->expr()->not($expr);
}
if (!empty($forceParameters)) {
$parameters = $forceParameters;
} elseif (!$returnParameter) {
$parameters = array();
} else {
$string = $filter->strict ? $filter->string : "%{$filter->string}%";
$parameters = array("{$unique}" => $string);
}
return array($expr, $parameters);
}
/**
* Get language by short name
*
* @param string $shortName
* @throws EmptyArgumentException, InvalidArgumentException
* @return Language object
*/
public static function getLanguage($shortName, $cacheMinutes = null)
{
if (empty($shortName)) {
throw new EmptyArgumentException("Empty shortName argument.");
}
$sql = MySqlDbManager::getQueryObject();
$qb = new QueryBuilder();
$qb->select(new Field('*'))->from(Tbl::get('TBL_LANGUAGES'))->where($qb->expr()->equal(new Field('name'), $shortName));
$sql->exec($qb->getSQL(), $cacheMinutes);
if ($sql->countRecords()) {
$l = new Language();
$data = $sql->fetchRecord();
static::setData($data, $l);
return $l;
}
throw new InvalidArgumentException("There is no language with such short name (" . $shortName . ")");
}
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'));
}
}
}
public function hookInvalidLoginAttempt($params)
{
if ($this->config->AuxConfig->loginBruteForceProtectionEnabled) {
if (isset($_SERVER['REMOTE_ADDR'])) {
$sql = MySqlDbManager::getQueryObject();
$qb = new QueryBuilder();
$sql->exec($qb->select(new Field('count'))->from(Tbl::get('TBL_SECURITY_INVALID_LOGINS_LOG', 'RequestLimiter'))->where($qb->expr()->equal(new Field('ip'), $_SERVER['REMOTE_ADDR']))->getSQL());
$failedAuthCount = $sql->fetchField('count');
$newFailedAuthCount = $failedAuthCount + 1;
if ($newFailedAuthCount >= $this->config->AuxConfig->failedLoginLimit) {
Reg::get(ConfigManager::getConfig("Security", "RequestLimiter")->Objects->RequestLimiter)->blockIP();
$qb = new QueryBuilder();
$sql->exec($qb->delete(Tbl::get('TBL_SECURITY_INVALID_LOGINS_LOG', 'RequestLimiter'))->where($qb->expr()->equal(new Field('ip'), $_SERVER['REMOTE_ADDR']))->getSQL());
throw new RequestLimiterTooManyAuthTriesException("Too many unsucessful authorization tries.");
}
$qb = new QueryBuilder();
$sql->exec($qb->insert(Tbl::get('TBL_SECURITY_INVALID_LOGINS_LOG', 'RequestLimiter'))->values(array('ip' => $_SERVER['REMOTE_ADDR']))->onDuplicateKeyUpdate()->set(new Field('count'), $qb->expr()->sum(new Field('count'), 1))->getSQL());
}
}
}
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());
}
/**
* Add rule to conditions
* @param QueryBuilder $qb
* @param array $rule
* @param string $alias
*/
private static function addRule($qb, $rule, $alias = null)
{
foreach ($rule as $key => $value) {
$field = $key;
foreach ($value as $_cond => $_match) {
$condition = $_cond;
$match = $_match;
}
}
if (!is_numeric($match) && !is_array($match)) {
if (!preg_match('|\'.*\'|', $match)) {
$match = "'{$match}'";
}
} else {
if (is_array($match) && $condition == 'between') {
foreach ($match as $index => $value) {
if (!preg_match('|\'.*\'|', $value)) {
$match[$index] = "'{$value}'";
}
}
}
}
$className = self::alias($alias);
switch ($condition) {
case 'is':
if (strcasecmp('NULL', $match) == 0) {
return $qb->expr()->isNull("{$className}.{$field}");
} else {
return $qb->expr()->isNotNull("{$className}.{$field}");
}
case 'like':
return $qb->expr()->like("{$className}.{$field}", $match);
case 'neq':
return $qb->expr()->neq("{$className}.{$field}", $match);
case 'nlike':
return $qb->expr()->like("{$className}.{$field} NOT", $match);
case 'in':
return $qb->expr()->in("{$className}.{$field}", implode(",", $match));
case 'notin':
return $qb->expr()->in("{$className}.{$field} NOT", implode(",", $match));
case 'gt':
return $qb->expr()->gt("{$className}.{$field}", $match);
case 'gte':
return $qb->expr()->gte("{$className}.{$field}", $match);
case 'lt':
return $qb->expr()->lt("{$className}.{$field}", $match);
case 'lte':
return $qb->expr()->lte("{$className}.{$field}", $match);
case 'between':
return $qb->expr()->between("{$className}.{$field}", reset($match), end($match));
default:
return $qb->expr()->eq("{$className}.{$field}", $match);
}
}
/**
* 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());
}
/**
* Adds the command where clause to the QueryBuilder
*
* @param QueryBuilder $q
* @param $filter
*
* @return array
*/
protected function addSearchCommandWhereClause(&$q, $filter)
{
$command = $filter->command;
$string = $filter->string;
$unique = $this->generateRandomParameterName();
$returnParameter = true;
//returning a parameter that is not used will lead to a Doctrine error
$expr = false;
$parameters = array();
//DBAL QueryBuilder does not have an expr()->not() function; boo!!
if ($filter->not) {
$xFunc = "orX";
$xSubFunc = "andX";
$eqFunc = "neq";
$nullFunc = "isNotNull";
$likeFunc = "notLike";
} else {
$xFunc = "andX";
$xSubFunc = "orX";
$eqFunc = "eq";
$nullFunc = "isNull";
$likeFunc = "like";
}
switch ($command) {
case $this->translator->trans('mautic.lead.lead.searchcommand.isanonymous'):
$expr = $q->expr()->{$xFunc}($q->expr()->{$xSubFunc}($q->expr()->{$eqFunc}("l.firstname", $q->expr()->literal('')), $q->expr()->{$nullFunc}("l.firstname")), $q->expr()->{$xSubFunc}($q->expr()->{$eqFunc}("l.lastname", $q->expr()->literal('')), $q->expr()->{$nullFunc}("l.lastname")), $q->expr()->{$xSubFunc}($q->expr()->{$eqFunc}("l.company", $q->expr()->literal('')), $q->expr()->{$nullFunc}("l.company")), $q->expr()->{$xSubFunc}($q->expr()->{$eqFunc}("l.email", $q->expr()->literal('')), $q->expr()->{$nullFunc}("l.email")));
if (!empty($this->availableSocialFields)) {
foreach ($this->availableSocialFields as $field) {
$expr->add($q->expr()->{$xSubFunc}($q->expr()->{$eqFunc}("l.{$field}", $q->expr()->literal('')), $q->expr()->{$nullFunc}("l.{$field}")));
}
}
$returnParameter = false;
break;
case $this->translator->trans('mautic.core.searchcommand.ismine'):
$expr = $q->expr()->{$eqFunc}("l.owner_id", $this->currentUser->getId());
$returnParameter = false;
break;
case $this->translator->trans('mautic.lead.lead.searchcommand.isunowned'):
$expr = $q->expr()->{$xFunc}($q->expr()->{$eqFunc}("l.owner_id", 0), $q->expr()->{$nullFunc}("l.owner_id"));
$returnParameter = false;
break;
case $this->translator->trans('mautic.lead.lead.searchcommand.owner'):
$expr = $q->expr()->{$xFunc}($q->expr()->{$likeFunc}('LOWER(u.firstName)', ':' . $unique), $q->expr()->{$likeFunc}('LOWER(u.lastName)', ':' . $unique));
break;
case $this->translator->trans('mautic.core.searchcommand.name'):
$expr = $q->expr()->{$xFunc}($q->expr()->{$likeFunc}('LOWER(l.firstname)', ":{$unique}"), $q->expr()->{$likeFunc}('LOWER(l.lastname)', ":{$unique}"));
break;
case $this->translator->trans('mautic.lead.lead.searchcommand.list'):
//obtain the list details
$list = $this->_em->getRepository("MauticLeadBundle:LeadList")->findOneByAlias($string);
if (!empty($list)) {
$listId = (int) $list->getId();
} else {
//force a bad expression as the list doesn't exist
$listId = 0;
}
$sq = $this->_em->getConnection()->createQueryBuilder()->select('ll.lead_id')->from(MAUTIC_TABLE_PREFIX . 'lead_lists_leads', 'll');
$sq->where($sq->expr()->andX($sq->expr()->eq('ll.leadlist_id', $listId), $sq->expr()->orX($sq->expr()->isNull('ll.manually_removed'), $sq->expr()->eq('ll.manually_removed', ':false'))))->setParameter('false', false, 'boolean');
$results = $sq->execute()->fetchAll();
$leadIds = array();
foreach ($results as $row) {
$leadIds[] = $row['lead_id'];
}
if (!count($leadIds)) {
$leadIds[] = 0;
}
$expr = $q->expr()->in('l.id', $leadIds);
break;
case $this->translator->trans('mautic.core.searchcommand.ip'):
// search by IP
$sq = $this->_em->getConnection()->createQueryBuilder();
$sq->select('lip.lead_id')->from(MAUTIC_TABLE_PREFIX . 'lead_ips_xref', 'lip')->join('lip', MAUTIC_TABLE_PREFIX . 'ip_addresses', 'ip', 'lip.ip_id = ip.id')->where($sq->expr()->{$likeFunc}('ip.ip_address', ":{$unique}"))->setParameter($unique, $string);
$results = $sq->execute()->fetchAll();
$leadIds = array();
foreach ($results as $row) {
$leadIds[] = $row['lead_id'];
}
if (!count($leadIds)) {
$leadIds[] = 0;
}
$expr = $q->expr()->in('l.id', $leadIds);
break;
case $this->translator->trans('mautic.lead.lead.searchcommand.tag'):
// search by tag
$sq = $this->_em->getConnection()->createQueryBuilder();
$sq->select('x.lead_id')->from(MAUTIC_TABLE_PREFIX . 'lead_tags_xref', 'x')->join('x', MAUTIC_TABLE_PREFIX . 'lead_tags', 't', 'x.tag_id = t.id')->where($sq->expr()->{$eqFunc}('t.tag', ":{$unique}"))->setParameter($unique, $string);
$results = $sq->execute()->fetchAll();
$leadIds = array();
foreach ($results as $row) {
$leadIds[] = $row['lead_id'];
}
if (!count($leadIds)) {
$leadIds[] = 0;
}
$expr = $q->expr()->in('l.id', $leadIds);
break;
case $this->translator->trans('mautic.core.searchcommand.email'):
$expr = $q->expr()->{$likeFunc}('LOWER(l.email)', ":{$unique}");
break;
case $this->translator->trans('mautic.lead.lead.searchcommand.company'):
$expr = $q->expr()->{$likeFunc}('LOWER(l.company)', ":{$unique}");
break;
default:
if (in_array($command, $this->availableSearchFields)) {
$expr = $q->expr()->{$likeFunc}('LOWER(l.' . $command . ')', ":{$unique}");
}
break;
}
$string = $filter->strict ? $filter->string : "%{$filter->string}%";
if ($command != $this->translator->trans('mautic.lead.lead.searchcommand.list')) {
$parameters[$unique] = $string;
}
return array($expr, $returnParameter ? $parameters : array());
}
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;
}
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;
}
/**
* 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;
}
/**
* Count a value in a column
*
* @param QueryBuilder $query
*
* @return array
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function countValue($query, $column, $value)
{
$query->select('count(' . $column . ') as quantity')->from(MAUTIC_TABLE_PREFIX . 'leads', 'l')->leftJoin('l', MAUTIC_TABLE_PREFIX . 'lead_points_change_log', 'lp', 'lp.lead_id = l.id')->andwhere($query->expr()->eq($column, ':value'))->setParameter('value', $value);
$result = $query->execute()->fetch();
return $result['quantity'];
}
/**
* 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();
}
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);
}
