CREATE procedure [dbo].[MisStoreExtrasAndCalcDiscount] 
	@OrderBookBudgetCalcDiscount BIT, 
	@DiscountAsNettoPrice BIT, 
	@ChargeDiscountAsProduct BIT,
	@DiscountVatValueWare FLOAT,  --VAT rate for the discount
	@Room_HallType tinyint, 
	@ReferenceNumber int, 
	@FolioNumber int, 
	@DateStamp char(8), 
	@SegmentNumber int, 
	@PointOfSaleNumber int, 
	@RoomTypeNumber int, 
	@HotelCustomerNumber int, 
	@Status smallint, 
	@CampaignCode int, 
	@ProjectCode int, 
	@StaffID int, 
	@OwnerReservation bit, 
	@DiscountAccountNumber INT
AS
SET NOCOUNT ON

/*
  KJ 20150105: Bug: Fix for "Error when post of more than one pieces of a menu ware not in percent". Case #CR081859
    KJ 20140624: Ext: Menu ware split in whole amount. Req #1561
	SG 20130129, CR 051930: No discount was calculated when VAT=0
	OF 20110913: New for extras and calculating of discount
*/

Declare @VatValueWareText varchar(5)

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

Declare @Price money
Declare @VatOfPrice money
Declare @AccountNumber int
Declare @DepartmentNumber smallint
Declare @MisType int
Declare @WarePayment smallint
Declare @WareGroupNumber int
Declare @WareNumber int
Declare @GuestNumber int
Declare @ValueTypeExtra int
DECLARE @HandleAsMenuWare BIT
DECLARE @MenuVatValueNumber INT
DECLARE @MenuProcentage MONEY
DECLARE	@MenuWareResultAccountNumber INT
DECLARE @MenuWarePrice FLOAT
DECLARE @MenuWarePriceVat FLOAT
Declare @MenuVatValueWare FLOAT
DECLARE @InPercent BIT

DECLARE @AllDiscountRate FLOAT
DECLARE @DiscountRate FLOAT
DECLARE @AddressType INT
DECLARE @tmpPrice MONEY
DECLARE @DiscountPrice MONEY
DECLARE @DiscountPriceVat MONEY
DECLARE @SplitBillNumber INT
Declare @Text varchar(100)
Declare @ResultPriceVat MONEY
DECLARE @Pieces INT

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)

Declare ExtrasCur Cursor Fast_Forward READ_ONLY For
SELECT 
	isnull(ArrangementLines.Pieces * ArrangementLines.Price,0) as Total,
	isnull(
	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) as TotalVat,
	Accounts.AccountNumber,
	Case when ArrangementLines.FK_ExDepartment is null then isnull( 
			(SELECT TOP 1 PK_Department FROM Departments d WITH (NOLOCK)
				INNER JOIN ReservationMain r WITH (NOLOCK) ON d.HotelNumber = r.HotelNumber AND AutoCharge = 1 AND ISNULL(MarkAsDeleted, 0) = 0 AND ReferenceNumber = @ReferenceNumber), -1)
		else
			ArrangementLines.FK_ExDepartment
		end as DepartmentNumber,
	isnull(WareGroups.WareType, -1),
	isnull(WareGroups.Payment, -1),
	WareGroups.SortNumber as WareGroupNumber,
	Ware.WareNumber,
	ArrangementReservationsXtab.GuestNumber,
	ArrangementLines.Pieces
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 = @DateStamp and arrangementroomxtab.arrangementLinenumber is null 
and ((@BudgetExtra = 0) OR (not ArrangementLines.FK_ExDepartment is null))
AND ISNULL(ArrangementLines.PacketItemNumber, 0) = 0

Open ExtrasCur 

Fetch Next From ExtrasCur Into @Price, @VatOfPrice, @AccountNumber, @DepartmentNumber, @MisType, @WarePayment, @WaregroupNumber, @WareNumber, @GuestNumber, @Pieces

WHILE(@@FETCH_STATUS <> -1) 
Begin 
	Set @ValueTypeExtra = 0
	if @MisType = 1
	begin
		Set @ValueTypeExtra = 5
	end
	else if @MisType = 2
	begin
		Set @ValueTypeExtra = 4
	end
	else if @MisType = 0
	begin
		if @WarePayment = 7 -- ManuelRoom
		begin
			Set @ValueTypeExtra = 1	
		end
		else if @WarePayment = 8 -- ManuelHalls
		begin
			Set @ValueTypeExtra = 2
		end 
	end

