Import XLS databaze do Freenetisu
Z Wiki UnArt Slavičín
<?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 ...
?>
