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.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;

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, int pageIndex = 1, int pageSize = 10, bool isExport = false)
        //{
        //    if (start == null)
        //    {
        //        start = DateTools.GetWeekFirstDayMon(DateTime.Now);
        //    }
        //    if (end == null)
        //    {
        //        end = DateTools.GetWeekLastDaySun(DateTime.Now);
        //    }

        //    //部门
        //    var deptList = departmentBLL.DataTableToList(departmentBLL.GetList(" F_DeptNameSpell=3 ").Tables[0]);
        //    var distinctQuery = feedbackBLL.DataTableToList(feedbackBLL.GetList($"  F_IsFeedEnd=1 and F_Type in (1,2) and  F_State=1 and F_IsDelete not in (1)  and  datediff(s,'{start}',F_CreateTime)>0  and   datediff(s,'{end}',F_CreateTime)<0  ").Tables[0]).DistinctBy(x => x.F_WorkOrderId).ToList();
        //    List<DeptCountDto> list = new List<DeptCountDto>();
        //    foreach (var item in deptList)
        //    {
        //        var count = distinctQuery.Where(y => y.F_CreateDeptId == item.F_DeptId).Count();
        //        list.Add(new DeptCountDto
        //        {
        //            deptname = item.F_DeptName ?? "",
        //            count = count
        //        });
        //    }

        //    if (isExport)
        //    {
        //        var dt = list.ToDataTable(new string[] { "deptname", "count" });
        //        new NPOIHelper().ExportToExcel($"处理数量统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", dt, new string[] { "单位名称", "数量" });
        //        return Success("导出excel");
        //    }
        //    var obj = new
        //    {
        //        rows = list.Skip((pageIndex - 1) * pageSize).Take(pageSize),
        //        total = list.Count
        //    };

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

        /// <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 GetDeptEfficiency(DateTime? start, DateTime? end, int pageIndex = 1, int pageSize = 10, bool isExport = false)
        //{
        //    if (start == null)
        //    {
        //        start = DateTime.Now.Date.AddDays(-6);
        //    }
        //    if (end == null)
        //    {
        //        end = DateTime.Now.Date.AddDays(1).AddSeconds(-1);
        //    }

        //    //部门
        //    var deptList = departmentBLL.DataTableToList(departmentBLL.GetList(" F_DeptNameSpell=3 ").Tables[0]);
        //    var workOrderList = new BLL.T_Bus_WorkOrderReport().DataTableToEfficiencyList(DbHelperSQL.Query($"SELECT  A.F_WorkOrderId,C.F_CreateDeptId,B.F_IsOverdue,B.F_IsSatisfie FROM T_Bus_WorkOrder A LEFT JOIN T_Bus_AssignedInfo B ON A.F_WorkOrderId = B.F_WorkOrderId LEFT JOIN T_Bus_Feedback C ON A.F_WorkOrderId = C.F_WorkOrderId WHERE  datediff(s, '{start}', A.F_CreateTime) > 0  and   datediff(s, '{end}', 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) AND C.F_IsFeedEnd = 1 and C.F_Type in (1,2) and C.F_State = 1 and C.F_IsDelete not in (1) ").Tables[0]).DistinctBy(x => x.WorkOrderId).ToList();

        //    //var feedbackList = feedbackBLL.DataTableToList(feedbackBLL.GetList($" F_IsFeedEnd=1 and F_Type in (1,2) and  F_State=1 and F_IsDelete not in (1)  and  datediff(s,'{start}',F_CreateTime)>0  and   datediff(s,'{end}',F_CreateTime)<0  ").Tables[0]).DistinctBy(x => x.F_WorkOrderId).ToList();
        //    //var assignedList = assignedInfoBLL.DataTableToList(assignedInfoBLL.GetList($" F_IsDelete=0 and F_IsSure in (0,1,2) and  F_State in (0,1)   and  datediff(s,'{start}',F_CreateTime)>0  and   datediff(s,'{end}',F_CreateTime)<0  ").Tables[0]).DistinctBy(x => x.F_WorkOrderId);

        //    //var deptJoinAssignedQ = deptList.Join(
        //    //                        assignedList,
        //    //                        a => a.F_DeptId,
        //    //                        b => b.F_MainDeptId,
        //    //                        (a, b) => new { a.F_DeptName, b.F_WorkOrderId, b.F_IsOverdue });
        //    //var feedbackS = feedbackList.Select(x => x.F_WorkOrderId).ToList();
        //    //var deptJoinAssignedQW = deptJoinAssignedQ.Where(x => feedbackS.Contains(x.F_WorkOrderId)).ToList();

        //    List<DeptEfficiencyDto> list = new List<DeptEfficiencyDto>();
        //    deptList.ForEach(x =>
        //    {
        //        var deptWorkOrderList = workOrderList.Where(y => x.F_DeptId == y.DeptId).ToList();

        //        var deptname = x.F_DeptName ?? "";
        //        var count = deptWorkOrderList.Count();
        //        var intimeCount = deptWorkOrderList.Where(y => y.IsOverdue).Count();
        //        var outtimeCount = deptWorkOrderList.Where(y => y.IsSatisfie).Count();
        //        list.Add(new DeptEfficiencyDto
        //        {
        //            deptname = x.F_DeptName ?? "",
        //            count = count,
        //            intimecount = intimeCount,
        //            outtimecount = outtimeCount,
        //            efficrate = count == 0 ? "0%" : (intimeCount == 0 ? "0%" : ((double)intimeCount / count * 100).ToString("f2") + "%")
        //        });
        //    });

        //    if (isExport)
        //    {
        //        var dt = list.ToDataTable(new string[] { "deptname", "count", "intimecount", "outtimecount", "efficrate" });
        //        new NPOIHelper().ExportToExcel($"处理效率统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", dt, new string[] { "单位名称", "处理总量", "及时量", "超时量", "处理及时率" });
        //        return Success("导出excel");
        //    }
        //    var obj = new
        //    {
        //        rows = list.Skip((pageIndex - 1) * pageSize).Take(pageSize),
        //        total = list.Count
        //    };

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

        ///// <summary>
        ///// 市民满意度评价
        ///// </summary>
        ///// <returns></returns>
        //public ActionResult GetDeptSatisfied(DateTime? start, DateTime? end, int pageIndex = 1, int pageSize = 10, bool isExport = false)
        //{
        //    if (start == null)
        //    {
        //        start = DateTime.Now.Date.AddDays(-6);
        //    }
        //    if (end == null)
        //    {
        //        end = DateTime.Now.Date.AddDays(1).AddSeconds(-1);
        //    }

        //    //部门
        //    var deptList = departmentBLL.DataTableToList(departmentBLL.GetList(" F_DeptNameSpell=3 ").Tables[0]);
        //    var feedbackList = feedbackBLL.DataTableToList(feedbackBLL.GetList($"  F_IsFeedEnd=1 and F_Type in (1,2) and  F_State=1 and F_IsDelete not in (1)  and  datediff(s,'{start}',F_CreateTime)>0  and   datediff(s,'{end}',F_CreateTime)<0  ").Tables[0]).DistinctBy(x => x.F_WorkOrderId);
        //    var assignedList = assignedInfoBLL.DataTableToList(assignedInfoBLL.GetList($" F_IsDelete=0 and F_IsSure in (0,1,2) and  F_State in (0,1)   and  datediff(s,'{start}',F_CreateTime)>0  and   datediff(s,'{end}',F_CreateTime)<0  ").Tables[0]).DistinctBy(x => x.F_WorkOrderId);
        //    var deptJoinAssignedQ = deptList.Join(
        //                            assignedList,
        //                            a => a.F_DeptId,
        //                            b => b.F_MainDeptId,
        //                            (a, b) => new { a.F_DeptName, b.F_WorkOrderId, b.F_IsSatisfie });
        //    var feedbackS = feedbackList.Select(x => x.F_WorkOrderId).ToList();
        //    var deptJoinAssignedQW = deptJoinAssignedQ.Where(x => feedbackS.Contains(x.F_WorkOrderId)).ToList();
        //    List<DeptSatisfiedDto> list = new List<DeptSatisfiedDto>();
        //    deptList.ForEach(x =>
        //    {
        //        var deptJoinAssignedQWW = deptJoinAssignedQW.Where(y => y.F_DeptName == x.F_DeptName).ToList();

        //        var deptname = x.F_DeptName ?? "";
        //        var count = deptJoinAssignedQWW.Count();
        //        var satisfiedCount = deptJoinAssignedQWW.Where(y => y.F_IsSatisfie == 0).Count();
        //        var notsatisfiedCount = deptJoinAssignedQWW.Where(y => y.F_IsSatisfie == 1).Count();
        //        list.Add(new DeptSatisfiedDto
        //        {
        //            deptname = x.F_DeptName ?? "",
        //            count = count,
        //            satisfiedcount = satisfiedCount,
        //            notsatisfiedcount = notsatisfiedCount,
        //            satisfiedrate = count == 0 ? "0%" : (satisfiedCount == 0 ? "0%" : ((double)satisfiedCount / count * 100).ToString("f2") + "%")
        //        });
        //    });
        //    if (isExport)
        //    {
        //        var dt = list.ToDataTable(new string[] { "deptname", "count", "satisfiedcount", "notsatisfiedcount", "satisfiedrate" });
        //        new NPOIHelper().ExportToExcel($"市民满意度评价报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", dt, new string[] { "单位名称", "处理总量", "满意总量", "处理总量", "满意度" });
        //        return Success("导出excel");
        //    }
        //    var obj = new
        //    {
        //        rows = list.Skip((pageIndex - 1) * pageSize).Take(pageSize),
        //        total = list.Count
        //    };

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

        /// <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="",int type=0)
        {
            if (start == null)
            {
                start = DateTime.Now;
                start = new DateTime(start.Value.Year, start.Value.Month, 1);
            }
            if (end == null)
            {
                end = DateTime.Now;
            }
            Dictionary<string, string> paras = new Dictionary<string, string>();
            paras.Add("@sdate", start.Value.ToString("yyyy-MM-dd HH:mm:ss"));
            paras.Add("@edate", end.Value.ToString("yyyy-MM-dd HH:mm:ss"));
            paras.Add("@source", source);
            var obj = DbHelperSQL.RunProcedure("P_DeptEfficiencyReport_1", paras, "DeptEfficiencyReport");
            var newSet = new DataSet();
          
            DataTable exceldt = new DataTable();
            DataSet  newSet1 = new DataSet();

            if (type > 0)
            {
                exceldt.Columns.Add("ranking", typeof(string));//
            }
            exceldt.Columns.Add("deptname", typeof(string));//单位名称
            exceldt.Columns.Add("slcount", typeof(int));//受理量
            exceldt.Columns.Add("bjcount", typeof(int));//办结量
            exceldt.Columns.Add("timelycount", typeof(int));//及时量
            exceldt.Columns.Add("timeoutcount", typeof(int));//延期量
            exceldt.Columns.Add("remindcount", typeof(int));//催办量
            exceldt.Columns.Add("zzreloadcount", typeof(string));//郑州重办量
            exceldt.Columns.Add("bdreloadcount", typeof(string));//本地重办量
            exceldt.Columns.Add("bjlyrate", typeof(string));//办结率
            exceldt.Columns.Add("yqlyrate", typeof(string));//延期率
            if (type>0)
            {
                exceldt.Columns.Add("timelyrate", typeof(string));//及时率
                exceldt.Columns.Add("timely", typeof(string));//及时
            }
           

            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++)
                {
                    var deptname = obj.Tables[0].Rows[i]["deptname"].ToString();
                    int completedcount = 0, timelycount = 0, timeoutcount = 0; double timelyrate = 0;
                    int slcount = 0;//受理量
                    int bjcount = 0;//办结量
                    int remindcount = 0;//催办量
                    int zzreloadcount = 0;//郑州重办量
                    int bdreloadcount = 0;//本地重办量
                    double bjlyrate = 0;//办结率
                    double yqlyrate = 0;// 延期率
                    
                    #region 赋值
                    int.TryParse(obj.Tables[0].Rows[i]["slcount"].ToString(), out slcount);
                    int.TryParse(obj.Tables[0].Rows[i]["bjcount"].ToString(), out bjcount);
                    int.TryParse(obj.Tables[0].Rows[i]["timelycount"].ToString(), out timelycount);
                    int.TryParse(obj.Tables[0].Rows[i]["timeoutcount"].ToString(), out timeoutcount);
                    int.TryParse(obj.Tables[0].Rows[i]["remindcount"].ToString(), out remindcount);
                    int.TryParse(obj.Tables[0].Rows[i]["zzreloadcount"].ToString(), out zzreloadcount);
                    int.TryParse(obj.Tables[0].Rows[i]["bdreloadcount"].ToString(), out bdreloadcount);
                    int.TryParse(obj.Tables[0].Rows[i]["completedcount"].ToString(), out completedcount);
                    //  timeoutcount = completedcount - timelycount;
                    // obj.Tables[0].Rows[i]["timeoutcount"] = timeoutcount;
                    if (slcount > 0)
                    {
                        bjlyrate = (double)bjcount / (double)slcount;
                        obj.Tables[0].Rows[i]["bjlyrate"] = bjlyrate.ToString("0.00%");//办结率

                        yqlyrate = (double)timeoutcount / (double)slcount;
                        obj.Tables[0].Rows[i]["yqlyrate"] = yqlyrate.ToString("0.00%");//办结率

                        if (type > 0)
                        {
                          //  timelyrate = (double)timelycount / (double)slcount;
                           // obj.Tables[0].Rows[i]["timely"] = timelyrate;
                          //  obj.Tables[0].Rows[i]["timelyrate"] = timelyrate.ToString("0.00%");//及时率

                        }
                          
                    }
                    //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;
                    //} 
                    #endregion
                    #region 赋值
                    DataRow dr = exceldt.NewRow();
                    dr["deptname"] = deptname;
                    dr["slcount"] = slcount.ToString();
                    dr["bjcount"] =bjcount       .ToString();
                    dr["timelycount"] =timelycount   .ToString();
                    dr["timeoutcount"] =timeoutcount  .ToString();
                    dr["remindcount"] =remindcount   .ToString();
                    dr["zzreloadcount"] =zzreloadcount .ToString();
                    dr["bdreloadcount"] = bdreloadcount.ToString();
                    if (slcount > 0)
                    {
                        bjlyrate = (double)bjcount / (double)slcount;
                        dr["bjlyrate"] = bjlyrate.ToString("0.00%");//办结率

                        yqlyrate = (double)timeoutcount / (double)slcount;
                        dr["yqlyrate"] = yqlyrate.ToString("0.00%");//办结率
                        if (type > 0)
                        {
                            timelyrate = (double)timelycount / (double)slcount;
                            dr["timely"] = timelyrate;
                            dr["timelyrate"] = timelyrate.ToString("0.00%");//及时率
                        }
                    }
                    else
                    {
                        if (type > 0)
                        {
                            dr["timely"] = 0;
                        }
                    }
                   
                    exceldt.Rows.Add(dr);
                    #endregion
                }
            
                if (type >0)
                {
                     exceldt.DefaultView.Sort = "timely desc";
                    exceldt = exceldt.DefaultView.ToTable();
                    for (int i = 0; i < exceldt.Rows.Count; i++)
                    {
                        exceldt.Rows[i]["ranking"] = i + 1;
                    }
                    exceldt.Columns.Remove("timely");
                }
                else
                {
                    newSet1.Tables.Add(exceldt);
                    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 (type >0)
            {
                if (isExport)
                {
                    string[] cols = new string[] {"排名", "单位名称", "受理量", "办结量", "及时量", "延期量", "催办量", "郑州重办量", "本地重办量", "办结率", "延期率","及时率" };
                    NPOIHelper npoi = new NPOIHelper();
                    if (npoi.ExportToExcel("处理效率统计报表", exceldt, cols) == "")
                    {
                        return Success("导出成功");
                    }
                    else
                    {
                        return Error("导出失败");
                    }
                }
                return Success("成功", exceldt);
            }
            else
            {
                if (isExport)
                {
                    string[] cols = new string[] { "单位名称", "受理量", "办结量", "及时量", "延期量", "催办量", "郑州重办量", "本地重办量", "办结率", "延期率" };
                    NPOIHelper npoi = new NPOIHelper();
                    if (npoi.ExportToExcel("处理效率统计报表", newSet1.Tables[0], cols) == "")
                    {
                        return Success("导出成功");
                    }
                    else
                    {
                        return Error("导出失败");
                    }
                }
                return Success("成功", newSet);
            }
          
        }
  
        /// <summary>
        /// 市民满意度评价
        /// </summary>
        /// <returns></returns>
        public ActionResult GetDeptSatisfied(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;
            }
           
            List<deptSatisfiedReport> DeptSatisfiedReport = new List<deptSatisfiedReport>();
            var workorder = new BLL.T_Bus_AssignedInfo().GetModelList
                ("   F_Id in ( select MAX (F_Id )  from T_Bus_AssignedInfo    where "+
        " F_IsDelete = 0 and F_IsSure = 1 and F_State = 1 "+
         "  and isnull(F_FeedbackTime, '') <> '' "+
       " and datediff(day, F_FeedbackTime, '" + start + "') <= 0 " +
        "and datediff(day, F_FeedbackTime, '" + end + "') >= 0 and " +
        " F_WorkOrderId in (select F_WorkOrderId from T_Bus_WorkOrder "+
        " where F_WorkState = 9 and F_IsDelete = 0)  group by F_WorkOrderId,F_MainDeptId  ) ");

            var vis = "select * from T_Bus_AssignedInfo a INNER JOIN T_Bus_VisitResult v  on a.F_Id = v.F_AssignedId WHERE " +
                "v.F_IsDelete = 0 and v.F_State = 1 and a.F_Id IN((select max(F_Id)" +
                " from T_Bus_AssignedInfo where F_IsDelete = 0 and F_State = 1 and isnull(F_FeedbackTime, '') <> ''" +
                "  and datediff(day, F_FeedbackTime, '"+ start + "') <= 0 and datediff(day, F_FeedbackTime, '"+ end + "') >= 0" +
                "  and F_WorkOrderId in (select F_WorkOrderId from T_Bus_WorkOrder where F_WorkState = 9 and F_IsDelete = 0)   " +
                "  group by F_WorkOrderId,F_MainDeptId))";
            DataTable visit = DbHelperSQL.Query(vis).Tables[0];
            List<Model.T_Bus_AssignedInfo> visitlist = new BLL.T_Bus_AssignedInfo().DataTableToList(visit);
         

            var deptMent = new BLL.T_Sys_Department().GetModelList(" F_State=0 AND F_IsDept='1'  ");
            if (deptMent!=null )
            {
               foreach (var it  in deptMent)
                {
                    deptSatisfiedReport deptSatisfied = new deptSatisfiedReport();
                    deptSatisfied.count = 0;
                    deptSatisfied.deptname = it.F_DeptName;
                    deptSatisfied.notsatisfiedcount = 0;
                    deptSatisfied.satisfiedcount = 0;
                    deptSatisfied.satisfiedrate = "";
                    deptSatisfied.fiedrate = 0;
                    deptSatisfied.deptid = it.F_DeptId;
                    int   visits = 0;
                    if (workorder !=null )
                    {
                        foreach (var iv in workorder)
                        {
                            if (iv.F_MainDeptId == it.F_DeptId)
                                deptSatisfied.count++;
                        }
                        if (visit!=null )
                        {
                            foreach (var iv in visitlist)
                            {
                                if (iv.F_MainDeptId == it.F_DeptId)
                                {
                                    visits++;
                                    if ( iv.F_IsSatisfie == 1)
                                        deptSatisfied.satisfiedcount++;
                                }
                                
                              
                            }
                        }
                        deptSatisfied.notsatisfiedcount = visits - deptSatisfied.satisfiedcount;
                        if (deptSatisfied.count > 0)
                        {
                            deptSatisfied.fiedrate = (float)deptSatisfied.satisfiedcount / deptSatisfied.count;
                            deptSatisfied.satisfiedrate = string.Format("{0:f2}%",(float )deptSatisfied.satisfiedcount / deptSatisfied.count * 100);
                        }
                        DeptSatisfiedReport.Add(deptSatisfied);
                    }
                    else
                    {
                        DeptSatisfiedReport.Add(deptSatisfied);
                    }
                }
            }
        
           var result = DeptSatisfiedReport.OrderBy(x => x .deptid ).OrderByDescending (x => x.fiedrate);
            if (isExport)
            {
                string[] col = new string[] { "单位名称", "处理总量", "满意总量", "不满意总量", "满意度" };
                new NPOIHelper().SatisfiedToExcel($"满意度统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", result, col);
                return Success("导出excel");
                //   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_DeptSatisfiedReport", paras, "DeptSatisfiedReport");
                //  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 = result,
            };
            return Success("成功", ob );
         
        }


        /// <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, int deptId = -1, bool isExport = false)
        //{
        //    StringBuilder sb1 = new StringBuilder();
        //    StringBuilder sb2 = new StringBuilder();
        //    if (start == null)
        //    {
        //        start = DateTime.Now.AddDays(-7);
        //    }
        //    if (end == null)
        //    {
        //        end = DateTime.Now.AddDays(1).AddSeconds(-1);
        //    }
        //    //部门
        //    var deptList = departmentBLL.DataTableToList(departmentBLL.GetList(" F_DeptNameSpell=3 ").Tables[0]);
        //    //var feedbackList = feedbackBLL.DataTableToList(feedbackBLL.GetList($"  F_IsFeedEnd=1 and F_Type in (1,2) and  F_State=1 and F_IsDelete not in (1)  and  datediff(s,'{start}',F_CreateTime)>0  and   datediff(s,'{end}',F_CreateTime)<0 ").Tables[0]).DistinctBy(y => y.F_WorkOrderId).ToList();
        //    var assignedList = assignedInfoBLL.DataTableToList(assignedInfoBLL.GetList($" F_IsDelete = 0 and F_IsSure in (0, 1, 2) and  F_State in (0, 1)   and datediff(s,'{start}', F_CreateTime)> 0  and datediff(s,'{end}', F_CreateTime)< 0  ").Tables[0]);
        //    var visitList = visitResultBLL.DataTableToList(visitResultBLL.GetList($"   datediff(s,'{start}',F_CreateTime)>0  and   datediff(s,'{end}',F_CreateTime)<0   ").Tables[0]);
        //    var delayList = delayTimeBLL.DataTableToList(delayTimeBLL.GetList($"  datediff(s,'{start}',F_CreateTime)>0  and   datediff(s,'{end}',F_CreateTime)<0  ").Tables[0]);
        //    List<DeptAssessmentDto> list = new List<DeptAssessmentDto>();
        //    double satisfiedsum = 0;
        //    double notsatisfiedsum = 0;
        //    double intimsum = 0;

        //    deptList.ForEach(x =>
        //    {
        //        var deptname = x.F_DeptName ?? "";
        //        if (deptname == "市公安局")
        //        {
        //            var t = 1;
        //        }
        //        //交办总量
        //        var query = assignedList.Where(y => y.F_MainDeptId == x.F_DeptId).DistinctBy(y => y.F_WorkOrderId).ToList();
        //        var workOrderIdQuery = query.Select(y => y.F_WorkOrderId).ToList();
        //        var assignedcount = query.Count();
        //        //已办理总量
        //        //var completedcount = feedbackList.Where(y => y.F_CreateDeptId == x.F_DeptId ).Count();
        //        var completedcount = query.Where(y => y.F_FeedbackTime != null).Count();
        //        //及时量
        //        double intimecount = query.Where(y =>y.F_FeedbackTime != null && y.F_LimitTime>=y.F_FeedbackTime).Count();
        //        intimsum += intimecount;
        //        //超时量
        //        var outtimeQuery = query.Where(y =>y.F_FeedbackTime != null && y.F_LimitTime >= y.F_FeedbackTime).ToList();
        //        //超时均长
        //        var outtimecount = outtimeQuery.Count();
        //        var avgouttime = (from o in outtimeQuery
        //                          select new
        //                          {
        //                              differ = (DateTime)o.F_FeedbackTime - (DateTime)o.F_LimitTime
        //                          }).Where(y => y.differ.Ticks > 0);

        //        var hours = 0;
        //        if (avgouttime.Count() > 0)
        //        {
        //            var avg = avgouttime.Average(y => y.differ.Ticks);
        //            hours = (DateTime.MinValue.AddTicks((long)avg) - DateTime.MinValue).Hours;
        //        }

        //        //延时量
        //        var delayedcount = delayList.Where(y => workOrderIdQuery.Contains(y.F_WorkOrderId)).Count();
        //        //处理均长
        //        var completedtimeQuery = (from o in query
        //                                  select new
        //                                  {
        //                                      differ = (o.F_FeedbackTime == null || o.F_SureTime == null) ? 0 : ((o.F_FeedbackTime ?? DateTime.MinValue) - (o.F_SureTime ?? DateTime.MinValue)).Ticks
        //                                  }).Where(y => y.differ > 0).ToList();

        //        var avgcompletedtime = (double)0;
        //        var maxcompletedtime = (double)0;
        //        if (completedtimeQuery.Count() > 0)
        //        {
        //            avgcompletedtime = new TimeSpan((long)completedtimeQuery.Average(y => y.differ)).Hours;
        //            //最大处理时长
        //            maxcompletedtime = new TimeSpan(completedtimeQuery.Max(y => y.differ)).Hours;

        //        }


        //        //回访量
        //        var visitCount = visitList.Where(y => workOrderIdQuery.Contains(y.F_WorkOrderId)).Count();
        //        //满意量(满意率)
        //        double satisfiedcount = query.Where(y => y.F_IsSatisfie == 1).Count();
        //        //不满意量(不满意率)
        //        double notsatisfiedcount = query.Where(y => y.F_IsSatisfie == 0).Count();
        //        satisfiedsum += satisfiedcount;
        //        notsatisfiedsum += notsatisfiedcount;
        //        list.Add(new DeptAssessmentDto
        //        {
        //            deptname = x.F_DeptName ?? "",
        //            assignedcount = assignedcount,
        //            completedcount = completedcount,
        //            intime = completedcount == 0 ? "0(0%)" : $"{intimecount}({(intimecount == 0 ? "0%" : ((intimecount / completedcount * 100).ToString("f2")) + "%")})",
        //            outtimecount = outtimecount,
        //            avgouttime = hours,
        //            delayedcount = delayedcount,
        //            avghandletime = avgcompletedtime,
        //            maxhandletime = maxcompletedtime,
        //            visitcount = visitCount,
        //            satisfiedrate = assignedcount == 0 ? "0(0%)" : $"{satisfiedcount}({(satisfiedcount == 0 ? "0%" : (satisfiedcount / assignedcount * 100).ToString("f2"))}%)",
        //            notsatisfiedrate = assignedcount == 0 ? "0(0%)" : $"{notsatisfiedcount}({(notsatisfiedcount == 0 ? "0%" : (notsatisfiedcount / assignedcount * 100).ToString("f2") + "%")})",
        //        });
        //    });
        //    var assignedsum = list.Sum(x => x.assignedcount);
        //    var completedsum = list.Sum(x => x.completedcount);
        //    var count = list.Count();
        //    var total = new
        //    {
        //        assignedcount = list.Sum(x => x.assignedcount),
        //        completedcount = list.Sum(x => x.completedcount),
        //        intime = completedsum == 0 ? $"0(0%)" : $"{intimsum}({(intimsum == 0 ? "0%" : (intimsum / completedsum * 100).ToString("f2"))}%)",
        //        outtimecount = list.Sum(x => x.outtimecount),
        //        avgouttime = list.Sum(x => x.outtimecount) / list.Count(),
        //        delayedcount = list.Sum(x => x.delayedcount),
        //        avghandletime = count > 0 ? Math.Floor((double)list.Sum(x => x.avghandletime) / count) : 0,
        //        maxhandletime = count > 0 ? list.Max(x => x.maxhandletime) : 0,
        //        visitcount = list.Sum(x => x.visitcount),
        //        satisfiedrate = assignedsum == 0 ? $"0(0%)" : $"{satisfiedsum}({(satisfiedsum == 0 ? "0%" : (satisfiedsum / assignedsum * 100).ToString("f2"))}%)",
        //        notsatisfiedrate = assignedsum == 0 ? $"0(0%)" : $"{notsatisfiedsum}({(notsatisfiedsum == 0 ? "0%" : (notsatisfiedsum / assignedsum * 100).ToString("f2") + "%")})",
        //    };
        //    if (isExport)
        //    {
        //        list.Add(new DeptAssessmentDto
        //        {
        //            deptname = "总计",
        //            assignedcount = total.assignedcount,
        //            completedcount = total.completedcount,
        //            intime = total.intime,
        //            outtimecount = total.outtimecount,
        //            avgouttime = total.avgouttime,
        //            delayedcount = total.delayedcount,
        //            avghandletime = total.avghandletime,
        //            maxhandletime = total.maxhandletime,
        //            visitcount = total.visitcount,
        //            satisfiedrate = total.satisfiedrate,
        //            notsatisfiedrate = total.notsatisfiedrate
        //        });
        //        var dt = list.ToDataTable(new string[] { "deptname", "completedcount", "intime", "outtimecount", "avgouttime", "delayedcount", "avghandletime", "maxhandletime", "visitcount", "satisfiedrate", "notsatisfiedrate" });
        //        new NPOIHelper().ExportToExcel($"单位绩效考核报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", dt, new string[] { "单位名称", "坐席", "手工录单数", "通话成单数", "当即办理数", "交办量", "已办理", "回访量", "市民满意", "市民不满意" });
        //        return Success("导出excel");
        //    }
        //    return Success("", new
        //    {
        //        list = list,
        //        total = total
        //    });
        //}

        /// <summary>
        /// 单位绩效考核
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetDeptAssessment(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"));
            paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
            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>
        ///// <param name="pageIndex"></param>
        ///// <param name="pageSize"></param>
        ///// <returns></returns>
        //public ActionResult GetUserAccountAssessment(DateTime? start, DateTime? end, int deptId = -1, bool isExport = false)
        //{
        //    if (start == null)
        //    {
        //        start = DateTime.Now.Date.AddDays(-6);
        //    }
        //    if (end == null)
        //    {
        //        end = DateTime.Now.Date.AddDays(1).AddSeconds(-1);
        //    }
        //    string where = "";
        //    if (deptId > 0)
        //    {
        //        where = " AND  F_DeptId=" + deptId;
        //    }
        //    var userList = userAccount.DataTableToList(userAccount.GetList(" F_DeleteFlag=0 " + where).Tables[0]);
        //    var workOrderList = workOrderBLL.DataTableToList(workOrderBLL.GetList($" F_IsDelete=0 and  datediff(s,'{start}',F_CreateTime)>0  and   datediff(s,'{end}',F_CreateTime)<0  ").Tables[0]);
        //    var deptList = departmentBLL.DataTableToList(departmentBLL.GetList($"  F_DeptNameSpell=3  ").Tables[0]);
        //    var assignedList = assignedInfoBLL.DataTableToList(assignedInfoBLL.GetList($" F_IsDelete=0 and F_IsSure in (0,1,2) and  F_State in (0,1)   and  datediff(s,'{start}',F_CreateTime)>0  and   datediff(s,'{end}',F_CreateTime)<0  ").Tables[0]).DistinctBy(x => x.F_WorkOrderId);
        //    var assignedIdList = assignedList.Select(x => x.F_WorkOrderId);
        //    var assignedSatisfiedIdList = assignedList.Where(y => y.F_IsSatisfie == 1).Select(x => x.F_WorkOrderId);
        //    var assignedNotSatisfiedIdList = assignedList.Where(y => y.F_IsSatisfie == 0).Select(x => x.F_WorkOrderId);
        //    var feedbackList = feedbackBLL.DataTableToList(feedbackBLL.GetList($"  F_IsFeedEnd=1 and F_Type in (1,2) and  F_State=1 and F_IsDelete not in (1)  and  datediff(s,'{start}',F_CreateTime)>0  and   datediff(s,'{end}',F_CreateTime)<0   ").Tables[0]).DistinctBy(x => x.F_WorkOrderId).Select(x => x.F_WorkOrderId);
        //    var visitResultList = visitResultBLL.DataTableToList(visitResultBLL.GetList($"   datediff(s,'{start}',F_CreateTime)>0  and   datediff(s,'{end}',F_CreateTime)<0   ").Tables[0]).Select(x => x.F_WorkOrderId);
        //    List<UserAccountAssessmentDto> list = new List<UserAccountAssessmentDto>();
        //    double satisfiedsum = 0;
        //    double notsatisfiedsum = 0;
        //    foreach (var u in userList)
        //    {
        //        var dept = deptList.SingleOrDefault(y => y.F_DeptId == u.F_DeptId);
        //        if (dept == null)
        //            continue;
        //        var deptName = dept.F_DeptName ?? "";
        //        var workOrderQuery = workOrderList.Where(x => x.F_CreateUser == u.F_UserCode);
        //        var workOrderCount = workOrderQuery.Count();
        //        var manualcount = workOrderQuery.Where(x => x.F_CallRecordId != null).Count();
        //        var assignedcount = workOrderQuery.Where(x => assignedIdList.Contains(x.F_WorkOrderId)).Count();
        //        double satisfiedcount = workOrderQuery.Where(x => assignedSatisfiedIdList.Contains(x.F_WorkOrderId)).Count();
        //        double notsatisfiedcount = workOrderQuery.Where(x => assignedNotSatisfiedIdList.Contains(x.F_WorkOrderId)).Count();
        //        satisfiedsum += satisfiedcount;
        //        notsatisfiedsum += notsatisfiedcount;
        //        list.Add(new UserAccountAssessmentDto
        //        {
        //            detpname = deptName,
        //            useraccount = $"{u.F_UserName}({u.F_UserCode})",
        //            manualcount = manualcount,
        //            talkcount = workOrderCount - manualcount,
        //            immecount = workOrderQuery.Where(x => x.F_IsResult == 1).Count(),
        //            assignedcount = assignedcount,
        //            completedcount = workOrderQuery.Where(x => feedbackList.Contains(x.F_WorkOrderId)).Count(),
        //            visitcount = workOrderQuery.Where(x => visitResultList.Contains(x.F_WorkOrderId)).Count(),
        //            satisfiedrate = assignedcount == 0 ? $"0(0%)" : $"{satisfiedcount}({(satisfiedcount == 0 ? "0%" : ((satisfiedcount / assignedcount * 100).ToString("f2") + "%"))})",
        //            notsatisfiedrate = assignedcount == 0 ? $"0(0%)" : $"{notsatisfiedcount}({(notsatisfiedcount == 0 ? "0%" : ((notsatisfiedcount / assignedcount * 100).ToString("f2") + "%"))})",

        //        });
        //    }
        //    var assignedsum = list.Sum(x => x.assignedcount);
        //    var total = new
        //    {
        //        manualcount = list.Sum(x => x.manualcount),
        //        talkcount = list.Sum(x => x.talkcount),
        //        immecount = list.Sum(x => x.immecount),
        //        assignedcount = assignedsum,
        //        completedcount = list.Sum(x => x.completedcount),
        //        visitcount = list.Sum(x => x.visitcount),
        //        satisfiedrate = assignedsum == 0 ? $"0(0%)" : $"{satisfiedsum}({(satisfiedsum == 0 ? "0%" : ((satisfiedsum / assignedsum * 100).ToString("f2") + "%"))})",
        //        notsatisfiedrate = assignedsum == 0 ? $"0(0%)" : $"{notsatisfiedsum}({(notsatisfiedsum == 0 ? "0%" : ((notsatisfiedsum / assignedsum * 100).ToString("f2") + "%"))})",

        //    };
        //    if (isExport)
        //    {
        //        list.Add(new UserAccountAssessmentDto
        //        {
        //            detpname = "总",
        //            useraccount = "计",
        //            manualcount = total.manualcount,
        //            talkcount = total.talkcount,
        //            immecount = total.immecount,
        //            assignedcount = total.assignedcount,
        //            completedcount = total.completedcount,
        //            visitcount = total.visitcount,
        //            satisfiedrate = total.satisfiedrate,
        //            notsatisfiedrate = total.notsatisfiedrate
        //        });
        //        var dt = list.ToDataTable(new string[] { "detpname", "useraccount", "manualcount", "talkcount", "immecount", "assignedcount", "completedcount", "visitcount", "satisfiedrate", "notsatisfiedrate", });
        //        new NPOIHelper().ExportToExcel($"坐席工作统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", dt, new string[] { "单位名称", "交办总量", "已办理总量", "及时量", "超时量", "超市均长(小时)", "延时量", "处理均长(小时)", "最大处理时间(小时)", "回访量", "市民满意", "市民不满意", });
        //        return Success("导出excel");
        //    }
        //    return Success("", new
        //    {
        //        list = list,
        //        total = total
        //    });
        //}

        ///// <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.Date.AddDays(-6);
        //    }
        //    if (end == null)
        //    {
        //        end = DateTime.Now.Date.AddDays(1).AddSeconds(-1);
        //    }
        //    var where = "";
        //    if (deptId > 0)
        //    {
        //        where = " AND  F_MainDeptId=" + deptId;
        //    }
        //    var workOrderList = workOrderBLL.DataTableToList(workOrderBLL.GetList($" F_IsDelete=0 and  datediff(s,'{start}',F_CreateTime)>0  and   datediff(s,'{end}',F_CreateTime)<0  ").Tables[0]);
        //    var deptList = departmentBLL.DataTableToList(departmentBLL.GetList($" F_DeptNameSpell=3   ").Tables[0]);
        //    var assignedList = assignedInfoBLL.DataTableToList(assignedInfoBLL.GetList($" F_IsDelete=0 and F_IsSure in (0,1,2) and  F_State in (0,1)   and  datediff(s,'{start}',F_CreateTime)>0  and   datediff(s,'{end}',F_CreateTime)<0  " + where).Tables[0]).DistinctBy(x => x.F_WorkOrderId);

        //    List<OutTimeNotReplyDto> list = new List<OutTimeNotReplyDto>();
        //    foreach (var item in assignedList)
        //    {
        //        var deptName = deptList.SingleOrDefault(x => x.F_DeptId == item.F_MainDeptId)?.F_DeptName ?? "";
        //        if (string.IsNullOrWhiteSpace(deptName))
        //            continue;
        //        var workorder = workOrderList.SingleOrDefault(x => x.F_WorkOrderId == item.F_WorkOrderId);
        //        if (workorder == null)
        //            continue;
        //        list.Add(new OutTimeNotReplyDto
        //        {
        //            deptname = deptName,
        //            content = workorder?.F_ComContent ?? "",
        //            time = item.F_CreateTime == null ? "" : Convert.ToDateTime(item.F_CreateTime).ToString("yyyy-MM-dd HH:mm:ss"),
        //            limit = item.F_LimitTime == null ? "" : Convert.ToDateTime(item.F_LimitTime).ToString("yyyy-MM-dd HH:mm:ss"),
        //            reply = item.F_FeedbackTime == null ? "" : Convert.ToDateTime(item.F_FeedbackTime).ToString("yyyy-MM-dd HH:mm:ss")
        //        });
        //    }
        //    if (isExport)
        //    {
        //        var dt = list.ToDataTable(new string[] { "deptname", "content", "time", "limit", "reply" });
        //        new NPOIHelper().ExportToExcel($"逾期未回复统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", dt, new string[] { "承办单位", "诉求内容", "转交时间", "回复时限", "回复时间" });
        //        return Success("导出excel");
        //    }
        //    return Content(new
        //    {
        //        rows = list.Skip((pageIndex - 1) * pageSize).Take(pageSize),
        //        total = list.Count
        //    }.ToJson());

        //}

        /// <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"));
            paras.Add("@edate", end.Value.ToString("yyyy-MM-dd"));
            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)
        {
            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"));
            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);
        }

        /// <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.Date.AddDays(-6);
        //    }
        //    if (end == null)
        //    {
        //        end = DateTime.Now.Date.AddDays(1).AddSeconds(-1);
        //    }

        //    var workOrderList = workOrderBLL.DataTableToList(workOrderBLL.GetList($" F_IsDelete=0 and  datediff(s,'{start}',F_CreateTime)>0  and   datediff(s,'{end}',F_CreateTime)<0  ").Tables[0]);
        //    var deptList = departmentBLL.DataTableToList(departmentBLL.GetList($"  ").Tables[0]);
        //    var assignedList = assignedInfoBLL.DataTableToList(assignedInfoBLL.GetList($" F_IsDelete=0 and F_IsSure in (0,1,2) and  F_State in (0,1)   and  datediff(s,'{start}',F_CreateTime)>0  and   datediff(s,'{end}',F_CreateTime)<0  ").Tables[0]).DistinctBy(x => x.F_WorkOrderId);
        //    var userAccountList = userAccount.DataTableToList(userAccount.GetList(" F_DeleteFlag=0 ").Tables[0]);
        //    List<OutNotSatisfiedDto> list = new List<OutNotSatisfiedDto>();
        //    foreach (var item in assignedList)
        //    {
        //        var deptName = deptList.SingleOrDefault(x => x.F_DeptId == item.F_MainDeptId)?.F_DeptName ?? "";
        //        if (string.IsNullOrWhiteSpace(deptName))
        //            continue;
        //        var workorder = workOrderList.SingleOrDefault(x => x.F_WorkOrderId == item.F_WorkOrderId);
        //        if (workorder == null)
        //            continue;
        //        var userAccount = userAccountList.SingleOrDefault(x => x.F_UserCode == workorder.F_CreateUser);
        //        if (userAccount == null)
        //            continue;
        //        list.Add(new OutNotSatisfiedDto
        //        {
        //            workorder = item.F_WorkOrderId,
        //            deptname = deptName,
        //            title = workorder.F_ComTitle,
        //            creattime = Convert.ToDateTime(workorder.F_CreateTime).ToString("yyyy-MM-dd HH:mm:ss"),
        //            useraccount = userAccount.F_UserName + $"({userAccount.F_UserCode})",

        //        });
        //    }
        //    if (isExport)
        //    {
        //        var dt = list.ToDataTable(new string[] { "workorder", "detpname", "title", "creattime", "useraccount" });
        //        new NPOIHelper().ExportToExcel($"不满意统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", dt, new string[] { "诉求编号", "承办单位", "诉求标题", "诉求时间", "受理人" });
        //        return Success("导出excel");
        //    }
        //    return Content(new
        //    {
        //        rows = list.Skip((pageIndex - 1) * pageSize).Take(pageSize),
        //        total = list.Count
        //    }.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, int pageIndex = 1, int pageSize = 10, bool isExport = false)
        //{
        //    if (start == null)
        //    {
        //        start = DateTime.Now.Date.AddDays(-6);
        //    }
        //    if (end == null)
        //    {
        //        end = DateTime.Now.Date.AddDays(1).AddSeconds(-1);
        //    }
        //    var deptList = departmentBLL.DataTableToList(departmentBLL.GetList(" F_State=0 AND  F_DeptNameSpell=3 ").Tables[0]);
        //    var delayList = new BLL.T_Bus_WorkOrderReport().DataTableToDelayList(DbHelperSQL.Query($"SELECT A.F_WorkOrderId,B.F_MainDeptId, C.F_ID FROM T_Bus_WorkOrder A LEFT JOIN T_Bus_AssignedInfo B ON A.F_WorkOrderId = B.F_WorkOrderId LEFT JOIN T_Bus_DelayTime C ON B.F_Id = C.F_AssignedId where datediff(s, '{start}', A.F_CreateTime) > 0  and datediff(s, '{end}', A.F_CreateTime) < 0  and A.F_IsDelete = 0 AND A.F_WorkOrderId IS NOT NULL AND B.F_ID IS NOT NULL AND B.F_MainDeptId 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]);
        //    List<DelayDto> list = new List<DelayDto>();
        //    deptList.ForEach(x =>
        //    {
        //        var assignQuery = delayList.Where(y => x.F_DeptId == y.DeptId).ToList();
        //        var delayQuery = assignQuery.Where(y => y.DelayId > 0).ToList();
        //        var count = assignQuery.Count();
        //        var delayCount = delayQuery.Count();
        //        list.Add(new DelayDto
        //        {
        //            deptname = x.F_DeptName,
        //            count = count,
        //            delaycount = delayCount,
        //            delayrate = count == 0 ? "0%" : delayCount == 0 ? "0%" : ((double)delayCount / count * 100).ToString("f2") + "%"
        //        });
        //    });
        //    if (isExport)
        //    {
        //        var dt = list.ToDataTable(new string[] { "name", "count", "delaycount", "delayrate" });
        //        new NPOIHelper().ExportToExcel($"延时分析统计{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", dt, new string[] { "承办单位", "受理数量", "延时数量", "延时率" });
        //        return Success("导出excel");
        //    }
        //    return Content(new
        //    {
        //        rows = list.Skip((pageIndex - 1) * pageSize).Take(pageSize),
        //        total = list.Count
        //    }.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 GetNotice(DateTime? start, DateTime? end, int pageIndex = 1, int pageSize = 10, bool isExport = false)
        //{
        //    if (start == null)
        //    {
        //        start = DateTime.Now.Date.AddDays(-6);
        //    }
        //    if (end == null)
        //    {
        //        end = DateTime.Now.Date.AddDays(1).AddSeconds(-1);
        //    }
        //    var deptList = departmentBLL.DataTableToList(departmentBLL.GetList(" F_State=0 AND  F_DeptNameSpell=3 ").Tables[0]);
        //    var noticeList = new BLL.T_Bus_WorkOrderReport().DataTableToNoticeList(DbHelperSQL.Query($"SELECT  A.F_WorkOrderId,B.F_MainDeptId,B.F_IsNotice  FROM T_Bus_WorkOrder A LEFT JOIN T_Bus_AssignedInfo B ON A.F_WorkOrderId = B.F_WorkOrderId where datediff(s, '{start}', A.F_CreateTime) > 0  and   datediff(s, '{end}', A.F_CreateTime) < 0  and A.F_IsDelete = 0 AND A.F_WorkOrderId IS NOT NULL AND B.F_ID IS NOT NULL AND B.F_MainDeptId 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]);
        //    List<NoticeDto> list = new List<NoticeDto>();
        //    deptList.ForEach(x =>
        //    {
        //        var assignQuery = noticeList.Where(y => x.F_DeptId == y.DeptId).ToList();
        //        var noticeQuery = assignQuery.Where(y => y.IsNotice).ToList();
        //        var count = assignQuery.Count();
        //        var noticeCount = noticeQuery.Count();
        //        list.Add(new NoticeDto
        //        {
        //            deptname = x.F_DeptName,
        //            count = count,
        //            noticecount = noticeCount,
        //            noticerate = count == 0 ? "0%" : noticeCount == 0 ? "0%" : ((double)noticeCount / count * 100).ToString("f2") + "%"
        //        });
        //    });
        //    if (isExport)
        //    {
        //        var dt = list.ToDataTable(new string[] { "name", "count", "noticecount", "noticerate" });
        //        new NPOIHelper().ExportToExcel($"通报批评{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", dt, new string[] { "承办单位", "受理数量", "通报批评数量", "通报批评率" });
        //        return Success("导出excel");
        //    }
        //    return Content(new
        //    {
        //        rows = list.Skip((pageIndex - 1) * pageSize).Take(pageSize),
        //        total = list.Count
        //    }.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 GetAppeal(DateTime? start, DateTime? end, int pageIndex = 1, int pageSize = 10, bool isExport = false)
        //{
        //    if (start == null)
        //    {
        //        start = DateTools.FirstDayOfMonth(DateTime.Now.Date);
        //    }
        //    if (end == null)
        //    {
        //        end = DateTools.LastDayOfMonth(DateTime.Now.Date);
        //    }
        //    var lastStart = DateTools.FirstDayOfPreviousMonth(DateTime.Now.Date);
        //    var lastEnd = DateTools.LastDayOfPrdviousMonth(DateTime.Now.Date);
        //    var lastYearStart = DateTools.FirstDayOfMonth(DateTime.Now.AddYears(-1).Date);
        //    var lastYearEnd = DateTools.LastDayOfMonth(DateTime.Now.AddYears(-1).Date);
        //    // var deptList = departmentBLL.DataTableToList(departmentBLL.GetList(" F_State=0 AND  F_DeptNameSpell=3 ").Tables[0]);
        //    var userList = userAccount.DataTableToList(userAccount.GetList(" F_DeleteFlag=0  AND F_SeatFlag=1 ").Tables[0]);
        //    var workOrderListNow = new BLL.T_Bus_WorkOrderReport().DataTableAppealList(DbHelperSQL.Query($"SELECT F_WorkOrderId,F_CreateUser FROM T_Bus_WorkOrder WHERE  datediff(s, '{start}', F_CreateTime) > 0  and   datediff(s, '{end}', F_CreateTime) < 0  and F_IsDelete = 0 AND F_WorkOrderId IS NOT NULL").Tables[0]);
        //    var workOrderListLast = new BLL.T_Bus_WorkOrderReport().DataTableAppealList(DbHelperSQL.Query($"SELECT F_WorkOrderId,F_CreateUser FROM T_Bus_WorkOrder WHERE  datediff(s, '{lastStart}', F_CreateTime) > 0  and   datediff(s, '{lastEnd}', F_CreateTime) < 0  and F_IsDelete = 0 AND F_WorkOrderId IS NOT NULL").Tables[0]);
        //    var workOrderListLastYear = new BLL.T_Bus_WorkOrderReport().DataTableAppealList(DbHelperSQL.Query($"SELECT F_WorkOrderId,F_CreateUser FROM T_Bus_WorkOrder WHERE  datediff(s, '{lastYearStart}', F_CreateTime) > 0  and   datediff(s, '{lastYearEnd}', F_CreateTime) < 0  and F_IsDelete = 0 AND F_WorkOrderId IS NOT NULL").Tables[0]);
        //    List<AppealDto> list = new List<AppealDto>();
        //    userList.ForEach(x =>
        //    {
        //        var count = workOrderListNow.Where(y => x.F_UserCode == y.UserCode).ToList().Count();
        //        var lastCount = workOrderListLast.Where(y => x.F_UserCode == y.UserCode).ToList().Count();
        //        var lastYearCount = workOrderListLastYear.Where(y => x.F_UserCode == y.UserCode).ToList().Count();
        //        list.Add(new AppealDto
        //        {
        //            name = x.F_UserName,
        //            count = count,
        //            mom = lastCount == 0 ? "0%" : count == 0 ? "0%" : ((double)count / lastCount * 100).ToString("f2") + "%",
        //            yoy = lastYearCount == 0 ? "0%" : count == 0 ? "0%" : ((double)count / lastYearCount * 100).ToString("f2") + "%"
        //        });
        //    });
        //    if (isExport)
        //    {
        //        var dt = list.ToDataTable(new string[] { "name", "count", "mom", "yoy" });
        //        new NPOIHelper().ExportToExcel($"诉求受理对比{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", dt, new string[] { "坐席名称", "受理数量", "上月环比", "去年同期同比" });
        //        return Success("导出excel");
        //    }
        //    return Content(new
        //    {
        //        rows = list.Skip((pageIndex - 1) * pageSize).Take(pageSize),
        //        total = list.Count
        //    }.ToJson());
        //}

        /// <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)
        {
            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_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>
        /// <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)
        {
            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());
            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="start"></param>
        ///// <param name="end"></param>
        ///// <param name="pageIndex"></param>
        ///// <param name="pageSize"></param>
        ///// <param name="isExport"></param>
        ///// <returns></returns>
        //public ActionResult GetWorkLoad(DateTime? start, DateTime? end, int deptId = -1, int pageIndex = 1, int pageSize = 10, bool isExport = false)
        //{
        //    if (start == null)
        //    {
        //        start = DateTime.Now.Date.AddDays(-6);
        //    }
        //    if (end == null)
        //    {
        //        end = DateTime.Now.Date.AddDays(1).AddSeconds(-1);
        //    }
        //    string where = "";
        //    if (deptId > 0)
        //    {
        //        where = " AND F_DeptId=" + deptId;
        //    }
        //    var userList = userAccount.DataTableToList(userAccount.GetList(" F_DeleteFlag=0 and F_RoleId=6 " + where).Tables[0]);
        //    var deptList = departmentBLL.DataTableToList(departmentBLL.GetList($" F_State=0 AND F_DeptNameSpell=3  ").Tables[0]);
        //    var assignedList = assignedInfoBLL.DataTableToList(assignedInfoBLL.GetList($" F_IsDelete = 0 and F_IsSure in (0, 1) and  F_State in (1) and datediff(s,'{start}', F_CreateTime)> 0  and datediff(s,'{end}', F_CreateTime)< 0  ").Tables[0]);
        //    var submitsuperList= submitsuperBLL.DataTableToList(submitsuperBLL.GetList($" F_IsDelete = 0 and F_Type in (3) and  F_State in (1) and datediff(s,'{start}', F_CreateTime)> 0  and datediff(s,'{end}', F_CreateTime)< 0  ").Tables[0]);
        //    List<UserAccountWorkLoad> list = new List<UserAccountWorkLoad>();
        //    foreach (var u in userList)
        //    {
        //        var dept = deptList.SingleOrDefault(x => x.F_DeptId == u.F_DeptId);
        //        if (dept == null)
        //            continue;
        //        var deptName = dept.F_DeptName ?? "";
        //        //转派次数
        //        var assignedCount = assignedList.Where(x => x.F_CreateUser == u.F_UserCode).Count();
        //        //退回坐席次数
        //        var returnedcount = submitsuperList.Where(x => x.F_CreateUser == u.F_UserCode).Count();
        //        //调度工作量
        //        var workcount = assignedCount + returnedcount;
        //        list.Add(new UserAccountWorkLoad
        //        {
        //            detpname = deptName,
        //            useraccount = $"{u.F_UserName}({u.F_UserCode})",
        //            fordeliverycount = assignedCount,   
        //            returnedcount= returnedcount,
        //            workcount = workcount
        //        });
        //    }
        //    var total = new
        //    {
        //        fordeliverycount = list.Sum(x => x.fordeliverycount),
        //        returnedcount = list.Sum(x => x.returnedcount),
        //        workcount = list.Sum(x => x.workcount)
        //    };
        //    //导出
        //    if (isExport)
        //    {
        //        list.Add(new UserAccountWorkLoad
        //        {
        //            detpname = "总",
        //            useraccount = "计",
        //            fordeliverycount = total.fordeliverycount,
        //            returnedcount = total.returnedcount,
        //            workcount = total.workcount
        //        });
        //        var dt = list.ToDataTable(new string[] { "detpname", "useraccount", "fordeliverycount", "returnedcount", "workcount", });
        //        new NPOIHelper().ExportToExcel($"调度工作量统计报表{Convert.ToDateTime(start).ToString("yyyyMMdd")}-{Convert.ToDateTime(end).ToString("yyyyMMdd")}", dt, new string[] { "部门名称", "坐席", "转派次数", "退回坐席次数", "调度工作量", });
        //        return Success("导出excel");
        //    }
        //    var obj = new
        //    {
        //        rows = list,    //list.Skip((pageIndex - 1) * pageSize).Take(pageSize),
        //        total = total
        //    };
        //    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 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)
        {
            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_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");
            }
            int userId = Utils.StrToInt(User.UserData["F_UserID"], 0);
            var usermodel = userAccount.GetModel(userId);
            if (usermodel.F_RoleCode == "GLY")
            {
                var maindeptid = 0;
                if (usermodel.F_RoleCode == "WLDW")
                {
                    maindeptid = usermodel.F_DeptId;
                }
                else if (usermodel.F_RoleCode == "EJWLDW")
                {
                    maindeptid = usermodel.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 = 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");
            }
            int userId = Utils.StrToInt(User.UserData["F_UserID"], 0);
            var usermodel = userAccount.GetModel(userId);
            if (usermodel.F_RoleCode != "GLY")
            {
                var maindeptid = 0;
                if (usermodel.F_RoleCode == "WLDW")
                {
                    maindeptid = usermodel.F_DeptId;
                }
                else if (usermodel.F_RoleCode == "EJWLDW")
                {
                    maindeptid = usermodel.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_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}-{edate}", 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 userId = Utils.StrToInt(User.UserData["F_UserID"], 0);
            int deptid = 0;
            var usermodel = userAccount.GetModel(userId);
            if (usermodel.F_RoleCode != "GLY")
            {
                if (usermodel.F_RoleCode == "WLDW" || usermodel.F_RoleCode == "EJWLDW")
                {
                    deptid = usermodel.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)
        {
            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 userId = Utils.StrToInt(User.UserData["F_UserID"], 0);
            int deptid = 0;
            var usermodel = userAccount.GetModel(userId);
            if (usermodel.F_RoleCode != "GLY")
            {
                if (usermodel.F_RoleCode == "WLDW" || usermodel.F_RoleCode == "EJWLDW")
                {
                    deptid = usermodel.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_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)
        {
            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 userId = Utils.StrToInt(User.UserData["F_UserID"], 0);
            int deptid = 0;
            var usermodel = userAccount.GetModel(userId);
            if (usermodel.F_RoleCode != "GLY")
            {
                if (usermodel.F_RoleCode == "WLDW" || usermodel.F_RoleCode == "EJWLDW")
                {
                    deptid = usermodel.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_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)
        {
            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 userId = Utils.StrToInt(User.UserData["F_UserID"], 0);
            var usermodel = userAccount.GetModel(userId);
            int deptid = 0;
            if (usermodel.F_RoleCode != "GLY")
            {
                if (usermodel.F_RoleCode == "WLDW" || usermodel.F_RoleCode == "EJWLDW")
                {
                    deptid = usermodel.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", 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 userId = Utils.StrToInt(User.UserData["F_UserID"], 0);
            var usermodel = userAccount.GetModel(userId);
            int deptid = 0;
            if (usermodel.F_RoleCode != "GLY")
            {
                if (usermodel.F_RoleCode == "WLDW" || usermodel.F_RoleCode == "EJWLDW")
                {
                    deptid = usermodel.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
        
        #region 巩义新工单表
        /// <summary>
        /// 工单逾期数量表
        /// </summary>
        /// <param name="date"></param>
        /// <param name="isdc"></param>
        /// <returns></returns>
        public ActionResult GetBeOverdue(DateTime? start, DateTime? end, int deptid = 0, 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_DeptId=" + deptid;
            }
            string sql = $"  select F_DeptName as 单位,(select COUNT(1) from T_Bus_WorkOrder a where ((F_WorkState in (6, 7, 9, 14) and F_LimitTime < a.F_DealTime) or   (F_WorkState not in (6, 7, 9, 14) and F_LimitTime < GETDATE()))  and F_IsDelete = 0 and     F_MainDeptId = n.F_DeptId  and F_CreateTime>='" + start.Value.ToString("yyyy-MM-dd") +"' and F_CreateTime<='" + end.Value.ToString("yyyy-MM-dd") +"') AS 逾期数量  from T_Sys_Department n where F_DeptName != '巩义市'" + where;

         
            if (isdc > 0)
            {
                var dtdc = DbHelperSQL.Query(sql).Tables[0];
                var msg = new NPOIHelper().ExportToExcel("逾期数量", dtdc);
                if (msg == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }

            var dt = DbHelperSQL.Query(sql).Tables[0];
            var obj = new
            {
                date = dt
            };
              
            return Content(obj.ToJson());
        }

        public class Dayweekmonth
        {
            public int Acceptance;
            public int Handled;
            public int Nothandled;
        }
        /// <summary>
        /// 工单日周月表
        /// </summary>
        /// <param name="date"></param>
        /// <param name="isdc"></param>
        /// <returns></returns>
        public ActionResult GetDayweekmonth(DateTime? date, string stime,string etime , int type = 0, int dateType = 0, int deptid = 0
            , bool isExport = false,string  source="")
        {
            var date1 = date;
            var start1 = "";
            if (date == null)
            {
                date = DateTime.Now.Date;
                start1 = DateTime.Now.ToString("yyyy-MM-dd");
            }

            if (string.IsNullOrEmpty(stime) && string.IsNullOrEmpty(etime))
            {
                stime = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00";
                etime = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
            }
            if (type == 1)
            {
                string where = "";
                string where1 = "";
                where += " and F_CreateTime>='" + stime + "' ";
                where += " and F_CreateTime<='" + etime + "' ";
                where1 = " and wo.F_Hcdatetime <= CONVERT(datetime, '" + etime + "', 20) and  wo.F_Hcdatetime>=CONVERT(datetime,'" + stime + "',20)";
                if (!string.IsNullOrEmpty(source))
                {
                    where += "and F_InfoSource='" + source + "'";
                    where1 += "and F_InfoSource='" + source + "'";
                }
                //郑州转派件
                string Zzzpj = $"select count(*) as 转派件 from (select DISTINCT wo.F_OldWorkOrderId from T_Bus_WorkOrder wo where  F_OldWorkOrderId is not null and F_OldWorkOrderId !='' and F_IsDelete =0  " + where + ") b";
                var Zzzpjcount = DbHelperSQL.GetSingle(Zzzpj);
                //郑州已回传件
                string Zzyhc = $"select count(*) as 回传件 from (select DISTINCT wo.F_OldWorkOrderId from T_Bus_WorkOrder wo where F_OldWorkOrderId!='' and wo.F_WorkState in (9,14) " + where1 + ") b";
                var Zzyhccount = DbHelperSQL.GetSingle(Zzyhc);
                var ob = new
                {
                    Acceptance = Zzzpjcount,
                    Handled = Zzyhccount,
                };
                if (isExport)
                {
                    DataTable dt = new DataTable();
                    dt.Columns.Add("Acceptance", typeof(string));
                    dt.Columns.Add("Handled", typeof(string));
                    dt.Rows[0]["Acceptance"] = Zzzpjcount;
                    dt.Rows[0]["Handled"] = Zzyhccount;
                    var cols = new string[] { "郑州转派件", "郑州回传件" };
                    new NPOIHelper().ExportToExcel($"工单报表{Convert.ToDateTime(stime).ToString("yyyyMMdd")}-{Convert.ToDateTime(etime).ToString("yyyyMMdd")}", dt, cols);
                    return Success("导出excel");
                }
                var a = ob.ToJson();
                return Content(ob.ToJson());
            }


            else if (type == 2)
            {
                type = 1;
                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.ToString());
                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);
            }
            else 
            {
                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.ToString());
                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="start"></param>
        /// <param name="end"></param>
        /// <param name="deptid"></param>
        /// <param name="isExport"></param>
        /// <returns></returns>
        public ActionResult GetptSatisfaction(DateTime? start, DateTime? end,int   deptid=0, 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;
            }
            string dept = "";
            if (deptid>0)
            {
                dept = 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"));
            paras.Add("@deptid", dept);
            paras.Add("@source", source);
            var obj = DbHelperSQL.RunProcedure("P_DeptSatisfaction", paras, "DeptSatisfactionReport");
            obj.Tables[0].Columns.Remove("rate");

            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 GetDeptAchievements(DateTime? start, DateTime? end,int deptid=0, 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;
            }
            string dept = "";
            if (deptid > 0)
            {
                dept = 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"));
            paras.Add("@deptid", dept);
            var obj = DbHelperSQL.RunProcedure("P_DeptDeptAchievements", paras, "DeptDeptAchievements");
            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 GetDeptEvaluation(DateTime? start, DateTime? end, int deptid = 0, 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;
            }
            string dept = "";
            if (deptid > 0)
            {
                dept = 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"));
            paras.Add("@deptid", dept);
            var obj = DbHelperSQL.RunProcedure("P_DeptEvaluation", paras, "DeptEvaluation");
            if (isExport)
            {
                string[] cols = new string[] { "单位名称", "工单实时签收率", "实时退单率", "重复退单率", "按时办结率", "办结提速率", "结果满意率", "知识库报送情况", "工作量附加分",
                    "综合考评","综合考评备注","监督检查","监督检查备注","合计" };
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcels("绩效考核报表", 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 GetPerformanceAssessment(DateTime? start, DateTime? end, int deptid = 0, 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;
            }
            string dept = "";
            if (deptid > 0)
            {
                dept = 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"));
            paras.Add("@deptid", dept);
            var obj = DbHelperSQL.RunProcedure("P_PerformanceAssessment", paras, "PerformanceAssessment");

            var dt1 = obj.Tables[0];
            dt1.DefaultView.Sort = "total DESC";
            var dt= dt1.DefaultView.ToTable();
            dt.Columns.Add("totalsort", typeof(int));//超时状态
            int totalsortcount = 1;
            foreach (DataRow dr in dt.Rows)
            {
                dr["totalsort"] = totalsortcount;
                totalsortcount++;
            }
            
            if (isExport)
            {
                string[] cols = new string[] { "单位名称","办件数量", "工单实时签收率", "实时退单率", "重复退单率", "按时办结率", "办结提速率","扫描件上传速率", "结果满意率",
                    "信息宣传","信息宣传备注",
                    "优秀案件","优秀案件备注",
                    "即办事项","即办事项备注","工作量附加分","监督检查", "监督检查备注","合计" };
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcels("绩效考核报表", obj.Tables[0], cols) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }

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

        /// <summary>
        /// 绩效考核列表展示
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetPerformanceAssessmentList(DateTime? start, DateTime? end, int deptid = 0, int tmptype = 1, int pagesize = 20, int pageindex = 1, bool IsScore = true)
        {
            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;
            }
            string dept = "";
            if (deptid > 0)
            {
                dept = deptid + "";
            }
            SqlParameter[] Paras =
            {
                new SqlParameter("@sdate",SqlDbType.VarChar,20),
                new SqlParameter("@edate",SqlDbType.VarChar,20),
                new SqlParameter("@deptid",SqlDbType.VarChar,20),
                new SqlParameter("@tmptype",SqlDbType.Int),
                new SqlParameter("@pagesize",SqlDbType.Int),
                new SqlParameter("@pageindex",SqlDbType.Int),
                new SqlParameter("@IsScore",SqlDbType.Bit)

            };
            Paras[0].Value = start;
            Paras[1].Value = end;
            Paras[2].Value = deptid;
            Paras[3].Value = tmptype;
            Paras[4].Value = pagesize;
            Paras[5].Value = pageindex;
            Paras[6].Value = IsScore;
            //var obj = DbHelperSQL.RunProcedure("P_PerformanceAssessment0317", Paras, "PerformanceAssessment").Tables[0];
            //DataTable dbobj = obj.Clone();
            //dbobj.Clear();
            //foreach (DataRow row in obj.Rows)
            //{
            //    if (Convert.ToInt32(row["tmptype"]) == tmptype)
            //    {
            //        dbobj.Rows.Add(row.ItemArray);

            //    }
            //}
            var HelperSQL = DbHelperSQL.RunProcedure("P_PerformanceAssessment0317", Paras, "PerformanceAssessment");
            var obj = new
            {
                state = "success",
                message = "成功",
                data = HelperSQL.Tables[0],
                total = int.Parse(HelperSQL.Tables[1].Rows[0]["total"].ToString())
            };

            return Content(obj.ToJson());

        }


        /// <summary>
        /// 绩效考核
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetDeptCheck(DateTime? time=null , int deptid = 0, bool isExport = false)
        {
            string date = "";
            string sql = "";
            if (time ==null )
            {
                if (DateTime.Now.Day >= 10)
                    date = (DateTime.Now.AddMonths(-1)).ToString("yyyyMM");
                else
                    date = (DateTime.Now.AddMonths(-2)).ToString("yyyyMM");
            }
            else
            {
                date = time.Value .ToString("yyyyMM");
            }
           
            if (deptid>0)
            {
                var dept = new BLL.T_Sys_Department().GetModel(deptid);
                if (dept!=null )
                {
                    if (dept.F_DeptName == "市直有关单位"|| dept.F_DeptName == "镇(街道)")
                    {
                        sql += "and F_DeptID in(select F_DeptID from T_Sys_Department where F_PartentId='"+ deptid + "' )";
                    }
                    else 
                    sql += "and F_DeptID='" + deptid + "'";
                }
              
            }
            if (isExport)
            {
                string[] cols = new string[] { "单位名称", "办件数","工作量附加分", "查收回复率", "逾期率", 
                    "综合考评","综合考评备注","上月得分" ,"加减分","合计"};
                string jxsql = "select F_DeptName,F_HandleCount, " +
                    "F_Additional,F_Check,F_Beoverdue,F_Comprehensive,F_ComprehensiveRemaks," +
                    "F_LastTotal,F_Bonuspoints,F_Total  from T_Sys_Achievements where F_Date=" + date
             + sql;
                var obj = DbHelperSQL.Query(jxsql).Tables[0];
                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcels("绩效考核报表", obj, cols) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            else
            {
                string jxsql = "select * from T_Sys_Achievements where F_Date='" + date+"'"
                + sql;
                var obj = DbHelperSQL.Query(jxsql).Tables[0];
                return Success("成功", obj);
            }
           
        }
        #endregion

        //多派单统计信息
        //统计已结案的工单，按照工单号、最后一次处理部门进行分组统计 ，
        //统计该工单交办给该部门的次数，列表仅显示次数大于1的数据。
        //列表项：工单号、部门、交办量；筛选项：交办时间范围、部门、工单号。另外列表数据可导出
        public ActionResult GetAssignInfotReport(string workorderid,string start, string end, int deptid = 0, bool isExport = false,int pageIndex = 1,int pageSize = 10)
        {          

            string wheresql = "";

            if (!string.IsNullOrEmpty(start)  )
            {
                wheresql += " and b.F_CreateTime >='"+ start + "'";
            }
            if (!string.IsNullOrEmpty(end))
            {
                wheresql += " and b.F_CreateTime <='" + end + "'";
            }
            if (deptid > 0)
            {
                wheresql += " and b.F_MainDeptId ='"+ deptid + "' ";
            }

            if (!string.IsNullOrEmpty(workorderid))
            {
                wheresql += "  and b.F_WorkOrderId='"+ workorderid + "'";
            }
            string tablesql = " (select  b.F_WorkOrderId F_WorkOrderId,dbo.GetDeptName(b.F_MainDeptId) DeptName,COUNT(1) total from T_Bus_WorkOrder  a left join  T_Bus_AssignedInfo b on b.F_MainDeptId = a.F_MainDeptId and a.F_WorkOrderId = b.F_WorkOrderId where a.F_WorkState = 14 and b.F_State = 1 " + wheresql+"  group by b.F_WorkOrderId ,b.F_MainDeptId  having COUNT(1) > 1) x";
                      
            if (isExport)
            {
                string sql2 = "  select  b.F_WorkOrderId,dbo.GetDeptName(b.F_MainDeptId),COUNT(1) total from T_Bus_WorkOrder  a left join  T_Bus_AssignedInfo b on b.F_MainDeptId = a.F_MainDeptId and a.F_WorkOrderId = b.F_WorkOrderId where a.F_WorkState = 14 and b.F_State = 1 " + wheresql + "  group by b.F_WorkOrderId ,b.F_MainDeptId  having COUNT(1) > 1  ";

                NPOIHelper npoi = new NPOIHelper();
                var dt2 = DbHelperSQL.Query(sql2).Tables[0];
                string result = npoi.ExportToExcel("多派单统计信息", dt2, new string[] { "工单号", "部门", "交办量" });

                return string.IsNullOrWhiteSpace(result) ? Success("导出成功") : Error("导出失败");
            }
            int recordCount = 0;

            var dt = BLL.PagerBLL.GetListPager(
              tablesql,
              "F_WorkOrderId",
              "*",
              "",
              "ORDER BY F_WorkOrderId DESC",
              pageSize,
              pageIndex,
              true,
              out recordCount);

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

            return Content(obj.ToJson());


          
        }


    }
}