CREATE PROCEDURE dbo.OnlineCreateGiftToken @AAmount MONEY, @AHotelCustomerNumber INT,
  @AFirstName VARCHAR(50), @ALastName VARCHAR(50), @AAddress1 VARCHAR(50), @AAddress2 VARCHAR(50), @AAddress3 VARCHAR(50),
  @ACountryCode VARCHAR(3), @AZipCode VARCHAR(10), @ACity VARCHAR(40), @AEmail VARCHAR(96), @AOrderId INT,
  @AGiftTokenNumber VARCHAR(100) OUTPUT, @AExpireDate DATETIME OUTPUT, @AGiftTokenType INT = -1, @AGlobalGiftTokenSystem BIT = 0, 
  @ALocalGiftTokenNumber VARCHAR(100) = '', @ACreditCardFee MONEY = 0, @AOnlineCCType VARCHAR(100) = '', @AFolioNumber INT = -1 OUTPUT, @ADiscountAmount MONEY = 0 AS
SET NOCOUNT ON

/*
  Created by Kent Johansen june 2012
  Changes:
    KJ 20221229: Ext: Discount on issue of Gift certificate. Req #4151
    KJ 20191120: Bug: Fix for "Post is made on wrong folionumber if bonus card". Case #CR174101
    KJ 20170830: Bug: Fix for "Prevent use of same GTN. Do not get multiple numbers at a time because of timeing issues".
    KJ 20191101: Bug: Fix for "Error calling postering if CashDesk is enabled". Case #CR115115
    KJ 20190928: Bug: Fix for "Error calling postering if CashDesk is enabled". Case #CR113714
    KJ 20160513: Ext: Added folioNumber to output. This should be added to gift certificate when it is printed. Used for online redeem.
    KJ 20160225: Ext: Added credit card fee Req #2756
    KJ 20141211: Bug: Fix for "Cannot insert the value NULL into column 'FK_CashBox', table 'SWE_AngaVallen.dbo.TransactionsRelation'; column does not allow nulls.".
    KJ 20141208: Bug: Fix for "Cannot insert the value NULL into column 'FK_CashBox', table 'SWE_AngaVallen.dbo.TransactionsRelation'; column does not allow nulls.".
    nb20140721 FolioIdentity discontinued
    20130628: Bug: Fix for "Error when multiple wares and only on gift cert. type". Case #CR06
    20120913: Ext: Added Global gifttoken system. Req #1305
    20120625: Ext: Changed AGiftTokenType as default param
  
*/

DECLARE @FK_Department INT
DECLARE @WareNumberPayment INT
DECLARE @DoContinue BIT
SET @DoContinue = 1

IF ISNULL(@ALastName, '') = '' AND ISNULL(@AGlobalGiftTokenSystem, 0) = 0
BEGIN
  SET @DoContinue = 0
  RAISERROR('Error in storedprocedure <<OnlineCreateGiftToken>>: @ALastName is blank.', 15, 1)
END

SELECT @FK_Department = dbo.GetHotelSetupKeyValue('PicassoOnlinePosteringDepartment', @AHotelCustomerNumber)
IF ISNULL(@FK_Department, 0) <= 0
BEGIN
  SET @DoContinue = 0
  RAISERROR('Error in storedprocedure <<OnlineCreateGiftToken>>: @FK_Department not valid.', 15, 1)
END

SELECT @WareNumberPayment = dbo.GetHotelSetupKeyValue ('OnlineInhousePaymentWareNumber', @AHotelCustomerNumber)
IF ISNULL(@WareNumberPayment, 0) <= 0 AND ISNULL(@AGlobalGiftTokenSystem, 0) = 0
BEGIN
  SET @DoContinue = 0
  RAISERROR('Error in storedprocedure <<OnlineCreateGiftToken>>: Online Inhouse Payment Ware <= 0.', 15, 1)
END
  
DECLARE @AddressNumber INT
DECLARE @FolioNumber INT
DECLARE @OnlineUserId INT
DECLARE @WareText VARCHAR(100)
DECLARE @WareTextPayment VARCHAR(100)
DECLARE @WareNumber INT
DECLARE @tmpWareNumber INT
DECLARE @GlobalTransactionNumber INT
DECLARE @TransNrShowOnBill INT
DECLARE @OriginalGlobalTransactionNumber INT
DECLARE @Payment INT
DECLARE @cnt INT
DECLARE @tmpGtt INT
DECLARE @tmpFolioNumber INT
DECLARE @AutoDepartment INT
DECLARE @GiftTokenAmount MONEY
DECLARE @PaymentAmount MONEY
DECLARE @WareTextDiscount VARCHAR(100)
DECLARE @WareNumberDiscount INT

