IF OBJECT_ID('dbo.HRM_spUserAndRelatedEmpWithAttendanceCalcInfo') IS NOT NULL
	DROP PROCEDURE dbo.HRM_spUserAndRelatedEmpWithAttendanceCalcInfo
GO

CREATE PROCEDURE [dbo].[HRM_spUserAndRelatedEmpWithAttendanceCalcInfo]
	@ymCode	int
AS
BEGIN

	set rowcount 0

	if @ymCode is null
		set @ymCode=-1
		
	declare @colSql nvarchar(MAX)
	set @colSql=''

	--build factors sql:
	select @colSql=@colSql
	+' , max(CASE AttendanceFactorId WHEN '+ltrim(str(AttendanceFactorId))+' THEN '+'CalcValStr'+' ELSE '''' END) AS '+quotename(Title)
	+' , max(CASE AttendanceFactorId WHEN '+ltrim(str(AttendanceFactorId))+' THEN '+'FinValStr'+' ELSE '''' END) AS '+quotename(Title+' (ويرايشي)')
	from hrm_tblAttendanceFactor 
	where ScaleCode=2 AND
	AttendanceFactorID in (49)
	order by PFPrintPriority

	--print @colSql

	--final sql:
	declare @finSql nvarchar(MAX)
	set @finSql='select UR.UserID,UR.UserPartyID, MV.EmpCode as [كد پرسنلي], ES.FirstName as [نام],ES.LastName as [نام خانوادگي] ,MV.EmpFullName as [نام و نام خانوادگي],MV.EmployeeId as [شناسه كارمند] 
				 ,ES.DepartmentTitle as [واحد سازماني],ES.EmpTypeDesc as[نوع استخدام],ES.CostCenter as [مركز هزينه],ES.WorkLocationDesc as [محل خدمت] '+
				@colSql+
				',C.FromDate as [شروع ماه],C.ToDate as [پايان ماه],C.Description as [نام ماه]'+
				' FROM hrm_vwMonthlyAtdCalc MV 
				JOIN HRM_vwrptLastEmpStatute AS ES ON  MV.EmployeeId = ES.EmployeeID
				JOIN HRM_tblUserAndRelatedEmp UR ON  ES.EmployeeID = UR.RelatedEmpID
				CROSS JOIN CMS_tblMonthRange C '+
				' where mv.AttendanceFactorID in (49) AND MV.EffectiveYMCode='+ltrim(str(@ymCode))+' and C.Code='+str(@ymCode)+
				' group by UR.UserID,UR.UserPartyID, MV.EmployeeId,MV.EmpCode,MV.EmpFullName,C.FromDate,C.ToDate,C.Description 
				 ,ES.DepartmentTitle,ES.EmpTypeDesc,ES.CostCenter,ES.WorkLocationDesc,ES.FirstName ,ES.LastName'

	--print @finSql

	exec(@finSql)

END

