message_list.cs

CRM管理系统 CRM管理系统

//------------------------------------*/
//版权所有：杭州商易信息技术有限公司
//功能描述：消息列表
//    作者：沈伟
//    日期：2008/08/26
//------------------------------------*/
using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using SystemFrameworks;
namespace Itsv.DAL
{
    /// <summary>
    /// 数据访问类message_list。
    /// </summary>
    public class message_list
    {
        public message_list()
        { }
        #region  成员方法

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

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int msg_id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from message_list where msg_id=" + msg_id + "");
            return DbHelperSQL.Exists(strSql.ToString());
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public void Add(Itsv.Model.message_list model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into message_list(");
            strSql.Append("sender,sender_id,receiver,receiver_id,content,title,is_security,is_important,is_read,acc_name,acc_path,date_time");
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append("'" + model.sender + "',");
            strSql.Append("" + model.sender_id + ",");
            strSql.Append("'" + model.receiver + "',");
            strSql.Append("" + model.receiver_id + ",");
            strSql.Append("'" + model.content + "',");
            strSql.Append("'" + model.title + "',");
            strSql.Append("" + model.is_security + ",");
            strSql.Append("" + model.is_important + ",");
            strSql.Append("" + model.is_read + ",");
            strSql.Append("'" + model.acc_name + "',");
            strSql.Append("'" + model.acc_path + "',");
            strSql.Append("'" + model.date_time + "'");
            strSql.Append(")");
            DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public void Update(Itsv.Model.message_list model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update message_list set ");
            strSql.Append("sender='" + model.sender + "',");
            strSql.Append("sender_id=" + model.sender_id + ",");
            strSql.Append("receiver='" + model.receiver + "',");
            strSql.Append("receiver_id=" + model.receiver_id + ",");
            strSql.Append("content='" + model.content + "',");
            strSql.Append("title='" + model.title + "',");
            strSql.Append("is_security=" + model.is_security + ",");
            strSql.Append("is_important=" + model.is_important + ",");
            strSql.Append("is_read=" + model.is_read + ",");
            strSql.Append("acc_name='" + model.acc_name + "',");
            strSql.Append("acc_path='" + model.acc_path + "',");
            strSql.Append("date_time='" + model.date_time + "'");
            strSql.Append(" where msg_id=" + model.msg_id + "");
            DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public void Delete(int msg_id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete message_list ");
            strSql.Append(" where msg_id=" + msg_id);
            DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Itsv.Model.message_list GetModel(int msg_id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  ");
            strSql.Append(" [msg_id],[sender],[sender_id],[receiver],[receiver_id],[content],[title],[is_security],[is_important],[is_read],[acc_name],[acc_path],[date_time] ");
            strSql.Append(" from message_list ");
            strSql.Append(" where msg_id=" + msg_id);
            Itsv.Model.message_list model = new Itsv.Model.message_list();
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            model.msg_id = msg_id;
            if (ds.Tables[0].Rows.Count > 0)
            {
                model.sender = ds.Tables[0].Rows[0]["sender"].ToString();
                if (ds.Tables[0].Rows[0]["sender_id"].ToString() != "")
                {
                    model.sender_id = int.Parse(ds.Tables[0].Rows[0]["sender_id"].ToString());
                }
                model.receiver = ds.Tables[0].Rows[0]["receiver"].ToString();
                if (ds.Tables[0].Rows[0]["receiver_id"].ToString() != "")
                {
                    model.receiver_id = int.Parse(ds.Tables[0].Rows[0]["receiver_id"].ToString());
                }
                model.content = ds.Tables[0].Rows[0]["content"].ToString();
                model.title = ds.Tables[0].Rows[0]["title"].ToString();
                if (ds.Tables[0].Rows[0]["is_security"].ToString() != "")
                {
                    model.is_security = int.Parse(ds.Tables[0].Rows[0]["is_security"].ToString());
                }
                if (ds.Tables[0].Rows[0]["is_important"].ToString() != "")
                {
                    model.is_important = int.Parse(ds.Tables[0].Rows[0]["is_important"].ToString());
                }
                if (ds.Tables[0].Rows[0]["is_read"].ToString() != "")
                {
                    model.is_read = int.Parse(ds.Tables[0].Rows[0]["is_read"].ToString());
                }
                model.acc_name = ds.Tables[0].Rows[0]["acc_name"].ToString();
                model.acc_path = ds.Tables[0].Rows[0]["acc_path"].ToString();
                if (ds.Tables[0].Rows[0]["date_time"].ToString() != "")
                {
                    model.date_time = DateTime.Parse(ds.Tables[0].Rows[0]["date_time"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select msg_id,sender,sender_id,sender+'('+(select name from users where sender_id=user_id)+')' as sender_name,receiver,receiver_id,receiver+'('+(select name from users where receiver_id=user_id)+')' as receiver_name,content,title,is_security,is_important,is_read,acc_name,acc_path,date_time  FROM message_list");
            if (strWhere.Trim() != "")
            {

                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }


        /// <summary>
        /// 记录总数
        /// </summary>
        public string count(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from message_list where " + strWhere);
            return DbHelperSQL.GetSingle(strSql.ToString()).ToString();
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Itsv.Model.message_list GetModelByReceivedID(int received_id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ");
            strSql.Append(" [msg_id],[sender],[sender_id],[receiver],[receiver_id],[content],[title],[is_security],[is_important],[is_read],[acc_name],[acc_path],[date_time] ");
            strSql.Append(" from message_list ");
            strSql.Append(" where receiver_id=" + received_id);
            strSql.Append(" and is_read = 0 order by msg_id desc ");
            Itsv.Model.message_list model = new Itsv.Model.message_list();
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                model.msg_id = Convert.ToInt32(ds.Tables[0].Rows[0]["msg_id"].ToString());
                model.sender = ds.Tables[0].Rows[0]["sender"].ToString();
                if (ds.Tables[0].Rows[0]["sender_id"].ToString() != "")
                {
                    model.sender_id = int.Parse(ds.Tables[0].Rows[0]["sender_id"].ToString());
                }
                model.receiver = ds.Tables[0].Rows[0]["receiver"].ToString();
                if (ds.Tables[0].Rows[0]["receiver_id"].ToString() != "")
                {
                    model.receiver_id = int.Parse(ds.Tables[0].Rows[0]["receiver_id"].ToString());
                }
                model.content = ds.Tables[0].Rows[0]["content"].ToString();
                model.title = ds.Tables[0].Rows[0]["title"].ToString();
                if (ds.Tables[0].Rows[0]["is_security"].ToString() != "")
                {
                    model.is_security = int.Parse(ds.Tables[0].Rows[0]["is_security"].ToString());
                }
                if (ds.Tables[0].Rows[0]["is_important"].ToString() != "")
                {
                    model.is_important = int.Parse(ds.Tables[0].Rows[0]["is_important"].ToString());
                }
                if (ds.Tables[0].Rows[0]["is_read"].ToString() != "")
                {
                    model.is_read = int.Parse(ds.Tables[0].Rows[0]["is_read"].ToString());
                }
                model.acc_name = ds.Tables[0].Rows[0]["acc_name"].ToString();
                model.acc_path = ds.Tables[0].Rows[0]["acc_path"].ToString();
                if (ds.Tables[0].Rows[0]["date_time"].ToString() != "")
                {
                    model.date_time = DateTime.Parse(ds.Tables[0].Rows[0]["date_time"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 更新一条数据是否是已读
        /// </summary>
        public void UpdateIsRead(int msg_id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update message_list set is_read = 1");
            strSql.Append(" where msg_id=" + msg_id);
            DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 根据name获得user_id 和account
        /// </summary>
        public DataSet queryUserID(string name)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT user_id,account FROM Users WHERE account = '" + name + "'");
            return DbHelperSQL.Query(strSql.ToString());
        }

    }
}
        #endregion