Press "Enter" to skip to content

PHP: Magento: Extract orders based on tax status and payment type

When you are doing your quarterly VAT returns and inputting details of your Magento shopping cart ordersinto your accounting software (such as the brilliant Crunch system), wouldn’t it be handy to be able to get a simple list of all orders between two dates, whether tax was paid on the order or not (note: this query will NOT work if you have orders with taxable and non-taxable items) and the method of payment (if you are running the SagePaySuite, it’ll also list whether payment was made via Amex):

COUNT(*) AS ordercount,SUM(nettotal) AS nettotal,SUM(taxamount) AS taxamount,taxstatus,paymenttype
sales_flat_order.increment_id AS orderid,
sales_flat_order.entity_id AS internalid,
sales_flat_order.base_total_paid-sales_flat_order.base_tax_amount AS nettotal,
IF (sales_flat_order.base_tax_amount>0,'tax','untaxed') AS taxstatus,
sales_flat_order.base_tax_amount AS taxamount,
IF (sales_flat_order_payment.method='sagepayform',IF (sagepaysuite_transaction.card_type='AMEX','Amex','SagePay'),sales_flat_order_payment.method) AS paymenttype
JOIN sales_flat_order_payment ON sales_flat_order_payment.parent_id=sales_flat_order.entity_id
LEFT OUTER JOIN sagepaysuite_transaction ON sagepaysuite_transaction.order_id=sales_flat_order.entity_id
AND sales_flat_order.created_at>='2012-11-01' AND sales_flat_order.created_at<='2013-01-31' ) AS subquery GROUP BY subquery.taxstatus,subquery.paymenttype