SELECT @cnt = COUNT(*) 
FROM GiftTokenType WITH (NOLOCK)
WHERE Online = 1

IF ISNULL(@cnt, 0) = 0
BEGIN
  SET @DoContinue = 0
  RAISERROR('Error in storedprocedure <<OnlineCreateGiftToken>>: No Online Gift certificate types.', 15, 1)
END

SELECT @tmpGtt = Id 
FROM GiftTokenType WITH (NOLOCK)
WHERE Online = 1
AND Id = @AGiftTokenType

IF @cnt > 1 AND ISNULL(@tmpGtt, 0) <= 0
BEGIN
  SET @DoContinue = 0
  RAISERROR('Error in storedprocedure <<OnlineCreateGiftToken>>: Multiple Gift certificate types and none seleted.', 15, 1)
END

IF ISNULL(@AGiftTokenType, 0) <= 0
BEGIN
  SELECT TOP 1 @AGiftTokenType = Id
  FROM GiftTokenType WITH (NOLOCK)
  WHERE Online = 1
  ORDER BY SortNumber DESC
END

-- Issue Gift certificate allowed on selected hotel?
SELECT @tmpWareNumber = Ware.WareNumber, @WareText = Ware.WareName, @WareNumber = Ware.WareNumber 
FROM Ware WITH (NOLOCK)
INNER JOIN WareDepartmentsXtab WITH (NOLOCK) ON WareDepartmentsXtab.WareNumber = Ware.WareNumber
  AND WareDepartmentsXtab.Type = 1 -- Cashier department
INNER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = WareDepartmentsXtab.FK_Department
  AND ISNULL(Departments.MarkAsDeleted, 0) = 0
INNER JOIN Customer WITH (NOLOCK) ON Customer.HotelNumber = Departments.HotelNumber
  AND Customer.CustomerNumber = @AHotelCustomerNumber
  AND Customer.CustomerTypeNumber = 7
  AND ISNULL(Customer.MarkAsDeleted, 0) = 0
WHERE FK_GiftTokenTypeId = @AGiftTokenType
AND Payment = 17 -- Gift token Issue

IF ISNULL(@tmpWareNumber, 0) <= 0
BEGIN
  SET @DoContinue = 0
  RAISERROR('Error in storedprocedure <<OnlineCreateGiftToken>>: Gift Certificate issue ware not allowed on selected hotel', 15, 1)  
END  
  
-- Payment ware allowed on selected hotel?
IF ISNULL(@AGlobalGiftTokenSystem, 0) = 0
BEGIN
  SELECT @tmpWareNumber = Ware.WareNumber
  FROM Ware WITH (NOLOCK)
  INNER JOIN WareDepartmentsXtab WITH (NOLOCK) ON WareDepartmentsXtab.WareNumber = Ware.WareNumber
    AND WareDepartmentsXtab.Type = 1 -- Cashier department
  INNER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = WareDepartmentsXtab.FK_Department
    AND ISNULL(Departments.MarkAsDeleted, 0) = 0
  INNER JOIN Customer WITH (NOLOCK) ON Customer.HotelNumber = Departments.HotelNumber
    AND Customer.CustomerNumber = @AHotelCustomerNumber
    AND Customer.CustomerTypeNumber = 7
    AND ISNULL(Customer.MarkAsDeleted, 0) = 0
  WHERE Ware.WareNumber = @WareNumberPayment

  IF ISNULL(@tmpWareNumber, 0) <= 0
  BEGIN
    SET @DoContinue = 0
    RAISERROR('Error in storedprocedure <<OnlineCreateGiftToken>>: Online Inhouse Payment ware not allowed on selected hotel', 15, 1)  
  END
END  

