1、create database hotelmanager /*创建数据库 hotelmanager*/gouse hotelmanagergocreate table cusinfo /*创建数据表 cusinfo*/(dingname varchar(10) not null,cname varchar(10), cid char(18), vip char(2)gocreate table ordform /*创建数据表 ordform*/(ono varchar(36) not null,cname varchar(10) not null,csex char(2), cid char(
2、18), rid char(6) not null,rstyle varchar(10), arrivetime datetime, cno char(10) not null,dingname varchar(10),livetime char(10), dingph char(15) not null,ozt char(4)gocreate table roominfo /*创建数据表 roominfo*/(rid char(6) not null,rstyle varchar(10), kfzt char(2) not null)gocreate table liveorder /*创建
3、数据表 liveorder*/(lno varchar(36) not null,rid char(6) not null,rstyle varchar(10), btime datetime not null,cno char(10), cname varchar(10) not null,etime datetime,ono varchar(36),lzt char(4) )gocreate table bill /*创建数据表 bill*/(bno varchar(36) not null,rid char(6) not null,cname varchar(10),cust money
4、, btime datetime,etime datetime,remarks varchar(40) gocreate table roomsp /*创建数据表 roomsp*/(rstyle varchar(10) not null,rprice money)gocreate table hordform /*创建数据表 hordform*/(ono varchar(36) not null,cname varchar(10) not null,csex char(2), cid char(18), rid char(6) not null,rstyle varchar(10), arri
5、vetime datetime, cno char(10) not null,dingname varchar(10),livetime char(10), dingph char(15) not null,ozt char(4)gocreate table hliveorder /*创建数据表 hliveorder*/(lno varchar(36) not null,rid char(6) not null,rstyle varchar(10), btime datetime not null,cno char(10), cname varchar(10) not null,etime d
6、atetime,ono varchar(36),lzt char(4) )goinsert into cusinfo values(mao,zcl,330227198805060071,n)insert into cusinfo values(qxb,zyy,330227198603070022,n)goinsert into roominfo values(2010,s,n)insert into roominfo values(2201,s,n)insert into roominfo values(2301,s,n) insert into roominfo values(3010,s,
7、y) insert into roominfo values(3012,s,n)insert into roominfo values(3015,s,n)insert into roominfo values(4210,d,n)insert into roominfo values(4110,d,n)insert into roominfo values(4010,d,n)insert into roominfo values(5220,d,n)insert into roominfo values(5210,d,n)insert into roominfo values(5201,d,n)g
8、oinsert into roomsp values(s,200)insert into roomsp values(d,300)go insert into ordform values(o0201001,zcl,f,330227198805060071,2301,s,2010-01-02,1,mao,1,883060089,n)goinsert into liveorder values(l2010001,2301,s,2010-01-02,1,zcl,2010-01-03,o0201001,n)insert into liveorder values(l2010002,3010,s,20
9、10-01-03,1,qxb,2010-01-04,o0201002,y)insert into liveorder(lno,rid,rstyle,btime,cno,cname,ono,lzt) values(l2010003,5220,d,2010-01-09,1,myy,o0201003,y)insert into liveorder(lno,rid,rstyle,btime,cno,cname,ono,lzt)values(l2010004,5201,d,2010-01-02,2,zxx,null,y)goinsert into bill(bno,rid,cname,cust,btim
10、e) values(b2010003,5220,myy,0,2010-01-10)insert into bill(bno,rid,cname,cust,btime) values(b2010004,5201,zxx,0,2010-01-02)goalter table cusinfo add primary key(dingname)alter table ordform add primary key(ono)alter table roominfo add primary key(rid)alter table liveorder add primary key(lno)alter ta
11、ble bill add primary key(bno)alter table roomsp add primary key(rstyle)gocreate proc ordformproc lno varchar(36) /*保存入住单*/asbegin tranupdate roominfo set kfzt=y from roominfo,liveorder where liveorder.rid=roominfo.rid and liveorder.lno=lno /*修改客房信息*/update ordform set ozt=y from ordform,liveorder wh
12、ere liveorder.ono=ordform.ono and liveorder.lno=lno /*修改预订单信息*/insert into hordform select * from ordform where ozt=y /*将已入住的预订单放入历史*/delete from ordform where ozt=y /*清除已入住预订单*/commitgoselect * from roominfoselect * from ordformgoexec ordformproc l2010001exec ordformproc l2010004select * from roomi
13、nfoselect * from ordformgocreate proc liveorderproc lno varchar(36) /*收银退房*/asbegin tranupdate liveorder set etime=getdate(),lzt=n where lno=lno update roominfo set kfzt=n from roominfo,liveorder where roominfo.rid=liveorder.rid and liveorder.lno=lno /*修改客房状态*/insert into hliveorder select * from li
14、veorder where lno=lno /*导入历史*/delete from liveorder where lno=lno /*清除入住单*/commitgoselect * from liveorderselect * from hliveordergoexec liveorderproc l2010004goselect * from liveorderselect * from hliveorder select *from roominfo where rid=5201gocreate proc billproc lno varchar(36) /*计算应收款*/asbegin
15、 trandeclare total moneydeclare datecount intupdate liveorder set etime=getdate() where lno=lno /*设置离店时间*/select datecount=datediff(dd,btime,getdate() from liveorder where lno=lno /*计算住店天数*/select total=0select total=datecount*(isnull(rprice,0) from liveorder,bill,roomsp where bill.rid=liveorder.rid and liveorder.rstyle=roomsp.rstyle and lno=lno /*计算住店金额*/update bill set etime=getdate(),cust=total from bill,liveorder where bill.rid=liveorder.rid and lno=lno /*修改账单*/commitgo select * from liveorderselect * from bill goexec billproc l2010004goselect * from liveorderselect * from bill
