dbadmin/ftquery.asp at master · stpatrick2016/dbadmin · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
<%@ Language=VBScript %>
<!--#include file=scripts/inc_common.asp-->
<html>
<head>
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
<meta name="GENERATOR" content="Microsoft Visual Studio 6.0">
<link href="default.css" rel="stylesheet" type="text/css">
<title>DBA:<%=langCaptionFreeTypeQuery%></title>
<script type="text/javascript" language="javascript" src="scripts/common.js" defer></script>
</head>
<body>

<%	call DBA_WriteNavigation%>

<%
	dim dba, strQuery, rec, AffectedRecords, pagesize, page, abspage, sClass, i, fld, strFilter
	strQuery = CStr(Request("query").Item)
	if IsNumeric(Request("pagesize").Item) then pagesize = CInt(Request("pagesize").Item) else pagesize = 10
	if IsNumeric(Request("page").Item) then page = CInt(Request("page").Item) else page = 1
	if page < 1 then page = 1
	if pagesize < 1 then pagesize = StpProfile.GetProfileNumber("settings", "page_size", 10)
	if pagesize < 1 then pagesize = 10

	set dba = new DBAdmin
	dba.Connect Session(DBA_cfgSessionDBPathName), Session(DBA_cfgSessionDBPassword)
	if Len(strQuery) > 0 then
		set rec = dba.RunScript( _
			strQuery, _
			Request.Form("transaction").Item, _
			Request.Form("ignore_errors").Item, _
			AffectedRecords)
	end if
%>

<!--BEGIN RESULTS FORM-->
<%
	if Len(strQuery) > 0 then
		DBA_BeginNewTable langFTQResults, "", "90%", ""
%>
<%
		'first let's write out what was affected
		for each i in AffectedRecords
			DBA_WriteSuccess langRecordsAffected & "&nbsp;" & i & "<br>"
		next
		if rec.State <> adStateClosed then
			rec.CacheSize = pagesize
			rec.PageSize = pagesize
			if rec.PageCount > 0 then rec.AbsolutePage = page
			abspage = rec.AbsolutePage
%>
<h3 align="center"><%=langTotalRecords%>&nbsp;<b><%=rec.RecordCount%></b></h3>

	<!--BEGIN EXPORT OPTIONS-->
<%			if rec.RecordCount > 0 then%>
<p align=center>
*&nbsp;<img src="images/xml.gif" border="0" width="16" height="16"><a href="export_xml.asp?sql=<%=Server.URLEncode(strQuery)%>" alt="<%=langXMLExportAlt%>"><%=langXMLExport%></a>&nbsp;
*&nbsp;<img src="images/excel.gif" border="0" width="16" height="16"><a href="export_csv.asp?sql=<%=Server.URLEncode(strQuery)%>" alt="<%=langExcelExportAlt%>"><%=langExcelExport%></a>&nbsp;*
</p>
<%			end if%>
	<!--END EXPORT OPTIONS-->

<table align="center">
	<tr><td align="center">
	<form action="ftquery.asp" method="post">
			<%=langFilter%>&nbsp;
			<select name="filter_field">
				<option value=""></option>
<%	For Each fld in rec.Fields%>
				<option value="<%=fld.Name%>"><%=fld.Name%></option>
