Edit C:\Users\Administrator\Desktop\SQL20150830-20160530\15.08.13.24_0002_PV_Application_Total

Edit C:\Users\Administrator\Desktop\SQL20150830-20160530\15.08.13.24_0002_PV_Application_Total_Upd.sql

/*
  ?????Application??????????????
  ???@reTotal  bit   0:??????????  1:?????? 
        @ConnectionString  nvarchar(255)  ????Pub_PVHistory?????????????
         Data Source=192.168.2.25;database=AVAResourcesPlatformPV;Integrated Security=false; user id=sa;password=xxxxxxxx;
  ?????
      Create by qjz at 2015-04-10
      ??????  Modify By Qin at 2015-08-06
      ???????? Modify By Qin at 2015-08-13
*/
ALTER procedure [dbo].[PV_Application_Total]
(
  @reTotal bit = 0,
  @ConnectionString nvarchar(255) = ''
)
as
begin
  declare @totaltime datetime
  declare @curtime datetime
  declare @guid varchar(32)
  declare @errorcount int
  
  declare @sql varchar(8000)

set @errorcount = 0
  
  if exists(select 1 from Sys_AVAPlatformSetting)
  begin
    select @totaltime = LastApplicationPVTotalTime from Sys_AVAPlatformSetting
  end  
  else
  begin
    set @totaltime = '1990-01-01 00:00:00'  
  end  
  
  -- ??????
  delete from pub_pvhistory where PVTarget = 'Application' and pvguid not in (select guid from Pub_Application);
  
  if DATEDIFF(day,@totaltime,getdate()) > 0  -- ???????????????????????????????????
  begin
    set @reTotal = 1
  end
  
  set @ConnectionString = isnull(@ConnectionString,'')
  set @ConnectionString = replace(@ConnectionString,'Integrated Security=false;','') -- ??????

