segment_subscriber_id = {$subscribersTable}.id"
);
}
} catch (InvalidStateException $exception) {
return 0;
}
$statement = $this->executeQuery($queryBuilder);
/** @var string $result */
$result = $statement->fetchOne();
return (int)$result;
}
/**
* @param DynamicSegmentFilterData[] $filters
* @return int
* @throws InvalidStateException
*/
public function getDynamicSubscribersCount(array $filters): int {
$segment = new SegmentEntity('temporary segment', SegmentEntity::TYPE_DYNAMIC, '');
foreach ($filters as $filter) {
$segment->addDynamicFilter(new DynamicSegmentFilterEntity($segment, $filter));
}
$queryBuilder = $this->createDynamicStatisticsQueryBuilder();
$queryBuilder = $this->filterSubscribersInDynamicSegment($queryBuilder, $segment, null);
$statement = $this->executeQuery($queryBuilder);
/** @var array{all:string} $result */
$result = $statement->fetch();
return (int)$result['all'];
}
private function createCountQueryBuilder(): QueryBuilder {
$subscribersTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName();
return $this->entityManager
->getConnection()
->createQueryBuilder()
->select("count(DISTINCT $subscribersTable.id)")
->from($subscribersTable);
}
private function createDynamicStatisticsQueryBuilder(): QueryBuilder {
$subscribersTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName();
return $this->entityManager
->getConnection()
->createQueryBuilder()
->from($subscribersTable)
->addSelect("IFNULL(SUM(
CASE WHEN $subscribersTable.deleted_at IS NULL
THEN 1 ELSE 0 END
), 0) as `all`")
->addSelect("IFNULL(SUM(
CASE WHEN $subscribersTable.deleted_at IS NOT NULL
THEN 1 ELSE 0 END
), 0) as trash")
->addSelect("IFNULL(SUM(
CASE WHEN $subscribersTable.status = :status_subscribed AND $subscribersTable.deleted_at IS NULL
THEN 1 ELSE 0 END
), 0) as :status_subscribed")
->addSelect("IFNULL(SUM(
CASE WHEN $subscribersTable.status = :status_unsubscribed AND $subscribersTable.deleted_at IS NULL
THEN 1 ELSE 0 END
), 0) as :status_unsubscribed")
->addSelect("IFNULL(SUM(
CASE WHEN $subscribersTable.status = :status_inactive AND $subscribersTable.deleted_at IS NULL
THEN 1 ELSE 0 END
), 0) as :status_inactive")
->addSelect("IFNULL(SUM(
CASE WHEN $subscribersTable.status = :status_unconfirmed AND $subscribersTable.deleted_at IS NULL
THEN 1 ELSE 0 END
), 0) as :status_unconfirmed")
->addSelect("IFNULL(SUM(
CASE WHEN $subscribersTable.status = :status_bounced AND $subscribersTable.deleted_at IS NULL
THEN 1 ELSE 0 END
), 0) as :status_bounced")
->setParameter('status_subscribed', SubscriberEntity::STATUS_SUBSCRIBED)
->setParameter('status_unsubscribed', SubscriberEntity::STATUS_UNSUBSCRIBED)
->setParameter('status_inactive', SubscriberEntity::STATUS_INACTIVE)
->setParameter('status_unconfirmed', SubscriberEntity::STATUS_UNCONFIRMED)
->setParameter('status_bounced', SubscriberEntity::STATUS_BOUNCED);
}
private function createStaticStatisticsQueryBuilder(SegmentEntity $segment): QueryBuilder {
$subscriberSegmentTable = $this->entityManager->getClassMetadata(SubscriberSegmentEntity::class)->getTableName();
$subscribersTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName();
return $this->entityManager
->getConnection()
->createQueryBuilder()
->from($subscriberSegmentTable, 'subscriber_segment')
->where('subscriber_segment.segment_id = :segment_id')
->setParameter('segment_id', $segment->getId())
->join('subscriber_segment', $subscribersTable, 'subscribers', 'subscribers.id = subscriber_segment.subscriber_id')
->addSelect('IFNULL(SUM(
CASE WHEN subscribers.deleted_at IS NULL
THEN 1 ELSE 0 END
), 0) as `all`')
->addSelect('IFNULL(SUM(
CASE WHEN subscribers.deleted_at IS NOT NULL
THEN 1 ELSE 0 END
), 0) as trash')
->addSelect('IFNULL(SUM(
CASE WHEN subscribers.status = :status_subscribed AND subscriber_segment.status = :status_subscribed AND subscribers.deleted_at IS NULL
THEN 1 ELSE 0 END
), 0) as :status_subscribed')
->addSelect('IFNULL(SUM(
CASE WHEN (subscribers.status = :status_unsubscribed OR subscriber_segment.status = :status_unsubscribed) AND subscribers.deleted_at IS NULL
THEN 1 ELSE 0 END
), 0) as :status_unsubscribed')
->addSelect('IFNULL(SUM(
CASE WHEN subscribers.status = :status_inactive AND subscriber_segment.status != :status_unsubscribed AND subscribers.deleted_at IS NULL
THEN 1 ELSE 0 END
), 0) as :status_inactive')
->addSelect('IFNULL(SUM(
CASE WHEN subscribers.status = :status_unconfirmed AND subscriber_segment.status != :status_unsubscribed AND subscribers.deleted_at IS NULL
THEN 1 ELSE 0 END
), 0) as :status_unconfirmed')
->addSelect('IFNULL(SUM(
CASE WHEN subscribers.status = :status_bounced AND subscriber_segment.status != :status_unsubscribed AND subscribers.deleted_at IS NULL
THEN 1 ELSE 0 END
), 0) as :status_bounced')
->setParameter('status_subscribed', SubscriberEntity::STATUS_SUBSCRIBED)
->setParameter('status_unsubscribed', SubscriberEntity::STATUS_UNSUBSCRIBED)
->setParameter('status_inactive', SubscriberEntity::STATUS_INACTIVE)
->setParameter('status_unconfirmed', SubscriberEntity::STATUS_UNCONFIRMED)
->setParameter('status_bounced', SubscriberEntity::STATUS_BOUNCED);
}
private function createStaticGlobalStatusStatisticsQueryBuilder(SegmentEntity $segment): QueryBuilder {
$subscriberSegmentTable = $this->entityManager->getClassMetadata(SubscriberSegmentEntity::class)->getTableName();
$subscribersTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName();
return $this->entityManager
->getConnection()
->createQueryBuilder()
->from($subscriberSegmentTable, 'subscriber_segment')
->where('subscriber_segment.segment_id = :segment_id')
->setParameter('segment_id', $segment->getId())
->join('subscriber_segment', $subscribersTable, 'subscribers', 'subscribers.id = subscriber_segment.subscriber_id')
->addSelect('IFNULL(SUM(
CASE WHEN subscribers.deleted_at IS NULL
THEN 1 ELSE 0 END
), 0) as `all`')
->addSelect('IFNULL(SUM(
CASE WHEN subscribers.deleted_at IS NOT NULL
THEN 1 ELSE 0 END
), 0) as trash')
->addSelect('IFNULL(SUM(
CASE WHEN subscribers.status = :status_subscribed AND subscribers.deleted_at IS NULL
THEN 1 ELSE 0 END
), 0) as :status_subscribed')
->addSelect('IFNULL(SUM(
CASE WHEN subscribers.status = :status_unsubscribed AND subscribers.deleted_at IS NULL
THEN 1 ELSE 0 END
), 0) as :status_unsubscribed')
->addSelect('IFNULL(SUM(
CASE WHEN subscribers.status = :status_inactive AND subscribers.deleted_at IS NULL
THEN 1 ELSE 0 END
), 0) as :status_inactive')
->addSelect('IFNULL(SUM(
CASE WHEN subscribers.status = :status_unconfirmed AND subscribers.deleted_at IS NULL
THEN 1 ELSE 0 END
), 0) as :status_unconfirmed')
->addSelect('IFNULL(SUM(
CASE WHEN subscribers.status = :status_bounced AND subscribers.deleted_at IS NULL
THEN 1 ELSE 0 END
), 0) as :status_bounced')
->setParameter('status_subscribed', SubscriberEntity::STATUS_SUBSCRIBED)
->setParameter('status_unsubscribed', SubscriberEntity::STATUS_UNSUBSCRIBED)
->setParameter('status_inactive', SubscriberEntity::STATUS_INACTIVE)
->setParameter('status_unconfirmed', SubscriberEntity::STATUS_UNCONFIRMED)
->setParameter('status_bounced', SubscriberEntity::STATUS_BOUNCED);
}
public function getSubscribersWithoutSegmentCount(): int {
$queryBuilder = $this->entityManager->createQueryBuilder();
$queryBuilder
->select('COUNT(DISTINCT s) AS subscribersCount')
->from(SubscriberEntity::class, 's');
$this->addConstraintsForSubscribersWithoutSegment($queryBuilder);
return (int)$queryBuilder->getQuery()->getSingleScalarResult();
}
public function getSubscribersWithoutSegmentStatisticsCount(): array {
$subscribersTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName();
$queryBuilder = $this->entityManager
->getConnection()
->createQueryBuilder();
$queryBuilder
->addSelect('IFNULL(SUM(
CASE WHEN s.deleted_at IS NULL
THEN 1 ELSE 0 END
), 0) as `all`')
->addSelect('IFNULL(SUM(
CASE WHEN s.deleted_at IS NOT NULL
THEN 1 ELSE 0 END
), 0) as trash')
->addSelect('IFNULL(SUM(
CASE WHEN s.status = :status_subscribed AND s.deleted_at IS NULL
THEN 1 ELSE 0 END
), 0) as :status_subscribed')
->addSelect('IFNULL(SUM(
CASE WHEN s.status = :status_unsubscribed AND s.deleted_at IS NULL
THEN 1 ELSE 0 END
), 0) as :status_unsubscribed')
->addSelect('IFNULL(SUM(
CASE WHEN s.status = :status_inactive AND s.deleted_at IS NULL
THEN 1 ELSE 0 END
), 0) as :status_inactive')
->addSelect('IFNULL(SUM(
CASE WHEN s.status = :status_unconfirmed AND s.deleted_at IS NULL
THEN 1 ELSE 0 END
), 0) as :status_unconfirmed')
->addSelect('IFNULL(SUM(
CASE WHEN s.status = :status_bounced AND s.deleted_at IS NULL
THEN 1 ELSE 0 END
), 0) as :status_bounced')
->from($subscribersTable, 's')
->setParameter('status_subscribed', SubscriberEntity::STATUS_SUBSCRIBED)
->setParameter('status_unsubscribed', SubscriberEntity::STATUS_UNSUBSCRIBED)
->setParameter('status_inactive', SubscriberEntity::STATUS_INACTIVE)
->setParameter('status_unconfirmed', SubscriberEntity::STATUS_UNCONFIRMED)
->setParameter('status_bounced', SubscriberEntity::STATUS_BOUNCED);
$this->addConstraintsForSubscribersWithoutSegmentToDBAL($queryBuilder);
$statement = $this->executeQuery($queryBuilder);
$result = $statement->fetch();
return $result;
}
public function addConstraintsForSubscribersWithoutSegment(ORMQueryBuilder $queryBuilder): void {
$deletedSegmentsQueryBuilder = $this->entityManager->createQueryBuilder();
$deletedSegmentsQueryBuilder->select('sg.id')
->from(SegmentEntity::class, 'sg')
->where($deletedSegmentsQueryBuilder->expr()->isNotNull('sg.deletedAt'));
$queryBuilder
->leftJoin(
's.subscriberSegments',
'ssg',
Join::WITH,
(string)$queryBuilder->expr()->andX(
$queryBuilder->expr()->eq('ssg.subscriber', 's.id'),
$queryBuilder->expr()->eq('ssg.status', ':statusSubscribed'),
$queryBuilder->expr()->notIn('ssg.segment', $deletedSegmentsQueryBuilder->getDQL())
)
)
->andWhere('ssg.id IS NULL')
->setParameter('statusSubscribed', SubscriberEntity::STATUS_SUBSCRIBED);
}
public function addConstraintsForSubscribersWithoutSegmentToDBAL(QueryBuilder $queryBuilder): void {
$deletedSegmentsQueryBuilder = $this->entityManager->createQueryBuilder();
$subscribersSegmentTable = $this->entityManager->getClassMetadata(SubscriberSegmentEntity::class)->getTableName();
$deletedSegmentsQueryBuilder->select('sg.id')
->from(SegmentEntity::class, 'sg')
->where($deletedSegmentsQueryBuilder->expr()->isNotNull('sg.deletedAt'));
$queryBuilder
->leftJoin(
's',
$subscribersSegmentTable,
'ssg',
(string)$queryBuilder->expr()->and(
$queryBuilder->expr()->eq('ssg.subscriber_id', 's.id'),
$queryBuilder->expr()->eq('ssg.status', ':statusSubscribed'),
$queryBuilder->expr()->notIn('ssg.segment_id', $deletedSegmentsQueryBuilder->getQuery()->getSQL())
)
)
->andWhere('ssg.id IS NULL')
->setParameter('statusSubscribed', SubscriberEntity::STATUS_SUBSCRIBED);
}
private function loadSubscriberIdsInSegment(int $segmentId, array $candidateIds = null): array {
$segment = $this->getSegment($segmentId);
$subscribersTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName();
$queryBuilder = $this->entityManager
->getConnection()
->createQueryBuilder()
->select("DISTINCT $subscribersTable.id")
->from($subscribersTable);
if ($segment->isStatic()) {
$queryBuilder = $this->filterSubscribersInStaticSegment($queryBuilder, $segment, SubscriberEntity::STATUS_SUBSCRIBED);
} else {
$queryBuilder = $this->filterSubscribersInDynamicSegment($queryBuilder, $segment, SubscriberEntity::STATUS_SUBSCRIBED);
}
if ($candidateIds) {
$queryBuilder->andWhere("$subscribersTable.id IN (:candidateIds)")
->setParameter('candidateIds', $candidateIds, Connection::PARAM_STR_ARRAY);
}
$statement = $this->executeQuery($queryBuilder);
$result = $statement->fetchAll();
return array_column($result, 'id');
}
private function filterSubscribersInStaticSegment(
QueryBuilder $queryBuilder,
SegmentEntity $segment,
string $status = null
): QueryBuilder {
$subscribersSegmentsTable = $this->entityManager->getClassMetadata(SubscriberSegmentEntity::class)->getTableName();
$subscribersTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName();
$parameterName = "segment_{$segment->getId()}"; // When we use this method more times the parameter name has to be unique
$queryBuilder = $queryBuilder->join(
$subscribersTable,
$subscribersSegmentsTable,
'subsegment',
"subsegment.subscriber_id = $subscribersTable.id AND subsegment.segment_id = :$parameterName"
)->andWhere("$subscribersTable.deleted_at IS NULL")
->setParameter($parameterName, $segment->getId());
if ($status) {
$queryBuilder = $queryBuilder->andWhere("$subscribersTable.status = :status")
->andWhere("subsegment.status = :status")
->setParameter('status', $status);
}
return $queryBuilder;
}
private function filterSubscribersInDynamicSegment(
QueryBuilder $queryBuilder,
SegmentEntity $segment,
string $status = null
): QueryBuilder {
$filters = [];
$dynamicFilters = $segment->getDynamicFilters();
foreach ($dynamicFilters as $dynamicFilter) {
$filters[] = $dynamicFilter->getFilterData();
}
// We don't allow dynamic segment without filers since it would return all subscribers
// For BC compatibility fetching an empty result
if (count($filters) === 0) {
return $queryBuilder->andWhere('0 = 1');
} elseif ($segment instanceof SegmentEntity) {
try {
$queryBuilder = $this->filterHandler->apply($queryBuilder, $segment);
} catch (InvalidFilterException $e) {
// If a segment has an invalid filter, we should simply consider it empty instead of throwing
// an unhandled error. Unhandled errors here can break many admin pages.
$queryBuilder->andWhere('0 = 1');
}
}
$subscribersTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName();
$queryBuilder = $queryBuilder->andWhere("$subscribersTable.deleted_at IS NULL");
if ($status) {
$queryBuilder = $queryBuilder->andWhere("$subscribersTable.status = :status")
->setParameter('status', $status);
}
return $queryBuilder;
}
private function getSegment(int $id): SegmentEntity {
$segment = $this->entityManager->find(SegmentEntity::class, $id);
if (!$segment instanceof SegmentEntity) {
throw new NotFoundException('Segment not found');
}
return $segment;
}
private function executeQuery(QueryBuilder $queryBuilder): Result {
$result = $queryBuilder->execute();
// Execute for select always returns statement but PHP Stan doesn't know that :(
if (!$result instanceof Result) {
throw new InvalidStateException('Invalid query.');
}
return $result;
}
public function getSubscribersGlobalStatusStatisticsCount(SegmentEntity $segment): array {
if ($segment->isStatic()) {
$queryBuilder = $this->createStaticGlobalStatusStatisticsQueryBuilder($segment);
} else {
$queryBuilder = $this->createDynamicStatisticsQueryBuilder();
$this->filterSubscribersInDynamicSegment($queryBuilder, $segment);
}
$statement = $this->executeQuery($queryBuilder);
return $statement->fetch();
}
public function getSubscribersStatisticsCount(SegmentEntity $segment): array {
if ($segment->isStatic()) {
$queryBuilder = $this->createStaticStatisticsQueryBuilder($segment);
} else {
$queryBuilder = $this->createDynamicStatisticsQueryBuilder();
$this->filterSubscribersInDynamicSegment($queryBuilder, $segment);
}
$statement = $this->executeQuery($queryBuilder);
return $statement->fetch();
}
}
Fatal error: Uncaught Error: Class "MailPoet\Segments\SegmentSubscribersRepository" not found in /htdocs/wp-content/plugins/mailpoet/generated/FreeCachedContainer.php:4758
Stack trace:
#0 /htdocs/wp-content/plugins/mailpoet/generated/FreeCachedContainer.php(4798): MailPoetGenerated\FreeCachedContainer->getSegmentSubscribersRepositoryService()
#1 /htdocs/wp-content/plugins/mailpoet/generated/FreeCachedContainer.php(5268): MailPoetGenerated\FreeCachedContainer->getSubscribersFinderService()
#2 /htdocs/wp-content/plugins/mailpoet/generated/FreeCachedContainer.php(2612): MailPoetGenerated\FreeCachedContainer->getSubscriberSubscribeControllerService()
#3 /htdocs/wp-content/plugins/mailpoet/generated/FreeCachedContainer.php(2640): MailPoetGenerated\FreeCachedContainer->getHooks2Service()
#4 /htdocs/wp-content/plugins/mailpoet/vendor-prefixed/symfony/dependency-injection/Container.php(122): MailPoetGenerated\FreeCachedContainer->getInitializerService()
#5 /htdocs/wp-content/plugins/mailpoet/vendor-prefixed/symfony/dependency-injection/Container.php(110): MailPoetVendor\Symfony\Component\DependencyInjection\Container->make('MailPoet\\Config...', 1)
#6 /htdocs/wp-content/plugins/mailpoet/lib/DI/ContainerWrapper.php(39): MailPoetVendor\Symfony\Component\DependencyInjection\Container->get('MailPoet\\Config...')
#7 /htdocs/wp-content/plugins/mailpoet/mailpoet_initializer.php(89): MailPoet\DI\ContainerWrapper->get('MailPoet\\Config...')
#8 /htdocs/wp-content/plugins/mailpoet/mailpoet.php(194): require_once('/htdocs/wp-cont...')
#9 /htdocs/wp-settings.php(526): include_once('/htdocs/wp-cont...')
#10 /htdocs/wp-config.php(85): require_once('/htdocs/wp-sett...')
#11 /htdocs/wp-load.php(50): require_once('/htdocs/wp-conf...')
#12 /htdocs/wp-blog-header.php(13): require_once('/htdocs/wp-load...')
#13 /htdocs/index.php(17): require('/htdocs/wp-blog...')
#14 {main}
thrown in /htdocs/wp-content/plugins/mailpoet/generated/FreeCachedContainer.php on line 4758