← All changes | back/Classes/MailStatClass.php +132 -32 10.1.4 → 11.1.0 View file →

		@@ -13,10 +13,12 @@
13	13	$this->table = 'mail_stat';
14	14	$this->pkey = 'mail_id';
15	15	}
16	16
17		- public function save($mailStat)
	17	+ public function save($element): ?int
18	18	{
	19	+ $mailStat = is_object($element) ? get_object_vars($element) : $element;
	20	+
19	21	$column = [];
20	22	$valueColumn = [];
21	23	$columnName = acym_getColumns('mail_stat');
22	24
		@@ -60,8 +62,9 @@
60	62	$onDuplicate[] = ' unsubscribe_total = unsubscribe_total + '.intval($mailStat['unsubscribe_total']);
61	63	}
62	64
63	65	if (isset($mailStat['tracking_sale'])) {
	66	+ // Cannot be changed as it is not guaranteed that the plugin is updated at the same time
64	67	$onDuplicate[] = 'tracking_sale = '.floatval($mailStat['tracking_sale']);
65	68	}
66	69
67	70	if (isset($mailStat['currency'])) {
		@@ -75,39 +78,39 @@
75	78	} else {
76	79	$query = 'INSERT IGNORE INTO '.$query;
77	80	}
78	81
79		- return acym_query($query);
	82	+ return (int)acym_query($query);
80	83	}
81	84
82		- public function getTotalSubscribersByMailId($mailId)
	85	+ public function getTotalSubscribersByMailId(int $mailId): int
83	86	{
84	87	$result = acym_loadResult('SELECT total_subscribers FROM #__acym_mail_stat WHERE mail_id = '.intval($mailId));
85	88
86		- return $result === null ? 0 : $result;
	89	+ return empty($result) ? 0 : $result;
87	90	}
88	91
89		- public function getTotalSubscribersByMailIdWithChild($mailId)
	92	+ public function getTotalSubscribersByMailIdWithChild(int $mailId): int
90	93	{
91	94	$result = acym_loadResult(
92	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).')'
93	96	);
94	97
95		- return $result === null ? 0 : $result;
	98	+ return empty($result) ? 0 : $result;
96	99	}
97	100
98		- public function getOneByMailId($id = '')
	101	+ public function getOneByMailId(int $id): ?object
99	102	{
100	103	$query = 'SELECT SUM(sent) AS sent, SUM(fail) AS fail FROM #__acym_mail_stat';
101	104	$query .= empty($id) ? '' : ' WHERE `mail_id` = '.intval($id);
102	105
103		- return acym_loadObject($query);
	106	+ $stats = acym_loadObject($query);
	107	+
	108	+ return empty($stats) ? null : $stats;
104	109	}
105	110
106		- public function getSentFailByMailIds($mailIds = [])
	111	+ public function getSentFailByMailIds(array $mailIds = []): object
107	112	{
108		- if (!is_array($mailIds)) $mailIds = [$mailIds];
109		-
110	113	acym_arrayToInteger($mailIds);
111	114
112	115	$query = 'SELECT SUM(sent) AS sent, SUM(fail) AS fail FROM #__acym_mail_stat';
113	116	$query .= empty($mailIds) ? '' : ' WHERE `mail_id` IN ('.implode(',', $mailIds).')';
		@@ -114,14 +117,16 @@
114	117
115	118	return acym_loadObject($query);
116	119	}
117	120
118		- public function getOneRowByMailId($mailId)
	121	+ public function getOneRowByMailId(int $mailId): ?object
119	122	{
120		- return acym_loadObject('SELECT * FROM #__acym_mail_stat WHERE mail_id = '.intval($mailId));
	123	+ $stats = acym_loadObject('SELECT * FROM #__acym_mail_stat WHERE mail_id = '.intval($mailId));
	124	+
	125	+ return empty($stats) ? null : $stats;
121	126	}
122	127
123		- public function getAllMailsForStats($search = '')
	128	+ public function getAllMailsForStats(string $search = ''): array
124	129	{
125	130	$mailClass = new MailClass();
126	131
127	132	$query = 'SELECT mail.*
		@@ -140,9 +145,8 @@
140	145	$querySearch = ' AND '.$querySearch;
141	146	}
142	147
143	148	$query .= ' WHERE mail.parent_id IS NULL '.$querySearch;
144		-
145	149	$query .= ' ORDER BY mail_stat.send_date DESC LIMIT 20';
146	150
147	151	$mails = acym_loadObjectList($query);
148	152	$mailsAuto = acym_loadObjectList($queryAutoCampaign);
		@@ -149,9 +153,9 @@
