#!/usr/bin/php
<?php

// https:// github.com/mevdschee/php-crud-api
include('class.Transaction.php');
// include('functions.php');

$transaction = new Transaction();

// get transactions_updates
$updates = $transaction->updateTransactionsByUpdates();

#var_dump($updates); die;

foreach ($updates as $update) {

    // get transaction
    $sql = <<<EOF
    SELECT
        *
    FROM
        transactions
    WHERE 1
        AND :whereClause
    ;
EOF;

    if ($update['transaction_key'] == "transaction_id") {
        $whereClause = str_replace(':value', $update['transaction_value_old'], "transaction_id = ':value' and binary transaction_id  = ':value'");
    } else {
        $whereClause = str_replace(':id', $update['transaction_id'], "id = ':id'");;
    }

    $sql = str_replace(':whereClause', $whereClause, $sql);

    #echo $sql . "\r\n";

    $statement = $transaction->db->prepare($sql);
    $statement->execute();
    $result = $statement->fetchAll(PDO::FETCH_ASSOC);

    if ($result) {

        // get transactions by transaction_id and product_id
        // and binary transaction_id = ':transaction_id'
        $sql = <<<EOF
            SELECT
                *
            FROM
                transactions
            WHERE 1
                AND transaction_id = ':transaction_id' and binary transaction_id = ':transaction_id'
                :product_id
                AND id >= ':id'
            ;
EOF;

        $sql = str_replace(':transaction_id', $result[0]['transaction_id'], $sql);
        $sql = str_replace(':product_id', strlen($result[0]['product_id']) ? "AND product_id = '" . $result[0]['product_id'] . "'" : 'AND product_id IS NULL', $sql);
        $sql = str_replace(':id', $result[0]['id'], $sql);

        #echo $sql . "\r\n";

        $statement = $transaction->db->prepare($sql);
        $statement->execute();
        $results = $statement->fetchAll(PDO::FETCH_ASSOC);

        if ($result) {

            foreach ($results as $result) {

                // update transactions by transactions_updates
                $sql = <<<EOF
                    UPDATE
                        transactions
                    SET
                        :transaction_key = ':transaction_value_new'
                    WHERE 1
                        AND id = ':id'
                    ;
EOF;

                $sql = str_replace(':transaction_key', $update['transaction_key'], $sql);
                $sql = str_replace(':transaction_value_new', $update['transaction_value_new'], $sql);
                $sql = str_replace(':id', $result['id'], $sql);

                $sql = preg_replace('/[\n]/', '', $sql);
                $sql = preg_replace('/[\s\s]+/', ' ', $sql);
                $sql = preg_replace('/^ +/', '', $sql);

                echo $sql . "\r\n";

                #echo "3. " . $sql . "<br>";

                // $statement = $transaction->db->prepare($sql);
                // $statement->execute();

                // recalculate commission values
                echo 'UPDATE IGNORE transactions SET commission_net = (commission_gross * (commission_percentage/100)) WHERE 1 AND id = "' . $result['id'] . '";' . "\r\n";
                echo 'UPDATE IGNORE transactions SET commission_fee = (commission_gross - commission_net) WHERE 1 AND id = "' . $result['id'] . '";' . "\r\n";
            }
        }
    } else {
        #echo '# No Data Found, so we ignore this record<br>';
    }
}

// calculate empty commission values
echo 'UPDATE IGNORE transactions SET commission_net = (commission_gross * (commission_percentage/100)) WHERE 1 AND commission_net IS NULL;' . "\r\n";
echo 'UPDATE IGNORE transactions SET commission_fee = (commission_gross - commission_net) WHERE 1 AND commission_fee IS NULL;' . "\r\n";

// We now adding the source with an SKID with a attached $ sign, but we want to split this to target and source
echo "UPDATE `transactions` SET `original_target` = `target`, `target` = REPLACE(REPLACE(`target`, '[', ''),']','') WHERE `target` LIKE '%SK%' AND (`target` LIKE '%[%' OR `target` LIKE '%]%');" . "\r\n";
echo "UPDATE `transactions` SET `original_target` = `target`, `target` = FN_CLEAN(`target`) WHERE `id` >= 300000000 AND `target` LIKE '%SK%' AND RIGHT(`target`, 1) NOT IN ('0' , '1', '2', '3', '4', '5', '6', '7', '8', '9') AND `target` NOT LIKE '%$%';" . "\r\n";
echo "UPDATE `transactions` SET `original_target` = `target`, `source` = FN_SPLIT(`target`, '$', 2), `target` = FN_SPLIT(`target`, '$', 1) WHERE `id` >= 300000000 AND `target` LIKE '%$%';" . "\r\n";