<?php
/**
 * Created by PhpStorm.
 * User: Admin
 * Date: 07.09.2016
 * Time: 17:48
 */

if (php_sapi_name() != 'cli') {
    exit();
}

require_once('../../Bin/init.php');
require_once(ROOT_FOLDER . 'Bin/Security.php');
require_once(ROOT_FOLDER . '/Bin/constants.php');

$query = new Bin_Query();
$table = 'transaction_relations';
$viewQuery = "SELECT trans.manual_transaction_id,
              trans.notes
              FROM manual_transactions AS trans
              WHERE trans.status = 1 AND trans.notes LIKE 'TB from #%'
              UNION
              SELECT rej_trans.manual_transaction_id,
              rej_trans.notes
              FROM manual_transactions_rejected AS rej_trans
              WHERE rej_trans.status = 1 AND rej_trans.notes LIKE 'TB from #%'";

$query->executeQuery($viewQuery);
$transactions = $query->records;
$transactionIds = array();
$output = array();
foreach ($transactions as $transaction) {
    $child_id = (int)$transaction['manual_transaction_id'];
    if (stripos($transaction['notes'], 'TB from #') !== false) {
        $n = str_replace('TB from #', '', $transaction['notes']);
        $n = explode(' ', $n);
        $parent_id = (int)$n[0];
        $output[] = array('parent_transaction_id' => $parent_id, 'transaction_id' => $child_id);
        $transactionIds[] = $child_id;
    }
}

$updateSqlQuery = "UPDATE $table SET parent_transaction_id = CASE ";
foreach ($output as $value) {
    $parent_id = empty($value['parent_transaction_id']) ? 'NULL' : $value['parent_transaction_id'];
    $child_id = $value['transaction_id'];
    $updateSqlQuery .= "WHEN transaction_id = '$child_id' THEN '$parent_id' ";
}
$updateSqlQuery .= "END WHERE transaction_id IN (" . implode(',', $transactionIds) . ")";
$query->executeQuery($updateSqlQuery);

echo "Found and updated " . count($output) . " relations.";