查询客房信息存储过程和退房存储过程和添加人员信息存储过程

最新推荐文章于 2020-05-15 10:52:30 发布

weixin_30911451

最新推荐文章于 2020-05-15 10:52:30 发布

阅读量262

点赞数 1

文章标签：数据库

原文链接：http://www.cnblogs.com/zhanying/p/4097153.html

版权

手写版

 1 if exists(select * from sysobjects where name ='usp_Seachroom')
 2 drop procedure usp_Seachroom
 3 go
 4 create proc usp_seachroom
 5 @roomstate int=-1,
 6 @roomtype  int=-1,
 7 @roomnum  nvarchar(50)=null,
 8 @bedcount int=-1
 9 as
10 declare @sql nvarchar(500)=
11     'select RoomTypeId,RoomStateId,Bedcount,  RoomNum,roomstate.statename , roomtype.typename, Description from room
12  inner join roomstate on room.roomstateid=roomstate.stateid
13  inner join roomtype on room.roomtypeid=roomtype.typeid'
14  if(@roomstate<>-1)
15  set @sql+=' and roomstate.stateid=@rstate'
16  if(@roomtype<>-1)
17  set @sql+=' and roomtype.typeid=@rtype'
18  if(@roomnum is not null)
19  set @sql+=' and room.roomnum=@rnum'
20  if(@bedcount<>-1)
21  set @sql+=' and room.bedcount=@bcount'
22  
23  exec sp_executesql @sql,N'@rstate int,@rtype int,@rnum nvarchar(50),@bcount int',@rstate=@roomstate,@rtype=@roomtype,@rnum
24  =@roomtype,@bcount=@bedcount
25 go
26 
27 exec usp_seachroom @bedcount=2

查询客房

 1 if exists(select * from sysobjects where name ='usp_LeaveRoom')
 2 drop procedure usp_LeaveRoom
 3 go
 4 --1 计算金额
 5     --普通房间
 6     --时钟房间
 7 --2 天数
 8     --如果退房日期和开放日期不是同一天，算到中午12点
 9     --如果是同一天开放和退房，算一天
10 --3修改房间状态
11 --4在客户端显示详细退房信息
12 create procedure usp_Leaveroom
13 @gusetid int,@totalmoney int out
14 as
15 declare @price money 
16 declare @roomtype nvarchar(50)
17 declare @roomnum nvarchar(50)
18 declare @intime datetime
19 --declare @totalmoney money
20 select @roomnum=guest.RoomNum,@price=Price,@roomtype=TypeName,@intime=InTime from guest 
21 inner join Room on 
22 guest.RoomNum=room.RoomNum 
23 inner join RoomType on
24 RoomType.TypeId=room.RoomTypeId 
25 where guest.GuestId=@gusetid
26 
27 declare @day int
28 --算时间
29 if @roomtype='时钟房'
30     begin
31        set @day=DATEDIFF(hh,@intime,GETDATE())+ceiling((DATEPART(MI,GETDATE())*1.0/60))
32        if(@day<=3)
33        begin
34         set @totalmoney=@price
35        end
36        else
37        begin
38         set @totalmoney=@price+(@day-3)*30 
39        end
40     end
41 else
42     begin
43         set @day= DATEDIFF(DD,@intime,GETDATE())
44             if (@day=0 or (DATEPART(HH,GETDATE())>12))
45             begin
46             set @day+=1
47             end
48         set @totalmoney=@day*@price
49     end
50 declare @error int
51 begin transaction
52 update Room set RoomStateId=(select StateId from RoomState where StateName='待租') where RoomNum=
53 @roomnum
54 set @error+=@@ERROR
55 update guest set OutTime =GETDATE(),TotalMoney=@totalmoney where GuestId=@gusetid
56 set @error+=@@ERROR
57 if @error<>0
58 begin
59 rollback transaction
60 return -1
61 end
62 else
63 begin
64 commit transaction
65 return 1
66 end
67 go
68 
69 declare @totalmoney int
70 exec usp_Leaveroom 6,@totalmoney out
71 print @totalmoney

