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

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

/*  
  ?????Video??????????????  
  ???@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_Video_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 = LastVideoPVTotalTime from Sys_AVAPlatformSetting  
  end    
  else  
  begin  
    set @totaltime = '1990-01-01 00:00:00'    
  end 
  
  -- ??????
  delete from pub_pvhistory where PVTarget = 'Video' and pvguid not in (select guid from pub_video);
  
  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_VideoPV from Pub_VideoPV where 1=2  
    set @errorcount = @errorcount + @@ERROR  
      
    if (@ConnectionString <> '')   -- ????
    begin    
      set @sql = '
      INSERT INTO #Pub_VideoPV([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 = ''Video'' AND CreateTime > '''+convert(varchar,@totaltime,120)+
      ''' GROUP BY PVGuid  '
      print @sql
      exec(@sql)
      set @errorcount = @errorcount + @@ERROR      
    end
    else
    begin  -- ????
      INSERT INTO #Pub_VideoPV([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 = 'Video' AND CreateTime > @totaltime  
      GROUP BY PVGuid  
      set @errorcount = @errorcount + @@ERROR 
    end                
      
    set @curtime = GETDATE()  
      
    create index idx_temp on #Pub_VideoPV([Guid])   
    set @errorcount = @errorcount + @@ERROR  
      
    if @@ERROR <> 0  
    begin  
      goto err  
    end      
    begin transaction  
	  update Pub_VideoPV  
		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_VideoPV a, #Pub_VideoPV b  
	  where a.Guid = b.Guid  
	  set @errorcount = @errorcount + @@ERROR  
	       
	  delete #Pub_VideoPV from #Pub_VideoPV a, Pub_VideoPV b where a.guid = b.Guid  
	  set @errorcount = @errorcount + @@ERROR  
	       
	  insert into Pub_VideoPV([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_VideoPV  
	  set @errorcount = @errorcount + @@ERROR  
	    
	  UPDATE Pub_Video  
		 SET   
		  VideoView = ISNULL((SELECT TOP 1 AllViewCount FROM Pub_VideoPV WITH(NOLOCK) WHERE (Guid = Pub_Video.guid)) ,0),   
		  CommentCount = ISNULL((SELECT TOP 1 AllCommentCount FROM Pub_VideoPV WITH(NOLOCK) WHERE (Guid = Pub_Video.guid)), 0),   
		  CollectCount = ISNULL((SELECT TOP 1 AllCollectCount FROM Pub_VideoPV WITH(NOLOCK) WHERE (Guid = Pub_Video.guid)), 0)     
	  set @errorcount = @errorcount + @@ERROR  
	    
			UPDATE Pub_User SET VideoView =(SELECT ISNULL(SUM(VideoView), 0) AS UserVideoView FROM Pub_Video WITH(NOLOCK) WHERE (UserGuid = pub_user.guid))   
			set @errorcount = @errorcount + @@ERROR   
	       
	  if not exists(select 1 from Sys_AVAPlatformSetting)  
	  begin  
		set @guid = REPLACE(newid(),'-','')  
		insert into Sys_AVAPlatformSetting([guid],[Version],[LastVideoPVTotalTime])  
		values(@guid,1,@curtime)           
		set @errorcount = @errorcount + @@ERROR  
	  end  
	  else  
	  begin  
		update Sys_AVAPlatformSetting set LastVideoPVTotalTime = @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_VideoPV  
      set @errorcount = @errorcount + @@ERROR  
       
      SET @curtime = GETDATE()  
      
      if (@ConnectionString <> '')   -- ????
      begin
        set @sql = '
        INSERT INTO Pub_VideoPV([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 = ''Video'' AND CreateTime <= '''+convert(varchar,@curtime,120)+  
        ''' GROUP BY PVGuid '
		print @sql
        exec(@sql)
        set @errorcount = @errorcount + @@ERROR        
                
      end
      else  -- ????
      begin
        INSERT INTO Pub_VideoPV([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 = 'Video' AND CreateTime <= @curtime  
        GROUP BY PVGuid       
        set @errorcount = @errorcount + @@ERROR        
      end 
     
      UPDATE Pub_Video  
         SET   
          VideoView = ISNULL((SELECT TOP 1 AllViewCount FROM Pub_VideoPV WITH(NOLOCK) WHERE (Guid = Pub_Video.guid)) ,0),   
          CommentCount = ISNULL((SELECT TOP 1 AllCommentCount FROM Pub_VideoPV WITH(NOLOCK) WHERE (Guid = Pub_Video.guid)), 0),   
          CollectCount = ISNULL((SELECT TOP 1 AllCollectCount FROM Pub_VideoPV WITH(NOLOCK) WHERE (Guid = Pub_Video.guid)), 0)    
      set @errorcount = @errorcount + @@ERROR  
    
      UPDATE Pub_User SET VideoView =(SELECT ISNULL(SUM(VideoView), 0) AS UserVideoView FROM Pub_Video WITH(NOLOCK) WHERE (UserGuid = pub_user.guid))   
      set @errorcount = @errorcount + @@ERROR     
              
      if not exists(select 1 from Sys_AVAPlatformSetting)  
      begin  
        set @guid = REPLACE(newid(),'-','')  
        insert into Sys_AVAPlatformSetting([guid],[Version],[LastVideoPVTotalTime])  
        values(@guid,1,@curtime)     
        set @errorcount = @errorcount + @@ERROR        
      end  
      else  
      begin  
        update Sys_AVAPlatformSetting set LastVideoPVTotalTime = @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