#!/usr/bin/php
<?php

include('class.Transaction.php');
include('functions.php');

$transaction = new Transaction();

/*
$id = 40;

$fields = $transaction->get($id);
$keys = $transaction->getJsonKeys($id);

var_dump($fields);
var_dump($keys);

foreach ($fields as $field) {

	$field = $transaction->isTimestamp($field);
	var_dump($field);

}
*/

$results = $transaction->updateTransactions();

if (!function_exists('str_contains')) {
    function str_contains(string $haystack, string $needle): bool
    {
        return '' === $needle || false !== strpos($haystack, $needle);
    }
}

$mapping = array();

/*
    ref: https://  app.strackr.com/ressources/docs/api/#api-Reports-Transaction_list
*/
$mapping['strackr'] = array(
    'shop'                      => 'advertiser_name',   //  Shop name from network
    'target'                    => '0',                 //  TBD - currently configured acording to cj network
    'transaction_status'        => 'status_id',         //  strackr status id. Allowed values: pending, declined, confirmed
    'value'                     => 'price',             //  Amount of the transaction.
    'commission_gross'          => 'revenue',           //  Revenue from the transaction.
    'commission_reference'      => '',                  //  Not in use
    'transaction_id'            => 'id',                //  strackr transactionId. Use source_id for Transaction ID from the network.
    'product_id'                => '',                  //  TBD
    'click_date'                => 'clicked',           //  Click user datetime (ISO 8601).
    'transaction_date'          => 'sold',              //  Transaction datetime (ISO 8601).
    'description'               => 'name',              //  TBD Aggregate all bascket items' name (which is Name of the product.)?
    'currency'                  => 'currency',          //  Currency ISO 4217 code.
    'ip'                        => '',                  //  N/A
    'paid'                      => 'paid',              //  Is the transaction paid out? (boolean)
    'paid_on'                   => 'paid_updated',      //  Date of payment (ISO 8601)
    'network_id'                => 'network_id',        //  Id of the Strackr network
    'order_id'                  => 'order_id',          //  Id of the Strackr order
);

$mapping['strackr-v4'] = array(
    'shop'                      => 'advertiser_name',   //  Shop name from network
    'target'                    => '0',                 //  TBD - currently configured acording to cj network
    'transaction_status'        => 'status_id',         //  strackr status id. Allowed values: pending, declined, confirmed
    'value'                     => 'order_amount',      //  Amount of the transaction (v4: price → order_amount).
    'commission_gross'          => 'revenue',           //  Revenue from the transaction.
    'commission_reference'      => '',                  //  Not in use
    'transaction_id'            => 'id',                //  strackr transactionId. Use source_id for Transaction ID from the network.
    'product_id'                => '',                  //  TBD
    'click_date'                => 'clicked_at',        //  Click user datetime (ISO 8601) (v4: clicked → clicked_at).
    'transaction_date'          => 'sold_at',           //  Transaction datetime (ISO 8601) (v4: sold → sold_at).
    'description'               => 'name',              //  v4: name not available without basket expansion, using advertiser_name instead
    'currency'                  => 'currency',          //  Currency ISO 4217 code.
    'ip'                        => '',                  //  N/A
    'paid'                      => 'is_paid',           //  Is the transaction paid out? (boolean) (v4: paid → is_paid)
    'paid_on'                   => 'paid_updated_at',   //  Date of payment (ISO 8601) (v4: paid_updated → paid_updated_at)
    'network_id'                => 'network_id',        //  Id of the Strackr network
    'order_id'                  => 'order_id',          //  Id of the Strackr order
);

$mapping['strackr-daisycon'] = array(
    'shop'                      => 'advertiser_name',   //  Shop name from network
    'target'                    => '0',                 //  TBD - currently configured acording to cj network
    'transaction_status'        => 'status_id',         //  strackr status id. Allowed values: pending, declined, confirmed
    'value'                     => 'price',             //  Amount of the transaction.
    'commission_gross'          => 'revenue',           //  Revenue from the transaction.
    'commission_reference'      => '',                  //  Not in use
    'transaction_id'            => 'id',                //  strackr transactionId. Use source_id for Transaction ID from the network.
    'product_id'                => '',                  //  TBD
    'click_date'                => 'clicked',           //  Click user datetime (ISO 8601).
    'transaction_date'          => 'sold',              //  Transaction datetime (ISO 8601).
    'description'               => 'name',              //  TBD Aggregate all bascket items' name (which is Name of the product.)?
    'currency'                  => 'currency',          //  Currency ISO 4217 code.
    'ip'                        => '',                  //  N/A
    'paid'                      => 'paid',              //  Is the transaction paid out? (boolean)
    'paid_on'                   => 'paid_updated',      //  Date of payment (ISO 8601)
    'network_id'                => 'network_id',        //  Id of the Strackr network
    'order_id'                  => 'order_id',          //  Id of the Strackr order
);

