HeBi12345_API/CallCenterApi/CallCenterAp.../CallCenterAp.../Controllers/report/WorkOrderRep...

using CallCenter.Utility;
using CallCenter.Utility.Linq;

using CallCenter.Utility.Time;
using CallCenterApi.Common;
using CallCenterApi.DB;
using CallCenterApi.Interface.Controllers.Base;
using CallCenterApi.Interface.Models.Dto;
using CallCenterApi.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;
using static CallCenter.Utility.NPOIHelper;

namespace CallCenterApi.Interface.Controllers.report
{
  //  [Authority]
    public class WorkOrderReportController : BaseController
    {
        private readonly BLL.T_Sys_Department departmentBLL = new BLL.T_Sys_Department();
        private readonly BLL.T_Bus_WorkOrder workOrderBLL = new BLL.T_Bus_WorkOrder();
        private readonly BLL.T_Bus_AssignedInfo assignedInfoBLL = new BLL.T_Bus_AssignedInfo();
        private readonly BLL.T_Bus_Feedback feedbackBLL = new BLL.T_Bus_Feedback();
        private readonly BLL.T_Sys_DictionaryValue dictValueBLL = new BLL.T_Sys_DictionaryValue();
        private readonly BLL.T_Sys_Area areaBLL = new BLL.T_Sys_Area();
        private readonly BLL.T_Bus_VisitResult visitResultBLL = new BLL.T_Bus_VisitResult();
        private readonly BLL.T_Bus_DelayTime delayTimeBLL = new BLL.T_Bus_DelayTime();
        private readonly BLL.T_Sys_UserAccount userAccount = new BLL.T_Sys_UserAccount();
        private readonly BLL.T_Bus_SubmitSuper submitsuperBLL = new BLL.T_Bus_SubmitSuper();

