set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: Dotte
-- Create date: 2010-8-11
-- Description: 统计XX客户使用情况
-- =============================================
ALTER PROCEDURE [dbo].[SP_CNIM_App_UseStatistic]
@startDate varchar(50)='1753/01/01 00:00:00',
@endDate varchar(50)='9999/12/31 23:59:59'
AS
BEGIN
--变量定义
declare @companyID nvarchar(20) --企业号
declare @strSql nvarchar(4000) --临时sql
declare @DataBaseName nvarchar(200) --数据库名
--1、遍历所有的用户
--定义游标
DECLARE company_cursor CURSOR FOR
SELECT CompanyID,DataBaseName FROM PF_CompanyAttachedInfo t1 ORDER BY CompanyID
--打开游标
OPEN company_cursor
--开始遍历
FETCH NEXT FROM company_cursor INTO @CompanyID,@DataBaseName
WHILE @@FETCH_STATUS = 0
BEGIN
--企业人数
set @strSql='UPDATE dbo.App_UseStatistic SET UserNum=( select count(1) from '+@DataBaseName+'.dbo.UserInfo where AccountState=1 ) '
--在线人数
set @strSql=@strSql+', OnlineNum=( select count(1) from (select num=count(1) from '+@DataBaseName+'.dbo.User_LogInfo where DateDiff(day,'''+@StartDate+''',EntryTime)>=0 and DateDiff(day,EntryTime,'''+@EndDate+''')>=0 group by LogUserID ) t1 ) '
--单聊消息数
set @strSql=@strSql+', SingleTalkMsgNum=( select count(1) from '+@DataBaseName+'.dbo.S_SingleTalkMsg where DateDiff(day,'''+@StartDate+''',EntryTime)>=0 and DateDiff(day,EntryTime,'''+@EndDate+''')>=0 ) '
--群聊消息数
set @strSql=@strSql+', GroupTalkMsgNum=( select count(1) from '+@DataBaseName+'.dbo.S_GroupTalkMsg where DateDiff(day,'''+@StartDate+''',EntryTime)>=0 and DateDiff(day,EntryTime,'''+@EndDate+''')>=0 ) '
--文件消息数
set @strSql=@strSql+', FileMsgNum=( select count(1) from '+@DataBaseName+'.dbo.S_FileMsg where DateDiff(day,'''+@StartDate+''',EntryTime)>=0 and DateDiff(day,EntryTime,'''+@EndDate+''')>=0 ) '
--系统消息数
set @strSql=@strSql+', SysMsgNum=( select count(1) from '+@DataBaseName+'.dbo.S_SysMsg where DateDiff(day,'''+@StartDate+''',EntryTime)>=0 and DateDiff(day,EntryTime,'''+@EndDate+''')>=0 ) '
--第一次使用时间
set @strSql=@strSql+', FirstUseDate=( select Top 1 LogTime from '+@DataBaseName+'.dbo.User_LogInfo order by LogID asc ) '
--最后一次使用时间
set @strSql=@strSql+', LastUseDate=( select Top 1 LogTime from '+@DataBaseName+'.dbo.User_LogInfo order by LogID desc ) '
--版本
set @strSql=@strSql+', ClientVersion=( select Top 1 Version from '+@DataBaseName+'.dbo.ClientVersion order by ID desc ) '
set @strSql=@strSql+' WHERE companyID= '+@CompanyID
set @strSql=@strSql+';';
--频道名
set @strSql=@strSql+'
declare @chanelNames nvarchar(1000);
set @chanelNames='''';
select @chanelNames=@chanelNames+chanelName+'','' from CNIM_APP.dbo.PF_Rel_ChanelCompany a
inner join CNIM_APP.dbo.PF_Chanel b on a.ChanelID=b.ChanelID
where a.companyID='+@CompanyID+';
update App_UseStatistic set ChanelName=@chanelNames where companyID='+@CompanyID
--print @strSql;
BEGIN TRY
exec(@strSql);
END TRY
BEGIN CATCH
--在这里添加错误处理语句
END CATCH
--移动游标
FETCH NEXT FROM company_cursor into @CompanyID,@DataBaseName
END --遍历结束
--关闭游标
CLOSE company_cursor
DEALLOCATE company_cursor
--频道数
set @strSql='Update App_UseStatistic set ChanelNum=( select count(1) from CNIM_APP.dbo.PF_Rel_ChanelCompany where companyID=App_UseStatistic.companyID ) '
exec(@strSql);
END