hoteldb.sql

酒店管理系统,您酒店物业管理的好帮手

insert guestRoom values('GR560',4,'北面朝山',80084,'停用','2008-2-8 7:00',default,'无')
insert guestRoom values('GR561',4,'窗户朝海',80032,'可供','2008-1-5 6:00',default,'无')
insert guestRoom values('GR562',4,'北面朝山',80021,'维修','2008-3-8 6:00',default,'无')
insert guestRoom values('GR563',4,'北面朝山',80078,'可供','2008-3-8 6:00',default,'无')
insert guestRoom values('GR564',4,'正南',80079,'可供','2008-3-8 6:00',default,'无')
insert guestRoom values('GR565',4,'正向北',80075,'维修','2008-3-8 6:00',default,'无')
insert guestRoom values('GR566',4,'正向北',80032,'维修','2008-3-8 6:00',default,'无')
insert guestRoom values('GR567',4,'正向北',80029,'维修','2008-1-1 6:00',default,'无')
insert guestRoom values('GR568',4,'北面朝山',80087,'可供','2008-1-8 6:00',default,'无')
insert guestRoom values('GR569',4,'窗户朝海',80054,'可供','2008-4-5 6:00',default,'无')
insert guestRoom values('GR570',4,'北面朝山',80084,'停用','2008-2-8 7:00',default,'无')
insert guestRoom values('GR571',4,'窗户朝海',80032,'可供','2008-1-5 6:00',default,'无')
insert guestRoom values('GR572',4,'北面朝山',80021,'维修','2008-3-8 6:00',default,'无')
insert guestRoom values('GR573',4,'北面朝山',80078,'可供','2008-3-8 6:00',default,'无')
insert guestRoom values('GR574',4,'正南',80079,'可供','2008-3-8 6:00',default,'无')
insert guestRoom values('GR575',5,'正南',80046,'可供','2008-3-8 6:00',default,'无')
insert guestRoom values('GR576',5,'面向大海',80034,'维修','2008-1-1 6:00',default,'无')
insert guestRoom values('GR577',5,'北面朝山',80076,'可供','2008-1-8 6:00',default,'无')
insert guestRoom values('GR578',5,'窗户朝海',80092,'可供','2008-4-5 6:00',default,'无')
insert guestRoom values('GR579',5,'北面朝山',80093,'停用','2008-2-8 7:00',default,'无')
insert guestRoom values('GR580',5,'窗户朝海',80094,'可供','2008-1-5 6:00',default,'无')
insert guestRoom values('GR581',5,'北面朝山',80076,'可供','2008-3-8 6:00',default,'无')
insert guestRoom values('GR582',5,'正向北',80075,'维修','2008-3-8 6:00',default,'无')
insert guestRoom values('GR583',5,'正向北',80032,'维修','2008-3-8 6:00',default,'无')
insert guestRoom values('GR584',5,'正向北',80029,'维修','2008-1-1 6:00',default,'无')
insert guestRoom values('GR585',5,'北面朝山',80087,'可供','2008-1-8 6:00',default,'无')
insert guestRoom values('GR586',5,'窗户朝海',80054,'可供','2008-4-5 6:00',default,'无')
insert guestRoom values('GR587',5,'北面朝山',80084,'停用','2008-2-8 7:00',default,'无')
insert guestRoom values('GR588',5,'窗户朝海',80032,'可供','2008-1-5 6:00',default,'无')
insert guestRoom values('GR589',5,'北面朝山',80021,'维修','2008-3-8 6:00',default,'无')
insert guestRoom values('GR590',5,'北面朝山',80078,'可供','2008-3-8 6:00',default,'无')
insert guestRoom values('GR591',5,'正南',80079,'可供','2008-3-8 6:00',default,'无')
insert guestRoom values('GR592',5,'正向北',80075,'维修','2008-3-8 6:00',default,'无')
insert guestRoom values('GR593',5,'正向北',80032,'维修','2008-3-8 6:00',default,'无')
insert guestRoom values('GR594',5,'正向北',80029,'维修','2008-1-1 6:00',default,'无')
insert guestRoom values('GR595',5,'北面朝山',80087,'可供','2008-1-8 6:00',default,'无')
insert guestRoom values('GR596',5,'窗户朝海',80054,'可供','2008-4-5 6:00',default,'无')
insert guestRoom values('GR597',5,'北面朝山',80084,'停用','2008-2-8 7:00',default,'无')
insert guestRoom values('GR598',5,'窗户朝海',80032,'可供','2008-1-5 6:00',default,'无')
insert guestRoom values('GR599',5,'北面朝山',80021,'维修','2008-3-8 6:00',default,'无')
insert guestRoom values('GR600',5,'北面朝山',80078,'可供','2008-3-8 6:00',default,'无')
insert guestRoom values('GR601',5,'正南',80079,'可供','2008-3-8 6:00',default,'无')
insert guestRoom values('GR602',6,'正南',80046,'可供','2008-3-8 6:00',default,'无')
insert guestRoom values('GR603',6,'面向大海',80034,'维修','2008-1-1 6:00',default,'无')
insert guestRoom values('GR604',6,'北面朝山',80076,'可供','2008-1-8 6:00',default,'无')
insert guestRoom values('GR605',6,'窗户朝海',80092,'可供','2008-4-5 6:00',default,'无')
insert guestRoom values('GR606',6,'北面朝山',80093,'停用','2008-2-8 7:00',default,'无')
insert guestRoom values('GR607',6,'窗户朝海',80094,'可供','2008-1-5 6:00',default,'无')
insert guestRoom values('GR608',6,'北面朝山',80076,'可供','2008-3-8 6:00',default,'无')
insert guestRoom values('GR609',6,'正向北',80075,'维修','2008-3-8 6:00',default,'无')
insert guestRoom values('GR610',6,'正向北',80032,'维修','2008-3-8 6:00',default,'无')
insert guestRoom values('GR611',6,'正向北',80029,'维修','2008-1-1 6:00',default,'无')
insert guestRoom values('GR612',6,'北面朝山',80087,'可供','2008-1-8 6:00',default,'无')
insert guestRoom values('GR613',6,'窗户朝海',80054,'可供','2008-4-5 6:00',default,'无')
insert guestRoom values('GR614',6,'北面朝山',80084,'停用','2008-2-8 7:00',default,'无')
insert guestRoom values('GR615',6,'窗户朝海',80032,'可供','2008-1-5 6:00',default,'无')
insert guestRoom values('GR616',6,'北面朝山',80021,'维修','2008-3-8 6:00',default,'无')
insert guestRoom values('GR617',6,'北面朝山',80078,'可供','2008-3-8 6:00',default,'无')
insert guestRoom values('GR618',6,'正南',80079,'可供','2008-3-8 6:00',default,'无')
insert guestRoom values('GR619',6,'正向北',80075,'维修','2008-3-8 6:00',default,'无')
insert guestRoom values('GR620',6,'正向北',80032,'维修','2008-3-8 6:00',default,'无')
insert guestRoom values('GR621',6,'正向北',80029,'维修','2008-1-1 6:00',default,'无')
insert guestRoom values('GR622',6,'北面朝山',80087,'可供','2008-1-8 6:00',default,'无')
insert guestRoom values('GR623',6,'窗户朝海',80054,'可供','2008-4-5 6:00',default,'无')
insert guestRoom values('GR624',6,'北面朝山',80084,'停用','2008-2-8 7:00',default,'无')
insert guestRoom values('GR625',6,'窗户朝海',80032,'可供','2008-1-5 6:00',default,'无')
insert guestRoom values('GR626',6,'北面朝山',80021,'维修','2008-3-8 6:00',default,'无')
insert guestRoom values('GR627',6,'北面朝山',80078,'可供','2008-3-8 6:00',default,'无')
insert guestRoom values('GR628',6,'正南',80079,'可供','2008-3-8 6:00',default,'无')


