LYGJJ_4.0/Web/Report - 副.../THSC_Report....

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Text;

public partial class Report_THSC_Report : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {

        }
    }

    /// <summary>
    /// 获取坐席名称（x轴数据）
    /// </summary>
    /// <returns></returns>
    public string GetAgent()
    {
        string result = "[";
        var userAccountList = new XYFDRQ.BLL.T_Sys_UserAccount().GetModelList("F_StateFlag=1 and F_SeatFlag=1");
        foreach (XYFDRQ.Model.T_Sys_UserAccount item in userAccountList)
        {
            result += "'" + item.F_UserName + "',";
        }
        result = result.TrimEnd(',');
        return result + "]";
    }


    /// <summary>
    /// 获取坐席的呼出次数
    /// </summary>
    /// <param name="BeginTime">开始时间</param>
    /// <param name="EndTime">结束时间</param>
    /// <param name="Tag">1表示呼出，0表示呼入</param>
    /// <returns></returns>
    public string GetCallRecordOut(string BeginTime, string EndTime, string Tag)
    {
        return GetHighChartData(BeginTime, EndTime, Tag);
    }

    /// <summary>
    /// 获取坐席的呼入次数
    /// </summary>
    /// <param name="BeginTime">开始时间</param>
    /// <param name="EndTime">结束时间</param>
    /// <returns></returns>
    public string GetCallRecordIn(string BeginTime, string EndTime, string Tag)
    {
        return GetHighChartData(BeginTime, EndTime, Tag);
    }


    /// <summary>
    /// 获取呼入呼出数据
    /// </summary>
    /// <param name="BeginTime">开始时间</param>
    /// <param name="EndTime">结束时间</param>
    /// <param name="Tag">1表示呼出，0表示呼入</param>
    /// <returns></returns>
    private string GetHighChartData(string BeginTime, string EndTime, string Tag)
    {
        string result = "[";
        string sql = " and CallType=" + Tag; // 呼入
        var userAccountList = new XYFDRQ.BLL.T_Sys_UserAccount().GetModelList("F_StateFlag=1 and F_SeatFlag=1");

        if (BeginTime != "")
        {
            sql += " and DATEDIFF(d,BeginTime,'" + BeginTime + "')<=0";
        }
        if (EndTime != "")
        {
            sql += " and DATEDIFF(d,BeginTime,'" + EndTime + "')>=0";
        }

        DataTable dt = XYFDRQ.DBUtility.DbHelperSQL.Query("SELECT UserId,UserCode,UserName,COUNT(*) AS calloutCount FROM vw_callRecords where 1=1 " + sql + "  GROUP BY UserId,UserCode,UserName").Tables[0];
        Dictionary<string, int> myDictionary = new Dictionary<string, int>();

        foreach (XYFDRQ.Model.T_Sys_UserAccount item in userAccountList)
        {
            if (!myDictionary.ContainsKey(item.F_UserName))
                myDictionary.Add(item.F_UserName, 0);

            DataRow drUser = dt.AsEnumerable().FirstOrDefault(dr => dr.Field<string>("UserName") == item.F_UserName);

            if (drUser != null)
            {
                myDictionary[item.F_UserName] += int.Parse(drUser[3].ToString());
            }
        }

        foreach (var item in myDictionary)
        {
            result += item.Value + ",";
        }

        result = result.TrimEnd(',');
        return result + "]";
    }

    /// <summary>
    /// 获取通话总时长
    /// </summary>
    /// <param name="BeginTime">开始时间</param>
    /// <param name="EndTime">结束时间</param>
    /// <returns></returns>
    public string GetCallRecordTotalTime(string BeginTime, string EndTime)
    {
        string result = "[";
        string sql = "";

        if (BeginTime != "")
        {
            sql += " and DATEDIFF(d,BeginTime,'" + BeginTime + "')<=0";
        }
        if (EndTime != "")
        {
            sql += " and DATEDIFF(d,BeginTime,'" + EndTime + "')>=0";
        }
        var userAccountList = new XYFDRQ.BLL.T_Sys_UserAccount().GetModelList("F_StateFlag=1 and F_SeatFlag=1");
        DataTable dt = XYFDRQ.DBUtility.DbHelperSQL.Query("SELECT UserId,UserCode,UserName,COUNT(*) AS calloutCount,SUM(TalkLongTime) AS TalkSummaryTime FROM vw_callRecords where 1=1 " + sql + "  GROUP BY UserId,UserCode,UserName").Tables[0];

        foreach (XYFDRQ.Model.T_Sys_UserAccount item in userAccountList)
        {
            DataRow drUser = dt.AsEnumerable().FirstOrDefault(dr => dr.Field<string>("UserName") == item.F_UserName);

            if (drUser != null && drUser[4].ToString()!="")
                result += drUser[4].ToString() + ",";
            else
                result += "0,";
        }

        result = result.TrimEnd(',');
        return result + "]";
    }

    /// <summary>
    /// 获取通话平均时长
    /// </summary>
    /// <param name="BeginTime">开始时间</param>
    /// <param name="EndTime">结束时间</param>
    /// <returns></returns>
    public string GetCallAverageTime(string BeginTime, string EndTime)
    {
        string result = "[";

        string[] CallInTime = GetCallRecordIn(BeginTime, EndTime, "0").TrimStart('[').TrimEnd(']').Split(',');
        string[] CallOutTime = GetCallRecordOut(BeginTime, EndTime, "1").TrimStart('[').TrimEnd(']').Split(',');

        string[] CallTotalTime = GetCallRecordTotalTime(BeginTime, EndTime).TrimStart('[').TrimEnd(']').Split(',');

        for (int i = 0; i < CallInTime.Length; i++)
        {
            double subt = 0.0;
            if (double.Parse(CallInTime[i]) != 0)
            {
                subt += double.Parse(CallInTime[i]);
            }
            if (double.Parse(CallOutTime[i]) != 0)
            {
                subt += double.Parse(CallOutTime[i]);
            }
            if (subt != 0)
                result += (double.Parse(CallTotalTime[i]) / subt).ToString("F2") + ",";
            else
                result += "0,";
        }
        result = result.TrimEnd(',');
        return result + "]";
    }

    /// <summary>
    /// 生成表格的标题和统计字段
    /// </summary>
    /// <param name="TableTitle">表格的标题</param>
    /// <param name="TableHead">表格统计字段</param>
    /// <returns></returns>
    public StringBuilder CreateTableTitleAndHead(string TableTitle, string BeginTime, string EndTime, params object[] TableHead)
    {
        StringBuilder html = new StringBuilder();
        html.Append("<h1 style='font-size: 18px;font-weight: bold;color: #333333;text-align:center;width:100%;'><b>" + TableTitle + "</b></h1>");
        html.Append("<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" width=\"100%\" align='center'>");
        html.Append("<tr style=\"text-align: center;\">");
        for (int i = 0; i < TableHead.Length; i++)
        {
            //标题第一行
            html.Append("<td class=\"formtabletitle1\"style=\"height: 25px;background-color: #D5EDFE;border-left: 1px solid #CCCCCC;border-right: 1px solid #CCCCCC;border-top: 1px solid #CCCCCC;border-bottom: 1px solid #CCCCCC;padding: 3px 3px 3px 3px;text-align:center;\">" + TableHead[i] + "</td>");
        }
        html.Append("</tr>");
        GetTabelData(html, BeginTime, EndTime);
        html.Append("</table>");
        return html;
    }

    /// <summary>
    /// 获取表格统计数据
    /// </summary>
    /// <param name="SB"></param>
    /// <param name="BeginTime"></param>
    /// <param name="EndTime"></param>
    public void GetTabelData(StringBuilder SB, string BeginTime, string EndTime)
    {
        string[] CallInTime = GetCallRecordIn(BeginTime, EndTime, "0").TrimStart('[').TrimEnd(']').Split(',');
        string[] CallOutTime = GetCallRecordOut(BeginTime, EndTime, "1").TrimStart('[').TrimEnd(']').Split(',');
        string[] CallTotalTime = GetCallRecordTotalTime(BeginTime, EndTime).TrimStart('[').TrimEnd(']').Split(',');
        string[] AgentArr = GetAgent().TrimStart('[').TrimEnd(']').Split(',');
        string[] AverageTime=GetCallAverageTime(BeginTime, EndTime).TrimStart('[').TrimEnd(']').Split(',');

        for (int i = 0; i < AgentArr.Length; i++)
        {
            SB.Append("<tr style=\"text-align: center;\" onmouseover=\"this.style.backgroundColor='#F6F6F6'\" onmouseout=\"this.style.backgroundColor='#ffffff'\" >");
            SB.Append("<td class=\"formtabletitle1\"style=\"height: 25px;background-color: #fff;border-left: 1px solid #CCCCCC;border-right: 1px solid #CCCCCC;border-top: 1px solid #CCCCCC;border-bottom: 1px solid #CCCCCC;padding: 3px 3px 3px 3px;text-align:center;\">" + AgentArr[i].Replace('\'',' ') + "</td>");
            SB.Append("<td class=\"formtabletitle1\"style=\"height: 25px;background-color: #fff;border-left: 1px solid #CCCCCC;border-right: 1px solid #CCCCCC;border-top: 1px solid #CCCCCC;border-bottom: 1px solid #CCCCCC;padding: 3px 3px 3px 3px;text-align:center;\">" + CallInTime[i] + "</td>");
            SB.Append("<td class=\"formtabletitle1\"style=\"height: 25px;background-color: #fff;border-left: 1px solid #CCCCCC;border-right: 1px solid #CCCCCC;border-top: 1px solid #CCCCCC;border-bottom: 1px solid #CCCCCC;padding: 3px 3px 3px 3px;text-align:center;\">" + CallOutTime[i] + "</td>");
            SB.Append("<td class=\"formtabletitle1\"style=\"height: 25px;background-color: #fff;border-left: 1px solid #CCCCCC;border-right: 1px solid #CCCCCC;border-top: 1px solid #CCCCCC;border-bottom: 1px solid #CCCCCC;padding: 3px 3px 3px 3px;text-align:center;\">" + CallTotalTime[i] + "S</td>");
            SB.Append("<td class=\"formtabletitle1\"style=\"height: 25px;background-color: #fff;border-left: 1px solid #CCCCCC;border-right: 1px solid #CCCCCC;border-top: 1px solid #CCCCCC;border-bottom: 1px solid #CCCCCC;padding: 3px 3px 3px 3px;text-align:center;\">" + AverageTime[i] + "S</td>");
            SB.Append("</tr>");
        }


    }
    /// <summary>
    /// wbx 4-30 导出Excel
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnExtWord_Click(object sender, EventArgs e)
    {
        string s = CreateTableTitleAndHead("洛阳12329客户服务热线坐席通话时长统计", txtBeginTime.Text.Trim(),txtEndTime.Text.Trim(),"坐席名称", "呼入次数", "呼出次数","通话总时长","平均通话时长").ToString();
        byte[] content = System.Text.Encoding.Default.GetBytes(s);

        HttpResponse clsreponse = System.Web.HttpContext.Current.Response;
        clsreponse.ClearHeaders();
        clsreponse.ClearContent();
        clsreponse.AddHeader("Content-Disposition", "attachment;filename=THSC" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
        clsreponse.ContentType = "application/ms-excel";
        clsreponse.OutputStream.Write(content, 0, content.Length);
        clsreponse.End();
    }
}