USE [T9eHR]

GO

/****** Object:  StoredProcedure [dbo].[Sp_RPT030309]    Script Date: 2023/3/10 12:26:32 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER proc [dbo].[Sp_RPT030309](

    @FullCode varchar(200)='9',

@UseName varchar(20)='',

@Success bit=0 output,

@Msg nvarchar(100)='' output,

@RtnTblResult  bit=1,

@WorkDate DATETIME

)

as

set nocount on

begin try

IF NOT OBJECT_ID('tempdb..#Emps') IS NULL

DROP TABLE #Emps

SELECT

E.ID EmpID,CASE WHEN E.Sex = 2 THEN 0 ELSE E.Sex END Sex,D.FullCode INTO #Emps

FROM T_HR_Employee E

JOIN T_HR_Department D

ON E.DeptID = D.ID

WHERE E.DimissionDate IS NULL

AND D.FullCode LIKE @FullCode + '%'

ORDER BY D.FullCode

SELECT

CAST(a.Id AS VARCHAR(100)) AS Id,CAST(a.ParentId AS VARCHAR(100)) AS ParentId,a.DepartmentCode DeptCode,a.DepartmentName DeptName,a.FullName,a.DeptLeve level,T2.*

,(SELECT CASE WHEN COUNT(1) > 0 THEN 'false'ELSE 'true'END FROM T_HR_Department b WHERE b.ParentId = a.Id)AS isLeaf,

CASE WHEN a.DeptLeve <= 3 THEN 'true' ELSE 'false' END AS expanded

,ISNULL(T4.ycqrs,0)AS ycqrs,ISNULL(T3.sjcqrs,0) AS sjcqrs,ISNULL(T4.ycqrs,0)-ISNULL(T3.sjcqrs,0) AS qqrs

,CONVERT(VARCHAR(20),ISNULL(Round (T3.sjcqrs/(CASE  WHEN ISNULL(T4.ycqrs,0)=0 THEN 1.0 ELSE T4.ycqrs END)*100 ,2),0))+'%'AS cql

FROM T_HR_Department a

LEFT JOIN (

SELECT D.FullCode,SUM(ISNULL(rs, 0)) zrs,SUM(ISNULL(man, 0)) man,SUM(ISNULL(rs, 0)) - SUM(ISNULL(man, 0)) women

FROM T_HR_Department D

LEFT JOIN (

SELECT FullCode,COUNT(EmpID) rs,SUM(Sex) man

FROM #Emps GROUP BY FullCode

) T ON T.FullCode LIKE D.FullCode + '%'

GROUP BY D.FullCode

) T2 ON a.FullCode = T2.FullCode

LEFT JOIN

(

SELECT D.FullCode,SUM(ISNULL(sjcqrs, 0)) sjcqrs

FROM T_HR_Department D

LEFT JOIN

(

SELECT B.FullCode,sjcqrs=convert(float,ISNULL(COUNT(a.EmpID),0 ))

FROM dbo.T_HR_WorkingTime a

LEFT JOIN T_HR_Department B on A.DeptID=B.ID 

WHERE 

WorkDate =@WorkDate AND 

ISNULL(G_cqgs,0)>=4 

--AND B.FullCode LIKE @FullCode + '%'

GROUP BY B.FullCode

)T ON T.FullCode= D.FullCode

GROUP BY D.FullCode

)T3 ON A.FullCode = T3.FullCode

LEFT JOIN

(

SELECT D.FullCode,SUM(ISNULL(ycqrs, 0)) ycqrs

FROM T_HR_Department D

LEFT JOIN

(

SELECT B.FullCode,ycqrs=convert(float,ISNULL(COUNT(a.EmpID),0))

FROM dbo.T_HR_WorkingTime a

LEFT JOIN T_HR_Department B on A.DeptID=B.ID 

WHERE 

WorkDate =@WorkDate 

--ISNULL(G_cqgs,0)>=4

--AND B.FullCode LIKE @FullCode + '%'

GROUP BY B.FullCode

)T ON T.FullCode=D.FullCode

GROUP BY D.FullCode

)T4 ON A.FullCode = T4.FullCode

WHERE a.FullCode LIKE @FullCode + '%'

ORDER BY a.DepartmentCode

IF NOT OBJECT_ID('tempdb..#Emps') IS NULL

DROP TABLE #Emps

goto Success  

--------------------------------------------------------------------------------------错误捕捉结束  

End try  

begin catch  

select @Msg=@Msg +char(13)+char(10) + Error_Message()  

goto Rtn  

end Catch  

--------------------------------------------------------------------------------------错误捕捉结束  

Success:  

select @Success=1,@msg=''  

goto Rtn  

Rtn:  

if @RtnTblResult=1

begin