declare @AttCollectLogId uniqueidentifier
declare @DepartmentId uniqueidentifier
set @AttCollectLogId=newid()
--1、导入刷卡数据
INSERT INTO [AttendanceCollect]
([AttendanceCollectId]
,[MachineId]
,[MachineCode]
,[CardId]
,[CardCode]
,[EmployeeName]
,[EmployeeCode]
,[EmployeeId]
,[OwnerId]
,[DepartmentName]
,[DepartmentId]
,[Date]
,[Time]
,[IsManual]
,[Flag]
,ApproveResultId
,[StateId]
,IsForAttendance
,AttendanceCollectLogId)
select [AttendanceCollectId]=newid()
,[MachineId]=[Machine].MachineId
,[MachineCode]=cardinfo.clockid
,[CardId]=[Card].CardId
,[CardCode]=[Card].CardNo
,[EmployeeName]=[Employee].CnName
,[EmployeeCode]=[Employee].Code
,[EmployeeId]=[Employee].EmployeeId
,[ownerId]=[Employee].EmployeeId
,[DepartmentName]=[Department].Name
,[DepartmentId]=[Department].DepartmentId
,[Date] = cardinfo.date+' '+cardinfo.time
,[Time] = cardinfo.time
,[IsManual] = 0
,[Flag] = '1'
,ApproveResultId='OperatorResult_001'
,StateId ='PlanState_003'
,IsForAttendance='1'
,AttendanceCollectLogId=@AttCollectLogId
from
(select * from (
select right(CONVERT(varchar(16),DATEADD(ss,a.KqTime, cast('00:00:00' AS datetime)), 121),5) time,
CONVERT(varchar(100),a.KqDate, 23) date,
'5'+cast(CardId as varchar) carid,
right(CONVERT(varchar(16), DATEADD(ss,a.KqTime, cast('00:00:00' AS datetime)), 121),5) as checktime,
a.DevID as clockid,
b.employeeid
from [192.168.3.101].[HsSmartHR170921].dbo.KQ_KqData a,employee b
where b.code=case when @DepartmentId='NB001000' then '50'+cast(CardId as varchar) else '5'+cast(CardId as varchar) end
and a.EmpSysID is not null ) as info
where not exists (
select top 1 * from
(select [CardCode],[MachineCode],[DateTime]=CONVERT(varchar(100), Date, 23)+' '+CONVERT(varchar(100), Time, 24),employeeid
from [AttendanceCollect])
as collect
where collect.employeeid=info.employeeid
and substring(CONVERT(varchar(100), collect.[DateTime], 20),1,16)=info.date+' '+info.time)
) as cardinfo
left join [Machine] on [Machine].Code = cardinfo.clockid
left join [Card] on [Card].employeeid = cardinfo.employeeid
and [Card].UseTypeId='UseType_001' and ([card].RevokeDate is null or [card].RevokeDate>=GETDATE()-2)
left join [Employee] on [Employee].employeeid= [Card].EmployeeId
left join [Department] on [Department].DepartmentId=[Employee].DepartmentId
where [Card].CardNo is not null and cardinfo.date >='2017-03-01'
--2写刷卡日志
Insert into AttendanceCollectlog(
IsManual,
AttendanceCollectlogId,
MachineTypeId,
FileSize,
CollectDate,
CollectEmployeeId,
CollectOperationDate,
CollectUserId,
CreateDate,
LastModifieddate,
Flag,
Remark
)
select
IsManual=1,
AttendanceCollectlogId=@AttCollectLogId,
MachineTypeId='90DE5165-151A-46A0-8FB2-C3C77DC82B05',
FileSize=0,
CollectDate=getdate(),
CollectEmployeeId='7F9A68EC-1C41-48DB-9D67-FED82A4EECBC',
CollectOperationDate=getdate(),
CollectUserId='b7e83f6c-f854-44d9-a9eb-de0878d5d82c',
CreateDate=getdate(),
LastModifieddate=getdate(),
Flag=1,
Remark='同步时间:'+ convert(varchar(100),getdate(),120)
这是全部的
但是又报这个错 已以用户 NT AUTHORITY\SYSTEM 的身份执行。 将字符串转换为 uniqueidentifier 时失败。 [SQLSTATE 42000] (错误 8169). 该步骤失败。
手机看不到红色部分
