1

I have a Magento 1.9 site and I am trying to write a sql query that would return a listing of all skus (disabled and enabled) in the site as well as how many of each sku was sold between a specific date range and then also show how many qty are currently on-hand still for the item.

My issue is I have the below query however it only lists the data I need of the items that were sold between x and x date. The below query is not listing all items even items that were not sold between the stated dates.

I am not sure how to change the query. I need it all as one query since I am exporting the result to excel

  //form post variables  
  $fromdate = mysqli_real_escape_string($cn, $_POST['volumefrom']);
  $todate = mysqli_real_escape_string($cn, $_POST['volumeto']); 

  $list = mysqli_query($cn, "SELECT sales_flat_order_item.name, sales_flat_order_item.sku, sum(sales_flat_order_item.qty_ordered) As Qty_Sold, 
                          cataloginventory_stock_item.qty AS On_Hand FROM sales_flat_order_item, catalog_product_entity_varchar, 
                          catalog_product_entity, cataloginventory_stock_item 
                          WHERE catalog_product_entity_varchar.attribute_id = 71 AND catalog_product_entity_varchar.entity_id = cataloginventory_stock_item.product_id AND catalog_product_entity_varchar.entity_id = catalog_product_entity.entity_id AND
                          sales_flat_order_item.sku = catalog_product_entity.sku AND sales_flat_order_item.price > 0 AND 
                         sales_flat_order_item.created_at >= '$fromdate 00:00:00' AND sales_flat_order_item.created_at <= '$todate 23:59:59' 
                         GROUP BY sales_flat_order_item.sku ORDER BY sales_flat_order_item.sku LIMIT 1000");
  • Why don't you use Magento's collections instead of direct SQL? Have you tried it? – Julien Lachal May 31 '17 at 15:59
  • Because I am running this query outside of Magento on a remote server that connects to the database to run custom reports like this within an external reporting portal. – Jayreis May 31 '17 at 16:07
  • I suggest using magento collections then print out the query to see what magento uses. So, after getting the collection do $collection->getSelectSql(true) – MagentJo May 31 '17 at 21:34
  • Also, design wise, you may just want to set up a magento controller that takes a post of whatever values and then does the collections then outputs the result via JSON and have your remote server ping the magento controller with your post variables and retrieve whatever data necessary from the JSON you produce on the magento side.... just a thought... kind of like constructing an api – MagentJo May 31 '17 at 21:39
0

Well you could get Magento to do all the work, I suppose. Why would you want to do that, you're just taking resource off your store? As to moving text files around via JSON - you've got to extract it, dump it somewhere, arrange security on the file location, consume the JSON string across the network, parse it into a database location for reporting and keep an eye on the job to make sure it's running reliably.

Or you could just use ODBC and talk directly to MySQL.

Actually in terms of the question, it's tricky because you have to understand all of Magentos little quirks, from your code I can see immediately that you're not accounting for configurable and simple items, so the danger here is that you're duplicating lines on the join, you need to ensure you're getting uniques before joining to sales. I'd build a product model query, build a stock model query and build a sales model and then relate them together on the other side of the fence, it makes life a lot easier. You could create a MySQL stored procedure to do this, but they're not very efficient because MySQL doesn't precompile.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.