{
  "facility_sql": "SELECT /*FACILITY_TAG*/\r\n  CONCAT(p.REV_CATEGORY, IF(LENGTH(p.REVENUE_SUBCATEGORY) > 0, CONCAT(':', p.REVENUE_SUBCATEGORY), '')) AS Account,\r\n  v.vendor_name AS Vendor,\r\n  p.description AS Description,\r\n  SUM(ii.QUANTITY) AS Qty,\r\n  ROUND(SUM(ii.QUANTITY * COALESCE(lr.legacy_unit_cost, 0)), 2) AS Cost,\r\n  ROUND(SUM(ii.PRICE), 2) AS Sales,\r\n  ROUND(\r\n    CASE WHEN SUM(ii.QUANTITY) > 0\r\n      THEN SUM(ii.QUANTITY * COALESCE(lr.legacy_unit_cost, 0)) / SUM(ii.QUANTITY)\r\n      ELSE 0\r\n    END, 2\r\n  ) AS Average_Cost,\r\n  ROUND(\r\n    CASE WHEN SUM(ii.QUANTITY) > 0\r\n      THEN SUM(ii.PRICE) / SUM(ii.QUANTITY)\r\n      ELSE 0\r\n    END, 2\r\n  ) AS Average_Price,\r\n  ROUND(\r\n    CASE WHEN SUM(ii.PRICE) > 0\r\n      THEN ((SUM(ii.PRICE) - SUM(ii.QUANTITY * COALESCE(lr.legacy_unit_cost, 0))) / SUM(ii.PRICE)) * 100\r\n      ELSE 0\r\n    END, 2\r\n  ) AS Percentage,\r\n  ROUND(SUM(ii.PRICE) - SUM(ii.QUANTITY * COALESCE(lr.legacy_unit_cost, 0)), 2) AS Margin\r\nFROM invoice_items ii\r\nJOIN invoices i ON ii.invoice_id = i.invoice_id\r\nJOIN payments pay ON i.payment_id = pay.payment_id\r\nJOIN products p ON ii.product_id = p.product_id\r\nJOIN vendors v ON p.vendor_id = v.vendor_id\r\nLEFT JOIN (\r\n  SELECT r.product_id, r.legacy_unit_cost\r\n  FROM restock r\r\n  JOIN (\r\n    SELECT product_id, MAX(postdate) AS max_postdate\r\n    FROM restock\r\n    WHERE quantity > 0\r\n      AND rtype IN ('RESTOCK','TRANSFER_IN')\r\n      AND postdate <= '/*END_DATE*/'\r\n    GROUP BY product_id\r\n  ) rr ON r.product_id = rr.product_id AND r.postdate = rr.max_postdate\r\n) lr ON lr.product_id = p.product_id\r\nWHERE i.voidedinvoice = 0\r\n  AND pay.postdate BETWEEN '/*START_DATE*/' AND '/*END_DATE*/'\r\n  AND p.product_type IN ('ISINGLE','ICHILD')\r\n  AND NOT p.INACTIVE\r\nGROUP BY p.REV_CATEGORY, p.REVENUE_SUBCATEGORY, v.vendor_name, p.description\r\nORDER BY Qty DESC;",
  "merge_sql": "",
  "json": "{\r\n  \"facility_column_tag\": \"/*FACILITY_TAG*/\",\r\n  \"wants_start_date\": {\r\n    \"tag\": \"/*START_DATE*/\"\r\n  },\r\n  \"wants_end_date\": {\r\n    \"tag\": \"/*END_DATE*/\"\r\n  }\r\n}",
  "name": "Inventory - Margins"
}