1. Background introduction

In order to achieve the goal of employee leave, overtime and other personnel process flow and attendance automation, it is necessary to dock the personnel process in OA with EHR system, so that employees only need to fill in application forms, and enter EHR system automatically after approval for attendance settlement.In this system docking, the OA system provider is Blue Ling Technology, and the EHR system provider is Tongxin Technology.This system docking scope: personnel leave process, overtime process, card-filling process, employee turnover process.

2. Program Combing

This system integration takes the form of intermediate tables as follows:

2.1 Link an OA database to an EHR database using the following linking methods: Link Database.

Sketch here

2.2 Write the personnel process data to the intermediate table.

An intermediate table is added to the vacation process, overtime process, card filling process and quitting process to store actual business data such as vacation, overtime, etc.The process is initiated in the OA system, audited, and written to the intermediate table at the end of the process.The OA system ensures the correctness of intermediate table data, so strict control is required in process checking. Attendance Process Check Secondly, in case of employee's incorrect application form, there should also be a perfect disposal method to add the process of attendance exception report in OA to handle attendance problems such as absence requests and faulty card repairs.

2.2.1 Overtime Intermediate Table

The EHR system calculates overtime based on the worker's schedule, so you only need to know the employee's identification card number and the date and hours of overtime. The middle table here is the data generated by the overtime application process. After entering the EHR system, it will be used to compare with the punch-in data. Generally, small values are used as the number of overtime hours for the final attendance settlement, such as sheets.Three actually overtime 3 hours, but apply for 4 hours, then take 3 hours as the data of attendance settlement at the time of settlement.Of course, other fields can be added to the intermediate table, depending on the actual situation.

2.2.2 Leave Intermediate Table

The EHR system calculates leave times based on the employees'shifts, so only the type of leave, start time and end time are required here.

2.2.3 Card Intermediate Table

The EHR system calculates leave times based on the employees'shifts, so only the type of leave, start time and end time are required here.

2.2.4 Intermediate Table for Departure

The EHR system calculates leave times based on the employees'shifts, so only the type of leave, start time and end time are required here.

2.3 EHR takes data from the intermediate table view and writes it to the EHR system.

Use the intermediate tables of the OA system to generate the corresponding views, and then write the stored procedures for synchronizing the intermediate table data in the EHR system, as follows:

CREATE proc [dbo].[OAsj1910]
 as 
----OA Signature card synchronization data interface--- 
IF (SELECT object_id('tempdb..#tkqqk'))>0 
DROP TABLE #tkqqk
select * into #tkqqk from (select distinct fd_buKaShiJian,fd_shiBieKaHao,fd_xingMing,fd_gongZuoRiQi,fd_buKaLeiXing,fd_buKaYuanYin
 from  　[kqoa].[ekpa].[dbo].[ekp_personnel_additional_hr] where fd_buKaShiJian>=getdate()-120)a

insert into t_hr_patchcard
(Time,id,EmpID,WorkDate,Sequence,ReasonID,PatchType,IsOverTime,AppStatus,Remark,CreatorCode,
Creator,CreateTime,ModifierCode,Modifier,ModifyTime,ApproverCode,Approver,ApproveTime,Attachment)
select   distinct t.fd_bukashijian,
((select case when max(id) is null then 1 else max(id)+1 end from t_hr_patchcard )+ ROW_NUMBER() over (order by e.code)) as id, 
e.id as empid,null,null,
case when t.fd_buKaYuanYin=1 then 1 when t.fd_buKaYuanYin=3 then 3 else 4 end ,
case when t.fd_bukaleixing=1 then 0 else 1 end, 
null,1,null,'OA','OA synchronization',getdate(),null,null,null,null,null,null,null  
  from  t_hr_employee e,#tkqqk t 
where e.code=t.fd_shibiekahao
and not exists(select 1 from t_hr_patchcard k where k.EmpID = e.ID and  k.Time =t.fd_bukashijian )

