CREATE PROCEDURE DBO.[FolioBonuscardUpdate] @ReferenceNumber INT, @GuestNumber INT, @PkCardOld INT, @PkCardNew INT AS
SET NOCOUNT ON
/*NB20191011 Created
  NOTE- Add, Remove or Replace Folios bonuscards controlled by triggers using @PkCardNew, @PkCardOld or both params
*/
DECLARE @DebugLevel TINYINT, @Msg VARCHAR(2000), @PkStaff INT, @FolioNumber INT, @AddressType TINYINT, @Date DATETIME, @AddressTypeCSV VARCHAR(10)
SET @DebugLevel = 0
IF @DebugLevel = 3
BEGIN
  PRINT '--- [FolioBonuscardUpdate] begin ---'
  SET @Msg = 'DECLARE @ReferenceNumber INT, @GuestNumber INT, @PkCardOld INT, @PkCardNew INT'+CHAR(13)
  IF ISNULL( @ReferenceNumber, -1) = -1
    SET @Msg = @Msg+'SET @ReferenceNumber = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @ReferenceNumber = '+CONVERT(VARCHAR,@ReferenceNumber)+CHAR(13)
  IF ISNULL( @GuestNumber, -1) = -1
    SET @Msg = @Msg+'SET @GuestNumber = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @GuestNumber = '+CONVERT(VARCHAR,@GuestNumber)+CHAR(13)
  IF ISNULL( @PkCardOld, -1) = -1
    SET @Msg = @Msg+'SET @PkCardOld = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @PkCardOld = '+CONVERT(VARCHAR,@PkCardOld)+CHAR(13)
  IF ISNULL( @PkCardNew, -1) = -1
    SET @Msg = @Msg+'SET @PkCardNew = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg+'SET @PkCardNew = '+CONVERT(VARCHAR, @PkCardNew)+CHAR(13)

  SET @Msg = @Msg+'EXEC dbo.FolioBonuscardUpdate @ReferenceNumber, @GuestNumber, @PkCardOld, @PkCardNew'+ CHAR(13)
  PRINT @Msg
  PRINT '--- [FolioBonuscardUpdate] end ---'
END
SELECT TOP 1 @PkStaff = FK_UserIdNumber FROM PersonnelLogInfo (NOLOCK)
WHERE spid = @@SPID
IF @@RowCount = 0
  SELECT TOP 1 @PkStaff = UserIdNumber FROM Personel (NOLOCK) WHERE UserTypeNumber=2 --Techotel
SET @Date = GETDATE()
SELECT @AddressTypeCSV = dbo.GetHotelSetupKeyValue('BonuscardReservation2Folio', -1)
SET @AddressTypeCSV = ISNULL(@AddressTypeCSV, '1') --Default guest
SET @PkCardNew = ISNULL(@PkCardNew, 0)
SET @PkCardOld = ISNULL(@PkCardOld, 0)
IF @DebugLevel > 0
BEGIN
  PRINT '@PkCardOld='+CONVERT(VARCHAR,@PkCardOld)
  PRINT '@PkCardNew='+CONVERT(VARCHAR,@PkCardNew)
  PRINT '@AddressTypeCSV='+ISNULL(CONVERT(VARCHAR,@AddressTypeCSV), '')