if (@ConnectionString <> '')   -- ????
  begin
    exec sp_configure 'show advanced options',1
    reconfigure
    exec sp_configure 'Ad Hoc Distributed Queries',1
    reconfigure    
  end     
  
  if @reTotal = 0  -- ??????????
  begin      
    select * into #Pub_ApplicationPV from Pub_ApplicationPV where 1=2
    set @errorcount = @errorcount + @@ERROR
    
    if (@ConnectionString <> '')   -- ????
    begin  
      set @sql ='   
      INSERT INTO #Pub_ApplicationPV([Guid],[Version],[WeekViewCount],[MoonViewCount],[DayViewCount],[YearViewCount],[AllViewCount],[DayCommentCount],[WeekCommentCount],[MoonCommentCount],[YearCommentCount],[AllCommentCount],[DayCollectCount],[WeekCollectCount],[MoonCollectCount],[YearCollectCount],[AllCollectCount])
      SELECT PVGuid, 
             1 AS Version, 
             SUM(ISNULL(CASE WHEN DATEDIFF(day, CreateTime, GETDATE()) <= 7 AND PVTYPE = 1 THEN 1 END,0)) AS WeekViewCount, 
             SUM(ISNULL(CASE WHEN DATEDIFF(month, CreateTime, GETDATE()) = 0 AND PVTYPE = 1 THEN 1 END,0)) AS MoonViewCount, 
             SUM(ISNULL(CASE WHEN DATEDIFF(day, CreateTime, GETDATE()) = 0 AND PVTYPE = 1 THEN 1 END,0)) AS DayViewCount, 
             SUM(ISNULL(CASE WHEN DATEDIFF(Year, CreateTime, GETDATE()) = 0 AND PVTYPE = 1 THEN 1 END,0)) AS YearViewCount, 
             SUM(ISNULL(CASE WHEN PVTYPE = 1 THEN 1 END,0)) AS AllViewCount,              
             SUM(ISNULL(CASE WHEN DATEDIFF(day, CreateTime, GETDATE()) = 0 AND PVTYPE = 2 THEN 1 END,0)) AS DayCommentCount, 
             SUM(ISNULL(CASE WHEN DATEDIFF(day, CreateTime, GETDATE()) <= 7 AND PVTYPE = 2 THEN 1 END,0)) AS WeekCommentCount, 
             SUM(ISNULL(CASE WHEN DATEDIFF(month, CreateTime, GETDATE()) = 0 AND PVTYPE = 2 THEN 1 END,0)) AS MoonCommentCount, 
             SUM(ISNULL(CASE WHEN DATEDIFF(Year, CreateTime, GETDATE()) = 0 AND PVTYPE = 2 THEN 1 END,0)) AS YearCommentCount, 
             SUM(ISNULL(CASE WHEN PVTYPE = 2 THEN 1 END,0)) AS AllCommentCount, 
             SUM(ISNULL(CASE WHEN DATEDIFF(day, CreateTime, GETDATE()) = 0 AND PVTYPE = 3 THEN 1 END,0)) AS DayCollectCount, 
             SUM(ISNULL(CASE WHEN DATEDIFF(day, CreateTime, GETDATE()) <= 7 AND PVTYPE = 3 THEN 1 END,0)) AS WeekCollectCount, 
             SUM(ISNULL(CASE WHEN DATEDIFF(month, CreateTime, GETDATE()) = 0 AND PVTYPE = 3 THEN 1 END,0)) AS MoonCollectCount, 
             SUM(ISNULL(CASE WHEN DATEDIFF(Year, CreateTime, GETDATE()) = 0 AND PVTYPE = 3 THEN 1 END,0)) AS YearCollectCount, 
             SUM(ISNULL(CASE WHEN PVTYPE = 3 THEN 1 END,0)) AS AllCollectCount 
      FROM OPENDATASOURCE(''SQLOLEDB'','''+@ConnectionString+''').AVAResourcesPlatformPV.dbo.Pub_PVHistory
      WHERE PVTarget = ''Application'' AND CreateTime > '''+convert(varchar,@totaltime,120)+
      ''' GROUP BY PVGuid'
      print @sql
      exec(@sql)
      set @errorcount = @errorcount + @@ERROR    
    end
    else
    begin  -- ????
      INSERT INTO #Pub_ApplicationPV([Guid],[Version],[WeekViewCount],[MoonViewCount],[DayViewCount],[YearViewCount],[AllViewCount],[DayCommentCount],[WeekCommentCount],[MoonCommentCount],[YearCommentCount],[AllCommentCount],[DayCollectCount],[WeekCollectCount],[MoonCollectCount],[YearCollectCount],[AllCollectCount])
      SELECT PVGuid, 
             1 AS Version, 
             SUM(ISNULL(CASE WHEN DATEDIFF(day, CreateTime, GETDATE()) <= 7 AND PVTYPE = 1 THEN 1 END,0)) AS WeekViewCount, 
             SUM(ISNULL(CASE WHEN DATEDIFF(month, CreateTime, GETDATE()) = 0 AND PVTYPE = 1 THEN 1 END,0)) AS MoonViewCount, 
             SUM(ISNULL(CASE WHEN DATEDIFF(day, CreateTime, GETDATE()) = 0 AND PVTYPE = 1 THEN 1 END,0)) AS DayViewCount, 
             SUM(ISNULL(CASE WHEN DATEDIFF(Year, CreateTime, GETDATE()) = 0 AND PVTYPE = 1 THEN 1 END,0)) AS YearViewCount, 
             SUM(ISNULL(CASE WHEN PVTYPE = 1 THEN 1 END,0)) AS AllViewCount,              
             SUM(ISNULL(CASE WHEN DATEDIFF(day, CreateTime, GETDATE()) = 0 AND PVTYPE = 2 THEN 1 END,0)) AS DayCommentCount, 
             SUM(ISNULL(CASE WHEN DATEDIFF(day, CreateTime, GETDATE()) <= 7 AND PVTYPE = 2 THEN 1 END,0)) AS WeekCommentCount, 
             SUM(ISNULL(CASE WHEN DATEDIFF(month, CreateTime, GETDATE()) = 0 AND PVTYPE = 2 THEN 1 END,0)) AS MoonCommentCount, 
             SUM(ISNULL(CASE WHEN DATEDIFF(Year, CreateTime, GETDATE()) = 0 AND PVTYPE = 2 THEN 1 END,0)) AS YearCommentCount, 
             SUM(ISNULL(CASE WHEN PVTYPE = 2 THEN 1 END,0)) AS AllCommentCount, 
             SUM(ISNULL(CASE WHEN DATEDIFF(day, CreateTime, GETDATE()) = 0 AND PVTYPE = 3 THEN 1 END,0)) AS DayCollectCount, 
             SUM(ISNULL(CASE WHEN DATEDIFF(day, CreateTime, GETDATE()) <= 7 AND PVTYPE = 3 THEN 1 END,0)) AS WeekCollectCount, 
             SUM(ISNULL(CASE WHEN DATEDIFF(month, CreateTime, GETDATE()) = 0 AND PVTYPE = 3 THEN 1 END,0)) AS MoonCollectCount, 
             SUM(ISNULL(CASE WHEN DATEDIFF(Year, CreateTime, GETDATE()) = 0 AND PVTYPE = 3 THEN 1 END,0)) AS YearCollectCount, 
             SUM(ISNULL(CASE WHEN PVTYPE = 3 THEN 1 END,0)) AS AllCollectCount 
      FROM Pub_PVHistory 
      WHERE PVTarget = 'Application' AND CreateTime > @totaltime
      GROUP BY PVGuid
      set @errorcount = @errorcount + @@ERROR     
    end  
    
    set @curtime = GETDATE()
    
    create index idx_temp on #Pub_ApplicationPV([Guid]) 
    set @errorcount = @errorcount + @@ERROR
    
    if @@ERROR <> 0
    begin
      goto err
    end    
    begin transaction
		update Pub_ApplicationPV
		  set WeekViewCount = a.WeekViewCount + b.WeekViewCount,
			  MoonViewCount = a.MoonViewCount + b.MoonViewCount,
			  DayViewCount = a.DayViewCount + b.DayViewCount,
			  YearViewCount = a.YearViewCount + b.YearViewCount,
			  AllViewCount = a.AllViewCount + b.AllViewCount,
	          
			  DayCommentCount = a.DayCommentCount + b.DayCommentCount,
			  WeekCommentCount = a.WeekCommentCount + b.WeekCommentCount,
			  MoonCommentCount = a.MoonCommentCount + b.MoonCommentCount,
			  YearCommentCount = a.YearCommentCount + b.YearCommentCount,
			  AllCommentCount = a.AllCommentCount + b.AllCommentCount,
	          
			  DayCollectCount = a.DayCollectCount + b.DayCollectCount,
			  WeekCollectCount = a.WeekCollectCount + b.WeekCollectCount,
			  MoonCollectCount = a.MoonCollectCount + b.MoonCollectCount,
			  YearCollectCount = a.YearCollectCount + b.YearCollectCount, 
			  AllCollectCount = a.AllCollectCount + b.AllCollectCount
			     
		from Pub_ApplicationPV a, #Pub_ApplicationPV b
		where a.Guid = b.Guid
		set @errorcount = @errorcount + @@ERROR
	    
		delete #Pub_ApplicationPV from #Pub_ApplicationPV a, Pub_ApplicationPV b where a.guid = b.Guid
		set @errorcount = @errorcount + @@ERROR
	    
		insert into Pub_ApplicationPV([Guid],[Version],[WeekViewCount],[MoonViewCount],[DayViewCount],[YearViewCount],[AllViewCount],[DayCommentCount],[WeekCommentCount],[MoonCommentCount],[YearCommentCount],[AllCommentCount],[DayCollectCount],[WeekCollectCount],[MoonCollectCount],[YearCollectCount],[AllCollectCount])
		select [Guid],[Version],[WeekViewCount],[MoonViewCount],[DayViewCount],[YearViewCount],[AllViewCount],[DayCommentCount],[WeekCommentCount],[MoonCommentCount],[YearCommentCount],[AllCommentCount],[DayCollectCount],[WeekCollectCount],[MoonCollectCount],[YearCollectCount],[AllCollectCount]
		from #Pub_ApplicationPV
		set @errorcount = @errorcount + @@ERROR
		
		UPDATE Pub_Application
		   SET 
			   VideoView = ISNULL((SELECT TOP 1 AllViewCount FROM Pub_ApplicationPV WITH(NOLOCK) WHERE (Guid = Pub_Application.guid)) ,0), 
			   CommentCount = ISNULL((SELECT TOP 1 AllCommentCount FROM Pub_ApplicationPV WITH(NOLOCK) WHERE (Guid = Pub_Application.guid)), 0), 
			   CollectCount = ISNULL((SELECT TOP 1 AllCollectCount FROM Pub_ApplicationPV WITH(NOLOCK) WHERE (Guid = Pub_Application.guid)), 0)			
		set @errorcount = @errorcount + @@ERROR
	    
		if not exists(select 1 from Sys_AVAPlatformSetting)
		begin
		  set @guid = REPLACE(newid(),'-','')
		  insert into Sys_AVAPlatformSetting([guid],[Version],[LastApplicationPVTotalTime])
		  values(@guid,1,@curtime)         
		  set @errorcount = @errorcount + @@ERROR
		end
		else
		begin
		  update Sys_AVAPlatformSetting set LastApplicationPVTotalTime = @curtime
		  set @errorcount = @errorcount + @@ERROR
		end 
			
		if @errorcount <> 0
		begin
		  goto trans_err
		end
	commit transaction
    if (@ConnectionString <> '')   -- ????
    begin
      exec sp_configure 'Ad Hoc Distributed Queries',0
      reconfigure
      exec sp_configure 'show advanced options',0
      reconfigure   
    end
	return 0	        
  end
  else
  begin  --  ??????   
    begin transaction   
		DELETE Pub_ApplicationPV
		set @errorcount = @errorcount + @@ERROR
	    
		SET @curtime = GETDATE()
	    
	    if (@ConnectionString <> '')   -- ????
	    begin
	      set @sql = '
		  INSERT INTO Pub_ApplicationPV([Guid],[Version],[WeekViewCount],[MoonViewCount],[DayViewCount],[YearViewCount],[AllViewCount],[DayCommentCount],[WeekCommentCount],[MoonCommentCount],[YearCommentCount],[AllCommentCount],[DayCollectCount],[WeekCollectCount],[MoonCollectCount],[YearCollectCount],[AllCollectCount])
		  SELECT PVGuid, 
		       1 AS Version, 
			   SUM(ISNULL(CASE WHEN DATEDIFF(day, CreateTime, GETDATE()) <= 7 AND PVTYPE = 1 THEN 1 END,0)) AS WeekViewCount, 
			   SUM(ISNULL(CASE WHEN DATEDIFF(month, CreateTime, GETDATE()) = 0 AND PVTYPE = 1 THEN 1 END,0)) AS MoonViewCount, 
			   SUM(ISNULL(CASE WHEN DATEDIFF(day, CreateTime, GETDATE()) = 0 AND PVTYPE = 1 THEN 1 END,0)) AS DayViewCount, 
			   SUM(ISNULL(CASE WHEN DATEDIFF(Year, CreateTime, GETDATE()) = 0 AND PVTYPE = 1 THEN 1 END,0)) AS YearViewCount, 
			   SUM(ISNULL(CASE WHEN PVTYPE = 1 THEN 1 END,0)) AS AllViewCount,              
			   SUM(ISNULL(CASE WHEN DATEDIFF(day, CreateTime, GETDATE()) = 0 AND PVTYPE = 2 THEN 1 END,0)) AS DayCommentCount, 
			   SUM(ISNULL(CASE WHEN DATEDIFF(day, CreateTime, GETDATE()) <= 7 AND PVTYPE = 2 THEN 1 END,0)) AS WeekCommentCount, 
			   SUM(ISNULL(CASE WHEN DATEDIFF(month, CreateTime, GETDATE()) = 0 AND PVTYPE = 2 THEN 1 END,0)) AS MoonCommentCount, 
			   SUM(ISNULL(CASE WHEN DATEDIFF(Year, CreateTime, GETDATE()) = 0 AND PVTYPE = 2 THEN 1 END,0)) AS YearCommentCount, 
			   SUM(ISNULL(CASE WHEN PVTYPE = 2 THEN 1 END,0)) AS AllCommentCount, 
			   SUM(ISNULL(CASE WHEN DATEDIFF(day, CreateTime, GETDATE()) = 0 AND PVTYPE = 3 THEN 1 END,0)) AS DayCollectCount, 
			   SUM(ISNULL(CASE WHEN DATEDIFF(day, CreateTime, GETDATE()) <= 7 AND PVTYPE = 3 THEN 1 END,0)) AS WeekCollectCount, 
			   SUM(ISNULL(CASE WHEN DATEDIFF(month, CreateTime, GETDATE()) = 0 AND PVTYPE = 3 THEN 1 END,0)) AS MoonCollectCount, 
			   SUM(ISNULL(CASE WHEN DATEDIFF(Year, CreateTime, GETDATE()) = 0 AND PVTYPE = 3 THEN 1 END,0)) AS YearCollectCount, 
			   SUM(ISNULL(CASE WHEN PVTYPE = 3 THEN 1 END,0)) AS AllCollectCount 
		  FROM OPENDATASOURCE(''SQLOLEDB'','''+@ConnectionString+''').AVAResourcesPlatformPV.dbo.Pub_PVHistory
		  WHERE PVTarget = ''Application'' AND CreateTime <= '''+convert(varchar,@curtime,120)+
		  ''' GROUP BY PVGuid '    
		  print @sql
          exec(@sql)
		  set @errorcount = @errorcount + @@ERROR
		end
		else
		begin
		  INSERT INTO Pub_ApplicationPV([Guid],[Version],[WeekViewCount],[MoonViewCount],[DayViewCount],[YearViewCount],[AllViewCount],[DayCommentCount],[WeekCommentCount],[MoonCommentCount],[YearCommentCount],[AllCommentCount],[DayCollectCount],[WeekCollectCount],[MoonCollectCount],[YearCollectCount],[AllCollectCount])
		  SELECT PVGuid, 
		       1 AS Version, 
			   SUM(ISNULL(CASE WHEN DATEDIFF(day, CreateTime, GETDATE()) <= 7 AND PVTYPE = 1 THEN 1 END,0)) AS WeekViewCount, 
			   SUM(ISNULL(CASE WHEN DATEDIFF(month, CreateTime, GETDATE()) = 0 AND PVTYPE = 1 THEN 1 END,0)) AS MoonViewCount, 
			   SUM(ISNULL(CASE WHEN DATEDIFF(day, CreateTime, GETDATE()) = 0 AND PVTYPE = 1 THEN 1 END,0)) AS DayViewCount, 
			   SUM(ISNULL(CASE WHEN DATEDIFF(Year, CreateTime, GETDATE()) = 0 AND PVTYPE = 1 THEN 1 END,0)) AS YearViewCount, 
			   SUM(ISNULL(CASE WHEN PVTYPE = 1 THEN 1 END,0)) AS AllViewCount,              
			   SUM(ISNULL(CASE WHEN DATEDIFF(day, CreateTime, GETDATE()) = 0 AND PVTYPE = 2 THEN 1 END,0)) AS DayCommentCount, 
			   SUM(ISNULL(CASE WHEN DATEDIFF(day, CreateTime, GETDATE()) <= 7 AND PVTYPE = 2 THEN 1 END,0)) AS WeekCommentCount, 
			   SUM(ISNULL(CASE WHEN DATEDIFF(month, CreateTime, GETDATE()) = 0 AND PVTYPE = 2 THEN 1 END,0)) AS MoonCommentCount, 
			   SUM(ISNULL(CASE WHEN DATEDIFF(Year, CreateTime, GETDATE()) = 0 AND PVTYPE = 2 THEN 1 END,0)) AS YearCommentCount, 
			   SUM(ISNULL(CASE WHEN PVTYPE = 2 THEN 1 END,0)) AS AllCommentCount, 
			   SUM(ISNULL(CASE WHEN DATEDIFF(day, CreateTime, GETDATE()) = 0 AND PVTYPE = 3 THEN 1 END,0)) AS DayCollectCount, 
			   SUM(ISNULL(CASE WHEN DATEDIFF(day, CreateTime, GETDATE()) <= 7 AND PVTYPE = 3 THEN 1 END,0)) AS WeekCollectCount, 
			   SUM(ISNULL(CASE WHEN DATEDIFF(month, CreateTime, GETDATE()) = 0 AND PVTYPE = 3 THEN 1 END,0)) AS MoonCollectCount, 
			   SUM(ISNULL(CASE WHEN DATEDIFF(Year, CreateTime, GETDATE()) = 0 AND PVTYPE = 3 THEN 1 END,0)) AS YearCollectCount, 
			   SUM(ISNULL(CASE WHEN PVTYPE = 3 THEN 1 END,0)) AS AllCollectCount 
		  FROM Pub_PVHistory 
		  WHERE PVTarget = 'Application' AND CreateTime <= @curtime
		  GROUP BY PVGuid     
		  set @errorcount = @errorcount + @@ERROR		
		end
	  
		UPDATE Pub_Application
		   SET 
			   VideoView = ISNULL((SELECT TOP 1 AllViewCount FROM Pub_ApplicationPV WITH(NOLOCK) WHERE (Guid = Pub_Application.guid)) ,0), 
			   CommentCount = ISNULL((SELECT TOP 1 AllCommentCount FROM Pub_ApplicationPV WITH(NOLOCK) WHERE (Guid = Pub_Application.guid)), 0), 
			   CollectCount = ISNULL((SELECT TOP 1 AllCollectCount FROM Pub_ApplicationPV WITH(NOLOCK) WHERE (Guid = Pub_Application.guid)), 0)  
		set @errorcount = @errorcount + @@ERROR
	           
		if not exists(select 1 from Sys_AVAPlatformSetting)
		begin
		  set @guid = REPLACE(newid(),'-','')
		  insert into Sys_AVAPlatformSetting([guid],[Version],[LastApplicationPVTotalTime])
		  values(@guid,1,@curtime)   
		  set @errorcount = @errorcount + @@ERROR      
		end
		else
		begin
		  update Sys_AVAPlatformSetting set LastApplicationPVTotalTime = @curtime
		  set @errorcount = @errorcount + @@ERROR
		end       
		
		if @errorcount <> 0
		begin
		  goto trans_err
		end
		    
    commit transaction
    if (@ConnectionString <> '')   -- ????
    begin
      exec sp_configure 'Ad Hoc Distributed Queries',0
      reconfigure
      exec sp_configure 'show advanced options',0
      reconfigure   
    end
    return 0
  end
  
  err:
    if (@ConnectionString <> '')   -- ????
    begin
      exec sp_configure 'Ad Hoc Distributed Queries',0
      reconfigure
      exec sp_configure 'show advanced options',0
      reconfigure   
    end
    raiserror 88888 '????'
    return -1
  
  trans_err:
    if (@ConnectionString <> '')   -- ????
    begin
      exec sp_configure 'Ad Hoc Distributed Queries',0
      reconfigure
      exec sp_configure 'show advanced options',0
      reconfigure   
    end
    rollback transaction
    raiserror 88888 '????'
    return -1
end

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de