#!/usr/bin/perl -w
#
#  LMS version 1.11-git
#
#  Copyright (C) 2001-2012 LMS Developers
#
#  Please, see the doc/AUTHORS for more information about authors!
#
#  This program is free software; you can redistribute it and/or modify
#  it under the terms of the GNU General Public License Version 2 as
#  published by the Free Software Foundation.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with this program; if not, write to the Free Software
#  Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307,
#  USA.
#
#  $Id$

use strict;
use DBI;
use Config::IniFiles;
use Getopt::Long;
use vars qw($configfile $quiet $help $version $update $fetch $list $merge);
use Cwd;
use LWP::UserAgent;

my $_version = '1.11-git';

my %options = (
	"--config-file|C=s"	=>	\$configfile,
	"--quiet|q"		    =>	\$quiet,
	"--help|h"		    =>	\$help,
	"--version|v"	    =>	\$version,
	"--update|u"	    =>	\$update,
	"--list|l=s"	    =>	\$list,
	"--fetch|f"	        =>	\$fetch,
	"--merge|m"	        =>	\$merge,
);

Getopt::Long::config("no_ignore_case");
GetOptions(%options);

if($help)
{
	print STDERR <<EOF;
lms-teryt, version $_version
(C) 2001-2012 LMS Developers

-C, --config-file=/etc/lms/lms.ini	alternate config file (default: /etc/lms/lms.ini);
-h, --help			print this help and exit;
-v, --version		print version info and exit;
-q, --quiet			suppress any output, except errors;
-u, --update	    update TERYT database
-l, --list=<list>   comma-separated list of state IDs
-f, --fetch	        fetch TERYT XML files from the Internet
-m, --merge	        merge TERYT with LMS database
EOF
	exit 0;
}

if($version)
{
	print STDERR <<EOF;
lms-teryt, version $_version
(C) 2001-2012 LMS Developers

EOF
	exit 0;
}

if(!$configfile)
{
	$configfile = "/etc/lms/lms.ini";
}

if(!$quiet)
{
	print STDOUT "lms-teryt, version $_version\n";
	print STDOUT "(C) 2001-2012 LMS Developers\n";
	print STDOUT "Using file $configfile as config.\n";
}

if(! -r $configfile)
{
	print STDERR "Fatal error: Unable to read configuration file $configfile, exiting.\n";
	exit 1;
}

my $ini = new Config::IniFiles -file => $configfile;
print @Config::IniFiles::errors;

my $dbtype = $ini->val('database', 'type') || 'mysql';
my $dbhost = $ini->val('database', 'host') || 'localhost';
my $dbuser = $ini->val('database', 'user') || 'root';
my $dbpasswd = $ini->val('database', 'password') || '';
my $dbname = $ini->val('database', 'database') || 'lms';

my $url = $ini->val('teryt', 'url') || 'http://www.stat.gov.pl/broker/access/prefile/listPreFiles.jspa';
my $zip_bin = $ini->val('teryt', 'unzip_binary') || '/usr/bin/unzip';

my $dir = $ini->val('teryt', 'dir') || getcwd;
if (!$list) {
    $list = $ini->val('teryt', 'state_list') || '';
}

######## Download TERYT files ###################################################
if ($fetch)
{
    my $ua = LWP::UserAgent->new;
    my $response = $ua->get($url);

    if ($response->is_success)
    {
        my $content = $response->content;
        my $file_url = $url;

	if ($ua->can("show_progress")) {
		$ua->show_progress(1);
	}

        while ($content =~ m/href="(downloadPreFile\.jspa[^\"]+)"/g) {
            my $file = $1;
            $file_url =~ s/[^\/]+$//;
            $file_url .= $file;

            my $response = $ua->get($file_url);
            if ($response->is_success) {
                my $type = $response->filename;

                if ($type !~ /^(SIMC|TERC|ULIC)_[0-9]+\.zip/i) {
                    next;
                }

                my $filename = $dir.'/'.$response->filename;
                # save zip file
                open(FILE, ">$filename") or die $!;
                print FILE $response->content or die $!;
                close(FILE) or die $!;
                # extract file (zip -> xml)
                system("$zip_bin -qd $dir $filename");
            }
        }
    }
    else {
        print STDERR "Error: ".$response->status_line."\n";
    }
}

