CREATE PROCEDURE DBO.FindFolioNumber @ReferenceNumber Integer, @GuestNumber Integer, @UserIDNumber Integer, @WareNumber Integer, @SplitbillNumber Integer, @CreateNewFolioIfNotExists bit, @FolioNumber Integer OUTPUT, @AddressType SmallInt OUTPUT, @FirstAddressNumber INT = 0, @TableReservationNumber int = 0, @ProvisionPostOnGuest BIT = 0  AS
SET NOCOUNT ON
/*
  KJ 20191120: Bug: Fix for "Post is made on wrong folionumber if bonus card". Case #CR174101
  KJ 20160105: Bug: Fix for "Unable to convert the varchar value 0, to datatype integer". Case #CR096926
  KJ 20150430: Bug: Fix for "Provision not posted on correct folio".
  --nb20140721 FolioIdentity discontinued
  KJ 20110525: Ext: "ProjectCode added". Req# 787 
  KJ 20110518: Ext: "Post provision on guest". Req# 890+908
  NB 20091022 Bug safety HotelCustomerNumber = -1 by creating folio not in domino
  KJ 20090917: Ext: Collect group on halltypes used to open correct folio
  KJ 20090916: Bug: Fix for "Can not insert the value NULL into field chairnumber"
  KJ 20090914: Ext: Bowling+table folios should be posted on same folio. Case #CR009895
  KJ 20090811: Bug: Bowling folio not related to correct ConferenceLineNumber
  KJ 20090811: Ext: Post on p-folio for bowling reservation, or use first Table/bowling line
  KJ 20090806: Ext: Use only one folio for bowling reservations. Post is made on only one table if multiple tables on reservation
  KJ/NB 20081104: Bug: Fix for "Multiple Folios for same type sometimes exists" - Use existing even if is closed
  KJ 20080304: Bug: Fix for "Tablenumber sometimes NULL"
  KJ 20080125: Bug: Fix for "AddressNumber sometimes NULL"
  KJ 20080116: Bug: Fix for "FirstFolioNumber sometimes NULL"
  KJ 20070212: Changed lock timeout
--nb20070208 Do not store GuestNumber on folios when dealing with tables, get the guest and referencenumber from the reservation
  2006 0627 fix for cannot insert dublicate key in reservationaddressxtab 
  20051201 NB Fix af når ikke domino og natkørsel opretter folier
  20050613 JP+AV fix save of ReservationAddressXTab
  20050526 JP changed to use GetNextNumber SP
  14-04-2005  Imp tablebooking
  24-03-2004  Use billtype from customer/hotelsetup when create new folio
  13-09-2004  bugfix billtype and new folio
  14-09-2004  bugfix billtype and new folio (13/9)

  NOTE: Needs a AddressNumber and FolioNumber to avoid locking of identity tables!
*/

Declare @TempAddressType smallint
Declare @GuestTypeNumber smallint
Declare @BillNumber Integer
Declare @DoWhile Bit
Declare @AddressNumber Integer

Declare @BillType int
Declare @DefaultBillTypeHotelSetup int
DECLARE @DefaultBillTypeHotelSetupStr VARCHAR(100)

Declare @TableNumber int
Declare @ChairNumber int
DECLARE @TempConfLineNumber INT

--Select @DefaultBillTypeHotelSetup = isnull(Convert(Integer, Keyvalue),0) from HotelSetup WITH (Nolock) where searchkey = 'BillTypeRoom'
SELECT @DefaultBillTypeHotelSetupStr = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'BillTypeRoom'

IF dbo.IsReallyNumeric(@DefaultBillTypeHotelSetupStr) = 1
  SET @DefaultBillTypeHotelSetup = ISNULL(CONVERT(INTEGER, @DefaultBillTypeHotelSetupStr), 0)
SET @DefaultBillTypeHotelSetup = ISNULL(@DefaultBillTypeHotelSetup, 0)

