USE [accountmanager]
GO
/****** Object: StoredProcedure [dbo].[PersonInfoAddByXmlProc] Script Date: 2019/5/9/周四 10:47:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: hsb
-- Create date: 20190509
-- Description: 新员工门店分配导入
-- =============================================
Alter PROCEDURE [dbo].[PersonExpoerToShopUPByXmlProc]
@xmlStrHeGe xml,
@xmlStrBuHeGe xml,
@xmlStrjson xml,
@add_per int ,
@out NVARCHAR(100) OUTPUT
AS
BEGIN
BEGIN transaction
BEGIN TRY
BEGIN
DECLARE @handle INT;
DECLARE @PrepareXmlStatus INT;
DECLARE @count INT;
DECLARE @guid NVARCHAR(64)
SET @guid=replace(NEWID(), '-', '')
EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @xmlStrHeGe;
IF ( 1=1)
BEGIN
UPDATE F SET F.per_dept=C.id,F.per_shop=D.SHOPID,F.per_busi=E.Roleid
FROM OPENXML(@handle, '/ArrayOfPersonImportModel/PersonImportModel', 2)
WITH (
身份证 NVARCHAR(20),
姓名 NVARCHAR(20),
区域 NVARCHAR(200),
分店 NVARCHAR(200),
职位 NVARCHAR(200)
) A
INNER JOIN [dbo].[Base_Department_Tbl] C ON A.区域=C.deptName and ISNULL( c.del,0)=0
LEFT JOIN [dbo].[Base_Shop_Tbl] D ON A.分店=D.Shopname and ISNULL( d.del,0)=0
INNER JOIN [dbo].[Base_RoleList] E ON A.职位=E.RoleName and ISNULL( e.IsDel,0)=0
LEFT Join [dbo].[Base_Person_Tbl] F ON A.身份证=F.per_idcard AND F.DEL=0
SET @out =[OperationDB].[dbo].[GetProcOutValFunc](1,'导入成功！')
--导入日志
INSERT INTO [dbo].[PersonImportLogTbl](jsonHege,jsonBuHege,json,addper)
VALUES(@xmlStrHeGe,@xmlStrBuHeGe,@xmlStrjson,@add_per)
--时间轴
INSERT INTO [EmployeeTimeline](perId,newDept,newShop,newArea,opType,opTime,newPosition)
SELECT b.id,b.per_dept,b.per_shop,c.parentID,3,getdate(),b.per_busi
from [Base_Person_Tbl] b
left join [dbo].[Base_Department_Tbl] c on b.per_dept=c.id
where b.[route]=@guid
--插入条数
SET @count=(select COUNT(1) from [dbo].[Base_Person_Tbl] where [route]=@guid)
update [dbo].[Base_Person_Tbl] set per_code=id where [route]=@guid
SET @out =[OperationDB].[dbo].[GetProcOutValFunc](@count,'导入成功！')
END
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SET @out =[OperationDB].[dbo].[GetProcOutValFunc](-1,'操作失败，服务器内部错误,请联系管理员！[-2]！')
ROLLBACK TRANSACTION
INSERT INTO LogDB.DBO.SysProErrLogTbl values(ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(), ERROR_LINE() ,ERROR_MESSAGE(),GETDATE())
END CATCH
END
USE [accountmanager]
GO
/****** Object: StoredProcedure [dbo].[PersonInfoAddByXmlProc] Script Date: 2019/5/9/周四 10:47:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: hsb
-- Create date: 20181112
-- Description: 人事表导入
-- =============================================
ALTER PROCEDURE [dbo].[PersonInfoAddByXmlProc]
@xmlStrHeGe xml,
@xmlStrBuHeGe xml,
@xmlStrjson xml,
@add_per int ,
@out NVARCHAR(100) OUTPUT
AS
BEGIN
BEGIN transaction
BEGIN TRY
BEGIN
DECLARE @handle INT;
DECLARE @PrepareXmlStatus INT;
DECLARE @count INT;
DECLARE @guid NVARCHAR(64)
SET @guid=replace(NEWID(), '-', '')
EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @xmlStrHeGe;
IF ( 1=1)
BEGIN
INSERT INTO [Base_Person_Tbl](per_idcard,per_name,per_sex,per_age,per_birthday,per_tel,per_knowWay,per_know,per_exper,per_height,per_email,per_ancientFrom,per_nation,per_address,urgentConnectionPer,urgentPhone,per_dept,per_shop,per_busi,per_job,per_graduateTime,industryYears,per_marry,add_per,add_date,per_school,[route])
SELECT A.身份证,A.姓名
,CASE A.性别 WHEN '男' THEN 1 WHEN '女' THEN 0 else '2' END 性别
,year(getdate())-CONVERT(int, substring(A.身份证,7,4)) 年龄
,CONVERT(datetime, substring(A.身份证,7,8)) 出生日期
,A.电话号码
,CASE A.入职渠道 WHEN '网络招聘' THEN 1 WHEN '校园招聘' THEN 2 WHEN '上门招聘' THEN 3 WHEN '转介绍' THEN 4 WHEN '其他途径' THEN 5 else 0 END 入职渠道
,CASE A.学历 WHEN '博士' THEN 1 WHEN '硕士' THEN 2 WHEN '本科' THEN 3 WHEN '大专' THEN 4 WHEN '中专' THEN 7 WHEN '高中' THEN 5 WHEN '初中及以下' THEN 6 else 0 END 学历
,A.专业,A.身高,A.邮箱,A.籍贯,A.民族,A.居住地,A.紧急联系人,A.紧急联系人电话,C.id,ISNULL( D.shopID,0) shopID,E.RoleID
,A.入职时间+' 00:00:00.000' 入职时间
,A.毕业时间+' 00:00:00.000' 毕业时间
,CONVERT(int, A.行业年限) 行业年限
,CASE A.婚姻状况 WHEN '已婚' THEN 1 WHEN '未婚' THEN 0 else '2' END 婚姻状况
,@add_per 添加人
,GETDATE() 添加时间
,A.毕业学校
,@guid 来路
FROM OPENXML(@handle, '/ArrayOfPersonImportModel/PersonImportModel', 2)
WITH (
身份证 NVARCHAR(20),
姓名 NVARCHAR(20),
性别 NVARCHAR(20),
电话号码 NVARCHAR(20),
-- 大区 NVARCHAR(20),
区域 NVARCHAR(200),
分店 NVARCHAR(200),
职位 NVARCHAR(200),
入职渠道 NVARCHAR(20),
学历 NVARCHAR(20),
专业 NVARCHAR(20),
身高 NVARCHAR(20),
邮箱 NVARCHAR(20),
籍贯 NVARCHAR(20),
民族 NVARCHAR(100),
居住地 NVARCHAR(600),
紧急联系人 NVARCHAR(20),
紧急联系人电话 NVARCHAR(20),
入职时间 NVARCHAR(20),
毕业时间 NVARCHAR(20),
行业年限 NVARCHAR(20),
婚姻状况 NVARCHAR(20),
毕业学校 NVARCHAR(200)
) A
--LEFT JOIN [dbo].[Base_Department_Tbl] B ON A.大区=B.deptName
inner JOIN [dbo].[Base_Department_Tbl] C ON A.区域=C.deptName and ISNULL( c.del,0)=0
left JOIN [dbo].[Base_Shop_Tbl] D ON A.分店=D.Shopname and ISNULL( d.del,0)=0
inner JOIN [dbo].[Base_RoleList] E ON A.职位=E.RoleName and ISNULL( e.IsDel,0)=0
where A.身份证 not in (select per_idcard from [dbo].[Base_Person_Tbl] where del!=2)
SET @out =[OperationDB].[dbo].[GetProcOutValFunc](1,'导入成功！')
--导入日志
INSERT INTO [dbo].[PersonImportLogTbl](jsonHege,jsonBuHege,json,addper)
VALUES(@xmlStrHeGe,@xmlStrBuHeGe,@xmlStrjson,@add_per)
--时间轴
INSERT INTO [EmployeeTimeline](perId,newDept,newShop,newArea,opType,opTime,newPosition)
SELECT b.id,b.per_dept,b.per_shop,c.parentID,3,getdate(),b.per_busi
from [Base_Person_Tbl] b
left join [dbo].[Base_Department_Tbl] c on b.per_dept=c.id
where b.[route]=@guid
--插入条数
SET @count=(select COUNT(1) from [dbo].[Base_Person_Tbl] where [route]=@guid)
update [dbo].[Base_Person_Tbl] set per_code=id where [route]=@guid
SET @out =[OperationDB].[dbo].[GetProcOutValFunc](@count,'导入成功！')
END
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SET @out =[OperationDB].[dbo].[GetProcOutValFunc](-1,'操作失败，服务器内部错误,请联系管理员！[-2]！')
ROLLBACK TRANSACTION
INSERT INTO LogDB.DBO.SysProErrLogTbl values(ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(), ERROR_LINE() ,ERROR_MESSAGE(),GETDATE())
END CATCH
END