退房

 1 if exists(select * from sysobjects where name='usp_guestinsert')
 2 drop proc usp_guestinsert
 3 go
 4 create proc usp_guestinsert
 5  @Name nvarchar(50),
 6            @Gender nchar(1),
 7            @CardId char(18),
 8            @Phone char(11)=null,
 9            @RoomNum nvarchar(50),
10           -- @InTime datetime,
11            @Deposit money,
12            @OutTime datetime=null,
13            @TotalMoney money=null
14 
15 as
16 declare @error int=0
17 begin transaction
18 
19 insert into guest values(@Name,@Gender,@CardId,@Phone,@RoomNum,default,@Deposit,@OutTime,@TotalMoney)
20 set @error+=@@ERROR
21 update Room set RoomStateId=(select StateName from roomstate where StateName='已租') where RoomNum=@RoomNum
22 set @error+=@@ERROR
23 if(@error<>0)
24 begin 
25 rollback transaction
26 return -1
27 end
28 else
29 begin
30 commit transaction
31 return 1
32 end
33 go

插入客房

带注释版

查询客房存储过程

 1 if exists(select * from sysobjects where name='usp_SearchRoom')
 2  drop proc usp_SearchRoom
 3 go 
 4 create proc usp_SearchRoom
 5  @roomnum nvarchar(50)=null,--客房号
 6  @stateId int=0,--状态ID
 7  @typeName nvarchar(50)=null,--类型名称
 8  @bednum int=-1 --床位数   
 9 as
10  --初始的查询命令
11  declare @sql Nvarchar(500)='SELECT     dbo.Room.RoomNum, dbo.Room.RoomStateId, dbo.Room.RoomTypeId,dbo.Room.BedNum, dbo.Room.Description, dbo.RoomState.StateName,  dbo.RoomType.TypeName, dbo.RoomType.Price, dbo.RoomType.Description AS RoomDes  FROM    dbo.Room INNER JOIN dbo.RoomState ON dbo.Room.RoomStateId = dbo.RoomState.StateId INNER JOIN   dbo.RoomType ON dbo.Room.RoomTypeId = dbo.RoomType.TypeId'
12  if(@roomnum is not null)
13    set @sql=@sql+' and room.roomnum=@rnum'
14  if(@stateId<>0) 
15   set @sql=@sql+' and StateId=@sId' 
16  if(@typeName is not null) 
17   set @sql=@sql+' and TypeName=@tName' 
18  if(@bednum<>-1) 
19   set @sql=@sql+' and BedNum=@bnum' 
20   --在sql中执行sql命令
21   --exec sp_executesql N'select * from student where classid=@id',N'@id int',@id=7
22   --如果 是将参数分开创建，那么在调用的时候必须赋值所有参数值
23   --exec sp_executesql @sql,N'@rnum nvarchar(50)',N'@sId int',@rnum=@roomnum,@sId=@stateId
24   --如果将所有参数在一起创建，那么在调用的时候可以任意指定其中的参数实参值    相当于in
25     exec sp_executesql @sql,N'@rnum nvarchar(50),@sId int,@tName nvarchar(50),@bnum int',@rnum=@roomnum,@sId=@stateId,@tName=@typeName,@bnum=@bednum--将存储过程接收的参数赋值给sql命令中的参数变量
26 go 
27 
28 exec usp_SearchRoom @typeName='单人间'

 1 --1.传入房间号
 2 --2.客户ID：修改时使用
 3 
 4 ----计算金额：
 5 --1.不同房间类型的价格不一样
 6 --    1.钟点房：三个小时以内，原价，超出部门每一小时30块
 7 --    2.其它房间按指定价格计算
 8 
 9 --2.天数
