hotelquery.aspx.cs

酒店管理,主要从事酒店管理都可以.这可是北大青鸟的程序

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.Odbc;

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 HotelHome;
using HotelHome.room;
using HotelHome.Command;

namespace HotelHome

{
	/// <summary>
	/// HotelQuery 的摘要说明。
	/// </summary>
	public class HotelQuery : System.Web.UI.Page
	{
		protected System.Web.UI.WebControls.DataGrid DataGrid1;
		protected System.Web.UI.WebControls.Button btnQuery;
		protected System.Web.UI.HtmlControls.HtmlImage IMG1;
		protected System.Web.UI.HtmlControls.HtmlImage img2;
		protected System.Web.UI.WebControls.DropDownList hotelLevel;
		protected System.Web.UI.WebControls.DropDownList maxprice;
		protected System.Web.UI.WebControls.DropDownList place;
		protected System.Web.UI.WebControls.TextBox HotelName;
		protected System.Web.UI.WebControls.DropDownList roomtype;
		protected System.Data.DataSet roomtype_ds;
		protected System.Data.DataSet place_ds;
		protected System.Web.UI.WebControls.Label Label1;
		protected PageControl PageControl1;
		protected System.Web.UI.WebControls.TextBox startdate;
		protected System.Web.UI.WebControls.TextBox enddate;
		protected System.Web.UI.WebControls.TextBox txtNum;
		DBService mycon;
		private void Page_Load(object sender, System.EventArgs e)
		{
			// 在此处放置用户代码以初始化页面
			mycon=new DBService();
			this.PageControl1.setDataGrid(this.DataGrid1);
		
			if(!this.IsPostBack)
			{
				this.PageControl1.setPageSize(Convert.ToInt32(System.Configuration.ConfigurationSettings.AppSettings["PageSize"]));
			
				this.PageControl1.setSQL("select * from temproom where 1=0");
			
				this.DataGrid1.Visible=false;
				this.PageControl1.Visible=false;
				
				this.hotelLevel.Items.Clear();
				this.maxprice.Items.Clear();
				
				//酒店星级
				this.hotelLevel.Items.Add("不限");
				this.hotelLevel.Items.Add("3");
				this.hotelLevel.Items.Add("4");
				this.hotelLevel.Items.Add("5");
        
				//最高价格
				this.maxprice.Items.Add("不限");
				this.maxprice.Items.Add("400");
				this.maxprice.Items.Add("800");
				this.maxprice.Items.Add("1200");
				this.maxprice.Items.Add("1600");
				this.maxprice.Items.Add("2000");
			
        
				//客房类型	--从数据库获取后绑定 //要设置DATAValueField ,dataFieldText的值为表的列名
				string sqlstr="select typeid,typename from roomtype";
				this.roomtype_ds=mycon.executeBySQL(sqlstr);
				this.roomtype.DataSource=this.roomtype_ds.Tables[0];//
				this.roomtype.DataValueField="typeid";
				this.roomtype.DataTextField ="typename";
				this.roomtype.DataBind();

				this.roomtype.Items.Insert(0,"不限");
				//地理位置  --从数据库获取后绑定
				string sql="select distinct(hotelplace) hotelplace from hotel";
				this.place_ds=mycon.executeBySQL(sql);
				this.place.DataSource=this.place_ds.Tables[0];
				this.place.DataBind();
				this.place.Items.Insert(0,"不限");

				//
				this.startdate.Text=System.DateTime.Today.ToShortDateString();
				this.enddate.Text=System.DateTime.Today.ToShortDateString();
				//
				this.txtNum.Text="1";
				//
				
				this.IMG1.Attributes.Add("onclick","return calendar(startdate)");
				this.img2.Attributes.Add("onclick","return calendar(enddate)");
				this.btnQuery.Attributes.Add("onclick","return doValidate();");
				//
				if(Session["memID"]==null)
				{
					this.DataGrid1.Columns[7].Visible=false;
				}
			}
			
		}

		#region Web 窗体设计器生成的代码
		override protected void OnInit(EventArgs e)
		{
			//
			// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
			//
			InitializeComponent();
			base.OnInit(e);
		}
		
