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

namespace CallCenterApi.Interface.Controllers.WebChart
{
    public class InfoNewController : BaseController
    {
        // GET: InfoNew
        public ActionResult Index()
        {
            return Content("InfoNew");
        }
        public ActionResult WorkOrdrList()
        {
            DataTable dt = new DataTable();
            string sql = " and a.F_IsDelete=0 ";
            int source = RequestString.GetInt("source", 0);
            int hour = RequestString.GetInt("hour", -1);
            int type = RequestString.GetInt("type", 0);
            int dealtype = RequestString.GetInt("dealtype", -1);
            int timetype = RequestString.GetInt("timetype", 1);
            int sourceArea = RequestString.GetInt("sourceArea", 0);
            int keyid = RequestString.GetInt("keyid", 0);
            int deptid = RequestString.GetInt("deptid", 0);
            int key = RequestString.GetInt("key", 0);
            int dptype = RequestString.GetInt("dptype", 0);
            int kptype = RequestString.GetInt("kptype", 0);
           
            int handling = RequestString.GetInt("handling", 0);
            Dictionary<int, int> DWDeptid = new Dictionary<int, int>();
            DWDeptid.Add(220, 1267);
            DWDeptid.Add(219, 1266);
            DWDeptid.Add(197, 1268);
            DWDeptid.Add(204, 1271);
            DWDeptid.Add(199, 1264);
            DWDeptid.Add(218, 1265);
            DWDeptid.Add(202, 1269);
            DWDeptid.Add(203, 1270);

            string  kpcontent = RequestString.GetQueryString("kpcontent");
            string latitude = RequestString.GetQueryString("latitude");
            string longitude = RequestString.GetQueryString("longitude");
            string strpageindex = RequestString.GetQueryString("page");
            string PhoneType = RequestString.GetQueryString("PhoneType");
            string keyword = RequestString.GetQueryString("keyword");
            int Satisfie = RequestString.GetInt("Satisfie", 0);
            int pageindex = 1;
            string strpagesize = RequestString.GetQueryString("pagesize");
            int pagesize = 10;
            if (strpageindex.Trim() != "")
            {
                pageindex = Convert.ToInt32(strpageindex);
            }
            if (dealtype > -1)
            {
                if (dealtype == 2)
                {//按时
                    sql += " and F_IsResult=0 and isnull(F_DealTime,'')<>'' and F_LimitTime>= a.F_DealTime ";
                }
                else if (dealtype == 3)
                {//超期
                    sql += " and isnull(a.F_DealTime,'')<>'' and a.F_LimitTime<a.F_DealTime ";
                }
                else
                    sql += " and isnull(a.F_IsResult,0) = '" + dealtype + "' ";
            }
            if (strpagesize.Trim() != "")
            {
                pagesize = Convert.ToInt32(strpagesize);
            }
            if (keyword.Trim ()!="")
            {
                sql += " and (charindex('"+ keyword + "', F_ComContent) > 0 or charindex('" + keyword + "', F_ComTitle )> 0 or charindex('" + keyword + "' , F_Content)> 0)";

            }
            string value = "";
            if (key >0)
            {
                sql += "and a.F_Key in(select F_ValueId  from  dbo.GetValueId(" + key + ")) ";
               
            }
            if (handling>0)
            {
                if (handling==1)
                {
                    value = "inner join (select distinct F_WorkOrderId  from T_Bus_RemindRecord  WITH(NOLOCK)   where datediff(MONTH, T_Bus_RemindRecord.F_CreateTime , getdate())= 0 and F_Type = 1 ) b on a.F_WorkOrderId = b.F_WorkOrderId ";
                }
                else if (handling == 2)
                {
                    sql += "and a.F_IsResult=1";
                }
                else if (handling == 3)
                {
                    sql += "and a.F_IsResult !=1";
                }
                else if (handling == 4)
                {
                    value = "inner join (select distinct F_Name from T_SMS_RecvSMS  WITH(NOLOCK)  where datediff(MONTH,RecvTime, getdate())= 0   and Content like '%收到催办,请及时处理%') b on a.F_WorkOrderId = b.F_Name ";
                }

            }
            if (Satisfie==1)
            {
                sql += "a.F_WorkOrderId in (select  b.F_WorkOrderID from T_Bus_VisitResult b  WITH(NOLOCK) inner join T_Bus_VisitResult c on c.F_Id = b.F_Id and c .F_Id = (select max(t1.F_Id) from T_Bus_VisitResult t1 where t1.F_WorkOrderId = b.F_WorkOrderId)  where b .F_IsSatisfie = 0 and datediff(MONTH ,b.F_CreateTime ,getdate())=0)";
            }
            else if (Satisfie == 2)
            {
                sql += "a.F_WorkOrderId in (select  b.F_WorkOrderID from T_Bus_VisitResult b  WITH(NOLOCK) inner join T_Bus_VisitResult a on a.F_Id = b.F_Id and a .F_Id = (select max(t1.F_Id) from T_Bus_VisitResult t1 where t1.F_WorkOrderId = b.F_WorkOrderId)  where b .F_IsSatisfie = 0 and datediff(MONTH ,b.F_CreateTime ,getdate())=1)";
            }
            if (deptid > 0)
            {
                if (DWDeptid.ContainsKey(deptid))
                {
                    sql += "and a.F_MainDeptId in(" + DWDeptid[deptid] + "," + deptid + ")";
                }
                else 
                sql += "and a.F_MainDeptId='" + deptid + "'";
            }
            if (latitude.Trim ()!="")
            {
                sql += "and a.F_Latitude='" + latitude + "'";
            }
            if (longitude .Trim() != "")
            {
                sql += "and a.F_Longitude='" + longitude + "'";
            }
            if (PhoneType.Trim() != "")
            {
                sql += "and a.F_PhoneType='" + PhoneType + "'";
            }
            
           if (sourceArea >0)
            {
                sql += "and a.F_SourceArea='" + sourceArea + "'";
            }
            if (type >0)
            {
                sql += "and a.F_InfoType='" + type + "'";
            }
            if (source >0)
            {
                sql += "and a.F_InfoSource='" + source + "'";
            }

            if (keyid>0)
            {
                sql += "and a.F_Key='" + keyid + "' ";
            }
          
           
            
            if (kptype <=0)
            {
                if (timetype == 1)
                {
                    sql += "and datediff(DAY ,a.F_CreateTime ,getdate())=0 ";
                }
                else if (timetype == 2)
                {
                    sql += "and datediff(WEEK ,a.F_CreateTime ,getdate())=0 ";
                }
                else
                {
                    sql += "and datediff(MONTH ,a.F_CreateTime ,getdate())=0 ";
                }
            }
          
            if (hour > -1)
            {
                sql += " and  datepart(hh,a.F_CreateTime)=" + hour;
            }
            if (dptype>0)
            {
                if (deptid <= 0)
                {
                    return Error("请选择部门");
                }
                    if (dptype==1)
                {
                    //当月平均办理时长
                    sql += "and a.F_IsResult=0 and datediff(MONTH , a.F_CreateTime , getdate())= 0  and a.F_DealDeptId  is not null and a.F_MainDeptId is not null and a.F_WorkState in(6, 9)  and a.F_AssignTime is not null and F_DealTime is not null";
                }
                else if (dptype == 1)
                {
                    //上月平均办理时长
                    sql += "and F_IsResult=0 and datediff(MONTH , F_CreateTime , getdate())= 1  and F_DealDeptId  is not null and F_MainDeptId is not null and F_WorkState in(6, 9)  and F_AssignTime is not null and F_DealTime is not null";
                }
                else if (dptype == 2)
                {
                    //上月平均办理时长
                    sql += "and F_IsResult=0 and datediff(MONTH , F_CreateTime , getdate())= 1  and F_DealDeptId  is not null and F_MainDeptId is not null and F_WorkState in(6, 9)  and F_AssignTime is not null and F_DealTime is not null";
                }
                else if (dptype == 3)
                {
                    //工单数量
                    sql += " and datediff(MONTH ,F_CreateTime ,getdate())=0 and F_WorkState not in (0, 1, 11) and F_MainDeptId is not null and F_MainDeptId != ''";
                }
                else if (dptype == 4)
                {
                    //工单不满意数量
                    sql += "and  datediff(MONTH ,F_CreateTime ,getdate())=0 and F_WorkState not in (0, 1, 11) and F_MainDeptId is not null and F_MainDeptId != ''and F_WorkOrderId in(select F_WorkOrderId from T_Bus_VisitResult where F_IsSatisfie = 0 and F_CreateTime > a.F_AssignTime )";
                }
                else if (dptype == 5)
                {
                    //工单超期
                    sql += " and ((isnull(F_DealTime,F_CloseTime )>(select top 1 F_LimitTime  from T_Bus_AssignedInfo WITH(NOLOCK)WHERE F_WorkOrderId = a.F_WorkOrderId  and F_State = 1 and F_IsDelete = 0 and F_IsSure<>3   ORDER BY F_Id DESC) AND F_WorkState = 9 AND F_CloseTime IS NOT NULL) OR(isnull(F_DealTime, getdate()) > (select top 1 F_LimitTime from T_Bus_AssignedInfo WITH(NOLOCK)WHERE F_WorkOrderId = a.F_WorkOrderId  and F_State = 1 and F_IsDelete = 0 and F_IsSure <> 3   ORDER BY F_Id DESC) AND F_WorkState in(2, 4, 6,7, 8) ) )  and datediff(MONTH , a.F_CreateTime , getdate())= 0 ";
                }
               

            }
            if (kptype>0)
            {
                if (deptid <= 0)
                {
                    return Error("请选择部门");
                }
                if (string .IsNullOrEmpty (kpcontent))
                    return Error("请选择考评内容");
                if (kptype==1)
                {
                    if (DWDeptid.ContainsKey(deptid))
                    {
                        sql += " and a.F_WorkOrderId in(select F_WorkOrderId from T_Sys_Achievements where  F_Deptid in(" + DWDeptid[deptid] + "," + deptid + ")" + " and datediff(MONTH ,F_CreatTime ,getdate())=0    and F_Type = 0 and F_Evaluation = '" + kpcontent + "') ";
                    }
                    else
                        sql += " and a.F_WorkOrderId in( select F_WorkOrderId from T_Sys_Achievements where  F_Deptid=" + deptid + " and datediff(MONTH ,F_CreatTime ,getdate())=0    and F_Type = 0 and F_Evaluation = '" + kpcontent + "') ";


                }
                else
                {

                    if (DWDeptid.ContainsKey(deptid))
                    {
                        sql += " and a.F_WorkOrderId in(select F_WorkOrderId from T_Sys_Achievements where  F_Deptid in(" + DWDeptid[deptid] + "," + deptid + ")" + " and datediff(MONTH ,F_CreatTime ,getdate())=0    and F_Type = 0 and F_Bonusitems = '" + kpcontent + "') ";
                    }
                    else
                        sql += " and a.F_WorkOrderId in(select F_WorkOrderId from T_Sys_Achievements where  F_Deptid=" + deptid + " and datediff(MONTH ,F_CreatTime ,getdate())=0    and F_Type = 0 and F_Bonusitems = '" + kpcontent + "') ";

                }
            }
            string cols = "  a.F_WorkOrderId,F_ComTitle,F_WorkState,F_MainDeptId,F_IsResult,a.F_CreateTime,dbo.GetUserName(a.F_CreateUser) as UserName,dbo.GetDictionaryName(F_InfoType) as TypeName,dbo.GetDictionaryName(F_InfoSource) as SourceName,dbo.GetDictionaryName(F_Key) KeyName,dbo.GetDeptNames(F_MainDeptId) as DeptName,F_CloseTime,F_LimitTime as LimitTime,F_ComContent,F_CusPhone,F_AssignTime";
            int recordCount = 0;
            dt = BLL.PagerBLL.GetListPager(
                "T_Bus_WorkOrder a WITH(NOLOCK)"+ value,
                "a.F_WorkOrderId",
                cols,
                sql,
                "ORDER BY a.F_CreateTime DESC",
                pagesize,
                pageindex,
                true,
                out recordCount);

            var obj = new
            {
                state = "success",
                message = "成功",
                rows = dt,
                total = recordCount
            };
            return Content(obj.ToJson());
        }
        /// <summary>
        /// 退回工单列表
        /// </summary>
        /// <returns></returns>
        public ActionResult WorkBackOrdrList()
        {
            DataTable dt = new DataTable();
            string sql = " and a.F_IsDelete=0 ";
            int type = RequestString.GetInt("type", 0);
            int deptid = RequestString.GetInt("deptid", 0);
            string strpageindex = RequestString.GetQueryString("page");
            int pageindex = 1;
            string strpagesize = RequestString.GetQueryString("pagesize");
            int pagesize = 10;
            if (strpageindex.Trim() != "")
            {
                pageindex = Convert.ToInt32(strpageindex);
            }
            if (strpagesize.Trim() != "")
            {
                pagesize = Convert.ToInt32(strpagesize);
            }
            Dictionary<int, int> DWDeptid = new Dictionary<int, int>();
            DWDeptid.Add(220, 1267);
            DWDeptid.Add(219, 1266);
            DWDeptid.Add(197, 1268);
            DWDeptid.Add(204, 1271);
            DWDeptid.Add(199, 1264);
            DWDeptid.Add(218, 1265);
            DWDeptid.Add(202, 1269);
            DWDeptid.Add(203, 1270);
            if (deptid>0)
            {
                if (DWDeptid.ContainsKey(deptid))
                {
                    sql += "and V.F_CreateDeptId in(" + DWDeptid[deptid] + "," + deptid + ")";
                }
                else
                    sql += "and V.F_CreateDeptId='" + deptid + "'";



            }
            if (type >0)
            {
                sql += "and datediff(hh,(select F_CreateTime   from T_Bus_AssignedInfo   where F_Id = V.F_AssignedId )  ,v.F_CreateTime)> 4";
            }
            sql += "and datediff(MONTH ,a.F_CreateTime ,getdate())=0 ";
            string value = " inner JOIN T_Bus_Feedback V on a .F_WorkOrderId =V.F_WorkOrderId and V.F_Type =3";
            string cols = "a.F_WorkOrderId,a.F_ComTitle,a.F_WorkState,a.F_MainDeptId,a.F_IsResult,a.F_CreateTime,dbo.GetUserName(a.F_CreateUser) as UserName,dbo.GetDictionaryName(a.F_InfoType) as TypeName,dbo.GetDictionaryName(F_InfoSource) as SourceName,dbo.GetDictionaryName(F_Key) KeyName,dbo.GetDeptNames(F_MainDeptId) as DeptName,F_CloseTime,F_LimitTime as LimitTime,F_ComContent ,v.F_CreateDeptId";
            int recordCount = 0;
            dt = BLL.PagerBLL.GetListPager(
                "T_Bus_WorkOrder a WITH(NOLOCK)" + value,
                "a.F_WorkOrderId",
                cols,
                sql,
                "ORDER BY a.F_CreateTime DESC",
                pagesize,
                pageindex,
                true,
                out recordCount);
            var obj = new
            {
                state = "success",
                message = "成功",
                rows = dt,
                total = recordCount
            };
            return Content(obj.ToJson());
        }
        /// <summary>
        /// 知识库
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="etime"></param>
        /// <param name="deptid"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public ActionResult GetListDemands( string stime, string etime, int deptid = 0,int type=0)
        {
            string sql = " and F_IsDelete=0 ";
            DataTable dt = new DataTable();
            string strpageindex = RequestString.GetQueryString("page");
            int pageindex = 1;
            string strpagesize = RequestString.GetQueryString("pagesize");
            int pagesize = 10;
            Dictionary<int, int> DWDeptid = new Dictionary<int, int>();
            DWDeptid.Add(220, 1267);
            DWDeptid.Add(219, 1266);
            DWDeptid.Add(197, 1268);
            DWDeptid.Add(204, 1271);
            DWDeptid.Add(199, 1264);
            DWDeptid.Add(218, 1265);
            DWDeptid.Add(202, 1269);
            DWDeptid.Add(203, 1270);
            if (deptid > 0)
            {
                if (DWDeptid.ContainsKey(deptid))
                {
                    sql += " and F_Deptid in(" + DWDeptid[deptid] + "," + deptid + ")";
                   
                }
                else
                    sql += " and F_Deptid='" + deptid + "'";
            }
               
            else
                return Error("请选择部门");
            sql += "and datediff(MONTH ,F_CreateTime ,getdate())=0 ";
            if (type>0)
            {
                sql += "and isnull(F_SubmitTime , getdate()) >F_LimitTime ";
            }
            if (strpageindex.Trim() != "")
                pageindex = Convert.ToInt32(strpageindex);
            if (strpagesize.Trim() != "")
                pagesize = Convert.ToInt32(strpagesize);
            int recordCount = 0;
            dt = BLL.PagerBLL.GetListPager(
                "T_Repository_List_Demands",
                "F_DemandsId",
                "*,dbo.GetDeptName(F_Deptid) F_DeptName,dbo.GetUserName(F_CreateUser) as F_CreateUserName,dbo.GetUserName(F_SubmitUser) as F_SubmitUserName",
                sql,
                "ORDER BY F_CreateTime desc",
                pagesize,
                pageindex,
                true,
                out recordCount);
            var obj = new
            {
                state = "success",
                message = "成功",
                rows = dt,
                total = recordCount
            };

            return Content(obj.ToJson());
        }

