UU_CallCenter/code/BaseCallCent.../reportmanage/operationdata/ZuoXiManYiDu...

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Data;

namespace HySoft.BaseCallCenter.Web.reportmanage.operationdata
{
    public partial class ZuoXiManYiDu : System.Web.UI.Page
    {
        protected string TableHtml = "";
        protected void Page_Load(object sender, EventArgs e)
        {
            OutputTable();
        }

        private void OutputTable()
        {
            //查询条件
            string starttime = txtStartTime.Text.Trim();
            string endtime = txtEndTime.Text.Trim();
            if (string.IsNullOrWhiteSpace(starttime)) { starttime = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).ToString("yyyy-MM-dd"); txtStartTime.Text = starttime; }
            if (string.IsNullOrWhiteSpace(endtime)) { endtime = DateTime.Today.ToString("yyyy-MM-dd"); txtEndTime.Text = endtime; }

            string tabletitle = "坐席满意度评价情况报表";
            //string[] tablehead = new string[] { "角色","工号","坐席姓名", "非常满意", "非常满意占比", "基本满意", "基本满意占比", "不满意", "不满意占比", "未评价", "未评价占比" };
            string[] tablehead = new string[] { "角色", "工号", "坐席姓名", "满意", "一般", "不满意", "评价总量", "满意率", "评价率" };
            TableHtml = createTable(tabletitle, tablehead, starttime, endtime);
        }
        protected void btnConfirm_Click(object sender, EventArgs e)
        {
            OutputTable();
        }
        protected void btnExport_Click(object sender, EventArgs e)
        {
            string fileName = "坐席满意度评价情况报表" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
            Response.Clear();
            Response.Charset = "GB2312";
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName));
            Response.ContentType = "application/ms-excel";
            Response.Write(TableHtml);
            Response.End();
        }
        private string createTable(string title, string[] head, string starttime, string endtime)
        {
            StringBuilder sb = new StringBuilder();
            //表名称
            sb.Append(createTableTitle(title));
            //表列名
            sb.Append("<table border='0' cellpadding='0' cellspacing='0' width='100%' align='center'>");
            sb.Append(createTableHead(head));
            //表内容
            sb.Append(createTableBody(starttime, endtime));
            sb.Append("</table>");
            return sb.ToString();
        }
        /// <summary>
        /// 表名称
        /// </summary>
        /// <param name="title">表名</param>
        /// <returns></returns>
        protected string createTableTitle(string title)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("<h1 style='font-size: 18px; font-weight: bold; color: #333333; text-align: center;width: 100%;'><b>" + title + "</b></h1>");
            return sb.ToString();
        }
        /// <summary>
        /// 表头（表列名）
        /// </summary>
        /// <param name="head"></param>
        /// <returns></returns>
        protected string createTableHead(string[] head)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("<tr 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;\">");
            for (int i = 0; i < head.Length; i++)
            {
                //if (i == 0) sb.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;\">" + head[i] + "</td>");
                //else
                    sb.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;\">" + head[i] + "</td>");
            }
            sb.Append("</tr>");
            return sb.ToString();
        }
        /// <summary>
        /// 表体（内容）
        /// </summary>
        /// <param name="starttime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        private string createTableBody(string starttime, string endtime)
        {
            string time = "";
            if (starttime != null && starttime.Trim() != "")
            {
                time += " and CONVERT(varchar , BeginTime, 120)>=CONVERT(varchar , '" + starttime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                time += " and CONVERT(varchar , BeginTime, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }
            //存储数据
            Dictionary<string, List<List<string>>> body = new Dictionary<string, List<List<string>>>();
            //坐席
            DataTable dtable = new BLL.T_Sys_UserAccount().GetList("F_RoleId in (1,4,17,28,29)").Tables[0];//客服坐席、班长坐席、售前坐席、投诉坐席
            int allcount = dtable.Rows.Count;
            for (int i = 0; i < allcount; i++)
            {
                int userid = Convert.ToInt32(dtable.Rows[i]["F_UserId"].ToString());
                List<string> bodyitem = new List<string>();
                string[] UNDN = getRoleNameAndUserName(userid);
                bodyitem.Add(UNDN[1]);
                bodyitem.Add(UNDN[2]);
                //bodyitem.Add(UNDN[3]);
                DataTable dt = new BLL.T_Call_CallRecords().GetList("UserId='" + userid + "'" + time).Tables[0];
                string[] cp = getMYDCountAndPercent1(dt);
                bodyitem.AddRange(cp);

                ////1非常满意、2基本满意、3不满意、4未评价
                //for (int j = 1; j <= 4; j++)
                //{
                //    string[] cp = getMYDCountAndPercent(dt, j);
                //    bodyitem.AddRange(cp);
                //}
                if (body.ContainsKey(UNDN[0]))
                {
                    body[UNDN[0]].Add(bodyitem);
                }
                else
                {
                    List<List<string>> bodylist = new List<List<string>>();
                    bodylist.Add(bodyitem);
                    body.Add(UNDN[0], bodylist);
                }
            }
            StringBuilder sb = new StringBuilder();
            foreach (string key in body.Keys)
            {
                bool bb = true;
                int count = body[key].Count;
                foreach (List<string> listiteam in body[key])
                {
                    sb.Append("<tr style=\"text-align: center;\" onmouseover=\"this.style.backgroundColor='#F6F6F6'\" onmouseout=\"this.style.backgroundColor='#ffffff'\">");
                    if (bb)
                    {
                        sb.Append("<td rowspan=\"" + count + "\" 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;\">" + key + "</td>");
                        bb = false;
                    }
                    foreach (string item in listiteam)
                    {
                        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;\">" + item + "</td>");
                    }
                    sb.Append("</tr>");
                }
                
            }
            return sb.ToString();
        }
        private string createTableBody1(string starttime, string endtime)
        {
            //存储数据
            List<List<string>> body = new List<List<string>>();
            //坐席
            DataTable dtable = new BLL.T_Sys_UserAccount().GetList("F_RoleId in (1,4,28,29)").Tables[0];//客服坐席、班长坐席、售前坐席、投诉坐席
            int count = dtable.Rows.Count;
            for (int i = 0; i < count; i++)
            {
                //1非常满意、2基本满意、3不满意
                List<string> bodyitem = new List<string>();
                int userid = Convert.ToInt32(dtable.Rows[i]["F_UserId"].ToString());
                string sqlwhere = string.Format("UserId={0} AND EndTime>='{1}' AND EndTime<'{2}'", userid, starttime, endtime);
                DataTable dt = new BLL.T_Call_CallRecords().GetList(sqlwhere).Tables[0];
                string[] UNDN = getRoleNameAndUserName(userid);
                bodyitem.AddRange(UNDN);
                for (int j = 0; j < 4; j++)
                {
                    string[] cp = getMYDCountAndPercent(dt, j);
                    bodyitem.AddRange(cp);
                }
                body.Add(bodyitem);
            }
            body.Sort(new ListCompare());
            StringBuilder sb = new StringBuilder();
            foreach (var list in body)
            {
                sb.Append("<tr style=\"text-align: center;\" onmouseover=\"this.style.backgroundColor='#F6F6F6'\" onmouseout=\"this.style.backgroundColor='#ffffff'\">");
                foreach (var listiteam in list)
                {
                    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;\">" + listiteam + "</td>");
                }
                sb.Append("</tr>");
            }
            return sb.ToString();
        }
        /// <summary>
        /// 每种满意度的个数和占比
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        private string[] getMYDCountAndPercent(DataTable dt, int type)
        {
            int allcount = dt.Rows.Count;
            int typecount = 0;
            if (type == 4) typecount = dt.Select("MYD IS NULL").Count();
            else typecount = dt.Select("MYD=" + type).Count();
            string percent = "0.00";
            if (allcount > 0) percent = (typecount * 1.0 / allcount).ToString("0.00");
            return new string[] { typecount.ToString(), percent };
        }

        private string[] getMYDCountAndPercent1(DataTable dt)
        {
            int allcount = dt.Rows.Count;
            int my = 0;//满意
            int yb = 0;//一般
            int myc = 0;//满意总量
            int bmy = 0;//不满意
            int pjc = 0;//评价量
            string myl = "0";//满意率
            string pjl = "0";//评价率

            my = dt.Select("MYD=1").Count();
            yb = dt.Select("MYD=2").Count();
            myc = my + yb;
            bmy= dt.Select("MYD=3").Count();
            pjc= dt.Select("MYD IS not NULL").Count();

            if (pjc > 0) myl = (myc * 100.0 / pjc).ToString("0.00") + "%";
            if (allcount > 0) pjl= (pjc * 100.0 / allcount).ToString("0.00") + "%";
            return new string[] { my.ToString(), yb.ToString(), bmy.ToString(), pjc.ToString(), myl, pjl };
        }

        /// <summary>
        /// 通过UserId得到角色和姓名
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        private string[] getRoleNameAndUserName(int userid)
        {
            var user = new BLL.T_Sys_UserAccount().GetModel(userid);
            string[] resarr = new string[3];
            if (user != null)
            {
                var role = new BLL.T_Sys_RoleInfo().GetModel(user.F_RoleId);
                if (role != null)
                {
                    resarr[0] = role.F_RoleName;
                }
                resarr[1] = user.F_UserCode;
                resarr[2] = user.F_UserName;
                //var dept = new BLL.T_Sys_Department().GetModel(user.F_DeptId);
                //if (dept != null)
                //{
                //    resarr[3] = dept.F_DeptName;
                //}
            }
            return resarr;
        }
    }
    class ListCompare : IComparer<List<string>>
    {
        public int Compare(List<string> a, List<string> b)
        {
            return a[0].CompareTo(b[0]);
        }
    }
}