← All changes | back/Classes/UserStatClass.php +99 -82 10.2.2 → 11.1.0 View file →

		@@ -13,16 +13,15 @@
13	13
14	14	$this->table = 'user_stat';
15	15	}
16	16
17		- public function save($userStat, $overrideSendDate = false)
	17	+ public function save(object $element, bool $overrideSendDate = false): ?int
18	18	{
	19	+ $userStat = get_object_vars($element);
	20	+
19	21	$column = [];
20	22	$valueColumn = [];
21	23	$columnName = acym_getColumns('user_stat');
22		- if (![$userStat] || !is_array($userStat)) {
23		- $userStat = (array)$userStat;
24		- }
25	24
26	25	foreach ($userStat as $key => $value) {
27	26	if (in_array($key, $columnName)) {
28	27	$column[] = '`'.acym_secureDBColumn($key).'`';
		@@ -37,10 +36,10 @@
37	36
38	37	$query = 'INSERT INTO #__acym_user_stat ('.implode(',', $column).') VALUE ('.implode(', ', $valueColumn).')';
39	38	$onDuplicate = [];
40	39
41		- if (!empty($userStat['statusSending'])) {
42		- $onDuplicate[] = $userStat['statusSending'] == 0 ? 'fail = fail + 1' : 'sent = sent + 1';
	40	+ if (isset($userStat['statusSending'])) {
	41	+ $onDuplicate[] = empty($userStat['statusSending']) ? 'fail = fail + 1' : 'sent = sent + 1';
43	42	}
44	43
45	44	if ($overrideSendDate && !empty($userStat['send_date'])) {
46	45	$onDuplicate[] = 'send_date = '.acym_escapeDB($userStat['send_date']);
		@@ -47,13 +46,8 @@
47	46	}
48	47
49	48	if (!empty($userStat['open'])) {
50	49	$onDuplicate[] = 'open = open + 1';
51		- $automationClass = new AutomationClass();
52		- $automationClass->trigger('user_open', ['userId' => $userStat['user_id'], 'mailId' => $userStat['mail_id']]);
53		-
54		- $scenarioHelper = new ScenarioHelper();
55		- $scenarioHelper->trigger('user_open', ['userId' => $userStat['user_id'], 'mailId' => $userStat['mail_id']]);
56	50	}
57	51
58	52	if (!empty($userStat['open_date'])) {
59	53	$onDuplicate[] = 'open_date = '.acym_escapeDB($userStat['open_date']);
		@@ -83,20 +77,28 @@
83	77	$query .= ' ON DUPLICATE KEY UPDATE ';
84	78	$query .= implode(',', $onDuplicate);
85	79	}
86	80
87		- acym_query($query);
	81	+ return (int)acym_query($query);
88	82	}
89	83
90		- public function getOneByMailAndUserId($mail_id, $user_id)
	84	+ public static function triggerActivity(string $event, int $userId, int $mailId): void
91	85	{
92		- $query = 'SELECT * FROM #__acym_user_stat WHERE `mail_id` = '.intval($mail_id).' AND `user_id` = '.intval($user_id);
	86	+ $payload = ['userId' => $userId, 'mailId' => $mailId];
93	87
94		- return acym_loadObject($query);
	88	+ (new AutomationClass())->trigger($event, $payload);
	89	+ (new ScenarioHelper())->trigger($event, $payload);
95	90	}
96	91
97		- public function getDetailedStatistics(array $options)
	92	+ public function getOneByMailAndUserId(int $mailId, int $userId): ?object
98	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	+ {
99	101	$limit = $options['limit'] ?? 10;
100	102	$offset = $options['offset'] ?? 0;
101	103	$mailId = $options['mail_id'] ?? 0;
102	104
		@@ -110,13 +112,16 @@
110	112	$limit
111	113	);
112	114	}
113	115
114		- public function getAllUserStatByUserId($idUser)
	116	+ public function getAllUserStatByUserId(int $userId): array
115	117	{
116		- $query = 'SELECT * FROM #__acym_user_stat WHERE user_id = '.intval($idUser);
117		-
118		- return acym_loadObjectList($query);
	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	+ );
119	124	}
120	125
121	126	public function getDetailedStats(array $settings): array
122	127	{
		@@ -181,13 +186,15 @@
181	186
182	187	$totalClicksForMails = [];
183	188	$campaignsForMails = [];
184	189	foreach ($mails as $key => $mail) {
	190	+ // Add tracked income amount
185	191	if (!empty($trackingSales[$mail->mail_id.'-'.$mail->user_id])) {
186	192	acym_trigger('getCurrency', [&$mails[$key]->currency]);
187	193	$mails[$key]->sales = $trackingSales[$mail->mail_id.'-'.$mail->user_id]->sale;
188	194	}
189	195
	196	+ // Add total clicks
190	197	if (!isset($totalClicksForMails[$mail->mail_id])) {
191	198	$totalClicksForMails[$mail->mail_id] = acym_loadObjectList(
192	199	'SELECT SUM(click) AS nbClicks, user_id
193	200	FROM #__acym_url_click
		@@ -197,8 +204,9 @@
197	204	);
198	205	}
199	206	$mails[$key]->total_click = empty($totalClicksForMails[$mail->mail_id][$mail->user_id]) ? 0 : $totalClicksForMails[$mail->mail_id][$mail->user_id]->nbClicks;
200	207
	208	+ // Handle multilingual
201	209	$mainMailId = empty($mail->parent_id) ? $mail->mail_id : $mail->parent_id;
202	210	if (!isset($campaignsForMails[$mainMailId])) {
203	211	$campaignsForMails[$mainMailId] = acym_loadObject('SELECT id AS campaign_id, parent_id FROM #__acym_campaign WHERE mail_id = '.intval($mainMailId));
204	212	}
		@@ -213,51 +221,47 @@
213	221	'total' => acym_loadObject($queryCount),
214	222	];
215	223	}
216	224
217		- public function getTotalFailClickOpenByMailIds($mailIds)
	225	+ public function getOpenTimeStats(array $mailIds): array
218	226	{
219	227	acym_arrayToInteger($mailIds);
220		- if (empty($mailIds)) return [];
	228	+ $specificMailsCondition = empty($mailIds) ? '' : ' AND mail_id in ('.implode(',', $mailIds).')';
	229	+ $offsetSeconds = acym_getTimeOffsetCMS();
221	230
222		- $query = 'SELECT mail_id, SUM(fail) AS fail, SUM(sent) AS sent, SUM(open) AS open FROM #__acym_user_stat WHERE mail_id IN ('.implode(',', $mailIds).') GROUP BY mail_id';
223		-
224		- return acym_loadObjectList($query, 'mail_id');
	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	+ ];
225	253	}
226	254
227		- public function getUserWithNoMailOpen()
	255	+ public function getDefaultStat(): array
