/// 获取热点路由数据
/// <summary>
/// 获取热点路由数据
/// </summary>
/// <param name="query"></param>
/// <param name="pageindex"></param>
/// <param name="pagesize"></param>
/// <param name="total"></param>
/// <param name="hzdt"></param>
/// <returns></returns>
public DataTable GetRoutepointData(QueryRoutepoint query, int pageindex, int pagesize, out int total, out DataTable hzdt)
{
string hzsql = "";
string sql = GetRoutepointSql(query, out hzsql);
DataSet ds = new DataSet();
using (SqlConnection conn = new SqlConnection(CONNECTIONSTRINGS))
{
conn.Open();
//设置周起始星期
SqlHelper.ExecuteNonQuery(conn, CommandType.Text, "SET DATEFIRST 1");
//查询详情
SqlParameter[] parameters =
{
new SqlParameter("@SQL", SqlDbType.NVarChar, 4000),
new SqlParameter("@Order", SqlDbType.NVarChar, 200),
new SqlParameter("@CurPage", SqlDbType.Int, 4),
new SqlParameter("@PageRows", SqlDbType.Int, 4),
new SqlParameter("@TotalRecorder", SqlDbType.Int, 4)
};
parameters[0].Value = sql;
parameters[1].Value = "ordertime desc";
parameters[2].Value = pageindex;
parameters[3].Value = pagesize;
parameters[4].Direction = ParameterDirection.Output;
ds = SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, P_Page, parameters);
total = (int)(parameters[4].Value);
//汇总数据查询
hzdt = SqlHelper.ExecuteDataset(conn, CommandType.Text, hzsql).Tables[0];
}
return(ds.Tables[0]);
}
/// 查询数据
/// <summary>
/// 查询数据
/// </summary>
private void BindData()
{
QueryRoutepoint query = new QueryRoutepoint();
query.BusinessType = this.BusinessType;
query.StartTime = this.StartTime;
query.EndTime = this.EndTime;
query.ShowTime = this.ShowTime;
DataTable hzdt = new DataTable();
DataTable dt = BLL.Routepoint.Instance.GetRoutepointData(query, BLL.PageCommon.Instance.PageIndex, PageSize, out RecordCount, out hzdt);
if (dt != null)
{
repeaterList.DataSource = dt;
repeaterList.DataBind();
}
if (hzdt != null && hzdt.Rows.Count > 0)
{
total_count.Text = RecordCount.ToString();
objecttype.Text = "--";
n_entered.Text = CommonFunction.ObjectToInteger(hzdt.Rows[0]["n_entered"]).ToString();
n_entered_out.Text = CommonFunction.ObjectToInteger(hzdt.Rows[0]["n_entered_out"]).ToString();
n_answered.Text = CommonFunction.ObjectToInteger(hzdt.Rows[0]["n_answered"]).ToString();
pc_n_answered.Text = DecimalToStr(hzdt.Rows[0]["pc_n_answered"]) + "%";
pc_n_distrib_in_tr.Text = DecimalToStr(hzdt.Rows[0]["pc_n_distrib_in_tr"]) + "%";
av_t_answered.Text = DecimalToStr(hzdt.Rows[0]["av_t_answered"]);
}
TotalCount.Value = RecordCount.ToString();
litPagerDown.Text = BLL.PageCommon.Instance.LinkStringByPost(BLL.Util.GetUrl(), GroupLength, RecordCount, PageSize, BLL.PageCommon.Instance.PageIndex, 1);
}
private void ExportData()
{
QueryRoutepoint query = new QueryRoutepoint();
query.BusinessType = this.BusinessType;
query.StartTime = this.StartTime;
query.EndTime = this.EndTime;
query.ShowTime = this.ShowTime;
DataTable hzdt = new DataTable();
DataTable dt = BLL.Routepoint.Instance.GetRoutepointData(query, 1, -1, out RecordCount, out hzdt);
if (dt != null)
{
if (hzdt != null && hzdt.Rows.Count > 0)
{
DataRow dr = dt.NewRow();
dr["begintime"] = "合计（共" + RecordCount + "项）";
dr["objecttype"] = "--";
dr["n_entered"] = CommonFunction.ObjectToInteger(hzdt.Rows[0]["n_entered"]).ToString();
dr["n_entered_out"] = CommonFunction.ObjectToInteger(hzdt.Rows[0]["n_entered_out"]).ToString();//修改转人工量指标 2015-3-4 强斐
dr["n_answered"] = CommonFunction.ObjectToInteger(hzdt.Rows[0]["n_answered"]).ToString();
dr["pc_n_answered"] = DecimalToStr(hzdt.Rows[0]["pc_n_answered"]) + "%";
dr["pc_n_distrib_in_tr"] = DecimalToStr(hzdt.Rows[0]["pc_n_distrib_in_tr"]) + "%";
dr["av_t_answered"] = DecimalToStr(hzdt.Rows[0]["av_t_answered"]);//队列平均排队时长 qiangfei 2015-3-5
dt.Rows.Add(dr);
}
dt.Columns.Remove("RowNumber");
dt.Columns["begintime"].ColumnName = "日期";
dt.Columns["objecttype"].ColumnName = "业务类型";
dt.Columns["n_entered"].ColumnName = "呼入量";
dt.Columns["n_entered_out"].ColumnName = "转人工量";//修改转人工量指标 2015-3-4 强斐
dt.Columns["n_answered"].ColumnName = "电话总接通量";
dt.Columns["pc_n_answered"].ColumnName = "接通率";
dt.Columns["pc_n_distrib_in_tr"].ColumnName = "30秒内服务水平";
dt.Columns["av_t_answered"].ColumnName = "平均等待时长（秒）";//队列平均排队时长 qiangfei 2015-3-5
BLL.Util.ExportToCSV("热线数据报表" + DateTime.Now.ToString("yyyy-MM-dd-hh-mm-ss"), dt);
}
}
/// 组装各个统计方法的查询语句
/// <summary>
/// 组装各个统计方法的查询语句
/// </summary>
/// <param name="query"></param>
/// <param name="hzsql"></param>
/// <returns></returns>
private string GetRoutepointSql(QueryRoutepoint query, out string hzsql)
{
string hourwhere = FromAndWhere(query.StartTime, query.EndTime, query.BusinessType);
hzsql = "select " + SumFieldSql() + hourwhere;
string sql = "";
switch (query.ShowTime.ToLower())
{
case "hour":
sql = "select " + ShowHourTime() + EnumSql() + DetailFieldSql() + hourwhere;
break;
case "day":
sql = "select " + DaySelect() + " * " + hourwhere;
sql = "select " + ShowDayTime() + EnumSql() + SumFieldSql() + " from (" + sql + ") tmp group by " + GroupByDay() + "objecttype";
break;
case "week":
sql = "select " + WeekSelect() + " * " + hourwhere;
sql = "select " + ShowWeekTime(query.StartTime, query.EndTime) + EnumSql() + SumFieldSql() + " from (" + sql + ") tmp group by " + GroupByWeek() + "objecttype";
break;
case "month":
sql = "select " + MonthSelect() + " * " + hourwhere;
sql = "select " + ShowMonthTime(query.StartTime, query.EndTime) + EnumSql() + SumFieldSql() + " from (" + sql + ") tmp group by " + GroupByMonth() + "objecttype";
break;
}
//修改转人工量指标 2015-3-4 强斐
return(@"select begintime,objecttype,n_entered,n_entered_out,n_answered," +
"rtrim(convert(decimal(18,2), pc_n_answered))+'%' as pc_n_answered," +
"rtrim(convert(decimal(18,2), pc_n_distrib_in_tr))+'%' as pc_n_distrib_in_tr," +
//"rtrim(convert(decimal(18,2), av_t_distributed)) as av_t_distributed, " +
"rtrim(convert(decimal(18,2), av_t_answered)) as av_t_answered " +
"YanFaFROM (" + sql + ") t");
}
/// 获取热点路由数据
/// <summary>
/// 获取热点路由数据
/// </summary>
/// <param name="query">查询条件</param>
/// <param name="pageindex"></param>
/// <param name="pagesize"></param>
/// <param name="total"></param>
/// <param name="hzdt">汇总数据</param>
/// <returns></returns>
public DataTable GetRoutepointData(QueryRoutepoint query, int pageindex, int pagesize, out int total, out DataTable hzdt)
{
return(Dal.Routepoint.Instance.GetRoutepointData(query, pageindex, pagesize, out total, out hzdt));
}
