<%@ Language=VBScript %> <% Option Explicit Response.Buffer = true Response.Expires = 0 %> <% 'MAIN EXECUTE Dim rstTypes Dim rstArtTypes Dim rstArtikels Dim SearchString Dim TypeID Dim TypeCriteria Dim Criteria Dim sql Dim insql Dim aSearch Dim aSearch2 Dim VKP_Inc Dim VKP_Exc Dim i Dim AbsPage Dim bColor Set rstTypes = Server.CreateObject("ADODB.Recordset") Set rstArtTypes = Server.CreateObject("ADODB.Recordset") Set rstArtikels = Server.CreateObject("ADODB.Recordset") rstArtikels.CursorLocation = 3 'All the recordsets go here Set rstTypes = Cnn.Execute ("SELECT * FROM ART_TYPES WHERE InternetDisplay = 1 ORDER BY Omschrijving") Sub Release() Cnn.Close Set Cnn = Nothing Set rstTypes = Nothing Set rstArtTypes = Nothing Set rstArtikels = Nothing End Sub '-------------------------------------------------------------------- 'PARSE QUERYSTRING '-------------------------------------------------------------------- SearchString = Request.QueryString("product_search_text") TypeID = CInt(Request.QueryString("product_search_typeid")) AbsPage = CInt(Request.QueryString("page")) If AbsPage = 0 Then AbsPage = 1 '--------------- 'PAGE-PROCESSING '--------------- 'Set starters sql-string sql = "SELECT art.Omschrijving AS Omschrijving, art.Aankoopprijs + (art.Winstmarge*art.Aankoopprijs/100) AS VKP_ex, " sql = sql & " Btw" sql = sql & " FROM ART_ARTIKELS art " sql = sql & " LEFT JOIN ART_TYPES typ ON art.TypeID = typ.TypeID " sql = sql & " WHERE art.Publiceren = 1 " 'Search articles in types If Len(SearchString) > 0 Then Criteria = "" 'Replace possible typo's or incorrect syntax SearchString = Replace(SearchString,"'"," ") SearchString = Replace(SearchString,"-","") 'Split searchstring into 2 arrays aSearch = Split(SearchString," ") aSearch2 = Split(SearchString," ") 'Search aliases for types found in searchstring insql = "SELECT DISTINCT ItemID,SearchItem FROM ONL_SEARCH_ALIAS WHERE SearchItem IN (" For i = 0 To UBound(aSearch,1) If Len(Criteria) > 0 Then Criteria = Criteria & "," Criteria = Criteria & "'" & aSearch(i) & "'" Cnn.Execute "INSERT INTO ONL_SEARCHSTRINGS (SessionID,SearchDate,Value) VALUES (" & Session.SessionID & ",'" & MakeSQLDate(Day(Now()),Month(Now()),Year(Now())) & " " & Right("0" & Hour(Now()),2) & ":" & Right("0" & Minute(Now()),2) & ":" & Right("0" & Second(Now()),2) & "','" & Replace(aSearch(i),"'","''") & "')" Next insql = insql & Criteria & ")" Set rstArtTypes = Cnn.Execute(insql) 'Parse found types for use in sql-string While Not rstArtTypes.EOF If Len(TypeCriteria) > 0 Then TypeCriteria = TypeCriteria & "," TypeCriteria = TypeCriteria & rstArtTypes("ItemID") For i = 0 To UBound(aSearch,1) If UCase(aSearch(i)) = UCase(rstArtTypes("SearchItem")) Then aSearch2(i) = "" Next rstArtTypes.MoveNext Wend 'Search in types found in searchstring If Len(TypeCriteria) > 0 Then Criteria = "" For i = 0 To UBound(aSearch2,1) If Len(aSearch2(i)) > 0 Then Criteria = Criteria & " AND art.Omschrijving LIKE '%" & aSearch2(i) & "%'" End If Next sql = sql & " AND ((art.TypeID IN (" & TypeCriteria & ")" sql = sql & Criteria & ")" End If If Len(TypeCriteria) > 0 Then sql = sql & " OR (" Else sql = sql & " AND (" End If 'Criteria for text without found types in searchstring (search in whole database) Criteria = "" For i = 0 To UBound(aSearch,1) If Len(Criteria) > 0 Then Criteria = Criteria & " AND " Criteria = Criteria & "art.Omschrijving LIKE '%" & aSearch(i) & "%'" Next sql = sql & Criteria & ")" If Len(TypeCriteria) > 0 Then sql = sql & ")" If TypeID > 0 Then sql = sql & " AND art.TypeID = " & TypeID End If Else 'Display per selected type 'append typeid to sql-string sql = sql & " AND art.TypeID = " & TypeID End If 'Append sorting to sql-string sql = sql & " ORDER BY Omschrijving" rstArtikels.PageSize = 15 Call rstArtikels.Open(sql,Cnn,0,1) If Not rstArtikels.EOF Then rstArtikels.AbsolutePage = AbsPage End If %>

PRODUKTEN ZOEKEN: Omschrijving / type: Type selektie:   <% While Not rstTypes.EOF %> " <%If rstTypes("TypeID") = TypeID Then Response.Write " SELECTED "%>> <%=rstTypes("Omschrijving")%> <% rstTypes.MoveNext Wend %> Zoekfunctie resulteerde in <%=rstArtikels.RecordCount%> gevonden artikels <% If rstArtikels.PageCount > 1 Then If AbsPage > 1 Then Response.Write "Vorige | " Else If rstArtikels.PageCount > 1 Then Response.Write "Vorige | " End If End If For i = 1 To rstArtikels.PageCount If i <> rstArtikels.AbsolutePage Then Response.Write "" & i & " | " Else Response.Write "" & i & " | " End If Next If AbsPage < rstArtikels.PageCount Then Response.Write " Volgende " Else If rstArtikels.PageCount > 1 Then Response.Write " Volgende " End If End If End If %> <% bColor = True If rstArtikels.RecordCount > 0 Then %> <% i = 1 While (Not rstArtikels.EOF) And i <= rstArtikels.PageSize%> <% 'Compute Salesprice Inclusive VAT VKP_Exc = Round(NToZ(rstArtikels("VKP_Ex")),2) VKP_Inc = Round(VKP_Exc + (VKP_Exc * NToZ(rstArtikels("Btw"))/100) ,2) If VKP_Inc = 0 Then VKP_Inc = "On Call" If VKP_Exc = 0 Then VKP_Exc = "On Call"%> <% rstArtikels.MoveNext i = i + 1 bColor = Not bColor Wend %> Produktomschrijving BTW Inclusief BTW Exclusief ><%=rstArtikels("Omschrijving")%> ><%=VKP_Inc%> ><%=VKP_Exc%> <% Else If Searchstring <> "" Or TypeID <> 0 Then %> Er werden geen produkten gevonden die voldoen aan het zoekcriteria. <% End If End If %>
<% If rstArtikels.PageCount > 1 Then If AbsPage > 1 Then Response.Write "Vorige | " Else If rstArtikels.PageCount > 1 Then Response.Write "Vorige | " End If End If For i = 1 To rstArtikels.PageCount If i <> AbsPage Then Response.Write "" & i & " | " Else Response.Write "" & i & " | " End If Next If AbsPage < rstArtikels.PageCount Then Response.Write " Volgende " Else If rstArtikels.PageCount > 1 Then Response.Write " Volgende " End If End If End If %>

<% Release %>