XinXiangXLX_API/codegit/CallCenterApi/CallCenterAp.../CallCenterAp.../Controllers/report/WOReportCont...

using CallCenter.Utility;
using CallCenterApi.DB;
using CallCenterApi.Interface.Controllers.Base;
using CallCenterApi.Interface.Models.Enum;
using CallCenterApi.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Mvc;
using System.Dynamic;

namespace CallCenterApi.Interface.Controllers.report
{
    public class WOReportController : BaseController
    {
        BLL.T_Cus_CustomerBaseNew cusbll = new BLL.T_Cus_CustomerBaseNew();
        BLL.T_Sys_Department departmentBLL = new BLL.T_Sys_Department();
        BLL.T_Wo_WorkOrderItem_New itembll = new BLL.T_Wo_WorkOrderItem_New();
        BLL.T_Wo_MaterialManage mmBLL = new BLL.T_Wo_MaterialManage();
        BLL.T_Wo_QuestionManage quesBLL = new BLL.T_Wo_QuestionManage();
        private readonly BLL.T_Wo_WorkOrder woBLL = new BLL.T_Wo_WorkOrder();
        BLL.T_Wo_WorkOrder workOrder = new BLL.T_Wo_WorkOrder();
        public ActionResult GetDicValueListByFlag(string flag)
        {
            DataTable dt = new DataTable();

            dt = new BLL.T_Sys_DictionaryValue().GetList(" F_DictionaryFlag='" + flag + "' and F_State=1 ").Tables[0];

            return Success("列表加载成功", dt);
        }
        public ActionResult GetDataList(string stime, string endtime, int deptid = 0)
        {
            DataTable dtNew = new DataTable();//getData(stime, endtime, deptid);
            var obj = new
            {
                dtNew,
                totle = dtNew.Rows.Count
            };
            return Success("获取业务报表数据", obj);

        }
        public ActionResult ExptList(string stime, string endtime, int deptid = 0)
        {
            NPOIHelper npoi = new NPOIHelper();
            DataTable dt = new DataTable();
            // DataTable dt = getData(stime, endtime, deptid);
            if (npoi.ExportToExcel("业务报表数据", dt) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");
            }
        }
        public class market
        {
            public string typename;
            public string salexinxiang;
            public string salejiujiang;
            public string salexinjiang;
            public string saledongbei;
            public string total;
        }

        public class marketList
        {
            public string typename;
            public int salexinxinag;
            public int salejiujiang;
            public int salexinjiang;
            public int saledongbei;
            public int total;
            public string immediate;
            public int overdue;
            public int satisfied;
            public int dissatisfied;
        }
        /// <summary>
        /// 导出反馈类型明细表
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public ActionResult ExitTypelist(string stime, string endtime)
        {
            DataTable dt = new DataTable();
            string sql = $"  F_IsDelete=0";
            if (stime != null && stime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }
            sql += $"  and T_Wo_WorkOrder.F_State in ( " + (int)EnumWorkOrderState.finish + "," + (int)EnumWorkOrderState.evaluate + ") "; ;
            // var depts = new BLL.T_Wo_WorkOrder().GetModelList(sql);
            dt.Columns.Add("反馈类型");
            dt.Columns.Add("新乡");
            dt.Columns.Add("九江");
            dt.Columns.Add("新疆");
            dt.Columns.Add("东北");
            dt.Columns.Add("合计");
            dt.Columns.Add("即时办结率");
           
            for (int i = 0; i < 8; i++)
            {
                DataRow drNew = dt.NewRow();
                switch (i)
                {
                    case 0:
                        drNew["反馈类型"] = "合计";
                        break;
                    case 1:
                        drNew["反馈类型"] = "咨询";
                        break;
                    case 2:
                        drNew["反馈类型"] = "投诉";
                        break;
                    case 3:
                        drNew["反馈类型"] = "抽检";
                        break;
                    case 4:
                        drNew["反馈类型"] = "建议";
                        break;
                    case 5:
                        drNew["反馈类型"] = "超期数量";
                        break;
                    case 6:
                        drNew["反馈类型"] = "满意数量";
                        break;
                    case 7:
                        drNew["反馈类型"] = "不满意数量";
                        break;
                }
                string nsg = "";
                if (i > 0&& i<5)
                {
                    nsg = "and F_Type=" + i;
                }
              else   if (i > 4)
                {
                    if (i ==5)
                    {
                        nsg = "and F_IsOver=" + 1;
                    }
                   else  if (i == 6)
                    {
                        nsg = "and F_VisitResult in('非常满意','满意')";
                    }
                    else if (i == 7)
                    {
                        nsg = "and F_VisitResult='不满意'";
                    }
                }
                drNew["新乡"] = woBLL.GetModelList(sql + "and F_SalesBase='新乡'" + nsg).Count.ToString();
                drNew["九江"] = woBLL.GetModelList(sql + "and F_SalesBase='九江'" + nsg).Count.ToString(); ;
                drNew["新疆"] = woBLL.GetModelList(sql + "and F_SalesBase='新疆' " + nsg).Count.ToString();
                drNew["东北"] = woBLL.GetModelList(sql + "and F_SalesBase='东北'" + nsg).Count.ToString();
                drNew["合计"] = woBLL.GetModelList(sql + nsg).Count.ToString();
                int a = woBLL.GetModelList(sql + "and F_DealType='当即办理'" + nsg).Count;
                int b = woBLL.GetModelList(sql + nsg).Count;
                if (b > 0)
                {
                    drNew["即时办结率"] = string.Format("{0:f2}%", a / b * 100);
                }
                else
                {
                    drNew["即时办结率"] = string.Format("0.00%");
                }
                dt.Rows.Add(drNew);
            }

            NPOIHelper npoi = new NPOIHelper();
            if (npoi.ExportToExcel("反馈类型明细表", dt) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");

            }
        }
        /// <summary>
        /// 反馈类型明细表
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>

        public ActionResult GetTypelist(string stime, string endtime)
        {
            string sql = $"  F_IsDelete=0";
            if (stime != null && stime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }
            sql += $"  and T_Wo_WorkOrder.F_State in ( " + (int)EnumWorkOrderState.finish + "," + (int)EnumWorkOrderState.evaluate + ") "; ;
            List<marketList> markets = new List<marketList>();


            for (int i = 0; i < 8; i++)
            {
                marketList brand = new marketList();
                switch (i)
                {
                    case 0:
                        brand.typename = "合计";
                        break;
                    case 1:
                        brand.typename = "咨询";
                        break;
                    case 2:
                        brand.typename = "投诉";
                        break;
                    case 3:
                        brand.typename = "抽检";
                        break;
                    case 4:
                        brand.typename = "建议";
                        break;
                    case 5:
                        brand.typename = "超期数量";
                        break;
                    case 6:
                        brand.typename = "满意数量";
                        break;
                    case 7:
                        brand.typename = "不满意数量";
                        break;
                }
                string nsg = "";
                if (i > 0 && i < 5)
                {
                    nsg = "and F_Type=" + i;
                }
                else if (i > 4)
                {
                    if (i == 5)
                    {
                        nsg = "and F_IsOver=" + 1;
                    }
                    else if (i == 6)
                    {
                        nsg = "and F_VisitResult in('非常满意','满意')";
                    }
                    else if (i == 7)
                    {
                        nsg = "and F_VisitResult='不满意'";
                    }
                }

                brand.salexinxinag = woBLL.GetModelList(sql + "and F_SalesBase='新乡'" + nsg).Count;
                brand.salejiujiang = woBLL.GetModelList(sql + "and F_SalesBase='九江'" + nsg).Count;
                brand.salexinjiang = woBLL.GetModelList(sql + "and F_SalesBase='新疆'" + nsg).Count;
                brand.saledongbei = woBLL.GetModelList(sql + "and F_SalesBase='东北'" + nsg).Count;
                brand.total = woBLL.GetModelList(sql + nsg).Count;
                int a = woBLL.GetModelList(sql + "and F_DealType='当即办理'" + nsg).Count;
                int b = woBLL.GetModelList(sql + nsg).Count;
                if (b > 0)
                {
                    brand.immediate = string.Format("{0:f2}%", a / b * 100);
                }
                else
                {
                    brand.immediate = string.Format("0.00%");
                }
                markets.Add(brand);
            }


            return Success("获取成功", markets);
        }
        /// <summary>
        /// 导出销售基地明细表
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public ActionResult ExitSalesBaselist(string stime, string endtime)
        {
            DataTable dt = new DataTable();
            string sql = $"  F_IsDelete=0";
            if (stime != null && stime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }
            sql += $"  and T_Wo_WorkOrder.F_State in ( " + (int)EnumWorkOrderState.finish + "," + (int)EnumWorkOrderState.evaluate + ") "; ;
            var ConsultingNeeds = quesBLL.GetList("  F_Type = '" + EnumWOType.ConsultingNeeds.ToString() + "' and F_IsDelete=0 ").Tables[0];
            var Complaint = quesBLL.GetList("  F_Type = '" + EnumWOType.Complaint.ToString() + "' and F_IsDelete=0 " + "and F_Label=3").Tables[0];
            var SpotCheck = quesBLL.GetList("  F_Type = '" + EnumWOType.SpotCheck.ToString() + "' and F_IsDelete=0 ").Tables[0];
            List<Model.T_Wo_QuestionManage> ConsultingNeedslist = quesBLL.DataTableToList(ConsultingNeeds);//咨询
            List<Model.T_Wo_QuestionManage> Complaintlist = quesBLL.DataTableToList(Complaint);//投诉
            List<Model.T_Wo_QuestionManage> SpotChecklist = quesBLL.DataTableToList(SpotCheck);//抽检
            List<Model.T_Wo_WorkOrder> workerdongbei = woBLL.GetModelList(sql + "and F_SalesBase='东北'");
            List<Model.T_Wo_WorkOrder> workerjiujiang = woBLL.GetModelList(sql + "and F_SalesBase='九江'");
            List<Model.T_Wo_WorkOrder> workerxinjiang = woBLL.GetModelList(sql + "and F_SalesBase='新疆'");
            List<Model.T_Wo_WorkOrder> workerxinxiang = woBLL.GetModelList(sql + "and F_SalesBase='新乡'");
            List<Model.T_Wo_WorkOrder> workertotal = woBLL.GetModelList(sql);
            dt.Columns.Add("类型");
            dt.Columns.Add("新乡");
            dt.Columns.Add("九江");
            dt.Columns.Add("新疆");
            dt.Columns.Add("东北");
            dt.Columns.Add("合计");
            for (int i = 0; i < ConsultingNeedslist.Count + Complaintlist.Count + SpotChecklist.Count + 5; i++)
            {
                DataRow drNew = dt.NewRow();
                int saledongbei = 0;
                int salejiujiang = 0;
                int salexinjiang = 0;
                int salexinxiang = 0;
                int total = 0;
                saledongbei = workercount(i, workerdongbei, ConsultingNeedslist, Complaintlist, SpotChecklist);
                salejiujiang = workercount(i, workerjiujiang, ConsultingNeedslist, Complaintlist, SpotChecklist);
                salexinjiang = workercount(i, workerxinjiang, ConsultingNeedslist, Complaintlist, SpotChecklist);
                salexinxiang = workercount(i, workerxinxiang, ConsultingNeedslist, Complaintlist, SpotChecklist);
                total = workercount(i, workertotal, ConsultingNeedslist, Complaintlist, SpotChecklist);
                market brand = new market();
                switch (i)
                {
                    case 0:
                        drNew["类型"] = "合计";

                        break;
                    case 1:
                        drNew["类型"] = "即时办结率";

                        break;
                    case 2:
                        drNew["类型"] = "超期数量";
                        break;
                    case 3:
                        drNew["类型"] = "满意数量";
                        break;
                    case 4:
                        drNew["类型"] = "不满意数量";
                        break;
                }
                if (i < ConsultingNeedslist.Count + 5 && i >= 5)
                {
                    drNew["类型"] = ConsultingNeedslist[i - 5].F_QuestionName;
                }
                else if (i >= ConsultingNeedslist.Count + 5 && i < ConsultingNeedslist.Count + Complaintlist.Count + 5)
                {
                    drNew["类型"] = Complaintlist[i - ConsultingNeedslist.Count - 5].F_QuestionName;
                }
                else if (i >= ConsultingNeedslist.Count + Complaintlist.Count + 5 && i < ConsultingNeedslist.Count + Complaintlist.Count + SpotChecklist.Count + 5)
                {
                    drNew["类型"] = SpotChecklist[i - ConsultingNeedslist.Count - Complaintlist.Count - 5].F_QuestionName;
                }
                if (i == 1)
                {
                    if (total > 0)
                    {
                        drNew["东北"] = string.Format("{0:f2}%", saledongbei / total * 100);
                        drNew["九江"] = string.Format("{0:f2}%", salejiujiang / total * 100);
                        drNew["新疆"] = string.Format("{0:f2}%", salexinjiang / total * 100);
                        drNew["新乡"] = string.Format("{0:f2}%", salexinxiang / total * 100);
                        drNew["合计"] = string.Format("{0:f2}%", 100);
                    }
                    else
                    {
                        drNew["东北"] = "0.00%";
                        drNew["九江"] = "0.00%";
                        drNew["新疆"] = "0.00%";
                        drNew["新乡"] = "0.00%";
                        drNew["合计"] = "0.00%";
                    }
                }
                else
                {
                    drNew["东北"] = saledongbei.ToString();
                    drNew["九江"] = salejiujiang.ToString();
                    drNew["新疆"] = salexinjiang.ToString();
                    drNew["新乡"] = salexinxiang.ToString();
                    drNew["合计"] = total.ToString();
                }
                dt.Rows.Add(drNew);
            }
            NPOIHelper npoi = new NPOIHelper();
            if (npoi.ExportToExcel("销售基地明细表", dt) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");

            }
        }
        /// <summary>
        /// 销售基地明细表
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>

