ZYKJ_API/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

            //17=系统管理员,36=客服,37=组长,38=组员,39=业主,40=话务员,41=客服主管,43=分公司市场部主任,45=业务稽核,46=集团客户部,
            //47 =网络部,48=市场部经理,50=业务支撑中心,51=市场部,52=客户服务中心,53=总经理,54=副总经理,55=业务负责人,56=投诉处理员,57=基础业务主管,


            var deptId = CurrentUser.UserData.F_DeptId;
            var roleId = CurrentUser.UserData.F_RoleId;
            var deptCode = CurrentUser.UserData.F_DeptCode;
            var usercode = CurrentUser.UserData.F_UserCode;
            var deptSQL = "";
            var usercodeSQL = "";
            if (roleId != 0)
            {
                if (roleId != 17)
                {
                    if (roleId == 36 || roleId == 38 || roleId == 40)
                    {
                        usercodeSQL += $" and F_UserCode='{usercode}'";
                    }
                    else
                    {
                        deptSQL += $" and F_DeptId={deptId} ";
                    }

                }
            }

            DataTable dtdpt = null;
            if (!string.IsNullOrWhiteSpace(dpt) && Convert.ToInt32(dpt) >= 0)
            {
                dtdpt = DbHelperSQL.Query("select F_DeptId,F_DeptName,F_Remark from  T_Sys_Department where F_ParentId!=0 and F_DeptId=" + dpt + deptSQL).Tables[0];
            }
            else
            {
                dtdpt = DbHelperSQL.Query("select F_DeptId,F_DeptName,F_Remark from  T_Sys_Department where F_ParentId!=0" + deptSQL).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!='' {usercodeSQL} 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);
                    }
                }
            }
            //}
            return dtNew;
        }
    }
}