quaryform.aspx.cs

房屋管理系统 房屋管理系统 房屋管理系统 房屋管理系统

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Configuration;

namespace RoomMngSystem
{
	/// <summary>
	/// QuaryForm 的摘要说明。
	/// </summary>
	public class QuaryForm : System.Web.UI.Page
	{
		protected System.Web.UI.WebControls.Button Btn_StatForm;
		protected System.Web.UI.WebControls.Button Btn_QuaryForm;
		protected System.Web.UI.WebControls.CheckBoxList CBL_BNo;
		protected System.Data.Odbc.OdbcConnection oCn_QuaryForm;
		protected System.Data.Odbc.OdbcDataAdapter oDA_BNo;
		protected System.Web.UI.WebControls.TextBox TB_RoomNo;
		protected System.Web.UI.WebControls.CheckBox CB_BNo;
		protected System.Web.UI.WebControls.CheckBox CB_RoomNo;
		protected System.Web.UI.WebControls.CheckBox CB_Area;
		protected System.Web.UI.WebControls.DropDownList DDL_Cond;
		protected System.Web.UI.WebControls.Label Lb_And;
		protected System.Web.UI.WebControls.TextBox TB_Area1;
		protected System.Web.UI.WebControls.TextBox TB_Area2;
		protected System.Web.UI.WebControls.CheckBox CB_SeatNo;
		protected System.Web.UI.WebControls.DropDownList DDL_SeatCond;
		protected System.Web.UI.WebControls.TextBox TB_Seat1;
		protected System.Web.UI.WebControls.Label Lb_And2;
		protected System.Web.UI.WebControls.TextBox TB_Seat2;
		protected System.Web.UI.WebControls.CheckBox CB_Unit;
		protected System.Web.UI.WebControls.TextBox TB_Unit;
		protected System.Web.UI.WebControls.CheckBox CB_Usage;
		protected System.Data.Odbc.OdbcCommand oCmd_Init;
		protected System.Web.UI.WebControls.DropDownList DDL_Using;
		protected System.Web.UI.WebControls.TextBox TB_Charge;
		protected System.Web.UI.WebControls.CheckBox CB_Charge;
		protected System.Web.UI.WebControls.CheckBox CB_Water;
		protected System.Web.UI.WebControls.CheckBoxList CBL_Water;
		protected System.Web.UI.WebControls.CheckBox CB_Elec;
		protected System.Web.UI.WebControls.CheckBoxList CBL_Elec;
		protected System.Web.UI.WebControls.CheckBox CB_net;
		protected System.Web.UI.WebControls.CheckBoxList CBL_Net;
		protected System.Web.UI.WebControls.CheckBox CB_Tel;
		protected System.Web.UI.WebControls.CheckBoxList CBL_Tel;
		protected System.Web.UI.WebControls.CheckBox CB_Used;
		protected System.Web.UI.WebControls.CheckBoxList CBL_Used;
		protected System.Web.UI.WebControls.CheckBox CB_Sex;
		protected System.Web.UI.WebControls.CheckBoxList CBL_Sex;
		protected System.Web.UI.WebControls.Button Btn_Select;
		protected System.Web.UI.WebControls.Button Btn_Quary;
		protected System.Web.UI.WebControls.CheckBox CB_memo;
		protected System.Web.UI.WebControls.CheckBox CB_Multimedia;
		protected System.Web.UI.WebControls.CheckBoxList CBL_Multimedia;
		protected System.Web.UI.WebControls.Image Image1;
		protected System.Web.UI.WebControls.Button Btn_PWDAlter;
		protected System.Web.UI.WebControls.CheckBox CB_Index;
	
