考勤数据同步作业

--思路
  由于用户数据库外网不能访.采用由用户推送数据到OA 
  解决方案: 由用户数据库发送考勤数据到考勤接口
        步骤 1.在用户数据库新增一个和用户考勤记录表一样的表(为了不污染用户原本系统数据库.)
             2.将原本数据表的数据导入到新的考勤表
             3.将考勤数据同步到OA 
             
   实现:
       1.在用户数据库新增一个和用户考勤记录表一样的表
                       
                CREATE TABLE [dbo].[oa_checkinout](
                    [id] [int] IDENTITY(1,1) NOT NULL,
                    [checktime] [datetime] NOT NULL,
                    [checktype] [nvarchar](5) NULL,
                    [verifycode] [int] NULL,
                    [newtype] [nvarchar](3) NULL,
                    [abnormiteid] [int] NULL,
                    [schid] [int] NULL,
                    [purpose] [int] NULL,
                    [workcode] [nvarchar](10) NULL,
                    [reserved] [nvarchar](100) NULL,
                    [sn] [nvarchar](20) NULL,
                    [userid] [int] NOT NULL,
                    [isSync] int not null)--新增字段  表示是否同步
                    
        2.将原本数据表的数据导入到新的考勤表
                
            采用作业的方式每个一段时间去同步本地最新的考勤数据
            作业名称:
set identity_insert [zkeco].[dbo].[oa_checkinout]  ON
 INSERT  INTO [zkeco].[dbo].[oa_checkinout] ( id, checktime, checktype, verifycode,newtype, abnormiteid,schid,purpose,workcode,reserved,SN, userid, isSync)
 SELECT c.id, c.checktime, c.checktype, c.verifycode,c.newtype, c.abnormiteid,c.schid,c.purpose,c.workcode,c.reserved,c.SN, c.userid, 0
  FROM [zkeco].[dbo].[checkinout] c        LEFT  OUTER JOIN userinfo u ON  c.userid=u.userid  WHERE  c.checktime >= DATEADD(day, -10, CONVERT(VARCHAR(100), GETDATE(), 23))
 AND NOT EXISTS (SELECT  id, checktime, checktype, verifycode,newtype, abnormiteid,schid,purpose,workcode,reserved,SN, userid, isSync FROM [zkeco].[dbo].[oa_checkinout]
   WHERE id=c.id AND c.checktime=checktime AND c.userid=userid )
        
        3.将考勤数据同步到OA 
        
        DECLARE @ServiceUrl AS VARCHAR(1000)
 -- 考勤API接口地址全路径
DECLARE @UrlAddress VARCHAR(500)
SET @UrlAddress = 'http://om1.24om.com/tengji/Common/API/HRAttendance.ashx'
  ----这里需要修改
 --地址
DECLARE @Object AS INT
    -- sp_OACreate 的物理地址
DECLARE @ResponseText AS VARCHAR(8000)
  --返回的结果信息
DECLARE @Body AS VARCHAR(MAX)
  --post 的数据
DECLARE @temp TABLE
    (
      id INT ,
      empno VARCHAR(50) ,
      empname VARCHAR(50) ,
      checktime DATETIME
    )
--表变量  需要根据实际情况修改
--以下参数对应WebService中4个参数的[参数名]  不能修改 
DECLARE @type VARCHAR(4) ,
    @productkey VARCHAR(10) ,
    @hard VARCHAR(4) ,
    @log VARCHAR(3) ,
    @empnoPrefix VARCHAR(4)
SET @type = 'type'
--来源类型  zk 或 dh
SET @productkey = 'productkey'
--产品密钥
SET @hard = 'hard'
--HR系统考勤机ID
SET @log = 'log'
--考勤数据字符串拼接
SET @empnoPrefix = ''
--人员编号前缀 
--以下参数对应WebService中4个参数的[参数名的值]  根据实际情况修改
DECLARE @type_value VARCHAR(10) ,
    @productkey_value VARCHAR(100) ,
    @hard_value VARCHAR(10) ,
    @log_value VARCHAR(MAX)
SET @type_value = 'zk'
SET @productkey_value = '54afd009-9be6-4f01-975e-38dfecd84d5a'  
SET @hard_value = '1'
SET @log_value = ''
------考勤相关变量---------

DECLARE @pageSize INT
  --分页大小 
SET @pageSize = 1000
DECLARE @pageIndex INT
  --当前
SET @pageIndex = 0
DECLARE @dWKTime DATETIME
 --考勤时间  

--生成LOG  
DECLARE @id INT ,
    @userid INT ,
    @checktime DATETIME ,
    @badgenumber NVARCHAR(50) ,
    @user_name NVARCHAR(50) 

