HeBi12345_API/CallCenterApi/CallCenterAp.../CallCenterAp.../Controllers/report/BusinessCont...

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

namespace CallCenterApi.Interface.Controllers.report
{
    //[Authority]
    public class BusinessController : BaseController
    {
        private BLL.T_Bus_WorkOrder WorkOrder = new BLL.T_Bus_WorkOrder();
        /// <summary>
        /// 投诉统计
        /// </summary>
        /// <returns></returns>
        public ActionResult GetTSCountList()
        {
            string year = RequestString.GetQueryString("year");
            string month = RequestString.GetQueryString("month");
            if (string.IsNullOrEmpty(year))
            {
                year = DateTime.Now.ToString("yyyy");
            }
            if (string.IsNullOrEmpty(month))
            {
                month = DateTime.Now.ToString("MM");
            }

            BLL.T_Sys_DictionaryValue DictionaryValue = new BLL.T_Sys_DictionaryValue();

            string day = DateTime.DaysInMonth(int.Parse(year), int.Parse(month)).ToString();
            string date = year + "." + month + "." + day;
            StringBuilder sb = new StringBuilder();
            sb.Append("<table id=\"month\" width =\"700px\"  border=\"0\" cellpadding=\"0\" cellspacing=\"1\" bgcolor=\"#4aadfb\">");
            sb.Append("<tr><td bgcolor=\"#FFFFFF\" align=\"center\" colspan =\"4\"><font color=\"black\" size=\"4px\" >" + year + "年" + month + "月" + "市长热线受理投诉统计表</font><br/><font color=\"black\" size=\"2px\" >" + DateTime.Now.ToLongDateString().ToString() + "</font></td></tr>");
            sb.Append("<tr bgcolor=\"#eef3f6\" height=\"20\"  align=\"center\"><th colspan =\"2\" width=\"40%\">类别</th><th>当月数</th><th>累计数</th></tr>");
            DataSet ds = DictionaryValue.GetList(" F_ItemId=2");
            int T_Allcount = 0;
            int M_Allcount = 0;
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                DataTable dt = DictionaryValue.GetList(" F_PrentId=" + row["F_ValueId"].ToString()).Tables[0];//获取列表
                if (dt != null && dt.Rows.Count > 1)
                {
                    //第一个，“投诉”的
                    sb.Append("<tr style=\"border:1px solid #E0EEFE\" align=\"center\"><td bgcolor=\"#FFFFFF\" rowspan =\"" + dt.Rows.Count.ToString() + "\">" + row["F_Value"].ToString() + "</td>");
                    for (int n = 0; n < dt.Rows.Count; n++)
                    {
                        //内容大类
                        //int T_count =WorkOrder.GetList(" F_InfoConBigType=" + dt.Rows[n]["F_ValueId"].ToString() + " and  F_IsClosed=1 and DateDiff(yy,F_RegDate,'"+ date + "')=0 and DateDiff(d,F_RegDate,'"+ date + "')>=0").Tables[0].Rows.Count;//当年
                        //int M_count = WorkOrder.GetList(" F_InfoConBigType=" + dt.Rows[n]["F_ValueId"].ToString() + " and  F_IsClosed=1 and DateDiff(mm,F_RegDate,'" + date + "')=0").Tables[0].Rows.Count;//月
                        int T_count = WorkOrder.GetList(" F_IsDelete=0 and F_InfoConBigType=" + dt.Rows[n]["F_ValueId"].ToString() + " and DateDiff(yy,F_RegDate,'" + date + "')=0 and DateDiff(d,F_RegDate,'" + date + "')>=0  and F_InfoConSmallType is not null ").Tables[0].Rows.Count;//当年
                        int M_count = WorkOrder.GetList(" F_IsDelete=0 and F_InfoConBigType=" + dt.Rows[n]["F_ValueId"].ToString() + " and DateDiff(mm,F_RegDate,'" + date + "')=0  and F_InfoConSmallType is not null ").Tables[0].Rows.Count;//月
                        if (n == 0)
                        {
                            sb.Append("<td bgcolor=\"#FFFFFF\">" + dt.Rows[n]["F_Value"] + "</td><td bgcolor=\"#FFFFFF\">" + M_count.ToString() + "</td><td bgcolor=\"#FFFFFF\">" + T_count.ToString() + "</td></tr>");
                        }
                        else
                        {
                            sb.Append("<tr style=\"border:1px solid #E0EEFE\" align=\"center\"><td bgcolor=\"#FFFFFF\">" + dt.Rows[n]["F_Value"] + "</td><td bgcolor=\"#FFFFFF\">" + M_count.ToString() + "</td><td bgcolor=\"#FFFFFF\">" + T_count.ToString() + "</td></tr>");
                        }
                        T_Allcount += T_count;//累积数
                        M_Allcount += M_count;//当月数
                    }
                }
                else
                {
                    //只有一个的
                    //int T_count = WorkOrder.GetList(" F_InfoType=" + row["F_ValueId"].ToString() + " and F_IsClosed=1 and DateDiff(yy,F_RegDate,'" + date + "')=0 and DateDiff(d,F_RegDate,'" + date + "')>=0").Tables[0].Rows.Count;
                    //int M_count = WorkOrder.GetList(" F_InfoType=" + row["F_ValueId"].ToString() + " and F_IsClosed=1 and DateDiff(mm,F_RegDate,'" + date + "')=0").Tables[0].Rows.Count;
                    int T_count = WorkOrder.GetList(" F_IsDelete=0 and F_InfoType=" + row["F_ValueId"].ToString() + " and DateDiff(yy,F_RegDate,'" + date + "')=0 and DateDiff(d,F_RegDate,'" + date + "')>=0  and F_InfoConSmallType is not null ").Tables[0].Rows.Count;
                    int M_count = WorkOrder.GetList(" F_IsDelete=0 and F_InfoType=" + row["F_ValueId"].ToString() + " and DateDiff(mm,F_RegDate,'" + date + "')=0  and F_InfoConSmallType is not null ").Tables[0].Rows.Count;
                    sb.Append("<tr style=\"border:1px solid #E0EEFE\" align=\"center\"><td bgcolor=\"#FFFFFF\" colspan=\"2\">" + row["F_Value"] + "</td><td bgcolor=\"#FFFFFF\">" + M_count.ToString() + "</td><td bgcolor=\"#FFFFFF\">" + T_count.ToString() + "</td></tr>");
                    T_Allcount += T_count;
                    M_Allcount += M_count;
                }
            }
            //sb.Append("<tr><td colspan =\"2\" bgcolor=\"#FFFFFF\" align=\"center\">合计</td><td bgcolor=\"#FFFFFF\" align=\"center\">" + M_Allcount + "</td><td bgcolor=\"#FFFFFF\" align=\"center\">" + T_Allcount + "</td></tr>");
            T_Allcount = 0;
            M_Allcount = 0;
            sb.Append("<tr style=\"border:1px solid #E0EEFE \" height=\"20px\" align=\"center\" ><td bgcolor=\"#FFFFFF\" colspan =\"4\"></td></tr>");
            DataSet _ds = DictionaryValue.GetList(" F_ItemId=6");
            foreach (DataRow row in _ds.Tables[0].Rows)
            {
                //int T_count = WorkOrder.GetList(" F_InfoSource=" + row["F_ValueId"].ToString() + " and F_IsClosed=1 and  DateDiff(yy,F_RegDate,'" + date + "')=0 and DateDiff(d,F_RegDate,'" + date + "')>=0").Tables[0].Rows.Count;
                //int M_count = WorkOrder.GetList(" F_InfoSource=" + row["F_ValueId"].ToString() + " and  F_IsClosed=1 and  DateDiff(mm,F_RegDate,'" + date + "')=0").Tables[0].Rows.Count;
                int T_count = WorkOrder.GetList(" F_InfoSource=" + row["F_ValueId"].ToString() + " and  DateDiff(yy,F_RegDate,'" + date + "')=0 and DateDiff(d,F_RegDate,'" + date + "')>=0").Tables[0].Rows.Count;
                int M_count = WorkOrder.GetList(" F_InfoSource=" + row["F_ValueId"].ToString() + " and  DateDiff(mm,F_RegDate,'" + date + "')=0").Tables[0].Rows.Count;
                sb.Append("<tr style=\"border:1px solid #E0EEFE\" align=\"center\"><td bgcolor=\"#FFFFFF\" colspan=\"2\">" + row["F_Value"] + "</td><td bgcolor=\"#FFFFFF\">" + M_count.ToString() + "</td><td bgcolor=\"#FFFFFF\">" + T_count.ToString() + "</td></tr>");
                T_Allcount += T_count;
                M_Allcount += M_count;
            }
            sb.Append("<tr style=\"border:1px solid #E0EEFE\" align=\"center\"><td colspan =\"2\" bgcolor=\"#FFFFFF\">合计</td><td bgcolor=\"#FFFFFF\">" + M_Allcount + "</td><td bgcolor=\"#FFFFFF\">" + T_Allcount + "</td></tr>");
            sb.Append("<tr style=\"border:1px solid #E0EEFE \" height=\"20px\" align=\"center\" ><td bgcolor=\"#FFFFFF\" colspan =\"4\"></td></tr>");
            //int count1 = WorkOrder.GetList(" F_IsResult=1 and F_IsClosed=1 and DateDiff(yy,F_RegDate,'" + date + "')=0 and DateDiff(d,F_RegDate,'" + date + "')>=0").Tables[0].Rows.Count;
            //int countm1 = WorkOrder.GetList(" F_IsResult=1 and F_IsClosed=1 and  DateDiff(m,F_RegDate,'" + date + "')=0").Tables[0].Rows.Count;
            //int count2 = WorkOrder.GetList(" F_IsResult=0 and F_IsClosed=1 and DateDiff(yy,F_RegDate,'" + date + "')=0 and DateDiff(d,F_RegDate,'" + date + "')>=0").Tables[0].Rows.Count;
            //int countm2 = WorkOrder.GetList(" F_IsResult=0 and F_IsClosed=1 and DateDiff(m,F_RegDate,'" + date + "')=0").Tables[0].Rows.Count;
            int count1 = WorkOrder.GetList(" F_IsResult=1 and DateDiff(yy,F_RegDate,'" + date + "')=0 and DateDiff(d,F_RegDate,'" + date + "')>=0").Tables[0].Rows.Count;
            int countm1 = WorkOrder.GetList(" F_IsResult=1 and DateDiff(m,F_RegDate,'" + date + "')=0").Tables[0].Rows.Count;
            int count2 = WorkOrder.GetList(" F_IsResult=0 and DateDiff(yy,F_RegDate,'" + date + "')=0 and DateDiff(d,F_RegDate,'" + date + "')>=0").Tables[0].Rows.Count;
            int countm2 = WorkOrder.GetList(" F_IsResult=0 and DateDiff(m,F_RegDate,'" + date + "')=0").Tables[0].Rows.Count;
            sb.Append("<tr style=\"border:1px solid #E0EEFE\" align=\"center\"><td bgcolor=\"#FFFFFF\" colspan =\"2\">直办</td><td bgcolor=\"#FFFFFF\">" + countm1 + "</td><td bgcolor=\"#FFFFFF\">" + count1 + "</td></tr>");
            sb.Append("<tr style=\"border:1px solid #E0EEFE\" align=\"center\"><td colspan =\"2\" bgcolor=\"#FFFFFF\">督办</td><td bgcolor=\"#FFFFFF\">" + countm2 + "</td><td bgcolor=\"#FFFFFF\">" + count2 + "</td></tr></table>");

