/**
* @param OnlineShop_Framework_Pricing_IRule $rule
* @param string $field
*
* @return mixed
*/
private function getData(OnlineShop_Framework_Pricing_IRule $rule, $field)
{
if (!array_key_exists($rule->getId(), self::$cache)) {
$query = <<<'SQL'
SELECT 1
, priceRule.ruleId
, count(priceRule.o_id) as "soldCount"
, sum(orderItem.totalPrice) as "salesAmount"
-- DEBUG INFOS
, orderItem.oo_id as "orderItem"
, `order`.orderdate
FROM object_query_%2$d as `order`
-- ordered products
JOIN object_relations_%2$d as orderItems
ON( 1
AND orderItems.fieldname = "items"
AND orderItems.src_id = `order`.oo_id
)
-- order item
JOIN object_%1$d as orderItem
ON ( 1
AND orderItem.o_id = orderItems.dest_id
)
-- add active price rules
JOIN object_collection_PricingRule_%1$d as priceRule
ON( 1
AND priceRule.o_id = orderItem.oo_id
AND priceRule.fieldname = "PricingRules"
AND priceRule.ruleId = %3$d
)
WHERE 1
AND `order`.orderState = "committed"
LIMIT 1
SQL;
try {
$query = sprintf($query, \Pimcore\Model\Object\OnlineShopOrderItem::classId(), \Pimcore\Model\Object\OnlineShopOrder::classId(), $rule->getId());
$conn = \Pimcore\Resource::getConnection();
self::$cache[$rule->getId()] = $conn->fetchRow($query);
} catch (Exception $e) {
Logger::error($e);
}
}
return self::$cache[$rule->getId()][$field];
}
/**
* @return $this
*/
public function joinPaymentInfo()
{
// create sub select
$paymentQuery = Resource::getConnection()->select();
$paymentQuery->from(['_paymentInfo' => 'object_collection_PaymentInfo_' . OnlineShopOrder::classId()], ['paymentReference' => 'GROUP_CONCAT(",", _paymentInfo.paymentReference, "," SEPARATOR ",")', 'o_id' => '_order.o_id'])->join(['_order' => 'object_' . OnlineShopOrder::classId()], '_order.oo_id = _paymentInfo.o_id', '');
// join
$this->getQuery()->joinLeft(['paymentInfo' => new Zend_Db_Expr('(' . $paymentQuery . ')')], 'paymentInfo.o_id = `order`.oo_id', '');
return $this;
}
protected function getCurrentAmount(OnlineShop_Framework_Pricing_IRule $rule)
{
if (!array_key_exists($rule->getId(), $this->currentSalesAmount)) {
$query = <<<'SQL'
SELECT 1
, count(priceRule.o_id) as "count"
, sum(orderItem.totalPrice) as "amount"
-- DEBUG INFOS
, orderItem.oo_id as "orderItem"
, `order`.orderdate
FROM object_query_%2$d as `order`
-- ordered products
JOIN object_relations_%2$d as orderItems
ON( 1
AND orderItems.fieldname = "items"
AND orderItems.src_id = `order`.oo_id
)
-- order item
JOIN object_%1$d as orderItem
ON ( 1
AND orderItem.origin__id is null
AND orderItem.o_id = orderItems.dest_id
)
-- add active price rules
JOIN object_collection_PriceRule_%1$d as priceRule
ON( 1
AND priceRule.o_id = orderItem.oo_id
AND priceRule.fieldname = "priceRules"
AND priceRule.ruleId = %3$d
)
WHERE 1
AND `order`.orderState = "committed"
AND `order`.origin__id is null
LIMIT 1
SQL;
$query = sprintf($query, \Pimcore\Model\Object\OnlineShopOrderItem::classId(), \Pimcore\Model\Object\OnlineShopOrder::classId(), $rule->getId());
$conn = \Pimcore\Resource::getConnection();
$this->currentSalesAmount[$rule->getId()] = (int) $conn->fetchRow($query)['amount'];
}
return $this->currentSalesAmount[$rule->getId()];
}
