CREATE PROCEDURE dbo.PostExDepFromArrangement @ReferenceNumber int, @GuestNumber int, @SplitBillNumber int, @UserIDNumber int, @PostFromDate Datetime, @PostToDate Datetime, @CountryNumber int, 
  @ChargedAmount money OUTPUT, @HotelCustomerNumber INT, @ProductWareLinkNumber int=-1, @TableReservationNumber INT = 0, @TransNoCSV VARCHAR(MAX) = '' OUTPUT AS
SET NOCOUNT ON

/*
  Changes:
    KJ 20181031: Support for Mixed resources
    KJ 20181008: Support for Mixed resources
    KJ 20180618: Opti: Optimized sp
    KJ 20180112: New: Collect folio
    KJ 20170614: Ext: Use Allow zero price for posting. Req #2907
    KJ 20150326: Ext: Added arranagementlinenumber. Handle for multiple precharge.
    KJ 20131114: Bug: Fix for "Extras not charged on table folio". Case #66478
    KJ 20110505: Bug: Charge discount as product is implemented
    KJ 20100406: post it on the correct GuestNumber
    KJ 20100128: Ext: Making it more secure that only extras is posted
    KJ 20081007: Bug: Fix for "Only charge on table folio if only table on reservation"
    KJ 20080828: Bug: Fix for "Only charge on table folio if any table on reservation"
    KJ 20080805: Bug: Fix for "Only charge on table folio if any table on reservation"
    KJ 20080131: Bug: Fix for "Arrangementlines sometimes charged on table even if tablebooking isn't enabled"
    KJ 20071206: Bug: Fix for "When table booking enabled and table bokking isn't the error can not instert the value null into column tablenumber"
    KJ 20071130: Bug: Fix for "Mixed reservation (rooms/Halls and tables results in extra's charged multiple times."
    KJ 20071129: Added distinct to select arrangementlinies.
    KJ 20071128: Ext: Added post "table" extras on table folio.
    KJ 20070410: Department rewrite
    KJ 20070222: Fix for 'Arrangement lines with negative amount never charged'.
    KJ 20060707: Added TablereservationNumber to charge tables
    KJ 20060504: Autocharge department changed.
    Rettet d. 21/02-2005 by KS :  Imp DiscountAsNettoPrice
    Rettet d. 23/11-2004 by KS :  Order by [Time] (same order as reservation)
    Rettet d. 15/09-2004 by KS :  some optimization
    Rettet d. 14/05-2004 by KS :  Imp. HotelCustomernumber for multihotel(s)
    Rettet d. 03/02-2004 :  changed department to char(1)
    Rettet d. 16/01-2003 :   Added Read_Only +  Fast_forward  when Declare of  Cursor 
    Rettet d. 5/12-2002 : Charge by periode
    Rettet d. 3/12-2002 : imp countrynumber
*/

Declare @WareText VarChar(50)
DECLARE @FK_ExDepartment INT
Declare @Pieces Int
Declare @WareNumber Int
Declare @Price Money
Declare @TransNrShowOnBillOnAdd Int
Declare @FolioNumber int
Declare @AddressType SmallInt

Declare @DiscountWareNumber int
Declare @DiscountwareText varchar(50)
Declare @AllDiscountRate float
Declare @DiscountPrice money
Declare @TempStr varchar(60)

Declare @PostDate DateTime
Declare @ArrangementLinesDate DateTime

Declare @HotelCountryNumber int
Declare @DiscountAsNettoPrice bit
DECLARE @UseTableDepartmentsInTableBooking BIT
DECLARE @ChargeOnTable INT
DECLARE @DummyArrangementLinieNumber INT
DECLARE @DummyArrLineTime CHAR(5)
DECLARE @DummyOrgGT INT

DECLARE @TableBookingEnabled BIT
DECLARE @ChargeDiscountAsProduct BIT

DECLARE @WareAllowZeroPrice BIT

DECLARE @BookMixedResourcesEnabled BIT
DECLARE @tmpHotelCustomerNumber INT

SET @ChargedAmount = 0

IF ISNULL(@TransNoCSV, '') = ''
  SET @TransNoCSV = ''

