CREATE FUNCTION dbo.GiftTokenStatus (@AGiftTokenNumber VARCHAR(50), @CurTimeStamp DATETIME) RETURNS INT AS
BEGIN
/*
  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
  Changes:
    KJ 20130903: Support for the very, very, very old sql server 2000.
*/

DECLARE @Status INT
SET @Status = 0

DECLARE @tmpStatus INT
SET @tmpStatus = 0

DECLARE @tmpAmount MONEY

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), @CurTimeStamp, 108))

SELECT TOP 1 @Status = GT.Status
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 @Status = 1
  BEGIN
    IF @AllowRedeemExpired = 0
      SET @Status = 4
  END
END  
ELSE IF @Status = 6
BEGIN
--  EXEC GiftTokenGetBalance @AGiftTokenNumber, @tmpAmount OUTPUT
  SELECT @tmpAmount = SUM(Transactions.PriceOverwrite*Pieces)*-1
  FROM GiftToken WITH (NOLOCK)
  INNER JOIN GiftTokenTransactionsXTab WITH (NOLOCK) ON GiftTokenTransactionsXTab.FK_GiftTokenId = GiftToken.Id
    AND GiftToken.GiftTokenNumber = @AGiftTokenNumber
  INNER JOIN Transactions WITH (NOLOCK) ON Transactions.OriginalGlobalTransactionNumber = GiftTokenTransactionsXTab.FK_OriginalGlobalTransactionNumber
    AND Transactions.ShowOnBillFlag = 1
  IF @tmpAmount = 0
    SET @Status = 2
END  

  RETURN @Status
END