OMNIBUS directive in Virtuemart - a simple solution

[hazael]

There has been a lot of discussion here regarding the requirement to display the lowest price within the last 30 days according to the EU directive, but no one has offered a concrete solution.

I have created a very simple script that handles this task perfectly. It would be great if Max — who is not present here at the moment (perhaps currently on the front line, fighting for a free Ukraine 🙂) — could consider implementing this feature natively in VirtueMart once he returns.

In the meantime, here is a simple solution:
In the database (phpMyAdmin) we create a new table

Code Select Expand

CREATE TABLE IF NOT EXISTS `#__virtuemart_product_price_history` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `virtuemart_product_id` INT UNSIGNED NOT NULL,
    `price` DECIMAL(15,5) NOT NULL DEFAULT 0,
    `date` DATE NOT NULL,
    UNIQUE KEY `unique_product_date` (`virtuemart_product_id`, `date`),
    INDEX (`virtuemart_product_id`),
    INDEX (`date`)
);

--------------------------------------------------------

Now we create a php script of any name, which we put in any hidden place in the joomla folder:

Code Select Expand

<?php
define('JPATH_BASE', realpath(__DIR__ . '/..'));
define('_JEXEC', 1);
require_once JPATH_BASE . '/includes/app.php';

use Joomla\CMS\Factory;

$app = Factory::getApplication('site');

if (!class_exists('VmConfig')) {
    require(JPATH_BASE . '/administrator/components/com_virtuemart/helpers/config.php');
}
VmConfig::loadConfig();

if (!class_exists('VmModel')) {
    require(JPATH_BASE . '/administrator/components/com_virtuemart/helpers/vmmodel.php');
}

$db = JFactory::getDbo();
$query = $db->getQuery(true)
    ->select('virtuemart_product_id')
    ->from('#__virtuemart_products');
$db->setQuery($query);
$products = $db->loadColumn();

$productModel = VmModel::getModel('product');
$date = date('Y-m-d');

foreach ($products as $product_id) {
    $product = $productModel->getProduct($product_id, true, false, false, true);
    $prices = $productModel->getPrice($product, [], 1);
    $finalPrice = isset($prices['salesPrice']) ? $prices['salesPrice'] : 0;

    // Pobierz ostatnio zapisaną cenę dla tego produktu
    $query = $db->getQuery(true)
        ->select('price')
        ->from('#__virtuemart_product_price_history')
        ->where('virtuemart_product_id = ' . (int)$product_id)
        ->order('date DESC')
        ->setLimit(1);
    $db->setQuery($query);
    $lastSavedPrice = $db->loadResult();


    if (is_null($lastSavedPrice) || (float)$finalPrice < (float)$lastSavedPrice) {
        $queryInsert = "
            INSERT INTO #__virtuemart_product_price_history (virtuemart_product_id, price, date)
            VALUES (" . (int)$product_id . ", " . (float)$finalPrice . ", " . $db->quote($date) . ")
            ON DUPLICATE KEY UPDATE price = VALUES(price)
        ";

        $db->setQuery($queryInsert);
        $db->execute();
    }
}

$queryDelete = $db->getQuery(true)
    ->delete('#__virtuemart_product_price_history')
    ->where('date < DATE_SUB(CURDATE(), INTERVAL 30 DAY)');

$db->setQuery($queryDelete);
$db->execute();

echo '<script>window.location.href = "/";</script>';
echo '<noscript><meta http-equiv="refresh" content="0; url=/" /></noscript>';
exit;

The above script generates an up-to-date copy of the prices for each product. Prices older than 30 days are automatically removed from the database. The script overwrites or adds a new price entry for a given product only if its current price is lower than any previously recorded price within the last 30 days.

According to the EU Omnibus directive, only the lowest price from the last 30 days is relevant. This approach prevents generating unnecessary data and helps keep the database clean and efficient.

The script executes very fast, even in stores with a large product database.

It is recommended to schedule this PHP file via a CRON job to run once per day.

------------------------------------------

Now all we need to do is add a simple PHP code in our product template in the:
/com_virtuemart/productdetails/default.php

Code Select Expand

<?php
$db = JFactory::getDbo();
$today = date('Y-m-d');

