apply_modifiers.php

<?php
use \Tygh\Registry;

define('AREA', 'A');
define('ACCOUNT_TYPE', 'admin');

require(dirname(__FILE__) . '/init.php');

Registry::set('runtime.company_id', 0);

$from_db_name = Registry::get('config.db_name') . '_backup';
$from_db_host = Registry::get('config.db_host');
$from_db_password = Registry::get('config.db_password');
$from_db_user = Registry::get('config.db_user');
$from_db_params = [
    'table_prefix' => Registry::get('config.table_prefix'),
    'dbc_name' => $from_db_name
];

$to_db_name = Registry::get('config.db_name');
$to_db_host = Registry::get('config.db_host');
$to_db_password = Registry::get('config.db_password');
$to_db_user = Registry::get('config.db_user');
$to_db_params = [
    'table_prefix' => Registry::get('config.table_prefix'),
    'dbc_name' => $from_db_name
];

$dry_run = true;


$from_db_connection = new \Tygh\Database\Connection();
$from_db_connection->connect(
    $from_db_user,
    $from_db_password,
    $from_db_host,
    $from_db_name,
    $from_db_params
) or die('Can not connect to from db');

$to_db_connection = new \Tygh\Database\Connection();
$to_db_connection->connect(
    $to_db_user,
    $to_db_password,
    $to_db_host,
    $to_db_name,
    $to_db_params
) or die('Can not connect to destination db');

$product_ids = $to_db_connection->getColumn('SELECT product_id FROM ?:product_variation_group_products');

foreach ($product_ids as $product_id) {
    $from_product_data = $from_db_connection->getRow('SELECT variation_options FROM ?:products WHERE product_id = ?i AND parent_product_id != 0', $product_id);
    $variation_options = [];

    if (empty($from_product_data['variation_options'])) {
        $from_product_data = $from_db_connection->getRow('SELECT variation_options FROM ?:products WHERE parent_product_id = ?i AND is_default_variation = ?s', $product_id, 'Y');
    }

    if (!empty($from_product_data['variation_options'])) {
        $variation_options = array_filter(json_decode($from_product_data['variation_options'], true));
    }

    if (empty($variation_options)) {
        fn_echo("Product #{$product_id}: Variation options not found. Skipped.<br>");
        continue;
    }

    $product_data = $to_db_connection->getRow('SELECT weight FROM ?:products WHERE product_id = ?i', $product_id);
    $prices = $to_db_connection->getArray('SELECT price, usergroup_id, lower_limit, percentage_discount FROM ?:product_prices WHERE product_id = ?i', $product_id);

    $ult_prices = [];

    if (fn_allowed_for('ULTIMATE')) {
        $ult_prices = $to_db_connection->getArray('SELECT price, usergroup_id, lower_limit, percentage_discount, company_id FROM ?:ult_product_prices WHERE product_id = ?i', $product_id);
    }

    $variants = $from_db_connection->getArray('SELECT modifier, modifier_type, weight_modifier, weight_modifier_type, variant_id FROM ?:product_option_variants WHERE variant_id IN (?n)', $variation_options);

    if (fn_allowed_for('ULTIMATE')) {
        $ult_variants = $from_db_connection->getMultiHash('SELECT modifier, modifier_type, company_id, variant_id FROM ?:ult_product_option_variants WHERE variant_id IN (?n)', ['company_id', 'variant_id'], $variation_options);
    }

    foreach ($prices as $item) {
        $price = $item['price'];

        foreach ($variants as $variant) {
            if ($variant['modifier_type'] === 'A') { // Absolute
                $price += floatval($variant['modifier']);
            } else { // Percentage
                $price += floatval($variant['modifier']) * $item['price'] / 100;
            }
        }

        if ($price != $item['price']) {
            fn_echo("Product #{$product_id}: Modifiers applying: Old price: {$item['price']}, New price: {$price}.<br>");

            !$dry_run && $to_db_connection->query('UPDATE ?:product_prices SET price = ?d WHERE product_id = ?i AND usergroup_id = ?i AND lower_limit = ?i',
                $price, $product_id, $item['usergroup_id'], $item['lower_limit']
            );
        }
    }

    foreach ($ult_prices as $item) {
        $price = $item['price'];

        foreach ($variants as $variant) {
            if (isset($ult_variants[$item['company_id']][$variant['variant_id']])) {
                $variant = $ult_variants[$item['company_id']][$variant['variant_id']];
            }

            if ($variant['modifier_type'] === 'A') { // Absolute
                $price += floatval($variant['modifier']);
            } else { // Percentage
                $price += floatval($variant['modifier']) * $item['price'] / 100;
            }
        }

        if ($price != $item['price']) {
            fn_echo("Product #{$product_id}: Modifiers applying: Old price: {$item['price']}, New price: {$price}.<br>");

            !$dry_run && $to_db_connection->query('UPDATE ?:ult_product_prices SET price = ?d WHERE product_id = ?i AND usergroup_id = ?i AND lower_limit = ?i AND company_id = ?i',
                $price, $product_id, $item['usergroup_id'], $item['lower_limit'], $item['company_id']
            );
        }
    }

    $weight = $product_data['weight'];

    foreach ($variants as $variant) {
        if ($variant['weight_modifier_type'] === 'A') { // Absolute
            $price += floatval($variant['weight_modifier']);
        } else { // Percentage
            $price += floatval($variant['weight_modifier']) * $item['price'] / 100;
        }
    }

    if ($weight != $product_data['weight']) {
        fn_echo("Product #{$product_id}: Modifiers applying: Old weight: {$product_data['weight']}, New weight: {$weight}.<br>");

        !$dry_run && $to_db_connection->query('UPDATE ?:products SET weight = ?d WHERE product_id = ?i', $weight, $product_id);
    }
}

fn_echo("Done");