KN_CAMP_SOFTWARE_TEST/Code/ClassLib/ZHYQ/OracleDAL/TBL/SysMessageLog.cs

using System;
using System.Data;
using System.Text;
using FangYar.Model.TBL;
using MySql.Data.MySqlClient;

namespace FangYar.OracleDAL.TBL
{
    public class SysMessageLogDAL : FangYar.IDAL.TBL.SysMessageLogIDAL 
    {
        /// <summary>
        /// 根据接收人uid获取未读数
        /// </summary>
        /// <param name="ReceiveId">接收人uid</param>
        /// <returns></returns>
        public int GetPendingCount(string ReceiveId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from tbl_message_log where RECEIVEID = '" + ReceiveId + "' and STATE= '0'");
            object obj = FangYar.Common.MySqlHelper.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 获取符合条件的数量
        /// </summary>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        public int GetListCount(string whereStr)
        {
            
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from tbl_message_log ");
            if (!string.IsNullOrEmpty(whereStr))
            {
                strSql.Append(" where " + whereStr);
            }
            object obj = FangYar.Common.MySqlHelper.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 根据接收人uid获取消息通知(日志)（包含分页）
        /// </summary>
        /// <param name="PageIndex">页码</param>
        /// <param name="PageSize">条数</param>
        /// <param name="ReceiveId">接收人uid</param>
        /// <param name="orderById">点击的本条数据置顶</param>
        /// <returns></returns>
        public DataTable GetList(int PageIndex, int PageSize, string ReceiveId, string state, string orderById)
        {
            int startnum = (PageIndex - 1) * PageSize;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from tbl_message_log where RECEIVEID = '"+ ReceiveId + "' and STATE = '"+ state + "'");
            strSql.Append(" order by");
            if (!string.IsNullOrEmpty(orderById))
            {
                strSql.Append(" ID='"+ orderById + "' desc ,");
            }
            strSql.Append(" SENDTIME desc ");
            strSql.Append("limit " + startnum + ", " + PageSize);
            return Common.MySqlHelper.QueryTable(strSql.ToString());
        }


        /// <summary>
        /// 根据主键ID，获取消息通知(日志)
        /// </summary>
        /// <param name="id">主键ID</param>
        /// <returns></returns>
        public Model.TBL.TBL_MESSAGE_LOG GetModel(string id)
        {
            Model.TBL.TBL_MESSAGE_LOG model = new TBL_MESSAGE_LOG();
            string sql = "select * from TBL_MESSAGE_LOG where ID=@ID";
            MySqlParameter[] paras ={
                new MySqlParameter("@ID",MySqlDbType.VarChar,50)
            };
            paras[0].Value = id;
            DataTable dt = FangYar.Common.MySqlHelper.QueryTable(sql, paras);
            if (dt.Rows.Count > 0)
            {
                DataRowToModel(model, dt.Rows[0]);
                return model;
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="logModel">APP消息通知(日志)表</param>
        /// <returns></returns>
        public bool AddLog(TBL_MESSAGE_LOG logModel)
        {
            StringBuilder strsql = new StringBuilder();
            strsql.Append("insert into TBL_MESSAGE_LOG (ID,TITLE,CONTENT,SENDID,SENDNAME,SENDTIME,TYPE,URL,PARAM,RECEIVEID,RECEIVENAME,OPENTIME,STATE,ORG_ID,PARTY_ORG_ID,IS_CAMP,METTING_ID,EXTENDCODE1,EXTENDCODE2,EXTENDCODE3,EXTENDCODE4,EXTENDCODE5,EXTENDCODE6)");
            strsql.Append("values");
            strsql.Append("(@ID,@TITLE,@CONTENT,@SENDID,@SENDNAME,@SENDTIME,@TYPE,@URL,@PARAM,@RECEIVEID,@RECEIVENAME,@OPENTIME,@STATE,@ORG_ID,@PARTY_ORG_ID,@IS_CAMP,@METTING_ID,@EXTENDCODE1,@EXTENDCODE2,@EXTENDCODE3,@EXTENDCODE4,@EXTENDCODE5,@EXTENDCODE6)");
            MySqlParameter[] paras ={
                                    new MySqlParameter("@ID",logModel.ID),
                                    new MySqlParameter("@TITLE",logModel.TITLE),
                                    new MySqlParameter("@CONTENT",logModel.CONTENT),
                                    new MySqlParameter("@SENDID",logModel.SENDID),
                                    new MySqlParameter("@SENDNAME",logModel.SENDNAME),
                                    new MySqlParameter("@SENDTIME",logModel.SENDTIME),
                                    new MySqlParameter("@TYPE",logModel.TYPE),
                                    new MySqlParameter("@URL",logModel.URL),
                                    new MySqlParameter("@PARAM",logModel.PARAM),
                                    new MySqlParameter("@RECEIVEID",logModel.RECEIVEID),
                                    new MySqlParameter("@RECEIVENAME",logModel.RECEIVENAME),
                                    new MySqlParameter("@OPENTIME",logModel.OPENTIME),
                                    new MySqlParameter("@STATE",logModel.STATE),
                                    new MySqlParameter("@ORG_ID",logModel.ORG_ID),
                                    new MySqlParameter("@PARTY_ORG_ID",logModel.PARTY_ORG_ID),
                                    new MySqlParameter("@IS_CAMP",logModel.IS_CAMP),
                                    new MySqlParameter("@METTING_ID",logModel.METTING_ID),
                                    new MySqlParameter("@EXTENDCODE1",logModel.EXTENDCODE1),
                                    new MySqlParameter("@EXTENDCODE2",logModel.EXTENDCODE2),
                                    new MySqlParameter("@EXTENDCODE3",logModel.EXTENDCODE3),
                                    new MySqlParameter("@EXTENDCODE4",logModel.EXTENDCODE4),
                                    new MySqlParameter("@EXTENDCODE5",logModel.EXTENDCODE5),
                                    new MySqlParameter("@EXTENDCODE6",logModel.EXTENDCODE6)
                                    };
            int i = FangYar.Common.MySqlHelper.ExecuteSql(strsql.ToString(), paras);
            if (i > 0) { return true; }
            else { return false; }
        }


        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="logModel">APP消息通知(日志)表</param>
        /// <returns></returns>
        public bool EditLog(TBL_MESSAGE_LOG logModel)
        {

            StringBuilder strsql = new StringBuilder();
            strsql.Append("update TBL_MESSAGE_LOG set ");
            strsql.Append("TITLE=@TITLE,");
            strsql.Append("CONTENT=@CONTENT,");
            strsql.Append("SENDID=@SENDID,");
            strsql.Append("SENDNAME=@SENDNAME,");
            strsql.Append("SENDTIME=@SENDTIME,");
            strsql.Append("TYPE=@TYPE,");
            strsql.Append("URL=@URL,");
            strsql.Append("PARAM=@PARAM,");
            strsql.Append("RECEIVEID=@RECEIVEID,");
            strsql.Append("RECEIVENAME=@RECEIVENAME,");
            strsql.Append("OPENTIME=@OPENTIME,");
            strsql.Append("STATE=@STATE,");
            strsql.Append("ORG_ID=@ORG_ID,");
            strsql.Append("PARTY_ORG_ID=@PARTY_ORG_ID,");
            strsql.Append("IS_CAMP=@IS_CAMP,");
            strsql.Append("METTING_ID=@METTING_ID,");
            strsql.Append("EXTENDCODE1=@EXTENDCODE1,");
            strsql.Append("EXTENDCODE2=@EXTENDCODE2,");
            strsql.Append("EXTENDCODE3=@EXTENDCODE3,");
            strsql.Append("EXTENDCODE4=@EXTENDCODE4,");
            strsql.Append("EXTENDCODE5=@EXTENDCODE5,");
            strsql.Append("EXTENDCODE6=@EXTENDCODE6");
            strsql.Append(" where ID=@ID");
            MySqlParameter[] paras ={
                new MySqlParameter("@TITLE",logModel.TITLE),
                new MySqlParameter("@CONTENT",logModel.CONTENT),
                new MySqlParameter("@SENDID",logModel.SENDID),
                new MySqlParameter("@SENDNAME",logModel.SENDNAME),
                new MySqlParameter("@SENDTIME",logModel.SENDTIME),
                new MySqlParameter("@TYPE",logModel.TYPE),
                new MySqlParameter("@URL",logModel.URL),
                new MySqlParameter("@PARAM",logModel.PARAM),
                new MySqlParameter("@RECEIVEID",logModel.RECEIVEID),
                new MySqlParameter("@RECEIVENAME",logModel.RECEIVENAME),
                new MySqlParameter("@OPENTIME",logModel.OPENTIME),
                new MySqlParameter("@STATE",logModel.STATE),
                new MySqlParameter("@ORG_ID",logModel.ORG_ID),
                new MySqlParameter("@PARTY_ORG_ID",logModel.PARTY_ORG_ID),
                new MySqlParameter("@IS_CAMP",logModel.IS_CAMP),
                new MySqlParameter("@METTING_ID",logModel.METTING_ID),
                new MySqlParameter("@EXTENDCODE1",logModel.EXTENDCODE1),
                new MySqlParameter("@EXTENDCODE2",logModel.EXTENDCODE2),
                new MySqlParameter("@EXTENDCODE3",logModel.EXTENDCODE3),
                new MySqlParameter("@EXTENDCODE4",logModel.EXTENDCODE4),
                new MySqlParameter("@EXTENDCODE5",logModel.EXTENDCODE5),
                new MySqlParameter("@EXTENDCODE6",logModel.EXTENDCODE6),
                new MySqlParameter("@ID",logModel.ID)
            };
            int i = FangYar.Common.MySqlHelper.ExecuteSql(strsql.ToString(), paras);
            if (i > 0) { return true; }
            else { return false; }
        }


        /// <summary>
        /// 根据主键ID修改：打开时间、状态
        /// </summary>
        /// <param name="id">主键ID</param>
        /// <param name="openTime">打开时间</param>
        /// <param name="state">状态：0:未读；1:已读</param>
        /// <returns></returns>
        public bool EditLog(string id, string openTime, string state)
        {
            StringBuilder strsql = new StringBuilder();
            strsql.Append("update TBL_MESSAGE_LOG set ");
            strsql.Append("OPENTIME=@OPENTIME,");
            strsql.Append("STATE=@STATE");
            strsql.Append(" where ID=@ID");
            MySqlParameter[] paras ={
                new MySqlParameter("@OPENTIME",openTime),
                new MySqlParameter("@STATE",state),
                new MySqlParameter("@ID",id)
            };
            int i = FangYar.Common.MySqlHelper.ExecuteSql(strsql.ToString(), paras);
            if (i > 0) { return true; }
            else { return false; }
        }

        /// <summary>
        /// 根据接收人ID修改 (将未读状态转为已读状态)：打开时间、状态
        /// </summary>
        /// <param name="receiveId">接收人ID</param>
        /// <param name="openTime">打开时间</param>
        /// <param name="state">状态：0:未读；1:已读</param>
        /// <returns></returns>
        public bool EditReceiveLog(string receiveId, string openTime, string state)
        {
            StringBuilder strsql = new StringBuilder();
            strsql.Append("update TBL_MESSAGE_LOG set ");
            strsql.Append("OPENTIME=@OPENTIME,");
            strsql.Append("STATE=@STATE");
            strsql.Append(" where RECEIVEID=@RECEIVEID and STATE=0");
            MySqlParameter[] paras ={
                new MySqlParameter("@OPENTIME",openTime),
                new MySqlParameter("@STATE",state),
                new MySqlParameter("@RECEIVEID",receiveId)
            };
            int i = FangYar.Common.MySqlHelper.ExecuteSql(strsql.ToString(), paras);
            if (i > 0) { return true; }
            else { return false; }
        }
















        /// <summary>
        /// DataRow转model实体类对象
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private void DataRowToModel(Model.TBL.TBL_MESSAGE_LOG model, DataRow dr)
        {
            if (dr.Table.Columns.Contains("ID"))
            {
                if (dr["ID"] != null && dr["ID"].ToString() != "")
                {
                    model.ID = dr["ID"].ToString();
                }
            }
            if (dr.Table.Columns.Contains("TITLE"))
            {
                if (dr["TITLE"] != null && dr["TITLE"].ToString() != "")
                {
                    model.TITLE = dr["TITLE"].ToString();
                }
            }
            if (dr.Table.Columns.Contains("CONTENT"))
            {
                if (dr["CONTENT"] != null && dr["CONTENT"].ToString() != "")
                {
                    model.CONTENT = dr["CONTENT"].ToString();
                }
            }
            if (dr.Table.Columns.Contains("SENDID"))
            {
                if (dr["SENDID"] != null && dr["SENDID"].ToString() != "")
                {
                    model.SENDID = dr["SENDID"].ToString();
                }
            }
            if (dr.Table.Columns.Contains("SENDNAME"))
            {
                if (dr["SENDNAME"] != null && dr["SENDNAME"].ToString() != "")
                {
                    model.SENDNAME = dr["SENDNAME"].ToString();
                }
            }
            if (dr.Table.Columns.Contains("SENDTIME"))
            {
                if (dr["SENDTIME"] != null && dr["SENDTIME"].ToString() != "")
                {
                    model.SENDTIME = dr["SENDTIME"].ToString();
                }
            }
            if (dr.Table.Columns.Contains("TYPE"))
            {
                if (dr["TYPE"] != null && dr["TYPE"].ToString() != "")
                {
                    model.TYPE = dr["TYPE"].ToString();
                }
            }
            if (dr.Table.Columns.Contains("URL"))
            {
                if (dr["URL"] != null && dr["URL"].ToString() != "")
                {
                    model.URL = dr["URL"].ToString();
                }
            }
            if (dr.Table.Columns.Contains("PARAM"))
            {
                if (dr["PARAM"] != null && dr["PARAM"].ToString() != "")
                {
                    model.PARAM = dr["PARAM"].ToString();
                }
            }
            if (dr.Table.Columns.Contains("RECEIVEID"))
            {
                if (dr["RECEIVEID"] != null && dr["RECEIVEID"].ToString() != "")
                {
                    model.RECEIVEID = dr["RECEIVEID"].ToString();
                }
            }
            if (dr.Table.Columns.Contains("RECEIVENAME"))
            {
                if (dr["RECEIVENAME"] != null && dr["RECEIVENAME"].ToString() != "")
                {
                    model.RECEIVENAME = dr["RECEIVENAME"].ToString();
                }
            }
            if (dr.Table.Columns.Contains("OPENTIME"))
            {
                if (dr["OPENTIME"] != null && dr["OPENTIME"].ToString() != "")
                {
                    model.OPENTIME = dr["OPENTIME"].ToString();
                }
            }
            if (dr.Table.Columns.Contains("STATE"))
            {
                if (dr["STATE"] != null && dr["STATE"].ToString() != "")
                {
                    model.STATE = dr["STATE"].ToString();
                }
            }
            if (dr.Table.Columns.Contains("ORG_ID"))
            {
                if (dr["ORG_ID"] != null && dr["ORG_ID"].ToString() != "")
                {
                    model.ORG_ID = dr["ORG_ID"].ToString();
                }
            }
            if (dr.Table.Columns.Contains("PARTY_ORG_ID"))
            {
                if (dr["PARTY_ORG_ID"] != null && dr["PARTY_ORG_ID"].ToString() != "")
                {
                    model.PARTY_ORG_ID = dr["PARTY_ORG_ID"].ToString();
                }
            }
            if (dr.Table.Columns.Contains("IS_CAMP"))
            {
                if (dr["IS_CAMP"] != null && dr["IS_CAMP"].ToString() != "")
                {
                    model.IS_CAMP = dr["IS_CAMP"].ToString();
                }
            }
            if (dr.Table.Columns.Contains("METTING_ID"))
            {
                if (dr["METTING_ID"] != null && dr["METTING_ID"].ToString() != "")
                {
                    model.METTING_ID = dr["METTING_ID"].ToString();
                }
            }
            if (dr.Table.Columns.Contains("EXTENDCODE1"))
            {
                if (dr["EXTENDCODE1"] != null && dr["EXTENDCODE1"].ToString() != "")
                {
                    model.EXTENDCODE1 = dr["EXTENDCODE1"].ToString();
                }
            }
            if (dr.Table.Columns.Contains("EXTENDCODE2"))
            {
                if (dr["EXTENDCODE2"] != null && dr["EXTENDCODE2"].ToString() != "")
                {
                    model.EXTENDCODE2 = dr["EXTENDCODE2"].ToString();
                }
            }
            if (dr.Table.Columns.Contains("EXTENDCODE3"))
            {
                if (dr["EXTENDCODE3"] != null && dr["EXTENDCODE3"].ToString() != "")
                {
                    model.EXTENDCODE3 = dr["EXTENDCODE3"].ToString();
                }
            }
            if (dr.Table.Columns.Contains("EXTENDCODE4"))
            {
                if (dr["EXTENDCODE4"] != null && dr["EXTENDCODE4"].ToString() != "")
                {
                    model.EXTENDCODE4 = dr["EXTENDCODE4"].ToString();
                }
            }
            if (dr.Table.Columns.Contains("EXTENDCODE5"))
            {
                if (dr["EXTENDCODE5"] != null && dr["EXTENDCODE5"].ToString() != "")
                {
                    model.EXTENDCODE5 = dr["EXTENDCODE5"].ToString();
                }
            }
            if (dr.Table.Columns.Contains("EXTENDCODE6"))
            {
                if (dr["EXTENDCODE6"] != null && dr["EXTENDCODE6"].ToString() != "")
                {
                    model.EXTENDCODE6 = dr["EXTENDCODE6"].ToString();
                }
            }
        }
    }
}