Created
December 16, 2011 19:26
-
-
Save msarchet/1487527 to your computer and use it in GitHub Desktop.
getPrice
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment