读取SQL人事档案到domino数据库中

读取SQL人事档案到domino数据库中

很多时候我们想将人事的资料和DOMINO系统结合起来，但第一步就必须先读取人事档案到DOMINO数据库中，以下是我写的一点小程序，可参考。

第一步，现在人事系统中打开数据库，编写SQL语句，将人事资料中需要的字段读取出来。如下：

select a.id,a.dept as '部门编号',replace(b.longname,' ','') as '部门名称',a.code as '人事编号',a.cardno as '卡号',a.name as '姓名',a.sfz as '身份证',a.borndate as '出生日期',case a.sex when '0' then '男' when '1' then '女' end as '性别',a.pydate as '聘用日期',a.symonths as '试用期',a.zzdate as '转正日期',a.roombed as '房间号',a.ifdaka as '是否打卡',case a.state when '0' then '在职' when '9' then '离职' end as '状态',a.lzdate as '离职日期',c.name as '离职原因',a.zhiji as '职级',a.zhiwu as '职务代号',d.name as '职务名称',e.name as '学历',f.name as '婚否',g.name as '籍贯',a.g_jtzz as '家庭地址',a.eName as '英文名称',a.g_dnno as '电脑编号',a.g_bgdh as '座机',a.g_lxsj as '手机',a.g_xlt as '小灵通'from ZlEmployee a left join zldept b on a.dept=b.code left join e_lzcause c on a.lzcause=c.code left join e_zhiwu d on a.zhiwu=d.code left join e_xueli e on a.xueli=e.code left join e_hunyin f on a.hunyin=f.code left join e_jiguan g on a.jiguan=g.code

第二步：打开Domino Designer，新建一个数据库。

第三步：新建一个表单，按照SQL语句中的字段添加域，并添加一些表单的日常操作，如新增，修改，保存等。但由于是读取人事资料库的数据，所以个人建议不采用这些操作，以保证数据的一致性和准确性。

第四步：按照表单，新增一个视图。

第五步：新增一个代理，设置一下代理参数等。然后在Initialize中选择LotusScript，编写以下代码：

Sub Initialize
'每天从更新的dianhuabu中读取新宝的数据到SQL中，然后从SQL中更新dianhuaben中新宝的数据
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'ADO 数据库连接
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Const adStateOpen = 1
Const adCmdText = 1
Const adOpenStatic = 3
Const adLockOptimistic = 3
'sql数据库连接参数：数据库名、用户密码、用户名、连接名（本地用local，外地用IP）
Dim SqlDatabaseName,SqlPassword,SqlUsername,SqlLocalName,DBConStr,Conn,EmpRS,strCode
SqlDatabaseName = "txcard"
SqlPassword = "hr%admin^"
SqlUsername = "hradmin"
SqlLocalName = "192.168.20.7"
DBConStr = "Provider = Sqloledb; User ID = " & SqlUsername & "; Password = " & SqlPassword & "; Initial Catalog = " & SqlDatabaseName & "; Data Source = " & SqlLocalName & ";"
'Create and Open Connection Object
Set Conn = CreateObject("ADODB.Connection")
Conn.Open DBConStr
Set EmpRS=CreateObject("ADODB.Recordset")
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'文档定义
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim session As New notessession
Dim db As notesdatabase
Dim view As notesview
Dim doc As notesdocument
Dim doctemp As notesdocument
Set db = session.getdatabase("dwserver1/weili","dwpub人事基础.nsf")
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'删除原有数据库中的数据
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set view = db.getview("人事基础档案库")
Set doc=view.GetFirstDocument ()
If Not(doc Is Nothing) Then
While Not ( doc Is Nothing )
Set doctemp=doc
Call doc.Remove (True)
Set doc=view.GetFirstDocument ()
Wend
End If
'导入新宝的座机号码到电话本中.
SQLCmd = "select a.id,a.dept as '部门编号',replace(b.longname,' ','') as '部门名称',a.code as '人事编号',a.cardno as '卡号',a.name as '姓名',a.sfz as '身份证',a.borndate as '出生日期',case a.sex when '0' then '男' when '1' then '女' end as '性别',a.pydate as '聘用日期',a.symonths as '试用期',a.zzdate as '转正日期',a.roombed as '房间号',a.ifdaka as '是否打卡',case a.state when '0' then '在职' when '9' then '离职' end as '状态',a.lzdate as '离职日期',c.name as '离职原因',a.zhiji as '职级',a.zhiwu as '职务代号',d.name as '职务名称',e.name as '学历',f.name as '婚否',g.name as '籍贯',a.g_jtzz as '家庭地址',a.eName as '英文名称',a.g_dnno as '电脑编号',a.g_bgdh as '座机',a.g_lxsj as '手机',a.g_xlt as '小灵通'from ZlEmployee a left join zldept b on a.dept=b.code left join e_lzcause c on a.lzcause=c.code left join e_zhiwu d on a.zhiwu=d.code left join e_xueli e on a.xueli=e.code left join e_hunyin f on a.hunyin=f.code left join e_jiguan g on a.jiguan=g.code"
EmpRS.Open SQLCmd,Conn,adOpenStatic,adLockOptimistic,adCmdText
If Not EmpRs.EOf Then
EmpRS.MoveFirst
Else
Exit Sub
End If

While(Not EmpRS.EOF)
Set doc=db.createdocument
doc.Form="frmHrBase"
doc.Type="frmHrBase"
doc.id=EmpRS("id").Value
doc.部门编号=EmpRS("部门编号").Value
doc.部门名称=EmpRS("部门名称").Value
doc.人事编号=EmpRS("人事编号").Value
doc.卡号=EmpRS("卡号").Value
doc.姓名=EmpRS("姓名").Value
doc.身份证=EmpRS("身份证").Value
doc.出生日期=EmpRS("出生日期").Value
doc.性别=EmpRS("性别").Value
doc.聘用日期=EmpRS("聘用日期").Value
doc.试用期=EmpRS("试用期").Value
doc.转正日期=EmpRS("转正日期").Value
doc.房间号=EmpRS("房间号").Value
doc.是否打卡=EmpRS("是否打卡").Value
doc.状态=EmpRS("状态").Value
doc.离职日期=EmpRS("离职日期").Value
doc.离职原因=EmpRS("离职原因").Value
doc.职级=EmpRS("职级").Value
doc.职务代号=EmpRS("职务代号").Value
doc.职务名称=EmpRS("职务名称").Value
doc.学历=EmpRS("学历").Value
doc.婚否=EmpRS("婚否").Value
doc.籍贯=EmpRS("籍贯").Value
doc.家庭地址=EmpRS("家庭地址").Value
doc.英文名称=EmpRS("英文名称").Value
doc.电脑编号=EmpRS("电脑编号").Value
doc.座机=EmpRS("座机").Value
doc.手机=EmpRS("手机").Value
doc.小灵通=EmpRS("小灵通").Value
Call doc.save(True,True)
Call doc.ComputeWithForm(False,False)
EmpRS.MoveNext
Wend
EmpRS.Close
End Sub

第六步：直接运行代理即可在新建的domino数据库中视图界面。

此数据库可做一个日常调用，建议每日运行两次，保证数据的及时性和准确性。

以此数据库为基础，我们就可以开展其他的集成工作。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/54654/viewspace-425491/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/54654/viewspace-425491/