sql-tools/update.sql

IF object_id('dbo.PMergeMember','P') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.PMergeMember
END
GO


CREATE PROCEDURE dbo.PMergeMember 
(
    @SourceMemId BIGINT,
    @TargetMemId BIGINT,
    @TargetCardId BIGINT,
    @CompId BIGINT,
    @UserId BIGINT,
    @Memo VARCHAR(1024)
) 
AS
BEGIN
    DECLARE @TargetMemNo VARCHAR(50)
    DECLARE @TargetCardNo VARCHAR(50)
    DECLARE @TargetCardTypeId BIGINT --<EFBFBD>¿<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ID
    DECLARE @TargetCardCompId BIGINT --<EFBFBD>¿<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>˾
    DECLARE @MemState INT
   -- DECLARE @CardState INT
    DECLARE @Point DECIMAL(18,6)

    SELECT @MemState = FState,
           @Point = FPoint
      FROM TMember WITH(NOLOCK) WHERE FMemId = @SourceMemId 
    
    IF @@ROWCOUNT = 0 
    BEGIN
        RAISERROR('ԭ<EFBFBD><EFBFBD>ԱID<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>',16,1)
        RETURN 50000
    END
    
    IF @MemState = 3
    BEGIN
        RAISERROR('ԭ<EFBFBD><EFBFBD>Ա<EFBFBD><EFBFBD>ת<EFBFBD><EFBFBD>',16,1)
        RETURN 50000
    END


    SELECT @MemState = FState,
           @TargetMemNo = FMemNo
      FROM TMember WITH(NOLOCK) WHERE FMemId = @TargetMemId 
    IF @@ROWCOUNT = 0 
    BEGIN
        RAISERROR('<EFBFBD>»<EFBFBD>ԱID<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>',16,1)
        RETURN 50000
    END
    
    IF @MemState = 3
    BEGIN
        RAISERROR('<EFBFBD>»<EFBFBD>Ա<EFBFBD><EFBFBD>ת<EFBFBD><EFBFBD>',16,1)
        RETURN 50000
    END
    
    IF @SourceMemId = @TargetMemId
    BEGIN
        RAISERROR('ԭ<EFBFBD><EFBFBD>Ա<EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ա<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ͬ',16,1)
        RETURN 50000
    END

