SELECT
o.OrderID,
-- MAX(pl.pay_authdate) as Date,
MAX(o.CustomerID) AS CustomerID,
DATE_FORMAT(MAX(o.OrderDate), '%Y%m%d') AS TransactionDate,
DATE_FORMAT(MAX(o.OrderDate), '%Y%m%d') AS AuthorizedDate,
DATE_FORMAT(MAX(o.OrderDate), '%Y%m%d') AS CapturedDate,
MAX(o.ShipAddress1) AS ShipToAddr1,
MAX(o.ShipAddress2) AS ShipToAddr2,
MAX(o.ShipCity) AS ShipToCity,
MAX(o.ShipState) AS ShipToState,
MAX(LEFT(o.ShipPostalCode,5)) AS ShipToZip5,
MAX(SUBSTRING(o.ShipPostalCode,7,4)) AS ShipToZip4,
'1' AS CartItemIndex,
'1' AS CartItem,
'00000' AS CartItemTIC,
SUM(CASE
WHEN pl.Pay_Result = 'CREDIT'
THEN -pl.Pay_Amount
ELSE pl.Pay_Amount
END)
AS CartItemPrice,
'1' AS CartItemQty,
MAX(IFNULL(o.SalesTaxRate1, 0) + IFNULL(o.SalesTaxRate2, 0) + IFNULL(o.SalesTaxRate3, 0)) AS CartItemTaxRate,
'' AS CartItemTaxAmount,
MAX(o.PaymentAmount) AS CurrentBalance,
CASE
	WHEN c.paysstatetax = 'N' THEN 'Tax Exempt'
	ELSE NULL
END AS Tax_Exempt,
o.ShipCountry

FROM orders o
JOIN payment_log pl ON o.OrderID = pl.Pay_OrderID
JOIN customers c ON o.customerid = c.customerid

WHERE YEAR(o.orderdate) = YEAR(CURDATE())
AND MONTH(o.orderdate) = MONTH(curdate())
AND (pl.Deleted IS NULL OR pl.Deleted != 'Y')
AND pl.Pay_Result IN ('CREDIT','DEBIT','CAPTURE')
AND o.salestaxrate1 = 0
AND o.order_entry_system NOT LIKE 'MARKETPLACE'
AND o.order_entry_system NOT LIKE 'vMARKETPLACE'
AND o.ShipCountry = 'United States'
AND o.ShipState NOT IN ('NH','MS','OR','MT','DE','AK','AA','AE','AP','PR','GU','VI','MP','AS')
AND c.paysstatetax IS NULL
GROUP BY o.OrderID
ORDER BY o.OrderID DESC
-- LIMIT 12 -- TESTING