        #region 工单类型
        /// <summary>
        /// 获取当日24小时类型受理量
        /// </summary>
        /// <returns></returns>
        public ActionResult GetTypeCount24ByNow()
        {
            var date = DateTime.Now;
            string where = $" and F_IsDelete=0 and F_CreateTime>='{date.ToString("yyyy-MM-dd")} 00:00:00'";
            var hr = date.Hour + 1;
            int[] hours = Enumerable.Range(0, hr).ToArray<int>();

            var typeList = new BLL.T_Sys_DictionaryValue().GetModelList(" F_PrentId=37 and F_State=0", "  F_ValueId ");

            var obj = new
            {
                hours,
                cols = typeList.Select(p => p.F_Value).ToList(),
                colsid= typeList.Select(p => p.F_ValueId ).ToList(),
                counts = new List<int[]>(),
            };
            string sqltype = " select datepart(hh, F_CreateTime) hour, count(1) count,F_InfoType type  from dbo.T_Bus_WorkOrder where 1=1 " + where + " group by datepart(hh, F_CreateTime),F_InfoType order by datepart(hh, F_CreateTime)";
            DataTable dttype = DbHelperSQL.Query(sqltype).Tables[0];

            foreach (var type in typeList)
            {
                int[] ah = new int[hours.Length];
                for (int i = 0; i < hours.Length; i++)
                {
                    var drs = dttype.Select("type='" + type.F_ValueId + "' and hour='" + hours[i] + "'");
                    int hc = 0;
                    if (drs.Length > 0)
                        hc = (from DataRow dr in drs select dr.Field<int>("count")).FirstOrDefault();

                    ah[i] = hc;
                }
                obj.counts.Add(ah);
            }
            obj.cols.Insert(0, "总数");
            obj. colsid.Insert(0, 0);
            string sql = "select datepart(hh, F_CreateTime) hour, count(1) count from dbo.T_Bus_WorkOrder where 1=1 " + where + " group by datepart(hh, F_CreateTime) order by datepart(hh, F_CreateTime)";
            var dt = DbHelperSQL.Query(sql).Tables[0];

            int[] th = new int[hours.Length];
            for (int i = 0; i < hours.Length; i++)
            {
                var drs = dt.Select("hour='" + hours[i] + "'");
                int hc = 0;
                if (drs.Length > 0)
                    hc = (from DataRow dr in drs select dr.Field<int>("count")).FirstOrDefault();
                th[i] = hc;
            }
            obj.counts.Insert(0, th);
          
            return Success("加载成功", obj);
        }
        /// <summary>
        /// 获取当日受理量
        /// </summary>
        /// <returns></returns>
        public ActionResult GetAcceptanceCountByNow()
        {
            var date = DateTime.Now;
            string where = $" and F_IsDelete=0 and F_CreateTime>='{date.ToString("yyyy-MM-dd")} 00:00:00'";
          
            string sql = $" and   BeginTime>='{date.ToString("yyyy-MM-dd")} 00:00:00' and CallType = 0 ";
            string sqlhw = " select SUM(case  CallType  when 0 then 1 else 0 end) hrcount ,SUM(case  CallType  when 0 then(case CallState when 1then 1 else 0 end ) else 0 end) jtcount, SUM(case  CallType  when 1 then 1 else 0 end) hccount from T_Call_CallRecords where  1=1 "+sql;
            DataTable dthw = DbHelperSQL.Query(sqlhw).Tables[0];

           
         
            string sqltype = " select COUNT(1) lrcount,SUM(case F_WorkState  when 6 then 1 when 9 then 1 else 0 end  ) blcount ,SUM(case F_WorkState  when 9 then 1 else 0 end) wjcount from dbo.T_Bus_WorkOrder where 1=1"+where ;
            DataTable dttype = DbHelperSQL.Query(sqltype).Tables[0];
            var obj = new
            {
               hw= dthw,
               gd= dttype
            };
            return Success("加载成功", obj);
        }
        /// <summary>
        /// 获取当日24小时受理量
        /// </summary>
        /// <returns></returns>
        public ActionResult GetAcceptanceCount24ByNow()
        {
            var date = DateTime.Now;
            string where = $" and F_IsDelete=0 and F_CreateTime>='{date.ToString("yyyy-MM-dd")} 00:00:00'";
            var hr = date.Hour + 1;
            int[] hours = Enumerable.Range(0, hr).ToArray<int>();
            var obj = new
            {
                hours,
                hrcount = new List<int[]>(),
                jtcount = new List<int[]>(),
                count = new List<int[]>(),
                counts = new List<int[]>(),
            };
            string sql = $" and   BeginTime>='{date.ToString("yyyy-MM-dd")} 00:00:00' and CallType = 0 ";
            string sqlhw = " select datepart(hh, BeginTime ) hour, count(1) count,sum(case  CallState when 1 then 1 else 0 end ) as jtcount from  dbo.T_Call_CallRecords where 1=1  "+ sql + " group by datepart(hh, BeginTime) order by datepart(hh, BeginTime)";
            DataTable dthw = DbHelperSQL.Query(sqlhw).Tables[0];

            int[] hwc = new int[hours.Length]; int[] jcc = new int[hours.Length];
            for (int i = 0; i < hours.Length; i++)
            {
                var drs = dthw.Select("hour='" + hours[i] + "'");
                int hw = 0; int jc = 0;
                if (drs.Length > 0)
                {
                    hw = (from DataRow dr in drs select dr.Field<int>("count")).FirstOrDefault();
                    jc = (from DataRow dr in drs select dr.Field<int>("jtcount")).FirstOrDefault();
                }
                hwc[i] = hw;
                jcc[i] = jc;
            }
            obj.hrcount.Add(hwc);
            obj.jtcount  .Add(jcc);
            string sqltype = " select datepart(hh, F_CreateTime) hour, count(1) count,sum(F_IsResult) as resultcount from dbo.T_Bus_WorkOrder where 1=1 "+where +" group by datepart(hh, F_CreateTime) order by datepart(hh, F_CreateTime)";
            DataTable dttype = DbHelperSQL.Query(sqltype).Tables[0];

            int[] ah = new int[hours.Length]; int[] rh = new int[hours.Length];
            for (int i = 0; i < hours.Length; i++)
            {
                var drs = dttype.Select("hour='" + hours[i] + "'");
                int hc = 0;int rc = 0;
                if (drs.Length > 0)
                {
                    hc = (from DataRow dr in drs select dr.Field<int>("count")).FirstOrDefault();
                    rc = (from DataRow dr in drs select dr.Field<int>("resultcount")).FirstOrDefault();
                }
                ah[i] = hc;
                rh[i] = rc;
            }
            obj.count.Add(ah);
            obj.counts.Add(rh);


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

        /// <summary>
        /// 获取当日类型受理量
        /// </summary>
        /// <returns></returns>
        public ActionResult GetTypeCountByNow()
        {
            var date = DateTime.Now;
            string sql = $" select F_Value TypeName,(select COUNT(1) from T_Bus_WorkOrder with(nolock) where F_IsDelete=0 and F_InfoType=F_ValueId and F_CreateTime>='{date.ToString("yyyy-MM-dd")} 00:00:00') Count from dbo.T_Sys_DictionaryValue with(nolock) where F_PrentId=37 and F_State=0 ";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];

            DataRow dr = dt.NewRow();
            dr["TypeName"] = "总数";
            string tsql = $"select COUNT(1) from T_Bus_WorkOrder with(nolock) where F_IsDelete=0 and F_CreateTime>='{date.ToString("yyyy-MM-dd")} 00:00:00'";

            dr["Count"] = Int32.Parse( DbHelperSQL.GetSingle(tsql).ToString());

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

        #region 问题分类
        public class KeyCount
        {
            public int ValueCount { set; get; }
            public int ValueId { set; get; }
        }
       
        /// <summary>
        /// 获取当日问题分类受理量
        /// </summary>
        /// <returns></returns>
        public ActionResult GetKeyCountByNow(DateTime? date=null  ,int pid = 38 )
        {
            if (date==null )
             date = DateTime.Now;
            string sql = $" select F_Key keyid,count(1) count from dbo.T_Bus_WorkOrder with(nolock)  where F_IsDelete=0 and F_CreateTime>='{date.Value .ToString("yyyy-MM-dd")} 00:00:00' group by F_Key ";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            var keyAllList = new BLL.T_Sys_DictionaryValue().GetModelList(" F_State=0 ");
            var keylist = keyAllList.Where(p => p.F_PrentId == pid).ToList();
            int total = 0;
            List<KeyCount> jo = new List<KeyCount>();
            if (RedisHelper.StringGet("InfoNewKeyCountValueId" + pid) != null)
            {
                if (GetLarge("InfoNewKeyCountValueId" + pid, 43200))
                {
                    Task.Run(() =>
                {
                        string obsql = $" select (select COUNT(1) from [GetValueId](p .F_ValueId )) as ValueCount ,p.F_ValueId ValueId from T_Sys_DictionaryValue p with(nolock) where F_PrentId = '" + pid + "' and F_State = 0 ";
                        DataTable ob = DbHelperSQL.Query(obsql).Tables[0];
                        RedisHelper.KeyDelete("InfoNewKeyCountValueId" + pid);
                        RedisHelper.StringSet("InfoNewKeyCountValueId" + pid, ob.ToJson(), new TimeSpan(24, 0, 0));
                    RedisHelper.KeyDelete("InfoNewKeyCountValueId" + pid + "time");
                    RedisHelper.StringSet("InfoNewKeyCountValueId" + pid+"time", DateTime .Now .ToString ("yyyy-MM-dd HH:mm:ss"), new TimeSpan(24, 0, 0));
                   
                  
                });
                ;
                }
                jo = JsonConvert.DeserializeObject<List<KeyCount>>(RedisHelper.StringGet("InfoNewKeyCountValueId" + pid).ToString());
              
            }
            else
            {
                string obsql = $" select (select COUNT(1) from [GetValueId](p .F_ValueId )) as ValueCount ,p.F_ValueId ValueId from T_Sys_DictionaryValue p with(nolock) where F_PrentId = '" + pid + "' and F_State = 0 ";
                DataTable ob = DbHelperSQL.Query(obsql).Tables[0];
                RedisHelper.StringSet("InfoNewKeyCountValueId" + pid, ob.ToJson(), new TimeSpan(24, 0, 0));
                RedisHelper.StringSet("InfoNewKeyCountValueId" + pid + "time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), new TimeSpan(24, 0, 0));
                jo = JsonConvert.DeserializeObject<List<KeyCount>>(ob.ToJson());
            }
            int TotalClass = 1;
            var list = keylist.Select(p =>
            {
                var ids = getChildren(keyAllList, p.F_ValueId);
                ids.Insert(0, p.F_ValueId);
                int sum = dt.Select(" keyid in (" + string.Join(",", ids) + ") ").Sum(x => x.Field<int>("count"));
                int classcount = jo.Where(x => x.ValueId == p.F_ValueId).First().ValueCount;
                total += sum;
                TotalClass += classcount;
                return new
                {
                    KeyId = p.F_ValueId,
                    KeyName = p.F_Value,
                    Count = sum,
                    ClassCount= classcount
                };
            }).ToList();
          
            var listrate = list.Select(p => new
            {
                p.KeyId,
                p.KeyName,
                p.Count,
                Rate = string.Format("{0}%", Math.Round(p.Count * 100.00 / total, 2)),
                p .ClassCount

            });
            var obj = new
            {
                Date = listrate.OrderByDescending(p => p.Count),
                ClaseeCount = listrate.Count(),
                Total = total,
                ClassTotal= TotalClass
            };

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


        /// <summary>
        /// 获取当月单位问题分类受理量
        /// </summary>
        /// <returns></returns>
        public ActionResult GetKeyByNow(int deptid=0,int handling=0)
        {
            string value = "";
            string sql1 = "";
            if (handling > 0)
            {
                if (handling == 1)
                {
                    value = "inner join T_Bus_RemindRecord b on a.F_WorkOrderId = b.F_WorkOrderId and datediff(MONTH , a.F_CreateTime   , getdate())= 0 and F_Type = 1 ";
                }
                else if (handling == 2)
                {
                    sql1 += "and F_IsResult=1";
                }
                else if (handling == 3)
                {
                    sql1 += "and F_IsResult !=1";
                }
                else if (handling == 4)
                {
                    value = "inner join T_SMS_RecvSMS b on a .F_WorkOrderId = b.F_Name   and datediff(MONTH , b.RecvTime  , getdate())= 0 and datediff(MONTH , a.F_CreateTime   , getdate())= 0 and Content like '%收到催办,请及时处理%' ";
                }

            }
            var date = DateTime.Now;
            string sql = $" select DISTINCT F_Key keyid,count(1) count from dbo.T_Bus_WorkOrder a    WITH(NOLOCK)" +value +" where F_IsDelete=0 and datediff(MONTH , F_CreateTime , getdate())= 0 " + sql1 + " group by F_Key ";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];

            var keyAllList = new BLL.T_Sys_DictionaryValue().GetModelList(" F_State=0 ");
            var keylist = keyAllList.Where(p => p.F_PrentId == 38).ToList();
            int total = 0;
            List<KeyCount> jo = new List<KeyCount>();
            if (RedisHelper.StringGet(handling+"InfoNewKeyCountDept" + deptid) != null)
            {
                if (GetLarge(handling + "InfoNewKeyCountDept" + deptid, 43200))
                {
                    Task.Run(() =>
                {
                   
                        string obsql = $" select (select COUNT(1) from [GetValueId](p .F_ValueId )) as ValueCount ,p.F_ValueId ValueId from T_Sys_DictionaryValue p where F_PrentId =38 and F_State = 0 ";
                        DataTable ob = DbHelperSQL.Query(obsql).Tables[0];
                        RedisHelper.KeyDelete(handling + "InfoNewKeyCountDept" + deptid);
                        RedisHelper.StringSet(handling + "InfoNewKeyCountDept" + deptid, ob.ToJson(), new TimeSpan(24, 0, 0));
                    RedisHelper.KeyDelete(handling + "InfoNewKeyCountDept" + deptid + "time");
                    RedisHelper.StringSet(handling + "InfoNewKeyCountDept" + deptid+"time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), new TimeSpan(24, 0, 0));
                   
                   
                });
                ;
                }
                jo = JsonConvert.DeserializeObject<List<KeyCount>>(RedisHelper.StringGet(handling+"InfoNewKeyCountDept" + deptid).ToString());

            }
            else
            {
                string obsql = $" select (select COUNT(1) from [GetValueId](p .F_ValueId )) as ValueCount ,p.F_ValueId ValueId from T_Sys_DictionaryValue p where F_PrentId = '38' and F_State = 0 ";
                DataTable ob = DbHelperSQL.Query(obsql).Tables[0];
                RedisHelper.StringSet(handling+"InfoNewKeyCountDept" + deptid, ob.ToJson(), new TimeSpan(24, 0, 0));
                RedisHelper.StringSet(handling + "InfoNewKeyCountDept" + deptid + "time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), new TimeSpan(24, 0, 0));
                jo = JsonConvert.DeserializeObject<List<KeyCount>>(ob.ToJson());
            }
            int TotalClass = 1;
            var list = keylist.Select(p =>
            {
                var ids = getChildren(keyAllList, p.F_ValueId);
                ids.Insert(0, p.F_ValueId);
                int sum = dt.Select(" keyid in (" + string.Join(",", ids) + ") ").Sum(x => x.Field<int>("count"));
                int classcount = jo.Where(x => x.ValueId == p.F_ValueId).First().ValueCount;
                total += sum;
                TotalClass += classcount;
                return new
                {
                    KeyId = p.F_ValueId,
                    KeyName = p.F_Value,
                    Count = sum,
                    ClassCount = classcount
                };
            }).ToList();

            var listrate = list.Select(p => new
            {
                p.KeyId,
                p.KeyName,
                p.Count,
                Rate = string.Format("{0}%", Math.Round(p.Count * 100.00 / total, 2)),
                p.ClassCount

            });
            var obj = new
            {
                Date = listrate.OrderByDescending(p => p.Count),
                ClaseeCount = listrate.Count(),
                Total = total,
                ClassTotal = TotalClass
            };

            return Success("加载成功", obj);
        }
        /// <summary>
        /// 获取区域受理量
        /// </summary>
        /// <param name="date"></param>
        /// <param name="isdc"></param>
        /// <returns></returns>
        public ActionResult GetAreaCount()
        {
            if (RedisHelper.StringGet("InfoNewGetAreaCount") != null)
            {
               
                    Task.Run(() =>
                {
                    Dictionary<string, string> paras = new Dictionary<string, string>();
                    var ob = DbHelperSQL.RunProcedure("P_Areacontrast_1", paras, "Areacontrast").Tables[0];
                    RedisHelper.KeyDelete("InfoNewGetAreaCount");
                    RedisHelper.StringSet("InfoNewGetAreaCount", ob.ToJson(), new TimeSpan(0, 5, 0));
                  
                });
               
                return Content(RedisHelper.StringGet("InfoNewGetAreaCount").ToString());
            }
            else
            {
                Dictionary<string, string> paras = new Dictionary<string, string>();
                var obj = DbHelperSQL.RunProcedure("P_Areacontrast_1", paras, "Areacontrast").Tables[0];
                RedisHelper.StringSet("InfoNewGetAreaCount", obj.ToJson(), new TimeSpan(0, 5, 0));
               
                return Content(obj.ToJson());
                
            }
        }



        /// <summary>
        /// 获取当日问题分类受理量
        /// </summary>
        /// <returns></returns>
        public ActionResult GetMapByNow(int type=1,string areaid = "")
        {
            if (RedisHelper.StringGet("InfoNewMapByNow" + type+ areaid) != null)
            {
                if (GetLarge("InfoNewMapByNow" + type + areaid))
                {
                    Task.Run(() =>
                {
                    Dictionary<string, string> paras = new Dictionary<string, string>();
                    var ob = DbHelperSQL.RunProcedure("P_MapByNow", paras, "MapByNow").Tables[0];
                    paras.Add("@type", type.ToString());
                    paras.Add("@areaid", areaid.ToString());
                    RedisHelper.KeyDelete("InfoNewMapByNow" + type + areaid);
                    RedisHelper.StringSet("InfoNewMapByNow" + type + areaid, ob.ToJson(), new TimeSpan(0, 5, 0));
                    RedisHelper.KeyDelete("InfoNewMapByNow" + type + areaid + "time");
                    RedisHelper.StringSet("InfoNewMapByNow" + type + areaid + "time",
 DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), new TimeSpan(0, 5, 0));
                });
                }
                return Content(RedisHelper.StringGet("InfoNewMapByNow" + type + areaid).ToString());
            }
            else
            {
                Dictionary<string, string> paras = new Dictionary<string, string>();
                paras.Add("@type", type.ToString());
                paras.Add("@areaid", areaid.ToString());
                var obj = DbHelperSQL.RunProcedure("P_MapByNow", paras, "MapByNow").Tables[0];
                RedisHelper.StringSet("InfoNewMapByNow" + type + areaid, obj.ToJson(), new TimeSpan(0, 5, 0));
                RedisHelper.StringSet("InfoNewMapByNow" + type + areaid + "time",
  DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), new TimeSpan(0, 5, 0));
                return Content(obj.ToJson());
            }
        }
        /// <summary>
        /// 获取当日问题分类受理量
        /// </summary>
        /// <returns></returns>
        public ActionResult GetKeyAreaCountByNow(int pid = 38,int id =0)
        {
            var date = DateTime.Now;
            string sql = $" select F_Key keyid,count(1) count from dbo.T_Bus_WorkOrder where F_IsDelete=0 and F_Township='{id}' and F_CreateTime>='{date.ToString("yyyy-MM-dd")} 00:00:00' group by F_Key ";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];

            var keyAllList = new BLL.T_Sys_DictionaryValue().GetModelList(" F_State=0 ");
            var keylist = keyAllList.Where(p => p.F_PrentId == pid).ToList();
            var list = keylist.Select(p =>
            {
                var ids = getChildren(keyAllList, p.F_ValueId);
                ids.Insert(0, p.F_ValueId);
                int sum = dt.Select(" keyid in (" + string.Join(",", ids) + ") ").Sum(x => x.Field<int>("count"));
              
               
                return new
                {
                    KeyId = p.F_ValueId,
                    KeyName = p.F_Value,
                    Count = sum,
                };
            }).ToList();

           
          
            return Success("加载成功", list);
        }
        /// <summary>
        /// 获取办理情况三级
        /// </summary>
        /// <param name="date"></param>
        /// <param name="isdc"></param>
        /// <returns></returns>
        public ActionResult GetHandleCount( int type=0)
        {
            if (RedisHelper.StringGet("InfoNewHandleCount"+ type) != null)
            {
                if (GetLarge("InfoNewHandleCount" + type))
                {
                    Task.Run(() =>
                {
                    Dictionary<string, string> paras = new Dictionary<string, string>();
                    var ob = DbHelperSQL.RunProcedure("P_Handle", paras, "Handle").Tables[0];
                    paras.Add("@type", type.ToString());
                    RedisHelper.KeyDelete("InfoNewHandleCount" + type);
                    RedisHelper.StringSet("InfoNewHandleCount" + type, ob.ToJson(), new TimeSpan(0, 5, 0));
                    RedisHelper.KeyDelete("InfoNewHandleCount" + type  + "time");
                    RedisHelper.StringSet("InfoNewHandleCount" + type + "time",
 DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), new TimeSpan(0, 5, 0));
                });
                }
                return Content(RedisHelper.StringGet("InfoNewHandleCount"+ type).ToString());
            }
            else
            {
                Dictionary<string, string> paras = new Dictionary<string, string>();
                var obj = DbHelperSQL.RunProcedure("P_Handle", paras, "Handle").Tables[0];
                RedisHelper.StringSet("InfoNewHandleCount"+ type, obj.ToJson(), new TimeSpan(0, 5, 0));
                RedisHelper.StringSet("InfoNewHandleCount" + type + "time",
 DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), new TimeSpan(0, 5, 0));
                return Content(obj.ToJson());
            }
        }
        /// <summary>
        /// 获取平均办理时长
        /// </summary>
        /// <param name="date"></param>
        /// <param name="isdc"></param>
        /// <returns></returns>
        public ActionResult GetDeptDayTime(int type=0)
        {
            if (RedisHelper.StringGet("InfoNewGetDeptDayTime"+ type) != null)
            {

                Task.Run(() =>
               {
                   Dictionary<string, string> paras = new Dictionary<string, string>();
                   paras.Add("@type", type.ToString());
                   var ob = DbHelperSQL.RunProcedure("P_DeptDayTime", paras, "DeptDayTime").Tables[0]; ;
                   RedisHelper.KeyDelete("InfoNewGetDeptDayTime" + type);
                   RedisHelper.StringSet("InfoNewGetDeptDayTime" + type, ob.ToJson(), new TimeSpan(0, 5, 0));
               });              
                return Content(RedisHelper.StringGet("InfoNewGetDeptDayTime"+ type).ToString());
            }
            else
            {
                Dictionary<string, string> paras = new Dictionary<string, string>();
                paras.Add("@type", type.ToString());
                var obj = DbHelperSQL.RunProcedure("P_DeptDayTime", paras, "DeptDayTime").Tables[0]; ;
                RedisHelper.StringSet("InfoNewGetDeptDayTime"+ type, obj.ToJson(), new TimeSpan(0, 5, 0));
                ;
                return Content(obj.ToJson());
            }
           
        }