BEGIN TRAN MergeMember

    IF @TargetCardId != 0 
    BEGIN
        SELECT @TargetCardNo = FCardNo,
               @TargetCardTypeId = FTypeId,
               @TargetCardCompId = FCompId
          FROM TMemberCard WITH(NOLOCK) 
         WHERE FCardId = @TargetCardId
         
        IF @@ROWCOUNT = 0
        BEGIN
            RAISERROR('<EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD>ID<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>',16,1)
            GOTO _ERROR
        END
      --  SELECT @TargetBalance = FBalance FROM TMemberCardAccount WITH(NOLOCK) WHERE FCardId = @TargetCardId AND FAcctId = @TargetBalance
    END
    ELSE
    BEGIN        SELECT *

          INTO #SourceCard 
          FROM TMemberCard AS C WITH(NOLOCK)  
         WHERE C.FMemId = @SourceMemId
           AND EXISTS(SELECT 1 FROM TMemberCardAccount AS A WITH(NOLOCK) WHERE A.FCardId = C.FCardId AND (A.FBalance != 0 OR A.FArrear != 0))
           
        DECLARE @Count INT
        SELECT @Count = @@ROWCOUNT
        IF @Count > 0 
        BEGIN
            --<EFBFBD>Զ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>һ<EFBFBD>Ŵ<EFBFBD>ֵ<EFBFBD><EFBFBD>
            IF EXISTS(SELECT 1 FROM TMemberCard WITH(NOLOCK) WHERE FMemId = @TargetMemId)
            BEGIN 
                RAISERROR('<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ѡ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD>',16,1)
                GOTO _ERROR
            END
            ELSE
            BEGIN
                IF @Count != 1
                BEGIN 
                    RAISERROR('<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ѡ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD>',16,1)
                    GOTO _ERROR
                END
                
                SELECT @TargetCardId = dbo.FNNewId()
                SELECT @TargetCardNo = @TargetMemNo
                UPDATE #SourceCard 
                   SET FCardid = @TargetCardId,
                       FMemId = @TargetMemId,
                       FCardNo = @TargetMemNo,
                       FCardFaceNo = @TargetMemNo
                INSERT INTO TMemberCard(FCardId,FCardNo,FCardFaceNo,FTypeId,FValueType,FCompId,FMemId,FState,FCreateDate,FExpiredDate,FTotalTimes,FUsedTimes,FMemo,FSaleCompId,FPaySmsFee)
                SELECT FCardId,FCardNo,FCardFaceNo,FTypeId,FValueType,FCompId,FMemId,FState,FCreateDate,FExpiredDate,FTotalTimes,FUsedTimes,FMemo,FSaleCompId,FPaySmsFee
                  FROM #SourceCard
            END
        END
    END
    
    CREATE TABLE #Account
    (
        FAcctId BIGINT,
        FBalance DECIMAL(18,6),
        FArrear DECIMAL(18,6),
        FCreateDate DATETIME,
        FExpiredDate DATETIME,
        FMemo VARCHAR(1024)
    )
    

    --ɾ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ظ<EFBFBD><EFBFBD><EFBFBD>
    DELETE TMemberMerge
     WHERE FSourceMemId = @SourceMemId 
       AND FTargetMemId = @TargetMemId

    DELETE TMemberMergeDetail 
     WHERE FSourceMemId = @SourceMemId 
       AND FTargetMemId = @TargetMemId
       
    SELECT FCardId,FCardNo,FTypeId
      INTO #Card
      FROM TMemberCard WITH(NOLOCK) 
     WHERE FMemId = @SourceMemId 

    --<EFBFBD><EFBFBD>ת<EFBFBD><EFBFBD>ֵ<EFBFBD><EFBFBD>
    DECLARE @CardId BIGINT
    DECLARE @CardNo VARCHAR(50)
    DECLARE @CardTypeId BIGINT
    DECLARE CardCur CURSOR FOR
	SELECT FCardId,FCardNo,FTypeId FROM #Card
	
	OPEN CardCur
	FETCH CardCur INTO @CardId,@CardNo,@CardTypeId
	WHILE @@FETCH_STATUS = 0
	BEGIN
        INSERT #Account(FAcctId,FBalance,FArrear,FCreateDate,FExpiredDate,FMemo)
        SELECT FAcctId,FBalance,FArrear,FCreateDate,FExpiredDate,FMemo
          FROM TMemberCardAccount AS C WITH(NOLOCK)
         WHERE FCardId = @CardId
           AND (FBalance != 0 OR FArrear != 0)
        IF @@ERROR > 0
        BEGIN
            GOTO _ERROR
        END
    
        --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>¿<EFBFBD><EFBFBD>˻<EFBFBD>
        UPDATE A
           SET A.FBalance = A.FBalance + B.FBalance,
               A.FDeposit = A.FDeposit + B.FBalance,
               A.FArrear = A.FArrear + B.FArrear
          FROM TMemberCardAccount AS A ,#Account AS B
         WHERE A.FCardId = @TargetCardId
           AND A.FAcctId = B.FAcctId
        IF @@ERROR > 0
        BEGIN
            GOTO _ERROR
        END
    
        INSERT TMemberCardAccount(FId,FMemId,FCardId,FAcctId,FDeposit,FBalance,FArrear,FCreateDate,FExpiredDate,FMemo)
        SELECT dbo.FNNewId(),@TargetMemId,@TargetCardId,A.FAcctId,A.FBalance,A.FBalance,A.FArrear,A.FCreateDate,A.FExpiredDate,A.FMemo
          FROM #Account AS A
         WHERE NOT EXISTS(SELECT 1 FROM TMemberCardAccount AS B WITH(NOLOCK) WHERE @TargetCardID = B.FCardId AND B.FAcctId = A.FAcctId)
        
        IF @@ERROR > 0 GOTO _Error
    
        --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>Ͽ<EFBFBD><EFBFBD>˻<EFBFBD>
        UPDATE A 
           SET A.FBalance = 0,
               A.FArrear = 0
          FROM TMemberCardAccount AS A
         WHERE A.FCardId = @CardId
        IF @@ERROR > 0
        BEGIN
            GOTO _ERROR
        END
        
	    FETCH CardCur INTO @CardId,@CardNo,@CardTypeId
	END
	CLOSE CardCur
    DEALLOCATE CardCur
    
    --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
    IF ISNULL(@Point,0) != 0
    BEGIN
        UPDATE TMember WITH(ROWLOCK) SET FPoint = FPoint + @Point
         WHERE FMemId = @TargetMemId
        IF @@ERROR > 0 GOTO _Error
    END
    
    --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʷ
    INSERT TMemberMergeDetail(FId,FSourceMemId,FSourceCardId,FTargetMemId,FDetailType,FDetailId)
    SELECT dbo.FNNewId(),@SourceMemId,FCardId,@TargetMemId,'PointHist',FId
      FROM TMemberPointHist WITH(NOLOCK)
     WHERE FMemId = @SourceMemId 
    IF @@ERROR > 0 GOTO _Error
     
    UPDATE TMemberPointHist
       SET FMemId = @TargetMemId,
           FCardId = @TargetCardId
     WHERE FMemId = @SourceMemId
    IF @@ERROR > 0 GOTO _Error
     
    --<EFBFBD>Ƴ<EFBFBD>
    INSERT TMemberMergeDetail(FId,FSourceMemId,FSourceCardId,FTargetMemId,FDetailType,FDetailId)
    SELECT dbo.FNNewId(),@SourceMemId,0,@TargetMemId,'Course',FId
      FROM TMemberCourse WITH(NOLOCK)
     WHERE FMemId = @SourceMemId
    IF @@ERROR > 0 GOTO _Error

    UPDATE A
       SET A.FMemId = @TargetMemId
      FROM TMemberCourse AS A
     WHERE A.FMemId = @SourceMemId
    IF @@ERROR > 0 GOTO _Error

    --<EFBFBD><EFBFBD>¼<EFBFBD>Ƴ<EFBFBD><EFBFBD><EFBFBD>ʷ
    INSERT TMemberMergeDetail(FId,FSourceMemId,FSourceCardId,FTargetMemId,FDetailType,FDetailId)
    SELECT dbo.FNNewId(),@SourceMemId,0,@TargetMemId,'CourseHist',FId
      FROM TMemberCourseHist WITH(NOLOCK)
     WHERE FMemId = @SourceMemId
    IF @@ERROR > 0 GOTO _Error

    UPDATE A
       SET A.FMemId = @TargetMemId
      FROM TMemberCourseHist AS A
     WHERE A.FMemId = @SourceMemId
    IF @@ERROR > 0 GOTO _Error

    --<EFBFBD>ײ<EFBFBD>
    INSERT TMemberMergeDetail(FId,FSourceMemId,FSourceCardId,FTargetMemId,FDetailType,FDetailId)
    SELECT dbo.FNNewId(),@SourceMemId,0,@TargetMemId,'CoursePack',FId
      FROM TMemberCourseHist WITH(NOLOCK)
     WHERE FMemId = @SourceMemId
    IF @@ERROR > 0 GOTO _Error

    UPDATE A
       SET A.FMemId = @TargetMemId
      FROM TMemberCoursePack AS A
     WHERE A.FMemId = @SourceMemId 
    IF @@ERROR > 0 GOTO _Error


    --<EFBFBD>Ƴ̲<EFBFBD>Ʒ
    INSERT TMemberMergeDetail(FId,FSourceMemId,FSourceCardId,FTargetMemId,FDetailType,FDetailId)
    SELECT dbo.FNNewId(),@SourceMemId,0,@TargetMemId,'CourseGoods',FId
      FROM TMemberCourseHist WITH(NOLOCK)
     WHERE FMemId = @SourceMemId
    IF @@ERROR > 0 GOTO _Error

    UPDATE A
       SET A.FMemId = @TargetMemId
      FROM TMemberCourseGoods AS A
     WHERE A.FMemId = @SourceMemId
    IF @@ERROR > 0 GOTO _Error


    --<EFBFBD><EFBFBD>¼<EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD>ʷ
    INSERT TMemberMergeDetail(FId,FSourceMemId,FSourceCardId,FTargetMemId,FDetailType,FDetailId)
    SELECT dbo.FNNewId(),@SourceMemId,FCardId,@TargetMemId,'CardAcctHist',FId
      FROM TMemberCardAcctHist WITH(NOLOCK)
     WHERE FMemId = @SourceMemId
    IF @@ERROR > 0 GOTO _Error

    UPDATE TMemberCardAcctHist  
       SET FCardId = @TargetCardId,
           FMemId = @TargetMemId
     WHERE FMemId = @SourceMemId
    IF @@ERROR > 0 GOTO _Error


    --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʷ
    INSERT TMemberMergeDetail(FId,FSourceMemId,FSourceCardId,FTargetMemId,FDetailType,FDetailId)
    SELECT dbo.FNNewId(),@SourceMemId,FCardId,@TargetMemId,'TransHist',FId
      FROM TMemberTransHist WITH(NOLOCK)
     WHERE FMemId = @SourceMemId
    IF @@ERROR > 0 GOTO _Error

    UPDATE TMemberTransHist  
       SET FCardId = @TargetCardId,
           FMemId = @TargetMemId
     WHERE FMemId = @SourceMemId
    IF @@ERROR > 0 GOTO _Error

    ----<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʷ֧<EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʽ
    --INSERT TMemberMergeDetail(FId,FSourceMemId,FSourceCardId,FTargetMemId,FDetailType,FDetailId)
    --SELECT dbo.FNNewId(),@SourceMemId,FCardId,@TargetMemId,'TransHistPayment',FId
    --  FROM TMemberTransHistPayment WITH(NOLOCK)
    -- WHERE FMemId = @SourceMemId

    --UPDATE TMemberTransHistPayment  
    --   SET FCardId = @TargetCardId,
    --       FMemId = @TargetMemId
    -- WHERE FMemId = @SourceMemId
     

    --<EFBFBD><EFBFBD>ϵͳ<EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD>ʷ
    INSERT TMemberMergeDetail(FId,FSourceMemId,FSourceCardId,FTargetMemId,FDetailType,FDetailId)
    SELECT dbo.FNNewId(),@SourceMemId,FCardId,@TargetMemId,'CardOldAcctHist',FId
      FROM TMemberCardOldAcctHist WITH(NOLOCK)
     WHERE FMemId = @SourceMemId
    IF @@ERROR > 0 GOTO _Error

    UPDATE TMemberCardOldAcctHist  
       SET FCardId = @TargetCardId,
           FMemId = @TargetMemId
     WHERE FMemId = @SourceMemId
    IF @@ERROR > 0 GOTO _Error


    --<EFBFBD><EFBFBD>ϵͳ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʷ
    INSERT TMemberMergeDetail(FId,FSourceMemId,FSourceCardId,FTargetMemId,FDetailType,FDetailId)
    SELECT dbo.FNNewId(),@SourceMemId,FCardId,@TargetMemId,'OldTransHist',FId
      FROM TMemberOldTransHist WITH(NOLOCK)
     WHERE FMemId = @SourceMemId
    IF @@ERROR > 0 GOTO _Error

    UPDATE TMemberOldTransHist  
       SET FCardId = @TargetCardId,
           FMemId = @TargetMemId
     WHERE FMemId = @SourceMemId
    IF @@ERROR > 0 GOTO _Error
     
    --Ƿ<EFBFBD><EFBFBD>
    INSERT TMemberMergeDetail(FId,FSourceMemId,FSourceCardId,FTargetMemId,FDetailType,FDetailId)
    SELECT dbo.FNNewId(),@SourceMemId,FCardId,@TargetMemId,'Arrear',FId
      FROM TMemberTransHist WITH(NOLOCK)
     WHERE FMemId = @SourceMemId
    IF @@ERROR > 0 GOTO _Error

    UPDATE TMemberArrear
       SET FCardId = @TargetCardId,
           FMemId = @TargetMemId
     WHERE FMemId = @SourceMemId  
    IF @@ERROR > 0 GOTO _Error 
    
    --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
    INSERT TMemberMergeDetail(FId,FSourceMemId,FSourceCardId,FTargetMemId,FDetailType,FDetailId)
    SELECT dbo.FNNewId(),@SourceMemId,0,@TargetMemId,'Repay',FId
      FROM TMemberRepay WITH(NOLOCK)
     WHERE FMemId = @SourceMemId
    IF @@ERROR > 0 GOTO _Error

    UPDATE TMemberRepay
       SET FMemId = @TargetMemId
     WHERE FMemId = @SourceMemId  
    IF @@ERROR > 0 GOTO _Error 
     
     
    ----Ƿ<EFBFBD><EFBFBD>Ա<EFBFBD><EFBFBD>
    --INSERT TMemberMergeDetail(FId,FSourceMemId,FSourceCardId,FTargetMemId,FDetailType,FDetailId)
    --SELECT dbo.FNNewId(),@SourceMemId,0,@TargetMemId,'ArrearEmployee',FId
    --  FROM TMemberArrearEmployee WITH(NOLOCK)
    -- WHERE FMemId = @SourceMemId
     
    --UPDATE TMemberArrearEmployee
    --   SET FMemId = @TargetMemId
    -- WHERE FMemId = @SourceMemId   
    
    --<EFBFBD><EFBFBD><EFBFBD>ѵ<EFBFBD><EFBFBD><EFBFBD>
    UPDATE A
       SET A.FMemId = @TargetMemId,
           A.FMemNo = @TargetMemNo,
           A.FCardId = CASE WHEN A.FCardId != 0 THEN @TargetCardId ELSE A.FCardId END,
           A.FCardNo = CASE WHEN A.FCardId != 0 THEN @TargetCardNo ELSE A.FCardNo END
      FROM TConsumeBill AS A
     WHERE A.FMemId = @SourceMemId
    IF @@ERROR > 0 GOTO _Error
     
    --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
    UPDATE A
       SET A.FMemId = @TargetMemId,
           A.FMemNo = @TargetMemNo,
           A.FCardId = CASE WHEN A.FCardId != 0 THEN @TargetCardId ELSE A.FCardId END,
           A.FCardNo = CASE WHEN A.FCardId != 0 THEN @TargetCardNo ELSE A.FCardNo END
      FROM TOrderBill AS A
     WHERE A.FMemId = @SourceMemId 
    IF @@ERROR > 0 GOTO _Error   
     
    --ԤԼ
  /*  UPDATE A
       SET A.FMemId = @TargetMemId,
           A.FMemNo = @TargetMemNo
      FROM TBookingRequest AS A
     WHERE A.FMemId = @SourceMemId  
    IF @@ERROR > 0 GOTO _Error  
         */
         
      
    --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
    UPDATE A
       SET A.FMemId = @TargetMemId,
           A.FMemNo = @TargetMemNo,
           A.FPayCardId = CASE WHEN A.FPayCardId != 0 THEN @TargetCardId ELSE A.FPayCardId END,
           A.FPayCardNo = CASE WHEN A.FPayCardId != 0 THEN @TargetCardNo ELSE A.FPayCardNo END
      FROM TMemberSaleBill AS A
     WHERE A.FMemId = @SourceMemId     
    IF @@ERROR > 0 GOTO _Error
     
    UPDATE A
       SET A.FCardId = @TargetCardId,
           A.FCardNo = @TargetCardNo,
           A.FCardFaceNo = @TargetCardNo
      FROM TMemberSaleCard AS A WITH(ROWLOCK),#Card AS B
     WHERE A.FCardId = B.FCardId
    IF @@ERROR > 0 GOTO _Error
     
    UPDATE A
       SET A.FCardId = @TargetCardId,
           A.FCardNo = @TargetCardNo
      FROM TMemberSaleExchange AS A WITH(ROWLOCK),#Card AS B
     WHERE A.FCardId = B.FCardId
    IF @@ERROR > 0 GOTO _Error
     
   
    --<EFBFBD><EFBFBD><EFBFBD>¼<EFBFBD><EFBFBD><EFBFBD><EFBFBD>˻<EFBFBD><EFBFBD><EFBFBD>ʷ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
    EXEC PRecalcCardAccHistBalance @TargetCardId
    IF @@ERROR > 0 GOTO _Error
 
    UPDATE A
       SET A.FState = 3
      FROM TMember AS A
     WHERE A.FMemId = @SourceMemId
    IF @@ERROR > 0 GOTO _ERROR

    UPDATE A
       SET A.FState = 3
      FROM TMemberCard AS A
     WHERE A.FMemId = @SourceMemId
    IF @@ERROR > 0 GOTO _ERROR
    
    --<EFBFBD><EFBFBD>Ա<EFBFBD>ϲ<EFBFBD><EFBFBD><EFBFBD>¼
    INSERT TMemberMerge(FId,FSourceMemId,FTargetMemId,FTargetCardId,FCreatorId,FCreateTime,FMemo,FAutoGen)
    SELECT dbo.FNNewId(),@SourceMemId,@TargetMemId,@TargetCardId,@UserId,GETDATE(),@Memo,0
    IF @@ERROR > 0 GOTO _ERROR

    
    --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ص<EFBFBD>Ҳ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
    INSERT TMemberMerge(FId,FSourceMemId,FTargetMemId,FTargetCardId,FCreatorId,FCreateTime,FMemo,FAutoGen)
    SELECT dbo.FNNewId(),FSourceMemId,@TargetMemId,@TargetCardId,@UserId,GETDATE(),@Memo,1
      FROM TMemberMerge 
     WHERE FTargetMemId = @SourceMemId
     
    IF @@ERROR > 0 GOTO _ERROR
    
    --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ص<EFBFBD>Ҳ<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
    INSERT TMemberMergeDetail(FId,FSourceMemId,FSourceCardId,FTargetMemId,FDetailType,FDetailId)
    SELECT dbo.FNNewId(),FSourceMemId,FSourceCardId,@TargetMemId,FDetailType,FDetailId
      FROM TMemberMergeDetail 
     WHERE FTargetMemId = @SourceMemId

    IF @@ERROR > 0 GOTO _Error
    
    GOTO _OK
        
    