$mapping['strackr-tradedoubler'] = array(
    'shop'                      => 'advertiser_name',   //  Shop name from network
    'target'                    => '0',                 //  TBD - currently configured acording to cj network
    'transaction_status'        => 'status_id',         //  strackr status id. Allowed values: pending, declined, confirmed
    'value'                     => 'price',             //  Amount of the transaction.
    'commission_gross'          => 'revenue',           //  Revenue from the transaction.
    'commission_reference'      => '',                  //  Not in use
    'transaction_id'            => 'id',                //  strackr transactionId. Use source_id for Transaction ID from the network.
    'product_id'                => '',                  //  TBD
    'click_date'                => 'clicked',           //  Click user datetime (ISO 8601).
    'transaction_date'          => 'sold',              //  Transaction datetime (ISO 8601).
    'description'               => 'name',              //  TBD Aggregate all bascket items' name (which is Name of the product.)?
    'currency'                  => 'currency',          //  Currency ISO 4217 code.
    'ip'                        => '',                  //  N/A
    'paid'                      => 'paid',              //  Is the transaction paid out? (boolean)
    'paid_on'                   => 'paid_updated',      //  Date of payment (ISO 8601)
    'network_id'                => 'network_id',        //  Id of the Strackr network
    'order_id'                  => 'order_id',          //  Id of the Strackr order
);

$mapping['strackr-booking'] = array(
    'shop'                      => 'advertiser_name',   //  Shop name from network
    'target'                    => '0',                 //  TBD - currently configured acording to cj network
    'transaction_status'        => 'status_id',         //  strackr status id. Allowed values: pending, declined, confirmed
    'value'                     => 'price',             //  Amount of the transaction.
    'commission_gross'          => 'revenue',           //  Revenue from the transaction.
    'commission_reference'      => '',                  //  Not in use
    'transaction_id'            => 'id',                //  strackr transactionId. Use source_id for Transaction ID from the network.
    'product_id'                => '',                  //  TBD
    'click_date'                => 'clicked',           //  Click user datetime (ISO 8601).
    'transaction_date'          => 'sold',              //  Transaction datetime (ISO 8601).
    'description'               => 'name',              //  TBD Aggregate all bascket items' name (which is Name of the product.)?
    'currency'                  => 'currency',          //  Currency ISO 4217 code.
    'ip'                        => '',                  //  N/A
    'paid'                      => 'paid',              //  Is the transaction paid out? (boolean)
    'paid_on'                   => 'paid_updated',      //  Date of payment (ISO 8601)
    'network_id'                => 'network_id',        //  Id of the Strackr network
    'order_id'                  => 'order_id',          //  Id of the Strackr order
);

$mapping['strackr-tradetracker'] = array(
    'shop'                      => 'advertiser_name',   //  Shop name from network
    'target'                    => '0',                 //  TBD - currently configured acording to cj network
    'transaction_status'        => 'status_id',         //  strackr status id. Allowed values: pending, declined, confirmed
    'value'                     => 'price',             //  Amount of the transaction.
    'commission_gross'          => 'revenue',           //  Revenue from the transaction.
    'commission_reference'      => '',                  //  Not in use
    'transaction_id'            => 'id',                //  strackr transactionId. Use source_id for Transaction ID from the network.
    'product_id'                => '',                  //  TBD
    'click_date'                => 'clicked',           //  Click user datetime (ISO 8601).
    'transaction_date'          => 'sold',              //  Transaction datetime (ISO 8601).
    'description'               => 'name',              //  TBD Aggregate all bascket items' name (which is Name of the product.)?
    'currency'                  => 'currency',          //  Currency ISO 4217 code.
    'ip'                        => '',                  //  N/A
    'paid'                      => 'paid',              //  Is the transaction paid out? (boolean)
    'paid_on'                   => 'paid_updated',      //  Date of payment (ISO 8601)
    'network_id'                => 'network_id',        //  Id of the Strackr network
    'order_id'                  => 'order_id',          //  Id of the Strackr order
);