select * from EmployeeType

insert EmployeeType values('服务员')
insert EmployeeType values('部长')
insert EmployeeType values('主管')
insert EmployeeType values('经理')
insert EmployeeType values('总经理')



select * from Employee

insert employee values('EM9001','陈杰','男',21,'本科',null,1,'正常','无')
insert employee values('EM9011','杨琴','女',25,'硕士',null,2,'正常','无')
insert employee values('EM9012','张慧敏','女',21,'本科',null,1,'正常','无')
insert employee values('EM9013','李国家','男',21,'高中',null,1,'正常','无')

insert employee values('EM9014','成龙','男',22,'初中',null,1,'正常','无')
insert employee values('EM9015','房祖名','男',25,'初中',null,2,'正常','无')
insert employee values('EM9016','张山','男',22,'初中',null,1,'正常','无')
insert employee values('EM9017','李李四','男',41,'高中',null,1,'正常','无')
insert employee values('EM9008','黄三','男',24,'本科',null,1,'正常','无')
insert employee values('EM9019','李里','男',25,'高中',null,2,'正常','无')
insert employee values('EM9020','王二小','男',28,'高中',null,1,'正常','无')
insert employee values('EM9021','蔡远锋','男',31,'高中',null,1,'正常','无')
insert employee values('EM9022','欧林龙','男',21,'本科',null,1,'正常','无')
insert employee values('EM9023','黄名真','女',65,'硕士',null,2,'正常','无')
insert employee values('EM9024','欧一琼','女',41,'本科',null,1,'正常','无')
insert employee values('EM9025','王无','男',22,'高中',null,1,'正常','无')
insert employee values('EM9026','黄马','男',29,'本科',null,1,'正常','无')
insert employee values('EM9027','李勇','男',28,'硕士',null,2,'正常','无')
insert employee values('EM9028','刘恒','女',25,'高中',null,1,'正常','无')
insert employee values('EM9029','藿西','男',20,'高中',null,1,'正常','无')
insert employee values('EM9030','梁景光','男',24,'高中',null,1,'正常','无')
insert employee values('EM9031','邓中夏','女',25,'硕士',null,2,'正常','无')
insert employee values('EM9032','张江煞','女',23,'本科',null,1,'正常','无')
insert employee values('EM9033','李江煞','男',23,'高中',null,1,'正常','无')
insert employee values('EM9034','高尔基','男',25,'本科',null,1,'正常','无')
insert employee values('EM9035','楚云飞','男',25,'硕士',null,2,'正常','无')
insert employee values('EM9036','慕北北','男',27,'本科',null,1,'正常','无')
insert employee values('EM9037','陆小凤','男',27,'高中',null,1,'正常','无')
insert employee values('EM9038','王力红','男',28,'高中',null,1,'正常','无')
insert employee values('EM9039','小凡','女',29,'硕士',null,2,'正常','无')
insert employee values('EM9040','苏华','女',23,'本科',null,1,'正常','无')
insert employee values('EM9041','周枉','男',31,'高中',null,1,'正常','无')
insert employee values('EM9042','黄风','女',41,'本科',null,1,'正常','无')
insert employee values('EM9043','黄畅','女',55,'硕士',null,2,'正常','无')
insert employee values('EM9044','福容王','女',31,'本科',null,1,'正常','无')
insert employee values('EM9045','吴宇','男',61,'高中',null,1,'正常','无')
insert employee values('EM9046','向因','女',21,'本科',null,1,'正常','无')
insert employee values('EM9047','杨扬','女',25,'硕士',null,2,'正常','无')
insert employee values('EM9048','张慧','女',21,'高中',null,1,'正常','无')
insert employee values('EM9049','李家','男',21,'高中',null,1,'正常','无')
insert employee values('EM9050','陈杰几','男',21,'本科',null,1,'正常','无')
insert employee values('EM9051','和和','女',25,'硕士',null,2,'正常','无')
insert employee values('EM9052','张敏之','女',21,'本科',null,1,'正常','无')
insert employee values('EM9053','李红','女',21,'高中',null,1,'正常','无')


