SZRXYS/BaseCallCent.../reportmanage/dataanalysis/CallInPerson...

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.dataanalysis
{
    public partial class CallInPerson : System.Web.UI.Page
    {
        protected string TableHtml = null;
        protected void Page_Load(object sender, EventArgs e)
        {
            #region Demo
            //PrintTable();
            //string[] head = { "日期", "工单量", "一线技术员工单量", "一线技术员工占比", "代理技术员工单量", "代理技术员占比", "客户工单量", "客户占比"};
            //string[,] body = new string[30, 8];
            //for (int i = 0; i < 30; i++)
            //{
            //    for (int j = 0; j < 8; j++)
            //    {
            //        if (j == 0)
            //        {
            //            body[i, j] = "2015/4/" + (i + 1).ToString();
            //        }
            //        else body[i, j] = "";
            //    }
            //}
            //string[] sumiteam = { "合计：", "工单总量", "一线技术员工单总量", "一线技术员工占比", "代理技术员工单总量", "代理技术员占比", "客户工单总量", "客户占比" };
            //StringBuilder sb = new StringBuilder();
            //sb.Append(createTableTitle("来电人员工单分布"));
            //sb.Append(createTable1(head, body, sumiteam));
            //TableHtml = sb.ToString(); 
            #endregion
            OutputTable();
        }

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

            string[] head = { "日期", "工单量", "一线技术员工单量", "一线技术员工占比", "代理技术员工单量", "代理技术员占比", "客户工单量", "客户占比" };
            TableHtml = createTable("来电人员性质工单分布", head, starttime, endtime);
        }
        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();
        }
        private string createTableHead(string[] head)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("<tr style=\"text-align: center;\">");
            for (int i = 0; i < head.Length; i++)
            {
                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();
        }
        private string createTableBody(string starttime, string endtime)
        {
            //收集数据
            List<List<string>> body = new List<List<string>>();
            for (DateTime dt = Convert.ToDateTime(starttime); dt <= Convert.ToDateTime(endtime); dt = dt.AddDays(1))
            {
                List<string> bodyiteam = new List<string>();
                bodyiteam.Add(dt.ToShortDateString());
                string sql = string.Format(" F_WORKORDERTYPEID={0}", 16);
                sql += " AND F_STARTTIME>='" + dt.ToShortDateString() + " 00:00:00'";
                sql += " AND F_STARTTIME<='" + dt.ToShortDateString() + " 23:59:59'";
               // sql += " AND F_ENDTIME IS NOT NULL AND F_ENDTIME !=''";
                DataTable dtable = new BLL.T_Wo_WorkOrderBase().GetList(sql).Tables[0];
                //今天的总工单量
                int todaycount = dtable.Rows.Count;
                bodyiteam.Add(todaycount.ToString());
                List<Model.T_Sys_DictionaryValue> modellist = new BLL.T_Sys_DictionaryValue().GetModelList("F_DictionaryFlag='bxrxz'");//所有报修人性质
                foreach (var model in modellist)
                {
                    //这种性质的报修人的工单量
                    int thistypecount = 0;
                    //这种性质的报修人的工单百分比
                    string percent = null;
                    if (model.F_Name == "一线技术员")
                        thistypecount = dtable.Select("F_STANDARDADDRESS='" + model.F_Name + "' OR F_STANDARDADDRESS IS NULL OR F_STANDARDADDRESS=''").Length;
                    else thistypecount = dtable.Select("F_STANDARDADDRESS='" + model.F_Name + "'").Length;
                    if (todaycount == 0) percent = "0.00%";
                    else percent = (thistypecount * 1.0 / todaycount * 100).ToString("0.00") + "%";
                    bodyiteam.Add(thistypecount.ToString());
                    bodyiteam.Add(percent);
                }
                dtable.Dispose();
                body.Add(bodyiteam);
            }
            //打印表格
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < body.Count;i++ )
            {
                if (body[i][1] != "0")
                {
                    sb.Append("<tr style=\"text-align: center;\" onmouseover=\"this.style.backgroundColor='#F6F6F6'\" onmouseout=\"this.style.backgroundColor='#ffffff'\">");
                    foreach (var listiteam in body[i])
                    {
                        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>");
                }
            }
            //合计数据
            List<string> sum = new List<string>();
            sum = getSumCountAndPercent(starttime, endtime, 16);
            sb.Append("<tr style=\"text-align: center;\" onmouseover=\"this.style.backgroundColor='#F6F6F6'\" onmouseout=\"this.style.backgroundColor='#ffffff'\">");
            for (int j = 0; j < sum.Count; j++)
            {
                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;\">" + sum[j] + "</td>");
            }
            sb.Append("</tr>");
            return sb.ToString();
        }
        private List<string> getSumCountAndPercent(string starttime, string endtime, int type)
        {
            List<string> sum = new List<string>();
            sum.Add("合计：");
            string sql = string.Format(" F_WORKORDERTYPEID={0}", type);
            if (starttime != "")
            {
                sql += " AND F_STARTTIME>='" + starttime + " 00:00:00'";
            }
            if (endtime != "")
            {
                sql += " AND F_STARTTIME<='" + endtime + " 23:59:59'";
            }
            DataTable dt = new BLL.T_Wo_WorkOrderBase().GetList(sql).Tables[0];
            //工单总数
            int allcount = dt.Rows.Count;
            sum.Add(allcount.ToString());

            List<Model.T_Sys_DictionaryValue> modellist = new BLL.T_Sys_DictionaryValue().GetModelList("F_DictionaryFlag='bxrxz'");//所有报修人性质
            foreach (var model in modellist)
            {
                int count = 0;
                if (model.F_Name == "一线技术员")
                    count = dt.Select("F_STANDARDADDRESS is null or F_STANDARDADDRESS='' or F_STANDARDADDRESS='" + model.F_Name + "'").Length;
                else count = dt.Select("F_STANDARDADDRESS='" + model.F_Name + "'").Length;
                string average = "0.00%";
                if (allcount != 0) average = (count * 1.0 / allcount * 100).ToString("0.00") + "%";
                sum.Add(count.ToString());
                sum.Add(average);
            }
            dt.Dispose();
            return sum;
        }
        protected void btnConfirm_Click(object sender, EventArgs e)
        {
            //PrintTable();
            OutputTable();
        }
        private void PrintTable()
        {
            //查询条件
            string starttime = txtStartTime.Text.Trim();
            if (string.IsNullOrWhiteSpace(starttime))
                starttime = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).ToShortDateString();
            string endtime = txtEndTime.Text.Trim();
            if (string.IsNullOrWhiteSpace(endtime)) endtime = DateTime.Today.AddDays(-1).ToShortDateString();
            //工单总数
            int workordercount = getWorkOrderCount(starttime, endtime, 16);
            //打印表
            List<Model.T_Sys_DictionaryValue> modellist = new BLL.T_Sys_DictionaryValue().GetModelList("F_DictionaryFlag='bxrxz'");
            //表列名
            List<string> head = new List<string>();
            head.Add("");
            foreach (var model in modellist)
                head.Add(model.F_Name);
            head.Add("其他");
            //表主体(最多支持10种性质的报修人,即：可以在报修人性质字典中添加10种性质的报修人)
            string[,] body = new string[,] { { "工单数量", "", "", "", "", "", "", "", "", "", "", ""}, 
                                             { "工单占比", "", "", "", "", "", "", "", "", "", "", ""} };
            for (int i = 0; i < head.Count; i++)
            {
                if (head[i] != "")
                {
                    //各种性质报修人的工单数和百分比
                    string[] cp = getWorkOrderCountAndPercent(starttime, endtime, 16, head[i]);
                    body[0, i] = cp[0];
                    body[1, i] = cp[1];
                }
            }
            string[] sumiteam = new string[] { "工单总数", workordercount.ToString() };
            StringBuilder sb = new StringBuilder();
            sb.Append(createTableTitle("来电人员工单分布"));
            sb.Append(createTable(head.ToArray(), body, sumiteam));
            TableHtml = sb.ToString();
        }
        /// <summary>
        /// 工单总数量
        /// </summary>
        /// <returns></returns>
        private int getWorkOrderCount(string starttime, string endtime, int type)
        {
            string sql = string.Format("F_WORKORDERTYPEID={0}", type);
            if (starttime != "")
            {
                sql += " AND F_STARTTIME>='" + starttime + " 00:00:00'";
            }
            if (endtime != "")
            {
                sql += " AND F_STARTTIME<='" + endtime + " 23:59:59'";
            }
            return new BLL.T_Wo_WorkOrderBase().GetList(sql).Tables[0].Rows.Count;
        }
        /// <summary>
        /// 不同来电人性质的工单数及百分率
        /// </summary>
        /// <param name="starttime">开始时间</param>
        /// <param name="endtime">结束时间</param>
        /// <param name="type">工单类型</param>
        /// <param name="callinpersontype">来电人性质</param>
        /// <returns></returns>
        private string[] getWorkOrderCountAndPercent(string starttime, string endtime, int type, string callinpersontype)
        {
            string sql = string.Format(" F_WORKORDERTYPEID={0}", type);
            if (starttime != "")
            {
                sql += " AND F_STARTTIME>='" + starttime + " 00:00:00'";
            }
            if (endtime != "")
            {
                sql += " AND F_STARTTIME<='" + endtime + " 23:59:59'";
            }
            DataTable dt = new BLL.T_Wo_WorkOrderBase().GetList(sql).Tables[0];
            //工单总数
            int allcount = dt.Rows.Count;
            string[] arr = new string[2];
            int count = 0;
            if (callinpersontype == "一线技术员")
                count = dt.Select("F_STANDARDADDRESS is null or F_STANDARDADDRESS='' or F_STANDARDADDRESS='" + callinpersontype + "'").Length;
            else count = dt.Select("F_STANDARDADDRESS='" + callinpersontype + "'").Length;
            dt.Dispose();
            arr[0] = count.ToString();
            arr[1] = (count * 1.0 / allcount * 100).ToString("0.00") + "%";
            return arr;
        }

        /// <summary>
        /// 表头（表列名）
        /// </summary>
        /// <param name="head">列名数组</param>
        /// <returns></returns>
        protected string createTable(string[] head, string[,] body, string[] sumiteam)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("<table border='0' cellpadding='0' cellspacing='0' width='100%' align='center'>");
            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++)
            {
                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>");
            sb.Append(createTableBody(head, body, sumiteam));
            sb.Append("</table>");
            return sb.ToString();
        }
        /// <summary>
        /// 表纵轴和横轴的名称
        /// </summary>
        /// <param name="dimensionname">数组长度为2。 dimensionname[0]：横轴名 dimensionname[1]：纵轴名</param>
        /// <returns></returns>
        private string createDimensionName(string[] dimensionname)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("<table style=\"width: 100%; height=100%\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\">");
            for (int i = 0; i < dimensionname.Length; i++)
            {
                sb.Append("<tr>");
                for (int j = 0; j < dimensionname.Length; j++)
                {
                    if (i == 0 && j != 0)
                        sb.Append("<td>" + dimensionname[i] + "</td>");
                    else if (i != 0 && j == 0)
                        sb.Append("<td>" + dimensionname[i] + "</td>");
                    else
                        sb.Append("<td></td>");
                }
                sb.Append("</tr>");
            }
            sb.Append("</table>");
            return sb.ToString();
        }
        private string createTableBody(string[] head, string[,] body, string[] sumiteam)
        {
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < body.GetLength(0); i++)
            {
                sb.Append("<tr style=\"text-align: center;\" onmouseover=\"this.style.backgroundColor='#F6F6F6'\" onmouseout=\"this.style.backgroundColor='#ffffff'\">");
                for (int j = 0; j < head.Length; j++)
                {
                    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;\">" + body[i, j] + "</td>");
                }
                sb.Append("</tr>");
            }
            sb.Append("<tr style=\"text-align: center;\" onmouseover=\"this.style.backgroundColor='#F6F6F6'\" onmouseout=\"this.style.backgroundColor='#ffffff'\">");
            for (int j = 0; j < sumiteam.Length; j++)
            {
                if (j > 0)
                    sb.Append("<td colspan=\"" + (head.Length - 1) + "\" 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;\">" + sumiteam[j] + "</td>");
                else 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;\">" + sumiteam[j] + "</td>");
            }
            sb.Append("</tr>");
            return sb.ToString();
        }
        protected string createTable1(string[] head, string[,] body, string[] sumiteam)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("<table border='0' cellpadding='0' cellspacing='0' width='100%' align='center'>");
            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++)
            {
                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>");
            sb.Append(createTableBody1(head, body, sumiteam));
            sb.Append("</table>");
            return sb.ToString();
        }
        private string createTableBody1(string[] head, string[,] body, string[] sumiteam)
        {
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < body.GetLength(0); i++)
            {
                sb.Append("<tr style=\"text-align: center;\" onmouseover=\"this.style.backgroundColor='#F6F6F6'\" onmouseout=\"this.style.backgroundColor='#ffffff'\">");
                for (int j = 0; j < head.Length; j++)
                {
                    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;\">" + body[i, j] + "</td>");
                }
                sb.Append("</tr>");
            }
            sb.Append("<tr style=\"text-align: center;\" onmouseover=\"this.style.backgroundColor='#F6F6F6'\" onmouseout=\"this.style.backgroundColor='#ffffff'\">");
            for (int j = 0; j < sumiteam.Length; j++)
            {
                sb.Append("<td class=\"formtabletitle1\" style=\"height: 25px;background-color: #E4D354;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;\">" + sumiteam[j] + "</td>");
            }
            sb.Append("</tr>");
            return sb.ToString();
        }
        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();
        }
    }
}