CREATE PROCEDURE [dbo].[GiftTokenGenerateGiftTokenNumber] @AGiftTokenTypeId INT, @AGiftTokenNumber VARCHAR(50) OUTPUT, @AHotelCustomerNumber INT AS
BEGIN

  /*
    Created by Kent Johansen January 2009
    Notes: Generate GiftTokenNumber based on template. If no template then just use the identity (GetNextNumber)
    Changes:
      KJ 20131204: Ext: Added ramdom caracter. req #2165
      KJ 20130812: Bug: Fix for "Error generating certificate number". case #CR063154
      KJ 20130125: Ext: GIFTCERTIFICATEIDENTITY per hotel
      KJ 20120905: Ext: Added GUID
      KJ 20101008: Bug: Fix for "Lenght of gifttoken number wrong"
      KJ 20100625: Added setting for Identity lenght
      KJ 20100625: Day and month always 2 digits
      KJ 20090223: CURSOR should be LOCAL
  */
  
  DECLARE @Template VARCHAR(1000)
  DECLARE @Number VARCHAR(50)
  DECLARE @tmp VARCHAR(50)
  DECLARE @CurrentDateStr CHAR(8)
  DECLARE @TheDateTime DATETIME
  DECLARE @HotelNumber INT
  DECLARE @IdentityLength INT
  
  SELECT @IdentityLength = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'GiftCertificateIdentityDigits'
  SET @IdentityLength = ISNULL(@IdentityLength, 5)

  SET @HotelNumber = -1
  IF @AHotelCustomerNumber <> -1
  BEGIN
    SELECT @HotelNumber = HotelNumber 
    FROM Customer WITH (NOLOCK)
    WHERE CustomerNumber = @AHotelCustomerNumber
  END
  
  SELECT TOP 1 @Template = CONVERT(VARCHAR(255), CONVERT(VARBINARY(255), Blob)) 
  FROM Templates t WITH (NOLOCK) 
  INNER JOIN GiftTokenType gtt WITH (NOLOCK) ON gtt.FK_TemplateNumber = t.TemplateNumber
    AND gtt.Id = @AGiftTokenTypeId
  WHERE t.TemplateType = 1 -- GenerateId
  AND ((t.HotelNumber = @HotelNumber) OR (t.HotelNumber = -1))
  ORDER BY t.HotelNumber DESC

  IF ISNULL(@Template, '') = ''
  BEGIN
    -- No template
    SET @AGiftTokenNumber = ''
    EXEC GetNextNumber 'GIFTCERTIFICATEIDENTITY', 1, @AGiftTokenNumber OUTPUT, @AHotelCustomerNumber
    IF ISNULL(@AGiftTokenNumber, 0) < 0
      RAISERROR('Error in storedprocedure <<GiftTokenGenerateGiftTokenNumber>>: Not able to get gift certificate identity',15,1)      
  END
  ELSE
  BEGIN
    -- Generate id from template
    SET @AGiftTokenNumber = ''

    SELECT @CurrentDateStr = SYSTEMDATE FROM System WITH (NOLOCK) WHERE NUMBER = 1    
    SET @TheDateTime = CONVERT(DATETIME, @CurrentDateStr + ' ' + CONVERT(CHAR(8), CURRENT_TIMESTAMP, 108))

    DECLARE db_cur CURSOR LOCAL FOR
      SELECT Value FROM DBO.SplitList(@Template)
    OPEN db_cur
  
    FETCH NEXT FROM db_cur INTO @Number

    WHILE @@FETCH_STATUS = 0
    BEGIN
      SET @tmp = ''
      -- TGiftCertificateTemplateField = (gctfIdentity, gctfYear, gctfMonth, gctfDay, gctfHotelNumber, gctfOwn, gctfGUID)
      IF @Number = '0'
      BEGIN
        EXEC GetNextNumber 'GIFTCERTIFICATEIDENTITY', 1, @tmp OUTPUT, @AHotelCustomerNumber
        IF ISNULL(@tmp, 0) < 0
          RAISERROR('Error in storedprocedure <<GiftTokenGenerateGiftTokenNumber>>1: Not able to get gift certificate identity',15,1)      
        SET @tmp = RIGHT('0000000000'+CONVERT(VARCHAR(10), @tmp), @IdentityLength)
      END  
      ELSE IF @Number = '1'
        SET @tmp = DATEPART(YEAR, @TheDateTime)
      ELSE IF @Number = '2'
        SET @tmp = RIGHT('00'+CONVERT(VARCHAR(2), DATEPART(MONTH, @TheDateTime)), 2)
      ELSE IF @Number = '3'
        SET @tmp = RIGHT('00'+CONVERT(VARCHAR(2), DATEPART(DAY, @TheDateTime)), 2)
      ELSE IF @Number = '4'
        SET @tmp = @HotelNumber
      ELSE IF @Number = '6'
        SET @tmp = NEWID()
      ELSE IF @Number = '7'
        SELECT @tmp = LEFT(CAST(NEWID() AS VARCHAR(36)), 1)
      ELSE -- OwnText
        SET @tmp = @Number

      SET @AGiftTokenNumber = @AGiftTokenNumber + CAST(@tmp AS VARCHAR(50))

      FETCH NEXT FROM db_cur INTO @Number
    END
    CLOSE db_cur
    DEALLOCATE db_cur
  END  
END