--创建游标
DECLARE cur CURSOR
FOR
    SELECT  c.id, c.checktime, c.userid,u.badgenumber,u.name
    FROM    [zkeco].[dbo].[oa_checkinout] c
             LEFT JOIN [zkeco].[dbo].[userinfo] u ON c.userid=u.userid
    WHERE   isSync = 0
            AND checktime <= GETDATE()
            AND checktime >= DATEADD(day, -30,  CONVERT(VARCHAR(100), GETDATE(), 23))
    ORDER BY c.checktime 
OPEN cur
FETCH NEXT  FROM cur  INTO @id,  @checktime,@userid,@badgenumber,@user_name
WHILE ( @@fetch_status = 0 ) 
    BEGIN        
             --(1)生成所需要的数据 userID  dWKTime
        SET @dWKTime = @checktime

        IF @badgenumber IS NOT NULL
            AND @userID > 0 
            BEGIN
                
                        SET @log_value = @log_value
                            + CONVERT(VARCHAR(100), @badgenumber) + '|'
                            + CONVERT(VARCHAR(100), @dWKTime, 120) + ';'
                        INSERT  INTO @temp
                                ( id ,
                                  empno ,
                                  empname ,
                                  checktime
                                )
                        VALUES  ( @id ,
                                  @badgenumber , -- empno - varchar(50)
                                  @user_name , -- empname - varchar(50)
                                  @checktime
                                )
                        SET @pageSize = @pageSize + 1
            --分页
                        IF @pageIndex = @pageSize 
                            BEGIN                 
                                SET @ServiceUrl = @UrlAddress 
                                SET @Body = @type + '=' + @type_value + '&'
                                    + @productkey + '=' + @productkey_value
                                    + '&' + @hard + '=' + @hard_value + '&'
                                    + @log + '=' + @log_value  

                                EXEC sp_OACreate 'MSXML2.ServerXMLHTTP',
                                    @Object OUT;
                                EXEC sp_OAMethod @Object, 'open', NULL, 'post',
                                    @ServiceUrl, 'false'
                                EXEC sp_OAMethod @Object, 'setRequestHeader',
                                    NULL, 'Content-Type',
                                    'application/x-www-form-urlencoded'
                                EXEC sp_OAMethod @Object, 'send', NULL, @Body
                                EXEC sp_OAMethod @Object, 'responseText',
                                    @ResponseText OUTPUT
                                EXEC sp_OADestroy @Object                        
                        --更新同步状态
                                IF LTRIM(RTRIM(@ResponseText)) = '0' 
                                    BEGIN    
                                    ----成功状态成功后的回调操作 开始  
                                        UPDATE  [zkeco].[dbo].[oa_checkinout]
                                        SET     [zkeco].[dbo].[oa_checkinout].isSync = 1
                                        FROM    @temp AS t
                                        WHERE   [zkeco].[dbo].[oa_checkinout].id = t.id
                                            
                                                AND [zkeco].[dbo].[oa_checkinout].checktime = t.checktime
                                        DELETE  @temp
                                    ----成功状态成功后的回调操作 结束
                                    END
                                --初始化数据                                  
                                SET @log_value = ''
                                SET @pageIndex = 0    
                                                    
                            END 
            --分页结束                 
            END 
         
        FETCH NEXT FROM cur INTO @id,  @checktime,@userid,@badgenumber,@user_name
    END
CLOSE cur        
DEALLOCATE cur
--log结束
IF @log_value != '' 
    BEGIN
        SET @ServiceUrl = @UrlAddress 
        SET @Body = @type + '=' + @type_value + '&' + @productkey + '='
            + @productkey_value + '&' + @hard + '=' + @hard_value + '&' + @log
            + '=' + @log_value
        EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;
        EXEC sp_OAMethod @Object, 'open', NULL, 'post', @ServiceUrl, 'false'
        EXEC sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type',
            'application/x-www-form-urlencoded'
        EXEC sp_OAMethod @Object, 'send', NULL, @Body
        EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
        EXEC sp_OADestroy @Object
--更新同步状态
        IF LTRIM(RTRIM(@ResponseText)) = '0' 
            BEGIN
                ----成功状态成功后的回调操作 开始  
                UPDATE  [zkeco].[dbo].[oa_checkinout]
                                        SET     [zkeco].[dbo].[oa_checkinout].isSync = 1
                                        FROM    @temp AS t
                                        WHERE   [zkeco].[dbo].[oa_checkinout].id = t.id
                                               
                                                AND [zkeco].[dbo].[oa_checkinout].checktime = t.checktime
                DELETE  @temp
            ----成功状态成功后的回调操作 结束
            END
    END