        /// <summary>
        /// 获取平台办理情况分类
        /// </summary>
        /// <returns></returns>
        public ActionResult GetHandling()
        {
            if (RedisHelper.StringGet("InfoNewHandling") != null)
            {
                if (GetLarge("InfoNewHandling"))
                {
                    Task.Run(() =>
                {
                    Dictionary<string, string> paras = new Dictionary<string, string>();
                    var ob = DbHelperSQL.RunProcedure("P_Handling", paras, "Handling").Tables[0]; ;
                    RedisHelper.KeyDelete("InfoNewHandling");
                    RedisHelper.StringSet("InfoNewHandling", ob.ToJson(), new TimeSpan(0, 5, 0));
                    RedisHelper.KeyDelete("InfoNewHandling" + "time");
                    RedisHelper.StringSet("InfoNewHandling" + "time",
 DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), new TimeSpan(0, 5, 0));
                });

                }
                return Content(RedisHelper.StringGet("InfoNewHandling").ToString());
            }
            else
            {
                Dictionary<string, string> paras = new Dictionary<string, string>();
                var obj = DbHelperSQL.RunProcedure("P_Handling", paras, "Handling").Tables[0]; ;
                RedisHelper.StringSet("InfoNewHandling", obj.ToJson(), new TimeSpan(0, 5, 0));
                RedisHelper.StringSet("InfoNewHandling" + "time",
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), new TimeSpan(0, 5, 0));
                return Content(obj.ToJson());
            }
        }

        /// <summary>
        /// 获取绩效
        /// </summary>
        /// <param name="date"></param>
        /// <param name="isdc"></param>
        /// <returns></returns>
        public ActionResult GetDeptDeptAchievements(string starttime="", string endtime="",int type=0)
        {

            if (string.IsNullOrEmpty(starttime))
            {
                starttime = DateTime.Now.AddMonths(-1).ToString("yyyy-MM") + "-21 00:00:00";
                endtime = DateTime.Now.ToString("yyyy-MM") + "-20 00:00:00";
            }
            else
            {
                if (string.IsNullOrEmpty(endtime))
                {
                    endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                }
            }
            if (RedisHelper.StringGet("InfoNewGetDeptAchievements"+ type+ starttime+ endtime) != null)
            {
                    Task.Run(() =>
                {
                    Dictionary<string, string> paras = new Dictionary<string, string>();
                    paras.Add("@type", type.ToString());
                    paras.Add("@sdate", starttime);
                    paras.Add("@edate", endtime);
                    var ob = DbHelperSQL.RunProcedure("P_DeptAchievements", paras, "DeptAchievements").Tables[0]; ;
                    RedisHelper.KeyDelete("InfoNewGetDeptAchievements" + type + starttime + endtime);
                    RedisHelper.StringSet("InfoNewGetDeptAchievements" + type + starttime + endtime, ob.ToJson(), new TimeSpan(0, 5, 0));
                    
                });
                
                return Content(RedisHelper.StringGet("InfoNewGetDeptAchievements"+ type + starttime + endtime).ToString());
            }
            else
            {
                Dictionary<string, string> paras = new Dictionary<string, string>();
                paras.Add("@type", type.ToString());
                paras.Add("@sdate", starttime);
                paras.Add("@edate", endtime);
                var obj = DbHelperSQL.RunProcedure("P_DeptAchievements", paras, "DeptAchievements").Tables[0]; ;
                RedisHelper.StringSet("InfoNewGetDeptAchievements"+ type + starttime + endtime, obj.ToJson(), new TimeSpan(0, 5, 0));
                return Content(obj.ToJson());
            }

        }
       
       
        /// <summary>
        /// 平台受理情况概况
        /// </summary>
        /// <returns></returns>
        public ActionResult GetAreaAcceptance()
        {
            if (RedisHelper.StringGet("InfoNewGetAcceptance") != null)
            {
               
                    Task.Run(() =>
                {
                    Dictionary<string, string> paras = new Dictionary<string, string>();
                    var ob = DbHelperSQL.RunProcedure("P_Acceptance", paras, "Acceptance").Tables[0]; ;
                    RedisHelper.KeyDelete("InfoNewGetAcceptance");
                    RedisHelper.StringSet("InfoNewGetAcceptance", ob.ToJson(), new TimeSpan(0, 5, 0));
                });
               
                return Content(RedisHelper.StringGet("InfoNewGetAcceptance").ToString());
            }
            else
            {
                Dictionary<string, string> paras = new Dictionary<string, string>();
                var obj = DbHelperSQL.RunProcedure("P_Acceptance", paras, "Acceptance").Tables[0]; ;
                RedisHelper.StringSet("InfoNewGetAcceptance", obj.ToJson(), new TimeSpan(0, 5, 0));
                return Content(obj.ToJson());
            }



        }
        /// <summary>
        /// 获取当月区域坐标
        /// </summary>
        /// <returns></returns>
        public ActionResult GetCoordinate(string  areaid)
        {
            string sqlarea = "select F_Latitude,F_Longitude from t_bus_workorder    where datediff(MONTH , F_CreateTime , getdate())= 0 and F_IsDelete = 0 and F_Latitude is not null and F_Longitude is not null and F_SourceArea = '"+ areaid + "' GROUP BY F_Latitude,F_Longitude";
            DataTable dttype = DbHelperSQL.Query(sqlarea).Tables[0];
            var obj = new
            {
                Coordinate= dttype
            };
            return Success("获取成功", obj);
        }


        /// <summary>
        /// 获取父级下的所有子级id
        /// </summary>
        /// <param name="list"></param>
        /// <param name="pid"></param>
        /// <returns></returns>
        private List<int> getChildren(List<Model.T_Sys_DictionaryValue> list, int pid)
        {
            List<int> ids = new List<int>();
            var clist = list.Where(p => p.F_PrentId == pid).ToList();
            foreach (var d in clist)
            {
                ids.Add(d.F_ValueId);
                ids = ids.Union(getChildren(list, d.F_ValueId)).ToList();
            }

            return ids;
        }
        #endregion

        #region 受理渠道
        /// <summary>
        /// 获取当日渠道受理量
        /// </summary>
        /// <returns></returns>
        public ActionResult GetSourceCountByNow(string stime ,string etime)
        {
            var date = DateTime.Now;
            if (string .IsNullOrEmpty(stime) )
            {
                stime = date.ToString("yyyy-MM-dd") + " 00:00:00";
            }
           
            if (string.IsNullOrEmpty(etime))
            {
                etime = date.ToString("yyyy-MM-dd HH:mm:ss");
            }
            
            string sql = $" select F_Value TypeName,F_ValueId ValueId,(select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0 and F_InfoSource=F_ValueId and F_CreateTime>='{stime}'and F_CreateTime<='{etime}' ) Count from dbo.T_Sys_DictionaryValue where F_PrentId=36 and F_State=0 order by F_ValueId ";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];

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

        #region 办理情况
        /// <summary>
        /// 获取当日办理类型受理量
        /// </summary>
        /// <returns></returns>
        public ActionResult GetDealTypeCountByNow()
        {
            var date = DateTime.Now;
            DataTable dt = new DataTable();
            dt.Columns.Add("DealType");
            dt.Columns.Add("Count");

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

            string tsql = $"select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0 and isnull(F_IsResult,0)=1 and F_CreateTime>='{date.ToString("yyyy-MM-dd")} 00:00:00'";
            dr["Count"] = DbHelperSQL.GetSingle(tsql).ToString();

            DataRow dr1 = dt.NewRow();
            dr["DealType"] = "网络转办";

            string tsql1 = $"select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0 and isnull(F_IsResult,0)=0 and F_CreateTime>='{date.ToString("yyyy-MM-dd")} 00:00:00'";
            dr["Count"] = DbHelperSQL.GetSingle(tsql1).ToString();

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

        #region 工单相关
        /// <summary>
        /// 工单列表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetList()
        {
            DataTable dt = new DataTable();
            string sql = " and F_IsDelete=0 ";
            string strstate = HttpUtility.UrlDecode(RequestString.GetQueryString("state"));
            string strstarttime = HttpUtility.UrlDecode(RequestString.GetQueryString("starttime"));
            string strendtime = HttpUtility.UrlDecode(RequestString.GetQueryString("endtime"));
            int source = RequestString.GetInt("source", 0);
            int keyid = RequestString.GetInt("keyid", 0);
            int type = RequestString.GetInt("type", 0);
            int sourcearea = RequestString.GetInt("sourcearea", 0);
            int deptid = RequestString.GetInt("deptid", 0);
            int dealtype = RequestString.GetInt("dealtype", -1);
            string strpageindex = RequestString.GetQueryString("page");
            int pageindex = 1;
            string strpagesize = RequestString.GetQueryString("pagesize");
            int pagesize = 10;
            string dealstarttime = HttpUtility.UrlDecode(RequestString.GetQueryString("dealstarttime"));
            string dealstrendtime = HttpUtility.UrlDecode(RequestString.GetQueryString("dealstrendtime"));

            if (dealstarttime.Trim() != "" && dealstarttime != "undefined")
            {
                if (dealstrendtime.Trim() != "" && dealstrendtime != "undefined")
                {
                    sql += " and F_DealTime  between   '" + dealstarttime +
                             "' AND  '" + dealstrendtime + "'";
                }
                else
                {
                    sql += " and F_DealTime>='" + dealstarttime + "' ";
                }
            }
            else
            {
                if (dealstrendtime.Trim() != "" && dealstrendtime != "undefined")
                {
                    sql += " and F_DealTime<='" + dealstrendtime + "' ";
                }
            }


            if (strstate.Trim() != "" && strstate != "undefined")
            {
                sql += " and F_WorkState = '" + strstate.Trim() + "' ";
            }

            if (source != 0)
            {
                sql += " and F_InfoSource = '" + source + "' ";
            }

            if (keyid != 0)
            {
                sql += " and F_Key = '" + keyid + "' ";
            }

            if (type != 0)
            {
                sql += " and F_InfoType = '" + type + "' ";
            }

            if (sourcearea != 0)
            {
                sql += " and F_SourceArea = '" + sourcearea + "' ";
            }

            if (deptid != 0)
            {
                sql += " and  (F_MainDeptId = '" + deptid + "' or F_MainDeptID3='" + deptid + "')";
            }

            if (strstarttime.Trim() != "" && strstarttime != "undefined")
            {
                if (strendtime.Trim() != "" && strendtime != "undefined")
                {
                    sql += " and F_CreateTime  between   '" + strstarttime +
                             "' AND  '" + strendtime + "'";
                }
                else
                {
                    sql += " and F_CreateTime>='" + strstarttime + "' ";
                }
            }
            else
            {
                if (strendtime.Trim() != "" && strendtime != "undefined")
                {
                    sql += " and F_CreateTime<='" + strendtime + "' ";
                }
            }

            if (dealtype != -1)
            {
                sql += " and isnull(F_IsResult,0) = '" + dealtype + "' ";
            }

            if (strpageindex.Trim() != "")
            {
                pageindex = Convert.ToInt32(strpageindex);
            }

            if (strpagesize.Trim() != "")
            {
                pagesize = Convert.ToInt32(strpagesize);
            }

            string cols = "F_Result,F_WorkOrderId,F_WorkState,F_DealTime,F_IsResult,F_CreateTime,dbo.GetUserName(F_CreateUser) as UserName,dbo.GetDictionaryName(F_InfoType) as TypeName,dbo.GetDictionaryName(F_InfoSource) as SourceName,dbo.GetDeptNames(F_MainDeptId) as DeptName,F_CloseTime,F_AssignTime as AssignTime,F_LimitTime as LimitTime,F_ComContent ";

            int recordCount = 0;
            dt = BLL.PagerBLL.GetListPager(
                "T_Bus_WorkOrder WITH(NOLOCK)",
                "F_WorkOrderId",
                cols,
                sql,
                "ORDER BY F_CreateTime DESC",
                pagesize,
                pageindex,
                true,
                out recordCount);

            var obj = new
            {
                state = "success",
                message = "成功",
                rows = dt,
                total = recordCount
            };
            return Content(obj.ToJson());
        }
        /// <summary>
        /// 工单详情
        /// </summary>
        /// <returns></returns>
        public ActionResult GetWorkOrder()
        {
            string strworkorderid = HttpUtility.UrlDecode(RequestString.GetQueryString("workorderid"));
            if (!string.IsNullOrEmpty(strworkorderid))
            {
                string sql = "select *,dbo.GetUserName(F_CreateUser) as UserName,dbo.GetDictionaryName(F_InfoType) as TypeName1,dbo.GetAreaName(F_SourceArea) as AreaName,dbo.GetDictionaryName(F_InfoSource) as SourceName,dbo.GetDictionaryName(F_Key) as KeyName,dbo.GetDeptName(F_MainDeptId) deptname from T_Bus_WorkOrder a WITH(NOLOCK) where F_WorkOrderId ='" + strworkorderid + "'";
                var dt = DbHelperSQL.Query(sql).Tables[0];
                if (dt.Rows.Count > 0)
                {

                    if (dt.Rows[0]["F_IsProtect"] != null)
                    {
                        if (dt.Rows[0]["F_IsProtect"].ToString() == "1")
                        {
                            dt.Rows[0]["F_CusName"] = "";
                            dt.Rows[0]["F_CusPhone"] = "";
                            dt.Rows[0]["F_ConPhone"] = "";
                        }
                    }

                    return Success("获取成功", dt);
                }
                else
                {
                    return Error("获取失败");
                }

            }
            else
            {
                return Error("参数传输失败");
            }
        }
        /// <summary>
        /// 工单流转
        /// </summary>
        /// <returns></returns>
        public ActionResult GetWorkOrderProcess()
        {
            string strworkorderid = HttpUtility.UrlDecode(RequestString.GetQueryString("workorderid"));
            if (!string.IsNullOrEmpty(strworkorderid))
            {
                string gcsql = "select F_Message,F_CreateTime from T_Bus_Operation where F_IsDelete=0 and F_WorkOrderId ='" + strworkorderid + "' order by F_CreateTime ";
                var gcdt = DbHelperSQL.Query(gcsql).Tables[0];
                return Success("获取成功", gcdt);
            }
            else
            {
                return Error("参数传输失败");
            }
        }

        //获取通话记录列表
        public ActionResult GetCallList()
        {
            string sql = "";
            DataTable dt = new DataTable();
            string callstate = HttpUtility.UrlDecode(RequestString.GetQueryString("callstate"));
            string calltype = HttpUtility.UrlDecode(RequestString.GetQueryString("calltype"));
            string actiontype = HttpUtility.UrlDecode(RequestString.GetQueryString("actiontype"));
            string starttime = HttpUtility.UrlDecode(RequestString.GetQueryString("starttime"));
            string endtime = HttpUtility.UrlDecode(RequestString.GetQueryString("endtime"));
            string tasktype = HttpUtility.UrlDecode(RequestString.GetQueryString("tasktype"));
            string extnumber = HttpUtility.UrlDecode(RequestString.GetQueryString("extnumber"));
            string PhoneType = HttpUtility.UrlDecode(RequestString.GetQueryString("phoneType"));
            int type = RequestString.GetInt("type", 0);
            int islike = RequestString.GetInt("islike", 1);
            string strpageindex = RequestString.GetQueryString("page");
            int pageindex = 1;
            string strpagesize = RequestString.GetQueryString("pagesize");
            int pagesize = 10;
            int hour = RequestString.GetInt("hour", -1);
            if (hour > -1)
            {
                int hours = hour + 1;
                if (hour < 23)
                    sql += " and datepart(hh,BeginTime)>=" + hour + " AND datepart(hh,BeginTime)<" + hours;
                else
                    sql += " and  datepart(hh,BeginTime)>=" + hour;
            }

            if (type != 0)
            {
                sql += " and F_CallInType = '" + type + "' ";
            }
          
            if (PhoneType != null && PhoneType.Trim() != "")
            {
                sql += " and PhoneType='" + PhoneType + "'";
            }

            if (callstate.Trim() != "")
            {
                if (callstate.Trim() == "0")
                {
                    sql += " and CallState=0 and isnull(UserCode,'')!='' ";
                }
                else if (callstate.Trim() == "3")
                {
                    sql += " and CallState=0 and CallType=0 and isnull(UserCode,'')='' ";
                }
                else
                {
                    sql += " and CallState='" + callstate + "'";
                }
            }
            if (calltype.Trim() != "")
            {
                if (calltype == "3")
                {
                    sql += " and CallType='1'";
                    sql += " and CallRecordsId in(select F_CallRecordId from T_Bus_VisitResult WITH(NOLOCK) where F_CallRecordId !='' and F_CallRecordId is not null )";
                }
                else
                    sql += " and CallType='" + calltype + "'";
            }
            if (tasktype.Trim() != "")
            {
                if (tasktype.Trim() == "2")
                {
                    sql += " and TaskType='" + tasktype + "'";
                }
                else if (tasktype.Trim() == "0")
                {
                    sql += " and (TaskType is null or TaskType!='2')";
                }
            }
            if (actiontype.Trim() != "")
            {
                sql += " and ActionType=" + actiontype.Trim();
            }
            if (starttime.Trim() != "")
            {
                sql += " and BeginTime>='" + starttime + "' ";
            }
            else
            {
                DateTime date = DateTime.Now;
                sql += $" and  BeginTime>='{date.ToString("yyyy-MM-dd")} 00:00:00'";
            }

            if (endtime.Trim() != "")
            {
                sql += " and BeginTime<='" + endtime + "' ";
            }
            if (extnumber.Trim() != "")
            {
                sql += " and isnull(ExtNumber,'')='" + extnumber.Trim() + "'";
            }

            if (strpageindex.Trim() != "")
            {
                pageindex = Convert.ToInt32(strpageindex);
            }

            if (strpagesize.Trim() != "")
            {
                pagesize = Convert.ToInt32(strpagesize);
            }
          
           
                int recordCount = 0;
                dt = BLL.PagerBLL.GetListPager(
                    "T_Call_CallRecords WITH(NOLOCK)",
                    "CallRecordsId",
                    "*,dbo.GetUserName(UserCode) as UserName, WorkOrderId,dbo.GetDictionaryName(F_CallInType) as TypeName",
                    sql,
                    "ORDER BY CallRecordsId desc",
                    pagesize,
                    pageindex,
                    true,
                    out recordCount);
                var obj = new
                {
                    state = "success",
                    message = "成功",
                    rows = dt,
                    total = recordCount
                };

                return Content(obj.ToJson());
            
        }



        #endregion

        #region 新
        /// <summary>
        /// 获取当日24小时话务受理量
        /// </summary>
        /// <returns></returns>
        public ActionResult GetHWCount24ByNow()
        {
            var date = DateTime.Now;
            var hr = date.Hour + 1;
            int[] hours = Enumerable.Range(0, hr).ToArray<int>();
            var obj = new
            {
                hours,
                hwcount = new List<int[]>(),
                jtcount = new List<int[]>()
            };
            string sql = $" and   BeginTime>='{date.ToString("yyyy-MM-dd")} 00:00:00'  ";
            string sqlhw = " select datepart(hh, BeginTime ) hour,SUM(case CallState when 1 then 1 else 0 end ) jietong ,count(1) count from T_Call_CallRecords where  1=1" + sql + " group by datepart(hh, BeginTime) order by datepart(hh, BeginTime)";
            DataTable dthw = DbHelperSQL.Query(sqlhw).Tables[0];

            int[] hwc = new int[hours.Length]; int[] jcc = new int[hours.Length];
            for (int i = 0; i < hours.Length; i++)
            {
                var drs = dthw.Select("hour='" + hours[i] + "'");
                int hw = 0; int jt = 0;
                if (drs.Length > 0)
                {
                    hw = (from DataRow dr in drs select dr.Field<int>("count")).FirstOrDefault();
                    jt= (from DataRow dr in drs select dr.Field<int>("jietong")).FirstOrDefault();
                }
                jcc[i] = jt;
                hwc[i] = hw;
            }
            obj.jtcount.Add(jcc);
            obj.hwcount.Add(hwc);

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


        /// <summary>
        /// 获取当月每日话务受理量
        /// </summary>
        /// <returns></returns>
        public ActionResult GetHWCountMonthByNow()
        {
            var date = DateTime.Now;
            var hr = date.Day;
            int[] days = Enumerable.Range(1, hr).ToArray<int>();
            var obj = new
            {
                days,
                hwcount = new List<int[]>(),
                jtcount = new List<int[]>()
            };
            string sql = $" and   BeginTime>='{date.ToString("yyyy-MM")}-01 00:00:00'  ";
            string sqlhw = " select datepart(dd, BeginTime ) day,SUM(case CallState when 1 then 1 else 0 end ) jietong, count(1) count from T_Call_CallRecords a where  1=1 "+ sql  + " group by datepart(dd, BeginTime) order by datepart(dd, BeginTime)";
            DataTable dthw = DbHelperSQL.Query(sqlhw).Tables[0];

            int[] hwc = new int[days.Length]; int[] jcc = new int[days.Length];
            for (int i = 0; i < days.Length; i++)
            {
               
                var drs = dthw.Select("day='" + days[i] + "'");
                int hw = 0;int jt = 0;
                if (drs.Length > 0)
                {
                    hw = (from DataRow dr in drs select dr.Field<int>("count")).FirstOrDefault();
                    jt = (from DataRow dr in drs select dr.Field<int>("jietong")).FirstOrDefault();
                }
                jcc[i] = jt;
                hwc[i] = hw;
            }
            obj.jtcount.Add(jcc);
            obj.hwcount.Add(hwc);

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

        /// <summary>
        /// 获取热线整合
        /// </summary>
        /// <returns></returns>
        public ActionResult GetHotline(string  stime,string etime)
        {
            string sql = "";
            if (!string .IsNullOrEmpty (stime)&& !string.IsNullOrEmpty(etime))
            {
                 sql = " select SUBSTRING(a.F_Remark ,6,len(a.F_Remark)) as name, (select COUNT(1) from t_bus_workorder  WITH(NOLOCK) where F_IsDelete = 0 and  F_CreateTime between '" + stime + "'and  '"+ etime + "'  and F_PhoneType = a.F_Value ) as count,a.F_Value value from T_Sys_DictionaryValue  a  WITH(NOLOCK) where F_PrentId = '2498' and F_State = 0 and F_Value != '12349' and  F_Value != '12345' order by F_ValueId asc";
            }
            else
            {
                 sql = " select SUBSTRING(a.F_Remark ,6,len(a.F_Remark)) as name, (select COUNT(1) from t_bus_workorder  WITH(NOLOCK) where F_IsDelete = 0 and datediff(MONTH , F_CreateTime , getdate())= 0 and F_PhoneType = a.F_Value ) as count,a.F_Value value from T_Sys_DictionaryValue a  WITH(NOLOCK) where F_PrentId = '2498' and F_State = 0 and F_Value != '12349' and  F_Value != '12345' order by F_ValueId asc";
            }
           
            DataTable dthw = DbHelperSQL.Query(sql).Tables[0];
            dthw.Columns.Add("bili", typeof(string));//
            float  t = 0;
            foreach (DataRow it in dthw.Rows )
            {
                t+= Int32.Parse(it["count"].ToString());
            }
            foreach (DataRow it in dthw.Rows)
            {
               
               if (t >0)
                {
                    float v= Int32.Parse(it["count"].ToString());
                    it["bili"] = string.Format("{0:f2}%",v/t*100);
                }
               else
                {
                    it["bili"] = string.Format("{0:f2}%", 100 / dthw.Rows.Count  );
                }
            }
            return Success("加载成功", dthw);
        }

        /// <summary>
        /// 重大事件预警
        /// </summary>
        /// <returns></returns>
        public ActionResult Majorevents(string stime, string etime)
        {
            DateTime date = DateTime.Now;
            int type = 2;
            if (string .IsNullOrEmpty (stime))
            {
                type = type - 1;
                stime = new DateTime(date.Year, date.Month, 1).ToString ("yyyy-MM-dd HH:mm:ss");
            }
            if (string.IsNullOrEmpty(etime))
            {
                type = type - 1;
                etime = date.ToString("yyyy-MM-dd HH:mm:ss");
            }
            if (type==0)
            {
                if (RedisHelper.StringGet("InfoNewGetMajorevents") != null)
                {
                    if (GetLarge("InfoNewGetMajorevents"))
                    {
                        Task.Run(() =>
                    {
                        Dictionary<string, string> paras = new Dictionary<string, string>();
                        paras.Add("@sdate", stime);
                        paras.Add("@edate", etime);
                        var ob = DbHelperSQL.RunProcedure("P_Majorevents_1", paras, "Majorevents").Tables[0]; ;
                        RedisHelper.KeyDelete("InfoNewGetMajorevents");
                        RedisHelper.StringSet("InfoNewGetMajorevents", ob.ToJson(), new TimeSpan(0, 5, 0));
                        RedisHelper.KeyDelete("InfoNewGetMajorevents" + "time");
                        RedisHelper.StringSet("InfoNewGetMajorevents" + "time",
 DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), new TimeSpan(0, 5, 0));
                    });
                    }
                    return Content(RedisHelper.StringGet("InfoNewGetMajorevents").ToString());
                }
                else
                {
                    Dictionary<string, string> paras = new Dictionary<string, string>();
                    paras.Add("@sdate", stime);
                    paras.Add("@edate", etime);
                    var obj = DbHelperSQL.RunProcedure("P_Majorevents_1", paras, "Majorevents").Tables[0]; ;
                    RedisHelper.StringSet("InfoNewGetMajorevents", obj.ToJson(), new TimeSpan(0, 5, 0));
                    RedisHelper.StringSet("InfoNewGetMajorevents" + "time",
 DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), new TimeSpan(0, 5, 0));
                    return Content(obj.ToJson());
                }
            }
            else
            {
                Dictionary<string, string> paras = new Dictionary<string, string>();
                paras.Add("@sdate", stime);
                paras.Add("@edate", etime);
                var obj = DbHelperSQL.RunProcedure("P_Majorevents_1", paras, "Majorevents").Tables[0]; ;
                return Content(obj.ToJson());
            }
            
        }
        /// <summary>
        /// 获取当日回访情况
        /// </summary>
        /// <returns></returns>
        public ActionResult GetVisitCount()
        {
            if (RedisHelper.StringGet("InfoNewGetVisitCount") != null)
            {
                if (GetLarge("InfoNewGetVisitCount"))
                {
                    Task.Run(() =>
                {
                    Dictionary<string, string> paras = new Dictionary<string, string>();
                    var ob = DbHelperSQL.RunProcedure("P_VisitCount", paras, "VisitCount").Tables[0]; ;
                    RedisHelper.KeyDelete("InfoNewGetVisitCount");
                    RedisHelper.StringSet("InfoNewGetVisitCount", ob.ToJson(), new TimeSpan(0, 5, 0));
                    RedisHelper.KeyDelete("InfoNewGetVisitCount" + "time");
                    RedisHelper.StringSet("InfoNewGetVisitCount" + "time",
                     DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), new TimeSpan(0, 5, 0));
                });
                }
                return Content(RedisHelper.StringGet("InfoNewGetVisitCount").ToString());
            }
            else
            {
                Dictionary<string, string> paras = new Dictionary<string, string>();
                var obj = DbHelperSQL.RunProcedure("P_VisitCount", paras, "VisitCount").Tables[0]; ;
                RedisHelper.StringSet("InfoNewGetVisitCount", obj.ToJson(), new TimeSpan(0, 5, 0));
                RedisHelper.StringSet("InfoNewGetVisitCount" + "time",
                    DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), new TimeSpan(0, 5, 0));
                return Content(obj.ToJson());
            }
        }

        /// <summary>
        /// 获取问题分类区域数量
        /// </summary>
        /// <returns></returns>
        public ActionResult GetKeyArea(int pid = 38)
        {
            if (RedisHelper.StringGet("InfoNewGetKeyArea" + pid) != null)
            {
                if (GetLarge("InfoNewGetKeyArea"))
                {
                    Task.Run(() =>
                {
                    Dictionary<string, string> paras = new Dictionary<string, string>();
                    paras.Add("@pid", pid.ToString());
                    var ob = DbHelperSQL.RunProcedure("P_KeyArea", paras, "KeyArea").Tables[0]; ;
                    RedisHelper.KeyDelete("InfoNewGetKeyArea" + pid);
                    RedisHelper.StringSet("InfoNewGetKeyArea" + pid, ob.ToJson(), new TimeSpan(0, 5, 0));
                    RedisHelper.KeyDelete("InfoNewGetKeyArea" + pid + "time");
                    RedisHelper.StringSet("InfoNewGetKeyArea" + pid + "time",
 DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), new TimeSpan(0, 5, 0));
                });
                }
                return Content(RedisHelper.StringGet("InfoNewGetKeyArea" + pid).ToString());
            }
            else
            {
                Dictionary<string, string> paras = new Dictionary<string, string>();
                paras.Add("@pid", pid.ToString());
                var obj = DbHelperSQL.RunProcedure("P_KeyArea", paras, "KeyArea").Tables[0]; ;
                RedisHelper.StringSet("InfoNewGetKeyArea" + pid, obj.ToJson(), new TimeSpan(0, 5, 0));
                RedisHelper.StringSet("InfoNewGetKeyArea" + pid + "time",
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), new TimeSpan(0, 5, 0));
                return Content(obj.ToJson());
            }
        }
        /// <summary>
        /// 获取当日24小时类型受理量
        /// </summary>
        /// <returns></returns>
        public ActionResult GetTypeCount()
        {
            var date = DateTime.Now;
            string where = $" and F_IsDelete=0 and datediff(MONTH ,F_CreateTime ,getdate())=0 ";
            string sql = $"  select a.F_Value  type ,(select COUNT(1) from T_Bus_WorkOrder WITH(NOLOCK)   where F_InfoType = a.F_ValueId " + where + "  ) count from T_Sys_DictionaryValue a where F_PrentId = 37 and F_State = 0   order by F_ValueId asc  ";
            DataTable dthw = DbHelperSQL.Query(sql).Tables[0];
            dthw.Columns.Add("bili", typeof(string));//
            float t = 0;
            foreach (DataRow it in dthw.Rows)
            {
                t += Int32.Parse(it["count"].ToString());
            }
            foreach (DataRow it in dthw.Rows)
            {
                if (t > 0)
                {
                    float v = Int32.Parse(it["count"].ToString());
                    it["bili"] = string.Format("{0:f2}%", v / t * 100);
                }
                else
                {
                    it["bili"] = string.Format("{0:f2}%", 100 / dthw.Rows.Count);
                }
            }
            return Success("加载成功", dthw);

        }

        /// <summary>
        /// 获取区域列表
        /// </summary>
        /// <returns></returns>
        ////[Authority]
        public ActionResult GetAreaChildren(int parentid = 0)
        {
            DataTable dt = new BLL.T_Sys_AreaChildren().GetList(" isnull(F_PrentId,0)='" + parentid + "' and F_State=0 ").Tables[0];
            return Success("列表加载成功", dt);
        }
        /// <summary>
        /// 获取当日24小时类型受理量
        /// </summary>
        /// <returns></returns>
        public ActionResult GetAreaTypeCount(int areaid)
        {
            var date = DateTime.Now;
            string where = $" and F_IsDelete=0 and F_CreateTime>='{date.ToString("yyyy-MM-dd")} 00:00:00'";
            string sql = $"  select a.F_Value  type ,(select COUNT(1) from T_Bus_WorkOrder   where F_InfoType = a.F_ValueId and F_SourceArea='" + areaid + "' " + where + "  ) count from T_Sys_DictionaryValue a where F_PrentId = 37 and F_State = 0   order by F_ValueId asc  ";
            DataTable dthw = DbHelperSQL.Query(sql).Tables[0];
            dthw.Columns.Add("bili", typeof(string));//
            float t = 0;
            foreach (DataRow it in dthw.Rows)
            {
                t += Int32.Parse(it["count"].ToString());
            }
            foreach (DataRow it in dthw.Rows)
            {
                if (t > 0)
                {
                    float v = Int32.Parse(it["count"].ToString());
                    it["bili"] = string.Format("{0:f2}%", v / t * 100);
                }
                else
                {
                    it["bili"] = string.Format("{0:f2}%", 100 / dthw.Rows.Count);
                }
            }
            return Success("加载成功", dthw);

        }
        public class TownshipCount
        {
            public string Name { set; get; }
            public int  Township { set; get; }
            public Dictionary<string, int> InfoType { set; get; }
        }
        /// <summary>
        /// 获取当日
        /// </summary>
        /// <returns></returns>
        public ActionResult GetTownshipCount(int id)
        {
            var date = DateTime.Now;
            string where = $" and F_IsDelete=0 and F_CreateTime>='{date.ToString("yyyy-MM-dd")} 00:00:00'";
            string sql = $" select F_Township Township,F_InfoType type,count(1) count from dbo.T_Bus_WorkOrder WITH(NOLOCK) where F_IsDelete=0 and F_CreateTime>='{date.ToString("yyyy-MM-dd")} 00:00:00' group by F_Township,F_InfoType ";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            var keyAllList = new BLL.T_Sys_AreaChildren ().GetModelList(" F_State=0 ");
            List<Model.T_Sys_AreaChildren> keylist = new List<Model.T_Sys_AreaChildren>();
            if (id == 4965)
            {
                keylist = keyAllList.Where(p => p.F_PrentId == id||p .F_PrentId == 4974).ToList();
            }
            else
            {
                keylist = keyAllList.Where(p => p.F_PrentId == id).ToList();
            }
            string sql1 = $" select F_Township Township,F_InfoType type,count(1) count from dbo.T_Bus_WorkOrder WITH(NOLOCK) where F_IsDelete=0 and F_CreateTime>='{date.ToString("yyyy-MM-dd")} 00:00:00' group by F_Township,F_InfoType ";
            var modellist = new BLL.T_Sys_DictionaryValue().GetModelList("F_PrentId = 37 and F_State = 0");
            List<TownshipCount> list = new List<TownshipCount>();
            foreach (var p in keylist)
            {
                TownshipCount townshipCount = new TownshipCount();
                string name = ""; int type = 0;
                if (p.F_Code == "1")
                {
                    if (list.Exists(t => t.Name == "其他街道"))
                    {
                        type = 1;
                        townshipCount = list.Where(x => x.Name == "其他街道").FirstOrDefault();
                    }
                    else
                    {
                        townshipCount.Name = "其他街道";
                    }
                }
                else
                    townshipCount.Name = p.F_AreaName;
                if (type == 0)
                    townshipCount.InfoType = new Dictionary<string, int>();
                foreach (var it in modellist)
                {
                    if (type == 1)
                    {
                        if (townshipCount.InfoType.ContainsKey(it.F_Value))
                        {
                            townshipCount.InfoType[it.F_Value] = townshipCount.InfoType[it.F_Value] + dt.Select(" Township='" + p.F_AreaId + "' and type='" + it.F_ValueId + "'").Sum(x => x.Field<int>("count"));
                        }
                        else
                        {
                            townshipCount.InfoType.Add(it.F_Value, dt.Select(" Township='" + p.F_AreaId + "' and type='" + it.F_ValueId + "'").Sum(x => x.Field<int>("count")));
                        }
                    }
                    else
                    {
                        townshipCount.InfoType.Add(it.F_Value, dt.Select(" Township='" + p.F_AreaId + "' and type='" + it.F_ValueId + "'").Sum(x => x.Field<int>("count")));
                    }
                }
                if (type == 0)
                {
                    list.Add(townshipCount);
                }
            }
          
           
            return Success("加载成功", list);
        }
       public class AreaChildrenCount
        {
            public string Name { set; get; }
            public int  count { set; get; }
        }
        /// <summary>
        /// 获取乡镇工单数量
        /// </summary>
        /// <returns></returns>
        public ActionResult GetAreaChildrenCount(int areaid)
        {
            var date = DateTime.Now;
            string where = $" and F_IsDelete=0 and F_CreateTime>='{date.ToString("yyyy-MM-dd")} 00:00:00'";
            string key = areaid.ToString ();
            if (areaid == 4965)
            {
                key = "4965,4974";
            }
            string sql = $"  select a.F_AreaName Name,a.F_Code Code,(select COUNT(1) from T_Bus_WorkOrder   where F_Township = a.F_AreaId  " + where + "   ) count from dbo.T_Sys_AreaChildren a where F_PrentId  in("+ key + ") and F_State = 0   order by F_AreaId asc  ";
            DataTable dthw = DbHelperSQL.Query(sql).Tables[0];
            List<AreaChildrenCount> list = new List<AreaChildrenCount>();
         
            foreach (DataRow dr in dthw.Rows )
            {
             
                AreaChildrenCount areaChildrenCount = new AreaChildrenCount();
                int type = 0;
                if (dr["Code"].ToString() == "1")
                {
                    if (list.Exists(t => t.Name == "其他街道"))
                    {
                        type = 1;
                        areaChildrenCount = list.Where(x => x.Name == "其他街道").FirstOrDefault();
                    }
                    else
                    {
                        areaChildrenCount.Name = "其他街道";
                    }
                }
                else
                    areaChildrenCount.Name = dr["Name"].ToString();
                if (type ==1)
                {
                    areaChildrenCount.count += int.Parse(dr["count"].ToString());
                }
                else
                {
                    areaChildrenCount.count  =int .Parse ( dr["count"].ToString());
                    list.Add(areaChildrenCount);
                }
            }
            return Success("加载成功", list);
        }

        /// <summary>
        /// 获取当日渠道受理量
        /// </summary>
        /// <returns></returns>
        public ActionResult GetSourceCountBy()
        {
            if (RedisHelper.StringGet("InfoNewSourceCount" ) != null)
            {
                if (GetLarge("InfoNewSourceCount"))
                {
                    Task.Run(() =>
                {
                    Dictionary<string, string> paras = new Dictionary<string, string>();
                    var ob = DbHelperSQL.RunProcedure("P_SourceCount", paras, "SourceCount").Tables[0]; ;
                    RedisHelper.KeyDelete("InfoNewSourceCount");
                    RedisHelper.StringSet("InfoNewSourceCount", ob.ToJson(), new TimeSpan(0, 5, 0));
                    RedisHelper.KeyDelete("InfoNewSourceCount"  + "time");
                    RedisHelper.StringSet("InfoNewSourceCount" + "time",
                     DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), new TimeSpan(0, 5, 0));
                });
                  
                }
                return Content(RedisHelper.StringGet("InfoNewSourceCount").ToString());
            }
            else
            {
                Dictionary<string, string> paras = new Dictionary<string, string>();
                var obj = DbHelperSQL.RunProcedure("P_SourceCount", paras, "SourceCount").Tables[0]; ;
                RedisHelper.StringSet("InfoNewSourceCount", obj.ToJson(), new TimeSpan(0, 5, 0));
                RedisHelper.StringSet("InfoNewSourceCount" + "time",
                    DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), new TimeSpan(0, 5, 0));
                return Content(obj.ToJson());
            }
        }
        /// <summary>
        /// 获取当日话务量和接通量
        /// </summary>
        /// <returns></returns>
        public ActionResult GetTrafficCountByNow()
        {
            var date = DateTime.Now;

            string sql = $" and   BeginTime>='{date.ToString("yyyy-MM-dd")} 00:00:00' ";
            string sqlhw = " select COUNT(1) hwcount,    SUM(case  CallType when 0 then(case CallState when 1then 1 else 0 end) else 0 end) jtcount from T_Call_CallRecords WITH(NOLOCK) where  1 = 1  " + sql;
            DataTable dthw = DbHelperSQL.Query(sqlhw).Tables[0];
          
            var obj = new
            {
                hw = dthw
            };
            return Success("加载成功", obj);
        }
        /// <summary>
        /// 获取当日24小时呼入呼出
        /// </summary>
        /// <returns></returns>
        public ActionResult GetHWTypeCount24ByNow()
        {
            var date = DateTime.Now;
            var hr = date.Hour + 1;
            int[] hours = Enumerable.Range(0, hr).ToArray<int>();
            var obj = new
            {
                hours,
                hrcount = new List<int[]>(),
                hccount = new List<int[]>()
            };
            string sql = $" and   BeginTime>='{date.ToString("yyyy-MM-dd")} 00:00:00'  ";
            string sqlhw = " select datepart(hh, BeginTime ) hour,SUM(case CallType when 0 then 1 else 0 end ) hrcount ,SUM(case CallType when 1 then 1 else 0 end ) hccount  from T_Call_CallRecords where  1=1" + sql + " group by datepart(hh, BeginTime) order by datepart(hh, BeginTime)";
            DataTable dthw = DbHelperSQL.Query(sqlhw).Tables[0];

            int[] hrl = new int[hours.Length]; int[] hcl = new int[hours.Length];
            for (int i = 0; i < hours.Length; i++)
            {
                var drs = dthw.Select("hour='" + hours[i] + "'");
                int hrr = 0; int hc = 0;
                if (drs.Length > 0)
                {
                    hrr = (from DataRow dr in drs select dr.Field<int>("hrcount")).FirstOrDefault();
                    hc = (from DataRow dr in drs select dr.Field<int>("hccount")).FirstOrDefault();
                }
                hrl[i] = hrr;
                hcl[i] = hc;
            }
            obj.hrcount.Add(hrl);
            obj.hccount.Add(hcl);

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

        /// <summary>
        /// 呼叫中心话务数据统计表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetDistributionCount()
        {
            var date = DateTime.Now;
            string where = $" and F_IsDelete=0 and F_CreateTime>='{date.ToString("yyyy-MM-dd")} 00:00:00'";
           
            string sql = $" and   BeginTime>='{date.ToString("yyyy-MM-dd")} 00:00:00'";
            string sqlhw = " select SUM(case CallType when 0 then(case CallState when 1then 1 else 0 end ) else 0 end ) hrjtcount,SUM(case CallType when 1 then(case CallState when 1then 1 else 0 end) else 0 end) hcjtcount,SUM(case CallType when 0 then TalkLongTime else 0 end) / 60  hrtime,sUM(case CallType when 1 then TalkLongTime else 0 end) / 60  hctime,(case SUM(case CallType when 0 then(case CallState when 1then 1 else 0 end) else 0 end)when 0 then 0 else (SUM(case CallType when 0 then TalkLongTime else 0 end) / SUM(case CallType when 0 then(case CallState when 1then 1 else 0 end) else 0 end)) end ) pjhrtime,(case SUM(case CallType when 1 then(case CallState when 1then 1 else 0 end) else 0 end)when 0 then 0  else  (SUM(case CallType when 1 then TalkLongTime else 0 end) / SUM(case CallType when 1 then(case CallState when 1then 1 else 0 end) else 0 end)) end) pjhctime, SUM(case CallState when 0 then(case CallType when 0 then  (case  when UserCode = ''  then 1 when UserCode is null then 1  else 0 end)else 0 end)else 0 end) fqcount, SUM (case CallState when 0 then(case CallType when 0 then (case  when UserCode = ''  then 0 when UserCode is null then 0  else 1 end)else 0 end )else 0 end  ) hscount,  SUM( case datediff(hh, BeginTime, getdate()) when 0 then( case CallState when 0 then(case CallType when 0 then (case  when UserCode = ''  then 0 when UserCode is null then 0  else 1 end)else 0 end)else 0 end) else 0 end) sdhscount from T_Call_CallRecords  WITH(NOLOCK) where 1=1 " + sql;
            DataTable dthw = DbHelperSQL.Query(sqlhw).Tables[0];

           
            string sqltype = "select sum (case F_InfoSource when 6 then 1 else 0 end) dxcount , sum(case F_InfoSource when 5then 1 else 0 end) wxcount ,sum(case F_InfoSource when 3 then 1 else 0 end) wbcount ,sum(case F_InfoSource when 2527 then 1 else 0 end) apcount ,sum(case F_InfoSource when 2 then 1 else 0 end) xxcount from t_bus_workorder WITH(NOLOCK) where 1=1 " + where ;
            DataTable dttype = DbHelperSQL.Query(sqltype).Tables[0];

            var obj = new
            {
               hw= dthw,
               gd= dttype
            };
          


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

        /// <summary>
        /// 分时段话务量某日数据
        /// </summary>
        /// <returns></returns>
        public ActionResult GetDayparting()
        {
            if (RedisHelper.StringGet("InfoNewDayparting") != null)
            {
                
                    Task.Run(() =>
                {
                    Dictionary<string, string> paras = new Dictionary<string, string>();
                    var ob = DbHelperSQL.RunProcedure("P_Dayparting", paras, "Dayparting").Tables[0]; ;
                    RedisHelper.KeyDelete("InfoNewDayparting");
                    RedisHelper.StringSet("InfoNewDayparting", ob.ToJson(), new TimeSpan(0, 5, 0));
                   
                });
                
                return Content(RedisHelper.StringGet("InfoNewDayparting").ToString());
            }
            else
            {
                Dictionary<string, string> paras = new Dictionary<string, string>();
                var obj = DbHelperSQL.RunProcedure("P_Dayparting", paras, "Dayparting").Tables[0]; ;
                RedisHelper.StringSet("InfoNewDayparting", obj.ToJson(), new TimeSpan(0, 5, 0));
                return Content(obj.ToJson());
            }
        }
        #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;
        }

    }
}