Autobio_API/codegit/CallCenterApi/CallCenterAp.../CallCenterAp.../Controllers/report/GDLYControll...

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.report
{
    public class GDLYController : BaseController
    {
        //// GET: GDLY
        //public ActionResult Index()
        //{
        //    return View();
        //}
        /// <summary>
        /// 获取工单来源报表
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public ActionResult GetDataList(string stime, string endtime)
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            DataTable dtNew = new DataTable();
            dtNew = getData(stime, endtime);
            res = Success("获取工单来源数据成功", dtNew);

            return res;
        }

        private DataTable getData(string stime, string endtime)
        {
            DataTable dtNew = new DataTable();
            #region 添加表格标题
            DataColumn dc = new DataColumn("坐席人员");
            dtNew.Columns.Add(dc);
            var sqlGDLY = " and F_State=1 and F_DictionaryFlag='GDLY' ";
            var recordCount = 0;
            var dtGDLY = BLL.PagerBLL.GetListPager(
              "T_Sys_DictionaryValue",
              "F_DictionaryValueId",
              "*",
              sqlGDLY,
              "ORDER BY F_Sort ",
              10,
              1,
              true,
              out recordCount);
            List<Model.T_Sys_DictionaryValue> modelList = new BLL.T_Sys_DictionaryValue().DataTableToList(dtGDLY);

            for (int i = 0; i < modelList.Count; i++)
            {
                string colname = modelList[i].F_Name;
                dtNew.Columns.Add(new DataColumn(colname));
            }
            #endregion

            string sqltimeCallRecords = "";
            if (stime != null && stime.Trim() != "")
            {
                sqltimeCallRecords += " and CONVERT(varchar , CreateTime, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sqltimeCallRecords += " and CONVERT(varchar , CreateTime, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }

            double day = 0;
            if (stime != null && endtime != null && endtime.Trim() != "" && stime.Trim() != "")
            {
                TimeSpan time = Convert.ToDateTime(endtime) - Convert.ToDateTime(stime);
                day = time.Days + 1;
            }
            //获取坐席数据
            DataTable dt = new DataTable();
            string sql = "SELECT * from T_Sys_UserAccount where F_DeleteFlag=0 order by F_Userid asc";
            dt = DbHelperSQL.Query(sql).Tables[0];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow drNew = dtNew.NewRow();
                drNew["坐席人员"] = dt.Rows[i]["F_UserName"].ToString();
                //获取工单来源数据
                DataTable dtnew = new DataTable();
                string sqlnew = "select * from T_Sys_DictionaryValue where F_State=1 and F_DictionaryFlag='GDLY'";
                dtnew = DbHelperSQL.Query(sqlnew).Tables[0];
                for (int j = 0; j < dtnew.Rows.Count; j++)
                {
                    string str = "select count(*) from T_Wo_WorkOrder where IsDel=0 and type=" + Convert.ToInt32(dtnew.Rows[j]["F_DictionaryValueId"].ToString()) + " and CreateUserID='" + dt.Rows[i]["F_Userid"].ToString() + "'" + sqltimeCallRecords;
                    DataTable dtj = DbHelperSQL.Query(str).Tables[0];
                    int sum = 0;
                    if (dtj.Rows[0][0] != null && dtj.Rows[0][0].ToString() != "")
                    {
                        sum = Convert.ToInt32(dtj.Rows[0][0]);
                    }
                    drNew[j + 1] = sum.ToString();
                }
                
                dtNew.Rows.Add(drNew);
            }
            
            return dtNew;
        }

        /// <summary>
        /// 导出excel
        /// </summary>
        /// <returns></returns>
        public ActionResult ExportExcel(string stime, string endtime)
        {
            ActionResult res = NoToken("未知错误，请重新登录");
            if (Request.IsAuthenticated)
            {
                //导出dtnew
                NPOIHelper npoi = new NPOIHelper();
                DataTable dt = getData(stime, endtime);
                if (npoi.ExportToExcel("工单来源数据报表", dt, null) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            return res;
        }
    }
}