/// 计算日期的分组名称
/// <summary>
/// 计算日期的分组名称
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
private string GetMyOrderStatDate(QueryCallRecordInfo query)
{
string sqldatename = "";
if (query.QueryType == 1)
{
sqldatename = @"(CONVERT(NVARCHAR(50),(CONVERT(varchar(100),(
SELECT TOP 1 mind FROM f_R_GetMWD(CONVERT(DATE,'" + query.BeginTime + "'),CONVERT(DATE,'" + query.EndTime + @"'),1) as tmptime
WHERE a.StatDate between tmptime.mind AND tmptime.maxd ),23))+'到'+
CONVERT(NVARCHAR(50),(
SELECT TOP 1 maxd FROM f_R_GetMWD(convert(DATE,'" + query.BeginTime + "'),CONVERT(DATE,'" + query.EndTime + @"'),1) as tmptime
WHERE a.StatDate between tmptime.mind AND tmptime.maxd),23))) AS StartTime,";
}
else if (query.QueryType == 2)
{
sqldatename = @"(CONVERT(NVARCHAR(50),(CONVERT(varchar(100),(
SELECT TOP 1 mind FROM f_R_GetMWD(CONVERT(DATE,'" + query.BeginTime + "'),CONVERT(DATE,'" + query.EndTime + @"'),2) as tmptime
WHERE a.StatDate between tmptime.mind AND tmptime.maxd),23))+'到'+
CONVERT(NVARCHAR(50),(
SELECT TOP 1 maxd FROM f_R_GetMWD(convert(DATE,'" + query.BeginTime + "'),CONVERT(DATE,'" + query.EndTime + @"'),2) as tmptime
WHERE a.StatDate between tmptime.mind AND tmptime.maxd),23))) AS StartTime,";
}
else if (query.QueryType == 3)
{
sqldatename = "CONVERT(varchar(100), a.StatDate, 23)AS StartTime,";
}
else if (query.QueryType == 4)
{
sqldatename = "( '" + query.BeginTime.Value.ToString("yyyy-MM-dd") + @"'+' '+ right('0'+convert(VARCHAR(2),a.hour),2)+'时到'+right('0'+convert(VARCHAR(2),a.hour+1),2)+'时') AS StartTime,";
}
return(sqldatename);
}
private void BindData()
{
if (string.IsNullOrEmpty(TaskID))
{
return;
}
else
{
task = BLL.OrderCRMStopCustTask.Instance.GetOrderCRMStopCustTask(TaskID);
if (task == null)
{
return;
}
}
QueryCallRecordInfo query = new QueryCallRecordInfo();
//按照任务查询话务
query.TaskTypeID = (int)Entities.ProjectSource.S2_客户核实;
query.TaskID = this.TaskID;
query.LoginID = -1;
int totalCount = 0;
string tableEndName = ""; //查询现在表数据
DataTable dt = BLL.CallRecordInfo.Instance.GetCallRecordInfo(query, "c.CreateTime desc", 1, -1, tableEndName, out totalCount);
//设置数据源
if (dt != null && dt.Rows.Count > 0)
{
repeater_Contact.DataSource = dt;
}
//绑定列表数据
repeater_Contact.DataBind();
}
/// 抽检频次统计方法
/// <summary>
/// 抽检频次统计方法
/// </summary>
/// <param name="query"></param>
/// <param name="order"></param>
/// <param name="currentPage"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable GetQS_ResultFrequenyStatistics(QueryCallRecordInfo query, string order, int currentPage, int pageSize, string tableEndName, out int totalCount)
{
string where = string.Empty;
where = getWhereBySelect(query);
DataSet ds;
SqlParameter[] parameters =
{
new SqlParameter("@whereOut", SqlDbType.NVarChar, 4000),
new SqlParameter("@where", SqlDbType.NVarChar, 4000),
new SqlParameter("@tableend", SqlDbType.NVarChar, 20),
new SqlParameter("@order", SqlDbType.NVarChar, 200),
new SqlParameter("@pagesize", SqlDbType.Int, 4),
new SqlParameter("@indexpage", SqlDbType.Int, 4),
new SqlParameter("@totalRecorder", SqlDbType.Int, 4)
};
parameters[0].Value = query.QSScoreCreaters;
parameters[1].Value = where;
parameters[2].Value = tableEndName;
parameters[3].Value = order;
parameters[4].Value = pageSize;
parameters[5].Value = currentPage;
parameters[6].Direction = ParameterDirection.Output;
ds = SqlHelper.ExecuteDataset(CONNECTIONSTRINGS, CommandType.StoredProcedure, "P_FrequencyStatistics", parameters);
totalCount = (int)(parameters[6].Value);
return(ds.Tables[0]);
}
/// 查询录音质检数据
/// <summary>
/// 查询录音质检数据
/// </summary>
/// <param name="query"></param>
/// <param name="order"></param>
/// <param name="currentPage"></param>
/// <param name="pageSize"></param>
/// <param name="tableEndName"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable GetQS_ResultList(QueryCallRecordInfo query, string order, int currentPage, int pageSize, string tableEndName, out int totalCount, log4net.ILog log)
{
string where = string.Empty;
where = getWhereBySelect(query);
DataSet ds;
SqlParameter[] parameters =
{
new SqlParameter("@where", SqlDbType.NVarChar, 4000),
new SqlParameter("@tableend", SqlDbType.NVarChar, 20),
new SqlParameter("@order", SqlDbType.NVarChar, 200),
new SqlParameter("@pagesize", SqlDbType.Int, 4),
new SqlParameter("@indexpage", SqlDbType.Int, 4),
new SqlParameter("@totalRecorder", SqlDbType.Int, 4)
};
parameters[0].Value = where;
parameters[1].Value = tableEndName;
parameters[2].Value = order;
parameters[3].Value = pageSize;
parameters[4].Value = currentPage;
parameters[5].Direction = ParameterDirection.Output;
Stopwatch sw = new Stopwatch();
sw.Start();
log.Info(string.Format("【调用脚本P_QS_RESULT_SELECTLIST】,开始：条件：{0}", where));
ds = SqlHelper.ExecuteDataset(CONNECTIONSTRINGS, CommandType.StoredProcedure, "P_QS_RESULT_SELECTLIST", parameters);
sw.Stop();
totalCount = (int)(parameters[5].Value);
log.Info(string.Format("【调用脚本P_QS_RESULT_SELECTLIST】,耗时：{2}毫秒，条件：{0}，查询总数：{1}", where, totalCount, sw.ElapsedMilliseconds));
return(ds.Tables[0]);
}
/// 报表条件
/// <summary>
/// 报表条件
/// </summary>
/// <param name="query"></param>
/// <param name="where"></param>
/// <param name="addHour"></param>
private string GetReportWhereCall(QueryCallRecordInfo query, string type)
{
//是否需要数据权限
bool isneedright = true;
string where = "";
//时间
if (query.BeginTime != Constant.DATE_INVALID_VALUE)
{
where += " AND a.StatDate >='" + DateTime.Parse(query.BeginTime.ToString()).ToString("yyyy-MM-dd") + "'";
}
if (query.EndTime != Constant.DATE_INVALID_VALUE)
{
where += " AND a.StatDate <'" + DateTime.Parse(query.EndTime.ToString()).AddDays(1).ToString("yyyy-MM-dd") + "'";
}
//坐席
if (!string.IsNullOrEmpty(query.Agent))
{
isneedright = false;
where += " and a.AgentUserID=" + CommonFunction.ObjectToInteger(SqlFilter(query.Agent));
}
//呼出-分组
if (query.BGID != Constant.INT_INVALID_VALUE)
{
isneedright = false;
where += " AND em.BGID=" + query.BGID + "";
}
//工号
if (!string.IsNullOrEmpty(query.AgentNum))
{
where += " and em.AgentNum='" + SqlFilter(query.AgentNum) + "'";
}
//呼入-业务线
if (!string.IsNullOrEmpty(query.selBusinessType))
{
where += " AND CONVERT(NVARCHAR(50),(call.AreaCode+''+call.TelMainNum))='" + query.selBusinessType + "'";
}
//数据权限
if (isneedright)
{
if (query.LoginID != Constant.INT_INVALID_VALUE)
{
string whereDataRight = "";
if (type == "CallIN")
{
whereDataRight = Dal.UserGroupDataRigth.Instance.GetSqlRightstr("a", "a", "BGID", "AgentUserID", query.LoginID);
}
else if (type == "CallOUT")
{
whereDataRight = Dal.UserGroupDataRigth.Instance.GetSqlRightstr("em", "a", "BGID", "AgentUserID", query.LoginID);
}
where += whereDataRight;
}
}
return(where);
}
/// 计算小时字段名称
/// <summary>
/// 计算小时字段名称
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
private string GetHourFiled(QueryCallRecordInfo query)
{
string addHour = ",0 AS hour ";
if (query.QueryType == 4)
{
addHour = " ";
}
return(addHour);
}
/// 呼出报表-汇总
/// <summary>
/// 呼出报表-汇总
/// </summary>
/// <param name="query"></param>
/// <param name="conn"></param>
/// <param name="searchTableName"></param>
/// <param name="searchAgentTableName"></param>
/// <returns></returns>
public DataTable GetCallOutDataReportTotal(QueryCallRecordInfo query, SqlConnection conn, string searchTableName, string searchAgentTableName)
{
//小时字段
string addHour = GetHourFiled(query);
//话务表过滤条件
string where_call = GetReportWhereCall(query, "CallOUT");
//获取日期部分
string sqldatename = GetMyOrderStatDate(query);
//获取查询语句
string sql = GetCallReportOutSql(searchTableName, searchAgentTableName, addHour, where_call, sqldatename, "汇总");
//查询数据
return(SqlHelper.ExecuteDataset(conn, System.Data.CommandType.Text, sql).Tables[0]);
}
/// 呼出报表-明细
/// <summary>
/// 呼出报表-明细
/// </summary>
/// <param name="query"></param>
/// <param name="conn"></param>
/// <param name="searchTableName"></param>
/// <param name="searchAgentTableName"></param>
/// <param name="PageIndex"></param>
/// <param name="PageSize"></param>
/// <param name="RecordCount"></param>
/// <returns></returns>
public DataTable GetCallOutReportData(QueryCallRecordInfo query, SqlConnection conn, string searchTableName, string searchAgentTableName, int PageIndex, int PageSize, out int RecordCount)
{
RecordCount = 0;
//小时字段
string addHour = GetHourFiled(query);
//话务表过滤条件
string where_call = GetReportWhereCall(query, "CallOUT");
//获取日期部分
string sqldatename = GetMyOrderStatDate(query);
//获取查询语句
string sql = GetCallReportOutSql(searchTableName, searchAgentTableName, addHour, where_call, sqldatename, "明细");
//查询数据
return(GetPagedTable(conn, sql, "", "StartTime DESC,TrueName ASC", PageIndex, PageSize, out RecordCount));
}
private void BindData()
{
Entities.QueryCallRecordInfo query = new QueryCallRecordInfo();
query.LoginID = -1;
query.CRMCustID = ch.CustID;
string tableEndName = ""; //查询现在表数据
DataTable dt = BLL.CallRecordInfo.Instance.GetCallRecordInfo(query, "c.CreateTime desc", BLL.PageCommon.Instance.PageIndex, PageSize, tableEndName, out RecordCount);
if (dt != null)
{
repeaterTableList.DataSource = dt;
repeaterTableList.DataBind();
}
//分页控件
AjaxPager_CallRecord.PageSize = 5;
AjaxPager_CallRecord.InitPager(RecordCount);
}
private void BindInComingCallData()
{
QueryCallRecordInfo query = new QueryCallRecordInfo();
query.BeginTime = Convert.ToDateTime(this.StartTime);
query.EndTime = Convert.ToDateTime(this.EndTime);
query.QueryType = CommonFunction.ObjectToInteger(this.QueryType);
query.LoginID = BLL.Util.GetLoginUserID();
query.selBusinessType = this.BusinessType;
query.AgentNum = this.AgentNum; //agentnum
query.Agent = this.AgentID; //userid
// string tableEndName = BLL.Util.CalcTableNameByMonth(3, CommonFunction.ObjectToDateTime(query.StartTime));
DataTable dts = null;
SqlConnection conn = null;
try
{
string msg = "";
string searchTableName = "Report_CallRecord_Day";
string searchAgentTableName = "Report_AgentState_Day";
conn = CallRecordReport.Instance.CreateSqlConnection();
if (DateTime.Now.Date == query.BeginTime.Value.Date && DateTime.Now.Date == query.EndTime.Value.Date)
{
Dictionary <ReportTempType, string> dic1 = CallRecordReport.Instance.CreateReportCallRecordStatForDayTmp(conn, query.BeginTime.Value, ReportTempType.Day, out msg);
searchTableName = dic1[ReportTempType.Day];
Dictionary <ReportTempType, string> dic2 = CallRecordReport.Instance.CreateReportAgentStateStatForDayTmp(conn, query.BeginTime.Value, ReportTempType.Day, out msg);
searchAgentTableName = dic2[ReportTempType.Day];
}
dts = CallRecordReport.Instance.GetCallInReportData(query, conn, BLL.PageCommon.Instance.PageIndex, PageSize, searchTableName, searchAgentTableName, out RecordCount);
}
catch (Exception ex)
{
throw ex;
}
finally
{
CallRecordReport.Instance.CloseSqlConnection(conn);
}
repeaterList.DataSource = dts;
repeaterList.DataBind();
litPagerDown.Text = BLL.PageCommon.Instance.LinkStringByPost(BLL.Util.GetUrl(), GroupLength, RecordCount, PageSize, BLL.PageCommon.Instance.PageIndex, 1);
}
/// 合格型-导出基本信息
/// <summary>
/// 合格型-导出基本信息
/// </summary>
/// <param name="query">查询条件</param>
/// <returns></returns>
public DataTable GetBaseInfoByQualifiedType(QueryCallRecordInfo query, string tableEndName)
{
string where = string.Empty;
where = getWhereBySelect(query);
DataSet ds;
SqlParameter[] parameters =
{
new SqlParameter("@where", SqlDbType.NVarChar, 4000),
new SqlParameter("@tableend", SqlDbType.NVarChar, 20)
};
parameters[0].Value = where;
parameters[1].Value = tableEndName;
ds = SqlHelper.ExecuteDataset(CONNECTIONSTRINGS, CommandType.StoredProcedure, "p_QS_Result_ExportBaseInfoByQualifiedType", parameters);
return(ds.Tables[0]);
}
/// 回访列表-功能废弃
/// <summary>
/// 回访列表-功能废弃
/// </summary>
/// <param name="queryCC_CallRecords"></param>
/// <param name="currentPage"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable GetCC_CallRecordsByRV(QueryCallRecordInfo queryCC_CallRecords, int currentPage, int pageSize, out int totalCount)
{
string where = "";
if (queryCC_CallRecords.CustID != Constant.STRING_INVALID_VALUE)
{
where += " and CustInfo.CustID = '" + SqlFilter(queryCC_CallRecords.CustID) + "'";
}
if (queryCC_CallRecords.CustName != Constant.STRING_INVALID_VALUE)
{
where += " and CustInfo.CustName like '%" + SqlFilter(queryCC_CallRecords.CustName) + "%'";
}
if (queryCC_CallRecords.CreateUserID != Constant.INT_INVALID_VALUE)
{
where += " and ReturnVisit.CustID in (select custid from CRM2009.dbo.CustUserMapping where userid=" + queryCC_CallRecords.CreateUserID + " ) ";
}
string order = "ReturnVisit.createtime desc";
DataSet ds;
SqlParameter[] parameters =
{
new SqlParameter("@where", SqlDbType.VarChar, 8000),
new SqlParameter("@order", SqlDbType.NVarChar, 100),
new SqlParameter("@pagesize", SqlDbType.Int, 4),
new SqlParameter("@page", SqlDbType.Int, 4),
new SqlParameter("@totalRecorder", SqlDbType.Int, 4)
};
parameters[0].Value = where;
parameters[1].Value = order;
parameters[2].Value = pageSize;
parameters[3].Value = currentPage;
parameters[4].Direction = ParameterDirection.Output;
ds = SqlHelper.ExecuteDataset(CONNECTIONSTRINGS, CommandType.StoredProcedure, "p_CC_CallRecords_Select_by_rv", parameters);
totalCount = int.Parse(parameters[4].Value.ToString());
return(ds.Tables[0]);
}
/// 查询来去电数据
/// <summary>
/// 查询来去电数据
/// </summary>
/// <param name="query">查询条件</param>
/// <param name="userid">当前用户ID</param>
/// <param name="order">排序</param>
/// <param name="currentPage">页号,-1不分页</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="totalCount">总行数</param>
/// <returns></returns>
public DataTable GetCallRecordInfo(QueryCallRecordInfo query, string order, int currentPage, int pageSize, string tableEndName, out int totalCount)
{
return(Dal.CallRecordInfo.Instance.GetCallRecordInfo(query, order, currentPage, pageSize, tableEndName, out totalCount));
}
/// 查询录音质检数据
/// <summary>
/// 查询录音质检数据
/// </summary>
/// <param name="query">查询条件</param>
/// <param name="order">排序</param>
/// <param name="currentPage">页号,-1不分页</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="totalCount">总行数</param>
/// <returns>集合</returns>
public DataTable GetQS_ResultList(QueryCallRecordInfo query, string order, int currentPage, int pageSize, string tableEndName, out int totalCount)
{
return(Dal.QS_Result.Instance.GetQS_ResultList(query, order, currentPage, pageSize, tableEndName, out totalCount, BLL.Loger.Log4Net));
}
public void ProcessRequest(HttpContext context)
{
BitAuto.YanFa.SysRightManager.Common.UserInfo.Check();
context.Response.ContentType = "text/plain";
string message = "";
if ((context.Request["Action"] + "").Trim() == "InComingCallTotal")
{
string newAgentIDS = "";
#region 根据AgentNum找到对应的AgentIds
if (string.IsNullOrEmpty(this.AgentID))
{
if (!string.IsNullOrEmpty(this.AgentNum))
{
DataTable dtModel = BLL.EmployeeAgent.Instance.GetEmployeeAgentsByAgentNum(this.AgentNum);
string agentIDFromAgentNum = "";
for (int i = 0; i < dtModel.Rows.Count; i++)
{
agentIDFromAgentNum += "," + dtModel.Rows[i]["UserID"].ToString();
}
if (!string.IsNullOrEmpty(agentIDFromAgentNum))
{
agentIDFromAgentNum = agentIDFromAgentNum.Substring(1, agentIDFromAgentNum.Length - 1);
}
if (!string.IsNullOrEmpty(agentIDFromAgentNum))
{
newAgentIDS = agentIDFromAgentNum;
}
else
{
newAgentIDS = "-100";
}
}
}
else
{
newAgentIDS = this.AgentID;
}
#endregion
QueryCallRecordInfo query = new QueryCallRecordInfo();
query.BeginTime = Convert.ToDateTime(this.StartTime);
query.EndTime = Convert.ToDateTime(this.EndTime);
query.QueryType = CommonFunction.ObjectToInteger(this.QueryType);
query.LoginID = BLL.Util.GetLoginUserID();
query.selBusinessType = this.BusinessType;
query.AgentNum = this.AgentNum; //agentnum
query.Agent = this.AgentID; //userid
// string tableEndName = BLL.Util.CalcTableNameByMonth(3, CommonFunction.ObjectToDateTime(query.StartTime));
DataTable dt = null;
SqlConnection conn = null;
try
{
conn = CallRecordReport.Instance.CreateSqlConnection();
string msg = "";
string searchTableName = "Report_CallRecord_Day";
string searchAgentTableName = "Report_AgentState_Day";
if (DateTime.Now.Date == query.BeginTime.Value.Date && DateTime.Now.Date == query.EndTime.Value.Date)
{
Dictionary <ReportTempType, string> dic1 = CallRecordReport.Instance.CreateReportCallRecordStatForDayTmp(conn, query.BeginTime.Value, ReportTempType.Day, out msg);
searchTableName = dic1[ReportTempType.Day];
Dictionary <ReportTempType, string> dic2 = CallRecordReport.Instance.CreateReportAgentStateStatForDayTmp(conn, query.BeginTime.Value, ReportTempType.Day, out msg);
searchAgentTableName = dic2[ReportTempType.Day];
}
dt = CallRecordReport.Instance.GetCallInReportDataTotal(query, conn, searchTableName, searchAgentTableName);
}
catch (Exception)
{
throw;
}
finally
{
CallRecordReport.Instance.CloseSqlConnection(conn);
}
JsonData jsondata = new JsonData();
if (dt == null || dt.Rows.Count <= 0)
{
jsondata.N_CallIsQuantity = "";
jsondata.T_RingingTime = "";
jsondata.T_TalkTime = "";
jsondata.T_AfterworkTime = "";
jsondata.T_SetLogin = "";
jsondata.P_WorkTimeUse = "";
jsondata.A_AverageRingTime = "";
jsondata.A_AverageTalkTime = "";
jsondata.A_AfterworkTime = "";
jsondata.T_SetBuzy = "";
jsondata.N_SetBuzy = "";
jsondata.A_AverageSetBusy = "";
jsondata.N_TransferIn = "";
jsondata.N_TransferOut = "";
}
else
{
jsondata.N_CallIsQuantity = dt.Rows[0]["N_CallIsQuantity"].ToString();
jsondata.T_RingingTime = dt.Rows[0]["T_RingingTime"].ToString();
jsondata.T_TalkTime = dt.Rows[0]["T_TalkTime"].ToString();
jsondata.T_AfterworkTime = dt.Rows[0]["T_AfterworkTime"].ToString();
jsondata.T_SetLogin = dt.Rows[0]["T_SetLogin"].ToString();
jsondata.P_WorkTimeUse = dt.Rows[0]["P_WorkTimeUse"].ToString();
jsondata.A_AverageRingTime = dt.Rows[0]["A_AverageRingTime"].ToString();
jsondata.A_AverageTalkTime = dt.Rows[0]["A_AverageTalkTime"].ToString();
jsondata.A_AfterworkTime = dt.Rows[0]["A_AfterworkTime"].ToString();
jsondata.T_SetBuzy = dt.Rows[0]["T_SetBuzy"].ToString();
jsondata.N_SetBuzy = dt.Rows[0]["N_SetBuzy"].ToString();
jsondata.A_AverageSetBusy = dt.Rows[0]["A_AverageSetBusy"].ToString();
jsondata.N_TransferIn = dt.Rows[0]["N_TransferIn"].ToString();
jsondata.N_TransferOut = dt.Rows[0]["N_TransferOut"].ToString();
}
message = Newtonsoft.Json.JavaScriptConvert.SerializeObject(jsondata);
context.Response.Write(message);
context.Response.End();
}
else
{
success = false;
message = "request error";
BitAuto.ISDC.CC2012.BLL.AJAXHelper.WrapJsonResponse(success, result, message);
}
}
/// 呼入报表-明细
/// <summary>
/// 呼入报表-明细
/// </summary>
/// <param name="query"></param>
/// <param name="conn"></param>
/// <param name="PageIndex"></param>
/// <param name="PageSize"></param>
/// <param name="searchTableName"></param>
/// <param name="searchAgentTableName"></param>
/// <param name="RecordCount"></param>
/// <returns></returns>
public DataTable GetCallInReportData(QueryCallRecordInfo query, SqlConnection conn, int PageIndex, int PageSize, string searchTableName, string searchAgentTableName, out int RecordCount)
{
return(Dal.CallRecordReport.Instance.GetCallInReportData(query, conn, BLL.PageCommon.Instance.PageIndex, PageSize, searchTableName, searchAgentTableName, out RecordCount));
}
/// 呼入报表-汇总
/// <summary>
/// 呼入报表-汇总
/// </summary>
/// <param name="query"></param>
/// <param name="conn"></param>
/// <param name="searchTableName"></param>
/// <param name="searchAgentTableName"></param>
/// <returns></returns>
public DataTable GetCallInReportDataTotal(QueryCallRecordInfo query, SqlConnection conn, string searchTableName, string searchAgentTableName)
{
return(Dal.CallRecordReport.Instance.GetCallInReportDataTotal(query, conn, searchTableName, searchAgentTableName));
}
/// 合格型-导出基本信息和质检标准信息
/// <summary>
/// 合格型-导出基本信息和质检标准信息
/// </summary>
/// <param name="query">查询条件</param>
/// <returns> </returns>
public DataTable GetBaseInfoByQualifiedType(QueryCallRecordInfo query, string tableEndName)
{
return(Dal.QS_Result.Instance.GetBaseInfoByQualifiedType(query, tableEndName));
}
/// 根据条件，获取查询类
/// <summary>
/// 根据条件，获取查询类
/// </summary>
/// <param name="Name"></param>
/// <param name="ANI"></param>
/// <param name="Agent">坐席名称</param>
/// <param name="TaskID"></param>
/// <param name="BeginTime"></param>
/// <param name="EndTime"></param>
/// <param name="AgentNum"></param>
/// <param name="PhoneNum"></param>
/// <param name="TaskCategory"></param>
/// <param name="SpanTime1"></param>
/// <param name="SpanTime2"></param>
/// <param name="AgentGroup"></param>
/// <returns></returns>
public QueryCallRecordInfo GetQueryModel(string Name, string ANI, string Agent, string TaskID, string CallID,
string BeginTime, string EndTime, string AgentNum, string PhoneNum, string TaskCategory,
string SpanTime1, string SpanTime2, string AgentGroup, string CallStatus, int loginID,
string ownGroup, string oneSelf, string Category, string ivrScore, string icomingSource, string selBusinessType, string SelSolve)
{
DateTime tmpDt = new DateTime();
int tmpInt = 0;
Int64 tmpInt64 = 0;
QueryCallRecordInfo query = new QueryCallRecordInfo();
if (icomingSource != "" && int.TryParse(icomingSource, out tmpInt))
{
query.IncomingSource = tmpInt;
}
if (ivrScore != "" && ivrScore != "-1" && int.TryParse(ivrScore, out tmpInt))
{
query.IVRScore = tmpInt;
}
if (Name != "")
{
query.CustName = Name;
}
if (ANI != "")
{
query.ANI = ANI;
}
if (Agent != "")
{
query.Agent = Agent;
}
if (TaskID != "")
{
query.TaskID = TaskID;
}
if (CallID != "" && Int64.TryParse(CallID, out tmpInt64))
{
query.CallID = tmpInt64;
}
if (BeginTime != "" && DateTime.TryParse(BeginTime, out tmpDt))
{
query.BeginTime = tmpDt;
}
if (EndTime != "" && DateTime.TryParse(EndTime, out tmpDt))
{
query.EndTime = tmpDt;
}
if (!string.IsNullOrEmpty(AgentNum))
{
query.AgentNum = AgentNum;
}
if (PhoneNum != "")
{
query.PhoneNum = PhoneNum;
}
if (TaskCategory != "" && int.TryParse(TaskCategory, out tmpInt))
{
query.TaskTypeID = tmpInt;
}
if (SpanTime1 != "" && int.TryParse(SpanTime1, out tmpInt))
{
query.SpanTime1 = tmpInt;
}
if (SpanTime2 != "" && int.TryParse(SpanTime2, out tmpInt))
{
query.SpanTime2 = tmpInt;
}
if (AgentGroup != "" && int.TryParse(AgentGroup, out tmpInt) && AgentGroup != "-1")
{
query.BGID = tmpInt;
}
if (Category != "" && int.TryParse(Category, out tmpInt) && Category != "-1")
{
query.SCID = tmpInt;
}
if (CallStatus != "" && int.TryParse(CallStatus, out tmpInt))
{
query.CallStatus = tmpInt;
}
if (selBusinessType != "" && selBusinessType != "-1")
{
query.selBusinessType = selBusinessType;
}
if (SelSolve != "" && SelSolve != "-1")
{
query.SelSolve = CommonFunction.ObjectToInteger(SelSolve);
}
#region 分组权限
if (loginID != Constant.INT_INVALID_VALUE)
{
query.LoginID = loginID;
}
if (ownGroup != string.Empty)
{
query.OwnGroup = ownGroup;
}
if (oneSelf != string.Empty)
{
query.OneSelf = oneSelf;
}
#endregion
return(query);
}
/// 抽查频次统计
/// <summary>
/// 抽查频次统计
/// </summary>
/// <param name="query"></param>
/// <param name="order"></param>
/// <param name="currentPage"></param>
/// <param name="pageSize"></param>
/// <param name="tableEndName"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable GetQS_ResultFrequenyStatistics(QueryCallRecordInfo query, string order, int currentPage, int pageSize, string tableEndName, out int totalCount)
{
return(Dal.QS_Result.Instance.GetQS_ResultFrequenyStatistics(query, order, currentPage, pageSize, tableEndName, out totalCount));
}
/// 查询来去电数据
/// <summary>
/// 查询来去电数据
/// </summary>
/// <param name="query">查询条件</param>
/// <param name="userid">当前用户ID</param>
/// <param name="order">排序</param>
/// <param name="currentPage">页号,-1不分页</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="totalCount">总行数</param>
/// <returns></returns>
public DataTable GetCallRecordInfo(QueryCallRecordInfo query, string order, int currentPage, int pageSize, string tableEndName, out int totalCount)
{
string where = string.Empty;
#region 数据权限判断
if (query.LoginID > 0)
{
string whereDataRight = "";
whereDataRight = Dal.UserGroupDataRigth.Instance.GetSqlRightstr("c", "BGID", "CreateUserID", query.LoginID);
where += whereDataRight;
}
#endregion
#region 组成条件
//c
if (query.CustID != Constant.STRING_INVALID_VALUE)
{
where += " AND c.CustID='" + SqlFilter(query.CustID) + "'";
}
if (!string.IsNullOrEmpty(query.CRMCustID))
{
where += @"AND c.CustID IN
(SELECT a.CustID FROM dbo.CustBasicInfo a
INNER JOIN dbo.DealerInfo b ON a.CustID=b.CustID
INNER JOIN CRM2009.dbo.DMSMember c ON b.MemberCode=c.MemberCode
WHERE a.Status=0 AND b.Status=0 AND c.Status=0
AND a.CustCategoryID=3
AND ISNULL(c.MemberCode,'')<>'' AND c.CustID='" + SqlFilter(query.CRMCustID) + "')";
}
if (query.CallID != Constant.INT_INVALID_VALUE)
{
where += " AND c.CallID = " + query.CallID;
}
if (query.CustName != Constant.STRING_INVALID_VALUE && query.CustName != "")
{
where += " AND c.CustName LIKE '%" + SqlFilter(query.CustName) + "%'";
}
if (query.ANI != Constant.STRING_INVALID_VALUE && query.ANI != "")
{
where += " AND c.ANI ='" + SqlFilter(query.ANI) + "'";
}
if (query.Agent != Constant.STRING_INVALID_VALUE && query.Agent != "")
{
where += " AND c.CreateUserID IN " + " ( SELECT UserID FROM SysRightsManager.dbo.UserInfo WHERE TrueName LIKE '%" + SqlFilter(query.Agent) + "%')";
}
if (!string.IsNullOrEmpty(query.TaskID) && query.TaskID != "-1")
{
where += " AND c.TaskID ='" + SqlFilter(query.TaskID) + "'";
}
if (query.BeginTime != Constant.DATE_INVALID_VALUE)
{
where += " AND c.BeginTime >='" + DateTime.Parse(query.BeginTime.ToString()).ToString("yyyy-MM-dd") + "'";
}
if (query.EndTime != Constant.DATE_INVALID_VALUE)
{
where += " AND c.BeginTime <'" + DateTime.Parse(query.EndTime.ToString()).AddDays(1).ToString("yyyy-MM-dd") + "'";
}
if (!string.IsNullOrEmpty(query.AgentNum))
{
where += " AND c.CreateUserID IN (SELECT userid FROM EmployeeAgent WHERE AgentNum='" + SqlFilter(query.AgentNum) + "')";
}
if (query.PhoneNum != Constant.STRING_INVALID_VALUE && query.PhoneNum != "")
{
where += " AND c.PhoneNum ='" + SqlFilter(query.PhoneNum) + "'";
}
if (query.TaskTypeID.HasValue && query.TaskTypeID > 0)
{
where += " AND c.TaskTypeID=" + query.TaskTypeID.Value;
}
if (query.SpanTime1 != Constant.INT_INVALID_VALUE)
{
where += " AND c.TallTime>=" + query.SpanTime1;
}
if (query.SpanTime2 != Constant.INT_INVALID_VALUE)
{
where += " AND c.TallTime<=" + query.SpanTime2;
}
if (query.CallStatus != Constant.INT_INVALID_VALUE)
{
where += " AND c.CallStatus=" + query.CallStatus;
}
if (query.SessionID != Constant.STRING_INVALID_VALUE)
{
where += " AND c.SessionID='" + SqlFilter(query.SessionID) + "'";
}
if (query.BGID != Constant.INT_INVALID_VALUE)
{
where += " AND c.BGID=" + query.BGID + "";
}
if (query.SCID != Constant.INT_INVALID_VALUE)
{
where += " AND c.SCID=" + query.SCID + "";
}
if (query.CallID != Constant.INT_INVALID_VALUE)
{
where += " AND c.CallID=" + query.CallID + "";
}
//org
if (!string.IsNullOrEmpty(query.selBusinessType) && query.selBusinessType != "-1")
{
where += " AND org.SwitchINNum='" + SqlFilter(query.selBusinessType) + "'";
}
if (!string.IsNullOrEmpty(query.OutTypes))
{
where += " AND org.OutBoundType in (" + Dal.Util.SqlFilterByInCondition(query.OutTypes) + ")";
}
//cot
if (query.ProjectId >= 0)
{
where += " AND cot.ProjectID =" + query.ProjectId;
}
if (query.IsSuccess >= 0)
{
where += " AND cot.IsSuccess =" + query.IsSuccess;
if (query.IsSuccess == 0 && query.FailReason >= 0)
{
where += " AND cot.NotSuccessReason =" + query.FailReason;
}
}
//ivr
//问题解决 第一级
if (query.SelSolve != Constant.INT_INVALID_VALUE && query.SelSolve >= 0)
{
where += " AND isnull(ivr.Score,0) like '" + query.SelSolve + "%'";
}
//满意度 第2级
if (query.IVRScore != Constant.INT_INVALID_VALUE && query.IVRScore >= 0)
{
where += " AND isnull(ivr.Score,0) like '[12]" + query.IVRScore + "'";
}
#endregion
DataSet ds;
SqlParameter[] parameters =
{
new SqlParameter("@where", SqlDbType.NVarChar, 40000),
new SqlParameter("@order", SqlDbType.NVarChar, 200),
new SqlParameter("@pagesize", SqlDbType.Int, 4),
new SqlParameter("@indexpage", SqlDbType.Int, 4),
new SqlParameter("@tableend", SqlDbType.NVarChar, 200),
new SqlParameter("@totalRecorder", SqlDbType.Int, 4)
};
parameters[0].Value = where;
parameters[1].Value = order;
parameters[2].Value = pageSize;
parameters[3].Value = currentPage;
parameters[4].Value = tableEndName;
parameters[5].Direction = ParameterDirection.Output;
ds = SqlHelper.ExecuteDataset(CONNECTIONSTRINGS, CommandType.StoredProcedure, P_CALLRECORDINFO_SELECT, parameters);
totalCount = (int)(parameters[5].Value);
return(ds.Tables[0]);
}
private void ExportData()
{
QueryCallRecordInfo query = new QueryCallRecordInfo();
query.BeginTime = Convert.ToDateTime(this.StartTime);
query.EndTime = Convert.ToDateTime(this.EndTime);
query.QueryType = CommonFunction.ObjectToInteger(this.QueryType);
query.LoginID = BLL.Util.GetLoginUserID();
query.selBusinessType = this.BusinessType;
query.AgentNum = this.AgentNum; //agentnum
query.Agent = this.AgentID; //userid
DataTable dt = null;
SqlConnection conn = null;
DataTable dtSum = null;
try
{
conn = CallRecordReport.Instance.CreateSqlConnection();
string msg = "";
string searchTableName = "Report_CallRecord_Day";
string searchAgentTableName = "Report_AgentState_Day";
if (DateTime.Now.Date == query.BeginTime.Value.Date && DateTime.Now.Date == query.EndTime.Value.Date)
{
Dictionary <ReportTempType, string> dic1 = CallRecordReport.Instance.CreateReportCallRecordStatForDayTmp(conn, query.BeginTime.Value, ReportTempType.Day, out msg);
searchTableName = dic1[ReportTempType.Day];
Dictionary <ReportTempType, string> dic2 = CallRecordReport.Instance.CreateReportAgentStateStatForDayTmp(conn, query.BeginTime.Value, ReportTempType.Day, out msg);
searchAgentTableName = dic2[ReportTempType.Day];
}
dt = CallRecordReport.Instance.GetCallInReportData(query, conn, 1, -1, searchTableName, searchAgentTableName, out RecordCount);
dtSum = CallRecordReport.Instance.GetCallInReportDataTotal(query, conn, searchTableName, searchAgentTableName);
}
catch (Exception)
{
throw;
}
finally
{
CallRecordReport.Instance.CloseSqlConnection(conn);
}
if (dt != null)
{
if (dtSum != null && dtSum.Rows.Count > 0)
{
DataRow dr = dt.NewRow();
dr["StartTime"] = "合计（共" + RecordCount + "项）";
dr["TrueName"] = "--";
dr["AgentNum"] = "--";
dr["N_CallIsQuantity"] = dtSum.Rows[0]["N_CallIsQuantity"];
dr["T_RingingTime"] = dtSum.Rows[0]["T_RingingTime"];
dr["T_TalkTime"] = dtSum.Rows[0]["T_TalkTime"];
dr["T_AfterworkTime"] = dtSum.Rows[0]["T_AfterworkTime"];
dr["T_SetLogin"] = dtSum.Rows[0]["T_SetLogin"];
dr["P_WorkTimeUse"] = dtSum.Rows[0]["P_WorkTimeUse"];
dr["A_AverageRingTime"] = dtSum.Rows[0]["A_AverageRingTime"];
dr["A_AverageTalkTime"] = dtSum.Rows[0]["A_AverageTalkTime"];
dr["A_AfterworkTime"] = dtSum.Rows[0]["A_AfterworkTime"];
dr["T_SetBuzy"] = dtSum.Rows[0]["T_SetBuzy"];
dr["N_SetBuzy"] = dtSum.Rows[0]["N_SetBuzy"];
dr["A_AverageSetBusy"] = dtSum.Rows[0]["A_AverageSetBusy"];
dr["N_TransferOut"] = dtSum.Rows[0]["N_TransferOut"];
dr["N_TransferIn"] = dtSum.Rows[0]["N_TransferIn"];
dt.Rows.Add(dr);
}
dt.Columns.Remove("rn");
dt.Columns.Remove("UserID");
dt.Columns["StartTime"].SetOrdinal(0);
dt.Columns["TrueName"].SetOrdinal(1);
dt.Columns["AgentNum"].SetOrdinal(2);
dt.Columns["N_CallIsQuantity"].SetOrdinal(3);
dt.Columns["T_RingingTime"].SetOrdinal(4);
dt.Columns["T_TalkTime"].SetOrdinal(5);
dt.Columns["T_AfterworkTime"].SetOrdinal(6);
dt.Columns["T_SetLogin"].SetOrdinal(7);
dt.Columns["P_WorkTimeUse"].SetOrdinal(8);
dt.Columns["A_AverageRingTime"].SetOrdinal(9);
dt.Columns["A_AverageTalkTime"].SetOrdinal(10);
dt.Columns["A_AfterworkTime"].SetOrdinal(11);
dt.Columns["T_SetBuzy"].SetOrdinal(12);
dt.Columns["N_SetBuzy"].SetOrdinal(13);
dt.Columns["A_AverageSetBusy"].SetOrdinal(14);
dt.Columns["N_TransferOut"].SetOrdinal(15);
dt.Columns["N_TransferIn"].SetOrdinal(16);
dt.Columns["StartTime"].ColumnName = "日期";
dt.Columns["TrueName"].ColumnName = "客服";
dt.Columns["AgentNum"].ColumnName = "工号";
dt.Columns["N_CallIsQuantity"].ColumnName = "电话总接通量";
dt.Columns["T_RingingTime"].ColumnName = "总振铃时长";
dt.Columns["T_TalkTime"].ColumnName = "总通话时长";
dt.Columns["T_AfterworkTime"].ColumnName = "总话后时长";
dt.Columns["T_SetLogin"].ColumnName = "总在线时长";
dt.Columns["P_WorkTimeUse"].ColumnName = "工时利用率";
dt.Columns["A_AverageRingTime"].ColumnName = "平均振铃时长(秒)";
dt.Columns["A_AverageTalkTime"].ColumnName = "平均通话时长(秒)";
dt.Columns["A_AfterworkTime"].ColumnName = "平均话后时长(秒)";
dt.Columns["T_SetBuzy"].ColumnName = "置忙总时长";
dt.Columns["N_SetBuzy"].ColumnName = "置忙次数";
dt.Columns["A_AverageSetBusy"].ColumnName = "平均置忙时长(秒)";
dt.Columns["N_TransferOut"].ColumnName = "电话转出次数";
dt.Columns["N_TransferIn"].ColumnName = "电话转入次数";
BLL.Util.ExportToCSV("呼入报表" + DateTime.Now.ToString("yyyy-MM-dd-hh-mm-ss"), dt);
}
}
/// 回访列表-功能废弃
/// <summary>
/// 回访列表-功能废弃
/// </summary>
/// <param name="queryCC_CallRecords"></param>
/// <param name="currentPage"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable GetCC_CallRecordsByRV(QueryCallRecordInfo queryCC_CallRecords, int currentPage, int pageSize, out int totalCount)
{
return(Dal.CallRecordInfo.Instance.GetCC_CallRecordsByRV(queryCC_CallRecords, currentPage, pageSize, out totalCount));
}
