一、业务背景
考勤同步shr，这个在大多数公司都存在的业务，并不陌生，在这里我详述下其实现方式，方便借鉴参考。
二、关键步骤
这里，我们的解决思路是：
第一步：从考勤数据库获取考勤数据
//连接考勤机数据库
try
{
string strcon = "";
string sql = "";
if (cboNetWorkDBType.SelectedIndex == 0) {
//Access数据库连接字符串
strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtLocalBrowse.Text + ";";
//Access数据库查询考勤数据脚本
sql = "SELECT 'LHR'& Format$(Val(IIF(ISNULL(u.badgenumber),'0',u.badgenumber)),\"0000\") & '-' & Format$(c.checktime,\"General Date\") AS 主键, " +
"CSTR(c.userid) AS 中控员工编码, " +
"'LHR'&Format$(Val(IIF(ISNULL(u.badgenumber),'0',u.badgenumber)),\"0000\") AS SHR员工编码, " +
"u.name AS 员工姓名, " +
"c.checktime AS 打卡时间, " +
"c.sn AS 打卡设备号, " +
"m.machinealias AS 打卡设备名称 " +
"from ( checkinout AS c " +
"left join userinfo AS u ON u.userid = c.userid ) " +
"left join machines AS m ON m.sn = c.sn " +
"where u.badgenumber is not null ";
if (chkInputDate.CheckState == CheckState.Checked)
{
sql = sql + " and c.checktime > #" + dtInputDate.Value.Date + "# ";
}
}
第二步：将获取到的考勤数据存储到中间表
//链接oracle数据库
OleDbConnection orclConn = null;//oracle数据库连接
//OracleConnection orclConn = null;//orcl数据库连接 新方法连接安装后不用安装oralce客户端
OleDbDataReader orcReader = null;//oracle数据库读取
try
{
string connString = "";
string querySql = "select * from ZKTEMPRECORD where 1=1 ";
if(cboTargetDBType.SelectedIndex == 0)
{
//SQLServer数据库连接字符串
connString = "Provider=SQLOLEDB.1;Connect Timeout=30;data source=" + txtTargetDB.Text + ";Initial Catalog=" + txtTargetDBName.Text + ";user id=" + txtTargetDBUserName.Text + ";pwd=" + txtTargetDBPN.Text + "";
querySql = "select * from ZKTEMPRECORD where 1=1 ";
if (chkInputDate.CheckState == CheckState.Checked)
{
querySql = querySql + " and checktime >= '" + dtInputDate.Value.Date + "' ";
}
}
else if (cboTargetDBType.SelectedIndex == 1)
{
//Oracle数据库连接字符串
connString = "Provider=MSDAORA.1;User ID=" + txtTargetDBUserName.Text + ";Password=" + txtTargetDBPN.Text + ";Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = " + txtTargetDB.Text + ")(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = " + txtTargetDBName.Text + ")))";
querySql = "select * from ZKTEMPRECORD where 1=1 ";
if (chkInputDate.CheckState == CheckState.Checked)
{
querySql = querySql + " and to_char(checktime,'yyyy-mm-dd') >= '" + dtInputDate.Value.Date.ToString("yyyy-MM-dd") + "' ";
}
}
第三步：在shr考勤档案设置好每个人对应的考勤编号
if (newDt.Rows.Count != 0)
{
Console.WriteLine("成功过滤满足条件的考勤数据" + newDt.Rows.Count+"条！");
WriteLog("成功过滤满足条件的考勤数据" + newDt.Rows.Count + "条！");
System.Environment.SetEnvironmentVariable("nls_lang", "SIMPLIFIED CHINESE_CHINA.ZHS16GBK", EnvironmentVariableTarget.Process);
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand("select * from ZKTEMPRECORD where 1=2", orclConn);
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
adapter.Fill(newDt);
adapter.Update(newDt);
adapter.Dispose();
builder.Dispose();
Console.WriteLine("成功插入SHR数据库中间表考勤数据" + newDt.Rows.Count + "条！");
WriteLog("成功插入SHR数据库中间表考勤数据" + newDt.Rows.Count + "条！");
}
第四步：通过中间表和考情档案表关联，将未同步的考勤记录写入shr考勤表
//同步临时表中的数据到EAS-HR中考勤表中
string sqlUpdate = "";
if(cboTargetDBType.SelectedIndex == 0)
{
sqlUpdate = " insert into T_HR_ATS_PUNCHCARDRECORD " +
" (FID,FAttendanceNum,FProposerID,FPunchCardTime, " +
" FPunchCardDate,FEquipmentNum,FAdminOrgUnitID,FHrOrgUnitID, " +
" FPunchCardSource,FPunchCardPlace, " +
" FName_l1,FName_l2,FName_l3,FNumber,FDescription_l1,FDescription_l2,FDescription_l3, " +
" FSimpleName,FCreatorID,FCreateTime,FLastUpdateUserID,FLastUpdateTime,FControlUnitID) " +
" select dbo.newbosid('62875335') as easid, " +
//" t.shrnumber," +
" pp.fattendancenum,p.FID,t.checktime,CONVERT(varchar(100),t.checktime,23),t.checkname," +
" pi.fpersondep," +
" p.FHROrgUnitID,5,t.checkname, " +
" null,null,null,t.tempid,null,null,null,null,(select FID from T_PM_User where FNumber = 'user'), " +
" getdate(),(select FID from T_PM_User where FNumber = 'user'),getdate(),'00000000-0000-0000-0000-000000000000CCE7AED4' as cuid " +
" from ( SELECT distinct tempid,zknumber,shrnumber,shrname,checktime,checknum,checkname FROM ZKTEMPRECORD ) t " +
" left join T_BD_Person p on p.FNumber = t.shrnumber " +
" left join T_HR_ATS_ATTENDANCEFILE as pp on pp.FPROPOSERID = p.fid " +
" left join T_HR_PERSONPOSITION pi on pi.Fpersonid = p.Fid " +
" where 1=1 and p.FID is not null " +
" and t.tempid not in (SELECT FNumber FROM T_HR_ATS_PUNCHCARDRECORD where FPUNCHCARDTIME is not null and FAttendanceNum is not null and FNumber is not null) ";
}
推荐阅读