USE [IMS_HR]
GO
/****** Object: StoredProcedure [dbo].[proc_Month_AttendanceMaster] Script Date: 10/25/2019 14:58:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_Month_AttendanceMaster]
@CompanyID nvarchar(max),
@YearMonth datetime
AS
BEGIN
declare @date char(7),@startdate datetime,@enddate datetime
set @date=convert(varchar(7),@YearMonth,102)
--set @date='2012.06'
set @startdate=@date+'.01'
set @enddate=dateadd(month,1,@startdate)
--2019.05.01 00:00:00-2016.6.01 00:00:00
--select @startdate ,@enddate
--查假期的
declare @start datetime
declare @end datetime
--查时间范围
declare @start0 datetime
declare @end0 datetime
set @start=@startdate;
set @end=@enddate;
set @start0=@startdate;
set @end0=@enddate;
declare @weekday char(6)
set @start=convert(datetime,convert(varchar(50),@start,23))
set @end=convert(datetime,convert(varchar(50),@end,23))
--select @start ,@end
DELETE FROM AttendanceDetails where YearMonth=@date and CompanyID IN (SELECT Id FROM [dbo].[Fn_StringSplitToTable] (
@CompanyID
,','))
--DELETE FROM AttendanceDetails where YearMonth=@date ;
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#PUB_Dimission_cache') and type='U')
BEGIN
drop table #PUB_Dimission_cache
END
CREATE TABLE #PUB_Dimission_cache(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeCode] [int] NULL,
[DimissionDate] [datetime] NULL,
[DimissionType] [int] NULL,
[CompanyID] [int] NULL)
INSERT INTO #PUB_Dimission_cache
(EmployeeCode
,DimissionDate
,CompanyID,DimissionType)
SELECT EmployeeCode
,DimissionDate
,CompanyID,DimissionType
FROM PUB_Dimission where isview=0 and DimissionDate>=@startdate and DimissionDate<@enddate;
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#Check_BusinessTripManagement_cache') and type='U')
BEGIN
drop table #Check_BusinessTripManagement_cache
END
CREATE TABLE #Check_BusinessTripManagement_cache(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeCode] [int] NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[CompanyID] [int] NULL,
--[CreateTime] [datetime] NULL
)
INSERT INTO #Check_BusinessTripManagement_cache
(EmployeeCode
,StartTime
,EndTime
,CompanyID
--,CreateTime
)
SELECT EmployeeCode
,StartTime
,EndTime
,CompanyID
--,CreateTime
FROM Check_BusinessTripManagement where isview=0 and (StartTime between @startdate and @enddate or EndTime between @startdate and @enddate);
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#Check_VacationManagement_cache') and type='U')
BEGIN
drop table #Check_VacationManagement_cache
END
CREATE TABLE #Check_VacationManagement_cache(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeCode] [int] NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[VacationsType] [int] NULL,
[CompanyID] [int] NULL,
[CreateTime] [datetime] NULL)
INSERT INTO #Check_VacationManagement_cache
(EmployeeCode
,StartTime
,EndTime
,VacationsType
,CompanyID
,CreateTime)
SELECT a.EmployeeCode
,a.StartTime
,a.EndTime
,a.VacationsType
,a.CompanyID
,null --c.SpTime
FROM Check_VacationManagement a
--inner join
--(select VacationID,max(ID) as ID from Check_VacationFlow WHERE SpPostilState=1 group by VacationID) b on a.VacationID=b.VacationID
--inner join Check_VacationFlow c on c.ID=b.ID
where a.isview=0 and (a.StartTime between @startdate and @enddate or a.EndTime between @startdate and @enddate)
and not exists (select VacationID as ID from Check_VacationFlow WHERE SpPostilState=1 and VacationID=a.VacationID);
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#Check_RepairLabelManagement_cache') and type='U')
BEGIN
drop table #Check_RepairLabelManagement_cache
END
CREATE TABLE #Check_RepairLabelManagement_cache(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeCode] [int] NULL,
[RepairLabelTime] [datetime] NULL,
[RepairLabelType] [int] NULL,
[Source] [int] NULL,
[CompanyID] [int] NULL,
[CreateTime] [datetime] NULL)
INSERT INTO #Check_RepairLabelManagement_cache
(EmployeeCode
,RepairLabelTime
,RepairLabelType
,Source
,CompanyID
,CreateTime)
SELECT EmployeeCode
,RepairLabelTime
,RepairLabelType
,Source
,CompanyID
,CreateTime
FROM Check_RepairLabelManagement where isview=0 and isState=2 and RepairLabelTime between @startdate and @enddate;
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#tpMonth_AttendanceMaster') and type='U')
BEGIN
drop table #tpMonth_AttendanceMaster
END
create table #tpMonth_AttendanceMaster (id int identity(1,1),date varchar(50),weekday char(8))
while @start<@end
begin
select @weekday=datename(weekday,@start)
if @weekday NOT IN ( 'Saturd' ,'Sunday','星期六','星期日') insert #tpMonth_AttendanceMaster(date,weekday) values(convert(varchar(50),@start,23),@weekday)
select @start=dateadd(day,1,@start)
end
-- 删除法定节假日(OA中设置)
delete #tpMonth_AttendanceMaster where date in (select convert(char(10),date,120) from IMS_PUB.IMS_OA.dbo.OA_OnWorkLeaveSet where date >=@start0 and date <@end0 and Type ='法定节假日')
-- 添加周末上班 (OA中设置)
insert #tpMonth_AttendanceMaster select convert(char(10),date,120),'周末上班' from IMS_PUB.IMS_OA.dbo.OA_OnWorkLeaveSet where date >=@start0 and date <@end0 and Type ='周末上班'
--Select * FROM #tpMonth_AttendanceMaster
---- 生成明细情况---------------------------------------------------------------------------
declare @EveryDateStart nvarchar(50) --每天的标准开始时间
declare @EveryDateEnd nvarchar(50) --每天的标准结束时间
declare @sbsj nvarchar(50) --上班时间
declare @zwfg nvarchar(50) --中午分隔时间
declare @xbsj nvarchar(50) --下班时间
--每天的标准开始
set @EveryDateStart='00:00:00'
set @EveryDateEnd='23:59:59'
set @sbsj='8:30:00'
set @zwfg='12:00:00'
set @xbsj='17:30:00'
DECLARE @vendor_CompanyID int, @vendor_EmployeeCode int, @vendor_EmployeeName nvarchar(50);
DECLARE vendor_cursor CURSOR FOR SELECT CompanyID,EmployeeCode,EmployeeName FROM PUB_EmployeeHis where IsState in (0,1) and YearMonth=@date and EmployeeCode not in (select EmployeeCode from AttendanceNoUser)
and CompanyID IN (SELECT Id FROM [dbo].[Fn_StringSplitToTable] (
@CompanyID
,','))
OPEN vendor_cursor;
FETCH NEXT FROM vendor_cursor
INTO @vendor_CompanyID, @vendor_EmployeeCode, @vendor_EmployeeName;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO AttendanceDetails
(CompanyID
,EmployeeCode
,EmployeeName
,YearMonth
,WeekDay
,FDate
,FGdsbsj
,FSjSbsj
,FSwLx
,FGdXbsj
,FSjXbsj
,FXwLx
,FMark
,FSwLxIsChiDao
,FXwLxIsChiDao)
Select @vendor_CompanyID
,@vendor_EmployeeCode
,@vendor_EmployeeName
,@date as YearMonth
,WeekDay
,date
,'08:30'
,''
,'上班未打卡'
,'17:30'
,''
,'下班未打卡'
,''
,0
,0 FROM #tpMonth_AttendanceMaster
FETCH NEXT FROM vendor_cursor
INTO @vendor_CompanyID, @vendor_EmployeeCode, @vendor_EmployeeName;
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;
---- 替换生成明细情况---------------------------------------------------------------------------
--当前考勤月的时间
--以最后一新一次时间为准,
--当前时间大于等于离职时间的不计考勤,全为离职。超过离职时间的考勤都是离职
UPDATE AttendanceDetails
SET FSjSbsjRec=StartTime,FSjSbsjCreate=CreateTime,FSjSbsj = (case
when VacationsType=613 then '' --'病假'
when VacationsType=609 then '' --'产假'
when VacationsType=615 then '08:30' --'调休'
when VacationsType=610 then '08:30' --'婚假'
when VacationsType=611 then '08:30' --'年假'
when VacationsType=614 then '08:30' --'丧假'
when VacationsType=612 then ''--'事假'
end)
,FSwLx =(case
when VacationsType=613 then '病假'
when VacationsType=609 then '产假'
when VacationsType=615 then '调休'
when VacationsType=610 then '婚假'
when VacationsType=611 then '年假'
when VacationsType=614 then '丧假'
when VacationsType=612 then '事假'
end)
,FMark =''
,FSwLxIsChiDao=0 FROM AttendanceDetails a CROSS APPLY
(
select top 1 (case when datediff(dd,StartTime,a.FDate)=0 then StartTime else a.FDate+' '+@sbsj end) as StartTime,VacationsType,CreateTime from #Check_VacationManagement_cache b
where a.EmployeeCode=b.EmployeeCode and a.CompanyID=b.CompanyID
and (
(Convert(datetime,a.FDate+' '+@EveryDateStart)<StartTime and StartTime< Convert(datetime,a.FDate+' '+@zwfg))
or (Convert(datetime,a.FDate+' '+@EveryDateStart)<EndTime and EndTime< Convert(datetime,a.FDate+' '+@zwfg))
or (StartTime<=Convert(datetime,a.FDate+' '+@EveryDateStart) and Convert(datetime,a.FDate+' '+@EveryDateStart)<=EndTime
and StartTime<=Convert(datetime,a.FDate+' '+@zwfg) and Convert(datetime,a.FDate+' '+@zwfg)<=EndTime)
)
and (StartTime between @startdate and @enddate or EndTime between @startdate and @enddate)
and (a.FSjSbsjCreate is null or a.FSjSbsjCreate<b.CreateTime)
order by CreateTime desc
) c
UPDATE AttendanceDetails
SET FSjSbsjRec=(case when datediff(dd,StartTime,a.FDate)=0 then StartTime else a.FDate+' '+@sbsj end),FSjSbsjCreate=NULL,FSjSbsj = '08:30'
,FSwLx ='出差'
,FMark =''
,FSwLxIsChiDao=0 FROM AttendanceDetails a
CROSS APPLY
(
select top 1 StartTime from #Check_BusinessTripManagement_cache b
where a.EmployeeCode=b.EmployeeCode and a.CompanyID=b.CompanyID
and (
(Convert(datetime,a.FDate+' '+@EveryDateStart)<=StartTime and StartTime<= Convert(datetime,a.FDate+' '+@zwfg))
or (Convert(datetime,a.FDate+' '+@EveryDateStart)<=EndTime and EndTime<= Convert(datetime,a.FDate+' '+@zwfg))
or (StartTime<=Convert(datetime,a.FDate+' '+@EveryDateStart) and Convert(datetime,a.FDate+' '+@EveryDateStart)<=EndTime and StartTime<=Convert(datetime,a.FDate+' '+@zwfg) and Convert(datetime,a.FDate+' '+@zwfg)<=EndTime)
)
and (StartTime between @startdate and @enddate or EndTime between @startdate and @enddate)
) c
UPDATE AttendanceDetails
SET FSjSbsjRec=c.RepairLabelTime,FSjSbsjCreate=NULL,FSjSbsj =convert(varchar,c.RepairLabelTime,24)
,FSwLx =(case
when c.Source=657 then
'上班补签'
else
'上班打卡'
end)
,FMark =FMark+(case when c.RepairLabelTime>Convert(datetime,a.FDate+' '+@sbsj) then
'迟到('+convert(nvarchar(50),datediff(minute,c.RepairLabelTime,Convert(datetime,a.FDate+' '+@sbsj)))+'分钟)'
else
''
end)
,FSwLxIsChiDao=(case when c.RepairLabelTime>Convert(datetime,a.FDate+' '+@sbsj) then
1
else
0
end) FROM AttendanceDetails a
CROSS APPLY
(
select top 1 RepairLabelTime,Source
FROM #Check_RepairLabelManagement_cache b where b.EmployeeCode=a.EmployeeCode and b.CompanyID=a.CompanyID
and Convert(datetime,a.FDate+' '+@EveryDateStart)<=b.RepairLabelTime
and b.RepairLabelTime<= Convert(datetime,a.FDate+' '+@zwfg)
and b.RepairLabelTime between @startdate and @enddate
) c
UPDATE AttendanceDetails
SET FSjSbsjRec=b.DimissionDate,FSjSbsjCreate=b.DimissionDate,FSjSbsj =''
,FSwLx ='离职'
,FMark =''
,FSwLxIsChiDao=0 FROM AttendanceDetails a
INNER JOIN #PUB_Dimission_cache b
ON a.EmployeeCode=b.EmployeeCode and a.CompanyID=b.CompanyID
and a.FDate>=b.DimissionDate
UPDATE AttendanceDetails
SET FSjXbsjRec=EndTime,FSjXbsjCreate=CreateTime,FSjXbsj = (case
when VacationsType=613 then '' --'病假'
when VacationsType=609 then '' --'产假'
when VacationsType=615 then '17:30' --'调休'
when VacationsType=610 then '17:30' --'婚假'
when VacationsType=611 then '17:30' --'年假'
when VacationsType=614 then '17:30' --'丧假'
when VacationsType=612 then ''--'事假'
end)
,FXwLx =(case
when VacationsType=613 then '病假'
when VacationsType=609 then '产假'
when VacationsType=615 then '调休'
when VacationsType=610 then '婚假'
when VacationsType=611 then '年假'
when VacationsType=614 then '丧假'
when VacationsType=612 then '事假'
end)
,FMark =''
,FXwLxIsChiDao=0 FROM AttendanceDetails a CROSS APPLY
(select top 1 (case when datediff(dd,EndTime,a.FDate)=0 then EndTime else a.FDate+' '+@xbsj end) as EndTime,VacationsType,CreateTime from #Check_VacationManagement_cache b
where a.EmployeeCode=b.EmployeeCode and a.CompanyID=b.CompanyID
and (
Convert(datetime,a.FDate+' '+@zwfg)<StartTime and StartTime<(Convert(datetime,a.FDate+' '+@EveryDateEnd) )
or (Convert(datetime,a.FDate+' '+@zwfg)<EndTime and EndTime<Convert(datetime,a.FDate+' '+@EveryDateEnd) )
or (StartTime<=Convert(datetime,a.FDate+' '+@zwfg) and Convert(datetime,a.FDate+' '+@zwfg)<=EndTime
and StartTime<=Convert(datetime,a.FDate+' '+@EveryDateEnd) and Convert(datetime,a.FDate+' '+@EveryDateEnd)<=EndTime)
)
and (StartTime between @startdate and @enddate or EndTime between @startdate and @enddate)
and (a.FSjXbsjCreate is null or a.FSjXbsjCreate<b.CreateTime)
order by CreateTime desc
) c
UPDATE AttendanceDetails
SET FSjXbsjRec=(case when datediff(dd,EndTime,a.FDate)=0 then EndTime else a.FDate+' '+@xbsj end),FSjXbsjCreate=NULL,FSjXbsj = '17:30'
,FXwLx ='出差'
,FMark =''
,FXwLxIsChiDao=0 FROM AttendanceDetails a
CROSS APPLY
( select top 1 EndTime from #Check_BusinessTripManagement_cache b
where a.EmployeeCode=b.EmployeeCode and a.CompanyID=b.CompanyID
and (
(StartTime<Convert(datetime,a.FDate+' '+@zwfg) and Convert(datetime,a.FDate+' '+@zwfg)<EndTime)
or (StartTime<Convert(datetime,a.FDate+' '+@EveryDateEnd) and Convert(datetime,a.FDate+' '+@EveryDateEnd)<EndTime)
or (StartTime<=Convert(datetime,a.FDate+' '+@zwfg) and Convert(datetime,a.FDate+' '+@zwfg)<=EndTime and StartTime<=Convert(datetime,a.FDate+' '+@EveryDateEnd) and Convert(datetime,a.FDate+' '+@EveryDateEnd)<=EndTime)
)
and (StartTime between @startdate and @enddate or EndTime between @startdate and @enddate)
) c
UPDATE AttendanceDetails
SET FSjXbsjRec=c.RepairLabelTime,FSjXbsjCreate=NULL,FSjXbsj =convert(varchar,c.RepairLabelTime,24)
,FXwLx =(case
when Source=657 then
'下班补签'
else
'下班打卡'
end)
,FMark =FMark+(case when RepairLabelTime<Convert(datetime,a.FDate+' '+@xbsj) then
'早退('+convert(nvarchar(50),datediff(minute,Convert(datetime,a.FDate+' '+@xbsj),RepairLabelTime))+'分钟)'
else
''
end)
,FXwLxIsChiDao=(case when RepairLabelTime<Convert(datetime,a.FDate+' '+@xbsj) then
1
else
0
end) FROM AttendanceDetails a
CROSS APPLY
(
select top 1 RepairLabelTime,Source
FROM #Check_RepairLabelManagement_cache b where b.EmployeeCode=a.EmployeeCode and b.CompanyID=a.CompanyID
and b.RepairLabelTime <= Convert(datetime,a.FDate+' '+@EveryDateEnd)
and RepairLabelTime>= Convert(datetime,a.FDate+' '+@zwfg)
and RepairLabelTime between @startdate and @enddate
) c
UPDATE AttendanceDetails
SET FSjXbsjRec=b.DimissionDate,FSjSbsjCreate=b.DimissionDate,FSjXbsj =''
,FXwLx ='离职'
,FMark =''
,FXwLxIsChiDao=0 FROM AttendanceDetails a
INNER JOIN #PUB_Dimission_cache b
ON a.EmployeeCode=b.EmployeeCode and a.CompanyID=b.CompanyID
and a.FDate>=b.DimissionDate
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#PUB_Dimission_cache') and type='U')
BEGIN
drop table #PUB_Dimission_cache
END
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#Check_BusinessTripManagement_cache') and type='U')
BEGIN
drop table #Check_BusinessTripManagement_cache
END
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#Check_VacationManagement_cache') and type='U')
BEGIN
drop table #Check_VacationManagement_cache
END
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#Check_RepairLabelManagement_cache') and type='U')
BEGIN
drop table #Check_RepairLabelManagement_cache
END
-- 清理临时表
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#tpMonth_AttendanceMaster') and type='U')
BEGIN
drop table #tpMonth_AttendanceMaster
END
--select * FROM AttendanceDetails a CROSS APPLY
-- (
-- select top 1 StartTime,VacationsType,CreateTime from #Check_VacationManagement_cache b
-- where a.EmployeeCode=b.EmployeeCode and a.CompanyID=b.CompanyID
-- and (
-- (Convert(datetime,a.FDate+' '+@EveryDateStart)<StartTime and StartTime< Convert(datetime,a.FDate+' '+@zwfg))
-- or (Convert(datetime,a.FDate+' '+@EveryDateStart)<EndTime and EndTime< Convert(datetime,a.FDate+' '+@zwfg))
-- or (StartTime<=Convert(datetime,a.FDate+' '+@EveryDateStart) and Convert(datetime,a.FDate+' '+@EveryDateStart)<=EndTime and StartTime<=Convert(datetime,a.FDate+' '+@zwfg) and Convert(datetime,a.FDate+' '+@zwfg)<=EndTime)
--)
-- and (StartTime between @startdate and @enddate or EndTime between @startdate and @enddate)
--and (a.FSjSbsjCreate is null or a.FSjSbsjCreate<b.CreateTime)
--order by CreateTime desc
-- ) c where a.EmployeeCode=223600
SELECT [ID]
,[CompanyID]
,[EmployeeCode]
,[EmployeeName]
,[YearMonth]
,[WeekDay]
,[FDate]
,[FGdsbsj]
,[FSjSbsjRec]
,[FSjSbsjCreate]
,[FSjSbsj]
,[FSwLx]
,[FSwLxIsChiDao]
,[FGdXbsj]
,[FSjXbsjRec]
,[FSjXbsjCreate]
,[FSjXbsj]
,[FXwLx]
,[FXwLxIsChiDao]
,[FMark]
FROM [IMS_HR].[dbo].[AttendanceDetails] where YearMonth=@date and CompanyID IN (SELECT Id FROM [dbo].[Fn_StringSplitToTable] (
@CompanyID
,','))
--and EmployeeCode=223600
END
GO