AttendanceDetails

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