		private void Page_Load(object sender, System.EventArgs e)
		{
			// 在此处放置用户代码以初始化页面
			oCn_QuaryForm.ConnectionString=ConfigurationSettings.AppSettings["oCn_Str"];
			if(!Page.IsPostBack)
			{
            oCmd_Init.CommandText="select bno from BuildingInfo";
			oCn_QuaryForm.Open();
			CBL_BNo.DataSource=oCmd_Init.ExecuteReader();
			CBL_BNo.DataBind();
			oCn_QuaryForm.Close();

			oCn_QuaryForm.Open();
            oCmd_Init.CommandText="select using from Using";
			DDL_Using.DataSource=oCmd_Init.ExecuteReader();
			DDL_Using.DataBind();
			oCn_QuaryForm.Close();
            DDL_Using.SelectedValue="其他";
			oCn_QuaryForm.Open();
			oCmd_Init.CommandText="select water from Water";
			CBL_Water.DataSource=oCmd_Init.ExecuteReader();
			CBL_Water.DataBind();
			oCn_QuaryForm.Close();
				for(int i=0;i<CBL_Water.Items.Count;i++)
					CBL_Water.Items[i].Selected=true;
			oCn_QuaryForm.Open();
			oCmd_Init.CommandText="select elect from Elect";
			CBL_Elec.DataSource=oCmd_Init.ExecuteReader();
			CBL_Elec.DataBind();
			oCn_QuaryForm.Close();
				for(int i=0;i<CBL_Elec.Items.Count;i++)
					CBL_Elec.Items[i].Selected=true;
			oCn_QuaryForm.Open();
			oCmd_Init.CommandText="select net from net";
			CBL_Net.DataSource=oCmd_Init.ExecuteReader();
			CBL_Net.DataBind();
			oCn_QuaryForm.Close();
				for(int i=0;i<CBL_Net.Items.Count;i++)
					CBL_Net.Items[i].Selected=true;
			oCn_QuaryForm.Open();
			oCmd_Init.CommandText="select tel_info from Phone where exists(select * from RoomInfo where phone=tel_bit) order by tel_bit";
			CBL_Tel.DataSource=oCmd_Init.ExecuteReader();
			CBL_Tel.DataBind();
			oCn_QuaryForm.Close();
				for(int i=0;i<CBL_Tel.Items.Count;i++)
					CBL_Tel.Items[i].Selected=true;
			oCn_QuaryForm.Open();
			oCmd_Init.CommandText="select unused_info from unused where exists(select * from RoomInfo where used=unused_bit) order by unused_bit";
			CBL_Used.DataSource=oCmd_Init.ExecuteReader();
			CBL_Used.DataBind();
			oCn_QuaryForm.Close();
				for(int i=0;i<CBL_Used.Items.Count;i++)
					CBL_Used.Items[i].Selected=true;
			oCn_QuaryForm.Open();
			oCmd_Init.CommandText="select sex from Sex";
			CBL_Sex.DataSource=oCmd_Init.ExecuteReader();
			CBL_Sex.DataBind();
			oCn_QuaryForm.Close();
				for(int i=0;i<CBL_Sex.Items.Count;i++)
					CBL_Sex.Items[i].Selected=true;
				oCn_QuaryForm.Open();
				oCmd_Init.CommandText="select media from multimedia";
				CBL_Multimedia.DataSource=oCmd_Init.ExecuteReader();
				CBL_Multimedia.DataBind();
				oCn_QuaryForm.Close();
				for(int i=0;i<CBL_Multimedia.Items.Count;i++)
					CBL_Multimedia.Items[i].Selected=true;
				SelectAll();
				DDL_Using.Items.Add(new ListItem("",""));
			}
		}

		#region Web 窗体设计器生成的代码
		override protected void OnInit(EventArgs e)
		{
			//
			// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
			//
			InitializeComponent();
			base.OnInit(e);
		}
		