        public ActionResult GetSalesBaselist(string stime, string endtime)
        {
            string sql = $"  F_IsDelete=0";
            if (stime != null && stime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }
            sql += $"  and T_Wo_WorkOrder.F_State in ( " + (int)EnumWorkOrderState.finish + "," + (int)EnumWorkOrderState.evaluate + ") "; ;
            var ConsultingNeeds = quesBLL.GetList("  F_Type = '" + EnumWOType.ConsultingNeeds.ToString() + "' and F_IsDelete=0 ").Tables[0];
            var Complaint = quesBLL.GetList("  F_Type = '" + EnumWOType.Complaint.ToString() + "' and F_IsDelete=0 " + "and F_Label=3").Tables[0];
            var SpotCheck = quesBLL.GetList("  F_Type = '" + EnumWOType.SpotCheck.ToString() + "' and F_IsDelete=0 ").Tables[0];
            List<Model.T_Wo_QuestionManage> ConsultingNeedslist = quesBLL.DataTableToList(ConsultingNeeds);//咨询
            List<Model.T_Wo_QuestionManage> Complaintlist = quesBLL.DataTableToList(Complaint);//投诉
            List<Model.T_Wo_QuestionManage> SpotChecklist = quesBLL.DataTableToList(SpotCheck);//抽检
            List<Model.T_Wo_WorkOrder> workerdongbei = woBLL.GetModelList(sql + "and F_SalesBase='东北'");
            List<Model.T_Wo_WorkOrder> workerjiujiang = woBLL.GetModelList(sql + "and F_SalesBase='九江'");
            List<Model.T_Wo_WorkOrder> workerxinjiang = woBLL.GetModelList(sql + "and F_SalesBase='新疆'");
            List<Model.T_Wo_WorkOrder> workerxinxiang = woBLL.GetModelList(sql + "and F_SalesBase='新乡'");
            List<Model.T_Wo_WorkOrder> workertotal = woBLL.GetModelList(sql);
            List<market> markets = new List<market>();


            for (int i = 0; i < ConsultingNeedslist.Count + Complaintlist.Count + SpotChecklist.Count + 5; i++)
            {
                int saledongbei = 0;
                int salejiujiang = 0;
                int salexinjiang = 0;
                int salexinxiang = 0;
                int total = 0;
                saledongbei = workercount(i, workerdongbei, ConsultingNeedslist, Complaintlist, SpotChecklist);
                salejiujiang = workercount(i, workerjiujiang, ConsultingNeedslist, Complaintlist, SpotChecklist);
                salexinjiang = workercount(i, workerxinjiang, ConsultingNeedslist, Complaintlist, SpotChecklist);
                salexinxiang = workercount(i, workerxinxiang, ConsultingNeedslist, Complaintlist, SpotChecklist);
                total = workercount(i, workertotal, ConsultingNeedslist, Complaintlist, SpotChecklist);
                market brand = new market();
                switch (i)
                {
                    case 0:
                        brand.typename = "合计";

                        break;
                    case 1:
                        brand.typename = "即时办结率";

                        break;
                    case 2:
                        brand.typename = "超期数量";
                        break;
                    case 3:
                        brand.typename = "满意数量";
                        break;
                    case 4:
                        brand.typename = "不满意数量";
                        break;
                }
                if (i < ConsultingNeedslist.Count + 5 && i >= 5)
                {
                    brand.typename = ConsultingNeedslist[i - 5].F_QuestionName;
                }
                else if (i >= ConsultingNeedslist.Count + 5 && i < ConsultingNeedslist.Count + Complaintlist.Count + 5)
                {
                    brand.typename = Complaintlist[i - ConsultingNeedslist.Count - 5].F_QuestionName;
                }
                else if (i >= ConsultingNeedslist.Count + Complaintlist.Count + 5 && i < ConsultingNeedslist.Count + Complaintlist.Count + SpotChecklist.Count + 5)
                {
                    brand.typename = SpotChecklist[i - ConsultingNeedslist.Count - Complaintlist.Count - 5].F_QuestionName;
                }
                if (i == 1)
                {
                    if (total > 0)
                    {
                        brand.saledongbei = string.Format("{0:f2}%", saledongbei / total * 100);
                        brand.salejiujiang = string.Format("{0:f2}%", salejiujiang / total * 100);
                        brand.salexinjiang = string.Format("{0:f2}%", salexinjiang / total * 100);
                        brand.salexinxiang = string.Format("{0:f2}%", salexinxiang / total * 100);
                        brand.total = string.Format("{0:f2}%", 100);
                    }
                    else
                    {
                        brand.saledongbei = "0.00%";
                        brand.salejiujiang = "0.00%";
                        brand.salexinjiang = "0.00%";
                        brand.salexinxiang = "0.00%";
                        brand.total = "0.00%";
                    }
                }
                else
                {
                    brand.saledongbei = saledongbei.ToString();
                    brand.salejiujiang = salejiujiang.ToString();
                    brand.salexinjiang = salexinjiang.ToString();
                    brand.salexinxiang = salexinxiang.ToString();
                    brand.total = total.ToString();
                }
                markets.Add(brand);
            }

            return Success("获取成功", markets);
        }
        public class Category
        {
            public string typename;
            public string urea;//尿素
            public string compound;//复合肥
            public string fertilizer;//水溶肥
            public string vehicle;//车用尿素
            public string melamine;//三聚氰胺
            public string methanol;//甲醇
            public string furfuryl;//糠醇
            public string humic;//腐植酸
            public string dimethyl;//二甲醚
            public string furfural;//糠醛
            public string liquid;//液氨
            public string other;//其他
           
        }
        /// <summary>
        /// 导出产品名称明细表
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public ActionResult ExitProductlist(string stime, string endtime)
        {
            DataTable dt = new DataTable();
            string sql = $"  F_IsDelete=0";
            if (stime != null && stime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }
            sql += $"  and T_Wo_WorkOrder.F_State in ( " + (int)EnumWorkOrderState.finish + "," + (int)EnumWorkOrderState.evaluate + ") "; ;
            var ConsultingNeeds = quesBLL.GetList("  F_Type = '" + EnumWOType.ConsultingNeeds.ToString() + "' and F_IsDelete=0 ").Tables[0];
            var Complaint = quesBLL.GetList("  F_Type = '" + EnumWOType.Complaint.ToString() + "' and F_IsDelete=0 " + "and F_Label=3").Tables[0];
            var SpotCheck = quesBLL.GetList("  F_Type = '" + EnumWOType.SpotCheck.ToString() + "' and F_IsDelete=0 ").Tables[0];
            List<Model.T_Wo_QuestionManage> ConsultingNeedslist = quesBLL.DataTableToList(ConsultingNeeds);//咨询
            List<Model.T_Wo_QuestionManage> Complaintlist = quesBLL.DataTableToList(Complaint);//投诉
            List<Model.T_Wo_QuestionManage> SpotChecklist = quesBLL.DataTableToList(SpotCheck);//抽检
            List<Model.T_Wo_WorkOrder> workerurea = woBLL.GetModelList(sql + "and F_TS_Category='尿素'");
            List<Model.T_Wo_WorkOrder> workercompound = woBLL.GetModelList(sql + "and F_TS_Category='复合肥'");
            List<Model.T_Wo_WorkOrder> workerfertilizer = woBLL.GetModelList(sql + "and F_TS_Category='水溶肥'");
            List<Model.T_Wo_WorkOrder> workervehicle = woBLL.GetModelList(sql + "and F_TS_Category='车用尿素'");
            List<Model.T_Wo_WorkOrder> workermelamine = woBLL.GetModelList(sql + "and F_TS_Category='三聚氰胺'");
            List<Model.T_Wo_WorkOrder> workermethanol = woBLL.GetModelList(sql + "and F_TS_Category='甲醇'");
            List<Model.T_Wo_WorkOrder> workerfurfuryl = woBLL.GetModelList(sql + "and F_TS_Category='糠醇'");
            List<Model.T_Wo_WorkOrder> workerhumic = woBLL.GetModelList(sql + "and F_TS_Category='腐植酸'");
            List<Model.T_Wo_WorkOrder> workerdimethyl = woBLL.GetModelList(sql + "and F_TS_Category='二甲醚'");
            List<Model.T_Wo_WorkOrder> workerfurfural = woBLL.GetModelList(sql + "and F_TS_Category='糠醛'");
            List<Model.T_Wo_WorkOrder> workerliquid = woBLL.GetModelList(sql + "and F_TS_Category='液氨'");
            List<Model.T_Wo_WorkOrder> workerother = woBLL.GetModelList(sql + "and F_TS_Category='其他'");
            List<Model.T_Wo_WorkOrder> workertotal = woBLL.GetModelList(sql);
            dt.Columns.Add("类型");
            dt.Columns.Add("尿素");
            dt.Columns.Add("复合肥");
            dt.Columns.Add("水溶肥");
            dt.Columns.Add("车用尿素");
            dt.Columns.Add("三聚氰胺");
            dt.Columns.Add("甲醇");
            dt.Columns.Add("糠醇");
            dt.Columns.Add("腐植酸");
            dt.Columns.Add("二甲醚");
            dt.Columns.Add("糠醛");
            dt.Columns.Add("液氨");
            dt.Columns.Add("其他");
            for (int i = 0; i < ConsultingNeedslist.Count + Complaintlist.Count + SpotChecklist.Count + 5; i++)
            {
                DataRow drNew = dt.NewRow();
                int urea = 0, compound = 0, fertilizer = 0, vehicle = 0, total = 0, melamine = 0, methanol = 0, furfuryl = 0
                  , humic = 0, dimethyl = 0, furfural = 0, liquid = 0, other = 0;
                urea = workercount(i, workerurea, ConsultingNeedslist, Complaintlist, SpotChecklist);
                compound = workercount(i, workercompound, ConsultingNeedslist, Complaintlist, SpotChecklist);
                fertilizer = workercount(i, workerfertilizer, ConsultingNeedslist, Complaintlist, SpotChecklist);
                vehicle = workercount(i, workervehicle, ConsultingNeedslist, Complaintlist, SpotChecklist);
                melamine = workercount(i, workermelamine, ConsultingNeedslist, Complaintlist, SpotChecklist);
                methanol = workercount(i, workermethanol, ConsultingNeedslist, Complaintlist, SpotChecklist);
                furfuryl = workercount(i, workerfurfuryl, ConsultingNeedslist, Complaintlist, SpotChecklist);
                humic = workercount(i, workerhumic, ConsultingNeedslist, Complaintlist, SpotChecklist);
                dimethyl = workercount(i, workerdimethyl, ConsultingNeedslist, Complaintlist, SpotChecklist);
                furfural = workercount(i, workerfurfural, ConsultingNeedslist, Complaintlist, SpotChecklist);
                liquid = workercount(i, workerliquid, ConsultingNeedslist, Complaintlist, SpotChecklist);
                other = workercount(i, workerother, ConsultingNeedslist, Complaintlist, SpotChecklist);
                total = workercount(i, workertotal, ConsultingNeedslist, Complaintlist, SpotChecklist);
                Category brand = new Category();
                switch (i)
                {
                    case 0:
                        drNew["类型"] = "合计";

                        break;
                    case 1:
                        drNew["类型"] = "即时办结率";

                        break;
                    case 2:
                        drNew["类型"] = "超期数量";
                        break;
                    case 3:
                        drNew["类型"] = "满意数量";
                        break;
                    case 4:
                        drNew["类型"] = "不满意数量";
                        break;
                }
                if (i < ConsultingNeedslist.Count + 5 && i >= 5)
                {
                    drNew["类型"] = ConsultingNeedslist[i - 5].F_QuestionName;
                }
                else if (i >= ConsultingNeedslist.Count + 5 && i < ConsultingNeedslist.Count + Complaintlist.Count + 5)
                {
                    drNew["类型"] = Complaintlist[i - ConsultingNeedslist.Count - 5].F_QuestionName;
                }
                else if (i >= ConsultingNeedslist.Count + Complaintlist.Count + 5 && i < ConsultingNeedslist.Count + Complaintlist.Count + SpotChecklist.Count + 5)
                {
                    drNew["类型"] = SpotChecklist[i - ConsultingNeedslist.Count - Complaintlist.Count - 5].F_QuestionName;
                }
                if (i == 1)
                {
                    if (total > 0)
                    {
                        drNew["尿素"] = string.Format("{0:f2}%", urea / total * 100);
                        drNew["复合肥"] = string.Format("{0:f2}%", compound / total * 100);
                        drNew["水溶肥"] = string.Format("{0:f2}%", fertilizer / total * 100);
                        drNew["车用尿素"] = string.Format("{0:f2}%", vehicle / total * 100);
                        drNew["三聚氰胺"] = string.Format("{0:f2}%", melamine / total * 100);
                        drNew["甲醇"] = string.Format("{0:f2}%", methanol / total * 100);
                        drNew["糠醇"] = string.Format("{0:f2}%", furfuryl / total * 100);
                        drNew["腐植酸"] = string.Format("{0:f2}%", humic / total * 100);
                        drNew["二甲醚"] = string.Format("{0:f2}%", dimethyl / total * 100);
                        drNew["糠醛"] =string.Format("{0:f2}%", furfural / total * 100);
                        drNew["液氨"] = string.Format("{0:f2}%", liquid / total * 100);
                        drNew["其他"] = string.Format("{0:f2}%", other / total * 100);
                    }
                    else
                    {
                        drNew["尿素"] = "0.00%";
                        drNew["复合肥"] = "0.00%";
                        drNew["水溶肥"] = "0.00%";
                        drNew["车用尿素"] = "0.00%";
                        drNew["三聚氰胺"] = "0.00%";
                        drNew["甲醇"] = "0.00%";
                        drNew["糠醇"] = "0.00%";
                        drNew["腐植酸"] = "0.00%";
                        drNew["二甲醚"] = "0.00%";
                        drNew["糠醛"] = "0.00%";
                        drNew["液氨"] = "0.00%";
                        drNew["其他"] = "0.00%";
                    }
                }
                else
                {
                    drNew["尿素"] = urea.ToString();
                    drNew["复合肥"] = compound.ToString();
                    drNew["水溶肥"] = fertilizer.ToString();
                    drNew["车用尿素"] = vehicle.ToString();
                    drNew["三聚氰胺"] = melamine.ToString();
                    drNew["甲醇"] = methanol.ToString();
                    drNew["糠醇"] = furfuryl.ToString();
                    drNew["腐植酸"] = humic.ToString();
                    drNew["二甲醚"] = dimethyl.ToString();
                    drNew["糠醛"] = furfural.ToString();
                    drNew["液氨"] = liquid.ToString();
                    drNew["其他"] = other.ToString();
                }
                dt.Rows.Add(drNew);
            }
            NPOIHelper npoi = new NPOIHelper();
            if (npoi.ExportToExcel("产品名称明细表", dt) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");

            }
        }
        /// <summary>
        /// 产品名称明细表
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>

        public ActionResult GetProductlist(string stime, string endtime)
        {
            string sql = $"  F_IsDelete=0";
            if (stime != null && stime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }
            sql += $"  and T_Wo_WorkOrder.F_State in ( " + (int)EnumWorkOrderState.finish + "," + (int)EnumWorkOrderState.evaluate + ") "; ;
            var ConsultingNeeds = quesBLL.GetList("  F_Type = '" + EnumWOType.ConsultingNeeds.ToString() + "' and F_IsDelete=0 ").Tables[0];
            var Complaint = quesBLL.GetList("  F_Type = '" + EnumWOType.Complaint.ToString() + "' and F_IsDelete=0 " + "and F_Label=3").Tables[0];
            var SpotCheck = quesBLL.GetList("  F_Type = '" + EnumWOType.SpotCheck.ToString() + "' and F_IsDelete=0 ").Tables[0];
        List<Model.T_Wo_WorkOrder> workerurea = woBLL.GetModelList(sql + "and F_TS_Category='尿素'");
        List<Model.T_Wo_WorkOrder> workercompound = woBLL.GetModelList(sql + "and F_TS_Category='复合肥'");
        List<Model.T_Wo_WorkOrder> workerfertilizer = woBLL.GetModelList(sql + "and F_TS_Category='水溶肥'");
        List<Model.T_Wo_WorkOrder> workervehicle = woBLL.GetModelList(sql + "and F_TS_Category='车用尿素'");
        List<Model.T_Wo_WorkOrder> workermelamine = woBLL.GetModelList(sql + "and F_TS_Category='三聚氰胺'");
        List<Model.T_Wo_WorkOrder> workermethanol = woBLL.GetModelList(sql + "and F_TS_Category='甲醇'");
        List<Model.T_Wo_WorkOrder> workerfurfuryl = woBLL.GetModelList(sql + "and F_TS_Category='糠醇'");
        List<Model.T_Wo_WorkOrder> workerhumic = woBLL.GetModelList(sql + "and F_TS_Category='腐植酸'");
        List<Model.T_Wo_WorkOrder> workerdimethyl = woBLL.GetModelList(sql + "and F_TS_Category='二甲醚'");
        List<Model.T_Wo_WorkOrder> workerfurfural = woBLL.GetModelList(sql + "and F_TS_Category='糠醛'");
        List<Model.T_Wo_WorkOrder> workerliquid = woBLL.GetModelList(sql + "and F_TS_Category='液氨'");
        List<Model.T_Wo_WorkOrder> workerother = woBLL.GetModelList(sql + "and F_TS_Category='其他'");
            List<Model.T_Wo_WorkOrder> workertotal = woBLL.GetModelList(sql );
            List<Model.T_Wo_QuestionManage> ConsultingNeedslist = quesBLL.DataTableToList(ConsultingNeeds);//咨询
            List<Model.T_Wo_QuestionManage> Complaintlist = quesBLL.DataTableToList(Complaint);//投诉
            List<Model.T_Wo_QuestionManage> SpotChecklist = quesBLL.DataTableToList(SpotCheck);//抽检
            List<Category> markets = new List<Category>();
            for (int i = 0; i < ConsultingNeedslist.Count + Complaintlist.Count + SpotChecklist.Count + 5; i++)
            {
                int urea = 0, compound=0, fertilizer=0, vehicle=0, total=0, melamine=0, methanol=0, furfuryl=0
                    , humic=0, dimethyl=0, furfural=0, liquid=0, other=0;
                urea = workercount(i, workerurea, ConsultingNeedslist, Complaintlist, SpotChecklist);
                compound = workercount(i, workercompound, ConsultingNeedslist, Complaintlist, SpotChecklist);
                fertilizer = workercount(i, workerfertilizer, ConsultingNeedslist, Complaintlist, SpotChecklist);
                vehicle = workercount(i, workervehicle, ConsultingNeedslist, Complaintlist, SpotChecklist);
                melamine = workercount(i, workermelamine, ConsultingNeedslist, Complaintlist, SpotChecklist);
                methanol = workercount(i, workermethanol, ConsultingNeedslist, Complaintlist, SpotChecklist);
                furfuryl = workercount(i, workerfurfuryl, ConsultingNeedslist, Complaintlist, SpotChecklist);
                humic = workercount(i, workerhumic, ConsultingNeedslist, Complaintlist, SpotChecklist);
                dimethyl = workercount(i, workerdimethyl, ConsultingNeedslist, Complaintlist, SpotChecklist);
                furfural = workercount(i, workerfurfural, ConsultingNeedslist, Complaintlist, SpotChecklist);
                liquid = workercount(i, workerliquid, ConsultingNeedslist, Complaintlist, SpotChecklist);
                other = workercount(i, workerother, ConsultingNeedslist, Complaintlist, SpotChecklist);
                total = workercount(i, workertotal, ConsultingNeedslist, Complaintlist, SpotChecklist);
                Category brand = new Category();
                switch (i)
                {
                    case 0:
                        brand.typename = "合计";

                        break;
                    case 1:
                        brand.typename = "即时办结率";

                        break;
                    case 2:
                        brand.typename = "超期数量";
                        break;
                    case 3:
                        brand.typename = "满意数量";
                        break;
                    case 4:
                        brand.typename = "不满意数量";
                        break;
                }
                if (i < ConsultingNeedslist.Count + 5 && i >= 5)
                {
                    brand.typename = ConsultingNeedslist[i - 5].F_QuestionName;
                }
                else if (i >= ConsultingNeedslist.Count + 5 && i < ConsultingNeedslist.Count + Complaintlist.Count + 5)
                {
                    brand.typename = Complaintlist[i - ConsultingNeedslist.Count - 5].F_QuestionName;
                }
                else if (i >= ConsultingNeedslist.Count + Complaintlist.Count + 5 && i < ConsultingNeedslist.Count + Complaintlist.Count + SpotChecklist.Count + 5)
                {
                    brand.typename = SpotChecklist[i - ConsultingNeedslist.Count - Complaintlist.Count - 5].F_QuestionName;
                }
                if (i == 1)
                {
                    if (total > 0)
                    {
                        brand.urea  = string.Format("{0:f2}%", urea / total * 100);
                        brand.compound = string.Format("{0:f2}%", compound / total * 100);
                        brand.fertilizer = string.Format("{0:f2}%", fertilizer / total * 100);
                        brand.vehicle = string.Format("{0:f2}%", vehicle / total * 100);
                        brand.melamine = string.Format("{0:f2}%", melamine / total * 100);
                        brand.methanol = string.Format("{0:f2}%", methanol / total * 100);
                        brand.furfuryl = string.Format("{0:f2}%", furfuryl / total * 100);
                        brand.humic = string.Format("{0:f2}%", humic / total * 100);
                        brand.dimethyl = string.Format("{0:f2}%", dimethyl / total * 100);
                        brand.furfural = string.Format("{0:f2}%", furfural / total * 100);
                        brand.liquid = string.Format("{0:f2}%", liquid / total * 100);
                        brand.other = string.Format("{0:f2}%", other / total * 100);
                    }
                    else
                    {
                      
                        brand.urea = "0.00%";
                        brand.compound = "0.00%";
                        brand.fertilizer = "0.00%";
                        brand.vehicle = "0.00%";
                        brand.melamine = "0.00%";
                        brand.methanol = "0.00%";
                        brand.furfuryl = "0.00%";
                        brand.humic = "0.00%";
                        brand.dimethyl = "0.00%";
                        brand.furfural = "0.00%";
                        brand.liquid = "0.00%";
                        brand.other = "0.00%";
                    }
                }
                else
                {
                    brand.urea = urea.ToString();
                    brand.compound = compound.ToString();
                    brand.fertilizer = fertilizer.ToString();
                    brand.vehicle = vehicle.ToString();
                    brand.melamine = melamine.ToString();
                    brand.methanol = methanol.ToString();
                    brand.furfuryl = furfuryl.ToString();
                    brand.humic = humic.ToString();
                    brand.dimethyl = dimethyl.ToString();
                    brand.furfural = furfural.ToString();
                    brand.liquid = liquid.ToString();
                    brand.other = other.ToString();
                }
                markets.Add(brand);
            }

            return Success("获取成功", markets);
        }

