SZRXYS/BaseCallCent.../reportmanage/callreports/workorderrep...

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.callreports
{
    public partial class workorderreport : 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[] { "员工", "来电接通数", "总工单数量", "当日结束工单数量", "当日结束工单占比", "非当日结束工单数量", "非当日结束工单占比", "平均当日接单量", "平均当日结束工单量" };
            TableHtml = createTable(tabletitle, tablehead, 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();
        }
        /// <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 colspan=\"2\" 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();
        }
        private string createTableBody(string starttime, string endtime)
        {
            //存储数据的数据字典
            Dictionary<int, List<string>> body = new Dictionary<int, List<string>>();
            Dictionary<int, List<string>> body1 = new Dictionary<int, List<string>>();
            Dictionary<int, List<string>> body2 = new Dictionary<int, List<string>>();
            Dictionary<int, List<string>> body3 = 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'";
            }
            //sql += " AND F_STARTTIME IS NOT NULL AND F_ENDTIME IS NOT NULL";
            DataTable dtable = new BLL.T_Wo_WorkOrderBase().GetList(sql).Tables[0];
            //这段时间工单总数
            int allcount = dtable.Rows.Count;
            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)
                {
                    #region 工程师
                    //处理这个工单工程师
                    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)
                                {
                                    #region 一线工程师
                                    if (user.F_RoleId == 30)//一线工程师
                                    {
                                        string instanceid = dtable.Rows[i]["F_INSTANCEID"].ToString();
                                        //这个工单是某个部门的工程师处理的就存储起来，如果还有其他工单也是此部门处理的就增加1
                                        if (body2.ContainsKey(user.F_UserId))
                                        {
                                            body2[user.F_UserId][2] = (Convert.ToInt32(body2[user.F_UserId][2]) + 1).ToString();
                                            body2[user.F_UserId][3] = (Convert.ToInt32(body2[user.F_UserId][3]) + Convert.ToInt32(IsTodayFinish(workorderbase) ? 1 : 0)).ToString();
                                            body2[user.F_UserId][5] = (Convert.ToInt32(body2[user.F_UserId][5]) + Convert.ToInt32(IsTodayFinish(workorderbase) ? 0 : 1)).ToString();
                                        }
                                        else
                                        {
                                            //员工		来电接通数	总工单数量	当日结束工单数量	当日结束工单占比	
                                            //非当日结束工单数量	非当日结束工单占比	平均每日接单量	平均每日结束工单量		
                                            body2.Add(user.F_UserId, new List<string>() { "一线工程师", "-", "1", IsTodayFinish(workorderbase) ? "1" : "0", "", IsTodayFinish(workorderbase) ? "0" : "1", "", "", "" });
                                        }
                                    }
                                    #endregion
                                    #region 二线工程师
                                    else if (user.F_RoleId == 31)//二线工程师
                                    {
                                        string instanceid = dtable.Rows[i]["F_INSTANCEID"].ToString();
                                        //这个工单是某个部门的工程师处理的就存储起来，如果还有其他工单也是此部门处理的就增加1
                                        if (body3.ContainsKey(user.F_UserId))
                                        {
                                            body3[user.F_UserId][2] = (Convert.ToInt32(body3[user.F_UserId][2]) + 1).ToString();
                                            body3[user.F_UserId][3] = (Convert.ToInt32(body3[user.F_UserId][3]) + Convert.ToInt32(IsTodayFinish(workorderbase) ? 1 : 0)).ToString();
                                            body3[user.F_UserId][5] = (Convert.ToInt32(body3[user.F_UserId][5]) + Convert.ToInt32(IsTodayFinish(workorderbase) ? 0 : 1)).ToString();
                                        }
                                        else
                                        {
                                            //员工		来电接通数	总工单数量	当日结束工单数量	当日结束工单占比	
                                            //非当日结束工单数量	非当日结束工单占比	平均每日接单量	平均每日结束工单量		
                                            body3.Add(user.F_UserId, new List<string>() { "二线工程师", "-", "1", IsTodayFinish(workorderbase) ? "1" : "0", "", IsTodayFinish(workorderbase) ? "0" : "1", "", "", "" });
                                        }
                                    }
                                    #endregion
                                }
                            }
                        }
                    }
                    #endregion

                    #region 客服
                    int createuserid = 0;
                    int.TryParse(workorderbase.Rows[0]["F_CREATEBY"].ToString(), out createuserid);
                    Model.T_Sys_UserAccount userinfo = new BLL.T_Sys_UserAccount().GetModel(createuserid);
                    if (userinfo != null)
                    {
                        if (body1.ContainsKey(createuserid))
                        {
                            body1[createuserid][2] = (Convert.ToInt32(body1[createuserid][2]) + 1).ToString();
                            body1[createuserid][3] = (Convert.ToInt32(body1[createuserid][3]) + Convert.ToInt32(IsTodayFinish(workorderbase) ? 1 : 0)).ToString();
                            body1[createuserid][5] = (Convert.ToInt32(body1[createuserid][5]) + Convert.ToInt32(IsTodayFinish(workorderbase) ? 0 : 1)).ToString();
                        }
                        else
                        {
                            //员工		来电接通数	总工单数量	当日结束工单数量	当日结束工单占比	
                            //非当日结束工单数量	非当日结束工单占比	平均每日接单量	平均每日结束工单量		
                            body1.Add(createuserid, new List<string>() { "客服", CallInCount(createuserid, starttime, endtime), "1", IsTodayFinish(workorderbase) ? "1" : "0", "", IsTodayFinish(workorderbase) ? "0" : "1", "", "", "" });
                        }
                    }
                    #endregion
                }
            }
            AddRange1(body, body2);
            AddRange2(body, body3);
            AddRange3(body, body1);
            TimeSpan ts = DateTime.Parse(endtime) - DateTime.Parse(starttime);
            double daycount = ts.TotalDays + 1;
            StringBuilder sb = new StringBuilder();
            #region 打印表格
            bool b1 = false; bool b2 = false; bool b3 = false;
            foreach (var key in body.Keys)
            {
                List<string> Values = body[key];
                sb.Append("<tr style=\"text-align: center;\" onmouseover=\"this.style.backgroundColor='#F6F6F6'\" onmouseout=\"this.style.backgroundColor='#ffffff'\">");
                //if (Values[0] == "客服" && b1 == false)
                //{
                //    sb.Append("<td rowspan=\"" + (body1.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;\">" + Values[0] + "</td>");
                //    b1 = true;
                //}
                if (Values[0] == "一线工程师" && b2 == false)
                {
                    sb.Append("<td rowspan=\"" + (body2.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;\">" + Values[0] + "</td>");
                    b2 = true;
                }
                if (Values[0] == "二线工程师" && b3 == false)
                {
                    sb.Append("<td rowspan=\"" + (body3.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;\">" + Values[0] + "</td>");
                    b3 = true;
                }
                if (getUserNameAndDeptName(key)[0] == "合计：") sb.Append("<td colspan=\"2\" 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>");
                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;\">" + getUserNameAndDeptName(key)[0] + "</td>");
                for (int i = 0; i < Values.Count(); i++)
                {
                    Values[4] = "0.00%"; if (Values[2] != "0") Values[4] = (Convert.ToInt32(Values[3]) * 100.0 / Convert.ToInt32(Values[2])).ToString("0.00") + "%";
                    Values[6] = "0.00%"; if (Values[2] != "0") Values[6] = (Convert.ToInt32(Values[5]) * 100.0 / Convert.ToInt32(Values[2])).ToString("0.00") + "%";
                    Values[7] = "0.00"; if (daycount > 0) Values[7] = (Convert.ToInt32(Values[2]) / daycount).ToString("0.00");
                    Values[8] = "0.00"; if (daycount > 0) Values[8] = (Convert.ToInt32(Values[3]) / daycount).ToString("0.00");
                    if (i == 0) 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>");
            }
            #endregion
            return sb.ToString();
        }
        /// <summary>
        /// 通过UserId得到姓名和部门名
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        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;
                }
            }
            else
            {
                if (userid > -3)
                    resarr[0] = "小计：";
                else resarr[0] = "合计：";
            }
            return resarr;
        }
        /// <summary>
        /// 某工单是否今天完成
        /// </summary>
        /// <param name="workorderbase"></param>
        /// <returns></returns>
        private bool IsTodayFinish(DataTable workorderbase)
        {
            string starttime = DateTime.Parse(workorderbase.Rows[0]["F_STARTTIME"].ToString()).ToShortDateString();
            string endtime = DateTime.Parse(workorderbase.Rows[0]["F_ENDTIME"].ToString()).ToShortDateString();
            workorderbase.Dispose();
            if (starttime == endtime) return true;
            else return false;
        }
        /// <summary>
        /// 某个客服一段时间的接通电话数
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="starttime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        private string CallInCount(int userid, string starttime, string endtime)
        {
            string sqlwhere = string.Format("UserId={0} AND CallState=1 AND CallType=0 AND EndTime>='{1}' AND EndTime<'{2}'", userid, starttime, endtime);//接通的呼入电话
            DataTable dt = new BLL.T_Call_CallRecords().GetList(sqlwhere).Tables[0];
            return dt.Rows.Count.ToString();
        }
        private void AddRange1(Dictionary<int, List<string>> body, Dictionary<int, List<string>> bodytype)
        {
            var dicSort = from objDic in bodytype orderby int.Parse(objDic.Value[2]) descending select objDic;
            List<string> sum = new List<string>() { "一线工程师", "-", "0", "0", "", "0", "", "", "" };
            foreach (KeyValuePair<int, List<string>> kvp in dicSort)
            {
                sum[2] = (int.Parse(sum[2]) + int.Parse(kvp.Value[2])).ToString();
                sum[3] = (int.Parse(sum[3]) + int.Parse(kvp.Value[3])).ToString();
                sum[5] = (int.Parse(sum[5]) + int.Parse(kvp.Value[5])).ToString();
                if (!body.ContainsKey(kvp.Key))
                    body.Add(kvp.Key, kvp.Value);
            }
            body.Add(-1, sum);
        }
        private void AddRange2(Dictionary<int, List<string>> body, Dictionary<int, List<string>> bodytype)
        {
            var dicSort = from objDic in bodytype orderby int.Parse(objDic.Value[2]) descending select objDic;
            List<string> sum = new List<string>() { "二线工程师", "-", "0", "0", "", "0", "", "", "" };
            foreach (KeyValuePair<int, List<string>> kvp in dicSort)
            {
                sum[2] = (int.Parse(sum[2]) + int.Parse(kvp.Value[2])).ToString();
                sum[3] = (int.Parse(sum[3]) + int.Parse(kvp.Value[3])).ToString();
                sum[5] = (int.Parse(sum[5]) + int.Parse(kvp.Value[5])).ToString();
                if (!body.ContainsKey(kvp.Key))
                    body.Add(kvp.Key, kvp.Value);
            }
            body.Add(-2, sum);
        }
        private void AddRange3(Dictionary<int, List<string>> body, Dictionary<int, List<string>> bodytype)
        {
            var dicSort = from objDic in bodytype orderby int.Parse(objDic.Value[2]) descending select objDic;
            List<string> sum = new List<string>() { "客服", "0", "0", "0", "", "0", "", "", "" };
            foreach (KeyValuePair<int, List<string>> kvp in dicSort)
            {
                sum[1] = (int.Parse(sum[1]) + int.Parse(kvp.Value[1])).ToString();
            }
            sum[2] = (int.Parse(body[-1][2]) + int.Parse(body[-2][2])).ToString();
            sum[3] = (int.Parse(body[-1][3]) + int.Parse(body[-2][3])).ToString();
            sum[5] = (int.Parse(body[-1][5]) + int.Parse(body[-2][5])).ToString();
            body.Add(-3, sum);
        }
        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);
        }
    }
}