Convert all product prices current excluding tax to including tax

Question

I want to convert all prices of my products from excluding tax to including tax. I have a german Magento 2.1.8 shop, so I would need to multiply by/include 19 % tax: net price * 1,19 percent german tax = price including tax

Doing this manually is not possible (I currently have 13000 products created). Which tables in the database do I need to modify? I have only configurable and simple products.

Answer 1

to modify a Magento attribute on a store basis, you may use the function updateAttributes. Please see below a snippet that may show you how it could work:

<?php
      $storeId = 'germanStoreId';

      $collection = Mage::getModel('catalog/product')->getCollection()
      $collection->addMinimalPrice()
        ->addFinalPrice()
        ->addTaxPercents()
        ->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
        ->addUrlRewrite();

      foreach ($collection as $product) {

        Mage::getSingleton('catalog/product_action')->updateAttributes(
            array($productId),
            array('price' => $product->getPrice()*1.9),
            $storeId
        );

     }

Answer 2

Make a backup first! And test first in develop environment.

To convert all prices from net (german: netto) to gross (german: brutto) ones, you need to issue the following SQL statement.

But at first you need to obtain your attribute_id of the price attribute. To do so, go to backend > stores > attributes > product and click on the price attribute. Obtain the id from the URL (see screenshot below):

In this case it is 67 but yours can be different!

Update catalog_product_entity_decimal set value = ROUND(value*1.19, 2) WHERE attribute_id = #your_attribute_id#

where #your_attribute_id#would be your previously obtained attribute_id of the price attribute (in this case 67).

This query will also round your prices! F.e. if your net price was 19.3193 the result will be 22.99.

If you have special prices on some of your products, you do need to repeat the above steps for the attribute_id corresponding to the attribute special_price.

Do not forget to set your catalog prices to including tax. Afterwards reindex prices via cli php bin/magento indexer:reindex.