<%@ 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="C#" runat="server">
	SqlConnection Source;
	SqlConnection Master;
	void Page_load(object sender,EventArgs e){
		AllTables.ToolTip="AllTables";
		NowSelFields.ToolTip="NowSelFields";
		Cri.ToolTip="Cri";
		ErrMsg.Visible=false;
		NowRes.Visible=false;
		NowCri.Visible=false;
		Source = (SqlConnection)Session["NowSTKSQL"];
		Master = (SqlConnection)Session["NowMaster"];//在Global.asax中已定义
		if(IsPostBack==false){
			SqlDataAdapter NComm = new SqlDataAdapter("USE stockSQL EXEC dbo.sp_tables", Master);
			DataSet DS = 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"; //将数据表名称置于DropDownlist
			AllTables.DataBind();
			DataTable NowFields = new DataTable("AllFields");//建立新table
			DS.Tables.Add("AllFields");
			DataColumn NowCol;//建立新列
			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);
			DataTable NowCris = new DataTable("AllCris");//建立新table
			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;//选取And
		}
	}
	void selectTable(object sender, EventArgs e){
		NowCri.Visible=true;//目前条件的Label
		NowRes.Visible=true;//所有记录的Label
		NowRes.Text="所有记录：";
		SqlDataAdapter NComm = new SqlDataAdapter("select * from " + AllTables.SelectedItem.Text, Source);
		DataSet DS = (DataSet)Session["NowDSQU"];
		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();
		foreach(DataColumn Col in DS.Tables[AllTables.SelectedItem.Text].Columns){
			DataRow Row = DS.Tables["AllFields"].NewRow();
			Row["FieldName"]=Col.ColumnName;
			Row["FieldType"]=Col.ColumnName + ";" + Col.DataType.ToString();
			DS.Tables["AllFields"].Rows.Add(Row);
		}
		NowSelFields.DataSource=DS.Tables["AllFields"].DefaultView;
		NowSelFields.DataTextField="FieldName";
		NowSelFields.DataValueField="FieldType"; //结合至列之DropDownlist
		NowSelFields.DataBind();
		NowSelFields.SelectedIndex=0;//选取第一个列
		SetOper();
	}
	void selectField(object sender,EventArgs e){
		NowCri.Visible=true;
		SetOper();
	}
	void SetOper(){
		ArrayList Cris = new ArrayList();
		string NowType = NowSelFields.SelectedItem.Value;
		int x=NowType.IndexOf(";",0);//取得;之分隔符
		string NowType1=NowType.Remove(0,x+1);//取得类型
		if(NowType1.IndexOf("String")>= 0){//若是字符串
			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("不等于(<>)");
		}
		Cris.Add("空白(Is Null)");
		Cris.Add("非空白(Is Not Null)");
		Cri.DataSource=Cris;
		Cri.DataBind();
	}
	void CheckAnd(object sender,EventArgs e){
		if(Check1.Checked==true){
			Check2.Checked=false;
		}
		else{
			Check2.Checked=true;
		}
	}
	void CheckOr(object sender,EventArgs e){
		if(Check2.Checked=true){
			Check1.Checked=false;
		}
		else{
			Check1.Checked=true;
		}
	}
	void addCriClick(object sender,EventArgs e){
		string x=Cri.SelectedItem.Value;
		string y=NowSelFields.SelectedItem.Value;		
		if(y.IndexOf("DateTime")>=0){ //若为日期
			if(x.IndexOf("Null")==-1){
				try{//判断输入值是否为日期
					DateTime D1 = Convert.ToDateTime(CValue.Text);
					CValue.Text="#" + CValue.Text + "#";
				}
				catch{
					ErrMsg.Text = "日期输入错误";
					ErrMsg.Visible=true;
					return;
				}
			}
		}
		if(CValue.Text==""){ //判断有无输入值
			if(x.IndexOf("Null")==-1){
				ErrMsg.Text="未输入比较值";
				ErrMsg.Visible=true;
				return;
			}
		}
		try{
			DataSet DS = (DataSet)Session["NowDSQU"];
			DataRow newCri;
			if(DS.Tables["AllCris"].Rows.Count==0){
				newCri = DS.Tables["AllCris"].NewRow();
				newCri["AndOr"]="";
			}
			else{
				newCri = DS.Tables["AllCris"].NewRow();
				if(Check1.Checked==true){
					newCri["AndOr"]="And";
				}
				else{
					newCri["AndOr"]="Or";
				}
			}
			string d="";
			switch (Cri.SelectedItem.Value.ToString()){
			case "等于(=)":
				d = "= " + CValue.Text;
				break;
			case "大于(>)":
				d = "> " + CValue.Text;
				break;
			case "小于(<)":
				d = "< " + CValue.Text;
				break;
			case "大于等于(>=)":
				d = ">= " + CValue.Text;
				break;
			case "小于等于(<=)":
				d = "= " + CValue.Text;
				break;
			case "不等于(<>)":
				d = "<> " + CValue.Text;
				break;
			case "开头是(Like*)":
				d = "Like '" + CValue.Text + "*'";
				break;
			case "结尾是(*Like)":
				d = "Like '*" + CValue.Text + "'";
				break;
			case "包含(*Like*)":
				d = "Like '*" + CValue.Text + "*'";
				break;
			case "不含(Not Like)":
				d = "Not Like '" + CValue.Text + "'";
				break;
			case "等于(Like)":
				d = "Like '" + CValue.Text + "'";
				break;
			case "空白(Is Null)":
				d = "Is Null";
				break;
			case "非空白(Is Not Null)":
				d = "Is Not Null";
				break;
			}
			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;
		}
	}
	void celallCriClick(object sender, EventArgs e){//清除所有条件
		NowRes.Visible=true;
		NowCri.Visible=true;
		DataSet DS = (DataSet)Session["NowDSQU"];
		DS.Tables["AllCris"].Rows.Clear();
		AllCri.DataSource=DS.Tables["AllCris"].DefaultView;
		AllCri.DataBind();
	}
	void celCriClick(object Sender,EventArgs e){//清除条件
		if(AllCri.SelectedIndex==-1){
			ErrMsg.Text="未选取条件或没有条件";
			ErrMsg.Visible=true;
		}
		else{
			DataSet DS = (DataSet)Session["NowDSQU"];
			DS.Tables["AllCris"].Rows.RemoveAt(AllCri.SelectedIndex);
			AllCri.DataSource=DS.Tables["AllCris"].DefaultView;
			AllCri.DataBind();
		}
	}
	void SetSelect(object Sender,DataGridCommandEventArgs e){
		if(AllCri.SelectedIndex==e.Item.ItemIndex){
			AllCri.SelectedIndex=-1;
		}
		else{
			AllCri.SelectedIndex=e.Item.ItemIndex;//设定选取项目
		}
	}
	void ExuClick(object Sender,EventArgs e){//执行筛选
		NowRes.Visible=true;
		NowCri.Visible=true;
		NowRes.Text="筛选结果：";
		DataSet DS = (DataSet)Session["NowDSQU"];
		string CQU="";
		foreach(DataRow Row in DS.Tables["AllCris"].Rows){
			CQU += Row["AndOr"] + " " + Row["Content"];
		}
		DS.Tables[AllTables.SelectedItem.Text].DefaultView.RowFilter=CQU;
		Resault.DataSource=DS.Tables[AllTables.SelectedItem.Text].DefaultView;
		Resault.DataBind();
	}
	void celExuClick(object Sender, EventArgs e){//清除筛选
		NowRes.Visible=true;
		NowCri.Visible=true;
		NowRes.Text="所有记录：";
		DataSet DS = (DataSet)Session["NowDSQU"];
		DS.Tables[AllTables.SelectedItem.Text].DefaultView.RowFilter="";
		Resault.DataSource=DS.Tables[AllTables.SelectedItem.Text].DefaultView;
		Resault.DataBind();
	}
	</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>