<%	Next%>
			</select>
			<select name="filter_cmp">
				<option value="=">=</option>
				<option value=">">></option>
				<option value="<"><</option>
				<option value=">=">>=</option>
				<option value="<="><=</option>
				<option value="<>"><></option>
				<option value="LIKE">LIKE</option>
			</select>
			<input type="text" name="filter_criteria" size="10">

			&nbsp;&nbsp;
		<%=langPageSize%>&nbsp;
		<select name="pagesize">
			<%=DBA_GetComboOptions(5, 50, 5, pagesize)%>
		</select>
		<input type="hidden" name="query" value="<%=Replace(strQuery, """", "&quot;")%>">
		<input type="submit" value="<%=langSubmit%>" class="button">
	</form>
	</td></tr>
</table>

	<p align="left">
<%			if abspage > 1 then%>
				<a href="ftquery.asp?query=<%=Server.URLEncode(strQuery)%>&amp;page=<%=(abspage - 1)%>&amp;pagesize=<%=pagesize%>"><font size="1">&laquo;&nbsp;<%=langPrev%></font></a>
<%			end if%>
<%			for i=1 to rec.PageCount
				if i = abspage then%>
					<font size="2">[<%=i%>]</font>&nbsp;
			<%	else%>
					<font size="1">&nbsp;[<a href="ftquery.asp?query=<%=Server.URLEncode(strQuery)%>&amp;page=<%=i%>&amp;pagesize=<%=pagesize%>"><%=i%></a>]&nbsp;</font>
			<%	end if
			Next
			if abspage < rec.PageCount and abspage > 0 then%>
				<a href="ftquery.asp?query=<%=Server.URLEncode(strQuery)%>&amp;page=<%=(abspage + 1)%>&amp;pagesize=<%=pagesize%>"><font size="1"><%=langNext%>&nbsp;&raquo;</font></a>
<%			end if
			i = 0
%>
	</p>

		<table align="center" border="1" width="100%">
		<tr>
<%			for each fld in rec.Fields%>
				<th><%=fld.Name%></th>
<%			next%>
		</tr>

<%
			strFilter = BuildFilter(rec)
			if Len(strFilter) > 0 then call rec.Find(strFilter)
			do while not rec.EOF and i < rec.PageSize and rec.State <> adStateClosed
				if sClass = "oddrow" then sClass = "evenrow" else sClass = "oddrow"
%>
		<tr class="<%=sClass%>" onmouseover="style.backgroundColor='#ffdfbf'" onmouseout="style.backgroundColor=''">
<%				for each fld in rec.Fields%>
					<td valign="top" align="center">
<%					if fld.Type <> adBinary then
						if fld.Value <> "" then Response.Write Replace(fld.Value, "<", "&lt;") else Response.Write "&nbsp;"
					else
						Response.Write "&lt;" & langBinaryData & "&gt;"
					end if
%>
					</td>
<%				next%>
</tr>
<%				rec.MoveNext
				i = i + 1
			loop
%>

</table>

	<p align="left">
<%			if abspage > 1 then%>
				<a href="ftquery.asp?query=<%=Server.URLEncode(strQuery)%>&amp;page=<%=(abspage - 1)%>&amp;pagesize=<%=pagesize%>"><font size="1">&laquo;&nbsp;<%=langPrev%></font></a>
<%			end if%>
<%			for i=1 to rec.PageCount
				if i = abspage then%>
					<font size="2">[<%=i%>]</font>&nbsp;
<%				else%>
					<font size="1">&nbsp;[<a href="ftquery.asp?query=<%=Server.URLEncode(strQuery)%>&amp;page=<%=i%>&amp;pagesize=<%=pagesize%>"><%=i%></a>]&nbsp;</font>
<%				end if
			Next
			if abspage < rec.PageCount and abspage > 0 then%>
				<a href="ftquery.asp?query=<%=Server.URLEncode(strQuery)%>&amp;page=<%=(abspage + 1)%>&amp;pagesize=<%=pagesize%>"><font size="1"><%=langNext%>&nbsp;&raquo;</font></a>
<%			end if%>
	</p>
<%		end if
		call DBA_EndNewTable
	end if
%>
<!--END RESULTS FORM-->


<!--BEGIN QUERY FORM-->
<%
	DBA_BeginNewTable langFreeTypeQuery, langFreeTypeQueryAlt, "90%", ""
	if dba.HasError then DBA_WriteError Replace(dba.LastError, vbCrLf, "<br>")
%>
	<p align="center"><%=langTypeSQL%></p>
	<form action="ftquery.asp" method="post">
	<table align="center" border="0">
		<tr><td>
			<textarea name="query" rows="10" cols="50"><%=strQuery%></textarea>
		</td></tr>
		<tr><td>
			<input type="checkbox" name="transaction" value="-1">&nbsp;<%=langUseTransaction%>
		</td></tr>
		<tr><td>
			<input type="checkbox" name="ignore_errors" value="-1">&nbsp;<%=langIgnoreErrors%>
		</td></tr>
		<tr><td align="center">
			<input class="button" type="submit" value="<%=langRunIt%>" name="submit">
		</td></tr>
	</table>
	</form>

<%
	call DBA_EndNewTable
	set dba = Nothing
%>

<!--END QUERY FORM-->

<!--#include file=scripts/inc_footer.inc-->
</body>
</html>
<%
	Function BuildFilter(ByRef rc)
		dim filter, field, cmp, criteria, fldType, fld
		filter = ""
		field = Request.Form("filter_field").Item
		cmp = Request.Form("filter_cmp").Item
		criteria = Request.Form("filter_criteria").Item

		If Len(field) > 0 and Len(criteria) > 0 then
			set fld = new DBAField
			fld.FieldType = rc(field).Type
			fldType = fld.GetSQLTypeName()
			set fld = Nothing
			If fldType = "TEXT" or fldType = "MEMO" Then
				'remove asterics if only at beginning
				If Left(criteria, 1) = "*" and Right(criteria, 1) <> "*" Then criteria = Mid(criteria, 2)
				criteria = "'" & Replace(criteria, "'", "''") & "'"
			ElseIf fldType = "DATETIME" Then
				criteria = "#" & criteria & "#"
			Else
				If cmp = "LIKE" Then cmp = "="
			End If
			filter = field & " " & cmp & " " & criteria
		End If

		BuildFilter = filter
	End Function
%>