CREATE PROCEDURE [dbo].[PostGiftCertificate] @AWareNumber INT, @AOriginalGlobalTransactionNumber INT, @AGiftTokenId VARCHAR(100), @AGiftTokenPayment INT, @AHotelCustomerNumber INT, @AAmount MONEY, @ADoNotGetNewGiftTokenId BIT = 0 AS
/*
  Created by Kent Johansen January 2009
  Note: 
  Changes:
    KJ 20180619: Bug: Fix for "Can not insert the value NULL into GiftTokenNumber". Case #CR148254
    KJ 20160221: Ext: Added @ADoNotGetNewGiftTokenId for GiftToken Import. Req #2918
    KJ 20130104: Ext: Added partly redeem. Req #933
    KJ 20120920: Ext: Added Global gifttoken system. Req #1305
    KJ 20101222: Bug: Fix for "Gift certificate number not correct in note field on transactions"
    KJ 20100104: Bug: Fix for "Issue moved to payments, Cancel gift Cert. not correct"
    KJ 20091230: Bug: Fix for "GiftCertificateMonth sometimes NULL"
    KJ 20091216: Bug: Fix for "GiftCertificate not cancelled correct"

*/  

DECLARE @GiftCertificateIssue INT
DECLARE @GiftCertificateRedeem INT
DECLARE @Ident INT
DECLARE @ExpireDate DATETIME
DECLARE @CurrentDateStr CHAR(8)
DECLARE @PostForDatetime DATETIME
DECLARE @GiftCertificateMonth INT
DECLARE @GiftTokenNumber INT
DECLARE @GiftTokenTypeIdWare INT
DECLARE @GiftTokenTypeIdPayment INT
DECLARE @tmpAmount MONEY

SELECT @GiftCertificateMonth = CONVERT(INT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'GiftCertificateMonth'
SET @GiftCertificateMonth = ISNULL(@GiftCertificateMonth, 12)
SELECT @CurrentDateStr = SystemDate FROM System WITH (NOLOCK) WHERE Number = 1
SET @PostForDatetime = CONVERT(DATETIME, @CurrentDateStr + ' ' + CONVERT(CHAR(8), CURRENT_TIMESTAMP, 108))

SELECT @GiftCertificateIssue = COUNT(*), @GiftTokenTypeIdWare = FK_GiftTokenTypeId
FROM Ware WITH (NOLOCK) 
WHERE WareNumber = @AWareNumber 
AND Payment = 17
AND ISNULL(MarkAsDeleted, 0) = 0
GROUP BY FK_GiftTokenTypeId

SELECT @GiftCertificateRedeem = COUNT(*), @GiftTokenTypeIdPayment = FK_GiftTokenTypeId 
FROM Ware WITH (NOLOCK) 
WHERE WareNumber = @AWareNumber 
AND Payment = 15
AND ISNULL(MarkAsDeleted, 0) = 0
GROUP BY FK_GiftTokenTypeId

IF ISNULL(@GiftCertificateIssue, 0) > 0
BEGIN
  -- Calculate the @AGiftTokenId
  IF ISNULL(@ADoNotGetNewGiftTokenId, 0) <> 1
    EXEC GiftTokenGenerateGiftTokenNumber @GiftTokenTypeIdWare, @AGiftTokenId OUTPUT, @AHotelCustomerNumber

  INSERT INTO GiftToken (GiftTokenNumber, Status) VALUES (@AGiftTokenId, 1) -- Status: 1 = Issue
  SET @Ident = @@Identity
  INSERT INTO GiftTokenTransactionsXTab (FK_OriginalGlobalTransactionNumber, FK_GiftTokenId) VALUES (@AOriginalGlobalTransactionNumber, @Ident)
  INSERT INTO GiftTokenExpire(FK_GiftTokenId, ExpireDate) VALUES (@Ident, DATEADD(MONTH, @GiftCertificateMonth, @PostForDatetime))
  IF ISNULL(@GiftTokenTypeIdWare, -1) > 0
  BEGIN
    INSERT INTO GiftTokenGiftTokenTypeXTab VALUES (@GiftTokenTypeIdWare, @Ident)
    UPDATE Transactions SET Note = @AGiftTokenId WHERE GlobaltransactionNumber = @AOriginalGlobalTransactionNumber 
  END
END
ELSE IF ISNULL(@GiftCertificateRedeem, 0) > 0
BEGIN
  IF @AGiftTokenPayment = 16 -- Cancel gift certificate
    INSERT INTO GiftToken (GiftTokenNumber, Status) VALUES (@AGiftTokenId, 3) -- Status: 3 = Cancel
  ELSE
  BEGIN
    EXEC GiftTokenGetBalance @AGiftTokenId, @tmpAmount OUTPUT
    
    IF @AAmount < @tmpAmount
      INSERT INTO GiftToken (GiftTokenNumber, Status) VALUES (@AGiftTokenId, 6) -- Status: 6 = Partly redeem
    ELSE
      INSERT INTO GiftToken (GiftTokenNumber, Status) VALUES (@AGiftTokenId, 2) -- Status: 2 = Redeem
  END

  SET @Ident = @@Identity
  INSERT INTO GiftTokenTransactionsXTab (FK_OriginalGlobalTransactionNumber, FK_GiftTokenId) VALUES (@AOriginalGlobalTransactionNumber, @Ident)
  
  IF ISNULL(@GiftTokenTypeIdPayment, -1) > 0
    INSERT INTO GiftTokenGiftTokenTypeXTab VALUES (@GiftTokenTypeIdPayment, @Ident)
END