CREATE PROCEDURE dbo.OnlinePostering  @Referencenumber INT, @WareTextOverwrite VARCHAR(100), @TotalPrice MONEY, @Note VARCHAR(50) , @TransNrShowOnBill INT OUTPUT, 
  @AGlobalTransactionNumber INT = 0, @AGuestNumber INT = 0, @AOverwriteCheckForMultipleTransactions BIT = 0, @ACreditCardFee MONEY = 0, @AOnlineCCType VARCHAR(100) = '' AS
SET NOCOUNT ON

/*
    Created by Troels Buch Marts 2005
    
    Changes:
	  KJ 20150611: Bug: Fix for "Wrong department for credit card fee extras"
	  KJ 20150513: Bug: Fix for "Deleted wares is used".
	  KJ 20150505: Bug: Fix for "Arrangement line for cc fee not saved correctly"
      KJ 20150413: Ext: Added post cc fee as arrangement
      KJ 20150316: Ext: Added OnlineCCType.
	  KJ 20140611: Ext: CashDesk per hotel. Case #CR073269
      KJ 20121212: Ext: Added support for credit card fee.
      KJ 20111201: Bug: Fix for "Error when calling sp from PayAndCheckOut-app. (payment when inhouse)".
      KJ 20111014: Bug: Fix for "This sp updates the moveddelete stauts on old Transactions when called in online checkout". Case #CR034965
      KJ 20110624: Bug: Fix for "Online warenumber is per hotel".
      KJ 20110308: Bug: Fix for "Raise error when online ware entry is set to NONE"
      KJ 20110211: Bug: Fix for "Raise error when inhouse payment isn't set"
      KJ 20110131: Ext: Added support for GuestNumber > 0 and payment for inhouse reservations
      KJ 20101229: Bug: Fix for "CashBox not always set"
      NB 20101128: Bug: In Domino the wrong cashbox used because not relation to the hotel. Relation now added and Externals should specify internally the correct cashbox used for Online
      KJ 20100713: Bug: Fix for "wrong cashbox relation"
      KJ 20100211: Bug: Fix for "Wrong department for Domino Hotels"
      KJ 20081217: Ext: Added GlobalTransactionNumber as input param
      KJ 20081217: Bug: Fix for "Moved deleted on online deposit"
      KJ 20080103: Bug: Fix for "Do not raise error when other transactions than deposit has been made"
      KJ 20071018: Added Raise error when some deposit transactions is marked as deleted.
      KJ 20070620: Fix error for @Departments
      KJ 20070412: Department Rewrite
      KJ 20070306: Changed the way to check for sp called multiple times
      KJ 20070112: Fix for "Sometimes this SP is called multiple times."
      TB20060330: Removed begin tran
      KJ 20060215: Changed update transactions.
      KJ20060123: Possible fix for MovedDeleted = 1
      KJ20060119: Making sure that MovedDeleted = 0
      KJ20060116: Fix for "Not the correct HotelNumber in Followup-table"
      TB20051221: Check if If Domino before change Department
      TB20051220: Added check for Logstor - Must be changed as soon as Hotelsetup can handle Domino
      KJ20051123: Followup: Userid changed to Onlineuser, and not '!' as default
      KJ20051122: Added Update of DepositFollowupNumber
      KJ20051109: The DepositFollowNumber removed again.
      KJ20051104: Added DepositFollowupNumber to call to Postering SP
      KJ20051028: Removed @ErrorLevel -> not used.
      KJ20051028: Added RaiseError if @referencenumber <= 0
      KJ20050707: Forgot to set @CentralBookingType
      KJ20050706: Moved the line 'deallocate db_cur'
      KJ20050630: Only create followup if payment = 12 (recieved deposit)
      TB20050621: Now calling postering again
      KJ20050616: Creating followups. 
      TB/KJ20050616: Payment not set. Getting the payment type from ware.
      TB20050615: Changed @Department to VARCHAR insted of INT
      TB20050408: Changed check on @Department, @Warenumber and @CountryidCount from -1 to 0

    NOTES: 	- 

*/
DECLARE @cnt1 INT
DECLARE @UserId INT
DECLARE @FK_Department INT
DECLARE @Warenumber INT
DECLARE @WareNumberInhouse INT
DECLARE @CentralBookingType smallint
DECLARE @Payment INT
DECLARE @PaymentInhouse INT
DECLARE @Countryid INT
DECLARE @CountryidCount INT
DECLARE @Debug INT
DECLARE @HotelCustomerNumber1 int
DECLARE @HotelNumber INT
DECLARE @FolioNumber INT
DECLARE @Cnt INT
DECLARE @DummyOriginalGlobalTransactionNumber INT
DECLARE @UseCashDesk BIT
DECLARE @CashDesk INT
DECLARE @Status INT
DECLARE @tmpWareNumber INT
DECLARE @tmpWareNumberInhouse INT
DECLARE @tmpPayment INT
DECLARE @tmpPaymentInhouse INT
DECLARE @AutoDepartment INT

