Ecommerce dashboard - Orders by day

Michael Knudsen

Posted on 28/07/2023 12:38:55

Does anyone know what determines whether an order is included in the 'Orders by day' widget?

I assume it's based on the EcomOrders tabel, but which fields has to be filled in order to have it in the 'Orders by day' widget?

Br. Michael Knudsen

Replies

Shiwanka Chathuranga

Posted on 31/07/2023 11:17:57

[OrderComplete]=TRUE from EcomOrder Table

Michael Knudsen

Posted on 31/07/2023 14:27:25

Hello Shiwanka,

- thanks for your response, but even the Shopify order has 'OrderComplete'=True and 'OrderStateId'=OS2 (completed), it still dosn't count in the 'Orders by day' widget :-/

Do you have any other suggestions?

Br. Michael Knudsen

Imar Spaanjaars

Posted on 31/07/2023 16:17:10

This post has been marked as an answer

Here's the SQL that order by day executes:

sqlBuilder.Add("SELECT CONVERT(CHAR(11), EcomOrders.OrderCompletedDate, 102) AS OrderCompletedDate, COUNT(*) AS OrderDateCounter ");
sqlBuilder.Add(EcommerceHelper.GetOrderQueryForWidgets());
sqlBuilder.Add("AND DATEDIFF(DAY, EcomOrders.OrderCompletedDate, GETDATE()) <= 6");
sqlBuilder.Add("GROUP BY CONVERT(CHAR(11), EcomOrders.OrderCompletedDate, 102) ");
sqlBuilder.Add("ORDER BY CONVERT(CHAR(11), EcomOrders.OrderCompletedDate, 102) ");

And here's how GetOrderQueryForWidgets looks:

public static CommandBuilder GetOrderQueryForWidgets()
{
    var sqlBuilder = new CommandBuilder()
        .Add("FROM EcomOrders ")
        .Add("INNER JOIN EcomOrderStates ON EcomOrderStates.OrderStateID = EcomOrders.OrderStateID ")
        .Add("WHERE EcomOrderStates.OrderStateDontUseInstatistics = {0} ", false)
        .Add("AND EcomOrders.OrderComplete = {0} ", true)
        .Add("AND EcomOrders.OrderDeleted = {0} ", false)
        .Add("AND EcomOrders.OrderIsLedgerEntry = {0} ", false)
        .Add("AND (EcomOrders.OrderIsRecurringOrderTemplate IS NULL OR EcomOrders.OrderIsRecurringOrderTemplate = {0}) ", false);

    return sqlBuilder;
}

Do all of these match?

Also, the query uses OrderCompletedDate so make sure the order has a value there as well.

Imar

Votes for this answer: 1

Michael Knudsen

Posted on 01/08/2023 08:28:36

Thanks for the detailed respons Imar! It was exactly the OrderCompletedDate that I was missing, so this will be set to the same as OrderDate from now on.

Br. Michael Knudsen

You must be logged in to post in the forum

Developer forum

Ecommerce dashboard - Orders by day

Replies