UU_CallCenter/code/BaseCallCent.../reportmanage/operationdata/controls/ZuoXiReport....

using HySoft.Common;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

namespace HySoft.BaseCallCenter.Web.reportmanage.operationdata.controls
{
    /// <summary>
    /// ZuoXiReport 的摘要说明
    /// </summary>
    public class ZuoXiReport : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/plain";
            string action = context.Request.QueryString["action"];
            switch (action)
            {
                case "getlist":
                    context.Response.Write(GetDataList(context));//读取数据
                    break;
            }
        }
        #region
        //岗位    工号    姓名    来话接听数量-lhjtl    受理座席转接量-slzxzjl   接听电话总量-jtdhzl 外呼电话量-whdhl 外呼电话接起量-whdhjql
        //签入时长-qrsc  来话接听时长（秒）-ldjtsc	转接电话接听时长（秒）-zjdhjtsc    总接听时长（秒）-zjtsc
        //平均来话接听时长（秒 / 次）-pjlhjtsc	平均转接电话接听时长（秒 / 次）-pjzjdhjtsc	平均接听通话时长（秒 / 次）-pjjtthsc	
        //总呼出通话时长（秒）-zhcthsc	总呼出成功通话时长（秒）-zhccgthsc	
        //平均呼出通话时长（秒 / 次）-pjhcthsc	平均成功呼出通话时长（秒 / 次）-pjcghcthsc
        //来话话后置忙总时长（秒）-lhhhzmzsc	转接电话话后置忙总时长（秒）-zjdhhhzmzsc	话后置忙总时长（秒）-hhzmzsc	
        //来话话后置忙平均时长（秒 / 次）-lhhhzmpjsc	转接电话话后置忙平均时长（秒 / 次）-zjdhhhzmpjsc	
        //平均话后置忙时长（秒 / 次）-pjhhzmsc	
        //来话持线等待总时长（秒）-lhcxddzsc	转接电话持线等待总时长（秒）-zjdhcxddzsc
        //持线等待总时长（秒）-cxddzsc	来话持线等待平均时长（秒 / 次）-lhcxddpjsc	
        //转接电话持线等待平均时长（秒 / 次）-zjdhcxddpjsc	持线等待平均时长（秒 / 次）-cxddpjsc
        //未准备好总时长（秒）-wzbhzsc	示闲总时长（秒）-sxzsc	事后处理时长（秒）-shclsc	
        //来电信息点击量 -ldxxdjl   信息点击总量-xxdjzl
        int lhjtl = 0; int slzxzjl = 0; int jtdhzl = 0; int whdhl = 0; int whdhjql = 0;
        int qrsc = 0; int ldjtsc = 0; int zjdhjtsc = 0; int zjtsc = 0;
        string pjlhjtsc = "0"; string pjzjdhjtsc = "0"; string pjjtthsc = "0";
        int zhcthsc = 0; int zhccgthsc = 0;
        string pjhcthsc = "0"; string pjcghcthsc = "0";
        int lhhhzmzsc = 0; int zjdhhhzmzsc = 0; int hhzmzsc = 0;
        int lhhhzmpjsc = 0; int zjdhhhzmpjsc = 0;
        string pjhhzmsc = "0";
        int lhcxddzsc = 0; int zjdhcxddzsc = 0;
        int cxddzsc = 0; string lhcxddpjsc = "0";
        int zjdhcxddpjsc = 0; string cxddpjsc = "0";
        int wzbhzsc = 0; int sxzsc = 0; int shclsc = 0; int ldxxdjl = 0; int xxdjzl = 0;
        #endregion
        private string GetDataList(HttpContext context)
        {
            string res = "";
            try
            {
                string sqlu = "";
                string dpt = CommonRequest.GetQueryString("dpt");
                string txtStartTime = CommonRequest.GetQueryString("starttime");
                string txtEndTime = CommonRequest.GetQueryString("endtime");

                string strpageindex = context.Request.Params["page"];
                int pageindex = 1;
                string strpagesize = context.Request.Params["pagesize"];
                int pagesize = 20;
                sqlu = " F_SeatFlag=1 and F_DeleteFlag=0";
                if (dpt.Trim() != "")
                {
                    sqlu += " and F_DeptId=" + dpt.Trim();
                }
                string sqltimeCallRecords = "";
                string strsqlcall = " 1=1 ";
                if (txtStartTime != null && txtStartTime.Trim() != "")
                {
                    sqltimeCallRecords += " and CONVERT(varchar , TimeAlerting, 120)>=CONVERT(varchar , '" + txtStartTime.Trim() + " 00:00:01', 120) ";
                    strsqlcall += " and CONVERT(varchar , BeginTime, 120)>=CONVERT(varchar , '" + txtStartTime.Trim() + " 00:00:01', 120) ";
                }
                if (txtEndTime != null && txtEndTime.Trim() != "")
                {
                    sqltimeCallRecords += " and CONVERT(varchar , TimeAlerting, 120)<=CONVERT(varchar , '" + txtEndTime.Trim() + " 23:59:59', 120) ";
                    strsqlcall += " and CONVERT(varchar , BeginTime, 120)<=CONVERT(varchar , '" + txtEndTime.Trim() + " 23:59:59', 120) ";
                }

                Model.PageData<object> pageData = new Model.PageData<object>();
                var userslist = new BLL.T_Sys_UserAccount().GetModelList(sqlu);
                var calllist = new BLL.T_Call_CallRecords().GetModelList(strsqlcall);
                var rolelist = new BLL.T_Sys_RoleInfo().GetModelList("");
                pageData.Rows = userslist.Select(n =>
                {
                    GetTimes(n.F_WorkNumber, sqltimeCallRecords, strsqlcall);
                    lhjtl = calllist.Where(c => c.UserCode == n.F_WorkNumber && c.CallType == 0 && c.CallState == 1).ToList().Count();//来话接听数量
                    whdhl = calllist.Where(c => c.UserCode == n.F_WorkNumber && c.CallType == 1).ToList().Count();//外呼电话量
                    whdhjql = calllist.Where(c => c.UserCode == n.F_WorkNumber && c.CallType == 1 && c.CallState == 1).ToList().Count();//外呼电话接起量
                    ldxxdjl = calllist.Where(c => c.UserCode == n.F_WorkNumber && c.F_ZXreasonid > 0).ToList().Count();//来电信息点击量
                    string rolename = rolelist.Where(r => r.F_RoleId == n.F_RoleId).FirstOrDefault<Model.T_Sys_RoleInfo>().F_RoleName;
                    return new
                    {
                        RoleName = rolename,
                        UserCode = n.F_WorkNumber,
                        UserName = n.F_UserName,
                        LHJTL = lhjtl,
                        SLZXZJL = slzxzjl,
                        JTDHZL = jtdhzl,
                        WHDHL = whdhl,
                        WHDHJQL = whdhjql,
                        QRSC = qrsc,
                        LDJTSC = ldjtsc,
                        ZJDHJTSC = zjdhjtsc,
                        ZJTSC = zjtsc,
                        PJLHJTSC = pjlhjtsc,
                        PJZJDHJTSC = pjzjdhjtsc,
                        PJJTTHSC = pjjtthsc,
                        ZHCTHSC = zhcthsc,
                        ZHCCGTHSC = zhccgthsc,
                        PJHCTHSC = pjhcthsc,
                        PJCGHCTHSC = pjcghcthsc,
                        LHHHZMZSC = lhhhzmzsc,
                        ZJDHHHZMZSC = zjdhhhzmzsc,
                        HHZMZSC = hhzmzsc,
                        LHHHZMPJSC = lhhhzmpjsc,
                        ZJDHHHZMPJSC = zjdhhhzmpjsc,
                        PJHHZMSC = pjhhzmsc,
                        LHCXDDZSC = lhcxddzsc,
                        ZJDHCXDDZSC = zjdhcxddzsc,
                        CXDDZSC = cxddzsc,
                        LHCXDDPJSC = lhcxddpjsc,
                        ZJDHCXDDPJSC = zjdhcxddpjsc,
                        CXDDPJSC = cxddpjsc,
                        WZBHZSC = wzbhzsc,
                        SXZSC = sxzsc,
                        SHCLSC = shclsc,
                        LDXXDJL = ldxxdjl,
                        XXDJZL = xxdjzl
                    };
                }).ToList<object>().Skip((pageindex - 1) * pagesize).Take(pagesize).ToList();
                pageData.Total = userslist.Count;

                res = pageData.ToJson();
            }
            catch (Exception ex)
            { }
            return res;
        }
        private void GetTimes(string usercode, string sqltimeCallRecords, string strsqlcall)
        {
            //岗位    工号    姓名    *来话接听数量-lhjtl    受理座席转接量-slzxzjl   接听电话总量-jtdhzl *外呼电话量-whdhl *外呼电话接起量-whdhjql
            //*签入时长-qrsc  *来话接听时长（秒）-ldjtsc	转接电话接听时长（秒）-zjdhjtsc    总接听时长（秒）-zjtsc
            //*平均来话接听时长（秒 / 次）-pjlhjtsc	平均转接电话接听时长（秒 / 次）-pjzjdhjtsc	平均接听通话时长（秒 / 次）-pjjtthsc	
            //*总呼出通话时长（秒）-zhcthsc	总呼出成功通话时长（秒）-zhccgthsc	
            //*平均呼出通话时长（秒 / 次）-pjhcthsc	平均成功呼出通话时长（秒 / 次）-pjcghcthsc
            //来话话后置忙总时长（秒）-lhhhzmzsc	转接电话话后置忙总时长（秒）-zjdhhhzmzsc	话后置忙总时长（秒）-hhzmzsc	
            //来话话后置忙平均时长（秒 / 次）-lhhhzmpjsc	转接电话话后置忙平均时长（秒 / 次）-zjdhhhzmpjsc	
            //平均话后置忙时长（秒 / 次）-pjhhzmsc	
            //*来话持线等待总时长（秒）-lhcxddzsc	转接电话持线等待总时长（秒）-zjdhcxddzsc
            //持线等待总时长（秒）-cxddzsc	*来话持线等待平均时长（秒 / 次）-lhcxddpjsc	
            //转接电话持线等待平均时长（秒 / 次）-zjdhcxddpjsc	持线等待平均时长（秒 / 次）-cxddpjsc
            //未准备好总时长（秒）-wzbhzsc	*示闲总时长（秒）-sxzsc	事后处理时长（秒）-shclsc	
            //*来电信息点击量 -ldxxdjl   信息点击总量-xxdjzl

            #region  来话持线等待总时长 来话持线等待平均时长
            DataTable dtTrunk = DBUtility.DbHelperSQL.Query("select COUNT(*) as ct,SUM(DATEDIFF(Second,timewantagent,timeconnected)) as dd from rep_trunk_call_in where AgentId=" + usercode + " " + sqltimeCallRecords.Replace("TimeAlerting", "TimeCallIn")).Tables[0];
            if (dtTrunk.Rows.Count > 0 )
            {
                int itrunkct = 0;
                if (dtTrunk.Rows[0]["ct"].ToString() != "")
                {
                    itrunkct = int.Parse(dtTrunk.Rows[0]["ct"].ToString());
                }
                if (dtTrunk.Rows[0]["dd"].ToString() != "")
                {
                    lhcxddzsc = int.Parse(dtTrunk.Rows[0]["dd"].ToString());
                }
                else
                {
                    lhcxddzsc = 0;
                }

                if (itrunkct != 0)
                {
                    lhcxddpjsc = (lhcxddzsc / itrunkct).ToString();
                }
                else
                {
                    lhcxddpjsc = "0";
                }
            }
            
            #endregion

            #region 来话接听时长 平均来话接听时长
            int iLHjtcs = 0;
            DataTable dtCallEvey = DBUtility.DbHelperSQL.Query("select COUNT(*) as ct,SUM(TalkLongTime) as sc from T_Call_CallRecords where CallState=1 and CallType=0 and  usercode=" + usercode + " " + strsqlcall).Tables[0];
            if (dtCallEvey != null && dtCallEvey.Rows.Count > 0)
            {
                if (dtCallEvey.Rows[0]["ct"].ToString() != "")
                {
                    iLHjtcs = int.Parse(dtCallEvey.Rows[0]["ct"].ToString());//接听次数
                }
                if (dtCallEvey.Rows[0]["sc"].ToString() != "")
                {
                    ldjtsc = int.Parse(dtCallEvey.Rows[0]["sc"].ToString());//通话秒数
                }
                else
                {
                    ldjtsc = 0;
                }
                if (iLHjtcs != 0)
                {
                    pjlhjtsc = (ldjtsc / iLHjtcs).ToString();
                }
                else
                    pjlhjtsc = "0";
            }
            #endregion

            #region 总接听时长 平均接听通话时长
            int iZjtcs = 0;
            DataTable dtCallZEvey = DBUtility.DbHelperSQL.Query("select COUNT(*) as ct,SUM(TalkLongTime) as sc from T_Call_CallRecords where CallState=1 and  usercode=" + usercode + " " + strsqlcall).Tables[0];
            if (dtCallZEvey != null && dtCallZEvey.Rows.Count > 0)
            {
                if (dtCallZEvey.Rows[0]["ct"].ToString() != "")
                {
                    iZjtcs = int.Parse(dtCallZEvey.Rows[0]["ct"].ToString());//接听次数
                }
                if (dtCallZEvey.Rows[0]["sc"].ToString() != "")
                {
                    zjtsc = int.Parse(dtCallZEvey.Rows[0]["sc"].ToString());//通话秒数
                }
                else
                {
                    zjtsc = 0;
                }
                if (iZjtcs != 0)
                {
                    pjjtthsc = (ldjtsc / iZjtcs).ToString();
                }
                else
                    pjjtthsc = "0";
            }
            #endregion

            #region 总呼出通话时长  平均呼出通话时长
            zhcthsc = zjtsc - ldjtsc;
            int iHCjtcs = 0;
            iHCjtcs=iZjtcs - iLHjtcs;
            if (iHCjtcs != 0)
            {
                pjhcthsc = (zhcthsc / iHCjtcs).ToString();
            }
            else
                pjhcthsc = "0";
            #endregion

            #region 签入时长 示闲总时长
            DataTable dtAgent = DBUtility.DbHelperSQL.Query("select COUNT(*) as dl,SUM(LoginTimes) as dlsc,SUM(reposenum) as zm,SUM(ReposeTimes) as xx from rep_agent_detail where AgentId=" + usercode + " " + sqltimeCallRecords.Replace("TimeAlerting", "TimeLogin")).Tables[0];
            if (dtAgent != null && dtAgent.Rows.Count > 0)
            {
                if (dtAgent.Rows[0]["dlsc"] != null && dtAgent.Rows[0]["dlsc"].ToString() != "")
                {
                    qrsc = int.Parse(dtAgent.Rows[0]["dlsc"].ToString());//签入时长
                }
                else
                {
                    qrsc = 0;
                }
                if (dtAgent.Rows[0]["xx"] != null && dtAgent.Rows[0]["xx"].ToString() != "")
                {
                    sxzsc = int.Parse(dtAgent.Rows[0]["xx"].ToString());//示闲总时长
                }
                else
                {
                    sxzsc = 0;
                }

            }
            else
            {
                qrsc = 0;
                sxzsc = 0;
            }
            #endregion
        }
        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}