IF @DoContinue = 1
BEGIN
  SET @tmpFolioNumber = -1
  IF ISNULL(@AGlobalGiftTokenSystem, 0) = 1
  BEGIN
    SELECT @tmpFolioNumber = FolioNumber FROM FOLIOS WITH (NOLOCK)
    INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = Folios.ReservationAddressNumber
      AND Link = @AHotelCustomerNumber
    WHERE OpenFolio=1 
    AND HotelCustomerNumber = @AHotelCustomerNumber 
    AND ISNULL(Folios.ReferenceNumber, 0) = 0
    
    IF @tmpFolioNumber <= 0
    BEGIN
      SET @AddressNumber = 0
      EXEC UpdateReservationAddress @AHotelCustomerNumber, 7, @AddressNumber OUTPUT -- Reservation address is created here.

      IF @AddressNumber > 0
      BEGIN
        INSERT INTO Folios (GuestNumber, ReservationAddressNumber, OpenFolio, Note, HotelCustomerNumber)
        VALUES (0, @AddressNumber, 1, '', @AHotelCustomerNumber)
        SELECT @FolioNumber = SCOPE_IDENTITY()--@@IDENTITY
      END
    END
    ELSE
      SET @FolioNumber = @tmpFolioNumber
  END
  ELSE
  BEGIN
    EXEC ReservationAddressCreate @AFirstName, @ALastName, @AAddress1, @AAddress2, @AAddress3, @ACountryCode, @AZipCode, @ACity, @AEmail, @AOrderId, @AddressNumber OUTPUT

    IF @AddressNumber > 0
    BEGIN
      INSERT INTO Folios (GuestNumber, ReservationAddressNumber, OpenFolio, Note, HotelCustomerNumber)
      VALUES (0, @AddressNumber, 1, '', @AHotelCustomerNumber)
      SELECT @FolioNumber = SCOPE_IDENTITY()--@@IDENTITY
    END
  END

  -- Poster gavekort
  SELECT @OnlineUserId = Keyvalue FROM HotelSetup WITH (NOLOCK) WHERE Searchkey = 'PicassoOnlinePosteringUserIdNumber'
  IF @OnlineUserId <= 0
    SELECT TOP 1 @OnlineUserId = UserIDNumber FROM Personel WITH (NOLOCK)

  EXEC GetNextNumber 'GLOBALTRANSACTIONIDENTITY', 1, @GlobalTransactionNumber OUTPUT

  DECLARE @UseCashDesk BIT
  DECLARE @CashDesk INT
  SELECT @UseCashDesk = dbo.GetHotelSetupKeyValue ('FiscalPrinterSweden', @AHotelCustomerNumber)
  SET @UseCashDesk = ISNULL(@UseCashDesk,0)

  IF @UseCashDesk = 1
    EXEC GetCashDeskForOnline @AHotelCustomerNumber, @CashDesk OUTPUT
  ELSE
    SET @CashDesk = NULL

  IF ISNULL(@ACreditCardFee, 0) > 0
    SET @GiftTokenAmount = @AAmount - @ACreditCardFee
  ELSE
    SET @GiftTokenAmount = @AAmount
  EXEC Postering 0, 0, 1, 0, 0, 0, @OnlineUserId, @FolioNumber, 0, -1, 0, @FolioNumber, @WareText, -1, @GiftTokenAmount, -1, 0, 0, 0, 0, @FK_Department, @WareNumber, 17, @GlobalTransactionNumber, 0, 0, '', @TransNrShowOnBill OUTPUT, @AHotelCustomerNumber, NULL, 0, 0, NULL, NULL, 0, 0, 0, @OriginalGlobalTransactionNumber OUTPUT, @CashDesk

  SET @AFolioNumber = @FolioNumber

  SELECT @AGiftTokenNumber = Transactions.Note, @AExpireDate = GiftTokenExpire.ExpireDate
  FROM Transactions WITH (NOLOCK)
  INNER JOIN GiftTokenTransactionsXTab WITH (NOLOCK) ON GiftTokenTransactionsXTab.FK_OriginalGlobalTransactionNumber = Transactions.OriginalGlobalTransactionNumber
  INNER JOIN GiftToken WITH (NOLOCK) ON GiftToken.Id = GiftTokenTransactionsXTab.FK_GiftTokenId
  INNER JOIN GiftTokenExpire WITH (NOLOCK) ON GiftTokenExpire.FK_GiftTokenId = GiftToken.Id
  WHERE FolioNumber = @FolioNumber
  AND TransactionNumber = @TransNrShowOnBill
  
  IF ISNULL(@AGlobalGiftTokenSystem, 0) > 0
  BEGIN
    UPDATE GiftToken 
    SET GiftTokenNumber2 = @ALocalGiftTokenNumber 
    WHERE GiftTokenNumber = @AGiftTokenNumber
    AND Status = 1 -- issued
  END

  IF ISNULL(@AGlobalGiftTokenSystem, 0) = 0
  BEGIN
    -- Poster betaling
    IF ISNULL(@ADiscountAmount, 0) > 0
      SET @PaymentAmount = @AAmount - @ADiscountAmount
    ELSE 
      SET @PaymentAmount = @AAmount

    SELECT @WareTextPayment = WareName, @Payment = Payment 
    FROM Ware WITH (NOLOCK)
    WHERE WareNumber = @WareNumberPayment
    AND ISNULL(MarkAsDeleted, 0) = 0

    EXEC GetNextNumber 'GLOBALTRANSACTIONIDENTITY', 1, @GlobalTransactionNumber OUTPUT
    EXEC Postering 0, 0, 1, 0, 0, 0, @OnlineUserId, @FolioNumber, 0, -1, 0, @FolioNumber, @WareTextPayment, 1, @PaymentAmount, -1, 0, 0, 0, 0, @FK_Department, @WareNumberPayment, @Payment, @GlobalTransactionNumber, 0, 0, '', @TransNrShowOnBill OUTPUT, @AHotelCustomerNumber, NULL, 0, 0, NULL, NULL, 0, 0, 0, @OriginalGlobalTransactionNumber OUTPUT, @CashDesk

    -- Poster discount
    IF ISNULL(@ADiscountAmount, 0) > 0
    BEGIN
      SELECT @WareTextDiscount = Ware.WareName, @Payment = Ware.Payment, @WareNumberDiscount = Ware.WareNumber
      FROM Ware WITH (NOLOCK) 
      INNER JOIN WareGroups WITH (NOLOCK) ON WARE.WareGroupNumber = WareGroups.WareGroupNumber
        AND WareGroups.Payment = 5  
      WHERE Ware.Type = 19
      AND Ware.MarkAsDeleted IS NULL

      EXEC GetNextNumber 'GLOBALTRANSACTIONIDENTITY', 1, @GlobalTransactionNumber OUTPUT
      EXEC Postering 0, 1, 0, 0, 0, 0, @OnlineUserId, @FolioNumber, 0, -1, 0, @FolioNumber, @WareTextDiscount, 1, @ADiscountAmount, -1, 0, 0, 0, 0, @FK_Department, @WareNumberDiscount, @Payment, @GlobalTransactionNumber, 0, 0, '', @TransNrShowOnBill OUTPUT, @AHotelCustomerNumber, NULL, 0, 0, NULL, NULL, 0, 0, 0, @OriginalGlobalTransactionNumber OUTPUT, @CashDesk
    END

    IF ISNULL(@ACreditCardFee, 0) > 0
    BEGIN
      SELECT TOP 1 @AutoDepartment = PK_Department 
      FROM Departments WITH (NOLOCK) 
      WHERE AutoCharge = 1 
      AND ((MarkAsDeleted=0) OR (MarkAsDeleted IS NULL))  
      AND (Departments.HotelNumber IN (SELECT HotelNumber FROM Customer WITH (NOLOCK) WHERE CustomerTypeNumber = 7 AND CustomerNumber = @AHotelCustomerNumber))
      ORDER BY SortNumber

      if ISNULL(@AutoDepartment, 0) = 0
      Select TOP 1 @AutoDepartment = PK_Department from Departments WITH (nolock) where ((MarkASDeleted=0) OR (MarkASDeleted is null)) order by SortNumber

      DECLARE @FeeWareNumber INT
      DECLARE @TNrShowOnBill INT
      DECLARE @ccFeeName VARCHAR(100)
  
      SELECT @FeeWareNumber = WareNumber, @ccFeeName = WareName
      FROM Ware WITH(NOLOCK)
      INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
      WHERE Ware.SortNumber = 6
      AND WareGroups.Payment = 4 -- fees
  
      EXEC OnlinePosteringExt @FeeWareNumber, @AHotelCustomerNumber, @FolioNumber, @ACreditCardFee, 1, '', @TNrShowOnBill OUTPUT
    END

    --Create Bill and checkout
    EXEC OnlineCreateBillAndCheckout @FolioNumber, @AHotelCustomerNumber
  END  
END