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

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
{
    //  [Authority]
    public class TalkTimeController : BaseController
    {
        //通话时长统计

        //获取表头
        public ActionResult GetColumnList()
        {
            ActionResult res = NoToken("未知错误，请重新登录");

            String[] str = { "坐席名称", "呼入次数", "呼入时长(秒)", "呼出次数", "呼出时长(秒)", "呼出未接通次数", "振铃时长(秒)", "通话总时长(秒)", "平均通话时长(秒)" };
            res = Success("获取通话时长统计表头成功", str);

            return res;
        }

        //获取数据
        public ActionResult GetDataList(string stime, string endtime, string dpt)
        {
            ActionResult res = NoToken("未知错误，请重新登录");

            DataTable dtNew = new DataTable();
            dtNew = GetData(stime, endtime, dpt);
            #region
            //DataTable dtNew = new DataTable();
            //#region 编辑表头
            //DataColumn dc1 = new DataColumn("坐席名称", Type.GetType("System.String"));
            //DataColumn dc2 = new DataColumn("呼入次数", Type.GetType("System.String"));
            //DataColumn dc3 = new DataColumn("呼入时长", Type.GetType("System.String"));
            //DataColumn dc4 = new DataColumn("呼出次数", Type.GetType("System.String"));
            //DataColumn dc5 = new DataColumn("呼出时长", Type.GetType("System.String"));
            //DataColumn dc6 = new DataColumn("呼出未接通次数", Type.GetType("System.String"));
            //DataColumn dc7 = new DataColumn("振铃时长", Type.GetType("System.String"));
            //DataColumn dc8 = new DataColumn("通话总时长", Type.GetType("System.String"));
            //DataColumn dc9 = new DataColumn("平均通话总时长", 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);
            //#endregion

            //string[] CallInTime = GetCallRecordIn(stime, endtime, "0").TrimStart('[').TrimEnd(']').Split(',');
            //string[] CallInTimes = GetCallRecordTotalTime(stime, endtime, "0").TrimStart('[').TrimEnd(']').Split(',');
            //string[] CallOutTime = GetCallRecordOut(stime, endtime, "1","").TrimStart('[').TrimEnd(']').Split(',');
            //string[] CallOutTimes = GetCallRecordTotalTime(stime, endtime, "1").TrimStart('[').TrimEnd(']').Split(',');
            //string[] CallOutWTime = GetCallRecordOut(stime, endtime, "1", "0").TrimStart('[').TrimEnd(']').Split(',');
            //string[] CallOutWTimes = GetCallRecordRingTime(stime, endtime).TrimStart('[').TrimEnd(']').Split(',');
            //string[] CallTotalTime = GetCallRecordTotalTime(stime, endtime, "").TrimStart('[').TrimEnd(']').Split(',');
            //string[] AgentArr = GetAgent().TrimStart('[').TrimEnd(']').Split(',');
            //string[] AverageTime = GetCallAverageTime(stime, endtime).TrimStart('[').TrimEnd(']').Split(',');

            //for (int i = 0; i < AgentArr.Length; i++)
            //{
            //    DataRow drNew = dtNew.NewRow();

            //    drNew["坐席名称"] = AgentArr[i].Replace('\'', ' ');
            //    drNew["呼入次数"] = CallInTime[i];
            //    drNew["呼入时长"] = CallInTimes[i];
            //    drNew["呼出次数"] = CallOutTime[i];
            //    drNew["呼出时长"] = CallOutTimes[i];
            //    drNew["呼出未接通次数"] = CallOutWTime[i];
            //    drNew["振铃时长"] = CallOutWTimes[i];
            //    drNew["通话总时长"] = CallTotalTime[i];
            //    drNew["平均通话总时长"] = AverageTime[i];

            //    dtNew.Rows.Add(drNew);
            //}
            #endregion
            res = Success("获取通话时长数据成功", dtNew);

            return res;
        }

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

        //获取数据源
        private DataTable GetData(string stime, string endtime, string dpt)
        {
            DataTable dtNew = new DataTable();
            #region 编辑表头
            DataColumn dc1 = new DataColumn("坐席名称", Type.GetType("System.String"));
            DataColumn dc2 = new DataColumn("呼入次数", Type.GetType("System.String"));
            DataColumn dc3 = new DataColumn("呼入时长", Type.GetType("System.String"));
            DataColumn dc4 = new DataColumn("呼出次数", Type.GetType("System.String"));
            DataColumn dc5 = new DataColumn("呼出时长", Type.GetType("System.String"));
            DataColumn dc6 = new DataColumn("呼出未接通次数", Type.GetType("System.String"));
            DataColumn dc7 = new DataColumn("振铃时长", Type.GetType("System.String"));
            DataColumn dc8 = new DataColumn("通话总时长", Type.GetType("System.String"));
            DataColumn dc9 = new DataColumn("平均通话总时长", 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);
            #endregion

            string[] CallInTime = GetCallRecordIn(stime, endtime, "0", dpt).TrimStart('[').TrimEnd(']').Split(',');
            string[] CallInTimes = GetCallRecordTotalTime(stime, endtime, "0", dpt).TrimStart('[').TrimEnd(']').Split(',');
            string[] CallOutTime = GetCallRecordOut(stime, endtime, "1", "", dpt).TrimStart('[').TrimEnd(']').Split(',');
            string[] CallOutTimes = GetCallRecordTotalTime(stime, endtime, "1", dpt).TrimStart('[').TrimEnd(']').Split(',');
            string[] CallOutWTime = GetCallRecordOut(stime, endtime, "1", "0", dpt).TrimStart('[').TrimEnd(']').Split(',');
            string[] CallOutWTimes = GetCallRecordRingTime(stime, endtime, dpt).TrimStart('[').TrimEnd(']').Split(',');
            string[] CallTotalTime = GetCallRecordTotalTime(stime, endtime, "", dpt).TrimStart('[').TrimEnd(']').Split(',');
            string[] AgentArr = GetAgent(dpt).TrimStart('[').TrimEnd(']').Split(',');
            string[] AverageTime = GetCallAverageTime(stime, endtime, dpt).TrimStart('[').TrimEnd(']').Split(',');

            for (int i = 0; i < AgentArr.Length; i++)
            {
                DataRow drNew = dtNew.NewRow();

                drNew["坐席名称"] = AgentArr[i].Replace('\'', ' ');
                drNew["呼入次数"] = CallInTime[i];
                drNew["呼入时长"] = CallInTimes[i];
                drNew["呼出次数"] = CallOutTime[i];
                drNew["呼出时长"] = CallOutTimes[i];
                drNew["呼出未接通次数"] = CallOutWTime[i];
                drNew["振铃时长"] = CallOutWTimes[i];
                drNew["通话总时长"] = CallTotalTime[i];
                drNew["平均通话总时长"] = AverageTime[i];

                dtNew.Rows.Add(drNew);
            }

            return dtNew;
        }

        #region
        /// <summary>
        /// 获取坐席的呼出次数
        /// </summary>
        /// <param name="BeginTime">开始时间</param>
        /// <param name="EndTime">结束时间</param>
        /// <param name="Tag">1表示呼出，0表示呼入</param>
        /// <returns></returns>
        public string GetCallRecordOut(string BeginTime, string EndTime, string Tag, string state, string dpt)
        {
            return GetHighChartData(BeginTime, EndTime, Tag, state, dpt);
        }
        /// <summary>
        /// 获取通话总时长
        /// </summary>
        /// <param name="BeginTime">开始时间</param>
        /// <param name="EndTime">结束时间</param>
        /// <returns></returns>
        public string GetCallRecordTotalTime(string BeginTime, string EndTime, string tag, string dpt)
        {
            string result = "[";
            string sql = "";

            if (BeginTime != null && BeginTime != "")
            {
                sql += " and DATEDIFF(d,BeginTime,'" + BeginTime + "')<=0";
            }
            if (EndTime != null && EndTime != "")
            {
                sql += " and DATEDIFF(d,BeginTime,'" + EndTime + "')>=0";
            }
            if (dpt != null && dpt.Trim() != "")
            {
                sql += $" AND UserCode IN (SELECT F_UserCode FROM dbo.T_Sys_UserAccount WHERE F_DeptId = {dpt} AND f_seatFlag=1 and F_WorkNumber!='') ";
            }
            if (tag != "")
            {
                sql += " and CallType=" + tag;
            }
            var userAccountList = new BLL.T_Sys_UserAccount().GetModelList(" F_SeatFlag=1");
            //DataTable dt = DbHelperSQL.Query("SELECT UserId,UserCode,UserName,COUNT(*) AS calloutCount,SUM(TalkLongTime) AS TalkSummaryTime FROM T_Call_CallRecords where 1=1 " + sql + "  GROUP BY UserId,UserCode,UserName").Tables[0];
            //DataTable dt = DbHelperSQL.Query("SELECT UserId,UserCode,UserName,COUNT(*) AS calloutCount FROM T_Call_CallRecords where 1=1 " + sql + "  GROUP BY UserId,UserCode,UserName").Tables[0];
            var recordstr = " select users.F_UserId,users.F_UserCode,users.F_UserName,COUNT(*) AS calloutCount,SUM(TalkLongTime) AS TalkSummaryTime from T_Sys_UserAccount users left join T_Call_CallRecords records on users.F_UserCode = records.UserCode and 1=1 " + sql + " GROUP BY users.F_UserId,users.F_UserCode,users.F_UserName";
            DataTable dt = DbHelperSQL.Query(recordstr).Tables[0];

            foreach (Model.T_Sys_UserAccount item in userAccountList)
            {
                DataRow drUser = dt.AsEnumerable().FirstOrDefault(dr => dr.Field<string>("F_UserCode") == item.F_UserCode);

                if (drUser != null && drUser[4].ToString() != "")
                    result += drUser[4].ToString() + ",";
                else
                    result += "0,";
            }

            result = result.TrimEnd(',');
            return result + "]";
        }
        /// <summary>
        /// 获取坐席名称（x轴数据）
        /// </summary>
        /// <returns></returns>
        public string GetAgent(string dpt)
        {
            string sqluser = "";
            if (dpt != null && dpt.Trim() != "")
            {
                sqluser += $" and F_DeptId = {dpt} ";
            }
            string result = "[";
            var userAccountList = new BLL.T_Sys_UserAccount().GetModelList($" F_SeatFlag=1 {sqluser}");
            foreach (Model.T_Sys_UserAccount item in userAccountList)
            {
                result += "'" + item.F_UserName + "',";
            }
            result = result.TrimEnd(',');
            return result + "]";
        }
        /// <summary>
        /// 获取通话平均时长
        /// </summary>
        /// <param name="BeginTime">开始时间</param>
        /// <param name="EndTime">结束时间</param>
        /// <returns></returns>
        public string GetCallAverageTime(string BeginTime, string EndTime, string dpt)
        {
            string result = "[";

            string[] CallInTime = GetCallRecordIn(BeginTime, EndTime, "0", dpt).TrimStart('[').TrimEnd(']').Split(',');
            string[] CallOutTime = GetCallRecordOut(BeginTime, EndTime, "1", "", dpt).TrimStart('[').TrimEnd(']').Split(',');

            string[] CallTotalTime = GetCallRecordTotalTime(BeginTime, EndTime, "", dpt).TrimStart('[').TrimEnd(']').Split(',');

            for (int i = 0; i < CallInTime.Length; i++)
            {
                double subt = 0.0;
                if (double.Parse(CallInTime[i]) != 0)
                {
                    subt += double.Parse(CallInTime[i]);
                }
                if (double.Parse(CallOutTime[i]) != 0)
                {
                    subt += double.Parse(CallOutTime[i]);
                }
                if (subt != 0)
                    result += (double.Parse(CallTotalTime[i]) / subt).ToString("F2") + ",";
                else
                    result += "0,";
            }
            result = result.TrimEnd(',');
            return result + "]";
        }
        /// <summary>
        /// 获取坐席的呼入次数
        /// </summary>
        /// <param name="BeginTime">开始时间</param>
        /// <param name="EndTime">结束时间</param>
        /// <returns></returns>
        public string GetCallRecordIn(string BeginTime, string EndTime, string Tag, string dpt)
        {
            return GetHighChartData(BeginTime, EndTime, Tag, "", dpt);
        }
        /// <summary>
        /// 获取呼入呼出数据
        /// </summary>
        /// <param name="BeginTime">开始时间</param>
        /// <param name="EndTime">结束时间</param>
        /// <param name="Tag">1表示呼出，0表示呼入</param>
        /// <returns></returns>
        private string GetHighChartData(string BeginTime, string EndTime, string Tag, string state, string dpt)
        {
            string result = "[";
            string sql = " and CallType=" + Tag; // 呼入
            string sqluser = ""; // 呼入

            if (!string.IsNullOrEmpty(BeginTime))
            {
                sql += " and BeginTime>='" + BeginTime + " 00:00:00'";
                //sql += " and DATEDIFF(d,BeginTime,'" + BeginTime + "')<=0";
            }
            if (!string.IsNullOrEmpty(EndTime))
            {
                sql += " and BeginTime<='" + EndTime + " 23:59:59'";
                //sql += " and DATEDIFF(d,BeginTime,'" + EndTime + "')>=0";
            }
            if (state.Trim() != "")
            {
                sql += " and CallState=" + state.Trim();
            }
            if (dpt != null && dpt.Trim() != "")
            {
                sqluser += $" and F_DeptId = {dpt} ";
                sql += $" AND UserCode IN (SELECT F_UserCode FROM dbo.T_Sys_UserAccount WHERE F_DeptId = {dpt} AND f_seatFlag=1 and F_WorkNumber!='') ";
            }

            var userAccountList = new BLL.T_Sys_UserAccount().GetModelList($" F_SeatFlag=1  {sqluser}");
            //DataTable dt = DbHelperSQL.Query("SELECT UserId,UserCode,UserName,COUNT(*) AS calloutCount FROM T_Call_CallRecords where 1=1 " + sql + "  GROUP BY UserId,UserCode,UserName").Tables[0];
            //var recordstr = " select users.F_UserId,users.F_UserCode,users.F_UserName,COUNT(*) AS calloutCount from T_Sys_UserAccount users right join T_Call_CallRecords records on users.F_UserCode = records.UserCode and 1=1 " + sql + " GROUP BY users.F_UserId,users.F_UserCode,users.F_UserName";
            //20180509 通话时长统计不对  machenyang
            //20180509 通话时长呼入次数 与 坐席工作报表 呼入电话数 数值不统一 修改（原因是一个是根据工号查询，一个是工号姓名【由于可能工号不变，会修改姓名】）  lihai
            //var recordstr = " select records.UserCode,records.UserId,records.UserName,COUNT(*) from T_Call_CallRecords records where 1=1 " + sql + "  and UserCode is not null group by records.UserCode,records.UserId,records.UserName";
            var recordstr = " select records.UserCode,COUNT(*) from T_Call_CallRecords records where 1=1 " + sql + "  and UserCode is not null group by records.UserCode";
            DataTable dt = DbHelperSQL.Query(recordstr).Tables[0];
            Dictionary<string, int> myDictionary = new Dictionary<string, int>();

            foreach (Model.T_Sys_UserAccount item in userAccountList)
            {
                if (!myDictionary.ContainsKey(item.F_UserCode))
                    myDictionary.Add(item.F_UserCode, 0);

                DataRow drUser = dt.AsEnumerable().FirstOrDefault(dr => dr.Field<string>("UserCode") == item.F_UserCode);

                if (drUser != null)
                {
                    myDictionary[item.F_UserCode] += int.Parse(drUser[1].ToString());
                    //myDictionary[item.F_UserCode] += int.Parse(drUser[3].ToString());
                }
            }

            foreach (var item in myDictionary)
            {
                result += item.Value + ",";
            }

            result = result.TrimEnd(',');
            return result + "]";
        }
        /// <summary>
        /// 获取振铃时长
        /// </summary>
        /// <param name="BeginTime"></param>
        /// <param name="EndTime"></param>
        /// <returns></returns>
        public string GetCallRecordRingTime(string BeginTime, string EndTime, string dpt)
        {
            string result = "[";
            string sql = "";

            if (!string.IsNullOrEmpty(BeginTime))
            {
                sql += " and BeginTime>='" + BeginTime + "'";
                //sql += " and DATEDIFF(d,BeginTime,'" + BeginTime + "')<=0";
            }
            if (!string.IsNullOrEmpty(EndTime))
            {
                sql += " and BeginTime<='" + EndTime + "'";
                //sql += " and DATEDIFF(d,BeginTime,'" + EndTime + "')>=0";
            }
            if (dpt != null && dpt.Trim() != "")
            {
                sql += $" AND UserCode IN (SELECT F_UserCode FROM dbo.T_Sys_UserAccount WHERE F_DeptId = {dpt} AND f_seatFlag=1 and F_WorkNumber!='') ";
            }
            //只统计呼入振铃时长
            sql += " and CallType=0 ";

            var userAccountList = new BLL.T_Sys_UserAccount().GetModelList(" F_SeatFlag=1");
            DataTable dt = DbHelperSQL.Query("  select UserCode,COUNT(1) count,SUM(Ringtimes) as RingTimes from(SELECT UserCode, datediff(second, RingStartTime, RingEndTime) as Ringtimes FROM T_Call_CallRecords where 1 = 1 " + sql + ") t GROUP BY UserCode").Tables[0];

            foreach (Model.T_Sys_UserAccount item in userAccountList)
            {
                DataRow drUser = dt.AsEnumerable().FirstOrDefault(dr => dr.Field<string>("UserCode") == item.F_UserCode);

                if (drUser != null && drUser[2].ToString() != "")
                    result += drUser[2].ToString() + ",";
                else
                    result += "0,";
            }

            result = result.TrimEnd(',');
            return result + "]";
        }
        #endregion
    }
}