PageRenderTime 12ms CodeModel.GetById 7ms app.highlight 1ms RepoModel.GetById 1ms app.codeStats 1ms

/SQL/scripts/db/search.asp

http://github.com/khaneh/Orders
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) = "&gt;"
 90	myArrOpNumeric(0, 2) = "&lt;"
 91	myArrOpNumeric(0, 3) = "&gt;="
 92	myArrOpNumeric(0, 4) = "&lt;="
 93	myArrOpNumeric(0, 5) = "&lt;&gt;"
 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"">&nbsp;</TD>"
202								myObjStr.strAppend "<TD CLASS=""forminfo"">"  & getListBox("mla_op_" & i, myArrOpBinary, 0, 0, " ", "", "alphanumeric", "") & "</TD>"
203								myObjStr.strAppend "<TD CLASS=""forminfo"">&nbsp;</TD>"
204							Case 4 : ' Date
205								myObjStr.strAppend "<TD CLASS=""forminfo"">&nbsp;</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"">&nbsp;</TD>"
215								myObjStr.strAppend "<TD CLASS=""forminfo"">"  & getListBox("mla_op_" & i, myArrOpBool, 0, 0, " ", "", "alphanumeric", "") & "</TD>"
216								myObjStr.strAppend "<TD CLASS=""forminfo"">&nbsp;</TD>"
217							Case 6 : ' Unique Identifier
218								myObjStr.strAppend "<TD CLASS=""forminfo"">&nbsp;</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"">&nbsp;</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>&nbsp;</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			&nbsp;
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"> &nbsp;
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" -->