SELECT @BookMixedResourcesEnabled = ISNULL(KeyValue,0) FROM HotelSetup WITH (NOLOCK) WHERE Searchkey='BookMixedResourcesEnabled'
SELECT @DiscountAsNettoPrice = ISNULL(KeyValue,0) from HotelSetup WITH (nolock) where Searchkey='DiscountAsNettoPrice'
SELECT @UseTableDepartmentsInTableBooking = ISNULL(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'UseTableDepartmentsInTableBooking'
SELECT @TableBookingEnabled = ISNULL(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'TableBookingEnabled'
SELECT @ChargeDiscountAsProduct = CONVERT(BIT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'ChargeDiscountAsProduct'
SET @ChargeDiscountAsProduct = ISNULL(@ChargeDiscountAsProduct,0)
SET @UseTableDepartmentsInTableBooking = ISNULL(@UseTableDepartmentsInTableBooking,0)
SET @BookMixedResourcesEnabled = ISNULL(@BookMixedResourcesEnabled, 0)

SET @DiscountWareNumber = 0	

SELECT @DiscountwareText = WareName, @DiscountWareNumber = WareNumber 
FROM Ware WITH (NOLOCK) 
INNER JOIN  WareGroups WITH (NOLOCK) ON Ware.WareGroupNumber = WareGroups.WareGroupNumber
WHERE Ware.SortNumber=10
AND Ware.MarkAsDeleted IS NULL
AND WareGroups.Payment=5 
AND ISNULL(WareGroups.MarkAsDeleted, '0') = '0' 

DECLARE ArrangementLineCur CURSOR STATIC READ_ONLY FORWARD_ONLY FOR
  SELECT DISTINCT 
  ArrangementLines.ArrangementLineNumber,
  ArrangementLines.[Time], 
  ArrangementLines.WareText,
  Departments.PK_Department,
  ArrangementLines.Pieces,
  ArrangementLines.WareNumber,
  ArrangementLines.Price,
  Convert(DateTime,ArrangementLines.Date),
  CASE WHEN ISNULL(wdx.FK_Department, 0) = 0 THEN
    0
  ELSE
    1
  END AS ChargeOnTable,
  Ware.AllowZeroPrice,
  Customer.CustomerNumber
  FROM ArrangementLines WITH (NOLOCK) 
  INNER JOIN ArrangementReservationsXtab WITH (NOLOCK) ON ArrangementLines.ArrangementLineNumber = ArrangementReservationsXtab.ArrangementLineNumber 
  INNER JOIN Ware WITH (NOLOCK) ON ArrangementLines.WareNumber = Ware.WareNumber
  LEFT OUTER JOIN WareDepartmentsXTab wdx WITH (NOLOCK) ON wdx.WareNumber = Ware.WareNumber AND wdx.Type = 3
  INNER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = ArrangementLines.FK_ExDepartment
  LEFT OUTER JOIN Transactions (NOLOCK) ON Transactions.FK_ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
  INNER JOIN Customer WITH (NOLOCK) ON Customer.HotelNumber = Departments.HotelNumber
    AND Customer.HotelNumber >= 0
    AND Customer.CustomerTypeNumber = 7
    AND ISNULL(Customer.MarkAsDeleted, 0) = 0
  WHERE ArrangementReservationsXtab.ReferenceNumber = @ReferenceNumber
  AND ArrangementReservationsXtab.GuestNumber = @GuestNumber
  AND ArrangementLines.Date >= Convert(CHAR(8), @PostFromDate, 112) 
  AND ArrangementLines.Date <= Convert(CHAR(8), @PostToDate, 112)
  AND Departments.AutoCharge = 1
  AND ArrangementLines.WareNumber > 0
  AND ISNULL(ArrangementLines.PacketItemNumber, 0) = 0
  AND Transactions.FK_ArrangementLineNumber IS NULL
  ORDER BY [Time], ArrangementLines.ArrangementLineNumber
OPEN ArrangementLineCur

FETCH NEXT FROM ArrangementLineCur INTO @DummyArrangementLinieNumber, @DummyArrLineTime, @WareText, @FK_ExDepartment, @Pieces, @WareNumber, @Price, @ArrangementLinesDate, @ChargeOnTable, @WareAllowZeroPrice, @tmpHotelCustomerNumber

While (@@Fetch_Status=0)
Begin
  if IsNull(@Pieces,0)=0 SET @Pieces=0
  if IsNull(@Price,0)=0 SET @Price=Convert(Money,0)

  SET @PostDate = Convert(DateTime, @ArrangementLinesDate)

  IF @Pieces <> 0 AND ((@Price <> 0) OR (@Price = 0 AND @WareAllowZeroPrice = 1))
  BEGIN
    SET @Price = Round(@Price,2)
    SET @ChargedAmount = (@ChargedAmount +  @Price)
    SET @AddressType = 0 

    IF @BookMixedResourcesEnabled = 1
      SET @HotelCustomerNumber = @tmpHotelCustomerNumber

    IF ((@TableBookingEnabled = 1) AND (DBO.IsOnlyTableReservation(@ReferenceNumber) = 1)) AND
      (
        (@UseTableDepartmentsInTableBooking = 0) AND (ISNULL(@TableReservationNumber, -1) > 0) OR
        (@UseTableDepartmentsInTableBooking = 1) AND (ISNULL(@ChargeOnTable, 0) = 1)
      )
    BEGIN
      IF @UseTableDepartmentsInTableBooking = 1 AND @TableReservationNumber = 0
        SELECT @TableReservationNumber = ReservationConferenceLine.ReservationConfLineNumber FROM ReservationConferenceLine WITH (NOLOCK) WHERE ReferenceNumber = @ReferenceNumber AND TableBooking = 1
      IF ISNULL(@TableReservationNumber, 0) > 0
        EXEC FindFolioNumber @ReferenceNumber, @GuestNumber, @UserIDNumber, @WareNumber, @SplitbillNumber, 1, @FolioNumber OUTPUT, @AddressType OUTPUT, 0, @TableReservationNumber
      ELSE
        Exec FindFolioNumber @ReferenceNumber, @GuestNumber, @UserIDNumber, @WareNumber, @SplitbillNumber, 1, @FolioNumber OUTPUT, @AddressType OUTPUT
    END
    ELSE
      Exec FindFolioNumber @ReferenceNumber, @GuestNumber, @UserIDNumber, @WareNumber, @SplitbillNumber, 1, @FolioNumber OUTPUT, @AddressType OUTPUT

    Exec Postering @PostDate, 0, 1, 0, @GuestNumber, 0, @UserIDNumber, @FolioNumber, 0, 0, 0, 0, @wareText, @Pieces, @Price, @CountryNumber, 0, 0, 0, 0, @FK_ExDepartment, @WareNumber, 0, 0, 0, 0, '', @TransNrShowOnBillOnAdd OUTPUT, 
      @HotelCustomerNumber, @ProductWareLinkNumber, 0, 0, 0, 0, 0, 0, 0, @DummyOrgGT OUTPUT, -1, @DummyArrangementLinieNumber
    IF ISNULL(@TransNrShowOnBillOnAdd, 0) > 0
      SET @TransNoCSV = @TransNoCSV + CAST(@TransNrShowOnBillOnAdd AS VARCHAR) + ','

    if (@DiscountWareNumber>0) AND (@DiscountAsNettoPrice=0)
    begin
      Exec GetDiscountRate @ReferenceNumber, @GuestNumber, @AddressType, 0, @AllDiscountRate output

      if (@AllDiscountRate>0)
      begin
        SET @DiscountPrice =  Round(((@Price)*(@AllDiscountRate/100))*@Pieces,2)
        SET @TempStr = LTRIM(RTRIM(Str(@AllDiscountRate) +' % ' + @DiscountwareText))
        SET @ChargedAmount = (@ChargedAmount -  @DiscountPrice)
        IF @ChargeDiscountAsProduct = 0  
          Exec Postering @PostDate, 1, 1, 0, 0, 0, @UserIDNumber, @FolioNumber, 0, 0, 0, 0, @TempStr, 1, @DiscountPrice, @CountryNumber, 0, 0, 0, 0, @FK_ExDepartment, @DiscountWareNumber, 0, 0, 0, 0,'', @TransNrShowOnBillOnAdd OUTPUT, 
            @HotelCustomerNumber, @ProductWareLinkNumber, 0, 0, 0, 0, 0, 0, 0, @DummyOrgGT OUTPUT, -1, @DummyArrangementLinieNumber
        ELSE
          Exec Postering @PostDate, 1, 1, 0, 0, 0, @UserIDNumber, @FolioNumber, 0, 0, 0, 0, @TempStr, 1, @DiscountPrice, @CountryNumber, 0, 0, 0, 0, @FK_ExDepartment, @DiscountWareNumber, 0, 0, 0, 0,'', @TransNrShowOnBillOnAdd OUTPUT, 
            @HotelCustomerNumber, @ProductWareLinkNumber, @WareNumber, 0, 0, 0, 0, 0, 0, @DummyOrgGT OUTPUT, -1, @DummyArrangementLinieNumber
        IF ISNULL(@TransNrShowOnBillOnAdd, 0) > 0
          SET @TransNoCSV = @TransNoCSV + CAST(@TransNrShowOnBillOnAdd AS VARCHAR) + ','
      end
    end
  end

  Fetch Next From ArrangementLineCur Into @DummyArrangementLinieNumber, @DummyArrLineTime, @WareText, @FK_ExDepartment, @Pieces, @WareNumber, @Price, @ArrangementLinesDate, @ChargeOnTable, @WareAllowZeroPrice, @tmpHotelCustomerNumber
end

Close ArrangementLineCur
Deallocate ArrangementLineCur