CREATE PROCEDURE [dbo].[GetVatValueAndAccountNumbers] @DayProduct Bit, @GetVatFromNumber Smallint, @TypeNumber Integer, 
      @WareNumber Integer OUTPUT, @BalanceAccountNumber Integer OUTPUT, @ResultAccountNumber Integer OUTPUT, @VATAccountNumber Integer OUTPUT , @VatValue Float OUTPUT, @WareText VARChar(50) OUTPUT, 
	  @WareGroupNumber Integer = -1 OUTPUT, @WareType Integer = 99 OUTPUT AS
SET NOCOUNT ON

-- KJ 20211022: Bug: Fix for "Wrong ware/account used when nul/empty product used when posting by product type". Case #CR210022
-- SG 20200514: Added optional parameter @WareType
-- OF 20110815: WareGroupNumber added.
-- KJ 20051118: Postering på Segment eller POS forkert, hvis der er blevet "slettet" nogle Segementer eller POS.
-- KJ 20051010 MaxPax and DefaultPax deleted in select statement from ConferenceRoomTypes
-- Rettet d. 12/04-2005 af KS : Changed order by text to type when conferencerooms
-- Rettet d. 21/02-2005 af KS : Added 'Text' and sortnumber to all selects
-- Rettet d. 23/7-2004 af KS : Added 'Text' to order by if segments

-- Rettet d. 29/1-2003 : added producttype if null or empty 
-- Rettet d. 11/02-2003 : fixed problem with sortorder if product type
-- Rettet d. 11/06-2003 : removed order by if not producttype
-- Rettet d. 25/06-2003 : select * from segment,pos,room/hallstype because sortorder is different when only select a single field
-- 20040219: nb fast antal fields i cursor selects
-- Rettet d. 23/6-2004 af KS : Added order by to segments
-- Rettet d. 24/6-2004 af KS : Added markasdeleted for Segments/POS

Declare @PaymentType SmallInt
Declare @ProductTypeNumber Integer  -- Product number!!!!

if (@DayProduct=0)
  SET @PaymentType = 2
else
  SET @PaymentType = 3

if IsNull(@TypeNumber,-1)=-1 
begin
  if @GetVatFromNumber<>3 
    SET @TypeNumber = 0
  else
  begin
    if @TypeNumber = -1
      SELECT @ProductTypeNumber = CONVERT(INT,KEYVALUE) FROM HOTELSETUP WITH (NOLOCK) WHERE SEARCHKEY = 'NullProductType'
    else
      SELECT @ProductTypeNumber = CONVERT(INT,KEYVALUE) FROM HOTELSETUP WITH (NOLOCK) WHERE SEARCHKEY = 'EmptyProductType'

    IF @ProductTypeNumber = 0
      SELECT TOP 1 @ProductTypeNumber = ProductTypeNumber FROM ProductTypes WITH (NOLOCK) ORDER BY SortNumber, Text
  end 
end

Declare @Index Integer
SET @Index = 1