SET @Debug = 0

if (@Referencenumber <= 0)
  RAISERROR('Error in storedprocedure <<OnlinePostering>>: @Referencenumber <= 0. ',15,1)

Select @CentralBookingType = Convert(Integer, Keyvalue) From HotelSetup WITH (NOLOCK) WHERE SearchKey = 'CentralBookingType'
if isnull(@CentralBookingType,0)=0 SET @CentralBookingType = 0

IF ISNULL(@AOverwriteCheckForMultipleTransactions,0) = 0
BEGIN
  SELECT @cnt1 = COUNT(*) FROM Folios WITH (NOLOCK) WHERE ReferenceNumber = @ReferenceNumber
  IF @cnt1 > 0
  BEGIN
    SELECT @cnt1 = COUNT(*) FROM Transactions T WITH (NOLOCK) 
    INNER JOIN Folios F WITH (NOLOCK) ON T.FolioNumber = F.FolioNumber AND ReferenceNumber = @ReferenceNumber AND Payment IN (11,12)
  
    IF @cnt1 > 0
      RAISERROR('Error in storedprocedure <<OnlinePostering>>: This SP is called multiple times',15,1)
  END
END

SET @AGuestNumber = ISNULL(@AGuestNumber, 0)

SELECT @UserId = Keyvalue FROM Hotelsetup WITH (NOLOCK) Where Searchkey = 'PicassoOnlinePosteringUserIdNumber'
IF @UserId <= 0
  SELECT TOP 1 @UserId = UserIDNumber FROM Personel WITH (Nolock)

SELECT @HotelCustomerNumber1 = Customer.CustomerNumber, @HotelNumber = Customer.HotelNumber 
FROM ReservationMain WITH (NOLOCK) 
INNER JOIN Customer WITH (NOLOCK) ON Customer.HotelNumber = ReservationMain.HotelNumber 
  AND ReferenceNumber = @ReferenceNumber
SELECT @FK_Department = dbo.GetHotelSetupKeyValue ('PicassoOnlinePosteringDepartment', @HotelCustomerNumber1)

SELECT @tmpWareNumber = dbo.GetHotelSetupKeyValue ('PicassoOnlinePosteringWarenumber', @HotelCustomerNumber1)
SELECT @tmpWareNumberInhouse = dbo.GetHotelSetupKeyValue ('OnlineInhousePaymentWareNumber', @HotelCustomerNumber1)

SELECT @tmpPayment = Payment FROM Ware (NOLOCK) WHERE WareNumber = @tmpWareNumber
SELECT @tmpPaymentInhouse = Payment FROM Ware (NOLOCK) WHERE WareNumber = @tmpWareNumberInhouse

SET @WareNumber = 0

IF ISNULL(@AOnlineCCType, '') <> ''
BEGIN
  SELECT TOP 1 @Warenumber = CreditCardTypeWareXTab.WareNumber
  FROM TypeNoName WITH (NOLOCK)
  INNER JOIN CreditCardType WITH (NOLOCK) ON CreditCardType.CreditCardTypeID = TypeNoName.FK_NoName
  INNER JOIN CreditCardTypeWareXTab WITH (NOLOCK) ON CreditCardTypeWareXTab.CreditCardTypeID = CreditCardType.CreditCardTypeID
  INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = CreditCardTypeWareXTab.WareNumber
    AND Ware.Payment = @tmpPayment
    AND ISNULL(MarkAsDeleted, 0) = 0
  WHERE TypeNoName.Type_FK = 2 -- Online CC type text
  AND TypeNoName.Name = @AOnlineCCType
  ORDER BY CreditCardType.Number
END

IF ISNULL(@Warenumber, 0) <= 0
  SELECT @Warenumber = @tmpWareNumber
IF ISNULL(@WareNumber, 0) = 0
  RAISERROR('Online entry ware number is empty. Set value in HotelSet - Basc - Online', 15, 1)
  
