CREATE PROCEDURE [dbo].[CalcForecastArrangement] @ReferenceNumber int, @GuestNumber INT, @ADate char(8), @MISType int, @Total money output, @TotalVat money = 0 output AS
SET NOCOUNT ON

/*
  Changes: 
    KJ 20100215: Bug: Fix for "Extras without department not included when running all extras"
    KJ 20100128: Ext: Makeing it more secure to only include extras and not package linies with faulty department
    KJ 20070206: Cleanup
    KJ 20070305: Use FK_ExDepartment
    KJ xxxxxxxx: Added @GuestNumber to input param

*/

Declare @VatValueWareText varchar(5)

Declare @VatValueIndex0 Float
Declare @VatValueIndex1 Float
Declare @VatValueIndex2 Float
Declare @VatValueIndex3 Float
Declare @VatValueIndex4 Float
Declare @VatValueIndex5 Float

SET @VatValueIndex0 = 0

Select @VatValueWareText = KeyValue from HotelSetUp WITH (NOLOCK)  Where  SearchKey='VATValue1'
if (charindex(',',@VatValueWareText)>0)
  SET @VatValueWareText = STUFF(@VatValueWareText, charindex(',',@VatValueWareText) ,1, '.')

SET @VatValueIndex1 = CONVERT(Float,@VatValueWareText) 

Select @VatValueWareText = KeyValue from HotelSetUp WITH (NOLOCK)  Where  SearchKey='VATValue2'
if (charindex(',',@VatValueWareText)>0)
  SET @VatValueWareText = STUFF(@VatValueWareText, charindex(',',@VatValueWareText) ,1, '.')
SET @VatValueIndex2 = CONVERT(Float,@VatValueWareText) 

Select @VatValueWareText = KeyValue from HotelSetUp WITH (NOLOCK)  Where  SearchKey='VATValue3'
if (charindex(',',@VatValueWareText)>0)
  SET @VatValueWareText = STUFF(@VatValueWareText, charindex(',',@VatValueWareText) ,1, '.')
SET @VatValueIndex3 = CONVERT(Float,@VatValueWareText) 

Select @VatValueWareText = KeyValue from HotelSetUp WITH (NOLOCK)  Where  SearchKey='VATValue4'
if (charindex(',',@VatValueWareText)>0)
  SET @VatValueWareText = STUFF(@VatValueWareText, charindex(',',@VatValueWareText) ,1, '.')
SET @VatValueIndex4 = CONVERT(Float,@VatValueWareText) 

Select @VatValueWareText = KeyValue from HotelSetUp WITH (NOLOCK)  Where  SearchKey='VATValue5'
if (charindex(',',@VatValueWareText)>0)
  SET @VatValueWareText = STUFF(@VatValueWareText, charindex(',',@VatValueWareText) ,1, '.')
SET @VatValueIndex5 = CONVERT(Float,@VatValueWareText) 

if isnull(@VatValueIndex1,0) = 0  SET @VatValueIndex1 = 0
if isnull(@VatValueIndex2,0) = 0  SET @VatValueIndex2 = 0
if isnull(@VatValueIndex3,0) = 0  SET @VatValueIndex3 = 0
if isnull(@VatValueIndex4,0) = 0  SET @VatValueIndex4 = 0
if isnull(@VatValueIndex5,0) = 0  SET @VatValueIndex5 = 0

DECLARE @BudgetExtra INT
SELECT @BudgetExtra = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'BudgetExtra'
SET @BudgetExtra = ISNULL(@BudgetExtra, 1)

SELECT 
@Total = isnull(Sum(ArrangementLines.Pieces * ArrangementLines.Price),0),
@TotalVat = 
  isnull(Sum(
  CASE WHEN
    Accounts.VATValueNumber = 0  THEN 
      (ArrangementLines.Pieces * ArrangementLines.Price * ((1-(1/convert(float,((@VatValueIndex0/100)+1))))))  
    when Accounts.VATValueNumber = 1  THEN
      (ArrangementLines.Pieces * ArrangementLines.Price * ((1-(1/convert(float,((@VatValueIndex1/100)+1))))))  
    when Accounts.VATValueNumber = 2  THEN
      (ArrangementLines.Pieces * ArrangementLines.Price * ((1-(1/convert(float,((@VatValueIndex2/100)+1))))))  
    when Accounts.VATValueNumber = 3  THEN
      (ArrangementLines.Pieces * ArrangementLines.Price * ((1-(1/convert(float,((@VatValueIndex3/100)+1))))))  
    when Accounts.VATValueNumber = 4  THEN
      (ArrangementLines.Pieces * ArrangementLines.Price * ((1-(1/convert(float,((@VatValueIndex4/100)+1))))))  
    when Accounts.VATValueNumber = 5  THEN
      (ArrangementLines.Pieces * ArrangementLines.Price * ((1-(1/convert(float,((@VatValueIndex5/100)+1))))))  
  end), 0)
FROM ArrangementLines WITH (NOLOCK) 
inner join ArrangementReservationsXtab WITH (nolock) on ArrangementReservationsXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber 
  and referencenumber = @ReferenceNumber AND GuestNumber = @GuestNumber
inner join Ware WITH (NOLOCK) ON ArrangementLines.WareNumber = Ware.WareNumber
inner JOIN WareGroups WITH (Nolock) on WareGroups.WaregroupNumber = Ware.WareGroupNumber 
inner join Accounts WITH (NOLOCK) ON Accounts.AccountNumber = Ware.ResultAccountNumber
left outer join arrangementroomxtab WITH (nolock) on arrangementlines.arrangementLinenumber = arrangementroomxtab.arrangementLinenumber
where WareGroups.WareType = @MISType 
and ArrangementLines.Date = @ADate and arrangementroomxtab.arrangementLinenumber is null 
and ((@BudgetExtra = 0) OR (not ArrangementLines.FK_ExDepartment is null))
AND ISNULL(ArrangementLines.PacketItemNumber, 0) = 0
