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.Text;
using System.Web;
using System.Web.Mvc;

namespace CallCenterApi.Interface.Controllers.report
{
    [Authority]
    public class TalkTimeController : BaseController
    {
        //通话时长统计

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

        }

        //获取数据
        public ActionResult GetDataList(string stime, string endtime)
        {
            DataTable dtNew = new DataTable();
            dtNew = GetDataNew(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
            return Success("获取通话时长数据成功", dtNew);
        }

        //导出数据
        public ActionResult ExptList(string stime, string endtime)
        {
            NPOIHelper npoi = new NPOIHelper();
            DataTable dt = GetDataNew(stime, endtime);
            if (npoi.ExportToExcel("通话时长数据", dt) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");
            }
        }
        private DataTable GetDataNew(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"));

            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

            var sql = "";
            #region 筛选
            if (stime != null && stime != "")
            {
                sql += " and DATEDIFF(d,BeginTime,'" + stime + "')<=0";
            }
            if (endtime != null && endtime != "")
            {
                sql += " and DATEDIFF(d,BeginTime,'" + endtime + "')>=0";
            }
            #endregion
            var userAccountList = new BLL.T_Sys_UserAccount().GetModelList(" F_SeatFlag=1 and F_DeleteFlag=0 ");

            //通话时长
            var timeslist = DbHelperSQL.Query("SELECT UserCode,calltype,COUNT(*) AS callCount,SUM(TalkLongTime) AS TalkSummaryTime FROM T_Call_CallRecords where 1=1 " + sql + "  GROUP BY UserCode,calltype").Tables[0];
            //振铃时长
            var zllist= DbHelperSQL.Query(" SELECT UserCode, SUM(RingLongTime) RingTimes FROM T_Call_CallRecords where 1 = 1 and CallType=0 and CallState=0 " + sql + " GROUP BY UserCode ").Tables[0];
            //呼出未接通
            var hclist= DbHelperSQL.Query("SELECT UserCode,COUNT(*) AS calloutCount FROM T_Call_CallRecords where 1=1 and calltype=1 and callstate=0 " + sql + "  GROUP BY UserCode").Tables[0];

            foreach (var useraccount in userAccountList)
            {
                //呼入次数，呼入时长，呼出次数，呼出时长，呼出未接通次数，振铃时长
                int incount = 0, intimes = 0, outcount = 0, outtimes = 0, outwcount = 0, zltimes = 0;
                //呼入数据
                var drin = timeslist.Select("UserCode='" + useraccount.F_UserCode + "' and calltype=0");
                if (drin.Length > 0)
                {
                    incount = (from DataRow dr in drin select dr.Field<int>("callCount")).FirstOrDefault();
                    intimes = (from DataRow dr in drin select dr.Field<int>("TalkSummaryTime")).FirstOrDefault();
                }
                //呼出数据
                var drout = timeslist.Select("UserCode='" + useraccount.F_UserCode + "' and calltype=1");
                if (drout.Length > 0)
                {
                    outcount = (from DataRow dr in drout select dr.Field<int>("callCount")).FirstOrDefault();
                    outtimes = (from DataRow dr in drout select dr.Field<int>("TalkSummaryTime")).FirstOrDefault();
                }
                //呼出未接通数据
                var droutw = hclist.Select("UserCode='" + useraccount.F_UserCode + "' ");
                if (droutw.Length > 0)
                    outwcount = (from DataRow dr in droutw select dr.Field<int>("calloutCount")).FirstOrDefault();
                //振铃时长数据
                var drzl = zllist.Select("UserCode='" + useraccount.F_UserCode + "' ");
                if (drzl.Length > 0)
                    outwcount = (from DataRow dr in drzl select dr.Field<int>("RingTimes")).FirstOrDefault();

                double avgtimes = 0.00;

                if ((incount + outcount) > 0)
                    avgtimes = double.Parse((intimes + outtimes).ToString()) / double.Parse((incount + outcount).ToString());
                #region 保存数据
                DataRow drNew = dtNew.NewRow();
                drNew["坐席名称"] = useraccount.F_UserName + "(" + useraccount.F_UserCode + ")" ;
                drNew["呼入次数"] = incount;
                drNew["呼入时长"] = intimes;
                drNew["呼出次数"] = outcount;
                drNew["呼出时长"] = outtimes;
                drNew["呼出未接通次数"] = outwcount;
                drNew["振铃时长"] = zltimes;
                drNew["通话总时长"] = intimes+ outtimes;
                drNew["平均通话总时长"] = avgtimes.ToString("F2");

                dtNew.Rows.Add(drNew);
                #endregion
            }

            return dtNew;
        }
        #region 20191122 zhengbingbing 优化前
        //获取数据源
        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"));

            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);
            }

            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 != null && tag.Trim() != "")
            {
                sql += " and CallType=" + tag;
            }
            var userAccountList = new BLL.T_Sys_UserAccount().GetModelList(" F_SeatFlag=1");
            DataTable dt = DbHelperSQL.Query("SELECT UserCode,COUNT(*) AS calloutCount,SUM(TalkLongTime) AS TalkSummaryTime FROM T_Call_CallRecords where 1=1 " + sql + "  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 + "]";
        }
        /// <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 + "(" + item.F_UserCode + ")" + "',";
            }
            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)
        {
            return GetHighChartData(BeginTime, EndTime, Tag, "");
        }
        /// <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 = " and CallType=" + Tag; // 呼入
            var userAccountList = new BLL.T_Sys_UserAccount().GetModelList(" F_SeatFlag=1");

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

            DataTable dt = DbHelperSQL.Query("SELECT UserCode,COUNT(*) AS calloutCount FROM T_Call_CallRecords where 1=1 " + sql + "  GROUP BY UserCode").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 + "(" + item.F_UserCode + ")", 0);

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

                if (drUser != null)
                {
                    myDictionary[item.F_UserName + "(" + item.F_UserCode + ")"] += int.Parse(drUser[1].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 != null && BeginTime.Trim() != "")
            {
                //sql += " and BeginTime>='" + BeginTime + "'";
                sql += " and DATEDIFF(d,BeginTime,'" + BeginTime + "')<=0";
            }
            if (EndTime != null && EndTime.Trim() != "")
            {
                //sql += " and BeginTime<='" + EndTime + "'";
                sql += " and DATEDIFF(d,BeginTime,'" + EndTime + "')>=0";
            }
            sql += " and CallType=0  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, 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
        #endregion

        /// <summary>
        /// 坐席接听满意度统计
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetUserSatisfied(DateTime? start, DateTime? end, bool isExport = false)
        {
            StringBuilder sb1 = new StringBuilder();
            StringBuilder sb2 = new StringBuilder();
            if (start == null)
            {
                start = DateTime.Now;
                start = new DateTime(start.Value.Year, start.Value.Month, 1);
            }
            if (end == null)
            {
                end = DateTime.Now;
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
            paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
            var obj = DbHelperSQL.RunProcedure("P_UserSatisfiedReport", paras, "UserSatisfiedReport");
            if (isExport)
            {
                string[] cols = new string[] { "姓名", "接听量", "满意量", "一般量", "不满意量", "满意率" };
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcel("坐席接听满意度统计报表", obj.Tables[0], cols) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            return Success("成功", obj);
        }
    }
}