/SQL/scripts/db/search.asp
ASP | 256 lines | 235 code | 15 blank | 6 comment | 1 complexity | 4dd1be4cbb8768786b6d297ce17a4aa1 MD5 | raw file
1<!-- #INCLUDE FILE="../inc/mla_sql_include.asp" --> 2<% 3 Dim myNodeId, myDbName, myObjName, myObjType, myStrTree, myStrClass 4 Dim myObjStr, i 5 Dim myArrCol, myColCount 6 Dim myColStr, myWhereStr, myBoolOp 7 8 myNodeId = Request.QueryString("nid") 9 myDbName = Request.QueryString("db") 10 myObjName = Request.QueryString("obj") 11 myObjType = Request.QueryString("type") 12 13 If Request.Form("mla_submit") <> "" Then 14 openConnection 15 Select Case myObjType 16 Case "tbl" : myArrCol = getSimpleTblCol(myDbName, myObjName) 17 Case "view" : myArrCol = getSimpleViewCol(myDbName, myObjName) 18 End Select 19 If isArray(myArrCol) Then myColCount = UBound(myArrCol, 2) Else myColCount = -1 End If 20 myBoolOp = Request.Form("mla_match") 21 myWhereStr = "" 22 For i = 0 To myColCount 23 If Request.Form("mla_display_" & i) <> "" Then myColStr = myColStr & "[" & rembracket(myArrCol(0, i)) & "], " 24 If Request.Form("mla_op_" & i) <> "-1" And Request.Form("mla_op_" & i) <> "" Then 25 If myWhereStr <> "" Then myWhereStr = myWhereStr & " " & myBoolOp 26 If Request.Form("mla_bool_" & i) <> "-1" Then myWhereStr = myWhereStr & " " & Request.Form("mla_bool_" & i) 27 myWhereStr = myWhereStr & " " & " (" 28 myWhereStr = myWhereStr & "[" & rembracket(myArrCol(0, i)) & "]" 29 If Request.Form("mla_op_" & i) <> "IS NULL" And Request.Form("mla_op_" & i) <> "IS NOT NULL" Then 30 myWhereStr = myWhereStr & " " 31 If myArrCol(3, i) = 1 Or myArrCol(3, i) = 2 Then 32 Select Case Request.Form("mla_op_" & i) 33 Case "EXACT" : myWhereStr = myWhereStr & " LIKE '" & remquote(Request.Form("mla_value_" & i)) & "'" 34 Case "STARTS" : myWhereStr = myWhereStr & " LIKE '" & remquote(Request.Form("mla_value_" & i)) & "%'" 35 Case "ENDS" : myWhereStr = myWhereStr & " LIKE '%" & remquote(Request.Form("mla_value_" & i)) & "'" 36 Case "CONTAINS" : myWhereStr = myWhereStr & " LIKE '%" & remquote(Request.Form("mla_value_" & i)) & "%'" 37 Case Else : myWhereStr = myWhereStr & " " & Request.Form("mla_op_" & i) & " '" & remquote(Request.Form("mla_value_" & i)) & "'" 38 End Select 39 ElseIf myArrCol(3, i) = 4 Then 40 myWhereStr = myWhereStr & " " & Request.Form("mla_op_" & i) 41 myWhereStr = myWhereStr & " CONVERT(DATETIME, '" & str2date(Request.Form("mla_value_" & i)) & "', 112)" 42 If Request.Form("mla_op_" & i) = "BETWEEN" Or Request.Form("mla_op_" & i) = "NOT BETWEEN" Then 43 myWhereStr = myWhereStr & " AND CONVERT(DATETIME, '" & str2date(Request.Form("mla_value2_" & i)) & "', 112)" 44 End If 45 ElseIf myArrCol(3, i) = 5 Then 46 Select Case Request.Form("mla_op_" & i) 47 Case "IS TRUE" : myWhereStr = myWhereStr & " = 1" 48 Case Else : myWhereStr = myWhereStr & " = 0" 49 End Select 50 ElseIf myArrCol(3, i) = 6 Then 51 myWhereStr = myWhereStr & " " & Request.Form("mla_op_" & i) & " '" & remquote(Request.Form("mla_value_" & i)) & "'" 52 Else 53 myWhereStr = myWhereStr & " " & Request.Form("mla_op_" & i) 54 myWhereStr = myWhereStr & " " & Request.Form("mla_value_" & i) 55 If Request.Form("mla_op_" & i) = "BETWEEN" Or Request.Form("mla_op_" & i) = "NOT BETWEEN" Then 56 myWhereStr = myWhereStr & " AND " & Request.Form("mla_value2_" & i) 57 End If 58 End If 59 Else 60 myWhereStr = myWhereStr & " " & Request.Form("mla_op_" & i) 61 End If 62 myWhereStr = myWhereStr & ")" 63 End If 64 Next 65 If myColStr <> "" Then myColStr = Left(myColStr, Len(myColStr)-2) 66 closeConnection 67 Response.Redirect "content.asp?nid=" & myNodeId & "&db=" & Server.URLEncode(myDbName) & "&obj=" & Server.URLEncode(myObjName) & "&type=" & Server.URLEncode(myObjType) & "&col=" & Server.URLEncode(myColStr) & "&cnd=" & Server.URLEncode(myWhereStr) 68 Response.End 69 End If 70 71 openConnection 72 Select Case myObjType 73 Case "tbl" : 74 myStrTree = getTreeStr(Mid(myNodeId, 2) & "_1_3", Array(myDbName, myObjName)) 75 myArrCol = getSimpleTblCol(myDbName, myObjName) 76 Case "view" : 77 myStrTree = getTreeStr(Mid(myNodeId, 2) & "_2_3", Array(myDbName, myObjName)) 78 myArrCol = getSimpleViewCol(myDbName, myObjName) 79 End Select 80 closeConnection 81 If isArray(myArrCol) Then myColCount = UBound(myArrCol, 2) Else myColCount = -1 End If 82 83 Dim myArrBool(0, 0) 84 myArrBool(0, 0) = "NOT" 85 86 ' Numeric 87 Dim myArrOpNumeric(0, 9) 88 myArrOpNumeric(0, 0) = "=" 89 myArrOpNumeric(0, 1) = ">" 90 myArrOpNumeric(0, 2) = "<" 91 myArrOpNumeric(0, 3) = ">=" 92 myArrOpNumeric(0, 4) = "<=" 93 myArrOpNumeric(0, 5) = "<>" 94 myArrOpNumeric(0, 6) = "IS NULL" 95 myArrOpNumeric(0, 7) = "IS NOT NULL" 96 myArrOpNumeric(0, 8) = "BETWEEN" 97 myArrOpNumeric(0, 9) = "NOT BETWEEN" 98 99 ' Alpha 100 Dim myArrOpAlpha(1, 6) 101 myArrOpAlpha(0, 0) = myTObj.getTerm(430) 102 myArrOpAlpha(1, 0) = "EXACT" 103 myArrOpAlpha(0, 1) = myTObj.getTerm(431) 104 myArrOpAlpha(1, 1) = "STARTS" 105 myArrOpAlpha(0, 2) = myTObj.getTerm(438) 106 myArrOpAlpha(1, 2) = "ENDS" 107 myArrOpAlpha(0, 3) = myTObj.getTerm(432) 108 myArrOpAlpha(1, 3) = "CONTAINS" 109 myArrOpAlpha(0, 4) = "LIKE" 110 myArrOpAlpha(1, 4) = "LIKE" 111 myArrOpAlpha(0, 5) = "IS NULL" 112 myArrOpAlpha(1, 5) = "IS NULL" 113 myArrOpAlpha(0, 6) = "IS NOT NULL" 114 myArrOpAlpha(1, 6) = "IS NOT NULL" 115 116 ' Text 117 Dim myArrOpText(1, 5) 118 myArrOpText(0, 0) = myTObj.getTerm(431) 119 myArrOpText(1, 0) = "STARTS" 120 myArrOpText(0, 1) = myTObj.getTerm(438) 121 myArrOpText(1, 1) = "ENDS" 122 myArrOpText(0, 2) = myTObj.getTerm(432) 123 myArrOpText(1, 2) = "CONTAINS" 124 myArrOpText(0, 3) = "LIKE" 125 myArrOpText(1, 3) = "LIKE" 126 myArrOpText(0, 4) = "IS NULL" 127 myArrOpText(1, 4) = "IS NULL" 128 myArrOpText(0, 5) = "IS NOT NULL" 129 myArrOpText(1, 5) = "IS NOT NULL" 130 131 ' Binary 132 Dim myArrOpBinary(0, 1) 133 myArrOpBinary(0, 0) = "IS NULL" 134 myArrOpBinary(0, 1) = "IS NOT NULL" 135 136 ' Boolean 137 Dim myArrOpBool(0, 3) 138 myArrOpBool(0, 0) = "IS NULL" 139 myArrOpBool(0, 1) = "IS NOT NULL" 140 myArrOpBool(0, 2) = "IS TRUE" 141 myArrOpBool(0, 3) = "IS FALSE" 142 143 ' UNIQUE IDENTIFIER 144 Dim myArrOpUId(0, 0) 145 myArrOpUId(0, 0) = "=" 146 147%> 148<!-- #INCLUDE FILE="../inc/metaheader.asp" --> 149<BODY> 150 <SCRIPT LANGUAGE="JavaScript" TYPE="text/javascript"> 151 <!-- 152 function openCalendar(pURL, pFormName, pControlName) { 153 var myURL = pURL + "?form=" + pFormName + "&elt=" + pControlName + "&initialDate=" + escape(eval("document." + pFormName + "." + pControlName).value); 154 openPopUp(myURL, "mlc_popup", 180, 180, 10, 10); 155 return (true); 156 } 157 158 function checkall() { 159 var myObj; 160 var myCheck = (!(document.mla_search.mla_display_0.checked)); 161 for (var i = 0; i <= <% = myColCount %>; i++) { 162 myObj = eval("document.mla_search.mla_display_" + i); 163 myObj.checked = myCheck; 164 } 165 return (true); 166 } 167 //--> 168 </SCRIPT> 169 170 <P CLASS="treeinfo"><% = myStrTree %></P> 171 172 <FORM NAME="mla_search" METHOD=POST ACTION="search.asp?nid=<% = myNodeId %>&db=<% = Server.URLEncode(myDbName) %>&obj=<% = Server.URLEncode(myObjName) %>&type=<% = myObjType %>"> 173 <TABLE BORDER=0 CELLPADDING=2 CELLSPACING=0 CLASS="content" SUMMARY="Search Form"> 174 <TR><TD CLASS="caption"><IMG SRC="../../themes/<% = mla_cfg_theme %>/images/action/search.gif" WIDTH="16" HEIGHT="16" BORDER=0 ALIGN="MIDDLE" ALT="Search"> <% = myTObj.getTerm(154) %></TD></TR> 175 <TR><TD> 176 <TABLE BORDER=0 CELLPADDING=2 CELLSPACING=2 ALIGN=CENTER CLASS="content" WIDTH="100%" SUMMARY="Search Form"> 177 <THEAD> 178 <TR> 179 <TD CLASS="collabel"><% = myTObj.getTerm(435) %></TD> 180 <TD CLASS="collabel" COLSPAN=3><% = myTObj.getTerm(436) %></TD> 181 <TD CLASS="collabel"><% = myTObj.getTerm(437) %> <A HREF=# onclick="return(checkall());"><IMG SRC="../../themes/<% = mla_cfg_theme %>/images/action/uncheck.gif" WIDTH="12" HEIGHT="12" BORDER=0 ALIGN="MIDDLE" ALT="Uncheck"></A></TD> 182 </TR> 183 </THEAD> 184 <TBODY> 185 <% 186 Set myObjStr = New mlt_string 187 For i = 0 To myColCount 188 If i MOD 2 = 0 Then myStrClass = "odd" Else myStrClass = "even" End If 189 myObjStr.strAppend "<TR CLASS=""" & myStrClass & """>" & vbCrLf 190 myObjStr.strAppend "<TD CLASS=""formlabel"">" & myArrCol(0, i) & "</TD>" 191 Select Case myArrCol(3, i) 192 Case 1 : ' Alpha 193 myObjStr.strAppend "<TD CLASS=""forminfo"">" & getListBox("mla_bool_" & i, myArrBool, 0, 0, " ", "", "", "") & "</TD>" 194 myObjStr.strAppend "<TD CLASS=""forminfo"">" & getListBox("mla_op_" & i, myArrOpAlpha, 1, 0, " ", "", "alphanumeric", "") & "</TD>" 195 myObjStr.strAppend "<TD CLASS=""forminfo""><INPUT TYPE=""text"" NAME=""mla_value_" & i & """ CLASS=""alpha_edit""></TD>" 196 Case 2 : ' Text 197 myObjStr.strAppend "<TD CLASS=""forminfo"">" & getListBox("mla_bool_" & i, myArrBool, 0, 0, " ", "", "", "") & "</TD>" 198 myObjStr.strAppend "<TD CLASS=""forminfo"">" & getListBox("mla_op_" & i, myArrOpText, 1, 0, " ", "", "alphanumeric", "") & "</TD>" 199 myObjStr.strAppend "<TD CLASS=""forminfo""><INPUT TYPE=""text"" NAME=""mla_value_" & i & """ CLASS=""alpha_edit""></TD>" 200 Case 3 : ' Binary 201 myObjStr.strAppend "<TD CLASS=""forminfo""> </TD>" 202 myObjStr.strAppend "<TD CLASS=""forminfo"">" & getListBox("mla_op_" & i, myArrOpBinary, 0, 0, " ", "", "alphanumeric", "") & "</TD>" 203 myObjStr.strAppend "<TD CLASS=""forminfo""> </TD>" 204 Case 4 : ' Date 205 myObjStr.strAppend "<TD CLASS=""forminfo""> </TD>" 206 myObjStr.strAppend "<TD CLASS=""forminfo"">" & getListBox("mla_op_" & i, myArrOpNumeric, 0, 0, " ", "", "alphanumeric", "") & "</TD>" 207 myObjStr.strAppend "<TD CLASS=""forminfo""><INPUT TYPE=""text"" NAME=""mla_value_" & i & """ CLASS=""num_edit"">" 208 myObjStr.strAppend "<A HREF=# onclick=""openCalendar('mlc_popup.asp', 'mla_search', 'mla_value_" & i & "'); return false;""><IMG SRC=""../../themes/" & mla_cfg_theme & "/images/action/calendar.gif"" WIDTH=""16"" HEIGHT=""16"" BORDER=0 ALIGN=""MIDDLE"" ALT=""Calendar""></A>" 209 myObjStr.strAppend " <B>AND</B> " 210 myObjStr.strAppend "<INPUT TYPE=""text"" NAME=""mla_value2_" & i & """ CLASS=""num_edit"">" 211 myObjStr.strAppend "<A HREF=# onclick=""openCalendar('mlc_popup.asp', 'mla_search', 'mla_value2_" & i & "'); return false;""><IMG SRC=""../../themes/" & mla_cfg_theme & "/images/action/calendar.gif"" WIDTH=""16"" HEIGHT=""16"" BORDER=0 ALIGN=""MIDDLE"" ALT=""Calendar""></A>" 212 myObjStr.strAppend "</TD>" 213 Case 5 : ' Boolean 214 myObjStr.strAppend "<TD CLASS=""forminfo""> </TD>" 215 myObjStr.strAppend "<TD CLASS=""forminfo"">" & getListBox("mla_op_" & i, myArrOpBool, 0, 0, " ", "", "alphanumeric", "") & "</TD>" 216 myObjStr.strAppend "<TD CLASS=""forminfo""> </TD>" 217 Case 6 : ' Unique Identifier 218 myObjStr.strAppend "<TD CLASS=""forminfo""> </TD>" 219 myObjStr.strAppend "<TD CLASS=""forminfo"">" & getListBox("mla_op_" & i, myArrOpUId, 0, 0, " ", "", "alphanumeric", "") & "</TD>" 220 myObjStr.strAppend "<TD CLASS=""forminfo""><INPUT TYPE=""text"" NAME=""mla_value_" & i & """ CLASS=""alpha_edit""></TD>" 221 Case Else : ' numeric 222 myObjStr.strAppend "<TD CLASS=""forminfo""> </TD>" 223 myObjStr.strAppend "<TD CLASS=""forminfo"">" & getListBox("mla_op_" & i, myArrOpNumeric, 0, 0, " ", "", "alphanumeric", "") & "</TD>" 224 myObjStr.strAppend "<TD CLASS=""forminfo""><INPUT TYPE=""text"" NAME=""mla_value_" & i & """ CLASS=""num_edit"">" 225 myObjStr.strAppend " <B>AND</B> " 226 myObjStr.strAppend "<INPUT TYPE=""text"" NAME=""mla_value2_" & i & """ CLASS=""num_edit""></TD>" 227 End Select 228 myObjStr.strAppend "<TD CLASS=""forminfo""><INPUT TYPE=""checkbox"" NAME=""mla_display_" & i & """ CHECKED></TD>" 229 myObjStr.strAppend "</TR>" & vbCrLf 230 Next 231 Response.Write myObjStr.getStr() 232 Set myObjStr = Nothing 233 %> 234 </TBODY> 235 </TABLE> 236 </TD></TR> 237 <TR><TD> </TD></TR> 238 <TR> 239 <TD ALIGN=CENTER> 240 <INPUT TYPE="radio" NAME="mla_match" VALUE="AND" ID="all" CHECKED><LABEL FOR="all"><% = myTObj.getTerm(433) %></LABEL> 241 242 <INPUT TYPE="radio" NAME="mla_match" VALUE="OR" ID="any"><LABEL FOR="any"><% = myTObj.getTerm(434) %></LABEL> 243 </TD> 244 </TR> 245 <TR> 246 <TD ALIGN=CENTER> 247 <INPUT TYPE="reset" VALUE="<% = myTObj.getTerm(50) %>" NAME="mla_reset"> 248 <INPUT TYPE="submit" VALUE="<% = myTObj.getTerm(54) %>" NAME="mla_submit"> 249 </TD> 250 </TR> 251 </TABLE> 252 </FORM> 253 254</BODY> 255</HTML> 256<!-- #INCLUDE FILE="../inc/mla_sql_end.asp" -->