reply.cs

动易SiteFactory&#8482 网上商店系统1.0源代码

namespace PowerEasy.SqlServerDal.Crm
{
    using PowerEasy.IDal.Crm;
    using PowerEasy.Model.Crm;
    using PowerEasy.SqlServerDal;
    using System;
    using System.Collections.Generic;
    using System.Data;

    public class Reply : IReply
    {
        public bool Add(ReplyInfo info)
        {
            Parameters cmdParams = GetParameters(info);
            cmdParams.AddInParameter("@QuestionID", DbType.Int32, info.QuestionId);
            return DBHelper.ExecuteSql("Insert into PE_Reply(ID,QuestionID,ReplyCreator,ReplyTime,ReplyContent) Values(@ID,@QuestionID,@ReplyCreator,@ReplyTime,@ReplyContent)", cmdParams);
        }

        public bool DeleteById(int id)
        {
            return DBHelper.ExecuteSql("Delete PE_Reply Where ID = @ID", new Parameters("@ID", DbType.Int32, id));
        }

        public bool DeleteByQuestionId(int questionId)
        {
            return DBHelper.ExecuteSql("Delete PE_Reply Where QuestionID = @QuestionID", new Parameters("@QuestionID", DbType.Int32, questionId));
        }

        public bool DeleteByQuestionId(string questionIdList)
        {
            return DBHelper.ExecuteSql("Delete PE_Reply Where QuestionID In (" + questionIdList + ")");
        }

        private static ReplyInfo GetInfobyReader(NullableDataReader rdr)
        {
            ReplyInfo info = new ReplyInfo();
            info.Id = rdr.GetInt32("ID");
            info.QuestionId = rdr.GetInt32("QuestionID");
            info.ReplyCreator = rdr.GetString("ReplyCreator");
            info.ReplyTime = rdr.GetDateTime("ReplyTime");
            info.ReplyContent = rdr.GetString("ReplyContent");
            return info;
        }

        public ReplyInfo GetLastReplyById(int questionId)
        {
            using (NullableDataReader reader = DBHelper.ExecuteReaderSql("Select top 1 ReplyCreator,ReplyTime from PE_Reply where QuestionID = @ID Order by ReplyTime DESC", new Parameters("@ID", DbType.Int32, questionId)))
            {
                if (reader.Read())
                {
                    ReplyInfo info = new ReplyInfo();
                    info.ReplyCreator = reader.GetString("ReplyCreator");
                    info.ReplyTime = reader.GetDateTime("ReplyTime");
                    return info;
                }
                return new ReplyInfo(true);
            }
        }

        public int GetMaxId()
        {
            return DBHelper.GetMaxId("PE_Reply", "ID");
        }

        private static Parameters GetParameters(ReplyInfo info)
        {
            Parameters parameters = new Parameters();
            parameters.AddInParameter("@ID", DbType.Int32, info.Id);
            parameters.AddInParameter("@ReplyCreator", DbType.String, info.ReplyCreator);
            parameters.AddInParameter("@ReplyTime", DbType.DateTime, info.ReplyTime);
            parameters.AddInParameter("@ReplyContent", DbType.String, info.ReplyContent);
            return parameters;
        }

        public ReplyInfo GetReplyById(int id)
        {
            using (NullableDataReader reader = DBHelper.ExecuteReaderSql("Select * from PE_Reply where ID = @ID", new Parameters("@ID", DbType.Int32, id)))
            {
                if (reader.Read())
                {
                    return GetInfobyReader(reader);
                }
                return new ReplyInfo(true);
            }
        }

        public IList<ReplyInfo> GetReplyByQuestionId(int questionId)
        {
            IList<ReplyInfo> list = new List<ReplyInfo>();
            using (NullableDataReader reader = DBHelper.ExecuteReaderSql("select * from PE_Reply Where QuestionID = @QuestionID order by ID", new Parameters("@QuestionID", DbType.Int32, questionId)))
            {
                while (reader.Read())
                {
                    list.Add(GetInfobyReader(reader));
                }
            }
            return list;
        }

        public DataTable GetReplyStatistic()
        {
            DataTable table = new DataTable();
            table.Columns.Add("AdminName", typeof(string));
            table.Columns.Add("QuestionCount", typeof(int));
            table.Columns.Add("ReplyCount", typeof(int));
            using (NullableDataReader reader = DBHelper.ExecuteReaderSql("select ReplyCreator, count(DISTINCT QuestionID)as QuestionCount ,Count(QuestionID) as ReplyCount from PE_Reply where ReplyCreator in (select AdminName from PE_Admin) Group by  replyCreator order by QuestionCount desc"))
            {
                while (reader.Read())
                {
                    DataRow row = table.NewRow();
                    row["AdminName"] = reader.GetString("ReplyCreator");
                    row["QuestionCount"] = reader.GetInt32("QuestionCount");
                    row["ReplyCount"] = reader.GetInt32("ReplyCount");
                    table.Rows.Add(row);
                }
            }
            return table;
        }

        public bool HasOtherReplyer(int questionId)
        {
            return DBHelper.ExistsSql("select R.ID from PE_Reply R inner join PE_Question Q on R.QuestionID=Q.ID where Q.ID=@ID and R.ReplyCreator != Q.QuestionCreator", new Parameters("@ID", DbType.Int32, questionId));
        }

        public bool Update(ReplyInfo info)
        {
            return DBHelper.ExecuteSql("Update PE_Reply set ReplyCreator=@ReplyCreator,ReplyTime=@ReplyTime,ReplyContent=@ReplyContent Where ID = @ID", GetParameters(info));
        }
    }
}