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:
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
image
product_id
manufacturer_id
manufacturer_name
product_name
size (the only attribute I use on my items)
location
model
total_ordered
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;
