<%@ Register TagPrefix="Title" TagName="TitleShow" Src="/aspnetdata/Title.ascx" %>
<%@Page Debug=true %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Web" %>
<html>
<head>
	<script language="VB" runat="server">
	Dim Source AS SqlConnection
	Dim Master as SqlConnection
	Sub Page_load(sender as object,e as EventArgs)
		AllTables.ToolTip="AllTables"
		NowSelFields.ToolTip="NowSelFields"
		Cri.ToolTip="Cri"
		ErrMsg.Visible=false
		NowRes.Visible=false
		NowCri.Visible=false
		Source = Ctype(Session("NowSTKSQL"),SqlConnection)
		Master = Ctype(Session("NowMaster"),SqlConnection)
		if IsPostBack=false then
			Dim NComm as SqlDataAdapter = new SqlDataAdapter("USE stockSQL EXEC dbo.sp_tables", Master)
			Dim DS As DataSet = new DataSet()
			NComm.Fill(DS,"AllTable")
			DS.Tables("AllTable").DefaultView.RowFilter = "TABLE_TYPE NOT LIKE 'SYSTEM TABLE'"
			AllTables.DataSource=DS.Tables("AllTable").DefaultView
			AllTables.DataValueField="TABLE_NAME"
			AllTables.DataBind()
			Dim NowFields as DataTable = new DataTable("AllFields")
			DS.Tables.Add("AllFields")
			Dim NowCol as DataColumn
			NowCol = new DataColumn()
			NowCol.ColumnName="FieldName"
			NowCol.DataType=System.Type.GetType("System.String")
			DS.Tables("AllFields").Columns.Add(NowCol)
			NowCol = new DataColumn()
			NowCol.ColumnName="FieldType"
			NowCol.DataType=System.Type.GetType("System.String")
			DS.Tables("AllFields").Columns.Add(NowCol)
			Dim NowCris as DataTable = new DataTable("AllCris")
			DS.Tables.Add("AllCris")
			NowCol = new DataColumn()
			NowCol.ColumnName="AndOr"
			NowCol.DataType=System.Type.GetType("System.String")
			DS.Tables("AllCris").Columns.Add(NowCol)
			NowCol = new DataColumn()
			NowCol.ColumnName="Content"
			NowCol.DataType=System.Type.GetType("System.String")
			DS.Tables("AllCris").Columns.Add(NowCol)
			Session("NowDSQU")=DS
			Check1.Checked=true
		end if
	end sub
	Sub selectTable(sender as object, e as EventArgs)
		NowCri.Visible=true'目前条件的Label
		NowRes.Visible=true'所有记录的Label
		NowRes.Text="所有记录："
		Dim NComm as SqlDataAdapter = new SqlDataAdapter("select * from " + AllTables.SelectedItem.Text, Source)
		Dim DS as DataSet = Ctype(Session("NowDSQU"),DataSet)
		DS.Tables("AllCris").Rows.Clear()'清除所有条件
		DS.Tables("AllFields").Rows.Clear()'清除所有行
		AllCri.DataSource=DS.Tables("AllCris").DefaultView
		AllCri.DataBind()
		NComm.Fill(DS,AllTables.SelectedItem.Text)'以选取的数据表名称放入DataSet
		Resault.DataSource=DS.Tables(AllTables.SelectedItem.Text).DefaultView
		Resault.DataBind()
		Dim Col as DataColumn
		for each Col in DS.Tables(AllTables.SelectedItem.Text).Columns
			Dim Row as DataRow = DS.Tables("AllFields").NewRow()
			Row("FieldName")=Col.ColumnName
			Row("FieldType")=Col.ColumnName & "" & Col.DataType.ToString()
			DS.Tables("AllFields").Rows.Add(Row)
		next
		NowSelFields.DataSource=DS.Tables("AllFields").DefaultView
		NowSelFields.DataTextField="FieldName"
		NowSelFields.DataValueField="FieldType" '结合至列的DropDownlist
		NowSelFields.DataBind()
		NowSelFields.SelectedIndex=0'选取第一个列
		SetOper()
	end sub
	Sub selectField(sender as object,e as EventArgs)
		NowCri.Visible=true
		SetOper()
	end sub
	Sub SetOper()
		Dim Cris as ArrayList = new ArrayList()
		Dim NowType as string = NowSelFields.SelectedItem.Value
		Dim x as integer = NowType.IndexOf("",0)'取得的之分隔符
		Dim NowType1 as string = NowType.Remove(0,x+1)'取得类型
		if NowType1.IndexOf("String")>= 0 then'若是字串
			Cris.Add("开头是(Like*)")
			Cris.Add("结尾是(*Like)")
			Cris.Add("包含(*Like*)")
			Cris.Add("不含(Not Like)")
			Cris.Add("等于(Like)")
		else'不是字串
			Cris.Add("等于(=)")
			Cris.Add("大于(>)")
			Cris.Add("小于(<)")
			Cris.Add("大于等于(>=)")
			Cris.Add("小于等于(<=)")
			Cris.Add("不等于(<>)")
		end if
		Cris.Add("空白(Is Null)")
		Cris.Add("非空白(Is Not Null)")
		Cri.DataSource=Cris
		Cri.DataBind()
	end sub
	Sub CheckAnd(sender as object,e as EventArgs)
		if Check1.Checked=true then
			Check2.Checked=false
		else
			Check2.Checked=true
		end if
	end sub
	Sub CheckOr(sender as object,e as EventArgs)
		if Check2.Checked=true then
			Check1.Checked=false
		else
			Check1.Checked=true
		end if
	end sub
	Sub addCriClick(sender as object,e as EventArgs)
		Dim x as string = Cri.SelectedItem.Value
		Dim y as string = NowSelFields.SelectedItem.Value		
		if y.IndexOf("DateTime")>=0 then '若为日期
			if x.IndexOf("Null")=-1 then
				try'判断输入值是否为日期
					Dim D1 as DateTime = Convert.ToDateTime(CValue.Text)
					CValue.Text="#" + CValue.Text + "#"
				catch
					ErrMsg.Text = "日期输入错误"
					ErrMsg.Visible=true
					return
				end try
			end if
		end if
		if CValue.Text="" then '判断有无输入值
			if x.IndexOf("Null")=-1 then
				ErrMsg.Text="未输入比较值"
				ErrMsg.Visible=true
				return
			end if
		end if
		try
			Dim DS as DataSet = Ctype(Session("NowDSQU"),DataSet)
			Dim newCri as DataRow
			if DS.Tables("AllCris").Rows.Count=0 then
				newCri = DS.Tables("AllCris").NewRow()
				newCri("AndOr")=""
			else
				newCri = DS.Tables("AllCris").NewRow()
				if Check1.Checked=true then
					newCri("AndOr")="And"
				else
					newCri("AndOr")="Or"
				end if
			end if
			dim d as string =""
			select case Cri.SelectedItem.Value.ToString()
			Case Is="等于(=)"
				d = "= " & CValue.Text
			Case Is="大于(>)"
				d = "> "  & CValue.Text
			Case Is="小于(<)"
				d = "< "  & CValue.Text
			Case Is="大于等于(>=)"
				d = ">= " & CValue.Text
			Case Is="小于等于(<=)"
				d = "= " & CValue.Text
			Case Is="不等于(<>)"
				d = "<> " & CValue.Text
			Case Is="开头是(Like*)"
				d = "Like '" & CValue.Text & "*'"
			Case Is="结尾是(*Like)"
				d = "Like '*" & CValue.Text & "'"
			Case Is="包含(*Like*)"
				d = "Like '*" & CValue.Text & "*'"
			Case Is="不含(Not Like)"
				d = "Not Like '" & CValue.Text & "'"
			Case Is="等于(Like)"
				d = "Like '" & CValue.Text & "'"
			Case Is="空白(Is Null)"
				d = "Is Null"
			Case Is="非空白(Is Not Null)"
				d = "Is Not Null"
			End Select
			newCri("Content")=" " & NowSelFields.SelectedItem.Text & " " & d
			DS.Tables("AllCris").Rows.Add(newCri)
			AllCri.DataSource=DS.Tables("AllCris").DefaultView
			AllCri.DataBind()
		catch
			ErrMsg.Text = "发生错误，可能未指定列位或运算子"
			ErrMsg.Visible=true
		end try
	end sub
	Sub celallCriClick(sender as object, e as EventArgs)'清除所有条件
		NowRes.Visible=true
		NowCri.Visible=true
		Dim DS as DataSet = Ctype(Session("NowDSQU"),DataSet)
		DS.Tables("AllCris").Rows.Clear()
		AllCri.DataSource=DS.Tables("AllCris").DefaultView
		AllCri.DataBind()
	end sub
	Sub celCriClick(sender as object,e as EventArgs)'清除条件
		if AllCri.SelectedIndex=-1 then
			ErrMsg.Text="未选取条件或没有条件"
			ErrMsg.Visible=true
		else
			Dim DS as DataSet = Ctype(Session("NowDSQU"),DataSet)
			DS.Tables("AllCris").Rows.RemoveAt(AllCri.SelectedIndex)
			AllCri.DataSource=DS.Tables("AllCris").DefaultView
			AllCri.DataBind()
		end if
	end sub
	Sub SetSelect(sender as object,e as DataGridCommandEventArgs)
		if AllCri.SelectedIndex=e.Item.ItemIndex then
			AllCri.SelectedIndex=-1
		else
			AllCri.SelectedIndex=e.Item.ItemIndex'设定选取项目
		end if
	end sub
	Sub ExuClick(sender as object,e as EventArgs)'执行筛选
		NowRes.Visible=true
		NowCri.Visible=true
		NowRes.Text="筛选结果："
		Dim DS as DataSet = Ctype(Session("NowDSQU"),DataSet)
		Dim CQU as string
		Dim Row as DataRow
		for each Row in DS.Tables("AllCris").Rows
			CQU += Row("AndOr") & " " & Row("Content")
		next
		DS.Tables(AllTables.SelectedItem.Text).DefaultView.RowFilter=CQU
		Resault.DataSource=DS.Tables(AllTables.SelectedItem.Text).DefaultView
		Resault.DataBind()
	end sub
	Sub celExuClick(sender as object, e as EventArgs)'清除筛选
		NowRes.Visible=true
		NowCri.Visible=true
		NowRes.Text="所有记录："
		Dim DS as DataSet = Ctype(Session("NowDSQU"),DataSet)
		DS.Tables(AllTables.SelectedItem.Text).DefaultView.RowFilter=""
		Resault.DataSource=DS.Tables(AllTables.SelectedItem.Text).DefaultView
		Resault.DataBind()
	end sub
	</script>