		/// <summary>
		/// 设计器支持所需的方法 - 不要使用代码编辑器修改
		/// 此方法的内容。
		/// </summary>
		private void InitializeComponent()
		{    
			this.roomtype_ds = new System.Data.DataSet();
			this.place_ds = new System.Data.DataSet();
			((System.ComponentModel.ISupportInitialize)(this.roomtype_ds)).BeginInit();
			((System.ComponentModel.ISupportInitialize)(this.place_ds)).BeginInit();
			this.btnQuery.Click += new System.EventHandler(this.Button1_Click);
			this.DataGrid1.SelectedIndexChanged += new System.EventHandler(this.DataGrid1_SelectedIndexChanged);
			// 
			// roomtype_ds
			// 
			this.roomtype_ds.DataSetName = "NewDataSet";
			this.roomtype_ds.Locale = new System.Globalization.CultureInfo("zh-CN");
			// 
			// place_ds
			// 
			this.place_ds.DataSetName = "NewDataSet";
			this.place_ds.Locale = new System.Globalization.CultureInfo("zh-CN");
			this.Load += new System.EventHandler(this.Page_Load);
			((System.ComponentModel.ISupportInitialize)(this.roomtype_ds)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.place_ds)).EndInit();

		}
		#endregion
		//预订该房
		public  void reserve(object sender, System.EventArgs e)
		{   
			string roomID=((DataGridItem)((LinkButton)sender).Parent.Parent).Cells[1].Text;
			String sqlstring="select guestroom.roomid roomid, hotel.hotelid, hotel.Hotelname,roomtype.typeid,roomtype.typename , hotel.HotelLevel from hotel,guestroom,roomtype where hotel.hotelID=guestroom.hotelID and guestroom.roomtype=roomtype.typeid and guestroom.roomid='"+roomID+"'";
			DataSet ds1=mycon.executeBySQL(sqlstring);
			roomDTO roomdto=new roomDTO();
			roomdto.roomID=Convert.ToInt32(ds1.Tables[0].Rows[0][0]==DBNull.Value?"0":ds1.Tables[0].Rows[0][0]);
			roomdto.hotelID=Convert.ToInt32(ds1.Tables[0].Rows[0][1]==DBNull.Value?"0":ds1.Tables[0].Rows[0][1]);
			roomdto.hotelname=Convert.ToString(ds1.Tables[0].Rows[0][2]==DBNull.Value?"":ds1.Tables[0].Rows[0][2]);
			roomdto.roomtype=Convert.ToInt32(ds1.Tables[0].Rows[0][3]==DBNull.Value?"0":ds1.Tables[0].Rows[0][3]);
			roomdto.typename=Convert.ToString(ds1.Tables[0].Rows[0][4]==DBNull.Value?"":ds1.Tables[0].Rows[0][4]);
			roomdto.HotelLevel=Convert.ToInt32(ds1.Tables[0].Rows[0][5]==DBNull.Value?"0":ds1.Tables[0].Rows[0][5]);
			DateTime startDate=Convert.ToDateTime(this.startdate.Text);
			DateTime endDate=Convert.ToDateTime(this.enddate.Text);
			Guid guid=Guid.NewGuid();
			string strGuid=guid.ToString().Replace("-","");
			Session["GUID"]=strGuid;
			int i_num=Convert.ToInt32(this.txtNum.Text);
	        bool available=Business.query(startDate,endDate,roomdto,i_num,strGuid);
			if(available!=false)
			{
				String insSQL="insert into reserve(roomID,resStartDate,resEndDate,MemID,status,resNumber,bookdate) values('"+roomdto.roomID+"','"+startDate+"','";
				insSQL+=endDate+"','"+Session["memID"]+"','1','"+this.txtNum.Text+"',sysdate())";
				
				int days=Business.getDays(startDate,endDate);
				System.Data.Odbc.OdbcTransaction tran;//声明事务(必须在同一个Connection里)
				OdbcConnection conn=mycon.getCon();
				conn.Open();
				OdbcCommand comm=new OdbcCommand();
				tran=conn.BeginTransaction();
				comm.Connection=conn;
				comm.Transaction=tran;
				try
				{							
					comm.CommandText=insSQL;
					int query=comm.ExecuteNonQuery();
					comm.Parameters.Clear();//使用同一个command时必须先clear           
				
					//选定日期范围的天数--即要预订的天数
					int dateCount=0;
					dateCount=Business.getDays(startDate,endDate);
				                
					String comstring;
					for(int count=0;count<dateCount;count++)
					{
						//查找在选定日期内记录是否存在
						string sql;	
						DateTime dtTmp=startDate.Add(new TimeSpan(count,0,0,0,0));						
						sql="select count(0) from inuseroom where roomid='"+roomdto.roomID+"' and resdate=DATE_ADD('"+startDate.ToString()+"',INTERVAL '"+count+"' DAY)";
						comm.CommandText=sql;
						int intCount=Convert.ToInt32(comm.ExecuteScalar());				

						//当天记录已存在
						if(intCount>0)
						{		
					
							comstring="update inuseroom set inuseNumber=inuseNumber+? where roomid='"+roomdto.roomID+"' and resdate=DATE_ADD('"+startDate.ToString()+"',INTERVAL '"+count+"' DAY)";
							comm.CommandText=comstring;					
							comm.Parameters.Add("1",i_num);					
							
						}
						else
						{				
							comstring="insert into inuseroom(roomid,resDate,inuseNumber) values(?,DATE_ADD('"+startDate.ToString()+"',INTERVAL '"+count+"' DAY),?) ";
							comm.CommandText=comstring;
							comm.Parameters.Add("1",roomdto.roomID);					
							comm.Parameters.Add("2",i_num);

						}				  
						query=comm.ExecuteNonQuery();
						comm.Parameters.Clear();
					}
					tran.Commit();//提交事务
					Response.Write("<script>alert('恭喜您预订成功!');</script>");			
					//this.Response.Redirect("../HotelQuery.aspx");
				}
				catch(Exception ex)
				{	
					tran.Rollback();  
					Console.WriteLine("Error:"+ex.Message.ToString()); 
					Response.Write("<script>alert('预订失败');</script>");
			    
					//this.Response.Redirect("HotelQuery.aspx");
				}				
				finally
				{
					try
					{
						conn.Close();
					}
					catch
					{
					}
				}	 		
			}
			else
			{
				Response.Write("<scirpt>alert('预定失败！')</sciprt>");
			}

//			this.Session["hotelname"]=((DataGridItem)((LinkButton)sender).Parent.Parent).Cells[3].Text;
//			this.Session["roomtype"]=((DataGridItem)((LinkButton)sender).Parent.Parent).Cells[4].Text;
//			this.Session["roomid"]=((DataGridItem)((LinkButton)sender).Parent.Parent).Cells[1].Text;
//			this.Session["startdate"]=this.startdate.Text.Trim();
//			this.Session["enddate"]=this.enddate.Text.Trim();
//
//			DataSet numds=new DataSet();
//			string sql="select max(inuseNumber) m from inuseroom where roomid="+this.Session["roomid"]+" and resdate between '"+this.startdate.Text.Trim()+"' and '"+this.enddate.Text.Trim()+"'";
//			numds=mycon.executeBySQL(sql);			
//			//在指定日期范围内客房已被预订的最大数量
//            int maxinuseNum=0;
//			//客房总量
//			int total=Convert.ToInt32(((DataGridItem)((LinkButton)sender).Parent.Parent).Cells[6].Text);
//			//在指定日期范围内客房允许预订的最大数量
//			int maxresNum=0;
//			if(numds.Tables[0].Rows.Count>0)
//			{
//				if(numds.Tables[0].Rows[0]["m"]==System.DBNull.Value)
//				{
//				  maxinuseNum=0;
//				}
//				else
//				{
//				  maxinuseNum=Convert.ToInt32(numds.Tables[0].Rows[0]["m"]);
//				}
//				this.Response.Write(maxinuseNum.ToString());			
//    			maxresNum=total-maxinuseNum;
//			}
//			if(maxresNum>0)
//			{
//				this.Session["maxresNum"]=maxresNum.ToString();
//				this.Response.Redirect("hotelreserve.aspx");
//			}
//			else
//			{
//			  this.Response.Write("<script>alert('对不起，该客房暂不能预订！ ');</script>");
//			}

		
		}
		//查看详情
		public void detail(object sender, System.EventArgs e)
		{ 
			string roomID=((DataGridItem)((LinkButton)sender).Parent.Parent).Cells[1].Text;
			this.Session["hotelname"]=((DataGridItem)((LinkButton)sender).Parent.Parent).Cells[3].Text;
			this.Session["roomtype"]=((DataGridItem)((LinkButton)sender).Parent.Parent).Cells[4].Text;
			this.Session["roomid"]=roomID;
			this.Session["startdate"]=this.startdate.Text.Trim();
			this.Session["enddate"]=this.enddate.Text.Trim();
            this.Response.Redirect("room_Detail.aspx?roomID="+ roomID );
		}

