proctable.sql

连锁影院售票系统

/*create proc AddNewMember  --添加新加盟成员
  @TheaterID varchar(4),
  @TheaterName varchar(20),
  @AdminID varchar(6),
  @LocationCity varchar(20),
  @Address varchar(50),
  @TelNo varchar(15),
  @Longitude varchar(20),
  @Latitude varchar(20),
  @AdminName varchar(20)
as
begin tran
   insert into TheaterInf
     values(@TheaterID,  @TheaterName,  @AdminID,  @LocationCity,  @Address,  @TelNo,  @Longitude,  @Latitude)
   if not exists(select * from UserLogin where UserID=@AdminID)
     insert into UserLogin values(@AdminID,@AdminName,11111111,2,@TheaterID,0)
commit
go*/

/*create proc AddNewCinema--添加新放映室信息
  @CinemaID varchar(4),
  @CinemaType varchar(6),
  @TheaterID varchar(4),
  @SingleSeat int ,
  @Boxes int
as
begin tran
  insert into CinemaInf
    values(@CinemaID,  @CinemaType,  @TheaterID,  @SingleSeat,  @Boxes)
--comit
go*/

/*create proc AddNewUser--添加新用户
  @UserID varchar(6),
  @Password varchar(10),
  @UserRights int,
  @TheaterID varchar(4),
  @UserName varchar(20)
as
begin tran
  insert into UserLogin
    values(@UserID, @UserName,  @Password,  @UserRights,  @TheaterID,0)
  insert into LoginRecord(UserID)
    values(@UserID)
  update LoginRecord set LoginTime=0000-00-00,LogoutTime=0000-00-00,SingleTickets=0,boxTickets=0,BackTickets=0,Total=0
    where UserID=@UserID
commit
go*/

/*create proc LoginInfRecord--记录前台票务人员登陆及相关营业信息信息
  @UserID varchar(6),
  @LoginTime varchar(20)
as
begin tran
  update LoginRecord set LoginTime=@LoginTime,LogoutTime=0000-00-00,SingleTickets=0,boxTickets=0,BackTickets=0,Total=0
    where UserID=@UserID
commit
go*/

/*create proc LogoutInfRecord--票务人员离开记录及相关营业信息
  @UserID varchar(6),
  @LogoutTime varchar(20)
as
begin tran
  update LoginRecord set LogoutTime=@LogoutTime
    where UserID=@UserID
  insert into LoginHistory (UserID ,LoginTime,LogoutTime,SingleTickets ,BoxTickets,BackTickets,Total)
    select * from LoginRecord where UserID=@UserID
  update UserLogin set Online=0 where UserID=@UserID
commit
go*/

/*create proc AddFilm--影片库新片入库
  @FilmID varchar(8),
  @FilmName varchar(50),
  @FilmType varchar(6),
  @Director varchar(50),
  @LeadActors varchar(50),
  @PlayWright varchar(50),
  @BriefStory varchar(1000),
  @PlaybillAdd varchar(50)
as
begin tran
  insert into FilmInf
    values (@FilmID,  @FilmName,  @FilmType,  @Director,  @LeadActors,  @PlayWright,  @BriefStory,  @PlaybillAdd)
commit
go*/

/*create proc BackTicket--退票记录
  @OnShowIndex int,
  @OnShowDate varchar(20),
  @OnShowNOP varchar(6),
  @FilmID varchar(8),
  @CinemaID varchar(4),
  @SeatNo int,
  @BoxNo int
as
begin tran
  insert into BackTicketInf
    values(@OnShowIndex,@OnShowDate,  @OnShowNOP,  @FilmID,  @CinemaID,  @SeatNo,  @BoxNo)
commit
go*/

/*create proc LoginCheck--验证用户是否存在，若存在返回其UserName,UserRights,TheaterID信息；若不存在则返回权限值为0
  @UserID varchar(6),
  @Password varchar(20),
  @UserRights int OUTPUT,
  @UserName varchar(20) OUTPUT,
  @TheaterID varchar(4) OUTPUT,
  @Online int OUTPUT
as
begin tran
  select @UserID=UserID,@UserName=UserName,@TheaterID=TheaterID,@Online=Online
    from UseLogin
    where UserID=@UserID and Password=@Password
  if @@Rowcount<1
    select @UserRights=0
  else if @Online=0
         update UserLogin set Online=1 where UserID=@UserID
  else select @UserRights=0
go*/

