Import XLS databaze do Freenetisu

<php> <?php

   function removecz($czstring) {
     return strtr($czstring,
       "\xE1\xE8\xEF\xEC\xE9\xED\xF2\xF3\xF8\x9A\x9D\xF9\xFA\xFD\x9E\xF4\xBC".
       "\xBE\xC1\xC8\xCF\xCC\xC9\xCD\xC2\xD3\xD8\x8A\x8D\xDA\xDD\x8E\xD2\xD9\xEF\xCF",
       
       "\x61\x63\x64\x65\x65\x69\x6E\x6F\x72\x73\x74\x75\x75\x79\x7A\x6F\x4C".
       "\x6C\x41\x43\x44\x45\x45\x49\x4E\x4F\x52\x53\x54\x55\x59\x5A\x4E\x55\x64\x44");
   }
   $link = mysql_connect('localhost', 'root', , TRUE) 
       or die('Could not connect: ' . mysql_error());
   echo 'Connected OK';
   mysql_select_db('freenetis') or die('Could not select database');
   mysql_query("SET CHARACTER SET 'utf8'") or die('Could not set charset');
   mysql_query("SET collation_connection=utf8_czech_ci") or die('Could not set collation');

// echo "

\n"; /** Vyber všechny IP ze staré DB, jejichž rozsah není v nové DB */ $kontrolaIP=" (select ap, ip_pc as ip, mac_pc as mac, 'pc' as typ, adresa_ulice, jmeno, prijmeni from aaajeto LEFT JOIN ip_addresses ON substring_index(ip_addresses.ip_address, ".", 3)=substring_index(aaajeto.ip_pc, ".", 3) where (ip_pc is not null or mac_pc is not null) and ip_address is null )UNION ( select ap, ip_krabicky as ip, mac_krabicky as mac, 'krabicka' as typ, adresa_ulice, jmeno, prijmeni from aaajeto LEFT JOIN ip_addresses ON substring_index(ip_addresses.ip_address, ".", 3)=substring_index(aaajeto.ip_krabicky, ".", 3) where (ip_krabicky is not null or mac_krabicky is not null) and ip_address is null )UNION ( select ap, ip2 as ip, mac2 as mac, 'pc' as typ, adresa_ulice, jmeno, prijmeni from aaajeto LEFT JOIN ip_addresses ON substring_index(ip_addresses.ip_address, ".", 3)=substring_index(aaajeto.ip2, ".", 3) where (ip2 is not null or mac2 is not null) and ip_address is null ) UNION ( select ap, ip3 as ip, mac3 as mac, 'pc' as typ, adresa_ulice, jmeno, prijmeni from aaajeto LEFT JOIN ip_addresses ON substring_index(ip_addresses.ip_address, ".", 3)=substring_index(aaajeto.ip3, ".", 3) where (ip3 is not null or mac3 is not null) and ip_address is null ) order by ip "; /** Vyber všechny IP adresy z nové databáze, jejichž rozsah není použitý ve staré databázi * Tento dotaz je vhodný akorát pro výpis routerů, na které nejsou připojení uživatelé * */ $kontrolaIProuteru="select ip_addresses.* from ip_addresses LEFT JOIN aaajeto ON substring_index(ip_addresses.ip_address, ".", 3)=substring_index(aaajeto.ip_pc, ".", 3) where aaajeto.ip_pc is null;"; /** * select aaajeto.ip_krabicky from aaajeto LEFT JOIN ip_addresses * ON substring_index(ip_addresses.ip_address, ".", 3)=substring_index(aaajeto.ip_pc, ".", 3) * where ip_address is null; */ $dotazPC_krabicky=" ( SELECT DISTINCT new_db_id, 'pc' as typ, typ_krabicky_anteny, ip, t1.mac, lower(dhcp) as dhcp, pripojujici_technik, technik2, users.surname as user_surname, users.name as user_name, users.id as user_id, technik.surname as technik_surname, technik.name as technik_name, technik.id as technik_id, segments.name as segment_name, segments.id as segment_id FROM ( (SELECT typ_krabicky_anteny, ip_pc as ip, mac_pc as mac, pripojujici_technik, technik2, new_db_id, dhcp FROM aaajeto where ip_pc is not null) union (SELECT typ_krabicky_anteny, ip2 as ip, mac2 as mac, pripojujici_technik, technik2, new_db_id, dhcp FROM aaajeto where ip2 is not null) union (SELECT typ_krabicky_anteny, ip3 as ip, mac3 as mac, pripojujici_technik, technik2, new_db_id, dhcp FROM aaajeto where ip3 is not null) ) as t1 LEFT JOIN users on new_db_id=users.member_id LEFT JOIN users as technik on pripojujici_technik=concat(technik.surname, ' ', technik.name) LEFT JOIN ip_addresses ON substring_index(ip_addresses.ip_address, ".", 3)=substring_index(t1.ip, ".", 3) LEFT JOIN ifaces on ip_addresses.iface_id=ifaces.id LEFT JOIN segments on ifaces.segment_id=segments.id WHERE t1.ip is not null or t1.mac is not null ) union ( SELECT DISTINCT new_db_id, 'klient' as typ, typ_krabicky_anteny, ip_krabicky as ip, mac_krabicky as mac, lower(dhcp) as dhcp, pripojujici_technik, technik2, users.surname as user_surname, users.name as user_name, users.id as user_id, technik.surname as technik_surname, technik.name as technik_name, technik.id as technik_id, segments.name as segment_name, segments.id as segment_id FROM aaajeto left JOIN ip_addresses ON substring_index(ip_addresses.ip_address, ".", 3)=substring_index(aaajeto.ip_krabicky, ".", 3) LEFT JOIN users on new_db_id=users.member_id LEFT JOIN users as technik on pripojujici_technik=concat(technik.surname, ' ', technik.name) LEFT JOIN ifaces on ip_addresses.iface_id=ifaces.id LEFT JOIN segments on ifaces.segment_id=segments.id WHERE ip_krabicky is not null or mac_krabicky is not null ) order by dhcp "; $result = mysql_query($dotazPC_krabicky) or die('Query failed: ' . mysql_error()); // echo "\n"; while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { /** * Nyní vložíme do databáze všechna zařízení, která uživatel vlastní: */ $typ=$line["typ"]; $typKrabicky=$line["typ_krabicky_anteny"]; if ($typKrabicky=="") $description="NULL"; else $description="'$typKrabicky'"; $ip=$line["ip"]; $mac=$line["mac"]; $dhcp=$line["dhcp"]; $user_id=$line["user_id"]; $technik_id=$line["technik_id"]; $segment_id=$line["segment_id"]; $comment==$line["technik2"]; $device_name=$typ." ".$line["$user_name"]." ".$line["user_surname"]; if ($ip!="" || $mac!="") { $res_ins = mysql_query("INSERT INTO DEVICES (user_id, name, type, description, comment, engineer_id) VALUES ($user_id, '$device_name', '$typ', $description, '$comment', $technik_id)" ); if (!$res_ins) echo 'Invalid query - insert device: ' . mysql_error(); else { $device_id=mysql_insert_id(); $res_ins=mysql_query("INSERT INTO ifaces (device_id, segment_id, MAC) VALUES ($device_id, $segment_id, $mac) "); if (!$res_ins) echo 'Invalid query - insert iface: ' . mysql_error(); else if ($ip!="" && $ip!="NULL") { $iface_id=mysql_insert_id(); $res_ins=mysql_query("INSERT INTO ip_addresses (iface_id, subnet_id, IP_address) VALUES ($iface_id, 1, '$ip') "); } // if ($ip!="" ... } } } //foreach ($IPs ... ?> </php>