        public class Department
        {
            public string dapatname;
            public int pending;//待接单
            public int beprocessed;//待处理
            public int processed;//已处理
            public int Overdue;//超期
            public int satisfied;//满意
            public int dissatisfied;//不满意
        }
        public int  GetDeptWorkOrderID(int  deptId)
        {
            int str =0;
            var user = new BLL.T_Sys_UserAccount().GetModelList("  F_DeptId =" + deptId);
            
           foreach (var it in user)
            {
                if (it .F_RoleId==59)
                {
                    str = deptId;
                    break;
                }
            }
            return str;
        }
        /// <summary>
        /// 导出部门工单数量表
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public ActionResult ExitDepartmentlist(string stime, string endtime)
        {
            DataTable dt = new DataTable();
            string sql = $"  F_IsDelete=0";
            if (stime != null && stime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }
            var tab = new BLL.T_Sys_Department().GetModelList(" F_State=1 and F_Layer=0");
            List<int> modeldeptid = new List<int>();
            List<string> modeldeptname = new List<string>();
            foreach (var it in tab)
            {
                if (GetDeptWorkOrderID(it.F_DeptId) > 0)
                {
                    modeldeptid.Add(it.F_DeptId);
                    modeldeptname.Add(it.F_DeptName);
                }
            }
            dt.Columns.Add("部门名称");
            dt.Columns.Add("待接单");
            dt.Columns.Add("待处理");
            dt.Columns.Add("已处理");
            dt.Columns.Add("超期数量");
            dt.Columns.Add("满意数量");
            dt.Columns.Add("不满意数量");
            
            for (int i = 0; i < modeldeptid.Count; i++)
            {
                DataRow drNew = dt.NewRow();
                var itemlist = itembll.GetModelList("F_NextDept=" + modeldeptid[i]);
                drNew["部门名称"] = modeldeptname[i];
                int pending = 0; int beprocessed = 0; int processed = 0; int Overdue = 0; int satisfied = 0; int dissatisfied = 0;
                foreach (var it in itemlist)
                {
                    if (it.F_IsUsed == 0 && it.F_OptType == 1)
                    {
                        pending++;
                        var Overworkorder = woBLL.GetModelList(sql +"and F_IsOver=1 and F_ID=" + it.F_WoID);
                        Overdue++;
                    }
                    if (it.F_OptType == 2 && it.F_IsUsed == 0)
                    {
                        beprocessed++;
                        var Overworkorder = woBLL.GetModelList(sql + "and F_IsOver=1 and F_ID=" + it.F_WoID);
                        Overdue++;
                    }
                }
                var user = new BLL.T_Sys_UserAccount().GetModelList("  F_DeptId =" + modeldeptid[i]);
                foreach (var it in user)
                {
                    if (it.F_RoleId == 59)
                    {
                        var workorder = woBLL.GetModelList(sql + "and F_State=10 and F_DealBy='" + it.F_UserCode + "'");
                        processed += workorder.Count;
                        foreach (var im in workorder)
                        {
                            if (im.F_IsOver == 1)
                                Overdue++;
                            if (im.F_VisitResult == "满意" || im.F_VisitResult == "非常满意")
                                satisfied++;
                            if (im.F_VisitResult == "不满意")
                                dissatisfied++;
                        }
                    }
                    
                }
                drNew["待接单"]  = pending;
                drNew["待处理"] = beprocessed;
                drNew["已处理"] = processed;
                drNew["超期数量"] = Overdue;
                drNew["满意数量"] = satisfied;
                drNew["不满意数量"] = dissatisfied;
                dt.Rows.Add(drNew);
            }
            NPOIHelper npoi = new NPOIHelper();
            if (npoi.ExportToExcel("部门工单数量表", dt) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");

            }
        }
        /// <summary>
        /// 部门工单数量表
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public ActionResult GetDepartmentList(string stime, string endtime)
        {
            string sql = $"  F_IsDelete=0";
            if (stime != null && stime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }
            var  tab = new BLL.T_Sys_Department().GetModelList(" F_State=1 and F_Layer=0");
            List<int> modeldeptid = new List<int>();
            List<string > modeldeptname= new List<string>();
            foreach (var it in tab)
            {
                if (GetDeptWorkOrderID(it .F_DeptId )>0)
                {
                    modeldeptid.Add(it.F_DeptId);
                    modeldeptname.Add(it.F_DeptName);
                }
            }
            List<Department> Departmentlist = new List<Department>();
            for (int i=0;i< modeldeptid.Count;i++)
            {
                var itemlist = itembll.GetModelList("F_NextDept=" + modeldeptid[i]);
                Department model = new Department();
                model.dapatname = modeldeptname[i];
                int pending = 0;int beprocessed = 0;int processed = 0;int Overdue = 0;int satisfied = 0;int dissatisfied = 0;
                foreach (var it in itemlist)
                {
                    if (it .F_IsUsed ==0&&it .F_OptType ==1)
                    {
                        pending++;
                        var Overworkorder = woBLL.GetModelList(sql +"and F_IsOver=1 and F_ID="+ it.F_WoID );
                        Overdue++;
                    }
                    if (it .F_OptType ==2&& it.F_IsUsed == 0)
                    {
                        beprocessed++;
                        var Overworkorder = woBLL.GetModelList(sql + "and F_IsOver=1 and F_ID=" + it.F_WoID);
                        Overdue++;
                    }
                }
                var user = new BLL.T_Sys_UserAccount().GetModelList("  F_DeptId =" + modeldeptid[i ]);
                foreach (var it in user)
                {
                    if (it.F_RoleId == 59)
                    {
                        var workorder = woBLL.GetModelList(sql + "and F_State=10 and F_DealBy='" + it.F_UserCode + "'");
                        processed += workorder.Count;
                        foreach (var im in workorder)
                        {
                            if (im .F_IsOver ==1)
                                Overdue++;
                            if (im.F_VisitResult == "满意" || im.F_VisitResult == "非常满意")
                                satisfied++;
                            if (im.F_VisitResult == "不满意")
                                dissatisfied++;
                        }
                    }
                }
                model.pending = pending;
                model.beprocessed = beprocessed;
                model.processed = processed;
                model.Overdue = Overdue;
                model.satisfied = satisfied;
                model.dissatisfied = dissatisfied;
                Departmentlist.Add(model);
            }
            if (Departmentlist.Count > 0)
            {
                for (int j = 0; j < Departmentlist.Count - 1; j++)
                {
                    for (int z = 0; z < Departmentlist.Count - 1 - j; z++)
                    {
                        if (Departmentlist[z].processed < Departmentlist[z + 1].processed)
                        {
                            var temp = Departmentlist[z + 1];
                            Departmentlist[z + 1] = Departmentlist[z];
                            Departmentlist[z] = temp;
                        }
                    }
                }
            }
            return Success("获取成功", Departmentlist);
        }
        public class Returnvisit
        {
            public string name;
            public int bevisit;
            public int visit;
            public int Overdue;
            public int visitcont;
            public int satisfied;
            public int dissatisfied;
        }
        /// <summary>
        /// 导出客户回访数量表
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public ActionResult ExitReturnvisitList(string stime, string endtime)
        {
            DataTable dt = new DataTable();
            string sql = $"  F_IsDelete=0";
            if (stime != null && stime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }
            dt.Columns.Add("类别");
            dt.Columns.Add("待回访");
            dt.Columns.Add("已回访");
            dt.Columns.Add("回访总数");
            dt.Columns.Add("超期数量");
            dt.Columns.Add("满意数量");
            dt.Columns.Add("不满意数量");
            var workordewr = woBLL.GetModelList(sql + "and F_State in(10,11) and F_IsVisit =1");
            string name = "客户回访"; int bevisit = 0, visit = 0, Overdue = 0, satisfied = 0, dissatisfied = 0; int visitcont = 0;
            var workorder = woBLL.GetModelList(sql + "and F_State in(10,11)");
            visitcont = workorder.Count;
            foreach (var it in workordewr)
            {
                if (it.F_State == 10)
                    bevisit++;
                if (it.F_State == 11)
                    visit++;
                if (it.F_IsOver  == 1)
                    Overdue++;
                if (it.F_VisitResult == "满意" || it.F_VisitResult == "非常满意")
                    satisfied++;
                if (it.F_VisitResult == "不满意")
                    dissatisfied++;
            }
            var obj = new
            {
                name,
                bevisit,
                visit,
                visitcont,
                Overdue,
                satisfied,
                dissatisfied
            };
              DataRow drNew = dt.NewRow();
              drNew["类别"] = name;
              drNew["待回访"] = bevisit;
                drNew["已回访"] = visit;
                drNew["回访总数"] = visitcont;
                drNew["超期数量"] = Overdue;
                drNew["满意数量"] = satisfied;
                drNew["不满意数量"] = dissatisfied;
                dt.Rows.Add(drNew);
          
            NPOIHelper npoi = new NPOIHelper();
            if (npoi.ExportToExcel("客户回访数量表", dt) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");

            }
        }
        /// <summary>
        /// 客户回访数量表
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public ActionResult GetReturnvisitList(string stime, string endtime)
        {
            string sql = $"  F_IsDelete=0";
            if (stime != null && stime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }
            List<Returnvisit> returnvisits = new List<Returnvisit>();
            Returnvisit model = new Returnvisit();
            var workordewr = woBLL.GetModelList(sql + "and F_State in(10,11) and F_IsVisit =1");
            model. name ="客户回访"; int bevisit = 0, visit = 0, Overdue = 0, satisfied = 0, dissatisfied = 0;int visitcont = 0;
            var workorder = woBLL.GetModelList(sql + "and F_State in(10,11)");
            visitcont = workorder.Count;
            foreach (var it in workordewr)
            {
                if (it.F_State == 10)
                    bevisit++;
                if (it.F_State == 11)
                    visit++;
                if (it.F_IsOver == 1)
                    Overdue++;
                if (it.F_VisitResult == "满意" || it.F_VisitResult == "非常满意")
                    satisfied++;
                if (it.F_VisitResult == "不满意")
                    dissatisfied++;
            }
            model.bevisit = bevisit; model.visit = visit; model.visitcont = visitcont; model.Overdue = Overdue;
            model.satisfied = satisfied; model.dissatisfied = dissatisfied;
            returnvisits.Add(model);
            return Success("获取成功", returnvisits
           );

        }