/*create proc UpdateFilmInf--修改影片信息
  @FilmID varchar(8),
  @FilmName varchar(50),
  @FilmType varchar(6),
  @Director varchar(50),
  @LeadActors varchar(50),
  @PlayWright varchar(50),
  @BriefStory varchar(1000),
  @PlaybillAdd varchar(50)
as
begin tran
  update FilmInf set FilmName=@FilmName,FilmType=FilmType,Director=@Director,LeadActors=@LeadActors,PlayWright=@PlayWright,BriefStory=@BriefStory,PlaybillAdd=@PlaybillAdd  where FilmID=@FilmID
go*/

/*create proc UpdateMemberInf--修改加盟商信息
  @TheaterID varchar(4),
  @TheaterName varchar(20),
  @AdminID varchar(6),
  @LocationCity varchar(20),
  @Address varchar(50),
  @TelNo varchar(15),
  @Longitude varchar(20),
  @Latitude varchar(20),
  @AdminName varchar(20)
as
begin tran
  update TheaterInf set TheaterName=@TheaterName,AdminID=@AdminID,LocationCity=@LocationCity,Address=@Address,TelNo=@TelNo,Longitude=@Longitude,Latitude=@Latitude  where TheaterID=@TheaterID
go*/

/*create proc ShowEmptyCinemaforSystem--系统管理员察看所选时段的空闲放映室
  @OnShowDate varchar(20),
  @OnShowNOP varchar(6),
  @CinemaID varchar(4) OUTPUT
as
begin tran
  select @CinemaID=CinemaID from CinemaInf
    where CinemaID not in (select CinemaID from OnShowPlan where OnShowDate=@OnShowDate and OnShowNOP=@OnShowNOP)
go*/

/*create proc ShowEmptyCinemaforTheater--剧场管理员察看所选时段的空闲/即可修改的放映室
  @OnShowDate varchar(20),
  @OnShowNOP varchar(6),
  @theaterID varchar(4),
  @CinemaID varchar(4) OUTPUT
as
begin tran
  select @CinemaID=CinemaID from CinemaInf
    where CinemaID not in (select CinemaID from OnShowPlan where TheaterID=@TheaterID and ChangeAble=1 and  OnShowDate=@OnShowDate and OnShowNOP=@OnShowNOP)
go*/

/*create proc DoOnShowPlan--安排上映表
  @OnShowDate varchar(20),
  @FilmID varchar(8),
  @FilmName varchar(50),
  @CinemaID varchar(4),
  @OnShowNOP varchar(6),
  @SaleAble int,
  @ChangeAble int,
  @OnShowBand int
as
begin tran
  insert into OnShowPlan (OnShowDate,FilmID,FilmName,CinemaID,OnShowNOP,SaleAble,ChangeAble,OnShowBand) 
    values (@OnShowDate,@FilmID,@FilmName,@CinemaID,@OnShowNOP,@SaleAble,@ChangeAble,@OnShowBand)
go*/

/*create proc UpdateOnShowPlan--剧场管理员对上映信息进行修改
  @OnShowIndex int,
  @OnShowDate varchar(20),
  @FilmID varchar(8),
  @FilmName varchar(50),
  @CinemaID varchar(4),
  @OnShowNOP varchar(6),
  @SaleAble int,
  @ChangeAble int,
  @OnShowBand int
as
begin tran
  update onShowPlan set OnShowDate=@OnShowDate,FilmID=@FilmID,FilmName=@FilmName,CinemaID=@CinemaID,OnShowNOP=@OnShowNOP,SaleAble=@SaleAble,ChangeAble=@ChangeAble  where OnShowIndex=@OnShowIndex
go*/

/*create proc MaxSaleOnce--查询管理员赋予的一次最大售票量
  @OnShowIndex int,
  @OnShowBand int OUTPUT
as
begin tran
  select @OnShowBand=OnShowBand from onShowPlan
    where OnShowIndex=@OnShowIndex
go*/