_ERROR:
    ROLLBACK TRAN MergeMember
    RETURN -1

_OK:
    COMMIT TRAN MergeMember
    EXEC PSetEmpPerfCacheExpired 1
    RETURN 0

END
GO
IF object_id('dbo.PRecoveryMember','P') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.PRecoveryMember
END
GO


CREATE PROCEDURE dbo.PRecoveryMember 
(
    @MemId BIGINT,
    @NewMemNo VARCHAR(50),
    @NewCard VARCHAR(7000)
)
AS
BEGIN

BEGIN TRAN RecoveryMember
    --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
    UPDATE A
       SET A.FMemNo = @NewMemNo
      FROM TOrderBill AS A
     WHERE A.FMemId = @MemId 
    IF @@ERROR != 0 GOTO _Error
    
    --ԤԼ
   /* UPDATE A
       SET A.FMemNo = @NewMemNo
      FROM TBookingRequest AS A
     WHERE A.FMemId = @MemId 
    IF @@ERROR != 0 GOTO _Error
*/
    --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
    UPDATE A
       SET A.FMemNo = @NewMemNo
      FROM TMemberSaleBill AS A
     WHERE A.FMemId = @MemId
    IF @@ERROR != 0 GOTO _Error

    --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
    UPDATE A
       SET A.FMemNo = @NewMemNo
      FROM TConsumeBill AS A
     WHERE A.FMemId = @MemId
    IF @@ERROR != 0 GOTO _Error

    --<EFBFBD><EFBFBD>Ա
    UPDATE A
       SET A.FMemNo = @NewMemNo,
           A.FDeleted = 0
      FROM TMember AS A
     WHERE A.FMemId = @MemId 
    IF @@ERROR != 0 GOTO _Error

    --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
    DECLARE @CardId BIGINT
    DECLARE @NewCardNo VARCHAR(50)

    DECLARE CardCur CURSOR FOR
     SELECT FCardId 
       FROM TMemberCard WITH(NOLOCK)
      WHERE FMemId = @MemId
    IF @@ERROR != 0 GOTO _Error

    OPEN CardCur
    FETCH CardCur INTO @CardId
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @NewCardNo = dbo.FNGetParamValue(@NewCard,@CardId)
        IF ISNULL(@NewCardNo,'') = ''
        BEGIN
            RAISERROR('û<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>´<EFBFBD>ֵ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>',16,1)
            GOTO _Error
        END
        
        UPDATE A
           SET A.FCardNo = @NewCardNo
          FROM TOrderBill AS A
         WHERE A.FCardId = @CardId 
        IF @@ERROR != 0 GOTO _Error

        UPDATE A
           SET A.FCardNo = @NewCardNo
          FROM TConsumeBill AS A
         WHERE A.FCardId = @CardId 
        IF @@ERROR != 0 GOTO _Error

        UPDATE A
           SET A.FPayCardNo = @NewCardNo
          FROM TMemberSaleBill AS A
         WHERE A.FPayCardId = @CardId 
        IF @@ERROR != 0 GOTO _Error

        UPDATE A
           SET A.FCardNo = @NewCardNo,
               A.FCardFaceNo = @NewCardNo
          FROM TMemberSaleCard AS A
         WHERE A.FCardId = @CardId 
        IF @@ERROR != 0 GOTO _Error

        UPDATE A
           SET A.FCardNo = @NewCardNo,
               A.FCardFaceNo = @NewCardNo,
               A.FDeleted = 0 
          FROM TMemberCard AS A
         WHERE A.FCardId = @CardId
        IF @@ERROR != 0 GOTO _Error

        FETCH CardCur INTO @CardId
    END
 
    DECLARE @Error INT

