Zobrazení zdroje stránky Import XLS databaze do Freenetisu

<source lang="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 "<table>\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 "<table>\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 ...
    
            

?>
</source>