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