1 alter proc proc_DRU8
  2 @order_id as int,
  3 @VoucherID as nvarchar(25)
  4 as
  5 declare @cur as cursor
  6 declare @cpsn_num as nvarchar(10)
  7 declare @flag as int
  8 declare @flag2 as int=0
  9 
 10 
 11 --update HR_OT_HEADER set order_statr='AP',ap_man='0792',an_man='HR',per_num=50 where order_statr<>'C' and ID=5580
 12 --update HR_OT_Delay set order_statr='AP' where order_statr<>'C' and order_ID=5580
 13 
 14 begin transaction
 15 set xact_abort on
 16 --declare @tran_error int
 17 --    set @tran_error = 0
 18 begin try
 19 update hr_ot_loginuser set pass = '1' where userName = '3020' 
 20 
 21 update HR_OT_HEADER set order_statr='HR',ap_man='HR',an_man=' ',per_num=90 where order_statr<>'C' and ID=@order_id
 22  --set @tran_error = @tran_error + @@error
 23 update HR_OT_Delay set order_statr='HR' where order_statr<>'C' and order_ID=@order_id
 24  --set @tran_error = @tran_error + @@error
 25 
 26     set @cur= cursor forward_only static for
 27     select per_num from HR_OT_Delay where order_statr<>'C' and order_ID=@order_id
 28     open @cur
 29     fetch next from @cur into @cpsn_num
 30     
 31     while (@@fetch_status = 0)
 32     begin
 33     if(@cpsn_num='0057' or @cpsn_num='0284'or @cpsn_num='0408'or @cpsn_num='0608'or @cpsn_num='0656'or @cpsn_num='0852'or @cpsn_num='1562'or @cpsn_num='1761'or @cpsn_num='2236'or @cpsn_num='2687'or @cpsn_num='2695'or @cpsn_num='2696'or @cpsn_num='3032')
 34     set @flag=1
 35     else if(@cpsn_num='2858' or @cpsn_num='2514'or @cpsn_num='1017'or @cpsn_num='1197')
 36     set @flag=2
 37     else
 38     set @flag=3
 39     fetch next from @cur into @cpsn_num
 40     end
 41 close @cur
 42 deallocate @cur
 43 
 44 if(@flag=1 and @flag2=0)
 45 
 46     insert into  HR_TM_OverTimeVoucher 
 47 select orderNo,null,'10103',null,'CS01',IcomputeType,VReason,Remark,OT_DATE,OT_TIMES,null,null,left(OT_BTIME,5),left(OT_ETIME,5),
 48 (case when OT_DATE>OT_BDATE then -1 when OT_DATE<OT_BDATE then 1 when OT_DATE=OT_BDATE then 0 end),
 49 (case when OT_DATE>OT_EDATE then -1 when OT_DATE<OT_EDATE then 1 when OT_DATE=OT_EDATE then 0 end),
 50 left(OT_BTIME,5),left(OT_ETIME,5),0,0,0,null,null,null,ID,1,'system','jj','system','jj','system','jj',C_date ,C_date  ,C_date,null 
 51 from HR_OT_HEADER where ID=@order_id
 52  --set @tran_error = @tran_error + @@error
 53  set @flag2=1
 54 
 55 
 56 if(@flag=2 and @flag2=0)
 57 
 58 insert into  HR_TM_OverTimeVoucher 
 59 select orderNo,null,10506,null,'CS01',IcomputeType,VReason,Remark,OT_DATE,OT_TIMES,null,null,left(OT_BTIME,5),left(OT_ETIME,5),
 60 (case when OT_DATE>OT_BDATE then -1 when OT_DATE<OT_BDATE then 1 when OT_DATE=OT_BDATE then 0 end),
 61 (case when OT_DATE>OT_EDATE then -1 when OT_DATE<OT_EDATE then 1 when OT_DATE=OT_EDATE then 0 end),
 62 left(OT_BTIME,5),left(OT_ETIME,5),0,0,0,null,null,null,ID,1,'system','jj','system','jj','system','jj',C_date ,C_date  ,C_date,null 
 63 from HR_OT_HEADER where ID=@order_id
 64  --set @tran_error = @tran_error + @@error
 65  set @flag2=1
 66 
 67 if(@flag=3 and @flag2=0)
 68 
 69     insert into HR_TM_OverTimeVoucher 
 70 select orderNo,null,dep,null,'CS01',IcomputeType,VReason,Remark,OT_DATE,OT_TIMES,null,null,left(OT_BTIME,5),left(OT_ETIME,5),
 71 (case when OT_DATE>OT_BDATE then -1 when OT_DATE<OT_BDATE then 1 when OT_DATE=OT_BDATE then 0 end),
 72 (case when OT_DATE>OT_EDATE then -1 when OT_DATE<OT_EDATE then 1 when OT_DATE=OT_EDATE then 0 end),
 73 left(OT_BTIME,5),left(OT_ETIME,5),0,0,0,null,null,null,ID,1,'system','jj','system','jj','system','jj',C_date ,C_date  ,C_date,null 
 74 from HR_OT_HEADER where ID=@order_id
 75  --set @tran_error = @tran_error + @@error
 76  set @flag2=1
 77 
 78 
 79 declare @cur2 as cursor
 80 declare @per_num as nvarchar(10)
 81 declare @ot_date as date
 82 declare @ot_times as decimal(5,1)
 83 declare @vreason as nvarchar(50)
 84 declare @vremark as nvarchar(50)
 85 declare @ot_btime as time
 86 declare @ot_etime as time
 87 declare @ot_bdate as date
 88 declare @ot_edate as date
 89 declare @i as int
 90 declare @j as int
 91 declare @order_num as int
 92 
 93     set @cur2= cursor forward_only static for
 94     select order_id,per_num,ot_date,ot_times,vreason,remarks,ot_btime,ot_etime,ot_bdate,ot_edate from HR_OT_Delay where order_statr<>'C' and order_ID=@order_id
 95     open @cur2
 96     fetch next from @cur2 into @order_num,@per_num,@ot_date,@ot_times,@vreason,@vremark,@ot_btime,@ot_etime,@ot_bdate,@ot_edate
 97     while (@@fetch_status = 0)
 98     begin
 99     
