2

When I run the Ordered Products Report in Magento backend, the generated report lists all products ordered and their ordered quantity -- however, they don't seem to be grouped correctly by SKU. I have the same product showing up multiple times. I would like the report to generate correctly and group by SKU.

Here is an example report:

|----------|------------------------------------------|-------------|------------------|
| Interval | Product                                  | SKU         | Ordered Quantity | 
|----------|------------------------------------------|-------------|------------------|
| 8/12/18  | CBD Freeze Pain Relief 3oz Roller 1500mg | TFR-04-1500 | 8                |
|          | CBD Oil Tincture Drops 30mL Berry 300mg  | TIN-BR-0300 | 6                |
|          | CBD Oil Tincture Drops 30mL Berry 750mg  | TIN-BR-0750 | 5                |
|          | CBD Oil Tincture Drops 30mL Berry 300mg  | TIN-BR-0300 | 3                |
|----------|------------------------------------------|-------------|------------------|

Notice that SKU TIN-BR-0300 is listed twice for the same day/interval.

2

We found a solution so if anyone comes across this same issue

the group() query was removed when sku was added to the table array and the SUM() needed to be added to the ordered_qty.qty_ordered

Open File:

vendor/magento/module-reports/Model/ResourceModel/Product/Sold/Collection.php

Go to line 47 and replace

    public function addOrderedQty($from = '', $to = '')
{
    $connection = $this->getConnection();
    $orderTableAliasName = $connection->quoteIdentifier('order');

    $orderJoinCondition = [
        $orderTableAliasName . '.entity_id = order_items.order_id',
        $connection->quoteInto("{$orderTableAliasName}.state <> ?", \Magento\Sales\Model\Order::STATE_CANCELED),
    ];

    if ($from != '' && $to != '') {
        $fieldName = $orderTableAliasName . '.created_at';
        $orderJoinCondition[] = $this->prepareBetweenSql($fieldName, $from, $to);
    }

    $this->getSelect()->reset()->from(
        ['order_items' => $this->getTable('sales_order_item')],
        [
            'ordered_qty' => 'order_items.qty_ordered',
            'order_items_name' => 'order_items.name',
            'order_items_sku' => 'order_items.sku'
        ]
    )->joinInner(
        ['order' => $this->getTable('sales_order')],
        implode(' AND ', $orderJoinCondition),
        []
    )->where(
        'order_items.parent_item_id IS NULL'
    )->having(
        'order_items.qty_ordered > ?',
        0
    );
    return $this;
}

With this

    public function addOrderedQty($from = '', $to = '')
{
    $connection = $this->getConnection();
    $orderTableAliasName = $connection->quoteIdentifier('order');

    $orderJoinCondition = [
        $orderTableAliasName . '.entity_id = order_items.order_id',
        $connection->quoteInto("{$orderTableAliasName}.state <> ?", \Magento\Sales\Model\Order::STATE_CANCELED),
    ];

    if ($from != '' && $to != '') {
        $fieldName = $orderTableAliasName . '.created_at';
        $orderJoinCondition[] = $this->prepareBetweenSql($fieldName, $from, $to);
    }

    $this->getSelect()->reset()->from(
        ['order_items' => $this->getTable('sales_order_item')],
        [
            'ordered_qty' => 'SUM(order_items.qty_ordered)',
            'order_items_name' => 'order_items.name',
            'order_items_sku' => 'order_items.sku'
        ]
    )->joinInner(
        ['order' => $this->getTable('sales_order')],
        implode(' AND ', $orderJoinCondition),
        []
    )->where(
        'order_items.parent_item_id IS NULL'
    )->group(
        'order_items.product_id'
    )->having(
        'SUM(order_items.qty_ordered) > ?',
        0
    );
    return $this;
}
| improve this answer | |
  • This worked perfectly! Why has this still not been implemented into the Magento code (I am currently using 2.2.7)? Also I just want to note that you should never modify core code. Please create a module and add these changes. – RLTcode Dec 18 '18 at 23:43
0

This is a known bug and will be fixed in the 2.2.10 and 2.3.3 release: https://github.com/magento/magento2/pull/22806

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.