alter table WorkFlowStep add CtPercent numeric(18,4) null
alter table WorkFlowStep add CtInvolved bit null

alter table [Order] add CtAmount numeric(18,2) null -- 可供分配的绩效值
alter table [Order] add CtRatio numeric(18,2) null
alter table [Order] add CtType int null
alter table [Order] add CtStatus bit null --null:未开始分配 0:未完成分配 1:已完成
go


-- Order表新增WorkFlowStepNo，用于流程当前所处步骤查询时，更容易的查询到协作者
alter table [Order] add WorkFlowStepNo int null
go

create table OrderUser
(Id int identity(1,1) not null primary key,
OrderNo nvarchar(50) not null foreign key references [Order](OrderNo),
WorkFlowStepNo int not null foreign key references WorkFlowStep(WorkFlowStepNo),
UserId int not null foreign key references [User](UserId),
UserName nvarchar(50) null,
IsMain bit not null,
IsCooperator bit not null,
CtInvolved bit not null,
CtBase numeric(18,2) null,
CtRatio numeric(18,4) null,
CtAmount numeric(18,2) null
)

go 

-- insert trigger
create trigger OrderFlowInsert
on [dbo].[OrderFlow] after insert
as
begin
	declare @WorkFlowStepNo int,@CtInvolved bit,@OrderNo nvarchar(50),@UserId int,@UserName nvarchar(50),@IsEndPoint bit
	select @WorkFlowStepNo = WorkFlowStepNo,@OrderNo=OrderNo,@UserId=UserId,@UserName=UserName from inserted
	select @CtInvolved = CtInvolved,@IsEndPoint=IsEndPoint from WorkFlowStep where WorkFlowStepNo=@WorkFlowStepNo
	set @CtInvolved=ISNULL(@CtInvolved,0)
	if @UserId is not null and @IsEndPoint!=1 
	begin
		if not exists (select UserId from OrderUser where UserId=@UserId and WorkFlowStepNo=@WorkFlowStepNo and OrderNo=@OrderNo)
		begin
			insert into OrderUser(OrderNo,WorkFlowStepNo,UserId,UserName,IsMain,IsCooperator,CtInvolved)
		values(@OrderNo,@WorkFlowStepNo,@UserId,@UserName,1,0,@CtInvolved)	
		end
		else
		begin
			update OrderUser set IsMain=1 where UserId=@UserId and WorkFlowStepNo=@WorkFlowStepNo and OrderNo=@OrderNo
		end		
	end	 
	
	update [Order] set WorkFlowStepNo=@WorkFlowStepNo where OrderNo=@OrderNo
end
go

create trigger OrderFlowUpdate
on [dbo].[OrderFlow] after update
as
begin
	declare @OrderFlowNo int, @WorkFlowStepNo int,@CtInvolved bit,@OrderNo nvarchar(50),@OldUserId int,@NewUserId int, @NewUserName nvarchar(50),@IsEndPoint bit
	select @OrderFlowNo=OrderFlowNo, @WorkFlowStepNo = WorkFlowStepNo,@OrderNo=OrderNo,@NewUserId=UserId,@NewUserName=UserName from inserted
	select @OldUserId=UserId from deleted
	select @IsEndPoint=IsEndPoint,@CtInvolved = CtInvolved from WorkFlowStep where WorkFlowStepNo=@WorkFlowStepNo
	set @CtInvolved=ISNULL(@CtInvolved,0)
	if @OldUserId!=@NewUserId and @IsEndPoint!=1
	begin		 
		delete from OrderUser where OrderNo=@OrderNo and WorkFlowStepNo=@WorkFlowStepNo and UserId=@OldUserId and IsMain=1 and IsCooperator=0				 
		
		if not exists (select UserId from OrderUser where UserId=@NewUserId and WorkFlowStepNo=@WorkFlowStepNo and OrderNo=@OrderNo)
		begin
			insert into OrderUser(OrderNo,WorkFlowStepNo,UserId,UserName,IsMain,IsCooperator,CtInvolved)
		values(@OrderNo,@WorkFlowStepNo,@NewUserId,@NewUserName,1,0,@CtInvolved)	
		end
		else
		begin
			update OrderUser set IsMain=1 where UserId=@NewUserId and WorkFlowStepNo=@WorkFlowStepNo and OrderNo=@OrderNo
		end		
	end	 
end
go 

-- process old data
update WorkFlowStep set CtInvolved=1 where IsEndPoint!=1
update WorkFlowStep set CtInvolved=0 where IsEndPoint=1

update  [Order]  set WorkFlowStepNo=
(select WorkFlowStepNo from OrderFlow a where OrderNo=[Order].OrderNo and
OrderFlowNo =(select MAX(OrderFlowNo) from OrderFlow b where OrderNo=[Order].OrderNo))

insert into OrderUser
select distinct OrderNo,OrderFlow.WorkFlowStepNo,UserId,UserName,1,0,1,null,null,null from OrderFlow inner join WorkFlowStep on OrderFlow.WorkFlowStepNo=WorkFlowStep.WorkFlowStepNo
where UserId is not null and IsEndPoint!=1

go

create index IX_OrderUser_OrderNo on OrderUser(OrderNo asc)
go
 

update configuration set SettingValue='5.0.1' where SettingName='DatabaseVersion'
 go
 
 