CREATE PROCEDURE dbo.GiftTokenGetStatus @AGiftTokenNumber VARCHAR(50), @AStatus INT OUTPUT, @AAmount MONEY OUTPUT AS
SET NOCOUNT ON
/*
  Created by Kent Johansen september 2012
  Returns the status for GiftToken
  Possible values: 
  AStatus = 0=Unknown, 1=Issue, 2=Redeem, 3=Cancel, 4=Expire, 5=Redeem global 6=Redeem partly
  AAmount = beløb på gavekort
*/

SET @AStatus = 0
SET @AAmount = 0

DECLARE @tmpStatus INT
SET @tmpStatus = 0

DECLARE @AllowRedeemExpired BIT
SELECT @AllowRedeemExpired = CONVERT(BIT, KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE Searchkey = 'GiftCertificateAllowRedeemOfExpired'
SET @AllowRedeemExpired = ISNULL(@AllowRedeemExpired, 0)

SELECT @tmpStatus = 4 -- Expired
FROM GiftToken GT WITH (NOLOCK)
INNER JOIN GiftTokenExpire gte WITH (NOLOCK) ON gte.FK_GiftTokenId = GT.Id
LEFT OUTER JOIN GiftTokenGiftTokenTypeXTab gtXTab  WITH (NOLOCK) ON gtXTab.FK_GiftTokenId = GT.Id
LEFT OUTER JOIN GiftTokenType gtt  WITH (NOLOCK) ON gtt.Id = gtXTab.FK_GiftTokenTypeId
WHERE GT.GiftTokenNumber = @AGiftTokenNumber
AND gte.ExpireDate < CONVERT(DATETIME, (SELECT SystemDate FROM System  WITH (NOLOCK) WHERE Number = 1) + ' ' + CONVERT(CHAR(8), CURRENT_TIMESTAMP, 108))

SELECT TOP 1 @AStatus = GT.Status, @AAmount = Transactions.PriceOverwrite
FROM GiftToken GT WITH (NOLOCK)
LEFT OUTER JOIN GiftTokenGiftTokenTypeXTab gtXTab  WITH (NOLOCK) ON gtXTab.FK_GiftTokenId = GT.Id
LEFT OUTER JOIN GiftTokenType gtt  WITH (NOLOCK) ON gtt.Id = gtXTab.FK_GiftTokenTypeId
INNER JOIN GiftTokenTransactionsXTab WITH (NOLOCK) ON GiftTokenTransactionsXTab.FK_GiftTokenId = GT.Id
INNER JOIN Transactions WITH (NOLOCK) ON Transactions.GlobalTransactionNumber = GiftTokenTransactionsXTab.FK_OriginalGlobalTransactionNumber
  AND Transactions.ShowOnBillFlag = 1
WHERE GT.GiftTokenNumber = @AGiftTokenNumber
GROUP BY GT.Status, Transactions.PriceOverwrite
ORDER BY GT.Status DESC

IF @tmpStatus = 4 
BEGIN
  IF @AStatus = 1
  BEGIN
    IF @AllowRedeemExpired = 0
    BEGIN
      SET @AStatus = 4
      SET @AAmount = 0
    END
  END
  ELSE IF @AStatus = 2 OR @AStatus = 3 -- redeemed or cencelled
    SET @AAmount = 0
END
