acymailing / back / Classes / MailStatClass.php

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

266 lines 8.7 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	$onDuplicate[] = 'tracking_sale = '.floatval($mailStat['tracking_sale']);
67	}
68
69	if (isset($mailStat['currency'])) {
70	$onDuplicate[] = 'currency = '.acym_escapeDB($mailStat['currency']);
71	}
72
73	if (!empty($onDuplicate)) {
74	$query .= ' ON DUPLICATE KEY UPDATE ';
75	$query .= implode(',', $onDuplicate);
76	$query = 'INSERT INTO '.$query;
77	} else {
78	$query = 'INSERT IGNORE INTO '.$query;
79	}
80
81	return (int)acym_query($query);
82	}
83
84	public function getTotalSubscribersByMailId(int $mailId): int
85	{
86	$result = acym_loadResult('SELECT total_subscribers FROM #__acym_mail_stat WHERE mail_id = '.intval($mailId));
87
88	return empty($result) ? 0 : $result;
89	}
90
91	public function getTotalSubscribersByMailIdWithChild(int $mailId): int
92	{
93	$result = acym_loadResult(
94	'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).')'
95	);
96
97	return empty($result) ? 0 : $result;
98	}
99
100	public function getOneByMailId(int $id): ?object
101	{
102	$query = 'SELECT SUM(sent) AS sent, SUM(fail) AS fail FROM #__acym_mail_stat';
103	$query .= empty($id) ? '' : ' WHERE `mail_id` = '.intval($id);
104
105	$stats = acym_loadObject($query);
106
107	return empty($stats) ? null : $stats;
108	}
109
110	public function getSentFailByMailIds(array $mailIds = []): object
111	{
112	acym_arrayToInteger($mailIds);
113
114	$query = 'SELECT SUM(sent) AS sent, SUM(fail) AS fail FROM #__acym_mail_stat';
115	$query .= empty($mailIds) ? '' : ' WHERE `mail_id` IN ('.implode(',', $mailIds).')';
116
117	return acym_loadObject($query);
118	}
119
120	public function getOneRowByMailId(int $mailId): ?object
121	{
122	$stats = acym_loadObject('SELECT * FROM #__acym_mail_stat WHERE mail_id = '.intval($mailId));
123
124	return empty($stats) ? null : $stats;
125	}
126
127	public function getAllMailsForStats(string $search = ''): array
128	{
129	$mailClass = new MailClass();
130
131	$query = 'SELECT mail.*
132	FROM #__acym_mail AS mail
133	JOIN #__acym_mail_stat AS mail_stat ON mail.id = mail_stat.mail_id';
134
135	$queryAutoCampaign = 'SELECT mail.* FROM #__acym_mail AS mail
136	JOIN #__acym_campaign as campaign ON campaign.mail_id = mail.id AND campaign.sending_type = '.acym_escapeDB(CampaignClass::SENDING_TYPE_AUTO);
137
138	$querySearch = '';
139
140	if (!empty($search)) {
141	$querySearch .= ' mail.name LIKE '.acym_escapeDB('%'.acym_utf8Encode($search).'%').' ';
142	$queryAutoCampaign .= ' WHERE '.$querySearch;
143
144	$querySearch = ' AND '.$querySearch;
145	}
146
147	$query .= ' WHERE mail.parent_id IS NULL '.$querySearch;
148	$query .= ' ORDER BY mail_stat.send_date DESC LIMIT 20';
149
150	$mails = acym_loadObjectList($query);
151	$mailsAuto = acym_loadObjectList($queryAutoCampaign);
152
153	return $mailClass->decode(array_merge($mails, $mailsAuto));
154	}
155
156	public function getCumulatedStatsByMailIds(array $mailsIds = []): object
157	{
158	acym_arrayToInteger($mailsIds);
159	$condMailIds = '';
160	if (!empty($mailsIds)) {
161	$condMailIds = 'WHERE mail_id IN ('.implode(',', $mailsIds).')';
162	}
163
164	$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;
165
166	return acym_loadObject($query);
167	}
168
169	public function getByMailIds(array $mailIds): ?object
170	{
171	acym_arrayToInteger($mailIds);
172
173	if (empty($mailIds)) {
174	return null;
175	}
176
177	return acym_loadObject('SELECT * FROM #__acym_mail_stat WHERE mail_id IN ('.implode(',', $mailIds).')');
178	}
179
180	public function migrateTrackingSale(): void
181	{
182	$query = 'SELECT tracking_sale, currency, mail_id FROM #__acym_user_stat WHERE currency IS NOT NULL';
183
184	$trackingSales = acym_loadObjectList($query);
185
186	if (empty($trackingSales)) return;
187
188	$mailStats = [];
189
190	foreach ($trackingSales as $sale) {
191	if (empty($mailStats[$sale->mail_id])) {
192	$mailStats[$sale->mail_id] = new \stdClass();
193	$mailStats[$sale->mail_id]->mail_id = $sale->mail_id;
194	$mailStats[$sale->mail_id]->tracking_sale = $sale->tracking_sale;
195	$mailStats[$sale->mail_id]->currency = $sale->currency;
196	} else {
197	$mailStats[$sale->mail_id]->tracking_sale += $sale->tracking_sale;
198	}
199	}
200
201	foreach ($mailStats as $mailStat) {
202	$this->save($mailStat);
203	}
204	}
205
206	public function getBestEmailByStats(array $mailIds, string $statsType): array
207	{
208	acym_arrayToInteger($mailIds);
209
210	$mailStats = acym_loadObjectList(
211	'SELECT ms.total_subscribers, ms.mail_id, ms.open_total, ms.click_total
212	FROM #__acym_mail_stat AS ms
213	WHERE ms.mail_id IN ('.implode(',', $mailIds).')',
214	'mail_id'
215	);
216
217	foreach ($mailStats as $mailStat) {
218	$mailStat->click_rate = $mailStat->open_total > 0 ? $mailStat->click_total / $mailStat->open_total : 0;
219	$mailStat->open_rate = $mailStat->total_subscribers > 0 ? $mailStat->open_total / $mailStat->total_subscribers : 0;
220	}
221
222	if ($statsType === 'click_rate') {
223	return ['click_rate' => $this->getBestMailByRate($mailStats, 'click_rate')];
224	} elseif ($statsType === 'open_rate') {
225	return ['open_rate' => $this->getBestMailByRate($mailStats, 'open_rate')];
226	} else {
227	return [
228	'click_rate' => $this->getBestMailByRate($mailStats, 'click_rate'),
229	'open_rate' => $this->getBestMailByRate($mailStats, 'open_rate'),
230	];
231	}
232	}
233
234	private function getBestMailByRate(array $mailStats, string $type): int
235	{
236	uasort(
237	$mailStats,
238	function ($a, $b) use ($type) {
239	if ($a->$type == $b->$type) {
240	return 0;
241	}
242
243	return ($a->$type < $b->$type) ? 1 : -1;
244	}
245	);
246
247	return array_keys($mailStats)[0];
248	}
249
250	public function incrementClicks(int $mailId, bool $isFirst): void
251	{
252	acym_query(
253	'UPDATE #__acym_mail_stat
254	SET click_total = click_total + 1'.($isFirst ? ', click_unique = click_unique + 1' : '').'
255	WHERE mail_id = '.intval($mailId)
256	);
257	}
258
259	public function getOneSentEmail(): bool
260	{
261	$firstSentEmail = acym_loadObject('SELECT * FROM #__acym_mail_stat WHERE sent > 0');
262
263	return !empty($firstSentEmail);
264	}
265	}
266