/*
	SELECT @IsMenuWare = COUNT(*) FROM MenuWareXTab WHERE MenuNumber = @WareNumber
	IF @IsMenuWare = 1
	BEGIN
	  SELECT @SumPercentage = SUM(mw.procentage)
	  FROM dbo.ware w WITH (NOLOCK) 
	  INNER JOIN dbo.MenuWareXTab mw WITH (NOLOCK) ON w.warenumber = mw.warenumber 
	  AND mw.menunumber = @WareNumber

	  IF @SumPercentage <> 100
		SET @HandleAsMenuWare = 0
	  ELSE
		SET @HandleAsMenuWare = 1
	END
	ELSE
	  SET @HandleAsMenuWare = 0
*/
    EXEC WareIsMenuWare @WareNumber, @Price, @Pieces, @HandleAsMenuWare OUTPUT

	IF @HandleAsMenuWare = 1
	BEGIN
	  -- get the menuwares
	  DECLARE db_menuware_cur CURSOR STATIC FOR
		SELECT mw.Procentage, Accounts.VatValueNumber, w.ResultAccountNumber, wg.SortNumber AS WaregroupNumber, ISNUll(MenuWare.InPercent, 0)
		FROM Ware w WITH (NOLOCK)          
		INNER JOIN MenuWareXTab mw WITH (NOLOCK) ON w.WareNumber = mw.WareNumber AND mw.MenuNumber = @WareNumber
		INNER JOIN WareGroups wg WITH (NOLOCK) ON wg.WareGroupNumber = w.WareGroupNumber
		INNER JOIN ACCOUNTS WITH (NOLOCK) ON w.ResultAccountNumber = Accounts.AccountNumber
		INNER JOIN MenuWare WITH (NOLOCK) ON MenuWare.FK_MenuNumber = mw.MenuNumber
	  OPEN db_menuware_cur

	  FETCH NEXT FROM db_menuware_cur INTO @MenuProcentage, @MenuVatValueNumber, @MenuWareResultAccountNumber, @WareGroupNumber, @InPercent
	  
	  WHILE @@FETCH_STATUS = 0
	  BEGIN
		EXEC GetVatvalueFromVATValueNumber @MenuVatValueNumber, @MenuVatValueWare OUTPUT
		IF @InPercent = 1
          SET @MenuWarePrice = ((@Price*@MenuProcentage)/100.0)
		ELSE
          SET @MenuWarePrice = @MenuProcentage

		IF @OrderBookBudgetCalcDiscount = 1
		BEGIN
		  SET @AllDiscountRate = 0
		  EXEC FindFolioNumber @ReferenceNumber, @GuestNumber, 0, @WareNumber, @SplitbillNumber, 0, @FolioNumber OUTPUT, @AddressType OUTPUT

		  EXEC GetDiscountRate @ReferenceNumber, @GuestNumber, @AddressType, 0, @AllDiscountRate OUTPUT

		  IF @AllDiscountRate>0
		  BEGIN
			IF @DiscountAsNettoPrice=0
			BEGIN
			  SET @DiscountPrice = Round((@MenuWarePrice*(@AllDiscountRate/100)),2)*-1
			  SET @DiscountPriceVat = (@DiscountPrice*(1-(1/((@DiscountVatValueWare/100)+1))))
			  
			  IF @ChargeDiscountAsProduct = 0
			  begin	
				INSERT INTO MISTABLE (Room_HallType, ReferenceNumber, GuestNumber, FolioNumber, DateStamp, SegmentNumber, PointOfSaleNumber, RoomTypeNumber, BookedAdult, BookedChildren1, BookedChildren2, BookedNumberOfRooms, BookedPrice, BookedVatOfPrice, BookedPackage, BookedVatOfPackage, ActualAdult, ActualChildren1, ActualChildren2, ActualNumberOfRooms, ActualPrice, ActualVatOfPrice, ActualPackage, ActualVatOfPackage, ValueType, HotelCustomerNumber, ReservationStatus, CampaignCode, ProjectCode, StaffID, WareGroupNumber, AccountNumber, DepartmentNumber, Text, OwnerReservation) 
				VALUES (@Room_HallType, @referencenumber, @GuestNumber, -1, @DateStamp, @SegmentNumber, @PointOfSaleNumber, @RoomTypeNumber, 0, 0, 0, 0, @DiscountPrice, @DiscountPriceVat, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, @ValueTypeExtra, @HotelCustomerNumber, @Status, @CampaignCode, @ProjectCode, @StaffID, @WareGroupNumber, @DiscountAccountNumber, @DepartmentNumber, '', @OwnerReservation) 
			  end
			  ELSE
			  begin
				INSERT INTO MISTABLE (Room_HallType, ReferenceNumber, GuestNumber, FolioNumber, DateStamp, SegmentNumber, PointOfSaleNumber, RoomTypeNumber, BookedAdult, BookedChildren1, BookedChildren2, BookedNumberOfRooms, BookedPrice, BookedVatOfPrice, BookedPackage, BookedVatOfPackage, ActualAdult, ActualChildren1, ActualChildren2, ActualNumberOfRooms, ActualPrice, ActualVatOfPrice, ActualPackage, ActualVatOfPackage, ValueType, HotelCustomerNumber, ReservationStatus, CampaignCode, ProjectCode, StaffID, WareGroupNumber, AccountNumber, DepartmentNumber, Text, OwnerReservation) 
				VALUES (@Room_HallType, @referencenumber, @GuestNumber, -1, @DateStamp, @SegmentNumber, @PointOfSaleNumber, @RoomTypeNumber, 0, 0, 0, 0, @DiscountPrice, @DiscountPriceVat, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, @ValueTypeExtra, @HotelCustomerNumber, @Status, @CampaignCode, @ProjectCode, @StaffID, @WareGroupNumber, @MenuWareResultAccountNumber, @DepartmentNumber, '', @OwnerReservation) 
			  end
			END
			ELSE
			  SET @MenuWarePrice = @MenuWarePrice-(Round((@MenuWarePrice*(@AllDiscountRate/100)),2))
		  END
		END   

	  	SET @MenuWarePriceVat = (@MenuWarePrice*(1-(1/((@DiscountVatValueWare/100)+1))))
		INSERT INTO MISTABLE (Room_HallType, ReferenceNumber, GuestNumber, FolioNumber, DateStamp, SegmentNumber, PointOfSaleNumber, RoomTypeNumber, BookedAdult, BookedChildren1, BookedChildren2, BookedNumberOfRooms, BookedPrice, BookedVatOfPrice, BookedPackage, BookedVatOfPackage, ActualAdult, ActualChildren1, ActualChildren2, ActualNumberOfRooms, ActualPrice, ActualVatOfPrice, ActualPackage, ActualVatOfPackage, ValueType, HotelCustomerNumber, ReservationStatus, CampaignCode, ProjectCode, StaffID, WareGroupNumber, AccountNumber, DepartmentNumber, Text, OwnerReservation) 
		VALUES (@Room_HallType, @referencenumber, @GuestNumber, -1, @DateStamp, @SegmentNumber, @PointOfSaleNumber, @RoomTypeNumber, 0, 0, 0, 0, @MenuWarePrice, @MenuWarePriceVat, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, @ValueTypeExtra, @HotelCustomerNumber, @Status, @CampaignCode, @ProjectCode, @StaffID, @WareGroupNumber, @MenuWareResultAccountNumber, @DepartmentNumber, '', @OwnerReservation) 
	    
		FETCH NEXT FROM db_menuware_cur INTO @MenuProcentage, @MenuVatValueNumber, @MenuWareResultAccountNumber, @WareGroupNumber, @InPercent
	  END  
	  CLOSE db_menuware_cur
	  DEALLOCATE db_menuware_cur
	END
	ELSE
	BEGIN
	  -- Calc Discount
	  IF @OrderBookBudgetCalcDiscount = 1
	  BEGIN
		SET @AllDiscountRate = 0
		EXEC FindFolioNumber @ReferenceNumber, @GuestNumber, 0, @WareNumber, @SplitbillNumber, 0, @FolioNumber OUTPUT, @AddressType OUTPUT

		EXEC GetDiscountRate @ReferenceNumber, @GuestNumber, @AddressType, 0, @AllDiscountRate OUTPUT

		IF @AllDiscountRate>0
		BEGIN
		  IF @DiscountAsNettoPrice=0
		  BEGIN
			SET @DiscountPrice = Round((@Price*(@AllDiscountRate/100)),2)*-1
		  	SET @DiscountPriceVat = (@DiscountPrice*(1-(1/((@DiscountVatValueWare/100)+1))))

			IF @ChargeDiscountAsProduct = 0
			BEGIN
				INSERT INTO MISTABLE (Room_HallType, ReferenceNumber, GuestNumber, FolioNumber, DateStamp, SegmentNumber, PointOfSaleNumber, RoomTypeNumber, BookedAdult, BookedChildren1, BookedChildren2, BookedNumberOfRooms, BookedPrice, BookedVatOfPrice, BookedPackage, BookedVatOfPackage, ActualAdult, ActualChildren1, ActualChildren2, ActualNumberOfRooms, ActualPrice, ActualVatOfPrice, ActualPackage, ActualVatOfPackage, ValueType, HotelCustomerNumber, ReservationStatus, CampaignCode, ProjectCode, StaffID, WareGroupNumber, AccountNumber, DepartmentNumber, Text, OwnerReservation) 
				VALUES (@Room_HallType, @referencenumber, @GuestNumber, -1, @DateStamp, @SegmentNumber, @PointOfSaleNumber, @RoomTypeNumber, 0, 0, 0, 0, @DiscountPrice, @DiscountPriceVat, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, @ValueTypeExtra, @HotelCustomerNumber, @Status, @CampaignCode, @ProjectCode, @StaffID, @WareGroupNumber, @DiscountAccountNumber, @DepartmentNumber, '', @OwnerReservation) 
			END
			ELSE
			BEGIN
				INSERT INTO MISTABLE (Room_HallType, ReferenceNumber, GuestNumber, FolioNumber, DateStamp, SegmentNumber, PointOfSaleNumber, RoomTypeNumber, BookedAdult, BookedChildren1, BookedChildren2, BookedNumberOfRooms, BookedPrice, BookedVatOfPrice, BookedPackage, BookedVatOfPackage, ActualAdult, ActualChildren1, ActualChildren2, ActualNumberOfRooms, ActualPrice, ActualVatOfPrice, ActualPackage, ActualVatOfPackage, ValueType, HotelCustomerNumber, ReservationStatus, CampaignCode, ProjectCode, StaffID, WareGroupNumber, AccountNumber, DepartmentNumber, Text, OwnerReservation) 
				VALUES (@Room_HallType, @referencenumber, @GuestNumber, -1, @DateStamp, @SegmentNumber, @PointOfSaleNumber, @RoomTypeNumber, 0, 0, 0, 0, @DiscountPrice, @DiscountPriceVat, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, @ValueTypeExtra, @HotelCustomerNumber, @Status, @CampaignCode, @ProjectCode, @StaffID, @WareGroupNumber, @AccountNumber, @DepartmentNumber, '', @OwnerReservation) 
			END
		  END
		  ELSE
			SET @Price = @Price-(Round((@Price*(@AllDiscountRate/100)),2))
		END
	  END  
	  -- Calc Discount END

 	 INSERT INTO MISTABLE (Room_HallType, ReferenceNumber, GuestNumber, FolioNumber, DateStamp, SegmentNumber, PointOfSaleNumber, RoomTypeNumber, BookedAdult, BookedChildren1, BookedChildren2, BookedNumberOfRooms, BookedPrice, BookedVatOfPrice, BookedPackage, BookedVatOfPackage, ActualAdult, ActualChildren1, ActualChildren2, ActualNumberOfRooms, ActualPrice, ActualVatOfPrice, ActualPackage, ActualVatOfPackage, ValueType, HotelCustomerNumber, ReservationStatus, CampaignCode, ProjectCode, StaffID, WareGroupNumber, AccountNumber, DepartmentNumber, Text, OwnerReservation) 
	 VALUES (@Room_HallType, @referencenumber, @GuestNumber, -1, @DateStamp, @SegmentNumber, @PointOfSaleNumber, @RoomTypeNumber, 0, 0, 0, 0, @Price, @VatOfPrice, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, @ValueTypeExtra, @HotelCustomerNumber, @Status, @CampaignCode, @ProjectCode, @StaffID, @WareGroupNumber, @AccountNumber, @DepartmentNumber, '', @OwnerReservation) 
	END


	Fetch Next From ExtrasCur Into @Price, @VatOfPrice, @AccountNumber, @DepartmentNumber, @MisType, @WarePayment, @WaregroupNumber, @WareNumber, @GuestNumber, @Pieces
end
Close ExtrasCur 
Deallocate ExtrasCur