149	153
150	154	return $mailClass->decode(array_merge($mails, $mailsAuto));
151	155	}
152	156
153		- public function getCumulatedStatsByMailIds($mailsIds = [])
	157	+ public function getCumulatedStatsByMailIds(array $mailsIds = []): object
154	158	{
155	159	acym_arrayToInteger($mailsIds);
156	160	$condMailIds = '';
157	161	if (!empty($mailsIds)) {
		@@ -157,23 +161,25 @@
157	161	if (!empty($mailsIds)) {
158	162	$condMailIds = 'WHERE mail_id IN ('.implode(',', $mailsIds).')';
159	163	}
160	164
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;
	165	+ $query = 'SELECT SUM(sent) AS sent, SUM(open_unique) AS open, SUM(open_total) AS open_total, SUM(click_unique) AS click_unique, SUM(click_total) AS click_total, SUM(fail) AS fails, SUM(bounce_unique) AS bounces FROM #__acym_mail_stat '.$condMailIds;
162	166
163	167	return acym_loadObject($query);
164	168	}
165	169
166		- public function getByMailIds($mailIds)
	170	+ public function getByMailIds(array $mailIds): ?object
167	171	{
168		- if (!is_array($mailIds)) $mailIds = [$mailIds];
	172	+ acym_arrayToInteger($mailIds);
169	173
170		- acym_arrayToInteger($mailIds);
	174	+ if (empty($mailIds)) {
	175	+ return null;
	176	+ }
171	177
172	178	return acym_loadObject('SELECT * FROM #__acym_mail_stat WHERE mail_id IN ('.implode(',', $mailIds).')');
173	179	}
174	180
175		- public function migrateTrackingSale()
	181	+ public function migrateTrackingSale(): void
176	182	{
177	183	$query = 'SELECT tracking_sale, currency, mail_id FROM #__acym_user_stat WHERE currency IS NOT NULL';
178	184
179	185	$trackingSales = acym_loadObjectList($query);
		@@ -183,15 +189,14 @@
183	189	$mailStats = [];
184	190
185	191	foreach ($trackingSales as $sale) {
186	192	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		- ];
	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;
192	197	} else {
193		- $mailStats[$sale->mail_id]['tracking_sale'] += $sale->tracking_sale;
	198	+ $mailStats[$sale->mail_id]->tracking_sale += $sale->tracking_sale;
194	199	}
195	200	}
196	201
197	202	foreach ($mailStats as $mailStat) {
		@@ -209,11 +214,11 @@
209	214	WHERE ms.mail_id IN ('.implode(',', $mailIds).')',
210	215	'mail_id'
211	216	);
212	217
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;
	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;
216	221	}
217	222
218	223	if ($statsType === 'click_rate') {
219	224	return ['click_rate' => $this->getBestMailByRate($mailStats, 'click_rate')];
		@@ -226,9 +231,9 @@
226	231	];
227	232	}
228	233	}
229	234
230		- private function getBestMailByRate($mailStats, $type)
	235	+ private function getBestMailByRate(array $mailStats, string $type): int