_Ok:
    COMMIT TRAN RecoveryMember
    SET @Error = 0
    GOTO _End
    
_Error:
    ROLLBACK TRAN RecoveryMember
    SET @Error = -1
    GOTO _End

_End:
    EXEC PDeleteCursor 'CardCur'
    print @Error
    RETURN @Error
END
GO



IF object_id('dbo.PSoftDeleteMember','P') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.PSoftDeleteMember
END
GO


CREATE PROCEDURE dbo.PSoftDeleteMember 
(
    @MemId BIGINT
)
AS
BEGIN
    DECLARE @OldMemNo VARCHAR(50)
    DECLARE @MemNo VARCHAR(50)
    DECLARE @Cnt INT 
    SET @Cnt = 1
    
    SELECT @MemNo = FMemNo FROM TMember WITH(NOLOCK) WHERE FMemId = @MemId
    IF @@ROWCOUNT = 0 
    BEGIN
        RETURN
    END
    
    SELECT @OldMemNo = @MemNo
    
    IF @MemNo NOT LIKE '%<EFBFBD><EFBFBD>ɾ%'
    BEGIN
        SELECT @MemNo = @MemNo + '<EFBFBD><EFBFBD>ɾ';
    END
    ELSE 
    BEGIN
        SELECT @MemNo = LEFT(@MemNo,CHARINDEX('<EFBFBD><EFBFBD>ɾ',@MemNo)+1)
    END

    DECLARE @NewMemNo VARCHAR(100)
    SELECT  @NewMemNo  = @MemNo  + CAST(@Cnt AS VARCHAR(10))

    WHILE(EXISTS(SELECT 1 FROM TMember WITH(NOLOCK) WHERE FMemNo = @NewMemNo))
    BEGIN
        SET @Cnt = @Cnt + 1
        SELECT @NewMemNo = @MemNo + CAST(@Cnt AS VARCHAR(10))
    END
       
    DECLARE @InTrans BIT
    IF @@TRANCOUNT = 0
    BEGIN
        SET @InTrans = 1
        BEGIN TRANSACTION SOFT_DELETE_MEMBER
    END
    ELSE
    BEGIN
        SET @InTrans = 0
    END
 
    --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
    UPDATE A
       SET A.FMemNo = @NewMemNo
      FROM TMemberSaleBill AS A
     WHERE A.FMemid = @MemId
     
    IF @@ERROR > 0
        GOTO _Error
     
    --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
    UPDATE A
       SET A.FMemNo = @NewMemNo
      FROM TConsumeBill AS A
     WHERE A.FMemid = @MemId
       
    IF @@ERROR > 0
        GOTO _Error
