1<?php
2
3verifyCsrfGetToken();
4
5require dirname(__FILE__).'/../structure.php';
6require dirname(__FILE__).'/../inc/importlib.php';
7require dirname(__FILE__).'/../CsvReader.php';
8
9@ob_end_flush();
10$status = 'FAIL';
11output('<p class="information">'.$GLOBALS['I18N']->get('Reading emails from file ... '));
12flush();
13if (filesize($_SESSION['import_file']) > 50000) {
14    @ini_set('memory_limit', memory_get_usage() + 50 * filesize($_SESSION['import_file']));
15}
16flush();
17
18$csvReader = new CsvReader($_SESSION['import_file'], $_SESSION['import_field_delimiter']);
19$total = $csvReader->totalRows();
20output(sprintf('..'.$GLOBALS['I18N']->get('ok, %d lines').'</p>', $total));
21--$total; // now the number of subscribers to be imported
22$headers = $csvReader->getRow();
23$headers = array_unique($headers);
24$_SESSION['columnnames'] = $headers;
25
26//## show progress and adjust working space
27if ($total > 0) {
28    $import_field_delimiter = $_SESSION['import_field_delimiter'];
29    if ($total > 300 && !$_SESSION['test_import']) {
30        // this is a possibly a time consuming process, so show a progress bar
31        flush();
32        // increase the memory to make sure we are not running out
33        ini_set('memory_limit', '32M');
34    }
35
36    //## store the chosen mappings in the $system_attribute_mapping list
37    // print "A: ".sizeof($import_attribute);
38    foreach ($system_attributes as $key => $val) {
39        if (isset($_SESSION['systemindex'][$key])) {
40            $system_attribute_mapping[$key] = $_SESSION['systemindex'][$key];
41        }
42    }
43
44    //## Parse the lines into records
45    //  print "<br/>Loading emails .. ";
46    flush();
47    $count = array();
48    $count['email_add'] = 0;
49    $count['exist'] = 0;
50    $count['list_add'] = 0;
51    $count['group_add'] = 0;
52    $count['foundblacklisted'] = 0;
53    $c = 1;
54    $count['invalid_email'] = 0;
55    $num_lists = count($_SESSION['lists']);
56    $cnt = 0;
57    $count['emailmatch'] = 0;
58    $count['fkeymatch'] = 0;
59    $count['dataupdate'] = 0;
60    $count['duplicate'] = 0;
61    $additional_emails = 0;
62
63    while ($values = $csvReader->getRow()) {
64        set_time_limit(60);
65        // will contain attributes to store / change
66        $user = array();
67        $system_values = array();
68        foreach ($system_attribute_mapping as $column => $index) {
69            //   print '<br/>'.$column . ' = '. $values[$index];
70            if (!empty($values[$index])) {
71                $system_values[$column] = $values[$index];
72            } else {
73                $system_values[$column] = '';
74            }
75        }
76        //# Check completeness
77        $index = clean($system_values['email']);
78        $invalid = 0;
79        if (!$index) {
80            if ($_SESSION['show_warnings']) {
81                Warn($GLOBALS['I18N']->get('Record has no email').
82                    ": $c -> $line");
83            }
84            $index = $GLOBALS['I18N']->get('Invalid Email')." $c";
85            $system_values['email'] = $_SESSION['assign_invalid'];
86            $invalid = 1;
87            ++$count['invalid_email'];
88        }
89
90        //print ("<pre>" . var_dump($_SESSION["import_attribute"]) . "</pre>"); // debug
91        //    dbg('_SESSION["import_attribute"',$_SESSION["import_attribute"]); //debug
92        ##@TODO, this needs rewriting, as $unused_systemattr doesn' t actually exist here
93        // if (count($values) != (count($_SESSION['import_attribute']) + count($system_attributes) - count($unused_systemattr)) && !empty($_SESSION['test_import']) && !empty($_SESSION['show_warnings'])) {
94        //     Warn('Record has more values than header indicated ('.
95        //         count($values).'!='.
96        //         (count($_SESSION['import_attribute']) + count($system_attributes) - count($unused_systemattr)).
97        //         "), this may cause trouble: $index");
98        // }
99        if (!$invalid || ($invalid && $_SESSION['omit_invalid'] != 'yes')) {
100            $user['systemvalues'] = $system_values;
101            reset($_SESSION['import_attribute']);
102            $replace = array();
103            foreach ($_SESSION['import_attribute'] as $key => $val) {
104                if (!empty($values[$val['index']])) {
105                    $user[$val['index']] = htmlspecialchars($values[$val['index']]);
106                    $replace[$key] = htmlspecialchars($values[$val['index']]);
107                }
108            }
109        } else {
110            // Warn("Omitting invalid one: $email");
111        }
112        $user['systemvalues']['email'] = parsePlaceHolders($system_values['email'],
113            array_merge($replace, $system_values, array(
114                'number' => $c,
115            )));
116        $user['systemvalues']['email'] = cleanEmail($user['systemvalues']['email']);
117        ++$c;
118        if (!isset($user['systemvalues']['htmlemail'])) {
119            $user['systemvalues']['htmlemail'] = 1;
120        }
121        if ($_SESSION['test_import']) {
122
123//      var_dump($user["systemvalues"]);exit;
124            $html = '';
125            foreach ($user['systemvalues'] as $column => $value) {
126                if (strpos($column, 'grouptype_') === 0) {
127                    if (isset($system_attributes[$column])) {
128                        $column = $system_attributes[$column];
129                    }
130                }
131                if (!empty($column)) {
132                    if ($value) {
133                        $html .= "$column -> $value<br/>\n";
134                    } else {
135                        $html .= "$column -> ".$GLOBALS['I18N']->get('clear value')."<br/>\n";
136                    }
137                }
138            }
139            //  var_dump($_SESSION["systemindex"]);
140
141            reset($_SESSION['import_attribute']);
142            foreach ($_SESSION['import_attribute'] as $column => $item) {
143                if (!empty($user[$item['index']])) {
144                    if ($item['record'] == 'new') {
145                        $html .= ' '.$GLOBALS['I18N']->get('New Attribute').': '.$item['column'];
146                    } elseif ($item['record'] == 'skip') {
147                        // forget about it
148                        $html .= ' '.$GLOBALS['I18N']->get('Skip value').' '.$column.': ';
149                    } elseif ($item['record'] != 'system') {
150                        $html .= $attributes[$item['record']];
151//            var_dump($attributes[$item['record']]);
152                    } else {
153                        $html .= $item['column'];
154                    }
155                    $html .= ' -> '.$user[$item['index']].'<br/>';
156                }
157            }
158            if ($html) {
159                echo '<blockquote>'.$html.'</blockquote><hr />';
160            }
161        } else { // not test
162            @ob_end_flush();
163            if ($cnt % 5 == 0) {
164                echo '<script type="text/javascript">
165        var parentJQuery = window.parent.jQuery;
166        parentJQuery("#progressbar").updateProgress("' .$cnt.','.$total.'");
167        </script>';
168                flush();
169            }
170            ++$cnt;
171
172            if (!$invalid || ($invalid && $_SESSION['omit_invalid'] != 'yes')) {
173                // do import
174                //# create new attributes
175                foreach ($_SESSION['import_attribute'] as $column => $item) {
176                    if ($item['record'] == 'new') {
177                        Sql_Query(sprintf('insert into %s (name,type) values("%s","textline")', $tables['attribute'],
178                            addslashes($column)));
179                        $attid = Sql_Insert_id();
180                        Sql_Query(sprintf('update %s set tablename = "attr%d" where id = %d', $tables['attribute'],
181                            $attid, $attid));
182                        Sql_Query('create table '.$GLOBALS['table_prefix'].'listattr_attr'.$attid.'
183                        (id integer not null primary key auto_increment, name varchar(255), unique (name(150)),
184                        listorder integer default 0)');
185                        $_SESSION['import_attribute'][$column]['record'] = $attid;
186                    }
187                }
188                $new = 0;
189                if (!empty($user['systemvalues']['foreignkey'])) {
190                    //         dbg('Importing on FK '.$user["systemvalues"]["foreignkey"].' email :'.$user["systemvalues"]["email"]);
191                    $result = Sql_query(sprintf('select id,uniqid from %s where foreignkey = "%s"', $tables['user'],
192                        $user['systemvalues']['foreignkey']));
193                    // print "<br/>Using foreign key for matching: ".$user["systemvalues"]["foreign key"];
194                    ++$count['fkeymatch'];
195                    $exists = Sql_Affected_Rows();
196                    $existing_user = Sql_fetch_array($result);
197                    // check whether the email will clash
198                    $clashcheck = Sql_Fetch_Array_Query(sprintf('select id,foreignkey,uniqid from %s
199                    where email = "%s"', $tables['user'], $user['systemvalues']['email']));
200                    if (!empty($clashcheck['id']) && $clashcheck['id'] != $existing_user['id']) {
201                        //https://mantis.phplist.org/view.php?id=17752
202                        // if the existing record does not have an FK, we treat it as an update, matched on email
203                        if (empty($clashcheck['foreignkey'])) {
204                            ++$count['emailmatch'];
205                            --$count['fkeymatch'];
206                            $exists = 1;
207                            $existing_user = $clashcheck;
208                        } else {
209                            ++$count['duplicate'];
210                            $notduplicate = 0;
211                            $c = 0;
212                            while (!$notduplicate) {
213                                ++$c;
214                                $req = Sql_Query(sprintf('select id from %s where email = "%s"', $tables['user'],
215                                    $GLOBALS['I18N']->get('duplicate').
216                                    "$c ".$user['systemvalues']['email']));
217                                $notduplicate = !Sql_Affected_Rows();
218                            }
219                            if (!$_SESSION['retainold']) {
220                                Sql_Query(sprintf('update %s set email = "%s" where email = "%s"', $tables['user'],
221                                    "duplicate$c ".
222                                    $user['systemvalues']['email'], $user['systemvalues']['email']));
223                                addUserHistory("duplicate$c ".$user['systemvalues']['email'], 'Duplication clash ',
224                                    ' User marked duplicate email after clash with imported record');
225                            } else {
226                                if ($_SESSION['show_warnings']) {
227                                    echo Warn($GLOBALS['I18N']->get('Duplicate Email').' '.$user['systemvalues']['email'].$GLOBALS['I18N']->get(' user imported as ').'&quot;'.$GLOBALS['I18N']->get('duplicate')."$c ".$user['systemvalues']['email'].'&quot;');
228                                }
229                                $user['systemvalues']['email'] = $GLOBALS['I18N']->get('duplicate')."$c ".$user['systemvalues']['email'];
230                            }
231                        }
232                    }
233                } else {
234                    dbg('Importing on email '.$user['systemvalues']['email']);
235                    $result = Sql_query(sprintf('select id,uniqid from %s where email = "%s"', $tables['user'],
236                        $user['systemvalues']['email']));
237                    // print "<br/>Using email for matching: ".$user["systemvalues"]["email"];
238                    ++$count['emailmatch'];
239                    $exists = Sql_Affected_Rows();
240                    $existing_user = Sql_fetch_array($result);
241                }
242                if ($exists) {
243                    // User exist, remember some values to add them to the lists
244                    ++$count['exist'];
245                    $userid = $existing_user['id'];
246                    $uniqid = $existing_user['uniqid'];
247                } else {
248                    // user does not exist
249                    $new = 1;
250                    // this is very time consuming when importing loads of users as it does a lookup
251                    // needs speeding up if possible
252                    $uniqid = getUniqid();
253                    $confirmed = $_SESSION['notify'] != 'yes' && !preg_match('/Invalid Email/i', $index);
254
255                    $query = sprintf('INSERT INTO %s (email,entered,confirmed,uniqid,htmlemail,uuid)
256                    values("%s",now(),%d,"%s",1,"%s")', $tables['user'], $user['systemvalues']['email'], $confirmed,
257                        $uniqid, (string) uuid::generate(4));
258                    $result = Sql_query($query, 1);
259                    $userid = Sql_insert_id();
260                    if (!$userid) {
261                        // no id returned, so it must have been a duplicate entry
262                        if ($_SESSION['show_warnings']) {
263                            echo Warn($GLOBALS['I18N']->get('Duplicate Email').' '.$user['systemvalues']['email']);
264                        }
265                        $c = 0;
266                        while (!$userid) {
267                            ++$c;
268                            $query = sprintf('INSERT INTO %s (email,entered,confirmed,uniqid,htmlemail,uuid)
269                            values("%s",now(),%d,"%s",1,"%s")', $tables['user'], $user['systemvalues']['email'].
270                                " ($c)", 0, $uniqid, (string) uuid::generate(4));
271                            $result = Sql_query($query, 1);
272                            $userid = Sql_insert_id();
273                        }
274                        $user['systemvalues']['email'] = $user['systemvalues']['email']." ($c)";
275                    }
276
277                    ++$count['email_add'];
278                    $some = 1;
279                }
280
281                $history_entry = $GLOBALS['admin_scheme'].'://'.getConfig('website').$GLOBALS['adminpages'].'/?page=user&id='.$userid."\n\n";
282                reset($_SESSION['import_attribute']);
283                //   var_dump($_SESSION);exit;
284                if ($new || (!$new && $_SESSION['overwrite'] == 'yes')) {
285                    $query = '';
286                    ++$count['dataupdate'];
287                    $old_data = Sql_Fetch_Array_Query(sprintf('select * from %s where id = %d', $tables['user'],
288                        $userid));
289                    $old_data = array_merge($old_data, getUserAttributeValues('', $userid));
290                    foreach ($user['systemvalues'] as $column => $value) {
291                        if (!empty($column)) { // && !empty($value)) {
292                            if ($column == 'groupmapping' || strpos($column, 'grouptype_') === 0) {
293                                //# specifically request this group, so that it doesn't interfere with the "groups" which are the ones
294                                //# submitted in the form
295
296                                if (strpos($column, 'grouptype_') === 0) {
297                                    list($tmp, $type) = explode('_', $column);
298                                } else {
299                                    $type = $_SESSION['grouptype'];
300                                }
301                                $type = sprintf('%d', $type);
302                                //# verify the type is set
303                                if (!in_array($type, array_keys($GLOBALS['config']['usergroup_types']))) {
304                                    Warn('Invalid group membership type'.$type);
305                                    dbg($type, 'Type not found');
306                                }
307
308                                $columnGroups = explode(',', $value);
309                                foreach ($columnGroups as $sGroup) {
310                                    $sGroup = trim($sGroup);
311                                    $groupIdReq = Sql_Fetch_Row_Query(sprintf('select id from groups where name = "%s"',
312                                        $sGroup));
313                                    if (empty($groupIdReq[0])) {
314                                        Sql_Query(sprintf('insert into groups (name) values("%s")', $sGroup));
315                                        Warn("Group $sGroup added");
316                                        $groupIdReq[0] = Sql_Insert_id();
317                                    }
318                                    dbg('Adding to group '.$sGroup.' with type '.$GLOBALS['config']['usergroup_types'][$type]);
319                                    //# @@ this may cause problems on not-upgraded DBs
320                                    Sql_Query(sprintf('replace into user_group (userid,groupid,type) values(%d,%d,%d)',
321                                        $userid, $groupIdReq[0], $type));
322                                }
323                            } else {
324                                $query .= sprintf('%s = "%s",', $column, $value);
325                            }
326                        }
327                    }
328                    if ($query) {
329                        $query = substr($query, 0, -1);
330                        // this may cause a duplicate error on email, so add ignore
331                        Sql_Query("update ignore {$tables['user']} set $query where id = $userid");
332                    }
333                    foreach ($_SESSION['import_attribute'] as $item) {
334                        if (isset($user[$item['index']]) && is_numeric($item['record']) && strpos($item['record'],
335                                'grouptype_') !== 0
336                        ) {
337                            $attribute_index = $item['record'];
338                            $uservalue = $user[$item['index']];
339                            // check whether this is a textline or a selectable item
340                            $att = Sql_Fetch_Row_Query('select type,tablename,name from '.$tables['attribute']." where id = $attribute_index"); ////
341                            switch ($att[0]) {
342                                case 'select':
343                                case 'radio':
344                                    $val = Sql_Query("select id from $table_prefix"."listattr_$att[1] where name = \"$uservalue\"");
345                                    // if we do not have this value add it
346                                    if (!Sql_Affected_Rows()) {
347                                        Sql_Query("insert into $table_prefix"."listattr_$att[1] (name) values(\"$uservalue\")");
348                                        Warn("Value $uservalue added to attribute $att[2]");
349                                        $user_att_value = Sql_Insert_Id();
350                                    } else {
351                                        $d = Sql_Fetch_Row($val);
352                                        $user_att_value = $d[0];
353                                    }
354                                    break;
355                                case 'checkboxgroup':
356                                    $values = explode(',', $uservalue);
357                                    $valueIds = array();
358                                    foreach ($values as $importValue) {
359                                        $val = Sql_Query("select id from $table_prefix"."listattr_$att[1] where name = \"$importValue\"");
360                                        // if we do not have this value add it
361                                        if (!Sql_Affected_Rows()) {
362                                            Sql_Query("insert into $table_prefix"."listattr_$att[1] (name) values(\"$importValue\")");
363                                            Warn("Value $importValue added to attribute $att[2]");
364                                            $valueIds[] = Sql_Insert_Id();
365                                        } else {
366                                            $d = Sql_Fetch_Row($val);
367                                            $valueIds[] = $d[0];
368                                        }
369                                    }
370                                    $user_att_value = implode(',', $valueIds);
371                                    break;
372                                case 'checkbox':
373                                    $uservalue = trim($uservalue);
374                                    //print $uservalue;exit;
375                                    if (!empty($uservalue) && $uservalue != 'off') {
376                                        $user_att_value = 'on';
377                                    } else {
378                                        $user_att_value = '';
379                                    }
380                                    break;
381                                case 'date':
382                                    //                $user_att_value = parseDate($uservalue);
383                                    $user_att_value = $uservalue;
384                                    break;
385                                default:
386                                    $user_att_value = $uservalue;
387                                    break;
388                            }
389
390                            Sql_query(sprintf('replace into %s (attributeid,userid,value) values(%d,%d,"%s")',
391                                $tables['user_attribute'], $attribute_index, $userid, $user_att_value));
392                        } else {
393                            if ($item['record'] != 'skip') {
394                                // add an empty entry if none existed
395                                Sql_Query(sprintf('insert ignore into %s (attributeid,userid,value) values(%d,%d,"")',
396                                    $tables['user_attribute'], $item['record'], $userid));
397                            }
398                        }
399                    }
400                    $current_data = Sql_Fetch_Array_Query(sprintf('select * from %s where id = %d', $tables['user'],
401                        $userid));
402                    $current_data = array_merge($current_data, getUserAttributeValues('', $userid));
403                    $information_changed = 0;
404                    foreach ($current_data as $key => $val) {
405                        if (!is_numeric($key)) {
406                            if (isset($old_data[$key]) && $old_data[$key] != $val && $old_data[$key] && $key != 'password' && $key != 'modified') {
407                                $information_changed = 1;
408                                $history_entry .= "$key = $val\n*changed* from $old_data[$key]\n";
409                            }
410                        }
411                    }
412                    if (!$information_changed) {
413                        $history_entry .= "No user details changed\n";
414                    }
415                }
416
417                //add this user to the lists identified, except when they are blacklisted
418                $isBlackListed = isBlackListed($user['systemvalues']['email']);
419                if (!$isBlackListed && is_array($_SESSION['lists'])) {
420                    reset($_SESSION['lists']);
421                    $addition = 0;
422                    $listoflists = '';
423                    foreach ($_SESSION['lists'] as $key => $listid) {
424                        $query = 'insert ignore INTO '.$tables['listuser']." (userid,listid,entered) values($userid,$listid,now())";
425                        $result = Sql_query($query, 1);
426                        // if the affected rows is 0, the user was already subscribed
427                        if (Sql_Affected_Rows() == 1) {
428                            $addition = 1;
429                            $history_entry .= s('Subscribed to %s', listName($key))."\n";
430                        }
431                        $listoflists .= '  * '.listName($key)."\n"; // $_SESSION["listname"][$key] . "\n";
432                    }
433                    if ($addition) {
434                        ++$count['list_add'];
435                    }
436                    if (!TEST && $_SESSION['notify'] == 'yes' && $addition) {
437                        $subscribemessage = str_replace('[LISTS]', $listoflists,
438                            getUserConfig('subscribemessage', $userid));
439                        if (function_exists('sendmail')) {
440                            sendMail($user['systemvalues']['email'], getConfig('subscribesubject'), $subscribemessage,
441                                system_messageheaders(), $envelope);
442                            if (isset($_SESSION['throttle_import'])) {
443                                sleep($_SESSION['throttle_import']);
444                            }
445                        }
446                    }
447                } elseif ($isBlackListed) {
448                    //# mark blacklisted, just in case ##17288
449                    Sql_Query(sprintf('update %s set blacklisted = 1 where id = %d', $tables['user'], $userid));
450                    ++$count['foundblacklisted'];
451                }
452                addUserHistory($user['systemvalues']['email'], 'Import by '.adminName(), $history_entry);
453
454                if (!is_array($_SESSION['groups'])) {
455                    $groups = array();
456                } else {
457                    $groups = $_SESSION['groups'];
458                }
459                if (isset($everyone_groupid) && !in_array($everyone_groupid, $groups)) {
460                    array_push($groups, $everyone_groupid);
461                }
462            }
463        } // end else not test
464        if ($_SESSION['test_import'] && $c > 50) {
465            break;
466        }
467    }
468
469    $report = '';
470    if (empty($some) && !$count['list_add']) {
471        $report .= '<br/>'.s('All the emails already exist in the database and are member of the lists');
472    } else {
473        $report .= '<br/>'.s('%d emails succesfully imported to the database and added to %d lists.',
474                $count['email_add'], $num_lists);
475        $report .= '<br/>'.s('%d emails subscribed to the lists', $count['list_add']);
476        if ($count['exist']) {
477            $report .= '<br/>'.s('%d emails already existed in the database', $count['exist']);
478        }
479    }
480    if ($count['invalid_email']) {
481        $report .= '<br/>'.s('%d Invalid Emails found.', $count['invalid_email']);
482        if (!$_SESSION['omit_invalid']) {
483            $report .= '<br/>'.s('These records were added, but the email has been made up from ').$_SESSION['assign_invalid'];
484        } else {
485            $report .= '<br/>'.s('These records were deleted. Check your source and reimport the data. Duplicates will be identified.');
486        }
487    }
488    if ($count['duplicate']) {
489        $report .= '<br/>'.s('%d duplicate emails found.', $count['duplicate']);
490    }
491    if ($_SESSION['overwrite'] == 'yes') {
492        $report .= '<br/>'.s('Subscriber data was updated for %d subscribers', $count['dataupdate']);
493    }
494    if ($count['foundblacklisted']) {
495        $report .= '<br/>'.s('%d emails were on the blacklist and have not been added to the lists',
496                $count['foundblacklisted']);
497    }
498    $report .= '<br/>'.s('%d subscribers were matched by foreign key, %d by email', $count['fkeymatch'],
499            $count['emailmatch']);
500    if (!$GLOBALS['commandline']) {
501        echo $report;
502        if (function_exists('sendmail')) {
503            sendMail(getConfig('admin_address'), $GLOBALS['I18N']->get('phplist Import Results'), $report);
504        }
505        if (function_exists('logevent')) {
506            logEvent($report);
507        }
508        foreach ($GLOBALS['plugins'] as $pluginname => $plugin) {
509            $plugin->importReport($report);
510        }
511    } else {
512        output($report);
513    }
514    $htmlupdate = $report.'<br/>'.'<div class="button btn btn-default">'.PageLinkButton('import2', s('Import some more emails')).'</div>';
515    $htmlupdate = str_replace("'", "\'", $htmlupdate);
516
517    clearImport();
518    $status = '<script type="text/javascript">
519      var parentJQuery = window.parent.jQuery;
520      parentJQuery("#progressbar").progressbar("destroy");
521      parentJQuery("#busyimage").hide();
522      parentJQuery("#progresscount").html(\'' .$htmlupdate.'\');
523      </script>';
524}
525