% @Language = "VBScript" %> <% cat = "ana" %> <% SearchClass = request.querystring("sc") SearchMan = request.querystring("co") SearchUID = request.querystring("tester") 'IF SearchASS = "" THEN 'SearchASS = "[a-z]%" 'END IF IF SearchClass = "" THEN SearchClass = request.form("SearchClass") END IF commaplaceCLASS = InStr(SearchClass,",") IF commaplaceCLASS <> 0 THEN commaplaceCLASS = commaplaceCLASS +1 ' add to delete comma too SearchClass = Mid(SearchClass,commaplaceCLASS) ' return all after comma place END IF IF SearchClass = "" THEN SearchClass = "all" END IF IF SearchMan = "A" THEN ' fix SearchMan = "A&T" END IF IF SearchMan = "B" THEN ' fix SearchMan = "B&E Scientific" END IF IF SearchMan = "J" THEN ' J&J fix SearchMan = "J&J" END IF IF SearchMan = "" THEN SearchMan = request.form("SearchMan") END IF IF SearchMan = "" THEN SearchMan = "all" END IF commaplaceMAN = InStr(SearchMan,",") IF commaplaceMAN <> 0 THEN commaplaceMAN = commaplaceMAN +1 ' add to delete comma too SearchMan = Mid(SearchMan,commaplaceMAN) ' return all after comma place END IF SearchClass = Trim(SearchClass) SearchMan = Trim(SearchMan) ' SearchASS split and count Assays 1-5 SearchASS = request.form("SearchASS") SearchASSfunc = request.form("SearchASS") SearchASSfunc = replace (SearchASSfunc, ", ",",") strArray = Split(SearchASS,",") Dim i For i = 0 to Ubound(strArray) IF (i) = 0 THEN SearchASS = TRIM(strArray(i)) iJoinLIST = SearchASS ELSEIF (i) = 1 THEN SearchASS2 = TRIM(strArray(i)) iJoin = 2 iJoinLIST = iJoinLIST & ", " & SearchASS2 ELSEIF (i) = 2 THEN SearchASS3 = TRIM(strArray(i)) iJoinLIST = iJoinLIST & ", " & SearchASS3 iJoin = 3 ELSEIF (i) = 3 THEN SearchASS4 = TRIM(strArray(i)) iJoinLIST = iJoinLIST & ", " & SearchASS4 iJoin = 4 ELSEIF (i) = 4 THEN SearchASS5 = TRIM(strArray(i)) iJoinLIST = iJoinLIST & ", " & SearchASS5 iJoin = 5 ELSEIF (i) = 5 THEN SearchASS5 = TRIM(strArray(i)) iJoinLIST = iJoinLIST & ", " & SearchASS5 iJoin = 6 ELSEIF (i) = 6 THEN SearchASS5 = TRIM(strArray(i)) iJoinLIST = iJoinLIST & ", " & SearchASS5 iJoin = 7 ELSEIF (i) = 7 THEN SearchASS5 = TRIM(strArray(i)) iJoinLIST = iJoinLIST & ", " & SearchASS5 iJoin = 8 ELSEIF (i) = 8 THEN SearchASS5 = TRIM(strArray(i)) iJoinLIST = iJoinLIST & ", " & SearchASS5 iJoin = 9 ELSEIF (i) = 9 THEN SearchASS5 = TRIM(strArray(i)) iJoinLIST = iJoinLIST & ", " & SearchASS5 iJoin = 10 ELSEIF (i) = 10 THEN SearchASS5 = TRIM(strArray(i)) iJoinLIST = iJoinLIST & ", " & SearchASS5 iJoin = 11 END IF Next %>
|
<%
' ================== Declare SQL Query ===============
IF SearchASS = "" THEN
IF SearchClass = "all" AND SearchMan = "all" THEN
strSQL = "SELECT * FROM SearchProductSelectionList WHERE MarketPublish = 'Yes' ORDER BY DiamondPN ASC;"
ELSEIF SearchClass <> "all" AND SearchMan = "all" THEN
strSQL = "SELECT * FROM SearchProductSelectionList WHERE Classification = '" & SearchClass &"' AND MarketPublish = 'Yes' ORDER BY DiamondPN ASC;"
ELSEIF SearchClass = "all" AND SearchMan <> "all" THEN
strSQL = "SELECT * FROM SearchProductSelectionList WHERE ProductBrand LIKE '" & SearchMan &"' AND MarketPublish = 'Yes' ORDER BY DiamondPN ASC;"
' test Urinalysis
' ELSEIF SearchClass = "Urinalysis" THEN
' strSQL = "SELECT * FROM SearchProductSelectionList ORDER BY DiamondPN ASC;"
ELSE
strSQL = "SELECT * FROM SearchProductSelectionList WHERE Classification = '" & SearchClass &"' AND ProductBrand LIKE '" & SearchMan &"' AND MarketPublish = 'Yes' ORDER BY DiamondPN ASC;"
END IF
ELSE ' Assays have been chosen
IF SearchMan = "all" THEN
SearchManJ = "[a-z]%"
ELSE
SearchManJ = SearchMan
END IF
IF SearchClass = "all" THEN
SearchClassJ = "[a-z]%"
ELSE
SearchClassJ = SearchClass
END IF
' ================== Declare SQL Query ===============
IF iJoin= 2 THEN
strSQL = "SELECT a.DiamondPN, a.ModelNumber, a.ProductBrand, a.Classification FROM "
strSQL = strSQL & " (SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay = '" & SearchASS & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') a "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & " (SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay = '" & SearchASS2 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') b "
strSQL = strSQL & " ON a.DiamondPN = b.DiamondPN "
strSQL = strSQL & " ORDER by DiamondPN ASC ; "
ELSEIF iJoin= 3 THEN
strSQL = "SELECT a.DiamondPN, a.ModelNumber, a.ProductBrand, a.Classification FROM "
strSQL = strSQL & " (SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') a "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & " (SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS2 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') b "
strSQL = strSQL & " ON a.DiamondPN = b.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS3 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') c "
strSQL = strSQL & " ON a.DiamondPN = c.DiamondPN "
strSQL = strSQL & " ORDER by DiamondPN ASC ; "
ELSEIF iJoin= 4 THEN
strSQL = "SELECT a.DiamondPN, a.ModelNumber, a.ProductBrand, a.Classification FROM "
strSQL = strSQL & " (SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') a "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & " (SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS2 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') b "
strSQL = strSQL & " ON a.DiamondPN = b.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS3 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') c "
strSQL = strSQL & " ON a.DiamondPN = c.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS4 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') d "
strSQL = strSQL & " ON a.DiamondPN = d.DiamondPN "
strSQL = strSQL & " ORDER by DiamondPN ASC ; "
ELSEIF iJoin= 5 THEN
strSQL = "SELECT a.DiamondPN, a.ModelNumber, a.ProductBrand , a.Classification FROM "
strSQL = strSQL & " (SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') a "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & " (SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS2 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') b "
strSQL = strSQL & " ON a.DiamondPN = b.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS3 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') c "
strSQL = strSQL & " ON a.DiamondPN = c.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS4 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') d "
strSQL = strSQL & " ON a.DiamondPN = d.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') e "
strSQL = strSQL & " ON a.DiamondPN = e.DiamondPN "
strSQL = strSQL & " ORDER by DiamondPN ASC ; "
ELSEIF iJoin= 6 THEN
strSQL = "SELECT a.DiamondPN, a.ModelNumber, a.ProductBrand , a.Classification FROM "
strSQL = strSQL & " (SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') a "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & " (SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS2 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') b "
strSQL = strSQL & " ON a.DiamondPN = b.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS3 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') c "
strSQL = strSQL & " ON a.DiamondPN = c.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS4 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') d "
strSQL = strSQL & " ON a.DiamondPN = d.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') e "
strSQL = strSQL & " ON a.DiamondPN = e.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') f "
strSQL = strSQL & " ON a.DiamondPN = f.DiamondPN "
strSQL = strSQL & " ORDER by DiamondPN ASC ; "
ELSEIF iJoin= 7 THEN
strSQL = "SELECT a.DiamondPN, a.ModelNumber, a.ProductBrand , a.Classification FROM "
strSQL = strSQL & " (SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') a "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & " (SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS2 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') b "
strSQL = strSQL & " ON a.DiamondPN = b.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS3 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') c "
strSQL = strSQL & " ON a.DiamondPN = c.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS4 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') d "
strSQL = strSQL & " ON a.DiamondPN = d.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') e "
strSQL = strSQL & " ON a.DiamondPN = e.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') f "
strSQL = strSQL & " ON a.DiamondPN = f.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') g "
strSQL = strSQL & " ON a.DiamondPN = g.DiamondPN "
strSQL = strSQL & " ORDER by DiamondPN ASC ; "
ELSEIF iJoin= 8 THEN
strSQL = "SELECT a.DiamondPN, a.ModelNumber, a.ProductBrand , a.Classification FROM "
strSQL = strSQL & " (SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') a "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & " (SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS2 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') b "
strSQL = strSQL & " ON a.DiamondPN = b.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS3 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') c "
strSQL = strSQL & " ON a.DiamondPN = c.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS4 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') d "
strSQL = strSQL & " ON a.DiamondPN = d.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') e "
strSQL = strSQL & " ON a.DiamondPN = e.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') f "
strSQL = strSQL & " ON a.DiamondPN = f.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') g "
strSQL = strSQL & " ON a.DiamondPN = g.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') h "
strSQL = strSQL & " ON a.DiamondPN = h.DiamondPN "
strSQL = strSQL & " ORDER by DiamondPN ASC ; "
ELSEIF iJoin= 9 THEN
strSQL = "SELECT a.DiamondPN, a.ModelNumber, a.ProductBrand , a.Classification FROM "
strSQL = strSQL & " (SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') a "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & " (SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS2 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') b "
strSQL = strSQL & " ON a.DiamondPN = b.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS3 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') c "
strSQL = strSQL & " ON a.DiamondPN = c.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS4 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') d "
strSQL = strSQL & " ON a.DiamondPN = d.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') e "
strSQL = strSQL & " ON a.DiamondPN = e.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') f "
strSQL = strSQL & " ON a.DiamondPN = f.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') g "
strSQL = strSQL & " ON a.DiamondPN = g.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') h "
strSQL = strSQL & " ON a.DiamondPN = h.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') k "
strSQL = strSQL & " ON a.DiamondPN = k.DiamondPN "
strSQL = strSQL & " ORDER by DiamondPN ASC ; "
ELSEIF iJoin= 10 THEN
strSQL = "SELECT a.DiamondPN, a.ModelNumber, a.ProductBrand , a.Classification FROM "
strSQL = strSQL & " (SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') a "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & " (SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS2 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') b "
strSQL = strSQL & " ON a.DiamondPN = b.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS3 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') c "
strSQL = strSQL & " ON a.DiamondPN = c.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS4 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') d "
strSQL = strSQL & " ON a.DiamondPN = d.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') e "
strSQL = strSQL & " ON a.DiamondPN = e.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') f "
strSQL = strSQL & " ON a.DiamondPN = f.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') g "
strSQL = strSQL & " ON a.DiamondPN = g.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') h "
strSQL = strSQL & " ON a.DiamondPN = h.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') k "
strSQL = strSQL & " ON a.DiamondPN = k.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') l "
strSQL = strSQL & " ON a.DiamondPN = l.DiamondPN "
strSQL = strSQL & " ORDER by DiamondPN ASC ; "
ELSEIF iJoin= 11 THEN
strSQL = "SELECT a.DiamondPN, a.ModelNumber, a.ProductBrand , a.Classification FROM "
strSQL = strSQL & " (SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') a "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & " (SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS2 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') b "
strSQL = strSQL & " ON a.DiamondPN = b.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS3 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') c "
strSQL = strSQL & " ON a.DiamondPN = c.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS4 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') d "
strSQL = strSQL & " ON a.DiamondPN = d.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') e "
strSQL = strSQL & " ON a.DiamondPN = e.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') f "
strSQL = strSQL & " ON a.DiamondPN = f.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') g "
strSQL = strSQL & " ON a.DiamondPN = g.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') h "
strSQL = strSQL & " ON a.DiamondPN = h.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') k "
strSQL = strSQL & " ON a.DiamondPN = k.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') l "
strSQL = strSQL & " ON a.DiamondPN = l.DiamondPN "
strSQL = strSQL & " INNER JOIN "
strSQL = strSQL & "(SELECT DISTINCT DiamondPN, ModelNumber, ProductBrand, Classification FROM SearchProductTestAssays WHERE TestAssay='" & SearchASS5 & "' AND ProductBrand LIKE '" & SearchManJ & "' AND Classification LIKE '" & SearchClassJ & "' AND MarketPublish = '1') m "
strSQL = strSQL & " ON a.DiamondPN = m.DiamondPN "
strSQL = strSQL & " ORDER by DiamondPN ASC ; "
ELSE
' strSQL = "SELECT DISTINCT DiamondPN, Classification, ModelNumber, ProductBrand from SearchProductTestAssays WHERE TestAssay = '" & SearchASS & "' AND ProductBrand = '" & SearchMan & "' AND Classification = '" & SearchClass & "' AND MarketPublish = '1' ORDER by DiamondPN ASC ;"
IF SearchClass = "all" AND SearchMan = "all" THEN
strSQL = "SELECT DISTINCT DiamondPN, Classification, ModelNumber, ProductBrand from SearchProductTestAssays WHERE TestAssay = '" & SearchASS & "' AND MarketPublish = '1' ORDER by DiamondPN ASC ;"
ELSEIF SearchClass = "all" AND SearchMan <> "all" THEN
strSQL = "SELECT DISTINCT DiamondPN, Classification, ModelNumber, ProductBrand from SearchProductTestAssays WHERE TestAssay = '" & SearchASS & "' AND ProductBrand = '" & SearchMan & "' AND MarketPublish = '1' ORDER by DiamondPN ASC ;"
ELSEIF SearchClass <> "all" AND SearchMan = "all" THEN
strSQL = "SELECT DISTINCT DiamondPN, Classification, ModelNumber, ProductBrand from SearchProductTestAssays WHERE TestAssay = '" & SearchASS & "' AND Classification = '" & SearchClass & "' AND MarketPublish = '1' ORDER by DiamondPN ASC ;"
ELSE
strSQL = "SELECT DISTINCT DiamondPN, Classification, ModelNumber, ProductBrand from SearchProductTestAssays WHERE TestAssay = '" & SearchASS & "' AND ProductBrand = '" & SearchMan & "' AND Classification = '" & SearchClass & "' AND MarketPublish = '1' ORDER by DiamondPN ASC ;"
END IF
END IF
END IF
' ================== Set SQL Query for retrieving search results ===============
' FUNC Stuff =======================
'IF SearchClass = "all" THEN
' SearchClass = "Blood Gas,Electrolyte,Chemistry,Immunology,Hematology,Coagulation"
'END IF
'IF SearchMan = "all" THEN
' SearchMan = SearchManfunc
'END IF
'strSQL = "select * from dbo.fncRetrInstr('" & SearchClass & "','" & SearchMan & "','" & SearchASSfunc & "') order by DiamondPN ASC ;"
'strSQL = "select * from dbo.fncRetrInstr('Electrolyte','BioMaxima,Audicom','Calcium (Ca),Chloride (Cl),pH')"
'Response.Write(strSQL)
' / FUNC Stuff =======================
%>
<%
' COUNTER
oRS.Open strSQL,oConn
rowc = 0
Do While NOT oRS.Eof
rowc = rowc +1
oRS.MoveNext
Loop
GRIDCOUNT= rowc
oRS.Close
' / counter
' ================== Open Recordset / Return Records ===============
oRS.Open strSQL,oConn
' ================== Data Found? ===============
If oRS.EOF Then
Response.Write("No Analyzers Match These Assay Selections") Response.Write(""& iJoinLIST &" Total Results: " & GRIDCOUNT & " ")
IF Session("FAVcount") = "" THEN
Session("FAVcount") = 0
END IF
%>
" & (strSQL) & " " ' ================== Close Conn / Recordset =============== oRS.Close Set oRS=nothing oConn.Close Set oConn=nothing %> |