update Permission set PermissionName='收支明细查询' where PermissionNo='P0402'

insert into Permission
values ('P0412','04','收支明细新增',0) 
update [User] set [Permissions]=[PERMISSIONS]+',P0412' where CHARINDEX('P0402',[Permissions])>0 or IsAdmin=1

insert into Permission
values ('P0413','04','收支明细编辑',0) 
update [User] set [Permissions]=[PERMISSIONS]+',P0413' where CHARINDEX('P0402',[Permissions])>0 or IsAdmin=1

insert into Permission
values ('P0414','04','收支明细删除',0) 
update [User] set [Permissions]=[PERMISSIONS]+',P0414' where CHARINDEX('P0402',[Permissions])>0 or IsAdmin=1

GO

create procedure BatchProcessOrderPerformance
@OrderNos nvarchar(2500),
@Spliter nvarchar(2),
@Result int output --1:未设置提成比例
as
begin 
	
	declare @ctType int
	declare @ctRatio numeric(18,2)
	declare @ctAmount numeric(18,2)
	
	select @ctType = SettingValue from  CompanyConfiguration where SettingName='PerformanceCalculationType' 
	select @ctRatio = SettingValue from  CompanyConfiguration where SettingName='PerformanceCalculationRatio'
		
	if @ctRatio=0	
	begin
		set @Result=1
		return
	end	
	
	create table #orderList(OrderNo nvarchar(50))
	declare @OrderUser table (Id int,WorkFlowStepNo int,CtPercent numeric(18,4),CtInvolved bit)
	declare @sqlString nvarchar(max)
	declare @OrderNo nvarchar(50)
	declare @OrderAmount numeric(18,2), @TotalFee numeric(18,2)
	set @sqlString = 'insert into #orderList select ''' + replace(@OrderNos,@Spliter,''' union select ''') + ''''	 
	exec(@sqlString)
	
	declare CurOrder cursor for select OrderNo from #orderList
	OPEN CurOrder
	
	FETCH NEXT FROM CurOrder INTO @OrderNo 
	
	BEGIN TRY
	
		begin tran tran_1
		
		WHILE @@FETCH_STATUS=0
		begin				
			set @OrderAmount = null
			select @OrderAmount = Amount from [Order] where OrderNo = @OrderNo
			
			if @OrderAmount is null  goto LabelNext			 
				
			if @ctType = 1
			begin
				set @ctAmount = @OrderAmount * @ctRatio / 100.0
			end
			else				
			begin
				set @TotalFee = null
				select @TotalFee = TotalFee  from OrderCost where OrderNo = @OrderNo
				if @TotalFee is null goto LabelNext
				set @ctAmount = (@OrderAmount - @TotalFee) * @ctRatio / 100.0
			end			
			
			delete @OrderUser
			
			insert into @OrderUser select a.Id,a.WorkFlowStepNo,isnull(b.CtPercent,0),isnull(b.CtInvolved,0) 
				from OrderUser a inner join WorkFlowStep b on a.WorkFlowStepNo = b.WorkFlowStepNo and a.OrderNo = @OrderNo		
			 
			update [OrderUser] set CtInvolved = (select top 1 CtInvolved from @OrderUser where WorkFlowStepNo = OrderUser.WorkFlowStepNo)
				where OrderNo = @OrderNo			 
			
			update [OrderUser] set CtBase = @ctAmount * ISNULL((select top 1 CtPercent from @OrderUser where WorkFlowStepNo = OrderUser.WorkFlowStepNo),0) /100.0,
				CtStepRatio = ROUND(isnull((select top 1 CtPercent from @OrderUser where WorkFlowStepNo = OrderUser.WorkFlowStepNo),0),2),
				CtRatio = ROUND(100.0 / (select COUNT(Id) from @OrderUser where WorkFlowStepNo = OrderUser.WorkFlowStepNo),2)
				where OrderNo = @OrderNo and CtInvolved = 1
			
			update [OrderUser] set CtAmount = CtBase * CtRatio /100.0
				where OrderNo = @OrderNo and CtInvolved = 1			 
			
			update [Order] set CtType = @ctType, CtStatus = 1, CtAmount=@ctAmount,CtRatio = @ctRatio
				where OrderNo = @OrderNo
	
			LabelNext:
			FETCH NEXT FROM CurOrder INTO @OrderNo 
		end		 
		set @Result=0
		commit tran tran_1
	end try
	begin catch
		--print ERROR_MESSAGE()
		IF @@TRANCOUNT > 0 
		ROLLBACK TRAN tran_1
	end catch	
	
	CLOSE CurOrder
	DEALLOCATE CurOrder	
	
	drop table #orderList
	
end 
go

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