Autobio_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()
        {
            ActionResult res = NoToken("未知错误，请重新登录");
           
                String[] str = { "坐席人员", "通话总时长（小时）", "呼出通话总时长（小时）"
                        , "呼出通话占比", "呼入通话总时长", "呼入通话占比"
                        ,"平均日呼入通话时长（小时）","平均日呼出通话时长（小时）"
                };

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

     
            return res;
        }
        //获取数据
        public ActionResult GetDataList(string stime, string endtime, string dpt)
        {
            ActionResult res = NoToken("未知错误，请重新登录");
         
                DataTable dtNew = new DataTable();
                dtNew = getData(stime, endtime, dpt);
                #region
                //#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 , TalkStartTime, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
                //}
                //if (endtime != null && endtime.Trim() != "")
                //{
                //    sqltimeCallRecords += " and CONVERT(varchar , TalkStartTime, 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();
                //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 ";
                //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];

                //    if (dt1.Rows[0]["sums"] == null || dt1.Rows[0]["sums"].ToString() == "" || dt1.Rows[0]["sums"].ToString() == "0")
                //    {
                //        continue;
                //    }
                //    drNew["坐席人员"] = dt.Rows[i]["F_UserName"].ToString();
                //    drNew["通话总时长"] = (Convert.ToDouble(dt1.Rows[0]["sums"]) / 3600).ToString("f2");
                //    Double sums = 0;
                //    if (dt1.Rows[0]["sums"] != null && dt1.Rows[0]["sums"].ToString() != "")
                //    {
                //        zong1 += Convert.ToInt32(dt1.Rows[0]["sums"]);
                //        sums = Convert.ToInt32(dt1.Rows[0]["sums"]);
                //    }
                //    if (dt1.Rows[0]["sumshuchu"] == null || dt1.Rows[0]["sumshuchu"].ToString() == "")
                //    {
                //        drNew["呼出通话总时长"] = "0";
                //    }
                //    else
                //    {
                //        drNew["呼出通话总时长"] = (Convert.ToDouble(dt1.Rows[0]["sumshuchu"]) / 3600).ToString("f2");
                //    }
                //    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("f2");
                //    }
                //    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") + "%";
                //    }
                    
                //    drNew["平均日呼入通话时长"] = (sumshuru / day / 3600).ToString("f2");
                //    drNew["平均日呼出通话时长"] = (sumshuchu / day / 3600).ToString("f2");

                //    dtNew.Rows.Add(drNew);
                //}

                //DataRow drzj = dtNew.NewRow();
                //drzj["坐席人员"] = "总计";
                //drzj["通话总时长"] = (zong1 / 3600).ToString("f2");
                //drzj["呼出通话总时长"] = (zong2 / 3600).ToString("f2");
                //if (zong1 == 0 || zong2 == 0)
                //{
                //    drzj["呼出通话占比"] = "0.00" + "%";
                //}
                //else
                //{
                //    drzj["呼出通话占比"] = (Convert.ToDouble(zong2 / zong1) * 100).ToString("f2") + "%";
                //}
                //drzj["呼入通话总时长"] = (zong3 / 3600).ToString("f2");
                //if (zong1 == 0 || zong3 == 0)
                //{
                //    drzj["呼入通话占比"] = "0.00" + "%";
                //}
                //else
                //{
                //    drzj["呼入通话占比"] = (Convert.ToDouble(zong3 / zong1) * 100).ToString("f2") + "%";
                //}
                //drzj["平均日呼入通话时长"] = (zong3 / day / 3600).ToString("f2");
                //drzj["平均日呼出通话时长"] = (zong2 / day / 3600).ToString("f2");
                //dtNew.Rows.Add(drzj);
                #endregion
                res = Success("获取坐席日通话时长统计数据成功", dtNew);
         
            return res;
        }

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

        /// <summary>
        /// 2018-05-03 lihai 修改用户条件
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        private DataTable getData(string stime, string endtime, string 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 (!string.IsNullOrEmpty(dpt))
            {
                sqlUser += $" AND F_DeptId = {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;
        }
    }
}