During a recent freelance engagement I stumbled upon an interesting challenge:
This was for an eCommerce website based on the popular AspDotNetStorefront package. ASPDNSF is a capable piece of software that allows for a multitude of customization options to tailor the website to operator needs. This particular eCommerce operation was based out of New York state and their requirements were the accurate calculation of sales tax for all orders that are to be shipped to NY. The ASPDNSF package allows for customization of tax rates at various geographical levels such as State, Region and ZipCode. For New York State however the zipcode is not granular enough to guarantee an uniform tax rate for all adresses that fall within. So 2 different addresses that happen to be in the same zipcode can possibly fall under different tax jurisdictions with different applicable rates.
Fortunately there are online vendors that exist that do precisely this sort of thing (for a fee of course). One such vendor is CCH. CCH publishes a webservice that makes it possible to perform various tax calculations based on specific adress information.
I'll describe a way to integrate an online realtime tax rate lookup system with no code changes to the ASPDNSF package. This solution will work for those installations that use Sql Server 2005/2008 as their data store.
We begin by creating a new DLL project in Visual Studio 2008 (the C# Express 2008 version works just fine as well) and call it CCHTaxRateLookup.
Add a Web Reference to https://webservices.esalestax.net/CertiTAX.NET/CertiCalc.asmx?WSDL and rename the Web reference name to : CertiTAX.NET.SalesTaxOnlineCalc
add the following code
using System;
using System.Data.SqlTypes;
using CCHTaxRateLookup.CertiTAX.NET.SalesTaxOnlineCalc;
using Microsoft.SqlServer.Server;
public class UserDefinedFunctions
{
[SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = true)]
public static SqlDouble CCHTaxRateLookup(string address1, string address2,
string city, string zipCode,
string merchantKey, string sku)
{
var address = new Address
{
Street1 = address1,
Street2 = address2,
City = city,
PostalCode = zipCode,
Nation = "USA"
};
var order = new Order
{
Address = address,
Nexus = "POSH",
SerialNumber = merchantKey,
CalculateTax = true,
ConfirmAddress = false,
LineItems = new OrderLineItem[1]
};
var li = new OrderLineItem
{
ItemId = "Item1",
Quantity = 1,
ExtendedPrice = Decimal.Parse("10000.00"),
StockingUnit = sku
};
order.LineItems[0] = li;
try
{
var calc = new CertiCalc();
TaxTransaction tx = calc.Calculate(order);
return new SqlDouble(Convert.ToDouble(tx.TotalTax)/100);
}
catch
{
return new SqlDouble(0.00);
}
}
}
This little snippet creates a “dummy” order with a single line item and submits it to the webservice for determining the proper tax rate. The price for the item is set to $10000.00 which will give us a 3 digit accuracy for expressing the resulting tax rate. You may want to setup a dummy taxable SKU with the web service before hand to ensure the item will get taxed. This particular eCommerce site had a very small number of SKU’s they sell online making it easier to setup a single rate lookup and use the built-in tax calculation functionality in ASPDNSF.
Compile the DLL and register the assembly with Sql Server. Since we have a Web Reference there will also be a CCHTaxRateLookup.XmlSerializers.dll located in the build directory, this DLL will also be required registered with Sql Server.
Note: you may also need to set the ‘clr enabled’ option on the server and mark your ASPDNSF database as TRUSTWORTHY if your database is not setup to host assemblies.
Now that we have the assembly registered we need to create a function that points to our SQLCLR so that we can call it using T-SQL.
USE [ASPDNSFDatabase]
GO
CREATE FUNCTION [dbo].[CCHTaxRateLookup](@address1 [nvarchar](200), @address2 [nvarchar](200), @city [nvarchar](50), @zipCode [nvarchar](10), @merchantKey [nvarchar](50), @sku [nvarchar](30))
RETURNS [float] WITH EXECUTE AS CALLER
AS
: none; padding: 0px;">EXTERNAL NAME [CCHTaxRateLookup].[UserDefinedFunctions].[CCHTaxRateLookup]
0px;">GO
--------
At this point you should be able to test the function using Query Analyzer.
Notice the 2 different tax rates returned for 2 addresses that happen to be in the same zipcode.
With all this plumbing in place we are now ready to inject our enhanced sales tax rate information directly into ASPDNSF. The key point of injection is a stored procedure called aspdnsf_GetCartSubTotalAndTax. Before we move forward with this its useful to remember that this stored proc gets called an awful lot and reaching out to a webservice from SQLCLR for each call can be a rather costly operation. One solution to speed up the lookups is to create a separate lookup table that associates a particular AddressId with the proper taxrate.
CREATE TABLE [dbo].[AddressTaxRate](
[AddressID] [int] NOT NULL,
[TaxRate] [float] NOT NULL
) ON [PRIMARY]
GO
This will allow us to cache the taxrate lookups for a given address and not hit the expensive web service everytime the procedure is called. ( and it gets called a lot ).
With all this in place we are ready to modify the stored aspdnsf_GetCartSubTotalAndTax procedure as follows :
{...}
-- Calculate item Tax
declare @vatroundingmethod int
select @vatroundingmethod = case configvalue when 'true' then 1 else 0 end from dbo.appconfig with (nolock) where [name] = 'VAT.RoundPerItem'
-- these are the variables we'll need to populate
DECLARE @TAXRATE FLOAT;
DECLARE @address1 VARCHAR(200);
DECLARE @address2 VARCHAR(200);
DECLARE @state2 VARCHAR(20);
DECLARE @city VARCHAR(50);
DECLARE @zip VARCHAR(10);
DECLARE @ShipAddressID INT;
-- lookup the shipping address and initialize the variables
SELECT @address1 = a.Address1, @address2 = a.Address2, @city = a.City, @zip = a.Zip, @state2 = a.State,
@ShipAddressID = a.AddressID
FROM Customer c JOIN Address a ON c.ShippingAddressID = a.AddressID
WHERE c.CustomerID = @customerid
-- we only do this for NY
IF @state2 = 'NY'
BEGIN
-- have we seen this address before ?
IF EXISTS (SELECT TaxRate FROM AddressTaxRate WHERE AddressId = @ShipAddressID)
BEGIN
SELECT @TAXRATE = TaxRate FROM AddressTaxRate WHERE AddressId = @ShipAddressID
END
ELSE
BEGIN
-- no, make the webservice call and find the tax rate
SELECT @TAXRATE = dbo.CCHTaxRateLookup(@address1, @address2, @city, @zip, 'YOUR_KEY_HERE', 'SKU2')
-- make a note of it for subsequent calls
INSERT INTO AddressTaxRate (AddressId, TaxRate) VALUES (@ShipAddressID, @TAXRATE)
END
END
ELSE
BEGIN
SELECT @TAXRATE = 0
END
-- apply the taxrate on all taxable items in shopping cart
UPDATE @tblsubtotal
SET Tax = CASE sc.IsTaxable
WHEN 1 THEN
case @vatroundingmethod
when 1 then round(FinalDiscountedPrice*((isnull(cr.taxrate, 0)+isnull(sr.taxrate, 0)+isnull(zr.taxrate, 0))/100), 2)*Qty
else round((FinalDiscountedPrice*Qty)*(@TAXRATE/100), 2)
end
ELSE 0
END
{...}
You may also wish to move the webservice call out of the proc altogether and place it inside an UPDATE/INSERT trigger on the Address table as a further optimization.
Useful links : The New York State Department of Taxation and Finance makes available this particular page to perform address based sales tax rate lookups on an ad-hoc basis.