<%Option Explicit%>
<%
'******************************************************************
' This routine should not be changed by you.
' This routine handles all the database work
' To change product displays go to shopproductformat.asp.
' ' Logic
' if search is specified then sql is in session variable
' otherwise generated from
' id = category id the number in the database
' cat is a category name
' Product=name product name. cannot be used with id or cat
' l3= sub sub category
' l4= sub sub category
' l5= sub sub category
' page=x ' used when recusrsively called to display next page
' search=yes ' used when someone else created sql such as search.asp
' ' then sql in session variable SQL
' if xstockLow is set, then product will not display if stock limit has been reached
' Now support sub sub categories
' Set DisplayCategoryImages to Yes to display category images
' Multi categories per product
'******************************************************************
dim search
Dim dbc
Dim PRODUCTNAME, CATALOGID
Dim ProductFields ' fields being displayed in order
Dim ProductCaptions ' Product column captions
Dim ProductFieldCount ' count of fields
Dim ProductSelect
Dim ProdIndex
Dim level3, level4, level5
ProductSelect=ucase(xProductSelect)
SetSess "CurrentUrl","shopdisplayproducts.asp"
mypage=request.querystring("page")
mypagesize=xProductsPerPage
' If there is no page, then we must generate sql otherwise sqlis in Session(sqlQuery)
if mypage= "" then
mypage=1 ' first time through
ProcessFirst ' get input variables
CreateSql ' generate sql
else
sql=GetSess("sqlquery") ' on recursive calls we stored sql in sessikon variable
Category=GetSess("Category") ' see what previous one was
Subcat=getsess("Subcat")
cat_id=getsess("Cat_id")
end if
ShopPageHeader ' normal page header
DisplayProducts ' display products
ShopPageTrailer ' normal trailer
' Process first time
Sub ProcessFirst()
CAT_ID = Request("id") ' category id
CATEGORY = Request("cat") ' category name
SUBCAT=Request("subcat") ' subcategory id
PRODUCTNAME=Request("PRODUCT") ' product name
CATALOGID=Request("CATALOGID") ' catalogid
SetSess "Category",CATEGORY 'remember category see what previous one was
setsess "Subcat",subcat
setsess "cat_id",cat_id
LEVEL3=Request("L3")
LEVEL4=Request("L4")
LEVEL5=Request("L5")
end sub
'
Sub CreateSQL()
search=Request.querystring("Search")
if search<>"" then
SQL=GetSess("SQL")
exit sub
end if
If xProductMultiCategories="Yes" then
CreateMultiSQL
exit sub
end if
sql = "select * from products where "
if cat_id <> "" then
sql = sql & " ccategory = " & cat_id
else
if catalogid<>"" then
sql = sql & " catalogid = " & catalogid
else
if productname="" then
sql = sql & "category like '"& category & "%'"
else
sql = sql & "cname like '"& productname & "%'"
end if
end if
end if
if subcat<> "" then
sql = sql & " and subcategoryid=" & subcat
end if
if level3<>"" then
sql=sql & " and level3 like '%" & level3 & "%'"
end if
if level4<>"" then
sql=sql & " and level4 like '%" & level4 & "%'"
end if
if level5<>"" then
sql=sql & " and level5 like '%" & level5 & "%'"
end if
sql = sql & " and (hide is null or hide=0)"
if xstocklow<>"" then
lngcstock= clng(xstocklow)
sql = sql & " and cstock> " & lngcstock
end if
sql = sql & " order by " & xsortproducts
'debugwrite sql
end sub
Sub CreateMultiSql
dim strProductFields
dim i
dim strdistinct
strdistinct="DISTINCTROW"
if UCASE(xdatabasetype)="SQLSERVER" then
strdistinct="DISTINCT"
end if
strProductFields=GetSess("strProductFields")
If strProductFields="" then
dim fldsRS
sql="select top 1 * from products"
Set fldsRS = Server.CreateObject("ADODB.Recordset")
ShopOpenDatabase dbc
fldsRS.Open SQL,dbc,adOpenForwardOnly,adLockReadOnly,adCMDText
dim tmpStr
For i=0 To fldsRS.Fields.Count-1
IF i=fldsRS.Fields.Count-1 then
strProductFields=strProductFields & "p." & fldsRS(i).name
else
strProductFields=strProductFields & "p." & fldsRS(i).name & ", "
end if
Next
fldsRS.Close
Set fldsRS=Nothing
ShopCloseDatabase dbc
SetSess "strProductFields", strProductFields
End If
sql="select " & strdistinct & " " & strproductfields
sql=sql & " from products p, prodcategories cc, categories c"
if subcat<> "" then
sql = sql & ", prodsubcategories sc"
end if
sql=sql & " where cc.intcatalogid=p.catalogid and cc.intcategoryid=c.categoryid and"
if cat_id <> "" then
sql = sql & " cc.intcategoryid = " & cat_id
else
if catalogid<>"" then
sql = sql & " p.catalogid = " & catalogid
else
if productname="" then
sql = sql & " c.catdescription like '"& category & "%'"
else
sql = sql & "p.cname like '"& productname & "%'"
end if
end if
end if
if subcat<> "" then
sql = sql & " and sc.intsubcategoryid=" & subcat & " and sc.intcatalogid=p.catalogid"
end if
if level3<>"" then
sql=sql & " and level3 like '%" & level3 & "%'"
end if
if level4<>"" then
sql=sql & " and level4 like '%" & level4 & "%'"
end if
if level5<>"" then
sql=sql & " and level5 like '%" & level5 & "%'"
end if
sql=sql & " and (hide is null or hide=0) "
if xstocklow<>"" then
lngcstock= clng(xstocklow)
sql = sql & " and cstock> " & lngcstock
end if
sql = sql & " order by " & xsortproducts
'SetSess "SQL", sql
'debugwrite sql
end sub
' ****** Display Products
Sub DisplayProducts()
Dim header
Dim recordcount
dim words
dim wordcount
dim i
dim msg
dim rc
ShopOpenDatabase dbc
header=Prodheaderfont & ""
If category <> "" Then
header = header & Category
else
header= header & LangProduct01
End If
header = header & ""
response.write header
ShowCategoryImage
ShopOpenRecordSet SQL,objRS, mypagesize, mypage
if objRS.eof then
objRS.Close
set objRS=nothing
ShopCloseDatabase dbc
response.write "
" & xfont & langProductSearch & ""
exit sub
end if
recordcount=0
response.write "" & xfont & LangCommonPage & mypage & LangCommonOf & maxpages & ""
If ProductSelect="YES" then
Response.Write("
")
end if
Call PageNavBar (SQL)
objRS.Close
set objRS=nothing
ShopCloseDatabase dbc
end sub
'******************************
' Sub ShowCategoryImage
' =====================
' MWB March 11 2001
' If DisplayCategoryImages is set to Yes
' Displays the CatImage if there are not subcategories
' Displays the SubCatImage if there is
'******************************
Sub ShowCategoryImage
Dim ImageFileName
Dim rs
Dim query
imagefilename=""
If DisplayCategoryImages <> "Yes" Then exit sub
If SubCat="" and cat_id="" then exit sub
If subcat<>"" Then
query = "select subcatimage from subcategories where subcategoryid = " & subcat
query=query & " AND categoryid=" & cat_id
set rs = dbc.execute(query)
If not rs.EOF Then
ImageFileName = rs("SubCatImage")
if isnull(imagefilename) then
imagefilename=""
end if
end if
rs.close
set rs=nothing
end if
if imagefilename="" then
query = "select catimage from categories where categoryid = " & cat_id
set rs = dbc.execute(query)
If not rs.EOF Then
imagefilename = rs("catimage")
if isnull(imagefilename) then
imagefilename=""
end if
end if
rs.close
set rs=nothing
End if
If ImageFileName="" then exit sub
%>
><%
End Sub
%>