/** * 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); }