USE [XinYe12345]
GO
/****** Object:  StoredProcedure [dbo].[P_Info_KeyCount]    Script Date: 01/16/2020 14:46:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[P_Info_KeyCount] 
@sdate varchar(20),
@edate varchar(20),
@keyid int=0
AS
BEGIN
  declare @totalsql VARCHAR(MAX)
  declare @createsql VARCHAR(1000)
  declare @insertsql VARCHAR(MAX)
  set @createsql = 'create table #tempkeycounttable("关键字" 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 #allkeycount from T_Bus_WorkOrder where F_IsDelete=0  
	and datediff(day,F_CreateTime,@sdate)<=0 and datediff(day,F_CreateTime,@edate)>=0

  DECLARE TCUR CURSOR FOR
  -- SELECT F_ValueId,F_Value FROM dbo.T_Sys_DictionaryValue where F_ItemId = 3 and F_State = 0 and (F_ValueId=@keyid or @keyid=0)
     with cte(F_ValueId ,F_Value) as
(
select F_ValueId  ,F_Value from T_Sys_DictionaryValue where F_PrentId = 38 and F_State=0  and (F_ValueId=@keyid or @keyid=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 TCUR--打开游标
	FETCH NEXT FROM TCUR INTO @temptid,@temptvalue --id、名称
	WHILE @@FETCH_STATUS=0
			BEGIN
          select * into #keytypecount from #allkeycount where ','+F_Key+',' like '%,'+CONVERT(varchar(100),@temptid)+',%'
          set @insertsql = @insertsql+'insert into #tempkeycounttable 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 #keytypecount where F_IsDelete=0  and  F_InfoType=@tempsid
							
              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); '
                set @temptnum=1
             END
				  select @tempcount=count(1) from #keytypecount
					set @insertsql=@insertsql+','''+CONVERT(VARCHAR(50),@tempcount)+''''
          set @insertsql = @insertsql+'); '
          drop TABLE #keytypecount
          FETCH NEXT FROM TCUR INTO @temptid,@temptvalue  
			END
	CLOSE TCUR--关闭游标
	DEALLOCATE TCUR
  DROP TABLE #allkeycount
  set @totalsql = @createsql+@insertsql+'SELECT *,row_number() over(order by "合计" desc) "排名" from #tempkeycounttable ;DROP TABLE #tempkeycounttable'

  exec(@totalsql)
  
END