← All changes | back/Classes/MailStatClass.php +90 -1 10.11.1 → 11.1.0 View file →

		@@ -62,8 +62,9 @@
62	62	$onDuplicate[] = ' unsubscribe_total = unsubscribe_total + '.intval($mailStat['unsubscribe_total']);
63	63	}
64	64
65	65	if (isset($mailStat['tracking_sale'])) {
	66	+ // Cannot be changed as it is not guaranteed that the plugin is updated at the same time
66	67	$onDuplicate[] = 'tracking_sale = '.floatval($mailStat['tracking_sale']);
67	68	}
68	69
69	70	if (isset($mailStat['currency'])) {
		@@ -160,9 +161,9 @@
160	161	if (!empty($mailsIds)) {
161	162	$condMailIds = 'WHERE mail_id IN ('.implode(',', $mailsIds).')';
162	163	}
163	164
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	+ $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;
165	166
166	167	return acym_loadObject($query);
167	168	}
168	169
		@@ -260,6 +261,94 @@
260	261	{
261	262	$firstSentEmail = acym_loadObject('SELECT * FROM #__acym_mail_stat WHERE sent > 0');
262	263
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	+ ];
264	353	}
265	354	}