Break code to make it work

In part one we explained, what our problem is: We want to show gross and net prices, but recalculate the gross price if the tax rate changes (OSS – one stop shop).

We still are on Shopware 6.3 and therefore our implementation might be a lot more complex, than it would be in Shopware 6.4, but changing the price logic, display and tax rates is hard. So hard, that we decided to ignore discounts, coupon codes, etc. for the moment to make it work. (One might think, that this is a horrible technical decision and I agree, but from a business point of view it is a good one: Break feature we don’t use to get rid of a show stopper is a good decision and for me as a developer it was hard work to understand this.)

27 to rule them all – the better solution

Thanks to Moritz Naczenski we have a better solution. The learning from a nice chat with him is:

If in doubt, use the rule builder.

unnamed Shopware user

So here is the plan:

1. Generate a rule for each country you deliver to (you can think about merging countries with the same tax rate, but if these change, this will break, don’t do it)
2. Create an advanced price with unlinked gross/net price and the correct price for the tax rate

How not to do it manually

Be careful, this code has a few pitfalls, like loading all products (which is ok in our setup, we have only a couple hundred…)

<?php declare(strict_types=1);

namespace Winkelwagen\Migration;

use Doctrine\DBAL\Connection;
use Shopware\Core\Framework\Migration\MigrationStep;
use Shopware\Core\Framework\Uuid\Uuid;

class Migration1634478159CreateOssRules extends MigrationStep
{
    public function getCreationTimestamp(): int
    {
        return 1634478159;
    }

    public function update(Connection $connection): void
    {
        $countries = $connection->fetchAll(
            'SELECT * FROM country WHERE iso3 IN
                (\'AUT\', \'BEL\', \'BGR\', \'HRV\', \'CYP\', \'CZE\', \'DNK\', \'EST\', \'FIN\', \'FRA\',
                 \'GRC\', \'HUN\', \'IRL\', \'ITA\',\'LVA\', \'LTU\', \'LUX\', \'MLT\', \'NLD\', \'POL\',
                 \'PRT\', \'ROU\', \'SVK\', \'SVN\', \'ESP\', \'SWE\');'
        );

        foreach ($countries as $country) {
            $countryId = $country['id'];
            $ruleId = Uuid::randomBytes();
            $connection->insert(
                'rule',
                [
                    "id"           => $ruleId,
                    "name"         => "OSS: Lieferung nach " . $country['iso'],
                    "priority"     => 100,
                    "module_types" => "{\"types\":[\"price\"]}",
                    "created_at"   => date('Y-m-d H:i:s')
                ]
            );
            $condition1 = Uuid::randomBytes();
            $condition2 = Uuid::randomBytes();
            $condition3 = Uuid::randomBytes();
            $connection->insert('rule_condition', [
                    "id"         => $condition1,
                    "type"       => "orContainer",
                    "rule_id"    => $ruleId,
                    "created_at" => date('Y-m-d H:i:s'),
                ]
            );
            $connection->insert('rule_condition', [
                "id"         => $condition2,
                "type"       => "andContainer",
                "rule_id"    => $ruleId,
                "parent_id"  => $condition1,
                "created_at" => date('Y-m-d H:i:s'),
            ]);
            $countryIdhex = Uuid::fromBytesToHex($countryId);
            $connection->insert('rule_condition', [
                "id"         => $condition3,
                "type"       => "customerShippingCountry",
                "rule_id"    => $ruleId,
                "parent_id"  => $condition2,
                "value"      => "{\"operator\":\"=\",\"countryIds\":[\"$countryIdhex\"]}",
                "created_at" => date('Y-m-d H:i:s'),
            ]);
        }
    }

    public function updateDestructive(Connection $connection): void
    {
        // implement update destructive
    }
}

<?php declare(strict_types=1);

namespace Winkelwagen\Migration;

use Doctrine\DBAL\Connection;
use Shopware\Core\Framework\Migration\MigrationStep;
use Shopware\Core\Framework\Uuid\Uuid;

class Migration1634484129CreateOssPrices extends MigrationStep
{
    public function getCreationTimestamp(): int
    {
        return 1634484129;
    }

    public function update(Connection $connection): void
    {
        $rules = $this->loadRules($connection);
        $taxes = $this->loadTaxes($connection);
        $euroId = Uuid::fromBytesToHex($connection->fetchAssoc('SELECT id FROM currency;')['id']);
        $products = $connection->fetchAll('SELECT * FROM product;');
        foreach ($products as $product) {
            foreach ($rules as $rule) {
                if (!isset($taxes[$product['tax_id']][$rule['country_id']], $product['price'])) {
                    continue;
                }
                $tax = $taxes[$product['tax_id']][$rule['country_id']];
                $randomId = Uuid::randomHex();
                $priceId = Uuid::randomBytes();
                $productPrice = current(json_decode($product['price'], true));
                $netPrice = round($productPrice['net'], 2);
                $grossPrice = round($productPrice['net'] * $tax, 2);
                $price = [
                    "id"                 => $priceId,
                    "version_id"         => $product['version_id'],
                    "rule_id"            => $rule['id'],
                    "product_id"         => $product['id'],
                    "product_version_id" => $product['version_id'],
                    "price"              => "{\"$randomId\":{\"currencyId\":\"$euroId\",\"gross\":$grossPrice,\"linked\":false,\"net\":$netPrice,\"listPrice\":null}}",
                    "quantity_start"     => 1,
                    "quantity_end"       => null,
                    "custom_fields"      => null,
                    "created_at"         => date('Y-m-d H:i:s'),
                    "updated_at"         => null
                ];
                $connection->insert('product_price', $price);
            }
        }
    }

    private function loadTaxes(Connection $connection): array
    {
        $taxes = $connection->fetchAll('SELECT tax_id, country_id, tax_rate FROM tax_rule;');
        $return = [];
        foreach ($taxes as $tax) {
            $return[$tax['tax_id']][$tax['country_id']] = (100 + $tax['tax_rate']) / 100;
        }
        return $return;
    }

    private function loadRules(Connection $connection): array
    {
        $rules = $connection->fetchAll(
            'SELECT
                r.id,
                rc.value
            FROM
                rule r
                INNER JOIN rule_condition rc ON r.id = rc.rule_id
            WHERE
                r.name LIKE \'OSS: Lieferung nach%\'
                AND `type` = \'customerShippingCountry\''
        );
        $return = [];
        foreach ($rules as $rule) {
            $return[] = [
                'id'         => $rule['id'],
                'country_id' => Uuid::fromHexToBytes(json_decode($rule['value'], true)['countryIds'][0])
            ];
        }
        return $return;
    }

    public function updateDestructive(Connection $connection): void
    {
        // implement update destructive
    }
}

Other articles from this category