if (@TypeNumber>0) OR (IsNull(@TypeNumber,-1)=-1  AND @GetVatFromNumber=3)
begin
  Declare @ValueFound Bit

  Declare @SegmentNumber Integer
  Declare @POSNumber Integer
  Declare @RoomTypeNumber Integer

  Declare @VATValueNumber Integer

  Declare @VatValueText VARChar(6)

  Declare @RoomType varchar(10)

  Declare @Text char(50)
  Declare @Bit bit
  Declare @Int int

  SET @ValueFound = 0

  if (@GetVatFromNumber=0)  -- Get Vat From Segment
  begin
    Declare SegmentCur Cursor Fast_forward read_only For
      Select Number, Text, Description, MarkAsDeleted, SortNumber From Segments WITH (NOLOCK) where Number > 0 /*and ((MarkAsDeleted=0) OR (MarkAsDeleted is null))*/  ORDER BY SortNumber, Text

    Open SegmentCur

    Fetch Next  From SegmentCur Into @SegmentNumber,@Text, @Text, @Bit, @Int

    While ((@@Fetch_Status=0) and (@ValueFound = 0))
    begin
      if (@SegmentNumber <> @TypeNumber)
      begin
        SET @Index = @Index + 1

        Fetch Next From SegmentCur Into @SegmentNumber,@Text, @Text, @Bit, @Int
      end
      else
        SET @ValueFound = 1
    end

    Close SegmentCur
    DeAllocate SegmentCur
  end
  else
  if (@GetVatFromNumber=1) -- Get Vat From POS
  begin
    Declare POSCur Cursor Fast_forward read_only For
      Select Number, Text, Description, MarkAsDeleted, SortNumber From PointOfSales WITH (NOLOCK) where Number > 0 /*and ((MarkAsDeleted=0) OR (MarkAsDeleted is null))*/ ORDER BY SortNumber,text

    Open POSCur

    Fetch Next From POSCur Into @POSNumber,@Text, @Text, @Bit, @Int

    While ((@@Fetch_Status=0) and (@ValueFound = 0))
    begin
      if (@POSNumber <> @TypeNumber)
      begin
        SET @Index = @Index + 1

        Fetch Next From POSCur Into @POSNumber,@Text, @Text, @Bit, @Int
      end
      else
        SET @ValueFound = 1
    end

    Close POSCur
    DeAllocate POSCur
  end
  else
  if (@GetVatFromNumber=2)-- Get Vat From Roomtype
  begin
    if (@DayProduct=0)
    begin
      Declare RoomtypeCur  Cursor Fast_forward read_only For
      Select RoomtypeNumber, Type, Text, MaxPax, DefaultPax, UpgradeTypeNumber, SortNumber From RoomTypes WITH (NOLOCK) order by SortNumber,Text
    end
    else
    begin
      Declare RoomtypeCur Cursor Fast_forward read_only For
      Select ConferenceRoomTypeNumber, Type, Text, 1, 1 From ConferenceRoomTypes WITH (Nolock) order by SortNumber, Type
    end

    Open RoomtypeCur

    if (@DayProduct=0)
      Fetch Next From RoomtypeCur Into @RoomtypeNumber,@RoomType, @Text, @int, @int, @int, @int
    else
      Fetch Next From RoomtypeCur Into @RoomtypeNumber,@RoomType, @Text, @int, @int

    While ((@@Fetch_Status=0) and (@ValueFound = 0))
    begin
      if (@RoomtypeNumber <> @TypeNumber)
      begin
        SET @Index = @Index + 1

        if (@DayProduct=0)
          Fetch Next From RoomtypeCur Into @RoomtypeNumber,@RoomType, @Text, @int, @int, @int, @int
        else
          Fetch Next From RoomtypeCur Into @RoomtypeNumber,@RoomType, @Text, @int, @int
      end
      else
        SET @ValueFound = 1
    end
    Close RoomtypeCur
    DeAllocate RoomtypeCur
  end
  else
  if (@GetVatFromNumber=3)  -- Get Vat From Producttype/ProductNumber
  begin
    if @TypeNumber>0 
      Select @ProductTypeNumber = ProductTypeNumber From Products WITH (NOLOCK) Where ProductNumber = @TypeNumber

    SET @TypeNumber = @ProductTypeNumber

    Declare ProducttypeCur  Cursor Fast_forward read_only For
      Select ProductTypeNumber From ProductTypes WITH (Nolock) ORDER BY SortNumber, Text

    Open ProducttypeCur

    Fetch Next From ProducttypeCur Into @ProductTypeNumber

    While ((@@Fetch_Status=0) and (@ValueFound = 0))
    begin
      if (@ProductTypeNumber <> @TypeNumber)
      begin
        SET @Index = @Index + 1

        Fetch Next From ProducttypeCur Into @ProductTypeNumber
      end
      else
        SET @ValueFound = 1
    end

    Close ProducttypeCur
    DeAllocate ProducttypeCur
  end
  else
  if (@GetVatFromNumber = 4)
    SET @Index= @TypeNumber
end

Declare WareCur Cursor SCROLL read_only For
  SELECT -- Kontonummere til kontering af selve ophold minus pakker
    Ware.ResultAccountNumber,
    Ware.BalanceAccountNumber,
    Ware.WareNumber,
    Ware.WareName,
    WAREGROUPS.SortNumber,
	Ware.Type
  From WARE WITH (NOLOCK) 
    INNER JOIN WAREGROUPS WITH (NOLOCK) ON WARE.WareGroupNumber =  WAREGROUPS.WareGroupNumber
  WHERE (WAREGROUPS.Payment=@PaymentType) AND ((Ware.MarkAsDeleted is null)) --AND (CanBeDeleted=0) efter anmodning fra KA (skulle ikke betyde noget hvis system ikke er fucked up)
  Order By Ware.SortNumber

Open WareCur

if (@TypeNumber>0)
  Fetch ABSOLUTE @Index From WareCur Into  @ResultAccountNumber, @BalanceAccountNumber, @WareNumber, @WareText, @WareGroupNumber, @WareType
else
  Fetch LAST From WareCur Into  @ResultAccountNumber, @BalanceAccountNumber, @WareNumber, @WareText, @WareGroupNumber, @WareType

Close WareCur
DeAllocate WareCur

Select
  @VATValueNumber = VatValueNumber,  
  @VATAccountNumber = VATAccountNumber
From Accounts WITH (NOLOCK)  
Where Accounts.AccountNumber=@ResultAccountNumber

exec GetVatvalueFromVATValueNumber @VATValueNumber, @VatValue OUTPUT
