getPrice

getPrice.sql

USE [RxDataSQL]
GO
/****** Object:  StoredProcedure [scriptassist].[getPrice]    Script Date: 12/16/2011 12:15:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [scriptassist].[getPrice]

	(
		--Price Table you are using
		@PriceTableID int,
		--Can be and is used as 0 on normal price calculations
		@SavedPriceTableID int,
		--From Script
		@AWP float,
		--From script
		@Cost float,
		--From Script only used if table is break on QTY
		@Qty float,
		--Dispensing Fee
		@Fee float OUTPUT,
		--Price
		@Price float OUTPUT,
		--Tells you if something isn't set up right in price tables
		@Error int OUTPUT,
		--Tells you if you can't calculate price from price table used
		@UseSavedPT bit OUTPUT,
		--I don't know why this is an output becuase it's the same as normal
		@SalesTaxBasis varchar(2) OUTPUT,
		--The amount of sales tax
		@SalesTaxRate float OUTPUT
	)

AS

/*
	ERROR NUMBERS
	0- No Error
	1- PriceTable and SavedPriceTable Not found
	2- No break points entered for price table
	3- Cost entered for calculation (AWP) was zero
	4- Cost entered for calculation (Cost) was zero
	5- BreakOnQty enabled and qty entered is zero
	6- Multiplier to be used is null or zero
*/

Declare @BreakOnQty bit
Declare @UseCost bit
Declare @FoundBreakPoint int
Declare @CostUsed float

Select TOP 1 
		@SalesTaxBasis = ISNULL(SalesTaxBasis,'2'),
		@SalesTaxRate = (ISNULL(SalesTax,0) / 100)
From Options

Set @Error = 0; 
Set @UseSavedPT = 0

If (Select Count(*) From PriceTable Where PriceTableID = @PriceTableID) > 0 
	BEGIN
		Print 'Found PriceTableID'
		If (Select Count(*) From PriceTableInfo Where PriceTableID = @PriceTableID) <= 0
			BEGIN
				Print 'Primary PriceTable has no break points entered'
				Set @Error = 2
				RETURN
			END
	END
Else
	BEGIN
		If (Select Count(*) From PriceTable Where PriceTableID = @SavedPriceTableID) <= 0
			BEGIN
				Print 'Saved or primary Price Table not found'
				Set @Error = 1
				RETURN
			END
		Else
			If (Select Count(*) From PriceTableInfo Where PriceTableID = @SavedPriceTableID) <= 0
				BEGIN
					Print 'Saved PriceTable has no break points entered'
					Set @Error = 2
					RETURN
				END
			Else
				BEGIN
					Print 'Using Saved price table'
					Set @PriceTableID = @SavedPriceTableID
					Set @UseSavedPT = 1
				END
	END
				
Select @BreakOnQty = ISNULL(BreakOnQty,0), @UseCost = ISNULL(UseCost,0) From PriceTable Where PriceTableID = @PriceTableID

Declare @PriceTableInfoID int
Declare @BreakPoint float
Declare @BreakPointEntered float

If @UseCost = 0
	If @AWP <= 0
		BEGIN
			Print 'AWP to be used was zero'
			Set @Error = 3
			RETURN
		END
	Else
		Set @CostUsed = @AWP
Else
	If @Cost <= 0
		BEGIN
			Print 'Cost to be used was zero'
			Set @Error = 4
			RETURN
		END
	Else
		Set @CostUsed = @Cost

If @BreakOnQty = 1 
	If @Qty <= 0
		BEGIN
			Print 'BreakOnQty enabled and Qty entered is zero'
			Set @Error = 5
			RETURN
		END
	Else
		Set @BreakPointEntered = @Qty
Else
	Set @BreakPointEntered = @CostUsed

--Here is the cursor to find the correct breakpoint
--Unlike RSRX1 I found I did not need to compare to previous breakpoint as we are going up
declare BreakPoints cursor scroll for
	Select PriceTableInfoID, BreakPoint From PriceTableInfo Where PriceTableID = @PriceTableID Order By BreakPoint

Open BreakPoints

Fetch First From BreakPoints Into @PriceTableInfoID, @BreakPoint

While @@fetch_status <> -1
begin
	If @BreakPointEntered < @BreakPoint
		BREAK
		
	Print @PriceTableInfoID
	Print @BreakPoint
	
fetch next
 from BreakPoints
 into @PriceTableInfoID, @BreakPoint

end

deallocate BreakPoints

Print 'Found Breakpoint'
Print @PriceTableInfoID
Print @BreakPoint

Declare @MarkUpMultiplier float
Declare @MarkUpAddend float

Select @MarkUpMultiplier = ISNULL(MarkUpMultiplier,0), @MarkUpAddend = ISNULL(MarkUpAddend,0) 
	From PriceTableInfo Where PriceTableInfoID = @PriceTableInfoID

If ISNULL(@MarkUpMultiplier,0) <= 0
	BEGIN
		Set @Error = 6
		Print 'MarkUpMultiplier is zero or null'
		RETURN
	END
	
Set @Fee = ISNULL(@MarkUpAddend,0)

Set @Price = (ISNULL(@MarkUpMultiplier,0) * ISNULL(@CostUsed,0)) + ISNULL(@MarkUpAddend,0)