<%@ 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 & "" 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 "

" & langErrNoRecFound & "

" else rstemp.MoveFirst rstemp.PageSize = pMaxItemsPerPage totalPages = rstemp.PageCount totalRecs = rstemp.RecordCount rstemp.AbsolutePage = curPage %>
<%=listHeading%>

<% 'Show Page Navigation and Sort if more than one record returned if totalRecs > 1 then %>
<% 'Show Page Navigation if more than one page if totalPages > 1 then call pageNavigation("selectPageTop") else Response.Write " " end if %> <%call pageSort("sortPageTop")%>

<% 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")&"") %> <% count = count + 1 rstemp.moveNext 'Draw line between products if not rstemp.EOF and count < rstemp.pageSize then %> <% end if loop %>
<%=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 %>


<% 'Show Page Navigation if more than one page returned if totalPages > 1 then %>
<%call pageNavigation("selectPageBot")%>
<% 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 %>