</head>
<body background="/aspnetdata/images/winback.gif">
<Title:TitleShow id="BookT" Runat="server" />
<form runat="server">
<font color="#FF0000"><b>1.</b></font>选择数据表：<asp:dropdownlist id="AllTables" Autopostback="true" OnSelectedIndexChanged="selectTable" Runat="server" />
<br><font color="#FF0000"><b>2.</b></font>指定条件间关系(第一个条件不须选取)：<asp:CheckBox tooltip="Check1" id="Check1" Text="And" OnCheckedChanged="CheckAnd" AutoPostBack="true" runat="server" />
<asp:CheckBox tooltip="Check2" id="Check2" Text="Or" OnCheckedChanged="CheckOr" AutoPostBack="true"  runat="server" />
<br><font color="#FF0000"><b>3.</b></font>指定字段<asp:dropdownlist id="NowSelFields" AutoPostBack="true" OnSelectedIndexChanged="selectField" runat="server" />
　<font color="#FF0000"><b>4.</b></font>指定运算符<asp:dropdownlist id="Cri" runat="server" /><br><font color="#FF0000"><b>5.</b></font>值：<asp:textbox tooltip="CValue" id="CValue" Runat="Server" />
<p><ASP:Button Text="加入条件" id="addCri" OnClick="addCriClick" runat="server" />
<ASP:Button Text="清除条件" id="celCri" Onclick="celCriClick" runat="server" />
<ASP:Button Text="清除所有条件" OnClick="celallCriClick"  id="celallCri" runat="server" />
<ASP:Button Text="执行筛选" id="Exu" Onclick="ExuClick" runat="server" />
<ASP:Button Text="取消筛选" id="celExu" Onclick="celExuClick" runat="server" />
<br><font color="#FF0000"><b><asp:label id="ErrMsg" Visible="true" Runat="server" /></b></font>
<asp:label id="NowCri" Text="目前条件：" Runat="server" />
<ASP:DataGrid id="AllCri" runat="server"
        BorderColor="black"
        BorderWidth="1"
        GridLines="Both"
        CellPadding="3"
        CellSpacing="0"
        Font-Name="Verdana"
        Font-Size="8pt"
        HeaderStyle-BackColor="GreenYellow"
AlternatingItemStyle-BackColor="LightYellow"
SelectedItemStyle-BackColor="Yellow"
SelectedItemStyle-Font-Bold="true"
AutoGenerateColumns="false"
OnItemCommand="SetSelect"
>
<Columns>
<asp:ButtonColumn Text="选取" CommandName="ShowGetDetail" ItemStyle-Wrap="false"/>
<asp:BoundColumn HeaderText="关系" DataField="AndOr" ItemStyle-Wrap="false"/>
<asp:BoundColumn HeaderText="运算式" DataField="Content" ItemStyle-Wrap="false"/>
</Columns>
</ASP:DataGrid>
<p><asp:label id="NowRes" Text="筛选结果：" Runat="server" />
<ASP:DataGrid id="Resault" runat="server"
        BorderColor="black"
        BorderWidth="1"
        GridLines="Both"
        CellPadding="3"
        CellSpacing="0"
        Font-Name="Verdana"
        Font-Size="8pt"
        HeaderStyle-BackColor="GreenYellow"
AlternatingItemStyle-BackColor="LightYellow"
AutoGenerateColumns="true"
></ASP:DataGrid>
<asp:label id="nn" Visible="true" Runat="server" />
</form>
</body>