SET @START = '{STARTDATE}', @END = '{ENDDATE}', @ACCOUNT = '{ACCOUNT}', @SUBACCOUNT = '{SUBACCOUNT}'; SET @QSOLD = 0, @QAVGINV = 0, @COGSSOLD = 0, @COGSAVGINV = 0, @RSOLD = 0, @RMARGIN = 0; SELECT v.DESCRIPTION, V.Account, NULL as '_', v.Inventory_Turns, v.Quantity_Sold, v.Avg_Inventory, NULL as '__', v.COGS_Turns, v.COGS, v.Avg_Inventory_Value, NULL as '___', v.Retail_Sold, v.Retail_Margin, NULL as '____', v.GMROI FROM (SELECT DESCRIPTION, Account, CONVERT( ROUND(Inv_Turns,1), decimal(10,1) ) AS Inventory_Turns, Quantity_Sold AS Quantity_Sold, @QSOLD := @QSOLD+Quantity_Sold AS Quantity_Sold2, CONVERT( ROUND(Avg_Inventory,1), char ) AS Avg_Inventory, @QAVGINV := @QAVGINV+CONVERT( ROUND(Avg_Inventory,1), char ) AS Avg_Inventory2, CONVERT( ROUND(COGS_Turns,1), decimal(10,1) ) AS COGS_Turns, CONVERT( ROUND(COGS,2), decimal(10,2) ) AS COGS, @COGSSOLD := @COGSSOLD+CONVERT( ROUND(COGS,2), decimal(10,2) ) AS COGS2, CONVERT( ROUND(Avg_Inv_Value,2), decimal(10,2) ) AS Avg_Inventory_Value, @COGSAVGINV := @COGSAVGINV+CONVERT( ROUND(Avg_Inv_Value,2), decimal(10,2) ) AS Avg_Inventory_Value2, CONVERT( ROUND(Retail_Sold,2), decimal(10,2) ) AS Retail_Sold, @RSOLD := @RSOLD+CONVERT( ROUND(Retail_Sold,2), decimal(10,2) ) AS Retail_Sold2, CONVERT( ROUND(Retail_Margin,2), decimal(10,2) ) AS Retail_Margin, @RMARGIN := @RMARGIN+CONVERT( ROUND(Retail_Margin,2), decimal(10,2) ) AS Retail_Margin2, CONVERT( ROUND(Retail_Margin/Avg_Inv_Value,1), decimal(10,1) ) AS GMROI FROM ( SELECT products.DESCRIPTION, CONCAT(products.REV_CATEGORY,':',products.REVENUE_SUBCATEGORY) AS Account, SUM(countvalue.COGS) AS COGS, AVG(countvalue.Inv_Value) AS Avg_Inv_Value, (SUM(countvalue.COGS))/(AVG(countvalue.Inv_Value)) AS COGS_Turns, SUM(countvalue.QOGS) AS Quantity_Sold, AVG(countvalue.Inventory) AS Avg_Inventory, (SUM(countvalue.QOGS))/(AVG(countvalue.Inventory)) AS Inv_Turns, SUM(countvalue.PRICE) AS Retail_Sold, (SUM(countvalue.PRICE)-SUM(countvalue.COGS)) AS Retail_Margin FROM ( SELECT * FROM ( SELECT DATE_FORMAT(POSTDATE,'%Y-%m-%d') AS POSTDATE, PRODUCT_ID, SUM(IF(Trans_Type = 'SALE', QUANTITY*(-1), 0)) AS QOGS, Inventory, SUM(IF(Trans_Type = 'SALE', (QUANTITY*UnitCost)*(-1), 0)) AS COGS, Inv_Value, ROUND(SUM(PRICE),2) AS PRICE FROM ( SELECT POSTDATE, PRODUCT_ID, Trans_Type, QUANTITY, Inventory, UnitCost, Inv_Value, PRICE FROM ( SELECT DATE_FORMAT(POSTDATE,'%Y-%m-%d') AS POSTDATE, IF( CAST(@PROD_ID AS UNSIGNED) = CAST(PRODUCT_ID AS UNSIGNED), @NEW := 0, @NEW := 1 ) AS New_Prod, (@PROD_ID := PRODUCT_ID) AS PRODUCT_ID, Trans_Type, QUANTITY, IF( @NEW = 1, @INV_VALUE := QUANTITY*UNITCOST, IF( Trans_Type = 'RESTOCK', @INV_VALUE := @INV_VALUE+(QUANTITY*UNITCOST), @INV_VALUE := IF( @INVENTORY < 1, @UNITCOST*(@INVENTORY+QUANTITY), @INV_VALUE+((@INV_VALUE/@INVENTORY)*QUANTITY) ) ) ) AS Inv_Value, IF( @NEW = 1, @INVENTORY := QUANTITY, @INVENTORY := @INVENTORY+QUANTITY ) AS Inventory, @UNITCOST := IF( Trans_Type = 'RESTOCK', UNITCOST, IF( @INVENTORY < 1 AND @NEW = 0, @UNITCOST, IF( @INVENTORY < 1 AND @NEW = 1, 0, @INV_VALUE/@INVENTORY ) ) ) AS UnitCost, PRICE FROM ( ( SELECT invoices.POSTDATE, IF(products.PARENT_PRODUCT_ID = 0, invoice_items.PRODUCT_ID, products.PARENT_PRODUCT_ID) AS PRODUCT_ID, invoice_items.QUANTITY*(-1) AS QUANTITY, 'SALE' AS Trans_Type, 0 AS UNITCOST, invoice_items.PRICE FROM invoice_items Inner Join products ON products.PRODUCT_ID = invoice_items.PRODUCT_ID Inner Join invoices ON invoices.INVOICE_ID = invoice_items.INVOICE_ID WHERE invoices.VOIDEDINVOICE = 0 AND (products.PRODUCT_TYPE = 'ICHILD' OR products.PRODUCT_TYPE = 'ISINGLE') AND if(@ACCOUNT='' and @SUBACCOUNT='',true,if(@SUBACCOUNT='', products.REV_CATEGORY=@ACCOUNT, products.REV_CATEGORY=@ACCOUNT and products.REVENUE_SUBCATEGORY=@SUBACCOUNT)) ) UNION ALL ( SELECT restock.POSTDATE, IF(products.PARENT_PRODUCT_ID=0, restock.PRODUCT_ID, products.PARENT_PRODUCT_ID) AS PRODUCT_ID, restock.QUANTITY, restock.RTYPE AS Trans_Type, restock.UNITCOST, 0 AS PRICE FROM restock Inner Join products ON products.PRODUCT_ID = restock.PRODUCT_ID WHERE (products.PRODUCT_TYPE = 'ICHILD' OR products.PRODUCT_TYPE = 'ISINGLE') AND if(@ACCOUNT='' and @SUBACCOUNT='',true,if(@SUBACCOUNT='', products.REV_CATEGORY=@ACCOUNT, products.REV_CATEGORY=@ACCOUNT and products.REVENUE_SUBCATEGORY=@SUBACCOUNT)) ) UNION ALL ( SELECT dates.TransDate AS POSTDATE, productlist.PRODUCT_ID, 0 AS QUANTITY, 'DATE' AS Trans_Type, 0 AS UNITCOST, 0 AS PRICE FROM ( SELECT TransDate FROM ( SELECT adddate( (SELECT DATE_FORMAT(POSTDATE,'%Y-%m-%d') AS POSTDATE FROM invoices LIMIT 1) , @row) TransDate, @row:=@row+1 i FROM daily_numbers, (SELECT @row := 0) row, (SELECT @PROD_ID := 0, @NEW := 0, @INV_VALUE := 0, @INVENTORY := 0, @UNITCOST := 0) vars ) datesinit WHERE TransDate <= CURDATE() ) dates cross join ( SELECT IF(products.PARENT_PRODUCT_ID = 0, products.PRODUCT_ID, products.PARENT_PRODUCT_ID) AS PRODUCT_ID FROM products WHERE (products.PRODUCT_TYPE = 'ICHILD' OR products.PRODUCT_TYPE = 'ISINGLE') AND if(@ACCOUNT='' and @SUBACCOUNT='',true,if(@SUBACCOUNT='', products.REV_CATEGORY=@ACCOUNT, products.REV_CATEGORY=@ACCOUNT and products.REVENUE_SUBCATEGORY=@SUBACCOUNT)) GROUP BY IF(products.PARENT_PRODUCT_ID = 0, products.PRODUCT_ID, products.PARENT_PRODUCT_ID) ORDER BY products.PRODUCT_ID ASC ) productlist ORDER BY productlist.PRODUCT_ID ASC, dates.TransDate ASC ) ORDER BY PRODUCT_ID ASC, POSTDATE ASC ) trans ) fulltrans ORDER BY PRODUCT_ID ASC, POSTDATE DESC ) reversedtrans GROUP BY CONCAT(DATE_FORMAT(POSTDATE,'%Y-%m-%d'),PRODUCT_ID) ) groupedtrans ORDER BY PRODUCT_ID ASC, POSTDATE ASC ) countvalue INNER JOIN products ON countvalue.PRODUCT_ID = products.PRODUCT_ID WHERE DATE(countvalue.POSTDATE) >= @START AND DATE(countvalue.POSTDATE) <= @END GROUP BY countvalue.PRODUCT_ID ) output WHERE Quantity_Sold > 0 AND Avg_Inventory > 0 ORDER BY Account ASC, ROUND(Inv_Turns,1) DESC ) v UNION (SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ) UNION (SELECT 'TOTAL', NULL, NULL, CONVERT( ROUND(@QSOLD/@QAVGINV,1), decimal(10,1) ), CONVERT(@QSOLD,decimal(10,1)), @QAVGINV, NULL, CONVERT( ROUND(@COGSSOLD/@COGSAVGINV,1), decimal(10,1) ), CONVERT(@COGSSOLD,decimal(10,2)), CONVERT(@COGSAVGINV,decimal(10,2)), NULL, CONVERT(@RSOLD,decimal(10,2)), CONVERT(@RMARGIN,decimal(10,2)), NULL, CONVERT( ROUND(@RMARGIN/@RSOLD,1), decimal(10,1) ) ) /*allow-multigym*/