<?

$dbconn = pg_connect("host=192.168.0.19 port=5432 dbname=taxi2 user=postgres password=root")
or die('Could not connect: ' . pg_last_error());

$streets = get_ss();
foreach($streets AS $street){

    $streetalien = get_sa($street['idx_streetaliens']);

    if(stripos($streetalien['name'],'(САМАРА)') === false){
        continue;
    }

    $builds = get_bl($streetalien['idx']);

    $streetalien['name'] = str_replace('(САМАРА)','(САМАРА для НСК)',$streetalien['name']);
    $idx_sa = insert_sa($streetalien);//$street['idx_streetaliens'];//

    echo "idx_sa: $idx_sa\n";

    $street['idx_streetaliens'] = $idx_sa;
    $idx_ss = $street['idx'];
    update_ss($idx_ss,$idx_sa);

    echo "idx_ss: $idx_ss\n";

    $idx_bl = 0; $first = true;
    foreach($builds AS $build){
        $build['idx_street'] = $idx_sa;
        $idx_bl = insert_bl($build);
        if($first){
            echo "idx_bl: $idx_bl";
            $first = false;
        }
    }
    echo " - $idx_bl\n";

}

pg_close($dbconn);

function get_ss(){

    $res = array();

    $query = "SELECT * FROM ulica WHERE cityid = 4";// LIMIT 5 OFFSET 10
    $result = pg_query($query) or die('Ошибка запроса: ' . pg_last_error());
    while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
        $res[] = $line;
    }
    pg_free_result($result);

    return $res;

}
function get_sa($id){

    $res = array();

    $query = "SELECT * FROM streetaliens WHERE idx = $id LIMIT 1";
    $result = pg_query($query) or die('Ошибка запроса: ' . pg_last_error());
    while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {

        $res = $line;

        $line_utf = $line;
        $line_utf['name'] = iconv('cp1251','utf8',$line_utf['name']);
        echo $line_utf['name']."\n";//print_r($line_utf);

    }
    pg_free_result($result);

    return $res;

}
function get_bl($id){

    $res = array();

    $query = "SELECT * FROM buildinglocation WHERE idx_street = $id";
    $result = pg_query($query) or die('Ошибка запроса: ' . pg_last_error());
    while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
        $res[] = $line;
    }
    pg_free_result($result);

    return $res;

}

function update_ss($idx_ss,$idx_sa){

    $result = pg_query("
        UPDATE ulica SET idx_streetaliens = $idx_sa WHERE idx = $idx_ss
        ") or die('Ошибка запроса: ' . pg_last_error());

}

function insert_sa($row){

    $result = pg_query("
        INSERT INTO streetaliens
        (
          name
          ) values
        (
          '$row[name]'
          ) RETURNING idx;
        ") or die('Ошибка запроса: ' . pg_last_error());
    $insert_id = pg_fetch_array($result);

    return $insert_id[0];

}
function insert_ss($row){

    $row['rayonid'] = intval($row['rayonid']);

    $result = pg_query("
        INSERT INTO ulica
        (
          ulica,rayonid,comment,deleted,tolist,
          name,idx_streetaliens,canreject,zagorod,specnotice,
          ulicacategoryid,cityid,displaypriority,kladr_code,fts
          ) values
        (
          '$row[ulica]',$row[rayonid],'$row[comment]','$row[deleted]','$row[tolist]',
          '$row[name]','$row[idx_streetaliens]','$row[canreject]','$row[zagorod]','$row[specnotice]',
          '$row[ulicacategoryid]','$row[cityid]','$row[displaypriority]','$row[kladr_code]','$row[fts]'
          ) RETURNING idx;
        ") or die('Ошибка запроса: ' . pg_last_error());
    $insert_id = pg_fetch_array($result);

    return $insert_id[0];

}
function insert_bl($row){

    $result = pg_query("
        INSERT INTO buildinglocation
        (
          idx_street,house,latitude,longitude,name
          ) values
        (
          '$row[idx_street]','$row[house]','$row[latitude]','$row[longitude]','$row[name]'
          ) RETURNING idx;
        ") or die('Ошибка запроса: ' . pg_last_error());
    $insert_id = pg_fetch_array($result);

    return $insert_id[0];

}