IF ISNULL(@AOnlineCCType, '') <> ''
BEGIN
  SELECT TOP 1 @WareNumberInhouse = CreditCardTypeWareXTab.WareNumber
  FROM TypeNoName WITH (NOLOCK)
  INNER JOIN CreditCardType WITH (NOLOCK) ON CreditCardType.CreditCardTypeID = TypeNoName.FK_NoName
  INNER JOIN CreditCardTypeWareXTab WITH (NOLOCK) ON CreditCardTypeWareXTab.CreditCardTypeID = CreditCardType.CreditCardTypeID
  INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = CreditCardTypeWareXTab.WareNumber
    AND Ware.Payment = @tmpPaymentInhouse
    AND ISNULL(MarkAsDeleted, 0) = 0
  WHERE TypeNoName.Type_FK = 2 -- Online CC type text
  AND TypeNoName.Name = @AOnlineCCType
  ORDER BY CreditCardType.Number
END
IF ISNULL(@WareNumberInhouse, 0) <= 0
  SET @WareNumberInhouse = @tmpWareNumberInhouse

SELECT @Payment = Payment FROM Ware WITH (NOLOCK) Where WareNumber = @WareNumber
SELECT @PaymentInhouse = Payment FROM Ware WITH (NOLOCK) Where WareNumber = @WareNumberInhouse

SELECT @CountryidCount = Count(*) FROM Hotelsetup WITH (NOLOCK) Where Searchkey = 'CountryCode'

IF @CountryidCount > 0
  SELECT @Countryid = Keyvalue FROM Hotelsetup WITH (NOLOCK) Where Searchkey = 'CountryCode'
ELSE
  SET @Countryid = 1

IF @Debug = 1
begin
  print '******************************'
  print @Referencenumber 
  print @UserId
  print @WareTextOverwrite
  print CONVERT(varchar(50),@TotalPrice)
  print @Countryid
  print @FK_Department
  print @Warenumber
  PRINT @WareNumberInhouse
  print @Note
  print @TransNrShowOnBill 
  print '******************************'
END

IF @Payment = 12
BEGIN
  -- Followup
  DECLARE
    @SystemDate smalldatetime,
    @DepositText varchar(50),
    @ShownName smallint,
    @Iden int,
    @Departments varchar(50),
    @UserIdNumber int

  SELECT @SystemDate = SYSTEMDATE FROM System WITH (NOLOCK) WHERE Number = 1
  SET @DepositText = 'Depositum Online'

  SELECT @ShownName = AddressType FROM ReservationAddress WITH (NOLOCK) 
  WHERE AddressNumber IN (
    SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) 
    WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber = @AGuestNumber)
  ORDER BY AddressType DESC

  INSERT INTO FollowUp ([Date], Type, DepositAmount, Priority, State, Note, Shownname, CreatedDate, CreatedBy, UserCreated, SubType, RuleNumber, OverWritten, Tag, ReservationStatus, HotelNumber)
  VALUES (@SystemDate, 15, @TotalPrice, 17, 3, @DepositText, @ShownName, @SystemDate+convert(varchar, getdate(), 108), @UserId, NULL, NULL, 1, 0, NULL, NULL, @HotelNumber)

  SELECT @Iden = @@Identity

  INSERT INTO ReservationFollowUpXTab (FollowUpNumber, ReferenceNumber, GuestNumber)
  VALUES (@Iden, @Referencenumber, @AGuestNumber)

  SELECT @Departments = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'FollowUpDefaultDepartmentsDeposits'
  IF @Departments <> ''
  DECLARE db_cur CURSOR FOR
    SELECT DISTINCT Personel.UserIdNumber 
    FROM Personel WITH (NOLOCK) 
    INNER JOIN PersonelDepartmentsXtab pd WITH (NOLOCK) ON pd.UserIdNumber = Personel.UserIdNumber 
      AND pd.FK_Department IN (SELECT number FROM DBO.iter_intlist_to_table(@Departments))
  OPEN db_cur 

  FETCH NEXT FROM db_cur INTO @UserIdNumber

  WHILE @@fetch_status = 0
  BEGIN
    INSERT INTO PersonalFollowUpXTab (UserIdNumber, FollowUpNumber) VALUES (@UserIdNumber, @Iden)
    FETCH NEXT FROM db_cur INTO @UserIdNumber
  END
  DEALLOCATE db_cur
END

IF ISNULL(@AGlobalTransactionNumber, 0) <= 0
  EXEC GetNextNumber 'GLOBALTRANSACTIONIDENTITY', 2, @AGlobalTransactionNumber OUTPUT

