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

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

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

select a.ida.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 SqlDatabaseNameSqlPasswordSqlUsernameSqlLocalNameDBConStrConnEmpRSstrCode

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.ida.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 SQLCmdConnadOpenStaticadLockOptimisticadCmdText

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(TrueTrue)

Call doc.ComputeWithForm(FalseFalse)

EmpRS.MoveNext

Wend

EmpRS.Close

End Sub

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

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

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