NewslettersRepository.php — MailPoet – Newsletters, Email Marketing, and Automation 5.34.2

mailpoet / lib / Newsletter / NewslettersRepository.php

mailpoet / lib / Newsletter Last commit date

Editor 2 months ago Embed 1 month ago Links 2 months ago Listing 9 months ago Options 2 years ago Preview 1 month ago Renderer 5 days ago RestApi 1 week ago Scheduler 5 days ago Segment 1 year ago Sending 1 week ago Sharing 1 month ago Shortcodes 3 days ago Statistics 1 week ago ViewInBrowser 1 month ago ApiDataSanitizer.php 3 years ago AutomatedLatestContent.php 2 months ago AutomaticEmailsRepository.php 3 years ago BlockPostQuery.php 2 months ago BulkActionController.php 1 month ago BulkActionException.php 1 month ago DynamicProducts.php 11 months ago NewsletterCoupon.php 2 months ago NewsletterDeleteController.php 2 years ago NewsletterHtmlSanitizer.php 2 years ago NewsletterPostsRepository.php 2 years ago NewsletterResendController.php 5 days ago NewsletterSaveController.php 1 month ago NewsletterValidator.php 1 year ago NewslettersRepository.php 2 weeks ago StatusController.php 1 month ago Url.php 2 months ago index.php 3 years ago

NewslettersRepository.php

908 lines

