Calculating Shipping and Tax

 

Introduction.  Now we will take a slight departure from our usual approach.  I am going to present two stored procedures, sp_GetShippingRate and sp_GetTaxRate.  But rather than developing an ASP that uses them, I am going to present two code snippets that will be used in the Payment.asp.  These snippets will be used to do the shipping and tax computations.  The Payment.asp will be presented in the next web page within the Payment.asp.

The Jerke book uses a COM based object developed in Visual Basic to do these calculations.  What you would do is create an ActiveX DLL in VB6 and then make an executable deployment with all of its dependencies and put it on the server.  This DLL, Dynamic Link Library, could then be accessed from ASPs on the server.

While this approach is very intelligent, I do not think we have the time for me to spend to develop it.

 

Stored Procedure Calling ASP

sp_GetShippingRate

sp_GetTaxRate

 Payment.asp

 

The Stored Procedures.  Since these are more familiar I will first list out the stored procedures that you should create using SQL and ASPs pretty much exactly like we have for the other stored procedures.  The first file should be called sp_GetShippingRate.asp.
<%@ Language=VBScript %>
<% Option Explicit %>
<!--#include File="adovbs.inc"-->

<%
' Open a connection to our SQL Server database
' We will use the ADO Driver connection
Dim connfoxFire, strSQLCreate
Set connfoxFire = Server.CreateObject("ADODB.Connection")
connfoxFire.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _
"Database=YourUserName;UID=cis; PWD=csatqu"

connfoxFire.Open

' Creating the SQL String to create the stored procedure
 strSQLCreate = "CREATE PROCEDURE sp_GetShippingRate AS " & _
"select * from shipping"

connfoxFire.execute(strSQLCreate)

connfoxFire.Close
Set connfoxFire = Nothing

Response.Write "<font size = 5>The SQL has executed</font>"

%>

 

While this actually returns all of the shipping rates and the corresponding upper and lower limits from the table, it is used as the basis of determining the exact shipping rate based on the number of CDs that have been ordered in TaxShip.asp.

The second file should be called sp_GetTaxRate.asp.
<%@ Language=VBScript %>
<% Option Explicit %>
<!--#include File="adovbs.inc"-->

<%
' Open a connection to our SQL Server database
' We will use the ADO Driver connection
Dim connfoxFire, strSQLCreate
Set connfoxFire = Server.CreateObject("ADODB.Connection")
connfoxFire.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _
"Database=YourUserName;UID=cis; PWD=csatqu"

connfoxFire.Open

' Creating the SQL String to create the stored procedure
 strSQLCreate = "CREATE PROCEDURE sp_GetTaxRate @chrState varchar(2) AS " & _
"select fltTaxRate from tax where chrState = @chrState"

connfoxFire.execute(strSQLCreate)

connfoxFire.Close
Set connfoxFire = Nothing

Response.Write "<font size = 5>The SQL has executed</font>"

%>

 

Like all of the other ASPs based on CREATE SQL commands, these files need to be uploaded to your web and then executed once.  After they have been executed, you should get an error if you try to execute them again because the stored procedures should already be there.  After these sp_name.asp files have been used they should be removed from your space on the server.

 

 

 

The Tax Code Snippet.  This snippet makes use of strState that contains the state of the shopper, and the intOrderTotal that contains the order total will be passed to the Tax snippet so that the tax can be obtained.
' This tax snippet will be used in Payment.asp

' Create an ADO database connection
set dbTax = server.createobject("adodb.connection")

' Create the record set
set rsTax = server.CreateObject("adodb.recordset")

' Open the DSN-less connection
dbTax.ConnectionString = "Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _
"Database=WildWillies;UID=cis; PWD=csatqu"

dbTax.Open

' Creat the SQL statement and pass in the
' state to get the appropriate rate
strSQL = "execute sp_GetTaxRate '" & strState & "'"

' Get the record set.
Set rsTax = dbTax.Execute(strSQL)

' See if a rate was returned.
If rsTax.EOF = False Then

' Set the amount of tax.
Tax = (rsTax("fltTaxRate") * intOrderTotal)

Else

' Return no tax.
Tax = 0

End If

In truth, there are only two states that charge sales tax on such purchases, VA at 4.5% and TX at 8%.

 

 

The Shipping Code Snippet.  This snippet makes use of intQuantity representing the number of items being ordered is passed to the Shipping function so that the shipping costs, based purely on quantity ordered in this situation, can be returned.  Often, the business logic associated with these computations is going to be more complex, but this approach still demonstrates a major approach to development.

' The snippet will calculate the appropriate shipping
' within Payment.asp.  The amount is based on the quantity of items in the order.

' Declare our variables.
Dim strSQL As String

' Create an ADO database connection
set dbShipping = server.createobject("adodb.connection")

' Create the record set
set rsShipping = server.CreateObject("adodb.recordset")

' Open the connection
dbShipping.ConnectionString = "Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _
"Database=WildWillies;UID=cis; PWD=csatqu"

dbShipping.Open

' Declare the SQL statement
strSQL = "execute sp_GetShippingRate"

' Retrieve the record set
Set rsShipping = dbShipping.Execute(strSQL)

' Start out with a shipping rate of $0
Shipping = 0

' Loop through the quantity settings
Do Until rsShipping.EOF

' Check to see if the quantity is between the current low and high values
If intQuantity >= rsShipping("intLowQuantity") And intQuantity <= rsShipping("intHighQuantity") Then

' If so set the shipping fee.
Shipping = rsShipping("intFee")

End If

' Move to the next row
rsShipping.MoveNext

Loop

 

The shipping computations are actually based on quantities and reflected in the following table.  With the way we have structured the code this could be easily enhanced.

 

Lower Limit Upper Limit Total Cost
1 item 10 items $2 for all
11 items 20 $4
21 30 $6
31 - $8