ALTER PROCEDURE dbo.yw_getSiteGroups_test(
@userId varchar(50),
@condition nvarchar(100),
@startPage int,
@pageSize int
)
as
BEGIN
if not exists(select * from tempdb..sysobjects where id=object_id('tempdb..#Temp_site_groups1'))
begin
--print '不存在临时表';
create table #Temp_site_groups1
(
groupNo int NOT NULL,
groupName nvarchar(50) not NULL,
total int not NULL,
exceptionState int not NULL,
inundate int not NULL,
offLine int not NULL,
siteOverflow int not NULL,
totalPercentage varchar(10) not NULL,
exceptionStatePercentage varchar(10) not NULL,
inundatePercentage varchar(10) not NULL,
offLinePercentage varchar(10) not NULL,
siteOverflowPercentage varchar(10) not NULL
)
end
truncate table #Temp_site_groups1;
DECLARE @level int;
DECLARE @userNo varchar(50);
SELECT @level = Level,@userNo = User_No from XF_Sys_User where id = @userId;
declare @sql1 varchar(1000);
if @level =1 BEGIN
declare groupCursor CURSOR FOR SELECT distinct s.d_Zn_name dZnName,s.id as parentId from Site_SiteGroups s
where s.emp_id = 29 and s.parentid = 0
AND s.d_Zn_name like concat('%',@condition,'%')
order by s.d_Zn_name offset @startPage row fetch next @pageSize row only
END
ELSE BEGIN
declare groupCursor CURSOR FOR SELECT distinct s.d_Zn_name dZnName,s.id as parentId from Site_SiteGroups s
where s.emp_id = 29 and s.parentid = 0
AND EXISTS (SELECT GroupNo FROM Group_User g where g.GroupNo = s.id and g.UserNo = @userNo )
AND s.d_Zn_name like concat('%',@condition,'%')
order by s.d_Zn_name offset @startPage row fetch next @pageSize row only
END
OPEN groupCursor
DECLARE @groupName nvarchar(10),@groupId varchar(50)
FETCH NEXT FROM groupCursor INTO @groupName,@groupId
while @@FETCH_STATUS=0
BEGIN
DECLARE @total int = 0,@exceptionState int = 0,@inundate int = 0,@offLine int = 0,@siteOverflow int = 0,@totalPercentage varchar(10) = '0%',@exceptionStatePercentage varchar(10) = '0%',@inundatePercentage varchar(10) = '0%',@offLinePercentage varchar(10) = '0%',@siteOverflowPercentage varchar(10) = '0%'
SELECT @total = count(1) from Site_SiteGroups s inner join TempData a on s.d_Name = a.ST where s.parentid = @groupId
declare siteDataCursor CURSOR FOR SELECT DISTINCT s.d_name,a.Z,a.SBL1,a.SBL2,a.TURB,a.TURB1,a.CODMN,
a.AI,a.MST,a.PH,a.NH4N,a.C,a.DO,a.COND,a.REDOX,a.VA,a.US,a.UC,a.SSM10,a.T10,a.ZT,a.TT,a.VT
from Site_SiteGroups s inner join TempData a on s.d_Name = a.ST
where s.parentid = @groupId order by a.TT desc
OPEN siteDataCursor
DECLARE @siteNo varchar(10),@z [numeric](10, 3),@sbl1 [numeric](10, 3),@sbl2 [numeric](10, 3),@turb [numeric](5, 1),@turb1 [numeric](10, 3),@codmn [numeric](10, 3),@ai [numeric](10, 3),@mst [numeric](10, 3),@ph [numeric](10, 3),@nh4n [numeric](10, 3),@c [numeric](10, 3),@do [numeric](10, 3),@cond [numeric](10, 3),@redox [numeric](10, 3),@va [numeric](10, 3),@us [numeric](10, 3),@uc [numeric](10, 3),@ssm10 [numeric](10, 3),@t10 [numeric](10, 3),@zt int,@tt datetime,@vt [numeric](4, 2)
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
while @@FETCH_STATUS=0
BEGIN
-- 掉线判断
DECLARE @diffMinutes int
set @diffMinutes = DATEDIFF(minute, @tt, GETDATE())
if(@groupId='2559' or @groupId = '8379') BEGIN
if @diffMinutes>= 68 begin
set @offLine = @offLine + 1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END
END
ELSE BEGIN
if @diffMinutes>= 180 begin
set @offLine = @offLine + 1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END
END
-- 水位 悬浮物 流量
if(@groupId = '6951' or @groupId = '9317' or @groupId = '9326' or @groupId = '9329' ) begin
DECLARE @alarmValue [numeric](10, 3)
SELECT @alarmValue = s.AlarmValue FROM Site_SiteAlarmConfig s where s.Serial = @siteNo
if(@z is not null) BEGIN
if(@z < 0) BEGIN
set @exceptionState = @exceptionState +1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END
else if (@alarmValue is not null) BEGIN
if @z > @alarmValue begin
set @siteOverflow = @siteOverflow +1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END
END
END
END
ELSE IF(@groupId = '11224') BEGIN
DECLARE @zPolice [numeric](10, 3),@zWarn [numeric](10, 3)
SELECT @zPolice = CONVERT(NUMERIC(10,3),zPolice),
@zWarn = CONVERT(NUMERIC(10,3),zWarn) FROM Water_Level_Warning where ST = @siteNo
if(@z is not null) BEGIN
if(@z < 0) BEGIN
set @exceptionState = @exceptionState +1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END
ELSE IF (@zPolice is not null) BEGIN
if @z >= @alarmValue or @z >= @zPolice begin
set @exceptionState = @exceptionState +1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END
END
END
END
ELSE BEGIN
IF @z is not null and @z < 0 BEGIN
set @exceptionState = @exceptionState +1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END
END
-- 流量异常
IF @sbl1 is not null and @sbl1 <= -99999999.99 BEGIN
set @exceptionState = @exceptionState +1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END
IF @sbl2 is not null and @sbl2 <= -99999999.99 BEGIN
set @exceptionState = @exceptionState +1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END
IF @turb is not null and @turb <= 0 BEGIN
set @exceptionState = @exceptionState +1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END
IF @turb1 is not null and @turb1 <= 0 BEGIN
set @exceptionState = @exceptionState +1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END
IF @codmn is not null and @codmn = -0.1 BEGIN
set @exceptionState = @exceptionState +1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END
IF @ai is not null and @ai = -99.9 BEGIN
set @exceptionState = @exceptionState +1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END
IF @mst is not null and @mst = -0.1 BEGIN
set @exceptionState = @exceptionState +1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END
IF @ph is not null and @ph = -0.1 BEGIN
set @exceptionState = @exceptionState +1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END
IF @nh4n is not null and @nh4n = -0.1 BEGIN
set @exceptionState = @exceptionState +1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END
IF @c is not null and @c = -99.9 BEGIN
set @exceptionState = @exceptionState +1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END
IF @do is not null and @do = -0.1 BEGIN
set @exceptionState = @exceptionState +1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END
IF @cond is not null and @cond = 99999.0 BEGIN
set @exceptionState = @exceptionState +1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END
IF @redox is not null and @redox = -0.1 BEGIN
set @exceptionState = @exceptionState +1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END
IF @va is not null and @va = -99.99900 BEGIN
set @exceptionState = @exceptionState +1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END
IF @us is not null and @us = -0.1 BEGIN
set @exceptionState = @exceptionState +1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END
IF @uc is not null and @uc = -99.00000 BEGIN
set @exceptionState = @exceptionState +1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END
IF @ssm10 is not null and @ssm10 = -9999.90000 BEGIN
set @exceptionState = @exceptionState +1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END
IF @zt is not null and (@zt >= 4096 and @zt < 8192) BEGIN
set @inundate = @inundate +1
FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
continue;
END

		FETCH NEXT FROM siteDataCursor INTO @siteNo,@z,@sbl1,@sbl2,@turb,@turb1,@codmn,@ai,@mst,@ph,@nh4n,@c,@do,@cond,@redox,@va,@us,@uc,@ssm10,@t10,@zt,@tt,@vt
	END
	IF(@total >0) BEGIN
		set @totalPercentage = '100%'
		set @exceptionStatePercentage =cast(cast(@exceptionState*100.0/@total as int) as varchar(10))+'%'
		set @inundatePercentage = cast(cast(@inundate*100.0/@total as int) as varchar(10))+'%'
		set @offLinePercentage = cast(cast(@offLine*100.0/@total as int) as varchar(10))+'%'
		set @siteOverflowPercentage = cast(cast(@siteOverflow*100.0/@total as int) as varchar(10))+'%'
	END
	INSERT INTO #Temp_site_groups1(groupNo,groupName,total,exceptionState,inundate,offLine,siteOverflow,totalPercentage,exceptionStatePercentage,inundatePercentage,offLinePercentage,siteOverflowPercentage)
		VALUES(@groupId,@groupName,@total,@exceptionState,@inundate,@offLine,@siteOverflow,@totalPercentage,@exceptionStatePercentage,@inundatePercentage,@offLinePercentage,@siteOverflowPercentage)
	CLOSE siteDataCursor
	DEALLOCATE siteDataCursor
	FETCH NEXT FROM groupCursor INTO @groupName,@groupId
END
CLOSE groupCursor
DEALLOCATE groupCursor
select * from #Temp_site_groups1

END