SZRXYS/BaseCallCent.../reportmanage/dataanalysis/KnowledgeSed...

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 KnowledgeSediment : System.Web.UI.Page
    {
        protected string TableHtml = null;
        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[,] tablebody = 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++)
            {
                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="body"></param>
        /// <returns></returns>
        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++)
            {
                string knowsql = "F_KNOWLEDGE is not null order by ID desc";
                var hisinfomodel1 = new BLL.T_Wo_WorkOrderHistoryInfo().GetModelList("F_INSTANCEID='"
                    + dtable.Rows[i]["F_INSTANCEID"] + "' AND " + knowsql).FirstOrDefault();
                if (hisinfomodel1 != null)
                {
                    //如果形成知识
                    if (hisinfomodel1.F_KNOWLEDGE == 0)
                    {
                        //处理这个工单工程师
                        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)
                                {
                                    int id = Convert.ToInt32(constructor.F_USERID);
                                    Model.T_Sys_UserAccount user = new BLL.T_Sys_UserAccount().GetModel(id);
                                    if (user != null)
                                    {
                                        if (constructor.F_ISMAIN == 1)
                                        {
                                            //这个工单是某个工程师处理的就存储起来，如果还有其他工单也是他处理的就增加1
                                            if (body.ContainsKey(user.F_UserName))
                                            {
                                                body[user.F_UserName][0] = (Convert.ToInt32(body[user.F_UserName][0]) + 1).ToString();
                                            }
                                            else
                                            {
                                                body.Add(user.F_UserName, new List<string>() { "1", "0", "0" });
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            } 
            #endregion
            //循环这段时间的工单，筛选出已经形成知识的工单
            #region 统计已经形成知识的数量
            for (int i = 0; i < allcount; i++)
            {
                //是否已经形成知识
                string alreadyknowsql = "F_ALREADYKNOWLEDGE is not null order by ID desc";
                var hisinfomodel2 = new BLL.T_Wo_WorkOrderHistoryInfo().GetModelList("F_INSTANCEID='"
                    + dtable.Rows[i]["F_INSTANCEID"] + "' and " + alreadyknowsql).FirstOrDefault();
                if (hisinfomodel2 != null)
                {
                    if (hisinfomodel2.F_ALREADYKNOWLEDGE == 0)
                    {
                        //处理这个工单工程师
                        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)
                                {
                                    int id = Convert.ToInt32(constructor.F_USERID);
                                    Model.T_Sys_UserAccount user = new BLL.T_Sys_UserAccount().GetModel(id);
                                    if (user != null)
                                    {
                                        if (constructor.F_ISMAIN == 1)
                                        {
                                            //这个工单是某个工程师处理的就存储起来，如果还有其他工单也是他处理的就增加1
                                            if (body.ContainsKey(user.F_UserName))
                                            {
                                                body[user.F_UserName][1] = (Convert.ToInt32(body[user.F_UserName][1]) + 1).ToString();
                                            }
                                            else
                                            {
                                                body.Add(user.F_UserName, new List<string>() { "0", "1", "0" });
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            } 
            #endregion
            //打印表格
            StringBuilder sb = new StringBuilder();
            var dicSort = from objDic in body orderby int.Parse(objDic.Value[0]) descending select objDic;
            foreach (var kvp in dicSort)
            {
                List<string> Values = kvp.Value;
                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>();
            int knowsum = 0;
            int alreadyknowsum = 0;
            string p = "0.00%";
            foreach (string key in body.Keys)
            {
                knowsum += Convert.ToInt32(body[key][0]);
                alreadyknowsum += Convert.ToInt32(body[key][1]);
                if (body[key][0] == "0") body[key][2] = "0.00%";
                body[key][2] = (Convert.ToInt32(body[key][1]) * 1.0 / Convert.ToInt32(body[key][0])).ToString("0.00") + "%";
            }
            if (knowsum > 0) p = (alreadyknowsum * 1.0 / knowsum).ToString("0.00") + "%";
            sum.Add(knowsum.ToString()); sum.Add(alreadyknowsum.ToString()); sum.Add(p);
            body.Clear();
            body.Add("合计：", sum);
            foreach (var key in body.Keys)
            {
                List<string> Values = body[key];
                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();
        }

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