#!/usr/bin/perl -w

use DBI;
use Socket;
use integer;


    sub exec_query {
        $sth = $dbh->prepare($query) or die "ERROR: " . $dbh->errstr . "\nLINE: $count\n";
        $sth->execute or die "ERROR: " . $dbh->errstr . "\nLINE: $count\n";
        $dbh->commit;
        undef $query;
    };

$database = "phones";
$hostname = "212.124.89.226";
$user     = "gf";
$password = "nema";

%cities = (
    BLAGOEVGRAD=>'1',
    BOURGAS=>'2',
    DOBRICH=>'3',
    GABROVO=>'4',
    KIUSTENDIL=>'5',
    LOVECH=>'6',
    MONTANA=>'7',
    PERNIK=>'8',
    PLEVEN=>'9',
    PLOVDIV=>'10',
    RAZGRAD=>'11',
    RUSE=>'12',
    SHUMEN=>'13',
    SILISTRA=>'14',
    SLIVEN=>'15',
    SOFIA=>'16',
    TARGOVISHTE=>'17',
    VARNA=>'18',
    VELIKO_TARNOVO=>'19',
    VIDIN=>'20',
    VRATSA=>'21'
);

$dbh = DBI->connect("DBI:Pg:dbname=$database;host=$hostname", $user, $password) || die "Error: " . $dbh->errstr . "\n";
$dbh->{AutoCommit} = 0;

foreach $CITY (@ARGV) {
    $city = $cities{$CITY};
    open FILE, "< $CITY" or die "Cant open file.\n";

    $x=0;
    $count = 0;
    foreach $line (<FILE>) {
        $count++;
        if ($x > 1500) {
    	    $x = 0;
	    &exec_query;
        };
        chomp $line;
        $line =~ s/0x00/ /g;
        $line =~ s/  / /g;
        $line =~ s/ ;/;/g;
        ($name,$phone,$address) = split(";",$line);
        $name =~ s/. $//;
        $phone =~ s/. $//;
        $address =~ s/. $//;

        $name_sql    = $dbh->quote($name);
        $phone_sql   = $dbh->quote($phone);
        $address_sql = $dbh->quote($address);
        $city_sql    = $dbh->quote($city);

        $query .= "INSERT INTO phones (name,phone,address,city) VALUES ($name_sql,$phone_sql,$address_sql,$city_sql);";
        $x++;
    };
    &exec_query;
    close FILE;
};

$sth->finish;
$dbh->disconnect();