if (!$update && !$merge)
{
    exit 0;
}

my $dbase;
my $utsfmt;

if($dbtype =~ /mysql/)
{
	$dbase = DBI->connect("DBI:mysql:database=$dbname;host=$dbhost","$dbuser","$dbpasswd", { RaiseError => 1 });
	$dbase->do("SET NAMES utf8");
	$utsfmt = "UNIX_TIMESTAMP()";
}
elsif($dbtype eq "postgres")
{
	$dbase = DBI->connect("DBI:Pg:dbname=$dbname;host=$dbhost","$dbuser","$dbpasswd", { RaiseError => 1 });
	$utsfmt = "EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))";
}
else
{
	print STDERR "Fatal error: unsupported database type: $dbtype, exiting.\n";
	exit 1;
}

sub last_insert_id
{
    if ($dbtype eq "postgres") {
        my $table = shift;
        return $dbase->last_insert_id(undef, undef, $table, undef);
    }

    return $dbase->{'mysql_insertid'};
}

sub get_entry
{
    my $fh = shift;
    my $record = {};

    if (eof($fh)) {
        return;
    }

    while (my $line = <$fh>) {
        if ($line =~ /^<row>/) {
            $record = {};
        }
        elsif ($line =~ /^<\/row>/) {
            return $record;
        }
        elsif ($line =~ /^<col name="([A-Z0-9_]+)">([^<]+)<\/col>/) {
            my $key = $1;
            my $value = $2;

            if ($key =~ /^(WOJ|POW|GMI|RODZ|RODZ_GMI|SYM|SYMPOD|SYM_UL)$/) {
                $value = int($value);
            }
            else {
                $value =~ s/(^\s|\s$)//g;
            }
            #print "$key: $value\n";
            $record->{$key} = $value;
        }
    }

    return $record;
}

sub in_array {
    my $search_for = shift;
    my @arr = @_;

    foreach my $value (@arr) {
        return 1 if $value eq $search_for;
    }
    return 0;
}

my $row;
my $dbq;
my $fh;
my $where = '';
my @ids_arr = map(int, split(/,/, $list));
my $ids = join(',', @ids_arr);

