-- Version 2018-09-05 Accomodates for multi-gym transfers
SET @STARTDATE = '{STARTDATE}', @ENDDATE = '{ENDDATE}';
SET @GIFTCARD_PROD_ID = (SELECT value FROM settings WHERE `name` = 'GiftCardProductId');

(
SELECT 
	Sold.Date AS 'Date',
	Sold.Card AS 'Gift Card',
	Convert(Sold.Amount,decimal(10,2)) AS 'Sold Amount',
	Convert(Redeemed.Amount,decimal(10,2)) AS 'Redeemed Amount',
	NULL as '_',
	IF(Convert(Sold.Amount-IFNULL(Redeemed.Amount,0),decimal(10,2))=0,NULL,Convert(Sold.Amount-IFNULL(Redeemed.Amount,0),decimal(10,2))) AS 'UnRedeemed $',
	null AS 'UnRedeemed %'
FROM (
	SELECT
		null AS 'Date',
		'Redeemed' AS 'Type',
		(Sum(invoice_items.PRICE)*-1) AS 'Amount',
		invoice_items.EXTRAINFO AS 'Card'
	FROM
		invoices
		Inner Join invoice_items ON invoice_items.INVOICE_ID = invoices.INVOICE_ID
		Inner Join payments ON payments.PAYMENT_ID = invoices.PAYMENT_ID
	WHERE
		invoice_items.PRODUCT_ID =  @GIFTCARD_PROD_ID AND
		(invoice_items.DESCRIPTION LIKE  '%Redeem%' OR invoice_items.DESCRIPTION = 'Gift Card Debit') AND
		payments.VOIDEDPAYMENT =  '0'
	GROUP BY
		invoice_items.EXTRAINFO
) AS Redeemed
	RIGHT JOIN (
		SELECT
			payments.POSTDATE 'Date',
			'Sell' AS 'Type',
			Sum(invoice_items.PRICE) AS 'Amount',
			invoice_items.EXTRAINFO AS 'Card'
		FROM
			invoices
			Inner Join invoice_items ON invoice_items.INVOICE_ID = invoices.INVOICE_ID
			Inner Join payments ON payments.PAYMENT_ID = invoices.PAYMENT_ID
		WHERE
			invoice_items.PRODUCT_ID =  @GIFTCARD_PROD_ID AND
			invoice_items.DESCRIPTION NOT LIKE '%Redeem%' AND
			invoice_items.DESCRIPTION <> 'Gift Card Debit' AND
			payments.VOIDEDPAYMENT =  '0' AND
			DATE(payments.POSTDATE) >=  @STARTDATE AND
			DATE(payments.POSTDATE) <=  @ENDDATE
		GROUP BY
			invoice_items.EXTRAINFO
		ORDER BY
			payments.POSTDATE ASC
	) AS Sold ON Redeemed.Card = Sold.Card
)

UNION

(
SELECT 
	null AS 'Date',
	null AS 'Gift Card',
	Convert(SUM(Sold.Amount),decimal(10,2)) AS 'Sold Amount',
	Convert(SUM(Redeemed.Amount),decimal(10,2)) AS 'Redeemed Amount',
	NULL as '_',
	Convert(SUM(Sold.Amount)-SUM(Redeemed.Amount),decimal(10,2)) AS 'UnRedeemed $',
	CONCAT(
		Convert(
			(1-(SUM(Redeemed.Amount)/Sum(Sold.Amount)))*100,
			decimal(10,1)
		),
		'%'
	) AS 'Unredeemed %'
FROM (
	SELECT
		null AS 'Date',
		'Redeemed' AS 'Type',
		(Sum(invoice_items.PRICE)*-1) AS 'Amount',
		invoice_items.EXTRAINFO AS 'Card'
	FROM
		invoices
		Inner Join invoice_items ON invoice_items.INVOICE_ID = invoices.INVOICE_ID
		Inner Join payments ON payments.PAYMENT_ID = invoices.PAYMENT_ID
	WHERE
		invoice_items.PRODUCT_ID =  @GIFTCARD_PROD_ID AND
		(invoice_items.DESCRIPTION LIKE  '%Redeem%' OR invoice_items.DESCRIPTION = 'Gift Card Debit') AND
		payments.VOIDEDPAYMENT =  '0'
	GROUP BY
		invoice_items.EXTRAINFO) AS Redeemed
	RIGHT JOIN (
		SELECT
			payments.POSTDATE 'Date',
			'Sell' AS 'Type',
			Sum(invoice_items.PRICE) AS 'Amount',
			invoice_items.EXTRAINFO AS 'Card'
		FROM
			invoices
			Inner Join invoice_items ON invoice_items.INVOICE_ID = invoices.INVOICE_ID
			Inner Join payments ON payments.PAYMENT_ID = invoices.PAYMENT_ID
		WHERE
			invoice_items.PRODUCT_ID =  @GIFTCARD_PROD_ID AND
			invoice_items.DESCRIPTION NOT LIKE '%Redeem%' AND
			invoice_items.DESCRIPTION <> 'Gift Card Debit' AND
			payments.VOIDEDPAYMENT =  '0' AND
			DATE(payments.POSTDATE) >=  @STARTDATE AND
			DATE(payments.POSTDATE) <=  @ENDDATE
		GROUP BY
			invoice_items.EXTRAINFO
		ORDER BY
			payments.POSTDATE ASC
	) AS Sold ON Redeemed.Card = Sold.Card
)
/*allow-multigym*/