Financial reporting for CS-Cart
I have three SQL Queries that make my reporting life a little more comfortable.
I’ll jump right in assuming you know how to run SQL via phpMyAdmin.
Total orders for a given month and year
SELECT `order_id` , DATE( FROM_UNIXTIME( `timestamp` ) ) AS order_date, `total`
FROM `cscart_orders`
WHERE `status` = 'P'
AND DATE_FORMAT( DATE( FROM_UNIXTIME( `timestamp` ) ) , '%Y' ) = '2012'
AND DATE_FORMAT( DATE( FROM_UNIXTIME( `timestamp` ) ) , '%m' ) = '07'
ORDER BY `order_date` ASCTotal order value for a given month and year
SELECT Sum(`total`) , DATE_FORMAT( DATE( FROM_UNIXTIME( `timestamp` ) ) , '%m' ) AS order_month, DATE_FORMAT( DATE( FROM_UNIXTIME( `timestamp` ) ) , '%Y' ) AS order_year FROM `cscart_orders` WHERE `status` = 'P' AND DATE_FORMAT( DATE( FROM_UNIXTIME( `timestamp` ) ) , '%Y' ) = '2012' AND DATE_FORMAT( DATE( FROM_UNIXTIME( `timestamp` ) ) , '%m' ) = '07' ORDER BY `order_id` DESC
Total order value for a given day, month and year
SELECT Sum(`total`) ,DATE_FORMAT( DATE( FROM_UNIXTIME( `timestamp` ) ) , '%d' ) AS order_day, DATE_FORMAT( DATE( FROM_UNIXTIME( `timestamp` ) ) , '%m' ) AS order_month, DATE_FORMAT( DATE( FROM_UNIXTIME( `timestamp` ) ) , '%Y' ) AS order_year FROM `cscart_orders` WHERE `status` = 'P' AND DATE_FORMAT( DATE( FROM_UNIXTIME( `timestamp` ) ) , '%Y' ) = '2012' AND DATE_FORMAT( DATE( FROM_UNIXTIME( `timestamp` ) ) , '%m' ) = '07' AND DATE_FORMAT( DATE( FROM_UNIXTIME( `timestamp` ) ) , '%d' ) = '18' ORDER BY `order_id` DESC
Leave a Reply
Want to join the discussion?Feel free to contribute!