        private int workercount(int i ,  List<Model .T_Wo_WorkOrder> workerlist, List<Model.T_Wo_QuestionManage> ConsultingNeedslist, List<Model.T_Wo_QuestionManage> Complaintlist, List<Model.T_Wo_QuestionManage> SpotChecklist)
        {
            int total = 0;
            foreach (var it in workerlist)
            {
                switch (i)
                {
                    case 0:
                            total++;
                        break;
                    case 1:
                        if (it.F_DealType == "当即办理")
                            total++;
                        break;
                    case 2:
                        if (it.F_IsOver == 1)
                            total++;
                        break;
                    case 3:
                        if (it.F_VisitResult == "满意" || it.F_VisitResult == "非常满意")
                            total++;
                        break;
                    case 4:
                        if (it.F_VisitResult == "不满意")
                            total++;
                        break;

                }
                if (i < ConsultingNeedslist.Count + 5 && i >= 5)
                {
                    if (it.F_QuestionType == ConsultingNeedslist[i - 5].F_Id.ToString () )
                        total++;
                }
                else if (i >= ConsultingNeedslist.Count + 5 && i < ConsultingNeedslist.Count + Complaintlist.Count + 5)
                {
                    if (it.F_QuestionType == Complaintlist[i - ConsultingNeedslist.Count - 5].F_Id.ToString())
                        total++;
                }
                else if (i >= ConsultingNeedslist.Count + Complaintlist.Count + 5 && i < ConsultingNeedslist.Count + Complaintlist.Count+ SpotChecklist.Count  + 5)
                {
                    if (it.F_QuestionType == SpotChecklist[i - ConsultingNeedslist.Count - Complaintlist.Count - 5].F_Id.ToString())
                        total++;
                }
            }
            return total;
        }
        /// <summary>
        /// 导出集团市场问题反馈汇总表
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public ActionResult ExptQuestionList(string stime, string endtime, string salesBase, string channel, int type = 0)
        {

            string sql = $"  F_IsDelete=0";
            DataTable dt = new DataTable();
            #region 筛选条件
            if (type > 0 && type < 5)//工单类型
                sql += $" and F_Type=" + type;
            if (stime != null && stime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }
            if (!string.IsNullOrWhiteSpace(salesBase))//销售基地
                sql += $" and F_SalesBase like '%" + salesBase.Trim() + "%'";
            if (!string.IsNullOrWhiteSpace(channel))
            {
                sql += $"  and T_Wo_WorkOrder.F_CustomerID in ( " + GetCustomerID(channel) + ") "; ;
            }
            sql += $"  and T_Wo_WorkOrder.F_State in ( " + (int)EnumWorkOrderState.finish + "," + (int)EnumWorkOrderState.evaluate + ") "; ;
            sql += " order by F_CreateOn desc";
            var depts = new BLL.T_Wo_WorkOrder().GetModelList(sql);
            #endregion
            dt.Columns.Add("创建时间");
            dt.Columns.Add("销售基地");
            dt.Columns.Add("渠道类型");
            dt.Columns.Add("反馈类型");
            dt.Columns.Add("费用");
            dt.Columns.Add("大区名称");
            dt.Columns.Add("分公司名称");
            dt.Columns.Add("业务员");
            dt.Columns.Add("品牌");
            dt.Columns.Add("产品");
            dt.Columns.Add("超时时限");
            dt.Columns.Add("回访与否");
            dt.Columns.Add("关闭与否");
            foreach (var it in depts)
            {
                DataRow drNew = dt.NewRow();
                if (!string .IsNullOrEmpty (it.F_CustomerID))
               {
                    if (valcode(it.F_CustomerID, 10))//验证客户编码为10位纯数字
                    {
                        Model.T_Cus_CustomerBaseNew cuModel = cusbll.GetModel(it.F_CustomerID.ToString());//通过客户编号获取客户资料
                        if (cuModel != null)
                        {
                            Model.T_Sys_Department pdModel = departmentBLL.GetModel(cuModel.F_AreaID);//通过公司id获取部门
                            if (pdModel != null)
                            {
                                drNew["大区名称"] = pdModel.F_DeptName;
                            }
                            Model.T_Sys_Department pdModel1 = departmentBLL.GetModel(cuModel.F_BranchID);
                            if (pdModel1 != null)
                            {
                                drNew["分公司名称"] = pdModel1.F_DeptName;
                            }
                            drNew["业务员"] = cuModel.F_Salesman;
                            drNew["品牌"] = cuModel.F_Brands;
                            drNew["渠道类型"] = cuModel.F_Channel;

                        }
                    }
                }
               
                drNew["产品"] = it.F_TS_Category;
                if (it.F_Type == "1")
                {
                    drNew["反馈类型"] = "咨询";//工单类型:咨询及需求、投诉、抽检、其他(其它手动输入别的点选)
                }
                else if (it.F_Type == "2")
                {
                    drNew["反馈类型"] = "投诉";//工单类型:咨询及需求、投诉、抽检、其他(其它手动输入别的点选)
                }
                else if (it.F_Type == "3")
                {
                    drNew["反馈类型"] = "抽检";//工单类型:咨询及需求、投诉、抽检、其他(其它手动输入别的点选)
                }
                else
                {
                    drNew["反馈类型"] = "建议及其他";//工单类型:咨询及需求、投诉、抽检、其他(其它手动输入别的点选)
                }
                drNew["销售基地"] = it.F_SalesBase;//销售基地:新乡、新疆、九江、东北、其他(其它手动输入别的点选)
                drNew["创建时间"] = it.F_CreateOn.ToString();
                if (TimeToOver(it.F_ID, it.F_limit) < 0)
                    drNew["超时时限"] = Math.Abs(TimeToOver(it.F_ID,it .F_limit )).ToString();
                else
                    drNew["超时时限"] = "0";
                if (it.F_State == 11 && it.F_IsVisit == 1)
                    drNew["回访与否"] = "是";
                else
                    drNew["回访与否"] = "否";
                if (it.F_State == 11)
                    drNew["关闭与否"] = "是";
                else
                    drNew["关闭与否"] = "否";
                dt .Rows.Add(drNew);
            }
            NPOIHelper npoi = new NPOIHelper();
            if (npoi.ExportToExcel("集团市场问题反馈汇总表", dt) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");

            }

        }
        /// <summary>
        /// 集团市场问题反馈汇总表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetQuestionList(string stime, string endtime, string salesBase, string channel, int type = 0, int page = 1, int limit = 10)
        {
            string sql = $" and F_IsDelete=0";
            DataTable dt = new DataTable();
            #region 筛选条件
            if (type > 0 && type < 5)//工单类型
                sql += $" and F_Type=" + type;
            if (stime != null && stime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }
            if (!string.IsNullOrWhiteSpace(salesBase))//销售基地
                sql += $" and F_SalesBase like '%" + salesBase.Trim() + "%'";
            if (!string.IsNullOrWhiteSpace(channel))
            {
                sql += $"  and T_Wo_WorkOrder.F_CustomerID in ( " + GetCustomerID(channel) + ") "; ;
            }
            sql += $"  and T_Wo_WorkOrder.F_State in ( " + (int)EnumWorkOrderState.finish + "," + (int)EnumWorkOrderState.evaluate + ") "; ;
            #endregion
            int recordCount = 0;
            if (!string.IsNullOrWhiteSpace(sql))
            {

                dt = BLL.PagerBLL.GetListPager(
                    "T_Wo_WorkOrder",
                    "F_ID",
                    "*",
                    sql,
                    "ORDER BY T_Wo_WorkOrder.F_ID desc",
                    limit,
                    page,
                    true,
                    out recordCount);
            }
            List<Model.T_Wo_WorkOrder> modlelist = new BLL.T_Wo_WorkOrder().DataTableToList(dt);
            if (modlelist.Count > 0)
            {
                List<Questions> input = modeltooip(modlelist);
                var obj = new
                {
                    state = "success",
                    message = "成功",
                    rows = input,
                    total = recordCount
                };

                return Content(obj.ToJson()); ;

            }
            else
            {

                return Success("暂无工单"); ;
            }

        }
        /// <summary>
        /// 导出化肥产品投诉明细表
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public ActionResult ExptComplaintList(string stime, string endtime, string salesBase, string channel)
        {

            string sql = $"  F_IsDelete=0";
            DataTable dt = new DataTable();
            #region 筛选条件
            sql += $" and F_Type=" + 2;
            if (stime != null && stime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }
            if (!string.IsNullOrWhiteSpace(salesBase))//销售基地
                sql += $" and F_SalesBase like '%" + salesBase.Trim() + "%'";
            if (!string.IsNullOrWhiteSpace(channel))
            {
                sql += $"  and T_Wo_WorkOrder.F_CustomerID in ( " + GetCustomerID(channel) + ") "; ;
            }
            sql += $"  and T_Wo_WorkOrder.F_QuestionType in ( '" + GetComplaint("'化肥产品质量问题反馈','化肥服务质量问题反馈','化肥农作物长势问题反馈'") + "') "; ;
            sql += $"  and T_Wo_WorkOrder.F_State in ( " + (int)EnumWorkOrderState.finish + "," + (int)EnumWorkOrderState.evaluate + ") "; ;
            sql += " order by F_CreateOn desc";
            #endregion
            var depts = new BLL.T_Wo_WorkOrder().GetModelList(sql);

            dt.Columns.Add("客户姓名");
            dt.Columns.Add("客户电话");
            dt.Columns.Add("公司名称");
            dt.Columns.Add("事发地-省");
            dt.Columns.Add("事发地-市");
            dt.Columns.Add("事发地-县");
            dt.Columns.Add("事发地-乡");
            dt.Columns.Add("事发地-详细");
            dt.Columns.Add("销售基地");
            dt.Columns.Add("大区名称");
            dt.Columns.Add("业务员");
            dt.Columns.Add("问题描述");
            dt.Columns.Add("问题类别1");
            dt.Columns.Add("问题类别2");
            dt.Columns.Add("问题类别3");
            dt.Columns.Add("客户编号");
            dt.Columns.Add("物料编码");
            dt.Columns.Add("物料名称");
            dt.Columns.Add("型号");
            dt.Columns.Add("规格");
            dt.Columns.Add("一级分类");
            dt.Columns.Add("二级分类");
            dt.Columns.Add("三级分类");
            dt.Columns.Add("品牌");
            dt.Columns.Add("复合肥肥效");
            dt.Columns.Add("产品线");
            dt.Columns.Add("工艺");
            dt.Columns.Add("生产批次号");
            dt.Columns.Add("问题数量");
            dt.Columns.Add("生产基地");
            dt.Columns.Add("初审意见");
            dt.Columns.Add("超出质保期限");
            dt.Columns.Add("质量事件等级");
            dt.Columns.Add("索赔金额");
            dt.Columns.Add("责任单位");
            dt.Columns.Add("审批意见");
            dt.Columns.Add("审批不同意意见");
            dt.Columns.Add("补偿方式");
            dt.Columns.Add("补偿钱款");
            dt.Columns.Add("对应责任单位");
            dt.Columns.Add("确认实收数量");
            dt.Columns.Add("改进要求");
            dt.Columns.Add("对应审批工作流单号");
            dt.Columns.Add("处理方式");
            dt.Columns.Add("处理人");
            dt.Columns.Add("处理时间");
            dt.Columns.Add("处理内容");
            dt.Columns.Add("处理结果");
            dt.Columns.Add("未处理原因");
            dt.Columns.Add("添加时间");
            foreach (var it in depts)
            {
                DataRow drNew = dt.NewRow();
                if (!string .IsNullOrEmpty (it.F_CustomerID))
                {
                    if (valcode(it.F_CustomerID, 10))//验证客户编码为10位纯数字
                    {
                        drNew["客户编号"] = it.F_CustomerID;//
                        Model.T_Cus_CustomerBaseNew cuModel = cusbll.GetModel(it.F_CustomerID.ToString());//通过客户编号获取客户资料
                        if (cuModel != null)
                        {
                            Model.T_Sys_Department pdModel = departmentBLL.GetModel(cuModel.F_AreaID);//通过公司id获取部门
                            if (pdModel != null)
                            {
                                drNew["大区名称"] = pdModel.F_DeptName;//
                            }
                            Model.T_Sys_Department pdModel1 = departmentBLL.GetModel(cuModel.F_BranchID);
                            if (pdModel1 != null)
                            {
                                drNew["分公司名称"] = pdModel1.F_DeptName;//分公司名称
                            }
                            drNew["业务员"] = cuModel.F_Salesman;//业务员
                        }
                    }

                }
                if (!string .IsNullOrEmpty (it.F_MaterialID))
                {
                    if (valcode(it.F_MaterialID.ToString(), 11))
                    {
                        drNew["物料编码"] = int.Parse(it.F_MaterialID);//验证物料编码为11位纯数字
                        Model.T_Wo_MaterialManage maModel = mmBLL.GetModel(it.F_MaterialID.ToString());//通过物料编码获取物料信息
                        if (maModel != null)
                        {
                            drNew["物料名称"] = maModel.F_MaterialName;//物料名称
                            drNew["型号"] = maModel.F_Model;//型号
                            drNew["规格"] = maModel.F_Specs;//规格
                            drNew["一级分类"] = maModel.F_Level1;//一级分类
                            drNew["二级分类"] = maModel.F_Level2;//二级分类
                            drNew["三级分类"] = maModel.F_Level3;//三级分类
                            drNew["品牌"] = maModel.F_Brand;//品牌
                            drNew["复合肥肥效"] = maModel.F_MaterialEffect;//F_MaterialEffect
                            drNew["产品线"] = maModel.F_Pipeline;//F_Pipeline
                            drNew["工艺"] = maModel.F_Craft;//F_Craft
                        }
                    }
                }
               
                if (!string.IsNullOrEmpty(it.F_QuestionType))
                {
                    Model.T_Wo_QuestionManage Manage = null;
                    try
                    {
                        int b = int.Parse(it.F_QuestionType.Trim());
                        Manage = quesBLL.GetModel(b);


                    }
                    catch (Exception)
                    {
                        Manage = quesBLL.GetModel(it.F_QuestionType); //问题类别（投诉抽捡和咨询类别不一样）
                    }
                    if (Manage != null)
                    {
                        if (Manage.F_Label == "1")
                        {
                            drNew["问题类别1"] = Manage.F_QuestionName;
                            drNew["问题类别2"] = "";
                            drNew["问题类别3"] = "";
                        }
                        else if (Manage.F_Label == "2")
                        {
                            Model.T_Wo_QuestionManage Manage1 = quesBLL.GetModel(int.Parse(Manage.F_ParentId.ToString()));//获取问题类别
                            drNew["问题类别1"] = Manage1.F_QuestionName;
                            drNew["问题类别2"] = Manage.F_QuestionName;
                            drNew["问题类别3"] = "";
                        }
                        else
                        {
                            Model.T_Wo_QuestionManage Manage1 = quesBLL.GetModel(int.Parse(Manage.F_ParentId.ToString()));//获取问题类别
                            Model.T_Wo_QuestionManage Manage2 = quesBLL.GetModel(int.Parse(Manage1.F_ParentId.ToString()));//获取问题类别
                            drNew["问题类别1"] = Manage2.F_QuestionName;//问题类别1
                            drNew["问题类别2"] = Manage1.F_QuestionName;//问题类别2
                            drNew["问题类别3"] = Manage.F_QuestionName;//问题类别3
                        }
                    }
                }
                drNew["客户姓名"] = it.F_CusName;//客户姓名
                drNew["客户电话"] = it.F_CusPhone;//
                drNew["公司名称"] = it.F_CompanyName;//
                drNew["事发地-省"] = it.F_IncidentProvince;//
                drNew["事发地-市"] = it.F_IncidentCity;//
                drNew["事发地-县"] = it.F_IncidentCountry;//
                drNew["事发地-乡"] = it.F_IncidentTownship;//
                drNew["事发地-详细"] = it.F_IncidentDetailed;//
                drNew["销售基地"] = it.F_SalesBase;//:新乡、新疆、九江、东北、其他(其它手动输入别的点选)
                drNew["问题描述"] = it.F_Description;//
                drNew["生产批次号"] = it.F_BatchNumber;//
                drNew["问题数量"] = it.F_TS_Quantity;//投诉-（吨）
                drNew["生产基地"] = it.F_SC_ProductBase;//市场管理科-（新乡、新疆、九江、东北、贴牌、无法确定）
                drNew["初审意见"] = it.F_SC_PreliminaryOpinion;// 市场管理科-:退货、换货、补偿、自行处理
                drNew["超出质保期限"] = it.F_SC_QualityMonth;//市场管理科-（算法：工单创建日期-生产日期，生产日期根据生产批次号获取，单位：月）
                drNew["质量事件等级"] = it.F_ZL_QualityEventLevel;// 质量管理科-（未遂、一般、较大、重大）
                drNew["索赔金额"] = it.F_ZL_ClaimAmount;//质量管理科-（单位元，对应责任单位）
                drNew["责任单位"] = it.F_ZL_ResponsibleUnit;//质量管理科-（需列出，可选多个）
                drNew["审批意见"] = it.F_ZL_ApprovalOpinions;// 质量管理科-（同意、不同意，若选择不同意，增加输入框输入补充内容）
                drNew["审批不同意意见"] = it.F_ZL_ApprovalDisagree;// 质量管理科-
                drNew["补偿方式"] = it.F_ZL_CompensationMethod;//质量管理科-（实物、钱款【单位元】，若选择钱款，增加输入框输入补充内容）
                drNew["补偿钱款"] = it.F_ZL_CompensationMoney;// 质量管理科-
                drNew["对应责任单位"] = it.F_ZL_CResponsibleUnits;//质量管理科-（生产、研发、农化、物流、质量管理科，销售内勤）
                drNew["确认实收数量"] = it.F_ZL_Fquantity;// 质量管理科-（吨）
                drNew["改进要求"] = it.F_ZL_ImprovementRequirements;//质量管理科-
                drNew["对应审批工作流单号"] = it.F_ZL_CApprovalNo;//质量管理科-
                drNew["处理方式"] = it.F_DealType;//:当即办理、电话转接、网络转办
                drNew["处理人"] = it.F_DealBy;//
                drNew["处理时间"] = it.F_DealTime;//
                drNew["处理内容"] = it.F_DealContent;//
                drNew["处理结果"] = it.F_DealResult;//
                drNew["未处理原因"] = it.F_DealReasons;//
                drNew["添加时间"] = it.F_CreateOn; ;//
                dt.Rows.Add(drNew);
            }
                NPOIHelper npoi = new NPOIHelper();
            if (npoi.ExportToExcel("化肥产品投诉明细表", dt) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");

            }

        }
        /// <summary>
        /// 获取问题类别
        /// </summary>
        /// <returns></returns>
        public string GetComplaint(string msg)
        {
            string str = "";
            string str1 = " select F_Id from T_Wo_QuestionManage where F_QuestionName in(" + msg + ")";
            string str3 = " select F_Id from T_Wo_QuestionManage where F_ParentId in(" + str1 + ")";
            var dept = quesBLL .GetModelList("F_ParentId in(" + str3+")");
            if (dept != null)
            {
                foreach (var it in dept)
                {
                    
                        if (str != "")
                        str += "','" + it.F_Id ;
                        else
                        str += it.F_Id;
                   
                }
            }
          //  string str4 = " select F_Id from T_Wo_QuestionManage where F_ParentId in(" + str3 + ")";
            return str;
        }

