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 TotalTelController : BaseController
    {
        //话务量统计

        //获取表头
        public ActionResult GetColumnList()
        {
            String[] str = getcols();
            //String[] str = { "时间", "电话呼入次数", "电话呼出次数", "正常接听次数","未接通次数", "语音留言次数", "主动放弃次数", "黑名单拒接", "平均呼入时间（秒）" };
            //String[] str = { "时间", "电话呼入次数", "电话呼出次数", "正常接听次数", "语音留言次数", "主动放弃次数", "平均呼入时间（秒）" };
            return Success("获取话务量统计表头成功", str);
        }
        private string[] getcols()
        {
            String[] str = { "时间", "电话呼入次数",  "正常呼入接听次数", "今日电话总量", "呼入未接通次数", "呼入主动放弃次数", "平均呼入时间", "峰值排队人数", "电话呼出次数", "正常呼出接听次数", "接通次数", "呼损次数", "接通率", "呼损率" };
            return str;
        }

        //获取数据
        public ActionResult GetDataList(string stime, string etime)
        {
            DataTable dtNew = new DataTable();
            dtNew = getData(stime, etime);

            var obj = new
            {
                state = "success",
                message = "获取话务量数据成功",
                rows = dtNew,
                total = dtNew.Rows.Count
            };

            return Content(obj.ToJson());
        }

        //导出数据
        public ActionResult ExptList(string stime, string etime)
        {
            NPOIHelper npoi = new NPOIHelper();
            DataTable dt = getData(stime, etime);
            if (npoi.ExportToExcel("话务量数据", dt, getcols()) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");
            }
        }


        public ActionResult TotalTelReport( string stime, string etime ,int type = 0,int isdc=0)
        {

           

            string where  = "";

            DateTime beginTime;
            DateTime endTime;
            if (stime != null && stime != "")
                beginTime = DateTime.Parse(stime);
            else
                 beginTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-01 00:00:00"));

            if (etime != null && etime != "")
                endTime = DateTime.Parse(etime);
            else
                endTime = DateTime.Parse(DateTime.Now.ToShortDateString() + " 23:59:59");
            where += "  DATEDIFF(d,BeginTime,'" + beginTime.ToString("yyyy-MM-dd HH:mm:ss") + "')<=0";
            where += " and  DATEDIFF(d,BeginTime,'" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "')>=0";

            string sql = " select CONVERT(CHAR(10),BeginTime,120) 时间,  COUNT(1) 电话总量, " +
                   " COALESCE(SUM(case  when CallType = 0 " +
                   " and UserCode != '' and UserCode is not null  then 1 else 0 end) ,0) 转坐席总量, " +
                   "  COALESCE(SUM(case  when CallType = 0 " +
                   " and(UserCode IS NULL  or UserCode = '') then 1 else 0 end),0) 主动放弃量," +
                   "  COALESCE(SUM(case  when CallType = 1 " +
                   " and  LEN(CallNumber) != 4 then 1 else 0 end),0)  外呼总量, " +
                   "  COALESCE(SUM(case  when CallType = 1 " +
                   " and  LEN(CallNumber) = 4 then 1 else 0 end),0)  转移总量, " +
                   " COALESCE(SUM(case  when CallType = 0 " +
                   "  and  CallState = 1 then 1 else 0 end) ,0) 呼入接通量," +
                   "  COALESCE(SUM(case  when CallType = 0 " +
                   " and  CallState = 0 then 1 else 0 end) ,0) 呼入未接通量 " +
                   " from T_Call_CallRecords " +
                   " where  " + where +
                   " group by CONVERT(CHAR(10), BeginTime, 120) order by CONVERT(CHAR(10), BeginTime, 120)";
            if (type==1)
            {
                sql = " select CONVERT(CHAR(7),BeginTime,120) 时间, COUNT(1) 电话总量, " +
                   " COALESCE(SUM(case  when CallType = 0 " +
                   " and UserCode != '' and UserCode is not null  then 1 else 0 end) ,0) 转坐席总量, " +
                   "  COALESCE(SUM(case  when CallType = 0 " +
                   " and(UserCode IS NULL  or UserCode = '') then 1 else 0 end),0) 主动放弃量," +
                   "  COALESCE(SUM(case  when CallType = 1 " +
                   " and  LEN(CallNumber) != 4 then 1 else 0 end),0)  外呼总量, " +
                   "  COALESCE(SUM(case  when CallType = 1 " +
                   " and  LEN(CallNumber) = 4 then 1 else 0 end),0)  转移总量, " +
                   " COALESCE(SUM(case  when CallType = 0 " +
                   "  and  CallState = 1 then 1 else 0 end) ,0) 呼入接通量," +
                   "  COALESCE(SUM(case  when CallType = 0 " +
                   " and  CallState = 0 then 1 else 0 end) ,0) 呼入未接通量 " +
                   " from T_Call_CallRecords " +
                   " where  " + where+
                   " group by CONVERT(CHAR(7), BeginTime, 120) order by CONVERT(CHAR(7), BeginTime, 120)";
            }
            var dataTable = DbHelperSQL.Query(sql).Tables [0];
            dataTable.Columns.Add("接通率", typeof(string));
            int dhzl = 0;   int zxxzl = 0;int zdfql = 0;int whzl = 0;int zyzl = 0;int hrjtl = 0;int hrwjtl = 0;
            foreach (DataRow row in dataTable.Rows)
            {
                dhzl+= int.Parse(row["电话总量"].ToString());
                zxxzl += int.Parse(row["转坐席总量"].ToString());
                zdfql += int.Parse(row["主动放弃量"].ToString());
                whzl += int.Parse(row["外呼总量"].ToString());
                zyzl += int.Parse(row["转移总量"].ToString());
                hrjtl += int.Parse(row["呼入接通量"].ToString());
                hrwjtl += int.Parse(row["呼入未接通量"].ToString());
                if (int.Parse(row["转坐席总量"].ToString()) > 0)
                {
                    row["接通率"] = string.Format("{0:f2}%", float.Parse(row["呼入接通量"].ToString()) /
                        float.Parse(row["转坐席总量"].ToString()) * 100);
                }
                else
                {
                    row["接通率"] = "0%";
                }
            }
            DataRow drtNew = dataTable.NewRow();
            drtNew["时间"] = "合计";
            drtNew["电话总量"] = dhzl;
            drtNew["转坐席总量"] = zxxzl;
            drtNew["主动放弃量"] = zdfql;
            drtNew["外呼总量"] = whzl;
            drtNew["转移总量"] = zyzl;
            drtNew["呼入接通量"] = hrjtl;
            drtNew["呼入未接通量"] = hrwjtl;
            dataTable.Rows.Add(drtNew);
            if (zxxzl>0)
            {
                drtNew["接通率"]= string.Format("{0:f2}%", float.Parse(drtNew["呼入接通量"].ToString()) /
                        float.Parse(drtNew["转坐席总量"].ToString()) * 100);
            }
             else
            {
                drtNew["接通率"] = "0%";
            }
            if (isdc > 0)
            {
                if (isdc==2)
                {
                    NPOIHelper npoi = new NPOIHelper();
                    dataTable = dataTable.DefaultView.ToTable(false, new string[] { "时间", "转坐席总量", "呼入接通量",
                "接通率"
         });
                    string[] cols = { "时间", "呼入总量", "接听量", "接通率" };
                    if (npoi.ExportToExcel("话务报表", dataTable, cols) == "")
                    {
                        return Success("导出成功");
                    }
                    else
                    {
                        return Error("导出失败");
                    }
                }
                else
                {
                    NPOIHelper npoi = new NPOIHelper();
                    if (npoi.TotalTel("话务报表", dataTable) == "")
                    {
                        return Success("导出成功");
                    }
                    else
                    {
                        return Error("导出失败");
                    }
                }
               
            }
            else
                return Success("获取成功", dataTable);
        }



        private DataTable getData(string stime, string etime)
        {
            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 dc16 = 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"));
            DataColumn dc12 = new DataColumn("接通次数", Type.GetType("System.String"));
            DataColumn dc13 = new DataColumn("呼损次数", Type.GetType("System.String"));
            DataColumn dc14 = new DataColumn("接通率", Type.GetType("System.String"));
            DataColumn dc15 = 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(dc16);
            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);
            #endregion

            DateTime beginTime;
            DateTime endTime;
            string sql = "";

            int days = DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month);
            if (stime != null && stime != "")
                beginTime = DateTime.Parse(stime);
            else
                beginTime = DateTime.Parse(DateTime.Now.AddDays(-days).ToShortDateString());

            if (etime != null && etime != "")
                endTime = DateTime.Parse(etime );
            else
                endTime = DateTime.Parse(DateTime.Now.ToShortDateString() + " 23:59:59");


            sql += " and DATEDIFF(d,BeginTime,'" + beginTime.ToString("yyyy-MM-dd HH:mm:ss") + "')<=0";
            sql += " and DATEDIFF(d,BeginTime,'" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "')>=0";
            //  sqll += " and DATEDIFF(d,F_LeaveTime,'" + beginTime.ToString("yyyy-MM-dd HH:mm:ss") + "')<=0";
            //   sqll += " and DATEDIFF(d,F_LeaveTime,'" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "')>=0";

            var  dt = new BLL.T_Call_CallRecords().GetModelList("1=1  " + sql);


            
            int tcallincount = 0;
            int tcalloutcount = 0;
            int tcallinjtcount = 0;
            //int tlycount = 0;
            int tsrcount = 0;
            int tfqcount = 0;
            int twjtcount = 0;
            int tcallinhcjtcount = 0;
            int tcallinjtongcount = 0;
            int thscount= 0;
            int tzxdhcount = 0;
            double tthsc = 0;
            for (DateTime t = beginTime; t <= endTime; t = t.AddDays(1))
            {
                string key = t.ToString("yyyy.MM.dd");
                int callincount = dt.Where(x => x.BeginTime.Value.ToString("yyyy.MM.dd") ==key  && x.CallType == 0).Count();

                //int callincount = dt.Select("begintime >='" + t.ToString() + "' and begintime<'" + t.AddDays(1).ToString() + "' and CallType=0").Length;
                int calloutcount = dt.Where(x => x.BeginTime.Value.ToString("yyyy.MM.dd") == key && x.CallType == 1).Count();
                //int calloutcount = dt.Select("begintime >='" + t.ToString() + "' and begintime<'" + t.AddDays(1).ToString() + "' and CallType=1").Length;
                int callinjtcount = dt.Where(x => x.BeginTime.Value.ToString("yyyy.MM.dd") == key && x.CallType == 0&&
              x. CallState == 1).Count();
                // int callinjtcount = dt.Select("begintime >='" + t.ToString() + "' and begintime<'" + t.AddDays(1).ToString() + "' and CallType=0 and CallState=1 ").Length;
                int callinhcjtcount = dt.Where(x => x.BeginTime.Value.ToString("yyyy.MM.dd") == key && x.CallType == 1 &&
              x.CallState == 1).Count();
                //int callinhcjtcount = dt.Select("begintime >='" + t.ToString() + "' and begintime<'" + t.AddDays(1).ToString() + "' and CallType=1 and CallState=1 ").Length;

                int srcount = dt.Where(x => x.BeginTime.Value.ToString("yyyy.MM.dd") == key && x.DealType == 1).Count();
                // int srcount = dt.Select("begintime >='" + t.ToString() + "' and begintime<'" + t.AddDays(1).ToString() + "' and DealType=1 ").Length;
                int wjtcount = dt.Where(x => x.BeginTime.Value.ToString("yyyy.MM.dd") == key && x.CallState  == 0
                   &&x.CallType == 0 && x.UserCode != ""&& x.UserCode != null).Count();
                //  int wjtcount = dt.Select("begintime >='" + t.ToString() + "' and begintime<'" + t.AddDays(1).ToString() + "' and CallState=0 and CallType=0 and isnull(usercode,'')<>'' ").Length;

                int fqcount = dt.Where(x => x.BeginTime.Value.ToString("yyyy.MM.dd") == key && x.CallState == 0
              && (x.UserCode == "" || x.UserCode == null)).Count();
                // int fqcount = dt.Select("begintime >='" + t.ToString() + "' and begintime<'" + t.AddDays(1).ToString() + "' and CallState=0 and isnull(usercode,'')='' ").Length;

                int thsc =dt.Where(x => x.BeginTime.Value.ToString("yyyy.MM.dd") == key && x.CallType == 0
              && x.CallState==1&&x.TalkLongTime !=null ).Sum (x=>x.TalkLongTime.Value  );
              //  string thsc = gettimes(dt.Select("begintime >='" + t.ToString() + "' and begintime<'" + t.AddDays(1).ToString() + "' and CallType=0 and CallState=1 "));
                int callinjtongcount = dt.Where(x => x.BeginTime.Value.ToString("yyyy.MM.dd") == key&&
               x.CallState == 1).Count();
              int hscount= dt.Where(x => x.BeginTime.Value.ToString("yyyy.MM.dd") == key &&
              x.DealType == 5).Count();

                int zxdhcount = dt.Where(x => x.BeginTime.Value.ToString("yyyy.MM.dd") == key  
                &&  x.UserCode !=""&& x.UserCode != null).Count ();


                tcallinjtongcount = tcallinjtongcount + callinjtongcount;
                tcallincount = tcallincount + callincount;
                tcalloutcount = tcalloutcount + calloutcount;
                tcallinjtcount = tcallinjtcount + callinjtcount;
                twjtcount = twjtcount + wjtcount;
                //tlycount = tlycount + lycount;
                tsrcount = tsrcount + srcount;
                tfqcount = tfqcount + fqcount;
                tthsc = tthsc + thsc;
                thscount = thscount + hscount;
                tzxdhcount = tzxdhcount + zxdhcount;
                tcallinhcjtcount = tcallinhcjtcount + callinhcjtcount;
                DataRow drNew = dtNew.NewRow();

                drNew["时间"] = key;
                drNew["电话呼入次数"] = callincount;
                drNew["电话呼出次数"] = calloutcount;
                drNew["正常接听次数"] = callinjtcount;
                drNew["未接通次数"] = wjtcount;
                //drNew["语音留言次数"] = lycount;
                drNew["主动放弃次数"] = fqcount;
              //  drNew["电话骚扰次数"] = srcount;
                drNew["正常呼出接听次数"] = callinhcjtcount;
                drNew["接通次数"] = callinjtongcount;
                drNew["呼损次数"] = hscount;
                drNew["转坐席电话量"] = zxdhcount;
                if ((callincount + calloutcount) > 0 && thsc > 0)
                    drNew["平均呼入时间"] = DateTimeConvert.parseTimeSeconds((int)(thsc / callinjtcount), 0);
                else
                    drNew["平均呼入时间"] = "0";


                if (zxdhcount>0)
                {
                    drNew["接通率"] =  double.Parse((callinjtongcount * 100).ToString()) / zxdhcount;
                    drNew["接通率"] = double.Parse(drNew["接通率"].ToString()).ToString("F2");
                }
                else
                {
                    drNew["接通率"] = 0;
                }


                if ((tcallinjtongcount + thscount) != 0)
                {
                    drNew["呼损率"] = (tcallinjtongcount + thscount) == 0 ? 0 : double.Parse((thscount * 100).ToString()) / (tcallinjtongcount + thscount);
                    drNew["呼损率"] = double.Parse(drNew["呼损率"].ToString()).ToString("F2");
                }
                else
                {
                    drNew["呼损率"] = 0;
                }

                dtNew.Rows.Add(drNew);
            }
            DataView dv = dtNew.DefaultView;
            dv.Sort = "时间 desc";
            dtNew = dv.ToTable();


            DataRow drtNew = dtNew.NewRow();

            drtNew["时间"] = "合计";
            drtNew["电话呼入次数"] = tcallincount;
            drtNew["电话呼出次数"] = tcalloutcount;
            drtNew["正常接听次数"] = tcallinjtcount;
            drtNew["未接通次数"] = twjtcount;
            //drtNew["语音留言次数"] = tlycount;
            drtNew["主动放弃次数"] = tfqcount;
            drtNew["转坐席电话量"] = tzxdhcount;
            // drtNew["电话骚扰次数"] = tsrcount;
            drtNew["正常呼出接听次数"] = tcallinhcjtcount;
            drtNew["接通次数"] = tcallinjtongcount;
            drtNew["呼损次数"] = thscount;
            if ((tcallincount + tcalloutcount) > 0 && tthsc > 0)
                drtNew["平均呼入时间"] = DateTimeConvert.parseTimeSeconds((int)(tthsc / tcallinjtcount), 0);
            else
                drtNew["平均呼入时间"] = "0";
            if (tzxdhcount > 0)
            {
                drtNew["接通率"] = double.Parse((tcallinjtongcount * 100).ToString()) / tzxdhcount;
                drtNew["接通率"] = double.Parse(drtNew["接通率"].ToString()).ToString("F2");
            }
            else
            {
                drtNew["接通率"] = 0;
            }


            if ((tcallinjtongcount + thscount) != 0)
            {
                drtNew["呼损率"] = (tcallinjtongcount + thscount) == 0 ? 0 : double.Parse((thscount * 100).ToString()) / (tcallinjtongcount + thscount);
                drtNew["呼损率"] = double.Parse(drtNew["呼损率"].ToString()).ToString("F2");
            }
            else
            {
                drtNew["呼损率"] = 0;
            }

            dtNew.Rows.Add(drtNew);
            var dtdc = DbHelperSQL.Query("select convert(varchar(10),updatetime,120) updatetime,max(Count) sal from rep_wait_count group by convert(varchar(10),updatetime,120); ").Tables[0];
            for (int i = 0; i < dtdc.Rows.Count; i++)
            {
                var time = dtdc.Rows[i]["updatetime"];
                var sal = dtdc.Rows[i]["sal"];
                for (int j = 0; j < dtNew.Rows.Count; j++)
                {
                    var timeto = dtNew.Rows[j]["时间"].ToString().Replace(".", "-");

                    if (time.ToString() == timeto)
                    {
                        dtNew.Rows[j]["峰值排队人数"] = sal;
                    }


                }
            }
            for (int i = 0; i < dtNew.Rows.Count; i++)
            {
                if (string.IsNullOrEmpty(dtNew.Rows[i]["峰值排队人数"].ToString()))
                {
                    dtNew.Rows[i]["峰值排队人数"] = "0";

                }
            }
           
            return dtNew;
        }

        private string gettimes(DataRow[] drs)
        {
            int thsc = 0;
            for (int i = 0; i < drs.Length; i++)
            {
                if (drs[i]["TalkLongTime"].ToString() != "")
                    thsc += int.Parse(drs[i]["TalkLongTime"].ToString());
            }
            return thsc.ToString();
        }
    }
}