CREATE PROCEDURE DBO.[DepositCalc] @ReferenceNumber int, @GuestNumber int, @newstatus int = 8 AS
/*
Changes:
 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 @cnt INT

SET @fee = 0

SET @makedeposit = 0

CREATE TABLE #Deposits (
	ReferenceNumber INT,
	GuestNumber INT,
	RuleNumber INT,
	RuleDate DateTime,
	RuleAmount Float,
  RuleNote VARCHAR (5000) NULL)
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

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'
  if isnull(@RuleActive,0) = 0 SET @RuleActive = 0

  if @RuleActive = 1
  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 @cnt = COUNT(*)
        FROM Reservations WITH (NOLOCK)
        LEFT OUTER JOIN ReservationConferenceLine WITH (NOLOCK) ON ReservationConferenceLine.ReferenceNumber = Reservations.ReferenceNumber
          AND ISNULL(ReservationConferenceLine.GuestNo, 0) = Reservations.GuestNumber
        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))) 

        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) 
              values (-1, -1,-1, @date1, 0, 'Date before systemdate')
            END
          end
          else
          begin
            if @amount+@transamount+@fee > 0
            begin
              insert into #deposits (Referencenumber, GuestNumber, RuleNumber, RuleDate, RuleAmount, RuleNote) 
              values (@ReferenceNumber, @GuestNumber, @fup_counter, @date1, @amount+@transamount+@fee, @note)
            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)

DROP TABLE #Deposits