using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using ZXDT.DBUtility;//Please add references
namespace ZXDT.CallCenter.DAL
{
    /// <summary>
    /// 数据访问类:T_Report
    /// </summary>
    public partial class T_Report
    {
        public T_Report()
        { }
        #region  Method


        /// <summary>
        ///获取坐席话务报表
        /// </summary>
        public DataTable GetAgentCallRecordReport(string strWhere)
        {
            string timeks = strWhere.Split(' ')[0];
            string timejs = strWhere.Split(' ')[1];
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select UserCode,UserName,
COUNT(1) as TotalRecord,
(select COUNT(1) from T_Call_CallRecords where CallType=0 and UserId=a.UserId and DATEDIFF(d,IvrStartTime,'" + timeks + "')<=0 and DATEDIFF(d,IvrStartTime,'" + timejs + "')>=0) as InCall,");
            strSql.Append(@"
(select COUNT(1) from T_Call_CallRecords where CallType=1 and UserId=a.UserId and DATEDIFF(d,BeginTime,'" + timeks + "')<=0 and DATEDIFF(d,BeginTime,'" + timejs + "')>=0) as OutCall,isnull(Sum(Datediff(s,BeginTime,EndTime)),0) As Second,isnull(avg(Datediff(s,BeginTime,EndTime)),0) As AvgSecond from T_Call_CallRecords as a where a.CallType=0 ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(@"and DATEDIFF(d,IvrStartTime,'" + timeks + "')<=0 and DATEDIFF(d,IvrStartTime,'" + timejs + "')>=0");
            }
            strSql.Append(@" group by a.UserId,a.UserCode,a.UserName
order by UserCode");
            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }

        /// <summary>
        ///获取坐席业务报表
        /// </summary>
        public DataTable GetAgentBusinessReport(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT userName,COUNT(1) as TotalCount
  FROM T_Bus_WorkOrder where 1=1 ");

            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(strWhere);
            }
            strSql.Append(@" group by userName");
            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }
        /// <summary>
        ///获取客户满意度
        /// </summary>
        public DataTable GetCustomerValuationReport(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select UserCode,UserName,sum(case MYD when 1 then 1 else 0 end) as MYCount,sum(case MYD when 2 then 1 else 0 end) as YBCount,sum(case MYD when 3 then 1 else 0 end) as BMYCount from T_Call_CallRecords
where MYD is not null and UserCode is not null ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(strWhere);
            }
            strSql.Append(@" group by UserCode,UserName");
            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }
        //GetCallInTypeReport
        /// <summary>
        ///获取来电方式统计
        /// </summary>
        public DataTable GetCallInTypeReport(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            //select DATEPART(HOUR,BeginTime) as hrhour,COUNT(CallId) as hrCount from T_Call_CallRecords where CallType=0 and DATEDIFF(d,BeginTime,'2013-07-01')<=0 and DATEDIFF(d,BeginTime,'2013-07-18')>=0 group by DATEPART(HOUR,BeginTime)
            strSql.Append(@"select DATEPART(HOUR,BeginTime) as hrhour,COUNT(CallId) as hrCount from T_Call_CallRecords where CallType=0 ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(strWhere);
            }
            strSql.Append(@" group by DATEPART(HOUR,BeginTime)");
            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }
        /// <summary>
        ///获取话务量统计（圆饼形式）
        /// </summary>
        public DataTable GetCallCountReport(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select COUNT(1) as totalCount,CallType from T_Call_CallRecords where 1=1 ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(strWhere);
            }
            strSql.Append(@" group by CallType");
            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }
        /// <summary>
        ///获取话务量统计(表格形式)
        /// </summary>
        public DataTable GetCallCountReport1(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select CallId from T_Call_CallRecords where 1=1 ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(strWhere);
            }
            strSql.Append(@" group by CallId");
            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }
        #endregion  Method
    }
}