        /// <summary>
        /// 化肥产品投诉明细表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetComplaintList(string stime, string endtime, string salesBase, string channel, int page = 1, int limit = 10)
        {
            string sql = $" and F_IsDelete=0";
            DataTable dt = new DataTable();
            #region 筛选条件
            sql += $" and F_Type=" + 2;
            if (stime != null && stime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }
            if (!string.IsNullOrWhiteSpace(salesBase))//销售基地
                sql += $" and F_SalesBase like '%" + salesBase.Trim() + "%'";
            if (!string.IsNullOrWhiteSpace(channel))
            {
                sql += $"  and T_Wo_WorkOrder.F_CustomerID in ( " + GetCustomerID(channel) + ") "; ;
            }
            sql += $"  and T_Wo_WorkOrder.F_QuestionType in ( '" + GetComplaint("'化肥产品质量问题反馈','化肥服务质量问题反馈','化肥农作物长势问题反馈'") + "') "; ;
            sql += $"  and T_Wo_WorkOrder.F_State in ( " + (int)EnumWorkOrderState.finish + "," + (int)EnumWorkOrderState.evaluate + ") "; ;
            #endregion
            int recordCount = 0;
            if (!string.IsNullOrWhiteSpace(sql))
            {

                dt = BLL.PagerBLL.GetListPager(
                    "T_Wo_WorkOrder",
                    "F_ID",
                    "*",
                    sql,
                    "ORDER BY T_Wo_WorkOrder.F_ID desc",
                     limit,
                    page,
                    true,
                    out recordCount);
            }
            List<Model.T_Wo_WorkOrder> modlelist = new BLL.T_Wo_WorkOrder().DataTableToList(dt);
            if (modlelist.Count > 0)
            {
                List<WorkOrderComplaint> input = modelComplaint(modlelist);
                var obj = new
                {
                    state = "success",
                    message = "成功",
                    rows = input,
                    total = recordCount
                };

                return Content(obj.ToJson()); ;

            }
            else
            {

                return Success("暂无工单"); ;
            }

        }
        /// <summary>
        /// 导出市场投诉处理时效明细表
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public ActionResult ExptPrescriptionList(string stime, string endtime, string salesBase, string channel)
        {

            string sql = $"  F_IsDelete=0";
            DataTable dt = new DataTable();
            #region 筛选条件
            sql += $" and F_Type=" + 2;
            if (stime != null && stime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }
            if (!string.IsNullOrWhiteSpace(salesBase))//销售基地
                sql += $" and F_SalesBase like '%" + salesBase.Trim() + "%'";
            if (!string.IsNullOrWhiteSpace(channel))
            {
                sql += $"  and T_Wo_WorkOrder.F_CustomerID in ( " + GetCustomerID(channel) + ") "; ;
            }

            sql += $"  and T_Wo_WorkOrder.F_State in ( " + (int)EnumWorkOrderState.finish + "," + (int)EnumWorkOrderState.evaluate + ") "; ;
            sql += " order by F_CreateOn desc";
            var depts = new BLL.T_Wo_WorkOrder().GetModelList(sql);
            #endregion
            dt.Columns.Add("创建时间");
            dt.Columns.Add("销售基地");
            dt.Columns.Add("大区名称");
            dt.Columns.Add("分公司名称");
            dt.Columns.Add("产品");
            dt.Columns.Add("品牌");
            dt.Columns.Add("问题类别1");
            dt.Columns.Add("问题类别2");
            dt.Columns.Add("问题类别3");
            dt.Columns.Add("问题描述");
            dt.Columns.Add("生产基地");
            dt.Columns.Add("对应责任单位");
            foreach (var it in depts)
            {
                DataRow drNew = dt.NewRow();
                if (!string .IsNullOrEmpty (it.F_CustomerID))
                {
                    if (valcode(it.F_CustomerID, 10))//验证客户编码为10位纯数字
                    {
                        Model.T_Cus_CustomerBaseNew cuModel = cusbll.GetModel(it.F_CustomerID.ToString());//通过客户编号获取客户资料
                        if (cuModel != null)
                        {
                            Model.T_Sys_Department pdModel = departmentBLL.GetModel(cuModel.F_AreaID);//通过公司id获取部门
                            if (pdModel != null)
                            {
                                drNew["大区名称"] = pdModel.F_DeptName;//大区名称
                            }
                            Model.T_Sys_Department pdModel1 = departmentBLL.GetModel(cuModel.F_BranchID);
                            if (pdModel1 != null)
                            {
                                drNew["分公司名称"] = pdModel1.F_DeptName;//大区名称
                            }
                            drNew["品牌"] = cuModel.F_Brands;//品牌
                        }
                    }
                }
                if (!string.IsNullOrEmpty(it.F_QuestionType))
                {
                    Model.T_Wo_QuestionManage Manage = null ;
                    try
                    {
                        int b = int.Parse(it.F_QuestionType.Trim());
                        Manage = quesBLL.GetModel(b);
                      

                    }
                    catch (Exception)
                    {
                        Manage = quesBLL.GetModel(it.F_QuestionType); //问题类别（投诉抽捡和咨询类别不一样）
                    }
                    if (Manage != null)
                    {
                        if (Manage.F_Label == "1")
                        {
                            drNew["问题类别1"] = Manage.F_QuestionName;
                            drNew["问题类别2"] = "";
                            drNew["问题类别3"] = "";
                        }
                        else if (Manage.F_Label == "2")
                        {
                            Model.T_Wo_QuestionManage Manage1 = quesBLL.GetModel(int.Parse(Manage.F_ParentId.ToString()));//获取问题类别
                            drNew["问题类别1"] = Manage1.F_QuestionName;
                            drNew["问题类别2"] = Manage.F_QuestionName;
                            drNew["问题类别3"] = "";
                        }
                        else
                        {
                            Model.T_Wo_QuestionManage Manage1 = quesBLL.GetModel(int.Parse(Manage.F_ParentId.ToString()));//获取问题类别
                            Model.T_Wo_QuestionManage Manage2 = quesBLL.GetModel(int.Parse(Manage1.F_ParentId.ToString()));//获取问题类别
                            drNew["问题类别1"] = Manage2.F_QuestionName;//问题类别1
                            drNew["问题类别2"] = Manage1.F_QuestionName;//问题类别2
                            drNew["问题类别3"] = Manage.F_QuestionName;//问题类别3
                        }
                    }
                }
                drNew["生产基地"] = it.F_SC_ProductBase;//生产基地
                drNew["对应责任单位"] = it.F_ZL_CResponsibleUnits;//责任科室
                drNew["问题描述"] = it.F_Description;//问题描述
                drNew["产品"] = it.F_TS_Category;//产品
                drNew["销售基地"] = it.F_SalesBase;//销售基地:新乡、新疆、九江、东北、其他(其它手动输入别的点选)
                drNew["创建时间"] = it.F_CreateOn.ToString();//时间
                dt.Rows.Add(drNew);
            }
                NPOIHelper npoi = new NPOIHelper();
            if (npoi.ExportToExcel("市场投诉处理时效明细表", dt) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");

            }

        }
        /// <summary>
        /// 市场投诉处理时效明细表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetPrescriptionList(string stime, string endtime, string salesBase, string channel, int page = 1, int limit = 10)
        {
            string sql = $" and F_IsDelete=0";
            DataTable dt = new DataTable();
            #region 筛选条件
            sql += $" and F_Type=" + 2;
            if (stime != null && stime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }
            if (!string.IsNullOrWhiteSpace(salesBase))//销售基地
                sql += $" and F_SalesBase like '%" + salesBase.Trim() + "%'";
            if (!string.IsNullOrWhiteSpace(channel))
            {
                sql += $"  and T_Wo_WorkOrder.F_CustomerID in ( " + GetCustomerID(channel) + ") "; ;
            }
            sql += $"  and T_Wo_WorkOrder.F_State in ( " + (int)EnumWorkOrderState.finish + "," + (int)EnumWorkOrderState.evaluate + ") "; ;
            #endregion
            int recordCount = 0;
            if (!string.IsNullOrWhiteSpace(sql))
            {

                dt = BLL.PagerBLL.GetListPager(
                    "T_Wo_WorkOrder",
                    "F_ID",
                    "*",
                    sql,
                    "ORDER BY T_Wo_WorkOrder.F_ID desc",
                    limit,
                    page,
                    true,
                    out recordCount);
            }
            List<Model.T_Wo_WorkOrder> modlelist = new BLL.T_Wo_WorkOrder().DataTableToList(dt);
            if (modlelist.Count > 0)
            {
                List<Prescription> input = modelPrescription(modlelist);
                var obj = new
                {
                    state = "success",
                    message = "成功",
                    rows = input,
                    total = recordCount
                };

                return Content(obj.ToJson()); ;

            }
            else
            {

                return Success("暂无工单"); ;
            }

        }
        /// <summary>
        /// 导出化工产品市场投诉汇总表
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public ActionResult ExptChemicalList(string stime, string endtime, string salesBase, string channel)
        {

            string sql = $"  F_IsDelete=0";
            DataTable dt = new DataTable();
            #region 筛选条件
            sql += $" and F_Type=" + 2;
            if (stime != null && stime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }
            if (!string.IsNullOrWhiteSpace(salesBase))//销售基地
                sql += $" and F_SalesBase like '%" + salesBase.Trim() + "%'";
            if (!string.IsNullOrWhiteSpace(channel))
            {
                sql += $"  and T_Wo_WorkOrder.F_CustomerID in ( " + GetCustomerID(channel) + ") "; ;
            }
            sql += $"  and T_Wo_WorkOrder.F_QuestionType in ( '" + GetComplaint("'化工产品质量问题反馈','化工服务质量问题反馈'") + "') "; ;
            sql += $"  and T_Wo_WorkOrder.F_State in ( " + (int)EnumWorkOrderState.finish + "," + (int)EnumWorkOrderState.evaluate + ") "; ;
            sql += " order by F_CreateOn desc";
            var depts = new BLL.T_Wo_WorkOrder().GetModelList(sql);
            #endregion
            dt.Columns.Add("化工产品名称");
            dt.Columns.Add("客户单位");
            dt.Columns.Add("生产日期");
            dt.Columns.Add("反馈日期");
            dt.Columns.Add("喷码");
            dt.Columns.Add("问题分类");
            dt.Columns.Add("问题描述");
            dt.Columns.Add("处理方法");
            dt.Columns.Add("责任部门");
            dt.Columns.Add("销售基地");
            dt.Columns.Add("业务员");
            foreach (var it in depts)
            {
                DataRow drNew = dt.NewRow();
                if (!string .IsNullOrEmpty (it.F_CustomerID))
                {
                    if (valcode(it.F_CustomerID, 10))//验证客户编码为10位纯数字
                    {

                        Model.T_Cus_CustomerBaseNew cuModel = cusbll.GetModel(it.F_CustomerID.ToString());//通过客户编号获取客户资料
                        if (cuModel != null)
                        {
                            Model.T_Sys_Department pdModel = departmentBLL.GetModel(cuModel.F_AreaID);//通过公司id获取部门

                            drNew["业务员"] = cuModel.F_Salesman;//业务员
                        }
                    }
                }
                
                drNew["化工产品名称"] = it.F_TS_Category;//产品
                drNew["客户单位"] = it.F_CompanyName;//客户单位
                if (!string.IsNullOrEmpty(it.F_BatchNumber))
                {
                    var prono = it.F_BatchNumber.Substring(0, 6);
                    try
                    {
                        var pronos = DateTime.ParseExact(prono, "yyMMdd", null).ToString("yyyy-MM-dd");
                        //  var protime = Convert.ToDateTime(pronos);

                        drNew["生产日期"] = pronos;
                    }
                    catch
                    {
                        drNew["生产日期"] = "";
                    }
                }
                drNew["反馈日期"] = it.F_CreateOn.ToString(); ;
                drNew["喷码"] = it.F_BatchNumber;
                Model.T_Wo_QuestionManage Manage = null;
                try
                {
                    int b = int.Parse(it.F_QuestionType.Trim());
                    Manage = quesBLL.GetModel(b);
                    if (Manage!=null )
                    {
                        drNew["问题分类"] = Manage.F_QuestionName ;
                    }
                   


                }
                catch (Exception)
                {
                    drNew["问题分类"] = it.F_QuestionType;
                }
              
                drNew["问题描述"] = it.F_Description;
                drNew["处理方法"] = it.F_DealType;
                drNew["责任部门"] = it.F_ZL_CResponsibleUnits;
                drNew["销售基地"] = it.F_SalesBase;
                dt.Rows.Add(drNew);
            }
                NPOIHelper npoi = new NPOIHelper();
            if (npoi.ExportToExcel("化工产品市场投诉汇总表", dt) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");

            }

        }
        /// <summary>
        /// 化工产品市场投诉汇总表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetChemicalList(string stime, string endtime, string salesBase, string channel, int page = 1, int limit = 10)
        {
            string sql = $" and F_IsDelete=0";
            DataTable dt = new DataTable();
            #region 筛选条件
            sql += $" and F_Type=" + 2;
            if (stime != null && stime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }
            if (!string.IsNullOrWhiteSpace(salesBase))//销售基地
                sql += $" and F_SalesBase like '%" + salesBase.Trim() + "%'";
            if (!string.IsNullOrWhiteSpace(channel))
            {
                sql += $"  and T_Wo_WorkOrder.F_CustomerID in ( " + GetCustomerID(channel) + ") "; ;
            }
            sql += $"  and T_Wo_WorkOrder.F_QuestionType in ( '" + GetComplaint("'化工产品质量问题反馈','化工服务质量问题反馈'") + "') "; ;
            sql += $"  and T_Wo_WorkOrder.F_State in ( " + (int)EnumWorkOrderState.finish + "," + (int)EnumWorkOrderState.evaluate + ") "; ;
            #endregion
            int recordCount = 0;
            if (!string.IsNullOrWhiteSpace(sql))
            {

                dt = BLL.PagerBLL.GetListPager(
                    "T_Wo_WorkOrder",
                    "F_ID",
                    "*",
                    sql,
                    "ORDER BY T_Wo_WorkOrder.F_ID desc",
                    limit,
                    page,
                    true,
                    out recordCount);
            }
            List<Model.T_Wo_WorkOrder> modlelist = new BLL.T_Wo_WorkOrder().DataTableToList(dt);
            if (modlelist.Count > 0)
            {
                List<Product> input = modelProduct(modlelist);
                var obj = new
                {
                    state = "success",
                    message = "成功",
                    rows = input,
                    total = recordCount
                };

                return Content(obj.ToJson()); ;

            }
            else
            {

                return Success("暂无工单"); ;
            }

        }
        public class Feedback
        {
            public string name;
            public int  daynumber;
            public int mounthnumber;
            public int Totalnumber;
            public string Proportion;
        }

        public class Complainthandling
        {
            public string name;
            public int number;//数量
            public string ongesttime;//用时最长
            public string minimumtime;//用时最短
            public string averageTime;//平均用时
            public string median;//中位数
            public string Overdue;//超期数量
            public string efficiency;//效率提升率
        }
        public class Channel
        {
            public string channel;//渠道
            public int Telephone;//电话
            public int WeChat;//微信
            public int Immediate;//即时办结
            public int  Transfer;//即时转接
            public int Verysatisfied;//非常满意
            public int Satisfaction;//满意度
            public int Commonly;//一般
            public int Unsatisfactory;//不满意
            public int Total;
        }
        public class BigDate
        {
            public List<Template> salesBase;//反馈类型
            public List<Template> product;//产品
            public List<Template> channel;//渠道
            public List<Template> state;//工单状态
            public List<Template> satisfaction;//满意度
            public List<Template> overdue;//超期
        }
        /// <summary>
        /// 反馈类型
        /// </summary>
        public class Template
        {
            public string name;//名称
            public int number;// 数量
            public int Lastyearnumber;//去年同比
            public int Lastmonthnumber;//上月同比
            public int Lastyear;//去年同比
            public int Lastmonth;//上月同比

        }
      
