XiXianDC_ZhuBiao_API/codegit/CallCenterApi/CallCenterAp.../CallCenterAp.../Controllers/InfoControll...

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

namespace CallCenterApi.Interface.Controllers
{
    public class InfoController : BaseController
    {
        private BLL.T_Wo_WorkOrder workOrderBLL = new BLL.T_Wo_WorkOrder();
        private BLL.T_RegionCategory regionCategoryBLL = new BLL.T_RegionCategory();
        private BLL.T_Sys_DictionaryValue dicValueBLL = new BLL.T_Sys_DictionaryValue();
        private BLL.T_Sys_Department departmentBLL = new BLL.T_Sys_Department();

        #region 
        //来源渠道情况
        public ActionResult GetByQD()
        {
            var dt1 = Getslqd24();
            var dt2 = GetArea();
            var dt3 = GetArea24();


            return Success("加载成功", new
            {
                a = dt1,
                b = dt2,
                c = dt3
            });
        }

        //接单部门情况
        public ActionResult GetByDept()
        {
            var dt1 = GetJD();
            var dt2 = Getdpt();
            var dt3 = GetGDCL();


            return Success("加载成功", new
            {
                a = dt1,
                b = dt2,
                c = dt3
            });
        }

        //投诉举报情况
        #endregion

        #region 1.来源渠道情况
        //受理渠道24小时实时统计
        private object Getslqd24()
        {
            DataTable dt=DbHelperSQL.Query("select F_Name,(select COUNT(1) from T_Wo_WorkOrder where  IsDel=0 and datediff(day,createtime,getdate())=0 and Source=F_Name) con from T_Sys_DictionaryValue where F_DictionaryFlag='GDLY'  and F_State=1").Tables[0];
            
            return dt;
        }
        //受理区域统计
        private object GetArea()
        {
            //186 郑州市
            DataTable dt = DbHelperSQL.Query("select F_RegionName,(select COUNT(1) from T_Wo_WorkOrder where  IsDel=0 and datediff(day,createtime,getdate())=0 and County=F_RegionName) con from T_RegionCategory where  F_DeleteFlag=0 and  F_ParentId=186").Tables[0];

            return dt;
        }
        //受理区域统计(按小时统计)
        private object GetArea24()
        {
            DataTable dt = DbHelperSQL.Query("select datepart(hh,CreateTime) hor,County, count(1) con from T_Wo_WorkOrder where IsDel = 0 and datediff(day, createtime, getdate()) = 0 group by datepart(hh, CreateTime), County").Tables[0];
            var regionList = regionCategoryBLL.DataTableToList(regionCategoryBLL.GetList(" F_DeleteFlag=0 and  F_ParentId=186 ").Tables[0]);

            int[] times = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24 };
            var query3 = times.Select(x => new
            {
                Time = x,
                Arealist = regionList.Select(z =>
                {
                    var con = dt.Select(" hor=" + x.ToString() + " and County='" + z.F_RegionName + "'")[0]["con"].ToString();
                    if (string.IsNullOrEmpty(con))
                    {
                        con = "0";
                    }
                    return new
                    {
                        Area = z.F_RegionName,
                        Count = con
                    };
                }).OrderBy(z => z.Area)
            });
            
            return query3;
        }

        #endregion

        #region 2.接单部门情况
        //接单情况汇总(月份)
        private object GetJD()
        {
            DataTable dt = DbHelperSQL.Query("select datepart(month,CreateTime) mon,TypeClass, count(1) con from T_Wo_WorkOrder where IsDel = 0 and datediff(month,createtime,getdate())<=12 group by datepart(month, CreateTime), TypeClass) desc").Tables[0];
            var tsList = dicValueBLL.DataTableToList(dicValueBLL.GetList(" F_DictionaryFlag='TSLX'  and F_State=1  ").Tables[0]);

            int[] months = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 };
            var query3 = months.Select(x => new
            {
                Month = DateTime.Now.AddMonths(x - 1).ToString("yyyyMM"),
                TSList = tsList.Select(z =>
                {
                    var con = dt.Select(" mon=" + DateTime.Now.AddMonths(x - 1).Month + " and TypeClass='" + z.F_DictionaryValueId + "'")[0]["con"].ToString();
                    if (string.IsNullOrEmpty(con))
                    {
                        con = "0";
                    }
                    return new
                    {
                        tsname = z.F_Name,
                        Count = con
                    };
                }).OrderBy(z => z.tsname)
            });

