Import XLS databaze do Freenetisu
Z Wiki UnArt Slavičín
Skočit na navigaciSkočit na vyhledávání
<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 "
