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

手写版

if exists(select * from sysobjects where name ='usp_Seachroom')
drop procedure usp_Seachroom
go
create proc usp_seachroom
@roomstate int=-1,
@roomtype  int=-1,
@roomnum  nvarchar(50)=null,
@bedcount int=-1
as
declare @sql nvarchar(500)=
    'select RoomTypeId,RoomStateId,Bedcount,  RoomNum,roomstate.statename , roomtype.typename, Description from room
 inner join roomstate on room.roomstateid=roomstate.stateid
 inner join roomtype on room.roomtypeid=roomtype.typeid'
 if(@roomstate<>-1)
 set @sql+=' and roomstate.stateid=@rstate'
 if(@roomtype<>-1)
 set @sql+=' and roomtype.typeid=@rtype'
 if(@roomnum is not null)
 set @sql+=' and room.roomnum=@rnum'
 if(@bedcount<>-1)
 set @sql+=' and room.bedcount=@bcount'
 
 exec sp_executesql @sql,N'@rstate int,@rtype int,@rnum nvarchar(50),@bcount int',@rstate=@roomstate,@rtype=@roomtype,@rnum
 =@roomtype,@bcount=@bedcount
go

exec usp_seachroom @bedcount=2

if exists(select * from sysobjects where name ='usp_LeaveRoom')
drop procedure usp_LeaveRoom
go
--1 计算金额
    --普通房间
    --时钟房间
--2 天数
    --如果退房日期和开放日期不是同一天，算到中午12点
    --如果是同一天开放和退房，算一天
--3修改房间状态
--4在客户端显示详细退房信息
create procedure usp_Leaveroom
@gusetid int,@totalmoney int out
as
declare @price money 
declare @roomtype nvarchar(50)
declare @roomnum nvarchar(50)
declare @intime datetime
--declare @totalmoney money
select @roomnum=guest.RoomNum,@price=Price,@roomtype=TypeName,@intime=InTime from guest 
inner join Room on 
guest.RoomNum=room.RoomNum 
inner join RoomType on
RoomType.TypeId=room.RoomTypeId 
where guest.GuestId=@gusetid

declare @day int
--算时间
if @roomtype='时钟房'
    begin
       set @day=DATEDIFF(hh,@intime,GETDATE())+ceiling((DATEPART(MI,GETDATE())*1.0/60))
       if(@day<=3)
       begin
        set @totalmoney=@price
       end
       else
       begin
        set @totalmoney=@price+(@day-3)*30 
       end
    end
else
    begin
        set @day= DATEDIFF(DD,@intime,GETDATE())
            if (@day=0 or (DATEPART(HH,GETDATE())>12))
            begin
            set @day+=1
            end
        set @totalmoney=@day*@price
    end
declare @error int
begin transaction
update Room set RoomStateId=(select StateId from RoomState where StateName='待租') where RoomNum=
@roomnum
set @error+=@@ERROR
update guest set OutTime =GETDATE(),TotalMoney=@totalmoney where GuestId=@gusetid
set @error+=@@ERROR
if @error<>0
begin
rollback transaction
return -1
end
else
begin
commit transaction
return 1
end
go

declare @totalmoney int
exec usp_Leaveroom 6,@totalmoney out
print @totalmoney

 

if exists(select * from sysobjects where name='usp_guestinsert')
drop proc usp_guestinsert
go
create proc usp_guestinsert
 @Name nvarchar(50),
           @Gender nchar(1),
           @CardId char(18),
           @Phone char(11)=null,
           @RoomNum nvarchar(50),
          -- @InTime datetime,
           @Deposit money,
           @OutTime datetime=null,
           @TotalMoney money=null

as
declare @error int=0
begin transaction

insert into guest values(@Name,@Gender,@CardId,@Phone,@RoomNum,default,@Deposit,@OutTime,@TotalMoney)
set @error+=@@ERROR
update Room set RoomStateId=(select StateName from roomstate where StateName='已租') where RoomNum=@RoomNum
set @error+=@@ERROR
if(@error<>0)
begin 
rollback transaction
return -1
end
else
begin
commit transaction
return 1
end
go

带注释版

查询客房存储过程

if exists(select * from sysobjects where name='usp_SearchRoom')
 drop proc usp_SearchRoom
go 
create proc usp_SearchRoom
 @roomnum nvarchar(50)=null,--客房号
 @stateId int=0,--状态ID
 @typeName nvarchar(50)=null,--类型名称
 @bednum int=-1 --床位数   
