I am trying to run Report > Customers > Orders and having difficulties. It takes a very long time (+10min) to load and eventually results in the Page Not Found browser error. I suspect that this is related to the fact that I have over 30,000 orders and somewhere it just times out.
I am able to run the same sql query behind the report in myphpadmin manually without any issues, which is all I need in my case. The query that is run by default is the script below. How can I modify this query so that it only pulls orders in "shipped" status and orders let's say from 01.01.2017 until 12.31.2017?
Code: Select all
SELECT
tmp.customer_id,
tmp.customer,
tmp.email,
tmp.customer_group,
tmp.status,
COUNT(tmp.order_id) AS orders,
SUM(tmp.products) AS products,
SUM(tmp.total) AS total
FROM
(
SELECT
o.order_id,
c.customer_id,
CONCAT(o.firstname, ' ', o.lastname) AS customer,
o.email,
cgd.name AS customer_group,
c.status,
(
SELECT
SUM(op.quantity)
FROM
`oc_order_product` op
WHERE
op.order_id = o.order_id
GROUP BY
op.order_id
) AS products,
o.total
FROM
`oc_order` o
LEFT JOIN `oc_customer` c ON
(o.customer_id = c.customer_id)
LEFT JOIN oc_customer_group_description cgd ON
(
c.customer_group_id = cgd.customer_group_id
)
WHERE
o.customer_id > 0 AND cgd.language_id = '1' AND o.order_status_id > '0'
) tmp
GROUP BY
tmp.customer_id
ORDER BY
total
DESC
LIMIT 0, 20