----OA Overtime application process interface--
insert into t_hr_daychange
(ChangeDay,ID,EmpID,AppStatus,Remark,CreatorCode,Creator,CreateTime,
ModifierCode,Modifier,ModifyTime,ApproverCode,Approver,ApproveTime,sqjb)
select  distinct CONVERT(varchar,k.fd_jiabansuoshuriqi,23) AS ChangeDay , 
((select case when max(id) is null then 1 else max(id)+1 end from t_hr_daychange )+ ROW_NUMBER() over (order by e.code)) as id,
e.id as empid, 
1,null,'OA','OA synchronization',getdate(),null,null,null,null,null,null,sum(cast(k.fd_jiabanxiaoshishu as numeric(10,1)))
from T_HR_Employee e,[kqoa].[ekpa].[dbo].[ekp_personnel_overtime_hr]  k where e.code=k.fd_shibiekahao
and  not exists(select 1 from t_hr_daychange t where t.EmpID = e.ID 
and CONVERT(varchar(100), t.ChangeDay, 23) =CONVERT(varchar(100), k.fd_jiabansuoshuriqi, 23))
group by e.id,CONVERT(varchar,k.fd_jiabansuoshuriqi,23),e.code

----OA Leave Process Interface--

IF (SELECT object_id('tempdb..#tkqqj1'))>0 
DROP TABLE #tkqqj1
select * into #tkqqj1 from (select distinct fd_qingJiaKaiShiShiJian,fd_shiBieKaHao,fd_xingMing,
fd_qingJiaLeiXing,fd_qingJiaJieShuShiJian,fd_qingjiashishu
 from  [kqoa].[ekpa].[dbo].[ekp_personnel_leave_hr] 
 where fd_qingJiaKaiShiShiJian>=getdate()-180 )a

insert into  t_hr_absence
(BeginTime,ID,EmpID,AbsenceDay,BeginDate,EndDate,EndTime,IsMianKa,IsContinuous,TotalHour,
TypeID,AppStatus,Remark,CreatorCode,Creator,CreateTime,ModifierCode,Modifier,ModifyTime,
ApproverCode,Approver,ApproveTime,Attachment,PBeginDate,PEndDate,PBeginTime,PEndTime,
PTotalHour,LeaveStatus,OutTime,ReturnTime)
select  distinct k.fd_qingjiakaishishijian ,((select case when max(id) is null then 1 else max(id)+1 end from t_hr_absence )+ ROW_NUMBER() over (order by e.code)) as id,
e.id as empid,
null,null,null,k.fd_qingjiajieshushijian,1,null,k.fd_qingjiashishu,
case when k.fd_qingjialeixing=20 then 11 
     when k.fd_qingjialeixing=21 then 8 
	 when k.fd_qingjialeixing=1  then 1 
	 when k.fd_qingjialeixing=2  then 2 
	 when k.fd_qingjialeixing=3  then 3 
	 when k.fd_qingjialeixing=4  then 4 
	 when k.fd_qingjialeixing=5  then 10 
	 when k.fd_qingjialeixing=6  then 12 
	 when k.fd_qingjialeixing=7  then 6 
	 when k.fd_qingjialeixing=8  then 13 
	 when k.fd_qingjialeixing=9  then 14 
	 when k.fd_qingjialeixing=10  then 9 
	 when k.fd_qingjialeixing=23  then 7 
	 when k.fd_qingjialeixing=24  then 15 
	 else 5 end,
1,'','OA','OA synchronization',getdate(),'OA','OA synchronization',getdate(),'OA','OA synchronization',getdate(),null,null,null,null,null,null,0,null,null
from t_hr_employee e,#tkqqj1 k where e.code=k.fd_shibiekahao
--and k.fd_shibiekahao='2046149'
and not exists(select 1 from t_hr_absence t where t.EmpID = e.ID 
and t.BeginTime=k.fd_qingjiakaishishijian and t.Endtime= k.fd_qingjiajieshushijian)

DROP TABLE #tkqqj1   

Next, you need to write a timed task to execute the stored procedure at a certain time. sqlserver set timer task

After the above three steps, the process data such as absence, overtime and card filling can be displayed in the foreground interface of EHR. When the EHR system calculates attendance, it will process the data synchronized with OA and the data of staff scheduling and card filling, and finally get the employee attendance report.