IM12345_Api/CallCenterCo.../CallCenter.W.../ChatSql.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CallCenterApi.DB;
using System.Data.SqlClient;
using System.Data;

namespace CallCenter.WebChatServer
{
    public class ChatSql
    {
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int AddUser(T_Chat_User model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into T_Chat_User(");
            strSql.Append("F_Type,F_Name,F_Account,F_OpenId,F_HeadImgUrl,F_MainUserCode,F_OtherUserCode,F_IP,F_State,F_CreateUser,F_CreateTime,F_IsDelete,F_DeleteUser,F_DeleteTime)");
            strSql.Append(" values (");
            strSql.Append("@F_Type,@F_Name,@F_Account,@F_OpenId,@F_HeadImgUrl,@F_MainUserCode,@F_OtherUserCode,@F_IP,@F_State,@F_CreateUser,@F_CreateTime,@F_IsDelete,@F_DeleteUser,@F_DeleteTime)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
                    new SqlParameter("@F_Type", SqlDbType.Int,4),
                    new SqlParameter("@F_Name", SqlDbType.NVarChar,500),
                    new SqlParameter("@F_Account", SqlDbType.VarChar,500),
                    new SqlParameter("@F_OpenId", SqlDbType.VarChar,500),
                    new SqlParameter("@F_HeadImgUrl", SqlDbType.VarChar,500),
                    new SqlParameter("@F_MainUserCode", SqlDbType.VarChar,50),
                    new SqlParameter("@F_OtherUserCode", SqlDbType.VarChar,500),
                    new SqlParameter("@F_IP", SqlDbType.VarChar,50),
                    new SqlParameter("@F_State", SqlDbType.Int,4),
                    new SqlParameter("@F_CreateUser", SqlDbType.VarChar,50),
                    new SqlParameter("@F_CreateTime", SqlDbType.DateTime),
                    new SqlParameter("@F_IsDelete", SqlDbType.Int,4),
                    new SqlParameter("@F_DeleteUser", SqlDbType.VarChar,50),
                    new SqlParameter("@F_DeleteTime", SqlDbType.DateTime)};
            parameters[0].Value = model.F_Type;
            parameters[1].Value = model.F_Name;
            parameters[2].Value = model.F_Account;
            parameters[3].Value = model.F_OpenId;
            parameters[4].Value = model.F_HeadImgUrl;
            parameters[5].Value = model.F_MainUserCode;
            parameters[6].Value = model.F_OtherUserCode;
            parameters[7].Value = model.F_IP;
            parameters[8].Value = model.F_State;
            parameters[9].Value = model.F_CreateUser;
            parameters[10].Value = model.F_CreateTime;
            parameters[11].Value = model.F_IsDelete;
            parameters[12].Value = model.F_DeleteUser;
            parameters[13].Value = model.F_DeleteTime;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool UpdateUser(T_Chat_User model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_Chat_User set ");
            strSql.Append("F_Type=@F_Type,");
            strSql.Append("F_Name=@F_Name,");
            strSql.Append("F_Account=@F_Account,");
            strSql.Append("F_OpenId=@F_OpenId,");
            strSql.Append("F_HeadImgUrl=@F_HeadImgUrl,");
            strSql.Append("F_MainUserCode=@F_MainUserCode,");
            strSql.Append("F_OtherUserCode=@F_OtherUserCode,");
            strSql.Append("F_IP=@F_IP,");
            strSql.Append("F_State=@F_State,");
            strSql.Append("F_CreateUser=@F_CreateUser,");
            strSql.Append("F_CreateTime=@F_CreateTime,");
            strSql.Append("F_IsDelete=@F_IsDelete,");
            strSql.Append("F_DeleteUser=@F_DeleteUser,");
            strSql.Append("F_DeleteTime=@F_DeleteTime");
            strSql.Append(" where F_Id=@F_Id");
            SqlParameter[] parameters = {
                    new SqlParameter("@F_Type", SqlDbType.Int,4),
                    new SqlParameter("@F_Name", SqlDbType.NVarChar,500),
                    new SqlParameter("@F_Account", SqlDbType.VarChar,500),
                    new SqlParameter("@F_OpenId", SqlDbType.VarChar,500),
                    new SqlParameter("@F_HeadImgUrl", SqlDbType.VarChar,500),
                    new SqlParameter("@F_MainUserCode", SqlDbType.VarChar,50),
                    new SqlParameter("@F_OtherUserCode", SqlDbType.VarChar,500),
                    new SqlParameter("@F_IP", SqlDbType.VarChar,50),
                    new SqlParameter("@F_State", SqlDbType.Int,4),
                    new SqlParameter("@F_CreateUser", SqlDbType.VarChar,50),
                    new SqlParameter("@F_CreateTime", SqlDbType.DateTime),
                    new SqlParameter("@F_IsDelete", SqlDbType.Int,4),
                    new SqlParameter("@F_DeleteUser", SqlDbType.VarChar,50),
                    new SqlParameter("@F_DeleteTime", SqlDbType.DateTime),
                    new SqlParameter("@F_Id", SqlDbType.Int,4)};
            parameters[0].Value = model.F_Type;
            parameters[1].Value = model.F_Name;
            parameters[2].Value = model.F_Account;
            parameters[3].Value = model.F_OpenId;
            parameters[4].Value = model.F_HeadImgUrl;
            parameters[5].Value = model.F_MainUserCode;
            parameters[6].Value = model.F_OtherUserCode;
            parameters[7].Value = model.F_IP;
            parameters[8].Value = model.F_State;
            parameters[9].Value = model.F_CreateUser;
            parameters[10].Value = model.F_CreateTime;
            parameters[11].Value = model.F_IsDelete;
            parameters[12].Value = model.F_DeleteUser;
            parameters[13].Value = model.F_DeleteTime;
            parameters[14].Value = model.F_Id;

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

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public long AddMsg(T_Chat_Message model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into T_Chat_Message(");
            strSql.Append("F_FromUser,F_ToUser,F_Message,F_File,F_HumanClass,F_IP,F_SourceType,F_MessageType,F_State,F_CreateUser,F_CreateTime,F_IsDelete,F_DeleteUser,F_DeleteTime)");
            strSql.Append(" values (");
            strSql.Append("@F_FromUser,@F_ToUser,@F_Message,@F_File,@F_HumanClass,@F_IP,@F_SourceType,@F_MessageType,@F_State,@F_CreateUser,@F_CreateTime,@F_IsDelete,@F_DeleteUser,@F_DeleteTime)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
                    new SqlParameter("@F_FromUser", SqlDbType.VarChar,500),
                    new SqlParameter("@F_ToUser", SqlDbType.VarChar,500),
                    new SqlParameter("@F_Message", SqlDbType.NVarChar,-1),
                    new SqlParameter("@F_File", SqlDbType.VarChar,-1),
                    new SqlParameter("@F_HumanClass", SqlDbType.Int,4),
                    new SqlParameter("@F_IP", SqlDbType.VarChar,50),
                    new SqlParameter("@F_SourceType", SqlDbType.Int,4),
                    new SqlParameter("@F_MessageType", SqlDbType.Int,4),
                    new SqlParameter("@F_State", SqlDbType.Int,4),
                    new SqlParameter("@F_CreateUser", SqlDbType.VarChar,500),
                    new SqlParameter("@F_CreateTime", SqlDbType.DateTime),
                    new SqlParameter("@F_IsDelete", SqlDbType.Int,4),
                    new SqlParameter("@F_DeleteUser", SqlDbType.VarChar,50),
                    new SqlParameter("@F_DeleteTime", SqlDbType.DateTime)};
            parameters[0].Value = model.F_FromUser;
            parameters[1].Value = model.F_ToUser;
            parameters[2].Value = model.F_Message;
            parameters[3].Value = model.F_File;
            parameters[4].Value = model.F_HumanClass;
            parameters[5].Value = model.F_IP;
            parameters[6].Value = model.F_SourceType;
            parameters[7].Value = model.F_MessageType;
            parameters[8].Value = model.F_State;
            parameters[9].Value = model.F_CreateUser;
            parameters[10].Value = model.F_CreateTime;
            parameters[11].Value = model.F_IsDelete;
            parameters[12].Value = model.F_DeleteUser;
            parameters[13].Value = model.F_DeleteTime;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt64(obj);
            }
        }

        /// <summary>
		/// 增加一条数据
		/// </summary>
		public int AddIP(T_Chat_IPList model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into T_Chat_IPList(");
            strSql.Append("F_OpenId,F_IP,F_Country,F_CountryCode,F_Area,F_AreaCode,F_Region,F_RegionCode,F_City,F_CityCode,F_County,F_CountyCode,F_ISP,F_ISPCode,F_CreateTime,F_Type)");
            strSql.Append(" values (");
            strSql.Append("@F_OpenId,@F_IP,@F_Country,@F_CountryCode,@F_Area,@F_AreaCode,@F_Region,@F_RegionCode,@F_City,@F_CityCode,@F_County,@F_CountyCode,@F_ISP,@F_ISPCode,@F_CreateTime,@F_Type)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
                    new SqlParameter("@F_OpenId", SqlDbType.VarChar,500),
                    new SqlParameter("@F_IP", SqlDbType.VarChar,50),
                    new SqlParameter("@F_Country", SqlDbType.NVarChar,50),
                    new SqlParameter("@F_CountryCode", SqlDbType.VarChar,50),
                    new SqlParameter("@F_Area", SqlDbType.NVarChar,50),
                    new SqlParameter("@F_AreaCode", SqlDbType.VarChar,50),
                    new SqlParameter("@F_Region", SqlDbType.NVarChar,50),
                    new SqlParameter("@F_RegionCode", SqlDbType.VarChar,50),
                    new SqlParameter("@F_City", SqlDbType.NVarChar,50),
                    new SqlParameter("@F_CityCode", SqlDbType.VarChar,50),
                    new SqlParameter("@F_County", SqlDbType.NVarChar,50),
                    new SqlParameter("@F_CountyCode", SqlDbType.VarChar,50),
                    new SqlParameter("@F_ISP", SqlDbType.NVarChar,50),
                    new SqlParameter("@F_ISPCode", SqlDbType.VarChar,50),
                    new SqlParameter("@F_CreateTime", SqlDbType.DateTime),
                    new SqlParameter("@F_Type", SqlDbType.Int,4)};
            parameters[0].Value = model.F_OpenId;
            parameters[1].Value = model.F_IP;
            parameters[2].Value = model.F_Country;
            parameters[3].Value = model.F_CountryCode;
            parameters[4].Value = model.F_Area;
            parameters[5].Value = model.F_AreaCode;
            parameters[6].Value = model.F_Region;
            parameters[7].Value = model.F_RegionCode;
            parameters[8].Value = model.F_City;
            parameters[9].Value = model.F_CityCode;
            parameters[10].Value = model.F_County;
            parameters[11].Value = model.F_CountyCode;
            parameters[12].Value = model.F_ISP;
            parameters[13].Value = model.F_ISPCode;
            parameters[14].Value = model.F_CreateTime;
            parameters[15].Value = model.F_Type;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
		/// 增加一条数据
		/// </summary>
		public int AddInOut(T_Chat_InOut model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into T_Chat_InOut(");
            strSql.Append("F_OpenId,F_IP,F_InTime,F_OutTime,F_Type)");
            strSql.Append(" values (");
            strSql.Append("@F_OpenId,@F_IP,@F_InTime,@F_OutTime,@F_Type)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
                    new SqlParameter("@F_OpenId", SqlDbType.VarChar,50),
                    new SqlParameter("@F_IP", SqlDbType.VarChar,50),
                    new SqlParameter("@F_InTime", SqlDbType.DateTime),
                    new SqlParameter("@F_OutTime", SqlDbType.DateTime),
                    new SqlParameter("@F_Type", SqlDbType.Int,4)};
            parameters[0].Value = model.F_OpenId;
            parameters[1].Value = model.F_IP;
            parameters[2].Value = model.F_InTime;
            parameters[3].Value = model.F_OutTime;
            parameters[4].Value = model.F_Type;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
		/// 更新一条数据
		/// </summary>
		public bool UpdateInOut(T_Chat_InOut model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_Chat_InOut set ");
            strSql.Append("F_OpenId=@F_OpenId,");
            strSql.Append("F_IP=@F_IP,");
            strSql.Append("F_InTime=@F_InTime,");
            strSql.Append("F_OutTime=@F_OutTime,");
            strSql.Append("F_Type=@F_Type");
            strSql.Append(" where F_Id=@F_Id");
            SqlParameter[] parameters = {
                    new SqlParameter("@F_OpenId", SqlDbType.VarChar,50),
                    new SqlParameter("@F_IP", SqlDbType.VarChar,50),
                    new SqlParameter("@F_InTime", SqlDbType.DateTime),
                    new SqlParameter("@F_OutTime", SqlDbType.DateTime),
                    new SqlParameter("@F_Type", SqlDbType.Int,4),
                    new SqlParameter("@F_Id", SqlDbType.Int,4)};
            parameters[0].Value = model.F_OpenId;
            parameters[1].Value = model.F_IP;
            parameters[2].Value = model.F_InTime;
            parameters[3].Value = model.F_OutTime;
            parameters[4].Value = model.F_Type;
            parameters[5].Value = model.F_Id;

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

        /// <summary>
		/// 更新签出时间
		/// </summary>
		public bool UpdateOutTime(string code)
        {
            string sql = "update T_Chat_InOut set F_OutTime=getdate() where F_OpenId='"+ code + "' and isnull(F_OutTime,'')=''";
            int rows = DbHelperSQL.ExecuteSql(sql);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获取客户记录
        /// </summary>
        /// <returns></returns>
        public DataTable GetChatLog(string code, out long nextnum, out int isfirst, int count = 10, long num = 0)
        {
            string sql = "select top " + count + " * from T_Chat_Message where (F_FromUser='" + code + "' or F_ToUser='" + code + "')";
            if (num > 0)
            {
                sql += " and F_Id <" + num;
            }
            sql += " order by F_CreateTime desc";

            var dt = DbHelperSQL.Query(sql).Tables[0];
            int n = dt.Rows.Count;
            nextnum = 0;
            if (n > 0)
            {
                nextnum = long.Parse(dt.Rows[n - 1]["F_Id"].ToString());
            }
            isfirst = 0;
            if (n < count)
            {
                isfirst = 1;
            }

            return dt;
        }

        /// <summary>
        /// 获取最近记录的时间
        /// </summary>
        /// <returns></returns>
        public string GetMessageDateByCode(string code)
        {
            string sql = "select MAX(F_CreateTime) from T_Chat_Message where (F_FromUser='" + code + "' or F_ToUser='" + code + "')";
            object obj = DbHelperSQL.GetSingle(sql);
            if (obj == null)
                return "";
            else
                return obj.ToString();
        }

        /// <summary>
        /// 获取客户信息
        /// </summary>
        /// <returns></returns>
        public T_Chat_User GetCusUser(string code)
        {
            string sql = "select top 1 * from T_Chat_User where F_OpenId='" + code + "' order by F_CreateTime desc";
            var dt = DbHelperSQL.Query(sql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                var row = dt.Rows[0];

                T_Chat_User model = new T_Chat_User();

                if (row["F_Id"] != null && row["F_Id"].ToString() != "")
                {
                    model.F_Id = int.Parse(row["F_Id"].ToString());
                }
                if (row["F_Type"] != null && row["F_Type"].ToString() != "")
                {
                    model.F_Type = int.Parse(row["F_Type"].ToString());
                }
                if (row["F_Name"] != null)
                {
                    model.F_Name = row["F_Name"].ToString();
                }
                if (row["F_Account"] != null)
                {
                    model.F_Account = row["F_Account"].ToString();
                }
                if (row["F_OpenId"] != null)
                {
                    model.F_OpenId = row["F_OpenId"].ToString();
                }
                if (row["F_HeadImgUrl"] != null)
                {
                    model.F_HeadImgUrl = row["F_HeadImgUrl"].ToString();
                }
                if (row["F_MainUserCode"] != null)
                {
                    model.F_MainUserCode = row["F_MainUserCode"].ToString();
                }
                if (row["F_OtherUserCode"] != null)
                {
                    model.F_OtherUserCode = row["F_OtherUserCode"].ToString();
                }
                if (row["F_IP"] != null)
                {
                    model.F_IP = row["F_IP"].ToString();
                }
                if (row["F_State"] != null && row["F_State"].ToString() != "")
                {
                    model.F_State = int.Parse(row["F_State"].ToString());
                }
                if (row["F_CreateUser"] != null)
                {
                    model.F_CreateUser = row["F_CreateUser"].ToString();
                }
                if (row["F_CreateTime"] != null && row["F_CreateTime"].ToString() != "")
                {
                    model.F_CreateTime = DateTime.Parse(row["F_CreateTime"].ToString());
                }
                if (row["F_IsDelete"] != null && row["F_IsDelete"].ToString() != "")
                {
                    model.F_IsDelete = int.Parse(row["F_IsDelete"].ToString());
                }
                if (row["F_DeleteUser"] != null)
                {
                    model.F_DeleteUser = row["F_DeleteUser"].ToString();
                }
                if (row["F_DeleteTime"] != null && row["F_DeleteTime"].ToString() != "")
                {
                    model.F_DeleteTime = DateTime.Parse(row["F_DeleteTime"].ToString());
                }

                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获取客户信息
        /// </summary>
        /// <returns></returns>
        public T_Chat_IPList GetIPInfo(string ip, string openid)
        {
            string sql = "select top 1 * from T_Chat_IPList where F_IP='" + ip + "' and F_OpenId='" + openid + "'  order by F_CreateTime desc";
            var dt = DbHelperSQL.Query(sql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                var row = dt.Rows[0];

                T_Chat_IPList model = new T_Chat_IPList();

                if (row["F_Id"] != null && row["F_Id"].ToString() != "")
                {
                    model.F_Id = int.Parse(row["F_Id"].ToString());
                }
                if (row["F_OpenId"] != null)
                {
                    model.F_OpenId = row["F_OpenId"].ToString();
                }
                if (row["F_IP"] != null)
                {
                    model.F_IP = row["F_IP"].ToString();
                }
                if (row["F_Country"] != null)
                {
                    model.F_Country = row["F_Country"].ToString();
                }
                if (row["F_CountryCode"] != null)
                {
                    model.F_CountryCode = row["F_CountryCode"].ToString();
                }
                if (row["F_Area"] != null)
                {
                    model.F_Area = row["F_Area"].ToString();
                }
                if (row["F_AreaCode"] != null)
                {
                    model.F_AreaCode = row["F_AreaCode"].ToString();
                }
                if (row["F_Region"] != null)
                {
                    model.F_Region = row["F_Region"].ToString();
                }
                if (row["F_RegionCode"] != null)
                {
                    model.F_RegionCode = row["F_RegionCode"].ToString();
                }
                if (row["F_City"] != null)
                {
                    model.F_City = row["F_City"].ToString();
                }
                if (row["F_CityCode"] != null)
                {
                    model.F_CityCode = row["F_CityCode"].ToString();
                }
                if (row["F_County"] != null)
                {
                    model.F_County = row["F_County"].ToString();
                }
                if (row["F_CountyCode"] != null)
                {
                    model.F_CountyCode = row["F_CountyCode"].ToString();
                }
                if (row["F_ISP"] != null)
                {
                    model.F_ISP = row["F_ISP"].ToString();
                }
                if (row["F_ISPCode"] != null)
                {
                    model.F_ISPCode = row["F_ISPCode"].ToString();
                }
                if (row["F_CreateTime"] != null && row["F_CreateTime"].ToString() != "")
                {
                    model.F_CreateTime = DateTime.Parse(row["F_CreateTime"].ToString());
                }
                if (row["F_Type"] != null && row["F_Type"].ToString() != "")
                {
                    model.F_Type = int.Parse(row["F_Type"].ToString());
                }

                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获取客服名称
        /// </summary>
        /// <returns></returns>
        public string GetUserName(string usercode)
        {
            string sql = "select F_UserName  from T_Sys_UserAccount where F_UserCode='" + usercode + "'";
            string name = DbHelperSQL.GetSingle(sql).ToString();           
            return name;
        }

        /// <summary>
        /// 是否黑名单
        /// </summary>
        /// <returns></returns>
        public bool IsBlack(string ip)
        {
            string sql = "select F_Id  from T_Chat_IPBlackList where F_IP='" + ip + "' and F_IsDelete=0 and F_State=1";

            return DbHelperSQL.GetSingle(sql)!=null;
        }

        /// <summary>
        /// 是否存入ip
        /// </summary>
        /// <returns></returns>
        public bool IsExitIP(string ip,string openid)
        {
            string sql = "select F_Id  from T_Chat_IPList where F_IP='" + ip + "' and F_OpenId='" + openid + "' ";

            return DbHelperSQL.GetSingle(sql) != null;
        }

        private object lockcode = new object();

        /// <summary>
        /// 获取游客ID
        /// </summary>
        /// <returns></returns>
        public string GetCode()
        {
            lock (lockcode)
            {
                string code = "";
                string pre = "Web" + DateTime.Now.ToString("yyyyMMdd");
                string sql = "select Top 1 F_OpenId  from T_Chat_User where substring(F_OpenId,1,11)='" + pre + "' order by F_CreateTime desc";
                var obj = DbHelperSQL.GetSingle(sql);
                if (obj == null)
                {
                    code = pre + "00001";
                }
                else
                {
                    string lastcode = obj.ToString();
                    int con = Int32.Parse(lastcode.Substring(lastcode.Length - 5, 5));
                    code = pre + (con + 1).ToString("00000");
                }

                T_Chat_User user = new T_Chat_User();
                user.F_OpenId = code;
                user.F_Account = code;
                user.F_Type = 1;
                user.F_State = 0;
                user.F_IsDelete = 0;
                user.F_CreateTime = DateTime.Now;

                AddUser(user);

                return code;

            }
        }

        #region 支持多入口菜单 - 例如：欢迎词

        /// <summary>
        /// 获取接入欢迎语
        /// </summary>
        /// <returns></returns>
        public string GetWebcomeByGroupId(int groupid)
        {
            // 后期如果坐席有分组可以用
            string sql = "SELECT TOP 1 F_Welcome FROM T_Chat_IM_Entrance WHERE F_State = 0 AND F_IsDelete = 0";
            object obj = DbHelperSQL.GetSingle(sql);
            if (obj == null)
                return "";
            else
                return obj.ToString();
        }
        #endregion

        #region 快速回复 - 例如：欢迎词，结束语，常用语，公共用语等

        /// <summary>
        /// 获取快速回复内容 - 还没有用到
        /// </summary>
        /// <returns></returns>
        public string GetReplyContenByCId(int classid)
        {
            string sql = "SELECT TOP 1 F_ReplyContent FROM dbo.T_Chat_IM_ReplyList WHERE  F_IsDelete = 0 AND F_ClassId=" + classid + " ";
            object obj = DbHelperSQL.GetSingle(sql);
            if (obj == null)
                return "";
            else
                return obj.ToString();
        }
        #endregion

        #region 智能机器人 - 关键词

        /// <summary>
        /// 获取最近记录的时间
        /// </summary>
        /// <returns></returns>
        public string GetSmartRobotByKey(string keyword)
        {
            string keyWords = KeywordSpliter.DoGetKeyword(keyword);
            string[] sArrayKey = keyWords.Trim().Split(',');
            string sqlStr = "";
            object obj = null;

            foreach (string key in sArrayKey)
            {
                if (key.Trim() != "")
                    sqlStr += " or F_KeyWord like '%" + key + "%'";
            }
            if (!string.IsNullOrEmpty(sqlStr))
            {
                // 去掉前面的or，且拼接（)
                StringBuilder sb = new StringBuilder(sqlStr);
                sb.Replace(" or ", " AND (", 0, 4);
                sqlStr = sb.ToString();
                sqlStr += ")";
                string sql = "SELECT TOP 1 F_Content FROM dbo.T_Chat_IM_SmartRobot WHERE 1=1 AND F_IsDelete = 0 AND F_State = 1 " + sqlStr + " ";
                obj = DbHelperSQL.GetSingle(sql);
            }            
            if (obj == null)
                return "";
            else
                return obj.ToString();
        }
        #endregion

        #region 对象 类

        /// <summary>
        /// 客户类
        /// </summary>
        public class T_Chat_User
        {
            public T_Chat_User()
            { }
            #region Model
            private int _f_id;
            private int? _f_type;
            private string _f_name;
            private string _f_account;
            private string _f_openid;
            private string _f_headimgurl;
            private string _f_mainusercode;
            private string _f_otherusercode;
            private string _f_ip;
            private int? _f_state;
            private string _f_createuser;
            private DateTime? _f_createtime;
            private int? _f_isdelete;
            private string _f_deleteuser;
            private DateTime? _f_deletetime;
            /// <summary>
            /// 
            /// </summary>
            public int F_Id
            {
                set { _f_id = value; }
                get { return _f_id; }
            }
            /// <summary>
            /// 类型（1网站2微信）
            /// </summary>
            public int? F_Type
            {
                set { _f_type = value; }
                get { return _f_type; }
            }
            /// <summary>
            /// 昵称
            /// </summary>
            public string F_Name
            {
                set { _f_name = value; }
                get { return _f_name; }
            }
            /// <summary>
            /// 微信账号或者网站账号
            /// </summary>
            public string F_Account
            {
                set { _f_account = value; }
                get { return _f_account; }
            }
            /// <summary>
            /// 微信标识或者网站标识
            /// </summary>
            public string F_OpenId
            {
                set { _f_openid = value; }
                get { return _f_openid; }
            }
            /// <summary>
            /// 头像url
            /// </summary>
            public string F_HeadImgUrl
            {
                set { _f_headimgurl = value; }
                get { return _f_headimgurl; }
            }
            /// <summary>
            /// 主客服
            /// </summary>
            public string F_MainUserCode
            {
                set { _f_mainusercode = value; }
                get { return _f_mainusercode; }
            }
            /// <summary>
            /// 副客服
            /// </summary>
            public string F_OtherUserCode
            {
                set { _f_otherusercode = value; }
                get { return _f_otherusercode; }
            }
            /// <summary>
            /// IP
            /// </summary>
            public string F_IP
            {
                set { _f_ip = value; }
                get { return _f_ip; }
            }
            /// <summary>
            /// 状态
            /// </summary>
            public int? F_State
            {
                set { _f_state = value; }
                get { return _f_state; }
            }
            /// <summary>
            /// 
            /// </summary>
            public string F_CreateUser
            {
                set { _f_createuser = value; }
                get { return _f_createuser; }
            }
            /// <summary>
            /// 
            /// </summary>
            public DateTime? F_CreateTime
            {
                set { _f_createtime = value; }
                get { return _f_createtime; }
            }
            /// <summary>
            /// 
            /// </summary>
            public int? F_IsDelete
            {
                set { _f_isdelete = value; }
                get { return _f_isdelete; }
            }
            /// <summary>
            /// 
            /// </summary>
            public string F_DeleteUser
            {
                set { _f_deleteuser = value; }
                get { return _f_deleteuser; }
            }
            /// <summary>
            /// 
            /// </summary>
            public DateTime? F_DeleteTime
            {
                set { _f_deletetime = value; }
                get { return _f_deletetime; }
            }
            #endregion Model

        }

        /// <summary>
        /// 消息类
        /// </summary>
        public class T_Chat_Message
        {
            public T_Chat_Message()
            { }
            #region Model
            private long _f_id;
            private string _f_fromuser;
            private string _f_touser;
            private string _f_message;
            private string _f_file;
            private string _f_ip;
            private int? _f_humanclass = 0;
            private int? _f_sourcetype;
            private int? _f_messagetype;
            private int? _f_state;
            private string _f_createuser;
            private DateTime? _f_createtime;
            private int? _f_isdelete;
            private string _f_deleteuser;
            private DateTime? _f_deletetime;
            /// <summary>
            /// 
            /// </summary>
            public long F_Id
            {
                set { _f_id = value; }
                get { return _f_id; }
            }
            /// <summary>
            /// 发送人
            /// </summary>
            public string F_FromUser
            {
                set { _f_fromuser = value; }
                get { return _f_fromuser; }
            }
            /// <summary>
            /// 接收人
            /// </summary>
            public string F_ToUser
            {
                set { _f_touser = value; }
                get { return _f_touser; }
            }
            /// <summary>
            /// 内容
            /// </summary>
            public string F_Message
            {
                set { _f_message = value; }
                get { return _f_message; }
            }
            /// <summary>
            /// 附件
            /// </summary>
            public string F_File
            {
                set { _f_file = value; }
                get { return _f_file; }
            }
            /// <summary>
            /// 发送人（判断图像 0不知 1客服 2客户 3机器人）
            /// </summary>
            public int? F_HumanClass
            {
                set { _f_humanclass = value; }
                get { return _f_humanclass; }
            }
            /// <summary>
            /// IP
            /// </summary>
            public string F_IP
            {
                set { _f_ip = value; }
                get { return _f_ip; }
            }
            /// <summary>
            /// 类型（1网站2微信）
            /// </summary>
            public int? F_SourceType
            {
                set { _f_sourcetype = value; }
                get { return _f_sourcetype; }
            }
            /// <summary>
            /// 消息类型(1文字消息，2列表消息，3图片/视频/语音)
            /// </summary>
            public int? F_MessageType
            {
                set { _f_messagetype = value; }
                get { return _f_messagetype; }
            }
            /// <summary>
            /// 状态
            /// </summary>
            public int? F_State
            {
                set { _f_state = value; }
                get { return _f_state; }
            }
            /// <summary>
            /// 
            /// </summary>
            public string F_CreateUser
            {
                set { _f_createuser = value; }
                get { return _f_createuser; }
            }
            /// <summary>
            /// 
            /// </summary>
            public DateTime? F_CreateTime
            {
                set { _f_createtime = value; }
                get { return _f_createtime; }
            }
            /// <summary>
            /// 
            /// </summary>
            public int? F_IsDelete
            {
                set { _f_isdelete = value; }
                get { return _f_isdelete; }
            }
            /// <summary>
            /// 
            /// </summary>
            public string F_DeleteUser
            {
                set { _f_deleteuser = value; }
                get { return _f_deleteuser; }
            }
            /// <summary>
            /// 
            /// </summary>
            public DateTime? F_DeleteTime
            {
                set { _f_deletetime = value; }
                get { return _f_deletetime; }
            }
            #endregion Model

        }

        /// <summary>
        /// 用户IP类
        /// </summary>
        public class T_Chat_IPList
        {
            public T_Chat_IPList()
            { }
            #region Model
            private int _f_id;
            private string _f_openid;
            private string _f_ip;
            private string _f_country;
            private string _f_countrycode;
            private string _f_area;
            private string _f_areacode;
            private string _f_region;
            private string _f_regioncode;
            private string _f_city;
            private string _f_citycode;
            private string _f_county;
            private string _f_countycode;
            private string _f_isp;
            private string _f_ispcode;
            private DateTime? _f_createtime;
            private int? _f_type;
            /// <summary>
            /// 
            /// </summary>
            public int F_Id
            {
                set { _f_id = value; }
                get { return _f_id; }
            }
            /// <summary>
            /// 
            /// </summary>
            public string F_OpenId
            {
                set { _f_openid = value; }
                get { return _f_openid; }
            }
            /// <summary>
            /// 
            /// </summary>
            public string F_IP
            {
                set { _f_ip = value; }
                get { return _f_ip; }
            }
            /// <summary>
            /// 
            /// </summary>
            public string F_Country
            {
                set { _f_country = value; }
                get { return _f_country; }
            }
            /// <summary>
            /// 
            /// </summary>
            public string F_CountryCode
            {
                set { _f_countrycode = value; }
                get { return _f_countrycode; }
            }
            /// <summary>
            /// 
            /// </summary>
            public string F_Area
            {
                set { _f_area = value; }
                get { return _f_area; }
            }
            /// <summary>
            /// 
            /// </summary>
            public string F_AreaCode
            {
                set { _f_areacode = value; }
                get { return _f_areacode; }
            }
            /// <summary>
            /// 
            /// </summary>
            public string F_Region
            {
                set { _f_region = value; }
                get { return _f_region; }
            }
            /// <summary>
            /// 
            /// </summary>
            public string F_RegionCode
            {
                set { _f_regioncode = value; }
                get { return _f_regioncode; }
            }
            /// <summary>
            /// 
            /// </summary>
            public string F_City
            {
                set { _f_city = value; }
                get { return _f_city; }
            }
            /// <summary>
            /// 
            /// </summary>
            public string F_CityCode
            {
                set { _f_citycode = value; }
                get { return _f_citycode; }
            }
            /// <summary>
            /// 
            /// </summary>
            public string F_County
            {
                set { _f_county = value; }
                get { return _f_county; }
            }
            /// <summary>
            /// 
            /// </summary>
            public string F_CountyCode
            {
                set { _f_countycode = value; }
                get { return _f_countycode; }
            }
            /// <summary>
            /// 
            /// </summary>
            public string F_ISP
            {
                set { _f_isp = value; }
                get { return _f_isp; }
            }
            /// <summary>
            /// 
            /// </summary>
            public string F_ISPCode
            {
                set { _f_ispcode = value; }
                get { return _f_ispcode; }
            }
            /// <summary>
            /// 
            /// </summary>
            public DateTime? F_CreateTime
            {
                set { _f_createtime = value; }
                get { return _f_createtime; }
            }
            /// <summary>
            /// 
            /// </summary>
            public int? F_Type
            {
                set { _f_type = value; }
                get { return _f_type; }
            }
            #endregion Model

        }

        /// <summary>
        /// 用户签入签出时间
        /// </summary>
        public class T_Chat_InOut
        {
            public T_Chat_InOut()
            { }
            #region Model
            private int _f_id;
            private string _f_openid;
            private string _f_ip;
            private DateTime? _f_intime;
            private DateTime? _f_outtime;
            private int? _f_type;
            /// <summary>
            /// 
            /// </summary>
            public int F_Id
            {
                set { _f_id = value; }
                get { return _f_id; }
            }
            /// <summary>
            /// 
            /// </summary>
            public string F_OpenId
            {
                set { _f_openid = value; }
                get { return _f_openid; }
            }
            /// <summary>
            /// 
            /// </summary>
            public string F_IP
            {
                set { _f_ip = value; }
                get { return _f_ip; }
            }
            /// <summary>
            /// 
            /// </summary>
            public DateTime? F_InTime
            {
                set { _f_intime = value; }
                get { return _f_intime; }
            }
            /// <summary>
            /// 
            /// </summary>
            public DateTime? F_OutTime
            {
                set { _f_outtime = value; }
                get { return _f_outtime; }
            }
            /// <summary>
            /// 用户类型（1客服，2游客）
            /// </summary>
            public int? F_Type
            {
                set { _f_type = value; }
                get { return _f_type; }
            }
            #endregion Model

        }
        #endregion

    }
}