SZRXYS/BaseCallCent.../reportmanage/dataanalysis/BasicEnginee...

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 BasicEngineerWorkOrder : System.Web.UI.Page
    {
        protected string TableHtml = null;
        protected void Page_Load(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 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[] { "客服部名称", "一线工程师姓名", "工单数量", "总处理时间(小时)", "平均响应时间(小时)", "平均处理时间(小时)" };
            TableHtml = createTable(tabletitle, tablehead, starttime, endtime);
        }
        protected 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++)
            {
                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="head"></param>
        /// <returns></returns>
        protected string createTableBody(string starttime, string endtime)
        {
            //存储数据的数据字典
            Dictionary<string, Dictionary<int, List<string>>> body = new Dictionary<string, Dictionary<int, List<string>>>();
            Dictionary<string, Dictionary<int, List<string>>> newbody = new Dictionary<string, Dictionary<int, 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 = dtable.Select("F_INSTANCEID=" + dtable.Rows[i]["F_INSTANCEID"]).CopyToDataTable();
                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)
                                {
                                    if (user.F_RoleId == 30)
                                    {
                                        string instanceid = dtable.Rows[i]["F_INSTANCEID"].ToString();
                                        string[] UNDN = getUserNameAndDeptName(user.F_UserId);
                                        if (body.ContainsKey(UNDN[1]))
                                        {
                                            if (body[UNDN[1]].ContainsKey(user.F_UserId))
                                            {
                                                body[UNDN[1]][user.F_UserId][0] = (Convert.ToInt32(body[UNDN[1]][user.F_UserId][0]) + 1).ToString();
                                                body[UNDN[1]][user.F_UserId][1] = (Convert.ToInt32(body[UNDN[1]][user.F_UserId][1]) + Convert.ToInt32(getSumTime(instanceid, "6,7"))).ToString();
                                                body[UNDN[1]][user.F_UserId][2] = (Convert.ToInt32(body[UNDN[1]][user.F_UserId][2]) + Convert.ToInt32(getSumTime(instanceid, "6"))).ToString();
                                                body[UNDN[1]][user.F_UserId][3] = (Convert.ToInt32(body[UNDN[1]][user.F_UserId][3]) + Convert.ToInt32(getSumTime(instanceid, "7"))).ToString();
                                            }
                                            else
                                            {
                                                body[UNDN[1]].Add(user.F_UserId, new List<string>() { "1", getSumTime(instanceid, "6,7"), getSumTime(instanceid, "6"), getSumTime(instanceid, "7") });
                                            }
                                        }
                                        else
                                        {
                                            Dictionary<int, List<string>> bodylist = new Dictionary<int, List<string>>();
                                            bodylist.Add(user.F_UserId, new List<string>() { "1", getSumTime(instanceid, "6,7"), getSumTime(instanceid, "6"), getSumTime(instanceid, "7") });
                                            body.Add(UNDN[1], bodylist);
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
            #endregion

            StringBuilder sb = new StringBuilder();
            #region 部门合计
            foreach (var keys in body.Keys)
            {
                Dictionary<int, List<string>> Values = body[keys];
                Dictionary<int, List<string>> newValues = new Dictionary<int, List<string>>();

                List<string> sum = new List<string>() { "0", "0", "0", "0" };
                foreach (var kv in Values)
                {
                    List<string> values = kv.Value;
                    sum[0] = (int.Parse(sum[0]) + int.Parse(values[0])).ToString();
                    sum[1] = (int.Parse(sum[1]) + int.Parse(values[1])).ToString();
                    sum[2] = (int.Parse(sum[2]) + int.Parse(values[2])).ToString();
                    sum[3] = (int.Parse(sum[3]) + int.Parse(values[3])).ToString();
                    newValues.Add(kv.Key, kv.Value);
                }
                newValues.Add(-1, sum);
                newbody.Add(keys, newValues);
            } 
            #endregion
            #region 按部门排序
            var KeysValues = from objDic in newbody orderby int.Parse(objDic.Value[-1][0]) descending select objDic;
            foreach (var keyvalue in KeysValues)
            {
                #region 打印表格--排序
                Dictionary<int, List<string>> bodyValues = keyvalue.Value;
                int count = bodyValues.Count;
                bool bb = true;
                var Values = from objDic in bodyValues orderby int.Parse(objDic.Value[0]) descending select objDic;
                foreach (var kv in Values)
                {
                    List<string> values = kv.Value;
                    values[1] = GetTimeInfo((Convert.ToInt32(values[1]) * 1.0 / Convert.ToInt32(values[0])).ToString("0.00"));
                    values[2] = GetTimeInfo((Convert.ToInt32(values[2]) * 1.0 / Convert.ToInt32(values[0])).ToString("0.00"));
                    values[3] = GetTimeInfo((Convert.ToInt32(values[3]) * 1.0 / Convert.ToInt32(values[0])).ToString("0.00"));
                    if (kv.Key != -1)
                    {
                        sb.Append("<tr style=\"text-align: center;\" onmouseover=\"this.style.backgroundColor='#F6F6F6'\" onmouseout=\"this.style.backgroundColor='#ffffff'\">");

                        if (bb)
                        {
                            sb.Append("<td rowspan=\"" + (count - 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;\">" + keyvalue.Key + "</td>");
                            bb = false;
                        }

                        string[] undn = getUserNameAndDeptName(kv.Key);
                        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;\">" + undn[0] + "</td>");
                        foreach (var v in values)
                        {
                            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;\">" + v + "</td>");
                        }
                        sb.Append("</tr>");
                    }
                }
                #endregion
            } 
            #endregion
            #region 未按部门排序
            //foreach (var keys in body.Keys)
            //{
            //    #region 打印表格--排序
            //    Dictionary<int, List<string>> bodyValues = body[keys];
            //    int count = bodyValues.Count;
            //    bool bb = true;
            //    var Values = from objDic in bodyValues orderby int.Parse(objDic.Value[0]) descending select objDic;
            //    foreach (var kv in Values)
            //    {
            //        List<string> values = kv.Value;
            //        values[1] = GetTimeInfo((Convert.ToInt32(values[1]) * 1.0 / Convert.ToInt32(values[0])).ToString("0.00"));
            //        values[2] = GetTimeInfo((Convert.ToInt32(values[2]) * 1.0 / Convert.ToInt32(values[0])).ToString("0.00"));
            //        values[3] = GetTimeInfo((Convert.ToInt32(values[3]) * 1.0 / Convert.ToInt32(values[0])).ToString("0.00"));
            //        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;\">" + keys + "</td>");
            //            bb = false;
            //        }
            //        string[] undn = getUserNameAndDeptName(kv.Key);
            //        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;\">" + undn[0] + "</td>");
            //        foreach (var v in values)
            //        {
            //            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;\">" + v + "</td>");
            //        }
            //        sb.Append("</tr>");
            //    }
            //    #endregion
            //    #region 打印表格--未排序
            //    //Dictionary<int, List<string>> Values = body[keys];
            //    //int count = Values.Count;
            //    //bool bb = true;
            //    //foreach (var k in Values.Keys)
            //    //{
            //    //    List<string> values = Values[k];
            //    //    values[1] = (Convert.ToInt32(values[1]) * 1.0 / Convert.ToInt32(values[0])).ToString("0.00");
            //    //    values[2] = (Convert.ToInt32(values[2]) * 1.0 / Convert.ToInt32(values[0])).ToString("0.00");
            //    //    values[3] = (Convert.ToInt32(values[3]) * 1.0 / Convert.ToInt32(values[0])).ToString("0.00");
            //    //    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;\">" + keys + "</td>");
            //    //        bb = false;
            //    //    }
            //    //    string[] undn = getUserNameAndDeptName(k);
            //    //    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;\">" + undn[0] + "</td>");
            //    //    foreach (var v in values)
            //    //    {
            //    //        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;\">" + v + "</td>");
            //    //    }
            //    //    sb.Append("</tr>");
            //    //}
            //    #endregion
            //}
            #endregion
            return sb.ToString();
        }
        protected string createTableBody1(string starttime, string endtime)
        {
            //存储数据的数据字典
            Dictionary<int, List<string>> body = new Dictionary<int, 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)
                                {
                                    if (user.F_RoleId == 30)
                                    {
                                        string instanceid = dtable.Rows[i]["F_INSTANCEID"].ToString();
                                        //这个工单是某个部门的工程师处理的就存储起来，如果还有其他工单也是此部门处理的就增加1
                                        if (body.ContainsKey(user.F_UserId))
                                        {
                                            body[user.F_UserId][0] = (Convert.ToInt32(body[user.F_UserId][0]) + 1).ToString();
                                            body[user.F_UserId][1] = (Convert.ToInt32(body[user.F_UserId][1]) + Convert.ToInt32(getSumTime(instanceid, "6,7"))).ToString();
                                            body[user.F_UserId][2] = (Convert.ToInt32(body[user.F_UserId][2]) + Convert.ToInt32(getSumTime(instanceid, "6"))).ToString();
                                            body[user.F_UserId][3] = (Convert.ToInt32(body[user.F_UserId][3]) + Convert.ToInt32(getSumTime(instanceid, "7"))).ToString();
                                        }
                                        else
                                        {
                                            body.Add(user.F_UserId, new List<string>() { "1", getSumTime(instanceid, "6,7"), getSumTime(instanceid, "6"), getSumTime(instanceid, "7"), getUserNameAndDeptName(user.F_UserId)[1] });
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
            #endregion

            StringBuilder sb = new StringBuilder();
            foreach (var key in body.Keys)
            {
                List<string> Values = body[key];
                Values[1] = (Convert.ToInt32(Values[1]) * 1.0 / Convert.ToInt32(Values[0])).ToString("0.00");
                Values[2] = (Convert.ToInt32(Values[2]) * 1.0 / Convert.ToInt32(Values[0])).ToString("0.00");
                Values[3] = (Convert.ToInt32(Values[3]) * 1.0 / Convert.ToInt32(Values[0])).ToString("0.00");
                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;\">" + Values[4] + "</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;\">" + getUserNameAndDeptName(key)[0] + "</td>");
                for (int i = 0; i < Values.Count(); i++)
                {
                    if (i == 4) continue;
                    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;\">" + Values[i] + "</td>");
                }
                sb.Append("</tr>");
            }
            return sb.ToString();
        }
        /// <summary>
        /// 时间转换(将秒转换为易读的时间)
        /// </summary>
        /// <param name="times">秒</param>
        /// <returns></returns>
        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 string[] getUserNameAndDeptName(int userid)
        {
            var user = new BLL.T_Sys_UserAccount().GetModel(userid);
            string[] resarr = new string[2];
            if (user != null)
            {
                resarr[0] = user.F_UserName;
                var dept = new BLL.T_Sys_Department().GetModel(user.F_DeptId);
                if (dept != null)
                {
                    resarr[1] = dept.F_DeptName;
                }
            }
            return resarr;
        }
        protected void btnConfirm_Click(object sender, EventArgs e)
        {
            OutputTable();
        }
    }
}