Re: Custom Sales Reports by Product (item total and by qty)
Run this sql at the end of the month -- cronjob for just before midnight works best tested on 4.5.4 - YMMV
select a.orderid, from_unixtime(b.date), a.productid, a.productcode, a.product, a.price
FROM xcart_order_details a, xcart_orders b
WHERE a.orderid in
(
select orderid from `xcart_orders` where status in ('S','P',' ')
and EXTRACT(YEAR_MONTH FROM FROM_UNIXTIME(date)) = EXTRACT(YEAR_MONTH FROM SUBDATE(CURDATE(), INTERVAL 1 DAY))
) and a.orderid = b.orderid;
Or this one daily ..
select a.orderid, from_unixtime(b.date), a.productid, a.productcode, a.product, a.price
FROM xcart_order_details a, xcart_orders b
WHERE a.orderid in
(
select orderid from `xcart_orders` where status in ('S','P',' ')
and EXTRACT(YEAR_MONTH FROM FROM_UNIXTIME(date)) = EXTRACT(YEAR_MONTH FROM SUBDATE(CURDATE(), INTERVAL 1 DAY))
and EXTRACT(DAY FROM FROM_UNIXTIME(date)) = EXTRACT(DAY FROM SUBDATE(CURDATE(), INTERVAL 1 DAY))
) and a.orderid = b.orderid;
__________________
X-Cat 4.5.4 & (1/2)
Live June 1, 2013.
|