CREATE PROCEDURE DBO.[DepositCalc] @ReferenceNumber int, @GuestNumber int, @newstatus int = 8, @APrepaidAmount MONEY, @AOnline BIT AS
/*
Changes:
 KJ 20241111: Exe: Added Room type for todo rules
 KJ 20240219: Ext: Added department for todo rules.
 KJ 20221006: Bug: Do not crate rule if amount is zero
 KJ 20220830: Bug: Fix for "Deposit rules in fixed amount (not percentage) not created". Case #CR222503
 KJ 20220713: Ext: Deposit rules only for online
 KJ 20220707: Added @APrepaidAmount. Used if amount has already been paid - used for online bookings.
 KJ 20170405: Ext: Rules per segment and hall type
 KJ 20140723: Ext/Bug: Removed unused paramaters. Case #CR074301
 nb 20140721 FolioIdentity discontinued
 KJ 20070411: Department rewrite
 KJ 20060705: Add a line to #deposit even if paymentdate is before systemdate
 KJ 20051102 : Fix for "General SQL error. Lock request time out period exceeded. Cannot insert the value NULL into column 'RuleAmount', table 'tempdb.dbo.#Deposits"
 17/08-2004 KS : Imp active rule
 12/03-2003 Jacob Pedersen
*/

DECLARE @depositrule INT 
DECLARE @systemdate DATETIME
DECLARE @date1 DATETIME
DECLARE @arrivaldate DATETIME
DECLARE @departuredate DATETIME
DECLARE @fup_counter INT
DECLARE @status INT
DECLARE @teststatus VARCHAR(50)
DECLARE @max_counter INT
DECLARE @totalprice FLOAT
DECLARE @extraprice FLOAT
DECLARE @amount FLOAT
DECLARE @transamount FLOAT
DECLARE @note VARCHAR(5000)
DECLARE @makeDeposit BIT
DECLARE @fee MONEY
DECLARE @RuleActive BIT
DECLARE @SegmentCSV VARCHAR(2000)
DECLARE @HallTypeCSV VARCHAR(2000)
DECLARE @RoomTypeCSV VARCHAR(2000)
DECLARE @cnt INT
DECLARE @OnlyOnline BIT
DECLARE @OnlineDepositTask BIT
DECLARE @DepartmentCSV VARCHAR(2000)

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

SET @fee = 0

SET @makedeposit = 0

CREATE TABLE #Deposits (
	ReferenceNumber INT,
	GuestNumber INT,
	RuleNumber INT,
	RuleDate DateTime,
	RuleAmount Float,
  RuleNote VARCHAR (5000) NULL,
  RuleDepartmentCSV VARCHAR(2000)
)
ON [PRIMARY]

set @max_counter = 10
set @fup_counter = 1
set @transamount = 0

set @teststatus = ''

if @newstatus = 8 
  select @status = status
  from reservations WITH (nolock) 
  where referencenumber = @referencenumber 
  and guestnumber = @guestnumber
else
  set @status = @newstatus

select @systemdate = Convert(datetime, systemdate , 112) from system WITH (nolock)
select @arrivaldate = convert(datetime, arrivaldate, 112) from reservations WITH (nolock) where referencenumber = @referencenumber and guestnumber = @guestnumber
select @departuredate = convert(datetime, departuredate, 112) from reservations WITH (nolock) where referencenumber = @referencenumber and guestnumber = @guestnumber

if @GuestNumber = 0
  exec ChargeAndCalcCancellationFee @referencenumber, @GuestNumber, @totalprice output, @fee output, 1, -1, -1, 1
else
  exec ChargeAndCalcCancellationFee @referencenumber, @GuestNumber, @totalprice output, @fee output, 1, -1, -1, 0

IF ISNULL(@APrepaidAmount, 0) > 0
  SET @totalprice = @totalprice - @APrepaidAmount

