eStack Dashboard consists of the following modules.
- Welcome Message
- Top Seller (Past 7 Days)
SELECT * FROM ( SELECT p.id, p.sku, p.title, SUM(olp.qty) as volume, ca.name FROM RetailOrder ro INNER JOIN OOrder o ON ro.id = o.id INNER JOIN OrderLine ol ON ol.order_id = o.id INNER JOIN OrderLineProduct olp ON olp.orderLine_id = ol.id INNER JOIN Item p ON olp.product_id = p.id INNER JOIN ChannelAccount as ca ON o.channelAccount_id = ca.id WHERE o.checkoutDate >= '2024-08-05' AND o.checkoutDate <= '2024-08-12' GROUP BY p.sku -- , ca.name ) AS T ORDER BY volume DESC LIMIT 20
- Month To Date
SELECT SUM(t.lineTotalPrice) AS rev, SUM(shippingPrice) AS shippingRevenue, COUNT(o.id) AS ocnt, SUM(t.unitCnt) AS ucnt, MIN(o.checkoutDate) AS coMin, MAX(o.checkoutDate) AS coMax FROM OOrder o INNER JOIN ( SELECT ol.order_id AS order_id, SUM(olp.qty) * ol.qty AS unitCnt, COALESCE(ol.shippingPrice, 0) AS shippingPrice, (COALESCE(ol.qty, 0) * COALESCE(ol.itemPrice, 0)) + COALESCE(ol.shippingPrice, 0) AS lineTotalPrice FROM OrderLine ol INNER JOIN OrderLineProduct olp ON ol.id = olp.orderline_id GROUP BY order_id ) t ON t.order_id = o.id WHERE checkoutDate >= '2024-07-31 22:00:00' AND o.status IN ('active', 'complete') AND o.discr != 'DirectOrder';
- Last Month
SELECT SUM(t.lineTotalPrice) rev, COUNT(o.id) as ocnt, SUM(t.unitCnt) as ucnt FROM OOrder o INNER JOIN ( SELECT ol.order_id as order_id, SUM(olp.qty) * ol.qty as unitCnt, (COALESCE(ol.qty,0) * COALESCE(ol.itemPrice,0) ) + COALESCE(ol.shippingPrice,0) AS lineTotalPrice FROM OrderLine ol INNER JOIN OrderLineProduct olp ON ol.id = olp.orderline_id GROUP BY order_id ) t ON t.order_id = o.id WHERE checkoutDate >= '2024-07-01 22:00:00' and checkoutDate < '2024-07-31 22:00:00' AND o.status IN ('active', 'complete') AND o.discr != 'DirectOrder';
- Two Months Ago
SELECT SUM(t.lineTotalPrice) rev, COUNT(o.id) as ocnt, SUM(t.unitCnt) as ucnt FROM OOrder o INNER JOIN ( SELECT ol.order_id as order_id, SUM(olp.qty) * ol.qty as unitCnt, (COALESCE(ol.qty,0) * COALESCE(ol.itemPrice,0) ) + COALESCE(ol.shippingPrice,0) AS lineTotalPrice FROM OrderLine ol INNER JOIN OrderLineProduct olp ON ol.id = olp.orderline_id GROUP BY order_id ) t ON t.order_id = o.id WHERE checkoutDate >= '2024-06-01 22:00:00' and checkoutDate < '2024-07-01 22:00:00' AND o.status IN ('active', 'complete') AND o.discr != 'DirectOrder';
- Last 31 Days
Query how to get the last 31 Days Sales Report.
SELECT ca.name AS channelAccount, DATE(CONVERT_TZ(o.checkoutDate, 'UTC', 'America/Los_Angeles')) AS dayDate, SUM(t.unitCnt) AS unitCnt, SUM(shippingPrice) AS shippingRevenue, SUM(lineTotalPrice) AS revenue, COUNT(DISTINCT o.id) AS orderCnt, ca.id AS channelAccount_id, min(o.checkoutDate) AS coMin, max(o.checkoutDate) AS coMax FROM OOrder AS o LEFT JOIN ChannelAccount AS ca ON ca.id = o.channelAccount_id LEFT JOIN ( SELECT ol.order_id AS order_id, SUM(olp.qty) * ol.qty AS unitCnt, COALESCE(ol.shippingPrice, 0) AS shippingPrice, (COALESCE(ol.qty, 0) * COALESCE(ol.itemPrice, 0)) AS lineTotalPrice FROM OrderLine ol INNER JOIN OrderLineProduct olp ON ol.id = olp.orderline_id GROUP BY order_id ) t ON t.order_id = o.id WHERE o.status IN ('complete', 'active', 'stock-hold') AND o.checkoutDate >= '2024-07-11 22:00:00' AND o.checkoutDate < '2024-08-12 10:57:11' GROUP BY dayDate DESC, ca.name ASC;