#使用技巧#考勤数据同步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) ";

                }






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

一、业务背景考勤同步shr,这个在大多数公司都存在的业务,并不陌生,在这里我详述下其实现方式,方便借鉴参考。二、关键步骤这里,我们的...
点击下载文档
分享:
确认删除?
回到顶部
客服QQ
  • 客服QQ点击这里给我发消息