$mapping['strackr-awin'] = array(
    'shop'                      => 'advertiser_name',   //  Shop name from network
    'target'                    => '0',                 //  TBD - currently configured acording to cj network
    'transaction_status'        => 'status_id',         //  strackr status id. Allowed values: pending, declined, confirmed
    'value'                     => 'price',             //  Amount of the transaction.
    'commission_gross'          => 'revenue',           //  Revenue from the transaction.
    'commission_reference'      => '',                  //  Not in use
    'transaction_id'            => 'id',                //  strackr transactionId. Use source_id for Transaction ID from the network.
    'product_id'                => '',                  //  TBD
    'click_date'                => 'clicked',           //  Click user datetime (ISO 8601).
    'transaction_date'          => 'sold',              //  Transaction datetime (ISO 8601).
    'description'               => 'name',              //  TBD Aggregate all bascket items' name (which is Name of the product.)?
    'currency'                  => 'currency',          //  Currency ISO 4217 code.
    'ip'                        => '',                  //  N/A
    'paid'                      => 'paid',              //  Is the transaction paid out? (boolean)
    'paid_on'                   => 'paid_updated',      //  Date of payment (ISO 8601)
    'network_id'                => 'network_id',        //  Id of the Strackr network
    'order_id'                  => 'order_id',          //  Id of the Strackr order
);

$mapping['strackr-partnerize'] = array(
    'shop'                      => 'advertiser_name',   //  Shop name from network
    'target'                    => '0',                 //  TBD - currently configured acording to cj network
    'transaction_status'        => 'status_id',         //  strackr status id. Allowed values: pending, declined, confirmed
    'value'                     => 'price',             //  Amount of the transaction.
    'commission_gross'          => 'revenue',           //  Revenue from the transaction.
    'commission_reference'      => '',                  //  Not in use
    'transaction_id'            => 'id',                //  strackr transactionId. Use source_id for Transaction ID from the network.
    'product_id'                => '',                  //  TBD
    'click_date'                => 'clicked',           //  Click user datetime (ISO 8601).
    'transaction_date'          => 'sold',              //  Transaction datetime (ISO 8601).
    'description'               => 'name',              //  TBD Aggregate all bascket items' name (which is Name of the product.)?
    'currency'                  => 'currency',          //  Currency ISO 4217 code.
    'ip'                        => '',                  //  N/A
    'paid'                      => 'paid',              //  Is the transaction paid out? (boolean)
    'paid_on'                   => 'paid_updated',      //  Date of payment (ISO 8601)
    'network_id'                => 'network_id',        //  Id of the Strackr network
    'order_id'                  => 'order_id',          //  Id of the Strackr order
);

//  klm
$mapping['klm'] = array(
    'shop'                      => 'campaign_title',
    'target'                    => 'publisher_reference',
    'transaction_status'        => 'item_status',
    'value'                     => 'value',
    'commission_gross'          => 'publisher_commission',
    'commission_reference'      => 'conversion_reference',
    'transaction_id'            => 'conversion_id',
    'click_date'                => 'set_time',
    'transaction_date'          => 'conversion_time',
    'description'               => 'category', //   passenger category departure_date origin return_date dest
    'currency'                  => 'currency',
    'ip'                        => 'referer_ip',
);

//  performancehorizon
$mapping['performancehorizon'] = array(
    'shop'                      => 'campaign_title',
    'target'                    => 'publisher_reference',
    'transaction_status'        => 'item_status',
    'value'                     => 'value',
    'commission_gross'          => 'publisher_commission',
    'commission_reference'      => 'conversion_reference',
    'transaction_id'            => 'conversion_id',
    'click_date'                => 'set_time',
    'transaction_date'          => 'conversion_time',
    'description'               => 'category', //   passenger category departure_date origin return_date dest
    'currency'                  => 'currency',
    'ip'                        => 'referer_ip',
);