$query = $db->getQuery(true)
    ->select('price, date')
    ->from('#__virtuemart_product_price_history')
    ->where('virtuemart_product_id = ' . (int)$product->virtuemart_product_id)
    ->where('date >= DATE_SUB(' . $db->quote($today) . ', INTERVAL 30 DAY)')
    ->where('date < ' . $db->quote($today))
    ->order('price ASC, date ASC')
    ->setLimit(1);

$db->setQuery($query);
$lowestPriceData = $db->loadAssoc();

if ($product->prices['discountAmount'] != 0 && !empty($lowestPriceData) && $lowestPriceData['price'] > 0):
    $currency = CurrencyDisplay::getInstance();
    $convertedLowestPrice = $currency->priceDisplay($lowestPriceData['price']);
    $lowestPriceDate = date('d.m.Y', strtotime($lowestPriceData['date']));
?>
    <div class="ominibus">
        The lowest price in the last 30 days before the promotion:
        <strong><?php echo $convertedLowestPrice; ?></strong><br>
        The day was in force: <strong><?php echo $lowestPriceDate; ?></strong>
    </div>
<?php endif; ?>
The above script will always display the lowest price within 30 days for the currently displayed product. The price will be automatically converted to the currently displayed currency ( unfortunately with the current exchange rate, but it's better than nothing ;-)).

The historical price will only display if the selected product has a price discount and the lowest historical price is more than 1 day old.

As a bonus - the lowest price will be displayed with the real date - i.e. the day on which the promotional action was in effect. This ensures that these prices will never be considered fictitious.

[hazael]

if you are using the Yootheme template to display a dynamic product page, you can implement this last code as a regular Joomla module.

In the modules folder, create a mod_lowestprice folder. And add 2 files: mod_lowestprice.php and mod_lowestprice.xml


in mod_lowestprice.php:

Code Select Expand

<?php
defined('_JEXEC') or die;
use Joomla\CMS\Language\Text;
use Joomla\CMS\Factory;

if (!class_exists('VmConfig')) {
    require(JPATH_ADMINISTRATOR . '/components/com_virtuemart/helpers/config.php');
}
VmConfig::loadConfig();
if (!class_exists('VmModel')) {
    require(JPATH_ADMINISTRATOR . '/components/com_virtuemart/helpers/vmmodel.php');
}
$productId = Factory::getApplication()->input->getInt('virtuemart_product_id', 0);

if ($productId > 0) {
    $db = Factory::getDbo();
    $timezone = Factory::getConfig()->get('offset');  
    $date = new DateTime('now', new DateTimeZone($timezone));  
    $today = $date->format('Y-m-d');  

    $query = $db->getQuery(true)
        ->select('price, date')
        ->from($db->qn('#__virtuemart_product_price_history'))
        ->where('virtuemart_product_id = ' . (int)$productId)
        ->where('date >= DATE_SUB(' . $db->quote($today) . ', INTERVAL 30 DAY)')
        ->where('date < ' . $db->quote($today)) // tylko ceny sprzed dzisiaj
        ->order('price ASC, date ASC')
        ->setLimit(1);

    $db->setQuery($query);
    $lowestPriceData = $db->loadAssoc();

    $productModel = VmModel::getModel('product');
    $product = $productModel->getProduct($productId, true, false, false, true);
    $prices = $productModel->getPrice($product, [], 1);


    if (!empty($prices['discountAmount']) && $prices['discountAmount'] != 0 && !empty($lowestPriceData) && $lowestPriceData['price'] > 0) {
        $currency = CurrencyDisplay::getInstance();
        $convertedLowestPrice = $currency->priceDisplay($lowestPriceData['price']);
        $lowestPriceDate = date('d.m.Y', strtotime($lowestPriceData['date']));

        echo '<div class="uk-text-small uk-margin-small-top"><span>'. Text::_('LOWEST_PRICE').':</span> <span>' . $convertedLowestPrice . '</span></div>';
        echo '<div class="uk-text-small"><span>'. Text::_('LOWEST_DATE').':</span> <span>' . $lowestPriceDate . '</span></div>';
    }
}
in mod_lowestprice.xml

Code Select Expand

<?xml version="1.0" encoding="utf-8"?>
<extension type="module" version="4.0" client="site" method="upgrade">
    <name>mod_lowestprice</name>
    <author>666</author>
    <version>1.0.0</version>
    <description>Module for displaying the lowest price in the last 30 days on VirtueMart product page</description>
    <files>
        <filename module="mod_lowestprice">mod_lowestprice.php</filename>
    </files>
</extension>
You cannot view this attachment.