第八章酒店管理系统

--创建Hotel数据库
use master
go
if exists (select * from sysdatabases where name='Hotel')
drop database Hotel
create database Hotel
on primary 
(
  name='Hotel_data',
  filename='D:\Project_S2\Hotel_data.mdf',
  size=5mb,
  maxsize=100mb,
  filegrowth=15%
)
log on
(
  name='Hotel_log',
  filename='D:\Project_S2\Hotel_log.ldf',
  size=5mb,
  maxsize=100mb,
  filegrowth=15%
)
go
--创建ROOM表（客房信息表）
use Hotel
go
if exists (select * from sysobjects where name='Room') 
drop table Room 
create table Room
(
RoomID int identity(1,1) primary key,
BedNum int not null,
GuestNum int not null,
Description nvarchar(32) not null,
RoomStateID int,
RoomTypeID int
)
go
--创建RoomType表（客房类型）
create table RoomType
(
TypeID int identity(1,1) primary key,
TypeName nvarchar(32) ,
TypePrice Decimal
)
go
--创建RoomState表（客房状态）
create table RoomState
(
RoomStateID int identity(1,1) primary key,
RoomStateName nvarchar(32)
)
go
--创建ResideState表（结账状态表）
create table ResideState
(
ResideID int identity(1,1) primary key,
ResideName nvarchar(32) not null
)
go
--创建GuestRecord表（客人信息表）
use Hotel
go
if exists (select * from sysobjects where name='GuestRecord') 
drop table GuestRecord 
create table GuestRecord
(
GuestID int identity(1,1) primary key,
GuestName nvarchar(32) not null,
IdentityID varchar(32) ,
RoomID int,
ResideId int,
ResideDate datetime,
LeaveDate datetime,
Deposit decimal,
TotalMoney decimal
)
go
--客房类型表约束
alter table RoomType
add constraint CK_TypePrice check (TypePrice>=0)
go
--客房信息表约束
alter table Room 
add constraint DF_BedNum default(2) for BedNum
go

alter table Room 
add constraint DF_GuestNum default ('0') for GuestNum
go

alter table Room
add constraint CK_GuestNum check (GuestNum>=0)
go

alter table Room
add constraint FK_RoomStateID foreign key (RoomStateID)references RoomState (RoomStateID)
go

alter table Room
add constraint FK_RoomTypeID foreign key (RoomTypeID)references RoomType (TypeID)
go
--客人信息表约束
alter table GuestRecord
add constraint CK_IdentityID check (IdentityID=18)
go

alter table GuestRecord
add constraint FK_RoomID foreign key (RoomID) references Room (RoomID)
go

alter table GuestRecord
add constraint FK_ResideID foreign key (ResideID) references ResideState (ResideId)
go

alter table GuestRecord
add constraint DF_ResideId default ('未结账') for ResideID
go

