SQL to list all skus, inventory on hand and qty sold between x and x date

Question

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");

Answer 1

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.