GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects
where id=object_id(N'[dbo].[p_Police_Binding_Statistics]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_Police_Binding_Statistics]
GO
SET QUOTED_IDENTIFIER ON
GO
/*add 20190604 xqy 警员绑定统计*/
CREATE PROC [dbo].[p_Police_Binding_Statistics]
@OrgNo VARCHAR(40) , --组织机构代码
@RoleId NVARCHAR(100)=null --角色ID
AS
DECLARE @R VARCHAR(12) ='';
SET @R=dbo.EarseZero(@OrgNO);
--二级组织机构集合 #t_Org
DECLARE @ID INT;
SELECT @ID=Id FROM t_Organization WHERE OrgNo=@OrgNO AND IsDel=0
select dbo.EarseZero(OrgNo) as Org,OrgNo,ShowName as ShortName,OrgName
INTO #t_Org
FROM t_Organization WHERE (PId=@ID OR Id=@ID) AND IsDel=0
ORDER BY Org
--警员总数
select count(uid) as JYZS,GAJGDM,dbo.EarseZero(GAJGDM) as GAJGDM_N
INTO #JYZS from T_user
where (@RoleId is null or uid in (select uid from t_userrole where RID=@RoleId))
and (@OrgNo IS null or GAJGDM like ''+@R+'%')
group by GAJGDM
--申请数
select count(PPID) as WeiXinTotal,u.GAJGDM,dbo.EarseZero(GAJGDM) as GAJGDM_N1
INTO #SQS
from t_policeprove p join t_user u on p.policeid=u.Jh
where (@RoleId is null or uid in (select uid from t_userrole where RID=@RoleId))
and (@OrgNo IS null or GAJGDM like ''+@R+'%')
group by GAJGDM
--实名认证数
select count(PPID) as Name_Authentication,u.GAJGDM,dbo.EarseZero(GAJGDM) as GAJGDM_N2
INTO #SMRZS
from t_policeprove p join t_user u on p.policeid=u.Jh
where (@RoleId is null or uid in (select uid from t_userrole where RID=@RoleId))
and (@OrgNo IS null or GAJGDM like ''+@R+'%')
and status=1 group by GAJGDM
--近7天访问总数
select count(ID) as Visit7Day,WorkUnit,dbo.EarseZero(WorkUnit) as GAJGDM_N3
into #7TFWZS
from t_log l join t_user u on l.UserId=u.UID
where DateDiff(dd,operationtime,getdate())<=7
and operationAction='150112' and workUnit IS not null
and (@RoleId is null or uid in (select uid from t_userrole where RID=@RoleId))
and (@OrgNo IS null or GAJGDM like ''+@R+'%')
group by WorkUnit
--组织机构和警员总数联合
select a.org,a.orgno,a.OrgName,sum(JYZS)JYZS into #Result1 from #t_Org a join #JYZS b on a.org=b.GAJGDM_N group by a.org,a.orgno,a.OrgName;
--select * from #Result1;
--组织机构和申请数,有疑问,多了
select c.org,c.orgno,c.OrgName,sum(WeiXinTotal)WeiXinTotal into #Result2 from #t_Org c join #SQS d on c.org=d.GAJGDM_N1 group by c.org,c.orgno,c.OrgName;
--select * from #Result2;
--组织机构和实名认证数
select e.org,e.orgno,e.OrgName,sum(Name_Authentication)Name_Authentication into #Result3 from #t_Org e join #SMRZS f on e.org=f.GAJGDM_N2 group by e.org,e.orgno,e.OrgName;
--select * from #Result3;
--组织机构和近七天访问数
select g.org,g.orgno,g.OrgName,sum(Visit7Day)Visit7Day into #Result4 from #t_Org g join #7TFWZS h on g.org=h.GAJGDM_N3 group by g.org,g.orgno,g.OrgName;
--select * from #Result4;
--组织机构
--JYZS 是警员总数
--WeiXinTotal 是申请数
--Name_Authentication 是实名认证数
--WSQS 是未申请数 未申请数=申请数-实名认证数
--Visit7Day 是近七天访问数
select a1.*,
isnull(JYZS,0) JYZS,isnull(WeiXinTotal,0)WeiXinTotal,isnull(Name_Authentication,0)Name_Authentication,isnull(Visit7Day,0)Visit7Day
--JYZS,WeiXinTotal,Name_Authentication,Visit7Day
,isnull(WeiXinTotal-Name_Authentication,0)WSQS
From #t_Org a1
left join #Result1 b1 on a1.orgno=b1.orgno
left join #Result2 b2 on a1.orgno=b2.orgno
left join #Result3 b3 on a1.orgno=b3.orgno
left join #Result4 b4 on a1.orgno=b4.orgno