DECLARE @UseTableBooking BIT
SELECT @UseTableBooking = CONVERT(BIT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'TableBookingEnabled'
SET @UseTableBooking = ISNULL(@UseTableBooking, 0)

DECLARE @ProjCode INT

--SET lock_timeout 60000
DECLARE @LockTimeOut INT
SET @LockTimeOut = @@LOCK_TIMEOUT
SET LOCK_TIMEOUT 60000

if @TableReservationNumber = 0 -- Ikke nødvendigt hvis bordbooking
begin
  IF @ProvisionPostOnGuest = 1
  BEGIN
    SET @TempAddressType = 1
  END
  ELSE
  BEGIN
    if @WareNumber>0 AND @SplitbillNumber>0
    begin --   TSplitBillGuestType = (sbgNotDefined, sbgGuest, sbgGuestMain, sbgCompany, sbgCompanyMain, sbgAgency, sbgAgencyMain, sbgAgent, sbgAgentMain);
            --   TAddressType             = (atNone, atGuest,  atCompany,atAgency, atAgent, atCompanyCorp);
      Select @GuestTypeNumber = GuestType from SplitBillWareGroupXTab WITH (NOLOCK) where SplitBillNumber = @SplitbillNumber AND WareGroupNumber in (Select WareGroupNumber From Ware WITH (NOLOCK) where WareNumber=@WareNumber)
      IF ISNULL(@GuestTypeNumber,1) = 1 
        SET @GuestTypeNumber = 1 -- Safe if not marked - guest pay

      if (@GuestTypeNumber in (2,4,6,8))
      begin
        SET @GuestNumber=0
        SET @TempAddressType = (@GuestTypeNumber/2)
      end
      else
      begin
        if @GuestTypeNumber=1
          SET @TempAddressType = 1
        else if @GuestTypeNumber=3
          SET @TempAddressType = 2
        else if @GuestTypeNumber=5
          SET @TempAddressType = 3
        else if @GuestTypeNumber=7
         SET @TempAddressType = 4
      end
    end
    else if (@AddressType>0) AND (@ReferenceNumber>0)
    BEGIN
      IF @ProvisionPostOnGuest <> 1
        SET @TempAddressType = @AddressType
      ELSE
        SET @TempAddressType = 1
    END
    else
      SET @TempAddressType = 1
  END

  SET @AddressNumber=0

  Select @AddressNumber = ISNULL(ReservationAddress.AddressNumber, 0),
         @BillType = isnull(Customer.BillTypeNumber,0)
  from ReservationAddress WITH (NOLOCK)
  INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.AddressNumber = ReservationAddress.AddressNumber
  Left outer join Customer WITH (nolock) on ReservationAddress.Link = Customer.CustomerNumber
  WHERE ReservationAddressXTab.ReferenceNumber=@ReferenceNumber and ReservationAddressXTab.GuestNumber=@GuestNumber AND ReservationAddressXTab.AddressTypeNumber = @TempAddressType --AV + JP 20050613

  if (ISNULL(@AddressNumber, 0)=0) AND (@TempAddressType>0) AND (@ReferenceNumber>0) AND (@CreateNewFolioIfNotExists= 1)
  begin
    SET @BillType = 0
    IF ISNULL(@FirstAddressNumber, 0) = 0
    BEGIN
      EXEC GetNextNumber 'ReservationAddressIdentity', 1, @AddressNumber OUTPUT
    END
    ELSE
      SET @AddressNumber = @FirstAddressNumber

    insert into ReservationAddress  (AddressNumber, AddressType, LastName, Phone) Values (@AddressNumber, @TempAddressType, 'Enter name', '99')
    IF NOT EXISTS (select Referencenumber FROM ReservationAddressXtab WITH (NOLOCK) WHERE ReferenceNumber = @ReferenceNumber AND GuestNumber = @GuestNumber AND AddressTypeNumber = @TempAddressType) -- AV 2006 0627
      insert into ReservationAddressXTab (ReferenceNumber, GuestNumber, AddressNumber, AddressTypeNumber) values (@ReferenceNumber, @GuestNumber, @AddressNumber, @TempAddressType)								    --------------------''''---------
  end
End -- if @TableReservationNumber = 0 END

SET @FolioNumber = -1

if @TableReservationNumber > 0
begin
  SET @TempAddressType = 1
  Select @TableNumber = ConferenceRoomNumber, @ChairNumber = ChairNumber from ReservationConferenceLine WITH (nolock) where ReservationConfLineNumber = @TableReservationNumber
  SET @TableNumber = ISNULL(@TableNumber, 0)
  SET @ChairNumber = ISNULL(@ChairNumber, 0)
end
else
begin
  SET @TableNumber = 0
  SET @ChairNumber = 0
end

if @TableReservationNumber = 0
  SELECT @FolioNumber = FolioNumber FROM FOLIOS WITH (NOLOCK) WHERE ReferenceNumber=@ReferenceNumber AND GuestNumber=@GuestNumber AND ReservationAddressNumber=@AddressNumber --AND OpenFolio=1
else
BEGIN
  SELECT @FolioNumber = FolioRestResTableChairsXTab.FolioNumber FROM FolioRestResTableChairsXTab WITH (NOLOCK)
  INNER JOIN Folios WITH (nolock) ON FolioRestResTableChairsXTab.FolioNumber = Folios.FolioNumber
  WHERE RestaurantReservationNumber = @TableReservationNumber

  -- If res. contains at least one table with bowling/bowling scheduled then all tables with resources type Table, 
  -- Table Scheduler, Bowling and Bowling Scheduler should be posted on same folio
  IF ISNULL(@FolioNumber, -1) = -1 AND @UseTableBooking = 1
  BEGIN  
    DECLARE @HallCollectGroup SMALLINT

    SELECT @HallCollectGroup = crt.CollectGroup
    FROM ConferenceRoomTypes crt WITH (NOLOCK)
    INNER JOIN ReservationConferenceLine rcl WITH (NOLOCK) ON rcl.BookedConferenceRoomTypeNumber = crt.ConferenceRoomTypeNumber
    AND rcl.ReservationConfLineNumber = @TableReservationNumber

    SET @HallCollectGroup = ISNULL(@HallCollectGroup, 0)

    IF @HallCollectGroup > 0
    BEGIN
      SET @TempConfLineNumber = NULL

      SELECT @FolioNumber = FolioRestResTableChairsXTab.FolioNumber, 
        @TableReservationNumber = ReservationConferenceLine.ReservationConfLineNumber,
        @TempConfLineNumber = ReservationConferenceLine.ReservationConfLineNumber,
        @TableNumber = ReservationConferenceLine.ConferenceRoomNumber, 
        @ChairNumber = ReservationConferenceLine.ChairNumber
      FROM ReservationConferenceLine WITH (NOLOCK)
      INNER JOIN ConferenceRoomTypes WITH (NOLOCK) ON ConferenceRoomTypes.ConferenceRoomTypeNumber = ReservationConferenceLine.BookedConferenceRoomTypeNumber
      LEFT JOIN FolioRestResTableChairsXTab WITH (NOLOCK) ON FolioRestResTableChairsXTab.RestaurantReservationNumber = ReservationConferenceLine.ReservationConfLineNumber
      WHERE ReservationConferenceLine.ReferenceNumber = @ReferenceNumber
      AND ReservationConferenceLine.Plenum = 1
      AND ConferenceRoomTypes.CollectGroup = @HallCollectGroup

      IF ISNULL(@FolioNumber, -1) = -1 AND ISNULL(@TempConfLineNumber, -1) = -1 
      BEGIN
        -- folio with CollectGroup does not exists
        SELECT TOP 1 @FolioNumber = FolioRestResTableChairsXTab.FolioNumber,
          @TableReservationNumber = ReservationConferenceLine.ReservationConfLineNumber,
          @TableNumber = ReservationConferenceLine.ConferenceRoomNumber, 
          @ChairNumber = ReservationConferenceLine.ChairNumber
        FROM ReservationConferenceLine WITH (NOLOCK)
        INNER JOIN ConferenceRoomTypes WITH (NOLOCK) ON ConferenceRoomTypes.ConferenceRoomTypeNumber = ReservationConferenceLine.BookedConferenceRoomTypeNumber
        LEFT JOIN FolioRestResTableChairsXTab WITH (NOLOCK) ON FolioRestResTableChairsXTab.RestaurantReservationNumber = ReservationConferenceLine.ReservationConfLineNumber
        WHERE ReservationConferenceLine.ReferenceNumber = @ReferenceNumber
        AND ConferenceRoomTypes.CollectGroup = @HallCollectGroup
        ORDER BY ReservationConferenceLine.ScreenLine 
      END
    END

    IF ISNULL(@FolioNumber, -1) = -1
      SET @FolioNumber = -1
    SET @TableNumber = ISNULL(@TableNumber, 0)
    SET @ChairNumber = ISNULL(@ChairNumber, 0)
  END
END

if (@FolioNumber=-1) AND (@CreateNewFolioIfNotExists= 1)
begin
  Declare @ServerDateTime DateTime
  Declare @CurrentDateStr Char(8)

  Select @CurrentDateStr = SYSTEMDATE FROM System WITH (NOLOCK)  WHERE NUMBER = 1  -- Only one record

  SET @ServerDateTime = CURRENT_TIMESTAMP

  if @BillType=0
    SET @BillType = @DefaultBillTypeHotelSetup

  DECLARE @HotelCustomerNumber INT
  SET @HotelCustomerNumber = -1
  SELECT TOP 1 @HotelCustomerNumber = (CASE WHEN dbo.GetHotelSetupKeyValue('CentralBookingType', -1) = 7 THEN ISNULL(CustomerNumber, -1) ELSE -1 END) 
  FROM Customer WITH (NOLOCK) INNER JOIN
    ReservationMain WITH (NOLOCK) ON Customer.HotelNumber > -1 AND Customer.HotelNumber = ReservationMain.HotelNumber
  WHERE ReservationMain.ReferenceNumber=@ReferenceNumber

  SELECT @ProjCode = FK_ProjectCode FROM ReservationMain WITH (NOLOCK) WHERE ReferenceNumber = @ReferenceNumber
  IF ISNULL(@TableReservationNumber, 0) = 0
    INSERT INTO FOLIOS (ReferenceNumber, GuestNumber, ReservationAddressNumber, OpenFolio, BillTypeNumber, HotelCustomerNumber, FkProjectCode) VALUES (@ReferenceNumber, @GuestNumber, @AddressNumber, 1, @BillType, @HotelCustomerNumber, @ProjCode)
  else
    INSERT INTO FOLIOS (OpenFolio, HotelCustomerNumber, FkProjectCode) VALUES (1, @HotelCustomerNumber, @ProjCode)
  SELECT @FolioNumber = SCOPE_IDENTITY()--@@IDENTITY
  IF ISNULL(@TableReservationNumber, 0) <> 0 AND ISNULL(@FolioNumber, 0) > 0
    INSERT INTO FolioRestResTableChairsXTab (FolioNumber, TableNumber, ChairNumber, RestaurantReservationNumber) VALUES (@FolioNumber, @TableNumber, @ChairNumber, @TableReservationNumber)
end

SET @AddressType=@TempAddressType

--SET lock_timeout 0
DECLARE @SqlStr VARCHAR(25)
SET @SqlStr = 'SET LOCK_TIMEOUT '+ CONVERT(VARCHAR, @LockTimeOut)
EXEC (@SqlStr)