if ($update)
{
######### Get current TERC database ##################################################
my %terc;
$dbq = $dbase->prepare("
    SELECT ident AS woj, '0' AS pow, '0' AS gmi, 0 AS rodz,
        UPPER(name) AS nazwa, id, 'WOJ' AS type
    FROM location_states
    UNION
    SELECT s.ident AS woj, d.ident AS pow, '0' AS gmi, 0 AS rodz,
        d.name AS nazwa, d.id, 'POW' AS type
    FROM location_districts d
    JOIN location_states s ON (d.stateid = s.id)
    UNION
    SELECT s.ident AS woj, d.ident AS pow, b.ident AS gmi, b.type AS rodz,
        b.name AS nazwa, b.id, 'GMI' AS type
    FROM location_boroughs b
    JOIN location_districts d ON (b.districtid = d.id)
    JOIN location_states s ON (d.stateid = s.id)
");
$dbq->execute();
while ($row = $dbq->fetchrow_hashref()) {
    $terc{$row->{'woj'}.':'.$row->{'pow'}.':'.$row->{'gmi'}.':'.$row->{'rodz'}} = {
        'NAZWA' => $row->{'nazwa'},
        'id' => $row->{'id'},
        'type' => $row->{'type'},
    };
}

######## Update TERC database ########################################################
# Prepare some queries
my $state_insert = $dbase->prepare("INSERT INTO location_states (name, ident) VALUES (LOWER(?), ?)");
my $state_update = $dbase->prepare("UPDATE location_states SET name = LOWER(?) WHERE id = ?");
my $state_delete = $dbase->prepare("DELETE FROM location_states WHERE id = ?");
my $district_insert = $dbase->prepare("INSERT INTO location_districts (stateid, name, ident) VALUES (?, ?, ?)");
my $district_update = $dbase->prepare("UPDATE location_districts SET stateid = ?, name = ? WHERE id = ?");
my $district_delete = $dbase->prepare("DELETE FROM location_districts WHERE id = ?");
my $borough_insert = $dbase->prepare("INSERT INTO location_boroughs (districtid, name, ident, type) VALUES (?, ?, ?, ?)");
my $borough_update = $dbase->prepare("UPDATE location_boroughs SET districtid = ?, name = ? WHERE id = ?");
my $borough_delete = $dbase->prepare("DELETE FROM location_boroughs WHERE id = ?");

# Parse TERC file
open($fh, '<', "$dir/TERC.xml") or die $!;
while (my $record = get_entry($fh)) {

    if (!defined($record->{'NAZWA'})) {
        next;
    }

    my $stateid = int($record->{'WOJ'} || 0);
    if ($list && !in_array($stateid, @ids_arr)) {
        next;
    }

    my $districtid = int($record->{'POW'} || 0);
    my $boroughid  = int($record->{'GMI'} || 0);
    my $type       = int($record->{'RODZ'} || 0);
    my $key        = "$stateid:$districtid:$boroughid:$type";
    my $data       = $terc{$key};

    # states (województwa)
    if (!$districtid) {
        if ($data) {
            if ($data->{'NAZWA'} ne $record->{'NAZWA'}) {
                $state_update->execute($record->{'NAZWA'}, $data->{'id'});
            }
            $terc{$key}{'valid'} = 1;
        }
        else {
            $state_insert->execute($record->{'NAZWA'}, $stateid);
            my $insertid = last_insert_id('location_states');
            $terc{$key} = {'id' => $insertid, 'NAZWA' => $record->{'NAZWA'},
                'type' => 'WOJ', 'valid' => 1};
        }
    }
    # districts (powiaty)
    elsif (!$boroughid) {
        my $statekey = "$stateid:0:0:0";
        my $state = $terc{$statekey};
        if ($data) {
            if ($data->{'NAZWA'} ne $record->{'NAZWA'}) {
                $district_update->execute($state->{'id'}, $record->{'NAZWA'}, $data->{'id'});
            }
            $terc{$key}{'valid'} = 1;
        }
        else {
            $district_insert->execute($state->{'id'}, $record->{'NAZWA'}, $districtid);
            my $insertid = last_insert_id('location_districts');
            $terc{$key} = {'id' => $insertid, 'NAZWA' => $record->{'NAZWA'},
                'type' => 'POW', 'valid' => 1};
        }
    }
    # boroughs (gminy)
    else {
        my $districtkey = "$stateid:$districtid:0:0";
        my $district = $terc{$districtkey};
        if ($data) {
            if ($data->{'NAZWA'} ne $record->{'NAZWA'}) {
                $borough_update->execute($district->{'id'}, $record->{'NAZWA'}, $data->{'id'});
            }
            $terc{$key}{'valid'} = 1;
        }
        else {
            $borough_insert->execute($district->{'id'}, $record->{'NAZWA'}, $boroughid, $type);
            my $insertid = last_insert_id('location_boroughs');
            $terc{$key} = {'id' => $insertid, 'NAZWA' => $record->{'NAZWA'},
                'type' => 'GMI', 'valid' => 1};
        }
    }
}
close($fh);

while (my($key, $value) = each(%terc)) {
    if (!$value->{'valid'}) {
        if ($value->{'type'} eq 'GMI') {
            $borough_delete->execute($value->{'id'});
        }
        elsif ($value->{'type'} eq 'POW') {
            $district_delete->execute($value->{'id'});
        }
        elsif ($value->{'type'} eq 'WOJ') {
            $state_delete->execute($value->{'id'});
        }
    }
}

# Close prepared statements
$state_insert->finish();
$state_update->finish();
$state_delete->finish();
$district_insert->finish();
$district_update->finish();
$district_delete->finish();
$borough_insert->finish();
$borough_update->finish();
$borough_delete->finish();


######### Get current SIMC database ##################################################
my %simc;
my %cities_r;
my %cities;
$dbq = $dbase->prepare("
    SELECT s.ident AS woj, d.ident AS pow, b.ident AS gmi, b.type AS rodz_gmi,
        c.ident AS sym, c.name AS nazwa, c.id,
        (CASE WHEN cc.ident IS NOT NULL THEN cc.ident ELSE c.ident END) AS sympod
    FROM location_cities c
    JOIN location_boroughs b ON (c.boroughid = b.id)
    JOIN location_districts d ON (b.districtid = d.id)
    JOIN location_states s ON (d.stateid = s.id)
    LEFT JOIN location_cities cc ON (c.cityid = cc.id)
");
$dbq->execute();
while ($row = $dbq->fetchrow_hashref()) {
    $simc{$row->{'sym'}} = {
        'key' => $row->{'woj'}.':'.$row->{'pow'}.':'.$row->{'gmi'}.':'.$row->{'rodz_gmi'},
        'NAZWA' => $row->{'nazwa'},
        'SYM' => $row->{'sym'},
        'SYMPOD' => $row->{'sympod'},
        'id' => $row->{'id'},
    };
}

######## Update SIMC database ########################################################
# Prepare some queries
my $city_insert = $dbase->prepare("INSERT INTO location_cities (boroughid, name, cityid, ident) VALUES (?, ?, ?, ?)");
my $city_update = $dbase->prepare("UPDATE location_cities SET boroughid = ?, name = ?, cityid = ? WHERE id = ?");
my $city_delete = $dbase->prepare("DELETE FROM location_cities WHERE id = ?");

# Parse SIMC file
open($fh, '<', "$dir/SIMC.xml") or die $!;
while (my $record = get_entry($fh)) {

    if (!defined($record->{'NAZWA'})) {
        next;
    }

    my $stateid = int($record->{'WOJ'} || 0);
    if ($list && !in_array($stateid, @ids_arr)) {
        next;
    }

    my $districtid = int($record->{'POW'} || 0);
    my $boroughid  = int($record->{'GMI'} || 0);
    my $type       = int($record->{'RODZ_GMI'} || 0);
    my $id         = int($record->{'SYM'} || 0);
    my $refid      = int($record->{'SYMPOD'} || 0);
    my $key        = "$stateid:$districtid:$boroughid:$type";
    my $terc_data  = $terc{$key};
    my $data       = $simc{$id};

    if (!$terc_data) {
        print STDERR "Not recognised TERYT-TERC key: $key\n";
    }

    if ($refid == $id) {
        undef $refid;
    }
    # refid not found (refered city is below this one), process later
    elsif (!$simc{$refid}) {
        push(@{$cities_r{$refid}}, {
            'key' => $key, 'NAZWA' => $record->{'NAZWA'}, 'SYM' => $id,
        });
    }
    else {
        $refid = $simc{$refid}->{'id'};
    }
    # entry exists
    if ($data) {
        if ($data->{'NAZWA'} ne $record->{'NAZWA'}
            || $data->{'SYMPOD'} ne $record->{'SYMPOD'}
            || $data->{'key'} ne $key
        ) {
            $city_update->execute($terc_data->{'id'}, $record->{'NAZWA'}, $refid, $data->{'id'});
        }
        # mark data as valid
        $simc{$id}{'valid'} = 1;
        $cities{$id} = $data->{'id'};
    }
    # add new city
    else {
        $city_insert->execute($terc_data->{'id'}, $record->{'NAZWA'}, $refid, $id);
        my $insertid = last_insert_id('location_cities');
        $cities{$id} = $insertid;
    }

    # process references
    if ($cities_r{$id}) {
        while (my $elem = pop(@{$cities_r{$id}})) {
            my $rid = $elem->{'SYM'};
            $data = $simc{$rid};
            # entry exists
            if ($data) {
                if ($data->{'NAZWA'} ne $elem->{'NAZWA'}
                    || $data->{'SYMPOD'} ne $id
                    || $data->{'key'} ne $key
                ) {
                    $city_update->execute($terc_data->{'id'}, $elem->{'NAZWA'}, $cities{$id}, $data->{'id'});
                }
                # mark data as valid
                $simc{$rid}{'valid'} = 1;
                $cities{$rid} = $rid;
            }
            # add new city
            else {
                $city_insert->execute($terc_data->{'id'}, $elem->{'NAZWA'}, $cities{$id}, $rid);
                my $insertid = last_insert_id('location_cities');
                $cities{$rid} = $insertid;
            }
        }
    }
}
close($fh);

while (my($key, $value) = each(%simc)) {
    if (!$value->{'valid'}) {
        $city_delete->execute($value->{'id'});
    }
}

$city_insert->finish();
$city_update->finish();
$city_delete->finish();
undef %terc;
undef %simc;
undef %cities_r;

######### Get current ULIC database ##################################################
my %ulic;
my %str_types;
# street types
$dbq = $dbase->prepare("SELECT id, name FROM location_street_types");
$dbq->execute();
while ($row = $dbq->fetchrow_hashref()) {
    $str_types{$row->{'name'}} = $row->{'id'};
}
$dbq = $dbase->prepare("
    SELECT s.id, s.ident, s.name, s.name2, s.typeid, c.ident AS city
    FROM location_streets s
    JOIN location_cities c ON (s.cityid = c.id)
");
$dbq->execute();
while ($row = $dbq->fetchrow_hashref()) {
    $ulic{$row->{'ident'}.':'.$row->{'city'}} = {
        'NAZWA' => $row->{'name'},
        'NAZWA2' => $row->{'name2'},
        'typeid' => int($row->{'typeid'}),
        'id' => $row->{'id'},
    };
}

######## Update ULIC database ########################################################
# Prepare some queries
my $type_insert = $dbase->prepare("INSERT INTO location_street_types (name) VALUES (LOWER(?))");
my $street_insert = $dbase->prepare("INSERT INTO location_streets (cityid, name, name2, typeid, ident) VALUES (?, ?, ?, ?, ?)");
my $street_update = $dbase->prepare("UPDATE location_streets SET cityid = ?, name = ?, name2 = ?, typeid = ? WHERE id = ?");
my $street_delete = $dbase->prepare("DELETE FROM location_streets WHERE id = ?");

# Parse ULIC file
open($fh, '<', "$dir/ULIC.xml") or die $!;
while (my $record = get_entry($fh)) {

    if (!defined($record->{'NAZWA_1'})) {
        next;
    }

    my $stateid = int($record->{'WOJ'} || 0);
    if ($list && !in_array($stateid, @ids_arr)) {
        next;
    }

    my $cityid = int($record->{'SYM'} || 0);
    my $id     = int($record->{'SYM_UL'} || 0);
    my $key    = "$id:$cityid";
    my $type   = $record->{'CECHA'} || '';
    my $typeid = int($str_types{$type} || 0);
    my $data   = $ulic{$key};
    my $cid    = $cities{$cityid};

    if (!$typeid) {
        $type_insert->execute($type);
        my $insertid = last_insert_id('location_street_types');
        $str_types{$type} = $typeid = int($insertid);
    }
    # entry exists
    if ($data) {
        if ($data->{'NAZWA'} ne $record->{'NAZWA_1'}
            || (defined($record->{'NAZWA_2'}) && (!defined($data->{'NAZWA2'}) || $data->{'NAZWA2'} ne $record->{'NAZWA_2'}))
            || $data->{'typeid'} != $typeid
        ) {
            $street_update->execute($cid, $record->{'NAZWA_1'}, $record->{'NAZWA_2'}, $typeid, $data->{'id'});
        }
        # mark data as valid
        $ulic{$key}{'valid'} = 1;
    }
    # add new street
    else {
        $street_insert->execute($cid, $record->{'NAZWA_1'}, $record->{'NAZWA_2'}, $typeid, $id);
    }
}
close($fh);

while (my($key, $value) = each(%ulic)) {
    if (!$value->{'valid'}) {
        $street_delete->execute($value->{'id'});
    }
}

$type_insert->finish();
$street_insert->finish();
$street_update->finish();
$street_delete->finish();
undef %ulic;
undef %str_types;
undef %cities;
}

######## Merge TERYT with LMS database ###########################################
sub location_update
{
	my $row = shift;
	my $street_get = shift;
	my $city_get = shift;
	my $update = shift;

	my $street_id;
	my $city_id;
	my $house = '';
	my $flat = '';
	my $city;
	my $street;
	my $addr = $row->{'location'};

	# use customer address
	if (!$addr) {
		$addr = $row->{'address'};
		if ($row->{'city'}) {
			$addr = $row->{'city'} . ', ' . $addr;
		}
	}

	my $orig_location = $addr;

	printf("%s (%04d) %s: ", $row->{'name'}, $row->{'id'}, $addr) if not $quiet;

	# get house/flat
	if ($addr =~ /\s(([0-9-\/]+\s*[a-z]*)\s*m\.?\s*([0-9a-z\/\-]+))$/i) {
		$addr = substr($addr, 0, -1 * length($1));
		$house = $2;
		$flat = $3;
	}
	elsif ($addr =~ /\s([0-9-]+\s*[a-z]*\s*)$/i) {
		$addr = substr($addr, 0, -1 * length($1));
		$house = $1;
	}
	elsif ($addr =~ /\s(([0-9-]+\s*[a-z]*)\/([0-9a-z]+))$/i) {
		$addr = substr($addr, 0, -1 * length($1));
		$house = $2;
		$flat = $3;
	}

	# assume format: <City>, <Street>
	if ($addr =~ /^([^,]+),.*/i) {
		$street = substr($addr, length($1)+1);
		$city = $1;
	}
	else {
		$city = $addr;
		$street = ''
	}

	# try to remove prefix UL., AL., PL., SKWER, BULW., RONDO, PARK, RYNEK, SZOSA, DROGA, OS., OGRÓD, WYSPA, WYB.
	$street =~ s/^\s*(((ul\.|pl\.|al\.|bulw\.|os\.|wyb\.)\s*)|((skwer|rondo|park|rynek|szosa|droga|ogród|wyspa)\s+))//i;
	$street =~ s/(^\s+|\s+$)//g;
	$city =~ s/(^\s+|\s+$)//g;

	# find location_street and/or location_city IDs
	if ($street && $city) {
		$street_get->execute($street, $street, "$city%");
		my $srows = $street_get->fetchall_hashref('id');
		if (scalar(keys %$srows) == 1) {
			foreach my $srow (keys %$srows) {
				$street_id = $srows->{$srow}->{'id'};
				$city_id = $srows->{$srow}->{'cityid'};
			}
		}
		else {
			$city_get->execute($street);
			if (my $srow = $city_get->fetchrow_hashref()) {
				$city_id = $srow->{'id'};
				$orig_location =~ s/$city, //;
			}
		}
	}
	elsif ($city) {
		$city_get->execute($city);
		if (my $crow = $city_get->fetchrow_hashref()) {
			$city_id = $crow->{'id'};
		}
	}

	# update node location
	if ($street_id || $city_id) {
		print "found\n" if not $quiet;
		$update->execute($city_id, $street_id, $house, $flat, $orig_location, $row->{'id'});
	}
	else {
		print "not found\n" if not $quiet;
	}
}

if ($merge)
{
	my $node_update = $dbase->prepare("UPDATE nodes SET location_city = ?, location_street = ?,
		location_house = ?, location_flat = ?, location = ? WHERE id = ?");
	my $dev_update = $dbase->prepare("UPDATE netdevices SET location_city = ?, location_street = ?,
		location_house = ?, location_flat = ?, location = ? WHERE id = ?");
	my $street_get = $dbase->prepare("SELECT s.id, s.cityid, (CASE WHEN s.name2 IS NULL THEN s.name ELSE s.name2 || ' ' || s.name END) AS name 
		FROM location_streets s
		JOIN location_cities c ON (s.cityid = c.id)
		WHERE ("
		.($dbtype =~ /mysql/ ? "(CASE WHEN s.name2 IS NULL THEN s.name ELSE CONCAT(s.name2, ' ', s.name) END)"
			: "(CASE WHEN s.name2 IS NULL THEN s.name ELSE s.name2 || ' ' || s.name END)")." = ? OR s.name = ?)
			AND c.name LIKE ?
		ORDER BY c.cityid");
	my $city_get = $dbase->prepare("SELECT id
		FROM location_cities
		WHERE name = ?
		ORDER BY cityid LIMIT 1");

	# nodes
	$dbq = $dbase->prepare("SELECT n.id, n.name, n.location, c.address, c.city
		FROM nodes n
		JOIN customers c ON (c.id = n.ownerid)
		WHERE n.location_city IS NULL AND n.location_street IS NULL
			AND (n.location <> '' OR c.address <> '')");
	$dbq->execute();
	while ($row = $dbq->fetchrow_hashref()) {
		location_update($row, $street_get, $city_get, $node_update);
	}

	# netdevices
	$dbq = $dbase->prepare("SELECT id, name, location
		FROM netdevices WHERE location_city IS NULL AND location_street IS NULL
		AND location <> ''");
	$dbq->execute();
	while ($row = $dbq->fetchrow_hashref()) {
		location_update($row, $street_get, $city_get, $dev_update);
	}

	$node_update->finish();
	$dev_update->finish();
	$street_get->finish();
	$city_get->finish();
}

$dbq->finish();
$dbase->disconnect();
