Synear_API/codegit/CallCenterApi/CallCenterAp.../CallCenterAp.../Controllers/report/ZuoXiTongHua...

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
{
   // [Authority]
    public class ZuoXiTongHuaController : BaseController
    {
        //坐席日通话时长
        //获取表头
        public ActionResult GetColumnList()
        {
            if (Request.IsAuthenticated)
            {

                String[] str = { "坐席人员", "通话总时长（小时）", "呼出通话总时长（小时）"
                        , "呼出通话占比", "呼入通话总时长", "呼入通话占比"
                        ,"平均日呼入通话时长（小时）","平均日呼出通话时长（小时）"
                };

                return Success("获取坐席日通话时长统计表头成功", str);

            }
            return NoToken("未知错误，请重新登录");
        }
        //获取数据
        public ActionResult GetDataList(string stime, string endtime)
        {
            if (Request.IsAuthenticated)
            {
                int userId = CurrentUser.UserData.F_UserId;
                Model.T_Sys_UserAccount userModel = new BLL.T_Sys_UserAccount().GetModel(userId);

                DataTable dtNew = new DataTable();
                dtNew = getData(stime, endtime, userModel.F_SeartGroupID.Value);

                return Success("获取坐席日通话时长统计数据成功", dtNew);
            }
            return NoToken("未知错误，请重新登录");
        }

        //导出数据
        public ActionResult ExptList(string stime, string endtime)
        {
            if (Request.IsAuthenticated)
            {
                int userId = CurrentUser.UserData.F_UserId;
                Model.T_Sys_UserAccount userModel = new BLL.T_Sys_UserAccount().GetModel(userId);

                NPOIHelper npoi = new NPOIHelper();
                DataTable dt = getData(stime, endtime, userModel.F_SeartGroupID.Value);
                if (npoi.ExportToExcel("坐席日通话时长数据", dt) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
            return NoToken("未知错误，请重新登录");
        }

        /// <summary>
        /// 2018-05-03 lihai 修改用户条件
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        private DataTable getData(string stime, string endtime, int dpt)
        {
            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("呼入通话占比");
            DataColumn dc7 = new DataColumn("平均日呼入通话时长");
            DataColumn dc8 = 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);
            dtNew.Columns.Add(dc7);
            dtNew.Columns.Add(dc8);
            #endregion

            string sqltimeCallRecords = "";
            if (stime != null && stime.Trim() != "")
            {
                sqltimeCallRecords += " and CONVERT(varchar , BeginTime, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            }
            if (endtime != null && endtime.Trim() != "")
            {
                sqltimeCallRecords += " and CONVERT(varchar , BeginTime, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            }
            double zong1 = 0;
            double zong2 = 0;
            double zong3 = 0;
            double day = 0;
            Double sumshuchu = 0;
            Double sumshuru = 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();
            //2018-05-03 lihai 修改用户条件
            //string sql = "SELECT  *,(SELECT sum(TalkLongTime)  FROM T_Call_CallRecords where  UserId=T_Sys_UserAccount.F_Userid " + sqltimeCallRecords + ") as counts from T_Sys_UserAccount where F_RoleId in(1,28,29) order by counts desc ";
            //2018-07-06 lihai 部门搜索条件
            var sqlUser = "";
            if (dpt != 0)
            {
                sqlUser += " and F_SeartGroupID = '" + dpt + "' ";
            }
            string sql = "SELECT  *,(SELECT sum(TalkLongTime)  FROM T_Call_CallRecords where  UserId=T_Sys_UserAccount.F_Userid " + sqltimeCallRecords + ") as counts from T_Sys_UserAccount where f_seatFlag=1 and F_WorkNumber!='' " + sqlUser + " order by counts desc ";
            dt = DbHelperSQL.Query(sql).Tables[0];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow drNew = dtNew.NewRow();

                sql = "SELECT (SELECT sum(TalkLongTime)  FROM T_Call_CallRecords where  UserId=" + dt.Rows[i]["F_UserId"].ToString() + "" + sqltimeCallRecords + ") as sums,";
                sql += "(SELECT sum(TalkLongTime) FROM T_Call_CallRecords where  UserId=" + dt.Rows[i]["F_UserId"].ToString() + " and CallType=0" + sqltimeCallRecords + ") as sumshuru,";
                sql += "(SELECT sum(TalkLongTime)  FROM T_Call_CallRecords where  UserId=" + dt.Rows[i]["F_UserId"].ToString() + " and CallType=1" + sqltimeCallRecords + ") as sumshuchu";
                DataTable dt1 = new DataTable();
                dt1 = DbHelperSQL.Query(sql).Tables[0];

                object sumsobj = 0;
                if (dt1.Rows[0]["sums"] == null || dt1.Rows[0]["sums"].ToString() == "" || dt1.Rows[0]["sums"].ToString() == "0")
                {
                    //continue;
                }
                else {
                    sumsobj = dt1.Rows[0]["sums"];
                }
                drNew["坐席人员"] = dt.Rows[i]["F_UserName"].ToString();
                //drNew["通话总时长"] = (Convert.ToDouble(dt1.Rows[0]["sums"]) / 3600).ToString("f2");
                //drNew["通话总时长"] = (Convert.ToDouble(sumsobj) / 3600).ToString("0.0000");
                drNew["通话总时长"] = CallCenter.Utility.DateTimeConvert.parseTimeSeconds(Convert.ToInt32(sumsobj), 0);
                Double sums = 0;
                if (sumsobj != null && sumsobj.ToString() != "")
                {
                    zong1 += Convert.ToInt32(sumsobj);
                    sums = Convert.ToInt32(sumsobj);
                }
                if (dt1.Rows[0]["sumshuchu"] == null || dt1.Rows[0]["sumshuchu"].ToString() == "")
                {
                    drNew["呼出通话总时长"] = "0";
                }
                else
                {
                    //drNew["呼出通话总时长"] = (Convert.ToDouble(dt1.Rows[0]["sumshuchu"]) / 3600).ToString("0.0000");
                    drNew["呼出通话总时长"] = CallCenter.Utility.DateTimeConvert.parseTimeSeconds(Convert.ToInt32(dt1.Rows[0]["sumshuchu"]), 0);
                }
                sumshuchu = 0;
                if (dt1.Rows[0]["sumshuchu"] != null && dt1.Rows[0]["sumshuchu"].ToString() != "")
                {
                    zong2 += Convert.ToInt32(dt1.Rows[0]["sumshuchu"]);
                    sumshuchu = Convert.ToInt32(dt1.Rows[0]["sumshuchu"]);
                }
                if (sums == 0)
                {
                    drNew["呼出通话占比"] = "0.00" + "%";
                }
                else
                {
                    drNew["呼出通话占比"] = (sumshuchu / sums * 100).ToString("f2") + "%";
                }
                if (dt1.Rows[0]["sumshuru"] == null || dt1.Rows[0]["sumshuru"].ToString() == "")
                {
                    drNew["呼入通话总时长"] = "0";
                }
                else
                {
                    //drNew["呼入通话总时长"] = (Convert.ToDouble(dt1.Rows[0]["sumshuru"]) / 3600).ToString("0.0000");
                    drNew["呼入通话总时长"] = CallCenter.Utility.DateTimeConvert.parseTimeSeconds(Convert.ToInt32(dt1.Rows[0]["sumshuru"]), 0);
                }
                sumshuru = 0;

                if (dt1.Rows[0]["sumshuru"] != null && dt1.Rows[0]["sumshuru"].ToString() != "")
                {
                    zong3 += Convert.ToInt32(dt1.Rows[0]["sumshuru"]);
                    sumshuru = Convert.ToInt32(dt1.Rows[0]["sumshuru"]);
                }
                if (sums == 0)
                {
                    drNew["呼入通话占比"] = "0.00" + "%";
                }
                else
                {
                    drNew["呼入通话占比"] = (sumshuru / sums * 100).ToString("f2") + "%";
                }
                if (day == 0)
                {
                    drNew["平均日呼入通话时长"] = "0";
                    drNew["平均日呼出通话时长"] = "0";
                }
                else
                {
                    //drNew["平均日呼入通话时长"] = (sumshuru / day ).ToString("0.0000");    
                    //drNew["平均日呼出通话时长"] = (sumshuchu / day).ToString("0.0000");

                    drNew["平均日呼入通话时长"] = CallCenter.Utility.DateTimeConvert.parseTimeSeconds(Convert.ToInt32(sumshuru / day), 0);
                    drNew["平均日呼出通话时长"] = CallCenter.Utility.DateTimeConvert.parseTimeSeconds(Convert.ToInt32(sumshuchu / day), 0);
                }
                dtNew.Rows.Add(drNew);
            }

            DataRow drzj = dtNew.NewRow();
            drzj["坐席人员"] = "总计";
            //drzj["通话总时长"] = (zong1 / 3600).ToString("0.0000");
            //drzj["呼出通话总时长"] = (zong2 / 3600).ToString("0.0000");

            drzj["通话总时长"] = CallCenter.Utility.DateTimeConvert.parseTimeSeconds(Convert.ToInt32(zong1), 0);
            drzj["呼出通话总时长"] = CallCenter.Utility.DateTimeConvert.parseTimeSeconds(Convert.ToInt32(zong2), 0);
            if (zong1 == 0 || zong2 == 0)
            {
                drzj["呼出通话占比"] = "0.00" + "%";
            }
            else
            {
                drzj["呼出通话占比"] = (Convert.ToDouble(zong2 / zong1) * 100).ToString("f2") + "%";
            }
            //drzj["呼入通话总时长"] = (zong3 / 3600).ToString("0.0000");
            drzj["呼入通话总时长"] = CallCenter.Utility.DateTimeConvert.parseTimeSeconds(Convert.ToInt32(zong3), 0);
            if (zong1 == 0 || zong3 == 0)
            {
                drzj["呼入通话占比"] = "0.00" + "%";
            }
            else
            {
                drzj["呼入通话占比"] = (Convert.ToDouble(zong3 / zong1) * 100).ToString("f2") + "%";
            }
            if (day == 0)
            {
                drzj["平均日呼入通话时长"] = "0";
                drzj["平均日呼出通话时长"] = "0";
            }
            else
            {
                //drzj["平均日呼入通话时长"] = (zong3 / day / 3600).ToString("0.0000");
                //drzj["平均日呼出通话时长"] = (zong2 / day / 3600).ToString("0.0000");

                drzj["平均日呼入通话时长"] = CallCenter.Utility.DateTimeConvert.parseTimeSeconds(Convert.ToInt32(zong3 / day), 0);
                drzj["平均日呼出通话时长"] = CallCenter.Utility.DateTimeConvert.parseTimeSeconds(Convert.ToInt32(zong2 / day), 0);
            }
            dtNew.Rows.Add(drzj);
            return dtNew;
        }
    }
}