open CaseID_cur
FETCH NEXT FROM CaseID_cur INTO @workflowinstance_id
WHILE @@FETCH_STATUS = 0
BEGIN
open workflow_cur
fetch last from workflow_cur into @finieshdate,@state,@receive_type_id,@Unit_id
print N'宗案件数'
print @CurrProcessNum
print '案件ID:'
print @workflowinstance_id
if @finieshdate is null and @state=100507 and @receive_type_id=100603 --表示正在处理:state=100507 finished_date is null receive_type_id=100603
begin
select @ErrorA=count(*) from #Event_Unit
where workflow_instance_id=@workflowinstance_id and receive_object_id<> @Unit_id and state=100507
print '正在处理中回退总数'
print @ErrorA
end
else
if @finieshdate is not null and @state=100507 and @receive_type_id=100603 -- 表示处理完
begin
select @ErrorC=count(*) from #Event_Unit
where workflow_instance_id=@workflowinstance_id and receive_object_id<> @Unit_id and state=100507 print '表示处理完回退总数:'
print @MaxErrors
end
else
if @finieshdate is null and @state=100505 and @receive_type_id<>100603--表示回退状态标志 state=100505 finished_date is null
begin
select @ErrorB =count(*)
from #Event_Unit
where workflow_instance_id=@workflowinstance_id and state=100507
and receive_type_id=100603 and receive_object_id is not null
select @MaxErrors=max(t)
from
(
select receive_object_id,count(*)t
from #Event_Unit
where workflow_instance_id=@workflowinstance_id and state=100507
group by receive_object_id
) MaxE
if @ErrorB>1 begin select @ErrorB=@ErrorB-@MaxErrors end
print '表示回退de回退数:'
print @ErrorB
end
else
begin goto lastwhill end set @Errors=@ErrorA+@ErrorB+@ErrorC --
insert into #Workflow values(@workflowinstance_id,@Errors)
print 'insert #workflow:'
set @ErrorA=0
set @ErrorB=0
set @ErrorC=0
set @Errors=0
lastwhill:
close workflow_cur
set @CurrProcessNum=@CurrProcessNum-1FETCH NEXT FROM CaseID_cur INTO @workflowinstance_id
END
close CaseID_cur
DEALLOCATE CaseID_cur
DEALLOCATE workflow_cur
FETCH NEXT FROM CaseID_cur INTO @workflowinstance_id
WHILE @@FETCH_STATUS = 0
BEGIN
open workflow_cur
fetch last from workflow_cur into @finieshdate,@state,@receive_type_id,@Unit_id
print N'宗案件数'
print @CurrProcessNum
print '案件ID:'
print @workflowinstance_id
if @finieshdate is null and @state=100507 and @receive_type_id=100603 --表示正在处理:state=100507 finished_date is null receive_type_id=100603
begin
select @ErrorA=count(*) from #Event_Unit
where workflow_instance_id=@workflowinstance_id and receive_object_id<> @Unit_id and state=100507
print '正在处理中回退总数'
print @ErrorA
end
else
if @finieshdate is not null and @state=100507 and @receive_type_id=100603 -- 表示处理完
begin
select @ErrorC=count(*) from #Event_Unit
where workflow_instance_id=@workflowinstance_id and receive_object_id<> @Unit_id and state=100507 print '表示处理完回退总数:'
print @MaxErrors
end
else
if @finieshdate is null and @state=100505 and @receive_type_id<>100603--表示回退状态标志 state=100505 finished_date is null
begin
select @ErrorB =count(*)
from #Event_Unit
where workflow_instance_id=@workflowinstance_id and state=100507
and receive_type_id=100603 and receive_object_id is not null
select @MaxErrors=max(t)
from
(
select receive_object_id,count(*)t
from #Event_Unit
where workflow_instance_id=@workflowinstance_id and state=100507
group by receive_object_id
) MaxE
if @ErrorB>1 begin select @ErrorB=@ErrorB-@MaxErrors end
print '表示回退de回退数:'
print @ErrorB
end
else
begin goto lastwhill end set @Errors=@ErrorA+@ErrorB+@ErrorC --
insert into #Workflow values(@workflowinstance_id,@Errors)
print 'insert #workflow:'
set @ErrorA=0
set @ErrorB=0
set @ErrorC=0
set @Errors=0
lastwhill:
close workflow_cur
set @CurrProcessNum=@CurrProcessNum-1FETCH NEXT FROM CaseID_cur INTO @workflowinstance_id
END
close CaseID_cur
DEALLOCATE CaseID_cur
DEALLOCATE workflow_cur
(
select workflow_instance_id,i.fid,i.state,i.receive_type_id,i.receive_object_id, d.activity_name,i.finished_date
from szum2007_dev.dbo.ty_wf_ex_local_activity_instance i
inner join szum2007_dev.dbo.ty_wf_ex_local_activity_extend d on i.workflow_activity_id= d.activity_id
inner join szum2007_dev.dbo.szum_event E on E.current_activity=i.workflow_instance_id
where start_case_time >= @StartDate and (end_case_time <= @EndDate or end_case_time is null )
and( i.receive_type_id =100603 or state=100505)
)h
order by workflow_instance_id,fid
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'idx_TmpEvent_unit') DROP INDEX idx_TmpEvent_unit on #Event_Unit
create index idx_TmpEvent_unit on #Event_Unit(workflow_instance_id,receive_object_id,state)
--给临时表建立索引
declare CaseID_cur INSENSITIVE cursor for select workflow_instance_id from #Event_Unit for READ ONLY --外游标
内部判断性游标
declare CaseID_cur INSENSITIVE cursor for select workflow_instance_id from #Event_Unit for READ ONLY