<%
Sub OLDProductCreateSQL (sql)
'*****************************************************
' Generates SQL to display a product
' expects most parameters to be in global
' Version 4.0 for compatibility mode
' Jan 21, 2002
'*****************************************************
sql=""
If getconfig("xProductMultiCategories")="Yes" then
   OLDProductCreateMultiSQL sql
   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 getconfig("xstocklow")<>"" then
    lngcstock= clng(getconfig("xstocklow")) 
    sql = sql & " and cstock> " & lngcstock
end if
if getconfig("xproductmatch")="Yes" then
   sql=sql & " and productmatch='" & xproductmatch & "'"
end if 
if getconfig("xproductmatchcustomer")="Yes" then
   if GetSess("CustomerProductGroup")<>"" then
     sql=sql & " and customermatch='" & getsess("customerProductgroup") & "'"
   end if  
end if
If getconfig("xselectproductsbylanguage")="Yes" and getsess("language")<>"" then
            sql=sql & " and clanguage='" & getsess("language") & "'"
end if    
	    
sql = sql & " order by " & getconfig("xsortproducts")
'debugwrite sql
end sub

Sub OLDProductCreateMultiSql (sql)
dim strProductFields
dim i
dim strdistinct
strdistinct="DISTINCTROW"
if UCASE(xdatabasetype)="SQLSERVER" then
	strdistinct="DISTINCT"
