/*
  功能：统计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