{
  "facility_sql": "(SELECT /*FACILITY_TAG*/ top.* FROM\r\n(SELECT \r\n        ACCOUNT,\r\n        DESCRIPTION,\r\n        COLOR_DESC,\r\n        SIZE_DESC,\r\n        QTY,\r\n        Convert(LAST_UNIT_COST,decimal(10,2)) LAST_UNIT_COGS,\r\n        Convert(AVG_UNIT_PRICE,decimal(10,2)) AVG_UNIT_PRICE,\r\n        Convert(NET_COST,decimal(10,2)) NET_COGS,\r\n        Convert(NET_PRICE,decimal(10,2)) NET_PRICE,\r\n        MARGIN AS 'MARGIN_PERCENT',\r\n        Convert(NET_MARGIN,decimal(10,2)) NET_MARGIN\r\nFROM\r\n        (SELECT\r\n        CONCAT(products.REV_CATEGORY,':',products.REVENUE_SUBCATEGORY) ACCOUNT,\r\n        products.DESCRIPTION,\r\n        products.COLOR_DESC,\r\n        products.SIZE_DESC,\r\n        sum(invoice_items.QUANTITY) QTY,\r\n        ROUND(cost.UNITCOST,2) LAST_UNIT_COST,\r\n        ROUND((sum(invoice_items.PRICE)/sum(invoice_items.QUANTITY)),2) AVG_UNIT_PRICE,\r\n        (sum(invoice_items.QUANTITY)*cost.UNITCOST) NET_COST,\r\n        sum(invoice_items.PRICE) NET_PRICE,\r\n        CONCAT(ROUND(((1-((sum(invoice_items.QUANTITY)*cost.UNITCOST)/sum(invoice_items.PRICE)))*100),1),\"%\") AS 'MARGIN',\r\n        (sum(invoice_items.PRICE)-(sum(invoice_items.QUANTITY)*cost.UNITCOST)) NET_MARGIN\r\n        FROM\r\n        invoice_items\r\n        INNER JOIN invoices ON invoice_items.INVOICE_ID = invoices.INVOICE_ID\r\n        INNER JOIN payments ON payments.PAYMENT_ID = invoices.PAYMENT_ID\r\n        INNER JOIN products ON products.PRODUCT_ID = invoice_items.PRODUCT_ID\r\n        LEFT JOIN \r\n        (SELECT * FROM (SELECT        restock.PRODUCT_ID,        restock.UNITCOST FROM        restock        WHERE        RTYPE = 'RESTOCK'        AND DATE(restock.POSTDATE) <=  '/*END_DATE*/' ORDER BY restock.PRODUCT_ID ASC, restock.POSTDATE DESC) r1 GROUP BY r1.PRODUCT_ID) as cost\r\n        ON invoice_items.PRODUCT_ID = cost.PRODUCT_ID\r\n        WHERE\r\n        payments.DECLINED = 0 AND\r\n        payments.VOIDEDPAYMENT = 0 AND\r\n        products.PRODUCT_TYPE like \"I%\" AND\r\n        DATE(payments.POSTDATE) >=  '/*START_DATE*/' AND\r\n        DATE(payments.POSTDATE) <=  '/*END_DATE*/'\r\n        GROUP BY\r\n        invoice_items.PRODUCT_ID\r\n        ORDER BY\r\n        invoice_items.PRODUCT_ID) a\r\nORDER BY\r\nACCOUNT ASC,\r\nDESCRIPTION ASC)top)\r\n\r\n\r\nUNION ALL\r\n\r\n\r\n(SELECT /*FACILITY_TAG*/ \r\nACCOUNT,\r\nDESCRIPTION,\r\nCOLOR_DESC,\r\nSIZE_DESC,\r\nQTY,\r\n'' LAST_UNIT_COGS,\r\n'' AVG_UNIT_PRICE,\r\nConvert(NET_COST,decimal(10,2)) NET_COGS,\r\nConvert(NET_PRICE,decimal(10,2)) NET_PRICE,\r\nCONCAT(ROUND(((1-(NET_COST/NET_PRICE))*100),1),\"%\") AS 'MARGIN_%',\r\nConvert((NET_PRICE-NET_COST),decimal(10,2)) NET_MARGIN\r\nFROM\r\n        (SELECT \r\n        '' ACCOUNT,\r\n        '' DESCRIPTION,\r\n        '' COLOR_DESC,\r\n        'TOTAL' SIZE_DESC,\r\n        sum(QTY) QTY,\r\n        '' LAST_UNIT_COST,\r\n        '' AVG_UNIT_PRICE,\r\n        sum(NET_COST) NET_COST,\r\n        sum(NET_PRICE) NET_PRICE,\r\n        CONCAT(ROUND(((1-(sum(NET_COST)/sum(NET_PRICE)))*100),1),\"%\") AS 'MARGIN_PERCENT',\r\n        '' NET_MARGIN\r\n        FROM\r\n                (SELECT *\r\n                FROM\r\n                        (SELECT\r\n                        CONCAT(products.REV_CATEGORY,':',products.REVENUE_SUBCATEGORY) ACCOUNT,\r\n                        products.DESCRIPTION,\r\n                        products.COLOR_DESC,\r\n                        products.SIZE_DESC,\r\n                        sum(invoice_items.QUANTITY) QTY,\r\n                        ROUND(cost.UNITCOST,2) LAST_UNIT_COST,\r\n                        ROUND((sum(invoice_items.PRICE)/sum(invoice_items.QUANTITY)),2) AVG_UNIT_PRICE,\r\n                        (sum(invoice_items.QUANTITY)*cost.UNITCOST) NET_COST,\r\n                        sum(invoice_items.PRICE) NET_PRICE,\r\n                        CONCAT(ROUND(((1-((sum(invoice_items.QUANTITY)*cost.UNITCOST)/sum(invoice_items.PRICE)))*100),1),\"%\") AS 'MARGIN_%',\r\n                        (sum(invoice_items.PRICE)-(sum(invoice_items.QUANTITY)*cost.UNITCOST)) NET_MARGIN\r\n                        FROM\r\n                        invoice_items\r\n                        INNER JOIN invoices ON invoice_items.INVOICE_ID = invoices.INVOICE_ID\r\n                        INNER JOIN payments ON payments.PAYMENT_ID = invoices.PAYMENT_ID\r\n                        INNER JOIN products ON products.PRODUCT_ID = invoice_items.PRODUCT_ID\r\n                        LEFT JOIN \r\n                        (SELECT * FROM (SELECT        restock.PRODUCT_ID,        restock.UNITCOST FROM        restock        WHERE        RTYPE = 'RESTOCK'        AND DATE(restock.POSTDATE) <=  '/*END_DATE*/' ORDER BY restock.PRODUCT_ID ASC, restock.POSTDATE DESC) r1 GROUP BY r1.PRODUCT_ID) as cost\r\n                        ON invoice_items.PRODUCT_ID = cost.PRODUCT_ID\r\n                        WHERE\r\n                        payments.DECLINED = 0 AND\r\n                        payments.VOIDEDPAYMENT = 0 AND\r\n                        products.PRODUCT_TYPE like \"I%\" AND\r\n                        DATE(payments.POSTDATE) >=  '/*START_DATE*/' AND\r\n                        DATE(payments.POSTDATE) <=  '/*END_DATE*/'\r\n                        GROUP BY\r\n                        invoice_items.PRODUCT_ID\r\n                        ORDER BY\r\n                        invoice_items.PRODUCT_ID) a\r\n                ORDER BY\r\n                ACCOUNT ASC,\r\n                DESCRIPTION ASC) b) b2)",
  "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": "COGS"
}