I'm a .NET developer and not that good with php, I'm also new to opencart so I really appreciate any help on this one.
Basically what i need to know is how to create a custom report on opencart.
I know all reports must be based on a query so I have one that just works fine when tested on php mysql admin.
Now, i have to put it on a report and add a menu to opencart.
Here is the query I'm using:
The query returns the following columns:
Code: Select all
SELECT product.image, order_product.product_id, manufacturer.manufacturer_id, manufacturer.name AS manufacturer_name, order_product.name AS product_name, CAST(order_option.value AS CHAR(10000) CHARACTER SET utf8) AS size, product.location, order_product.model, SUM(order_product.quantity) AS total_ordered FROM order_product INNER JOIN `order` ON order_product.order_id = order.order_id INNER JOIN product ON order_product.product_id = product.product_id INNER JOIN manufacturer ON product.manufacturer_id = manufacturer.manufacturer_id LEFT JOIN order_option ON order_product.order_product_id = order_option.order_product_id WHERE order.order_status_id = 5 GROUP BY product.image, order_product.product_id, manufacturer.manufacturer_id, manufacturer.name , order_product.name, size, product.location, order_product.model ORDER BY manufacturer.name , order_product.name
size (the only attribute I use on my items)
KIND OF OFF TOPIC - IF YOU WANT TO UNDERSTAND WHAT THE QUERY DOES:
To understand the query you have to understand the scenario:
- I don't keep stock, so when somebody order something I have to go to the supplier and get it.
- Let's say i have 10 new orders and 5 people ordered different quantities of the same item.
- What I do today is to manually go through each item on each order and create a spreadsheet with the totals.
It's time consuming and I'm always adding something or missing something.
The query will do this for me. Group same items from all paid orders and give me a pretty list I can just print and take with me to my supplier
Thanks in advance;