聽說幸福…在山的那邊 (浪機子)

USE [DB_B]

/****** Object:  StoredProcedure [dbo].[SP_Upload_FUZZY]    Script Date: 2017/2/18 下午 02:06:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 20170217
-- Description: 
-- EXEC [SP_Upload_FUZZY]
-- a.會設定變動的table為哪些，針對當日新增的table寫入FileControl 控制
-- b.將資料由 DB_A copy DB_B
-- c.將欄位去識別化(useid & username)
-- d.更新FileControl 控制檔
-- =============================================
ALTER PROCEDURE [dbo].[SP_Upload_FUZZY]
AS
BEGIN
 SET NOCOUNT ON;
    DECLARE @TABLE_NAME nvarchar(100)
    DECLARE @SQL nvarchar(max)
 DECLARE @IsExist char(1)
 DECLARE @IsErrFlag char(1)
 DECLARE @spname  varchar(50)
 DECLARE @ERROR int
 DECLARE @ROWCOUNT int
 DECLARE @ERROR_MESSAGE nvarchar(4000)
 SET @spname='SP_Upload_FUZZY'
 SET @IsErrFlag = 'N'

 INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'BEGIN **********************' , '0' , '' , ''

 --**** Insert FileControl ****************************************************************************************

--找出尚未寫入控制檔的table，表示其為本日新增的table
 INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'Insert FileControl BEGIN' , '0' , '' , ''
 INSERT DB_B.dbo.FileControl
 SELECT top 10 a.TABLE_NAME,'N' IsUpload, getdate() LogDate
 FROM DB_A.INFORMATION_SCHEMA.Tables  a
 LEFT JOIN DB_B.dbo.FileControl b on a.TABLE_NAME=b.TABLE_NAME
    where  a.TABLE_CATALOG ='DB_A' and  left(a.TABLE_NAME,5) = 'usr_0'  and b.TABLE_NAME is NULL  
    order by TABLE_NAME
 
 INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'Insert FileControl END' , '0' , '' , ''
 --***************************************************************************************************************

 --**** Check FileControl ****************************************************************************************
 INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'CHECK FileControl BEGIN' , '0' , '' , ''
 Declare cur_TABLE Cursor for   
 select top 2 TABLE_NAME  from FileControl   where IsUpload = 'N' and  len(TABLE_NAME) >= 10 order by TABLE_NAME
 Open cur_TABLE
 Fetch next from cur_TABLE
 Into @TABLE_NAME
   
 while (@@fetch_status = 0)
   begin 

      INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'Copy File=' + @TABLE_NAME , '0' , '' , ''
     
   --將DB_A的資料copy 到DB_B過來
   SET @SQL = ' SELECT * into  [DB_B].[dbo].' + @TABLE_NAME
   SET @SQL = @SQL + ' FROM  [DB_A].[dbo].' + @TABLE_NAME
     
   INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'Copy ' + @TABLE_NAME + ' START', '0' , '' , @SQL  
   BEGIN TRY
          EXEC (@SQL) 
          INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'Copy ' + @TABLE_NAME + ' END', '0' , '' , ''  
         SET @IsErrFlag = 'N'
   END TRY
   BEGIN CATCH
      SET @IsErrFlag = 'Y'
      SET @ERROR = @@ERROR
      SET @ROWCOUNT = @@ROWCOUNT
      SET @ERROR_MESSAGE = ERROR_MESSAGE()
      INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'Copy ' + @TABLE_NAME + ' ERROR', @ERROR , @ERROR_MESSAGE , '' 
   END CATCH 
  
   if  @IsErrFlag = 'N'
   begin      
     
    --從剛新建的table中，判斷是不是有欄位叫客戶姓名，若有則要清空
    SET @SQL = 'SELECT Case When count(*) > 0 then''Y'' else ''N'' end IsExist  '
    SET @SQL = @SQL + ' FROM INFORMATION_SCHEMA.COLUMNS  '
    SET @SQL = @SQL + ' WHERE TABLE_NAME = ''' + @TABLE_NAME + ''' and COLUMN_NAME  =''user_name''   '  
   
    INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'SET user_name BEGIN', '0' , '' , @SQL    
    SET @IsExist = 'N'
    declare @t table(IsExist varchar(1))
    insert into @t EXEC (@SQL)
    select @IsExist=IsExist from @t    

    INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'SET user_name IsExist=' + @IsExist, '0' , '' , ''         
    if  @IsExist = 'Y'
    begin
     SET @SQL = '   UPDATE a '
     SET @SQL = @SQL + ' SET user_name = '''' '
     SET @SQL = @SQL + ' From  [DB_B].[dbo].' + @TABLE_NAME + ' a '      

     INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'SET user_name START' , '0' , '' , @SQL
     BEGIN TRY
        EXEC (@SQL) 
        INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'SET user_name END', '0' , '' , ''  
        SET @IsErrFlag = 'N'
     END TRY
     BEGIN CATCH
        SET @IsErrFlag = 'Y'
        SET @ERROR = @@ERROR
        SET @ROWCOUNT = @@ROWCOUNT
        SET @ERROR_MESSAGE = ERROR_MESSAGE()
        INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'SET user_name ERROR', @ERROR , @ERROR_MESSAGE , '' 
     END CATCH
    end

  --從剛新建的table中，判斷是不是有欄位叫客戶id，若有則要清空
    SET @SQL = 'SELECT Case When count(*) > 0 then''Y'' else ''N'' end IsExist  '
    SET @SQL = @SQL + ' FROM INFORMATION_SCHEMA.COLUMNS  '
    SET @SQL = @SQL + ' WHERE TABLE_NAME = ''' + @TABLE_NAME + ''' and COLUMN_NAME  =''cust_id''   '

    INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'SET cust_id BEGIN', '0' , '' , @SQL    
    SET @IsExist = 'N'
    declare @t1 table(IsExist varchar(1))
    insert into @t1 EXEC (@SQL)
    select @IsExist=IsExist from @t1    
       
    INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'SET cust_id IsExist=' + @IsExist, '0' , '' , '@IsErrFlag=' + @IsErrFlag  
    if  @IsExist = 'Y'  and @IsErrFlag = 'N'
    begin

     --fn_FUZZY為函式，是說將F123456789 傳回F120XXXXXX
     SET @SQL = '   UPDATE a '
     SET @SQL = @SQL + ' SET cust_id = Case When len(cust_id) > 8 then [dbo].[fn_FUZZY](''1'',''1'',cust_id) else cust_id end '
     SET @SQL = @SQL + ' From  [DB_B].[dbo].' + @TABLE_NAME + ' a '   
    
     INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'SET cust_id START', '0' , '' , @SQL
     BEGIN TRY
        EXEC (@SQL) 
        INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'SET cust_id END', '0' , '' , ''  
        SET @IsErrFlag = 'N'
     END TRY
     BEGIN CATCH
        SET @IsErrFlag = 'Y'
        SET @ERROR = @@ERROR
        SET @ROWCOUNT = @@ROWCOUNT
        SET @ERROR_MESSAGE = ERROR_MESSAGE()
        INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'SET cust_id ERROR', @ERROR , @ERROR_MESSAGE , ''     
     END CATCH
    end

    --更新上傳記號
    INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'SET IsUpload  BEGIN', '0' , '' , '@IsErrFlag=' + @IsErrFlag     
    if  @IsErrFlag = 'N'
    begin
    SET @SQL = ' UPDATE   DB_B.dbo.FileControl  SET IsUpload = ''2'' WHERE TABLE_NAME=''' + @TABLE_NAME +''' '    
    INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'SET IsUpload  START', '0' , '' , @SQL
     BEGIN TRY
        EXEC (@SQL) 
        INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'SET IsUpload  END', '0' , '' , ''  
        SET @IsErrFlag = 'N'
     END TRY
     BEGIN CATCH
        SET @IsErrFlag = 'Y'
        SET @ERROR = @@ERROR
        SET @ROWCOUNT = @@ROWCOUNT
        SET @ERROR_MESSAGE = ERROR_MESSAGE()
     INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'SET IsUpload  ERROR', @ERROR , @ERROR_MESSAGE , ''     
     END CATCH
   end
   end   

  Fetch next from cur_TABLE
  Into @TABLE_NAME
   end 
 
  close cur_TABLE
  deallocate cur_TABLE 
 
  INSERT INTO BatchLog(DscptMain , DscptSub , Error , ErrorMsg, SQL ) SELECT @spname , 'FINISH **********************' , '0' , '' , '' 
  --***************************************************************************************************************  
END