Autobio_API/codegit/CallCenterApi/CallCenterAp.../CallCenterAp.../Controllers/report/ZuoXiWorkTot...

using CallCenter.Utility;
using CallCenterApi.DB;
using CallCenterApi.Interface.Controllers.Base;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace CallCenterApi.Interface.Controllers.report
{
    public class ZuoXiWorkTotalController : BaseController
    {
        //坐席工作统计
        //获取表头
        public ActionResult GetColumnList()
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            
                String[] str = { "部门", "坐席", "坐席工号", "呼入电话数", "坐席接通量", "呼损量"
                        ,"接通率","呼损率","平均排队时间","接听通话次数","通话分钟数","通话秒数"
                        ,"平均接听通话秒数","平均振铃秒数","呼入占有率","用户评价","坐席登录次数"
                        ,"登录时长分钟数","工作时长分钟数","平均每天工作时长（小时）","平均操作分钟数"
                        ,"置忙次数","休息时长分钟数","平均每天休息时长（小时）"
                        ,"置忙平均休息分钟数","质检平均成绩"
                };
                res = Success("获取坐席工作统计表头成功", str);
         
            return res;
        }

        //获取数据
        public ActionResult GetDataList(string stime,string endtime,string dpt,string dayss)
        {
            ActionResult res = NoToken("未知错误，请重新登录");
         
                DataTable dtNew = new DataTable();
                dtNew = getData(stime, endtime, dpt, dayss);
                #region
                //#region
                //DataColumn dc1 = new DataColumn("部门");
                //DataColumn dc2 = new DataColumn("坐席");
                //DataColumn dc3 = new DataColumn("坐席工号");
                //DataColumn dc4 = new DataColumn("呼入电话数");
                //DataColumn dc5 = new DataColumn("坐席接通量");
                //DataColumn dc6 = new DataColumn("呼损量");
                //DataColumn dc7 = new DataColumn("接通率");
                //DataColumn dc8 = new DataColumn("呼损率");
                //DataColumn dc9 = new DataColumn("平均排队时间");
                //DataColumn dc10 = new DataColumn("接听通话次数");
                //DataColumn dc11 = new DataColumn("通话分钟数");
                //DataColumn dc12 = new DataColumn("通话秒数");
                //DataColumn dc13 = new DataColumn("平均接听通话秒数");
                //DataColumn dc14 = new DataColumn("平均振铃秒数");
                //DataColumn dc15 = new DataColumn("呼入占有率");
                //DataColumn dc16 = new DataColumn("用户评价");
                //DataColumn dc17 = new DataColumn("坐席登录次数");
                //DataColumn dc18 = new DataColumn("登录时长分钟数");
                //DataColumn dc19 = new DataColumn("工作时长分钟数");
                //DataColumn dc20 = new DataColumn("平均每天工作时长");
                //DataColumn dc21 = new DataColumn("平均操作分钟数");
                //DataColumn dc22 = new DataColumn("置忙次数");
                //DataColumn dc23 = new DataColumn("休息时长分钟数");
                //DataColumn dc24 = new DataColumn("平均每天休息时长");
                //DataColumn dc25 = new DataColumn("置忙平均休息分钟数");
                //DataColumn dc26 = new DataColumn("质检平均成绩");

                //dtNew.Columns.Add(dc1);
                //dtNew.Columns.Add(dc2);
                //dtNew.Columns.Add(dc3);
                //dtNew.Columns.Add(dc4);
                //dtNew.Columns.Add(dc5);
                //dtNew.Columns.Add(dc6);
                //dtNew.Columns.Add(dc7);
                //dtNew.Columns.Add(dc8);
                //dtNew.Columns.Add(dc9);
                //dtNew.Columns.Add(dc10);
                //dtNew.Columns.Add(dc11);
                //dtNew.Columns.Add(dc12);
                //dtNew.Columns.Add(dc13);
                //dtNew.Columns.Add(dc14);
                //dtNew.Columns.Add(dc15);
                //dtNew.Columns.Add(dc16);
                //dtNew.Columns.Add(dc17);
                //dtNew.Columns.Add(dc18);
                //dtNew.Columns.Add(dc19);
                //dtNew.Columns.Add(dc20);
                //dtNew.Columns.Add(dc21);
                //dtNew.Columns.Add(dc22);
                //dtNew.Columns.Add(dc23);
                //dtNew.Columns.Add(dc24);
                //dtNew.Columns.Add(dc25);
                //dtNew.Columns.Add(dc26);
                //#endregion
                //int days = 30;
                //string sqltimeCallRecords = "";
                //string strsqlcall = "";
                //if (stime != null && stime.Trim() != "")
                //{
                //    sqltimeCallRecords += " and CONVERT(varchar , TimeAlerting, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
                //    strsqlcall += " and CONVERT(varchar , BeginTime, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
                //}
                //if (endtime != null && endtime.Trim() != "")
                //{
                //    sqltimeCallRecords += " and CONVERT(varchar , TimeAlerting, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
                //    strsqlcall += " and CONVERT(varchar , BeginTime, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
                //}
                //if (dayss != null && dayss.Trim() != "")
                //{
                //    days = int.Parse(dayss.Trim());
                //}
                //#region
                //int ijtcs = 0;//接听通话次数 
                //int ithms = 0;//通话秒数
                //int izlms = 0;//振铃秒数
                //int ipjjtms = 0;//平均接听秒数
                //int ipjzlms = 0;//平均振铃秒数
                //double ihrzyl = 0.00;//呼入占有率
                //int izxdlcs = 0;//坐席登录次数
                //int idlsc = 0;//登录时长
                //int iWorktime = 0;//工作时长
                //int izmcs = 0;//置忙次数
                //int ixxsc = 0;//休息时长
                //int ipjczsc = 0;//平均操作时长
                //int ipjzmxxsc = 0;//平均置忙休息时长


                //int itrunkct = 0;//总数
                //int itrunkjj = 0;//总接听
                //int itrunkhs = 0;//呼损量
                //int itrunkpjsc = 0;//平均排队时间
                //double itrunkjtl = 0.00;//接通率
                //double itrunkhsl = 0.00;//互损率
                //double itrunkpjpd = 0.00;//平均排队
                //#endregion
                //DataTable dtdpt = null;
                //if (dpt != null && dpt.Trim() != "")
                //{
                //    dtdpt = DbHelperSQL.Query("select F_DeptId,F_DeptName,F_Remark from  T_Sys_Department where F_ParentId!=0 and F_DeptId=" + dpt).Tables[0];
                //}
                //else
                //{
                //    dtdpt = DbHelperSQL.Query("select F_DeptId,F_DeptName,F_Remark from  T_Sys_Department where F_ParentId!=0").Tables[0];
                //}
                //if (dtdpt != null && dtdpt.Rows.Count > 0)
                //{
                //    foreach (DataRow dr in dtdpt.Rows)
                //    {   
                //        DataTable dt = new DataTable();
                //        string sql = "SELECT F_UserCode,F_UserName,F_WorkNumber,F_UserId FROM T_Sys_UserAccount where f_seatFlag=1 and F_DeptId=" + dr["F_DeptId"] + " and F_WorkNumber!='' order by  F_WorkNumber asc";
                //        dt = DbHelperSQL.Query(sql).Tables[0];
                //        for (int i = 0; i < dt.Rows.Count; i++)
                //        {
                //            if (!string.IsNullOrEmpty(dt.Rows[i]["F_WorkNumber"].ToString()))
                //            {
                //                DataRow drNew = dtNew.NewRow();
                //                drNew["部门"] = dr["F_DeptName"].ToString();
                //                drNew["坐席"] = dt.Rows[i]["F_UserName"].ToString();
                //                drNew["坐席工号"] = dt.Rows[i]["F_WorkNumber"].ToString();
                //                if (i == 0)
                //                {//呼入电话

                //                    string strsql = "";
                //                    if (stime != null && stime.Trim() != "")
                //                    {
                //                        strsql += " and CONVERT(varchar , TimeCallIn, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
                //                    }
                //                    if (endtime != null && endtime.Trim() != "")
                //                    {
                //                        strsql += " and CONVERT(varchar , TimeCallIn, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
                //                    }

                //                    DataTable dtTrunk = DbHelperSQL.Query("select COUNT(*) as ct,SUM(DATEDIFF(Second,timewantagent,timeconnected)) as dd from rep_trunk_call_in where AgentID='" + dt.Rows[i]["F_UserCode"].ToString() + "' " + strsql).Tables[0];
                //                    DataTable dtcall = DbHelperSQL.Query("select COUNT(*) as ct,(select COUNT(*) from T_Call_CallRecords where CallState=1 and CallType=0 and  UserCode='" + dt.Rows[i]["F_UserCode"].ToString() + "' " + strsqlcall + ") as jt from T_Call_CallRecords where CallType=0 and UserCode='" + dt.Rows[i]["F_UserCode"].ToString() + "' " + strsqlcall).Tables[0];

                //                    if (dtcall != null && dtcall.Rows.Count > 0)
                //                    {
                //                        if (dtcall.Rows[0]["ct"].ToString() != "")
                //                        {
                //                            itrunkct = int.Parse(dtcall.Rows[0]["ct"].ToString());
                //                        }
                //                        else
                //                        {
                //                            itrunkct = 0;
                //                        }
                //                        if (dtcall.Rows[0]["jt"].ToString() != "")
                //                        {
                //                            itrunkjj = int.Parse(dtcall.Rows[0]["jt"].ToString());
                //                        }
                //                        else
                //                        {
                //                            itrunkjj = 0;
                //                        }
                //                        if (dtTrunk.Rows.Count > 0 && dtTrunk.Rows[0]["dd"].ToString() != "")
                //                        {
                //                            itrunkpjsc = int.Parse(dtTrunk.Rows[0]["dd"].ToString());
                //                        }
                //                        else
                //                        {
                //                            itrunkpjsc = 0;
                //                        }
                //                    }
                //                    itrunkhs = itrunkct - itrunkjj;
                //                    if (itrunkct != 0)
                //                    {
                //                        itrunkjtl = itrunkjj / double.Parse(itrunkct.ToString());
                //                        itrunkhsl = itrunkhs / double.Parse(itrunkct.ToString());
                //                        itrunkpjpd = itrunkpjsc / double.Parse(itrunkct.ToString());
                //                    }
                //                    else
                //                    {
                //                        itrunkjtl = 0.00;
                //                        itrunkhsl = 0.00;
                //                        itrunkpjpd = 0.00;
                //                    }
                //                    drNew["呼入电话数"] = itrunkct;
                //                    drNew["坐席接通量"] = itrunkjj;
                //                    drNew["呼损量"] = itrunkhs;
                //                    drNew["接通率"] = (itrunkjtl * 100).ToString("0.00") + "%";
                //                    drNew["呼损率"] = (itrunkhsl * 100).ToString("0.00") + "%";
                //                    drNew["平均排队时间"] = itrunkpjpd.ToString("0.00");

                //                }
                //                else
                //                {
                //                    drNew["呼入电话数"] = "0";
                //                    drNew["坐席接通量"] = "0";
                //                    drNew["呼损量"] = "0";
                //                    drNew["接通率"] = "0.00" + "%";
                //                    drNew["呼损率"] = "0.00" + "%";
                //                    drNew["平均排队时间"] = "0.00";
                //                }


                //                DataTable dtext = DbHelperSQL.Query("select COUNT(*) as ct,SUM(PeriodTalking) as sc,SUM(PeriodAlerting) as pa from rep_ext_call_in where CalleeAgentID=" + dt.Rows[i]["F_WorkNumber"] + " " + sqltimeCallRecords).Tables[0];
                //                DataTable dtCallEvey = DbHelperSQL.Query("select COUNT(*) as ct,SUM(TalkLongTime) as sc from T_Call_CallRecords where CallState=1 and  userid=" + dt.Rows[i]["F_UserId"] + " " + strsqlcall).Tables[0];
                //                if (dtCallEvey != null && dtCallEvey.Rows.Count > 0)
                //                {
                //                    if (dtCallEvey.Rows[0]["ct"].ToString() != "")
                //                    {
                //                        ijtcs = int.Parse(dtCallEvey.Rows[0]["ct"].ToString());//接听次数
                //                    }
                //                    else
                //                    {
                //                        ijtcs = 0;
                //                    }
                //                    if (dtCallEvey.Rows[0]["sc"].ToString() != "")
                //                    {
                //                        ithms = int.Parse(dtCallEvey.Rows[0]["sc"].ToString());//通话秒数
                //                    }
                //                    else
                //                    {
                //                        ithms = 0;
                //                    }
                //                    if (dtext.Rows[0]["pa"].ToString() != "")
                //                    {
                //                        izlms = int.Parse(dtext.Rows[0]["pa"].ToString());//振铃秒数
                //                    }
                //                    else
                //                    {
                //                        izlms = 0;
                //                    }
                //                }
                //                drNew["接听通话次数"] = ijtcs.ToString();
                //                drNew["通话分钟数"] = (ithms / 60).ToString();
                //                drNew["通话秒数"] = ithms.ToString();
                                
                //                if (ijtcs != 0)
                //                {
                //                    ipjjtms = ithms / ijtcs;//平均接通秒数
                //                    ipjzlms = izlms / ijtcs;//平均振铃秒数
                //                }
                //                else
                //                {
                //                    ipjjtms = 0;//平均接通秒数
                //                    ipjzlms = 0;//平均振铃秒数
                //                }
                //                drNew["平均接听通话秒数"] = ipjjtms.ToString();
                //                drNew["平均振铃秒数"] = ipjzlms.ToString();
                                
                //                //坐席登录次数
                //                DataTable dtAgent = DbHelperSQL.Query("select COUNT(*) as dl,SUM(LoginTimes) as dlsc,SUM(reposenum) as zm,SUM(ReposeTimes) as xx from rep_agent_detail where AgentId=" + dt.Rows[i]["F_WorkNumber"] + " " + sqltimeCallRecords.Replace("TimeAlerting", "TimeLogin")).Tables[0];
                //                if (dtAgent != null && dtAgent.Rows.Count > 0)
                //                {
                //                    if (dtAgent.Rows[0]["dl"] != null && dtAgent.Rows[0]["dl"].ToString() != "")
                //                    {
                //                        izxdlcs = int.Parse(dtAgent.Rows[0]["dl"].ToString());//登录次数
                //                    }
                //                    else
                //                    {
                //                        izxdlcs = 0;
                //                    }
                //                    if (dtAgent.Rows[0]["dlsc"] != null && dtAgent.Rows[0]["dlsc"].ToString() != "")
                //                    {
                //                        idlsc = int.Parse(dtAgent.Rows[0]["dlsc"].ToString());//登录时长
                //                    }
                //                    else
                //                    {
                //                        idlsc = 0;
                //                    }
                //                    if (dtAgent.Rows[0]["zm"] != null && dtAgent.Rows[0]["zm"].ToString() != "")
                //                    {
                //                        izmcs = int.Parse(dtAgent.Rows[0]["zm"].ToString());//置忙次数
                //                    }
                //                    else
                //                    {
                //                        izmcs = 0;
                //                    }
                //                    if (dtAgent.Rows[0]["xx"] != null && dtAgent.Rows[0]["xx"].ToString() != "")
                //                    {
                //                        ixxsc = int.Parse(dtAgent.Rows[0]["xx"].ToString());//休息时长
                //                    }
                //                    else
                //                    {
                //                        ixxsc = 0;
                //                    }

                //                }
                //                iWorktime = idlsc - ixxsc;//工作时长
                //                if (izxdlcs != 0)
                //                {
                //                    ipjczsc = iWorktime / izxdlcs;//平均操作秒数
                //                }
                //                else
                //                {
                //                    ipjczsc = 0;
                //                }
                //                if (izmcs != 0)
                //                {
                //                    ipjzmxxsc = ixxsc / izmcs;//置忙平均操作时长
                //                }
                //                else
                //                {
                //                    ipjzmxxsc = 0;
                //                }

                //                //呼入占有率
                //                if (iWorktime != 0)
                //                {
                //                    ihrzyl = ithms / Convert.ToDouble(iWorktime);
                //                }
                //                else
                //                {
                //                    ihrzyl = 0.00;
                //                }
                //                drNew["呼入占有率"] = (ihrzyl * 100).ToString("0.00") + "%";
                //                //用户评价

                //                object obj = DbHelperSQL.GetSingle("select count(*) from T_Call_CallRecords where MYD is not null and CallState=1 and userid=" + dt.Rows[i]["F_UserId"] + strsqlcall);
                //                string myd = "";
                //                if (obj != null)
                //                {
                //                    myd = obj.ToString();
                //                }
                //                drNew["用户评价"] = myd;
                //                drNew["坐席登录次数"] = izxdlcs.ToString();
                //                drNew["登录时长分钟数"] = (idlsc / 60).ToString();
                //                drNew["工作时长分钟数"] = (iWorktime / 60).ToString();
                //                drNew["平均每天工作时长"] = Math.Round((double.Parse(iWorktime.ToString()) / 60 / 60 / days), 2).ToString();
                //                drNew["平均操作分钟数"] = (ipjczsc / 60).ToString();
                //                drNew["置忙次数"] = izmcs.ToString();
                //                drNew["休息时长分钟数"] = (ixxsc / 60).ToString();
                //                drNew["平均每天休息时长"] = Math.Round((double.Parse(ixxsc.ToString()) / 60 / 60 / days), 2).ToString();
                //                drNew["置忙平均休息分钟数"] = (ipjzmxxsc / 60).ToString();
                                
                //                DataSet dszj = DbHelperSQL.Query("select convert(int,SUM(f_qcscore)) as fs,COUNT(*) as fsct from T_Call_CallRecords where F_QCState=1 and UserId=" + dt.Rows[i]["F_UserId"] + strsqlcall);
                //                int zfs = 0;
                //                if (dszj != null && dszj.Tables.Count > 0)
                //                {
                //                    DataTable dtzj = dszj.Tables[0];
                //                    if (dtzj.Rows.Count > 0 && dtzj.Rows[0]["fs"].ToString() != "" && dtzj.Rows[0]["fsct"].ToString() != "")
                //                    {
                //                        zfs = Convert.ToInt32(dtzj.Rows[0]["fs"].ToString()) / int.Parse(dtzj.Rows[0]["fsct"].ToString());
                //                    }
                //                }
                //                drNew["质检平均成绩"] = zfs.ToString();

                //                dtNew.Rows.Add(drNew);
                //            }
                //        }
                //    }
                //}
                #endregion
                res = Success("获取坐席工作统计数据成功", dtNew);
         
            return res;
        }

        //导出数据
        public ActionResult ExptList(string stime, string endtime, string dpt, string dayss)
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            if (Request.IsAuthenticated)
            {
                NPOIHelper npoi = new NPOIHelper();
                DataTable dt = getData(stime, endtime, dpt, dayss);
                if (npoi.ExportToExcel("坐席工作数据", dt) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            return res;
        }

        private DataTable getData(string stime, string endtime, string dpt, string dayss)
        {
            DataTable dtNew = new DataTable();
            #region
            DataColumn dc1 = new DataColumn("部门");
            DataColumn dc2 = new DataColumn("坐席");
            DataColumn dc3 = new DataColumn("坐席工号");
            DataColumn dc4 = new DataColumn("呼入电话数");
            DataColumn dc5 = new DataColumn("坐席接通量");
            DataColumn dc6 = new DataColumn("呼损量");
            DataColumn dc7 = new DataColumn("接通率");
            DataColumn dc8 = new DataColumn("呼损率");
            DataColumn dc9 = new DataColumn("平均排队时间");
            DataColumn dc10 = new DataColumn("接听通话次数");
            DataColumn dc11 = new DataColumn("通话分钟数");
            DataColumn dc12 = new DataColumn("通话秒数");
            DataColumn dc13 = new DataColumn("平均接听通话秒数");
            DataColumn dc14 = new DataColumn("平均振铃秒数");
            DataColumn dc15 = new DataColumn("呼入占有率");
            DataColumn dc16 = new DataColumn("用户评价");
            DataColumn dc17 = new DataColumn("坐席登录次数");
            DataColumn dc18 = new DataColumn("登录时长分钟数");
            DataColumn dc19 = new DataColumn("工作时长分钟数");
            DataColumn dc20 = new DataColumn("平均每天工作时长");
            DataColumn dc21 = new DataColumn("平均操作分钟数");
            DataColumn dc22 = new DataColumn("置忙次数");
            DataColumn dc23 = new DataColumn("休息时长分钟数");
            DataColumn dc24 = new DataColumn("平均每天休息时长");
            DataColumn dc25 = new DataColumn("置忙平均休息分钟数");
            DataColumn dc26 = new DataColumn("质检平均成绩");

            dtNew.Columns.Add(dc1);
            dtNew.Columns.Add(dc2);
            dtNew.Columns.Add(dc3);
            dtNew.Columns.Add(dc4);
            dtNew.Columns.Add(dc5);
            dtNew.Columns.Add(dc6);
            dtNew.Columns.Add(dc7);
            dtNew.Columns.Add(dc8);
            dtNew.Columns.Add(dc9);
            dtNew.Columns.Add(dc10);
            dtNew.Columns.Add(dc11);
            dtNew.Columns.Add(dc12);
            dtNew.Columns.Add(dc13);
            dtNew.Columns.Add(dc14);
            dtNew.Columns.Add(dc15);
            dtNew.Columns.Add(dc16);
            dtNew.Columns.Add(dc17);
            dtNew.Columns.Add(dc18);
            dtNew.Columns.Add(dc19);
            dtNew.Columns.Add(dc20);
            dtNew.Columns.Add(dc21);
            dtNew.Columns.Add(dc22);
            dtNew.Columns.Add(dc23);
            dtNew.Columns.Add(dc24);
            dtNew.Columns.Add(dc25);
            dtNew.Columns.Add(dc26);
            #endregion
            int days = 30;
            string sqltimeCallRecords = "";
            string strsqlcall = "";
            if (stime != null && stime.Trim() != "")
            {
                sqltimeCallRecords += " and CONVERT(varchar , TimeAlerting, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
                strsqlcall += " and CONVERT(varchar , BeginTime, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sqltimeCallRecords += " and CONVERT(varchar , TimeAlerting, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
                strsqlcall += " and CONVERT(varchar , BeginTime, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }
            if (dayss != null && dayss.Trim() != "")
            {
                days = int.Parse(dayss.Trim());
            }
            #region
            int ijtcs = 0;//接听通话次数 
            int ithms = 0;//通话秒数
            int izlms = 0;//振铃秒数
            int ipjjtms = 0;//平均接听秒数
            int ipjzlms = 0;//平均振铃秒数
            double ihrzyl = 0.00;//呼入占有率
            int izxdlcs = 0;//坐席登录次数
            int idlsc = 0;//登录时长
            int iWorktime = 0;//工作时长
            int izmcs = 0;//置忙次数
            int ixxsc = 0;//休息时长
            int ipjczsc = 0;//平均操作时长
            int ipjzmxxsc = 0;//平均置忙休息时长


            int itrunkct = 0;//总数
            int itrunkjj = 0;//总接听
            int itrunkhs = 0;//呼损量
            int itrunkpjsc = 0;//平均排队时间
            double itrunkjtl = 0.00;//接通率
            double itrunkhsl = 0.00;//互损率
            double itrunkpjpd = 0.00;//平均排队
            #endregion
            DataTable dtdpt = null;
            if (dpt != null && dpt.Trim() != "")
            {
                dtdpt = DbHelperSQL.Query("select F_DeptId,F_DeptName,F_Remark from  T_Sys_Department where F_ParentId!=0 and F_DeptId=" + dpt).Tables[0];
            }
            else
            {
                dtdpt = DbHelperSQL.Query("select F_DeptId,F_DeptName,F_Remark from  T_Sys_Department where F_ParentId!=0").Tables[0];
            }
            if (dtdpt != null && dtdpt.Rows.Count > 0)
            {
                //foreach (DataRow dr in dtdpt.Rows)
                //{
                    DataTable dt = new DataTable();
                    //string sql = "SELECT F_UserCode,F_UserName,F_WorkNumber,F_UserId FROM T_Sys_UserAccount where f_seatFlag=1 and F_DeptId=" + dr["F_DeptId"] + " and F_WorkNumber!='' order by  F_WorkNumber asc";
                    //20180509 部门筛选框不能用 （需要去掉一个客服组8000所在） machenyang
                    var dptstr = "";
                var dptname = "";
                if (dtdpt.Rows.Count == 1) { 
                    dptstr = " and F_DeptId="+dtdpt.Rows[0]["F_DeptId"];
                    dptname = dtdpt.Rows[0]["F_DeptName"].ToString();
                }

                string sql = "SELECT F_UserCode,F_UserName,F_WorkNumber,F_UserId,F_DeptId FROM T_Sys_UserAccount where f_seatFlag=1 and F_WorkNumber!='' "+ dptstr + " order by  F_WorkNumber asc";
                dt = DbHelperSQL.Query(sql).Tables[0];
                var datadep = new BLL.T_Sys_Department().GetModelList("1=1");
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        if (!string.IsNullOrEmpty(dt.Rows[i]["F_WorkNumber"].ToString()))
                        {
                            DataRow drNew = dtNew.NewRow();
                            //drNew["部门"] = dr["F_DeptName"].ToString();
                            drNew["部门"] = datadep.Where(p => p.F_DeptId == Int32.Parse(dt.Rows[i]["F_DeptId"].ToString())).Select(p => p.F_DeptName).ToList()[0].ToString();
                            drNew["坐席"] = dt.Rows[i]["F_UserName"].ToString();
                            drNew["坐席工号"] = dt.Rows[i]["F_WorkNumber"].ToString();
                            //if (i == 0)
                            {//呼入电话

                                string strsql = "";
                                if (stime != null && stime.Trim() != "")
                                {
                                    strsql += " and CONVERT(varchar , TimeCallIn, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
                                }
                                if (endtime != null && endtime.Trim() != "")
                                {
                                    strsql += " and CONVERT(varchar , TimeCallIn, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
                                }

                                DataTable dtTrunk = DbHelperSQL.Query("select COUNT(*) as ct,SUM(DATEDIFF(Second,timewantagent,timeconnected)) as dd from rep_trunk_call_in where AgentID='" + dt.Rows[i]["F_UserCode"].ToString() + "' " + strsql).Tables[0];
                                DataTable dtcall = DbHelperSQL.Query("select COUNT(*) as ct,(select COUNT(*) from T_Call_CallRecords WITH(NOLOCK) where CallState=1 and CallType=0 and  UserCode='" + dt.Rows[i]["F_UserCode"].ToString() + "' " + strsqlcall + ") as jt from T_Call_CallRecords where CallType=0 and UserCode='" + dt.Rows[i]["F_UserCode"].ToString() + "' " + strsqlcall).Tables[0];

                                if (dtcall != null && dtcall.Rows.Count > 0)
                                {
                                    if (dtcall.Rows[0]["ct"].ToString() != "")
                                    {
                                        itrunkct = int.Parse(dtcall.Rows[0]["ct"].ToString());
                                    }
                                    else
                                    {
                                        itrunkct = 0;
                                    }
                                    if (dtcall.Rows[0]["jt"].ToString() != "")
                                    {
                                        itrunkjj = int.Parse(dtcall.Rows[0]["jt"].ToString());
                                    }
                                    else
                                    {
                                        itrunkjj = 0;
                                    }
                                    if (dtTrunk.Rows.Count > 0 && dtTrunk.Rows[0]["dd"].ToString() != "")
                                    {
                                        itrunkpjsc = int.Parse(dtTrunk.Rows[0]["dd"].ToString());
                                    }
                                    else
                                    {
                                        itrunkpjsc = 0;
                                    }
                                }
                                itrunkhs = itrunkct - itrunkjj;
                                if (itrunkct != 0)
                                {
                                    itrunkjtl = itrunkjj / double.Parse(itrunkct.ToString());
                                    itrunkhsl = itrunkhs / double.Parse(itrunkct.ToString());
                                    itrunkpjpd = itrunkpjsc / double.Parse(itrunkct.ToString());
                                }
                                else
                                {
                                    itrunkjtl = 0.00;
                                    itrunkhsl = 0.00;
                                    itrunkpjpd = 0.00;
                                }
                                drNew["呼入电话数"] = itrunkct;
                                drNew["坐席接通量"] = itrunkjj;
                                drNew["呼损量"] = itrunkhs;
                                drNew["接通率"] = (itrunkjtl * 100).ToString("0.00") + "%";
                                drNew["呼损率"] = (itrunkhsl * 100).ToString("0.00") + "%";
                                drNew["平均排队时间"] = itrunkpjpd.ToString("0.00");

                            }
                            //else
                            //{
                            //    drNew["呼入电话数"] = "0";
                            //    drNew["坐席接通量"] = "0";
                            //    drNew["呼损量"] = "0";
                            //    drNew["接通率"] = "0.00" + "%";
                            //    drNew["呼损率"] = "0.00" + "%";
                            //    drNew["平均排队时间"] = "0.00";
                            //}


                            DataTable dtext = DbHelperSQL.Query("select COUNT(*) as ct,SUM(PeriodTalking) as sc,SUM(PeriodAlerting) as pa from rep_ext_call_in where CalleeAgentID=" + dt.Rows[i]["F_WorkNumber"] + " " + sqltimeCallRecords).Tables[0];
                            DataTable dtCallEvey = DbHelperSQL.Query("select COUNT(*) as ct,SUM(TalkLongTime) as sc from T_Call_CallRecords WITH(NOLOCK) where CallState=1 and  userid=" + dt.Rows[i]["F_UserId"] + " " + strsqlcall).Tables[0];
                            if (dtCallEvey != null && dtCallEvey.Rows.Count > 0)
                            {
                                if (dtCallEvey.Rows[0]["ct"].ToString() != "")
                                {
                                    ijtcs = int.Parse(dtCallEvey.Rows[0]["ct"].ToString());//接听次数
                                }
                                else
                                {
                                    ijtcs = 0;
                                }
                                if (dtCallEvey.Rows[0]["sc"].ToString() != "")
                                {
                                    ithms = int.Parse(dtCallEvey.Rows[0]["sc"].ToString());//通话秒数
                                }
                                else
                                {
                                    ithms = 0;
                                }
                                if (dtext.Rows[0]["pa"].ToString() != "")
                                {
                                    izlms = int.Parse(dtext.Rows[0]["pa"].ToString());//振铃秒数
                                }
                                else
                                {
                                    izlms = 0;
                                }
                            }
                            drNew["接听通话次数"] = ijtcs.ToString();
                            //drNew["通话分钟数"] = (ithms / 60).ToString();
                            drNew["通话分钟数"] = CallCenter.Utility.DateTimeConvert.parseTimeSeconds(ithms, 0);
                            drNew["通话秒数"] = ithms.ToString();

                            if (ijtcs != 0)
                            {
                                ipjjtms = ithms / ijtcs;//平均接通秒数
                                ipjzlms = izlms / ijtcs;//平均振铃秒数
                            }
                            else
                            {
                                ipjjtms = 0;//平均接通秒数
                                ipjzlms = 0;//平均振铃秒数
                            }
                            drNew["平均接听通话秒数"] = ipjjtms.ToString();
                            drNew["平均振铃秒数"] = ipjzlms.ToString();

                            //坐席登录次数
                            DataTable dtAgent = DbHelperSQL.Query("select COUNT(*) as dl,SUM(LoginTimes) as dlsc,SUM(reposenum) as zm,SUM(ReposeTimes) as xx from rep_agent_detail where AgentId=" + dt.Rows[i]["F_WorkNumber"] + " " + sqltimeCallRecords.Replace("TimeAlerting", "TimeLogin")).Tables[0];
                            if (dtAgent != null && dtAgent.Rows.Count > 0)
                            {
                                if (dtAgent.Rows[0]["dl"] != null && dtAgent.Rows[0]["dl"].ToString() != "")
                                {
                                    izxdlcs = int.Parse(dtAgent.Rows[0]["dl"].ToString());//登录次数
                                }
                                else
                                {
                                    izxdlcs = 0;
                                }
                                if (dtAgent.Rows[0]["dlsc"] != null && dtAgent.Rows[0]["dlsc"].ToString() != "")
                                {
                                    idlsc = int.Parse(dtAgent.Rows[0]["dlsc"].ToString());//登录时长
                                }
                                else
                                {
                                    idlsc = 0;
                                }
                                if (dtAgent.Rows[0]["zm"] != null && dtAgent.Rows[0]["zm"].ToString() != "")
                                {
                                    izmcs = int.Parse(dtAgent.Rows[0]["zm"].ToString());//置忙次数
                                }
                                else
                                {
                                    izmcs = 0;
                                }
                                if (dtAgent.Rows[0]["xx"] != null && dtAgent.Rows[0]["xx"].ToString() != "")
                                {
                                    ixxsc = int.Parse(dtAgent.Rows[0]["xx"].ToString());//休息时长
                                }
                                else
                                {
                                    ixxsc = 0;
                                }

                            }
                            iWorktime = idlsc - ixxsc;//工作时长
                            if (izxdlcs != 0)
                            {
                                ipjczsc = iWorktime / izxdlcs;//平均操作秒数
                            }
                            else
                            {
                                ipjczsc = 0;
                            }
                            if (izmcs != 0)
                            {
                                ipjzmxxsc = ixxsc / izmcs;//置忙平均操作时长
                            }
                            else
                            {
                                ipjzmxxsc = 0;
                            }

                            //呼入占有率
                            if (iWorktime != 0)
                            {
                                ihrzyl = ithms / Convert.ToDouble(iWorktime);
                            }
                            else
                            {
                                ihrzyl = 0.00;
                            }
                            drNew["呼入占有率"] = (ihrzyl * 100).ToString("0.00") + "%";
                            //用户评价
                            //未评价为0
                            object obj = DbHelperSQL.GetSingle("select count(*) from T_Call_CallRecords WITH(NOLOCK) where MYD is not null and myd<>0 and CallState=1 and userid=" + dt.Rows[i]["F_UserId"] + strsqlcall);
                            string myd = "";
                            if (obj != null)
                            {
                                myd = obj.ToString();
                            }
                            drNew["用户评价"] = myd;
                            drNew["坐席登录次数"] = izxdlcs.ToString();
                            drNew["登录时长分钟数"] = (idlsc / 60).ToString();
                            drNew["工作时长分钟数"] = (iWorktime / 60).ToString();
                            drNew["平均每天工作时长"] = Math.Round((double.Parse(iWorktime.ToString()) / 60 / 60 / days), 2).ToString();
                            drNew["平均操作分钟数"] = (ipjczsc / 60).ToString();
                            drNew["置忙次数"] = izmcs.ToString();
                            drNew["休息时长分钟数"] = (ixxsc / 60).ToString();
                            drNew["平均每天休息时长"] = Math.Round((double.Parse(ixxsc.ToString()) / 60 / 60 / days), 2).ToString();
                            drNew["置忙平均休息分钟数"] = (ipjzmxxsc / 60).ToString();

                            DataSet dszj = DbHelperSQL.Query("select convert(int,SUM(f_qcscore)) as fs,COUNT(*) as fsct from T_Call_CallRecords WITH(NOLOCK) where F_QCState=1 and UserId=" + dt.Rows[i]["F_UserId"] + strsqlcall);
                            int zfs = 0;
                            if (dszj != null && dszj.Tables.Count > 0)
                            {
                                DataTable dtzj = dszj.Tables[0];
                                if (dtzj.Rows.Count > 0 && dtzj.Rows[0]["fs"].ToString() != "" && dtzj.Rows[0]["fsct"].ToString() != "")
                                {
                                    zfs = Convert.ToInt32(dtzj.Rows[0]["fs"].ToString()) / int.Parse(dtzj.Rows[0]["fsct"].ToString());
                                }
                            }
                            drNew["质检平均成绩"] = zfs.ToString();

                            dtNew.Rows.Add(drNew);
                        }
                    }
                //}
            }
            return dtNew;
        }
    }
}