Post by wafflemeister » Fri May 27, 2022 11:56 am

Opencart 1.5.6.4

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

Active Member

Posts

Joined
Fri Jun 21, 2013 11:10 pm

Post by wafflemeister » Fri May 27, 2022 9:56 pm

Following up to my own question, here is the solution if anyone cares...

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
            AND date(o.date_added) >= '2018-01-01'
            AND date(o.date_added) <= '2018-12-31'
        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 = '3'
) tmp
GROUP BY
    tmp.customer_id
ORDER BY
    total
DESC
LIMIT 0, 20

Active Member

Posts

Joined
Fri Jun 21, 2013 11:10 pm
Who is online

Users browsing this forum: No registered users and 67 guests