%@ Language=VBScript %>
<%
'*************************************************************************
' DO NOT MODIFY THIS SCRIPT IF YOU WANT UPDATES TO WORK!
' Function : Displays a list of products that match a given criteria...
' : - Matches search criteria
' : - Matches a category
' : - Matches "specials" on flagged products
' : If a category is supplied which has sub categories, the
' : script will display a summary of categories instead of the
' : product list.
'*************************************************************************
Option explicit
Response.Buffer = true
%>
<%
'Work Fields
dim I
dim totalRecs
dim totalPages
dim count
dim curPage
dim catPos
dim catLst
dim listHeading
dim special
dim strSearch, strSearchMax, strSearchMin, strSearchCat
dim sortField
dim queryStr
'Categories
dim IDCategory
dim categoryDesc
dim IDParentCategory
dim categoryHTML
'Product
dim IDProduct
dim SKU
dim Description
dim DescriptionLong
dim Price
dim Details
dim listPrice
dim smallImageURL
dim imageURL
dim Stock
dim fileName
dim noShipCharge
'Database
dim mySQL
dim conntemp
dim rstemp
dim rstemp2
'Session
dim idOrder
dim idCust
'*************************************************************************
'Open Database Connection
call openDb()
'Store Configuration
if loadConfig() = false then
call errorDB(langErrConfig,"")
end if
'Get/Set Cart/Order Session
idOrder = sessionCart()
'Get/Set Customer Session
idCust = sessionCust()
'---------------------------------
' PARMS - Search
'---------------------------------
strSearch = Request("strSearch")
strSearchMin = Request("strSearchMin")
strSearchMax = Request("strSearchMax")
strSearchCat = Request("strSearchCat")
if len(strSearch & strSearchMin & strSearchMax & strSearchCat) > 0 then
'Get rid of malicious HTML
strSearch = validHTML(strSearch)
strSearchMin = validHTML(strSearchMin)
strSearchMax = validHTML(strSearchMax)
strSearchCat = validHTML(strSearchCat)
'Get rid of multiple spaces in keywords
do until instr(strSearch," ") = 0
strSearch = replace(strSearch," "," ")
loop
'If, after all this string manipulation, we have an empty string...
if len(strSearch & strSearchMin & strSearchMax & strSearchCat) = 0 then
Response.Clear
response.redirect "sysMsg.asp?errMsg=" & server.URLEncode(langErrInvSearch)
end if
'Assign default values
if not(isNumeric(strSearchMin)) then
strSearchMin = 0
else
strSearchMin = CDbl(strSearchMin)
end if
if not(isNumeric(strSearchMax)) then
strSearchMax = 0
else
strSearchMax = CDbl(strSearchMax)
end if
if not(isNumeric(strSearchCat)) then
strSearchCat = 0
else
strSearchCat = CInt(strSearchCat)
end if
end if
'---------------------------------
' PARMS - Specials
'---------------------------------
special = Request.QueryString("special")
if len(special) > 0 and special <> "Y" then
special = "N"
end if
'---------------------------------
' PARMS - Categories
'---------------------------------
idCategory = Request.QueryString("idCategory")
if len(idCategory) > 0 then
'Validate that Category is numeric
if not IsNumeric(idCategory) then
Response.Clear
response.redirect "sysMsg.asp?errMsg=" & server.URLEncode(langErrInvCategory)
end if
'Validate that Category exists in DB
mySQL = "SELECT idCategory " _
& "FROM categories " _
& "WHERE idCategory = " & validSQL(idCategory,"I")
set rsTemp = openRSexecute(mySQL)
if rsTemp.eof then
Response.Clear
response.redirect "sysMsg.asp?errMsg=" & server.URLEncode(langErrInvCategory)
end if
call closeRS(rsTemp)
end if
'---------------------------------
' PARMS - Validate
'---------------------------------
if len(strSearch & strSearchMin & strSearchMax & strSearchCat) = 0 _
and len(special) = 0 _
and len(idCategory) = 0 then
mySQL = "SELECT idCategory " _
& "FROM categories " _
& "WHERE IdParentCategory = 0"
set rsTemp = openRSexecute(mySQL)
if rsTemp.eof then
Response.Clear
response.redirect "sysMsg.asp?errMsg=" & server.URLEncode(langErrInvCategory & " / " & langErrInvSearch)
else
IDCategory = rsTemp("idCategory")
end if
call closeRS(rsTemp)
end if
%>
<%
'---------------------------------
' Main driver
'---------------------------------
'SEARCH
if len(strSearch & strSearchMin & strSearchMax & strSearchCat) > 0 then
listHeading = "" & langGenSearchFor & " : " & strSearch & " [" & strSearchMin & "," & strSearchMax & "," & strSearchCat & "] "
queryStr = "strSearch=" & Server.UrlEncode(strSearch) & "&strSearchMin=" & Server.UrlEncode(strSearchMin) & "&strSearchMax=" & Server.UrlEncode(strSearchMax) & "&strSearchCat=" & Server.UrlEncode(strSearchCat)
call displayItems("search")
else
'SPECIALS
if len(special) > 0 then
listHeading = "" & langGenSpecials & ""
queryStr = "special=Y"
call displayItems("special")
'CATEGORIES
else
'Determine category tree position (eg: You are at : cat1 > cat2)
catPos = getCategoryPos(IDCategory,"")
'Expand the Category tree from the supplied category onward
catLst = expandCategory(IDCategory,"")
'Display Category Tree position, trim the " <" in catPos
listHeading = "" & langGenYouAreAt & " : " & mid(catPos,3)
'Display list of products that match category
if len(trim(catLst)) = 0 then
queryStr = "idcategory=" & IDCategory
call displayItems("list")
'Display Category Tree
else
call displayCategory()
end if
end if
end if
%>
<%
call closeDb()
'*************************************************************************
'Determine category position eg: [You are at > cat1 > cat2] (recursive)
'*************************************************************************
function getCategoryPos(IDCategory,tempStr)
dim mySQL, rsTemp
mySQL = "SELECT idCategory,idParentcategory,categoryDesc " _
& "FROM categories " _
& "WHERE idCategory = " & validSQL(idCategory,"I")
set rsTemp = openRSexecute(mySQL)
do while not rsTemp.eof
tempStr = " > " & rsTemp("categoryDesc") & "" & tempStr
tempStr = getCategoryPos(rsTemp("idParentcategory"),tempStr)
rsTemp.movenext
loop
call closeRS(rsTemp)
getCategoryPos = tempStr
end function
'*************************************************************************
'Expand Categories tree from given category (recursive). Will also
'display the number of products in each sub category.
'*************************************************************************
function expandCategory(IDCategory,tempStr)
dim mySQL, rsTemp, catArr, row
'Get Sub-Categories
mySQL = "SELECT idCategory, categoryDesc,categoryHTML," _
& " (SELECT COUNT(*) " _
& " FROM products, categories_products " _
& " WHERE products.idProduct = categories_products.idProduct " _
& " AND categories_products.idCategory = categories.idCategory " _
& " AND active = -1) " _
& " AS prodCount " _
& "FROM categories " _
& "WHERE idParentcategory = " & validSQL(idCategory,"I") & " " _
& "ORDER BY categoryDesc "
set rsTemp = openRSexecute(mySQL)
if not rsTemp.EOF then
'Use getRows() to reduce DB resource requirements. This is a
'little more difficult to work with, but makes the queries
'much faster. After populating the array, the values are :
'- catArr(0,row) = idCategory
'- catArr(1,row) = categoryDesc
'- catArr(2,row) = categoryHTML
'- catArr(3,row) = prodCount
catArr = rsTemp.getRows()
end if
call closeRS(rsTemp)
'Show Sub-Categories
if isArray(catArr) then
tempStr = tempStr & "
"
for row = 0 to UBound(catArr,2)
tempStr = tempStr & "
"
end if
expandCategory = tempStr
end function
'*************************************************************************
'Display Category Tree
'*************************************************************************
sub displayCategory()
%>
<%=listHeading%>
<%=catLst%>
<%
end sub
'*************************************************************************
'Display list of products for category
'*************************************************************************
sub displayItems(listAction)
'Determine sort order
sortField = lcase(trim(Request.QueryString("sortField")))
if sortField <> "description" _
and sortField <> "price" then
sortField = "description"
end if
'Determine page number
curPage = Request.QueryString("curPage")
if len(curPage) = 0 or not isNumeric(curPage) then
curPage = 1
else
curPage = CLng(curPage)
end if
'Create SQL statement
select case listAction
'SEARCH
case "search"
'SQL - General
mySQL = "SELECT a.idProduct,a.SKU,a.description," _
& " a.descriptionLong,a.listPrice,a.Price," _
& " a.SmallImageUrl,a.Stock,a.fileName," _
& " a.noShipCharge " _
& "FROM products a " _
& "WHERE a.active = -1 "
'SQL - Minimum Price
if strSearchMin <> 0 then
mySQL = mySQL & "AND a.Price >= " & validSQL(strSearchMin,"D") & " "
end if
'SQL - Maximum Price
if strSearchMax <> 0 then
mySQL = mySQL & "AND a.Price <= " & validSQL(strSearchMax,"D") & " "
end if
'SQL - Category
if strSearchCat <> 0 then
mySQL = mySQL _
& "AND EXISTS ("_
& " SELECT b.idCategory " _
& " FROM categories_products b " _
& " WHERE b.idProduct = a.idProduct " _
& " AND b.idCategory = " & validSQL(strSearchCat,"I") & ") "
end if
'SQL - Keywords
if len(strSearch) > 0 then
'Declare extra variables
dim searchArr, tmpSQL1, tmpSQL2, tmpSQL3, tmpSQL4
'Create array of keywords
searchArr = split(trim(strSearch)," ")
'Keyword search SQL
tmpSQL1 = "(a.details LIKE "
tmpSQL2 = "(a.description LIKE "
tmpSQL3 = "(a.descriptionLong LIKE "
tmpSQL4 = "(a.SKU LIKE "
for i = 0 to Ubound(searchArr)
if i = Ubound(searchArr) then
tmpSQL1 = tmpSQL1 & "'%" & validSQL(searchArr(i),"A") & "%')"
tmpSQL2 = tmpSQL2 & "'%" & validSQL(searchArr(i),"A") & "%')"
tmpSQL3 = tmpSQL3 & "'%" & validSQL(searchArr(i),"A") & "%')"
tmpSQL4 = tmpSQL4 & "'%" & validSQL(searchArr(i),"A") & "%')"
else
tmpSQL1 = tmpSQL1 & "'%" & validSQL(searchArr(i),"A") & "%' OR a.details LIKE "
tmpSQL2 = tmpSQL2 & "'%" & validSQL(searchArr(i),"A") & "%' OR a.description LIKE "
tmpSQL3 = tmpSQL3 & "'%" & validSQL(searchArr(i),"A") & "%' OR a.descriptionLong LIKE "
tmpSQL4 = tmpSQL4 & "'%" & validSQL(searchArr(i),"A") & "%' OR a.SKU LIKE "
end if
next
'Put it all together
mySQL = mySQL & "AND (" & tmpSQL1 & " OR " & tmpSQL2 & " OR " & tmpSQL3 & " OR " & tmpSQL4 & ") "
end if
'Sort Order
mySQL = mySQL & "ORDER BY a." & sortField
'------------------------------------------------------------
'SPECIALS
case "special"
mySQL = "SELECT idProduct,SKU,Description,DescriptionLong," _
& " ListPrice,Price,SmallImageUrl,Stock," _
& " fileName,noShipCharge " _
& "FROM products " _
& "WHERE hotDeal = -1 " _
& "AND active = -1 " _
& "ORDER BY " & sortField
'CATEGORY
case else
mySQL = "SELECT a.idProduct,a.SKU,a.Description," _
& " a.DescriptionLong,a.ListPrice,"_
& " a.Price,a.SmallImageUrl,a.Stock," _
& " a.fileName,a.noShipCharge " _
& "FROM products a, categories_products b " _
& "WHERE a.idProduct = b.idProduct " _
& "AND b.idCategory = " & validSQL(idCategory,"I") & " " _
& "AND a.active = -1 " _
& "ORDER BY a." & sortField
end select
'Create and Open recordset
set rsTemp = openRSopen(mySQL,0,adOpenStatic,adLockReadOnly,adCmdText,pMaxItemsPerPage)
'Read through recordset and display products
if rstemp.eof then
response.write "
<%
'Show Page Navigation and Sort if more than one record returned
if totalRecs > 1 then
%>
<%
end if
%>
<%
do while not rstemp.eof and count < rstemp.pageSize
IDProduct = rstemp("idProduct")
SKU = trim(rstemp("SKU")&"")
Description = trim(rstemp("description")&"")
DescriptionLong = trim(rstemp("descriptionLong")&"")
listPrice = rstemp("listPrice")
Price = rstemp("price")
smallImageURL = trim(rstemp("smallImageUrl")&"")
Stock = rstemp("Stock")
fileName = trim(rstemp("fileName")&"")
noShipCharge = trim(rstemp("noShipCharge")&"")
%>
<%=Description%>(<%=SKU%>)
<%=DescriptionLong%>
<%
'Show Pricing
if not(pHidePricingZero=-1 and Price=0) then
if listPrice > Price then
Response.Write "" & langGenListPrice & ":" & pCurrencySign & moneyS((listPrice)) & " "
end if
Response.Write "" & langGenOurPrice & ":" & pCurrencySign & moneyS(Price) & ""
if (listPrice - Price) > 0 then
Response.Write " " & langGenYouSave & ":" & pCurrencySign & moneyS((listPrice-Price)) & " (" & formatNumber((((listPrice-Price)/listPrice)*100),0) & "%)"
end if
end if
%>
<%
'Show Extended layout?
if listViewLayout = 1 then
'Free Shipping message (if not a downloadable item)
if UCase(noShipCharge) = "Y" and len(fileName) = 0 then
Response.Write "" & langGenFreeShipping & " "
end if
'In stock, Out of stock message
if pShowStockView = -1 then
if pHideAddStockLevel = -1 then
Response.Write "" & langGenInStock & " "
else
if Stock > pHideAddStockLevel then
Response.Write "" & langGenInStock & " "
else
Response.Write "" & langGenOutStock & " "
end if
end if
end if
'Show current ratings
mySQL="SELECT SUM(revRating) AS revSum, " _
& " COUNT(revRating) AS revCount " _
& "FROM reviews " _
& "WHERE idProduct = " & validSQL(idProduct,"I") & " " _
& "AND revStatus = 'A' "
set rsTemp2 = openRSexecute(mySQL)
if not rsTemp2.EOF then
if rsTemp2("revSum") > 0 and rsTemp2("revCount") > 0 then
Response.Write "" _
& "" _
& langGenAverageRating _
& " : " _
& ratingImage(rsTemp2("revSum")/rsTemp2("revCount")) & " "
end if
end if
call closeRS(rsTemp2)
end if
%>
<%
'Display small product image
if smallImageURL <> "" then
Response.Write ""
else
Response.Write ""
end if
%>
<%
'Show View and Add buttons
if pCatalogOnly = 0 and _
(pHideAddStockLevel = -1 or _
pHideAddStockLevel < CDbl(Stock)) then
'Check for options and adjust ADD button.
mySQL = "SELECT idOptionGroup " _
& "FROM optionsGroupsXref " _
& "WHERE idProduct = " & validSQL(idProduct,"I")
set rsTemp2 = openRSexecute(mySQL)
if rsTemp2.eof then
%>
<%
else
%>
<%
end if
%>
<%
call closeRS(rsTemp2)
end if
%>
<%
count = count + 1
rstemp.moveNext
'Draw line between products
if not rstemp.EOF and count < rstemp.pageSize then
%>
<%
end if
loop
%>
<%
'Show Page Navigation if more than one page returned
if totalPages > 1 then
%>
<%
else
%>
<%
end if
end if
call closeRS(rsTemp)
end sub
'*********************************************************************
'Display page navigation
'*********************************************************************
sub pageNavigation(formFieldName)
Response.Write langGenNavPage & " "
Response.Write " " & langGenOf & " " & TotalPages & " "
Response.Write "[ "
if curPage > 1 then
Response.Write "" & langGenNavBack & ""
else
Response.Write langGenNavBack
end if
Response.Write " | "
if curPage < TotalPages then
Response.Write "" & langGenNavNext & ""
else
Response.Write langGenNavNext
end if
Response.Write " ]"
end sub
'*********************************************************************
'Display sort list
'*********************************************************************
sub pageSort(formFieldName)
Response.Write langGenSort & " : "
%>
<%
end sub
%>