MYSQL Searches for use with Zencart
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 = '69'
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 = '69'
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='69'
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')
;
Comment [2]
dan (Sat Nov 6, 2010 (08:01:11)) [PermLink]: Limited by order type: SELECT name.products_options_values, affiliation.products_options_values
FROM orders_products_attributes AS name,
orders_products_attributes AS affiliation, orders AS date, orders_products as product
WHERE name.orders_products_id = affiliation.orders_products_id
AND name.orders_id = date.orders_id
AND product.orders_products_id = name.orders_products_id
AND product.products_id = 55
AND date.orders_id > 151
AND name.products_options = ‘Name’
AND affiliation.products_options = ‘Affiliation’
LIMIT 0,100;dan (Sat Nov 6, 2010 (08:44:28)) [PermLink]: This one extracts all current subscriptions after the cut-off order date:
SELECT customers.customers_name, customers.customers_email_address,
date.products_options_values
FROM orders AS customers, orders_products_attributes AS date
WHERE customers.orders_id = date.orders_id
AND date.orders_id > 151
AND products_options_values > 2009
LIMIT 0,100;