1	<?php // phpcs:ignore SlevomatCodingStandard.TypeHints.DeclareStrictTypes.DeclareStrictTypesMissing
2
3	namespace MailPoet\Newsletter;
4
5	if (!defined('ABSPATH')) exit;
6
7
8	use DateTimeInterface;
9	use MailPoet\AutomaticEmails\WooCommerce\Events\AbandonedCart;
10	use MailPoet\AutomaticEmails\WooCommerce\Events\FirstPurchase;
11	use MailPoet\AutomaticEmails\WooCommerce\Events\PurchasedInCategory;
12	use MailPoet\AutomaticEmails\WooCommerce\Events\PurchasedProduct;
13	use MailPoet\Doctrine\Repository;
14	use MailPoet\Entities\NewsletterEntity;
15	use MailPoet\Entities\NewsletterOptionEntity;
16	use MailPoet\Entities\NewsletterOptionFieldEntity;
17	use MailPoet\Entities\NewsletterSegmentEntity;
18	use MailPoet\Entities\ScheduledTaskEntity;
19	use MailPoet\Entities\SendingQueueEntity;
20	use MailPoet\Logging\LoggerFactory;
21	use MailPoet\Newsletter\Sending\NewsletterReplayMetadata;
22	use MailPoet\Util\Helpers;
23	use MailPoetVendor\Carbon\Carbon;
24	use MailPoetVendor\Doctrine\DBAL\ArrayParameterType;
25	use MailPoetVendor\Doctrine\DBAL\ParameterType;
26	use MailPoetVendor\Doctrine\ORM\EntityManager;
27	use MailPoetVendor\Doctrine\ORM\Query\Expr\Join;
28
29	/**
30	* @extends Repository<NewsletterEntity>
31	*/
32	class NewslettersRepository extends Repository {
33	private LoggerFactory $loggerFactory;
34
35	public function __construct(
36	EntityManager $entityManager
37	) {
38	parent::__construct($entityManager);
39	$this->loggerFactory = LoggerFactory::getInstance();
40	}
41
42	protected function getEntityClassName() {
43	return NewsletterEntity::class;
44	}
45
46	/**
47	* @param string[] $types
48	* @return NewsletterEntity[]
49	*/
50	public function findActiveByTypes($types) {
51	return $this->entityManager
52	->createQueryBuilder()
53	->select('n')
54	->from(NewsletterEntity::class, 'n')
55	->where('n.status = :status')
56	->setParameter('status', NewsletterEntity::STATUS_ACTIVE)
57	->andWhere('n.deletedAt is null')
58	->andWhere('n.type IN (:types)')
59	->setParameter('types', $types)
60	->orderBy('n.subject')
61	->getQuery()
62	->getResult();
63	}
64
65	public function getCountForStatusAndTypes(string $status, array $types): int {
66	return intval($this->entityManager
67	->createQueryBuilder()
68	->select('COUNT(n.id)')
69	->from(NewsletterEntity::class, 'n')
70	->where('n.status = :status')
71	->andWhere('n.deletedAt is null')
72	->andWhere('n.type IN (:types)')
73	->setParameter('status', $status)
74	->setParameter('types', $types)
75	->getQuery()
76	->getSingleScalarResult());
77	}
78
79	public function getCountOfActiveAutomaticEmailsForEvent(string $event): int {
80	return intval($this->entityManager->createQueryBuilder()
81	->select('COUNT(n.id)')
82	->from(NewsletterEntity::class, 'n')
83	->where('n.status = :status')
84	->andWhere('n.deletedAt IS NULL')
85	->andWhere('n.type IN (:types)')
86	->join('n.options', 'o', Join::WITH, 'o.value = :event')
87	->join('o.optionField', 'f', Join::WITH, 'f.name = :nameEvent AND f.newsletterType IN (:types)')
88	->setParameter('status', NewsletterEntity::STATUS_ACTIVE)
89	->setParameter('nameEvent', NewsletterOptionFieldEntity::NAME_EVENT)
90	->setParameter('types', [NewsletterEntity::TYPE_AUTOMATION_TRANSACTIONAL, NewsletterEntity::TYPE_AUTOMATIC], ArrayParameterType::STRING)
91	->setParameter('event', $event)
92	->getQuery()
93	->getSingleScalarResult());
94	}
95
96	public function getCountOfEmailsWithWPPost(): int {
97	return intval($this->entityManager->createQueryBuilder()
98	->select('COUNT(n.id)')
99	->from(NewsletterEntity::class, 'n')
100	->andWhere('n.wpPost IS NOT NULL')
101	->getQuery()
102	->getSingleScalarResult());
103	}
104
105	/**
106	* @return NewsletterEntity[]
107	*/
108	public function findActiveByTypeAndGroup(string $type, ?string $group): array {
109	$qb = $this->entityManager
110	->createQueryBuilder()
111	->select('n')
112	->from(NewsletterEntity::class, 'n')
113	->where('n.status = :status')
114	->setParameter('status', NewsletterEntity::STATUS_ACTIVE)
115	->andWhere('n.deletedAt IS NULL')
116	->andWhere('n.type = :type')
117	->setParameter('type', $type);
118
119	if ($group) {
120	$qb->join('n.options', 'o', Join::WITH, 'o.value = :group')
121	->join('o.optionField', 'f', Join::WITH, 'f.name = :nameGroup AND f.newsletterType = :type')
122	->setParameter('nameGroup', NewsletterOptionFieldEntity::NAME_GROUP)
123	->setParameter('group', $group);
124	}
125
126	return $qb->getQuery()->getResult();
127	}
128
129	/**
130	* @param string[] $types
131	* @return NewsletterEntity[]
132	*/
133	public function findDraftByTypes($types) {
134	return $this->entityManager
135	->createQueryBuilder()
136	->select('n')
137	->from(NewsletterEntity::class, 'n')
138	->where('n.status = :status')
139	->setParameter('status', NewsletterEntity::STATUS_DRAFT)
140	->andWhere('n.deletedAt is null')
141	->andWhere('n.type IN (:types)')
142	->setParameter('types', $types)
143	->orderBy('n.subject')
144	->getQuery()
145	->getResult();
146	}
147
148	/**
149	* Returns the number of standard newsletter campaigns sent in the given period.
150	*/
151	public function getStandardNewsletterSentCount(DateTimeInterface $since): int {
152	return (int)$this->doctrineRepository->createQueryBuilder('n')
153	->select('COUNT(n)')
154	->join('n.queues', 'q')
155	->join('q.task', 't')
156	->andWhere('n.type = :type')
157	->andWhere('n.status = :status')
158	->andWhere('t.status = :taskStatus')
159	// Intentionally skip "Send latest newsletter" replay sends; they would skew the campaign count.
160	// We use only the date of the initial sending.
161	->andWhere('q.meta IS NULL OR q.meta NOT LIKE :latestNewsletterReplayMeta')
162	->andWhere('t.meta IS NULL OR t.meta NOT LIKE :latestNewsletterReplayMeta')
163	->andWhere('t.processedAt >= :since')
164	->setParameter('type', NewsletterEntity::TYPE_STANDARD)
165	->setParameter('status', NewsletterEntity::STATUS_SENT)
166	->setParameter('taskStatus', ScheduledTaskEntity::STATUS_COMPLETED)
167	->setParameter('latestNewsletterReplayMeta', NewsletterReplayMetadata::getMetaLikePattern())
168	->setParameter('since', $since)
169	->getQuery()
170	->getSingleScalarResult() ?: 0;
171	}
172
173	public function getGutenbergNewsletterSentCount(): int {
174	return intval($this->entityManager->createQueryBuilder()
175	->select('COUNT(n.id)')
176	->from(NewsletterEntity::class, 'n')
177	->where('n.deletedAt IS NULL')
178	->andWhere('n.wpPost IS NOT NULL')
179	->andWhere('n.status IN (:statuses)')
180	->setParameter('statuses', [NewsletterEntity::STATUS_SENT])
181	->getQuery()
182	->getSingleScalarResult());
183	}
184
185	public function getTotalGutenbergNewsletterCount() {
186	return intval($this->entityManager->createQueryBuilder()
187	->select('COUNT(n.id)')
188	->from(NewsletterEntity::class, 'n')
189	->where('n.deletedAt IS NULL')
190	->andWhere('n.wpPost IS NOT NULL')
191	->getQuery()
192	->getSingleScalarResult());
193	}
194
195	public function getAnalytics(): array {
196	// for automatic emails join 'event' newsletter option to further group the counts
197	$eventOptionId = (int)$this->entityManager->createQueryBuilder()
198	->select('nof.id')
199	->from(NewsletterOptionFieldEntity::class, 'nof')
200	->andWhere('nof.newsletterType = :eventOptionFieldType')
201	->andWhere('nof.name = :eventOptionFieldName')
202	->setParameter('eventOptionFieldType', NewsletterEntity::TYPE_AUTOMATIC)
203	->setParameter('eventOptionFieldName', 'event')
204	->getQuery()
205	->getSingleScalarResult();
206
207	$results = $this->doctrineRepository->createQueryBuilder('n')
208	->select('n.type, eventOption.value AS event, COUNT(n) AS cnt')
209	->leftJoin('n.options', 'eventOption', Join::WITH, "eventOption.optionField = :eventOptionId")
210	->andWhere('n.deletedAt IS NULL')
211	->andWhere('n.status IN (:statuses)')
212	->setParameter('eventOptionId', $eventOptionId)
213	->setParameter('statuses', [NewsletterEntity::STATUS_ACTIVE, NewsletterEntity::STATUS_SENT])
214	->groupBy('n.type, eventOption.value')
215	->getQuery()
216	->getResult();
217
218	$analyticsMap = [];
219	foreach ($results as $result) {
220	$type = $result['type'];
221	if ($type === NewsletterEntity::TYPE_AUTOMATIC) {
222	if (!isset($analyticsMap[$type]) \|\| !is_array($analyticsMap[$type])) {
223	$analyticsMap[$type] = [];
224	}
225	$analyticsMap[$type][$result['event'] ?? ''] = (int)$result['cnt'];
226	} else {
227	$analyticsMap[$type] = (int)$result['cnt'];
228	}
229	}
230
231	$automaticEmailsMap = $analyticsMap[NewsletterEntity::TYPE_AUTOMATIC] ?? [];
232	$automaticEmailsMap = is_array($automaticEmailsMap) ? $automaticEmailsMap : [];
233
234	$data = [
235	'welcome_newsletters_count' => $analyticsMap[NewsletterEntity::TYPE_WELCOME] ?? 0,
236	'notifications_count' => $analyticsMap[NewsletterEntity::TYPE_NOTIFICATION] ?? 0,
237	'automatic_emails_count' => array_sum($automaticEmailsMap),
238	'automation_emails_count' => $analyticsMap[NewsletterEntity::TYPE_AUTOMATION] ?? 0,
239	're-engagement_emails_count' => $analyticsMap[NewsletterEntity::TYPE_RE_ENGAGEMENT] ?? 0,
240	'sent_newsletters_count' => $analyticsMap[NewsletterEntity::TYPE_STANDARD] ?? 0,
241	'sent_newsletters_7_days' => $this->getStandardNewsletterSentCount(Carbon::now()->subDays(7)),
242	'sent_newsletters_3_months' => $this->getStandardNewsletterSentCount(Carbon::now()->subMonths(3)),
243	'sent_newsletters_30_days' => $this->getStandardNewsletterSentCount(Carbon::now()->subDays(30)),
244	'first_purchase_emails_count' => $automaticEmailsMap[FirstPurchase::SLUG] ?? 0,
245	'product_purchased_emails_count' => $automaticEmailsMap[PurchasedProduct::SLUG] ?? 0,
246	'product_purchased_in_category_emails_count' => $automaticEmailsMap[PurchasedInCategory::SLUG] ?? 0,
247	'abandoned_cart_emails_count' => $automaticEmailsMap[AbandonedCart::SLUG] ?? 0,
248	'total_gutenberg_newsletter_count' => $this->getTotalGutenbergNewsletterCount() ?: 0,
249	'sent_gutenberg_newsletter_count' => $this->getGutenbergNewsletterSentCount() ?: 0,
250	];
251	// Count all campaigns
252	$analyticsMap[NewsletterEntity::TYPE_AUTOMATIC] = array_sum($automaticEmailsMap);
253	// Post notification history is not a campaign, we count only the parent notification
254	unset($analyticsMap[NewsletterEntity::TYPE_NOTIFICATION_HISTORY]);
255	$data['campaigns_count'] = array_sum($analyticsMap);
256	return $data;
257	}
258
259	/**
260	* @param array $params
261	* @return NewsletterEntity[]
262	*/
263	public function getArchives(array $params = []) {
264	$types = [
265	NewsletterEntity::TYPE_STANDARD,
266	NewsletterEntity::TYPE_NOTIFICATION_HISTORY,
267	];
268
269	$queryBuilder = $this->entityManager
270	->createQueryBuilder()
271	->select('n')
272	->distinct()
273	->from(NewsletterEntity::class, 'n')
274	->innerJoin(SendingQueueEntity::class, 'sq', Join::WITH, 'sq.newsletter = n.id')
275	->innerJoin(ScheduledTaskEntity::class, 'st', Join::WITH, 'st.id = sq.task')
276	->where('n.type IN (:types)')
277	->andWhere('st.status = :statusCompleted')
278	->andWhere('n.deletedAt IS NULL')
279	->orderBy('st.processedAt', 'DESC')
280	->addOrderBy('st.id', 'ASC')
281	->setParameter('types', $types)
282	->setParameter('statusCompleted', SendingQueueEntity::STATUS_COMPLETED);
283
284	$excludeFromArchiveSubQuery = $this->entityManager
285	->createQueryBuilder()
286	->select('1')
287	->from(NewsletterOptionEntity::class, 'archiveOption')
288	->innerJoin('archiveOption.optionField', 'archiveOptionField')
289	->where('archiveOption.newsletter = n')
290	->andWhere('archiveOption.value = :excludeFromArchive')
291	->andWhere('archiveOptionField.name = :excludeFromArchiveOptionName')
292	->getDQL();
293
294	$queryBuilder
295	->andWhere($queryBuilder->expr()->orX(
296	'n.type != :standardNewsletterType',
297	$queryBuilder->expr()->not($queryBuilder->expr()->exists($excludeFromArchiveSubQuery))
298	))
299	->setParameter('standardNewsletterType', NewsletterEntity::TYPE_STANDARD)
300	->setParameter('excludeFromArchive', '1')
301	->setParameter('excludeFromArchiveOptionName', NewsletterOptionFieldEntity::NAME_EXCLUDE_FROM_ARCHIVE);
302
303	$segmentIds = $params['segmentIds'] ?? [];
304	if (!empty($segmentIds)) {
305	$queryBuilder->innerJoin(NewsletterSegmentEntity::class, 'ns', Join::WITH, 'ns.newsletter = n.id')
306	->andWhere('ns.segment IN (:segmentIds)')
307	->setParameter('segmentIds', $segmentIds);
308	}
309
310	$startDate = $params['startDate'] ?? null;
311	if ($startDate instanceof DateTimeInterface) {
312	$queryBuilder
313	->andWhere('st.processedAt >= :startDate')
314	->setParameter('startDate', $startDate);
315	}
316
317	$endDate = $params['endDate'] ?? null;
318	if ($endDate instanceof DateTimeInterface) {
319	$queryBuilder
320	->andWhere('st.processedAt <= :endDate')
321	->setParameter('endDate', $endDate);
322	}
323
324	$subjectContains = $params['subjectContains'] ?? null;
325	if (is_string($subjectContains)) {
326	$queryBuilder
327	->andWhere($queryBuilder->expr()->like('n.subject', ':subjectContains'))
328	->setParameter('subjectContains', '%' . Helpers::escapeSearch($subjectContains) . '%');
329	}
330
331	$limit = $params['limit'] ?? null;
332	if (is_int($limit) && $limit > 0) {
333	$queryBuilder->setMaxResults($limit);
334	}
335
336	return $queryBuilder->getQuery()->getResult();
337	}
338
339	public function findEmbeddableNewsletterById(int $newsletterId): ?NewsletterEntity {
340	return $this->entityManager
341	->createQueryBuilder()
342	->select('n')
343	->from(NewsletterEntity::class, 'n')
344	->where('n.id = :newsletterId')
345	->andWhere('n.status = :status')
346	->andWhere('n.deletedAt IS NULL')
347	->andWhere('n.type IN (:types)')
348	->setParameter('newsletterId', $newsletterId)
349	->setParameter('status', NewsletterEntity::STATUS_SENT)
350	->setParameter('types', [
351	NewsletterEntity::TYPE_STANDARD,
352	NewsletterEntity::TYPE_NOTIFICATION_HISTORY,
353	], ArrayParameterType::STRING)
354	->getQuery()
355	->getOneOrNullResult();
356	}
357
358	/**
359	* @return array<int, array{id: int, subject: string\|null, sentAt: \DateTimeInterface\|string\|null, type: string\|null, wpPostId: int\|null}>
360	*/
361	public function findEmbeddableNewsletterRows(string $search = '', int $limit = 20): array {
362	$queryBuilder = $this->entityManager
363	->createQueryBuilder()
364	->select('
365	n.id,
366	n.subject,
367	n.type,
368	IDENTITY(n.wpPost) AS wpPostId,
369	MAX(st.processedAt) AS sentAt
370	')
371	->from(NewsletterEntity::class, 'n')
372	->innerJoin(SendingQueueEntity::class, 'sq', Join::WITH, 'sq.newsletter = n.id')
373	->innerJoin(ScheduledTaskEntity::class, 'st', Join::WITH, 'st.id = sq.task')
374	->where('n.status = :newsletterStatus')
375	->andWhere('n.deletedAt IS NULL')
376	->andWhere('n.type IN (:types)')
377	->andWhere('st.status = :taskStatus')
378	->setParameter('newsletterStatus', NewsletterEntity::STATUS_SENT)
379	->setParameter('taskStatus', ScheduledTaskEntity::STATUS_COMPLETED)
380	->setParameter('types', [
381	NewsletterEntity::TYPE_STANDARD,
382	NewsletterEntity::TYPE_NOTIFICATION_HISTORY,
383	], ArrayParameterType::STRING)
384	->groupBy('n.id')
385	->addGroupBy('n.subject')
386	->addGroupBy('n.type')
387	->addGroupBy('n.wpPost')
388	->orderBy('sentAt', 'DESC')
389	->addOrderBy('n.id', 'DESC')
390	->setMaxResults($limit);
391
392	if ($search !== '') {
393	$queryBuilder
394	->andWhere(
395	$queryBuilder->expr()->orX(
396	$queryBuilder->expr()->like('n.subject', ':search'),
397	$queryBuilder->expr()->like('sq.newsletterRenderedSubject', ':search')
398	)
399	)
400	->setParameter('search', '%' . Helpers::escapeSearch($search) . '%');
401	}
402
403	$rows = $queryBuilder->getQuery()->getArrayResult();
404	$result = [];
405	foreach ($rows as $row) {
406	if (!is_array($row)) {
407	continue;
408	}
409
410	$subject = $row['subject'] ?? null;
411	$type = $row['type'] ?? null;
412	$sentAt = $row['sentAt'] ?? null;
413	$wpPostId = $row['wpPostId'] ?? null;
414	$id = $row['id'] ?? null;
415	if (!is_numeric($id)) {
416	continue;
417	}
418
419	$result[] = [
420	'id' => (int)$id,
421	'subject' => is_scalar($subject) ? (string)$subject : null,
422	'sentAt' => $sentAt instanceof \DateTimeInterface \|\| is_string($sentAt) ? $sentAt : null,
423	'type' => is_scalar($type) ? (string)$type : null,
424	'wpPostId' => is_numeric($wpPostId) ? (int)$wpPostId : null,
425	];
426	}
427
428	return $result;
429	}
430
431	/**
432	* @return array{newsletter: NewsletterEntity, queue: SendingQueueEntity, task: ScheduledTaskEntity}\|null
433	*/
434	public function findLatestSentStandardForSegment(int $segmentId, int $overfetchLimit = 100): ?array {
435	$newsletterTable = $this->entityManager->getClassMetadata(NewsletterEntity::class)->getTableName();
436	$queueTable = $this->entityManager->getClassMetadata(SendingQueueEntity::class)->getTableName();
437	$taskTable = $this->entityManager->getClassMetadata(ScheduledTaskEntity::class)->getTableName();
438	$newsletterSegmentTable = $this->entityManager->getClassMetadata(NewsletterSegmentEntity::class)->getTableName();
439
440	$rows = $this->entityManager->getConnection()->executeQuery(
441	"
442	SELECT n.id AS newsletter_id, sq.id AS queue_id, st.id AS task_id
443	FROM {$newsletterTable} n
444	INNER JOIN {$queueTable} sq ON sq.newsletter_id = n.id
445	INNER JOIN {$taskTable} st ON st.id = sq.task_id
446	INNER JOIN {$newsletterSegmentTable} ns ON ns.newsletter_id = n.id
447	WHERE n.type = :type
448	AND n.status = :newsletterStatus
449	AND n.deleted_at IS NULL
450	AND st.status = :taskStatus
451	AND st.type = :taskType
452	AND st.processed_at IS NOT NULL
453	AND sq.count_processed > 0
454	AND ns.segment_id = :segmentId
455	-- Skip replay queues/tasks so we pick the original standard send as the replay source,
456	-- not a previous replay (whose processed_at could otherwise win the ORDER BY).
457	AND (sq.meta IS NULL OR sq.meta NOT LIKE :latestNewsletterReplayMeta)
458	AND (st.meta IS NULL OR st.meta NOT LIKE :latestNewsletterReplayMeta)
459	ORDER BY st.processed_at DESC, st.id DESC
460	LIMIT :limit
461	",
462	[
463	'type' => NewsletterEntity::TYPE_STANDARD,
464	'newsletterStatus' => NewsletterEntity::STATUS_SENT,
465	'taskStatus' => ScheduledTaskEntity::STATUS_COMPLETED,
466	'taskType' => 'sending',
467	'segmentId' => $segmentId,
468	'latestNewsletterReplayMeta' => NewsletterReplayMetadata::getMetaLikePattern(),
469	'limit' => $overfetchLimit,
470	],
471	[
472	'limit' => ParameterType::INTEGER,
473	]
474	)->fetchAllAssociative();
475
476	foreach ($rows as $row) {
477	$newsletterId = $row['newsletter_id'] ?? null;
478	$queueId = $row['queue_id'] ?? null;
479	$taskId = $row['task_id'] ?? null;
480	if (!is_numeric($newsletterId) \|\| !is_numeric($queueId) \|\| !is_numeric($taskId)) {
481	continue;
482	}
483	$newsletter = $this->findOneById((int)$newsletterId);
484	$queue = $this->entityManager->find(SendingQueueEntity::class, (int)$queueId);
485	$task = $this->entityManager->find(ScheduledTaskEntity::class, (int)$taskId);
486	if (
487	$newsletter instanceof NewsletterEntity
488	&& $queue instanceof SendingQueueEntity
489	&& $task instanceof ScheduledTaskEntity
490	&& !NewsletterReplayMetadata::isLatestNewsletterReplayMeta($queue->getMeta())
491	&& !NewsletterReplayMetadata::isLatestNewsletterReplayMeta($task->getMeta())
492	) {
493	return [
494	'newsletter' => $newsletter,
495	'queue' => $queue,
496	'task' => $task,
497	];
498	}
499	}
500
501	return null;
502	}
503
504	/**
505	* @return int - number of processed ids
506	*/
507	public function bulkTrash(array $ids): int {
508	if (empty($ids)) {
509	return 0;
510	}
511	$this->loggerFactory->getLogger(LoggerFactory::TOPIC_NEWSLETTERS, $attachProcessors = true)->info(
512	'trashing newsletters',
513	['id' => $ids]
514	);
515	// Fetch children id for trashing
516	$childrenIds = $this->fetchChildrenIds($ids);
517	$ids = array_merge($ids, $childrenIds);
518
519	$this->entityManager->createQueryBuilder()
520	->update(NewsletterEntity::class, 'n')
521	->set('n.deletedAt', 'CURRENT_TIMESTAMP()')
522	->where('n.id IN (:ids)')
523	->setParameter('ids', $ids)
524	->getQuery()->execute();
525
526	// Trash scheduled tasks
527	$scheduledTasksTable = $this->entityManager->getClassMetadata(ScheduledTaskEntity::class)->getTableName();
528	$sendingQueueTable = $this->entityManager->getClassMetadata(SendingQueueEntity::class)->getTableName();
529	$this->entityManager->getConnection()->executeStatement("
530	UPDATE $scheduledTasksTable t
531	JOIN $sendingQueueTable q ON t.`id` = q.`task_id`
532	SET t.`deleted_at` = NOW()
533	WHERE q.`newsletter_id` IN (:ids)
534	", ['ids' => $ids], ['ids' => ArrayParameterType::INTEGER]);
535
536	// Trash sending queues
537	$this->entityManager->getConnection()->executeStatement("
538	UPDATE $sendingQueueTable q
539	SET q.`deleted_at` = NOW()
540	WHERE q.`newsletter_id` IN (:ids)
541	", ['ids' => $ids], ['ids' => ArrayParameterType::INTEGER]);
542
543	// Trash CPT.
544	$wpPostIds = $this->getWpPostIds($ids);
545
546	foreach ($wpPostIds as $wpPostId) {
547	wp_trash_post($wpPostId);
548	}
549
550	return count($ids);
551	}
552
553	public function bulkRestore(array $ids) {
554	if (empty($ids)) {
555	return 0;
556	}
557	// Fetch children ids to restore
558	$childrenIds = $this->fetchChildrenIds($ids);
559	$ids = array_merge($ids, $childrenIds);
560
561	$this->entityManager->createQueryBuilder()->update(NewsletterEntity::class, 'n')
562	->set('n.deletedAt', ':deletedAt')
563	->where('n.id IN (:ids)')
564	->setParameter('deletedAt', null)
565	->setParameter('ids', $ids)
566	->getQuery()->execute();
567
568	// Restore scheduled tasks and pause running ones
569	$scheduledTasksTable = $this->entityManager->getClassMetadata(ScheduledTaskEntity::class)->getTableName();
570	$sendingQueueTable = $this->entityManager->getClassMetadata(SendingQueueEntity::class)->getTableName();
571	$this->entityManager->getConnection()->executeStatement("
572	UPDATE $scheduledTasksTable t
573	JOIN $sendingQueueTable q ON t.`id` = q.`task_id`
574	SET t.`deleted_at` = null, t.`status` = IFNULL(t.status, :pausedStatus)
575	WHERE q.`newsletter_id` IN (:ids)
576	", [
577	'ids' => $ids,
578	'pausedStatus' => ScheduledTaskEntity::STATUS_PAUSED,
579	], [
580	'ids' => ArrayParameterType::INTEGER,
581	]);
582
583	// Restore sending queues
584	$this->entityManager->getConnection()->executeStatement("
585	UPDATE $sendingQueueTable q
586	SET q.`deleted_at` = null
587	WHERE q.`newsletter_id` IN (:ids)
588	", ['ids' => $ids], ['ids' => ArrayParameterType::INTEGER]);
589
590	// Untrash CPT.
591	$wpPostIds = $this->getWpPostIds($ids);
592
593	foreach ($wpPostIds as $wpPostId) {
594	wp_untrash_post($wpPostId);
595	}
596	return count($ids);
597	}
598
599	/** @param int[] $ids */
600	public function deleteByIds(array $ids): void {
601	$this->entityManager->createQueryBuilder()
602	->delete(NewsletterEntity::class, 'n')
603	->where('n.id IN (:ids)')
604	->setParameter('ids', $ids)
605	->getQuery()
606	->execute();
607
608	// delete was done via DQL, make sure the entities are also detached from the entity manager
609	$this->detachAll(function (NewsletterEntity $entity) use ($ids) {
610	return in_array($entity->getId(), $ids, true);
611	});
612	}
613
614	/**
615	* Parent post-notification newsletters that have at least one history with a
616	* paused or invalid sending task — i.e. sending was started but is now
617	* stalled and won't progress until the user intervenes (e.g. unauthorized
618	* sender domain, deleted segment, manual pause).
619	*
620	* Results are deduplicated per parent so a single noisy parent (which can
621	* accumulate multiple stuck histories — see PostNotificationScheduler::
622	* createPostNotificationSendingTask) doesn't crowd out other affected
623	* parents up to $limit. `hasInvalid` is true if any of the parent's stuck
624	* histories is in the INVALID state (more severe than PAUSED).
625	*
626	* @return array<int, array{parent: NewsletterEntity, hasInvalid: bool}>
627	*/
628	public function findStuckPostNotificationParents(int $limit = 5): array {
629	$rows = $this->entityManager->createQueryBuilder()
630	->select(
631	'p',
632	'MAX(CASE WHEN t.status = :invalidStatus THEN 1 ELSE 0 END) AS hasInvalid',
633	'MAX(t.updatedAt) AS latestUpdate'
634	)
635	->from(NewsletterEntity::class, 'p')
636	->join('p.children', 'n')
637	->join('n.queues', 'q')
638	->join('q.task', 't')
639	->where('p.type = :parentType')
640	->andWhere('n.type = :historyType')
641	->andWhere('n.status = :sendingStatus')
642	->andWhere('p.deletedAt IS NULL')
643	->andWhere('n.deletedAt IS NULL')
644	->andWhere('t.status IN (:stuckStatuses)')
645	->setParameter('parentType', NewsletterEntity::TYPE_NOTIFICATION)
646	->setParameter('historyType', NewsletterEntity::TYPE_NOTIFICATION_HISTORY)
647	->setParameter('sendingStatus', NewsletterEntity::STATUS_SENDING)
648	->setParameter('invalidStatus', ScheduledTaskEntity::STATUS_INVALID)
649	->setParameter('stuckStatuses', [
650	ScheduledTaskEntity::STATUS_PAUSED,
651	ScheduledTaskEntity::STATUS_INVALID,
652	])
653	->groupBy('p.id')
654	->orderBy('latestUpdate', 'DESC')
655	->setMaxResults($limit)
656	->getQuery()->getResult();
657
658	return array_map(static function (array $row): array {
659	return [
660	'parent' => $row[0],
661	'hasInvalid' => (bool)$row['hasInvalid'],
662	];
663	}, $rows);
664	}
665
666	/**
667	* @return NewsletterEntity[]
668	*/
669	public function findSendingNotificationHistoryWithoutPausedOrInvalidTask(NewsletterEntity $newsletter): array {
670	return $this->entityManager->createQueryBuilder()
671	->select('n')
672	->from(NewsletterEntity::class, 'n')
673	->join('n.queues', 'q')
674	->join('q.task', 't')
675	->where('n.parent = :parent')
676	->andWhere('n.type = :type')
677	->andWhere('n.status = :status')
678	->andWhere('n.deletedAt IS NULL')
679	->andWhere('t.status != :taskStatusPaused')
680	->andWhere('t.status != :taskStatusInvalid')
681	->setParameter('parent', $newsletter)
682	->setParameter('type', NewsletterEntity::TYPE_NOTIFICATION_HISTORY)
683	->setParameter('status', NewsletterEntity::STATUS_SENDING)
684	->setParameter('taskStatusPaused', ScheduledTaskEntity::STATUS_PAUSED)
685	->setParameter('taskStatusInvalid', ScheduledTaskEntity::STATUS_INVALID)
686	->getQuery()->getResult();
687	}
688
689	/**
690	* Returns standard newsletters ordered by sentAt
691	* @return NewsletterEntity[]
692	*/
693	public function getStandardNewsletterList(): array {
694	return $this->entityManager->createQueryBuilder()
695	->select('PARTIAL n.{id,subject,sentAt}, PARTIAL wpPost.{id, postTitle}')
696	->addSelect('CASE WHEN n.sentAt IS NULL THEN 1 ELSE 0 END as HIDDEN sent_at_is_null')
697	->from(NewsletterEntity::class, 'n')
698	->leftJoin('n.wpPost', 'wpPost')
699	->where('n.type = :typeStandard')
700	->andWhere('n.deletedAt IS NULL')
701	->orderBy('sent_at_is_null', 'DESC')
702	->addOrderBy('n.sentAt', 'DESC')
703	->setParameter('typeStandard', NewsletterEntity::TYPE_STANDARD)
704	->getQuery()
705	->getResult();
706	}
707
708	/**
709	* Returns standard newsletters and active automation emails ordered by sentAt.
710	* Drafts (which includes deactivated automations) are excluded to keep the
711	* dropdown focused on automations the user is currently running.
712	*
713	* @return NewsletterEntity[]
714	*/
715	public function getStandardAndAutomationNewsletterList(): array {
716	$queryBuilder = $this->entityManager->createQueryBuilder();
717	return $queryBuilder
718	->select('PARTIAL n.{id,subject,type,sentAt}, PARTIAL wpPost.{id, postTitle}')
719	->addSelect('CASE WHEN n.sentAt IS NULL THEN 1 ELSE 0 END as HIDDEN sent_at_is_null')
720	->from(NewsletterEntity::class, 'n')
721	->leftJoin('n.wpPost', 'wpPost')
722	->where(
723	$queryBuilder->expr()->orX(
724	$queryBuilder->expr()->eq('n.type', ':typeStandard'),
725	$queryBuilder->expr()->andX(
726	$queryBuilder->expr()->in('n.type', ':automationTypes'),
727	$queryBuilder->expr()->eq('n.status', ':statusActive')
728	)
729	)
730	)
731	->andWhere('n.deletedAt IS NULL')
732	->orderBy('sent_at_is_null', 'DESC')
733	->addOrderBy('n.sentAt', 'DESC')
734	->setParameter('typeStandard', NewsletterEntity::TYPE_STANDARD)
735	->setParameter('automationTypes', [NewsletterEntity::TYPE_AUTOMATION, NewsletterEntity::TYPE_AUTOMATION_TRANSACTIONAL], ArrayParameterType::STRING)
736	->setParameter('statusActive', NewsletterEntity::STATUS_ACTIVE)
737	->getQuery()
738	->getResult();
739	}
740
741	/**
742	* Returns standard newsletters ordered by sentAt
743	* filter by status STATUS_SCHEDULED, STATUS_SENDING, STATUS_SENT
744	* @return NewsletterEntity[]
745	*/
746	public function getStandardNewsletterListWithMultipleStatuses($limit): array {
747	$statuses = [
748	NewsletterEntity::STATUS_SCHEDULED,
749	NewsletterEntity::STATUS_SENDING,
750	NewsletterEntity::STATUS_SENT,
751	];
752
753	$query = $this->entityManager->createQueryBuilder()
754	->select('PARTIAL n.{id,subject,sentAt}')
755	->addSelect('CASE WHEN n.sentAt IS NULL THEN 1 ELSE 0 END as HIDDEN sent_at_is_null')
756	->from(NewsletterEntity::class, 'n')
757	->where('n.type = :typeStandard')
758	->andWhere('n.status IN (:statuses)')
759	->andWhere('n.deletedAt IS NULL')
760	->orderBy('sent_at_is_null', 'DESC')
761	->addOrderBy('n.sentAt', 'DESC')
762	->setParameter('typeStandard', NewsletterEntity::TYPE_STANDARD)
763	->setParameter('statuses', $statuses);
764
765	if (is_int($limit)) {
766	$query->setMaxResults($limit);
767	}
768
769	$result = $query->getQuery()->getResult();
770	if (!is_array($result)) {
771	return [];
772	}
773	/** @var NewsletterEntity[] $result */
774	return $result;
775	}
776
777	/**
778	* Returns sent post-notification history newsletters ordered by sentAt
779	* @return NewsletterEntity[]
780	*/
781	public function getNotificationHistoryItems($limit): array {
782	$query = $this->entityManager->createQueryBuilder()
783	->select('PARTIAL n.{id,subject,sentAt}')
784	->addSelect('CASE WHEN n.sentAt IS NULL THEN 1 ELSE 0 END as HIDDEN sent_at_is_null')
785	->from(NewsletterEntity::class, 'n')
786	->where('n.type = :typeNotificationHistory')
787	->andWhere('n.status = :status')
788	->andWhere('n.deletedAt IS NULL')
789	->orderBy('sent_at_is_null', 'DESC')
790	->addOrderBy('n.sentAt', 'DESC')
791	->setParameter('typeNotificationHistory', NewsletterEntity::TYPE_NOTIFICATION_HISTORY)
792	->setParameter('status', NewsletterEntity::STATUS_SENT);
793
794	if (is_int($limit)) {
795	$query->setMaxResults($limit);
796	}
797
798	$result = $query->getQuery()->getResult();
799	if (!is_array($result)) {
800	return [];
801	}
802	/** @var NewsletterEntity[] $result */
803	return $result;
804	}
805
806	public function prefetchOptions(array $newsletters) {
807	$this->entityManager->createQueryBuilder()
808	->select('PARTIAL n.{id}, o, opf')
809	->from(NewsletterEntity::class, 'n')
810	->join('n.options', 'o')
811	->join('o.optionField', 'opf')
812	->where('n.id IN (:newsletters)')
813	->setParameter('newsletters', $newsletters)
814	->getQuery()
815	->getResult();
816	}
817
818	public function prefetchSegments(array $newsletters) {
819	$this->entityManager->createQueryBuilder()
820	->select('PARTIAL n.{id}, ns, s')
821	->from(NewsletterEntity::class, 'n')
822	->join('n.newsletterSegments', 'ns')
823	->join('ns.segment', 's')
824	->where('n.id IN (:newsletters)')
825	->setParameter('newsletters', $newsletters)
826	->getQuery()
827	->getResult();
828	}
829
830	/**
831	* Returns a list of emails that are either scheduled standard emails
832	* or active automatic emails of the provided types.
833	*
834	* @param array $automaticEmailTypes
835	*
836	* @return array
837	*/
838	public function getScheduledStandardEmailsAndActiveAutomaticEmails(array $automaticEmailTypes): array {
839	$queryBuilder = $this->entityManager->createQueryBuilder();
840
841	$newsletters = $queryBuilder
842	->select('n')
843	->from(NewsletterEntity::class, 'n')
844	->orWhere(
845	$queryBuilder->expr()->andX(
846	$queryBuilder->expr()->eq('n.type', ':typeStandard'),
847	$queryBuilder->expr()->eq('n.status', ':statusScheduled')
848	)
849	)
850	->orWhere(
851	$queryBuilder->expr()->andX(
852	$queryBuilder->expr()->in('n.type', ':automaticEmailTypes'),
853	$queryBuilder->expr()->eq('n.status', ':statusActive')
854	)
855	)
856	->setParameter('typeStandard', NewsletterEntity::TYPE_STANDARD)
857	->setParameter('statusScheduled', NewsletterEntity::STATUS_SCHEDULED)
858	->setParameter('automaticEmailTypes', $automaticEmailTypes)
859	->setParameter('statusActive', NewsletterEntity::STATUS_ACTIVE)
860	->getQuery()
861	->getResult();
862
863	return $newsletters;
864	}
865
866	public function getCorruptNewsletters(): array {
867	return $this->findBy(['status' => NewsletterEntity::STATUS_CORRUPT, 'deletedAt' => null]);
868	}
869
870	public function setAsCorrupt(NewsletterEntity $entity): void {
871	$entity->setStatus(NewsletterEntity::STATUS_CORRUPT);
872	$this->persist($entity);
873	$this->flush();
874	}
875
876	/**
877	* @param int[] $parentIds
878	* @return int[]
879	*/
880	public function fetchChildrenIds(array $parentIds): array {
881	/** @var string[] $ids */
882	$ids = $this->entityManager->createQueryBuilder()
883	->select('n.id')
884	->from(NewsletterEntity::class, 'n')
885	->where('n.parent IN (:ids)')
886	->setParameter('ids', $parentIds)
887	->getQuery()
888	->getSingleColumnResult();
889	return array_map('intval', $ids);
890	}
891
892	public function getWpPostIds(array $ids): array {
893	/** @var string[] $wpPostIds */
894	$wpPostIds = $this->entityManager->createQueryBuilder()
895	->select('IDENTITY(n.wpPost) AS id')
896	->from(NewsletterEntity::class, 'n')
897	->where('n.id IN (:ids)')
898	->andWhere('n.wpPost IS NOT NULL')
899	->setParameter('ids', $ids)
900	->getQuery()
901	->getSingleColumnResult();
902
903	$wpPostIds = array_map('intval', $wpPostIds);
904
905	return $wpPostIds;
906	}
907	}
908