Anyang_api/CallCenterApi/CallCenterAp.../CallCenterAp.../Controllers/WebChart/InfoNewContr...

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 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 dptype = RequestString.GetInt("dptype", 0);
            int kptype = RequestString.GetInt("kptype", 0);

            int handling = RequestString.GetInt("handling", 0);


            string  kpcontent = RequestString.GetQueryString("kpcontent");
            string latitude = RequestString.GetQueryString("latitude");
            string longitude = RequestString.GetQueryString("longitude");
            string strpageindex = RequestString.GetQueryString("page");
            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>=F_DealTime ";
                }
                else if (dealtype == 3)
                {//超期
                    sql += " and isnull(F_DealTime,'')<>'' and F_LimitTime<F_DealTime ";
                }
                else
                    sql += " and isnull(F_IsResult,0) = '" + dealtype + "' ";
            }
            if (strpagesize.Trim() != "")
            {
                pagesize = Convert.ToInt32(strpagesize);
            }
            string value = "";
            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)
                {
                    sql += "and F_IsResult=1";
                }
                else if (handling == 3)
                {
                    sql += "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 '%收到催办,请及时处理%' ";
                }

            }
            if (deptid > 0)
            {
                sql += "and F_MainDeptId='" + deptid + "'";
            }
            if (latitude.Trim ()!="")
            {
                sql += "and F_Latitude='" + latitude + "'";
            }
            if (longitude .Trim() != "")
            {
                sql += "and F_Longitude='" + longitude + "'";
            }
           if (sourceArea >0)
            {
                sql += "and F_SourceArea='" + sourceArea + "'";
            }
            if (type >0)
            {
                sql += "and F_InfoType='" + type + "'";
            }
            if (source >0)
            {
                sql += "and F_InfoSource='" + source + "'";
            }

            if (keyid>0)
            {
                sql += "and F_Key='"+ keyid + "' ";
            }
            if (kptype <=0)
            {
                if (timetype == 1)
                {
                    sql += "and datediff(DAY ,F_CreateTime ,getdate())=0 ";
                }
                else if (timetype == 2)
                {
                    sql += "and datediff(WEEK ,F_CreateTime ,getdate())=0 ";
                }
                else
                {
                    sql += "and datediff(MONTH ,F_CreateTime ,getdate())=0 ";
                }
            }
          
            if (hour > -1)
            {
                sql += " and  datepart(hh,F_CreateTime)=" + hour;
            }
            if (dptype>0)
            {
                if (deptid <= 0)
                {
                    return Error("请选择部门");
                }
                    if (dptype==1)
                {
                    //当月平均办理时长
                    sql += " F_IsResult=0 and datediff(MONTH , F_CreateTime , getdate())= 0  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 == 1)
                {
                    //上月平均办理时长
                    sql += " 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 += " 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 += "  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 += "  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 += "  ((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, 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)
                {
                    sql +=" and F_WorkOrderId in( F_Deptid="+ deptid + " and datediff(MONTH ,F_CreatTime ,getdate())=0    and F_Type = 0 and F_Evaluation = '"+ kpcontent + "') ";
                }
                else
                {
                    sql += " and F_WorkOrderId in( F_Deptid=" + deptid + " and datediff(MONTH ,F_CreatTime ,getdate())=0    and F_Type = 0 and F_Bonusitems = '" + kpcontent + "') ";
                }
            }
            string cols = "DISTINCT  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";
            int recordCount = 0;
            dt = BLL.PagerBLL.GetListPager(
                "T_Bus_WorkOrder a WITH(NOLOCK)"+ value,
                "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 WorkBackOrdrList()
        {
            DataTable dt = new DataTable();
            string sql = " and 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);
            }
            if (deptid>0)
            {
                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 ,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,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 ,v.F_CreateDeptId";
            int recordCount = 0;
            dt = BLL.PagerBLL.GetListPager(
                "T_Bus_WorkOrder a WITH(NOLOCK)" + value,
                "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>
        /// <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;

            if (deptid > 0)
                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 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 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 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(int pid = 38 )
        {
            var date = DateTime.Now;
            string sql = $" select F_Key keyid,count(1) count from dbo.T_Bus_WorkOrder where F_IsDelete=0 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();
            int total = 0;
            List<KeyCount> jo = new List<KeyCount>();
            if (RedisHelper.StringGet("InfoNewKeyCountValueId" + pid) != null)
            {
                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 = '" + 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));
                });
                ;
               
                 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 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));
                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", 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", 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=0,string areaid = "")
        {
            if (RedisHelper.StringGet("InfoNewMapByNow" + type+ areaid) != null)
            {
                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());
                    RedisHelper.KeyDelete("InfoNewMapByNow" + type + areaid);
                    RedisHelper.StringSet("InfoNewMapByNow" + type + areaid, ob.ToJson(), new TimeSpan(0, 5, 0));
                });
                return Content(RedisHelper.StringGet("InfoNewMapByNow" + type + areaid).ToString());
            }
            else
            {
                Dictionary<string, string> paras = new Dictionary<string, string>();
                var obj = DbHelperSQL.RunProcedure("P_MapByNow", paras, "MapByNow").Tables[0];
                RedisHelper.StringSet("InfoNewMapByNow" + type + areaid, obj.ToJson(), new TimeSpan(0, 5, 0));
                return Content(obj.ToJson());
            }
        }
        /// <summary>
        /// 获取办理情况三级
        /// </summary>
        /// <param name="date"></param>
        /// <param name="isdc"></param>
        /// <returns></returns>
        public ActionResult GetHandleCount( int type=0)
        {
            if (RedisHelper.StringGet("InfoNewHandleCount"+ type) != null)
            {
                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));
                });
                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));
                return Content(obj.ToJson());
            }
        }
        /// <summary>
        /// 获取平均办理时长
        /// </summary>
        /// <param name="date"></param>
        /// <param name="isdc"></param>
        /// <returns></returns>
        public ActionResult GetDeptDayTime()
        {
            if (RedisHelper.StringGet("InfoNewGetDeptDayTime") != null)
            {
                Task.Run(() =>
                {
                    Dictionary<string, string> paras = new Dictionary<string, string>();
                    var ob = DbHelperSQL.RunProcedure("P_DeptDayTime", paras, "DeptDayTime").Tables[0]; ;
                    RedisHelper.KeyDelete("InfoNewGetDeptDayTime");
                    RedisHelper.StringSet("InfoNewGetDeptDayTime", ob.ToJson(), new TimeSpan(0, 5, 0));
                });
                return Content(RedisHelper.StringGet("InfoNewGetDeptDayTime").ToString());
            }
            else
            {
                Dictionary<string, string> paras = new Dictionary<string, string>();
                var obj = DbHelperSQL.RunProcedure("P_DeptDayTime", paras, "DeptDayTime").Tables[0]; ;
                RedisHelper.StringSet("InfoNewGetDeptDayTime", obj.ToJson(), new TimeSpan(0, 5, 0));
                return Content(obj.ToJson());
            }
           
        }


        /// <summary>
        /// 获取平台办理情况分类
        /// </summary>
        /// <returns></returns>
        public ActionResult GetHandling()
        {
            if (RedisHelper.StringGet("InfoNewHandling") != null)
            {
                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));
                });
                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));
                return Content(obj.ToJson());
            }
        }

        /// <summary>
        /// 获取绩效
        /// </summary>
        /// <param name="date"></param>
        /// <param name="isdc"></param>
        /// <returns></returns>
        public ActionResult GetDeptDeptAchievements()
        {
            if (RedisHelper.StringGet("InfoNewGetDeptAchievements") != null)
            {
                Task.Run(() =>
                {
                    Dictionary<string, string> paras = new Dictionary<string, string>();
                    var ob = DbHelperSQL.RunProcedure("P_DeptAchievements", paras, "DeptAchievements").Tables[0]; ;
                    RedisHelper.KeyDelete("InfoNewGetDeptAchievements");
                    RedisHelper.StringSet("InfoNewGetDeptAchievements", ob.ToJson(), new TimeSpan(0, 5, 0));
                });
                return Content(RedisHelper.StringGet("InfoNewGetDeptAchievements").ToString());
            }
            else
            {
                Dictionary<string, string> paras = new Dictionary<string, string>();
                var obj = DbHelperSQL.RunProcedure("P_DeptAchievements", paras, "DeptAchievements").Tables[0]; ;
                RedisHelper.StringSet("InfoNewGetDeptAchievements", 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()
        {
            var date = DateTime.Now;
            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>='{date.ToString("yyyy-MM-dd")} 00:00:00') Count from dbo.T_Sys_DictionaryValue where F_PrentId=36 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 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 办理情况
        /// <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
    }
}