		//查询
		private void Button1_Click(object sender, System.EventArgs e)
		{		
			
			DataSet ds1=new DataSet();
			DBService dbs=new DBService();
			string sqlstring;
			string strWhere=" and 1=1 ";//where语句
			//level
			if(!this.hotelLevel.SelectedValue.Equals("不限"))
			{
				strWhere += " and hotel.HotelLevel=" + this.hotelLevel.SelectedValue;
			}
			//roomtype
			if(!this.roomtype.SelectedValue.Equals("不限"))
			{
			   strWhere += " and guestroom.roomtype='"+this.roomtype.SelectedValue+"'";
			}
			//date


			//maxprice
			if(!this.maxprice.SelectedValue.Equals("不限"))
			{
				strWhere += " and guestroom.roomprice<=" +this.maxprice.SelectedValue;
			}			
            //place
			if(!this.place.SelectedValue.Equals("不限"))
			{
				strWhere += " and hotel.hotelplace='"+this.place.SelectedValue+"'";
			}

			//hotelname
			if(!this.HotelName.Equals("")&&this.HotelName!=null)
			{
				strWhere += " and hotel.Hotelname like '%" + this.HotelName.Text.Trim()+"%'";
			}
			//SQL string
			sqlstring="select guestroom.roomid roomid, hotel.hotelid, hotel.Hotelname,roomtype.typeid,roomtype.typename , hotel.HotelLevel from hotel,guestroom,roomtype where hotel.hotelID=guestroom.hotelID and guestroom.roomtype=roomtype.typeid "+strWhere;
          	
			//
			ds1=dbs.executeBySQL(sqlstring);
			int i_count=ds1.Tables[0].Rows.Count;
			System.Collections.ArrayList tempList=new ArrayList();
			for(int i=0;i<i_count-1;i++)
			{
				roomDTO roomdto=new roomDTO();
				roomdto.roomID=Convert.ToInt32(ds1.Tables[0].Rows[i][0]==DBNull.Value?"0":ds1.Tables[0].Rows[i][0]);
				roomdto.hotelID=Convert.ToInt32(ds1.Tables[0].Rows[i][1]==DBNull.Value?"0":ds1.Tables[0].Rows[i][1]);
				roomdto.hotelname=Convert.ToString(ds1.Tables[0].Rows[i][2]==DBNull.Value?"":ds1.Tables[0].Rows[i][2]);
				roomdto.roomtype=Convert.ToInt32(ds1.Tables[0].Rows[i][3]==DBNull.Value?"0":ds1.Tables[0].Rows[i][3]);
				roomdto.typename=Convert.ToString(ds1.Tables[0].Rows[i][4]==DBNull.Value?"":ds1.Tables[0].Rows[i][4]);
				roomdto.HotelLevel=Convert.ToInt32(ds1.Tables[0].Rows[i][5]==DBNull.Value?"0":ds1.Tables[0].Rows[i][5]);
				tempList.Add(roomdto);
			}

			DateTime startDate=Convert.ToDateTime(this.startdate.Text);
			DateTime endDate=Convert.ToDateTime(this.enddate.Text);
			int i_num=Convert.ToInt32(this.txtNum.Text);

			bool available=false;
			ArrayList removeList=new ArrayList();
			Guid guid=Guid.NewGuid();
			string strGuid=guid.ToString().Replace("-","");
			Session["GUID"]=strGuid;
			foreach(roomDTO roomdto in tempList)
			{	
				string roomID=roomdto.roomID.ToString();
				available=Business.query(startDate,endDate,roomdto,i_num,strGuid);
				if(available==false)
				{
					removeList.Add(roomdto);
				}
			}

			foreach(roomDTO roomdto in removeList)
			{
				tempList.Remove(roomdto);
			}

			foreach(roomDTO roomdto in tempList)
			{
				
				String insSQL="insert into temproom(roomid,hotelid,hotellevel,roomtype,hotelname,leftnum,typename,price,guid) values('"+roomdto.roomID+"','";
				insSQL+=roomdto.hotelID+"',"+roomdto.HotelLevel.ToString()+",'"+roomdto.roomtype+"','"+roomdto.hotelname+"','"+roomdto.leftNum+"','"+roomdto.typename+"',"+roomdto.Price.ToString()+",'"+strGuid+"')";
				dbs.ExecuteNonQuery(insSQL);
			}
	
			sqlstring="select * from temproom where guid='"+strGuid+"'";

            this.PageControl1.setSQL(sqlstring);  
    		this.PageControl1.MyDataBind();
			this.DataGrid1.Visible=true;
			this.PageControl1.Visible=true;
		}

		private void DataGrid1_SelectedIndexChanged(object sender, System.EventArgs e)
		{
		
		}	

		

		
	
	}
}