        public string GetCustomerCode(int area=0,int branch=0,string channel="")
        {
            string str = "";
            string sql = "";
            if (area >0)
            {
                sql += " and F_AreaID =" + area;
            }
            if (branch>0)
            {
                sql += " and F_BranchID =" + branch;
            }
            if (channel!="")
            {
                sql += " and F_Channel= '" + channel + "'";
            }
            if (sql != "")
            {
                str = "select F_CustomerCode from T_Cus_CustomerBaseNew where F_IsDelete=0" + sql;
            }
            return str;
        }
        public string GetCustomerrel(int area = 0, int branch = 0, string channel = "")
        {
            string str = "";
            string sql = "";
            if (area > 0)
            {
                sql += " and F_AreaID =" + area;
            }
            if (branch > 0)
            {
                sql += " and F_BranchID =" + branch;
            }
            if (channel != "")
            {
                sql += " and F_Channel= '" + channel + "'";
            }
            str = "select F_LegalPhone from T_Cus_CustomerBaseNew where F_IsDelete=0"  +sql;
            return str;
        }
        /// <summary>
        /// 大数据一览表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetBigdataList(string stime, string endtime,string salesBase ,string product,string channel, int  area=0,int branch=0, int type = 0)
        {
            #region 
            if (!Refresh())
            {
                return Error("查询失败");
            }
            string sql = $"  F_IsDelete=0";
            DataTable dt = new DataTable();
            #region 筛选条件
            if (string.IsNullOrEmpty(stime))
            {
                stime = DateTime.Now.Date.ToString("yyyy-MM-01");
            }
            if (string.IsNullOrEmpty(endtime))
            {
                endtime = DateTime.Now.Date.ToString("yyyy-MM-dd");
            }
            int  Months = 1;
           if (!string.IsNullOrEmpty(stime)&& !string.IsNullOrEmpty(endtime))
            {
                Months = DateTime.Parse(endtime).Month - DateTime.Parse(stime).Month;
                if (DateTime.Parse(endtime).Day > DateTime.Parse(stime).Day )
                {
                    Months = Months + 1;
                }
            }
            
        
            string smouth= Convert.ToDateTime(DateTime.Parse(stime).AddDays(1 - DateTime.Parse(stime).Day).AddMonths(-Months).ToLongDateString()).ToString("yyyy-MM-dd");
            string endmouth = Convert.ToDateTime(DateTime.Parse(endtime).AddDays(1 - DateTime.Parse(endtime).Day).AddMonths(-Months).ToLongDateString()).ToString("yyyy-MM-dd");
            string syear = Convert.ToDateTime(DateTime.Parse(stime).AddDays(1 - DateTime.Parse(stime).Day).Date.AddYears (-1).ToLongDateString()).ToString("yyyy-MM-dd");
            string endyear= Convert.ToDateTime(DateTime.Parse(endtime).AddDays(1 - DateTime.Parse(endtime).Day).Date.AddMonths(1).AddSeconds(-1).AddYears(-1).ToLongDateString()).ToString("yyyy-MM-dd");
           
           
            #endregion
            string time = $" and datediff(day,F_CreateOn,'{stime}')<=0 and datediff(day,F_CreateOn,'{endtime}')>=0 ";
            string lastmouth = $" and datediff(day,F_CreateOn,'{smouth}')<=0 and datediff(day,F_CreateOn,'{endmouth}')>=0 ";
            string lastyear= $" and datediff(day,F_CreateOn,'{syear}')<=0 and datediff(day,F_CreateOn,'{endyear}')>=0 ";
            BigDate model = new BigDate();
            model.salesBase  = new List<Template>();
            string strType = "";
            if (type >0)
            {
                sql += $"and F_Type = " + type;
            }
            if (!string.IsNullOrEmpty(salesBase))
            {
                strType = $" and F_SalesBase='" + salesBase.Trim() + "'";
            }
            string strChannel = "";
            if (area > 0 || branch > 0 || !string.IsNullOrEmpty(channel))
            {
                strChannel= " and  T_Wo_WorkOrder.F_CusPhone in ( " + GetCustomerrel(area, branch, channel) + ") ";
            }
            string strCategory = "";
            if (!string.IsNullOrEmpty(product))
            {
                strCategory = $" and F_TS_Category='" + product.Trim() + "'";
            }
            #region 反馈类型
            for (int i=0;i <5;i++)
            {
                string sqltype = "";
                Template template = new Template() ;
                switch (i)
                {
                    case 0:
                        template.name = "合计";
                        break;
                    case 1:
                        template.name = "新乡";
                        break;
                    case 2:
                        template.name = "新疆";
                        break;
                    case 3:
                        template.name = "九江";
                        break;
                    case 4:
                        template.name = "东北";
                        break;
                   
                }
                if (i>0 )
                {
                    sqltype = $"and F_SalesBase = '"+ template.name + "'" ;
                }
                else
                {
                    sqltype = $"and F_SalesBase in ('新乡','新疆','九江','东北')";
                }
                model.salesBase .Add(ReturenTemplate(template,sql + sqltype+ strChannel+ strCategory, time , lastmouth, lastyear));
            }
            #endregion
            #region 产品
            var dicv = dicvalueBll.GetList(" F_DictionaryFlag='" + "ZXCPMC" + "' and F_State=1 ").Tables[0];
            model.product = new List<Template>();
            List<Model.T_Sys_DictionaryValue> dicval = new List<Model.T_Sys_DictionaryValue>();
            if (dicv != null)
            {
                dicval = dicvalueBll.DataTableToList(dicv);
            }
            if (dicval != null && dicval.Count > 0) 
            {
                string msf = "";
                foreach (var it in dicval)
                {
                    if (msf == "")
                        msf += " '" + it.F_Name + "'";
                    else
                        msf += " ,'" + it.F_Name + "'";
                }
                Template template = new Template();
                template.name = "合计";
                model.product.Add(ReturenTemplate(template, sql + strChannel + $"and F_TS_Category in (" + msf + ")" + strType, time, lastmouth, lastyear));
                foreach (var it in dicval)
                {
                    Template templates = new Template();
                    string mag = "";
                    mag = $"and F_TS_Category = '" + it.F_Name+"'";
                    templates.name = it.F_Name;
                    model.product.Add(ReturenTemplate(templates, sql + strChannel + strType + mag, time, lastmouth, lastyear));
                }
            }
            #endregion
            #region 渠道类型
            var QDLX = dicvalueBll.GetList(" F_DictionaryFlag='" + "QDLX" + "' and F_State=1 ").Tables[0];
            model.channel = new List<Template>();
            List<Model.T_Sys_DictionaryValue> QDLXl = new List<Model.T_Sys_DictionaryValue>();
            if (QDLX != null)
            {
                QDLXl = dicvalueBll.DataTableToList(QDLX);
            }
            if (QDLXl != null && QDLXl.Count > 0)
            {
                Template templates = new Template();
                templates.name = "合计";
                model.channel.Add(ReturenTemplate(templates, sql + strCategory + strType , time, lastmouth, lastyear));
                foreach (var it in QDLXl)
                {
                    Template template = new Template();
                    string str = "";
                    str = " and  T_Wo_WorkOrder.F_CusPhone in ( " + GetCustomerrel(area, branch, it.F_Name) + ") ";
                    template.name = it.F_Name;
                    model.channel.Add(ReturenTemplate(template,sql+strCategory  + strType + str, time, lastmouth, lastyear));
                }
            }
            #endregion
            #region 工单类型
            model.state = new List<Template>();
            for (int i = 0; i < 5; i++)
            {
                string sqlchannel = "";
           
                Template template = new Template();
                switch (i)
                {
                    case 0:
                        template.name = "合计";
                        break;
                    case 1:
                        template.name = "待处理";
                        sqlchannel = " and F_State in (0, 1, 2, 3, 4, 5, 6, 7, 12, 14, 16)";
                        break;
                    case 2:
                        template.name = "已处理";
                        sqlchannel = " and F_State in (10,11)";
                        break;
                    case 3:
                        template.name = "待回访";
                        sqlchannel = " and F_State in (10)";
                        break;
                    case 4:
                        template.name = "已回访";
                        sqlchannel = " and F_State in (11)";
                        break;
                   
                }
                model.state.Add(ReturenTemplate(template,sql  + sqlchannel+strChannel + strCategory + strType, time, lastmouth, lastyear));
            }
            #endregion
            #region 满意度
            model.satisfaction = new List<Template>();
            for (int i = 0; i <5; i++)
            {
                string sqlsatisfaction = "";
                sqlsatisfaction += " and F_State in (11)";
                sqlsatisfaction += " and F_IsVisit =1";
                Template template = new Template();
                switch (i)
                {
                    case 0:
                        template.name += "合计";
                        break;
                    case 1:
                        template.name = "非常满意";
                        sqlsatisfaction += " and F_VisitResult ='非常满意'";
                        break;
                    case 2:
                        template.name = "满意";
                        sqlsatisfaction += " and F_VisitResult ='满意'";
                        break;
                    case 3:
                        template.name = "一般";
                        sqlsatisfaction += " and F_VisitResult ='一般'";
                        break;
                    case 4:
                        template.name = "不满意";
                        sqlsatisfaction += " and F_VisitResult ='不满意'";
                        break;
                  
                }
                model.satisfaction .Add(ReturenTemplate(template,sql + sqlsatisfaction + strChannel + strCategory + strType, time, lastmouth, lastyear));
                #endregion
              
                 
            }
            #endregion
            #region 超期工单
            model.overdue = new List<Template>();
            string sqloverdue = "";
            sqloverdue += strChannel + strCategory + strType;
            var modlelist = workOrder.GetModelList(sql + sqloverdue + "and F_IsOver=1" + time);
            Template templatee = new Template();
            templatee.name = "合计";
            templatee.number =0;
            model.overdue.Add(templatee);
            if (modlelist.Count > 0)
            {
                foreach (var it in modlelist)
                {
                    var itemList = itembll.GetModelList("  F_WoID=" + it.F_ID + "and F_WoState=1" + " order by F_ID desc");
                    if (itemList.Count > 0)
                    {
                        if (itemList[0].F_NextDept > 0)
                        {
                            var deptmodel = departmentBLL.GetModel(int.Parse(itemList[0].F_NextDept.ToString()));
                            if (model.overdue.Count > 0)
                            {
                                 
                                  int overdue = 0;
                                    foreach (var im in model.overdue)
                                    {
                                        if (im.name == deptmodel.F_DeptName)
                                        {
                                            im.number += 1;
                                            overdue = 1;
                                        }
                                      if (im .name =="合计")
                                      {
                                        im.number += 1;
                                         }
                                       }
                                    if (overdue == 0)
                                    {
                                        Template templates = new Template();
                                        templates.name = deptmodel.F_DeptName;
                                        templates.number = 1;
                                        model.overdue.Add(templates);
                                   }
                            }
                            else
                            {
                                Template templates = new Template();
                                templates.name = deptmodel.F_DeptName;
                                templates.number = 1;
                                model.overdue.Add(templates);
                            }


                        }
                    }
                }
            }
            if (model.overdue.Count > 0)
            {
                foreach (var it in model.overdue)
                {
                    if (it .name =="合计")
                    {
                        var lastmouthmodlel = workOrder.GetModelList(sql + sqloverdue + "and F_IsOver=1" + lastmouth).Count ;
                        var lastyearmodlel = workOrder.GetModelList(sql + sqloverdue + "and F_IsOver=1" + lastyear).Count ;
                        it.Lastmonthnumber = lastmouthmodlel;
                        it.Lastyearnumber = lastyearmodlel;
                        it.Lastmonth = it.number - lastmouthmodlel;
                        it.Lastyear = it.number - lastyearmodlel;
                    }
                    else
                    {
                        var deptmodel = departmentBLL.GetModel(it.name);
                        var lastmouthmodlel = workOrder.GetModelList(sql + sqloverdue + "and F_IsOver=1" + lastmouth);
                        int lastmouthlist = 0;
                        int lastyearlist = 0;
                        if (lastmouthmodlel.Count > 0)
                        {
                            foreach (var im in lastmouthmodlel)
                            {
                                var itemList = itembll.GetModelList("  F_WoID=" + im.F_ID + "and F_WoState=1" + "and F_NextDept=" + deptmodel.F_DeptId + " order by F_ID desc");
                                if (itemList.Count > 0)
                                {
                                    lastmouthlist++;
                                }
                            }
                        }
                        var lastyearmodlel = workOrder.GetModelList(sql + sqloverdue + "and F_IsOver=1" + lastyear);
                        if (lastyearmodlel.Count > 0)
                        {
                            foreach (var im in lastyearmodlel)
                            {
                                var itemList = itembll.GetModelList("  F_WoID=" + im.F_ID + "and F_WoState=1" + "and F_NextDept=" + deptmodel.F_DeptId + " order by F_ID desc");
                                if (itemList.Count > 0)
                                {
                                    lastyearlist++;
                                }
                            }
                        }
                        it.Lastmonthnumber = lastmouthlist;
                        it.Lastyearnumber = lastyearlist;
                        it.Lastmonth = it.number - lastmouthlist;
                        it.Lastyear = it.number - lastyearlist;
                    }
                }
                 
            }
            if (model.overdue.Count > 0)
            {
                for (int j = 0; j < model.overdue.Count - 1; j++)
                {
                    for (int z = 0; z < model.overdue.Count - 1 - j; z++)
                    {
                        if (model.overdue[z].number < model.overdue[z + 1].number)
                        {
                            var temp = model.overdue[z + 1];
                            model.overdue[z + 1] = model.overdue[z];
                            model.overdue[z] = temp;
                        }
                    }
                }
            }
            #endregion
            var obj = new
            {
                state = "success",
                message = "成功",
                rows = model,
                total = 6
            };

            return Content(obj.ToJson()); ;
        }
      
   
        private bool Refresh()
        {
            int type = 0;
            var modlelist = workOrder.GetModelList(" F_IsDelete=0" + "and F_IsOver=0" + " and F_State in(" + (int)EnumWorkOrderState.dealing + "," + (int)EnumWorkOrderState.receive + "," +
                (int)EnumWorkOrderState.assign + "," + (int)EnumWorkOrderState.reback + "," + (int)EnumWorkOrderState.abreback + ")");
            if (modlelist.Count > 0)
            {
                foreach (var it in modlelist)
                {

                    if (TimeforOver(it.F_ID))
                    {
                        type = 0;
                    }
                    else
                    {
                        type = 1;
                    }
                    if (type != it.F_IsOver)
                    {
                        it.F_IsOver = type;
                        bool n = workOrder.Update(it);
                        if (!n)
                        {
                            return false;
                        }
                    }
                }

            }
            return true;
        }
        /// <summary>
        /// 是否超时
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        private bool TimeforOver(int id)
        {
            var itemlasts = itembll.GetModelList("  F_ItemType=" + (int)EnumItemType.deal + " and  F_WoID=" + id + " ");
            string time1 = "", time2 = ""; int x = 0, y = 0;
            float time = 0;
            if (itemlasts.Count > 0)
            {
                for (int i = 0; i < itemlasts.Count; i++)
                {
                    if (itemlasts[i].F_WoState >= 1)
                    {
                        if (itemlasts[i].F_WoState == 1)
                        {
                            x = i;
                            time1 = itemlasts[i].F_CreateTime.ToString();
                            time = float.Parse(itemlasts[i].F_LimitTime);
                        }
                        if (itemlasts[i].F_WoState == 10)
                        {
                            y = i;
                            time2 = itemlasts[i].F_CreateTime.ToString();
                        }
                    }
                }
                if (time2 != "")
                {
                    if (time1 != "")
                    {
                        if (x > y)
                        {
                            int a = (DateTime.Parse(time2) - DateTime.Parse(time1)).Hours;
                            if (a > time)
                            {
                                return false;
                            }
                        }
                        else
                        {
                            int a = (DateTime.Now - DateTime.Parse(time1)).Hours;
                            if (a > time)
                            {
                                return false;
                            }
                        }
                    }
                    else
                    {
                        return true;
                    }
                }
                else if (time1 != "")
                {
                    int a = (DateTime.Now - DateTime.Parse(time1)).Hours;
                    if (a > time)
                    {
                        return false;
                    }
                }
                else
                {
                    return true;
                }
            }
            else
            {
                return true;
            }
            return true;
        }
        public Template ReturenTemplate(Template template,string sql,string time,string lastmouth,string lastyear )
        {
            template.number = new BLL.T_Wo_WorkOrder().GetModelList(sql + time).Count;//
            int mounthnumber = 0, yearnumber = 0;
            mounthnumber = new BLL.T_Wo_WorkOrder().GetModelList(sql + lastmouth).Count;//
            yearnumber = new BLL.T_Wo_WorkOrder().GetModelList(sql + lastyear).Count;//
            template.Lastmonthnumber = mounthnumber;
            template.Lastyearnumber = yearnumber;
            template.Lastmonth = template.number - mounthnumber;
            template.Lastyear = template.number - yearnumber;
            return template;
        }


