泛微OA与第三方人事系统[二]－数据脚本篇

接上文

泛微OA与第三方人事系统[一]-数据分析篇

1年前

0707

根据数据字段分析，编写数据库脚本，具体过程略

人员对接脚本如下

MERGE INTO HrmResource240104 AS target USING (
	SELECT
		R.ID,
		R.per_no,
		R.per_nm,
		R.link_tel,
		R.idcard_no,
		R.group1_no,
		R.addr,
		R.sex,
		R.jg,
		R.mz,
		R.xl,
		A.subcompanyid1,
		A.id AS departmendid,
		G.idd,
		R.ent_date,
		R.birth_date,
		R.marriage,
		R.work_date 
	FROM
		R_person AS R
		INNER JOIN hrmdepartment AS A ON R.group4_no = A.departmentcode
		INNER JOIN R_ZW AS G ON R.zw_no = G.zw_no 
	WHERE
		is_out = 'N' 
		AND group1_no IN ( '01', '06' ) 
		
	) AS source ON ( target.workcode = source.per_no ) 
	WHEN NOT MATCHED BY TARGET THEN
	INSERT (
		id,
		loginid,
		lastname,
		systemlanguage,
		mobile,
		seclevel,
		departmentid,
		subcompanyid1,
		certificatenum,
		workcode,
		STATUS,
		homeaddress,
		totalspace,
		bankid1,
		jobtitle,
		UUID,
		folk,
		nativeplace,
		accounttype,
		companystartdate,
		createdate,
		locationid,
		workstartdate,
		sex,
		birthday,
		maritalstatus 
	)
VALUES
	(
		source.ID,
		source.per_no,
		source.per_nm,
		'7',
		source.link_tel,
		'10',
		source.departmendid,
		source.subcompanyid1,
		source.idcard_no,
		source.per_no,
		'0',
		source.addr,
		'100',
		'0',
		source.idd,
		NEWID (),
		source.mz,
		source.jg,
		'0',
		source.ent_date,
		CONVERT ( CHAR ( 10 ), GETDATE (), 23 ),
		'2',
		source.work_date,
	CASE

			WHEN source.sex = '男' THEN
			'0' 
			WHEN source.sex = '女' THEN
			'1' ELSE NULL 
		END,
		source.birth_date,
	CASE

			WHEN source.marriage LIKE '%未婚%' THEN
			'0' 
			WHEN source.marriage LIKE '%已婚%' THEN
			'1' 
			WHEN source.marriage LIKE '%离异%' THEN
			'3' ELSE NULL 
		END 
		) 
		WHEN MATCHED 
		AND (
			SOURCE.per_no NOT IN ( SELECT loginid FROM HrmResource240104 )) THEN
		UPDATE 
			SET target.loginid = source.per_no,
			target.lastname = source.per_nm,
			target.systemlanguage = '7',
			target.mobile = source.link_tel,
			target.seclevel = '10',
			target.departmentid = source.departmendid,
			target.certificatenum = source.idcard_no,
			target.STATUS = '0',
			target.subcompanyid1 = source.subcompanyid1,
			target.bankid1 = '0',
			target.jobtitle = source.idd,
			target.homeaddress = source.addr,
			target.folk = source.mz,
			target.nativeplace = source.jg,
			target.accounttype = '0',
			target.companystartdate = source.ent_date,
			target.createdate = CONVERT ( CHAR ( 10 ), GETDATE (), 23 ),
			target.locationid = '2',
			target.sex =
		CASE
	
				WHEN source.sex = '男' THEN
				'0' 
				WHEN source.sex = '女' THEN
				'1' ELSE NULL 
			END,
			target.workstartdate = source.work_date,
			target.totalspace = '100',
			target.birthday = source.birth_date,
			target.maritalstatus =
		CASE
	
				WHEN source.marriage LIKE '%未婚%' THEN
				'0' 
				WHEN source.marriage LIKE '%已婚%' THEN
				'1' 
				WHEN source.marriage LIKE '%离异%' THEN
				'3' ELSE NULL 
			END,
	target.UUID = NEWID ();