//  tradetracker
$mapping['tradetracker'] = array(
    'shop'                      => 'name',
    'target'                    => 'reference',
    'transaction_status'        => 'transactionStatus',
    'transaction_paidout'       => 'paidOut',
    'value'                     => 'orderAmount',
    'commission_gross'          => 'commission',
    'commission_reference'      => 'transactionType',
    'transaction_id'            => 'ID',
    'click_date'                => 'originatingClickDate',
    'transaction_date'          => 'registrationDate',
    'description'               => 'URL',
    'currency'                  => 'currency',
    'ip'                        => 'IP',
);

//  tradedoubler
$mapping['tradedoubler'] = array(
    'shop'                      => 'Program',
    'target'                    => 'EPI',
    'transaction_status'        => 'Event2',
    'transaction_paidout'       => 'Event2',
    'value'                     => 'empty4',
    'commission_gross'          => 'Commissie',
    'commission_reference'      => 'Event',
    'transaction_id'            => 'empty2',
    'product_id'                => 'Product info',
    'click_date'                => 'Tijd',
    'transaction_date'          => 'Tijd2',
    'description'               => 'Event',
    'currency'                  => 'EUR',
    'ip'                        => '',
);

//  wehkamp
/*
$mapping['wehkamp'] = array(
    'shop'                      => 'Program',
    'target'                    => 'EPI',
    'transaction_status'        => 'Event2',
    'transaction_paidout'       => 'Event2',
    'value'                     => 'empty4',
    'commission_gross'          => 'Commissie',
    'commission_reference'      => 'Event',
    'transaction_id'            => 'empty2',
    'product_id'                => 'Product info',
    'click_date'                => 'Tijd',
    'transaction_date'          => 'Tijd2',
    'description'               => 'Event',
    'currency'                  => 'EUR',
    'ip'                        => '',
);
*/

//  zanox
$mapping['zanox'] = array(
    'shop'                      => '$3',
    'target'                    => '$4',
    'transaction_status'        => 'reviewState',
    'value'                     => 'amount',
    'commission_gross'          => 'commission',
    'commission_reference'      => 'clickId',
    'transaction_id'            => 'id',
    'click_date'                => 'clickDate',
    'transaction_date'          => 'trackingDate',
    'description'               => '$3',
    'currency'                  => 'currency',
    'ip'                        => '',
);

//  awin
$mapping['awin'] = array(
    'shop'                      => 'advertiserId',
    'target'                    => 'clickRef',
    'transaction_status'        => 'commissionStatus',
    'transaction_paidout'       => 'paidToPublisher',
    'value'                     => 'amount2',
    'commission_gross'          => 'commissionAmount',
    'commission_reference'      => '',
    'transaction_id'            => 'id',
    'click_date'                => 'clickDate',
    'transaction_date'          => 'transactionDate',
    'description'               => '',
    'currency'                  => 'currency',
    'ip'                        => '',
);

//  daisycon
$mapping['daisycon'] = array(
    'shop'                      => 'program_name',
    'target'                    => 'subid',
    'transaction_status'        => 'status',
    'value'                     => 'revenue',
    'commission_gross'          => 'commission',
    'commission_reference'      => 'fourhash',
    'transaction_id'            => 'affiliatemarketing_id',
    'click_date'                => 'date_click',
    'transaction_date'          => 'date',
    'description'               => 'publisher_description',
    'currency'                  => 'EUR',
    'ip'                        => 'anonymous_ip',
);

//  bol
//   $mapping['bol'] = array(
//       'shop'                      => 'Bol.com',
//       'target'                    => 'subId',
//       'transaction_status'        => 'orderItemState',
//       'value'                     => 'turnover',
//       'commission_gross'          => 'commission',
//       'commission_reference'      => '',
//       'transaction_id'            => 'orderNr',
//       'product_id'		=> 'productId',
//       'click_date'                => 'clickDate',
//       'transaction_date'          => 'orderDate',
//       'description'               => 'productName',
//       'currency'                  => 'EUR',
//       'ip'                        => '',
//   );

