|
<%
'------------------------- Make SQL ----------------------------------------------------------------------------
' ------------------------ SELECT ... FROM .... -----------------------------------------
'The SQL FROM part changes depending on if an Author or a Reference is specified.
'Author
sqlAuthor = ""
if author1<>"" then
sqlAuthor = sqlAuthor & " LEFT OUTER JOIN " & _
" AuthorTeamSequence S1 INNER JOIN " & _
" Author A1 ON S1.AuthorFk = A1.AuthorId ON S1.AuthorTeamFk = NomName.BasAuthorTeamFk OR " & _
" S1.AuthorTeamFk = NomName.AuthorTeamFk "
End if ' author1
if author2<>"" then
sqlAuthor = sqlAuthor & " LEFT OUTER JOIN " & _
" AuthorTeamSequence S2 INNER JOIN " & _
" Author A2 ON S2.AuthorFk = A2.AuthorId ON NomName.AuthorTeamFk = S2.AuthorTeamFk OR " & _
" NomName.BasAuthorTeamFk = S2.AuthorTeamFk "
end if ' author2
'Reference
sqlReference = ""
if mclYear <>"" then
sqlReference = " LEFT OUTER JOIN Reference LEFT OUTER JOIN Reference InRef ON Reference.InRefFk = InRef.RefId " & _
" ON NomName.NomRefFk = Reference.RefId "
end if
'Accepted, Synonym, Missaplied -SQL
SQLAccSelect = " SELECT DISTINCT NomName.FullNameCache AS FullName, PTaxonAcc.DoubtfulFlag, PTaxonAcc.IdInSource as MCLId, " & _
" PTaxonAcc.PTNameFk, PTaxonAcc.PTRefFK, PTaxonAcc.MCLOccurenceString as AreaOcc, Fact.Fact as occState, NomName.RankFk " & _
" FROM Name NomName INNER JOIN PTaxon PTaxonAcc ON NomName.NameId = PTaxonAcc.PTNameFk " & _
" LEFT OUTER JOIN Fact ON PTaxonAcc.PTNameFk = Fact.PTNameFk AND PTaxonAcc.PTRefFk = Fact.PTRefFk AND Fact.FactCategoryFk = 20 " & _
sqlReference & _
sqlAuthor
SQLSynSelect = " SELECT DISTINCT NomName.FullNameCache AS FullName, PTaxonSyn.DoubtfulFlag, PTaxonSyn.PTNameFk, PTaxonSyn.PTRefFK " & _
" FROM Name NomName INNER JOIN RelPTaxon " & _
" INNER JOIN PTaxon PTaxonSyn ON RelPTaxon.PTNameFk1 = PTaxonSyn.PTNameFk AND RelPTaxon.PTRefFk1 = PTaxonSyn.PTRefFk " & _
" INNER JOIN PTaxon PTaxonAcc ON RelPTaxon.PTNameFk2 = PTaxonAcc.PTNameFk AND RelPTaxon.PTRefFk2 = PTaxonAcc.PTRefFk " & _
" INNER JOIN Name AccName ON AccName.NameId = PTaxonAcc.PTNameFk " & _
" ON NomName.NameId = PTaxonSyn.PTNameFk " & _
" LEFT OUTER JOIN Fact ON PTaxonAcc.PTNameFk = Fact.PTNameFk AND PTaxonAcc.PTRefFk = Fact.PTRefFk AND Fact.FactCategoryFk = 20 " & _
sqlReference & _
sqlAuthor
SQLMisSelect = " SELECT DISTINCT NomName.NameCache AS Name" & _
" FROM Name NomName INNER JOIN RelPTaxon " & _
" INNER JOIN PTaxon PTaxonSyn ON RelPTaxon.PTNameFk1 = PTaxonSyn.PTNameFk AND RelPTaxon.PTRefFk1 = PTaxonSyn.PTRefFk " & _
" INNER JOIN PTaxon PTaxonAcc ON RelPTaxon.PTNameFk2 = PTaxonAcc.PTNameFk AND RelPTaxon.PTRefFk2 = PTaxonAcc.PTRefFk " & _
" INNER JOIN Name AccName ON AccName.NameId = PTaxonAcc.PTNameFk " & _
" ON NomName.NameId = PTaxonSyn.PTNameFk " & _
" LEFT OUTER JOIN Fact ON PTaxonAcc.PTNameFk = Fact.PTNameFk AND PTaxonAcc.PTRefFk = Fact.PTRefFk AND Fact.FactCategoryFk = 20 " & _
""
' ----------------------------- SQL WHERE ------------------------------------------
sqlWhereName=""
sqlWhereAutYear=""
sqlWhereArea=""
sqlWhereFam=""
' First take care that the entry is correct or return the errors!
errorcode=2
if mclName = "" then
mclName = "*"
end if
mclName=Replace(mclName, "*", "%")
mclName=Replace(mclName, "+", " ")
mclName=Replace(mclName, "'", "''")
mclName=Replace(mclName, "e", "[eë]")
sqlWhereName=sqlWhereName & "(NomName.NameCache LIKE '"& mclName &"')"
'family
sqlWhereFam = ""
if family <>"" then
sqlWhereFam = sqlWhereFam & " AND AccName.MCLFamilyName = '" & family & "'"
end if
'genus
if genus <>"" then
sqlWhereFam = sqlWhereFam & " AND AccName.Genus = '" & genus & "'"
end if
'year
if mclYear <>"" then
sqlWhereAutYear = sqlWhereAutYear & " And ( (dbo.f_MCL_inYear(Reference.RefYear, '" & mclYear & "') = 1 ) " & _
" OR (dbo.f_MCL_inYear(InRef.RefYear, '" & mclYear & "') = 1) ) "
end if
'author
'CANDO
'Ersetzten von Sonderzeichen
'replaceSpecialCharacter Funktioniert leider nicht richtig gut , vielleicht mit compareOption besser ??
'author1=replaceSpecialCharacter(author1)
'author2=replaceSpecialCharacter(author2)
'Response.Write("Authoer1:" & author1 & " " )
'Response.Write("Authoer2:" & author2)
if author1 <>"" or author2 <> "" then
sqlWhereAuthor = ""
author1=Replace(author1, "*", "%")
author1=Replace(author1, "+", " ")
author1=Replace(author1, "'", "''")
author2=Replace(author2, "*", "%")
author2=Replace(author2, "+", " ")
author2=Replace(author2, "'", "''")
if author1 <>"" then
sqlWhereAuthor = sqlWhereAuthor & "(A1.Abbrev LIKE '" & author1 & "')"
'insert operator if 2 authors are queried
if author2 <> "" then
if autBool = "or" then
sqlAutBool = " OR "
else
sqlAutBool = " AND "
end if
sqlWhereAuthor= sqlWhereAuthor & " " & autBool & " "
End if
End if
if author2 <>"" then
sqlWhereAuthor = sqlWhereAuthor & "(A2.Abbrev LIKE '" & author2 & "')"
End if
sqlWhereAutYear = sqlWhereAutYear & " AND (" & sqlWhereAuthor & ")"
end if
'---- status --------------------------
sqlStatus = ""
if allStatus = "e" then
sqlStatus = " AND Fact = 'E' "
elseif allStatus = "x" then
sqlStatus = " AND Fact = 'X' "
else
'Do nothing
end if
'---- Area ----------------------------
sqlWhereArea = "1=1"
for i = 1 to areaCount
area = areaName(i)
op = areaBool(i)
st = mclStatus(i)
if area = "" then exit for 'the coming areas must be empty as well
ar = left(area,2)
'local occurence includes status (endemic/ not native)
sqlLocal = ""
if st = "" then
sqlLocal = " PTaxonAcc.MCLOccurenceString LIKE '%" & ar & "[^a-z]%' OR PTaxonAcc.MCLOccurenceString LIKE '%" & left(area,2) & "' " '[^a-z]% is to avoid matches like ALi
'OLD
'elseif st = "e" then
'sqlLocal = " PTaxonAcc.MCLOccurenceString LIKE '%" & left(area,2) & "%' AND NOT PTaxonAcc.MCLOccurenceString LIKE '%![%" & left(area,2) & "[^a-z]%!]%' ESCAPE '!'"
'sqlLocal = " (PTaxonAcc.MCLOccurenceString LIKE '" & left(area,2) & " %' OR PTaxonAcc.MCLOccurenceString LIKE '%[ !?!-]" & left(area,2) & "%' ESCAPE '!' )"
'elseif st = "n" then
'sqlLocal = " PTaxonAcc.MCLOccurenceString LIKE '%![%" & left(area,2) & "[^a-z]%!]%' ESCAPE '!'"
elseif st = "+" then
sqlLocal = " PTaxonAcc.MCLOccurenceString LIKE '% " & ar & "%' OR PTaxonAcc.MCLOccurenceString LIKE '" & ar & "[^a-z]%' OR PTaxonAcc.MCLOccurenceString LIKE '" & ar & "'"
elseif st = "-" then
sqlLocal = " PTaxonAcc.MCLOccurenceString LIKE '%(-" & ar & "%)'"
elseif st = "?" then
sqlLocal = " PTaxonAcc.MCLOccurenceString LIKE '%?" & ar & "%'"
elseif st = "D" then
sqlLocal = " PTaxonAcc.MCLOccurenceString LIKE '%d" & ar & "%'"
elseif st = "E" then
sqlLocal = " PTaxonAcc.MCLOccurenceString LIKE '%†" & ar & "%'"
elseif st = "A" then
sqlLocal = " PTaxonAcc.MCLOccurenceString LIKE '%a" & ar & "%'"
elseif st = "N" then
sqlLocal = " PTaxonAcc.MCLOccurenceString LIKE '%n" & ar & "%'"
elseif st = "P" then
sqlLocal = " PTaxonAcc.MCLOccurenceString LIKE '%p" & ar & "%'"
end if
sqlLocal = "(" & sqlLocal & ")"
'area with bool operator
if i = 1 then
sqlWhereArea = op & " " & sqlLocal
elseif op <> "AllNot" then
if op = "NOT" then op = "AND NOT"
sqlWhereArea = sqlWhereArea & " " & op & " " & sqlLocal
elseif op = "AllNot" then
sqlWhereArea = " (" & sqlWhereArea & ") " & " AND NOT " & sqlLocal
end if
Next 'i
sqlWhereArea = " AND (" & sqlWhereArea & ")"
%>
<%
' ------------------------ SQL Order By -------------------------------------
SELECT CASE order
CASE "mcl"
sqlOrder = " PTaxonAcc.IdInSource "
CASE "name"
sqlOrder = " NomName.FullNameCache "
CASE "rank"
sqlOrder = " NomName.RankFk, NomName.FullNameCache "
CASE ELSE
sqlOrder = " NomName.FullNameCache "
END SELECT
'------------------------- Complete SQL --------------------------------------
SQLAcc = SQLAccSelect & _
" WHERE " & _
sqlWhereName & sqlWhereAutYear & _
sqlWhereArea & sqlWhereFam & _
sqlStatus & _
" AND (PTaxonAcc.IdInSource <> '') " & _
" ORDER BY "& sqlOrder
SQLAcc = Replace(SQLAcc, "AccName.", "NomName.") 'AccName and NomName are the same for accepted taxa, a AccName-Table does not exist in the query
SQLSyn = SQLSynSelect & _
" WHERE "& sqlWhereName & sqlWhereAutYear & _
sqlWhereArea & sqlWhereFam & _
sqlStatus & _
" AND (RelPTaxon.RelQualifierFk = 2) " & _
" ORDER BY NomName.FullNameCache "
'wenn Author oder Jahr ausgewählt wurden, wird kein Datensatz angezeigt, da Missapplied names ohne Author und Jahr
if sqlWhereAutYear <> "" then sqlWhereAutYear = " AND (1=0)" 'Missaplied Names don't have Author or year
SQLMis = SQLMisSelect & _
" WHERE "& sqlWhereName & sqlWhereAutYear & _
sqlWhereArea & sqlWhereFam & _
sqlStatus & _
" AND (RelPTaxon.RelQualifierFk = 3) " & _
" ORDER BY NomName.NameCache "
'-------- error handling ---------------
'name
'Replace all wildcards by '*'
mclNameError = Replace(mclName, "_","*")
mclNameError = Replace(mclNameError, "[", "*")
mclNameError = Replace(mclNameError, "]", "*")
mclNameError = Replace(mclNameError, "^", "*")
if Instr(mclNameError,"%") = 0 then
errorcode = 0
else
errorcode = 2
pos = Instr(mclNameError,"%")
do
pos = Instr(mclNameError,"%")
if pos > 2 then
errorcode = 0
else
if pos > 1 then
errorcode = 1
end if
end if
mclNameError = mid( mclNameError, pos +1 )
Loop while pos < 2 AND pos > 0
End if
'family - if family is chosen errorcode = 0 except Leuminosae, Caryophyllae, Crudiferae and Labiatae.
'The last need at least one character of the name chosen
if family <>"" then
'errorcode = errorcode - 1
errorcode = 0
end if
'genus + year
if genus <>"" or mclYear <>"" then
errorcode = 0
end if
'author
authorError1 = Replace(author1, "_", "*")
authorError2 = Replace(author2, "_", "*")
if Instr(authorError1, "*") > 1 or Instr(authorError2, "*") > 1 then
errorcode = errorcode - 1
end if
'area
if areaName(1) <> "" and areaBool(1)<>"NOT" then
errorcode = errorcode = errorcode - 1
end if
if errorcode > 0 then %>
Your query is too unspecific. Please specify your query.
<% elseif strName="What did you do in Berlin?" then %>
<% ' This sentence is just for testing %>
<%Response.write("![]()
Erasmus in Berlin 2002/2003. Viel Spass!!")
' ------------- error handling end--------------
'------------------------------ BEGIN DATABASE REQUEST ---------------------------------
elseif (1= 1) Then '(1=0) for debugging
' **************************** Accepted Taxa *************************************
if (debug = true) Then Response.write("SQLAccepted:
" & SQLAcc & "
")
if (debug = true) Then Response.write("SQL_Name:
" & sqlWhereName & "
")
if (debug = true) Then Response.write("SQL_AutYear:
" & sqlWhereAutYear & "
")
if (debug = true) Then Response.write("SQL_Area:
" & sqlWhereArea & "
")
if (debug = true) Then Response.write("SQL_Fam:
" & sqlWhereFam & "
")
Set RAcc = Server.CreateObject("ADODB.Recordset")
'elseif (1=0) then 'for debugging
openRecordset RAcc, SQLAcc
If Err.number <> 0 then
TrapError Err.description
End If
'-------- error handling 2---------------
if RAcc.RecordCount < 0 then
TrapError Err.description
elseif RAcc.RecordCount > maxRecords then %>
Too many hits. Please specify your query more in detail.
<%
else
%>
- Accepted taxa (<%=RAcc.RecordCount%>)
|
<% if RAcc.RecordCount > 0 then %>
<%else %>
-
<%end if%>
<%
RAcc.close
set RAcc=Nothing
%>
|
<%
' **************************** Synonym Names *************************************
' if (debug = false) Then Response.write("SQLSynonym:
" & SQLSynonym & "
")
'elseif(1=0) Then
Set RSyn = Server.CreateObject("ADODB.Recordset")
openRecordset RSyn, SQLSyn
If Err.number <> 0 then
TrapError Err.description
else
%>
- Synonyms (<%=RSyn.RecordCount%>)
<%
End If 'Synonyms END
' **************************** Misapplied Names *************************************
' if (debug = false) Then Response.write("SQLSynonym:
" & SQLSynonym & "
")
Set RMis = Server.CreateObject("ADODB.Recordset")
'else
openRecordset RMis, SQLMis
If Err.number <> 0 then
TrapError Err.description
ELSE
%>
- Misapplied names (<%=RMis.RecordCount%>)
<%
End If 'MisappliedNames
end if 'errorhandling 2
end if 'end of error/ dataoutput handling
ProcessErrors(debug)
%>
|
|
