using CallCenter.Utility;
using CallCenterApi.DB;
using CallCenterApi.Interface.Controllers.Base;
using System;
using System.Collections;
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 YearContrastController : BaseController
    {
        //呼叫数据对比
        public ActionResult GetDataList(string beginyear, string endyear)
        {
            ArrayList arrlist = new ArrayList();

            if (beginyear != null && beginyear.Trim() != "")
            {
                var data = new
                {
                    key = beginyear + "客户呼叫数",
                    value = GetKhCall(beginyear).TrimEnd(',').Split(','),
                    type = 0
                };
                arrlist.Add(data);

                var data1 = new
                {
                    key = beginyear + "坐席接听数",
                    value = GetZxJt(beginyear).TrimEnd(',').Split(','),
                    type = 0
                };
                arrlist.Add(data1);

                var data2 = new
                {
                    key = beginyear + "接通率",
                    value = GetJTL(beginyear).TrimEnd(',').Split(','),
                    type = 0
                };
                arrlist.Add(data2);
            }
            if (endyear != null && endyear.Trim() != "")
            {
                var data = new
                {
                    key = endyear + "客户呼叫数",
                    value = GetKhCall(endyear).TrimEnd(',').Split(','),
                    type = 1
                };
                arrlist.Add(data);

                var data1 = new
                {
                    key = endyear + "坐席接听数",
                    value = GetZxJt(endyear).TrimEnd(',').Split(','),
                    type = 1
                };
                arrlist.Add(data1);

                var data2 = new
                {
                    key = endyear + "接通率",
                    value = GetJTL(endyear).TrimEnd(',').Split(','),
                    type = 1
                };
                arrlist.Add(data2);
            }

            return Success("获取呼叫数据分析数据成功", arrlist);
        }

        //导出数据
        public ActionResult ExptList(string beginyear, string endyear)
        {
            NPOIHelper npoi = new NPOIHelper();
            DataTable dt = GetData(beginyear, endyear);
            if (npoi.ExportToExcel("通话时长数据", dt) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");
            }
        }

        private DataTable GetData(string beginyear, string endyear)
        {
            DataTable dtNew = new DataTable();
            if ((beginyear != null && beginyear.Trim() != "") && (endyear != null && endyear.Trim() != ""))
            {
                #region 编辑表头
                DataColumn dc1 = new DataColumn("月份", Type.GetType("System.String"));
                DataColumn dc2 = new DataColumn(beginyear + "年客户呼叫数", Type.GetType("System.String"));
                DataColumn dc3 = new DataColumn(beginyear + "年坐席接听数", Type.GetType("System.String"));
                DataColumn dc4 = new DataColumn(beginyear + "年坐席接听率", Type.GetType("System.String"));
                DataColumn dc5 = new DataColumn(endyear + "年客户呼叫数", Type.GetType("System.String"));
                DataColumn dc6 = new DataColumn(endyear + "年坐席接听数", Type.GetType("System.String"));
                DataColumn dc7 = new DataColumn(endyear + "年坐席接听率", 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);
                #endregion
                string[] bkhcall = GetKhCall(beginyear).Split(',');
                string[] bzxjt = GetZxJt(beginyear).Split(',');
                string[] bjtl = GetJTL(beginyear).Split(',');

                string[] ekhcall = GetKhCall(endyear).Split(',');
                string[] ezxjt = GetZxJt(endyear).Split(',');
                string[] ejtl = GetJTL(endyear).Split(',');

                for (int i = 0; i < 12; i++)
                {
                    DataRow drNew = dtNew.NewRow();
                    drNew["月份"] = (i + 1).ToString() + "月";
                    drNew[beginyear + "年客户呼叫数"] = bkhcall[i];
                    drNew[beginyear + "年坐席接听数"] = bzxjt[i];
                    drNew[beginyear + "年坐席接听率"] = bjtl[i];
                    drNew[endyear + "年客户呼叫数"] = ekhcall[i];
                    drNew[endyear + "年坐席接听数"] = ezxjt[i];
                    drNew[endyear + "年坐席接听率"] = ejtl[i];
                    dtNew.Rows.Add(drNew);
                }
            }

            return dtNew;
        }
        //客户呼叫数
        private string GetKhCall(string strtime)
        {
            DataTable dt = new DataTable();
            string str = "";
            string sql = " and YEAR(beginTime) = " + strtime;
            DataTable dtConnect = DbHelperSQL.Query("select MONTH(BeginTime) yearmonths,COUNT(1) ccount FROM T_Call_CallRecords where 1=1 and CallType=0 " + sql + " group by MONTH(BeginTime) order by MONTH(BeginTime)").Tables[0];

            for (int i = 1; i <= 12; i++)
            {
                var drConnect_month = dtConnect.Select("yearmonths='" + i + "'");
                if (drConnect_month.Length > 0)
                {
                    int drConnectCount_month = (from DataRow dr in drConnect_month select dr.Field<int>("ccount")).FirstOrDefault();
                    str += drConnectCount_month.ToString() + ",";
                }
                else
                    str += "0,";
            }
            return str.Trim(',');
        }

        //坐席接听数
        private string GetZxJt(string strtime)
        {
            DataTable dt = new DataTable();
            string str = "";
            string sql = " and YEAR(beginTime) = " + strtime;
            DataTable dtConnect = DbHelperSQL.Query("select MONTH(BeginTime) yearmonths,COUNT(1) ccount FROM T_Call_CallRecords where 1=1 and CallType=0 and CallState=1 " + sql + " group by MONTH(BeginTime) order by MONTH(BeginTime)").Tables[0];
            for (int i = 1; i <= 12; i++)
            {
                var drConnect_month = dtConnect.Select("yearmonths='" + i + "'");
                if (drConnect_month.Length > 0)
                {
                    int drConnectCount_month = (from DataRow dr in drConnect_month select dr.Field<int>("ccount")).FirstOrDefault();
                    str += drConnectCount_month.ToString() + ",";
                }
                else
                    str += "0,";
            }
            return str.Trim(',');
        }

        //坐席接听率
        private string GetJTL(string strtime)
        {
            DataTable dt = new DataTable();
            string str = "";
            string sql = " and UserCode is not null and YEAR(beginTime) = " + strtime;
            DataTable dtConnect = DbHelperSQL.Query("select MONTH(BeginTime) yearmonths,COUNT(1) ccount FROM T_Call_CallRecords where 1=1 and CallType=0 " + sql + " group by MONTH(BeginTime) order by MONTH(BeginTime)").Tables[0];
            DataTable dtJT = DbHelperSQL.Query("select MONTH(BeginTime) yearmonths,COUNT(1) ccount FROM T_Call_CallRecords where 1=1 and CallType=0 and CallState=1 " + sql + " group by MONTH(BeginTime) order by MONTH(BeginTime)").Tables[0];
            for (int i = 1; i <= 12; i++)
            {
                int countjt = 0;
                int count = 0;
                var drConnect_month = dtConnect.Select("yearmonths='" + i + "'");
                if (drConnect_month.Length > 0)
                {
                    count = (from DataRow dr in drConnect_month select dr.Field<int>("ccount")).FirstOrDefault();
                }
                var drjt_month = dtJT.Select("yearmonths='" + i + "'");
                if (drjt_month.Length > 0)
                {
                    countjt = (from DataRow dr in drjt_month select dr.Field<int>("ccount")).FirstOrDefault();
                }

                double ibgnjtl = 0.00;

                if (count > 0)
                {
                    ibgnjtl = countjt / double.Parse(count.ToString());
                    str += (ibgnjtl * 100).ToString("0.00") + ",";
                }
                else
                {
                    str += "0.00,";
                }
            }
            return str.Trim(',');
        }
        #region 20191112 zhengbingbing 优化前
        ////客户呼叫数
        //private string GetKhCall(string strtime)
        //{
        //    DataTable dt = new DataTable();
        //    string str = "";
        //    for (int i = 1; i <= 12; i++)
        //    {
        //        string sqltimeCallRecords = "";
        //        string strmm = i.ToString();
        //        if (i <= 9)
        //        {
        //            strmm = "0" + i;
        //        }
        //        if (strtime != "")
        //        {
        //            //sqltimeCallRecords += " and CONVERT(varchar , timecallin, 120)>=CONVERT(varchar , '" + strtime + "-" + strmm + "-01 00:00:01', 120) ";
        //            //sqltimeCallRecords += " and CONVERT(varchar , timecallin, 120)<=CONVERT(varchar , '" + strtime + "-" + strmm + "-31 23:59:59', 120) ";
        //            sqltimeCallRecords += " and CONVERT(varchar , begintime, 120)>=CONVERT(varchar , '" + strtime + "-" + strmm + "-01 00:00:01', 120) ";
        //            sqltimeCallRecords += " and CONVERT(varchar , begintime, 120)<=CONVERT(varchar , '" + strtime + "-" + strmm + "-31 23:59:59', 120) ";
        //        }

        //        //dt = DbHelperSQL.Query("select COUNT(*) as ct from rep_trunk_call_in  where 1=1 " + sqltimeCallRecords).Tables[0];
        //        dt = DbHelperSQL.Query("select COUNT(*) as ct from T_Call_CallRecords  where CallType=0 " + sqltimeCallRecords).Tables[0];
        //        if (dt != null && dt.Rows.Count > 0)
        //        {
        //            if (dt.Rows[0]["ct"].ToString() != "")
        //            {
        //                str += dt.Rows[0]["ct"].ToString() + ",";
        //            }
        //            else
        //            {
        //                str += "0,";
        //            }
        //        }
        //        else
        //        {
        //            str += "0,";
        //        }
        //    }
        //    if (str != "")
        //    {
        //        str.Remove(str.Length - 1);
        //    }
        //    return str;
        //}

        ////坐席接听数
        //private string GetZxJt(string strtime)
        //{
        //    DataTable dt = new DataTable();
        //    string str = "";
        //    for (int i = 1; i <= 12; i++)
        //    {
        //        string sqltimeCallRecords = "";
        //        string strmm = i.ToString();
        //        if (i <= 9)
        //        {
        //            strmm = "0" + i;
        //        }
        //        if (strtime != "")
        //        {
        //            //sqltimeCallRecords += " and CONVERT(varchar , timecallin, 120)>=CONVERT(varchar , '" + strtime + "-" + strmm + "-01 00:00:01', 120) ";
        //            //sqltimeCallRecords += " and CONVERT(varchar , timecallin, 120)<=CONVERT(varchar , '" + strtime + "-" + strmm + "-31 23:59:59', 120) ";
        //            sqltimeCallRecords += " and CONVERT(varchar , begintime, 120)>=CONVERT(varchar , '" + strtime + "-" + strmm + "-01 00:00:01', 120) ";
        //            sqltimeCallRecords += " and CONVERT(varchar , begintime, 120)<=CONVERT(varchar , '" + strtime + "-" + strmm + "-31 23:59:59', 120) ";
        //        }

        //        //dt = DbHelperSQL.Query("select COUNT(*) as ct from rep_trunk_call_in where PeriodTalking>0 " + sqltimeCallRecords).Tables[0];
        //        dt = DbHelperSQL.Query("select COUNT(*) as ct from T_Call_CallRecords  where CallType=0 and CallState=1 " + sqltimeCallRecords).Tables[0];
        //        if (dt != null && dt.Rows.Count > 0)
        //        {
        //            if (dt.Rows[0]["ct"].ToString() != "")
        //            {
        //                str += dt.Rows[0]["ct"].ToString() + ",";
        //            }
        //            else
        //            {
        //                str += "0,";
        //            }
        //        }
        //        else
        //        {
        //            str += "0,";
        //        }
        //    }
        //    if (str != "")
        //    {
        //        str.Remove(str.Length - 1);
        //    }
        //    return str;
        //}

        ////坐席接听率
        //private string GetJTL(string strtime)
        //{
        //    DataTable dt = new DataTable();
        //    string str = "";
        //    for (int i = 1; i <= 12; i++)
        //    {
        //        string sqltimeCallRecords = "";
        //        string strmm = i.ToString();
        //        if (i <= 9)
        //        {
        //            strmm = "0" + i;
        //        }
        //        if (strtime != "")
        //        {
        //            //sqltimeCallRecords += " and CONVERT(varchar , timecallin, 120)>=CONVERT(varchar , '" + strtime + "-" + strmm + "-01 00:00:01', 120) ";
        //            //sqltimeCallRecords += " and CONVERT(varchar , timecallin, 120)<=CONVERT(varchar , '" + strtime + "-" + strmm + "-31 23:59:59', 120) ";
        //            sqltimeCallRecords += " and CONVERT(varchar , begintime, 120)>=CONVERT(varchar , '" + strtime + "-" + strmm + "-01 00:00:01', 120) ";
        //            sqltimeCallRecords += " and CONVERT(varchar , begintime, 120)<=CONVERT(varchar , '" + strtime + "-" + strmm + "-31 23:59:59', 120) ";
        //        }

        //        //dt = DbHelperSQL.Query("select COUNT(*) as ct,(select COUNT(*) from rep_trunk_call_in where PeriodTalking>0 " + sqltimeCallRecords + ") as jt from rep_trunk_call_in  where 1=1  " + sqltimeCallRecords).Tables[0];
        //        dt = DbHelperSQL.Query("select COUNT(*) as ct,(select COUNT(*) from T_Call_CallRecords where CallType = 0 and CallState = 1 and UserCode is not null " + sqltimeCallRecords + ") as jt from T_Call_CallRecords where CallType = 0 and UserCode is not null  " + sqltimeCallRecords).Tables[0];

        //        int ibgnjt = 0;
        //        double ibgnjtl = 0.00;

        //        if (dt != null && dt.Rows.Count > 0)
        //        {

        //            if (dt.Rows[0]["jt"].ToString() != "")
        //            {
        //                ibgnjt = int.Parse(dt.Rows[0]["jt"].ToString());
        //            }

        //            if (dt.Rows[0]["ct"].ToString() != "" && dt.Rows[0]["ct"].ToString() != "0")
        //            {
        //                ibgnjtl = ibgnjt / Convert.ToDouble(dt.Rows[0]["ct"].ToString());
        //                str += (ibgnjtl * 100).ToString("0.00") + ",";
        //            }
        //            else
        //            {
        //                str += "0.00,";
        //            }
        //        }
        //        else
        //        {
        //            str += "0.00,";
        //        }
        //    }
        //    if (str != "")
        //    {
        //        str.Remove(str.Length - 1);
        //    }
        //    return str;
        //}
        #endregion
    }
}