            return query3;
        }

        //各部门受理工单情况
        private object Getdpt()
        {
            DataTable dt = DbHelperSQL.Query("select F_DeptName,(select COUNT(1) from T_Wo_WorkOrder where  IsDel=0 and datediff(day,createtime,getdate())=0 and ResponDept=F_DeptId) con from T_Sys_Department where F_State=1").Tables[0];

            return dt;
        }

        //工单处理时间分布（小时）
        private object GetGDCL()
        {
            DataTable dt = DbHelperSQL.Query("select datepart(hh,CreateTime) hor,TypeClass, count(1) con from T_Wo_WorkOrder where IsDel = 0 and datediff(day,createtime,getdate())=0 group by datepart(hh, CreateTime), TypeClass").Tables[0];
            var tsList = dicValueBLL.DataTableToList(dicValueBLL.GetList(" F_DictionaryFlag='TSLX'  and F_State=1  ").Tables[0]);

            int[] times = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24 };
            var query3 = times.Select(x => new
            {
                Time = x,
                TSList = tsList.Select(z =>
                {
                    var con = dt.Select(" hor=" + x.ToString() + " and TypeClass='" + z.F_DictionaryValueId + "'")[0]["con"].ToString();
                    if (string.IsNullOrEmpty(con))
                    {
                        con = "0";
                    }
                    return new
                    {
                        tsname = z.F_Name,
                        Count = con
                    };
                }).OrderBy(z => z.tsname)
            });

            return query3;
        }
        #endregion

        #region 3.投诉举报情况
        //投诉/举报汇总
        //举报单位/个人统计
        //投诉关键字统计
        //投诉单位统计
        //举报关键字统计
        #endregion

        #region 4.话务数量情况
        //话务量实时数据统计
        private object GetHWLSS()
        {
            //接通量
            DataTable dt = DbHelperSQL.Query(" select datepart(hh,BeginTime) hor, count(1) con from T_Call_CallRecords where CallState=1 and datediff(day, BeginTime, getdate()) = 0 group by datepart(hh,BeginTime) ").Tables[0];
            //来电量
            DataTable dt1 = DbHelperSQL.Query(" select datepart(hh,BeginTime) hor, count(1) con from T_Call_CallRecords where CallType=0 and datediff(day, BeginTime, getdate()) = 0 group by datepart(hh,BeginTime) ").Tables[0];

            int[] times = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24 };
            var query3 = times.Select(x => {
                var hwcon = dt.Select(" hor=" + x.ToString() + " ")[0]["con"].ToString();//接通量
                var lhcon = dt1.Select(" hor=" + x.ToString() + " ")[0]["con"].ToString();//来电量

                if (string.IsNullOrEmpty(hwcon))
                {
                    hwcon = "0";
                }
                if (string.IsNullOrEmpty(lhcon))
                {
                    lhcon = "0";
                }
                return new
                {

                    Time = x,
                    hwcount = hwcon,
                    lhcount = lhcon,
                };
            });

            return query3;
        }
        //管辖区域来话量统计
        //坐席闲忙比例
        private object GetZXXM()
        {
            DataTable dt = DbHelperSQL.Query(" select datepart(hh,OccurTime) hor,State, count(1) from rep_agent_state where datediff(day, OccurTime, getdate()) = 0 group by datepart(hh,OccurTime),State ").Tables[0];

            int[] times = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24 };
            var query3 = times.Select(x => {
                var con= dt.Select(" hor=" + x.ToString() + " ")[0]["con"].ToString();//总量
                var kxcon = dt.Select(" hor=" + x.ToString() + " State=2 ")[0]["con"].ToString();//空闲
                var thcon = dt.Select(" hor=" + x.ToString() + " State=3 ")[0]["con"].ToString();//通话中
                var hhclcon = dt.Select(" hor=" + x.ToString() + " State=4 ")[0]["con"].ToString();//话后处理中
                var xxcon = dt.Select(" hor=" + x.ToString() + " State=5 ")[0]["con"].ToString();//小休
                var zlcon = dt.Select(" hor=" + x.ToString() + " State=6 ")[0]["con"].ToString();//被请求

                var kxbl = 0.00; var thbl = 0.00; var zmbl = 0.00;

                if (!string.IsNullOrEmpty(con))
                {
                    if (!string.IsNullOrEmpty(kxcon))
                    {
                        kxbl = double.Parse(kxcon) / double.Parse(con);
                    }
                    if (!string.IsNullOrEmpty(thcon))
                    {
                        thbl = double.Parse(thcon) / double.Parse(con);
                    }
                    if (!string.IsNullOrEmpty(xxcon))
                    {
                        zmbl = double.Parse(xxcon) / double.Parse(con);
                    }
                }
                return new
                {
                    Time = x,
                    kxbl = Math.Round(kxbl, 2),
                    thbl = Math.Round(thbl, 2),
                    zmbl = Math.Round(zmbl, 2),
                };
            });

            return query3;
        }
        //话务量总体统计
        private object GetHWZL()
        {
            //话务量
            DataTable dt = DbHelperSQL.Query(" select count(1) con from T_Call_CallRecords where datediff(day, BeginTime, getdate()) = 0 ").Tables[0];

            var hwcon = dt.Rows[0]["con"].ToString();//话务量
            if (string.IsNullOrEmpty(hwcon))
            {
                hwcon = "0";
            }
            //来话量
            DataTable dt1 = DbHelperSQL.Query(" select count(1) con from T_Call_CallRecords where CallType=0 and datediff(day, BeginTime, getdate()) = 0 ").Tables[0];
            var lhcon = dt1.Rows[0]["con"].ToString();//话务量
            if (string.IsNullOrEmpty(lhcon))
            {
                lhcon = "0";
            }

            //接通量,通话时长
            DataTable dt2 = DbHelperSQL.Query(" select count(1) con,sum(TalkLongTime) tltimes from T_Call_CallRecords where CallState=1 and datediff(day, BeginTime, getdate()) = 0 ").Tables[0];
            var jtcon = dt2.Rows[0]["con"].ToString();//接通量
            var thtimes = dt2.Rows[0]["tltimes"].ToString();//通话时长
            if (string.IsNullOrEmpty(jtcon))
            {
                jtcon = "0";
            }
            if (string.IsNullOrEmpty(thtimes))
            {
                thtimes = "0";
            }
            var jtl = 0.00;//接通率
            if (hwcon != "0")
            {
                jtl = double.Parse(jtcon) / double.Parse(hwcon) ;
            }
            var pjthtimes = 0;//平均通话时长
            if (jtcon != "0")
            {
                pjthtimes = int.Parse(thtimes) / int.Parse(jtcon);
            }

            var query3 = new {
                hwcon = hwcon,
                lhcon = lhcon,
                jtcon = jtcon,
                pjthtimes = pjthtimes,
                jtll= Math.Round(jtl, 2)
            };
            return query3;
        }
        #endregion

        #region 5.中心大数据
        //工单类型各数量
        //接通率统计源
        //投诉举报关键话题排名
        //通话数量统计
        private object GetHWL()
        {
            //话务量
            DataTable dt = DbHelperSQL.Query(" select datepart(hh,BeginTime) hor,count(1) con from T_Call_CallRecords where datediff(day, BeginTime, getdate()) = 0 group by datepart(hh,BeginTime) ").Tables[0];
            
            int[] times = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24 };
            var query3 = times.Select(x =>
            {
                var hwcon = dt.Select(" hor=" + x.ToString() + " ")[0]["con"].ToString();//话务量
                var wjt = 0;
                if (hwcon == "0")
                {
                    hwcon = "0";
                }
                return new
                {
                    Time = x,
                    hwcon = hwcon
                };
            });
            return query3;
        }
        //接通率统计
        private object GetJTL()
        {
            //话务量
            DataTable dt = DbHelperSQL.Query(" select datepart(hh,BeginTime) hor,count(1) con from T_Call_CallRecords where datediff(day, BeginTime, getdate()) = 0 group by datepart(hh,BeginTime) ").Tables[0];
            
            //接通量,通话时长
            DataTable dt2 = DbHelperSQL.Query(" select datepart(hh,BeginTime) hor,count(1) con from T_Call_CallRecords where CallState=1 and datediff(day, BeginTime, getdate()) = 0 group by datepart(hh,BeginTime) ").Tables[0];
            
            int[] times = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24 };
            var query3 = times.Select(x =>
            {
                var hwcon = dt.Select(" hor=" + x.ToString() + " ")[0]["con"].ToString();//话务量
                var jtcon = dt2.Select(" hor=" + x.ToString() + " ")[0]["con"].ToString();

                var wjt = 0;
                if (hwcon != "0")
                {
                    wjt = int.Parse(hwcon) - int.Parse(jtcon);
                }
                return new
                {
                    Time = x,
                    jtcon = jtcon,
                    wjtcon = wjt,
                };
            });
            return query3;
        }
        //平均通话时长统计
        private object GetPjthsc()
        {
            //接通量，通话时长
            DataTable dt = DbHelperSQL.Query(" select datepart(hh,BeginTime) hor,count(1) con,sum(TalkLongTime) tltimes from T_Call_CallRecords where CallState=1 and datediff(day, BeginTime, getdate()) = 0 group by datepart(hh,BeginTime) ").Tables[0];

            int[] times = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24 };
            var query3 = times.Select(x => {
                var jtcon = dt.Select(" hor=" + x.ToString() + " ")[0]["con"].ToString();
                var thtimes = dt.Select(" hor=" + x.ToString() + " ")[0]["tltimes"].ToString();

                var pjthsc = 0;
                if (!string.IsNullOrEmpty(jtcon))
                {
                    pjthsc = int.Parse(thtimes) / int.Parse(jtcon);
                }
                return new
                {
                    Time = x,
                    pjthsc= pjthsc,
                };
            });

            return query3;
        }
        #endregion

        #region 6.服务知识
        //工单处理时间统计和分布
        //群众咨询知识库情况统计
        //工单处理时间统计和分布
        #endregion

        #region 12345版大屏接口

        #region 来源渠道
        /// <summary>
        /// 获取受理区域数量
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetAreaCount24ByDate(DateTime? date)
        {
            string where = " and IsDel=0";
            if (date != null)
            {
                where += $" and datediff(day,CreateTime,'{date.Value.ToString("yyyy-MM-dd")}')=0 ";
            }
            else
            {
                where += " and datediff(day,CreateTime,getdate())=0";
            }

            var areaList = new BLL.T_Sys_DictionaryValue().GetModelList(" F_DictionaryFlag='JBDW' and F_State=1 ");

            //int[] hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
            int[] hours = Enumerable.Range(0, 24).ToArray<int>();

            var obj = new
            {
                hours = hours,
                cols = areaList.Select(p => p.F_Name),
                counts = new List<int[]>(),
                tcounts = new int[areaList.Count],
                items = new List<object>()
            };
            int n = 0;
            DataTable[] dts = new DataTable[areaList.Count];
            foreach (var area in areaList)
            {
                int t = 0;
                string strwhere = where;
                strwhere += " and County='" + area.F_DictionaryValueId + "'";
                int[] count = new int[24];
                //for (int i = 0; i < hours.Length; i++)
                //{
                //    string sql = " select count(1) from dbo.T_Bus_WorkOrder where datepart(hh, F_CreateTime)=" + hours[i] + strwhere;
                //    count[i] = Int32.Parse(DbHelperSQL.GetSingle(sql).ToString());
                //    t = t + count[i];
                //}
                //2019-4-11修改查询语句
                string sqlc = " select datepart(hh, CreateTime),count(1) from dbo.T_Wo_WorkOrder where 1=1" + strwhere + " group by datepart(hh, CreateTime)";
                DataTable dtc = DbHelperSQL.Query(sqlc).Tables[0];
                dts[n] = dtc;
                /*for (int i = 0; i < hours.Length; i++)
                {
                    for (int j = 0; j < dtc.Rows.Count; j++)
                    {
                        if (dtc.Rows[j][0].ToString() == hours[i].ToString())
                        {
                            if (!string.IsNullOrEmpty(dtc.Rows[j][1].ToString()))
                            {
                                count[i] = Int32.Parse(dtc.Rows[j][1].ToString());
                                t = t + count[i];
                            }

                        }
                    }
                }

                obj.counts.Add(count);

                obj.tcounts[n]= t;*/
                n += 1;

                string sqltype = " select F_Name TypeName,(select COUNT(1) from T_Wo_WorkOrder where InfoTypeID=F_DictionaryValueId " + strwhere
                + ") Count from dbo.T_Sys_DictionaryValue where F_DictionaryFlag='XXFL' and F_State=1 ";
                DataTable dttype = DbHelperSQL.Query(sqltype).Tables[0];
                obj.items.Add(dttype);
            }
            for (int m = 0; m < areaList.Count; m++)
            {
                DataTable dtc = dts[m];
                int[] count = new int[24];
                int t = 0;
                for (int i = 0; i < hours.Length; i++)
                {
                    for (int j = 0; j < dtc.Rows.Count; j++)
                    {
                        if (dtc.Rows[j][0].ToString() == hours[i].ToString())
                        {
                            if (!string.IsNullOrEmpty(dtc.Rows[j][1].ToString()))
                            {
                                count[i] = Int32.Parse(dtc.Rows[j][1].ToString());
                                t = t + count[i];
                            }

                        }
                    }
                }
                obj.counts.Add(count);

                obj.tcounts[m] = t;
            }

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

        /// <summary>
        /// 获取受理区域数量
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetAreaCountByDate(DateTime? start, DateTime? end, int isdpjk = 0)//isdpjk新版大屏使用接口，返回字段不能重复
        {
            //start = DateTime .Parse ("2018-05-21");
            //end = DateTime.Parse("2018-05-23");
            string where = " and IsDel=0";
            if (start == null && end == null)
            {
                where += " and datediff(day,CreateTime,getdate())=0";
            }
            else
            {
                if (start == null) { start = DateTime.Now; }
                if (end == null) { end = DateTime.Now; }
                where += $" and datediff(day,CreateTime,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,CreateTime,'{end.Value.ToString("yyyy-MM-dd")}')>=0 ";
            }
            //2019-3-27因当即办理和转办都计算为工单量将isnull(F_IsResult,0)=0去掉改为1=1
            string sql = " select F_DictionaryValueId F_AreaId,F_Name AreaName,(select COUNT(1) from T_Wo_WorkOrder where 1=1 " + where
                //+ "and F_SourceArea=F_AreaId) Count from dbo.T_Sys_Area where F_PrentId=0 and F_State=0  order by F_AreaName";//2019-3-26 去掉永城，添加条件and F_AreaName not like '%永城%'
                + "and County=F_DictionaryValueId) Count from dbo.T_Sys_DictionaryValue where F_DictionaryFlag='JBDW' and F_State=1";//2019-3-26 去掉永城，添加条件and F_AreaName not like '%永城%'
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            //2019-3-26 去掉当即办理
            /*DataRow dr = dt.NewRow();
            dr["F_AreaId"] = "0";
            dr["AreaName"] = "当即办理";

            string sqlzb = "select COUNT(1) from T_Bus_WorkOrder where isnull(F_IsResult,0)=1" + where;
            dr["Count"] = DbHelperSQL.GetSingle(sqlzb).ToString();
            dt.Rows.Add(dr);*/
            dt.Columns.Add("Item", typeof(object));
            foreach (DataRow drnew in dt.Rows)
            {
                string strwhere = where;
                string straid = drnew["F_AreaId"].ToString();
                if (straid != "0")
                {
                    //strwhere += " and isnull(F_IsResult,0)=0 and F_SourceArea='" + straid + "' ";//2019-3-27注释掉，改为统计当即办理和转办
                    //strwhere += " and( (isnull(F_IsResult,0)=0 and F_SourceArea='" + straid + "') or  isnull(F_IsResult,0)=1)";
                    strwhere += " and County='" + straid + "'";
                }
                //else
                //{
                //    strwhere += " and isnull(F_IsResult,0)=1 ";
                //}
                if (isdpjk == 0)
                {
                    string sqltype = " select F_Name TypeName,(select COUNT(1) from T_Wo_WorkOrder where InfoTypeID=F_DictionaryValueId " + strwhere
                    + ") Count from dbo.T_Sys_DictionaryValue where F_DictionaryFlag='XXFL' and F_State=1 ";
                    DataTable dttype = DbHelperSQL.Query(sqltype).Tables[0];
                    drnew["Item"] = dttype;
                }
                else
                {
                    string sqltype = " select F_Name TypeName,(select COUNT(1) from T_Wo_WorkOrder where InfoTypeID=F_DictionaryValueId " + strwhere
                    + ") CountT from dbo.T_Sys_DictionaryValue where F_DictionaryFlag='XXFL' and F_State=1 ";
                    DataTable dttype = DbHelperSQL.Query(sqltype).Tables[0];
                    drnew["Item"] = dttype;
                }


            }

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

        /// <summary>
        /// 获取来源百分比
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetSourcePercentByDate(DateTime? start, DateTime? end, string areaid, int isdpjk = 0)//isdpjk新版大屏使用接口，返回字段不能重复
        {
            string where = " and IsDel=0";
            if (start == null && end == null)
            {
                where += " and datediff(day,CreateTime,getdate())=0";
            }
            else
            {
                if (start == null) { start = DateTime.Now; }
                if (end == null) { end = DateTime.Now; }
                where += $" and datediff(day,CreateTime,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,CreateTime,'{end.Value.ToString("yyyy-MM-dd")}')>=0 ";
            }
            if (!string.IsNullOrEmpty(areaid))
            {
                where = " and County=" + areaid;
            }
            string sql = " select F_DictionaryValueId Id,F_Name Source,(select COUNT(1) from T_Wo_WorkOrder where  County=F_DictionaryValueId " + where
                + ") Count from  dbo.T_Sys_DictionaryValue where F_DictionaryFlag='GDLY' and F_State=1 order by F_Name";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];

            int n = Int32.Parse(DbHelperSQL.GetSingle("select COUNT(1) from T_Wo_WorkOrder where 1=1 " + where).ToString());

            dt.Columns.Add("Percent", typeof(object));
            dt.Columns.Add("Item", typeof(object));
            foreach (DataRow drnew in dt.Rows)
            {
                if (n > 0)
                {
                    drnew["Percent"] = Math.Round((double.Parse(drnew["Count"].ToString()) * 100 / n), 2);
                }
                string straid = drnew["Id"].ToString();
                string strwhere = where + " and County='" + straid + "' ";

                if (isdpjk == 0)
                {
                    string sqltype = " select F_Name TypeName,(select COUNT(1) from T_Wo_WorkOrder where InfoTypeID=F_DictionaryValueId " + strwhere
                    + ") Count from dbo.T_Sys_DictionaryValue where F_DictionaryFlag='GDLX' and F_State=1 order by F_Name";
                    DataTable dttype = DbHelperSQL.Query(sqltype).Tables[0];

                    drnew["Item"] = dttype;
                }
                else
                {
                    string sqltype = " select F_Name TypeName,(select COUNT(1) from T_Wo_WorkOrder where InfoTypeID=F_DictionaryValueId " + strwhere
                    + ") CountT from dbo.T_Sys_DictionaryValue where F_DictionaryFlag='GDLX' and F_State=1 order by F_Name";
                    DataTable dttype = DbHelperSQL.Query(sqltype).Tables[0];

                    drnew["Item"] = dttype;
                }
            }

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

        #region 投诉举报---后两个接口未改，没有此项
        /// <summary>
        /// 获取类型数量
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetTypeCountByDate(DateTime? start, DateTime? end)
        {
            string where = " and IsDel=0";
            if (start == null && end == null)
            {
                where += " and datediff(day,CreateTime,getdate())=0";
            }
            else
            {
                if (start == null) { start = DateTime.Now; }
                if (end == null) { end = DateTime.Now; }
                where += $" and datediff(day,CreateTime,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,CreateTime,'{end.Value.ToString("yyyy-MM-dd")}')>=0 ";
            }
            string sql = " select F_Name TypeName,(select COUNT(1) from T_Wo_WorkOrder where InfoTypeID=F_DictionaryValueId " + where
                + ") Count from dbo.T_Sys_DictionaryValue where F_DictionaryFlag='GDLX' and F_State=1  order by F_Name";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];

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

        /// <summary>
        /// 接单情况汇总(月份)
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetTypeCountMonthByDate(string start, string end)
        {
            DateTime startdate = DateTime.Now.AddMonths(-12);
            DateTime enddate = DateTime.Now;
            string sqlwhere = "";
            if (!string.IsNullOrEmpty(start))
            {
                startdate = DateTime.Parse(start + "-01");
                sqlwhere += " and DATEDIFF(day, CreateTime,'" + startdate.ToString("yyyy-MM-dd") + "')<= 0 ";
            }
            if (!string.IsNullOrEmpty(end))
            {
                enddate = DateTime.Parse(end + "-01");
                sqlwhere += " and DATEDIFF(day, CreateTime,'" + enddate.ToString("yyyy-MM-dd") + "')>= 0  ";
            }
            int cnt = (enddate.Year - startdate.Year) * 12 + enddate.Month - startdate.Month + 1;

            string[] months = new string[cnt]; DateTime[] monthss = new DateTime[cnt];

            for (int i = 0; i < cnt; i++)
            {
                var date = startdate.AddMonths(i);
                string mon = date.ToString("yyyyMM");
                months[i] = mon;
                monthss[i] = date;
            }

            var typelist = dicValueBLL.GetModelList("F_DictionaryFlag='GDLX' and F_State=1");
            var obj = new
            {
                months = months,
                cols = typelist.Select(p => p.F_Name),
                counts = new List<int[]>(),
            };

            string sql = "select DATEPART(year,CreateTime) years,DATEPART(MONTH,CreateTime) months,InfoType,COUNT(1) ccount from T_Wo_WorkOrder where 1=1 and IsDel=0  " + sqlwhere + " group by DATEPART(YEAR,CreateTime),DATEPART(MONTH, CreateTime) ,InfoType order by DATEPART(YEAR,CreateTime),DATEPART(MONTH, CreateTime) ,InfoType";
            DataTable dtnew = DbHelperSQL.Query(sql).Tables[0];
            foreach (var l in typelist)
            {
                int[] count = new int[cnt];
                for (int i = 0; i < months.Length; i++)
                {
                    var drInfo = dtnew.Select("years='" + monthss[i].ToString("yyyy") + "' and months='" + monthss[i].ToString("MM") + "' and InfoType=" + l.F_Name );
                    count[i] = (from DataRow dr in drInfo select dr.Field<int>("ccount")).FirstOrDefault();
                }
                obj.counts.Add(count);
            }
            
            return Success("加载成功", obj);
        }

        /// <summary>
        /// 获取关键词数量
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetKeyCountByDate(DateTime? start, DateTime? end)
        {
            string where = " and IsDel=0";
            if (start == null && end == null)
            {
                where += " and datediff(day,CreateTime,getdate())=0";
            }
            else
            {
                if (start == null) { start = DateTime.Now; }
                if (end == null) { end = DateTime.Now; }
                where += $" and datediff(day,CreateTime,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,CreateTime,'{end.Value.ToString("yyyy-MM-dd")}')>=0 ";
            }
            string sql = "select * from (select F_DictionaryValueId Id,F_Name TypeName,(select COUNT(1) from T_Wo_WorkOrder where IsDel=0 " + where
                       + "and ','+F_Key+',' like '%,'+CONVERT(varchar(100),F_ValueId)+',%') Count "
                       + "from dbo.T_Sys_DictionaryValue where F_ItemId = 3 and F_State = 0) a order by a.Count desc ";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];

            dt.Columns.Add("Item", typeof(object));
            foreach (DataRow drnew in dt.Rows)
            {
                string strwhere = where;
                string straid = drnew["Id"].ToString();
                strwhere += " and ','+F_Key+',' like '%," + straid + ",%' ";

                string sqltype = " select F_Value TypeName,(select COUNT(1) from T_Bus_WorkOrder where F_InfoType=F_ValueId " + strwhere
                + ") Count from dbo.T_Sys_DictionaryValue where F_PrentId=37 and F_State=0 ";
                DataTable dttype = DbHelperSQL.Query(sqltype).Tables[0];

                drnew["Item"] = dttype;
            }

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

        /// <summary>
        /// 获取承办单位数量
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetDeptCountByDate(DateTime? start, DateTime? end, string areaid, int deptid = 0)
        {
            string where = " and F_IsDelete=0";
            if (start == null && end == null)
            {
                where += " and datediff(day,F_CreateTime,getdate())=0";
            }
            else
            {
                if (start == null) { start = DateTime.Now; }
                if (end == null) { end = DateTime.Now; }
                where += $" and datediff(day,F_CreateTime,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,F_CreateTime,'{end.Value.ToString("yyyy-MM-dd")}')>=0 ";
            }
            string sql = " select F_DeptId Id,F_DeptName AreaName,(select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0 and F_WorkOrderID in "
                + "(select F_WorkOrderID from T_Bus_AssignedInfo where F_IsSure!=3 " + where
                + " and F_MainDeptId=F_DeptId)) Count from dbo.T_Sys_Department where F_IsDept='1' ";
            if (deptid > 0)
            {
                sql += " and F_DeptId='" + deptid + "'";
            }
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];

            dt.Columns.Add("Item", typeof(object));
            foreach (DataRow drnew in dt.Rows)
            {
                string strwhere = where;
                string straid = drnew["Id"].ToString();
                strwhere += " and F_MainDeptId='" + straid + "' ";

                string sqltype = " select F_Value TypeName,(select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0 and F_InfoType=F_ValueId and F_WorkOrderID in"
                    + "(select F_WorkOrderID from T_Bus_AssignedInfo where F_IsSure!=3 " + strwhere
                + ")) Count from dbo.T_Sys_DictionaryValue where F_PrentId=37 and F_State=0 ";
                DataTable dttype = DbHelperSQL.Query(sqltype).Tables[0];

                drnew["Item"] = dttype;
            }

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

        #region 话务数量---待测试
        /// <summary>
        /// 话务量实时数据统计
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetTelCount24ByDate(DateTime? date)
        {
            if (date == null)
            {
                date = DateTime.Now;
            }

            string strdate = date.Value.ToString("yyyy-MM-dd");

            //int[] hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
            int[] hours = Enumerable.Range(0, 24).ToArray<int>();
            int[] tcounts = new int[24];//来电数量
            int[] ccounts = new int[24];//接通数量
            int[] lcounts = new int[24];//留言数量
            int[] gcounts = new int[24];//放弃数量
            int[] scounts = new int[24];//骚扰数量

            string ldsql = $"select  DATEPART(HH,BeginTime) hours,COUNT(1) ccount from T_Call_CallRecords where CallType=0 and DATEDIFF(day, BeginTime, '" + strdate + "') = 0 group by DATEPART(HH, BeginTime) order by DATEPART(HH, BeginTime)";
            string jtsql = $"select  DATEPART(HH,BeginTime) hours,COUNT(1) ccount from T_Call_CallRecords where CallType=0 and CallState=1 and DATEDIFF(day, BeginTime, '" + strdate + "') = 0 group by DATEPART(HH, BeginTime) order by DATEPART(HH, BeginTime)";
            string lysql = $"select  DATEPART(HH,BeginTime) hours,COUNT(1) ccount from T_Call_CallRecords where CallType=0 and DealType=4 and DATEDIFF(day, BeginTime, '" + strdate + "') = 0 group by DATEPART(HH, BeginTime) order by DATEPART(HH, BeginTime)";
            string srsql = $"select  DATEPART(HH,BeginTime) hours,COUNT(1) ccount from T_Call_CallRecords where CallType=0 and DealType=1 and DATEDIFF(day, BeginTime, '" + strdate + "') = 0 group by DATEPART(HH, BeginTime) order by DATEPART(HH, BeginTime)";

            DataTable dtld = DbHelperSQL.Query(ldsql).Tables[0];
            DataTable dtjt = DbHelperSQL.Query(jtsql).Tables[0];
            DataTable dtly = DbHelperSQL.Query(lysql).Tables[0];
            DataTable dtsr = DbHelperSQL.Query(srsql).Tables[0];

            for (int i = 0; i < hours.Length; i++)
            {
                var drld = dtld.Select("hours='" + hours[i] + "' ");
                tcounts[i] = (from DataRow dr in drld select dr.Field<int>("ccount")).FirstOrDefault();

                var drjt = dtjt.Select("hours='" + hours[i] + "' ");
                ccounts[i] = (from DataRow dr in drjt select dr.Field<int>("ccount")).FirstOrDefault();

                var drly = dtly.Select("hours='" + hours[i] + "' ");
                lcounts[i] = (from DataRow dr in drly select dr.Field<int>("ccount")).FirstOrDefault();

                var drsr = dtsr.Select("hours='" + hours[i] + "' ");
                scounts[i] = (from DataRow dr in drsr select dr.Field<int>("ccount")).FirstOrDefault();

                gcounts[i] = tcounts[i] - ccounts[i] - lcounts[i] - scounts[i];

            }
            #region 优化前
            //for (int i = 0; i < hours.Length; i++)
            //{
            //    string sql = " select count(1) from dbo.T_Call_CallRecords where CallType=0 and datediff(day, BeginTime, '" + strdate + "') = 0  "
            //            + " and datepart(hh,BeginTime)=" + hours[i];
            //    tcounts[i] = Int32.Parse(DbHelperSQL.GetSingle(sql).ToString());

            //    sql = " select count(1) from dbo.T_Call_CallRecords where CallType=0 and datediff(day, BeginTime, '" + strdate + "') = 0  "
            //            + " and CallState=1 and datepart(hh,BeginTime)=" + hours[i];
            //    ccounts[i] = Int32.Parse(DbHelperSQL.GetSingle(sql).ToString());

            //    sql = " select count(1) from dbo.T_Call_CallRecords where CallType=0 and datediff(day, BeginTime,'" + strdate + "') = 0  "
            //            + " and DealType=4 and datepart(hh,BeginTime)=" + hours[i];
            //    lcounts[i] = Int32.Parse(DbHelperSQL.GetSingle(sql).ToString());

            //    sql = " select count(1) from dbo.T_Call_CallRecords where CallType=0 and datediff(day, BeginTime, '" + strdate + "') = 0  "
            //            + " and DealType=1  and datepart(hh,BeginTime)=" + hours[i];
            //    scounts[i] = Int32.Parse(DbHelperSQL.GetSingle(sql).ToString());

            //    gcounts[i] = tcounts[i] - ccounts[i] - lcounts[i] - scounts[i];

            //}
            #endregion

            var obj = new
            {
                hours = hours,
                rcounts = tcounts,
                ccounts = ccounts,
                //lcounts = lcounts,
                gcounts = gcounts,
                scounts = scounts
            };

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

        /// <summary>
        /// 获取通话数量
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetTelCountByDate(DateTime? date)
        {
            string where = " ";
            if (date == null)
            {
                date = DateTime.Now;
            }

            string strdate = date.Value.ToString("yyyy-MM-dd");
            where += " and datediff(day,BeginTime,'" + strdate + "')=0";

            //if (start == null && end == null)
            //{
            //    where += " and datediff(day,BeginTime,getdate())=0";
            //}
            //else
            //{
            //    if (start == null) { start = DateTime.Now; }
            //    if (end == null) { end = DateTime.Now; }
            //    where += $" and datediff(day,BeginTime,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,BeginTime,'{end.Value.ToString("yyyy-MM-dd")}')>=0 ";
            //}

            //话务量
            //DataTable dt = DbHelperSQL.Query(" select count(1) con from T_Call_CallRecords where 1=1 " + where).Tables[0];

            //var hwcon = dt.Rows[0]["con"].ToString();//话务量
            //外呼量
            DataTable dt = DbHelperSQL.Query(" select count(1) con from T_Call_CallRecords where CallType=1 " + where).Tables[0];

            var hwcon = dt.Rows[0]["con"].ToString();//话务量

            //来话量
            DataTable dt1 = DbHelperSQL.Query(" select count(1) con from T_Call_CallRecords where CallType=0 " + where).Tables[0];
            var lhcon = dt1.Rows[0]["con"].ToString();//话务量

            //接通量,通话时长
            DataTable dt2 = DbHelperSQL.Query(" select count(1) con,sum(TalkLongTime) tltimes from T_Call_CallRecords where CallState=1 " + where).Tables[0];
            var jtcon = dt2.Rows[0]["con"].ToString();//接通量
            var ths = dt2.Rows[0]["tltimes"];//通话时长
            var thtimes = "0";
            if (ths != null && ths.ToString() != "")
            {
                thtimes = ths.ToString();
            }
            //string jtl = "-";//接通率
            //if (hwcon != "0")
            //{
            //    jtl = (double.Parse(jtcon) / double.Parse(hwcon)).ToString("0.00%");
            //}
            double jtl = 0;//接通率
            if (hwcon != "0")
            {
                jtl = Math.Round((double.Parse(jtcon) * 100 / double.Parse(hwcon)), 2);
            }
            double pjthtimes = 0.00;//平均通话时长
            if (jtcon != "0")
            {
                pjthtimes = Math.Round(double.Parse(thtimes) / double.Parse(jtcon));
            }

            var obj = new
            {
                hwcon = hwcon,
                lhcon = lhcon,
                jtcon = jtcon,
                pjthtimes = pjthtimes,
                jtl = jtl,
                thtimes
            };

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

        /// <summary>
        /// 坐席闲忙比例
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetUserStateCount24ByDate(DateTime? start, DateTime? end)
        {
            string where = " 1=1 ";
            if (start == null && end == null)
            {
                where += " and datediff(day,OccurTime,getdate())=0";
            }
            else
            {
                if (start == null) { start = DateTime.Now; }
                if (end == null) { end = DateTime.Now; }
                where += $" and datediff(day,OccurTime,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,OccurTime,'{end.Value.ToString("yyyy-MM-dd")}')>=0 ";
            }

            string sql = "select hor,type,COUNT(1) con from (select datepart(hh, OccurTime) hor, State type, agentid, count(1) con  "
                + " from rep_agent_state   where " + where
                + "  group by datepart(hh, OccurTime), State, agentid ) t group by hor, type";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];

            //int[] hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
            int[] hours = Enumerable.Range(0, 24).ToArray<int>();
            double[] kxpercents = new double[24];
            double[] thpercents = new double[24];
            double[] zmpercents = new double[24];

            for (int i = 0; i < hours.Length; i++)
            {

                var list = dt.Select(" hor=" + hours[i]);
                int con = 0;//总量
                foreach (var l in list)
                {
                    con = con + Int32.Parse(l["con"].ToString());
                }
                //var con = list.Count() > 0 ? list[0]["con"].ToString() : "0";//总量
                var kxlist = dt.Select(" hor=" + hours[i] + " and type=2 ");
                var kxcon = kxlist.Count() > 0 ? kxlist[0]["con"].ToString() : "0";//空闲
                var thlist = dt.Select(" hor=" + hours[i] + " and type=3 ");
                var thcon = thlist.Count() > 0 ? thlist[0]["con"].ToString() : "0"; ;//通话中
                var hhcllist = dt.Select(" hor=" + hours[i] + " and type=4 ");
                var hhclcon = hhcllist.Count() > 0 ? hhcllist[0]["con"].ToString() : "0"; ;//话后处理中
                var xxlist = dt.Select(" hor=" + hours[i] + " and type=5 ");
                var xxcon = xxlist.Count() > 0 ? xxlist[0]["con"].ToString() : "0"; ;//小休
                var zllist = dt.Select(" hor=" + hours[i] + " and type=6 ");
                var zlcon = zllist.Count() > 0 ? zllist[0]["con"].ToString() : "0"; ;//被请求
                if (con == 0)
                {
                    kxpercents[i] = 0;
                    thpercents[i] = 0;
                    zmpercents[i] = 0;
                }
                else
                {
                    double  zm = double.Parse(hhclcon) + double.Parse(xxcon) + double.Parse(zlcon);
                    kxpercents[i] = Math.Round((double.Parse(kxcon) * 100 / con), 2);
                    thpercents[i] = Math.Round((double.Parse(thcon) * 100 / con), 2);
                    zmpercents[i] = Math.Round((zm * 100 / con), 2);//zmpercents[i] = Math.Round((double.Parse(xxcon) * 100 / con), 2);//2020-4-14调整置忙比例计算
                }

            }
            var obj = new
            {
                hours = hours,
                kxpercents = kxpercents,
                thpercents = thpercents,
                zmpercents = zmpercents
            };
            return Success("加载成功", obj);
        }
        #endregion

        #region 中心大数据---正在修改，有一部分数据没有

        /// <summary>
        /// 工单类型各数量
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetCenterTypeCountByDate()
        {
            string sql = " select F_Name TypeName,(select COUNT(1) from T_Wo_WorkOrder where IsDel=0 "
                + "and InfoTypeID=F_DictionaryValueId) Count from dbo.T_Sys_DictionaryValue whereF_DictionaryFlag='GDLX' and F_State=1 ";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            DataRow dr = dt.NewRow();
            dr["TypeName"] = "工单总计";

            string sqlzj = "select COUNT(1) from T_Wo_WorkOrder  WITH(NOLOCK) where IsDel=0 ";
            dr["Count"] = DbHelperSQL.GetSingle(sqlzj).ToString();

            dt.Rows.InsertAt(dr, 0);

            DataRow dr1 = dt.NewRow();
            dr1["TypeName"] = "今日受理量";

            string sqldayaccept = "select COUNT(1) from T_Wo_WorkOrder  WITH(NOLOCK) where IsDel=0 and datediff(day,CreateTime,getdate())=0 ";
            dr1["Count"] = DbHelperSQL.GetSingle(sqldayaccept).ToString();

            dt.Rows.InsertAt(dr1, 1);

            DataRow dr2 = dt.NewRow();
            dr2["TypeName"] = "今日交办量";//----没有交办数据

            string sqldayassign = "select COUNT(1) from T_Wo_WorkOrder  WITH(NOLOCK) where IsDel=0 and datediff(day,CreateTime,getdate())=0 and isnull(F_IsResult,0)=0 and F_WorkState>1 ";
            dr2["Count"] = DbHelperSQL.GetSingle(sqldayassign).ToString();

            dt.Rows.InsertAt(dr2, 2);

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

        /// <summary>
        /// 工单类型各数量(new)
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetTypeCountNew(string areaid)
        {
            string strwhere = "";
            if (!string.IsNullOrEmpty(areaid))
            {
                strwhere += " and F_SourceArea=" + areaid;
            }
            int n = Int32.Parse(DbHelperSQL.GetSingle("select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0 and isnull(F_InfoType,0)!=0" + strwhere).ToString());

            string sql = " select F_Value TypeName,(select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0 and  datediff(day,F_CreateTime,getdate())=0"
                + "and F_InfoType=F_ValueId" + strwhere + ") DayCount, (select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0 and  datediff(month,F_CreateTime,getdate())=0"
                + "and F_InfoType=F_ValueId" + strwhere + ") MonthCount, (select COUNT(1) from T_Bus_WorkOrder where F_IsDelete=0 "
                + "and F_InfoType=F_ValueId" + strwhere + ") TotalCount from dbo.T_Sys_DictionaryValue where F_PrentId=37 and F_State=0 order by F_Value ";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];
            dt.Columns.Add("Percent", typeof(object));
            foreach (DataRow drnew in dt.Rows)
            {
                if (n > 0)
                {
                    drnew["Percent"] = Math.Round((double.Parse(drnew["TotalCount"].ToString()) * 100 / n), 2) + "%";
                }
                else
                {
                    drnew["Percent"] = "";
                }
            }

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

        /// <summary>
        /// 接通率统计
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetTelRate24ByDate(DateTime? date)
        {
            if (date == null)
            {
                date = DateTime.Now;
            }

            string strdate = date.Value.ToString("yyyy-MM-dd");

            //int[] hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
            int[] hours = Enumerable.Range(0, 24).ToArray<int>();
            //string[] rates = new string[24];
            double[] rates = new double[24];

            for (int i = 0; i < hours.Length; i++)
            {
                string sql = " select count(1) from dbo.T_Call_CallRecords  WITH(NOLOCK) where CallType=0 and CallState=1 and UserCode is not null "
                    + " and datediff(day, BeginTime, '" + strdate + "') = 0  and datepart(hh,BeginTime)=" + hours[i];
                var c = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());

                sql = " select count(1) from dbo.T_Call_CallRecords  WITH(NOLOCK) where CallType=0 and UserCode is not null "
                    + "and datediff(day, BeginTime,  '" + strdate + "') = 0 and datepart(hh,BeginTime)=" + hours[i];
                var t = Int32.Parse(DbHelperSQL.Query(sql).Tables[0].Rows[0][0].ToString());

                //rates[i]= t > 0 ? ((double)c / t).ToString("0.00%") : "-";
                rates[i] = t > 0 ? Math.Round(((double)c * 100 / t), 2) : 0;
            }

            var obj = new
            {
                hours = hours,
                rates = rates
            };
            return Success("加载成功", obj);
        }

        /// <summary>
        /// 平均通话时长统计
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetAvgTelTime24ByDate(DateTime? date)
        {
            if (date == null)
            {
                date = DateTime.Now;
            }

            string strdate = date.Value.ToString("yyyy-MM-dd");

            //接通量，通话时长
            string sql = " select datepart(hh,BeginTime) hor,count(1) con,sum(TalkLongTime) tltimes from T_Call_CallRecords  WITH(NOLOCK) where "
                + "CallState=1 and datediff(day, BeginTime, '" + strdate + "') = 0 group by datepart(hh,BeginTime) ";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];

            //int[] hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
            int[] hours = Enumerable.Range(0, 24).ToArray<int>();
            double[] avgtimes = new double[24];
            for (int i = 0; i < hours.Length; i++)
            {
                var jtlist = dt.Select(" hor=" + hours[i] + " ");
                var jtcon = jtlist.Count() > 0 ? jtlist[0]["con"].ToString() : "0";
                var thtimes = jtlist.Count() > 0 ? jtlist[0]["tltimes"].ToString() : "0";

                double avgtime = 0.00;
                if (jtcon != "0")
                {
                    if (thtimes == "") { thtimes = "0"; }
                    avgtime = Math.Round(double.Parse(thtimes) / double.Parse(jtcon), 2);
                }

                avgtimes[i] = avgtime;
            }
            var obj = new
            {
                hours = hours,
                avgtimes = avgtimes
            };
            return Success("加载成功", obj);
        }

        #endregion

        #region 安全感、满意度数据大屏展示
        private readonly BLL.T_Ask_QuestionItems questionItemBLL = new BLL.T_Ask_QuestionItems();
        private readonly BLL.T_Sys_DictionaryValue dvItemBLL = new BLL.T_Sys_DictionaryValue();
        private readonly BLL.T_Call_OutAnswers ansBLL = new BLL.T_Call_OutAnswers();

        #region 社会治安安全感统计数据
        /// <summary>
        /// 
        /// </summary>
        /// <param name="TaskID">任务id</param>
        /// <param name="quesid">问题id</param>
        /// <param name="countryid">乡镇id</param>
        /// <param name="sdate">开始时间</param>
        /// <param name="edate">结束时间</param>
        /// <returns></returns>
        public ActionResult GetAQG(string TaskID, string ques, string countryid, string sdate, string edate,int isdc=0)
        {
            string quesid = "52";
            if (string.IsNullOrEmpty(sdate))
            {
                sdate = DateTime.Now.ToString("yyyy-MM") + "-01 00:00:00";
            }
            if (string.IsNullOrEmpty(edate))
            {
                edate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
            }

            #region 新建输出表
            DataTable dtnew = new DataTable();
            dtnew.Columns.Add("乡镇");

            var dsi = questionItemBLL.GetModelList(" F_QuestionId='" + quesid + "'");
            if (dsi.Count > 0)
            {
                foreach (var itemconf in dsi)
                {
                    string cname = itemconf.F_ItemName;
                    if (itemconf.F_ItemName.Contains ("→"))
                    {
                        int index = itemconf.F_ItemName.IndexOf("→");
                        cname = itemconf.F_ItemName.Substring(0,index);
                    }
                    dtnew.Columns.Add(cname);
                    dtnew.Columns.Add(cname + "比例");
                }
            }
            dtnew.Columns.Add("总数");
            dtnew.Columns.Add("安全感比例");
            #endregion

            string strtaskid = "";
            if (!string.IsNullOrEmpty(TaskID))
            {
                strtaskid = " and F_TaskID='" + TaskID + "' ";
            }
            //2018-7-6
            DataRow drtotal = dtnew.NewRow();
            drtotal["乡镇"] = "合计";
            #region 根据单位和QuestionItemID统计数量
            if (string.IsNullOrEmpty(countryid))//countryid == "")//为空时统计全部乡镇信息，否则显示单个乡镇数据
            {
                //从数据字典表中获取乡镇信息（JBDW）
                var dsc = dvItemBLL.GetModelList(" F_DictionaryFlag='XZQY'");

                //var dsc = questionItemBLL.GetModelList(" F_QuestionId='34'");//获取全部乡镇
                if (dsc.Count > 0)
                {
                    foreach (var iconf in dsc)
                    {
                        DataRow dr = dtnew.NewRow();

                        dr["乡镇"] = iconf.F_Name;
                        DataSet dsa = DbHelperSQL.Query("select count(F_Expand3) from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 and ltrim(rtrim(F_Answer))='" + iconf.F_Name + "' "  + strtaskid);
                        DataTable dta = dsa.Tables[0];
                        if (dta.Rows.Count > 0)
                        {
                            int totalnum = 0;
                            decimal safenum = 0;
                            decimal knownnum = 0;
                            decimal joinnum = 0;
                            decimal mynum = 0;
                            decimal unknownnum = 0;
                            //获取问题各项答案数据
                            if (dsi.Count > 0)
                            {
                                foreach (var itemconf in dsi)
                                {
                                    var dsk = ansBLL.GetRecordCount(" F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 "  + strtaskid + " and ltrim(rtrim(F_Answer))='" + iconf.F_Name + "') and F_QID='" + quesid + "' and F_QIID='" + itemconf.F_ItemId + "'");
                                    string cname = itemconf.F_ItemName;
                                    if (itemconf.F_ItemName.Contains("→"))
                                    {
                                        int index = itemconf.F_ItemName.IndexOf("→");
                                        cname = itemconf.F_ItemName.Substring(0, index );
                                    }
                                    dr[cname] = dsk;

                                    totalnum += dsk;
                                    if (quesid == "52")
                                    {
                                        string qitem = itemconf.F_ItemName;
                                        if ((qitem.Length > 1 && qitem.Substring(0, 2) == "安全") || (qitem.Length > 3 && qitem.Substring(0, 4) == "基本安全"))
                                        {
                                            safenum += dsk;
                                        }
                                    }
                                    #region
                                    /*else if (quesid == "50")
                                    {
                                        if (itemconf.F_ItemName == "参加了")
                                        {
                                            joinnum += dsk;
                                        }
                                        else if (itemconf.F_ItemName == "知道，没参加")
                                        {
                                            knownnum += dsk;
                                        }
                                    }
                                    else if (quesid == "53")
                                    {
                                        if (itemconf.F_ItemName == "比较了解" || itemconf.F_ItemName == "基本了解" || itemconf.F_ItemName == "知道一些")
                                        {
                                            knownnum += dsk;
                                        }

                                    }
                                    else if (quesid == "54")
                                    {
                                        if (itemconf.F_ItemName == "非常满意" || itemconf.F_ItemName == "一般满意")
                                        {
                                            mynum += dsk;
                                        }
                                        else if (itemconf.F_ItemName == "不知道")
                                        {
                                            unknownnum += dsk;
                                        }

                                    }*/
                                    #endregion

                                }
                                dr["总数"] = totalnum;
                                //公众安全感需加安全感指数
                                if (quesid == "52")
                                {
                                    foreach (var itemconf in dsi)
                                    {
                                        if (totalnum > 0)
                                        {
                                            string cname = itemconf.F_ItemName;
                                            if (itemconf.F_ItemName.Contains("→"))
                                            {
                                                int index = itemconf.F_ItemName.IndexOf("→");
                                                cname = itemconf.F_ItemName.Substring(0, index );
                                            }
                                            dr[cname  + "比例"] = Math.Round(decimal.Parse(dr[cname ].ToString()) / totalnum * 100, 2).ToString() + "%";
                                        }
                                        else
                                        {
                                            string cname = itemconf.F_ItemName;
                                            if (itemconf.F_ItemName.Contains("→"))
                                            {
                                                int index = itemconf.F_ItemName.IndexOf("→");
                                                cname = itemconf.F_ItemName.Substring(0, index);
                                            }
                                            dr[cname  + "比例"] = 0;
                                        }

                                    }

                                    if (totalnum > 0)
                                    {
                                        dr["安全感比例"] = Math.Round(safenum / totalnum * 100, 2).ToString() + "%";//安全感=（安全+基本安全）/成功总数
                                    }
                                    else
                                    {
                                        dr["安全感比例"] = 0;
                                    }
                                }
                                //平安建设知晓率需加知晓率和参与率
                                /*if (quesid == "50")
                                {
                                    if (totalnum > 0)
                                    {
                                        dr["知晓率"] = Math.Round((knownnum + joinnum) / totalnum * 100, 2).ToString() + "%";//知晓率=（参加过+知道没参加）/成功总数
                                        dr["参与率"] = Math.Round(joinnum / totalnum * 100, 2).ToString() + "%";//参与率=参加过/成功总数
                                    }
                                    else
                                    {
                                        dr["知晓率"] = 0;
                                        dr["参与率"] = 0;
                                    }
                                }
                                if (quesid == "53")
                                {
                                    if (totalnum > 0)
                                    {
                                        dr["知晓率"] = Math.Round(knownnum / totalnum * 100, 2).ToString() + "%";//知晓率=（参加过+知道没参加）/成功总数

                                    }
                                    else
                                    {
                                        dr["知晓率"] = 0;

                                    }
                                }
                                if (quesid == "54")
                                {
                                    if (totalnum - unknownnum > 0)
                                    {
                                        dr["满意度"] = Math.Round(mynum / (totalnum - unknownnum) * 100, 2).ToString() + "%";//知晓率=（参加过+知道没参加）/成功总数

                                    }
                                    else
                                    {
                                        dr["满意度"] = 0;

                                    }
                                }*/
                                dtnew.Rows.Add(dr);
                            }
                        }
                    }
                }
            }
            else//SELECT COUNT(1) as buanquanshu FROM T_Call_OutAnswers WHERE F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where F_OptOn between '" + startdate + "' and '" + enddate + "' and F_QID=102 and F_QIID=101) and F_QID=102 and F_QIID=101
            {
                string contyname = "";
                var dsn = dvItemBLL.GetModelList(" F_DictionaryValueId='" + countryid + "'");
                if (dsn.Count > 0)
                {
                    contyname = dsn[0].F_Name;// iconf.F_Name;                   
                }
                DataRow dr = dtnew.NewRow();
                //根据id获取单位名和总数,因为按F_Answer分组此项值已固定，所以查询最多只有一行数据，固定取第一行
                //select count(F_Expand3),F_Answer from T_Call_OutAnswers where F_TaskID='1' and F_OptOn between '2018-02-03' and '2018-06-25' and F_QIID='167' GROUP BY F_Answer 
                DataSet dsa = DbHelperSQL.Query("select count(F_Expand3) from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 and ltrim(rtrim(F_Answer))='" + contyname + "'"+ strtaskid);
                DataTable dta = dsa.Tables[0];
                if (dta.Rows.Count > 0)
                {
                    dr["乡镇"] = contyname;// dta.Rows[0][1].ToString();
                    //dr["总数"] = dta.Rows[0][0].ToString();
                    int totalnum = 0;
                    decimal safenum = 0;
                    decimal knownnum = 0;
                    decimal joinnum = 0;
                    decimal mynum = 0;
                    //获取问题各项答案数据
                    if (dsi.Count > 0)
                    {
                        foreach (var itemconf in dsi)
                        {
                            var dsc = ansBLL.GetRecordCount(" F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where F_TaskID='" + TaskID + "' and DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 "  + strtaskid + " and ltrim(rtrim(F_Answer))='" + contyname + "') and F_QID='" + quesid + "' and F_QIID='" + itemconf.F_ItemId + "'");
                            string cname = itemconf.F_ItemName;
                            if (itemconf.F_ItemName.Contains("→"))
                            {
                                int index = itemconf.F_ItemName.IndexOf("→");
                                cname = itemconf.F_ItemName.Substring(0, index );
                            }
                            dr[cname ] = dsc;
                            totalnum += dsc;
                            if (quesid == "52")
                            {
                                string qitem = itemconf.F_ItemName;
                                if ((qitem.Length > 1 && qitem.Substring(0, 2) == "安全") || (qitem.Length > 3 && qitem.Substring(0, 4) == "基本安全"))
                                {
                                    safenum += dsc;
                                }
                            }
                            #region
                            /*else if (quesid == "50" || quesid == "新增")
                            {
                                if (itemconf.F_ItemName == "参加了")
                                {
                                    joinnum += dsc;
                                }
                                else if (itemconf.F_ItemName == "知道，没参加")
                                {
                                    knownnum += dsc;
                                }
                            }
                            else if (quesid == "53")
                            {
                                if (itemconf.F_ItemName == "比较了解" || itemconf.F_ItemName == "基本了解" || itemconf.F_ItemName == "知道一些")
                                {
                                    knownnum += dsc;
                                }

                            }
                            else if (quesid == "54")
                            {
                                if (itemconf.F_ItemName == "非常满意" || itemconf.F_ItemName == "一般满意")
                                {
                                    mynum += dsc;
                                }

                            }*/
                            #endregion
                        }
                        dr["总数"] = totalnum;
                        //公众安全感需加安全感指数
                        if (quesid == "52")
                        {
                            
                                foreach (var itemconf in dsi)
                                {
                                if (totalnum > 0)
                                {
                                    string cname = itemconf.F_ItemName;
                                    if (itemconf.F_ItemName.Contains("→"))
                                    {
                                        int index = itemconf.F_ItemName.IndexOf("→");
                                        cname = itemconf.F_ItemName.Substring(0, index);
                                    }
                                    dr[cname  + "比例"] = Math.Round(decimal.Parse(dr[itemconf.F_ItemName].ToString()) / totalnum * 100, 2).ToString() + "%";
                                }
                                else
                                {
                                    string cname = itemconf.F_ItemName;
                                    if (itemconf.F_ItemName.Contains("→"))
                                    {
                                        int index = itemconf.F_ItemName.IndexOf("→");
                                        cname = itemconf.F_ItemName.Substring(0, index);
                                    }
                                    dr[cname  + "比例"] = 0;
                                }

                                }
                            
                            if (totalnum > 0)
                            {
                                dr["安全感比例"] = Math.Round(safenum / totalnum * 100, 2).ToString() + "%";//安全感=（安全+基本安全）/成功总数
                            }
                            else
                            {
                                dr["安全感比例"] = 0;
                            }
                        }
                        //平安建设知晓率需加知晓率和参与率
                        /*if (quesid == "50")
                        {
                            if (totalnum > 0)
                            {
                                dr["知晓率"] = Math.Round((knownnum + joinnum) / totalnum * 100, 2).ToString() + "%";//知晓率=（参加过+知道没参加）/成功总数
                                dr["参与率"] = Math.Round(joinnum / totalnum * 100, 2).ToString() + "%";//参与率=参加过/成功总数
                            }
                            else
                            {
                                dr["知晓率"] = 0;
                                dr["参与率"] = 0;
                            }
                        }
                        if (quesid == "53")
                        {
                            if (totalnum > 0)
                            {
                                dr["知晓率"] = Math.Round(knownnum / totalnum * 100, 2).ToString() + "%";//知晓率=（参加过+知道没参加）/成功总数

                            }
                            else
                            {
                                dr["知晓率"] = 0;

                            }
                        }
                        if (quesid == "54")
                        {
                            if (totalnum > 0)
                            {
                                dr["满意度"] = Math.Round(mynum / totalnum * 100, 2).ToString() + "%";//知晓率=（参加过+知道没参加）/成功总数

                            }
                            else
                            {
                                dr["满意度"] = 0;

                            }
                        }*/
                        dtnew.Rows.Add(dr);
                    }
                }
            }
            if (dsi.Count > 0)
            {
                foreach (var itemconf in dsi)
                {
                    if (quesid == "52")
                    {
                        if (!itemconf.F_ItemName .Contains ("比例"))
                        {
                            string cname = itemconf.F_ItemName;
                            if (itemconf.F_ItemName.Contains("→"))
                            {
                                int index = itemconf.F_ItemName.IndexOf("→");
                                cname = itemconf.F_ItemName.Substring(0, index);
                            }
                            drtotal[cname ] = ColumnSum(dtnew, itemconf.F_ItemName);
                           // drtotal[cname + "比例"] = ColumnSum(dtnew, itemconf.F_ItemName);
                            
                        }
                        else
                        {
                            string cname = itemconf.F_ItemName;
                            if (itemconf.F_ItemName.Contains("→"))
                            {
                                int index = itemconf.F_ItemName.IndexOf("→");
                                cname = itemconf.F_ItemName.Substring(0, index);
                            }
                            drtotal[cname +"比例"] = 0;
                        }
                    }
                    /*else if (quesid == "50")
                    {
                        if (itemconf.F_ItemName != "知晓率" && itemconf.F_ItemName != "参与率")
                        {
                            drtotal[itemconf.F_ItemName] = ColumnSum(dtnew, itemconf.F_ItemName);
                        }
                        else
                        {
                            drtotal[itemconf.F_ItemName] = "";
                        }
                    }
                    else if (quesid == "53")
                    {
                        if (itemconf.F_ItemName != "知晓率")
                        {
                            drtotal[itemconf.F_ItemName] = ColumnSum(dtnew, itemconf.F_ItemName);
                        }
                        else
                        {
                            drtotal[itemconf.F_ItemName] = "";
                        }
                    }
                    else if (quesid == "54")
                    {
                        if (itemconf.F_ItemName != "满意度")
                        {
                            drtotal[itemconf.F_ItemName] = ColumnSum(dtnew, itemconf.F_ItemName);
                        }
                        else
                        {
                            drtotal[itemconf.F_ItemName] = "";
                        }
                    }*/
                    else
                    {
                        string cname = itemconf.F_ItemName;
                        if (itemconf.F_ItemName.Contains("→"))
                        {
                            int index = itemconf.F_ItemName.IndexOf("→");
                            cname = itemconf.F_ItemName.Substring(0, index);
                        }
                        drtotal[cname ] = ColumnSum(dtnew, itemconf.F_ItemName);
                    }
                }
            }
            //if (quesid == "1" || quesid == "50")
            //{ drtotal["总数"] = ColumnSum(dtnew, "总数"); }
            drtotal["总数"] = ColumnSum(dtnew, "总数");

            #region 计算总的知晓率等
            if (quesid == "52")
            {
                decimal aqg = decimal.Parse(drtotal[1].ToString()) + decimal.Parse(drtotal[3].ToString());
                if (decimal.Parse(drtotal["总数"].ToString()) > 0)
                {
                    drtotal["安全感比例"] = Math.Round(aqg / decimal.Parse(drtotal["总数"].ToString()) * 100, 2).ToString() + "%";
                }
                else
                {
                    drtotal["安全感比例"] = 0;
                }

            }
            /*else if (quesid == "50")
            {

                if (decimal.Parse(drtotal["总数"].ToString()) > 0)
                {
                    drtotal["知晓率"] = Math.Round(decimal.Parse(drtotal["知道，没参加"].ToString()) / decimal.Parse(drtotal["总数"].ToString()) * 100, 2).ToString() + "%";
                    drtotal["参与率"] = Math.Round(decimal.Parse(drtotal["参加了"].ToString()) / decimal.Parse(drtotal["总数"].ToString()) * 100, 2).ToString() + "%";
                }

            }
            else if (quesid == "53")
            {
                decimal aqg = decimal.Parse(drtotal["比较了解"].ToString()) + decimal.Parse(drtotal["基本了解"].ToString()) + decimal.Parse(drtotal["知道一些"].ToString());
                if (decimal.Parse(drtotal["总数"].ToString()) > 0)
                {
                    drtotal["知晓率"] = Math.Round(aqg / decimal.Parse(drtotal["总数"].ToString()) * 100, 2).ToString() + "%";
                }
            }
            else if (quesid == "54")
            {
                decimal aqg = decimal.Parse(drtotal["非常满意"].ToString()) + decimal.Parse(drtotal["一般满意"].ToString());
                if (decimal.Parse(drtotal["总数"].ToString()) > 0)
                {
                    drtotal["满意度"] = Math.Round(aqg / decimal.Parse(drtotal["总数"].ToString()) * 100, 2).ToString() + "%";
                }
            }*/
            #endregion

            dtnew.Rows.Add(drtotal);
            dtnew.DefaultView.Sort = "总数 DESC";
            dtnew = dtnew.DefaultView.ToTable();
            if (isdc == 0)
            {
                return Success("获取安全感数据成功", dtnew);
            }
            else
            {
                string sedate = DateTime.Parse (sdate).ToString ("yyyy-MM-dd");
                if (sdate != edate)
                {
                    sedate = DateTime.Parse(sdate).ToString("yyyy-MM-dd") + "---" + DateTime.Parse(edate).ToString("yyyy-MM-dd");
                }

                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcel2("社会治安安全感" + sedate, dtnew, null) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            #endregion

        }
        #endregion

        #region 计算数据列和
        double ColumnSum(DataTable dt, string ColumnName)
        {
            double d = 0;
            foreach (DataRow row in dt.Rows)
            {
                string cname = ColumnName;
                if (ColumnName.Contains("→"))
                {
                    int index = ColumnName.IndexOf("→");
                    cname = ColumnName.Substring(0, index);
                }
                d += double.Parse(row[cname ].ToString());
            }
            return d;
        }
        #endregion

        #region 政法机关执法满意度报表
        /// <summary>
        /// 政法机关执法满意度报表
        /// </summary>
        /// <param name="TaskID">任务id</param>
        /// <param name="quesid">问题id</param>
        /// <param name="countryid">乡镇id</param>
        /// <param name="sdate">开始时间</param>
        /// <param name="edate">结束时间</param>
        /// <returns></returns>
        public ActionResult GetMYD(string TaskID, string sdate, string edate,string countyid,int isdc=0)
        {
            if (string.IsNullOrEmpty(sdate))
            {
                sdate = DateTime.Now.ToString("yyyy-MM") + "-01 00:00:00";
            }
            if (string.IsNullOrEmpty(edate))
            {
                edate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
            }
            #region 新建输出表
            DataTable dtnew = new DataTable();
           
            #endregion

            #region 添加列
            dtnew.Columns.Add("分类" );
            dtnew.Columns.Add("公安局");           
            dtnew.Columns.Add("检察院");           
            dtnew.Columns.Add("法院");           
            dtnew.Columns.Add("司法局");
            dtnew.Rows.Add();
            dtnew.Rows [0][0]="满意数量";
            dtnew.Rows.Add();
            dtnew.Rows[1][0] = "比较满意数量";
            dtnew.Rows.Add();
            dtnew.Rows[2][0] = "不太满意数量";
            dtnew.Rows.Add();
            dtnew.Rows[3][0] = "不满意数量";
            dtnew.Rows.Add();
            dtnew.Rows[4][0] = "不了解数量";
            dtnew.Rows.Add();
            dtnew.Rows[5][0] = "总计";
            dtnew.Rows.Add();
            dtnew.Rows[6][0] = "满意度";
            
            #endregion

            string strtaskid = "";
            if (!string.IsNullOrEmpty(TaskID))
            {
                strtaskid = " and F_TaskID='" + TaskID + "' ";
            }
            
            
            //从数据字典表中获取乡镇信息（JBDW）
            var ds = dvItemBLL.GetModelList(" F_DictionaryFlag='XZQY'");
            if (!string.IsNullOrEmpty(countyid))
            {
                ds= dvItemBLL.GetModelList(" F_DictionaryValueId="+ countyid);
            }
            
            if (ds.Count > 0)
            {
                foreach (var iconf in ds)
                {
                    #region 根据单位和满意度统计数量
                    
                    DataSet dsa = DbHelperSQL.Query("select isnull(SUM(case when F_QIID=262 then 1 else 0 end),0) as MY,isnull(SUM(case when F_QIID=263 then 1 else 0 end),0) as JBMY,isnull(SUM(case when F_QIID=264 then 1 else 0 end),0) as BTMY,isnull(SUM(case when F_QIID=265 then 1 else 0 end),0) as BMY,isnull(SUM(case when F_QIID=266 then 1 else 0 end),0) as BLJ,COUNT (F_Expand3) AS ZJ from T_Call_OutAnswers where F_QID=56 and F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 " + strtaskid + " and ltrim(rtrim(F_Answer))='" + iconf.F_Name + "')");
                    DataTable dta = dsa.Tables[0];
                    if (dta.Rows.Count > 0)
                    {
                        dtnew.Rows[0][1] = int.Parse(dtnew.Rows[0][1].ToString()==""?"0": dtnew.Rows[0][1].ToString()) +int.Parse (dta.Rows[0]["MY"].ToString());
                        dtnew.Rows[1][1] = int.Parse(dtnew.Rows[1][1].ToString() == "" ? "0" : dtnew.Rows[1][1].ToString()) + int.Parse(dta.Rows[0]["JBMY"].ToString()); 
                        dtnew.Rows[2][1] = int.Parse(dtnew.Rows[2][1].ToString() == "" ? "0" : dtnew.Rows[2][1].ToString()) + int.Parse(dta.Rows[0]["BTMY"].ToString()); 
                        dtnew.Rows[3][1] = int.Parse(dtnew.Rows[3][1].ToString() == "" ? "0" : dtnew.Rows[3][1].ToString()) + int.Parse(dta.Rows[0]["BMY"].ToString()); 
                        dtnew.Rows[4][1] = int.Parse(dtnew.Rows[4][1].ToString() == "" ? "0" : dtnew.Rows[4][1].ToString()) + int.Parse(dta.Rows[0]["BLJ"].ToString()); 
                        dtnew.Rows[5][1] = int.Parse(dtnew.Rows[5][1].ToString() == "" ? "0" : dtnew.Rows[5][1].ToString()) + int.Parse(dta.Rows[0]["ZJ"].ToString()); 
                        decimal my = Convert.ToDecimal(dtnew.Rows[0][1]) + Convert.ToDecimal(dtnew.Rows[1][1]);
                        decimal zj = Convert.ToDecimal(dtnew.Rows[5][1]) - Convert.ToDecimal(dtnew.Rows[4][1]);
                        if (zj > 0)
                        {
                            dtnew.Rows[6][1] = Math.Round((my / zj * 100), 2) + "%";
                        }
                        else
                        {
                            dtnew.Rows[6][1] = 0;
                        }
                    }
                    //检察院
                    DataSet dsb = DbHelperSQL.Query("select isnull(SUM(case when F_QIID=276 then 1 else 0 end),0) as MY,isnull(SUM(case when F_QIID=277 then 1 else 0 end),0) as JBMY,isnull(SUM(case when F_QIID=278 then 1 else 0 end),0) as BTMY,isnull(SUM(case when F_QIID=279 then 1 else 0 end),0) as BMY,isnull(SUM(case when F_QIID=280 then 1 else 0 end),0) as BLJ,COUNT (F_Expand3) AS ZJ from T_Call_OutAnswers where F_QID=58 and F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 " + strtaskid + "and ltrim(rtrim(F_Answer))='" + iconf.F_Name + "')");
                    DataTable dtb = dsb.Tables[0];
                    if (dtb.Rows.Count > 0)
                    {
                        dtnew.Rows[0][2] = int.Parse(dtnew.Rows[0][2].ToString() == "" ? "0" : dtnew.Rows[0][2].ToString()) + int.Parse(dtb.Rows[0]["MY"].ToString());
                        dtnew.Rows[1][2] = int.Parse(dtnew.Rows[1][2].ToString() == "" ? "0" : dtnew.Rows[1][2].ToString()) + int.Parse(dtb.Rows[0]["JBMY"].ToString());
                        dtnew.Rows[2][2] = int.Parse(dtnew.Rows[2][2].ToString() == "" ? "0" : dtnew.Rows[2][2].ToString()) + int.Parse(dtb.Rows[0]["BTMY"].ToString());
                        dtnew.Rows[3][2] = int.Parse(dtnew.Rows[3][2].ToString() == "" ? "0" : dtnew.Rows[3][2].ToString()) + int.Parse(dtb.Rows[0]["BMY"].ToString());
                        dtnew.Rows[4][2] = int.Parse(dtnew.Rows[4][2].ToString() == "" ? "0" : dtnew.Rows[4][2].ToString()) + int.Parse(dtb.Rows[0]["BLJ"].ToString());
                        dtnew.Rows[5][2] = int.Parse(dtnew.Rows[5][2].ToString() == "" ? "0" : dtnew.Rows[5][2].ToString()) + int.Parse(dtb.Rows[0]["ZJ"].ToString());
                        decimal my = Convert.ToDecimal(dtnew.Rows[0][2]) + Convert.ToDecimal(dtnew.Rows[1][2]);
                        decimal zj = Convert.ToDecimal(dtnew.Rows[5][2]) - Convert.ToDecimal(dtnew.Rows[4][2]);
                        if (zj > 0)
                        {
                            dtnew.Rows[6][2] = Math.Round((my / zj * 100), 2) + "%";
                        }
                        else
                        {
                            dtnew.Rows[6][2] = 0;
                        }
                    }
                    //法
                    DataSet dsc = DbHelperSQL.Query("select isnull(SUM(case when F_QIID=290 then 1 else 0 end),0) as MY,isnull(SUM(case when F_QIID=291 then 1 else 0 end),0) as JBMY,isnull(SUM(case when F_QIID=292 then 1 else 0 end),0) as BTMY,isnull(SUM(case when F_QIID=293 then 1 else 0 end),0) as BMY,isnull(SUM(case when F_QIID=294 then 1 else 0 end),0) as BLJ,COUNT (F_Expand3) AS ZJ from T_Call_OutAnswers where F_QID=60 and F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 " + strtaskid + " and ltrim(rtrim(F_Answer))='" + iconf.F_Name + "')");
                    DataTable dtc = dsc.Tables[0];
                    if (dtc.Rows.Count > 0)
                    {
                        dtnew.Rows[0][3] = int.Parse(dtnew.Rows[0][3].ToString() == "" ? "0" : dtnew.Rows[0][3].ToString()) + int.Parse(dtc.Rows[0]["MY"].ToString());
                        dtnew.Rows[1][3] = int.Parse(dtnew.Rows[1][3].ToString() == "" ? "0" : dtnew.Rows[1][3].ToString()) + int.Parse(dtc.Rows[0]["JBMY"].ToString());
                        dtnew.Rows[2][3] = int.Parse(dtnew.Rows[2][3].ToString() == "" ? "0" : dtnew.Rows[2][3].ToString()) + int.Parse(dtc.Rows[0]["BTMY"].ToString());
                        dtnew.Rows[3][3] = int.Parse(dtnew.Rows[3][3].ToString() == "" ? "0" : dtnew.Rows[3][3].ToString()) + int.Parse(dtc.Rows[0]["BMY"].ToString());
                        dtnew.Rows[4][3] = int.Parse(dtnew.Rows[4][3].ToString() == "" ? "0" : dtnew.Rows[4][3].ToString()) + int.Parse(dtc.Rows[0]["BLJ"].ToString());
                        dtnew.Rows[5][3] = int.Parse(dtnew.Rows[5][3].ToString() == "" ? "0" : dtnew.Rows[5][3].ToString()) + int.Parse(dtc.Rows[0]["ZJ"].ToString());
                        decimal my = Convert.ToDecimal(dtnew.Rows[0][3]) + Convert.ToDecimal(dtnew.Rows[1][3]);
                        decimal zj = Convert.ToDecimal(dtnew.Rows[5][3]) - Convert.ToDecimal(dtnew.Rows[4][3]);
                        if (zj > 0)
                        {
                            dtnew.Rows[6][3] = Math.Round((my / zj * 100), 2) + "%";
                        }
                        else
                        {
                            dtnew.Rows[6][3] = 0;
                        }
                    }
                    //司法局
                    DataSet dsd = DbHelperSQL.Query("select isnull(SUM(case when F_QIID=304 then 1 else 0 end),0) as MY,isnull(SUM(case when F_QIID=305 then 1 else 0 end),0) as JBMY,isnull(SUM(case when F_QIID=306 then 1 else 0 end),0) as BTMY,isnull(SUM(case when F_QIID=307 then 1 else 0 end),0) as BMY,isnull(SUM(case when F_QIID=308 then 1 else 0 end),0) as BLJ,COUNT (F_Expand3) AS ZJ from T_Call_OutAnswers where F_QID=62 and F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 " + strtaskid + "and ltrim(rtrim(F_Answer))='" + iconf.F_Name + "')");
                    DataTable dtd = dsd.Tables[0];
                    if (dtd.Rows.Count > 0)
                    {
                        dtnew.Rows[0][4] = int.Parse(dtnew.Rows[0][4].ToString() == "" ? "0" : dtnew.Rows[0][4].ToString()) + int.Parse(dtd.Rows[0]["MY"].ToString());
                        dtnew.Rows[1][4] = int.Parse(dtnew.Rows[1][4].ToString() == "" ? "0" : dtnew.Rows[1][4].ToString()) + int.Parse(dtd.Rows[0]["JBMY"].ToString());
                        dtnew.Rows[2][4] = int.Parse(dtnew.Rows[2][4].ToString() == "" ? "0" : dtnew.Rows[2][4].ToString()) + int.Parse(dtd.Rows[0]["BTMY"].ToString());
                        dtnew.Rows[3][4] = int.Parse(dtnew.Rows[3][4].ToString() == "" ? "0" : dtnew.Rows[3][4].ToString()) + int.Parse(dtd.Rows[0]["BMY"].ToString());
                        dtnew.Rows[4][4] = int.Parse(dtnew.Rows[4][4].ToString() == "" ? "0" : dtnew.Rows[4][4].ToString()) + int.Parse(dtd.Rows[0]["BLJ"].ToString());
                        dtnew.Rows[5][4] = int.Parse(dtnew.Rows[5][4].ToString() == "" ? "0" : dtnew.Rows[5][4].ToString()) + int.Parse(dtd.Rows[0]["ZJ"].ToString());
                        decimal my = Convert.ToDecimal(dtnew.Rows[0][4]) + Convert.ToDecimal(dtnew.Rows[1][4]);
                        decimal zj = Convert.ToDecimal(dtnew.Rows[5][4]) - Convert.ToDecimal(dtnew.Rows[4][4]);
                        if (zj > 0)
                        {
                            dtnew.Rows[6][4] = Math.Round((my / zj * 100), 2) + "%";
                        }
                        else
                        {
                            dtnew.Rows[6][4] = 0;
                        }
                    }
                    #endregion
                    
                }
            }
            if(isdc ==0)
            { 
            return Success("获取政法机关执法满意度数据成功", dtnew);
            }
            else
            {
                string sedate = DateTime.Parse(sdate).ToString("yyyy-MM-dd");
                if (sdate != edate)
                {
                    sedate = DateTime.Parse(sdate).ToString("yyyy-MM-dd") + "---" + DateTime.Parse(edate).ToString("yyyy-MM-dd");
                }

                NPOIHelper npoi = new NPOIHelper();
                if (npoi.ExportToExcel2("政法机关执法满意度" + sedate, dtnew, null) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }

        }

        public ActionResult GetMYDB(string TaskID, string sdate, string edate, string countyid)
        {
            if (string.IsNullOrEmpty(sdate))
            {
                sdate = DateTime.Now.ToString("yyyy-MM") + "-01 00:00:00";
            }
            if (string.IsNullOrEmpty(edate))
            {
                edate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
            }
            #region 新建输出表
            DataTable dtnew = new DataTable();

            #endregion

            #region 添加列
            dtnew.Columns.Add("分类");
            dtnew.Columns.Add("公安局");
            dtnew.Columns.Add("检察院");
            dtnew.Columns.Add("法院");
            dtnew.Columns.Add("司法局");
            dtnew.Rows.Add();
            dtnew.Rows[0][0] = "满意数量";
            dtnew.Rows.Add();
            dtnew.Rows[1][0] = "比较满意数量";
            dtnew.Rows.Add();
            dtnew.Rows[2][0] = "不太满意数量";
            dtnew.Rows.Add();
            dtnew.Rows[3][0] = "不满意数量";
            dtnew.Rows.Add();
            dtnew.Rows[4][0] = "不了解数量";
            dtnew.Rows.Add();
            dtnew.Rows[5][0] = "总计";
            dtnew.Rows.Add();
            dtnew.Rows[6][0] = "满意度";

            #endregion

            string strtaskid = "";
            if (!string.IsNullOrEmpty(TaskID))
            {
                strtaskid = " and F_TaskID='" + TaskID + "' ";
            }


            //从数据字典表中获取乡镇信息（JBDW）
            var ds = dvItemBLL.GetModelList(" F_DictionaryFlag='XZQY'");
            if (!string.IsNullOrEmpty(countyid))
            {
                ds = dvItemBLL.GetModelList(" F_DictionaryValueId=" + countyid);
            }

            if (ds.Count > 0)
            {
                foreach (var iconf in ds)
                {
                    #region 根据单位和满意度统计数量

                    DataSet dsa = DbHelperSQL.Query("select isnull(SUM(case when F_QIID=262 then 1 else 0 end),0) as MY,isnull(SUM(case when F_QIID=263 then 1 else 0 end),0) as JBMY,isnull(SUM(case when F_QIID=264 then 1 else 0 end),0) as BTMY,isnull(SUM(case when F_QIID=265 then 1 else 0 end),0) as BMY,isnull(SUM(case when F_QIID=266 then 1 else 0 end),0) as BLJ,COUNT (F_Expand3) AS ZJ from T_Call_OutAnswers where F_QID=56 and F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 " + strtaskid + " and ltrim(rtrim(F_Answer))='" + iconf.F_Name + "')");
                    DataTable dta = dsa.Tables[0];
                    if (dta.Rows.Count > 0)
                    {
                        dtnew.Rows[0][1] = dta.Rows[0]["MY"].ToString();
                        dtnew.Rows[1][1] = dta.Rows[0]["JBMY"].ToString();
                        dtnew.Rows[2][1] = dta.Rows[0]["BTMY"].ToString();
                        dtnew.Rows[3][1] = dta.Rows[0]["BMY"].ToString();
                        dtnew.Rows[4][1] = dta.Rows[0]["BLJ"].ToString();
                        dtnew.Rows[5][1] = dta.Rows[0]["ZJ"].ToString();
                        decimal my = Convert.ToDecimal(dta.Rows[0]["MY"]) + Convert.ToDecimal(dta.Rows[0]["JBMY"]);
                        decimal zj = Convert.ToDecimal(dta.Rows[0]["ZJ"]) - Convert.ToDecimal(dta.Rows[0]["BLJ"]);
                        if (zj > 0)
                        {
                            dtnew.Rows[6][1] = Math.Round((my / zj * 100), 2) + "%";
                        }
                        else
                        {
                            dtnew.Rows[6][1] = 0;
                        }
                    }
                    //检察院
                    DataSet dsb = DbHelperSQL.Query("select isnull(SUM(case when F_QIID=276 then 1 else 0 end),0) as MY,isnull(SUM(case when F_QIID=277 then 1 else 0 end),0) as JBMY,isnull(SUM(case when F_QIID=278 then 1 else 0 end),0) as BTMY,isnull(SUM(case when F_QIID=279 then 1 else 0 end),0) as BMY,isnull(SUM(case when F_QIID=280 then 1 else 0 end),0) as BLJ,COUNT (F_Expand3) AS ZJ from T_Call_OutAnswers where F_QID=58 and F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 " + strtaskid + "and ltrim(rtrim(F_Answer))='" + iconf.F_Name + "')");
                    DataTable dtb = dsb.Tables[0];
                    if (dtb.Rows.Count > 0)
                    {
                        dtnew.Rows[0][2] = dtb.Rows[0]["MY"].ToString();
                        dtnew.Rows[1][2] = dtb.Rows[0]["JBMY"].ToString();
                        dtnew.Rows[2][2] = dtb.Rows[0]["BTMY"].ToString();
                        dtnew.Rows[3][2] = dtb.Rows[0]["BMY"].ToString();
                        dtnew.Rows[4][2] = dtb.Rows[0]["BLJ"].ToString();
                        dtnew.Rows[5][2] = dtb.Rows[0]["ZJ"].ToString();
                        decimal my = Convert.ToDecimal(dtb.Rows[0]["MY"]) + Convert.ToDecimal(dtb.Rows[0]["JBMY"]);
                        decimal zj = Convert.ToDecimal(dtb.Rows[0]["ZJ"]) - Convert.ToDecimal(dtb.Rows[0]["BLJ"]);
                        if (zj > 0)
                        {
                            dtnew.Rows[6][2] = Math.Round((my / zj * 100), 2) + "%";
                        }
                        else
                        {
                            dtnew.Rows[6][2] = 0;
                        }
                    }
                    //法
                    DataSet dsc = DbHelperSQL.Query("select isnull(SUM(case when F_QIID=290 then 1 else 0 end),0) as MY,isnull(SUM(case when F_QIID=291 then 1 else 0 end),0) as JBMY,isnull(SUM(case when F_QIID=292 then 1 else 0 end),0) as BTMY,isnull(SUM(case when F_QIID=293 then 1 else 0 end),0) as BMY,isnull(SUM(case when F_QIID=294 then 1 else 0 end),0) as BLJ,COUNT (F_Expand3) AS ZJ from T_Call_OutAnswers where F_QID=60 and F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 " + strtaskid + " and ltrim(rtrim(F_Answer))='" + iconf.F_Name + "')");
                    DataTable dtc = dsc.Tables[0];
                    if (dtc.Rows.Count > 0)
                    {
                        dtnew.Rows[0][3] = dtc.Rows[0]["MY"].ToString();
                        dtnew.Rows[1][3] = dtc.Rows[0]["JBMY"].ToString();
                        dtnew.Rows[2][3] = dtc.Rows[0]["BTMY"].ToString();
                        dtnew.Rows[3][3] = dtc.Rows[0]["BMY"].ToString();
                        dtnew.Rows[4][3] = dtc.Rows[0]["BLJ"].ToString();
                        dtnew.Rows[5][3] = dtc.Rows[0]["ZJ"].ToString();
                        decimal my = Convert.ToDecimal(dtc.Rows[0]["MY"]) + Convert.ToDecimal(dtc.Rows[0]["JBMY"]);
                        decimal zj = Convert.ToDecimal(dtc.Rows[0]["ZJ"]) - Convert.ToDecimal(dtc.Rows[0]["BLJ"]);
                        if (zj > 0)
                        {
                            dtnew.Rows[6][3] = Math.Round((my / zj * 100), 2) + "%";
                        }
                        else
                        {
                            dtnew.Rows[6][3] = 0;
                        }
                    }
                    //司法局
                    DataSet dsd = DbHelperSQL.Query("select isnull(SUM(case when F_QIID=304 then 1 else 0 end),0) as MY,isnull(SUM(case when F_QIID=305 then 1 else 0 end),0) as JBMY,isnull(SUM(case when F_QIID=306 then 1 else 0 end),0) as BTMY,isnull(SUM(case when F_QIID=307 then 1 else 0 end),0) as BMY,isnull(SUM(case when F_QIID=308 then 1 else 0 end),0) as BLJ,COUNT (F_Expand3) AS ZJ from T_Call_OutAnswers where F_QID=62 and F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 " + strtaskid + "and ltrim(rtrim(F_Answer))='" + iconf.F_Name + "')");
                    DataTable dtd = dsd.Tables[0];
                    if (dtd.Rows.Count > 0)
                    {
                        dtnew.Rows[0][4] = dtd.Rows[0]["MY"].ToString();
                        dtnew.Rows[1][4] = dtd.Rows[0]["JBMY"].ToString();
                        dtnew.Rows[2][4] = dtd.Rows[0]["BTMY"].ToString();
                        dtnew.Rows[3][4] = dtd.Rows[0]["BMY"].ToString();
                        dtnew.Rows[4][4] = dtd.Rows[0]["BLJ"].ToString();
                        dtnew.Rows[5][4] = dtd.Rows[0]["ZJ"].ToString();
                        decimal my = Convert.ToDecimal(dtd.Rows[0]["MY"]) + Convert.ToDecimal(dtd.Rows[0]["JBMY"]);
                        decimal zj = Convert.ToDecimal(dtd.Rows[0]["ZJ"]) - Convert.ToDecimal(dtd.Rows[0]["BLJ"]);
                        if (zj > 0)
                        {
                            dtnew.Rows[6][4] = Math.Round((my / zj * 100), 2) + "%";
                        }
                        else
                        {
                            dtnew.Rows[6][4] = 0;
                        }
                    }
                    #endregion

                }
            }

            return Success("获取政法机关执法满意度数据成功", dtnew);

        }

        public ActionResult GetMYDOLD(string TaskID, string sdate, string edate, string countyid)
        {
            if (string.IsNullOrEmpty(sdate))
            {
                sdate = DateTime.Now.ToString("yyyy-MM") + "-01 00:00:00";
            }
            if (string.IsNullOrEmpty(edate))
            {
                edate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
            }
            #region 新建输出表
            DataTable dtnew = new DataTable();
            //for (int i = 0; i < 29; i++)
            //{
            //    dtnew.Columns.Add("Column" + i.ToString());
            //}
            #endregion

            #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("公安局满意度");
            dtnew.Columns.Add("检察院满意数量");
            dtnew.Columns.Add("检察院比较满意数量");
            dtnew.Columns.Add("检察院不太满意数量");
            dtnew.Columns.Add("检察院不满意数量");
            dtnew.Columns.Add("检察院不了解数量");
            dtnew.Columns.Add("检察院总计");
            dtnew.Columns.Add("检察院满意度");
            dtnew.Columns.Add("法院满意数量");
            dtnew.Columns.Add("法院比较满意数量");
            dtnew.Columns.Add("法院不太满意数量");
            dtnew.Columns.Add("法院不满意数量");
            dtnew.Columns.Add("法院不了解数量");
            dtnew.Columns.Add("法院总计");
            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

            string strtaskid = "";
            if (!string.IsNullOrEmpty(TaskID))
            {
                strtaskid = " and F_TaskID='" + TaskID + "' ";
            }

            DataRow drtotal = dtnew.NewRow();
            drtotal["乡镇"] = "合计";

            //从数据字典表中获取乡镇信息（JBDW）
            var ds = dvItemBLL.GetModelList(" F_DictionaryFlag='XZQY'");
            if (!string.IsNullOrEmpty(countyid))
            {
                ds = dvItemBLL.GetModelList(" F_DictionaryValueId=" + countyid);
            }
            //var dsc = questionItemBLL.GetModelList(" F_QuestionId='34'");//获取全部乡镇
            if (ds.Count > 0)
            {
                foreach (var iconf in ds)
                {
                    #region 根据单位和满意度统计数量
                    DataRow dr = dtnew.NewRow();
                    dr["乡镇"] = iconf.F_Name;
                    //公
                    DataSet dsa = DbHelperSQL.Query("select isnull(SUM(case when F_QIID=262 then 1 else 0 end),0) as MY,isnull(SUM(case when F_QIID=263 then 1 else 0 end),0) as JBMY,isnull(SUM(case when F_QIID=264 then 1 else 0 end),0) as BTMY,isnull(SUM(case when F_QIID=265 then 1 else 0 end),0) as BMY,isnull(SUM(case when F_QIID=266 then 1 else 0 end),0) as BLJ,COUNT (F_Expand3) AS ZJ from T_Call_OutAnswers where F_QID=56 and F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 " + strtaskid + " and ltrim(rtrim(F_Answer))='" + iconf.F_Name + "')");
                    DataTable dta = dsa.Tables[0];
                    if (dta.Rows.Count > 0)
                    {
                        dr["公安局满意数量"] = dta.Rows[0]["MY"].ToString();
                        dr["公安局比较满意数量"] = dta.Rows[0]["JBMY"].ToString();
                        dr["公安局不太满意数量"] = dta.Rows[0]["BTMY"].ToString();
                        dr["公安局不满意数量"] = dta.Rows[0]["BMY"].ToString();
                        dr["公安局不了解数量"] = dta.Rows[0]["BLJ"].ToString();
                        dr["公安局总计"] = dta.Rows[0]["ZJ"].ToString();
                        decimal my = Convert.ToDecimal(dta.Rows[0]["MY"]) + Convert.ToDecimal(dta.Rows[0]["JBMY"]);
                        decimal zj = Convert.ToDecimal(dta.Rows[0]["ZJ"]) - Convert.ToDecimal(dta.Rows[0]["BLJ"]);
                        if (zj > 0)
                        {
                            dr["公安局满意度"] = Math.Round((my / zj * 100), 2) + "%";
                        }
                        else
                        {
                            dr["公安局满意度"] = 0;
                        }
                    }
                    //检察院
                    DataSet dsb = DbHelperSQL.Query("select isnull(SUM(case when F_QIID=276 then 1 else 0 end),0) as MY,isnull(SUM(case when F_QIID=277 then 1 else 0 end),0) as JBMY,isnull(SUM(case when F_QIID=278 then 1 else 0 end),0) as BTMY,isnull(SUM(case when F_QIID=279 then 1 else 0 end),0) as BMY,isnull(SUM(case when F_QIID=280 then 1 else 0 end),0) as BLJ,COUNT (F_Expand3) AS ZJ from T_Call_OutAnswers where F_QID=58 and F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 " + strtaskid + "and ltrim(rtrim(F_Answer))='" + iconf.F_Name + "')");
                    DataTable dtb = dsb.Tables[0];
                    if (dtb.Rows.Count > 0)
                    {
                        dr["检察院满意数量"] = dtb.Rows[0]["MY"].ToString();
                        dr["检察院比较满意数量"] = dtb.Rows[0]["JBMY"].ToString();
                        dr["检察院不太满意数量"] = dtb.Rows[0]["BTMY"].ToString();
                        dr["检察院不满意数量"] = dtb.Rows[0]["BMY"].ToString();
                        dr["检察院不了解数量"] = dtb.Rows[0]["BLJ"].ToString();
                        dr["检察院总计"] = dtb.Rows[0]["ZJ"].ToString();
                        decimal my = Convert.ToDecimal(dtb.Rows[0]["MY"]) + Convert.ToDecimal(dtb.Rows[0]["JBMY"]);
                        decimal zj = Convert.ToDecimal(dtb.Rows[0]["ZJ"]) - Convert.ToDecimal(dtb.Rows[0]["BLJ"]);
                        if (zj > 0)
                        {
                            dr["检察院满意度"] = Math.Round((my / zj * 100), 2) + "%";
                        }
                        else
                        {
                            dr["检察院满意度"] = 0;
                        }
                    }
                    //法
                    DataSet dsc = DbHelperSQL.Query("select isnull(SUM(case when F_QIID=290 then 1 else 0 end),0) as MY,isnull(SUM(case when F_QIID=291 then 1 else 0 end),0) as JBMY,isnull(SUM(case when F_QIID=292 then 1 else 0 end),0) as BTMY,isnull(SUM(case when F_QIID=293 then 1 else 0 end),0) as BMY,isnull(SUM(case when F_QIID=294 then 1 else 0 end),0) as BLJ,COUNT (F_Expand3) AS ZJ from T_Call_OutAnswers where F_QID=60 and F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 " + strtaskid + " and ltrim(rtrim(F_Answer))='" + iconf.F_Name + "')");
                    DataTable dtc = dsc.Tables[0];
                    if (dtc.Rows.Count > 0)
                    {
                        dr["法院满意数量"] = dtc.Rows[0]["MY"].ToString();
                        dr["法院比较满意数量"] = dtc.Rows[0]["JBMY"].ToString();
                        dr["法院不太满意数量"] = dtc.Rows[0]["BTMY"].ToString();
                        dr["法院不满意数量"] = dtc.Rows[0]["BMY"].ToString();
                        dr["法院不了解数量"] = dtc.Rows[0]["BLJ"].ToString();
                        dr["法院总计"] = dtc.Rows[0]["ZJ"].ToString();
                        decimal my = Convert.ToDecimal(dtc.Rows[0]["MY"]) + Convert.ToDecimal(dtc.Rows[0]["JBMY"]);
                        decimal zj = Convert.ToDecimal(dtc.Rows[0]["ZJ"]) - Convert.ToDecimal(dtc.Rows[0]["BLJ"]);
                        if (zj > 0)
                        {
                            dr["法院满意度"] = Math.Round((my / zj * 100), 2) + "%";
                        }
                        else
                        {
                            dr["法院满意度"] = 0;
                        }
                    }
                    //司法局
                    DataSet dsd = DbHelperSQL.Query("select isnull(SUM(case when F_QIID=304 then 1 else 0 end),0) as MY,isnull(SUM(case when F_QIID=305 then 1 else 0 end),0) as JBMY,isnull(SUM(case when F_QIID=306 then 1 else 0 end),0) as BTMY,isnull(SUM(case when F_QIID=307 then 1 else 0 end),0) as BMY,isnull(SUM(case when F_QIID=308 then 1 else 0 end),0) as BLJ,COUNT (F_Expand3) AS ZJ from T_Call_OutAnswers where F_QID=62 and F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 " + strtaskid + "and ltrim(rtrim(F_Answer))='" + iconf.F_Name + "')");
                    DataTable dtd = dsd.Tables[0];
                    if (dtd.Rows.Count > 0)
                    {
                        dr["司法局满意数量"] = dtd.Rows[0]["MY"].ToString();
                        dr["司法局比较满意数量"] = dtd.Rows[0]["JBMY"].ToString();
                        dr["司法局不太满意数量"] = dtd.Rows[0]["BTMY"].ToString();
                        dr["司法局不满意数量"] = dtd.Rows[0]["BMY"].ToString();
                        dr["司法局不了解数量"] = dtd.Rows[0]["BLJ"].ToString();
                        dr["司法局总计"] = dtd.Rows[0]["ZJ"].ToString();
                        decimal my = Convert.ToDecimal(dtd.Rows[0]["MY"]) + Convert.ToDecimal(dtd.Rows[0]["JBMY"]);
                        decimal zj = Convert.ToDecimal(dtd.Rows[0]["ZJ"]) - Convert.ToDecimal(dtd.Rows[0]["BLJ"]);
                        if (zj > 0)
                        {
                            dr["司法局满意度"] = Math.Round((my / zj * 100), 2) + "%";
                        }
                        else
                        {
                            dr["司法局满意度"] = 0;
                        }
                    }
                    #endregion
                    dtnew.Rows.Add(dr);
                }
            }
            foreach (DataColumn item in dtnew.Columns)
            {
                //drDic.Add(item.ColumnName, dr[item.ColumnName]);
                if (item.ColumnName != "乡镇" && item.ColumnName != "公安局满意度" && item.ColumnName != "检察院满意度" && item.ColumnName != "法院满意度" && item.ColumnName != "司法局满意度")
                {
                    drtotal[item.ColumnName] = ColumnSum(dtnew, item.ColumnName);

                }
            }
            decimal gamy = decimal.Parse(drtotal[1].ToString()) + decimal.Parse(drtotal[2].ToString());
            decimal gatotal = decimal.Parse(drtotal[1].ToString()) + decimal.Parse(drtotal[2].ToString()) + decimal.Parse(drtotal[3].ToString()) + decimal.Parse(drtotal[4].ToString());
            if (gatotal > 0)
            {
                drtotal["公安局满意度"] = Math.Round((gamy / gatotal * 100), 2) + "%";
            }
            else
            {
                drtotal["公安局满意度"] = 0;
            }
            decimal jcymy = decimal.Parse(drtotal[8].ToString()) + decimal.Parse(drtotal[9].ToString());
            decimal jcytotal = decimal.Parse(drtotal[8].ToString()) + decimal.Parse(drtotal[9].ToString()) + decimal.Parse(drtotal[10].ToString()) + decimal.Parse(drtotal[11].ToString());
            if (jcytotal > 0)
            {
                drtotal["检察院满意度"] = Math.Round((jcymy / jcytotal * 100), 2) + "%";
            }
            else
            {
                drtotal["检察院满意度"] = 0;
            }
            decimal fymy = decimal.Parse(drtotal[15].ToString()) + decimal.Parse(drtotal[16].ToString());
            decimal fytotal = decimal.Parse(drtotal[15].ToString()) + decimal.Parse(drtotal[16].ToString()) + decimal.Parse(drtotal[17].ToString()) + decimal.Parse(drtotal[18].ToString());
            if (fytotal > 0)
            {
                drtotal["法院满意度"] = Math.Round((fymy / fytotal * 100), 2) + "%";
            }
            else
            {
                drtotal["法院满意度"] = 0;
            }
            decimal sfjmy = decimal.Parse(drtotal[22].ToString()) + decimal.Parse(drtotal[23].ToString());
            decimal sfjtotal = decimal.Parse(drtotal[22].ToString()) + decimal.Parse(drtotal[23].ToString()) + decimal.Parse(drtotal[24].ToString()) + decimal.Parse(drtotal[25].ToString());
            if (sfjtotal > 0)
            {
                drtotal["司法局满意度"] = Math.Round((sfjmy / sfjtotal * 100), 2) + "%";
            }
            else
            {
                drtotal["司法局满意度"] = 0;
            }

            dtnew.Rows.Add(drtotal);

            return Success("获取政法机关执法满意度数据成功", dtnew);

        }
        #endregion

        #region 政法机关执法满意度报表--列名没有改
        /// <summary>
        /// 政法机关执法满意度报表
        /// </summary>
        /// <param name="TaskID">任务id</param>
        /// <param name="quesid">问题id</param>
        /// <param name="countryid">乡镇id</param>
        /// <param name="sdate">开始时间</param>
        /// <param name="edate">结束时间</param>
        /// <returns></returns>
        public ActionResult GetMYDBAK(string TaskID, string sdate, string edate, string countyid)
        {
            if (string.IsNullOrEmpty(sdate))
            {
                sdate = DateTime.Now.ToString("yyyy-MM") + "-01 00:00:00";
            }
            if (string.IsNullOrEmpty(edate))
            {
                edate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
            }
            #region 新建输出表
            DataTable dtnew = new DataTable();
            for (int i = 0; i < 29; i++)
            {
                dtnew.Columns.Add("Column" + i.ToString());
            }
            #endregion

            string strtaskid = "";
            if (!string.IsNullOrEmpty(TaskID))
            {
                strtaskid = " and F_TaskID='" + TaskID + "' ";
            }

            DataRow drtotal = dtnew.NewRow();
            drtotal["Column0"] = "合计";

            //从数据字典表中获取乡镇信息（JBDW）
            var ds = dvItemBLL.GetModelList(" F_DictionaryFlag='XZQY'");
            if (!string.IsNullOrEmpty(countyid))
            {
                ds = dvItemBLL.GetModelList(" F_DictionaryValueId=" + countyid);
            }
            //var dsc = questionItemBLL.GetModelList(" F_QuestionId='34'");//获取全部乡镇
            if (ds.Count > 0)
            {
                foreach (var iconf in ds)
                {
                    #region 根据单位和满意度统计数量
                    DataRow dr = dtnew.NewRow();
                    dr["Column0"] = iconf.F_Name;
                    //公
                    DataSet dsa = DbHelperSQL.Query("select isnull(SUM(case when F_QIID=262 then 1 else 0 end),0) as MY,isnull(SUM(case when F_QIID=263 then 1 else 0 end),0) as JBMY,isnull(SUM(case when F_QIID=264 then 1 else 0 end),0) as BTMY,isnull(SUM(case when F_QIID=265 then 1 else 0 end),0) as BMY,isnull(SUM(case when F_QIID=266 then 1 else 0 end),0) as BLJ,COUNT (F_Expand3) AS ZJ from T_Call_OutAnswers where F_QID=56 and F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 " + strtaskid + " and ltrim(rtrim(F_Answer))='" + iconf.F_Name + "')");
                    DataTable dta = dsa.Tables[0];
                    if (dta.Rows.Count > 0)
                    {
                        dr["Column1"] = dta.Rows[0]["MY"].ToString();
                        dr["Column2"] = dta.Rows[0]["JBMY"].ToString();
                        dr["Column3"] = dta.Rows[0]["BTMY"].ToString();
                        dr["Column4"] = dta.Rows[0]["BMY"].ToString();
                        dr["Column5"] = dta.Rows[0]["BLJ"].ToString();
                        dr["Column6"] = dta.Rows[0]["ZJ"].ToString();
                        decimal my = Convert.ToDecimal(dta.Rows[0]["MY"]) + Convert.ToDecimal(dta.Rows[0]["JBMY"]);
                        decimal zj = Convert.ToDecimal(dta.Rows[0]["ZJ"]) - Convert.ToDecimal(dta.Rows[0]["BLJ"]);
                        if (zj > 0)
                        {
                            dr["Column7"] = Math.Round((my / zj * 100), 2) + "%";
                        }
                        else
                        {
                            dr["Column7"] = 0;
                        }
                    }
                    //检察院
                    DataSet dsb = DbHelperSQL.Query("select isnull(SUM(case when F_QIID=276 then 1 else 0 end),0) as MY,isnull(SUM(case when F_QIID=277 then 1 else 0 end),0) as JBMY,isnull(SUM(case when F_QIID=278 then 1 else 0 end),0) as BTMY,isnull(SUM(case when F_QIID=279 then 1 else 0 end),0) as BMY,isnull(SUM(case when F_QIID=280 then 1 else 0 end),0) as BLJ,COUNT (F_Expand3) AS ZJ from T_Call_OutAnswers where F_QID=58 and F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 " + strtaskid + "and ltrim(rtrim(F_Answer))='" + iconf.F_Name + "')");
                    DataTable dtb = dsb.Tables[0];
                    if (dtb.Rows.Count > 0)
                    {
                        dr["Column8"] = dtb.Rows[0]["MY"].ToString();
                        dr["Column9"] = dtb.Rows[0]["JBMY"].ToString();
                        dr["Column10"] = dtb.Rows[0]["BTMY"].ToString();
                        dr["Column11"] = dtb.Rows[0]["BMY"].ToString();
                        dr["Column12"] = dtb.Rows[0]["BLJ"].ToString();
                        dr["Column13"] = dtb.Rows[0]["ZJ"].ToString();
                        decimal my = Convert.ToDecimal(dtb.Rows[0]["MY"]) + Convert.ToDecimal(dtb.Rows[0]["JBMY"]);
                        decimal zj = Convert.ToDecimal(dtb.Rows[0]["ZJ"]) - Convert.ToDecimal(dtb.Rows[0]["BLJ"]);
                        if (zj > 0)
                        {
                            dr["Column14"] = Math.Round((my / zj * 100), 2) + "%";
                        }
                        else
                        {
                            dr["Column14"] = 0;
                        }
                    }
                    //法
                    DataSet dsc = DbHelperSQL.Query("select isnull(SUM(case when F_QIID=290 then 1 else 0 end),0) as MY,isnull(SUM(case when F_QIID=291 then 1 else 0 end),0) as JBMY,isnull(SUM(case when F_QIID=292 then 1 else 0 end),0) as BTMY,isnull(SUM(case when F_QIID=293 then 1 else 0 end),0) as BMY,isnull(SUM(case when F_QIID=294 then 1 else 0 end),0) as BLJ,COUNT (F_Expand3) AS ZJ from T_Call_OutAnswers where F_QID=60 and F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 " + strtaskid + " and ltrim(rtrim(F_Answer))='" + iconf.F_Name + "')");
                    DataTable dtc = dsc.Tables[0];
                    if (dtc.Rows.Count > 0)
                    {
                        dr["Column15"] = dtc.Rows[0]["MY"].ToString();
                        dr["Column16"] = dtc.Rows[0]["JBMY"].ToString();
                        dr["Column17"] = dtc.Rows[0]["BTMY"].ToString();
                        dr["Column18"] = dtc.Rows[0]["BMY"].ToString();
                        dr["Column19"] = dtc.Rows[0]["BLJ"].ToString();
                        dr["Column20"] = dtc.Rows[0]["ZJ"].ToString();
                        decimal my = Convert.ToDecimal(dtc.Rows[0]["MY"]) + Convert.ToDecimal(dtc.Rows[0]["JBMY"]);
                        decimal zj = Convert.ToDecimal(dtc.Rows[0]["ZJ"]) - Convert.ToDecimal(dtc.Rows[0]["BLJ"]);
                        if (zj > 0)
                        {
                            dr["Column21"] = Math.Round((my / zj * 100), 2) + "%";
                        }
                        else
                        {
                            dr["Column21"] = 0;
                        }
                    }
                    //司法局
                    DataSet dsd = DbHelperSQL.Query("select isnull(SUM(case when F_QIID=304 then 1 else 0 end),0) as MY,isnull(SUM(case when F_QIID=305 then 1 else 0 end),0) as JBMY,isnull(SUM(case when F_QIID=306 then 1 else 0 end),0) as BTMY,isnull(SUM(case when F_QIID=307 then 1 else 0 end),0) as BMY,isnull(SUM(case when F_QIID=308 then 1 else 0 end),0) as BLJ,COUNT (F_Expand3) AS ZJ from T_Call_OutAnswers where F_QID=62 and F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 " + strtaskid + "and ltrim(rtrim(F_Answer))='" + iconf.F_Name + "')");
                    DataTable dtd = dsd.Tables[0];
                    if (dtd.Rows.Count > 0)
                    {
                        dr["Column22"] = dtd.Rows[0]["MY"].ToString();
                        dr["Column23"] = dtd.Rows[0]["JBMY"].ToString();
                        dr["Column24"] = dtd.Rows[0]["BTMY"].ToString();
                        dr["Column25"] = dtd.Rows[0]["BMY"].ToString();
                        dr["Column26"] = dtd.Rows[0]["BLJ"].ToString();
                        dr["Column27"] = dtd.Rows[0]["ZJ"].ToString();
                        decimal my = Convert.ToDecimal(dtd.Rows[0]["MY"]) + Convert.ToDecimal(dtd.Rows[0]["JBMY"]);
                        decimal zj = Convert.ToDecimal(dtd.Rows[0]["ZJ"]) - Convert.ToDecimal(dtd.Rows[0]["BLJ"]);
                        if (zj > 0)
                        {
                            dr["Column28"] = Math.Round((my / zj * 100), 2) + "%";
                        }
                        else
                        {
                            dr["Column28"] = 0;
                        }
                    }
                    #endregion
                    dtnew.Rows.Add(dr);
                }
            }

            for (int i = 1; i < 29; i++)
            {
                if (i != 7 && i != 14 && i != 21 && i != 28)
                {
                    drtotal["Column" + i.ToString()] = ColumnSum(dtnew, "Column" + i.ToString());
                }
            }
            dtnew.Rows.Add(drtotal);

            return Success("获取政法机关执法满意度数据成功", dtnew);

        }
        #endregion

        #region 根据问题统计数据
        /// <summary>
        /// 
        /// </summary>
        /// <param name="TaskID">任务id</param>
        /// <param name="quesid">问题id</param>
        /// <param name="countryid">乡镇id</param>
        /// <param name="sdate">开始时间</param>
        /// <param name="edate">结束时间</param>
        /// <returns></returns>
        public ActionResult GetData(string TaskID, string ques, string countyid, string sdate, string edate)
        {
            if (string.IsNullOrEmpty(sdate))
            {
                sdate = DateTime.Now.ToString("yyyy-MM") + "-01 00:00:00";
            }
            if (string.IsNullOrEmpty(edate))
            {
                edate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
            }


            //countyid = "515";
            string quesid = "67";
            if (ques == "社会治安")
            {
                quesid = "53";

            }
            else if (ques == "扫黑除恶")
            {
                quesid = "55";
            }
            else if (ques == "巡防效果")
            {
                quesid = "65";   
            }
            else if (ques == "视频监控")
            {
                quesid = "67";
            }
            else if (ques == "公安执法")
            {
                quesid = "57";
            }
            else if (ques == "检察院执法")
            {
                quesid = "59";
            }
            else if (ques == "司法局执法")
            {
                quesid = "63";
            }
            else if (ques == "法院执法")
            {
                quesid = "61";
            }
            else if (ques == "黑恶势力")
            {
                quesid = "54";
            }

            #region 新建输出表
            DataTable dtnew = new DataTable();
            dtnew.Columns.Add("分类");
            dtnew.Columns.Add("数量");
            dtnew.Columns.Add("比例");
            int c = 0;
            var dsi = questionItemBLL.GetModelList(" F_QuestionId='" + quesid + "' ORDER BY F_Sort, F_ItemId");
            if (dsi.Count > 0)
            {
                foreach (var itemconf in dsi)
                {
                   
                    string cname = itemconf.F_ItemName;
                    if (!cname.Contains("不读出")&& !cname.Contains("无"))
                    {
                        dtnew.Rows.Add();
                        dtnew.Rows[c][0] = cname;
                        c++;
                    }
                    else if (itemconf.F_ItemName.Contains("不读出"))
                    {
                        int index = itemconf.F_ItemName.IndexOf("不读出");
                        cname = itemconf.F_ItemName.Substring(0, index - 1);
                        dtnew.Rows.Add();
                        dtnew.Rows[c][0] = cname;
                        c++;
                    }

                }
            }
           
            #endregion

            string strtaskid = "";
            if (!string.IsNullOrEmpty(TaskID))
            {
                strtaskid = " and F_TaskID='" + TaskID + "' ";
            }
            
            
            #region 根据单位和QuestionItemID统计数量
            if (string.IsNullOrEmpty(countyid))//为空时统计全部乡镇信息，否则显示单个乡镇数据
            {
                decimal  total = 0;
                DataSet ds = DbHelperSQL.Query("select count(F_Expand3) from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 and F_QID='"+quesid +"'" + strtaskid);
                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    total = Decimal.Parse (dt.Rows[0][0].ToString ());
                }

                c = 0;
                foreach (var itemconf in dsi)
                {
                    string cname = itemconf.F_ItemName;
                    if (itemconf.F_ItemName.Contains("不读出"))
                    {
                        int index = itemconf.F_ItemName.IndexOf("不读出");
                        cname = itemconf.F_ItemName.Substring(0,index -1);
                        //continue;
                    }
                        DataSet dsa = DbHelperSQL.Query("select count(F_Expand3) from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 and F_QID='" + quesid + "' and ltrim(rtrim(F_Answer))='" + itemconf.F_ItemName + "' " + strtaskid);
                    DataTable dta = dsa.Tables[0];
                    if (dta.Rows.Count > 0)
                    {
                        
                        dtnew.Rows[c][1] = dta.Rows[0][0];
                        if (total > 0)
                        {
                            dtnew.Rows[c][2] = Math.Round(decimal.Parse(dta.Rows[0][0].ToString()) / total * 100, 2).ToString() + "%";
                        }
                        else
                        {
                            dtnew.Rows[c][2] = 0;
                        }
                    }
                    c++;
                }
                                          
            }
            else
            {
                string contyname = "";
                var dsn = dvItemBLL.GetModelList(" F_DictionaryValueId='" + countyid + "'");
                if (dsn.Count > 0)
                {
                    contyname = dsn[0].F_Name;// iconf.F_Name;                   
                }
                #region
                decimal total = 0;
                DataSet ds = DbHelperSQL.Query("select count(F_Expand3) from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 and F_QID='" + quesid + "'" + strtaskid+ " and F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 " + strtaskid + " and ltrim(rtrim(F_Answer))='" + contyname + "')");
                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    total = Decimal.Parse(dt.Rows[0][0].ToString());
                }

                c = 0;
                foreach (var itemconf in dsi)
                {
                    string cname = itemconf.F_ItemName;
                    if (itemconf.F_ItemName.Contains("不读出"))
                    {
                        int index = itemconf.F_ItemName.IndexOf("不读出");
                        cname = itemconf.F_ItemName.Substring(0, index - 1);
                        //continue;
                    }
                    DataSet dsa = DbHelperSQL.Query("select count(F_Expand3) from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 and F_QID='" + quesid + "'" + strtaskid + " and ltrim(rtrim(F_Answer))='" + itemconf.F_ItemName + "' and F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 " + strtaskid + " and F_Answer='" + contyname + "')");
                    
                    DataTable dta = dsa.Tables[0];
                    if (dta.Rows.Count > 0)
                    {
                        dtnew.Rows[c][1] = dta.Rows[0][0];
                        if (total > 0)
                        {
                            dtnew.Rows[c][2] = Math.Round(decimal.Parse(dta.Rows[0][0].ToString()) / total * 100, 2).ToString() + "%";
                        }
                        else
                        {
                            dtnew.Rows[c][2] = 0;
                        }
                    }
                    c++;
                }
                
                #endregion
               
            }
            if (ques != "扫黑除恶"&& ques != "巡防效果" && ques != "视频监控")
            {
                dtnew.DefaultView.Sort = "数量 DESC";
                dtnew = dtnew.DefaultView.ToTable();
            }
            
           
            
            var obj = new
            {
                //ans = dsi,
                data = dtnew
            };
            return Success("按问题获取数据成功", obj);
            #endregion

        }

        public ActionResult GetDataBAK(string TaskID, string ques, string countyid, string sdate, string edate)
        {
            if (string.IsNullOrEmpty(sdate))
            {
                sdate = DateTime.Now.ToString("yyyy-MM") + "-01 00:00:00";
            }
            if (string.IsNullOrEmpty(edate))
            {
                edate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
            }
            string quesid = "53";
            if (ques == "社会治安")
            {
                quesid = "53";

            }
            else if (ques == "扫黑除恶")
            {
                quesid = "55";
            }
            else if (ques == "巡防效果")
            {
                quesid = "65";
            }
            else if (ques == "视频监控")
            {
                quesid = "66";
            }
            else if (ques == "公安执法")
            {
                quesid = "57";
            }
            else if (ques == "检察院执法")
            {
                quesid = "59";
            }
            else if (ques == "司法局执法")
            {
                quesid = "63";
            }
            else if (ques == "法院执法")
            {
                quesid = "61";
            }
            else if (ques == "黑恶势力")
            {
                quesid = "54";
            }

            #region 新建输出表
            DataTable dtnew = new DataTable();
            dtnew.Columns.Add("乡镇");

            var dsi = questionItemBLL.GetModelList(" F_QuestionId='" + quesid + "'");
            if (dsi.Count > 0)
            {
                foreach (var itemconf in dsi)
                {
                    string cname = itemconf.F_ItemName;

                    dtnew.Columns.Add(cname);
                    dtnew.Columns.Add(cname + "比例");
                }
            }

            #endregion

            string strtaskid = "";
            if (!string.IsNullOrEmpty(TaskID))
            {
                strtaskid = " and F_TaskID='" + TaskID + "' ";
            }

            //2018-7-6
            //DataRow drtotal = dtnew.NewRow();
            //drtotal["乡镇"] = "合计";
            #region 根据单位和QuestionItemID统计数量
            if (string.IsNullOrEmpty(countyid))//为空时统计全部乡镇信息，否则显示单个乡镇数据
            {
                decimal total = 0;
                DataSet ds = DbHelperSQL.Query("select count(F_Expand3) from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 and F_QID='" + quesid + "'" + strtaskid);
                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    total = Decimal.Parse(dt.Rows[0][0].ToString());
                }

                DataRow dr = dtnew.NewRow();
                dr["乡镇"] = "合计";
                foreach (var itemconf in dsi)
                {
                    DataSet dsa = DbHelperSQL.Query("select count(F_Expand3) from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 and F_QID='" + quesid + "' and ltrim(rtrim(F_Answer))='" + itemconf.F_ItemName + "' " + strtaskid);
                    DataTable dta = dsa.Tables[0];
                    if (dta.Rows.Count > 0)
                    {
                        dr[itemconf.F_ItemName] = dta.Rows[0][0];
                        if (total > 0)
                        {
                            dr[itemconf.F_ItemName + "比例"] = Math.Round(decimal.Parse(dr[itemconf.F_ItemName].ToString()) / total * 100, 2).ToString() + "%";
                        }
                        else
                        {
                            dr[itemconf.F_ItemName + "比例"] = 0;
                        }
                    }

                }
                dtnew.Rows.Add(dr);


            }
            else
            {
                string contyname = "";
                var dsn = dvItemBLL.GetModelList(" F_DictionaryValueId='" + countyid + "'");
                if (dsn.Count > 0)
                {
                    contyname = dsn[0].F_Name;// iconf.F_Name;                   
                }
                #region
                decimal total = 0;
                DataSet ds = DbHelperSQL.Query("select count(F_Expand3) from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 and F_QID='" + quesid + "'" + strtaskid + " and F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 " + strtaskid + " and ltrim(rtrim(F_Answer))='" + contyname + "')");
                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    total = Decimal.Parse(dt.Rows[0][0].ToString());
                }

                DataRow dr = dtnew.NewRow();
                dr["乡镇"] = "合计";
                foreach (var itemconf in dsi)
                {
                    DataSet dsa = DbHelperSQL.Query("select count(F_Expand3) from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 and F_QID='" + quesid + "'" + strtaskid + " and ltrim(rtrim(F_Answer))='" + itemconf.F_ItemName.Trim() + "' and F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 " + strtaskid + " and F_Answer='" + contyname + "')");

                    DataTable dta = dsa.Tables[0];
                    if (dta.Rows.Count > 0)
                    {
                        dr[itemconf.F_ItemName] = dta.Rows[0][0];
                        if (total > 0)
                        {
                            dr[itemconf.F_ItemName + "比例"] = Math.Round(decimal.Parse(dr[itemconf.F_ItemName].ToString()) / total * 100, 2).ToString() + "%";
                        }
                        else
                        {
                            dr[itemconf.F_ItemName + "比例"] = 0;
                        }
                    }

                }
                dtnew.Rows.Add(dr);
                #endregion

            }
            var obj = new
            {
                ans = dsi,
                data = dtnew
            };
            return Success("按问题获取数据成功", obj);
            #endregion

        }
        #endregion

        #region 统计调查问卷数量
        /// <summary>
        /// 
        /// </summary>
        /// <param name="TaskID">任务id</param>
        /// <param name="quesid">问题id</param>
        /// <param name="countryid">乡镇id</param>
        /// <param name="sdate">开始时间</param>
        /// <param name="edate">结束时间</param>
        /// <returns></returns>
        public ActionResult GetTotal(string TaskID, string countryid, string sdate, string edate)
        {
            if (string.IsNullOrEmpty(sdate))
            {
                //sdate = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00";
                sdate = DateTime.Now.ToString("yyyy-MM") + "-01 00:00:00";
            }
            if (string.IsNullOrEmpty(edate))
            {
                edate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
            }
            string quesid = "48";
            #region 新建输出表
            DataTable dtnew = new DataTable();
            dtnew.Columns.Add("分类");
            dtnew.Columns.Add("数量",typeof (int));
            dtnew.Rows.Add();
            dtnew.Rows[0][0] = "系统量";
            dtnew.Rows.Add();
            dtnew.Rows[1][0] = "今日量";
            var dsi = questionItemBLL.GetModelList(" F_QuestionId='" + quesid + "'");
            if (dsi.Count > 0)
            {
                int c = 2;
                foreach (var itemconf in dsi)
                {
                    string cname = itemconf.F_ItemName;
                    
                    if (!cname.Contains ("都不是"))
                    {
                        dtnew.Rows.Add();
                        dtnew.Rows[c][0] = cname;
                        c++;
                    }
                    
                }
            }
            #endregion

            string strtaskid = "";
            if (!string.IsNullOrEmpty(TaskID))
            {
                strtaskid = " and F_TaskID='" + TaskID + "' ";
            }

            DataSet ds0 = DbHelperSQL.Query("select count(F_Expand3) from T_Call_OutAnswers ");
            DataTable dt0 = ds0.Tables[0];
            if (dt0.Rows.Count > 0)
            {
                dtnew.Rows[0][1] = dt0.Rows[0][0];
            }
            //今日量
            DataSet ds1 = DbHelperSQL.Query("select count(F_Expand3) from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,GETDATE() )=0 ");
            DataTable dt1 = ds1.Tables[0];
            if (dt1.Rows.Count > 0)
            {
                dtnew.Rows[1][1] = dt1.Rows[0][0];
            }

            
            #region 根据单位和QuestionItemID统计数量
            int cou = 2;

            //从数据字典表中获取乡镇信息（JBDW）
            var dsc = dvItemBLL.GetModelList(" F_DictionaryFlag='XZQY'");

            int totalnum = 0;
            if (dsc.Count > 0)
            {
                foreach (var iconf in dsc)
                {

                    DataSet dsa = DbHelperSQL.Query("select count(F_Expand3) from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 and ltrim(rtrim(F_Answer))='" + iconf.F_Name + "' " + strtaskid);
                    DataTable dta = dsa.Tables[0];
                    if (dta.Rows.Count > 0)
                    {
                        dtnew.Rows[cou][1] = dta.Rows[0][0];
                        if (dta.Rows[0][0] != null && dta.Rows[0][0].ToString() != "")
                        {
                            totalnum += Convert.ToInt32(dta.Rows[0][0]);
                        }
                    }
                    cou++;
                }
            }

            

            dtnew.DefaultView.Sort = "数量 DESC";
            dtnew = dtnew.DefaultView.ToTable();

            #region 添加时间段总量
            dtnew.Rows.Add();
            dtnew.Rows[dtnew.Rows.Count - 1][0] = "总量";
            dtnew.Rows[dtnew.Rows.Count - 1][1] = totalnum;
            #endregion
            return Success("获取调查问卷统计数据成功", dtnew);
            #endregion

        }
        #endregion
        #endregion


        /// <summary>
        /// 外呼量统计
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetCallOutData(string date,string edate)
        {
            if (string.IsNullOrEmpty(date))
            {
                date = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00";
                //sdate = DateTime.Now.ToString("yyyy-MM") + "-01 00:00:00";
            }
            if (string.IsNullOrEmpty(edate))
            {
                edate = date;
                //edate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
            }
            int total = Int32.Parse(DbHelperSQL.GetSingle("select COUNT(1) from T_Call_CallRecords where CallType=1 and DATEDIFF(day,BeginTime,'" + date + "')<=0 and DATEDIFF(day,BeginTime,'" + edate + "')>=0").ToString());
            int wh = Int32.Parse(DbHelperSQL.GetSingle("select COUNT(1) from T_Call_CallRecords where CallType=1 and DATEDIFF(day,BeginTime,'" + date + "')<=0 and DATEDIFF(day,BeginTime,'" + edate + "')>=0").ToString());
            int jt = Int32.Parse(DbHelperSQL.GetSingle("select COUNT(1) from T_Call_CallRecords where CallType=1 and CallState=1 and DATEDIFF(day,BeginTime,'" + date + "')<=0 and DATEDIFF(day,BeginTime,'" + edate + "')>=0").ToString());
            int jdl = Int32.Parse(DbHelperSQL.GetSingle("select COUNT(1) from T_Wo_WorkOrder where CallID IN(select CallID from T_Call_CallRecords where CallType=1 and DATEDIFF(day,BeginTime,'" + date + "')<=0 and DATEDIFF(day,BeginTime,'" + edate + "')>=0) AND IsDel=0").ToString());
            DataTable dtnew = new DataTable();
            dtnew.Columns.Add("分类");
            dtnew.Columns.Add("数量");
            dtnew.Columns.Add("比例");
            dtnew.Rows.Add();
            dtnew.Rows[0][0] = "外呼量";
            dtnew.Rows.Add();
            dtnew.Rows[1][0] = "接通量";
            dtnew.Rows.Add();
            dtnew.Rows[2][0] = "建单量";

            dtnew.Rows[0][1] = wh;
            dtnew.Rows[1][1] = jt;
            dtnew.Rows[2][1] = jdl;
            if (total > 0)
            {
                dtnew.Rows[0][2] = Math.Round((double.Parse(wh.ToString()) * 100 / total), 2) + "%";
            }
            else
            { dtnew.Rows[0][2] = 0; }
            if (wh > 0)
            {
                dtnew.Rows[1][2] = Math.Round((double.Parse(jt.ToString()) * 100 / wh), 2) + "%";
            }
            else
            { dtnew.Rows[1][2] = 0; }
            if (jt > 0)
            {
                dtnew.Rows[2][2] = Math.Round((double.Parse(jdl.ToString()) * 100 / jt), 2) + "%";
            }
            else
            { dtnew.Rows[2][2] = 0; }

            

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

        #region 大屏数据添加导出
        /// <summary>
        /// 
        /// </summary>
        /// <param name="TaskID">任务id</param>
        /// <param name="quesid">问题id</param>
        /// <param name="countryid">乡镇id</param>
        /// <param name="sdate">开始时间</param>
        /// <param name="edate">结束时间</param>
        /// <returns></returns>
        public ActionResult ExportData(string TaskID, string ques, string countyid, string sdate, string edate)
        {
            if (string.IsNullOrEmpty(sdate))
            {
                sdate = DateTime.Now.ToString("yyyy-MM") + "-01 00:00:00";
            }
            if (string.IsNullOrEmpty(edate))
            {
                edate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
            }

            string excelname = "";
            //countyid = "515";
            string quesid = "67";
            if (ques == "社会治安")
            {
                quesid = "53";
                excelname = "社会治安不安全原因统计";
            }
            else if (ques == "扫黑除恶")
            {
                quesid = "55";
                excelname = "扫黑除恶满意度";
            }
            else if (ques == "巡防效果")
            {
                quesid = "65";
                excelname = "巡防效果满意度";
            }
            else if (ques == "视频监控")
            {
                quesid = "67";
                excelname = "视频监控满意度";
            }
            else if (ques == "公安执法")
            {
                quesid = "57";
                excelname = "公安机关执法不（太）满意原因";
            }
            else if (ques == "检察院执法")
            {
                quesid = "59";
                excelname = "检察机关执法不（太）满意原因";
            }
            else if (ques == "司法局执法")
            {
                quesid = "63";
                excelname = "司法局执法不（太）满意原因";
            }
            else if (ques == "法院执法")
            {
                quesid = "61";
                excelname = "法院执法不（太）满意原因";
            }
            else if (ques == "黑恶势力")
            {
                quesid = "54";
                excelname = "黑恶势力情况";
            }

            #region 新建输出表
            DataTable dtnew = new DataTable();
            dtnew.Columns.Add("原因");
            dtnew.Columns.Add("数量");
            dtnew.Columns.Add("比例");
            int c = 0;
            var dsi = questionItemBLL.GetModelList(" F_QuestionId='" + quesid + "' ORDER BY F_ItemId");
            if (dsi.Count > 0)
            {
                foreach (var itemconf in dsi)
                {

                    string cname = itemconf.F_ItemName;
                    if (!cname.Contains("不读出") && !cname.Contains("无"))
                    {
                        dtnew.Rows.Add();
                        dtnew.Rows[c][0] = cname;
                        c++;
                    }
                    else if (itemconf.F_ItemName.Contains("不读出"))
                    {
                        int index = itemconf.F_ItemName.IndexOf("不读出");
                        cname = itemconf.F_ItemName.Substring(0, index - 1);
                        dtnew.Rows.Add();
                        dtnew.Rows[c][0] = cname;
                        c++;
                    }

                }
            }

            #endregion

            string strtaskid = "";
            if (!string.IsNullOrEmpty(TaskID))
            {
                strtaskid = " and F_TaskID='" + TaskID + "' ";
            }


            #region 根据单位和QuestionItemID统计数量
            if (string.IsNullOrEmpty(countyid))//为空时统计全部乡镇信息，否则显示单个乡镇数据
            {
                decimal total = 0;
                DataSet ds = DbHelperSQL.Query("select count(F_Expand3) from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 and F_QID='" + quesid + "'" + strtaskid);
                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    total = Decimal.Parse(dt.Rows[0][0].ToString());
                }

                c = 0;
                foreach (var itemconf in dsi)
                {
                    string cname = itemconf.F_ItemName;
                    if (itemconf.F_ItemName.Contains("不读出"))
                    {
                        int index = itemconf.F_ItemName.IndexOf("不读出");
                        cname = itemconf.F_ItemName.Substring(0, index - 1);
                        //continue;
                    }
                    DataSet dsa = DbHelperSQL.Query("select count(F_Expand3) from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 and F_QID='" + quesid + "' and ltrim(rtrim(F_Answer))='" + itemconf.F_ItemName + "' " + strtaskid);
                    DataTable dta = dsa.Tables[0];
                    if (dta.Rows.Count > 0)
                    {

                        dtnew.Rows[c][1] = dta.Rows[0][0];
                        if (total > 0)
                        {
                            dtnew.Rows[c][2] = Math.Round(decimal.Parse(dta.Rows[0][0].ToString()) / total * 100, 2).ToString() + "%";
                        }
                        else
                        {
                            dtnew.Rows[c][2] = 0;
                        }
                    }
                    c++;
                }

            }
            else
            {
                string contyname = "";
                var dsn = dvItemBLL.GetModelList(" F_DictionaryValueId='" + countyid + "'");
                if (dsn.Count > 0)
                {
                    contyname = dsn[0].F_Name;// iconf.F_Name;                   
                }
                #region
                decimal total = 0;
                DataSet ds = DbHelperSQL.Query("select count(F_Expand3) from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 and F_QID='" + quesid + "'" + strtaskid + " and F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 " + strtaskid + " and ltrim(rtrim(F_Answer))='" + contyname + "')");
                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    total = Decimal.Parse(dt.Rows[0][0].ToString());
                }

                c = 0;
                foreach (var itemconf in dsi)
                {
                    string cname = itemconf.F_ItemName;
                    if (itemconf.F_ItemName.Contains("不读出"))
                    {
                        int index = itemconf.F_ItemName.IndexOf("不读出");
                        cname = itemconf.F_ItemName.Substring(0, index - 1);
                        //continue;
                    }
                    DataSet dsa = DbHelperSQL.Query("select count(F_Expand3) from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 and F_QID='" + quesid + "'" + strtaskid + " and ltrim(rtrim(F_Answer))='" + itemconf.F_ItemName + "' and F_Expand3 IN  (select F_Expand3 from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 " + strtaskid + " and F_Answer='" + contyname + "')");

                    DataTable dta = dsa.Tables[0];
                    if (dta.Rows.Count > 0)
                    {
                        dtnew.Rows[c][1] = dta.Rows[0][0];
                        if (total > 0)
                        {
                            dtnew.Rows[c][2] = Math.Round(decimal.Parse(dta.Rows[0][0].ToString()) / total * 100, 2).ToString() + "%";
                        }
                        else
                        {
                            dtnew.Rows[c][2] = 0;
                        }
                    }
                    c++;
                }

                #endregion

            }
            if (ques != "扫黑除恶" && ques != "巡防效果" && ques != "视频监控")
            {
                dtnew.DefaultView.Sort = "数量 DESC";
                dtnew = dtnew.DefaultView.ToTable();
            }

            string sedate = DateTime.Parse(sdate).ToString("yyyy-MM-dd");
            if (sdate != edate)
            {
                sedate = DateTime.Parse(sdate).ToString("yyyy-MM-dd") + "---" + DateTime.Parse(edate).ToString("yyyy-MM-dd");
            }

            NPOIHelper npoi = new NPOIHelper();
            if (npoi.ExportToExcel2(excelname+sedate , dtnew, null) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");
            }
            #endregion

        }
        #endregion

        #region 导出统计调查问卷数量
        /// <summary>
        /// 
        /// </summary>
        /// <param name="TaskID">任务id</param>
        /// <param name="quesid">问题id</param>
        /// <param name="countryid">乡镇id</param>
        /// <param name="sdate">开始时间</param>
        /// <param name="edate">结束时间</param>
        /// <returns></returns>
        public ActionResult GetTotalExpt(string TaskID, string countryid, string sdate, string edate)
        {
            if (string.IsNullOrEmpty(sdate))
            {
                sdate = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00";
                //sdate = DateTime.Now.ToString("yyyy-MM") + "-01 00:00:00";
            }
            if (string.IsNullOrEmpty(edate))
            {
                edate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
            }
            string quesid = "48";
            #region 新建输出表
            DataTable dtnew = new DataTable();
            dtnew.Columns.Add("分类");
            dtnew.Columns.Add("数量", typeof(int));
            dtnew.Rows.Add();
            dtnew.Rows[0][0] = "系统量";
            dtnew.Rows.Add();
            dtnew.Rows[1][0] = "今日量";
            var dsi = questionItemBLL.GetModelList(" F_QuestionId='" + quesid + "'");
            if (dsi.Count > 0)
            {
                int c = 2;
                foreach (var itemconf in dsi)
                {
                    string cname = itemconf.F_ItemName;

                    if (!cname.Contains("都不是"))
                    {
                        dtnew.Rows.Add();
                        dtnew.Rows[c][0] = cname;
                        c++;
                    }

                }
            }
            #endregion

            string strtaskid = "";
            if (!string.IsNullOrEmpty(TaskID))
            {
                strtaskid = " and F_TaskID='" + TaskID + "' ";
            }

            DataSet ds0 = DbHelperSQL.Query("select count(F_Expand3) from T_Call_OutAnswers ");
            DataTable dt0 = ds0.Tables[0];
            if (dt0.Rows.Count > 0)
            {
                dtnew.Rows[0][1] = dt0.Rows[0][0];
            }
            //今日量
            DataSet ds1 = DbHelperSQL.Query("select count(F_Expand3) from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,GETDATE() )=0 ");
            DataTable dt1 = ds1.Tables[0];
            if (dt1.Rows.Count > 0)
            {
                dtnew.Rows[1][1] = dt1.Rows[0][0];
            }


            #region 根据单位和QuestionItemID统计数量
            int cou = 2;

            //从数据字典表中获取乡镇信息（JBDW）
            var dsc = dvItemBLL.GetModelList(" F_DictionaryFlag='XZQY'");


            if (dsc.Count > 0)
            {
                foreach (var iconf in dsc)
                {

                    DataSet dsa = DbHelperSQL.Query("select count(F_Expand3) from T_Call_OutAnswers where  DATEDIFF(day,F_OptOn,'" + sdate + "')<=0 and DATEDIFF(day,F_OptOn,'" + edate + "')>=0 and ltrim(rtrim(F_Answer))='" + iconf.F_Name + "' " + strtaskid);
                    DataTable dta = dsa.Tables[0];
                    if (dta.Rows.Count > 0)
                    {
                        dtnew.Rows[cou][1] = dta.Rows[0][0];
                    }
                    cou++;
                }
            }
            dtnew.DefaultView.Sort = "数量 DESC";
            dtnew = dtnew.DefaultView.ToTable();
            //return Success("获取调查问卷统计数据成功", dtnew);
            string sedate = DateTime.Parse(sdate).ToString("yyyy-MM-dd");
            if (sdate != edate)
            {
                sedate = DateTime.Parse(sdate).ToString("yyyy-MM-dd") + "---" + DateTime.Parse(edate).ToString("yyyy-MM-dd");
            }

            NPOIHelper npoi = new NPOIHelper();
            if (npoi.ExportToExcel2("调查问卷统计数据"+sedate , dtnew, null) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");
            }
            #endregion

        }
        #endregion

        #region 话务数据导出
        /// <summary>
        /// 话务量实时数据统计
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetTelCount24ByDateExpt(DateTime? date)
        {
            if (date == null)
            {
                date = DateTime.Now;
            }

            string strdate = date.Value.ToString("yyyy-MM-dd");

            //int[] hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
            int[] hours = Enumerable.Range(0, 24).ToArray<int>();
            int[] tcounts = new int[24];//来电数量
            int[] ccounts = new int[24];//接通数量
            int[] lcounts = new int[24];//留言数量
            int[] gcounts = new int[24];//放弃数量
            int[] scounts = new int[24];//骚扰数量

            string ldsql = $"select  DATEPART(HH,BeginTime) hours,COUNT(1) ccount from T_Call_CallRecords where CallType=0 and DATEDIFF(day, BeginTime, '" + strdate + "') = 0 group by DATEPART(HH, BeginTime) order by DATEPART(HH, BeginTime)";
            string jtsql = $"select  DATEPART(HH,BeginTime) hours,COUNT(1) ccount from T_Call_CallRecords where CallType=0 and CallState=1 and DATEDIFF(day, BeginTime, '" + strdate + "') = 0 group by DATEPART(HH, BeginTime) order by DATEPART(HH, BeginTime)";
            string lysql = $"select  DATEPART(HH,BeginTime) hours,COUNT(1) ccount from T_Call_CallRecords where CallType=0 and DealType=4 and DATEDIFF(day, BeginTime, '" + strdate + "') = 0 group by DATEPART(HH, BeginTime) order by DATEPART(HH, BeginTime)";
            string srsql = $"select  DATEPART(HH,BeginTime) hours,COUNT(1) ccount from T_Call_CallRecords where CallType=0 and DealType=1 and DATEDIFF(day, BeginTime, '" + strdate + "') = 0 group by DATEPART(HH, BeginTime) order by DATEPART(HH, BeginTime)";

            DataTable dtld = DbHelperSQL.Query(ldsql).Tables[0];
            DataTable dtjt = DbHelperSQL.Query(jtsql).Tables[0];
            DataTable dtly = DbHelperSQL.Query(lysql).Tables[0];
            DataTable dtsr = DbHelperSQL.Query(srsql).Tables[0];

            for (int i = 0; i < hours.Length; i++)
            {
                var drld = dtld.Select("hours='" + hours[i] + "' ");
                tcounts[i] = (from DataRow dr in drld select dr.Field<int>("ccount")).FirstOrDefault();

                var drjt = dtjt.Select("hours='" + hours[i] + "' ");
                ccounts[i] = (from DataRow dr in drjt select dr.Field<int>("ccount")).FirstOrDefault();

                var drly = dtly.Select("hours='" + hours[i] + "' ");
                lcounts[i] = (from DataRow dr in drly select dr.Field<int>("ccount")).FirstOrDefault();

                var drsr = dtsr.Select("hours='" + hours[i] + "' ");
                scounts[i] = (from DataRow dr in drsr select dr.Field<int>("ccount")).FirstOrDefault();

                gcounts[i] = tcounts[i] - ccounts[i] - lcounts[i] - scounts[i];

            }
           
            DataTable dt = new DataTable();
            dt.Columns.Add("小时");
            dt.Columns.Add("来电数量");
            dt.Columns.Add("接通数量");
            dt.Columns.Add("放弃数量");
            dt.Columns.Add("黑名单拒接数量");
            for (int i = 0; i < 24; i++)
            {
                dt.Rows.Add();
                dt.Rows[i][0] = hours[i];
                dt.Rows[i][1] = tcounts[i];
                dt.Rows[i][2] = ccounts[i];
                dt.Rows[i][3] = gcounts[i];
                dt.Rows[i][4] = scounts[i];
            }

            //return Success("加载成功", obj);
            string sedate = Convert.ToDateTime (date).ToString("yyyy-MM-dd");
            
            NPOIHelper npoi = new NPOIHelper();
            if (npoi.ExportToExcel2("话务量实时数据统计" + sedate, dt, null) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");
            }
        }

        /// <summary>
        /// 获取通话数量
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetTelCountByDateExpt(DateTime? date)
        {
            string where = " ";
            if (date == null)
            {
                date = DateTime.Now;
            }

            string strdate = date.Value.ToString("yyyy-MM-dd");
            where += " and datediff(day,BeginTime,'" + strdate + "')=0";

            //if (start == null && end == null)
            //{
            //    where += " and datediff(day,BeginTime,getdate())=0";
            //}
            //else
            //{
            //    if (start == null) { start = DateTime.Now; }
            //    if (end == null) { end = DateTime.Now; }
            //    where += $" and datediff(day,BeginTime,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,BeginTime,'{end.Value.ToString("yyyy-MM-dd")}')>=0 ";
            //}

            //话务量
            //DataTable dt = DbHelperSQL.Query(" select count(1) con from T_Call_CallRecords where 1=1 " + where).Tables[0];

            //var hwcon = dt.Rows[0]["con"].ToString();//话务量
            //外呼量
            DataTable dt = DbHelperSQL.Query(" select count(1) con from T_Call_CallRecords where CallType=1 " + where).Tables[0];

            var hwcon = dt.Rows[0]["con"].ToString();//话务量

            //来话量
            DataTable dt1 = DbHelperSQL.Query(" select count(1) con from T_Call_CallRecords where CallType=0 " + where).Tables[0];
            var lhcon = dt1.Rows[0]["con"].ToString();//话务量

            //接通量,通话时长
            DataTable dt2 = DbHelperSQL.Query(" select count(1) con,sum(TalkLongTime) tltimes from T_Call_CallRecords where CallState=1 " + where).Tables[0];
            var jtcon = dt2.Rows[0]["con"].ToString();//接通量
            var ths = dt2.Rows[0]["tltimes"];//通话时长
            var thtimes = "0";
            if (ths != null && ths.ToString() != "")
            {
                thtimes = ths.ToString();
            }
            //string jtl = "-";//接通率
            //if (hwcon != "0")
            //{
            //    jtl = (double.Parse(jtcon) / double.Parse(hwcon)).ToString("0.00%");
            //}
            double jtl = 0;//接通率
            if (hwcon != "0")
            {
                jtl = Math.Round((double.Parse(jtcon) * 100 / double.Parse(hwcon)), 2);
            }
            double pjthtimes = 0.00;//平均通话时长
            if (jtcon != "0")
            {
                pjthtimes = Math.Round(double.Parse(thtimes) / double.Parse(jtcon));
            }

            var obj = new
            {
                hwcon = hwcon,
                lhcon = lhcon,
                jtcon = jtcon,
                pjthtimes = pjthtimes,
                jtl = jtl,
                thtimes
            };


            DataTable dtt = new DataTable();
            dtt.Columns.Add("外呼量");
            dtt.Columns.Add("呼入量");
            dtt.Columns.Add("接通量");
            dtt.Columns.Add("平均通话时长");
            dtt.Columns.Add("总通话时长");
           
                dtt.Rows.Add();
                dtt.Rows[0][0] = hwcon;
                dtt.Rows[0][1] = lhcon;
                dtt.Rows[0][2] = jtcon;
                dtt.Rows[0][3] = pjthtimes;
                dtt.Rows[0][4] = thtimes;
            

            //return Success("加载成功", obj);
            string sedate = Convert.ToDateTime (date).ToString("yyyy-MM-dd");

            NPOIHelper npoi = new NPOIHelper();
            if (npoi.ExportToExcel2("当日话务量总体统计" + sedate, dtt, null) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");
            }
        }


        /// <summary>
        /// 坐席闲忙比例
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetUserStateCount24ByDateExpt(DateTime? start, DateTime? end)
        {
            string where = " 1=1 ";
            if (start == null && end == null)
            {
                where += " and datediff(day,OccurTime,getdate())=0";
            }
            else
            {
                if (start == null) { start = DateTime.Now; }
                if (end == null) { end = DateTime.Now; }
                where += $" and datediff(day,OccurTime,'{start.Value.ToString("yyyy-MM-dd")}')<=0 and datediff(day,OccurTime,'{end.Value.ToString("yyyy-MM-dd")}')>=0 ";
            }

            string sql = "select hor,type,COUNT(1) con from (select datepart(hh, OccurTime) hor, State type, agentid, count(1) con  "
                + " from rep_agent_state   where " + where
                + "  group by datepart(hh, OccurTime), State, agentid ) t group by hor, type";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];

            //int[] hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
            int[] hours = Enumerable.Range(0, 24).ToArray<int>();
            double[] kxpercents = new double[24];
            double[] thpercents = new double[24];
            double[] zmpercents = new double[24];

            for (int i = 0; i < hours.Length; i++)
            {

                var list = dt.Select(" hor=" + hours[i]);
                int con = 0;//总量
                foreach (var l in list)
                {
                    con = con + Int32.Parse(l["con"].ToString());
                }
                //var con = list.Count() > 0 ? list[0]["con"].ToString() : "0";//总量
                var kxlist = dt.Select(" hor=" + hours[i] + " and type=2 ");
                var kxcon = kxlist.Count() > 0 ? kxlist[0]["con"].ToString() : "0";//空闲
                var thlist = dt.Select(" hor=" + hours[i] + " and type=3 ");
                var thcon = thlist.Count() > 0 ? thlist[0]["con"].ToString() : "0"; ;//通话中
                var hhcllist = dt.Select(" hor=" + hours[i] + " and type=4 ");
                var hhclcon = hhcllist.Count() > 0 ? hhcllist[0]["con"].ToString() : "0"; ;//话后处理中
                var xxlist = dt.Select(" hor=" + hours[i] + " and type=5 ");
                var xxcon = xxlist.Count() > 0 ? xxlist[0]["con"].ToString() : "0"; ;//小休
                var zllist = dt.Select(" hor=" + hours[i] + " and type=6 ");
                var zlcon = zllist.Count() > 0 ? zllist[0]["con"].ToString() : "0"; ;//被请求
                if (con == 0)
                {
                    kxpercents[i] = 0;
                    thpercents[i] = 0;
                    zmpercents[i] = 0;
                }
                else
                {
                    double zm = double.Parse(hhclcon) + double.Parse(xxcon) + double.Parse(zlcon);
                    kxpercents[i] = Math.Round((double.Parse(kxcon) * 100 / con), 2);
                    thpercents[i] = Math.Round((double.Parse(thcon) * 100 / con), 2);
                    zmpercents[i] = Math.Round((zm * 100 / con), 2);//zmpercents[i] = Math.Round((double.Parse(xxcon) * 100 / con), 2);//2020-4-14调整置忙比例计算
                }

            }
           
            DataTable dtt = new DataTable();
            dtt.Columns.Add("小时");
            dtt.Columns.Add("空闲比例");
            dtt.Columns.Add("通话比例");
            dtt.Columns.Add("置忙比例");
            
            for (int i = 0; i < 24; i++)
            {
                dtt.Rows.Add();
                dtt.Rows[i][0] = hours[i];
                dtt.Rows[i][1] = kxpercents[i];
                dtt.Rows[i][2] = thpercents[i];
                dtt.Rows[i][3] = zmpercents[i];
                
            }

            //return Success("加载成功", obj);
            string sedate = Convert.ToDateTime (start).ToString("yyyy-MM-dd");
            if (start != end)
            {
                sedate = Convert.ToDateTime(start).ToString("yyyy-MM-dd") + "---" + Convert.ToDateTime(end).ToString("yyyy-MM-dd");
            }

            NPOIHelper npoi = new NPOIHelper();
            if (npoi.ExportToExcel2("坐席闲忙比例统计" + sedate, dtt, null) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");
            }
        }


        /// <summary>
        /// 平均通话时长统计
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetAvgTelTime24ByDateExpt(DateTime? date)
        {
            if (date == null)
            {
                date = DateTime.Now;
            }

            string strdate = date.Value.ToString("yyyy-MM-dd");

            //接通量，通话时长
            string sql = " select datepart(hh,BeginTime) hor,count(1) con,sum(TalkLongTime) tltimes from T_Call_CallRecords  WITH(NOLOCK) where "
                + "CallState=1 and datediff(day, BeginTime, '" + strdate + "') = 0 group by datepart(hh,BeginTime) ";
            DataTable dt = DbHelperSQL.Query(sql).Tables[0];

            //int[] hours = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 };
            int[] hours = Enumerable.Range(0, 24).ToArray<int>();
            double[] avgtimes = new double[24];
            for (int i = 0; i < hours.Length; i++)
            {
                var jtlist = dt.Select(" hor=" + hours[i] + " ");
                var jtcon = jtlist.Count() > 0 ? jtlist[0]["con"].ToString() : "0";
                var thtimes = jtlist.Count() > 0 ? jtlist[0]["tltimes"].ToString() : "0";

                double avgtime = 0.00;
                if (jtcon != "0")
                {
                    if (thtimes == "") { thtimes = "0"; }
                    avgtime = Math.Round(double.Parse(thtimes) / double.Parse(jtcon), 2);
                }

                avgtimes[i] = avgtime;
            }
            //var obj = new
            //{
            //    hours = hours,
            //    avgtimes = avgtimes
            //};
            //return Success("加载成功", obj);

            DataTable dtt = new DataTable();
            dtt.Columns.Add("小时");
            dtt.Columns.Add("平均通话时长");
            

            for (int i = 0; i < 24; i++)
            {
                dtt.Rows.Add();
                dtt.Rows[i][0] = hours[i];
                dtt.Rows[i][1] = avgtimes[i];
                

            }

            //return Success("加载成功", obj);
            string sedate = Convert.ToDateTime (date).ToString("yyyy-MM-dd");
            
            NPOIHelper npoi = new NPOIHelper();
            if (npoi.ExportToExcel2("平均通话时长统计" + sedate, dtt, null) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");
            }
        }

        /// <summary>
        /// 外呼量统计
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public ActionResult GetCallOutDataExpt(string date, string edate)
        {
            if (string.IsNullOrEmpty(date))
            {
                date = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00";
                //sdate = DateTime.Now.ToString("yyyy-MM") + "-01 00:00:00";
            }
            if (string.IsNullOrEmpty(edate))
            {
                edate = date;
                //edate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59";
            }
            int total = Int32.Parse(DbHelperSQL.GetSingle("select COUNT(1) from T_Call_CallRecords where CallType=1 and DATEDIFF(day,BeginTime,'" + date + "')<=0 and DATEDIFF(day,BeginTime,'" + edate + "')>=0").ToString());
            int wh = Int32.Parse(DbHelperSQL.GetSingle("select COUNT(1) from T_Call_CallRecords where CallType=1 and DATEDIFF(day,BeginTime,'" + date + "')<=0 and DATEDIFF(day,BeginTime,'" + edate + "')>=0").ToString());
            int jt = Int32.Parse(DbHelperSQL.GetSingle("select COUNT(1) from T_Call_CallRecords where CallType=1 and CallState=1 and DATEDIFF(day,BeginTime,'" + date + "')<=0 and DATEDIFF(day,BeginTime,'" + edate + "')>=0").ToString());
            int jdl = Int32.Parse(DbHelperSQL.GetSingle("select COUNT(1) from T_Wo_WorkOrder where CallID IN(select CallID from T_Call_CallRecords where CallType=1 and DATEDIFF(day,BeginTime,'" + date + "')<=0 and DATEDIFF(day,BeginTime,'" + edate + "')>=0) AND IsDel=0").ToString());
            DataTable dtnew = new DataTable();
            dtnew.Columns.Add("分类");
            dtnew.Columns.Add("数量");
            //dtnew.Columns.Add("比例");
            dtnew.Rows.Add();
            dtnew.Rows[0][0] = "外呼量";
            dtnew.Rows.Add();
            dtnew.Rows[1][0] = "接通量";
            dtnew.Rows.Add();
            dtnew.Rows[2][0] = "建单量";

            dtnew.Rows[0][1] = wh;
            dtnew.Rows[1][1] = jt;
            dtnew.Rows[2][1] = jdl;
            //if (total > 0)
            //{
            //    dtnew.Rows[0][2] = Math.Round((double.Parse(wh.ToString()) * 100 / total), 2) + "%";
            //}
            //else
            //{ dtnew.Rows[0][2] = 0; }
            //if (wh > 0)
            //{
            //    dtnew.Rows[1][2] = Math.Round((double.Parse(jt.ToString()) * 100 / wh), 2) + "%";
            //}
            //else
            //{ dtnew.Rows[1][2] = 0; }
            //if (jt > 0)
            //{
            //    dtnew.Rows[2][2] = Math.Round((double.Parse(jdl.ToString()) * 100 / jt), 2) + "%";
            //}
            //else
            //{ dtnew.Rows[2][2] = 0; }



            string sedate = Convert.ToDateTime (date).ToString("yyyy-MM-dd");

            NPOIHelper npoi = new NPOIHelper();
            if (npoi.ExportToExcel2("外呼量统计" + sedate, dtnew, null) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");
            }
        }
        #endregion
    }
}