while @fup_counter <= @max_counter
begin
  select @RuleActive = Convert(Bit,keyvalue) from hotelsetup WITH (nolock) where searchkey like 'Deposit' + convert(varchar(2), @fup_counter) + 'Active'
  SELECT @OnlyOnline = CONVERT(BIT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'Deposit' + CONVERT(VARCHAR(2), @fup_counter) + 'OnlyOnline'
  SET @OnlyOnline = ISNULL(@OnlyOnline, 0)

  IF ISNULL(@RuleActive, 0) = 1 AND ((@OnlineDepositTask = 0) OR (@AOnline = @OnlyOnline))
  Begin   
    set @teststatus = '' 

    select @teststatus = keyvalue from hotelsetup WITH (nolock) where searchkey like 'Deposit' + convert(varchar(2), @fup_counter) + 'Status' and KeyValue like '%' + Convert(VarChar(2), @Status) + '%'

--    print '-------------'
--    print 'Rule       : ' + Convert(varchar, @fup_counter)      
    if @teststatus <> '' 
    begin
      select @depositrule = (select keyvalue from hotelsetup WITH (nolock) where searchkey like 'Deposit' + convert(varchar(2), @fup_counter) + 'DepositType')
      if @depositrule = '1' 
      begin
        select @Date1 =
        (
          --0 Create
          --1 Arrival
          --2 Departure
          Case (Select KeyValue From HotelSetup WITH (NOLOCK) where searchkey = 'Deposit' + convert(varchar(2), @fup_counter) + 'ComparedTo')
            when 0 then 
              (@systemdate + (select Convert(Int, keyvalue) from hotelsetup WITH (nolock) where searchkey = 'Deposit' + convert(varchar(2), @fup_counter) + 'Day'))
            when 1 then 
              (@arrivaldate - (select Convert(Int, keyvalue) from hotelsetup WITH (nolock) where searchkey = 'Deposit' + convert(varchar(2), @fup_counter) + 'Day'))
            when 2 then 
              (@departuredate + (select Convert(Int, keyvalue) from hotelsetup WITH (nolock) where searchkey = 'Deposit' + convert(varchar(2), @fup_counter) + 'Day'))
          end --case
        )
          Select @AMount = 
            Case (Select KeyValue From HotelSetup WITH (NOLOCK) where searchkey = 'Deposit' + convert(varchar(2), @fup_counter) + 'Procent')
              when 0 then 
                (select Convert(Int, keyvalue) from hotelsetup WITH (nolock) where searchkey = 'Deposit' + convert(varchar(2), @fup_counter) + 'Amount')
              when 1 then 
                ((@totalprice / 100) * (select Convert(Int, keyvalue) from hotelsetup WITH (nolock) where searchkey = 'Deposit' + convert(varchar(2), @fup_counter) + 'Amount'))
              else 
                'FEJL'
            End -- case

        select @note = (Select KeyValue From HotelSetup WITH (NOLOCK) where searchkey = 'Deposit' + convert(varchar(2), @fup_counter) + 'Text')
       
        SELECT @SegmentCSV = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'Deposit' + CONVERT(VARCHAR(2), @fup_counter) + 'SegmentCSV'
        SELECT @HallTypeCSV = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'Deposit' + CONVERT(VARCHAR(2), @fup_counter) + 'HallTypeCSV'
        SELECT @DepartmentCSV = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'Deposit' + CONVERT(VARCHAR(2), @fup_counter) + 'DepartmentCSV'
        SELECT @RoomTypeCSV = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'Deposit' + CONVERT(VARCHAR(2), @fup_counter) + 'RoomTypeCSV'

        SELECT @cnt = COUNT(*)
        FROM Reservations WITH (NOLOCK)
        LEFT OUTER JOIN ReservationConferenceLine WITH (NOLOCK) ON ReservationConferenceLine.ReferenceNumber = Reservations.ReferenceNumber
          AND ISNULL(ReservationConferenceLine.GuestNo, 0) = Reservations.GuestNumber
        LEFT OUTER JOIN ReservationRoomlineXTAB WITH (NOLOCK) ON ReservationRoomlineXTAB.ReferenceNumber = Reservations.ReferenceNumber
          AND ReservationRoomlineXTAB.ReferenceNumber = Reservations.ReferenceNumber
        LEFT OUTER JOIN ReservationRoomLine WITH (NOLOCK) ON ReservationRoomLine.LineNumber = ReservationRoomlineXTAB.LineNumber
        WHERE Reservations.ReferenceNumber = @ReferenceNumber
        AND Reservations.GuestNumber = @GuestNumber
        AND (ISNULL(@HallTypeCSV, '') = '' OR ReservationConferenceLine.BookedConferenceRoomTypeNumber IN (SELECT Number FROM dbo.iter_intlist_to_table_No_Dummy(@HallTypeCSV)))
        AND (ISNULL(@SegmentCSV, '') = '' OR Reservations.MarketSegment IN (SELECT Number FROM dbo.iter_intlist_to_table_No_Dummy(@SegmentCSV))) 
        AND (ISNULL(@RoomTypeCSV, '') = '' OR ReservationRoomLine.BookedTypeNumber IN (SELECT Number FROM dbo.iter_intlist_to_table_No_Dummy(@RoomTypeCSV))) 

        IF ISNULL(@cnt, 0) >= 1
        BEGIN
          if (@date1 < (Select Convert(DateTime, SystemDate, 112) from System WITH (nolock))) OR (@date1 <= (Select min(RuleDate) from #Deposits WITH (nolock)))
          begin
            if (Select Count (*) FROM #deposits WITH (nolock) WHERE ReferenceNumber <> -1) >= 1
              Update #deposits set RuleAmount = RuleAmount + @amount, RuleNumber = -1
            else
            BEGIN
              set @transamount = @amount       

              insert into #deposits (Referencenumber, GuestNumber, RuleNumber, RuleDate, RuleAmount, RuleNote, RuleDepartmentCSV) 
              values (-1, -1,-1, @date1, 0, 'Date before systemdate', @DepartmentCSV)
            END
          end
          else
          begin
            if @amount+@transamount+@fee > 0
            begin
              insert into #deposits (Referencenumber, GuestNumber, RuleNumber, RuleDate, RuleAmount, RuleNote, RuleDepartmentCSV) 
              values (@ReferenceNumber, @GuestNumber, @fup_counter, @date1, @amount+@transamount+@fee, @note, @DepartmentCSV)
            end
            set @transamount = 0
            set @fee = 0
          end
        end
      END
    end
  end
  set @fup_counter = @fup_counter + 1
end -- while

SELECT * 
FROM #deposits WITH (nolock)
WHERE RuleAmount <> 0

DROP TABLE #Deposits