--SELECT name
--FROM sys.tables 
--WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 1



/****** Object:  Index [NonClusteredIndex-20210720-154213]    Script Date: 20-10-2023 12:55:14 ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20210720-154213] ON [dbo].[TA_Tbl_DataProcessTemp]
(
	[ProcessDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
/****** Object:  Index [IDX_EmailSchedule_GetEmployeeEmail]    Script Date: 20-10-2023 12:55:14 ******/
CREATE NONCLUSTERED INDEX [IDX_EmailSchedule_GetEmployeeEmail] ON [dbo].[TA_tbl_EmailScheduleStatus]
(
	[ScheduleId] ASC,
	[Status] ASC
)
INCLUDE([SentDate],[To_Mail]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [IDX_GetEmployeeEmail]    Script Date: 20-10-2023 12:55:14 ******/
CREATE NONCLUSTERED INDEX [IDX_GetEmployeeEmail] ON [dbo].[TA_Tbl_Employee]
(
	[Employee_Status] ASC,
	[Isdeleted] ASC,
	[EmailId] ASC
)
INCLUDE([Employee_Id],[Employee_Code_Id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [IDX_EmailScheduler_GetEmployeeEmail]    Script Date: 20-10-2023 12:55:14 ******/
CREATE NONCLUSTERED INDEX [IDX_EmailScheduler_GetEmployeeEmail] ON [dbo].[TA_Tbl_ProcessedData]
(
	[Employee_Code_Id] ASC,
	[Date] ASC,
	[ProcessFlag] ASC
)
INCLUDE([Shift_Id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [NonClusteredIndex-20210720-154244]    Script Date: 20-10-2023 12:55:14 ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20210720-154244] ON [dbo].[TA_Tbl_ProcessedData]
(
	[Employee_Code_Id] ASC,
	[Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

/****** Object:  Index [ClusteredIndex-20210720-154152]    Script Date: 20-10-2023 12:55:13 ******/
CREATE CLUSTERED INDEX [ClusteredIndex-20210720-154152] ON [dbo].[TA_Tbl_DataProcessTemp]
(
	[ProcessDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]


/****** Object:  Index [ClusteredIndex-20210720-154103]    Script Date: 20-10-2023 12:55:13 ******/
CREATE CLUSTERED INDEX [ClusteredIndex-20210720-154103] ON [dbo].[Ta_Tbl_DataProcessCompanyLevelTemp]
(
	[LevelId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO


---------------------------------------------------------------------------------------------
-- AutoShift Updated Script 

ALTER PROCEDURE [dbo].[Proc_TA_GetClockDateForAutoShiftEmployee]
	@SwipeCardNo varchar(max),
	@inClk_Date datetime,
	@inClk_Time varchar(20),
	@CompanyId int
AS
BEGIN
	SET NOCOUNT ON;
	Declare @DateOfJoining datetime
	Declare @day int
	DECLARE @YEAR VARCHAR(5)
	DECLARE @MONTH VARCHAR(5)	
	Declare @EMPLOYEEAUTOID VARCHAR(50)
	DECLARE @SHIFTID INT
	Declare @PrevEndtime  Datetime	
	set @SHIFTID =0
	CREATE TABLE #TEMP (SHIFTID INT)
	Declare @flag char(1)
	set @flag='F'
	Select @DateOfJoining = DateOfJoining,@EMPLOYEEAUTOID =Employee_Id from TA_Tbl_Employee where SwipeCardNo =  @SwipeCardNo and Isdeleted=0
	if(@inClk_Date > @DateOfJoining)
	Begin
		Declare @PrevDay datetime
		Declare @PrevSatrtTime datetime
		DECLARE @QUERY VARCHAR(2000)
		Declare @WeekoffHours varchar(20)
		SET @SHIFTID=0
		SET  @PrevDay = DATEADD(d, -1, @inClk_Date)
		set @day =DATEPART(DAY,@PrevDay)
		SET @YEAR=DATEPART(YEAR,@PrevDay)
		SET @MONTH=DATEPART(MONTH,@PrevDay)
		Declare @MaxHoursAfterShift Nvarchar(100)
		select @WeekoffHours = WeeklyOffStartHours,@MaxHoursAfterShift=MaxHoursAfterShift from TA_Tbl_ShiftSettings where CompanyId = @CompanyId
		
		
		-- CHECK SHIFT IN TEMPORARY SHIFT ALLOCATION
		SET @QUERY = '	SELECT (TSA.DAY'+ CONVERT(VARCHAR(10),@day) + 'SHIFT) AS SHIFTID FROM TA_TBL_TEMPORARYSHIFTALLOCATION TSA					
						WHERE TSA.[YEAR]= ' + @YEAR + '  AND  TSA.[MONTH]= ' + @MONTH + ' AND EMPLOYEEID = '''+ @EMPLOYEEAUTOID +''' '	
		print @QUERY
		INSERT INTO #TEMP EXEC(@QUERY)
		SET @SHIFTID = (SELECT SHIFTID FROM #TEMP)
		SET @SHIFTID = ISNULL(@SHIFTID,0)
		DELETE FROM #TEMP
		Declare @WeekOffcount  int
		set @WeekOffcount=1
		-- CHECK SHIFT IN PERMANENT SHIFT ALLOCATION IF NOT IN TEMPORARY
		IF(@SHIFTID = 0)
			BEGIN
				
				SET @QUERY = '	SELECT (PSA.DAY'+ CONVERT(VARCHAR(10),@day) + 'SHIFT) AS SHIFTID FROM TA_TBL_PERMANENTSHIFTALLOCATION PSA							
								WHERE PSA.[YEAR]= ' + @YEAR + '  AND  PSA.[MONTH]= ' + @MONTH + ' AND EMPLOYEEID = ''' + @EMPLOYEEAUTOID +''''
				INSERT INTO #TEMP EXEC(@QUERY)
				SET @SHIFTID = (SELECT SHIFTID FROM #TEMP)
				SET @SHIFTID = ISNULL(@SHIFTID ,0)
				print @SHIFTID
				DELETE FROM #TEMP				
			END	
		--Check Starttime and End time for weeklyoff--
		if(@SHIFTID=-1)
		begin
			Declare @i int
			Declare @PreviousOneMoreDay datetime
			SET  @PreviousOneMoreDay = DATEADD(d,-1, @PrevDay)
			set @i=1
			while @i>0
			begin				
				  --dec 18 shift 5
				SET @QUERY = ' SELECT (PSA.DAY'+ CONVERT(VARCHAR(10),DATEPART(DAY,@PreviousOneMoreDay)) + 'SHIFT) AS SHIFTID FROM TA_TBL_PERMANENTSHIFTALLOCATION PSA
					WHERE PSA.[YEAR]= ' + @YEAR + '  AND  PSA.[MONTH]= ' + CONVERT(VARCHAR(10),DATEPART(DAY,@PreviousOneMoreDay)) + ' AND EMPLOYEEID = ''' + @EMPLOYEEAUTOID +''''
				INSERT INTO #TEMP EXEC(@QUERY)
				SET @SHIFTID = (SELECT SHIFTID FROM #TEMP)
				SET @SHIFTID = ISNULL(@SHIFTID ,0)
				DELETE FROM #TEMP
				set @WeekOffcount=@WeekOffcount+1
				if(@SHIFTID!=-1)
				begin
					set @i=-1
					break
				end				
				set @PreviousOneMoreDay=dateadd(d,-1,@PreviousOneMoreDay)
				set @i=@i+1	
			end
			if(@SHIFTID!=-1)
			begin
				Declare @Start int
				set @Start=1
				Declare @MaxShiftWorkHours datetime
				Declare @Shift_StartTime datetime
				select @Shift_StartTime=convert(datetime,Shift_StartTime),@MaxShiftWorkHours=convert(datetime,MaxShiftWorkHours)
				from TA_Tbl_Shift where Shift_Id = @SHIFTID			
				set @PrevSatrtTime= @PreviousOneMoreDay+@Shift_StartTime
				while @WeekOffcount>=@Start
				begin
					if(@Start=1)
					begin					
						set @PrevEndtime=@PrevSatrtTime+@WeekoffHours					
					end
					else
					begin
						set @PrevSatrtTime=DATEADD(minute,1,@PrevEndtime)
						set @PrevEndtime=@PrevEndtime+@WeekoffHours
					end
					--select @PrevSatrtTime,@PrevEndtime
					set @Start=@Start+1
				end			
		   End 		  
		end
		else if(@SHIFTID=0) --leave , lop days for auto shift configured employee
		begin
				IF EXISTS(select FNAttendanceCode from TA_Tbl_ProcessedData where Date = @PrevDay and Employee_Code_Id=@SwipeCardNo
				and FNAttendanceCode not in ('Present', 'Weekly Off', 'Holiday')
				and ANAttendanceCode not in ('Present', 'Weekly Off', 'Holiday'))
				Begin
					select @PrevDay = @inClk_Date
					select @PrevDay
					set @day =DATEPART(DAY,@PrevDay)
					SET @YEAR=DATEPART(YEAR,@PrevDay)
					SET @MONTH=DATEPART(MONTH,@PrevDay)	
					SET @QUERY = '	SELECT (PSA.DAY'+ CONVERT(VARCHAR(10),@day) + 'SHIFT) AS SHIFTID FROM TA_TBL_PERMANENTSHIFTALLOCATION PSA							
									WHERE PSA.[YEAR]= ' + @YEAR + '  AND  PSA.[MONTH]= ' + @MONTH + ' AND EMPLOYEEID = ''' + @EMPLOYEEAUTOID +''''
					INSERT INTO #TEMP EXEC(@QUERY)
					SET @SHIFTID = (SELECT SHIFTID FROM #TEMP)
					SET @SHIFTID = ISNULL(@SHIFTID ,0)
					DELETE FROM #TEMP
					if(@SHIFTID=-1) --for weeklyoff
					begin
						set @SHIFTID=0;
					end
					if(@SHIFTID=0)
					begin
						set @flag='T'
						goto GetShift;
					end
					goto UpdateShift_Permanent;
				End				
		end
		else
		begin
			select @PrevSatrtTime = @PrevDay+convert(datetime,Shift_StartTime),
			@PrevEndtime= @PrevDay+convert(datetime,Shift_EndTime)+convert(datetime,MaxShiftWorkHours)
			from TA_Tbl_Shift where Shift_Id = @SHIFTID
			
		end
		--For Night shift Add one day for the end date
		if(@PrevSatrtTime>=@PrevEndtime)
			begin
				set @PrevEndtime=dateadd(DAY,1,@PrevEndtime)
				
				set @PrevEndtime=DATEADD(MINUTE,dbo.[Func_TA_ConvertHoursToMinutes](@MaxHoursAfterShift),@PrevEndtime)
				
			end
		--select @PrevSatrtTime,@PrevEndtime
		IF(@inClk_Date+@inClk_Time between @PrevSatrtTime and @PrevEndtime)
		BEGIN
			if(@WeekOffcount>1)
			begin
				SELECT @PrevDay=dateadd(d,-(@WeekOffcount-1),@inClk_Date)
				set @SHIFTID=-1
				SELECT @PrevDay
			end
			else
			begin
				SELECT @PrevDay
			end
		END
		ELSE
		BEGIN
			--1.31 pm AND OUT CAME ON THE SAME DAY.	
			--Aug 1 9:30 pm to Aug 2 07:00 am	Shift name 930
			--Aug 2 03:00 pm to Aug 2 09:30 pm  Shift name 3pm
			--Aug 3 weeklyoff but still he came for work means Initial shift Id = -1
			-- Aug 3 07am Aug 3 03:00pm so new shift id will be allocated through autoshift shifts.
			select @PrevDay = @inClk_Date
			select @PrevDay
			set @SHIFTID=0;
			set @day =DATEPART(DAY,@PrevDay)
			SET @YEAR=DATEPART(YEAR,@PrevDay)
			SET @MONTH=DATEPART(MONTH,@PrevDay)	
			IF(@SHIFTID = 0)  -- It runs for current day , whatever shift id updated in permenent shift table will be taken here. If shift id not present, new shift id wil be find out
			BEGIN
				SET @QUERY = '	SELECT (PSA.DAY'+ CONVERT(VARCHAR(10),@day) + 'SHIFT) AS SHIFTID FROM TA_TBL_PERMANENTSHIFTALLOCATION PSA							
								WHERE PSA.[YEAR]= ' + @YEAR + '  AND  PSA.[MONTH]= ' + @MONTH + ' AND EMPLOYEEID = ''' + @EMPLOYEEAUTOID +''''
				INSERT INTO #TEMP EXEC(@QUERY)
				SET @SHIFTID = (SELECT SHIFTID FROM #TEMP)
				SET @SHIFTID = ISNULL(@SHIFTID ,0)
				DELETE FROM #TEMP				
			END	
			set @flag='T'
			goto GetShift;
			goto UpdateShift_Permanent;
		End		
	End	
	Else	
	Begin
		select @PrevDay = @inClk_Date
		select @PrevDay
		set @day =DATEPART(DAY,@PrevDay)
		SET @YEAR=DATEPART(YEAR,@PrevDay)
		SET @MONTH=DATEPART(MONTH,@PrevDay)	
		SET @QUERY = '	SELECT (PSA.DAY'+ CONVERT(VARCHAR(10),@day) + 'SHIFT) AS SHIFTID FROM TA_TBL_PERMANENTSHIFTALLOCATION PSA							
						WHERE PSA.[YEAR]= ' + @YEAR + '  AND  PSA.[MONTH]= ' + @MONTH + ' AND EMPLOYEEID = ''' + @EMPLOYEEAUTOID +''''
		INSERT INTO #TEMP EXEC(@QUERY)
		SET @SHIFTID = (SELECT SHIFTID FROM #TEMP)
		SET @SHIFTID = ISNULL(@SHIFTID ,0)
		DELETE FROM #TEMP
		if(@SHIFTID=-1)
		begin
			set @SHIFTID=0;
		end
		if(@SHIFTID=0)
		begin
			set @flag='T'
			goto GetShift;
		end
		goto UpdateShift_Permanent;
	End
	GetShift:
	begin
		--set @SHIFTID=0
		if(@flag='T')
		begin
			IF(@inClk_Time !='00:00' and @SHIFTID = 0 and @SHIFTID != -1)
			BEGIN
				Declare @Count int
				set @Count=0;
				Create Table #TempAutoShift(EmpId int, ShiftId int, clkDate datetime, punchtime varchar(40));
				Create Table #TempAutoShiftSingleShift(EmpId int, ShiftId int, clkDate datetime, punchtime varchar(40), shiftstartTime varchar(40), minutesdiffStart bigint, orderby int);
				insert into #TempAutoShift select isnull(EmployeeId,0),isnull(ShiftId1,0),@PrevDay,@inClk_Time from TA_Tbl_AutoShiftConfiguration where EmployeeId = @EMPLOYEEAUTOID and ShiftId1 !=0
				insert into #TempAutoShift select isnull(EmployeeId,0),isnull(ShiftId2,0),@PrevDay,@inClk_Time from TA_Tbl_AutoShiftConfiguration where EmployeeId = @EMPLOYEEAUTOID and ShiftId2 !=0
				insert into #TempAutoShift select isnull(EmployeeId,0),isnull(ShiftId3,0),@PrevDay,@inClk_Time from TA_Tbl_AutoShiftConfiguration where EmployeeId = @EMPLOYEEAUTOID and ShiftId3 !=0
				insert into #TempAutoShift select isnull(EmployeeId,0),isnull(ShiftId4,0),@PrevDay,@inClk_Time from TA_Tbl_AutoShiftConfiguration where EmployeeId = @EMPLOYEEAUTOID and ShiftId4 !=0
				insert into #TempAutoShift select isnull(EmployeeId,0),isnull(ShiftId5,0),@PrevDay,@inClk_Time from TA_Tbl_AutoShiftConfiguration where EmployeeId = @EMPLOYEEAUTOID and ShiftId5 !=0	
				select @Count = isnull(count(EmpId),0)from #TempAutoShift				
				IF(@Count = 1)
				BEGIN
					select @SHIFTID = ISNULL(ShiftId,0)	from #TempAutoShift							
				END
				--ELSE IF(@Count = 0)
				--BEGIN
				--	set @SHIFTID = 0						
				--END
				ELSE
				BEGIN
					insert into #TempAutoShiftSingleShift
					select b.EmpId,b.ShiftId,b.clkDate, b.punchtime,b.Shift_StartTime,datediff(minute,clkDate+b.Shift_StartTime,clkDate+punchtime) as Minutediff,
					RANK()over(order by diff) from
					(
						select a.EmpId,a.ShiftId,a.clkDate, a.punchtime,b.Shift_StartTime,datediff(minute,clkDate+b.Shift_StartTime,clkDate+punchtime) as Minutediff,
						case when datediff(minute,clkDate+Shift_StartTime,clkDate+punchtime)<0 then -datediff(minute,clkDate+Shift_StartTime,clkDate+punchtime)
						else datediff(minute,clkDate+Shift_StartTime,clkDate+punchtime) end as diff
						from #TempAutoShift a
						inner join TA_Tbl_Shift b on b.Shift_Id = a.ShiftId
					)as b		
					declare @loopcount int
					Declare @ShiftNewtime datetime
					set @loopcount = 1
					while (@loopcount <= 5)
					Begin		
							select @ShiftNewtime = clkDate+shiftstartTime from #TempAutoShiftSingleShift  where orderby=@loopcount	
							if(@ShiftNewtime < @PrevEndtime)
							Begin
								set @loopcount = @loopcount + 1;													
							End			
							else
							begin
								select @SHIFTID = ShiftId from #TempAutoShiftSingleShift  where orderby=@loopcount	
								break;					
							End			
					End								
				END
				set @flag='T'
				DROP TABLE #TempAutoShiftSingleShift
				DROP TABLE #TempAutoShift
			END
			ELSE
			BEGIN
				-- if employee forgot to mark clock in time and manual entry got entered for that entry means, the shift id is not updating	properly.
				--Client need to reprocess the logs once again.Implemented on 10-mar-2020. 05:54 pm			
				IF NOT EXISTS(SELECT Clk_InTime FROM TA_Tbl_ClockDetails WHERE Clk_EmpId=@SwipeCardNo and
				Clk_Date+Clk_InTime<@inClk_Date+@inClk_Time and Clk_Date=@inClk_Date)
				BEGIN
					--select 1
					set @SHIFTID=0;
					set @flag='T'
					goto GetShift;
				END				
			END
		end
	end	
	UpdateShift_Permanent:
	Begin
		if(@flag='T')
		begin
			DECLARE @DynamicQuery VARCHAR(MAX)
			SELECT @year=DATEPART(YEAR,@PrevDay),@month=DATEPART(MONTH,@PrevDay),@day=DATEPART(DAY,@PrevDay) 	
			SET @DynamicQuery =''
			--update in permenent shift allcoation table for reuse of same shift id in process data service reg auto shift configuration
			IF EXISTS(Select isnull(EmployeeId,0) from TA_Tbl_AutoShiftConfiguration where EmployeeId = @EMPLOYEEAUTOID)
			Begin
				if exists(select employeeId from TA_Tbl_PermanentShiftAllocation where month=@month and year=@year and employeeid=@EMPLOYEEAUTOID)
				begin
					SET @DynamicQuery =@DynamicQuery +'update TA_Tbl_PermanentShiftAllocation set day'+CONVERT(VARCHAR(2),@day)+'shift='+CONVERT(VARCHAR(2),@SHIFTID)+ ' where
					month='+CONVERT(VARCHAR(2),@month)+' and year='+CONVERT(VARCHAR(4),@year)+' and employeeid='+CONVERT(VARCHAR(40),@EMPLOYEEAUTOID)
				end
				else
				begin
					SET @DynamicQuery =@DynamicQuery +'insert into TA_Tbl_PermanentShiftAllocation (day'+CONVERT(VARCHAR(2),@day)+'shift,month,year,employeeid,Emp_id,dateshift)
					values('+CONVERT(VARCHAR(2),@SHIFTID)+ ','+CONVERT(VARCHAR(2),@month)+','+CONVERT(VARCHAR(4),@year)+','+CONVERT(VARCHAR(40),@EMPLOYEEAUTOID)+',1,'''+Convert(varchar(11),DATEADD(dd,-(DAY(getdate())-1),getdate()))+''')'
				end
				exec (@DynamicQuery)
			End
		end
	end
END