Have a SKU, need to find ProductID

[Thistle3408]

I bet this is simple; I'm still figuring this out.

I am on the admin side. I will have the SKU for a product that I can use to search/identify the product (that's all I will have).
I need to know how to use the SKU to get to the rest of the product information, specifically I need to see/update the inventory on hand. Once I have the product identified properly I will be looking at the "shippable", "warehouse" and "inventory tracked (Y/N) to decide what to do. If the product is shippable, from the specified warehouse and inventory is tracked, then I will update the inventory on hand.

(Must be in code, can't use existing batch or product edit, this will be behind the scenes so to speak.)

FYI, we're getting an XML, with several nodes (one for each SKU), that I'll have to loop though from another source.

[mazhar]

You can search by SKU in search box available in admin header section. Choose product option for Search In dropdown list and provide your product SKU in Search For text box.

[Thistle3408]

I was looking for C# code to do this.

Maybe there is code that processes that search in the admin header that I can clone....what .cs module would that me in?

[mazhar]

Try to make use of following code. It will return a list of products having specified SKU value.

Code: Select all

List<Product> products = ProductDataSource.FindProducts(string.Empty, "YOUR SKU HERE", 0, 0, 0);

[mazhar]

Here is a utility function. Simply pass it the SKU and it will return corresponding product.

Code: Select all

public Product GetProductForSku(string sku) 
    {
        ProductCollection products = ProductDataSource.LoadForCriteria(string.Format(" Sku = '{0}' ", sku));
        if (products.Count > 0)
            return products[0];
        return null;
    }

[Thistle3408]

mazhar,

Thanks, might there also be a similar way to find OrderID for any orders (therefore a collection/list) that have a specific ProductID in their baskets?

[mazhar]

Have a look at this, you can load all orders having desired product id by something like this

Code: Select all

public OrderCollection GetOrderForProductId(int productId)
    {
        OrderCollection orders = OrderDataSource.LoadForCriteria(string.Format(" ProductId = '{0}' ", productId));
return orders;
    }

[Thistle3408]

All very helpful.

Let's take this a step further....

Is there a nice tight way to find essentially what would be the intersection of
Order Status = "your status"
Product = productID within those orders
such that we can calculate the sum of the quantities ordered.

I guess I could create a collection of orders that have the desired status and then scroll through the items in each of those orders looking for the specific ProductID and up the quantities, but that seems inefficient.

Said a different way, if I know the ProductID and know an OrderStatus I want to efficiently obtain the sum of the quantities of the specified ProductID in the collection of orders that meet the OrderStatus criteria.

[Thistle3408]

Let me ask that in this way....

Is it possible to alter this:
OrderCollection orders = OrderDataSource.LoadForCriteria(string.Format(" ProductId = '{0}' ", productId))
to have an "AND" condition something like:
OrderCollection orders = (OrderDataSource.LoadForCriteria(string.Format(" ProductId = '{0}' ", productId)) && OrderDataSource.LoadForCriteria(string.Format(" StatusId = '{0}' ", statusId)))

[mazhar]

You can do that as following

Code: Select all

OrderCollection orders = OrderDataSource.LoadForCriteria(string.Format(" ProductId = '{0}' AND OrderStatusId = {1} ", productId,orderStatusId));

[Thistle3408]

So will this give me the sum of the quantities ordered in all orders that have the two statuses and also contain at least one item with the given productID? Even if the productID is part of a kit or not?

Code: Select all

							
// looking for orders with these two "statuses" and the productID of int ProdID

                                                        string StatProc = "7";
							string StatRFF = "2";
							int PROCqty = 0;
							// find the orders that have the correct order status and this product.
							OrderCollection orders = OrderDataSource.LoadForCriteria(string.Format(" ProductId = '{0}' AND OrderStatusId = {1}  AND OrderStatusId = {2} ", ProdID, StatProc, StatRFF));  

//Now I ought to have a collection of orders that have this product in them and the correct status.
							// Let's see if we have any?
							if (orders != null && orders.Count > 0) 	
							{
								// we get here because there are orders with the status and product we are worried about, so count 'em up.
								foreach (Order order in orders)
									{
										// put the add qty stuff here.
										 foreach (OrderItem item in order.Items)	
											{
												if (item.productID == ProdID) PROqty = PROqty + item.Quantity;
											}
									}


								// so now we have the qty deducted from AC but not WG, let's net it and save it
								ACqty = WGqty - PROCqty;
								product.InStock = ACqty; 
								product.Save();
							}
							else
								{
									product.InStock = WGqty;
            							        product.Save();
									// we got here by finding NO orders that had the status and product we are worried about.

								}

[mazhar]

Yeah it will, you just need to update order load statement as below

Code: Select all

OrderCollection orders = OrderDataSource.LoadForCriteria(string.Format(" ProductId = '{0}' AND OrderStatusId IN ({1},{2}) ", ProdID, StatProc, StatRFF));  

[Thistle3408]

mazhar,


Looks like we need to fix this a tad more. Getting:
SqlException: Invalid column name 'ProductId'.

Don't we need to do something like a "join" to get the Orderitems that would have the ProductId as a column? I am not the most knowledgable SQL person...


From the app.log:
OrderId,OrderNumber,OrderDate,StoreId,UserId,AffiliateId,BillToFirstName,BillToLastName,BillToCompany,BillToAddress1,BillToAddress2,BillToCity,BillToProvince,BillToPostalCode,BillToCountryCode,BillToPhone,BillToFax,BillToEmail,ProductSubtotal,TotalCharges,TotalPayments,OrderStatusId,Exported,RemoteIP,Referrer,GoogleOrderNumber,PaymentStatusId,ShipmentStatusId FROM ac_Orders WHERE ProductId = 1118 AND OrderStatusId IN (7,2)
System.Data.SqlClient.SqlException: Invalid column name 'ProductId'.

[Thistle3408]

figured this out...thanks