as
 --初始的查询命令
 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'
 if(@roomnum is not null)
   set @sql=@sql+' and room.roomnum=@rnum'
 if(@stateId<>0) 
  set @sql=@sql+' and StateId=@sId' 
 if(@typeName is not null) 
  set @sql=@sql+' and TypeName=@tName' 
 if(@bednum<>-1) 
  set @sql=@sql+' and BedNum=@bnum' 
  --在sql中执行sql命令
  --exec sp_executesql N'select * from student where classid=@id',N'@id int',@id=7
  --如果 是将参数分开创建，那么在调用的时候必须赋值所有参数值
  --exec sp_executesql @sql,N'@rnum nvarchar(50)',N'@sId int',@rnum=@roomnum,@sId=@stateId
  --如果将所有参数在一起创建，那么在调用的时候可以任意指定其中的参数实参值    相当于in
    exec sp_executesql @sql,N'@rnum nvarchar(50),@sId int,@tName nvarchar(50),@bnum int',@rnum=@roomnum,@sId=@stateId,@tName=@typeName,@bnum=@bednum--将存储过程接收的参数赋值给sql命令中的参数变量
go 

exec usp_SearchRoom @typeName='单人间'

--1.传入房间号
--2.客户ID：修改时使用

----计算金额：
--1.不同房间类型的价格不一样
--    1.钟点房：三个小时以内，原价，超出部门每一小时30块
--    2.其它房间按指定价格计算

--2.天数
--    1.如果退房日期和开房日期不是同一天，算到中午12点
--    2.如果是同一天开房日期和退房，那么就算做一天
    
--3.退房后修改房间的状态为  空闲
--4.在客户端显示详细退房信息

if exists(select * from sysobjects where name='usp_LeaveRoom')
 drop proc usp_LeaveRoom
go
create proc usp_LeaveRoom
 @totalMoney money output,--总金额
 @GuestId int, --客户编号
 @RoomNum nvarchar(50) --房间号 
as
declare @price money --房间价格
declare @inTime datetime --入住日期
declare @roomtype nvarchar(50) --房间类型
--declare @totalMoney money --总金额
select @price=price,@inTime=intime ,@roomtype=TypeName
    from guest 
    inner join Room on guest.RoomNum=Room.RoomNum
     inner join RoomType on Room.RoomTypeId=RoomType.TypeId 
     where guest.GuestId=@GuestId
declare @days int --入住天数或者入住小时数
--判断房间的类型，因为不同类型计算价格的单位(天，小时)不一样
if (@roomtype='钟点房') --8:00   11:59
    begin
        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)
        if(@days<=3)--如果没有超时，不需要额外计算金额
           set @totalMoney=@price;
        else
            set  @totalMoney=@price+(@days-3)*30  --如果超过三个小时，多出的每一小时按30块计算
    end
else
    begin
        set @days=(select DATEDIFF(dd,intime,getdate()) from guest where GuestId=@GuestId)--获取天数
        --如果  是当天开房和退房，那么应该算为一天，如果 退房时间在下午12点之后，那么应该增加一天
            if(@days=0 or (select DATEPART(hh,getdate()) from guest where GuestId=@GuestId)>12)
                set @days+=1    
       set @totalMoney=@days*@price
    end
declare @error int=0 --记录操作中可能出现的错误号，做为事务的判断标准
begin transaction
   --记录当前客户退房详细信息
    update guest set OutTime=GETDATE(),TotalMoney=@totalMoney where GuestId=@GuestId
    set @error+=@@ERROR
    --修改退房之后的房间状态
    update Room set RoomStateId=(select StateId from RoomState where StateName='空闲') where RoomNum=@RoomNum
    set @error+=@@ERROR
if @error<>0 --说明出错
    begin
        rollback transaction
        return -1
    end
else
    begin
        commit transaction
        return 1
    end
go 

exec usp_LeaveRoom 4 ,'F2009'

if exists(select * from sysobjects where name='usp_insertGuest')
 drop proc usp_insertGuest
go
create proc usp_insertGuest
@name nvarchar(50),
@Gender nchar(1),
@CardId char(18),
@Phone char(11)=null,
@RoomNum nvarchar(50),
@Deposit money,
@OutTime datetime=null,
@TotalMoney money=null
as
declare @error int =0--记录错误号
begin transaction
insert into guest values(@name,@Gender,@CardId,@Phone,@RoomNum,default,@Deposit,@OutTime,@TotalMoney)
 set @error=@error+@@ERROR
--修改当前房间的状态为  已入住
update Room set  RoomStateId=(select StateId from RoomState where StateName='已入住') where RoomNum=@RoomNum
 set @error=@error+@@ERROR
if(@error<>0)
 begin
    rollback  transaction
    return -1
 end  
else
  begin
   commit transaction
   return 1 
  end  
go