Orders by Manufacturer

[nikikelly]

Hi,

I'm wondering if anyone can offer me any suggestions on how to list my orders by manufacturer. Ideally would like to have an admin/orders.php page for each manufacturer that only shows those orders that have products by those manufacturers. I have my store set up so that each order is only going to have one manufacturer's products so I really just need help with setting up the SQL in the orders.php page to pull in a particular manufacturer. I intend to create a new orders_manufacturer1.php page for each manufacturer. SO, really I'll be using the orders.php page to do what it does currently - listing out ALL orders for the main admin person. But then I need to create separate order admin pages for each specific manufacturer. I guess this could be accomplished in the reporting section too......any advice greatly appreciated!

 

NK

[nikikelly]

Somehow I need to get the manufacturer ID into the results based on the ID that's passed via the URL (http://url.com/catalog/admin/orders.php?aID=1)

 

Here is the query I believe I need to alter:

$orders_query_raw = "select o.orders_id, o.customers_name, o.customers_id, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.customers_id = '" . (int)$cID . "' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by orders_id DESC";

 

And this is my attempt but I'm terrible at joins....

 

$orders_query_raw = "select o.orders_id, o.customers_name, o.customers_id, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, p.manufacturers_id , ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_PRODUCTS . " p, " . TABLE_ORDERS_STATUS . " s where p.manufacturers_id = '" . (int)$aID . "' and o.customers_id = '" . (int)$cID . "' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by orders_id DESC";

 

But this doesn't work.....maybe someone can see what I'm trying to do and correct my sad SQL attempt....

[nikikelly]

I think I might be on to something....

 

$orders_query_raw = "select o.orders_id, o.customers_name, o.customers_id, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, p.manufacturers_id , ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) left join " . TABLE_ORDERS_PRODUCTS . " op on (o.orders_id = op.orders_id) left join " . TABLE_PRODUCTS . " p on (op.products_id = p.products_id), " . TABLE_ORDERS_STATUS . " s where p.manufacturers_id = '" . (int)$aID . "' and o.customers_id = '" . (int)$cID . "' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by orders_id DESC";

 

Anyone care to verify that I am in fact - truly grabbing only those orders that are of a particular manufacturer_id pulled from the Products table? I have two left joins (one for pulling in the orders_products table on the orders_id and the other for pulling in the products table on the products_id....