/*
    --ԤԼ 
    UPDATE A
       SET A.FMemNo = @NewMemNo
      FROM TBookingRequest AS A
     WHERE A.FMemid = @MemId

    IF @@ERROR > 0
        GOTO _Error
*/
    --<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
    UPDATE A
       SET A.FMemNo = @NewMemNo
      FROM TOrderBill AS A
     WHERE A.FMemid = @MemId
     
    IF @@ERROR > 0
        GOTO _Error

    --<EFBFBD><EFBFBD>Ա<EFBFBD><EFBFBD><EFBFBD><EFBFBD>
    UPDATE A
       SET A.FMemNo = @NewMemNo,
           A.FDeleted = 1
      FROM TMember AS A
     WHERE A.FMemid = @MemId
   
    IF @@ERROR > 0
        GOTO _Error

    --<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
    DECLARE @CardNo VARCHAR(50)
    DECLARE @CardId BIGINT
    
    DECLARE CardCursor CURSOR FOR SELECT FCardId,FCardNo FROM TMemberCard WITH (NOLOCK) WHERE FMemId = @MemId
    OPEN CardCursor
    FETCH CardCursor INTO @CardId,@CardNo
    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @Error INT
        EXEC @ERROR = PSoftDeleteMemberCard @CardId

        IF @ERROR > 0
            GOTO _Error
    
        FETCH CardCursor INTO @CardId,@CardNo
    END
	CLOSE CardCursor
    DEALLOCATE CardCursor
    

_Ok:
    IF @InTrans = 1
    BEGIN
        COMMIT TRANSACTION SOFT_DELETE_MEMBER
    END
    RETURN
    
_Error:
    IF @InTrans = 1
    BEGIN
        ROLLBACK TRANSACTION SOFT_DELETE_MEMBER
    END 
    RETURN
    
END
GO