--思路
由于用户数据库外网不能访.采用由用户推送数据到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