query.java

用java实现的酒店管理系统

						tf12.setText("");
						tf11.requestFocus();
					}else {//若日期合法
						String nrc = tf13.getText();
						String sqlCode = "select a.chk_no 帐单号,b.r_no 房间号,b.c_name 宾客姓名,b.foregift 已收押金,a.money 实收金额,a.chk_time 结算时间,a.remark 备注 "+
										 "from checkout as a,livein as b where a.delmark = 0 and a.in_no = b.in_no and  (a.chk_no like '%"+nrc+"%' or b.r_no like '%"+nrc+"%' or b.c_name like '%"+nrc+"%') and a.chk_time between '"+start+"' and '"+end+"'";
						sunsql.initDTM(dtm1,sqlCode);
					}
					
				}
			}else {
				if(!chk12.isSelected()) {//****************两项全不选择
					JOptionPane.showMessageDialog(null,"请选择查询方式!");
					tf11.setText("");
					tf12.setText("");
					tf13.setText("");
					tf11.requestFocus();
				}else {                 //*****************只选择姓名/房号/帐单号查询
					String nrc = tf13.getText();
					String sqlCode = "select a.chk_no 帐单号,b.r_no 房间号,b.c_name 宾客姓名,b.foregift 已收押金,a.money 实收金额,a.chk_time 结算时间,a.remark 备注 "+
									 "from checkout as a,livein as b where a.delmark = 0 and a.in_no = b.in_no and  (a.chk_no like '%"+nrc+"%' or b.r_no like '%"+nrc+"%' or b.c_name like '%"+nrc+"%')";
					sunsql.initDTM(dtm1,sqlCode);
					tf11.setText("");	
					tf12.setText("");	
				}
			}
		}else if(o==bt12) {
			//===========================================================结帐单刷新
			String sqlCode = "select a.chk_no 帐单号,b.r_no 房间号,b.c_name 宾客姓名,b.foregift 已收押金,a.money 实收金额,a.chk_time 结算时间,a.remark 备注 "+
							 "from checkout as a,livein as b where a.delmark = 0 and a.in_no = b.in_no";
			sunsql.initDTM(dtm1,sqlCode);
			tf11.setText("");
			tf12.setText("");
			tf13.setText("");
			chk11.setSelected(false);
			chk12.setSelected(false);
		}else if(o==bt21) {
			//===========================================================全部宾客查询
			String nzr = tf21.getText();
			String sqlCode = "select m_id 会员编号,r_no 房间号,c_name 宾客姓名,sex 性别,zj_type 证件类型,zj_no 证件编号,renshu 人数,foregift 押金,"+
							 "days 预住天数,statemark 当前状态,in_time 入住时间,chk_time 结帐时间,chk_no 结算单号 from livein where delmark = 0 and (c_name like '%"+nzr+"%' or zj_no like '%"+nzr+"%' or r_no like '%"+nzr+"%')";
			sunsql.initDTM(dtm2,sqlCode);
		}else if(o==bt22) {
			//===========================================================全部宾客刷新
			String sqlCode = "select m_id 会员编号,r_no 房间号,c_name 宾客姓名,sex 性别,zj_type 证件类型,zj_no 证件编号,renshu 人数,foregift 押金,"+
							 "days 预住天数,statemark 当前状态,in_time 入住时间,chk_time 结帐时间,chk_no 结算单号 from livein where delmark = 0";
			sunsql.initDTM(dtm2,sqlCode);
			tf21.setText("");
		}else if(o==bt23) {
			//===========================================================今日来宾
			GregorianCalendar gc = new GregorianCalendar();
			String year = gc.get (GregorianCalendar.YEAR) + "";
			//为月份补'0'
			String month = gc.get (GregorianCalendar.MONTH) + 1 + "";
			if( month.length() == 1)
				month = "0" + month;
			//为天补'0'
			String day = gc.get (GregorianCalendar.DAY_OF_MONTH) + "";
			if( day.length () == 1)
				day = "0" + day;
			String in_time = year+"-"+month+"-"+day;
			
			String nzr = tf21.getText();
			String sqlCode = "select m_id 会员编号,r_no 房间号,c_name 宾客姓名,sex 性别,zj_type 证件类型,zj_no 证件编号,renshu 人数,foregift 押金,"+
							 "days 预住天数,statemark 当前状态,in_time 入住时间,chk_time 结帐时间,chk_no 结算单号 from livein where delmark = 0 and (c_name like '%"+nzr+"%' or zj_no like '%"+nzr+"%' or r_no like '%"+nzr+"%') and in_time = '"+in_time+"'";
			sunsql.initDTM(dtm2,sqlCode);
		}else if(o==bt31) {
			//===========================================================在店宾客查询
			if(rb31.isSelected()) {//*********************按入住时间查询
				String start = tf31.getText();
				String end = tf32.getText();
				if(!suntools.isDate(start)||!suntools.isDate(end)) {
					//若日期不合法
					JOptionPane.showMessageDialog(null,"日期输入有误,请正确输入(yyyy-mm-dd)");
					tf31.setText("");
					tf32.setText("");
					tf33.setText("");
					tf31.requestFocus();
				}else {//若日期合法
					start = tf31.getText()+" 00:00:00";
					end = tf32.getText()+" 23:59:59";	
					String sqlCode = "select a.r_no 房间号,b.r_type 房间类型,b.price 单价,c.discount 折扣比例,c.dis_price 折后单价,(c.price - c.dis_price) 优惠金额,a.in_time 入住时间 "+
									 "from livein as a,roomtype as b,customertype as c where a.statemark = '正在消费' and a.delmark = 0 and a.r_type_id = b.id and a.c_type_id = c.id and a.r_type_id = c.dis_attr and a.in_time between '"+start+"' and '"+end+"'";
					sunsql.initDTM(dtm3,sqlCode);
					tf33.setText("");
					}
			}else if(rb32.isSelected()) {//****************按房间号查询
				String r_no = tf33.getText();
				String sqlCode = "select a.r_no 房间号,b.r_type 房间类型,b.price 单价,c.discount 折扣比例,c.dis_price 折后单价,(c.price - c.dis_price) 优惠金额,a.in_time 入住时间 "+
								 "from livein as a,roomtype as b,customertype as c where a.statemark = '正在消费' and a.delmark = 0 and a.r_type_id = b.id and a.c_type_id = c.id and a.r_type_id = c.dis_attr and a.r_no like '%"+r_no+"%'";
				sunsql.initDTM(dtm3,sqlCode);
				tf31.setText("");
				tf32.setText("");
			}
		}else if(o==bt32) {
			//===========================================================在店宾客刷新
			String sqlCode = "select a.r_no 房间号,b.r_type 房间类型,b.price 单价,c.discount 折扣比例,c.dis_price 折后单价,(c.price - c.dis_price) 优惠金额,a.in_time 入住时间 "+
							 "from livein as a,roomtype as b,customertype as c where a.statemark = '正在消费' and a.delmark = 0 and a.r_type_id = b.id and a.c_type_id = c.id and a.r_type_id = c.dis_attr";
			sunsql.initDTM(dtm3,sqlCode);
			tf31.setText("");
			tf32.setText("");
			tf33.setText("");
			rb31.setSelected(true);
		}else if(o==bt41) {
			//===========================================================离店宾客查询
			if(chk41.isSelected()) {
				if(!chk42.isSelected()) {//**************只选择结帐时间查询
					String start = tf41.getText();
					String end = tf42.getText();
					if(!suntools.isDate(start)||!suntools.isDate(end)) {
						//若日期不合法
						JOptionPane.showMessageDialog(null,"日期输入有误,请正确输入(yyyy-mm-dd)");
						tf41.setText("");
						tf42.setText("");
						tf43.setText("");
						tf41.requestFocus();
						cb41.setSelectedIndex(0);
					}else {//若日期合法
						start = tf41.getText()+" 00:00:00";
						end = tf42.getText()+" 23:59:59";
						String sqlCode = "select a.chk_no 帐单号,b.r_no 房间号,c.r_type 房间类型,c.price 单价,d.discount 折扣比例,d.dis_price 实收金额,(d.price - d.dis_price) 优惠金额,a.chk_time 入帐时间 "+
										 "from checkout as a,livein as b,roomtype as c,customertype as d where a.delmark = 0 and b.statemark = '已结算' and a.in_no = b.in_no and b.r_type_id = c.id and b.c_type_id = d.id and b.r_type_id = d.dis_attr and a.chk_time between '"+start+"' and '"+end+"'";
						sunsql.initDTM(dtm4,sqlCode);
						tf43.setText("");
						chk41.setSelected(false);
						chk42.setSelected(false);
						cb41.setSelectedIndex(0);
					}
				}else {                 //***************两项联合查询
					String start = tf41.getText();
					String end = tf42.getText();
					if(!suntools.isDate(start)||!suntools.isDate(end)) {
						//若日期不合法
						JOptionPane.showMessageDialog(null,"日期输入有误,请正确输入(yyyy-mm-dd)");
						tf41.setText("");
						tf42.setText("");
						tf41.requestFocus();
					}else {//若日期合法
						String nrc = tf43.getText();
						if(cb41.getSelectedIndex()==0) {//按结帐单号
							String sqlCode = "select a.chk_no 帐单号,b.r_no 房间号,c.r_type 房间类型,c.price 单价,d.discount 折扣比例,d.dis_price 实收金额,(d.price - d.dis_price) 优惠金额,a.chk_time 入帐时间 "+
											 "from checkout as a,livein as b,roomtype as c,customertype as d where a.delmark = 0 and b.statemark = '已结算' and a.in_no = b.in_no and b.r_type_id = c.id and b.c_type_id = d.id and b.r_type_id = d.dis_attr and a.chk_no like '%"+nrc+"%' and a.chk_time between '"+start+"' and '"+end+"'";
							sunsql.initDTM(dtm4,sqlCode);
						}else if(cb41.getSelectedIndex()==1) {//按房间号
							String sqlCode = "select a.chk_no 帐单号,b.r_no 房间号,c.r_type 房间类型,c.price 单价,d.discount 折扣比例,d.dis_price 实收金额,(d.price - d.dis_price) 优惠金额,a.chk_time 入帐时间 "+
											 "from checkout as a,livein as b,roomtype as c,customertype as d where a.delmark = 0 and b.statemark = '已结算' and a.in_no = b.in_no and b.r_type_id = c.id and b.c_type_id = d.id and b.r_type_id = d.dis_attr and b.r_no like '%"+nrc+"%' and a.chk_time between '"+start+"' and '"+end+"'";
							sunsql.initDTM(dtm4,sqlCode);
						}
					}
					
				}
			}else {
				if(!chk42.isSelected()) {//****************两项全不选择
					JOptionPane.showMessageDialog(null,"请选择查询方式!");
					tf41.setText("");
					tf42.setText("");
					tf43.setText("");
					cb41.setSelectedIndex(0);
				}else {                 //*****************只选择房号/帐单号查询
					String nrc = tf43.getText();
					if(cb41.getSelectedIndex()==0) {//按结帐单号
						String sqlCode = "select a.chk_no 帐单号,b.r_no 房间号,c.r_type 房间类型,c.price 单价,d.discount 折扣比例,d.dis_price 实收金额,(d.price - d.dis_price) 优惠金额,a.chk_time 入帐时间 "+
										 "from checkout as a,livein as b,roomtype as c,customertype as d where a.delmark = 0 and b.statemark = '已结算' and a.in_no = b.in_no and b.r_type_id = c.id and b.c_type_id = d.id and b.r_type_id = d.dis_attr and a.chk_no like '%"+nrc+"%'";
						sunsql.initDTM(dtm4,sqlCode);
					}else if(cb41.getSelectedIndex()==1) {//按房间号
						String sqlCode = "select a.chk_no 帐单号,b.r_no 房间号,c.r_type 房间类型,c.price 单价,d.discount 折扣比例,d.dis_price 实收金额,(d.price - d.dis_price) 优惠金额,a.chk_time 入帐时间 "+
										 "from checkout as a,livein as b,roomtype as c,customertype as d where a.delmark = 0 and b.statemark = '已结算' and a.in_no = b.in_no and b.r_type_id = c.id and b.c_type_id = d.id and b.r_type_id = d.dis_attr and b.r_no like '%"+nrc+"%'";
						sunsql.initDTM(dtm4,sqlCode);
					}
					tf41.setText("");
					tf42.setText("");
				}
			}
		}else if(o==bt42) {
			//离店宾客刷新
			String sqlCode = "select a.chk_no 帐单号,b.r_no 房间号,c.r_type 房间类型,c.price 单价,d.discount 折扣比例,d.dis_price 实收金额,(d.price - d.dis_price) 优惠金额,a.chk_time 入帐时间 "+
						 	 "from checkout as a,livein as b,roomtype as c,customertype as d where a.delmark = 0 and b.statemark = '已结算' and a.in_no = b.in_no and b.r_type_id = c.id and b.c_type_id = d.id and b.r_type_id = d.dis_attr";
			sunsql.initDTM(dtm4,sqlCode);
			tf41.setText("");
			tf42.setText("");
			tf43.setText("");
			chk41.setSelected(false);
			chk42.setSelected(false);
			cb41.setSelectedIndex(0);
		}
	}
	
	/**=======================================================================**
	 *			MouseListener 监听
	 **=======================================================================**
	 */
	public void mouseClicked (MouseEvent me) {
	}

	public void mousePressed (MouseEvent me) {
	}

	public void mouseReleased(MouseEvent me) {
	}

	public void mouseEntered (MouseEvent me) {		//鼠标移进提示
		Object o = me.getSource();
		if(o==bt11) {
			//结帐单查询
			HotelFrame.lbA.setText (HotelFrame.clue + 
			"查询结帐单信息　      　　　　　　　　　　　　　　　　　 　");
		}else if(o==bt12) {
			//结帐单刷新
			HotelFrame.lbA.setText (HotelFrame.clue + 
			"刷新结帐单信息　      　　　　　　　　　　　　　　　　　 　");
		}else if(o==bt21) {
			//全部宾客查询
			HotelFrame.lbA.setText (HotelFrame.clue + 
			"查询全部宾客信息      　　　　　　　　　　　　　　　　　 　");
		}else if(o==bt22) {
			//全部宾客刷新
			HotelFrame.lbA.setText (HotelFrame.clue + 
			"刷新全部宾客信息      　　　　　　　　　　　　　　　　　 　");
		}else if(o==bt23) {
			//全部宾客过滤
			HotelFrame.lbA.setText (HotelFrame.clue + 
			"查询今日来宾信息      　　　　　　　　　　　　　　　　　 　");
		}else if(o==bt31) {
			//在店宾客查询
			HotelFrame.lbA.setText (HotelFrame.clue + 
			"查询在店宾客信息      　　　　　　　　　　　　　　　　　 　");
		}else if(o==bt32) {
			//在店宾客刷新
			HotelFrame.lbA.setText (HotelFrame.clue + 
			"刷新在店宾客信息      　　　　　　　　　　　　　　　　　 　");
		}else if(o==bt41) {
			//离店宾客查询
			HotelFrame.lbA.setText (HotelFrame.clue + 
			"查询离店宾客信息      　　　　　　　　　　　　　　　　　 　");
		}else if(o==bt42) {
			//离店宾客刷新
			HotelFrame.lbA.setText (HotelFrame.clue + 
			"刷新离店宾客信息      　　　　　　　　　　　　　　　　　 　");
		}
	}

	public void mouseExited (MouseEvent me) {
		HotelFrame.lbA.setText (HotelFrame.clue + "请选择功能项 ...   　　　　　　　　　　　　　　　　　　　　");
	}
}