using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.SessionState;
using System.Text;
using System.Data;
namespace HySoft.BaseCallCenter.Web.reportmanage.operationdata.controls
{
///
/// TowUserAPI 的摘要说明
///
public class TowUserAPI : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
string action = context.Request.QueryString["action"];
switch (action)
{
case "getlist":
context.Response.Write(GetDataListHtml(context));//读取数据
break;
}
}
public string GetDataListHtml(HttpContext context)
{
StringBuilder htmlReport = new StringBuilder();//存储生成的HTML
htmlReport.Append("");
htmlReport.Append("| 工程师姓名 | ");
htmlReport.Append("事件总数 | ");
htmlReport.Append("一般事件响应及时数 | ");
htmlReport.Append("紧急事件响应及时数 | ");
htmlReport.Append("事件响应及时率 | ");
htmlReport.Append("一般事件解决及时数 | ");
htmlReport.Append("紧急事件解决及时数 | ");
htmlReport.Append("事件解决及时率 | ");
//htmlReport.Append("紧急事件响应及时率 | ");
//htmlReport.Append("紧急事件解决及时率 | ");
htmlReport.Append("事件解决数 | ");
htmlReport.Append("事件解决率 | ");
htmlReport.Append("事件平均解决时间（小时） | ");
htmlReport.Append("回访评分总和 | ");
htmlReport.Append("回访事件总数 | ");
htmlReport.Append("客户评分 | ");
htmlReport.Append("
");
try
{
string txtStartTime = context.Request.QueryString["txtStartTime"];
string txtEndTime = context.Request.QueryString["txtEndTime"];
string sqltimeHistory = "";
if (txtStartTime != null && txtStartTime.Trim() != "")
{
sqltimeHistory += " and CONVERT(varchar , F_OPTDATE, 120)>=CONVERT(varchar , '" + txtStartTime.Trim() + " 00:00:01', 120) ";
}
if (txtEndTime != null && txtEndTime.Trim() != "")
{
sqltimeHistory += " and CONVERT(varchar , F_OPTDATE, 120)<=CONVERT(varchar , '" + txtEndTime.Trim() + " 23:59:59', 120) ";
}
string sqltimeTask = "";
if (txtStartTime != null && txtStartTime.Trim() != "")
{
sqltimeTask += " and CONVERT(varchar , F_CREATEDATE, 120)>=CONVERT(varchar , '" + txtStartTime.Trim() + " 00:00:01', 120) ";
}
if (txtEndTime != null && txtEndTime.Trim() != "")
{
sqltimeTask += " and CONVERT(varchar , F_CREATEDATE, 120)<=CONVERT(varchar , '" + txtEndTime.Trim() + " 23:59:59', 120) ";
}
double zong1 = 0;
double zong2 = 0;
double zong3 = 0;
double zong4 = 0;
double zong5 = 0;
double zong6 = 0;
double zong7 = 0;
double zong8 = 0;
double zongfen = 0;
int zongfencount = 0;
double zongtime = 0;
int zongtimecount = 0;
int count = 0;
int gerenfenshu = 0;
int gerencount = 0;
#region 查询在字典那块设置的时间
string ybgdJDcaoshi = "15552000";
string ybgdCLcaoshi = "15552000";
string jjgdJDcaoshi = "15552000";
string jjgdCLcaoshi = "15552000";
string sqlybgd = "SELECT * from T_Sys_DictionaryValue where F_DictionaryFlag='ybgd' order by F_Sort ";
DataTable dtybgd = DBUtility.DbHelperSQL.Query(sqlybgd).Tables[0];
if (dtybgd.Rows.Count > 1)
{
ybgdJDcaoshi = (Convert.ToInt32(dtybgd.Rows[0]["F_Name"]) * 3600).ToString();
ybgdCLcaoshi = (Convert.ToInt32(dtybgd.Rows[1]["F_Name"]) * 3600).ToString();
}
string sqljjgd = "SELECT * from T_Sys_DictionaryValue where F_DictionaryFlag='jjgd' order by F_Sort ";
DataTable dtjjgd = DBUtility.DbHelperSQL.Query(sqljjgd).Tables[0];
if (dtjjgd.Rows.Count > 1)
{
jjgdJDcaoshi = (Convert.ToInt32(dtybgd.Rows[0]["F_Name"]) * 3600).ToString();
jjgdCLcaoshi = (Convert.ToInt32(dtybgd.Rows[1]["F_Name"]) * 3600).ToString();
}
#endregion
#region 查询在告警超时那块设置的时间
//string JDcaoshi = "15552000";
//string CLcaoshi = "15552000";
//string sqlCLcaoshi = "select * from T_Wo_StateAlarmDate where F_WorkOrderTypeId=16 and F_WorkOrderStateId=7";
//DataTable dtCLcaoshi = DBUtility.DbHelperSQL.Query(sqlCLcaoshi).Tables[0];
//if (dtCLcaoshi.Rows.Count > 0)
//{
// if (Convert.ToInt32(dtCLcaoshi.Rows[0]["F_Ccount"]) > 0)
// {
// if (Convert.ToInt32(dtCLcaoshi.Rows[0]["F_Cunit"]) == 1)
// {
// CLcaoshi = (Convert.ToInt32(dtCLcaoshi.Rows[0]["F_Ccount"]) * 3600).ToString();
// }
// else if (Convert.ToInt32(dtCLcaoshi.Rows[0]["F_Cunit"]) == 2)
// {
// CLcaoshi = (Convert.ToInt32(dtCLcaoshi.Rows[0]["F_Ccount"]) * 3600 * 24).ToString();
// }
// else
// {
// CLcaoshi = (Convert.ToInt32(dtCLcaoshi.Rows[0]["F_Ccount"]) * 60).ToString();
// }
// }
//}
//string sqlcaoshi = "select * from T_Wo_StateAlarmDate where F_WorkOrderTypeId=16 and F_WorkOrderStateId=6";
//DataTable dtJDcaoshi = DBUtility.DbHelperSQL.Query(sqlcaoshi).Tables[0];
//if (dtJDcaoshi.Rows.Count > 0)
//{
// if (Convert.ToInt32(dtJDcaoshi.Rows[0]["F_Ccount"]) > 0)
// {
// if (Convert.ToInt32(dtJDcaoshi.Rows[0]["F_Cunit"]) == 1)
// {
// JDcaoshi = (Convert.ToInt32(dtJDcaoshi.Rows[0]["F_Ccount"]) * 3600).ToString();
// }
// else if (Convert.ToInt32(dtJDcaoshi.Rows[0]["F_Cunit"]) == 2)
// {
// JDcaoshi = (Convert.ToInt32(dtJDcaoshi.Rows[0]["F_Ccount"]) * 3600*24).ToString();
// }
// else
// {
// JDcaoshi = (Convert.ToInt32(dtJDcaoshi.Rows[0]["F_Ccount"]) * 60).ToString();
// }
// }
//}
#endregion
DataTable dt = new DataTable();
string sql = "SELECT *,(select COUNT(*) from T_Wo_WorkOrderTask where F_INSTANCEID in(select F_INSTANCEID from T_Wo_WorkOrderBase where 1=1 " + sqltimeTask + " ) and F_WORKORDERSTATEID=6 and F_OWNERID=T_Sys_UserAccount.F_UserId " + sqltimeTask + ") as counts from T_Sys_UserAccount where F_RoleId=31 order by counts desc ";
dt = DBUtility.DbHelperSQL.Query(sql).Tables[0];
for (int i = 0; i < dt.Rows.Count; i++)
{
sql = " select (select COUNT(*) from T_Wo_WorkOrderTask where F_INSTANCEID in(select F_INSTANCEID from T_Wo_WorkOrderBase where 1=1 " + sqltimeTask + " ) and F_WORKORDERSTATEID=6 and F_OWNERID=" + dt.Rows[i]["F_UserId"].ToString() + "" + sqltimeTask + ") as counts,";
sql += "(select count(*) from T_Wo_WorkOrderHistory where F_INSTANCEID in(select F_INSTANCEID from T_Wo_WorkOrderBase where 1=1 and F_REPAIRLEVEL is null or F_REPAIRLEVEL!=396 " + sqltimeTask + " ) and F_WORKORDERSTATEID=6 and F_OPTUSERID=" + dt.Rows[i]["F_UserId"].ToString() + " and F_ELAPSEDTIME<" + ybgdJDcaoshi + "" + sqltimeHistory + ") as ybgdjiedan,";
sql += "(select count(*) from T_Wo_WorkOrderHistory where F_INSTANCEID in(select F_INSTANCEID from T_Wo_WorkOrderBase where 1=1 and F_REPAIRLEVEL is null or F_REPAIRLEVEL!=396 " + sqltimeTask + " ) and F_WORKORDERSTATEID=7 and F_OPTUSERID=" + dt.Rows[i]["F_UserId"].ToString() + " and F_ELAPSEDTIME<" + ybgdCLcaoshi + "" + sqltimeHistory + ") as ybgdchuli,";
sql += "(select count(*) from T_Wo_WorkOrderHistory where F_INSTANCEID in(select F_INSTANCEID from T_Wo_WorkOrderBase where 1=1 and F_REPAIRLEVEL=396 " + sqltimeTask + " ) and F_WORKORDERSTATEID=6 and F_OPTUSERID=" + dt.Rows[i]["F_UserId"].ToString() + " and F_ELAPSEDTIME<" + jjgdJDcaoshi + "" + sqltimeHistory + ") as jjgdjiedan,";
sql += "(select count(*) from T_Wo_WorkOrderHistory where F_INSTANCEID in(select F_INSTANCEID from T_Wo_WorkOrderBase where 1=1 and F_REPAIRLEVEL=396 " + sqltimeTask + " ) and F_WORKORDERSTATEID=7 and F_OPTUSERID=" + dt.Rows[i]["F_UserId"].ToString() + " and F_ELAPSEDTIME<" + jjgdCLcaoshi + "" + sqltimeHistory + ") as jjgdchuli,";
sql += "(select count(*) from T_Wo_WorkOrderHistory where F_INSTANCEID in(select F_INSTANCEID from T_Wo_WorkOrderBase where 1=1 " + sqltimeTask + " ) and F_WORKORDERSTATEID=7 and F_OPTUSERID=" + dt.Rows[i]["F_UserId"].ToString() + " and F_ELAPSEDTIME is not null" + sqltimeHistory + ") as jiejue ";
DataTable dt1 = new DataTable();
dt1 = DBUtility.DbHelperSQL.Query(sql).Tables[0];
if (dt1.Rows[0]["counts"] == null || dt1.Rows[0]["counts"].ToString() == "" || dt1.Rows[0]["counts"].ToString() == "0")
{
continue;
}
count = count + 1;
sql = @"select sum(F_ELAPSEDTIME) as sum1,(select sum(F_ELAPSEDTIME) from T_Wo_WorkOrderHistory where F_WORKORDERSTATEID=6 and F_INSTANCEID in(select F_INSTANCEID from T_Wo_WorkOrderHistory where F_INSTANCEID in(select F_INSTANCEID from T_Wo_WorkOrderBase where 1=1 " + sqltimeTask + " ) and F_WORKORDERSTATEID=7 and F_OPTUSERID=" + dt.Rows[i]["F_UserId"].ToString() + ")" + sqltimeHistory + ") as sum2 from T_Wo_WorkOrderHistory where F_INSTANCEID in(select F_INSTANCEID from T_Wo_WorkOrderBase where 1=1 " + sqltimeTask + " ) and F_WORKORDERSTATEID=7 and F_OPTUSERID=" + dt.Rows[i]["F_UserId"].ToString() + "" + sqltimeHistory + "";
DataTable dt2 = DBUtility.DbHelperSQL.Query(sql).Tables[0];
string time = "0";
if (dt2.Rows[0]["sum1"].ToString() == "" && dt2.Rows[0]["sum2"].ToString() != "")
{
time = (Convert.ToDouble(dt2.Rows[0]["sum2"]) / Convert.ToDouble(dt1.Rows[0]["counts"]) / 3600).ToString("f2");
zongtime += Convert.ToDouble(dt2.Rows[0]["sum2"]);
zongtimecount += Convert.ToInt32(dt1.Rows[0]["counts"]);
}
else if (dt2.Rows[0]["sum1"].ToString() != "" && dt2.Rows[0]["sum2"].ToString() == "")
{
time = (Convert.ToDouble(dt2.Rows[0]["sum1"]) / Convert.ToDouble(dt1.Rows[0]["counts"]) / 3600).ToString("f2");
zongtime += Convert.ToDouble(dt2.Rows[0]["sum1"]);
zongtimecount += Convert.ToInt32(dt1.Rows[0]["counts"]);
}
else
if (dt2.Rows[0]["sum1"].ToString() != "" && dt2.Rows[0]["sum2"].ToString() != "")
{
time = ((Convert.ToDouble(dt2.Rows[0]["sum1"]) + Convert.ToDouble(dt2.Rows[0]["sum2"])) / Convert.ToDouble(dt1.Rows[0]["counts"]) / 3600).ToString("f2");
zongtime += Convert.ToDouble(dt2.Rows[0]["sum1"]) + Convert.ToDouble(dt2.Rows[0]["sum2"]);
zongtimecount += Convert.ToInt32(dt1.Rows[0]["counts"]);
}
sql = "select count(*) as counts from T_Wo_WorkOrderHistoryInfo where F_INSTANCEID in(select F_INSTANCEID from T_Wo_WorkOrderHistory where F_INSTANCEID in(select F_INSTANCEID from T_Wo_WorkOrderBase where 1=1 " + sqltimeTask + " ) and F_WORKORDERSTATEID=7 and F_OPTUSERID=" + dt.Rows[i]["F_UserId"].ToString() + ") and F_CUSTOMERSCORE is not null and F_CUSTOMERSCORE!=''" + sqltimeHistory + "";
DataTable dt3 = DBUtility.DbHelperSQL.Query(sql).Tables[0];
sql = "select SUM(convert(int,F_CUSTOMERSCORE)) as sums from T_Wo_WorkOrderHistoryInfo where F_INSTANCEID in(select F_INSTANCEID from T_Wo_WorkOrderHistory where F_INSTANCEID in(select F_INSTANCEID from T_Wo_WorkOrderBase where 1=1 " + sqltimeTask + " ) and F_WORKORDERSTATEID=7 and F_OPTUSERID=" + dt.Rows[i]["F_UserId"].ToString() + ") and F_CUSTOMERSCORE is not null and F_CUSTOMERSCORE!=''" + sqltimeHistory + "";
DataTable dt4 = DBUtility.DbHelperSQL.Query(sql).Tables[0];
string fenshu = "0";
if (dt4.Rows[0]["sums"].ToString() != "")
{
fenshu = (Convert.ToInt32(dt4.Rows[0]["sums"]) / Convert.ToInt32(dt3.Rows[0]["counts"])).ToString();
zongfen += Convert.ToDouble(dt4.Rows[0]["sums"]);
zongfencount += Convert.ToInt32(dt3.Rows[0]["counts"]);
gerenfenshu = Convert.ToInt32(dt4.Rows[0]["sums"]);
gerencount = Convert.ToInt32(dt3.Rows[0]["counts"]);
}
else
{
gerenfenshu = 0;
gerencount = 0;
}
htmlReport.AppendFormat("| {0} | ", dt.Rows[i]["F_UserName"].ToString());
htmlReport.AppendFormat("{0} | ", dt1.Rows[0]["counts"].ToString());
zong1 += Convert.ToInt32(dt1.Rows[0]["counts"]);
htmlReport.AppendFormat("{0} | ", dt1.Rows[0]["ybgdjiedan"].ToString());
zong2 += Convert.ToInt32(dt1.Rows[0]["ybgdjiedan"]);
htmlReport.AppendFormat("{0} | ", dt1.Rows[0]["jjgdjiedan"].ToString());
zong7 += Convert.ToInt32(dt1.Rows[0]["jjgdjiedan"]);
if (dt1.Rows[0]["ybgdjiedan"] == null || dt1.Rows[0]["jjgdjiedan"] == null|| dt1.Rows[0]["counts"] == null || dt1.Rows[0]["counts"].ToString() == "0")
{
htmlReport.AppendFormat("0.00% | ");
}
else
{
htmlReport.AppendFormat("{0} | ", ((Convert.ToDouble(dt1.Rows[0]["ybgdjiedan"])+Convert.ToDouble(dt1.Rows[0]["jjgdjiedan"])) / Convert.ToDouble(dt1.Rows[0]["counts"]) * 100).ToString("f2") + "%");
}
htmlReport.AppendFormat("{0} | ", dt1.Rows[0]["ybgdchuli"].ToString() + " ");
zong3 += Convert.ToInt32(dt1.Rows[0]["ybgdchuli"]);
htmlReport.AppendFormat("{0} | ", dt1.Rows[0]["jjgdchuli"].ToString() + " ");
zong8 += Convert.ToInt32(dt1.Rows[0]["jjgdchuli"]);
if (dt1.Rows[0]["ybgdchuli"] == null || dt1.Rows[0]["jjgdchuli"] == null || dt1.Rows[0]["counts"] == null || dt1.Rows[0]["counts"].ToString() == "0")
{
htmlReport.AppendFormat("0.00% | ");
}
else
{
htmlReport.AppendFormat("{0} | ", ((Convert.ToDouble(dt1.Rows[0]["ybgdchuli"])+Convert.ToDouble(dt1.Rows[0]["jjgdchuli"])) / Convert.ToDouble(dt1.Rows[0]["counts"]) * 100).ToString("f2") + "%");
}
//紧急请求开始
//if (dt1.Rows[0]["jjgdjiedan"] == null || dt1.Rows[0]["jjgdjiedan"].ToString() == "0" || dt1.Rows[0]["counts"] == null || dt1.Rows[0]["counts"].ToString() == "0")
//{
// htmlReport.AppendFormat("0.00% | ");
//}
//else
//{
// htmlReport.AppendFormat("{0} | ", (Convert.ToDouble(dt1.Rows[0]["jjgdjiedan"]) / Convert.ToDouble(dt1.Rows[0]["counts"]) * 100).ToString("f2") + "%");
//}
//if (dt1.Rows[0]["jjgdchuli"] == null || dt1.Rows[0]["jjgdchuli"].ToString() == "0" || dt1.Rows[0]["counts"] == null || dt1.Rows[0]["counts"].ToString() == "0")
//{
// htmlReport.AppendFormat("0.00% | ");
//}
//else
//{
// htmlReport.AppendFormat("{0} | ", (Convert.ToDouble(dt1.Rows[0]["jjgdchuli"]) / Convert.ToDouble(dt1.Rows[0]["counts"]) * 100).ToString("f2") + "%");
//}
//紧急请求结束
htmlReport.AppendFormat("{0} | ", dt1.Rows[0]["jiejue"].ToString());
zong4 += Convert.ToInt32(dt1.Rows[0]["jiejue"]);
if (dt1.Rows[0]["jiejue"] == null || dt1.Rows[0]["jiejue"].ToString() == "0" || dt1.Rows[0]["counts"] == null || dt1.Rows[0]["counts"].ToString() == "0")
{
htmlReport.AppendFormat("0.00% | ");
}
else
{
htmlReport.AppendFormat("{0} | ", (Convert.ToDouble(dt1.Rows[0]["jiejue"]) / Convert.ToDouble(dt1.Rows[0]["counts"]) * 100).ToString("f2") + "%");
}
htmlReport.AppendFormat("{0} | ", time);
zong5 += Convert.ToDouble(time);
htmlReport.AppendFormat("{0} | ", gerenfenshu);
htmlReport.AppendFormat("{0} | ", gerencount);
htmlReport.AppendFormat("{0} | ", fenshu);
zong6 += Convert.ToInt32(fenshu);
htmlReport.Append("
");
}
htmlReport.Append("");
htmlReport.AppendFormat("| {0} | ", "总计");
htmlReport.AppendFormat("{0} | ", zong1);
htmlReport.AppendFormat("{0} | ", zong2);
htmlReport.AppendFormat("{0} | ", zong7);
htmlReport.AppendFormat("{0} | ", (Convert.ToDouble((zong2+zong7) / zong1) * 100).ToString("f2") + "%");
htmlReport.AppendFormat("{0} | ", zong3);
htmlReport.AppendFormat("{0} | ", zong8);
htmlReport.AppendFormat("{0} | ", (Convert.ToDouble((zong3+zong8) / zong1) * 100).ToString("f2") + "%");
//htmlReport.AppendFormat("{0} | ", (Convert.ToDouble(zong7 / zong1) * 100).ToString("f2") + "%");
//htmlReport.AppendFormat("{0} | ", (Convert.ToDouble(zong8 / zong1) * 100).ToString("f2") + "%");
htmlReport.AppendFormat("{0} | ", zong4);
htmlReport.AppendFormat("{0} | ", (Convert.ToDouble(zong4 / zong1) * 100).ToString("f2") + "%");
if (zongtimecount == 0)
{
htmlReport.AppendFormat("0.00% | ");
}
else
{
htmlReport.AppendFormat("{0} | ", Convert.ToDouble(zongtime / zongtimecount / 3600).ToString("f2"));
}
htmlReport.AppendFormat("{0} | ", zongfen);
htmlReport.AppendFormat("{0} | ", zongfencount);
if (zongfencount == 0)
{
htmlReport.AppendFormat("0.00 | ");
}
else
{
htmlReport.AppendFormat("{0} | ", (zongfen / zongfencount).ToString("f2"));
}
htmlReport.Append("
");
htmlReport.Append("
");
}
catch { }
return htmlReport.ToString();
}
public bool IsReusable
{
get
{
return false;
}
}
}
}