rep_daqjcrb

 

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