using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; namespace DAL { public class DAL_Hotel { //操作员登录 public static DataTable OPlogin(string name, string pwd) { string sql = "select * from OPinfo where Oname='"+name+"' and pwd='"+pwd+"'"; return DBHelper.Query(sql); } //用户登录 public static DataTable UserLogin(string name, string pwd) { string sql = "select * from UserInfo where Uname='"+name+"' and upwd='"+pwd+"'"; return DBHelper.Query(sql); } //入住/预定信息查询 public static DataTable LiveMark(string table) { string idd=""; if (table == "record") { idd = "Reid"; } else { idd = "yid"; } string sql = "select * from " + table + " ,guestinfo,Roominfo where " + table + ".gid=guestinfo.gid and roominfo.roomid =" + table + ".roomid order by "+idd+" desc"; return DBHelper.Query(sql); } //入住/预定信息查询（分页） public static DataTable LiveMark(string table,int ye) { string idd = ""; if (table == "record") { idd = "Reid"; } else { idd = "yid"; } string sql = "select top 10 * from " + table + " ,guestinfo,Roominfo where " + table + ".gid=guestinfo.gid and roominfo.roomid =" + table + ".roomid and "+idd+" not in (select top " + (ye - 1) * 10 + " "+idd+" from " + table + " order by "+idd+" desc) order by " + idd + " desc"; return DBHelper.Query(sql); } //删除入住信息表 public static int DelRecord(int idd) { string sql = "delete from record where reid="+idd; return DBHelper.Modify(sql); } //查询所有顾客信息 public static DataTable ChaCus() { string sql = "select * from GuestInfo,GuestType where GuestType.GTid=GuestInfo.GTid"; return DBHelper.Query(sql); } //图标方式展示房间信息 public static DataTable imgtable(){ string sql = "select * from RoomInfo,RoomState,FloorInfo,RoomType where RoomState.Stateid=Roominfo.Rsid and RoomInfo.FId=FloorInfo.fid and RoomInfo.RTid=RoomType.Rtid"; return DBHelper.Query(sql); } //只查看单独类型的房间 public static DataTable image_TableType(int type) { string sql = "select top 36 * from RoomInfo,RoomState,FloorInfo,RoomType where RoomState.Stateid=Roominfo.Rsid and RoomInfo.FId=FloorInfo.fid and RoomInfo.RTid=RoomType.Rtid and roominfo.RTid=" + type; return DBHelper.Query(sql); } //图标方式展示房间信息（分页） public static DataTable imgtable(int ye) { string sql = "select top 36 * from RoomInfo,RoomState,FloorInfo,RoomType where RoomState.Stateid=Roominfo.Rsid and RoomInfo.FId=FloorInfo.fid and RoomInfo.RTid=RoomType.Rtid and roomid not in(select top " + (ye - 1) * 36 + " roomid from roominfo) order by roomid asc"; return DBHelper.Query(sql); } //根据顾客编号查询信息 public static DataTable Cha_Gid(string gid,string table) { string sql = "select * from " + table + " ,guestinfo,Roominfo where " + table + ".gid=guestinfo.gid and roominfo.roomid =" + table + ".roomid and " + table + ".gid like'%" + gid + "%'"; return DBHelper.Query(sql); } //根据房间编号查询信息 public static DataTable Cha_Roomid(string roomid,string table) { string sql = "select * from " + table + " ,guestinfo,Roominfo where " + table + ".gid=guestinfo.gid and roominfo.roomid =" + table + ".roomid and number like'%" + roomid + "%'"; return DBHelper.Query(sql); } //根据顾客姓名查询信息 public static DataTable Cha_Gname(string gname,string table) { string sql = "select * from " + table + " ,guestinfo,Roominfo where " + table + ".gid=guestinfo.gid and roominfo.roomid =" + table + ".roomid and gname like '%" + gname + "%'"; return DBHelper.Query(sql); } //根据身份证号查询信息 public static DataTable Cha_Idcard(string idcard,string table) { string sql = "select * from " + table + " ,guestinfo,Roominfo where " + table + ".gid=guestinfo.gid and roominfo.roomid =" + table + ".roomid and pid like'%" + idcard + "%'"; return DBHelper.Query(sql); } //根据手机号查询信息 public static DataTable Cha_Tel(string tel,string table) { string sql = "select * from "+table+" ,guestinfo,Roominfo where "+table+".gid=guestinfo.gid and roominfo.roomid ="+table+".roomid and mobile like '%" + tel + "%'"; return DBHelper.Query(sql); } //根据入住时间查询信息 public static DataTable Cha_LiveTime(string livetime,string table) { string sql = "select * from "+table+" ,guestinfo,Roominfo where "+table+".gid=guestinfo.gid and roominfo.roomid ="+table+".roomid and Convert(varchar,intime,120) like '%"+ livetime + "%'"; return DBHelper.Query(sql); } //根据单独id查询房间状态信息 public static DataTable Cha_One(int idd) { string sql = "select * from RoomInfo,RoomState,FloorInfo,RoomType where RoomState.Stateid=Roominfo.Rsid and RoomInfo.FId=FloorInfo.fid and RoomInfo.RTid=RoomType.Rtid and roomid=" + idd; return DBHelper.Query(sql); } //绑定房间类型 public static DataTable bind_roomtype() { string sql = "select * from RoomType"; return DBHelper.Query(sql); } //根据房间类型绑定未入住的房间编号 public static DataTable bind_roominfo(int typeid) { string sql = "select * from roominfo where rtid="+typeid+" and rsid=3"; return DBHelper.Query(sql); } //根据顾客编号查询该顾客的个人信息 public static DataTable Cha_Guestinfo(int idd) { string sql = "select * from GuestInfo,GuestType where GuestType.GTid=GuestInfo.GTid and gid =" + idd; return DBHelper.Query(sql); } //入住登记（入住信息表插入数据） public static int Add_Record(int gid,int roomid,DateTime intime,DateTime outtime,int day, double charge) { string sql = string.Format("insert into record values({0},{1},'{2}','{3}',default,{4},{5})",gid,roomid,intime,outtime,day,charge); return DBHelper.Modify(sql); } //修改房间状态 public static int Gai_roomstate(int roomid,int roomtype) { string sql = "update roominfo set rsid="+roomtype+" where roomid ="+roomid; return DBHelper.Modify(sql); } //根据记录编号查询入住信息 public static DataTable Cha_OneRecord(int roomid) { string sql = "select * from record,guestinfo,roominfo,guesttype,FloorInfo,roomtype where guestinfo.gid=record.gid and roominfo. roomid=record.roomid and record.roomid=" + roomid + " and guestinfo.gtid=guesttype.gtid and FloorInfo.fid=RoomInfo.fid and roomtype.rtid=roominfo.rtid and(rsid=2 or rsid = 1) and facttime='暂未离开'"; return DBHelper.Query(sql); } //计算入住的天数 public static DataTable Cha_DayNum(DateTime dt) { string sql = "select datediff(day,'"+dt+"',getdate()) 'daynum'"; return DBHelper.Query(sql); } //查询即将到期的客房 public static DataTable Cha_OutDay() { string sql = "select * ,(outtime-getdate())'day'from record,roominfo where record.roomid=roominfo.roomid and facttime = '暂未离开' order by day asc"; return DBHelper.Query(sql); } //退房时修改入住信息表 public static int Gai_Record(string facttime,int factnum,double charge,int reid){ string sql = string.Format("update record set facttime='{0}',daynum={1},charge={2} where reid ={3}",facttime,factnum,charge,reid); return DBHelper.Modify(sql); } //顾客消费金额更新 public static int Gai_GuestChargeSum(int gid,double charge) { string sql = "update Guestinfo set chargesum=chargesum+"+charge+" where gid="+gid; return DBHelper.Modify(sql); } //更新交易额统计表 public static int Gai_TradeCount(double money) { string sql = "insert into ChargeCount values(getdate(),"+money+")"; return DBHelper.Modify(sql); } //查询销售额 public static DataTable Cha_Charge(int day) { string sql = "select sum(charge) from chargecount where (select datediff(day,date,getdate()))<="+day; return DBHelper.Query(sql); } //用户房间续费该表 public static int Gai_AddDay(DateTime intime, DateTime outtime, int addday, int charge, int roomid) { string sql =string.Format("update record set intime='{0}',outtime='{1}',daynum=daynum+{2},charge=charge+{3} where roomid={4} and facttime='暂未离开'",intime,outtime,addday,charge,roomid); return DBHelper.Modify(sql); } //换房原因绑定 public static DataTable Cha_TranHome() { string sql = "select * from Trans"; return DBHelper.Query(sql); } //换房维修费用查询 public static DataTable Cha_Transmoney(int idd) { string sql = "select * from Trans where transid="+idd; return DBHelper.Query(sql); } //绑定顾客等级 public static DataTable bind_GuestTname() { string sql = "select * from Guesttype"; return DBHelper.Query(sql); } //新增顾客信息 public static int Add_GuestInfo(string name,int type,string sex,string mobile, int chargesum,string pid) { string sql = string.Format("insert into Guestinfo values('{0}',{1},'{2}','{3}',{4},'{5}')",name,type,sex, mobile,chargesum,pid); return DBHelper.Modify(sql); } //修改管理员密码 public static int Gai_OPPwd(string opname, string pwd) { string sql = "update OPInfo set pwd='"+pwd+"' where oname='"+opname+"'"; return DBHelper.Modify(sql); } //根据顾客编号查询顾客信息 public static DataTable Cha_GuestInfoByGid(int idd) { string sql = "select * from GuestInfo,GuestType where GuestType.GTid=GuestInfo.GTid and gid like '%" + idd + "%'"; return DBHelper.Query(sql); } //根据顾客编号查询顾客信息(分页) public static DataTable Cha_GuestInfoByGid(int idd,int ye) { string sql = "select top 6 * from GuestInfo,GuestType where GuestType.GTid=GuestInfo.GTid and gid like '%" + idd + "%'and gid not in (select top " + (ye - 1) * 6 + " gid from guestinfo where gid like '%" + idd + "%' )"; return DBHelper.Query(sql); } //根据顾客身份证号查询顾客信息 public static DataTable Cha_GuestInfoByPid(string pid) { string sql = "select * from GuestInfo,GuestType where GuestType.GTid=GuestInfo.GTid and pid like '%" + pid + "%'"; return DBHelper.Query(sql); } //根据顾客身份证号查询顾客信息(分页) public static DataTable Cha_GuestInfoByPid(string pid,int ye) { string sql = "select top 6 * from GuestInfo,GuestType where GuestType.GTid=GuestInfo.GTid and pid like '%" + pid + "%'and gid not in (select top " + (ye - 1) * 6 + " gid from guestinfo where pid like '%" + pid + "%' )"; return DBHelper.Query(sql); } //根据顾客手机号查询顾客信息 public static DataTable Cha_GuestInfoByMobile(string mobile) { string sql = "select * from GuestInfo,GuestType where GuestType.GTid=GuestInfo.GTid and mobile like '%" + mobile + "%'"; return DBHelper.Query(sql); } //根据顾客手机号查询顾客信息（分页） public static DataTable Cha_GuestInfoByMobile(string mobile,int ye) { string sql = "select top 6 * from GuestInfo,GuestType where GuestType.GTid=GuestInfo.GTid and mobile like '%" + mobile + "%'and gid not in (select top " + (ye - 1) * 6 + " gid from guestinfo where Mobile like '%" + mobile + "%' )"; return DBHelper.Query(sql); } //根据顾客姓名查询顾客信息 public static DataTable Cha_GuestInfoByGname(string gname) { string sql = "select * from GuestInfo,GuestType where GuestType.GTid=GuestInfo.GTid and Gname like '%" + gname + "%'"; return DBHelper.Query(sql); } //根据顾客姓名查询顾客信息(分页) public static DataTable Cha_GuestInfoByGname(string gname,int ye) { string sql = "select top 6 * from GuestInfo,GuestType where GuestType.GTid=GuestInfo.GTid and Gname like '%" + gname + "%'and gid not in (select top " + (ye - 1) * 6 + " gid from guestinfo where Gname like '%" + gname + "%' )"; return DBHelper.Query(sql); } //查出指定年份的销售额 public static DataTable Cha_YearChargeCount(int year) { string sql = "select month(date),sum(charge) from chargecount where year(date)='"+year+"' group by month(date)"; return DBHelper.Query(sql); } //查询指定月份的销售额 public static DataTable Cha_MonthChargeCount(int month,int year) { string sql = "select month(date),sum(charge) from chargecount where year(date)='"+year+"' and month(date)='"+month+"' group by month(date)"; return DBHelper.Query(sql); } //DropDwonList绑定年份 public static DataTable Bind_DropDwonListBindYear() { string sql = "select year(date) 'year' from chargecount group by year(date) order by year(date) desc"; return DBHelper.Query(sql); } //查询最近三年的收益总额 public static DataTable Cha_ThreeChargeCount(int year) { string sql = "select year(date) 'years',sum(charge)from chargecount group by year(date) having year(date) between " + (year - 2) + " and " +year; return DBHelper.Query(sql); } //客户信息表分页 public static DataTable Cha_CusFenYe(int ye) { string sql = "select top 6 * from GuestInfo,GuestType where GuestType.GTid=GuestInfo.GTid and Gid not in (select top " + (ye - 1) * 6 + " gid from GuestInfo) order by gid desc"; return DBHelper.Query(sql); } //查看楼层信息 public static DataTable Bind_LouCeng() { string sql = "select * from FloorInfo"; return DBHelper.Query(sql); } //新增房间 public static int Add_Room(string name,int fid,string mark,int roomid) { string sql = string.Format("update roominfo set number='{0}',fid={1},rsid=3,mark='{2}' where roomid={3}",name,fid,mark,roomid); return DBHelper.Modify(sql); } //入住预定（预定信息表插入数据） public static int Add_Reserve(int gid, int roomid, DateTime intime, DateTime outtime, int day, double charge,double actcharge) { string sql = string.Format("insert into Reserve values({0},{1},'{2}','{3}',default,{4},{5},{6})", gid, roomid, intime, outtime, day, charge,actcharge); return DBHelper.Modify(sql); } //根据房间编号查询预约信息 public static DataTable Cha_OneReserve(int roomid) { string sql = "select * from reserve,guestinfo,roominfo,guesttype,FloorInfo,roomtype where guestinfo.gid=reserve.gid and roominfo. roomid=reserve.roomid and reserve.roomid= "+roomid+" and guestinfo.gtid=guesttype.gtid and FloorInfo.fid=RoomInfo.fid and roomtype.rtid=roominfo.rtid and(rsid=2 or rsid = 1) and facttime='暂未入住'"; return DBHelper.Query(sql); } //取消房间预约 public static int Qu_Reserve(int roomid,string state) { string sql = "update reserve set facttime='"+state+"' where roomid="+roomid+" and facttime='暂未入住'"; return DBHelper.Modify(sql); } //删除预约信息表 public static int DelReserve(int idd) { string sql = "delete from reserve where yid=" + idd; return DBHelper.Modify(sql); } //修改顾客信息 public static int ChangeGuestInfo(string name, int GTid, string sex, string mobile, int chargesum, string pid, int gid) { string sql = string.Format("update GuestInfo set gname='{0}',Gtid={1},Sex='{2}',mobile='{3}',Chargesum='{4}',pid='{5}' where gid={6}",name,GTid,sex,mobile,chargesum,pid,gid); return DBHelper.Modify(sql); } //删除顾客 public static int Del_GuestInfo(int gid) { string sql = "delete from Guestinfo where gid="+gid; return DBHelper.Modify(sql); } //顾客升级 public static int UpdateGuest(int gid) { string sql = "update Guestinfo set Gtid=Gtid+1 where gid="+gid; return DBHelper.Modify(sql); } } }