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
{
///
| 部门名称 | "); 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("{0} | ", dtDept.Rows[j]["deptname"].ToString()); 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 " + sqltimeTask + " ) and F_WORKORDERSTATEID=6 and F_OPTUSERID=" + dt.Rows[i]["F_UserId"].ToString() + " and F_ELAPSEDTIME<" + JDcaoshi + "" + sqltimeHistory + ") as ybgdjiedan,"; //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<" + CLcaoshi + "" + sqltimeHistory + ") as ybgdchuli,"; //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 "; 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("
| {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("|||