MYSQL Searches for use with Zencart [2]

2011 extract badge info

SELECT 
DISTINCT 
o.orders_id,
name.products_options_values, 
affiliation.products_options_values, 
o.customers_email_address
FROM 
orders_products_attributes AS name, 
orders_products_attributes AS affiliation, 
orders_products_attributes AS opa, 
orders AS o, 
orders_products AS op
WHERE 
name.orders_products_id = affiliation.orders_products_id
AND name.products_options = 'Name'
AND affiliation.products_options = 'Affiliation'
AND name.orders_id = o.orders_id
AND op.orders_id = o.orders_id
AND (op.products_id = '66' 
OR op.products_id = '68')

2011 extract registrations

SELECT o.orders_id, o.date_purchased, o.customers_id,
o.customers_name, op.products_id, op.products_name,
opa.products_options, opa.products_options_values, op.final_price
FROM orders AS o, orders_products AS op
LEFT JOIN orders_products_attributes AS opa 
ON op.orders_products_id = opa.orders_products_id
AND opa.products_options = 'Register for the 2011 TEI Conference'
WHERE op.orders_id = o.orders_id
AND (op.products_id = '66' 
OR op.products_id = '68')
AND o.date_purchased > '2011-07-01 00:00:00'
ORDER BY op.products_id

Extract workshop registrations:

SELECT opa.products_options_id, opa.products_options,
opa.products_options_values, o.customers_name 
FROM orders AS o, orders_products AS op 
LEFT JOIN orders_products_attributes AS opa 
ON op.orders_products_id = opa.orders_products_id 
WHERE o.date_purchased > '2011-07-01 00:00:00' 
AND products_options_id > '27' 
AND op.orders_id = o.orders_id 
AND opa.products_options_values != 'No optional activity'
AND op.products_id='66' 
OR op.orders_id = o.orders_id AND op.products_id='68' 
ORDER BY opa.products_options

Extract all orders on a product by product basis, ordered by product, including the option for year end of subscription if it is present and including any comments about refunds (note: the last two work only because I specifically the options I am looking for have excluded everything else).

SELECT o.orders_id, o.date_purchased, o.customers_id,
o.customers_name, op.products_id, op.products_name,
opa.products_options, opa.products_options_values, op.final_price,
osh.comments 
FROM orders AS o, orders_products AS op 
LEFT JOIN orders_products_attributes AS opa 
ON op.orders_products_id = opa.orders_products_id 
AND opa.products_options = 'Valid until the last day of'
LEFT JOIN orders_status_history AS osh 
ON op.orders_id = osh.orders_id
AND osh.comments LIKE '%REFUND INITIATED%'
WHERE op.orders_id = o.orders_id
AND o.date_purchased > '2011-09-30 23:59:59'
AND o.date_purchased < '2011-11-01 00:00:00'
ORDER BY op.products_id

To build a list of names and affiliations for conference badges (<2010):

SELECT name.products_options_values, affiliation.products_options_values
FROM orders_products_attributes AS name, 
orders_products_attributes AS affiliation
WHERE name.orders_products_id = affiliation.orders_products_id
AND name.products_options = 'Name'
AND affiliation.products_options = 'Affiliation';

Same thing, with email addresses:

SELECT name.products_options_values, 
affiliation.products_options_values, 
customers.customers_email_address
FROM orders_products_attributes AS name, 
orders_products_attributes AS affiliation, orders AS customers
WHERE name.orders_products_id = affiliation.orders_products_id
AND name.products_options = 'Name'
AND affiliation.products_options = 'Affiliation'
AND name.orders_id = customers.orders_id

The gets a list of subscribers:

SELECT customers.customers_name, customers.customers_email_address, 
workshop.products_options_values
FROM orders AS customers, orders_products_attributes AS workshop
WHERE customers.orders_id = workshop.orders_id 
AND (workshop.products_options_values = '2009'
OR workshop.products_options_values = '2010'
OR workshop.products_options_values = '2011'
OR workshop.products_options_values = '2012'
OR workshop.products_options_values = '2013')

This search pulls all the registrations for a specific workshop plus the number of orders in each case:

SELECT customers.customers_name, customers.customers_email_address, 
workshop.products_options_values, orders.products_quantity 
FROM orders AS customers, orders_products_attributes AS workshop, 
orders_products AS orders 
WHERE orders.products_name = 'Register for a Preconference Workshop' 
AND orders.orders_id = customers.orders_id 
AND orders.orders_id = workshop.orders_id 
AND workshop.products_options_values = 
'A TEI-based Publishing Workflow, Nov. 11, 15:00-18:00.'

This one builds a list of all invoices:

SELECT 
orders.lp_trans_num AS 1stData_Invoice_No, 
lp.transaction_reference_number AS 1stData_Reference_No,
orders.orders_id AS Zencart_Invoice_No,
orders.date_purchased AS Purchase_Date,
orders.billing_name AS Invoice_Name,
orders.order_total AS Invoice_Tot
FROM
orders,
linkpoint_api AS lp
WHERE 
orders.lp_trans_num > 0
AND
lp.lp_trans_num = orders.lp_trans_num;

This breaks the invoices down by item. It has one weakness: it can’t accommodate discount information from the table orders_total: this is because I need to do some kind of self-join on orders_total before using it: the table has 2-3 rows for each order (1 for sub-total, 1 for discounts (if any), potentially one more for coupons, and 1 for total), all in the same column.

SELECT 
orders.lp_trans_num AS 1stData_Invoice_No, 
lp.transaction_reference_number AS 1stData_Reference_No,
orders.orders_id AS Zencart_Invoice_No,
orders.date_purchased AS Purchase_Date,
orders.billing_name AS Invoice_Name,
orders.order_total AS Invoice_Tot,
products.products_name AS Item,
CONCAT(attributes.products_options,': ',attributes.products_options_values) AS Options,
products.final_price AS Unit_Price,
products.products_quantity AS Quantity,
products.final_price * products.products_quantity AS Item_SubTotal
FROM
orders,
orders_products AS products,
orders_products_attributes AS attributes,
linkpoint_api AS lp
WHERE 
orders.lp_trans_num > 0
AND
orders.orders_id = products.orders_id
AND
products.orders_id = attributes.orders_id
AND
products.orders_products_id = attributes.orders_products_id
AND
lp.lp_trans_num = orders.lp_trans_num
AND
(attributes.products_options = 'Valid until the last day of'
OR
attributes.products_options = 'Registration Category'
OR
attributes.products_options = 'Pre-conference Workshop')
;