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

February 2nd, 2013 by Richy B. Leave a reply »

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):

SELECT
COUNT(*) AS ordercount,SUM(nettotal) AS nettotal,SUM(taxamount) AS taxamount,taxstatus,paymenttype
FROM
(
SELECT
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
FROM
sales_flat_order
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
WHERE
sales_flat_order.state='complete'
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

This post is over 6 months old.

This means that, despite my best intentions, it may no longer be accurate.

This blog holds over 12 years of archived content - during that time, I may have changed my opinion of something, technology will have advanced (and old "best standards" may no longer be the case), my technology "know how" has improved etc etc - it would probably take me a considerable amount of time to update all the archival entries: and defeat the point of keeping them anyway.

Please take these posts for what they are: a brief look into my past, my history, my journey and "caveat emptor".

Leave a Reply

Human Verification: In order to verify that you are a human and not a spam bot, please enter the answer into the following box below based on the instructions contained in the graphic.


gamy-dance