← All changes | back/Classes/UserStatClass.php +360 -0 10.2.0 → 11.1.0 View file →

		@@ -1,0 +1,360 @@
	1	+<?php
	2	+
	3	+namespace AcyMailing\Classes;
	4	+
	5	+use AcyMailing\Helpers\ScenarioHelper;
	6	+use AcyMailing\Core\AcymClass;
	7	+
	8	+class UserStatClass extends AcymClass
	9	+{
	10	+ public function __construct()
	11	+ {
	12	+ parent::__construct();
	13	+
	14	+ $this->table = 'user_stat';
	15	+ }
	16	+
	17	+ public function save(object $element, bool $overrideSendDate = false): ?int
	18	+ {
	19	+ $userStat = get_object_vars($element);
	20	+
	21	+ $column = [];
	22	+ $valueColumn = [];
	23	+ $columnName = acym_getColumns('user_stat');
	24	+
	25	+ foreach ($userStat as $key => $value) {
	26	+ if (in_array($key, $columnName)) {
	27	+ $column[] = '`'.acym_secureDBColumn($key).'`';
	28	+
	29	+ if ($key === 'tracking_sale') {
	30	+ $valueColumn[] = strlen($value) === 0 ? 'NULL' : floatval($value);
	31	+ } else {
	32	+ $valueColumn[] = acym_escapeDB($value);
	33	+ }
	34	+ }
	35	+ }
	36	+
	37	+ $query = 'INSERT INTO #__acym_user_stat ('.implode(',', $column).') VALUE ('.implode(', ', $valueColumn).')';
	38	+ $onDuplicate = [];
	39	+
	40	+ if (isset($userStat['statusSending'])) {
	41	+ $onDuplicate[] = empty($userStat['statusSending']) ? 'fail = fail + 1' : 'sent = sent + 1';
	42	+ }
	43	+
	44	+ if ($overrideSendDate && !empty($userStat['send_date'])) {
	45	+ $onDuplicate[] = 'send_date = '.acym_escapeDB($userStat['send_date']);
	46	+ }
	47	+
	48	+ if (!empty($userStat['open'])) {
	49	+ $onDuplicate[] = 'open = open + 1';
	50	+ }
	51	+
	52	+ if (!empty($userStat['open_date'])) {
	53	+ $onDuplicate[] = 'open_date = '.acym_escapeDB($userStat['open_date']);
	54	+ }
	55	+
	56	+ if (!empty($userStat['tracking_sale'])) {
	57	+ $onDuplicate[] = 'tracking_sale = '.floatval($userStat['tracking_sale']);
	58	+ }
	59	+
	60	+ if (!empty($userStat['currency'])) {
	61	+ $onDuplicate[] = 'currency = '.acym_escapeDB($userStat['currency']);
	62	+ }
	63	+
	64	+ if (!empty($userStat['unsubscribe'])) {
	65	+ $onDuplicate[] = 'unsubscribe = '.intval($userStat['unsubscribe']);
	66	+ }
	67	+
	68	+ if (!empty($userStat['device'])) {
	69	+ $onDuplicate[] = 'device = '.acym_escapeDB($userStat['device']);
	70	+ }
	71	+
	72	+ if (!empty($userStat['opened_with'])) {
	73	+ $onDuplicate[] = 'opened_with = '.acym_escapeDB($userStat['opened_with']);
	74	+ }
	75	+
	76	+ if (!empty($onDuplicate)) {
	77	+ $query .= ' ON DUPLICATE KEY UPDATE ';
	78	+ $query .= implode(',', $onDuplicate);
	79	+ }
	80	+
	81	+ return (int)acym_query($query);
	82	+ }
	83	+
	84	+ public static function triggerActivity(string $event, int $userId, int $mailId): void
	85	+ {
	86	+ $payload = ['userId' => $userId, 'mailId' => $mailId];
	87	+
	88	+ (new AutomationClass())->trigger($event, $payload);
	89	+ (new ScenarioHelper())->trigger($event, $payload);
	90	+ }
	91	+
	92	+ public function getOneByMailAndUserId(int $mailId, int $userId): ?object
	93	+ {
	94	+ $userStat = acym_loadObject('SELECT * FROM #__acym_user_stat WHERE `mail_id` = '.intval($mailId).' AND `user_id` = '.intval($userId));
	95	+
	96	+ return empty($userStat) ? null : $userStat;
	97	+ }
	98	+
	99	+ public function getDetailedStatistics(array $options): array
	100	+ {
	101	+ $limit = $options['limit'] ?? 10;
	102	+ $offset = $options['offset'] ?? 0;
	103	+ $mailId = $options['mail_id'] ?? 0;
	104	+
	105	+ return acym_loadObjectList(
	106	+ 'SELECT user_stat.*, `user`.email
	107	+ FROM #__acym_user_stat AS user_stat
	108	+ JOIN #__acym_user AS `user` ON user_stat.user_id = `user`.id
	109	+ WHERE `mail_id` = '.intval($mailId),
	110	+ '',
	111	+ $offset,
	112	+ $limit
	113	+ );
	114	+ }
	115	+
	116	+ public function getAllUserStatByUserId(int $userId): array
	117	+ {
	118	+ return acym_loadObjectList(
	119	+ 'SELECT userStat.*, mail.subject
	120	+ FROM #__acym_user_stat AS userStat
	121	+ JOIN #__acym_mail AS mail ON mail.id = userStat.mail_id
	122	+ WHERE userStat.user_id = '.intval($userId)
	123	+ );
	124	+ }
	125	+
	126	+ public function getDetailedStats(array $settings): array
	127	+ {
	128	+ $mailClass = new MailClass();
	129	+
	130	+ $query = 'SELECT us.*, m.name, m.subject, m.parent_id, u.email, u.name AS username
	131	+ FROM #__acym_user_stat AS us
	132	+ LEFT JOIN #__acym_user AS u ON us.user_id = u.id
	133	+ INNER JOIN #__acym_mail AS m ON us.mail_id = m.id';
	134	+ $queryCount = 'SELECT COUNT(*) AS total FROM #__acym_user_stat as us
	135	+ LEFT JOIN #__acym_user AS u ON us.user_id = u.id
	136	+ INNER JOIN #__acym_mail AS m ON us.mail_id = m.id';
	137	+ $where = [];
	138	+
	139	+ if (!empty($settings['mail_ids'])) {
	140	+ if (!is_array($settings['mail_ids'])) $settings['mail_ids'] = [$settings['mail_ids']];
	141	+ acym_arrayToInteger($settings['mail_ids']);
	142	+ $where[] = 'us.mail_id IN ('.implode(',', $settings['mail_ids']).')';
	143	+ }
	144	+
	145	+ if (!empty($settings['search'])) {
	146	+ $searchTerms = acym_escapeDB('%'.$settings['search'].'%');
	147	+ $where[] = 'm.name LIKE '.$searchTerms.' OR u.email LIKE '.$searchTerms.' OR u.name LIKE '.$searchTerms;
	148	+ }
	149	+
	150	+ if (!empty($where)) {
	151	+ $query .= ' WHERE ('.implode(') AND (', $where).')';
	152	+ $queryCount .= ' WHERE ('.implode(') AND (', $where).')';
	153	+ }
	154	+
	155	+ if (!empty($settings['ordering']) && !empty($settings['ordering_sort_order'])) {
	156	+ if (in_array($settings['ordering'], ['email', 'name'])) {
	157	+ $table = 'u';
	158	+ } elseif ($settings['ordering'] === 'subject') {
	159	+ $table = 'm';
	160	+ } else {
	161	+ $table = 'us';
	162	+ }
	163	+ $query .= ' ORDER BY '.$table.'.'.acym_secureDBColumn($settings['ordering']).' '.acym_secureDBColumn(strtoupper($settings['ordering_sort_order'])).', us.user_id ASC';
	164	+ }
	165	+
	166	+ $mails = acym_loadObjectList($query, '', $settings['offset'], $settings['detailedStatsPerPage']);
	167	+
	168	+ if (!empty($mails)) {
	169	+ if (acym_isTrackingSalesActive()) {
	170	+ $mailIds = [];
	171	+ foreach ($mails as $mail) {
	172	+ if (!in_array($mail->mail_id, $mailIds)) $mailIds[] = $mail->mail_id;
	173	+ }
	174	+
	175	+ $trackingSales = [];
	176	+ $trackingSalesDB = acym_loadObjectList(
	177	+ 'SELECT SUM(tracking_sale) as sale, currency, mail_id, user_id FROM #__acym_user_stat WHERE mail_id IN ('.implode(
	178	+ ',',
	179	+ $mailIds
	180	+ ).') AND currency IS NOT NULL GROUP BY user_id, mail_id'
	181	+ );
	182	+ foreach ($trackingSalesDB as $oneMailTrackSales) {
	183	+ $trackingSales[$oneMailTrackSales->mail_id.'-'.$oneMailTrackSales->user_id] = $oneMailTrackSales;
	184	+ }
	185	+ }
	186	+
	187	+ $totalClicksForMails = [];
	188	+ $campaignsForMails = [];
	189	+ foreach ($mails as $key => $mail) {
	190	+ // Add tracked income amount
	191	+ if (!empty($trackingSales[$mail->mail_id.'-'.$mail->user_id])) {
	192	+ acym_trigger('getCurrency', [&$mails[$key]->currency]);
	193	+ $mails[$key]->sales = $trackingSales[$mail->mail_id.'-'.$mail->user_id]->sale;
	194	+ }
	195	+
	196	+ // Add total clicks
	197	+ if (!isset($totalClicksForMails[$mail->mail_id])) {
	198	+ $totalClicksForMails[$mail->mail_id] = acym_loadObjectList(
	199	+ 'SELECT SUM(click) AS nbClicks, user_id
	200	+ FROM #__acym_url_click
	201	+ WHERE mail_id = '.intval($mail->mail_id).'
	202	+ GROUP BY user_id',
	203	+ 'user_id'
	204	+ );
	205	+ }
	206	+ $mails[$key]->total_click = empty($totalClicksForMails[$mail->mail_id][$mail->user_id]) ? 0 : $totalClicksForMails[$mail->mail_id][$mail->user_id]->nbClicks;
	207	+
	208	+ // Handle multilingual
	209	+ $mainMailId = empty($mail->parent_id) ? $mail->mail_id : $mail->parent_id;
	210	+ if (!isset($campaignsForMails[$mainMailId])) {
	211	+ $campaignsForMails[$mainMailId] = acym_loadObject('SELECT id AS campaign_id, parent_id FROM #__acym_campaign WHERE mail_id = '.intval($mainMailId));
	212	+ }
	213	+
	214	+ $mails[$key]->campaign_id = empty($campaignsForMails[$mainMailId]) ? 0 : $campaignsForMails[$mainMailId]->campaign_id;
	215	+ $mails[$key]->parent_id = empty($campaignsForMails[$mainMailId]) ? 0 : $campaignsForMails[$mainMailId]->parent_id;
	216	+ }
	217	+ }
	218	+
	219	+ return [
	220	+ 'detailed_stats' => $mailClass->decode($mails),
	221	+ 'total' => acym_loadObject($queryCount),
	222	+ ];
	223	+ }
	224	+
	225	+ public function getOpenTimeStats(array $mailIds): array
	226	+ {
	227	+ acym_arrayToInteger($mailIds);
	228	+ $specificMailsCondition = empty($mailIds) ? '' : ' AND mail_id in ('.implode(',', $mailIds).')';
	229	+ $offsetSeconds = acym_getTimeOffsetCMS();
	230	+
	231	+ return [
	232	+ 'total_open' => acym_loadResult(
	233	+ 'SELECT SUM(`open`)
	234	+ FROM #__acym_user_stat
	235	+ WHERE open_date IS NOT NULL '.$specificMailsCondition
	236	+ ),
	237	+ 'stats' => acym_loadObjectList(
	238	+ 'SELECT
	239	+ SUM(`open`) AS `open_total`,
	240	+ DATE_FORMAT(DATE_ADD(open_date, INTERVAL '.$offsetSeconds.' SECOND), "%w") AS `day`,
	241	+ FORMAT(CONVERT(DATE_FORMAT(DATE_ADD(open_date, INTERVAL '.$offsetSeconds.' SECOND), "%H"), SIGNED INTEGER) / 3, 0) AS `hour`,
	242	+ CONCAT(
	243	+ DATE_FORMAT(DATE_ADD(open_date, INTERVAL '.$offsetSeconds.' SECOND), "%w"),
	244	+ "_",
	245	+ FORMAT(CONVERT(DATE_FORMAT(DATE_ADD(open_date, INTERVAL '.$offsetSeconds.' SECOND), "%H"), SIGNED INTEGER) / 3, 0)
	246	+ ) AS `date_id`
	247	+ FROM `#__acym_user_stat`
	248	+ WHERE open_date IS NOT NULL '.$specificMailsCondition.'
	249	+ GROUP BY date_id',
	250	+ 'date_id'
	251	+ ),
	252	+ ];
	253	+ }
	254	+
	255	+ public function getDefaultStat(): array
	256	+ {
	257	+ $percentageRemaining = 100;
	258	+ $stats = [];
	259	+
	260	+ for ($day = 0; $day < 7; $day++) {
	261	+ $stats[$day] = [];
	262	+ for ($hour = 0; $hour < 8; $hour++) {
	263	+ $stats[$day][$hour] = $this->getRandomStatOpenTime($percentageRemaining, $hour);
	264	+ }
	265	+ }
	266	+
	267	+ return $stats;
	268	+ }
	269	+
	270	+ public function getOpenSourcesStats(array $mailIds = []): array
	271	+ {
	272	+ acym_arrayToInteger($mailIds);
	273	+
	274	+ $query = 'SELECT opened_with, COUNT(*) AS number FROM #__acym_user_stat WHERE `open` > 0';
	275	+ if (!empty($mailIds)) {
	276	+ $query .= ' AND mail_id IN ('.implode(',', $mailIds).')';
	277	+ }
	278	+ $query .= ' GROUP BY opened_with';
	279	+
	280	+ return acym_loadObjectList($query);
	281	+ }
	282	+
	283	+ public function deleteDetailedStatsPeriod(): array
	284	+ {
	285	+ $shouldClearExpiredStats = $this->config->get('delete_stats_enabled', 0);
	286	+ if (empty($shouldClearExpiredStats)) {
	287	+ return [];
	288	+ }
	289	+
	290	+ $deleteAfterXSeconds = $this->config->get('delete_stats', 86400 * 360);
	291	+ if (empty($deleteAfterXSeconds)) {
	292	+ return [];
	293	+ }
	294	+
	295	+ $date = acym_date(time() - $deleteAfterXSeconds, 'Y-m-d H:i', false);
	296	+
	297	+ $queries = [
	298	+ '#__acym_user_stat' => 'DELETE FROM #__acym_user_stat WHERE send_date < '.acym_escapeDB($date),
	299	+ '#__acym_url_click' => 'DELETE FROM #__acym_url_click WHERE date_click < '.acym_escapeDB($date),
	300	+ ];
	301	+
	302	+ $messages = [];
	303	+ foreach ($queries as $table => $query) {
	304	+ try {
	305	+ $status = acym_query($query);
	306	+ if (!empty($status)) {
	307	+ $messages[] = acym_translationSprintf('ACYM_DELETE_X_ROWS_TABLE_X', $status, acym_translation('ACYM_USER_DETAILED_STATS').' ('.$table.')');
	308	+ }
	309	+ } catch (\Exception $e) {
	310	+ $messages[] = $e->getMessage();
	311	+ }
	312	+ }
	313	+
	314	+ if (empty($messages)) {
	315	+ return [];
	316	+ }
	317	+
	318	+ return ['message' => implode("\r\n", $messages)];
	319	+ }
	320	+
	321	+ private function getRandomStatOpenTime(int &$percentageRemaining, int $hour): int
	322	+ {
	323	+ if (empty($percentageRemaining)) {
	324	+ return 0;
	325	+ }
	326	+
	327	+ $randoms = [
	328	+ 0 => [0, 1],
	329	+ 1 => [0, 2],
	330	+ 2 => [1, 2],
	331	+ 3 => [1, 3],
	332	+ 4 => [3, 5],
	333	+ 5 => [1, 3],
	334	+ 6 => [1, 2],
	335	+ 7 => [0, 2],
	336	+ ];
	337	+
	338	+ $percentage = acym_rand($randoms[$hour][0], $randoms[$hour][1]);
	339	+
	340	+ if ($percentageRemaining - $percentage < 0) {
	341	+ return 0;
	342	+ }
	343	+
	344	+ $percentageRemaining -= $percentage;
	345	+
	346	+ return $percentage;
	347	+ }
	348	+
	349	+ public function hasUserReceivedMail(int $mailId, int $userId): bool
	350	+ {
	351	+ $sentToRecipient = acym_loadResult(
	352	+ 'SELECT COUNT(*)
	353	+ FROM #__acym_user_stat AS userStat
	354	+ WHERE userStat.mail_id = '.intval($mailId).'
	355	+ AND userStat.user_id = '.intval($userId)
	356	+ );
	357	+
	358	+ return !empty($sentToRecipient);
	359	+ }
	360	+}