Shopware 6: Showing gross prices, net price, taxes and calculations – part 2 (the solution)
Fabian Blechschmidt
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:
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) 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
PHPStorm/IDEA, composer updates and 100% CPU
In one of our projects PHPStorm ran a while on 100% CPU, an investigation showed, that the process „checking for available composer updates“ ran and used all the resources. Most likely it got stuck somewhere. To get rid of the problem, one can turn off this feature. In IDEA Ultimate here: [Settings] -> Languages & […]
Custom Fields, dots and MySQL’s JSON_EXTRACT
We all know custom fields – hopefully – at least this is not about the basics. You can add custom fields to nearly all entites: orders, products, categories, … You can name them whatever you like, e.g. pluginname.property – but using dots . in your custom field name is a bad idea, because then you […]
gpg failed to sign the data
I was doing some work, when suddenly my git failed to commit changed. I dig a little around, asked StackOverflow but it didn’t help. Finally when running the commit manually and not through PHPStorm I got a proper error: Ah! The key is expired! Got it. Did you know you can extend a GPG key? […]