using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using ZXDT.DBUtility;

namespace ZXDT.CallCenter.DAL
{

    public partial class ReportDAL
    {
        public ReportDAL()
        { }


        #region 其他各项指标
        /// <summary>
        /// 获取接通率数据
        /// </summary>
        /// <param name="StartDate">开始日期</param>
        /// <param name="EndDate">结束日期</param>
        /// <returns></returns>
        public DataTable GetOtherData1(string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  CallState,COUNT(CallState) as num  FROM T_Call_CallRecords  ");
            strSql.Append(" where CallType=0  ");
            strSql.Append(" and CONVERT(varchar(100), BeginTime, 8)>='08:30:00' ");
            strSql.Append(" and CONVERT(varchar(100), BeginTime, 8)<='20:00:00' ");
            if (!string.IsNullOrEmpty(startDate))
            {
                strSql.Append(" and BeginTime>='" + startDate + "  00:00:00' ");
            }

            if (!string.IsNullOrEmpty(endDate))
            {
                strSql.Append(" and BeginTime<='" + endDate + " 23:59:59' ");
            }


            strSql.Append("   GROUP BY CallState");

            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }


        }
        /// <summary>
        /// 获取流失率数据
        /// </summary>
        /// <param name="StartDate">开始日期</param>
        /// <param name="EndDate">结束日期</param>
        /// <returns></returns>
        public DataTable GetOtherData2(string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  CallState,COUNT(CallState) as num  FROM T_Call_CallRecords  ");
           // strSql.Append(" where CallType=0  AND ( RingLongTime>15 OR CallState=1) ");
            strSql.Append(" where CallType=0    ");
            strSql.Append(" and CONVERT(varchar(100), BeginTime, 8)>='08:30:00' ");
            strSql.Append(" and CONVERT(varchar(100), BeginTime, 8)<='20:00:00' ");
            if (!string.IsNullOrEmpty(startDate))
            {
                strSql.Append(" and BeginTime>='" + startDate + "  00:00:00' ");
            }

            if (!string.IsNullOrEmpty(endDate))
            {
                strSql.Append(" and BeginTime<='" + endDate + " 23:59:59' ");
            }


            strSql.Append("   GROUP  BY CallState");

            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }


        }
        /// <summary>
        /// 按时转出率
        /// </summary>
        /// <param name="StartDate">开始日期</param>
        /// <param name="EndDate">结束日期</param>
        /// <returns></returns>
        public DataTable GetOtherData3(string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT COUNT(1) num,t.CallState FROM   ");
            strSql.Append(" (SELECT CASE   ");

            //未转派 超时0
            strSql.Append("  WHEN F_SENTORDERTIME IS NULL AND DATEDIFF(minute, DATEADD(day,1,F_CREATEDATE),  GETDATE())> 0  THEN 0   ");
            //未转派 未超时1
            strSql.Append("  WHEN F_SENTORDERTIME IS NULL  THEN 1   ");
            //已转派 超时0
            strSql.Append("  WHEN DATEDIFF(minute, DATEADD(day,1,F_CREATEDATE), F_SENTORDERTIME)>0 THEN 0   ");
            //已转派 未超时1
            strSql.Append("  ELSE 1 END AS CallState FROM T_Wo_WorkOrderBase   ");

            strSql.Append(" where 1=1 ");
            if (!string.IsNullOrEmpty(startDate))
            {
                strSql.Append(" and F_CreateDate>='" + startDate + "  00:00:00' ");
            }

            if (!string.IsNullOrEmpty(endDate))
            {
                strSql.Append(" and F_CreateDate<='" + endDate + " 23:59:59' ");
            }


            strSql.Append(" ) t GROUP BY t.CallState");

            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }


        }

        /// <summary>
        /// 按时办结率
        /// </summary>
        /// <param name="StartDate">开始日期</param>
        /// <param name="EndDate">结束日期</param>
        /// <returns></returns>
        public DataTable GetOtherData4(string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT COUNT(1) num,t.CallState,t.F_HOUSING FROM   ");
            strSql.Append(" (SELECT CASE   ");
            // 超时0
            strSql.Append("  WHEN F_OVERTIMES > 0  THEN 0   ");
            // 未超时1
            strSql.Append("  ELSE 1 END AS CallState,F_HOUSING FROM T_Wo_WorkOrderBase   ");

            strSql.Append(" where 1=1 AND F_HOUSING in ('投诉','建议','咨询')");
            if (!string.IsNullOrEmpty(startDate))
            {
                strSql.Append(" and F_CreateDate>='" + startDate + "  00:00:00' ");
            }

            if (!string.IsNullOrEmpty(endDate))
            {
                strSql.Append(" and F_CreateDate<='" + endDate + " 23:59:59' ");
            }


            strSql.Append(" ) t GROUP BY t.CallState,t.F_HOUSING");

            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }


        }


        /// <summary>
        /// 按时答复率
        /// </summary>
        /// <param name="StartDate">开始日期</param>
        /// <param name="EndDate">结束日期</param>
        /// <returns></returns>
        public DataTable GetOtherData5(string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT COUNT(1) num,t.CallState FROM   ");
            strSql.Append(" (SELECT CASE   ");
            // 超时0
            strSql.Append(" WHEN ReplayTime IS NULL AND DATEDIFF(second,  GETDATE(), NeedReplayTime)<0 THEN 0 ");
            // 超时0
            strSql.Append("  WHEN ReplayTime IS NOT NULL AND DATEDIFF(second,  ReplayTime, NeedReplayTime)<0 THEN 0   ");
            strSql.Append(" ELSE 1 END AS CallState FROM dbo.T_Wo_WorkOrderHistory   ");

            strSql.Append("  WHERE NeedReplayTime IS NOT NULL  ");
            if (!string.IsNullOrEmpty(startDate))
            {
                strSql.Append(" and F_OPTDATE>='" + startDate + "  00:00:00' ");
            }

            if (!string.IsNullOrEmpty(endDate))
            {
                strSql.Append(" and F_OPTDATE<='" + endDate + " 23:59:59' ");
            }


            strSql.Append(" ) t GROUP BY t.CallState");

            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }


        }

        /// <summary>
        /// 定责指标
        /// </summary>
        /// <param name="StartDate">开始日期</param>
        /// <param name="EndDate">结束日期</param>
        /// <returns></returns>
        public DataTable GetOtherData6(string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT F_SERVICENATURE,COUNT(1)AS num  FROM dbo.T_Wo_WorkOrderBase  ");
            strSql.Append(" WHERE F_WORKORDERSTATEID=5 and  F_HOUSING='投诉'  ");
            if (!string.IsNullOrEmpty(startDate))
            {
                strSql.Append(" and F_CreateDate>='" + startDate + "  00:00:00' ");
            }

            if (!string.IsNullOrEmpty(endDate))
            {
                strSql.Append(" and F_CreateDate<='" + endDate + " 23:59:59' ");
            }

            strSql.Append(" GROUP BY  F_SERVICENATURE ");

            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }


        }

        /// <summary>
        /// 首呼解决率
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public DataTable GetOtherData10(string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT COUNT(1) num,t.CallState,F_HOUSING FROM   ");
            strSql.Append(" (SELECT CASE   ");
            //首呼解决
            strSql.Append("  WHEN F_WORKORDERTYPEID=3  THEN 1   ");
            //转单
            strSql.Append("  ELSE 0 END AS CallState,F_HOUSING FROM T_Wo_WorkOrderBase   ");

            strSql.Append(" where 1=1 AND F_HOUSING IN('投诉','建议') ");
            if (!string.IsNullOrEmpty(startDate))
            {
                strSql.Append(" and F_CreateDate>='" + startDate + "  00:00:00' ");
            }

            if (!string.IsNullOrEmpty(endDate))
            {
                strSql.Append(" and F_CreateDate<='" + endDate + " 23:59:59' ");
            }


            strSql.Append(" ) t GROUP BY t.F_HOUSING,t.CallState");

            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }


        }

        #endregion

        #region 定责类型统计报表
        public DataTable GetOtherData7(string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  F_PROVINCE as name,COUNT(1)AS num  FROM (");
            strSql.Append(" SELECT DISTINCT F_WORKORDERID,F_PROVINCE,F_CITY,F_AREA FROM  ");
            strSql.Append("   dbo.T_Wo_WorkOrderBase a INNER JOIN T_Wo_WorkOrderNotice b ON a.F_WORKORDERID=b.OrderId");
            strSql.Append("  Where NoticeState=1 AND NoticeType=1 and F_WORKORDERSTATEID=5");
            if (!string.IsNullOrEmpty(startDate))
            {
                strSql.Append(" and a.F_CreateDate>='" + startDate + " 00:00:00' ");
            }

            if (!string.IsNullOrEmpty(endDate))
            {
                strSql.Append(" and a.F_CreateDate<='" + endDate + " 23:59:59' ");
            }

            strSql.Append(" ) t  GROUP  BY  t.F_PROVINCE  ORDER BY num DESC  ");

            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }

        }
        public DataTable GetOtherData8(string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT F_CITY as name,COUNT(1)AS num  FROM (");
            strSql.Append(" SELECT DISTINCT F_WORKORDERID,F_PROVINCE,F_CITY,F_AREA FROM  ");
            strSql.Append("   dbo.T_Wo_WorkOrderBase a INNER JOIN T_Wo_WorkOrderNotice b ON a.F_WORKORDERID=b.OrderId");
            strSql.Append("  Where NoticeState=1 AND NoticeType=1  and F_WORKORDERSTATEID=5");
            if (!string.IsNullOrEmpty(startDate))
            {
                strSql.Append(" and a.F_CreateDate>='" + startDate + " 00:00:00' ");
            }

            if (!string.IsNullOrEmpty(endDate))
            {
                strSql.Append(" and a.F_CreateDate<='" + endDate + " 23:59:59' ");
            }

            strSql.Append(" ) t  GROUP  BY  t.F_CITY  ORDER BY num DESC  ");

            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }

        }
        public DataTable GetOtherData9(string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  F_AREA as name,COUNT(1)AS num  FROM (");
            strSql.Append(" SELECT DISTINCT F_WORKORDERID,F_PROVINCE,F_CITY,F_AREA FROM  ");
            strSql.Append("   dbo.T_Wo_WorkOrderBase a INNER JOIN T_Wo_WorkOrderNotice b ON a.F_WORKORDERID=b.OrderId");
            strSql.Append("  Where NoticeState=1 AND NoticeType=1  and F_WORKORDERSTATEID=5");
            if (!string.IsNullOrEmpty(startDate))
            {
                strSql.Append(" and a.F_CreateDate>='" + startDate + " 00:00:00' ");
            }

            if (!string.IsNullOrEmpty(endDate))
            {
                strSql.Append(" and a.F_CreateDate<='" + endDate + " 23:59:59' ");
            }

            strSql.Append(" ) t  GROUP  BY  t.F_AREA  ORDER BY num DESC  ");

            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }

        }

        #endregion

    }
}