Morozov&Pimnev blog

$oShop = Core_Entity::factory('Shop', 1);

$current_date = date('Y-m-d H:i:s');
$query_currency_switch = 'price';

// Получаем список валют
$aShop_Currencies = Core_Entity::factory('Shop_Currency')->findAll();

foreach ($aShop_Currencies as $oShop_Currency)
{
	// Получаем коэффициент пересчета для каждой валюты для магазина
	$currency_coefficient = Shop_Controller::instance()->getCurrencyCoefficientInShopCurrency(
		$oShop_Currency, $oShop->Shop_Currency
	);

	$query_currency_switch = "IF (shop_items.shop_currency_id = {$oShop_Currency->id}, 
		IF (shop_discounts.value, 
			IF(shop_discounts.type, 
				IF(shop_tax_id AND shop_taxes.tax_is_included = 0, price + (shop_taxes.rate / 100 * price), price) * {$currency_coefficient} - shop_discounts.value, 
				IF(shop_tax_id AND shop_taxes.tax_is_included = 0, price + (shop_taxes.rate / 100 * price), price) * (100 - shop_discounts.value) * {$currency_coefficient} / 100
			), 
			IF(shop_tax_id AND shop_taxes.tax_is_included = 0, price + (shop_taxes.rate / 100 * price), price) * {$currency_coefficient}
		),
		{$query_currency_switch}
	)";
}

$oShop_Items = $oShop->Shop_Items;

$oShop_Items->queryBuilder()
	->select(array(Core_QueryBuilder::expression($query_currency_switch), 'absolute_price'))
	->leftJoin('shop_taxes', 'shop_items.shop_tax_id', '=', 'shop_taxes.id')
	->leftJoin('shop_item_discounts', 'shop_items.id', '=', 'shop_item_discounts.shop_item_id')
	->leftJoin('shop_discounts', 'shop_item_discounts.shop_discount_id', '=', 'shop_discounts.id', array(
		array('AND (' => array('shop_discounts.end_datetime', '>=', $current_date)),
		array('OR' => array('shop_discounts.end_datetime', '=', '0000-00-00 00:00:00')),
		array('AND' => array('shop_discounts.start_datetime', '<=', $current_date)),
		array(')' => NULL)
	))
	->groupBy('shop_items.id')
	->clearOrderBy()
	->orderBy('absolute_price', 'ASC'); // сортировка по абсолютной цене по возрастанию

$oShop_Items = $oShop_Items->findAll();