alter table GuestRecord
add constraint CK_LeaveDate check (LeaveDate>=ResideDate)
go
----------------------------------------------------------------------------------------------------------------------------
--添加数据
-------------------------------------客房类型表（RoomType）------------------------------------------------------------------
insert into RoomType(TypeName, TypePrice)
values ('标准间',CAST(180.00 as Decimal(18,2)))
insert into RoomType(TypeName, TypePrice)
values ('单人间',CAST(128.00 as Decimal(18,2)))
insert into RoomType(TypeName, TypePrice)
values ('三人间',CAST(208.00 as Decimal(18,2)))
insert into RoomType(TypeName, TypePrice)
values ('总统套房',CAST(998.00 as Decimal(18,2)))
insert into RoomType(TypeName, TypePrice)
values ('长包房',CAST(108.00 as Decimal(18,2)))
insert into RoomType(TypeName, TypePrice)
values ('豪华标准间',CAST(268.00 as Decimal(18,2)))
insert into RoomType(TypeName, TypePrice)
values ('单人套房',CAST(368.00 as Decimal(18,2)))
insert into RoomType(TypeName, TypePrice)
values ('双人套房',CAST(568.00 as Decimal(18,2)))
---------------------------------------客房状态表（RoomState）---------------------------------------------------------------
insert into RoomState(RoomStateName)
values ('已入住')
insert into RoomState(RoomStateName)
values ('空闲')
insert into RoomState(RoomStateName)
values ('维修') 
---------------------------------------结账状态表（ResideState）--------------------------------------------------------------
insert into ResideState (ResideName)
values ('已结账')
insert into ResideState (ResideName)
values ('未结账')
---------------------------------------客人信息表（GuestRecord）--------------------------------------------------------------
insert into GuestRecord (GuestName, IdentityID, RoomID, ResideId, ResideDate, LeaveDate, Deposit, TotalMoney)
values ('张三','211022188708197801','1001',1,'2009-09-12 12:30:08.000','2009-09-13 12:30:00.000',1000.00,180.00)
insert into GuestRecord (GuestName, IdentityID, RoomID, ResideId, ResideDate, LeaveDate, Deposit, TotalMoney)
values ('李四','211022199808196707','1002',1,'2009-09-10 12:30:08.000','2009-09-11 12:30:00.000',1000.00,208.00)
insert into GuestRecord (GuestName, IdentityID, RoomID, ResideId, ResideDate, LeaveDate, Deposit, TotalMoney)
values ('王五','211022188608197809','1003',2,'2009-09-12 12:30:08.000',null,3000.00,null)
insert into GuestRecord (GuestName, IdentityID, RoomID, ResideId, ResideDate, LeaveDate, Deposit, TotalMoney)
values ('张三三','211022188708197806','1004',2,'2009-09-12 12:30:08.000',null,800.00,null)
insert into GuestRecord (GuestName, IdentityID, RoomID, ResideId, ResideDate, LeaveDate, Deposit, TotalMoney)
values ('耿直','211022188708197808','1005',2,'2009-09-12 12:30:08.000',null,1500.00,null)
insert into GuestRecord (GuestName, IdentityID, RoomID, ResideId, ResideDate, LeaveDate, Deposit, TotalMoney)
values ('代静','211022188708197810','1006',2,'2009-09-12 12:30:08.000',null,5000.00,null)
insert into GuestRecord (GuestName, IdentityID, RoomID, ResideId, ResideDate, LeaveDate, Deposit, TotalMoney)
values ('石岩','211022188708197811','1007',2,'2009-09-12 12:30:08.000',null,1000.00,null)
------------------------------------------客房信息表（Room）--------------------------------------------------------------------
insert into Room(BedNum, GuestNum, Description, RoomStateID, RoomTypeID)
values ('1001',1,'标准间',1,1)
insert into Room(BedNum, GuestNum, Description, RoomStateID, RoomTypeID)
values ('1002',3,'三人间',1,3)
insert into Room(BedNum, GuestNum, Description, RoomStateID, RoomTypeID)
values ('1003',2,'豪华套房',2,6)
insert into Room(BedNum, GuestNum, Description, RoomStateID, RoomTypeID)
values ('1004',1,'长包房',2,5)
insert into Room(BedNum, GuestNum, Description, RoomStateID, RoomTypeID)
values ('1005',1,'单人套房',2,7)
insert into Room(BedNum, GuestNum, Description, RoomStateID, RoomTypeID)
values ('1006',2,'总统套房',3,4)
insert into Room(BedNum, GuestNum, Description, RoomStateID, RoomTypeID)
values ('1007',1,'标准间',2,1)
------------------------------------------------------------------------------------------------------------------------------------
--1.
create procedure usp_GetGuestNumByTypeName
@RoomType varchar(20),
@count int output
as
set @RoomType='标准间'
declare @num int
select @num=count(@RoomType) from Room where RoomID=1 and RoomStateID=1
print @num
GO
set nocount on
declare @count int
declare @RoomType varchar(20)
set @RoomType='标准间'
exec usp_GetGuestNumByTypeName @RoomType,@count output
print '入住酒店'+@RoomType+'的客人总人数是：'+cast(@count as varchar(10))
--检查判断错误
declare @err int 
set @err =@@ERROR
if @err<>0
   begin
   print '错误号：'+convert(varchar(5),@err)
   return            --退出批处理，后续语句不再执行
   end
go

--删除
use Hotel
go
if exists (select * from sysobjects where name='usp_GetGuestNumByTypeName')
drop procedure usp_GetGuestNumByTypeName
go
--2.
--想要RoomId有固定的数然后查询，如果查不到则等于-1然后查询所有，不然查询单行RoomId等于什么就查什么？？？？？
create procedure usp_GetGuestRoom
@Room int 
as
--declare @Room int
select RoomID,BedNum,RoomStateID,Description,TypeName,TypePrice from RoomType,Room 
 where RoomType.TypeID=Room.RoomTypeID and @Room=RoomID
 print @Room
go
declare @Room int
exec  usp_GetGuestRoom @Room
if @Room!=-1
  begin
  select RoomID,BedNum,RoomStateID,Description,TypeName,TypePrice from RoomType,Room 
  where RoomType.TypeID=Room.RoomTypeID and @Room=RoomID
  end
else
  begin
  select RoomID,BedNum,RoomStateID,Description,TypeName,TypePrice from RoomType,Room 
  where RoomType.TypeID=Room.RoomTypeID 
  end
--3.删除酒店客房类型编号是三人间的记录1条
create procedure usp_deleteRoomTypeById
@RoomTYPE varchar(20),
@Result int
as 
--select 语句
go
declare @RoomTYPE varchar(20)
declare @Result int
set @RoomTYPE='三人间'
exec @Result=usp_deleteRoomTypeById @RoomTYPE
if (@Result>0)
  print '删除酒店客房类型是'+@RoomTYPE+'的记录'+cast(@Result as varchar(10))+'条'
else
  print '删除酒店客房类型是'+@RoomTYPE+'的记录，失败'
--4.
--（1行受影响）
--（1行受影响）
--插入客户记录操作成功
--客户编号是11
create procedure usp_insertGuestRecord
@Result1 int,
@GuestId int output,
@identityID varchar(20),
@guestName varchar(32),
@roomID int,
@ResideDate datetime,
@deposit money
as 
----select 语句
go
declare @Result1 int
declare @GuestId int 
declare @identityID varchar(20)
declare @guestName varchar(32)
declare @roomID int
declare @ResideDate datetime
declare @deposit money
--set @GuestId=''
exec @Result1=usp_insertGuestRecord @GuestId output,@identityID ,@guestName,@roomID,@ResideDate,@deposit

print @Result1
if (@Result1=0)
begin
  print '插入客户记录操作成功'
  print '客人编号是'+cast(@GuestId as varchar)
end
else
  print '插入客户记录操作失败'