{
  "facility_sql": "SET @STARTDATE = '/*START_DATE*/', @ENDDATE = '/*END_DATE*/';\r\nSET @GIFTCARD_PROD_ID = (SELECT value FROM settings WHERE `name` = 'GiftCardProductId');\r\n\r\nSELECT /*FACILITY_TAG*/ sum(ifnull(`Sold Amount`, 0)) AS SoldAmount, sum(ifnull(`Redeemed Amount`, 0)) AS RedeemedAmount, sum(ifnull(`UnRedeemed $`, 0)) AS UnRedeemedAmount\r\n\r\nFROM (\r\n\r\nSELECT \r\n\tSold.Date AS 'Date',\r\n\tSold.Card AS 'Gift Card',\r\n\tConvert(Sold.Amount,decimal(10,2)) AS 'Sold Amount',\r\n\tConvert(Redeemed.Amount,decimal(10,2)) AS 'Redeemed Amount',\r\n\tNULL as '_',\r\n\tIF(Convert(Sold.Amount-IFNULL(Redeemed.Amount,0),decimal(10,2))=0,NULL,Convert(Sold.Amount-IFNULL(Redeemed.Amount,0),decimal(10,2))) AS 'UnRedeemed $',\r\n\tnull AS 'UnRedeemed %'\r\nFROM (\r\n\tSELECT\r\n\t\tnull AS 'Date',\r\n\t\t'Redeemed' AS 'Type',\r\n\t\t(Sum(invoice_items.PRICE)*-1) AS 'Amount',\r\n\t\tinvoice_items.EXTRAINFO AS 'Card'\r\n\tFROM\r\n\t\tinvoices\r\n\t\tInner Join invoice_items ON invoice_items.INVOICE_ID = invoices.INVOICE_ID\r\n\t\tInner Join payments ON payments.PAYMENT_ID = invoices.PAYMENT_ID\r\n\tWHERE\r\n\t\tinvoice_items.PRODUCT_ID =  @GIFTCARD_PROD_ID AND\r\n\t\t(invoice_items.DESCRIPTION LIKE  '%Redeem%' OR invoice_items.DESCRIPTION = 'Gift Card Debit') AND\r\n\t\tpayments.VOIDEDPAYMENT =  '0'\r\n\tGROUP BY\r\n\t\tinvoice_items.EXTRAINFO\r\n) AS Redeemed\r\n\tRIGHT JOIN (\r\n\t\tSELECT\r\n\t\t\tpayments.POSTDATE 'Date',\r\n\t\t\t'Sell' AS 'Type',\r\n\t\t\tSum(invoice_items.PRICE) AS 'Amount',\r\n\t\t\tinvoice_items.EXTRAINFO AS 'Card'\r\n\t\tFROM\r\n\t\t\tinvoices\r\n\t\t\tInner Join invoice_items ON invoice_items.INVOICE_ID = invoices.INVOICE_ID\r\n\t\t\tInner Join payments ON payments.PAYMENT_ID = invoices.PAYMENT_ID\r\n\t\tWHERE\r\n\t\t\tinvoice_items.PRODUCT_ID =  @GIFTCARD_PROD_ID AND\r\n\t\t\tinvoice_items.DESCRIPTION NOT LIKE '%Redeem%' AND\r\n\t\t\tinvoice_items.DESCRIPTION <> 'Gift Card Debit' AND\r\n\t\t\tpayments.VOIDEDPAYMENT =  '0' AND\r\n\t\t\tDATE(payments.POSTDATE) >=  @STARTDATE AND\r\n\t\t\tDATE(payments.POSTDATE) <=  @ENDDATE\r\n\t\tGROUP BY\r\n\t\t\tinvoice_items.EXTRAINFO\r\n\t\tORDER BY\r\n\t\t\tpayments.POSTDATE ASC\r\n\t) AS Sold ON Redeemed.Card = Sold.Card\r\n) dt",
  "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": "Gift Cards: Sold, Redeemed, and Unredeemed"
}