        /// <summary>
        /// 市场反馈一览表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetFeedbacklList()
        {
            string sql = $"  F_IsDelete=0";
            DataTable dt = new DataTable();
            #region 筛选条件
            List<Feedback> modelList = new List<Feedback>(5);
            string smonthtime = DateTime.Now.Date.ToString("yyyy-MM-01 00:00:00");
            string sdaytime = DateTime.Now.Date.ToString("yyyy-MM-dd 00:00:00");
            string endtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            for (int i = 1; i <= 5; i++)
            {
                Feedback model = new Feedback();
                    switch (i)
                    {
                        case 1:
                            model.name = "咨询";
                            break;
                        case 2:
                            model.name = "投诉";
                            break;
                        case 3:
                            model.name = "抽检";
                            break;
                        case 4:
                            model.name = "建议";
                            break;
                        case 5:
                            model.name = "合计";
                            break;
                    }
               float a= workOrder.GetModelList(sql ).Count;
                if (i <5)
                {
                   
                  model .daynumber = workOrder.GetModelList(sql + $" and datediff(day,F_CreateOn,'{sdaytime}')<=0 and datediff(day,F_CreateOn,'{endtime}')>=0 "  + "and F_Type=" + i ).Count;
                    model.mounthnumber  = workOrder.GetModelList(sql + $" and datediff(day,F_CreateOn,'{smonthtime}')<=0 and datediff(day,F_CreateOn,'{endtime}')>=0 " + "and F_Type=" + i).Count;
                    model.Totalnumber = workOrder.GetModelList(sql  + "and F_Type=" + i).Count;
                    model.Proportion = string.Format("{0:f2}%", model.Totalnumber / a*100);
                }
                else
                {
                    model.daynumber = workOrder.GetModelList(sql + $" and datediff(day,F_CreateOn,'{sdaytime}')<=0 and datediff(day,F_CreateOn,'{endtime}')>=0 " ).Count;
                    model.mounthnumber = workOrder.GetModelList(sql + $" and datediff(day,F_CreateOn,'{smonthtime}')<=0 and datediff(day,F_CreateOn,'{endtime}')>=0 ").Count;
                    model.Totalnumber = workOrder.GetModelList(sql).Count;
                    model.Proportion = string.Format("{0:f2}%", model.Totalnumber / a * 100);
                }
                
              
                modelList.Add(model);
            }
              #endregion
    

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

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


        /// <summary>
        /// 渠道数量对比表
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public ActionResult GetChanelList(string stime, string endtime)
        {
            string sql = $" and F_IsDelete=0";
            DataTable dt = new DataTable();
            #region 筛选条件
            if (stime != null && stime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }
            #endregion
            List<string> channelname = new List<string>();
            channelname.Add("总经销");
            channelname.Add("二级商");
            channelname.Add("种田大户");
            channelname.Add("公司员工");
            channelname.Add("农户");
            channelname.Add("化工客户");
            channelname.Add("其他");
            int Distribution = 0, Quotient=0, Bigfarmer = 0, Companystaf = 0, Peasant = 0, Customers = 0, Other = 0;
            for (int i=0;i < channelname.Count;i++)
            {
                string msg = "'" +channelname[i] + "'";
                var modellist = woBLL.GetModelList(" F_CustomerID in (" + GetCustomer(msg) + ")" + sql + "and F_Type in (2,3)");
                var modellis = woBLL.GetModelList(" F_CusPhone in (" + GetCustomerrel(msg) + ")" + sql + "and F_Type in (1,4)");
                switch (i )
                {
                    case 0:
                        Distribution = modellist.Count + modellis.Count;//总经销
                        break;
                    case 1:
                        Quotient = modellist.Count + modellis.Count;//二级商
                        break;
                    case 2:
                        Bigfarmer = modellist.Count + modellis.Count;//种田大户
                        break;
                    case 3:
                        Companystaf = modellist.Count + modellis.Count;//公司员工
                        break;
                    case 4:
                        Peasant = modellist.Count + modellis.Count;//农户
                        break;
                    case 5:
                        Customers = modellist.Count + modellis.Count;//化工客户
                        break;
                    case 6:
                        Other = modellist.Count + modellis.Count;//其他
                        break;
                }
                     
            }

            var obj = new
            {
                state = "success",
                message = "成功",
                rows=new
                {
                    Distribution,
                    Quotient,
                    Bigfarmer,
                    Companystaf,
                    Peasant,
                    Customers,
                    Other,
                }
                ,
                total =6
            };

            return Content(obj.ToJson()); ;


        }
        BLL.T_Sys_DictionaryValue dicvalueBll = new BLL.T_Sys_DictionaryValue();
        public class Brand
        {
            public string name;
            public int number;
        }
    
        /// <summary>
        /// 产品品牌对照表
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public ActionResult GetbrandList(string stime, string endtime)
        {
            string sql = $" and F_IsDelete=0";
            DataTable dt = new DataTable();
            #region 筛选条件
            if (stime != null && stime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }
            #endregion
            var dicv = dicvalueBll.GetList(" F_DictionaryFlag='" + "ZXCPMC" + "' and F_State=1 ").Tables[0];
            List<Brand> list = new List<Brand>();
            List<Model.T_Sys_DictionaryValue> dicval = new List<Model.T_Sys_DictionaryValue>();
            if (dicv !=null )
            {
                dicval = dicvalueBll.DataTableToList(dicv);
            }
            if (dicval !=null&& dicval.Count >0)
            {
                foreach (var it in dicval)
                {
                    var modellist = woBLL.GetModelList(" F_TS_Category in ('" + it.F_Name  + "')" + sql );
                    Brand brand = new Brand();
                    brand.name = it.F_Name;
                    brand.number  = modellist.Count;
                    list.Add(brand);

                }
            }
            var obj = new
            {
                state = "success",
                message = "成功",
                rows = list
                 ,
                total = list.Count ,
           };
            return Content(obj.ToJson()); ;
        }
        public class QuestionType
        {
            public string F_QuestionType;
            public int number;
        }
        /// <summary>
        ///月份投诉处理周期表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetComplainthandlList(string product,string salesBase,string stime ="", string endtime="", string questionName = "" )
        {
            string sql = $"  F_IsDelete=0";
            if (!string.IsNullOrWhiteSpace(salesBase))//销售基地
                sql += $" and F_SalesBase like '%" + salesBase.Trim() + "%'";
            if (!string.IsNullOrWhiteSpace(product))//产品
                sql += $" and F_TS_Category='" + product.Trim() + "'";
            string Queststr = "";
            if (questionName!="")
            {
                Queststr = " select * from T_Wo_QuestionManage where F_IsDelete=0 and F_Label in(3) and F_QuestionName='" + questionName+"'";
            }
            else
            {

                Queststr = " select * from T_Wo_QuestionManage where F_IsDelete=0 AND F_Label in(3)";
            }
            DataTable QuestionManage = DbHelperSQL.Query(Queststr).Tables[0];
            List<Model.T_Wo_QuestionManage> categorylist = quesBLL.DataTableToList(QuestionManage);
            List<Complainthandling> modeNamelList = new List<Complainthandling>(categorylist.Count);
            string time = "";
            if (stime == "")
            {
                stime = DateTime.Now.AddDays(1 - DateTime.Now.Day).Date.ToString("yyyy-MM-dd");
            }
            if (endtime == "")
            {
                endtime = DateTime.Now.ToString("yyyy-MM-dd");
            }
            List<Complainthandling> modelList = new List<Complainthandling>(categorylist.Count);
            for (int i = 0; i < categorylist.Count; i++)
            {
                Complainthandling model = new Complainthandling();
                model.name = categorylist[i].F_QuestionName;
                string strnumber = " select  F_QuestionType,count(F_QuestionType) number from T_Wo_WorkOrder where" + sql+"and F_QuestionType= '" + categorylist[i].F_Id  +"'" +
                    "group by F_QuestionType order by count(F_QuestionType) desc";
                DataTable QuestionType = DbHelperSQL.Query(strnumber).Tables[0];
                List<QuestionType> QuestionTypelists = DataTableToList(QuestionType);
                if (QuestionTypelists.Count >0)
                {
                    model.number = QuestionTypelists[0].number;
                }
                else
                {
                    model.number = 0;
                }
                string msg = "select F_QuestionType,datediff(HH ,F_CreateOn,F_DealTime) number from T_Wo_WorkOrder where " + sql + time + "and F_QuestionType  =" + "'" + categorylist[i].F_Id  + "'" + "ORDER BY number ";
                DataTable dtxy = DbHelperSQL.Query(msg).Tables[0];
                List<QuestionType> QuestionTypes = DataTableToList(dtxy);
                float average = 0;
                int med = QuestionTypes.Count / 2;
                if (QuestionTypes.Count>0)
                {
                    for (int j = 0; j < QuestionTypes.Count; j++)
                    {
                        if(QuestionTypes.Count==1)
                        {
                            model.minimumtime = QuestionTypes[j].number.ToString();
                            model.ongesttime = QuestionTypes[j].number.ToString();
                            model.median = QuestionTypes[j].number.ToString();
                        }
                        else
                        {
                            if (j == 0)
                            {
                                model.minimumtime = QuestionTypes[j].number.ToString();
                            }
                            else if (j == QuestionTypes.Count - 1)
                            {
                                model.ongesttime = QuestionTypes[j].number.ToString();
                            }
                            else if (j == med)
                            {
                                model.median = QuestionTypes[j].number.ToString();
                            }
                        }
                        average += QuestionTypes[j].number;
                    }
                }
                else
                {
                    model.minimumtime = "0";
                    model.ongesttime = "0";
                    model.median = "0";
                }
               
                float ave = average / QuestionTypes.Count;
                if (float.IsNaN(ave))
                {
                    ave = 0;
                }
                model.averageTime = string.Format("{0:f2}", ave);
                string Over = "select  F_QuestionType,count(F_IsOver) number from T_Wo_WorkOrder where  " + sql +
                    "and F_QuestionType  ='" + categorylist[i].F_Id  + "'" + time + "group by F_QuestionType order by count(F_IsOver) desc";
                DataTable Overdtxy = DbHelperSQL.Query(Over).Tables[0];
                List<QuestionType> Overdue = DataTableToList(Overdtxy);
                if (Overdue.Count >0)
                {
                    model.Overdue = Overdue[0].number.ToString();
                }
                else
                {
                    model.Overdue ="0";
                }
                int year = DateTime.Now.Year;//当前年  
                int mouth = DateTime.Now.Month;//当前月  
                if (stime != "")
                {
                    year = DateTime.Parse(stime).Year;
                    mouth = DateTime.Parse(stime).Month;
                }
                int beforeYear = 0;
                int beforeMouth = 0;
                if (mouth <= 1)//如果当前月是一月，那么年份就要减1  
                {
                    beforeYear = year - 1;
                    beforeMouth = 12;//上个月  
                }
                else
                {
                    beforeYear = year;
                    beforeMouth = mouth - 1;//上个月  
                }
                string beforeMouthOneDay = beforeYear + "年" + beforeMouth + "月" + 1 + "日";//上个月第一天  
                string beforeMouthLastDay = beforeYear + "年" + beforeMouth + "月" + DateTime.DaysInMonth(year, beforeMouth) + "日";//上个月最后一天
                string time1 = DateTime.Parse(beforeMouthOneDay).ToString("yyyy-MM-dd");
                string time2 = DateTime.Parse(beforeMouthLastDay).ToString("yyyy-MM-dd");
                string times = "";
                times += " and CONVERT(varchar , F_CreateOn, 120)>=CONVERT(varchar , '" + time1.Trim() + " 00:00:01', 120) ";
                times += " and CONVERT(varchar , F_CreateOn, 120)<=CONVERT(varchar , '" + time2.Trim() + " 23:59:59', 120) ";
                string msgs = "select F_QuestionType,datediff(HH ,F_CreateOn,F_DealTime) number from T_Wo_WorkOrder where " + sql + times + "and F_QuestionType  =" + "'" + categorylist[i].F_Id  + "'" + "ORDER BY number ";
                DataTable dtxys = DbHelperSQL.Query(msgs).Tables[0];
                List<QuestionType> Questionmonth = DataTableToList(dtxys);
                float efficien = 0;
                for (int j = 0; j < Questionmonth.Count; j++)
                {
                    efficien += Questionmonth[j].number;
                }
                float eff = efficien / Questionmonth.Count;
                float Percentile = (ave - eff) / eff * 100;
              
                if (float .IsNaN (Percentile) )
                {
                    Percentile = 0;
                }
                model.efficiency = string.Format("-{0:f2}% ", Percentile);
                modelList.Add(model);
            }
            modelList.Sort((a, b) => b .number .CompareTo(a .number ));
            var obj = new
            {
                state = "success",
                message = "成功",
                rows = modelList,
                total = modelList.Count 
            };

            return Content(obj.ToJson()); ;
        }
        public class People
        {
            public string name { get; set; }
            public int age { get; set; }
            public string sex { get; set; }
        }
        /// <summary>
        /// 渠道来源数据分析表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetChannel(string channel="")
        {
            string sql = $" and F_IsDelete=0";
            sql += "and F_State=" + (int)EnumWorkOrderState.evaluate;
            DataTable dt = new DataTable();
            #region 筛选条件
           
            List<Channel> modelList = new List<Channel> ();
            List<string> channelname = new List<string>();
            if (channel != "")
            {
                channelname.Add(channel);
            }
            else
            {
                channelname.Add("总经销");
                channelname.Add("二级商");
                channelname.Add("种田大户");
                channelname.Add("公司员工");
                channelname.Add("其他公众");
            }
            for (int i = 0; i <channelname.Count ; i++)
            {
                Channel model = new Channel();
                model.channel = channelname[i ];
                string changel = "";
                if (channelname[i ]!= "其他公众")
                {
                    changel ="'"+ model.channel.Trim()+"'";
                }
                else
                {
                    changel = "'农户','化工客户','其他'";
                }
                string msg = "";
               
                var modellist= woBLL.GetModelList(" F_CustomerID in (" + GetCustomer(changel) + ")" + sql+ "and F_Type in (2,3)");
                var modellis = woBLL.GetModelList(" F_CusPhone in (" + GetCustomerrel(changel) + ")" + sql + "and F_Type in (1,4)");
                int phone = 0, Chat = 0, diate=0, Trans=0, Satis=0, Unsatis= 0,Verysatisfied=0, Commonly=0;
                foreach (var it in modellist)
                {
                    if (it .F_Source =="1")
                        phone ++;
                    if (it.F_Source == "2" || it.F_Source == "3")
                        Chat++;
                    if (it.F_DealType == "当即办理")
                        diate++;
                    if (it.F_DealType == "电话转接")
                        Trans++;
                    if ( it.F_VisitResult == "非常满意")
                        Verysatisfied++;
                    if (it.F_VisitResult == "满意")
                        Satis++;
                    if (it.F_VisitResult == "一般")
                        Commonly++;
                    if ( it.F_VisitResult == "不满意")
                        Unsatis++;
                }
                foreach (var it in modellis)
                {
                    if (it.F_Source == "1")
                        phone++;
                    if (it.F_Source == "2" || it.F_Source == "3")
                        Chat++;
                    if (it.F_DealType == "当即办理")
                        diate++;
                    if (it.F_DealType == "电话转接")
                        Trans++;
                    if (it.F_VisitResult == "非常满意")
                        Verysatisfied++;
                    if (it.F_VisitResult == "满意")
                        Satis++;
                    if (it.F_VisitResult == "一般")
                        Commonly++;
                    if (it.F_VisitResult == "不满意")
                        Unsatis++;
                }
                model.Telephone = phone;
                model.WeChat = Chat;
                model.Immediate = diate;
                 model.Transfer = Trans;
                model.Verysatisfied = Verysatisfied;
                model.Commonly = Commonly;
                model.Total  = modellist.Count;
               model.Satisfaction = Satis;
                 model.Unsatisfactory = Unsatis;
                modelList.Add(model);
            }
            #endregion


            var obj = new
            {
                state = "success",
                message = "成功",
                rows = modelList,
                total = modelList.Count 
            };

            return Content(obj.ToJson()); ;
        }
        /// <summary>
        /// 大数据区域表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetRegionList(string stime, string endtime, string salesBase, string channel, int type = 0, int page = 1, int limit = 10)
        {
            string sql = $" and F_IsDelete=0";
            DataTable dt = new DataTable();
            #region 筛选条件
            if (type > 0 && type < 5)//工单类型
                sql += $" and F_Type=" + type;
            if (stime != null && stime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sql += " and CONVERT(varchar , F_CreateOn, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }
            if (!string.IsNullOrWhiteSpace(salesBase))//销售基地
                sql += $" and F_SalesBase like '%" + salesBase.Trim() + "%'";
            if (!string.IsNullOrWhiteSpace(channel))
            {
                sql += $"  and T_Wo_WorkOrder.F_CustomerID in ( " + GetCustomerID(channel) + ") "; ;
            }
            sql += $"  and T_Wo_WorkOrder.F_State in ( " + (int)EnumWorkOrderState.finish + "," + (int)EnumWorkOrderState.evaluate + ") "; ;
            #endregion
            int recordCount = 0;
            if (!string.IsNullOrWhiteSpace(sql))
            {

                dt = BLL.PagerBLL.GetListPager(
                    "T_Wo_WorkOrder",
                    "F_ID",
                    "*",
                    sql,
                    "ORDER BY T_Wo_WorkOrder.F_ID desc",
                    limit,
                    page,
                    true,
                    out recordCount);
            }
            List<Model.T_Wo_WorkOrder> modlelist = new BLL.T_Wo_WorkOrder().DataTableToList(dt);
            if (modlelist.Count > 0)
            {
                List<Questions> input = modeltooip(modlelist);
                var obj = new
                {
                    state = "success",
                    message = "成功",
                    rows = input,
                    total = recordCount
                };

                return Content(obj.ToJson()); ;

            }
            else
            {

                return Success("暂无工单"); ;
            }

        }
        public string GetCustomer(string msg)
        {
            string str = "";
            str = "select F_CustomerCode from T_Cus_CustomerBaseNew where F_IsDelete=0" + " and F_Channel in(" + msg+")";
            return str;
        }
        public string GetCustomerrel(string msg)
        {
            string str = "";
            str = "select F_LegalPhone from T_Cus_CustomerBaseNew where F_IsDelete=0" + " and F_Channel in(" + msg + ")";
            return str;
        }
        private string ChannelType(string msg)
        {
            string mag = "";
            DataTable dtxy = DbHelperSQL.Query(msg).Tables[0];
            mag = dtxy.Rows.Count.ToString();
            return mag;
        }

        /// <summary>
        /// 验证物料编码为10位纯数字
        /// </summary>
        private bool valcode(string code, int number)
        {
            var res = false;
            //11位数字
            if (code.Length == number && Regex.IsMatch(code, @"^\d{" + number + "}$"))
                res = true;
            return res;
        }
        /// <summary>
        /// 根据model返回Prescription对象
        /// </summary>
        private List<Prescription> modelPrescription(List<Model.T_Wo_WorkOrder> model1)
        {
            int userId = CurrentUser.UserData.F_UserId;

            if (model1 != null && model1.Count > 0)
            {
                List<Prescription> Input = new List<Prescription>(model1.Count);
                for (int i = 0; i < model1.Count; i++)
                {
                    Prescription model = new Prescription();
                    if (!string .IsNullOrEmpty (model1[i].F_CustomerID))
                    {
                        if (valcode(model1[i].F_CustomerID, 10))//验证客户编码为10位纯数字
                        {
                            Model.T_Cus_CustomerBaseNew cuModel = cusbll.GetModel(model1[i].F_CustomerID);//通过客户编号获取客户资料
                            if (cuModel != null)
                            {
                                Model.T_Sys_Department pdModel = departmentBLL.GetModel(cuModel.F_AreaID);//通过公司id获取部门
                                if (pdModel != null)
                                {
                                    model.AreaName = pdModel.F_DeptName;//大区名称
                                }
                                Model.T_Sys_Department pdModel1 = departmentBLL.GetModel(cuModel.F_BranchID);
                                if (pdModel1 != null)
                                {
                                    model.BranchName = pdModel1.F_DeptName;//大区名称
                                }
                                model.Brands = cuModel.F_Brands;//品牌
                            }
                        }
                    }
                   
                    if (!string.IsNullOrEmpty(model1[i].F_QuestionType))
                    {
                        Model.T_Wo_QuestionManage Manage = null;
                        try
                        {
                            int b = int.Parse(model1[i].F_QuestionType.Trim());
                            Manage = quesBLL.GetModel(b);


                        }
                        catch (Exception)
                        {
                            Manage = quesBLL.GetModel(model1[i].F_QuestionType); //问题类别（投诉抽捡和咨询类别不一样）
                        }
                        if (Manage != null)
                        {
                            if (Manage.F_Label == "1")
                            {
                                model.QuestionTypeone = Manage.F_QuestionName;
                                model.QuestionTypetwo = "";
                                model.QuestionTypethree = "";
                            }
                            else if (Manage.F_Label == "2")
                            {
                                Model.T_Wo_QuestionManage Manage1 = quesBLL.GetModel(int.Parse(Manage.F_ParentId.ToString()));//获取问题类别
                                model.QuestionTypeone = Manage1.F_QuestionName;
                                model.QuestionTypetwo = Manage.F_QuestionName;
                                model.QuestionTypethree = "";
                            }
                            else
                            {
                                Model.T_Wo_QuestionManage Manage1 = quesBLL.GetModel(int.Parse(Manage.F_ParentId.ToString()));//获取问题类别
                                Model.T_Wo_QuestionManage Manage2 = quesBLL.GetModel(int.Parse(Manage1.F_ParentId.ToString()));//获取问题类别
                                model.QuestionTypeone = Manage2.F_QuestionName;//问题类别1
                                model.QuestionTypetwo = Manage1.F_QuestionName;//问题类别2
                                model.QuestionTypethree = Manage.F_QuestionName;//问题类别3
                            }
                        }
                    }
                    model.ProductBase = model1[i].F_SC_ProductBase;//生产基地
                    model.CResponsibleUnits = model1[i].F_ZL_CResponsibleUnits;//责任科室
                    model.Description = model1[i].F_Description;//问题描述
                    model.Category = model1[i].F_TS_Category;//产品

                    model.SalesBase = model1[i].F_SalesBase;//销售基地:新乡、新疆、九江、东北、其他(其它手动输入别的点选)
                    model.CreatOn = model1[i].F_CreateOn.ToString();//时间
                 
                    Input.Add(model);
                }
                return Input;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 根据model返回Questions对象
        /// </summary>
        private List<Questions> modeltooip(List<Model.T_Wo_WorkOrder> model1)
        {
            int userId = CurrentUser.UserData.F_UserId;

            if (model1 != null && model1.Count > 0)
            {
                List<Questions> Input = new List<Questions>(model1.Count);
                for (int i = 0; i < model1.Count; i++)
                {
                    Questions model = new Questions();
                    if (!string .IsNullOrEmpty (model1[i].F_CustomerID))
                    {
                        if (valcode(model1[i].F_CustomerID, 10))//验证客户编码为10位纯数字
                        {
                            Model.T_Cus_CustomerBaseNew cuModel = cusbll.GetModel(model1[i].F_CustomerID);//通过客户编号获取客户资料
                            if (cuModel != null)
                            {
                                Model.T_Sys_Department pdModel = departmentBLL.GetModel(cuModel.F_AreaID);//通过公司id获取部门
                                if (pdModel != null)
                                {
                                    model.AreaName = pdModel.F_DeptName;
                                }
                                Model.T_Sys_Department pdModel1 = departmentBLL.GetModel(cuModel.F_BranchID);
                                if (pdModel1 != null)
                                {
                                    model.BranchName = pdModel1.F_DeptName;
                                }
                                model.Salesman = cuModel.F_Salesman;
                                model.Brands = cuModel.F_Brands;
                                model.Channel = cuModel.F_Channel;

                            }
                        }
                    }
                    model .Category = model1[i].F_TS_Category ;
                    if (model1[i].F_Type == "1")
                    {
                        model.Type  = "咨询";//工单类型:咨询及需求、投诉、抽检、其他(其它手动输入别的点选)
                    }
                    else if (model1[i].F_Type == "2")
                    {
                        model.Type = "投诉";//工单类型:咨询及需求、投诉、抽检、其他(其它手动输入别的点选)
                    }
                    else if (model1[i].F_Type == "3")
                    {
                        model.Type = "抽检";//工单类型:咨询及需求、投诉、抽检、其他(其它手动输入别的点选)
                    }
                    else
                    {
                        model.Type = "建议及其他";//工单类型:咨询及需求、投诉、抽检、其他(其它手动输入别的点选)
                    }
                    model.SalesBase = model1[i].F_SalesBase;//销售基地:新乡、新疆、九江、东北、其他(其它手动输入别的点选)
                    model.CreatOn = model1[i].F_CreateOn .ToString ();
                    if (TimeToOver(model1[i].F_ID, model1[i].F_limit) <0)
                    model.Overtime = Math .Abs(TimeToOver(model1[i].F_ID, model1[i].F_limit)).ToString ();
                    else
                        model.Overtime = "0";
                    if (model1[i].F_State == 11 && model1[i].F_IsVisit == 1)
                        model.Isvisit = "是";
                    else
                        model.Isvisit = "否";
                    if (model1[i].F_State == 11)
                        model.IsClose  = "是";
                    else
                        model.IsClose = "否";
                    Input.Add(model);
                }
                return Input;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 根据model返回WorkOrderComplaint对象
        /// </summary>
        private List<WorkOrderComplaint> modelComplaint(List<Model.T_Wo_WorkOrder> model1)
        {
            if (model1 != null && model1.Count > 0)
            {
                List<WorkOrderComplaint> Input = new List<WorkOrderComplaint>(model1.Count);
                for (int i = 0; i < model1.Count; i++)
                {
                    WorkOrderComplaint model = new WorkOrderComplaint();
                    if (!string .IsNullOrEmpty (model1[i].F_CustomerID))
                    {
                        if (valcode(model1[i].F_CustomerID, 10))//验证客户编码为10位纯数字
                        {
                            model.F_CustomerID = model1[i].F_CustomerID;//客户编码
                            Model.T_Cus_CustomerBaseNew cuModel = cusbll.GetModel(model1[i].F_CustomerID);//通过客户编号获取客户资料
                            if (cuModel != null)
                            {
                                Model.T_Sys_Department pdModel = departmentBLL.GetModel(cuModel.F_AreaID);//通过公司id获取部门
                                if (pdModel != null)
                                {
                                    model.F_AreaName = pdModel.F_DeptName;//大区名称
                                }
                                Model.T_Sys_Department pdModel1 = departmentBLL.GetModel(cuModel.F_BranchID);
                                if (pdModel1 != null)
                                {
                                    model.F_BranchName = pdModel1.F_DeptName;//分公司名称
                                }
                                model.F_Salesman = cuModel.F_Salesman;//业务员
                            }
                        }
                    }
                    if (!string .IsNullOrEmpty (model1[i].F_MaterialID))
                    {
                        if (valcode(model1[i].F_MaterialID, 11))
                        {
                            model.F_MaterialID =model1[i].F_MaterialID;//验证物料编码为11位纯数字
                            Model.T_Wo_MaterialManage maModel = mmBLL.GetModel(model1[i].F_MaterialID);//通过物料编码获取物料信息
                            if (maModel != null)
                            {
                                model.F_MaterialName = maModel.F_MaterialName;//物料名称
                                model.F_Model = maModel.F_Model;//型号
                                model.F_Specs = maModel.F_Specs;//规格
                                model.F_Level1 = maModel.F_Level1;//一级分类
                                model.F_Level2 = maModel.F_Level2;//二级分类
                                model.F_Level3 = maModel.F_Level3;//三级分类
                                model.F_Brand = maModel.F_Brand;//品牌
                                model.F_MaterialEffect = maModel.F_MaterialEffect;//F_MaterialEffect
                                model.F_Pipeline = maModel.F_Pipeline;//F_Pipeline
                                model.F_Craft = maModel.F_Craft;//F_Craft
                            }
                        }
                    }
                    if (!string .IsNullOrEmpty(model1[i].F_QuestionType))
                    {
                        Model.T_Wo_QuestionManage Manage = null;
                        try
                        {
                            int b = int.Parse(model1[i].F_QuestionType.Trim());
                            Manage = quesBLL.GetModel(b);


                        }
                        catch (Exception)
                        {
                            Manage = quesBLL.GetModel(model1[i].F_QuestionType); //问题类别（投诉抽捡和咨询类别不一样）
                        }
                        if (Manage !=null )
                        {
                            if (Manage .F_Label =="1")
                            {
                                model.F_QuestionTypeone = Manage.F_QuestionName;
                                model.F_QuestionTypetwo = "";
                                model.F_QuestionTypethree ="";
                            }
                            else if (Manage.F_Label == "2")
                            {
                                Model.T_Wo_QuestionManage Manage1 = quesBLL.GetModel(int .Parse (Manage.F_ParentId.ToString () ) );//获取问题类别
                                model.F_QuestionTypeone = Manage1.F_QuestionName;
                                model.F_QuestionTypetwo = Manage.F_QuestionName;
                                model.F_QuestionTypethree = "";
                            }
                            else
                            {
                                Model.T_Wo_QuestionManage Manage1 = quesBLL.GetModel(int.Parse(Manage.F_ParentId.ToString()));//获取问题类别
                                Model.T_Wo_QuestionManage Manage2 = quesBLL.GetModel(int.Parse(Manage1.F_ParentId.ToString()));//获取问题类别
                                model.F_QuestionTypeone = Manage2.F_QuestionName;//问题类别1
                                model.F_QuestionTypetwo = Manage1.F_QuestionName;//问题类别2
                                model.F_QuestionTypethree =Manage.F_QuestionName;//问题类别3
                            }
                        }
                    }
                    model.F_CusName = model1[i].F_CusName;//客户姓名
                    model.F_CusPhone = model1[i].F_CusPhone;//客户电话
                    model.F_CompanyName = model1[i].F_CompanyName;//公司名称
                    model.F_IncidentProvince = model1[i].F_IncidentProvince;//事发地-省
                    model.F_IncidentCity = model1[i].F_IncidentCity;//事发地-市
                    model.F_IncidentCountry = model1[i].F_IncidentCountry;//事发地-县
                    model.F_IncidentTownship = model1[i].F_IncidentTownship;//事发地-乡
                    model.F_IncidentDetailed = model1[i].F_IncidentDetailed;//事发地-详细
                    model.F_SalesBase = model1[i].F_SalesBase;//销售基地:新乡、新疆、九江、东北、其他(其它手动输入别的点选)
                    model.F_Description = model1[i].F_Description;//问题描述
                    model.F_BatchNumber = model1[i].F_BatchNumber;//生产批次号
                    model.F_TS_Quantity = model1[i].F_TS_Quantity;//投诉-问题数量（吨）
                    model.F_SC_ProductBase = model1[i].F_SC_ProductBase;//市场管理科-生产基地（新乡、新疆、九江、东北、贴牌、无法确定）
                    model.F_SC_PreliminaryOpinion = model1[i].F_SC_PreliminaryOpinion;// 市场管理科-初审意见:退货、换货、补偿、自行处理
                    model.F_SC_QualityMonth = model1[i].F_SC_QualityMonth;//市场管理科-超出质保期限（算法：工单创建日期-生产日期，生产日期根据生产批次号获取，单位：月）
                    model.F_ZL_QualityEventLevel = model1[i].F_ZL_QualityEventLevel;// 质量管理科-质量事件等级（未遂、一般、较大、重大）
                    model.F_ZL_ClaimAmount = model1[i].F_ZL_ClaimAmount;//质量管理科-索赔金额（单位元，对应责任单位）
                    model.F_ZL_ResponsibleUnit = model1[i].F_ZL_ResponsibleUnit;//质量管理科-责任单位（需列出，可选多个）
                    model.F_ZL_ApprovalOpinions = model1[i].F_ZL_ApprovalOpinions;// 质量管理科-审批意见（同意、不同意，若选择不同意，增加输入框输入补充内容）
                    model.F_ZL_ApprovalDisagree = model1[i].F_ZL_ApprovalDisagree;// 质量管理科-审批不同意意见
                    model.F_ZL_CompensationMethod = model1[i].F_ZL_CompensationMethod;//质量管理科-补偿方式（实物、钱款【单位元】，若选择钱款，增加输入框输入补充内容）
                    model.F_ZL_CompensationMoney = model1[i].F_ZL_CompensationMoney;// 质量管理科-补偿钱款
                    model.F_ZL_CResponsibleUnits = model1[i].F_ZL_CResponsibleUnits;//质量管理科-对应责任单位（生产、研发、农化、物流、质量管理科，销售内勤）
                    model.F_ZL_Fquantity = model1[i].F_ZL_Fquantity;// 质量管理科-确认实收数量（吨）
                    model.F_ZL_ImprovementRequirements = model1[i].F_ZL_ImprovementRequirements;//质量管理科-改进要求
                    model.F_ZL_CApprovalNo = model1[i].F_ZL_CApprovalNo;//质量管理科-对应审批工作流单号
                    model.F_DealType = model1[i].F_DealType;//处理方式:当即办理、电话转接、网络转办
                    model.F_DealBy = model1[i].F_DealBy;//处理人
                    model.F_DealTime = model1[i].F_DealTime;//处理时间
                    model.F_DealContent = model1[i].F_DealContent;//处理内容
                    model.F_DealResult = model1[i].F_DealResult;//处理结果
                    model.F_DealReasons = model1[i].F_DealReasons;//未处理原因
                    model.F_CreateOn = model1[i].F_CreateOn; ;//添加时间
                    Input.Add(model);
                }
                return Input;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 根据model返回WorkOrderComplaint对象
        /// </summary>
        private List<Product> modelProduct(List<Model.T_Wo_WorkOrder> model1)
        {
            if (model1 != null && model1.Count > 0)
            {
                List<Product> Input = new List<Product>(model1.Count);
                for (int i = 0; i < model1.Count; i++)
                {
                    Product model = new Product();
                    if (!string .IsNullOrEmpty (model1[i].F_CustomerID))
                    {
                        if (valcode(model1[i].F_CustomerID, 10))//验证客户编码为10位纯数字
                        {

                            Model.T_Cus_CustomerBaseNew cuModel = cusbll.GetModel(model1[i].F_CustomerID);//通过客户编号获取客户资料
                            if (cuModel != null)
                            {
                                Model.T_Sys_Department pdModel = departmentBLL.GetModel(cuModel.F_AreaID);//通过公司id获取部门

                                model.F_Salesman = cuModel.F_Salesman;//业务员
                            }
                        }
                    }
                   
                    model.Category = model1[i].F_TS_Category;//产品
                    model.Company = model1[i].F_CompanyName;//客户单位
                    if (!string.IsNullOrEmpty(model1[i].F_BatchNumber))
                    {
                        var prono = model1[i].F_BatchNumber.Substring(0, 6);
                        try
                        {
                            var pronos = DateTime.ParseExact(prono, "yyMMdd", null).ToString("yyyy-MM-dd");
                          //  var protime = Convert.ToDateTime(pronos);

                            model.ProductionTime = pronos;
                        }
                        catch
                        {
                            model.ProductionTime = "";
                        }
                    }
                    model.CreatOn = model1[i].F_CreateOn .ToString (); ;
                    model.Spurtcode = model1[i].F_BatchNumber;
                    Model.T_Wo_QuestionManage Manage = null;
                    try
                    {
                        int b = int.Parse(model1[i].F_QuestionType.Trim());
                        Manage = quesBLL.GetModel(b);
                        if (Manage !=null )
                        {
                            model.QuestionType = Manage.F_QuestionName;
                        }

                    }
                    catch (Exception)
                    {
                        model.QuestionType = model1[i].F_QuestionType;
                    }
                 
                    model.Description = model1[i].F_Description;
                    model.DealType = model1[i].F_DealType;
                    model.CResponsibleUnits = model1[i].F_ZL_CResponsibleUnits;
                    model.SalesBase = model1[i].F_SalesBase;
                    Input.Add(model);
                }
                return Input;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 是否超时
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        private float   TimeToOver(int id,int time)
        {
            var itemlasts = itembll.GetModelList("  F_ItemType=" + (int)EnumItemType.deal + " and  F_WoID=" + id + "order by F_ID desc ");
            string time1 = "", time2 = ""; int x = 0, y = 0;
           
            if (itemlasts.Count > 0)
            {
                for (int i = 0; i < itemlasts.Count; i++)
                {
                    if (itemlasts[i].F_WoState >= 1)
                    {
                        if (itemlasts[i].F_WoState == 1)
                        {
                            x = i;
                            time1 = itemlasts[i].F_CreateTime.ToString();
                        }
                        if (itemlasts[i].F_WoState == 10)
                        {
                            y = i;
                            time2 = itemlasts[i].F_CreateTime.ToString();
                        }
                    }
                }
                if (time2 != "")
                {
                    if (time1 != "")
                    {
                        if (x > y)
                        {
                            float a = (DateTime.Parse(time2) - DateTime.Parse(time1)).Hours;
                            float b = time - a;
                            return b;
                        }
                        else
                        {
                            float a = (DateTime.Now - DateTime.Parse(time1)).Hours;
                            float b = time - a;
                            return b;
                        }
                    }
                    else
                    {
                        return 0;
                    }
                }
                else if (time1 != "")
                {
                    float a = (DateTime.Now - DateTime.Parse(time1)).Hours;
                    float b = time - a;
                    return b;
                }
                else
                {
                    return 0;
                }
            }
            return 0;
        }
        /// <summary>
        /// 获取客户编号
        /// </summary>
        /// <returns></returns>
        public string GetCustomerID(string channel)
        {
            string str = string.Empty;
            str = "select F_CustomerCode from T_Cus_CustomerBaseNew where F_Channel='" + channel + "' "  + " and isnull(F_IsDelete,'0')='0' "; ;

            return str;
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public List<QuestionType> DataTableToList(DataTable dt)
        {
            List<QuestionType> modelList = new List<QuestionType>();
            int rowsCount = dt.Rows.Count;
            if (rowsCount > 0)
            {
                QuestionType model;
                for (int n = 0; n < rowsCount; n++)
                {
                    model = DataRowToModel(dt.Rows[n]);
                    if (model != null)
                    {
                        modelList.Add(model);
                    }
                }
            }
            return modelList;
        }
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public QuestionType DataRowToModel(DataRow row)
        {
            QuestionType model = new QuestionType();
            if (row != null)
            {
                if (row["F_QuestionType"] != null)
                {
                    model.F_QuestionType = row["F_QuestionType"].ToString();
                }
                if (row["number"] != null && row["number"].ToString() != "")
                {
                    model.number = int.Parse(row["number"].ToString());
                }
            }
            return model;
        }
        private DataTable getData(string stime, string endtime, int deptid = 0)
        {
            DataTable dtNew = new DataTable();
            #region 添加表格标题
            //部门，工号，姓名，建单量，接单量，未处理量，异常量，已处理量
            dtNew.Columns.Add("部门");
            dtNew.Columns.Add("工号");
            dtNew.Columns.Add("姓名");
            dtNew.Columns.Add("建单量");
            dtNew.Columns.Add("接单量");
            dtNew.Columns.Add("未处理量");
            dtNew.Columns.Add("异常量");
            dtNew.Columns.Add("已处理量");
            #endregion
            #region 筛选条件
            string sqlwo = " isdel=0 "; string sqluser = " F_DeleteFlag=0 ";string sqldept = " F_State=1 ";
            if (stime != null && stime.Trim() != "")
            {
                sqlwo += " and CONVERT(varchar , CreateTime, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sqlwo += " and CONVERT(varchar , CreateTime, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }

            if (deptid > 0)
            {
                sqluser += " and F_DeptId=" + deptid;
                sqldept += " and F_DeptId=" + deptid;
            }
            #endregion
            //获取部门
            var depts = new BLL.T_Sys_Department().GetModelList(sqldept);
            //获取人员
            var users = new BLL.T_Sys_UserAccount().GetModelList(sqluser);

            #region 获取工单量
            //--创建量，接单量，未处理量，异常量，已处理量
            //--创建量
            string sqlcreate = $" select CreateUser,COUNT(1) con from T_Wo_WorkOrder where " +sqlwo+" group by CreateUser";
            DataTable dtcreate = DbHelperSQL.Query(sqlcreate).Tables[0];
            //--接单量
            string sqljd = $"select F_NextUser, COUNT(1) con from( " +
            "select  F_WoID, F_NextUser from T_Wo_WorkOrderItem_New where F_ItemType = 1 and F_WoState = " + (int)EnumWorkOrderState.assign + " " +
            "and F_WoID in (select ID FROM T_Wo_WorkOrder where " + sqlwo + ") " +
            "group by F_WoID, F_NextUser) t group by F_NextUser ";
            DataTable dtjd= DbHelperSQL.Query(sqljd).Tables[0];
            //--未处理量
            string sqlwcl = $"select F_CreateUser, COUNT(1) con from( " +
            "select  F_WoID, F_CreateUser from T_Wo_WorkOrderItem_New where F_ItemType = 1 and F_WoState < " + (int)EnumWorkOrderState.finish + " " +
            "and F_WoID in (select ID FROM T_Wo_WorkOrder where " + sqlwo + ") " +
            "group by F_WoID, F_CreateUser) t group by F_CreateUser ";
            DataTable dtwcl = DbHelperSQL.Query(sqlwcl).Tables[0];
            //--异常量：以提交异常退回的人员为准
            string sqlyc = $"select F_CreateUser, COUNT(1) con from( " +
            "select  F_WoID, F_CreateUser from T_Wo_WorkOrderItem_New where F_ItemType = 1 and F_WoState = " + (int)EnumWorkOrderState.abreback + " " +
            "and F_WoID in (select ID FROM T_Wo_WorkOrder where " + sqlwo + ") " +
            "group by F_WoID, F_CreateUser) t group by F_CreateUser ";
            DataTable dtyc = DbHelperSQL.Query(sqlyc).Tables[0];
            //--已处理量：以最终处理人为准
            string sqlwj = $" select LastDealUser,COUNT(1) con from T_Wo_WorkOrder where " + sqlwo + " and State=" + (int)EnumWorkOrderState.finish + " group by LastDealUser";
            DataTable dtwj = DbHelperSQL.Query(sqlwj).Tables[0];
            #endregion

            foreach (var user in users)
            {
                DataRow drNew = dtNew.NewRow();
                //部门，
                var dept = depts.Where(d => d.F_DeptId == user.F_DeptId).FirstOrDefault();
                var deptname = dept != null ? dept.F_DeptName : "";
                //建单量，
                var createcount = "0";
                var createRows = dtcreate.Select(" CreateUser = '" + user.F_UserCode + "'");
                if (createRows.Length > 0)
                    createcount = createRows[0]["con"].ToString();
                //接单量，
                var jdcount = "0";
                var jdRows = dtjd.Select(" F_NextUser = '" + user.F_UserCode + "'");
                if (jdRows.Length > 0)
                    jdcount = jdRows[0]["con"].ToString();
                //未处理量，
                var wclcount = "0";
                var wclRows=dtwcl.Select(" F_CreateUser = '" + user.F_UserCode + "'");
                if (wclRows.Length > 0)
                    wclcount = wclRows[0]["con"].ToString();
                //异常量，
                var yccount = "0";
                var ycRows = dtyc.Select(" F_CreateUser = '" + user.F_UserCode + "'");
                if (ycRows.Length>0)
                    yccount=ycRows[0]["con"].ToString();
                //已处理量
                var wjcount = "0";
                var wjRows = dtwj.Select(" LastDealUser = '" + user.F_UserCode + "'");
                if (wjRows.Length > 0)
                    wjcount= wjRows[0]["con"].ToString();
                drNew["部门"] = deptname;
                drNew["工号"] = user.F_UserCode;
                drNew["姓名"] = user.F_UserName;
                drNew["建单量"] = createcount;
                drNew["接单量"] = jdcount;
                drNew["未处理量"] = wclcount;
                drNew["异常量"] = yccount;
                drNew["已处理量"] = wjcount;

                dtNew.Rows.Add(drNew);
            }

            return dtNew;
        }
    }
  
}
public class DynamicModel : DynamicObject
{
    private string propertyName;
    public string PropertyName
    {
        get { return propertyName; }
        set { propertyName = value; }
    }

    // The inner dictionary.
    Dictionary<string, object> dicProperty
        = new Dictionary<string, object>();
    public Dictionary<string, object> DicProperty
    {
        get
        {
            return dicProperty;
        }
    }


    // This property returns the number of elements
    // in the inner dictionary.
    public int Count
    {
        get
        {
            return dicProperty.Count;
        }
    }

    // If you try to get a value of a property 
    // not defined in the class, this method is called.
    public override bool TryGetMember(
        GetMemberBinder binder, out object result)
    {
        // Converting the property name to lowercase
        // so that property names become case-insensitive.
        string name = binder.Name;

        // If the property name is found in a dictionary,
        // set the result parameter to the property value and return true.
        // Otherwise, return false.
        return dicProperty.TryGetValue(name, out result);
    }

    // If you try to set a value of a property that is
    // not defined in the class, this method is called.
    public override bool TrySetMember(
        SetMemberBinder binder, object value)
    {
        // Converting the property name to lowercase
        // so that property names become case-insensitive.
        if (binder.Name == "Property")
        {
            dicProperty[PropertyName] = value;
        }
        else
        {
            dicProperty[binder.Name] = value;
        }


        // You can always add a value to a dictionary,
        // so this method always returns true.
        return true;
    }
}