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("");
sb.Append(createTableHead(head));
//表内容
sb.Append(createTableBody(starttime, endtime));
sb.Append("
");
return sb.ToString();
}
///
/// 表名称
///
/// 表名
///
protected string createTableTitle(string title)
{
StringBuilder sb = new StringBuilder();
sb.Append("" + title + "
");
return sb.ToString();
}
///
/// 表头（表列名）
///
///
///
protected string createTableHead(string[] head)
{
StringBuilder sb = new StringBuilder();
sb.Append("");
for (int i = 0; i < head.Length; i++)
{
sb.Append("| " + head[i] + " | ");
}
sb.Append("
");
return sb.ToString();
}
///
/// 表内容
///
///
///
private string createTableBody(string starttime, string endtime)
{
//存储数据的数据字典
Dictionary> body = new Dictionary>();
//这段时间已经结束的报修工单
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 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() { "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 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() { "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 Values = kvp.Value;
string tdcolor = "#fff"; if (kvp.Key == "合计：") tdcolor = "#D5EDFE";
sb.Append("");
sb.Append("| " + kvp.Key + " | ");
foreach (var value in Values)
{
sb.Append("" + value + " | ");
}
sb.Append("
");
}
#region 合计
//合计
List sum = new List();
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 Values = body[key];
string tdcolor = "#fff"; if (key == "合计：") tdcolor = "#D5EDFE";
sb.Append("");
sb.Append("| " + key + " | ");
foreach (var value in Values)
{
sb.Append("" + value + " | ");
}
sb.Append("
");
}
#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();
}
}
}