/****** Object:  Table [dbo].[TA_Tbl_EmailScheduled_Employees]    Script Date: 18-08-2026 16:05:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TA_Tbl_EmailScheduled_Employees](
	[ScheduleId] [bigint] NULL,
	[Employee_id] [bigint] NULL
) ON [PRIMARY]
GO



/****** Object:  StoredProcedure [dbo].[Proc_TA_SelectLeaveApplicationDetails]    Script Date: 19-08-2026 15:16:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- Modified By: Manikandan.R
-- Modified Date: 28-Feb-2012
-- Reason: Filter Condition is not working 
-- =============================================
/*


exec Proc_TA_SelectLeaveApplicationDetails @EmployeeID='530',@LevelRights='100',@Employee_Code_Id=NULL,@Employee_FirstName=NULL,@LeaveApplicationStartDate='1900-01-01 00:00:00',
@LeaveApplicationEndDate='1900-01-01 00:00:00',@EmpSwipeCard=NULL,@UserGroupName='uiStaff',@ParentId=0

exec Proc_TA_SelectLeaveApplicationDetails @EmployeeID=1,@LevelRights='100',@Employee_Code_Id=NULL,@Employee_FirstName=NULL,
@LeaveApplicationStartDate='1900-01-01 00:00:00',@LeaveApplicationEndDate='1900-01-01 00:00:00',@EmpSwipeCard=NULL,@UserGroupName='SuperAdmin',@ParentId=0

*/


ALTER  procedure [dbo].[Proc_TA_SelectLeaveApplicationDetails]
	@EmployeeID int,
	@LevelRights varchar(8000) = null, 
	@Employee_Code_Id nvarchar(50) = null, 
	@Employee_FirstName varchar(50) = null, 
	@LeaveApplicationStartDate datetime = '01/01/1900', 
	@LeaveApplicationEndDate datetime = '01/01/1900' ,
	@EmpSwipeCard varchar(20),
	@UserGroupName varchar(250),
	@ParentId int