            return Success("成功", sb.ToString());

        }

        /// <summary>
        /// 导出投诉统计
        /// </summary>
        /// <returns></returns>
        public ActionResult ExportTSCountList()
        {
            string year = RequestString.GetQueryString("year");
            string month = RequestString.GetQueryString("month");
            if (string.IsNullOrEmpty(year))
            {
                year = DateTime.Now.ToString("yyyy");
            }
            if (string.IsNullOrEmpty(month))
            {
                month = DateTime.Now.ToString("MM");
            }

            BLL.T_Sys_DictionaryValue DictionaryValue = new BLL.T_Sys_DictionaryValue();

            string day = DateTime.DaysInMonth(int.Parse(year), int.Parse(month)).ToString();
            string date = year + "." + month + "." + day;

            DataTable dtNew = new DataTable();

            DataColumn dc1 = new DataColumn("类别", Type.GetType("System.String"));
            DataColumn dc2 = new DataColumn("类别1", Type.GetType("System.String"));
            DataColumn dc3 = new DataColumn("当月数", Type.GetType("System.String"));
            DataColumn dc4 = new DataColumn("累计数", Type.GetType("System.String"));

            dtNew.Columns.Add(dc1);
            dtNew.Columns.Add(dc2);
            dtNew.Columns.Add(dc3);
            dtNew.Columns.Add(dc4);

            int tscount = 0;

            DataSet ds = DictionaryValue.GetList(" F_ItemId=2");

            foreach (DataRow row in ds.Tables[0].Rows)
            {
                DataTable dt = DictionaryValue.GetList(" F_PrentId=" + row["F_ValueId"].ToString()).Tables[0];//获取列表
                if (dt != null && dt.Rows.Count > 1)
                {
                    tscount = dt.Rows.Count;
                    for (int n = 0; n < dt.Rows.Count; n++)
                    {
                        //内容大类
                        int T_count = WorkOrder.GetList(" F_IsDelete=0 and F_InfoConBigType=" + dt.Rows[n]["F_ValueId"].ToString() + " and DateDiff(yy,F_RegDate,'" + date + "')=0 and DateDiff(d,F_RegDate,'" + date + "')>=0  and F_InfoConSmallType is not null ").Tables[0].Rows.Count;//当年
                        int M_count = WorkOrder.GetList(" F_IsDelete=0 and F_InfoConBigType=" + dt.Rows[n]["F_ValueId"].ToString() + " and DateDiff(mm,F_RegDate,'" + date + "')=0  and F_InfoConSmallType is not null ").Tables[0].Rows.Count;//月

                        DataRow drNew = dtNew.NewRow();
                        drNew["类别"] = row["F_Value"].ToString();
                        drNew["类别1"] = dt.Rows[n]["F_Value"].ToString();
                        drNew["当月数"] = M_count;
                        drNew["累计数"] = T_count;
                        dtNew.Rows.Add(drNew);
                    }
                }
                else
                {
                    //只有一个的
                    int T_count = WorkOrder.GetList(" F_IsDelete=0 and F_InfoType=" + row["F_ValueId"].ToString() + " and DateDiff(yy,F_RegDate,'" + date + "')=0 and DateDiff(d,F_RegDate,'" + date + "')>=0  and F_InfoConSmallType is not null ").Tables[0].Rows.Count;
                    int M_count = WorkOrder.GetList(" F_IsDelete=0 and F_InfoType=" + row["F_ValueId"].ToString() + " and DateDiff(mm,F_RegDate,'" + date + "')=0  and F_InfoConSmallType is not null ").Tables[0].Rows.Count;

                    DataRow drNew = dtNew.NewRow();
                    drNew["类别"] = row["F_Value"].ToString();
                    drNew["类别1"] = row["F_Value"].ToString();
                    drNew["当月数"] = M_count;
                    drNew["累计数"] = T_count;
                    dtNew.Rows.Add(drNew);
                }
            }

            DataSet _ds = DictionaryValue.GetList(" F_ItemId=6");
            foreach (DataRow row in _ds.Tables[0].Rows)
            {
                int T_count = WorkOrder.GetList(" F_InfoSource=" + row["F_ValueId"].ToString() + " and  DateDiff(yy,F_RegDate,'" + date + "')=0 and DateDiff(d,F_RegDate,'" + date + "')>=0").Tables[0].Rows.Count;
                int M_count = WorkOrder.GetList(" F_InfoSource=" + row["F_ValueId"].ToString() + " and  DateDiff(mm,F_RegDate,'" + date + "')=0").Tables[0].Rows.Count;

                DataRow drNew = dtNew.NewRow();
                drNew["类别"] = row["F_Value"].ToString();
                drNew["类别1"] = row["F_Value"].ToString();
                drNew["当月数"] = M_count;
                drNew["累计数"] = T_count;
                dtNew.Rows.Add(drNew);
            }

            int count1 = WorkOrder.GetList(" F_IsResult=1 and DateDiff(yy,F_RegDate,'" + date + "')=0 and DateDiff(d,F_RegDate,'" + date + "')>=0").Tables[0].Rows.Count;
            int countm1 = WorkOrder.GetList(" F_IsResult=1 and DateDiff(m,F_RegDate,'" + date + "')=0").Tables[0].Rows.Count;
            int count2 = WorkOrder.GetList(" F_IsResult=0 and DateDiff(yy,F_RegDate,'" + date + "')=0 and DateDiff(d,F_RegDate,'" + date + "')>=0").Tables[0].Rows.Count;
            int countm2 = WorkOrder.GetList(" F_IsResult=0 and DateDiff(m,F_RegDate,'" + date + "')=0").Tables[0].Rows.Count;

            DataRow drNew1 = dtNew.NewRow();
            drNew1["类别"] = "直办";
            drNew1["类别1"] = "直办";
            drNew1["当月数"] = countm1;
            drNew1["累计数"] = count1;
            dtNew.Rows.Add(drNew1);

            DataRow drNew2 = dtNew.NewRow();
            drNew2["类别"] = "督办";
            drNew2["类别1"] = "督办";
            drNew2["当月数"] = countm2;
            drNew2["累计数"] = count2;
            dtNew.Rows.Add(drNew2);

            NPOIHelper npoi = new NPOIHelper();
            if (npoi.TSExportToExcel(dtNew, tscount) == "")
            {
                return Success("导出成功");
            }
            else
            {
                return Error("导出失败");
            }

        }

