using Newtonsoft.Json.Linq;
using RMYY_CallCenter_Api.DB;
using RMYY_CallCenter_Api.Utility;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace RMYY_CallCenter_Api.Controllers.Quality
{
    public class TalkTimeController : BaseController
    {
        //通话时长统计

        //获取表头
        public ActionResult GetColumnList()
        {
            ActionResult res = NoToken("未知错误，请重新登录");

            String[] str = { "坐席名称", "呼入次数", "呼入时长", "呼出次数", "呼出时长", "呼出未接通次数", "振铃时长", "通话总时长", "平均通话时长" };
            res = Success("获取通话时长统计表头成功", str);

            return res;
        }

        //获取数据
        public ActionResult GetDataList(string stime, string endtime)
        {
            ActionResult res = NoToken("未知错误，请重新登录");

            DataTable dtNew = new DataTable();
            dtNew = GetData(stime, endtime);
            #region
            //DataTable dtNew = new DataTable();
            //#region 编辑表头
            //DataColumn dc1 = new DataColumn("坐席名称", Type.GetType("System.String"));
            //DataColumn dc2 = new DataColumn("呼入次数", Type.GetType("System.String"));
            //DataColumn dc3 = new DataColumn("呼入时长", Type.GetType("System.String"));
            //DataColumn dc4 = new DataColumn("呼出次数", Type.GetType("System.String"));
            //DataColumn dc5 = new DataColumn("呼出时长", Type.GetType("System.String"));
            //DataColumn dc6 = new DataColumn("呼出未接通次数", Type.GetType("System.String"));
            //DataColumn dc7 = new DataColumn("振铃时长", Type.GetType("System.String"));
            //DataColumn dc8 = new DataColumn("通话总时长", Type.GetType("System.String"));
            //DataColumn dc9 = new DataColumn("平均通话总时长", Type.GetType("System.String"));

            //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);
            //dtNew.Columns.Add(dc9);
            //#endregion

            //string[] CallInTime = GetCallRecordIn(stime, endtime, "0").TrimStart('[').TrimEnd(']').Split(',');
            //string[] CallInTimes = GetCallRecordTotalTime(stime, endtime, "0").TrimStart('[').TrimEnd(']').Split(',');
            //string[] CallOutTime = GetCallRecordOut(stime, endtime, "1","").TrimStart('[').TrimEnd(']').Split(',');
            //string[] CallOutTimes = GetCallRecordTotalTime(stime, endtime, "1").TrimStart('[').TrimEnd(']').Split(',');
            //string[] CallOutWTime = GetCallRecordOut(stime, endtime, "1", "0").TrimStart('[').TrimEnd(']').Split(',');
            //string[] CallOutWTimes = GetCallRecordRingTime(stime, endtime).TrimStart('[').TrimEnd(']').Split(',');
            //string[] CallTotalTime = GetCallRecordTotalTime(stime, endtime, "").TrimStart('[').TrimEnd(']').Split(',');
            //string[] AgentArr = GetAgent().TrimStart('[').TrimEnd(']').Split(',');
            //string[] AverageTime = GetCallAverageTime(stime, endtime).TrimStart('[').TrimEnd(']').Split(',');

            //for (int i = 0; i < AgentArr.Length; i++)
            //{
            //    DataRow drNew = dtNew.NewRow();

            //    drNew["坐席名称"] = AgentArr[i].Replace('\'', ' ');
            //    drNew["呼入次数"] = CallInTime[i];
            //    drNew["呼入时长"] = CallInTimes[i];
            //    drNew["呼出次数"] = CallOutTime[i];
            //    drNew["呼出时长"] = CallOutTimes[i];
            //    drNew["呼出未接通次数"] = CallOutWTime[i];
            //    drNew["振铃时长"] = CallOutWTimes[i];
            //    drNew["通话总时长"] = CallTotalTime[i];
            //    drNew["平均通话总时长"] = AverageTime[i];

            //    dtNew.Rows.Add(drNew);
            //}
            #endregion
            res = Success("获取通话时长数据成功", dtNew);

            return res;
        }

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

        //获取数据源
        private DataTable GetData(string stime, string endtime)
        {
            DataTable dtNew = new DataTable();
            #region 编辑表头
            DataColumn dc1 = new DataColumn("坐席名称", Type.GetType("System.String"));
            DataColumn dc2 = new DataColumn("呼入次数", Type.GetType("System.String"));
            DataColumn dc3 = new DataColumn("呼入时长", Type.GetType("System.String"));
            DataColumn dc4 = new DataColumn("呼出次数", Type.GetType("System.String"));
            DataColumn dc5 = new DataColumn("呼出时长", Type.GetType("System.String"));
            DataColumn dc6 = new DataColumn("呼出未接通次数", Type.GetType("System.String"));
            DataColumn dc7 = new DataColumn("振铃时长", Type.GetType("System.String"));
            DataColumn dc8 = new DataColumn("通话总时长", Type.GetType("System.String"));
            DataColumn dc9 = new DataColumn("平均通话总时长", Type.GetType("System.String"));

            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);
            dtNew.Columns.Add(dc9);
            #endregion

            string[] CallInTime = GetCallRecordIn(stime, endtime, "0").TrimStart('[').TrimEnd(']').Split(',');
            string[] CallInTimes = GetCallRecordTotalTime(stime, endtime, "0").TrimStart('[').TrimEnd(']').Split(',');
            string[] CallOutTime = GetCallRecordOut(stime, endtime, "1", "").TrimStart('[').TrimEnd(']').Split(',');
            string[] CallOutTimes = GetCallRecordTotalTime(stime, endtime, "1").TrimStart('[').TrimEnd(']').Split(',');
            string[] CallOutWTime = GetCallRecordOut(stime, endtime, "1", "0").TrimStart('[').TrimEnd(']').Split(',');
            string[] CallOutWTimes = GetCallRecordRingTime(stime, endtime).TrimStart('[').TrimEnd(']').Split(',');
            string[] CallTotalTime = GetCallRecordTotalTime(stime, endtime, "").TrimStart('[').TrimEnd(']').Split(',');
            string[] AgentArr = GetAgent().TrimStart('[').TrimEnd(']').Split(',');
            string[] AverageTime = GetCallAverageTime(stime, endtime).TrimStart('[').TrimEnd(']').Split(',');

            for (int i = 0; i < AgentArr.Length; i++)
            {
                DataRow drNew = dtNew.NewRow();

                drNew["坐席名称"] = AgentArr[i].Replace('\'', ' ');
                drNew["呼入次数"] = CallInTime[i];
                drNew["呼入时长"] = CallInTimes[i];
                drNew["呼出次数"] = CallOutTime[i];
                drNew["呼出时长"] = CallOutTimes[i];
                drNew["呼出未接通次数"] = CallOutWTime[i];
                drNew["振铃时长"] = CallOutWTimes[i];
                drNew["通话总时长"] = CallTotalTime[i];
                drNew["平均通话总时长"] = AverageTime[i];

                dtNew.Rows.Add(drNew);
            }

            return dtNew;
        }

        #region
        /// <summary>
        /// 获取坐席的呼出次数
        /// </summary>
        /// <param name="BeginTime">开始时间</param>
        /// <param name="EndTime">结束时间</param>
        /// <param name="Tag">1表示呼出，0表示呼入</param>
        /// <returns></returns>
        public string GetCallRecordOut(string BeginTime, string EndTime, string Tag, string state)
        {
            return GetHighChartData(BeginTime, EndTime, Tag, state);
        }
        /// <summary>
        /// 获取通话总时长
        /// </summary>
        /// <param name="BeginTime">开始时间</param>
        /// <param name="EndTime">结束时间</param>
        /// <returns></returns>
        public string GetCallRecordTotalTime(string BeginTime, string EndTime, string tag)
        {
            string result = "[";
            string sql = "";

            if (BeginTime != null && BeginTime != "")
            {
                sql += " and DATEDIFF(d,BeginTime,'" + BeginTime + "')<=0";
            }
            if (EndTime != null && EndTime != "")
            {
                sql += " and DATEDIFF(d,BeginTime,'" + EndTime + "')>=0";
            }
            if (tag != "")
            {
                sql += " and CallType=" + tag;
            }
            var userAccountList = new Bll.T_Sys_UserAccount().GetModelList(" F_SeatFlag=1");
            //DataTable dt = DbHelperSQL.Query("SELECT UserId,UserCode,UserName,COUNT(*) AS calloutCount,SUM(TalkLongTime) AS TalkSummaryTime FROM T_Call_CallRecords where 1=1 " + sql + "  GROUP BY UserId,UserCode,UserName").Tables[0];
            //DataTable dt = DbHelperSQL.Query("SELECT UserId,UserCode,UserName,COUNT(*) AS calloutCount FROM T_Call_CallRecords where 1=1 " + sql + "  GROUP BY UserId,UserCode,UserName").Tables[0];
            var recordstr = " select users.F_UserId,users.F_UserCode,users.F_UserName,COUNT(*) AS calloutCount,SUM(TalkLongTime) AS TalkSummaryTime from T_Sys_UserAccount users left join T_Call_CallRecords records on users.F_UserCode = records.UserCode and 1=1 " + sql + " GROUP BY users.F_UserId,users.F_UserCode,users.F_UserName";
            DataTable dt = DbHelperSQL.Query(recordstr).Tables[0];

            foreach (Model.T_Sys_UserAccount item in userAccountList)
            {
                DataRow drUser = dt.AsEnumerable().FirstOrDefault(dr => dr.Field<string>("F_UserCode") == item.F_UserCode);

                if (drUser != null && drUser[4].ToString() != "")
                    result += drUser[4].ToString() + ",";
                else
                    result += "0,";
            }

            result = result.TrimEnd(',');
            return result + "]";
        }
        /// <summary>
        /// 获取坐席名称（x轴数据）
        /// </summary>
        /// <returns></returns>
        public string GetAgent()
        {
            string result = "[";
            var userAccountList = new Bll.T_Sys_UserAccount().GetModelList(" F_SeatFlag=1");
            foreach (Model.T_Sys_UserAccount item in userAccountList)
            {
                result += "'" + item.F_UserName + "',";
            }
            result = result.TrimEnd(',');
            return result + "]";
        }
        /// <summary>
        /// 获取通话平均时长
        /// </summary>
        /// <param name="BeginTime">开始时间</param>
        /// <param name="EndTime">结束时间</param>
        /// <returns></returns>
        public string GetCallAverageTime(string BeginTime, string EndTime)
        {
            string result = "[";

            string[] CallInTime = GetCallRecordIn(BeginTime, EndTime, "0").TrimStart('[').TrimEnd(']').Split(',');
            string[] CallOutTime = GetCallRecordOut(BeginTime, EndTime, "1", "").TrimStart('[').TrimEnd(']').Split(',');

            string[] CallTotalTime = GetCallRecordTotalTime(BeginTime, EndTime, "").TrimStart('[').TrimEnd(']').Split(',');

            for (int i = 0; i < CallInTime.Length; i++)
            {
                double subt = 0.0;
                if (double.Parse(CallInTime[i]) != 0)
                {
                    subt += double.Parse(CallInTime[i]);
                }
                if (double.Parse(CallOutTime[i]) != 0)
                {
                    subt += double.Parse(CallOutTime[i]);
                }
                if (subt != 0)
                    result += (double.Parse(CallTotalTime[i]) / subt).ToString("F2") + ",";
                else
                    result += "0,";
            }
            result = result.TrimEnd(',');
            return result + "]";
        }
        /// <summary>
        /// 获取坐席的呼入次数
        /// </summary>
        /// <param name="BeginTime">开始时间</param>
        /// <param name="EndTime">结束时间</param>
        /// <returns></returns>
        public string GetCallRecordIn(string BeginTime, string EndTime, string Tag)
        {
            return GetHighChartData(BeginTime, EndTime, Tag, "");
        }
        /// <summary>
        /// 获取呼入呼出数据
        /// </summary>
        /// <param name="BeginTime">开始时间</param>
        /// <param name="EndTime">结束时间</param>
        /// <param name="Tag">1表示呼出，0表示呼入</param>
        /// <returns></returns>
        private string GetHighChartData(string BeginTime, string EndTime, string Tag, string state)
        {
            string result = "[";
            string sql = " and CallType=" + Tag; // 呼入
            var userAccountList = new Bll.T_Sys_UserAccount().GetModelList(" F_SeatFlag=1");

            if (!string.IsNullOrEmpty(BeginTime))
            {
                sql += " and BeginTime>='" + BeginTime + " 00:00:00'";
                //sql += " and DATEDIFF(d,BeginTime,'" + BeginTime + "')<=0";
            }
            if (!string.IsNullOrEmpty(EndTime))
            {
                sql += " and BeginTime<='" + EndTime + " 23:59:59'";
                //sql += " and DATEDIFF(d,BeginTime,'" + EndTime + "')>=0";
            }
            if (state.Trim() != "")
            {
                sql += " and CallState=" + state.Trim();
            }

            //DataTable dt = DbHelperSQL.Query("SELECT UserId,UserCode,UserName,COUNT(*) AS calloutCount FROM T_Call_CallRecords where 1=1 " + sql + "  GROUP BY UserId,UserCode,UserName").Tables[0];
            //var recordstr = " select users.F_UserId,users.F_UserCode,users.F_UserName,COUNT(*) AS calloutCount from T_Sys_UserAccount users right join T_Call_CallRecords records on users.F_UserCode = records.UserCode and 1=1 " + sql + " GROUP BY users.F_UserId,users.F_UserCode,users.F_UserName";
            //20180509 通话时长统计不对  machenyang
            var recordstr = " select records.UserCode,records.UserId,records.UserName,COUNT(*) from T_Call_CallRecords records where 1=1 " + sql + "  and UserCode is not null group by records.UserCode,records.UserId,records.UserName";
            DataTable dt = DbHelperSQL.Query(recordstr).Tables[0];
            Dictionary<string, int> myDictionary = new Dictionary<string, int>();

            foreach (Model.T_Sys_UserAccount item in userAccountList)
            {
                if (!myDictionary.ContainsKey(item.F_UserCode))
                    myDictionary.Add(item.F_UserCode, 0);

                DataRow drUser = dt.AsEnumerable().FirstOrDefault(dr => dr.Field<string>("UserCode") == item.F_UserCode);

                if (drUser != null)
                {
                    myDictionary[item.F_UserCode] += int.Parse(drUser[3].ToString());
                }
            }

            foreach (var item in myDictionary)
            {
                result += item.Value + ",";
            }

            result = result.TrimEnd(',');
            return result + "]";
        }
        /// <summary>
        /// 获取振铃时长
        /// </summary>
        /// <param name="BeginTime"></param>
        /// <param name="EndTime"></param>
        /// <returns></returns>
        public string GetCallRecordRingTime(string BeginTime, string EndTime)
        {
            string result = "[";
            string sql = "";

            if (!string.IsNullOrEmpty(BeginTime))
            {
                sql += " and BeginTime>='" + BeginTime + "'";
                //sql += " and DATEDIFF(d,BeginTime,'" + BeginTime + "')<=0";
            }
            if (!string.IsNullOrEmpty(EndTime))
            {
                sql += " and BeginTime<='" + EndTime + "'";
                //sql += " and DATEDIFF(d,BeginTime,'" + EndTime + "')>=0";
            }
            //只统计呼入振铃时长
            sql += " and CallType=0 ";

            var userAccountList = new Bll.T_Sys_UserAccount().GetModelList(" F_SeatFlag=1");
            DataTable dt = DbHelperSQL.Query("  select UserCode,COUNT(1) count,SUM(Ringtimes) as RingTimes from(SELECT UserCode, datediff(second, RingStartTime, RingEndTime) as Ringtimes FROM T_Call_CallRecords where 1 = 1 " + sql + ") t GROUP BY UserCode").Tables[0];

            foreach (Model.T_Sys_UserAccount item in userAccountList)
            {
                DataRow drUser = dt.AsEnumerable().FirstOrDefault(dr => dr.Field<string>("UserCode") == item.F_UserCode);

                if (drUser != null && drUser[2].ToString() != "")
                    result += drUser[2].ToString() + ",";
                else
                    result += "0,";
            }

            result = result.TrimEnd(',');
            return result + "]";
        }
        #endregion

        //话务工作报表


        //groupby 0是坐席 1 是分机号

        public ActionResult GetUserCallReport(string stime, string endtime, int isdc = 0,int groupby=0)
        {

            NPOIHelper npoi = new NPOIHelper();
            DataTable dt = GetUserCallReport(stime, endtime,groupby);

            if (isdc == 0)
            {
                return Success("话务工作报表数据成功", dt);
            }
            else
            {
                if (npoi.ExportToExcel("话务工作报表", dt, colsusercallreport) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }

        }
        public string[] colsusercallreport = { "坐席名称", "呼入次数", "呼入时长", "呼出次数", "呼出时长", "呼出未接通次数", "振铃时长", "通话总时长", "平均通话总时长" };
        private DataTable GetUserCallReport(string stime, string endtime,int groupby=0)
        {
            string sql = "";
            string groupbysql = "";

            if (groupby == 0)
            {
                sql = "  SELECT ExtNumber username,";
                groupbysql = " group by ExtNumber";
            }

            else
            {
                sql = "SELECT dbo.GetUserName(UserCode) +'('+usercode+')'  username,";
                groupbysql = " group by UserCode ";


            }

            string wheresql = "";
            if (!string.IsNullOrEmpty(stime))
            {
                wheresql += " and BeginTime>='" + stime + " 00:00:00'";
                
            }
            if (!string.IsNullOrEmpty(endtime))
            {
                wheresql += " and BeginTime<='" + endtime + " 23:59:59'";

            }
             sql += "  sum (case when CallType = 0 then 1 else 0 end) hurucishu ,sum(case when CallType = 1 then 0 else TalkLongTime end)   hurushichang, sum(case when CallType = 1 then 1 else 0 end)  huchucishu ,  sum(case when CallType = 0 then 0 else TalkLongTime end)  huchushichang , SUM(case when CallType = 1 and CallState = 0 then 1 else 0 end) huchuweijietongcishu, SUM(RingLongTime) zhenlingshichang, SUM(TalkLongTime) tonghuazongshichang, SUM(TalkLongTime) / COUNT(1) pingjuntonghuashichang     FROM[RMYY_CallCenter].[dbo].[T_Call_CallRecords]        where UserCode is not null " + wheresql+ groupbysql;

           return  DbHelperSQL.Query(sql).Tables[0];


           
        }



        //获取坐席工作数据
        public ActionResult  ZuoXiGZGetDataList(string stime, string endtime, string dpt, string dayss,string usercode,int isExport=0)
        {
            DataTable dtNew = new DataTable();
            dtNew = getData(stime, endtime, dpt, dayss,usercode);
            if (isExport > 0)
            {
                NPOIHelper npoi = new NPOIHelper();
           
                if (npoi.ExportToExcel("坐席工作数据", dtNew) == "")
                {
                   return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }
                    

            return Success("获取坐席工作统计数据成功", dtNew);
        }

        ////导出数据
        //public ActionResult ExptList(string stime, string endtime, string dpt, string dayss)
        //{
        //    NPOIHelper npoi = new NPOIHelper();
        //    DataTable dt = getData(stime, endtime, dpt, dayss);
        //    if (npoi.ExportToExcel("坐席工作数据", dt) == "")
        //    {
        //        return Success("导出成功");
        //    }
        //    else
        //    {
        //        return Error("导出失败");
        //    }
        //}

        private DataTable getData(string stime, string endtime, string dpt, string dayss,string usercode)
        {
            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("呼损率");
            DataColumn dc9 = new DataColumn("平均排队时间");
            DataColumn dc10 = new DataColumn("接听通话次数");
            DataColumn dc11 = new DataColumn("通话分钟数");
            DataColumn dc12 = new DataColumn("通话秒数");
            DataColumn dc13 = new DataColumn("平均接听通话秒数");
            DataColumn dc14 = new DataColumn("平均振铃秒数");
            DataColumn dc15 = new DataColumn("呼入占有率");
            DataColumn dc16 = new DataColumn("用户评价");
            DataColumn dc17 = new DataColumn("坐席登录次数");
            DataColumn dc18 = new DataColumn("登录时长分钟数");
            DataColumn dc19 = new DataColumn("工作时长分钟数");
            DataColumn dc20 = new DataColumn("平均每天工作时长");
            //DataColumn dc21 = new DataColumn("平均操作分钟数");
            DataColumn dc22 = new DataColumn("置忙次数");
            DataColumn dc23 = new DataColumn("休息时长分钟数");
            DataColumn dc24 = new DataColumn("平均每天休息时长");
            DataColumn dc25 = 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);
            dtNew.Columns.Add(dc9);
            dtNew.Columns.Add(dc10);
            dtNew.Columns.Add(dc11);
            dtNew.Columns.Add(dc12);
            dtNew.Columns.Add(dc13);
            dtNew.Columns.Add(dc14);
            dtNew.Columns.Add(dc15);
            dtNew.Columns.Add(dc16);
            dtNew.Columns.Add(dc17);
            dtNew.Columns.Add(dc18);
            dtNew.Columns.Add(dc19);
            dtNew.Columns.Add(dc20);
            //dtNew.Columns.Add(dc21);
            dtNew.Columns.Add(dc22);
            dtNew.Columns.Add(dc23);
            dtNew.Columns.Add(dc24);
            dtNew.Columns.Add(dc25);

            #endregion
            int days = 30;
            string sqltimeCallRecords = "";
            string strsqlcall = "";
            string strsqlTrunk = "";
            string strsqlAgent = "";
            string sqlUser = "";

            var datenow = DateTime.Now;
            if (stime == null || stime.Trim() == "")
            {
                stime = datenow.ToString("yyyy-MM-01");
            }
            if (endtime == null || endtime.Trim() == "")
            {
                endtime = datenow.ToString("yyyy-MM-dd");
            }

            sqltimeCallRecords += " and CONVERT(varchar , TimeAlerting, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            strsqlcall += " and CONVERT(varchar , BeginTime, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            strsqlTrunk += " and CONVERT(varchar , TimeCallIn, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";
            strsqlAgent += " and CONVERT(varchar , TimeLogin, 120)>=CONVERT(varchar , '" + stime.Trim() + " 00:00:01', 120) ";

            sqltimeCallRecords += " and CONVERT(varchar , TimeAlerting, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            strsqlcall += " and CONVERT(varchar , BeginTime, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            strsqlTrunk += " and CONVERT(varchar , TimeCallIn, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";
            strsqlAgent += " and CONVERT(varchar , TimeLogin, 120)<=CONVERT(varchar , '" + endtime.Trim() + " 23:59:59', 120) ";

            if (dpt != null && dpt.Trim() != "")
            {
                sqlUser += " and F_DeptId=" + dpt;
            }
            if (dayss != null && dayss.Trim() != "")
            {
                days = int.Parse(dayss.Trim());
            }
            if ( !string.IsNullOrEmpty(usercode) )
            {
                sqlUser += " and F_DeptId=" + dpt;
            }


            DataTable dtdpt = DbHelperSQL.Query("select F_DeptId,F_DeptName,F_Remark from  T_Sys_Department WITH(NOLOCK)   ").Tables[0];
            DataTable dtUser = DbHelperSQL.Query("SELECT F_UserCode,F_UserName,F_UserId,F_DeptId FROM T_Sys_UserAccount WITH(NOLOCK) where F_DeleteFlag=0 and f_seatFlag=1 AND F_DeptId>0 " + sqlUser ).Tables[0];

            DataTable dtTrunk = DbHelperSQL.Query("select AgentID,COUNT(*) as ct,SUM(DATEDIFF(Second,timewantagent,timeconnected)) as dd from rep_trunk_call_in WITH(NOLOCK) where 1=1 " + strsqlTrunk + " group by AgentID ").Tables[0];
            DataTable dtcall = DbHelperSQL.Query("select UserCode,CallType,CallState,MYD from T_Call_CallRecords WITH(NOLOCK) where 1=1 " + strsqlcall).Tables[0];
            DataTable dtext = DbHelperSQL.Query("select CalleeAgentID,COUNT(*) as ct,SUM(PeriodTalking) as sc,SUM(PeriodAlerting) as pa from rep_ext_call_in WITH(NOLOCK) where 1=1 " + sqltimeCallRecords + " group by CalleeAgentID ").Tables[0];
            DataTable dtCallEvey = DbHelperSQL.Query("select usercode,COUNT(*) as ct,SUM(TalkLongTime) as sc from T_Call_CallRecords WITH(NOLOCK) where CallState=1 " + strsqlcall + " group by usercode ").Tables[0];
            DataTable dtAgent = DbHelperSQL.Query("select AgentId,COUNT(*) as dl,SUM(LoginTimes) as dlsc,SUM(reposenum) as zm,SUM(ReposeTimes) as xx from rep_agent_detail WITH(NOLOCK) where 1=1 " + strsqlAgent + " group by AgentId ").Tables[0];
            DataTable dtzj = DbHelperSQL.Query("select usercode,convert(int,SUM(f_qcscore)) as fs,COUNT(*) as fsct from T_Call_CallRecords WITH(NOLOCK) where F_QCState=1 " + strsqlcall + " group by usercode ").Tables[0];




            if (dtUser != null && dtUser.Rows.Count > 0)
            {
                for (int i = 0; i < dtUser.Rows.Count; i++)
                {
                    #region

                    int ijtcs = 0;//接听通话次数 
                    int ithms = 0;//通话秒数
                    int izlms = 0;//振铃秒数
                    int ipjjtms = 0;//平均接听秒数
                    int ipjzlms = 0;//平均振铃秒数
                    double ihrzyl = 0.00;//呼入占有率
                    int izxdlcs = 0;//坐席登录次数
                    int idlsc = 0;//登录时长
                    int iWorktime = 0;//工作时长
                    int izmcs = 0;//置忙次数
                    int ixxsc = 0;//休息时长
                    int ipjczsc = 0;//平均操作时长
                    int ipjzmxxsc = 0;//平均置忙休息时长


                    int itrunkct = 0;//总数
                    int itrunkjj = 0;//总接听
                    int itrunkhs = 0;//呼损量
                    int itrunkpjsc = 0;//平均排队时间
                    double itrunkjtl = 0.00;//接通率
                    double itrunkhsl = 0.00;//互损率
                    double itrunkpjpd = 0.00;//平均排队
                    #endregion
                    if (!string.IsNullOrEmpty(dtUser.Rows[i]["F_UserCode"].ToString()))
                    {
                        DataRow drNew = dtNew.NewRow();
                        drNew["坐席"] = dtUser.Rows[i]["F_UserName"].ToString();
                        // drNew["坐席工号"] = dtUser.Rows[i]["F_WorkNumber"].ToString();
                        drNew["坐席工号"] = dtUser.Rows[i]["F_UserCode"].ToString();
                        #region 部门名称

                        DataRow[] dtDept = dtdpt.Select(" F_DeptId=" + dtUser.Rows[i]["F_DeptId"].ToString() + " ");
                        if (dtDept != null && dtDept.Count() > 0)
                            drNew["部门"] = dtDept[0]["F_DeptName"].ToString();
                        else
                            drNew["部门"] = "";
                        #endregion

                        #region 呼入数据
                        DataRow[] drTrunk = dtTrunk.Select(" AgentID='" + dtUser.Rows[i]["F_UserCode"].ToString() + "'");
                        DataRow[] drCallIn = dtcall.Select(" CallType=0 and UserCode='" + dtUser.Rows[i]["F_UserCode"].ToString() + "'");
                        DataRow[] drCallInJT = dtcall.Select(" CallType=0 and CallState=1 and UserCode='" + dtUser.Rows[i]["F_UserCode"].ToString() + "'");
                        if (drCallIn != null && drCallIn.Count() > 0)
                            itrunkct = drCallIn.Count();
                        if (drCallInJT != null && drCallInJT.Count() > 0)
                            itrunkjj = drCallInJT.Count();
                        if (drTrunk.Count() > 0 && drTrunk[0]["dd"].ToString() != "")
                            itrunkpjsc = int.Parse(drTrunk[0]["dd"].ToString());

                        itrunkhs = itrunkct - itrunkjj;
                        if (itrunkct != 0)
                        {
                            itrunkjtl = itrunkjj / double.Parse(itrunkct.ToString());
                            itrunkhsl = itrunkhs / double.Parse(itrunkct.ToString());
                            itrunkpjpd = itrunkpjsc / double.Parse(itrunkct.ToString());
                        }
                        else
                        {
                            itrunkjtl = 0.00;
                            itrunkhsl = 0.00;
                            itrunkpjpd = 0.00;
                        }
                        drNew["呼入电话数"] = itrunkct;
                        drNew["坐席接通量"] = itrunkjj;
                        drNew["呼损量"] = itrunkhs;
                        drNew["接通率"] = (itrunkjtl * 100).ToString("0.00") + "%";
                        drNew["呼损率"] = (itrunkhsl * 100).ToString("0.00") + "%";
                        drNew["平均排队时间"] = itrunkpjpd.ToString("0.00");


                        #endregion
                        #region 坐席接听数据
                        DataRow[] drExt = dtext.Select(" CalleeAgentID='" + dtUser.Rows[i]["F_UserCode"].ToString() + "'");
                        DataRow[] drCallEvey = dtCallEvey.Select(" usercode='" + dtUser.Rows[i]["F_UserCode"].ToString() + "'");
                        if (drCallEvey != null && drCallEvey.Count() > 0)
                        {
                            if (drCallEvey[0]["ct"].ToString() != "")
                            {
                                ijtcs = int.Parse(drCallEvey[0]["ct"].ToString());//接听次数
                            }
                            else
                            {
                                ijtcs = 0;
                            }
                            if (drCallEvey[0]["sc"].ToString() != "")
                            {
                                ithms = int.Parse(drCallEvey[0]["sc"].ToString());//通话秒数
                            }
                            else
                            {
                                ithms = 0;
                            }
                        }
                        if (drExt != null && drExt.Count() > 0)
                        {
                            if (drExt[0]["pa"].ToString() != "")
                            {
                                izlms = int.Parse(drExt[0]["pa"].ToString());//振铃秒数
                            }
                            else
                            {
                                izlms = 0;
                            }
                        }
                        drNew["接听通话次数"] = ijtcs.ToString();
                        drNew["通话分钟数"] = (ithms / 60).ToString();
                        drNew["通话秒数"] = ithms.ToString();

                        if (ijtcs != 0)
                        {
                            ipjjtms = ithms / ijtcs;//平均接通秒数
                            ipjzlms = izlms / ijtcs;//平均振铃秒数
                        }
                        else
                        {
                            ipjjtms = 0;//平均接通秒数
                            ipjzlms = 0;//平均振铃秒数
                        }
                        drNew["平均接听通话秒数"] = ipjjtms.ToString();
                        drNew["平均振铃秒数"] = ipjzlms.ToString();
                        #endregion

                        #region 坐席登录次数
                        DataRow[] drAgent = dtAgent.Select(" AgentId='" + dtUser.Rows[i]["F_UserCode"].ToString() + "'");
                        if (drAgent != null && drAgent.Count() > 0)
                        {
                            if (drAgent[0]["dl"] != null && drAgent[0]["dl"].ToString() != "")
                            {
                                izxdlcs = int.Parse(drAgent[0]["dl"].ToString());//登录次数
                            }
                            else
                            {
                                izxdlcs = 0;
                            }
                            if (drAgent[0]["dlsc"] != null && drAgent[0]["dlsc"].ToString() != "")
                            {
                                idlsc = int.Parse(drAgent[0]["dlsc"].ToString());//登录时长
                            }
                            else
                            {
                                idlsc = 0;
                            }
                            if (drAgent[0]["zm"] != null && drAgent[0]["zm"].ToString() != "")
                            {
                                izmcs = int.Parse(drAgent[0]["zm"].ToString());//置忙次数
                            }
                            else
                            {
                                izmcs = 0;
                            }
                            if (drAgent[0]["xx"] != null && drAgent[0]["xx"].ToString() != "")
                            {
                                ixxsc = int.Parse(drAgent[0]["xx"].ToString());//休息时长
                            }
                            else
                            {
                                ixxsc = 0;
                            }

                        }
                        iWorktime = idlsc - ixxsc;//工作时长
                        if (izxdlcs != 0)
                        {
                            ipjczsc = iWorktime / izxdlcs;//平均操作秒数
                        }
                        else
                        {
                            ipjczsc = 0;
                        }
                        if (izmcs != 0)
                        {
                            ipjzmxxsc = ixxsc / izmcs;//置忙平均操作时长
                        }
                        else
                        {
                            ipjzmxxsc = 0;
                        }

                        //呼入占有率
                        if (iWorktime != 0)
                        {
                            ihrzyl = ithms / Convert.ToDouble(iWorktime);
                        }
                        else
                        {
                            ihrzyl = 0.00;
                        }
                        drNew["呼入占有率"] = (ihrzyl * 100).ToString("0.00") + "%";
                        drNew["坐席登录次数"] = izxdlcs.ToString();
                        drNew["登录时长分钟数"] = (idlsc / 60).ToString();
                        drNew["工作时长分钟数"] = (iWorktime / 60).ToString();
                        drNew["平均每天工作时长"] = Math.Round((double.Parse(iWorktime.ToString()) / 60 / 60 / days), 2).ToString();
                        //drNew["平均操作分钟数"] = (ipjczsc / 60).ToString();
                        drNew["置忙次数"] = izmcs.ToString();
                        drNew["休息时长分钟数"] = (ixxsc / 60).ToString();
                        drNew["平均每天休息时长"] = Math.Round((double.Parse(ixxsc.ToString()) / 60 / 60 / days), 2).ToString();
                        drNew["置忙平均休息分钟数"] = (ipjzmxxsc / 60).ToString();
                        #endregion

                        #region 用户评价
                        DataRow[] drmyd = dtcall.Select(" MYD is not null and CallState=1 and usercode='" + dtUser.Rows[i]["F_UserCode"].ToString() + "' ");
                        int myd = drmyd != null ? drmyd.Count() : 0;
                        drNew["用户评价"] = myd.ToString();
                        #endregion

                      
                        dtNew.Rows.Add(drNew);

                    }
                }
            }
            return dtNew;
        }


        //日期  shenqing科室 设备名称 数量 金额（元） 维修部门 维修Ren
        //维修费用报表
        //搜索条件 chuli时间间隔：shenqing科室 设备名称  维修部门 维修人：
        public ActionResult AllPriceList(string stime, string endtime, string applicationdept, string equipId,string maintenancer,string maintenanceDept,int isExport
            = 0)
        {
          


            if (isExport > 0)
            {
                string wheresql = "";

            if (!string.IsNullOrEmpty(stime))
            {
                wheresql += " and a.F_CreateTime>='" + stime + " 00:00:00'";

            }
            if (!string.IsNullOrEmpty(endtime))
            {
                wheresql += " and a.F_CreateTime<='" + endtime + " 23:59:59'";

            }

            if (!string.IsNullOrEmpty(applicationdept))
            {
                wheresql += " and c.F_ApplicationDept='"+ applicationdept + "'";

            }
            if (!string.IsNullOrEmpty(equipId))
            {
                wheresql += " and a.F_DicId='" + equipId + "'";

            }
            if (!string.IsNullOrEmpty(maintenancer))
            {
                wheresql += " and b.F_Maintenancer='" + maintenancer + "'";

            }
            if (!string.IsNullOrEmpty(maintenanceDept))
            {
                wheresql += " and b.F_MaintenanceDept='" + maintenanceDept + "'";

            }


          

          
                string sql2 = "    select a.F_CreateTime,c.F_ApplicationDept, dbo.GetDepName(c.F_ApplicationDept) ApplicationDeptname,  a.F_EquipName,a.F_DicId,a.F_EquipAmount,a.F_EquipPrice,dbo.GetDepName(b.F_MaintenanceDept)  MaintenanceDeptName,  b.F_MaintenanceDept,b.F_Maintenancer,dbo.GetUserName(b.F_Maintenancer)  Maintenancer  from[T_InvolvedEquip_RepairProcess] a left join T_FaultRepair_Process b on     a.F_RepairProcessId = b.F_ProcessId  LEFT join T_FaultRepair_Base c on b.F_WorkOrderCode = c.F_WorkOrderCode  where 1=1  " + wheresql;
                DataTable dtNew = DbHelperSQL.Query(sql2).Tables[0];
                //日期  shenqing科室 设备名称 数量 金额（元） 维修部门 维修Ren
                dtNew.Columns.Remove("F_ApplicationDept");
                dtNew.Columns.Remove("F_DicId");
                dtNew.Columns.Remove("F_MaintenanceDept");
                dtNew.Columns.Remove("F_Maintenancer");
                
                NPOIHelper npoi = new NPOIHelper();

                if (npoi.ExportToExcel("维修费用报表", dtNew, colsAllPriceList) == "")
                {
                    return Success("导出成功");
                }
                else
                {
                    return Error("导出失败");
                }
            }

            string where = "";
            string strpageindex = RequestString.GetQueryString("page");
            int pageindex = 1;
            string strpagesize = RequestString.GetQueryString("pagesize");
            int pagesize = 10;
            if (!string .IsNullOrEmpty (strpageindex))
            {
                pageindex = int.Parse(strpageindex);
            }
            if (!string.IsNullOrEmpty(strpagesize))
            {
                pagesize = int.Parse(strpagesize);
            }
            if (!string.IsNullOrEmpty(stime))
            {
                where += " and  F_CreateTime>='" + stime + " 00:00:00'";

            }
            if (!string.IsNullOrEmpty(endtime))
            {
                where += " and  F_CreateTime<='" + endtime + " 23:59:59'";

            }

            if (!string.IsNullOrEmpty(applicationdept))
            {
                where += " and  F_ApplicationDept='" + applicationdept + "'";

            }
            if (!string.IsNullOrEmpty(equipId))
            {
                where += " and  F_DicId='" + equipId + "'";

            }
            if (!string.IsNullOrEmpty(maintenancer))
            {
                where += " and  F_Maintenancer='" + maintenancer + "'";

            }
            if (!string.IsNullOrEmpty(maintenanceDept))
            {
                where += " and  F_MaintenanceDept='" + maintenanceDept + "'";

            }

            string sql = "   (select a.F_ID, a.F_CreateTime,c.F_ApplicationDept, dbo.GetDepName(c.F_ApplicationDept) ApplicationDeptname,  a.F_EquipName,a.F_DicId,a.F_EquipAmount,a.F_EquipPrice,dbo.GetDepName(b.F_MaintenanceDept)  MaintenanceDeptName,  b.F_MaintenanceDept,b.F_Maintenancer,dbo.GetUserName(b.F_Maintenancer)  Maintenancer  from[T_InvolvedEquip_RepairProcess] a left join T_FaultRepair_Process b on     a.F_RepairProcessId = b.F_ProcessId  LEFT join T_FaultRepair_Base c on b.F_WorkOrderCode = c.F_WorkOrderCode ) t";

            int recordCount = 0;
            DataTable datatable = Bll.PagerBll.GetListPager
                                            (sql,
                                              "F_ID",
                                              "* ",
                                              where,
                                              "",
                                              pagesize,
                                              pageindex,
                                              true,
                                              out recordCount
                                            );    

            var obj = new
            {                
                rows = datatable,
                total = recordCount
            };





            return Success("获取成功",obj);
        }

        public string[] colsAllPriceList = { "日期", "科室", "设备名称", "数量", "金额（元）", "维修部门", "维修人" };

    }
}