select * from userInfo

insert userInfo values('US1001','EM9001','111111','启用',default,default,default,default,default)

insert userInfo values('US1002','EM9011','222222','启用',default,1,1,1,1)

-----------------商品
select * from ItemsType

insert ItemsType values('食品类')
insert ItemsType values('服务类')
insert ItemsType values('消耗品')


select * from ItemsPrice

insert ItemsPrice values('红葡萄酒',1,290,'无')
insert ItemsPrice values('毛巾',3,20,'无')
insert ItemsPrice values('饼干',1,10,'无')
insert ItemsPrice values('沐足',3,50,'无')


insert ItemsPrice values('葡萄酒',1,290,'无')
insert ItemsPrice values('脸巾',3,20,'无')
insert ItemsPrice values('甜点',1,10,'无')
insert ItemsPrice values('点心',3,50,'无')
insert ItemsPrice values('红酒',1,290,'无')
insert ItemsPrice values('沐浴用品',3,20,'无')
insert ItemsPrice values('娱乐',1,10,'无')
insert ItemsPrice values('沐足',3,50,'无')
insert ItemsPrice values('红葡萄酒',1,290,'无')
insert ItemsPrice values('毛巾',3,20,'无')
insert ItemsPrice values('饼干',1,10,'无')
insert ItemsPrice values('沐足',3,50,'无')
insert ItemsPrice values('红葡萄酒',1,290,'无')
insert ItemsPrice values('毛巾',3,20,'无')
insert ItemsPrice values('饼干',1,10,'无')
insert ItemsPrice values('沐足',3,50,'无')
insert ItemsPrice values('红葡萄酒',1,290,'无')
insert ItemsPrice values('毛巾',3,20,'无')
insert ItemsPrice values('饼干',1,10,'无')
insert ItemsPrice values('沐足',3,50,'无')
insert ItemsPrice values('红葡萄酒',1,290,'无')
insert ItemsPrice values('毛巾',3,20,'无')
insert ItemsPrice values('饼干',1,10,'无')
insert ItemsPrice values('沐足',3,50,'无')
insert ItemsPrice values('红葡萄酒',1,290,'无')
insert ItemsPrice values('毛巾',3,20,'无')
insert ItemsPrice values('饼干',1,10,'无')
insert ItemsPrice values('沐足',3,50,'无')
insert ItemsPrice values('红葡萄酒',1,290,'无')
insert ItemsPrice values('毛巾',3,20,'无')
insert ItemsPrice values('饼干',1,10,'无')
insert ItemsPrice values('沐足',3,50,'无')
insert ItemsPrice values('红葡萄酒',1,290,'无')
insert ItemsPrice values('毛巾',3,20,'无')
insert ItemsPrice values('饼干',1,10,'无')
insert ItemsPrice values('沐足',3,50,'无')


