CREATE PROCEDURE DBO.[DepositCalc] @ReferenceNumber int, @GuestNumber int, @newstatus int = 8 AS

/*
Changes:
 KJ 20140723: Ext/Bug: Removed unused paramaters. Case #CR074301
 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

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')
       
        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
  set @fup_counter = @fup_counter + 1
end -- while

select * from #deposits WITH (nolock)

DROP TABLE #Deposits