//  booking
//   added TTV as value (Kees 30/07/2018)
$mapping['booking'] = array(
    'shop'                      => 'Booking.com',
    'target'                    => 'Label',
    'transaction_status'        => 'Status',
    'value'                     => 'TTV', #was 495.00 EUR. Now 495
    'commission_gross'          => 'Your commission', #'Fee ( EUR )'
    'commission_reference'      => 'UFI', #'Hotel ufi'
    'transaction_id'            => 'Booking number', #'Book Nr.',
    'click_date'                => 'Booking date', #'Booked'
    'transaction_date'          => 'Booking date', #'Booked'
    'description'               => 'Property name', #'Hotel name'
    'currency'                  => 'EUR',
    'ip'                        => '',
);

//  getyourguide
$mapping['getyourguide'] = array(
    'shop'                      => 'GetYourGuide.com',
    'target'                    => 'Campaign',
    'transaction_status'        => 'Status',
    'value'                     => 'Total',
    'commission_gross'          => 'Commission',
    'commission_reference'      => 'Shopping cart',
    'transaction_id'            => 'ID',
    'click_date'                => 'Booking date',
    'transaction_date'          => 'Activity date',
    'description'               => 'Activity name',
    'currency'                  => 'EUR',
    'ip'                        => '',
);

//  komparu
$mapping['komparu'] = array(
    'shop'                      => 'Komparu',
    'target'                    => 'reference',
    'transaction_status'        => 'status',
    'value'                     => '',
    'commission_gross'          => 'revenue',
    'commission_reference'      => 'link',
    'transaction_id'            => '',
    'click_date'                => 'click_isotime',
    'transaction_date'          => 'lead_isotime',
    'description'               => 'click_description',
    'currency'                  => 'EUR',
    'ip'                        => 'ip',
);

//  familyblend (affiliate4you)
$mapping['familyblend'] = array(
    'shop'                      => 'name2',
    'target'                    => 'linkinfo',
    'transaction_status'        => 'status',
    'value'                     => 'orderamount',
    'commission_gross'          => 'commission',
    'commission_reference'      => 'ordernumber',
    'transaction_id'            => 'a4yorderid',
    'click_date'                => 'orderdate',
    'transaction_date'          => 'orderdate',
    'description'               => '',
    'currency'                  => 'EUR',
    'ip'                        => '',
);

//  affilinet
$mapping['affilinet'] = array(
    'shop'                      => 'ProgramTitle',
    'target'                    => 'SubId',
    'transaction_status'        => 'TransactionStatus',
    'value'                     => 'NetPrice',
    'commission_gross'          => 'PublisherCommission',
    'commission_reference'      => '',
    'transaction_id'            => 'TransactionId',
    'click_date'                => 'ClickDate',
    'transaction_date'          => 'RegistrationDate',
    'description'               => 'RateDescription',
    'currency'                  => 'EUR',
    'ip'                        => '',
);

//  affiliate4you
$mapping['affiliate4you'] = array(
    'shop'                      => 'name2',
    'target'                    => 'linkinfo',
    'transaction_status'        => 'status',
    'value'                     => 'orderamount',
    'commission_gross'          => 'commission',
    'commission_reference'      => 'ordernumber',
    'transaction_id'            => 'a4yorderid',
    'click_date'                => 'orderdate',
    'transaction_date'          => 'orderdate',
    'description'               => '',
    'currency'                  => 'EUR',
    'ip'                        => '',
);

//  empty
$mapping['empty'] = array(
    'shop'                      => '',
    'target'                    => '',
    'transaction_status'        => '',
    'value'                     => '',
    'commission_gross'          => '',
    'commission_reference'      => '',
    'transaction_id'            => '',
    'click_date'                => '',
    'transaction_date'          => '',
    'description'               => '',
    'currency'                  => '',
    'ip'                        => '',
);

//  statuses
$status = array(
    "A",
    "accepted",
    "AFGEKEURD",
    "approved",
    "Cancelled",
    "cancelled",
    "Confirmed",
    "confirmed",
    "D",
    "declined",
    "disapproved",
    "final",
    "GOEDGEKEURD",
    "INBEHANDELING",
    "new",
    "ok",
    "open",
    "P",
    "pending",
    "rejected",
    "Yes",
    "Not Finalized",
    "Finalized",
    "Canceled",
    "Finalised",
    "true",
    "Booked",
    "Stayed",
    "Cancelled by guest",
    "No-show",
    "Cancelled by property",
);

