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("");
sb.Append(createTableHead(new string[] { "客服部名称", "工单数量", "总处理时间(小时)", "平均响应时间(小时)", "平均处理时间(小时)" }));
//表内容
sb.Append(createTableBody(starttime, endtime));
sb.Append("
");
TableHtml = 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++)
{
//处理这个工单工程师
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)
{
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() { "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>> lst = new List>>(body);
//lst.Sort(delegate(KeyValuePair> s1, KeyValuePair> 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 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("");
sb.Append("| " + kvp.Key + " | ");
foreach (var value in Values)
{
sb.Append("" + value + " | ");
}
sb.Append("
");
}
#region 合计
//合计
List sum = new List();
long count = 0;
double sumaveragetime = 0.0;
double averagereceivetime = 0.0;
double averagedealwithtime = 0.0;
foreach (var key in body.Keys)
{
List 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 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("");
sb.Append("| " + key + " | ");
foreach (var value in Values)
{
sb.Append("" + value + " | ");
}
sb.Append("
");
}
#endregion
return sb.ToString();
}
///
/// 时间转换(将秒转换为易读的时间)
///
/// 秒
///
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 deptlist = new BLL.T_Sys_Department().GetModelList("F_DeptName LIKE '%客服部%'");
foreach (Model.T_Sys_Department dept in deptlist)
{
sb.Append("");
sb.Append("| " + dept.F_DeptName + " | ");
sb.Append("0 | ");
sb.Append("0 | ");
sb.Append("0 | ");
sb.Append("0 | ");
sb.Append("
");
}
sb.Append("");
sb.Append("| 合计： | ");
sb.Append("0 | ");
sb.Append("0 | ");
sb.Append("0 | ");
sb.Append("0 | ");
sb.Append("
");
}
///
/// 表名称
///
/// 表名
///
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();
}
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();
}
}
}