sql_procedurecode.txt

连锁影院售票系统

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,@Password,2,@TheaterID)
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)
as
begin tran
  insert into UserLogin
    values(@UserID,  @Password,  @UserRights,  @TheaterID)
  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
    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
    valuse(@FilmID varchar(8),  @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,
  @UserID int 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)
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)
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

creater 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
  select UserID from UserLogin
    where UserID=@UserID
  if @@Rowcuont=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
    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 SaleOfBackTickets--从退票表中搜寻符合条件的影票并售出
  @onShowIndex int,
  @BoxorNot int,--标示所申请购买的影票的种类
  @OnShowDate varchar(20) OUTPUT,
  @OnShowNOP varchar(6) OUTPUT, 
  @BoxNo int OUTPUT, 
  @SeatNo OUTPUT, 
  @FilmID varchar(8) OUTPUT, 
  @CinemID varchar(4) OUTPUT
as
begin tran
  declare int @mark
  set @mark=@BoxorNot
  if @mark=1
    begin
      select TOP 1 @OnShowDate=OnShowDate,@OnShowNOP=OnShowNOP,@BoxNo=BoxNo,@FilmID=FilmID,@CinemaID from BackTickets
        where OnShowIndex=@OnShowIndex
      Delete from BackTickets where OnShowIndex=@OnShowIndex and BoxNo=@BoxNo
  else
    begin
      select TOP 1 @OnShowDate=OnShowDate,@OnShowNOP=OnShowNOP,@SeatNo=SeatNo,@FilmID=FilmID,@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
  declare int @day
  declare int @flag
  set @day=@OnShowNOP/4
  set @flag=@BoxorNot
  if @day=0
    begin
      if @flag=1
        begin
          select @BoxTickets=BoxTickets from SaleTodayInf where OnShowIndex=@OnShowIndex
          update SaleTodayInf set BoxTickets=BoxTickets-1 where OnShowIndex=@OnShowIndex
          updateLoginRecord set BoxTickets=BoxTickets+1 where UserID=@UserID
        end
      else
        begin
          select @SeatTickets=SeatTickets from SaleTodayInf where OnShowIndex=@OnShowIndex
          update SaleTodayInf set SeatTickets=SeatTickets-1 where OnShowIndex=@OnShowIndex
          updateLoginRecord set SeatTickets=SeatTickets+1 where UserID=@UserID
        end
    end
  if @day=1
    begin
      if @flag=1
        begin
          select @BoxTickets=BoxTickets from SaleTodayInf where OnShowIndex=@OnShowIndex
          update SaleTodayInf set BoxTickets=BoxTickets-1 where OnShowIndex=@OnShowIndex
          updateLoginRecord set BoxTickets=BoxTickets+1 where UserID=@UserID
        end
      else
        begin
          select @SeatTickets=SeatTickets from SaleTodayInf where OnShowIndex=@OnShowIndex
          update SaleTodayInf set SeatTickets=SeatTickets-1 where OnShowIndex=@OnShowIndex
          updateLoginRecord set SeatTickets=SeatTickets+1 where UserID=@UserID
        end
    end
  if @day=2
    begin
      if @flag=1
        begin
          select @BoxTickets=BoxTickets from SaleTodayInf where OnShowIndex=@OnShowIndex
          update SaleTodayInf set BoxTickets=BoxTickets-1 where OnShowIndex=@OnShowIndex
          updateLoginRecord set BoxTickets=BoxTickets+1 where UserID=@UserID
        end
      else
        begin
          select @SeatTickets=SeatTickets from SaleTodayInf where OnShowIndex=@OnShowIndex
          update SaleTodayInf set SeatTickets=SeatTickets-1 where OnShowIndex=@OnShowIndex
          updateLoginRecord set SeatTickets=SeatTickets+1 where UserID=@UserID
        end
    end
go

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

create proc MostSoldFilms
as
  select Top 10