#使用技巧#考勤数据同步shr考勤记录

栏目：s-hr cloud知识作者：金蝶来源：金蝶云社区发布：2024-09-17浏览：1

#使用技巧#考勤数据同步shr考勤记录

一、业务背景

考勤同步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) ";

}