100     if(@ot_bdate>@ot_date)
101     set @i=1
102     if(@ot_bdate<@ot_date)
103     set @i=-1
104     if(@ot_bdate=@ot_date)
105     set @i=0
106     if(@ot_edate>@ot_date)
107     set @j=1
108     if(@ot_edate<@ot_date)
109     set @j=-1
110     if(@ot_edate=@ot_date)
111     set @j=0
112     
113     insert into hr_tm_OverTimeresult 
114 (cPsn_Num,nOvertimeHours,nManHours,dJbDate,vJbCode,vReason,vRemark,dBeginTime,dEndTime,dDutyTime,dOffTime,bOverDate,bOverDate2,bPeriod,bCompute,iComputeType,iRecordId,cAuditorNum,cAuditor,dAuditTime,bAuditFlag,cCreatorNum,cCreator,dCreatTime,cOperatorNum,cOperator,dOperatTime,rFreeCardMode,VoucherID) 
115 values (@per_num,0,@ot_times,@ot_date,(select (case when cPsnProperty='1' or cPsnProperty='0'  then 'CU01' else ( select  (case when rDateProperty='0' then 'CS01' when rDateProperty='1' then 'CS02' when rDateProperty='2' then 'CS03' end ) from paibanresult where cPsn_Num=hr_hi_person.cPsn_Num and convert(varchar(20),ddutydate,23) =@ot_date) end) from hr_hi_person where cPsn_Num=@per_num),@vreason,@vremark,@ot_btime,@ot_etime,@ot_btime,@ot_etime, @i, @j,0,0,5,@order_num,'jj','system',convert(nvarchar(15),getdate(),23),1,'jj','system',convert(nvarchar(15),getdate(),23),'jj','system',convert(nvarchar(15),getdate(),23),0,@VoucherID)
116  --set @tran_error = @tran_error + @@error
117     fetch next from @cur2 into @order_num,@per_num,@ot_date,@ot_times,@vreason,@vremark,@ot_btime,@ot_etime,@ot_bdate,@ot_edate
118     end
119 close @cur2
120 deallocate @cur2
121 
122 insert into HR_OT_Logs (Order_ID,Long_ID,Order_statr,AP_Num,AP_datetime,P_man,N_man) values (@order_id,@VoucherID,'HR',90,getdate(),'HR',' ')
123  --set @tran_error = @tran_error + @@error
124 
125 update hr_tm_OverTimeresult set ddutytime=cast(ddutytime as nvarchar(5)),dofftime=cast(dofftime as nvarchar(5)) 
126 where len(cast(ddutytime as nvarchar(10)))>5 or len(cast(dofftime as nvarchar(10)))>5
127  --set @tran_error = @tran_error + @@error
128   commit tran 
129 end try
130  begin catch        
131         --set @tran_error = @tran_error + 1
132         rollback tran 
133     end catch
134 --if (@tran_error > 0)
135 --    begin
136 --        --执行出错，回滚事务
137 --        rollback tran 
138 --    end
139 --else
140 --    begin
141 --        commit tran 
142 --    end