acymailing / back / Classes / UserStatClass.php

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

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