        /// <summary>
        /// 逾期统计
        /// </summary>
        /// <returns></returns>
        [Authority]
        public ActionResult GetYQCountList(int isdc = 0, int deptid = 0)
        {
            string starttime = RequestString.GetQueryString("starttime");
            string endtime = RequestString.GetQueryString("endtime");
            if (string.IsNullOrEmpty(starttime))
            {
                starttime = DateTime.Now.ToString("yyyy-MM") + "-01";
            }
            if (string.IsNullOrEmpty(endtime))
            {
                endtime = DateTime.Now.ToString("yyyy-MM-dd");
            }
            starttime = starttime + " 00:00:00";
            endtime = endtime + " 23:59:59";
            string sqlwhere = "";
            if (User.F_RoleCode == "WLDW")
            {
                sqlwhere += "and a.F_MainDeptId='" + User.F_DeptId + "'";
            }
            else
            {
                if (deptid > 0)
                {
                    sqlwhere += "and a.F_MainDeptId='" + deptid + "'";
                }
            }
            string sql = "select * from(select dbo.GetDeptNames(F_MainDeptId) F_DeptName,F_WorkOrderId,F_ComContent,F_AssignTime,F_LimitTime,F_DealTime,(SELECT COUNT(1) FROM T_Sys_WorkOFFDays WITH(NOLOCK) WHERE F_OffState = 1 AND F_OffDate<(case when a.F_DealTime is null then GETDATE() else a.F_DealTime end) AND F_OffDate> a.F_LimitTime) F_OverDay from T_Bus_WorkOrder a WITH(NOLOCK)where a.F_IsDelete=0 "+ sqlwhere + " and a.F_IsResult != 1 and a.F_CreateTime >= '" + starttime + "' and a.F_CreateTime <= '"+ endtime + "' and F_MainDeptId> 0) b where b.F_OverDay > 0 order by b.F_DeptName";

            var dt = DbHelperSQL.Query(sql).Tables[0];

            string[] cols = { "单位", "编号", "内容", "转办日期", "应反馈日期", "反馈日期", "逾期天数" };
            if (isdc > 0)
            {
                NPOIHelper npoi = new NPOIHelper();

                if (npoi.ExportToExcel("逾期报表", dt, cols) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }

            var obj = new
            {
                cols = cols,
                data = dt
            };
            return Success("成功", obj);
        }

        /// <summary>
        /// 部门统计
        /// </summary>
        /// <returns></returns>
        public ActionResult GetDeptCountList()
        {
            string year = RequestString.GetQueryString("year");
            string month = RequestString.GetQueryString("month");
            if (string.IsNullOrEmpty(year))
            {
                year = DateTime.Now.ToString("yyyy");
            }
            if (string.IsNullOrEmpty(month))
            {
                month = DateTime.Now.ToString("MM");
            }

            string day = DateTime.Now.ToString("dd");
            if (year != DateTime.Now.ToString("yyyy") || month != DateTime.Now.ToString("MM"))
            {
                day = DateTime.DaysInMonth(int.Parse(year), int.Parse(month)).ToString();
            }
            string date = year + "." + month + "." + day;

            string bjmonth = DateTime.Now.AddMonths(-1).ToString("MM");
            string bjsdate = year + "-" + bjmonth + "-25 23:59:59";
            string bjedate = year + "-" + month + "-25 23:59:59";

            DataTable dt = new BLL.T_Sys_Department().GetAllList().Tables[0];
            dt.Columns.Add("slcount", typeof(int));
            dt.Columns.Add("cfjcount", typeof(int));
            dt.Columns.Add("yqcount", typeof(int));
            dt.Columns.Add("bjl", typeof(string));
            string regDate = new BLL.T_Sys_WorkCalendar().GetOverDay(date, "7", 0);
            foreach (DataRow dr in dt.Rows)
            {
                string did = dr["F_DeptId"].ToString();
                string sql = "select * from T_Bus_WorkOrder where F_IsDelete=0 and  DateDiff(yy,F_RegDate,'" + date + "')=0  and  DateDiff(m,F_RegDate,'" + date + "')=0 and F_WorkOrderId in (select F_WorkOrderId  from T_Bus_AssignedInfo where F_DeptId='" + did + "') ";
                var dtsl = DbHelperSQL.Query(sql).Tables[0];
                dr["slcount"] = dtsl.Rows.Count;
                dr["cfjcount"] = dtsl.Select(" F_InfoType='8'").Count();
                //dr["yqcount"] = WorkOrder.getOverDueWorkOrder(regDate, did).Rows.Count;

                string sql1 = "select * from T_Bus_WorkOrder where F_IsDelete=0 and  F_RegDate>'" + bjsdate + "'  and  F_RegDate<='" + bjedate + "' and F_WorkOrderId in (select F_WorkOrderId  from T_Bus_AssignedInfo where F_DeptId='" + did + "') ";
                var dtsl1 = DbHelperSQL.Query(sql1).Tables[0];
                var bj = dtsl1.Select(" F_WorkState='22'");

                double percent = Convert.ToDouble(bj.Count()) / Convert.ToDouble(dtsl1.Rows.Count);
                dr["bjl"] = string.Format("{0:0.00%}", percent);//得到5.88%
            }

            return Success("成功", dt);

        }

        /// <summary>
        /// 逾期工单报表
        /// </summary>
        /// <returns></returns>
        public ActionResult GetDeptYiQiList(int isdc = 0)
        {
            string starttime = RequestString.GetQueryString("starttime");
            string endtime = RequestString.GetQueryString("endtime");
            if (string.IsNullOrEmpty(starttime))
            {
                starttime = DateTime.Now.ToString("yyyy-MM") + "-01";
            }
            if (string.IsNullOrEmpty(endtime))
            {
                endtime = DateTime.Now.ToString("yyyy-MM-dd");
            }
            starttime = starttime + " 00:00:00";
            endtime = endtime + " 23:59:59";

            string sql = "select F_DeptName DeptName,Count(1) [Count] from(select dbo.GetDeptNames(F_MainDeptId) F_DeptName,F_WorkOrderId,F_ComContent,F_AssignTime,F_LimitTime,F_DealTime,(SELECT COUNT(1) FROM T_Sys_WorkOFFDays WITH(NOLOCK) WHERE F_OffState = 1 AND F_OffDate<(case when a.F_DealTime is null then GETDATE() else a.F_DealTime end) AND F_OffDate> a.F_LimitTime) F_OverDay from T_Bus_WorkOrder a WITH(NOLOCK)where a.F_IsDelete=0 and a.F_IsResult != 1 and a.F_CreateTime >= '" + starttime + "' and a.F_CreateTime <= '" + endtime + "' and F_MainDeptId> 0) b where b.F_OverDay > 0 Group By F_DeptName order by Count(1) desc";

            var dt = DbHelperSQL.Query(sql).Tables[0];

            string[] cols = { "交办单位", "逾期统计" };
            if (isdc > 0)
            {
                NPOIHelper npoi = new NPOIHelper();

                if (npoi.ExportToExcel("逾期统计", dt, cols) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }

            var obj = new
            {
                cols = cols,
                data = dt
            };
            return Success("成功", obj);
        }

        /// <summary>
        /// 办理情况统计(台账)
        /// </summary>
        /// <returns></returns>
        public ActionResult GetDealList(int isdc = 0)
        {
            int keyid = RequestString.GetInt("keyid", 0);
            string yearmonth = RequestString.GetQueryString("yearmonth");
            string month = RequestString.GetQueryString("month");
            if (string.IsNullOrEmpty(yearmonth))
            {
                yearmonth = DateTime.Now.ToString("yyyy-MM");
            }

            string sqlwhere = " and a.F_IsDelete=0 ";
            string sql = string.Empty;

            if (keyid != 0)
            {
                sql = " INNER JOIN [GetValueId]('" + keyid + "') Value on a.F_Key = Value.F_ValueId";
            }

            string starttime = yearmonth + "-01";
            string endtime = DateTime.Parse(starttime).AddMonths(1).ToString("yyyy-MM-dd");
            sqlwhere += " and a.F_CreateTime between '" + starttime + "' and '" + endtime + "'";

            string strpageindex = RequestString.GetQueryString("page");
            int pageindex = 1;
            string strpagesize = RequestString.GetQueryString("pagesize");
            int pagesize = 10;
            if (strpageindex.Trim() != "")
            {
                pageindex = Convert.ToInt32(strpageindex);
            }

            if (strpagesize.Trim() != "")
            {
                pagesize = Convert.ToInt32(strpagesize);
            }

            string[] colNames = { "序号", "受理编号", "办理部门", "办结时间", "内容摘要", "办理结果", "备注", "投诉人", "联系人" };

            if (isdc > 0)
            {
                string sqls = "select ROW_NUMBER() OVER (ORDER BY F_CreateTime asc) row,F_WorkOrderId, (case when F_IsResult=1 then '市长热线' else dbo.GetDeptNames(F_MainDeptId) end) F_DeptName,F_DealTime,F_ComContent,F_Result,dbo.GetDictionaryName(F_InfoSource) F_SourceName, F_CusPhone,F_ConPhone From T_Bus_WorkOrder a WITH(NOLOCK) " + sql + " where 1=1 " + sqlwhere;

                var dt = DbHelperSQL.Query(sqls).Tables[0];
                NPOIHelper npoi = new NPOIHelper();

                if (npoi.ExportToExcel(yearmonth + "台账", dt, colNames) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            else
            {
                string cols = "F_WorkOrderId,(case when F_IsResult=1 then '市长热线' else dbo.GetDeptNames(F_MainDeptId) end ) F_DeptName,F_DealTime,F_ComContent,F_Result,dbo.GetDictionaryName(F_InfoSource) F_SourceName, F_CusPhone,F_ConPhone";
                int recordCount = 0;
                var dt = BLL.PagerBLL.GetListPager(
                    "T_Bus_WorkOrder a WITH(NOLOCK)" + sql,
                    "F_Id",
                    cols,
                    sqlwhere,
                    "ORDER BY F_CreateTime DESC",
                    pagesize,
                    pageindex,
                    true,
                    out recordCount);

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