老考勤及HR系统人员操作

系统人员变更

[23000] [FreeTDS][SQL Server]The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.AttendanceDatas_dbo.FrameworkUser_UserID". The conflict occurred in database "Attendance", table "dbo.AttendanceDatas", column 'UserID'. (547)
[01000] [FreeTDS][SQL Server]The statement has been terminated. (3621)

-- 需要将表`AttendanceDatas`中对应的数据删除--

INSERT INTO [dbo].[FrameworkUser] ([ID], [ITCode], [Email], [Name], [CreateTime], [CreateBy], [UpdateTime], [UpdateBy], [OldEmployeeID], [EmployeeID], [OtherSuperiorID], [Password], [OnTheJob], [WorkStateID], [PersonTypeID], [SEX], [MaritalStatus], [WorkPhone], [CellPhone], [Address], [StartWorkDate], [WorkDate], [EndWorkDate], [WorkYear], [IsValid], [ParentID], [WFDefNode_ID], [CompanyID], [DepartmentID], [SubDepartmentID], [ThreeDepartmentID], [ConcurrentDepartment], [HRSecretaryLing], [IsAdjust], [IDCardNo], [CapacityType]) VALUES ('98FF5CC2-66B4-11EE-9A03-00163E2E383B', N'zhangwha', N'zhangwha@litsoft.com.cn', N'梁伟恒', '2023-10-09 21:00:10.000', N'BS-HR', '2023-10-09 21:00:11.000', N'HS-HR', NULL, N'89137', NULL, NULL, '1', '00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000001', '1', 1, N'15916200033', N'15916200033', NULL, '2023-10-09 00:00:00.000', '2024-04-10 00:00:00.000', NULL, NULL, '1', NULL, NULL, '04D4F145-F348-4510-BC78-E76AAE5037E7', '24BDE1C0-F97B-11EB-AF26-00163E03E338', '49BCB67A-BF54-11ED-B564-00163E2E383B', '49BCB735-BF54-11ED-B564-00163E2E383B', NULL, '2023-10-09 00:00:00.000', NULL, N'440402199203029178', 2);

[23000] [FreeTDS][SQL Server]The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.AttFlexTimes_dbo.FrameworkUser_UserID". The conflict occurred in database "Attendance", table "dbo.AttFlexTimes", column 'UserID'. (547)
[01000] [FreeTDS][SQL Server]The statement has been terminated. (3621)

[23000] [FreeTDS][SQL Server]The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.AttSubsidyForFoods_dbo.FrameworkUser_UserID". The conflict occurred in database "Attendance", table "dbo.AttSubsidyForFoods", column 'UserID'. (547)
[01000] [FreeTDS][SQL Server]The statement has been terminated. (3621)

[23000] [FreeTDS][SQL Server]The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.AttYearHolidays_dbo.FrameworkUser_UserID". The conflict occurred in database "Attendance", table "dbo.AttYearHolidays", column 'UserID'. (547)
[01000] [FreeTDS][SQL Server]The statement has been terminated. (3621)

98FF5CC2-66B4-11EE-9A03-00163E2E383B

删除人员

删除报错：

[23000] [FreeTDS][SQL Server]The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.LabourContracts_dbo.FrameworkUsers_UserID". The conflict occurred in database "LitSoft.HrMapDB20140225", table "dbo.LabourContracts", column 'UserID'. (547)
[01000] [FreeTDS][SQL Server]The statement has been terminated. (3621)

• 根据提示查找表dbo.LabourContracts", column 'UserID'. (547)中这列值为上表中的13451，然后删除！

至此就OK了。

关于请假审批

--根据ITCode查询出Userid   取ID值
select * from FrameworkUser where itcode='yanan'

--根据Userid、请假时间，清除请假记录
select * from AttHolidayApplies 
where userid='03F6032D-A120-11EB-AF26-00163E03E338' 
and CONVERT(varchar(10),BeginTime,120)='2023-07-13'


--清除请假记录
delete from AttHolidayApplies where id='BF9DA127-06F5-402B-81FB-E221324AF9BA'

delete from AttHolidayApplies where id='656D2E78-C3F0-4281-8459-BCFCA2E5D5B9'



--根据Userid、请假时间，清除补录记录
select * from AttAdditionalAttendances 
where userid='03F6032D-A120-11EB-AF26-00163E03E338' 
and CONVERT(varchar(10),BeginTime,120)='2023-07-13'

delete from AttAdditionalAttendances where id='3479E28B-6E8A-4DE4-B5DA-B3163011B5F1'

delete from AttAdditionalAttendances where id='26D7F12C-74AD-4BBF-B2AC-3D61D3E912C2'

--根据Userid、请假时间段，回滚考勤打卡时间
select * from AttendanceDatas 
where userid='03F6032D-A120-11EB-AF26-00163E03E338' 
and CONVERT(varchar(10),BeginTime,120)='2023-07-13'

/*
AttendanceStatusID='00000000-0000-0000-0000-000000000002',   # 状态为全天未打卡
回滚一天
*/

update AttendanceDatas set 
BeginTime=null,
EndTime=null,
AttendanceStatusID='00000000-0000-0000-0000-000000000002',  
TotalApplyTime=null,
ApplyDemo=null where id='37A2087B-7531-4F84-AB0D-026A70DB395F'