ALTER PROCEDURE [dbo].[Proc_TA_Rpt_LeaveSummaryDetails] 
(
	@fromDat datetime,
	@ToDat datetime,
	@empId varchar(100),
	@shift int,
	@EmployeeName varchar(50),
	@LeaveType varchar(50),
	@LeaveStatus varchar(30),
	@employeeCategory int,
	@employeeType int,
	@UserGroupName varchar(250),
	@GradeLevel varchar(20)	,
	@EmpSwipeCard varchar(20),
	@ParentId int,
	@DesignationID int,
	@Gender varchar(10),
	@EmpAutoID int
)
AS
BEGIN
	SET NOCOUNT ON;
	create table #EmpLevel(EmployeeId int,LevelId int,EffectiveDate datetime,Employee_Code_Id varchar(50),SwipeCard varchar(50))
	create table #EmpDesignation(EmployeeId int,DesignationId int,EffectiveDate datetime,Employee_Code_Id varchar(50),SwipeCard varchar(50))
	create table #EmpType(EmployeeId int,TypeId int,EffectiveDate datetime,Employee_Code_Id varchar(50),SwipeCard varchar(50))
	create table #EmpCategory(EmployeeId int,CategoryId int,EffectiveDate datetime,Employee_Code_Id varchar(50),SwipeCard varchar(50))
	create table #LevelRights(LevelId int)
	--Marian
	Create Table #TempLeaveType (ID int identity(1,1), LeaveType varchar(50))
	insert into #TempLeaveType (LeaveType)
	select LeaveType from TA_Tbl_LeaveType order by LeaveType_Id
	--Drop Table #TempLeaveType
	--Marian
	
	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](@EmpAutoID) as EmpAutoId  
    insert into  #EmployeeIds select * from [dbo].[fn_TA_GetMappedEmployees_SancAuthority](@EmpAutoID) 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],[LevelId],[EmployeeId] ASC
	) ON [PRIMARY]
	--Employee Designation Indexes
	/****** Object:  Index [IDX_Effective]    Script Date: 05/27/2009 16:28:22 ******/
	CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpDesignation] 
	(
		[EffectiveDate],[DesignationId],[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],[TypeId],[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],[CategoryId],[EmployeeId] ASC
	)ON [PRIMARY]
	insert into #EmpDesignation exec Proc_TA_EmpDesignation @todat,@empId,@DesignationID	
	insert into #EmpType exec [Proc_TA_EmpType] @todat,@empId,@employeeType	
	insert into #EmpCategory exec [Proc_TA_EmpCategory] @todat,@empId,@employeeCategory	
	insert into #EmpLevel exec  [Proc_TA_EmpLevel] @todat,@empId
	if(@EmployeeName is not null and @EmployeeName <> '')
		begin
			create table #EmployeeName(EmployeeID varchar(25))
			insert into #EmployeeName
			select [Value] from dbo.[SplitUpEmployeeName](@EmployeeName,',')
		end
	create table #TempLeaveDetails 
	(
		Employee_Id bigint,
		Employee_Code_Id nvarchar(15),
		Employee_Name nvarchar(150),
		Designation nvarchar(250),
		PolicyName nvarchar(50),
		FromDate datetime,
		ToDate datetime,
		LeaveType_Id int,
		LeaveType_Name nvarchar(10),
		Leave_Days decimal(18,1),
		Leave_Days_Availed_Approved decimal(18,1),
		Leave_Days_Availed_InProgress decimal(18,1),
		Leave_Days_Remaining decimal(18,1),
		LOP_Days decimal(18,1),
		LOP_Days_Availed_Approved decimal(18,1),
		LOP_Days_Availed_InProgress decimal(18,1),
		LOP_Days_Remaining decimal(18,1),
		RestrictedHoliday decimal(18,1),
		RestrictedHoliday_Availed_Approved decimal(18,1),
		RestrictedHoliday_Availed_InProgress decimal(18,1),
		RestrictedHoliday_Remaining decimal(18,1),
		YearSettingsId int
	)

    declare @sqlQuery varchar(8000)
    set @sqlQuery = ''
	set @sqlQuery = @sqlQuery + 'insert into #TempLeaveDetails '
	set @sqlQuery = @sqlQuery + '(Employee_Id,Employee_Code_Id,Employee_Name,Designation,PolicyName,FromDate,ToDate,LeaveType_Id,LeaveType_Name, '
	set @sqlQuery = @sqlQuery + 'Leave_Days ,LOP_Days,RestrictedHoliday,Leave_Days_Availed_Approved,Leave_Days_Availed_InProgress,Leave_Days_Remaining, '
	set @sqlQuery = @sqlQuery + 'LOP_Days_Availed_Approved,LOP_Days_Availed_InProgress,LOP_Days_Remaining,RestrictedHoliday_Availed_Approved,RestrictedHoliday_Availed_InProgress,RestrictedHoliday_Remaining,YearSettingsId) '
	set @sqlQuery = @sqlQuery + 'select E.Employee_Id,E.Employee_Code_Id,isnull(E.Employee_FirstName,'''') + space(1) + isnull(E.Employee_lastName,''''),replace(TA_Tbl_Title.TitleName,''&amp;'',''&''),LP.PolicyName,Emp_LP.FromDate,Emp_LP.ToDate, '
	set @sqlQuery = @sqlQuery + 'LPD.LeaveType_Id,LT.LeaveType,case when YS.YearSettings_Id = empltd.YearSettingsId then Emp_LPD.No_Of_Days + empltd.PreviousYearBalance	     
	else Emp_LPD.No_Of_Days end,LPD.Max_LOP_Days,HS.Yearly_Maxdays as RestrictedHoliday,case when YS.YearSettings_Id = empltd.YearSettingsId then empltd.NoOffLeaveAvailed
	else 0 end,	0,0,0,0,0,0,0,0,ys.YearSettings_Id '
	set @sqlQuery = @sqlQuery + 'from TA_Tbl_Employee E '
		If Not (@EmployeeName = '')  
		Begin  
			SET @sqlQuery = @sqlQuery + ' Inner join #EmployeeName on #EmployeeName.EmployeeID=E.Employee_Code_Id '
		End
	set @sqlQuery = @sqlQuery + 'Inner join #EmpLevel on #EmpLevel.employeeid = E.Employee_Id '
	set @sqlQuery = @sqlQuery + 'Inner join Ta_Tbl_levelDetails on #EmpLevel.LevelId = Ta_Tbl_levelDetails.id '
	set @sqlQuery = @sqlQuery + 'Inner join #EmpCategory on #EmpCategory.employeeid = E.Employee_Id '
	set @sqlQuery = @sqlQuery + 'Inner join #EmpType on #EmpType.employeeid = E.Employee_Id '
	set @sqlQuery = @sqlQuery + 'Inner join #EmpDesignation on #EmpDesignation.employeeid = E.Employee_Id '
   	set @sqlQuery = @sqlQuery + 'INNER JOIN TA_Tbl_Title ON #EmpDesignation.DesignationId = dbo.TA_Tbl_Title.TitleId '
	set @sqlQuery = @sqlQuery + 'INNER JOIN TA_Tbl_Grade ON dbo.TA_Tbl_Title.GradeId = dbo.TA_Tbl_Grade.Grade_ID '
	set @sqlQuery = @sqlQuery + 'Inner join #LevelRights on #LevelRights.LevelId=#EmpLevel.LevelId '
	set @sqlQuery = @sqlQuery + 'inner join TA_Tbl_Emp_LeavePolicy Emp_LP on Emp_LP.employeeId=E.Employee_Id '
	set @sqlQuery = @sqlQuery + 'inner join TA_Tbl_LeavePolicy LP on LP.Policy_Id = Emp_LP.LeavePolicy_Id '
	set @sqlQuery = @sqlQuery + 'inner join TA_Tbl_YearSetting YS on YS.YearSettings_Id = Emp_LP.YearSettingId '
	set @sqlQuery = @sqlQuery + 'inner join TA_Tbl_LeavePolicyDetails LPD on LPD.Policy_Id=Emp_LP.LeavePolicy_Id '
	set @sqlQuery = @sqlQuery + 'inner join TA_Tbl_LeaveType LT on LT.LeaveType_Id = LPD.LeaveType_Id  '
	set @sqlQuery = @sqlQuery + 'inner join TA_Tbl_Emp_LeavePolicyDetails Emp_LPD on Emp_LPD.Emp_LeavePolicyId = Emp_LP.Emp_LeavePolicyId '
	set @sqlQuery = @sqlQuery + 'and Emp_LPD.LeaveType_Id = LPD.LeaveType_Id '
	set @sqlQuery = @sqlQuery + 'left join TA_Tbl_HolidaySettings HS on HS.YearSettingId = YS.YearSettings_Id '
	set @sqlQuery = @sqlQuery + 'inner join ta_tbl_emp_LeaveTypedetails empltd on (empltd.employeeid = Emp_LP.EmployeeId and empltd.leavetypeid=lpd.leavetype_id and empltd.YearSettingsId= YS.YearSettings_Id)'
	set @sqlQuery = @sqlQuery + 'where E.IsDeleted =0 and E.Employee_Code_Id<>''18000000'' and E.Employee_Status = 1 '
	set @sqlQuery = @sqlQuery + 'and YS.IsActive=1  '
	if(@UserGroupName<>'SuperAdmin')
		begin
			SET @sqlQuery = @sqlQuery + ' AND TA_Tbl_Grade.Grade_Level>=' + @GradeLevel  
		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(20))
		end
	if(@employeeType<>0)
		begin
			set @sqlQuery = @sqlQuery + ' and #EmpType.TypeID = ' + Cast(@employeeType  as nvarchar(20))
		end
	if(@DesignationID<>0)
		begin
		   set @sqlQuery = @sqlQuery + ' and #EmpDesignation.DesignationId = ' + cast(@DesignationID as nvarchar(8))
		end	
	if(@Gender<>'Both')
		begin
		   set @sqlQuery = @sqlQuery + ' and E.Gender = ''' + cast(@Gender as nvarchar(8)) + ''' '
		end
	if(@isReportingto > 0)
            BEGIN
               set @sqlQuery = @sqlQuery + ' and (E.Employee_ID IN (select * from #EmployeeIds)) '  
            END
		 set @sqlQuery = @sqlQuery + ' order by Employee_Id '
	exec(@sqlQuery)


	declare @tablecoulm table (id int identity(1,1), CoulmnName nvarchar(100))

	/*Marian*/
	Declare @LeaveTypeCount int
	Declare @count int=1
    Set @LeaveTypeCount=0
	Select @LeaveTypeCount= COUNT(*) From #TempLeaveType


	While @count<= @LeaveTypeCount
	Begin
		Declare @SqlQuery1 Varchar(MAX)
		Declare @leaveTypeColName varchar(50)
		set @leaveTypeColName=''
		Select @leaveTypeColName=LeaveType From #TempLeaveType Where ID=@count
		Set @SqlQuery1='Alter Table #TempLeaveDetails Add ['+@leaveTypeColName + '-TK' +'] FLOAT'
		Execute (@SqlQuery1)

		set @SqlQuery1=''
		Set @SqlQuery1='Alter Table #TempLeaveDetails Add ['+@leaveTypeColName + '-AV'+'] FLOAT'
		Execute (@SqlQuery1)

		set @SqlQuery1=''
		Set @SqlQuery1='Alter Table #TempLeaveDetails Add ['+@leaveTypeColName + '-BL'+'] FLOAT'
		Execute (@SqlQuery1)
		insert into @tablecoulm values (@leaveTypeColName)
		set @count=@count+1
	End
	
	declare @loop int=1,@LCount int=0
	select @LCount=Count(*) from @tablecoulm

	while(@LCount>=@loop)
		begin
			declare @LeaveTypeName nvarchar(10)
			select @LeaveTypeName=CoulmnName from @tablecoulm where id=@loop
			declare @LeaveUpdate nvarchar(MAX)=''
			select @LeaveUpdate='update #TempLeaveDetails set ['+ @LeaveTypeName +'-TK] =Leave_Days_Availed_Approved+ A.NumberOfDays from
			(
				select LA.Employee_Id,sum(LA.NumberOfDays) as NumberOfDays,LA.LeaveType_id from TA_Tbl_LeaveApplication LA
				inner join #TempLeaveDetails LD on LD.Employee_Id = LA.Employee_Id
				inner join TA_Tbl_LeaveType LT on LT.LeaveType_id=LA.LeaveType_id and LT.LeaveType='''+ @LeaveTypeName +'''
				and LD.LeaveType_Id = LA.LeaveType_id 
				where LA.type=''Leave'' and LA.Status in (''Pending'',''Forwarded'',''Approved'') and LA.isdeleted=0 and
				(LA.Leave_FromDate between LD.FromDate and LD.ToDate)
				and (LA.Leave_ToDate between LD.FromDate and LD.ToDate)
				group by  LA.Employee_Id,LA.LeaveType_id
			) as A inner join #TempLeaveDetails on (#TempLeaveDetails.Employee_Id = A.Employee_Id and #TempLeaveDetails.LeaveType_id = A.LeaveType_id)'
			EXEC (@LeaveUpdate)

			select @LeaveUpdate =''
			select @LeaveUpdate='update #TempLeaveDetails set ['+ @LeaveTypeName +'-AV]=case when Carry_PrevYear<>0 then Leave_Days+Carry_PrevYear
				else Leave_Days end from #TempLeaveDetails 
				inner join TA_Tbl_LeaveType LT on LT.LeaveType_id=#TempLeaveDetails.LeaveType_id and LT.LeaveType='''+ @LeaveTypeName +'''
				inner join TA_Tbl_YearClosure yc on (#TempLeaveDetails.YearSettingsId=yc.Year_SettingID and #TempLeaveDetails.LeaveType_Id=yc.LeaveTypeID)
				and #TempLeaveDetails.Employee_Id=yc.Employee_Id '
			EXEC (@LeaveUpdate)

			select @LeaveUpdate =''
			select @LeaveUpdate='update #TempLeaveDetails set ['+ @LeaveTypeName +'-BL] = (['+ @LeaveTypeName +'-AV] -['+ @LeaveTypeName +'-TK])'
			EXEC (@LeaveUpdate)

			select @loop=@loop+1 
		end

		--select * from #TempLeaveDetails

	create table #TempLeaveDetails_Final 
	(
	Employee_Code_Id nvarchar(15),
	Employee_Name nvarchar(150),
	Designation nvarchar(250),
	PolicyName nvarchar(50)
	)
	insert into #TempLeaveDetails_Final(Employee_Code_Id, Employee_Name, Designation, PolicyName)
	select distinct Employee_Code_Id,Employee_Name,Designation,PolicyName from #TempLeaveDetails

	DECLARE @dateFormat as int
	SET @dateFormat=(Select DateFormat from Func_TA_GetDateTimeFormat())
	
	Declare @LeaveTypeCountFL int
	Declare @countFL int=1
    Set @LeaveTypeCountFL=0
	Select @LeaveTypeCountFL= COUNT(*) From #TempLeaveType


	While @countFL<= @LeaveTypeCountFL
	Begin
		Declare @SqlQuery1FL Varchar(MAX)
		Declare @leaveTypeColNameFL varchar(50)
		set @leaveTypeColNameFL=''
		Select @leaveTypeColNameFL=LeaveType From #TempLeaveType Where ID=@countFL
		Set @SqlQuery1FL='Alter Table #TempLeaveDetails_Final Add ['+@leaveTypeColNameFL + '-TK' +'] FLOAT'
		Execute (@SqlQuery1FL)

		set @SqlQuery1FL=''
		Set @SqlQuery1FL='Alter Table #TempLeaveDetails_Final Add ['+@leaveTypeColNameFL + '-AV'+'] FLOAT'
		Execute (@SqlQuery1FL)

		set @SqlQuery1FL=''
		Set @SqlQuery1FL='Alter Table #TempLeaveDetails_Final Add ['+@leaveTypeColNameFL + '-BL'+'] FLOAT'
		Execute (@SqlQuery1FL)

		set @SqlQuery1FL=''
		set @SqlQuery1FL='update T set T.['+@leaveTypeColNameFL + '-TK'+']=S.['+@leaveTypeColNameFL + '-TK'+']
		from #TempLeaveDetails_Final T 
		inner join #TempLeaveDetails S on T.Employee_Code_Id=S.Employee_Code_Id and S.['+@leaveTypeColNameFL + '-TK'+'] is not null '
		Execute (@SqlQuery1FL)
		
		set @SqlQuery1FL=''
		set @SqlQuery1FL='update T set T.['+@leaveTypeColNameFL + '-AV'+']=S.['+@leaveTypeColNameFL + '-AV'+']
		from #TempLeaveDetails_Final T 
		inner join #TempLeaveDetails S on T.Employee_Code_Id=S.Employee_Code_Id and S.['+@leaveTypeColNameFL + '-AV'+'] is not null '
		Execute (@SqlQuery1FL)

		set @SqlQuery1FL=''
		set @SqlQuery1FL='update T set T.['+@leaveTypeColNameFL + '-BL'+']=S.['+@leaveTypeColNameFL + '-BL'+']
		from #TempLeaveDetails_Final T 
		inner join #TempLeaveDetails S on T.Employee_Code_Id=S.Employee_Code_Id and S.['+@leaveTypeColNameFL + '-BL'+'] is not null '
		Execute (@SqlQuery1FL)


		
		set @SqlQuery1FL=''
		set @SqlQuery1FL='update #TempLeaveDetails_Final set ['+@leaveTypeColNameFL + '-AV'+']=0 where ['+@leaveTypeColNameFL + '-AV'+'] is null'
		Execute (@SqlQuery1FL)

		set @SqlQuery1FL=''
		set @SqlQuery1FL='update #TempLeaveDetails_Final set ['+@leaveTypeColNameFL + '-TK'+']=0 where ['+@leaveTypeColNameFL + '-TK'+'] is null'
		Execute (@SqlQuery1FL)

		set @SqlQuery1FL=''
		set @SqlQuery1FL='update #TempLeaveDetails_Final set ['+@leaveTypeColNameFL + '-BL'+']=0 where ['+@leaveTypeColNameFL + '-BL'+'] is null'
		Execute (@SqlQuery1FL)


		insert into @tablecoulm values (@leaveTypeColNameFL)
		set @countFL=@countFL+1

	End

	
	
	select *
	--Employee_Code_Id,
	--Employee_Name,
	--Designation,
	--PolicyName,
	--REPLACE(CONVERT(VARCHAR(20), FromDate,@dateFormat),' ','-'),
	--REPLACE(CONVERT(VARCHAR(20), ToDate,@dateFormat),' ','-'),
	--LeaveType_Name,
	--Leave_Days,
	--Leave_Days_Availed_Approved,
	--Leave_Days_Availed_InProgress,
	--Leave_Days_Remaining
	from #TempLeaveDetails_Final order by Employee_Code_Id--,LeaveType_Name
	drop table #TempLeaveDetails
	drop table #TempLeaveDetails_Final
	drop table #EmpLevel
	drop table #EmpDesignation
	drop table #EmpType
	drop table #EmpCategory
	drop table #LevelRights
	drop table #TempLeaveType
	drop table #EmployeeIds
END

-----------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[Proc_TA_LeaveBalanceImport]
	@LeaveBalanceData 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 ImportFromExcel
	EXEC SP_XML_PrePareDocument @XMLParams OUTPUT, @LeaveBalanceData
	INSERT INTO ImportFromExcel	
	SELECT	ROW_NUMBER()over(order by (Cast(EmployeeID as Numeric))),
			EmployeeID,
			convert(float,isnull(LeaveType1,0.0)),
			convert(float,isnull(LeaveType2,0.0)),
			convert(float,isnull(LeaveType3,0.0)),
			convert(float,isnull(LeaveType4,0.0)),
			convert(float,isnull(LeaveType5,0.0)),
			convert(float,isnull(LeaveType6,0.0)),
			convert(float,isnull(LeaveType7,0.0)),
			convert(float,isnull(LeaveType8,0.0)),
			convert(float,isnull(LeaveType9,0.0)),
			convert(float,isnull(LeaveType10,0.0)),
			convert(float,isnull(LeaveType11,0.0)),
			convert(float,isnull(LeaveType12,0.0)),
			convert(float,isnull(LeaveType13,0.0)),
			convert(float,isnull(LeaveType14,0.0)),
			convert(float,isnull(LeaveType15,0.0)),
			convert(float,isnull(LeaveType16,0.0)),
			convert(float,isnull(LeaveType17,0.0)),
			convert(float,isnull(LeaveType18,0.0)),
			convert(float,isnull(LeaveType19,0.0)),
			convert(float,isnull(LeaveType20,0.0)),
			convert(float,isnull(LeaveType21,0.0)),
			convert(float,isnull(LeaveType22,0.0)),
			convert(float,isnull(LeaveType23,0.0)),
			convert(float,isnull(LeaveType24,0.0)),
			convert(float,isnull(LeaveType25,0.0)),
			convert(float,isnull(LeaveType26,0.0)),
			convert(float,isnull(LeaveType27,0.0)),
			convert(float,isnull(LeaveType28,0.0)),
			convert(float,isnull(LeaveType29,0.0)),
			convert(float,isnull(LeaveType30,0.0))
			
				
	FROM OPENXML (@XMLParams, '/dsLeaveBalance/TblLeaveBalance', 2) WITH 
	(		
		[Sl.No]	int,
		EmployeeID	varchar(50),
		LeaveType1	varchar(50),
		LeaveType2	varchar(50),
		LeaveType3	varchar(50),
		LeaveType4	varchar(50),
		LeaveType5	varchar(50),
		LeaveType6	varchar(50),
		LeaveType7	varchar(50),
		LeaveType8	varchar(50),
		LeaveType9	varchar(50),
		LeaveType10 varchar(50),
		LeaveType11 varchar(50),
		LeaveType12 varchar(50),
		LeaveType13 varchar(50),
		LeaveType14 varchar(50),
		LeaveType15 varchar(50),
		LeaveType16 varchar(50),
		LeaveType17 varchar(50),
		LeaveType18 varchar(50),
		LeaveType19 varchar(50),
		LeaveType20 varchar(50),
		LeaveType21 varchar(50),
		LeaveType22 varchar(50),
		LeaveType23 varchar(50),
		LeaveType24 varchar(50),
		LeaveType25 varchar(50),
		LeaveType26 varchar(50),
		LeaveType27 varchar(50),
		LeaveType28 varchar(50),
		LeaveType29 varchar(50),
		LeaveType30 varchar(50)
	) Tbl
	--LEFT OUTER JOIN TA_Tbl_Employee E ON E.Employee_Code_Id = Tbl.Employeeid
	--AND E.IsDeleted = 0 AND E.Employee_Status = 1
	
	Execute Proc_TA_BulkImportPrevLeaveBalance
	
END

-----------------------------------------------------------------------------------------------

ALTER PROCEDURE [dbo].[Proc_TA_BulkImportPrevLeaveBalance]
AS
BEGIN
	SET NOCOUNT on;	
	Declare @YearSetID int
	Declare @Count int
	Declare @Start int
	set @Start=1
	--select * from TA_Tbl_Emp_LeaveTypeDetails
	select @YearSetID=YearSettings_Id from TA_Tbl_YearSetting where IsActive=1
	select @Count=COUNT(*) from ImportFromExcel	
	--select * from ImportFromExcel
	Create Table #TempLeaveType (ID int identity(1,1), LeaveType_Id int)
	While @Count>=@Start
	BEGIN		

		Declare @EmpAutoID bigint
		Declare @Employeeid  varchar(50)
		Declare @LeaveType1  decimal(18,1)
		Declare @LeaveType2  decimal(18,1)
		Declare @LeaveType3  decimal(18,1)
		Declare @LeaveType4  decimal(18,1)
		Declare @LeaveType5  decimal(18,1)
		Declare @LeaveType6  decimal(18,1)
		Declare @LeaveType7  decimal(18,1)
		Declare @LeaveType8  decimal(18,1)
		Declare @LeaveType9  decimal(18,1)
		Declare @LeaveType10 decimal(18,1)
		Declare @LeaveType11 decimal(18,1)
		Declare @LeaveType12 decimal(18,1)
		Declare @LeaveType13 decimal(18,1)
		Declare @LeaveType14 decimal(18,1)
		Declare @LeaveType15 decimal(18,1)
		Declare @LeaveType16 decimal(18,1)
		Declare @LeaveType17 decimal(18,1)
		Declare @LeaveType18 decimal(18,1)
		Declare @LeaveType19 decimal(18,1)
		Declare @LeaveType20 decimal(18,1)
		Declare @LeaveType21 decimal(18,1)
		Declare @LeaveType22 decimal(18,1)
		Declare @LeaveType23 decimal(18,1)
		Declare @LeaveType24 decimal(18,1)
		Declare @LeaveType25 decimal(18,1)
		Declare @LeaveType26 decimal(18,1)
		Declare @LeaveType27 decimal(18,1)
		Declare @LeaveType28 decimal(18,1)
		Declare @LeaveType29 decimal(18,1)
		Declare @LeaveType30 decimal(18,1)
		
		set @Employeeid=''
		set @LeaveType1=0 
		set @LeaveType2=0 
		set @LeaveType3=0 
		set @LeaveType4=0 
		set @LeaveType5=0 
		set @LeaveType6=0 
		set @LeaveType7=0
		set @LeaveType8=0 
		set @LeaveType9=0
		set @LeaveType10=0 
		set @LeaveType11=0 
		set @LeaveType12=0
		set @LeaveType13=0
		set @LeaveType14=0 
		set @LeaveType15=0
		set @LeaveType16=0 
		set @LeaveType17=0 
		set @LeaveType18=0 
		set @LeaveType19=0 
		set @LeaveType20=0 
		set @LeaveType21=0 
		set @LeaveType22=0
		set @LeaveType23=0 
		set @LeaveType24=0
		set @LeaveType25=0 
		set @LeaveType26=0 
		set @LeaveType27=0
		set @LeaveType28=0
		set @LeaveType29=0 
		set @LeaveType30=0
		
		select 
		@Employeeid=Employeeid,
		@LeaveType1=LeaveType1,
		@LeaveType2=LeaveType2,
		@LeaveType3=LeaveType3, 
		@LeaveType4=LeaveType4,
		@LeaveType5=LeaveType5,
		@LeaveType6=LeaveType6,
		@LeaveType7=LeaveType7,
		@LeaveType8=LeaveType8,
		@LeaveType9=LeaveType9,
		@LeaveType10=LeaveType10, 
		@LeaveType11=LeaveType11,
		@LeaveType12=LeaveType12,
		@LeaveType13=LeaveType13,
		@LeaveType14=LeaveType14,
		@LeaveType15=LeaveType15,
		@LeaveType16=LeaveType16,
		@LeaveType17=LeaveType17,
		@LeaveType18=LeaveType18, 
		@LeaveType19=LeaveType19,
		@LeaveType20=LeaveType20,
		@LeaveType21=LeaveType21,
		@LeaveType22=LeaveType22,
		@LeaveType23=LeaveType23,
		@LeaveType24=LeaveType24,
		@LeaveType25=LeaveType25, 
		@LeaveType26=LeaveType26,
		@LeaveType27=LeaveType27,
		@LeaveType28=LeaveType28,
		@LeaveType29=LeaveType29,
		@LeaveType30=LeaveType30
					
		from ImportFromExcel where [Sl.No]=@Start
		
	
		
		insert into #TempLeaveType (LeaveType_Id)
		select LeaveType_Id from TA_Tbl_LeaveType order by LeaveType_Id

		select @EmpAutoID=Employee_Id from TA_Tbl_Employee where Employee_Code_Id=@Employeeid and Isdeleted=0
		Declare @i int
		set @i=1
		if(@EmpAutoID is not null and @EmpAutoID<>'')
		begin
			Declare @LeaveTypeID int
			--1
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=1 --order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType1,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END
			end
			---2
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=2 --order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType2,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---3
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=3 --order by LeaveType_Id			
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType3,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---4
			set @LeaveTypeID=0							
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=4 --order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType4,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---5
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=5 -- order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType5,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---6
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=6 order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType6,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---7
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=7 -- order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType7,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---8
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=8 -- order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType8,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---9
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=9 --order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType9,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---10
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=10 --order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType10,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---11
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=11 --order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType11,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---12
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=12 --order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType12,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---13
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=13 -- order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType13,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---14
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=14 --order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType14,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---15
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=15 --order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType15,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			--16
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=16 --order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType16,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END
			end
			---17
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=17-- order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType17,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---18
			--set @LeaveTypeID=0
			--select @LeaveTypeID=LeaveType_Id from TA_Tbl_LeaveType where LeaveType_Id=18 order by LeaveType_Id			
			--print @LeaveTypeID
			--if(isnull(@LeaveTypeID,'')!='')
			--begin
			--	IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
			--	BEGIN
			--		print @YearSetID
			--		update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType18,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
			--	END	
			--end
			set @LeaveTypeID=0
			Select @LeaveTypeID=LeaveType_Id From #TempLeaveType where ID=18
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType18,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---19
			set @LeaveTypeID=0	
									
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=19 --order by LeaveType_Id

			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType19,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---20
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=20 --order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType20,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---21
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=21 --order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType21,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---22
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=22 -- order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType22,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---23
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=23  --order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType23,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---24
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=24 --order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType24,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---25
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=25 --order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType25,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---26
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=26 --order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType26,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---27
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=27 --order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType27,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---28
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=28 --order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType28,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---29
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=29 --order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType29,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
			---30
			set @LeaveTypeID=0				
			select @LeaveTypeID=LeaveType_Id from #TempLeaveType where ID=30 --order by LeaveType_Id
			if(isnull(@LeaveTypeID,'')!='')
			begin
				IF EXISTS(SELECT * FROM TA_Tbl_YearClosure where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID)
				BEGIN
					update TA_Tbl_YearClosure set Carry_PrevYear=isnull(@LeaveType30,0) where Employee_Id=@EmpAutoID and Year_SettingID=@YearSetID and LeaveTypeId=@LeaveTypeID
				END	
			end
		end
		set @Start=@Start+1 
	END
	
	delete from ImportFromExcel

	drop table #TempLeaveType
	select 1
END

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


ALTER PROCEDURE [dbo].[Proc_Ta_GetParentLevelidUsingEmpid]
	-- Add the parameters for the stored procedure here
	@EmployeeAutoID int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    DECLARE @SQLEMPLEVEL NVARCHAR(4000)
	CREATE TABLE #EMPLEVEL
	(
		EMPLOYEEID INT,
		LEVELID INT,
		EFFECTIVEDATE DATETIME,	
		SWIPECARD VARCHAR(50)
	)
	Declare @levelId int	
	set @levelId=
	(
		SELECT top 1 EL.LEVELID 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 and EMP.Employee_id=@EmployeeAutoID
			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
	)
	while((select parentId from Ta_Tbl_levelDetails where id=@levelId)!=0)
	begin
		select @levelId=parentId from Ta_Tbl_levelDetails where id=@levelId
		print @levelId
	end
	
	Select isNull(@levelId,0)
	
END

----------------------------------------------------------------------------------------------------------------------------------

ALTER PROCEDURE [dbo].[Proc_TA_SelectEmployeeDetails_LeaveApplication] 
(  
	@EmployeeId INT=0 
)  
AS
BEGIN
	DECLARE @ActiveYearId INT
	DECLARE @EffectiveDate datetime
	DECLARE @EffectBPayDate datetime
	DECLARE @EffectLevelDate datetime
	DECLARE @EffectCategoryDate datetime
	DECLARE @EffectTypeDate datetime
	---Approval matrix concept
	DECLARE @LeveID bigint
	Declare @Approver1 int
	Declare @Approver1Name varchar(500)
	--Declare @Approver2 int
	--Declare @Approver3 int
	--Declare @Approver4 int
	Declare @CategoryID int
	
	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 @LeveID=LevelId from TA_Tbl_EmployeeLevel where EmployeeId=@EmployeeId and EffectiveDate=@EffectLevelDate
	select @EffectCategoryDate=Max(EffectiveDate)from TA_Tbl_EmployeeCategory where EmployeeId=@EmployeeId
	select @CategoryID=CategoryId from TA_Tbl_EmployeeCategory where EmployeeId=@EmployeeId and EffectiveDate=@EffectCategoryDate
	select @EffectTypeDate=Max(EffectiveDate) from TA_Tbl_EmployeeType where EmployeeId=@EmployeeId
	declare @ReportingToEmailID as nvarchar(100)
	declare @ApprovalAuthorityEmailID as nvarchar(100)
	
	if exists
	(
		select Mat.ActionItem from ApprovalMatrix Mat
		inner join approvalMatrix_Level ml on Mat.MatrixId=ml.matrixID
		where ml.levelId=@LeveID and Mat.ActionItemValue=3 and Mat.EmployeeCategoryValue=0
	)
	begin
		select top 1 @Approver1=Approver1Value
		--,@Approver2=Approver2Value,@Approver3=Approver3Value,@Approver4=Approver4Value 
		from ApprovalMatrix Mat
		inner join approvalMatrix_Level ml on Mat.MatrixId=ml.matrixID
		where ml.levelId=@LeveID and Mat.ActionItemValue=3 and Mat.EmployeeCategoryValue=0
	end
	else
	begin
		select top 1 @Approver1=Approver1Value
		--,@Approver2=Approver2Value,@Approver3=Approver3Value,@Approver4=Approver4Value 
		from ApprovalMatrix Mat
		inner join approvalMatrix_Level ml on Mat.MatrixId=ml.matrixID
		where ml.levelId=@LeveID and Mat.ActionItemValue=3 and Mat.EmployeeCategoryValue=@CategoryID
	end
	
	if(@Approver1=1)
	begin
		select @ReportingToEmailID=EmailId,@Approver1Name=Employee_FirstName from TA_Tbl_Employee where Employee_Id=(select Reporting_To from TA_Tbl_Employee where Employee_Id=@EmployeeId)
	end
	else if(@Approver1=2)
	begin
		select @ReportingToEmailID=EmailId,@Approver1Name=Employee_FirstName  from TA_Tbl_Employee where Employee_Id=(select Leave_Sanc_Authority from TA_Tbl_Employee where Employee_Id=@EmployeeId)
	end
	else if(@Approver1=3)
	begin
		select @ReportingToEmailID=EmailId,@Approver1Name=Employee_FirstName  from TA_Tbl_Employee where Employee_Id=(select DepartHead_ID from TA_Tbl_Employee where Employee_Id=@EmployeeId)
	end
	else if(@Approver1=4)
	begin
		select @ReportingToEmailID=EmailId,@Approver1Name=Employee_FirstName  from TA_Tbl_Employee where Employee_Id=(select CompanyHeadID from TA_Tbl_Employee where Employee_Id=@EmployeeId)	
	end
	
	SELECT @ReportingToEmailID as 'ReportingToEmailID',@Approver1Name as Approval_Name,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,E.Basic_Salary 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,
			@ApprovalAuthorityEmailID as 'ApprovalAuthorityEmailID',
			E.penalty, E.MobileClockInOUt,E.SMSEnabled,
			E.IsFoodAllowance as IsFoodAllowanceEnabled,
			E.IsShiftAllowance as IsShiftAllowanceEnabled,
			E.DepartHead_ID,(DH.Employee_FirstName + ' ' + DH.Employee_LastName) as DepartmentHeadName,
			DH.EmailId as DepartmentHead_MailID,
			E.CompanyHeadID,(CH.Employee_FirstName + ' ' + CH.Employee_LastName) as CompanyHeadName,
			CH.EmailId as CompanyHead_MailID
	FROM TA_Tbl_Employee E   
	left Outer join TA_Tbl_EmployeeEntrance EE On EE.Employee_Code_Id=E.Employee_Code_Id 
	left join TA_Tbl_Employee DH on E.DepartHead_ID=DH.Employee_Id
	left join TA_Tbl_Employee CH on E.CompanyHeadID=CH.Employee_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_RptMonthClosure]    
(  
	@EmployeeName varchar(max),    
	@LevelID varchar(8000),
	@MonthClosureId varchar(500),
	@EmployeeId varchar(max),
	@employeeCategory int,
	@employeeType int,
	@UserGroupName varchar(250),
	@GradeLevel varchar(20),
	@EmpSwipeCard varchar(20),
	@ParentId int,
	@DesignationID int,
	@Gender varchar(10),
	@EmpAutoID int	
)
AS    
BEGIN 
	SET NOCOUNT ON;
	--Temporary table's creation
	create table #EmpLevel(EmployeeId int,LevelId int,EffectiveDate datetime,Employee_Code_Id varchar(50),SwipeCard varchar(50))
	create table #EmpDesignation(EmployeeId int,DesignationId int,EffectiveDate datetime,Employee_Code_Id varchar(50),SwipeCard varchar(50))
	create table #EmpType(EmployeeId int,TypeId int,EffectiveDate datetime,Employee_Code_Id varchar(50),SwipeCard varchar(50))
	create table #EmpCategory(EmployeeId int,CategoryId int,EffectiveDate datetime,Employee_Code_Id varchar(50),SwipeCard varchar(50))
	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](@EmpAutoID) as EmpAutoId  
    insert into  #EmployeeIds select * from [dbo].[fn_TA_GetMappedEmployees_SancAuthority](@EmpAutoID) as EmpAutoId
	create table #LevelRights(LevelId int)
	insert into  #LevelRights select * from [dbo].[fn_TA_GetParentChild](@UserGroupName,@ParentID) as LevelId
	CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpLevel] 
	(
		[EffectiveDate],[LevelId] ,[EmployeeId] ASC
	) ON [PRIMARY]
	CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpDesignation] 
	(
		[EffectiveDate],[DesignationId],[EmployeeId] ASC
	)ON [PRIMARY]
	CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpType] 
	(
		[EffectiveDate],[TypeId],[EmployeeId] ASC
	) ON [PRIMARY]
	CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpCategory] 
	(
		[EffectiveDate],[CategoryId],[EmployeeId] ASC
	)ON [PRIMARY]
	Declare @toDate datetime
	set @toDate=GETDATE()
	insert into #EmpDesignation exec Proc_TA_EmpDesignation @toDate,@EmployeeId,@DesignationID	
	insert into #EmpType exec [Proc_TA_EmpType] @toDate,@EmployeeId,@employeeType	
	insert into #EmpCategory exec [Proc_TA_EmpCategory] @toDate,@EmployeeId,@employeeCategory	
	insert into #EmpLevel exec  [Proc_TA_EmpLevel] @toDate,@EmployeeId	
	Declare @sqlTempProcessedData1 varchar(8000)
	--To split up the comma separated values
	if(@EmployeeId is not null and @EmployeeId <> '')
		begin
			create table #EmployeeID(EmployeeID varchar(25))
			insert into #EmployeeID
			select * from SplitupEmployeeCode(@EmployeeId)
		end	
	if(@EmployeeName is not null and @EmployeeName <> '')
		begin
			create table #EmployeeName(EmployeeID varchar(25))
			insert into #EmployeeName
			select [Value] from dbo.[SplitUpEmployeeName](@EmployeeName,',')
		end
	
	declare @TopLevelId as int
	DECLARE @childID INT 
	;WITH ParentLevel AS
	(
		SELECT *, 1 AS Lvl FROM Ta_Tbl_levelDetails WHERE id = @ParentId
		UNION ALL
		SELECT rh.*, Lvl+1 AS Lvl FROM Ta_Tbl_levelDetails rh
		INNER JOIN ParentLevel rc ON rh.id = rc.ParentId
	)
	SELECT TOP 1 @TopLevelId=id FROM ParentLevel r where parentId=0 ORDER BY lvl DESC
	
	Declare @isShownSpecialOT int
	Declare @NormalSPOT2 int
	Declare @OffSPOT1 int
	Declare @OffSPOT2 int
	Declare @HolidaySPOT1 int
	Declare @HolidaySPOT2 int
	Declare @ShiftAllow int
	Declare @FoodAllow int
	Declare @MealAllow int
	Declare @SHAllow int
	Declare @Att_Allow int
	
	set @isShownSpecialOT =1
	set @NormalSPOT2 =1
	set @OffSPOT1 =1
	set @OffSPOT2 =1
	set @HolidaySPOT1 =1
	set @HolidaySPOT2 =1
	set @ShiftAllow =1
	set @FoodAllow =1
	set @MealAllow =1
	set @SHAllow =1
	set @Att_Allow =1
	
	select	@isShownSpecialOT=IsSpecialOTRequired,
			@NormalSPOT2=NormalSPOT2,
			@OffSPOT1=OffSPOT1,
			@OffSPOT2=OffSPOT2,
			@HolidaySPOT1=HolidaySPOT1,
			@HolidaySPOT2=HolidaySPOT2,
			@ShiftAllow=ShiftAllow,
			@FoodAllow=FoodAllow,
			@MealAllow=MealAllow,
			@SHAllow=SHAllow,
			@Att_Allow=Att_Allow  from TA_Tbl_ShiftSettings where CompanyId=@TopLevelId
	
	declare @sqlQuery as varchar(8000)    
	set @sqlQuery=''
	set @sqlQuery = 'select e.Employee_Code_Id as Employee_Id,isnull(e.Employee_FirstName,'''') +  space(1) + isnull(e.Employee_LastName,'''') as EmployeeName,
	Convert(varchar,MonthClosure_StartDate,106) + '' - '' + Convert(varchar,MonthClosure_EndDate,106) as Duration,
	ppd.HrsWork,ppd.HrsOT,ppd.NormOT,ppd.OFFOT,ppd.HolOT,ppd.EarlyOut,ppd.LateIn,ppd.WorkHrs as TotWorkHrs,
	ppd.HrsLess,ppd.HrsExtra,isnull(ppd.LeaveDays,0) as LeaveDays,isnull(ppd.PresentDays,0) as PresentDays,
	isnull(ppd.AbsentDays,0) as AbsentDays,isnull(ppd.Holidays,0) as Holidays,isnull(ppd.LOPDays,0) as LOPDays,
	replace(T.TitleName,''&amp;'',''&'') as TitleName,e.Gender,ppd.TotalPermissionHours,ppd.FullPaidLeave as FullPaidLeave,ppd.HalfPaidLeave as HalfPaidLeave,ppd.UnPaidLeave as UnPaidLeave'
	
	if(@isShownSpecialOT=1)
	begin
		set @sqlQuery = @sqlQuery + ' ,[Actual Week Day Special OT 1]=isnull(ppd.SPEC_NormOT1,''00:00'')'
	end
	if(@NormalSPOT2=1)
	begin
		set @sqlQuery = @sqlQuery + ' ,[Actual Week Day Special OT 2]=isnull(ppd.SPEC_NormOT2,''00:00'')'
	end
	if(@OffSPOT1=1)
	begin
		set @sqlQuery = @sqlQuery + ' ,[Actual Week Off Special OT 1]=isnull(ppd.SPEC_OffOT1,''00:00'')'
	end
	if(@OffSPOT2=1)
	begin
		set @sqlQuery = @sqlQuery + ' ,[Actual Week OFF Special OT 2]=isnull(ppd.SPEC_OffOT2,''00:00'')'
	end
	if(@HolidaySPOT1=1)
	begin
		set @sqlQuery = @sqlQuery + ' ,[Actual Holiday Special OT 1]=isnull(ppd.SPEC_HOLOT1,''00:00'')'
	end
	if(@HolidaySPOT2=1)
	begin
		set @sqlQuery = @sqlQuery + ' ,[Actual Holiday Special OT 2]=isnull(ppd.SPEC_HOLOT2,''00:00'')'
	end
	if(@ShiftAllow=1)
	begin
		set @sqlQuery = @sqlQuery + ' ,[Shift Allowance]=isnull(Shift_Allow,0)'
	end
	if(@FoodAllow=1)
	begin
		set @sqlQuery = @sqlQuery + ' ,[Food Allowance]=isnull(Food_Allow,0)'
	end
	if(@MealAllow=1)
	begin
		set @sqlQuery = @sqlQuery + ' ,[Meal Allowance]=isnull(Meal_Allow,0)'
	end
	if(@SHAllow=1)
	begin
		set @sqlQuery = @sqlQuery + ' ,[SH Allowance]=isnull(SH_Allow,0)'
	end
	if(@Att_Allow=1)
	begin
		set @sqlQuery = @sqlQuery + ' ,[Attendance Allowance]=isnull(SH_Allow,0)'
	end
	set @sqlQuery = @sqlQuery + ' from TA_Tbl_MonthClosure mc inner join TA_Tbl_PayrollProcessDetails 
	ppd on ppd.MonthClosure_Id=mc.MonthClosure_Id'  
	set @sqlQuery = @sqlQuery + ' inner join Ta_Tbl_Employee e on e.SwipeCardNo=ppd.Employee_Id'  
	set @sqlQuery = @sqlQuery + ' Inner join #EmpLevel Lev on Lev.employeeid = e.Employee_Id '
	Set @sqlQuery = @sqlQuery + ' INNER JOIN TA_Tbl_LevelDetails L ON  Lev.LevelID = L.Id '	
	set @sqlQuery = @sqlQuery + ' Inner join #EmpDesignation EDesig on EDesig.employeeid = e.Employee_Id '
	Set @sqlQuery = @sqlQuery + ' INNER JOIN TA_Tbl_Title T ON  T.TitleId = EDesig.DesignationId '
	SET @sqlQuery = @sqlQuery + ' INNER JOIN TA_Tbl_Grade ON TA_Tbl_Grade.Grade_ID = T.GradeId '
	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 = Lev.levelId '
	If Not (@EmployeeName = '')  
		Begin  
			SET @sqlQuery = @sqlQuery + ' Inner join #EmployeeName on #EmployeeName.EmployeeID=e.Employee_code_Id '
		End
				If Not (@EmployeeId = '')  
		Begin  
			SET @sqlQuery = @sqlQuery + ' Inner join #EmployeeID on #EmployeeID.EmployeeID=e.Employee_code_Id '
		End
	set @sqlQuery = @sqlQuery + ' where e.Isdeleted=0 and e.report_exclude=0 and mc.IsDeleted=0'    
	if not(@MonthClosureId='' or @MonthClosureId='0')    
		begin    
			set @sqlQuery = @sqlQuery + ' And (mc.MonthClosure_Id In (' + @MonthClosureId + '))'    
		End 
	if(@Gender<>'Both')
		begin
			set @sqlQuery = @sqlQuery + ' and e.Gender = ''' + cast(@Gender as nvarchar(8)) + ''' '
		end	
	if(@isReportingto > 0)
            BEGIN
               set @sqlQuery = @sqlQuery + ' and (e.Employee_ID IN (select * from #EmployeeIds)) '  
            END
	if(@UserGroupName<>'SuperAdmin')
		begin
			SET @sqlQuery = @sqlQuery + ' AND TA_Tbl_Grade.Grade_Level>=' + @GradeLevel + ''
		end
	set @sqlQuery = @sqlQuery + ' order by e.Employee_FirstName'
	exec(@sqlQuery)
	
	--Remove the temporary tables here 
	drop table #EmpLevel
	drop table #EmpDesignation
	drop table #EmpType
	drop table #EmpCategory
	drop table #LevelRights
END
-----------------------------------------------------------------------------------------------------------------------------------------

ALTER PROCEDURE [dbo].[Proc_TA_EmployeeDetailsForPayrollProcess]
@MonthClosureEndDate nVarchar(15),
@Levels varchar(8000),
@UserGroupName varchar(250),
@ParentId int,
@MonthCloseID int,
@AuditEmpId int,
@ProcessCount int,
@ProcessType varchar(50),
@strEmployees xml,
@Employee_code_Id varchar(25),
@Employeename varchar(50)
AS
BEGIN TRY
      BEGIN TRANSACTION    -- Start the transaction
            SET NOCOUNT ON;
            Declare @RowCountEmployeeID int
            Declare @RowCountEmployeeName int
    create table #EmployeeID(EmployeeID varchar(25))
            create table #EmployeeName(EmployeeName varchar(25))
            if(@Employee_code_Id is not null and @Employee_code_Id <> '')
            begin
                  if(SUBSTRING(@Employee_code_Id,LEN(@Employee_code_Id),len(@Employee_code_Id)) = ',')
                  begin
                        set @Employee_code_Id=SUBSTRING(@Employee_code_Id,0,len(@Employee_code_Id))             
                  end
                        insert into #EmployeeID
                        select value from dbo.split(@Employee_code_Id,',')
                        select @RowCountEmployeeID=COUNT(EmployeeID) from #EmployeeID                        
          end
            if(@EmployeeName is not null and @EmployeeName <> '')
            begin
                  if(SUBSTRING(@EmployeeName,LEN(@EmployeeName),len(@EmployeeName)) = ',')
                  begin
                        set @EmployeeName=SUBSTRING(@EmployeeName,0,len(@EmployeeName))                        
                  end 
                        insert into #EmployeeName
                        select value from dbo.split(@EmployeeName,',')                    
                        select @RowCountEmployeeName=COUNT(EmployeeName) from #EmployeeName                       
                  end
      --Temporary table's creation
      create table #ReprocessEmployees(EmployeeCodeId varchar(15))
      create table #EmpLevel(EmployeeId int,LevelId int,EffectiveDate datetime)
      create table #LevelRights(LevelId int)
      insert into  #LevelRights select * from [dbo].[fn_TA_GetParentChild](@UserGroupName,@ParentID) as LevelId 
      ----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]
      CREATE TABLE #TempProcessedData
      (
            [Process_Rec_ID] bigint,[Employee_Code_Id] nvarchar(20),
            [Date] datetime,  [Shift_Id] int,
            [InTime] nvarchar(25),[OutTime] nvarchar(25),
            [HrsWork] nvarchar(10),[HrsOT] nvarchar(10),
            [NormOT] nvarchar(10),[OffOT] nvarchar(10),
            [HolOT] nvarchar(10),[EarlyOut] nvarchar(10),
            [LateIn] nvarchar(10),[Attendance] nvarchar(20),
            [WorkHrs] nvarchar(10),[HrsLess] nvarchar(10),
            [HrsExtra] nvarchar(10),[LateInMins] float,
            [EarlyOutMins] float,[ExtraMins] float,
            [OtMins] float,[OvertimeStatus] nvarchar(50),
            [TotalDays] numeric(18, 1),[LeaveCode] nvarchar(20),
            [LateEntryReason] nvarchar(260),[EarlyOutReason] nvarchar(260),
            [ProcessFlag] bit,[Status] nvarchar(50),
            [FNAttendanceCode] nvarchar(15),[ANAttendanceCode] nvarchar(15),[HoursWorkInMins] float,
            [LessHoursInMins] float,[WorkedHrsInMins] float,
            SPEC_NormOT1 int default(0),
            SPEC_NormOT2 int default(0),
            SPEC_OffOT1	int default(0),
            SPEC_OffOT2	int default(0),
            SPEC_HOLOT1	int default(0),
            SPEC_HOLOT2	int default(0),
            Shift_Allow decimal(18,1),
			Food_Allow decimal(18,1),
			Meal_Allow decimal(18,1),
			SH_Allow decimal(18,1),
			Att_Allow decimal(18,1),
			A_Shift int default(0),
			B_Shift int default(0),
			C_Shift int default(0),
			TotalShiftDays as (A_Shift+B_Shift+C_Shift),
			BAndCShift as (B_Shift+C_Shift)
      )
      Declare @Index int
      Set @Index = 0;
      EXEC sp_xml_preparedocument @Index OUTPUT, @strEmployees
      insert into #ReprocessEmployees (EmployeeCodeId)
      select EmployeeId from OpenXML(@Index, '/NewDataSet/ReprocessEmployees')
      With (EmployeeId varchar(15) 'EmployeeId')
      Exec sp_xml_removedocument @Index   
      DECLARE @SQLQUERY VARCHAR(8000)
      SET @SQLQUERY=''
      --select * from #ReprocessEmployees
      --Employee Level insert in Temporary table
      if exists(select EmployeeCodeId from #ReprocessEmployees)
      BEGIN
                  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 
                  inner join #ReprocessEmployees on #ReprocessEmployees.EmployeeCodeId=emp.Employee_Code_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(@MonthClosureEndDate as nvarchar(50)), 102) and EL.LevelId is not null
      ENd
      else
      BEGIN
          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(@MonthClosureEndDate as nvarchar(50)), 102) and EL.LevelId is not null
      END
      if (@ProcessType='BindGrid')
      BEGIN
            SET @SQLQUERY='' + 'SELECT E.Employee_Id,E.Employee_Code_Id,E.SwipeCardNo,E.Employee_FirstName + Space(1) + isnull(E.Employee_LastName,'''') as "Employee Name" FROM TA_Tbl_Employee E '
            SET @SQLQUERY=@SQLQUERY +' INNER JOIN TA_Tbl_EmployeeLevel ON E.Employee_Id = TA_Tbl_EmployeeLevel.EmployeeId '
            SET @SQLQUERY=@SQLQUERY +' Inner join #EmpLevel on #EmpLevel.EffectiveDate=TA_Tbl_EmployeeLevel.EffectiveDate and #EmpLevel.employeeid=TA_Tbl_EmployeeLevel.EmployeeId '
            SET @SQLQUERY=@SQLQUERY +' Inner join #LevelRights on #LevelRights.LevelId=#EmpLevel.Levelid '
            IF(@RowCountEmployeeID >0)
            BEGIN
                        SET @SQLQUERY=@SQLQUERY +' Inner join #EmployeeID ON #EmployeeID.EmployeeID  = E.EMPLOYEE_CODE_ID '
            END
            if(@RowCountEmployeeName >0)
            begin
                        SET @SQLQUERY=@SQLQUERY +' Inner join #EmployeeName on #EmployeeName.EmployeeName = E.Employee_FirstName ' 
            end
            SET @SQLQUERY=@SQLQUERY +' WHERE E.Employee_Firstname<>''Administrator'' AND E.Isdeleted = 0'        
            EXEC(@SQLQUERY)
      END
      else
      BEGIN
            declare @fromDat datetime
            declare @todat datetime
            select @fromDat=MonthClosure_StartDate,@todat=MonthClosure_EndDate from Ta_Tbl_MonthClosure where MonthClosure_Id=@MonthCloseID
            insert into #TempProcessedData
            select [Process_Rec_ID],pd.[Employee_code_id],pd.[Date],PD.[Shift_Id],[InTime],[OutTime],[HrsWork],[HrsOT],[NormOT],[OffOT],[HolOT],[EarlyOut],
            [LateIn],[Attendance],[WorkHrs],[HrsLess],[HrsExtra],[LateInMins],[EarlyOutMins],[ExtraMins],[OtMins],[OvertimeStatus],[TotalDays],
            [LeaveCode],[LateEntryReason],[EarlyOutReason],[ProcessFlag],[Status],[FNAttendanceCode],[ANAttendanceCode],[HoursWorkInMins],[LessHoursInMins],substring([WorkHrs],0,charindex(':',[WorkHrs]))*60+substring([WorkHrs],charindex(':',[WorkHrs])+1,len([WorkHrs])) as [WorkedHrsInMins],
            Case when spot.isFinal='Approved' then case when (Modified_Spec_NormOTHrs1 is not null and Modified_Spec_NormOTHrs1!='') then spot.Modified_Spec_NormOTMins1 else pd.SPEC_NormOTMins1 end end,
            Case when spot.isFinal='Approved' then case when (Modified_Spec_NormOTHrs2 is not null and Modified_Spec_NormOTHrs2!='') then spot.Modified_Spec_NormOTMins2 else pd.SPEC_NormOTMins2 end end,
            Case when spot.isFinal='Approved' then case when (Modified_Spec_OffOTHrs1 is not null and Modified_Spec_OffOTHrs1!='') then spot.Modified_Spec_OffOTMins1 else pd.SPEC_OffOTMins1 end end,
            Case when spot.isFinal='Approved' then case when (Modified_Spec_OffOTHrs2 is not null and Modified_Spec_OffOTHrs2!='') then spot.Modified_Spec_OffOTMins2 else pd.SPEC_OffOTMins2 end end,
            Case when spot.isFinal='Approved' then case when (Modified_Spec_HolOTHrs1 is not null and Modified_Spec_HolOTHrs1!='') then spot.Modified_Spec_HolOTMins1 else pd.SPEC_HOLOTMins1 end end,
            Case when spot.isFinal='Approved' then case when (Modified_Spec_HolOTHrs2 is not null and Modified_Spec_HolOTHrs2!='') then spot.Modified_Spec_HolOTMins2 else pd.SPEC_HOLOTMins2 end end,
            
            --Case when PD.OvertimeStatus='Approved' then case when (Modified_Shift_Allow is not null and Modified_Shift_Allow!=0) then spot.Modified_Shift_Allow else pd.Shift_Allow end end,
            0,
            Case when spot.isFinal='Approved' then case when (Modified_Food_Allow is not null and Modified_Food_Allow!=0) then spot.Modified_Food_Allow else pd.Food_Allow end end,
            Case when spot.isFinal='Approved' then case when (Modified_Meal_Allow is not null and Modified_Meal_Allow!=0) then spot.Modified_Meal_Allow else pd.Meal_Allow end end,
            Case when spot.isFinal='Approved' then case when (Modified_SH_Allow is not null and Modified_SH_Allow!=0) then spot.Modified_SH_Allow else pd.SH_Allow end end,
            0--Newly added here..,
            ,
            case when S.Shift_StartTime='08:00 AM' then 1 else 0 end,
            case when S.Shift_StartTime='04:00 PM' then 1 else 0 end,
            case when S.Shift_StartTime='11:50 PM' then 1 else 0 end
            from TA_Tbl_ProcessedData PD
            Left outer join Ta_tbl_specialOvertimeDetails spot on (PD.Employee_Code_Id=spot.Employee_code_id and PD.Date=spot.Date)
            Left outer join TA_Tbl_Shift S on PD.Shift_Id=S.Shift_Id
            where PD.Employee_Code_Id in (select SwipeCardNo from ta_tbl_employee e
            inner join #EmpLevel el on el.employeeid=e.employee_id
            inner join #LevelRights lr on lr.levelId=el.levelid WHERE e.Employee_Firstname<>'Administrator' AND e.Isdeleted = 0) AND
            (PD.Date >= CONVERT(DATETIME,cast( @fromDat as nvarchar(50)), 102)) 
            AND (PD.Date <= CONVERT(DATETIME,cast(@todat as nvarchar(50)), 102))
            
            --select * from #TempProcessedData
            
            CREATE TABLE #TempPayrollProcessDetails(
            [MonthClosure_Id] [int] NULL,
            [Employee_Id] [nvarchar](20) NULL,
            [HrsWork] [nvarchar](25) NULL default('00:00'),
            [HrsOT] [nvarchar](25) NULL default('00:00'),
            [NormOT] [nvarchar](25) NULL default('00:00'),
            [OffOT] [nvarchar](25) NULL default('00:00'),
            [HolOT] [nvarchar](25) NULL default('00:00'),
            [OtMins] int default(0),
            [EarlyOut] [nvarchar](25)  default('00:00'),
            [LateIn] [nvarchar](25)  default('00:00'),
            [WorkHrs] [nvarchar](25)  default('00:00'),
            [HrsLess] [nvarchar](25)  default('00:00'),
            [HrsExtra] [nvarchar](25)  default('00:00'),
            [LeaveDays] [numeric](18, 1) default(0.0),
            [AbsentDays] [numeric](18, 1) default(0.0),
            [PresentDays] [numeric](18, 1) default(0.0),
            [Holidays] [numeric](18, 1) default(0.0),
            [LOPDays] [numeric](18, 1) default(0.0),
            [Emp_Id] [int] NULL,
            [TimeStamp] [datetime] NULL
            ,TotalPermissionHours nvarchar(25)null default('00:00')
            ,WeekOffDays [numeric](18, 1) default(0.0),
            SPEC_NormOT1 nvarchar(10)default('00:00'),
            SPEC_NormOT2 nvarchar(10)default('00:00'),
            SPEC_OffOT1	nvarchar(10)default('00:00'),
            SPEC_OffOT2	nvarchar(10)default('00:00'),
            SPEC_HOLOT1	nvarchar(10)default('00:00'),
            SPEC_HOLOT2	nvarchar(10)default('00:00'),
            Shift_Allow decimal(18,1),
			Food_Allow decimal(18,1),
			Meal_Allow decimal(18,1),
			SH_Allow decimal(18,1),
			Att_Allow decimal(18,1),
			[FullPaidLeave] [numeric](18, 1) default(0.0),
			[HalfPaidLeave] [numeric](18, 1) default(0.0),
			[UnPaidLeave] [numeric](18, 1) default(0.0)
            )
            insert into #TempPayrollProcessDetails (Employee_Id) select distinct [Employee_Code_Id] as Employee_Id from #TempProcessedData
            update #TempPayrollProcessDetails set [MonthClosure_Id]=@MonthCloseID
            select E.employee_code_id,sum(convert(int,substring(no_of_hours,1,(charindex(':',no_of_hours)-1))))as PermissionHours,
            sum(convert(int,substring(no_of_hours,charindex(':',no_of_hours)+1,len(no_of_hours)))) as permissionminutes 
            into #Permission_Details  from ta_tbl_permission P inner join 
            ta_tbl_employee E on E.employee_id=P.employee_id WHERE       (p.Permission_Date >= CONVERT(DATETIME,cast( @fromDat as nvarchar(50)), 102)) 
            AND (p.Permission_Date <= CONVERT(DATETIME,cast(@todat as nvarchar(50)), 102))
            and P.IsDeleted=0 and P.Permission_Status = 'Approved'
            group by E.employee_code_id
            update #TempPayrollProcessDetails set TotalPermissionHours=
            case when   len(convert(varchar(20),PermissionHours + permissionminutes/60))=1 and len(convert(varchar(20),permissionminutes%60))=1 then             
                        '0'+convert(varchar(20),PermissionHours + permissionminutes/60)      +':0'+convert(varchar(20),permissionminutes%60)
            when  len(convert(varchar(20),PermissionHours + permissionminutes/60))=1 and len(convert(varchar(20),permissionminutes%60))!=1 then
                        '0'+convert(varchar(20),PermissionHours + permissionminutes/60)      +':'+convert(varchar(20),permissionminutes%60)
            when  len(convert(varchar(20),PermissionHours + permissionminutes/60))!=1 and len(convert(varchar(20),permissionminutes%60))=1 then
                        '0'+convert(varchar(20),PermissionHours + permissionminutes/60)      +':'+convert(varchar(20),permissionminutes%60)
            else
                        '0'+convert(varchar(20),PermissionHours + permissionminutes/60)      +':'+convert(varchar(20),permissionminutes%60)
            end
            from #TempPayrollProcessDetails PD
            inner join #Permission_Details PerD on PD.Employee_Id=PerD.employee_code_id
            update #TempPayrollProcessDetails 
            set NormOT=convert(varchar, cast(T1.OTMins as int) / 60) + ':' + right(convert(char(3), 100 + cast(cast(T1.OTMins as int) as numeric) % 60), 2)
            from (select sum(OTMins) as OTMins,Employee_Code_Id from #TempProcessedData  where OvertimeStatus='Approved' and NormOT is not null and NormOT<>'' and NormOT<>'00:00'
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id     
            update #TempPayrollProcessDetails set NormOT=isnull(NormOT,'00:00')
            /**********************/
            --update TPP set SPEC_NormOT1=TP.SPEC_NormOT1,
            --SPEC_NormOT2=TP.SPEC_NormOT2,
            --SPEC_HOLOT1=TP.SPEC_HOLOT1,
            --SPEC_HOLOT2=TP.SPEC_HOLOT2,
            --SPEC_OffOT1=TP.SPEC_OffOT1,
            --SPEC_OffOT2=TP.SPEC_OffOT2
            --from #TempPayrollProcessDetails TPP inner join #TempProcessedData TP
            --on TPP.Employee_Id=TP.Employee_Code_Id  
			/***********Special Overtime**************/
			update #TempPayrollProcessDetails 
            set SPEC_NormOT1=dbo.ConvertMinsToHours(t1.OTMins)
            from (select sum(SPEC_NormOT1) as OTMins,Employee_Code_Id from #TempProcessedData  where SPEC_NormOT1 is not null and SPEC_NormOT1<>''
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails 
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id

			update #TempPayrollProcessDetails 
            set SPEC_NormOT2=dbo.ConvertMinsToHours(t1.OTMins)
            from (select sum(SPEC_NormOT2) as OTMins,Employee_Code_Id from #TempProcessedData  where SPEC_NormOT2 is not null and SPEC_NormOT2<>''
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails 
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id

			update #TempPayrollProcessDetails 
            set SPEC_OffOT1=dbo.ConvertMinsToHours(t1.OTMins)
            from (select sum(SPEC_OffOT1) as OTMins,Employee_Code_Id from #TempProcessedData  where SPEC_OffOT1 is not null and SPEC_OffOT1<>''
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails 
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id

			update #TempPayrollProcessDetails 
            set SPEC_OffOT2=dbo.ConvertMinsToHours(t1.OTMins)
            from (select sum(SPEC_OffOT2) as OTMins,Employee_Code_Id from #TempProcessedData  where SPEC_OffOT2 is not null and SPEC_OffOT2<>''
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails 
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id

			update #TempPayrollProcessDetails 
            set SPEC_HOLOT1=dbo.ConvertMinsToHours(t1.OTMins)
            from (select sum(SPEC_HOLOT1) as OTMins,Employee_Code_Id from #TempProcessedData  where SPEC_HOLOT1 is not null and SPEC_HOLOT1<>''
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails 
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id

			update #TempPayrollProcessDetails 
            set SPEC_HOLOT2=dbo.ConvertMinsToHours(t1.OTMins)
            from (select sum(SPEC_HOLOT2) as OTMins,Employee_Code_Id from #TempProcessedData  where SPEC_HOLOT2 is not null and SPEC_HOLOT2<>''
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails 
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id
			/***********Special Overtime**************/
			/***********Allowances**************/
			--Commented for new concept...
			--update #TempPayrollProcessDetails 
   --         set Shift_Allow=t1.OTMins
   --         from (select sum(Shift_Allow) as OTMins,Employee_Code_Id from #TempProcessedData  where Shift_Allow is not null and Shift_Allow<>0
   --         Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails 
   --         on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id
   
			update #TempPayrollProcessDetails 
			set Shift_Allow=case when T1.BAndCShift>10 then tot else BAndCShift end
		    from 
		    (
				select BAndCShift=sum(BAndCShift),tot=sum(TotalShiftDays),Employee_Code_Id from #TempProcessedData  where Shift_Allow is not null and Shift_Allow<>0
				Group by Employee_Code_Id
			) as T1 inner join #TempPayrollProcessDetails 
		    on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id
            --print 'manikandan'
            update #TempPayrollProcessDetails 
            set Food_Allow=t1.OTMins
            from (select sum(Food_Allow) as OTMins,Employee_Code_Id from #TempProcessedData  where Food_Allow is not null and Food_Allow<>0
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails 
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id
            
            update #TempPayrollProcessDetails 
            set Meal_Allow=t1.OTMins
            from (select sum(Meal_Allow) as OTMins,Employee_Code_Id from #TempProcessedData  where Meal_Allow is not null and Meal_Allow<>0
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails 
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id
            
            update #TempPayrollProcessDetails 
            set SH_Allow=t1.OTMins
            from (select sum(SH_Allow) as OTMins,Employee_Code_Id from #TempProcessedData  where SH_Allow is not null and SH_Allow<>0
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails 
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id
            
			update #TempPayrollProcessDetails 
            set Att_Allow= case when T1.OTMins Is not null and t1.OTMins!=0 then 0 else 1 end
            from (select sum(isnull(LateInMins,0))+SUM(isnull(EarlyOutMins,0)) as OTMins,Employee_Code_Id from #TempProcessedData
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails 
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id 
            
            update #TempPayrollProcessDetails 
            set Att_Allow= case when T1.OTMins Is not null and t1.OTMins!=0 then 0 else 1 end
            from 
            (
				select sum(case when (FNAttendanceCode='Absent' Or FNAttendanceCode='Absent') then 1 else 0 end) as OTMins,Employee_Code_Id from #TempProcessedData
				Group by Employee_Code_Id
            ) as T1 inner join #TempPayrollProcessDetails 
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id where Att_Allow=0
            
            update #TempPayrollProcessDetails 
            set Att_Allow= case when T1.OTMins Is not null and t1.OTMins!=0 then 0 else 1 end
            from 
            (
				select sum(case when (FNAttendanceCode='AL' Or FNAttendanceCode='AL') then 1 else 0 end) as OTMins,Employee_Code_Id from #TempProcessedData
				Group by Employee_Code_Id
            ) as T1 inner join #TempPayrollProcessDetails 
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id where Att_Allow=0
			
			/***********Allowances**************/
			


            update #TempPayrollProcessDetails 
            set OffOT=case when len(convert(varchar(20),T1.OTMins/60))=1 then '0'+convert(varchar(20),T1.OTMins/60)
       else convert(varchar(20),T1.OTMins/60)end+':'+
       case when convert(varchar(20),T1.OTMins%60)=0 then
       '00' else case when len(convert(varchar(20),T1.OTMins%60))=1 then '0'+convert(varchar(20),T1.OTMins%60)
       else convert(varchar(20),T1.OTMins%60)end end
            from (select CONVERT(int,sum(OTMins)) as OTMins,Employee_Code_Id from #TempProcessedData  where OvertimeStatus='Approved' and OffOT is not null and OffOT<>'' and OffOT<>'00:00'
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id  
            update #TempPayrollProcessDetails 
            set OffOT=case when len(convert(varchar(20),T1.OTMins/60))=1 then '0'+convert(varchar(20),T1.OTMins/60)
       else convert(varchar(20),T1.OTMins/60)end+':'+
       case when convert(varchar(20),T1.OTMins%60)=0 then
       '00' else case when len(convert(varchar(20),T1.OTMins%60))=1 then '0'+convert(varchar(20),T1.OTMins%60)
       else convert(varchar(20),T1.OTMins%60)end end
            from (select CONVERT(int,sum(OTMins)) as OTMins,Employee_Code_Id from #TempProcessedData  where OvertimeStatus='Approved' and OffOT is not null and OffOT<>'' and OffOT<>'00:00'
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id  
            update #TempPayrollProcessDetails set OffOT=isnull(OffOT,'00:00')
            update #TempPayrollProcessDetails 
            set HolOT=case when len(convert(varchar(20),T1.OTMins/60))=1 then '0'+convert(varchar(20),T1.OTMins/60)
       else convert(varchar(20),T1.OTMins/60)end+':'+
       case when convert(varchar(20),T1.OTMins%60)=0 then
       '00' else case when len(convert(varchar(20),T1.OTMins%60))=1 then '0'+convert(varchar(20),T1.OTMins%60)
       else convert(varchar(20),T1.OTMins%60)end end
            from (select CONVERT(int,sum(OTMins)) as OTMins,Employee_Code_Id from #TempProcessedData  where OvertimeStatus='Approved' and HolOT is not null and HolOT<>''  and HolOT<>'00:00'
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id     
            update #TempPayrollProcessDetails set HolOT=isnull(HolOT,'00:00') 
            update #TempPayrollProcessDetails 
            set HrsWork=case when len(convert(varchar(20),T1.HrsWork/60))=1 then '0'+convert(varchar(20),T1.HrsWork/60)
       else convert(varchar(20),T1.HrsWork/60)end+':'+
       case when convert(varchar(20),T1.HrsWork%60)=0 then
       '00' else case when len(convert(varchar(20),T1.HrsWork%60))=1 then '0'+convert(varchar(20),T1.HrsWork%60)
       else convert(varchar(20),T1.HrsWork%60)end end
            from (
            select employee_code_id , CONVERT(int,SUM(HoursWorkInMins)) as HrsWork
            from #TempProcessedData group by employee_code_id ) as T1 
            inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id           
            --select * from #TempProcessedData
            update #TempPayrollProcessDetails 
            set WorkHrs=case when (charindex(':',T1.WorkHrs)=2 and charindex(':',reverse(T1.WorkHrs))=2) then '0'+T1.WorkHrs + '0'  
            when charindex(':',reverse(T1.WorkHrs))=2 then T1.WorkHrs +'0'
            when charindex(':',T1.WorkHrs)=2 then '0'+T1.WorkHrs
            when T1.WorkHrs is null then '00:00'
            else T1.WorkHrs end
            from (select cast(cast((sum(DATEPART(hour,WorkHrs)) * 60 + (sum(DATEPART(minute,WorkHrs)))) / 60  as int) as varchar(50)) + ':' + cast(sum(DATEPART(minute,WorkHrs)) % 60 as varchar(2)) as WorkHrs,Employee_Code_Id from #TempProcessedData
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id
            update #TempPayrollProcessDetails 
            set EarlyOut=case when len(convert(varchar(20),T1.EarlyOut/60))=1 then '0'+convert(varchar(20),T1.EarlyOut/60)
       else convert(varchar(20),T1.EarlyOut/60)end+':'+
       case when convert(varchar(20),T1.EarlyOut%60)=0 then
       '00' else case when len(convert(varchar(20),T1.EarlyOut%60))=1 then '0'+convert(varchar(20),T1.EarlyOut%60)
       else convert(varchar(20),T1.EarlyOut%60)end end
            from (select CONVERT(int,sum(EarlyOutMins)) as EarlyOut,Employee_Code_Id from #TempProcessedData
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id
            update #TempPayrollProcessDetails 
            set LateIn=case when len(convert(varchar(20),T1.LateIn/60))=1 then '0'+convert(varchar(20),T1.LateIn/60)
       else convert(varchar(20),T1.LateIn/60)end+':'+
       case when convert(varchar(20),T1.LateIn%60)=0 then
       '00' else case when len(convert(varchar(20),T1.LateIn%60))=1 then '0'+convert(varchar(20),T1.LateIn%60)
       else convert(varchar(20),T1.LateIn%60)end end
            from (select CONVERT(int,SUM(LateInMins)) as LateIn,Employee_Code_Id from #TempProcessedData
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id
            --Added where condition by Manjula.P on 07-Oct-2013 to avoiding Date out of range error Mantis : 0019299  
            update #TempPayrollProcessDetails 
            set HrsLess=case when len(convert(varchar(20),T1.HrsLess/60))=1 then '0'+convert(varchar(20),T1.HrsLess/60)
       else convert(varchar(20),T1.HrsLess/60)end+':'+
       case when convert(varchar(20),T1.HrsLess%60)=0 then
       '00' else case when len(convert(varchar(20),T1.HrsLess%60))=1 then '0'+convert(varchar(20),T1.HrsLess%60)
       else convert(varchar(20),T1.HrsLess%60)end end
            from (select CONVERT(int,SUM(LessHoursInMins)) as HrsLess,Employee_Code_Id from #TempProcessedData
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id
            update #TempPayrollProcessDetails 
            set HrsExtra=convert(char(5), dateadd(MINUTE, T1.HrsExtra, ''), 114)                
            from (select sum(ExtraMins) as HrsExtra,Employee_Code_Id from #TempProcessedData
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id

            --First Half Leave Days update
            update #TempPayrollProcessDetails 
            set Leavedays=isnull(T1.Leavedays,0.0) *0.5
            from (select count(*) as Leavedays,Employee_Code_Id from #TempProcessedData
            where FNAttendanceCode in (Select LeaveType from Ta_Tbl_LeaveType where IsDeleted=0)
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id

            --Secod Half Leave Days update
            update #TempPayrollProcessDetails 
            set Leavedays=#TempPayrollProcessDetails.Leavedays + isnull(T1.Leavedays,0.0) *0.5
            from (select count(*) as Leavedays,Employee_Code_Id from #TempProcessedData
            where ANAttendanceCode in (Select LeaveType from Ta_Tbl_LeaveType where IsDeleted=0)
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id

			--Added the Full pay, Half Pay and Unpay leave karthick on 02-Sep-2022
			
			-- 0 Un Pay
			 --First Half Leave Days update
            update #TempPayrollProcessDetails 
            set UnPaidLeave=isnull(T1.Leavedays,0.0) *0.5
            from (select count(*) as Leavedays,Employee_Code_Id from #TempProcessedData
            where FNAttendanceCode in (Select LeaveType from Ta_Tbl_LeaveType where IsDeleted=0 and LeaveCategory=0)
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id

			--Secod Half Leave Days update
			update #TempPayrollProcessDetails 
            set UnPaidLeave=#TempPayrollProcessDetails.UnPaidLeave + isnull(T1.Leavedays,0.0) *0.5
            from (select count(*) as Leavedays,Employee_Code_Id from #TempProcessedData
            where ANAttendanceCode in (Select LeaveType from Ta_Tbl_LeaveType where IsDeleted=0 and LeaveCategory=0)
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id

			-- 1 Full pay
			 --First Half Leave Days update
            update #TempPayrollProcessDetails 
            set FullPaidLeave=isnull(T1.Leavedays,0.0) *0.5
            from (select count(*) as Leavedays,Employee_Code_Id from #TempProcessedData
            where FNAttendanceCode in (Select LeaveType from Ta_Tbl_LeaveType where IsDeleted=0 and LeaveCategory=1)
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id

			--Secod Half Leave Days update
			update #TempPayrollProcessDetails 
            set FullPaidLeave=#TempPayrollProcessDetails.FullPaidLeave + isnull(T1.Leavedays,0.0) *0.5
            from (select count(*) as Leavedays,Employee_Code_Id from #TempProcessedData
            where ANAttendanceCode in (Select LeaveType from Ta_Tbl_LeaveType where IsDeleted=0 and LeaveCategory=1)
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id

			-- 2 Half Pay
			 --First Half Leave Days update
            update #TempPayrollProcessDetails 
            set HalfPaidLeave=isnull(T1.Leavedays,0.0) *0.5
            from (select count(*) as Leavedays,Employee_Code_Id from #TempProcessedData
            where FNAttendanceCode in (Select LeaveType from Ta_Tbl_LeaveType where IsDeleted=0 and LeaveCategory=2)
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id

			--Secod Half Leave Days update
			update #TempPayrollProcessDetails 
            set HalfPaidLeave=#TempPayrollProcessDetails.HalfPaidLeave + isnull(T1.Leavedays,0.0) *0.5
            from (select count(*) as Leavedays,Employee_Code_Id from #TempProcessedData
            where ANAttendanceCode in (Select LeaveType from Ta_Tbl_LeaveType where IsDeleted=0 and LeaveCategory=2)
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id

			--END

            update #TempPayrollProcessDetails 
            set Leavedays=#TempPayrollProcessDetails.Leavedays + isnull(T1.Leavedays,0.0) *0.5
            from (select count(*) as Leavedays,Employee_Code_Id from #TempProcessedData
            where FNAttendanceCode in ('RH')
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id
            update #TempPayrollProcessDetails 
            set Leavedays=#TempPayrollProcessDetails.Leavedays + isnull(T1.Leavedays,0.0) *0.5
            from (select count(*) as Leavedays,Employee_Code_Id from #TempProcessedData
            where ANAttendanceCode in ('RH')
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id
            --First Half Present Days update
            update #TempPayrollProcessDetails 
            set Presentdays=isnull(T1.Presentdays,0.0) *0.5
            from (select count(*) as Presentdays,Employee_Code_Id from #TempProcessedData
            where FNAttendanceCode='Present' or FNAttendanceCode='Comp Off'
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id
            --Second Half Present Days update
            update #TempPayrollProcessDetails 
            set Presentdays=#TempPayrollProcessDetails.Presentdays + isnull(T1.Presentdays,0.0) *0.5
            from (select count(*) as Presentdays,Employee_Code_Id from #TempProcessedData
            where ANAttendanceCode='Present'  or ANAttendanceCode='Comp Off'
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id
            --First Half Absent Days update
            update #TempPayrollProcessDetails 
            set Absentdays=isnull(T1.Absentdays,0.0) *0.5
            from (select count(*) as Absentdays,Employee_Code_Id from #TempProcessedData
            where FNAttendanceCode= 'UUL'--- 'Absent'
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id
            --Second Half Absent Days update
            update #TempPayrollProcessDetails 
            set Absentdays=#TempPayrollProcessDetails.Absentdays + isnull(T1.Absentdays,0.0) *0.5
            from (select count(*) as Absentdays,Employee_Code_Id from #TempProcessedData
            where ANAttendanceCode='UUL'--- 'Absent'
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id
            --First Half Holiday/Weekly Off Days update
            update #TempPayrollProcessDetails 
            set WeekOffDays=isnull(T1.Holidays,0.0) *0.5
            from (select count(*) as Holidays,Employee_Code_Id from #TempProcessedData
            where FNAttendanceCode='Weekly Off'
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails        
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id
            update #TempPayrollProcessDetails 
            set Holidays=isnull(T1.Holidays,0.0) *0.5
            from (select count(*) as Holidays,Employee_Code_Id from #TempProcessedData
            where FNAttendanceCode='Holiday'
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id
            --Second Half Holiday/Weekly  Days update
            update #TempPayrollProcessDetails 
            set WeekOffDays=#TempPayrollProcessDetails.WeekOffDays + isnull(T1.Holidays,0.0) *0.5
            from (select count(*) as Holidays,Employee_Code_Id from #TempProcessedData
            where ANAttendanceCode='Weekly Off'
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id
            update #TempPayrollProcessDetails 
            set Holidays=#TempPayrollProcessDetails.Holidays + isnull(T1.Holidays,0.0) *0.5
            from (select count(*) as Holidays,Employee_Code_Id from #TempProcessedData
            where ANAttendanceCode='Holiday'
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id
            --First Half LOP Days update
            update #TempPayrollProcessDetails 
            set LOPDays=isnull(T1.LOPDays,0.0) *0.5
            from (select count(*) as LOPDays,Employee_Code_Id from #TempProcessedData
            where FNAttendanceCode='UL'-- 'LOP' 
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id
            --Second Half LOP Days update
            update #TempPayrollProcessDetails 
            set LOPDays=#TempPayrollProcessDetails.LOPDays + isnull(T1.LOPDays,0.0) *0.5
            from (select count(*) as LOPDays,Employee_Code_Id from #TempProcessedData
            where ANAttendanceCode='UL'-- 'LOP'
            Group by Employee_Code_Id) as T1 inner join #TempPayrollProcessDetails
            on #TempPayrollProcessDetails.Employee_Id=T1.Employee_Code_Id
            update #TempPayrollProcessDetails set Emp_Id=@AuditEmpId
            update #TempPayrollProcessDetails set [TimeStamp]=getdate()       
            if(@ProcessType='Process')
            BEGIN
                  if(@ProcessCount=0)
                  BEGIN
                  --select 1
                              insert into  TA_Tbl_PayrollProcessDetails (MonthClosure_Id, Employee_Id, HrsWork, HrsOT, NormOT, OffOT, HolOT, EarlyOut, LateIn, WorkHrs, HrsLess, HrsExtra, 
                              LeaveDays, AbsentDays, PresentDays, Holidays, LOPDays, Emp_Id, [TimeStamp],TotalPermissionHours,WeekOffDays,SPEC_NormOT1,SPEC_NormOT2,SPEC_OffOT1,SPEC_OffOT2,SPEC_HOLOT1,SPEC_HOLOT2,
                              Shift_Allow,Food_Allow,Meal_Allow,SH_Allow,Attendance_Allow,FullPaidLeave,HalfPaidLeave,UnPaidLeave)
                              select MonthClosure_Id, Employee_Id, HrsWork, HrsOT, NormOT, OffOT, HolOT, EarlyOut, LateIn, WorkHrs, HrsLess, HrsExtra, 
                              LeaveDays, AbsentDays, PresentDays, Holidays, LOPDays, Emp_Id, [TimeStamp],TotalPermissionHours,WeekOffDays,SPEC_NormOT1,SPEC_NormOT2,SPEC_OffOT1,SPEC_OffOT2,SPEC_HOLOT1,SPEC_HOLOT2,Shift_Allow,
                              Food_Allow,Meal_Allow,SH_Allow,Att_Allow,FullPaidLeave,HalfPaidLeave,UnPaidLeave from #TempPayrollProcessDetails
                              Insert into ta_tbl_monthClosureHistory values(@MonthCloseID,@AuditEmpId,GETDATE(),'P')
                  END
                  else
                  BEGIN
                              update TA_Tbl_PayrollProcessDetails set HrsWork=T1.HrsWork, HrsOT=T1.HrsOT, NormOT=T1.NormOT, OffOT=T1.OffOT, HolOT=T1.HolOT, EarlyOut=T1.EarlyOut, LateIn=T1.LateIn, WorkHrs=T1.WorkHrs, HrsLess=T1.HrsLess, HrsExtra=T1.HrsExtra, 
                              LeaveDays=T1.LeaveDays, 
							  AbsentDays=T1.AbsentDays, 
							  PresentDays=T1.PresentDays, 
							  Holidays=T1.Holidays, 
							  LOPDays=T1.LOPDays,
							  TotalPermissionHours=T1.TotalPermissionHours
                              ,WeekOffDays=T1.WeekOffDays
                              ,SPEC_NormOT1=T1.SPEC_NormOT1,SPEC_NormOT2=T1.SPEC_NormOT2,SPEC_OffOT1=T1.SPEC_OffOT1,SPEC_OffOT2=T1.SPEC_OffOT2,
                              SPEC_HOLOT1=T1.SPEC_HOLOT1,SPEC_HOLOT2=T1.SPEC_HOLOT2,
                              Shift_Allow=T1.Shift_Allow,
							  Food_Allow=T1.Food_Allow,
							  Meal_Allow=T1.Meal_Allow,
							  SH_Allow	=T1.SH_Allow,
							  Attendance_Allow=T1.Att_Allow,
							  FullPaidLeave=T1.FullPaidLeave,
							  HalfPaidLeave=T1.HalfPaidLeave,
							  UnPaidLeave=T1.UnPaidLeave
                              from #TempPayrollProcessDetails T1 inner join TA_Tbl_PayrollProcessDetails T2
                              on T1.MonthClosure_Id=T2.MonthClosure_Id and T1.Employee_Id=T2.Employee_Id
                              Insert into ta_tbl_monthClosureHistory values(@MonthCloseID,787,GETDATE(),'UP')
                  END               
      END
      else if (@ProcessType='Preview')
      BEGIN
                  delete from TA_Tbl_TempPayrollProcessDetails where MonthClosure_Id=@MonthCloseID
                  insert into  TA_Tbl_TempPayrollProcessDetails (MonthClosure_Id, Employee_Id, HrsWork, HrsOT, NormOT, OffOT, HolOT, EarlyOut, LateIn, WorkHrs, HrsLess, HrsExtra, 
                  LeaveDays, AbsentDays, PresentDays, Holidays, LOPDays, Emp_Id, [TimeStamp],TotalPermissionHours,WeekOffDays,SPEC_NormOT1,SPEC_NormOT2,SPEC_OffOT1,SPEC_OffOT2,SPEC_HOLOT1,SPEC_HOLOT2,
                  Shift_Allow,Food_Allow,Meal_Allow,SH_Allow,Attendance_Allow,FullPaidLeave,HalfPaidLeave,UnPaidLeave)
                  select MonthClosure_Id, Employee_Id, HrsWork, HrsOT, NormOT, OffOT, HolOT, EarlyOut, LateIn, WorkHrs, HrsLess, HrsExtra, 
                  LeaveDays, AbsentDays, PresentDays, Holidays, LOPDays, Emp_Id, [TimeStamp],TotalPermissionHours,WeekOffDays,SPEC_NormOT1,SPEC_NormOT2,SPEC_OffOT1,SPEC_OffOT2,SPEC_HOLOT1,SPEC_HOLOT2,Shift_Allow,
                              Food_Allow,Meal_Allow,SH_Allow,Att_Allow,FullPaidLeave,HalfPaidLeave,UnPaidLeave
                  from #TempPayrollProcessDetails
      END
            select MonthClosure_Id, Employee_Id as Employee_Code_Id, HrsWork, HrsOT, NormOT, OffOT, HolOT, EarlyOut, LateIn, WorkHrs, HrsLess, HrsExtra, 
            LeaveDays, AbsentDays, PresentDays, Holidays, LOPDays, Emp_Id, [TimeStamp],TotalPermissionHours,WeekOffDays,SPEC_NormOT1=isnull(SPEC_NormOT1,'00:00'),SPEC_NormOT2=isnull(SPEC_NormOT2,'00:00'),SPEC_OffOT1=isnull(SPEC_OffOT1,'00:00'),SPEC_OffOT2=isnull(SPEC_OffOT2,'00:00'),SPEC_HOLOT1=isnull(SPEC_HOLOT1,'00:00'),SPEC_HOLOT2=isnull(SPEC_HOLOT2,'00:00')
            ,Shift_Allow=isnull(Shift_Allow,0),Food_Allow=isnull(Food_Allow,0),Meal_Allow=isnull(Meal_Allow,0),SH_Allow=isnull(SH_Allow,0),Attendance_Allow=isnull(Attendance_Allow,0),PayrollProcess_Id,FullPaidLeave,HalfPaidLeave,UnPaidLeave from TA_Tbl_PayrollProcessDetails
            --Remove the temporary tables here  
            drop table #TempPayrollProcessDetails
      end
      COMMIT
END TRY
BEGIN CATCH
      -- Whoops, there was an error
      IF @@TRANCOUNT > 0
      ROLLBACK
      -- Raise an error with the details of the exception
      DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
      SELECT @ErrMsg = ERROR_MESSAGE(),
      @ErrSeverity = ERROR_SEVERITY()
      RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
      --Remove the temporary tables here  
      drop table #EmpLevel
      drop table #LevelRights
      drop table #TempProcessedData

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[Proc_TA_SelectMonthClosureDetails]  
-- Add the parameters for the stored procedure here  
@monthClosureId int,  
@levelId int,   
@startDate nvarchar(25),  
@endDate nvarchar(25),  
@closedDate nvarchar(25)  
AS  
declare @sqlQuery as nvarchar(4000)      
set @sqlQuery=''      
BEGIN  
	-- SET NOCOUNT ON added to prevent extra result sets from  
	-- interfering with SELECT statements.  
	--insert into testingmonthclosure values(@monthClosureId,@levelId,@startDate,@endDate,@closedDate)
	SET NOCOUNT ON;  
	--Temporary table's creation
	create table #EmpLevel(EmployeeId int,LevelId int,EffectiveDate datetime)
	create table #EmpDesignation(EmployeeId int,DesignationId int,EffectiveDate datetime)
	create table #EmpType(EmployeeId int,TypeId int,EffectiveDate datetime)
	create table #EmpCategory(EmployeeId int,CategoryId int,EffectiveDate datetime)
	--Employee Level Indexes
	/****** Object:  Index [IDX_Effective]    Script Date: 05/27/2009 16:28:22 ******/
	CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpLevel] 
	(
		[EffectiveDate] ,[LevelId],[EmployeeId] ASC
	) ON [PRIMARY]
	--Employee Designation Indexes
	/****** Object:  Index [IDX_Effective]    Script Date: 05/27/2009 16:28:22 ******/
	CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpDesignation] 
	(
		[EffectiveDate] ,[DesignationId],[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],[TypeId],[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] ,[CategoryId],[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
	--Employee Designation insert in Temporary table
	insert into #EmpDesignation select EDesig.EmployeeId,ed.DesignationId,EDesig.EffectiveDate from (
	select max(ed.EffectiveDate) as EffectiveDate,ed.EmployeeId as EmployeeId from TA_Tbl_EmployeeDesignation ed
	inner join ta_tbl_employee emp on ed.EmployeeId = emp.Employee_Id 
	where emp.Isdeleted=0 group by EmployeeId) EDesig
	inner join TA_Tbl_EmployeeDesignation ED on ED.EffectiveDate=EDesig.EffectiveDate and ED.employeeid=EDesig.EmployeeId
	AND ED.EffectiveDate <= CONVERT(DATETIME,cast(getdate() as nvarchar(50)), 102) and ED.DesignationId is not null
	--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
	--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
	-- Insert statements for procedure here  
	Declare @isShownSpecialOT int
	Declare @NormalSPOT2 int
	Declare @OffSPOT1 int
	Declare @OffSPOT2 int
	Declare @HolidaySPOT1 int
	Declare @HolidaySPOT2 int
	Declare @ShiftAllow int
	Declare @FoodAllow int
	Declare @MealAllow int
	Declare @SHAllow int
	Declare @Att_Allow int
	set @isShownSpecialOT =0
	set @NormalSPOT2 =0
	set @OffSPOT1 =0
	set @OffSPOT2 =0
	set @HolidaySPOT1 =0
	set @HolidaySPOT2 =0
	set @ShiftAllow =0
	set @FoodAllow =0
	set @MealAllow =0
	set @SHAllow =0
	set @Att_Allow =0
	Declare @CompanyID bigint
	if((select parentId from Ta_Tbl_levelDetails where id=@levelId)!=0)
	begin
		;WITH ParentLevel AS
			(
				SELECT *, 1 AS Lvl FROM Ta_Tbl_levelDetails WHERE id = @levelId
				UNION ALL
				SELECT rh.*, Lvl+1 AS Lvl FROM Ta_Tbl_levelDetails rh
				INNER JOIN ParentLevel rc ON rh.id = rc.ParentId
			)
			SELECT @CompanyID=id
			FROM ParentLevel r where parentId=0
			ORDER BY lvl DESC
	end
	else
	begin
		set @CompanyID=@levelId
	end
	select	@isShownSpecialOT=IsSpecialOTRequired,
			@NormalSPOT2=NormalSPOT2,
			@OffSPOT1=OffSPOT1,
			@OffSPOT2=OffSPOT2,
			@HolidaySPOT1=HolidaySPOT1,
			@HolidaySPOT2=HolidaySPOT2,
			@ShiftAllow=ShiftAllow,
			@FoodAllow=FoodAllow,
			@MealAllow=MealAllow,
			@SHAllow=SHAllow,
			@Att_Allow=Att_Allow  from TA_Tbl_ShiftSettings where CompanyId=@CompanyID
	set @sqlQuery = @sqlQuery + 'select e.Employee_Code_Id as Employee_Id,
										isnull(e.Employee_FirstName,'''') +  space(1) + isnull(e.Employee_LastName,'''') as EmployeeName,'  
	set @sqlQuery = @sqlQuery + 'isnull(ppd.PresentDays,0) as PresentDays,
								 isnull(ppd.AbsentDays,0) as AbsentDays,
								 isnull(ppd.LeaveDays,0) as LeaveDays,
								 isnull(ppd.WeekOffDays,0) as WeekOffDays,
								 isnull(ppd.LOPDays,0) as LOPDays,
								 isnull(ppd.Holidays,0) as Holidays,
								 ppd.HrsWork,
								 isnull(ppd.HrsExtra,''00:00'') HrsExtra,
								 isnull(ppd.HrsLess,''00:00'') HrsLess,
								 ppd.NormOT,
								 ppd.OffOT,
								 ppd.HolOT,
								 isnull(ppd.TotalPermissionHours,''00:00'') TotalPermissionHours,
								 ppd.LateIn,
								 ppd.EarlyOut,'
	set @sqlQuery = @sqlQuery + '
								 SPEC_NormOT1=isnull(SPEC_NormOT1,''00:00''),
								 SPEC_NormOT2=isnull(SPEC_NormOT2,''00:00''),
								 SPEC_OffOT1=isnull(SPEC_OffOT1,''00:00''),SPEC_OffOT2=isnull(SPEC_OffOT2,''00:00''),
								 SPEC_HOLOT1=isnull(SPEC_HOLOT1,''00:00''),SPEC_HOLOT2=isnull(SPEC_HOLOT2,''00:00''),
								 Shift_Allow=isnull(ppd.Shift_Allow,0),
								 Food_Allow=isnull(ppd.Food_Allow,0),
								 Meal_Allow=isnull(ppd.Meal_Allow,0),
								 SH_Allow=isnull(ppd.SH_Allow,0),
								 Att_Allow=isnull(Attendance_Allow,0),
								isShownSpecialOT='+convert(varchar(20),isnull(@isShownSpecialOT,0))+',
								NormalSPOT2='+convert(varchar(20),isnull(@NormalSPOT2,0))+',
								OffSPOT1='+convert(varchar(20),isnull(@OffSPOT1,0))+',
								OffSPOT2='+convert(varchar(20),isnull(@OffSPOT2,0))+',
								HolidaySPOT1='+convert(varchar(20),isnull(@HolidaySPOT1,0))+',
								HolidaySPOT2='+convert(varchar(20),isnull(@HolidaySPOT2,0))+',
								ShiftAllow='+convert(varchar(20),isnull(@ShiftAllow,0))+',
								FoodAllow	='+convert(varchar(20),isnull(@FoodAllow,0))+',
								MealAllow	='+convert(varchar(20),isnull(@MealAllow,0))+',
								SHAllow='+convert(varchar(20),isnull(@SHAllow,0))+',
								Att_Allow='+convert(varchar(20),isnull(@Att_Allow,0))+',mc.MonthClosure_Id,PayrollProcess_Id,
								isnull(ppd.FullPaidLeave,0) as FullPaidLeave,
								isnull(ppd.HalfPaidLeave,0) as HalfPaidLeave,
								isnull(ppd.UnPaidLeave,0) as UnPaidLeave
								from TA_Tbl_MonthClosure mc'
	set @sqlQuery = @sqlQuery + ' inner join TA_Tbl_PayrollProcessDetails ppd on ppd.MonthClosure_Id=mc.MonthClosure_Id'      
	set @sqlQuery = @sqlQuery + ' inner join Ta_Tbl_Employee e on e.SwipeCardNo=ppd.Employee_Id'    
	Set @sqlQuery = @sqlQuery + ' INNER JOIN TA_Tbl_EmployeeLevel Lev ON Lev.EmployeeID= e.Employee_Id '  
	Set @sqlQuery = @sqlQuery + ' INNER JOIN TA_Tbl_LevelDetails L ON  Lev.LevelID=L.Id '   
	Set @sqlQuery = @sqlQuery + ' INNER JOIN TA_Tbl_EmployeeDesignation EDesig ON EDesig.EmployeeID= e.Employee_Id '  
	Set @sqlQuery = @sqlQuery + ' INNER JOIN TA_Tbl_Title T ON  T.TitleId=EDesig.DesignationId '  
	SET @sqlQuery = @sqlQuery + ' INNER JOIN TA_Tbl_Grade ON TA_Tbl_Grade.Grade_ID = T.GradeId '  
	set @sqlQuery = @sqlQuery + ' inner join TA_Tbl_EmployeeCategory  on e.Employee_Id = TA_Tbl_EmployeeCategory.EmployeeId'  
	set @sqlQuery = @sqlQuery + ' inner join TA_Tbl_Category on TA_Tbl_EmployeeCategory.CategoryId = TA_Tbl_Category.CategoryId'  
	set @sqlQuery = @sqlQuery + ' inner join TA_Tbl_EmployeeType  on e.Employee_Id = TA_Tbl_EmployeeType.EmployeeId'  
	set @sqlQuery = @sqlQuery + ' inner join TA_Tbl_Type on TA_Tbl_EmployeeType.TypeId = TA_Tbl_Type.TypeId'   
	set @sqlQuery = @sqlQuery + ' Inner join #EmpLevel on #EmpLevel.EffectiveDate=Lev.EffectiveDate and #EmpLevel.employeeid=Lev.EmployeeId '
	set @sqlQuery = @sqlQuery + ' Inner join #EmpDesignation on #EmpDesignation.EffectiveDate=EDesig.EffectiveDate and #EmpDesignation.employeeid=EDesig.EmployeeId '
	set @sqlQuery = @sqlQuery + ' Inner join #EmpType on #EmpType.EffectiveDate=TA_Tbl_EmployeeType.EffectiveDate and #EmpType.employeeid=TA_Tbl_EmployeeType.EmployeeId '
	set @sqlQuery = @sqlQuery + ' Inner join #EmpCategory on #EmpCategory.EffectiveDate=TA_Tbl_EmployeeCategory.EffectiveDate and #EmpCategory.employeeid=TA_Tbl_EmployeeCategory.EmployeeId '
	set @sqlQuery = @sqlQuery + ' where e.Isdeleted=0 and mc.IsDeleted=0 and mc.MonthClosure_StartDate=CONVERT(DATETIME, ''' +cast( @startDate as nvarchar(50))+ ''', 102) and mc.MonthClosure_EndDate=CONVERT(DATETIME, ''' +cast( @endDate as nvarchar(50))+ ''', 102) and mc.MonthClosure_ClosingDate=CONVERT(DATETIME, ''' +cast( @closedDate as nvarchar(50))+ ''', 102) And mc.MonthClosure_Id = '+cast(@monthClosureId as nvarchar(4))+' And mc.OrgLevel_Id = '+cast(@levelId as nvarchar(10))+''  
	set @sqlQuery = @sqlQuery + ' order by e.Employee_Code_Id'         
	exec(@sqlQuery)  
	select isShownSpecialOT=isnull(@isShownSpecialOT,0),
	NormalSPOT2=isnull(@NormalSPOT2,0),
	OffSPOT1=isnull(@OffSPOT1,0),
	OffSPOT2=isnull(@OffSPOT2,0),
	HolidaySPOT1=isnull(@HolidaySPOT1,0),
	HolidaySPOT2=isnull(@HolidaySPOT2,0),
	ShiftAllow=isnull(@ShiftAllow,0),
	FoodAllow	=isnull(@FoodAllow,0),
	MealAllow	=isnull(@MealAllow,0),
	SHAllow=isnull(@SHAllow,0),
	Att_Allow=isnull(@Att_Allow,0)
	--Remove the temporary tables here  
	drop table #EmpLevel
	drop table #EmpDesignation
	drop table #EmpType
	drop table #EmpCategory
END 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ALTER PROCEDURE [dbo].[Proc_Ta_ImportLeaveData]
	@LeaveData XML = NULL,
	@EmpAutoID bigInt,
	@UserGroup VARCHAR(max)
AS
BEGIN
	Declare @local_LeaveData XML = @LeaveData
	Declare	@local_EmpAutoID bigInt=@EmpAutoID
	Declare @local_UserGroup VARCHAR(max)=@UserGroup
	
	set nocount on
	DECLARE @XMLParams INT, @YearSettingId INT, @NosId INT
	DECLARE @ErrMsg VARCHAR(MAX), @ErrIds VARCHAR(MAX)
	DECLARE @StartDate DATETIME
	DECLARE @ErrorMsg TABLE
	(
		RecordID int,
		Employee_Id VARCHAR(100), 
		Error_Msg VARCHAR(MAX),
		flag bit default(1),
		date datetime
	)	
	BEGIN TRY
		BEGIN TRAN ImportLeaveData
			EXEC SP_XML_PrePareDocument @XMLParams OUTPUT, @local_LeaveData	
			DECLARE @alertMsg VARCHAR(MAX)
			SET @alertMsg=''
			CREATE TABLE #EMPLEVEL
			(
				EMPLOYEEID INT,
				LEVELID INT,
				EFFECTIVEDATE DATETIME,
				EMPLOYEE_CODE_ID VARCHAR(50),
				SWIPECARD VARCHAR(50)
			)
			CREATE TABLE #LEVELRIGHTS(LEVELID INT)			
			INSERT INTO  #LEVELRIGHTS SELECT * FROM [DBO].[FN_TA_GETPARENTCHILD](@local_UserGroup,0) AS LEVELID
				
			DECLARE @SQLEMPLEVEL			NVARCHAR(4000)			
			SET @SQLEMPLEVEL=
			'INSERT INTO #EMPLEVEL SELECT ELEVEL.EMPLOYEEID,EL.LEVELID,ELEVEL.EFFECTIVEDATE,EMPLOYEE_CODE_ID,SWIPECARDNO  FROM 
			(
				SELECT MAX(EL.EFFECTIVEDATE) AS EFFECTIVEDATE,EL.EMPLOYEEID AS EMPLOYEEID,EMP.EMPLOYEE_CODE_ID,EMP.SWIPECARDNO FROM TA_TBL_EMPLOYEELEVEL EL
				INNER JOIN TA_TBL_EMPLOYEE EMP ON EL.EMPLOYEEID = EMP.EMPLOYEE_ID 
				INNER JOIN #LEVELRIGHTS ON #LEVELRIGHTS.LEVELID=EL.LEVELID
				WHERE  SWIPECARDNO IS NOT NULL AND EMP.ISDELETED=0 '
			SET @SQLEMPLEVEL = @SQLEMPLEVEL +' GROUP BY EMPLOYEEID,EMPLOYEE_CODE_ID,EMP.SWIPECARDNO
			) ELEVEL
			INNER JOIN TA_TBL_EMPLOYEELEVEL EL ON EL.EFFECTIVEDATE=ELEVEL.EFFECTIVEDATE AND EL.EMPLOYEEID=ELEVEL.EMPLOYEEID
			INNER JOIN #LEVELRIGHTS ON #LEVELRIGHTS.LEVELID=EL.LEVELID
			AND EL.EFFECTIVEDATE <= CONVERT(DATETIME,'''+CAST(dbo.DateOnly(getdate()) AS NVARCHAR(50))+''', 102) AND EL.LEVELID IS NOT NULL'
			EXECUTE(@SQLEMPLEVEL)			
			
			create table #TempLeaveApp
			(
				autoid int identity(1,1),
				[Sl.no] NVARCHAR(20), 
				Employee_Code_Id NVARCHAR(20), 
				Fromdate NVARCHAR(20), 
				Todate NVARCHAR(20),
				LeaveType NVARCHAR(15),
				NumberOfDays float,
				Reason NVARCHAR(max),
				EmpAutoID bigint
			)	
					
			INSERT INTO #TempLeaveApp
			SELECT Tbl.*,E.Employee_Id FROM OPENXML (@XMLParams, '/dsLeave/dtLeave', 2) WITH 
			(
				[Sl.no] NVARCHAR(20), 
				Employee_Code_Id NVARCHAR(20), 
				FromDate NVARCHAR(20), 
				ToDate NVARCHAR(20), 
				LeaveType NVARCHAR(15),
				NumberOfDays float,				
				Reason NVARCHAR(max) 
			) Tbl			
			LEFT OUTER JOIN TA_Tbl_Employee E ON E.Employee_Code_Id = Tbl.Employee_Code_Id
			AND E.IsDeleted = 0 AND E.Employee_Status = 1
			
			INSERT INTO @ErrorMsg
			SELECT autoid,Employee_Code_Id,'',1,Fromdate FROM #TempLeaveApp
			
			--select * from #TempLeaveApp
			
			EXEC SP_XML_RemoveDocument @XMLParams
			IF NOT EXISTS(SELECT Employee_Code_Id FROM #TempLeaveApp)
			BEGIN
				SET @ErrMsg = 'There is no record in the file'
				SET @ErrIds = 0
				RAISERROR(@ErrMsg, 16, @ErrIds, 'Error')
			END
			IF EXISTS(SELECT Employee_Code_Id FROM #TempLeaveApp WHERE ISNULL(Employee_Code_Id, '') = '')
			BEGIN
				UPDATE @ErrorMsg set Error_Msg=isnull(Error_Msg,'')+'Employee Id is missing;<br />',flag=0 FROM @ErrorMsg EM
				inner join #TempLeaveApp tot on tot.autoid=EM.RecordID 
				WHERE ISNULL(tot.Employee_Code_Id, '') = ''
			END			
			IF EXISTS(SELECT Employee_Code_Id FROM #TempLeaveApp WHERE ISNULL(Fromdate, '') = '')
			BEGIN
				UPDATE @ErrorMsg set Error_Msg=isnull(Error_Msg,'')+'From Date is missing;<br />',flag=0 FROM @ErrorMsg EM
				inner join #TempLeaveApp tot on tot.autoid=EM.RecordID 
				WHERE ISNULL(Fromdate,'')= ''
			END	
			IF EXISTS(SELECT Employee_Code_Id FROM #TempLeaveApp WHERE ISNULL(Todate, '') = '')
			BEGIN
				UPDATE @ErrorMsg set Error_Msg=isnull(Error_Msg,'')+'To Date is missing;<br />',flag=0 FROM @ErrorMsg EM
				inner join #TempLeaveApp tot on tot.autoid=EM.RecordID 
				WHERE ISNULL(tot.Todate, '') = ''
			END
			
			IF EXISTS(SELECT Employee_Code_Id FROM #TempLeaveApp WHERE ISNULL(LeaveType, '') = '')
			BEGIN
				UPDATE @ErrorMsg set Error_Msg=isnull(Error_Msg,'')+'Leave Type is missing;<br />',flag=0 FROM @ErrorMsg EM
				inner join #TempLeaveApp tot on tot.autoid=EM.RecordID 
				WHERE ISNULL(tot.LeaveType, '') = ''
			END	
			
			IF EXISTS(SELECT TLA.LeaveType FROM #TempLeaveApp TLA
			Left Outer Join TA_Tbl_LeaveType  LT on TLA.LeaveType=LT.LeaveType
			where LT.LeaveType is null and TLA.LeaveType is not null)
			BEGIN
				UPDATE @ErrorMsg set Error_Msg=isnull(Error_Msg,'')+'Leave Type is not available. Create the leave type in leave type master;<br />',flag=0 FROM @ErrorMsg EM
				inner join #TempLeaveApp tot on tot.autoid=EM.RecordID 				
				Left Outer Join TA_Tbl_LeaveType  LT on tot.LeaveType=LT.LeaveType
				where LT.LeaveType is null and tot.LeaveType is not null
			END
			
			IF EXISTS(SELECT Employee_Code_Id FROM #TempLeaveApp WHERE LEN(Reason)>1000)
			BEGIN
				UPDATE @ErrorMsg set Error_Msg=isnull(Error_Msg,'')+'Reason field filled more than 1000 characters;<br />',flag=0 FROM @ErrorMsg EM
				inner join #TempLeaveApp tot on tot.autoid=EM.RecordID 
				WHERE LEN(Reason)>1000
			END		
			
			IF EXISTS(SELECT Employee_Code_Id FROM #TempLeaveApp WHERE Todate<Fromdate)
			BEGIN
				UPDATE @ErrorMsg set Error_Msg=isnull(Error_Msg,'')+'Todate should be greater than from date;<br />',flag=0 FROM @ErrorMsg EM
				inner join #TempLeaveApp tot on tot.autoid=EM.RecordID 
				WHERE Todate<Fromdate
			END	
			IF EXISTS(SELECT Employee_Code_Id FROM #TempLeaveApp WHERE DATEDIFF(d,fromdate,todate)>0)
			BEGIN
				UPDATE @ErrorMsg set Error_Msg=isnull(Error_Msg,'')+'Leave should not be allowed to add more than one day;<br />',flag=0 FROM @ErrorMsg EM
				inner join #TempLeaveApp tot on tot.autoid=EM.RecordID 
			END		
			
			IF Exists(
			SELECT autoid FROM 
			(
				SELECT ROW_NUMBER()OVER(PARTITION BY Employee_Code_Id,fromdate ORDER BY Employee_Code_Id ASC)AS duplicate,
						autoid FROM #TempLeaveApp
			)AS A WHERE duplicate>1)
			BEGIN
				set @alertMsg='Duplication Record Found;Please find and remove duplicate record in excel file;<br />'				
				UPDATE @ErrorMsg SET Error_Msg=	CASE	WHEN Error_Msg!='' THEN Error_Msg+@alertMsg
														Else @alertMsg
												END,
				flag=0 FROM @ErrorMsg EM
				INNER JOIN
				(
					SELECT ROW_NUMBER()OVER(PARTITION BY Employee_Code_Id,fromdate ORDER BY Employee_Code_Id ASC)AS duplicate,
					Employee_Code_Id,autoid FROM #TempLeaveApp
				)AS A on EM.RecordID=a.autoid WHERE a.duplicate>1
				and ISNULL(a.Employee_Code_Id, '') != ''
			END
			
			IF EXISTS(SELECT t.Employee_Code_Id FROM #TempLeaveApp t INNER JOIN ta_tbl_employee e on t.Employee_Code_Id=e.Employee_Code_Id
			LEFT OUTER JOIN #EMPLEVEL EL on e.Employee_Id=EL.EMPLOYEEID WHERE (EL.EMPLOYEEID IS NULL OR EL.EMPLOYEEID=''))
			BEGIN
				set @alertMsg='Employee is not available or you are not having an previlege to import overtime data for this employee;<br />'				
				UPDATE @ErrorMsg SET Error_Msg=CASE	WHEN Error_Msg!='' THEN Error_Msg+@alertMsg
														Else @alertMsg
												END,
				flag=0 FROM @ErrorMsg EM
				INNER JOIN #TempLeaveApp t on EM.RecordID=t.autoid				
				INNER JOIN ta_tbl_employee e on t.Employee_Code_Id=e.Employee_Code_Id
				LEFT OUTER JOIN #EMPLEVEL EL on e.Employee_Id=EL.EMPLOYEEID WHERE (EL.EMPLOYEEID IS NULL OR EL.EMPLOYEEID='')
				and ISNULL(t.Employee_Code_Id, '') != ''
			END
			
			IF EXISTS(SELECT Fromdate FROM #TempLeaveApp where Fromdate>(select Todate from TA_Tbl_YearSetting where IsActive=1))
			BEGIN
				UPDATE @ErrorMsg set Error_Msg=isnull(Error_Msg,'')+'Apply Leave on available Year settings Days;<br />',flag=0 FROM @ErrorMsg EM
				inner join #TempLeaveApp tot on tot.autoid=EM.RecordID 
				WHERE Fromdate>(select Todate from TA_Tbl_YearSetting where IsActive=1)
			END			
			
			IF EXISTS(SELECT Fromdate FROM #TempLeaveApp where Todate>(select Todate from TA_Tbl_YearSetting where IsActive=1))
			BEGIN
				UPDATE @ErrorMsg set Error_Msg=isnull(Error_Msg,'')+'Apply Leave on available Year settings Days;<br />',flag=0 FROM @ErrorMsg EM
				inner join #TempLeaveApp tot on tot.autoid=EM.RecordID 
				WHERE Todate>(select Todate from TA_Tbl_YearSetting where IsActive=1)
			END
			
			IF EXISTS(SELECT LAE.LeaveApplicationId from #TempLeaveApp TLA 
			Inner join TA_Tbl_LeaveApplicationExists LAE on (TLA.EmpAutoID=LAE.Employee_Id and TLA.Fromdate=LAE.Leave_FromDate and TLA.Todate=LAE.Leave_ToDate)
			Inner join TA_Tbl_LeaveApplication LA on (LAE.LeaveApplicationId=LA.LeaveApplication_Id)
			where LA.IsDeleted=0 and LA.Status!='Rejected')
			BEGIN
				UPDATE @ErrorMsg set Error_Msg=isnull(Error_Msg,'')+'Leave is already updated into system for this date;<br />',flag=0 FROM @ErrorMsg EM
				inner join #TempLeaveApp TLA on TLA.autoid=EM.RecordID 
				Inner join TA_Tbl_LeaveApplicationExists LAE on (TLA.EmpAutoID=LAE.Employee_Id and TLA.Fromdate=LAE.Leave_FromDate and TLA.Todate=LAE.Leave_ToDate)
				Inner join TA_Tbl_LeaveApplication LA on (LAE.LeaveApplicationId=LA.LeaveApplication_Id)
				where LA.IsDeleted=0 and LA.Status!='Rejected'
			END			
			IF NOT EXISTS(SELECT [Employee_Id] FROM @ErrorMsg WHERE flag=0)
			BEGIN								
				Declare @TotLeaveRecord int
				select @TotLeaveRecord=COUNT(*) from #TempLeaveApp				
				Declare @start int
				set @start=1				
				while @TotLeaveRecord>=@start
				begin					
					Declare @EmployeeID INT
					Declare @Leave_ApplicationDate DATETIME=null
					set @Leave_ApplicationDate=dbo.DateOnly(getdate())
					Declare @LeaveType_id INT
					
					Declare @Type NVARCHAR(20)
					set @Type='Leave'
					Declare @Leave_FromDate DATETIME
					Declare @Leave_ToDate DATETIME
					Declare @Leave_SessionFrom NVARCHAR(20)
					set @Leave_SessionFrom='First half'
					Declare @Leave_SessionTo NVARCHAR(20)
					set @Leave_SessionTo='Second half'
					Declare @NumberOfDays DECIMAL(8,2)
					set @NumberOfDays =1
					Declare @Reason NVARCHAR(250)
					Declare @IsBorrow INT
					set @IsBorrow=0
					Declare @auEmpId NVARCHAR(50)
					set @auEmpId=@EmpAutoID
					Declare @YearSettings_Id NVARCHAR(20)
					select @YearSettings_Id=YearSettings_Id from TA_Tbl_YearSetting where IsActive=1
					Declare @LeaveStatus NVARCHAR(30)
					set @LeaveStatus='Approved'
					Declare @SuccessiveYear INT
					set @SuccessiveYear=0
					Declare @Intervening char(5)				
					set @Intervening=''
					declare @Employee_Code_Id varchar(20)
					
					Declare @leaveTypeName varchar(20)
					
					select @leaveTypeName=LeaveType,@Employee_Code_Id=Employee_Code_Id,@EmployeeID=EmpAutoID,@Leave_FromDate=Fromdate,@Leave_ToDate=Todate,@Reason=Reason from #TempLeaveApp where autoid=@start
					select @LeaveType_id=LeaveType_Id from TA_Tbl_LeaveType where LeaveType=@leaveTypeName
					
					--select @EmployeeID,@Leave_ApplicationDate,
					--@LeaveType_id,@Type,@Leave_FromDate,@Leave_ToDate,@Leave_SessionFrom ,@Leave_SessionTo ,
					--@NumberOfDays ,@Reason ,@IsBorrow,@auEmpId,@YearSettings_Id,@LeaveStatus ,@SuccessiveYear ,@Intervening
					
					Execute [Proc_TA_InsertLeaveApplication] @EmployeeID,@Leave_ApplicationDate,
					@LeaveType_id,@Type,@Leave_FromDate,@Leave_ToDate,@Leave_SessionFrom ,@Leave_SessionTo ,
					@NumberOfDays ,@Reason ,@IsBorrow,@auEmpId,@YearSettings_Id,@LeaveStatus ,@SuccessiveYear ,@Intervening
					
					--update TA_Tbl_ProcessedData set ProcessFlag=0 where Employee_Code_Id=@Employee_Code_Id
					--and Date=@Leave_FromDate
					
					set @start=@start+1
				end
			END
			ELSE 
			BEGIN
				SELECT	RecordID=RecordID+1,
						Employee_Id, 
						Error_Msg,
						date
				FROM @ErrorMsg where flag=0
			END
		COMMIT TRAN ImportLeaveData
			SELECT 'Msg:DataImportedSuccessfully' [Message]
	END TRY		
	BEGIN CATCH
		SET @ErrMsg = ERROR_MESSAGE()
		SET @ErrIds = @@ERROR
		ROLLBACK TRAN ImportLeaveData
		RAISERROR(@ErrMsg, 16, @ErrIds, 'Error')
	END CATCH
END
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ALTER PROCEDURE [dbo].[Proc_TA_DeleteLeaveApplication]
(
	@LeaveApplication_Id INT,
	@auEmpId INT
)
AS
BEGIN		
	SET NOCOUNT ON;
	DECLARE @CompOffId BIGINT 
	set @CompOffId= 0
	Declare @RemainingDays NUMERIC(18,1) 
	set @RemainingDays= 0.0
	declare @EmployeeId INT
	declare @NumberOfDays NUMERIC(18, 1)
	DECLARE @TblRecord TABLE(Id INT IDENTITY(1, 1), CompOffId INT, Process BIT)
	SELECT @EmployeeId = Employee_Id, @NumberOfDays = NumberOfDays FROM TA_Tbl_LeaveApplication
		WHERE LeaveApplication_Id = @LeaveApplication_Id
	INSERT INTO @TblRecord
		SELECT ExtraTime_Id, 0 FROM TA_Tbl_OverTimeStatus
			WHERE LeaveApplicationId LIKE '%:' + CAST(@LeaveApplication_Id AS VARCHAR(20)) + '%'
		ORDER BY [Date]
		declare @ValCount as int
		SELECT @ValCount=COUNT(*) FROM TA_Tbl_OverTimeStatus
			WHERE LeaveApplicationId LIKE '%:' + CAST(@LeaveApplication_Id AS VARCHAR(20)) + '%'
		WHILE ((EXISTS(SELECT Process FROM @TblRecord WHERE Process = 0)) AND @NumberOfDays > 0)
		BEGIN
		
			SET @CompOffId = (SELECT TOP 1 CompOffId FROM @TblRecord WHERE Process = 0 ORDER BY Id)
			UPDATE TA_Tbl_OverTimeStatus SET
				@RemainingDays = RemainingDays,
				RemainingDays = (CASE 
					WHEN (@ValCount=2) THEN @RemainingDays + 0.5 else case when (@ValCount>2) THEN CASE 
				WHEN (@NumberOfDays > NoOfDays) THEN NoOfDays
				ELSE (@RemainingDays + @NumberOfDays) end else NoOfDays end END),
				CompOffSatus = (CASE
					WHEN DateToExpired < GETDATE() THEN 'Expired'
					WHEN (@RemainingDays <= @NumberOfDays) OR (@RemainingDays > @NumberOfDays) THEN 'Not Taken' END),
				LeaveApplicationId = case	when @NumberOfDays>0.5 then REPLACE(LeaveApplicationId, ':' + CAST(@LeaveApplication_Id AS VARCHAR(20)), '') 
								else	case	when (select COUNT(Value) from dbo.Split(LeaveApplicationId,':') where Value=@LeaveApplication_Id)=2 
								then ':'+CAST(@LeaveApplication_Id AS VARCHAR(20))
												else REPLACE(LeaveApplicationId, ':' + CAST(@LeaveApplication_Id AS VARCHAR(20)), '') 
										end
					end
			WHERE ExtraTime_Id = @CompOffId
			SELECT @NumberOfDays = case when (@ValCount=2) then (@NumberOfDays - 0.5) else (@NumberOfDays - RemainingDays) end FROM TA_Tbl_OverTimeStatus
				WHERE ExtraTime_Id = @CompOffId
			UPDATE @TblRecord SET Process = 1 WHERE CompOffId = @CompOffId

		END
	--WHILE ((EXISTS(SELECT Process FROM @TblRecord WHERE Process = 0)) AND @NumberOfDays > 0)
	--BEGIN
	--	SET @CompOffId = (SELECT TOP 1 CompOffId FROM @TblRecord WHERE Process = 0 ORDER BY Id)
	--	UPDATE TA_Tbl_OverTimeStatus SET
	--		@RemainingDays = RemainingDays,
	--		RemainingDays = (CASE 
	--			WHEN (@NumberOfDays > NoOfDays) THEN (@RemainingDays + @NumberOfDays) - NoOfDays
	--			ELSE (@RemainingDays + @NumberOfDays) END),
	--		CompOffSatus = (CASE
	--			WHEN DateToExpired < GETDATE() THEN 'Expired'
	--			WHEN (@RemainingDays <= @NumberOfDays) OR (@RemainingDays > @NumberOfDays) THEN 'Not Taken' END),
	--		LeaveApplicationId = REPLACE(LeaveApplicationId, ':' + CAST(@LeaveApplication_Id AS VARCHAR(20)), '')
	--	WHERE ExtraTime_Id = @CompOffId
	--	SELECT @NumberOfDays = (@NumberOfDays - RemainingDays) FROM TA_Tbl_OverTimeStatus
	--		WHERE ExtraTime_Id = @CompOffId
	--	UPDATE @TblRecord SET Process = 1 WHERE CompOffId = @CompOffId
	--END
	Delete from TA_Tbl_LeaveApplication WHERE LeaveApplication_Id = @LeaveApplication_Id
	Delete from TA_Tbl_LeaveApplicationExists WHERE LeaveApplicationId = @LeaveApplication_Id
	Delete from TA_Tbl_LeaveApplicationPrintDetails_Eastman WHERE leaveAppID = @LeaveApplication_Id
	Delete from TA_Tbl_LeaveApplication_FileUpload WHERE LeaveApplication_Id = @LeaveApplication_Id
	--print 'test loop'
END


---------------------------------------------------------------------------------------------------------------------------------------------


ALTER PROCEDURE [dbo].[Proc_TA_GetRemainingNumberOfLeavesForSuccessiveYear]
(
	@Employee_Id int,
	@LeaveType_Id int,
	@YearSettingId int
)
AS
Begin
	SET NOCOUNT ON;
	declare @LeaveApplicationCount as decimal(18,1)
	declare @LOPCount as decimal(18,1)
	declare @CurrentyearSettingId int
	Declare @SuccessiveYearSettingId int
	Declare @SuccesiveLeavepolicyCount float
	Declare @MaxapplicableDays float
	Declare @CurrentYearStartDate datetime
	declare @isWFH int
	declare @IsProrataRequired int
	declare @IsDocProofRequired int
	select @CurrentyearSettingId=yearsettings_Id,@CurrentYearStartDate=FromDate from ta_tbl_yearsetting where Isactive=1	
	select top 1 @SuccessiveYearSettingId = yearsettings_Id from ta_tbl_yearsetting where yearSettings_Id > (select Yearsettings_Id from ta_tbl_yearsetting where Isactive=1)
	Declare @DateofJoining datetime
	Select @DateofJoining=DateOfJoining from TA_Tbl_Employee where Employee_Id=@Employee_Id and Isdeleted=0 and Employee_Status=1
	Select @isWFH=isWFH,@IsDocProofRequired =IsDocProofRequired from TA_Tbl_LeaveType where LeaveType_Id=@LeaveType_Id
	if(@LeaveType_Id<>-1)
	begin
		select @LeaveApplicationCount=sum(la.NumberOfDays) from Ta_Tbl_LeaveApplication la
		inner join TA_Tbl_YearSetting ys on ys.YearSettings_Id=la.YearSettings_Id 
		where ys.YearSettings_Id=@YearSettingId and la.LeaveType_Id=@LeaveType_Id 
		and la.Employee_Id=@Employee_Id and la.Type not in ('Restricted Holiday','Comp Off','ESI','LOP') 
		and la.status not in('Rejected') and la.isdeleted=0 and la.IsBorrow=0
		select @LOPCount=sum(la.NumberOfDays) from Ta_Tbl_LeaveApplication la
		inner join TA_Tbl_YearSetting ys on ys.YearSettings_Id=la.YearSettings_Id 
		where ys.YearSettings_Id=@YearSettingId and la.LeaveType_Id=@LeaveType_Id 
		and la.Employee_Id=@Employee_Id and la.Type in ('LOP') 
		and la.status not in('Rejected') and la.isdeleted=0 and la.IsBorrow=0
		if(@YearSettingId = @CurrentyearSettingId)
		begin
			if(@LeaveApplicationCount > 0)
			begin
				Select (ISNULL(yc.carry_PrevYear,0.0)+ ISNULL(yc.Current_Quota,0.0))- (ISNULL(sum(la.NumberOfDays),0.0) + ISNULL(yc.Encashment,0.0)) as RemainingNoOfLeavesCurrentYear,ISNULL(@LeaveApplicationCount,0) as availed,ISNULL(@LOPCount,0) as LOPavailed,
				case when(CASE	WHEN DATEPART(d,@DateofJoining)>15 then
						case	when	(case	when DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining))<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
												else DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining)),getdate())+1
										 end *	LPD.MaxDays_Month-ISNULL(sum(la.NumberOfDays),0.0))<0 then 0
								when	(case	when DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining))<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
												else DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining)),getdate())+1
										 end *	LPD.MaxDays_Month-ISNULL(sum(la.NumberOfDays),0.0))>LPD.No_Of_Days then LPD.No_Of_Days
								else	case	when DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining))<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
												else DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining)),getdate())+1
										end *LPD.MaxDays_Month
						end			
					else
						case	when	(case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
												else DATEDIFF(month,@DateofJoining,getdate())+1
										 end *	LPD.MaxDays_Month-ISNULL(sum(la.NumberOfDays),0.0))<0 then 0
								when	(case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
												else DATEDIFF(month,@DateofJoining,getdate())+1
										 end *	LPD.MaxDays_Month-ISNULL(sum(la.NumberOfDays),0.0))>LPD.No_Of_Days then LPD.No_Of_Days
								else	case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
												else DATEDIFF(month,@DateofJoining,getdate())+1
										end *LPD.MaxDays_Month
						end
			end - (ISNULL(sum(la.NumberOfDays),0.0) + ISNULL(yc.Encashment,0.0))-ISNULL(ltd.NoOffLeaveAvailed,0)+ISNULL(yc.carry_PrevYear,0.0))<=0 then 0
			else 
				CASE	WHEN DATEPART(d,@DateofJoining)>15 then
							case	when	(case	when DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining))<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
													else DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining)),getdate())+1
											 end *	LPD.MaxDays_Month-ISNULL(sum(la.NumberOfDays),0.0))<0 then 0
									when	(case	when DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining))<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
													else DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining)),getdate())+1
											 end *	LPD.MaxDays_Month-ISNULL(sum(la.NumberOfDays),0.0))>LPD.No_Of_Days then LPD.No_Of_Days
									else	case	when DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining))<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
													else DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining)),getdate())+1
											end *LPD.MaxDays_Month
							end			
						else
							case when LPD.No_Of_Days<=(
							case	when	(case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
													else DATEDIFF(month,@DateofJoining,getdate())+1
											 end *	LPD.MaxDays_Month-ISNULL(sum(la.NumberOfDays),0.0))<0 then 0
									when	(case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
													else DATEDIFF(month,@DateofJoining,getdate())+1
											 end *	LPD.MaxDays_Month-ISNULL(sum(la.NumberOfDays),0.0))>LPD.No_Of_Days then LPD.No_Of_Days
									else	case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
													else DATEDIFF(month,@DateofJoining,getdate())+1
											end *LPD.MaxDays_Month
							end) then LPD.No_Of_Days 
							else    case	when	(case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
													else DATEDIFF(month,@DateofJoining,getdate())+1
											 end *	LPD.MaxDays_Month-ISNULL(sum(la.NumberOfDays),0.0))<0 then 0
											when	(case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
															else DATEDIFF(month,@DateofJoining,getdate())+1
													 end *	LPD.MaxDays_Month-ISNULL(sum(la.NumberOfDays),0.0))>LPD.No_Of_Days then LPD.No_Of_Days
											else	case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
													else DATEDIFF(month,@DateofJoining,getdate())+1
											end *LPD.MaxDays_Month
									end
							end	
				end - (ISNULL(sum(la.NumberOfDays),0.0) + ISNULL(yc.Encashment,0.0))-ISNULL(ltd.NoOffLeaveAvailed,0)+ISNULL(yc.carry_PrevYear,0.0)+ISNULL(ltd.PreviousYearBalance,0.0)
			end 
				RemainingNoOfLeaves	,isnull(@isWFH,0) as isWFH,IsProrataRequired=isnull(@IsProrataRequired,1),IsDocProofRequired=isnull(@IsDocProofRequired,0)
				from TA_tbl_Yearclosure yc
				inner join TA_Tbl_YearSetting ys on yc.Year_SettingID=ys.YearSettings_Id
				inner join Ta_Tbl_LeaveApplication la on la.LeaveType_Id =yc.LeaveTypeId and yc.employee_Id=la.Employee_id and la.YearSettings_Id=ys.YearSettings_Id
				inner join TA_Tbl_Emp_LeavePolicy LP on (LP.EmployeeId=yc.Employee_Id and LP.YearSettingId=yc.Year_SettingID)
				inner join TA_Tbl_Emp_LeavePolicyDetails LPD on (LP.LeavePolicy_Id=LPD.Policy_ID and LP.Emp_LeavePolicyId=LPD.Emp_LeavePolicyId  and LPD.LeaveType_Id=yc.LeaveTypeID)
				left outer join ta_tbl_emp_LeaveTypedetails LTD on (LP.EmployeeId=LTD.EmployeeId and LTD.LeaveTypeId=LPD.LeaveType_Id and yc.Year_SettingID=LTD.YearSettingsId)
				where yc.employee_Id=@Employee_Id and LPD.LeaveType_Id=@LeaveType_Id and la.employee_Id=@Employee_Id and yc.LeaveTypeId=@LeaveType_Id and la.LeaveType_Id=@LeaveType_Id and ys.YearSettings_Id=@YearSettingId and la.Isdeleted=0 and la.Status in ('Pending','Forwarded','Approved') and la.Type not in ('Restricted Holiday','Comp Off','ESI','LOP') and la.IsBorrow=0 
				Group by Yc.LeaveTypeId,yc.Carry_PrevYear,yc.Current_Quota,yc.Borrow_NextYear,yc.Encashment,LPD.MaxDays_Month,ys.FromDate,LPD.No_Of_Days,NoOffLeaveAvailed,ltd.PreviousYearBalance
			end
			else 
			begin
				declare @TempYearSettingsId int
				--print's'
				set @TempYearSettingsId= (select Yearsettings_Id from ta_tbl_yearSetting where Isactive=1)				
				select (ISNULL(yc.carry_PrevYear,0.0)+ ISNULL(yc.Current_Quota,0.0)) as RemainingNoOfLeavesCurrentYear,ISNULL(@LeaveApplicationCount,0) as availed,ISNULL(@LOPCount,0) as LOPavailed,
				case when (CASE	WHEN DATEPART(d,@DateofJoining)>15 then
						case when	(case	when DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining))<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
											else DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining)),getdate())+1
									 end *LPD.MaxDays_Month- ISNULL(yc.Encashment,0.0))<0 then 0 
							 when	(case	when DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining))<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
											else DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining)),getdate())+1
									 end *LPD.MaxDays_Month- ISNULL(yc.Encashment,0.0))>LPD.No_Of_Days then LPD.No_Of_Days
							else case	when DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining))<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
										else DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining)),getdate())+1
								 end *LPD.MaxDays_Month- ISNULL(yc.Encashment,0.0)
						end
					else
						case when	(case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
											else DATEDIFF(month,@DateofJoining,getdate())+1
									 end *LPD.MaxDays_Month- ISNULL(yc.Encashment,0.0))<0 then 0 
							 when	(case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
											else DATEDIFF(month,@DateofJoining,getdate())+1
									 end *LPD.MaxDays_Month- ISNULL(yc.Encashment,0.0))>LPD.No_Of_Days then LPD.No_Of_Days
							else case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
										else DATEDIFF(month,@DateofJoining,getdate())+1
								 end *LPD.MaxDays_Month- ISNULL(yc.Encashment,0.0)
						end					
			end-ISNULL(ltd.NoOffLeaveAvailed,0)+ISNULL(yc.carry_PrevYear,0.0))<=0 then 0
			else 			
				CASE	WHEN DATEPART(d,@DateofJoining)>15 then
							case when	(case	when DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining))<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
												else DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining)),getdate())+1
										 end *LPD.MaxDays_Month- ISNULL(yc.Encashment,0.0))<0 then 0 
								 when	(case	when DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining))<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
												else DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining)),getdate())+1
										 end *LPD.MaxDays_Month- ISNULL(yc.Encashment,0.0))>LPD.No_Of_Days then LPD.No_Of_Days
								else case	when DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining))<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
											else DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining)),getdate())+1
									 end *LPD.MaxDays_Month- ISNULL(yc.Encashment,0.0)
							end
						else
							case when	(case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
												else DATEDIFF(month,@DateofJoining,getdate())+1
										 end *LPD.MaxDays_Month- ISNULL(yc.Encashment,0.0))<0 then 0 
								 when	(case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
												else DATEDIFF(month,@DateofJoining,getdate())+1
										 end *LPD.MaxDays_Month- ISNULL(yc.Encashment,0.0))>LPD.No_Of_Days then LPD.No_Of_Days
								else case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
											else DATEDIFF(month,@DateofJoining,getdate())+1
									 end *LPD.MaxDays_Month- ISNULL(yc.Encashment,0.0)
							end					
				end-ISNULL(ltd.NoOffLeaveAvailed,0)+ISNULL(yc.carry_PrevYear,0.0)+ISNULL(ltd.PreviousYearBalance,0.0)
			end RemainingNoOfLeaves,isnull(@isWFH,0) as isWFH,IsProrataRequired=isnull(@IsProrataRequired,1),IsDocProofRequired=isnull(@IsDocProofRequired,0)
				from TA_tbl_Yearclosure yc
				inner join TA_Tbl_YearSetting ys on yc.Year_SettingID=ys.YearSettings_Id 
				inner join TA_Tbl_Emp_LeavePolicy LP on (LP.EmployeeId=yc.Employee_Id and LP.YearSettingId=yc.Year_SettingID)
				inner join TA_Tbl_Emp_LeavePolicyDetails LPD on (LP.LeavePolicy_Id=LPD.Policy_ID and LP.Emp_LeavePolicyId=LPD.Emp_LeavePolicyId  and LPD.LeaveType_Id=yc.LeaveTypeID)
				left outer join ta_tbl_emp_LeaveTypedetails LTD on (LP.EmployeeId=LTD.EmployeeId and LTD.LeaveTypeId=LPD.LeaveType_Id and yc.Year_SettingID=LTD.YearSettingsId)
				where yc.employee_Id=@Employee_Id and yc.LeaveTypeId=@LeaveType_Id and ys.YearSettings_Id=@YearSettingId				
				Group by Yc.LeaveTypeId,yc.Carry_PrevYear,yc.Current_Quota,yc.Borrow_NextYear,yc.Encashment,LPD.MaxDays_Month,ys.FromDate,LPD.No_Of_Days,NoOffLeaveAvailed,ltd.PreviousYearBalance
			end
		end
		else if(@YearSettingId=@SuccessiveYearSettingId)
		begin	
			select top 1 @MaxapplicableDays=MaxDays_applicable from ta_tbl_leavepolicydetails where policy_Id=(select top 1 Leavepolicy_Id from ta_tbl_emp_leavepolicy where EmployeeId=@Employee_Id and LeaveType_Id=@LeaveType_Id order by ta_tbl_emp_leavepolicy.ToDate desc)
			select @SuccesiveLeavepolicyCount=sum(NumberofDays) from ta_tbl_LeaveApplication where YearSettings_Id=@YearSettingId and Isdeleted=0 and status <> 'Rejected' and Type not in ('Restricted Holiday','Comp Off','ESI','LOP') 
			and Employee_Id=@Employee_Id and LeaveType_Id=@LeaveType_Id
			if @SuccesiveLeavepolicyCount is null
			begin
				set @SuccesiveLeavepolicyCount=0.0
			end	
			select @MaxapplicableDays - @SuccesiveLeavepolicyCount as RemainingNoOfLeaves,ISNULL(@LeaveApplicationCount,0) as availed,ISNULL(@LOPCount,0) as LOPavailed
		end
	end
	else
	begin
		if(@YearSettingId = @CurrentyearSettingId)
		begin
			Select lt.LeaveType as 'Leave Type',(ISNULL(yc.carry_PrevYear,0.0)+ ISNULL(yc.Current_Quota,0.0))- (ISNULL(sum(la.NumberOfDays),0.0) + ISNULL(yc.Encashment,0.0)) as 'Number of Days / year',
			case when(CASE	WHEN DATEPART(d,@DateofJoining)>15 then
						case	when	(case	when DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining))<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
												else DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining)),getdate())+1
										 end *	LPD.MaxDays_Month-ISNULL(sum(la.NumberOfDays),0.0))<0 then 0
								when	(case	when DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining))<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
												else DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining)),getdate())+1
										 end *	LPD.MaxDays_Month-ISNULL(sum(la.NumberOfDays),0.0))>LPD.No_Of_Days then LPD.No_Of_Days
								else	case	when DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining))<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
												else DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining)),getdate())+1
										end *LPD.MaxDays_Month
						end			
					else
						case	when	(case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
												else DATEDIFF(month,@DateofJoining,getdate())+1
										 end *	LPD.MaxDays_Month-ISNULL(sum(la.NumberOfDays),0.0))<0 then 0
								when	(case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
												else DATEDIFF(month,@DateofJoining,getdate())+1
										 end *	LPD.MaxDays_Month-ISNULL(sum(la.NumberOfDays),0.0))>LPD.No_Of_Days then LPD.No_Of_Days
								else	case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
												else DATEDIFF(month,@DateofJoining,getdate())+1
										end *LPD.MaxDays_Month
						end
			end - (ISNULL(sum(la.NumberOfDays),0.0) + ISNULL(yc.Encashment,0.0))-ISNULL(ltd.NoOffLeaveAvailed,0)+ISNULL(yc.carry_PrevYear,0.0))<=0 then 0
			else 
				CASE	WHEN DATEPART(d,@DateofJoining)>15 then
							case	when	(case	when DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining))<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
													else DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining)),getdate())+1
											 end *	LPD.MaxDays_Month-ISNULL(sum(la.NumberOfDays),0.0))<0 then 0
									when	(case	when DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining))<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
													else DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining)),getdate())+1
											 end *	LPD.MaxDays_Month-ISNULL(sum(la.NumberOfDays),0.0))>LPD.No_Of_Days then LPD.No_Of_Days
									else	case	when DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining))<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
													else DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining)),getdate())+1
											end *LPD.MaxDays_Month
							end			
						else
							case when LPD.No_Of_Days<=(
							case	when	(case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
													else DATEDIFF(month,@DateofJoining,getdate())+1
											 end *	LPD.MaxDays_Month-ISNULL(sum(la.NumberOfDays),0.0))<0 then 0
									when	(case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
													else DATEDIFF(month,@DateofJoining,getdate())+1
											 end *	LPD.MaxDays_Month-ISNULL(sum(la.NumberOfDays),0.0))>LPD.No_Of_Days then LPD.No_Of_Days
									else	case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
													else DATEDIFF(month,@DateofJoining,getdate())+1
											end *LPD.MaxDays_Month
							end) then LPD.No_Of_Days 
							else    case	when	(case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
													else DATEDIFF(month,@DateofJoining,getdate())+1
											 end *	LPD.MaxDays_Month-ISNULL(sum(la.NumberOfDays),0.0))<0 then 0
											when	(case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
															else DATEDIFF(month,@DateofJoining,getdate())+1
													 end *	LPD.MaxDays_Month-ISNULL(sum(la.NumberOfDays),0.0))>LPD.No_Of_Days then LPD.No_Of_Days
											else	case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
													else DATEDIFF(month,@DateofJoining,getdate())+1
											end *LPD.MaxDays_Month
									end
							end
				end - (ISNULL(sum(la.NumberOfDays),0.0) + ISNULL(yc.Encashment,0.0))-ISNULL(ltd.NoOffLeaveAvailed,0)+ISNULL(yc.carry_PrevYear,0.0)+ISNULL(ltd.PreviousYearBalance,0.0)
			end 
			as 'Number of Days'
			from TA_tbl_Yearclosure yc
			inner join TA_Tbl_YearSetting ys on yc.Year_SettingID=ys.YearSettings_Id 
			inner join TA_Tbl_LeaveType lt on lt.LeaveType_Id=yc.LeaveTypeID
			inner join TA_Tbl_Emp_LeavePolicy LP on (LP.EmployeeId=yc.Employee_Id and LP.YearSettingId=yc.Year_SettingID)
			inner join TA_Tbl_Emp_LeavePolicyDetails LPD on (LP.LeavePolicy_Id=LPD.Policy_ID and LP.Emp_LeavePolicyId=LPD.Emp_LeavePolicyId and LPD.LeaveType_Id=lt.LeaveType_Id)
			left outer join Ta_Tbl_LeaveApplication la on la.LeaveType_Id =lt.LeaveType_Id and la.YearSettings_Id=ys.YearSettings_Id
			left outer join ta_tbl_emp_LeaveTypedetails LTD on (LP.EmployeeId=LTD.EmployeeId and LTD.LeaveTypeId=LPD.LeaveType_Id and yc.Year_SettingID=LTD.YearSettingsId)
			where yc.employee_Id=@Employee_Id and la.employee_Id=@Employee_Id and ys.YearSettings_Id=@YearSettingId and la.Isdeleted=0 
			and la.Status in ('Pending','Forwarded','Approved') and la.Type not in ('Restricted Holiday','Comp Off','ESI','LOP') 
			and la.IsBorrow=0 
			Group by Yc.LeaveTypeId,yc.Carry_PrevYear,yc.Current_Quota,yc.Borrow_NextYear,yc.Encashment,lt.LeaveType_Id,lt.LeaveType,LPD.MaxDays_Month,ys.FromDate,LPD.No_Of_Days,NoOffLeaveAvailed,ltd.PreviousYearBalance
			union
			Select lt.LeaveType as 'Leave Type',(ISNULL(yc.carry_PrevYear,0.0)+ ISNULL(yc.Current_Quota,0.0)) - ISNULL(yc.Encashment,0.0) as 'Number of Days / year',
			case when (CASE	WHEN DATEPART(d,@DateofJoining)>15 then
						case when	(case	when DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining))<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
											else DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining)),getdate())+1
									 end *LPD.MaxDays_Month- ISNULL(yc.Encashment,0.0))<0 then 0 
							 when	(case	when DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining))<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
											else DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining)),getdate())+1
									 end *LPD.MaxDays_Month- ISNULL(yc.Encashment,0.0))>LPD.No_Of_Days then LPD.No_Of_Days
							else case	when DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining))<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
										else DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining)),getdate())+1
								 end *LPD.MaxDays_Month- ISNULL(yc.Encashment,0.0)
						end
					else
						case when	(case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
											else DATEDIFF(month,@DateofJoining,getdate())+1
									 end *LPD.MaxDays_Month- ISNULL(yc.Encashment,0.0))<0 then 0 
							 when	(case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
											else DATEDIFF(month,@DateofJoining,getdate())+1
									 end *LPD.MaxDays_Month- ISNULL(yc.Encashment,0.0))>LPD.No_Of_Days then LPD.No_Of_Days
							else case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
										else DATEDIFF(month,@DateofJoining,getdate())+1
								 end *LPD.MaxDays_Month- ISNULL(yc.Encashment,0.0)
						end					
			end-ISNULL(ltd.NoOffLeaveAvailed,0)+ISNULL(yc.carry_PrevYear,0.0))<=0 then 0
			else 			
				CASE	WHEN DATEPART(d,@DateofJoining)>15 then
							case when	(case	when DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining))<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
												else DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining)),getdate())+1
										 end *LPD.MaxDays_Month- ISNULL(yc.Encashment,0.0))<0 then 0 
								 when	(case	when DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining))<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
												else DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining)),getdate())+1
										 end *LPD.MaxDays_Month- ISNULL(yc.Encashment,0.0))>LPD.No_Of_Days then LPD.No_Of_Days
								else case	when DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining))<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
											else DATEDIFF(month,DATEADD(d,-DATEPART(d,@DateofJoining)+1,dateadd(MONTH,1,@DateofJoining)),getdate())+1
									 end *LPD.MaxDays_Month- ISNULL(yc.Encashment,0.0)
							end
						else
							case when	(case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
												else DATEDIFF(month,@DateofJoining,getdate())+1
										 end *LPD.MaxDays_Month- ISNULL(yc.Encashment,0.0))<0 then 0 
								 when	(case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
												else DATEDIFF(month,@DateofJoining,getdate())+1
										 end *LPD.MaxDays_Month- ISNULL(yc.Encashment,0.0))>LPD.No_Of_Days then LPD.No_Of_Days
								else case	when @DateofJoining<=ys.FromDate then DATEDIFF(month,ys.fromdate,getdate())+1
											else DATEDIFF(month,@DateofJoining,getdate())+1
									 end *LPD.MaxDays_Month- ISNULL(yc.Encashment,0.0)
							end					
				end-ISNULL(ltd.NoOffLeaveAvailed,0)+ISNULL(yc.carry_PrevYear,0.0)+ISNULL(ltd.PreviousYearBalance,0.0)
			end
			as 'Number of Days'
			from TA_tbl_Yearclosure yc
			inner join TA_Tbl_YearSetting ys on yc.Year_SettingID=ys.YearSettings_Id
			inner join TA_Tbl_LeaveType lt on lt.LeaveType_Id=yc.LeaveTypeID
			inner join TA_Tbl_Emp_LeavePolicy LP on (LP.EmployeeId=yc.Employee_Id and LP.YearSettingId=yc.Year_SettingID)
			inner join TA_Tbl_Emp_LeavePolicyDetails LPD on (LP.LeavePolicy_Id=LPD.Policy_ID and LP.Emp_LeavePolicyId=LPD.Emp_LeavePolicyId and LPD.LeaveType_Id=lt.LeaveType_Id)
			left outer join ta_tbl_emp_LeaveTypedetails LTD on (LP.EmployeeId=LTD.EmployeeId and LTD.LeaveTypeId=LPD.LeaveType_Id and yc.Year_SettingID=LTD.YearSettingsId)
			where yc.employee_Id=@Employee_Id and yc.LeaveTypeId not in (
			select LeaveType_Id from Ta_Tbl_LeaveApplication la
			inner join TA_Tbl_YearSetting ys on la.YearSettings_ID=ys.YearSettings_Id
			where Employee_Id=@Employee_Id and Isdeleted=0 and IsBorrow=0 
			and Type not in ('Restricted Holiday','Comp Off','ESI','LOP') and Status not in ('Rejected') and ys.IsActive=1)
			and ys.YearSettings_Id=@YearSettingId
		end
		else if (@YearSettingId=@SuccessiveYearSettingId)
		begin	
			Select lt.LeaveType as 'Leave Type',(ISNULL(LPD.MaxDays_Applicable,0.0))
			- (ISNULL(sum(la.NumberOfDays),0.0)) as 'Number of Days / year',LPD.MaxDays_Month,
			case when (DATEDIFF(month,@CurrentYearStartDate,getdate())+1)*LPD.MaxDays_Month- (ISNULL(sum(la.NumberOfDays),0.0))<0 then 0
			else (DATEDIFF(month,@CurrentYearStartDate,getdate())+1)*LPD.MaxDays_Month- (ISNULL(sum(la.NumberOfDays),0.0)) 
			end as 'Number of Days'
			from ta_tbl_leavepolicydetails LPD
			inner join TA_Tbl_Emp_LeavePolicy elp on elp.LeavePolicy_Id=lpd.Policy_Id 
			inner join TA_Tbl_YearSetting ys on ys.YearSettings_Id=@CurrentyearSettingId
			inner join TA_Tbl_LeaveType lt on lt.LeaveType_Id=LPD.LeaveType_Id
			left outer join Ta_Tbl_LeaveApplication la on la.LeaveType_Id =lt.LeaveType_Id and la.YearSettings_Id=ys.YearSettings_Id
			inner join TA_Tbl_Emp_LeavePolicy LP on (LP.EmployeeId=elp.EmployeeId and LP.YearSettingId=elp.YearSettingId)			
			where elp.EmployeeId=@Employee_Id 
			and la.employee_Id=@Employee_Id and ys.YearSettings_Id=@CurrentyearSettingId and la.Isdeleted=0 
			and la.Status in ('Pending','Forwarded','Approved') and la.Type not in ('Restricted Holiday','Comp Off','ESI','LOP') 
			and la.IsBorrow=0 
			and elp.ToDate =(select top 1 TA_Tbl_Emp_LeavePolicy.ToDate from TA_Tbl_Emp_LeavePolicy
			inner join TA_Tbl_YearSetting on TA_Tbl_YearSetting.YearSettings_Id=TA_Tbl_Emp_LeavePolicy.YearSettingid 
			where TA_Tbl_Emp_LeavePolicy.EmployeeId=@Employee_Id and TA_Tbl_YearSetting.IsActive=1 order by TA_Tbl_Emp_LeavePolicy.ToDate desc)
			Group by lt.LeaveType_Id,LPD.MaxDays_Applicable,lt.LeaveType,LPD.MaxDays_Month,ys.FromDate
			union
			Select lt.LeaveType as 'Leave Type',(ISNULL(LPD.MaxDays_Applicable,0.0)) as 'Number of Days / year',LPD.MaxDays_Month,
			case when (DATEDIFF(month,@CurrentYearStartDate,getdate())+1)*LPD.MaxDays_Month>ELPD.No_Of_Days then ELPD.No_Of_Days else  
			(DATEDIFF(month,@CurrentYearStartDate,getdate())+1)*LPD.MaxDays_Month
			end
			as 'Number of Days'
			from ta_tbl_leavepolicydetails LPD 
			inner join TA_Tbl_Emp_LeavePolicy elp on elp.LeavePolicy_Id=lpd.Policy_Id 
			inner join TA_Tbl_YearSetting ys on ys.YearSettings_Id=@CurrentyearSettingId
			inner join TA_Tbl_LeaveType lt on lt.LeaveType_Id=LPD.LeaveType_Id
			inner join TA_Tbl_Emp_LeavePolicy LP on (LP.EmployeeId=elp.EmployeeId and LP.YearSettingId=elp.YearSettingId)
			inner join TA_Tbl_Emp_LeavePolicyDetails ELPD on 
			(LP.Emp_LeavePolicyId=ELPD.Emp_LeavePolicyId and ELPD.LeaveType_Id=lt.LeaveType_Id and ELPD.EmpID=LP.EmployeeId)
			where elp.EmployeeId=@Employee_Id and LPD.Leavetype_Id not in(
			select LeaveType_Id from Ta_Tbl_LeaveApplication la
			where la.Employee_Id=@Employee_Id and Isdeleted=0 and IsBorrow=0 
			and Type not in ('Restricted Holiday','Comp Off','ESI','LOP') and Status not in ('Rejected')and la.YearSettings_Id=@CurrentyearSettingId)
			and ys.YearSettings_Id=@CurrentyearSettingId
			and elp.ToDate =(select top 1 TA_Tbl_Emp_LeavePolicy.ToDate from TA_Tbl_Emp_LeavePolicy
			inner join TA_Tbl_YearSetting on TA_Tbl_YearSetting.YearSettings_Id=TA_Tbl_Emp_LeavePolicy.YearSettingid 
			where TA_Tbl_Emp_LeavePolicy.EmployeeId=@Employee_Id and TA_Tbl_YearSetting.IsActive=1 order by TA_Tbl_Emp_LeavePolicy.ToDate desc)
		end
	end
end
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


ALTER   PROCEDURE [dbo].[Proc_TA_GetLeaveApplicationByLeaveApplicationId]
@LeaveApplication_Id int =null
as
declare @strLeaveType as nvarchar(50)
BEGIN
	SET NOCOUNT ON;
	DECLARE @dateFormat as INT
	set @dateFormat =(Select DateFormat from dbo.Func_TA_GetDateTimeFormat())
	set @strLeaveType=(select Type from TA_Tbl_LeaveApplication where LeaveApplication_Id=@LeaveApplication_Id and isdeleted=0)	
	DECLARE @FileNotFound as varchar(200) = 'File Not Found';
	DECLARE @FileFound as varchar(200) = 'Download';
	if(@strLeaveType='Leave' or @strLeaveType='LOP')
		begin
			select E.Employee_Code_Id as employeeid,
			la.LeaveApplication_Id as LeaveApplication_Id,
			la.Employee_Id as Employee_Id,
			e.Employee_Code_Id as Employee_Code_Id,
			e.emailId as emailId,
			(e.Employee_FirstName + ' ' + isnull(e.Employee_LastName,'')) as Employee_FirstName,
			case	when cast(@dateFormat as varchar(100))=106 then REPLACE(CONVERT(VARCHAR, la.Leave_ApplicationDate,@dateFormat),' ','-') 
				else CONVERT(VARCHAR, la.Leave_ApplicationDate, @dateFormat) end 
			as Leave_ApplicationDate,
			la.LeaveType_id as LeaveType_id,
			la.Type as Type,
			(lt.LeaveType + ' / '  + la.Type) as LeaveType,
			case	when cast(@dateFormat as varchar(100))=106 then REPLACE(CONVERT(VARCHAR, la.Leave_FromDate,@dateFormat),' ','-') 
				else CONVERT(VARCHAR, la.Leave_FromDate, @dateFormat) end as Leave_FromDate,
				case	when cast(@dateFormat as varchar(100))=106 then REPLACE(CONVERT(VARCHAR, la.Leave_ToDate,@dateFormat),' ','-') 
				else CONVERT(VARCHAR, la.Leave_ToDate, @dateFormat) end 
			as Leave_ToDate,
			la.Leave_SessionFrom as Leave_SessionFrom,
			la.Leave_SessionTo as Leave_SessionTo,
			la.NumberOfDays as NumberOfDays,
			la.Reason as Reason,
			la.Status as Status,
			la.IsDeleted as IsDeleted,
			la.Remarks as Remarks,
			la.IsBorrow as IsBorrow,
			ld.name as LevelName,
			la.YearSettings_Id,
			Mobile_Number,
			Reporting_To,
			Leave_Sanc_Authority
			,LA_FU.FileName as Download,
			case when LA_FU.FileName is null then cast(@FileNotFound as varchar(100)) else cast(@FileFound as varchar(100)) end as Result
			from  TA_Tbl_LeaveApplication la  
			inner join  TA_Tbl_LeaveType lt   
			on la.LeaveType_Id = lt.LeaveType_Id
			inner join TA_Tbl_Employee e on e.Employee_Id=la.Employee_Id
			inner join TA_Tbl_EmployeeLevel el on e.Employee_Id = el.EmployeeId
			inner join Ta_Tbl_levelDetails ld on el.LevelId = ld.id 
			left outer join TA_Tbl_LeaveApplication_FileUpload LA_FU on LA.LeaveApplication_Id = LA_FU.LeaveApplication_Id 
			where la.LeaveApplication_Id = @LeaveApplication_Id and la.IsDeleted=0 and lt.Isdeleted=0
			And el.EffectiveDate = (select top 1 EffectiveDate from TA_Tbl_EmployeeLevel where EmployeeId = e.Employee_Id AND EffectiveDate <=CONVERT(DATETIME,getdate(), 102) order by EffectiveDate desc)
		end
	else if(@strLeaveType='Comp Off' or @strLeaveType='ESI')
		begin
			select E.Employee_Code_Id as employeeid,la.LeaveApplication_Id as LeaveApplication_Id,
			la.Employee_Id as Employee_Id,
			e.Employee_Code_Id as Employee_Code_Id,
			e.emailId as emailId,
			(e.Employee_FirstName + ' ' + isnull(e.Employee_LastName,'')) as Employee_FirstName,
			case	when cast(@dateFormat as varchar(100))=106 then REPLACE(CONVERT(VARCHAR, la.Leave_ApplicationDate,@dateFormat),' ','-') 
				else CONVERT(VARCHAR, la.Leave_ApplicationDate, @dateFormat) end 
			as Leave_ApplicationDate,
			la.LeaveType_id as LeaveType_id,
			la.Type as Type,
			('Others ' + '/' +la.Type) as LeaveType,
			case	when cast(@dateFormat as varchar(100))=106 then REPLACE(CONVERT(VARCHAR, la.Leave_FromDate,@dateFormat),' ','-') 
				else CONVERT(VARCHAR, la.Leave_FromDate, @dateFormat) end as Leave_FromDate,
				case	when cast(@dateFormat as varchar(100))=106 then REPLACE(CONVERT(VARCHAR, la.Leave_ToDate,@dateFormat),' ','-') 
				else CONVERT(VARCHAR, la.Leave_ToDate, @dateFormat) end 
			as Leave_ToDate,
			la.Leave_SessionFrom as Leave_SessionFrom,
			la.Leave_SessionTo as Leave_SessionTo,
			la.NumberOfDays as NumberOfDays,
			la.Reason as Reason,
			la.Status as Status,
			la.IsDeleted as IsDeleted,
			la.Remarks as Remarks,
			la.IsBorrow as IsBorrow,
			ld.name as LevelName,
			la.YearSettings_Id,
			Mobile_Number,
			Reporting_To,
			Leave_Sanc_Authority
			,LA_FU.FileName as Download,
			case when LA_FU.FileName is null then cast(@FileNotFound as varchar(100)) else cast(@FileFound as varchar(100)) end as Result
			from  TA_Tbl_LeaveApplication la  
			inner join TA_Tbl_Employee e on e.Employee_Id=la.Employee_Id
			inner join TA_Tbl_EmployeeLevel el on e.Employee_Id = el.EmployeeId
			inner join Ta_Tbl_levelDetails ld on el.LevelId = ld.id
			left outer join TA_Tbl_LeaveApplication_FileUpload LA_FU on LA.LeaveApplication_Id = LA_FU.LeaveApplication_Id 
			where la.LeaveApplication_Id = @LeaveApplication_Id and la.IsDeleted=0
			And el.EffectiveDate = (select top 1 EffectiveDate from TA_Tbl_EmployeeLevel where EmployeeId = e.Employee_Id AND EffectiveDate <=CONVERT(DATETIME,getdate(), 102) order by EffectiveDate desc)
		end
	if(@strLeaveType='Restricted Holiday')
		begin
			select E.Employee_Code_Id as employeeid,la.LeaveApplication_Id as LeaveApplication_Id,
			la.Employee_Id as Employee_Id,
			e.Employee_Code_Id as Employee_Code_Id,
			e.emailId as emailId,
			(e.Employee_FirstName + ' ' + isnull(e.Employee_LastName,'')) as Employee_FirstName,
			case	when cast(@dateFormat as varchar(100))=106 then REPLACE(CONVERT(VARCHAR, la.Leave_ApplicationDate,@dateFormat),' ','-') 
				else CONVERT(VARCHAR, la.Leave_ApplicationDate, @dateFormat) end
			as Leave_ApplicationDate,
			la.LeaveType_id as LeaveType_id,
			la.Type as Type,
			(la.Type + ' / ' +h.Holiday_Description) as LeaveType,
			case	when cast(@dateFormat as varchar(100))=106 then REPLACE(CONVERT(VARCHAR, la.Leave_FromDate,@dateFormat),' ','-') 
				else CONVERT(VARCHAR, la.Leave_FromDate, @dateFormat) end as Leave_FromDate,
				case	when cast(@dateFormat as varchar(100))=106 then REPLACE(CONVERT(VARCHAR, la.Leave_ToDate,@dateFormat),' ','-') 
				else CONVERT(VARCHAR, la.Leave_ToDate, @dateFormat) end 
			as Leave_ToDate,
			la.Leave_SessionFrom as Leave_SessionFrom,
			la.Leave_SessionTo as Leave_SessionTo,
			la.NumberOfDays as NumberOfDays,
			la.Reason as Reason,
			la.Status as Status,
			la.IsDeleted as IsDeleted,
			la.Remarks as Remarks,
			la.IsBorrow as IsBorrow,
			ld.name as LevelName,
			la.YearSettings_Id,
			Mobile_Number,
			Reporting_To,
			Leave_Sanc_Authority
			,LA_FU.FileName as Download,
			case when LA_FU.FileName is null then cast(@FileNotFound as varchar(100)) else cast(@FileFound as varchar(100)) end as Result
			from  TA_Tbl_LeaveApplication la  
			inner join  TA_Tbl_Holiday h  
			on la.LeaveType_Id = h.Holiday_Id  
			inner join TA_Tbl_Employee e 
			on e.Employee_Id=la.Employee_Id
			inner join TA_Tbl_EmployeeLevel el on e.Employee_Id = el.EmployeeId
			inner join Ta_Tbl_levelDetails ld on el.LevelId = ld.id
			left outer join TA_Tbl_LeaveApplication_FileUpload LA_FU on LA.LeaveApplication_Id = LA_FU.LeaveApplication_Id 
			where la.LeaveApplication_Id = @LeaveApplication_Id and la.IsDeleted=0 and h.Isdeleted=0
			And el.EffectiveDate = (select top 1 EffectiveDate from TA_Tbl_EmployeeLevel where EmployeeId = e.Employee_Id AND EffectiveDate <=CONVERT(DATETIME,getdate(), 102) order by EffectiveDate desc)
		end
END

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ALTER    PROCEDURE [dbo].[Proc_TA_CheckLeaveApplication]
(
	@Employee_Id int,
	@Leave_FromDate datetime,
	@Leave_ToDate datetime,
	@LeaveType int
)
AS
BEGIN
	SET NOCOUNT ON;
	if exists(Select * from TA_Tbl_LeaveType where LeaveType_Id=@LeaveType and isnull(isWFH,0)=1)
		begin
			select COUNT(*)  as CS from TA_Tbl_LeaveApplication LA 
			Left outer join TA_Tbl_LeaveType LT on LT.LeaveType_Id=LA.LeaveType_id
			inner join TA_Tbl_LeaveApplicationExists LAE on LA.Employee_Id=LAE.Employee_Id and la.LeaveApplication_Id=lae.LeaveApplicationId where LA.Employee_Id =cast(@Employee_Id as nvarchar(5)) And LAE.IsDeleted=0 and LAE.Status<>'Rejected' AND ((LAE.Leave_FromDate between(CONVERT(DATETIME, cast(@Leave_FromDate as nvarchar(50))   , 102)) and (CONVERT(DATETIME, cast(@Leave_ToDate as nvarchar(50)), 102))) or (LAE.Leave_ToDate between (CONVERT(DATETIME, cast(@Leave_FromDate as nvarchar(50))  , 102)) and (CONVERT(DATETIME, cast(@Leave_ToDate as nvarchar(50)), 102)))) and isnull(LT.isWFH,0)=1
		end
	else
		begin
			select COUNT(*)  as CS from TA_Tbl_LeaveApplication LA 
			Left outer join TA_Tbl_LeaveType LT on LT.LeaveType_Id=LA.LeaveType_id
			inner join TA_Tbl_LeaveApplicationExists LAE on LA.Employee_Id=LAE.Employee_Id and la.LeaveApplication_Id=lae.LeaveApplicationId where LA.Employee_Id =cast(@Employee_Id as nvarchar(5)) And LAE.IsDeleted=0 and LAE.Status<>'Rejected' AND ((LAE.Leave_FromDate between(CONVERT(DATETIME, cast(@Leave_FromDate as nvarchar(50))   , 102)) and (CONVERT(DATETIME, cast(@Leave_ToDate as nvarchar(50)), 102))) or (LAE.Leave_ToDate between (CONVERT(DATETIME, cast(@Leave_FromDate as nvarchar(50))  , 102)) and (CONVERT(DATETIME, cast(@Leave_ToDate as nvarchar(50)), 102)))) and isnull(LT.isWFH,0)=0
	end
END
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ALTER  PROCEDURE [dbo].[Proc_TA_SelectLeaveApplicationById]
(
	@LeaveApplication_Id int
)
AS
	declare @strLeaveType as nvarchar(50)
BEGIN
	SET NOCOUNT ON;
	set @strLeaveType=(select Type from TA_Tbl_LeaveApplication where LeaveApplication_Id=@LeaveApplication_Id and isdeleted=0)
	if(@strLeaveType='Leave' or @strLeaveType='LOP')
		begin
			select la.LeaveApplication_Id as LeaveApplication_Id,
			la.Employee_Id as Employee_Id,
			la.Leave_ApplicationDate as Leave_ApplicationDate,
			la.LeaveType_id as LeaveType_id,
			la.Type as Type,
			lt.LeaveType as LeaveType,
			la.Leave_FromDate as Leave_FromDate,
			la.Leave_ToDate as Leave_ToDate,
			la.Leave_SessionFrom as Leave_SessionFrom,
			la.Leave_SessionTo as Leave_SessionTo,
			la.NumberOfDays as NumberOfDays,
			la.Reason as Reason,
			la.Status as Status,
			la.IsDeleted as IsDeleted,
			la.Remarks as Remarks,
			la.IsBorrow as IsBorrow,
			la.YearSettings_Id,
			isnull(lt.isWFH,0) as isWFH
			from  TA_Tbl_LeaveApplication la  
			inner join  TA_Tbl_LeaveType lt   
			on la.LeaveType_Id = lt.LeaveType_Id   
			where la.LeaveApplication_Id = @LeaveApplication_Id and la.IsDeleted=0 and lt.Isdeleted=0
		end
	else if(@strLeaveType='Comp Off' or @strLeaveType='ESI')
		begin
			select la.LeaveApplication_Id as LeaveApplication_Id,
			la.Employee_Id as Employee_Id,
			la.Leave_ApplicationDate as Leave_ApplicationDate,
			la.LeaveType_id as LeaveType_id,
			la.Type as Type,
			'Others' as LeaveType,
			la.Leave_FromDate as Leave_FromDate,
			la.Leave_ToDate as Leave_ToDate,
			la.Leave_SessionFrom as Leave_SessionFrom,
			la.Leave_SessionTo as Leave_SessionTo,
			la.NumberOfDays as NumberOfDays,
			la.Reason as Reason,
			la.Status as Status,
			la.IsDeleted as IsDeleted,
			la.Remarks as Remarks,
			la.IsBorrow as IsBorrow,
			la.YearSettings_Id ,
			0 as isWFH
			from  TA_Tbl_LeaveApplication la  
			where la.LeaveApplication_Id = @LeaveApplication_Id and la.IsDeleted=0
		end
	if(@strLeaveType='Restricted Holiday')
		begin
			select la.LeaveApplication_Id as LeaveApplication_Id,
			la.Employee_Id as Employee_Id,
			la.Leave_ApplicationDate as Leave_ApplicationDate,
			la.LeaveType_id as LeaveType_id,
			la.Type as Type,
			h.Holiday_Description as LeaveType,
			la.Leave_FromDate as Leave_FromDate,
			la.Leave_ToDate as Leave_ToDate,
			la.Leave_SessionFrom as Leave_SessionFrom,
			la.Leave_SessionTo as Leave_SessionTo,
			la.NumberOfDays as NumberOfDays,
			la.Reason as Reason,
			la.Status as Status,
			la.IsDeleted as IsDeleted,
			la.Remarks as Remarks,
			la.IsBorrow as IsBorrow,
			la.YearSettings_Id,
			0 as isWFH
			from  TA_Tbl_LeaveApplication la  
			inner join  TA_Tbl_Holiday h  
			on la.LeaveType_Id = h.Holiday_Id   
			where la.LeaveApplication_Id = @LeaveApplication_Id and la.IsDeleted=0 and h.Isdeleted=0
		end
END

----------------------------------------------------------------------------------------------------------------------------------------

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
	DECLARE @FileNotFound as varchar(200) = 'File Not Found';
	DECLARE @FileFound as varchar(200) = 'Download';
	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)
	if(@isReportingto=1 and @UserGroupName!='Superadmin')
	begin
		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
		insert into  #EmployeeIds select * from [dbo].fn_TA_GetMappedEmployees_CompanyHead(@EmployeeID)		as EmpAutoId
		insert into  #EmployeeIds select * from [dbo].fn_TA_GetMappedEmployees_DepartHead(@EmployeeID)		as EmpAutoId
		insert into  #EmployeeIds values(@EmployeeID)
    end
    --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.Reason as Reason,la.Remarks as Remarks,NumberOfDays,isnull(lt.isWFH,0) as isWFH,
	LA_FU.FileName as Download,case when LA_FU.FileName is null then '''+ cast(@FileNotFound as varchar(100)) +''' else '''+ cast(@FileFound as varchar(100)) +''' end as Result '   
	set @sqlQuery = @sqlQuery + ' from TA_Tbl_LeaveApplication la ' 
	set @sqlQuery = @sqlQuery + ' left outer join TA_Tbl_LeaveApplication_FileUpload LA_FU on la.LeaveApplication_Id = LA_FU.LeaveApplication_Id ' 
	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
	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.Reason as Reason,la.Remarks as Remarks,NumberOfDays,0 as isWFH,
									LA_FU.FileName as Download,case when LA_FU.FileName is null then '''+ cast(@FileNotFound as varchar(100)) +''' else '''+ cast(@FileFound as varchar(100)) +''' end as Result
									from TA_Tbl_Holiday h ' 
	set @sqlQuery1 = @sqlQuery1 + ' inner join  TA_Tbl_LeaveApplication la on la.LeaveType_Id=h.Holiday_Id '
	set @sqlQuery1 = @sqlQuery1 + ' left outer join TA_Tbl_LeaveApplication_FileUpload LA_FU on la.LeaveApplication_Id = LA_FU.LeaveApplication_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 @sqlQuery1 = @sqlQuery1 + ' 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.Reason as Reason,la.Remarks as Remarks,NumberOfDays,0 as isWFH ,LA_FU.FileName as Download,case when LA_FU.FileName is null then '''+ cast(@FileNotFound as varchar(100)) +''' else '''+ cast(@FileFound as varchar(100)) +''' end as Result '  
	set @sqlQuery2 = @sqlQuery2 + ' from TA_Tbl_LeaveApplication la '
	set @sqlQuery2 = @sqlQuery2 + ' left outer join TA_Tbl_LeaveApplication_FileUpload LA_FU on la.LeaveApplication_Id = LA_FU.LeaveApplication_Id ' 
	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 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 @sqlQuery2 = @sqlQuery2 + ' 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 '
	exec (@sqlQuery+@sqlQuery1+@sqlQuery2+@sqlQuery3)
	drop table #LevelRights
	drop table #SwipeCardNo
END
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ALTER PROCEDURE [dbo].[Proc_TA_SelectLeaveApplicationDetailsByEmpId]
(
@Employee_Id int,
@GradeLevel varchar(20),
@LevelRights varchar(8000), 
@LeaveApplicationStartDate datetime = '01/01/1900', 
@LeaveApplicationEndDate datetime = '01/01/1900',
@UserGroupName varchar(250),
@ParentId int
)    
AS    
BEGIN
	SET NOCOUNT ON;
	create table #LevelRights(LevelId int)
	CREATE NONCLUSTERED INDEX [IDX_LevelId] ON [dbo].[#LevelRights]([LevelId] ASC) ON [PRIMARY]
	insert into  #LevelRights select * from [dbo].[fn_TA_GetParentChild](@UserGroupName,@ParentId) as LevelId
	DECLARE @CurrentDate AS NVARCHAR(30)
	Declare @LeaveSuccessiveFromDate datetime
	Declare @LeaveSuccessiveToDate datetime
	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= dateadd(day,(select max(NoofSuccessiveDaysinyear) from ta_tbl_shiftsettings) ,ToDate) 
					from ta_tbl_yearsetting where Isactive=1 
				end
			else
				begin
					if exists(select NoofSuccessiveDaysinyear from ta_tbl_shiftsettings where companyId=@ParentId)
					begin
						select @LeaveSuccessiveToDate= dateadd(day,(select NoofSuccessiveDaysinyear from ta_tbl_shiftsettings 
						where companyId=@ParentId),ToDate) from ta_tbl_yearsetting where Isactive=1
					end
					else
					begin
						select @LeaveSuccessiveToDate= dateadd(day,(select NoofSuccessiveDaysinyear from ta_tbl_shiftsettings 
						where companyId=-1),ToDate) from ta_tbl_yearsetting where Isactive=1
					end
				end
		end
	declare @sqlQuery as varchar(8000)      
	declare @sqlQuery1 as varchar(8000)
	declare @sqlQuery2 as varchar(8000)
	declare @sqlQuery4 as varchar(8000)        
	set @sqlQuery = ''      
	set @sqlQuery1 = ''
	set @sqlQuery2 = ''
	set @sqlQuery4 = ''
	DECLARE @dateFormat as int
	SET @dateFormat=(Select DateFormat from Func_TA_GetDateTimeFormat())
	SET @CurrentDate = GetDate()
	DECLARE @FileNotFound as varchar(200) = 'File Not Found';
	DECLARE @FileFound as varchar(200) = 'Download';
	set @sqlQuery = @sqlQuery + ' select '''' as employeeid,'' '' as Employee_firstName,'''' as ''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.Reason as Reason,la.Remarks as Remarks,
	la.NumberOfDays NumberOfDays,
	case	when status=''Pending''		then ''Pen_16x16.png''
			when status=''Forwarded''	then ''Forw_52x52.png''
			when status=''Approved''	then ''Appr_52x52.png'' 
			when status=''Rejected''	then ''Rej_52x52.png'' 
	end as image,
	case	when status=''Pending''		then ''#DD4B39''
			when status=''Forwarded''	then ''#FF7F00''
			when status=''Approved''	then ''#00CC71'' 
			when status=''Rejected''	then ''#00CC74'' 
	end as btnColor,isnull(lt.isWFH,0) as isWFH,
	LA_FU.FileName as Download,case when LA_FU.FileName is null then '''+ cast(@FileNotFound as varchar(100)) +''' else '''+ cast(@FileFound as varchar(100)) +''' end as Result
	from TA_Tbl_LeaveApplication la '
	set @sqlQuery = @sqlQuery + ' left outer join TA_Tbl_LeaveApplication_FileUpload LA_FU on la.LeaveApplication_Id = LA_FU.LeaveApplication_Id ' 
	set @sqlQuery = @sqlQuery + ' inner join TA_Tbl_LeaveType lt on la.LeaveType_Id = lt.LeaveType_Id '
	set @sqlQuery = @sqlQuery + ' INNER JOIN TA_Tbl_EmployeeDesignation ON la.Employee_Id = TA_Tbl_EmployeeDesignation.EmployeeId '
	set @sqlQuery = @sqlQuery + ' INNER JOIN TA_Tbl_EmployeeLevel ON la.Employee_Id = TA_Tbl_EmployeeLevel.EmployeeId '
	set @sqlQuery = @sqlQuery + ' INNER JOIN TA_Tbl_LevelDetails ON TA_Tbl_LevelDetails.id = TA_Tbl_EmployeeLevel.LevelId '
	set @sqlQuery = @sqlQuery + ' INNER JOIN TA_Tbl_Title ON TA_Tbl_Title.TitleId=TA_Tbl_EmployeeDesignation.DesignationId '
	set @sqlQuery = @sqlQuery + ' INNER JOIN TA_Tbl_Grade ON TA_Tbl_Grade.Grade_ID = TA_Tbl_Title.GradeId '
	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=TA_Tbl_LevelDetails.id '
	set @sqlQuery = @sqlQuery + ' where la.Employee_Id = ''' + cast(@Employee_Id as nvarchar(100))  + ''' and la.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))'    
	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
	set @sqlQuery = @sqlQuery + ' and TA_Tbl_EmployeeDesignation.EffectiveDate = (select top 1 EffectiveDate from TA_Tbl_EmployeeDesignation where EmployeeId = la.Employee_Id and EffectiveDate <=CONVERT(DATETIME,''' + cast(@CurrentDate as nvarchar(50)) + ''', 102) order by EffectiveDate desc) '
	set @sqlQuery = @sqlQuery + ' and TA_Tbl_EmployeeLevel.EffectiveDate = (select top 1 EffectiveDate from TA_Tbl_EmployeeLevel where EmployeeId = la.Employee_Id and EffectiveDate <=CONVERT(DATETIME,''' + cast(@CurrentDate as nvarchar(50)) + ''', 102) order by EffectiveDate desc) '
	set @sqlQuery = @sqlQuery + ' AND TA_Tbl_Grade.Grade_Level >=' + cast(@GradeLevel as nvarchar(100))  
	set @sqlQuery1 = @sqlQuery1 + ' union '    
	set @sqlQuery1 = @sqlQuery1 + ' select '''' as employeeid,'''' as Employee_firstName,'''' as ''name'',la.LeaveApplication_Id,la.Employee_Id,CONVERT(VARCHAR, la.Leave_ApplicationDate, '+cast(@dateFormat as varchar(100))+') 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.Reason as Reason,la.Remarks as Remarks,
	la.NumberOfDays NumberOfDays,
	case	when status=''Pending''		then ''Pen_16x16.png''
			when status=''Forwarded''	then ''Forw_52x52.png''
			when status=''Approved''	then ''Appr_52x52.png'' 
			when status=''Rejected''	then ''Rej_52x52.png'' 
	end as image
	,
	case	when status=''Pending''		then ''#DD4B39''
			when status=''Forwarded''	then ''#FF7F00''
			when status=''Approved''	then ''#00CC71'' 
			when status=''Rejected''	then ''#00CC74'' 
	end as btnColor,0 as isWFH ,
	LA_FU.FileName as Download,case when LA_FU.FileName is null then '''+ cast(@FileNotFound as varchar(100)) +''' else '''+ cast(@FileFound as varchar(100)) +''' end as Result
	from TA_Tbl_Holiday h  '  
	set @sqlQuery1 = @sqlQuery1 + ' inner join  TA_Tbl_LeaveApplication la on la.LeaveType_Id=h.Holiday_Id '
	set @sqlQuery1 = @sqlQuery1 + ' left outer join TA_Tbl_LeaveApplication_FileUpload LA_FU on la.LeaveApplication_Id = LA_FU.LeaveApplication_Id ' 
	set @sqlQuery1 = @sqlQuery1 + ' INNER JOIN TA_Tbl_EmployeeDesignation ON la.Employee_Id = TA_Tbl_EmployeeDesignation.EmployeeId '
	set @sqlQuery1 = @sqlQuery1 + ' INNER JOIN TA_Tbl_EmployeeLevel ON la.Employee_Id = TA_Tbl_EmployeeLevel.EmployeeId '
	set @sqlQuery1 = @sqlQuery1 + ' INNER JOIN TA_Tbl_LevelDetails ON TA_Tbl_LevelDetails.id = TA_Tbl_EmployeeLevel.LevelId '
	set @sqlQuery1 = @sqlQuery1 + ' INNER JOIN TA_Tbl_Title ON TA_Tbl_Title.TitleId=TA_Tbl_EmployeeDesignation.DesignationId '
	set @sqlQuery1 = @sqlQuery1 + ' INNER JOIN TA_Tbl_Grade ON TA_Tbl_Grade.Grade_ID = TA_Tbl_Title.GradeId '
	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=TA_Tbl_LevelDetails.id '
	set @sqlQuery1 = @sqlQuery1 + ' where la.type=''Restricted Holiday'' and la.Employee_Id=''' + cast(@Employee_Id as nvarchar(100))  + ''' and la.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)) ' 
	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 @sqlQuery1 = @sqlQuery1 + ' and TA_Tbl_EmployeeDesignation.EffectiveDate = (select top 1 EffectiveDate from TA_Tbl_EmployeeDesignation where EmployeeId = la.Employee_Id and EffectiveDate <=CONVERT(DATETIME,''' + cast(@CurrentDate as nvarchar(50)) + ''', 102) order by EffectiveDate desc) '
	set @sqlQuery1 = @sqlQuery1 + ' and TA_Tbl_EmployeeLevel.EffectiveDate = (select top 1 EffectiveDate from TA_Tbl_EmployeeLevel where EmployeeId = la.Employee_Id and EffectiveDate <= CONVERT(DATETIME,''' + cast(@CurrentDate as nvarchar(50)) + ''', 102)  order by EffectiveDate desc) ' 
	set @sqlQuery1 = @sqlQuery1 + ' AND TA_Tbl_Grade.Grade_Level >= ' + cast(@GradeLevel as nvarchar(100))  
	set @sqlQuery2 = @sqlQuery2 + ' union '
	set @sqlQuery2 = @sqlQuery2 + ' select '''' as employeeid,'''' as Employee_firstName,'''' as ''name'',la.LeaveApplication_Id,la.Employee_Id,
	CONVERT(VARCHAR, la.Leave_ApplicationDate, '+cast(@dateFormat as varchar(100))+') 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 as LeaveType,la.Status as Status,la.Reason as Reason,la.Remarks as Remarks,
	la.NumberOfDays NumberOfDays,
	case	when status=''Pending''		then ''Pen_16x16.png''
			when status=''Forwarded''	then ''Forw_52x52.png''
			when status=''Approved''	then ''Appr_52x52.png'' 
			when status=''Rejected''	then ''Rej_52x52.png'' 
	end as image
	,
		case	when status=''Pending''		then ''#f4c9c3''
			when status=''Forwarded''	then ''#ffd8b2''
			when status=''Approved''	then ''#b2efd4'' 
			when status=''Rejected''	then ''#00CC74'' 
	end as btnColor,0 as isWFH
	 ,LA_FU.FileName as Download,case when LA_FU.FileName is null then '''+ cast(@FileNotFound as varchar(100)) +''' else '''+ cast(@FileFound as varchar(100)) +''' end as Result
	from TA_Tbl_LeaveApplication la '     
	set @sqlQuery2 = @sqlQuery2 + ' left outer join TA_Tbl_LeaveApplication_FileUpload LA_FU on la.LeaveApplication_Id = LA_FU.LeaveApplication_Id ' 
	set @sqlQuery2 = @sqlQuery2 + ' INNER JOIN TA_Tbl_EmployeeDesignation ON la.Employee_Id = TA_Tbl_EmployeeDesignation.EmployeeId '
	set @sqlQuery2 = @sqlQuery2 + ' INNER JOIN TA_Tbl_EmployeeLevel ON la.Employee_Id = TA_Tbl_EmployeeLevel.EmployeeId '
	set @sqlQuery2 = @sqlQuery2 + ' INNER JOIN TA_Tbl_LevelDetails ON TA_Tbl_LevelDetails.id = TA_Tbl_EmployeeLevel.LevelId '
	set @sqlQuery2 = @sqlQuery2 + ' INNER JOIN TA_Tbl_Title ON TA_Tbl_Title.TitleId=TA_Tbl_EmployeeDesignation.DesignationId '
	set @sqlQuery2 = @sqlQuery2 + ' INNER JOIN TA_Tbl_Grade ON TA_Tbl_Grade.Grade_ID = TA_Tbl_Title.GradeId '
	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=TA_Tbl_LevelDetails.id '
	set @sqlQuery2 = @sqlQuery2 + ' where la.Employee_Id=''' + cast(@Employee_Id as nvarchar(100))  + ''' and (la.type=''Comp Off'' or la.type=''ESI'') and  la.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))'
	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 @sqlQuery2 = @sqlQuery2 + ' and TA_Tbl_EmployeeDesignation.EffectiveDate = (select top 1 EffectiveDate from TA_Tbl_EmployeeDesignation where EmployeeId = la.Employee_Id and EffectiveDate <=CONVERT(DATETIME,''' + cast(@CurrentDate as nvarchar(50)) + ''', 102) order by EffectiveDate desc) '
	set @sqlQuery2 = @sqlQuery2 + ' and TA_Tbl_EmployeeLevel.EffectiveDate = (select top 1 EffectiveDate from TA_Tbl_EmployeeLevel where EmployeeId = la.Employee_Id and EffectiveDate <=CONVERT(DATETIME,''' + cast(@CurrentDate as nvarchar(50)) + ''', 102) order by EffectiveDate desc) '
	set @sqlQuery2 = @sqlQuery2 + ' AND TA_Tbl_Grade.Grade_Level >=' + cast(@GradeLevel as nvarchar(100))  
	set @sqlQuery4= @sqlQuery4 + ' Order by LeaveApplication_Id desc '
	exec (@sqlQuery+@sqlQuery1+@sqlQuery2+@sqlQuery4)
	drop table #LevelRights
END
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


ALTER    PROCEDURE [dbo].[Proc_TA_GetEmployeeRequiredPeriod]    
@Employee_Id bigint,    
@leaveType_Id int,
@LeaveFromDate datetime,
@LeaveToDate datetime    
AS    
declare @test as decimal    
declare @Result as varchar(10)    
if((select IsActive from TA_Tbl_YearSetting where @LeaveToDate between FromDate and ToDate) = 1)
BEGIN  
	set @test=(select ApplicableAfterDays from TA_Tbl_LeavePolicyDetails lpd     
	inner join TA_TBL_Emp_LeavePolicy elp on elp.LeavePolicy_Id=lpd.Policy_Id   
	inner join TA_tbl_YearSetting y on y.YearSettings_Id=elp.YearSettingId     
	where lpd.leaveType_Id=@leaveType_Id  and elp.EmployeeId=@Employee_Id and y.IsActive=1 
	and elp.ToDate =(select top 1 TA_Tbl_Emp_LeavePolicy.ToDate from TA_Tbl_Emp_LeavePolicy
	inner join TA_Tbl_YearSetting on TA_Tbl_YearSetting.YearSettings_Id=TA_Tbl_Emp_LeavePolicy.YearSettingid
	where TA_Tbl_YearSetting.IsActive=1 and TA_Tbl_Emp_LeavePolicy.EmployeeId=@Employee_Id 
	and (@LeaveFromDate between TA_Tbl_Emp_LeavePolicy.FromDate and TA_Tbl_Emp_LeavePolicy.ToDate) and (@LeaveToDate between TA_Tbl_Emp_LeavePolicy.FromDate and TA_Tbl_Emp_LeavePolicy.ToDate)
	order by TA_Tbl_Emp_LeavePolicy.ToDate desc))
END
ELSE
BEGIN
	set @test=(select ApplicableAfterDays from TA_Tbl_LeavePolicyDetails lpd     
	inner join TA_TBL_Emp_LeavePolicy elp on elp.LeavePolicy_Id=lpd.Policy_Id   
	inner join TA_tbl_YearSetting y on y.YearSettings_Id=elp.YearSettingId     
	where lpd.leaveType_Id=@leaveType_Id  and elp.EmployeeId=@Employee_Id and y.IsActive=1 
	and elp.ToDate =(select top 1 TA_Tbl_Emp_LeavePolicy.ToDate from TA_Tbl_Emp_LeavePolicy
	inner join TA_Tbl_YearSetting on TA_Tbl_YearSetting.YearSettings_Id=TA_Tbl_Emp_LeavePolicy.YearSettingid
	where TA_Tbl_YearSetting.IsActive=1 and TA_Tbl_Emp_LeavePolicy.EmployeeId=@Employee_Id 
	--and (@LeaveFromDate between TA_Tbl_Emp_LeavePolicy.FromDate and TA_Tbl_Emp_LeavePolicy.ToDate) and (@LeaveToDate between TA_Tbl_Emp_LeavePolicy.FromDate and TA_Tbl_Emp_LeavePolicy.ToDate)
	order by TA_Tbl_Emp_LeavePolicy.ToDate desc))
	
END
--and elp.FromDate<=CONVERT(NVARCHAR(20),getdate(),101) and elp.ToDate>=CONVERT(NVARCHAR(20),getdate(),101))     
if(@test=0)    
 begin    
 set @Result='N' 
 end    
else    
 begin    
  declare @eligible as decimal    
  if((select IsActive from TA_Tbl_YearSetting where @LeaveToDate between FromDate and ToDate) = 1)
  BEGIN    
	  set @eligible=(select distinct (Datediff(d,e.DateOfJoining,getdate()) - lpd.ApplicableAfterDays) as EligibleDays   from TA_Tbl_LeavePolicyDetails lpd inner join    
	  TA_TBL_Emp_LeavePolicy elp on elp.LeavePolicy_Id=lpd.Policy_Id     
	  inner join TA_Tbl_Employee e on e.employee_Id=elp.EmployeeId     
	  inner join TA_tbl_YearSetting y on y.YearSettings_Id=elp.YearSettingId    
	  where elp.EmployeeId=@Employee_Id and lpd.LeaveType_Id=@leaveType_Id and y.IsActive=1
	  and elp.ToDate =(select top 1 TA_Tbl_Emp_LeavePolicy.ToDate from TA_Tbl_Emp_LeavePolicy
	  inner join TA_Tbl_YearSetting on TA_Tbl_YearSetting.YearSettings_Id=TA_Tbl_Emp_LeavePolicy.YearSettingid
	  where TA_Tbl_YearSetting.IsActive=1 and TA_Tbl_Emp_LeavePolicy.EmployeeId=@Employee_Id
	  and (@LeaveFromDate between TA_Tbl_Emp_LeavePolicy.FromDate and TA_Tbl_Emp_LeavePolicy.ToDate) and (@LeaveToDate between TA_Tbl_Emp_LeavePolicy.FromDate and TA_Tbl_Emp_LeavePolicy.ToDate) 
	  order by TA_Tbl_Emp_LeavePolicy.ToDate desc))  
	    
  END 
  ELSE
  BEGIN
      set @eligible=(select distinct (Datediff(d,e.DateOfJoining,getdate()) - lpd.ApplicableAfterDays) as EligibleDays   from TA_Tbl_LeavePolicyDetails lpd inner join    
	  TA_TBL_Emp_LeavePolicy elp on elp.LeavePolicy_Id=lpd.Policy_Id     
	  inner join TA_Tbl_Employee e on e.employee_Id=elp.EmployeeId     
	  inner join TA_tbl_YearSetting y on y.YearSettings_Id=elp.YearSettingId    
	  where elp.EmployeeId=@Employee_Id and lpd.LeaveType_Id=@leaveType_Id and y.IsActive=1
	  and elp.ToDate =(select top 1 TA_Tbl_Emp_LeavePolicy.ToDate from TA_Tbl_Emp_LeavePolicy
	  inner join TA_Tbl_YearSetting on TA_Tbl_YearSetting.YearSettings_Id=TA_Tbl_Emp_LeavePolicy.YearSettingid
	  where TA_Tbl_YearSetting.IsActive=1 and TA_Tbl_Emp_LeavePolicy.EmployeeId=@Employee_Id
	  --and (@LeaveFromDate between TA_Tbl_Emp_LeavePolicy.FromDate and TA_Tbl_Emp_LeavePolicy.ToDate) and (@LeaveToDate between TA_Tbl_Emp_LeavePolicy.FromDate and TA_Tbl_Emp_LeavePolicy.ToDate) 
	  order by TA_Tbl_Emp_LeavePolicy.ToDate desc)) 
  END
    if (@eligible>=0)    
      begin    
      set @Result='Y'    
      end       
    else    
     begin    
     set @Result='R'    
     end    
end    
select @Result as RemainingNoOfLeaves    

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Create PROCEDURE [dbo].[Proc_TA_Tbl_LeaveApplication_FileUpload]
	-- Add the parameters for the stored procedure here
		@FileName varchar(50) =NULL,  
        @FilePath nvarchar(200)=NULL,  
        @Emp_Id int=NULL,
        @LeaveApplication_Id int=NULL,
        @Type nvarchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	if(@Type = 'InsertMedicalCertificate')
	begin
	insert into TA_Tbl_LeaveApplication_FileUpload values(@FileName,@FilePath,@Emp_Id,@LeaveApplication_Id);
	end
	else if(@Type = 'SelectMedicalCertificate')
	begin
	select FilePath from TA_Tbl_LeaveApplication_FileUpload where LeaveApplication_Id=@LeaveApplication_Id
	end
	else if(@Type = 'DeleteMedicalCertificate')
	begin
	Delete from TA_Tbl_LeaveApplication_FileUpload where LeaveApplication_Id=@LeaveApplication_Id
	end
	else if(@Type = 'GetCountMedicalCertificate')
	begin
	select COUNT(*) as GetCount from TA_Tbl_LeaveApplication_FileUpload where LeaveApplication_Id=@LeaveApplication_Id
	end
END
-----------------------------------------------------------------------------------------------------------------------------------------
Create PROCEDURE [dbo].[Pro_GetLeaveApplicationId] 
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    select LeaveApplicationID=IDENT_CURRENT('TA_Tbl_LeaveApplication')+1
END
-----------------------------------------------------------------------------------------------------------------------------------------------
ALTER     PROCEDURE [dbo].[Proc_TA_SelectLeavePolicyDetailsLeaveApplication] 
@Employee_Id int,
@LeaveType_Id int,
@FromDate datetime,
@ToDate datetime
AS
BEGIN
	Declare @DateofJoining datetime
	Declare @currentMonth datetime
	Declare @FinancilaYearStartDate datetime
	Declare @difference_fy int
	Declare @difference_DJ int
	Declare @flag int
	IF(@FromDate>GETDATE() or @ToDate>GETDATE())
	BEGIN
		DECLARE @diff INT
		IF(@FromDate>GETDATE() and @ToDate>GETDATE())
		BEGIN
			SET @diff=DATEDIFF(MONTH,GETDATE(),@ToDate)
			SET @currentMonth=DATEADD(MONTH,@diff,GETDATE())
		END
		ELSE IF(@FromDate>GETDATE())
		BEGIN
			SET @diff=DATEDIFF(MONTH,GETDATE(),@FromDate)
			SET @currentMonth=DATEADD(MONTH,@diff,GETDATE())
		END
		ELSE
		BEGIN
			SET @diff=DATEDIFF(MONTH,GETDATE(),@ToDate)
			SET @currentMonth=DATEADD(MONTH,@diff,GETDATE())
		END
	END
	ELSE
	BEGIN	
		set @currentMonth=GETDATE()
	END
	IF(@currentMonth is null)
	BEGIN
		set @currentMonth=GETDATE()
	END	
	--if((select IsActive from TA_Tbl_YearSetting where @FromDate between FromDate and ToDate) = 1)
	--	BEGIN
	--		select @DateofJoining=
	--		CASE	WHEN DATEPART(d,DateOfJoining)>15 then
	--					CASE	WHEN DATEADD(D,0,DATEADD(d,-DATEPART(d,DateOfJoining)+1,dateadd(MONTH,1,DateOfJoining)))>15 then dateadd(d,0,DATEADD(D,0,DATEADD(d,-DATEPART(d,DateOfJoining)+1,dateadd(MONTH,1,DateOfJoining))))
	--							ELSE DATEADD(D,0,DATEADD(d,-DATEPART(d,DateOfJoining)+1,dateadd(MONTH,1,DateOfJoining)))
	--					END 
	--				ELSE DATEADD(d,0,DATEADD(d,0,DateOfJoining))
	--		END
	--		from TA_Tbl_Employee where Employee_Id=@Employee_Id and Isdeleted=0
	--		select @FinancilaYearStartDate=FromDate from TA_Tbl_YearSetting where IsActive=1
	--		set @difference_fy=DATEDIFF(month,@FinancilaYearStartDate,@currentMonth)+1
	--		set @difference_DJ=DATEDIFF(month,@DateofJoining,@currentMonth)+1
	--		select @flag=MIN(b.a) from
	--		(
	--			select	(@difference_DJ) as a union all
	--			select  (@difference_fy) as a
	--		)as b
	Declare @multiply int
	if((select IsActive from TA_Tbl_YearSetting where @FromDate between FromDate and ToDate) = 1)
		BEGIN
			select @FinancilaYearStartDate=FromDate from TA_Tbl_YearSetting where IsActive=1
			select @multiply=
			CASE	WHEN DATEPART(d,DateOfJoining)>15 then
						case	when DATEADD(d,-DATEPART(d,DateOfJoining)+1,dateadd(MONTH,1,DateOfJoining))<=@FinancilaYearStartDate then DATEDIFF(month,@FinancilaYearStartDate,getdate())+1
								else DATEDIFF(month,DATEADD(d,-DATEPART(d,DateOfJoining)+1,dateadd(MONTH,1,DateOfJoining)),getdate())+1
						end 
					else
						case	when DateOfJoining<=@FinancilaYearStartDate then DATEDIFF(month,@FinancilaYearStartDate,getdate())+1
								else DATEDIFF(month,DateOfJoining,getdate())+1
						end 
			END
			from TA_Tbl_Employee where Employee_Id=@Employee_Id and Isdeleted=0
			Select *,Eligible=@multiply*lpd.MaxDays_Month from Ta_TBl_LeavePolicyDetails lpd  
			inner join Ta_Tbl_Emp_LeavePolicy elp on elp.LeavePolicy_Id=lpd.Policy_Id
			where elp.EmployeeId=@Employee_Id and lpd.LeaveType_Id=@LeaveType_Id 
			and elp.ToDate =(select top 1 TA_Tbl_Emp_LeavePolicy.ToDate from TA_Tbl_Emp_LeavePolicy
			inner join TA_Tbl_YearSetting on TA_Tbl_YearSetting.YearSettings_Id=TA_Tbl_Emp_LeavePolicy.YearSettingid
			where TA_Tbl_YearSetting.IsActive=1
			and (@FromDate between TA_Tbl_Emp_LeavePolicy.FromDate and TA_Tbl_Emp_LeavePolicy.ToDate) and (@ToDate between TA_Tbl_Emp_LeavePolicy.FromDate and TA_Tbl_Emp_LeavePolicy.ToDate)
			and TA_Tbl_Emp_LeavePolicy.EmployeeId=@Employee_Id order by TA_Tbl_Emp_LeavePolicy.ToDate desc)
		END
	ELSE
		BEGIN
			--select @DateofJoining=
			--CASE	WHEN DATEPART(d,DateOfJoining)>15 then 
			--			CASE	WHEN DATEADD(D,0,DATEADD(d,-DATEPART(d,DateOfJoining)+1,dateadd(MONTH,1,DateOfJoining)))>15 then dateadd(d,0,DATEADD(D,0,DATEADD(d,-DATEPART(d,DateOfJoining)+1,dateadd(MONTH,1,DateOfJoining))))
			--					ELSE DATEADD(D,0,DATEADD(d,-DATEPART(d,DateOfJoining)+1,dateadd(MONTH,1,DateOfJoining)))
			--			END 
			--		ELSE DATEADD(d,0,DATEADD(d,0,DateOfJoining))
			--END
			--from TA_Tbl_Employee where Employee_Id=@Employee_Id and Isdeleted=0
			--select @FinancilaYearStartDate=FromDate from TA_Tbl_YearSetting where @FromDate between FromDate and ToDate
			--set @difference_fy=DATEDIFF(month,@FinancilaYearStartDate,@currentMonth)+1
			--set @difference_DJ=DATEDIFF(month,@DateofJoining,@currentMonth)+1
			--select @flag=MIN(b.a) from
			--(
			--	select	(@difference_DJ) as a union all
			--	select  (@difference_fy) as a
			--)as b	
			select @FinancilaYearStartDate=FromDate from TA_Tbl_YearSetting where IsActive=1
			select @multiply=
			CASE	WHEN DATEPART(d,DateOfJoining)>15 then
						case	when DATEADD(d,-DATEPART(d,DateOfJoining)+1,dateadd(MONTH,1,DateOfJoining))<=@FinancilaYearStartDate then DATEDIFF(month,@FinancilaYearStartDate,getdate())+1
								else DATEDIFF(month,DATEADD(d,-DATEPART(d,DateOfJoining)+1,dateadd(MONTH,1,DateOfJoining)),getdate())+1
						end 
					else
						case	when DateOfJoining<=@FinancilaYearStartDate then DATEDIFF(month,@FinancilaYearStartDate,getdate())+1
								else DATEDIFF(month,DateOfJoining,getdate())+1
						end 
			END 
			from TA_Tbl_Employee where Employee_Id=@Employee_Id and Isdeleted=0	
			Select *,Eligible=@multiply*lpd.MaxDays_Month from Ta_TBl_LeavePolicyDetails lpd
			inner join Ta_Tbl_Emp_LeavePolicy elp on elp.LeavePolicy_Id=lpd.Policy_Id
			where elp.EmployeeId=@Employee_Id and lpd.LeaveType_Id=@LeaveType_Id 
			and elp.ToDate =(select top 1 TA_Tbl_Emp_LeavePolicy.ToDate from TA_Tbl_Emp_LeavePolicy
			inner join TA_Tbl_YearSetting on TA_Tbl_YearSetting.YearSettings_Id=TA_Tbl_Emp_LeavePolicy.YearSettingid
			where TA_Tbl_YearSetting.IsActive=1
			and TA_Tbl_Emp_LeavePolicy.EmployeeId=@Employee_Id order by TA_Tbl_Emp_LeavePolicy.ToDate desc)
		END
END
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ALTER PROCEDURE [dbo].[Proc_TA_Update_LeaveTypeDetails]        
(        
 @LeaveType NVARCHAR(50),        
 @LeaveDescription NVARCHAR(250),      
 @TypeId INT,    
 @employeeId int,  
 @CountFactor int,
 @EmpCategory int,
 @EmpType int,
 @Gender varchar(10),
 @Religion int,
 @IsDocProofRequired int,
 @LeaveCtgry int
)        
AS        
 BEGIN        
  --declare @sql nvarchar(2000)          
  --SET @sql='
  UPDATE TA_Tbl_LeaveType SET LeaveType =@LeaveType,LeaveDescription= @LeaveDescription, 
  Emp_ID=@employeeId,CountFactor=@CountFactor,EmployeeCategory=@EmpCategory,EmployeeType=@EmpType,Gender=@Gender,Religion=@Religion,IsDocProofRequired=@IsDocProofRequired,LeaveCategory=@LeaveCtgry WHERE LeaveType_Id=@TypeId
 -- exec(@sql)    
 END 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[Proc_TA_Insert_LeaveTypeDetails]    
(    
 @LeaveType NVARCHAR(50),
 @LeaveDescription NVARCHAR(250),  
 @Emp_Id NVARCHAR(50),  
 @CountFactor int,
 @EmployeeCategory int,
 @EmployeeType int,
 @Gender varchar(10),
 @Religion int,
 @IsDocProofRequired int,
 @LeaveCtgry int
)    
AS  
SET NOCOUNT ON    
 BEGIN    
  declare @sql nvarchar(2000)  
	insert into TA_Tbl_LeaveType(LeaveType,LeaveDescription,IsDeleted,Emp_ID,CountFactor,EmployeeCategory,EmployeeType,Gender,Religion,IsDocProofRequired,LeaveCategory)
	VAlues(@LeaveType,@LeaveDescription,0,@Emp_Id,@CountFactor,@EmployeeCategory,@EmployeeType,@Gender,@Religion,@IsDocProofRequired,@LeaveCtgry)
End
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER Proc [dbo].[Proc_TA_GetLeaveTypeDetailsById]      
(      
 @LeaveId int,    
 @UserGroupName nVarchar(250)      
)      
AS      
Begin   
  SET NOCOUNT ON;   
  IF(@UserGroupName = 'SuperAdmin')    
    BEGIN    
       SELECT LeaveType_Id,LeaveType,LeaveDescription,CountFactor,IsDeleted,       
       (select categoryname from TA_Tbl_Category where CategoryId=EmployeeCategory)as EmployeeCategory,EmployeeCategory as EmployeeCategorys,
              (select Typename from TA_Tbl_Type where TypeId=EmployeeType)as EmployeeType,EmployeeType as EmployeeTypes,
              Gender,(select Name from TA_Tbl_Religion where ID=Religion)as Religion,Religion as Religions,IsDocProofRequired=isnull(IsDocProofRequired,1),LeaveCategory
       FROM TA_Tbl_LeaveType Where LeaveType_Id=@LeaveId
    END    
 ELSE    
   BEGIN    
       SELECT LeaveType_Id,LeaveType,LeaveDescription,CountFactor,IsDeleted,(select categoryname from TA_Tbl_Category where CategoryId=EmployeeCategory)as EmployeeCategory,EmployeeCategory as EmployeeCategorys,
       (select Typename from TA_Tbl_Type where TypeId=EmployeeType)as EmployeeType,EmployeeType as EmployeeTypes,
       Gender,(select Name from TA_Tbl_Religion where ID=Religion)as Religion
       ,Religion as Religions,IsDocProofRequired=isnull(IsDocProofRequired,1),LeaveCategory
        FROM TA_Tbl_LeaveType 
       Where IsDeleted=0 AND LeaveType_Id=@LeaveId      
   END  
END
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------