ditie2.0_web/web/BaseCallCent.../T_Call_CallR...


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

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("CallRecordsId", "T_Call_CallRecords");
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int CallRecordsId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from T_Call_CallRecords");
            strSql.Append(" where CallRecordsId=@CallRecordsId");
            SqlParameter[] parameters = {
					new SqlParameter("@CallRecordsId", SqlDbType.Int,4)
			};
            parameters[0].Value = CallRecordsId;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(YTSoft.BaseCallCenter.Model.T_Call_CallRecords model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into T_Call_CallRecords(");
            strSql.Append(@"CallId,CallNumber,CusId,CusCode,CallType,CallState,DealType,BeginTime,IvrStartTime,IvrEndTime,
RingStartTime,RingEndTime,TalkStartTime,TalkEndTime,EndTime,LongTime,IvrLongTime,RingLongTime,
TalkLongTime,UserId,UserCode,UserName,ExtNumber,FilePath,IsExitWorkOrder,IsDeal,OperateType,
OperateObject,OperateTime,Remark,MYD,BusinessType,Location,groupcode,TaskType,TaskPhoneID,TaskID,ActionID,ActionType,F_ServiceType)");
            strSql.Append(" values (");
            strSql.Append(@"@CallId,@CallNumber,@CusId,@CusCode,@CallType,@CallState,@DealType,@BeginTime,@IvrStartTime,@IvrEndTime,
@RingStartTime,@RingEndTime,@TalkStartTime,@TalkEndTime,@EndTime,@LongTime,@IvrLongTime,@RingLongTime,@TalkLongTime,@UserId,@UserCode,
@UserName,@ExtNumber,@FilePath,@IsExitWorkOrder,@IsDeal,@OperateType,@OperateObject,@OperateTime,@Remark,@MYD,@BusinessType,@Location,
@groupcode,@TaskType,@TaskPhoneID,@TaskID,@ActionID,@ActionType,@F_ServiceType)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@CallId", SqlDbType.VarChar,50),
					new SqlParameter("@CallNumber", SqlDbType.VarChar,100),
					new SqlParameter("@CusId", SqlDbType.VarChar,50),
					new SqlParameter("@CusCode", SqlDbType.VarChar,50),
					new SqlParameter("@CallType", SqlDbType.Int,4),
					new SqlParameter("@CallState", SqlDbType.Int,4),
					new SqlParameter("@DealType", SqlDbType.Int,4),
					new SqlParameter("@BeginTime", SqlDbType.DateTime),
					new SqlParameter("@IvrStartTime", SqlDbType.DateTime),
					new SqlParameter("@IvrEndTime", SqlDbType.DateTime),
					new SqlParameter("@RingStartTime", SqlDbType.DateTime),
					new SqlParameter("@RingEndTime", SqlDbType.DateTime),
					new SqlParameter("@TalkStartTime", SqlDbType.DateTime),
					new SqlParameter("@TalkEndTime", SqlDbType.DateTime),
					new SqlParameter("@EndTime", SqlDbType.DateTime),
					new SqlParameter("@LongTime", SqlDbType.Int,4),
					new SqlParameter("@IvrLongTime", SqlDbType.Int,4),
					new SqlParameter("@RingLongTime", SqlDbType.Int,4),
					new SqlParameter("@TalkLongTime", SqlDbType.Int,4),
					new SqlParameter("@UserId", SqlDbType.Int,4),
					new SqlParameter("@UserCode", SqlDbType.VarChar,50),
					new SqlParameter("@UserName", SqlDbType.NVarChar,20),
					new SqlParameter("@ExtNumber", SqlDbType.VarChar,50),
					new SqlParameter("@FilePath", SqlDbType.VarChar,200),
					new SqlParameter("@IsExitWorkOrder", SqlDbType.Bit,1),
					new SqlParameter("@IsDeal", SqlDbType.Int,4),
					new SqlParameter("@OperateType", SqlDbType.Int,4),
					new SqlParameter("@OperateObject", SqlDbType.VarChar,20),
					new SqlParameter("@OperateTime", SqlDbType.DateTime),
					new SqlParameter("@Remark", SqlDbType.NVarChar,1000),
					new SqlParameter("@MYD", SqlDbType.Int,4),
					new SqlParameter("@BusinessType", SqlDbType.Int,4),
					new SqlParameter("@Location", SqlDbType.NVarChar,100),

                    new SqlParameter("@groupcode", SqlDbType.VarChar,50),
                    new SqlParameter("@TaskType", SqlDbType.VarChar,50),
                    new SqlParameter("@TaskPhoneID", SqlDbType.VarChar,50),
                    new SqlParameter("@TaskID", SqlDbType.VarChar,50),
                     new SqlParameter("@ActionID", SqlDbType.Int,4),
                    new SqlParameter("@ActionType", SqlDbType.Int,4),
                    new SqlParameter("@F_ServiceType", SqlDbType.Int,4)
                    

            };
           

            parameters[0].Value = model.CallId;
            parameters[1].Value = model.CallNumber;
            parameters[2].Value = model.CusId;
            parameters[3].Value = model.CusCode;
            parameters[4].Value = model.CallType;
            parameters[5].Value = model.CallState;
            parameters[6].Value = model.DealType;
            parameters[7].Value = model.BeginTime;
            parameters[8].Value = model.IvrStartTime;
            parameters[9].Value = model.IvrEndTime;
            parameters[10].Value = model.RingStartTime;
            parameters[11].Value = model.RingEndTime;
            parameters[12].Value = model.TalkStartTime;
            parameters[13].Value = model.TalkEndTime;
            parameters[14].Value = model.EndTime;
            parameters[15].Value = model.LongTime;
            parameters[16].Value = model.IvrLongTime;
            parameters[17].Value = model.RingLongTime;
            parameters[18].Value = model.TalkLongTime;
            parameters[19].Value = model.UserId;
            parameters[20].Value = model.UserCode;
            parameters[21].Value = model.UserName;
            parameters[22].Value = model.ExtNumber;
            parameters[23].Value = model.FilePath;
            parameters[24].Value = model.IsExitWorkOrder;
            parameters[25].Value = model.IsDeal;
            parameters[26].Value = model.OperateType;
            parameters[27].Value = model.OperateObject;
            parameters[28].Value = model.OperateTime;
            parameters[29].Value = model.Remark;
            parameters[30].Value = model.MYD;
            parameters[31].Value = model.BusinessType;
            parameters[32].Value = model.Location;


            parameters[33].Value = model.groupcode;
            parameters[34].Value = model.TaskType;
            parameters[35].Value = model.TaskPhoneID;
            parameters[36].Value = model.TaskID;
            parameters[37].Value = model.ActionID;
            parameters[38].Value = model.ActionType;
            parameters[39].Value = model.F_ServiceType;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(YTSoft.BaseCallCenter.Model.T_Call_CallRecords model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_Call_CallRecords set ");
            strSql.Append("CallId=@CallId,");
            strSql.Append("CallNumber=@CallNumber,");
            strSql.Append("CusId=@CusId,");
            strSql.Append("CusCode=@CusCode,");
            strSql.Append("CallType=@CallType,");
            strSql.Append("CallState=@CallState,");
            strSql.Append("DealType=@DealType,");
            strSql.Append("BeginTime=@BeginTime,");
            strSql.Append("IvrStartTime=@IvrStartTime,");
            strSql.Append("IvrEndTime=@IvrEndTime,");
            strSql.Append("RingStartTime=@RingStartTime,");
            strSql.Append("RingEndTime=@RingEndTime,");
            strSql.Append("TalkStartTime=@TalkStartTime,");
            strSql.Append("TalkEndTime=@TalkEndTime,");
            strSql.Append("EndTime=@EndTime,");
            strSql.Append("LongTime=@LongTime,");
            strSql.Append("IvrLongTime=@IvrLongTime,");
            strSql.Append("RingLongTime=@RingLongTime,");
            strSql.Append("TalkLongTime=@TalkLongTime,");
            strSql.Append("UserId=@UserId,");
            strSql.Append("UserCode=@UserCode,");
            strSql.Append("UserName=@UserName,");
            strSql.Append("ExtNumber=@ExtNumber,");
            strSql.Append("FilePath=@FilePath,");
            strSql.Append("IsExitWorkOrder=@IsExitWorkOrder,");
            strSql.Append("IsDeal=@IsDeal,");
            strSql.Append("OperateType=@OperateType,");
            strSql.Append("OperateObject=@OperateObject,");
            strSql.Append("OperateTime=@OperateTime,");
            strSql.Append("Remark=@Remark,");
            strSql.Append("MYD=@MYD,");
            strSql.Append("BusinessType=@BusinessType,");
            strSql.Append("Location=@Location");


            //groupcode,TaskType,TaskPhoneID,TaskID,ActionID,ActionType
            strSql.Append("groupcode=@groupcode,");
            strSql.Append("TaskType=@TaskType,");
            strSql.Append("TaskPhoneID=@TaskPhoneID,");
            strSql.Append("TaskID=@TaskID,");
            strSql.Append("ActionID=@ActionID,");
            strSql.Append("F_ServiceType=@F_ServiceType,");
            
            strSql.Append("ActionType=@ActionType");



            strSql.Append(" where CallRecordsId=@CallRecordsId");
            SqlParameter[] parameters = {
					new SqlParameter("@CallId", SqlDbType.VarChar,50),
					new SqlParameter("@CallNumber", SqlDbType.VarChar,100),
					new SqlParameter("@CusId", SqlDbType.VarChar,50),
					new SqlParameter("@CusCode", SqlDbType.VarChar,50),
					new SqlParameter("@CallType", SqlDbType.Int,4),
					new SqlParameter("@CallState", SqlDbType.Int,4),
					new SqlParameter("@DealType", SqlDbType.Int,4),
					new SqlParameter("@BeginTime", SqlDbType.DateTime),
					new SqlParameter("@IvrStartTime", SqlDbType.DateTime),
					new SqlParameter("@IvrEndTime", SqlDbType.DateTime),
					new SqlParameter("@RingStartTime", SqlDbType.DateTime),
					new SqlParameter("@RingEndTime", SqlDbType.DateTime),
					new SqlParameter("@TalkStartTime", SqlDbType.DateTime),
					new SqlParameter("@TalkEndTime", SqlDbType.DateTime),
					new SqlParameter("@EndTime", SqlDbType.DateTime),
					new SqlParameter("@LongTime", SqlDbType.Int,4),
					new SqlParameter("@IvrLongTime", SqlDbType.Int,4),
					new SqlParameter("@RingLongTime", SqlDbType.Int,4),
					new SqlParameter("@TalkLongTime", SqlDbType.Int,4),
					new SqlParameter("@UserId", SqlDbType.Int,4),
					new SqlParameter("@UserCode", SqlDbType.VarChar,50),
					new SqlParameter("@UserName", SqlDbType.NVarChar,20),
					new SqlParameter("@ExtNumber", SqlDbType.VarChar,50),
					new SqlParameter("@FilePath", SqlDbType.VarChar,200),
					new SqlParameter("@IsExitWorkOrder", SqlDbType.Bit,1),
					new SqlParameter("@IsDeal", SqlDbType.Int,4),
					new SqlParameter("@OperateType", SqlDbType.Int,4),
					new SqlParameter("@OperateObject", SqlDbType.VarChar,20),
					new SqlParameter("@OperateTime", SqlDbType.DateTime),
					new SqlParameter("@Remark", SqlDbType.NVarChar,1000),
					new SqlParameter("@MYD", SqlDbType.Int,4),
					new SqlParameter("@BusinessType", SqlDbType.Int,4),
					new SqlParameter("@Location", SqlDbType.NVarChar,100),

                      new SqlParameter("@groupcode", SqlDbType.VarChar,50),
                    new SqlParameter("@TaskType", SqlDbType.VarChar,50),
                    new SqlParameter("@TaskPhoneID", SqlDbType.VarChar,50),
                    new SqlParameter("@TaskID", SqlDbType.VarChar,50),
                     new SqlParameter("@ActionID", SqlDbType.Int,4),
                      new SqlParameter("@F_ServiceType", SqlDbType.Int,4),
                   
                    new SqlParameter("@ActionType", SqlDbType.Int,4),




                    new SqlParameter("@CallRecordsId", SqlDbType.Int,4)};
            parameters[0].Value = model.CallId;
            parameters[1].Value = model.CallNumber;
            parameters[2].Value = model.CusId;
            parameters[3].Value = model.CusCode;
            parameters[4].Value = model.CallType;
            parameters[5].Value = model.CallState;
            parameters[6].Value = model.DealType;
            parameters[7].Value = model.BeginTime;
            parameters[8].Value = model.IvrStartTime;
            parameters[9].Value = model.IvrEndTime;
            parameters[10].Value = model.RingStartTime;
            parameters[11].Value = model.RingEndTime;
            parameters[12].Value = model.TalkStartTime;
            parameters[13].Value = model.TalkEndTime;
            parameters[14].Value = model.EndTime;
            parameters[15].Value = model.LongTime;
            parameters[16].Value = model.IvrLongTime;
            parameters[17].Value = model.RingLongTime;
            parameters[18].Value = model.TalkLongTime;
            parameters[19].Value = model.UserId;
            parameters[20].Value = model.UserCode;
            parameters[21].Value = model.UserName;
            parameters[22].Value = model.ExtNumber;
            parameters[23].Value = model.FilePath;
            parameters[24].Value = model.IsExitWorkOrder;
            parameters[25].Value = model.IsDeal;
            parameters[26].Value = model.OperateType;
            parameters[27].Value = model.OperateObject;
            parameters[28].Value = model.OperateTime;
            parameters[29].Value = model.Remark;
            parameters[30].Value = model.MYD;
            parameters[31].Value = model.BusinessType;
            parameters[32].Value = model.Location;


            parameters[33].Value = model.groupcode;
            parameters[34].Value = model.TaskType;
            parameters[35].Value = model.TaskPhoneID;
            parameters[36].Value = model.TaskID;
            parameters[37].Value = model.ActionID;
            parameters[38].Value = model.F_ServiceType; 
            parameters[39].Value = model.ActionType;


            parameters[40].Value = model.CallRecordsId;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int CallRecordsId)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_Call_CallRecords ");
            strSql.Append(" where CallRecordsId=@CallRecordsId");
            SqlParameter[] parameters = {
					new SqlParameter("@CallRecordsId", SqlDbType.Int,4)
			};
            parameters[0].Value = CallRecordsId;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string CallRecordsIdlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_Call_CallRecords ");
            strSql.Append(" where CallRecordsId in (" + CallRecordsIdlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public YTSoft.BaseCallCenter.Model.T_Call_CallRecords GetModel(int CallRecordsId)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 * from T_Call_CallRecords ");
            strSql.Append(" where CallRecordsId=@CallRecordsId");
            SqlParameter[] parameters = {
					new SqlParameter("@CallRecordsId", SqlDbType.Int,4)
			};
            parameters[0].Value = CallRecordsId;

            YTSoft.BaseCallCenter.Model.T_Call_CallRecords model = new YTSoft.BaseCallCenter.Model.T_Call_CallRecords();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public YTSoft.BaseCallCenter.Model.T_Call_CallRecords DataRowToModel(DataRow row)
        {
            YTSoft.BaseCallCenter.Model.T_Call_CallRecords model = new YTSoft.BaseCallCenter.Model.T_Call_CallRecords();
            if (row != null)
            {
                if (row["CallId"] != null)
                {
                    model.CallId = row["CallId"].ToString();
                }
                if (row["CallNumber"] != null)
                {
                    model.CallNumber = row["CallNumber"].ToString();
                }
                if (row["CusId"] != null)
                {
                    model.CusId = row["CusId"].ToString();
                }
                if (row["CusCode"] != null)
                {
                    model.CusCode = row["CusCode"].ToString();
                }
                if (row["CallType"] != null && row["CallType"].ToString() != "")
                {
                    model.CallType = int.Parse(row["CallType"].ToString());
                }
                if (row["CallState"] != null && row["CallState"].ToString() != "")
                {
                    model.CallState = int.Parse(row["CallState"].ToString());
                }
                if (row["DealType"] != null && row["DealType"].ToString() != "")
                {
                    model.DealType = int.Parse(row["DealType"].ToString());
                }
                if (row["BeginTime"] != null && row["BeginTime"].ToString() != "")
                {
                    model.BeginTime = DateTime.Parse(row["BeginTime"].ToString());
                }
                if (row["IvrStartTime"] != null && row["IvrStartTime"].ToString() != "")
                {
                    model.IvrStartTime = DateTime.Parse(row["IvrStartTime"].ToString());
                }
                if (row["IvrEndTime"] != null && row["IvrEndTime"].ToString() != "")
                {
                    model.IvrEndTime = DateTime.Parse(row["IvrEndTime"].ToString());
                }
                if (row["RingStartTime"] != null && row["RingStartTime"].ToString() != "")
                {
                    model.RingStartTime = DateTime.Parse(row["RingStartTime"].ToString());
                }
                if (row["RingEndTime"] != null && row["RingEndTime"].ToString() != "")
                {
                    model.RingEndTime = DateTime.Parse(row["RingEndTime"].ToString());
                }
                if (row["TalkStartTime"] != null && row["TalkStartTime"].ToString() != "")
                {
                    model.TalkStartTime = DateTime.Parse(row["TalkStartTime"].ToString());
                }
                if (row["TalkEndTime"] != null && row["TalkEndTime"].ToString() != "")
                {
                    model.TalkEndTime = DateTime.Parse(row["TalkEndTime"].ToString());
                }
                if (row["EndTime"] != null && row["EndTime"].ToString() != "")
                {
                    model.EndTime = DateTime.Parse(row["EndTime"].ToString());
                }
                if (row["LongTime"] != null && row["LongTime"].ToString() != "")
                {
                    model.LongTime = int.Parse(row["LongTime"].ToString());
                }
                if (row["IvrLongTime"] != null && row["IvrLongTime"].ToString() != "")
                {
                    model.IvrLongTime = int.Parse(row["IvrLongTime"].ToString());
                }
                if (row["RingLongTime"] != null && row["RingLongTime"].ToString() != "")
                {
                    model.RingLongTime = int.Parse(row["RingLongTime"].ToString());
                }
                if (row["TalkLongTime"] != null && row["TalkLongTime"].ToString() != "")
                {
                    model.TalkLongTime = int.Parse(row["TalkLongTime"].ToString());
                }
                if (row["UserId"] != null && row["UserId"].ToString() != "")
                {
                    model.UserId = int.Parse(row["UserId"].ToString());
                }
                if (row["UserCode"] != null)
                {
                    model.UserCode = row["UserCode"].ToString();
                }
                if (row["UserName"] != null)
                {
                    model.UserName = row["UserName"].ToString();
                }
                if (row["ExtNumber"] != null)
                {
                    model.ExtNumber = row["ExtNumber"].ToString();
                }
                if (row["FilePath"] != null)
                {
                    model.FilePath = row["FilePath"].ToString();
                }
                if (row["IsExitWorkOrder"] != null && row["IsExitWorkOrder"].ToString() != "")
                {
                    if ((row["IsExitWorkOrder"].ToString() == "1") || (row["IsExitWorkOrder"].ToString().ToLower() == "true"))
                    {
                        model.IsExitWorkOrder = true;
                    }
                    else
                    {
                        model.IsExitWorkOrder = false;
                    }
                }
                if (row["IsDeal"] != null && row["IsDeal"].ToString() != "")
                {
                    model.IsDeal = int.Parse(row["IsDeal"].ToString());
                }
                if (row["OperateType"] != null && row["OperateType"].ToString() != "")
                {
                    model.OperateType = int.Parse(row["OperateType"].ToString());
                }
                if (row["OperateObject"] != null)
                {
                    model.OperateObject = row["OperateObject"].ToString();
                }
                if (row["OperateTime"] != null && row["OperateTime"].ToString() != "")
                {
                    model.OperateTime = DateTime.Parse(row["OperateTime"].ToString());
                }
                if (row["Remark"] != null)
                {
                    model.Remark = row["Remark"].ToString();
                }
                if (row["MYD"] != null && row["MYD"].ToString() != "")
                {
                    model.MYD = int.Parse(row["MYD"].ToString());
                }
                if (row["BusinessType"] != null && row["BusinessType"].ToString() != "")
                {
                    model.BusinessType = int.Parse(row["BusinessType"].ToString());
                }
                if (row["Location"] != null)
                {
                    model.Location = row["Location"].ToString();
                }
                if (row["CallRecordsId"] != null && row["CallRecordsId"].ToString() != "")
                {
                    model.CallRecordsId = int.Parse(row["CallRecordsId"].ToString());
                }

                //--groupcode,TaskType,TaskPhoneID,TaskID,ActionID,ActionType
                if (row["groupcode"] != null)
                {
                    model.groupcode = row["groupcode"].ToString();
                }
                if (row["TaskType"] != null)
                {
                    model.TaskType = row["TaskType"].ToString();
                }
                if (row["TaskPhoneID"] != null)
                {
                    model.TaskPhoneID = row["TaskPhoneID"].ToString();
                }
                if (row["TaskID"] != null)
                {
                    model.TaskID = row["TaskID"].ToString();
                }
                if (row["ActionID"] != null && row["ActionID"].ToString() != "")
                {
                    model.ActionID = int.Parse(row["ActionID"].ToString());
                }
                if (row["ActionType"] != null && row["ActionType"].ToString() != "")
                {
                    model.ActionType = int.Parse(row["ActionType"].ToString());

                }


                if (row["CallRecordsId"] != null && row["CallRecordsId"].ToString() != "")
                {
                    model.CallRecordsId = int.Parse(row["CallRecordsId"].ToString());
                }

                
                     if (row["F_ServiceType"] != null && row["F_ServiceType"].ToString() != "")
                {
                    model.F_ServiceType = int.Parse(row["F_ServiceType"].ToString());
                }

            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM T_Call_CallRecords ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" * ");
            strSql.Append(" FROM T_Call_CallRecords ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM T_Call_CallRecords ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.CallRecordsId desc");
            }
            strSql.Append(")AS Row, T.*  from T_Call_CallRecords T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "T_Call_CallRecords";
            parameters[1].Value = "CallRecordsId";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  BasicMethod
        #region  ExtensionMethod

        #endregion  ExtensionMethod

        #region 更新来电振铃信息

        /// <summary>
        /// 更新来电振铃信息
        /// </summary>
        public bool UpdateCallInRingTelRecord(Model.T_Call_CallRecords model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_Call_CallRecords set ");
            strSql.Append("IvrLongTime=datediff(second,IvrStartTime,IvrEndTime),");
            strSql.Append("WaitEndTime=getdate(),");
            strSql.Append("WaitLongTime=datediff(second,WaitStartTime,getdate()),");
            strSql.Append("RingStartTime=getdate(),");
            strSql.Append("UserId=@UserId,");
            strSql.Append("UserCode=@UserCode,");
            strSql.Append("UserName=@UserName,");
            strSql.Append("ExtNumber=@ExtNumber,");
            strSql.Append("NoCallState=3,");//未接状态为振铃
            strSql.Append("DealType=@DealType");
            strSql.Append(" where CallId=@CallId ");
            SqlParameter[] parameters = {
					new SqlParameter("@UserId", SqlDbType.Int,4),
					new SqlParameter("@UserCode", SqlDbType.VarChar,50),
					new SqlParameter("@UserName", SqlDbType.NVarChar,20),
					new SqlParameter("@ExtNumber", SqlDbType.VarChar,50),				
					new SqlParameter("@CallId", SqlDbType.VarChar,50),
                    new SqlParameter("@DealType", SqlDbType.Int,4)};

            parameters[0].Value = model.UserId;
            parameters[1].Value = model.UserCode;
            parameters[2].Value = model.UserName;
            parameters[3].Value = model.ExtNumber;
            parameters[4].Value = model.CallId;
            parameters[5].Value = model.DealType;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        #endregion

        #region 更新来电接通信息

        /// <summary>
        /// 更新来电接通信息
        /// </summary>
        public bool UpdateCallInAnswerTelRecord(Model.T_Call_CallRecords model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_Call_CallRecords set ");
            strSql.Append("CallState=@CallState,");
            strSql.Append("RingEndTime=getdate(),");
            strSql.Append("RingLongTime=datediff(second,RingStartTime,getdate()),");
            strSql.Append("TalkStartTime=getdate(),");
            strSql.Append("DealType=@DealType");
            strSql.Append(" where CallId=@CallId");
            SqlParameter[] parameters = {
					new SqlParameter("@CallId", SqlDbType.VarChar,50),
					new SqlParameter("@CallState", SqlDbType.Int,4),
                      new SqlParameter("@DealType", SqlDbType.Int,4)};
            parameters[0].Value = model.CallId;
            parameters[1].Value = model.CallState;
            parameters[2].Value = model.DealType;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        #endregion

        #region 更新来电挂断电话记录信息

        /// <summary>
        /// 更新来电挂断电话记录信息
        /// </summary>
        public bool UpdateCallInHookTelRecord(string p_CallId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_Call_CallRecords SET TalkLongTime=datediff(second,TalkStartTime,getdate()), LongTime=datediff(second,BeginTime,getdate()), RingEndTime=(case when RingEndTime is  null then GETDATE() else RingEndTime end ),TalkEndTime=(case when TalkStartTime is null then null else GETDATE() end),EndTime=GETDATE(),DealType=(case CallState when 1 then 6 else DealType end)");
            strSql.Append(" where CallId=@CallId");
            SqlParameter[] parameters = {
					new SqlParameter("@CallId", SqlDbType.VarChar,50)};
            parameters[0].Value = p_CallId;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        #endregion

        #region 更新来电录音开始信息

        /// <summary>
        /// 更新来电录音开始信息
        /// </summary>
        public bool UpdateCallInPathTelRecord(Model.T_Call_CallRecords model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_Call_CallRecords set ");
            strSql.Append("CallState=@CallState,");
            strSql.Append("FilePath=@FilePath,");
            strSql.Append("DealType=@DealType");
            strSql.Append(" where CallId=@CallId");
            SqlParameter[] parameters = {
					new SqlParameter("@CallId", SqlDbType.VarChar,50),
					new SqlParameter("@CallState", SqlDbType.Int,4),
                    new SqlParameter("@FilePath", SqlDbType.VarChar,200),
                      new SqlParameter("@DealType", SqlDbType.Int,4)};
            parameters[0].Value = model.CallId;
            parameters[1].Value = model.CallState;
            parameters[2].Value = model.FilePath;
            parameters[3].Value = model.DealType;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        #endregion

        #region 根据来电号码获取电话记录
        public YTSoft.BaseCallCenter.Model.T_Call_CallRecords GetModelByTelphone(string CallNumber)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 * from T_Call_CallRecords ");
            strSql.Append(" where CallNumber=@CallNumber order by BeginTime desc");
            SqlParameter[] parameters = {
					new SqlParameter("@CallNumber", SqlDbType.VarChar,100)
			};
            parameters[0].Value = CallNumber;

            YTSoft.BaseCallCenter.Model.T_Call_CallRecords model = new YTSoft.BaseCallCenter.Model.T_Call_CallRecords();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 根据CallId获取电话记录
        public YTSoft.BaseCallCenter.Model.T_Call_CallRecords GetModelByCallId(string CallId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 * from T_Call_CallRecords ");
            strSql.Append(" where CallId=@CallId order by BeginTime desc");
            SqlParameter[] parameters = {
					new SqlParameter("@CallId", SqlDbType.VarChar,50)
			};
            parameters[0].Value = CallId;

            YTSoft.BaseCallCenter.Model.T_Call_CallRecords model = new YTSoft.BaseCallCenter.Model.T_Call_CallRecords();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        public YTSoft.BaseCallCenter.Model.T_Call_CallRecords GetModelByRecordId(string CallId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 * from T_Call_CallRecords ");
            strSql.Append(" where CallRecordsId=@CallRecordsId order by BeginTime desc");
            SqlParameter[] parameters = {
					new SqlParameter("@CallRecordsId", SqlDbType.Int)
			};
            parameters[0].Value = Convert.ToInt32(CallId);

            YTSoft.BaseCallCenter.Model.T_Call_CallRecords model = new YTSoft.BaseCallCenter.Model.T_Call_CallRecords();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 更新来电处理状态信息

        /// <summary>
        /// 更新来电处理状态信息
        /// </summary>
        public bool UpdateCallInRecordDealType(string callid, int type)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_Call_CallRecords set ");
            strSql.Append("DealType=@DealType");
            strSql.Append(" where CallId=@CallId ");
            SqlParameter[] parameters = {			
					new SqlParameter("@CallId", SqlDbType.VarChar,50),
                    new SqlParameter("@DealType", SqlDbType.Int,4)};


            parameters[0].Value = callid;
            parameters[1].Value = type;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        #endregion


        #region 报表数据


        /// <summary>
        /// 获取小时分布数据
        /// </summary>
        /// <param name="StartDate">开始日期</param>
        /// <param name="EndDate">结束日期</param>
        /// <returns></returns>
        public DataTable GetdTypeData(string dType, int? busType, string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  COUNT(*) callInCount,DATEPART(" + dType + ", BeginTime) DateType FROM T_Call_CallRecords ");
            strSql.Append(" where CallType=0 ");
            if (!string.IsNullOrEmpty(startDate))
            {
                strSql.Append(" and BeginTime>='" + startDate + "  00:00:00' ");
            }

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

            if (busType != null && (int)busType > 0)
            {
                if ((int)busType == 1)
                {
                    strSql.Append(" and F_REPAIRMANID<=2 ");
                }
                else if ((int)busType == 2)
                {
                    strSql.Append(" and F_REPAIRMANID=3 ");
                }
                else if ((int)busType == 3)
                {
                    strSql.Append(" and F_REPAIRMANID=4 ");
                }
            }
            strSql.Append("  GROUP BY DATEPART(" + dType + ", BeginTime) ORDER BY DateType asc");

            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 GetHourData(string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  COUNT(*) callInCount,DATEPART(HH, BeginTime) CallHour FROM T_Call_CallRecords ");
            strSql.Append(" where CallType=0 ");
            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 DATEPART(HH, BeginTime) ORDER BY CallHour asc");

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


        }
        public DataTable GetHourDataList(string CallHour, string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select *,CONVERT(varchar,BeginTime, 120 ) as BeginTimeNew,CONVERT(varchar,TalkStartTime, 120 ) as TalkStartTimeNew,CONVERT(varchar,TalkEndTime, 120 ) as TalkEndTimeNew,CONVERT(varchar,TalkStartTime, 120 ) as TalkStartTimeNew,CONVERT(varchar,TalkEndTime, 120 ) as TalkEndTimeNew from T_Call_CallRecords");
            strSql.Append(" where CallType=0 ");
            if (!string.IsNullOrEmpty(startDate))
            {
                strSql.Append(" and BeginTime>='" + startDate + "  00:00:00' ");
            }

            if (!string.IsNullOrEmpty(endDate))
            {
                strSql.Append(" and BeginTime<='" + endDate + " 23:59:59' ");
            }
            if (!string.IsNullOrEmpty(CallHour))
            {
                strSql.Append(" and DATEPART(HH, BeginTime)=" + CallHour);
            }
            strSql.Append("   ORDER BY CallId desc");

            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 GetBussTypeData(string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT   COUNT(*) callInCount,BusinessType FROM T_Call_CallRecords  ");
            strSql.Append(" where CallType=0 ");
            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 BusinessType ORDER BY BusinessType ASC");
            strSql.Append("   GROUP BY BusinessType ORDER BY BusinessType ASC");
            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 GetWorkOrderBussTypeData(string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  COUNT(*) callInCount,F_FILEFLAG BusinessType,F_HOUSING BusinessTypeName 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("   GROUP BY F_FILEFLAG,F_HOUSING ORDER BY F_HOUSING ASC");

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


        }


        public DataTable GetWorkOrderBussTypeDataRX(string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  COUNT(*) callInCount,F_FILEFLAG BusinessType,F_HOUSING BusinessTypeName 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("   GROUP BY F_FILEFLAG,F_HOUSING ORDER BY F_HOUSING ASC");

            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 GetWorkOrderBussTypeFromData(string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  COUNT(*) callInCount,F_FILEFLAG BusinessType,F_HOUSING BusinessTypeName,F_REPAIRMANID,F_WORKORDERFROM 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("   GROUP BY F_FILEFLAG,F_HOUSING ,F_REPAIRMANID,F_WORKORDERFROM ORDER BY F_HOUSING ASC");

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


        }
        public DataTable GetWorkOrderBussTypeFromDataNew(string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  COUNT(*) callInCount,F_FILEFLAG BusinessType,F_HOUSING BusinessTypeName,F_REPAIRMANID,F_WORKORDERFROM,F_RETURNVISITPROBLEM,F_WORKORDERTYPEID FROM T_Wo_WorkOrderBase  ");
            strSql.Append(" where 1=1  and F_FILEFLAG>0 AND F_WORKORDERTYPEID>0 ");
            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_FILEFLAG,F_HOUSING ,F_REPAIRMANID,F_WORKORDERFROM,F_RETURNVISITPROBLEM,F_WORKORDERTYPEID ORDER BY F_HOUSING ASC");

            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 GetWorkOrderWeekData(string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  COUNT(*) callInCount,F_FILEFLAG BusinessType,datepart(weekday,F_CREATEDATE) Weeks FROM T_Wo_WorkOrderBase   ");
            strSql.Append(" where 1=1  ");
            if (!string.IsNullOrEmpty(startDate))
            {
                strSql.Append(" and F_CreateDate>='" + startDate + "' ");
            }

            if (!string.IsNullOrEmpty(endDate))
            {
                strSql.Append(" and F_CreateDate<='" + endDate + "' ");
            }
            strSql.Append(" GROUP BY F_FILEFLAG,datepart(weekday,F_CREATEDATE)");

            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 GetWorkOrderUeserData(string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT F_LINKMAN 坐席工号,F_REPAIRMANNAME 坐席姓名, COUNT(*) 业务量,F_REPAIRMANID 渠道 FROM T_Wo_WorkOrderBase   ");
            strSql.Append(" where  1=1  and F_REPAIRLEVEL>0 and F_FILEFLAG>0 ");
            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_LINKMAN,F_REPAIRMANNAME,F_REPAIRMANID ");

            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 GetWorkOrderFrom(string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  COUNT(*) callInCount,F_REPAIRMANID 渠道 FROM T_Wo_WorkOrderBase  ");
            strSql.Append(" where 1=1  and F_REPAIRLEVEL>0 and F_FILEFLAG>0");
            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_REPAIRMANID");

            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 GetWorkOrderTrainFunction(string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  F_LINKMAN 坐席工号 , F_REPAIRMANNAME 坐席姓名 , COUNT(*) 业务量 ,F_FILEFLAG 业务类别,F_WORKORDERTYPEID 处理方式 FROM T_Wo_WorkOrderBase   ");
            strSql.Append(" where 1=1 and F_FILEFLAG>0 AND F_WORKORDERTYPEID>0 ");
            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_LINKMAN,F_REPAIRMANNAME, F_FILEFLAG,F_WORKORDERTYPEID");

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


        }

        public DataTable GetWorkOrderTrainFunctionRX(string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  F_LINKMAN 坐席工号 , F_REPAIRMANNAME 坐席姓名 , COUNT(*) 业务量 ,F_FILEFLAG 业务类别,F_WORKORDERTYPEID 处理方式 ,F_REPAIRMANID FROM T_Wo_WorkOrderBase   ");
            strSql.Append(" where 1=1 and F_FILEFLAG>0 AND F_WORKORDERTYPEID>0 AND F_REPAIRMANID<=2");
            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_LINKMAN,F_REPAIRMANNAME, F_FILEFLAG,F_WORKORDERTYPEID ,F_REPAIRMANID");

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


        }
        public DataTable GetWorkOrderTrainFunctionRXQT(string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  F_LINKMAN 坐席工号 , F_REPAIRMANNAME 坐席姓名 , COUNT(*) 业务量 ,F_FILEFLAG 业务类别,F_WORKORDERTYPEID 处理方式 ,F_REPAIRMANID FROM T_Wo_WorkOrderBase   ");
            strSql.Append(" where 1=1 and F_FILEFLAG>0 AND F_WORKORDERTYPEID>0 ");
            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_LINKMAN,F_REPAIRMANNAME, F_FILEFLAG,F_WORKORDERTYPEID ,F_REPAIRMANID");

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


        }
        public DataTable GetWorkOrderTrainFunctionRXWX(string startDate, string endDate,string wbwx)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  F_LINKMAN 坐席工号 , F_REPAIRMANNAME 坐席姓名 , COUNT(*) 业务量 ,F_FILEFLAG 业务类别,F_WORKORDERTYPEID 处理方式 ,F_REPAIRMANID FROM T_Wo_WorkOrderBase   ");
            strSql.Append(" where 1=1 and F_FILEFLAG>0 AND F_WORKORDERTYPEID>0 ");
             
            switch(wbwx.Trim())
             {
                case "3": strSql.Append(" and F_REPAIRMANID=3");break;//微信
                case "4": strSql.Append(" and F_REPAIRMANID=4"); break;//微博
                default: strSql.Append(" and F_REPAIRMANID in (1,2)"); break;//1、2语音
            }
            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_LINKMAN,F_REPAIRMANNAME, F_FILEFLAG,F_WORKORDERTYPEID ,F_REPAIRMANID");

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


        }
        public DataTable GetWorkOrderTrainFunctionRXMX(string startDate, string endDate,string sql)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT   COUNT(*) YWSL,F_REPAIRLEVEL,F_FILEFLAG  FROM T_Wo_WorkOrderBase ");
            strSql.Append(" where 1=1  ");
            if (!string.IsNullOrEmpty(sql)) { 
            strSql.Append(" and " +sql);
            }
            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_REPAIRLEVEL,F_FILEFLAG ORDER BY YWSL DESC");

            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 CallTimeMath(string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT userId, COUNT(*) 呼叫次数,SUM(TalkLongTime) 通话时长,CallType 呼叫类型 FROM T_Call_CallRecords    ");
            strSql.Append(" WHERE userId>0  ");
            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 CallType,userId ");

            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 WorkOrderDate(string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT ");
            strSql.Append("  CASE WHEN F_REPAIRMANID = 1 THEN '语音' WHEN F_REPAIRMANID = 2 THEN '语音'WHEN F_REPAIRMANID = 3 THEN '微信' WHEN F_REPAIRMANID = 4 THEN '微博' END 工单类型 , ");
            strSql.Append(" F_HOUSING , ");
            strSql.Append(" F_ADSLACCOUNT , ");
            strSql.Append("   CASE WHEN F_FILEFLAG = 3 THEN ");
            strSql.Append("  CASE WHEN F_WORKORDERTYPEID = 3 THEN '客服处理' ");
            strSql.Append("      WHEN F_WORKORDERTYPEID = 1 THEN '转办' ");
            strSql.Append("   END ");
            strSql.Append("ELSE '' ");
            strSql.Append(" END  处理方式 , ");
            strSql.Append("CASE WHEN F_FILEFLAG =3 then ");
            strSql.Append("  CASE WHEN F_WORKORDERTYPEID = 1 THEN ");
            strSql.Append("   CASE WHEN ISNULL(F_RETURNVISITRESULT, 2) = 0 THEN '一般' ");
            strSql.Append("      WHEN ISNULL(F_RETURNVISITRESULT, 2) = 1 THEN '满意' ");
            strSql.Append("    WHEN ISNULL(F_RETURNVISITRESULT, 2) = 2 THEN '一般' ");
            strSql.Append("      WHEN ISNULL(F_RETURNVISITRESULT, 2) = 3 THEN '不满意' ");
            strSql.Append("  END ");
            strSql.Append("ELSE '' ");
            strSql.Append("	 END  ");
            strSql.Append("ELSE '' ");
            strSql.Append(" END  ");
            strSql.Append("AS 满意度 , ");
            strSql.Append(" COUNT(*) AS 总数 ");
            strSql.Append("FROM    [dbo].[T_Wo_WorkOrderBase] ");
            strSql.Append("WHERE   F_FILEFLAG > 0 ");
            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 CASE WHEN F_REPAIRMANID = 1 THEN '语音' ");
            strSql.Append("    WHEN F_REPAIRMANID = 2 THEN '语音' ");
            strSql.Append("  WHEN F_REPAIRMANID = 3 THEN '微信' ");
            strSql.Append("  WHEN F_REPAIRMANID = 4 THEN '微博' ");
            strSql.Append("  END , ");
            strSql.Append("  F_FILEFLAG , ");
            strSql.Append("   F_HOUSING , ");
            strSql.Append("  F_REPAIRLEVEL , ");
            strSql.Append("  F_ADSLACCOUNT , ");
            strSql.Append("  CASE WHEN F_FILEFLAG = 3 THEN ");
            strSql.Append("  CASE WHEN F_WORKORDERTYPEID = 3 THEN '客服处理' ");
            strSql.Append("      WHEN F_WORKORDERTYPEID = 1 THEN '转办' ");
            strSql.Append("   END ");
            strSql.Append("	ELSE '' ");
            strSql.Append("	 END  ");
            strSql.Append("	, ");
            strSql.Append("	CASE WHEN F_FILEFLAG =3 then ");
            strSql.Append("  CASE WHEN F_WORKORDERTYPEID = 1 THEN ");
            strSql.Append("   CASE WHEN ISNULL(F_RETURNVISITRESULT, 2) = 0 THEN '一般' ");
            strSql.Append("       WHEN ISNULL(F_RETURNVISITRESULT, 2) = 1 THEN '满意' ");
            strSql.Append("      WHEN ISNULL(F_RETURNVISITRESULT, 2) = 2 THEN '一般'");
            strSql.Append("      WHEN ISNULL(F_RETURNVISITRESULT, 2) = 3 THEN '不满意'");
            strSql.Append("   END");
            strSql.Append("	ELSE ''");
            strSql.Append("	 END ");
            strSql.Append("ELSE '' ");
            strSql.Append("	 END  ");
            strSql.Append("ORDER BY 工单类型,F_FILEFLAG,F_ADSLACCOUNT ");


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


        }




        #endregion

        #region 获取在线坐席

        /// <summary>
        /// 获取在线坐席
        /// </summary>
        /// <returns></returns>
        public DataTable GetLoginUserInfo()
        {
            //当前日期
            string todayDate = DateTime.Now.ToString("yyyy-MM-dd");
            DateTime nowDate = DateTime.Now;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT u.F_UserName userName,F_UserCode userCode FROM T_Sys_LoginLogs l LEFT JOIN  T_Sys_UserAccount u  ON l.F_LoginId=u.F_UserId WHERE u.F_SeatFlag=1 AND l.F_LoginDate>='" + todayDate + " 00:00:01'");
            strSql.Append(" AND l.F_LoginDate<='" + todayDate + " 23:59:59' GROUP BY  u.F_UserName,F_UserCode");
            if (DateTime.Parse((todayDate + " 08:30:00")) <= nowDate && nowDate < DateTime.Parse((todayDate + " 20:30:00")))
            {


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


        }




        #endregion


        #region 获取坐席微信/微博工单数量


        /// <summary>
        /// 获取坐席微信/微博工单数量
        /// </summary>
        /// <param name="StartDate">开始日期</param>
        /// <param name="EndDate">结束日期</param>
        /// <returns></returns>
        public int  WorkOrderCount( int? busType, string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  COUNT(*) callInCount 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' ");
            }
            if (busType != null && (int)busType > 0)
            {
                if ((int)busType == 1)
                {
                    strSql.Append(" and F_REPAIRMANID<=2 ");
                }
                else if ((int)busType == 2)
                {
                    strSql.Append(" and F_REPAIRMANID=3 ");
                }
                else if ((int)busType == 3)
                {
                    strSql.Append(" and F_REPAIRMANID=4 ");
                }
            }
            object  totalCount = DbHelperSQL.GetSingle(strSql.ToString());
            
            if(totalCount!=null&&!string.IsNullOrEmpty(totalCount.ToString()))
            {
                return int.Parse(totalCount.ToString());
            }
            else
            {
                return 0;
            }


        }


        #endregion


        /// <summary>
        /// 重新设置线路
        /// </summary>
        public bool SetLine(int F_Linenum,string CallId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Update T_Call_CallRecords ");
            strSql.Append(" set F_Linenum=@F_Linenum");
            strSql.Append(" Where CallId=@CallId");
            SqlParameter[] parameters = {
                    new SqlParameter("@F_Linenum", SqlDbType.Int,4),
                    new SqlParameter("@CallId", SqlDbType.VarChar,10)
            };
            parameters[0].Value = F_Linenum;
            parameters[1].Value = CallId;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

    }
}