acymailing / back / Classes / MailStatClass.php

MailStatClass.php in AcyMailing – An Ultimate Newsletter Plugin and Marketing Automation Solution for WordPress 11.0.0, at back/Classes/MailStatClass.php

267 lines 8.8 KB

Raw Download Zip

1	<?php
2
3	namespace AcyMailing\Classes;
4
5	use AcyMailing\Core\AcymClass;
6
7	class MailStatClass extends AcymClass
8	{
9	public function __construct()
10	{
11	parent::__construct();
12
13	$this->table = 'mail_stat';
14	$this->pkey = 'mail_id';
15	}
16
17	public function save($element): ?int
18	{
19	$mailStat = is_object($element) ? get_object_vars($element) : $element;
20
21	$column = [];
22	$valueColumn = [];
23	$columnName = acym_getColumns('mail_stat');
24
25	foreach ($mailStat as $key => $value) {
26	if (in_array($key, $columnName)) {
27	$column[] = '`'.acym_secureDBColumn($key).'`';
28
29	if ($key === 'tracking_sale') {
30	$valueColumn[] = is_null($value) \|\| strlen($value) === 0 ? 'NULL' : floatval($value);
31	} else {
32	$valueColumn[] = acym_escapeDB($value);
33	}
34	}
35	}
36
37	$query = '#__acym_mail_stat ('.implode(',', $column).') VALUES ('.implode(', ', $valueColumn).')';
38
39	$onDuplicate = [];
40
41	if (isset($mailStat['sent'])) {
42	$onDuplicate[] = ' sent = sent + '.intval($mailStat['sent']);
43	}
44
45	if (isset($mailStat['fail'])) {
46	$onDuplicate[] = ' fail = fail + '.intval($mailStat['fail']);
47	}
48
49	if (isset($mailStat['open_unique'])) {
50	$onDuplicate[] = 'open_unique = open_unique + '.intval($mailStat['open_unique']);
51	}
52
53	if (isset($mailStat['open_total'])) {
54	$onDuplicate[] = 'open_total = open_total + '.intval($mailStat['open_total']);
55	}
56
57	if (isset($mailStat['total_subscribers'])) {
58	$onDuplicate[] = 'total_subscribers = total_subscribers + '.intval($mailStat['total_subscribers']);
59	}
60
61	if (isset($mailStat['unsubscribe_total'])) {
62	$onDuplicate[] = ' unsubscribe_total = unsubscribe_total + '.intval($mailStat['unsubscribe_total']);
63	}
64
65	if (isset($mailStat['tracking_sale'])) {
66	// Cannot be changed as it is not guaranteed that the plugin is updated at the same time
67	$onDuplicate[] = 'tracking_sale = '.floatval($mailStat['tracking_sale']);
68	}
69
70	if (isset($mailStat['currency'])) {
71	$onDuplicate[] = 'currency = '.acym_escapeDB($mailStat['currency']);
72	}
73
74	if (!empty($onDuplicate)) {
75	$query .= ' ON DUPLICATE KEY UPDATE ';
76	$query .= implode(',', $onDuplicate);
77	$query = 'INSERT INTO '.$query;
78	} else {
79	$query = 'INSERT IGNORE INTO '.$query;
80	}
81
82	return (int)acym_query($query);
83	}
84
85	public function getTotalSubscribersByMailId(int $mailId): int
86	{
87	$result = acym_loadResult('SELECT total_subscribers FROM #__acym_mail_stat WHERE mail_id = '.intval($mailId));
88
89	return empty($result) ? 0 : $result;
90	}
91
92	public function getTotalSubscribersByMailIdWithChild(int $mailId): int
93	{
94	$result = acym_loadResult(
95	'SELECT SUM(total_subscribers) FROM #__acym_mail_stat WHERE mail_id IN (SELECT id FROM #__acym_mail WHERE id = '.intval($mailId).' OR parent_id = '.intval($mailId).')'
96	);
97
98	return empty($result) ? 0 : $result;
99	}
100
101	public function getOneByMailId(int $id): ?object
102	{
103	$query = 'SELECT SUM(sent) AS sent, SUM(fail) AS fail FROM #__acym_mail_stat';
104	$query .= empty($id) ? '' : ' WHERE `mail_id` = '.intval($id);
105
106	$stats = acym_loadObject($query);
107
108	return empty($stats) ? null : $stats;
109	}
110
111	public function getSentFailByMailIds(array $mailIds = []): object
112	{
113	acym_arrayToInteger($mailIds);
114
115	$query = 'SELECT SUM(sent) AS sent, SUM(fail) AS fail FROM #__acym_mail_stat';
116	$query .= empty($mailIds) ? '' : ' WHERE `mail_id` IN ('.implode(',', $mailIds).')';
117
118	return acym_loadObject($query);
119	}
120
121	public function getOneRowByMailId(int $mailId): ?object
122	{
123	$stats = acym_loadObject('SELECT * FROM #__acym_mail_stat WHERE mail_id = '.intval($mailId));
124
125	return empty($stats) ? null : $stats;
126	}
127
128	public function getAllMailsForStats(string $search = ''): array
129	{
130	$mailClass = new MailClass();
131
132	$query = 'SELECT mail.*
133	FROM #__acym_mail AS mail
134	JOIN #__acym_mail_stat AS mail_stat ON mail.id = mail_stat.mail_id';
135
136	$queryAutoCampaign = 'SELECT mail.* FROM #__acym_mail AS mail
137	JOIN #__acym_campaign as campaign ON campaign.mail_id = mail.id AND campaign.sending_type = '.acym_escapeDB(CampaignClass::SENDING_TYPE_AUTO);
138
139	$querySearch = '';
140
141	if (!empty($search)) {
142	$querySearch .= ' mail.name LIKE '.acym_escapeDB('%'.acym_utf8Encode($search).'%').' ';
143	$queryAutoCampaign .= ' WHERE '.$querySearch;
144
145	$querySearch = ' AND '.$querySearch;
146	}
147
148	$query .= ' WHERE mail.parent_id IS NULL '.$querySearch;
149	$query .= ' ORDER BY mail_stat.send_date DESC LIMIT 20';
150
151	$mails = acym_loadObjectList($query);
152	$mailsAuto = acym_loadObjectList($queryAutoCampaign);
153
154	return $mailClass->decode(array_merge($mails, $mailsAuto));
155	}
156
157	public function getCumulatedStatsByMailIds(array $mailsIds = []): object
158	{
159	acym_arrayToInteger($mailsIds);
160	$condMailIds = '';
161	if (!empty($mailsIds)) {
162	$condMailIds = 'WHERE mail_id IN ('.implode(',', $mailsIds).')';
163	}
164
165	$query = 'SELECT SUM(sent) AS sent, SUM(open_unique) AS open, SUM(fail) AS fails, SUM(bounce_unique) AS bounces FROM #__acym_mail_stat '.$condMailIds;
166
167	return acym_loadObject($query);
168	}
169
170	public function getByMailIds(array $mailIds): ?object
171	{
172	acym_arrayToInteger($mailIds);
173
174	if (empty($mailIds)) {
175	return null;
176	}
177
178	return acym_loadObject('SELECT * FROM #__acym_mail_stat WHERE mail_id IN ('.implode(',', $mailIds).')');
179	}
180
181	public function migrateTrackingSale(): void
182	{
183	$query = 'SELECT tracking_sale, currency, mail_id FROM #__acym_user_stat WHERE currency IS NOT NULL';
184
185	$trackingSales = acym_loadObjectList($query);
186
187	if (empty($trackingSales)) return;
188
189	$mailStats = [];
190
191	foreach ($trackingSales as $sale) {
192	if (empty($mailStats[$sale->mail_id])) {
193	$mailStats[$sale->mail_id] = new \stdClass();
194	$mailStats[$sale->mail_id]->mail_id = $sale->mail_id;
195	$mailStats[$sale->mail_id]->tracking_sale = $sale->tracking_sale;
196	$mailStats[$sale->mail_id]->currency = $sale->currency;
197	} else {
198	$mailStats[$sale->mail_id]->tracking_sale += $sale->tracking_sale;
199	}
200	}
201
202	foreach ($mailStats as $mailStat) {
203	$this->save($mailStat);
204	}
205	}
206
207	public function getBestEmailByStats(array $mailIds, string $statsType): array
208	{
209	acym_arrayToInteger($mailIds);
210
211	$mailStats = acym_loadObjectList(
212	'SELECT ms.total_subscribers, ms.mail_id, ms.open_total, ms.click_total
213	FROM #__acym_mail_stat AS ms
214	WHERE ms.mail_id IN ('.implode(',', $mailIds).')',
215	'mail_id'
216	);
217
218	foreach ($mailStats as $mailStat) {
219	$mailStat->click_rate = $mailStat->open_total > 0 ? $mailStat->click_total / $mailStat->open_total : 0;
220	$mailStat->open_rate = $mailStat->total_subscribers > 0 ? $mailStat->open_total / $mailStat->total_subscribers : 0;
221	}
222
223	if ($statsType === 'click_rate') {
224	return ['click_rate' => $this->getBestMailByRate($mailStats, 'click_rate')];
225	} elseif ($statsType === 'open_rate') {
226	return ['open_rate' => $this->getBestMailByRate($mailStats, 'open_rate')];
227	} else {
228	return [
229	'click_rate' => $this->getBestMailByRate($mailStats, 'click_rate'),
230	'open_rate' => $this->getBestMailByRate($mailStats, 'open_rate'),
231	];
232	}
233	}
234
235	private function getBestMailByRate(array $mailStats, string $type): int
236	{
237	uasort(
238	$mailStats,
239	function ($a, $b) use ($type) {
240	if ($a->$type == $b->$type) {
241	return 0;
242	}
243
244	return ($a->$type < $b->$type) ? 1 : -1;
245	}
246	);
247
248	return array_keys($mailStats)[0];
249	}
250
251	public function incrementClicks(int $mailId, bool $isFirst): void
252	{
253	acym_query(
254	'UPDATE #__acym_mail_stat
255	SET click_total = click_total + 1'.($isFirst ? ', click_unique = click_unique + 1' : '').'
256	WHERE mail_id = '.intval($mailId)
257	);
258	}
259
260	public function getOneSentEmail(): bool
261	{
262	$firstSentEmail = acym_loadObject('SELECT * FROM #__acym_mail_stat WHERE sent > 0');
263
264	return !empty($firstSentEmail);
265	}
266	}
267