USE GO/****** Object: StoredProcedure . Script Date: 2023/3/10 12:26:32 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc .( @FullCode varchar(200)='9', @UseName varchar(20)='', @Success bit=0 output, @Msg nvarchar(100)='' output, @RtnTblResult bit=1, @WorkDate DATETIME)as set nocount onbegin 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
