/**
* {@inheritdoc}
*/
public function searchDestinationUser(CallEventInterface $ce)
{
/** @var Call $call */
$call = $ce->getCall();
$qb = $this->em->createQueryBuilder();
$query = $qb->select('u')->from('UserBundle:User', 'u')->leftJoin('u.groups', 'g')->leftJoin('u.contacts', 'c')->where('g.account = :account')->setParameter('account', $call->getAccount())->andWhere('c.sip LIKE :sip')->setParameter('sip', '%' . $ce->getDstNumber() . '%')->setMaxResults(1)->getQuery();
return $query->getOneOrNullResult();
}
/**
* Cleanup old sessions
* @link http://php.net/manual/en/sessionhandlerinterface.gc.php
* @param int $maxLifetime <p>
* Sessions that have not updated for
* the last maxLifetime seconds will be removed.
* </p>
* @return bool <p>
* The return value (usually TRUE on success, FALSE on failure).
* Note this value is returned internally to PHP for processing.
* </p>
* @since 5.4.0
*/
public function gc($maxLifetime)
{
$qb = $this->entityManager->createQueryBuilder();
$qb->delete(get_class($this->sessionDataClass), 's');
$qb->where($qb->expr()->lt('s.lastHit', time() - $maxLifetime));
$qb->getQuery()->execute();
return true;
}
public function findAllWithCategories()
{
# Doctrine dokumentace: http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/query-builder.html#high-level-api-methods
$queryBuilder = $this->entityManager->createQueryBuilder();
$products = $queryBuilder->select('p.id, p.name, pc.name as categoryName')->from(Product::class, 'p')->join('p.categories', 'pc')->getQuery()->getResult(AbstractQuery::HYDRATE_ARRAY);
// šetří výkon
dump($products);
die;
}
public function __invoke(User $user)
{
$qb = $this->em->createQueryBuilder();
$qb2 = $this->em->createQueryBuilder();
$ids = $qb2->select('IDENTITY(u.group)')->from(User::class, 'u')->where('u.login = :login')->setParameter('login', $user->getLogin())->getQuery()->getArrayResult();
return $qb->select('ug')->from(UserGroup::class, 'ug')->where($qb->expr()->in('ug.id', array_map(function ($el) {
return current($el);
}, $ids)))->orderBy('ug.name')->getQuery()->getResult();
}
public function __construct(EntityManagerInterface $entityManager, $entityName = null, Query $query = null)
{
$this->entityManager = $entityManager;
$this->entityName = $entityName;
if (is_null($query) && is_string($entityName) && class_exists($entityName)) {
$query = $this->entityManager->createQueryBuilder()->select('o')->from($this->entityName, 'o')->getQuery();
}
$this->query = $query;
}
/**
* @param FormBuilderInterface $builder
* @param array $options
*/
public function buildForm(FormBuilderInterface $builder, array $options)
{
$types = [Notice::DEFAULT_TYPE];
// add user-defined types
$result = $this->em->createQueryBuilder()->select('n.type')->from('AnimeDbAppBundle:Notice', 'n')->where('n.type IS NOT NULL')->groupBy('n.type')->getQuery()->getResult();
foreach ($result as $row) {
$types[] = $row['type'];
}
$builder->add('status', 'choice', ['choices' => [Notice::STATUS_CREATED => 'New', Notice::STATUS_SHOWN => 'Shown', Notice::STATUS_CLOSED => 'Closed'], 'required' => false])->add('type', 'choice', ['choices' => $this->getNormalLabels($types), 'required' => false])->setMethod('GET');
}
/**
* Method used to count leads for a specified day
* @param $forms Array of forms
* @param $minDate DateTime object
* @return mixed
*/
protected function getLeadsCountForForms($forms, $minDate)
{
foreach ($forms as $form) {
$formIds[] = $form->getId();
}
// formated date time
$formatedMinDate = $minDate->format('Y-m-d');
// Count leads for the specified day
$querybuilder = $this->entity_manager->createQueryBuilder();
$querybuilder->select('count(l)')->from('TellawLeadsFactoryBundle:Leads', 'l')->where('l.form IN (:formIds)')->andWhere('l.createdAt BETWEEN :minDate AND :maxDate')->setParameter('formIds', $formIds)->setParameter('minDate', $formatedMinDate . " 00:00:00")->setParameter('maxDate', $formatedMinDate . " 23:59:59");
$querybuilder = $this->excludeInternalLeads($querybuilder);
return $querybuilder->getQuery()->getSingleScalarResult();
}
/**
* {@inheritdoc}
*
* @return FamilyInterface[]
*/
public function findBySearch($search = null, array $options = [])
{
$qb = $this->entityManager->createQueryBuilder()->select('f')->from($this->entityName, 'f');
if (null !== $search && '' !== $search) {
$qb->where('f.code like :search')->setParameter('search', '%' . $search . '%');
if (isset($options['locale'])) {
$qb->leftJoin('f.translations', 'ft');
$qb->orWhere('ft.label like :search AND ft.locale = :locale');
$qb->setParameter('search', '%' . $search . '%');
$qb->setParameter('locale', $options['locale']);
}
}
$qb = $this->applyQueryOptions($qb, $options);
return $qb->getQuery()->getResult();
}
/**
* Count the number of objects in a collection with the given values.
*
* @param string $collection
* @param string $column
* @param array $values
* @param array $extra
*
* @return int
*/
public function getMultiCount($collection, $column, array $values, array $extra = [])
{
$builder = $this->em->createQueryBuilder();
$builder->select('count(e)')->from($collection, 'e');
$builder->where($builder->expr()->in(":{$column}", ":{$column}"));
foreach ($extra as $key => $extraValue) {
$builder->andWhere("e.{$key} = :{$key}");
}
$query = $builder->getQuery();
$query->setParameter($column, $values);
foreach ($extra as $key => $extraValue) {
$query->setParameter($key, $extraValue);
}
return $query->presence();
}
/**
* @param string $search
* @param array $options
*
* @return QueryBuilder
*/
protected function findBySearchQb($search, array $options)
{
//TODO: refactor on master because this is exactly the same that FamilySearchableRepository
//TODO: and should be put in Akeneo\Bundle\StorageUtilsBundle\Doctrine\ORM\Repository\SearchableRepository
$qb = $this->entityManager->createQueryBuilder()->select('a')->from($this->entityName, 'a');
$options = $this->resolveOptions($options);
if (null !== $search) {
$qb->leftJoin('a.translations', 'at');
$qb->where('a.code like :search')->setParameter('search', '%' . $search . '%');
if (null !== ($localeCode = $options['locale'])) {
$qb->orWhere('at.label like :search AND at.locale like :locale');
$qb->setParameter('search', '%' . $search . '%');
$qb->setParameter('locale', $localeCode);
}
}
if (!empty($options['identifiers'])) {
$qb->andWhere('a.code in (:codes)');
$qb->setParameter('codes', $options['identifiers']);
}
if (!empty($options['excluded_identifiers'])) {
$qb->andWhere('a.code not in (:codes)');
$qb->setParameter('codes', $options['excluded_identifiers']);
}
if (null !== $options['limit']) {
$qb->setMaxResults($options['limit']);
if (null !== $options['page']) {
$qb->setFirstResult($options['limit'] * ($options['page'] - 1));
}
}
//TODO: this part is specific to attributes
if ($options['exclude_unique']) {
$qb->andWhere('a.unique = 0');
}
if (null !== $options['types']) {
$qb->andWhere('a.attributeType in (:types)');
$qb->setParameter('types', $options['types']);
}
$qb->leftJoin('a.group', 'ag');
if (!empty($options['attribute_groups'])) {
$qb->andWhere('ag.code in (:groups)');
$qb->setParameter('groups', $options['attribute_groups']);
}
$qb->orderBy('ag.code');
$qb->orderBy('ag.sortOrder');
$qb->groupBy('a.id');
return $qb;
}
public function search()
{
//$finder = $this->container->get('fos_elastica.finder.search.articles');
$bool = new Query\Bool();
$multiMatch = new Query\MultiMatch();
$multiMatch->setFields(['subjects', 'title', 'keywords', 'subtitle', 'citations.raw', 'journal.title', 'journal.subtitle']);
$multiMatch->setType('phrase_prefix');
$multiMatch->setQuery($this->getParam('term'));
$bool->addMust($multiMatch);
if ($this->filter) {
foreach ($this->filter as $key => $filter) {
$filterObj = new Query\Match();
$this->applyFilter($filterObj, $key, $filter);
$bool->addMust($filterObj);
}
}
$missing = new Filter\Missing("issue");
$not = new Filter\BoolNot($missing);
$notQ = new Query\Filtered();
$notQ->setFilter($not);
$bool->addMust($notQ);
$query = new Query();
$query->setQuery($bool);
$query->setFrom($this->getPage() * $this->getLimit());
$query->setSize($this->getLimit());
$aggregation = new Terms('journals');
$aggregation->setField('journal.id');
$aggregation->setOrder('_count', 'desc');
$qb = $this->em->createQueryBuilder();
$qb->select('count(r.id)')->from('OjsJournalBundle:Journal', 'r')->where($qb->expr()->eq('r.status', 3));
$aggregation->setSize($qb->getQuery()->getSingleScalarResult());
$query->addAggregation($aggregation);
$aggregation = new Terms('authors');
$aggregation->setField('articleAuthors.author.id');
$aggregation->setOrder('_count', 'desc');
$qb = $this->em->createQueryBuilder();
$qb->select('count(r.id)')->from('OjsJournalBundle:Author', 'r');
$aggregation->setSize($qb->getQuery()->getSingleScalarResult());
$query->addAggregation($aggregation);
$elasticaAdapter = new ElasticaAdapter($this->index, $query);
$pagerFanta = new Pagerfanta($elasticaAdapter);
$pagerFanta->setMaxPerPage($this->getLimit());
$pagerFanta->setCurrentPage($this->getPage());
/** @var ResultSet $search */
$search = $pagerFanta->getCurrentPageResults();
$result = $search->getResults();
//$search->getResults();
$this->pager = $pagerFanta;
$transformer = new ElasticaToModelTransformer($this->registry, 'OjsJournalBundle:Article');
$transformer->setPropertyAccessor($this->propertyAccessor);
$this->result = $transformer->transform($result);
$this->setCount($pagerFanta->getNbResults());
$this->addAggregation('journal', $this->transform($search->getAggregation('journals')['buckets'], 'OjsJournalBundle:Journal'));
$this->addAggregation('author', $this->transform($search->getAggregation('authors')['buckets'], 'OjsJournalBundle:Author'));
return $this;
}
/**
* @param string $class
* @param mixed $fit
* @return \Doctrine\ORM\QueryBuilder
*/
public function build($class, $fit = null)
{
$alias = uniqid("A");
/** @var \Doctrine\Orm\QueryBuilder $qb */
$qb = $this->em->createQueryBuilder();
if ($fit instanceof SelectFitInterface && $fit->getSelect($alias)) {
$qb->select($fit->getSelect($alias));
} else {
$qb->select($alias);
}
$qb->from($class, $alias);
if ($fit instanceof WhereWithJoinFitInterface) {
$join = $fit->getJoin($alias);
if ($join) {
$qb->join($alias, $join->getJoin(), $join->getAlias(), $join->getCondition());
}
$where = $fit->getWhere($alias);
if ($where) {
$qb->andWhere($where);
}
}
if ($fit instanceof WhereFitInterface) {
$where = $fit->getWhere($alias);
if ($where) {
$qb->andWhere($where);
$qb->setParameters($fit->getParameters());
}
}
if ($fit instanceof OrderFitInterface) {
$order = $fit->getOrder($alias);
if ($order) {
$qb->orderBy($order);
}
}
if ($fit instanceof LimitFitInterface) {
$limit = $fit->getLimit();
if ($limit) {
$qb->setMaxResults($limit);
}
}
return $qb;
}
/**
* @param array $selectedIds
* @param string $value
*
* @return int
*/
protected function finishBatch(array &$selectedIds, $value)
{
$qBuilder = $this->entityManager->createQueryBuilder();
$entitiesCount = $qBuilder->update($this->entityName, 'e')->set('e.' . $this->fieldName, ':value')->where($qBuilder->expr()->in('e.' . $this->identifierName, $selectedIds))->getQuery()->setParameter('value', $value)->execute();
$this->entityManager->flush();
if ($this->entityManager->getConnection()->getTransactionNestingLevel() == 1) {
$this->entityManager->clear();
}
$selectedIds = [];
return $entitiesCount;
}
/**
* @param string $entityName
* @param int $userId
*
* @return \DateTime|null
*/
protected function getMaxDateFromEntityByUserId($entityName, $userId)
{
$max = null;
$query = $this->entityManager->createQueryBuilder();
$query->select('MAX(e.date) AS max_date')->from($entityName, 'e')->where('e.user = :user_id')->setParameter('user_id', $userId)->setMaxResults(1);
$result = $query->getQuery()->getResult();
if ($result && isset($result[0]['max_date'])) {
$max = DateUtil::dateTimeFromMySqlFormat($result[0]['max_date']);
}
return $max;
}
/**
* {@inheritdoc}
*
* @return FamilyInterface[]
*/
public function findBySearch($search = null, array $options = [])
{
$qb = $this->entityManager->createQueryBuilder()->select('f')->from($this->entityName, 'f');
$qb->leftJoin('f.translations', 'ft');
if (null !== $search && '' !== $search) {
$qb->where('f.code like :search')->setParameter('search', "%{$search}%");
$qb->orWhere('ft.label like :search')->setParameter('search', "%{$search}%");
}
if (isset($options['identifiers']) && is_array($options['identifiers']) && !empty($options['identifiers'])) {
$qb->andWhere('f.code in (:codes)');
$qb->setParameter('codes', $options['identifiers']);
}
if (isset($options['limit'])) {
$qb->setMaxResults((int) $options['limit']);
if (isset($options['page'])) {
$qb->setFirstResult((int) $options['limit'] * ((int) $options['page'] - 1));
}
}
return $qb->getQuery()->getResult();
}
/**
* Build iterator
*
* NOTE : The submission fields are added as export fields as well ...
*/
public function buildIterator()
{
$qb = $this->em->createQueryBuilder();
$qb->select('fs')->from('KunstmaanFormBundle:FormSubmission', 'fs')->innerJoin('fs.node', 'n', 'WITH', 'fs.node = n.id')->andWhere('n.id = :node')->andWhere('fs.lang = :lang')->setParameter('node', $this->nodeTranslation->getNode()->getId())->setParameter('lang', $this->nodeTranslation->getLang())->addOrderBy('fs.created', 'DESC');
$iterableResult = $qb->getQuery()->iterate();
$isHeaderWritten = false;
$collection = new ArrayCollection();
foreach ($iterableResult as $row) {
/* @var FormSubmission $submission */
$submission = $row[0];
// Write row data
$data = array('id' => $submission->getId(), 'date' => $submission->getCreated()->format('d/m/Y H:i:s'), 'language' => $submission->getLang());
foreach ($submission->getFields() as $field) {
$header = $this->translator->trans($field->getLabel());
if (!$isHeaderWritten) {
$this->addExportField($header, $header);
}
$data[$header] = $field->__toString();
}
$isHeaderWritten = true;
$collection->add(array($data));
}
$this->iterator = $collection->getIterator();
}
public function __invoke()
{
$qb = $this->em->createQueryBuilder();
return $qb->select('t.title', 'COUNT(p.id) as countOfPosts')->from(Tag::class, 't')->leftJoin('t.posts', 'p')->groupBy('t.id')->orderBy('t.title')->getQuery()->getResult();
}
public function __invoke()
{
$qb = $this->em->createQueryBuilder();
return $qb->select('p', 't')->from(Post::class, 'p')->leftJoin('p.tags', 't')->orderBy('p.publishedAt')->getQuery()->getResult();
}
/**
* {@inheritdoc}
*/
public function createQueryBuilder()
{
return $this->wrapped->createQueryBuilder();
}
/**
* Get a query builder for the migration table.
* @return QueryBuilder
*/
protected function query()
{
return $this->em->createQueryBuilder()->select('o')->from(Migration::class, 'o');
}
/**
* Queries for existing users.
*
* @param string[] $suggestions
*
* @return string[]
*/
private function queryExistingUsersBySuggestedNames(array $suggestions)
{
$qb = $this->entityManager->createQueryBuilder();
$qb->select('user.username')->from('Account:User', 'user')->where($qb->expr()->in('user.username', ':nameList'))->setParameter(':nameList', $suggestions);
return array_column($qb->getQuery()->getResult(), 'username');
}
public function __invoke()
{
$qb = $this->em->createQueryBuilder();
return $qb->select('ug.name', 'COUNT(u.id) as countOfUsers')->from(UserGroup::class, 'ug')->leftJoin('ug.users', 'u')->groupBy('ug.id')->orderBy('ug.name')->getQuery()->getResult();
}
/**
* @param int $limit
* @return array
*/
public function getLastUsers($limit = 5)
{
$qb = $this->em->createQueryBuilder();
$qb->select('u')->from('NFQUserBundle:User', 'u')->orderBy('u.id', 'DESC')->setMaxResults($limit);
return $qb->getQuery()->getArrayResult();
}
/**
* @return array
*/
public function getList()
{
$qb = $this->em->createQueryBuilder();
$qb->select('e')->from($this->options->getEntityClass(), 'e')->where('e.deleted = 0')->orderBy('e.id', 'ASC');
return $qb->getQuery()->getResult();
}
/**
* {@inheritdoc}
*/
public function find($identifier)
{
$queryBuilder = $this->entityManager->createQueryBuilder();
$queryBuilder->select('s')->from('AppMainBundle:Submission', 's')->where('s.identifier = :identifier')->setParameter('identifier', $identifier);
return $queryBuilder->getQuery()->getOneOrNullResult();
}
/**
* Returns an array with all instances of a recurring event. If an event was detached it's not part of the series' instances.
*
* todo: Refactor - combining eluceo/ical and sabre/vobject this way seems very expensive.
* @param Event $event
* @param \DateTime|null $dateFrom
* @param \DateTime|null $dateTo
*
* @return Event[]
*
* @throws \Exception
*/
public function getInstances(Event $event, \DateTime $dateFrom = null, \DateTime $dateTo = null, $findOnlyOne = false)
{
$eventInstances = array();
// this is necessary for using nthOccurrence, otherwise the maximum recurrence entities will result in an error
// todo: Refactor - solve recurrence count for nthOccurrence
VObject\Settings::$maxRecurrences = -1;
if (!$event->getRecurrenceRule()) {
return array($event);
}
$dateFrom = $dateFrom ? $dateFrom : $event->getDtStart();
if (!$dateFrom) {
return $eventInstances;
}
if (!$dateTo) {
//default to one year if no end is set
$dateTo = clone $event->getDtStart();
$dateTo->add(new \DateInterval('P10Y'));
}
if (!$dateFrom || !$dateTo) {
throw new \Exception('Trying to get instances of a recurring event without dateFrom and/or dateTo being set.');
}
//create the calendar
$vCalendar = new Calendar($event->getUniqueId());
if (self::isValidDateTime($event->getDtStart()) && self::isValidDateTime($event->getDtEnd())) {
$vCalendar->addComponent($event);
}
//get edited events: depending on $includeEditedEvents to mark these events as deleted or to replace them by their edited event
$editedEventsByTimestamp = array();
$qb = $this->em->createQueryBuilder();
$qb->select('e')->from('Xima\\ICalBundle\\Entity\\Component\\Event', 'e')->where('e.uniqueId = :uniqueId')->andWhere($qb->expr()->isNotNull('e.recurrenceId'))->setParameter('uniqueId', $event->getUniqueId());
$editedEvents = $qb->getQuery()->getResult();
foreach ($editedEvents as $editedEvent) {
/* @var $editedEvent \Xima\ICalBundle\Entity\Component\Event */
if (self::isValidDateTime($editedEvent->getDtStart()) && self::isValidDateTime($editedEvent->getDtEnd()) && self::isValidDateTime($editedEvent->getRecurrenceId()->getDatetime())) {
$editedEventsByTimestamp[$editedEvent->getDtStart()->getTimestamp()] = $editedEvent;
$vCalendar->addComponent($editedEvent);
}
}
//render the calendar and parse it to get all recurrences of the event
$vCalendarExpandedData = $vCalendar->render();
$vCalendarExpanded = VObject\Reader::read($vCalendarExpandedData);
/* @var $vCalendarExpanded \Sabre\VObject\Component\VCalendar */
$vCalendarExpanded = $vCalendarExpanded->expand($dateFrom, $dateTo);
foreach ($vCalendarExpanded->getComponents() as $instanceComp) {
/* @var $instanceComp \Sabre\VObject\Component\VEvent */
// It's basically the same event, but with the new calculated dates and times...
// @todo: refactor so that dtStart is set when dateFrom gets set and so on...
$dtStart = new \DateTime();
$dtStart->setTimestamp($instanceComp->DTSTART->getDateTime()->getTimestamp());
$dtEnd = new \DateTime();
$dtEnd->setTimestamp($instanceComp->DTEND->getDateTime()->getTimestamp());
$eventInstance = clone $event;
/* @var $eventInstance Event */
$eventInstance->setDtStart($dtStart);
$eventInstance->setDateFrom($dtStart);
$eventInstance->setTimeFrom($dtStart);
$eventInstance->setDtEnd($dtEnd);
$eventInstance->setDateTo($dtEnd);
$eventInstance->setTimeTo($dtEnd);
if ($findOnlyOne) {
return array($eventInstance);
}
if (isset($editedEventsByTimestamp[$instanceComp->DTSTART->getDateTime()->getTimestamp()])) {
// if the instance was detached, it's not part of the series' instances
continue;
} else {
$eventInstances[] = $eventInstance;
}
}
return $eventInstances;
}
public function __invoke(Tag $tag)
{
$qb = $this->em->createQueryBuilder();
return $qb->select('p', 't')->from(Post::class, 'p')->leftJoin('p.tags', 't')->where(':tag MEMBER OF p.tags')->setParameter('tag', $tag)->orderBy('p.publishedAt')->getQuery()->getResult();
}
/**
* @return \Doctrine\ORM\QueryBuilder
*/
protected function makeDelete()
{
return $this->em->createQueryBuilder()->delete(PasswordReminder::class, 'o');
}
/**
* {@inheritdoc}
*/
public function createQueryBuilder($alias, $indexBy = null)
{
return $this->entityManager->createQueryBuilder()->select($alias)->from($this->mediaEntityName, $alias, $indexBy);
}
/**
* Get all Entities
*
* @param Includes $includes
* @param Sorting $sort
* @param Filters $filters
* @return array
*/
public function all(Includes $includes, Sorting $sort, Filters $filters)
{
$qb = $this->em->createQueryBuilder();
$qb->select('e')->from($this->class, 'e');
$qb = $this->processSorting($qb, $sort);
$qb = $this->processIncludes($qb, $includes);
if (isset($this->filters)) {
$qb = $this->filters->process($qb, $filters);
}
return new ArrayCollection($qb->getQuery()->getResult());
}