--SELECT @UseCashDesk = CONVERT(BIT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE Searchkey = 'FiscalPrinterSweden'
SELECT @UseCashDesk = dbo.GetHotelSetupKeyValue ('FiscalPrinterSweden', @HotelCustomerNumber1)
SET @UseCashDesk = ISNULL(@UseCashDesk,0)

DECLARE @ArrivalDate CHAR(8)
DECLARE @ArrivalTime CHAR(5)

SELECT @Status = Status, @ArrivalDate = ArrivalDate, @ArrivalTime = ArrivalTime
FROM Reservations WITH (NOLOCK)
WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber = @AGuestNumber

IF @Status >= 5 -- inhouse / Use the inhouse payment ware
BEGIN
  IF ISNULL(@WareNumberInhouse, 0) <= 0
    RAISERROR('Error in storedprocedure <<OnlinePostering>>: Online Inhouse Payment Ware <= 0. ',15,1)

  SET @Warenumber = @WareNumberInhouse 
  SET @Payment = @PaymentInhouse
END

IF @UseCashDesk = 1
BEGIN
  EXEC GetCashDeskForOnline @HotelCustomerNumber1, @CashDesk OUTPUT

  EXEC Postering 0, 0, 1, @Referencenumber, @AGuestNumber, 0, @UserId, 0, 0, 0, 0, 0, @WareTextOverwrite, 1, @TotalPrice, @Countryid, 0, 0, 0, 0, @FK_Department, @Warenumber, @Payment, @AGlobalTransactionNumber, 0, 0, @Note, @TransNrShowOnBill OUTPUT,
    -1, -1, 0, 0, 0, 0, 0, 0, 0, 0, @DummyOriginalGlobalTransactionNumber OUTPUT, @CashDesk
END
ELSE
BEGIN
  EXEC Postering 0, 0, 1, @Referencenumber, @AGuestNumber, 0, @UserId, 0, 0, 0, 0, 0, @WareTextOverwrite, 1, @TotalPrice, @Countryid, 0, 0, 0, 0, @FK_Department, @Warenumber, @Payment, @AGlobalTransactionNumber, 0, 0, @Note, @TransNrShowOnBill 
END

SELECT @FolioNumber = FolioNumber FROM Folios WITH (NOLOCK) WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber = @AGuestNumber --AND HotelCustomerNumber = @HotelCustomerNumber1   KJ20060215
--UPDATE Transactions SET DepositFollowupNumber = @Iden, MovedDeleted = 0 WHERE FolioNumber = @FolioNumber

SELECT @Cnt = COUNT(*) FROM Transactions WITH (NOLOCK) WHERE FolioNumber = @FolioNumber AND MovedDeleted = 1 AND DepositFollowupNumber = @Iden
IF (@Cnt > 0)
BEGIN
  DECLARE @Str VARCHAR(250)
  SET @Str = 'Error in storedprocedure <<OnlinePostering>>: MovedDeleted = 1'+convert(varchar(100), @FolioNumber)
  RAISERROR(@Str, 15, 1)
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 ((@CentralBookingType NOT IN (3,7)) OR (Departments.HotelNumber IN (SELECT HotelNumber FROM Customer WITH (NOLOCK) WHERE CustomerTypeNumber = 7 AND CustomerNumber = @HotelCustomerNumber1)))
  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 @CCFeeAsArr BIT
  DECLARE @ccFeeName VARCHAR(100)
  DECLARE @Ident INT
  SELECT @CCFeeAsArr = Keyvalue FROM Hotelsetup WITH (NOLOCK) WHERE Searchkey = 'OnlineCreditCardFeeAsArrangement'
  
  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
  
  IF ISNULL(@CCFeeAsArr, 0) = 1
  BEGIN
    INSERT INTO ArrangementLines (Date, PacketItemNumber, Time, Price, Pieces, WareText, PlaceText, WareNumber, HallNumber, FK_EXDepartment, FK_HallNumber)
    VALUES (@ArrivalDate, NULL, @ArrivalTime,  @ACreditCardFee, 1, @ccFeeName, '', @FeeWareNumber, NULL, @AutoDepartment, NULL)
	SET @Ident = @@IDENTITY
	INSERT INTO ArrangementReservationsXtab VALUES (@Ident, @Referencenumber, @AGuestNumber, 0)
  END    
  else
    EXEC OnlinePosteringExt @FeeWareNumber, @HotelCustomerNumber1, @FolioNumber, @ACreditCardFee, 1, '', @TNrShowOnBill OUTPUT
END