USE [XinYe12345]
GO
/****** Object:  StoredProcedure [dbo].[P_Info_DeptAcceptCount]    Script Date: 01/16/2020 14:03:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[P_Info_DeptAcceptCount] 
@sdate varchar(20),
@edate varchar(20),
@deptid int=0
AS
BEGIN
  declare @totalsql VARCHAR(MAX)
  declare @createsql VARCHAR(1000)
  declare @insertsql VARCHAR(MAX)
  set @createsql = 'create table #tempdeptacceptcounttable("部门名称" VARCHAR(50)'
  set @insertsql=''

  declare @tempsid int
  declare @tempsvalue varchar(50)
  declare @temptid int
  declare @temptvalue varchar(50)
  declare @tempcount int
  declare @temptotalcount int
	declare @temprate varchar(50)

  declare @temptnum int=0

  SELECT * INTO #allacceptcount from T_Bus_AssignedInfo where F_IsDelete=0 and F_IsSure!=3 
	and datediff(day,F_CreateTime,@sdate)<=0 and datediff(day,F_CreateTime,@edate)>=0
	SELECT @temptotalcount=count(1) FROM #allacceptcount

  DECLARE TCUR CURSOR FOR
  SELECT F_DeptId,F_DeptName FROM dbo.T_Sys_Department where F_IsDept='1' and (F_DeptId=@deptid or @deptid=0)
	OPEN TCUR--打开游标
	FETCH NEXT FROM TCUR INTO @temptid,@temptvalue --id、名称
	WHILE @@FETCH_STATUS=0
			BEGIN
          select * into #deptacceptcount from #allacceptcount where F_MainDeptId=@temptid
          set @insertsql = @insertsql+'insert into #tempdeptacceptcounttable values(N'''+@temptvalue+''''
          DECLARE SCUR CURSOR FOR
	      --  SELECT F_ValueId,F_Value FROM T_Sys_DictionaryValue WHERE F_State=0 AND  F_ItemId=2
 with cte(F_ValueId ,F_Value) as
(
select F_ValueId  ,F_Value from T_Sys_DictionaryValue where F_PrentId = 37 and F_State=0  
 UNION all select t.F_ValueId ,t.F_Value from T_Sys_DictionaryValue as t 
    inner join cte as c on  c.F_ValueId  =t.F_PrentId 
  )
select * from cte
	        OPEN SCUR--打开游标
	        FETCH NEXT FROM SCUR INTO @tempsid,@tempsvalue --id、名称
	        WHILE @@FETCH_STATUS=0
			      BEGIN
              select * into #deptaccepttypecount from T_Bus_WorkOrder where F_IsDelete=0  and  F_InfoType=@tempsid
							and F_WorkOrderID in (SELECT F_WorkOrderID FROM  #deptacceptcount)
              if @temptnum=0
                BEGIN
                  set @createsql=@createsql+',"'+@tempsvalue+'" int'
                END
              if @temptid>0
                BEGIN
                  select @tempcount=count(1) from #deptaccepttypecount
                END
              set @insertsql=@insertsql+','''+CONVERT(VARCHAR(50),@tempcount)+''''
              drop TABLE #deptaccepttypecount
              FETCH NEXT FROM SCUR INTO @tempsid,@tempsvalue  
			      END
	        CLOSE SCUR--关闭游标
	        DEALLOCATE SCUR
          if @temptnum=0
             BEGIN
                set @createsql = @createsql+',"合计" int,"占比" VARCHAR(50)); '
                set @temptnum=1
             END
				  select @tempcount=count(1) from T_Bus_WorkOrder where F_IsDelete=0 and F_WorkOrderID in (SELECT F_WorkOrderID FROM  #deptacceptcount)
					set @insertsql=@insertsql+','''+CONVERT(VARCHAR(50),@tempcount)+''''
					--占比率
        if @temptotalcount>0
          BEGIN
            select @temprate=cast(convert (decimal(18,2),100*cast(@tempcount as float)/@temptotalcount ) as varchar)+'%'
          END
        ELSE
          BEGIN
            select @temprate=''
          END
					set @insertsql=@insertsql+','''+@temprate+''''
          set @insertsql = @insertsql+'); '
          drop TABLE #deptacceptcount
          FETCH NEXT FROM TCUR INTO @temptid,@temptvalue  
			END
	CLOSE TCUR--关闭游标
	DEALLOCATE TCUR
  DROP TABLE #allacceptcount

  set @totalsql = @createsql+@insertsql+'SELECT * from #tempdeptacceptcounttable order by "合计" desc;DROP TABLE #tempdeptacceptcounttable'
  exec(@totalsql)
  
END