as  
BEGIN
	SET NOCOUNT ON;
	declare @sqlQuery as varchar(8000)      
	declare @sqlQuery1 as varchar(8000)
	declare @sqlQuery2 as varchar(8000) 
	declare @sqlQuery3 as varchar(8000) 
	declare @sqlQuery4 as varchar(8000) 
	declare @isReportingto as int
	select @isReportingto=ReportsBasedOn from TA_Tbl_Usergroup where groupName=@UserGroupName
	Declare @LeaveSuccessiveFromDate datetime
	Declare @LeaveSuccessiveToDate datetime
	Declare @Levelid int
	Declare @RowCount int
	DECLARE @dateFormat as int
	SET @dateFormat=(Select DateFormat from Func_TA_GetDateTimeFormat())
	set @EmpSwipeCard=SUBSTRING(@EmpSwipeCard,0,len(@EmpSwipeCard)+1)	
	create table #SwipeCardNo(SwipecardNo varchar(25))
	create table #LevelRights(LevelId int)
	create table #EmployeeIds(EmpAutoId int)
    insert into  #EmployeeIds select * from [dbo].[fn_TA_GetMappedEmployees_ReportingTo](@EmployeeID) as EmpAutoId  
    insert into  #EmployeeIds select * from [dbo].[fn_TA_GetMappedEmployees_SancAuthority](@EmployeeID) as EmpAutoId
    --select * from #EmployeeIds
	CREATE NONCLUSTERED INDEX [IDX_SwipecardNo] ON [dbo].[#SwipeCardNo]([SwipecardNo] ASC) ON [PRIMARY]
	CREATE NONCLUSTERED INDEX [IDX_LevelId] ON [dbo].[#LevelRights]([LevelId] ASC) ON [PRIMARY]
	insert into #SwipeCardNo select value from dbo.split(@EmpSwipeCard,',')
	select @RowCount=COUNT(SwipecardNo) from #SwipeCardNo
	insert into  #LevelRights select * from [dbo].[fn_TA_GetParentChild](@UserGroupName,@ParentId) as LevelId
	set @Levelid=@ParentId
	WHILE((SELECT Id from TA_tbl_LevelDetails where id=@Levelid) <> '') 
		BEGIN 
			if((select levelID from TA_tbl_LevelDetails where Id=@Levelid) <> 1)
				begin	
					set @Levelid = (select parentId from TA_tbl_LevelDetails where id=@Levelid) 
				END
			ELSE
				BEGIN
					BREAK
				END
		END
	set @ParentId=@Levelid
	if((select FromDate from ta_tbl_yearsetting where Isactive=1) is null)
		begin
			set @LeaveSuccessiveFromDate='01/01/1900'
			set @LeaveSuccessiveToDate='01/01/1900'			
		end
	else
		begin
			select @LeaveSuccessiveFromDate = FromDate from ta_tbl_yearsetting where Isactive=1	
			if (@ParentId =0 )
				begin
					select @LeaveSuccessiveToDate=  isnull(dateadd(day,(select max(NoofSuccessiveDaysinyear) from ta_tbl_shiftsettings) ,ToDate),0) 
					from ta_tbl_yearsetting where Isactive=1 
				end
			else
				begin
					select @LeaveSuccessiveToDate= isnull(dateadd(day,(select NoofSuccessiveDaysinyear from ta_tbl_shiftsettings 
					where companyId=@ParentId),ToDate),0) from ta_tbl_yearsetting where Isactive=1
				end
		end
	
	set @sqlQuery = ''      
	set @sqlQuery1 = ''  
	set @sqlQuery2 = ''
	set @sqlQuery3 = ''
	set @sqlQuery4=''
	set @sqlQuery = @sqlQuery + ' select Employee_Code_Id as employeeid,e.Employee_FirstName + '' '' + isnull(e.Employee_LastName,'''') as Employee_FirstName,ld.name,
					la.LeaveApplication_Id,la.Employee_Id,case when '+cast(@dateFormat as varchar(100))+'=106 then
					REPLACE(CONVERT(VARCHAR, Leave_ApplicationDate,'+cast(@dateFormat as varchar(100))+'),'+''' '''+','+'''-'''+') 
					else CONVERT(VARCHAR, Leave_ApplicationDate, '+cast(@dateFormat as varchar(100))+') end  AS Leave_ApplicationDate,
					case when '+cast(@dateFormat as varchar(100))+'=106 then
					REPLACE(CONVERT(VARCHAR, Leave_FromDate,'+cast(@dateFormat as varchar(100))+'),'+''' '''+','+'''-'''+') 
					else CONVERT(VARCHAR, Leave_FromDate, '+cast(@dateFormat as varchar(100))+') end
					+ '' To '' + 
					case when '+cast(@dateFormat as varchar(100))+'=106 then
					REPLACE(CONVERT(VARCHAR, Leave_ToDate,'+cast(@dateFormat as varchar(100))+'),'+''' '''+','+'''-'''+') 
					else CONVERT(VARCHAR, Leave_ToDate, '+cast(@dateFormat as varchar(100))+') end  AS Duration, '
	set @sqlQuery = @sqlQuery + ' (lt.LeaveType + '' / '' + la.Type) as LeaveType,la.Status as Status,la.NumberOfDays,la.Reason as Reason,la.Remarks as Remarks '   
	set @sqlQuery = @sqlQuery + ' from TA_Tbl_LeaveApplication la ' 
	set @sqlQuery = @sqlQuery + ' inner join  TA_Tbl_LeaveType lt  on la.LeaveType_Id = lt.LeaveType_Id ' 
	set @sqlQuery = @sqlQuery + ' inner join Ta_tbl_employee e on e.Employee_Id=la.Employee_Id '
	set @sqlQuery = @sqlQuery + ' inner join TA_Tbl_EmployeeLevel el on e.Employee_Id = el.EmployeeId '
	set @sqlQuery = @sqlQuery + ' inner join Ta_tbl_leveldetails ld on  el.LevelId=ld.id '
	set @sqlQuery = @sqlQuery + ' inner join TA_Tbl_YearSetting ys on la.YearSettings_Id=ys.YearSettings_Id '
	set @sqlQuery = @sqlQuery + ' inner join #LevelRights on #LevelRights.LevelId=ld.id '
	if(@RowCount >0)
		begin
			set @sqlQuery = @sqlQuery + ' inner join #SwipeCardNo on #SwipeCardNo.SwipecardNo=e.swipecardNo '
		end
	set @sqlQuery = @sqlQuery + ' where la.IsDeleted = 0  and e.isdeleted=0 and la.type in (''Leave'',''LOP'') and la.Leave_FromDate >= convert(datetime,''' + cast(@LeaveSuccessiveFromDate as varchar(20)) + ''',101) and la.Leave_ToDate <= convert(datetime, ''' + cast(@LeaveSuccessiveToDate as varchar(20)) + ''',101)'
	set @sqlQuery = @sqlQuery + ' and el.EffectiveDate = (select top 1 EffectiveDate from TA_Tbl_EmployeeLevel where EmployeeId = e.Employee_Id and EffectiveDate <=  la.Leave_ApplicationDate order by EffectiveDate desc) '
	If not(@Employee_Code_Id = '')
		Begin
			set @sqlQuery = @sqlQuery + ' and e.Employee_Code_Id = ''' + cast(@Employee_Code_Id as nvarchar(50)) + ''''
		End
	 if(@isReportingto > 0)
            BEGIN
               set @sqlQuery = @sqlQuery + ' and (e.Employee_ID IN (select * from #EmployeeIds)) '  
            END
	If not(@Employee_FirstName = '')
		Begin
			set @sqlQuery = @sqlQuery + ' and (e.Employee_FirstName +'' '' + isnull(e.Employee_LastName,'''') like ''' + cast(@Employee_FirstName+'%' as nvarchar(100)) + ''')'
		End
	If not(@LeaveApplicationStartDate = '01/01/1900' and @LeaveApplicationEndDate = '01/01/1900')
		Begin
			set @sqlQuery = @sqlQuery + ' and (la.Leave_FromDate between convert(datetime,''' + cast(@LeaveApplicationStartDate as nvarchar(11)) + ''', 101) and convert(datetime,''' + cast(@LeaveApplicationEndDate as nvarchar(11)) + ''',101)'
			set @sqlQuery = @sqlQuery + ' or la.Leave_ToDate between convert(datetime,''' + cast(@LeaveApplicationStartDate as nvarchar(11)) + ''', 101) and convert(datetime,''' + cast(@LeaveApplicationEndDate as nvarchar(11)) + ''',101))'
		End

		--print @sqlQuery


	set @sqlQuery1 = @sqlQuery1 + ' union '
	set @sqlQuery1 = @sqlQuery1 + '	select Employee_Code_Id as employeeid,e.Employee_FirstName + '' '' + isnull(e.Employee_LastName,'''') as Employee_FirstName ,ld.name,la.LeaveApplication_Id,la.Employee_Id,
									case when '+cast(@dateFormat as varchar(100))+'=106 then
									REPLACE(CONVERT(VARCHAR, Leave_ApplicationDate,'+cast(@dateFormat as varchar(100))+'),'+''' '''+','+'''-'''+') 
									else CONVERT(VARCHAR, Leave_ApplicationDate, '+cast(@dateFormat as varchar(100))+') end  AS Leave_ApplicationDate,
									case when '+cast(@dateFormat as varchar(100))+'=106 then
									REPLACE(CONVERT(VARCHAR, Leave_FromDate,'+cast(@dateFormat as varchar(100))+'),'+''' '''+','+'''-'''+') 
									else CONVERT(VARCHAR, Leave_FromDate, '+cast(@dateFormat as varchar(100))+') end
									+ '' To '' + 
									case when '+cast(@dateFormat as varchar(100))+'=106 then
									REPLACE(CONVERT(VARCHAR, Leave_ToDate,'+cast(@dateFormat as varchar(100))+'),'+''' '''+','+'''-'''+') 
									else CONVERT(VARCHAR, Leave_ToDate, '+cast(@dateFormat as varchar(100))+') end  AS Duration,										h.Holiday_Description as LeaveType,la.Status as Status,la.NumberOfDays,la.Reason as Reason,la.Remarks as Remarks from TA_Tbl_Holiday h ' 
	set @sqlQuery1 = @sqlQuery1 + ' inner join  TA_Tbl_LeaveApplication la on la.LeaveType_Id=h.Holiday_Id '
	set @sqlQuery1 = @sqlQuery1 + ' inner join Ta_tbl_employee e on e.Employee_Id=la.Employee_Id '  
	set @sqlQuery1 = @sqlQuery1 + ' inner join TA_Tbl_EmployeeLevel el on e.Employee_Id = el.EmployeeId '
	set @sqlQuery1 = @sqlQuery1 + ' inner join Ta_tbl_leveldetails ld on  el.LevelId=ld.id '
	set @sqlQuery1 = @sqlQuery1 + ' inner join TA_Tbl_YearSetting ys on la.YearSettings_Id=ys.YearSettings_Id '
	set @sqlQuery1 = @sqlQuery1 + ' inner join #LevelRights on #LevelRights.LevelId=ld.id '
	if(@RowCount >0)
		begin
			set @sqlQuery1 = @sqlQuery1 + ' inner join #SwipeCardNo on #SwipeCardNo.SwipecardNo=e.swipecardNo '
		end
	set @sqlQuery1 = @sqlQuery1 + ' where la.type=''Restricted Holiday'' and la.Isdeleted=0  and e.isdeleted=0'
	set @sqlQuery1 = @sqlQuery1 + ' and la.Leave_Fromdate >= convert(datetime,''' + cast(@LeaveSuccessiveFromDate as varchar(20)) + ''',101) and la.Leave_ToDate <= convert(datetime, ''' + cast(@LeaveSuccessiveToDate as varchar(20)) + ''',101) and el.EffectiveDate = (select top 1 EffectiveDate from TA_Tbl_EmployeeLevel where EmployeeId = e.Employee_Id and EffectiveDate <=  la.Leave_ApplicationDate order by EffectiveDate desc) '
	If not(@Employee_Code_Id = '')
		Begin
			set @sqlQuery1 = @sqlQuery1 + ' and e.Employee_Code_Id = ''' + cast(@Employee_Code_Id as nvarchar(50)) + ''''
		End
	If not(@Employee_FirstName = '')
		Begin
			set @sqlQuery1 = @sqlQuery1 + ' and (e.Employee_FirstName +'' '' + isnull(e.Employee_LastName,'''') like ''' + cast(@Employee_FirstName+'%' as nvarchar(100)) + ''')'
		End	
	if(@isReportingto > 0)
            BEGIN
               set @sqlQuery = @sqlQuery + ' and (e.Employee_ID IN (select * from #EmployeeIds)) '  
            END
	If not(@LeaveApplicationStartDate = '01/01/1900' and @LeaveApplicationEndDate = '01/01/1900')
		Begin		
			set @sqlQuery1 = @sqlQuery1 + ' and (la.Leave_FromDate between convert(datetime,''' + cast(@LeaveApplicationStartDate as nvarchar(11)) + ''', 101) and convert(datetime,''' + cast(@LeaveApplicationEndDate as nvarchar(11)) + ''',101)'
			set @sqlQuery1 = @sqlQuery1 + ' or la.Leave_ToDate between convert(datetime,''' + cast(@LeaveApplicationStartDate as nvarchar(11)) + ''', 101) and convert(datetime,''' + cast(@LeaveApplicationEndDate as nvarchar(11)) + ''',101))'
		End
	set @sqlQuery2 = @sqlQuery2 + ' union '
	set @sqlQuery2 = @sqlQuery2 + ' select Employee_Code_Id as employeeid,e.Employee_FirstName  + '' '' + isnull(e.Employee_LastName,'''') as Employee_FirstName ,ld.name,la.LeaveApplication_Id,la.Employee_Id,
									case when '+cast(@dateFormat as varchar(100))+'=106 then
									REPLACE(CONVERT(VARCHAR, Leave_ApplicationDate,'+cast(@dateFormat as varchar(100))+'),'+''' '''+','+'''-'''+') 
									else CONVERT(VARCHAR, Leave_ApplicationDate, '+cast(@dateFormat as varchar(100))+') end  AS Leave_ApplicationDate,
									case when '+cast(@dateFormat as varchar(100))+'=106 then
									REPLACE(CONVERT(VARCHAR, Leave_FromDate,'+cast(@dateFormat as varchar(100))+'),'+''' '''+','+'''-'''+') 
									else CONVERT(VARCHAR, Leave_FromDate, '+cast(@dateFormat as varchar(100))+') end
									+ '' To '' + 
									case when '+cast(@dateFormat as varchar(100))+'=106 then
									REPLACE(CONVERT(VARCHAR, Leave_ToDate,'+cast(@dateFormat as varchar(100))+'),'+''' '''+','+'''-'''+') 
									else CONVERT(VARCHAR, Leave_ToDate, '+cast(@dateFormat as varchar(100))+') end  AS Duration,la.Type '
	set @sqlQuery2 = @sqlQuery2 + ' as LeaveType,la.Status as Status,la.NumberOfDays,la.Reason as Reason,la.Remarks as Remarks '   
	set @sqlQuery2 = @sqlQuery2 + ' from TA_Tbl_LeaveApplication la '
	set @sqlQuery2 = @sqlQuery2 + ' inner join Ta_tbl_employee e on e.Employee_Id=la.Employee_Id '
	set @sqlQuery2 = @sqlQuery2 + ' inner join TA_Tbl_EmployeeLevel el on e.Employee_Id = el.EmployeeId '
	set @sqlQuery2 = @sqlQuery2 + ' inner join Ta_tbl_leveldetails ld on  el.LevelId=ld.id '
	set @sqlQuery2 = @sqlQuery2 + ' inner join TA_Tbl_YearSetting ys on la.YearSettings_Id=ys.YearSettings_Id '
	set @sqlQuery2 = @sqlQuery2 + ' inner join #LevelRights on #LevelRights.LevelId=ld.id '
	if(@RowCount >0)
		begin
			set @sqlQuery2 = @sqlQuery2 + ' inner join #SwipeCardNo on #SwipeCardNo.SwipecardNo=e.swipecardNo '
		end
	set @sqlQuery2 = @sqlQuery2 + ' where la.Isdeleted=0  and (la.type=''Comp Off'' or la.type=''ESI'') and  e.isdeleted=0  and la.Leave_FromDate >= convert(datetime,''' + cast(@LeaveSuccessiveFromDate as varchar(20)) + ''',101) and la.Leave_ToDate <= convert(datetime, ''' + cast(@LeaveSuccessiveToDate as varchar(20)) + ''',101)'
	set @sqlQuery2 = @sqlQuery2 + ' and el.EffectiveDate = (select top 1 EffectiveDate from TA_Tbl_EmployeeLevel where EmployeeId = e.Employee_Id and  EffectiveDate <=  la.Leave_ApplicationDate order by EffectiveDate desc) '
	If not(@Employee_Code_Id = '')
		Begin
			set @sqlQuery2 = @sqlQuery2 + ' and e.Employee_Code_Id = ''' + cast(@Employee_Code_Id as nvarchar(50)) + ''''
		End
		If (@EmployeeID > 1)
		Begin
			set @sqlQuery2 = @sqlQuery2 + ' and e.Employee_Id = ''' + cast(@EmployeeID as nvarchar(50)) + ''''
		End
	If not(@Employee_FirstName = '')
		Begin
			set @sqlQuery2 = @sqlQuery2 + ' and (e.Employee_FirstName +'' '' + isnull(e.Employee_LastName,'''') like ''' + cast(@Employee_FirstName+'%' as nvarchar(100)) + ''')'
		End
	if(@isReportingto > 0)
            BEGIN
               set @sqlQuery = @sqlQuery + ' and (e.Employee_ID IN (select * from #EmployeeIds)) '  
            END
	If not(@LeaveApplicationStartDate = '01/01/1900' and @LeaveApplicationEndDate = '01/01/1900')
		Begin		
			set @sqlQuery2 = @sqlQuery2 + ' and (la.Leave_FromDate between convert(datetime,''' + cast(@LeaveApplicationStartDate as nvarchar(11)) + ''', 101) and convert(datetime,''' + cast(@LeaveApplicationEndDate as nvarchar(11)) + ''',101)'
			set @sqlQuery2 = @sqlQuery2 + ' or la.Leave_ToDate between convert(datetime,''' + cast(@LeaveApplicationStartDate as nvarchar(11)) + ''', 101) and convert(datetime,''' + cast(@LeaveApplicationEndDate as nvarchar(11)) + ''',101))'
		End
	set @sqlQuery3= @sqlQuery3 + ' Order by LeaveApplication_Id desc '

	print @sqlQuery2
	exec (@sqlQuery+@sqlQuery1+@sqlQuery2+@sqlQuery3)
	drop table #LevelRights
	drop table #SwipeCardNo
END


------------
GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_SelectEmployeeDetails]    Script Date: 19-08-2026 18:08:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Proc_TA_SelectEmployeeDetails 361
-- Modified by : Prabakaran.P
-- Modified on : 26-Apr-08
--	Proc_TA_SelectEmployeeDetails 3
ALTER PROCEDURE [dbo].[Proc_TA_SelectEmployeeDetails] 
(  
	@EmployeeId INT=0 
)  
AS
BEGIN
	DECLARE @ActiveYearId INT
	DECLARE @EffectiveDate datetime
	DECLARE @EffectBPayDate datetime
	DECLARE @EffectLevelDate datetime
	DECLARE @EffectCategoryDate datetime
	DECLARE @EffectTypeDate datetime
	select @EffectiveDate=Max(EffectiveDate) from TA_Tbl_EmployeeDesignation where EmployeeId=@EmployeeId
	select @EffectBPayDate=Max(EffectiveDate) from TA_Tbl_EmployeePay where EmployeeId=@EmployeeId
	select @EffectLevelDate=Max(EffectiveDate) from TA_Tbl_EmployeeLevel where EmployeeId=@EmployeeId
	select @EffectCategoryDate=Max(EffectiveDate) from TA_Tbl_EmployeeCategory where EmployeeId=@EmployeeId
	select @EffectTypeDate=Max(EffectiveDate) from TA_Tbl_EmployeeType where EmployeeId=@EmployeeId
	declare @ReportingToEmailID as nvarchar(100)
	declare @ApprovalAuthorityEmailID as nvarchar(100)
	select @ReportingToEmailID=EmailId from TA_Tbl_Employee where Employee_Id=(select Reporting_To from TA_Tbl_Employee where Employee_Id=@EmployeeId)
	select @ApprovalAuthorityEmailID=EmailId from TA_Tbl_Employee where Employee_Id=(select Leave_Sanc_Authority from TA_Tbl_Employee where Employee_Id=@EmployeeId)
	
	SELECT	E.Employee_Id,ELevel.LevelId,L.name AS LevelName,E.UserGroup_Id,g.groupName,EDesig.DesignationId as Designation_Id,EDesig.EffectiveDate as EDesigEffectDate,
			T.TitleName,E.Bus_Id,TV.Route_Name AS Bus,E.Employee_FirstName,E.Employee_MiddleName,isnull(E.Employee_LastName,'') as Employee_LastName, 
			E.Father_name,E.Mother_name,CONVERT(NVARCHAR,E.DateofBirth,101) AS DateofBirth ,CONVERT(NVARCHAR,E.DateofBirth_Record,101) AS DateofBirth_Record,  
			E.Blood_Group,E.EmailId,E.Contact_Address,E.Permanent_Address,E.Phone_Number,E.Mobile_Number,E.Gender,E.Marital_status,  
			E.Nationality,R.Name as Religion,R.ID as ReligionId,E.Photo,E.Personal_mailid,E.Passport_No,CONVERT(NVARCHAR,E.Passport_expirydate,101) AS Passport_expirydate,  
			E.Visa_No,CONVERT(NVARCHAR,E.Visa_expirydate,101) AS Visa_expirydate,E.Vehicle_Type,E.Vehicle_No,  
			CONVERT(NVARCHAR,E.Anniversary_Date,101) AS Anniversary_Date,E.Native_Place,Look.Title AS City,E.District,E.State,Look1.Title AS Statename,E.Country,Look2.Title AS Countryname,  
			E.Spouse_No,E.Children_No,E.Employee_Code_Id,CONVERT(NVARCHAR,E.DateOfJoining,101) AS DateOfJoining,E.Reporting_To,(Rep.Employee_FirstName + '' + isnull(Rep.Employee_LastName,'')) as Reporting,  
			E.Leave_Sanc_Authority,(Sanc.Employee_FirstName + '' + isnull(Sanc.Employee_LastName,'')) AS SancAuth, E.Prev_Experience,EPay.BasicPay as Basic_Salary,EPay.EffectiveDate as EPayEffectDate,E.Hostel,E.UserName,E.Emp_password,  
			E.Qual_JoiningTime,E.Qual_Employment,E.OutDoorJob,E.CompOff_Eligible,E.OT_Eligible,E.ESI_Eligible,E.PF_Eligible,   
			E.Report_Exclude,E.Employee_Status,E.SwipeCardNo,tblCat.CategoryName,tblCat.CategoryId,ECat.EffectiveDate as CategoryEffDate,tblType.TypeName,tblType.TypeId,EType.EffectiveDate as TypeEffDate,ELevel.EffectiveDate as LevelEffDate,E.Isdeleted,E.IsFlexible,convert(varchar(20),E.InActiveDate,110)as InActiveDate
			,E.CustomField1,E.CustomField2,E.CustomField3,E.CustomField4,E.CustomField5,ENT.Entrance_Name,@ReportingToEmailID as 'ReportingToEmailID',@ApprovalAuthorityEmailID as 'ApprovalAuthorityEmailID',0 As MobileClockInOUt,0 AS SMSEnabled,E.WFH_Eligible
	FROM TA_Tbl_Employee E   
	left Outer join TA_Tbl_EmployeeEntrance EE On EE.Employee_Code_Id=E.Employee_Code_Id 
	left Outer  Join TA_Tbl_Terminal TER On TER.Terminal_ReaderID=EE.Device_Id
	left Outer  Join TA_Tbl_Entrance_INOUTDetails ED On ED.Reader_Id=TER.Terminal_Id
	left Outer  Join TA_Tbl_Entrance ENT On ENT.Entrance_Id=ED.Entrance_Id
	left Outer join ta_tbl_usergroup g on g.groupId = e.usergroup_id  
	LEFT OUTER JOIN TA_Tbl_EmployeeDesignation EDesig ON EDesig.EmployeeID= E.Employee_Id 
	LEFT OUTER JOIN TA_Tbl_EmployeePay EPay ON EPay.EmployeeID= E.Employee_Id 
	LEFT OUTER JOIN TA_Tbl_EmployeeLevel ELevel ON ELevel.EmployeeID= E.Employee_Id 
	LEFT OUTER JOIN TA_Tbl_LevelDetails L ON L.Id=ELevel.LevelId 
	LEFT OUTER JOIN TA_Tbl_EmployeeCategory ECat ON ECat.EmployeeID= E.Employee_Id 
	LEFT OUTER JOIN TA_Tbl_EmployeeType EType ON EType.EmployeeID= E.Employee_Id 
	LEFT OUTER JOIN TA_Tbl_Title T ON T.TitleId=EDesig.DesignationId  
	LEFT OUTER JOIN TA_Tbl_RouteMaster TV ON TV.[Id]=E.Bus_Id  
	LEFT OUTER JOIN TA_Tbl_Lookup Look ON Look.ID=E.Native_Place  
	LEFT OUTER JOIN TA_Tbl_Lookup Look1 ON Look1.ID=E.State  
	LEFT OUTER JOIN TA_Tbl_Lookup Look2 ON Look2.ID=E.Country  
	LEFT OUTER JOIN TA_Tbl_Employee Rep ON Rep.Employee_Id = E.Reporting_To  
	LEFT OUTER JOIN TA_Tbl_Employee Sanc ON Sanc.Employee_Id = E.Leave_Sanc_Authority 
	INNER JOIN TA_Tbl_Category tblCat ON tblCat.CategoryId= ECat.CategoryId
	INNER JOIN TA_Tbl_Type tblType ON tblType.TypeId= EType.TypeId
	LEFT OUTER JOIN TA_Tbl_Religion R on e.Religion=R.ID
	WHERE E.Employee_Id=@EmployeeId  AND EDesig.EffectiveDate=@EffectiveDate AND EPay.EffectiveDate=@EffectBPayDate AND ELevel.EffectiveDate=@EffectLevelDate AND ECat.EffectiveDate=@EffectCategoryDate AND EType.EffectiveDate=@EffectTypeDate
END  


-- =============================================
ALTER PROCEDURE [dbo].[Proc_TA_UpdateShiftReport]
      -- Add the parameters for the stored procedure here
      @WeekStartDay nvarchar(3),
      @WeekEndDay nvarchar(3),
      @MonthStartDay nvarchar(3),
      @MonthEndDay nvarchar(3),
      @MapKey nvarchar(20)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      update ta_tbl_shiftsettings set CumulativeWeekStartDay=@WeekStartDay,CumulativeWeekEndDay=@WeekEndDay,MonthStartDay=@MonthStartDay,MonthEndDay=@MonthEndDay
    -- Insert statements for procedure here
      select -1 as 'ShiftSettingId'
END


GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_SelectApprovalSettings]    Script Date: 21-08-2026 13:28:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Procedure Name		:	Proc_TA_SelectApprovalSettings
Description			:	select the companies
Tables Included		:	TA_Tbl_ApprovalSettings
Created By			:	Prabakaran.P
Created On			:	11-Sep-09
Modified By	:
Modified On	:
Proc_TA_SelectApprovalSettings 1
*/
ALTER PROCEDURE [dbo].[Proc_TA_SelectApprovalSettings]
-- Add the parameters for the stored procedure here
@intCompanyId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	select *,0 as webClockInOut from TA_Tbl_ApprovalSettings where CompanyId=@intCompanyId
END


GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_InsertUpdateApprovalSettings]    Script Date: 21-08-2026 13:32:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		Prabakaran.P
-- Create date: 11-Sep-09
-- Description:	
-- Modifed By :		K.Gopinath
-- Create date: 01-02-2014
-- Description:	Modified to add OD as seperate column
-- =============================================
ALTER PROCEDURE [dbo].[Proc_TA_InsertUpdateApprovalSettings]
@intCompanyId int,  
@intPattern int,  
@intRoster int, 
@intOTApproval int,  
@intLeaveSanction int,  
@intPermissionSanction int, 
@intManualEntry int,
@intOnDutyEntry int,
@Approval int,
@Emp_Id int,
@webclockInOut int
AS
BEGIN

declare @CompanyCount as int
set @CompanyCount=(select count(*) from TA_Tbl_ApprovalSettings where CompanyId=@intCompanyId)


	if (@CompanyCount=0)	
		Begin
				Insert into TA_Tbl_ApprovalSettings 
				(CompanyId,Pattern, Roster,OTApproval,LeaveSanction,PermissionSanction,ManualEntry,ODEntry,Emp_Id,Approval) 
				values (@intCompanyId,@intPattern,@intRoster,@intOTApproval,@intLeaveSanction,@intPermissionSanction,@intManualEntry,@intOnDutyEntry,@Emp_Id,@Approval)
		End
	Else
		Begin
				update TA_Tbl_ApprovalSettings set
					CompanyId=@intCompanyId,
					Pattern = @intPattern,
					Roster = @intRoster,
					OTApproval = @intOTApproval,
					LeaveSanction=@intLeaveSanction,
					PermissionSanction=@intPermissionSanction,
					ManualEntry=@intManualEntry,
					ODEntry=@intOnDutyEntry,
					Approval=@Approval,
					Emp_Id=@Emp_Id
				where CompanyId=@intCompanyId
		End

END


GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_UpdateShiftSettings]    Script Date: 21-08-2026 12:10:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- ALTER  date: <Create Date,,>
-- Description:	<Description,,>
-- Modified By		:	A.K.T.MOORTHI
-- Modified Date	:	01-Apl-2013
-- Description		:	Adding the New Column(Default Lunch hrs)
--Modified by Gopinath on 18Jun14 for Angler Migration added new column named as 'ExceptionHours'
-- =============================================
ALTER PROCEDURE [dbo].[Proc_TA_UpdateShiftSettings] 
(
--	@tag nvarchar(10),
--	@CompanyId as int,
--	@maxHoursBeforeShift as varchar(10),
--	@maxHoursAfterShift as varchar(10),
--	@shiftBreak as varchar(10),
--	@shiftMaxBreakHours as varchar(10),
--	@monthlyWorkingHours as varchar(10),
--	@weeklyWorkingHours as varchar(10),
--	@AuditEmp as nvarchar(20),
--	@HolidayOT as float,
--	@WeekOffOT as float,
--	@otOption as varchar(10),
--	@strMonthClosureDate as varchar(20),
--	@flexibleStatus int,
--	@flexibleValue int,
--	@otStatus int,		
--	@fullAttendance int,
--	@halfAttendance int,
--	@DefaultLunchHours VARCHAR(10),
--	@MaxLunchHrs NVARCHAR(5),
--	@WeekOffStartHours as varchar(5),
--	@IgnorePunch tinyint,
--	@IgnorePunchInterval varchar(5),
--	@AfterShiftTime bit,
--	@BeforeShiftTime bit,
--	@DefinedWorkhrs bit,
--	@NoOfSuccessiveDaysInYear int,
--	@CompensationValidityDays int,
--	@HalfDayCompOff VARCHAR(5),
--	@FullDayCompOff VARCHAR(5),
--	@VaildityCompOffDays INT,
--	@LateEntry bit,
--	@EarlyEntry bit,
--	@EarlyExit bit,
--	@LateExit bit,
--	@IsLunchDeduction bit,
--	@LunchDeductionHours nvarchar(10)='00:00',
--	@ExceptionHours as varchar(10),
--	@24maxHoursBeforeShift as varchar(10),
--	@24weeklyWorkingHours as varchar(10),
--    @Flexi_AfterShiftTime	bit,
--	@Flexi_BeforeShiftTime	bit,
--	@Flexi_DefinedWorkhrs	bit,
--	@CumulativeWeekStartDay as varchar(10),
--	@CumulativeWeekEndDay as varchar(10),	
--	@isCarryForwardCOff int,
--	@isRequestingforCompOff int,
--	@FirstAndLast bit,
--	@SpecialOverTime1 int,	
--	@SpecialOverTime2 int,
--	@LATEMINSPERDAY int,
--	@LATEDAYSPERMONTH int,
--	@EarlyExitMinsPerDay int,
--	@EarlyExitDaysPerMonth int,
--	@EnableAbsent int

   @tag NVARCHAR(10),
    @CompanyId INT,

    @maxHoursBeforeShift VARCHAR(10),
    @maxHoursAfterShift VARCHAR(10),
    @shiftBreak VARCHAR(10),
    @shiftMaxBreakHours VARCHAR(10),

    @monthlyWorkingHours VARCHAR(10),
    @weeklyWorkingHours VARCHAR(10),

    @AuditEmp NVARCHAR(20),

    @HolidayOT FLOAT,
    @WeekOffOT FLOAT,

    @otOption VARCHAR(10),
    @strMonthClosureDate VARCHAR(20),

    @flexibleStatus INT,
    @flexibleValue INT,
    @otStatus INT,

    @fullAttendance INT,
    @halfAttendance INT,

    @DefaultLunchHours VARCHAR(10),
    @MaxLunchHrs NVARCHAR(5),

    @WeekOffStartHours VARCHAR(5),

    @IgnorePunch TINYINT,
    @IgnorePunchInterval VARCHAR(5),

    @AfterShiftTime BIT,
    @BeforeShiftTime BIT,
    @DefinedWorkhrs BIT,

    @NoOfSuccessiveDaysInYear INT,
    @CompensationValidityDays INT,

    @HalfDayCompOff VARCHAR(5),
    @FullDayCompOff VARCHAR(5),
    @VaildityCompOffDays INT,

    @LateEntry BIT,
    @EarlyEntry BIT,
    @EarlyExit BIT,
    @LateExit BIT,

    @IsLunchDeduction BIT,
    @LunchDeductionHours NVARCHAR(10) = '00:00',

    @ExceptionHours VARCHAR(10),

    @24maxHoursBeforeShift VARCHAR(10),
    @24weeklyWorkingHours VARCHAR(10),

    @Flexi_AfterShiftTime BIT,
    @Flexi_BeforeShiftTime BIT,
    @Flexi_DefinedWorkhrs BIT,

    @CumulativeWeekStartDay VARCHAR(10),
    @CumulativeWeekEndDay VARCHAR(10),

    @isCarryForwardCOff INT,
    @isRequestingforCompOff INT,

    @FirstAndLast BIT,

    @SpecialOverTime1 INT,
    @SpecialOverTime2 INT,

    @LATEMINSPERDAY INT,
    @LATEDAYSPERMONTH INT,

    @EarlyExitMinsPerDay INT,
    @EarlyExitDaysPerMonth INT,
    @EnableAbsent INT
)	
AS
BEGIN
		Declare @isDisplay int=1
		declare @cou as int
		set @cou = (Select count(*) from TA_Tbl_shiftSettings where CompanyId=@CompanyId)		
		set @cou = (Select count(*) from TA_Tbl_shiftSettings where CompanyId=@CompanyId)
		if(@tag='OT')
		begin
			update TA_Tbl_shiftSettings set 
				HolidayOT=@HolidayOT,
				WeekOffOT=@WeekOffOT, 
				otOption=@otOption , 
				OTRoundoffEnable=@otStatus,
				AfterShiftTime=@AfterShiftTime,
				BeforeShiftTime=@BeforeShiftTime,
				DefinedWorkhrs=@DefinedWorkhrs,
				HalfDayCompOff = @HalfDayCompOff,
				FullDayCompOff = @FullDayCompOff,
				VaildityCompOffDays = @VaildityCompOffDays,
				Flexi_AfterShiftTime=@Flexi_AfterShiftTime,
				Flexi_BeforeShiftTime=@Flexi_BeforeShiftTime,
				Flexi_DefinedWorkhrs=@Flexi_DefinedWorkhrs,
				Carry_Forward_COff_Year=@isCarryForwardCOff,
				isRequestingforCompOff=@isRequestingforCompOff
				where CompanyId=@CompanyId
			  select ShiftSettingId from TA_Tbl_shiftSettings where CompanyId=@CompanyId
		end
		else
		begin
			if (@cou>0)
			Begin
			--select * from TA_Tbl_shiftSettings
				update TA_Tbl_shiftSettings set 
				maxHoursBeforeShift=@maxHoursBeforeShift, 
				maxHoursAfterShift=@maxHoursAfterShift,
				shiftBreak=@shiftBreak,
				shiftMaxBreakHours=@shiftMaxBreakHours,
				monthlyWorkingHours=@monthlyWorkingHours,
				weeklyWorkingHours=@weeklyWorkingHours, 
				EmpID=@AuditEmp,
				DefaultMonthClosureDate=@strMonthClosureDate,
				IsFlexible = @flexibleStatus,
				FlexibleFlag=@flexibleValue,
				FullDayAttendance=@fullAttendance,
				halfdayAttendance=@halfAttendance,
				WeeklyOffStartHours=@WeekOffStartHours,
				IgnoreDuplicatePunch=@IgnorePunch,
				PunchInterval=@IgnorePunchInterval,
				NoOfSuccessiveDaysInYear=CONVERT(int, @NoOfSuccessiveDaysInYear),
				CompensationValitityDays=@CompensationValidityDays,						
				lateEntry=@LateEntry,
				EarlyEntry=@EarlyEntry,
				EarlyExit=@EarlyExit,
				LateExit=@LateExit,
				timestamp1=getdate(),
				IsLunchDeduction=@IsLunchDeduction,
				LunchDeductionHours=@LunchDeductionHours,
				MaxHoursBeforeShift_24HrsShift=@24maxHoursBeforeShift,
				WeeklyOffStartHours_24HrsShift=@24weeklyWorkingHours,
				CumulativeWeekStartDay=@CumulativeWeekStartDay,
				CumulativeWeekEndDay=@CumulativeWeekEndDay,
				DefaultLunchHours = @DefaultLunchHours,
				MaxLunchHours = @MaxLunchHrs,
				MaxWorkPlaceInHours=@ExceptionHours,
				FirstAndLast=@FirstAndLast,
				isDisplay=@isDisplay
				where CompanyId=@CompanyId
			  select ShiftSettingId from TA_Tbl_shiftSettings where CompanyId=@CompanyId
			End
		Else
			Begin
				insert into Ta_Tbl_ShiftSettings (CompanyId,HolidayOT,WeekOffOT, otOption,maxHoursBeforeShift,maxHoursAfterShift,shiftBreak,shiftMaxBreakHours,monthlyWorkingHours,weeklyWorkingHours,EmpID,DefaultMonthClosureDate,IsFlexible,FlexibleFlag,OTRoundoffEnable,FullDayAttendance,halfdayAttendance,WeeklyOffStartHours,IgnoreDuplicatePunch,PunchInterval,AfterShiftTime,BeforeShiftTime,DefinedWorkhrs,NoOfSuccessiveDaysInYear,CompensationValitityDays, HalfDayCompOff, FullDayCompOff, VaildityCompOffDays
				,lateEntry,EarlyEntry,EarlyExit,LateExit
				,timestamp,timestamp1,IsLunchDeduction,LunchDeductionHours,Flexi_AfterShiftTime,Flexi_BeforeShiftTime,Flexi_DefinedWorkhrs,MaxHoursBeforeShift_24HrsShift,WeeklyOffStartHours_24HrsShift,CumulativeWeekStartDay,CumulativeWeekEndDay, DefaultLunchHours, MaxLunchHours,MaxWorkPlaceInHours,Carry_Forward_COff_Year,isRequestingforCompOff,FirstAndLast,isDisplay) 
				values (@CompanyId,null,null, null,@maxHoursBeforeShift,@maxHoursAfterShift,@shiftBreak,@shiftMaxBreakHours,@monthlyWorkingHours,@weeklyWorkingHours,@AuditEmp,@strMonthClosureDate,@flexibleStatus,@flexibleValue,null,@fullAttendance,@halfAttendance,@WeekOffStartHours,@IgnorePunch,@IgnorePunchInterval,null,null,null,CONVERT(int,@NoOfSuccessiveDaysInYear),@CompensationValidityDays, null, null, null
			,@LateEntry,@EarlyEntry,@EarlyExit,@LateExit
				,getdate(),GETDATE(),@IsLunchDeduction,@LunchDeductionHours,null,null,null,@24maxHoursBeforeShift,@24weeklyWorkingHours,@CumulativeWeekStartDay,@CumulativeWeekEndDay,@DefaultLunchHours, @MaxLunchHrs,@ExceptionHours,@isCarryForwardCOff,@isRequestingforCompOff,@FirstAndLast,@isDisplay)
				select @@identity
			End
		end
END


GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_GetShiftSettingsByComId]    Script Date: 21-08-2026 11:52:42 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
-- ================================================
-- Author:		Kumuthini
-- Create date: 15-Jan-09
-- Description:	Get the Shift Settings Details by Company Id
-- Modified By		:	A.K.T.MOORTHI
-- Modified Date	:	19-July-2013
-- Description		:	Added Select Query Column.
-- Modified By		:	Manjula.P
-- Modified On		:	18-Jan-2014
-- Descriptions		:	selecting lunch deduction hours columns for shift settings page
-- ================================================
-- Proc_TA_GetShiftSettingsByComId 1
ALTER PROCEDURE [dbo].[Proc_TA_GetShiftSettingsByComId] 
(
	@CompanyId int
)
AS
BEGIN
		SELECT ShiftSettingId,
		CompanyId,
		MaxHoursBeforeShift,
		MaxHoursAfterShift,
		ShiftBreak,
		ShiftMaxBreakHours,
		MonthlyWorkingHours,
		WeeklyWorkingHours,
		EmpID,
		HolidayOT,
		WeekOffOT,
		otOption,
		DefaultMonthClosureDate,
		IsFlexible,
		FlexibleFlag,
		OTRoundoffEnable,
		FullDayAttendanceHrs,
		HalfDayAttendanceHrs,
		HalfDayAttendance,
		FullDayAttendance,
		WeeklyOffStartHours,
		IgnoreDuplicatePunch,
		PunchInterval,
		AfterShiftTime,
		BeforeShiftTime,
		DefinedWorkhrs,
		NoOfSuccessiveDaysInYear,
		CompensationValitityDays,
		VaildityCompOffDays,
		CAST(HalfDayCompOff AS VARCHAR(5)) AS HalfDayCompOff,
		CAST(FullDayCompOff AS VARCHAR(5)) AS FullDayCompOff,
		lateentry,earlyentry,earlyexit,lateexit,
		--Added by MAnjula.P on 18-Jan-2014
		isnull(IsLunchDeduction,0) as IsLunchDeduction, ISNULL(LunchDeductionHours,'00:00') as LunchDeductionHours,
		Flexi_AfterShiftTime,Flexi_BeforeShiftTime,Flexi_DefinedWorkhrs,
		MaxHoursBeforeShift_24HrsShift,
		WeeklyOffStartHours_24HrsShift,
		CumulativeWeekStartDay,
		CumulativeWeekEndDay,
		[DefaultLunchHours],
		[MaxLunchHours],
		[MaxWorkPlaceInHours],Carry_Forward_COff_Year,isRequestingforCompOff,
		isnull(FirstAndLast,0)as FirstAndLast,
		isDisplay=isnull(isDisplay,0),0 As SpecialOverTime1, 0 As SpecialOverTime2, 0 AS LATEMINSPERDAY, 0 as LATEDAYSPERMONTH, 0 As EarlyExitMinsPerDay,
		0 As EarlyExitDaysPerMonth, 0 As EnableAbsent
	FROM TA_Tbl_shiftSettings WHERE CompanyId = @CompanyId
		Declare @SuccessiveyearId int
		set @SuccessiveyearId = (select top 1 yearsettings_Id from ta_tbl_yearSetting where yearsettings_id> (select yearsettings_id from ta_tbl_yearSetting where isactive=1) order by yearsettings_id)
		select count(*) as RecordCount from ta_tbl_leaveApplication LA inner join ta_tbl_yearSetting YS on LA.YearSettings_Id=YS.YearSettings_Id 
		where LA.yearsettings_id=@SuccessiveyearId and LA.companyId=@CompanyId and LA.Successiveyear=1 and LA.isdeleted=0
END


Alter  Table TA_Tbl_Shift Add empCategory int
Alter  Table TA_Tbl_Shift Add isStraightShift int
Alter  Table TA_Tbl_Shift Add specialOvertime_start2 nvarchar(20)
Alter  Table TA_Tbl_Shift Add MaxShiftWorkHours nvarchar(20)

ALTER  PROCEDURE [dbo].[Proc_TA_ShiftDetailsById]
(
@ShiftId INT
)
AS
declare @LevelId as int
BEGIN
	set @LevelId=(select Level_Id FROM TA_Tbl_Shift WHERE Shift_Id=@ShiftId)
	if(@LevelId!=0)
	BEGIN
		SELECT TA_Tbl_Shift.*, TA_Tbl_Leveldetails.Name,CategoryName=(select top 1 CategoryName from TA_Tbl_Category where CategoryId=empCategory) FROM TA_Tbl_Shift 
		INNER JOIN TA_Tbl_Leveldetails ON TA_Tbl_Shift.Level_Id=TA_Tbl_Leveldetails.ID  
		WHERE Shift_Id=@ShiftId
	END
	ELSE
	BEGIN
		SELECT TA_Tbl_Shift.*, '' as Name,CategoryName=(select top 1 CategoryName from TA_Tbl_Category where CategoryId=empCategory) FROM TA_Tbl_Shift 
		WHERE Shift_Id=@ShiftId
	END
END


GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_InsertLeavePolicy]    Script Date: 26-08-2026 12:29:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author				:	Arun vinoth
-- ALTER  date		:	28 June 2007
-- Description		:	Leave policy - Insert
--Modified By		:	Manikandan.R
--Modified Date		:	15-10-2011
--Modified By		:	A.K.T.MOORTHI
--Modified Date		:	31-Dec-2013
-- Description		:	Add the Halfdaycompoff, VaildDays Column for insert and update query.
-- exec Proc_TA_InsertLeavePolicy @Type='I',@Policy_Id=0,@PolicyName=N'Testing School',@Permission_MaxHrs_Month=N'06:00',@Permission_MaxInstances_Month=2,@Permission_MaxHrs_Instance=N'03:00',@Employee_Id=1,@PermissionBeforeLeave=1,@PermissionAfterLeave=0,@OtHourWise=0,@OtDayWise=0,@OtMinHrs='',@EmployeeCategory=0,@EmployeeType=0,@Gender='Both',@Religion=0,@HalfDayCompOff='',@VaildDays=0,@Permission_MinHrs_Instance=N'5'
-- =============================================
ALTER   PROCEDURE [dbo].[Proc_TA_InsertLeavePolicy] 
	@Type CHAR(1)='',
	@Policy_Id INT=0,
	@PolicyName NVARCHAR(50)='',
	@Permission_MaxHrs_Month NVARCHAR(5)='',
	@Permission_MaxInstances_Month INT=0,
	@Permission_MaxHrs_Instance NVARCHAR(5)='',
	@Employee_Id INT,
	@PermissionBeforeLeave INT, 
	@PermissionAfterLeave INT,
	@OtHourWise INT,
 	@OtDayWise INT,
	@OtMinHrs VARCHAR(20),
	@EmployeeCategory INT,
	@EmployeeType INT,
	@Gender VARCHAR(10),
	@Religion INT,
	@HalfDayCompOff VARCHAR(5),
	@VaildDays INT,
	@companyID int,
	@Permission_MinHrs_Instance nvarchar(10) =null
AS
	DECLARE @IsDeleted BIT
	SET @IsDeleted=0
BEGIN
	SET NOCOUNT ON;
	IF (@Type='I')
	BEGIN 
		INSERT INTO TA_Tbl_LeavePolicy (PolicyName, Permission_MaxHrs_Month, Permission_MaxInstances_Month, Permission_MaxHrs_Instance, IsDeleted, Emp_Id, PermissionBeforeLeave, PermissionAfterLeave, OtHourWise, OtDayWise, OtMinHrs, EmployeeCategory, EmployeeType, Gender, Religion, FullDayOTMinHrs, CompOffValidDays,CompanyID ,Permission_MinHrs_Instance)
			VALUES (@PolicyName, @Permission_MaxHrs_Month, @Permission_MaxInstances_Month, @Permission_MaxHrs_Instance, @IsDeleted, @Employee_Id, @PermissionBeforeLeave, @PermissionAfterLeave, @OtHourWise, @OtDayWise, @HalfDayCompOff, @EmployeeCategory, @EmployeeType, @Gender, @Religion, @OtMinHrs, @VaildDays,@companyID,@Permission_MinHrs_Instance)
	END
	ELSE IF (@Type='U')
	BEGIN
		UPDATE TA_Tbl_LeavePolicy SET 
			PolicyName = @PolicyName,
			Permission_MaxHrs_Month = @Permission_MaxHrs_Month,
			Permission_MaxInstances_Month = @Permission_MaxInstances_Month,
			Permission_MaxHrs_Instance = @Permission_MaxHrs_Instance,
			IsDeleted=@IsDeleted,
			Emp_Id=@Employee_Id,
			Time_Stamp=getdate(),
			PermissionBeforeLeave = @PermissionBeforeLeave,
			PermissionAfterLeave = @PermissionAfterLeave,
			OtHourWise = @OtHourWise,
			OtDayWise = @OtDayWise,
			OtMinHrs = @HalfDayCompOff,
			EmployeeCategory = @EmployeeCategory,
			EmployeeType = @EmployeeType,
			Gender = @Gender,
			Religion = @Religion,
			FullDayOTMinHrs = @OtMinHrs,
			CompOffValidDays = @VaildDays,
			CompanyID=@companyID,
			Permission_MinHrs_Instance=@Permission_MinHrs_Instance
		WHERE  Policy_Id = @Policy_Id
	END
END


GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_SelectLeavePolicy]    Script Date: 26-08-2026 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
-- =============================================
-- Author       : Arun vinoth
-- ALTER date   : 29 June 2007
-- Description  : Leave policy - select
-- Modified By  : Kumuthini.L      | Modified On: 06-Feb-2008
-- Modified By  : Prabakaran.P     | Modified On: 15-Feb-08
-- Modified By  : Manikandan.R     | Modified On: 15-Oct-11 | Description: Year closure changes
-- Modified By  : ANGLER Technologies | Modified On: 26-Aug-2026
-- Description  : Finetuned - removed temp table + UPDATE join pattern for 'flag' column
--                (replaced with inline correlated EXISTS), consolidated duplicate
--                SuperAdmin/non-SuperAdmin branches for @Type='A' into a single query.
--                Behavior/output columns are unchanged from the original procedure.
-- =============================================
*/
ALTER PROCEDURE [dbo].[Proc_TA_SelectLeavePolicy]
    @Type CHAR(1) = '',
    @Policy_Id INT = 0,
    @UserGroupName NVARCHAR(250)
AS
BEGIN
    SET NOCOUNT ON;

    IF (@Type = 'A')
    BEGIN
        -- Original had two near-identical branches for SuperAdmin vs others,
        -- differing only in the IsDeleted filter. Consolidated into one query.
        SELECT
            LP.*,
            (SELECT CategoryName FROM TA_Tbl_Category WHERE CategoryId = LP.EmployeeCategory) AS EmployeeCategorys,
            (SELECT TypeName FROM TA_Tbl_Type WHERE TypeId = LP.EmployeeType) AS EmployeeTypes,
            (SELECT Name FROM TA_Tbl_Religion WHERE ID = LP.Religion) AS Religions,
            CASE
                WHEN EXISTS (SELECT 1 FROM TA_Tbl_Emp_LeavePolicy ELP WHERE ELP.LeavePolicy_Id = LP.Policy_Id)
                    THEN 0
                ELSE 1
            END AS flag
        FROM TA_Tbl_LeavePolicy LP
        WHERE (@UserGroupName = 'SuperAdmin' OR LP.IsDeleted = 0);
    END

    ELSE IF (@Type = 'P')
    BEGIN
        -- Kept as two branches: SuperAdmin branch returns extra YearSetting/CompanyName
        -- columns that the non-SuperAdmin branch does not, matching original behavior.
        IF (@UserGroupName = 'SuperAdmin')
        BEGIN
            SELECT
                LP.*,
                GETDATE() AS YearSetting,
                (SELECT CategoryName FROM TA_Tbl_Category WHERE CategoryId = LP.EmployeeCategory) AS EmployeeCategorys,
                (SELECT TypeName FROM TA_Tbl_Type WHERE TypeId = LP.EmployeeType) AS EmployeeTypes,
                (SELECT Name FROM TA_Tbl_Religion WHERE ID = LP.Religion) AS Religions,
                CASE
                    WHEN EXISTS (SELECT 1 FROM TA_Tbl_Emp_LeavePolicy ELP WHERE ELP.LeavePolicy_Id = LP.Policy_Id)
                        THEN 0
                    ELSE 1
                END AS flag,
                (SELECT Name AS CompanyName FROM TA_Tbl_levelDetails WHERE id = LP.CompanyID) AS CompanyName
            FROM TA_Tbl_LeavePolicy LP
            WHERE LP.Policy_Id = @Policy_Id;
        END
        ELSE
        BEGIN
            SELECT
                LP.*,
                GETDATE() AS YearSetting,
                (SELECT CategoryName FROM TA_Tbl_Category WHERE CategoryId = LP.EmployeeCategory) AS EmployeeCategorys,
                (SELECT TypeName FROM TA_Tbl_Type WHERE TypeId = LP.EmployeeType) AS EmployeeTypes,
                (SELECT Name FROM TA_Tbl_Religion WHERE ID = LP.Religion) AS Religions,
                CASE
                    WHEN EXISTS (SELECT 1 FROM TA_Tbl_Emp_LeavePolicy ELP WHERE ELP.LeavePolicy_Id = LP.Policy_Id)
                        THEN 0
                    ELSE 1
                END AS flag
            FROM TA_Tbl_LeavePolicy LP
            WHERE LP.Policy_Id = @Policy_Id AND LP.IsDeleted = 0;
        END
    END
END


GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_SelectLeavePolicyDetails]    Script Date: 26-08-2026 16:12:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		Arun vinoth
-- Create date: 29 June 2007
-- Description:	Leave policy - select
-- =============================================
ALTER PROCEDURE [dbo].[Proc_TA_SelectLeavePolicyDetails] 
	-- Add the parameters for the stored procedure here
	
	@Policy_ID Varchar(50)
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

			BEGIN
				SELECT L.LeaveType,L.LeaveDescription,LP.*,0 as AvailServicePeriod FROM TA_Tbl_LeavePolicyDetails LP
				
				inner join TA_Tbl_LeaveType L ON LP.LeaveType_Id =L.LeaveType_Id
				
				WHERE LP.Policy_Id=@Policy_ID
			END
END

GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_GetManualEntrySanctionDetailsForSuperAdmin]    Script Date: 26-08-2026 17:23:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*    
Procedure Name  : Proc_TA_GetManualEntrySanctionDetailsForSuperAdmin 1,'1','Approved','','',0,0,'2018/01/01','2018/10/28',0,'','SuperAdmin',0
Description		: Get Manual Time Entry Sanction Details of Employees Based on their Level_Id ,Employee_Id ,etc..,    
Tables Included : TA_Tbl_ManualTimeEntry,Ta_tbl_leveldetails and TA_TBL_Employee  

exec Proc_TA_GetManualEntrySanctionDetailsForSuperAdmin @Employee_ID=1,@LevelId='5',@Status='All',@EmployeeId='',@EmployeeName='',@EmployeeCategory=0,@EmployeeType=0,@ManualEntry_FromDate='4/1/2026',@ManualEntry_ToDate='3/31/2027',@Isdeleted=0,@EmpSwipeCard='',@UserGroupName='SuperAdmin',@ParentID=5
*/    
ALTER PROCEDURE [dbo].[Proc_TA_GetManualEntrySanctionDetailsForSuperAdmin]    
	@Employee_ID int,
	@LevelId varchar(2000),    
	@Status varchar(20) =null,  
	@EmployeeId varchar(100),
	@EmployeeName varchar(50),  
	@EmployeeCategory int, 
	@EmployeeType int,
	@ManualEntry_FromDate varchar(20)=null,    
	@ManualEntry_ToDate varchar(20) =null,    
	@Isdeleted bit = 0 ,
	@EmpSwipeCard varchar(20),
	@UserGroupName varchar(250),
	@ParentID int  
AS  
BEGIN
	SET NOCOUNT ON;
	create table #EmpLevel(EmployeeId int,LevelId int,EffectiveDate datetime)
	create table #EmpType(EmployeeId int,TypeId int,EffectiveDate datetime)
	create table #EmpCategory(EmployeeId int,CategoryId int,EffectiveDate datetime)
	create table #LevelRights(LevelId int)
	insert into  #LevelRights select * from [dbo].[fn_TA_GetParentChild](@UserGroupName,@ParentID) as LevelId
	declare @isReportingto as int
	select @isReportingto=ReportsBasedOn from TA_Tbl_Usergroup where groupName=@UserGroupName
	create table #EmployeeIds(EmpAutoId int)
    insert into  #EmployeeIds select * from [dbo].[fn_TA_GetMappedEmployees_ReportingTo](@Employee_ID) as EmpAutoId  
    insert into  #EmployeeIds select * from [dbo].[fn_TA_GetMappedEmployees_SancAuthority](@Employee_ID) as EmpAutoId
	--Employee Level Indexes
	/****** Object:  Index [IDX_Effective]    Script Date: 05/27/2009 16:28:22 ******/
	CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpLevel] 
	(
		[EffectiveDate] ASC
	) ON [PRIMARY]
	/****** Object:  Index [IDX_LevelId]    Script Date: 05/27/2009 16:28:18 ******/
	CREATE NONCLUSTERED INDEX [IDX_LevelId] ON [dbo].[#EmpLevel] 
	(
		[LevelId] ASC
	) ON [PRIMARY]
	/****** Object:  Index [IDX_EmployeeId]    Script Date: 05/27/2009 16:27:55 ******/
	CREATE NONCLUSTERED INDEX [IDX_EmployeeId] ON [dbo].[#EmpLevel] 
	(
		[EmployeeId] ASC
	)ON [PRIMARY]
	--Employee Type Indexes
	/****** Object:  Index [IDX_Effective]    Script Date: 05/27/2009 16:28:22 ******/
	CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpType] 
	(
		[EffectiveDate] ASC
	) ON [PRIMARY]
	/****** Object:  Index [IDX_TypeId]    Script Date: 05/27/2009 16:28:18 ******/
	CREATE NONCLUSTERED INDEX [IDX_TypeId] ON [dbo].[#EmpType] 
	(
		[TypeId] ASC
	) ON [PRIMARY]
	/****** Object:  Index [IDX_EmployeeId]    Script Date: 05/27/2009 16:27:55 ******/
	CREATE NONCLUSTERED INDEX [IDX_EmployeeId] ON [dbo].[#EmpType] 
	(
		[EmployeeId] ASC
	)ON [PRIMARY]
	--Employee Category Indexes
	/****** Object:  Index [IDX_Effective]    Script Date: 05/27/2009 16:28:22 ******/
	CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpCategory] 
	(
		[EffectiveDate] ASC
	)ON [PRIMARY]
	/****** Object:  Index [IDX_CategoryId]    Script Date: 05/27/2009 16:28:18 ******/
	CREATE NONCLUSTERED INDEX [IDX_CategoryId] ON [dbo].[#EmpCategory] 
	(
		[CategoryId] ASC
	) ON [PRIMARY]
	/****** Object:  Index [IDX_EmployeeId]    Script Date: 05/27/2009 16:27:55 ******/
	CREATE NONCLUSTERED INDEX [IDX_EmployeeId] ON [dbo].[#EmpCategory] 
	(
		[EmployeeId] ASC
	) ON [PRIMARY]
	--Employee Level insert in Temporary table
	insert into #EmpLevel select Elevel.EmployeeId,EL.levelId,Elevel.EffectiveDate from (
	select max(el.EffectiveDate) as EffectiveDate,EmployeeId from TA_Tbl_EmployeeLevel el
	inner join ta_tbl_employee emp on el.EmployeeId = emp.Employee_Id 
	where emp.Isdeleted=0 group by EmployeeId) ELevel
	inner join TA_Tbl_EmployeeLevel EL on EL.EffectiveDate=ELevel.EffectiveDate and EL.employeeid=ELevel.EmployeeId
	AND EL.EffectiveDate <= CONVERT(DATETIME,cast(getdate() as nvarchar(50)), 102) and EL.LevelId is not null
	--select * from #EmpLevel
	--Employee Type insert in Temporary table
	insert into #EmpType select EmpType.EmployeeId,EType.TypeId,EmpType.EffectiveDate from (
	select max(et.EffectiveDate) as EffectiveDate,et.EmployeeId as EmployeeId from TA_Tbl_EmployeeType et
	inner join ta_tbl_employee emp on et.EmployeeId = emp.Employee_Id 
	where emp.Isdeleted=0 group by EmployeeId) EmpType
	inner join TA_Tbl_EmployeeType EType on EType.EffectiveDate=EmpType.EffectiveDate and EType.employeeid=EmpType.EmployeeId
	AND EType.EffectiveDate <=CONVERT(DATETIME,cast(getdate() as nvarchar(50)), 102)  and EType.TypeId is not null
	--select * from #EmpType
	--Employee Category insert in Temporary table
	insert into #EmpCategory select EmpCategory.EmployeeId,ECategory.CategoryId,EmpCategory.EffectiveDate from (
	select max(ec.EffectiveDate) as EffectiveDate,ec.EmployeeId as EmployeeId from TA_Tbl_EmployeeCategory ec
	inner join ta_tbl_employee emp on ec.EmployeeId = emp.Employee_Id 
	where emp.Isdeleted=0 group by EmployeeId) EmpCategory
	inner join TA_Tbl_EmployeeCategory ECategory on ECategory.EffectiveDate=EmpCategory.EffectiveDate and ECategory.employeeid=EmpCategory.EmployeeId
	AND ECategory.EffectiveDate <=CONVERT(DATETIME,cast(getdate() as nvarchar(50)), 102)   and ECategory.CategoryId is not null
		declare @sqlQuery as varchar(8000)
		set @sqlQuery=''
		
	DECLARE @dateFormat as int
	DECLARE @TimeFormat as nvarchar(25)
	Select @dateFormat=dateformat,@TimeFormat=timeformat from Func_TA_GetDateTimeFormat()
		
		set @sqlQuery = 'select MT.ManualTimeEntry_Id,ld.name,E.Employee_Code_Id,(E.Employee_FirstName + '' '' + isnull(E.Employee_LastName,'''')) as Employee_FirstName,
		
		case when '+cast(@dateFormat as varchar(100))+'=106 then REPLACE(CONVERT(VARCHAR, ManualTimeEntry_Date,'+CONVERT(varchar,@dateFormat)+'),'' '',''-'') 
			 else CONVERT(VARCHAR, ManualTimeEntry_Date, '+CONVERT(varchar,@dateFormat)+') 
		end AS ManualTimeEntry_Date,	
		case when '+cast(@dateFormat as varchar(100))+'=106 then REPLACE(CONVERT(VARCHAR, ManualTimeEntry_InDate,'+CONVERT(varchar,@dateFormat)+'),'' '',''-'') 
			 else CONVERT(VARCHAR, ManualTimeEntry_InDate, '+CONVERT(varchar,@dateFormat)+') 
		end+'' ''+
		case	when '''+CONVERT(varchar(20),@TimeFormat)+'''=''hh:mm'' then substring(CONVERT(varchar(8),DATEADD(minute,0,convert(datetime,ManualTimeEntry_InTime,0)), 114),0,6)
				else	substring(CONVERT(varchar(20),ManualTimeEntry_InTime),0,6)+'' '' + substring(CONVERT(varchar(20),ManualTimeEntry_InTime),10,2)
		end''InDateTime'',
		
		case when '+cast(@dateFormat as varchar(100))+'=106 then REPLACE(CONVERT(VARCHAR, ManualTimeEntry_OutDate,'+CONVERT(varchar,@dateFormat)+'),'' '',''-'') 
			 else CONVERT(VARCHAR, ManualTimeEntry_OutDate, '+CONVERT(varchar,@dateFormat)+') 
		end+'' ''+
		case	when '''+CONVERT(varchar(20),@TimeFormat)+'''=''hh:mm'' then substring(CONVERT(varchar(8),DATEADD(minute,0,convert(datetime,ManualTimeEntry_OutTime,0)), 114),0,6)
				else	substring(CONVERT(varchar(20),ManualTimeEntry_OutTime),0,6)+'' '' + substring(CONVERT(varchar(20),ManualTimeEntry_OutTime),10,2)
		end''OutDateTime'', 		
		
		MT.ManualTimeEntry_Status,MT.ManualTimeEntry_Reason,''0'' as SelectedRecords,MT.ManualTimeEntry_Status  from TA_TBL_Employee E '
		set @sqlQuery = @sqlQuery + ' inner join TA_TBL_ManualTimeEntry MT on  E.Employee_Code_Id=MT.ManualTimeEntry_Employee_Id '
		set @sqlQuery = @sqlQuery + ' Inner join #EmpLevel on #EmpLevel.employeeid = E.Employee_Id '
		set @sqlQuery = @sqlQuery + ' Inner join #EmpType on #EmpType.employeeid = E.Employee_Id '
		set @sqlQuery = @sqlQuery + ' Inner join #EmpCategory on #EmpCategory.employeeid = E.Employee_Id '
		set @sqlQuery = @sqlQuery + ' Inner join #LevelRights on #LevelRights.LevelId=#EmpLevel.levelID '
		set @sqlQuery = @sqlQuery + ' inner join Ta_tbl_leveldetails ld on  #EmpLevel.LevelId=ld.id ' 
		set @sqlQuery = @sqlQuery + ' inner join TA_Tbl_YearSetting ys on ys.YearSettings_Id=MT.YearSettings_Id '
		set @sqlQuery = @sqlQuery + ' where E.Isdeleted=0 and MT.IsDeleted=0 and ys.IsActive=1 and MT.IsOnDuty=0'   
		if not(@EmployeeName='')
		begin
			set @sqlQuery = @sqlQuery + ' and (E.Employee_FirstName + '' '' + isnull(E.Employee_LastName,'''') like ''' + cast(@EmployeeName+'%' as nvarchar(100)) + ''')'
		end
		if(@isReportingto > 0)
            BEGIN
               set @sqlQuery = @sqlQuery + ' and (E.Employee_ID IN (select * from #EmployeeIds)) '  
            END
		If Not (@EmpSwipeCard = '')  
		Begin  
			Set @sqlQuery = @sqlQuery + ' And E.SwipeCardNo = ''' + @EmpSwipeCard + ''' '  
		End 
		if(@EmployeeCategory<>0)
		begin
			set @sqlQuery = @sqlQuery +' and #EmpCategory.CategoryId = ' + cast(@employeeCategory as nvarchar(8))
		end
		if(@EmployeeType<>0)
		begin
			set @sqlQuery = @sqlQuery + ' and #EmpType.TypeId = ' + cast(@employeeType as nvarchar(8))
		end
		if not (@EmployeeId='')
		begin
				set @sqlQuery = @sqlQuery + ' and E.Employee_Code_Id = ''' + cast (@EmployeeId as nvarchar(50)) + ''' '
		end		
		if not(@Status is null or @Status='' or @Status='0' or @Status='All')
		begin
			set @sqlQuery = @sqlQuery + ' and (MT.ManualTimeEntry_Status IN (''' + @Status  + '''))'
		End
		if not(cast(@ManualEntry_FromDate as nvarchar(11))='' or cast(@ManualEntry_FromDate as nvarchar(11))='0')
		begin
			set @sqlQuery = @sqlQuery + ' and (( isnull(MT.ManualTimeEntry_InDate,'''+cast('1990/01/01' as nvarchar(11))+''') between ''' + cast(@ManualEntry_FromDate as nvarchar(11)) +''' and ''' + cast(@ManualEntry_ToDate as nvarchar(11)) +''') '
		End
		if not(cast(@ManualEntry_ToDate as nvarchar(11))='' or cast(@ManualEntry_ToDate as nvarchar(11))='0')
		begin
			set @sqlQuery = @sqlQuery + ' or ( isnull(MT.ManualTimeEntry_OutDate,'''+cast('1990/01/01' as nvarchar(11))+''') between  ''' + cast(@ManualEntry_FromDate as nvarchar(11)) +''' and ''' + cast(@ManualEntry_ToDate as nvarchar(11)) +''')) '
		End
           set @sqlQuery = @sqlQuery + ' order by MT.ManualTimeEntry_Date desc '
	--print(@sqlQuery) 
	exec(@sqlQuery) 
	drop table #EmpLevel
	drop table #EmpType
	drop table #EmpCategory
	drop table #LevelRights
END



GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_UpdateOvertimeHours]    Script Date: 26-08-2026 18:34:38 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
--Proc_TA_UpdateOvertimeHours  '240',0,0,'04:00','00:00','00:00',180,0,0,'03:00','00:00','00:00',2496,'787','15/04/2022',0,0,0,0,0,0,'00:00','00:00','00:00','00:00','00:00','00:00',1,'15/06/2022'
Create PROCEDURE [dbo].[Proc_TA_UpdateOvertimeHours]
(
    @intOTHrs		int,
    @intWOFOTHrs	int,
    @intHDOTFrs		int,
    @OTHours		varchar(5),
    @WOFOTHours		varchar(5),
    @HDOTHours		varchar(5),
    @intUpOTHrs		int,
    @intUpWOFOTHrs	int,
    @intUpHDOTFrs	int,
    @UpOTHours		varchar(5),
    @UpWOFOTHours	varchar(5),
    @UpHDOTHours	varchar(5),
    @Pro_RecordID	bigint,
    @Employee_code_id varchar(50),
    @Date varchar(20),
	@uploadedEmpid bigInt
)
 AS
BEGIN
	set nocount on
	UPDATE TA_Tbl_ProcessedData SET ActNormOTMins=@intOTHrs,ActOffOTMins=@intWOFOTHrs,
	ActHolOTMins=@intHDOTFrs,ActOTMins=(@intOTHrs + @intWOFOTHrs + @intHDOTFrs),ActHrsOT='00:00',
	ActNormOT=@OTHours,ActOffOT=@WOFOTHours,ActHolOT=@HDOTHours,
	NormOTMins=@intUpOTHrs,OffOTMins=@intUpWOFOTHrs,HolOTMins=@intUpHDOTFrs,
	OTMins=(@intUpOTHrs + @intUpWOFOTHrs + @intUpHDOTFrs),NormOT=@UpOTHours,OffOT=@UpWOFOTHours,
	HolOT=@UpHDOTHours WHERE Process_Rec_ID=@Pro_RecordID;
END
GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_InsertUpdateEmailScheduleDetails]    Script Date: 26-08-2026 19:53:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
--Proc_TA_InsertUpdateEmailScheduleDetails 'Test',41,'Daily','Monday','Monday','Monday',1,1,1,'10:30 AM',1,1,1,'PortableDocFormat',1
ALTER PROCEDURE [dbo].[Proc_TA_InsertUpdateEmailScheduleDetails]
(
	@ScheduleName varchar(250),
	@ReportId int,
	@Frequency varchar(20),
	@DayToSend varchar(10),
	@DayFrom varchar(10),
	@DayTo varchar(10),
	@DateToSend int,
	@DateFrom int,
	@DateTo int,
	@TimeToSend varchar(20),
	@EmployeeId int,
	@EmailSendType int,
	@Authentication int,
	@FileType varchar(20),
	@Enabled int,
	@ScheduleId int,
	@Type varchar(2),
	@ReportName varchar(50)
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	if(@Type = 'I')
	BEGIN
		if not exists(select * from TA_Tbl_EmailSchedule where ScheduleName = @ScheduleName and IsDeleted=0)
		BEGIN		
			insert into TA_Tbl_EmailSchedule (ScheduleName,ReportId,Frequency,DayToSend,DayFrom,DayTo,DateToSend,DateFrom,DateTo,TimeToSend,EmployeeId,EmailSendType,Authentication,FileType,Enabled,IsDeleted,ReportName)
			values(@ScheduleName,@ReportId,@Frequency,@DayToSend,@DayFrom,@DayTo,@DateToSend,@DateFrom,@DateTo,@TimeToSend,@EmployeeId,@EmailSendType,@Authentication,@FileType,@Enabled,0,@ReportName)
			
			select cast(@@IDENTITY as int) as InsertedId
		END
		else
		BEGIN
			select 0
		END
	END
	else if(@Type = 'U')
	BEGIN
		if not exists(select * from TA_Tbl_EmailSchedule where ScheduleName = @ScheduleName and IsDeleted=0 and ScheduleId<>@ScheduleId)
		BEGIN
			update TA_Tbl_EmailSchedule set ScheduleName = @ScheduleName,ReportId = @ReportId,Frequency = @Frequency,DayToSend = @DayToSend,
			DayFrom = @DayFrom,DayTo = @DayTo,DateToSend = @DateToSend, DateFrom = @DateFrom, DateTo = @DateTo, TimeToSend=@TimeToSend,
			EmployeeId = @EmployeeId, EmailSendType = @EmailSendType, Authentication = @Authentication, FileType = @FileType, Enabled = @Enabled,IsDeleted =0,
			ReportName=@ReportName 
			where ScheduleId = @ScheduleId 
			
			select 1
		END
		else 
		BEGIN
		    select 0
		END
	END
	
END
GO
/****** Object:  StoredProcedure [dbo].[Proc_Ta_InsertAndUpdatEmployeeDetails_EmailScheduler]    Script Date: 27-08-2026 08:02:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<R.Manikandan>
-- Create date: <04-April-2019 05:48 PM>
-- Description:	<Insert the employee details for email scheduler>
/*
Proc_Ta_InsertAndUpdatEmployeeDetails_EmailScheduler 7,
'<NewDataSet>
  <Table>
    <Employee_Id>2</Employee_Id>
    <Employee_Code_Id>659</Employee_Code_Id>
    <Employee_FirstName>اختبارات S</Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>34</RowNumber>
    <TitleName>PL (Project Leader)</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>3</Employee_Id>
    <Employee_Code_Id>956</Employee_Code_Id>
    <Employee_FirstName>Karthikeyan R</Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>6</RowNumber>
    <TitleName>TL (Team Leader)</TitleName>
    <emailScheduler_flag>1</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>4</Employee_Id>
    <Employee_Code_Id>787</Employee_Code_Id>
    <Employee_FirstName>Manikandan RR</Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>9</RowNumber>
    <TitleName>Developer</TitleName>
    <emailScheduler_flag>1</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>5</Employee_Id>
    <Employee_Code_Id>758</Employee_Code_Id>
    <Employee_FirstName>Sathishkumar S</Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>15</RowNumber>
    <TitleName>Developer</TitleName>
    <emailScheduler_flag>1</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>6</Employee_Id>
    <Employee_Code_Id>916</Employee_Code_Id>
    <Employee_FirstName>Gopinath K</Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>5</RowNumber>
    <TitleName>Developer</TitleName>
    <emailScheduler_flag>1</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>7</Employee_Id>
    <Employee_Code_Id>1010</Employee_Code_Id>
    <Employee_FirstName>Sankar </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>14</RowNumber>
    <TitleName>Developer</TitleName>
    <emailScheduler_flag>1</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>8</Employee_Id>
    <Employee_Code_Id>976</Employee_Code_Id>
    <Employee_FirstName>Anbu </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>1</RowNumber>
    <TitleName>Developer</TitleName>
    <emailScheduler_flag>1</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>9</Employee_Id>
    <Employee_Code_Id>8012</Employee_Code_Id>
    <Employee_FirstName>Manikandan V</Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>10</RowNumber>
    <TitleName>Developer</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>10</Employee_Id>
    <Employee_Code_Id>8013</Employee_Code_Id>
    <Employee_FirstName>Manoj M</Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>12</RowNumber>
    <TitleName>Developer</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>11</Employee_Id>
    <Employee_Code_Id>2500</Employee_Code_Id>
    <Employee_FirstName>mahi S</Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>8</RowNumber>
    <TitleName>Developer</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>12</Employee_Id>
    <Employee_Code_Id>1061</Employee_Code_Id>
    <Employee_FirstName>ANTANIJABARAJ </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>2</RowNumber>
    <TitleName>Developer</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>13</Employee_Id>
    <Employee_Code_Id>100101</Employee_Code_Id>
    <Employee_FirstName>ANTANIJABARAJ </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>3</RowNumber>
    <TitleName>Developer</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>14</Employee_Id>
    <Employee_Code_Id>100102</Employee_Code_Id>
    <Employee_FirstName>Antanijabaraj J</Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>4</RowNumber>
    <TitleName>Jr Developer_Angler</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>17</Employee_Id>
    <Employee_Code_Id>100</Employee_Code_Id>
    <Employee_FirstName>TCS Jeffrin </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>20</RowNumber>
    <TitleName>TCS Sr Developer</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>18</Employee_Id>
    <Employee_Code_Id>101</Employee_Code_Id>
    <Employee_FirstName>TCS Edwin </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>18</RowNumber>
    <TitleName>TCS Jr Developer</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>19</Employee_Id>
    <Employee_Code_Id>102</Employee_Code_Id>
    <Employee_FirstName>TCS David </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>17</RowNumber>
    <TitleName>TCS Jr Developer</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>20</Employee_Id>
    <Employee_Code_Id>103</Employee_Code_Id>
    <Employee_FirstName>TCS Babu </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>16</RowNumber>
    <TitleName>TCS Jr Developer</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>21</Employee_Id>
    <Employee_Code_Id>104</Employee_Code_Id>
    <Employee_FirstName>TCS Raja </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>24</RowNumber>
    <TitleName>TCS Jr Developer</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>22</Employee_Id>
    <Employee_Code_Id>105</Employee_Code_Id>
    <Employee_FirstName>TCS Kevin </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>21</RowNumber>
    <TitleName>TCS Jr Developer</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>23</Employee_Id>
    <Employee_Code_Id>106</Employee_Code_Id>
    <Employee_FirstName>TCS Esther </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>19</RowNumber>
    <TitleName>TCS Jr Developer</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>24</Employee_Id>
    <Employee_Code_Id>107</Employee_Code_Id>
    <Employee_FirstName>TCS Suresh </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>26</RowNumber>
    <TitleName>TCS Jr Developer</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>25</Employee_Id>
    <Employee_Code_Id>108</Employee_Code_Id>
    <Employee_FirstName>TCS Peter </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>22</RowNumber>
    <TitleName>TCS Jr Developer</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>26</Employee_Id>
    <Employee_Code_Id>109</Employee_Code_Id>
    <Employee_FirstName>TCS Poorni </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>23</RowNumber>
    <TitleName>TCS Jr Developer</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>27</Employee_Id>
    <Employee_Code_Id>110</Employee_Code_Id>
    <Employee_FirstName>TCS Sindu </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>25</RowNumber>
    <TitleName>TCS Jr Developer</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>28</Employee_Id>
    <Employee_Code_Id>1000</Employee_Code_Id>
    <Employee_FirstName>Kavin </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>7</RowNumber>
    <TitleName>Jr Developer_Angler</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>29</Employee_Id>
    <Employee_Code_Id>2000</Employee_Code_Id>
    <Employee_FirstName>UserPM </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>33</RowNumber>
    <TitleName>Project Manager</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>30</Employee_Id>
    <Employee_Code_Id>2001</Employee_Code_Id>
    <Employee_FirstName>UserPL </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>32</RowNumber>
    <TitleName>Project Lead</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>31</Employee_Id>
    <Employee_Code_Id>2002</Employee_Code_Id>
    <Employee_FirstName>UserJrA </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>29</RowNumber>
    <TitleName>Jr Software Engineer</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>32</Employee_Id>
    <Employee_Code_Id>2003</Employee_Code_Id>
    <Employee_FirstName>UserJrB </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>30</RowNumber>
    <TitleName>Jr Software Engineer</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>33</Employee_Id>
    <Employee_Code_Id>2004</Employee_Code_Id>
    <Employee_FirstName>UserJrC </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>31</RowNumber>
    <TitleName>Jr Software Engineer</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>34</Employee_Id>
    <Employee_Code_Id>1478</Employee_Code_Id>
    <Employee_FirstName>Probation Period </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>13</RowNumber>
    <TitleName>Developer</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>35</Employee_Id>
    <Employee_Code_Id>1479</Employee_Code_Id>
    <Employee_FirstName>TEsting maximum entry </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>28</RowNumber>
    <TitleName>Developer</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>36</Employee_Id>
    <Employee_Code_Id>1592</Employee_Code_Id>
    <Employee_FirstName>Manikandan </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>11</RowNumber>
    <TitleName>PL (Project Leader)</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
  <Table>
    <Employee_Id>37</Employee_Id>
    <Employee_Code_Id>7536</Employee_Code_Id>
    <Employee_FirstName>test </Employee_FirstName>
    <Employee_Status>Active</Employee_Status>
    <Isdeleted>False</Isdeleted>
    <RowNumber>27</RowNumber>
    <TitleName>Developer</TitleName>
    <emailScheduler_flag>0</emailScheduler_flag>
  </Table>
</NewDataSet>'
*/
-- =============================================
Create PROCEDURE [dbo].[Proc_Ta_InsertAndUpdatEmployeeDetails_EmailScheduler]
	@InsertedId int,
	@ShiftData XML = NULL
AS
BEGIN	
	SET NOCOUNT ON;
	DECLARE @XMLParams INT, @YearSettingId INT, @NosId INT
	DECLARE @ErrMsg VARCHAR(MAX), @ErrIds VARCHAR(MAX)
	DECLARE @StartDate DATETIME
	DECLARE @ErrorMsg TABLE(Employee_Id VARCHAR(100), [Year] VARCHAR(10), [Month] VARCHAR(20), Error_Msg VARCHAR(MAX))		
	DELETE FROM [TA_Tbl_ImportShiftAllocation]
	Create table #Temp_TA_Tbl_EmailScheduled_Employees(ScheduleId bigint,Employee_id bigint,flag int)
	EXEC SP_XML_PrePareDocument @XMLParams OUTPUT, @ShiftData
	Delete from TA_Tbl_EmailScheduled_Employees where ScheduleId=@InsertedId
	INSERT INTO TA_Tbl_EmailScheduled_Employees
				SELECT @InsertedId,Employee_Id
				FROM OPENXML (@XMLParams, 'NewDataSet/Table', 2) WITH (Employee_Id VARCHAR(50), emailScheduler_flag int) Tbl
				Where emailScheduler_flag=1
END
