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 CallInPerson : System.Web.UI.Page
{
protected string TableHtml = null;
protected void Page_Load(object sender, EventArgs e)
{
#region Demo
//PrintTable();
//string[] head = { "日期", "工单量", "一线技术员工单量", "一线技术员工占比", "代理技术员工单量", "代理技术员占比", "客户工单量", "客户占比"};
//string[,] body = new string[30, 8];
//for (int i = 0; i < 30; i++)
//{
// for (int j = 0; j < 8; j++)
// {
// if (j == 0)
// {
// body[i, j] = "2015/4/" + (i + 1).ToString();
// }
// else body[i, j] = "";
// }
//}
//string[] sumiteam = { "合计：", "工单总量", "一线技术员工单总量", "一线技术员工占比", "代理技术员工单总量", "代理技术员占比", "客户工单总量", "客户占比" };
//StringBuilder sb = new StringBuilder();
//sb.Append(createTableTitle("来电人员工单分布"));
//sb.Append(createTable1(head, body, sumiteam));
//TableHtml = sb.ToString();
#endregion
OutputTable();
}
private void OutputTable()
{
//查询条件
string starttime = txtStartTime.Text.Trim();
if (string.IsNullOrWhiteSpace(starttime))
{ starttime = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).ToString("yyyy-MM-dd"); txtStartTime.Text = starttime; }
string endtime = txtEndTime.Text.Trim();
if (string.IsNullOrWhiteSpace(endtime)) { endtime = DateTime.Today.ToString("yyyy-MM-dd"); txtEndTime.Text = endtime; }
string[] head = { "日期", "工单量", "一线技术员工单量", "一线技术员工占比", "代理技术员工单量", "代理技术员占比", "客户工单量", "客户占比" };
TableHtml = createTable("来电人员性质工单分布", head, 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();
}
private 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)
{
//收集数据
List> body = new List>();
for (DateTime dt = Convert.ToDateTime(starttime); dt <= Convert.ToDateTime(endtime); dt = dt.AddDays(1))
{
List bodyiteam = new List();
bodyiteam.Add(dt.ToShortDateString());
string sql = string.Format(" F_WORKORDERTYPEID={0}", 16);
sql += " AND F_STARTTIME>='" + dt.ToShortDateString() + " 00:00:00'";
sql += " AND F_STARTTIME<='" + dt.ToShortDateString() + " 23:59:59'";
// sql += " AND F_ENDTIME IS NOT NULL AND F_ENDTIME !=''";
DataTable dtable = new BLL.T_Wo_WorkOrderBase().GetList(sql).Tables[0];
//今天的总工单量
int todaycount = dtable.Rows.Count;
bodyiteam.Add(todaycount.ToString());
List modellist = new BLL.T_Sys_DictionaryValue().GetModelList("F_DictionaryFlag='bxrxz'");//所有报修人性质
foreach (var model in modellist)
{
//这种性质的报修人的工单量
int thistypecount = 0;
//这种性质的报修人的工单百分比
string percent = null;
if (model.F_Name == "一线技术员")
thistypecount = dtable.Select("F_STANDARDADDRESS='" + model.F_Name + "' OR F_STANDARDADDRESS IS NULL OR F_STANDARDADDRESS=''").Length;
else thistypecount = dtable.Select("F_STANDARDADDRESS='" + model.F_Name + "'").Length;
if (todaycount == 0) percent = "0.00%";
else percent = (thistypecount * 1.0 / todaycount * 100).ToString("0.00") + "%";
bodyiteam.Add(thistypecount.ToString());
bodyiteam.Add(percent);
}
dtable.Dispose();
body.Add(bodyiteam);
}
//打印表格
StringBuilder sb = new StringBuilder();
for (int i = 0; i < body.Count;i++ )
{
if (body[i][1] != "0")
{
sb.Append("");
foreach (var listiteam in body[i])
{
sb.Append("| " + listiteam + " | ");
}
sb.Append("
");
}
}
//合计数据
List sum = new List();
sum = getSumCountAndPercent(starttime, endtime, 16);
sb.Append("");
for (int j = 0; j < sum.Count; j++)
{
sb.Append("| " + sum[j] + " | ");
}
sb.Append("
");
return sb.ToString();
}
private List getSumCountAndPercent(string starttime, string endtime, int type)
{
List sum = new List();
sum.Add("合计：");
string sql = string.Format(" F_WORKORDERTYPEID={0}", type);
if (starttime != "")
{
sql += " AND F_STARTTIME>='" + starttime + " 00:00:00'";
}
if (endtime != "")
{
sql += " AND F_STARTTIME<='" + endtime + " 23:59:59'";
}
DataTable dt = new BLL.T_Wo_WorkOrderBase().GetList(sql).Tables[0];
//工单总数
int allcount = dt.Rows.Count;
sum.Add(allcount.ToString());
List modellist = new BLL.T_Sys_DictionaryValue().GetModelList("F_DictionaryFlag='bxrxz'");//所有报修人性质
foreach (var model in modellist)
{
int count = 0;
if (model.F_Name == "一线技术员")
count = dt.Select("F_STANDARDADDRESS is null or F_STANDARDADDRESS='' or F_STANDARDADDRESS='" + model.F_Name + "'").Length;
else count = dt.Select("F_STANDARDADDRESS='" + model.F_Name + "'").Length;
string average = "0.00%";
if (allcount != 0) average = (count * 1.0 / allcount * 100).ToString("0.00") + "%";
sum.Add(count.ToString());
sum.Add(average);
}
dt.Dispose();
return sum;
}
protected void btnConfirm_Click(object sender, EventArgs e)
{
//PrintTable();
OutputTable();
}
private void PrintTable()
{
//查询条件
string starttime = txtStartTime.Text.Trim();
if (string.IsNullOrWhiteSpace(starttime))
starttime = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).ToShortDateString();
string endtime = txtEndTime.Text.Trim();
if (string.IsNullOrWhiteSpace(endtime)) endtime = DateTime.Today.AddDays(-1).ToShortDateString();
//工单总数
int workordercount = getWorkOrderCount(starttime, endtime, 16);
//打印表
List modellist = new BLL.T_Sys_DictionaryValue().GetModelList("F_DictionaryFlag='bxrxz'");
//表列名
List head = new List();
head.Add("");
foreach (var model in modellist)
head.Add(model.F_Name);
head.Add("其他");
//表主体(最多支持10种性质的报修人,即：可以在报修人性质字典中添加10种性质的报修人)
string[,] body = new string[,] { { "工单数量", "", "", "", "", "", "", "", "", "", "", ""},
{ "工单占比", "", "", "", "", "", "", "", "", "", "", ""} };
for (int i = 0; i < head.Count; i++)
{
if (head[i] != "")
{
//各种性质报修人的工单数和百分比
string[] cp = getWorkOrderCountAndPercent(starttime, endtime, 16, head[i]);
body[0, i] = cp[0];
body[1, i] = cp[1];
}
}
string[] sumiteam = new string[] { "工单总数", workordercount.ToString() };
StringBuilder sb = new StringBuilder();
sb.Append(createTableTitle("来电人员工单分布"));
sb.Append(createTable(head.ToArray(), body, sumiteam));
TableHtml = sb.ToString();
}
///
/// 工单总数量
///
///
private int getWorkOrderCount(string starttime, string endtime, int type)
{
string sql = string.Format("F_WORKORDERTYPEID={0}", type);
if (starttime != "")
{
sql += " AND F_STARTTIME>='" + starttime + " 00:00:00'";
}
if (endtime != "")
{
sql += " AND F_STARTTIME<='" + endtime + " 23:59:59'";
}
return new BLL.T_Wo_WorkOrderBase().GetList(sql).Tables[0].Rows.Count;
}
///
/// 不同来电人性质的工单数及百分率
///
/// 开始时间
/// 结束时间
/// 工单类型
/// 来电人性质
///
private string[] getWorkOrderCountAndPercent(string starttime, string endtime, int type, string callinpersontype)
{
string sql = string.Format(" F_WORKORDERTYPEID={0}", type);
if (starttime != "")
{
sql += " AND F_STARTTIME>='" + starttime + " 00:00:00'";
}
if (endtime != "")
{
sql += " AND F_STARTTIME<='" + endtime + " 23:59:59'";
}
DataTable dt = new BLL.T_Wo_WorkOrderBase().GetList(sql).Tables[0];
//工单总数
int allcount = dt.Rows.Count;
string[] arr = new string[2];
int count = 0;
if (callinpersontype == "一线技术员")
count = dt.Select("F_STANDARDADDRESS is null or F_STANDARDADDRESS='' or F_STANDARDADDRESS='" + callinpersontype + "'").Length;
else count = dt.Select("F_STANDARDADDRESS='" + callinpersontype + "'").Length;
dt.Dispose();
arr[0] = count.ToString();
arr[1] = (count * 1.0 / allcount * 100).ToString("0.00") + "%";
return arr;
}
///
/// 表头（表列名）
///
/// 列名数组
///
protected string createTable(string[] head, string[,] body, string[] sumiteam)
{
StringBuilder sb = new StringBuilder();
sb.Append("");
sb.Append("");
for (int i = 0; i < head.Length; i++)
{
sb.Append("| " + head[i] + " | ");
}
sb.Append("
");
sb.Append(createTableBody(head, body, sumiteam));
sb.Append("
");
return sb.ToString();
}
///
/// 表纵轴和横轴的名称
///
/// 数组长度为2。 dimensionname[0]：横轴名 dimensionname[1]：纵轴名
///
private string createDimensionName(string[] dimensionname)
{
StringBuilder sb = new StringBuilder();
sb.Append("");
for (int i = 0; i < dimensionname.Length; i++)
{
sb.Append("");
for (int j = 0; j < dimensionname.Length; j++)
{
if (i == 0 && j != 0)
sb.Append("| " + dimensionname[i] + " | ");
else if (i != 0 && j == 0)
sb.Append("" + dimensionname[i] + " | ");
else
sb.Append(" | ");
}
sb.Append("
");
}
sb.Append("
");
return sb.ToString();
}
private string createTableBody(string[] head, string[,] body, string[] sumiteam)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < body.GetLength(0); i++)
{
sb.Append("");
for (int j = 0; j < head.Length; j++)
{
sb.Append("| " + body[i, j] + " | ");
}
sb.Append("
");
}
sb.Append("");
for (int j = 0; j < sumiteam.Length; j++)
{
if (j > 0)
sb.Append("| " + sumiteam[j] + " | ");
else sb.Append("" + sumiteam[j] + " | ");
}
sb.Append("
");
return sb.ToString();
}
protected string createTable1(string[] head, string[,] body, string[] sumiteam)
{
StringBuilder sb = new StringBuilder();
sb.Append("");
sb.Append("");
for (int i = 0; i < head.Length; i++)
{
sb.Append("| " + head[i] + " | ");
}
sb.Append("
");
sb.Append(createTableBody1(head, body, sumiteam));
sb.Append("
");
return sb.ToString();
}
private string createTableBody1(string[] head, string[,] body, string[] sumiteam)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < body.GetLength(0); i++)
{
sb.Append("");
for (int j = 0; j < head.Length; j++)
{
sb.Append("| " + body[i, j] + " | ");
}
sb.Append("
");
}
sb.Append("");
for (int j = 0; j < sumiteam.Length; j++)
{
sb.Append("| " + sumiteam[j] + " | ");
}
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();
}
}
}