关于值班管理的存储过程

create proc Proc_WatchingSetup
  --参数为四种角色的起始位置
  @leader int,
  @Male int,
  @Female int,
  @Driver int
as

declare @i int                                           --计数器
declare @j int
declare @PersonID int
declare @weekday int
declare @InsertPoint datetime
declare @msg char(20)

set @i=1
set @j=1

-- 事务开始
Begin tran ReChange
--删除明天以后的记录（队列已改变删除以前的）
delete from Watching where [Datetime]>GetDate()

if (@@error <>0)
   Begin
     rollback tran
     set @msg='error1'
     return
   end

--重新插入后30天的日期和星期
while @i<=30
  begin
     insert Watching (Datetime,WeekDay) values (dateadd(day,@i,{fn curdate()}),datepart(weekday,dateadd(day,@i,{fn curdate()})))    
     set @i=@i+1
  end

if (@@error !=0)
   Begin
     rollback tran
     set @msg='error2'
     return
   end

--开始使用游标

set @j=1

--////首先按排队顺序读出领导的队列

declare cur_watchingPerson scroll cursor For
   select Personid  from watching_person where part=1 order by orderCode asc

open cur_watchingPerson

--移动到开始位置
fetch absolute @leader from cur_watchingPerson into @PersonID
if @@fetch_status=-1
   fetch first from cur_watchingperson into @PersonID

set @i=1

while @i<=30
  begin    

     while @j<=7 --最长可能是1人插入7天
       begin       
        update watching set LeaderId=@PersonID where [datetime]=(dateadd(day,@i,{fn curdate()}))       
        if (@@error !=0)
          Begin
           rollback tran
           set @msg='error3'
          return
        end
        --如果不足7天就到周末 退出循环 换人
        select @weekday=datepart(weekday,dateadd(day,@i,{fn curdate()}))
        set @i=@i+1
        if (@weekday=1)
          break
         
       end

     set @j=1
    
     fetch next from cur_watchingperson into @PersonID
     -- 如果超出边界 回头队列第一位
     if @@fetch_status=-1
        fetch first from cur_watchingperson into @PersonID
  end

Close cur_watchingPerson
deallocate cur_watchingPerson

--////////////司机很领导完全一样
declare cur_watchingPerson4 scroll cursor For
   select Personid  from watching_person where part=4 order by orderCode asc

open cur_watchingPerson4

--移动到开始位置
fetch absolute @driver from cur_watchingPerson4 into @PersonID
if @@fetch_status=-1
    fetch first from cur_watchingperson4 into @PersonID

set @i=1

while @i<=30
  begin
     while @j<=7 --最长可能是1人插入7天
       begin       
        update watching set driverId=@PersonID where [datetime]=(dateadd(day,@i,{fn curdate()}))       
        if (@@error !=0)
          Begin
          --rollback tran
          set @msg='error3'
          return
        end

        select @weekday=datepart(weekday,dateadd(day,@i,{fn curdate()}))
        set @i=@i+1
        if (@weekday=1)
          break
         
       end

     set @j=1
     fetch next from cur_watchingperson4 into @PersonID
     -- 如果超出边界 回头队列第一位
     if @@fetch_status=-1
        fetch first from cur_watchingperson4 into @PersonID
  end

Close cur_watchingPerson4
deallocate cur_watchingPerson4

--///////////

--汉子每天1人值夜班 相对容易
declare cur_watchingPerson2 scroll cursor For
   select Personid  from watching_person where part=2 order by orderCode asc

open cur_watchingPerson2

--移动到开始位置
fetch absolute @male from cur_watchingPerson2 into @PersonID
if @@fetch_status=-1
    fetch first from cur_watchingperson2 into @PersonID

set @i=1

while @i<=30
  begin
 
     update watching set MaleId=@PersonID where [datetime]=(dateadd(day,@i,{fn curdate()}))     
     if (@@error !=0)
       Begin
          rollback tran
          set @msg='error3'
          return
       end
      
      set @i=@i+1

     fetch next from cur_watchingperson2 into @PersonID
     -- 如果超出边界 回头队列第一位
     if @@fetch_status=-1   
        fetch first from cur_watchingperson2 into @PersonID
 
  end

Close cur_watchingPerson2
deallocate cur_watchingPerson2

--大妈每周六周日2人值白班
declare cur_watchingPerson3 scroll cursor For
   select Personid  from watching_person where part=3 order by orderCode asc

open cur_watchingPerson3

fetch absolute @female from cur_watchingPerson3 into @PersonID
if @@fetch_status=-1
    fetch first from cur_watchingperson3 into @PersonID

set @i=1

while @i<=30
  begin

     select @weekday=[weekday] from watching where [datetime]=(dateadd(day,@i,{fn curdate()}))
    
     --判断 只有周末的半天才值班 安排2人
     if @weekday=7 or @weekday=1
        begin   
          
        --插入第一位
        update watching set Female1=@PersonID where [datetime]=(dateadd(day,@i,{fn curdate()}))       
    
        if (@@error !=0)
         Begin
           rollback tran
           set @msg='error3'
          return
        end

        fetch next from cur_watchingperson3 into @PersonID
        -- 如果超出边界 回头队列第一位
        if @@fetch_status=-1   
           fetch first from cur_watchingperson3 into @PersonID       
        --插入第二位
        update watching set Female2=@PersonID where [datetime]=(dateadd(day,@i,{fn curdate()}))       
    
        if (@@error !=0)
         Begin
          --rollback tran
           set @msg='error3'
          return
        end

       end
      
      set @i=@i+1
      fetch next from cur_watchingperson3 into @PersonID
        -- 如果超出边界 回头队列第一位
        if @@fetch_status=-1   
           fetch first from cur_watchingperson3 into @PersonID    
 
  end

Close cur_watchingPerson3
deallocate cur_watchingPerson3

commit tran