Anyang_api/CallCenterApi/CallCenterAp.../CallCenterAp.../Controllers/tel/SeatMonitori...

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.tel
{
    //[Authority]
    public class SeatMonitoringController : BaseController
    {
        BLL.T_Sys_UserAccount Userbll = new BLL.T_Sys_UserAccount();
        BLL.T_Call_CallRecords callBLL = new BLL.T_Call_CallRecords();
        /// <summary>
        /// 获取坐席列表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetList()
        {
            DataTable dt = new DataTable();
            dt = Userbll.GetList(" f_seatflag=1", "RIGHT(F_UserName ,1) asc").Tables[0];

            if (dt.Rows.Count > 0)
            {
                return Success("坐席列表加载成功", dt);
            }
            else
            {
                return Error("坐席列表加载失败");
            }
        }

        //坐席监控列表
        public ActionResult GetAgentList()
        {
            DataTable dtNew = new DataTable();
            #region 编辑表头
            DataColumn dc1 = new DataColumn("UserCode", Type.GetType("System.String"));//工号
            DataColumn dc2 = new DataColumn("UserName", Type.GetType("System.String"));//姓名
            DataColumn dc3 = new DataColumn("ExtNumber", Type.GetType("System.String"));//分机号
            DataColumn dc4 = new DataColumn("free", Type.GetType("System.String"));//空闲时长
            DataColumn dc5 = new DataColumn("repose", Type.GetType("System.String"));//置忙时长
            DataColumn dc6 = new DataColumn("talking", Type.GetType("System.String"));//通话时长
            DataColumn dc7 = new DataColumn("postprocess", Type.GetType("System.String"));//话后处理时长
            DataColumn dc8 = new DataColumn("reposeconut", Type.GetType("System.String"));//置忙次数
            DataColumn dc9 = new DataColumn("logcount", Type.GetType("System.String"));//登录次数
            DataColumn dc10 = new DataColumn("logintime", Type.GetType("System.String"));//签入时间
            DataColumn dc11 = new DataColumn("logtimes", Type.GetType("System.String"));//当前签入时长
            DataColumn dc12 = new DataColumn("callincount", Type.GetType("System.String"));//呼入量
            DataColumn dc13 = new DataColumn("calloutcount", Type.GetType("System.String"));//呼出量

            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);
            #endregion
            string sql = " select *,DATEDIFF(SECOND,LoginTime,GETDATE())times from rep_agentState_Duration where DATEDIFF(day,logintime,getdate())=0 and LogoutTime is null order by logintime ";
            string sql1 = " select AgentID,SUM(free) free,SUM(repose) repose,SUM(talking) talking,SUM(postprocess) postprocess,SUM(reposeconut) reposeconut,COUNT(1) logcount "
                + " from rep_agentState_Duration " + " where DATEDIFF(day, logintime, getdate()) = 0 " +
                " group by AgentID ";
            string sql2 = " select usercode,calltype,count(1) callcount from T_Call_CallRecords where DATEDIFF(day, begintime, getdate()) = 0 group by calltype,usercode ";

            DataTable dtagent = DbHelperSQL.Query(sql).Tables[0];
            DataTable dtCount = DbHelperSQL.Query(sql1).Tables[0];
            DataTable dtCall = DbHelperSQL.Query(sql2).Tables[0];

            List<Model.T_Sys_UserAccount> userList = Userbll.GetModelList(" f_seatflag=1 "," f_userid desc ");
            for (int i = 0; i < dtagent.Rows.Count; i++)
            {
                DataRow drNew = dtNew.NewRow();

                Model.T_Sys_UserAccount User = userList.Where(u => u.F_UserCode == dtagent.Rows[i]["AgentID"].ToString()).FirstOrDefault();
                if (User != null)
                    drNew["UserName"] = User.F_UserName;
                else
                    drNew["UserName"] = "";

                drNew["UserCode"] = dtagent.Rows[i]["AgentID"];
                drNew["ExtNumber"] = dtagent.Rows[i]["Exten"];
                drNew["logintime"] = Convert.ToDateTime(dtagent.Rows[i]["LoginTime"].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
                drNew["logtimes"] = dtagent.Rows[i]["times"];
                drNew["free"] = "0";
                drNew["repose"] = "0";
                drNew["talking"] = "0";
                drNew["postprocess"] = "0";
                drNew["reposeconut"] = "0";
                drNew["logcount"] = "0";
                drNew["callincount"] = "0";
                drNew["calloutcount"] = "0";

                for (int j = 0; j < dtCount.Rows.Count; j++)
                {
                    if (dtCount.Rows[j]["AgentID"].ToString() == dtagent.Rows[i]["AgentID"].ToString())
                    {
                        drNew["free"] = dtCount.Rows[j]["free"];
                        drNew["repose"] = dtCount.Rows[j]["repose"];
                        drNew["talking"] = dtCount.Rows[j]["talking"];
                        drNew["postprocess"] = dtCount.Rows[j]["postprocess"];
                        drNew["reposeconut"] = dtCount.Rows[j]["reposeconut"];
                        drNew["logcount"] = dtCount.Rows[j]["logcount"];
                        break;
                    }
                }
                for (int z = 0; z < dtCall.Rows.Count; z++)
                {
                    if (dtCall.Rows[z]["usercode"].ToString() == dtagent.Rows[i]["AgentID"].ToString())
                    {
                        if (dtCall.Rows[z]["calltype"].ToString() == "0")
                            drNew["callincount"] = dtCall.Rows[z]["callcount"].ToString();
                        if (dtCall.Rows[z]["calltype"].ToString() == "1")
                            drNew["calloutcount"] = dtCall.Rows[z]["callcount"].ToString();
                    }
                }

                var drnn = dtNew.Select("UserCode='" + dtagent.Rows[i]["AgentID"] + "'");
                if (drnn.Length > 0)
                {
                    foreach (DataRow row in drnn)
                    {
                        row["logintime"] = drNew["logintime"];
                        row["logtimes"] = drNew["logtimes"];
                        row["free"] = drNew["free"];
                        row["repose"] = drNew["repose"];
                        row["talking"] = drNew["talking"];
                        row["postprocess"] = drNew["postprocess"];
                        row["reposeconut"] = drNew["reposeconut"];
                        row["logcount"] = drNew["logcount"];
                        row["callincount"] = drNew["callincount"];
                        row["calloutcount"] = drNew["calloutcount"];
                    }
                }
                else
                    dtNew.Rows.Add(drNew);
            }
            
            return Success("坐席监控数据获取成功", dtNew);
        }

        //坐席监控报表
        public ActionResult GetReportList(string stime, string etime)
        {
            DataTable dtNew = new DataTable();
            dtNew = GetData(stime, etime);
            return Success("坐席监控报表获取", dtNew);

        }
        //获取数据源
        private DataTable GetData(string stime, string endtime)
        {
            DataTable dtNew = new DataTable();
            #region 编辑表头
            DataColumn dc0 = new DataColumn("Date", Type.GetType("System.String"));//工号
            DataColumn dc1 = new DataColumn("UserCode", Type.GetType("System.String"));//工号
            DataColumn dc2 = new DataColumn("UserName", Type.GetType("System.String"));//姓名
            DataColumn dc3 = new DataColumn("ExtNumber", Type.GetType("System.String"));//分机号
            DataColumn dc4 = new DataColumn("free", Type.GetType("System.String"));//空闲时长
            DataColumn dc5 = new DataColumn("repose", Type.GetType("System.String"));//置忙时长
            DataColumn dc6 = new DataColumn("talking", Type.GetType("System.String"));//通话时长
            DataColumn dc7 = new DataColumn("postprocess", Type.GetType("System.String"));//话后处理时长
            DataColumn dc8 = new DataColumn("reposeconut", Type.GetType("System.String"));//置忙次数
            DataColumn dc9 = new DataColumn("logcount", Type.GetType("System.String"));//登录次数
            DataColumn dc10 = new DataColumn("logintime", Type.GetType("System.String"));//签入时间
            DataColumn dc11 = new DataColumn("logouttime", Type.GetType("System.String"));//签出时间
            DataColumn dc12 = new DataColumn("logintimes", Type.GetType("System.String"));//签入总时长
            DataColumn dc13 = new DataColumn("callincount", Type.GetType("System.String"));//呼入量
            DataColumn dc14 = new DataColumn("calloutcount", Type.GetType("System.String"));//呼出量
            dtNew.Columns.Add(dc0);
            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);
            #endregion

            DateTime dts = DateTime.Now, dte = DateTime.Now;

            if (stime != null && stime.Trim() != "")
            {
                dts = DateTime.Parse(stime.Trim());
            }
            if (endtime != null && endtime.Trim() != "")
            {
                dte = DateTime.Parse(endtime.Trim());
            }
   


            List<Model.T_Sys_UserAccount> userList = Userbll.GetModelList(" f_seatflag=1 ","F_UserCode ");
            for (DateTime dt = dts; dt <= dte; dt = dt.AddDays(1))
            {
                string wh1 = " and DATEDIFF(second ,logintime,'" + dt.ToString("yyyy-MM-dd HH:mm:ss") + "')<=0 and DATEDIFF(day,logintime,'" + dte.ToString("yyyy-MM-dd HH:mm:ss")  + "')>=0 ";
                string wh2 = " and DATEDIFF(second ,begintime,'" + dts.ToString("yyyy-MM-dd HH:mm:ss")  + "')<=0 and DATEDIFF(day,begintime,'" + dte.ToString("yyyy-MM-dd HH:mm:ss")  + "')>=0 ";

                string sql = " select * from rep_agentState_Duration where LogoutTime is not null " + wh1 + " order by AgentID asc, logintime desc ";
                string sql1 = " select AgentID,SUM(free) free,SUM(repose) repose,SUM(talking) talking,SUM(postprocess) postprocess,SUM(reposeconut) reposeconut,COUNT(1) logcount,SUM(DATEDIFF(second,logintime,logouttime)) logtimes "
                    + " from rep_agentState_Duration  where LogoutTime is not null" + wh1 + " group by AgentID  order by AgentID ";
                string sql2 = " select usercode,calltype,count(1) callcount from T_Call_CallRecords where 1=1 " + wh2 + " group by calltype,usercode ";

                //string sql3 = "select AgentID,Exten,MIN(LoginTime)  LoginTime,MAX(LogoutTime) LogoutTime  from rep_agentState_Duration where LogoutTime is not null " + wh1 + " group by AgentID,Exten ";
                string sql3 = "select AgentID,MIN(LoginTime)  LoginTime,MAX(LogoutTime) LogoutTime  from rep_agentState_Duration where LogoutTime is not null " + wh1 + " group by AgentID ";
                string sql4 = " select AgentID,Exten from rep_agentState_Duration a " +
                            "where LogoutTime is not null  " + wh1 +
                            "and ID = (select MAX(ID)  from rep_agentState_Duration b where LogoutTime is not null " + wh1 +
                            "and a.AgentID = b.AgentID group by AgentID) order by LoginTime desc";
                DataTable dtagent = DbHelperSQL.Query(sql3).Tables[0];
                DataTable dtExt = DbHelperSQL.Query(sql4).Tables[0];
                //DataTable dtagent = DbHelperSQL.Query(sql).Tables[0];
                DataTable dtCount = DbHelperSQL.Query(sql1).Tables[0];
                DataTable dtCall = DbHelperSQL.Query(sql2).Tables[0];

                foreach (Model.T_Sys_UserAccount item in userList)
                {
                    DataRow drNew = dtNew.NewRow();
                    drNew["Date"] = dt.ToString ("yyyy-MM-dd");
                    drNew["UserCode"] = item.F_UserCode;
                    drNew["UserName"] = item.F_UserName;
                    #region 初始化
                    drNew["ExtNumber"] = "";
                    drNew["logintime"] = "";
                    drNew["logouttime"] = "";
                    drNew["logintimes"] = DateTimeConvert.parseTimeSeconds(int.Parse("0"), 0);
                    drNew["free"] = DateTimeConvert.parseTimeSeconds(int.Parse("0"), 0);
                    drNew["repose"] = DateTimeConvert.parseTimeSeconds(int.Parse("0"), 0);
                    drNew["talking"] = DateTimeConvert.parseTimeSeconds(int.Parse("0"), 0);
                    drNew["postprocess"] = DateTimeConvert.parseTimeSeconds(int.Parse("0"), 0);
                    drNew["reposeconut"] = "0";
                    drNew["logcount"] = "0";
                    drNew["callincount"] = "0";
                    drNew["calloutcount"] = "0";
                    #endregion
                    for (int j = 0; j < dtagent.Rows.Count; j++)
                    {
                        if (dtagent.Rows[j]["AgentID"].ToString() == item.F_UserCode)
                        {
                            //drNew["ExtNumber"] = dtagent.Rows[j]["Exten"];
                            drNew["logintime"] = Convert.ToDateTime(dtagent.Rows[j]["LoginTime"].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
                            drNew["logouttime"] = Convert.ToDateTime(dtagent.Rows[j]["LogoutTime"].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
                            break;
                        }
                    }
                    for (int j = 0; j < dtExt.Rows.Count; j++)
                    {
                        if (dtExt.Rows[j]["AgentID"].ToString() == item.F_UserCode)
                        {
                            drNew["ExtNumber"] = dtExt.Rows[j]["Exten"];
                            break;
                        }
                    }

                    for (int j = 0; j < dtCount.Rows.Count; j++)
                    {
                        if (dtCount.Rows[j]["AgentID"].ToString() == item.F_UserCode)
                        {
                            drNew["free"] = DateTimeConvert.parseTimeSeconds(int.Parse(dtCount.Rows[j]["free"].ToString()), 0);
                            drNew["repose"] = DateTimeConvert.parseTimeSeconds(int.Parse(dtCount.Rows[j]["repose"].ToString()), 0);
                            drNew["talking"] = DateTimeConvert.parseTimeSeconds(int.Parse(dtCount.Rows[j]["talking"].ToString()), 0);
                            drNew["postprocess"] = DateTimeConvert.parseTimeSeconds(int.Parse(dtCount.Rows[j]["postprocess"].ToString()), 0);
                            drNew["reposeconut"] = dtCount.Rows[j]["reposeconut"];
                            drNew["logcount"] = dtCount.Rows[j]["logcount"];
                            drNew["logintimes"] = DateTimeConvert.parseTimeSeconds(int.Parse(dtCount.Rows[j]["logtimes"].ToString()), 0);
                            break;
                        }
                    }
                    for (int z = 0; z < dtCall.Rows.Count; z++)
                    {
                        if (dtCall.Rows[z]["usercode"].ToString() == item.F_UserCode)
                        {
                            if (dtCall.Rows[z]["calltype"].ToString() == "0")
                                drNew["callincount"] = dtCall.Rows[z]["callcount"].ToString();
                            if (dtCall.Rows[z]["calltype"].ToString() == "1")
                                drNew["calloutcount"] = dtCall.Rows[z]["callcount"].ToString();
                        }
                    }

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

        private string[] getcols()
        {
            return new string[] { "日期","工号", "姓名", "分机号", "空闲时长", "置忙时长", "通话时长", "话后处理时长", "置忙次数", "登录次数", "签入时间", "签出时间", "签入总时长", "呼入量", "呼出量" };
        }

        //导出数据
        public ActionResult ExptList(string stime, string endtime)
        {
            NPOIHelper npoi = new NPOIHelper();
            DataTable dt = GetData(stime, endtime);
            if (npoi.ExportToExcel("坐席考勤报表", dt, getcols()) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");
            }

        }

        /// <summary>
        /// 坐席考勤详细报表
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="etime"></param>
        /// <returns></returns>
        public ActionResult GetReportDetailList(int isdc=0)
        {
            string strusercode = HttpUtility.UrlDecode(RequestString.GetQueryString("usercode"));
            string strstarttime = HttpUtility.UrlDecode(RequestString.GetQueryString("stime"));
            string strendtime = HttpUtility.UrlDecode(RequestString.GetQueryString("etime"));

            string strpageindex = RequestString.GetQueryString("page");
            int pageindex = 1;
            string strpagesize = RequestString.GetQueryString("pagesize");
            int pagesize = 10;

            string sqlwhere = "";

            if (strusercode.Trim() != "" && strusercode != "undefined")
            {
                sqlwhere += " and UserCode = '" + strusercode.Trim() + "' ";
            }
            if (strstarttime.Trim() != "" && strstarttime != "undefined")
            {
                sqlwhere += " and datediff(day,logintime,'" + strstarttime + "')<=0 ";
            }
            if (strendtime.Trim() != "" && strendtime != "undefined")
            {
                sqlwhere += " and datediff(day,logintime,'" + strendtime + "')>=0   ";
            }

            if (isdc > 0)
            {
                string[] cols= new string[] { "工号", "姓名", "分机号", "签入时间", "签出时间", "签入总时长", "空闲时长", "置忙时长", "通话时长", "话后处理时长", "置忙次数",  "呼入量", "呼出量" };
                var dtdc = DbHelperSQL.Query(" select * from V_Tel_SeatWork where 1=1 " + sqlwhere).Tables[0];
                dtdc.Columns.Remove("ID");
                var msg = new NPOIHelper().ExportToExcel("坐席考勤详细报表", dtdc, cols);
                if (msg == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }

            if (strpageindex.Trim() != "")
            {
                pageindex = Convert.ToInt32(strpageindex);
            }

            if (strpagesize.Trim() != "")
            {
                pagesize = Convert.ToInt32(strpagesize);
            }

            int recordCount = 0;
            var dt = BLL.PagerBLL.GetListPager(
                "V_Tel_SeatWork",
                "ID",
                "*",
                sqlwhere,
                "ORDER BY UserCode,logintime",
                pagesize,
                pageindex,
                true,
                out recordCount);

            var obj = new
            {
                state = "success",
                message = "成功",
                rows = dt,
                total = recordCount
            };
            return Content(obj.ToJson());

        }

    }
}