/*create proc UpdateUserLogin--用于修改密码或姓名
  @Password varchar(20),
  @UserID varchar(6),
  @UserName varchar(20)
as
begin tran
  update UserLogin set [Password]=@Password,UserName=@UserName
    where UserID=@UserID
go*/

/*create proc SelectUserInf--查询用户密码或姓名
  @UserID varchar(6),
  @UserName varchar(20) OUTPUT
as
begin tran
  select @UserName=UserName from UserLogin
    where UserID=@UserID
go*/

/*create proc TransRights--赋予普通票务人员以销售异地影票的权限
  @UserID varchar(6),
  @Password varchar(20),
  @TheaterID varchar(4),
  @UserRights int OUTPUT
as
begin tran
  select @UserRights=UserRights from UserLogin
    where UserID=@UserID and Password=@Password and TheaterID=@TheaterID
  if @@Rowcount<1
     select @UserRights=0
go*/

/*create proc CheckUserID--检验用户ID是否可用
  @UserID varchar(6),
  @Flag int OUTPUT
as
begin tran
  select * from UserLogin
    where UserID=@UserID
  if @@Rowcount=1
    select @Flag=1 --即用户ID不可用
  else select @Flag=0 --即用户ID可用
go*/

/*create proc LeftBackTickets--返回符合条件的退回的票的数量
  @OnShowIndex int,
  @BoxorNot int,--标示所申请购买的影票的种类
  @BackNums int OUTPUT
as
begin tran
  --declare int @mark
  --set @mark=@BoxorNot
  --if @mark=1
  if @BoxorNot=1
    select * from BackTickets where OnShowIndex=@OnShowIndex and SeatNo=0
  else select * from BackTickets where OnShowIndex=@OnShowIndex and BoxNo=0
  select @BackNums=@@Rowcount
go*/

/*create proc PintTheaterInf--打印票信息
  @TheaterID varchar(4),
  @TheaterName varchar(20) output,
  @LocationCity varchar(20) output,
  @Address varchar(50) output,
  @TelNo varchar(15) output,
  @Longitude varchar(20) output,
  @Latitude varchar(20) output
as
begin tran
  select @TheaterName=TheaterName,@LocationCity=LocationCity,@Address=Address,@TelNo=TelNo,@Longitude=Longitude,@Latitude=Latitude
    from TheaterInf where TheaterID=@TheaterID
go*/

/*create proc SaleOfBackTickets--从退票表中搜寻符合条件的影票并售出
  @UserID varchar(6),
  @OnShowIndex int,
  @BoxorNot int,--标示所申请购买的影票的种类
  @OnShowDate varchar(20) OUTPUT,
  @OnShowNOP varchar(6) OUTPUT, 
  @BoxNo int OUTPUT, 
  @SeatNo int OUTPUT, 
  @FilmID varchar(8) OUTPUT, 
  @CinemaID varchar(4) OUTPUT
as
begin tran
  if @BoxorNot=1 
    begin
      select TOP 1 @OnShowDate=OnShowDate,@OnShowNOP=OnShowNOP,@BoxNo=BoxNo,@FilmID=FilmID,@CinemaID=CinemaID
        from BackTickets
        where OnShowIndex=@OnShowIndex
    end
 else
    begin
      select TOP 1 @OnShowDate=OnShowDate,@OnShowNOP=OnShowNOP,@SeatNo=SeatNo,@FilmID=FilmID,@CinemaID=CinemaID 
        from BackTickets
        where OnShowIndex=@OnShowIndex
      delete from BackTickets where OnShowIndex=@OnShowIndex and SeatNo=@SeatNo
    end
  update LoginRecord set BackTickets=BackTickets-1 where UserID=@UserID
go
*/

