<?php

function getArrayElementByKey($array, $key) {
    if (array_key_exists($key, $array)) {
        return $array[$key];
    } else {
        return null;
    }
}

$csv = array_map('str_getcsv', file('1.1.csv'));

$utf_csv = [];
$utf_rows = [];

// меняем кодировку на UTF-8, парсим
foreach ($csv as $rows) {
    $utf_row = mb_convert_encoding(implode(',', $rows), 'UTF-8', 'Windows-1251');
    $utf_rows = explode(';', $utf_row);
    array_push($utf_csv, $utf_rows);
}

unset($csv);
unset($rows);
unset($utf_row);
unset($utf_rows);

//убираем первый элемент массива - заголовки
array_shift($utf_csv);

$temp = [];
$parsed_data = [];

// оставляем только город Хабаровск, адрес + money_collecting_way, bank_bik, money_ppl_collected
foreach ($utf_csv as $rows) {
    // удаляем ненужную нам информацию из массива
    array_splice($rows, 0, 5);
    array_splice($rows, 1, 10);
    array_splice($rows, 2, 1);
    array_splice($rows, 4, 13);

    // делим адрес на составные части
    $address_split = explode(', ', $rows[0]);

    // город
    $city = getArrayElementByKey(explode(' ', $address_split[0]), 0);

    // улица
    $street_name = getArrayElementByKey(explode(' ', $address_split[1]), 0);

    // кв-л, ул, б-р и т.д.
    $street_type = getArrayElementByKey(explode(' ', $address_split[1]), 1);

    // номер дома (если есть корпус, то преобразуем)
    $house_num = getArrayElementByKey(explode(' ', $address_split[2]), 2);

    if (array_key_exists(3, $address_split)) {
        if (0 < substr_count($address_split[3], 'корпус')) {
            $house_num = $house_num . 'к' . getArrayElementByKey(explode(' ', $address_split[3]), 1);
        }
    }

    $money_collecting_way = (int)$rows[1];

    $bank_bik = (int)$rows[2];

    // меняем запятую на точку и превращаем в число
    $money_ppl_collected = str_replace(',', '.', $rows[3]);
    $money_ppl_collected = (float)$money_ppl_collected;

    if ($city == 'Хабаровск') {
        array_push($temp, $city, $street_name, $street_type, mb_strtolower($house_num), $money_collecting_way, $bank_bik, $money_ppl_collected);
        array_push($parsed_data, $temp);
        $temp = [];
    }
}

unset($utf_csv);
unset($rows);
unset($address_split);
unset($city);
unset($street_name);
unset($street_type);
unset($house_num);
unset($money_collecting_way);
unset($bank_bik);
unset($money_ppl_collected);
unset($temp);

$host = '127.0.0.1';
$db   = 'narayone';
$user = 'root';
$pass = '123';
$charset = 'utf8';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
$pdo = new PDO($dsn, $user, $pass, $opt);

$query = 'SELECT house.id, house_restore_budget.house_id, house.aoguid, ' .
            'new.FORMALNAME, new.SHORTNAME, house.HOUSENUM, house_restore_budget.money_ppl_collected ' .
            'FROM narayone.house INNER JOIN narayone.new ON house.AOGUID = new.AOGUID ' .
            'LEFT JOIN narayone.house_restore_budget ON house.id = house_restore_budget.house_id';

$db_data = [];

$stmt = $pdo->query($query);

while ($row = $stmt->fetch()) {
    array_push($db_data, $row);
}

$pdo = null;
unset($query);
unset($row);

$has_data = [];
$has_no_data = [];
foreach ($db_data as $data) {
    if ($data['house_id'] === null) {
        array_push($has_no_data, $data);
    } else {
        array_push($has_data, $data);
    }
}

unset($db_data);
unset($data);

$pdo = new PDO($dsn, $user, $pass, $opt);
$query = 'UPDATE narayone.house_restore_budget SET house_restore_budget.money_ppl_collected = ? WHERE house_restore_budget.house_id = ?';
$stmt = $pdo->prepare($query);

$updated = [];
$updated_data = [];

// будет перезаписывать постоянно, потому что БД округляет числа (всегда по-разному)
foreach ($has_data as $data) {
    foreach ($parsed_data as $rows) {
        if ($rows[1] == $data['FORMALNAME'] && $rows[2] == $data['SHORTNAME'] && $rows[3] == mb_strtolower($data['HOUSENUM']) && $rows[6] != $data['money_ppl_collected']) {
            $updated_data['id'] = $data['id'];
            $updated_data['money_ppl_collected_old'] = $data['money_ppl_collected'];
            $updated_data['money_ppl_collected_new'] = $rows[6];
            array_push($updated, $updated_data);
            try {
                $stmt->execute(array($rows[6], $data['house_id']));
            } catch (PDOException $e) {
                echo $e->getMessage();
            }
        }
    }
}

$pdo = null;
unset($query);
unset($data);
unset($rows);
unset($updated_data);

$pdo = new PDO($dsn, $user, $pass, $opt);
$query = 'INSERT INTO narayone.house_restore_budget ' .
            '(' .
                'house_restore_budget.house_id, ' .
                'house_restore_budget.demolition, ' .
                'house_restore_budget.money_collecting_way, ' .
                'house_restore_budget.bank_bik, ' .
                'house_restore_budget.money_ppl_collected, ' .
                'house_restore_budget.cofunding_total, ' .
                'house_restore_budget.cofunding_mo_budget, ' .
                'house_restore_budget.cofunding_subject_rf_budget, ' .
                'house_restore_budget.cofunding_rf_budget, ' .
                'house_restore_budget.cofunding_fond_budget' .
            ') VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
$stmt = $pdo->prepare($query);

$created = [];
$created_data = [];

foreach ($has_no_data as $data) {
    foreach ($parsed_data as $rows) {
        if ($rows[1] == $data['FORMALNAME'] && $rows[2] == $data['SHORTNAME'] && $rows[3] == mb_strtolower($data['HOUSENUM'])) {
            $created_data['id'] = $data['id'];
            $created_data['money_ppl_collected_old'] = $data['money_ppl_collected'];
            $created_data['money_ppl_collected_new'] = $rows[6];
            array_push($created, $created_data);
            try {
                $stmt->execute([
                    $data['id'], // house_id
                    0, // demolition
                    $rows[4], // money_collecting_way
                    $rows[5], // bank_bik
                    $rows[6], // money_ppl_collected
                    0, // cofunding_total
                    0, // cofunding_mo_budget
                    0, // cofunding_subject_rf_budget
                    0, // cofunding_rf_budget
                    0  // cofunding_fond_budget
                ]);
            } catch (PDOException $e) {
                echo $e->getMessage();
            }
        }
    }
}

$pdo = null;
unset($query);
unset($data);
unset($rows);
unset($created_data);

unset($host);
unset($db);
unset($user);
unset($pass);
unset($charset);
unset($dsn);
unset($opt);
unset($pdo);

echo 1;