USE GO/****** Object: StoredProcedure [dbo].[Web_UnitManage_NSMC_tb_Unit_StatByUserID] Script Date: 2012/10/26 16:20:44 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*-------------------------------------- 用途：根据用户ID查询辖区场所统计-- 项目名称：PSM5.7.3-- 说明：-- 时间：2012-09-24-- 编写者: ---------------------------------------- 修改记录:-- 编号修改时间修改人修改原因修改标注------------------------------------*//****************************************************************测试语句EXEC [Web_UnitManage_NSMC_tb_Unit_StatByUserID] @UserID=0****************************************************************/ALTER PROCEDURE [dbo].[Web_UnitManage_NSMC_tb_Unit_StatByUserID](@UserID INT)AS BEGINSET NOCOUNT ON DECLARE @SQL NVARCHAR(MAX) --根据用户ID，将需要处理的地市存入临时表SELECT A.AreaCode,A.AreaID,A.AreaNameINTO #TempAreaFROM dbo.NSMC_tb_Area A WITH(NOLOCK)WHERE (@UserID=0 OR EXISTS (SELECT 1 FROM dbo.NSMC_re_UserArea WITH(NOLOCK) WHERE AreaID=A.AreaID AND UserID=@UserID))AND A.ParentCode NOT IN (-1,-2)--如果不存在需要处理的记录，直接退出IF NOT EXISTS (SELECT 1 FROM #TempArea)RETURN--建立临时表记录结果集CREATE TABLE #TempResult(AreaID INT,AreaCode NVARCHAR(6),PoliceStationID INT,AreaPoliceName NVARCHAR(30),HotelCount INT,SchoolCount INT,EnterpriseCount INT,WifiUnitCount INT,AllUnitCount INT,OnlineUnit INT,OfflineUnit INT,UnitOnlineRate NUMERIC(6,2),OnlineCustomTotal INT,AlertCount INT) --先统计地区统计值SELECT U.AreaCode,COUNT(1) AS OnlineCustomTotalINTO #TempAreaCompFROM dbo.NSMC_log_Customer C WITH(NOLOCK)INNER JOIN dbo.NSMC_tb_Unit U WITH(NOLOCK)ON C.UnitID = U.UnitIDWHERE U.GuildID<>1AND C.StartTime>= CONVERT(nvarchar(10),getdate(),120) AND C.StartTime <= CONVERT(nvarchar(10),DATEADD(DAY,1,getdate()),120)GROUP BY U.AreaCodeSELECT U.AreaCode,COUNT(1) AS AlertCountINTO #TempAreaAlertFROM dbo.NSMC_tb_Alert A WITH(NOLOCK)INNER JOIN dbo.NSMC_tb_Unit U WITH(NOLOCK)ON A.GuildID = U.GuildID AND A.UnitCode = U.UnitCodeWHERE A.GuildID<>1 AND A.AlertDate >= CONVERT(nvarchar(10),getdate(),120) AND A.AlertDate <= CONVERT(nvarchar(10),DATEADD(DAY,1,getdate()),120)GROUP BY U.AreaCodeINSERT INTO #TempResultSELECT MAX(TA.AreaID) AS AreaID,TA.AreaCode AS AreaCode,NULL AS PoliceStationID,MAX(TA.AreaName) AS AreaPoliceName,SUM(CASE U.GuildID WHEN 2 THEN 1 ELSE 0 END) AS HotelCount,SUM(CASE U.GuildID WHEN 3 THEN 1 ELSE 0 END) AS SchoolCount,SUM(CASE U.GuildID WHEN 4 THEN 1 ELSE 0 END) AS EnterpriseCount,SUM(CASE U.GuildID WHEN 5 THEN 1 ELSE 0 END) AS WifiUnitCount,SUM(CASE WHEN U.GuildID<>1 THEN 1 ELSE 0 END) AS AllUnitCount,SUM(CASE WHEN US.CurStatus=1 AND U.GuildID<>1 THEN 1 ELSE 0 END) AS OnlineUnit,SUM(CASE WHEN ISNULL(US.CurStatus,0)=0 AND U.GuildID<>1 THEN 1 ELSE 0 END) AS OfflineUnit,(CASE WHEN SUM(CASE WHEN U.GuildID<>1 THEN 1 ELSE 0 END)=0 THEN 0 ELSESUM(CASE WHEN US.CurStatus=1 AND U.GuildID<>1 THEN 1 ELSE 0 END)*1.00/SUM(CASE WHEN U.GuildID<>1 THEN 1 ELSE 0 END) END)*100 AS UnitOnlineRate,MAX(ISNULL(TAC.OnlineCustomTotal,0)) AS OnlineCustomTotal,MAX(ISNULL(TAA.AlertCount,0)) AS AlertCountFROM #TempArea TALEFT JOIN dbo.NSMC_tb_Unit U WITH(NOLOCK)ON U.AreaCode=TA.AreaCodeLEFT JOIN dbo.NSMC_re_UnitStatus US WITH(NOLOCK)ON U.UnitID = US.UnitIDLEFT JOIN #TempAreaComp TACON TA.AreaCode=TAC.AreaCodeLEFT JOIN #TempAreaAlert TAAON TAA.AreaCode = TA.AreaCodeGROUP BY TA.AreaCode--再统计各警局辖区的统计值SELECT UPS.PoliceStationID, COUNT(1) AS OnlineCustomTotal INTO #TempCompFROM dbo.NSMC_log_Customer C WITH(NOLOCK)INNER JOIN dbo.NSMC_re_UnitPoliceStation UPS WITH(NOLOCK)ON C.UnitID = UPS.UnitIDINNER JOIN dbo.NSMC_tb_Unit U WITH(NOLOCK)ON UPS.UnitID = U.UnitIDWHERE U.GuildID<>1AND C.StartTime>= CONVERT(nvarchar(10),getdate(),120) AND C.StartTime <= CONVERT(nvarchar(10),DATEADD(DAY,1,getdate()),120)GROUP BY UPS.PoliceStationIDSELECT UPS.PoliceStationID, COUNT(1) AS AlertCount INTO #TempAlertFROM dbo.NSMC_tb_Alert A WITH(NOLOCK)INNER JOIN dbo.NSMC_tb_Unit U WITH(NOLOCK)ON A.GuildID = U.GuildID AND A.UnitCode = U.UnitCodeINNER JOIN dbo.NSMC_re_UnitPoliceStation UPS WITH(NOLOCK)ON U.UnitID=UPS.UnitIDWHERE A.GuildID<>1 AND A.AlertDate >= CONVERT(nvarchar(10),getdate(),120) AND A.AlertDate <= CONVERT(nvarchar(10),DATEADD(DAY,1,getdate()),120)GROUP BY UPS.PoliceStationIDINSERT INTO #TempResultSELECT MAX(TA.AreaID) AS AreaID,TA.AreaCode AS AreaCode,PS.PoliceStationID,MAX(PS.PoliceStationName) AS AreaPoliceName,SUM(CASE U.GuildID WHEN 2 THEN 1 ELSE 0 END) AS HotelCount,SUM(CASE U.GuildID WHEN 3 THEN 1 ELSE 0 END) AS SchoolCount,SUM(CASE U.GuildID WHEN 4 THEN 1 ELSE 0 END) AS EnterpriseCount,SUM(CASE U.GuildID WHEN 5 THEN 1 ELSE 0 END) AS WifiUnitCount,SUM(CASE WHEN U.GuildID<>1 THEN 1 ELSE 0 END) AS AllUnitCount,SUM(CASE WHEN US.CurStatus=1 AND U.GuildID<>1 THEN 1 ELSE 0 END) AS OnlineUnit,SUM(CASE WHEN ISNULL(US.CurStatus,0)=0 AND U.GuildID<>1 THEN 1 ELSE 0 END) AS OfflineUnit,(CASE WHEN SUM(CASE WHEN U.GuildID<>1 THEN 1 ELSE 0 END)=0 THEN 0 ELSESUM(CASE WHEN US.CurStatus=1 AND U.GuildID<>1 THEN 1 ELSE 0 END)*1.00/SUM(CASE WHEN U.GuildID<>1 THEN 1 ELSE 0 END) END)*100 AS UnitOnlineRate,MAX(ISNULL(TC.OnlineCustomTotal,0)) AS OnlineCustomTotal,MAX(ISNULL(TA2.AlertCount,0)) AS AlertCountFROM #TempArea TAINNER JOIN dbo.NSMC_tb_PoliceStation PS WITH(NOLOCK)ON TA.AreaID = PS.AreaIDLEFT JOIN dbo.NSMC_re_UnitPoliceStation UPS WITH(NOLOCK)ON PS.PoliceStationID = UPS.PoliceStationIDLEFT JOIN dbo.NSMC_tb_Unit U WITH(NOLOCK)ON UPS.UnitID = U.UnitIDLEFT JOIN dbo.NSMC_re_UnitStatus US WITH(NOLOCK)ON U.UnitID = US.UnitIDLEFT JOIN #TempAlert TA2ON PS.PoliceStationID=TA2.PoliceStationIDLEFT JOIN #TempComp TC ON PS.PoliceStationID = TC.PoliceStationIDGROUP BY TA.AreaCode,PS.PoliceStationIDSELECT AreaID,AreaCode,PoliceStationID,AreaPoliceName ,HotelCount ,SchoolCount,EnterpriseCount,WifiUnitCount,AllUnitCount ,OnlineUnit ,OfflineUnit,UnitOnlineRate,OnlineCustomTotal ,AlertCount FROM #TempResult ORDER BY AreaCode,PoliceStationID ASCEND
