<%@ Language=VBScript %>
<%
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'document title: sales.asp '
'created by: Don Demrow '
'function: to show sales data depending on query or default conditions '
' '
'created: 20050430 '
' '
' 20050501 added state arrays and loops. '
' 20050503 added check / uncheck functions. '
' 20050506 restructured application with VBScript functions. '
' 20050509 Changed "Submit" button appearance to make it stand out. '
' '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
%>
<%
Option Explicit ' force all variables to be declared.
Response.Buffer = True ' get everything before displaying on the client.
'This function will determine whether to use an "AND" in the query, or multiples of "OR"
function decideAndOr()
dim loopCount1 'declare loopCount to count how many multiples of "OR" to use in query
loopCount1 = 0 'initialize loopCount to zero.
do while loopCount1 < stateArrayLength
if eval(stateArray(loopCount1)) = 1 then
includedStateCount = includedStateCount + 1
if includedStateCount > 1 then
queryAndOr = " OR "
else
queryAndOr = " AND "
end if
salesQuery = salesQuery & queryAndOr & "salesState = '" & stateNameArray(loopCount1) & "'"
end if
loopCount1 = loopCount1 + 1
Loop
End function
'This function will write the "Sales by State" table.
function WriteSalesByStateTable()
dim spacerTD
spacerTD = Chr(9) & vbcrlf & "
")
end function
dim page, referringPage' declare variables used.
'get referring page name from the URL query string OR form. (One will be present, but not both.)
referringPage = Request.QueryString("referringPage") & Request.Form("referringPage")
if referringPage = "" then ' if no name is present,
referringPage = "Sales Totals" ' set a default referring page.
end if
'get page name from the URL query string OR form. (One will be present, but not both.)
page = Request.QueryString("page") & Request.Form("referringPage")
if page = "" then ' if no name is present,
page = "Sales by State" ' set a default page.
end if
'begin building the query - we'll add to it in a bit.
salesQuery = "SELECT salesCompany, salesState, salesAmount, salesDate FROM salesData WHERE 1 = 1"
dim showall, includedStateCount, queryAndOr, salesQuery ' for query builder
'get total sales by state, and total them.
dim allFLsales, allGAsales, allKYsales, allTXsales, allWIsales, salesByState, aggregateSales
salesByState = array("allFLsales", "allGAsales", "allKYsales", "allTXsales", "allWIsales")
allFLsales = 0
allGAsales = 0
allKYsales = 0
allTXsales = 0
allWIsales = 0
aggregateSales = 0
' State name declarations and array
dim Florida, Georgia, Kentucky, Texas, Wisconsin, stateNameArray
stateNameArray = array("Florida", "Georgia", "Kentucky", "Texas", "Wisconsin")
'State abbreviations declared, and array.
dim FL, GA, KY, TX, WI, IL, stateArray, stateArrayLength
stateArray = array("FL", "GA", "KY", "TX", "WI")
'Get the array Length.
stateArrayLength = (ubound(stateArray) + 1) ' "UBOUND" starts counting at ZERO, so add 1.
FL = request.querystring("inclFL")
if FL = "" then
FL = 0
end if
GA = request.querystring("inclGA")
if GA = "" then
GA = 0
end if
KY = request.querystring("inclKY")
if KY = "" then
KY = 0
end if
TX = request.querystring("inclTX")
if TX = "" then
TX = 0
end if
WI = request.querystring("inclWI")
if WI = "" then
WI = 0
end if
showall = request.querystring("showall")
if showall = "" then
showall = 0
end if
if showall = 1 then ' if showall is true, then check all the check boxes. This is default on page open.
FL = 1
GA = 1
KY = 1
TX = 1
WI = 1
end if
includedStateCount = 0 ' initialize so we can increment it if there's more than one state for the query
Call decideAndOr() 'Call filter conditions for query.
'prepare the "ORDER BY" portion of the query and initialize it
dim orderBy
if request.querystring("orderBy") = "" then
'Add "ID" on the line below, so "State" column is uniform in appearance (blue link) in initial view.
orderBy = "salesState, ID"
else
orderBy = request.querystring("orderBy")
end if
salesQuery = salesQuery & " ORDER BY " & orderBy' append "ORDER BY" onto the rest of the query.
%>
<%
' Make connection to the DB, and now we can use our query.
dim databaseConnection, MdbFilePath, SalesQueryRecordset
set databaseConnection = Server.createobject("ADODB.Connection")
MdbFilePath = Server.MapPath("asgSales.mdb")
databaseConnection.open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & MdbFilePath & ";"
set SalesQueryRecordset = Server.CreateObject("ADODB.Recordset")
SalesQueryRecordset.Open salesQuery, databaseConnection, 3
%>
ASG - <%=page%>
<%if (SalesQueryRecordset.BOF and SalesQueryRecordset.EOF) then%>
<%if page = "Sales by State" then%>
There are currently no records that match your query.
<%else%>
There are no sales data available for the state
or states you have selected.
<%end if%>
<%else%>
<%if page = "Sales by State" then%>
<%
dim orderByString
orderByString = replace(replace(orderBy, "ORDER BY", ""), "sales", "")
%>
Sales Data - ordered by <%=orderByString%>
<%
dim queryStringParam
queryStringParam = "page=" & escape(page) & "&referringPage=" & escape(referringPage) & "&inclFL=" & FL & "&inclGA=" & GA & "&inclKY=" & KY & "&inclTX=" & TX & "&inclWI=" & WI & "&showall=" & showall
%>
<%end if%>
<%
dim loopCount
loopCount = 0 ' used for MOD function to do alternating color
tags.
SalesQueryRecordset.MoveFirst
do while NOT SalesQueryRecordset.EOF
loopCount = loopCount + 1 ' increment loopCount for alternating
bgcolor attribute.
'add up all sales by states
if SalesQueryRecordset("salesState") = "Florida" then
allFLsales = allFLsales + SalesQueryRecordset("salesAmount")
end if
if SalesQueryRecordset("salesState") = "Georgia" then
allGAsales = allGAsales + SalesQueryRecordset("salesAmount")
end if
if SalesQueryRecordset("salesState") = "Kentucky" then
allKYsales = allKYsales + SalesQueryRecordset("salesAmount")
end if
if SalesQueryRecordset("salesState") = "Texas" then
allTXsales = allTXsales + SalesQueryRecordset("salesAmount")
end if
if SalesQueryRecordset("salesState") = "Wisconsin" then
allWIsales = allWIsales + SalesQueryRecordset("salesAmount")
end if
' Get the total sales figures.
aggregateSales = allFLsales + allGAsales + allKYsales + allTXsales + allWIsales
if page = "Sales by State" then
Call WriteSalesByStateTable ()
end if
SalesQueryRecordset.MoveNext
Loop
%>
<%if page = "Sales Totals" then%>
State
Amount
<%
dim loopCount2
loopCount2 = 0
do while loopCount2 < stateArrayLength%>
<%if eval(stateArray(loopCount2)) = 1 then%>
<%
SalesQueryRecordset.Close 'FIRST close recordset
databaseConnection.Close 'THEN close connection
set databaseConnection= nothing
set SalesQueryRecordset = nothing
%>
