SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO -- 漳村煤矿安全监控日报表 2008-02-19 ALTER procedure rep_daqjcrb( @kjid int = 2 , @year int = 2008 , @month int = 1 , @day int = 25 ) as SET NOCOUNT ON create table #temp_dt( prow int PRIMARY KEY , t1 nvarchar ( 100 ),t2 real ,t3 real ,t4 nvarchar ( 20 ),t5 int ,t6 nvarchar ( 20 ),t7 int ,t8 nvarchar ( 20 ),t9 nvarchar ( 20 ), t10 nvarchar ( 100 ),t11 real ,t12 real ,t13 nvarchar ( 20 ),t14 int ,t15 nvarchar ( 20 ),t16 int ,t17 nvarchar ( 20 ),t18 nvarchar ( 20 ), t19 nvarchar ( 100 ),t20 real ,t21 real ,t22 nvarchar ( 20 ),t23 int ,t24 nvarchar ( 20 ), t25 nvarchar ( 100 ),t26 real ,t27 nvarchar ( 20 ),t28 nvarchar ( 20 ) ); declare @PTPROPCH4 int , @PTPROPCO int , @PTSTATEKD int , @PTSTATEBJ int , @PTSTATEDD int , @PTSTATEDD2 int ; -- KD 饋電; bj 報警;dd:斷電 declare @start datetime , @end datetime ; declare @rowmax int , @row1 int , @row2 int , @row3 int , @row4 int ; declare @ptid nvarchar ( 100 ), @ptplace nvarchar ( 100 ), @ptprop int , @cxcount int , @maxdata real , @smdata real , @pjdata real , @begintime datetime , @endtime datetime , @ptstate int ; declare @ptid2 nvarchar ( 100 ), @ptplace2 nvarchar ( 100 ), @ptprop2 int ; -- 上次循環測點 declare @maxdata2 real , @smdata2 real , @pjdata2 real , @pjdatasum real , @cxcountsum int , @maxdatatime datetime ; declare @bjcountsum int , @bjtimesum int , @ddcountsum int , @ddtimesum int , @kdcountsum int , @kdtimesum int , @kdtime datetime ; declare @difftime int ; -- 設置常量 set @PTPROPCH4 = 1 ; set @PTPROPCO = 4 ; set @PTSTATEKD = 4 ; set @PTSTATEBJ = 1 ; set @PTSTATEDD = 2 ; set @PTSTATEDD2 = 3 ; -- 初始化變量 set @rowmax = 0 ; set @row1 = 0 ; set @row2 = 0 ; set @row3 = 0 ; set @row4 = 0 ; set @bjcountsum = 0 ; set @bjtimesum = 0 ; set @ddcountsum = 0 ; set @ddtimesum = 0 ; set @kdcountsum = 0 ; set @kdtimesum = 0 ; set @ptid2 = '' ; -- 初始化日期 set @start = cast ( cast ( @year as varchar ) + ' - ' + cast ( @month as varchar ) + ' - ' + cast ( @day as varchar ) as datetime ); set @end = DATEADD ( day , 1 , @start ); -- 聲明遊標 declare myCursor cursor for select ptid,ptplace,ptprop,cxcount,maxdata,smdata,pjdata,begintime,endtime,ptstate from KJ95cxdt where kjid = @kjid and begintime >= @start and begintime <= @end and (ptprop = @PTPROPCH4 or ptprop = @PTPROPCO or ptstate = @PTSTATEKD ) and ptstate <= @PTSTATEKD order by ptid,begintime open myCursor ; FETCH NEXT FROM myCursor into @ptid , @ptplace , @ptprop , @cxcount , @maxdata , @smdata , @pjdata , @begintime , @endtime , @ptstate ; WHILE @@FETCH_STATUS = 0 begin -- 單次循環 set @difftime = datediff (millisecond, @begintime , @endtime ); -- 步驟1 處理上次測點 if @ptid2 <> @ptid begin -- 1、處理上個測點數據 if @ptid2 <> '' begin set @pjdata2 = @pjdatasum / @cxcountsum ; -- 饋電 if @kdcountsum > 0 begin if @row4 >= @rowmax begin set @rowmax = @rowmax + 1 ; set @row4 = @rowmax ; insert into #temp_dt(prow,t25,t26,t27,t28) values ( @rowmax , @ptplace2 , @kdcountsum , convert ( varchar , @kdtime , 108 ),dbo.f_TimeSpan2Var( @kdtimesum )); end else begin set @row4 = @row4 + 1 ; update #temp_dt set t25 = @ptplace2 ,t26 = @kdcountsum ,t27 = convert ( varchar , @kdtime , 108 ),t28 = dbo.f_TimeSpan2Var( @kdtimesum ) where prow = @row4 ; end end -- 根據測點類型判斷威瓦斯或者CO if @ptprop2 = @PTPROPCO -- CO begin if @row3 >= @rowmax begin set @rowmax = @rowmax + 1 ; set @row3 = @rowmax ; insert into #temp_dt(prow,t19,t20,t21,t22,t23,t24) values ( @rowmax , @ptplace2 , @maxdata2 , @pjdata2 , convert ( varchar , @maxdatatime , 108 ), @bjcountsum ,dbo.f_TimeSpan2Var( @bjtimesum )); end else begin set @row3 = @row3 + 1 ; update #temp_dt set t19 = @ptplace2 ,t20 = @maxdata2 ,t21 = @pjdata2 ,t22 = convert ( varchar , @maxdatatime , 108 ),t23 = @bjcountsum ,t24 = dbo.f_TimeSpan2Var( @bjtimesum ) where prow = @row3 ; end end else -- 瓦斯 begin if @row1 > @row2 begin -- 取用@row2 從t10到t18 if @row2 >= @rowmax begin set @rowmax = @rowmax + 1 ; set @row2 = @rowmax ; insert into #temp_dt(prow,t10,t11,t12,t13,t14,t15,t16,t17,t18) values ( @rowmax , @ptplace2 , @maxdata2 , @pjdata2 , convert ( varchar , @maxdatatime , 108 ), @bjcountsum ,dbo.f_TimeSpan2Var( @bjtimesum ), @ddcountsum ,dbo.f_TimeSpan2Var( @ddtimesum ), '' ); end else begin set @row2 = @row2 + 1 ; update #temp_dt set t10 = @ptplace2 ,t11 = @maxdata2 ,t12 = @pjdata2 ,t13 = convert ( varchar , @maxdatatime , 108 ),t14 = @bjcountsum , t15 = dbo.f_TimeSpan2Var( @bjtimesum ),t16 = @ddcountsum ,t17 = dbo.f_TimeSpan2Var( @ddtimesum ),t18 = '' where prow = @row2 ; end end else begin -- 取用@row1 從t1到t9 if @row1 >= @rowmax begin set @rowmax = @rowmax + 1 ; set @row1 = @rowmax ; insert into #temp_dt(prow,t1,t2,t3,t4,t5,t6,t7,t8,t9) values ( @rowmax , @ptplace2 , @maxdata2 , @pjdata2 , convert ( varchar , @maxdatatime , 108 ), @bjcountsum ,dbo.f_TimeSpan2Var( @bjtimesum ), @ddcountsum ,dbo.f_TimeSpan2Var( @ddtimesum ), '' ); end else begin set @row1 = @row1 + 1 ; update #temp_dt set t1 = @ptplace2 ,t2 = @maxdata2 ,t3 = @pjdata2 ,t4 = convert ( varchar , @maxdatatime , 108 ),t5 = @bjcountsum , t6 = dbo.f_TimeSpan2Var( @bjtimesum ),t7 = @ddcountsum ,t8 = dbo.f_TimeSpan2Var( @ddtimesum ),t9 = '' where prow = @row1 ; end end end end -- 2、設置測點編碼、名稱、類型 set @ptid2 = @ptid ; set @ptplace2 = @ptplace ; set @ptprop2 = @ptprop ; set @bjcountsum = 0 ; set @bjtimesum = 0 ; set @ddcountsum = 0 ; set @ddtimesum = 0 ; set @kdcountsum = 0 ; set @kdtimesum = 0 ; set @maxdata2 = 0 ; set @smdata2 = 0 ; set @pjdata2 = 0 ; set @pjdatasum = 0 ; set @cxcountsum = 0 ; set @maxdatatime = @begintime ; end -- 步驟2 處理饋電 if @ptstate = @PTSTATEKD begin set @kdcountsum = @kdcountsum + @cxcount ; set @kdtime = @begintime ; set @kdtimesum = @kdtimesum + @difftime ; end -- 步驟3 處理瓦斯或CO -- 平均值 set @pjdatasum = @pjdatasum + @pjdata * @cxcount ; set @cxcountsum = @cxcountsum + @cxcount ; -- 最大值 if @maxdata > @maxdata2 begin set @maxdata2 = @maxdata ; set @maxdatatime = @begintime ; end -- 報警 if @ptstate = @PTSTATEBJ begin set @bjcountsum = @bjcountsum + @cxcount ; set @bjtimesum = @bjtimesum + @difftime ; end -- 斷電 if @ptstate = @PTSTATEDD or @ptstate = @PTSTATEDD2 begin set @ddcountsum = @ddcountsum + @cxcount ; set @ddtimesum = @ddtimesum + @difftime ; end FETCH NEXT FROM myCursor into @ptid , @ptplace , @ptprop , @cxcount , @maxdata , @smdata , @pjdata , @begintime , @endtime , @ptstate ; end DEALLOCATE myCursor; -- 處理最後循環的測點 set @pjdata2 = @pjdatasum / @cxcountsum ; -- 饋電 if @kdcountsum > 0 begin if @row4 >= @rowmax begin set @rowmax = @rowmax + 1 ; set @row4 = @rowmax ; insert into #temp_dt(prow,t25,t26,t27,t28) values ( @rowmax , @ptplace2 , @kdcountsum , convert ( varchar , @kdtime , 108 ),dbo.f_TimeSpan2Var( @kdtimesum )); end else begin set @row4 = @row4 + 1 ; update #temp_dt set t25 = @ptplace2 ,t26 = @kdcountsum ,t27 = convert ( varchar , @kdtime , 108 ),t28 = dbo.f_TimeSpan2Var( @kdtimesum ) where prow = @row4 ; end end -- 根據測點類型判斷威瓦斯或者CO if @ptprop2 = @PTPROPCO -- CO begin if @row3 >= @rowmax begin set @rowmax = @rowmax + 1 ; set @row3 = @rowmax ; insert into #temp_dt(prow,t19,t20,t21,t22,t23,t24) values ( @rowmax , @ptplace2 , @maxdata2 , @pjdata2 , convert ( varchar , @maxdatatime , 108 ), @bjcountsum ,dbo.f_TimeSpan2Var( @bjtimesum )); end else begin set @row3 = @row3 + 1 ; update #temp_dt set t19 = @ptplace2 ,t20 = @maxdata2 ,t21 = @pjdata2 ,t22 = convert ( varchar , @maxdatatime , 108 ),t23 = @bjcountsum ,t24 = dbo.f_TimeSpan2Var( @bjtimesum ) where prow = @row3 ; end end else -- 瓦斯 begin if @row1 >= @row2 begin -- 取用@row2 從t10到t18 if @row2 >= @rowmax begin set @rowmax = @rowmax + 1 ; set @row2 = @rowmax ; insert into #temp_dt(prow,t10,t11,t12,t13,t14,t15,t16,t17,t18) values ( @rowmax , @ptplace2 , @maxdata2 , @pjdata2 , convert ( varchar , @maxdatatime , 108 ), @bjcountsum ,dbo.f_TimeSpan2Var( @bjtimesum ), @ddcountsum ,dbo.f_TimeSpan2Var( @ddtimesum ), '' ); end else begin set @row2 = @row2 + 1 ; update #temp_dt set t10 = @ptplace2 ,t11 = @maxdata2 ,t12 = @pjdata2 ,t13 = convert ( varchar , @maxdatatime , 108 ),t14 = @bjcountsum , t15 = dbo.f_TimeSpan2Var( @bjtimesum ),t16 = @ddcountsum ,t17 = dbo.f_TimeSpan2Var( @ddtimesum ),t18 = '' where prow = @row2 ; end end else begin -- 取用@row1 從t1到t9 if @row1 >= @rowmax begin set @rowmax = @rowmax + 1 ; set @row1 = @rowmax ; insert into #temp_dt(prow,t1,t2,t3,t4,t5,t6,t7,t8,t9) values ( @rowmax , @ptplace2 , @maxdata2 , @pjdata2 , convert ( varchar , @maxdatatime , 108 ), @bjcountsum ,dbo.f_TimeSpan2Var( @bjtimesum ), @ddcountsum ,dbo.f_TimeSpan2Var( @ddtimesum ), '' ); end else begin set @row1 = @row1 + 1 ; update #temp_dt set t1 = @ptplace2 ,t2 = @maxdata2 ,t3 = @pjdata2 ,t4 = convert ( varchar , @maxdatatime , 108 ),t5 = @bjcountsum , t6 = dbo.f_TimeSpan2Var( @bjtimesum ),t7 = @ddcountsum ,t8 = dbo.f_TimeSpan2Var( @ddtimesum ),t9 = '' where prow = @row1 ; end end end select * from #temp_dt; return ; GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO