Create Order Reports (in sql or otherwise)

Hi

We sell event tickets, and have 2 products - Adult and Child tickets.

How can I run a report in SQL (via phpmyadmin) or otherwise to see how many of Adult tickets and Child tickets - basically products - were purchased?

I also need a list (group by account name).

e.g
PersonA brought 2 Adult tickets and 3 Child tickets
PersonB brought 5 Adult tickets and 2 Child tickets

The report should spit out something like:

PersonA
- Adult 2
- Child 3
PersonB
- Adult 5
- Child 2

Total
- Adult 7
- Child 5

This is basically to plan how many people will be attending.

Thanks...

Check out the developer documentation resources at www.hikashop.com/en/support/documentatio...r-documentation.html

Scroll to the end to see the data model and download it.

If you know how to read an ERD and can do some SQL wrapped in a bit of PHP then you're sorted. Looks great in SQLWorkbench.

Tony

I had to do this on a bit of an emergency notice.

Its great to have an ERD, I simply opened the tables in phpMyAdmin and figured out what I needed. I wll now check the ERD to see if I did something wrong (hopefully not).

For others who might be looking to do the same, here are the snippets of code.

FIRST, the SQL Query that will get Count of the Quantities. So I know I sold 'n' number of Adult Tickets and 'y' number of Child Tickets. This is to get a number so we can order food, arrange for appropriate venue, etc.

SELECT
count(tableprefix_hikashop_order_product.order_product_id) as Orders,
tableprefix_hikashop_order_product.order_product_name as Item,
sum(tableprefix_hikashop_order_product.order_product_quantity) as Quantity
FROM tableprefix_hikashop_order_product
INNER JOIN tableprefix_hikashop_order ON tableprefix_hikashop_order_product.order_id=tableprefix_hikashop_order.order_id
WHERE tableprefix_hikashop_order.order_status="confirmed"
Group By order_product_name

SECOND, Details of people attending, and what they purchased (line items from the order_products table). Had to work on some fancy inner joins (4 table join). If you want to limit to some particular products, use something on lines of -- AND tableprefix_hikashop_product.product_name like '%Membership' --

SELECT
tableprefix_users.name,
tableprefix_users.email,
tableprefix_hikashop_order.order_number,
DATE_FORMAT(FROM_UNIXTIME(tableprefix_hikashop_order.order_created),'%d/%m/%Y') as created,
tableprefix_hikashop_product.product_name as item,
tableprefix_hikashop_order_product.order_product_quantity as quantity,
tableprefix_hikashop_order_product.order_product_price as unit_price
FROM tableprefix_users
INNER JOIN tableprefix_hikashop_user ON tableprefix_hikashop_user.user_cms_id=tableprefix_users.id
INNER JOIN tableprefix_hikashop_order ON tableprefix_hikashop_order.order_user_id=tableprefix_hikashop_user.user_id
INNER JOIN tableprefix_hikashop_order_product ON tableprefix_hikashop_order_product.order_id=tableprefix_hikashop_order.order_id
INNER JOIN tableprefix_hikashop_product ON tableprefix_hikashop_product.product_id=tableprefix_hikashop_order_product.product_id
WHERE tableprefix_hikashop_order.order_status="confirmed"
ORDER BY tableprefix_users.name ASC, tableprefix_hikashop_order.order_created ASC

These two reports give me exactly what I was looking for. I embedded them into php, made some nice tables and the reports are available for the event organizers.

Hope this helps.