---------------------消费方式

select * from Consume
insert Consume values('会员卡')


--流水单
select * from reckoning



--订房

select * from RoomUse

insert roomUse values('600880001','80007','2008-5-2',null,'天',null,48,200,1,100,500,1,'US1001','2008-5-1','无')




-----------------插入付款方式
select * from Consume

insert Consume values('现金')
insert Consume values('银联卡')
insert Consume values('会员卡')
insert Consume values('贵宾卡')


------------------创建触发器-在交易信息表中插入-----------------

if exists(select name from sysobjects where name='trig_insert_Reckoning')
drop trigger trig_insert_Reckoning
GO

create trigger trig_insert_Reckoning
on Reckoning
from insert
as
declare @item varchar(20),@mymoney Numeric(20,2),@userID Char(6),@businessTime dateTime
select @item='押金',@mymoney=




		insert guestRoom values('222','0','222',22222,'可供','2008-5-7',default,'22')



select 客房编号=GuestRoomID,类型=(select RoomType from RoomType where TypeNum=GuestRoom.TypeNum),
客房位置=area,客房电话=RoomPhone,状态=state,晨唤=MorningCall,是否保密=case when state='0' then '是'
 else '否' end ,备注=remark from GuestRoom where TypeNum=1



----------------------------------

select ConsumeType from Consume
select * from certificate
select * from nationality


------------------------创建储存过程-------------------

create proc proc_Buyer
@BName varchar(8) ,
@Birthday dateTime,
@certificateID int,
@BCarNum char(18),
@BSex char(2),
@Phone char(11),
@Company varchar(50),
@NationalityID int,
@ProvinceID int,
@Address varchar(100),
@Remark varchar(200)
AS
insert Buyer values(@BName,@Birthday,@certificateID,@BCarNum,@BSex,@Phone,
@Company,@NationalityID,@ProvinceID,@Address,default,@Remark)
GO



create proc proc_RoomUse
@Buyer int,
@GuesRoomID varchar(10),
@ArriveTime dateTime,
@InTime dateTime,
@CountMode char(4),
@charge numeric(20,2),
@otherExpenses numeric(20,2),
@InForegift numeric(20,2),
@OutForegift numeric(20,2),
@BNum int,
@ConsumeID int,
@Rebate float,
@OpenUser char(6) ,
@FillTime dateTime,
@EndCharge numeric(20,2),
@State varchar(8),
@UserID char(6) ,
@ReckoningTime dateTime,
@Remark varchar(200)
AS
insert Roomuse values(@Buyer,@GuesRoomID,@ArriveTime,@InTime,@CountMode,@charge,@otherExpenses
,@InForegift,@OutForegift,@BNum,@ConsumeID,@Rebate,@OpenUser,@FillTime,@EndCharge,@State,@UserID,@ReckoningTime,@Remark)
GO



select max(reckoningID) from roomuse where GuestRoomID='GR201'

insert Account values(80007,101,1,55,1,110,'(无)')
select * from Account

select * from ItemsPrice