← All changes | back/Helpers/ImportHelper.php +164 -61 10.11.1 → 11.1.0 View file →

		@@ -7,8 +7,9 @@
7	7	use AcyMailing\Core\AcymObject;
8	8
9	9	class ImportHelper extends AcymObject
10	10	{
	11	+ //Handle messages to say that X users have been subscribed to list Y
11	12	private array $subscribedUsers = [];
12	13	private array $importUserInLists = [];
13	14	private array $allUserIds = [];
14	15	private array $columns = [];
		@@ -20,8 +21,9 @@
20	21	private bool $overwrite = false;
21	22	private string $header = '';
22	23	private string $separator = ',';
23	24
	25	+ //Variables used to handle the import on filter
24	26	public string $tableName = '';
25	27	public array $dbWhere = [];
26	28	public array $fieldsMap = [];
27	29	public array $subscriptionHistory = [];
		@@ -29,13 +31,15 @@
29	31
30	32	public function __construct()
31	33	{
32	34	parent::__construct();
	35	+ //We do an import... so it might take a lot of times and it also might take some memory... so we increase those limits if we can!
33	36	acym_increasePerf();
34	37	}
35	38
36	39	public function file(): bool
37	40	{
	41	+ //Step 1 : we copy the file in the correct directory
38	42	$importFile = acym_getVar('array', 'import_file', [], 'files');
39	43
40	44	if (empty($importFile['name'])) {
41	45	acym_enqueueMessage(acym_translation('ACYM_PLEASE_BROWSE_FILE_IMPORT'), 'error');
		@@ -44,14 +48,15 @@
44	48	}
45	49
46	50	$extension = strtolower(acym_fileGetExt($importFile['name']));
47	51
48		- if (!preg_match('#^(csv)$#Ui', $extension) || preg_match('#\.(php.?|.?htm.?|pl|py|jsp|asp|sh|cgi)$#Ui', $importFile['name'])) {
49		- acym_enqueueMessage(acym_translationSprintf('ACYM_ACCEPTED_TYPE', acym_escape($extension), $this->config->get('allowed_files')), 'error');
	52	+ if (!preg_match('#^(csv)$#Ui', $extension) || acym_isForbiddenFileExtension($importFile['name'])) {
	53	+ acym_enqueueMessage(acym_translationSprintf('ACYM_ACCEPTED_TYPE', esc_html($extension), $this->config->get('allowed_files')), 'error');
50	54
51	55	return false;
52	56	}
53	57
	58	+ // Checking error upload
54	59	$fileError = $importFile['error'];
55	60	if ($fileError > 0) {
56	61	switch ($fileError) {
57	62	case 1:
		@@ -79,18 +84,16 @@
79	84	$filename = uniqid('import_').'.csv';
80	85	acym_setVar('acym_import_filename', $filename);
81	86
82	87	if (!acym_uploadFile($importFile['tmp_name'], $uploadPath.$filename)) {
83		- if (!move_uploaded_file($importFile['tmp_name'], $uploadPath.$filename)) {
84		- acym_enqueueMessage(
85		- acym_translationSprintf(
86		- 'ACYM_FAIL_UPLOAD',
87		- '<b><i>'.acym_escape($importFile['tmp_name']).'</i></b>',
88		- '<b><i>'.acym_escape($uploadPath.$filename).'</i></b>'
89		- ),
90		- 'error'
91		- );
92		- }
	88	+ acym_enqueueMessage(
	89	+ acym_translationSprintf(
	90	+ 'ACYM_FAIL_UPLOAD',
	91	+ '<b><i>'.esc_html($importFile['tmp_name']).'</i></b>',
	92	+ '<b><i>'.esc_html($uploadPath.$filename).'</i></b>'
	93	+ ),
	94	+ 'error'
	95	+ );
93	96	}
94	97
95	98	return true;
96	99	}
		@@ -110,11 +113,14 @@
110	113	public function cms(): bool
111	114	{
112	115	global $acymCmsUserVars;
113	116
114		- $query = 'UPDATE IGNORE '.$acymCmsUserVars->table.' as b, #__acym_user as a SET a.email = b.'.$acymCmsUserVars->email.', a.name = b.'.$acymCmsUserVars->name.', a.active = 1 - b.'.$acymCmsUserVars->blocked.' WHERE a.cms_id = b.'.$acymCmsUserVars->id.' AND a.cms_id IS NOT NULL';
	117	+ //Update the users which already have a userid
	118	+ $query = 'UPDATE IGNORE '.$acymCmsUserVars->table.' as b, #__acym_user as a SET a.email = LOWER(b.'.$acymCmsUserVars->email.'), a.name = b.'.$acymCmsUserVars->name.', a.active = 1 - b.'.$acymCmsUserVars->blocked.' WHERE a.cms_id = b.'.$acymCmsUserVars->id.' AND a.cms_id IS NOT NULL';
115	119	$nbUpdated = acym_query($query);
116	120
	121	+ //Step 1 : update the existing ones.
	122	+ //Update the users to give them a userid
117	123	$query = 'UPDATE IGNORE '.$acymCmsUserVars->table.' as b, #__acym_user as a SET a.cms_id = b.'.$acymCmsUserVars->id.' WHERE a.email = b.'.$acymCmsUserVars->email;
118	124	$affected = acym_query($query);
119	125	$nbUpdated += intval($affected);
120	126
		@@ -119,8 +125,9 @@
119	125	$nbUpdated += intval($affected);
120	126
121	127	acym_enqueueMessage(acym_translationSprintf('ACYM_IMPORT_UPDATE', $nbUpdated), 'success');
122	128
	129	+ //Step 2 : delete the ones which don't exist any more
123	130	$query = 'SELECT a.id FROM #__acym_user as a LEFT JOIN '.$acymCmsUserVars->table.' as b on a.cms_id = b.'.$acymCmsUserVars->id.' WHERE b.'.$acymCmsUserVars->id.' IS NULL AND a.cms_id > 0';
124	131	$deletedSubid = acym_loadResultArray($query);
125	132
126	133	$query = 'SELECT a.id FROM #__acym_user as a LEFT JOIN '.$acymCmsUserVars->table.' as b on a.email = b.'.$acymCmsUserVars->email.' WHERE b.'.$acymCmsUserVars->id.' IS NULL AND a.cms_id > 0';
		@@ -131,12 +138,13 @@
131	138	$deletedUsers = $userClass->delete($deletedSubid);
132	139	acym_enqueueMessage(acym_translationSprintf('ACYM_IMPORT_DELETE', $deletedUsers));
133	140	}
134	141
	142	+ //Step 3 : insert the new ones
135	143	$time = time();
136	144	$formattedTime = acym_date($time, 'Y-m-d H:i:s');
137	145	$sourceImport = 'Import on '.$formattedTime;
138		- $query = 'INSERT IGNORE INTO #__acym_user (`name`,`email`,`creation_date`,`active`,`cms_id`, `source`) SELECT `user`.`'.$acymCmsUserVars->name.'`,`user`.`'.$acymCmsUserVars->email.'`,`user`.`'.$acymCmsUserVars->registered.'`,1 - `user`.'.$acymCmsUserVars->blocked.',`user`.`'.$acymCmsUserVars->id.'`,\''.$sourceImport.'\' FROM '.$acymCmsUserVars->table.' AS `user` ';
	146	+ $query = 'INSERT IGNORE INTO #__acym_user (`name`,`email`,`creation_date`,`active`,`cms_id`, `source`) SELECT `user`.`'.$acymCmsUserVars->name.'`,LOWER(`user`.`'.$acymCmsUserVars->email.'`),`user`.`'.$acymCmsUserVars->registered.'`,1 - `user`.'.$acymCmsUserVars->blocked.',`user`.`'.$acymCmsUserVars->id.'`,\''.$sourceImport.'\' FROM '.$acymCmsUserVars->table.' AS `user` ';
139	147
140	148	$queryJoin = [];
141	149	$queryWhere = [];
142	150	if (ACYM_CMS === 'wordpress' && is_multisite()) {
		@@ -174,8 +182,9 @@
174	182	acym_trigger('onAcymAfterCMSUserImport', [$sourceImport]);
175	183
176	184	acym_enqueueMessage(acym_translationSprintf('ACYM_IMPORT_NEW_SUBS', $insertedUsers), 'info');
177	185
	186	+ //Step 4 : subscribe all the registered users to one or several lists
178	187	$lists = $this->getImportedLists();
179	188	$listsSubscribe = [];
180	189	if (!empty($lists)) {
181	190	foreach ($lists as $listid => $val) {
		@@ -189,9 +198,9 @@
189	198	return true;
190	199	}
191	200
192	201	$query = 'INSERT IGNORE INTO #__acym_user_has_list (`user_id`,`list_id`,`status`,`subscription_date`) ';
193		- $query .= 'SELECT user.`id`, list.`id`, 1, '.acym_escapeDB(date('Y-m-d H:i:s', time() - date('Z'))).'
	202	+ $query .= 'SELECT user.`id`, list.`id`, 1, '.acym_escapeDB(gmdate('Y-m-d H:i:s', time())).'
194	203	FROM #__acym_list AS list, #__acym_user AS user ';
195	204	$conditions = [];
196	205	$conditions[] = 'list.`id` IN ('.implode(',', $listsSubscribe).')';
197	206	$conditions[] = 'user.`cms_id` > 0';
		@@ -201,9 +210,9 @@
201	210	$query .= ' JOIN #__user_usergroup_map AS `map` ON map.user_id = `user`.`cms_id`';
202	211	$conditions[] = '`map`.`group_id` IN ('.implode(', ', $groups).')';
203	212	} else {
204	213	$query .= ' JOIN #__usermeta AS `meta` ON meta.user_id = `user`.`cms_id` AND `meta`.`meta_key` = "#__capabilities"';
205		- $conditions[] = '`meta`.`meta_value` LIKE ('.implode(' OR `meta`.`meta_value` LIKE ', $groups).')';
	214	+ $conditions[] = '(`meta`.`meta_value` LIKE '.implode(' OR `meta`.`meta_value` LIKE ', $groups).')';
206	215	}
207	216	}
208	217
209	218	$query .= ' WHERE '.implode(' AND ', $conditions);
		@@ -227,15 +236,18 @@
227	236
228	237	return false;
229	238	}
230	239
	240	+ //We got a table... let's load the fields from this table
231	241	$fields = acym_getColumns($table, false, false);
232	242	if (empty($fields)) {
	243	+ //there is no field... so we consider it was the wrong table
233	244	acym_enqueueMessage(acym_translation('ACYM_SPECIFYTABLE'), 'warning');
234	245
235	246	return false;
236	247	}
237	248
	249	+ //Ok now we have some fields and now we have a table selected.
238	250	$equivalentFields = empty($this->fieldsMap) ? acym_getVar('array', 'fields', []) : $this->fieldsMap;
239	251
240	252	if (empty($equivalentFields['email'])) {
241	253	acym_enqueueMessage(acym_translation('ACYM_SPECIFYFIELDEMAIL'), 'warning');
		@@ -243,8 +255,9 @@
243	255	return false;
244	256	}
245	257
246	258	$select = [];
	259	+ //We check the data and create the query
247	260	foreach ($equivalentFields as $acyField => $tableField) {
248	261	$tableField = trim($tableField);
249	262	if (empty($tableField)) {
250	263	continue;
		@@ -267,10 +280,13 @@
267	280
268	281	$sourceTxt = 'Import on '.$formattedTime;
269	282	$select['`source`'] = acym_escapeDB($sourceTxt);
270	283
	284	+ $emailColumn = acym_secureDBColumn($select['`email`']);
	285	+ $select['`email`'] = 'LOWER('.$emailColumn.')';
	286	+
271	287	$query = 'INSERT IGNORE INTO #__acym_user ('.implode(' , ', array_keys($select)).') SELECT '.implode(' , ', $select);
272		- $query .= ' FROM '.acym_secureDBColumn($table).' WHERE '.acym_secureDBColumn($select['`email`']).' LIKE "%@%"';
	288	+ $query .= ' FROM '.acym_secureDBColumn($table).' WHERE '.$emailColumn.' LIKE "%@%"';
273	289	if (!empty($this->dbWhere)) {
274	290	$query .= ' AND ( '.implode(' ) AND (', $this->dbWhere).' )';
275	291	}
276	292
		@@ -286,8 +302,9 @@
286	302
287	303	acym_enqueueMessage(acym_translationSprintf('ACYM_IMPORT_NEW_SUBS', $affectedRows), 'info');
288	304
289	305
	306	+ //Step 4 : subscribe all the registered users to one or several lists
290	307	$lists = $this->getImportedLists();
291	308	$listsSubscribe = [];
292	309	if (!empty($lists)) {
293	310	foreach ($lists as $listid => $val) {
		@@ -301,12 +318,12 @@
301	318	return true;
302	319	}
303	320
304	321	$query = 'INSERT IGNORE INTO #__acym_user_has_list (`user_id`,`list_id`,`status`,`subscription_date`) ';
305		- $query .= 'SELECT user.`id`, list.`id`, 1, '.acym_escapeDB(date('Y-m-d H:i:s', time() - date('Z'))).'
	322	+ $query .= 'SELECT user.`id`, list.`id`, 1, '.acym_escapeDB(gmdate('Y-m-d H:i:s', time())).'
306	323	FROM #__acym_list AS list, #__acym_user AS user
307	324	WHERE list.`id` IN ('.implode(',', $listsSubscribe).')
308		- AND user.`email` IN (SELECT '.acym_secureDBColumn($select['`email`']).' FROM '.acym_secureDBColumn($table).')';
	325	+ AND user.`email` IN (SELECT LOWER('.$emailColumn.') FROM '.acym_secureDBColumn($table).')';
309	326
310	327	$nbsubscribed = acym_query($query);
311	328	acym_enqueueMessage(acym_translationSprintf('ACYM_IMPORT_SUBSCRIPTION', $nbsubscribed));
312	329
		@@ -314,8 +331,9 @@
314	331	}
315	332
316	333	public function mailpoet(): bool
317	334	{
	335	+ // Step 1 : import subscriber from mailpoet
318	336	$time = time();
319	337	$formattedTime = acym_date($time, 'Y-m-d H:i:s');
320	338	$sourceImport = 'import_on_'.$formattedTime.'_mailpoet';
321	339
		@@ -320,9 +338,9 @@
320	338	$sourceImport = 'import_on_'.$formattedTime.'_mailpoet';
321	339
322	340	$query = 'INSERT IGNORE INTO #__acym_user (`name`,`email`,`creation_date`,`active`, `confirmed`,`cms_id`, `source`)
323	341	SELECT CONCAT(`subscriber`.`first_name`, `subscriber`.`last_name`),
324		- `subscriber`.`email`,
	342	+ LOWER(`subscriber`.`email`),
325	343	`subscriber`.`created_at`,
326	344	IF(`subscriber`.`status` = "inactive", 0, 1),
327	345	IF(`subscriber`.`status` = "subscribed", 1, 0),
328	346	`subscriber`.`wp_user_id`,
		@@ -343,8 +361,9 @@
343	361	acym_query('UPDATE #__acym_configuration SET `value` = '.intval($time).' WHERE `name` = \'last_import\'');
344	362
345	363	acym_enqueueMessage(acym_translationSprintf('ACYM_IMPORT_NEW_SUBS', $insertedUsers), 'info');
346	364
	365	+ // Step 2 : subscribe all the registered users to one or several lists
347	366	$lists = $this->getImportedLists();
348	367	$listsSubscribe = [];
349	368	if (!empty($lists)) {
350	369	foreach ($lists as $listid => $val) {
		@@ -358,9 +377,9 @@
358	377	return true;
359	378	}
360	379
361	380	$query = 'INSERT IGNORE INTO #__acym_user_has_list (`user_id`,`list_id`,`status`,`subscription_date`) ';
362		- $query .= 'SELECT user.`id`, list.`id`, 1, '.acym_escapeDB(date('Y-m-d H:i:s', time() - date('Z'))).'
	381	+ $query .= 'SELECT user.`id`, list.`id`, 1, '.acym_escapeDB(gmdate('Y-m-d H:i:s', time())).'
363	382	FROM #__acym_list AS list, #__acym_user AS user ';
364	383	$conditions = [];
365	384	$conditions[] = 'list.`id` IN ('.implode(',', $listsSubscribe).')';
366	385	$conditions[] = 'user.`source` = '.acym_escapeDB($sourceImport);
		@@ -387,8 +406,9 @@
387	406	$this->forceConfirm = acym_getVar('bool', 'import_confirmed_generic', true);
388	407	$this->generateName = acym_getVar('bool', 'import_generate_generic', true);
389	408	$this->overwrite = acym_getVar('bool', 'import_overwrite_generic', true);
390	409
	410	+ // Remember user's choices
391	411	$this->config->saveConfig(
392	412	[
393	413	'import_confirmed' => $this->forceConfirm,
394	414	'import_generate' => $this->generateName,
		@@ -413,8 +433,9 @@
413	433	}
414	434
415	435	$contentFile = file_get_contents($uploadPath);
416	436
	437	+ //We convert into the correct charset
417	438	if (acym_getVar('cmd', 'acyencoding', '') != '') {
418	439	$encodingHelper = new EncodingHelper();
419	440	$contentFile = $encodingHelper->change($contentFile, acym_getVar('cmd', 'acyencoding'), 'UTF-8');
420	441	}
		@@ -445,9 +466,10 @@
445	466	}
446	467
447	468	$this->handleContent($contentFile);
448	469
449		- unlink($uploadPath);
	470	+ //We can now delete the file
	471	+ acym_deleteFile($uploadPath);
450	472	$this->cleanImportFolder();
451	473
452	474	return true;
453	475	}
		@@ -482,34 +504,41 @@
482	504
483	505	return $lists;
484	506	}
485	507
486		- public function additionalDataUsersImport(bool $isGeneric): string
	508	+ public function displayAdditionalDataUsersImport(bool $isGeneric): void
487	509	{
488	510	$buttonAddListId = $isGeneric ? 'acym__users__generic__import__create-list__button' : 'acym__users__import__create-list__button';
489	511	$buttonImportClass = $isGeneric ? 'acym__users__import__generic__import__button' : 'acym__users__import__button';
490	512	$buttonSkipId = $isGeneric ? 'acym__users__generic__import__skip__button' : 'acym__users__import__skip__button';
491	513	$buttonImportDataTask = $isGeneric ? 'listing' : '';
492		-
493		- return '<div class="cell align-right grid-x margin-bottom-2">
494		- <div id="acym__users__import__create-list" class="grid-x" style="display: none;">
495		- <label for="acym__users__import__create-list__field" class="margin-right-1 acym_vcenter">'.acym_translation('ACYM_LIST_NAME').' : </label>
496		- <div>
497		- <input id="acym__users__import__create-list__field" type="text">
498		- </div>
499		- </div>
500		- <button type="button" class="button button-secondary margin-left-1 acym_vcenter margin-right-2" id="'.$buttonAddListId.'">'.acym_translation(
501		- 'ACYM_CREATE_NEW_LIST'
502		- ).'</button>
503		- <i style="display: none;" class="acym_vcenter acymicon-circle-o-notch acymicon-spin" id="acym__users__import__create-list__loading-logo"></i>
504		- </div>
505		- <div class="cell align-right grid-x">
506		- <button type="button" class="button-secondary button cell shrink margin-right-1" id="'.$buttonSkipId.'">'.acym_translation('ACYM_SKIP').'</button>
507		- <button
508		- type="button"
509		- class="button-primary button acy_button_submit cell shrink margin-right-2 '.$buttonImportClass.'"
510		- id="acym__entity_select__button__submit" data-task="'.$buttonImportDataTask.'">'.acym_translation('ACYM_SUBSCRIBE_USERS_TO_THESE_LISTS').'</button>
511		- </div>';
	514	+ ?>
	515	+ <div class="cell align-right grid-x margin-bottom-2">
	516	+ <div id="acym__users__import__create-list" class="grid-x" style="display: none;">
	517	+ <label for="acym__users__import__create-list__field" class="margin-right-1 acym_vcenter">
	518	+ <?php echo esc_html(acym_translation('ACYM_LIST_NAME')); ?> :
	519	+ </label>
	520	+ <div>
	521	+ <input id="acym__users__import__create-list__field" type="text">
	522	+ </div>
	523	+ </div>
	524	+ <button type="button" class="button button-secondary margin-left-1 acym_vcenter margin-right-2" id="<?php echo esc_attr($buttonAddListId); ?>">
	525	+ <?php echo esc_html(acym_translation('ACYM_CREATE_NEW_LIST')); ?>
	526	+ </button>
	527	+ <i style="display: none;" class="acym_vcenter acymicon-circle-o-notch acymicon-spin" id="acym__users__import__create-list__loading-logo"></i>
	528	+ </div>
	529	+ <div class="cell align-right grid-x">
	530	+ <button type="button" class="button-secondary button cell shrink margin-right-1" id="<?php echo esc_attr($buttonSkipId); ?>">
	531	+ <?php echo esc_html(acym_translation('ACYM_SKIP')); ?>
	532	+ </button>
	533	+ <button
	534	+ type="button"
	535	+ class="button-primary button acy_button_submit cell shrink margin-right-2 <?php echo esc_attr($buttonImportClass); ?>"
	536	+ id="acym__entity_select__button__submit" data-task="<?php echo esc_attr($buttonImportDataTask); ?>">
	537	+ <?php echo esc_html(acym_translation('ACYM_SUBSCRIBE_USERS_TO_THESE_LISTS')); ?>
	538	+ </button>
	539	+ </div>
	540	+ <?php
512	541	}
513	542
514	543	private function createUploadFolder(): string
515	544	{
		@@ -520,11 +549,11 @@
520	549	if (!is_dir($folderPath)) {
521	550	acym_createDir($folderPath, true, true);
522	551	}
523	552
524		- if (!is_writable($folderPath)) {
525		- @chmod($folderPath, '0755');
526		- if (!is_writable($folderPath)) {
	553	+ if (!acym_isWritable($folderPath)) {
	554	+ acym_chmod($folderPath, 0755);
	555	+ if (!acym_isWritable($folderPath)) {
527	556	acym_enqueueMessage(acym_translationSprintf('ACYM_WRITABLE_FOLDER', $folderPath), 'warning');
528	557	}
529	558	}
530	559
		@@ -534,11 +563,13 @@
534	563	private function handleContent(string $contentFile): void
535	564	{
536	565	$timestamp = time();
537	566
	567	+ //We convert the file into something valid so that mac or not will be Ok!
538	568	$contentFile = str_replace(["\r\n", "\r"], "\n", $contentFile);
539	569	$importLines = explode("\n", $contentFile);
540	570
	571	+ //A little trick to take the second line if the first line is empty...
541	572	$i = 0;
542	573	$this->header = '';
543	574	$this->allUserIds = [];
544	575	while (empty($this->header) && $i < 10) {
		@@ -546,14 +577,17 @@
546	577	$i++;
547	578	}
548	579
549	580	if (strpos($this->header, '@') && !strpos($this->header, ',') && !strpos($this->header, ';') && !strpos($this->header, "\t")) {
	581	+ //We have an @ in the first line so there is definitely an issue there and the first line is not the right one!
	582	+ //And we don't have separators so we know the format
550	583	$this->header = 'email';
551	584	$i--;
552	585	}
553	586
	587	+ //Step 3 : we make sure the header of the file is correct (so it does correspond to columns from the database)
554	588	if (!$this->autoDetectHeader()) {
555		- acym_enqueueMessage(acym_translationSprintf('ACYM_IMPORT_HEADER', acym_escape($this->header)), 'error');
	589	+ acym_enqueueMessage(acym_translationSprintf('ACYM_IMPORT_HEADER', esc_html($this->header)), 'error');
556	590	acym_enqueueMessage(acym_translation('ACYM_IMPORT_EMAIL'), 'error');
557	591
558	592	return;
559	593	}
		@@ -567,19 +601,26 @@
567	601	$countUsersBeforeImport = $userClass->getCountTotalUsers();
568	602	$importUsers = [];
569	603	$errorLines = [];
570	604
	605	+ //Step 4 : we start importing the file as everything is OK
571	606	while (isset($importLines[$i])) {
	607	+ // If there are quotes in the line, check if it is a broken line or not
572	608	if (strpos($importLines[$i], '"') !== false) {
573	609	$data = [];
574	610	$j = $i + 1;
	611	+ // The position is the separator's one, -1 for the beginning as the line does not start by a separator
575	612	$position = -1;
576	613
	614	+ // Concatenate 30 lines max
577	615	while ($j < ($i + 30)) {
	616	+ // Test if the value is encapsulated by quotes
578	617	$quoteOpened = substr($importLines[$i], $position + 1, 1) == '"';
579	618
	619	+ // If encapsulated, search the end of the value
580	620	if ($quoteOpened) {
581	621	$nextQuotePosition = strpos($importLines[$i], '"', $position + 2);
	622	+ // If quotes in the value encapsulated by quotes... find the real closing quote...
582	623	while (
583	624	$nextQuotePosition !== false
584	625	&& $nextQuotePosition + 1 != strlen($importLines[$i])
585	626	&& substr($importLines[$i], $nextQuotePosition + 1, 1) != $this->separator
		@@ -586,9 +627,11 @@
586	627	) {
587	628	$nextQuotePosition = strpos($importLines[$i], '"', $nextQuotePosition + 1);
588	629	}
589	630
	631	+ // If we didn't find the whole value, then concatenate the current line with the next one
590	632	if ($nextQuotePosition === false) {
	633	+ // If end of import file, error...
591	634	if (!isset($importLines[$j])) {
592	635	break;
593	636	}
594	637
		@@ -596,18 +639,26 @@
596	639	$importLines[$i] = rtrim($importLines[$i], $this->separator);
597	640	unset($importLines[$j]);
598	641	$j++;
599	642	} else {
	643	+ // Found the entire value, add it to data and move the position to the next separator
600	644
	645	+ // If the quote is at the end of the line and the line is completed...
601	646	if (strlen($importLines[$i]) - 1 == $nextQuotePosition) {
602	647	$data[] = substr($importLines[$i], $position + 1);
603	648	break;
604	649	}
	650	+ // ,"toto",value...
	651	+ // 3456789
	652	+ // Start in 3+1 so 4: the first quote
	653	+ // Length = 9+1-(3+1) = 6, the length of "toto".
605	654	$data[] = substr($importLines[$i], $position + 1, $nextQuotePosition + 1 - ($position + 1));
606	655	$position = $nextQuotePosition + 1;
607	656	}
608	657	} else {
	658	+ // If not encapsulated by quotes, search the next separator
609	659	$nextSeparatorPosition = strpos($importLines[$i], $this->separator, $position + 1);
	660	+ // If not found, the line is completed
610	661	if ($nextSeparatorPosition === false) {
611	662	$data[] = substr($importLines[$i], $position + 1);
612	663	break;
613	664	} else { // If found the next separator, add the value in $data and change the position
		@@ -616,13 +667,16 @@
616	667	}
617	668	}
618	669	}
619	670
	671	+ // We unset some lines, don't forget to change the remaining lines keys
620	672	$importLines = array_merge($importLines);
621	673	} else {
	674	+ //We remove the separators from the end of the string as we don't need them... they will be added as empty string anyway during the import process.
622	675	$data = explode($this->separator, rtrim(trim($importLines[$i]), $this->separator));
623	676	}
624	677
	678	+ //We clean it... maybe there are other arguments at the end we should remove
625	679	if (!empty($this->separatorsToRemove)) {
626	680	for ($b = $numberColumns + $this->separatorsToRemove - 1; $b >= $numberColumns; $b--) {
627	681	if (isset($data[$b]) && (strlen($data[$b]) == 0 || $data[$b] == ' ')) {
628	682	unset($data[$b]);
		@@ -630,13 +684,15 @@
630	684	}
631	685	}
632	686
633	687	$i++;
	688	+ //We don't handle empty lines...
634	689	if (empty($importLines[$i - 1])) {
635	690	continue;
636	691	}
637	692
638	693	$this->totalTry++;
	694	+ //Lets try to fix it first...
639	695	if (count($data) > $numberColumns) {
640	696	$copy = $data;
641	697	foreach ($copy as $oneelem => $oneval) {
642	698	if (
		@@ -645,8 +701,9 @@
645	701	&& $oneval[strlen($oneval) - 1] !== '"'
646	702	&& isset($copy[$oneelem + 1])
647	703	&& $copy[$oneelem + 1][strlen($copy[$oneelem + 1]) - 1] == '"'
648	704	) {
	705	+ //We concat both with the separator
649	706	$data[$oneelem] = $copy[$oneelem].$this->separator.$copy[$oneelem + 1];
650	707	unset($data[$oneelem + 1]);
651	708	}
652	709	}
		@@ -653,9 +710,11 @@
653	710
654	711	$data = array_values($data);
655	712	}
656	713
	714	+ // Not enough columns found...
657	715	if (count($data) < $numberColumns) {
	716	+ // If not enough info compared to the header... lets add them as empty! We don't care if the user does not specify everything...
658	717	for ($a = count($data); $a < $numberColumns; $a++) {
659	718	$data[$a] = '';
660	719	}
661	720	}
		@@ -666,8 +725,9 @@
666	725	acym_enqueueMessage(acym_translationSprintf('ACYM_IMPORT_ARGUMENTS', $numberColumns), 'warning');
667	726	}
668	727	$errorcount++;
669	728
	729	+ //If it's for the first line, we return so we don't check the rest and let the user fix it
670	730	if ($this->totalTry == 1) {
671	731	return;
672	732	}
673	733	if (empty($errorLines)) {
		@@ -679,19 +739,23 @@
679	739
680	740	$newUser = new \stdClass();
681	741	$newUser->customfields = [];
682	742
	743	+ // Handle email column first to be able to use it with listids and listname
683	744	$emailKey = array_search('email', $this->columns);
684		- $newUser->email = trim(strip_tags($data[$emailKey]), '\'" ');
	745	+ $newUser->email = trim(acym_stripTags($data[$emailKey]), '\'" ');
	746	+ // Remove all whitespace type
685	747	$newUser->email = preg_replace("/\s+/u", '', $newUser->email);
686	748	if (!empty($newUser->email)) {
687	749	$newUser->email = acym_punycode($newUser->email);
688	750	}
689	751	$newUser->email = trim(str_replace([' ', "\t"], '', $encodingHelper->change($newUser->email, 'UTF-8', 'ISO-8859-1')));
	752	+ $newUser->email = strtolower($newUser->email);
690	753
691	754
692	755	if (!acym_isValidEmail($newUser->email)) {
693	756	static $errorcountfail = 0;
	757	+ //We limit to 10 errors otherwise it may break the Joomla messaging system in terms of memory usage
694	758	if ($errorcountfail == 0) {
695	759	acym_enqueueMessage(acym_translation('ACYM_ADDRESSES_INVALID'), 'warning');
696	760	}
697	761	$errorcountfail++;
		@@ -702,15 +766,18 @@
702	766	continue;
703	767	}
704	768
705	769	foreach ($data as $num => $value) {
	770	+ // Already handled the email address
706	771	if ($num == $emailKey) continue;
707	772
708	773	$field = $this->columns[$num];
709	774
	775	+ // Ignored
710	776	if ($field == 1) continue;
711	777
712	778	if ($field === 'listids') {
	779	+ //We explode the listids separated by "-" and forach of them we add the user in the importUserInLists
713	780	$liststosub = explode('-', trim($value, '\'" '));
714	781	foreach ($liststosub as $onelistid) {
715	782	$this->importUserInLists[intval(trim($onelistid))][] = acym_escapeDB($newUser->email);
716	783	}
		@@ -728,9 +795,9 @@
728	795	$newList = new \stdClass();
729	796	$newList->name = $onelistName;
730	797	$newList->active = 1;
731	798	$colors = ['#3366ff', '#7240A4', '#7A157D', '#157D69', '#ECE649'];
732		- $newList->color = $colors[rand(0, count($colors) - 1)];
	799	+ $newList->color = $colors[acym_rand(0, count($colors) - 1)];
733	800	$listid = $listClass->save($newList);
734	801	$newList->id = $listid;
735	802	$allLists[$onelistName] = $newList;
736	803	}
		@@ -775,10 +842,11 @@
775	842	continue;
776	843	}
777	844
778	845
	846	+ // If we assigned the data to an Acy custom field
779	847	if (strpos($field, 'cf_') === 0) {
780		- $newUser->customfields[substr($field, 3)] = trim(strip_tags($value), '\'" ');
	848	+ $newUser->customfields[substr($field, 3)] = trim(acym_stripTags($value), '\'" ');
781	849	continue;
782	850	}
783	851
784	852	if ($value === 'null') {
		@@ -783,9 +851,10 @@
783	851
784	852	if ($value === 'null') {
785	853	$newUser->$field = '';
786	854	} else {
787		- $newUser->$field = trim(strip_tags($value), '\'" ');
	855	+ //We remove anything which should not be there (quotes or spaces or return char or html tags)
	856	+ $newUser->$field = trim(acym_stripTags($value), '\'" ');
788	857	}
789	858	}
790	859
791	860	if (acym_isMultilingual() && empty($newUser->language)) {
		@@ -791,11 +860,13 @@
791	860	if (acym_isMultilingual() && empty($newUser->language)) {
792	861	$newUser->language = $this->config->get('multilingual_default', '');
793	862	}
794	863
	864	+ //Everything is Ok... we can add the line
795	865	$importUsers[] = $newUser;
796	866	$this->totalValid++;
797	867
	868	+ //Every 50 users, we handle it
798	869	if ($this->totalValid % 50 == 0) {
799	870	$this->insertUsers($importUsers, $timestamp);
800	871	$importUsers = [];
801	872	}
		@@ -822,8 +893,9 @@
822	893	$this->insertUsers($importUsers, $timestamp);
823	894
824	895	acym_query('UPDATE #__acym_configuration SET `value` = '.intval($timestamp).' WHERE `name` = \'last_import\'');
825	896
	897	+ // We could have imported empty values
826	898	acym_query('DELETE FROM #__acym_user_has_field WHERE `value` = ""');
827	899
828	900	$countUsersAfterImport = $userClass->getCountTotalUsers();
829	901	$totalInserted = $countUsersAfterImport - $countUsersBeforeImport;
		@@ -843,8 +915,9 @@
843	915	acym_enqueueMessage($reportMsg, 'info');
844	916
845	917	$this->insertSubscriptionHistory();
846	918
	919	+ // All users have been added properly into the database... we will now subscribe the users
847	920	$this->subscribeUsers();
848	921	}
849	922
850	923	private function insertUsers(array $users, int $timestamp): void
		@@ -870,8 +943,10 @@
870	943	foreach ($colNames as $oneColumn) {
871	944	acym_secureDBColumn($oneColumn);
872	945	}
873	946
	947	+ $dateColumns = $this->getUserDateColumns();
	948	+
874	949	$queryInsertUsers = 'INSERT'.($this->overwrite ? '' : ' IGNORE').' INTO #__acym_user (`'.implode('`,`', $colNames).'`) VALUES (';
875	950	$values = [];
876	951	$customFieldsvalues = [];
877	952	$allemails = [];
		@@ -883,15 +958,17 @@
883	958	if ($map === 'customfields') continue;
884	959
885	960	$oneValue = htmlspecialchars_decode($oneValue, ENT_QUOTES);
886	961
887		- if ($map !== 'id') {
	962	+ if ($map === 'id') {
	963	+ $oneValue = intval($oneValue);
	964	+ } elseif (isset($dateColumns[$map]) && strlen(trim((string)$oneValue)) === 0) {
	965	+ $oneValue = 'NULL';
	966	+ } else {
888	967	$oneValue = acym_escapeDB($oneValue);
889	968	if ($map === 'email') {
890	969	$allemails[] = $oneValue;
891	970	}
892		- } else {
893		- $oneValue = intval($oneValue);
894	971	}
895	972
896	973	$value[] = $oneValue;
897	974	}
		@@ -898,8 +975,9 @@
898	975
899	976	if (!isset($oneUser->key)) $value[] = acym_escapeDB(acym_generateKey(14));
900	977	$values[] = implode(',', $value);
901	978
	979	+ // Prepare import custom fields
902	980	if (!empty($oneUser->customfields)) $customFieldsvalues[$oneUser->email] = $oneUser->customfields;
903	981	}
904	982	acym_trigger('onAcymUserImport', [&$users]);
905	983
		@@ -950,16 +1028,33 @@
950	1028
951	1029	$this->allUserIds = array_merge($this->allUserIds, array_keys($importedUsers));
952	1030	}
953	1031
	1032	+ private function getUserDateColumns(): array
	1033	+ {
	1034	+ static $dateColumns = null;
	1035	+ if ($dateColumns === null) {
	1036	+ $dateColumns = [];
	1037	+ foreach (acym_loadObjectList('SHOW COLUMNS FROM #__acym_user') as $column) {
	1038	+ $isDateColumn = stripos($column->Type, 'date') !== false || stripos($column->Type, 'time') !== false;
	1039	+ if ($isDateColumn && strtoupper($column->Null) === 'YES') {
	1040	+ $dateColumns[strtolower($column->Field)] = true;
	1041	+ }
	1042	+ }
	1043	+ }
	1044	+
	1045	+ return $dateColumns;
	1046	+ }
	1047	+
954	1048	private function checkData(object &$user, int $timestamp)
955	1049	{
	1050	+ //Created field
956	1051	if (empty($user->creation_date)) {
957	1052	$user->creation_date = time();
958	1053	}
959	1054
960	1055	if (is_numeric($user->creation_date)) {
961		- $user->creation_date = date('Y-m-d H:i:s', $user->creation_date);
	1056	+ $user->creation_date = gmdate('Y-m-d H:i:s', $user->creation_date);
962	1057	}
963	1058
964	1059	if (!isset($user->active) || strlen($user->active) == 0) {
965	1060	$user->active = 1;
		@@ -972,8 +1067,9 @@
972	1067	if (empty($user->source)) {
973	1068	$user->source = 'Import on '.acym_date($timestamp, 'Y-m-d H:i:s');
974	1069	}
975	1070
	1071	+ //name field
976	1072	if (empty($user->name) && $this->generateName) {
977	1073	$user->name = ucwords(trim(str_replace(['.', '_', '-', 1, 2, 3, 4, 5, 6, 7, 8, 9, 0], ' ', substr($user->email, 0, strpos($user->email, '@')))));
978	1074	}
979	1075	}
		@@ -981,10 +1077,12 @@
981	1077	private function autoDetectHeader(): bool
982	1078	{
983	1079	$this->separator = ',';
984	1080
	1081	+ // Remove BOM characters if there is one
985	1082	$this->header = str_replace("\xEF\xBB\xBF", '', $this->header);
986	1083
	1084	+ // Detect the separator
987	1085	$listSeparators = ["\t", ';', ','];
988	1086	foreach ($listSeparators as $sep) {
989	1087	if (strpos($this->header, $sep) !== false) {
990	1088	$this->separator = $sep;
		@@ -993,8 +1091,9 @@
993	1091	}
994	1092
995	1093	$this->columns = explode($this->separator, $this->header);
996	1094
	1095	+ // Clean the headers
997	1096	for ($i = count($this->columns) - 1; $i >= 0; $i--) {
998	1097	if (strlen($this->columns[$i]) == 0) {
999	1098	unset($this->columns[$i]);
1000	1099	$this->separatorsToRemove++;
		@@ -1016,9 +1115,9 @@
1016	1115	if (!in_array($this->columns[$i], $columns) && $this->columns[$i] != 1) {
1017	1116	acym_enqueueMessage(
1018	1117	acym_translationSprintf(
1019	1118	'ACYM_IMPORT_ERROR_FIELD',
1020		- '<b>'.acym_escape($this->columns[$i]).'</b>',
	1119	+ '<b>'.esc_html($this->columns[$i]).'</b>',
1021	1120	'<b>'.implode('</b> | <b>', array_diff($columns, ['id', 'cms_id'])).'</b>'
1022	1121	),
1023	1122	'error'
1024	1123	);
		@@ -1041,9 +1140,9 @@
1041	1140	if (acym_fileGetExt($oneFile) != 'csv') {
1042	1141	continue;
1043	1142	}
1044	1143	if (filectime($oneFile) < time() - 86400) {
1045		- unlink($oneFile);
	1144	+ acym_deleteFile($oneFile);
1046	1145	}
1047	1146	}
1048	1147	}
1049	1148
		@@ -1048,13 +1147,14 @@
1048	1147	}
1049	1148
1050	1149	private function subscribeUsers(): void
1051	1150	{
	1151	+ //All users are known as : $this->allUserIds
1052	1152	if (empty($this->allUserIds)) {
1053	1153	return;
1054	1154	}
1055	1155
1056		- $subdate = date('Y-m-d H:i:s', time() - date('Z'));
	1156	+ $subdate = gmdate('Y-m-d H:i:s', time());
1057	1157
1058	1158	$listClass = new ListClass();
1059	1159	$lists = $this->getImportedLists();
1060	1160
		@@ -1072,8 +1172,9 @@
1072	1172	}
1073	1173	}
1074	1174
1075	1175	if (!empty($this->importUserInLists)) {
	1176	+ //We import the users based on the subscription of each of them, specified in the listids or listname columns...
1076	1177	foreach ($this->importUserInLists as $listid => $arrayEmails) {
1077	1178	if (empty($listid)) continue;
1078	1179
1079	1180	$listid = (int)$listid;
		@@ -1093,8 +1194,9 @@
1093	1194	}
1094	1195	}
1095	1196
1096	1197	if (!empty($lists)) {
	1198	+ //We do one query per list to avoid problem with huge queries and so in the mean time we can display info for each list
1097	1199	foreach ($lists as $listid => $val) {
1098	1200	if (empty($val)) {
1099	1201	continue;
1100	1202	}
		@@ -1100,9 +1202,9 @@
1100	1202	}
1101	1203
1102	1204	if ($val == -1) {
1103	1205	$dateColumn = 'unsubscribe_date';
1104		- $status = -1;
	1206	+ $status = 0;
1105	1207	} else {
1106	1208	$dateColumn = 'subscription_date';
1107	1209	$status = 1;
1108	1210	}
		@@ -1119,9 +1221,9 @@
1119	1221
1120	1222	if ($b > 200) {
1121	1223	$query = rtrim($query, ',');
1122	1224	if ($val == -1) {
1123		- $query .= ' ON DUPLICATE KEY UPDATE status = -1';
	1225	+ $query .= ' ON DUPLICATE KEY UPDATE status = 0';
1124	1226	$nbsubscribed = -acym_loadResult(
1125	1227	'SELECT COUNT(*) FROM #__acym_listsub WHERE `list_id` = '.intval($listid).' AND status != -1 AND `user_id` IN ('.implode(',', $currentSubids).')'
1126	1228	);
1127	1229	}
		@@ -1135,9 +1237,10 @@
1135	1237	$query .= '('.intval($listid).','.intval($subid).','.acym_escapeDB($subdate).','.$status.'),';
1136	1238	}
1137	1239	$query = rtrim($query, ',');
1138	1240	if ($val == -1) {
1139		- $query .= ' ON DUPLICATE KEY UPDATE status = -1';
	1241	+ $query .= ' ON DUPLICATE KEY UPDATE status = 0';
	1242	+ // It could be empty if we imported exactly 200, 400, 600... users
1140	1243	if (!empty($currentSubids)) {
1141	1244	$nbsubscribed = -acym_loadResult(
1142	1245	'SELECT COUNT(*) FROM #__acym_listsub WHERE `list_id` = '.intval($listid).' AND status != -1 AND `user_id` IN ('.implode(',', $currentSubids).')'
1143	1246	);