END
IF @PkCardNew <> @PkCardOld
BEGIN
	IF ISNULL(@PkCardNew, 0) > 0 and ISNULL(@PkCardOld, 0) = 0 --Add Card
	BEGIN
	  DECLARE #FolioCur CURSOR FAST_FORWARD READ_ONLY FOR
	  SELECT F.FolioNumber, RAX.AddressType 
	  FROM dbo.Folios F (NOLOCK) 
		LEFT JOIN dbo.ReservationAddress RAX (NOLOCK) ON RAX.AddressNumber = F.ReservationAddressNumber
		LEFT JOIN dbo.FolioCardXTab FCX (NOLOCK) ON FCX.FolioNumber = F.FolioNumber
	  WHERE F.ReferenceNumber = @ReferenceNumber AND F.GuestNumber = @GuestNumber AND (FCX.FK_Card IS NULL OR NOT LogNumberDelete IS NULL OR FCX.FK_Card <> @PkCardNew AND LogNumberDelete IS NULL)
	END
	ELSE IF ISNULL(@PkCardNew, 0) > 0 and ISNULL(@PkCardOld, 0) > 0 --Replace Card
	BEGIN
	  DECLARE #FolioCur CURSOR FAST_FORWARD READ_ONLY FOR
	  SELECT F.FolioNumber, RAX.AddressType
	  FROM dbo.Folios F (NOLOCK) 
		LEFT JOIN dbo.ReservationAddress RAX (NOLOCK) ON RAX.AddressNumber = F.ReservationAddressNumber
		LEFT JOIN dbo.FolioCardXTab FCX (NOLOCK) ON FCX.FolioNumber = F.FolioNumber
	  WHERE F.ReferenceNumber = @ReferenceNumber AND F.GuestNumber = @GuestNumber AND (FCX.FK_Card IS NULL OR NOT LogNumberDelete IS NULL OR FCX.FK_Card = @PkCardOld AND LogNumberDelete IS NULL)
	END    
	ELSE IF ISNULL(@PkCardNew, 0) = 0 and ISNULL(@PkCardOld, 0) > 0 --Remove Card
	BEGIN
	  DECLARE #FolioCur CURSOR FAST_FORWARD READ_ONLY FOR
	  SELECT F.FolioNumber, RAX.AddressType
	  FROM dbo.Folios F (NOLOCK) 
		LEFT JOIN dbo.ReservationAddress RAX (NOLOCK) ON RAX.AddressNumber = F.ReservationAddressNumber
		LEFT JOIN dbo.FolioCardXTab FCX (NOLOCK) ON FCX.FolioNumber = F.FolioNumber
	  WHERE F.ReferenceNumber = @ReferenceNumber AND F.GuestNumber = @GuestNumber AND FCX.FK_Card = @PkCardOld
	END
	
	OPEN #FolioCur
	FETCH NEXT FROM #FolioCur
	INTO @FolioNumber, @AddressType
		WHILE @@FETCH_STATUS = 0 
		BEGIN
			IF @DebugLevel > 0 
			BEGIN
			PRINT '@AddressType='+ISNULL(CONVERT(VARCHAR,@AddressType), '')
			END
			IF @AddressTypeCSV <> '' AND @AddressType IN (SELECT DISTINCT Number FROM dbo.iter_intlist_to_table(@AddressTypeCSV))
			IF ISNULL(@PkCardNew, 0) > 0 and ISNULL(@PkCardOld, 0) = 0 --Add Card
			BEGIN
				INSERT INTO dbo.LogPicasso (UserIdNumber, DateStamp, Text) VALUES (@PkStaff, @Date, 'Assigned card ' + CONVERT(VARCHAR, @PkCardNew) + ' from Folio ' + CONVERT(VARCHAR, @FolioNumber))
				INSERT INTO dbo.FolioCardXTab (FolioNumber, FK_Card, LogNumberCreate) VALUES (@FolioNumber, @PkCardNew, @@Identity)
			END
			ELSE IF ISNULL(@PkCardNew, 0) > 0 and ISNULL(@PkCardOld, 0) > 0 --Replace Card
			BEGIN
				INSERT INTO dbo.LogPicasso (UserIdNumber, DateStamp, Text) VALUES (@PkStaff, @Date, 'Removed card ' + CONVERT(VARCHAR, @PkCardOld) + ' from Folio ' + CONVERT(VARCHAR, @FolioNumber))
				UPDATE dbo.FolioCardXTab SET LogNumberDelete = @@Identity WHERE FolioNumber = @FolioNumber AND FK_Card = @PkCardOld
				INSERT INTO dbo.LogPicasso (UserIdNumber, DateStamp, Text) VALUES (@PkStaff, @Date, 'Assigned card ' + CONVERT(VARCHAR, @PkCardNew) + ' from Folio ' + CONVERT(VARCHAR, @FolioNumber))
				INSERT INTO dbo.FolioCardXTab (FolioNumber, FK_Card, LogNumberCreate) VALUES (@FolioNumber, @PkCardNew, @@Identity)
			END
			ELSE IF ISNULL(@PkCardNew, 0) = 0 and ISNULL(@PkCardOld, 0) > 0 --Remove Card
			BEGIN
				INSERT INTO dbo.LogPicasso (UserIdNumber, DateStamp, Text) VALUES (@PkStaff, @Date, 'Removed card ' + CONVERT(VARCHAR, @PkCardOld) + ' from Folio ' + CONVERT(VARCHAR, @FolioNumber))
				UPDATE dbo.FolioCardXTab SET LogNumberDelete = @@Identity WHERE FolioNumber = @FolioNumber AND FK_Card = @PkCardOld
			END  
			FETCH NEXT FROM #FolioCur
			INTO @FolioNumber, @AddressType
		END
	CLOSE #FolioCur
	DEALLOCATE #FolioCur
	
END
/*
SELECT DISTINCT RAX.ReferenceNumber, RAX.GuestNumber, RA.Link, CCX.FK_Card, (SELECT Count(*) fROM Folios (NOLOCK) WHERE ReferenceNumber = RAX.ReferenceNumber AND GuestNumber = RAX.GuestNumber) AS FolioCount  FROM ReservationAddressXTab RAX (NOLOCK) 
  INNER JOIN ReservationAddress RA (NOLOCK) ON RAX.AddressNumber = RA.AddressNumber AND RA.AddressType = 1
  INNER JOIN dbo.Customer C (NOLOCK) ON RA.Link = C.CustomerNumber
  INNER JOIN dbo.CustomerCardXTab CCX (NOLOCK) ON CCX.CustomerNumber = C.CustomerNumber
ORDER BY FolioCount, RAX.ReferenceNumber desc, RAX.GuestNumber
*/