/// 创建统计sql=坐席状态
/// <summary>
/// 创建统计sql=坐席状态
/// </summary>
/// <param name="type"></param>
/// <param name="tmp"></param>
/// <returns></returns>
private string CreateAgentStatetatSql(ReportTempType type, string tmp)
{
string groupby = "";
if (type == ReportTempType.Hour)
{
groupby = "StatDate,Hour,AgentUserID";
}
else if (type == ReportTempType.Day)
{
groupby = "StatDate,AgentUserID";
}
else
{
return("");
}
string sql = @"SELECT " + groupby + @",
SUM(is_qianru) AS N_SignIn ,-- 签入次数
SUM(is_qianchu) AS N_SignOut ,-- 签出次数
SUM(is_zhixian) AS N_Free ,-- 置闲次数
SUM(is_zhimang) AS N_Busy ,-- 置忙次数
SUM(is_busy0) AS N_Busy_0 ,-- 置忙-自动次数
SUM(is_busy1) AS N_Busy_1 ,-- 置忙-小休次数
SUM(is_busy2) AS N_Busy_2 ,-- 置忙-任务回访次数
SUM(is_busy3) AS N_Busy_3 ,-- 置忙-业务处理次数
SUM(is_busy4) AS N_Busy_4 ,-- 置忙-会议次数
SUM(is_busy5) AS N_Busy_5 ,-- 置忙-培训次数
SUM(is_busy6) AS N_Busy_6 ,-- 置忙-离席次数
SUM(t_qianru) AS T_SignIn ,-- 签入时长
NULL AS T_SignOut ,-- 签出时长
SUM(t_zhixian) AS T_Free ,-- 置闲时长
SUM(t_zhimang) AS T_Busy ,-- 置忙时长
SUM(t_busy0) AS T_Busy_0 ,-- 置忙-自动时长
SUM(t_busy1) AS T_Busy_1 ,-- 置忙-小休时长
SUM(t_busy2) AS T_Busy_2 ,-- 置忙-任务回访时长
SUM(t_busy3) AS T_Busy_3 ,-- 置忙-业务处理时长
SUM(t_busy4) AS T_Busy_4 ,-- 置忙-会议时长
SUM(t_busy5) AS T_Busy_5 ,-- 置忙-培训时长
SUM(t_busy6) AS T_Busy_6 ,-- 置忙-离席时长
GETDATE() AS CreateTime
INTO " + tmp + @"
FROM #tmp_state
GROUP BY " + groupby + @"
ORDER BY " + groupby;
return(sql);
}
/// 创建统计sql=话务
/// <summary>
/// 创建统计sql=话务
/// </summary>
/// <param name="type"></param>
/// <param name="tmp"></param>
/// <returns></returns>
private string CreateCallRecordStatSql(ReportTempType type, string tmp)
{
string groupby = "";
if (type == ReportTempType.Hour)
{
groupby = "StatDate,Hour,BGID,AgentUserID,CallDirection,CallType";
}
else if (type == ReportTempType.Day)
{
groupby = "StatDate,BGID,AgentUserID,CallDirection,CallType";
}
else
{
return("");
}
string sql = @"SELECT " + groupby + @",
COUNT(*) AS N_Total,--电话总量
SUM(ISEstablished) AS N_ETotal,--接通总量
COUNT(*)-SUM(ISEstablished) AS N_NoETotal ,-- 未接通总量
SUM(ISTransferIn) AS N_TransferIn ,-- 转入总量
SUM(ISTransferOut) AS N_TransferOut ,-- 转出总量
SUM(CASE ISEstablished WHEN 1 THEN T_Ringing ELSE 0 END) AS T_ERinging ,-- 接通振铃时长
SUM(CASE ISEstablished WHEN 0 THEN T_Ringing ELSE 0 END) AS T_NoERinging ,-- 未接通振铃时长
SUM(T_Ringing) AS T_Ringing ,-- 总振铃时长
SUM(T_Talk) AS T_Talk ,-- 总通话时长
SUM(AfterWorkTime) AS T_AfterWork ,-- 总话后时长
SUM(T_Ringing) + SUM(T_Talk) + SUM(AfterWorkTime) AS T_ALL ,-- 总时长=（总振铃+总通话+总话后）
GETDATE() AS CreateTime
INTO " + tmp + @"
FROM #tmp_call
GROUP BY " + groupby + @"
ORDER BY " + groupby;
return(sql);
}
/// 创建话务统计临时表
/// <summary>
/// 创建话务统计临时表
/// </summary>
/// <param name="conn"></param>
/// <param name="date"></param>
/// <param name="tableEndName"></param>
/// <param name="tmp_hour"></param>
/// <param name="tmp_day"></param>
public Dictionary <ReportTempType, string> CreateReportCallRecordStatForDayTmp(SqlConnection conn, DateTime date, ReportTempType tmptype, string tableendname, out string msg)
{
#region 创建临时表
string tmpsql = @"SELECT * ,
CASE WHEN ISNULL(EstablishedTime,'1970-1-1')>'1970-1-1' THEN 1 ELSE 0 END AS ISEstablished,--接通
CASE WHEN ISNULL(TransferInTime,'1970-1-1')>'1970-1-1' THEN 1 ELSE 0 END AS ISTransferIn,--转入
CASE WHEN ISNULL(TransferOutTime,'1970-1-1')>'1970-1-1' THEN 1 ELSE 0 END AS ISTransferOut,--转出
CASE WHEN ISNULL(EstablishedTime,'1970-1-1')>'1970-1-1' THEN (DATEDIFF(SECOND,RingingTime,EstablishedTime)) ELSE (DATEDIFF(SECOND,RingingTime,ReleaseTime)) END AS T_Ringing,--振铃时长
CASE WHEN ISNULL(EstablishedTime,'1970-1-1')>'1970-1-1' THEN (DATEDIFF(SECOND,EstablishedTime,ReleaseTime)) ELSE (0) END AS T_Talk--通话时长
INTO #tmp_call
FROM
(
SELECT
--主要字段
a.CreateTime,a.CreateUserID as AgentUserID,
CAST(a.CreateTime AS DATE) AS StatDate,
DATENAME(HOUR,a.CreateTime) AS [Hour],
CASE WHEN ISNULL(b.BGID,0)>0 THEN ISNULL(b.BGID,0) ELSE ISNULL(c.BGID,0) END AS BGID,
CASE a.CallStatus
WHEN 1 THEN ISNULL(d.CDID,0)
WHEN 2 THEN (CASE a.OutBoundType WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 4 THEN 3 ELSE 1 END)
WHEN 3 THEN ISNULL(d.CDID,0)
ELSE -1 END AS CallType,
a.CallStatus AS CallDirection,
--时间字段
a.InitiatedTime,a.RingingTime,a.EstablishedTime,ISNULL(a.AgentReleaseTime,a.CustomerReleaseTime) AS ReleaseTime,
a.AfterWorkBeginTime,a.AfterWorkTime,a.ConsultTime,a.ReconnectCall,a.TransferInTime,a.TransferOutTime
FROM dbo.CallRecord_ORIG" + tableendname + @" a
LEFT JOIN dbo.CallRecord_ORIG_Business" + tableendname + @" b ON a.CallID=b.CallID
LEFT JOIN dbo.EmployeeAgent c ON a.CreateUserID=c.UserID
LEFT JOIN dbo.CallDisplay d ON a.SwitchINNum=d.AreaCode+d.TelMainNum
WHERE a.CreateTime>='" + date.ToString("yyyy-MM-dd") + @" 00:00:00' AND a.CreateTime<='" + date.ToString("yyyy-MM-dd") + @" 23:59:59'
) tmp
WHERE tmp.BGID>0 AND tmp.CallType>0
AND ISNULL(InitiatedTime,'1970-1-1')>'1970-1-1'
AND ISNULL(RingingTime,'1970-1-1')>'1970-1-1'
AND ISNULL(ReleaseTime,'1970-1-1')>'1970-1-1'";
int a = SqlHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, tmpsql);
msg = "查询一天的数据量=" + a + ";";
#endregion
Dictionary <ReportTempType, string> list = new Dictionary <ReportTempType, string>();
if (tmptype == ReportTempType.Hour || tmptype == ReportTempType.All)
{
string tmp = "#tmp_callrecord_hour";
string sql = CreateCallRecordStatSql(ReportTempType.Hour, tmp);
int b = SqlHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql);
list.Add(ReportTempType.Hour, tmp);
msg += "统计小时数据=" + b + ";";
}
if (tmptype == ReportTempType.Day || tmptype == ReportTempType.All)
{
string tmp = "#tmp_callrecord_day";
string sql = CreateCallRecordStatSql(ReportTempType.Day, tmp);
int b = SqlHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql);
list.Add(ReportTempType.Day, tmp);
msg += "统计天数据=" + b + ";";
}
return(list);
}
/// 创建坐席状态统计临时表
/// <summary>
/// 创建坐席状态统计临时表
/// </summary>
/// <param name="conn"></param>
/// <param name="date"></param>
/// <param name="tablename"></param>
/// <param name="tmp_hour"></param>
/// <param name="tmp_day"></param>
public Dictionary <ReportTempType, string> CreateReportAgentStateStatForDayTmp(SqlConnection conn, DateTime date, ReportTempType tmptype, string tablename, out string msg)
{
#region 创建临时表
string tmpsql = @"
SELECT
--统计时间，小时，坐席，状态1,2，时长
CAST(StatDate AS DATE) AS StatDate,[Hour],AgentUserID,State,AgentAuxState,T_time,
--指标
CASE a.STATE WHEN 1 THEN 1 ELSE 0 END AS is_qianchu,
CASE a.STATE WHEN 2 THEN 1 ELSE 0 END AS is_qianru,
CASE a.STATE WHEN 3 THEN 1 ELSE 0 END AS is_zhixian,
CASE a.STATE WHEN 4 THEN 1 ELSE 0 END AS is_zhimang,
CASE a.STATE WHEN 1 THEN T_time ELSE 0 END AS t_qianchu,
CASE a.STATE WHEN 2 THEN T_time ELSE 0 END AS t_qianru,
CASE a.STATE WHEN 3 THEN T_time ELSE 0 END AS t_zhixian,
CASE a.STATE WHEN 4 THEN T_time ELSE 0 END AS t_zhimang,
CASE WHEN a.STATE=4 AND a.AgentAuxState=0 THEN 1 ELSE 0 END AS is_busy0,
CASE WHEN a.STATE=4 AND a.AgentAuxState=1 THEN 1 ELSE 0 END AS is_busy1,
CASE WHEN a.STATE=4 AND a.AgentAuxState=2 THEN 1 ELSE 0 END AS is_busy2,
CASE WHEN a.STATE=4 AND a.AgentAuxState=3 THEN 1 ELSE 0 END AS is_busy3,
CASE WHEN a.STATE=4 AND a.AgentAuxState=4 THEN 1 ELSE 0 END AS is_busy4,
CASE WHEN a.STATE=4 AND a.AgentAuxState=5 THEN 1 ELSE 0 END AS is_busy5,
CASE WHEN a.STATE=4 AND a.AgentAuxState=6 THEN 1 ELSE 0 END AS is_busy6,
CASE WHEN a.STATE=4 AND a.AgentAuxState=0 THEN T_time ELSE 0 END AS t_busy0,
CASE WHEN a.STATE=4 AND a.AgentAuxState=1 THEN T_time ELSE 0 END AS t_busy1,
CASE WHEN a.STATE=4 AND a.AgentAuxState=2 THEN T_time ELSE 0 END AS t_busy2,
CASE WHEN a.STATE=4 AND a.AgentAuxState=3 THEN T_time ELSE 0 END AS t_busy3,
CASE WHEN a.STATE=4 AND a.AgentAuxState=4 THEN T_time ELSE 0 END AS t_busy4,
CASE WHEN a.STATE=4 AND a.AgentAuxState=5 THEN T_time ELSE 0 END AS t_busy5,
CASE WHEN a.STATE=4 AND a.AgentAuxState=6 THEN T_time ELSE 0 END AS t_busy6
INTO #tmp_state
FROM (
--主表 计算每一个小时的间隔时长
SELECT
Oid,StatDate,AgentUserID,AgentName,State,AgentAuxState,stat_hour AS [Hour],
CASE WHEN stat_hour=start_hour THEN StartTime ELSE StatDate END AS calc_st,
CASE WHEN stat_hour=end_hour THEN EndTime ELSE DATEADD(hour,1,StatDate) END AS calc_et,
DATEDIFF(SECOND,(CASE WHEN stat_hour=start_hour THEN StartTime ELSE StatDate END),(CASE WHEN stat_hour=end_hour THEN EndTime ELSE DATEADD(hour,1,StatDate) END)) AS T_time
FROM (
SELECT
--所有拆分小时后的明细数据
tmp_main.StatDate,a.Oid,a.AgentID AS AgentUserID,a.AgentName,a.State,a.AgentAuxState,a.StartTime,a.EndTime,
DATENAME(HOUR,a.StartTime) AS start_hour,
DATENAME(HOUR,a.EndTime) AS end_hour,
DATENAME(HOUR,tmp_main.StatDate) AS stat_hour
FROM (
SELECT mind AS StatDate,AgentID
--全量时间表
FROM f_R_GetMWD('" + date.ToString("yyyy-MM-dd") + @" 00:00:00' ,'" + date.ToString("yyyy-MM-dd") + @" 23:59:59',4) AS tmp_date,
--全量坐席表
(SELECT DISTINCT AgentID FROM " + tablename + @" a
WHERE a.StartTime>='" + date.ToString("yyyy-MM-dd") + @" 00:00:00'
AND a.StartTime<='" + date.ToString("yyyy-MM-dd") + @" 23:59:59'
AND State IN (1,2,3,4)) AS tmp_agent
) AS tmp_main
--关联具体数据
INNER JOIN " + tablename + @" a
--8:30-12:30转成7:30-12:30转成8:00,9:00,10:00,11:00,12:00
--8:00-12:00转成7:00-12:00转成9:00,10:00,11:00
ON (tmp_main.AgentID=a.AgentID AND tmp_main.StatDate>DATEADD(HOUR,-1,a.StartTime) AND tmp_main.StatDate<a.EndTime)
WHERE a.State IN (1,2,3,4)
) tmp
) a";
int a = SqlHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, tmpsql);
msg = "查询一天的数据量=" + a + ";";
#endregion
Dictionary <ReportTempType, string> list = new Dictionary <ReportTempType, string>();
if (tmptype == ReportTempType.Hour || tmptype == ReportTempType.All)
{
string tmp = "#tmp_agentstate_hour";
string sql = CreateAgentStatetatSql(ReportTempType.Hour, tmp);
int b = SqlHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql);
list.Add(ReportTempType.Hour, tmp);
msg += "统计小时数据=" + b + ";";
}
if (tmptype == ReportTempType.Day || tmptype == ReportTempType.All)
{
string tmp = "#tmp_agentstate_day";
string sql = CreateAgentStatetatSql(ReportTempType.Day, tmp);
int b = SqlHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql);
list.Add(ReportTempType.Day, tmp);
msg += "统计天数据=" + b + ";";
}
return(list);
}
/// 创建坐席状态统计临时表
/// <summary>
/// 创建坐席状态统计临时表
/// </summary>
/// <param name="date"></param>
/// <param name="tmp_hour"></param>
/// <param name="tmp_day"></param>
public Dictionary <ReportTempType, string> CreateReportAgentStateStatForDayTmp(SqlConnection conn, DateTime date, ReportTempType tmptype, out string msg)
{
string tablename = "";
if (date.Date == DateTime.Today)
{
tablename = "AgentStateDetail";
}
else
{
tablename = "AgentStateDetailHistory";
}
return(Dal.CallRecordReport.Instance.CreateReportAgentStateStatForDayTmp(conn, date, tmptype, tablename, out msg));
}
/// 创建话务统计临时表
/// <summary>
/// 创建话务统计临时表
/// </summary>
/// <param name="date"></param>
/// <param name="tmp_hour"></param>
/// <param name="tmp_day"></param>
public Dictionary <ReportTempType, string> CreateReportCallRecordStatForDayTmp(SqlConnection conn, DateTime date, ReportTempType tmptype, out string msg)
{
string tableEndName = BLL.Util.CalcTableNameByMonth(3, date);
return(Dal.CallRecordReport.Instance.CreateReportCallRecordStatForDayTmp(conn, date, tmptype, tableEndName, out msg));
}
