← All changes | back/Classes/UserStatClass.php +0 -342 10.10.2 → 11.1.1 View file →

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