use [ElephantCredit]

go

begin transaction tran_bank;

print '**脚本开始执行!';

declare @tran_error int ,

    @negRuleId uniqueidentifier

    set @tran_error = 0;

    set @negRuleId = (select Id from [ElephantCredit].[dbo].[CreditRules] where [Provider]=N'CREDIT_PROVIDER_OPS' and [Code]=N'OPS_DOCTOR_RULE_N7_N')

    begin

       declare cursor_credit cursor scroll for

            (select CreditId from [ElephantCredit].[dbo].[CreditHistories] where RuleId = @negRuleId

             group by CreditId

            )

        open cursor_credit;

        declare @CreditId uniqueidentifier,

                @FinalCredit int=0,

                @OldCredit int=0,

                @PeakCredit int=0

        fetch first from cursor_credit into @CreditId;

        while (@@fetch_status = 0)

        begin                   

                --0.calculate the final credits/ignore these negative rule records.

                set @FinalCredit = (select case when (sum(cast([value] as int)))<0 then 0 else sum(cast([value] as int)) end

                 from [ElephantCredit].[dbo].[CreditHistories] where CreditId=@CreditId and RuleId != @negRuleId)

                if (@FinalCredit is null)

                BEGIN

                   --break;

                   goto _out

                end 

                --1.update credits principal using the latest credit

                set @OldCredit = (select AvailableValue from [ElephantCredit].[dbo].[Credits] where Id=@CreditId)

                set @PeakCredit = (select PeakValue from [ElephantCredit].[dbo].[Credits] where Id=@CreditId) 

                update [ElephantCredit].[dbo].[Credits] set

                AvailableValue = @FinalCredit,

                UpdatedTime =GETDATE()

                where Id=@CreditId

                print '---creditId:'+ cast(@CreditId as nvarchar(50)) + ',重置成新积分值:'+ cast(@FinalCredit as varchar) +',旧值:' + cast(@OldCredit as varchar) + ',峰值:' + cast(@PeakCredit as varchar);

                --2.update groups principal

                update [ElephantCredit].[dbo].[Growths] set

                [Value] = @FinalCredit,

                UpdatedTime =GETDATE()

                where ExternalId=(select RefId from [ElephantCredit].[dbo].[Credits] where Id=@CreditId)

                print '---成长值同步更新完毕!' ;

                _out:

                fetch next from cursor_credit into @CreditId;  

        end

        close cursor_credit;

        deallocate cursor_credit;  

        --3.clear all the dirty data

        delete from [ElephantCredit].[dbo].[CreditHistories] where RuleId = @negRuleId

        print '清理全部脏数据完毕';

    end

if (@tran_error > 0)

    begin

        rollback tran;

        print '**脚本执行失败!已回滚';

    end

else

    begin

        commit tran;

        print '**脚本执行成功!';

    end

go

 --Helper:

 --select * from  CreditHistories

 --select * from Credits --where Id='9416FEBA-C19F-4718-80D5-F01643D57DE5'

 --select * from CreditRules