/*create proc SaleOfNewTickets--从票库中销售新的影票
  @OnShowIndex int,
  @UserID varchar(6),
  @OnshowNOP int,
  @BoxorNot int,
  @SingleTickets int OUTPUT,
  @BoxTickets int OUTPUT
as
begin tran
  if @OnShowNOP/4=0
    begin
      if @BoxorNot=1
        begin
          select @BoxTickets=BoxTickets from SaleTodayInf where OnShowIndex=@OnShowIndex
          update SaleTodayInf set BoxTickets=BoxTickets-1 where OnShowIndex=@OnShowIndex
          update LoginRecord set BoxTickets=BoxTickets+1 where UserID=@UserID
        end
      else
        begin
          select @SingleTickets=SingleTickets from SaleTodayInf where OnShowIndex=@OnShowIndex
          update SaleTodayInf set SingleTickets=SingleTickets-1 where OnShowIndex=@OnShowIndex
          update LoginRecord set SingleTickets=SingleTickets+1 where UserID=@UserID
        end
    end
  if @OnShowNOP/4=1
    begin
      if @BoxorNot=1
        begin
          select @BoxTickets=BoxTickets from SaleTodayInf where OnShowIndex=@OnShowIndex
          update SaleTodayInf set BoxTickets=BoxTickets-1 where OnShowIndex=@OnShowIndex
          update LoginRecord set BoxTickets=BoxTickets+1 where UserID=@UserID
        end
      else
        begin
          select @SingleTickets=SingleTickets from SaleTodayInf where OnShowIndex=@OnShowIndex
          update SaleTodayInf set SingleTickets=SingleTickets-1 where OnShowIndex=@OnShowIndex
          update LoginRecord set SingleTickets=SingleTickets+1 where UserID=@UserID
        end
    end
  if @OnShowNOP/4=2
    begin
      if @BoxorNot=1
        begin
          select @BoxTickets=BoxTickets from SaleTodayInf where OnShowIndex=@OnShowIndex
          update SaleTodayInf set BoxTickets=BoxTickets-1 where OnShowIndex=@OnShowIndex
          update LoginRecord set BoxTickets=BoxTickets+1 where UserID=@UserID
        end
      else
        begin
          select @SingleTickets=SingleTickets from SaleTodayInf where OnShowIndex=@OnShowIndex
          update SaleTodayInf set SingleTickets=SingleTickets-1 where OnShowIndex=@OnShowIndex
          update LoginRecord set SingleTickets=SingleTickets+1 where UserID=@UserID
        end
    end
go*/

/*create proc PreparforBusiness--营业数据预备
  @OnShowDate varchar(20)
as
begin tran
  update OnShowPlan set OnShowNOP=OnShowNOP-4 where OnShowNOP>3
  insert into HistoryRecords(FilmID,CinemaID,TheaterID,OnShowNOP,SingleTickets,BoxTickets,BoxOfficeIntake)
    select FilmID,CinemaID,TheaterID,OnShowNOP,SingleTickets,BoxTickets,Intake from SaleTodayInf
  delete SaleTodayInf
  insert into SaleTodayInf  select * from SaleTomorrowInf
  Update SaleTodayInf set OnShowNOP=OnShowNOP-4
  delete SaleTomorrowInf
  insert into SaleTomorrowInf  select * from SaleThirddayInf
  Update SaleTomorrowInf set OnShowNOP=OnShowNOP-4
  delete SaleThirddayInf
  insert into SaleThirddayInf(OnShowIndex,TheaterID,CinemaID,FilmID,OnShowNOP,SingleTickets,BoxTickets)
         select OnShowPlan.OnShowIndex,CinemaInf.TheaterID,OnShowPlan.CinemaID,OnShowPlan.FilmID,OnShowPlan.OnShowNOP,CinemaInf.SingleSeat,CinemaInf.Boxes 
                                          from OnShowPlan,CinemaInf
                                          where OnShowPlan.OnShowNOP=8 or OnShowPlan.OnShowNOP=9 or OnShowPlan.OnShowNOP=10 or OnShowPlan.OnShowNOP=11 and OnShowPlan.CinemaID=CinemaInf.CinemaID
  update SaleThirddayInf set Intake=0
go*/


/*create proc MostSoldFilms
as
  select *
    from FilmInf
    where FilmID=(select top 10 FilmID from HistoryRecords
                    order by BoxOfficeIntake desc)*/