10 --    1.如果退房日期和开房日期不是同一天，算到中午12点
11 --    2.如果是同一天开房日期和退房，那么就算做一天
12     
13 --3.退房后修改房间的状态为  空闲
14 --4.在客户端显示详细退房信息
15 
16 if exists(select * from sysobjects where name='usp_LeaveRoom')
17  drop proc usp_LeaveRoom
18 go
19 create proc usp_LeaveRoom
20  @totalMoney money output,--总金额
21  @GuestId int, --客户编号
22  @RoomNum nvarchar(50) --房间号 
23 as
24 declare @price money --房间价格
25 declare @inTime datetime --入住日期
26 declare @roomtype nvarchar(50) --房间类型
27 --declare @totalMoney money --总金额
28 select @price=price,@inTime=intime ,@roomtype=TypeName
29     from guest 
30     inner join Room on guest.RoomNum=Room.RoomNum
31      inner join RoomType on Room.RoomTypeId=RoomType.TypeId 
32      where guest.GuestId=@GuestId
33 declare @days int --入住天数或者入住小时数
34 --判断房间的类型，因为不同类型计算价格的单位(天，小时)不一样
35 if (@roomtype='钟点房') --8:00   11:59
36     begin
37         set @days=(select DATEDIFF(hh,intime,getdate()) from guest where GuestId=@GuestId)+ceiling((select datepart(MI,getdate()) from guest where GuestId=@GuestId)*1.0/60)
38         if(@days<=3)--如果没有超时，不需要额外计算金额
39            set @totalMoney=@price;
40         else
41             set  @totalMoney=@price+(@days-3)*30  --如果超过三个小时，多出的每一小时按30块计算
42     end
43 else
44     begin
45         set @days=(select DATEDIFF(dd,intime,getdate()) from guest where GuestId=@GuestId)--获取天数
46         --如果  是当天开房和退房，那么应该算为一天，如果 退房时间在下午12点之后，那么应该增加一天
47             if(@days=0 or (select DATEPART(hh,getdate()) from guest where GuestId=@GuestId)>12)
48                 set @days+=1    
49        set @totalMoney=@days*@price
50     end
51 declare @error int=0 --记录操作中可能出现的错误号，做为事务的判断标准
52 begin transaction
53    --记录当前客户退房详细信息
54     update guest set OutTime=GETDATE(),TotalMoney=@totalMoney where GuestId=@GuestId
55     set @error+=@@ERROR
56     --修改退房之后的房间状态
57     update Room set RoomStateId=(select StateId from RoomState where StateName='空闲') where RoomNum=@RoomNum
58     set @error+=@@ERROR
59 if @error<>0 --说明出错
60     begin
61         rollback transaction
62         return -1
63     end
64 else
65     begin
66         commit transaction
67         return 1
68     end
69 go 
70 
71 exec usp_LeaveRoom 4 ,'F2009'

退房

 1 if exists(select * from sysobjects where name='usp_insertGuest')
 2  drop proc usp_insertGuest
 3 go
 4 create proc usp_insertGuest
 5 @name nvarchar(50),
 6 @Gender nchar(1),
 7 @CardId char(18),
 8 @Phone char(11)=null,
 9 @RoomNum nvarchar(50),
10 @Deposit money,
11 @OutTime datetime=null,
12 @TotalMoney money=null
13 as
14 declare @error int =0--记录错误号
15 begin transaction
16 insert into guest values(@name,@Gender,@CardId,@Phone,@RoomNum,default,@Deposit,@OutTime,@TotalMoney)
17  set @error=@error+@@ERROR
18 --修改当前房间的状态为  已入住
19 update Room set  RoomStateId=(select StateId from RoomState where StateName='已入住') where RoomNum=@RoomNum
20  set @error=@error+@@ERROR
21 if(@error<>0)
22  begin
23     rollback  transaction
24     return -1
25  end  
26 else
27   begin
28    commit transaction
29    return 1 
30   end  
31 go