228	256	{
229		- $query = 'SELECT user_id FROM #__acym_user_stat GROUP BY user_id HAVING MAX(open) = 0';
230		-
231		- return acym_loadResultArray($query);
232		- }
233		-
234		- public function getOpenTimeStats($mailIds)
235		- {
236		-
237		- if (!is_array($mailIds)) $mailIds = [$mailIds];
238		- acym_arrayToInteger($mailIds);
239		- $where = empty($mailIds) ? '' : ' AND mail_id in ('.implode(',', $mailIds).')';
240		-
241		- $query = 'SELECT SUM(`open`) AS open_total, DATE_FORMAT(open_date, "%w") AS day, FORMAT(CONVERT(DATE_FORMAT(open_date, "%H"), SIGNED INTEGER) / 3, 0) AS hour, CONCAT(DATE_FORMAT(open_date, "%w"), "_", FORMAT(CONVERT(DATE_FORMAT(open_date, "%H"), SIGNED INTEGER) / 3, 0)) AS date_id FROM `#__acym_user_stat` WHERE open_date IS NOT NULL '.$where.' GROUP BY date_id';
242		-
243		- $return['total_open'] = acym_loadResult('SELECT SUM(`open`) FROM #__acym_user_stat WHERE open_date IS NOT NULL '.$where);
244		- $return['stats'] = acym_loadObjectList($query, 'date_id');
245		-
246		- return $return;
247		- }
248		-
249		- public function getDefaultStat()
250		- {
251	257	$percentageRemaining = 100;
252	258	$stats = [];
253	259
254		-
255		- for ($day = 0 ; $day < 7 ; $day++) {
	260	+ for ($day = 0; $day < 7; $day++) {
256	261	$stats[$day] = [];
257		- for ($hour = 0 ; $hour < 8 ; $hour++) {
258		- $hourPercentage = $this->getRandomStatOpenTime($percentageRemaining, $hour);
259		- $stats[$day][$hour] = $hourPercentage;
	262	+ for ($hour = 0; $hour < 8; $hour++) {
	263	+ $stats[$day][$hour] = $this->getRandomStatOpenTime($percentageRemaining, $hour);
260	264	}
261	265	}
262	266
263	267	return $stats;
		@@ -262,37 +266,10 @@
262	266
263	267	return $stats;
264	268	}
265	269
266		- private function getRandomStatOpenTime(&$percentageRemaining, $hour)
	270	+ public function getOpenSourcesStats(array $mailIds = []): array
267	271	{
268		- if (empty($percentageRemaining)) return 0;
269		- $randoms = [
270		- 0 => [0, 1],
271		- 1 => [0, 2],
272		- 2 => [1, 2],
273		- 3 => [1, 3],
274		- 4 => [3, 5],
275		- 5 => [1, 3],
276		- 6 => [1, 2],
277		- 7 => [0, 2],
278		- ];
279		-
280		- $percentage = rand($randoms[$hour][0], $randoms[$hour][1]);
281		-
282		- if ($percentageRemaining - $percentage < 0) return 0;
283		-
284		-
285		- $percentageRemaining -= $percentage;
286		-
287		- return $percentage;
288		- }
289		-
290		- public function getOpenSourcesStats($mailIds = [])
291		- {
292		- if (!is_array($mailIds)) {
293		- $mailIds = [$mailIds];
294		- }
295	272	acym_arrayToInteger($mailIds);
296	273
297	274	$query = 'SELECT opened_with, COUNT(*) AS number FROM #__acym_user_stat WHERE `open` > 0';
298	275	if (!empty($mailIds)) {
		@@ -314,9 +291,9 @@
314	291	if (empty($deleteAfterXSeconds)) {
315	292	return [];
316	293	}
317	294
318		- $date = acym_date(time() - $deleteAfterXSeconds, 'Y-m-d H:i');
	295	+ $date = acym_date(time() - $deleteAfterXSeconds, 'Y-m-d H:i', false);
319	296
320	297	$queries = [
321	298	'#__acym_user_stat' => 'DELETE FROM #__acym_user_stat WHERE send_date < '.acym_escapeDB($date),
322	299	'#__acym_url_click' => 'DELETE FROM #__acym_url_click WHERE date_click < '.acym_escapeDB($date),
		@@ -338,6 +315,46 @@
338	315	return [];
339	316	}
340	317
341	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);
342	359	}
343	360	}