acymailing / back / Classes / UserStatClass.php

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

361 lines 12.9 KB

Raw Download Zip

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	}
361