1

I am trying to create custom report and want to display admin grid with columns as below: Interval(Period),Customer Group,Numbers of Orders,Average Order Amount,Total Order Amount

I have run the query in sql as below.

SELECT customer_group.customer_group_code as customergroup ,count(sales_order.entity_id) as totalorder,AVG(sales_order.base_grand_total) as avgorder, sum(sales_order.base_grand_total) as grandtotal FROM sales_order,customer_group where customer_group.customer_group_id=sales_order.customer_group_id GROUP BY sales_order.customer_group_id

Below is the output:

enter image description here

I got the result with data. Just unable to set these in collection and display in admin grid.Below is the code of collection.php

 <?php namespace Vendor\Module\Model\ResourceModel\Product\Sold;
class Collection extends \Magento\Reports\Model\ResourceModel\Order\Collection
{
    /**
     * Set Date range to collection
     *
     * @param int $from
     * @param int $to
     * @return $this
     */
    public function setDateRange($from, $to)
    {
        $this->_reset()->addAttributeToSelect(
            '*'
        )->addOrderedQty(
            $from,
            $to
        )->setOrder('ordered_qty',self::SORT_ORDER_DESC);
        return $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);
        }

    //here I am unable to get the collection
    $this->getSelect()->reset()->from(
        ['order' => $this->getTable('sales_order')],
        ['totalorder' => 'count(sales_order.entity_id)', 
            'avgorder' => 'AVG(sales_order.base_grand_total)',
            'grandtotal' => 'sum(sales_order.base_grand_total)' ],
            implode(' AND ', $orderJoinCondition)
    )->joinInner(
        ['customer' => $this->getTable('customer_group')],
        ['customercode' => 'customer.customer_group_code']
    )->where(
        'customer.customer_group_id=order.customer_group_id'
    )->group(
        'order.customer_group_id'
    );



    return $this;
}


public function setStoreIds($storeIds)
{
    if ($storeIds) {
        $this->getSelect()->where('order_items.store_id IN (?)', (array)$storeIds);
    }
    return $this;
}


public function setOrder($attribute, $dir = self::SORT_ORDER_DESC)
{
    if (in_array($attribute, ['orders', 'ordered_qty'])) {
        $this->getSelect()->order($attribute . ' ' . $dir);
    } else {
        parent::setOrder($attribute, $dir);
    }

    return $this;
}


protected function prepareBetweenSql($fieldName, $from, $to)
{
    return sprintf(
        '(%s BETWEEN %s AND %s)',
        $fieldName,
        $this->getConnection()->quote($from),
        $this->getConnection()->quote($to)
    );
} }
2
  • Did you try reindex?
    – Rv Singh
    Aug 9, 2019 at 10:52
  • Yes I did already. Aug 9, 2019 at 10:54

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.