using CallCenter.Utility;
using CallCenterApi.DB;
using CallCenterApi.Interface.Controllers.Base;
using CallCenterApi.Interface.Models.Common;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace CallCenterApi.Interface.Controllers
{
    public class InfoController : BaseController
    {
        private BLL.T_Bus_WorkOrder workOrderBLL = new BLL.T_Bus_WorkOrder();
        private BLL.T_Call_CallRecords callBLL = new BLL.T_Call_CallRecords();
        private BLL.T_Sys_DictionaryValue dicValueBLL = new BLL.T_Sys_DictionaryValue();
        private BLL.T_Sys_Department departmentBLL = new BLL.T_Sys_Department();
        private BLL.T_Sys_Area areaBLL = new BLL.T_Sys_Area();

        #region no user
        #region  获取数据
        //来源渠道情况
        public ActionResult GetSourceInfo(string areaid)
        {
           
            var obj = new
            {
                a = GetSourcePercent(areaid ),
                b = GetAreaCount(areaid),
                c = GetAreaCount24(areaid)
            };

            return Success("加载成功", obj);
        }

        //接单部门情况
        public ActionResult GetDeptInfo(string areaid)
        {
            var obj = new
            {
                a = GetStateCountMonth(areaid),
                b = GetDeptCount(),
                c = GetDealCount24(areaid)
            };

            return Success("加载成功", obj);
        }

        //投诉举报情况
        public ActionResult GetTypeInfo(string areaid)
        {
            var obj = new
            {
                a = GetTypeCountMonth(areaid),
                b = GetTypeCount(areaid),
                c = GetKeyCount(areaid ),
                d = GetDeptCount()
            };

            return Success("加载成功", obj);
        }
        //话务数量情况
        public ActionResult GetTelInfo()
        {
            var obj = new
            {
                a = GetTelCount24(),
                b = GetUserStateCount24(),
                c = GetTelCount()
            };

            return Success("加载成功", obj);
        }
        //中心大数据
        public ActionResult GetCenterInfo(string areaid)
        {
            var obj = new
            {
                a = GetTypeCount(areaid),
                b = GetTelRate24(),
                c = GetAvgTelTime24(),
                d = GetAreaCount(areaid),
                e = GetCenterTypeCount(areaid )
            };

            return Success("加载成功", obj);
        }
        //服务知识
        #endregion

        #region 1.来源渠道情况
        //受理渠道24小时实时统计
        private object GetSourceCount(string areaid)
        {
            string strwhere = "";
            if (!string.IsNullOrEmpty(areaid))
            {
                strwhere = " and F_SourceArea="+areaid ;
            }
            string sql = " select F_Value Source,(select COUNT(1) from T_Bus_WorkOrder where datediff(day,F_CreateTime,getdate())=0 and F_IsDelete=0 "
                + " and  F_InfoSource=F_ValueId"+strwhere +") Count from  dbo.T_Sys_DictionaryValue where F_PrentId=36 and F_State=0 ";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];

            return dt;
        }
        private object GetSourcePercent(string areaid)
        {
            string strwhere = "";
            if (!string.IsNullOrEmpty(areaid))
            {
                strwhere = " and F_SourceArea=" + areaid;
            }
            int n = Int32.Parse(DbHelperSQL.GetSingle("select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0 and datediff(day,F_CreateTime,getdate())=0"+strwhere ).ToString());
            var sourceList = dicValueBLL.GetModelList(" F_PrentId=36 and F_State=0 ");

            var obj = sourceList.Select(p =>
            {
                var sql = " select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0  and datediff(day,F_CreateTime,getdate())=0"
                        + " and F_InfoSource=" + p.F_ValueId+strwhere ;
                var cl = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());
                return new
                {
                    source = p.F_Value,
                    percent = n > 0 ? Math.Round(((double)cl * 100 / n), 2) : 0
                };
            });

            return obj;
        }
        //受理区域统计
        private object GetAreaCount(string areaid)
        {
            string strwhere = "";
            string strw = "";
            if (!string.IsNullOrEmpty(areaid))
            {
                strwhere = " and F_AreaId=" + areaid;
                strw = " and F_SourceArea=" + areaid;
            }
            //string sql = " select F_AreaName AreaName,(select COUNT(1) from T_Bus_WorkOrder where datediff(day,F_CreateTime,getdate())=0 and F_IsDelete=0 "
            //    + "and F_SourceArea=F_AreaId) Count from dbo.T_Sys_Area where F_PrentId=0 and F_State=0 ";

            string sql = " select F_AreaId,F_AreaName AreaName,(select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0 and isnull(F_IsResult,0)=0"
                + "and F_SourceArea=F_AreaId) Count from dbo.T_Sys_Area where F_PrentId=0 and F_State=0 "+strwhere ;
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];

            DataRow dr = dt.NewRow();
            dr["F_AreaId"] = "0";
            dr["AreaName"] = "当即办理";

            string sqlzb = "select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0 and isnull(F_IsResult,0)=1"+strw;
            dr["Count"] = DbHelperSQL.GetSingle(sqlzb).ToString();

            dt.Rows.Add(dr);
            //dt.Columns.Add("Item", typeof(object));
            //foreach (DataRow drnew in dt.Rows)
            //{
            //    string where = " and isnull(F_IsResult,0)=1 ";
            //    string straid = drnew["F_AreaId"].ToString();
            //    if (straid != "0")
            //    {
            //        where = " and isnull(F_IsResult,0)=0 and F_SourceArea='" + straid + "' ";
            //    }
            //    string sqltype = " select F_Value TypeName,(select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0 " + where
            //    + " and F_InfoType=F_ValueId) Count from dbo.T_Sys_DictionaryValue where F_ItemId=2 and F_State=0 ";
            //    DataTable dttype = DbHelperSQL.Query(sqltype).Tables[0];

            //    drnew["Item"] = dttype;
            //}

            return dt;
        }
        //受理区域统计(按小时统计)
        private object GetAreaCount24(string areaid)
        {
            string strwhere = "";
            if (!string.IsNullOrEmpty(areaid))
            {
                strwhere = " and F_AreaId=" + areaid;
            }

            var areaList = new BLL.T_Sys_Area().GetModelList(" F_PrentId=0 and F_State=0 "+strwhere );

            int[] hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };

            var obj = new
            {
                hours = hours,
                cols = areaList.Select(p => p.F_AreaName),
                counts = new List<int[]>()
            };

            foreach (var area in areaList)
            {
                int[] count = new int[24];
                for (int i = 0; i < hours.Length; i++)
                {
                    string sql = " select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 and F_SourceArea='" + area.F_AreaId + "'"
                        + " and datediff(day, F_CreateTime, getdate()) = 0 and datepart(hh, F_CreateTime)=" + hours[i];
                    count[i] = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());
                }
                obj.counts.Add(count);
            }

            return obj;
        }

        #endregion

        #region 2.接单部门情况
        //接单情况汇总(月份)
        private object GetStateCountMonth(string areaid)
        {
            string strwhere = "";
            if (!string.IsNullOrEmpty(areaid))
            {
                strwhere = " and F_SourceArea=" + areaid;
            }
            //int[] stateList = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 };
            int[] stateList = Enumerable.Range(0, 12).ToArray<int>();
            string[] nameList = { "新工单", "待交办", "待查收", "退回审核中", "办理中", "延时审核中", "已办理", "已回访", "重办中", "已结案", "待审核", "重办待交办" };
            //string[] nameList = { "新工单", "待交办", "待查收", "退回审核中", "办理中", "延时审核中", "已办理", "已回访", "重办中", "已结案" };
            int[] ints = new int[12];
            string[] months = new string[12];
            for (int i = 12; i > 0; i--)
            {
                var date = DateTime.Now.AddMonths(1 - i);
                string mon = date.ToString("yyyyMM");
                months[12 - i] = mon;
                ints[12 - i] = date.Month;
            }

            var obj = new
            {
                months = months,
                cols = nameList,
                counts = new List<int[]>()
            };

            foreach (var state in stateList)
            {
                int[] count = new int[12];
                for (int i = 0; i < ints.Length; i++)
                {
                    string sql = " select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 and F_WorkState=" + state
                        + " and datediff(month,F_CreateTime,getdate())<=12 and datepart(month, F_CreateTime)=" + ints[i]+strwhere ;
                    count[i] = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());

                }
                obj.counts.Add(count);
            }

            return obj;
        }

        //各部门受理工单情况
        //private object GetDeptCount()
        //{
        //    var list = departmentBLL.GetModelList(" 1=1 ");
        //    var obj = new
        //    {
        //        cols = list.Select(p => p.F_DeptName),
        //        totals = new List<int[]>(),
        //        counts = new List<int[]>(),
        //        percents = new List<string[]>()
        //    };

        //    int[] total = new int[list.Count];
        //    int[] count = new int[list.Count];
        //    string[] percent = new string[list.Count];

        //    for (int i = 0; i < list.Count; i++)
        //    {
        //        string sql = " select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 "
        //                + " and F_WorkOrderID in (select F_WorkOrderID from T_Bus_AssignedInfo where F_IsSure!=3 "
        //                + " and F_State=1 and F_IsDelete = 0 and F_MainDeptId=" + list[i].F_DeptId + ")";
        //        var tl = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());
        //        sql = " select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 "
        //                + " and F_WorkOrderID in (select F_WorkOrderID from T_Bus_AssignedInfo where F_IsSure=1 "
        //                + " and F_State=1 and F_IsDelete = 0 and F_MainDeptId=" + list[i].F_DeptId + " and F_FeedbackTime is not null)";
        //        var cl = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());

        //        total[i] = tl;
        //        count[i] = cl;
        //        percent[i] = tl > 0 ? ((double)cl / tl).ToString("0.00%") : "-";
        //    }

        //    obj.totals.Add(total);
        //    obj.counts.Add(count);
        //    obj.percents.Add(percent);

        //    return obj;
        //}
        private object GetDeptPercent(string areaid)
        {
            string strwhere = "";
            if (!string.IsNullOrEmpty(areaid))
            {
                strwhere = " and F_SourceArea=" + areaid;
            }
            var list = departmentBLL.GetModelList(" F_IsDept='1' ");

            var obj = list.Select(p =>
            {
                string sql = " select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 "
                        + " and F_WorkOrderID in (select F_WorkOrderID from T_Bus_AssignedInfo where F_State=1 and F_IsDelete = 0  "
                        + " and F_IsSure !=3 and F_MainDeptId=" + p.F_DeptId + ")"+strwhere ;
                var tl = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());
                sql = " select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 "
                        + " and F_WorkOrderID in (select F_WorkOrderID from T_Bus_AssignedInfo where F_State=1 and F_IsDelete = 0 "
                        + " and F_IsSure=1 and F_MainDeptId=" + p.F_DeptId + "  and F_FeedbackTime is not null)"+strwhere ;
                var cl = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());
                return new
                {
                    deptname = p.F_DeptName,
                    percent = tl > 0 ? Math.Round(((double)cl * 100 / tl), 2) : 0
                };
            });

            return obj;
        }

        //工单处理时间分布（小时）
        private object GetDealCount24(string areaid)
        {
            string strwhere = "";
            if (!string.IsNullOrEmpty(areaid))
            {
                strwhere = " and F_SourceArea=" + areaid;
            }
            //int[] hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
            //int[] acounts = new int[24];
            //int[] fcounts = new int[24];
            int[] hours = { 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18 };
            int[] acounts = new int[12];
            int[] fcounts = new int[12];

            for (int i = 0; i < hours.Count(); i++)
            {
                string sql = " select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 "
                        + " and F_WorkOrderID in (select F_WorkOrderID from T_Bus_AssignedInfo where F_IsSure!=3 and datediff(day,F_CreateTime,getdate())=0 "
                        + " and datepart(hh, F_CreateTime)=" + hours[i] + " and F_IsDelete = 0 and F_State=1 )"+strwhere ;
                var al = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());
                sql = " select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 "
                        + " and F_WorkOrderID in (select F_WorkOrderID from T_Bus_AssignedInfo where F_IsSure=1 and datediff(day,F_FeedbackTime,getdate())=0 "
                        + " and datepart(hh, F_FeedbackTime)=" + hours[i] + " and F_IsDelete = 0 and F_State=1 )"+strwhere ;
                var fl = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());

                acounts[i] = al;
                fcounts[i] = fl;
            }

            var obj = new
            {
                hours = hours,
                acounts = acounts,
                fcounts = fcounts
            };

            return obj;
        }
        //受理单位统计
        private object GetDeptCount()
        {
            var list = departmentBLL.GetModelList(" F_IsDept='1' ");

            var obj = list.Select(p =>
            {
                string sql = " select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 "
                        + " and F_WorkOrderID in (select F_WorkOrderID from T_Bus_AssignedInfo where F_IsDelete = 0 and F_State=1  "
                        + " and F_MainDeptId=" + p.F_DeptId + " and F_IsSure!=3)";
                var tl = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());
                return new
                {
                    deptname = p.F_DeptName,
                    count = tl
                };
            });

            return obj;
        }
        #endregion

        #region 3.投诉举报情况
        //工单类型各数量
        private object GetTypeCount(string areaid)
        {
            string strwhere = "";
            if (!string.IsNullOrEmpty(areaid))
            {
                strwhere = " and F_SourceArea=" + areaid;
            }
            var typelist = dicValueBLL.GetModelList(" F_PrentId=37 ");
            int[] counts = new int[typelist.Count];

            for (int i = 0; i < typelist.Count; i++)
            {
                string sql = " select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 and F_InfoType=" + typelist[i].F_ValueId+strwhere ;
                counts[i] = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());
            }
            var obj = new
            {
                cols = typelist.Select(p => p.F_Value),
                counts = counts
            };
            return obj;
        }
        //投诉类型统计(月份)
        private object GetTypeCountMonth(string areaid)
        {
            string strwhere = "";
            if (!string.IsNullOrEmpty(areaid))
            {
                strwhere = " and F_SourceArea=" + areaid;
            }
            int[] ints = new int[12];
            string[] months = new string[12];
            for (int i = 12; i > 0; i--)
            {
                var date = DateTime.Now.AddMonths(1 - i);
                string mon = date.ToString("yyyyMM");
                months[12 - i] = mon;
                ints[12 - i] = date.Month;
            }

            var typelist = dicValueBLL.GetModelList(" F_PrentId=37 ");
            var obj = new
            {
                months = months,
                cols = typelist.Select(p => p.F_Value),
                counts = new List<int[]>(),
            };

            foreach (var l in typelist)
            {
                int[] count = new int[12];
                for (int i = 0; i < ints.Length; i++)
                {
                    string sql = " select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 and F_InfoType=" + l.F_ValueId
                        + " and datediff(month,F_CreateTime,getdate())<=12 and datepart(month, F_CreateTime)=" + ints[i]+strwhere ;
                    count[i] = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());
                }
                obj.counts.Add(count);
            }

            return obj;
        }
        //工单处理时间分布（小时）
        private object GetTypeCount24(string areaid)
        {
            string strwhere = "";
            if (!string.IsNullOrEmpty(areaid))
            {
                strwhere = " and F_SourceArea=" + areaid;
            }
            int[] hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
            var typelist = dicValueBLL.GetModelList(" F_PrentId=37 ");
            var obj = new
            {
                hours = hours,
                cols = typelist.Select(p => p.F_Value),
                counts = new List<int[]>()
            };

            foreach (var l in typelist)
            {
                int[] count = new int[24];
                for (int i = 0; i < hours.Length; i++)
                {
                    string sql = " select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 and F_InfoType=" + l.F_ValueId
                       + " and datediff(day,F_CreateTime,getdate())=0 and datepart(hh, F_CreateTime)=" + hours[i]+strwhere ;
                    count[i] = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());
                }
                obj.counts.Add(count);
            }

            return obj;
        }
        //投诉关键字统计
        private object GetKeyCount(string areaid)
        {
            string strwhere = "";
            if (!string.IsNullOrEmpty(areaid))
            {
                strwhere = " and F_SourceArea=" + areaid;
            }
            //var typelist = dicValueBLL.GetModelList(" F_ItemId=3 ");

            //int[] counts = new int[typelist.Count];

            //for (int i = 0; i < typelist.Count; i++)
            //{
            //    string sql = " select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 and ','+F_Key+',' like '%,"+ typelist[i].F_ValueId + ",%'" ;
            //    counts[i] = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());
            //}
            //var obj = new
            //{
            //    cols = typelist.Select(p => p.F_Value),
            //    counts = counts
            //};
            string sql = "select * from (select F_Value TypeName,(select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0 "
                       + "and ','+F_Key+',' like '%,'+CONVERT(varchar(100),F_ValueId)+',%'"+strwhere +") Count "
                       + "from dbo.T_Sys_DictionaryValue where F_ItemId = 3 and F_State = 0) a order by a.Count desc ";
            return DbHelperSQL.Query(sql).Tables[0];
        }
        //举报单位/个人统计
        //投诉单位统计
        //举报关键字统计
        #endregion

        #region 4.话务数量情况
        //话务量实时数据统计
        private object GetTelCount24()
        {
            int[] hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
            int[] tcounts = new int[24];//来电数量
            int[] ccounts = new int[24];//接通数量
            int[] wjtcounts = new int[24];//未接通数量
            int[] lcounts = new int[24];//留言数量
            int[] gcounts = new int[24];//放弃数量
            int[] scounts = new int[24];//骚扰数量

            for (int i = 0; i < hours.Length; i++)
            {
                string sql = " select count(1) from dbo.T_Call_CallRecords where CallType=0 and datediff(day, BeginTime, getdate()) = 0  "
                        + " and datepart(hh,BeginTime)=" + hours[i];
                tcounts[i] = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());

                sql = " select count(1) from dbo.T_Call_CallRecords where CallType=0 and datediff(day, BeginTime, getdate()) = 0  "
                        + " and CallState=1 and datepart(hh,BeginTime)=" + hours[i];
                ccounts[i] = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());

                sql = " select count(1) from dbo.T_Call_CallRecords where CallType=0 and datediff(day, BeginTime, getdate()) = 0  "
                        + " and CallState=0 and isnull(usercode,'')<>'' and datepart(hh,BeginTime)=" + hours[i];
                wjtcounts[i] = 0;

                //sql = " select count(1) from dbo.T_Call_CallRecords where CallType=0 and datediff(day, BeginTime, getdate()) = 0  "
                //        + " and DealType=4 and datepart(hh,BeginTime)=" + hours[i];
                sql = " select count(1) from dbo.T_Call_LeaveRecord where datediff(day, F_LeaveTime, getdate()) = 0  "
                        + "and datepart(hh,F_LeaveTime)=" + hours[i];
                lcounts[i] = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());

                sql = " select count(1) from dbo.T_Call_CallRecords where CallType=0 and datediff(day, BeginTime, getdate()) = 0  "
                        + " and callstate=2  and datepart(hh,BeginTime)=" + hours[i];
                scounts[i] = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());

                gcounts[i] = tcounts[i] - ccounts[i] - wjtcounts[i] - scounts[i];
                //gcounts[i] = tcounts[i] - ccounts[i] - lcounts[i] - scounts[i];

            }

            var obj = new
            {
                hours = hours,
                rcounts = tcounts,
                ccounts = ccounts,
                //lcounts = lcounts,
                wjtcounts = wjtcounts,
                gcounts = gcounts,
                scounts = scounts
            };

            return obj;
        }
        //管辖区域来话量统计
        //坐席闲忙比例
        private object GetUserStateCount24()
        {
            //DataTable dt = DbHelperSQL.Query(" select datepart(hh,OccurTime) hor,State, count(1) con from rep_agent_state where datediff(day, OccurTime, getdate()) = 0 group by datepart(hh,OccurTime),State ").Tables[0];
            //select datepart(hh,TimeStart) hor,type, count(1) con   from rep_agentState_Detail   where datediff(day, TimeStart, getdate()) = 0 and TimeEnd is null  group by datepart(hh,TimeStart),type
            //DataTable dt = DbHelperSQL.Query("select datepart(hh,TimeStart) hor,type, count(1) con   from rep_agentState_Detail   where datediff(day, TimeStart, getdate()) = 0 and TimeEnd is null  group by datepart(hh,TimeStart),type").Tables[0];
            DataTable dt = DbHelperSQL.Query("select hor,type,COUNT(1) con from (select datepart(hh, TimeStart) hor, type, agentid, count(1) con   from rep_agentState_Detail   where datediff(day, TimeStart, getdate()) = 0   group by datepart(hh, TimeStart), type, agentid ) t group by hor, type").Tables[0];
            int[] hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
            double[] kxpercents = new double[24];
            double[] thpercents = new double[24];
            double[] zmpercents = new double[24];

            for (int i = 0; i < hours.Length; i++)
            {

                var list = dt.Select(" hor=" + hours[i]);
                int con = 0;//总量
                foreach (var l in list)
                {
                    con = con + Int32.Parse(l["con"].ToString());
                }
                //var con = list.Count() > 0 ? list[0]["con"].ToString() : "0";//总量
                var kxlist = dt.Select(" hor=" + hours[i] + " and type=2 ");
                var kxcon = kxlist.Count() > 0 ? kxlist[0]["con"].ToString() : "0";//空闲
                var thlist = dt.Select(" hor=" + hours[i] + " and type=3 ");
                var thcon = thlist.Count() > 0 ? thlist[0]["con"].ToString() : "0"; ;//通话中
                var hhcllist = dt.Select(" hor=" + hours[i] + " and type=4 ");
                var hhclcon = hhcllist.Count() > 0 ? hhcllist[0]["con"].ToString() : "0"; ;//话后处理中
                var xxlist = dt.Select(" hor=" + hours[i] + " and type=5 ");
                var xxcon = xxlist.Count() > 0 ? xxlist[0]["con"].ToString() : "0"; ;//小休
                var zllist = dt.Select(" hor=" + hours[i] + " and type=6 ");
                var zlcon = zllist.Count() > 0 ? zllist[0]["con"].ToString() : "0"; ;//被请求
                if (con == 0)
                {
                    kxpercents[i] = 0;
                    thpercents[i] = 0;
                    zmpercents[i] = 0;
                }
                else
                {
                    //kxpercents[i] = (double.Parse(kxcon) / double.Parse(con)).ToString("0.00%");
                    //thpercents[i] = (double.Parse(thcon) / double.Parse(con)).ToString("0.00%");
                    //zmpercents[i] = (double.Parse(xxcon) / double.Parse(con)).ToString("0.00%");
                    kxpercents[i] = Math.Round((double.Parse(kxcon) * 100 / con), 2);
                    thpercents[i] = Math.Round((double.Parse(thcon) * 100 / con), 2);
                    zmpercents[i] = Math.Round((double.Parse(xxcon) * 100 / con), 2);
                }

            }
            var obj = new
            {
                hours = hours,
                kxpercents = kxpercents,
                thpercents = thpercents,
                zmpercents = zmpercents
            };

            return obj;
        }
        //话务量总体统计
        private object GetTelCount()
        {
            //话务量
            DataTable dt = DbHelperSQL.Query(" select count(1) con from T_Call_CallRecords where datediff(day, BeginTime, getdate()) = 0 ").Tables[0];

            var hwcon = dt.Rows[0]["con"].ToString();//话务量

            //来话量
            DataTable dt1 = DbHelperSQL.Query(" select count(1) con from T_Call_CallRecords where CallType=0 and datediff(day, BeginTime, getdate()) = 0 ").Tables[0];
            var lhcon = dt1.Rows[0]["con"].ToString();//话务量

            //接通量,通话时长
            DataTable dt2 = DbHelperSQL.Query(" select count(1) con,sum(TalkLongTime) tltimes from T_Call_CallRecords where CallState=1 and datediff(day, BeginTime, getdate()) = 0 ").Tables[0];
            var jtcon = dt2.Rows[0]["con"].ToString();//接通量
            var ths = dt2.Rows[0]["tltimes"];//通话时长
            var thtimes = "0";
            if (ths != null && ths.ToString() != "")
            {
                thtimes = ths.ToString();
            }
            string jtl = "-";//接通率
            if (hwcon != "0")
            {
                jtl = (double.Parse(jtcon) / double.Parse(hwcon)).ToString("0.00%");
            }
            double pjthtimes = 0.00;//平均通话时长
            if (jtcon != "0")
            {
                pjthtimes = Math.Round(double.Parse(thtimes) / double.Parse(jtcon));
            }

            var obj = new
            {
                hwcon = hwcon,
                lhcon = lhcon,
                jtcon = jtcon,
                pjthtimes = pjthtimes,
                jtl = jtl
            };
            return obj;
        }
        #endregion

        #region 5.中心大数据
        //工单类型各数量
        private object GetCenterTypeCount(string areaid)
        {
            string strwhere = "";
            if (!string.IsNullOrEmpty(areaid))
            {
                strwhere = " and F_SourceArea=" + areaid;
            }
            string sql = " select F_Value TypeName,(select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0 "
                + "and F_InfoType=F_ValueId"+strwhere +") Count from dbo.T_Sys_DictionaryValue where F_PrentId=37 and F_State=0 ";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            DataRow dr = dt.NewRow();
            dr["TypeName"] = "总计";

            string sqlzj = "select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0 "+strwhere ;
            dr["Count"] = DbHelperSQL.GetSingle(sqlzj).ToString();

            dt.Rows.InsertAt(dr, 0);

            DataRow dr1 = dt.NewRow();
            dr1["TypeName"] = "今日";

            string sqlday = "select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0 and datediff(day,F_CreateTime,getdate())=0 "+strwhere ;
            dr1["Count"] = DbHelperSQL.GetSingle(sqlday).ToString();

            dt.Rows.InsertAt(dr1, 1);

            return dt;
        }
        //接通率统计源
        //投诉举报关键话题排名
        //通话数量统计
        //接通率统计
        private object GetTelRate24()
        {
            int[] hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
            //string[] rates = new string[24];
            double[] rates = new double[24];

            for (int i = 0; i < hours.Length; i++)
            {
                string sql = " select count(1) from dbo.T_Call_CallRecords where CallType=0 and CallState=1 and UserCode is not null and datediff(day, BeginTime, getdate()) = 0  "
                        + " and datepart(hh,BeginTime)=" + hours[i];
                var c = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());

                sql = " select count(1) from dbo.T_Call_CallRecords where CallType=0 and UserCode is not null and datediff(day, BeginTime, getdate()) = 0  "
                        + " and datepart(hh,BeginTime)=" + hours[i];
                var t = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());

                //rates[i]= t > 0 ? ((double)c / t).ToString("0.00%") : "-";
                rates[i] = t > 0 ? Math.Round(((double)c * 100 / t), 2) : 0;
            }

            var obj = new
            {
                hours = hours,
                rates = rates
            };

            return obj;
        }
        //平均通话时长统计
        private object GetAvgTelTime24()
        {
            //接通量，通话时长
            DataTable dt = DbHelperSQL.Query(" select datepart(hh,BeginTime) hor,count(1) con,sum(TalkLongTime) tltimes from T_Call_CallRecords where CallState=1 and datediff(day, BeginTime, getdate()) = 0 group by datepart(hh,BeginTime) ").Tables[0];

            int[] hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
            double[] avgtimes = new double[24];
            for (int i = 0; i < hours.Length; i++)
            {
                var jtlist = dt.Select(" hor=" + hours[i] + " ");
                var jtcon = jtlist.Count() > 0 ? jtlist[0]["con"].ToString() : "0";
                var thtimes = jtlist.Count() > 0 ? jtlist[0]["tltimes"].ToString() : "0";

                double avgtime = 0.00;
                if (jtcon != "0")
                {
                    if (thtimes == "") { thtimes = "0"; }
                    avgtime = Math.Round(double.Parse(thtimes) / double.Parse(jtcon), 2);
                }

                avgtimes[i] = avgtime;
            }
            var obj = new
            {
                hours = hours,
                avgtimes = avgtimes
            };

            return obj;
        }
        #endregion

        #region 6.服务知识
        //工单处理时间统计和分布
        //群众咨询知识库情况统计
        //工单处理时间统计和分布
        #endregion
        #endregion

        #region 来源渠道

        /// <summary>
        /// 获取来源数量
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetSourceCountByDate(DateTime? start, DateTime? end,string areaid)
        {
            string where = " and F_IsDelete=0";
            if (start == null && end == null)
            {
                where += " and datediff(day,F_CreateTime,getdate())=0";
            }
            else
            {
                if (start == null) { start = DateTime.Now; }
                if (end == null) { end = DateTime.Now; }
                where += $" and datediff(day,F_CreateTime,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,F_CreateTime,'{end.Value.ToString("yyyy-MM-dd")}')>=0 ";
            }
            if (!string.IsNullOrEmpty(areaid))
            {
                where = " and F_SourceArea=" + areaid;
            }

            string sql = " select F_ValueId Id,F_Value Source,(select COUNT(1) from T_Bus_WorkOrder where  F_InfoSource=F_ValueId " + where
                + ") Count from  dbo.T_Sys_DictionaryValue where F_PrentId=36 and F_State=0 ";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];

            dt.Columns.Add("Item", typeof(object));
            foreach (DataRow drnew in dt.Rows)
            {
                string straid = drnew["Id"].ToString();
                string strwhere = where + " and F_InfoSource='" + straid + "' ";

                string sqltype = " select F_Value TypeName,(select COUNT(1) from T_Bus_WorkOrder where F_InfoType=F_ValueId " + strwhere
                + ") Count from dbo.T_Sys_DictionaryValue where F_PrentId=37 and F_State=0 ";
                DataTable dttype = DbHelperSQL.Query(sqltype).Tables[0];

                drnew["Item"] = dttype;
            }

            return Success("加载成功", dt);
        }

        /// <summary>
        /// 获取来源百分比
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetSourcePercentByDate(DateTime? start, DateTime? end,string areaid,int isdpjk=0)//isdpjk新版大屏使用接口，返回字段不能重复
        {
            string where = " and F_IsDelete=0";
            string strstart = "";string strend = "";
            if (start == null && end == null)
            {
                where += " and datediff(day,F_CreateTime,getdate())=0";
            }
            else
            {
                if (start == null) { start = DateTime.Now;  }
                if (end == null) { end = DateTime.Now; }
                strstart = start.Value .ToString("yyyy-MM-dd");
                strend = end.Value .ToString("yyyy-MM-dd");
                where += $" and datediff(day,F_CreateTime,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,F_CreateTime,'{end.Value.ToString("yyyy-MM-dd")}')>=0 ";
            }
            if (RedisHelper.StringGet("GetSourcePercentByDate" + strstart + strend+ areaid+ isdpjk.ToString ()) != null)
            {
                return Success("加载成功", JsonConvert.DeserializeObject(RedisHelper.StringGet("GetSourcePercentByDate" + strstart + strend + areaid + isdpjk.ToString()).ToString()));
                //  return Success ("加载成功",)
            }
            if (!string.IsNullOrEmpty(areaid))
            {
                where = " and F_SourceArea=" + areaid;
            }
            string sql = " select F_ValueId Id,F_Value Source,(select COUNT(1) from T_Bus_WorkOrder where  F_InfoSource=F_ValueId " + where
                + ") Count from  dbo.T_Sys_DictionaryValue where F_PrentId=36 and F_State=0 order by F_Value";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];

            int n = Int32.Parse(DbHelperSQL.GetSingle("select COUNT(1) from T_Bus_WorkOrder where 1=1 " + where).ToString());

            dt.Columns.Add("Percent", typeof(object));
            dt.Columns.Add("Item", typeof(object));
            foreach (DataRow drnew in dt.Rows)
            {
                if (n > 0)
                {
                    drnew["Percent"] = Math.Round((double.Parse(drnew["Count"].ToString()) * 100 / n), 2);
                }
                else
                    drnew["Percent"] = "0";
                string straid = drnew["Id"].ToString();
                string strwhere = where + " and F_InfoSource='" + straid + "' ";

                if (isdpjk == 0)
                {
                    string sqltype = " select F_Value TypeName,(select COUNT(1) from T_Bus_WorkOrder where F_InfoType=F_ValueId " + strwhere
                    + ") Count from dbo.T_Sys_DictionaryValue where F_PrentId=37 and F_State=0 order by F_Value";
                    DataTable dttype = DbHelperSQL.Query(sqltype).Tables[0];

                    drnew["Item"] = dttype;
                }
                else
                {
                    string sqltype = " select F_Value TypeName,(select COUNT(1) from T_Bus_WorkOrder where F_InfoType=F_ValueId " + strwhere
                    + ") CountT from dbo.T_Sys_DictionaryValue where F_PrentId=37 and F_State=0 order by F_Value";
                    DataTable dttype = DbHelperSQL.Query(sqltype).Tables[0];

                    drnew["Item"] = dttype;
                }
            }
            RedisHelper.StringSet("GetSourcePercentByDate" + strstart + strend + areaid + isdpjk.ToString(), dt.ToJson(), new TimeSpan(0, 10, 0));
            return Success("加载成功", dt);
        }

        /// <summary>
        /// 获取来源数量百分比
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetSourceByDate(DateTime? start, DateTime? end,string areaid)
        {
            string where = " and F_IsDelete=0";
            if (start == null && end == null)
            {
                where += " and datediff(day,F_CreateTime,getdate())=0";
            }
            else
            {
                if (start == null) { start = DateTime.Now; }
                if (end == null) { end = DateTime.Now; }
                where += $" and datediff(day,F_CreateTime,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,F_CreateTime,'{end.Value.ToString("yyyy-MM-dd")}')>=0 ";
            }
            //2019-3-27添加if判断
            if (!string.IsNullOrEmpty(areaid))
            {
                where += " and F_SourceArea="+areaid ;
            }
            string sql = " select F_ValueId Id,F_Value Source,(select COUNT(1) from T_Bus_WorkOrder where  F_InfoSource=F_ValueId " + where
                + ") Count from  dbo.T_Sys_DictionaryValue where F_PrentId=36 and F_State=0 order by F_Value";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];

            int n = Int32.Parse(DbHelperSQL.GetSingle("select COUNT(1) from T_Bus_WorkOrder where 1=1 " + where).ToString());

            dt.Columns.Add("Percent", typeof(object));
            foreach (DataRow drnew in dt.Rows)
            {
                if (n > 0)
                {
                    drnew["Percent"] = Math.Round((double.Parse(drnew["Count"].ToString()) * 100 / n), 2) + "%";
                }
                else
                {
                    drnew["Percent"] = "";
                }
            }

            DataRow dr = dt.NewRow();
            dr["Source"] = "总计";
            dr["Count"] = n;
            dr["Percent"] = "100%";
            dt.Rows.Add(dr);

            return Success("加载成功", dt);
        }
        
        /// <summary>
        /// 获取受理区域数量
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetAreaCountByDate(DateTime? start, DateTime? end, int isdpjk = 0)//isdpjk新版大屏使用接口，返回字段不能重复
        {
            string where = " and F_IsDelete=0";
            string strstart = ""; string strend = "";
            if (start == null && end == null)
            {
                where += " and datediff(day,F_CreateTime,getdate())=0";
            }
            else
            {
                if (start == null) { start = DateTime.Now;  }
                if (end == null) { end = DateTime.Now; }
                strstart = start.Value .ToString("yyyy-MM-dd");
                strend = end.Value .ToString("yyyy-MM-dd");
                where += $" and datediff(day,F_CreateTime,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,F_CreateTime,'{end.Value.ToString("yyyy-MM-dd")}')>=0 ";
            }
            if (RedisHelper.StringGet("GetAreaCountByDate" + strstart + strend + isdpjk.ToString ()) != null)
            {
                return Success("加载成功", JsonConvert.DeserializeObject(RedisHelper.StringGet("GetAreaCountByDate" + strstart + strend + isdpjk.ToString ()).ToString()));
            }

            //2019-3-27因当即办理和转办都计算为工单量将isnull(F_IsResult,0)=0去掉改为1=1
            string sql = " select F_AreaId,F_AreaName AreaName,(select COUNT(1) from T_Bus_WorkOrder where 1=1 " + where
                //+ "and F_SourceArea=F_AreaId) Count from dbo.T_Sys_Area where F_PrentId=0 and F_State=0  order by F_AreaName";//2019-3-26 去掉永城，添加条件and F_AreaName not like '%永城%'
                + "and F_SourceArea=F_AreaId) Count from dbo.T_Sys_Area where F_PrentId=0 and F_State=0 order by F_AreaName";//20200324 显示永城数据
              //+ "and F_SourceArea=F_AreaId) Count from dbo.T_Sys_Area where F_PrentId=0 and F_State=0 and F_AreaName not like '%永城%' order by F_AreaName";  //2019-3-26 去掉永城，添加条件and F_AreaName not like '%永城%'

            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            //2019-3-26 去掉当即办理
            /*DataRow dr = dt.NewRow();
            dr["F_AreaId"] = "0";
            dr["AreaName"] = "当即办理";

            string sqlzb = "select COUNT(1) from T_Bus_WorkOrder where isnull(F_IsResult,0)=1" + where;
            dr["Count"] = DbHelperSQL.GetSingle(sqlzb).ToString();
            dt.Rows.Add(dr);*/
            dt.Columns.Add("Item", typeof(object));
            foreach (DataRow drnew in dt.Rows)
            {
                string strwhere = where;
                string straid = drnew["F_AreaId"].ToString();
                if (straid != "0")
                {
                    //strwhere += " and isnull(F_IsResult,0)=0 and F_SourceArea='" + straid + "' ";//2019-3-27注释掉，改为统计当即办理和转办
                    //strwhere += " and( (isnull(F_IsResult,0)=0 and F_SourceArea='" + straid + "') or  isnull(F_IsResult,0)=1)";
                    strwhere += " and F_SourceArea='" + straid + "'";
                }
                //else
                //{
                //    strwhere += " and isnull(F_IsResult,0)=1 ";
                //}
                if (isdpjk == 0)
                {
                    string sqltype = " select F_Value TypeName,(select COUNT(1) from T_Bus_WorkOrder where F_InfoType=F_ValueId " + strwhere
                    + ") Count from dbo.T_Sys_DictionaryValue where F_PrentId=37 and F_State=0 ";
                    DataTable dttype = DbHelperSQL.Query(sqltype).Tables[0];
                    drnew["Item"] = dttype;
                }
                else
                {
                    string sqltype = " select F_Value TypeName,(select COUNT(1) from T_Bus_WorkOrder where F_InfoType=F_ValueId " + strwhere
                    + ") CountT from dbo.T_Sys_DictionaryValue where F_PrentId=37 and F_State=0 ";
                    DataTable dttype = DbHelperSQL.Query(sqltype).Tables[0];
                    drnew["Item"] = dttype;
                }

                
            }
            RedisHelper.StringSet("GetAreaCountByDate" + strstart + strend + isdpjk.ToString (), dt.ToJson(), new TimeSpan(0, 10, 0));
            return Success("加载成功", dt);
        }
        /// <summary>
        /// 获取受理区域数量
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetMultiCount()
        {
            string sql = "  and F_WorkOrderID not in (select F_WorkOrderID from T_Bus_RemindRecord where F_Type=1 and F_IsDelete=0 and  (F_Content != '推诿工单' or F_Content is null  ) and F_State!=2)";
            string lx = "select COUNT(1) from t_bus_workorder where F_InfoSource != '1' and F_InfoSource != '2501' and F_InfoSource != '2580' and F_WorkState = 1 and F_IsDelete = 0 and datediff(DAY , F_CreateTime , getdate())= 0 "+ sql;
            string ys = "select COUNT(1) from t_bus_workorder where F_InfoSource != '1' and F_InfoSource != '2501' and F_InfoSource != '2580' and F_WorkState = 5 and F_IsDelete = 0 "+ sql;
            string th = "select COUNT(1) from t_bus_workorder where F_InfoSource != '1' and F_InfoSource != '2501' and F_InfoSource != '2580' and F_WorkState = 3 and F_IsDelete = 0 "+ sql;
            string bj = "select COUNT(1) from t_bus_workorder where F_InfoSource != '1' and F_InfoSource != '2501' and F_InfoSource != '2580' and F_IsDelete = 0 and datediff(DAY , F_CloseTime  , getdate())= 0  "+ sql;
            var lxcount = DbHelperSQL.Query(lx).Tables[0];
            var yscount = DbHelperSQL.Query(ys).Tables[0];
            var thcount = DbHelperSQL.Query(th).Tables[0];
            var bjcount = DbHelperSQL.Query(bj).Tables[0];
            var obj =
                new
                {
                    lxcount,
                    yscount,
                    thcount,
                    bjcount
                };
            return Success("加载成功", obj);
        }
        /// <summary>
        /// 获取受理区域数量
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetAreaCount24ByDate(DateTime? date)
        {
            string where = " and F_IsDelete=0";
            string strstart = "";
            if (date != null)
            {
                strstart = date.Value.ToString("yyyy-MM-dd");
                where += $" and datediff(day,F_CreateTime,'{date.Value.ToString("yyyy-MM-dd")}')=0 ";
            }
            else
            {
                where += " and datediff(day,F_CreateTime,getdate())=0";
            }
            if (RedisHelper.StringGet("GetAreaCount24ByDate" + strstart ) != null)
            {
                return Success("加载成功", JsonConvert.DeserializeObject(RedisHelper.StringGet("GetAreaCount24ByDate" + strstart ).ToString()));
               
            }
            var areaList = areaBLL.GetModelList(" F_PrentId=0 and F_State=0 ");
            var typeList = dicValueBLL.GetModelList(" F_PrentId=37 and F_State=0","  F_ValueId ");

            int[] hours = Enumerable.Range(0, 24).ToArray<int>();

            string sqlah = "select F_SourceArea, datepart(hh, F_CreateTime) hours,count(1) counts from dbo.T_Bus_WorkOrder where 1=1 " + where + "  group by F_SourceArea,datepart(hh, F_CreateTime) order by F_SourceArea,datepart(hh, F_CreateTime)";
            string sqlat = "select F_SourceArea,F_InfoType,count(1) counts from dbo.T_Bus_WorkOrder where 1=1 " + where + " group by F_SourceArea,F_InfoType order by F_SourceArea,F_InfoType";
            string sqla = "select F_SourceArea,count(1) counts from dbo.T_Bus_WorkOrder where 1=1 " + where + " group by F_SourceArea order by F_SourceArea";

            var dtah = DbHelperSQL.Query(sqlah).Tables[0];
            var dtat = DbHelperSQL.Query(sqlat).Tables[0];
            var dta = DbHelperSQL.Query(sqla).Tables[0];

            var obj = new
            {
                hours = hours,
                cols = areaList.Select(p => p.F_AreaName),
                counts = new List<int[]>(),
                tcounts = new int[areaList.Count],
                items = new List<object>()
            };
            int n = 0;
            foreach (var area in areaList)
            {
                #region 24小时
                int[] ah = new int[hours.Length];
                for (int i = 0; i < hours.Length; i++)
                {
                    var drah = dtah.Select("F_SourceArea='" + area.F_AreaId + "' and hours='" + hours[i] + "'");
                    int hc = 0;
                    if (drah.Length > 0)
                        hc = (from DataRow dr in drah select dr.Field<int>("counts")).FirstOrDefault();

                    ah[i] = hc;
                }
                obj.counts.Add(ah);
                #endregion
                #region 类型
                var tcobj = new List<object>();
                foreach (var type in typeList)
                {
                    var drat = dtat.Select("F_SourceArea='" + area.F_AreaId + "' and F_InfoType='" + type.F_ValueId + "'");
                    int tc = 0;
                    if (drat.Length > 0)
                        tc = (from DataRow dr in drat select dr.Field<int>("counts")).FirstOrDefault();

                    var objt = new
                    {
                        TypeName = type.F_Value,
                        Count = tc
                    };
                    tcobj.Add(objt);
                }
                obj.items.Add(tcobj);
                #endregion
                #region 总计
                int zj = 0;
                var dra = dta.Select("F_SourceArea='" + area.F_AreaId + "'");
                if (dra.Length > 0)
                    zj = (from DataRow dr in dra select dr.Field<int>("counts")).FirstOrDefault();
                obj.tcounts[n] = zj;
                #endregion
                n++;
            }

            #region 优化前 20200415 zhengbingbing 
            //var areaList = new BLL.T_Sys_Area().GetModelList(" F_PrentId=0 and F_State=0 ");

            ////int[] hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
            //int[] hours = Enumerable.Range(0,24).ToArray<int>();

            //var obj = new
            //{
            //    hours = hours,
            //    cols = areaList.Select(p => p.F_AreaName),
            //    counts = new List<int[]>(),
            //    tcounts = new int[areaList.Count],
            //    items = new List<object>()
            //};

            //int n = 0;

            //DataTable[] dts = new DataTable[areaList .Count];
            //foreach (var area in areaList)
            //{
            //    int t = 0;
            //    string strwhere = where;
            //    strwhere += " and F_SourceArea='" + area.F_AreaId + "'";
            //    int[] count = new int[24];
            //    //for (int i = 0; i < hours.Length; i++)
            //    //{
            //    //    string sql = " select count(1) from dbo.T_Bus_WorkOrder where datepart(hh, F_CreateTime)=" + hours[i] + strwhere;
            //    //    count[i] = Int32.Parse(DbHelperSQL.GetSingle(sql).ToString());
            //    //    t = t + count[i];
            //    //}
            //    //2019-4-11修改查询语句
            //    string sqlc = " select datepart(hh, F_CreateTime),count(1) from dbo.T_Bus_WorkOrder where 1=1" + strwhere + " group by datepart(hh, F_CreateTime)";
            //    DataTable dtc = DbHelperSQL.Query(sqlc).Tables[0];
            //    dts[n] = dtc;
            //    /*for (int i = 0; i < hours.Length; i++)
            //    {
            //        for (int j = 0; j < dtc.Rows.Count; j++)
            //        {
            //            if (dtc.Rows[j][0].ToString() == hours[i].ToString())
            //            {
            //                if (!string.IsNullOrEmpty(dtc.Rows[j][1].ToString()))
            //                {
            //                    count[i] = Int32.Parse(dtc.Rows[j][1].ToString());
            //                    t = t + count[i];
            //                }

            //            }
            //        }
            //    }

            //    obj.counts.Add(count);

            //    obj.tcounts[n]= t;*/
            //    n += 1;

            //    string sqltype = " select F_Value TypeName,(select COUNT(1) from T_Bus_WorkOrder where F_InfoType=F_ValueId " + strwhere
            //    + ") Count from dbo.T_Sys_DictionaryValue where F_PrentId=37 and F_State=0 ";
            //    DataTable dttype = DbHelperSQL.Query(sqltype).Tables[0];
            //    obj.items.Add(dttype);
            //}
            //for (int m = 0; m < areaList.Count; m++)
            //{
            //    DataTable dtc = dts[m];
            //    int[] count = new int[24];
            //    int t = 0;
            //    for (int i = 0; i < hours.Length; i++)
            //    {
            //        for (int j = 0; j < dtc.Rows.Count; j++)
            //        {
            //            if (dtc.Rows[j][0].ToString() == hours[i].ToString())
            //            {
            //                if (!string.IsNullOrEmpty(dtc.Rows[j][1].ToString()))
            //                {
            //                    count[i] = Int32.Parse(dtc.Rows[j][1].ToString());
            //                    t = t + count[i];
            //                }

            //            }
            //        }
            //    }
            //    obj.counts.Add(count);

            //    obj.tcounts[m] = t;
            //}
            #endregion
            RedisHelper.StringSet("GetAreaCount24ByDate" + strstart, obj.ToJson(), new TimeSpan(0, 10, 0));
            return Success("加载成功", obj);
        }

        #region 20190704
        /// <summary>
        /// 获取来源数量
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetSourceByDateNew(DateTime? start, DateTime? end, string areaid)
        {
            string where = " and F_IsDelete=0";
            if (start == null && end == null)
            {
                where += " and datediff(day,F_CreateTime,getdate())=0";
            }
            else
            {
                if (start == null) { start = DateTime.Now; }
                if (end == null) { end = DateTime.Now; }
                where += $" and datediff(day,F_CreateTime,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,F_CreateTime,'{end.Value.ToString("yyyy-MM-dd")}')>=0 ";
            }
            //2019-3-27添加if判断
            if (!string.IsNullOrEmpty(areaid))
            {
                where += " and F_SourceArea=" + areaid;
            }

            var divList = dicValueBLL.GetModelList("F_PrentId=36 and F_State=0 "," F_Value ");
            var wolist = workOrderBLL.GetModelList(" 1=1 " + where);
            var zjcount = wolist.Count();

            var counts = new List<object>();

            foreach (var item in divList)
            {
                var rate = "0";
                var wocount = wolist.Where(w => w.F_InfoSource == item.F_ValueId).Count();
                if (zjcount > 0)
                    rate = Math.Round(decimal.Parse(wocount.ToString()) / zjcount * 100, 2).ToString() + "%";
                var objcount = new { Source = item.F_Value, Count = wocount, Rate = rate };
                counts.Add(objcount);
            }
            var objzj = new
            {
                Source = "总计",
                Count = zjcount,
                Rate = "100%"
            };
            counts.Add(objzj);

            return Success("加载成功", counts);
        }

        /// <summary>
        /// 获取受理区域数量
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetAreaCountByDateNew(DateTime? start, DateTime? end, int isdpjk = 0)//isdpjk新版大屏使用接口，返回字段不能重复
        {
            string where = " and F_IsDelete=0";
            string strstart = ""; string strend = "";
            if (start != null)
                strstart = start.Value.ToString("yyyy-MM-dd");
            else
                strstart = DateTime.Now.ToString("yyyy-MM-01");
            if (end != null)
                strend = end.Value.ToString("yyyy-MM-dd");
            else
                strend = DateTime.Now.ToString("yyyy-MM-dd");
            if (RedisHelper.StringGet("GetAreaCountByDateNew" + strstart + strend+ isdpjk.ToString ()) !=null )
            {
                return Success("加载成功", JsonConvert.DeserializeObject(RedisHelper.StringGet ("GetAreaCountByDateNew" + strstart + strend + isdpjk.ToString()).ToString ()));
              //  return Success ("加载成功",)
            }
            where += $" and datediff(day,F_CreateTime,'{strstart}')<=0 and datediff(day,F_CreateTime,'{strend}')>=0 ";
            //where += $" and F_WorkOrderId in (select F_WorkOrderId FROM T_Bus_AssignedInfo "+ $" WHERE F_IsDelete = 0 and F_IsSure = 1 and F_State = 1 and isnull(F_FeedbackTime,'')<> '')";
 // + $" and datediff(day, F_FeedbackTime, '{strstart}')<= 0 and datediff(day, F_FeedbackTime, '{strend}')>= 0 "
 // + $" and F_WorkOrderId in (select F_WorkOrderId from T_Bus_WorkOrder where F_WorkState = " + (int)EnumWorkState.finish + "))";

            string sql = " select F_AreaName AreaName "
                + ",(select COUNT(1) from T_Bus_WorkOrder where 1=1 and F_SourceArea=F_AreaId " + where + ") SLCount "
                + ",(select COUNT(1) from T_Bus_WorkOrder where 1=1 and F_WorkState="+ (int)EnumWorkState.finish + " and F_SourceArea=F_AreaId " + where + ") JACount "
                + ",(select COUNT(1) from T_Bus_WorkOrder where 1=1 and F_WorkState=" + (int)EnumWorkState.finish + " and isnull(F_IsSatisfie,'')='1' and F_SourceArea=F_AreaId " + where + " ) MYCount "
                + ",(select COUNT(1) from T_Bus_WorkOrder where 1=1 and F_WorkState=" + (int)EnumWorkState.finish + " and F_SourceArea=F_AreaId " + where + ") PJCount "
                + " from dbo.T_Sys_Area where F_PrentId=0 and F_State=0 order by F_AreaId";//20200327 显示永城数据
            //+ " from dbo.T_Sys_Area where F_PrentId=0 and F_State=0 and F_AreaName not like '%永城%' order by F_AreaId";//2019-3-26 去掉永城，添加条件and F_AreaName not like '%永城%'
            //+ " from dbo.T_Sys_Area where F_PrentId=0 and F_State=0  order by F_AreaId";//不去掉永城
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            RedisHelper.StringSet("GetAreaCountByDateNew"+ strstart+ strend + isdpjk.ToString(), dt.ToJson(), new TimeSpan(0, 10, 0));
            return Success("加载成功", dt);
        }
        #endregion
        #endregion

        #region 接单部门

        /// <summary>
        /// 接单情况汇总(月份)
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetStateCountMonthByDate(string areaid)
        {
            //2019-3-27添加if判断
            string where = "";
            if (!string.IsNullOrEmpty(areaid))
            {
                where += " and F_SourceArea=" + areaid;
            }
            if (RedisHelper.StringGet("GetStateCountMonthByDate" + areaid) != null)
            {
                return Success("加载成功", JsonConvert.DeserializeObject(RedisHelper.StringGet("GetStateCountMonthByDate" + areaid).ToString()));
            }
            //int[] stateList = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 };
            int[] stateList = Enumerable.Range(0, 12).ToArray<int>();
            string[] nameList = { "新工单", "待交办", "待查收", "退回审核中", "办理中", "延时审核中", "已办理", "已回访", "重办中", "已结案","待审核","重办待交办" };
            int[] ints = new int[12];
            string[] months = new string[12];
            for (int i = 12; i > 0; i--)
            {
                var date = DateTime.Now.AddMonths(1 - i);
                string mon = date.ToString("yyyyMM");
                months[12 - i] = mon;
                ints[12 - i] = date.Month;
            }

            var obj = new
            {
                months = months,
                cols = nameList,
                counts = new List<int[]>()
            };
            DataTable[] dts = new DataTable[12];
            int n = 0;
            foreach (var state in stateList)
            {
                int[] count = new int[12];
                //for (int i = 0; i < ints.Length; i++)
                //{
                //    string sql = " select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 and F_WorkState=" + state
                //        + " and datediff(month,F_CreateTime,getdate())<=12 and datepart(month, F_CreateTime)=" + ints[i]+where;
                //    count[i] = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());

                //}
                //2019-4-11修改
                string sql = " select datepart(month, F_CreateTime), count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 and F_WorkState=" + state
                        + " and datediff(month,F_CreateTime,getdate())<=12 group by datepart(month, F_CreateTime)";
                DataTable dtc = DbHelperSQL.Query(sql).Tables[0];
                dts[n] = dtc;
                n += 1;
                /*for (int i = 0; i < ints.Length; i++)
                {
                    for (int j = 0; j < dtc.Rows.Count; j++)
                    {
                        if (dtc.Rows[j][0].ToString() == ints[i].ToString())
                        {
                            if (!string.IsNullOrEmpty(dtc.Rows[j][1].ToString()))
                            {
                                count[i] = Int32.Parse(dtc.Rows[j][1].ToString());

                            }

                        }
                    }
                }
                obj.counts.Add(count);*/
            }
            for (int m = 0; m < 12; m++)
            {
                DataTable dtc = dts[m];
                int[] count = new int[12];
                
                for (int i = 0; i < ints.Length; i++)
                {
                    for (int j = 0; j < dtc.Rows.Count; j++)
                    {
                        if (dtc.Rows[j][0].ToString() == ints[i].ToString())
                        {
                            if (!string.IsNullOrEmpty(dtc.Rows[j][1].ToString()))
                            {
                                count[i] = Int32.Parse(dtc.Rows[j][1].ToString());
                                
                            }

                        }
                    }
                }
                obj.counts.Add(count);
                
            }

            RedisHelper.StringSet("GetStateCountMonthByDate" + areaid, obj.ToJson(), new TimeSpan(0, 10, 0));
            return Success("加载成功", obj);
        }

        /// <summary>
        /// 接单情况汇总(月份)
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetStateCountByMonth(string month,string day,string areaid)
        {
            if (string.IsNullOrEmpty(month))
            {
                month = DateTime.Now.ToString("yyyy-MM");
            }

            if (!string.IsNullOrEmpty(day))
            {
                month = month + "-" + day;
            }
            else
            {
                month = month + "-01";
            }

            //int[] stateList = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 };
            //int[] stateList = Enumerable.Range(0, 10).ToArray<int>();
            //string[] nameList = { "新工单", "待交办", "待查收", "退回审核中", "办理中", "延时审核中", "已办理", "已回访", "重办中", "已结案" };
            int[] stateList = Enumerable.Range(0, 12).ToArray<int>();
            string[] nameList = { "新工单", "待交办", "待查收", "退回审核中", "办理中", "延时审核中", "已办理", "已回访", "重办中", "已结案", "待审核", "重办待交办" };

            DataTable dt = new DataTable();
            dt.Columns.Add("State", typeof(string));
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Count", typeof(string));
            foreach (var state in stateList)
            {
                string sql = " select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 and F_WorkState=" + state
                    + " and datediff(month,F_CreateTime,getdate())=0 ";
                if (!string.IsNullOrEmpty(day))
                {
                    //month = month + "-"+day ;
                    string  checkdate = DateTime.Parse(month ).ToString ("yyyy-MM-dd");
                    sql = " select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 and F_WorkState=" + state
                    + " and datediff(day,F_CreateTime,"+checkdate+")=0 ";
                }
                if (!string.IsNullOrEmpty(areaid))
                {
                    sql += " and F_SourceArea=" + areaid;
                }
                int n = int.Parse(DbHelperSQL.GetSingle(sql).ToString());

                DataRow dr = dt.NewRow();
                dr["State"] = state;
                dr["Name"] = nameList[state];
                dr["Count"] = n;

                dt.Rows.Add(dr);
            }

            return Success("加载成功", dt);
        }

        /// <summary>
        /// 单位受理处理时间分布（小时）
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetDealCount24ByDate(DateTime? date,string areaid)
        {
            //int[] hours = { 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18 };
            int[] hours = Enumerable.Range(7, 12).ToArray<int>();
            int[] acounts = new int[12];
            int[] fcounts = new int[12];

            string where = " and F_IsDelete= 0 and F_State=1 ";
            if (date == null)
            {
                date = DateTime.Now;
            }
            if (!string.IsNullOrEmpty(areaid))
            {
                where += " and F_SourceArea=" + areaid;
            }

            string sqla = $"select datepart(hh,F_CreateTime) hours,COUNT(1) counts from ( select F_Id, F_WorkOrderID, F_CreateTime from T_Bus_AssignedInfo where F_IsSure != 3  " + where + $" and datediff(day, F_CreateTime,'{date.Value.ToString("yyyy-MM-dd")}')= 0 and F_Id in (select max(F_Id) from T_Bus_AssignedInfo where F_IsSure != 3  " + where + $" and datediff(day, F_CreateTime,'{date.Value.ToString("yyyy-MM-dd")}')= 0 group by F_WorkOrderId) ) t group by datepart(hh, F_CreateTime)";
            var aa = DbHelperSQL.Query(sqla).Tables[0];

            string sqlf = $"select datepart(hh,F_FeedbackTime) hours,COUNT(1) counts from ( select F_Id, F_WorkOrderID, F_FeedbackTime from T_Bus_AssignedInfo where F_IsSure = 1 and isnull(F_FeedbackTime,'')<> '' " + where + $" and datediff(day, F_FeedbackTime,'{date.Value.ToString("yyyy-MM-dd")}')= 0 and F_Id in (select max(F_Id) from T_Bus_AssignedInfo where F_IsSure = 1 and isnull(F_FeedbackTime,'')<> '' " + where + $" and datediff(day, F_FeedbackTime,'{date.Value.ToString("yyyy-MM-dd")}')= 0 group by F_WorkOrderId) ) t group by datepart(hh, F_FeedbackTime)";
            var ff = DbHelperSQL.Query(sqlf).Tables[0];

            for (int i = 0; i < hours.Count(); i++)
            {
                int ac = 0; int fc = 0;
                var draa = aa.Select("hours='" + hours[i] + "'");
                var drff = ff.Select("hours='" + hours[i] + "'");
                if (draa.Length > 0)
                    ac = (from DataRow dr in draa select dr.Field<int>("counts")).FirstOrDefault();
                if (drff.Length > 0)
                    fc = (from DataRow dr in drff select dr.Field<int>("counts")).FirstOrDefault();

                acounts[i] = ac;
                fcounts[i] = fc;
            }

            #region 优化前 20200414 zhengbingbing
            //for (int i = 0; i < hours.Count(); i++)
            //{
            //    string sql = " select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 "
            //            + " and F_WorkOrderID in (select F_WorkOrderID from T_Bus_AssignedInfo where F_IsSure!=3 " + where
            //            + $" and datediff(day,F_CreateTime,'{date.Value.ToString("yyyy-MM-dd")}')=0 and datepart(hh, F_CreateTime)=" + hours[i] + ")";
            //    if (!string.IsNullOrEmpty(areaid))
            //    {
            //        sql += " and F_SourceArea=" + areaid;
            //    }
            //    var al = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());
            //    sql = " select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 "
            //            + " and F_WorkOrderID in (select F_WorkOrderID from T_Bus_AssignedInfo where F_IsSure=1 and isnull(F_FeedbackTime,'')<>''" + where
            //            + $" and datediff(day,F_FeedbackTime,'{date.Value.ToString("yyyy-MM-dd")}')=0 and datepart(hh, F_FeedbackTime)=" + hours[i] + ")";
            //    if (!string.IsNullOrEmpty(areaid))
            //    {
            //        sql += " and F_SourceArea=" + areaid;
            //    }
            //    var fl = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());

            //    acounts[i] = al;
            //    fcounts[i] = fl;
            //}
            #endregion
            var obj = new
            {
                hours = hours,
                acounts = acounts,
                fcounts = fcounts
            };

            return Success("加载成功", obj);
        }

        /// <summary>
        /// 获取部门列表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetDeptList()
        {
            DataTable dt = new DataTable();
            dt = departmentBLL.GetList( "F_State=0 and F_IsDept='1'", " F_Sort").Tables[0];
            return Success("加载成功", dt);
        }

        /// <summary>
        /// 获取承办单位数量
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetDeptCountByDate(DateTime? start, DateTime? end, string areaid, int deptid=0)
        {
            string where = " and F_IsDelete=0";
            if (start == null && end == null)
            {
                where += " and datediff(day,F_CreateTime,getdate())=0";
            }
            else
            {
                if (start == null) { start = DateTime.Now; }
                if (end == null) { end = DateTime.Now; }
                where += $" and datediff(day,F_CreateTime,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,F_CreateTime,'{end.Value.ToString("yyyy-MM-dd")}')>=0 ";
            }
            string sql = " select F_DeptId Id,F_DeptName AreaName,(select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0 and F_WorkOrderID in "
                +"(select F_WorkOrderID from T_Bus_AssignedInfo where F_IsSure!=3 " + where
                + " and F_MainDeptId=F_DeptId)) Count from dbo.T_Sys_Department where F_IsDept='1' ";
            if (deptid > 0)
            {
                sql += " and F_DeptId='" + deptid + "'";
            }
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];

            dt.Columns.Add("Item", typeof(object));
            foreach (DataRow drnew in dt.Rows)
            {
                string strwhere = where;
                string straid = drnew["Id"].ToString();
                strwhere += " and F_MainDeptId='" + straid + "' ";

                string sqltype = " select F_Value TypeName,(select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0 and F_InfoType=F_ValueId and F_WorkOrderID in"
                    + "(select F_WorkOrderID from T_Bus_AssignedInfo where F_IsSure!=3 " + strwhere
                + ")) Count from dbo.T_Sys_DictionaryValue where F_PrentId=37 and F_State=0 ";
                DataTable dttype = DbHelperSQL.Query(sqltype).Tables[0];

                drnew["Item"] = dttype;
            }

            return Success("加载成功", dt);
        }

        /// <summary>
        /// 获取承办单位数量(new)
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetDeptCountByDateNew(DateTime? start, DateTime? end, int deptid = 0)
        {
            if (start == null)
            {
                start = DateTime.Now;
            }
            if (end == null)
            {
                end = DateTime.Now;
            }
            string strstart = start.Value.ToString("yyyy-MM-dd");
            string strend = end.Value.ToString("yyyy-MM-dd");
            if (RedisHelper.StringGet("GetDeptCountByDateNew" + strstart + strend + deptid.ToString ()) != null)
            {
                return Success("加载成功", JsonConvert.DeserializeObject(RedisHelper.StringGet("GetDeptCountByDateNew" + strstart + strend + deptid.ToString()).ToString() ));
            }
            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"));
            paras.Add("@deptid", deptid.ToString());
            var obj = DbHelperSQL.RunProcedure("P_Info_DeptAcceptCount", paras, "P_Info_DeptAcceptCount");
            RedisHelper.StringSet("GetDeptCountByDateNew" + strstart + strend + deptid.ToString(), obj.Tables[0].ToJson(), new TimeSpan(0, 10, 0));
            return Success("成功", obj.Tables[0]);
        }

        #endregion

        #region 投诉举报

        /// <summary>
        /// 获取类型数量
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetTypeCountByDate(DateTime? start, DateTime? end)
        {
            string where = " and F_IsDelete=0";
            string strstart = ""; string strend = "";
            if (start == null && end == null)
            {
                where += " and datediff(day,F_CreateTime,getdate())=0";
            }
            else
            {
                if (start == null) { start = DateTime.Now; }
                if (end == null) { end = DateTime.Now;}
                strstart = start.Value .ToString("yyyy-MM-dd");
                strend = end.Value .ToString("yyyy-MM-dd");
                where += $" and datediff(day,F_CreateTime,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,F_CreateTime,'{end.Value.ToString("yyyy-MM-dd")}')>=0 ";
            }
            if (RedisHelper.StringGet("GetTypeCountByDate" + strstart + strend) != null)
            {
                return Success("加载成功", JsonConvert.DeserializeObject(RedisHelper.StringGet("GetTypeCountByDate" + strstart + strend).ToString()));
                //  return Success ("",)
            }
            string sql = " select F_Value TypeName,(select COUNT(1) from T_Bus_WorkOrder WITH(NOLOCK) where F_InfoType=F_ValueId " + where
                + ") Count from dbo.T_Sys_DictionaryValue WITH(NOLOCK) where F_PrentId=37 and F_State=0  order by F_Value";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            RedisHelper.StringSet("GetTypeCountByDate" + strstart + strend, dt.ToJson(), new TimeSpan(0, 10, 0));
            return Success("加载成功", dt);
        }

        ///// <summary>
        ///// 接单情况汇总(月份)
        ///// </summary>
        ///// <param name="start"></param>
        ///// <param name="end"></param>
        ///// <returns></returns>
        //public ActionResult GetTypeCountMonthByDate()
        //{
        //    int[] ints = new int[12];
        //    string[] months = new string[12];
        //    for (int i = 12; i > 0; i--)
        //    {
        //        var date = DateTime.Now.AddMonths(1 - i);
        //        string mon = date.ToString("yyyyMM");
        //        months[12 - i] = mon;
        //        ints[12 - i] = date.Month;
        //    }

        //    var typelist = dicValueBLL.GetModelList(" F_ItemId=2 ");
        //    var obj = new
        //    {
        //        months = months,
        //        cols = typelist.Select(p => p.F_Value),
        //        counts = new List<int[]>(),
        //    };

        //    foreach (var l in typelist)
        //    {
        //        int[] count = new int[12];
        //        for (int i = 0; i < ints.Length; i++)
        //        {
        //            string sql = " select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 and F_InfoType=" + l.F_ValueId
        //                + " and datediff(month,F_CreateTime,getdate())<=12 and datepart(month, F_CreateTime)=" + ints[i];
        //            count[i] = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());
        //        }
        //        obj.counts.Add(count);
        //    }

        //    return Success("加载成功", obj);
        //}

        /// <summary>
        /// 接单情况汇总(月份)
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetTypeCountMonthByDate(string start, string end)
        {
            DateTime startdate = DateTime.Now.AddMonths(-12);
            DateTime enddate = DateTime.Now;
            string sqlwhere = "";
            if (!string.IsNullOrEmpty(start))
            {
                startdate = DateTime.Parse(start + "-01");
                sqlwhere += " and DATEDIFF(day, F_CreateTime,'"+ startdate.ToString("yyyy-MM-dd") + "')<= 0 ";
            }
            if (!string.IsNullOrEmpty(end))
            {
                enddate = DateTime.Parse(end + "-01");
                sqlwhere += " and DATEDIFF(day, F_CreateTime,'" + enddate.ToString("yyyy-MM-dd") + "')>= 0  ";
            }
            if (RedisHelper.StringGet("GetTypeCountMonthByDate" + start + end) != null)
            {
                return Success("加载成功", JsonConvert.DeserializeObject(RedisHelper.StringGet("GetTypeCountMonthByDate" + start + end).ToString()));
            }
            int cnt = (enddate.Year - startdate.Year) * 12 + enddate.Month - startdate.Month + 1;

            string[] months = new string[cnt]; DateTime[] monthss = new DateTime[cnt];

            for (int i = 0; i < cnt; i++)
            {
                var date = startdate.AddMonths(i);
                string mon = date.ToString("yyyyMM");
                months[i] = mon;
                monthss[i] = date;
            }

            var typelist = dicValueBLL.GetModelList(" F_PrentId=37"," F_Value");
            var obj = new
            {
                months = months,
                cols = typelist.Select(p => p.F_Value),
                counts = new List<int[]>(),
            };

            string sql = "select DATEPART(year,F_CreateTime) years,DATEPART(MONTH,F_CreateTime) months,F_InfoType,COUNT(1) ccount from T_Bus_WorkOrder where 1=1 and F_IsDelete=0  " + sqlwhere + " group by DATEPART(YEAR,F_CreateTime),DATEPART(MONTH, F_CreateTime) ,F_InfoType order by DATEPART(YEAR,F_CreateTime),DATEPART(MONTH, F_CreateTime) ,F_InfoType";
            DataTable dtnew = DbHelperSQL.Query(sql).Tables[0];
            foreach (var l in typelist)
            {
                int[] count = new int[cnt];
                for (int i = 0; i < months.Length; i++)
                {
                    var drInfo = dtnew.Select("years='" + monthss[i].ToString("yyyy") + "' and months='"+ monthss[i].ToString("MM") + "' and F_InfoType=" + l.F_ValueId);
                    count[i] = (from DataRow dr in drInfo select dr.Field<int>("ccount")).FirstOrDefault();
                }
                obj.counts.Add(count);
            }
            #region 优化前
            //foreach (var l in typelist)
            //{
            //    int[] count = new int[cnt];
            //    for (int i = 0; i < months.Length; i++)
            //    {
            //        string sql = " select count(1) from dbo.T_Bus_WorkOrder where F_IsDelete = 0 and F_InfoType=" + l.F_ValueId
            //            + " and datediff(month,F_CreateTime,'"+ months[i]+"01" + "')=0 ";
            //        count[i] = Int32.Parse(DbHelperSQL.GetSingle(sql).ToString());
            //    }
            //    obj.counts.Add(count);
            //}
            #endregion

            RedisHelper.StringSet("GetTypeCountMonthByDate" + start + end, obj.ToJson(), new TimeSpan(0, 10, 0));
            return Success("加载成功", obj);
        }

        /// <summary>
        /// 获取关键词数量
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetKeyCountByDate(DateTime? start, DateTime? end)
        {
            string where = " and F_IsDelete=0";
            if (start == null && end == null)
            {
                where += " and datediff(day,F_CreateTime,getdate())=0";
            }
            else
            {
                if (start == null) { start = DateTime.Now; }
                if (end == null) { end = DateTime.Now; }
                where += $" and datediff(day,F_CreateTime,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,F_CreateTime,'{end.Value.ToString("yyyy-MM-dd")}')>=0 ";
            }
            string sql = "select * from (select F_ValueId Id,F_Value TypeName,(select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0 "+ where
                       + "and ','+F_Key+',' like '%,'+CONVERT(varchar(100),F_ValueId)+',%') Count "
                       + "from dbo.T_Sys_DictionaryValue where F_ItemId = 3 and F_State = 0) a order by a.Count desc ";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];

            dt.Columns.Add("Item", typeof(object));
            foreach (DataRow drnew in dt.Rows)
            {
                string strwhere = where;
                string straid = drnew["Id"].ToString();
                strwhere += " and ','+F_Key+',' like '%," + straid + ",%' ";

                string sqltype = " select F_Value TypeName,(select COUNT(1) from T_Bus_WorkOrder where F_InfoType=F_ValueId " + strwhere
                + ") Count from dbo.T_Sys_DictionaryValue where F_PrentId=37 and F_State=0 ";
                DataTable dttype = DbHelperSQL.Query(sqltype).Tables[0];

                drnew["Item"] = dttype;
            }

            return Success("加载成功", dt);
        }

        /// <summary>
        /// 获取关键词列表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetKeyList()
        {
            var keylist = dicValueBLL.GetModelList("  F_PrentId=38 and F_State = 0 "," F_ValueId");
            return Success("加载成功", keylist);
        }
        /// <summary>
        /// 获取关键词数量(new)
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetKeyCountByDateNew(DateTime? start, DateTime? end, int keyid = 0)
        {
            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"));
            paras.Add("@keyid", keyid.ToString());
            var obj = DbHelperSQL.RunProcedure("P_Info_KeyCount", paras, "P_Info_KeyCount");

            return Success("成功", obj.Tables[0]);
        }

        #endregion

        #region 话务数量

        /// <summary>
        /// 话务量实时数据统计
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetTelCount24ByDate(DateTime? date)
        {
            if (date == null)
            {
                date = DateTime.Now;
            }

            string strdate = date.Value.ToString("yyyy-MM-dd");

            //int[] hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
            int[] hours = Enumerable.Range(0, 24).ToArray<int>();
            int[] tcounts = new int[24];//来电数量
            int[] ccounts = new int[24];//接通数量
            int[] lcounts = new int[24];//留言数量
            int[] gcounts = new int[24];//放弃数量
            int[] scounts = new int[24];//骚扰数量

            string ldsql = $"select  DATEPART(HH,BeginTime) hours,COUNT(1) ccount from T_Call_CallRecords where CallType=0 and DATEDIFF(day, BeginTime, '" + strdate + "') = 0 group by DATEPART(HH, BeginTime) order by DATEPART(HH, BeginTime)";
            string jtsql = $"select  DATEPART(HH,BeginTime) hours,COUNT(1) ccount from T_Call_CallRecords where CallType=0 and CallState=1 and DATEDIFF(day, BeginTime, '" + strdate + "') = 0 group by DATEPART(HH, BeginTime) order by DATEPART(HH, BeginTime)";
            string lysql = $"select  DATEPART(HH,BeginTime) hours,COUNT(1) ccount from T_Call_CallRecords where CallType=0 and DealType=4 and DATEDIFF(day, BeginTime, '" + strdate + "') = 0 group by DATEPART(HH, BeginTime) order by DATEPART(HH, BeginTime)";
            string srsql = $"select  DATEPART(HH,BeginTime) hours,COUNT(1) ccount from T_Call_CallRecords where CallType=0 and DealType=1 and DATEDIFF(day, BeginTime, '" + strdate + "') = 0 group by DATEPART(HH, BeginTime) order by DATEPART(HH, BeginTime)";

            DataTable dtld = DbHelperSQL.Query(ldsql).Tables[0];
            DataTable dtjt = DbHelperSQL.Query(jtsql).Tables[0];
            DataTable dtly = DbHelperSQL.Query(lysql).Tables[0];
            DataTable dtsr = DbHelperSQL.Query(srsql).Tables[0];

            for (int i = 0; i < hours.Length; i++)
            {
                var drld = dtld.Select("hours='" + hours[i] + "' ");
                tcounts[i] = (from DataRow dr in drld select dr.Field<int>("ccount")).FirstOrDefault();

                var drjt = dtjt.Select("hours='" + hours[i] + "' ");
                ccounts[i] = (from DataRow dr in drjt select dr.Field<int>("ccount")).FirstOrDefault();

                var drly = dtly.Select("hours='" + hours[i] + "' ");
                lcounts[i] = (from DataRow dr in drly select dr.Field<int>("ccount")).FirstOrDefault();

                var drsr = dtsr.Select("hours='" + hours[i] + "' ");
                scounts[i] = (from DataRow dr in drsr select dr.Field<int>("ccount")).FirstOrDefault();
                
                gcounts[i] = tcounts[i] - ccounts[i] - lcounts[i] - scounts[i];

            }
            #region 优化前
            //for (int i = 0; i < hours.Length; i++)
            //{
            //    string sql = " select count(1) from dbo.T_Call_CallRecords where CallType=0 and datediff(day, BeginTime, '" + strdate + "') = 0  "
            //            + " and datepart(hh,BeginTime)=" + hours[i];
            //    tcounts[i] = Int32.Parse(DbHelperSQL.GetSingle(sql).ToString());

            //    sql = " select count(1) from dbo.T_Call_CallRecords where CallType=0 and datediff(day, BeginTime, '" + strdate + "') = 0  "
            //            + " and CallState=1 and datepart(hh,BeginTime)=" + hours[i];
            //    ccounts[i] = Int32.Parse(DbHelperSQL.GetSingle(sql).ToString());

            //    sql = " select count(1) from dbo.T_Call_CallRecords where CallType=0 and datediff(day, BeginTime,'" + strdate + "') = 0  "
            //            + " and DealType=4 and datepart(hh,BeginTime)=" + hours[i];
            //    lcounts[i] = Int32.Parse(DbHelperSQL.GetSingle(sql).ToString());

            //    sql = " select count(1) from dbo.T_Call_CallRecords where CallType=0 and datediff(day, BeginTime, '" + strdate + "') = 0  "
            //            + " and DealType=1  and datepart(hh,BeginTime)=" + hours[i];
            //    scounts[i] = Int32.Parse(DbHelperSQL.GetSingle(sql).ToString());

            //    gcounts[i] = tcounts[i] - ccounts[i] - lcounts[i] - scounts[i];

            //}
            #endregion

            var obj = new
            {
                hours = hours,
                rcounts = tcounts,
                ccounts = ccounts,
                //lcounts = lcounts,
                gcounts = gcounts,
                scounts = scounts
            };

            return Success("加载成功", obj);
        }

        /// <summary>
        /// 获取通话数量
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetTelCountByDate(DateTime? date)
        {
            string where = " ";
            if (date == null)
            {
                date = DateTime.Now;
            }

            string strdate = date.Value.ToString("yyyy-MM-dd");
            where += " and datediff(day,BeginTime,'" + strdate + "')=0";

            //if (start == null && end == null)
            //{
            //    where += " and datediff(day,BeginTime,getdate())=0";
            //}
            //else
            //{
            //    if (start == null) { start = DateTime.Now; }
            //    if (end == null) { end = DateTime.Now; }
            //    where += $" and datediff(day,BeginTime,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,BeginTime,'{end.Value.ToString("yyyy-MM-dd")}')>=0 ";
            //}

            //话务量
            DataTable dt = DbHelperSQL.Query(" select count(1) con from T_Call_CallRecords where 1=1 " + where).Tables[0];

            var hwcon = dt.Rows[0]["con"].ToString();//话务量

            //来话量
            DataTable dt1 = DbHelperSQL.Query(" select count(1) con from T_Call_CallRecords where CallType=0 " + where).Tables[0];
            var lhcon = dt1.Rows[0]["con"].ToString();//话务量

            //接通量,通话时长
            DataTable dt2 = DbHelperSQL.Query(" select count(1) con,sum(TalkLongTime) tltimes from T_Call_CallRecords where CallState=1 " + where).Tables[0];
            var jtcon = dt2.Rows[0]["con"].ToString();//接通量
            var ths = dt2.Rows[0]["tltimes"];//通话时长
            var thtimes = "0";
            if (ths != null && ths.ToString() != "")
            {
                thtimes = ths.ToString();
            }
            //string jtl = "-";//接通率
            //if (hwcon != "0")
            //{
            //    jtl = (double.Parse(jtcon) / double.Parse(hwcon)).ToString("0.00%");
            //}
            double jtl = 0;//接通率
            if (hwcon != "0")
            {
                jtl = Math.Round((double.Parse(jtcon) * 100 / double.Parse(hwcon)), 2); 
            }
            double pjthtimes = 0.00;//平均通话时长
            if (jtcon != "0")
            {
                pjthtimes = Math.Round(double.Parse(thtimes) / double.Parse(jtcon));
            }

            var obj = new
            {
                hwcon = hwcon,
                lhcon = lhcon,
                jtcon = jtcon,
                pjthtimes = pjthtimes,
                jtl = jtl,
                thtimes
            };

            return Success("加载成功", obj);
        }

        /// <summary>
        /// 坐席闲忙比例
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetUserStateCount24ByDate(DateTime? start, DateTime? end)
        {
            string where = " 1=1 ";
            if (start == null && end == null)
            {
                where += " and datediff(day,TimeStart,getdate())=0";
            }
            else
            {
                if (start == null) { start = DateTime.Now; }
                if (end == null) { end = DateTime.Now; }
                where += $" and datediff(day,TimeStart,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,TimeStart,'{end.Value.ToString("yyyy-MM-dd")}')>=0 ";
            }

            string sql = "select hor,type,COUNT(1) con from (select datepart(hh, TimeStart) hor, type, agentid, count(1) con  "
                + " from rep_agentState_Detail   where " + where
                + "  group by datepart(hh, TimeStart), type, agentid ) t group by hor, type";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];

            //int[] hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
            int[] hours = Enumerable.Range(0,24).ToArray<int>();
            double[] kxpercents = new double[24];
            double[] thpercents = new double[24];
            double[] zmpercents = new double[24];

            for (int i = 0; i < hours.Length; i++)
            {

                var list = dt.Select(" hor=" + hours[i]);
                int con = 0;//总量
                foreach (var l in list)
                {
                    con = con + Int32.Parse(l["con"].ToString());
                }
                //var con = list.Count() > 0 ? list[0]["con"].ToString() : "0";//总量
                var kxlist = dt.Select(" hor=" + hours[i] + " and type=2 ");
                var kxcon = kxlist.Count() > 0 ? kxlist[0]["con"].ToString() : "0";//空闲
                var thlist = dt.Select(" hor=" + hours[i] + " and type=3 ");
                var thcon = thlist.Count() > 0 ? thlist[0]["con"].ToString() : "0"; ;//通话中
                var hhcllist = dt.Select(" hor=" + hours[i] + " and type=4 ");
                var hhclcon = hhcllist.Count() > 0 ? hhcllist[0]["con"].ToString() : "0"; ;//话后处理中
                var xxlist = dt.Select(" hor=" + hours[i] + " and type=5 ");
                var xxcon = xxlist.Count() > 0 ? xxlist[0]["con"].ToString() : "0"; ;//小休
                var zllist = dt.Select(" hor=" + hours[i] + " and type=6 ");
                var zlcon = zllist.Count() > 0 ? zllist[0]["con"].ToString() : "0"; ;//被请求
                if (con == 0)
                {
                    kxpercents[i] = 0;
                    thpercents[i] = 0;
                    zmpercents[i] = 0;
                }
                else
                {
                    kxpercents[i] = Math.Round((double.Parse(kxcon) * 100 / con), 2);
                    thpercents[i] = Math.Round((double.Parse(thcon) * 100 / con), 2);
                    zmpercents[i] = Math.Round((double.Parse(xxcon) * 100 / con), 2);
                }

            }
            var obj = new
            {
                hours = hours,
                kxpercents = kxpercents,
                thpercents = thpercents,
                zmpercents = zmpercents
            };
            return Success("加载成功", obj);
        }

        #region 20190702
        /// <summary>
        /// 获取关键词数量
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetKeyCountRankByDate(DateTime? start, DateTime? end, int keyid = 0)
        {
            if (start == null)
            {
                start = DateTime.Now;
            }
            if (end == null)
            {
                end = DateTime.Now;
            }
            if (RedisHelper.StringGet("GetKeyCountRankByDate" + start.Value.ToString("yyyy-MM-dd") + end.Value.ToString("yyyy-MM-dd") + keyid.ToString()) != null)
            {
                return Success("加载成功", JsonConvert.DeserializeObject(RedisHelper.StringGet("GetKeyCountRankByDate" + start.Value.ToString("yyyy-MM-dd") + end.Value.ToString("yyyy-MM-dd") + keyid.ToString ()).ToString()));
            }
            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"));
            paras.Add("@keyid", keyid.ToString());
            var obj = DbHelperSQL.RunProcedure("P_KeyCountRankReport", paras, "KeyCountRankReport");
            RedisHelper.StringSet("GetKeyCountRankByDate"  +start.Value.ToString("yyyy-MM-dd") +end.Value.ToString("yyyy-MM-dd") + keyid.ToString(), obj.Tables[0].ToJson (), new TimeSpan(0, 10, 0));
            return Success("成功", obj.Tables[0]);
            
        }
        /// <summary>
        /// 获取单位关键词数量(new)
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetDeptKeyCountByDateNew(DateTime? start, DateTime? end, int deptid = 0, int keyid = 0)
        {
            if (start == null)
            {
                start = DateTime.Now;
            }
            if (end == null)
            {
                end = DateTime.Now;
            }
            string strstart = start.Value.ToString("yyyy-MM-dd");
            string strend = end.Value.ToString("yyyy-MM-dd");
            if (RedisHelper.StringGet("GetDeptKeyCountByDateNew" + strstart + strend + deptid.ToString() + keyid.ToString()) != null)
            {
                return Success("加载成功", JsonConvert.DeserializeObject(RedisHelper.StringGet("GetDeptKeyCountByDateNew" + strstart + strend+ deptid.ToString ()+ keyid.ToString ()).ToString()));
            }
            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"));
            paras.Add("@deptid", deptid.ToString());
            paras.Add("@keyid", keyid.ToString());
            var obj = DbHelperSQL.RunProcedure("P_Info_DeptKeyCount", paras, "P_Info_DeptKeyCount");
            RedisHelper.StringSet("GetDeptKeyCountByDateNew" + strstart + strend + deptid.ToString() + keyid.ToString(), obj.Tables[0].ToJson(), new TimeSpan(0, 10, 0));
            return Success("成功", obj.Tables[0]);
        } 

        #endregion

        #endregion

        #region 中心大数据

        /// <summary>
        /// 工单类型各数量
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetCenterTypeCountByDate()
        {
            string sql = " select F_Value TypeName,(select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0 "
                + "and F_InfoType=F_ValueId) Count from dbo.T_Sys_DictionaryValue where F_PrentId=37 and F_State=0 ";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            DataRow dr = dt.NewRow();
            dr["TypeName"] = "工单总计";

            string sqlzj = "select COUNT(1) from T_Bus_WorkOrder  WITH(NOLOCK) where F_IsDelete=0 ";
            dr["Count"] = DbHelperSQL.GetSingle(sqlzj).ToString();

            dt.Rows.InsertAt(dr, 0);

            DataRow dr1 = dt.NewRow();
            dr1["TypeName"] = "今日受理量";

            string sqldayaccept = "select COUNT(1) from T_Bus_WorkOrder  WITH(NOLOCK) where F_IsDelete=0 and datediff(day,F_CreateTime,getdate())=0 ";
            dr1["Count"] = DbHelperSQL.GetSingle(sqldayaccept).ToString();

            dt.Rows.InsertAt(dr1, 1);

            DataRow dr2 = dt.NewRow();
            dr2["TypeName"] = "今日交办量";

            string sqldayassign = "select COUNT(1) from T_Bus_WorkOrder  WITH(NOLOCK) where F_IsDelete=0 and datediff(day,F_CreateTime,getdate())=0 and isnull(F_IsResult,0)=0 and F_WorkState>1 ";
            dr2["Count"] = DbHelperSQL.GetSingle(sqldayassign).ToString();

            dt.Rows.InsertAt(dr2, 2);

            return Success("加载成功", dt);
        }

        /// <summary>
        /// 工单类型各数量(new)
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetTypeCountNew(string areaid,string date)
        {
            string strwhere = "";
            if (!string.IsNullOrEmpty(areaid))
            {
                strwhere += " and F_SourceArea=" + areaid;
            }
            string mon = " and  datediff(month,F_CreateTime,getdate())=0 ";
            string day = " and  datediff(day,F_CreateTime,getdate())=0 ";
            if(!string.IsNullOrEmpty(date))
            {
                day = " and datediff(day,F_CreateTime,'" + date + "')=0";
                mon = " and  datediff(month,F_CreateTime,'" + date + "')=0 ";
            }
            if (RedisHelper.StringGet("GetTypeCountNew" + areaid + date) != null)
            {
                return Success("加载成功", JsonConvert.DeserializeObject(RedisHelper.StringGet("GetTypeCountNew" + areaid + date).ToString()));
                //  return Success ("加载成功",)
            }

            int n = Int32.Parse(DbHelperSQL.GetSingle("select COUNT(1) from T_Bus_WorkOrder WITH(NOLOCK) where F_IsDelete=0 and isnull(F_InfoType,0)!=0" + strwhere ).ToString());

            string sql = " select F_Value TypeName,(select COUNT(1) from T_Bus_WorkOrder WITH(NOLOCK) where F_IsDelete=0 "
                + "and F_InfoType=F_ValueId" + strwhere+ day + ") DayCount, (select COUNT(1) from T_Bus_WorkOrder WITH(NOLOCK) where F_IsDelete=0 "
                + "and F_InfoType=F_ValueId" + strwhere+ mon + ") MonthCount, (select COUNT(1) from T_Bus_WorkOrder WITH(NOLOCK) where F_IsDelete=0 "
                + "and F_InfoType=F_ValueId" + strwhere + ") TotalCount from dbo.T_Sys_DictionaryValue WITH(NOLOCK) where F_PrentId=37 and F_State=0 order by F_Value ";
            //string sql = " select F_Value TypeName,(select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0 and  datediff(day,F_CreateTime,getdate())=0"
            //    + "and F_InfoType=F_ValueId" + strwhere + ") DayCount, (select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0 and  datediff(month,F_CreateTime,getdate())=0"
            //    + "and F_InfoType=F_ValueId" + strwhere + ") MonthCount, (select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0 "
            //    + "and F_InfoType=F_ValueId" + strwhere + ") TotalCount from dbo.T_Sys_DictionaryValue where F_PrentId=37 and F_State=0 order by F_Value ";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            dt.Columns.Add("Percent", typeof(object));
            foreach (DataRow drnew in dt.Rows)
            {
                if (n > 0)
                {
                    drnew["Percent"] = Math.Round((double.Parse(drnew["TotalCount"].ToString()) * 100 / n), 2) + "%";
                }
                else
                {
                    drnew["Percent"] = "";
                }
            }
            RedisHelper.StringSet("GetTypeCountNew" + areaid + date, dt.ToJson(), new TimeSpan(0, 10, 0));
            return Success("加载成功", dt);
        }

        /// <summary>
        /// 接通率统计
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetTelRate24ByDate(DateTime? date)
        {
            if (date == null)
            {
                date = DateTime.Now;
            }

            string strdate = date.Value.ToString("yyyy-MM-dd");

            //int[] hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
            int[] hours = Enumerable.Range(0,24).ToArray<int>();
            //string[] rates = new string[24];
            double[] rates = new double[24];

            for (int i = 0; i < hours.Length; i++)
            {
                string sql = " select count(1) from dbo.T_Call_CallRecords  WITH(NOLOCK) where CallType=0 and CallState=1 and UserCode is not null "
                    + " and datediff(day, BeginTime, '" + strdate + "') = 0  and datepart(hh,BeginTime)=" + hours[i];
                var c = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());

                sql = " select count(1) from dbo.T_Call_CallRecords  WITH(NOLOCK) where CallType=0 and UserCode is not null "
                    + "and datediff(day, BeginTime,  '" + strdate + "') = 0 and datepart(hh,BeginTime)=" + hours[i];
                var t = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());

                //rates[i]= t > 0 ? ((double)c / t).ToString("0.00%") : "-";
                rates[i] = t > 0 ? Math.Round(((double)c * 100 / t), 2) : 0;
            }

            var obj = new
            {
                hours = hours,
                rates = rates
            };
            return Success("加载成功", obj);
        }

        /// <summary>
        /// 平均通话时长统计
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetAvgTelTime24ByDate(DateTime? date)
        {
            if (date == null)
            {
                date = DateTime.Now;
            }

            string strdate = date.Value.ToString("yyyy-MM-dd");

            //接通量，通话时长
            string sql = " select datepart(hh,BeginTime) hor,count(1) con,sum(TalkLongTime) tltimes from T_Call_CallRecords  WITH(NOLOCK) where "
                + "CallState=1 and datediff(day, BeginTime, '" + strdate + "') = 0 group by datepart(hh,BeginTime) ";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];

            //int[] hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
            int[] hours = Enumerable.Range(0, 24).ToArray<int>();
            double[] avgtimes = new double[24];
            for (int i = 0; i < hours.Length; i++)
            {
                var jtlist = dt.Select(" hor=" + hours[i] + " ");
                var jtcon = jtlist.Count() > 0 ? jtlist[0]["con"].ToString() : "0";
                var thtimes = jtlist.Count() > 0 ? jtlist[0]["tltimes"].ToString() : "0";

                double avgtime = 0.00;
                if (jtcon != "0")
                {
                    if (thtimes == "") { thtimes = "0"; }
                    avgtime = Math.Round(double.Parse(thtimes) / double.Parse(jtcon), 2);
                }

                avgtimes[i] = avgtime;
            }
            var obj = new
            {
                hours = hours,
                avgtimes = avgtimes
            };
            return Success("加载成功", obj);
        }

        #endregion

        //2019-4-2用于大屏接通率统计
        /// <summary>
        /// 接通率统计
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetTelNum24ByDate(DateTime? date)
        {
            if (date == null)
            {
                date = DateTime.Now;
            }

            string strdate = date.Value.ToString("yyyy-MM-dd");

            //int[] hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
            int[] hours = Enumerable.Range(0, 24).ToArray<int>();
            //string[] rates = new string[24];
            double[] rates = new double[24];
            int[] jt = new int[24];
            int[] zs = new int[24];

            for (int i = 0; i < hours.Length; i++)
            {
                string sql = " select count(1) from dbo.T_Call_CallRecords  WITH(NOLOCK) where CallType=0 and CallState=1 and UserCode is not null "
                    + " and datediff(day, BeginTime, '" + strdate + "') = 0  and datepart(hh,BeginTime)=" + hours[i];
                var c = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());
                jt[i] = c;
                sql = " select count(1) from dbo.T_Call_CallRecords  WITH(NOLOCK) where CallType=0 and UserCode is not null "
                    + "and datediff(day, BeginTime,  '" + strdate + "') = 0 and datepart(hh,BeginTime)=" + hours[i];
                var t = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());
                zs[i] = t;

                //rates[i]= t > 0 ? ((double)c / t).ToString("0.00%") : "-";
                //rates[i] = t > 0 ? Math.Round(((double)c * 100 / t), 2) : 0;
            }

            var obj = new
            {
                hours ,
                jt,
                zs
            };
            return Success("加载成功", obj);
        }


        /// <summary>
        /// 用于大屏计算坐席闲忙比例
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetUserStateNum24ByDate(DateTime? start, DateTime? end)
        {
            string where = " 1=1 ";
            if (start == null && end == null)
            {
                where += " and datediff(day,TimeStart,getdate())=0";
            }
            else
            {
                if (start == null) { start = DateTime.Now; }
                if (end == null) { end = DateTime.Now; }
                where += $" and datediff(day,TimeStart,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,TimeStart,'{end.Value.ToString("yyyy-MM-dd")}')>=0 ";
            }

            string sql = "select hor,type,COUNT(1) con from (select datepart(hh, TimeStart) hor, type, agentid, count(1) con  "
                + " from rep_agentState_Detail   where " + where
                + "  group by datepart(hh, TimeStart), type, agentid ) t group by hor, type";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];

            //int[] hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
            int[] hours = Enumerable.Range(0, 24).ToArray<int>();
            double[] kxpercents = new double[24];
            double[] thpercents = new double[24];
            double[] zmpercents = new double[24];
            string[] kxcon=new string[24];
            string[] thcon = new string[24];
            string[] xxcon = new string[24];
            int[] con = new int[24];

            for (int i = 0; i < hours.Length; i++)
            {

                var list = dt.Select(" hor=" + hours[i]);
                //int con = 0;//总量
                foreach (var l in list)
                {
                    con[i] = con[i] + Int32.Parse(l["con"].ToString());
                }
                //var con = list.Count() > 0 ? list[0]["con"].ToString() : "0";//总量
                var kxlist = dt.Select(" hor=" + hours[i] + " and type=2 ");
                kxcon[i] = kxlist.Count() > 0 ? kxlist[0]["con"].ToString() : "0";//空闲
                var thlist = dt.Select(" hor=" + hours[i] + " and type=3 ");
                thcon[i] = thlist.Count() > 0 ? thlist[0]["con"].ToString() : "0"; ;//通话中
                var hhcllist = dt.Select(" hor=" + hours[i] + " and type=4 ");
                var hhclcon = hhcllist.Count() > 0 ? hhcllist[0]["con"].ToString() : "0"; ;//话后处理中
                var xxlist = dt.Select(" hor=" + hours[i] + " and type=5 ");
                xxcon[i] = xxlist.Count() > 0 ? xxlist[0]["con"].ToString() : "0"; ;//小休
                var zllist = dt.Select(" hor=" + hours[i] + " and type=6 ");
                var zlcon = zllist.Count() > 0 ? zllist[0]["con"].ToString() : "0"; ;//被请求
                if (con[i] == 0)
                {
                    kxpercents[i] = 0;
                    thpercents[i] = 0;
                    zmpercents[i] = 0;
                }
                else
                {
                    kxpercents[i] = Math.Round((double.Parse(kxcon[i]) * 100 / con[i]), 2);
                    thpercents[i] = Math.Round((double.Parse(thcon[i]) * 100 / con[i]), 2);
                    zmpercents[i] = Math.Round((double.Parse(xxcon[i]) * 100 / con[i]), 2);
                }

            }
            //var obj = new
            //{
            //    hours = hours,
            //    kxpercents = kxpercents,
            //    thpercents = thpercents,
            //    zmpercents = zmpercents
            //};
            var obj = new
            {
                hours,
                kxcon ,
                thcon,
                xxcon ,
                con
            };
            return Success("加载成功", obj);
        }
        #region 20191125 zhengbingbing 增加大屏显示热点分布接口
        /// <summary>
        /// 工单热点分布
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="deptid"></param>
        /// <param name="keyid"></param>
        /// <param name="sourcearea"></param>
        /// <param name="source"></param>
        /// <returns></returns>
        public ActionResult GetHotspotMap(DateTime? start, DateTime? end, int deptid = 0, int keyid = 0, int sourcearea = 0, int source = 0)
        {
            if (start == null)
            {
                start = DateTime.Now;
                //start = new DateTime(start.Value.Year, start.Value.Month, 1);
                start = DateTime.Now.AddDays(-3);
            }
            if (end == null)
            {
                end = DateTime.Now;
            }
            var where = " and ISNUMERIC(F_Longitude)>0 and ISNUMERIC(F_Latitude)>0 ";
            if (deptid > 0)
                where += " and F_WorkOrderID in(select F_WorkOrderID from T_Bus_AssignedInfo where F_MainDeptId = '" + deptid + "' and F_IsSure in (0,1) and F_State=1 and F_IsDelete=0 )";
            if (keyid > 0)
                where += " and ','+F_Key+',' like '%," + keyid + ",%' ";
            if (sourcearea > 0)
                where += " and F_SourceArea='" + sourcearea + "' ";
            if (source > 0)
                where += " and F_InfoSource = '" + source + "' ";

            string sql = $" and datediff(day,F_CreateTime,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,F_CreateTime,'{end.Value.ToString("yyyy-MM-dd")}')>=0 " + where;

            var orderlist = workOrderBLL.GetList(" 1=1 " + sql).Tables[0];
            var list = new List<string[]>();
            foreach (DataRow dr in orderlist.Rows)
            {
                var longitude = dr["F_Longitude"].ToString();
                var latitude = dr["F_Latitude"].ToString();
                var title = dr["F_ComTitle"].ToString();
                var content = dr["F_ComContent"].ToString();
                var strobj = new string[] { longitude, latitude, title, content };
                list.Add(strobj);
            }

            return Success("", list);
        }
        #endregion
        public bool GetLarge(string message, int Seconds = 5)
        {
            bool n = true;
            if (RedisHelper.StringGet(message + "time") != null)
            {
                DateTime time = DateTime.Parse(RedisHelper.StringGet(message + "time").ToString());
                TimeSpan ts = DateTime.Now - time;
                if (ts.TotalSeconds < Seconds)
                    return false;
            }
            return n;
        }
    }
}