1<?php
2require_once dirname(__FILE__).'/accesscheck.php';
3if (!ALLOW_IMPORT) {
4    echo '<p>'.$GLOBALS['I18N']->get('import is not available').'</p>';
5
6    return;
7}
8
9// import from a different PHPlist installation
10
11if ($require_login && !isSuperUser()) {
12    $access = accessLevel('import4');
13    if ($access == 'owner') {
14        $subselect = ' where owner = '.$_SESSION['logindetails']['id'];
15    } elseif ($access == 'all') {
16        $subselect = '';
17    } elseif ($access == 'none') {
18        $subselect = ' where id = 0';
19    }
20}
21
22function connectLocal()
23{
24    $database_connection = Sql_Connect(
25        $GLOBALS['database_host'],
26        $GLOBALS['database_user'],
27        $GLOBALS['database_password'],
28        $GLOBALS['database_name']);
29
30    return $database_connection;
31}
32
33function connectRemote()
34{
35    return Sql_Connect($_POST['remote_host'],
36        $_POST['remote_user'],
37        $_POST['remote_password'],
38        $_POST['remote_database']);
39}
40
41$result = Sql_query('SELECT id,name FROM '.$tables['list']." $subselect ORDER BY listorder");
42while ($row = Sql_fetch_array($result)) {
43    $available_lists[$row['id']] = $row['name'];
44    $some = 1;
45}
46if (!$some) {
47    // @@@@ not sure about this one:
48    echo $GLOBALS['I18N']->get('No lists available').', '.PageLink2('editlist', $GLOBALS['I18N']->get('add_list'));
49}
50//foreach ($_POST as $key => $val) {
51//  print "$key => $val<br/>";
52//}
53
54if (!$_POST['remote_host'] ||
55    !$_POST['remote_user'] ||
56    !$_POST['remote_password'] || !$_POST['remote_database']
57) {
58    printf('
59  <p class="information">' .$GLOBALS['I18N']->get('Please enter details of the remote Server').'</p>
60  <form method="post">
61  <table class="importForm">
62  <tr><td>' .$GLOBALS['I18N']->get('Server:').'</td><td><input type="text" name="remote_host" value="%s" size="30"></td></tr>
63  <tr><td>' .$GLOBALS['I18N']->get('user').'</td><td><input type="text" name="remote_user" value="%s" size="30"></td></tr>
64  <tr><td>' .$GLOBALS['I18N']->get('Password:').'</td><td><input type="text" name="remote_password" value="%s" size="30"></td></tr>
65  <tr><td>' .$GLOBALS['I18N']->get('Database Name:').'</td><td><input type="text" name="remote_database" value="%s" size="30"></td></tr>
66  <tr><td>' .$GLOBALS['I18N']->get('Table prefix:').'</td><td><input type="text" name="remote_prefix" value="%s" size="30"></td></tr>
67  <tr><td>' .$GLOBALS['I18N']->get('Usertable prefix:').'</td><td><input type="text" name="remote_userprefix" value="%s" size="30"></td></tr>
68  ', $_POST['remote_server'], $_POST['remote_user'], $_POST['remote_password'],
69        $_POST['remote_database'], $_POST['remote_prefix'], $_POST['remote_userprefix']);
70    $c = 0;
71    echo '<tr><td colspan="2">';
72    if (count($available_lists) > 1) {
73        echo $GLOBALS['I18N']->get('select_lists').'<br/>';
74    }
75    echo '<ul>';
76    foreach ($available_lists as $index => $name) {
77        printf('<li><input type="checkbox" name="lists[%d]" value="%d" %s>%s</li>',
78            $c, $index, is_array($_POST['lists']) && in_array($index, array_values($_POST['lists'])) ? 'checked' : '',
79            $name);
80        ++$c;
81    }
82    printf('
83  <li><input type="checkbox" name="copyremotelists" value="yes" %s>' .$GLOBALS['I18N']->get('Copy lists from remote server (lists are matched by name)').'</li>
84  </ul></td></tr>
85<tr><td>' .$GLOBALS['I18N']->get('Mark new users as HTML:').'</td><td><input type="checkbox" name="markhtml" value="yes" %s></td></tr>
86<tr><td colspan="2">' .$GLOBALS['I18N']->get('If you check "Overwrite Existing", information about a user in the database will be replaced by the imported information. Users are matched by email.').'</td></tr>
87<tr><td>' .$GLOBALS['I18N']->get('Overwrite Existing:').'</td><td><input type="checkbox" name="overwrite" value="yes" %s></td></tr>
88  <tr><td colspan="2"><p class="submit"><input type="submit" value="' .$GLOBALS['I18N']->get('continue').'"></p></td></tr>
89  </table></form>
90  ', $_POST['copyremotelists'] == 'yes' ? 'checked' : '', $_POST['markhtml'] == 'yes' ? 'checked' : '',
91        $_POST['overwrite'] == 'yes' ? 'checked' : ''
92    );
93} else {
94    set_time_limit(600);
95    ob_end_flush();
96    include_once 'structure.php';
97    echo $GLOBALS['I18N']->get('Making connection with remote database').'<br/>';
98    flush();
99    $remote = connectRemote();
100    if (!$remote) {
101        Fatal_Error($GLOBALS['I18N']->get('cannot connect to remote database'));
102
103        return;
104    }
105    $remote_tables = array(
106        'user'           => $_POST['remote_userprefix'].'user',
107        'list'           => $_POST['remote_prefix'].'list',
108        'listuser'       => $_POST['remote_prefix'].'listuser',
109        'attribute'      => $_POST['remote_userprefix'].'attribute',
110        'user_attribute' => $_POST['remote_userprefix'].'user_attribute',
111        'config'         => $_POST['remote_prefix'].'config',
112    );
113    echo $GLOBALS['I18N']->get('Getting data from ').htmlentities($_POST['remote_database']).'@'.htmlentities($_POST['remote_host']).'<br/>';
114
115    $version = Sql_Fetch_Row_Query("select value from {$remote_tables['config']} where item = \"version\"");
116    echo $GLOBALS['I18N']->get('Remote version is')." $version[0]<br/>\n";
117    $usercnt = Sql_Fetch_Row_Query("select count(*) from {$remote_tables['user']}");
118    echo $GLOBALS['I18N']->get('Remote version has')." $usercnt[0] ".$GLOBALS['I18N']->get('users').'<br/>';
119    if (!$usercnt[0]) {
120        Fatal_Error($GLOBALS['I18N']->get('No users to copy, is the prefix correct?'));
121
122        return;
123    }
124    $totalusers = $usercnt[0];
125    $listcnt = Sql_Fetch_Row_Query("select count(*) from {$remote_tables['list']}");
126    echo $GLOBALS['I18N']->get('Remote version has')." $listcnt[0] ".$GLOBALS['I18N']->get('lists').'<br/>';
127
128    flush();
129    echo '<h3>'.$GLOBALS['I18N']->get('Copying lists').'</h3>';
130    // first copy the lists across
131    $listmap = array();
132    $remote_lists = array();
133    $lists_req = Sql_Query("select * from {$remote_tables['list']}");
134    while ($row = Sql_Fetch_Array($lists_req)) {
135        array_push($remote_lists, $row);
136    }
137
138    connectLocal();
139    foreach ($remote_lists as $list) {
140        $localid_req = Sql_Fetch_Row_Query(sprintf('select id from %s where name = "%s"',
141            $tables['list'], $list['name']));
142        if ($localid_req[0]) {
143            $listmap[$list['id']] = $localid_req[0];
144            echo $GLOBALS['I18N']->get('list').' '.$list['name'].$GLOBALS['I18N']->get('exists locally')." <br/>\n";
145        } elseif ($_POST['copyremotelists']) {
146            $query = '';
147            foreach ($DBstruct['list'] as $colname => $colspec) {
148                if ($colname != 'id' && $colname != 'index' && $colname != 'unique' && $colname != 'primary key') {
149                    $query .= sprintf('%s = "%s",', $colname, addslashes($list[$colname]));
150                }
151            }
152            $query = substr($query, 0, -1);
153            echo $GLOBALS['I18N']->get('list').' '.$list['name'].$GLOBALS['I18N']->get('created locally')." <br/>\n";
154            Sql_Query("insert into {$tables['list']} set $query");
155            $listmap[$list['id']] = Sql_Insert_id();
156        } else {
157            echo $GLOBALS['I18N']->get('Remote list').' '.$list['name'].$GLOBALS['I18N']->get('not created')." <br/>\n";
158        }
159    }
160
161    connectRemote();
162    echo '<h3>'.$GLOBALS['I18N']->get('Copying attributes').'</h3>';
163    // now copy the attributes
164    $attributemap = array();
165    $remote_atts = array();
166    $att_req = Sql_Query("select * from {$remote_tables['attribute']}");
167    while ($row = Sql_Fetch_Array($att_req)) {
168        array_push($remote_atts, $row);
169    }
170
171    connectLocal();
172    foreach ($remote_atts as $att) {
173        $localid_req = Sql_Fetch_Row_Query(sprintf('select id from %s where name = "%s"',
174            $tables['attribute'], stripslashes($att['name'])));
175        if ($localid_req[0]) {
176            $attributemap[$att['id']] = $localid_req[0];
177            echo $GLOBALS['I18N']->get('Attribute').' '.$att['name'].$GLOBALS['I18N']->get('exists locally')." <br/>\n";
178        } else {
179            $query = '';
180            foreach ($DBstruct['attribute'] as $colname => $colspec) {
181                if ($colname != 'id' && $colname != 'index' && $colname != 'unique' && $colname != 'primary key') {
182                    $query .= sprintf('%s = "%s",', $colname, addslashes($att[$colname]));
183                }
184            }
185            $query = substr($query, 0, -1);
186            echo $GLOBALS['I18N']->get('Attribute').' '.$att['name'].$GLOBALS['I18N']->get('created locally')." <br/>\n";
187            Sql_Query("insert into {$tables['attribute']} set $query");
188            $attributemap[$att['id']] = Sql_Insert_id();
189            if ($att['type'] == 'select' || $att['type'] == 'radio' || $att['type'] == 'checkboxgroup') {
190                $query = "create table if not exists $table_prefix".'listattr_'.$att['tablename'].'
191        (id integer not null primary key auto_increment,
192        name varchar(255), unique (name(150)),listorder integer default 0)';
193                Sql_Query($query, 0);
194                connectRemote();
195                $attvalue_req = Sql_Query('select id,name,listorder from '.$_POST['remote_prefix'].'listattr_'.$att['tablename']);
196                $values = array();
197                while ($value = Sql_Fetch_Array($attvalue_req)) {
198                    array_push($values, $value);
199                }
200                connectLocal();
201                foreach ($values as $value) {
202                    Sql_Query(sprintf('replace into %slistattr_%s (name,id,listorder)
203            values("%s",%d,"%s")', $table_prefix, $att['tablename'], addslashes($value['name']), $value['id'],
204                        $value['listorder']));
205                }
206            }
207        }
208    }
209
210    echo '<h3>'.$GLOBALS['I18N']->get('Copying users').'</h3>';
211    // copy the users
212    $usercnt = 0;
213    $existcnt = 0;
214    $newcnt = 0;
215    while ($usercnt < $totalusers) {
216        set_time_limit(60);
217        connectRemote();
218        $req = Sql_Query("select * from {$remote_tables['user']} limit $usercnt,1");
219        $user = Sql_Fetch_Array($req);
220        ++$usercnt;
221        $new = 0;
222        if ($usercnt % 20 == 0) {
223            echo "$usercnt / $totalusers<br/>";
224            flush();
225        }
226        connectLocal();
227        $query = '';
228        $exists = Sql_Fetch_Row_Query(sprintf('select id from %s where email = "%s"', $tables['user'], $user['email']));
229        if ($exists[0]) {
230            ++$existcnt;
231            //    print $user["email"] .$GLOBALS['I18N']->get('exists locally')." ..";
232            if ($_POST['overwrite']) {
233                //      print " .. ".$GLOBALS['I18N']->get('overwriting local data')."<br/>";
234                $query = 'replace into '.$tables['user'].' set id = '.$exists[0].', ';
235            } else {
236                //      print " .. ".$GLOBALS['I18N']->get('keeping local data')."<br/>";
237            }
238            $userid = $exists[0];
239        } else {
240            ++$newcnt;
241            $new = 1;
242            //    print $user["email"] .$GLOBALS['I18N']->get('is a new user')."<br/>";
243            $query = 'insert into '.$tables['user'].' set ';
244        }
245        if ($query) {
246            foreach ($DBstruct['user'] as $colname => $colspec) {
247                if ($colname != 'id' && $colname != 'index' && $colname != 'unique' && $colname != 'primary key') {
248                    $query .= sprintf('%s = "%s",', $colname, addslashes($user[$colname]));
249                }
250            }
251            $query = substr($query, 0, -1);
252            //print $query . "<br/>";
253            Sql_Query("$query");
254            $userid = Sql_Insert_id();
255        }
256        if ($userid && $_POST['markhtml']) {
257            Sql_Query("update {$tables['user']} set htmlemail = 1 where id = $userid");
258        }
259
260        if ($new || (!$new && $_POST['overwrite'])) {
261            // now check for attributes and list membership
262            connectRemote();
263            $useratt = array();
264            $req = Sql_Query("select * from {$remote_tables['user_attribute']},
265        {$remote_tables['attribute']} where {$remote_tables['user_attribute']}.attributeid =
266        {$remote_tables['attribute']}.id and {$remote_tables['user_attribute']}.userid = $user[0]");
267            while ($att = Sql_Fetch_Array($req)) {
268                $value = '';
269                switch ($att['type']) {
270                    case 'select':
271                    case 'radio':
272                        $valreq = Sql_Fetch_Row_Query(sprintf('select name from %slistattr_%s where id = %d',
273                            $_POST['remote_prefix'], $att['tablename'], $att['value']));
274                        $value = $valreq[0];
275                        break;
276                    case 'checkboxgroup':
277                        $valreq = Sql_Query(sprintf('select name from %slistattr_%s where id in (%s)',
278                            sql_escape($_POST['remote_prefix']), $att['tablename'], $att['value']));
279                        while ($vals = Sql_fetch_Row($valreq)) {
280                            $value .= $vals[0].',';
281                        }
282                        break;
283                }
284                $att['displayvalue'] = $value;
285                array_push($useratt, $att);
286            }
287            $userlists = array();
288            $userlists = array_merge($_POST['lists'], $userlists);
289            if ($_POST['copyremotelists']) {
290                $req = Sql_Query("select * from {$remote_tables['listuser']},
291          {$remote_tables['list']} where {$remote_tables['listuser']}.listid =
292          {$remote_tables['list']}.id and {$remote_tables['listuser']}.userid = $user[0]");
293                while ($list = Sql_Fetch_Array($req)) {
294                    //  print $list["name"]."<br/>";
295                    array_push($userlists, $list);
296                }
297            }
298            connectLocal();
299            foreach ($useratt as $att) {
300                $localattid = $attributemap[$att['attributeid']];
301                if (!localattid) {
302                    echo $GLOBALS['I18N']->get('Error, no mapped attribute for').' '.$att['name'].'<br/>';
303                } else {
304                    $tname = Sql_Fetch_Row_Query("select tablename from {$tables['attribute']} where id = $localattid");
305                    switch ($att['type']) {
306                        case 'select':
307                        case 'radio':
308                            $valueid = Sql_Fetch_Row_Query(sprintf('select id from %slistattr_%s where name = "%s"',
309                                $table_prefix, $tname[0], $att['displayvalue']));
310                            if (!$valueid[0]) {
311                                Sql_Query(sprintf('insert into %slistattr_%s set name = "%s"',
312                                    $table_prefix, $tname[0], $att['displayvalue']));
313                                $att['value'] = Sql_Insert_id();
314                            } else {
315                                $att['value'] = $valueid[0];
316                            }
317                            break;
318                        case 'checkboxgroup':
319                            $vals = explode(',', $att['displayvalue']);
320                            array_pop($vals);
321                            $att['value'] = '';
322                            foreach ($vals as $val) {
323                                $valueid = Sql_Fetch_Row_Query(sprintf('select id from %slistattr_%s where name = "%s"',
324                                    $table_prefix, $tname[0], $val));
325                                if (!$valueid[0]) {
326                                    Sql_Query(sprintf('insert into %slistattr_%s set name = "%s"',
327                                        $table_prefix, $tname[0], $val));
328                                    $att['value'] .= Sql_Insert_id().',';
329                                } else {
330                                    $att['value'] .= $valueid[0].',';
331                                }
332                            }
333                            $att['value'] = substr($att['value'], 0, -1);
334                            break;
335                    }
336                    if ($att['value']) {
337                        Sql_Query(sprintf('replace into %s set
338              attributeid = %d, userid = %d, value = "%s"',
339                            $tables['user_attribute'], $localattid, $userid, addslashes($att['value'])));
340                    }
341                }
342            }
343        }
344        if (is_array($userlists)) {
345            foreach ($userlists as $list) {
346                if ($listmap[$list['listid']]) {
347                    Sql_Query(sprintf('replace into %s (listid,userid) values(%d,%d)',
348                        $tables['listuser'], $listmap[$list['listid']], $userid));
349                } else {
350                    echo $GLOBALS['I18N']->get('Error, no local list defined for').' '.$list['name'].'<br/>';
351                }
352            }
353        }
354    }
355    echo "$totalusers / $totalusers<br/>";
356    flush();
357    // @@@@ Not sure about this one:
358    printf('%s %d %s %s %d %s<br/>', $GLOBALS['I18N']->get('Done'), $newcnt,
359        $GLOBALS['I18N']->get('new users'),
360        $GLOBALS['I18N']->get('and'),
361        $existcnt, $GLOBALS['I18N']->get('existing users'));
362}
363?>
364
365
366