XYUpgrade_API/codegit/CallCenterApi/CallCenterAp.../CallCenterAp.../Controllers/report/CallBusyCont...

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 CallBusyController : BaseController
    {
        // 置忙统计表
        //获取表头
        public ActionResult GetColumnList()
        {
            return Success("获取置忙统计报表表头成功", getcol());
        }
        //获取数据
        public ActionResult GetDataList(string stime, string endtime)
        {
            DataTable dtNew = new DataTable();
            dtNew = getData(stime, endtime);
            return Success("获取置忙统计报表数据成功", dtNew);
        }
        //导出数据
        public ActionResult ExptList(string stime, string endtime)
        {
            if (Request.IsAuthenticated)
            {
                NPOIHelper npoi = new NPOIHelper();
                DataTable dt = getData(stime, endtime);
                if (npoi.ExportToExcel("置忙统计表", dt, getcol()) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            return NoToken("未知错误，请重新登录");
        }
        private string[] getcol()
        {
            String[] str = { "工号", "姓名", "在线时长", "置忙次数", "置忙时长", "置忙/在线时间比" };
            return str;
        }
        private DataTable getData(string stime, string endtime)
        {
            DataTable dtNew = new DataTable();
            #region
            DataColumn dc1 = new DataColumn("工号");
            DataColumn dc2 = new DataColumn("姓名");
            DataColumn dc3 = new DataColumn("在线时长");
            DataColumn dc4 = new DataColumn("置忙次数");
            DataColumn dc5 = new DataColumn("置忙时长");
            DataColumn dc6 = new DataColumn("时间比");

            dtNew.Columns.Add(dc1);
            dtNew.Columns.Add(dc2);
            dtNew.Columns.Add(dc3);
            dtNew.Columns.Add(dc4);
            dtNew.Columns.Add(dc5);
            dtNew.Columns.Add(dc6);
            #endregion

            int SummaryOnlineTotalTime = 0;
            int CallNoTotalData = 0;
            int CallNoTotalSummaryTime = 0;
            #region 时间搜索
            string sqllog = "", sqlzm = "";
            if (!string.IsNullOrWhiteSpace(stime))
            {
                sqlzm += " and DATEDIFF(d,F_OptOn,'" + stime + "')<=0";
                sqllog += " and datediff(d,F_LoginOutDate,'" + stime + "')<=0";
            }
            if (!string.IsNullOrWhiteSpace(endtime))
            {
                sqlzm += " and DATEDIFF(d,F_OptOn,'" + endtime + "')>=0";
                sqllog += " and datediff(d,F_LoginOutDate,'" + endtime + "')>=0";
            }
            #endregion

            #region 根据条件获取所需数据
            var userAccountList = new BLL.T_Sys_UserAccount().GetModelList("F_DeleteFlag=0 AND F_SeatRight=0");
            DataTable dtno = DbHelperSQL.Query("select F_OptTime as TalkNoTotalTime,* from T_Sys_CallOptLogs where F_State=2 and F_OptBy in (select F_UserId from T_Sys_UserAccount where F_DeleteFlag=0 AND F_SeatRight=0)" + sqlzm).Tables[0];

            DataTable dtLoginLog = DbHelperSQL.Query("SELECT ISNULL(DATEDIFF(s,F_LoginDate,F_LoginOutDate),0) as SummaryOnLine,* FROM dbo.T_Sys_LoginLogs where F_LoginId in (select F_UserId from T_Sys_UserAccount where F_DeleteFlag=0 and  F_SeatRight=0) " + sqllog + " ORDER BY F_LoginDate DESC ").Tables[0];
            #endregion
            foreach (Model.T_Sys_UserAccount item in userAccountList)
            {
                #region 计算所需数据
                int CallNoNum = dtno.AsEnumerable().Count(dr => dr.Field<int>("F_OptBy") == item.F_UserId);
                int CallNoTotalTime = dtno.AsEnumerable().Where(dr => dr.Field<int>("F_OptBy") == item.F_UserId).Sum(dri => dri.Field<int>("TalkNoTotalTime"));
                int SummaryOnlineTime = dtLoginLog.AsEnumerable().Where(dr => dr.Field<int>("F_LoginId") == item.F_UserId).Sum(dri => dri.Field<int>("SummaryOnLine"));

                SummaryOnlineTotalTime += SummaryOnlineTime;
                CallNoTotalData += CallNoNum;
                CallNoTotalSummaryTime += CallNoTotalTime;
                #endregion
                #region 数据提交到dt
                DataRow drNew = dtNew.NewRow();
                drNew["工号"] = item.F_UserCode;
                drNew["姓名"] = item.F_UserName;
                drNew["在线时长"] = DateTimeConvert.parseTimeSeconds(SummaryOnlineTime, 1);
                drNew["置忙次数"] = CallNoNum;
                drNew["置忙时长"] = DateTimeConvert.parseTimeSeconds(CallNoTotalTime, 1);
                drNew["时间比"] = SummaryOnlineTime == 0 ? "-" : (CallNoTotalTime / decimal.Parse(SummaryOnlineTime.ToString()) * 100).ToString("f") + "%";
                dtNew.Rows.Add(drNew);
                #endregion
            }

            #region 合计
            DataRow drtotal = dtNew.NewRow();
            drtotal["工号"] = "总计";
            drtotal["姓名"] = "";
            drtotal["在线时长"] = DateTimeConvert.parseTimeSeconds(SummaryOnlineTotalTime, 1);
            drtotal["置忙次数"] = CallNoTotalData;
            drtotal["置忙时长"] = DateTimeConvert.parseTimeSeconds(CallNoTotalSummaryTime, 1);
            drtotal["时间比"] = SummaryOnlineTotalTime == 0 ? "-" : (CallNoTotalSummaryTime / decimal.Parse(SummaryOnlineTotalTime.ToString()) * 100).ToString("f") + "%";
            dtNew.Rows.Add(drtotal);
            #endregion

            return dtNew;
        }
    }
}