SZRXYS/BaseCallCent.../reportmanage/dataanalysis/DepartmentWo...

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
{
    public partial class DepartmentWorkOrder : System.Web.UI.Page
    {
        protected string TableHtml = "";
        protected void Page_Load(object sender, EventArgs e)
        {
            OutputTable();
        }
        protected void btnConfirm_Click(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; }
            StringBuilder sb = new StringBuilder();
            //表名称
            sb.Append(createTableTitle("客服部工单分布"));
            //表列名
            sb.Append("<table border='0' cellpadding='0' cellspacing='0' width='100%' align='center'>");
            sb.Append(createTableHead(new string[] { "客服部名称", "工单数量", "总处理时间(小时)", "平均响应时间(小时)", "平均处理时间(小时)" }));
            //表内容
            sb.Append(createTableBody(starttime, endtime));
            sb.Append("</table>");
            TableHtml = sb.ToString();
        }
        private string createTableBody(string starttime, string endtime)
        {
            //存储数据的数据字典
            Dictionary<string, List<string>> body = new Dictionary<string, List<string>>();
            //这段时间已完成的报修工单
            string sql = string.Format("F_WORKORDERSTATEID=0 and F_WORKORDERTYPEID={0}", 16);//16为报修
            if (starttime != "")
            {
                sql += " AND F_STARTTIME>='" + starttime + " 00:00:00'";
            }
            if (endtime != "")
            {
                sql += " AND F_ENDTIME<='" + endtime + " 23:59:59'";
            }
            DataTable dtable = new BLL.T_Wo_WorkOrderBase().GetList(sql).Tables[0];
            //工单总数
            int allcount = dtable.Rows.Count;
            #region 遍历工单找到每个工单所属的部门，总接单和处理时间，总结单时间，总处理时间
            for (int i = 0; i < allcount; i++)
            {
                //处理这个工单工程师
                DataTable workorderbase = new BLL.T_Wo_WorkOrderBase().GetList("F_INSTANCEID=" + dtable.Rows[i]["F_INSTANCEID"]).Tables[0];
                if (workorderbase.Rows.Count > 0)
                {
                    List<Model.T_Wo_WorkOrderConstructor> constructorlist =
                        new BLL.T_Wo_WorkOrderConstructor().GetModelList("F_WORKORDERID=" + workorderbase.Rows[0]["F_WORKORDERID"]);
                    if (constructorlist.Count > 0)
                    {
                        foreach (Model.T_Wo_WorkOrderConstructor constructor in constructorlist)
                        {
                            if (constructor.F_ISMAIN == 1)
                            {
                                int id = Convert.ToInt32(constructor.F_USERID);
                                Model.T_Sys_UserAccount user = new BLL.T_Sys_UserAccount().GetModel(id);
                                if (user != null)
                                {
                                    Model.T_Sys_Department dept = new BLL.T_Sys_Department().GetModel(user.F_DeptId);
                                    if (dept != null)
                                    {
                                        string instanceid = dtable.Rows[i]["F_INSTANCEID"].ToString();
                                        //这个工单是某个部门的工程师处理的就存储起来，如果还有其他工单也是此部门处理的就增加1
                                        if (body.ContainsKey(dept.F_DeptName))
                                        {
                                            body[dept.F_DeptName][0] = (Convert.ToInt32(body[dept.F_DeptName][0]) + 1).ToString();
                                            body[dept.F_DeptName][1] = (Convert.ToInt32(body[dept.F_DeptName][1]) + Convert.ToInt32(getSumTime(instanceid, "6,7"))).ToString();
                                            body[dept.F_DeptName][2] = (Convert.ToInt32(body[dept.F_DeptName][2]) + Convert.ToInt32(getSumTime(instanceid, "6"))).ToString();
                                            body[dept.F_DeptName][3] = (Convert.ToInt32(body[dept.F_DeptName][3]) + Convert.ToInt32(getSumTime(instanceid, "7"))).ToString();
                                        }
                                        else
                                        {
                                            body.Add(dept.F_DeptName, new List<string>() { "1", getSumTime(instanceid, "6,7"), getSumTime(instanceid, "6"), getSumTime(instanceid, "7") });
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
            #endregion
            StringBuilder sb = new StringBuilder();
            //排序方法一
            var dicSort = from objDic in body orderby int.Parse(objDic.Value[0]) descending select objDic;
            #region 排序方法二
            //排序方法二
            //List<KeyValuePair<string, List<string>>> lst = new List<KeyValuePair<string, List<string>>>(body);
            //lst.Sort(delegate(KeyValuePair<string, List<string>> s1, KeyValuePair<string, List<string>> s2)
            //{
            //    int s10 = int.Parse(s1.Value[0]);
            //    int s20 = int.Parse(s2.Value[0]);
            //    return s20.CompareTo(s10);
            //});
            //body.Clear(); 
            #endregion
            foreach (var kvp in dicSort)
            {
                List<string> Values = kvp.Value;
                Values[1] = GetTimeInfo((Convert.ToDouble(Values[1]) * 1.0 / Convert.ToInt32(Values[0])).ToString("0.00"));
                Values[2] = GetTimeInfo((Convert.ToDouble(Values[2]) * 1.0 / Convert.ToInt32(Values[0])).ToString("0.00"));
                Values[3] = GetTimeInfo((Convert.ToDouble(Values[3]) * 1.0 / Convert.ToInt32(Values[0])).ToString("0.00"));
                string tdcolor = "#fff"; if (kvp.Key == "合计：") tdcolor = "#D5EDFE";
                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: " + tdcolor + ";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;\">" + kvp.Key + "</td>");
                foreach (var value in Values)
                {
                    sb.Append("<td class=\"formtabletitle1\" style=\"height: 25px;background-color: " + tdcolor + ";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;\">" + value + "</td>");
                }
                sb.Append("</tr>");
            }
            #region 合计
            //合计
            List<string> sum = new List<string>();
            long count = 0;
            double sumaveragetime = 0.0;
            double averagereceivetime = 0.0;
            double averagedealwithtime = 0.0;
            foreach (var key in body.Keys)
            {
                List<string> Values = body[key];
                count += Convert.ToInt32(Values[0]);
                sumaveragetime += Convert.ToDouble(Values[1]);
                averagereceivetime += Convert.ToDouble(Values[2]);
                averagedealwithtime += Convert.ToDouble(Values[3]);
            }
            sum.Add(count.ToString()); sum.Add(sumaveragetime.ToString());
            sum.Add(averagereceivetime.ToString()); sum.Add(averagedealwithtime.ToString());
            body.Clear();
            body.Add("合计：", sum);
            foreach (var key in body.Keys)
            {
                List<string> Values = body[key];
                Values[1] = (Convert.ToDouble(Values[1]) * 1.0 / Convert.ToInt32(Values[0])).ToString("0.00");
                Values[2] = (Convert.ToDouble(Values[2]) * 1.0 / Convert.ToInt32(Values[0])).ToString("0.00");
                Values[3] = (Convert.ToDouble(Values[3]) * 1.0 / Convert.ToInt32(Values[0])).ToString("0.00");
                string tdcolor = "#fff"; if (key == "合计：") tdcolor = "#D5EDFE";
                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: " + tdcolor + ";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>");
                foreach (var value in Values)
                {
                    sb.Append("<td class=\"formtabletitle1\"style=\"height: 25px;background-color: " + tdcolor + ";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;\">" + value + "</td>");
                }
                sb.Append("</tr>");
            } 
            #endregion
            return sb.ToString();
        }
        /// <summary>
        /// 时间转换(将秒转换为易读的时间)
        /// </summary>
        /// <param name="times">秒</param>
        /// <returns></returns>
        public string GetTimeInfo1(string times)
        {
            string res = "";
            try
            {
                double d = Convert.ToDouble(times);
                int t = Convert.ToInt32(d);
                if (t < 60)
                {
                    res = "" + t + "秒";
                }
                else if (t < 3600)
                {
                    res = "" + Convert.ToInt32(t / 60).ToString() + "分" + "" + Convert.ToInt32(t % 60).ToString() + "秒";
                }
                else if (t < 86400)
                {
                    res = "" + Convert.ToInt32(t / 3600).ToString() + "时" + "" + Convert.ToInt32((t % 3600) / 60).ToString() + "分" + "" + Convert.ToInt32((t % 3600) % 60).ToString() + "秒";
                }
                else
                {
                    res = "" + Convert.ToInt32(t / 86400).ToString() + "天" + "" + Convert.ToInt32((t % 86400) / 3600).ToString() + "时" + "" + Convert.ToInt32(((t % 86400) % 3600) / 60).ToString() + "分" + "" + Convert.ToInt32(((t % 86400) % 3600) % 60).ToString() + "秒";
                }
            }
            catch
            { }
            return res;
        }
        public string GetTimeInfo(string times)
        {
            string res = "";
            try
            {
                double d = Convert.ToDouble(times);
                res = (d / 3600).ToString("0.00");
            }
            catch
            { }
            return res;
        }
        private string getSumTime(string instanceid, string stateids)
        {
            string sqlwhere = "SELECT sum([F_ELAPSEDTIME]) FROM [T_Wo_WorkOrderHistory] WHERE F_INSTANCEID='" + instanceid + "' AND F_WORKORDERSTATEID IN (" + stateids + ")";
            DataTable dt = DBUtility.DbHelperSQL.Query(sqlwhere).Tables[0];
            long l = 0;
            long.TryParse(dt.Rows[0][0].ToString(), out l);
            return l.ToString();
        }
        private static void NewMethod(StringBuilder sb)
        {
            //表内容
            List<Model.T_Sys_Department> deptlist = new BLL.T_Sys_Department().GetModelList("F_DeptName LIKE '%客服部%'");
            foreach (Model.T_Sys_Department dept in deptlist)
            {
                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;\">" + dept.F_DeptName + "</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;\">0</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;\">0</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;\">0</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;\">0</td>");
                sb.Append("</tr>");
            }
            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: #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;\">合计：</td>");
            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;\">0</td>");
            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;\">0</td>");
            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;\">0</td>");
            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;\">0</td>");
            sb.Append("</tr>");
        }

        /// <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++)
            {
                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();
        }
        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();
        }
    }
}