        /// <summary>
        /// 处理数量统计
        /// </summary>
        /// <returns></returns>
        public ActionResult GetDeptCount(DateTime? start, DateTime? end, bool isExport = false)
        {
            if (start == null)
            {
                start = DateTime.Now;
                start = new DateTime(start.Value.Year, start.Value.Month, 1);
            }
            if (end == null)
            {
                end = DateTime.Now;
            }
          
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
            paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
            var obj = DbHelperSQL.RunProcedure("P_DeptDealReport", paras, "DeptDealReport");
            if (isExport)
            {
                var cols = new string[] { "单位名称", "数量" };
                new NPOIHelper().ExportToExcel($"处理数量统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
                return Success("导出excel");
            } 
            return Success("成功", obj);

        }

        /// <summary>
        ///  处理效率统计
        /// </summary>
        /// <returns></returns> 
        public ActionResult GetDeptEfficiency1(DateTime? start, DateTime? end, bool isExport = false)
        {
            if (start == null)
            {
                start = DateTime.Now;
                start = new DateTime(start.Value.Year, start.Value.Month, 1);
            }
            if (end == null)
            {
                end = DateTime.Now;
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
            paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
            var obj = DbHelperSQL.RunProcedure("P_DeptEfficiencyReport", paras, "DeptEfficiencyReport");
            if (isExport)
            {
                string[] cols = new string[] { "单位名称", "处理总量", "及时量", "超时量", "处理及时率" };
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcel("处理效率统计报表", obj.Tables[0], cols) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            return Success("成功", obj);
        }
        public ActionResult GetDeptEfficiency(DateTime? start, DateTime? end, bool isExport = false,string source="")
        {
            if (start == null)
            {
                start = DateTime.Now;
                start = new DateTime(start.Value.Year, start.Value.Month, 1);
            }
            if (end == null)
            {
                end = DateTime.Now;
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
            paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
            paras.Add("@source", source);
            var obj = DbHelperSQL.RunProcedure("P_DeptEfficiencyReport_1", paras, "DeptEfficiencyReport");
            var newSet = new DataSet();
            if (obj != null && obj.Tables[0] != null && obj.Tables[0].Rows.Count > 0)
            {
                obj.Tables[0].Columns.Add("rate", typeof(decimal));
                for (int i = 0; i < obj.Tables[0].Rows.Count; i++)
                {
                    int completedcount = 0, timelycount = 0, timeoutcount = 0; double timelyrate = 0;
                    int.TryParse(obj.Tables[0].Rows[i]["completedcount"].ToString(), out completedcount);
                    int.TryParse(obj.Tables[0].Rows[i]["timelycount"].ToString(), out timelycount);
                    int.TryParse(obj.Tables[0].Rows[i]["timeoutcount"].ToString(), out timeoutcount);
                    timeoutcount = completedcount - timelycount;
                    obj.Tables[0].Rows[i]["timeoutcount"] = timeoutcount;
                    
                    if (completedcount > 0)
                    {
                        timelyrate = double.Parse((timelycount * 100).ToString()) / completedcount;
                        obj.Tables[0].Rows[i]["timelyrate"] = timelyrate.ToString("f") + "%";
                        obj.Tables[0].Rows[i]["rate"] = timelyrate;
                    }
                }
                obj.Tables[0].DefaultView.Sort = "rate desc";
                newSet.Tables.Add(obj.Tables[0].DefaultView.ToTable());
                newSet.Tables[0].Columns.Remove("rate");
                //obj.Tables[0] = obj.Tables[0].DefaultView.ToTable();
            }
            if (isExport)
            {
                string[] cols = new string[] { "单位名称", "处理总量", "及时量", "超时量", "处理及时率" };
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcel("处理效率统计报表", newSet.Tables[0], cols) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            return Success("成功", newSet);
        }
  
        /// <summary>
        /// 市民满意度评价
        /// </summary>
        /// <returns></returns>
        public ActionResult GetDeptSatisfied(DateTime? start, DateTime? end,int source=0, bool isExport = false)
        {
            if (start == null)
            {
                start = DateTime.Now;
                start = new DateTime(start.Value.Year, start.Value.Month, 1);
            }
            if (end == null)
            {
                end = DateTime.Now;
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
            paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
            paras.Add("@source", source.ToString());
            var obj = DbHelperSQL.RunProcedure("P_DeptSatisfied", paras, "DeptSatisfied");

            if (isExport)
            {
                string[] cols = new string[] { "单位名称", "受理总量", "处理总量", "满意总量", "基本满意总量", "不满意总量", "满意度" };
                new NPOIHelper().ExportToExcel($"满意度统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
                return Success("导出excel");
            }
            var ob = new
            {
                state = "success",
                message = "成功",
                DeptSatisfiedReport = obj ,
            };
            return Success("成功", ob );
         
        }

        /// <summary>
        /// 市民满意度评价台账
        /// </summary>
        /// <returns></returns>
        public ActionResult GetDeptSatisfiedList(string start, string end,int deptId=0,int myd=-1, int page = 1, int pagesize = 10, bool isExport = false)
        {
            string sql = " and F_IsDelete=0 and F_WorkState=9 and F_MainDeptId is not null and F_MainDeptId !='' and F_DealDeptId is not null and F_DealDeptId !=''";
            if (!string.IsNullOrEmpty(start)&&start.Trim() != "" && start != "undefined")
            {
                sql += " and F_CreateTime>='" + start + "' ";
            }
            if (!string.IsNullOrEmpty(end) && end.Trim() != "" && end != "undefined")
            {
                sql += " and F_CreateTime<='" + end + "' ";
            }

            if (deptId > 0)
            {
                sql += " and F_MainDeptId= " + deptId;
            }
            if (myd > -1)
            {
                sql += " and MYD= " + myd;
            }

            if (isExport)
            {
                var dtdc = DbHelperSQL.Query("select F_WorkOrderId,F_ComTitle,F_ComContent,dbo.GetDeptNames(F_MainDeptId) as DeptName,dbo.GetUserName(F_CreateUser) as UserName,F_LimitTime,F_Result,MYD from (select *,(select top 1 (case F_IsSatisfie when 1 then '满意' when 0 then '不满意' else '基本满意' end) F_Satisfie from T_Bus_VisitResult WITH(NOLOCK) where F_IsDelete = 0 and F_WorkOrderId = a.F_WorkOrderId order by F_Id desc) MYD from T_Bus_WorkOrder a WITH(NOLOCK)) b where 1=1 " + sql ).Tables[0];

                string[] dccols = new string[] { "工单编号", "述求标题", "工单内容", "承办单位", "受理人",  "时限", "办理结果", "满意度" };
                new NPOIHelper().ExportToExcel($"满意度台账报表{DateTime.Now.ToString("yyyyMMddHHmmssfff")}", dtdc, dccols);
                return Success("导出excel");
            }

            string cols = "  F_WorkOrderId,F_ComTitle,F_ComContent,dbo.GetDeptNames(F_MainDeptId) as DeptName,dbo.GetUserName(F_CreateUser) as UserName,F_WorkState,F_LimitTime,F_Result,MYD";

            int recordCount = 0;
            DataTable dt = BLL.PagerBLL.GetListPager(
                "(select *,(select top 1 F_IsSatisfie from T_Bus_VisitResult WITH(NOLOCK) where F_IsDelete = 0 and F_WorkOrderId = a.F_WorkOrderId order by F_Id desc) MYD from T_Bus_WorkOrder a WITH(NOLOCK)) b",
                "F_Id",
                cols,
                sql,
                "ORDER BY F_CreateTime DESC",
                pagesize,
                page,
                true,
                out recordCount);

            var obj = new
            {
                state = "success",
                message = "成功",
                rows = dt,
                total = recordCount
            };
            return Content(obj.ToJson());

        }


        /// <summary>
        /// 县区满意度评价
        /// </summary>
        /// <returns></returns>
        #region 20191022 调整多主办单位需调整
        public ActionResult GetAreaSatisfied(DateTime? start, DateTime? end, bool isExport = false)
        {
            if (start == null)
            {
                start = DateTime.Now;
                start = new DateTime(start.Value.Year, start.Value.Month, 1);
            }
            if (end == null)
            {
                end = DateTime.Now;
            }

            //var sql = "F_IsDelete=0 and F_IsSure=1 and F_State=1 and isnull(F_FeedbackTime,'')<>'' "
            //    + "and datediff(day, F_FeedbackTime, @sdate) <= 0 and datediff(day, F_FeedbackTime, @edate) >= 0"
            //    + "and F_WorkOrderId in (select F_WorkOrderId from T_Bus_WorkOrder where F_WorkState = 9)";

            //var assignedInfoList = assignedInfoBLL.GetModelList(sql);

            //var workorderList = workOrderBLL.GetModelList("");

            //var deptList = departmentBLL.GetModelList(" F_State=0 AND F_PartentId=0 ");
            //foreach (var item in deptList)
            //{
            //    var deptlist2 = departmentBLL.GetModelList(" F_State=0 AND F_PartentId in (select F_DeptId FROM T_Sys_Department where F_State=0 AND F_PartentId=" + item.F_DeptId + ") ");
            //}

            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
            paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
            var obj = DbHelperSQL.RunProcedure("P_AreaSatisfiedReport", paras, "AreaSatisfiedReport");

            if (isExport)
            {
                string[] cols = new string[] { "县/区", "处理总量", "满意总量", "不满意总量", "满意度" };
                new NPOIHelper().ExportToExcel($"满意度统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
                return Success("导出excel");
            }
            return Success("成功", obj);
        }
        //public ActionResult GetAreaSatisfied(DateTime? start, DateTime? end, bool isExport = false)
        //{
        //    if (start == null)
        //    {
        //        start = DateTime.Now;
        //        start = new DateTime(start.Value.Year, start.Value.Month, 1);
        //    }
        //    if (end == null)
        //    {
        //        end = DateTime.Now;
        //    }
        //    //string sql = $"select * from dbo.GetDeptSatisfied('{start.Value.ToString("yyyy-MM-dd")}','{end.Value.ToString("yyyy-MM-dd")}') ORDER BY rate DESC";
        //    //var dtall = DbHelperSQL.Query(sql).Tables[0];
        //    Dictionary<string, string> paras = new Dictionary<string, string>();
        //    paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
        //    paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
        //    var obj = DbHelperSQL.RunProcedure("P_AreaSatisfiedReport", paras, "AreaSatisfiedReport");

        //    if (isExport)
        //    {
        //        string[] cols = new string[] { "县/区", "处理总量", "满意总量", "不满意总量", "满意度" };
        //        new NPOIHelper().ExportToExcel($"满意度统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
        //        return Success("导出excel");
        //    }
        //    return Success("成功", obj);
        //    //int recordCount = 0;
        //    //var dt = BLL.PagerBLL.GetListPager(
        //    //   "("+sql+") as v",
        //    //    "deptname",
        //    //    "*",
        //    //    "",
        //    //    "ORDER BY rate DESC",
        //    //    pageSize,
        //    //    pageIndex,
        //    //    true,
        //    //    out recordCount);

        //    //var obj = new
        //    //{
        //    //    state = "success",
        //    //    message = "成功",
        //    //    rows = dt,
        //    //    total = recordCount
        //    //};

        //    //return Content(obj.ToJson());
        //}
        #endregion
  

        /// <summary>
        /// 单位绩效考核
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetDeptAssessment(DateTime? start, DateTime? end, bool isExport = false,string source="")
        {
            StringBuilder sb1 = new StringBuilder();
            StringBuilder sb2 = new StringBuilder();
            if (start == null)
            {
                start = DateTime.Now;
                start = new DateTime(start.Value.Year, start.Value.Month, 1);
            }
            if (end == null)
            {
                end = DateTime.Now;
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
            paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
            paras.Add("@source", source);
            var obj = DbHelperSQL.RunProcedure("P_DeptAssessmentReport", paras, "DeptAssessmentReport");
            if (isExport)
            {
                string[] cols = new string[] { "单位名称", "已办总量", "及时量", "超时量", "超时时长(小时)", "延时量", "处理时长(小时)", "平均处理时长(小时)", "最大处理时长(小时)", "回访量", "市民满意", "市民不满意" };
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcel("单位绩效考核报表", obj.Tables[0], cols) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            return Success("成功", obj);
        }

       

        /// <summary>
        /// 坐席工作统计
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetUserAccountAssessment1(DateTime? start, DateTime? end, bool isExport = false)
        {
            StringBuilder sb1 = new StringBuilder();
            StringBuilder sb2 = new StringBuilder();
            if (start == null)
            {
                start = DateTime.Now;
                start = new DateTime(start.Value.Year, start.Value.Month, 1);
            }
            if (end == null)
            {
                end = DateTime.Now;
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd HH:mm:ss"));
            paras.Add("@edate", end.Value.ToString("yyyy-MM-dd HH:mm:ss"));
            var obj = DbHelperSQL.RunProcedure("P_UserAssessmentReport", paras, "UserAssessmentReport");
            if (isExport)
            {
                string[] cols = new string[] { "姓名", "接听量", "通话成单量", "手动录单量", "当即办理量", "网络转办量", "回访量"};
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcel("坐席工作统计报表", obj.Tables[0], cols) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            return Success("成功", obj);
        }
        public ActionResult GetUserAccountAssessment(DateTime? start, DateTime? end, bool isExport = false,string source="",string  name="")
        {
            StringBuilder sb1 = new StringBuilder();
            StringBuilder sb2 = new StringBuilder();
            if (start == null)
            {
                start = DateTime.Now;
                start = new DateTime(start.Value.Year, start.Value.Month, 1);
            }
            if (end == null)
            {
                end = DateTime.Now;
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd HH:mm:ss"));
            paras.Add("@edate", end.Value.ToString("yyyy-MM-dd HH:mm:ss"));
            paras.Add("@source", source);
            paras.Add("@name", name);
            
            var obj = DbHelperSQL.RunProcedure("P_UserAssessmentReport_1", paras, "UserAssessmentReport");
            if (isExport)
            {
                string[] cols = new string[] { "姓名", "接听量", "通话成单量", "手动录单量", "当即办理量", "网络转办量", "回访量","呼出量" ,"回拨量"};
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcel("坐席工作统计报表", obj.Tables[0], cols) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            return Success("成功", obj);
        }

        public ActionResult GetUserReturn(DateTime? start, DateTime? end, bool isExport = false)
        {
            StringBuilder sb1 = new StringBuilder();
            StringBuilder sb2 = new StringBuilder();
            if (start == null)
            {
                start = DateTime.Now;
                start = new DateTime(start.Value.Year, start.Value.Month, 1);
            }
            if (end == null)
            {
                end = DateTime.Now;
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd HH:mm:ss"));
            paras.Add("@edate", end.Value.ToString("yyyy-MM-dd HH:mm:ss"));
            var obj = DbHelperSQL.RunProcedure("P_UserReturn", paras, "UserReturn");
            if (isExport)
            {
                string[] cols = new string[] { "姓名", "工单量", "回访量", "未回访量", "回访率" };
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcel("坐席回访统计报表", obj.Tables[0], cols) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            return Success("成功", obj);
        }
     
        public ActionResult WorkOrdrList(int isdc=0)
        {
            DataTable dt = new DataTable();
            string sql = " and F_IsDelete=0 ";
         
            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 timetype = RequestString.GetInt("timetype", 0);
            int ishf = RequestString.GetInt("ishf", 0);
            int area = RequestString.GetInt("area", 0);
            int state = RequestString.GetInt("state", -1);
            int type = RequestString.GetInt("type", 0);
            int source = RequestString.GetInt("source", 0);
            string mobile = RequestString.GetQueryString("mobile");
            int dbdb = RequestString.GetInt("dbdb", 0);
            int db = RequestString.GetInt("db", 0);
            string dbusercode = RequestString.GetQueryString("dbusercode");
            string strpageindex = RequestString.GetQueryString("page");
            string strpagesize = RequestString.GetQueryString("pagesize");
            string usercode = RequestString.GetQueryString("usercode");
            string strstarttime = HttpUtility.UrlDecode(RequestString.GetQueryString("starttime"));
            string strendtime = HttpUtility.UrlDecode(RequestString.GetQueryString("endtime"));
            int isRelease = RequestString.GetInt("isRelease", 0);
            int name= RequestString.GetInt("name", 0);
            int handling = RequestString.GetInt("handling", 0);
            int pageindex = 1;
            int pagesize = 10;
            if (strpageindex.Trim() != "")
            {
                pageindex = Convert.ToInt32(strpageindex);
            }
            if (strpagesize.Trim() != "")
            {
                pagesize = Convert.ToInt32(strpagesize);
            }
            if (!string .IsNullOrEmpty (usercode))
            {
                sql += " and F_CreateUser='"+ usercode + "' ";
            }
            string value = "";
            if (string .IsNullOrEmpty (strstarttime)&& string.IsNullOrEmpty(strendtime))
            {
              
                if (db > 0 || dbdb>0)
                {
                   
                        DateTime start;
                        start = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);

                     if (dbdb==5)
                    {
                        value += "and datediff(SS ,F_CreateTime,'" + start.ToString("yyyy-MM-dd HH:mm:ss") + "')<=0 ";
                        value += " and  datediff(SS ,F_CreateTime,'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')>=0";
                    }
                     else
                    {
                        value += "and datediff(SS ,SupervisorTime,'" + start.ToString("yyyy-MM-dd HH:mm:ss") + "')<=0 ";
                        value += " and  datediff(SS ,SupervisorTime,'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')>=0";

                    }
                      
                }
                else
                {

                    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 if (timetype == 4)
                    {
                    }
                    else
                    {
                        sql += " and datediff(MONTH ,a.F_CreateTime ,getdate())=0 ";
                    }

                }
            }
            else
            {
                if (db>0 || dbdb > 0)
                {
                    
                    
                    if (dbdb == 5)
                    {
                        if (!string.IsNullOrEmpty(strstarttime))
                        {
                            value += "and datediff(SS ,F_CreateTime,'" + strstarttime + "')<=0 ";
                        }
                        if (!string.IsNullOrEmpty(strendtime))
                        {
                            value += " and  datediff(SS ,F_CreateTime,'" + strendtime + "')>=0";
                        }

                    }
                    else
                    {
                        if (!string.IsNullOrEmpty(strstarttime))
                        {
                            value += "and datediff(SS ,SupervisorTime,'" + strstarttime + "')<=0 ";
                        }
                        if (!string.IsNullOrEmpty(strendtime))
                        {
                            value += " and  datediff(SS ,SupervisorTime,'" + strendtime + "')>=0";
                        }

                    }
                }
                else
                {
                    if (!string.IsNullOrEmpty(strstarttime))
                    {
                        sql += "and datediff(SS ,F_CreateTime,'" + strstarttime + "')<=0 ";
                    }
                    if (!string.IsNullOrEmpty(strendtime))
                    {
                        sql += " and  datediff(SS ,F_CreateTime,'" + strendtime + "')>=0";
                    }

                }
               
            }
            if (dbdb>0)
            {
                sql += " and  F_WorkOrderID in(select F_WorkOrderID from T_Bus_RemindRecord WITH(NOLOCK) where F_Type= 1 and F_IsDelete = 0  and F_State = 0 and (Supervisor ='' or  Supervisor is null )"+ value + ") and F_WorkState  != '9'";
            }
            if(db>0)
            {
               
                if (!string .IsNullOrEmpty (dbusercode))
                {
                    value += "and Supervisor='" + dbusercode + "'";
                }
                if (db==1)
                {
                    sql += " and  a.F_WorkOrderID in(select F_WorkOrderID from T_Bus_RemindRecord WITH(NOLOCK) where F_Type= 1 and F_IsDelete = 0  " + value + " and  Supervisor !='' and Supervisor is not null )";
                }
                else if (db == 2)
                {
                    sql += " and  a.F_WorkOrderID in(select F_WorkOrderID from T_Bus_RemindRecord WITH(NOLOCK) where F_Type= 1 and F_State = 1 " + value + " )";
                }
                else if (db == 3)
                {
                    sql += " and a.F_WorkOrderID in(select F_WorkOrderID from T_Bus_RemindRecord WITH(NOLOCK) where F_Type= 1 and F_State = 2 " + value + " ) ";
                }
                else if (db == 4)
                {
                    sql += " and a.F_WorkOrderID in(select F_WorkOrderID from T_Bus_RemindRecord WITH(NOLOCK) where F_Type= 1  " + value + " ) and F_WorkState  = '9'";
                }
            }

             if (!string .IsNullOrEmpty (mobile ))
            {

                sql += "and a.F_CusPhone =  '" + mobile + "'";
            }
            if (area  > 0)
            {
                sql += "and a.F_SourceArea='" + area + "'";
            }
            if (state>-1)
            {
                sql += "and a.F_WorkState='" + state + "'";
            }
            if (type > 0)
            {
                sql += "and a.F_InfoType='" + type + "'";
            }
            if (source > 0)
            {
                sql += "and a.F_InfoSource='" + source + "'";
            }
            if (isRelease>0)
            {
                if (isRelease==2)
                sql += " and (F_IsRelease =2  or F_IsRelease  is null or F_IsRelease  ='')";
                else
                    sql += "and F_IsRelease ='"+ isRelease + "'";
            }
            if (ishf>0)
            {
                
                if (string.IsNullOrEmpty(strstarttime) && string.IsNullOrEmpty(strendtime))
                {
                    value += " and datediff(MONTH ,a.F_CreateTime ,getdate())=0 ";
                }
                else
                {
                    if (!string.IsNullOrEmpty(strstarttime))
                    {
                        value += "and datediff(SS ,F_CreateTime,'" + strstarttime + "')<=0 ";
                    }
                }
                if (ishf==1)
                {
                    sql += "and  F_WorkOrderId  in (select F_WorkOrderId from  T_Bus_VisitResult WITH(NOLOCK) " +
                        " where F_IsDelete =0 "+ value + "  )   ";
              
                }
                else
                {
                    sql += "and  F_WorkOrderId not  in (select F_WorkOrderId from  T_Bus_VisitResult WITH(NOLOCK) " +
                           "where  F_IsDelete =0 " + value + "  and F_IsSms !=1  )   ";
                }
            }
            if (handling>0)
            {
                if (deptid <= 0)
                    return Error("请选择部门");
                if (string.IsNullOrEmpty(strstarttime) && string.IsNullOrEmpty(strendtime))
                {
                    sql += " and datediff(MONTH ,a.F_CreateTime ,getdate())=0 ";
                }
                sql += "F_MainDeptId='" + deptid + "' and F_IsResult=0";
                switch (handling)
                {
                    case 2:
                        break;
                }
                   

            }
            string cols = "";
            string order = "ORDER BY a.F_CreateTime DESC";
            if (name >0)
            {
                cols = "distinct F_CusName";
                order = "";
            }
            else if (db>0)
            {
                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,dbo.GetUserName((select top 1 Supervisor from T_Bus_RemindRecord where F_WorkOrderId=a.F_WorkOrderId order by F_Id desc)) as Supervisor";
            }
            else 
            {
                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";
            }
            if (isdc > 0)
            {
                var top = " "; var orderby = " order by F_CreateTime desc";
                if (sql == " and F_IsDelete=0 ")
                {
                    top = " top 1000 "; orderby += " desc ";
                }
                if (db == 1)
                {
                    var dtdc = DbHelperSQL.Query(" select  ROW_NUMBER() OVER(ORDER BY F_CreateTime desc)  编号, F_WorkOrderId 事项单号,  dbo.GetDeptNames(F_MainDeptId ) 承办单位, "
                       + "(case F_WorkState when 0 then '新工单' when 1 then '待交办' when 2 then  '待查收'   when 3 then  '退回审核中' when 4 then  '办理中' when 5 then  '延时审核中' when 6 then  '已办理' when 7 then  '已审核' when 8 then  '重办中' when 9 then  '已完结' when 11 then  '重办待交办' when 12 then  '重办驳回' else '办理中' end)  督办情况,(case (select top 1 F_Mode  from T_Bus_RemindRecord  where  F_WorkOrderId = a.F_WorkOrderId order by F_Id desc) when 0 then '现场' when 1 then '电话'else '未知' end  ) 督办方式"
                      + " from T_Bus_WorkOrder a WITH(NOLOCK) where 1=1 " + sql + order).Tables[0];

                    var msg = new NPOIHelper().ExportToExcel("工单列表", dtdc);
                    if (msg == "")
                    {
                        return Success("导出成功");
                    }
                    else
                    {
                        return Error("导出失败");
                    }


                }
                else
                {
                    var dtdc = DbHelperSQL.Query(" select  ROW_NUMBER() OVER(ORDER BY F_CreateTime desc)  序号,F_CreateTime 日期, F_WorkOrderId 工单号,F_ComTitle 诉求标题,F_ComContent 诉求内容,dbo.GetDictionaryName(F_Key) 反映类别, "
                   + "  dbo.GetDeptNames(F_MainDeptId ) 主办单位, "
                   + " (select top 1 F_Result  from T_Bus_Feedback WITH(NOLOCK) where F_State=1 and F_Type!=3 and F_IsDelete=0 and  F_WorkOrderId =a.F_WorkOrderId order by F_Id desc) 办理结果, "
                   + " F_CusName 来电人姓名 ,F_CusPhone  来电人手机号 "
                   + " from T_Bus_WorkOrder a WITH(NOLOCK) where 1=1 " + sql + orderby).Tables[0];

                    var msg = new NPOIHelper().ExportToExcel("工单列表", dtdc);
                    if (msg == "")
                    {
                        return Success("导出成功");
                    }
                    else
                    {
                        return Error("导出失败");
                    }
                }
               
            }
            else
            {
                int recordCount = 0;
                dt = BLL.PagerBLL.GetListPager(
                    "T_Bus_WorkOrder a WITH(NOLOCK)",
                    "a.F_WorkOrderId",
                    cols,
                    sql,
                    order,
                    pagesize,
                    pageindex,
                    true,
                    out recordCount);
                var obj = new
                {
                    state = "success",
                    message = "成功",
                    rows = dt,
                    total = recordCount
                };
                return Content(obj.ToJson());
            }
           
        }
       
        public ActionResult IncomingCalls (string date,int isdc=0)
        {
            if (string.IsNullOrEmpty(date))
                date = DateTime.Now.ToString("yyyy-MM-dd");
            var modelcalllist = new BLL
                 .T_Call_CallRecords().GetModelList(" CallType =0  and DATEDIFF(DAY, BeginTime, '"+ date + "') = 0");
            var userlist = new BLL.T_Sys_UserAccount().GetModelList(" F_SeatFlag =1");



            int ldcount = 0;int jtcount = 0; int newldcount = 0; int newjtcount = 0;
            List<Incoming> incomings = new List<Incoming>();
           
            //incoming.time = "00:00-08:00";
            //incoming.IncomingCount = modelcalllist.Where(x => x.BeginTime.Value.Hour >= 0 && x.BeginTime.Value.Hour <= 8
            //).Count();
            //ldcount += incoming.IncomingCount;
            //incoming.Oncapacity = modelcalllist.Where(x => x.BeginTime.Value.Hour >= 0 && x.BeginTime.Value.Hour <= 8
            //&&x.CallState ==1 ).Count();
            //jtcount += incoming.Oncapacity;
            //var modelcalllists = modelcalllist.Where(x => x.UserCode != null && x.UserCode != "" && x.BeginTime.Value.Hour >= 0 && x.BeginTime.Value.Hour <=8).Select(x => x.UserCode).Distinct
            //    ();
          
            //if (modelcalllists!=null&& modelcalllists.Count() >0)
            //{
            //    incoming.seatsCount = modelcalllists.Count();
            //    foreach (var it in modelcalllists)
            //    {
            //        var  user = userlist.Where(x => x.F_UserCode == it);
            //        if (user != null && user.Count() > 0)
            //            incoming.seats += "," + user.FirstOrDefault().F_UserName;
            //        else
            //            incoming.seats += "," + it;
            //    }
            //    if (!string .IsNullOrEmpty(incoming.seats))
            //        incoming.seats = incoming.seats.TrimStart(',');
            //}
            //else
            //{
            //    incoming.seatsCount =0;
            //    incoming.seats = "";
            //}
            //incomings.Add(incoming);
            int v = 24;
            if (DateTime .Parse (date ).Year ==DateTime .Now .Year &&
                DateTime.Parse(date).Month  == DateTime.Now.Month && DateTime.Parse(date).Day  == DateTime.Now.Day)
            {
                v = DateTime.Now.Hour;
            }
            for (int i=0;i < v;    i ++)
            {
                Incoming incomin = new Incoming();
                int t = i + 1;
                string iss=i.ToString () ,tss=t .ToString ();
                if (i < 10)
                    iss = "0" + i;
                if (t < 10)
                    tss = "0" + t;

                incomin.time = iss + ":00" + "-" + tss + ":00";
                incomin.IncomingCount = modelcalllist.Where(x => x.BeginTime.Value.Hour ==i &&x.PhoneType =="12345"
                ).Count();
                ldcount += incomin.IncomingCount;
                incomin.Oncapacity = modelcalllist.Where(x => x.BeginTime.Value.Hour ==i
                && x.CallState == 1 && x.PhoneType == "12345").Count();
                jtcount  += incomin.Oncapacity;

                incomin.newIncomingCount = modelcalllist.Where(x => x.BeginTime.Value.Hour == i && x.PhoneType == "12345"
                ).Count();
                newldcount += incomin.newIncomingCount;
                incomin.newOncapacity = modelcalllist.Where(x => x.BeginTime.Value.Hour == i
                && x.CallState == 1 && x.PhoneType == "12345").Count();
                newjtcount += incomin.newOncapacity;

                var modelcalllistss = modelcalllist.Where(x => x.UserCode != null&& x.UserCode != "" && x.BeginTime.Value.Hour==i&&x.PhoneType =="12345").Select(x => x.UserCode).Distinct
                    ();

                if (modelcalllistss != null && modelcalllistss.Count() > 0)
                {
                    incomin.seatsCount = modelcalllistss.Count();
                    foreach (var it in modelcalllistss)
                    {
                        var user = userlist.Where(x => x.F_UserCode == it);
                        if (user != null && user.Count() > 0)
                            incomin.seats += "," + user.FirstOrDefault().F_UserName;
                        else
                            incomin.seats += "," + it;
                    }
                    if (!string.IsNullOrEmpty(incomin.seats))
                        incomin.seats= incomin.seats.TrimStart(',');
                }
                else
                {
                    incomin.seatsCount = 0;
                    incomin.seats = "";
                }

                var modelcalllists = modelcalllist.Where(x => x.UserCode != null && x.UserCode != "" && x.BeginTime.Value.Hour == i && x.PhoneType != "12345").Select(x => x.UserCode).Distinct
                  ();

                if (modelcalllists != null && modelcalllists.Count() > 0)
                {
                    incomin.newseatsCount = modelcalllists.Count();
                    foreach (var it in modelcalllists)
                    {
                        var user = userlist.Where(x => x.F_UserCode == it);
                        if (user != null && user.Count() > 0)
                            incomin.newseats += "," + user.FirstOrDefault().F_UserName;
                        else
                            incomin.newseats += "," + it;
                    }
                    if (!string.IsNullOrEmpty(incomin.newseats))
                        incomin.newseats = incomin.newseats.TrimStart(',');
                }
                else
                {
                    incomin.newseatsCount = 0;
                    incomin.newseats = "";
                }


                incomings.Add(incomin);
            }
            Incoming incomint = new Incoming();
           
            incomint.time = "共计";
            incomint.IncomingCount = ldcount;
            incomint.Oncapacity = jtcount;
            incomint.newIncomingCount = newldcount;
            incomint.newOncapacity = newjtcount;
            incomings.Add(incomint);
            if (isdc >0)
            {
                new NPOIHelper().IncomingCalls(incomings);
                return Success("导出excel");
            }
            return Success("获取成功", incomings);
        }

        /// <summary>
        /// 逾期未回复统计
        /// </summary>
        /// <param name="date"></param>
        /// <param name="deptId"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public ActionResult GetOutTimeNotApply(DateTime? start, DateTime? end, int deptId = -1, int pageIndex = 1, int pageSize = 10, bool isExport = false)
        {
            if (start == null)
            {
                start = DateTime.Now;
                start = new DateTime(start.Value.Year, start.Value.Month, 1);
            }
            if (end == null)
            {
                end = DateTime.Now;
            }
            var where = "";
            if (deptId > 0)
            {
                where = " AND  F_MainDeptId=" + deptId;
            }

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

            if (isExport)
            {
                string[] cols = new string[] { "工单编号", "承办单位", "工单内容", "转派时间", "回复时限", "超时时间" };
                var dtall = DbHelperSQL.Query("select * from V_Bus_OverNoBack where 1=1 "+sql).Tables[0];
                dtall.Columns.Remove("F_MainDeptId");
                dtall.Columns.Remove("F_CreateTime");
                new NPOIHelper().ExportToExcel($"逾期未回复统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", dtall, cols);
                return Success("导出excel");
            }

            int recordCount = 0;
            var dt = BLL.PagerBLL.GetListPager(
                "V_Bus_OverNoBack",
                "F_WorkOrderId",
                "*",
                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="date"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public ActionResult GetNotSatisfied(DateTime? start, DateTime? end, int pageIndex = 1, int pageSize = 10, bool isExport = false)
        {
            if (start == null)
            {
                start = DateTime.Now;
                start = new DateTime(start.Value.Year, start.Value.Month, 1);
            }
            if (end == null)
            {
                end = DateTime.Now;
            }

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


            if (isExport)
            {
                string[] cols = new string[] { "工单编号", "承办单位", "工单内容", "创建时间", "处理结果", "处理时间", "回访结果", "回访时间" };
                var dtall = DbHelperSQL.Query("select * from V_Bus_ReloadNoSatisfie where 1=1 " + sql + " order by cjTime DESC ").Tables[0];
                dtall.Columns.Remove("F_Id");
                new NPOIHelper().ExportToExcel($"重办不满意统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", dtall, cols);
                return Success("导出excel");
            }

            int recordCount = 0;
            var dt = BLL.PagerBLL.GetListPager(
                "V_Bus_ReloadNoSatisfie",
                "F_Id",
                "*",
                sql,
                "ORDER BY cjTime DESC",
                pageSize,
                pageIndex,
                true,
                out recordCount);

            var obj = new
            {
                state = "success",
                message = "成功",
                rows = dt,
                total = recordCount
            };

            return Content(obj.ToJson());


        }

  

        /// <summary>
        /// 延期统计
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="isExport"></param>
        /// <returns></returns>
        public ActionResult GetDelay(DateTime? start, DateTime? end, bool isExport = false)
        {
            if (start == null)
            {
                start = DateTime.Now;
                start = new DateTime(start.Value.Year, start.Value.Month, 1);
            }
            if (end == null)
            {
                end = DateTime.Now;
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
            paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
            var obj = DbHelperSQL.RunProcedure("P_DeptDelayReport", paras, "DeptDelayReport");
            if (isExport)
            {
                string[] cols = new string[] { "单位名称", "办理数量", "延时数量", "延时率" };
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcel("延时分析统计报表", obj.Tables[0], cols) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            return Success("成功", obj);
        }

        /// <summary>
        /// 通报批评
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="isExport"></param>
        /// <returns></returns>
        public ActionResult GetNotice(DateTime? start, DateTime? end, bool isExport = false)
        {
            if (start == null)
            {
                start = DateTime.Now;
                start = new DateTime(start.Value.Year, start.Value.Month, 1);
            }
            if (end == null)
            {
                end = DateTime.Now;
            }
            //string sql = "select * from dbo.GetNoticeReport('"+ start.Value.ToString("yyyy-MM-dd") + "','" + end.Value.ToString("yyyy-MM-dd") + "') ORDER BY zbcount+xbcount DESC";
            //var dt = DbHelperSQL.Query(sql).Tables[0];
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
            paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
            var obj = DbHelperSQL.RunProcedure("P_DeptNoticeReport", paras, "DeptNoticeReport");
            if (isExport)
            {
                var cols = new string[] { "承办单位", "主办受理数量", "主办通报批评数量", "主办通报批评率", "协办受理数量", "协办通报批评数量", "协办通报批评率" };
                new NPOIHelper().ExportToExcel($"通报批评{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
                return Success("导出excel");
            }
            return Success("成功", obj);
        }

    

        /// <summary>
        /// 诉求受理对比
        /// </summary>
        /// <returns></returns>
        public ActionResult GetAppeal1(DateTime? start, DateTime? end, bool isExport = false)
        {
            if (start == null)
            {
                start = DateTime.Now;
                start = new DateTime(start.Value.Year, start.Value.Month, 1);
            }
            if (end == null)
            {
                end = DateTime.Now;
            }

            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
            paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
            var obj = DbHelperSQL.RunProcedure("P_UserAppealReport", paras, "UserAppealReport");
            if (isExport)
            {
                var cols = new string[] { "坐席名称", "受理数量", "上月同期受理数量", "上月同期环比", "去年同期受理数量", "去年同期同比" };
                new NPOIHelper().ExportToExcel($"诉求受理对比{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
                return Success("导出excel");
            }
            return Success("成功", obj);
        }
        public ActionResult GetAppeal(DateTime? start, DateTime? end, bool isExport = false
            , string source = "")
        {
            if (start == null)
            {
                start = DateTime.Now;
                start = new DateTime(start.Value.Year, start.Value.Month, 1);
            }
            if (end == null)
            {
                end = DateTime.Now;
            }

            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
            paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
            paras.Add("@source", source);
            var obj = DbHelperSQL.RunProcedure("P_UserAppealReport_1", paras, "UserAppealReport");
            var newSet = new DataSet();var newtable = new DataTable("UserAppealReport");
            #region
            newtable.Columns.Add(new DataColumn("username"));
            newtable.Columns.Add(new DataColumn("count", typeof(int)));
            newtable.Columns.Add(new DataColumn("mcount", typeof(int)));
            newtable.Columns.Add(new DataColumn("mom"));
            newtable.Columns.Add(new DataColumn("ycount", typeof(int)));
            newtable.Columns.Add(new DataColumn("yoy"));
            #endregion
            if (obj != null && obj.Tables[0] != null && obj.Tables[0].Rows.Count > 0)
            {
                
                for (int i = 0; i < obj.Tables[0].Rows.Count; i++)
                {
                    int count = 0, mcount = 0, ycount = 0; double mom = 0, yoy = 0;
                    int.TryParse(obj.Tables[0].Rows[i]["count"].ToString(), out count);
                    int.TryParse(obj.Tables[0].Rows[i]["mcount"].ToString(), out mcount);
                    int.TryParse(obj.Tables[0].Rows[i]["ycount"].ToString(), out ycount);
                    
                    if(mcount>0)
                        mom = double.Parse((count * 100).ToString()) / mcount;
                    if (ycount > 0)
                        yoy = double.Parse((count * 100).ToString()) / ycount;
                    #region
                    DataRow drNew = newtable.NewRow();
                    drNew["username"] = obj.Tables[0].Rows[i]["username"];
                    drNew["count"] = count;
                    drNew["mcount"] = mcount;
                    drNew["mom"] = mom > 0 ? mom.ToString("f") + "%" : "0";
                    drNew["ycount"] = ycount;
                    drNew["yoy"] = yoy > 0 ? yoy.ToString("f") + "%" : "0";
                    newtable.Rows.Add(drNew);
                    #endregion
                }
                newtable.DefaultView.Sort = "count desc";
                newSet.Tables.Add(newtable.DefaultView.ToTable());
            }
            if (isExport)
            {
                var cols = new string[] { "坐席名称", "受理数量", "上月同期受理数量", "上月同期环比", "去年同期受理数量", "去年同期同比" };
                new NPOIHelper().ExportToExcel($"诉求受理对比{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", newSet.Tables[0], cols);
                return Success("导出excel");
            }
            return Success("成功", newSet);
        }
        /// <summary>
        /// 获取督办报表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetDBReportform(string starttime,string endtime)
        {
           
            if (string .IsNullOrEmpty (starttime))
            {
                starttime = DateTime.Now.ToString("yyyy-MM") + "-01 00:00:00";
                endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            }
            else
            {
               if (string.IsNullOrEmpty(endtime))
                {
                    endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                }
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", starttime);
            paras.Add("@edate", endtime);
            var obj1 = DbHelperSQL.RunProcedure("P_DBReportform_1", paras, "DBReportform").Tables[0];
            string sqlarea = "  select COUNT(1) from T_Bus_WorkOrder where F_WorkOrderID in(select F_WorkOrderID from T_Bus_RemindRecord WITH(NOLOCK) where F_Type=1 and F_IsDelete=0  and F_State =0 and (Supervisor ='' or  Supervisor is null   and F_CreateTime  between   '" + starttime +
                             "' AND  '" + endtime + "')) and F_WorkState  !='9'and F_IsDelete =0 ";
            DataTable dttype = DbHelperSQL.Query(sqlarea).Tables[0];
            var obj = new
            {
                obj1,
                total = dttype
            };
            
            return Content(obj.ToJson());
        }
        /// <summary>
        /// 坐席状态统计
        /// </summary>
        /// <param name="starttime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public ActionResult GetZuoXiState(string starttime, string endtime)
        {
            if (string.IsNullOrEmpty(starttime))
            {
                starttime = DateTime.Now.ToString("yyyy-MM") + "-01 00:00:00";
                endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            }
            else
            {
                if (string.IsNullOrEmpty(endtime))
                {
                    endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                }
            }
          string   sql = "   a.OccurTime  between   '" + starttime + "' AND  '" + endtime + "'";
            string value = "  TimeLogin  between   '" + starttime + "' AND  '" + endtime + "'";
            string value1 = "  TimeLogout  between   '" + starttime + "' AND  '" + endtime + "'";
            string sqlarea = "  select a.AgentId ,dbo.GetUserName(a.AgentId) as UserName,(select COUNT(1) from rep_agent_detail where "+ value + " and AgentId = a.AgentId and 1" +
                " is not null  ) as 签入 ,(select COUNT(1) from rep_agent_detail where "+ value1 + " and AgentId = a.AgentId and TimeLogout is not null  ) as 签出,SUM(case State when 5 then 1 else 0   end) as 置忙 ,SUM(case State when 2 then 1 else 0   end) as 置闲 from dbo.rep_agent_state a where "+ sql + " group by AgentId ";
            DataTable obj = DbHelperSQL.Query(sqlarea).Tables[0];
            return Content(obj.ToJson());
        }

        /// <summary>
        /// 坐席时间
        /// </summary>
        /// <param name="starttime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public ActionResult GetZuoXiTime (string starttime, string endtime,string usercode,int state=0)
        {
            if (string.IsNullOrEmpty(starttime))
            {
                starttime = DateTime.Now.ToString("yyyy-MM") + "-01 00:00:00";
                endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            }
            else
            {
                if (string.IsNullOrEmpty(endtime))
                {
                    endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                }
            }
            if (string.IsNullOrEmpty(usercode))
                return Error("请选择坐席");
            string sqlzuoxi = "";
            if (state==0)
            {
                sqlzuoxi = "select TimeLogin from rep_agent_detail where TimeLogin  between   '" + starttime + "' AND  '" + endtime + "' and AgentId ='" + usercode + "'and TimeLogin is not null  ";
            }
            else if (state == 1)
            {

                sqlzuoxi = "select TimeLogout from rep_agent_detail where TimeLogout  between   '" + starttime + "' AND  '" + endtime + "' and AgentId ='" + usercode + "' and TimeLogout is not null  ";
            }
            else if (state == 2)
            {
                sqlzuoxi = "select OccurTime from rep_agent_state where OccurTime  between   '" + starttime + "' AND  '" + endtime + "' and AgentId ='" + usercode + "' and State = 2 ";
            }
            else if (state == 3)
            {
                sqlzuoxi = "select OccurTime from rep_agent_state where OccurTime  between   '" + starttime + "' AND  '" + endtime + "' and AgentId ='" + usercode + "' and State = 5 ";
            }
            DataTable obj = DbHelperSQL.Query(sqlzuoxi).Tables[0];
            return Content(obj.ToJson());
        }
        /// <summary>
        /// 获取绩效
        /// </summary>
        /// <param name="date"></param>
        /// <param name="isdc"></param>
        /// <returns></returns>
        public ActionResult GetEvaluation(string date, int deptid = 0,int isdc=0)
        {
            string sql = "F_IsDelete=0";
            string message = "";string datemessage = "";
            if (!string.IsNullOrEmpty(date))
            {
                message = "统计周期：" + DateTime.Parse(date).AddMonths(-1).ToString("yyyy年MM月") + "-25日--" + DateTime.Parse(date).ToString("yyyy年MM月") + "-24日";

                datemessage = MonthtoUpper(DateTime.Parse(date).Month );
                date = DateTime.Parse(date).ToString("yyyyMM");
               
            }
            else
            {
                message = "统计周期：" + DateTime.Now.AddMonths(-1).ToString("yyyy年MM月") + "-25日--" + DateTime.Now.ToString("yyyy年MM月") + "-24日";
                date = DateTime .Now.AddMonths (-1).ToString("yyyyMM");
                datemessage = MonthtoUpper(DateTime.Now .Month);
            }
           
            sql += "and F_Date='" + date + "'";
            if (deptid>0)
            {
                sql += "and F_Deptid='" + deptid + "'";
            }
            if (isdc>0)
            {
                
                var dtdc = DbHelperSQL.Query(" select  *  from T_Sys_Evaluation a WITH(NOLOCK) where " + sql+ " " +
                    " and F_Undertake>0 and F_Deptid in (select F_DeptId  from T_Sys_Department " +
                    "where F_DeptId in (SELECT F_DeptId  FROM dbo.[GetDaptiD](155) where F_IsDept = 1) " +
                    "and F_PartentId =155 and F_IsDealDept = 1 and F_DeptName not like '%测试%') order by F_Total desc").Tables[0];

                var dtdc1 = DbHelperSQL.Query(" select  *  from T_Sys_Evaluation a WITH(NOLOCK) where " + sql + " " +
                    "and F_Undertake>0  and F_Deptid in (select F_DeptId  from T_Sys_Department " +
                    "where F_DeptId in (SELECT F_DeptId  FROM dbo.[GetDaptiD](155) where F_IsDept = 1) " +
                    "and F_PartentId in(167, 169,170) and F_IsDealDept = 1 and F_DeptName not like '%测试%') order by F_Total desc").Tables[0];

                var dtdc2 = DbHelperSQL.Query(" select  *  from T_Sys_Evaluation a WITH(NOLOCK) where " + sql + " " +
                    "  and F_Undertake>0 and F_Deptid in (select F_DeptId  from T_Sys_Department " +
                    "where F_DeptId in (SELECT F_DeptId  FROM dbo.[GetDaptiD](155) where F_IsDept = 1) " +
                    "and F_PartentId in(171, 172) and F_IsDealDept = 1 and F_DeptName not like '%测试%')  order by F_Total desc").Tables[0];

                var dtdc3 = DbHelperSQL.Query(" select  *  from T_Sys_Evaluation a WITH(NOLOCK) where " + sql + " " +
                    " and F_Undertake=0  order by F_Total desc").Tables[0];

                var msg = new NPOIHelper().ExportEvaluation ( dtdc, dtdc1, dtdc2, dtdc3, message, datemessage);
                if (msg == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            else
            {
                string Evaluation = "select * from T_Sys_Evaluation where " + sql + "order by F_Total desc ";
                DataTable obj = DbHelperSQL.Query(Evaluation).Tables[0];
                return Content(obj.ToJson());
            }
           

        }
        private static string MonthtoUpper(int month)
        {
            if (month < 10)
            {
                return NumtoUpper(month) ;
            }
            else
                if (month == 10) { return "十" ; }

            else
            {
                return "十" + NumtoUpper(month - 10) ;
            }
        }
        private static string NumtoUpper(int num)
        {
            String str = num.ToString();
            string rstr = "";
            int n;
            for (int i = 0; i < str.Length; i++)
            {
                n = Convert.ToInt16(str[i].ToString());//char转数字,转换为字符串，再转数字
                switch (n)
                {
                    case 0: rstr = rstr + "〇"; break;
                    case 1: rstr = rstr + "一"; break;
                    case 2: rstr = rstr + "二"; break;
                    case 3: rstr = rstr + "三"; break;
                    case 4: rstr = rstr + "四"; break;
                    case 5: rstr = rstr + "五"; break;
                    case 6: rstr = rstr + "六"; break;
                    case 7: rstr = rstr + "七"; break;
                    case 8: rstr = rstr + "八"; break;
                    default: rstr = rstr + "九"; break;
                }

            }
            return rstr;
        }
        /// <summary>
        /// 获取绩效
        /// </summary>
        /// <param name="date"></param>
        /// <param name="isdc"></param>
        /// <returns></returns>
        public ActionResult GetDeptDeptAchievements(string starttime, string endtime
            , int type=0,int   deptid=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");
                }
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", starttime);
            paras.Add("@edate", endtime);
            paras.Add("@type", type.ToString ());
            if (deptid>0)
            paras.Add("@deptid", deptid.ToString ());
            else
                paras.Add("@deptid","");
            var obj = DbHelperSQL.RunProcedure("P_DeptAchievements_1", paras, "DeptAchievements").Tables[0]; ;
            return Content(obj.ToJson());

        }

        /// <summary>
        /// 工单周报
        /// 暂时无用，多主办单位不处理
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="type"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public ActionResult GetWeaklyReport(DateTime? start, DateTime? end, int type = 0, int dateType = 0, int pageIndex = 1, int pageSize = 10, bool isExport = false)
        {
            StringBuilder sb1 = new StringBuilder();
            StringBuilder sb2 = new StringBuilder();
            StringBuilder sb3 = new StringBuilder();
            var startDate = new DateTime();
            var endDate = new DateTime();
            var lastStartDate = new DateTime();
            var lastEndDate = new DateTime();
            var differ = 0;
            var str = "";
            switch (dateType)
            {
                //日
                case 1:
                    if (start == null)
                    {
                        start = DateTime.Now.Date;
                    }
                    if (end == null)
                    {
                        end = DateTime.Now.Date.AddDays(1).AddSeconds(-1);
                    }
                    startDate = Convert.ToDateTime(start);
                    endDate = Convert.ToDateTime(end).AddDays(1).AddSeconds(-1);
                    differ = (endDate - startDate).Days + 1;
                    lastStartDate = startDate.AddDays(-differ);
                    lastEndDate = endDate.AddDays(-differ);
                    str = "日";
                    break;
                //周
                case 2:
                    if (start == null)
                    {
                        start = DateTime.Now.Date;
                    }
                    if (end == null)
                    {
                        end = DateTime.Now.Date.AddDays(1).AddSeconds(-1);
                    }
                    startDate = Convert.ToDateTime(start);
                    endDate = Convert.ToDateTime(end);
                    differ = 7 - (int)startDate.DayOfWeek;
                    startDate = DateTools.GetWeekFirstDayMon((DateTime)start);
                    endDate = DateTools.GetWeekLastDaySun((DateTime)end).AddDays(1).AddSeconds(-1);
                    differ = (endDate - startDate).Days + 1;
                    lastStartDate = DateTools.GetWeekFirstDayMon(startDate.AddDays(-1));
                    lastEndDate = DateTools.GetWeekLastDaySun(lastStartDate).AddDays(1).AddSeconds(-1);
                    str = "周";
                    break;
                //月
                case 3:
                    if (start == null)
                    {
                        start = DateTime.Now.Date;
                    }
                    if (end == null)
                    {
                        end = DateTime.Now.Date.AddDays(1).AddSeconds(-1);
                    }
                    startDate = Convert.ToDateTime(start);
                    endDate = Convert.ToDateTime(end);
                    differ = startDate.Day;
                    startDate = startDate.AddDays(-differ + 1);
                    endDate = endDate.AddDays(DateTime.DaysInMonth(startDate.Year, startDate.Month) - differ).AddDays(1).AddSeconds(-1);
                    //differ = (endDate - startDate). + 1;
                    lastStartDate = startDate.AddMonths(-1);
                    lastEndDate = lastStartDate.AddDays(DateTime.DaysInMonth(lastStartDate.Year, lastStartDate.Month) - 1).AddDays(1).AddSeconds(-1);
                    str = "月";
                    break;
                default:
                    break;
            }
            //type 查询类型 type=1 按区域;type=2 按部门; type=3 按工单类型
            switch (type)
            {
                case 1:
                    var areaList = areaBLL.DataTableToList(areaBLL.GetList(" F_State=0 ").Tables[0]);
                    var areaList1 = new BLL.T_Bus_WorkOrderReport().DataTableToAreaList(DbHelperSQL.Query($"SELECT  A.F_WorkOrderId,A.F_WorkState,A.F_SourceArea,B.F_AreaName, row_number() over(partition by B.F_AreaId order by A.F_Id desc) FROM T_Bus_WorkOrder A LEFT JOIN T_Sys_Area B ON A.F_SourceArea = B.F_AreaId WHERE  datediff(s, '{startDate}', A.F_CreateTime) > 0  and  datediff(s, '{endDate}', A.F_CreateTime) < 0  and A.F_IsDelete = 0 AND A.F_WorkOrderId IS NOT NULL AND A.F_SourceArea > 0").Tables[0]);
                    var areaList2 = new BLL.T_Bus_WorkOrderReport().DataTableToAreaList(DbHelperSQL.Query($"SELECT  A.F_WorkOrderId,A.F_WorkState,A.F_SourceArea,B.F_AreaName, row_number() over(partition by B.F_AreaId order by A.F_Id desc) FROM T_Bus_WorkOrder A LEFT JOIN T_Sys_Area B ON A.F_SourceArea = B.F_AreaId WHERE  datediff(s, '{lastStartDate}', A.F_CreateTime) > 0  and  datediff(s, '{lastEndDate}', A.F_CreateTime) < 0  and A.F_IsDelete = 0 AND A.F_WorkOrderId IS NOT NULL AND A.F_SourceArea > 0").Tables[0]);
                    List<DeptWeaklyReportDto> areaWeaklyReportDtoList = new List<DeptWeaklyReportDto>();
                    areaList.ForEach(x =>
                    {
                        var query = areaList1.Where(y => x.F_AreaId == y.FieldId).ToList();
                        var assignedCount = query.Count();
                        var lastAssignedcount = areaList2.Where(y => x.F_AreaId == y.FieldId).Count();
                        var countRate = lastAssignedcount == 0 ? "0%" : assignedCount == 0 ? "0%" : ((double)assignedCount / lastAssignedcount * 100).ToString("f2") + "%";
                        var completedCount = query.Where(y => y.WorkOrderState == 9).Count();
                        var completedRate = assignedCount == 0 ? "0%" : completedCount == 0 ? "0%" : ((double)completedCount / assignedCount * 100).ToString("f2") + "%";
                        areaWeaklyReportDtoList.Add(new DeptWeaklyReportDto
                        {
                            name = x.F_AreaName,
                            assignedcount = assignedCount,
                            countrate = countRate,
                            completedcount = completedCount,
                            completedrate = completedRate
                        });
                    });
                    if (isExport)
                    {
                        var dt = areaWeaklyReportDtoList.ToDataTable(new string[] { "name", "assignedcount", "countrate", "completedcount", "completedrate" });
                        new NPOIHelper().ExportToExcel($"按区域{str}报表{Convert.ToDateTime(startDate).ToString("yyyyMMdd")}-{Convert.ToDateTime(endDate).ToString("yyyyMMdd")}", dt, new string[] { "区域", "受理数量", "数量同比", "办结数量", "办结率" });
                        return Success("导出excel");
                    }
                    return Content(new
                    {
                        rows = areaWeaklyReportDtoList.Skip((pageIndex - 1) * pageSize).Take(pageSize),
                        total = areaWeaklyReportDtoList.Count
                    }.ToJson());
                case 2:
                    var deptList = departmentBLL.DataTableToList(departmentBLL.GetList(" F_State=0 AND F_DeptNameSpell=3 ").Tables[0]);
                    var deptList1 = new BLL.T_Bus_WorkOrderReport().DataTableToDeptList1(DbHelperSQL.Query($"SELECT  A.F_WorkOrderId,A.F_WorkState,B.F_MainDeptId,C.F_DeptName , row_number() over(partition by B.F_MainDeptId order by A.F_Id desc)FROM T_Bus_WorkOrder A LEFT JOIN T_Bus_AssignedInfo B ON A.F_WorkOrderId=B.F_WorkOrderId LEFT JOIN T_Sys_Department C ON B.F_MainDeptId=C.F_DeptId AND C.F_DeptNameSpell=3 WHERE  datediff(s,'{startDate}',A.F_CreateTime)>0  and   datediff(s,'{endDate}',A.F_CreateTime)<0  and A.F_IsDelete=0 AND A.F_WorkOrderId IS NOT NULL AND  B.F_IsDelete=0 and B.F_IsSure in (0,1,2) and  B.F_State in (0,1)").Tables[0]).DistinctBy(x => x.WorkOrderId).ToList();//DataTableToDeptList
                    var deptList2 = new BLL.T_Bus_WorkOrderReport().DataTableToDeptList1(DbHelperSQL.Query($"SELECT  A.F_WorkOrderId,A.F_WorkState,B.F_MainDeptId,C.F_DeptName , row_number() over(partition by B.F_MainDeptId order by A.F_Id desc)FROM T_Bus_WorkOrder A LEFT JOIN T_Bus_AssignedInfo B ON A.F_WorkOrderId=B.F_WorkOrderId LEFT JOIN T_Sys_Department C ON B.F_MainDeptId=C.F_DeptId AND C.F_DeptNameSpell=3 WHERE  datediff(s,'{lastStartDate}',A.F_CreateTime)>0  and   datediff(s,'{lastEndDate}',A.F_CreateTime)<0  and A.F_IsDelete=0 AND A.F_WorkOrderId IS NOT NULL AND  B.F_IsDelete=0 and B.F_IsSure in (0,1,2) and  B.F_State in (0,1)").Tables[0]).DistinctBy(x => x.WorkOrderId).ToList();//DataTableToDeptList
                    List<DeptWeaklyReportDto> deptWeaklyReportDtoList = new List<DeptWeaklyReportDto>();
                    deptList.ForEach(x =>
                     {
                         var query = deptList1.Where(y => y.FieldId!=null && y.FieldId.Split(',').Contains(x.F_DeptId.ToString())).ToList();
                         //var query = deptList1.Where(y => y.FieldId == x.F_DeptId).ToList();
                         var assignedCount = query.Count();
                         var lastAssignedcount = deptList2.Where(y => y.FieldId != null && y.FieldId.Split(',').Contains(x.F_DeptId.ToString())).Count();
                         //var lastAssignedcount = deptList2.Where(y => y.FieldId == x.F_DeptId).Count();
                         var countRate = lastAssignedcount == 0 ? "0%" : assignedCount == 0 ? "0%" : ((double)assignedCount / lastAssignedcount * 100).ToString("f2") + "%";
                         var completedCount = query.Where(y => y.WorkOrderState == 9).Count();
                         var completedRate = assignedCount == 0 ? "0%" : completedCount == 0 ? "0%" : ((double)completedCount / assignedCount * 100).ToString("f2") + "%";
                         deptWeaklyReportDtoList.Add(new DeptWeaklyReportDto
                         {
                             name = x.F_DeptName,
                             assignedcount = assignedCount,
                             countrate = countRate,
                             completedcount = completedCount,
                             completedrate = completedRate
                         });
                     });
                    if (isExport)
                    {
                        var dt = deptWeaklyReportDtoList.ToDataTable(new string[] { "name", "assignedcount", "countrate", "completedcount", "completedrate" });
                        new NPOIHelper().ExportToExcel($"按单位{str}报表{Convert.ToDateTime(startDate).ToString("yyyyMMdd")}-{Convert.ToDateTime(endDate).ToString("yyyyMMdd")}", dt, new string[] { "单位", "受理数量", "数量同比", "办结数量", "办结率" });
                        return Success("导出excel");
                    }
                    return Content(new
                    {
                        rows = deptWeaklyReportDtoList.Skip((pageIndex - 1) * pageSize).Take(pageSize),
                        total = deptWeaklyReportDtoList.Count
                    }.ToJson());
                case 3:
                    var typeList = dictValueBLL.DataTableToList(dictValueBLL.GetList(" F_State=0 and F_ItemId=2 ").Tables[0]);
                    var typeList1 = new BLL.T_Bus_WorkOrderReport().DataTableToTypeList(DbHelperSQL.Query($"SELECT  A.F_WorkOrderId,A.F_WorkState,A.F_InfoType,B.F_Value  , row_number() over(partition by A.F_InfoType order by A.F_Id desc) FROM T_Bus_WorkOrder A  LEFT JOIN T_Sys_DictionaryValue B ON A.F_InfoType=B.F_ValueId  WHERE  datediff(s,'{startDate}',A.F_CreateTime)>0  and   datediff(s,'{endDate}',A.F_CreateTime)<0  and A.F_IsDelete=0 AND A.F_WorkOrderId IS NOT NULL AND    B.F_State=0 and B.F_ItemId=2 ").Tables[0]);
                    var typeList2 = new BLL.T_Bus_WorkOrderReport().DataTableToTypeList(DbHelperSQL.Query($"SELECT  A.F_WorkOrderId,A.F_WorkState,A.F_InfoType,B.F_Value  , row_number() over(partition by A.F_InfoType order by A.F_Id desc) FROM T_Bus_WorkOrder A  LEFT JOIN T_Sys_DictionaryValue B ON A.F_InfoType=B.F_ValueId  WHERE  datediff(s,'{lastStartDate}',A.F_CreateTime)>0  and   datediff(s,'{lastEndDate}',A.F_CreateTime)<0  and A.F_IsDelete=0 AND A.F_WorkOrderId IS NOT NULL AND    B.F_State=0 and B.F_ItemId=2 ").Tables[0]);
                    List<DeptWeaklyReportDto> typeWeaklyReportDtoList = new List<DeptWeaklyReportDto>();
                    typeList.ForEach(x =>
                    {
                        var query = typeList1.Where(y => y.FieldId == x.F_ValueId);
                        var assignedCount = query.Count();
                        var lastAssignedcount = typeList2.Where(y => y.FieldId == x.F_ValueId).Count();
                        var countRate = lastAssignedcount == 0 ? "0%" : assignedCount == 0 ? "0%" : ((double)assignedCount / lastAssignedcount * 100).ToString("f2") + "%";
                        var completedCount = query.Where(y => y.WorkOrderState == 9).Count();
                        var completedRate = assignedCount == 0 ? "0%" : completedCount == 0 ? "0%" : ((double)completedCount / assignedCount * 100).ToString("f2") + "%";
                        typeWeaklyReportDtoList.Add(new DeptWeaklyReportDto
                        {
                            name = x.F_Value,
                            assignedcount = assignedCount,
                            countrate = countRate,
                            completedcount = completedCount,
                            completedrate = completedRate
                        });
                    });
                    if (isExport)
                    {
                        var dt = typeWeaklyReportDtoList.ToDataTable(new string[] { "name", "assignedcount", "countrate", "completedcount", "completedrate" });
                        new NPOIHelper().ExportToExcel($"按工单类型{str}报表{Convert.ToDateTime(startDate).ToString("yyyyMMdd")}-{Convert.ToDateTime(endDate).ToString("yyyyMMdd")}", dt, new string[] { "工单类型", "受理数量", "数量同比", "办结数量", "办结率" });
                        return Success("导出excel");
                    }
                    return Content(new
                    {
                        rows = typeWeaklyReportDtoList.Skip((pageIndex - 1) * pageSize).Take(pageSize),
                        total = typeWeaklyReportDtoList.Count
                    }.ToJson());
                case 4:
                    var sourceList = dictValueBLL.DataTableToList(dictValueBLL.GetList(" F_State = 0 and F_ItemId = 1").Tables[0]);
                    var sourceList1 = new BLL.T_Bus_WorkOrderReport().DataTableToSourceList(DbHelperSQL.Query($"SELECT  A.F_WorkOrderId,A.F_WorkState,A.F_InfoSource,B.F_Value  , row_number() over(partition by A.F_InfoSource order by A.F_Id desc) FROM T_Bus_WorkOrder A LEFT JOIN T_Sys_DictionaryValue B ON A.F_InfoSource = B.F_ValueId WHERE  datediff(s, '{startDate}', A.F_CreateTime) > 0  and   datediff(s, '{endDate}', A.F_CreateTime) < 0  and A.F_IsDelete = 0 AND A.F_WorkOrderId IS NOT NULL AND  B.F_State = 0 and B.F_ItemId = 1").Tables[0]);
                    var sourceList2 = new BLL.T_Bus_WorkOrderReport().DataTableToSourceList(DbHelperSQL.Query($"SELECT  A.F_WorkOrderId,A.F_WorkState,A.F_InfoSource,B.F_Value  , row_number() over(partition by A.F_InfoSource order by A.F_Id desc) FROM T_Bus_WorkOrder A LEFT JOIN T_Sys_DictionaryValue B ON A.F_InfoSource = B.F_ValueId WHERE  datediff(s, '{lastStartDate}', A.F_CreateTime) > 0  and   datediff(s, '{lastEndDate}', A.F_CreateTime) < 0  and A.F_IsDelete = 0 AND A.F_WorkOrderId IS NOT NULL AND  B.F_State = 0 and B.F_ItemId = 1").Tables[0]);
                    List<DeptWeaklyReportDto> sourceWeaklyReportDtoList = new List<DeptWeaklyReportDto>();
                    sourceList.ForEach(x =>
                    {
                        var query = sourceList1.Where(y => y.FieldId == x.F_ValueId).ToList();
                        var assignedCount = query.Count();
                        var lastAssignedcount = sourceList2.Where(y => y.FieldId == x.F_ValueId).Count();
                        var countRate = lastAssignedcount == 0 ? "0%" : assignedCount == 0 ? "0%" : ((double)assignedCount / lastAssignedcount * 100).ToString("f2") + "%";
                        var completedCount = query.Where(y => y.WorkOrderState == 9).Count();
                        var completedRate = assignedCount == 0 ? "0%" : completedCount == 0 ? "0%" : ((double)completedCount / assignedCount * 100).ToString("f2") + "%";
                        sourceWeaklyReportDtoList.Add(new DeptWeaklyReportDto
                        {
                            name = x.F_Value,
                            assignedcount = assignedCount,
                            countrate = countRate,
                            completedcount = completedCount,
                            completedrate = completedRate
                        });
                    });
                    if (isExport)
                    {
                        var dt = sourceWeaklyReportDtoList.ToDataTable(new string[] { "name", "assignedcount", "countrate", "completedcount", "completedrate" });
                        new NPOIHelper().ExportToExcel($"按工单来源{str}报表{Convert.ToDateTime(startDate).ToString("yyyyMMdd")}-{Convert.ToDateTime(endDate).ToString("yyyyMMdd")}", dt, new string[] { "工单来源", "受理数量", "数量同比", "办结数量", "办结率" });
                        return Success("导出excel");
                    }
                    return Content(new
                    {
                        rows = sourceWeaklyReportDtoList.Skip((pageIndex - 1) * pageSize).Take(pageSize),
                        total = sourceWeaklyReportDtoList.Count
                    }.ToJson());
                default:
                    return Error("参数错误");
            }
        }

        /// <summary>
        /// 工单日周月报
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="type"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public ActionResult GetDayWeekMonthReport(DateTime? date, int type = 0, int dateType = 0, bool isExport = false,string  source="")
        {
            if (date == null)
            {
                date = DateTime.Now.Date;
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@date", date.Value.ToString("yyyy-MM-dd"));
            paras.Add("@datatype", type.ToString());
            paras.Add("@datetype", dateType.ToString());
            paras.Add("@source", source);
            var obj = DbHelperSQL.RunProcedure("P_DayWeekMonthReport", paras, "DayWeekMonthReport");
            if (isExport)
            {
                var cols = new string[] { "名称", "受理数量", "数量同比", "结案数量", "结案率" };
                var start = obj.Tables[1].Rows[0]["startdate"].ToString();
                var end = obj.Tables[1].Rows[0]["enddate"].ToString();
                new NPOIHelper().ExportToExcel($"工单报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
                return Success("导出excel");
            }
            return Success("成功", obj);
        }
        /// <summary>
        /// 电话中心受话情况统计表
        /// </summary>
        /// <param name="starttime/"></param>
        /// <param name="endtime"></param>
        /// <param name="usercode"></param>
        /// <param name="group"></param>
        /// <param name="isExport"></param>
        /// <returns></returns>
        public ActionResult CenterReception(DateTime? starttime, DateTime? endtime,string usercode="",string group="",
           bool isExport = false)
        {

            DateTime date = DateTime.Now;
            if (starttime == null)
            {
                starttime = new DateTime(date.Year, date.Month, 1);
            }
            if (endtime == null)
            {
                endtime = DateTime.Now;
            }
          
           
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", starttime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
            paras.Add("@edate", endtime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
            paras.Add("@usercode", usercode);
            paras.Add("@group", group);
            var obj = DbHelperSQL.RunProcedure("P_CenterReception", paras, "CenterReception");
            if (isExport)
            {
                string message = new NPOIHelper().CenterReception(obj);
                if (message == "")
                    return Success("导出excel");
                else
                    return Error(message);
            }
            return Success("成功", obj);



        }

        /// <summary>
        /// 局委办件统计
        /// </summary>
        /// <param name="starttime"></param>
        /// <param name="endtime"></param>
        /// <param name="deptid"></param>
        /// <param name="source"></param>
        /// <param name="isExport"></param>
        /// <returns></returns>
        public ActionResult BureauHandling(DateTime? starttime, DateTime? endtime,int   deptid =0, int  source=0, bool isExport = false)
        {
            DateTime date = DateTime.Now;
            if (starttime == null)
            {
                starttime = new DateTime(date.Year, date.Month, 1);
            }
            if (endtime == null)
            {
                endtime = DateTime.Now;
            }
            string dept = "";
            if (deptid>0)
                dept= deptid.ToString();
            string infosource = "";
            if (source > 0)
                infosource = source.ToString();

            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", starttime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
            paras.Add("@edate", endtime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
            paras.Add("@deptid", dept);
            paras.Add("@source", infosource);
            var obj = DbHelperSQL.RunProcedure("P_DeptBureauHandling", paras, "DeptBureauHandling");
            if (isExport)
            {
                string message = new NPOIHelper().BureauHandling(obj);
                if (message == "")
                    return Success("导出excel");
                else
                    return Error(message);
            }
            return Success("成功", obj);

        }


        /// <summary>
        /// 局委评议统计
        /// </summary>
        /// <param name="starttime"></param>
        /// <param name="endtime"></param>
        /// <param name="deptid"></param>
        /// <param name="source"></param>
        /// <param name="isExport"></param>
        /// <returns></returns>
        public ActionResult DeptReview(DateTime? starttime, DateTime? endtime, int deptid = 0, int source = 0, bool isExport = false)
        {
            DateTime date = DateTime.Now;
            if (starttime == null)
            {
                starttime = new DateTime(date.Year, date.Month, 1);
            }
            if (endtime == null)
            {
                endtime = DateTime.Now;
            }
            string dept = "";
            if (deptid > 0)
                dept = deptid.ToString();
            string infosource = "";
            if (source > 0)
                infosource = source.ToString();

            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", starttime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
            paras.Add("@edate", endtime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
            paras.Add("@deptid", dept);
            paras.Add("@source", infosource);
            var obj = DbHelperSQL.RunProcedure("P_DeptReview", paras, "DeptReview");
            if (isExport)
            {
                var cols = new string[] { "序号", "承办单位", "办件总数", "评议率", "评议总数", "一次评议满意", "一次评议不满意"
                , "一次评议满意率", "二次评议满意",  "二次评议不满意", "二级评议满意率"};
                new NPOIHelper().ExportToExcel("局委评议统计", obj.Tables[0], cols);
                return Success("导出excel");
            }
            return Success("成功", obj);

        }

        /// <summary>
        /// 政民互动综合统计
        /// </summary>
        /// <param name="starttime"></param>
        /// <param name="endtime"></param>
        /// <param name="deptid"></param>
        /// <param name="source"></param>
        /// <param name="isExport"></param>
        /// <returns></returns>
        public ActionResult Government(DateTime? starttime, DateTime? endtime, int deptid = 0, int source = 0, bool isExport = false)
        {
            DateTime date = DateTime.Now;
            if (starttime == null)
            {
                starttime = new DateTime(date.Year, date.Month, 1);
            }
            if (endtime == null)
            {
                endtime = DateTime.Now;
            }
            string dept = "";
            if (deptid > 0)
                dept = deptid.ToString();
            string infosource = "";
            if (source > 0)
                infosource = source.ToString();

            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", starttime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
            paras.Add("@edate", endtime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
            paras.Add("@deptid", dept);
            paras.Add("@source", infosource);
            var obj = DbHelperSQL.RunProcedure("P_Government", paras, "Government");
            if (isExport)
            {
                string message = new NPOIHelper().Government(obj);
                if (message == "")
                    return Success("导出excel");
                else
                    return Error(message);
            }
            return Success("成功", obj);

        }
        /// <summary>
        /// 归并热线每日数据统计
        /// </summary>
        /// <param name="starttime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public ActionResult MergerHotline(DateTime? starttime , DateTime ? endtime,bool  isExport = false  )
        {
            DateTime date = DateTime.Now;
            if (starttime ==null )
            {
                starttime = new DateTime(date.Year, date.Month, 1);
            }
            if (endtime == null)
            {
                endtime = DateTime.Now;
            }
            List<MergerHot.MergerHotSource> MergerHotSource = new List<MergerHot.MergerHotSource>();
            var source = new BLL.T_Sys_DictionaryValue().GetModelList
                ("F_PrentId ='2498'");
            string key = "";
            foreach (var it in source)
            {
                key += ",'"+it.F_Value+"'" ;
            }
            var CallRecords = new BLL.T_Call_CallRecords().GetModelList("CallType=0 and BeginTime  between '" + starttime.Value.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + endtime.Value.ToString("yyyy-MM-dd HH:mm:ss") + "' and PhoneType in ("+ key .TrimStart (',') + ")");
          
            if (source !=null && source .Count >0)
            {
                for (DateTime dt = starttime.Value; dt <= endtime.Value; dt = dt.AddDays(1))
                {
                    MergerHot.MergerHotSource mergerHotSources = new MergerHot.MergerHotSource();
                    mergerHotSources.Data  = dt.ToString("MM月dd日");
                    mergerHotSources.MergerHot = new List<MergerHot.MergerHots>();
                    for (int i = 0; i < source.Count + 3; i++)
                    {
                        if (i < source.Count)
                        {
                            
                            MergerHot.MergerHots mergerHot = new MergerHot.MergerHots();
                            if (source[i].F_Value == "12345")
                            {
                                mergerHot.sort = source.Count;
                            }
                            else
                            {
                                mergerHot.sort = i-1;
                            }
                                mergerHot.Source  = source[i ].F_Value ;
                                mergerHot.Call = CallRecords.Where(x => x.BeginTime
                                .Value.Day   ==dt .Day && x.PhoneType == source[i].F_Value).Count();
                                mergerHot.Connect = CallRecords.Where(x => x.BeginTime
                                .Value.Day == dt.Day && x.CallState == 1 && x.PhoneType == source[i].F_Value).Count();
                                mergerHot.Notconnected = CallRecords.Where(x => x.BeginTime
                              .Value.Day == dt.Day && x.CallState == 0 && x.PhoneType == source[i].F_Value && x.UserCode != ""
                             && x.UserCode != null).Count();
                                mergerHot.voluntarily = CallRecords.Where(x => x.BeginTime
                         .Value.Day == dt.Day && x.CallState == 0 && x.PhoneType == source[i].F_Value && (x.UserCode == ""
                             || x.UserCode == null)).Count();

                            if (mergerHot.Call > 0)
                            {
                                mergerHot.rate = string.Format("{0:f2}%", (float)mergerHot.Connect / (float)mergerHot.Call * 100);
                            }
                            else
                            {
                                mergerHot.rate = "100%";
                            }
                         
                            mergerHotSources.MergerHot.Add(mergerHot);
                            
                        }
                        else if (i == source.Count)
                        {
                            MergerHot.MergerHots mergerHot = new MergerHot.MergerHots();
                            mergerHot.Source = "当日小计";
                            mergerHot.sort = source.Count + 1;
                            mergerHot.Call = CallRecords.Where(x => x.BeginTime
                            .Value.Day == dt.Day).Count();
                            mergerHot.Connect = CallRecords.Where(x => x.BeginTime
                            .Value.Day == dt.Day && x.CallState == 1).Count();
                            mergerHot.Notconnected = CallRecords.Where(x => x.BeginTime
                          .Value.Day == dt.Day && x.CallState == 0  && x.UserCode != ""
                         && x.UserCode != null).Count();
                            mergerHot.voluntarily = CallRecords.Where(x => x.BeginTime
                          .Value.Day == dt.Day && x.CallState == 0  && (x.UserCode == ""
                         || x.UserCode == null)).Count();
                            if (mergerHot.Call>0)
                            {
                                mergerHot.rate = string.Format("{0:f2}%", (float)mergerHot.Connect / (float)mergerHot.Call * 100);
                            }
                            else
                            {
                                mergerHot.rate = "100%";
                            }
                            mergerHotSources.MergerHot.Add(mergerHot);
                        }
                        else if (i == source.Count+1)
                        {
                            MergerHot.MergerHots mergerHot = new MergerHot.MergerHots();
                            mergerHot.Source = "累计";
                            mergerHot.sort = source.Count+2;
                            mergerHot.Call = CallRecords.Where(x => x.BeginTime 
                            .Value.Date  >=starttime  .Value.Date && x.BeginTime
                            .Value.Date <= dt.Date ).Count();
                            mergerHot.Connect = CallRecords.Where(x => x.BeginTime
                            .Value.Date >= starttime.Value.Date && x.BeginTime
                            .Value.Date <= dt.Date && x.CallState == 1 ).Count();
                            mergerHot.Notconnected = CallRecords.Where(x => x.BeginTime
                            .Value.Date >= starttime.Value.Date && x.BeginTime
                            .Value.Date <= dt.Date && x.CallState == 0 && x.UserCode != ""
                         && x.UserCode != null).Count();
                            mergerHot.voluntarily = CallRecords.Where(x => x.BeginTime
                            .Value.Date >= starttime.Value.Date && x.BeginTime
                            .Value.Date <= dt.Date && x.CallState == 0 && (x.UserCode == ""
                         || x.UserCode == null)).Count();
                            if (mergerHot.Call > 0)
                            {
                                mergerHot.rate = string.Format("{0:f2}%", (float)mergerHot.Connect / (float)mergerHot.Call * 100);
                            }
                            else
                            {
                                mergerHot.rate = "100%";
                            }
                            mergerHotSources.MergerHot.Add(mergerHot);
                        }
                        else if (i == source.Count+2)
                        {
                            MergerHot.MergerHots mergerHot = new MergerHot.MergerHots();
                            mergerHot.Source = "归并热线小计";
                            mergerHot.sort = source.Count-1;
                            mergerHot.Call = CallRecords.Where(x => x.BeginTime
                            .Value.Day == dt.Day&&x.PhoneType !="12345").Count();
                            mergerHot.Connect = CallRecords.Where(x => x.BeginTime
                            .Value.Day == dt.Day && x.CallState == 1 && x.PhoneType != "12345").Count();
                            mergerHot.Notconnected = CallRecords.Where(x => x.BeginTime
                          .Value.Day == dt.Day && x.CallState == 0 && x.UserCode != ""
                         && x.UserCode != null && x.PhoneType != "12345").Count();
                            mergerHot.voluntarily = CallRecords.Where(x => x.BeginTime
                          .Value.Day == dt.Day && x.CallState == 0 && (x.UserCode == ""
                         || x.UserCode == null) && x.PhoneType != "12345").Count();
                            mergerHot.callback = CallRecords.Where(x => x.BeginTime
                         .Value.Day == dt.Day &&x .PhoneType != "12345" && x.Callback == 1).Count();
                          
                            if (mergerHot.Call > 0)
                            {
                                mergerHot.rate = string.Format("{0:f2}%", (float)mergerHot.Connect / (float)mergerHot.Call * 100);
                            }
                            else
                            {
                                mergerHot.rate = "100%";
                            }
                            mergerHotSources.MergerHot.Add(mergerHot);
                        }
                    }
                    mergerHotSources.MergerHot= mergerHotSources.MergerHot.OrderBy(x => x.sort).ToList ();
                    MergerHotSource.Add(mergerHotSources);
                }
            }
            if (MergerHotSource!=null && MergerHotSource .Count >0)
            {
                MergerHot.MergerHotSource mergerHotSources = new MergerHot.MergerHotSource();
                mergerHotSources.Data = "合计";
                mergerHotSources.MergerHot = new List<MergerHot.MergerHots>();
                for (int i = 0; i < source.Count + 2; i++)
                {
                    if (i < source.Count)
                    {
                        MergerHot.MergerHots mergerHot = new MergerHot.MergerHots();
                        mergerHot.Source = source[i].F_Value;
                        mergerHot.Call = CallRecords.Where(x=> x.PhoneType == source[i].F_Value).Count();
                        mergerHot.Connect = CallRecords.Where(x =>  x.CallState == 1 && x.PhoneType == source[i].F_Value).Count();
                        mergerHot.Notconnected = CallRecords.Where(x =>  x.CallState == 0 && x.PhoneType == source[i].F_Value && x.UserCode != ""
                     && x.UserCode != null).Count();
                        mergerHot.voluntarily = CallRecords.Where(x =>  x.CallState == 0 && x.PhoneType == source[i].F_Value && (x.UserCode == ""
                     || x.UserCode == null)).Count();
                        if (source[i].F_Value=="12345")
                        {
                            mergerHot.sort = source.Count ;

                        }
                        else
                        {
                            mergerHot.sort =i -1;
                        }
                        if (mergerHot.Call > 0)
                        {
                            mergerHot.rate = string.Format("{0:f2}%", (float)mergerHot.Connect / (float)mergerHot.Call * 100);
                        }
                        else
                        {
                            mergerHot.rate = "100%";
                        }
                        mergerHotSources.MergerHot.Add(mergerHot);

                    }
                    else if (i == source.Count)
                    {
                        MergerHot.MergerHots mergerHot = new MergerHot.MergerHots();
                        mergerHot.Source ="当日小计";
                        mergerHot.sort = source.Count + 1;
                        mergerHot.Call = CallRecords.Count();
                        mergerHot.Connect = CallRecords.Where(x =>x.CallState == 1).Count();
                        mergerHot.Notconnected = CallRecords.Where(x =>  x.CallState == 0 &&   x.UserCode != ""
                     && x.UserCode != null).Count();
                        mergerHot.voluntarily = CallRecords.Where(x =>   x.CallState == 0  && (x.UserCode == ""
                     || x.UserCode == null)).Count();
                        if (mergerHot.Call > 0)
                        {
                            mergerHot.rate = string.Format("{0:f2}%", (float)mergerHot.Connect / (float)mergerHot.Call * 100);
                        }
                        else
                        {
                            mergerHot.rate = "100%";
                        }
                        mergerHotSources.MergerHot.Add(mergerHot);
                    }
                    else if (i == source.Count+1)
                    {
                        MergerHot.MergerHots mergerHot = new MergerHot.MergerHots();
                        mergerHot.Source = "归并热线小计";
                        mergerHot.sort = source.Count - 1;
                        mergerHot.Call = CallRecords.Where(x =>  x.PhoneType != "12345").Count();
                        mergerHot.Connect = CallRecords.Where(x =>  x.PhoneType != "12345").Count();
                        mergerHot.Notconnected = CallRecords.Where(x =>x.CallState == 0 && x.UserCode != ""
                     && x.UserCode != null && x.PhoneType != "12345").Count();
                        mergerHot.voluntarily = CallRecords.Where(x =>   x.CallState == 0 && (x.UserCode == ""
                     || x.UserCode == null && x.PhoneType != "12345")).Count();
                        mergerHot.callback = CallRecords.Where(x => x.PhoneType != "12345" && x.Callback == 1).Count();

                        if (mergerHot.Call > 0)
                        {
                            mergerHot.rate = string.Format("{0:f2}%", (float)mergerHot.Connect / (float)mergerHot.Call * 100);
                        }
                        else
                        {
                            mergerHot.rate = "100%";
                        }
                        mergerHotSources.MergerHot.Add(mergerHot);
                    }
                }
                mergerHotSources.MergerHot = mergerHotSources.MergerHot.OrderBy(x => x.sort).ToList();
                MergerHotSource.Add(mergerHotSources);
            }
            if (isExport)
            {
               
                new NPOIHelper().ExportMergerToExcel(MergerHotSource);
                return Success("导出excel");
            }
            return Success("获取成功", MergerHotSource);
            
        }
   

        /// <summary>
        /// 调度工作量统计
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="isExport"></param>
        /// <returns></returns>
        public ActionResult GetWorkLoad1(DateTime? start, DateTime? end, bool isExport = false)
        {
            if (start == null)
            {
                start = DateTime.Now;
                start = new DateTime(start.Value.Year, start.Value.Month, 1); 
            }
            if (end == null)
            {
                end = DateTime.Now;
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
            paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
            var obj = DbHelperSQL.RunProcedure("P_LeaderAssessmentReport", paras, "LeaderAssessmentReport");
            if (isExport)
            {
                var cols = new string[] { "姓名", "回退量", "交办量",  "退回审核量", "延时审核量", "重办量", "结案量" };
                new NPOIHelper().ExportToExcel($"调度工作量统计{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
                return Success("导出excel");
            }
            return Success("成功", obj);
        }
        public ActionResult GetWorkLoad(DateTime? start, DateTime? end, bool isExport = false, string source = "")
        {
            if (start == null)
            {
                start = DateTime.Now;
                start = new DateTime(start.Value.Year, start.Value.Month, 1);
            }
            if (end == null)
            {
                end = DateTime.Now;
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
            paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
            paras.Add("@source", source);
            var obj = DbHelperSQL.RunProcedure("P_LeaderAssessmentReport_1", paras, "LeaderAssessmentReport");
            if (isExport)
            {
                var cols = new string[] { "姓名", "回退量", "交办量", "退回审核量", "延时审核量", "重办量", "结案量" };
                new NPOIHelper().ExportToExcel($"调度工作量统计{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
                return Success("导出excel");
            }
            return Success("成功", obj);
        }


        /// <summary>
        /// 获取未处理报表
        /// </summary>
        /// <param name="date"></param>
        /// <param name="isdc"></param>
        /// <returns></returns>
        public ActionResult GetNoDealReport(DateTime? start, DateTime? end, int deptid = 0, int pageIndex = 1, int pageSize = 10, int isdc = 0)
        {
            if (start == null)
            {
                start = DateTime.Now;
                start = new DateTime(start.Value.Year, start.Value.Month, 1);
            }
            if (end == null)
            {
                end = DateTime.Now;
            }
            var where = "";
            if (deptid > 0)
            {
                where = " AND  F_MainDeptId=" + deptid;
            }

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


            if (isdc > 0)
            {
                string[] cols = new string[] { "工单编号", "承办单位", "工单内容", "转派时间", "回复时限", "超时/剩余", "工单状态" };
                var dtall = DbHelperSQL.Query("select * from V_Bus_NoDeal where 1=1 " + sql).Tables[0];
                dtall.Columns.Remove("F_MainDeptId");
                dtall.Columns.Remove("F_CreateTime");
                dtall.Columns.Remove("F_WorkState");
                new NPOIHelper().ExportToExcel($"单位未处理统计报表{start.Value.ToString("yyyyMMdd")}-{end.Value.ToString("yyyyMMdd")}", dtall, cols);
                return Success("导出excel");
            }

            int recordCount = 0;
            var dt = BLL.PagerBLL.GetListPager(
                "V_Bus_NoDeal",
                "F_WorkOrderId",
                "*",
                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 20190618 zhengbingbing 新增处理量统计按照时间筛选
        /// <summary>
        /// 处理量统计
        /// </summary>
        /// <param name="sdate"></param>
        /// <param name="edate"></param>
        /// <param name="isExport"></param>
        /// <returns></returns>
        public ActionResult GetDeptDayReport1(string sdate, string edate,int isdept=1, bool isExport = false)
        {
            if (string.IsNullOrEmpty(sdate))
            {
                sdate = DateTime.Now.Date.ToString("yyyy-MM-01");
            }
            if (string.IsNullOrEmpty(edate))
            {
                edate = DateTime.Now.Date.ToString("yyyy-MM-dd");
            }
            
            if (User.F_RoleCode == "GLY" || User.F_RoleCode == "SPZ" || User.F_RoleCode == "SPZJZ")
            {
                var maindeptid = 0;
                if (User.F_RoleCode == "WLDW")
                {
                    maindeptid = User.F_DeptId;
                }
                else if (User.F_RoleCode == "EJWLDW")
                {
                    maindeptid = User.F_DeptId;
                    isdept = 2;
                }
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", sdate);
            paras.Add("@edate", edate);
            paras.Add("@isdept", isdept.ToString ());
            var obj = DbHelperSQL.RunProcedure("P_DeptDayReport", paras, "DeptDayReport");
            if (isExport)
            {
                var cols = new string[] { "名称", "受理数量", "处理量", "延期量", "重办量", "结案量", "办理率", "办结率" };
                new NPOIHelper().ExportToExcel($"工单报表{sdate}-{edate}", obj.Tables[0], cols);
                return Success("导出excel");
            }
            return Success("成功", obj);
        }
        public ActionResult GetDeptDayReport(string sdate, string edate, int isdept = 0, bool isExport = false,string source="",int deptid=0 )
        {
            if (string.IsNullOrEmpty(sdate))
            {
                sdate = DateTime.Now.Date.ToString("yyyy-MM-01");
            }
            if (string.IsNullOrEmpty(edate))
            {
                edate = DateTime.Now.Date.ToString("yyyy-MM-dd");
            }
            
            if (User.F_RoleCode != "GLY" && User.F_RoleCode != "SPZ" && User.F_RoleCode != "SPZJZ")
            {
                if (User.F_RoleCode == "WLDW")
                {
                    isdept = User.F_DeptId;
                }
                else if (User.F_RoleCode == "EJWLDW")
                {
                    isdept = User.F_DeptId;
                }
            }
            string dept = "";
            if (isdept > 0)
                dept = isdept.ToString();
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", sdate);
            paras.Add("@edate", edate);
            paras.Add("@isdept", dept);
            paras.Add("@source", source);
            
            var obj = DbHelperSQL.RunProcedure("P_DeptDayReport_1", paras, "DeptDayReport");
            if (obj!=null && obj.Tables[0]!=null && obj.Tables[0].Rows.Count > 0) {
                for (int i = 0; i < obj.Tables[0].Rows.Count; i++)
                {
                    int acceptcount = 0, dealcount = 0, endcount = 0;double dealrate = 0, endrate = 0;
                    int.TryParse(obj.Tables[0].Rows[i]["acceptcount"].ToString(),out acceptcount);
                    int.TryParse(obj.Tables[0].Rows[i]["dealcount"].ToString(), out dealcount);
                    int.TryParse(obj.Tables[0].Rows[i]["endcount"].ToString(), out endcount);
                    if(acceptcount>0)
                    {
                        dealrate = double.Parse((dealcount * 100).ToString()) / acceptcount;
                        endrate = double.Parse((endcount * 100).ToString()) / acceptcount;
                        obj.Tables[0].Rows[i]["dealrate"] = dealrate.ToString("f") + "%";
                        obj.Tables[0].Rows[i]["endrate"] = endrate.ToString("f") + "%";
                    }
                    
                }
            }
            if (isExport)
            {
                var cols = new string[] { "名称", "受理数量", "处理量", "延期量", "重办量", "结案量", "办理率", "办结率" };
                new NPOIHelper().ExportToExcel($"处理数量统计报表{sdate.Substring(0, 10)}-{edate.Substring(0, 10)}", obj.Tables[0], cols);
                return Success("导出excel");
            }
            return Success("成功", obj);
        }
        
        #endregion
        #region  20191104 zhengbingbing 增加三级单位统计项
        /// <summary>
        /// 三级单位处理量统计
        /// </summary>
        /// <param name="sdate"></param>
        /// <param name="edate"></param>
        /// <param name="isExport"></param>
        /// <returns></returns>
        public ActionResult GetDept3DayReport1(string sdate, string edate, bool isExport = false)
        {
            if (string.IsNullOrEmpty(sdate))
            {
                sdate = DateTime.Now.Date.ToString("yyyy-MM-01");
            }
            if (string.IsNullOrEmpty(edate))
            {
                edate = DateTime.Now.Date.ToString("yyyy-MM-dd");
            }
            
            int deptid = 0;
            if (User.F_RoleCode != "GLY" && User.F_RoleCode != "SPZ" && User.F_RoleCode != "SPZJZ")
            {
                if (User.F_RoleCode == "WLDW" || User.F_RoleCode == "EJWLDW")
                {
                    deptid = User.F_DeptId;
                }
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", sdate);
            paras.Add("@edate", edate);
            paras.Add("@deptid", deptid.ToString());
            var obj = DbHelperSQL.RunProcedure("P_Dept3DayReport", paras, "Dept3DayReport");
            if (isExport)
            {
                var cols = new string[] { "单位名称", "受理数量", "处理量", "结案量", "办理率", "办结率" };
                new NPOIHelper().ExportToExcel($"工单报表{sdate}-{edate}", obj.Tables[0], cols);
                return Success("导出excel");
            }
            return Success("成功", obj);
        }
        public ActionResult GetDept3DayReport(string sdate, string edate, bool isExport = false,string source = "")
        {
            if (string.IsNullOrEmpty(sdate))
            {
                sdate = DateTime.Now.Date.ToString("yyyy-MM-01");
            }
            if (string.IsNullOrEmpty(edate))
            {
                edate = DateTime.Now.Date.ToString("yyyy-MM-dd");
            }
            
            int deptid = 0;
            if (User.F_RoleCode != "GLY" && User.F_RoleCode != "SPZ" && User.F_RoleCode != "SPZJZ")
            {
                if (User.F_RoleCode == "WLDW" || User.F_RoleCode == "EJWLDW")
                {
                    deptid = User.F_DeptId;
                }
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", sdate);
            paras.Add("@edate", edate);
            paras.Add("@deptid", deptid.ToString());
            paras.Add("@source", source);
            var obj = DbHelperSQL.RunProcedure("P_Dept3DayReport_1", paras, "Dept3DayReport");
            if (obj != null && obj.Tables[0] != null && obj.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < obj.Tables[0].Rows.Count; i++)
                {
                    int acceptcount = 0, dealcount = 0, endcount = 0; double dealrate = 0, endrate = 0;
                    int.TryParse(obj.Tables[0].Rows[i]["acceptcount"].ToString(), out acceptcount);
                    int.TryParse(obj.Tables[0].Rows[i]["dealcount"].ToString(), out dealcount);
                    int.TryParse(obj.Tables[0].Rows[i]["endcount"].ToString(), out endcount);
                    if (acceptcount > 0)
                    {
                        dealrate = double.Parse((dealcount * 100).ToString()) / acceptcount;
                        endrate = double.Parse((endcount * 100).ToString()) / acceptcount;
                        obj.Tables[0].Rows[i]["dealrate"] = dealrate.ToString("f") + "%";
                        obj.Tables[0].Rows[i]["endrate"] = endrate.ToString("f") + "%";
                    }

                }
            }
            if (isExport)
            {
                var cols = new string[] { "单位名称", "受理数量", "处理量", "结案量", "办理率", "办结率" };
                new NPOIHelper().ExportToExcel($"工单报表{sdate}-{edate}", obj.Tables[0], cols);
                return Success("导出excel");
            }
            return Success("成功", obj);
        }
        /// <summary>
        /// 三级单位满意度统计
        /// </summary>
        /// <param name="sdate"></param>
        /// <param name="edate"></param>
        /// <param name="isExport"></param>
        /// <returns></returns>
        public ActionResult GetDept3SatisfiedReport(string sdate, string edate, bool isExport = false, string source = "")
        {
            if (string.IsNullOrEmpty(sdate))
            {
                sdate = DateTime.Now.Date.ToString("yyyy-MM-01");
            }
            if (string.IsNullOrEmpty(edate))
            {
                edate = DateTime.Now.Date.ToString("yyyy-MM-dd");
            }
            
            int deptid = 0;
            if (User.F_RoleCode != "GLY" && User.F_RoleCode != "SPZ" && User.F_RoleCode != "SPZJZ")
            {
                if (User.F_RoleCode == "WLDW" || User.F_RoleCode == "EJWLDW")
                {
                    deptid = User.F_DeptId;
                }
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", sdate);
            paras.Add("@edate", edate);
            paras.Add("@deptid", deptid.ToString());
            paras.Add("@source", source);
            var obj = DbHelperSQL.RunProcedure("P_Dept3SatisfiedReport", paras, "Dept3SatisfiedReport");
            if (isExport)
            {
                string[] cols = new string[] { "单位名称", "处理总量", "满意总量", "不满意总量", "满意度" };
                new NPOIHelper().ExportToExcel($"满意度统计报表{Convert.ToDateTime(sdate).ToString("yyyyMMdd")}-{Convert.ToDateTime(edate).ToString("yyyyMMdd")}", obj.Tables[0], cols);
                return Success("导出excel");
            }
            return Success("成功", obj);
        }
        /// <summary>
        /// 三级单位处理效率统计
        /// </summary>
        /// <param name="sdate"></param>
        /// <param name="edate"></param>
        /// <param name="isExport"></param>
        /// <returns></returns>
        public ActionResult GetDept3EfficiencyReport(string sdate, string edate, bool isExport = false, string source = "")
        {
            if (string.IsNullOrEmpty(sdate))
            {
                sdate = DateTime.Now.Date.ToString("yyyy-MM-01");
            }
            if (string.IsNullOrEmpty(edate))
            {
                edate = DateTime.Now.Date.ToString("yyyy-MM-dd");
            }
            
            int deptid = 0;
            if (User.F_RoleCode != "GLY" && User.F_RoleCode != "SPZ" && User.F_RoleCode != "SPZJZ")
            {
                if (User.F_RoleCode == "WLDW" || User.F_RoleCode == "EJWLDW")
                {
                    deptid = User.F_DeptId;
                }
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", sdate);
            paras.Add("@edate", edate);
            paras.Add("@deptid", deptid.ToString());
            paras.Add("@source", source);
            var obj = DbHelperSQL.RunProcedure("P_Dept3EfficiencyReport", paras, "Dept3EfficiencyReport");
            if (isExport)
            {
                string[] cols = new string[] { "单位名称", "处理总量", "及时量", "超时量", "处理及时率" };
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcel("处理效率统计报表", obj.Tables[0], cols) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            return Success("成功", obj);
        }
        public ActionResult GetDept3EfficiencyReport1(string sdate, string edate, bool isExport = false)
        {
            if (string.IsNullOrEmpty(sdate))
            {
                sdate = DateTime.Now.Date.ToString("yyyy-MM-01");
            }
            if (string.IsNullOrEmpty(edate))
            {
                edate = DateTime.Now.Date.ToString("yyyy-MM-dd");
            }
            int deptid = 0;
            if (User.F_RoleCode != "GLY" && User.F_RoleCode != "SPZ" && User.F_RoleCode != "SPZJZ")
            {
                if (User.F_RoleCode == "WLDW" || User.F_RoleCode == "EJWLDW")
                {
                    deptid = User.F_DeptId;
                }
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", sdate);
            paras.Add("@edate", edate);
            paras.Add("@deptid", deptid.ToString());
            var obj = DbHelperSQL.RunProcedure("P_Dept3EfficiencyReport_1", paras, "Dept3EfficiencyReport");
            var newSet = new DataSet();
            if (obj != null && obj.Tables[0] != null && obj.Tables[0].Rows.Count > 0)
            {
                obj.Tables[0].Columns.Add("rate", typeof(decimal));
                for (int i = 0; i < obj.Tables[0].Rows.Count; i++)
                {
                    int completedcount = 0, timelycount = 0, timeoutcount = 0; double timelyrate = 0;
                    int.TryParse(obj.Tables[0].Rows[i]["completedcount"].ToString(), out completedcount);
                    int.TryParse(obj.Tables[0].Rows[i]["timelycount"].ToString(), out timelycount);
                    int.TryParse(obj.Tables[0].Rows[i]["timeoutcount"].ToString(), out timeoutcount);
                    timeoutcount = completedcount - timelycount;
                    obj.Tables[0].Rows[i]["timeoutcount"] = timeoutcount;

                    if (completedcount > 0)
                    {
                        timelyrate = double.Parse((timelycount * 100).ToString()) / completedcount;
                        obj.Tables[0].Rows[i]["timelyrate"] = timelyrate.ToString("f") + "%";
                        obj.Tables[0].Rows[i]["rate"] = timelyrate;
                    }
                }
                obj.Tables[0].DefaultView.Sort = "rate desc";
                newSet.Tables.Add(obj.Tables[0].DefaultView.ToTable());
                newSet.Tables[0].Columns.Remove("rate");
            }
            if (isExport)
            {
                string[] cols = new string[] { "单位名称", "处理总量", "及时量", "超时量", "处理及时率" };
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcel("处理效率统计报表", newSet.Tables[0], cols) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            return Success("成功", newSet);
        }
        #endregion
        /// <summary>
        /// 单位月统计
        /// </summary>
        /// <param name="date"></param>
        /// <param name="isExport"></param>
        /// <returns></returns>
        public ActionResult GetDeptMonthReport(string date, bool isExport = false)
        {
            if (string.IsNullOrEmpty(date))
            {
                date = DateTime.Now.Date.ToString("yyyy-MM");
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@date", date);
            var obj = DbHelperSQL.RunProcedure("P_DeptMonthReport", paras, "DeptMonthReport");
            if (isExport)
            {
                var cols = new string[] { "名称", "当月受理数量", "当月处理量", "往月处理量", "当月延期量", "往月延期量", "当月重办量", "往月重办量", "当月结案量", "当月办理率", "当月办结率" };
                new NPOIHelper().ExportToExcel($"工单报表{date}", obj.Tables[0], cols);
                return Success("导出excel");
            }
            return Success("成功", obj);
        }
        

        /// <summary>
        /// 单位办理率统计
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="isExport"></param>
        /// <returns></returns>
        public ActionResult GetDeptEndRate(string month, int type = 0, bool isExport = false)
        {
            string start = "", end = "";
            if (month != null)
            {
                var date = DateTime.Parse(month + "-01");
                start = date.ToString("yyyy-MM-dd"); ;
                end = date.AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
            }

            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", start);
            paras.Add("@edate", end);
            paras.Add("@type", type.ToString());
            var obj = DbHelperSQL.RunProcedure("P_DeptEndRateReport", paras, "DeptEndRateReport");
            if (isExport)
            {
                var cols = new string[] { "单位", "受理数量", "办理数量", "结案数量", "结案率" };
                new NPOIHelper().ExportToExcel($"单位办理率统计{month}", obj.Tables[0], cols, 1);
                return Success("导出excel");
            }
            return Success("成功", obj);
        }

        /// <summary>
        /// 工单热点分布
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="deptid"></param>
        /// <param name="keyid"></param>
        /// <param name="sourcearea"></param>
        /// <param name="source"></param>
        /// <returns></returns>
        public ActionResult GetHotspotMap(DateTime? start, DateTime? end, int deptid = 0, int keyid = 0,int sourcearea = 0, int source=0)
        {
            if (start == null)
            {
                start = DateTime.Now;
                //start = new DateTime(start.Value.Year, start.Value.Month, 1);
                start = DateTime.Now.AddDays(-3);
            }
            if (end == null)
            {
                end = DateTime.Now;
            }
            var where = " and ISNUMERIC(F_Longitude)>0 and ISNUMERIC(F_Latitude)>0 ";
            if (deptid > 0)
                where += " and F_WorkOrderID in(select F_WorkOrderID from T_Bus_AssignedInfo where F_MainDeptId = '" + deptid + "' and F_IsSure in (0,1) and F_State=1 and F_IsDelete=0 )";
            if (keyid > 0)
                where += " and ','+F_Key+',' like '%," + keyid + ",%' ";
            if (sourcearea > 0)
                where += " and F_SourceArea='" + sourcearea+"' ";
            if (source > 0)
                where += " and F_InfoSource = '" + source + "' ";

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

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

            return Success("",list);
        }
        #region 20191121 zhengbingbing 新增质检工作统计：工单回访量、回访量、结案量、工单重办量、重办量
        public ActionResult GetQualityAssessment(DateTime? start, DateTime? end, bool isExport = false)
        {
            if (start == null)
            {
                start = DateTime.Now;
                start = new DateTime(start.Value.Year, start.Value.Month, 1);
            }
            if (end == null)
            {
                end = DateTime.Now;
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
            paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
            var wherevi = " and F_CreateTime between '"+ start.Value.ToString("yyyy-MM-dd 00:00:00") + "' and '"+ end.Value.ToString("yyyy-MM-dd 23:59:59") + "'";
            var whereja = " and F_CloseTime between '" + start.Value.ToString("yyyy-MM-dd 00:00:00") + "' and '" + end.Value.ToString("yyyy-MM-dd 23:59:59") + "'";
            string sql = "select '('+F_UserCode+')'+F_UserName 姓名" +
",(select COUNT(1) from(select F_CreateUser, F_WorkOrderId from T_Bus_VisitResult where F_IsDelete = 0 "+ wherevi + " group by F_CreateUser, F_WorkOrderId) t where F_CreateUser = F_UserCode) 工单回访量" +
",(select COUNT(1) from T_Bus_VisitResult where  F_IsDelete = 0 " + wherevi + " and F_CreateUser = F_UserCode and F_IsDelete = 0) 回访量" +
",(select COUNT(1) from T_Bus_WorkOrder where F_WorkState = 9 and F_IsDelete = 0 and F_CloseUser = F_UserCode "+ whereja + ") 结案量" +
",(select COUNT(1) from(select F_CreateUser, F_WorkOrderId from T_Bus_Operation where F_IsDelete = 0 "+ wherevi + " and F_State = 11 and F_WorkOrderId in (select F_WorkOrderId from T_Bus_Operation where F_IsDelete = 0 and F_State = 8) group by F_CreateUser,F_WorkOrderId) tt where F_CreateUser = F_UserCode) 工单重办量" +
",(select COUNT(1) from T_Bus_Operation where F_IsDelete = 0 and F_State = 11 and F_CreateUser = F_UserCode " + wherevi + ") 重办量" +
      " from T_Sys_UserAccount where F_RoleId = 18 and F_DeleteFlag = 0 order by F_UserCode";
            var obj = DbHelperSQL.Query(sql);
            if (isExport)
            {
                string[] cols = new string[] { "姓名", "工单回访量", "回访量", "结案量", "工单重办量", "重办量" };
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcel("质检工作统计报表", obj.Tables[0], cols) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            return Success("成功", obj);
        }
        #endregion
       /// <summary>
       /// 
       /// </summary>
       /// <param name="start"></param>
       /// <param name="end"></param>
       /// <param name="isExport"></param>
       /// <returns></returns>
        public ActionResult GetAudithandling(DateTime? start, DateTime? end, bool isExport = false)
        {
            if (start == null)
            {
                start = DateTime.Now;
                start = new DateTime(start.Value.Year, start.Value.Month, 1);
            }
            if (end == null)
            {
                end = DateTime.Now;
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd"));
            paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
            var obj = DbHelperSQL.RunProcedure("P_Audithandling", paras, "Audithandling");
            if (isExport)
            {
                var cols = new string[] { "单位名称", "办理件数量", "退回件数量", "延时件数量","超期未果数量" };
                new NPOIHelper().ExportToExcel($"审核办理工单统计{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", obj.Tables[0], cols);
                return Success("导出excel");
            }
            return Success("成功", obj);
        }



    }
}