acymailing / back / Classes / MailStatClass.php

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

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