		/// <summary>
		/// 设计器支持所需的方法 - 不要使用代码编辑器修改
		/// 此方法的内容。
		/// </summary>
		private void InitializeComponent()
		{    
			this.oCn_QuaryForm = new System.Data.Odbc.OdbcConnection();
			this.oDA_BNo = new System.Data.Odbc.OdbcDataAdapter();
			this.oCmd_Init = new System.Data.Odbc.OdbcCommand();
			this.Btn_QuaryForm.Click += new System.EventHandler(this.Btn_QuaryForm_Click);
			this.Btn_StatForm.Click += new System.EventHandler(this.Btn_StatForm_Click);
			this.Btn_PWDAlter.Click += new System.EventHandler(this.Button1_Click);
			this.DDL_Cond.SelectedIndexChanged += new System.EventHandler(this.DDL_Cond_SelectedIndexChanged);
			this.DDL_SeatCond.SelectedIndexChanged += new System.EventHandler(this.DDL_SeatCond_SelectedIndexChanged);
			this.Btn_Select.Click += new System.EventHandler(this.Btn_Select_Click);
			this.Btn_Quary.Click += new System.EventHandler(this.Btn_Quary_Click);
			// 
			// oDA_BNo
			// 
			this.oDA_BNo.SelectCommand = this.oCmd_Init;
			this.oDA_BNo.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
																							  new System.Data.Common.DataTableMapping("Table", "BuildingInfo", new System.Data.Common.DataColumnMapping[] {
																																																			  new System.Data.Common.DataColumnMapping("bno", "bno"),
																																																			  new System.Data.Common.DataColumnMapping("using", "using")})});
			// 
			// oCmd_Init
			// 
			this.oCmd_Init.Connection = this.oCn_QuaryForm;
			this.Load += new System.EventHandler(this.Page_Load);

		}
		#endregion

		private void DDL_Cond_SelectedIndexChanged(object sender, System.EventArgs e)
		{
			if(DDL_Cond.SelectedValue=="between")
			{
				Lb_And.Visible=true;
				TB_Area2.Visible=true;
			}
			else
			{
				Lb_And.Visible=false;
				TB_Area2.Visible=false;
			}
		}

		private void Btn_Select_Click(object sender, System.EventArgs e)
		{
				//如果要全选
				if(Btn_Select.Text == "全 选")
				{
                    SelectAll();
					Btn_Select.Text = "全不选";
				}
				else
				{
					SelectNone();
					Btn_Select.Text = "全 选";
				}
		}

		private void SelectAll()
		{
			CB_BNo.Checked=true;
			CB_RoomNo.Checked=true;
			CB_Area.Checked=true;
            CB_SeatNo.Checked=true;
            CB_Unit.Checked=true;
            CB_Usage.Checked=true;
			CB_Charge.Checked=true;
			CB_Water.Checked=true;
			CB_Elec.Checked=true;
			CB_net.Checked=true;
            CB_Tel.Checked=true;
            CB_Used.Checked=true;
            CB_Sex.Checked=true;
			CB_memo.Checked=true;
			CB_Multimedia.Checked=true;
		}

		private void SelectNone()
		{
			CB_BNo.Checked=false;
			CB_RoomNo.Checked=false;
			CB_Area.Checked=false;
			CB_SeatNo.Checked=false;
			CB_Unit.Checked=false;
			CB_Usage.Checked=false;
			CB_Charge.Checked=false;
			CB_Water.Checked=false;
			CB_Elec.Checked=false;
			CB_net.Checked=false;
			CB_Tel.Checked=false;
			CB_Used.Checked=false;
			CB_Sex.Checked=false;
			CB_memo.Checked=false;
			CB_Multimedia.Checked=true;
		}

		private void Btn_Quary_Click(object sender, System.EventArgs e)
		{
			string strQuary="SELECT V_CHNRoomInfo.序号";
			if(CB_BNo.Checked)    strQuary+=",V_CHNRoomInfo.楼号";
			if(CB_RoomNo.Checked)     strQuary+=",V_CHNRoomInfo.房屋号";
			if(CB_Area.Checked)      strQuary+=",V_CHNRoomInfo.面积";
			if(CB_SeatNo.Checked)       strQuary+=",V_CHNRoomInfo.座位数";
			if(CB_Unit.Checked)      strQuary+=",V_CHNRoomInfo.使用单位";
			if(CB_Usage.Checked)     strQuary+=",V_CHNRoomInfo.用途";
			if(CB_Water.Checked)      strQuary+=",V_CHNRoomInfo.水";
			if(CB_Elec.Checked)  strQuary+=",V_CHNRoomInfo.电";
			if(CB_net.Checked)      strQuary+=",V_CHNRoomInfo.宽带";
			if(CB_Tel.Checked)    strQuary+=",V_CHNRoomInfo.电话";
			if(CB_Charge.Checked)  strQuary+=",V_CHNRoomInfo.负责人";
			if(CB_Used.Checked)  strQuary+=",V_CHNRoomInfo.是否闲置";
			if(CB_Sex.Checked)  strQuary+=",V_CHNRoomInfo.性别";
			if(CB_Multimedia.Checked)  strQuary+=",V_CHNRoomInfo.多媒体";
			if(CB_memo.Checked)   strQuary+=",V_CHNRoomInfo.备注";
			strQuary+=" FROM V_CHNRoomInfo WHERE 1=1";
			int i;
			//动态添加条件表达式
			//楼号
			try
			{
				strQuary+=" AND (V_CHNRoomInfo.楼号 in (''";
				for(i=0;i<CBL_BNo.Items.Count;i++)
				{
					if(CBL_BNo.Items[i].Selected)
						strQuary+=",'"+CBL_BNo.Items[i].Value+"'";
				}
			}
			finally
			{
				strQuary+="))";
			}
			//房屋号
			if(TB_RoomNo.Text!="")
				strQuary+=" AND (V_CHNRoomInfo.房屋号 like '%" +TB_RoomNo.Text + "%')";
			//面积
				if(DDL_Cond.SelectedValue!="between" && TB_Area1.Text!="")
					strQuary+=" AND (V_CHNRoomInfo.面积 " + DDL_Cond.SelectedValue + TB_Area1.Text+")";
				else if(TB_Area1.Text!="" && TB_Area2.Text!="")
                    strQuary+=" AND (V_CHNRoomInfo.面积 "+DDL_Cond.SelectedValue+" "+TB_Area1.Text+" AND "+TB_Area2.Text+")";
			//座位数
				if(DDL_SeatCond.SelectedValue!="between" && TB_Seat1.Text!="")
					strQuary+=" AND (V_CHNRoomInfo.座位数 " + DDL_SeatCond.SelectedValue + TB_Seat1.Text+")";
				else if(TB_Seat1.Text!="" && TB_Seat2.Text!="")
                    strQuary+=" AND (V_CHNRoomInfo.座位数 "+DDL_SeatCond.SelectedValue+" "+TB_Seat1.Text+" AND "+TB_Seat2.Text+")";
			//使用单位
			if(TB_Unit.Text!="")
				strQuary+=" AND (V_CHNRoomInfo.使用单位 like '%"+TB_Unit.Text+"%')";
			//用途
			if(DDL_Using.SelectedValue!="")    			   strQuary+=" AND (V_CHNRoomInfo.用途='" + DDL_Using.SelectedItem.Text + "')";
			//水
			try
			{
				strQuary+=" AND (V_CHNRoomInfo.水 in (''";
				for(i=0;i<CBL_Water.Items.Count;i++)
				{
					if(CBL_Water.Items[i].Selected)
						strQuary+=",'"+CBL_Water.Items[i].Value+"'";
				}
			}
			finally
			{
				strQuary+="))";
			}
			//电
			try
			{
				strQuary+=" AND (V_CHNRoomInfo.电 in (''";
				for(i=0;i<CBL_Elec.Items.Count;i++)
				{
					if(CBL_Elec.Items[i].Selected)
						strQuary+=",'"+CBL_Elec.Items[i].Value+"'";
				}
			}
			finally
			{
				strQuary+="))";
			}
			//宽带
			try
			{
				strQuary+=" AND (V_CHNRoomInfo.宽带 in (''";
				for(i=0;i<CBL_Net.Items.Count;i++)
				{
					if(CBL_Net.Items[i].Selected)
						strQuary+=",'"+CBL_Net.Items[i].Value+"'";
				}
			}
			finally
			{
				strQuary+="))";
			}
			//电话
			try
			{
				strQuary+=" AND (V_CHNRoomInfo.电话 in (''";
				for(i=0;i<CBL_Tel.Items.Count;i++)
				{
					if(CBL_Tel.Items[i].Selected)
						strQuary+=",'"+CBL_Tel.Items[i].Value+"'";
				}
			}
			finally
			{
				strQuary+="))";
			}
			//负责人
			if(TB_Charge.Text!="")	                   strQuary+=" AND (V_CHNRoomInfo.负责人 LIKE '%"+TB_Charge.Text+"%')";
			//是否闲置
			try
			{
				strQuary+=" AND (V_CHNRoomInfo.是否闲置 in (''";
				for(i=0;i<CBL_Used.Items.Count;i++)
				{
					if(CBL_Used.Items[i].Selected)
						strQuary+=",'"+CBL_Used.Items[i].Value+"'";
				}
			}
			finally
			{
				strQuary+="))";
			}
			//性别
			try
			{
				strQuary+=" AND (V_CHNRoomInfo.性别 in (''";
				for(i=0;i<CBL_Sex.Items.Count;i++)
				{
					if(CBL_Sex.Items[i].Selected)
						strQuary+=",'"+CBL_Sex.Items[i].Value+"'";
				}
			}
			finally
			{
				strQuary+="))";
			}
			//多媒体
			//性别
			try
			{
				strQuary+=" AND (V_CHNRoomInfo.多媒体 in (''";
				for(i=0;i<CBL_Multimedia.Items.Count;i++)
				{
					if(CBL_Multimedia.Items[i].Selected)
						strQuary+=",'"+CBL_Multimedia.Items[i].Value+"'";
				}
			}
			finally
			{
				strQuary+="))";
			}
			//返回生成的动态查询语句
			Session["QuaryStr"]=strQuary;
			Server.Transfer("QuaryResult.aspx");
		}

		private void DDL_SeatCond_SelectedIndexChanged(object sender, System.EventArgs e)
		{
			if(DDL_SeatCond.SelectedValue=="between")
			{
				Lb_And2.Visible=true;
				TB_Seat2.Visible=true;
			}
			else
			{
				Lb_And2.Visible=false;
				TB_Seat2.Visible=false;
			}
		
		}

		private void Button1_Click(object sender, System.EventArgs e)
		{
			Server.Transfer("PWDMng.aspx");
		}

		private void Btn_StatForm_Click(object sender, System.EventArgs e)
		{
			Server.Transfer("Statistic.aspx");
		}

		private void Btn_QuaryForm_Click(object sender, System.EventArgs e)
		{
			Server.Transfer("QuaryForm");
		}
	}
}