$status['accepted'] = array(
    "A",
    "accepted",
    "approved",
    "final",
    "GOEDGEKEURD",
    "ok",
    "Yes",
    "Confirmed",
    "confirmed",
    "Finalized",
    "Finalised",
    "true",
    "Stayed"
);

$status['declined'] = array(
    "AFGEKEURD",
    "Cancelled",
    "cancelled",
    "D",
    "declined",
    "disapproved",
    "rejected",
    "Canceled",
    "Cancelled by guest",
    "No-show",
    "Cancelled by property"
);

$status['paidout'] = array(
    "P",
    "confirmed",
    true
);

//  SPKWWWCOM-286 START
$booking_exclude_from = date_create("2020-01-01");
$booking_exclude_to = date_create("2020-02-29 23:59:59");
$booking_strackr_to_combine_ids =  date_create("2022-12-01");
$booking_strackr_to_combine_with_source_id =  date_create("2023-12-19");
$booking_exclude_exception_clubs = [9847, 5585]; # SPKWWWCOM-294 Apply Booking transactions fix (Pending to Accepted) on clubs 9847 and 5585 in the exclusion timeframe
//  SPKWWWCOM-286 END

foreach ($results as $result) {

    $values = [];

    $str = utf8_encode($result['transaction_json']);
    $json = json_decode($str);

    $transaction_source = $result['transaction_source'];

    if (strpos($transaction_source, '_requests_') !== false) {
        $affiliate = substr($transaction_source, strpos($transaction_source, '_requests_') + 10);
    } else {
        $affiliate = preg_replace('/.*_/', '', $transaction_source);
    }

    // New Strackr v4 files may include a time_type suffix: __tt__{time_type}
    $affiliate = preg_replace('/__tt__.*/', '', $affiliate);

    if ($json === NULL) {
        continue;
    }

    if (!array_key_exists($affiliate, $mapping)) {
        continue;
    }

    // echo "# " . $str . "\r\n";

    //  prework and formatting
    foreach ($mapping[$affiliate] as $key => $map) {
        if (isset($json->{$map})) {

            //  booking.com strip EUR from value
            $json->{$map} = str_replace(' EUR', '', $json->{$map});

            //  format time
            if (stripos($key, 'date') !== false) {
                $json->{$map} = formatDateTime('', $json->{$map});
            }

            //   We needed to do something with the Strackr Id's as they are case sensitive and in our system it is not.
            //   Therefor we combine the id + id2 of the request, to make an new id, but only for the transactions
            //   larger the the 1st of december 2022
            //   On 19-12-2023wWe had an issue with Strackr in combination with Partnerize, where we see that the sequence 
            //   of the basket changes and therefor creates a new transaction 
            if (str_contains($affiliate, 'strackr') && $key == "transaction_id") {
                // Check if this is v4 API (has sold_at) or v3 API (has sold)
                $sold_field = isset($json->{"sold_at"}) ? "sold_at" : "sold";
                $sold = new DateTime($json->{$sold_field});

                if ($sold >= $booking_strackr_to_combine_with_source_id) {
                    //   We create an exception for Bol.com, as they changed the id's with the new API and therefor created duplicates
                    if ($json->{"network_id"} == "X1Kq") {
                        // v4 API may not have source_id2, use source_id if not available
                        $source_id_field = isset($json->{"source_id2"}) ? $json->{"source_id2"} : $json->{"source_id"};
                        $values[$key] = $json->{"id"} . "_" . $source_id_field;
                    } else {
                        $values[$key] = $json->{"id"} . "_" . $json->{"source_id"};
                    }
                } elseif ($sold >= $booking_strackr_to_combine_ids) {
                    // v4 API may not have id2, fallback to just id
                    $id2_field = isset($json->{"id2"}) ? $json->{"id2"} : "";
                    if ($id2_field) {
                        $values[$key] = $json->{"id"} . "_" . $id2_field;
                    } else {
                        $values[$key] = $json->{"id"};
                    }
                } else {
                    $values[$key] = $json->{"id"};
                }
            } else {
                $values[$key] = $json->{$map};
            }
        } else {

            $values[$key] = $map;
        }
    }

    //  if there isn't a transaction id, create our own
    if (strlen($values['transaction_id']) == 0) {
        $values['transaction_id'] = md5($values['click_date'] . $values['transaction_date'] . $values['shop']);
    }

    //  update values by creating an sql string
    foreach ($values as $key => $value) {

        if (strlen($value) > 0) {

            // echo "# key: " . $key . ", value: " . $value . "\r\n";
            // echo "value: " . $value . "<br>";

            $sql = 'UPDATE transactions SET ';

            //  echo "transaction_source: " . $result['transaction_source'] . "<br>";
            //  echo "transaction_date: " . $result['transaction_date'] . "<br>";
            //  echo "transaction_id: " . $result['transaction_id'] . "<br>";
            //  echo "id: " . $result['id'] . "<br>";
            //  echo "id2: " . $values['id2'] . "<br>";
            //  echo "id: " . $values['id'] . "<br>";

            if ($key == 'value' or $key == 'commission_gross') {

                //  TODO: fix tradedoubler notation
                $value = (float) $value;
                $sql .= '`' . $key . '` = ' . number_format($value, 4, ".", "");
            } elseif ($key == 'transaction_status') {

                if (in_array($value, $status['accepted'])) {
                    //  SPKWWWCOM-286 START
                    if ($affiliate == 'booking') {
                        $transaction_date = $result['transaction_date'];

                        if (!$transaction_date instanceof DateTime) {
                            try {
                                $transaction_date = new DateTime($transaction_date);
                            } catch (exception $e) {
                                $transaction_date = false;
                            }
                        }

                        if ($transaction_date instanceof DateTime && ($transaction_date < $booking_exclude_from || $transaction_date > $booking_exclude_to || in_array($result['id'], $booking_exclude_exception_clubs))) {
                            $transaction_status = 'accepted';
                        } else {
                            continue;
                        }
                    } else {
                        $transaction_status = 'accepted';
                    }
                    //  SPKWWWCOM-286 END
                } elseif (in_array($value, $status['declined'])) {
                    $transaction_status = 'declined';
                } else {
                    continue;
                }

                $sql .= '`transaction_status` = "' . $transaction_status . '"';
            } elseif ($key == 'target') {

                $value = str_replace('%5B', '', $value);
                $value = str_replace('%5D', '', $value);
                $sql .= '`' . $key . '` = "' . addslashes(substr($value, 0, 64)) . '"';
            } elseif ($key == 'description') {

                $value = str_replace('%5B', '', $value);
                $value = str_replace('%5D', '', $value);
                $sql .= '`' . $key . '` = "' . addslashes(substr($value, 0, 255)) . '"';
            } elseif ($key == 'transaction_paidout') {

                if (in_array($value, $status['paidout'])) {
                    $transaction_paidout = true;
                } else {
                    continue;
                }

                $sql .= '`transaction_paidout` = "' . $transaction_paidout . '"';
            } elseif ($key == 'click_date' or $key == 'transaction_date' or $key == 'paid_on' ) {

                $value = formatDateTime('', $value);

                if ($value === null || trim($value) === '') {
                    $sql .= '`' . $key . '` = NULL';
                } else {
                    $sql .= '`' . $key . '` = "' . $value . '"';
                }
            } elseif ($key == 'paid') {

                echo "# paid: " . $value . "\r\n";

                if ($value === true || $value === 1 || trim($value) === "1" || trim($value) === 'true') {
                    $sql .= '`' . $key . '` = 1';
                } else {
                    $sql .= '`' . $key . '` = 0';
                }
            } else {

                $value = addslashes($value);
                $sql .= '`' . $key . '` = "' . $value . '"';
            }

            $sql .= ' WHERE 1 AND id = ' . $result['id'] . ';';
            echo $sql . "\r\n";
            //   echo "<br>";
        }
    }
}

echo "UPDATE `transactions` SET `order_id` = NULL WHERE `order_id` = 'order_id' OR `order_id` = 'null';\r\n";
echo "UPDATE `transactions` SET `order_id` = MD5(CONCAT(`shop`,`target`,`transaction_date`,`description`)) WHERE `shop` LIKE 'Coolblue%' and `order_id` IS NULL;\r\n";
echo "UPDATE `transactions` SET `order_id` = MD5(CONCAT(`shop`,`target`,`transaction_date`,`click_date`)) WHERE `shop` LIKE 'AliExpress%' and `order_id` IS NULL;\r\n";