231	236	{
232	237	uasort(
233	238	$mailStats,
234	239	function ($a, $b) use ($type) {
		@@ -242,9 +247,9 @@
242	247
243	248	return array_keys($mailStats)[0];
244	249	}
245	250
246		- public function incrementClicks(int $mailId, bool $isFirst)
	251	+ public function incrementClicks(int $mailId, bool $isFirst): void
247	252	{
248	253	acym_query(
249	254	'UPDATE #__acym_mail_stat
250	255	SET click_total = click_total + 1'.($isFirst ? ', click_unique = click_unique + 1' : '').'
		@@ -249,6 +254,101 @@
249	254	'UPDATE #__acym_mail_stat
250	255	SET click_total = click_total + 1'.($isFirst ? ', click_unique = click_unique + 1' : '').'
251	256	WHERE mail_id = '.intval($mailId)
252	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	+ public function incrementDetail(int $mailId, string $type, string $key): void
	268	+ {
	269	+ if (empty($mailId) || $type === '') {
	270	+ return;
	271	+ }
	272	+
	273	+ acym_query(
	274	+ 'INSERT INTO #__acym_mail_stat_detail (`mail_id`, `detail_type`, `detail_key`, `number`)
	275	+ VALUES ('.intval($mailId).', '.acym_escapeDB($type).', '.acym_escapeDB($key).', 1)
	276	+ ON DUPLICATE KEY UPDATE `number` = `number` + 1'
	277	+ );
	278	+ }
	279	+
	280	+ public function getAnonDevices(array $mailIds = []): array
	281	+ {
	282	+ return $this->getAnonGroupedDetail($mailIds, 'device', 'device');
	283	+ }
	284	+
	285	+ public function getAnonOpenSources(array $mailIds = []): array
	286	+ {
	287	+ return $this->getAnonGroupedDetail($mailIds, 'client', 'opened_with');
	288	+ }
	289	+
	290	+ private function getAnonGroupedDetail(array $mailIds, string $type, string $alias): array
	291	+ {
	292	+ acym_arrayToInteger($mailIds);
	293	+ $condMailIds = empty($mailIds) ? '' : ' AND mail_id IN ('.implode(',', $mailIds).')';
	294	+
	295	+ return acym_loadObjectList(
	296	+ 'SELECT detail_key AS `'.acym_secureDBColumn($alias).'`, SUM(number) AS number
	297	+ FROM #__acym_mail_stat_detail
	298	+ WHERE detail_type = '.acym_escapeDB($type).$condMailIds.'
	299	+ GROUP BY detail_key'
	300	+ );
	301	+ }
	302	+
	303	+ public function getAnonOpensByFormat(array $mailIds, string $format, string $start = '', string $end = ''): array
	304	+ {
	305	+ return $this->getAnonTimeSeries($mailIds, 'open_time', $format, 'open', 'open_date', $start, $end);
	306	+ }
	307	+
	308	+ private function getAnonTimeSeries(array $mailIds, string $type, string $format, string $countAlias, string $dateAlias, string $start, string $end): array
	309	+ {
	310	+ acym_arrayToInteger($mailIds);
	311	+ $cond = empty($mailIds) ? '' : ' AND mail_id IN ('.implode(',', $mailIds).')';
	312	+ if (!empty($start)) {
	313	+ $cond .= ' AND detail_key >= '.acym_escapeDB($start);
	314	+ }
	315	+ if (!empty($end)) {
	316	+ $cond .= ' AND detail_key <= '.acym_escapeDB($end);
	317	+ }
	318	+
	319	+ return acym_loadObjectList(
	320	+ 'SELECT SUM(number) AS `'.acym_secureDBColumn($countAlias).'`, DATE_FORMAT(detail_key, '.acym_escapeDB($format).') AS `'.acym_secureDBColumn($dateAlias).'`
	321	+ FROM #__acym_mail_stat_detail
	322	+ WHERE detail_type = '.acym_escapeDB($type).$cond.'
	323	+ GROUP BY `'.acym_secureDBColumn($dateAlias).'`'
	324	+ );
	325	+ }
	326	+
	327	+ public function getAnonOpenTimeStats(array $mailIds): array
	328	+ {
	329	+ acym_arrayToInteger($mailIds);
	330	+ $cond = empty($mailIds) ? '' : ' AND mail_id IN ('.implode(',', $mailIds).')';
	331	+ $offsetSeconds = (int)acym_getTimeOffsetCMS();
	332	+
	333	+ return [
	334	+ 'total_open' => acym_loadResult(
	335	+ 'SELECT SUM(number) FROM #__acym_mail_stat_detail WHERE detail_type = '.acym_escapeDB('open_time').$cond
	336	+ ),
	337	+ 'stats' => acym_loadObjectList(
	338	+ 'SELECT
	339	+ SUM(number) AS `open_total`,
	340	+ DATE_FORMAT(DATE_ADD(detail_key, INTERVAL '.$offsetSeconds.' SECOND), "%w") AS `day`,
	341	+ FORMAT(CONVERT(DATE_FORMAT(DATE_ADD(detail_key, INTERVAL '.$offsetSeconds.' SECOND), "%H"), SIGNED INTEGER) / 3, 0) AS `hour`,
	342	+ CONCAT(
	343	+ DATE_FORMAT(DATE_ADD(detail_key, INTERVAL '.$offsetSeconds.' SECOND), "%w"),
	344	+ "_",
	345	+ FORMAT(CONVERT(DATE_FORMAT(DATE_ADD(detail_key, INTERVAL '.$offsetSeconds.' SECOND), "%H"), SIGNED INTEGER) / 3, 0)
	346	+ ) AS `date_id`
	347	+ FROM #__acym_mail_stat_detail
	348	+ WHERE detail_type = '.acym_escapeDB('open_time').$cond.'
	349	+ GROUP BY date_id',
	350	+ 'date_id'
	351	+ ),
	352	+ ];
253	353	}
254	354	}