end if
strProductFields=Getconfig("strProductFields")
If strProductFields="" then
        OldGetProductFields
        strProductFields=Getconfig("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 getconfig("xstocklow")<>"" then
	    lngcstock= clng(getconfig("xstocklow")) 
	    sql = sql & " and cstock> " & lngcstock
	end if
	if getconfig("xproductmatch")="Yes" then
         sql=sql & " and p.productmatch='" & xproductmatch & "'"
        end if 
	if getconfig("xproductmatchcustomer")="Yes" then
          if GetSess("CustomerProductGroup")<>"" then
            sql=sql & " and p.customermatch='" & getsess("customerProductgroup") & "'"
          end if  
        end if    
        If getconfig("xselectproductsbylanguage")="Yes" and getsess("language")<>"" then
            sql=sql & " and clanguage='" & getsess("language") & "'"
         end if    
	sql = sql & " order by " & getconfig("xsortproducts")
	'SetSess "SQL", sql
	'debugwrite sql
end sub

Sub OLDSearchGenerateSQL()
If getconfig("xProductMultiCategories")="Yes" then
   OldSearchGenerateMultiSQL
   exit sub
end if   
dim i, j
dim whereok
Dim SearchFields
dim Fieldcount
SetupSearchFields SearchFields
Fieldcount=ubound(Searchfields)
whereok=" WHERE "
SQL = "SELECT * FROM products " 
if wordcount> 0  then
  SQL = SQL & whereok 
  SQL = SQL & "("
  Whereok="" 
  for i = 0 to wordcount-1
   SQL=SQL & whereok
    For j=0 to fieldcount
      If j> 0 then
         SQL = SQL & " OR "
      else
         SQL=SQL & " ( "
      end if  
      SQL = SQL & Searchfields(j) & " Like '%" & words(i) & "%' "
    next
    SQL = SQL & " )"
  whereok=" OR "
  next
  SQL = SQL & ")"
  whereok=" AND "
end if    
if catCount<>0  then
   SQL=SQL & whereok
   SQL = SQL & " ("  
   whereok=""  
   for i =0 to catcount-1
      SQL = SQL & whereok &  " CCategory = " & catArray(i)  
      whereOK=" OR "  
   next 
   Sql=Sql & ")"
   whereok=" AND "
end if
if SubcatCount<>0  then
   SQL=SQL & whereok
   SQL = SQL & " ("  
   whereok=""  
   for i =0 to Subcatcount-1
      SQL = SQL & whereok &  " subCategoryID = " & SubcatArray(i)  
      whereOK=" OR "  
   next 
   Sql=Sql & ")"
   whereok=" AND "
end if
Sql=Sql & whereok
sql=sql &  " (hide is null or hide=0)"
whereok=" AND " 
if getconfig("xstocklow")<>"" then
    lngCStock= clng(getconfig("xStockLow")) 
    SQL= SQL & WhereOK 
    Sql = sql & " cStock> " & lngCStock
    whereok=" AND " 
end if
if getconfig("xproductmatch")="Yes" then
   SQL= SQL & WhereOK 
   sql=sql & " productmatch='" & xproductmatch & "'"
   whereok=" AND " 
end if  
if getconfig("xproductmatchcustomer")="Yes" then
    if GetSess("CustomerProductGroup")<>"" then
       SQL= SQL & WhereOK 
       sql=sql & " customermatch='" & getsess("customerProductgroup") & "'"
     end if  
end if    
If getconfig("xselectproductsbylanguage")="Yes" and getsess("language")<>"" then
            sql=sql & " and clanguage='" & getsess("language") & "'"
end if    

sql = sql &  " order by " & getconfig("xSortProducts")
SetSess "SQL",SQL
SetSessA "words",Words
SetSess "wordcount",wordcount
'debugwrite SQL
end sub
'
Sub OLDSearchGenerateMultiSQL()
dim i, j, tmpstr
dim whereok
Dim SearchFields
dim Fieldcount
dim strdistinct
strdistinct="DISTINCTROW"
if UCASE(xdatabasetype)="SQLSERVER" then
	strdistinct="DISTINCT"
end if
SetupSearchFields SearchFields
Fieldcount=ubound(Searchfields)
whereok=" AND "
dim strProductFields
strProductFields=Getconfig("strProductFields")
If strProductFields="" then
      OldGetProductFields
      strProductFields=Getconfig("strProductFields")
end if      
tmpstr="select " & strdistinct & " " & strproductfields & " from products p, prodcategories cc, categories c"
sql= " where cc.intcatalogid=p.catalogid and cc.intcategoryid=c.categoryid "
if wordcount> 0  then
  SQL = SQL & whereok 
  SQL = SQL & "("
  Whereok="" 
  for i = 0 to wordcount-1
   SQL=SQL & whereok
    For j=0 to fieldcount
      If j> 0 then
         SQL = SQL & " OR "
      else
         SQL=SQL & " ( "
      end if  
      SQL = SQL & Searchfields(j) & " Like '%" & words(i) & "%' "
    next
    SQL = SQL & " )"
  whereok=" OR "
  next
  SQL = SQL & ")"
  whereok=" AND "
end if    
if catCount<>0  then
   SQL=SQL & whereok
   SQL = SQL & " ("  
   whereok=""  
   for i =0 to catcount-1
      SQL = SQL & whereok &  " cc.intCategoryId = " & catArray(i)  
      whereOK=" OR "  
   next 
   Sql=Sql & ")"
   whereok=" AND "
end if
if SubcatCount<>0  then
   SQL=SQL & whereok
   SQL = SQL & " ("  
   whereok=""
   dim blnSubcat
   blnSubCat=False  
   for i =0 to Subcatcount-1
      sql=sql & whereok &  "sc.intsubcategoryid" & "=" &  subcatarray(i) 
      whereOK=" OR "
      blnSubCat=True        
   next 
   
   If blnSubCat then
	sql=sql & ") and sc.intcatalogid=p.catalogid "
	tmpstr=trim(tmpstr) & ", prodsubcategories sc "
   Else
	Sql=Sql & ")"
   End If
   whereok=" AND "
end if
Sql=Sql & whereok
sql=sql &  " (hide is null or hide=0)"
whereok=" AND " 
if getconfig("xstocklow")<>"" then
    lngCStock= clng(getconfig("xStockLow")) 
    SQL= SQL & WhereOK 
    Sql = sql & " cStock> " & lngCStock
    whereok=" AND " 
end if
if getconfig("xproductmatch")="Yes" then
   SQL= SQL & WhereOK 
   sql=sql & " p.productmatch='" & xproductmatch & "'"
   whereok=" AND " 
end if    
if getconfig("xproductmatchcustomer")="Yes" then
    if GetSess("CustomerProductGroup")<>"" then
       SQL= SQL & WhereOK 
       sql=sql & " and p.customermatch='" & getsess("customerProductgroup") & "'"
       whereok=" AND " 
     end if  
end if    
If getconfig("xselectproductsbylanguage")="Yes" and getsess("language")<>"" then
            sql=sql & " and clanguage='" & getsess("language") & "'"
end if    

sql = sql &  " order by " & getconfig("xSortProducts")
sql=tmpStr & sql
SetSess "SQL",SQL
SetSessA "words",Words
SetSess "wordcount",wordcount
end sub

Sub OLDQueryGenerateSQl
on error resume next
firsttime="TRUE"
SQL = "SELECT * FROM products " 
if getconfig("xstocklow")<>"" then
    lngCStock= clng(getconfig("xStockLow")) 
    oldaddprefix 
    SQL = SQL  & " cStock> " & lngCStock
end if
for i=0 to keycount-1
      oldAddSQL  Keys(i), keyvalues(i), SQL
Next
oldaddprefix
sql=sql &  " (hide is NULL OR hide=0)"
if getconfig("xproductmatch")="Yes" then
   oldaddprefix
   sql=sql & " productmatch='" & xproductmatch & "'"
end if    
if getconfig("xproductmatchcustomer")="Yes" then
    if GetSess("CustomerProductGroup")<>"" then
       oldaddprefix
       sql=sql & " customermatch='" & getsess("customerProductgroup") & "'"
       whereok=" AND " 
     end if  
end if    
If getconfig("xselectproductsbylanguage")="Yes" and getsess("language")<>"" then
        oldaddprefix
        sql=sql & " clanguage='" & getsess("language") & "'"
end if    

sql = sql & " ORDER BY " & getconfig("xSortProducts")
if getconfig("xdebug")="Yes" then
   debugwrite sql
end if
end sub
'
SUB OLDAddSQL (strname,strvalue, SQL)
dim fieldtype, istrname
ustrname=Ucase(strname)
CheckValidField ustrname, rc, fieldtype
if rc>0 then
   exit sub
end if 
if Fieldtype ="Number" or FieldType="Currency" then

end if

if fieldtype="Text" or fieldtype="Memo" then
  If ucase(strvalue)=allvalues then          ' make all really mean all
     strvalue=""
  end if
   oldaddprefix
   SQL=SQL & " " &  strname & " like '" & strvalue & "%'"
   exit sub
end if 
If Fieldtype="DateTime" then
     oldaddprefix
     SQL=SQL & " " &  strname & "=#" & cdate(strvalue) & "#"
     exit sub
end if
If Fieldtype="Currency"  then
   if strvalue<0 then
       exit sub
   end if
   If not IsNumeric(strvalue) then
      exit sub
   end if 
   if getconfig("xConvertEuropeanNumbers")="Yes" then
     strvalue=replace(strvalue,",",".")
   end if
   'strvalue=Formatnumber(strvalue,2)
   oldaddprefix
   SQL=SQL & " " &  strname & "<=" & strvalue 
     exit sub
end if
if Fieldtype ="Number" then
    If strvalue<0 then
       exit sub
    end if
    If not IsNumeric(strvalue) then
      exit sub
     end if 
     oldaddprefix
     SQL=SQL & " " &  strname & "=" & strvalue 
     exit sub
end if
oldaddprefix
  SQL=SQL & " " &  strname & " like '" & strvalue & "%'"
end sub
'
Sub oldaddPrefix
if firsttime="TRUE" then
      SQL=SQL & " WHERE "
      firsttime="FALSE"
else
   SQL = SQL & " AND "
end if
end sub

Sub OLDQueryGenerateMultiSQL 
dim i
dim rc
dim strProductFields
dim strdistinct
strdistinct="DISTINCTROW"
if UCASE(xdatabasetype)="SQLSERVER" then
	strdistinct="DISTINCT"
end if
strProductFields=Getconfig("strProductFields")
If strProductFields="" then
        OldGetProductFields
        strProductFields=Getconfig("strProductFields")
end if        
tmpstr="select " & strdistinct & " " & strproductfields & " from products p, prodcategories cc, categories c "
'on error resume next
firsttime="FALSE"
sql=" where cc.intcatalogid=p.catalogid and cc.intcategoryid=c.categoryid "
for i=0 to keycount-1
      oldAddSQL  Keys(i), keyvalues(i), SQL
Next
oldaddprefix
sql=sql &  " (hide is NULL OR hide=0)"
if getconfig("xstocklow")<>"" then
    lngCStock= clng(getconfig("xStockLow")) 
    oldaddprefix 
    sql = sql  & " cstock > " & lngcstock
end if
if getconfig("xproductmatch")="Yes" then
   oldaddprefix
   sql=sql & " p.productmatch='" & xproductmatch & "'"
end if    
if getconfig("xproductmatchcustomer")="Yes" then
    if GetSess("CustomerProductGroup")<>"" then
       oldaddprefix
       sql=sql & " and p.customermatch='" & getsess("customerProductgroup") & "'"
     end if  
end if    
If getconfig("xselectproductsbylanguage")="Yes" and getsess("language")<>"" then
        oldaddprefix
        sql=sql & " clanguage='" & getsess("language") & "'"
end if    

sql = sql & " ORDER BY " & getconfig("xSortProducts")
sql=tmpStr & sql
if getconfig("xdebug")="Yes" then
   debugwrite sql
end if
end sub
'
Sub OldGetProductFields
dim i, strproductfields, fldRS
	dim fldsRS
	sql="select * 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
	SetConfig "strProductFields", strProductFields
End Sub
%>