XiXianDC_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)
        {
            ActionResult res = NoToken("未知错误，请重新登录");
           
                DataTable dtNew = new DataTable();
                dtNew = GetData(stime, endtime);
                #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)
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            if (Request.IsAuthenticated)
            {
                NPOIHelper npoi = new NPOIHelper();
                DataTable dt = GetData(stime, endtime);
                if (npoi.ExportToExcel("通话时长数据", dt) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            return res;
        }

        //获取数据源
        private DataTable GetData(string stime, string endtime)
        {
            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"));
            DataColumn dc10 = new DataColumn("通话总时长", Type.GetType("System.String"));
            DataColumn dc11 = 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);
            dtNew.Columns.Add(dc10);
            dtNew.Columns.Add(dc11);
            #endregion

            string[] CallInTime = GetCallRecordIn(stime, endtime, "0","").TrimStart('[').TrimEnd(']').Split(',');
            string[] CallInTimes = GetCallRecordTotalTime(stime, endtime, "0").TrimStart('[').TrimEnd(']').Split(',');
            string[] CallInWTime = GetCallRecordIn(stime, endtime, "1", "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[] CallTime = GetCallRecord(stime, endtime, "", "1").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["呼入未接通次数"] = CallInWTime[i];
                drNew["呼出次数"] = CallOutTime[i];
                drNew["呼出时长"] = CallOutTimes[i];
                drNew["呼出未接通次数"] = CallOutWTime[i];
                drNew["振铃时长"] = CallOutWTimes[i];
                drNew["接通总次数"] = CallTime[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)
        {
            return GetHighChartData(BeginTime, EndTime, Tag, state);
        }
        /// <summary>
        /// 获取通话总时长
        /// </summary>
        /// <param name="BeginTime">开始时间</param>
        /// <param name="EndTime">结束时间</param>
        /// <returns></returns>
        public string GetCallRecordTotalTime(string BeginTime, string EndTime, string tag)
        {
            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 (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];

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

                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 result = "[";
            var userAccountList = new BLL.T_Sys_UserAccount().GetModelList(" F_SeatFlag=1");
            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 result = "[";

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

            string[] CallTotalTime = GetCallRecordTotalTime(BeginTime, EndTime,"").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 state)
        {
            return GetHighChartData(BeginTime, EndTime, Tag, state);
        }
        /// <summary>
        /// 获取坐席的接通次数
        /// </summary>
        /// <param name="BeginTime">开始时间</param>
        /// <param name="EndTime">结束时间</param>
        /// <returns></returns>
        public string GetCallRecord(string BeginTime, string EndTime, string Tag, string state)
        {
            return GetHighChartData(BeginTime, EndTime, Tag, state);
        }
        /// <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 result = "[";
            string sql = " 1=1 "; 
            var userAccountList = new BLL.T_Sys_UserAccount().GetModelList(" F_SeatFlag=1");

            if (Tag != "")
            {
                sql += " and CallType=" + Tag.Trim();
            }

            if (BeginTime != "")
            {
                //sql += " and BeginTime>='" + BeginTime + "'";
                sql += " and DATEDIFF(d,BeginTime,'" + BeginTime + "')<=0";
            }
            if (EndTime != "")
            {
                //sql += " and BeginTime<='" + EndTime + "'";
                sql += " and DATEDIFF(d,BeginTime,'" + EndTime + "')>=0";
            }
            if (state.Trim() != "")
            {
                sql += " and CallState=" + state.Trim();
            }

            DataTable dt = DbHelperSQL.Query("SELECT UserId,UserCode,UserName,COUNT(*) AS calloutCount FROM T_Call_CallRecords where " + sql + "  GROUP BY UserId,UserCode,UserName").Tables[0];
            Dictionary<string, int> myDictionary = new Dictionary<string, int>();

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

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

                if (drUser != null)
                {
                    myDictionary[item.F_UserName] += 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 result = "[";
            string sql = "";

            if (BeginTime != "")
            {
                //sql += " and BeginTime>='" + BeginTime + "'";
                sql += " and DATEDIFF(d,BeginTime,'" + BeginTime + "')<=0";
            }
            if (EndTime != "")
            {
                //sql += " and BeginTime<='" + EndTime + "'";
                sql += " and DATEDIFF(d,BeginTime,'" + EndTime + "')>=0";
            }
            sql += " and CallType=1  and CallState=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, EndTime) 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
    }
}